MYSQL

整个MySQL的学习非常零散，因为mysql其实是我在大二基于数据库设计这门课所学到的知识，简单的SQL而已，甚至后来频繁使用mybatis+mybatisplus框架后，连SQL都记不太清了，被面试官打了好几次！！！决定回来深入了解一下MySQL，了解途径分为：狂神说mysql基础课，尚硅谷周阳mysql进阶，编程不良人的mysql索引https://www.bilibili.com/video/BV19y4y127h4?p=3&spm_id_from=pageDriver等视频课，以及万能的百度

重点为：mysql索引：b+数

1、MYSQL

mysql是现在主流的数据库，介于自己对于mysql的学习不够深入与遗忘，（经常使用框架的后遗症），所以准备把MYSQL从头到脚的回顾一遍，细细细！

2、操作数据库

数据库操作：

创建数据库 : create database [if not exists] 数据库名;
删除数据库 : drop database [if exists] 数据库名;
查看数据库 : show databases;
使用数据库 : use 数据库名;

数据类型

数值类型

字符串类型

日期和时间型数值类型

NULL值

理解为 "没有值" 或 "未知值"
不要用NULL进行算术运算 , 结果仍为NULL

数据字段属性

UnSigned
无符号的
声明该数据列不允许负数 .
ZEROFILL
0填充的
不足位数的用0来填充 , 如int(3),5则为005

Auto_InCrement

• 自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)
• 通常用于设置主键 , 且为整数类型
• 可定义起始值和步长
  • 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
  • SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)

NULL 和 NOT NULL
默认为NULL , 即没有插入该列的数值
如果设置为NOT NULL , 则该列必须有值

DEFAULT
默认的
用于设置默认值
例如,性别字段,默认为"男" , 否则为 "女" ; 若无指定该列的值 , 则默认值为"男"的值

-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住
址,email
-- 创建表之前 , 一定要先选择数据库
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) 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
-- 查看数据库的定义
SHOW CREATE DATABASE school;
-- 查看数据表的定义
SHOW CREATE TABLE student;
-- 显示表结构
DESC student; -- 设置严格检查模式(不能容错了)SET
sql_mode='STRICT_TRANS_TABLES';

修改删除表

alter table teacher rename as teacher1;
alter table teacher add age int(11);

alter table teacher1 modify age varchar(11);
alter table teacher1 change age age1 int(11);

alter table teacher1

MySQL的数据表的类型 : MyISAM , InnoDB ,

常见的 MyISAM 与 InnoDB 类型：

经验 ( 适用场合 ) :
适用 MyISAM : 节约空间及相应速度
适用 InnoDB : 安全性 , 事务处理及多用户操作数据表

数据表的存储位置

MySQL数据表以文件方式存放在磁盘中

• 包括表文件 , 数据文件 , 以及数据库的选项文件

• 位置 : Mysql安装目录\data\下存放数据表 . 目录名对应数据库名 , 该目录下文件名对应数据表

注意 :
InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件
MyISAM类型数据表对应三个文件 :

• .frm -- 表结构定义文件
• .MYD -- 数据文件 ( data )
• .MYI -- 索引文件 ( index )

设置数据表字符集

创建时通过命令来设置 , 如 : CREATE TABLE 表名()CHARSET = utf8;
如无设定 , 则根据MySQL数据库配置文件 my.ini 中的参数设定

3、MySQL数据管理

3.1、外键（不用）

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，
外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的
表被称为主表的从表。
在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包
括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。
外键作用
保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。 使两张表形成关联，外键只能
引用外表中的列的值或使用空值。

-- 创建外键的方式一 : 创建子表同时创建外键
-- 年级表 (id\年级名称)
CREATE TABLE `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)
CREATE TABLE `student` (
`studentno` INT(4) NOT NULL COMMENT '学号',
`studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` TINYINT(1) DEFAULT '1' COMMENT '性别',
`gradeid` INT(10) DEFAULT NULL COMMENT '年级',
`phoneNum` VARCHAR(50) NOT NULL COMMENT '手机',
`address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
`borndate` DATETIME DEFAULT NULL COMMENT '生日',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`
(`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建外键方式二 : 创建子表完毕后,修改子表添加外键
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`
(`gradeid`);

最佳实践

数据库就是单纯的表，代码层面实现

阿里巴巴JAVA规范

【不得使用外键与级联，一切外键概念必须在应用层解决】

原因：

每次Delete或者Update都必须考虑外键约束，会导致开发的时候很痛苦，测试数据极为不方便

3.2、DML语言

• INSERT (添加数据语句)
• UPDATE (更新数据语句)
• DELETE (删除数据语句)

3.3、INSERT增

-- 使用语句如何增加语句?
-- 语法 : INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')
INSERT INTO grade(gradename) VALUES ('大一');
-- 主键自增,那能否省略呢?
INSERT INTO grade VALUES ('大二');
-- 查询:INSERT INTO grade VALUE ('大二')错误代码： 1136
Column count doesn`t match value count at row 1
-- 结论:'字段1,字段2...'该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一
致.
-- 一次插入多条数据
INSERT INTO grade(gradename) VALUES ('大三'),('大四');

