标签：ename sal 索引 emp MySQL where select

一、简介

1、sql、DB、DBMS分别是什么，他们之间的关系？

DB：DataBase（数据库，数据库实际上在硬盘上以文件的形式存在）
DBMS：DataBase Management System（数据库管理系统，常见的有：MySQL、Oracle、DB2、Sybase、SqlServer...）
SQL：
- 结构化查询语言，是一门标准通用的语言，标准的sql适合于所有的数据库产品。
- SQL属于高级语言，只要能看懂英语单词，写出来的SQL语句，可以读懂什么意思。
- SQL语句在执行的时候，实际上内部也会先进行编译，然后再执行sql。（sql语句的编译由DBMS完成）

2、什么是表？

表：table是数据库的基本组成单元，所有的数据都以表格的形式组织，目的是可读性强。

一个表包括行和列：

行：被称为数据/记录（data）

列：被称为字段（column）

每一个字段应该包括哪些属性？

字段名、数据类型、相关的约束

3、SQL语句分类

DQL（数据查询语言）：查询语句，凡是select语句都是DQL。
DML（数据操作语言）：insert、delete、update，对表当中的数据进行增删改。
DDL（数据定义语言）：create、drop、alter，对表结构的增删改。
TCL（事务控制语言）：commit提交事务，rollback回滚事务。
DCL（数据控制语言）：grant授权、revoke撤销权限等。

4、什么是sql脚本呢？

当一个文件的扩展名是.sql，并且该文件中编写了大量的sql语句，我们称这样的文件为sql脚本。
注意：直接使用source命令可以执行sql脚本
sql脚本中的数据量太大的时候，无法打开，请使用source命令完成初始化。

二、命令

1、导入数据

 1. 登录mysql数据库管理系统 
    dos命令窗口：	
    	mysql -uroot -p123456
 2. 查看有哪些数据库
    show databases;
 3. 创建数据库
    create database bjpowernode;
 4. 使用bjpowernode
    use bjpowernode;
 5. 查看当前使用的数据库有哪些表
    show tables;
 6. 初始化数据
    source F:\QQ\815767870\FileRecv\bjpowernode.sql

2、删除数据库：

drop database bjpowernode;

3、查看表结构：

desc dept;

4、查看表数据：

select * from dept;

5、常用命令

select database();	查看当前使用的是哪个数据库
select version();	查看mysql的版本号
\c		命令，结束一条语句
exit	 命令，退出mysql

6、查看创建表的语句：

show create table emp;

三、查询

1、简单的查询语句（DQL）

语法格式：
select 字段名1,字段名2,字段名3,... from 表名;

提示：
1、任何一条sql语句以“;”结尾。
2、sql语句不区分大小写。

查询员工的年薪？（字段可以参与数学运算）
	select ename,sal * 12 from emp;
给查询结果的列重命名？
	select ename,sal * 12 as '年薪' from emp;
注意：标准sql语句中要求字符串使用单引号括起来。虽然mysql支持双引号，尽量别用。

as关键字可以省略
	select empno,ename,sal * 12 yearsal from emp;
查询所有字段？
	select * from emp;	// 实际开发中不建议使用*，效率较低。
查询结果集的去重？
	select distinct job from emp; // distinct关键字去除重复记录

2、条件查询

between and：

查询工资等于5000的员工姓名？
	select ename from emp where sal = 5000;
查询SMITH的工资？
	select sal from emp where ename = 'SMITH';	// 字符串使用单引号括起来
找出工资高于3000的员工？
	select ename,sal from emp where sal > 3000;
	select ename,sal from emp where sal >= 3000;
	select ename,sal from emp where sal < 3000;
	select ename,sal from emp where sal <= 3000;
找出工资不等于3000的员工？
	select ename,sal from emp where sal <> 3000;
	select ename,sal from emp where sal != 3000;
找出工资在1100和3000之间的员工，包括1100和3000？
	select ename,sal from emp where sal >= 1100 and sal <=3000;
	select ename,sal from emp where  sal between 1100 and 3000;	// between...and...是闭区间 [1100 ~ 3000]
	between...and...在使用的时候要左小右大。
	
between...and...除了可以使用在数字方面之外，还可以使用在字符串方面。
	select ename from emp where ename btween 'A' and 'D';	// 左闭右开

NULL：

找出哪些人津贴为NULL？
	在数据库中NULL不是一个值，代表什么也没有，为空。
	空不是一个值，不能用等号衡量。
	必须使用is null 或者is not null。
		select ename,sal,comm from emp where comm is null;
找出哪些人津贴不为NULL？
	select ename,sal,comm from emp where comm is not null;
找出哪些人没有津贴？
	select ename,sal,comm from emp where comm is null or comm = 0;
找出工作岗位是MANAGER和SALESMAN的员工？
	select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';

and和or联合起来用：

