初始Mysql

https://www.kuangstudy.com/bbs/1467760102697095170#header2

什么是数据库

概念：

​ 数据仓库（软件），安装在操作系统（window，Linux，mac…）之上。可以存储大量数据。500万

作用：

​ 数据库是存储数据，管理数据，

为什么学习数据库

• 趋势，岗位需求
• 大数据时代，得数据者得天下
• 被迫需求：存数据
• 数据库是所有软件体系中最核心得存在

数据库分类

关系型数据库：（SQL）

• MySQL、Oracle、SQL server、DB2、SQLlite…
• 通过表与表之间、行与行之间的关系进行数据的存储。

非关系型数据库：(NoSQL)Not Only SQL

• redis、MongDB
• 对象存储，通过对象自身的属性来决定。

连接数据库

命令行连接：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

mysql --uroot -p123456 -- 连接数据库

update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; -- 修改用户密码
flush privileges; -- 刷新权限

-------------------------------
-- 所有的命令都使用;结尾
show databases;    -- 查看所有的数据库

mysql> use school    -- 切换数据库 use 数据库名
Database changed

show tables;    -- 查看数据库中所有的表
describe student;    -- 显示数据库中所有的表的信息

create database westos;    -- 创建一个数据库

exit;    -- 退出连接

-- 单行注释（SQL 的本来的注释）
/*    (sql的多行注释)
hello
hhhh
*/

数据库XXX语言

DDL 定义

DML 操作

DQL 查询

DCL 控制

MySQL属性和操作

1.常用密令

1
2
3

show create database user; --查看创建数据库user的语句
show create table user; --查看创建数据表user的语句
desc user; --查看数据表user的结构

mysql的关键字不区分字母的大小写

2.操作数据库。数据库名：demo

• 创建数据库

  1	create database [if not exists] demo;

• 删除数据库

  1	drop database [if exists] demo;

• 使用数据库

  1	use `demo`; --tab键上面，如果表名或者字段名是一个特殊字符，就需要使用``

• 查看数据库

  1	show databases; --查看所有数据库

3.数据库列的类型

mysql数据类型长度问题总结：https://blog.csdn.net/yaruli/article/details/79187814

1. 数值
   • tinyint：十分小的数据，1个字节。
   • smallint：较小的数据，2个字节。
   • mediumint：中等大小的数据，3个字节。

   • int：标准的证书，4个字节。（常用的变量）

   • bigint：较大的数据，8个字节。
   • float：浮点数，4个字节。
   • double：浮点数，8个字节。
   • decimal：字符串形式的浮点数。涉及到金钱计算时，一般使用decimal
2. 字符串
   • char：字符串固定大小的，0~255字节。

   • varchar：可变字符串，0~65535字节。（常用的变量）

   • tinytext：微型文本，2^8-1字节。

   • text：文本串，2^16-1字节。

3. 时间
   • date：YYYY-MM-DD，日期格式。
   • time：HH：mm：ss，时间格式。

   • datatime：YYYY-MM-DD HH：mm：ss，最常用的时间格式。

   • timestamp：时间戳，1970.1.1道现在的毫秒数。

   • year：年份表示。
4. null
   • 没有值，未知。
   • 注意：不要使用NULL进行运算，没有意义，计算结果为NULL

4.数据库的字段属性

1. Unsigned
   • 无符号的整数。
   • 声明了该列不能为负数。
2. zerofill
   • 0填充
   • 不足的位数使用0填充。int(5)类型存储数字9会变成00009
3. 自增
   • 通常理解为自增，自动在上一条记录的基础上+1（默认）。
   • 通常用来设计唯一的主键，必须是整数类型。
   • 可以自定义设计主键自增的起始值和步长。
4. 非空
   • 勾选上not null，如果不给字段赋值，则会报错。
5. 默认
   • 设置默认的值。
   • sex，默认值为男，如果不指定该列的值，则会有默认的值。

拓展：

1
2
3
4
5
6
7

/*    每一个表，都必须存在一下五个字段！未来做项目用的，表示一个记录存在意义！
id    主键
`version`    乐观锁
is_delete    伪删除
gmt_create    创建时间
gmt_update    修改时间
*/

5.创建数据库

• 注意点，使用英文()，表的名称和字段尽量使用``括起来。

• AUTO_INCREMENT 自增。

• 字符串使用单引号括起来。

• 所有的语句后面加，（英文的），最后一个不用加。

• PRIMARY KEY主键，一般一个表只有一个唯一的主键。

  1 2 3 4 5 6 7 8 9 10 11 12 13 14

  -- 目标：创建一个school数据库 -- 创建学生表（列，字段） 使用SQL创建 -- 学号int 登录密码varchar(20) 姓名，性别varchar(2) 出生日期（datatime） 家庭地址，email CREATE TABLE IF NOT EXISTS `student`( `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号', `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名', `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码', `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别', `birthday` DATETIME DEFAULT NULL COMMENT '生日', `address` VARCHAR(20) DEFAULT NULL COMMENT '地址', `email` VARCHAR(20) DEFAULT NULL COMMENT '邮箱', PRIMARY KEY(`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8

• 格式：

  1 2 3 4 5 6

  CREATE TABLE [IF NOT EXISTS] 表名（ '字段名' 列类型[属性][索引][注释]， '字段名' 列类型[属性][索引][注释]， ...... '字段名'列类型[属性][索引][注释] ）[表类型][字符集设置][注释]

6.数据表类型

1. 关于数据库引擎
   • InnoDB：5.5版本以后默认使用。
   • MyISam：早些年使用的默认。

1
2
3

常规使用操作：
- MYISAM节约空间，速度较快。 
- INNODB安全性高，事务的处理，多表多用户操作。

1. 在物理空间存在的位置
   所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库，本质还是文件的存储。
2. MySQL引擎在物理文件上的区别。
   • InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件。
   • MYISAM对应文件：
     • *.frm：表结构的定义文件
     • *.MYD：数据文件（data）
     • *.MYI：索引文件（index）

7.设置数据库表的字符集编码

MySQL默认字符编码的设置：https://www.jianshu.com/p/ec0c86ee3e04

1

charset=utf8

• 不设置的话，会是mysql默认的字符集编码（不支持中文）
• MySQL的默认编码是Latin1，不支持中文。
• 在my.ini中配置默认的编码

1

character-set-server=utf8

8.修改数据表字段

1
2
3
4
5
6
7
8
9
10
11
12
13

-- 修改表名：ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)

-- 修改表的字段（重命名，修改约束）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11)    -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age age1 INT(1)    -- 字段重命名

-- 删除表的字段：ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1

*change一般用来字段重命名，modify不用来字段重命名，只能修改字段类型和约束*

9.删除数据表字段

1
2

-- 删除表（如果表存在再删除）
drop table if exists `teacher1`

MySQL数据管理

外键（了解即可）

