java基础的系统性学习或者说复习已经告一段落，正式开始数据库学习，当然基础部分的内容还是不能丢下，要时长复习，尚存的问题还有很多：数据库的优化（索引），悲观锁和乐观锁，Map的底层原理红黑树等！

1. 数据库简介

1.1 简介

数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。

数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。

数据库软件应该为数据库管理系统，数据库是通过数据库管理系统创建和操作的。

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

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.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.4 MySQL安装和卸载

1.4.1 安装

步骤1:访问地址:https://dev.mysql.com/downloads/mysql/

步骤2:下载压缩包，下载后解压，放在非C盘下，将解压文件夹下的bin路径添加到系统变量path中

步骤3:在mysql文件夹下找到my.ini或my-default.ini，如果没有.ini结尾的文件,直接创建该文件。新增内容为如下，注意basedir和datadir是我自己的路径位置，自定义。记得新增一个文件Data文件夹

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\mysql
# 设置mysql数据库的数据的存放目录
datadir=D:\mysql\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

步骤4:安装mysql
在mysql的安装目录中，打开bin文件夹，运行cmd.执行初始化数据库的指令：

1	mysqld --initialize --console

root用户的初始化密码:d?fwd_+ku2#M、d_g60Sd%geQg、66MV(e2l1E6n

要是你不小心关掉cmd，或者没记住，那也没事，删掉初始化的 datadir 目录，再执行一遍初始化命令，又会重新生成的。

步骤5:安装服务
在MySQL安装目录的 bin 目录下执行命令：
mysqld --install [服务名]这里的服务名默认是mysql，可以自定义

如果提示上述错误，需要关闭cmd,重新打开，使用管理员身份执行

安装完成之后
通过命令net start mysql启动MySQL的服务了。
通过命令net stop mysql停止服务。

注意:安装时，卸载其他版本的mysql数据库
步骤6:链接数据库

修改账户密码:

alter user 'root'@'localhost' identified with mysql_native_password BY '新密码';

修改密码，注意命令尾的分号一定要有，这是mysql的语法

步骤7:退出数据库

1.4.2 卸载

步骤1:使用管理员身份运行cmd,关闭mysql服务

步骤2:删除mysql服务

1	命令:sc delete mysql 或者 mysqld remove mysql

步骤3:刪除mysqlDB目录文件（安裝mysql时my.ini指定的目录）

1.5 数据库的导入和导出

导入：

1
2
3

mysql -u root -p输入密码登录mysql
use yhp;进入数据库
source d:/student.sql；将路径下的数据库文件导入

导出：

1	mysqldump -uroot -p yhp >D:/student.sql将数据库文件导出到指定路径

2. SQL语言

2.1 概述

什么是SQL

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

SQL语句分类

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

注意：sql语句以;结尾。mysql中的关键字不区分大小写

2.2 DDL(数据定义)

1 创建
CREATE DATABASE语句用于创建新的数据库：

编码方式:gb2312,utf-8,gbk,iso-8859-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;

2 查看数据库
查看当前数据库服务器中的所有数据库

1	show databases;

查看前面创建的mydb2数据库的定义信息

1 2	//show create database 数据库名; Show CREATE DATABASE mydb2;

3 修改数据库

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

查看服务器中的数据库，并把mydb2的字符集修改为utf8;

1	ALTER DATABASE mydb2 character SET utf8;

4 删除数据库
drop database 数据库名

1	DROP DATABASE mydb3;

5 其他语句
查看当前使用的数据库

1	Select database();

切换数据库: use 数据库名

1	USE mydb2;

2.3 DDL操作表

2.3.1 创建新表:

语法：

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

说明:表名，列名是自定义，多列之间使用逗号间隔，最后一列的逗号不能写
[约束] 表示可有可无
示例：

CREATE TABLE Employees(
id INT ,
age INT ,
first VARCHAR(255),
last VARCHAR(255)
);

常用数据类型：

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

2.3.2 CRUD

查:

当前数据库中的所有表：show tables;

查看表的字段信息：desc 表名；

查看表的创建细节：show create table 表名;

查看表的全部内容：select * from 表名
增：

增加列：alter table 表名 add 新列名新的数据类型
改：

修改列：alter table 表名 change 旧列名新列名新的数据类型

修改表名：alter table 旧表名 rename 新表名;

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

删除列：alter table 表名 drop 列名；

删除表：drop table 表名；

2.4 DML（数据操作）

DML是对表中的数据进行增、删、改的操作。不要与DDL混淆了。
主要包括：INSERT 、UPDATE、 DELETE
小知识：
在mysql中，字符串类型和日期类型都要用单引号括起来。
空值：null

sql中的运算符:

算术运算符：+，-，*，/(除法),求余(%)
赋值运算符：=
逻辑运算符：and(并且),or(或者),not（取非）
作用:用于连接多个条件时使用
关系运算符：

>,<,>=,<=,!=(不等于),=(等于),<>(不等于)

2.4.1 插入操作：INSERT

常用命令：

insert into 表名(列名) values(数据值);

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

注意:

1多列和多个列值之间使用逗号隔开

2.列名要和列值一一对应

3.非数值的列值两侧需要加单引号
常见错误: 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
插入的日期和字符一样，都使用引号括起来。

2.4.2 修改操作：update

UPDATE 表名 SET 列名1=列值1,列名2=列值2 ... WHERE 列名=值

实例：

将所有员工薪水修改为5000元。
将姓名为’zhangssan’的员工薪水修改为3000元。
将姓名为’lisi’的员工薪水修改为4000元,resume改为ccc。
将'你好'的薪水在原有基础上增加1000元。

mysql> update emp set salary=5000;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> update emp set salary=3000 where name=zhangsan;
ERROR 1054 (42S22): Unknown column 'zhangsan' in 'where clause'
mysql> update emp set salary=3000 where name='zhangsan';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update emp set salary=4000,resume='ccc' where name='lisi';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>  update emp set salary=salary+1000 where name='你好';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp;
+----+----------+--------+------------+---------+------------+----------+
| id | name     | gender | birthday   | salary  | entry_date | resume   |
+----+----------+--------+------------+---------+------------+----------+
|  1 | zhangsan | female | 1990-05-10 | 3000.00 | 2015-05-05 | goodgirl |
|  2 | lisi     | male   | 1995-05-10 | 4000.00 | 2015-05-05 | ccc      |
|  3 | 你好     | male   | 1995-05-10 | 6000.00 | 2015-05-05 | good boy |
+----+----------+--------+------------+---------+------------+----------+
3 rows in set (0.00 sec)

2.4.3 删除操作：DELETE

DELETE from 表名【WHERE 列名=值】或TRUNCATE table 表名

两种删除方法的区别（重要）

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

2.5 DCL（数据控制）

2.5.1 创建用户