3.4、UPDATE改

UPDATE grade SET gradename = '高中' WHERE gradeid = 1;

where运算符

3.5、DELETE删

DELETE FROM grade WHERE gradeid = 5;

TRUNCATE命令

-- 清空年级表
TRUNCATE grade

相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
不同 :
使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器
使用TRUNCATE TABLE不会对事务有影响 （事务后面会说）

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`;

-- 同样使用DELETE清空不同引擎的数据库表数据.重启数据库服务后
-- InnoDB : 自增列从初始值重新开始 (因为是存储在内存中,断电即失)
-- MyISAM : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)

4、使用DQL查询数据

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

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

总结：

select 去重 要查询的字段 from 表 (别名)
inner join 表 on 等值判断
where (值,子查询语句)
group by (通过哪个字段来分组)
having (过滤分组后的信息，条件和where一样)
order by (排序) desc
limit 开始,页面大小

创建数据表

-- 创建学生表
drop table if exists `student`;
create table `student`(
    `studentno` int(4) not null comment '学号',
    `loginpwd` varchar(20) default null,
    `studentname` varchar(20) default null comment '学生姓名',
    `sex` tinyint(1) default null comment '性别，0或1',
    `gradeid` int(11) default null comment '年级编号',
    `phone` varchar(50) not null comment '联系电话，允许为空',
    `address` varchar(255) not null comment '地址，允许为空',
    `borndate` datetime default null comment '出生时间',
    `email` varchar (50) not null comment '邮箱账号允许为空',
    `identitycard` varchar(18) default null comment '身份证号',
    primary key (`studentno`),
    unique key `identitycard`(`identitycard`),
    key `email` (`email`)
)engine=myisam default charset=utf8;

-- 创建年级表
drop table if exists `grade`;
create table `grade`(
	`gradeid` int(11) not null auto_increment comment '年级编号',
  `gradename` varchar(50) not null comment '年级名称',
    primary key (`gradeid`)
) engine=innodb auto_increment = 6 default charset = utf8;

-- 创建科目表
drop table if exists `subject`;
create table `subject`(
	`subjectno`int(11) not null auto_increment comment '课程编号',
    `subjectname` varchar(50) default null comment '课程名称',
    `classhour` int(4) default null comment '学时',
    `gradeid` int(4) default null comment '年级编号',
    primary key (`subjectno`)
)engine = innodb auto_increment = 19 default charset = utf8;

-- 创建成绩表
drop table if exists `result`;
create table `result`(
	`studentno` int(4) not null comment '学号',
    `subjectno` int(4) not null comment '课程编号',
    `examdate` datetime not null comment '考试日期',
    `studentresult` int (4) not null comment '考试成绩',
    key `subjectno` (`subjectno`)
)engine = innodb default charset = utf8;

插入数据

-- 插入学生数据 其余自行添加 这里只添加了2行
insert into `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
values
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');

-- 插入成绩数据  这里仅插入了一组，其余自行添加
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

