数据库1

程序员文章站 2022-06-04 07:51:16

...

今日学习目标 :
能够理解数据库的概念
能够安装MySql数据库
能够启动,关闭及登录MySql
能够使用SQL语句操作数据库
能够使用SQL语句操作表结构
能够使用SQL语句进行数据的添加
能够使用SQL语句添加约束

Mysql数据库
Mysql学习内容：三天的学习内容
第一天：
数据库的入门，从安装到卸载。
对于数据库、数据库表的操作以及数据记录的增删改
第二天：
表记录的操作
数据库多表的设计
第三天：
数据库多表查询
强化练习

学习内容：
1、mysql安装和卸载
2、数据库的操作
3、数据表的操作
4、数据记录的增删改

1数据库
什么是数据库？
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

存放数据：

数据存储的几种方式：
1、内存
2、硬盘
3、数据库

我们今天学习的数据库是mysql。关系型数据库。

什么是关系型数据库？

关系型数据库
建立在关系模型上的数据库系统。
关系模型：

1.1常用的关系型数据库

Oracle：甲骨文公司—专门的数据库厂商，oracle是收费，大型数据库，几乎可以用于任何系统任何平台。

MySQL：早期开源免费数据库产品，被oralce收购，从6.0开始出现收费版本。

DB2：IBM数据库产品，大型收费数据库。
SYBASE 中等规模数据库，收费（很少用了）。SYBASE公司的另外一款产品，PowerDesigner—数据库建模工具。
SQL Server 微软公司数据库产品收费中等规模数据库，操作系统要求是windows 结合.net 一起使用。

Java开发者主要使用 MySQL(5.5) 、Oracle、DB2 三种数据库

2Mysql安装 (掌握)
2.1mysql的安装和卸载
2.1.1mysql卸载
1、找到mysql的安装目录：
在mysql的安装目录。找到my.ini 文件。
找到datadir 并且记录路径。（mysql的数据文件的目录）
datadir=”C:/ProgramData/MySQL/MySQL Server 5.5/Data/”

Windows7系统：
进入C盘====》点击 “工具” ====》选择“文件夹选项” ====》点击 “查看”====》将“隐藏文件和文件夹”选项改为“显示隐藏的文件、文件夹和驱动器”，如下图所示：

3、打开控制面板—卸载程序
4、卸载mysql
5、删除mysql的安装目录，删除mysql的数据文件目录datadir.第一步中记录的文件目录
mysql默认的数据文件目录，在ProgramData这个目录下，并且ProgramData这个目录是隐藏目录，需要设置显示隐藏目录。
删除ProgramDate下的mysql的相关目录。

6、运行“regedit”：删除HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL文件夹
注意：一般情况下之前之前5步即可，如果还不能安装，就执行步骤6，运行regedit,试着删除注册表中的数据.

2.1.2mysql的安装

选择第二个，自定义

9、选择配置方式，“Detailed Configuration（手动精确配置）”、“Standard Configuration（标准配置）”，我们选择“Detailed Configuration”，方便熟悉配置过程。

10、选择服务器类型，“Developer Machine（开发测试类，mysql占用很少资源）”、“Server Machine（服务器类型，mysql占用较多资源）”、“Dedicated MySQL Server Machine（专门的数据库服务器，mysql占用所有可用资源）”

11、选择mysql数据库的大致用途，“Multifunctional Database（通用多功能型，好）”、“Transactional Database Only（服务器类型，专注于事务处理，一般）”、“Non-Transactional Database Only（非事务处理型，较简单，主要做一些监控、记数用，对MyISAM数据类型的支持仅限于non-transactional），按“Next”继续。

12、选择数据文件安装的目录，可以选择默认

一直默认下一步，直到如下：

设置mysql数据库的默认编码集

设置环境变量和是否开机启动：

设置密码:

该过程可能有点长：

安装完后：在命令窗口输入mysql -u root -p进行登录

2.1.3环境变量
在命令行中，输入mysql –u root –p 的时候，出现mysql不是内部命令。系统变量没有配置。

path后面加入：
是mysql的安装目录中的bin目录
添加path的时候，如果出现前面的没有分号，需要自己去加上分号；要注意分号的中英文。

按照完成之后，在cmd中输入
mysql –u root –p

出现如下图，表示mysql启动成功。

使用exit可以断开连接

2.1.4重置mysql–root密码

1)停止mysql服务运行输入services.msc 停止mysql服务

2)在cmd下输入: mysqld –skip-grant-tables
mysqld –skip-grant-tables：就是在启动mysql时不启动grant-tables，授权表
启动服务器光标不动（注意：不要关闭该窗口）

启动mysql 服务

3)新打开cmd 输入mysql -u root -p 不需要密码

use mysql; 作用：使用mysql这个数据库
update user set password=password(‘123’) WHERE user=’root’;

4)关闭两个cmd窗口注意：在任务管理器结束mysqld 进程

5) 在服务管理页面重启mysql 服务。

重新登录的时候需要使用新的密码。

3.数据库的操作 (了解)
3.1mysql数据库的内部存储结构
当一台电脑安装了mysql数据库服务，就可以把这台电脑称之为数据库服务器。

用户通过在dos窗口中输入：
mysql [-h 连接的主机ip -P端口3306）] -u 用户名 -p 密码

  mysql -h 127.0.0.1 -P 3306 -u root -p

  连接本机： 省略 -h 和 -P  主机和端口。

一个数据库服务器可以有多个数据库，一个数据库中可以有多个表（就是一个二维表），一个表中可以有多条数据记录。

总结：
一个数据库软件，可以管理多个数据仓库（数据库）。
一个数据仓库可以管理多个数据表。
每个数据表中可以存储多行数据记录。
提示：每创建一个数据仓库，会在mysql的数据文件目录中多一个文件夹。

3.2sql语言
SQL是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集。SQL功能强大、简单易学、使用方便，已经成为了数据库操作的基础，并且现在几乎所有的关系型数据库均支持SQL。

特点：
非过程性语言。一条语句一个结果。多条语句之间没有影响。每一条SQL执行完都会有一个具体的结果出现。

SQL是用来操作关系数据库的语言，具有查询、操纵、定义和控制关系型数据库的四方面功能

3.3SQL分类

DDL （数据定义语言）
数据定义语言 - Data Definition Language
用来定义数据库的对象，如数据表、视图、索引等
create drop alter truncate

DML （数据操纵语言）
数据处理语言 - Data Manipulation Language
在数据库表中更新，增加和删除记录
如 update， insert， delete 不包含查询

DCL （数据控制语言）
数据控制语言 – Data Control Language
指用于设置用户权限和控制事务语句
如grant，revoke，if…else，while，begin transaction

DQL （数据查询语言）（★★★★★）
数据查询语言 – Data Query Language
数据表记录的查询。
select

sql能做些什么？

1、操作数据库
2、操作数据库中的表
3、操作数据库中的表记录

我们学习SQL语句的路线：
1、学习SQL数据对数据库的操作
2、学习SQL语句对数据表结构的操作
3、学习SQL语句对数据表中的数据记录操作(★★★★★)
4、数据仓库中的数据备份和恢复(CV)。

4.数据库操作语句 (练习)

sql语句每一条需要有 ; 分号，表示结束。

在操作数据仓库之前,一定要确认mysql 数据库启动：
1、运行窗口，输入 services.msc 打开服务窗口

如果没有启动，选中mysql右键启动。

2、连接到数据库上

4.1查看所有数据库
目标：
1、如何查询mysql数据库软件的所有数据仓库？
2、如何查看具体数据仓库的编码？

1、查询当前所有的数据库：
语法： show databases;

2、查看当前数据的创建方式：查看数据库的编码表
语法：show create database 库名;

当我们创建数据库的时候，如果没有指定编码表，默认使用的是安装数据库软件时指定的编码表。

需求：
1、查询mysql数据库软件的所有数据仓库
2、查看mysql数据仓库的编码

information_schema数据库:
其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。
performance_schema 数据库：
存储引擎：命名PERFORMANCE_SCHEMA ,主要用于收集数据库服务器性能参数。
mysql 数据库:
mysql库是系统库，里面保存有账户信息，权限信息，存储过程，event,时区等信息。
test 数据库:
这个是安装时候创建的一个测试数据库，和它的名字一样，是一个完全的空数据库，没有任何表，可以删除。

4.2创建数据库
创建数据的语法：

1、create database 数据库名：由于创建数据库时没有指定编码表，因此会使用安装数据库时默认的编码表
2、create database 数据库名 character set 编码表名；创建数据库会使用指定的编码表

需求：数据库名称不要用中文
1、创建一个名称为mydb1的数据库。
2、创建一个使用utf8字符集的mydb2数据库。

1、创建一个名称为mydb1的数据库。

2、创建一个使用utf8字符集的mydb2数据库。

4.3删除数据库
语法：drop database 数据库名;

需求：删除前面创建的mydb1数据库

4.4修改数据库编码集
语法：alter database 数据库名称 character set 字符集;

需求：修改mydb2字符集为gbk

指定字符集是 utf8 utf-8 是错误的 (mysql不认识UTF-8)。
查询某个数据库的字符集,以及创建数据库的语句：show create database 数据库名;

4.5切换数据库和查看正在使用的数据库
当我们创建好了数据库之后，最终需要给数据库中添加数据表。然后给数据表中添加数据。
在需要添加数据表之前，我们需要先切换到这个数据仓库中。

切换数据库：
语法： use 数据库名;

查询当前正在使用的数据库：
语法：select database();

重点练习：创建库、删除库、切换库的语句

4.6数据库操作语句小结
创建
create database 数据库名
查看所有数据库
show databases。
查看指定数据库建表语句及字符集
show create database 数据库名
删除数据库
drop database 数据库名
修改数据库字符集-了解
alter database 数据库名 character set ‘字符集’
切换数据库
use 数据库名
查看当前数据库名称
select database();

5.数据表结构的sql语句 (了解)
5.1数据表的创建语句
注意：创建表的时候，一定要先切换到某一个具体的数据库
语法：use 数据库名

数据表结构：

建表语句：
create table 表名(
列名数据类型，
列名数据类型，
……
列名数据类型 (最后一个列不需要逗号)
)；

注意：多个列之间使用逗号隔开，最后一个列不需要输入逗号。
创建表的时候，一定切换到某一个具体的数据库。
use 数据库名。

数据类型：
字符类型 : Java中的String 和char 类型对应 mysql 中的 varchar(列的长度)、char(列的长度) variable 可变的
varchar:长度可变
name varchar(20):设置name字段的长度为20,name的储存长度在20以内都可以，并且会自动适应长短。
char:长度固定
name char(20):设置name字段的长度为20，name的存储长度在20以内，如果不满20，用空格补足。
例如：lisi
只有4个长度，剩下的用空格补足。
如果存储的长度超出了表中列的长度，存储报错。

问题：char 的性能好。 varchar可以节省空间。
通常时候，如果长度不固定，我们使用varchar。

使用char的情况。当某个字段的长度固定的时候，可以采用char。例如身份证号或者手机号。

大数据类型
Java中：
字节流：InputStream, outputStream
字符流：Reader, Writer
mysql中：
BLOB 保存的字节数据
TEXT 保存字符数据

通常我们不会把文件存储到数据库。（占用资源，操作速度慢）
我们会把文件的路径（通常存放在本地磁盘）存到数据库中。

数值型 : Java中：byte、short 、 int 、 long 、float、 double
整形：TINYINT 、SMALLINT、MEDIUMINT、INT、BIGINT、FLOAT、DOUBLE 在创建数据表的时候，数值型也有自己的长度，一般不需要指定，使用默认的长度。

概念 : 表达式类型自动提升. byte + byte -> int short + short -> int

小数：FLOAT（单精度）、DOUBLE（双精度）
在定义类型的时候可以设置两个参数，如sal float(5,2)：sal字段总共5位长度，可以有两位小数。

逻辑性
Java中的boolean
mysql中：BIT 比特位
BIT： 1 或者0组成的数据
其数据有两种取值：0和1，长度为1位。在输入0以外的其他值时，系统均把它们当1看待。这种数据类型常作为逻辑变量使用，用来表示真、假或是、否等。

日期型
Java 中的日期类 : Date, SimpleDateFormat, Calendar,
date ：日期—-只有日期，年月日
time：时间—–时分秒
dateTime：日期和时间都包含 “yyyy-MM-dd”
timestamp：时间戳. 日期和时间都包含 ——-当数据被修改的时候
timestamp:当其他数据发生改变的时候，这个字段会自己修改为当前时间。

【示例】
需求：创建一个员工表，员工表有工号、姓名、年龄、性别、生日。

注意：如果刚连数据库的话，需要先切换一个数据库才能进行建表。
建表如下：

建表语句如下：
create table emp(
id int,
name varchar(20),
age int,
gender int,
birthday date
);

5.2※查看表

show tables : 查看该数据库的所有的表

show create table 表名：查看建表语句以及字符集—了解

desc 表名：查看表的列的信息（查看表结构）

5.3约束
约束：规定，限制。限制表中列的内容。需要满足一定的规则。
数据库中的约束：限制列的内容。

意义：保证数据的有效性和完整性。可以确保数据库满足业务规则。

主键约束

主键约束：primary key
作用：设置某一个字段为主键，主键的特性是唯一(不能重复)，并且不能为空。
一般使用一个没有任何意义的字段，比如id作为一个主键（没有意义就不需要修改）。
如果主键是一个int类型，还可以设置主键自增长。（即在插入记录的时候可以不用设置主键，让其自动以递增的形式添加）
一张表中只能有一个主键。

【示例】
create table 表名 (
id int primary key auto_increment,
name varchar(20),
………….
)；

唯一约束 :

唯一约束：unique 内容不允许重复，可以为null（null不算重复）。
一个表里可以添加多个唯一约束。

写法：列名类型（长度） unique
【示例】
create table 表名 (
id int primary key auto_increment,
name varchar(20) unique,
………….
)；

唯一约束和主键约束的区别
1、唯一约束可以是空(null)。但是主键约束不能为空
2、一张表中只能有一个主键，但是唯一约束可以有多个

非空约束

非空约束：not null 。不允许为空。表示该列的内容不允许为空。

写法：列名类型（长度） not null,
【示例】
create table emp(
id int primary key auto_increment,
name varchar(20) unique,
age int not null,
………….
);

约束的目的。
目的：保证数据的正确性。
约束列。限制列的内容。

【示例】
需求：创建一个员工表，员工有工号、姓名、年龄、性别、生日和住址。
1、要求工号是主键并且设置主键自增长
2、姓名必须是唯一性的
3、年龄必须是非空的
4、地址必须是唯一的，并且非空。

建表如下：

查看结构：

5.4数据表结构的修改

修改数据表：
可以对表名、表中的列名、列的类型、列的约束进行增删改。

语法：alter table 表名增/删/改列名类型(长度) 约束；

1、增加列语法： alter table 表名 add 列名类型(长度) 约束;

需求1：在emp2表上增加salary列

alter table emp2 add salary double;

2、修改现有列类型、长度和约束
语法：alter table 表名 modify 列名类型(长度) 约束;

需求1：修改name列的长度为30
需求2：修改birthday列不能为null

3、修改现有列名称
语法：alter table 表名 change 旧列名新列名类型(长度) 约束;
需求：修改列名name为username

4、删除现有列语法：alter table 表名 drop 列名 ;
需求：删除age列

