[MySQL] 零基础学MySQL 02

1. 查询加强

在mysql中，日期类型可以直接比较

1.1 使用where子句

#使用where子句
#查找1991.3.3后入职的员工（hiredate：入职时间） 需要注意格式
SELECT * FROM emp WHERE hiredate>'1991-03-03';

1.2 like操作符（模糊查询）

%：表示0到多个任意字符
_：表示单个任意字符

1.3 如何显示首字母为S的员工的姓名和工资

#如何显示首字母为S的员工的姓名和工资
SELECT `ename`,`sal` FROM emp
	WHERE `ename` LIKE 'S%';

1.4 如何显示第三个字符为字母O的所有员工的姓名和工资

#如何显示第三个字符为字母O的所有员工的姓名和工资
SELECT ename,sal FROM emp
	WHERE ename LIKE '__O%';#两个下划线_

1.5 如何显示没有上级(mgr)的员工的情况

#如何显示没有上级(mgr)的员工的情况
SELECT * FROM emp
	WHERE mgr IS NULL;

1.6 如何查询表的结构

#如何查询表的结构
DESC emp;

1.7 使用order by子句排序

1.7.1 如何按照工资从低到高的顺序显示雇员的信息 默认就是升序（ASC）

#如何按照工资从低到高的顺序显示雇员的信息 默认就是升序（ASC）
SELECT * FROM emp ORDER BY sal;

1.7.2 按照部门号升序而雇员的工资降序排列，显示雇员信息（排序过后再排序）

按照部门号升序而雇员的工资降序排列，显示雇员信息（排序过后再排序）

SELECT * FROM emp ORDER BY deptno ASC;

此时部门号是升序排列的，但工资并不是降序排列的

SELECT * FROM emp ORDER BY deptno ASC,sal DESC;

此时部门号是升序，而工资是降序，完成了排序后再排序。

2. 分页查询

假如一个表有10万行记录，我们不可能直接把整张表全部返回出来。

基本语法：

select ... limit start,rows

表示从start+1行开始取，取出rows行，start从0开始计算

2.1 按雇员的id号升序取出，每页显示3条记录，请分别显示第1页，第2页，第3页

#分页查询
#按雇员的id号升序取出，每页显示3条记录，请分别显示第1页，第2页，第3页

#第1页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 0,3;
#第2页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 3,3;
#第3页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 6,3;

推导一个公式

select * from emp
	order by empno
	limit 每页显示的记录数（rows）*（第几页-1），每页显示的记录数（rows）
#要把结果算出来，不能写表达式

2.2 练习：按雇员的empno号降序取出，每页显示5条记录。请分别显示第3页，第5页对应的sql语句

练习：按雇员的empno号降序取出，每页显示5条记录。请分别显示第3页，第5页对应的sql语句

#page 3	
SELECT * FROM emp
	ORDER BY empno DESC
	LIMIT 10,5;# 5*(3-1)=10
#page 5
SELECT * FROM emp
	ORDER BY empno DESC
	LIMIT 20,5;# 5*(5-1)=20

3. 分组增强

使用分组函数和分组子句group by。

sql语句非常灵活，需要我们开动脑筋。

3.1 显示每种岗位的雇员总数、平均工资

#1 显示每种岗位的雇员总数、平均工资
SELECT COUNT(*),AVG(sal),job FROM emp
	GROUP BY job #按job岗位分组

3.2 显示雇员的总数以及获得补助的雇员数

#2 显示雇员的总数以及获得补助的雇员数
#获得补助的雇员数 就是comm列为非空 count(某列)，如果该列的值为NULL 是不会统计的
SELECT COUNT(*),COUNT(comm) FROM emp;

3.3 统计没有获得补助的雇员数

#统计没有获得补助的雇员数
#如果这一列为空，就返回非空的值(1),代表统计进去了；如果这一列不为空，反而返回一个空值，则不统计进去
SELECT COUNT(*),COUNT(IF(comm IS NULL,1,NULL))FROM emp;
或：
SELECT COUNT(*),COUNT(*)-COUNT(comm) FROM emp;

3.4 显示管理者mgr的总人数

#显示管理者mgr的总人数 
#一个管理者可以管理好几个人 所以需要去重（DISTINCT）
SELECT COUNT(DISTINCT mgr) FROM emp;

3.5 显示雇员工资的最大差额

#显示雇员工资的最大差额
#max(sal)-min(sal)
SELECT MAX(sal)-MIN(sal) FROM emp;

小技巧：尝试写->能通过就最好，不能通过就修改->继续尝试

4. 数据分组的总结

如果select语句同时包含group by，having，limit，order by，那么它们的顺序是group by，having，order by，limit

select column1,column2,column3... from table
	group by column
	having condition
	order by column
	limit start,rows;

4.1 请统计各个部门的平均工资，并且是大于1000的，并且按照平均工资从高到低排序，取出前两行记录

练习：请统计各个部门的平均工资，并且是大于1000的，并且按照平均工资从高到低排序，取出前两行记录
思路：
各个部门：group by deptno
平均工资：AVG(sal)
从高到低排序：DESC
取出前两行记录：LIMIT 0,2

SELECT deptno,AVG(sal) AS avg_sal FROM emp
	GROUP BY deptno
	HAVING avg_sal>1000
	ORDER BY  avg_sal DESC
	LIMIT 0,2;

5. mysql多表查询

介绍
在前面我们讲过mysql表的基本查询，但是都是对一张表进行的查询，这在实际的软件开发中还远远不够。
下面我们讲解的过程中，将用三张表（emp,dept,salgrade）为大家演示如何进行多表查询。

说明
多表查询是指基于两个和两个以上的表查询。在实际应用中，查询单个表可能不能满足你的需求。

多表查询练习

5.1 显示雇员名，雇员工资（emp表）及所在部门的名字（dept表）

显示雇员名，雇员工资（emp表）及所在部门的名字（dept表）

emp表：

dept表：

即在默认情况下：当两个表查询时，规则为：

1. 从第一张表中取出一行，让这一行和第二张表的每一条记录进行拼接，返回这个结果**[含有两张表的所有列]**。
2. 一共返回的记录数：第一张表的行数*第二张表的行数
3. 这样多表查询默认处理返回的结果称为笛卡尔积
4. 故解决这个多表查询的关键就是要写出正确的过滤条件 where
   

SELECT * FROM emp;#13行
SELECT * FROM dept;#4行
SELECT * FROM emp,dept;#52行

SELECT ename,sal,dname FROM emp,dept
	WHERE emp.deptno=dept.deptno;#13行（emp.deptno=dept.deptno：员工表的部门号要和部门表的部门号一致）

另：以下这样会出问题：（因为两个表都有deptno，所以不知道是哪个表的deptno）


改为：

SELECT ename,sal,dname,emp.deptno FROM emp,dept
	WHERE emp.deptno=dept.deptno;

当我们需要指定某个表的列时，需要 表.列名

小技巧：多表查询的过滤条件不能少于表的个数-1，否则会出现笛卡尔集

5.2 如何显示部门号为10的部门名、员工名和工资

练习：

1. 如何显示部门号为10的部门名、员工名和工资

SELECT dname,ename,sal,emp.deptno FROM emp,dept
	WHERE emp.deptno=dept.deptno AND emp.deptno=10;

5.3 显示各个员工的姓名、工资以及工资的级别

2. 显示各个员工的姓名、工资以及工资的级别
   salgrade表：
   
   

SELECT ename,sal,grade 
	FROM emp,salgrade
	WHERE sal BETWEEN losal AND hisal;#判断sal落在哪个工资级别

6. 自连接

自连接是指在同一张表的连接查询（将同一张表看作两张表）

6.1 显示公司员工名字和他的上级的名字

