MySQL数据库笔记

修改MySQL密码，可在的命令行输入：将密码修改为 1234

ALTER USER 'root'@'localhost' IDENTIFIED BY '1234';
1

笔记根据 狂神说 Java 记录。

一、初识MySQL

1.1、什么是数据库

数据库 （DB，DataBase）

概念：数据仓库，软件，安装在操作系统（windows,linux, mac…）之上！SQL，可以存储大量的数据，500万以下都可以存储。

作用：存储数据，管理数据

1.2、数据库分类

关系型数据库：（SQL）

• MySQL，Oracle，Sql Server，DB2，SQLlite
• 通过表和表之间，行和列之间的关系进行数据的存储 如学员信息表，考勤表…

非关系型数据库：（No SQL）Not Only

• Redis，MongDB

• 非关系型数据库，对象存储，通过对象自身的属性来决定。

DBMS：数据库管理系统 DataBase Management System

• 数据库的管理软件，科学有效的管理我们的数据。维护和获取数据；

1.3、MySQL简介

MySQL是一个**关系型数据库管理系统**

前世：由瑞典[MySQL AB](https://baike.baidu.com/item/MySQL AB/2620844?fromModule=lemma_inlink) 公司开发

今生：属于 Oracle 旗下产品

MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

开源的数据库软件~

特点：体积小、速度快、总体拥有成本低，招人成本比较低

中小型网站，或者大型网站都可以用，还可以做集群！

官网：https://www.mysql.com/

5.7稳定

安装建议：

1. 尽量不要使用exe，会生成注册表
2. 尽可能使用压缩包安装~

1. 查询MySQL的版本：select version();
2. 查询MySQL当前使用的字符集：show variables like “%char%”;
3. 查询指定数据库的指定数据表的状态信息：show table status from ‘tableName’ like ‘%tableName%’;
4. 查看数据库表所有字段的编码格式：show full columns from tableName;
5. 查看表的编码格式：show create table tableName;

二、操作数据库

2.1、操作数据库（了解）

1、创建数据库

CREATE DATABASE [IF NOT EXISTS] db;
1

2、删除数据库

DROP DATABASE [IF EXISTS] db;
1

3、使用数据库

-- tab键的上面，如果表名或者字段名是一个特殊字符，就需要带``
USE ``school`
1
2

4、查看数据库

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

2.2、数据库的列类型

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小 3个字节
• int 标准的整数 4个字节（常用）
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节 （精度问题）
• decimal 字符串形式的浮点数,金融计算的时候，一般用

字符串

• char 字符串搞定大小的 0~255
• varchar 可变字符串 0~65535 常用的变量 String
• tinytext 微型文本 2^8-1
• text 文本串 2^16-1 保存大文本

日期时间

java.util.Date

• date YYYY-MM-DD，日期
• time HH:mm:ss 时间格式
• datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
• timestamp 时间戳 1970.1.1到现在的毫秒数
• year 年份表示

null

• 没有值，未知
• 注意，不要使用null进行运算，结果为null

2.3、数据库的字段属性（重点）

Unsigned：

• 无符号的证书
• 声明了

zerofill：

• 0填充

自增：

• 通常理解为自增，自动在上一条记录的基础上 +1 （默认）
• 通常用来设计唯一的主键 index,必须是整数类型
• 可以自定义设置主键自增的起始值和步长

非空 NULL not null：

• 假设设置为 not null，如果不给他赋值，就会报错
• NULL 如果不填写，默认为NULL

默认：

• 设置默认的值！

扩展：

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

2.4、创建数据库表（重点）

--目标:创建一个schoo1数据库
--创建学生表(列,字段)使用SQL 创建
--学号int   登录密码varchar(20)   姓名,性别varchar(2)  出生日期(datatime)  家庭住址，emai1
--注意点使用英文()，表的名称和字段尽量使用括起来

-- AUTO_ INCREMENT 自增
-- COMMENT 属性注释
-- DEFAULT 默认值
-- NUT NULL 必须不为空
-- 字符串使用单引号括起来!
-- ENGINE=INNODB 设置引擎
-- 所有的语句后面加，(英文的)，最后一个不用加

-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键!

CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) DEFAULT NULL COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `address` VARCHAR (50) DEFAULT NULL COMMENT '家庭住址',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY(`id`)
) 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

常用命令：

SHOW CREATE DATABASE kuang -- 查看创建数据库的语句
SHOW CREATE TABLE student; -- 查看student数据表的定义语句
DESC student -- 显示表的结构
1
2
3

2.5、数据表的类型

-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/
1
2
3
4
5

常规使用操作：

• MYISAM 节约空间，速度较快，
• INNODB 安全性高，事务处理，多表多用户操作

在物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库，本质还是文件的存储

MySQL 引擎在物理文件上的区别

• InnoDB 在数据库表中，只有一个*.frm文件，以及上级目录下的ibdata1文件

• MYISAM 对应的文件

  • *.frm – 表结构的定义文件

  • *. MYD --存放表的数据文件

  • *.MYI --存放表的索引文件

设置数据库字符集编码

CHARSET=utf8
1

不设置的话，会是mysql默认的字符集编码-（不支持中文！）

MySQL的默认编码 Latin1，不支持中文

可以在my.ini中配置默认的编码（不建议）

 character-set-server=utf8
1

2.6、修改删除表

修改

--修改表名：ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
--增加表的字段（重命名，修改约束！）
--ALTER TABLE 表名 MODIFY age 字段名 列属性[]
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
1
2
3
4
5
6
7
8
9
10

删除

-- 删除表 (如果表存在再删除)
DROP TABLE IF EXISTS teacher1
1
2

所有的创建和删除操作尽量加上判断，以免报错~

注意点：

• ``字段名，使用这个包裹！
• 注释 – /**/
• sql关键字大小写不敏感，建议大家写小写
• 所有的符合用英文！

三、MySQL数据管理

3.1、外键（了解即可）

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

CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) DEFAULT NULL COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `address` VARCHAR (50) DEFAULT NULL COMMENT '家庭住址',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY(`id`),
  FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)-- 这句代码是设置外键的
) ENGINE = INNODB DEFAULT CHARSET=utf8
1
2
3
4
5
6
7
8
9
10
11

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

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

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

-- ALTER TABLE`表名`  ADD CONSTRAINT `FK_约束名` FOREIGN KEY（作为外键的列） REFERENCES 哪个表(哪个字段)
1
2
3
4

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

最佳实践

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

3.2、DML语言（全部记住）

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

DML语言：

• Insert
• Update
• Delete

3.3、添加

插入语句

-- 插入语句的语法
insert into `表名`([字段一，字段二]) values('值1','值2','值3')

insert into `student`(`name`,`sex`,`birthday`) values('小明','男','2000-01-01')

-- 一般写插入语句，我们一定要数据和字段一一对应。（不写字段默认全部字段）
-- 插入多行，针对一个字段，每行一个括号，一个()表示一个对象
insert into `grade`(`gradename`) values ('大二'),('大一');
1
2
3
4
5
6
7
8

注意事项：

1. 字段和字段之间用逗号分开
2. 字段可以省略，但是后面的值必须一一对应
3. 可以同时插入多条数据，VALUES后面的值需要使用，隔开即可values(‘值1’),(‘值2’)....

3.4、修改

update 修改的表名 set 旧值 = 新值 where 条件;

-- 语法：第一个必填，后面可选
UPDATE 表名 SET column_name = value,[column_name = value,...] where [条件]

-- 修改学员名字，带了简介
UPDATE `student` SET `name`='狂神' WHERE id=1;

-- 不指定条件的条件下，会改动所有的表！
UPDATE `student` SET `name`='长江一号'

-- 修改多个属性，逗号 隔开
UPDATE `student` SET `name`='狂神',`email`='123@qq.com' WHERE id=1;
1
2
3
4
5
6
7
8
9
10
11

条件：where 子句 运算符 id 等于 某个值，大于某个值，在某个区间内修改
操作符返回布尔值

语法：

UPDATE 表名 SET colnum_name=value,[colnum_name=value,...] WHERE [条件]
1

-- 通过多个条件定位数据，无上限！
UPDATE `student` SET `name`='狂神12' WHERE id=1 AND sex='女'
UPDATE `student` SET `birthday`=CURRENT_TIME() WHERE id=2
1
2
3

注意：

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

3.5、删除

delete 命令

-- 删除指定数据
DELETE FROM `student` WHERE id=2
1
2

TRUNCATE 命令

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

-- 清空 student 表
TRUNCATE `student`
1
2

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=utf8

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

了解即可：DALETE 删除的问题，重启数据库，现象

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

四、DQL查询数据(最重点)

4.1、DQL

(Data Query Language) :数据查询语言

所有的查询操作都用它 Select

简单的查询，复杂的查询它都能做

数据库中最核心的语言

使用频率最高的语言

SELECT 完整的语法：

SELECT [ALL | DISTINCT]
{- | table.* | [table.field1[as alias1][,table.filed2[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

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

4.2、指定查询字段

-- 查询全部数据
SELECT * FROM `dish_flavor` 

-- 查询指定字段
SELECT `id`,`name` FROM dish_flavor

-- 别名，给结果起一个名字 AS 可以给字段起别名，也可以给表起别名
SELECT `name` AS 名字,`value` AS 备注 FROM dish_flavor

-- 函数 concat(a,b)
SELECT CONCAT('名字：',NAME) AS 新名字 FROM dish_flavor
1
2
3
4
5
6
7
8
9
10
11

语法：SELECT 字段,… FROM 表

有时候，列名字不是那么见名知意。我们起别名 AS 字段名 AS 别名 表名 AS 别名

去重 distinct

作用：去除SELECT查询出来的结果中重复的结果，重复的数据只显示一条

SELECT * FROM dish_flavor	-- 查询所有

SELECT `name` FROM dish_flavor

SELECT DISTINCT `name` FROM dish_flavor	--发现重复数据，去重
1
2
3
4
5

数据库的列（表达式）

SELECT VERSION()	-- 查询系统版本（函数）
SELECT 100*3-1 AS 计算结果	-- 用来计算（表达式）
SELECT @@auto_increment_increment	-- 查询自增的步长（变量）

-- 学员考试成绩 +1分 查看
SELECT `studentno` , `studentresult` + 1 as '提分后' from result;
1
2
3
4
5
6

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

select 表达式 from 表

4.3、where条件子句

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

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

逻辑运算符

尽量使用英文字母

– 模糊查询（区间）

-- 查询考试成绩在 95~100分之间
SELECT studentNo,StudentResult FROM result 
WHERE studentresult BETWEEN 95 AND 100;


-- 模糊查询 （区间）
SELECT studentNo,StudentResult FROM result 
WHERE studentNo != 1000;

SELECT studentNo,StudentResult FROM result 
WHERE NOT studentNo = 1000;
1
2
3
4
5
6
7
8
9
10
11

模糊查询：比较运算符

-- ====== like =====
SELECT `id`,`value` FROM `dish_flavor`
WHERE `name` LIKE '辣%'

SELECT `id`,`value` FROM `dish_flavor`
WHERE `name` LIKE '辣_'

SELECT `id`,`value` FROM `dish_flavor`
WHERE `name` LIKE '%辣%'

-- ===== in (具体的一个或多个值)=====
SELECT `id`,`value` FROM `dish_flavor`
WHERE `name` IN ('忌口')
1
2
3
4
5
6
7
8
9
10
11
12
13

4.4、联表查询

join

-- 联表查询

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

/*
1. 分析需求，分析查询的字段来自哪些表
2.确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件： 学生表中 studentNo = 成绩表中 studentNo 
*/

-- join on(条件判断) 连接查询   on在取表之前判断
-- where 等值查询              where在取表之后才过滤

-- inner join

SELECT s.studentno, studentname, subjectno, studentresult 
FROM student as s 
inner join result as r
on s.studentno = r.studentno

-- right join
SELECT s.studentno, studentname, subjectno, studentresult 
FROM student s 
right join result r
on s.studentno = r.studentno

-- left join
SELECT s.studentno, studentname, subjectno, studentresult 
FROM student s 
left join result r
on s.studentno = r.studentno

-- 查询缺考的同学
SELECT s.studentno, studentname, subjectno, studentresult 
FROM student s 
left join result r
on s.studentno = r.studentno
where studentresult is null

-- 思考题(查询了参加考试的同学信息：学号 姓名 科目 分数)

/*
1. 分析需求，分析查询的字段来自哪些表 student result subject
2.确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件： 学生表中 studentNo = 成绩表中 studentNo 
*/

SELECT s.studentno,studentname,subjectname,studentresult
FROM student s 
right join result r
on r.studentno = s.studentno
inner join `subject` sub on r.subjectno = sub.subjectno

-- 查询学员所属的年级（学号，姓名，年级）
SELECT studentno,studentname,gradename
FROM student s
inner join grade g
on s.gradeid = g.gradeid

-- 查询参加c语言-2考试的同学信息 学号 姓名 科目名 分数
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
inner join result r
on s.studentno = r.studentno
inner join subject sub
on r.subjectno = sub.subjectno
where sub.subjectname = 'C语言-2'

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

-- from a left join b   左为准
-- from a right join b	右为准
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
73
74
75
76
77
78

自连接

CREATE TABLE `category`(
  `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
  `pid` INT(10) NOT NULL COMMENT '父id',
  `categoryname` VARCHAR(50) NOT NULL COMMENT '主题名字',
  PRIMARY KEY (`categoryid`) 
) ENGINE=INNODB  AUTO_INCREMENT=9 DEFAULT CHARSET=utf8; 

