数 据 库

1.数据库基础

1.1数据库简介

1.1.1简介

数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。
数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。
用户通过数据库管理系统访问数据库中的数据。
数据库软件应该为数据库管理系统，数据库是通过数据库管理系统创建和操作的。

数据库：存储、维护和管理数据的集合。

1.1.2常见数据库

• Oracle：Oracle数据库被认为是业界目前比较成功的关系型数据库管理系统。Oracle数据库可以运行在UNIX、Windows等主流操作系统平台，完全支持所有的工业标准，并获得最高级别的ISO标准安全性认证。
• MySQL：MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS(Relational Database Management System，关系数据库管理系统) 应用软件。
• DB2：DB2是IBM公司的产品，DB2数据库系统采用多进程多线索体系结构，其功能足以满足大中公司的需要，并可灵活地服务于中小型电子商务解决方案。
• Microsoft SQL Server：SQL Server 是Microsoft 公司推出的关系型数据库管理系统。具有使用方便可伸缩性好与相关软件集成程度高等优点。

1.1.3三大范式

什么是三大范式：

• 第一范式：无重复的列。当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。
• 第二范式：属性完全依赖于主键 [ 消除部分子函数依赖 ]。如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。
• 第三范式：属性不依赖于其它非主属性 [ 消除传递依赖 ]。设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF。满足第三范式（3NF）必须先满足第二范式（2NF）。第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

注：关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性
第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。

1.2 SQL语言

1.2.1概述

SQL：Structure Query Language（结构化查询语言），SQL被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准。
各数据库厂商都支持ISO的SQL标准，普通话
各数据库厂商在标准的基础上做了自己的扩展，方言
SQL 是一种标准化的语言，它允许你在数据库上执行操作，如创建项目，查询内容，更新内容，并删除条目等操作。
Create, Read, Update, and Delete 通常称为CRUD操作。

1.2.2分类

• DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等。
• DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）增删改。
• DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。
• DQL（Data Query Language）：数据查询语言，用来查询记录（数据）查询。

1.2.3 DDL数据定义语言

1. 操作数据库
创建数据库：

//create database 数据库名 
CREATE DATABASE mydb1; 
//create database 数据库名 character set 编码方式 
CREATE DATABASE mydb2 character SET GBK; 
//create database 数据库名 set 编码方式 collate 排序规则 
CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;

查看数据库：

//查看当前数据库服务器中的所有数据库
show databases;
//查看前面创建的mydb2数据库的定义信息 
Show CREATE DATABASE mydb2;

修改数据库：

alter database 数据库名 character set 编码方式

删除数据库：

DROP DATABASE mydb3;

其他语句：

//查看当前使用的数据库
Select database();
//切换数据库: use 数据库名
USE mydb2;

2.操作表
创建表：

CREATE TABLE 表名( 
	列名1 数据类型 [约束], 
	列名2 数据类型 [约束], 
	列名n 数据类型 [约束] 
);

常用数据类型：

名称	类型
int	整型
double	浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；默认支持四舍五入
char	固定长度字符串类型； char(10) 'aaa ’ 占10位
varchar	可变长度字符串类型； varchar(10) ‘aaa’ 占3位
text	字符串类型，比如小说信息；
blob	字节类型，保存文件信息(视频，音频，图片)；
date	日期类型，格式为：yyyy-MM-dd；
time	时间类型，格式为：hh:mm:ss
timestamp	时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值
datetime	日期时间类型 yyyy-MM-dd hh:mm:ss

删除表：

DROP TABLE table_name;

当前数据库中所有表：

SHOW TABLES;

查看表的字段信息：

DESC employee;

增加列:在上面员工表的基本上增加一个image列。
alter table 表名 add 新列名 新的数据类型

ALTER TABLE employee ADD image blob;

修改job列，使其长度为60。
alter table 表名 change 旧列名 新列名 新的数据类型

ALTER TABLE employee MODIFY job varchar(60); 
ALTER TABLE employee change job job varchar(60);

列名name修改为username

ALTER TABLE user CHANGE name username varchar(100);

删除image列,一次只能删一列。
alter table 表名 drop 列名

ALTER TABLE employee DROP image;

修改表名,表名改为user。

 alter table 旧表名 rename 新表名; alter table user rename users;

查看表格的创建细节

 show create table 表名; SHOW CREATE TABLE user;

修改表的字符集为gbk
alter table 表名 character set 编码方式

ALTER TABLE user CHARACTER SET gbk;

1.2.4 DML数据操作语言

DML是对表中的数据进行增、删、改的操作。不要与DDL混淆了。
主要包括：INSERT 、UPDATE、 DELETE
1.插入insert
insert into 表名(列名) values(数据值);

insert into student(stuname,stuage,stusex,birthday) values('张三1',18,'a','2000- 1-1');

– 注意:1多列和多个列值之间使用逗号隔开 2.列名要和列值一一对应
– 非数值的列值两侧需要加单引号
常见错误: Data too long for column ‘stusex’ at row 1
– 添加数据的时候可以将列名省略->当给所有列添加数据的时候
– 此时列值的顺序按照数据表中列的顺序执行
insert into student values(‘李四’,12,‘1111’,189.98,‘2000-1-1’,‘男’,‘2007-1-1’);
– 同时添加多行
insert into 表名(列名) values(第一行数据),(第二行数据),(),();

insert into student(stuname,stuage,stusex,birthday) values('张三3',18,'a','2000-1-1'), 
('张三4',18,'a','2000-1-1'), 
('张三5',18,'a','2000-1-1'), 
('张三6',18,'a','2000-1-1'), 
('张三7',18,'a','2000-1-1'),
('张三8',18,'a','2000-1-1');

注意：列名与列值的类型、个数、顺序要一一对应。
参数值不要超出列定义的长度。
如果插入空值，请使用null
插入的日期和字符一样，都使用引号括起来。

sql中的运算符: （1）算术运算符:+，-，*，/(除法),求余(%)
示例：
5/2
5%2
2/5
2%5
（2）赋值运算符:=
注：赋值方向：从右往左赋值
示例： name=‘张三’
(3) 逻辑运算符：
and(并且),or(或者),not（取非）
作用:用于连接多个条件时使用
(4) 关系运算符：
<,>,>=,<=,!=(不等于),=(等于),<>(不等于)
2.修改（更新）update
UPDATE 表名 SET 列名1=列值1,列名2=列值2 … WHERE 列名=值
3.删除delete
语法 ： DELETE from 表名 【WHERE 列名=值】

• DELETE 删除表中的数据，表结构还在；删除后的数据可以找回
• TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快。

小结:
为空的条件：列名 is null or 列名=’’
注:两个单引号表示空字符串

日期类型值的区别:
date：yyyy-MM-dd （年月日）
time：hh:mm:ss (时分秒)
datetime:yyyy-MM-dd hh:mm:ss (年月日时分秒)
获取当前系统时间:now()

select now();

1.2.5 DCL数据控制语言

1、创建用户：
create user 用户名@指定ip identified by 密码;