方式一：在创建表的时候，增加约束（麻烦，比较复杂）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- 学生表的gradeid字段，要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用） references 引用

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 IF NOT EXISTS `student`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
    `gradeid` INT(10) NOT NULL COMMENT '年级',
    `birthday` DATETIME DEFAULT NULL COMMENT '生日',
    `address` VARCHAR(20) DEFAULT NULL COMMENT '地址',
    `email` VARCHAR(20) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`),
    KEY `FK_gradeid`(`gradeid`),
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
    
)ENGINE=INNODB DEFAULT CHARSET=utf8

删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）

方式二：创建表成功后，添加外键约束

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

-- 学生表的gradeid字段，要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用） references 引用

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 IF NOT EXISTS `student`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
    `gradeid` INT(10) NOT NULL COMMENT '年级',
    `birthday` DATETIME DEFAULT NULL COMMENT '生日',
    `address` VARCHAR(20) DEFAULT NULL COMMENT '地址',
    `email` VARCHAR(20) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY（作为外键的列） REFERENCES 那个表（那个字段）

以上操作都是物理外键，数据库级别的外键，不建议使用！（避免数据库过多造成困扰）

==最佳实践==

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
• 我们想使用多张表的数据，想使用外键（程序去实现）

DML语言（全部记住）

数据库意义：数据存储，数据管理

DML语言：数据操作语言

• insert
• update
• delete

添加

1
2
3
4
5
6

-- 插入语句一定要数据和字段一一对应：insert into 表名（[字段1，字段2，...]）values('值1'，'值2'，...)
insert into `student`（id,name,age）values(1,'zhangsan',12);

-- 插入多个字段
INSERT INTO `grade`(`gradename`)
VALUES('大一'),('大二')

注意事项：

• 字段和字段之间使用 英文逗号 隔开
• 字段可以省略，但是后面的值必须要一一对应，不能少
• 可以同时插入多条数据，VALUES后面的值，需要使用,隔开即可 (),()

修改

1
2
3
4

-- 不指定条件的情况下，会修改所有的记录：update 表名 set column_name=value,[column_name=value,...] where [条件]
update `student` set name='张三' where id=1

UPDATE student SET NAME = '行初雾',email = '1092315259@qq.com' WHERE id = 1

条件：where子句

操作符：

• =、<>或!=、<、>、<=、>=、BETWEEN…and… 、AND、OR

1

UPDATE `student` set `name` = 'hello' WHERE `name` = '1' AND sex = '女'

语法：update 表名 set column_name=value,[column_name=value,…] where [条件]

注意：

• colnum_name 是数据库的列，尽量带上``
• 条件，筛选的条件，如果没有指定，则会修改所有的列
• value，是一个具体的值，也可以是一个变量
• 多个设置的属性之间，使用英文逗号隔开

删除

delete命令：

• 语法：delete from 表名 [where 条件]

• ```
  – 不指定条件的情况下，会删除所有的记录：delete from 表名
  delete from student where id=1;

  1 2 3 4 5

  TRUNCATE命令： - 作用：完全清空一个数据库表，表的结构和索引约束不会变

• • 清空整张表
    truncate table student;

    1 2 3 4 5 6 7 8 9 10

    delete 与 TRUNCATE 区别： 相同点：都能删除 数据，都不会删除表结构 不同点： - TRUNCATE 重新设置 自增列 计数器会归零 - TRUNCATE 不会影响事务

• • 测试delete与TRUNCATE区别
    CREATE TABLE test(
    id INT(4) NOT NULL AUTO_INCREMENT,
    coll VARCHAR(20) NOT NULL,
    PRIMARY KEY(id)
    )ENGINE=INNODB DEFAULT CHARSET=utf8mb4

INSERT INTO test(coll) VALUES(‘1’),(‘2’),(‘3’)

DELETE FROM test – 不会影响自增

TRUNCATE TABLE test – 自增会归零

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

delete删除问题（了解即可）：重启数据库，现象

- InnoDB    自增列会重1开始，（存在内存当中，断电即失）
- MyISAM    继续从上一个自增量开始（存在文件中，不会丢失）

## DQL查询数据（最重点）

### DQL

(Data Query LANGUAGE：数据查询语言)

- 所以的查询操作都用：Select
- ==数据库中最核心的语言，最重要的语句==
- 使用频率最高的语句

select 语法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,…]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] – 联合查询
[WHERE …] – 指定结果需满足的条件
[GROUP BY …] – 指定结果按照哪几个字段来分组
[HAVING] – 过滤分组的记录必须满足的次要条件
[ORDER BY …] – 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}];
– 指定查询的记录从哪条至哪条

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

注意：[]括号代表可选的，{}括号代表必选的

### 指定查询字段