INSERT INTO `category` (`categoryid`, `pid`, `categoryname`) 
VALUES ('2','1','信息技术'),
('3','1','软件开发'),
('5','1','美术设计'),
('4','3','数据库'),
('8','2','办公信息'),
('6','3','web开发'),
('7','5','ps技术');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

自己的表跟自己的表连接，核心：一张表拆为两张一样的表

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

-- 查询父子信息 子类的pid等于父类的id
SELECT a.categoryname as '父栏目',b.categoryname as '子栏目'
from category as a, category as b
WHERE a.categoryid = b.pid
1
2
3
4

4.5、分页和排序

排序：升序 ASC；降序 DESC

-- 排序：升序 ASC；降序 DESC
-- ORDER BY 通过哪个字段排序，怎么排
-- 查询的结果根据 价格 降序排序
SELECT d.id,d.name,d.price 
FROM dish d
INNER JOIN category c
ON d.`category_id`=c.`id`
WHERE c.`name`='湘菜'
ORDER BY d.`price` DESC
1
2
3
4
5
6
7
8
9

分页：

-- 为什么要分页？
-- 缓解数据库压力，给人的体验更好，  瀑布流（无限往下刷，刷不到低，抖音就类似）

-- 分页，每页只显示五条数据
-- 语法：LIMIT 起始页,页面大小
-- LIMIT 0,5 -- 1-5
-- LIMIT 1,5 -- 2-6
-- LIMIT 6,5