思考：显示公司员工名字和他的上级的名字

SMITH的上级是7902，即FORD

#自连接
#员工姓名在emp，上级的名字也在emp
#员工和上级是通过emp表的mgr列关联的
#给表取别名
SELECT worker.ename AS '职员名',boss.ename AS '上级名' 
	FROM emp worker,emp boss #定义了两张emp表
	WHERE worker.mgr=boss.empno;

自连接的特点：

1. 把同一张表当作两张表使用
2. 一定要给表取别名：表名 表别名（如emp worker、emp boss）
3. 列名不明确时，可以指定列的别名：列名 AS 列别名（如：worker.ename AS ‘职员名’、boss.ename AS ‘上级名’ ）

7. 子查询

什么是子查询？
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

单行子查询
单行子查询是只返回一行数据的子查询语句

多行子查询
多行子查询是返回多行数据的子查询，使用关键字in

7.1 单行子查询

7.1.1 如何显示与SMITH同一部门的所有员工

#如何显示与SMITH同一部门的所有员工

#1.先查询到SMITH的部门编号
SELECT deptno FROM emp
	WHERE ename='SMITH'

#2.把上面的select语句当作一个子查询来使用
SELECT *
	FROM emp
	WHERE deptno=(
	SELECT deptno 
	FROM emp
	WHERE ename='SMITH')

7.2 多行子查询

7.2.1 如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号，但是不含10自己的

#如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号，但是不含10自己的

#1.查询10号部门有哪些工作岗位
SELECT DISTINCT job FROM emp WHERE deptno=10 ; #DISTINCT job：重复工作岗位只取一个

#2.把查询的结果当作子查询来使用
SELECT ename,job,sal,deptno
	FROM emp
	WHERE job IN (
	SELECT DISTINCT job FROM emp WHERE deptno=10 #使用关键字in
	)
	AND deptno !=10;

8. 子查询临时表

我们可以把子查询的结果看作临时表来使用。

8.1 查询ecshop中各个类别中，价格最高的商品。

案例：查询ecshop中各个类别中，价格最高的商品。

把子查询当做一张临时表，可以解决很多很多复杂的查询

SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods;
#先得到各个类别中价格最高的商品 max+group by cat_id
#将这个当作临时表
SELECT cat_id,MAX(shop_price) 
	FROM ecs_goods
	GROUP BY cat_id;

使用temp.cat_id=ecs_goods.cat_id AND temp.max_price=ecs_goods.shop_price将上表和ecshop表对应起来：

SELECT goods_id,ecs_goods.cat_id,goods_name,shop_price
	FROM(
	SELECT cat_id,MAX(shop_price) AS max_price
	FROM ecs_goods
	GROUP BY cat_id
	) temp,ecs_goods
	WHERE temp.cat_id=ecs_goods.cat_id
	AND temp.max_price=ecs_goods.shop_price

结果：

9. 在mysql子查询中使用all和any操作符

9.1 如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

案例：如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

SELECT ename,sal,deptno 
		FROM emp
		WHERE sal>ALL(
			SELECT sal 
				   FROM emp 
				   WHERE deptno=30
					);

或者：

#下面的语句效果和上面的语句是一样的
SELECT ename,sal,deptno 
	   FROM emp
	   WHERE sal>(
			 SELECT MAX(sal) 
			 FROM emp
			 WHERE deptno=30
	)

9.2 如何显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号

案例：如何显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号

SELECT ename,sal,deptno 
	   FROM emp
	   WHERE sal>ANY(
	         SELECT sal 
	         	    FROM emp
			        WHERE deptno=30)
			
#下面的语句效果和上面的语句是一样的
SELECT ename,sal,deptno 
	   FROM emp
	   WHERE sal>(
		     SELECT MIN(sal) 
		     FROM emp
			 WHERE deptno=30
	)

10. 多列子查询

多列子查询是指查询返回多个列数据的子查询语句。

返回的是多列：

（字段1，字段2…）=（select 字段1，字段2 from…）

10.1 如何查询与Smith的部门和岗位完全相同的所有雇员（并且不含Smith本人）

如何查询与Smith的部门和岗位完全相同的所有雇员（并且不含Smith本人）

SELECT * FROM emp
	WHERE (deptno,job)=(
		SELECT deptno,job FROM emp
		WHERE ename='SMITH') AND ename!='SMITH'

10.2 查询与宋江数学、语文、英语成绩完全相同的学生

查询与宋江数学、语文、英语成绩完全相同的学生

SELECT * FROM student
	WHERE (math,chinese,english)=(
		SELECT math,chinese,english FROM student
		WHERE `name`='宋江'
		)

11. 子查询练习

11.1查找每个部门工资高于本部门平均工资的人的资料（把一个子查询当作一个临时表使用）

#1.先得到每个部门的部门号和对应的平均工资（按部门分组）
SELECT deptno,AVG(sal) 
	FROM emp
	GROUP BY deptno

#2.把上面的结果当作一个子查询，和emp表进行多表查询
SELECT * FROM (
	SELECT deptno,AVG(sal) AS avg_sal 
	FROM emp
	GROUP BY deptno) temp,emp
	WHERE temp.deptno=emp.deptno 
		  AND emp.sal>temp.avg_sal

11.2 查找每个部门工资最高的人的详细资料

2. 查找每个部门工资最高的人的详细资料
   
   每个部门的最高工资：
   
   或：

SELECT * FROM(
	SELECT MAX(sal) AS max_sal,deptno 
		FROM emp
		GROUP BY deptno) temp,emp
		WHERE temp.max_sal=emp.sal

每个部门的最高工资：

每个部门工资最高的人的详细资料：

11.3 查询每个部门的信息（包括：部门名，编号，地址）

dept表：

emp表：

3. 查询每个部门的信息（包括：部门名，编号，地址）

#1.部门名、部门编号、地址来自dept表
#2.各个部门的人员数量-->构建一个临时表
SELECT COUNT(*),deptno FROM emp
	GROUP BY deptno

得到临时表：

10号部门有3个人，20号部门有4个人，30号部门有6个人。COUNT(*) AS per_num：

SELECT dname,dept.deptno,loc,temp.per_num AS '人数'
	FROM dept,(
		SELECT COUNT(*) AS per_num,deptno 
			   FROM emp
		       GROUP BY deptno) temp #临时表
	           WHERE dept.deptno=temp.deptno;#临时表temp得到每个部门的人数和部门编号；dept.deptno=temp.deptno：将临时表和dept表链接，得到每个部门的名字、地址

#还有一种写法 表.*（如temp.*）表示将该表所有列都显示出来
SELECT temp.*,dname,loc
	FROM dept,(
	SELECT COUNT(*) AS per_num,deptno FROM emp
	GROUP BY deptno) temp
	WHERE dept.deptno=temp.deptno;

12. 表复制

12.1 自我复制数据（蠕虫复制）

有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据。

CREATE TABLE mytable01(
	id INT,
	`name` VARCHAR(32),
	sal DOUBLE,
	job VARCHAR(32),
	deptno INT);
DESC mytable01
SELECT * FROM mytable01;

#如何自我复制
#1.先把emp表的记录复制到mytable01
INSERT INTO mytable01
	(id,`name`,sal,job,deptno)
	SELECT empno,ename,sal,job,deptno
		FROM emp;
		
#2.自我复制
INSERT INTO mytable01
	SELECT * FROM mytable01;

12.2 如何删除掉一张表的重复记录？

思考题：如何删除掉一张表的重复记录？

思路：

1. 先创建一张临时表temp，该表的结构和mytable02相同
2. 把mytable02的记录通过distinct关键字的处理后，把记录复制到temp表中
3. 删除mytable02的记录
4. 把temp表的记录复制到mytable02中
5. drop掉temp表