5、修改表名语法： rename table 旧表名 to 新表名;
需求：将emp2表名修改为employee表

rename table emp2 to person;

6、修改表的字符集语法：alter table 表名 character set 编码集;
需求7：将employee的编码修改成utf8
查询库编码和表编码

5.5数据表的删除

语法：drop table 表名；

需求：删除emp表

5.6数据表小结

数据表创建
create table 表名(
列名数据类型约束，
列名数据类型约束约束，
………
);

查看表
show tables：查看所有的表
show create table 表名：查看建表语句以及字符集
desc 表名：查看表结构。
show columns from 表名;

修改表的语句
alter table 表名（add|modify|drop|change）列名类型（长度）约束。-了解
rename table 旧表名 to 新表名-了解

删除表
drop table 表名

6.数据记录的增删改（重点）
insert 语句—-数据记录的增加
在java代码中对数据库操作最频繁的就是对表中数据的CRUD操作：create read / retrive update delete
数据记录存储位置：表。

6.1.1方式一：全写
【示例】语法：insert into 表名 (列名，列名，列名……) values (值，值，值……);

注意事项：

1、值与列一一对应。有多少个列，就需要写多少个值。如果某一个列没有值。可以使用null。表示插入空。
2、值的数据类型，与列被定义的数据类型要相匹配。并且值的长度。不能够超多定义的列的长度。
3、字符串：插入字符类型的数据，必须写单引号。在mysql中，使用单引号表示字符串。
4、date 时间类型的数据也可以直接使用单引号表示： ‘yyyyMMdd’ ,’yyyy-MM-dd’,’yyyy/MM/dd’ 。
5、在插入数据的时候, 如果某些列可以为null, 或者是自动增长的列, 或者有默认值的, 在插入的时候可以省略. 或者编写null, 实现自动增长.
6、如果给表中的所有列插入数据, 这时可以省略表后面的列名, 直接写values.

使用select * from 表名 —查看该表的所有信息。

练习：向数据库中插入三条内容。
–如果主键是自动增长，可以直接书写null。

注意：我们需要再创建一张，建表语句如下：
create table user (
id int primary key auto_increment,
name varchar(20) unique,
age int not null,
gender int,
birthday date,
address varchar(50) unique not null
);

当插入含有中文的数据的时候，出现如下错误。

原因：dos窗口的字符集是gbk,而数据库是utf8，在数据进行保存的时候出现了编码错误。

mysql有六处使用了字符集，分别为：

client：客户端
是客户端使用的字符集。Dos命令窗口就属于客户端
connection：客户端
是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。
results：客户端
是数据库给客户端返回数据时使用的字符集设定，如果没有指明，使用服务器默认的字符集。
database：服务器
是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。
server：服务器
是服务器安装时指定的默认字符集设定。
system：服务器
是数据库系统使用的字符集设定。

错误的产生原因：
客户端的数据是gbk,而服务器中设置的客户端字符集是utf8的，编码格式不一致。
查看mysql服务的所有客户端和服务端使用的字符集：show variables like ‘character%’;

解决方案：
第一种-了解： set names gbk ; 临时将客户端的字符集设置为gbk
* 只对当前窗口有效，dos窗口关闭之后就会失效

第二种：配置mysql/my.ini 文件
my.ini 在mysql的安装目录：将客户端的字符集修改为gbk

注意：修改字符集之后需要重新连接数据库。退出 MySql, 重新登录即可.

在对数据表中的数据进行修改、插入的时候，使用的dos窗口，这时dos窗口需要对插入的中文进行编码，然后把数据插入到数据库中。而dos窗口中使用的编码表是gbk。但是在mysql数据库中针对客户端控制的设置的编码表是utf8.
这时只能修改数据库中默认的客户端程序的编码表。

修改mysql的配置文件，my.ini

修改为：

6.1.2方式二：省略部分列
注意：
可以省略部分列名。某一列有默认值，或者允许为空，才可以省略。

主键是自增长的认为是有默认值的。也可以省略

【示例】
需求：插入一行记录，该记录只有姓名、年龄以及住址。

常见错误如下：
1、姓名重复

2、age字段不能为空

6.1.3※方式三：省略所有的列
语法 : insert into 表名 values（值，值，值，值）；
1、表中有多少列，就有多个值。
2、按照表的结构，列和值去对应。
【示例】

整理：
如果主键：是自增长。不需要人工赋值（可以给值null）。数据库会按照自己的算法，为主键填充值。
通常遇到主键自增长，人工赋值的时候，赋值null。mysql会自己把null替换成新的值

6.2update 语句—-修改表记录
语法: update 表名 set 列名=值,列名=值…. [ where条件语句 ];

注意事项：
1、如果不加条件，将会修改某一列的所有值。
2、一般修改数据时，都需要增加条件。
多个列使用逗号隔开。

= 表示等于
>
<

=
<=
<> 表示不等

【示例1】：
将所有人的年龄修改为20岁。

由于没有设置条件，因此所有人的年龄改成了20岁

【示例2】
将姓名为张三的人的年龄改为18岁。

【示例3】
将姓名为李四的人的年龄改为30，地址改为航都路18号

【示例4】
将王五的年龄在原基础上增加2岁。

6.3delete语句—–删除表中数据的语句
语法：delete from 表名 [where条件语句]

删除满足条件的行的数据。delete是删除行的数据。

注意：如果没有where，删除表中的所有的记录。

delete删除的是行。

【示例1】：
删除表中名称为’王五’的记录。

【示例2】
删除年龄是30岁的员工。

【示例3】
删除表中所有记录。

6.4Truncate 语句—-删除数据
语法 : truncate table 表名：
先删除表,再创建表，就等于将数据全部删除了。

性能问题： truncate table的性能更好

【示例】
truncate table 表名；—删除表的所有记录.

面试题1：(了解)
delete删除表中的数据时没有加where 条件，会删除表中的所有数据，它与truncate 有什么区别？

删除过程不同
truncate 删除数据，过程先将整个表删除，再重新创建
delete 删除数据，逐行删除记录
truncate 效率要好于 delete
语言定义不同
truncate 属于DDL ，delete 属于DML

1.DELETE
　・DML语言
　・可以回退(可以将数据找回来)
　・可以有条件的删除

 DELETE FROM 表名 WHERE 条件

2.TRUNCATE TABLE
　・DDL语言
　・无法回退(不可以将数据找回来)
　・默认所有的表内容都删除
　・删除速度比delete快。

TRUNCATE TABLE 表名

TRUNCATE 表名

事务管理只能对DML 有效，被事务管理的SQL语句可以回滚到SQL执行前状态。

事务：逻辑上的一组(一条或者多条sql语句)操作。称之为事务。这组sql语句，要么都成功，要么都失败。

【举例】
a:1000块
b:1000块
a给b转账100块
a：1000-100=900
——出事了。
b：1000+100=1100
转账的过程中出现了问题，导致a账户少了100，但是b账户并没有收到。

正常操作下总额应该是不变的，a和b的总金额2000。

开启事务
a：1000-100=900
——出事了。
b：1000+100=1100
使用事务：当发现在这组操作发生错误的时候，事务回滚，回到转账之前的状态。

start transaction 开启事务

失败：rollback 事务的回滚 —- 事务的结束，把修改的内容恢复到之前的状态。
成功：commit 提交 ———–事务的结束。把修改的内容进行永久保存

事务只对数据操作语言有效。
【示例】
开启事务，使用delete语句删除数据，然后回滚事务
开启事务，使用truncate语句删除数据，然后回滚事务
分别测试以上例子，发现事务只对delete起作用，因为delete语句属于DML语言（数据库操纵语言）。
delete语句测试：

truncate语句测试：

面试题2：(了解)
delete from user; 和 drop table user; 有什么区别：

delete from user; 把person表中的所有数据全部删除，但是person的表还存在。
drop table user; 把person数据表从数据库中删除。

6.5数据记录增删改小结
新增：
insert into 表名 values(值，值，值…..)
insert into 表名(列名1，列名2，列名3….) values(值1，值2，值3…..)
insert into 表名(列名2，列名4，列名5….) values(值2，值4，值5…..)

修改：
update 表名 set 列名=值，列名=值 where 条件

删除：
delete from 表名 where 条件
如果不加where 条件，就是删除所有的数据。

删除：清空数据
truncate table 表名
通过删除整张表之后再重新创建一张表来达到清空数据的目的。

delete 和 truncate的区别是delete删除的数据在事务管理的情况下还能恢复，而truncate则不能恢复。

3今日学习目标 :
能够使用SQL语句查询数据
能够使用SQL语句进行条件查询
能够使用SQL语句进行排序
能够使用集合函数
能够使用SQL语句进行分组查询
能够完成数据的备份和恢复
能够使用可视化工具连接数据库, 操作数据库
能够说出多表之间的关系及建表
能够理解外键约束

Mysql
学习目标：
1、数据表记录的查询: 运算符、虑重、列运算、别名、排序、聚合函数、分组
2、数据库备份
3、多表设计：一对多、外键约束、多对多、一对一
4Sql可视化工具
可视化工具的作用：方法数据的操作和查看。
一般的可视化工具，都能从界面看到数据库-》数据库表-》表数据

连接可视化工具可能出现的问题：

1.数据记录的查询—(重点)

在企业中数据库运用最多的就是数据库中对于表记录的查询。
数据准备：
create table exam(
id int primary key auto_increment,
name varchar(20) not null,
chinese double,
math double,
english double
);
insert into exam values(null,’关羽’,85,76,60);
insert into exam values(null,’张飞’,70,75,70);
insert into exam values(null,’赵云’,90,65,95);
insert into exam values(null,’刘备’,97,50,50);
insert into exam values(null,’曹操’,90,89,80);
insert into exam values(null,’司马懿’,90,67,65);

1.1.语法一：查询所有信息
语法：select * from 表名；
表示查询表的所有信息。会把表的所有的列，所有的行都列出来。
【示例】
查询表中所有学生的信息。
语法：select * from 表名;

1.2.语法二：查询指定列的信息
语法： select 列名,列名… from 表名;
显示指定列数据。列出所有行信息。
【示例】

需求：查询表中所有学生的姓名和对应的英语成绩

1.3.语法三：条件查询
语法：select * from 表名 where 条件; 说明: * 可以指定具体列名.

查找符合where条件的数据。

作用：过滤，只有符合条件的，才去列出相应的信息。
【示例】：
查询姓名为赵云的学生成绩

1.4.运算符

比较运算符 > < <= >= = <> 大于、小于、大于/小于等于、不等于
between 1 and 10 显示某一区间的值:1—10之间
in(1,2,3) 显示在in列表中的值：1、2、3任意一个
Like ‘张_’
Like ‘张%’ 模糊查询：%表示零或任意多个字符，_表示一个字符.
例子1：张三丰 like ‘张%’
例子2：张三 like ‘张_’
is null、is not null 是否为空

逻辑运算符 and && 多个条件同时成立
or || 多个条件任意一个成立
not ! 不成立，例如：where not(age>18)

1)> < <= >= = <>

【示例1】
查询英语成绩大于90分的同学

【示例2】
查询英语分数不等于70分的所有同学

2)between
【示例】
查询英语分数在 80－90之间的同学(包含80和90)。

3)in
【示例】
查询数学分数为89,75,91的同学。

4)like

先插入一条记录：insert into exam values(null,’刘阿斗’,86,null,83);

【示例1】
查询所有姓刘的学生成绩。

【示例2】
查询所有姓刘两个字的学生成绩。

5)is null 和is not null

【示例1】
查询数学成绩不为null的学生

【示例2】
查询数学成绩为null的学生.

6)and 、or和not

【示例1】
查询数学分>80并且语文分>80的同学。

【示例2】
查询数学分>80 或者语文分>80的同学。

【示例3】
查询英语分数不大于60的学生

1.5.※虑重
查询排重：select distinct 列名 from 表名 [where 条件];

distinct：去重复。显示distinct后面列的内容，并且虑重。

【举例1】
select distinct 列1 from table;

结果：

【举例2】
select distinct 列1,列2 from table;

结果：

【示例】
过滤掉重复的语文成绩.

1.6.别名以及列运算
语法：select 列名 as 别名,列名 as 别名,列名 as 别名…. from 表名 [where 条件];

可以为列或者列进行运算后的结果起一个别名。

注意：数据库表中的原始数据不会改变。

【示例1】
写法：select 列名 as 别名 from 表名

【示例2】
as 也可以省略。
select 列名别名 from 表名

【示例3】
查询在所有学生的分数，在显示的时候每门课加10分特长分。（每一门课程都加10分）

【示例4】
查询每个学生的总分。

1.7.order by 排序
使用order by 子句排序查询结果。
语法：select * from 表名 order by 列名 asc|desc ;
asc是升序排列，desc是降序排列

注意：select语句关键字的顺序。
select …. from … where … order by …

注意 : 可以有多个排序条件, 首先按照第一列进行排序，如果第一列相同，按照第二列再进行排序。

默认是asc升序。

【示例1】
对语文成绩升序排序后输出。

【示例2】
对语文升序排序，如果语文成绩一样，按数学成绩降序排序。

【示例3】
对总分排序按从高到低降序输出

1.8.关于null的问题
如果使用null来进行表达式运算，那么计算的结果也会是null.

【示例1】
显示所有学生的姓名和总成绩；

mysql中，提供了一个函数：
ifnull(列名，默认值)
判断该列是否为null,如果为null，返回默认值，如果不为null，返回实际的值
例子：
ifnull(null,2) -> 2
ifnull(3,2) -> 3

【示例2】
查询显示学生姓名和总成绩。

【示例3】
对姓刘的学生成绩总分进行降序排序

null 与其它值进行运算的时候，结果还是null.

1.9.SQL中的聚合 / 聚集函数

聚集函数：多个数据进行运算，运算出一个结果。例如，求和，平均值，最大值，最小值。

SQL语言中定义了部分的函数，可以帮助我们完成对查询结果的计算操作：

1.9.1.count函数–统计记录数(统计行数)
语法：select count(*) | count(列名) from 表名

count（列名）统计该列有多少行。如果该列中有值为null的行，该行不做统计。
按照列去统计有多少行数据。

select count(列名) from 表名:按列来进行统计

注意： count在根据指定的列统计的时候，如果这一列中有null 不会被统计在其中。

select count(*) from 表名: 统计表中的行数。

【示例1】
统计一个班级共有多少学生？

或者

【示例2】
统计语文成绩大于等于90的学生有多少个？

【示例3】
统计总分大于250的人数有多少？

1.9.2.※sum求和函数
语法：select sum(列名) from 表名;

sum(列名) ——统计该列的所有的值的和。sum 也可排除null
select sum(列名) from 表名 where 条件

【示例1】
统计一个班级数学总成绩？

【示例2】
分别显示一个班级语文、英语、数学各科的总成绩

【示例3】
统计一个班级语文、英语、数学的成绩总和。

注意：这种写法会先将每一行的语数外成绩进行相加，然后再把每一行的语数外相加后的值进行求和。
这样写会出现一个问题，因为null值和任何值相加为null,导致在进行刘阿斗的语数外相加的时候，刘阿斗的总成绩就变成了null。
而最后sum求和的时候，就把刘阿斗的总成绩null给排除，因此总成绩会缺少刘阿斗的语文成绩和英语成绩。
解决方案：先对列进行求和，再进行语数外的相加
如下：

【示例4】
统计一个班级语文成绩平均分

注意：sum仅对数值类型的列起作用，否则会报错。