找出薪资大于1000的并且部门编号是20或30部门的员工。
	select ename,sal,deptno from emp where sal > 1000 and deptno = 20 or deptno = 30;	// 错误的
	select ename,sal,deptno from emp where sal > 1000 and (deptno = 20 or deptno = 30);	//正确的
注意：当运算符的优先级不确定的时候加小括号。

in等同于or：

找出工作岗位是MANAGER和SALESMAN的员工？
	select ename,job from emp where job in ('MANAGER','SALESMAN');
	select ename,sal from emp where sal in (800,5000);	// in后面的值不是区间，是具体的值。

not in：不在这几个值当中。

select ename,sal from emp where sal not in (800,5000);

模糊查询like

找出名字当中含有o的？
	（在模糊查询当中，必须掌握两个特殊的符号，一个是%，一个是_）
	%代表任意多个字符，_代表任意1个字符。
	select ename from emp where ename like '%o%';
找出名字中第二个字母是A的？
	select ename from emp where ename like '_A%';
找出名字中有下划线的？
	select ename from emp where ename like '%\_%';
找出名字中最后一个字母是T的？
	select ename from emp where ename like '%T';

3、排序（升序、降序）

按照工资升序，找出员工名和薪资？
	select ename,sal from emp order by sal;
注意：默认是升序。怎么指定升序或者降序呢？asc表示升序，desc表示降序。
	select ename,sal from emp order by sal;	// 升序
	select ename,sal from emp order by sal asc;	// 升序
	select ename,sal from emp order by sal desc; // 降序
按照工资的降序排序，当工资相同的时候再按照名字的升序排序，
	select ename,sal from emp order by sal desc, ename asc;

注意：越靠前的字段越能起到主导作用，只有当前面的字段无法完成排序的时候，才会启用后面的字段。

找出工作岗位是SALESMAN的员工，并且要求薪资的降序排序。

select
	ename,job,sal
from
	emp
where
	job = 'SALESMAN'
order by 
	sal desc;

4、分组函数

count	计数
sum	求和
avg	平均值
max	最大值
min	最小值
记住：所有的分组函数都是对“某一组”数据进行操作的。

找出工资总和？
	select sum(sal) from emp;
找出最高工资？
	select max(sal) from emp;
找出最低工资？
	select min(sal) from emp;
找出平均工资？
	select avg(sal) from emp;
找出总人数？
	select count(*) from emp;
	select count(ename) from emp;
	
分组函数一共5个。
分组函数还有另一个名字：多行处理函数。
多行处理函数的特点：输入多行，最终输出的结果是1行。

注意：分组函数自动忽略NULL。

count(*)和count(具体的某个字段)的区别？
	count(*)：统计总记录条数。（和某个字段无关）
	count(comm)：表示统计comm字段中不为NULL的数据总数量。
	
分组函数也能组合起来用：
	select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

找出工资高于平均工资的员工？
	select ename,sal from emp where sal > avg(sal); // ERROR 1111 (HY000): Invalid use of group function
	思考以上的错误信息：无效的使用了分组函数?
		原因：SQL语句中有一个语法规则，分组函数不可直接使用在where子句当中。
		因为group by是在where执行之后才会执行的，任何一个分组函数都是在group by语句执行结束后才会执行的。
		select		5
			..
		from		1
			..
		where		2
			..
		group by	3
			..
		having		4
			..
		order by	6
			..
	
    第一步：找出平均工资
    	select avg(sal) from emp;
    第二部：找出大于平均工资的员工
    	select ename,sal from emp where sal > (select avg(sal) from emp);

5、单行处理函数

什么是单行处理函数？
	输入一行，输出一行。

计算每个员工的年薪？
	select ename,(sal+comm)*12 as yearsal from emp;
	重点：所有数据库都是这样规定的，只要有NULL参与的运算结果一定是NULL。
	
	使用ifnull()函数：
	select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
	
ifnull() 空处理函数
	ifnull(可能为NULL的数据，被当做什么处理)

6、group by 和 having

group by：按照某个字段或者某些字段进行分组。

having：having是对分组之后的数据进行再次过滤。

注意：分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。
并且任何一个分组函数都是在group by语句执行结束后才会执行的。
当一条sql语句没用group by的话，整张表的数据会自成一组。

案例：找出每个工作岗位的最高薪资。
	select max(sal),job from emp group by job;
记住一个规则：当一条语句中有group by的时候，select后只能跟分组函数和参加分组的字段。

每个工作岗位的平均薪资？
	select job,avg(sal) from emp group by job;
	
多个字段能不能联合起来一块分组？
案例：找出每个部门不同工作岗位的最高薪资。 
	select max(sal),deptno,job from emp group by deptno,job;
	
找出每个部门的最高薪资，要求显示薪资大于2900的数据。
第一步：找出每个部门的最高薪资
	select max(sal),deptno from emp group by deptno;