CREATE TABLE temp LIKE mytable02;
INSERT INTO temp
	SELECT DISTINCT * FROM mytable02;

此时temp表没有重复记录：

DELETE FROM mytable02;

INSERT INTO mytable02
	SELECT * FROM temp;
	
DROP TABLE temp;

SELECT * FROM mytable02;

这个temp表就像交换函数中的临时变量一样。

13. 合并查询

有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号 union，union all。

13.1 union、union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行。

SELECT ename,sal,job FROM emp WHERE sal>2500

SELECT ename,sal,job FROM emp WHERE job='MANAGER'

13.2 合并两条查询语句的结果

#合并上面两条查询结果
#union all就是将两个查询结果合并，不会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER'

#union会自动去重
SELECT ename,sal,job FROM emp WHERE sal>2500 
UNION
SELECT ename,sal,job FROM emp WHERE job='MANAGER'

14. MySQL表外连接

14.1 列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门

1. 我们前面学习的查询，是利用where子句对两张表或者多张表形成的笛卡尔积进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的就不显示。

2. 比如：列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门。

3. 使用学习过的多表查询语句，看看效果如何。

SELECT dname,ename,job
	FROM emp,dept
	WHERE emp.deptno=dept.deptno
	ORDER BY dname
#显示出的只有三个部门，但实际上我们有四个部门（我们前面学习的查询，是利用where子句对两张表或者多张表形成的笛卡尔积进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的就不显示）

此时需要外连接。

14.2 外连接

1. 左外连接（如果左侧的表和右边的表即使没有匹配的记录，也会完全显示左边的表的记录，我们就说是左外连接）

2. 右外连接（如果右侧的表和左边的表即使没有匹配的记录，也会完全显示右边的表的记录，我们就说是右外连接）
   

#提前准备的表
#创建stu
CREATE TABLE stu(
		id INT,
		`name` VARCHAR(32));