SELECT d.id,d.name,d.price 
FROM dish d
INNER JOIN category c
ON d.`category_id`=c.`id`
WHERE c.`name`='湘菜'
ORDER BY d.`price` DESC
LIMIT 0,5


-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第N页 limit pageSize*（n-1）,pageSize
-- 【pageSize*（n-1）,pageSize】
-- 【pageSize：页面大小】
-- 【n：当前页】
-- 【数据总数/页面大小=总页数】
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

语法：LIMIT(查询起始下标，pageSize)

4.6、子查询

where (这个值是计算出来的)
本质：在where语句中嵌套一个子查询语句

/*============== 子查询 ================
什么是子查询?
在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
嵌套查询可由多个子查询组成,求解的方式是由里及外;
子查询返回的结果一般都是集合,故而建议使用IN关键字;
*/

-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
-- 方法一:使用连接查询
SELECT studentno,r.subjectno,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC;

-- 方法二:使用子查询(执行顺序:由里及外)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = '数据库结构-1'
 )
ORDER BY studentresult DESC;

-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
-- 方法一:使用连接查询
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = '高等数学-2' AND StudentResult>=80

-- 方法二:使用连接查询+子查询
-- 分数不小于80分的学生的学号和姓名
SELECT r.studentno,studentname 
FROM student s
INNER JOIN result r 
ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80