第二步：找出薪资大于2900
	select max(sal),deptno from emp group by deptno having max(sal) > 2900; // 这种方式效率低
	select max(sal),deptno from emp where sal > 2900 group by deptno; // 效率较高，建议能够使用where过滤的尽量使用where。

找出每个部门的平均薪资，要求显示薪资大于2900的数据。
第一步：找出每个部门的平均薪资
	select avg(sal),deptno from emp group by deptno;
第二步：找出薪资大于2900
	select avg(sal),deptno from emp group by deptno having avg(sal) > 2900;

7、总结一个完整的DQL语句怎么写？

select		5
	..
from		1
	..
where		2
	..
group by	3
	..
having		4
	..
order by	6
	..

8、连接查询

8.1、什么是连接查询？

在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。

8.2、连接查询的分类？

根据语法出现的年代来划分的话，包括：
- SQL92（一些老的DBA可能还在使用这种语法。DBA：DataBase Administrator，数据库管理员）
- SQL99（比较新的语法）
根据表的连接方式来划分，包括：
- 内连接：
  - 等值连接
  - 非等值连接
- 外连接：
  - 左外连接（左连接）
  - 右外连接（右连接）
- 全连接（这个很少用）

8.3、笛卡尔积现象

在表的连接查询方面有一种现象被称为：笛卡尔积现象。（笛卡尔乘积现象）

案例：找出每一个员工的部门名称，要求显示员工名和部门名
select ename,dname from emp,dept;

笛卡尔积现象：
	如果两张表进行连接查询没有任何条件限制，最后的查询结果条数是两张表中记录条数的乘积。
	
关于表的别名：
	select e.ename,d.dname from emp e,dept d;
	表的别名有什么好处？
		第一：执行效率高
		第二：可读性好

8.4、避免笛卡尔积现象，增加过滤条件

思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？

不会，只不过显示的是有效记录条数。

案例：找出每一个员工的部门名称，要求显示员工名和部门名
	select
		e.ename,d.dname
	from 
		emp e,dept d
	where
		e.deptno = d.deptno;

8.5、内连接之等值连接

最大特点：条件是等量关系

案例：查询每个员工的部门名称，要求显示员工名和部门名
SQL92:(太老了，不用)
	select
		e.name,d.name
	from
		emp e,dept d
	where
		e.deptno = d.deptno;

SQL99:(常用的) inner可以省略，带着可读性好一些
	select
		e.name,d.name
	from
		emp e
	inner join
		dept d
	on
		e.deptno = d.deptno;// 等量关系 等值连接
		
SQL99语法结构更清晰一些：表的连接条件和后来的where条件分离了。

8.6、内连接之非等值连接

最大特点：条件是非等量关系

案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级
select
	e.ename,e.sal,s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal;

8.7、自连接

最大特点：一张表看做两张表，自己连接自己

案例：找出每个员工的上级领导，要求显示员工名和对应的领导名
select
	e1.ename as '员工'，e2.ename as '领导'
from
	emp e1
join
	emp e2
on
	e1.mgr = e2.empno;

8.8、外连接

什么是外连接？

内连接：假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。
外连接：假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没用和主表中的数据匹配上，副表会自动模拟出NULL与之匹配。
外连接的分类：
- 左外连接（左连接）：表示左边的这张表是主表
- 右外连接（右连接）：表示右边的这张表是主表
- 左连接有右连接的写法，右连接也有对应的左连接的写法。

案例：找出每个员工的上级领导
// 左外连接 outer可省略
select
	a.ename as '员工',b.ename as '领导'
from
	emp a
left outer join
	emp b
on
	a.mgr = b.empno;
	
外连接最重要的特点是：主表的数据无条件的全部查询出来

案例：找出哪个部门没有员工？
select 
	d.*
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
where
	e.empno is null;

8.9、三张表连接查询

案例：找出每一个员工的部门名称以及工资等级
select
	e.ename,d.dname,s.grade
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal;
	
案例：找出每一个员工的部门名称、工资等级以及上级领导
select
	e.ename as '员工',d.dname,s.grade,e1.ename as '领导'
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal
left join
	emp e1
on
	e.mgr = e1.empno;

9、子查询

什么是子查询？

select语句当中嵌套select语句，被嵌套的select语句就是子查询

子查询可以出现在哪里？

select
	..(select).
from
	..(select).
where
	..(select).

9.1、where子句中使用子查询

案例：找出高于平均薪资的员工信息
select * from emp where sal > avg(sal); // 错误的写法，where后面不能直接使用分组函数。

第一步：找出平均薪资
	select avg(sal) from emp;
第二步：where过滤
	select * from emp where sal > 2073.214286;
	
合并：
	select * from emp where sal > (select avg(sal) from emp);

9.2、from后面嵌套子查询