create user test123@localhost IDENTIFIED by 'test123' 

create user 用户名@客户端ip identified by 密码; 指定IP才能登陆

create user test456@10.4.10.18 IDENTIFIED by 'test456'

create user 用户名@‘% ’ identified by 密码 任意IP均可登陆

create user test7@'%' IDENTIFIED by 'test7'

2、用户授权：
grant 权限1,权限2,…,权限n on
数据库名.* to 用户名@IP; 给指定用户授予指定指定数据库指定权限

grant select,insert,update,delete,create on chaoshi.* to 'test456'@'127.0.0.1';

grant all on . to 用户名@IP 给指定用户授予所有数据库所有权限

grant all on *.* to 'test456'@'127.0.0.1';

3、用户权限查询：
show grants for 用户名@IP;

show grants for 'root'@'%';

4、撤销用户权限：
revoke 权限1,权限2,…,权限n on 数据库名.* from 用户名@IP;

REVOKE SELECT ON *.* FROM 'root'@'%' ;

5、删除用户：
drop user 用户名@IP;

drop user test123@localhost;

1.2.6 DQL数据查询语言

1.简单查询
查询所有列

SELECT * FROM stu;

查询指定列

SELECT sid, sname, age FROM stu;

2.条件查询
条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
=、!=、<>、<、<=、>、>=; BETWEEN…AND； IN(set)； IS NULL； AND；OR； NOT；
3.模糊查询
当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。
语法: 列名 like ‘表达式’ //表达式必须是字符串
通配符:
_(下划线): 任意一个字符
%：任意0~n个字符,‘张%’
4.字段控制查询
(1)去除重复记录
去除重复记录（两行或两行以上记录中系列的上的数据都相同），例如emp表中sal字段就存在相同的记录。当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT：

SELECT DISTINCT sal FROM emp;

(2)查看雇员的月薪与佣金之和
因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不是数值类型，那么会出错。

SELECT *,sal+comm FROM emp;

comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。
下面使用了把NULL转换成数值0的函数IFNULL：

SELECT *,sal+IFNULL(comm,0) FROM emp;

(3)给列名添加别名
在上面查询中出现列名为sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为total：

SELECT *, sal+IFNULL(comm,0) AS total FROM emp

给列起别名时，是可以省略AS关键字的：

SELECT *,sal+IFNULL(comm,0) total FROM emp;

5.排序
语法： order by 列名 asc/desc
asc 升序 desc 降序 默认不写的话是升序
(1) 查询所有学生记录，按年龄升序排序

SELECT * FROM stu ORDER BY age ASC;

或者

SELECT * FROM stu ORDER BY age;

(2) 查询所有学生记录，按年龄降序排序

SELECT * FROM stu ORDER BY age DESC;

(3) 查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
多列排序：当前面的列的值相同的时候，才会按照后面的列值进行排序

SELECT * FROM emp ORDER BY sal DESC,empno ASC;

6.聚合函数
聚合函数是用来做纵向运算的函数。
COUNT(列名)：统计指定列不为NULL的记录行数；
MAX(列名)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN(列名)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM(列名)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG(列名)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
(1) COUNT
当需要纵向统计时可以使用COUNT()。
查询emp表中记录数：

SELECT COUNT(*) AS cnt FROM emp;

查询emp表中有佣金的人数：
注意，因为count()函数中给出的是comm列，那么只统计comm列非NULL的行数。

SELECT COUNT(comm) cnt FROM emp;

查询emp表中月薪大于2500的人数：

SELECT COUNT(*) FROM emp WHERE sal > 2500;

统计月薪与佣金之和大于2500元的人数：

SELECT COUNT(*) AS cnt FROM empWHERE sal+IFNULL(comm,0) > 2500;

查询有佣金的人数，以及有领导的人数：

SELECT COUNT(comm), COUNT(mgr)FROM emp;

(2) SUM和AVG
当需要纵向求和时使用sum()函数。

查询所有雇员月薪和：

SELECT SUM(sal) FROM emp;

查询所有雇员月薪和，以及所有雇员佣金和：

SELECT SUM(sal), SUM(comm) FROM emp;

查询所有雇员月薪+佣金和：

SELECT SUM(sal+IFNULL(comm,0))FROM emp;

统计所有员工平均工资：

SELECT AVG(sal) FROM emp;

(3) MAX和MIN
查询最高工资和最低工资：

SELECT MAX(sal), MIN(sal) FROM emp;

7.分组查询
当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部分来分组。
注意：如果查询语句中有分组操作，则select后面能添加的只能是聚合函数和被分组的列名。

having子句：
查询工资总和大于9000的部门编号以及工资和：

SELECT deptno, SUM(sal) 
FROM emp 
GROUP BY deptno 
HAVING SUM(sal) > 9000;

注：having与where的区别:
1.having是在分组后对数据进行过滤（必须出现group by）,where是在分组前对数据进行过滤
2.having后面可以使用分组函数(统计函数)，where后面不可以使用分组函数。
WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

补充: 多列分组
– 统计出stu表中每个班级的男女生各多少人

 select gradename,gender,count(*) from stu
 group by gradename,gender

8.limit
LIMIT用来限定查询结果的起始行，以及总行数。
limit 开始下标,显示条数；//开始下标从0开始（实现分页效果）
limit 显示条数；//表示默认从0开始获取数据

如果一页记录为10条，希望查看第3页记录应该怎么查呢？
第一页记录起始行为0，一共查询10行； limit 0,10
第二页记录起始行为10，一共查询10行；limit 10,10
第三页记录起始行为20，一共查询10行； limit 20,10
pageIndex 页码值 pageSize 每页显示条数

limit (pageindex-1)*pagesize,pagesize;

查询语句书写顺序：select – from- where- groupby- having- order by-limit
查询语句执行顺序：from - where -group by -having - select - order by-limit

2.数据库高级

2.1数据库完整性

用来保证存放到数据库中的数据是有效的,即数据的有效性和准确性
确保数据的完整性 = 在创建表时给表中添加约束
完整性的分类：

• 实体完整性(行完整性):
• 域完整性(列完整性):
• 引用完整性(关联表完整性):
  主键约束：primary key
  唯一约束：unique [key]
  非空约束：not null
  默认约束：default
  自动增长：auto_increment
  外键约束: foreign key
  建议这些约束应该在创建表的时候设置
  多个约束条件之间使用空格间隔
  例子：

create table student( 
	studentno int primary key auto_increment, 
	loginPwd varchar(20) not null default '123456', 
	studentname varchar(50) not null, sex char(2) not null, 
	gradeid int not null, phone varchar(255) not null, 
	address varchar(255) default '学生宿舍', 
	borndate datetime, 
	email varchar(50) 
);

2.2实体完整性