INSERT INTO stu VALUES(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
SELECT * FROM stu;

#创建exam
CREATE TABLE exam(
		id INT,
		grade INT);
INSERT INTO exam VALUES(1,56),(2,76),(11,8);
SELECT * FROM exam;

14.2.1 使用左外连接（显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号，成绩显示为空）

使用左外连接（显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号，成绩显示为空）

#这样只要jack和tom不符合条件
SELECT `name`,stu.id,grade
		FROM stu,exam
		WHERE stu.id=exam.id;

只显示了两个人的成绩，如果要显示4个人的成绩，可以使用左外连接：

#改成左外连接
SELECT `name`,stu.id,grade
	FROM stu LEFT JOIN exam
	ON stu.id=exam.id;	

显示所有人的成绩，如果没有成绩，也要显示该人的姓名和 id 号,成绩显示为空。

左外连接基本语法：
select … from 表1 left join 表2 on 条件
表1就是左表，表2就是右表

14.2.2 使用右外连接（显示所有成绩，如果没有名字匹配，显示空）

使用右外连接（显示所有成绩，如果没有名字匹配，显示空）
即：右边的表(exam) 和左表没有匹配的记录，也会把右表的记录显示出来。

#使用右外连接
#即使右边的表(exam)和左表没有匹配记录，也会把右表的记录显示出来
SELECT `name`,stu.id,grade
	FROM stu RIGHT JOIN exam
	ON stu.id=exam.id;

14.2.3 练习：列出部门名称和这些部门的员工的信息（名字和工作），同时列出那些没有员工的部门名

练习：列出部门名称和这些部门的员工的信息（名字和工作），同时列出那些没有员工的部门名。

1. 使用左外连接实现
2. 使用右外连接实现

#使用左外连接
SELECT dname,ename,job
	FROM dept LEFT JOIN emp
	ON dept.deptno=emp.deptno
	ORDER BY ename;
	
#使用右外连接
SELECT dname,ename,job
	FROM emp RIGHT JOIN dept
	ON dept.deptno=emp.deptno
	ORDER BY ename;

在实际的开发中，我们绝大多数情况下使用的是前面学过的连接：

15. 约束

约束用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括：not null、unique、primary key、foreign key和check五种。

15.1 primary key（主键）

字段名 字段类型 primary key

用于唯一的标识表行的数据，当定义主键约束后， 该列不能重复

#primary key
CREATE TABLE t17
	(id INT PRIMARY KEY,#表示id这列是主键
	`name` VARCHAR(32),
	 email VARCHAR(32));
	 
#主键列的值是不可以重复的
INSERT INTO t17
	VALUES(1,'jack','jack@sohu.com');
INSERT INTO t17
	VALUES(2,'tom','tom@sohu.com');
	
#下面这条语句会报错
INSERT INTO t17
	VALUES(1,'lhq','lhq@sohu.com');#上面已经有一个Id为1了，主键不能重复，故报错
SELECT * FROM t17;

15.2 主键的细节

1. primary key不能重复而且不能为null
   
2. 一张表中最多只能有一个主键，但可以是复合主键

/*错误的
CREATE TABLE t18
	(id INT PRIMARY KEY,#表示id这列是主键
	`name` VARCHAR(32) primary key,
	 email VARCHAR(32));*/

报错：有两个主键。

#复合主键
#当添加数据时，id和name如果同时相同，才违反了主键的约束
CREATE TABLE t18
	(id INT,
	`name` VARCHAR(32),
	 email VARCHAR(32),
	 PRIMARY KEY(id,`name`));#复合主键
INSERT INTO t18 VALUES(1,'tom','tom@qq.com');
INSERT INTO t18 VALUES(1,'jack','jack@qq.com');#ok,id和name如果同时相同，才违反了主键的约束
INSERT INTO t18 VALUES(2,'tom','tom@qq.com');

#下面这条语句是错误的
INSERT INTO t18 VALUES(1,'tom','abc@qq.com');
SELECT * FROM t18;

3. 主键的指定方式有两种
     (1) 直接在字段名后指定：字段名 primary key
     (2) 在表定义最后写primary key(列名)

CREATE TABLE t19
	(id INT,
	`name` VARCHAR(32) PRIMARY KEY,
	 email VARCHAR(32));

CREATE TABLE t20
	(id INT,
	`name` VARCHAR(32),
	 email VARCHAR(32),
	 PRIMARY KEY(`name`));

4. 使用desc 表名，可以看到primary key的情况

DESC t20;

在实际开发中，每个表往往都会设计一个主键。

15.3 not null

如果在列上定义了not null，那么当插入数据时，必须为列提供数据。

字段名 字段类型 not null

15.4 unique（唯一）

当定义了唯一约束后，该列值是不能重复的。

CREATE TABLE t21
	(id INT UNIQUE, #id列值是不能重复的
	`name` VARCHAR(32),
	 email VARCHAR(32));
INSERT INTO t21 VALUES(1,'jack','jack@qq.com');

#下面的语句是错误的
INSERT INTO t21 VALUES(1,'tom','tom@qq.com'); 

15.5 unique使用细节

1. 如果没有指定not null，则unique字段可以有多个null

INSERT INTO t21 VALUES(NULL,'tom','tom@qq.com'); 
#空值可以有多个
INSERT INTO t21 VALUES(NULL,'tom','tom@qq.com'); 
SELECT * FROM t21;

如果一个列（字段）是unique not null，则使用效果类似于primary key

2. 一张表可以有多个unique字段
   

CREATE TABLE t22
	(id INT UNIQUE,
	`name` VARCHAR(32) UNIQUE,
	 email VARCHAR(32));

15.6 foreign key（外键）

用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束。当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null。

学生表（从表），班级表（主表）

1. 把从表的class_id作为外键约束后，如果添加一个学生的班级编号为300，300号在主表里并不存在，那么添加就会失败。
2. 如果外键约束已经形成了，例如学生表的jack的class_id 200和班级表的id 200已经形成外键约束，那么直接删掉班级表的200编号一行会失败。除非先删掉学生表的对应位置，再删掉班级表的对应位置。

15.7 外键使用及细节

FOREIGN KEY (本表字段名) REFERENCES 主表名(主表名或unique字段名)

如果主表的字段不是主键或者unique的，那么就不唯一，如果有两个相同的id，外键就不知道指向哪一个。

所以外键指向另一个表的列时，要求另一个表的列必须是主键或者unique的。

#外键约束
#先创建主表 班级my_class
CREATE TABLE my_class
	(id INT PRIMARY KEY,#班级编号
	`name` VARCHAR(32) NOT NULL DEFAULT '');
	
#创建从表 学生my_stu
CREATE TABLE my_stu
	(id INT PRIMARY KEY,#学生编号
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	 class_id INT,#学生所在班级编号
	 
	-- 下面指定一个外键关系
	FOREIGN KEY (class_id) REFERENCES my_class(id));
	
#测试数据
#添加班级
INSERT INTO my_class VALUES(100,'java'),(200,'web');
SELECT * FROM my_class;

INSERT INTO my_stu VALUES(1,'tom',100);
INSERT INTO my_stu VALUES(2,'jack',200);

#下面的语句会报错 因为300号班级在主表里不存在
INSERT INTO my_stu VALUES(3,'lhq',300);
SELECT * FROM my_stu;

细节说明

1. 外键（class_id）指向的表的字段，要求是主键或者unique
2. 表的类型是innodb，这样的表才支持外键
3. 外键字段的类型要跟主键字段的类型一致（长度可以不同）
4. 外键字段的值，必须在主键字段中出现过，或者为null（前提是外键字段允许为null）
5. 一旦建立主外键的关系，数据就不能随意删除了:（没有外键约束才可以删）
   

15.6 check

用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000-2000之间。如果不在1000-2000之间就会提示出错。

oracle和sql server均支持check，但是mysql5.7目前还不支持check，只做语法校验，但不会生效

#check的使用
CREATE TABLE t23
	(id INT PRIMARY KEY,
	`name` VARCHAR(32),
	 sex VARCHAR(6) CHECK (sex IN('man','woman')),
	 sal DOUBLE CHECK(sal > 1000 AND sal <2000)
	);
	
#添加数据 没有真的生效
INSERT INTO t23
	VALUES(1,'jack','mmid',1);
SELECT * FROM t23;

15.7 商店售货系统表设计案例

现有一个商店的数据库shop_db，记录客户及其购物情况，由下面三个表组成：

1. 商品goods（商品号goods_id，商品名goods_name，单价unitprice，商品类别category，供应商provider）

2. 客户customer（客户号customer_id，姓名name，住址address，电邮email，性别sex，身份证card_id）

3. 购买purchase（购买订单号order_id，客户号customer_id，商品号goods_id，购买数量nums）

4. 建表，在定义中要求声明[进行合理设计]
   （1）每个表的主外键
   （2）客户的姓名不能为空值
   （3）电邮不能够重复
   （4）客户的性别只能是男或女
   （5）单价 unitprice 在1.0-9999.99之间（check）

#shop_db
#创建数据库shop_db
CREATE DATABASE shop_db;

#商品表
CREATE TABLE goods(
	goods_id INT PRIMARY KEY,
	goods_name VARCHAR(32) NOT NULL DEFAULT '',
	unitprice DECIMAL(10,2) NOT NULL DEFAULT 0 
		CHECK(unitprice>= 1.0 AND unitprice<=9999.99),
	category INT NOT NULL DEFAULT 0,
	provider VARCHAR(64) NOT NULL DEFAULT '' 
	);
	
#客户表
CREATE TABLE customer(
	customer_id INT PRIMARY KEY,
	`name` VARCHAR(64) NOT NULL DEFAULT '' ,
	address VARCHAR(64) NOT NULL DEFAULT '' ,
	email VARCHAR(64) UNIQUE NOT NULL DEFAULT '' ,
	sex ENUM('man','woman') NOT NULL ,#这里使用的是枚举类型，是生效的
	card_id CHAR(18) UNIQUE
	);
	
#购买表(订单表) 从表
CREATE TABLE purchase(
	order_id INT UNSIGNED PRIMARY KEY,
	customer_id CHAR(8) NOT NULL,
	#外键约束在后
	goods_id INT NOT NULL DEFAULT 0,#默认是0
	#外键约束在后
	nums INT NOT NULL DEFAULT 0,
	
	#外键约束
	FOREIGN KEY (customer_id) REFERENCES customer(customer_id),#purchase表中的customer_id要指向customer表的customer_id
	FOREIGN KEY (goods_id) REFERENCES goods(goods_id)#purchase表中的goods_id要指向goods表的goods_id
	);

goods表：

customer表

purchase表

16. 自增长

一个问题：在某张表中，存在一个id列（整数），我们希望在添加记录的时候，该列从1开始，自动地增长，该如何处理呢？

字段名 整形 primary key auto_increment

添加自增长字段的方式
如果字段1是自增长的，给字段1赋值要写null，或者不给字段1赋值。

insert into xxx (字段1,字段2......) values(null,'值'......);
insert into xxx (字段2,字段3......) values('值','值'......);
insert into xxx values(null,'值1','值2'......);

#自增长
CREATE TABLE t24
	(id INT PRIMARY KEY AUTO_INCREMENT,
	email VARCHAR(32) NOT NULL DEFAULT '' ,
	`name` VARCHAR(32) NOT NULL DEFAULT ''
	)
DESC t24;

#测试自增长的使用
INSERT INTO t24 (id,email,`name`) 
		VALUES(NULL,'jack@qq.com','jack');
INSERT INTO t24 
		VALUES(NULL,'tom@qq.com','tom');
		
#如果不是自增长的话，下面这个语法是错误的
INSERT INTO t24 (email,`name`) 
		VALUES('lhq@qq.com','lhq');
		
SELECT * FROM t24;

16.1 自增长使用细节：

1. 一般来说，自增长和主键配合使用（id INT PRIMARY KEY AUTO_INCREMENT,）
2. 自增长也可以单独使用，但是需要配合一个unique
3. 自增长修饰的字段为整数类型的（虽然小数也可以但非常少这样使用）：
   
4. 自增长默认从1开始，你也可以通过如下命令修改，如从100开始：

ALTER TABLE t25 AUTO_INCREMENT=100;

5. 如果添加数据时，给自增长字段（列）指定的有值，则以指定的值为准。如果指定了自增长。
   一般来说，就按照自增长的规则来添加数据。

INSERT INTO t25 VALUES(666,'lhq@qq.com','lhq');

6. 如果设置了自增长，删除掉某条记录后，自增长不会自动填补，会在删除数据的id上加一
   

17. MySQL索引

说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，查询速度就可能提高百倍千倍。

这里我们举例说明索引的好处【构建海量表 有8000000条数】

我们已经提前准备好了一个数据库和海量表。

CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;

#创建表EMP雇员
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
) ;

#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
);

#测试数据
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

delimiter $$

#创建一个函数，名字 rand_string，可以随机返回我指定的个数字符串
create function rand_string(n INT)
returns varchar(255) #该函数会返回一个字符串
begin
#定义了一个变量 chars_str， 类型  varchar(100)
#默认给 chars_str 初始值   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
 declare chars_str varchar(100) default
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
 declare return_str varchar(255) default '';
 declare i int default 0; 
 while i < n do
    # concat 函数 : 连接函数mysql函数
   set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
   set i = i + 1;
   end while;
  return return_str;
  end $$


 #这里我们又自定了一个函数,返回一个随机的部门号
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$

 #创建一个存储过程， 可以添加雇员
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit =0 把autocommit设置成0
 #autocommit = 0 含义: 不要自动提交
 set autocommit = 0; #默认不提交sql语句
 repeat
 set i = i + 1;
 #通过前面写的函数随机产生字符串和部门编号，然后加入到emp表
 insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
  until i = max_num
 end repeat;
 #commit整体提交所有sql语句，提高效率
   commit;
 end $$

 #添加8000000数据