案例：找出每个部门平均薪资的薪资等级

第一步：找出每个部门的平均薪水（按照部门编号分组，求sal平均值）
	select deptno,avf(sal) from emp group by deptno;

第二步：将以上的查询结果当做临时表t，让t表和salgrade s表连接
	select 
		t.*,s.grade
	from
		(select deptno,avg(sal) as avgsal from emp group by deptno) t
	join
		salgrade s
	on
		t.avgsal between s.losal and s.hisal;

9.3、在select后嵌套子查询

案例：找到每个员工所在的部门名称，要求显示员工名和部门名

select 
	e.ename,
	(select d.dname from dept d where e.deptno = d.deptno) as dname
from 
	emp e;

10、union（结果集相加）u你嗯

案例：找出工作岗位是SALESMAN和MANAGER的员工
第一种：select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
第二种：select ename,job from emp where job in ('MANAGER','SALESMAN');

第三种：union
select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';

两张不相关的表中的数据拼接在一起显示
select ename from emp
union
select dname from dept;

union两边查询的字段数量要一致

11、limit（分页查询）

limit是mysql特有的，其他数据库中没用，不通用。（Oracle中有一个相同的机制，叫做rownum）
limit取结果集中的部分数据，这是它的作用。

语法机制：
	limit startIndex, length
		startIndex表示起始位置
		length表示取几个
	
案例：取出工资前五名的员工（思路：降序取前5个）
	select ename,sal from emp order by sal desc limit 0,5;
	
limit是sql语句最后执行的一个环节
	select		5
		...
	from		1
		...
	where		2
		...
	group by	3
		...
	having		4
		...
	order by	6
		...
	limit		7
		...
		
案例：找出工资排名在第4到9名的员工
	select ename,sal from emp order by desc limit 3,6
	
通用分页
limit （pageNo - 1）* pageSize, pageSize

三、DDL表结构的增删改：

1、建表语句语法格式：

create table 表名(
	字段名1 数据类型,
    字段名2 数据类型,
    字段名3 数据类型,
    ...
);

2、MySQL常见的数据类型：

int		整数型（java中的int）
bigint	长整形（java中的long）
float	浮点型（java中的double）
char	定长字符串(String)
varchar	可变字符串(StringBuffer/StringBuilder)
date	日期类型(java.sql.Date)
BLOB	二进制大对象(存储图片、视频等流媒体信息) Binary Large OBject (对应java的Object)
CLOB	字符大对象（存储较大文本，比如可以存储4G的字符串）Character Large OBject(对应java的Object)

char和varchar怎么选择？
	在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别，生日等都是采用char。
	当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。

3、创建学生表

学生信息包括：
	学号、姓名、性别、班级编号、生日
	学号：bigint
	姓名：varchar
	性别：char
	班级编号：varchar
	生日：char
	
create table t_student(
	no bigint,
    name varchar(255),
    sex char(1),
    classno varchar(255),
    birth char(10)
);

4、表的复制：

语法：
	create table 表名 as select语句;
	将查询结果当做表创建出来。

5、将查询结果插入到一张表中

insert into dept1 select * from dept;

6、删除表

drop table emp;

7、修改表

change修改字段时需要指定要修改的字段，以及修改后的字段属性
alter table tablename change column newColumn type ;

modify修改字段时只需要指定要修改的字段和修改后的属性
alter table tablename modify column type；

即modify不能用来修改字段名，change可以修改字段名

四、表数据的增删改（DML）

1、insert语句插入数据

语法格式：
	insert into 表名(字段名1,字段名2,字段名3,...) values(值1,值2,值3,...)
	要求：字段的数量和值的数量相同，并且数据类型要对应相同。
	
insert into t_student(no,name,sex,classno,birth) values (1,'zhangsan','1','gaosan1ban','1950-10-12');

2、修改数据（update）

语法格式：
	update 表名 set 字段名1=值1,字段名2=值2,...where条件;
	
注意：没有条件整张表数据全部更新。

案例：将部门10的LOC修改为SHANGHAI，将部门名称修改为RENSHIBU
update dept set loc='SHANGHAI', dname = 'RENSHIBU' where deptno = 10;

3、删除数据

语法格式：
	delete from 表名 where 条件;

注意：没有条件全部删除。

案例：删除10部门数据？
delete from dept where deptno = 10;

怎么删除大表？（重点）
truncate table emp1; // 表被截断，不可回滚，永远丢失

五、约束（Constraint）

1、什么是约束？

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。

2、常见的约束有哪些？

非空约束（not null）：约束的字段不能为NULL
唯一约束（unique）：约束的字段不能重复
主键约束（primary key）：约束的字段既不能为NULL，也不能重复（简称PK）
外键约束（foreign key）：. . .（简称FK）
检查约束（check）：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。

2.1、非空约束 not null