-- 插入年级数据
insert into `grade` (`gradeid`,`gradename`) values(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

-- 插入科目数据
insert into `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);

4.1、指定查询字段

-- 查询所有学生信息
SELECT * FROM student;
-- 查询指定列(学号 , 姓名)
SELECT studentno,studentname FROM student;

AS 子句作为别名

-- 这里是为列取别名(当然as关键词可以省略)
SELECT studentno AS 学号,studentname AS 姓名 FROM student;
-- 使用as也可以为表取别名
SELECT studentno AS 学号,studentname AS 姓名 FROM student AS s;
-- 使用as,为查询结果取一个新名字
-- CONCAT()函数拼接字符串
SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;

DISTINCT关键字的使用

作用 : 去掉SELECT查询返回的记录结果中重复的记录 ( 返回所有列的值都相同 ) , 只返回一条

-- # 查看哪些同学参加了考试(学号) 去除重复项
SELECT * FROM result; -- 查看考试成绩
SELECT studentno FROM result; -- 查看哪些同学参加了考试
SELECT DISTINCT studentno FROM result; -- 了解:DISTINCT 去除重复项 , (默认是ALL)

使用表达式的列

-- selcet查询中可以使用表达式
SELECT @@auto_increment_increment; -- 查询自增步长
SELECT VERSION(); -- 查询版本号
SELECT 100*3-1 AS 计算结果; -- 表达式
-- 学员考试成绩集体提分一分查看
SELECT studentno,StudentResult+1 AS '提分后' FROM result;

4.2、where条件语句

where条件不能包含聚合函数

逻辑操作符

-- 满足条件的查询(where)
SELECT Studentno,StudentResult FROM result;

-- 查询考试成绩在95-100之间的
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 AND StudentResult<=100;

-- AND也可以写成 &&
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 && StudentResult<=100;

-- 区间查询(对应的词:精确查询)
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult BETWEEN 95 AND 100;

-- 除了1000号同学,要其他同学的成绩
SELECT studentno,studentresult
FROM result
WHERE studentno!=1000;

-- 使用NOT
SELECT studentno,studentresult
FROM result
WHERE NOT studentno=1000;

4.3、模糊查询（比较操作符）

-- 模糊查询 between and \ like \ in \ null
-- =============================================
-- LIKE
-- =============================================

-- 查询姓刘的同学的学号及姓名
-- like结合使用的通配符 : % (代表0到任意个字符) _ (一个字符)
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘%';

-- 查询姓刘的同学,后面只有一个字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘_';

-- 查询姓刘的同学,后面只有两个字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘__';

-- 查询姓名中含有 嘉 字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%嘉%';


-- 查询姓名中含有特殊字符的需要使用转义符号 '\'
-- 自定义转义符关键字: ESCAPE ':'
-- =============================================
-- IN
-- =============================================

-- 查询学号为1000,1001,1002的学生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);

-- 查询地址在北京,南京,河南洛阳的学生
SELECT studentno,studentname,address FROM student
WHERE address IN ('北京','南京','河南洛阳');

-- =============================================
-- NULL 空
-- =============================================

-- 查询出生日期没有填写的同学
-- 不能直接写=NULL , 这是代表错误的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL

-- 查询出生日期填写的同学
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;

-- 查询没有写家庭住址的同学(空字符串不等于null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;

4.4、联表查询(左连接右连接)

内连接、左连接、右连接

/*
连接查询join on
如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
内连接 inner join
	查询两个表中的结果集中的交集
外连接 outer join
	左外连接 left join
		(以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
	右外连接 right join
		(以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
等值连接where ==和非等值连接not
自连接
*/

-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;

/*思路:
(1):分析需求,确定查询的列来源于两个类,student result,连接查询
(2):确定使用哪种连接查询?(内连接)
*/
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno

-- 右连接(也可实现)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno

-- 等值连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno

-- 左连接 (查询了所有同学,不考试的也会查出来)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno

-- 查一下缺考的同学(左连接应用场景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
select s.studentno,s.studentname,r.subjectno,subjectname,r.studentresult
from student s
inner join result r
on s.studentno = r.studentno
inner join `subject` sub
on sub.subjectno = r.subjectno;

-- 总结：从几个表中查，一步一步来，两个表开始

自连接

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

数据库

CREATE TABLE `category`(
`categoryid` INT(3) NOT NULL COMMENT 'id',
`pid` INT(3) NOT NULL COMMENT '父id 没有父则为1',
`categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字',
PRIMARY KEY (`categoryid`) 
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 

INSERT INTO `category` (`categoryid`, `pid`, `categoryname`) VALUES (2, 1, '信息技术');
insert into `CATEGOrY` (`categoryid`, `pid`, `categoryname`) values (3, 1, '软件开发');
insert into `category` (`categoryid`, `PId`, `categoryname`) values (5, 1, '美术设计');
insert iNTO `category` (`categoryid`, `pid`, `categorynamE`) VAlUES (4, 3, '数据库'); 
insert into `category` (`CATEgoryid`, `pid`, `categoryname`) values (8, 2, '办公信息');
insert into `category` (`categoryid`, `pid`, `CAtegoryname`) values (6, 3, 'web开发'); 
inserT INTO `category` (`categoryid`, `pid`, `categoryname`) valueS (7, 5, 'ps技术');

父类

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

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

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- 查询父类对应的子类关系，就是自连接查询categoryid=pid
select a.categoryname as 'a', b.categoryname as 'b'
from category as a, category as b
where a.categoryid = b.pid

联表查询练习

-- 查询学员及所属的年级(学号,学生姓名,年级名)
SELECT studentno AS 学号,studentname AS '学生姓名',gradename AS '年级名称'
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`

-- 查询科目及所属的年级(科目名称,年级名称)
SELECT subjectname AS '科目名称',gradename AS '年级名称'
FROM SUBJECT sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid

-- 查询 高等数学-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='高等数学-1'

4.5、排序和分页

排序order by

/*============== 排序 ================
	语法 : ORDER BY
	ORDER BY 语句用于根据指定的列对结果集进行排序。
	ORDER BY 语句默认按照ASC升序对记录进行排序。
	如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。
*/
-- 查询 所有考试结果(学号 学生姓名 科目名称 成绩)
-- 按成绩降序排序
select * from result order by studentresult desc

分页limit by 起始页,页面大小

/*============== 分页 ================
	语法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
	好处 : (用户体验,网络传输,查询压力)
	推导:
	第一页 : limit 0,5
	第二页 : limit 5,5
	第三页 : limit 10,5
	......
	第N页 : limit (pageNo-1)*pageSzie,pageSzie
	[pageNo:页码,pageSize:单页面显示条数]
*/

-- 每页显示2条数据
select * 
from result 
order by studentresult desc
limit 0,2

4.6、子查询

where(这个值是计算出来的)

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

where (select * from )

-- 查询成绩>60的考试结果（学号，姓名，科目名称，成绩）,降序排列
-- 方式一：使用连接查询
select r.studentno,s.studentname,sub.subjectname,r.studentresult
from result r
inner join student s
on r.studentno=s.studentno
inner join `subject` sub
on r.subjectno = sub.subjectno
where studentresult>60
order by studentresult desc

/*============== 子查询 ================
	什么是子查询?
	在查询语句中的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;

-- 分数不小于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'
	)
)

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是从数据表中的>字段直接进行的筛选的
*/

5、MySQL函数

官方文档 : https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

5.1、常用函数

数据函数

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

字符串函数

SELECT CHAR_LENGTH('狂神说坚持就能成功'); -- 返回字符串包含的字符数
SELECT CONCAT('我','爱','程序'); -- 合并字符串,参数可以有多个
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱'); -- 替换字符串,从某个位置开始替换某个长度 "超级热爱编程helloworld"
SELECT LOWER('KuangShen'); -- 小写
SELECT UPPER('KuangShen'); -- 大写
SELECT LEFT('hello,world',5); -- 从左边截取 hello
SELECT RIGHT('hello,world',5); -- 从右边截取 word
SELECT REPLACE('狂神说坚持就能成功','坚持','努力'); -- 替换字符串 "狂神说努力就能成功"
SELECT SUBSTR('狂神说坚持就能成功',4,6); -- 截取字符串,开始和长度 "坚持就能成功"
SELECT REVERSE('狂神说坚持就能成功'); -- 反转 "功成能就持坚说神狂"

时间和日期函数

SELECT CURRENT_DATE(); -- 获取当前日期 2021-09-20
SELECT CURDATE(); -- 获取当前日期 2021-09-20
SELECT NOW(); -- 获取当前日期和时间 2021-09-20 09:23:32
SELECT LOCALTIME(); -- 获取当前日期和时间 2021-09-20 09:23:38
SELECT SYSDATE(); -- 获取当前日期和时间 2021-09-20 09:23:45
-- 获取年月日,时分秒
SELECT YEAR(NOW()); -- 2021
SELECT MONTH(NOW()); -- 9 
SELECT DAY(NOW()); -- 20
SELECT HOUR(NOW()); -- 9
SELECT MINUTE(NOW()); -- 24
SELECT SECOND(NOW()); -- 29

系统信息

SELECT VERSION(); -- mysql版本 5.7.27
SELECT USER(); -- mysql用户 root

5.2、聚合函数

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

-- 统计表中数据个数 count
-- 此时studen共有4个数据，其中存在一个studentname为null的数据
select count(studentname) from student -- 3 会忽略null值，只包含这一列，会忽略null值
select count(*) from student -- 4 不会忽略null，包括了所有列，相当于行数
select count(1) from student -- 4 不会忽略null，忽略所有列，用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;

5.3、MD5 加密

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 values(1,'zhangsan','123456');
insert into testmd5 values(2,'lisi','123456');
insert into testmd5 values(3,'wangwu','123456');
insert into testmd5 values(4,'ydd','123456');

-- 加密
update testmd5 set pwd=MD5(pwd);

-- 插入的时候加密
insert into testmd5 values(5,'lyy',MD5('123456'));

-- 检验
select * from testmd5 where name='ydd' and pwd=MD5('123456');

5.4、函数汇总小结

-- ================ 内置函数 ================

-- 数值函数
abs(x) -- 绝对值 abs(-10.9) = 10
format(x, d) -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
ceil(x) -- 向上取整 ceil(10.1) = 11
floor(x) -- 向下取整 floor (10.1) = 10
round(x) -- 四舍五入去整
mod(m, n) -- m%n m mod n 求余 10%3=1
pi() -- 获得圆周率
pow(m, n) -- m^n
sqrt(x) -- 算术平方根
rand() -- 随机数
truncate(x, d) -- 截取d位小数

-- 时间日期函数
now(), current_timestamp(); -- 当前日期时间
current_date(); -- 当前日期
current_time(); -- 当前时间
date('yyyy-mm-dd hh:ii:ss'); -- 获取日期部分
time('yyyy-mm-dd hh:ii:ss'); -- 获取时间部分
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- 格式化时间
unix_timestamp(); -- 获得unix时间戳
from_unixtime(); -- 从时间戳获得时间

-- 字符串函数
length(string) -- string长度，字节
char_length(string) -- string的字符个数
substring(str, position [,length]) -- 从str的position开始,取length个字符
replace(str ,search_str ,replace_str) -- 在str中用replace_str替换search_str
instr(string ,substring) -- 返回substring首次在string中出现的位置
concat(string [,...]) -- 连接字串
charset(str) -- 返回字串字符集
lcase(string) -- 转换成小写
left(string, length) -- 从string2中的左边起取length个字符
load_file(file_name) -- 从文件读取内容
locate(substring, string [,start_position]) -- 同instr,但可指定开始位置
lpad(string, length, pad) -- 重复用pad加在string开头,直到字串长度为length
ltrim(string) -- 去除前端空格
repeat(string, count) -- 重复count次
rpad(string, length, pad) --在str后用pad补充,直到长度为length
rtrim(string) -- 去除后端空格
strcmp(string1 ,string2) -- 逐字符比较两字串大小

-- 聚合函数
count()
sum();
max();
min();
avg();
group_concat()

-- 其他常用函数
md5();
default();

6、事务

6.1、事务的ACID原则

• 原子性(Atomic)
  整个事务中的所有操作，要么全部完成，要么全部不完成。发生错误，会被回滚（ROLLBACK）到事务开始前的状态。

• 一致性(Consist)

  一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)。

  【最终一致性】以转账案例为例，无论怎么转，最后和为1000

  

• 隔离性(Isolated)

  【隔离级别】会引起脏读等问题：https://badwei.xyz/archives/shi-wu-de-7-zhong-chuan-bo-xing-wei-4-zhong-ge-li-ji-bie-yi-ji-zang-xie-zang-du-bu-ke-zhong-fu-du-huan-du

  隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

• 持久性(Durable)

  事务未提交，服务器宕机，恢复到原状

  在事务提交完成以后，服务器宕机，持久化到数据库

6.2、mysql提交事务

-- 使用set语句来改变自动提交模式
SET autocommit = 0; /*关闭*/
SET autocommit = 1; /*开启*/

-- 注意:
--- 1.MySQL中默认是自动提交
--- 2.使用事务时应先关闭自动提交
-- 开始一个事务,标记事务的起始点
START TRANSACTION

-- 提交一个事务给数据库
COMMIT

-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK

-- 保存点(了解)
SAVEPOINT '保存点名称' -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT '保存点名称' -- 回滚到保存点
RELEASE SAVEPOINT '保存点名称' -- 删除保存点

7、索引

索引index是mysql高效获取数据的数据结构

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

索引的作用

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

7.1、索引分类

• 主键索引 (Primary Key) 主键只有一个，可以多列

• 唯一索引 (Unique) 一个表中唯一索引列可以有多个，每个列中数据不可重复
  唯一索引中的"唯一"并不是说这个索引在一个表中只能出现一次
  此处的唯一指的是一个列中的数据，表示一个列用了此索引，则此列中的数据不能有重复的

  CREATE TABLE `Grade`(
  `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
  `GradeName` VARCHAR(32) NOT NULL UNIQUE
  -- 或 UNIQUE KEY `GradeID` (`GradeID`)
  )
  