实体：即表中的一行(一条记录)代表一个实体（entity）
实体完整性的作用：标识每一行数据不重复。
约束类型：
主键约束（primary key）
唯一约束(unique
自动增长列(auto_increment)

2.2.1主键约束（primary key）

注：每个表中要有一个主键。
特点：数据唯一，且不能为null
示例：
第一种添加方式：

CREATE TABLE student( id int primary key, name varchar(50) );

第二种添加方式：此种方式优势在于，可以创建联合主键

CREATE TABLE student( id int, name varchar(50), primary key(id) ); 
CREATE TABLE student( classid int, stuid int, name varchar(50), primary key(classid,stuid) );

第三种添加方式：

CREATE TABLE student( id int, name varchar(50) ); 
ALTER TABLE student ADD PRIMARY KEY (id);

2.2.2唯一约束（unique）

特点：数据不能重复。
主键约束不允许为空，唯一约束允许一次为空

CREATE TABLE student( Id int primary key, Name varchar(50) unique );

2.2.3自动增长列（auto_increment）

必须应用在整数int类型的列，不能是字符串或浮点型等。

sqlserver数据库 (identity-标识列)
oracle数据库(sequence-序列)
给主键添加自动增长的数值，列只能是整数类型

CREATE TABLE student( Id int primary key auto_increment, Name varchar(50) ); 
INSERT INTO student(name) values(‘tom’);

2.3域完整性

域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较
域代表当前单元格
域完整性约束：数据类型 非空约束（not null） 默认值约束(default)
check约束（mysql不支持）check(sex=‘男’ or sex=‘女’)

2.3.1数据类型

数值类型

类型	大小	范围（有符号）	范围（无符号 ）	用途
tinyint	1 字节	(-128，127)	(0，255)	小整数值
smallint	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
mediumint	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
bigint	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
float	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
double	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值

日期类型:
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性

类型	大小(字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和

时间值，时间戳 ,当更新数据的时候自动添加更新时间
字符串类型:
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

2.3.2非空约束

not null

CREATE TABLE student( Id int primary key, Name varchar(50) not null, Sex varchar(10) ); 
INSERT INTO student values(1,’tom’,null);

2.3.3默认值约束

default

CREATE TABLE student( Id int primary key, Name varchar(50) not null, Sex varchar(10) default '男' ); 
insert intostudent1 values(1,'tom','女'); 
insert intostudent1 values(2,'jerry',default);

2.4引用完整性

（参照完整性）
外键约束：FOREIGN KEY
示例：

CREATE TABLE student(id int primary key, name varchar(50) not null, sex varchar(10) default '男' ); 
create table score( 
	id int primary key, 
	score int, 
	sid int , 
	constraint fk_score_sid foreign key(sid) references student(id) 
);

constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)
外键列的数据类型一定要与主键的类型一致
第二种添加外键方式。

ALTER TABLEscore1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);

2.5多表的关系

2.5.1 一对多/多对一关系

客户和订单，分类和商品，部门和员工.
一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键.

2.5.2 多对多关系

学生和课程
多对多关系建表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键.

2.5.3 一对一关系

在实际的开发中应用不多.因为一对一可以创建成一张表.
两种建表原则：
唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外键设置为unique.
主键对应：让一对一的双方的主键进行建立关系.

2.6多表查询

多表查询有如下几种：

1. 合并结果集:UNION 、 UNION ALL
2. 连接查询
   2.1 内连接 [INNER] JOIN ON
   2.2 外连接 OUTER JOIN ON
   左外连接 LEFT [OUTER] JOIN
   右外连接 RIGHT [OUTER] JOIN
   全外连接（MySQL不支持）FULL JOIN
   2.3 自然连接 NATURAL JOIN
3. 子查询

2.6.1合并结果集

作用：合并结果集就是把两个select语句的查询结果合并到一起
合并结果集有两种方式：

• UNION：去除重复记录，例如：SELECT* FROM t1 UNION SELECT * FROM t2；
• UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。

注意：被合并的两个结果：列数、列类型必须相同。

2.6.2连接查询

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。
连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。
那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

使用主外键关系做为条件来去除无用信息

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

一：内连接
上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言！
语法：

select 列名 
from 表1 
inner join 表2 
on 表1.列名=表2.列名 //外键列的关系 
where.....

等价于：

select 列名 
from 表1,表2 
where 表1.列名=表2.列名 and ...(其他条件)

注:
<1>表1和表2的顺序可以互换
<2>找两张表的等值关系时,找表示相同含义的列作为等值关系。
<3>点操作符表示“的”，格式：表名.列名
<4>可以使用as,给表名起别名,注意定义别名之后,统一使用别名

三表联查：
语法：

 select 列名 from 表1
 inner join 表2 on 表1.列名=表2.列名
 inner join 表3 on 表1或表2.列名=表3.列名 where

等价于：

select 列名 from 表1,表2,表3
 where 表1.列名=表2.列名 and 表1/表2.列名=表3.列名

SQL标准的内连接为：

SELECT * 
FROM emp e 
INNER JOIN dept d 
ON e.deptno=d.deptno;

内连接的特点：查询结果必须满足条件。

二：外连接
包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。
– 显示还没有员工的部门名称?
– 外联查询
– 左外联:select 列名 from 主表 left join 次表 on 主表.列名=次表.列名
– 1.主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示null
– 2.主表和次表不能随意调换位置
使用场景:一般会作为子查询的语句使用

select depname,name from 
(select e.*,d.depname from department d left join employee e on e.depid=d.depid 
) aa where aa.name is null;

– 右外联:select 列名 from 次表 right join 主表 on 主表.列名=次表.列名

a.左外连接

SELECT * FROM emp e 
LEFT OUTER JOIN dept d 
ON e.deptno=d.deptno;

左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的
显示NULL。

insert into emp values(1007,'何炅','主管',1006,'2019-6-1',1011,2004,50);

b.右外连接
右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

insert into dept values(60,'颜值部','成都'); 

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

三.自然连接
自然连接（NATURAL INNER JOIN）：自然连接是一种特殊的等值连接，他要求两个关系表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列。
语句：

select * from emp e natural join dept d;

2.6.3子查询

一个select语句中包含另一个完整的select语句。
子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

• 子查询出现的位置：
  a. where后，作为条为被查询的一条件的一部分；
  b. from后，作表；
• 当子查询出现在where后作为条件时，还可以使用如下关键字：
  a. any
  b. all
• 子查询结果集的形式：
  a. 单行单列（用于条件）
  b. 单行多列（用于条件）
  c. 多行单列（用于条件）
  d. 多行多列（用于表）

2.7扩展

2.7.1多行新增

insert into 表名(列名) values (列值),(列值),(列值)；

2.7.2多表更新

(1)update 表1,表2 set 列名=列值 where 表1.列名=表2.列名 and 其他限定条件
(2)update 表1
inner join 表2 on 表1.列名=表2.列名
set 列名=列值
where 限定条件
示例：

update employee e,salary s
 set title='助工',salary=1000
 where e.empid=s.empid and name='李四'

2.7.3多表删除