需求 : 这个结果好像不太完美，因为小数点后保留了多位小数，因此需要截取小数？
语法：round(数值, 小数的位数); 实现四舍五入的结果.

1.9.3.avg函数–平均值
语法： select avg(列名) from 表名;

avg(列名) —-求该列的平均值—-avg里面的null不作为统计

【示例1】
求一个班级数学平均分？

以上由于刘阿斗的math是null,因此，在平均的时候平均的是6个人的成绩，刘阿斗不算在内，而这样的平均方式显然是错误的，因此在算平均分的时候，要先进行ifnull的判断，在进行avg的运算。
sql语句修改如下：

【示例2】
求一个班级总分平均分
注意：不能和sum一起使用,并且要对每一列先进行ifnull的判断，如果不进行ifnull的判断的话会缺少刘阿斗的那一行的成绩，结果会变成总共7个人，结果只把除刘阿斗以外的所有人的成绩进行相加然后除以6。而准确结果应该是所有人的成绩相加除以7才对。

1.9.4.max/min 最大值/最小值
max(列) /min（列）统计该列的最大值或者最小值

select max(列名),min(列名) from 表名

—null 排除在外。

【示例1】
统计英语的最高分和最低分

【示例2】
统计总分的最高分和最低分
注意：不能和sum一起使用

1.10.group by分组查询 (重点)
分组：按照某一列或者某几列。把相同的数据，进行合并输出。
完整写法 : select … from … group by 列名;
按照某一列进行分组：
目的：仍然是统计使用。

说明：其实就是按列进行分类，然后可以对分类完的数据使用聚集函数进行运算。

注意事项：
1、聚合函数：分组之后进行计算；
2、通常 select的内容：a 被分组的列，b 聚合函数。
3、如果遇到这种情况按照每种，每个。类似的这些语句的时候，通常会使用分组。
4、如果使用group by 对数据进行分组之后还要过滤。这时一般不能使用where，因为where关键字的后面不能跟上面讲解的这些函数。如果需要在过滤的条件中加上述的函数，只能使用having关键字。
5、where 后不能跟聚合函数，having中可以跟聚合函数。

数据准备：
create table orders(
id int,
product varchar(20),
price float
);

insert into orders(id,product,price) values(1,’纸巾’,16);
insert into orders(id,product,price) values(2,’纸巾’,16);
insert into orders(id,product,price) values(3,’红牛’,5);
insert into orders(id,product,price) values(4,’洗衣粉’,60);
insert into orders(id,product,price) values(5,’苹果’,8);
insert into orders(id,product,price) values(6,’洗衣粉’,60);

【示例1】
查询购买的每种商品的总价。

先按照product进行分组，分组完成之后再给每一组进行求和。

【示例2】
查询每一种商品的总价大于30的商品，并显示总价。
如果分组之后，需要一些条件。则使用having 条件.，表示分组之后的条件。可以书写聚集函数。

以上函数的分析：

group by 列名 having 条件
分组之后加过滤条件。

where 和 having 的区别。

1、having 通常与group by 分组结合使用。 where 和分组无关。

2、having 可以书写聚合函数 (聚集函数出现的位置： select 之后，group by … having 之后)
例如having中的聚集函数（count，sum，avg，max，min），是不可以出现where条件中。

3、where 是在分组之前进行过滤的。having 是在分组之后进行过滤的。

说明 : select … from … where 条件1 … gropu by … having 条件2 order by

条件1 会先执行过滤
进行分组
条件2进行过滤

1.11.select 语句的执行顺序与查询小结 (重点)
查询关键字的出现的顺序是固定的

select …要显示的内容.. from …表名.. where 条件…. group by …分组的列 …having …分组后的条件… order by …排序

select …4… from …1.. where …2.. group by ..3…having ..5… order by …6.

查询的执行顺序。

from : 表名
where：条件过滤
group by : 分组
having ：分组之后进行过滤。
select ：执行完毕之后，查询内容。
order by ：排序输出显示.

查询总结：
from 表名

where 条件：
逻辑运算符：and or not
比较运算符：< > <= >= <> =
在….之间：between…and…
in(set)：任意set集合中的一个条件成立即可
is null/is not null
模糊匹配：like % _

group by 列：对列进行分组。

having 条件：
逻辑运算符：and or not
比较运算符：< > <= >= <> =
在….之间：between…and…
int(set)
is null/is not null
模糊匹配：like % _
聚合函数（sum,avg,max,min,count）
别名

order by desc/asc

2.数据库的备份和恢复

备份：把数据库里面的内容进行备份放到硬盘或者其他位置。如果数据库出现问题之后，可以进行数据的恢复。

2.1.数据库备份
【示例】
将某个数据库备份到指定的路径进行保存。

在mysql的安装目录的bin目录下有mysqldump命令，可以完成对数据库的备份。
语法：mysqldump -u 用户名 -p 数据库名 > 磁盘SQL文件路径
由于mysqldump命令不是sql命令，需要在dos窗口下使用。

在dos命令行窗口 :

2.2.数据库恢复方式一
步骤一：创建数据库
注意：备份的时候，没有备份创建数据库的语句。当需要恢复某个具体的数据库时候，需要手动的创建数据库。
手动创建数据库,并切换到该数据库

步骤二：导入数据
将备份的数据库导入进来

2.3.※数据库恢复方式二
步骤一：创建数据库

步骤二：
在dos命令行中输入。
语法：mysql -u 用户名 -p 导入库名 < 硬盘SQL文件绝对路径
注意：在cmd下使用，不是登录mysql后使用，和备份比较类似，只不过mysql后面不带dump，并且箭头是<,指向需要导入数据的新的数据库。
这种恢复的方式，也需要数据库提前存在。

3.多表设计
表与表之间可以总结出如下关系：

3.1.一对多
【案例】
表设计：记录部门中的员工信息
需求分析：
员工表和部门表
分析实体类的属性
分析实体类之间的关系
分析外键如何设置

步骤一：建表
创建数据库，并切换到该数据库

在创建的数据库中建表
create table dept(
id int primary key auto_increment,
name varchar(20)
);
create table employee(
id int primary key auto_increment,
name varchar(20),
age int ,
salary double
);

步骤二：添加数据
/给部门表添加数据/

insert into dept values(null,’人事部’);
insert into dept values(null,’财务部’);
insert into dept values(null,’公关部’);
insert into dept values(null,’总经理办公室’);

/给雇员表添加数据/

insert into employee values(null,’小乔’,18,10000);
insert into employee values(null,’大乔’,19,10000);
insert into employee values(null,’曹操’,20,12000);
insert into employee values(null,’周瑜’,21,13000);
insert into employee values(null,’刘备’,22,14000);

步骤三：添加关系
分析：部门表和员工表目前都是独立的存在，无法知道这些员工是属于哪个部门，因此员工表内的信息还存在着一些缺陷。
解决方案：可以在员工表中添加一列部门编号用来标识设置该员工所属的部门，如下：

通过上图分析，可以在多方表中添加一列来设置一对多的关系。
/employee表中添加一个dept_id列/
alter table employee add dept_id int;

update employee set dept_id =1 where id = 1;
update employee set dept_id =1 where id = 2;
update employee set dept_id =2 where id = 3;
update employee set dept_id =3 where id = 4;
update employee set dept_id =4 where id = 5;
结果：

3.2.外键约束
需求：人事部解散，将人事部删除。
【示例】
在部门表中删除关于人事部的记录
删除人事部的记录

分析：如果一个部门被删除，那么该部门中的工作人员就应该另有安排，不应该还挂在人事部下。所以，在删除一个部门之前，应该先对原人事部员工进行安排，而不应该随便就能删除一个部门。因此需要给删除一个部门添加一些限制，比如，在没有安排原人事部员工之前，无法删除部门。

以上给删除部门添加的限制，就可以称之为 外键约束。

部门表和员工表之间的关系
主表：被引用字段的那个表—部门表（一方）
从表：引入字段的表 — 员工表(多方)

添加外键需要注意的问题
如果从表要去添加一个外键约束。要求主表被引用的字段是主键或者唯一的。通常使用主键。
如果要删除主表中的数据。要求在从表中这个数据，要没有被引用，才可以去删除。
如果要向从表中去添加数据。要求在主表中，要有对应的数据。才可以去添加。
如果要删除表。要先删除从表。然后去删除主表。
新建表的时候。需要先去创建主表，然后去创建从表。

作用：保持数据的完整性，和有效性。

3.2.1.建表之后添加外键约束

语法 : alter table 从表名称 add foreign key (外键列的名称) references 主表名称(主键)
【示例】
给现成的dept表和employee表添加外键约束。

添加外键约束
报错如下：

原因分析
人事部已经在dept表中被删除，因此无法使用人事部的主键作为employee中的外键。

解决方案：
1、删除从表中的数据（删除大小乔）
2、修改数据（将大小乔的dept_id修改为dept表中存在的主键）
3、在dept表中添加主键为1的人事部。
以上任选其一即可。
这里使用第三种方案，重新插入一条人事部的记录，主键必须为1。

测试：再次删除人事部看其效果。

3.2.2.建表的时候添加外键约束
外键约束也可以在建表的时候就进行设置
/创建部门表/
create table dept(
id int primary key auto_increment,
name varchar(20)
);
/创建员工表/
create table employee(
id int primary key auto_increment,
name varchar(20),
age int ,
salary double,
dept_id int,
foreign key (dept_id) references dept(id)
);

【示例】
新增一个mydb3的数据库
在该数据中创建dept表和employee表，并且在建表是就设置外键约束。

步骤一：创建mydb3数据库

步骤二：创建dept表
create table dept(
id int primary key auto_increment,
name varchar(20)
);

步骤三：创建employee表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int ,
salary double,
dept_id int,
foreign key (dept_id) references dept(id)
);

注意：在建表时，必须先创建dept表，再创建employee表，因为在employee表中设置外键约束时引用了dept中的主键，如果dept表都还没创建的话，结果肯定是外键约束添加失败。

如果先创建employee表会报错如下：

/给部门表添加数据/

insert into dept values(null,’人事部’);
insert into dept values(null,’财务部’);
insert into dept values(null,’公关部’);
insert into dept values(null,’总经理办公室’);

/给雇员表添加数据/

insert into employee values(null,’小乔’,18,10000,1);
insert into employee values(null,’大乔’,19,10000,1);
insert into employee values(null,’曹操’,20,12000,2);
insert into employee values(null,’周瑜’,21,13000,3);
insert into employee values(null,’刘备’,22,14000,4);

delete from dept where name = ‘人事部’;

3.3.多对多
分析：程序员写项目。
分析有几个实体类
分析实体类的属性
分析实体类之间的关系

表设计：

建表语句：
– 创建程序员表
create table coder (
id int primary key auto_increment,
name varchar(30),
salary double
);

– 创建项目表
create table project(
id int primary key auto_increment,
name varchar(100)
);

– 创建中间表
create table coder_project(
coder_id int,
project_id int,
foreign key (coder_id) references coder(id),
foreign key (project_id) references project(id)
);

drop table coder_project;
drop table coder;
drop table project;

注意：在创建中间表之前，必须要先把coder表和project表创建出来。
由于外键约束的关系，在没有去掉中间表的外键关系之前，删除coder表或者project表中的外键相关记录都无法删除。

3.4.一对一

需求分析：人员信息和个人档案

分析实体类的属性
分析实体类之间的关系
分析外键如何设置

表设计：

【练习】
创建person表和record表，设置外键约束。

4.课程小结
库的操作
创建库：create database 库名 character set 编码表;
删除库：drop database 库名;
查询库：show databases;
查看库的编码表：show create database 库名;
更改库：use 库名;
查看当前正在使用的库：select database();
修改库的编码表：alter database 库名 character set 编码表;

表本身的操作
创建表：create table 表名( 列名列的类型(长度) 类的约束 ,列名列的类型(长度) 类的约束…… );
删除表：drop table 表名;
查询表：show tables;
查看表的结构：desc 表名;
查看表的编码表：show create table 表名;
修改表：alter table 表名增/删/改列名列的类型(长度) 约束;
add/drop/change/modify
修改表名：rename table 旧表名 to 新表名；

表中数据的操作
增：insert into 表名(列名) values(值);
删：delete from 表名 where 条件; truncate
改：update 表名 set 列名=值，列名=值 where 条件；
查：select 列名 as 别名 ,列名 as 别名… from 表名 where 条件 group by 列名 having 条件 order by 排序.
查询排重：select distinct 列名 from 表名 where 条件;

聚合函数：
count 统计个数、sum求和、avg 平均值、max、min
在使用这几个函数进行数据的统计分析时，有时需要对数据表中的列进行数据的分组处理。group by

分组 group by :

排序：order by 列名 asc | desc；

5今日学习目标 :
能够使用内连接进行多表查询
能够使用外连接进行多表查询
能够使用子查询进行多表查询

Mysql
学习内容：
1、多表查询：内连接查询、外连接查询、子查询
2、强化练习

1.复习
库的操作
创建库：create database 库名 character set 编码表;
删除库：drop database 库名;
查询库：show databases;
查看库的编码表：show create database 库名;
更改库：use 库名;
查看当前正在使用的库：select database();
修改库的编码表：alter database 库名 character set 编码表;

表中数据的操作
增：insert into 表名(列名) values(值);
删：delete from 表名 where 条件; truncate
改：update 表名 set 列名=值，列名=值 where 条件；
查：select 列名 as 别名 ,列名 as 别名… from 表名 where 条件；
查询排重：select distinct 列名 from 表名 where 条件;

分组 group by :

排序：order by 列名 asc | desc；

2.多表设计回顾

设计学生成绩管理系统
业务需求：
1、一个学生可以选择多门课程
2、一个课程可以有多个学生学习
3、每个学生的每门课程必须有成绩
4、一个老师可以教多门课程
5、一门课程只有一个老师来教

分析：有几个名词，大致可以分为几张表，表有哪些属性，表和表之间的关系。

表设计：

步骤一：创建表
create table student(
id int primary key auto_increment,
name varchar(20),
city varchar(10),
age int
);

create table teacher(
id int primary key auto_increment,
name varchar(20)
);

create table course(
id int primary key auto_increment,
name varchar(20),
teacher_id int,
foreign key (teacher_id) references teacher(id)
);

create table student_course(
student_id int,
course_id int,
score int,
foreign key (student_id) references student(id),
foreign key (course_id) references course(id)
);

步骤二：插入数据
insert into teacher values(null,’关羽’);
insert into teacher values(null,’张飞’);
insert into teacher values(null,’赵云’);

insert into student values(null,’小王’,’北京’,20);
insert into student values(null,’小李’,’上海’,18);
insert into student values(null,’小周’,’北京’,22);
insert into student values(null,’小刘’,’北京’,21);
insert into student values(null,’小张’,’上海’,22);
insert into student values(null,’小赵’,’北京’,17);
insert into student values(null,’小蒋’,’上海’,23);
insert into student values(null,’小韩’,’北京’,25);
insert into student values(null,’小魏’,’上海’,25);
insert into student values(null,’小明’,’北京’,20);

insert into course values(null,’语文’,1);
insert into course values(null,’数学’,1);
insert into course values(null,’生物’,2);
insert into course values(null,’化学’,2);
insert into course values(null,’物理’,2);
insert into course values(null,’英语’,3);

