16、MySQL
1. 数据库的重要性
数据库几乎是软件体系中最核心的一个存在。
2. 数据库分类
-
关系型数据库（SQL DB）
使用关系模型组织数据；
包括MySQL、Oracle、SQL Server、SQLite、DB2、…
-
非关系型数据库（NoSQL DB）
数据通常以对象的形式存储在数据库中；
对象之间的关系通过每个对象自身的属性来决定；
常用于存储非结构化的数据
包括 Redis、MongoDB、…
3. 数据库操作
MySQL 常用命令参考：MySQL 数据库常用命令大全（完整版）_mysql数据库命令大全
3.1 SQL 语言分类
| 名称 | 命令 | 解释 |
|---|---|---|
| DDL（数据定义语言） | CREATE、DROP、ALTER | 维护存储数据的结构，如数据库、表、视图、索引等 |
| DML（数据操作语言） | INSERT、UPDATE、DELETE | 操作数据库对象中所包含的数据 |
| DQL（数据查询语言） | SELECT | 查询数据库当中的数据 |
| DCL（数据控制语言） | GRANT、commit、rollback | 管理数据库，包括管理权限及数 |
3.2 存储引擎
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方法。
使用命令 show engines; 查看数据库可以支持的存储引擎，命令可以显示当前数据库支持的存储引擎情况。
MySQL 数据库支持多种存储引擎，如 MyISAM、InnoDB、MEMORY、…
MyISAM 与 InnoDB 对比：
| 特性 | MyISAM | InnoDB | MEMORY |
|---|---|---|---|
| 事务安全 | ❌ | ✅ | ❌ |
| 空间使用 | 小 | 大 | 小 |
| 内存使用 | 小 | 大 | 大 |
| 插入数据速度 | 快 | 慢 | 快 |
| 支持外键 | ❌ | ✅ | ❌ |
| 全文索引 | ✅ | ✅ (since MySQL 5.6) | - |
| 适用场合 | 安全性、事务处理以及多用户操作 | 节约空间、速度快 | 安全性要求较低、速度快 |
InnoDB 是 MySQL 的默认存储引擎。
3.3 存储位置（仅针对 MySQL 8.0）
MySQL 数据以文件方式存储在磁盘中，包括表文件、数据文件、以及数据库的选项文件
位置：
C:\ProgramData\MySQL\MySQL Server 8.0\DataData目录下存储数据，目录名对应数据库名，该目录下文件名对应数据表
注意：
- InnoDB 类型的数据表只有一个
*.ibd文件，以及上一级目录（...\Data）中的ibdata1文件； - MyISAM 类型的数据表对应三个文件：
*.sdi文件 - 表结构信息文件*.MYD文件 - 数据文件*.MYI文件 - 索引文件
3.4 MySQL 字符集和排序规则
字符集：推荐 utf8mb4
排序规则：推荐 utf8mb4_0900_ai_ci
3.5 MySQL 注释
单行注释：以 -- 开头
多行注释：以 /* 开头并以 */ 结尾
3.6 DDL
3.5.1 操作数据库
- 查看所有数据库：
show databases; - 创建数据库：
create database [if not exists] DBName; - 使用数据库：
use DBName; - 查看当前使用的数据库：
select database(); - 删除数据库：
drop database [if exists] DBName;
3.5.2 操作数据表
-
查看当前数据库下的所有数据表：
show tables; -
查看数据表的结构：
desc tableName; -
创建数据表：
1
2
3
4
5
6create table [if not exists] `tableName` (
'字段名1' 数据类型 [属性][索引][注释],
'字段名2' 数据类型 [属性][索引][注释],
#...
'字段名n' 数据类型 [属性][索引][注释]
); -
修改数据表：
- 修改表名：
alter table oldTableName rename as/to newName; - 增加字段：
alter table tableName add 字段名 数据类型; - 修改数据类型：
alter table tableName modify 字段名 新数据类型; - 修改字段名和数据类型：
alter table tableName change 字段名 新字段名 新数据类型; - 删除列：
alter table tableName drop 字段名;
- 修改表名：
-
删除数据表：
drop table [if exists] tableName;
3.7 DML
-
添加数据：
INSERT INTO tableName (column1, column2, ...) VALUES (value1, value2, ...), (value1, value2, ...), (value1, value2, ...)...; -
修改数据：
UPDATE tableName SET column1 = value1, column2 = value2, ... [WHERE condition]; -
删除数据：
DELETE FROM tableName [WHERE condition]; -
清空表数据但表结构、索引、约束等不变：
TRUNCATE TABLE tableName;Notice: 使用 TRUNCATE TABLE 会重新设置 AUTO_INCREMENT 计数器
3.8 DQL
-
【Important】SQL 查询语句执行顺序：
1
2
3
4
5
6
7
8select [all | distinct] {* | table.* | [table.field_1 [as alias_1][, table.field_2 [as alias_2]][, ...]]}
from table_name [as table_alias]
[left | right | inner join table_name_2] -- 连接查询
[where ...] -- 指定结果需满足的条件
[group by ...] -- 指定结果按照哪几个字段来分组
[having] -- 过滤分组的记录必须满足的次要条件
[order by ...] -- 指定查询记录按一个或多个条件排序
[limit {[offset,] rows | rows OFFSET offset}]; -- 指定查询的记录从哪条至哪条
优先级：where > group by > having > order by > limit
-
模糊查询（between…and…\like\in）
1
2
3
4
5
6
7
8
9-- between...and... 示例
select sno, sname, score from student where score between 0 and 100;
-- like 示例
select sno, sname, score from student where sname like "felix_"; -- 下划线 "_" 代表1个字符
select sno, sname, score from student where sname like "felix%"; -- "%" 代表0到任意个字符
-- in 示例
select sno, sname, address from student where address in ('北京', '南京', '重庆'); -
连接查询
参考博客：SQL 连接（JOIN）
-
排序
语法：
order by 字段名 desc/asc，desc 代表降序排序，asc 则表示升序排序 -
分页
语法：
select * from tableName limit [offset,] rows | rows OFFSET offsetlimit 子句可以被用于强制 select 语句返回指定的记录数；
limit 接受一个或两个数字参数，参数必须是一个整数常量：
-
如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
初始记录行的偏移量是 0，而不是 1
-
如果只给定一个参数，它表示返回最大的记录行数目
-
-
子查询
所谓子查询，就是在查询语句中的 where 条件子句中，又嵌套了另一个查询语句
3.9 外键
在数据库中，外键用于建立表与表之间的引用关系，维护数据的一致性和完整性。
外键字段关联到另一个表的主键字段，以建立两个表之间的关系。
-
创建外键
-
方式一：创建数据表时同时创建外键
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23-- 示例
-- 解释：CONSTRAINT constraintName 子句作用是为外键约束定义约束名称。如果省略它，MySQL 将自动生成一个名称。
CREATE TABLE `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
CREATE TABLE `student` (
`studentno` INT(4) NOT NULL COMMENT '学号',
`studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` TINYINT(1) DEFAULT '1' COMMENT '性别',
`gradeid` INT(10) DEFAULT NULL COMMENT '年级',
`phoneNum` VARCHAR(50) NOT NULL COMMENT '手机',
`address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
`borndate` DATETIME DEFAULT NULL COMMENT '生日',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 -
方式二：创建数据表完成后，修改数据表添加主键
1
2
3
4-- 示例
-- 解释：CONSTRAINT FK_gradeid 子句作用是为外键约束定义约束名称。如果省略它，MySQL 将自动生成一个名称。
ALTER TABLE `student` ADD CONSTRAINT constraintName FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);
-
-
删除外键：
1
2
3
4
5-- 第一步：删除外键
alter table tableName drop foreign key constraintName;
-- 第二步：删除外键索引
alter table tableName drop index constrainName;
【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
4. MySQL 函数
官方文档：MySQL :: MySQL 8.0 Reference Manual :: 14 Functions and Operators
4.1 常用函数
数据函数
SELECT ABS(-8);– 绝对值SELECT CEILING(9.4);--向上取整SELECT FLOOR(9.4);--向下取整SELECT RAND();--随机数，返回一个 0~1 之间的随机数SELECT SIGN(0);--符号函数: 负数返回-1，正数返回1，0返回0
字符串函数
SELECT CHAR_LENGTH('坚持就是胜利');– 返回字符串包含的字符数SELECT CONCAT('我','爱','编程');– 合并字符串，参数可以有多个SELECT INSERT(s1, x, len, s2);– 使用字符串 s2 替换字符串 s1 部分，起始于字符串 s1 的 x 位置，共替换字符串 s1 的 len 长度SELECT LOWER('KuangShen');– 转小写SELECT UPPER('KuangShen');– 转大写SELECT LEFT('hello,world',5);– 从左边截取SELECT RIGHT('hello,world',5);– 从右边截取SELECT REPLACE('坚持就能成功','坚持','努力');– 替换字符串SELECT SUBSTR('坚持就能成功',4,6);– 截取子字符串，开始和长度SELECT REVERSE('坚持就能成功');– 字符反转
日期时间函数
SELECT CURRENT_DATE();– 获取当前日期SELECT CURDATE();– 获取当前日期SELECT NOW();– 获取当前日期和时间SELECT LOCALTIME();– 获取当前日期和时间SELECT SYSDATE();– 获取当前日期和时间SELECT YEAR(NOW());– 获取年SELECT MONTH(NOW());– 获取月SELECT DAY(NOW());– 获取日SELECT HOUR(NOW());– 获取时SELECT MINUTE(NOW());– 获取分SELECT SECOND(NOW());– 获取秒
系统信息函数
SELECT VERSION();--查看 MySQL 版本SELECT USER();--查看当前用户
加密函数
md5()– 信息加密
4.2 聚合函数
-
COUNT()– 返回满足 select 条件的记录总和数-
count(字段名)会统计该字段在表中出现的次数，忽略字段为 null 的情况； -
count(*)包括了所有的列，相当于行数，在统计结果的时候，包含字段为 null 的记录； -
count(1)用 1 代表代码行，在统计结果的时候，包含字段为 null 的记录 。
从含义上讲，
count(1)与count(*)都表示对全部数据行的查询： 1）在表没有主键时，count(1)比count(*)快；
2）有主键时，主键作为计算条件，count(主键)效率最高；
3）若表格只有一个字段，则count(*)效率较高。
推荐使用
count(1) -
-
SUM()– 返回数字字段或表达式列作统计，返回一列的总和。 -
AVG()– 通常为数值字段或表达列作统计，返回一列的平均值 -
MAX()– 可以为数值字段，字符字段或表达式列作统计，返回最大的值。 -
MIN()– 可以为数值字段，字符字段或表达式列作统计，返回最小的值。
5. 事务
事务是一组 SQL 语句的执行，被视为一个单独的工作单元。
- 在 MySQL 中，只有使用了 InnoDB 数据库引擎的数据库或表才支持事务
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
- 事务用来管理 insert、update、delete 语句
事务特性（ACID）
-
原子性（Atomicity）
一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。
事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
-
一致性（Consistency）
事务完成后，所有数据的状态都是一致的。
“以转账为例，假设有五个账户，每个账户余额是100元，五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元。”
-
隔离性（Isolation）
数据库允许多个事务同时对其数据进行读写和修改，而隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，如：
- 脏读：A 事务正在修改数据但未提交，此时 B 事务去读取此条数据，B 事务读取的是未提交的数据，A 事务回滚。
- 不可重复读：事务 A 多次读取同一数据，但事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。
- 幻读：事务 A 第一次查询查到 5 条数据，第二次查到 8 条数据，这是因为在两次查询的间隙，事务 B 插入了 3 条数据。
不可重复读与幻读区别：
- 不可重复读是读取了其他事务更改的数据，针对 update 操作
- 幻读是读取了其他事务新增的数据，针对 insert 和 delete 操作
为了防止多个事务执行间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
SQL 标准定义四种隔离级别，包括：
- 读未提交（Read uncommitted）
- 读提交（read committed）
- 可重复读（repeatable read）
- 串行化（Serializable）
-
持久性（Durability）
事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
事务实现
-
Notice: MySQL 中默认是自动提交，使用事务时应先关闭自动提交
1
2
3
4
5-- 使用 set 语句改变自动提交模式
SET autocommit = 0; -- 关闭自动提交
SET autocommit = 1; -- 开启自动提交 -
事务处理
1
2
3
4
5
6
7
8
9
10
11SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开始一个事务，标记事务的起始点
/*
（待执行的SQL 语句）
*/
COMMIT -- 提交一个事务给数据库
ROLLBACK -- 将事务回滚，数据回到本次事务的初始状态
SET autocommit = 1 -- 还原MySQL数据库的自动提交 -
保存点（SAVEPOINT）
1
2
3
4
5SAVEPOINT sp_name -- 设置保存点(一个事务中可以有多个 SAVEPOINT)
ROLLBACK TO SAVEPOINT sp_name -- 回滚到保存点
RELEASE SAVEPOINT sp_name -- 删除保存点
6. 索引
6.1 索引作用
- 提高查询速度
- 确保数据的唯一性
- 可以加速表和表之间的连接，实现表与表之间的参照完整性
- 使用分组和排序子句进行数据检索时可以显著减少分组和排序的时间
- 全文检索字段进行搜索优化
6.2 索引分类
-
主键索引 -
PRIMARY KEY特点：
- 确保数据记录的唯一性
- 确定特定数据记录在数据库中的位置
- 一张数据表中最多只能有一个主键
-
唯一索引 -
UNIQUE KEY作用： 避免同一个表中某个字段的值重复
一张数据表中可以创建多个唯一索引
-
普通索引 -
INDEX/KEY作用 : 快速定位特定数据
注意：不宜添加太多普通索引，影响数据的插入、删除和修改操作
-
全文索引 -
FULLTEXT KEY作用：快速定位特定数据
注意：
-
只能用于 MyISAM 类型的数据表
-
只能用于 char, varchar, text 数据列类型
-
适合大型数据集
-
6.3 创建索引
-
方式一：创建数据表时同时创建索引
1
2
3
4
5CREATE TABLE tableName (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [索引名] (字段名[(长度)] [ASC |DESC])
); -
方式二：在已创建的数据表上创建索引
1
2
3
4
5-- CREATE 命令
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX index_name ON table_name (字段名[(长度)] [ASC |DESC]);
-- ALTER 命令
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX index_name (字段名[(长度)] [ASC |DESC]);
显示索引信息： SHOW INDEX FROM tableName;
6.4 删除索引
命令：
1 | -- 方式一 |
6.5 索引准则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表建议不要加索引
- 索引一般应加在查找条件的字段
7. 权限管理
7.1 用户管理
-
用户信息表：mysql.user
-
刷新权限
1
flush privileges;
-
增加用户
1
CREATE USER 'userName'@'host' IDENTIFIED BY [PASSWORD] 'pwd';
注意：
- 必须拥有 MySQL 数据库的全局 CREATE USER 权限，或拥有 INSERT 权限
- 只能创建用户，不能赋予权限
- 注意用户名格式，userName 表示用户名，host 指定该用户在哪个主机可以登录
- 如果是本地用户，host 可用
localhost - 如果想让该用户可以从任意远程主机登陆，host 可以使用通配符
%
- 如果是本地用户，host 可用
- 密码需要引号，即使是纯数字密码也要加引号
- 如果密码是一个普通的字符串，则不需要使用 PASSWORD 关键字
- 如果要把密码指定为由PASSWORD()函数返回的混编值，需包含 PASSWORD 关键字
-
重命名用户
1
RENAME USER old_user TO new_user
-
设置密码
1
2
3
4
5-- 为当前用户设置密码
SET PASSWORD = PASSWORD('pwd');
-- 为指定用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('pwd'); -
删除用户
1
DROP USER userName;
-
查看用户权限
1
2
3
4
5
6
7-- 查看权限
SHOW GRANTS FOR 用户名
-- 查看当前用户权限
SHOW GRANTS; -- 或
SHOW GRANTS FOR CURRENT_USER; -- 或
SHOW GRANTS FOR CURRENT_USER(); -
MySQL 权限解释
权限类型 权限说明 All/All Privileges 代表全局或者全数据库对象级别的所有权限 Alter 代表允许修改表结构的权限，但必须要求有create和insert权限配合。如果是rename表名，则要求有alter和drop原表， create和insert新表的权限 Alter routine 代表允许修改或者删除存储过程、函数的权限 Create 代表允许创建新的数据库和表的权限 Create routine 代表允许创建存储过程、函数的权限 Create tablespace 代表允许创建、修改、删除表空间和日志组的权限 Create temporary tables 代表允许创建临时表的权限 Create user 代表允许创建、修改、删除、重命名user的权限 Create view 代表允许创建视图的权限 Delete 允许执行delete操作 Drop 代表允许删除数据库、表、视图的权限，包括truncate table命令 Event 代表允许查询，创建，修改，删除MySQL事件 Execute 代表允许执行存储过程和函数的权限 File 代表允许在MySQL可以访问的目录进行读写磁盘文件操作，可使用的命令包括load data infile,select … into outfile,load file()函数 Grant option 代表是否允许此用户授权或者收回给其他用户你给予的权限,重新付给管理员的时候需要加上这个权限 Index 代表是否允许创建和删除索引 Insert 代表是否允许在表里插入数据，同时在执行analyze table,optimize table,repair table语句的时候也需要insert权限 Lock tables 代表允许对拥有select权限的表进行锁定，以防止其他链接对此表的读或写 Process 代表允许查看MySQL中的进程信息，比如执行show processlist, mysqladmin processlist, show engine等命令 Reference 是在5.7.6版本之后引入，代表是否允许创建外键 Reload 代表允许执行flush命令，指明重新加载权限表到系统内存中，refresh命令代表关闭和重新开启日志文件并刷新所有的表 lication client 代表允许执行show master status,show slave status,show binary logs命令 Replication slave 代表允许slave主机通过此用户连接master以便建立主从复制关系 Select 允许执行select操作 Show databases 代表允许执行show databases命令查看所有的数据库名 Show view 代表允许执行show create view命令查看视图创建的语句 Shutdown 代表允许关闭数据库实例，执行语句包括mysqladmin shutdown Super 代表允许执行一系列数据库管理命令，包括kill强制关闭某个连接命令， change master to创建复制关系命令，以及create/alter/drop server等命令 Trigger 代表允许创建，删除，执行，显示触发器的权限 Update 允许执行update操作 Usage 是创建一个用户之后的默认权限，其本身代表连接登录权限。使用create user语句创建的用户，默认就拥有这个usage权限，但是除了能登录之外， -
授权
1
2
3
4GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
-- *.* 表示所有库的所有表
-- 库名.表名 表示某库下面的某表 -
撤销权限
1
2
3REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名 -- 撤销所有权限
7.2 MySQL 备份
-
MySQL 数据库备份方法
- 备份工具，如 mysqldump, …
- 数据库管理工具
- 直接拷贝数据库文件和相关配置文件
-
mysqldump 使用
-
导出
1
2
3
4
5
6
7
8
9
10
11-- 导出一张表
mysqldump -u用户名 -p密码 库名 表名 > 文件名(一般为.sql后缀)
-- 导出多张表
mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名
-- 导出所有表
mysqldump -u用户名 -p密码 库名 > 文件名
-- 导出一个库
mysqldump -u用户名 -p密码 -B 库名 > 文件名 -
导入
1
2
3
4
5-- 在登录mysql的情况下
source 备份文件
-- 在不登录的情况下
mysql -u用户名 -p密码 库名 < 备份文件
-
8. 数据库规范化
9. JDBC
9.1 数据库驱动
- 数据库驱动就是直接操作数据库的一个程序
- 不同数据库产品的数据库驱动名字有差异
- 在程序中需要依赖数据库驱动来完成对数据库的操作
9.2 JDBC
SUN 公司为了简化、统一对数据库的操作，定义了一套 Java 操作数据库的规范（接口），称之为 JDBC。
这套接口由数据库厂商去实现，这样，开发人员只需要学习 jdbc 接口，并通过 jdbc 加载具体的驱动，就可以操作数据库。
JDBC 全称为：Java Database Connectivity（java 数据库连接），它主要由接口组成。
组成 JDBC 的 2 个包：java.sql、javax.sql
开发 JDBC 应用需要以上 2 个包的支持外，还需要导入相应 JDBC 的数据库实现（即数据库驱动）。
9.3 编写 JDBC 程序
-
导入 JDBC 驱动
-
编写程序
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43package pers.afelixliu.study.db;
import java.sql.*;
public class DBConnect {
public static void main(String[] args) {
// 0. 准备数据库连接信息
String url = "jdbc:mysql://localhost:3306/tmp?characterEncoding=utf-8&serverTimezone=Asia/Shanghai";
String username = "root";
String password = "1019";
try {
// 1. 加载数据库驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. 连接数据库
Connection connect = DriverManager.getConnection(url, username, password);
// 3. 创建操作对象
Statement statement = connect.createStatement();
// 4. 执行SQL语句
ResultSet result = statement.executeQuery("select * from book");
// 5. 处理执行结果
while (result.next()) {
String name = result.getString("name");
String author = result.getString("author");
double price = result.getDouble("price");
System.out.println("Name: " + name + ", Author: " + author + ", Price: " + price);
}
// 6. 释放资源
result.close();
statement.close();
connect.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}详细解释：
- DriverManager 类用于注册驱动，并创建与数据库的连接
-
Connection 类用于代表数据库的连接，客户端与数据库所有交互都是通过 Connection 对象完成的，常用方法如下：
-
createStatement()：创建向数据库发送 sql 的 statement 对象
-
prepareStatement(sql)：创建向数据库发送预编译 sql 的 PrepareSatement 对象
-
setAutoCommit(boolean autoCommit)：设置事务是否自动提交
-
commit()：在连接上提交事务
-
rollback()：在此连接上回滚事务
-
-
Statement 类用于向数据库发送 SQL 语句，常用方法如下：
-
executeQuery(String sql)：用于向数据发送查询语句
-
executeUpdate(String sql)：用于向数据库发送 insert、update 或 delete 语句
-
execute(String sql)：用于向数据库发送任意 sql 语句
-
addBatch(String sql)：把多条 sql 语句放到一个批处理中
-
executeBatch()：向数据库发送一批 sql 语句执行
-
-
ResultSet 类用于代表 sql 语句的执行结果。
ResultSet 封装执行结果时，采用的类似于表格的方式：ResultSet 对象维护了一个指向表格数据行的游标，初始的时候，游标在第一行之前，调用
ResultSet.next()方法，可以使游标指向具体的数据行，进行调用方法获取该行的数据。ResultSet 对象提供的都是用于获取数据的get方法：
-
获取任意类型的数据
-
getObject(int index)
-
getObject(string columnName)
-
-
获取指定类型的数据，例如：
-
getString(int index)
-
getString(String columnName)
-
ResultSet 还提供了对结果集进行滚动的方法：
-
next()：移动到下一行
-
Previous()：移动到前一行
-
absolute(int row)：移动到指定行
-
beforeFirst()：移动 resultSet 的最前面。
-
afterLast() ：移动到resultSet的最后面。
-
为确保资源释放代码能运行，资源释放代码一定要放在 finally 语句块中。
9.4 Connection 类处理事务
当 JDBC 程序向数据库获得一个 Connection 对象时，默认情况下这个 Connection 对象会自动向数据库提交在它上面发送的 SQL 语句。若想关闭这种默认提交方式，让多条SQL在一个事务中执行，可使用下列的 JDBC 控制事务语句：
1 | // 关闭事务自动提交（开启事务） |
9.5 详解 Statement 类
JDBC 中的 Statement 对象用于向数据库发送 SQL 语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
-
Statement 类的
executeUpdate()方法，用于向数据库发送增、删、改的 sql 语句，executeUpdate()执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化） -
Statement 类的
executeQuery()方法用于向数据库发送查询语句，executeQuery()方法返回代表查询结果的ResultSet对象
9.6 自定义 JDBC 工具类
第一步、在 src 目录下创建 db.properties 文件
1 | driver=com.mysql.cj.jdbc.Driver |
第二步、在 src 目录下创建 utils 文件夹，并在 utils 文件夹中创建 JDBCUtils.java 文件
1 | package utils; |
9.7 SQL 注入问题
SQL 注入就是攻击者通过在输入数据中注入恶意的 SQL 语句，利用应用程序对用户输入的不当处理，欺骗数据库服务器执行恶意的 SQL 指令，从而达到获取、篡改或破坏数据库中数据的目的。
举例：
比如一个登录表单，攻击者在用户名字段输入 admin' --，如果后端 SQL 语句是：
1 | SELECT * FROM users WHERE username = '[用户输入]' AND password = '[用户输入] |
则实际执行的 SQL 语句变成了：
1 | SELECT * FROM users WHERE username = 'admin' --' AND password = '[用户输入]' |
这里 -- 后面的内容被注释掉，因此 SQL 语句实际上忽略了密码检查。
9.8 PreparedStatement 类
PreparedStatement 类是 Statement 类的子类，它的实例对象可以通过调用
Connection.preparedStatement()方法获得。PreparedStatement 类的优点：
相对于 Statement 类而言，PreparedStatement 类可以避免 SQL 注入的问题。
Statement 类会使数据库频繁编译 SQL，可能造成数据库缓冲区溢出；PreparedStatement 类可对 SQL 进行预编译，从而提高数据库的执行效率。
PreparedStatement 类对于 sql 中的参数，允许使用占位符的形式进行替换，简化 sql 语句的编写。
使用举例：
1 | package pers.afelixliu.study.db; |
setDate() 方法的注意事项：
-
setDate()方法接受的是java.sql.Date对象，而不是java.util.Date对象。如果需要使用java.util.Date，可以通过将其转换为java.sql.Date对象来实现，例如：1
pstmt.setDate(4, new java.sql.Date(new java.util.Date().getTime()));
-
setDate()方法设置的日期只包含日期部分，不包含时间部分，默认的时间部分将会被设置为00:00:00。 -
如果需要同时设置日期和时间，可以使用
setTimestamp()方法，setTimestamp()方法可以有效地将java.sql.Timestamp对象插入到数据库的datetime或timestamp字段中。
PreparedStatement 类防止 SQL 注入的原因：
SQL执行的时候把整个参数用引号包起来，并把参数中的引号作为转义字符，从而避免了参数也作为条件的一部分。
9.9 数据库连接池
9.9.1 连接池基础
- 什么是数据库连接池？
数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。
- 为什么要使用连接池？
数据库连接是一种关键的有限的昂贵的资源，这一点在多用户的网页应用程序中体现的尤为突出。一个数据库连接对象均对应一个物理数据库连接，每次操作都打开一个物理连接，用完就关闭连接，这样造成系统的性能低下。
数据库连接池的解决方案是在应用程序启动时建立足够的数据库连接，并将这些连接组成一个连接池（简单说：在一个“池”里放了好多半成品的数据库连接对象），由应用程序动态地对池中的连接进行申请、使用和释放。对于多于连接池中连接数的并发请求，应该在请求队列中排队等待。并且应用程序可以根据池中连接的使用率，动态增加或减少池中的连接数。
连接池技术尽可能多地重用了消耗内存的资源，大大节省了内存，提高了服务器的服务效率，能够支持更多的客户服务。
通过使用连接池，将大大提高程序运行效率，同时，我们可以通过其自身的管理机制来监视数据库连接的数量、使用情况等。
数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。
数据库连接池的最小连接数和最大连接数的设置要考虑到以下几个因素：
-
最小连接数是连接池一直保持的数据库连接，但如果应用程序对数据库连接的使用量不大，将会有大量的数据库连接资源被浪费。
-
最大连接数是连接池能申请的最大连接数，如果数据库连接请求超过次数，后面的数据库连接请求将被加入到等待队列中，这会影响以后的数据库操作。
-
如果最小连接数与最大连接数相差很大，那么最先连接请求将会获利，之后超过最小连接数量的连接请求等价于建立一个新的数据库连接。不过，这些大于最小连接数的数据库连接在使用完不会马上被释放，它将被放到连接池中等待重复使用或是空闲超时后被释放。
编写连接池需实现 java.sql.DataSource 接口。
- 开源数据库连接池
目前很多 WEB 服务器（Weblogic, WebSphere, Tomcat）都提供了 DataSoruce 接口的实现，即连接池的实现。
通常我们把 DataSource 的实现，按其英文含义称之为数据源，数据源中都包含了数据库连接池的实现。
有一些开源组织提供了数据源的独立实现：
- DBCP / DPCP2 数据库连接池
- C3P0 数据库连接池
在使用了数据库连接池之后，在项目的实际开发中就不需要编写连接数据库的代码了，直接从数据源获得数据库的连接。
9.9.2 使用 DBCP2 数据源
第一步、加载以下依赖库
commons-logging-版本号.jarcommons-pool2-版本号.jarcommons-dbcp2-版本号.jar
第二步、在 src 目录下创建 dbcp.properties 文件
1 | driverClassName=com.mysql.cj.jdbc.Driver |
第三步、在 src 目录下创建 utils 文件夹，并在 utils 文件夹中创建 DBCPUtils.java 文件
1 | package utils; |
9.9.3 使用 C3P0 数据源
C3P0 与 DBCP 区别：DBCP 没有自动回收空闲连接的功能；C3P0 有自动回收空闲连接功能。
第一步、加载以下依赖库
c3p0-版本号.jarmchange-commons-java-版本号.jar
第二步、在 src 目录下创建 c3p0-config.xml 文件
注意事项：
-
配置文件名只能是
c3p0-config.xml -
在 xml 文件中，‘&’ 符号需要进行转义，使用 ‘&’ 来代替 ‘&’
1 |
|
第三步、在 src 目录下创建 utils 文件夹，并在 utils 文件夹中创建 DBCPUtils.java 文件
1 | package utils; |
