MySQL 学习

1. 数据库的分类

1.1 关系型数据库：

MySQL、Oracle等
通过表与表、行与列的关系进行存储数据。

1.2 非关系型数据库：

Radis等
通过存储对象来存储数据，数据由对象的属性决定。

2. 操作数据库

操作数据库 —> 操作数据库中的表 —> 操作数据库中表的数据

2.1 操作数据库

创建数据库

creat database [if not exists] testdatabase;

删除数据库

drop database [if exists] testdatabase;

使用数据库

-- 如果表名或字段名是特殊字符，则需要带上``
use `testdatabase`;

查看数据库
- ```
show databases;
```

2.2 创建数据库的表

create table if not exists `test_table01`(
	`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) not null default '男' comment '性别',
	`birthday` datetime default null comment '出生日期',
	`address` varchar(100) default null comment '家庭住址',
	`email` varchar(50) default null comment '邮箱',
	primary key(id)
)engine=innodb default charset=utf8

2.3 数据表的类型

/*
INNODB 默认使用
MYISAM 早些年使用
*/

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为2倍

各自的优点：

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

2.4 修改删除表

修改表

-- 修改表的各种操作
-- 1.修改表的表名    			 	公式： alter table 旧表名 rename as 新表名;
alter table `test_table01` rename as `test_table`;

-- 2.增加表的字段	   			 	公式： alter table 表名 add 字段名 数据类型 [默认 注释];
alter table `test_table` add age02 int(2) default 18 comment '年龄';

-- 3.修改表的字段   		     	公式： alter table 表名 modify 旧字段名 新数据类型;
--   MODIFY只能改数据类型和约束；	 公式： alter table 表名 change 旧字段名 新字段名 新数据类型;
--   CHANGE可以重命名以及数据类型和约束,但必须重命名后才能改数据类型和约束。
alter table `test_table` MODIFY age VARCHAR(2);
alter table `test_table` CHANGE age02 age int(2);
alter table `test_table` CHANGE age age01 VARCHAR(2);

-- 4.删除表的字段					公式： alter table 表名 drop 旧字段名;
alter table `test_table` DROP age01

删除表

-- 删除表的操作
-- 公式： drop table [if exists] `表名`;
drop table if exists `test_table`;

3. MySQL 数据管理

3.1 外键

MySQL可以在创建表时或者修改表时添加物理外键，数据库级别的外键，但不建议使用（避免数据库国多造成困扰）。

最佳实践

数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）。
当想要使用多张表的数据，使用外键时，通过程序来实现外键。

3.2 DML 语言（全部记住）

插入（insert）

-- 插入字段操作
-- 公式：insert into `表名` (`字段一`,`字段二`,....) values ('数据一','数据二',....);
insert into `test_table` (`name`,`pwd`) values ('小王','123654');

-- 插入多条字段
insert into `test_table` (`name`,`pwd`) values ('小红','789654'),('小白','132146');

修改（update）

-- 修改字段操作
-- 公式：update `表名` set `字段名一` = '新的值'[, `字段名二` = '新的值',...] where [条件];
update `test_table` set `name` = '老王' where `name` = '老王';

-- 修改多个字段
update `test_table` set `name` = '老王',`pwd` = '987465' where `name` = '小王';

注意：

合理运用条件中的 =，!=，>，<，>=，<=，between..and..，and，or
当没有设置条件时，将修改所有数据

删除（delete）

-- 删除字段操作
-- 公式：1.delete from `表名` where [条件]
-- 		2.truncate table `表名`
delete from `test_table` where `name` = '小白';
truncate table `test_table`;

delete 和 truncate 的区别：

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

了解即可：delete之后，重启数据库，不同表引擎的区别：

INNODB：自增会从1开始（存在内存中，断电即失）
MYISAM：继续从上一个增量开始（存在文件中，不会丢失）

4. DQL 查询数据（最重要⭐）

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}];
												-- 指定查询的记录从哪条到哪条

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

4.1 指定查询字段

-- 查询全部的学生
-- 公式：select 字段 from `表名`;
SELECT * FROM `student`;

-- 查询指定字段
select `studentno`, `studentname` from `student`;

-- as用来起别名，字段和表名都可以
select `studentno` as 学号, `studentname` as 姓名 from `student`;

-- 拼接函数concat(a,b)
select CONCAT('学号：',`studentno`,'，姓名：',`studentname`) as 信息 from `student`;

去重（distinct）

-- 查询哪些学生参加了考试
select * from `result`;						-- 查询全部成绩
select `studentno` from `result`;			-- 查询有哪些学生参加考试
-- 数据有重复，需要去重
select distinct `studentno` from `result`;

数据库的列（表达式）

-- 学员成绩+1分
select `studentno` as 学号, `studentresult` + 1  As 新成绩 from `result`;

4.2 Where 条件子句

-- ========================================== where子句 ==============================================
select `studentno` , `studentresult` from `result`;

-- 查询成绩在60-100的学生
select `studentno`, `studentresult` from `result`
where `studentresult` >= 60 and `studentresult` <= 100;

-- &&表达式
select `studentno`, `studentresult` from `result`
where `studentresult` >= 60 && `studentresult` <= 100;

-- between...and 表达式
select `studentno`, `studentresult` from `result`
where `studentresult` between 60 and 100;

-- not 表达式
select `studentno`, `studentresult` from `result`
where not `studentresult` > 60

模糊查询（比较运算符）

-- %代表0-任意个字符，_代表一个字符，只能用于like中
-- 查询所有姓张的学生
select * from `student`
where `studentname` like '张%';

-- 查询姓张的两个字的学生
select * from `student`
where `studentname` like '张_';

-- 查询姓张的三个字的学生
select * from `student`
where `studentname` like '张__';

-- 查询名字里带张的学生
select * from `student`
where `studentname` like '%张%';

-- 查询名字中间带张的学生
select * from `student`
where `studentname` like '_%张%';

-- ============================== in =============================================
-- 查询学号在1000，1001，1002的学生
select * from `student`
where `studentno` in(1000,1001,1002);

-- 查询地址在北京朝阳、广东深圳的学生
select * from `student`
where `address` in('北京朝阳','广东深圳');

-- ============================== null / not null =============================================
-- 查询电话号码为空的学生
select * from `student`
where `phone` = '';

-- 查询电话号码不为空的学生
select * from `student`
where `phone` is not null;

4.3 联表查询

join对比

-- ========================================= 联表查询 =============================================
/* 思路：
1. 分析需求，分析查询的字段来自哪些表
2. 确定使用哪种连接查询？7种
3. 确定交叉点（这两个表哪些数据是相同的）
*/
-- 查询参加了考试的学生（学号、姓名、科目编号、分数）
select * from `student`;
select * from `result`;

-- inner join
select s.studentno,studentname,subjectno,studentresult
from result as r
inner join student as s
on s.studentno = r.studentno;

-- right join  结果多了没参加考试的学生
select s.studentno,studentname,subjectno,studentresult
from result as r
right join student as s
on s.studentno = r.studentno;

-- right join 把左右两边的表调换试试，结果只有参加了考试的学生，因此，left左边的表都展示，right右边的表都展示
select s.studentno,studentname,subjectno,studentresult
from student as s
right join result as r
on s.studentno = r.studentno;

-- left join   结果和inner join一样，都是参加了考试的学生
select s.studentno,studentname,subjectno,studentresult
from result as r
left join student as s
on s.studentno = r.studentno;

-- left join 把左右两边的表调换试试，结果多了没参加考试的学生，因此，left左边的表都展示，right右边的表都展示
select s.studentno,studentname,subjectno,studentresult
from student as s
left join result as r
on s.studentno = r.studentno;

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

-- 查询学生所属的年纪（学号、姓名、年纪名称）
select studentno,studentname,gradename
from student as s
left join grade as g
on s.gradeid = g.gradeid;

-- 查询科目所属的年纪（科目名称，年纪名称）
select subjectname,gradename
from grade as g
inner join subject as s 
on g.gradeid = s.gradeid;

-- 思考题：查询参加考试同学的信息：学号、姓名、科目名、分数
select s.studentno,studentname,subjectname,studentresult
from result as r
left join student as s 
on s.studentno = r.studentno
left join `subject` as k
on r.subjectno = k.subjectno

-- 思考题：查询参加 数据库结构-1 考试同学的信息：学号、姓名、科目名、分数
select s.studentno,studentname,subjectname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
inner join `subject` as sub
on sub.subjectno = r.subjectno
where sub.subjectname = '数据库结构-1';

操作	描述
inner join	如果两个表种至少有一个匹配，就返回行
left join	会把left左边的表作为主表，返回左表所有的值，即使右表中没有匹配
right join	会把right右边的表作为主表，返回右表所有的值，即使左表中没有匹配

自连接

-- ============================== 自连接 =============================================
-- 查询父子信息
select f.categoryName as '父栏目',z.categoryName as '子栏目'
from category as f, category as z
where f.categoryid = z.pid;

4.4 分页和排序

排序（order by）

-- 排序的公式： order by 通过哪个字段排序  怎么排（asc升序，desc降序）
-- 查询的结果根据成绩排序
select s.studentno,studentname,subjectname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
inner join `subject` as sub
on sub.subjectno = r.subjectno
where sub.subjectname = '数据库结构-1'
order by studentresult asc;

分页（limit）

-- 分页的公式： limit 起始值，页面大小
-- 第一页： limit 0，5					 （1-1）*5
-- 第二页： limit 5，5					 （2-1）*5
-- 第三页： limit 10，5					 （3-1）*5
-- 第四页： limit 15，5					 （4-1）*5
-- 第N页：  limit （n-1）*5，5	 （n-1）*pagesize,pagesize
-- [pagesize:页面大小]
-- [（n-1）*pagesize:起始值]
-- [n:当前页]
-- [数据总数 / 页面大小 = 总页数]

-- 对排序的结果进行分页
select s.studentno,studentname,subjectname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
inner join `subject` as sub
on sub.subjectno = r.subjectno
order by studentresult asc
limit 0,5;

-- 查询 java第一学年 课程成绩排名前十的学生，并且分数要大于80的学生信息（学号、姓名、课程名称、分数）
select s.studentno,studentname,subjectname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
inner join `subject` as sub
on sub.subjectno = r.subjectno
where r.studentresult > 80 and sub.subjectname = 'Java程序设计-1'
order by r.studentresult desc
limit 0,10;

4.5 子查询

-- =========================================== where子查询 =============================================
-- 1.查询数据库结构-1 的所有考试结果（学号、名字、成绩）降序排序
-- 方式一：连接查询
select s.studentno,studentname,studentresult
from result as r
inner join `student` as s
on r.studentno = s.studentno
inner join `subject` as sub
on r.subjectno = sub.subjectno
where sub.subjectname = '数据库结构-1'
order by studentresult desc

-- 方式二：子查询
select s.studentno,studentname,studentresult
from result as r
inner join student as s
on r.studentno = s.studentno
where subjectno = (
			select subjectno
			from `subject`
			where subjectname = '数据库结构-1'
)
order by studentresult desc

-- 分数不小于80分的学生的学号和姓名
-- 方式一：连接查询
select distinct s.studentno,studentname
from student as s
inner join result as r
on s.studentno = r.studentno
where studentresult >= 80

-- 方式二：子查询
select studentno,studentname
from student
where studentno in (
			select studentno
			from result
			where studentresult >= 80
)

-- 在这个基础上加个科目，高等数学-2
-- 方式一：
select s.studentno,studentname
from student as s
inner join result as r
on s.studentno = r.studentno
inner join `subject` as sub
on sub.subjectno = r.subjectno
where subjectname = '高等数学-2' and studentresult >= 80

-- 方式二：
select studentno,studentname
from student
where studentno in (
			select studentno
			from result	as r
			inner join `subject` as sub
			on r.subjectno = sub.subjectno
			where subjectname = '高等数学-2' and studentresult >= 80
)

-- 方式三：(虽然阅读成本高，但效率要高于联表查询)
select studentno,studentname
from student
where studentno in (
			select studentno
			from result
			where subjectno = (
						select subjectno
						from `subject`
						where subjectname = '高等数学-2'
			) and studentresult >= 80
)

-- 查询c语言-1 前五名学生的信息（学号，姓名，成绩）
-- 方式一： 联表查询
select s.studentno,studentname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
inner join `subject` as sub
on sub.subjectno = r.subjectno
where subjectname = 'C语言-1'
order by studentresult desc
limit 0,5

-- 方式二：子查询
select s.studentno,studentname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
where subjectno = (
			select subjectno
			from `subject`
			where subjectname = 'C语言-1'
)
order by studentresult desc
limit 0,5

4.7 分组和过滤

-- 查询不同课程的平均分，最高分，最低分，并且平均分要高于80
-- 核心：根据不同学科分组
select subjectname,avg(studentresult) as 平均分,max(studentresult) as 最高分,min(studentresult) as 最低分
from result as r
inner join `subject` as sub
on r.subjectno = sub.subjectno
group by r.subjectno
having 平均分 >= 80

5. MySQL函数

5.1 聚合函数

函数	描述
count()	计数
sum()	求和
avg()	平均值
max()	最大值
min()	最小值

-- =========================================== 聚合函数 =============================================
-- count(字段)			会忽略所有的null值
-- count(*)					不会忽略null值，本质是计算行数
-- count(1)					不会忽略null值，本质是计算行数
-- 对于有主键的时候，用count(字段)效率要高于count(1)，否则count(1)效率高，即count(主键列)>count(1)>count(非主键列)
select count(studentname) from student;
select count(*) from student;
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;

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

-- =========================================== 测试MD5加密 =============================================
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','张三','132456'),
('2','李四','564123'),
('3','王五','456784'),
('4','赵六','213456'),
('5','小王','789546')

-- 加密
update `testmd5` set `pwd` = md5(`pwd`)

-- 插入时加密
insert into `testmd5`(`id`,`name`,`pwd`) values('6','小明',md5('123654'))

-- 查询
select * from `testmd5` where `name` = '小明' and `pwd` = md5('123654')

6. 事务⭐

事务原则：ACID原则原子性、一致性、隔离性、持久性

参考链接：(https://blog.csdn.net/dengjili/article/details/82468576/)

原子性（Atomicity）

要么都成功，要么都失败

一致性（Consistency）

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

隔离性（Isolation）

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

持久性（Durability）

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

隔离所导致的一些问题

脏读：

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

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。

执行事务

-- =========================================== 事务 =============================================

-- mysql 默认开启事务自动提交
set autocommit = 0 -- 关闭自动提交

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

insert xx
insert xx

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

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

-- 了解
savepoint 保存点名 -- 设置一个事务的保存点
rollback to savepoint 保存点名 -- 回滚到保存点
release savepoint 保存点名 -- 撤销保存点

模拟场景

-- 转账
create database money character set utf8 collate utf8_general_ci;
use money;

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.00),
('b',1000.00)

-- 模拟转账
set autocommit = 0;		-- 关闭自动提交
start transaction;		-- 开启事务

update `account` set `money` = `money` - 500 where `name` = 'a';		-- a给b转500 
update `account` set `money` = `money` + 500 where `name` = 'b';		-- b收到a的500

commit;			-- 提交，持久化
rollback;		-- 回滚，提交后没法回滚

set autocommit = 1;		-- 开启自动提交

7. 索引

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

7.1 索引的分类

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

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

参考链接：https://blog.csdn.net/jiadajing267/article/details/81269067

基础语法

-- 索引的使用

-- 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'); -- 全文索引

7.2 测试索引

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`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,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

-- 插入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`)
					VALUES(CONCAT('用户',i),'19224305@qq.com',concat('18',floor(rand()*999999999)),
								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` = '用户99999';		-- 0.421sec

explain select * from app_user where `name` = '用户99999'; -- 992742rows

-- id_表名_字段名
-- create index 索引名 on 表名(字段名)
create index id_app_user_name on app_user(`name`);

select * from app_user where `name` = '用户99999';		-- 0.001sec

explain select * from app_user where `name` = '用户99999'; -- 1row

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

7.3 索引原则

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

索引的数据结构

参考链接：CodingLabs - MySQL索引背后的数据结构及算法原理

Hash类型的索引

Btree：innodb的默认数据结构

8. 权限管理和备份

8.1 用户管理

SQL命令操作

用户表：mysql.user

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

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

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


-- 重命名  RENAME 原名子 leez TO 新名字;
RENAME USER leez01 TO leez;


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

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

GRANT ALL PRIVILEGES ON *.* TO leez;

-- 查询权限

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

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

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

-- 删除用户
DROP USER leez;

8.2 MySQL备份

直接拷贝物理文件
在 navicat 这种可视化工具中手动导出
使用命令行导出， mysqldump 命令行使用

# 一张表 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密码 库名<备份文件

9. 规范数据库设计

糟糕的数据库设计

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

良好的数据库设计

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

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

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

三大范式

参考链接：关系型数据库设计：三大范式的通俗理解 - 景寓6号 - 博客园 (cnblogs.com)

第一范式（1NF）

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

第二范式（2NF）

前提：满足第一范式

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

每张表只描述一件事情

第三范式（3NF）

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

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

规范数据库的设计

规范性和性能的问题

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

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

10. JDBC⭐

创建测试数据库

CREATE DATABASE jdbcstudy CHARACTER SET utf8 COLLATE utf8_general_ci;

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

10.1 编写测试代码

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

public class JDBCDemo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.用户信息和URL
        // useSSL=true可能会报错
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String userName = "root";
        String passWord = "";
        //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();
    }
}