语法：
delete 被删除数据的表 from 删除操作中使用的表
where 限定条件
注:多张表之间使用逗号间隔
示例：

 //删除人事部的信息
 delete d,e,s from department d,employee e,salary s
 where d.depid=e.depid and s.empid=e.empid and depname='人事部'

2.7.4日期运算函数

now() 获得当前系统时间
year(日期值) 获得日期值中的年份
date_add(日期,interval 计算值 计算的字段);
注：计算值大于0表示往后推日期，小于0表示往前推日期
示例：

 date_add(now(),interval -40 year);//40年前的日期

2.8数据库优化

1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.
备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。
3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or Name = 'admin'

可以这样查询：

select id from t where num = 10 
union all 
select id from t where Name = 'admin'

5.in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
很多时候用 exists 代替 in 是一个好的选择

3.数据库进阶

3.1事务的概念

事务（Transaction）是由一系列对系统中数据进⾏访问与更新的操作所组成的⼀个程序执行逻辑单元。

3.2事务的语法

1. start transaction; begin; （开始事务）
2. commit; 使得当前的修改确认（提交事务）
3. rollback; 使得当前的修改被放弃（回滚：事务在未提交之前才能回滚）

3.3事务的特性

1. 原⼦性（Atomicity） 事务的原⼦性是指事务必须是⼀个原子的操作序列单元。事务中包含的各项操作在⼀次执⾏过程中，只 允许出现两种状态之一。
   （1）全部执行成功
   （2）全部执行失败
   事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执⾏过程中出错， 会回滚到事务开始前的状态，所有的操作就像没有发⽣一样。也就是说事务是⼀个不可分割的整体，就 像化学中学过的原子，是物质构成的基本单位。
2. ⼀致性（Consistency） 事务的一致性是指事务的执⾏不能破坏数据库数据的完整性和一致性，一个事务在执⾏之前和执行之
   后，数据库都必须处以⼀致性状态。 比如：如果从A账户转账到B账户，不可能因为A账户扣了钱，⽽B账户没有加钱。
3. 隔离性（Isolation） 事务的隔离性是指在并发环境中，并发的事务是互相隔离的。也就是说，不同的事务并发操作相同的数 据时，每个事务都有各自完整的数据空间。 ⼀个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务是不能互相干扰的。
4. 持久性（Duration） 事务的持久性是指事务⼀旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服 务器宕机等故障。只要数据库重新启动，那么一定能够将其恢复到事务成功结束后的状态。

3.4事务的并发问题

• 脏读：读取到了没有提交的数据, 事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的 数据是脏数 据。
• 不可重复读：同⼀条命令返回不同的结果集（更新）.事务 A 多次读取同一数据，事务 B 在事务A 多次读取的 过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，结果不一致。
• 幻读：重复查询的过程中，数据 就发⽣了量的变化（insert， delete）。

3.5事务隔离级别

4.JDBC

4.1简介

JDBC（Java DataBase Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序。

Java 具有坚固、安全、易于使用、易于理解和可从网络上自动下载等特性，是编写数据库应用程序的杰出语言。所需要的只是 Java应用程序与各种不同数据库之间进行对话的方法。

JDBC可以在各种平台上使用Java，如Windows，Mac OS和各种版本的UNIX。

JDBC库包括通常与数据库使用相关的下面提到的每个任务的API。

• 连接数据库。
• 创建SQL或MySQL语句。
• 在数据库中执行SQL或MySQL查询。
• 查看和修改生成的记录。

4.2 JDBC体系结构

JDBC API支持用于数据库访问的两层和三层处理模型，但通常，JDBC体系结构由两层组成：

• JDBC API：这提供了应用程序到JDBC管理器连接。
• JDBC驱动程序API：这支持JDBC管理器到驱动程序连接。
  JDBC API使用驱动程序管理器和特定于数据库的驱动程序来提供与异构数据库的透明连接。

4.3 JDBC核心组件

• DriverManager： 此类管理数据库驱动程序列表。使用通信子协议将来自java应用程序的连接请求与适当的数据库驱动程序匹配。
• Driver:此接口处理与数据库服务器的通信，我们很少会直接与Driver对象进行交互。而是使用DriverManager对象来管理这种类型的对象。
• Connection：该界面具有用于联系数据库的所有方法。连接对象表示通信上下文，即，与数据库的所有通信仅通过连接对象。
• Statement：使用从此接口创建的对象将SQL语句提交到数据库。除了执行存储过程之外，一些派生接口还接受参数。
• ResultSet：在使用Statement对象执行SQL查询后，这些对象保存从数据库检索的数据。它作为一个迭代器，允许我们移动其数据。
• SQLException：此类处理数据库应用程序中发生的任何错误。

4.4使用步骤

构建JDBC应用程序涉及以下六个步骤：

• 导入包：需要包含包含数据库编程所需的JDBC类的包。大多数情况下，使用import java.sql.*就足够了。
• 注册JDBC驱动程序：要求您初始化驱动程序，以便您可以打开与数据库的通信通道。
• 打开连接：需要使用DriverManager.getConnection（）方法创建一个Connection对象，该对象表示与数据库的物理连接。
• 执行查询：需要使用类型为Statement的对象来构建和提交SQL语句到数据库。
• 从结果集中提取数据：需要使用相应的ResultSet.getXXX（）方法从结果集中检索数据。
• 释放资源：需要明确地关闭所有数据库资源，而不依赖于JVM的垃圾收集。

演示：
首先要导入驱动jar包：

import java.sql.*;