insert into student_course values(1,1,80);
insert into student_course values(1,2,90);
insert into student_course values(1,3,85);
insert into student_course values(1,4,78);
insert into student_course values(2,2,53);
insert into student_course values(2,3,77);
insert into student_course values(2,5,80);
insert into student_course values(3,1,71);
insert into student_course values(3,2,70);
insert into student_course values(3,4,80);
insert into student_course values(3,5,65);
insert into student_course values(3,6,75);
insert into student_course values(4,2,90);
insert into student_course values(4,3,80);
insert into student_course values(4,4,70);
insert into student_course values(4,6,95);
insert into student_course values(5,1,60);
insert into student_course values(5,2,70);
insert into student_course values(5,5,80);
insert into student_course values(5,6,69);
insert into student_course values(6,1,76);
insert into student_course values(6,2,88);
insert into student_course values(6,3,87);
insert into student_course values(7,4,80);
insert into student_course values(8,2,71);
insert into student_course values(8,3,58);
insert into student_course values(8,5,68);
insert into student_course values(9,2,88);
insert into student_course values(10,1,77);
insert into student_course values(10,2,76);
insert into student_course values(10,3,80);
insert into student_course values(10,4,85);
insert into student_course values(10,5,83);

3.多表查询
在实际的工作中，我们所需要的数据，通常会来自多张表。那么就涉及到多表关联查询的问题。
也就是指，如何使用sql语句一次性查询多张表的数据。

【示例】
数据准备：
有两张表，一张是水果表A,一张是价格表B
建表：
create table A(
id int primary key auto_increment,
name varchar(20) not null
);
insert into A values(1,’苹果’);
insert into A values(2,’橘子’);
insert into A values(3,’香蕉’);

create table B(
id int primary key auto_increment,
price double
);
insert into B values(1,2.30);
insert into B values(2,3.50);
insert into B values(4,null);

3.1.笛卡尔积
需求：查询两张表中关于水果的信息，要显示水果名称和水果价格。
多表查询语法：select * from a,b;

测试结果：

问题分析：
查询到的结果冗余了，变成了排列组合。
a表中的每一条记录，都和b表中的每一条进行匹配连接。
所得到的最终结果是，a表中的条目数乘以b表中的数据的条目数。

解决方案：在查询两张表的同时添加条件进行过滤，比如a表的id和必须和b表的id相同.

3.2.内连接查询
内连接 : 通过两张表查找其对应的记录.

隐式内连接查询：
select * from a,b where a.列名 = b.列名
在产生两张表的笛卡尔积的数据后，通过条件筛选出正确的结果。

显式内连接查询

显示的内连接，一般称为内连接，有INNER JOIN，查询到的数据为两个表经过ON条件过滤后的笛卡尔积然后在通过where条件进行筛选

3.3.外连接
需求：列出所有的水果的信息和价格。
3.3.1.左外连接

select * from a left outer join b on a.id = b.id
把left 关键字之前的表，是定义为左侧。 left关键字之后的表，定义右侧。outer可以省略
查询的内容，以左侧的表为主，如果左侧有数据，右侧没有对应的数据，仍然会把左侧数据进行显示。

分析：

3.3.2.右外连接

select * from a right outer join b on a.id = b.id

右外连接分析：
如果右侧有数据，左侧没匹配到，把右侧的数据显示出来。outer可以省略
right之前的是左侧，right之后的是右侧。

分析：

总结：
左外连接以关键字左侧数据为主。不管右侧的数据是否有对应，都把左侧的数据显示出来
右外连接以关键字右侧数据为主，不管左侧有没有数据对应。都把右侧的数据进行显示。

3.3.3.全外连接
需求：查询所有的水果的信息，和价格的信息，

select * from a full outer join b on a.id = b.id

全外连接分析：
就是左外连接和右外连接之和，将左右两表中的数据全部显示出来
mysql 不支持全外连接。

可以使用union来达到全外连接的查询效果。
union ：可以将左外连接查询和右外连接查询两条sql语句使用union合并起来进行查询，去掉重复的数据。

【示例】

union all :不去掉重复进行合并,相当于查询一次左外连接，然后再查询一次右外连接，然后将两次的查询结果合并。

3.4.多表查询总结

内连接：
1、隐式内连接：
Select * from a,b where a.id = b.id;
结果：C
2、显示内连接：
Select * from a inner join b on a.id = b.id;
结果：C

外连接：
1、左外连接
select * from a left outer join b on a.id = b.id
结果：A+C
2、右外连接
select * from a right outer join b on a.id = b.id
结果：B+C
3、union:相当于全外连接
select * from a left outer join b on a.id = b.id
union
select * from a right outer join b on a.id = b.id
结果：A+B+C,会自动虑重

select * from a left outer join b on a.id = b.id
union all
select * from a right outer join b on a.id = b.id
结果：A+B+C,有重复数据

3.5.5关联子查询
关联子查询：将一个查询作为另一个查询的一部分。
3.5.1.子查询
需求：查询年龄最大的学生的信息.
查什么？
最大的年龄和学生的信息

查询出最大的年龄是多少.

根据最大的年龄，去查询出具体的人员信息

组合成一条语句.
将查询最大年龄的语句作为一个条件，然后根据这个条件来查询这个学生的信息。

3.5.2.in的用法
in (20，30)：表示条件是20或者30，类似于添加条件select * from student where age = 20 or age = 30;

注意 : 不可以使用 id = …; 语句, 因为 id = 之后只能存放一个值, in 可以表示多个值取其中一个.

【示例】
需求：查询分数不及格的所有的学生信息

3.5.3.all的用法-了解
all：表示所有，和union一起使用。
左连接和右连接查询结果的合集。

union all :不去掉重复进行合并,相当于查询一次左外连接，然后再查询一次右外连接，然后将两次的查询结果合并。

all 表示所有。
a >all(1,2,3,4) 相当于a >1 and a >2 and a>3 and a >4 或者相当于 a > max(1,2,3,4);

业务需求：查询年龄最大的学生的信息。
查询什么？
1、查询最大的年龄是多少
2、通过年龄查询学生的信息
【示例】

步骤一：查询最大的年龄

步骤二：通过年龄查询学生的信息

步骤三：使用all代替max来进行查询

3.5.4.any和some(了解)

any : 表示任何一个
查询部分数据
a > any(1,3,5,6) 相当于 a>1 相当于 a > min(1,3,5,6);

a=any(1,3,5,6) 相当于 a in(1,3,5,6) 或者 a=1 or a=3 or a=5 or a=6

注意：any的后面需要跟语句，不能直接放值。

【示例】
查询成绩是90的学生的信息
分析：
1、查询成绩是90分的学生的id
2、通过学生的id查询学生的信息

步骤一：使用in查询成绩是90分的学生信息

步骤二：使用any查询

some：表示任何一个和any的作用相同
some 是 SQL-92标准的ANY的等效物
【示例需求同上】

3.5.5.as的用法 (重点)
as:不仅可以用来做列的别名，还可以将查询结果通过as作为一张表来使用。
【示例】
需求 : 查询不及格的学生信息和不及格分数

3.5.6.子查询小练习
业务需求：查询数学成绩比语文成绩高的所有学生信息.
查询什么？

3.6.mysql自带函数 (知道即可)
msyql数据库中自带了一些函数。在我们使用sql语句操作mysql数据库的时候，可以直接使用这些函数。

日期函数

4.SQL强化练习
4.1.学生选课信息查询
teacher 教师表
student 学生表
course 课程表
student_cource 选课表学生和课程的关系表

根据上述的4张表练习：

5.1.14.1.2 limit的用法-了解
作用 : 限制查询结果返回的数量.

语法：select * from 表名 LIMIT [offset], row_count;

注意 : 记录是从 0 开始编号的.

1查询平均成绩大于70分的同学的学号和平均成绩
分析：
1、先按学号进行分组
2、分组后添加条件成绩>70分

2查询所有同学的学号、姓名、选课数、总成绩

3查询学过赵云老师所教课的同学的学号、姓名

注意 : 一个老师可以教授多门课程, 因此可以使用 in.

4查询没学过关羽老师课的同学的学号、姓名

5查询学三门课以下的同学的学号、姓名

6查询各科成绩最高和最低的分

7查询学生信息和平均成绩

8查询各个城市的学生数量

9查询不及格的学生信息和课程信息

10统计每门课程的学生选修人数（超过四人的进行统计）

4.2.部门员工查询练习

create table dept(
deptno int primary key auto_increment,
dname varchar(14) ,
loc varchar(13)
) ;

create table emp(
empno int primary key auto_increment,
ename varchar(10),
JOB varchar(9),
MGR int,
HIREDATE date,
SAL int,
COMM int,
DEPTNO int not null,
foreign key (DEPTNO) references dept(deptno)
);

INSERT INTO dept VALUES(10,’ACCOUNTING’,’NEW YORK’);
INSERT INTO dept VALUES(20,’RESEARCH’,’DALLAS’);
INSERT INTO dept VALUES(30,’SALES’,’CHICAGO’);
INSERT INTO dept VALUES(40,’OPERATIONS’,’BOSTON’);

INSERT INTO emp VALUES(7369,’SMITH’,’CLERK’,7902,’1980-12-17’,800,NULL,20);
INSERT INTO emp VALUES(7499,’ALLEN’,’SALESMAN’,7698,’1981-02-20’,1600,300,30);
INSERT INTO emp VALUES(7521,’WARD’,’SALESMAN’,7698,’1981-02-22’,1250,500,30);
INSERT INTO emp VALUES(7566,’JONES’,’MANAGER’,7839,’1981-04-02’,2975,NULL,20);
INSERT INTO emp VALUES(7654,’MARTIN’,’SALESMAN’,7698,’1981-09-28’,1250,1400,30);
INSERT INTO emp VALUES(7698,’BLAKE’,’MANAGER’,7839,’1981-05-01’,2850,NULL,30);
INSERT INTO emp VALUES(7782,’CLARK’,’MANAGER’,7839,’1981-06-09’,2450,NULL,10);
INSERT INTO emp VALUES(7788,’SCOTT’,’ANALYST’,7566,’1987-06-13’,3000,NULL,20);
INSERT INTO emp VALUES(7839,’KING’,’PRESIDENT’,NULL,’1981-11-17’,5000,NULL,10);
INSERT INTO emp VALUES(7844,’TURNER’,’SALESMAN’,7698,’1981-09-08’,1500,0,30);
INSERT INTO emp VALUES(7876,’ADAMS’,’CLERK’,7788,’1987-06-13’,1100,NULL,20);
INSERT INTO emp VALUES(7900,’JAMES’,’CLERK’,7698,’1981-12-03’,950,NULL,30);
INSERT INTO emp VALUES(7902,’FORD’,’ANALYST’,7566,’1981-12-03’,3000,NULL,20);
INSERT INTO emp VALUES(7934,’MILLER’,’CLERK’,7782,’1983-01-23’,1300,NULL,10);

1.列出至少有4个员工的部门的信息。

2.列出薪金比“SMITH”多的所有员工。

3.列出所有员工的姓名及其直接上级的姓名。
提示：可以将一张表当成两张表来使用

4.列出受雇日期早于其直接上级的所有员工。

5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

6.列出所有“CLERK”（办事员）的姓名及其部门名称。

7.列出最低薪金大于1500的各种工作。

8.列出在部门“SALES”工作的员工的姓名，假定不知道销售部的部门编号。

9.列出薪金高于公司平均薪金的所有员工。

10.列出与“SCOTT”从事相同工作的所有员工。

11.列出薪金等于部门30中任意一个员工的薪金的所有员工的姓名和薪金。

12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

13.列出在每个部门工作的员工数量、平均工资。

14.列出所有员工的姓名、部门名称和工资。

15.列出所有部门的详细信息和部门人数。

16.列出各种工作的最低工资。

17.列出各个部门的MANAGER（经理）的最低薪金。

18.列出所有员工的年工资,按年薪从低到高排序。

19.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。

20.查询出所有薪水在’ALLEN’之上的所有人员信息。

21.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号员工名字薪水

22.查询出emp表中所有的工作种类（无重复）

23.查询出所有奖金（comm）字段不为空的人员的所有信息。

24.查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）

25.查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）

26.查询出名字中有“A”字符，并且薪水在1000以上（不包括1000）的所有员工信息。

27.查询出名字第三个字母是“M”的所有员工信息。

28.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。

29.查询出最早工作的那个人的名字、入职时间和薪水。

30.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.

31.显示出薪水最高人的职位。

32.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。

33.删除10号部门薪水最高的员工

delete from emp where sal (select * from (select max(sal) from emp where deptno = 10) as t1);

将红色语句作为临时表, 才能执行成功!

34.将薪水最高的员工的薪水降30%

35.查询员工姓名，工资和工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)

1.JDBC 入门
前面的两天我们学习了通过sql语句如何去操作数据库。而我们学习的Java技术能不能操作数据库呢？如何通过Java代码去操作数据库呢？

5.22. 驱动程序的说明 :

什么是驱动 ? 就是 JDBC 接口的实现类.

驱动程序 : 其实就是数据库各厂商对JDBC中定义接口的具体实现类. 加载驱动就是将接口的具体实现类代码加载到我们的Java程序中进行使用.

JDBC（Java DataBase Connectivity,java数据库连接）是一种用于执行SQL语句的Java API。JDBC是Java访问数据库的标准规范，可以为不同的关系型数据库提供统一访问，它由一组用Java语言编写的接口和类组成。

JDBC与数据库驱动的关系：接口与实现类的关系。

Java提供访问数据库规范称为JDBC，而生产厂商提供规范的实现类称为驱动。

JDBC是接口，驱动是接口的实现，没有驱动将无法完成数据库连接，从而不能操作数据库！每个数据库厂商都需要提供自己的驱动，用来连接自己公司的数据库，也就是说驱动一般都由数据库生成厂商提供。

3.JDBC的常用类和接口
JDBC有关的类：都在java.sql 和 javax.sql 包下.
接口在Java中是用来定义 行为规范的. 接口必须有实现类.

JDBC规范（掌握四个核心对象）：
DriverManager:用于注册驱动
Connection: 表示与数据库创建的连接
Statement: 操作数据库sql语句的对象
ResultSet: 结果集或一张虚拟表

5.34. JDBC编程快速入门 (初体验)
1、搭建数据库环境
运行 services.msc 检查mysql 是否启动
运行 cmd> mysql -u root -p 连接mysql 数据库
创建数据库 day04 —- create database day04;
切换到操作数据库 — use day04;

– 创建数据表 user
create table user(
id int primary key auto_increment,
username varchar(20) unique not null,
password varchar(20) not null,
email varchar(40) not null
);

– 插入一些数据记录
insert into user values(null,’zhangsan’,’123’,’[email protected]’);
insert into user values(null,’lisi’,’123’,’[email protected]’);
insert into user values(null,’wangwu’,’123’,’[email protected]’);

2、创建Java project day04
将 mysql 驱动复制 day04/ lib
下载链接地址 : https://dev.mysql.com/downloads/connector/j/

3、JDBC编程步骤
步骤一装载驱动 DriverManager.registerDriver(new Driver());
步骤二建立连接 Connection conn = DriverManager.getConnection(“jdbc:mysql://localhost:3306/day04”, “root”, “abc”);
步骤三操作数据SQL 对于结果集处理
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(“username”));
System.out.println(rs.getString(“email”));
}