drop table if exists t_user;
create table t_user(
	id int,
    username varchar(255) not null,
    password varchar(255)
);

insert into t_user(id,password) values (1,'0516');
ERROR 1364 (HY000): Field 'username' doesn`t have a default value

insert into t_user(id,username,password) values (1,'root','123');

2.2、唯一约束（unique）

唯一约束修饰的字段具有唯一性，不可重复，但可以为NULL。
案例：

drop table if exists t_user;
create table t_user(
	id int,
    username varchar(255) unique
);

insert into t_user values (1,'zhangsan');
insert into t_user values (2,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username'

案例：给两个列或多个列添加unique

联合唯一约束 表级约束
drop table if exists t_user;
create table t_user(
	id int,
    usercode varchar(255),
    username varchar(255),
    unique(usercode,username)
);

两个唯一约束 列级约束
drop table if exists t_user;
create table t_user(
	id int,
    usercode varchar(255) unique,
    username varchar(255) unique
);

2.3、主键约束（primary key）

主键字段中的数据不能为NULL，也不能重复。

怎么给一张表添加主键约束
drop table if exists t_user;
create table t_user(
	id int primary key,
    username varchar(255),
    email vaarchar(255)
);

drop table if exists t_user;
create table t_user(
	id int,
    username varchar(255),
    email vaarchar(255)，
    primary key(id)
);

主键相关术语：
- 主键约束：primary key
- 主键字段：id字段添加primary key后，id叫做主键字段
- 主键值：id字段中的每一个值都是主键值
主键有什么作用？
- 表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。
- 主键的作用：主键值是这行记录在这张表当中的唯一标识。（就像一个人的身份证号码一样）
主键分类：
- 根据主键字段的字段数量来划分：
  - 单一主键（推荐使用）
  - 复合主键（多个字段联合起来添加一个主键约束）（复合主键不建议使用，因为复合主键违背三范式）
- 根据主键性质来划分：
  - 自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）
  - 业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码做主键
  - 最好不要拿着和业务挂钩的字段作为主键，因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，单有的时候没有办法变化，因为变化可能会导致主键值重复。
一张表的主键约束只能有1个。

mysql提供主键自增：
drop table if exists t_user;
create table t_user(
	id int auto_increment, // id字段自动维护一个自增的数字，从1开始，以1递增。
    username varchar(255),
    primary key(id)
);
insert into t_user(username) values('A');
insert into t_user(username) values('B');
insert into t_user(username) values('C');
insert into t_user(username) values('D');
insert into t_user(username) values('E');

提示：Oracle也提供了一个自增机制，叫做：序列（sequence）对象。

2.4、外键约束（foreign key）

外键约束相关术语：
- 外键约束：foreign key
- 外键字段：添加有外键约束的字段
- 外键值：外键字段的每一个值。

t_class 班级表
cno(pk)     cname
-------------------------
101			******高三1班
102			******高三2班

t_student 学生表
sno(pk)     sname			classno(该字段添加外键约束fk)
---------------------------------------------------------------------------
1			zs1				101
2			zs2				101
3			zs3				102
4			zs4				102


将以上表的建表语句写出来：
	t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表。
	
顺序要求：
	删除数据的时候，先删除子表，再删除父表。
	添加数据的时候，先添加父表，再添加子表。
	创建表的时候，先创建父表，再创建子表。
	删除表的时候，先删除子表，再删除父表。
	
drop table if exists t_student;
drop table if exists t_class;

create table t_class(
	cno int,
    cname varchar(255),
    primary key(cno)
);

create table t_student(
	sno int,
    sname varchar(255),
    classno int,
    primary key(sno),
    foreign key(classno) references t_class(cno)
);

insert into t_class (cno,cname) values (101,'xxxxxxxxxxxxxxxxxx');
insert into t_class (cno,cname) values (102,'yyyyyyyyyyyyyyyyyy');

insert into t_student (sno,sname,classno) values (1,'zhangsan',101);
insert into t_student (sno,sname,classno) values (2,'lisi',102);
insert into t_student (sno,sname,classno) values (3,'wangwu',103);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bjpowernode`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))

外键可以为NULL

外键字段引用其他表的某个字段的时候，被引用的字段不一定是主键，但必须具有unique约束。

六、存储引擎（了解）

1、完整的建表语句

create table `t_x` (
	`id` int(11) default null
)engine=InnoDB default charset=utf8;

注意：在mysql中，凡是标识符是可以使用飘号括起来的。最好别用，不通用。

建表的时候可以指定存储引擎，也可以指定字符集。
mysql默认使用的存储引擎是InnoDB方式，默认采用的字符集是UTF8

2、什么是存储引擎？

存储引擎这个名字只有在mysql存在。（Oracle中有对应的机制，但是不叫做存储引擎。Orcale中没有特殊的名字，就是“表的存储方式”）
mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。
每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