call insert_emp(100001,8000000)$$

#命令结束符，再重新设置为;
delimiter ;

#在没有创建索引时，我们查询上表一条记录
SELECT * 
	FROM emp
	WHERE empno=1234567

在没有创建索引前，emp.ibd文件大小是524,288KB

#使用索引来优化一下，体验索引有多牛
#empno_index索引名称
#ON emp (empno) 表示在emp表的empno列创建索引
CREATE INDEX empno_index ON emp (empno)

创建索引后，emp.ibd文件大小是655,360KB

得出结论，创建的索引本身也会占用磁盘空间。

这里联想到“以空间换时间”的算法思想。

SELECT * 
	FROM emp
	WHERE empno=1234568

提升速度非常显著！

我们在ename上没有创建索引，那么查询ename时依然很慢

#创建索引后，只对创建了索引的列有效
SELECT * 
		FROM emp 
		WHERE ename='axJxCT';

又得创建ename的索引：

18. 索引机制

18.1 索引的原理

18.2 索引的代价

1. 磁盘占用会变大
2. 对update delete insert语句的效率会有影响

以删除为例，删除一条数据会导致整个索引数据结构的改变，需要重新更新调整数据结构，所以对速度会有影响。

但在项目中，select操作比update、delete、insert多得多。

19. 创建索引

19.1 索引的类型

1. 主键索引 如果某个列是主键，那它自然就是主索引 (primary key)

CREATE TABLE t1(
	id int primary key,#主键，同时也是索引，称为主键索引
	name varchar(32));

2. 唯一索引 (unique)

CREATE TABLE t2(
	id int unique,#id是唯一的，同时也是索引，称为unique索引
	name varchar(32));

3. 普通索引 (index)

4. 全文索引 (fulltext) [适用于MyISAM]
   一般开发不使用Mysql自带的全文索引，而是使用全文搜索 Solr 和 ElasticSearch （ES）

19.2 创建索引

#演示MySQL索引的使用
#创建索引
CREATE TABLE t26(
	id INT,
	`name` VARCHAR(32));

19.3 查询表是否有索引

#查询表是否有索引
SHOW INDEXES FROM t26;

19.4 添加唯一索引

#添加唯一索引
#id_index：索引名称
#ON t26：在表t26创建索引
#id:在id这列创建索引
CREATE UNIQUE INDEX id_index ON t26 (id);

19.5 添加普通索引

#添加普通索引
create index id_index on t26(id);

#如何选择唯一索引和普通索引
#1.如果某列的值是不会重复的，则优先考虑unique索引，否则使用普通索引

#添加普通索引的另一个方法
#alter table：修改表
-- alter table t26 add index id_index (id)

19.6 添加主键索引

#添加主键索引
#1.建表时指定primary key

#2.alter table t26 add primary key(id);
ALTER TABLE t26 ADD PRIMARY KEY(id);

20. 删除索引、查询索引

#删除索引
DROP INDEX id_index ON t26;
SHOW INDEX FROM t26;#查询索引

#删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY;

#修改索引就是先删除索引，再添加新的索引

#查询索引
#1.
SHOW INDEX FROM t26;
#2.
SHOW INDEXES FROM t26;
#3.
SHOW KEYS FROM t26;
#4.
DESC t26;

21 练习：建立主键索引

21.1 要求：创建一张订单表order（id，商品名，订购人，数量）要求id号为主键，请使用两种方式来创建主键。

CREATE TABLE order1(
	id INT PRIMARY KEY,
	goods_name VARCHAR(32),
	order_people VARCHAR(32),
	nums INT);
SHOW INDEXES FROM order1;

CREATE TABLE order2(
	id INT,
	goods_name VARCHAR(32),
	order_people VARCHAR(32),
	nums INT);
ALTER TABLE order2 ADD PRIMARY KEY(id);
SHOW INDEXES FROM order1;

22 练习：建立唯一索引

22.1 要求：创建一张特价菜谱表menu（id，菜谱名，厨师，点餐人身份证，价格）要求id号为主键，点餐人身份证是unique，请使用两种方式来创建unique

CREATE TABLE menu1(
	id INT PRIMARY KEY,
	food_name VARCHAR(32),
	cook_name VARCHAR(32),
	customer_idcard VARCHAR(32) UNIQUE,
	price INT);
SHOW INDEXES FROM menu1;

CREATE TABLE menu2(
	id INT PRIMARY KEY,
	food_name VARCHAR(32),
	cook_name VARCHAR(32),
	customer_idcard VARCHAR(32),
	price INT);
CREATE UNIQUE INDEX customer_idcard_index ON menu2(customer_idcard);
SHOW INDEXES FROM menu2;

23 练习：创建普通索引

23.1 要求：创建一张运动员表sportman（id，名字，特长）要求id号为主键，名字为普通索引。请使用三种方式来创建索引。

CREATE TABLE sportman1(
	id INT PRIMARY KEY,
	`name` VARCHAR(32),
	special_skill VARCHAR(32));
CREATE INDEX name_index ON sportman1(`name`);
SHOW INDEXES FROM sportman1;

CREATE TABLE sportman2(
	id INT PRIMARY KEY,
	`name` VARCHAR(32),
	special_skill VARCHAR(32));
ALTER TABLE sportman2 ADD INDEX name_index(`name`); 
SHOW INDEXES FROM sportman2;

CREATE TABLE sportman3(
	id INT,
	`name` VARCHAR(32),
	special_skill VARCHAR(32));
ALTER TABLE sportman3 ADD PRIMARY KEY (id);
CREATE INDEX name_index ON sportman3(`name`);
SHOW INDEXES FROM sportman3;

24 创建索引的规则

小结：在哪些列上适合使用索引

1. 较频繁的作为查询条件的字段应该创建索引

select * from emp where empno=1

2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
   例如：性别
   
3. 更新非常频繁的字段不适合创建索引

select * from emp where logincount=1

4. 不会出现在WHERE子句中的字段不该被创建索引

25. MySQL事务

25.1 什么是事务

事务用于保证数据的一致性，它由一组相关的dml语句（增删改语句）组成，该组的dml语句要么全部成功，要么全部失败。例如：转账就要用到事务来处理，用以保证数据的一致性。

我们要杜绝这种不安全情况。故：

22.2 事务和锁

当执行事务操作时（dml语句），mysql会在表上加锁，防止其他用户修改表的数据，这对用户来讲是非常重要的。

22.3 mysql数据库控制台事务的几个重要操作

1. start transaction 开始一个事务
2. savepoint 设置保存点
3. rollback to 回退事务
4. rollback 回退全部事务
5. commit 提交事务，所有的操作生效，不能回退
   
   这人容易让人联想到游戏里面的存档。

23. 事务操作

23.1 保存点

保存点(savepoint)是事务中的点，用于取消部分事务。当结束事务(commit)时，会自动地删除该事务所定义的所有保存点。当执行回退事务时，通过指定保存点可以回退到指定的点。

#1.创建一张测试表
CREATE TABLE t27(
	id INT,
	`name` VARCHAR(32));
	
#2.开启一个事务
START TRANSACTION;

#3.设置保存点
SAVEPOINT a;

#4.执行dml操作
INSERT INTO t27 VALUES(100,'tom');
SELECT * FROM t27;