-- 在上面SQL基础上,添加需求:课程为 高等数学-2
SELECT r.studentno,studentname 
FROM student s
INNER JOIN result r 
ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = '高等数学-2'
 )

-- 方法三:使用子查询
-- 分步写简单sql语句,然后将其嵌套起来
SELECT studentno,studentname FROM student WHERE studentno IN(
   SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
      SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

4.7、分组和过滤

-- 查询不同课程的平均分,最高分,最低分
-- 前提:根据不同的课程进行分组

SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
FROM result AS r
INNER JOIN `subject` AS s
ON r.subjectno = s.subjectno
GROUP BY r.subjectno
HAVING 平均分>80;

/*
where写在group by前面.
要是放在分组后面的筛选
要使用HAVING..
因为having是从前面筛选的字段再筛选，而where是从数据表中的>字段直接进行的筛选的
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

4.8、select小结

五、MySQL函数

官网：https://dev.mysql.com/doc/refman/5.7/en/

5.1、常用函数

-- 数学运算

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

-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 返回字符串长度
SELECT CONCAT('我','爱','中','国') -- 拼接字符串
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') -- 从某个位置开始替换某个长度 (str,pos,len,newstr)
SELECT UPPER('KuangShen') 
SELECT LOWER('KuangShen')
SELECT INSTR('KuangShen','h') --返回首次出现的位置7
SELECT REPLACE('坚持就能成功','坚持','努力')
SELECT SUBSTR('狂神说坚持就能成功',4,6) -- 返回指定的子字符串，即返回从4开始的长度为6的字符串(源字符串，截取的位置，截取的长度)
SELECT REVERSE('大大小小')  -- 反转


-- 查询姓 周 的同学 ，改成邹
SELECT REPLACE(studentname,'周','邹') FROM student WHERE studentname LIKE '周%'

-- 时间和日期函数（记住）
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前日期
SELECT LOCATIME()  -- 本地时间
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
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

5.2、聚合函数（常用）

-- 聚合函数
-- COUNT:非空的
SELECT COUNT(studentname) FROM student; -- 会忽略null
SELECT COUNT(*) FROM student;  -- 不会忽略null
SELECT COUNT(1) FROM student;  -- 推荐，会忽略null

-- 从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。
-- count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
-- count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录；
-- count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录 。
/*
很多人认为count(1)执行的效率会比count(*)高，原因是count(*)会存在全表扫描，而count(1)可以针对一个字段进行查询。其实不然，count(1)和count(*)都会对全表进行扫描，统计所有记录的条数，包括那些为null的记录，因此，它们的效率可以说是相差无几。而count(字段)则与前两者不同，它会统计该字段不为null的记录条数。

下面它们之间的一些对比：

1）在表没有主键时，count(1)比count(*)快
2）有主键时，主键作为计算条件，count(主键)效率最高；
3）若表格只有一个字段，则count(*)效率较高。
*/

SELECT SUM(StudentResult) AS 总和 FROM result;
SELECT AVG(StudentResult) AS 平均分 FROM result;
SELECT MAX(StudentResult) AS 最高分 FROM result;
SELECT MIN(StudentResult) AS 最低分 FROM result;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

5.3、数据库级别的MD5加密（扩展）

什么是 MD5？

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

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

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

-- ===== 测试MD5加密=====

CREATE TABLE `testmd5`(
	`id` INT(4) NOT NULL COMMENT 'ID',
	`name` VARCHAR(20) NOT NULL COMMENT '姓名',
	`pwd` VARCHAR(40) NOT NULL COMMENT '密码',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

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

-- 加密
UPDATE `testmd5` SET pwd=MD5(pwd) WHERE id=1
UPDATE `testmd5` SET pwd=MD5(pwd) WHERE id!=1-- 加密全部的密码

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

-- 如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name`='小明' AND pwd=MD5('123456')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

六、事务

6.1、什么是事务

要么都成功，要么都失败

---

1、SQL执行第一个： A 给 B 转账 A有1000 转 —> 200； B有200

2、SQL执行第二个： B 收到 A 的钱 A剩 800 —> B剩 400

---

将一组SQL放在一个批次中去执行~

事务原则： ACID 原则：原子性，一致性，隔离性，持久性 会出现（脏读，幻读…）

原子性（Atomicity）：

要么都成功，要么都失败

一致性（Consistency）：

事务前后的数据都是唯一的，要保持一致

持久性（Durability）：

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

事务没有提交，就恢复原状。

隔离性（Isolation）：

事务产生多并发时，互不干扰。

隔离所导致的一些问题：

脏读：

一个事务读取了另一个事务未提交的数据。

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。

虚读/幻读：

一个事务读取了别的事务插入的数据，导致前后读取不一致。

6.2、执行事务

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

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

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

-- sql
INSERT XX
INSERT XX

-- 提交 ： 持久化(成功)
COMMIT 
-- 回滚：  回到原来的样子（失败）
ROLLBACK
-- 事务结束(提交，回滚)
SET autocommit = 1 -- 开启自动提交

-- 了解
SAVEPOINT 保存点名称 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点 -- 删除保存点
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

事务的处理过程：

只有成功才能提交，失败不能提交！

练习：转账

-- ==== 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE 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=utf8

INSERT INTO `account`(`name`,`money`) 
VALUES('A',2000),('B',10000)

-- 模拟转账：事务
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 TRANSACTION=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

回滚在什么时候用？用在哪里？

七、索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

索引的作用

• 提高查询速度
• 确保数据的唯一性
• 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
• 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
• 全文检索字段进行搜索优化.

7.1、索引的分类

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

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

#方法一：创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);


-- 方法二：CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;


#方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;


#删除索引：DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;
#显示索引信息: SHOW INDEX FROM student;

/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);

/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';

/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('love');

/*
开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时，要先看一下自己的 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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

7.2、测试索引

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

CREATE TABLE `app_user` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT '',
  `email` VARCHAR(50) NOT NULL,
  `phone` VARCHAR(20) DEFAULT '',
  `gender` TINYINT(4) UNSIGNED DEFAULT '0',
  `password` VARCHAR(100) NOT NULL DEFAULT '',
  `age` TINYINT(4) DEFAULT NULL,
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 插入100万数据
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`)
    VALUE(CONCAT('用户',i),'534240118@qq.com',CONCAT('18',FLOOR(RAND()*9999999)),FLOOR(RAND()*2),
           UUID(),FLOOR(RAND()*100));
    SET i = i+1;
  END WHILE;
  RETURN i;
END;

SELECT mock_data(); -- 执行函数

SELECT * FROM app_user WHERE `name`='用户9999' -- 接近1秒
--分析一下上一条语句，发现查询了99999条数据
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999'  -- 查询99999条记录

-- 索引名 id_表名_字段名
-- create index 索引名 on 表名(字段)
CREATE INDEX id_app_user_name ON app_user(`name`); 
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999'  -- 0.001 s
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

部分代码摘自

7.3、索引原则

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

索引的数据结构

Hash类型的索引，

B树：InnoDB 的默认数据结构

八、权限管理和备份

8.1、用户管理

SQL yog 可视化

SQL 命令操作

用户表：

-- 创建用户  CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER kuangshen IDENTIFIED BY '123456'

-- 修改密码（修改当前密码）
SET PASSWORD = PASSWORD('111111')

-- 修改密码（修改指定用户密码）

SET PASSWORD FOR kuangshen = PASSWORD('111111')

-- 重命名  rename user 原名字 to 新名字
RENAME USER kuangshen TO kuangshen2

-- 用户授权   ALL PRIVILEGES 全部的权限   库，表
-- ALL PRIVILEGES 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO kuangshen2

-- 查询权限
SHOW GRANTS FOR kuangshen2  -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost


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

-- 删除用户
DROP USER kuangshen2
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

8.2、MySQL备份

为什么要备份？

• 保证重要的数据不丢失
• 数据转移 A—> B

数据库都是存在物理磁盘上的，可以直接拷走

MySQL数据库备份的方式

• 直接拷贝物理文件

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

  • 在想要导出的表或库中，右键，选择备份或导出
  

• 使用命令行 mysqldump 命令行使用

# mysqldump -h 主机 -u 用户 -p 密码 数据库名 表名 > 物理磁盘位置.文件名
mysqldump -hlocalhost -uroot -p1234 kuang student >C:/a.sql

# mysqldump -h 主机 -u 用户 -p 密码 数据库名 表名1 表名2 表名3> 物理磁盘位置.文件名
mysqldump -hlocalhost -uroot -p1234 kuang student1 student2 student3>C:/a.sql

# mysqldump -h 主机 -u 用户 -p 密码 数据库名  > 物理磁盘位置.文件名
mysqldump -hlocalhost -uroot -p1234 kuang >C:/a.sql

# 导入
# 登录情况下，切换到指定的数据库
# source 备份文件
source d:/a.sql

# 没有登录的时候用下面的命令导入
mysql -u用户名 -p密码 库名< 备份文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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

把数据库给朋友！sql文件给别人即可。

九、数据库的归约，三大范式

9.1、为什么需要设计？

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

糟糕的数据库设计：

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

良好的数据库设计：

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

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

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

数据库里面不区分大小写，一般不用驼峰命名，用下划线_区分： category_name

以 个人博客 举例

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

• 收集信息，分析需求
  • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
  • 分类表（文章分类，谁创建的）
  • 文章表（文章的信息）
  • 评论表
  • 友链表（友链信息）
• 标识实体（把需求落地到每个字段）
• 标识实体 之间 的关系
  • 写博客：user -> blog
  • 创建分类：user -> category
  • 关注：user -> user
  • 友链：links
  • 评论：user -> user -> blog

9.2、三大范式

为什么需要数据规范化？

• 信息重复
• 更新异常
• 插入异常
  • 无法正常显示信息
• 删除异常
  • 丢失有效信息

三大范式（了解）

第一范式（1NF）

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

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事

第三范式（3NF）

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

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

（规范数据库的设计）

规范性 和性能的问题

阿里开发手册：关联查询的表不得超过三张表！

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

十、JDBC（重点）

10.1、数据库驱动

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

之前的

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

10.2、JDBC

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

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

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

有了 JDBC 的

java.sql

javax.sql

还需要导入一个数据库驱动包 mysql-connector-java-5.1.47.jar，可以直接去maven官方仓库下载

10.3、第一个JDBC程序

创建测试数据库

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,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

1、创建一个普通项目

2、导入jar包

3、测试

import java.sql.*;

/**
 * @author: Arbicoral
 * @Description: 第一个 JDBC程序
 */
public class jdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1. 加载驱动
        Class.forName("com.sql.jdbc.Driver");

        // 2.用户信息和 url
        // useUnicode=true&characterEncoding=utf8&userSSL=true 背下即可
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&userSSL=true";
        String userName = "root";
        String password = "1234";

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

        // 6. 释放连接：最后写完的最先关闭，一定要关闭，不然很浪费资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}
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

步骤总结：

1. 加载驱动
2. 获取用户信息和url
3. 连接数据库 DriverManager
4. 创建执行SQL语句的对象 Statement
5. 执行SQL语句，并接收返回的结果
6. 释放连接

DriverManager

Class.forName("com.sql.jdbc.Driver");//固定写法，加载驱动

Connection connection = DriverManager.getConnection(url, userName, password);
// connection 就代表数据库，可以设置数据库自动提交，事务提交，事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();
1
2
3
4
5
6
7

URL

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

// mysql 默认端口：3306
// 可以看成是 https://www.baidu.com/
// 协议//主机地址:端口号/数据库名?参数1&参数2&参数3

// Oracle 默认端口：1521
// jdbc:oracle:thin:@localhost:1521:sid
1
2
3
4
5
6
7
8

Statement 执行 SQL的类 prepareStatement 执行 SQL的类

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

statement.executeQuery();// 查询操作，返回 resultSet
statement.execute();// 执行任何SQL，效率低
statement.executeUpdate();//更新、插入、删除都使用这个，返回一个受影响的行数
1
2
3
4
5

resultSet 查询的结果集：封装了所有的查询结果

获取指定的数据类型

resultSet.getObject();// 在不知道列类型下使用

// 如果知道列的类型就使用指定的类型
resultSet.getInt();
resultSet.getBoolean();
resultSet.getFloat();
resultSet.getDate();
......
1
2
3
4
5
6
7
8

遍历，指针

resultSet.previous();//移动到前一行
resultSet.beforeFirst();//移动到最前面
resultSet.next();//移动到下一个
resultSet.afterLast();//移动到最后
resultSet.absolute(row);//移动到指定行
1
2
3
4
5

释放资源

resultSet.close();
statement.close();
connection.close();// 耗资源！关掉！
1
2
3

10.4、statement对象

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

excuteUpdate

excuteQuery

1、工具类：

1. 先写配置文件：src下建一个 db.properties

   注：没有SSL证书就不需要SSL认证，将 useSSL=true 改为 useSSL=false

driver = "com.mysql.jdbc.Driver"
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=1234
1
2
3
4

2. 加载配置文件：

package com.kuang.lesson02.utils;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @author: Arbicoral
 * @Description: 加载配置文件，db.properties
 */
public class JdbcUtils {

    private static String driver = "com.mysql.jdbc.Driver";
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);// 加载流

            properties.getProperty("driver");
            properties.getProperty("url");
            properties.getProperty("username");
            properties.getProperty("password");

            //1.驱动只用加载一次
            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();
            }
        }
    }
}
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.kuang.lesson02;