create user 用户名@指定ip identified by 密码;

1	create user test123@localhost IDENTIFIED by 'test123'

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

1	create user test456@10.4.10.18 IDENTIFIED by 'test456'

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

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

2.5.2 用户授权

给指定用户授予指定数据库指定权限：

grant 权限1,权限2,........,权限n on 数据库名.* to 用户名@IP;
1
2
grant select,insert,update,delete,create on
chaoshi.* to 'test456'@'127.0.0.1';
给指定用户授予所有数据库所有权限：

grant all on *.* to 用户名@IP
1
grant all on *.* to 'test456'@'127.0.0.1'

2.5.3 用户权限查询

show grants for 用户名@IP;

1	show grants for 'root'@'%';

2.5.4 撤销用户权限

revoke 权限1,权限2,........,权限n on 数据库名.* from 用户名@IP;

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

2.5.5 删除用户

drop user 用户名@IP;

1	drop user test123@localhost;

2.6 DQL数据查询

数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。查询返回的结果集是一张虚拟表。

查询关键字：SELECT

语法： SELECT 列名 FROM 表名【修饰条件——见下表】

列名可以用*代替，表示所有列！！

SELECT 要查询的列名称
FROM 表名称
WHERE 限定条件 /*行条件*/
GROUP BY grouping_columns /*对结果分组*/
HAVING condition /*分组后的行条件*/
ORDER BY sorting_columns /*对结果分组*/
LIMIT offset_start, row_count /*结果限定*/

2.6.1 简单查询

查询所有列

SELECT * FROM stu;

查询指定列

SELECT sid, sname, age FROM stu;

2.6.2 条件查询

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
=、!=、<>、<、<=、>、>=; BETWEEN…AND； IN(set)； IS NULL； AND；OR； NOT；

IN的用法：

查询学号为S_1001，S_1002，S_1003的记录:
SELECT * FROM stu WHERE sid IN ('S_1001','S_1002','S_1003');
查询学号不是S_1001，S_1002，S_1003的记录
SELECT * FROM tab_student WHERE sid NOT IN('S1001','S1002','S_1003');

null的用法

1 2	查询年龄为null的记录 SELECT * FROM stu WHERE age IS NULL;

between and的用法(包含临界值)

查询年龄在20到40之间的学生记录
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;
==
SELECT * FROM stu WHERE age>=20 AND age<=40;

“非”的条件判断

查询性别非男的学生记录:
1.SELECT * FROM stu WHERE gender!='male';
2.SELECT * FROM stu WHERE NOT gender='male';
3.SELECT * FROM stu WHERE gender<>'male';

2.6.3 模糊查询

百度搜索就是比较典型的模糊查询

当想查询“姓名中包含a字母的学生“时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。

列名 like '表达式'//表达式必须是字符串

1
2
3

通配符:(类似正则表达)
_(下划线): 任意一个字符
%：任意0~n个字符,'张%'

实例：

(1)查询姓名由3个字构成的学生记录
SELECT * FROM stu WHERE sname LIKE '___';
(2)查询姓名由5个字母构成，并且第5个字母为“i”的学生记录
SELECT * FROM stu WHERE sname LIKE '____i';
(3)查询姓名中第2个字母为“i”的学生记录
SELECT * FROM stu WHERE sname LIKE '_i%';

2.6.4 字段控制查询

(1)去除重复记录
去除重复记录（两行或两行以上记录的数据相同），例如stu表中gender字段就存在相同的记录(只有男和女)。当只查询stu表的gender字段时，那么会出现重复记录，那么想去除重复记录，需要使用
DISTINCT

1	SELECT DISTINCT gender FROM stu;

# 查询性别列，去除重复记录
mysql> select distinct gender from stu;
+--------+
| gender |
+--------+
| male   |
| female |
| NULL   |
+--------+
3 rows in set (0.04 sec)
# 执行加法运算，排除空值得可能
mysql> select 100+ifnull(age,0) from stu;
+-------------------+
| 100+ifnull(age,0) |
+-------------------+
|               135 |
|               115 |
|               195 | 
+-------------------+
11 rows in set (0.00 sec)
# 执行加法运算，同时对新的列重命名
mysql> select 100+ifnull(age,0) As total from stu;
+-------+
| total |
+-------+
|   135 |
|   115 |
|   195 |
+-------+
11 rows in set (0.00 sec)

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

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

2.6.5 排序

order by 列名 asc/desc

//asc：升序； desc：降序；默认不写的话是升序

多列排序：当前面的列的值相同的时候，才会按照后面的列值进行排序

1 2	查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序 SELECT * FROM emp ORDER BY sal DESC,empno ASC;

2.6.6 聚合函数

聚合函数是用来做纵向运算的函数：

COUNT(列名)：统计指定列不为NULL的记录行数；
MAX(列名)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN(列名)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM(列名)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG(列名)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

1.count的用法

查询emp表中记录数
1
SELECT COUNT(*) AS cnt FROM emp;
查询emp表中有佣金的人数
1
SELECT COUNT(comm) cnt FROM emp;
注意：因为count()函数中给出的是comm列，那么只统计comm列非NULL的行数。

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

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

2.SUM和AVG

查询所有雇员月薪和，以及所有雇员佣金和
1
SELECT SUM(sal), SUM(comm) FROM emp;
统计所有员工平均工资
1
SELECT AVG(sal) FROM emp;

3.MAX和MIN

查询最高工资和最低工资
1
SELECT MAX(sal), MIN(sal) FROM emp;

2.6.7 分组查询

group by
注意:如果查询语句中有分组操作，则select后面能添加的只能是聚合函数和被分组的列名

查询每个部门的部门编号以及每个部门的人数：
1
select deptno,count(*) from emp group by deptno;
查询每个部门的部门编号以及每个部门工资大于1500的人数
1
select deptno,count(*) from emp where sal>1500 group by deptno;

多列分组

group by 列名，列名

统计出stu表中每个班级的男女生各多少人

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

2.6.8 having

类似于where 的条件语句（或者说过滤语句）

注：having与where的区别:

having是在分组后对数据进行过滤，where是在分组前对数据进行过滤
having后面可以使用分组函数(统计函数)，where后面不可以使用分组函数。
WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

最关键的一点：having的出现必然伴随着group by语句

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

1	select deptno,sum(sal) from emp group by deptno having sum(sal)>9000;

2.6.9 limit（分页)

LIMIT用来限定查询结果的起始行，以及总行数。

limit 开始下标,显示条数；//开始下标从0开始
limit 显示条数；//表示默认从0开始获取数据

查询10行记录，起始行从3开始
1
SELECT* FROM emp limit 3,10;

页数多就会出现一个问题，如果一页记录为10条，希望查看第3页记录应该怎么查呢？