SAVEPOINT b;
#执行dml操作
INSERT INTO t27 VALUES(200,'jack');
SELECT * FROM t27;

#回退到b
ROLLBACK TO b;
SELECT * FROM t27;

#回退到a
ROLLBACK TO a;
SELECT * FROM t27;

如果直接回退到a点，就会把从a点到回退时间点的所有保存点删除掉，也就是说，回退到a点后就不能再回到b点了。

23.2 提交事务（后悔药没有了）

使用commit语句可以提交事务。当执行了commit语句后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务后，其它会话[其他连接]将可以查看到事务变化后的新数据。（所有数据就正式生效）

23.3 事务注意事项

1. 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
   
   
2. 如果开始一个事务，你没有创建保存点，你可以执行rollbak，默认就是回退到事务开始的状态
   
   
3. 你也可以在这个事务中（还没有提交时）创建多个保存点，比如：savepoint aaa，执行dml，savepoint bbb
   
4. 可以在事务没有提交前选择回退到哪个保存点
5. mysql的事务机制需要在innodb的存储引擎下使用，myisam不能使用
6. 开始一个事务 start transaction; 也可以写成 set autocommit=off;

24. 事务隔离级别

24.1 事务隔离级别介绍

1. 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。
   
2. 如果不考虑隔离级别，可能会引发如下问题：脏读、不可重复读、幻读

24.2 脏读(dirty read)

脏读(dirty read)：有两个连接操纵一张表，当一个连接在其事务中读取另一个连接在其事务中尚未提交的改变(update、insert、delete)时，产生脏读；

24.3 不可重复读(nonrepeatable read)

不可重复读(nonrepeatable read)：同一查询在同一事务中多次进行，由于其它提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读；

24.4 幻读(phantom read)

幻读(phantom read)：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。

我们创建一个account表来进行操作，以便理解上面的内容。

25. 隔离级别演示

25.1 Read uncommitted 读未提交

25.1.1脏读

#演示mysql的事务隔离级别
#1.开启两个mysql控制台(mysql -u root -pxzx)
#2.查看当前mysql的隔离级别
SELECT @@tx_isolation;

-- mysql> SELECT @@tx_isolation;
# 控制台1和控制台2都为可重复读
-- +-----------------+
-- | @@tx_isolation  |
-- +-----------------+
-- | REPEATABLE-READ |
-- +-----------------+

#3.把控制台2的隔离级别设置为 Read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Query OK, 0 rows affected (0.00 sec)
-- 
-- mysql> select @@tx_isolation;#查看当前mysql的隔离级别
# 读未提交
-- +------------------+
-- | @@tx_isolation   |
-- +------------------+
-- | READ-UNCOMMITTED |
-- +------------------+

#4.在两个控制台都启动事务
START TRANSACTION;

#5.在控制台1和控制台2使用my_db02数据库并创建表account
USE my_db02;
CREATE TABLE `account`(
	id INT,
	`name` VARCHAR(32),
	money INT);
	
#6.在控制台1执行插入语句，并且未提交
INSERT INTO ACCOUNT VALUES(100,'tom',1000);

#此时 在隔离级别为READ-UNCOMMITTED的控制台2居然能使用select语句查询到控制台1中未提交的改变

#控制台1
SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

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

mysql> use my_db02;
Database changed
mysql> CREATE TABLE `account`(
    -> id INT,
    -> `name` VARCHAR(32),
    -> money INT);
Query OK, 0 rows affected (0.03 sec)

mysql> select * from account;
Empty set (0.00 sec)