步骤四释放资源
rs.close();
statement.close();
conn.close();
Statement对象对数据库操作分为两大部分:
1调用executeUpdate(sql语句)方法对数据库进行增删改的操作,返回值为int类型一个数,因为此方法操作的只是其中的一条数据.
2.调用executeQuery(sql)方法对数据库进行查询的操作.如果是查询操作,返回的是一个结果集合,集合中存放的对应着数据库里每一个数据对象.
@Test
public void demo01() throws SQLException {
// 1. 装载驱动
DriverManager.registerDriver(new Driver());
// 2. 建立连接
Connection conn = DriverManager.getConnection(“jdbc:mysql://localhost:3306/day04”, “root”, “123”);
// 3. 操作数据
String sql = “select * from user;”;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
int id = rs.getInt(“id”);
String username = rs.getString(“username”);
String password = rs.getString(“password”);
String email = rs.getString(“email”);
System.out.println(id + ” : ” + username + ” : ” + password + ” : ” + email);
}
// 4. 释放资源
rs.close();
stmt.close();
conn.close();
}

5.45. JDBC API 详解
1、 DriverManager 类
static void registerDriver(Driver driver) 注册一个JDBC驱动程序
注意：DriverManager中可以同时注册多个JDBC驱动例如：同时注册 mysql、oralce、db2 驱动，通过对JDBC URL分析，决定采用哪个驱动
static Connection getConnection(String url, String user, String password) 根据jdbc url 和用户名、密码获得一个数据库连接

Jdbc程序中的DriverManager用于加载驱动，并创建与数据库的链接，这个API的常用方法：
DriverManager.registerDriver(new Driver())
DriverManager.getConnection(url, user, password)，

When a Driver class is loaded, it should create an instance of itself and register it with the DriverManager. This means that a user can load and register a driver by doing Class.forName(“foo.bah.Driver”)

注意：在实际开发中并不推荐采用registerDriver方法注册驱动。原因：
一、查看Driver的源代码可以看到，如果采用此种方式，会导致驱动程序注册两次，也就是在内存中会有两个Driver对象。
二、程序依赖mysql的api，脱离mysql的jar包，程序将无法编译，将来程序切换底层数据库将会非常麻烦。//需要导入mysql包.

推荐方式：Class.forName(“com.mysql.jdbc.Driver”);
采用此种方式不会导致驱动对象在内存中重复出现，并且采用此种方式，程序仅仅只需要一个字符串，不需要依赖具体的驱动，使程序的灵活性更高。

同样，在开发中也不建议采用具体的驱动类型指向getConnection方法返回的connection对象,后期将会推荐从封装好的c3p0连接池中取出.

2、JDBC URL
jdbc:mysql://localhost:3306/day04
这里 jdbc: 是JDBC连接协议
这里 mysql:// 是mysql数据库连接协议，JDBC子协议
localhost:3306 主机和端口
day04 数据库

URL用于标识数据库的位置，程序员通过URL地址告诉JDBC程序连接哪个数据库，URL的写法为：

Mysql的url地址的简写形式： jdbc:mysql:///sid
常用属性：useUnicode=true&characterEncoding=UTF-8

MySQL 如果连接localhost:3306 可以省略
jdbc:mysql://localhost:3306/day04 ————— jdbc:mysql:///day04
JDBCURL 可以通过?和& 携带参数
常用属性：useUnicode=true&characterEncoding=UTF-8 ———– 解决操作数据库乱码问题

3、Connection 连接接口

getConnection 该方法会一个数据库连接对象.

应用一：获得SQL的操作对象
Statement conn.createStatement() 该对象可以将SQL发送给数据库进行执行
PreparedStatement conn.prepareStatement(sql) 对SQL语句进行预编译，防止SQL注入

应用二：对数据库事务进行管理
conn.setAutoCommit(boolean); 设置事务是否自动提交
conn.commit(); 提交数据库事务
conn.rollback(); 回滚数据库事务

Jdbc程序中的Connection，它用于代表数据库的链接，Collection是数据库编程中最重要的一个对象，客户端与数据库所有交互都是通过connection对象完成的，这个对象的常用方法：
createStatement()：创建向数据库发送sql的statement对象
prepareStatement(sql) ：创建向数据库发送预编译sql的PrepareSatement对象

setAutoCommit(boolean autoCommit)：设置事务是否自动提交。
commit() ：在连接上提交事务。 —与事务相关！！
rollback() ：在此连接上回滚事务。

4、Statement 用于将SQL 发送给数据库获得操作结果
发送单条SQL
executeUpdate 用于向数据库发送 insert update delete 语句，返回int 类型参数，代表影响记录行数
executeQuery 用于向数据库发送 select 语句，返回ResultSet 结果集对象
execute 用于数据库发送任何SQL语句（包括 DDL DML DCL）返回boolean ，SQL执行结果是ResultSet 返回true，否则 false

@Test
public void testUpdate_insert() throws SQLException, ClassNotFoundException {
    // 1. 装载驱动
    Class.forName("com.mysql.jdbc.Driver");
    // 2. 建立连接
    Connection conn = DriverManager.getConnection("jdbc:mysql:///day04", "root", "123");
    // 3. 操作数据
    String sql = "insert into user values(null,'zhaoliu','123','[email protected]');";
    Statement stmt = conn.createStatement();
    int affectedRowNum = stmt.executeUpdate(sql);
    System.out.println(affectedRowNum);
    // 4. 释放资源
    stmt.close();
    conn.close();
}






@Test
public void test_execute() throws SQLException, ClassNotFoundException {
    // 1. 装载驱动
    Class.forName("com.mysql.jdbc.Driver");
    // 2. 建立连接
    Connection conn = DriverManager.getConnection("jdbc:mysql:///day04", "root", "123");
    // 3. 操作数据
    // String sql = "update user set username = 'Jack', password = '888', email = '[email protected]' where id = 4;";
    String sql = "select * from user;";
    Statement stmt = conn.createStatement();
    boolean result = stmt.execute(sql);
    // 判断返回结果 result 的值
    if (result) {
        // select 语句
        ResultSet rs = stmt.getResultSet();
        while (rs.next()) {
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            String email = rs.getString("email");
            System.out.println(id + " : " + username + " : " + password + " : " + email);
        }
        rs.close();
    } else {
        // 非 select 语句
        int updateCount = stmt.getUpdateCount();
        System.out.println(updateCount);
    }
    // 4. 释放资源
    stmt.close();
    conn.close();
}

Statement 总结：
executeQuery(sql); sql语句只能是select语句，方法的返回值是ResultSet结果集。返回的是一个结果集合,集合中存放的对应着数据库里每一个数据对象.

executeUpdate(sql); sql语句不是select语句，方法的返回值是int类型，是sql语句影响的行数。
Execute(sql);sql可以是任何语句，返回值是boolean类型
true :说明sql语句是select语句，只能通过getResultSet()方法来获取select语句的结果集
* false:说明sql是非select语句，通过getUpdateCount()方法来获取sql影响的行数

4、使用ResultSet 遍历结果集
5、每次遍历rs.next得到的是数据库里每一个数据对象.要想获得里面的每一个属性,再用getString(String filed)方法获取.

while(rs.next()){
// 根据数据库内部列类型，选择相应 getXXX方法
int —- getInt
varchart —- getString
date —– getDate
}

getXXX 有两种写法第一种 getString(index) 结果集中列索引第二种 getString(列名) (常用的方法)

思考：如果SQL语句可能会返回一行数据，也可能查不到任何记录时，代码应该怎么写？ —– 用于登陆
if(rs.next()){
// 查到了数据
}else{
// 没有查到数据
}

Jdbc程序中的ResultSet用于代表Sql语句的执行结果。Resultset封装执行结果时，采用的类似于表格的方式。ResultSet 对象维护了一个指向表格数据行的游标cursor，初始的时候，游标在第一行之前，调用ResultSet.next() 方法，可以使游标指向具体的数据行，进而调用方法获取该行的数据。
ResultSet既然用于封装执行结果的，所以该对象提供的大部分方法都是用于获取数据的get方法：
获取任意类型的数据
getObject(int index)
getObject(string columnName)
获取指定类型的数据，例如：
getString(int index)
getString(String columnName)
提问：数据库中列的类型是varchar，获取该列的数据调用什么方法？Int类型呢？bigInt类型呢？Boolean类型？

7、释放资源，确保资源能够释放 —- 将代码放入finally 代码块

Jdbc程序运行完后，切记要释放程序在运行过程中，创建的那些与数据库进行交互的对象，这些对象通常是ResultSet, Statement和Connection对象。

特别是Connection对象，它是非常稀有的资源，用完后必须马上释放，如果Connection不能及时、正确的关闭，极易导致系统宕机。Connection的使用原则是尽量晚创建，尽量早的释放。

为确保资源释放代码能运行，资源释放代码也一定要放在finally语句中。

@Test
public void test_jdbc() {
    // 1. 装载驱动
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
        throw new RuntimeException("驱动加载失败!");
    }

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        // 2. 建立连接
        conn = DriverManager.getConnection("jdbc:mysql:///day04", "root", "123");
        // 3. 操作数据
        String sql = "select * from user;";
        stmt = conn.createStatement();
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            String email = rs.getString("email");
            System.out.println(id + " : " + username + " : " + password + " : " + email);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 4. 释放资源
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            stmt = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
}

5.56. JDBC的工具类抽取

编写对user表增删改查程序，从重复代码中提取公共方法 JDBCUtils 工具类，将数据库连接参数写入properties 配置文件

使用JDBC对数据库进行CRUD
Jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。

Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。

Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。

练习1：编写程序对User表进行增删改查操作。

@Test
public void test_insert() {
    // 1. 加载驱动
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
        throw new RuntimeException("驱动加载失败!");
    }

    Connection conn = null;
    Statement stmt = null;
    try {
        // 2. 建立连接
        conn = DriverManager.getConnection("jdbc:mysql:///day04", "root", "123");
        // 3. 操作数据
        String sql = "insert into user values(null, 'xiaoqi', '123', '[email protected]');";
        stmt = conn.createStatement();
        int affectedRowNumber = stmt.executeUpdate(sql);
        System.out.println(affectedRowNumber);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 4. 释放资源
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            stmt = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
}



@Test
public void test_delete() {
    // 1. 加载驱动
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
        throw new RuntimeException("驱动加载失败!");
    }
    Connection conn = null;
    Statement stmt = null;

    try {
        // 2. 建立连接
        conn = DriverManager.getConnection("jdbc:mysql:///day04", "root", "123");
        // 3. 操作数据
        String sql = "delete from user where id = 5;";
        stmt = conn.createStatement();
        int affectedRowNum = stmt.executeUpdate(sql);
        System.out.println(affectedRowNum);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 4. 释放资源
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            stmt = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
}



@Test
public void test_update() {
    // 1. 加载驱动
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
        throw new RuntimeException("驱动加载失败!");
    }
    Connection conn = null;
    Statement stmt = null;
    try {
        // 2. 建立连接
        conn = DriverManager.getConnection("jdbc:mysql:///day04", "root", "123");
        // 3. 操作数据
        String sql = "update user set username = 'zhaoliu', password = '123', email = '[email protected]' where id = 4;";
        stmt = conn.createStatement();
        int affectedRowNum = stmt.executeUpdate(sql);
        System.out.println(affectedRowNum);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 4. 释放资源
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            stmt = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
}



@Test
public void test_query() {
    // 1. 加载驱动
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
        throw new RuntimeException("驱动加载失败!");
    }

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {
        // 2. 建立连接
        conn = DriverManager.getConnection("jdbc:mysql:///day04", "root", "123");
        // 3. 操作数据
        String sql = "select * from user;";
        stmt = conn.createStatement();
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            String username = rs.getString("username");
            String password = rs.getString("password");
            System.out.println(username + " = " + password);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 4. 释放资源
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            stmt = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
}

练习2：编写工具类简化CRUD操作。

JDBCUtils 工具类抽取 :

版本一 :
public class JDBCUtils {

private static final String driverClass = "com.mysql.jdbc.Driver";
private static final String url = "jdbc:mysql:///day04";
private static final String user = "root";
private static final String password = "123";

// 加载驱动
public static void loadDriver() {
    // 1. 加载驱动
    try {
        Class.forName(driverClass);
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
        throw new RuntimeException("驱动加载失败!");
    }
}

// 获取连接
public static Connection getConnection() throws SQLException {
    Connection conn = DriverManager.getConnection(url, user, password);
    return conn;
}

// 释放资源
public static void release(Connection conn, Statement stmt, ResultSet rs) {
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        rs = null;
    }
    release(conn, stmt);
}

public static void release(Connection conn, Statement stmt) {
    if (stmt != null) {
        try {
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        stmt = null;
    }
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        conn = null;
    }
}

}

测试 :
@Test
public void test_query() {
// 1. 加载驱动
JDBCUtils.loadDriver();

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {
        // 2. 建立连接
        conn = JDBCUtils.getConnection();
        // 3. 操作数据
        String sql = "select * from user;";
        stmt = conn.createStatement();
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            String username = rs.getString("username");
            String password = rs.getString("password");
            System.out.println(username + " = " + password);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 4. 释放资源
        JDBCUtils.release(conn, stmt, rs);
    }
}

版本二 :

public class JDBCUtils {

// 属性
private static String driverClass;
private static String url;
private static String username;
private static String password;

// 请问 : 什么时候加载外部配置文件最合适 ???   
// 特点1 : 随着类的加载而加载.
// 特点2 : 静态代码块只在类加载的被执行一次. 仅一次.
static {
    Properties prop = new Properties();

    try {
        prop.load(new FileReader("jdbc.properties"));

        // 如果程序执行到这里, 说明外部资源文件加载成功, 需要给我们的静态属性赋值
        driverClass = prop.getProperty("driverClass");
        url = prop.getProperty("url");
        username = prop.getProperty("username");
        password = prop.getProperty("password");

        // 直接执行加载驱动
        loadDriver();

    } catch (IOException e) {
        e.printStackTrace();
        throw new RuntimeException("文件资源加载失败!");
    }
}

// 加载驱动
public static void loadDriver() {
    try {
        // 1. 加载驱动
        Class.forName(driverClass);
    } catch (ClassNotFoundException e) {
        // e.printStackTrace();
        // 驱动加载失败!
        throw new RuntimeException("驱动加载失败!");
    }
}

// 建立连接
public static Connection getConnection() throws SQLException {
    // 2. 建立连接
    return DriverManager.getConnection(url, username, password);
}

// 释放资源
public static void release(Connection conn, Statement stmt, ResultSet rs) {
    // 4. 释放资源
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // 将 rs 清空
        rs = null;
    }
    // 直接调用
    release(conn, stmt);
}
public static void release(Connection conn, Statement stmt) {
    // 4. 释放资源
    if (stmt != null) {
        try {
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        stmt = null;
    }
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        conn = null;
    }
}

}

测试二 :
@Test
public void test_query() {

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {
        // 1. 建立连接
        conn = JDBCUtils.getConnection();
        // 2. 操作数据
        String sql = "select * from user;";
        stmt = conn.createStatement();
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            String username = rs.getString("username");
            String password = rs.getString("password");
            System.out.println(username + " = " + password);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 3. 释放资源
        JDBCUtils.release(conn, stmt, rs);
    }
}



@Test
public void test_update() {
    Connection conn = null;
    Statement stmt = null;
    try {
        // 2. 建立连接
        conn = JDBCUtils.getConnection();
        // 3. 操作数据
        String sql = "update user set username = 'zhaoliu', password = '123', email = '[email protected]' where id = 4;";
        stmt = conn.createStatement();
        int affectedRowNum = stmt.executeUpdate(sql);
        System.out.println(affectedRowNum);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 4. 释放资源
        JDBCUtils.release(conn, stmt);
    }
}