3、查看当前mysql支持的存储引擎？

show engines \G

*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL

4、常见的存储引擎

      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
  
  MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。

MyISAM采用三个文件组织一个表：
- 格式文件 - 存储表结构的定义（.frm）
- 数据文件 - 存储表行的内容（.MYD）
- 索引文件 - 存储表上索引（.MYI）
优点：可被压缩，节省存储空间。并且可以被转换为只读表，提高检索效率。
缺点：不支持事务

      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES

优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障

      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO

缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中。

优点：查询速度最快。

七、事务（Transaction）

1、什么是事务

一个事务是一个完整的业务逻辑单元，不可再分。

比如：银行账户转账，从A账户向B账户转账10000，需要执行两条update语句。

update t_act set balance = balance - 10000 where actno = 'act-001';
update t_act set balance = balance + 10000 where actno = 'act-002';

以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。

要想保证以上的两条DML语句同时成功或者同时失败，就需要使用数据库的“事务机制”。

2、和事务相关的语句只有：DML语句。（insert delete update）

因为这三个语句都是和数据库表当中的“数据”相关的。

事务的存在是为了保证数据的完整性，安全性。

3、事务的特性

事务的四大特性：ACID
- A：原子性：事务是最小的工作单元，不可再分
- C：一致性：事务必须保证多条DML语句同时成功或者同时失败
- I：隔离性：事务A与事务B之间具有隔离
- D：持久性：最终数据必须持久化到硬盘文件中，事务才算成功的结束。

4、关于事务之间的隔离性

事务隔离存在隔离级别，理论上隔离级别包括4个
- 第一级别：读未提交（read uncommitted）
  - 对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
  - 读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。
- 第二级别：读已提交（read committed）
  - 对方事务提交之后的数据我方可以读取到
  - 存在的问题是：不可重复读
  - 解决了：脏读现象
- 第三级别：可重复读（repeatable read）
  - 存在的问题：读取到的数据是幻象
  - 解决了：不可重复读问题
- 第四级别：序列化读/串行化读
  - 存在问题：需要事务排队
  - 解决了所有问题

oracle数据库默认的隔离级别是：读已提交

mysql数据库默认的隔离级别是：可重复读

5、演示事务

mysql事务默认情况下是自动提交的
- 只要执行任意一条DML语句则提交一次

关闭自动提交
start transaction;

准备表：
drop table if exists t_user;
create table t_user(
	id int primary key auto_increment,
    username varchar(255)
);

演示：mysql事务自动提交
insert into t_user(username) values('zhangsan');