l 第一页记录起始行为0，一共查询10行； limit 0,10
l 第二页记录起始行为10，一共查询10行；limit 10,10
l 第三页记录起始行为20，一共查询10行； limit 20,10

此时可以通过函数的方式简单解决：

pageIndex 页码值 pageSize 每页显示条数

1	select * from stu limit (pageindex-1)*pagesize, pagesize;

2.6 总结

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

2.7 客户端工具

4.1 基于Navicat实现数据库操作
4.2 基于SQLyog实现数据库操作

3. 数据库高级

3.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)
);

3.2 实体完整性（记录）

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

主键约束（primary key）（特点：数据唯一，且不能为null）

注：每个表中要有一个主键。

3种添加主键的方式：

#常规
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);

唯一约束(unique)（特点：数据唯一，且不能为null）

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

自动增长列(auto_increment)

sqlserver数据库 (identity-标识列) // oracle数据库(sequence-序列)
注意：给主键添加自动增长的数值，列只能是整数类型
1
2
CREATE TABLE student( Id int primary key auto_increment, Name varchar(50) );
INSERT INTO student(name) values(‘tom’);
此时再添加记录时不需要在填入id的值，可以自动增长

3.3 域完整性

域：代表当前单元格（单元格）

域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较

域完整性约束（4）：数据类型，非空约束（not null），默认值约束(default) ，check约束（mysql不支持，check(sex=’男’ or sex=’女’)

3.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 744073 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类型有专有的自动更新特性

| 类型 | 大小(字节) | 范围 | 格式 | 用途 |
| ————- | ————— | —————————————————————————————— | —————————- | ————————————————————————————— |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 ,当更新数据的时候自动添加更新时间 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| 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 | 年份值 |
字符串类型

字符串类型指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类型，有相同的最大长度和存储需求

3.3.2 非空和默认约束（not null/default）

not null

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

default

1
2
3

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

3.4 引用完整性

外键约束：FOREIGN KEY

外键列的数据类型一定要与主键的类型一致

constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)

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)
);

1	ALTER TABLE score ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id)

4. 多表查询（联查）

多个表之间是有关系的，那么关系靠谁来维护?
多表约束：外键列

4.1 多表的关系

4.1.1 一对多/多对一关系

客户和订单，分类和商品，部门和员工

站在客户的角度，就是一对多，换位置站在订单的角度就是多对一！

一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键

以雇员和部门为例：
部门表（部门id，部门名称）
员工表（员工id，员工名称）
如果按一到多的形式添加外键（将员工id作为外键添加到部门表）：则部门表一定出现重复
如果部门的主键id字段传入员工表作为外键，此时只需要外键重复，员工记录无需重复

4.1.2 多对多关系

学生和课程的关系

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

课程表的主键classid，学生表的主键stuid
中间表middle的内容如何建立：
主键：middleid
映射：stuid，classid
对于stuid为1的学生，学习哪几门课程，就将classid填入

4.1.3 一对一关系

夫妻关系

在实际的开发中应用不多.因为一对一可以创建成一张表.

两种建表原则：

唯一外键对应：在一方创建一个外键指向另一方的主键，但这时候仍可能出现一对多的问题，此时，可以对外键设置为unique约束

主键对应：让一对一的双方的主键建立关系（一方的主键同时也是外键，和另一方的主键挂钩），此时，主键自身的无重复就取代了unique约束，也不用再额外创建外键列

4.2 多表查询方式

多表查询有如下几种：

合并结果集：UNION、 UNION ALL
连接查询
- 内连接 [INNER] JOIN ON
- 外连接 OUTER JOIN ON
  - 左外连接 LEFT [OUTER] JOIN
  - 右外连接 RIGHT [OUTER] JOIN
  - 全外连接（MySQL不支持）FULL JOIN
- 自然连接 NATURAL JOIN
子查询

4.2.1 合并结果集

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

前提：两个select语句的列数和列的类型相同

合并结果集有两种方式：

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

4.2.2 连接查询

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。

select * from t1,t2;

连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

实例：emp-员工表，dept-部门表

笛卡尔积结果

CREATE TABLE dept1(
    deptno int primary key,
    dname varchar(14),
    loc varchar(13)
);
insert into dept1 values(10,'服务部','北京');
insert into dept1 values(20,'研发部','北京');
insert into dept1 values(30,'销售部','北京');
insert into dept1 values(40,'主管部','北京');
CREATE TABLE emp1(
    empno int,
    ename varchar(50),
    job varchar(50),
    mgr int,
    hiredate date,
    sal double,
    comm double,
    deptno int
);
insert into emp1 values(1001,'张三','文员',1006,'2019-1-1',1000,2010,10);
insert into emp1 values(1002,'李四','程序员',1006,'2019-2-1',1100,2000,20);
insert into emp1 values(1003,'王五','程序员',1006,'2019-3-1',1020,2011,20);
insert into emp1 values(1004,'赵六','销售',1006,'2019-4-1',1010,2002,30);
insert into emp1 values(1005,'张猛','销售',1006,'2019-5-1',1001,2003,30);
insert into emp1 values(1006,'谢娜','主管',1006,'2019-6-1',1011,2004,40);
select * from emp,dept;

可以看到笛卡尔积的重复情况是非常可怕的

使用主外键关系做为条件来去除无用信息
1
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
上面查询结果会把两张表的所有列都查询出来，也许你不需要那么多列，这时就可以指定要查询的列了。
1
2
3
SELECT emp.ename,emp.sal,emp.comm,dept.dname
FROM emp,dept
WHERE emp.deptno=dept.deptno;

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

4.2.3 连接查询-内联查询

语法1：

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

等价于：

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

注意点：

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

实例：

//查询学生表中的学生姓名和分数表中的分数
select name,score from student as s
inner join scores as c
on s.studentid=c.stuid
等价于：
select name,score from student as s,scores as c
where s.studentid=c.stuid

三表联查

语法：
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.列名

上述语法中提到的“表1或表2”，指三表中和另外两张表都有联系的表，作为桥梁
内联查询的特点：查询结果必须满足条件，这也意味着表中不满足条件的数据会被抛弃

4.3.3 连接查询-外联查询

外连接的特点：查询出的结果存在不满足条件的可能（主表的数据被全数保存下来）

使用场景：一般会作为子查询的语句使用

左外联：select 列名 from 主表 left join 次表 on 主表.列名=次表.列名

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

主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示null
主表和次表不能随意调换位置

实例：

以上面雇员和部门的两张表为例，在雇员中添加一行员工记录，部门50（部门表中不存在）

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

以员工表为主表进行外联查询，同样以部门的等值关系作为条件，如图，不符合条件的50部门同样能够显示

同样可以把主表和次表按照右外联进行组织，部门表在前，雇员表在后

连接查询心得：

连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。

两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。