@Test
public void test_delete() {
    Connection conn = null;
    Statement stmt = null;

    try {
        // 1. 建立连接
        conn = JDBCUtils.getConnection();
        // 2. 操作数据
        String sql = "delete from user where id = 5;";
        stmt = conn.createStatement();
        int affectedRowNum = stmt.executeUpdate(sql);
        System.out.println(affectedRowNum);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 4. 释放资源
        JDBCUtils.release(conn, stmt);
    }
}

@Test
public void test_insert() {

    Connection conn = null;
    Statement stmt = null;
    try {
        // 1. 建立连接
        conn = JDBCUtils.getConnection();
        // 2. 操作数据
        String sql = "insert into user values(null, 'xiaoqi', '123', '[email protected]');";
        stmt = conn.createStatement();
        int affectedRowNumber = stmt.executeUpdate(sql);
        System.out.println(affectedRowNumber);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 4. 释放资源
        JDBCUtils.release(conn, stmt);
    }
}

5.67. sql 注入问题（★★★★★）
由于没有对用户输入进行充分检查，而SQL又是拼接而成，在用户输入参数时，在参数中添加一些SQL 关键字，达到改变SQL运行结果的目的，也可以完成恶意攻击。

String sql = select * from user where username =” and password =” ;

例如：
一、输入 username: zhangsan’ or ‘1’=’1 password 随意
select * from user where username =’zhangsan’ or ‘1’=’1’ and password =”;
* and 优先级执行高于 or

二、在SQL添加 – 是mysql的注释输入 username: zhangsan’ – password 随意
select * from user where username =’zhangsan’ – ’ and password =” ;

public class StatementDemo {
@Test
public void test_statement() {
// 需求 : 根据用户的数据, 判断在数据库中是否存在该用户, 如果存在, 输出登录成功! 否则, 登录失败!

    // 键盘录入, 接收用户的输入
    Scanner sc = new Scanner(System.in);
    System.out.println("用户名 : ");
    String username = sc.nextLine();
    System.out.println("密码 : ");
    String password = sc.nextLine();
    sc.close();

    System.out.println(username + " = " + password);

    /************ JDBC 数据库连接操作 ***************/
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {
        // 1. 建立连接
        conn = JDBCUtils.getConnection();
        // 2. 操作数据
        String sql = "select * from user where username = '"+username+"' and password = '"+password+"';";
        stmt = conn.createStatement();
        rs = stmt.executeQuery(sql);
        // 判断返回的结果
        if (rs.next()) {
            // 登录成功
            int id = rs.getInt("id");
            String u_name = rs.getString("username");
            String u_pwd = rs.getString("password");
            String email = rs.getString("email");
            System.out.println(id + " : " + u_name + " : " + u_pwd + " : " + email);
            System.out.println("登录成功!");
        } else {
            // 登录失败
            System.out.println("登录失败! 用户名或密码错误!");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 3. 释放资源
        JDBCUtils.release(conn, stmt, rs);
    }
}

}

正确逻辑测试 :

非正确逻辑测试 :

解决SQL注入：使用PreparedStatement 取代 Statement
PreparedStatement 解决SQL注入原理，运行在SQL中参数以?占位符的方式表示
select * from user where username = ? and password = ? ;
将带有?的SQL 发送给数据库完成编译（不能执行的SQL 带有?的SQL 进行编译叫做预编译），在SQL编译后发现缺少两个参数
PreparedStatement 可以将? 代替参数发送给数据库服务器，因为SQL已经编译过，参数中特殊字符不会当做特殊字符编译，无法达到SQL注入的目的
Statement conn.createStatement() 该对象可以将SQL发送给数据库进行执行
PreparedStatement Connection.preparedStatement(sql) 该对象可以将SQL发送给数据库进行执行
问题：
SQL注入原理是什么?
为什么PreparedStatement 可以防止SQL注入？

PreparedStatement是Statement的子接口，它的实例对象可以通过调用Connection.preparedStatement(sql)方法获得，相对于Statement对象而言好处：
1.PreperedStatement可以避免SQL注入的问题。
2.Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出。PreparedStatement 可对SQL进行预编译，从而提高数据库的执行效率。
3.并且PreperedStatement对于sql中的参数，允许使用占位符的形式进行替换，简化sql语句的编写。

代码修改如下 : 预处理方案
5.6.1查询

public class PreparedStatementDemo {
@Test
public void test_statement() {
// 需求 : 根据用户的数据, 判断在数据库中是否存在该用户, 如果存在, 输出登录成功! 否则, 登录失败!

    // 键盘录入, 接收用户的输入
    Scanner sc = new Scanner(System.in);
    System.out.println("用户名 : ");
    String username = sc.nextLine();
    System.out.println("密码 : ");
    String password = sc.nextLine();
    sc.close();

    System.out.println(username + " = " + password);

    /************ JDBC 数据库连接操作 ***************/
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
        // 1. 建立连接
        conn = JDBCUtils.getConnection();
        // 2. 操作数据
        String sql = "select * from user where username = ? and password = ?;";
        stmt = conn.prepareStatement(sql);
        // 设置sql语句的参数
        stmt.setString(1, username);
        stmt.setString(2, password);
        // 执行sql语句
        rs = stmt.executeQuery();
        // 判断返回的结果
        if (rs.next()) {
            // 登录成功
            int id = rs.getInt("id");
            String u_name = rs.getString("username");
            String u_pwd = rs.getString("password");
            String email = rs.getString("email");
            System.out.println(id + " : " + u_name + " : " + u_pwd + " : " + email);
            System.out.println("登录成功!");
        } else {
            // 登录失败
            System.out.println("登录失败! 用户名或密码错误!");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 3. 释放资源
        JDBCUtils.release(conn, stmt, rs);
    }
}

}

5.78. PreparedStatement的 CRUD（★★★★★★）
5.7.1修改

@Test
public void test_update() {
    Connection conn = null;
    PreparedStatement stmt = null;
    try {
        // 2. 建立连接
        conn = JDBCUtils.getConnection();
        // 3. 操作数据
        String sql = "update user set username = ?, password = ?, email = ? where id = ?;";
        stmt = conn.prepareStatement(sql);
        // 设置参数
        stmt.setString(1, "张三");
        stmt.setString(2, "888");
        stmt.setString(3, "[email protected]");
        stmt.setInt(4, 1);
        // 执行
        int affectedRowNum = stmt.executeUpdate();
        System.out.println(affectedRowNum);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 4. 释放资源
        JDBCUtils.release(conn, stmt);
    }
}

5.7.2删除

@Test
public void test_delete() {
    Connection conn = null;
    PreparedStatement stmt = null;

    try {
        // 1. 建立连接
        conn = JDBCUtils.getConnection();
        // 2. 操作数据
        String sql = "delete from user where id = ?;";
        stmt = conn.prepareStatement(sql);
        stmt.setInt(1, 4);
        int affectedRowNum = stmt.executeUpdate();
        System.out.println(affectedRowNum);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 4. 释放资源
        JDBCUtils.release(conn, stmt);
    }
}

5.7.3增加

@Test
public void test_insert() {

    Connection conn = null;
    PreparedStatement stmt = null;
    try {
        // 1. 建立连接
        conn = JDBCUtils.getConnection();
        // 2. 操作数据
        String sql = "insert into user values(?,?,?,?);";
        stmt = conn.prepareStatement(sql);
        // 设置参数
        stmt.setInt(1, 4);
        stmt.setString(2, "赵六");
        stmt.setString(3, "888");
        stmt.setString(4, "[email protected]");
        int affectedRowNumber = stmt.executeUpdate();
        System.out.println(affectedRowNumber);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 4. 释放资源
        JDBCUtils.release(conn, stmt);
    }
}

今日任务
1.了解数据库连接池的概念
2.了解数据库连接池的必要性
3.掌握数据库连接池的使用方式
4.了解DButils的概念
5.掌握DButils工具类的使用
6数据库连接池

6.1数据库连接池原理：
首先我们通过画图的形式来分析一下我们目前所学的jdbc程序的结构。
通过画图分析得出：当前的jdbc程序每次访问数据库都需要创建一个新的连接，访问完毕之后，还需要释放资源。那么在这样的一个过程中，连接的创建和销毁所消耗的资源是远远大于我们发送sql并执行的时间的。基于这样的情况，我们发现我们的jdbc程序将大量的资源浪费在了连接的创建和销毁上。
就像在上海坐地铁，就一站2分钟的路程，往往在买地铁票的过程需要等待至少10分钟以上的时间。这样是不合理的。所以我们需要对这样的结构进行优化。

思考上面的结构，大部分的时间浪费在了创建和销毁上。那么我们能不能实现将这些连接回收和利用呢？这样我们就不需要不停的创建和销毁了。只需要创建一次，放在指定的地方。而我们使用的时候，直接从里面拿就行了。用完放回原来的地方。不去销毁，当我再次使用的时候，去拿就行了。这样的解决方案就是我们需要的。

优化后的结构如下。
说明：首先创建一定数量的连接，然后放到指定的地方。当我们需要获取连接的时候，直接从指定的地方获取。用完了，我们再将连接放回去。这样就能实现我们连接的回收利用。并且不用花费时间在创建和销毁连接上。

一次性创建多个连接，将多个连接缓存在内存中，形成数据库连接池（内存数据库连接集合），如果应用程序需要操作数据库，只需要从连接池中获取一个连接，使用后，并不需要关闭连接，只需要将连接放回到连接池中。

好处：节省创建连接与释放连接的性能消耗 —- 连接池中连接起到复用的作用，提高程序性能

6.2 自定义数据库连接池的实现
JDBC定义 javax.sql.DataSource 数据库连接池接口，只需要实现该接口，就可以去定义数据库连接池

编写实现连接池注意事项：
1、一次性批量创建多个数据库连接，保存到集合对象中 —– 就是连接池
2、实现DataSource接口中 getConnection , 从连接池中取出连接
3、在用户使用连接后，能够将连接放回到连接池

自定义数据库连接池 :

/**
* 数据库连接池的实现步骤 :
* 1. 一次性创建多个数据库连接, 保存到数据库连接集合中. (就是连接池)
* 2. 实现 DataSource 接口中的 getConnection 方法, 从连接池中取出连接.
* 3. 在用户使用连接后, 能够将连接放回到连接池中.
*/

public class MyDataSource implements DataSource {

// 自定义一个用于保存连接的集合
private LinkedList<Connection> connList = new LinkedList<Connection>();

// 构造方法
// 步骤一 : 一次性创建多个数据库连接, 保存到数据库连接集合中.
public MyDataSource() {
    // 创建10个连接
    for (int i = 0; i < 10; i++) {
        try {
            Connection conn = JDBCUtils.getConnection();
            // 将连接对象添加到连接池集合中存储
            connList.add(conn);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("连接池初始化失败!");
        }
    }
    System.out.println("一次性初始化10个连接. 数据库连接池大小为: " + connList.size());
}

// 步骤二 : 实现 DataSource 接口中的 getConnection 方法, 从连接池中取出连接.
@Override
public Connection getConnection() throws SQLException {
    // 连接池为空, 没有连接, 新创建三个连接放入到连接池中
    if (connList.isEmpty()) {
        for (int i = 0; i < 3; i++) {
            Connection conn = JDBCUtils.getConnection();
            connList.add(conn);
        }
    }
    // 使用 remove 取出连接, 提供给用户使用, 表示该连接已经被占用
    Connection conn = connList.removeFirst();
    System.out.println("从连接池中获取一个连接. 数据库连接池大小为: " + connList.size());
    return conn;
}

// 步骤三 : 在用户使用连接后, 能够将连接放回到连接池中.
public void addBackToPook(Connection conn) {
    connList.add(conn);
    System.out.println("使用连接完毕后, 将连接归还给连接池. 数据库连接池大小为: " + connList.size());
}

@Override
public PrintWriter getLogWriter() throws SQLException {
    return null;
}

@Override
public int getLoginTimeout() throws SQLException {
    return 0;
}

@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
    return null;
}

@Override
public void setLogWriter(PrintWriter arg0) throws SQLException {
}

@Override
public void setLoginTimeout(int arg0) throws SQLException {
}

@Override
public boolean isWrapperFor(Class<?> arg0) throws SQLException {
    return false;
}

@Override
public <T> T unwrap(Class<T> arg0) throws SQLException {
    return null;
}

@Override
public Connection getConnection(String arg0, String arg1) throws SQLException {
    return null;
}

}

自定义数据库连接池的测试代码 :

public class MyDataSourceDemo {
@Test
public void testMyDataSource() {
// 需求 : 查询 User 表中的所有记录

    // 创建一个数据库连接池对象
    MyDataSource dataSource = new MyDataSource();

    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        // 1. 建立连接
        conn = dataSource.getConnection();
        // 2. 操作数据
        String sql = "select * from user;"; 
        stmt = conn.prepareStatement(sql);
        rs = stmt.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            String email = rs.getString("email");
            System.out.println(id + " : " + username + " : " + password + " : " + email);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 3. 释放资源
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            stmt = null;
        }
        if (conn != null) {
            // 将连接返回到连接池对象中
            dataSource.addBackToPook(conn);
        }
    }
}

}

自定义连接池程序的问题：
1、尽量不要使用具体对象类型的引用
MyDataSource dataSource = new MyDataSource(); 应该写为 DataSource dataSource = new MyDataSource();
2、使用自定义方法 addBackToDBPool 将连接放回连接池，需要用户在使用时需要记忆额外API

解决：让用户定义连接池时 DataSource dataSource = new MyDataSource(); 在用户使用连接后，应该调用conn.close(); 完成将连接放回到连接池
对close方法进行方法增强

练习：增强close方法，不真正关闭连接，而是将连接放回到连接池

说明: 使用动态代理完成方法的增强.

public class MyDataSource implements DataSource {

// 自定义一个用于保存连接的集合
private LinkedList<Connection> connList = new LinkedList<Connection>();

// 构造方法
// 步骤一 : 一次性创建多个数据库连接, 保存到数据库连接集合中.
public MyDataSource() {
    // 创建10个连接
    for (int i = 0; i < 10; i++) {
        try {
            Connection conn = JDBCUtils.getConnection();
            // 将连接对象添加到连接池集合中存储
            connList.add(conn);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("连接池初始化失败!");
        }
    }
    System.out.println("一次性初始化10个连接. 数据库连接池大小为: " + connList.size());
}

// 步骤二 : 实现 DataSource 接口中的 getConnection 方法, 从连接池中取出连接.
@Override
public Connection getConnection() throws SQLException {
    // 连接池为空, 没有连接, 新创建三个连接放入到连接池中
    if (connList.isEmpty()) {
        for (int i = 0; i < 3; i++) {
            Connection conn = JDBCUtils.getConnection();
            connList.add(conn);
        }
    }
    // 使用 remove 取出连接, 提供给用户使用, 表示该连接已经被占用
    final Connection conn = connList.removeFirst();

    // 需求 : 使用动态代理对象 conn 连接对象的 close 方法进行拦截和控制
    // 对 conn 对象进行代理, 让该方法返回被代理的 proxyConn 对象
    ClassLoader loader = MyDataSource.class.getClassLoader();
    Class<?>[] interfaces = conn.getClass().getInterfaces();
    Connection proxyConn = (Connection) Proxy.newProxyInstance(loader, interfaces, new InvocationHandler(){
        @Override
        public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
            // 判断需要被拦截和控制的方法
            if ("close".equals(method.getName())) {
                // 将连接放回到连接池对象集合中
                addBackToPook(conn);
                return null;
            }
            // 不需要拦截和控制的方法, 直接执行原来功能即可
            return method.invoke(conn, args);
        }
    });