• 常规索引 (Index)
  index 和 key 关键字都可以设置常规索引

  CREATE TABLE `result`(
  -- 省略一些代码
  	INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
  )
  -- 创建后添加
  ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);
  

• 全文索引 (FullText)
  作用 : 快速定位特定数据

全文索引

只能用于MyISAM类型的数据表
只能用于CHAR , VARCHAR , TEXT数据列类型
适合大型数据集

show index from student;

-- 增加全文索引
alter table student add FULLTEXT INDEX `studentnamefulltext` (`studentname`);

插入100万条数据

-- 插入100万数据
DROP FUNCTION IF EXISTS mock_data;
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), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
		SET i = i + 1;
	END WHILE;
	RETURN i;
END;
SELECT mock_data();

添加索引，效率测试

-- 测试查询速度
select * from app_user where `name` = '用户9998';

-- 创建索引
create index id_app_user_name on app_user(`name`);

7.2、索引准则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表建议不要加索引
• 索引一般应加在查找条件的字段

7.3、索引的数据结构(b+树)

b,b+,b*树参考文章：https://blog.csdn.net/u013411246/article/details/81088914

小结：

**二叉搜索树：**二叉树，每个结点只存储一个关键字，等于则命中，小于走左结点，大于

走右结点；

​ **B（B-）树：**多路搜索树，每个结点存储M/2到M个关键字，非叶子结点存储指向关键