4.3.4 连接查询-自然连接

自然连接（NATURAL INNER JOIN）：

自然连接是一种特殊的等值连接，他要求两个关系表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列，下面给出几个例子。

1	select * from emp e natural join dept d;

4.3.5 子查询

一个select语句中包含另一个完整的select语句。

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

子查询出现的位置：
- where后，作为条为被查询的一条件的一部分；
  
  当子查询出现在where后作为条件时，还可以使用如下关键字：
  - any
  - all
- from后，作表；(select查询语句本来就会产生一张虚拟表)
子查询结果集的形式：
- 单行单列（用于条件）
- 单行多列（用于条件）
- 多行单列（用于条件）
- 多行多列（用于表）
实例：工资高于30号部门所有人的员工信息
1
SELECT * FROMemp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);
查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。

第一步：查询30部门所有人工资
1
SELECT sal FROM emp WHERE deptno=30;
第二步：查询高于30部门所有人工资的员工信息
1
2
SELECT * FROM emp WHERE sal > ALL (第一步);
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30;

5. 扩展

核心：多表操作必然是要先建立多表关系（内连，外联，自然）

5.1 多表更新（多表联调）

update 表1,表2 set 列名=列值 where 表1.列名=表2.列名 and 其他限定条件
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='李四'

意义：将employee表和salary表中的李四对应的title和salary值进行更改

5.2 多表删除

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

实例：

//删除人事部的信息

1 2	delete d,e,s from department d,employee e,salary s where d.depid=e.depid and s.empid=e.empid and depname='人事部'

5.3 日期运算函数

now() 获得当前系统时间
year，month，day(日期值字段) 获得日期值中的相关数据
date_add(日期,interval 计算值计算的字段);
注：计算值大于0表示往后推日期，小于0表示往前推日期
示例：
date_add(now(),interval -40 year);//40年前的日期

6. 数据库优化

https://www.zhihu.com/question/36431635/answers/updated

对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
- 补充：索引的分类：主键索引，唯一索引，普通索引，全文索引（MYSQL的索引）
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。
应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。
应尽量避免在 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'
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 是一个好的选择

7. 数据库进阶-事务

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

7.1 事务的语法

启动：start transaction; begin;
提交：commit; 使得当前的修改确认
回滚：rollback; 使得当前的修改被放弃

注意：当前数据库引擎为innoDB，其余引擎不支持

可以看出和git的操作很接近，也就是对事务流程进行控制

前面提到的数据库数据的增删改查语句，是自动实现提交的

select * from grade;
#自动提交
insert into grade values(4,'aa');
#手动提交
begin；
insert into grade values(5,'bb');#此时临时添加数据到内存，未添加到数据库
commit;
#回滚
begin;
insert into grade values(6,'cc');
rollback；#在事务提交之前都可以回滚

7.2 事务的特性-ACID

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

7.3 事务的并发问题

脏读：

读取到了没有提交的数据, 事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。

不可重复读：

同⼀条命令返回不同的结果集（更新）.事务 A 多次读取同一数据，事务 B 在事务A 多次读取的过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，结果不一致。

幻读：

重复查询的过程中，数据就发⽣了量的变化（insert， delete）

7.4 事务的隔离级别-解决并发问题

4种事务隔离级别从上往下，级别越高，并发性越差，安全性就越来越高。⼀般数据默认级别是读以提交或可重复读
查看当前会话中事务的隔离级别

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.93 sec)

设置当前会话中的事务隔离级别

1 2	mysql> set session transaction isolation level read uncommitted; Query OK, 0 rows affected (0.00 sec)

.读未提交（READ_UNCOMMITTED）
读未提交，该隔离级别允许脏读取，其隔离级别是最低的。换句话说，如果一个事务正在处理理某一数据，并对其进⾏了更新，但同时尚未完成事务，因此还没有提交事务；而以此同时，允许另一个事务也能够访问该数据。
脏读示例：
在事务A和事务B同时执行时可能会出现如下场景：

余额应该为1500元才对。请看T5时间点，事务A此时查询的余额为0，这个数据就是脏数据，他是事务B 造成的，很明显是事务没有进行隔离造成的。
读已提交（READ_COMMITTED）
读已提交是不同的事务执行的时候只能获取到已经提交的数据（未提交的数据不被承认，避免了回滚的影响）。这样就不会出现上面的脏读的情况了。但是在同一个事务中执行同一个读取,结果不一致不可重复读示例

虽然解决了脏读问题，但是还是解决不了可重复读问题。

事务A其实除了了查询两次以外，其它什什么事情都没做，结果钱就从1000变成0了了，这就是不不可重复读的问题。
可重复读（REPEATABLE_READ）
可重复读就是保证在事务处理理过程中，多次读取同一个数据时，该数据的值和事务开始时刻是一致的。

核心：以事务A为例，只有本身的修改（update）才会改变查询结果，并发事务的修改提交都不会对自己的查询结果造成影响！！！

只有当事务A也进行提交后，结果为获取到已经提交的数据

但是：刚才所提到的修改是有限制的，对于数据量上的变化（insert，delete）仍会对查询结果造成影响！！！

因此该事务级别限制了不可重复读和脏读，但是有可能出现幻读的数据。
幻读
幻读就是指同样的事务操作，在前后两个时间段内执行对同一个数据项的读取，可能出现不一致的结果。诡异的更新事件

7.5 不同的隔离级别的锁的情况(了解)

读未提交（RU）: 有行级的锁，没有间隙锁。它与RC的区别是能够查询到未提交的数据。
读已提交（RC）：有行级的锁，没有间隙锁，读不到没有提交的数据。
可重复读（RR）：有行级的锁，也有间隙锁，每次读取的数据都是一样的，但可能出现幻读的情况。
序列化（S）：有行级锁，也有间隙锁，读表的时候，就已经上锁了

7.6 乐观锁和悲观锁

7.6.1 概念

并发控制：

常说的并发控制，一般都和数据库管理系统(DBMS)有关。在 DBMS 中的并发控制的任务，是确保在多个事务同时存取数据库中同一数据时，不破坏事务的隔离性、一致性和数据库的统一性。

实现并发控制的主要手段大致可以分为乐观并发控制和悲观并发控制两种。
首先要明确：无论是悲观锁还是乐观锁，都是人们定义出来的概念，可以认为是一种思想。其实不仅仅是关系型数据库系统中有乐观锁和悲观锁的概念，像 hibernate、tair、memcache 等都有类似的概念。所以，不应该拿乐观锁、悲观锁和其他的数据库锁等进行对比。乐观锁比较适用于读多写少的情况(多读场景)，悲观锁比较适用于写多读少的情况(多写场景)。

7.6.2 悲观锁(Pessimistic Lock)

啥是悲观锁（咋悲观的）