```sql
-- 查询全部的学生 SELECT 字段 FROM 表
select * from student;

-- 查询指定字段
select `StudentId`,`StudentName` from student;

-- 别名，给结果起一个名字 AS    可以给字段起别名，也可以给表起别名
select `StudentId` as 学号 , `StudentName` AS 学生姓名 from student as s;

-- 函数 Concat（a，b）  拼接
select concat('姓名：',StudentName) as 新名字 from student;

语法：==SELECT 字段,… FROM 表==

有时，列表名不是那么见名知意，可以起别名：AS

去重及数据库的表达式

去重：distinct

1
2
3
4
5
6
7
8
9
10

-- 查询全部考试成绩
SELECT * FROM result;

-- 查询有哪些同学参加了考试
SELECT `StudentID` FROM result;

-- 发现数据重复，去重

SELECT DISTINCT `StudentID` FROM result;

数据库的列（表达式）

1
2
3
4
5
6
7
8
9
10
11
12

-- 查询系统版本 （函数）
SELECT VERSION();

-- 用来计算 （表达式）
SELECT 100*3-1 AS 计算结果;

-- 查询自增的步长 （变量）
SELECT @@auto_increment_increment;

-- 学生考试成绩+1查看
SELECT `StudentID`,`StudentResult`+1 AS '+1后' FROM result;

==数据库中的表达式：文本值、列、Null、函数、计算表达式、系统变量…==

select ==表达式== from 表

where子句之逻辑运算符

作用：检索数据中符合条件的值

搜索的条件由一个或者多个表达式组成，结果布尔值

逻辑运算符

==尽量使用英文字母==

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 查询考试成绩在 95 ~ 100分之间
SELECT studentid,studentresult FROM result;

-- and
SELECT studentid,studentresult FROM result
WHERE studentresult >= 95 AND studentresult <= 100；

-- && 
SELECT studentid,studentresult FROM result
WHERE studentresult >= 95 && studentresult <= 100；

-- 模糊查询（区间）
SELECT studentid,studentresult FROM result
WHERE studentresult BETWEEN 95 AND 100;

-- 除了1000号学生之外的学生的成绩
-- !=
SELECT studentid,studentresult FROM result
WHERE studentid != 1000;

-- not
SELECT studentid,studentresult FROM result
WHERE NOT studentid = 1000;

模糊查询：比较运算符

模糊查询：比较运算符

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
43
44
45

-- 查询姓王的同学

-- like 结合 
--   %（代表0到任意个字符）
--   _（代表1）

-- 查询姓王的同学
SELECT `studentId`,`studentName` FROM `student`
WHERE studentName LIKE '王%';

-- 查询姓王的同学，名字后面只有一个字的
SELECT `studentId`,`studentName` FROM `student`
WHERE studentName LIKE '王_';

-- 查询姓王的同学，名字后面有两个字的
SELECT `studentId`,`studentName` FROM `student`
WHERE studentName LIKE '王__';

-- 查询名字中间有嘉字的同学
SELECT `studentId`,`studentName` FROM `student`
WHERE studentName LIKE '%嘉%';


-- in (具体的一个或者多个值)
-- 查询学号1001,1002,1003号学号
SELECT `studentId`,`studentName` FROM `student`
WHERE studentId IN ('1001','1002','1003');
-- 查询在北京的学生
SELECT `studentId`,`studentName` FROM `student`
WHERE address IN ('北京');


-- null
-- 查询地址为空的学生
SELECT `studentId`,`studentName` FROM `student`
WHERE address = '' OR address IS NULL;

-- not null
-- 查询有出生日期的同学 不为空
SELECT `studentId`,`studentName` FROM `student`
WHERE borndate IS NOT NULL;

-- 查询没有出生日期的同学 为空
SELECT `studentId`,`studentName` FROM `student`
WHERE borndate IS NULL;

连表查询join on

join 对比

七种join理论

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

--   =========连表查询==================

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student;
SELECT * FROM result;

-- join on 连接查询
-- where 等值查询

-- inner join
SELECT s.`StudentId`,`StudentName`,`SubjectID`,`StudentResult`
FROM student AS s
INNER JOIN result AS r
ON s.`StudentId` = r.`StudentID`

-- right join
SELECT s.`StudentId`,`StudentName`,`SubjectID`,`StudentResult`
FROM student AS s
RIGHT JOIN result AS r
ON s.`StudentId` = r.`StudentID`

-- left join
SELECT s.`StudentId`,`StudentName`,`SubjectID`,`StudentResult`
FROM student AS s
LEFT JOIN result AS r
ON s.`StudentId` = r.`StudentID`

-- 查询缺考的同学
SELECT s.`StudentId`,`StudentName`,`SubjectID`,`StudentResult`
FROM student AS s
LEFT JOIN result AS r
ON s.`StudentId` = r.`StudentID`
WHERE r.`StudentID` IS NULL

-- 查询了参加考试的同学信息（学号，学生姓名，科目名称，分数）
SELECT s.`StudentId`,`StudentName`,`Subjectname`,`StudentResult`
FROM student AS s
INNER JOIN result AS r
ON s.`StudentId` = r.`StudentID`
INNER JOIN SUBJECT AS sub
ON r.`SubjectID` = sub.`subjectno`


-- 查询了参加高等数学-1考试的同学信息（学号，学生姓名，科目名称，分数）
SELECT s.`StudentId`,`StudentName`,`Subjectname`,`StudentResult`
FROM student AS s
INNER JOIN result AS r
ON s.`StudentId` = r.`StudentID`
INNER JOIN SUBJECT AS sub
ON r.`SubjectID` = sub.`subjectno`
WHERE sub.`subjectname` = '高等数学-1'


-- 我要查询哪些数据 select ...
-- 从哪几个表中查 from 表 XXX join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加

自连接

自己的表和自己的表连接，核心：==一张表拆为两张一样的表即可==

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- 创建表
-- unsigned 无符号
-- auto_increment=9 自增的起始值
DROP TABLE IF EXISTS `category` ;
CREATE TABLE `category` (
  `category_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
  `pid` INT(10) NOT NULL COMMENT '父id',
  `category_name` VARCHAR(50) NOT NULL COMMENT '主题名字',
  PRIMARY KEY (`category_id`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- 插入值
INSERT INTO `category`(`category_id`,`pid`,`category_name`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

SELECT * FROM `category`;

父类

子类

操作：查询父类对应的子类关系

1
2
3
4

-- 查询父子信息，把一张表看为两个一模一样的表
SELECT a.`category_name` AS '父栏目',b.`category_name` AS '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`category_id`=b.`pid`;

分页和排序

排序

1
2
3
4
5
6
7
8
9
10

-- 排序： 升序 ASC  降序 DESC
-- ORDER BY 通过那个字段排序，怎么排
-- 查询的结果根据成绩降序 排序
SELECT s.`StudentId`,`StudentName`,`Subjectname`,`StudentResult`
FROM student AS s
INNER JOIN result AS r
ON s.`StudentId` = r.`StudentID`
INNER JOIN SUBJECT AS sub
ON r.`SubjectID` = sub.`subjectno`
ORDER BY `studentResult` ASC

分页

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

-- 100w
-- 为什么要分页
-- 缓解数据库压力，给人更好的体验   瀑布流
-- 分页，每页只显示五条数据
-- 语法 ： limit 起始值，页面的大小
-- 网页应用：当前，总的页数，每页大小
-- LIMIT 0,5    1~5
-- LIMIT 1,5    2~6
-- LIMIT 6,5
SELECT s.`StudentId`,`StudentName`,`Subjectname`,`StudentResult`
FROM student AS s
INNER JOIN result AS r
ON s.`StudentId` = r.`StudentID`
INNER JOIN SUBJECT AS sub
ON r.`SubjectID` = sub.`subjectno`
ORDER BY `studentResult` ASC
LIMIT 0,3

-- 第一页 limit 0,5    (1-1)*5
-- 第二页 limit 5,5    (2-1)*5
-- 第三页 limit 10,5   (3-1)*5
-- 第N页 limit 10,5    (n-1)*pageSize,pageSize
-- pageSize,页面大小
-- (n-1)*pageSize，起始值
-- n，当前页
-- 总页数 = (数据总数%页面大小==0)? (数据总数/页面大小) : (数据总数/页面大小 + 1)

语法： limit (查询起始下标,页面大小)

子查询和嵌套查询

where（这个值是计算出来的）

本质：在where语句中嵌套一个子查询语句

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

-- 1.查询数据库结构-1的所有考试结果（学号，科目名，成绩），降序排列
-- 方式1：使用连接查询
SELECT r.`StudentId`,`Subjectid`,`StudentResult`
FROM result AS r
INNER JOIN `subject` AS sub
ON r.`SubjectID` = sub.`subjectno`
WHERE sub.`subjectname` = '高等数学-1'
ORDER BY res.`StudentResult` DESC;


-- 使用子查询(由里及外)
select r.`StudentId`,subjectId,studentResult
from result as r
where SubjectID = (
    select Subjectno from `subject` where SubjectName = '高等数学-1'
)
ORDER BY res.`StudentResult` DESC;


-- 分数不小于80分的学生的学号和姓名

SELECT DISTINCT s.`StudentId`,s.`StudentName`
FROM student s
INNER JOIN result r
ON s.`StudentId` = r.`StudentID`
WHERE r.`StudentResult` > 80



-- 在这个基础上增加一个科目，查询课程为高等数学-1，且分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentId`,s.`StudentName`
FROM student s
INNER JOIN result r
ON s.`StudentId` = r.`StudentID`
WHERE r.`StudentResult` > 80 
AND r.`SubjectID` = (
    SELECT subjectno FROM `subject` WHERE subject.`subjectname` = '高等数学-1'
) 

SELECT DISTINCT s.`StudentId`,s.`StudentName`
FROM student s
INNER JOIN result r
ON s.`StudentId` = r.`StudentID`
INNER JOIN `subject` sub
ON r.`SubjectID` = sub.`subjectno`
WHERE sub.`subjectname` = '高等数学-1'
AND r.`StudentResult` > 80


--  再次改造（由里及外）
SELECT DISTINCT s.`StudentId`,s.`StudentName`
FROM student s
WHERE studentId IN (
    SELECT studentId FROM result WHERE StudentResult > 80
    AND subjectid = (
	SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-1'
    )
)

常用函数

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

SELECT ABS(-8); -- 绝对值
SELECT CEILING(9.4) ;-- 向上取整
SELECT FLOOR(9.4);-- 向下取整
SELECT RAND(); -- 返回一个0~1之间的随机数
SELECT SIGN(-10); -- 判断一个数的符号，0 返回0 负数返回-1 正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('哈哈'); -- 字符串长度
SELECT CONCAT('我','爱','你'); -- 拼接字符串
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱'); -- 插入，替换
SELECT LOWER('ZYY'); -- 小写字母
SELECT UPPER('zyy'); -- 大写字母
SELECT INSTR('zyy','y'); -- 返回第一次出现的子串的索引
SELECT REPLACE('坚持就能成功','坚持','努力'); -- 替换出现的指定字符串
SELECT SUBSTR('坚持就能成功', 5, 2); -- 返回指定的子字符串（源字符串，截取的位置，截取的长度）
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 SYSTEM_USER();
SELECT USER();
SELECT VERSION();

聚合函数及分组过滤

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 聚合函数
-- 都能统计 表中数据

-- count(字段) 会忽略所有的null值(想查询一个表中有多少个记录，就使用这个count())
SELECT COUNT(studentname) FROM student;
-- COUNT(*) 不会忽略所有的null值 本质计算行数
SELECT COUNT(*) FROM student;
-- COUNT(1) 不会忽略所有的null值 本质计算行数
SELECT COUNT(1) FROM student;


SELECT SUM(studentResult) AS '总和'  FROM result;
SELECT AVG(studentResult) AS '平均值'  FROM result;
SELECT MAX(studentResult) AS '最大值'  FROM result;
SELECT MIN(studentResult) AS '最小值'  FROM result;


-- 查询不同课程的平均分，最高分，最低分
SELECT sub.subjectName,AVG(r.studentResult),MAX(r.studentResult),MIN(r.studentResult) FROM result r
INNER JOIN `subject` sub
ON r.`SubjectID` = sub.`subjectno`
GROUP BY r.`SubjectID`
HAVING AVG(r.`StudentResult`) > 80

数据库级别的md5加密（扩展）

什么是MD5?

主要增加算法复杂度和不可逆性。

MD5不可逆，具体的值的md5是一样的

MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密前的值

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

CREATE TABLE `testmd5`(
  `id` INT(4) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `pwd` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 明文密码
INSERT INTO `testmd5`(`id`,`name`,`pwd`)
VALUES
(1,'张三','123456'),
(2,'李四','123456'),
(3,'王五','123456');


SELECT * FROM `testmd5`;

-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=2;

-- 插入的时候加密
INSERT INTO `testmd5`(`id`,`name`,`pwd`)
VALUES
(4,'小明',MD5('123456'));

-- 如何校验，将用户传递进来的密码，进行MD5加密，然后比对加密后的值

SELECT * FROM `testmd5` WHERE `name`='小明' AND pwd = MD5('123456');

事务

什么是事务

==要么都成功，要么都失败==

将一组sql放到一个批次中去执行

事务原则：ACID原则 原子性 、一致性、隔离性、持久性 （脏读，幻读。。。）

参考博客链接：事务ACID理解

原子性（Atomicity）

要么都成功，要么都失败

一致性（Consistency）

事务前后的数据完整性要保持一致

下图操作前和操作后的总和都是1000

隔离性（Isolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性（Durability）

事务一旦移交不可逆，被持久化到数据库中

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
43
44
45
46
47
48
49
50
51

-- mysql 是默认开启事务自动提交的
SET autocommit = 0  -- 关闭
SET autocommit = 1  -- 开启（默认的）

-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交

-- 事务开启
START TRANSACTION    -- 标记一个事务的开始，从这个之后的sql都在同一个事务内

INSERT xx
INSERT xx

COMMIT -- 提交：持久化（成功！）
ROLLBACK -- 回滚：回到原来的样子（失败！）

-- 事务结束
SET autocommit = 1  -- 开启自动提交

-- 了解
SAVEPOINT 保存点名  -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点


-- 转账

CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci	
USER shop

CREATE TABLE `account`(
    `id` INT(3) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(30) NOT NULL,
    `money` DECIMAL(9,2) NOT NULL,
    PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4

INSERT INTO account(`name`,`money`)
VALUE ('A',2000.00),('B',10000.00)

-- 模拟转账：事务
SET autocommit = 0;    -- 关闭自动提交
START TRANSACTION -- 开启一个事务

UPDATE account SET money=money-500 WHERE `name` = 'A' -- A-500
UPDATE account SET money=money+500 WHERE `name` = 'B' -- B+500

COMMIT;    -- 提交事务，就被持久化了！
ROLLBACK; -- 回滚

SET autocommit = 1; -- 恢复默认值

隔离所导致的一些问题

脏读：

1、在事务A执行过程中，事务A对数据资源进行了修改，事务B读取了事务A修改后的数据。

2、由于某些原因，事务A并没有完成提交，发生了RollBack操作，则事务B读取的数据就是脏数据。

这种读取到另一个事务未提交的数据的现象就是脏读(Dirty Read)。

不可重复读：

事务B读取了两次数据资源，在这两次读取的过程中事务A修改了数据，导致事务B在这两次读取出来的数据不一致。

这种**==在同一个事务中==，前后两次读取的数据不一致的现象就是不可重复读(Nonrepeatable Read)。**

虚读(幻读)

事务B前后两次读取同一个范围的数据，在事务B两次读取的过程中事务A新增了数据，导致事务B后一次读取到前一次查询没有看到的行。

幻读和不可重复读有些类似，但是幻读强调的是集合的增减，而不是单条数据的更新。

第一类更新丢失

事务A和事务B都对数据进行更新，但是事务A由于某种原因事务回滚了，把已经提交的事务B的更新数据给覆盖了。这种现象就是第一类更新丢失。

第二类更新丢失

其实跟第一类更新丢失有点类似，也是两个事务同时对数据进行更新，但是事务A的更新把已提交的事务B的更新数据给覆盖了。这种现象就是第二类更新丢失。

事务隔离级别

为了解决以上的问题，主流的关系型数据库都会提供四种事务的隔离级别。事务隔离级别从低到高分别是：读未提交，读已提交，可重复读，串行化。事务隔离级别越高，越能保证数据的一致性和完整性，但是执行效率也越低，所以在设置数据库的事务隔离级别时需要做一下权衡，mysql默认是可重复读

读未提交

读未提交(Read Uncommitted)，是最低的隔离级别，所有的事务都可以看到其他未提交的事务的执行结果。只能防止第一类更新丢失，不能解决脏读，可重复读，幻读，所以很少应用于实际项目。

读已提交

读已提交(Read Committed)，在该隔离级别下，一个事务的更新操作只有在该事务提交之后，另外一个事务才可能读取到同一笔数据更新后的结果。可以防止脏读和第一类更新丢失，但是不能解决可重复和幻读的问题。

可重复读（重要）

可重复读(Repeatable Read)，mysql默认的隔离级别。在该隔离级别下，一个事务多次读同一个数据，在这个事务还没有结束时，其他事务不能访问该数据（包括了读写），这样就可以在同一个事务内两次读到的数据是一样的。可以防止脏读、不可重复读、第一类更新丢失，第二类更新丢失的问题，不过还是会出现幻读。

串行化

串行化(Serializable)，这是最高的隔离级别。它要求事务序列化执行，事务只能一个接着一个的执行，不能并发执行。在这个级别，可以解决上面提到的所有并发问题，但是可能导致大量的超时现象和锁竞争，通常不会用这个隔离级别。

总结

扩展：回滚机制

在mysql中，恢复机制是通过回滚日志（undo log）实现的，所有的事务进行的修改都会先记录到这个回滚日志中，然后在堆数据库中的对应进行写入。

mysql的事务是由redo和undo的，redo操作的所有信息都是记录到重做日志（redo_log）中，也就是说当一个事务做commit操作时，需要先把这个事务的操作写到redo_log中，然后在把这些操作flush到磁盘上，当出现故障时，只需要读取redo_log，然后在重新flush到磁盘就行了。

而对于undo就比较麻烦，mysql在处理事务时，会在数据共享表空间里申请一个段就做segment段，用保存undo信息，当在处理rollback，不是完完全全的物理undo，而是逻辑undo，也就是说会之前的操作进行反操作（对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去。），但是这些共享表空间是不进行回收的。这些表空间的回收需要由mysql的master thread进程进行回收。

索引

Msql官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。

提取句子主干，就可以得到索引的本质：索引是数据结构。

索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

• 主键索引（primary key）
  • 唯一的标识，主键不可重复，只能有一个列作为主键
• 唯一索引 （unique key）
  • 避免重复的列出现，可以重复，多个列都可以标示为唯一索引
• 常规索引（key/index）
  • 默认的 index 或者key关键字来设置
• 全文索引（FullText）
  • 在特定的数据库引擎下才有，myisam
  • 快速定位数据

基础语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- 索引的使用

-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student;

-- 新增一个索引 (索引名) 列名

ALTER TABLE `student` ADD UNIQUE KEY `UK_IDENTITY_CARD` (`identity_card`);
ALTER TABLE `student` ADD KEY `K_STUDENT_NAME`(`student_name`);

ALTER TABLE `student`  ADD FULLTEXT INDEX `FI_PHONE` (`phone`);

-- explain 分析sql执行的状况

EXPLAIN SELECT * FROM student; -- 非全文索引

EXPLAIN SELECT * FROM student WHERE MATCH(`phone`) AGAINST('138'); -- 全文索引

【MySQL优化】——看懂explain_漫漫长途，终有回转；余味苦涩，终有回甘-CSDN博客_explain

测试索引

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
43
44
45
46
47
48
49
50
51
52

CREATE TABLE app_user (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` VARCHAR(50)  DEFAULT '' COMMENT '用户昵称',
  `email` VARCHAR(50)  NOT NULL COMMENT '用户邮箱',
  `phone` VARCHAR(20)  DEFAULT '' COMMENT '手机号',
  `gender` TINYINT(4)  UNSIGNED DEFAULT '0' COMMENT '性别（0：男  1：女）',
  `password` VARCHAR(100)  NOT NULL COMMENT '密码',
  `age` TINYINT(4)  DEFAULT '0' COMMENT '年龄',
  `create_time` DATETIME  DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

-- 创建函数时报错信息：This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled
-- 错误分析：我们创建函数时必须指定我们的函数是否是
/*
 1. DETERMINISTIC 不确定的
 2. NO SQL 没有SQl语句，当然也不会修改数据
 3. READS SQL DATA 只是读取数据，当然也不会修改数据
 4. MODIFIES SQL DATA 要修改数据
 5. CONTAINS SQL 包含了SQL语句*/
-- 解决办法：set global log_bin_trust_function_creators=TRUE;

-- 插入100万数据b (函数)
    
DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
  DECLARE num INT DEFAULT 1000000;
  DECLARE i INT DEFAULT 0;
  WHILE i<num DO
    INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
    VALUES(CONCAT('用户',i),'123345@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
    SET i = i+1;
  END WHILE;
  RETURN i;
END;

-- 执行函数
SELECT mock_data();

SELECT * FROM app_user;

-- 函数中间的插入语句
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUES(CONCAT('用户X'),
'123345@qq.com',
CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),
FLOOR(RAND()*2),
UUID(),
FLOOR(RAND()*100));

测试：

1
2
3
4
5
6
7
8
9
10
11

-- 加索引前
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.440 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';

-- 创建索引
-- id_表名_字段名  索引名
-- CREATE INDEX 索引名 ON 表名(`字段名`);
CREATE INDEX id_app_user_name ON app_user(`name`);
 -- 加索引后
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.002 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';

索引在小数据量的时候，用处不大，但是再大数据的时候，区分十分明显

索引原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表不需要加索引
• 索引一般加载常用来查询的字段上

索引的数据结构

Hash类型的索引

Btree ：innodb的默认数据结构

CodingLabs - MySQL索引背后的数据结构及算法原理

权限管理和备份

用户管理

sql 命令操作

用户表：mysql.user

本质:读这张表进行增删改查

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

-- 创建用户
CREATE USER zyy IDENTIFIED BY '123456';

-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('123456');

-- 修改密码(修改指定用户密码)
SET PASSWORD FOR zyy = PASSWORD('123456');


-- 重命名  RENAME 原名子 zyy TO 新名字;
RENAME USER zyy TO newzyy;


-- 用户授权  ALL PRIVILEGES 全部的权限，库，表

-- ALL PRIVILEGES 除了给别人授权不行，其他都能干

GRANT ALL PRIVILEGES ON *.* TO newzyy;

-- 查询权限

SHOW GRANTS FOR newzyy; -- 查看指定用户的权限

SHOW GRANTS FOR root@localhost; -- 查看root用户的权限

-- 撤销权限   REVOKE哪些权限，在哪个库，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM newzyy;

-- 删除用户
DROP USER newzyy;

MySQL备份

为什么要备份？

• 保证重要的数据不丢失
• 数据转移

mysql数据库备份的方式

• 直接拷贝物理文件

• 在sqlyog这种可视化工具中手动导出

  • 在想要导出的表或者库中，右键，导出

• 使用命令行导出 mysqldump 命令行使用

  1 2 3 4 5 6 7 8 9 10 11 12 13 14

  # 一张表 mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql # 多张表 mysqldump -h主机 -u用户名 -p密码 数据库 表名1 表名2 >物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school student result >D:/a.sql # 数据库 mysqldump -h主机 -u用户名 -p密码 数据库 >物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql # 导入 # 登录的情况下，切换到指定的数据库 # source 备份文件 # 也可以这样 mysql -u用户名 -p密码 库名<备份文件

  

假设你要备份数据库，防止数据丢失。

把数据库给别人，直接给sql即可。

如何设计一个项目的数据库

为什么需要设计

==当数据库比较复杂的时候，我们就需要设计了==

糟糕的数据库设计

• 数据冗余，浪费空间
• 数据库插入和删除都会麻烦、异常（屏蔽使用物理外键）
• 程序的性能差

良好的数据库设计

• 节省内存空间
• 保证数据库的完整性
• 方便我们开发系统

软件开发中，关于数据库的设计

• 分析需求，分析业务和需要处理的数据库的需求
• 概要设计：设计关系图E-R图

设计数据库的步骤（个人博客）

• 收集信息，分析需求
  • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
  • 分类表（文章分类，谁创建的）
  • 文章表（文章信息）
  • 评论表
  • 友链表（友情链接信息）
  • 自定义表（系统信息，某个关键的字，或者一些主字段） key:value
  • 关注表(粉丝数)
  • 说说表（发表心情， id…content…create_time）
• 标识实体（把需求落到每个字段）
• 标识实体之间的关系
  • 写博客：user –> blog
  • 创建分类：user –> category
  • 关注：user –> user
  • 友链：links
  • 评论：user –> user –> blog

(bbs / crm)

三大范式

第一范式（1NF）

原子性：保证每一列不可再分

第二范式（2NF）

前提：满足第一范式

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

每张表只描述一件事情

第三范式（3NF）

前提：满足第一范式和第二范式

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

规范数据库的设计

规范性和性能的问题

关联查询的表不得超过三张表

• 考虑商业化的需求和目标（成本，用户体验）数据库的性能更加重要
• 在规范性能的问题的时候，需要适当的考虑一下规范性
• 故意给某些表增加一些冗余的字段。（从多表查询中变为单表查询）
• 故意增加一些计算列（从大数据库降低为小数据量的查询：索引）

JDBC

数据库驱动

驱动：声卡，显卡，数据库

我们的程序会通过数据库驱动，和数据库打交道！

JDBC

SUN公司为了简化开发人员的（对数据库的统一）操作，提供了一个（java操作数据库的）规范，俗称JDBC

这些规范的实现由具体的厂商去做~

对于开发人员来说，我们只需要掌握JDBC接口的操作即可！

java.sql

javax.sql

还需要导入一个数据库驱动包 mysql-connector-java-5.1.47.jar

第一个JDBC程序

创建测试数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE DATABASE jdbcstudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcstudy;

CREATE TABLE users(
  `id` INT PRIMARY KEY,
  `name` VARCHAR(40),
  `password` VARCHAR(40),
  `email` VARCHAR(60),
  `birthday` DATE
);

INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`)
VALUES(1,'张三','123456','zs@sina.com','1980-12-04'),
(2,'李四','123456','lisi@sina.com','1981-12-04'),
(3,'王五','123456','wangwu@sina.com','1982-12-04');

1. 创建一个普通项目

2. 导入数据库驱动（jar包）

   

3. 编写测试代码

   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

   public class jdbcDemo1 { public static void main(String[] args) throws ClassNotFoundException, SQLException { //1.加载驱动 Class.forName("com.mysql.jdbc.Driver"); //固定写法，加载驱动 //2.用户信息和url //useUnicode=true 支持中文 //characterEncoding=utf8 //useSSL=false 使用安全的连接 String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false"; String username = "root"; String password = "cjy521213"; //3.连接成功，数据库对象 Connection 代表数据库 Connection connection = DriverManager.getConnection(url, username, password); //4.执行SQL的对象 Statement 执行SQL的对象 Statement statement = connection.createStatement(); //5.执行SQL的对象 去 执行SQL，可能存在结果，查看返回结果 String sql = "SELECT * FROM users"; ResultSet resultSet = statement.executeQuery(sql); while (resultSet.next()){ System.out.println("id = " + resultSet.getObject("id")); System.out.println("name = " + resultSet.getObject("name")); System.out.println("password = " + resultSet.getObject("password")); System.out.println("email = " + resultSet.getObject("email")); System.out.println("birthday = " + resultSet.getObject("birthday")); System.out.println("======================="); } //6.释放连接 resultSet.close(); statement.close(); connection.close(); } }

步骤总结：

1. 加载驱动
2. 连接数据库DriverManager
3. 获取执行SQL的对象 Statement
4. 获得返回的结果集
5. 释放连接

JDBC对象解释

DriverManager

1
2
3
4
5
6
7
8
9
10
11
12
13

//1.加载驱动
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//推荐这种写法加载驱动
Class.forName("com.mysql.jdbc.Driver");

Connection connection = DriverManager.getConnection(url, userName, passWord);
// connection代表数据库
// 数据库设置自动提交
// 事务提交
// 事务回滚
connection.setAutoCommit(true);
connection.commit();
connection.rollback();

URL

1
2
3
4
5
6

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";

// mysql默认端口3306
// 协议://主机地址:端口号/数据库名?参数1&参数2&参数3
// oracle默认端口1521
// jdbc:oracle:thin:@localhost:1521:sid

Statement 执行sql对象 、 PreparedStatement 执行sql对象

1
2
3
4
5

String sql = "SELECT * FROM users";//编写SQL

statement.executeQuery();//执行查询 返回ResultSet
statement.executeUpdate();//新增，删除，修改，都用这个，返回受影响的行数
statement.execute();//执行任何SQL

ResultSet 查询的结果集，封装了所有的查询结果

获得指定的数据类型

1
2
3
4
5
6
7
8
9
10

//在不知道列类型的情况下使用
resultSet.getObject();
//如果知道列类型，就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getDouble();
resultSet.getBigDecimal();
resultSet.getFloat();
resultSet.getDate();
//...

遍历，指针

1
2
3
4
5

resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行

释放资源

1
2
3

resultSet.close();
statement.close();
connection.close();//消耗资源

statement对象详解

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

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

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

CRUD操作-create

使用executeUpdate(String sql)方法完成数据添加操作，示例操作：

1
2
3
4
5
6

Statement statement = connection.createStatement();
String sql = "insert into user(...) values(...)";
int num = statement.executeUpdate(sql);
if (num > 0) {
    System.out.println("插入成功~");
}

CRUD操作-delete

1
2
3
4
5
6

Statement statement = connection.createStatement();
String sql = "delete from user where id=1";
int num = statement.executeUpdate(sql);
if (num > 0) {
    System.out.println("删除成功~");
}

CRUD操作-update

1
2
3
4
5
6

Statement statement = connection.createStatement();
String sql = "update user set name='' where name =''";
int num = statement.executeUpdate(sql);
if (num > 0) {
    System.out.println("修改成功~");
}

CRUD操作-read

1
2
3
4
5
6

Statement statement = connection.createStatement();
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
    //根据获取列的数据类型，分别调用resultSet的相应方法映射到java对象中
}

代码实现

1. 提取工具类

   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 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67

   package com.chang.lesson2.utils; import java.io.IOException; import java.io.InputStream; import java.sql.*; import java.util.Properties; /** * @author 行初雾 * @PackageName:com.chang.lesson2.utils * @ClassName: jdbcUtil * @Description: * @date 2022/1/21 2:55 */ public class jdbcUtil { private static String driver = null; private static String url = null; private static String username = null; private static String password = null; static { try { InputStream in = jdbcUtil.class.getClassLoader().getResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(in); driver = properties.getProperty("driver"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); //驱动只用加载一次 Class.forName(driver); } catch (Exception e) { e.printStackTrace(); } } //获取连接 public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url,username,password); } //释放连接资源 public static void release(Connection conn, Statement st, ResultSet rs){ if(rs!=null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(st!=null){ try { st.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }

   配置文件db.properties

   1 2 3 4

   driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false username=root password=cjy521213

2. 编写增删改的方法，executeUpdate

   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

   package com.chang.lesson2; import com.chang.lesson2.utils.jdbcUtil; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * @author 行初雾 * @PackageName:com.chang.lesson2 * @ClassName: testInsert * @Description: * @date 2022/1/21 2:55 */ public class testInsert { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = jdbcUtil.getConnection(); //获取数据库连接 st = conn.createStatement(); //获取SQL的执行对象 String sql = "INSERT INTO users(id,`name`,`password`,`email`,`birthday`)\n" + "VALUE(4,'行初雾','123456','1092315259@qq.com','1999-05-25')"; int i = st.executeUpdate(sql); if(i > 0){ System.out.println("插入成功！"); } } catch (SQLException e) { e.printStackTrace(); } finally { jdbcUtil.release(conn,st,rs); } } }

   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

   package com.chang.lesson2; import com.chang.lesson2.utils.jdbcUtil; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * @author 行初雾 * @PackageName:com.chang.lesson2 * @ClassName: testDelete * @Description: * @date 2022/1/21 3:15 */ public class testDelete { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = jdbcUtil.getConnection(); //获取数据库连接 st = conn.createStatement(); //获取SQL的执行对象 String sql = "DELETE FROM users WHERE id = 4"; int i = st.executeUpdate(sql); if(i > 0){ System.out.println("删除成功！"); } } catch (SQLException e) { e.printStackTrace(); } finally { jdbcUtil.release(conn,st,rs); } } }

   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

   package com.chang.lesson2; import com.chang.lesson2.utils.jdbcUtil; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * @author 行初雾 * @PackageName:com.chang.lesson2 * @ClassName: testUpdate * @Description: * @date 2022/1/21 3:17 */ public class testUpdate { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = jdbcUtil.getConnection(); //获取数据库连接 st = conn.createStatement(); //获取SQL的执行对象 String sql = "UPDATE users SET `name` = '行初雾',`email` = '1092315259@qq.com' WHERE id=1"; int i = st.executeUpdate(sql); if(i > 0){ System.out.println("更新成功！"); } } catch (SQLException e) { e.printStackTrace(); } finally { jdbcUtil.release(conn,st,rs); } } }

3. 查询

   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

   package com.chang.lesson2; import com.chang.lesson2.utils.jdbcUtil; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * @author 行初雾 * @PackageName:com.chang.lesson2 * @ClassName: testSelect * @Description: * @date 2022/1/21 3:20 */ public class testSelect { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = jdbcUtil.getConnection(); //获取数据库连接 st = conn.createStatement(); //获取SQL的执行对象 String sql = "SELECT * FROM users WHERE id = 1"; rs = st.executeQuery(sql); while(rs.next()){ System.out.println(rs.getString("name")); } } catch (SQLException e) { e.printStackTrace(); } finally { jdbcUtil.release(conn,st,rs); } } }

sql注入问题

sql存在漏洞，会被攻击导致数据泄露 ==SQL会被拼接==

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
43
44
45
46
47

package com.chang.lesson2;

import com.chang.lesson2.utils.jdbcUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author 行初雾
 * @PackageName:com.chang.lesson2
 * @ClassName: sqlReinject
 * @Description: sql注入
 * @date 2022/1/21 3:34
 */
public class sqlReinject {
    public static void main(String[] args) {
    	//正常登录
    	login("行初雾","123456");
    	
    	//sql注入
        login(" 'or '1=1","'or '1=1");
    }
    public static void login(String name,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = jdbcUtil.getConnection(); //获取数据库连接
            st = conn.createStatement(); //获取SQL的执行对象

            String sql = "SELECT * FROM users WHERE `name`= '"+name+"' AND `password`='"+password+"'";

            rs = st.executeQuery(sql);
            while(rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.release(conn,st,rs);
        }
    }
}

导致结果：错误的用户名或者密码可以获取到全部的用户信息

PreparedStatement对象

PreparedStatement可以防止SQL注入，效率更好

1. 增加

   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 43 44

   package com.chang.lesson3; import com.chang.lesson2.utils.jdbcUtil; import java.sql.*; /** * @author 行初雾 * @PackageName:com.chang.lesson3 * @ClassName: testInsert * @Description: * @date 2022/1/21 4:21 */ public class testInsert { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = jdbcUtil.getConnection(); //使用？占位符代替参数 String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES (?,?,?,?,?)"; //预编译SQL，先写SQL，然后不执行 st = conn.prepareStatement(sql); //手动给参数赋值 st.setInt(1,4); st.setString(2,"行初雾"); st.setString(3,"123456"); st.setString(4,"1092315259@qq.con"); st.setDate(5,new java.sql.Date(new java.util.Date().getTime())); int i = st.executeUpdate(); if(i > 0){ System.out.println("插入成功！"); } } catch (SQLException e) { e.printStackTrace(); } finally { jdbcUtil.release(conn,st,rs); } } }

2. 删除

   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

   import com.zyy.lesson02.utils.JDBCUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * @ClassName: TestDelete * @Description: TODO 类描述 * @Author: zyy * @Date: 2021/07/14 18:19 * @Version: 1.0 */ public class TestDelete { public static void main(String[] args) { Connection con = null; PreparedStatement st = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); //使用?占位符代替参数 String sql = "DELETE FROM users WHERE `id`=?"; //预编译SQL，先写SQL，然后不执行 st = con.prepareStatement(sql); //手动给参数赋值 st.setInt(1, 5); int num = st.executeUpdate(); if (num > 0) { System.out.println("删除成功！"); } } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.release(con, st, rs); } } }

3. 更新

   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 43 44

   package com.chang.lesson3; import com.chang.lesson2.utils.jdbcUtil; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * @author 行初雾 * @PackageName:com.chang.lesson3 * @ClassName: testUpdate * @Description: * @date 2022/1/21 4:38 */ public class testUpdate { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = jdbcUtil.getConnection(); //使用？占位符代替参数 String sql = "update users set `name`=? where `id` = ?"; //预编译SQL，先写SQL，然后不执行 st = conn.prepareStatement(sql); //手动给参数赋值 st.setInt(2,5); st.setString(1,"行初雾"); int i = st.executeUpdate(); if(i > 0){ System.out.println("修改成功！"); } } catch (SQLException e) { e.printStackTrace(); } finally { jdbcUtil.release(conn,st,rs); } } }

4. 查询

   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 43 44 45

   package com.chang.lesson3; import com.chang.lesson2.utils.jdbcUtil; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * @author 行初雾 * @PackageName:com.chang.lesson3 * @ClassName: testSelect * @Description: * @date 2022/1/21 4:40 */ public class testSelect { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = jdbcUtil.getConnection(); //使用？占位符代替参数 String sql = "select * from users where id=?"; //预编译SQL，先写SQL，然后不执行 st = conn.prepareStatement(sql); //手动给参数赋值 st.setInt(1,5); rs = st.executeQuery(); while(rs.next()){ System.out.println("id="+rs.getInt("id")); System.out.println("name=" + rs.getString("name")); System.out.println("======"); } } catch (SQLException e) { e.printStackTrace(); } finally { jdbcUtil.release(conn,st,rs); } } }

5. 防止sql注入

   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 43 44

   package com.chang.lesson3; import com.chang.lesson2.utils.jdbcUtil; import java.sql.*; import java.util.Properties; /** * @author 行初雾 * @PackageName:com.chang.lesson3 * @ClassName: sqlReinject * @Description: * @date 2022/1/21 4:43 */ public class sqlReinject { public static void main(String[] args) { //正常登录 login("行初雾","123456"); //sql注入 //login("' or '1=1","123456"); } public static void login(String name,String password){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = jdbcUtil.getConnection(); //获取数据库连接 String sql = "SELECT * FROM users WHERE `name`=? AND `password`=?"; st = conn.prepareStatement(sql); st.setString(1,name); st.setString(2,password); rs = st.executeQuery(); while(rs.next()){ System.out.println(rs.getString("name")); System.out.println(rs.getString("password")); } } catch (SQLException e) { e.printStackTrace(); } finally { jdbcUtil.release(conn,st,rs); } } }

   执行结果：查不到任何结果

使用idea连接数据库

连接成功后，就可以选择数据库

连接不上的话，可以看一下下面这里，配置对应的mysql版本

双击数据库

更新数据（提交）

idea编写sql

1
2
3
4
5
6
7
8
9
10
11

create table account
(
    id    int primary key auto_increment,
    name  varchar(40),
    money float
);

insert into account(name, money)
values ('A', 1000),
       ('B', 1000),
       ('C', 1000);

JDBC操作事务

==要么都成功，要么都失败==

ACID原则

原子性：要么全部成功，要么全部失败

一致性：总数不变

隔离性：多个进程互不干扰

持久性：一旦提交不可逆，持久化到数据库了

隔离性的问题：

脏读：一个事务读取了另外一个没有提交的事务

不可重复读：在同一个事务内，重复读取表中数据，表数据发生了改变

幻读：在一个事务内，读取到了别人插入的数据，导致前后读出来的结果不一致

代码实现

1. 开启事务con.setAutoCommit(false);
2. 一组业务执行完毕，提交事务
3. 可以在catch语句中显示的定义回滚语句，但是默认失败就会回滚

正常情况

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
43
44
45
46
47
48
49
50

package com.chang.lesson4;

import com.chang.lesson2.utils.jdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author 行初雾
 * @PackageName:com.chang.lesson4
 * @ClassName: testTransaction
 * @Description:
 * @date 2022/1/21 5:07
 */
public class testTransaction {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = jdbcUtil.getConnection();
            //关闭自动提交 自动开启事务
            conn.setAutoCommit(false);//开启事务

            String sql1 = "update account set money=money-100 where name='A";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            String sql2 = "update account set money=money+100 where name='B";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            //事务完毕，提交事务
            conn.commit();
            System.out.println("成功！");
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            jdbcUtil.release(conn,st,rs);
        }
    }
}

异常情况

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
43
44
45
46
47
48
49
50
51
52
53

package com.chang.lesson4;

import com.chang.lesson2.utils.jdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author 行初雾
 * @PackageName:com.chang.lesson4
 * @ClassName: testTransaction
 * @Description:
 * @date 2022/1/21 5:07
 */
public class testTransaction {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = jdbcUtil.getConnection();
            //关闭自动提交 自动开启事务
            conn.setAutoCommit(false);//开启事务

            String sql1 = "update account set money=money-100 where name='A";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();
            
            //默认失败
            int x = 1/0;//一定会异常

            String sql2 = "update account set money=money+100 where name='B";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            //事务完毕，提交事务
            conn.commit();
            System.out.println("成功！");
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            jdbcUtil.release(conn,st,rs);
        }
    }
}

DBCP-C3P0连接池

数据库连接 – 执行完毕 – 释放

连接– 释放 是十分浪费系统资源的

池化技术：准备一些预先的资源，过来就连接预先准备好的

最小连接数：10(常用连接)

最大连接数：100 （业务最高承载上线）

等待超时：100ms

编写连接池，实现一个接口DataSource

开源数据源实现

DBCP

C3p0

Druid:阿里巴巴

使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了

DBCP

需要用到的jar包

commons-dbcp-1.4

commons-pool-1.6

配置文件dbcp.properties

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

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

#初始化连接
initialSize=10

#最大连接数量
maxActive=50

#最大空闲连接
maxIdle=20

#最小空闲连接
minIdle=5

#超时等待时间以毫秒为单位 6000毫秒/1000等于60秒
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：【属性名=property;】
#注意：user 与 password 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_COMMITTED

工具类

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @ClassName: JDBCDBCPUtils
 * @Description: TODO 类描述
 * @Author: zyy
 * @Date: 2021/07/14 17:48
 * @Version: 1.0
 */
public class JDBCDBCPUtils {
    private static DataSource dataSource = null;

    static {
        try {
            InputStream in = JDBCDBCPUtils.class.getClassLoader().getResourceAsStream("dbcp.properties");
            Properties properties = new Properties();
            properties.load(in);
            //创建数据源 工厂模式
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     */
    public static Connection getConnection() throws SQLException {
        //从数据源中获取连接
        return dataSource.getConnection();
    }

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

测试类