字范围的子结点； 所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；

​ **B+树：**在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点

中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；

​ B*树：在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3；

索引参考文章：https://blog.csdn.net/wufuhuai/article/details/79631466

-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

b+树

b+树和hash区别

从上面的图来看，B+树索引和哈希索引的明显区别是：

• 如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
• 从示意图中也能看到，如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
• 同理，哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；
• 哈希索引也不支持多列联合索引的最左匹配规则；
• B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

8、权限管理，数据备份

8.1、权限管理

1.可视化工具管理

2.基本命令

-- 刷新权限
FLUSH PRIVILEGES
-- 增加用户 CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
	- 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
	- 只能创建用户，不能赋予权限。
	- 用户名，注意引号：如 'user_name'@'192.168.1.1'
	- 密码也需引号，纯数字密码也要加引号
	- 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD

-- 重命名用户 RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user

-- 设置密码
SET PASSWORD = PASSWORD('密码') -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码') -- 为指定用户设置密码

-- 删除用户 DROP USER kuangshen2
DROP USER 用户名

-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
	- all privileges 表示所有权限
	- *.* 表示所有库的所有表
	- 库名.表名 表示某库下面的某表
	
-- 查看权限 SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用户名
	-- 查看当前用户权限
	SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR
CURRENT_USER();