当要对数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，最好的办法就是直接对该数据进行加锁以防止并发。

这种借助数据库锁机制，在修改数据之前先锁定，再修改的方式被称之为悲观并发控制【Pessimistic Concurrency Control，缩写“PCC”，又名“悲观锁”】

悲观锁，正如其名，具有强烈的独占和排他特性。

它指的是对数据被外界事务(包括本系统当前的其他事务，以及来自外部系统的事务处理)修改持保守态度。因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据)。

之所以叫做悲观锁，是因为这是一种对数据的修改持有悲观态度的并发控制方式。总是假设最坏的情况，每次读取数据的时候都默认其他线程会更改数据，因此需要进行加锁操作，当其他线程想要访问数据时，都需要阻塞挂起。悲观锁的实现：

传统的关系型数据库使用这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
Java 里面的同步 synchronized 关键字的实现。

悲观锁的分类

共享锁【shared locks】又称为读锁，简称S锁。顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

排他锁【exclusive locks】又称为写锁，简称X锁。顾名思义，排他锁就是不能与其他锁并存，如果一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据行读取和修改。

缺点

即线程加锁的缺点，在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会。另外还会降低并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数据。

7.6.3 乐观锁(Optimistic Locking)

乐观锁是相对悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回给用户错误的信息，让用户决定如何去做。乐观锁适用于读操作多的场景，这样可以提高程序的吞吐量。

和git类似的版本检测，冲突抛出的功能

乐观锁机制采取了更加宽松的加锁机制。乐观锁是相对悲观锁而言，也是为了避免数据库幻读、业务处理时间过长等原因引起数据处理错误的一种机制，但乐观锁不会刻意使用数据库本身的锁机制，而是依据数据本身来保证数据的正确性。乐观锁的实现：

CAS 实现：Java 中java.util.concurrent.atomic包下面的原子变量使用了乐观锁的一种 CAS 实现方式。
版本号控制：一般是在数据表中加上一个数据版本号 version 字段，表示数据被修改的次数。当数据被修改时，version 值会+1。当线程A要更新数据值时，在读取数据的同时也会读取 version 值，在提交更新时，若刚才读取到的 version 值与当前数据库中的 version 值相等时才更新，否则重试更新操作，直到更新成功

乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。

7.6.4 两种锁的具体实现

悲观锁

悲观锁的实现，往往依靠数据库提供的锁机制。在数据库中，悲观锁的流程如下：
1. 在对记录进行修改前，先尝试为该记录加上排他锁(exclusive locks)。
2. 如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定。
3. 如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。
4. 期间如果有其他对该记录做修改或加排他锁的操作，都会等待解锁或直接抛出异常。
实例：拿比较常用的 MySql Innodb 引擎举例，来说明一下在 SQL 中如何使用悲观锁。

以上，在对id = 1的记录修改前，先通过 for update 的方式进行加锁，然后再进行修改。这就是比较典型的悲观锁策略。

如果以上修改库存的代码发生并发，同一时间只有一个线程可以开启事务并获得id=1的锁，其它的事务必须等本次事务提交之后才能执行。这样可以保证当前的数据不会被其它事务修改。

上面提到，使用 select…for update 会把数据给锁住，不过需要注意一些锁的级别，MySQL InnoDB 默认行级锁。行级锁都是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意。
乐观锁

主要就是两个步骤：冲突检测和数据更新。比较典型的就是 CAS (Compare and Swap)。

CAS 即比较并交换。是解决多线程并行情况下使用锁造成性能损耗的一种机制，CAS 操作包含三个操作数——内存位置(V)、预期原值(A)和新值(B)。如果内存位置的值(V)与预期原值(A)相匹配，那么处理器会自动将该位置值更新为新值(B)。否则，处理器不做任何操作。无论哪种情况，它都会在 CAS 指令之前返回该位置的值。CAS 有效地说明了“我认为位置(V)应该包含值(A)。如果包含该值，则将新值(B)放到这个位置；否则，不要更改该位置，只告诉我这个位置现在的值即可”。Java 中，sun.misc.Unsafe 类提供了硬件级别的原子操作来实现这个 CAS。java.util.concurrent包下大量的类都使用了这个 Unsafe.java 类的 CAS 操作。

当多个线程尝试使用 CAS 同时更新同一个变量时，只有其中一个线程能更新变量的值，而其它线程都失败，失败的线程并不会被挂起，而是被告知这次竞争中失败，并可以再次尝试。比如前面的扣减库存问题，通过乐观锁可以实现如下：

乐观锁使用

在更新之前，先查询一下库存表中当前库存数(quantity)，然后在做 update 的时候，以库存数作为一个修改条件。当提交更新的时候，判断数据库表对应记录的当前库存数与第一次取出来的库存数进行比对，如果数据库表当前库存数与第一次取出来的库存数相等，则予以更新，否则认为是过期数据

7.6.5 CAS的理解

https://blog.csdn.net/ls5718/article/details/52563959

8. JDBC

8.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查询。
查看和修改生成的记录。

JDBC体系结构

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

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

JDBC核心组件

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

8.2 使用步骤

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

导入包：需要导入包含数据库编程所需的JDBC类的包。大多数情况下，使用import java.sql.*就足够了。

注册JDBC驱动程序：要求您初始化驱动程序，以便您可以打开与数据库的通信通道。

//反射式加载
Class.forName("com.mysql.cj.jdbc.Driver");
//静态方法
Driver myDriver = new com.mysql.cj.jdbc.Driver();
DriverManager.registerDriver( myDriver );

打开连接：需要使用DriverManager.getConnection（）方法创建一个Connection对象，该对象表示与数据库的物理连接。
1
Connection conn = DriverManager.getConnection(url,userName,passWord);
注意：不同数据库执行驱动和连接的差异：驱动地址和连接地址不同

| RDBMS | JDBC驱动程序名称 | 网址格式 |
| ——— | ———————————————- | ————————————————————————————— |
| MYSQL8 | com.mysql.cj.jdbc.Driver | jdbc:mysql://hostname:3306/databaseName?serverTimezone=UTC |
| MySQL | com.mysql.jdbc.Driver | jdbc:mysql://hostname:3306/databaseName |
| ORACLE | oracle.jdbc.driver.OracleDriver | jdbc:oracle:thin:@hostname:port Number：databaseName |
| DB2 | COM.ibm.db2.jdbc.net.DB2Driver | jdbc:db2:hostname:port Number / databaseName |
| SYBASE | com.sybase.jdbc.SybDriver | jdbc:sybase:Tds:hostname:port Number / databaseName |

行查询：需要使用类型为Statement的对象来构建和提交SQL语句到数据库。

//定义sql，创建状态通道，进行sql语句的发送
Statement state = conn.createStatement();
//执行查询，获得结果集
ResultSet rs = state.executeQuery("select * from student");