mysql> insert into t_user(username) values('zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)


演示：关闭自动提交事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(username) values('lisi');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

6、演示事务的隔离级别

第一：演示read uncommitted
	设置事务的全局隔离级别：
		set global transaction isolation level read uncommitted;
	查看事务的全局隔离级别：
		select @@global.tx_isolation;

第二：演示read committed
第三：演示repeatable read
第四：演示serializable

八、索引

1、什么是索引？有什么用？

索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。
在数据库方面查询一张表的时候有两种检索方式：
- 全表扫描
- 根据索引检索（效率很高）
索引为什么可以提高检索效率呢？
- 其实最根本的原理是缩小了扫描的范围。
索引虽然可以提高检索效率，但是不能随意的添加索引。比如，表中的数据经常被修改，这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。
添加索引是给某一个字段，或者说某些字段添加索引

select ename,sal from emp where ename = 'SMITH';
当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。
当ename字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位。

2、怎么创建索引对象？删除？

创建索引对象
create index 索引名称 on 表名(字段名);
删除
drop index 索引名称 on 表名;

3、什么时候考虑给字段添加索引？（满足什么条件）

数据量庞大。（根据客户的需求，根据线上的环境）
该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
该字段经常出现在where子句中。（经常根据哪个字段查询）

4、注意：主键和具有unique约束的字段自动会添加索引。

根据主键查询效率较高。尽量根据主键查询。

5、查看sql语句的执行计划：

explain select ename,sal from emp where sal = 5000;
+----+-------------+-------+------------+------+---------------
| id | select_type | table | partitions | type | possible_keys
+----+-------------+-------+------------+------+---------------
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL
+----+-------------+-------+------------+------+---------------

-+------+---------+------+------+----------+-------------+
 | key  | key_len | ref  | rows | filtered | Extra       |
-+------+---------+------+------+----------+-------------+
 | NULL | NULL    | NULL |   14 |    10.00 | Using where |
-+------+---------+------+------+----------+-------------+

create index emp_sal_index on emp(sal); // 给sal字段添加索引
explain select ename,sal from emp where sal = 5000;
+----+-------------+-------+------------+------+---------------+
| id | select_type | table | partitions | type | possible_keys |
+----+-------------+-------+------------+------+---------------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_sal_index |
+----+-------------+-------+------------+------+---------------+

-+---------------+---------+-------+------+----------+-------+
 | key           | key_len | ref   | rows | filtered | Extra |
-+---------------+---------+-------+------+----------+-------+
 | emp_sal_index | 9       | const |    1 |   100.00 | NULL  |
-+---------------+---------+-------+------+----------+-------+

6、索引的实现原理

通过B Tree缩小扫描范围，底层索引进行了排序、分区，索引会携带数据在表中的“物理地址”，最终会通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位到表中的数据，效率是最高的。

select ename,sal from emp where sal = 5000;
通过索引转换为：
select ename,sal from emp where 物理地址 = 0x3;

7、索引的分类

单一索引：给单个字段添加索引

复合索引：给多个字段联合起来添加1个索引

主键索引：主键上会自动添加索引

唯一索引：有unique约束的字段会自动添加索引

。。。

8、索引什么时候失效

select ename from emp where ename like '%A%';
模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

九、视图（View）

1、什么是视图

站在不同的角度去看数据。（同一张表的数据，通过不同的角度去看待）

2、怎么创建视图？删除

create view myview as select empno,ename from emp;
drop view myview;

注意：只有DQL语句才能以视图对象的方式创建出来

3、对视图进行增删改查

可以对视图进行CRUD操作，对视图进行增删改查，会影响到原表数据。（通过视图影响原表数据的，不是直接操作的原表）

4、面向视图操作

create table table emp_bak as select * from emp;
create view myview as select empno,ename,sal from emp_bak;
update myview set ename='hehe',sal=1 where empno = 7369; // 通过视图修改原表数据
delete from myview where empno = 7369; // 通过视图删除原表数据

5、视图的作用

视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD。

十、DBA命令

1、将数据库当中的数据导出

在windows的dos命令窗口中执行：
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p333 // 导出整个库

mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p333 // 导出指定数据库当中的指定表

2、导入数据

create database bjpowernode;
use bjpowernode;
source D:\bjpowernode.sql

十一、数据库设计三范式

1、什么是设计范式

设计表的依据，按照这三范式设计出来的表不会出现数据冗余。

2、三范式是哪些？

第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。

第二范式：建立在第一范式的基础上，所有非主键字段完全依赖主键，不能产生部分依赖。

多对多？三张表，关系表，两外键。
t_student 学生表
sno(pk)		sname

t_teacher 教师表
tno(pk)		tname

t_student_teacher_relation 学生教师关系表
id(pk)		sno(fk)		tno(fk)

第三范式：建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生依赖传递。

一对多?两张表，多的表，加外键
班级t_class
cno(pk)		cname

学生t_student
sno(pk)		sname		classno(fk)

提醒：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。

3、一对一怎么设计

1.主键共享
t_user_login 用户登录表
id(pk)		username		password

t_user_detail 用户详细信息表
id(pk+fk)		realname		tel			...

----------------------------------------------

2.外键唯一
t_user_login 用户登录表
id(pk)		username		password

t_user_detail 用户详细信息表
id(pk+fk)		realname		tel			userid(fk+unique)			...

4、建表模板

DROP TABLE IF EXISTS ``;
CREATE TABLE `` (
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
	`record_status` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除字段：0正常, 1删除',
	`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	`creator_id` BIGINT(20) NOT NULL COMMENT '创建人ID',
	`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
	`updater_id` BIGINT(20) NOT NULL COMMENT '更新人ID',
	PRIMARY KEY ( `id` ) USING BTREE 
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='';

MySQL实战45讲

一、基础架构

Server层：
- 包括连接器、查询缓存、分析器、优化器、执行器等
- 涵盖MySQL的大多数核心服务功能
- 以及所有的内置函数（如日期、时间、数学和加密函数等）
- 所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
存储引擎层：
- 负责数据的存储和提取
- 支持InnoDB、MyISAM、Memory等多个存储引擎
- MySQL 5.5.5版本开始默认存储引擎为InnoDB

1、连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。

# 连接命令
mysql -h$ip -P$host -u$user -p

# 显示当前连接
show processlist

# 5.7以上 初始化连接资源
mysql_reset_connection

2、查询缓存

MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到key，那么这个value就会被直接返回给客户端。

将参数query_cache_type设置成DEMAND，默认的SQL语句都不使用查询缓存。

# 用SQL_CACHE显式指定使用查询缓存
select SQL_CACHE * from T where ID = 10;

注意：MySQL 8.0版本开始没有查询缓存这个功能了。

3、分析器

分析器会先做“词法分析”，例如从关键字“select”识别出这是一个查询语句，把字符串“T”识别成“表名T”，字符串“ID”识别成“列ID”。
之后做“语法分析”，根据词法分析的结果，语法分析器会根据语法规则判断SQL语句是否满足MySQL语法。

4、优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

5、执行器

执行器阶段开始执行语句，先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示：

mysql> select * from T where ID=10;

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

比如我们这个例子中的表T中，ID字段没有索引，那么执行器的执行流程是这样的：

调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中；
调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

二、日志系统

1、redo log（重做日志）

当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面。

有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

InnoDB引擎特有的日志
redo log是物理日志，记录的是“在某个数据页上做了什么修改”
redo log是循环写的，空间固定会用完

2、binlog（归档日志）

binlog是MySQL的Server层实现的，所有引擎都可以使用。
binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

3、update语句执行流程

update T set c=c+1 where ID=2;

执行器先找引擎取ID=2这一行。ID是主键，引擎直接用树搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的binlog，并把binlog写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

三、事务隔离

简单来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。

在MySQL中，事务支持是在引擎层实现的。

1、隔离性与隔离级别

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantomread）的问题，为了解决这些问题，就有了“隔离级别”的概念。

SQL标准的事务隔离级别包括：

读未提交（read uncommitted）：一个事务还没提交时，它做的变更就能被别的事务看到。
读提交（read committed）：一个事务提交之后，它做的变更才会被其他事务看到。
可重复读（repeatable read）：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化（serializable ）：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

-- 查看当前的隔离级别
show variables like 'transaction_isolation';

2、事务隔离的实现

在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

3、事务的启动方式

MySQL的事务启动方式有以下几种：

显式启动事务语句， begin 或 start transaction。配套的提交语句是commit，回滚语句是rollback。
set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个select语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行commit 或 rollback 语句，或者断开连接。

四、索引

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

1、索引的常见模型

哈希表：适用于只有等值查询的场景，比如Memcached及其他一些NoSQL引擎。
有序数组：只适用于静态存储引擎，比如你要保存的是2017年某个城市的所有人口

信息，这类不会再修改的数据。
N叉树：N叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中

了。

2、InnoDB的索引模型

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引：叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）
非主键索引：叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。

每一个索引在InnoDB里面对应一棵B+树。

假设，我们有一个主键列为ID的表，表中有字段k，并且在k上有索引。

create table T( 
    id int primary key,
    k int not null,
    name varchar(16),
    index (k)
)engine=InnoDB;

主键索引和普通索引的查询区别：

如果语句是select *fromTwhere ID=500，即主键查询方式，则只需要搜索ID这棵B+树；
如果语句是select *fromTwhere k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID

的值为500，再到ID索引树搜索一次。这个过程称为回表。

3、覆盖索引

索引已经“覆盖了”我们的查询需求，我们称为覆盖索引。覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

如果有一个高频请求，要根据市民的身份证号查询他的姓名，则可以建立一个（身份证号，姓名）的联合索引，在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

4、最左前缀原则

B+数这种索引结构，可以利用索引的“最左前缀”来定位记录。

在建立联合索引时，如何安排索引内字段的顺序？

评估标准是索引的复用能力。因为可以支持最左前缀，所以当已经有了（a，b）这个联合索引后，一般就不需要单独在a上建立索引了。如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑的。
如果既有联合查询，又有基于a、b各自的查询呢？查询条件里面只有b的语句，是无法使用（a，b）这个联合索引的，这时候你不得不维护另外一个索引，也就是需要同时维护（a，b）和（b）两个索引，这时候要考虑的原则就是空间了。比如上面这个市民表的情况，name字段是比age字段大的，那我就建议你创建一个（name,age)的联合索引和一个(age)的单字段索引。

5、索引下推

以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是10岁的所有男孩”。那么，SQL语句是这么写的：

select * from tuser where name like '张%' and age=10 and ismale=1;

由于索引前缀原则，这个语句在搜索索引树的时候只能用“张”这个条件。

在MySQL5.6之前，只能从第一个满足条件的记录开始一个个回表，到主键索引上找出数据行，在对比字段值。

而MySQL5.6引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

五、全局锁和表锁

1、全局锁

即对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是Flush tables with read lock（FTWRL）。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。
使用官方自带的逻辑备份工具mysqldump，使用参数-single-transaction，导数据之前就会启动一个事务，来确保拿到一致性视图，而由于MVCC的支持，这个过程中数据是可以正常更新的。
single-transaction方法只适用于所有的表都使用事务引擎的库。

2、表级锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁的语法是lock tables ...read/write，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
另一类表级锁MDL（meta data lock）不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。在MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

3、行锁

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。

行锁就是针对数据表中行记录的锁。这很好理解，比如事务A更新了一行，而这时候事务B也要更新同一行，则必须等事务A的操作完成后才能进行更新。

3.1、两阶段锁

在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这就是两阶段锁协议。

3.2、死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。当出现死锁以后，有两种策略：

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

标签：ename,sal,索引,emp,MySQL,where,select
From： https://www.cnblogs.com/kwanghuee/p/17584583.html

MySQL