    System.out.println("从连接池中获取一个连接. 数据库连接池大小为: " + connList.size());
    // 返回代理对象 proxyConn
    return proxyConn;
}

// 步骤三 : 在用户使用连接后, 能够将连接放回到连接池中.
public void addBackToPook(Connection conn) {
    connList.add(conn);
    System.out.println("使用连接完毕后, 将连接归还给连接池. 数据库连接池大小为: " + connList.size());
}

@Override
public PrintWriter getLogWriter() throws SQLException {
    return null;
}

@Override
public int getLoginTimeout() throws SQLException {
    return 0;
}

@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
    return null;
}

@Override
public void setLogWriter(PrintWriter arg0) throws SQLException {
}

@Override
public void setLoginTimeout(int arg0) throws SQLException {
}

@Override
public boolean isWrapperFor(Class<?> arg0) throws SQLException {
    return false;
}

@Override
public <T> T unwrap(Class<T> arg0) throws SQLException {
    return null;
}

@Override
public Connection getConnection(String arg0, String arg1) throws SQLException {
    return null;
}

}

测试类 :

public class MyDataSourceDemo {
@Test
public void testMyDataSource() {
// 需求 : 查询 User 表中的所有记录

    // 创建一个数据库连接池对象
    DataSource dataSource = new MyDataSource();

    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        // 1. 建立连接
        conn = dataSource.getConnection();
        // 2. 操作数据
        String sql = "select * from user;"; 
        stmt = conn.prepareStatement(sql);
        rs = stmt.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            String email = rs.getString("email");
            System.out.println(id + " : " + username + " : " + password + " : " + email);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 3. 释放资源
        JDBCUtils.release(conn, stmt, rs);
    }
}

}

7常用开源连接池
7.1DBCP 开源数据库连接池
一、DBCP 连接池
http://commons.apache.org/

DBCP 是Apache 的commons 项目的一个子项目
去官网下载 dbcp 和 pool 的jar包，DBCP 依赖 POOL的jar包

DBCP连接池核心类 BasicDataSource

说明 : 任何数据库连接池，需要与数据库连接，必须通过JDBC四个基本参数构造

1、手动设置参数
// 使用连接池
BasicDataSource basicDataSource = new BasicDataSource();
// 设置JDBC四个基本参数
basicDataSource.setDriverClassName(“com.mysql.jdbc.Driver”);
basicDataSource.setUrl(“jdbc:mysql:///day04”);
basicDataSource.setUsername(“root”);
basicDataSource.setPassword(“123”);

public class DBCPDemo {

// 手动设置参数 : DBCP 数据库连接池使用
@Test
public void testDBCP() {
    // 需求 : 查询 User 表中的所有记录

    // 创建一个数据库连接池对象
    BasicDataSource dataSource = new BasicDataSource();
    // 设置 JDBC 连接数据库的四个基本参数
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql:///day04");
    dataSource.setUsername("root");
    dataSource.setPassword("123");

    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        // 1. 建立连接
        conn = dataSource.getConnection();
        // 2. 操作数据
        String sql = "select * from user;";
        stmt = conn.prepareStatement(sql);
        rs = stmt.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            String email = rs.getString("email");
            System.out.println(id + " : " + username + " : " + password + " : " + email);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 3. 释放资源
        JDBCUtils.release(conn, stmt, rs);
        try {
            dataSource.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

}

2、通过配置文件
// 根据属性参数获得连接池
InputStream in = DBCPTest.class.getResourceAsStream(“/dbcp.properties”);
Properties properties = new Properties();
// 装载输入流
properties.load(in);

    DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);

// 配置文件设置参数 : DBCP 数据库连接池使用
@Test
public void testDBCP() {
    // 需求 : 查询 User 表中的所有记录

    // 配置连接
    Properties prop = new Properties();
    try {
        prop.load(new FileInputStream("src\\dbcp.properties"));
    } catch (IOException e1) {
        e1.printStackTrace();
        throw new RuntimeException("加载配置文件失败!");
    }

    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
        // 创建一个数据库连接池对象
        BasicDataSource dataSource = BasicDataSourceFactory.createDataSource(prop);
        // 1. 建立连接
        conn = dataSource.getConnection();
        // 2. 操作数据
        String sql = "select * from user;";
        stmt = conn.prepareStatement(sql);
        rs = stmt.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            String email = rs.getString("email");
            System.out.println(id + " : " + username + " : " + password + " : " + email);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 3. 释放资源
        JDBCUtils.release(conn, stmt, rs);
    }
}

7.2C3P0 开源数据库连接池
主流开源连接池，在Hibernate和Spring 都提供对C3P0连接池支持
去下载c3p0 开发包 http://sourceforge.net

1、手动
// 核心连接池类
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
// 设置四个JDBC基本连接属性
comboPooledDataSource.setDriverClass(“com.mysql.jdbc.Driver”);
comboPooledDataSource.setJdbcUrl(“jdbc:mysql:///day04”);
comboPooledDataSource.setUser(“root”);
comboPooledDataSource.setPassword(“123”);

@Test
public void test_c3p0() throws PropertyVetoException {
    // 需求 : 查询 user 表中的所有数据

    // 核心连接池类
    ComboPooledDataSource dataSource = new ComboPooledDataSource();
    // 设置四个JDBC基本连接属性
    dataSource.setDriverClass("com.mysql.jdbc.Driver");
    dataSource.setJdbcUrl("jdbc:mysql:///day04");
    dataSource.setUser("root");
    dataSource.setPassword("123");

    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
        // 1. 建立连接
        conn = dataSource.getConnection();
        // 2. 操作数据
        String sql = "select * from user;";
        stmt = conn.prepareStatement(sql);
        rs = stmt.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            String email = rs.getString("email");
            System.out.println(id + " : " + username + " : " + password + " : " + email);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 3. 释放资源
        JDBCUtils.release(conn, stmt, rs);
    }
}

2、在src下新建c3p0-config.xml
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource(); 会自定加载配置文件

常用基本连接池属性
acquireIncrement 如果连接池中连接都被使用了，一次性增长3个新的连接
initialPoolSize 连接池中初始化连接数量默认:3
maxPoolSize 最大连接池中连接数量默认：15连接
maxIdleTime 如果连接长时间没有时间，将被回收默认：0 连接永不过期
minPoolSize 连接池中最小连接数量默认：3

C3p0配置文件

或者

rollback;
方式2：自动提交，通过修改mysql全局变量“autocommit”进行控制
show variables like ‘%commit%’;

设置自动提交的参数为OFF:
set autocommit = 0; – 0:OFF 1:ON

扩展：Oracle数据库事务不自动提交

2.2.2JDBC事务操作
Connection对象的方法名描述
conn.setAutoCommit(false) 开启事务
conn.commit() 提交事务
conn.rollback() 回滚事务

注意：在jdbc事务操作中，事务的控制都是通过Connection对象完成的，当一个完整的业务操作前，我们首先使用connection.setAutoCommit(false)来开启事务，当业务操作完成之后，我们需要使用connection.commit()来提交事务。当然了，如果出现了异常，我们需要撤销所有的操作，所以出现异常，需要进行事务的回滚。
如下是jdbc操作事务的模板代码，具体操作我们会在我们的转账案例中完成。

//事务模板代码
public void demo01() throws SQLException{
// 获得连接
Connection conn = null;

try {
    //#1 开始事务
    conn.setAutoCommit(false);

    //.... 加钱 ,减钱

    //#2 提交事务
    conn.commit();
} catch (Exception e) {
    //#3 回滚事务
    conn.rollback();
} finally{
    // 释放资源
    conn.close();
}

}

9.1.1.1Web层

public class AccountWeb {
public static void main(String[] args) {

    // 1. 模拟数据
    String outUser = "Jack";
    String inUser = "Rose";
    int money = 100;

    // 2. 业务转账的操作
    AccountService accountService = new AccountService();
    boolean result = accountService.tranfer(outUser, inUser, money);

    // 3. 判断
    if (result == false) {
        System.out.println("转账失败!");
    } else {
        System.out.println("转账成功!");
    }
}

}

9.1.1.2service层

注意 : service层要获取 Connection 连接对象, 开始事务, 提交事务, 回滚事务, 最后别忘了将 连接对象 传入到 dao层的 out, in 方法参数中.

public class AccountService {

// 转账操作
public boolean tranfer(String outUser, String inUser, int money) {

    // 开启事务 : (由 `连接` 对象来控制)
    Connection conn = null;

    AccountDao accountDao = new AccountDao();

    try {
        // 获取连接对象
        conn = JDBCUtils.getConnection();
        // 开启事务
        conn.setAutoCommit(false);

        // 转出
        accountDao.out(conn, outUser, money);

        int i = 10 / 0;

        // 转入
        accountDao.in(conn, inUser, money);

        // 提交事务 (让修改永久保存)
        conn.commit();

        // 返回
        return true;
    } catch (Exception e) {
        e.printStackTrace();

        // 回滚事务
        if (conn != null) {
            try {
                conn.rollback();
                conn.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            conn = null;
        }
    }

    return false;
}

}

9.1.1.3dao层
注意 : 在执行 update 方法是一定要传入参数中的 conn 连接对象, 并且不要在创建 QueryRunner 对象是传入 数据库连接池.

public class AccountDao {

// Dao 层如果出异常, 如何处理 ???  直接抛, 交给 Service 层处理.
// 转出
public void out(Connection conn, String outUser, int money) throws SQLException {
    // 1. 创建一个 QueryRunner 对象
    QueryRunner queryRunner = new QueryRunner();
    // 2. 执行相对应的方法
    // 准备参数
    String sql = "update account set money = money - ? where name = ?;";
    Object[] params = {money, outUser};
    queryRunner.update(conn, sql, params);
}

// 转入
public void in(Connection conn, String inUser, int money) throws SQLException {
    // 1. 创建一个 QueryRunner 对象
    QueryRunner queryRunner = new QueryRunner();
    // 2. 执行相对应的方法
    // 准备参数
    String sql = "update account set money = money + ? where name = ?;";
    Object[] params = {money, inUser};
    queryRunner.update(conn, sql, params);      
}

}

2.2.3DBUtils事务操作
Connection对象的方法名描述
conn.setAutoCommit(false) 开启事务
new QueryRunner() 创建核心类，不设置数据源(手动管理连接)
query(conn , sql , handler, params ) 或
update(conn, sql , params) 手动传递连接
DbUtils.commitAndCloseQuietly(conn) 或
DbUtils.rollbackAndCloseQuietly(conn) 提交并关闭连接
回滚并关闭连接

过程与JDBC操作一样，只不过需要注意的是，connection必须手动控制，不能交给DBUtils去控制。

说明 : Service 层代码中关于 事务提交与回滚 使用 DBUtils 方式实现, 代码如下 :
public class AccountService {

// 转账操作
public boolean tranfer(String outUser, String inUser, int money) {

    // 开启事务 : (由 `连接` 对象来控制)
    Connection conn = null;

    AccountDao accountDao = new AccountDao();

    try {
        // 获取连接对象
        conn = JDBCUtils.getConnection();
        // 开启事务
        conn.setAutoCommit(false);

        // 转出
        accountDao.out(conn, outUser, money);

        int i = 10 / 0;

        // 转入
        accountDao.in(conn, inUser, money);

        // 提交事务 (让修改永久保存)
        DbUtils.commitAndCloseQuietly(conn);

        // 返回
        return true;
    } catch (Exception e) {
        e.printStackTrace();

        // 回滚事务
        DbUtils.rollbackAndCloseQuietly(conn);
    }

    return false;
}

}

2.3事务管理：传递Connection
修改service和dao，service将connection传递给dao，dao不需要自己获得连接

2.3.1Dao层
很明显，我们的事务是针对整个业务逻辑的。所以事务的管理应该在业务层进行管理。并且，需要注意的是，我们在整个事务的控制过程中应该是一个connection对象。所以，在业务层我们需要拿到connection对象，用connection对象对业务层进行事务处理。而既然在业务层获取了connection对象，那么就必须要保证我们在dao层拿到的connection对象和业务层拿到的connection对象是同一个，那么这里我们可以使用传递参数的方式将connection对象传递给dao层。
所以dao层需要修改，connection对象需要从业务层传递过来。并且需要注意的是，connection对象是不能在dao层关闭的，因为我们业务层还需要继续使用connection对象。所以dao层代码的修改如下：

public class AccountDao {

// 转出
public void out(Connection conn, String outUser, int money) throws SQLException {

    QueryRunner queryRunner = new QueryRunner();
    String sql = "update account set money = money - ? where name = ?;";
    Object[] params = {money, outUser};
    queryRunner.update(conn, sql, params);
}

// 转入
public void in(Connection conn, String inUser, int money) throws SQLException {
    QueryRunner queryRunner = new QueryRunner();
    String sql = "update account set money = money + ? where name = ?;";
    Object[] params = {money, inUser};
    queryRunner.update(conn, sql, params);
}

}

2.3.2service层
同样service层需要获取连接，而且这里的连接需要被传递到dao层去，并且最后需要释放资源。

public class AccountService {

// 转账操作
public boolean transfer(String outUser, String inUser, int money) {
    Connection conn = null;

    AccountDao accountDao = new AccountDao();
    try {
        // 1. 获取连接
        conn = JDBCUtils.getConnection();
        // 2. 开启事务
        conn.setAutoCommit(false);

        // 转出
        accountDao.out(conn, outUser, money);

        int i = 10 / 0;

        // 转入
        accountDao.in(conn, inUser, money);

        // 如果转出与转入都执行成功了, 需要提交事务
        conn.commit();
    } catch (Exception e) {
        e.printStackTrace();
        // 一旦发生异常, 表示转账失败了.
        // 需要回滚事务, 并返回 false
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            conn = null;
        }
        return false;
    }
    return true;
}

}

2.4 使用JDBCUtils工具类集成提交与回滚

public class JDBCUtils {

// 定义了一个 c3p0 数据库连接池对象
private static ComboPooledDataSource dataSource = new ComboPooledDataSource("day06");

// 获取连接对象
public static DataSource getDataSource() {
    return dataSource;
}

// 建立连接
public static Connection getConnection() throws SQLException {
    // 该连接对象已经从 c3p0 的数据库连接池中获取
    Connection conn = dataSource.getConnection();       
    return conn;
}

// 释放资源
public static void release(ResultSet rs, Statement stmt, Connection conn) {
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        rs = null;
    }
    // 调用下面的方法释放 `执行对象和连接对象`
    release(stmt, conn);
}

public static void release(Statement stmt, Connection conn) {
    if (stmt != null) {
        try {
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        stmt = null;
    }
    if (conn != null) {
        try {
            conn.close();   // conn 调用任何方法都是执行 `调用处理器` 的 invoke 方法.
        } catch (SQLException e) {
            e.printStackTrace();
        }
        conn = null;
    }
}

// 提交并关闭连接
public static void commitAndCloseQuietly(Connection conn) {
    DbUtils.commitAndCloseQuietly(conn);
}
// 回滚并关闭连接
public static void rollbackAndCloseQuietly(Connection conn) {
    DbUtils.rollbackAndCloseQuietly(conn);
}

}

2.5提高：ThreadLocal
2.5.1相关知识与案例介绍
在“事务传递参数版”中，我们必须修改方法的参数个数，传递连接对象，才可以完成整个事务操作。如果不传递参数，是否可以完成？
思考：在上述事务传递参数的管理方式中，我们这样传递参数的目的无非就是为了完成在整个业务逻辑过程中任何地方所用到的都是同一个connection对象就行了。所以，如果不传递参数，我们只需要保证我们在一条业务线中，保证我们任何地方所使用的的connection对象是同一个就行了。基于这个需要，我们考虑是否可以将当前业务线中所用到的connection存到一个地方，然后让当前业务线的所有connection都从这个地方去拿。这就需要使用我们接下来学习的ThreadLocal。
ThreadLocal底层分析.

java.lang.ThreadLocal 该类提供了线程局部 (thread-local) 变量，用于在当前线程*享数据。ThreadLocal工具类底层就是一个Map，key存放的当前线程，value存放需要共享的数据。如下图，在整个程序运行中，我们有个Map变量，每条线程又有属于自己的id，也就是thread.currentThread。所以我们可以向map中存储一个值，key就是当前线程的id，值就是我们需要存储的值。这样，我们在整个线程中的任何一个地方，我们可以通过当前线程的id从map中获取的值都是同一个。因此，我们在一条线程中，我们在service层和dao层从map中就能获取同一个connection对象了。

2.5.2分析并实现JDBCUtils中getConnection方法的修改
程序在获取连接时, 如果 ThreadLocal类中没有当前线程对象的存储连接, 那么就从数据库连接池中获取连接, 然后将该连接存储到 ThreadLocal 中.此时,当该线程再次获取连接时,先从ThreadLocal中查找是否存在该线程的连接对象,如果有,直接返回并获取,而不再从连接池中获取,这样就达到了在同一个线程中无论获取多少次连接, 其获取的都是同一个连接对象.

工具类的获取连接方法的改写，首先从local中获取连接，获取不到, 再从数据库连接池去获取

public class JDBCUtils {

// c3p0 数据库连接池对象属性
private static final ComboPooledDataSource dataSource = new ComboPooledDataSource("day06");
// 创建一个 ThreadLocal 对象
private static final ThreadLocal<Connection> local = new ThreadLocal<Connection>();

// 获取连接
public static Connection getConnection() throws SQLException {
    Connection conn = null;

    // Connection 对象优先从 ThreadLocal 中获取
    conn = local.get();
    // 如果 conn 为 null
    if (conn == null) {
        // 说明当前线程是第一次获取连接对象, 所以需要从数据库连接池中获取连接对象
        conn = dataSource.getConnection();
        // 然后将该连接对象存储到 local 中
        local.set(conn);
    }

    // 返回连接
    return conn;
}

// 获取数据库连接池对象
public static DataSource getDataSource() {
    return dataSource;
}

// 提交
public static void commitAndCloseQuietly(Connection conn) {
    // 调用 DBUtils 框架的方法
    DbUtils.commitAndCloseQuietly(conn);
}

// 回滚
public static void rollbackAndCloseQuietly(Connection conn) {
    // 调用 DBUtils 框架的方法
    DbUtils.rollbackAndCloseQuietly(conn);
}

// 释放资源
public static void release(Connection conn, Statement stmt, ResultSet rs) {
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        rs = null;
    }
    release(conn, stmt);
}

public static void release(Connection conn, Statement stmt) {
    if (stmt != null) {
        try {
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        stmt = null;
    }
    if (conn != null) {
        try {
            conn.close();
            // 如果当前线程关闭连接时, 将 ThreadLocal 中存储的连接清除
            local.remove();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        conn = null;
    }
}

}

2.5.2.1service层
这时候service层就不需要向dao层传递connection对象了。

public class AccountService {

// 转账操作
public boolean transfer(String outUser, String inUser, int money) {
    Connection conn = null;

    AccountDao accountDao = new AccountDao();
    try {
        // 1. 获取连接
        conn = JDBCUtils.getConnection();
        // 2. 开启事务
        conn.setAutoCommit(false);

        // 转出
        accountDao.out(outUser, money);

        int i = 10 / 0;

        // 转入
        accountDao.in(inUser, money);

        // 如果转出与转入都执行成功了, 需要提交事务
        JDBCUtils.commitAndCloseQuietly(conn);
    } catch (Exception e) {
        e.printStackTrace();
        // 一旦发生异常, 表示转账失败了.
        // 需要回滚事务, 并返回 false
        JDBCUtils.rollbackAndCloseQuietly(conn);
        return false;
    }
    return true;
}

}

2.5.2.2dao层
而dao层又回到了直接通过jdbcUtils工具类去获取连接对象。这时候是能够保证获取的connection对象和业务层是同一个。

public class AccountDao {

// 转出
public void out(String outUser, int money) throws SQLException {
    Connection conn = JDBCUtils.getConnection();
    QueryRunner queryRunner = new QueryRunner();
    String sql = "update account set money = money - ? where name = ?;";
    Object[] params = {money, outUser};
    queryRunner.update(conn, sql, params);
}

// 转入
public void in(String inUser, int money) throws SQLException {
    Connection conn = JDBCUtils.getConnection();
    QueryRunner queryRunner = new QueryRunner();
    String sql = "update account set money = money + ? where name = ?;";
    Object[] params = {money, inUser};
    queryRunner.update(conn, sql, params);
}

}

测试连接对象：
测试发现，当前线程中3次获取的connection对象都是同一个。

2.6案例总结：事务的特性 ACID (理解)
数据库的事务必须具备ACID特性，ACID是指 Atomic（原子性）、Consistensy（一致性）、Isolation（隔离型）和Durability（持久性）的英文缩写。

1、原子性（Atomicity）
事务包装的一组sql，要么都执行成功，要么都失败。这些操作是不可分割的。
2、一致性（Consistency）
数据库的数据状态是一致的。
事务的成功与失败，最终数据库的数据都是符合实际生活的业务逻辑。一致性绝大多数依赖业务逻辑和原子性。
3、持久性：（Durability）
事务成功提交之后，对于数据库的改变是永久的。哪怕数据库发生异常，重启之后数据亦然存在。
4、隔离性（Isolation）
一个事务的成功或者失败对于其他的事务是没有影响。2个事务应该相互独立。
2.6.1事务的隔离级别
如果不考虑事务的隔离性，由于事务的并发，将会出现以下问题：
1、脏读 – 最严重，杜绝发生
2、不可重复读
3、幻读（虚读）

查询数据库的隔离级别
select @@tx_isolation;

设置数据库的隔离级别
set session transaction isolation level 级别字符串
级别字符串：read uncommitted、read committed、repeatable read、serializable 串行 (只能一条线程一条线程依次执行), 并行 (多个线程可以并发执行)
例如：set session transaction isolation level read uncommitted;

2.6.2隔离级别：解决问题
数据库规范规定了4种隔离级别，分别用于描述两个事务并发的所有情况。
1.read uncommitted 读未提交，一个事务读到另一个事务没有提交的数据。
a)存在：3个问题（脏读、不可重复读、虚读）。
b)解决：0个问题
2.read committed 读已提交，一个事务读到另一个事务已经提交的数据。
a)存在：2个问题（不可重复读、虚读）。
b)解决：1个问题（脏读）
3.repeatable read（）: 可重复读，在一个事务中读到的数据始终保持一致，无论另一个事务是否提交。
a)存在：1个问题（虚读）。
b)解决：2个问题（脏读、不可重复读）
4.serializable 串行化，同时只能执行一个事务，相当于事务中的单线程。
a)存在：0个问题。
b)解决：3个问题（脏读、不可重复读、虚读）
安全和性能对比
安全性：serializable > repeatable read > read committed > read uncommitted
性能： serializable < repeatable read < read committed < read uncommitted
常见数据库的默认隔离级别：
MySql：repeatable read
Oracle：read committed