从结果集中提取数据：需要使用相应的ResultSet.getXXX（）方法从结果集中检索数据。
1
2
3
while(rs.next()){
//获取每行的信息
}
释放资源：需要明确地关闭所有数据库资源，而不依赖于JVM的垃圾收集。
1
//将数据库连接，状态通道，结果集全部关闭

实例：

public static void main(String[] args)  {
    Connection conn = null;
    Statement state = null;
    ResultSet rs = null;
    //加载驱动
    try{
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获得连接
        String userName = "root";
        String passWord = "QW06125231!";
        String url = "jdbc:mysql://localhost:3306/mydb1?serverTimezone=UTC";
        conn = DriverManager.getConnection(url,userName,passWord);
        //定义sql，创建状态通道，进行sql语句的发送
        state = conn.createStatement();
        //执行查询，获得结果集
        rs = state.executeQuery("select * from student");
        //取出结果
        while(rs.next()){
            //取出数据：rs.getXXX(“列名”); XXX表示数据类型
            System.out.println("姓名"+rs.getString("stuname")+"生日"
                    +rs.getDate("birthday")+"年龄"+rs.getInt("stuage")+"性别"+rs.getString("stusex"));
        }
    }catch(Exception e){
        e.printStackTrace();
    }finally {
        //关闭资源
        try{
            if(conn != null){
                conn.close();
            }
            if(rs != null){
                rs.close();
            }
            if(state != null){
                state.close();
            }
        }catch(Exception e){
        }
    }

}

增删改

上面查询语句 ResultSet rs = state.executeQuery("select * from student");应该改为executeUpdaet()方法

以增加记录为例：

1	int rs = state.executeUpdate("insert into student(stuname,stuage,stusex,birthday) value('张三9',18,'a','2000-1-1)");

8.3 SQL注入

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

实例：

1
2
3

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

如何避免SQL注入？

预状态通道PreparedStatement

该PreparedStatement的接口扩展了Statement接口，它为您提供了一个通用的Statement对象有两个优点附加功能

String sql = "select * from where username =? and password=?";
PreparedStatement pps = conn.prepareStatement(sql);
pps.setString(1, userName);
pps.setString(2, passWord);
//获得结果集
rs = pps.executeQuery();

JDBC中的所有用于判断的参数都由？符号，这被称为参数标记。在执行SQL语句之前，必须为每个参数提供值。所述的setXXX()方法将值绑定到所述参数，其中XXX代表要绑定到输入参数的值的Java数据类型。如果忘记提供值，将收到一个SQLException。

每个参数标记由其顺序位置引用。第一个标记表示位置1，下一个位置2等等。该方法与Java数组索引不同!

statement和PreparedStatement的区别

(1)statement属于状态通道，PreparedStatement属于预状态通道

(2)预状态通道会先编译sql语句,再去执行，比statement执行效率高

(3)预状态通道支持占位符? 给占位符赋值的时候，位置从1开始

(4)预状态通道可以防止sql注入，原因:预状态通道在处理值的时候以字符串的方式处理

防止sql注入原理

首先这个类进行语句执行不是直接进行语句的拼接，而是先进行预编译，用? 进行占位再set()的时候将属性左右加上 ‘ (int类型不会)

然后会把用户输入的所有字符集全部遍历如果识别出一些特殊字符比如换行符、、双引号、单引号还有一些特殊字符会进行编译

1	select * from login where name='xx' and pwd =' \' or \'1\' = \'1\' '

所以使用了PreparedStatement 就无法截断SQL语句，也就是说无法拼接SQL语句，防止了sql注入。

8.4 多表关系操作

分类：双向一对一，一对多，多对一，多对多

多表关系处理数据：

数据库通过外键建立两表关系
实体类通过属性的方式建立两表关系

实体类要求：类名=表名，列名=属性名

8.4.1 一对多和多对一

以学生和老师为例：

一对多（老师-学生）

核心：一的一方增加一个List属性，存放多方的对象，一对多的联系就建立了

bean层

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;
    }
}
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层

//定义dao接口
public interface TeacherDao {
//定义操作方法
//1.定义一个根据老师id查询老师信息(学生的信息)
    public Teacher getById(int tid);
}
//定义实现类
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="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 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.setStudentList(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;
    }
}

多对一（学生-老师）

同理：在多方中存放老师的属性

List<Student> students=new ArrayList<>();
while (resultSet.next()){
    //1.取出各自的信息
    Student student = new Student();
    student.setStuId(resultSet.getInt("stuid"));
    student.setStuName(resultSet.getString("stuname"));
    Teacher teacher = new Teacher();
    teacher.setTid(resultSet.getInt("tid"));
    teacher.setTname(resultSet.getString("tname"));
    //2.建立学生和老师之间的关系
    student.setTeacher(teacher);
    students.add(student);
}
return students;

8.4.2 一对一（妻子丈夫）

两方的属性中都由对方的对象！

8.4.3 多对多(科目-学生)

多对多的关系最少要三张表，中间表用于存放stuid和subid(联系两表的桥梁)

两方互相有对方的集合

8.5 JDBC 事务应用

如果JDBC连接处于自动提交模式，默认情况下，则每个SQL语句在完成后都会提交到数据库。事务使您能够控制是否和何时更改应用于数据库。它将单个SQL语句或一组SQL语句视为一个逻辑单元，如果任何语句失败，则整个事务将失败。

要启用手动事务支持，而不是JDBC驱动程序默认使用的自动提交模式，请使用Connection对象的setAutoCommit（）方法。如果将boolean false传递给setAutoCommit（），则关闭自动提交。我们可以传递一个布尔值true来重新打开它。

为什么要搞一个手动提交

答：因为很多时候数据库操作是夹杂着业务逻辑的，通过将其组建为事务，保证其原子性，即出现错误则全不执行！！

完成更改后，我们要提交更改，然后在连接对象上调用commit（）方法

conn.commit( );

否则，要使用连接名为conn的数据库回滚更新，请使用以下代码

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();
}

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);
}

8.6 批处理

批量处理允许您将相关的SQL语句分组到批处理中，并通过对数据库的一次调用提交它们。当您一次向数据库发送多个SQL语句时，可以减少连接数据库的开销，从而提高性能。

单个或多个T—SQL语句的集合，由应用程序一次性发送给SQL Server解析执行处理内的所有语句指令。

8.6.1 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();

8.6.2 PreparedStatement批处理

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

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

8.7 反射处理结果集

面对问题：正经的数据库表的列数可能有几十列，通过rs.getXXX()方法获取结果集的信息，然后用私有属性的set方法添加到对象中过于繁琐

答：通过反射简化结果集的获取

以8-4的学生表为例，应该做的修改：