mysql> insert into account values(100,'tom',1000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;
+------+------+-------+
| id   | name | money |
+------+------+-------+
|  100 | tom  |  1000 |
+------+------+-------+
1 row in set (0.00 sec)

#控制台2
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

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

mysql> use my_db02;
Database changed
mysql> select * from account;
Empty set (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

mysql> select * from account;
+------+------+-------+
| id   | name | money |
+------+------+-------+
|  100 | tom  |  1000 |
+------+------+-------+
1 row in set (0.00 sec)

控制台1并没有提交，但控制台2居然看到了控制台1添加的数据。这就叫脏读。

25.1.2 不可重复读和幻读

#控制台1
mysql> update account set money=800 where id=100;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into account values(200,'jack',2000);
Query OK, 1 row affected (0.00 sec)

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

#控制台2
mysql> select * from account;
+------+------+-------+
| id   | name | money |
+------+------+-------+
|  100 | tom  |   800 |
|  200 | jack |  2000 |
+------+------+-------+
2 rows in set (0.00 sec)

#读未提交演示结束，提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

控制台1的update和insert操作一旦提交了，控制台2并没有提交，但是控制台2却看到了控制台1的提交。
这就叫做不可重复读和幻读。控制台2的事务因为控制台1的事务的提交而被影响了。

假设有事务A、B、C、D。事务A提交了，而事务B还没提交。只要事务B没提交，那么事务B只希望读取到的是从自己开始事务的时间点开始的，而不被其他事务影响的数据。否则的话，A对表t1操作一番，C对表t1操作一番，D对表t1操作一番，那么事务B对表的读取就完全乱掉了。
（事务B在如下代码创建了事务B，本来按计划事务B是要读取id：100| name：tom | money：1000 这个表的，结果事务A把money改为800了，而且在表中又插入了一组数据，使得事务B无法读取到想要的那个表的数据，乱套了 ）

mysql> use my_db02;
Database changed
mysql> select * from account;
Empty set (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

mysql> select * from account;
+------+------+-------+
| id   | name | money |
+------+------+-------+
|  100 | tom  |  1000 |
+------+------+-------+
1 row in set (0.00 sec)

而控制台2的事务B会受控制台A的影响，是因为控制台2的隔离级别设置为读未提交级别，无法避免不可重复读和幻读的情况。

25.2 Read committed 读已提交

25.2.1 脏读（控制台2会读取到控制台1尚未提交的事务）

#在控制台1开启事务
#在控制台2将隔离级别由Read uncommitted改为READ committed
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- mysql> SELECT @@tx_isolation;
-- +----------------+
-- | @@tx_isolation |
-- +----------------+
-- | READ-COMMITTED |
-- +----------------+
-- 1 row in set (0.00 sec)

#在控制台2开启事务
START TRANSACTION;

#在控制台1添加一条数据
INSERT INTO ACCOUNT VALUES(300,'scott',8000);

#在控制台1查询肯定是能看到新添加的记录
#但因为隔离级别发生了变化，在控制台2查询时是看不到新添加的记录的
-- mysql> select * from account;
-- +------+------+-------+
-- | id   | name | money |
-- +------+------+-------+
-- |  100 | tom  |   800 |
-- |  200 | jack |  2000 |
-- +------+------+-------+
-- 2 rows in set (0.00 sec)
#说明读已提交的隔离级别不会出现脏读了

#控制台1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(300,'scott',8000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;
+------+-------+-------+
| id   | name  | money |
+------+-------+-------+
|  100 | tom   |   800 |
|  200 | jack  |  2000 |
|  300 | scott |  8000 |
+------+-------+-------+
3 rows in set (0.00 sec)

#控制台2
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ committed;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

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

mysql> select * from account;
+------+------+-------+
| id   | name | money |
+------+------+-------+
|  100 | tom  |   800 |
|  200 | jack |  2000 |
+------+------+-------+
2 rows in set (0.00 sec)

25.2.2 不可重复读（控制台2能读取到控制台1进行修改或删除操作且已提交的事务）和幻读（控制台2能读取到控制台1进行插入操作且已提交的事务）

#但是不可重复读和幻读仍然会出现
#在控制台1修改数据并提交后，在控制台2进行查询(此时控制台2并没有提交)，发现又能读到数据了
#控制台1
UPDATE ACCOUNT SET money=1800 WHERE id=200;
COMMIT;
#控制台2
-- mysql> select * from account;
-- +------+-------+-------+
-- | id   | name  | money |
-- +------+-------+-------+
-- |  100 | tom   |   800 |
-- |  200 | jack  |  1800 |
-- |  300 | scott |  8000 |
-- +------+-------+-------+
-- 3 rows in set (0.00 sec)
#说明在读已提交的隔离级别下已经出现了不可重复读和幻读

#控制台1
mysql> update account set money=1800 where id=200;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+------+-------+-------+
| id   | name  | money |
+------+-------+-------+
|  100 | tom   |   800 |
|  200 | jack  |  1800 |
|  300 | scott |  8000 |
+------+-------+-------+
3 rows in set (0.00 sec)

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

#控制台2
mysql> select * from account;
+------+-------+-------+
| id   | name  | money |
+------+-------+-------+
|  100 | tom   |   800 |
|  200 | jack  |  1800 |
|  300 | scott |  8000 |
+------+-------+-------+
3 rows in set (0.00 sec)

25.3 Repeatable read 可重复读

可重复读不会出现脏读、不可重复读和幻读了。

#控制台1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(400,'milan',6000);
Query OK, 1 row affected (0.00 sec)

mysql> update account set money=100 where id=300;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+------+-------+-------+
| id   | name  | money |
+------+-------+-------+
|  100 | tom   |   800 |
|  200 | jack  |  1800 |
|  300 | scott |   100 |
|  400 | milan |  6000 |
+------+-------+-------+
4 rows in set (0.00 sec)

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

#控制台2
mysql> SET SESSION TRANSACTION ISOLATION LEVEL Repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

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

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

mysql> use my_db02;
Database changed
mysql> select * from account;
+------+-------+-------+
| id   | name  | money |
+------+-------+-------+
|  100 | tom   |   800 |
|  200 | jack  |  1800 |
|  300 | scott |  8000 |
+------+-------+-------+
3 rows in set (0.00 sec)

mysql> select * from account;
+------+-------+-------+
| id   | name  | money |
+------+-------+-------+
|  100 | tom   |   800 |
|  200 | jack  |  1800 |
|  300 | scott |  8000 |
+------+-------+-------+
3 rows in set (0.00 sec)

25.4 Serializable 可串行化

隔离级别最强，不会出现脏读、不可重复读和幻读，而且会加锁（如果有表正在被操作，没有提交，他会卡住不操作，等表操作完后再进行操作）。

#控制台1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(500,'terry',80000);
Query OK, 1 row affected (0.00 sec)

mysql> update account set money=900 where id=300;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#控制台2
mysql> SET SESSION TRANSACTION ISOLATION LEVEL Serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)

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

mysql> use my_db02;
Database changed

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

mysql> select * from account;

这个时候有意思的事情发生了：控制台2会卡在那里不动，光标一直闪烁。

假设有事务A和事务B，事务A对表进行操作，但没有提交。
此时事务B也想对表进行操作，事务B会检测事务A有没有提交，如果没有提交，事务B会进行等待。

在事务A提交的一瞬间，事务B的查询立马就可以查询到。

26. 设置隔离

1. 查看当前会话隔离级别

SELECT @@tx_isolation;

2. 查看系统当前隔离级别

SELECT @@global.tx_isolation;

3. 设置当前会话隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别;

4. 设置系统当前隔离级别

SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别;

5. mysql默认的隔离级别是repeatable read，一般情况下，没有特殊要求，没有必要修改（因为该级别可以满足大部分项目需求）

如果需要全局修改，参考下图。

27. MySQL事务ACID

27.1 事务的acid特性

1. 原子性(Atomicity)
   原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
   
2. 一致性(Consistency)
   事务必须使数据库从一个一致性状态变换到另一个一致性状态
3. 隔离性(Isolation)（如可重复读隔离级别）
   事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离
   
4. 持久性(Durability)
   持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

27.2 事务的练习

1. 登录mysql控制客户端A，创建表Dog(id,name)，开始一个事务，添加两条记录。
2. 登录mysql控制客户端B，开始一个事务，设置为读未提交。
3. A客户端修改Dog一条记录，不要提交。看看B客户端是否看到变化，说明什么问题？
4. 登录mysql客户端C，开始一个事务，设置为读已提交，这时A客户端修改一条记录，不要提交，看看C客户端是否看到变化，说明什么问题？

#客户端A
mysql> use my_db02;
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> create table dog(id int,`name` varchar(32));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into dog values(1,'pupu');
Query OK, 1 row affected (0.01 sec)

mysql> insert into dog values(2,'haha');
Query OK, 1 row affected (0.00 sec)



mysql> update dog set `name`='dd' where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0



mysql> update dog set `name`='bb' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#客户端B
mysql> use my_db02;
Database changed

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

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

mysql> select * from dog;
+------+------+
| id   | name |
+------+------+
|    1 | pupu |
|    2 | haha |
+------+------+
2 rows in set (0.00 sec)



mysql> select * from dog;
+------+------+
| id   | name |
+------+------+
|    1 | pupu |
|    2 | dd   |
+------+------+
2 rows in set (0.00 sec)

#客户端C
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> use my_db02;
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dog;
+------+------+
| id   | name |
+------+------+
|    1 | bb   |
|    2 | dd   |
+------+------+
2 rows in set (0.00 sec)

28 MySQL表类型和存储引擎

28.1 基本介绍

1. MySQL的表类型由存储引擎（Storage Engines）决定，主要包括MyISAM、innoDB 、Memory等
2. MySQL数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoDB
3. 这六种又分为两类，一类是“事务安全型”（transaction-safe）[支持事务]，比如InnoDB；其余都属于第二类，称为“非事务安全型”（non-transaction-safe）[不支持事务][myisam和memory]

28.2 查看所有存储引擎

这里重点介绍三种：MyISAM、InnoDB、Memory

1. MyISAM不支持事务，也不支持外键，但其访问速度快，对事务完整性没有要求；
2. InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引；
3. Memory存储引擎使用存在内存中的内容来创建表。每个Memory表只实际对应一个磁盘文件。Memory类型的表访问速度非常快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是mysql服务一旦关闭，表中的数据就会丢失掉（也是因为放在内存中），表的结构还在。

28.3 三种存储引擎表使用案例

28.3.1 innoDB存储引擎

#innoDB存储引擎是前面一直使用过的，不再多说
#支持事务，支持外键，支持行级锁

28.3.2 myisam存储引擎

#myisam存储引擎
#1.添加速度快2.不支持外键和事务3.表级锁
CREATE TABLE t28(
	id INT,
	`name` VARCHAR(32)) ENGINE MYISAM;
	
#开启事务测试下是否真的不支持事务
START TRANSACTION;
SAVEPOINT t1;
INSERT INTO t28 VALUES(1,'jack');
SELECT * FROM t28;
ROLLBACK TO t1;
#查一下表，发现根本没回滚成功
SELECT * FROM t28;

28.3.3 memory存储引擎

#memory存储引擎
#1.数据存储在内存中(关闭了mysql服务，数据就丢失了，表结构还在)
#2.执行速度很快（没有IO读写）3.默认支持索引(hash表)
CREATE TABLE t29(
	id INT,
	`name` VARCHAR(32)) ENGINE MEMORY;
INSERT INTO t29 
			VALUES(1,'tom'),(2,'jack'),(3,'lhq');
SELECT * FROM t29;

表结构还在：

表中数据没了：

28.4 如何选择表的存储引擎

1. 如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择，速度快；
2. 如果需要支持事务，选择InnoDB；
3. Memory存储引擎就是将数据存储在内存中，由于没有I/O的等待，速度极快。但是由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。（经典用法：用户的在线状态）

如果一个用户的状态发生变化，我们就去修改user表，而用户的状态是频繁变化，操作就会非常频繁。我们把用户的在线状态用memory存储引擎存放。用户的在线状态不是很重要的数据，没了就没了。

28.5 修改存储引擎

ALTER TABLE `表名` ENGINE=存储引擎;

29. 视图(view)

看一个需求

emp表的列信息很多，有些信息是个人重要信息（比如sal，comm，mgr，hiredate），如果我们希望某个用户只能查询emp表的（empno，ename，job和deptno）信息，有什么办法？

其中一个方案就是视图。

29.1 基本概念

1. 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表（基表）
2. 视图和基表关系的示意图
   
   视图可以修改基表的数据：
   
   

29.2 视图的基本使用

1. create view 视图名 as select语句
2. alter view 视图名 as select语句（重新定义一个视图）
3. show create view 视图名（显式当初用怎样的指令创建视图的）
4. drop view 视图名1，视图2

29.2.1 创建一个视图emp_view01，只能查询emp表的（empno,ename,job和deptno）信息

#视图的使用
#创建一个视图emp_view01，只能查询emp表的（empno,ename,job和deptno）信息

#创建视图
CREATE VIEW emp_view01
	   AS 
	   SELECT empno,ename,job,deptno FROM emp;
	   
#查看视图
DESC emp_view01;
SELECT * FROM emp_view01;

#查看创建视图的指令
SHOW CREATE VIEW emp_view01;
#删除视图
DROP VIEW emp_view01;

29.3 视图细节讨论

1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件，并没有数据文件（基表有视图结构文件，也有数据文件）（形式：视图名.frm）
2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图（insert update delete）

#修改视图会影响到基表
UPDATE emp_view01
	SET job='manager'
	WHERE empno=7369;
#查询基表
SELECT * FROM emp;

修改基表会影响视图，这是毋庸置疑的。

3. 视图中可以再使用视图

#视图中可以再使用视图
#比如从emp_view01视图中选出empno和ename做出新的视图
CREATE VIEW emp_view02
		AS 
		SELECT empno,ename FROM emp_view01;
SELECT * FROM emp_view02;
#emp_view02还是和基表emp有映射关系

29.4 视图应用实例

29.4.1 视图最佳实践

1. 安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
2. 性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接JOIN。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
3. 灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。
   

29.4.2 练习：针对emp，dept和salgrade三张表，创建一个视图emp_view03，可以显示雇员编号，雇员名，雇员部门名称和薪水级别

练习：针对emp，dept和salgrade三张表，创建一个视图emp_view03，可以显示雇员编号，雇员名，雇员部门名称和薪水级别。

即，使用三张表构建一个视图
emp表：

dept表：

salgrade表：

/*
分析：使用三表联合查询得到结果
*/
#将3张表整合，获取empno,ename,dname,grade构成的表
SELECT empno,ename,dname,grade
	FROM emp,dept,salgrade
	WHERE emp.deptno=dept.deptno AND 
	(sal BETWEEN losal AND hisal);
	
#将得到的结果构建成视图
CREATE VIEW emp_view03
	AS SELECT  empno,ename,dname,grade
		FROM emp,dept,salgrade
		WHERE emp.deptno=dept.deptno AND 
		(sal BETWEEN losal AND hisal);
SELECT * FROM emp_view03;

30. MySQL用户管理

30.1 MySQL用户

mysql中的用户，都存储在系统数据库mysql中的user表中。

其中user表的重要字段说明：

1. host：允许登录的“位置”，localhost表示该用户只允许本机登录，也可以指定ip地址，比如：192.168.1.100
2. user：用户名
3. authentication_string：密码，是通过mysql的password()函数加密之后的密码

30.2 创建用户，同时指定密码

create user ‘用户名’@‘允许登录位置’ identified by ‘密码’;

30.3 删除用户

drop user ‘用户名’@‘允许登录位置’;

#mysql用户的管理
#原因：当我们做项目开发时，可以根据不同的开发人员，赋给他相应的mysql操作权限
#所以mysql数据库管理人员(root)，根据需要创建不同的用户，赋予相应的权限供开发人员使用

#1.创建新的用户
#（1）'lhq_edu' @ 'localhost' 表示用户的完整信息 'lhq_edu'用户名 'localhost'登录的ip
#（2）123456 密码 但是存放到mysql.user表时，是password('123456')加密后的密码。
CREATE USER 'lhq_edu'@'localhost' IDENTIFIED BY '123456';
SELECT `host`,`user`,`authentication_string`
		FROM mysql.user;
	
#2.删除用户
DROP USER 'lhq_edu'@'localhost';

30.4 用新用户登录sqly：

我们还可以将root用户能操作的数据库和数据对象授权给其他用户。

30.5 用户修改密码

修改自己的密码：set password=password(‘密码’);

修改他人的密码（需要有修改用户密码的权限）：set password for ‘用户名’@‘登录位置’=password(‘密码’);

30.6 MySQL权限管理

30.6.1 给用户授权

基本语法

grant 权限列表 on 库.对象名 to '用户名'@'登录位置' [identified by '密码']

说明：

1. 权限列表，多个权限用逗号分开
   grant select on…
   grant select,delete,create on…
   grant all on… //表示赋予该用户在该对象上的所有权限
2. 特别说明
   .：代表本系统中所有数据库的所有数据对象（表，视图，存储过程等）
   库.*：表示某个数据库中的所有数据对象（表，视图，存储过程等）
   
3. identified by可以省略，也可以写出
   （1）如果用户存在，就是修改该用户的密码
   （2）如果该用户不存在，就是创建该用户

30.6.2 回收用户权限

基本语法

revoke 权限列表 on 库.对象名 from '用户名'@'登录位置';

权限生效指令

#如果权限没有生效，可以执行下面命令
FLUSH PRIVILEGES;

30.6.3 用户权限管理练习

1. 创建一个用户（名字拼音），密码为123，并且只可以从本地登录，不让远程登录mysql
2. 使用root用户创建库和表[testdb下的news表]
3. 给用户分配查看news表和添加数据的权限
4. 测试看看用户是否只有这几个权限
5. 使用root用户修改密码为abc
6. 重新登录
7. 回收权限
8. 使用root用户删除你的用户

#root用户
#创建用户
CREATE USER 'bigdog'@'localhost' IDENTIFIED BY '123';

#创建测试数据库和测试表
CREATE DATABASE testdb;
CREATE TABLE news(
	id INT,
	content VARCHAR(32));
	
INSERT INTO news VALUES(100,'fake news');
SELECT * FROM news;

#授予SELECT,INSERT权限
GRANT SELECT,INSERT 
		ON testdb.news 
		TO 'bigdog'@'localhost';
		
#修改密码
SET PASSWORD FOR 'bigdog'@'localhost' =PASSWORD('abc'); 

#回收权限
REVOKE SELECT,INSERT ON testdb.news FROM 'bigdog'@'localhost';

#删除用户
DROP USER 'bigdog'@'localhost';

#创建的用户
#可以查看
SELECT * FROM news;

#可以添加
INSERT INTO news VALUES(200,'true news');

#不能修改
UPDATE news SET content='abcd' WHERE id=100;
#UPDATE command denied to user 'bigdog'@'localhost' for table 'news'

30.7 细节说明

1. 在创建用户的时候，如果不指定host，则为%，%表示所有ip都有连接权限

create user xxx;

2. 也可以这样指定

create user ‘xxx’@‘192.168.1.%’ #表示xxx用户在192.168.1.*的ip可以登录mysql

3. 在删除用户的时候，如果host不是%，需要明确指定’用户’@‘host值’

31. 作业

31.1