import com.kuang.lesson02.utils.JdbcUtils;

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

/**
 * @author: Arbicoral
 * @Description: 测试自己新写的工具类，测试插入一条语句
 */
public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        ResultSet rs = null;
        Statement st = null;

        try {
            conn = JdbcUtils.getConnection();// 获取数据库连接
            st = conn.createStatement();// 获取SQL执行的对象
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(4,'kuangshen','123456','123@qq.com','2021-01-01')";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功!");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            // 必须执行的，
            // 释放资源
            JdbcUtils.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

3、查

package com.kuang.lesson02;

import com.kuang.lesson02.utils.JdbcUtils;

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

/**
 * @author: Arbicoral
 * @Description: 测试工具类查询语句
 */
public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();

            String sql = "select * from users where id=4";
            rs = st.executeQuery(sql);// 推荐使用这个

            if (rs.next()){
                rs.getString("NAME");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.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

SQL注入的问题

本质：SQL存在漏洞，会被攻击导致数据泄露，SQL会拼接 or

package com.kuang.lesson02;

import com.kuang.lesson02.utils.JdbcUtils;

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

/**
 * @author: Arbicoral
 * @Description: SQL注入存在漏洞,当用于输入用户名和密码进行登录时，可以不需要知道用户名和密码就能盗取用户的全部信息
 */
public class SQLInject {
    public static void main(String[] args) {
         login("zhansan", "123456");// 正常写法
//        login(" 'or '1=1", " 'or '1=1");// 技巧，盗取全部用户信息
    }
    public static void login(String username, String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();

//             String sql = "select * from users where `NAME`='' or '1=1' and `password`='' or '1=1'";// SQL注入
            String sql = "select * from users where `NAME`='"+ username +"'  AND `PASSWORD`='"+ password +"'" ;// 正常写法
            rs = st.executeQuery(sql);

            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
                System.out.println("==============================");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.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

10.5、PrepareStatement对象

PrepareStatement 可以防止SQL注入，并且效率更好！

1、增加

package com.kuang.lesson02.lesson03;

import com.kuang.lesson02.utils.JdbcUtils;

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

/**
 * @author: Arbicoral
 * @Description: 使用 PrepareStatement对象 完成插入操作数据库
 */
public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();

            // 区别
            // 使用 ? 占位符代替参数
            String sql = "insert into users(id,NAME,PASSWORD,email,birthday) values (?,?,?,?,?)";

            // PreparedStatement 防止SQL注入的本质，把传递进来的参数当作字符
            // 假设其中存在转义字符，比如说  ' 会被直接转义
            st = conn.prepareStatement(sql); // 预编译sql，先写sql，然后不执行

            // 手动给参数赋值
            st.setInt(1,4);
            st.setString(2,"ou");
            st.setString(3,"123456");
            st.setString(4,"1234@qq.com");
            // 注意点：sql.Date ->数据库    java.sql.Date()
            //       util.Date ->Java    new java.util.Date().getTime() 获得时间戳
            st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));

            // 执行SQL
            int i = st.executeUpdate();
            if (i > 0){
                System.out.println("插入成功!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}
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

2、删除

package com.kuang.lesson02.lesson03;

import com.kuang.lesson02.utils.JdbcUtils;

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

/**
 * @author: Arbicoral
 * @Description: 使用 PrepareStatement对象 完成插入操作数据库
 */
public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();

            // 区别
            // 使用 ? 占位符代替参数
            String sql = "delete  from users where id = ?";

            st = conn.prepareStatement(sql); // 预编译sql，先写sql，然后不执行

            // 手动给参数赋值
            st.setInt(1,4);

            // 执行SQL
            int i = st.executeUpdate();
            if (i > 0){
                System.out.println("删除成功!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

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

3、修改

package com.kuang.lesson02.lesson03;

import com.kuang.lesson02.utils.JdbcUtils;

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

/**
 * @author: Arbicoral
 * @Description: 使用 PrepareStatement对象 完成更新数据库操作
 */
public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();

            // 区别
            // 使用 ? 占位符代替参数
            String sql = "update users set name= ? where id=?";

            st = conn.prepareStatement(sql); // 预编译sql，先写sql，然后不执行

            // 手动给参数赋值
            st.setString(1,"狂神");
            st.setInt(2,1);

            // 执行SQL
            int i = st.executeUpdate();
            if (i > 0){
                System.out.println("更新成功!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

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

4、查找

package com.kuang.lesson02.lesson03;

import com.kuang.lesson02.utils.JdbcUtils;

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

/**
 * @author: Arbicoral
 * @Description: 使用 PrepareStatement对象 完成查询数据库操作
 */
public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();

            // 区别
            // 使用 ? 占位符代替参数
            String sql = "select * from users where id=?";

            st = conn.prepareStatement(sql); // 预编译sql，先写sql，然后不执行

            // 手动给参数赋值
            st.setInt(1,1);

            // 执行SQL
            rs = st.executeQuery();
            if (rs.next()){
                rs.getString("NAME");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.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

5、SQL注入

PreparedStatement 防止SQL注入的本质，把传递进来的参数当作字符
假设其中存在转义字符，比如说 ’ 会被直接转义

10.6、使用IDEA连接数据库

需要先下载好 mysql连接Java的驱动，mysql-connector-java-5.1.47.jar，可以在MySQL官网下载，也可以在pom.xml中配置。

连接好之后还可以在IDEA中查看表，写SQL语句等等，功能强大~

10.7、事务

要么都成功，要么都失败

ACID原则

原子性：要么全部完成，要么都不完成

一致性：总数不变

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

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

隔离性会产生的问题：

脏读：一个事务读取了另一个事务未提交的事务

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

幻读/虚读：一个事务读取了别人插入的数据，导致前后读出来不一致

代码实现

1、开启事务：conn.setAutoCommit(false);

2、一组业务执行完毕，提交事务

3、可以在catch 语句中显式的定义 回滚语句，单默认失败就会回滚

package com.kuang.lesson02.Transaction;

import com.kuang.lesson02.utils.JdbcUtils;

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

/**
 * @author: Arbicoral
 * @Description: 测试转账
 */
public class Demo2 {
    public static void main(String[] args) {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.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) {
            // 找错误你就需要让它打印错误
            e.printStackTrace();
            try {
                conn.rollback();// 事务回滚
            } catch (SQLException ex) {
                // 这里打印的是新错误
                e.printStackTrace();
            }
            // 这里放到后面不会执行到，就中断了
//            e.printStackTrace();
        }finally {
            // 释放资源
            JdbcUtils.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

10.8、数据库连接池

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

连接–释放 的过程非常浪费系统资源

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

最小连接数：10

最大连接数：15

等待超时：100ms

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

开源数据源实现（拿来即用）

① DBCP：需要导入的包

        <dependency>
            <groupId>commons-dbcpgroupId>
            <artifactId>commons-dbcpartifactId>
            <version>1.4version>
        dependency>


        <dependency>
            <groupId>commons-poolgroupId>
            <artifactId>commons-poolartifactId>
            <version>1.6version>
        dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13

② C3P0：需要导入的包

<dependency>
    <groupId>com.mchangegroupId>
    <artifactId>c3p0artifactId>
    <version>0.9.5.5version>
dependency>


<dependency>
    <groupId>com.mchangegroupId>
    <artifactId>mchange-commons-javaartifactId>
    <version>0.2.19version>
dependency>

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

③ Druid：阿里巴巴

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

DBCP

    try {
            conn = JdbcUtils.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) {
            // 找错误你就需要让它打印错误
            e.printStackTrace();
            try {
                conn.rollback();// 事务回滚
            } catch (SQLException ex) {
                // 这里打印的是新错误
                e.printStackTrace();
            }
            // 这里放到后面不会执行到，就中断了
//            e.printStackTrace();
        }finally {
            // 释放资源
            JdbcUtils.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

结论：无论使用什么数据源，本质都是一样的，DataSource接口不会变，方法就不会变