//同样要先打开驱动，建立连接
//建立状态通道，获取结果集
String sqla = "select * from student";
pps = con.prepareStatement(sqla);
rs = pps.executeQuery();
/***修改部分，通过反射自动装填属性信息***/
//1. 返回数据库中的相关信息
ResultSetMetaData metaData = rs.getMetaData();

//2. 得到列数
int count=metaData.getColumnCount();

//3. 取得列名，存入字符串数组
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);
}

//4. 得到实体类中的所有的方法
Method[] methods =cla.getDeclaredMethods();
while(rs.next()){
    Object stu=cla.newInstance();//调取无参构造创建对象
    //需要暴力匹配的两方：1.列名，2.属性名，3.如果匹配就执行对应的get或set方法
    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);
}

8.8 封装工具类

通过修改6，7两个方法也可以实现批处理

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/yhp";
    private String username="root";
    private String password="123456";
    //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 {
            getConnection();//insert into users values(?,?,?,?,)
            pps=connection.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();
        }
    }
}

8.8.1 属性文件

对于上述封装工具类中的属性很有可能是动态变化的，因此也需要我们能够动态的引入变化的属性

方法1：创建db.properties文件

driver=com.mysql.jc.jdbc.Driver
url=jdbc:mysql://localhost:3306/yhp?serverTimezone=UTC
user=root
password=123456

工具类中读取属性文件:

此处用到的反射加载配置文件的方法见（java-基础 12.3.2）

Properties类的使用见（java基础 7.10）

private static String driverName;
   private static String url;
   private static String password;
   private static String username;
   //2.加载驱动
   static{
       try {
           InputStream inputStream = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
           Properties properties = new Properties();
           properties.load(inputStream);
           driverName = properties.getProperty("driver");
           url = properties.getProperty("url");
           username = properties.getProperty("user");
           password = properties.getProperty("password");
           Class.forName(driverName);
       } catch (Exception e) {
           e.printStackTrace();
       }
   }

8.9 连接池

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

8.9.1 自定义连接池

连接池轮廓

public class Pool{
    static LinkedList<Connection> list = new LinkedList<Connection>();
    static{
        for (int i = 0; i < 10; i++) {
            Connection connection = JDBCUtils.newInstance().getConnection();
            list.add(connection);
        }
    }
    /**
    * 从连接池子中获取连接的方式
    * @return
    */
    public static Connection getConnection(){
        if (list.isEmpty()) {
            //JDBCUtils类是自定义类，封装了连接数据库的信息代码
            Connection connection = JDBCUtils.newInstance().getConnection();
            list.addLast(connection);
        }
        Connection conn = list.removeFirst();
        return conn;
    }
    /**
    * 返回到连接池子中
    */
    public static void addBack(Connection conn){
        if (list.size() >= 10) {
            try {
            conn.close();
            } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            }
        }else{
            list.addLast(conn); //10
        }
    }
    /**
    * 获取连接池子中连接数量的方法
    */
    public static int getSize(){
        return list.size();
    }
}

8.9.2 java规范实现连接池

Java为连接池实现提供了一个规范(接口),规范的写法,我们需要实现DataSource接口!

但是实现DataSource接口有一个弊端,没有提供回收链接方法!这里我们将使用装饰者模式!

装饰Connection!具体实现代码如下(创建装饰Connection)：

尤其是重写的close方法，保证了关闭连接时能够回归连接池

public class MyConnection implements Connection{
//将被装饰者导入
private Connection conn;
private LinkedList<Connection> list;
public MyConnection(Connection conn, LinkedList<Connection> list) {
super();
this.conn = conn;
this.list = list;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return conn.unwrap(iface);
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return conn.isWrapperFor(iface);
}
@Override
public Statement createStatement() throws SQLException {
return conn.createStatement();
}
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
return conn.prepareStatement(sql);
}
@Override
public CallableStatement prepareCall(String sql) throws SQLException {
return null;
}
@Override
public String nativeSQL(String sql) throws SQLException {
return null;
}
@Override
public void setAutoCommit(boolean autoCommit) throws SQLException {
}
@Override
public boolean getAutoCommit() throws SQLException {
return false;
}
@Override
public void commit() throws SQLException {
conn.commit();
}
@Override
public void rollback() throws SQLException {
conn.rollback();
}
@Override
public void close() throws SQLException {
list.addLast(conn);
}
...
}

基于规范实现的连接池

/**
* 创建一个规范的连接池子
*/
public class DataSourcePool implements DataSource{
static LinkedList<Connection> list = new LinkedList<Connection>();
static{
for (int i = 0; i < 10; i++) {
Connection connection = JDBCUtils.newInstance().getConnection();
list.add(connection);
}
}
public static int getSize(){
return list.size();
}
@Override
public Connection getConnection() throws SQLException {
Connection conn = list.removeFirst();
MyConnection conn1 = new MyConnection(conn, list);
return conn1;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
return null;
}
@Override
public void setLogWriter(PrintWriter out) throws SQLException {
}
@Override
public void setLoginTimeout(int seconds) throws SQLException {
}
@Override
public int getLoginTimeout() throws SQLException {
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
return null;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return false;
}
@Override
public Connection getConnection(String username, String password) throws
SQLException {
return null;
}
}

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

注1:在DBCP连接池的配置中，还有一个maxIdle的属性，表示最大空闲连接数，超过的空闲连接将被释放，默认值为8。对应的该属性在Druid连接池已不再使用，配置了也没有效果，c3p0连接池则没有对应的属性。

注2：数据库连接池在初始化的时候会创建initialSize个连接，当有数据库操作时，会从池中取出一个连接。如果当前池中正在使用的连接数等于maxActive，则会等待一段时间，等待其他操作释放掉某一个连接，如果这个等待时间超过了maxWait，则会报错；如果当前正在使用的连接数没有达到maxActive，则判断当前是否空闲连接，如果有则直接使用空闲连接，如果没有则新建立一个连接。在连接使用完毕后，不是将其物理连接关闭，而是将其放入池中等待其他操作复用。

8.9.3 DBCP连接池

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

DBCP连接池的使用

导入相应jar包

mysql-jdbc.jar

commons-dbcp.jar

commons-pool.jar
硬编码使用DBCP

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

@Test
public void testHard() throws SQLException{
    // 硬编码 使用DBCP连接池子
    BasicDataSource source = new BasicDataSource();
    //设置连接的信息
    source.setDriverClassName("com.mysql.jdbc.Driver");
    source.setUrl("jdbc:mysql://localhost:3306/day2");
    source.setUsername("root");
    source.setPassword("111");
    Connection connection = source.getConnection();
    String sql = "select * from student";
    Statement createStatement = connection.createStatement();
    ResultSet executeQuery = createStatement.executeQuery(sql);
    while (executeQuery.next()) {
    System.out.println(executeQuery.getString(2));
    }
    connection.close(); //回收
}