步骤总结：

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

DriverManager

//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

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对象

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

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

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

获得指定的数据类型

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

遍历，指针

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

释放资源

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

10.2 statement对象详解

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

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

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

CRUD操作-create

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

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

CRUD操作-delete

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

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

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

代码实现

提取工具类

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

public class JDBCUtils {
    private static String driver = null;
    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);

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

            //驱动只用加载一次
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    //释放资源
    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();
            }
        }
    }
}

配置文件db.properties

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

编写增删改的方法，executeUpdate

import com.zyy.lesson02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestInsert {
    public static void main(String[] args) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            st = con.createStatement();
            String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`)\n" +
                    "VALUES (5,'钱七','123456','qianqi@sina.com','1988-12-04')";
            int num = st.executeUpdate(sql);
            if (num > 0) {
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(con, st, rs);
        }

    }
}

import com.zyy.lesson02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestDelete {
    public static void main(String[] args) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            st = con.createStatement();
            String sql = "DELETE FROM users WHERE `id`=5";
            int num = st.executeUpdate(sql);
            if (num > 0) {
                System.out.println("删除成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(con, st, rs);
        }
    }
}

import com.zyy.lesson02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestUpdate {
    public static void main(String[] args) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            st = con.createStatement();
            String sql = "UPDATE users SET birthday='1990-12-01' WHERE id=1";
            int num = st.executeUpdate(sql);
            if (num > 0) {
                System.out.println("更新成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(con, st, rs);
        }
    }
}

查询

import com.zyy.lesson02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestSelect {
    public static void main(String[] args) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            st = con.createStatement();
            String sql = "SELECT * FROM users WHERE id=1";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                System.out.println("id="+rs.getInt("id"));
                System.out.println("name="+rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(con, st, rs);
        }
    }
}

10.3 SQL注入

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

import com.zyy.lesson02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLQuestion {
    public static void main(String[] args) {
        //正常登录
        //login("张三","1234567");

        //sql注入
        login("' or '1=1","123456");
    }

    public static void login(String userName, String password) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            st = con.createStatement();
            String sql = "SELECT * FROM users WHERE `name`='"+userName+"' AND `password`='"+password+"'";
            // SELECT * FROM users WHERE `name`='' or '1=1' AND `password`='123456'
            System.out.println(sql);
            rs = st.executeQuery(sql);
            while (rs.next()) {
                System.out.println("id="+rs.getInt("id"));
                System.out.println("name="+rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(con, st, rs);
        }
    }
}

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

10.4 preparement对象详解

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

新增

import com.zyy.lesson02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestInsert {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            //使用?占位符代替参数
            String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES (?,?,?,?,?)";
            //预编译SQL，先写SQL，然后不执行
            st = con.prepareStatement(sql);
            //手动给参数赋值
            st.setInt(1, 5);
            st.setString(2, "钱七");
            st.setString(3, "123456");
            st.setString(4, "qianqi@sina.com");
            st.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
            int num = st.executeUpdate();
            if (num > 0) {
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(con, st, rs);
        }
    }
}

删除

import com.zyy.lesson02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

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

更新

import com.zyy.lesson02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestUpdate {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            //使用?占位符代替参数
            String sql = "UPDATE users SET birthday=? WHERE id=?";
            //预编译SQL，先写SQL，然后不执行
            st = con.prepareStatement(sql);
            //手动给参数赋值
            st.setDate(1, new java.sql.Date(new java.util.Date().getTime()));
            st.setInt(2, 1);
            int num = st.executeUpdate();
            if (num > 0) {
                System.out.println("修改成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(con, st, rs);
        }
    }
}

查询

import com.zyy.lesson02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestSelect {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            //使用?占位符代替参数
            String sql = "SELECT * FROM users WHERE id=?";
            //预编译SQL，先写SQL，然后不执行
            st = con.prepareStatement(sql);
            //手动给参数赋值
            st.setInt(1, 1);
            rs = st.executeQuery();
            while (rs.next()) {
                System.out.println("id="+rs.getInt("id"));
                System.out.println("name="+rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(con, st, rs);
        }
    }
}

防止sql注入

import com.zyy.lesson02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SQLQuestion {
    public static void main(String[] args) {
        //正常登录
        //login("张三","123456");

        //sql注入
        login("' or '1=1", "123456");
    }

    public static void login(String userName, String password) {
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            // PreparedStatement 防止SQL注入的本质，把传递进来的参数当做字符
            // 假设其中存在转义字符，比如说'会被直接转义
            String sql = "SELECT * FROM users WHERE `name`=? AND `password`=?";
            st = con.prepareStatement(sql);
            st.setString(1, userName);
            st.setString(2, password);
            rs = st.executeQuery();
            while (rs.next()) {
                System.out.println("id=" + rs.getInt("id"));
                System.out.println("name=" + rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(con, st, rs);
        }
    }
}

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

10.5 事务

要么都成功，要么都失败

ACID原则

原子性：要么全部成功，要么全部失败
一致性：总数不变
隔离性：多个进程互不干扰
持久性：一旦提交不可逆，持久化到数据库了

隔离性的问题：

脏读：一个事务读取了另外一个没有提交的事务
不可重复读：在同一个事务内，重复读取表中数据，表数据发生了改变
幻读：在一个事务内，读取到了别人插入的数据，导致前后读出来的结果不一致

代码实现

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

正常情况

import com.zyy.lesson02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestTransaction1 {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            //关闭自动提交 自动会开启事务
            con.setAutoCommit(false);//开启事务
            // A 转 B 100元
            String sql1 = "update account set money=money-100 where name='A'";
            ps = con.prepareStatement(sql1);
            ps.executeUpdate();
            String sql2 = "update account set money=money+100 where name='B'";
            ps = con.prepareStatement(sql2);
            ps.executeUpdate();
            //业务完毕，提交事务
            con.commit();
            System.out.println("A 转 B 100元 成功！");
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            JDBCUtils.release(con, ps, rs);
        }
    }
}

异常情况

import com.zyy.lesson02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestTransaction2 {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            //关闭自动提交 自动会开启事务
            con.setAutoCommit(false);//开启事务
            // A 转 B 100元
            String sql1 = "update account set money=money-100 where name='A'";
            ps = con.prepareStatement(sql1);
            ps.executeUpdate();
            
            //默认失败
            int x = 1/0; //一定会异常

            String sql2 = "update account set money=money+100 where name='B'";
            ps = con.prepareStatement(sql2);
            ps.executeUpdate();
            //业务完毕，提交事务
            con.commit();
            System.out.println("A 转 B 100元 成功！");
        } catch (SQLException e) {
            e.printStackTrace();
            //如果异常，默认也会回滚，下面不写也可以
//            try {
//                con.rollback();
//            } catch (SQLException ex) {
//                ex.printStackTrace();
//            }
        } finally {
            JDBCUtils.release(con, ps, rs);
        }
    }
}