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.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";

自连接

-- ============================== 自连接 =============================================
-- 查询父子信息
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(字段)			会忽略所有的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();
    }
}

步骤总结：

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

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

代码实现

1. 提取工具类

   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
   

2. 编写增删改的方法，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`)
   " +
                       "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);
           }
       }
   }
   

3. 查询

   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注入，效率更好

1. 新增

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

2. 删除

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

3. 更新

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

4. 查询

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

5. 防止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原则

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

• 一致性：总数不变

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

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

隔离性的问题：

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

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

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

代码实现

1. 开启事务con.setAutoCommit(false);
2. 一组业务执行完毕，提交事务
3. 可以在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);
        }
    }
}