-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名 -- 撤销所有权限

8.2、MySQL备份

mysql数据库备份的方式

• 直接拷贝物理文件
• 在可视化工具操作
• 命令行mysqldump

mysqldump命令

-- 导出
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)
可以-w携带备份条件

-- 导入
1. 在登录mysql的情况下： -- source D:/a.sql
source 备份文件
2. 在不登录的情况下
mysql -u用户名 -p密码 库名 < 备份文件

9、规范化数据库设计

9.1、为什么需要数据库设计

良好的数据库设计 :

• 节省数据的存储空间
• 能够保证数据的完整性
• 方便进行数据库应用系统的开发

软件项目开发周期中数据库设计 :

• 需求分析阶段: 分析客户的业务和数据处理需求

• 概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.

设计数据库步骤

• 收集信息
  与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.
• 标识实体[Entity]
  标识数据库要管理的关键对象或实体,实体一般是名词
• 标识每个实体需要存储的详细信息[Attribute]
• 标识实体之间的关系[Relationship]

9.2、三大范式

三大范式简述：

1.每列不可再分
2.每张表只做一件事情
3.所有字段都和主键直接相关，而非间接相关

第一范式 (1st NF)
第一范式的目标是确保每列的原子性,如果每列不可再分,则满足第一范式

第二范式(2nd NF)
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一
范式（1NF）。
第二范式要求每个表只描述一件事情

第三范式(3rd NF)
如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

9.3、规范化和性能的关系

阿里规范：连表查询不得超过三张表，原因：

• 数据库性能比规范化数据库更重要
• 在数据规范化的同时 , 要综合考虑数据库的性能
• 通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间
• 通过在给定的表中插入计算列,以方便查询

10.SQL注入

本质：sql拼接，or之类的拼接

10.1、SQL注入攻击实例

String sql = "select * from user_table where username=
' "+userName+" ' and password=' "+password+" '";

--当输入了上面的用户名和密码，上面的SQL语句变成：
SELECT * FROM user_table WHERE username=
'’or 1 = 1 -- and password='’

"""
--分析SQL语句：
--条件后面username=”or 1=1 用户名等于 ” 或1=1 那么这个条件一定会成功；

--然后后面加两个-，这意味着注释，它将后面的语句注释，让他们不起作用，这样语句永远都--能正确执行，用户轻易骗过系统，获取合法身份。
--这还是比较温柔的，如果是执行
SELECT * FROM user_table WHERE
username='' ;DROP DATABASE (DB Name) --' and password=''
--其后果可想而知…
"""

10.2、如何防御SQL注入

1、检查变量数据类型和格式

2、过滤特殊符号

3、绑定变量，使用预编译语句

10.3、什么是sql预编译

Prepared Statements

​ 很多情况，我们的一条sql语句可能会反复执行，或者每次执行的时候只有个别的值不同（比如query的where子句值不同，update的set子句值不同,insert的values值不同）。
　　如果每次都需要经过上面的词法语义解析、语句优化、制定执行计划等，则效率就明显不行了。

　　所谓预编译语句就是将这类语句中的值用占位符替代，可以视为将sql语句模板化或者说参数化，一般称这类语句叫Prepared Statements或者Parameterized Statements
　　预编译语句的优势在于归纳为：一次编译、多次运行，省去了解析优化等过程；此外预编译语句能防止sql注入。

10.4、MySQL的预编译功能

1.建表

CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(20) DEFAULT NULL,
  UNIQUE KEY `ab` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2.编译

prepare ins from 'insert into t select ?,?';

3.执行

set @a=999,@b='hello';
execute ins using @a,@b;
select * from t;

MySQL中的预编译语句作用域是session级，但我们可以通过max_prepared_stmt_count变量来控制全局最大的存储的预编译语句。

4.释放

deallocate prepare ins;

10.5、为什么PrepareStatement可以防止sql注入

​ badwei总结的本质【预编译SQL，语句是语句，参数是参数】

​ 原理是采用了预编译的方法，先将SQL语句中可被客户端控制的参数集进行编译，生成对应的临时变量集，再使用对应的设置方法，为临时变量集里面的元素进行赋值，赋值函数setString()，会对传入的参数进行强制类型检查和安全检查，所以就避免了SQL注入的产生。

1.为什么Statement会被sql注入

​ 在用户输入'or true or'之后sql语句结构改变。select*from tablename where username=''or true or'' and password=''

2.为什么Preparement可以防止SQL注入

因为Preparement样式为select*from tablename where username=? and password=?

​ 该SQL语句会在得到用户的输入之前先用数据库进行预编译，这样的话不管用户输入什么用户名和密码的判断始终都是并的逻辑关系，防止了SQL注入

　　简单总结，参数化能防注入的原因在于，语句是语句，参数是参数，参数的值并不是语句的一部分，数据库只按语句的语义跑，至于跑的时候是带一个普通背包还是一个怪物，不会影响行进路线，无非跑的快点与慢点的区别。