public class Jdbc {
    public static void main(String[] args){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String username="zxy";
            String password="199904";
            String url="jdbc:mysql://localhost:3306/canteen?serverTimezone=UTC";
            connection = DriverManager.getConnection(url,username,password);
            //3.定义sql，创建状态通道（进行SQL语句的发送）
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select * from dishes");//executeQuery(sql)执行查询
            //4.取出结果集信息
            while(resultSet.next()){//判断是否有下一条数据
                //取出数据：resultSet.getXXX("列名); XXX表示数据类型
                System.out.println("菜品名称："+resultSet.getString("dname")+"，价格："+resultSet.getDouble("price"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                //5.关闭资源
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

同理可进行增删改操作：

//执行查询
//resultSet = statement.executeQuery("select * from dishes");//executeQuery(sql)执行查询
//执行插入（返回结果为受影响的行数
int result = statement.executeUpdate("insert  into dishes(did,dname,price,dexcrip,location) values(7,'肉夹馍',12.0,'不错子','北京邮电大学')");
if (result>0){
    System.out.println("执行成功");
}else {
    System.out.println("执行失败");
}

4.5 SQL注入

4.5.1概念

就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。具体来说，它是利用现有应用程序，将（恶意的）SQL命令注入到后台数据库引擎执行的能力，它可以通过在Web表单中输入（恶意）SQL语句得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行SQL语句。比如先前的很多影视网站泄露VIP会员密码大多就是通过WEB表单递交查询字符暴出的，这类表单特别容易受到SQL注入式攻击。

String username ="admin"; 
String password=" 'abc' or 1=1 "; 
String sql="select * from users where username= '"+username+"' and password= "+password;

4.5.2解决办法-预状态通道

该PreparedStatement的接口扩展了Statement接口，它为您提供了一个通用的Statement对象有两个优点附加功能。
此语句使您可以动态地提供参数。

PreparedStatement pstmt = null;
try {
    String SQL = "Update Employees SET age = ? WHERE id = ?";
    pstmt = conn.prepareStatement(SQL); 
    . . .
} catch (SQLException e) { 
    . . .
} finally { 
    . . .
}

JDBC中的所有参数都由？符号，这被称为参数标记。在执行SQL语句之前，必须为每个参数提供值。
所述的setXXX()方法将值绑定到所述参数，其中XXX代表要绑定到输入参数的值的Java数据类型。如果忘记提供值，将收到一个SQLException。
每个参数标记由其顺序位置引用。第一个标记表示位置1，下一个位置2等等。该方法与Java数组索引不同，从0开始。

关闭PreparedStatement对象
就像关闭Statement对象一样，由于同样的原因，还应该关闭PreparedStatement对象。
一个简单的调用close（）方法将执行该作业。如果先关闭Connection对象，它也会关闭PreparedStatement对象。但是，应始终显式关闭PreparedStatement对象，以确保正确清理。

PreparedStatement pstmt = null; 
try {
	String SQL = "Update Employees SET age = ? WHERE id = ?"; 
	pstmt = conn.preparedStatement(SQL); 
	. . . 
}catch (SQLException e) { 
	. . . 
}finally { 
	pstmt.close(); 
}

对比statement和PreparedStatement：
(1)statement属于状态通道，PreparedStatement属于预状态通道
(2)预状态通道会先编译sql语句，再去执行，比statement执行效率高
(3)预状态通道支持占位符?，给占位符赋值的时候，位置从1开始
(4)预状态通道可以防止sql注入，原因:预状态通道在处理值的时候以字符串的方式处理

4.6 Java操作两表关系

4.6.1一对多（老师->学生）

实体类：

package jdbc.bean;

import java.util.ArrayList;
import java.util.List;

public class Teacher {
    private int tid;
    private String tname;
    private List<Student> list = new ArrayList<Student>();

    public List<Student> getList() {
        return list;
    }

    public void setList(List<Student> list) {
        this.list = list;
    }

    public int getTid() {
        return tid;
    }

    public void setTid(int tid) {
        this.tid = tid;
    }

    public String getTname() {
        return tname;
    }

    public void setTname(String tname) {
        this.tname = tname;
    }
}

package jdbc.bean;

public class Student {
    private int stuid;
    private String stuname;

    //外键列一般不生成属性
    // private int teacherid;
    public int getStuid() {
        return stuid;
    }

    public void setStuid(int stuid) {
        this.stuid = stuid;
    }

    public String getStuname() {
        return stuname;
    }

    public void setStuname(String stuname) {
        this.stuname = stuname;
    }
}

定义dao接口：

package jdbc.dao;

import jdbc.bean.Teacher;

public interface TeacherDao {
    //定义操作方法
    // 1.定义一个根据老师id查询老师信息(学生的信息)
    public Teacher getById(int tid);
}

实现接口：

package jdbc.dao;

import jdbc.bean.Student;
import jdbc.bean.Teacher;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TeacherDaoImpl implements TeacherDao {
    @Override
    public Teacher getById(int tid) {
        //操作数据库
        Connection connection = null;
        PreparedStatement pps = null;
        ResultSet resultSet = null;
        try {//1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2.获得链接
            String userName = "zxy";
            String passWord = "199904";
            String url = "jdbc:mysql://localhost:3306/kkb_test?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3.定义sql,创建预状态通道(进行sql语句的发送)
            String sql = "select * from student s,teacher t where s.teacherid=t.tid and t.tid=?";
            pps = connection.prepareStatement(sql);
            //给占位符赋值 （下标，内容） 从1开始
            pps.setInt(1, tid);
            //执行sql
            resultSet = pps.executeQuery();
            Teacher teacher = new Teacher();
            List<Student> students = new ArrayList<Student>();
            while (resultSet.next()) {
                //1.取出各自的信息
                teacher.setTid(resultSet.getInt("tid"));
                teacher.setTname(resultSet.getString("tname"));
                Student student = new Student();
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuname(resultSet.getString("stuname"));
                //2.建立学生和老师之间的关系
                students.add(student);
            }
            teacher.setList(students);
            return teacher;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                //5.关闭资源
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }
}

测试类：

package jdbc;

import jdbc.bean.Student;
import jdbc.bean.Teacher;
import jdbc.dao.TeacherDao;
import jdbc.dao.TeacherDaoImpl;

import java.util.List;

public class test {
    public static void main(String[] args) {
        TeacherDao dao = new TeacherDaoImpl();
        Teacher teacher = dao.getById(1);
        System.out.println("老师姓名:" + teacher.getTname());
        List<Student> studentList = teacher.getList();
        for (Student student : studentList) {
            System.out.println("\t studentname=" + student.getStuname());
        }
    }
}

4.6.2多对一（学生->老师）

与一对多类似，将实体类中的列表互换即可。

4.6.3一对一（妻子丈夫）

实体类：

package jdbc.bean;

public class Husband {
    private int husId;
    private String husName;
    private Wife wife;

    public int getHusId() {
        return husId;
    }

    public void setHusId(int husId) {
        this.husId = husId;
    }

    public String getHusName() {
        return husName;
    }

    public void setHusName(String husName) {
        this.husName = husName;
    }

    public Wife getWife() {
        return wife;
    }

    public void setWife(Wife wife) {
        this.wife = wife;
    }
}

package jdbc.bean;

public class Wife {
    private int wifeId;
    private String wifeName;
    private Husband husband;

    public int getWifeId() {
        return wifeId;
    }

    public void setWifeId(int wifeid) {
        this.wifeId = wifeid;
    }

    public String getWifeName() {
        return wifeName;
    }

    public void setWifeName(String wifeName) {
        this.wifeName = wifeName;
    }

    public Husband getHusband() {
        return husband;
    }

    public void setHusband(Husband husband) {
        this.husband = husband;
    }
}

接口：

package jdbc.dao;

import jdbc.bean.Husband;
import jdbc.bean.Wife;

public interface WifeDao {
    //查询妻子信息(包含丈夫信息)
    public Wife getWife(int wid);

    //查询丈夫信息(包含妻子信息)
    public Husband getHus(int hid);
}

实现类：

package jdbc.dao.impl;

import jdbc.bean.Husband;
import jdbc.bean.Wife;
import jdbc.dao.WifeDao;

import java.sql.*;

public class WifeDaoImpl implements WifeDao {
    @Override
    public Wife getWife(int wid) {
        //操作数据库
        Connection connection = null;
        PreparedStatement pps = null;
        ResultSet resultSet = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName = "root";
            String passWord = "123456";
            String url = "jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3.定义sql,创建预状态通道(进行sql语句的发送)
            String sql = "select * from wife w,husband h where w.hid=h.husid and w.wifeid=?";
            pps = connection.prepareStatement(sql);
            pps.setInt(1, wid);
            //执行sql
            resultSet = pps.executeQuery();
            Wife wife = new Wife();
            while (resultSet.next()) {
                //1.取出各自的信息
                wife.setWifeId(resultSet.getInt("wifeid"));
                wife.setWifeName(resultSet.getString("wifename"));
                Husband husband = new Husband();
                husband.setHusId(resultSet.getInt("husid"));
                husband.setHusName(resultSet.getString("husname"));
                //2.建立妻子和丈夫之间的关系
                wife.setHusband(husband);
            }
            return wife;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                //5.关闭资源
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public Husband getHus(int hid) {
        //操作数据库
        Connection connection = null;
        PreparedStatement pps = null;
        ResultSet resultSet = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName = "root";
            String passWord = "123456";
            String url = "jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3.定义sql,创建预状态通道(进行sql语句的发送)
            String sql = "select * from wife w,husband h where w.hid=h.husid and h.husid=?";
            pps = connection.prepareStatement(sql);
            pps.setInt(1, hid);
            //执行sql
            resultSet = pps.executeQuery();
            Husband husband = new Husband();
            while (resultSet.next()) {
                //1.取出各自的信息
                Wife wife = new Wife();
                wife.setWifeId(resultSet.getInt("wifeid"));
                wife.setWifeName(resultSet.getString("wifename"));
                husband.setHusId(resultSet.getInt("husid"));
                husband.setHusName(resultSet.getString("husname"));
                //2.建立妻子和丈夫之间的关系
                husband.setWife(wife);
            }
            return husband;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                //5.关闭资源
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }
}

测试：

public class test {
    public static void main(String[] args) {
        WifeDaoImpl wifeDao = new WifeDaoImpl();
        Wife wife = wifeDao.getWife(1);
        System.out.println(wife.getWifeName() + "," + wife.getHusband().getHusName());
        Husband hus = wifeDao.getHus(1);
        System.out.println(hus.getHusName() + "," + hus.getWife().getWifeName());
    }
}

4.6.4多对多（科目-学生）

同理

4.7数据库事务

4.7.1事务概述

一组要么同时执行成功，要么同时执行失败的SQL语句。是数据库操作的一个执行单元。

事务开始于：

• 连接到数据库上，并执行一条DML语句insert、update或delete
• 前一个事务结束后，又输入了另一条DML语句

事务结束于：

• 执行commit或rollback语句。
• 执行一条DDL语句，例如create table语句，在这种情况下，会自动执行commit语句。
• 执行一条DDL语句，例如grant语句，在这种情况下，会自动执行commit。
• 断开与数据库的连接
• 执行了一条DML语句，该语句却失败了，在这种情况中，会为这个无效的DML语句执行rollback语句。

4.7.2事务的四大特点

（ACID）

• actomicity(原子性)
  表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败
• consistency(一致性)
  表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态
• isolation(隔离性)
  事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。
• durability(持久性)
  持久性事务完成之后，它对于系统的影响是永久性的。

4.7.3 JDBC中的事务应用

如果JDBC连接处于自动提交模式，默认情况下，则每个SQL语句在完成后都会提交到数据库。
事务使您能够控制是否和何时更改应用于数据库。它将单个SQL语句或一组SQL语句视为一个逻辑单元，如果任何语句失败，则整个事务将失败。
要启用手动事务支持，而不是JDBC驱动程序默认使用的自动提交模式，请使用Connection对象的setAutoCommit（）方法。如果将boolean false传递给setAutoCommit（），则关闭自动提交。我们可以传递一个布尔值true来重新打开它。

4.7.4事务的提交和回滚

完成更改后，我们要提交更改，然后在连接对象上调用commit（）方法，否则，要使用连接名为conn的数据库回滚更新，调用rollback（）方法。

try{
	//Assume a valid connection object conn 
	conn.setAutoCommit(false); 
	Statement stmt = conn.createStatement(); 
	String SQL = "INSERT INTO Employees values (106, 20, 'Rita', 'Tez')"; 
	stmt.executeUpdate(SQL); 
	//Submit a malformed SQL statement that breaks 
	String SQL = "INSERTED IN Employees VALUES (107, 22, 'Sita', 'Singh')"; 
	stmt.executeUpdate(SQL); 
	// If there is no error. 
	conn.commit();
}catch(SQLException se){ 
	// If there is any error. 
	conn.rollback(); 
}

4.7.5 Savepoints

设置保存点时，可以在事务中定义逻辑回滚点。如果通过保存点发生错误，则可以使用回滚方法来撤消所有更改或仅保存在保存点之后所做的更改。
Connection对象有两种新的方法来帮助您管理保存点

• setSavepoint（String savepointName）：定义新的保存点。它还返回一个Savepoint对象。
• releaseSavepoint（Savepoint savepointName）：删除保存点。请注意，它需要一个Savepoint对象作为参数。此对象通常是由setSavepoint（）方法生成的保存点。

try{
	//Assume a valid connection object conn 
	conn.setAutoCommit(false); 
	Statement stmt = conn.createStatement(); 
	Savepoint savepoint1 = conn.setSavepoint("Savepoint1"); 
	String SQL = "INSERT INTO Employees VALUES (106, 20, 'Rita', 'Tez')"; 
	stmt.executeUpdate(SQL); 
	String SQL = "INSERTED IN Employees VALUES (107, 22, 'Sita', 'Tez')"; 
	stmt.executeUpdate(SQL); 
	conn.commit(); 
}catch(SQLException se){ 
	conn.rollback(savepoint1); 
}

1、要取消掉JDBC的自动提交：void setAutoCommit(boolean autoCommit)
2、执行各个SQL语句，加入到批处理之中
3、如果所有语句执行成功，则提交事务 commit()；如果出现了错误，则回滚：rollback()

try{
        connection.setAutoCommit(false);
        add(connection);
        // int i = 1/0; 
        sub(connection);
        System.out.println("===============");
        connection.commit();
    } catch(Exception e)
    {
        // TODO Auto-generated catch block 
        System.out.println("---------------");
        try {
            connection.rollback();
        } catch (SQLException e1) {
            // TODO Auto-generated catch block 
            e1.printStackTrace();
        }
    }

4.7.6批处理

Statement批处理
以下是使用语句对象的批处理的典型步骤序列

• 使用createStatement（）方法创建Statement对象。
• 使用setAutoCommit（）将auto-commit设置为false 。 - 使用addBatch（）方法在创建的语句对象上添加您喜欢的SQL语句到批处理中。
• 在创建的语句对象上使用executeBatch（）方法执行所有SQL语句。
• 最后，使用commit（）方法提交所有更改。

Statement stmt = conn.createStatement(); 
conn.setAutoCommit(false); 
//sql1 
String SQL = "INSERT INTO Employees (id, first, last, age) VALUES(200,'Zia', 'Ali', 30)"; 
stmt.addBatch(SQL); 
//sql2 
String SQL = "INSERT INTO Employees (id, first, last, age) VALUES(201,'Raj', 'Kumar', 35)"; 
stmt.addBatch(SQL); 
//sql3 
String SQL = "UPDATE Employees SET age = 35 WHERE id = 100"; 
stmt.addBatch(SQL); 
int[] count = stmt.executeBatch(); 
conn.commit();

PreparedStatement批处理

1. 使用占位符创建SQL语句。
2. 使用prepareStatement（） 方法创建PrepareStatement对象。
3. 使用setAutoCommit（）将auto-commit设置为false 。
4. 使用addBatch（）方法在创建的语句对象上添加您喜欢的SQL语句到批处理中。
5. 在创建的语句对象上使用executeBatch（）方法执行所有SQL语句。
6. 最后，使用commit（）方法提交所有更改。

String SQL = "INSERT INTO Employees (id, first, last, age) VALUES(?, ?, ?, ?)"; 
PreparedStatement pstmt = conn.prepareStatement(SQL); 
conn.setAutoCommit(false); 
// Set the variables 
pstmt.setInt( 1, 400 ); 
pstmt.setString( 2, "Pappu" ); 
pstmt.setString( 3, "Singh" ); 
pstmt.setInt( 4, 33 ); 
// Add it to the batch 
pstmt.addBatch(); 
// Set the variables 
pstmt.setInt( 1, 401 ); 
pstmt.setString( 2, "Pawan" ); 
pstmt.setString( 3, "Singh" ); 
pstmt.setInt( 4, 31 ); 
// Add it to the batch 
pstmt.addBatch(); 
//add more batches 
//Create an int[] to hold returned values 
int[] count = stmt.executeBatch(); 
//Explicitly commit statements to apply changes 
conn.commit();

4.8反射处理结果集

接口：

public interface FansheDao {
    //查询所有的学生信息
    public List<Student> findallstudent(Class cla);
}

实现类：

package jdbc.dao.impl;

import jdbc.bean.Student;
import jdbc.dao.FansheDao;

import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class FansheDaoImpl implements FansheDao {
    @Override
    public List<Student> findallstudent(Class cla) {
        Connection con = null;
        PreparedStatement pps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/kkb_test?serverTimezone=UTC", "zxy", "199904");
            List list = new ArrayList();
            String sqla = "select * from student";
            pps = con.prepareStatement(sqla);
            rs = pps.executeQuery();
            //得到数据库中的所有的列有哪些
            ResultSetMetaData metaData = rs.getMetaData();//存储结果集信息
            //返回数据库中的相关信息
            int count = metaData.getColumnCount();
            //得到列数
            String[] columnnames = new String[count];
            for (int i = 0; i < count; i++) {
                // System.out.println(metaData.getColumnName(i+1));//列的位置从1开始
                columnnames[i] = metaData.getColumnName(i + 1);
            }

            //得到实体类中的所有的方法
            Method[] methods = cla.getDeclaredMethods();
            while (rs.next()) {
                Object s = cla.newInstance();
                //调取无参构造创建对象
                for (String columnname : columnnames) {
                    String name = "set" + columnname;//setstuid
                    for (Method method : methods) {
                        if (method.getName().equalsIgnoreCase(name)) {
                            method.invoke(s, rs.getObject(columnname));//执行了对应的 set方法
                            break;
                        }
                    }
                }
                list.add(s);
            }
            System.out.println("执行成功");
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
}

测试：

FansheDao dao = new FansheDaoImpl();
List<Student> studentList = dao.findallstudent(Student.class);
for (Student student : studentList) {
    System.out.println(student.getStuid()+","+student.getStuname()+","+student.getTeacherid());
}

4.9工具类的定义

4.9.1 工具类DbUtils

package jdbc;

import java.sql.*;
import java.util.List;

public class DbUtils {
    //1.定义需要的工具类对象
    protected Connection connection = null;
    protected PreparedStatement pps = null;
    protected ResultSet rs = null;
    protected int k = 0;//受影响的行数
    private String url = "jdbc:mysql://localhost:3306/kkb_test?serverTimezone=UTC";
    private String username = "zxy";
    private String password = "199904";

    //2.加载驱动
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //3.获得连接
    protected Connection getConnection() {
        try {
            connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //4.创建通道
    protected PreparedStatement getPps(String sql) {
        try {
            pps = getConnection().prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pps;
    }

    //5.给占位符赋值 list中保存的是给占位符所赋的值
    private void setParams(List list) {
        try {
            if (list != null && list.size() > 0) {
                for (int i = 0; i < list.size(); i++) {
                    pps.setObject(i + 1, list.get(i));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //6.增删改调取的方法
    protected int update(String sql, List params) {
        try {
            getPps(sql);
            setParams(params);
            k = pps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return k;
    }

    //7.查询的时候调取一个方法
    protected ResultSet query(String sql, List list) {
        try {
            getPps(sql);
            setParams(list);
            rs = pps.executeQuery();
            return rs;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //8.关闭资源
    protected void closeall() {
        try {
            if (rs != null) {
                rs.close();
            }
            if (pps != null) {
                pps.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

由于方法都是protected类型，因此在使用该工具类时，只要实现类继承DbUtils后直接在里面调用对应方法即可。

4.9.2 properties文件

在src目录下创建db.properties文件。
properties文件保存数据库信息-特点：key-value存储方式

工具类中读取属性文件：
方法1：

 InputStream inputStream = DbUtils.class.getClassLoader().getResourceAsStream("db.properties");
 Properties properties = new Properties();
 properties.load(inputStream);
 driverName = properties.getProperty("driverclass");
 url = properties.getProperty("url");
 username = properties.getProperty("uname");
 password = properties.getProperty("password");

方法2：

 //参数只写属性文件名即可，不需要写后缀
 ResourceBundle bundle = ResourceBundle.getBundle("db");
 driverName = bundle.getString("driverclass");
 url = bundle.getString("url");
 username = bundle.getString("uname");
 password = bundle.getString("password");

4.10连接池

4.10.1数据库连接池原理

连接池基本的思想是在系统初始化的时候，将数据库连接作为对象存储在内存中，当用户需要访问数据库时，并非建立一个新的连接，而是从连接池中取出一个已建立的空闲连接对象。使用完毕后，用户也并非将连接关闭，而是将连接放回连接池中，以供下一个请求访问使用。而连接的建立、断开都由连接池自身来管理。同时，还可以通过设置连接池的参数来控制连接池中的初始连接数、连接的上下限数以及每个连接的最大使用次数、最大空闲时间等等，也可以通过其自身的管理机制来监视数据库连接的数量、使用情况等。

4.10.2自定义连接池（了解）

4.10.2.1概述

我们可以通过自定义的方式实现连接池!分析连接池类应该包含特定的属性和方法
属性：集合 放置Connection
方法：获取连接方法，回收连接方法

4.10.2.2 java规范实现连接池

Java为连接池实现提供了一个规范(接口)，规范的写法,我们需要实现DataSource接口
但是实现DataSource接口有一个弊端，没有提供回收链接方法。这里我们将使用装饰者模式

最小连接数:
是数据库一直保持的数据库连接数，所以如果应用程序对数据库连接的使用量不大，将有大量的数据库资源被浪费。
初始化连接数：
连接池启动时创建的初始化数据库连接数量。
最大连接数：
是连接池能申请的最大连接数，如果数据库连接请求超过此数，后面的数据库连接请求被加入到等待队列中。
最大等待时间：
当没有可用连接时，连接池等待连接被归还的最大时间，超过时间则抛出异常，可设置参数为0或者负数使得无限等待(根据不同连接池配置)。

**注1：**在DBCP连接池的配置中，还有一个maxIdle的属性，表示最大空闲连接数，超过的空闲连接将被释放，默认值为8。对应的该属性在Druid连接池已不再使用，配置了也没有效果，c3p0连接池则没有对应的属性。
**注2：**数据库连接池在初始化的时候会创建initialSize个连接，当有数据库操作时，会从池中取出一个连接。如果当前池中正在使用的连接数等于maxActive，则会等待一段时间，等待其他操作释放掉某一个连接，如果这个等待时间超过了maxWait，则会报错；如果当前正在使用的连接数没有达到maxActive，则判断当前是否空闲连接，如果有则直接使用空闲连接，如果没有则新建立一个连接。在连接使用完毕后，不是将其物理连接关闭，而是将其放入池中等待其他操作复用。

4.10.3 DBCP连接池

4.10.3.1概述

DBCP是一个依赖Jakarta commons-pool对象池机制的数据库连接池。DBCP可以直接的在应用程序中使用，Tomcat的数据源使用的就是DBCP。

4.10.3.2 DBCP连接池的使用

1. 导入相应jar包
   mysql-jdbc.jar
   commons-dbcp.jar
   commons-pool.jar
2. 硬编码使用DBCP

所谓的硬编码方式就是在代码中添加配置

加载驱动的static代码块中添加：

 //DBCP
 ResourceBundle bundle = ResourceBundle.getBundle("db");
 driverName = bundle.getString("driverclass");
 url = bundle.getString("url");
 username = bundle.getString("uname");
 password = bundle.getString("password");
 basicDataSource.setUsername(username);
 basicDataSource.setPassword(password);
 basicDataSource.setUrl(url);
 basicDataSource.setDriverClassName(driverName);

从而在获得链接时直接：

connection = basicDataSource.getConnection();

3. 软编码使用DBCP

所谓的软编码,就是在项目中添加配置文件,这样就不需要每次代码中添加配合!

1.项目中添加配置
文件名称: info.properties
文件位置: src下

#连接设置 
driverClassName=com.mysql.jdbc.Driver 
url=jdbc:mysql://localhost:3306/day2 
username=root 
password=111 
#<!-- 初始化连接 --> 
initialSize=10 
#最大连接数量 
maxActive=50 
#<!-- 最大空闲连接 --> 
maxIdle=20 
#<!-- 最小空闲连接 --> 
minIdle=5 
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 --> 
maxWait=6000

2.DButils工具类 代码实现

//1.创建dbcp的工具类对象
static BasicDataSource datasource = new BasicDataSource();
//2.加载驱动
static {
    try {
        //加载属性文件
        // 1.使用工具类 ，参数是属性文件的文件名（不要加后缀）
        ResourceBundle bundle = ResourceBundle.getBundle("db");
        driverClass = bundle.getString("driverclass");
        url = bundle.getString("url");
        username = bundle.getString("uname");
        password = bundle.getString("upass");
        init = bundle.getString("initsize");
        //2.将驱动地址等信息传递给dbcp
        datasource.setDriverClassName(driverClass);
        datasource.setUrl(url);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setInitialSize(Integer.parseInt(init));
    } catch (Exception e) {
        e.printStackTrace();
    }
}
//3.获得连接 
public static Connection getConn() {
    try {
        con = datasource.getConnection();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return con;
}

4.10.4 C3P0连接池

c3p0是一个开放源代码的JDBC连接池，它在lib目录中与Hibernate一起发布,包括了实现jdbc3和jdbc2扩展规范说明的Connection 和Statement 池的DataSources 对象。

c3p0与dbcp区别
1.
dbcp没有自动回收空闲连接的功能
c3p0有自动回收空闲连接功能
2.
dbcp需要手动设置配置文件
c3p0不需要手动设置

使用：
1.添加配置文件

c3p0是在外部添加配置文件,工具直接进行应用,因为直接引用,所以要求固定的命名和文件位置
文件位置: src
文件命名:c3p0-config.xml/c3p0-config.properties

代码：

<?xml version="1.0" encoding="utf-8"?>
<c3p0-config>
    <!-- 默认配置，如果没有指定则使用这个配置 -->
    <default-config>
        <!-- 基本配置 -->
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/kkb_test?serverTimezone=UTC</property>
        <property name="user">zxy</property>
        <property name="password">199904</property>
        <!--扩展配置-->
        <!-- 连接超过30秒报错-->
        <property name="checkoutTimeout">30000</property>
        <!--30秒检查空闲连接 -->
        <property name="idleConnectionTestPeriod">30</property>
        <property name="initialPoolSize">10</property>
        <!-- 30秒不适用丢弃-->
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
        <property name="maxStatements">200</property>
    </default-config>
</c3p0-config>

接下来导入jar包，并在DBUtils中创建对象：

//C3P0
private ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();

获得连接：

//C3P0
connection = comboPooledDataSource.getConnection();

4.10.5 Druid(德鲁伊)连接池

Druid 是目前比较流行的高性能的，分布式列存储的OLAP框架(具体来说是MOLAP)。它有如下几个特点：
一. 亚秒级查询
druid提供了快速的聚合能力以及亚秒级的OLAP查询能力，多租户的设计，是面向用户分析应用的理想方式。
二.实时数据注入
druid支持流数据的注入，并提供了数据的事件驱动，保证在实时和离线环境下事件的实效性和统一性
三.可扩展的PB级存储
druid集群可以很方便的扩容到PB的数据量，每秒百 万级别的数据注入。即便在加大数据规模的情况下，也能保证时其效性
四.多环境部署
druid既可以运行在商业的硬件上，也可以运行在云上。它可以从多种数据系统中注入数据，包括hadoop，spark，kafka，storm和samza等
五.丰富的社区
druid拥有丰富的社区，供大家学习

使用步骤：
1.导入jar包
2.加载驱动

//德鲁伊
ResourceBundle bundle = ResourceBundle.getBundle("db");
driverName = bundle.getString("driverclass");
url = bundle.getString("url");
username = bundle.getString("uname");
password = bundle.getString("password");
druidDataSource.setUsername(username);
druidDataSource.setPassword(password);
druidDataSource.setUrl(url);
druidDataSource.setDriverClassName(driverName);

获得连接：

//德鲁伊
connection = druidDataSource.getConnection();