在mysql数据库中，底层对于幻读做了优化，演示不了。

2.6.2.1脏读：指一个事务读取了另外一个事务未提交的数据。
脏读：指一个事务读取了另外一个事务未提交的数据。
set session transaction isolation level read uncommitted;

假设A向B转账100元，对应的sql语句如下：
开启事务
update account set money=money-100 where name=’a’;
update account set money=money+100 where nam=’b’;

两条sql语句执行完，b查询自己的账户多了100元。
b走后,a将事务进行回滚，这样B就损失了100元。

一个事务读取了另一个事务没有提交的数据，非常严重。应当尽量避免脏读。

2.6.2.2不可重复读：在一个事务内多次读取表中的数据，多次读取的结果不同。

不可重复读：在一个事务内多次读取表中的数据，多次读取的结果不同。
和脏读的区别：不可重复读是读取的已提交数据.

set session transaction isolation level read committed;

例如: 银行想查询A账户的余额，第一次查询的结果是200元，A向账户中又存了100元。此时，银行再次查询的结果变成了300元。两次查询的结果不一致，银行就会很困惑，以哪次为准。

和脏读不同的是：脏读读取的是前一事务未提交的数据，不可重复度读取的是前一事务已提交的事务。

很多人认为这有啥好困惑的，肯定是以后面的结果为准了。我们需要考虑这样一种情况，查询A账户的余额，一个打印到控制台，一个输出到硬盘上，同一个事务中只是顺序不同，两次查询结果不一致，到底以哪个为准，你会不会困惑呢？

当前事务查询A账户的余额为100元，另外一个事务更新余额为300元并提交，导致当前事务使用同一查询结果却变成了300元。

2.6.2.3幻读（虚读）

虚读和不可重复读的区别：
虚读强调的是数据表记录数的变化，主要是 insert 和 delete 语句。
不可重复读强调的是数据表内容的变化，主要是 update 语句。

set session transaction isolation level repeatable read;

指在一个事务中读取另一个事务插入或删除数据记录，导致当前事务读取数据前后不一致。
丙存款100元但未提交，这时银行做报表统计总额为500元，丙将事务提交，银行再统计就变成了 600元，两次统计结果不一致，银行便会不知所措。

一个事务读取另一个事务已经提交的数据，强调的是记录数的变化，常有sql类型为 insert和 delete。

说明 : 在mysql数据库中，底层对于幻读做了优化，演示不了。

2.6.2.4serializable 串行化
可以避免所有的问题。数据库执行这个事务，其他事务必须等待当前事务执行完毕，才能被执行。

set session transaction isolation level serializable;

思考题：为什么串行可以解决所有的问题？

上述所有的问题都是事务并行执行引起的，所以改成所有事务依次执行（串行），当然所有问题就都解决了。

10综合案例 & 阶段总结
1.1案例展示与分析

程序将划分层次
com.itheima.domain javaBean
com.itheima.utils 工具类
com.itheima.dao dao层
com.itheima.service service层（业务层，今天的情况内容非常少，直接调用dao，只有与业务挂钩时才能体现出来的）

1.2代码实现
1.2.1准备数据

– 创建数据库
create database day07;
– 切换数据库
use day07;
– 创建产品表
create table products (
pid int primary key auto_increment,
pname varchar(30),
price int,
category varchar(30)
);
– 添加数据
insert into products(pid,pname,price,category) values(null,’联想’,5000,’电脑办公’);
insert into products(pid,pname,price,category) values(null,’海尔’,3000,’家用电器’);
insert into products(pid,pname,price,category) values(null,’雷神’,7000,’电脑办公’);
insert into products(pid,pname,price,category) values(null,’Jack Jones’,500,’服装饰品’);
insert into products(pid,pname,price,category) values(null,’真维斯’,300,’服装饰品’);
insert into products(pid,pname,price,category) values(null,’花花公子’,1000,’服装饰品’);
insert into products(pid,pname,price,category) values(null,’劲霸男装’,3000,’服装饰品’);
insert into products(pid,pname,price,category) values(null,’香奈儿’,8000,’香水有毒’);

1.2.2查询所有

public class WebDemo {
private static Scanner sc = new Scanner(System.in);

public static void main(String[] args) {
    while (true) {
        System.out.println("请输入操作码: ");
        System.out.println("p:分页查询\tc:创建\tu:修改\td:删除\tda:批量删除\ti:通过id查询\tfa:查询所有\tq:退出\t");
        String choice = sc.nextLine();
        switch (choice) {
            case "p":
                findByPage();
                break;
            case "c":
                createProduct();
                break;
            case "u":
                updateProduct();
                break;
            case "d":
                deleteProduct();
                break;
            case "da":
                deleteByIdInTransaction();
                break;
            case "i":
                findById();
                break;
            case "fa":
                findAll();
                break;
            case "q":
                System.out.println("感谢您的使用, 欢迎下次再来.");
                System.exit(0);
                break;
            default :
                System.out.println("输入有误, 请重新输入.");
                break;
        }
    }
}

public class Product {
private int pid;
private String pname;
private int price;
private String category;
public int getPid() {
return pid;
}
public String getPname() {
return pname;
}
public int getPrice() {
return price;
}
public String getCategory() {
return category;
}
public void setPid(int pid) {
this.pid = pid;
}
public void setPname(String pname) {
this.pname = pname;
}
public void setPrice(int price) {
this.price = price;
}
public void setCategory(String category) {
this.category = category;
}
@Override
public String toString() {
return “Product [pid=” + pid + “, pname=” + pname + “, price=” + price + “, category=” + category + “]”;
}
}

数据库1

5.56. JDBC的工具类抽取

编写对user表增删改查程序，从重复代码中提取公共方法 JDBCUtils 工具类，将数据库连接参数写入properties 配置文件

或者

[评测视频]新小米路由器1TB和6TB好不好?该选择哪一款?

Formax金融圈体验投资活动 100%送1年迅雷VIP会员+7天

DS CADAM Drafting V5-6R2018 SP1安装破解激活图文详细教程(附下载)

条件数据库Android:sqllite的简单使用

京东众筹上线一款快充快放的安全快充移动电源：充30分钟用1天

ON1 Photo RAW 2018安装破解图文详细教程(附破解下载)

使用phonegap操作数据库的实现方法

Html5新标签datalist实现输入框与后台数据库数据的动态匹配

HTML5中indexedDB 数据库的使用实例

Android编程实现将压缩数据库文件拷贝到安装目录的方法

数据库1

5.56. JDBC的工具类抽取

编写对user表 增删改查程序，从重复代码中提取公共方法 JDBCUtils 工具类 ，将数据库连接参数写入properties 配置文件

或者

[评测视频]新小米路由器1TB和6TB好不好?该选择哪一款?

Formax金融圈体验投资活动 100%送1年迅雷VIP会员+7天

DS CADAM Drafting V5-6R2018 SP1安装破解激活图文详细教程(附下载)

条件数据库Android:sqllite的简单使用

京东众筹上线一款快充快放的安全快充移动电源：充30分钟用1天

ON1 Photo RAW 2018安装破解图文详细教程(附破解下载)

使用phonegap操作数据库的实现方法

Html5新标签datalist实现输入框与后台数据库数据的动态匹配

HTML5中indexedDB 数据库的使用实例

Android编程实现将压缩数据库文件拷贝到安装目录的方法

编写对user表增删改查程序，从重复代码中提取公共方法 JDBCUtils 工具类，将数据库连接参数写入properties 配置文件