10.6mybatis是如何防止SQL注入的　

mybatis中的#和$的区别

<select id="selectByNameAndPassword" parameterType="java.util.Map" resultMap="BaseResultMap">
select id, username, password, role
from user
where username = #{username,jdbcType=VARCHAR}
and password = #{password,jdbcType=VARCHAR}
</select>

<select id="selectByNameAndPassword" parameterType="java.util.Map" resultMap="BaseResultMap">
select id, username, password, role
from user
where username = ${username,jdbcType=VARCHAR}
and password = ${password,jdbcType=VARCHAR}
</select>

mybatis中的#和$的区别：

1、#将传入的数据都当成一个字符串，会对自动传入的数据加一个双引号

where username=#{username}
-- 如果传入的值是111,那么解析成sql时的值为where username="111",

2、$将传入的数据直接显示生成在sql中

where username=${username}
-- 如果传入的值是111,那么解析成sql时的值为where username=111；

3.#方式能够很大程度防止sql注入，$方式无法防止Sql注入

4、$方式一般用于传入数据库对象，例如传入表名

5、一般能用#的就别用$，若不得不使用“$”这样的参数，要手工地做好过滤工作，来防止sql注入攻击。

6、在MyBatis中，${xxx}这样格式的参数会直接参与SQL编译，从而不能避免注入攻击。但涉及到动态表名和列名时，只能使用${xxx}这样的参数格式。所以，这样的参数需要我们在代码中手工进行处理来防止注入。

【结论】在编写MyBatis的映射语句时，尽量采用“#”这样的格式。若不得不使用“$”这样的参数，要手工地做好过滤工作，来防止SQL注入攻击。

mybatis是如何做到防止sql注入的

MyBatis启用了预编译功能，在SQL执行前，会先将上面的SQL发送给数据库进行编译；执行时，直接使用编译好的SQL，替换占位符“?”就可以了。因为SQL注入只能对编译过程起作用，所以这样的方式就很好地避免了SQL注入的问题。

【底层实现原理】MyBatis是如何做到SQL预编译的呢？其实在框架底层，是JDBC中的PreparedStatement类在起作用，PreparedStatement是我们很熟悉的Statement的子类，它的对象包含了编译好的SQL语句。这种“准备好”的方式不仅能提高安全性，而且在多次执行同一个SQL时，能够提高效率。原因是SQL已编译好，再次执行时无需再编译

11、慢SQL

11.1、导致慢SQL的原因：

1.查询语句写的烂

2.索引失效

3.关联查询太多（设计缺陷）

4.服务器调优参数（缓冲，线程数）

11.2、SQL解析流程

11.3、七种join

内连接 inner join

左连接left join 左连接去交集 left join.. where * is null

右连接right join 右连接去交集 right join ..where * is null

全连接full outer join 全连接去交集 full outer join.. where * is null

12、索引（高级部分）

简介：

1.放入数据，排序

2.b+树，一页一页，默认16kB

3.一般2-3层，顶层常驻内存

12.1b+数结构详解

b+数查29的话：

真实的情况是，三层的b+数可以表示上百万的数据，只需要一到三次IO就可查到，非常快

mysql的innoDB将根节点常驻内存，所以查主键最多2次IO，非主键索引（唯一，常规，全文）最多3次IO

b+树和b树的区别

• B树

  每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null。

• B+树

  只有叶子节点存储data，叶子节点包含了这棵树的所有键值，叶子节点不存储指针。

• 后来，在B+树上增加了顺序访问指针，也就是每个叶子节点增加一个指向相邻叶子节点的指针，这样一棵树成了数据库系统实现索引的首选数据结构。

• b+树非叶子节点只存储【键（无值）和指针】信息

• 所有叶子节点之间都有一个链指针

• 数据都存在叶子节点中

12.2聚簇索引和非聚簇索引

**聚簇索引：**将数据存储与索引放到一块，索引结构的叶子节点保存了行数据（innoDB的主键索引）

**非聚簇索引：**将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置（其他索引）

非聚簇索引都是辅助索引，辅助索引访问数据需要二次查找，如图：，先在自己的索引表中找到主键，然后通过主键在主键索引中找到数据

**为什么存主键不存地址的原因：**增删改数据时，主键的地址会发生变化（主键索引会排序，b+树，地址会变化）

InnoDB使用的是聚簇索引（如上图），将主键组织到一颗b+树中，行数据存储在叶子节点上。

• 查找id的话，直接b+树检索
• 查找name的话，第一步在辅助索引的b+树中检索name，获取叶子节点对应的主键；第二步使用主键在主键索引中进行b+树检索
• 聚簇索引默认是主键，如果表中没有定义主键，InnoDB会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引

MYISAM使用的是非聚簇索引（如下图），非聚簇索引两颗b+树看起来没什么不同，节点的结构完全一致只是存储的内容不同，主键索引b+树存储了主键，辅助键索引b+树存储了辅助键。这两棵树的叶子节点都使用一个地址指向了真正的表数据，对于表数据来说，这两个键没有任何差别，由于索引树是独立的，叶子节点指针指向真实数据，通过辅助键索引检索无序访问主键的索引树