软编码使用DBCP

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

项目中添加配置
文件名称: 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

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) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}
//3.获得连接
public static Connection getConn() {
    try {
        con= datasource.getConnection();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return con;
}

C3P0连接池

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

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

实现方式

手动设置 ComboPooledDataSource
加载配置文件方式

src/c3p0-config.xml（文件名固定）
ComboPooledDataSource cpds = new ComboPooledDataSource();
加载 文件中 <default-config>中的配置
ComboPooledDataSource cpds = new ComboPooledDataSource("aaa");
加载 <named-config name="aaa"> 中的配置

实现步骤

导入jar包
c3p0-0.9.1.2.jar
mysql-connector-java-5.0.8.jar
添加配置文件

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

<?xml version="1.0" encoding="utf-8"?>
<c3p0-config>
<!-- 默认配置，如果没有指定则使用这个配置 -->
<default-config>
<!-- 基本配置 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/day2</property>
<property name="user">root</property>
<property name="password">111</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>
<!-- 命名的配置 -->
<named-config name="abc">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/day2</property>
<property name="user">root</property>
<property name="password">111</property>
<!-- 如果池中数据连接不够时一次增长多少个 -->
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">20</property>
<property name="minPoolSize">10</property>
<property name="maxPoolSize">40</property>
<property name="maxStatements">20</property>
<property name="maxStatementsPerConnection">5</property>
</named-config>
</c3p0-config>

注意:

c3p0的配置文件内部可以包含命名配置文件和默认配置文件!默认是选择默认配置!如果需要切换命名配置可以在创建c3p0连接池的时候填入命名即可!

```java
Connection con=null;
ComboPooledDataSource db=new ComboPooledDataSource(“abc”);
public Connection getCon(){
try {
con=db.getConnection();
System.out.println(“初始化的链接数量:”+db.getInitialPoolSize());
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}


#### Druid(德鲁伊)连接池

阿里出品，淘宝和支付宝专用数据库连接池，但它不仅仅是一个数据库连接池，它还包含一个ProxyDriver（代理驱动），一系列内置的JDBC组件库，一个SQL Parser(sql解析器)。支持所有JDBC兼容的数据库，包括Oracle、MySql、Derby、Postgresql、SQL Server、H2等等。Druid针对Oracle和MySql做了特别优化，比如Oracle的PS Cache内存占用优化，MySql的ping检测优化。
Druid提供了MySql、Oracle、Postgresql、SQL-92的SQL的完整支持，这是一个手写的高性能SQLParser，支持Visitor模式，使得分析SQL的抽象语法树很方便。简单SQL语句用时10微秒以内，复杂SQL用时30微秒。通过Druid提供的SQL Parser可以在JDBC层拦截SQL做相应处理，比如说分库分表、审计等。Druid防御SQL注入攻击的WallFilter就是通过Druid的SQL Parser分析语义实现的。Druid 是目前比较流行的高性能的，分布式列存储的OLAP框架(具体来说是MOLAP)。它有如下几个特点：
一. 亚秒级查询
druid提供了快速的聚合能力以及亚秒级的OLAP查询能力，多租户的设计，是面向用户分析应用的理想方式。
二.实时数据注入
druid支持流数据的注入，并提供了数据的事件驱动，保证在实时和离线环境下事件的实效性和统一性
三.可扩展的PB级存储
druid集群可以很方便的扩容到PB的数据量，每秒百 万级别的数据注入。即便在加大数据规模的情况下，也能保证时其效性
四.多环境部署
druid既可以运行在商业的硬件上，也可以运行在云上。它可以从多种数据系统中注入数据，包括hadoop，spark，kafka，storm和samza等
五.丰富的社区
druid拥有丰富的社区，供大家学习

- 使用步骤

  导入jar包

  编写工具类

```java
/**
* 阿里的数据库连接池
* 性能最好的
* Druid
* */
public class DruidUtils {
    //声明连接池对象
    private static DruidDataSource ds;
    static{
        ///实例化数据库连接池对象
        ds=new DruidDataSource();
        //实例化配置对象
        Properties properties=new Properties();
        try {
            //加载配置文件内容
            properties.load(DruidUtils.class.getResourceAsStream("dbcpconfig.properties"));
            //设置驱动类全称
            ds.setDriverClassName(properties.getProperty("driverClassName"));
            //设置连接的数据库
            ds.setUrl(properties.getProperty("url"));
            //设置用户名
            ds.setUsername(properties.getProperty("username"));
            //设置密码
            ds.setPassword(properties.getProperty("password"));
            //设置最大连接数量
            ds.setMaxActive(Integer.parseInt(properties.getProperty("maxActive")));
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
    }
}
//获取连接对象
    public static Connection getConnection() {
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }
}

注:在Druid连接池的配置中，driverClassName可配可不配，如果不配置会根据url自动识别dbType(数据库类型)，然后选择相应的driverClassName。

9. 实例和问题

project：day44_JDBC

9.1 PreparedStatement状态通道的占位符

只能对where后的参数进行占位，在将如下sql语句传入后执行报错

1	sql="update airinfo set flightId=?, flightNum=?,address=?,date=? where flightId=?";

修改如下：

"update airinfo set 
flightId="+a.getFlightId()+",
flightNum='"+a.getFlightNum()+"',
address='"+a.getAddress()+"',
date='"+a.getDate()+"' 
where flightId=?";

注意：每个字符类型的数据在加入字符串时需要用单引号包裹

9.2 “\t”占位符的使用

java里的\t是补全当前字符串长度到8的整数倍，最少1个最多8个空格。补多少要看你\t前字符串长度，比如当前字符串长度10，那么\t后长度是16，也就是补6个空格，如果当前字符串长度12，此时\t后长度是16，补4个空格

这就导致可能无法对齐的情况，我的解决方法

public String toString() {
        if(address.length()>=8) 
            return  flightId + "\t\t" + flightNum + "\t\t" + address +"\t" +  date+ "\t\t" ;
        else 
            return  flightId + "\t\t" + flightNum + "\t\t" + address +"\t\t" +  date+ "\t\t" ;
    }

因为address的长度在8以下和以上都存在

9.3 数据库为设置时区信息导致报错

错误日志

java.sql.SQLException: The server time zone value '�й���׼ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

解决方法
- 解决办法1：修改数据库时区
  
  在cmd命令中进入mysql输入如下命令即可
- 解决办法2：在MySQL的配置文件my.ini中加入
  
  default-time_zone = '+8:00'
- 解决办法3：根据提示需要在配置数据库的连接信息中，在MySQL的URL后面加上“?serverTimezone=UTC” ，如下所示：
  1
  2
  driver=com.mysql.cj.jdbc.Driver
  url=jdbc:mysql://localhost:3306/yhp?serverTimezone=UTC