使用聚簇索引的优势

每次访问聚簇索引都要进行两次查询，而非聚簇索引只进行一次，为什么还要使用聚簇索引呢？

1.缓存：由于行数据和聚簇索引的叶子节点在一起，同一页中有多条数据，访问统一数据页的不同数据时，已经把页加载到了buffer（缓冲器）中，再次访问，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回。

**2.增删改维护：**辅助索引的叶子节点存储的是主键的值，而不是数据存放地址。如果是非聚簇索引，存放的是数据地址，当增删改时，数据地址也会变化，需要更新辅助索引（变化大）。但是聚簇索引由于存储的是主键的值，只更新那一条数据即可。

辅助索引存储的是主键值，减少了辅助索引占用的空间大小。

使用聚簇索引需要注意什么

• 当使用主键为聚簇索引时，最好不要使用UUID，因为UUID的值太过离散，不适合排序，添加数据对索引树影响大
• 建议使用int类型的自增，对索引树影响小，（分布式系统不建议使用自增id,建议时间戳+随机数或者雪花算法）

12.3哪些情况需要创建索引

主键自动创建唯一索引

频繁作为查询条件的字段

查询中排序的字段

查询或分组的字段

12.4索引优化，索引失效

1.最好用=全值匹配

2.**最佳左前缀法则：**如果索引了多列，要遵守最佳左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。

3.不要在索引列上做任何操作（计算，函数，类型转换），会导致索引失效全表扫描

4.范围条件右边的列的索引无效

5.少用select * 尽量和索引重合

6.使用不等于（!=或者<>）会导致索引失效

7.is null is not null也无法使用索引

8.like索引失效，全表扫描

9.字符串失去单引号''会导致索引失效，比如查询'2000'，输入2000，默认int，mysql转为varchar，隐式类型转换

10.用or也会导致索引失效

12.5索引失效的b+数底层原理

什么情况下索引会失效。

比如：没遵循最佳左前缀法则、范围查询的右边会失效、like查询用不到索引等等

索引失效的原理是什么

联合索引

开局一张图，由数据库的a字段和b字段组成一个联合索引。

从本质上来说，联合索引也是一个B+树，和单值索引不同的是，联合索引的键值对不是1，而是大于1个。

a, b 排序分析

a顺序：1，1，2，2，3，3

b顺序：1，2，1，4，1，2

大家可以发现a字段是有序排列，b字段是无序排列（因为B+树只能选一个字段来构建有序的树）

一不小心又会发现，在a相等的情况下，b字段是有序的。

大家想想平时编程中我们要对两个字段排序，是不是先按照第一个字段排序，如果第一个字段出现相等的情况，就用第二个字段排序。这个排序方式同样被用到了B+树里。

1分析最佳左前缀原理

分析如下：

首先a字段在B+树上是有序的，所以我们可以通过二分查找法来定位到a=1的位置。

其次在a确定的情况下，b是相对有序的，因为有序，所以同样可以通过二分查找法找到b=2的位置。

2范围查询右边失效原理

select * from testTable where a>1 and b=2

分析如下：

首先a字段在B+树上是有序的，所以可以用二分查找法定位到1，然后将所有大于1的数据取出来，a可以用到索引。

b有序的前提是a是确定的值，那么现在a的值是取大于1的，可能有10个大于1的a，也可能有一百个a。

大于1的a那部分的B+树里，b字段是无序的（开局一张图），所以b不能在无序的B+树里用二分查找来查询，b用不到索引【索引失效】。

3like索引失效原理

一、%号放右边（前缀）

由于B+树的索引顺序，是按照首字母的大小进行排序，前缀匹配又是匹配首字母。所以可以在B+树上进行有序的查找，查找首字母符合要求的数据。所以有些时候可以用到索引。

二、%号放左边（后缀）

是匹配字符串尾部的数据，我们上面说了排序规则，尾部的字母是没有顺序的，所以不能按照索引顺序查询，就用不到索引。

三、两个%%号（中）

这个是查询任意位置的字母满足条件即可，只有首字母是进行索引排序的，其他位置的字母都是相对无序的，所以查找任意位置的字母是用不上索引的。

13、innoDB和MyISAM的区别

两种存储引擎的区别：

1、MyISAM是非事务安全的，而InnoDB是事务安全的

2、MyISAM锁的粒度是表级的，而InnoDB支持行级锁

3、MyISAM支持全文类型索引，而InnoDB不支持全文索引

4、MyISAM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyISAM

5、MyISAM表保存成文件形式，跨平台使用更加方便

6、MyISAM管理非事务表，提供高速存储和检索以及全文搜索能力，如果在应用中执行大量select操作可选择

7、InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，可选择。

innoDB支持事务

innoDB支持行锁

innoDB的页引擎的大小为16kB

三层大约有10亿条记录

mysql的默认隔离级别：可重读