MySQL 数据库（三）

十六、MySQL 表查询 - 加强
十七、多表查询
十八、MySQL 表子查询
十九、表复制
- 19.1 自我复制数据（蠕虫复制）
二十、合并查询
- 20.1 简单介绍
二十二、MySQL 约束
二十三、自增长
二十四、MySQL 索引

十六、MySQL 表查询 - 加强

16.1 查询加强

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

■ 使用where子句
? 如何查找1992.1.1后入职的员工
■ 如何使用 like 操作符
%：表示0到多个字符 _ ：表示单个字符
? 如何显示首字符为S的员工姓名和工资
? 如何显示第三个字符为大写0的所有员工的姓名和工资
■ 如何显示没有上级的雇员的情况
■ 查询表结构
使用order by子句
? 如何按照工资的从低到高的顺序，显示雇员的信息
? 按照部门号升序而雇员的工资降序排列,显示雇员信息

-- 查询加强
-- ■ 使用 where 子句
-- ?如何查找 1992.1.1 后入职的员工
-- 老师说明： 在 mysql 中,日期类型可以直接比较, 需要注意格式
SELECT * FROM emp
WHERE hiredate > '1992-01-01'
-- ■ 如何使用 like 操作符(模糊)
-- %: 表示 0 到多个任意字符 _: 表示单个任意字符
-- ?如何显示首字符为 S 的员工姓名和工资
SELECT ename, sal FROM emp
WHERE ename LIKE 'S%'
-- ?如何显示第三个字符为大写 O 的所有员工的姓名和工资
SELECT ename, sal FROM emp
WHERE ename LIKE '__O%'
-- ■ 如何显示没有上级的雇员的情况
SELECT * FROM emp
WHERE mgr IS NULL;
-- ■ 查询表结构
DESC emp
-- 使用 order by 子句
-- ?如何按照工资的从低到高的顺序[升序]，显示雇员的信息
SELECT * FROM emp
ORDER BY sal
-- ?按照部门号升序而雇员的工资降序排列 , 显示雇员信息
SELECT * FROM emp
ORDER BY deptno ASC , sal DESC;

16.2 分页查询

按雇员的 id 号升序取出，每页显示3条记录，请分别显示第1页，第2页，第3页
基本语法：select … limit start, rows

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

按雇员的empno号降序取出，每页显示5条记录。请分别显示第3页，第5页对应的sq|语句
第5页: select * from emp order by empno desc limit 20, 5

-- 分页查询
-- 按雇员的 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 每页显示记录数 * (第几页-1) , 每页显示记录数

16.3 使用分组函数和分组子句

(1)显示每种岗位的雇员总数、平均工资。
(2)显示雇员总数，以及获得补助的雇员数。
(3)显示管理者的总人数。
(4)显示雇员工资的最大差额。

-- 增强 group by 的使用
-- (1) 显示每种岗位的雇员总数、平均工资。
SELECT COUNT(*), AVG(sal), job
FROM emp
GROUP BY job;
-- (2) 显示雇员总数，以及获得补助的雇员数。
-- 思路: 获得补助的雇员数 就是 comm 列为非 null, 就是 count(列)，如果该列的值为 null, 是
-- 不会统计 , SQL 非常灵活，需要我们动脑筋.
SELECT COUNT(*), COUNT(comm)
FROM emp
-- 老师的扩展要求：统计没有获得补助的雇员数
SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL))
FROM emp
SELECT COUNT(*), COUNT(*) - COUNT(comm)
FROM emp
-- (3) 显示管理者的总人数。小技巧:尝试写->修改->尝试[正确的]
SELECT COUNT(DISTINCT mgr)
FROM emp;
-- (4) 显示雇员工资的最大差额。
-- 思路： max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal)
FROM emp;
SELECT * FROM e
mp;
select * from dept;
-- 应用案例：请统计各个部门 group by 的平均工资 avg，
-- 并且是大于 1000 的 having，并且按照平均工资从高到低排序， order by
-- 取出前两行记录 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

16.4 数据分组的总结

如果 select 语句同时包含有 group by，having，limit，order by 那么他们的顺序如下：

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

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

十七、多表查询

17.1 简单说明

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

17.2 多表查询练习

? 显示雇员名雇员工资及所在部的名字【笛卡尔集】
老韩小技巧：多表查询的条件不能少于表的个数- 1,否则会出现笛卡尔集
? 如何显示部门号为10的部门名、员工名和工资
? 显示各个员工的姓名，工资，及其工资的级别
学员练习：显示雇员名,雇员工资及所在部门的名字，并按部门排序[降序排].

-- 多表查询
-- ?显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】
/*
老韩分析
1. 雇员名,雇员工资 来自 emp 表
2. 部门的名字 来自 dept 表
3. 需求对 emp 和 dept 查询 ename,sal,dname,deptno
4. 当我们需要指定显示某个表的列是，需要 表.列表
*/
SELECT ename,sal,dname,emp.deptno
FROM emp, dept
WHERE emp.deptno = dept.deptno
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;
-- 老韩小技巧：多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔集
-- ?如何显示部门号为 10 的部门名、员工名和工资
SELECT ename,sal,dname,emp.deptno
FROM emp, dept
WHERE emp.deptno = dept.deptno AND emp.deptno = 10
-- ?显示各个员工的姓名，工资，及其工资的级别
-- 思路 姓名，工资 来自 emp 13
-- 工资级别 salgrade 5
-- 写 sql , 先写一个简单，然后加入过滤条件...
select ename, sal, grade
from emp , salgrade
where sal between losal and hisal;

17.3 自连接

自连接是指在表进行连接查询的时候连接的是它本身

-- 多表查询的 自连接
-- 思考题: 显示公司员工名字和他的上级的名字
-- 老韩分析： 员工名字 在 emp, 上级的名字的名字 emp
-- 员工和上级是通过 emp 表的 mgr 列关联
-- 这里老师小结：
-- 自连接的特点 1. 把同一张表当做两张表使用
-- 2. 需要给表取别名 表名 表别名
-- 3. 列名不明确，可以指定列的别名 列名 as 列的别名
SELECT worker.ename AS '职员名' , boss.ename AS '上级名'
FROM emp worker, emp boss
WHERE worker.mgr = boss.empno;
SELECT * FROM emp;

十八、MySQL 表子查询

18.1 简单介绍

子查询是指嵌入在其它 sql 语句中的 select 语句,也叫嵌套查询

18.2 单行子查询

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

18.3 多行子查询

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

-- 子查询的演示
-- 请思考：如何显示与 SMITH 同一部门的所有员工?
/*
1. 先查询到 SMITH 的部门号得到
2. 把上面的 select 语句当做一个子查询来使用
*/
SELECT deptno
FROM emp
WHERE ename = 'SMITH'
-- 下面的答案.
SELECT *
FROM emp
WHERE deptno = (
SELECT deptno
FROM emp
WHERE ename = 'SMITH'
)
-- 课堂练习:如何查询和部门 10 的工作相同的雇员的
-- 名字、岗位、工资、部门号, 但是不含 10 号部门自己的雇员.
/*
1. 查询到 10 号部门有哪些工作
2. 把上面查询的结果当做子查询使用
*/
select distinct job
from emp
where deptno = 10;
-- 下面语句完整
select ename, job, sal, deptno
from emp
where job in (
SELECT DISTINCT job
FROM emp
WHERE deptno = 10
) and deptno <> 10

小练习：子查询当作临时表使用
查询 ecshop 中各个类别中，价格最高的商品

SELECT goods_id,temp.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.max_price = ecs_goods.shop_price 
    AND temp.cat_id = ecs_goods.cat_id;

18.4 在多行子查询中使用 all 操作符

-- all 和 any 的使用
-- 请思考:显示工资比部门 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
)

18.5 在多行子查询中使用 any 操作符

-- 请思考:如何显示工资比部门 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
);

18.6 多列子查询

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

-- 多列子查询
-- 请思考如何查询与 allen 的部门和岗位完全相同的所有雇员(并且不含 allen 本人)
-- (字段 1， 字段 2 ...) = (select 字段 1，字段 2 from 。。。。)
-- 分析: 1. 得到 smith 的部门和岗位
SELECT deptno , job
FROM emp
WHERE ename = 'ALLEN'
-- 分析: 2 把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配
SELECT *
FROM emp
WHERE (deptno , job) = (
SELECT deptno , job
FROM emp
WHERE ename = 'ALLEN'
) AND ename != 'ALLEN'
-- 请查询 和宋江数学，英语，语文
-- 成绩 完全相同的学生
SELECT *
FROM student
WHERE (math, english, chinese) = (
SELECT math, english, chinese
FROM student
WHERE `name` = '宋江'
)
SELECT * FROM student;

18.7 在 form 子句中使用子查询

请思考:查找每个部门工资高于本部门平均工资的人的资料
这里要用到数据查询的小技巧，把一个子查询当作一个临时表使用
请思考:查找每个部门工资最高的人的详细资料

-- 子查询练习
-- 请思考：查找每个部门工资高于本部门平均工资的人的资料
-- 这里要用到数据查询的小技巧，把一个子查询当作一个临时表使用
-- 1. 先得到每个部门的 部门号和 对应的平均工资
SELECT deptno, AVG(sal) AS avg_sal
FROM emp GROUP BY deptno
-- 2. 把上面的结果当做子查询, 和 emp 进行多表查询
--
SELECT ename, sal, temp.avg_sal, emp.deptno
FROM emp, (
  SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
) temp
WHERE emp.deptno = temp.deptno AND emp.sal > temp.avg_sal
-- 查找每个部门工资最高的人的详细资料
SELECT ename, sal, temp.max_sal, emp.deptno
FROM emp, (
SELECT deptno, MAX(sal) AS max_sal
FROM emp
GROUP BY deptno
) temp
WHERE emp.deptno = temp.deptno AND emp.sal = temp.max_sal
-- 查询每个部门的信息(包括：部门名,编号,地址)和人员数量,我们一起完成。
-- 1. 部门名,编号,地址 来自 dept 表
-- 2. 各个部门的人员数量 -》 构建一个临时表
SELECT COUNT(*), deptno
FROM emp
GROUP BY deptno;
SELECT dname, dept.deptno, loc , tmp.per_num AS '人数'
FROM dept, (
SELECT COUNT(*) AS per_num, deptno
FROM emp
GROUP BY deptno
) tmp
WHERE tmp.deptno = dept.deptno
-- 还有一种写法 表.* 表示将该表所有列都显示出来, 可以简化 sql 语句
-- 在多表查询中，当多个表的列不重复时，才可以直接写列名
SELECT tmp.* , dname, loc
FROM dept, (
SELECT COUNT(*) AS per_num, deptno
FROM emp
GROUP BY deptno
) tmp
WHERE tmp.deptno = dept.deptno

十九、表复制

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

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

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

-- 表的复制
-- 为了对某个 sql 语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据
CREATE TABLE my_tab01
( id INT,
`name` VARCHAR(32),
sal DOUBLE,
job VARCHAR(32),
deptno INT);
DESC my_tab01
SELECT * FROM my_tab01;
-- 演示如何自我复制
-- 1. 先把 emp 表的记录复制到 my_tab01
INSERT INTO my_tab01
(id, `name`, sal, job,deptno)
SELECT empno, ename, sal, job, deptno FROM emp;
-- 2. 自我复制
INSERT INTO my_tab01
SELECT * FROM my_tab01;
SELECT COUNT(*) FROM my_tab01;
-- 如何删除掉一张表重复记录
-- 1. 先创建一张表 my_tab02,
-- 2. 让 my_tab02 有重复的记录
CREATE TABLE my_tab02 LIKE emp; -- 这个语句 把 emp 表的结构(列)，复制到 my_tab02
DESC my_tab02;
INSERT INTO my_tab02
SELECT * FROM emp;
SELECT * FROM my_tab02;
-- 3. 考虑去重 my_tab02 的记录
/*
思路
(1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02 一样
(2) 把 my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
(3) 清除掉 my_tab02 记录
(4) 把 my_tmp 表的记录复制到 my_tab02
(5) drop 掉 临时表 my_tmp
*/
-- (1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02 一样
create table my_tmp like my_tab02
-- (2) 把 my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
insert into my_tmp
select distinct * from my_tab02;
-- (3) 清除掉 my_tab02 记录
delete from my_tab02;
-- (4) 把 my_tmp 表的记录复制到 my_tab02
insert into my_tab02
select * from my_tmp;
-- (5) drop 掉 临时表 my_tmp
drop table my_tmp;
select * from my_tab02;

二十、合并查询

20.1 简单介绍

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

union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行。
select ename,sal,job from emp where sal> 2500 union all
select ename,sal,job from emp where job= ‘MANAGER’;
2. union
该操作赋与 union all 相似，但是会自动去掉结果集中重复行
select ename,sal,job from emp where sal> 2500 union
select ename,sal,job from emp where job= ‘manager’; ```sql — 合并查询

SELECT ename,sal,job FROM emp WHERE sal>2500 — 5 SELECT ename,sal,job FROM emp WHERE job=’MANAGER’ — 3

— union all 就是将两个查询结果合并，不会去重 SELECT ename,sal,job FROM emp WHERE sal>2500 — 5 UNION ALL SELECT ename,sal,job FROM emp WHERE job=’MANAGER’ — 3

— union 就是将两个查询结果合并，会去重 SELECT ename,sal,job FROM emp WHERE sal>2500 — 5 UNION SELECT ename,sal,job FROM emp WHERE job=’MANAGER’ — 3


<a name="H01ad"></a>
### 二十一、MySQL 表外连接
<a name="SdEVv"></a>
#### 21.1  看一个问题
1. 前面我们学习的查询，是利用 where 子句对两张表或者多张表，形成的笛卡尔积进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的，不显示
2. 比如：列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门。
3. 使用我们学习过的多表查询的SQL，看看效果如何？
<a name="zSqGn"></a>
#### 21.2  外连接简单介绍
1. 左外连接(如果左侧的表完全 显示我们就说是左外连接)
1. 右外连接(如果右侧的表完全显示我们就说是右外连接)
使用左连接(显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号,成绩显示为空)<br />select .. from表1 left join表2 on 条件[表1:就是左表表2:就是右表]
使用右外连接(显示所有成绩，如果没有名字匹配，显示空)<br />select .. from表1 right join表2 on条件表1:就是左表表2:就是右表]
```sql
-- 外连接
-- 比如：列出部门名称和这些部门的员工名称和工作，
-- 同时要求 显示出那些没有员工的部门。
-- 使用我们学习过的多表查询的 SQL， 看看效果如何?
SELECT dname, ename, job
FROM emp, dept
WHERE emp.deptno = dept.deptno
ORDER BY dname
SELECT * FROM dept;
SELECT * FROM emp;
-- 创建 stu
/*
id name
1 Jack
2 Tom
3 Kity
4 nono
*/
CREATE TABLE stu (
id INT,
`name` VARCHAR(32));
INSERT INTO stu VALUES(1, 'jack'),(2,'tom'),(3, 'kity'),(4, 'nono');
SELECT * FROM stu;
-- 创建 exam
/*
id grade
1 56
2 76
11 8
*/
CREATE TABLE exam(
id INT,
grade INT);
INSERT INTO exam VALUES(1, 56),(2,76),(11, 8);
SELECT * FROM exam;
-- 使用左连接
-- （显示所有人的成绩，如果没有成绩，也要显示该人的姓名和 id 号,成绩显示为空）
SELECT `name`, stu.id, grade
FROM stu, exam
WHERE stu.id = exam.id;
-- 改成左外连接
SELECT `name`, stu.id, grade
FROM stu LEFT JOIN exam
ON stu.id = exam.id;
-- 使用右外连接（显示所有成绩，如果没有名字匹配，显示空)
-- 即：右边的表(exam) 和左表没有匹配的记录，也会把右表的记录显示出来
SELECT `name`, stu.id, grade
FROM stu RIGHT JOIN exam
ON stu.id = exam.id;

小练习
列出部门名称和这些部门的员工信息(名字和工作)，同时列出那些没有员工的部门名。5min
1.使用左外连接实现
2.使用右外连接实现

-- 列出部门名称和这些部门的员工信息(名字和工作)，
-- 同时列出那些没有员工的部门名。5min
-- 使用左外连接实现
SELECT dname, ename, job
FROM dept LEFT JOIN emp
ON dept.deptno = emp.deptno
-- 使用右外连接实现
SELECT dname, ename, job
FROM emp RIGHT JOIN dept
ON dept.deptno = emp.deptno

老师小结：在实际的开发中，我们绝大多数情况下使用的是之前学过的连接方式

二十二、MySQL 约束

22.1 基本介绍

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

22.2 primary key(主键)-基本使用

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

细节说明：

primary key 不能重复而且不能为 null
一张表最多只能有一个主键，但可以是复合主键
主键的指定方式有两种
1. 直接在字段名后指定：字段名 primary key
2. 在表定义最后写 primary key(列名)
使用 desc 表名，可以看到 primary key 的情况
实际开发中，每个表往往都会设计一个主键

-- 主键使用
-- id name email
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, 'hsp', 'hsp@sohu.com');
SELECT * FROM t17;
-- 主键使用的细节讨论
-- primary key 不能重复而且不能为 null。
INSERT INTO t17
VALUES(NULL, 'hsp', 'hsp@sohu.com');
-- 一张表最多只能有一个主键, 但可以是复合主键(比如 id+name)
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@sohu.com');
INSERT INTO t18
VALUES(1, 'jack', 'jack@sohu.com');
INSERT INTO t18
VALUES(1, 'tom', 'xx@sohu.com'); -- 这里就违反了复合主键
SELECT * FROM t18;
-- 主键的指定方式 有两种
-- 1. 直接在字段名后指定：字段名 primakry 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`) -- 在表定义最后写 primary key(列名)
 );
-- 使用 desc 表名，可以看到 primary key 的情况
DESC t20 -- 查看 t20 表的结果，显示约束的情况
DESC t18

22.3 not null（非空）

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

22.4 unique（唯一）

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

使用细节：

如果没有指定 not null，则 unique 字段可以有多个 null
一张表可以有多个 unique 字段

-- unique 的使用
CREATE TABLE t21
(id INT UNIQUE , -- 表示 id 列是不可以重复的.
`name` VARCHAR(32) ,
email VARCHAR(32)
);
INSERT INTO t21
VALUES(1, 'jack', 'jack@sohu.com');
INSERT INTO t21
VALUES(1, 'tom', 'tom@sohu.com');
-- unqiue 使用细节
-- 1. 如果没有指定 not null , 则 unique 字段可以有多个 null
-- 如果一个列(字段)， 是 unique not null 使用效果类似 primary key
INSERT INTO t21
VALUES(NULL, 'tom', 'tom@sohu.com');
SELECT * FROM t21;
-- 2. 一张表可以有多个 unique 字段
CREATE TABLE t22
(id INT UNIQUE , -- 表示 id 列是不可以重复的.
`name` VARCHAR(32) UNIQUE , -- 表示 name 不可以重复
email VARCHAR(32)
);
DESC t22

22.5 foreign key（外键）

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

细节说明：

外键指向的表的字段，要求是 primary key 或者是 unique
表的类型是 innodb ，这样的表才支持外键
外键字段的类型要和主键字段的类型一致（长度可以不同）
外键的字段的值，必须在主键字段中出现过，或者为 null 【前提是外键字段允许为 null】
一旦建立主外键的关系，数据不能随意删除了

-- 外键演示
-- 创建 主表 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');
INSERT INTO my_class
VALUES(300, 'php');
SELECT * FROM my_class;
INSERT INTO my_stu
VALUES(1, 'tom', 100);
INSERT INTO my_stu
VALUES(2, 'jack', 200);
INSERT INTO my_stu
VALUES(3, 'hsp', 300);
INSERT INTO my_stu
VALUES(4, 'mary', 400); -- 这里会失败...因为 400 班级不存在
INSERT INTO my_stu
VALUES(5, 'king', NULL); -- 可以, 外键 没有写 not null
SELECT * FROM my_class;
-- 一旦建立主外键的关系，数据不能随意删除了
DELETE FROM my_class
WHERE id = 100;

22.6 check

用于强制行数据必须满足的条件，假定在 sal 列上定义了 check 约束，并要求 sal 列值在 1000 ~ 2000 之间如果不在就会提示出错
提示：Oracle 和 SQL Server 均支持 check ，但是在 mysql 5.7 目前还不支持 check ，只做语法校验，但不会生效。【开发时可以尝试使用枚举 ENUM 实现相关功能】

在 MySQL 中实现 check 功能，一般是在程序中控制，或者通过触发器实现。

-- 演示 check 的使用
-- mysql5.7 目前还不支持 check ,只做语法校验，但不会生效
-- 了解
-- 学习 oracle, sql server, 这两个数据库是真的生效.
-- 测试
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', 'mid', 1);
SELECT * FROM t23;

22.7 综合练习

-- 使用约束的课堂练习
CREATE DATABASE shop_db;
-- 现有一个商店的数据库 shop_db，记录客户及其购物情况，由下面三个表组成：
-- 商品 goods（商品号 goods_id，商品名 goods_name，单价 unitprice，商品类别 category，
-- 供应商 provider);
-- 客户 customer（客户号 customer_id,姓名 name,住址 address,电邮 email 性别 sex,身份证 card_Id);
-- 购买 purchase（购买订单号 order_id，客户号 customer_id,商品号 goods_id,购买数量 nums);
-- 1 建表，在定义中要求声明 [进行合理设计]：
-- (1)每个表的主外键；
-- (2)客户的姓名不能为空值；
-- (3)电邮不能够重复;
-- (4)客户的性别[男|女] check 枚举..
-- (5)单价 unitprice 在 1.0 - 9999.99 之间 check
-- 商品 goods
CREATE TABLE goods (
goods_id INT PRIMARY KEY,
goods_name VARCHAR(64) 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 '');
-- 客户 customer（客户号 customer_id,姓名 name,住址 address,电邮 email 性别 sex,
-- 身份证 card_Id);
CREATE TABLE customer(
customer_id CHAR(8) PRIMARY KEY, -- 程序员自己决定
`name` VARCHAR(64) NOT NULL DEFAULT '',
address VARCHAR(64) NOT NULL DEFAULT '',
email VARCHAR(64) UNIQUE NOT NULL,
sex ENUM('男','女') NOT NULL , -- 这里老师使用的枚举类型, 是生效
card_Id CHAR(18));
-- 购买 purchase（购买订单号 order_id，客户号 customer_id,商品号 goods_id,
-- 购买数量 nums);
CREATE TABLE purchase(
order_id INT UNSIGNED PRIMARY KEY,
customer_id CHAR(8) NOT NULL DEFAULT '', -- 外键约束在后
goods_id INT NOT NULL DEFAULT 0 , -- 外键约束在后
nums INT NOT NULL DEFAULT 0,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (goods_id) REFERENCES goods(goods_id));
DESC goods;
DESC customer;
DESC purchase;

二十三、自增长

23.1 基本介绍

如果希望某一列的数据（整数），在新增行数据时不用传入值也可以自动更新，并且数值会自动增长，那么可以使用自增长实现。
语法：

23.2 使用细节

一般来说自增长是和 primary key 配合使用的
自增长也可以单独使用【但是需要配合一个 unique】
自增长修饰的字段为整数型的【虽然小数也可以，但是很少会这样用】
自增长默认是从1开始的，也可以通过以下命令更改
alter table 表名 auto_increment = 新的开始值;
如果你添加数据时，给自增长字段（列）赋了一个值，则后面再添加行数据会以指定的值为基准自增长，如果制定了自增长，一般来说，就按照自增长的规则来添加数据

23.3 案例演示

-- 演示自增长的使用
-- 创建表
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
VALUES(NULL, 'tom@qq.com', 'tom');
INSERT INTO t24
(email, `name`) VALUES('hsp@sohu.com', 'hsp');
SELECT * FROM t24;
-- 修改默认的自增长开始值
ALTER TABLE t25 AUTO_INCREMENT = 100
CREATE TABLE t25
(id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32)NOT NULL DEFAULT '',
`name` VARCHAR(32)NOT NULL DEFAULT '');
INSERT INTO t25
VALUES(NULL, 'mary@qq.com', 'mary');
INSERT INTO t25
VALUES(666, 'hsp@qq.com', 'hsp');
SELECT * FROM t25;

二十四、MySQL 索引

24.1 索引快速入门

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

-- 创建测试数据库 tmp
CREATE DATABASE tmp;
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 COUNT(*) FROM emp;
-- 在没有创建索引时，我们的查询一条记录
SELECT *
FROM emp
WHERE empno = 1234567
-- 使用索引来优化一下， 体验索引的牛
-- 在没有创建索引前 , emp.ibd 文件大小 是 524m
-- 创建索引后 emp.ibd 文件大小 是 655m [索引本身也会占用空间.]
-- 创建 ename 列索引,emp.ibd 文件大小 是 827m
-- empno_index 索引名称
-- ON emp (empno) : 表示在 emp 表的 empno 列创建索引
CREATE INDEX empno_index ON emp (empno)
-- 创建索引后， 查询的速度如何
SELECT *
FROM emp
WHERE empno = 1234578 -- 0.003s 原来是 4.5s
-- 创建索引后，只对创建了索引的列有效
SELECT *
FROM emp
WHERE ename = 'PjDlwy' -- 没有在 ename 创建索引时，时间 4.7s
CREATE INDEX ename_index ON emp (ename) -- 在 ename 上创建索引

24.2 索引的原理

没有索引为什么会慢？因为要全盘扫描
使用索引为什么会快？因为索引会在表的结构基础上形成一个索引的数据结构，比如二叉树

索引的代价：

磁盘占用
对 dml （update，delete，insert）语句的效率影响

24.3 索引的类型

主键索引，主键自动的为主索引（类型 Primary Key）
唯一索引（UNIQUE）
普通索引（INDEX）
全文索引（FULLTEXT）[适用于 MyISAM]

一般开发，不使用 MySQL 自带的全文索引，而是使用：全文搜索 Solr 和 ElasticSearch（ES）

24.4 索引使用

添加索引
create [UNIQUE] index index_name on tbl_name (col_name[(length)] [ASC | DESC], … );
alter table table_name ADD INDEX [index_name] (index_col_name, … );
添加主键（索引）
alter table table_name ADD PRIMARY KEY(列名, … );
删除索引
DROP INDEX index_name ON tbl_name;
alter table table_name drop INDEX index_name;
删除主键索引比较特别
alter table t_b drop primary key;
查询索引（三种方式）
show index(es) from table_name;
show keys fromo table_name;
desc table_name;

-- 演示 mysql 的索引的使用
-- 创建索引
CREATE TABLE t25 (
id INT ,
`name` VARCHAR(32));
-- 查询表是否有索引
SHOW INDEXES FROM t25;
-- 添加索引
-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON t25 (id);
-- 添加普通索引方式 1
CREATE INDEX id_index ON t25 (id);
-- 如何选择
-- 1. 如果某列的值，是不会重复的，则优先考虑使用 unique 索引, 否则使用普通索引
-- 添加普通索引方式 2
ALTER TABLE t25 ADD INDEX id_index (id)
-- 添加主键索引
CREATE TABLE t26 (
id INT ,
`name` VARCHAR(32));
ALTER TABLE t26 ADD PRIMARY KEY (id)
SHOW INDEX FROM t25
-- 删除索引
DROP INDEX id_index ON t25
-- 删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY
-- 修改索引 ， 先删除，在添加新的索引
-- 查询索引
-- 1. 方式
SHOW INDEX FROM t25
-- 2. 方式
SHOW INDEXES FROM t25
-- 3. 方式
SHOW KEYS FROM t25
-- 4 方式
DESC t25

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

较频繁的作为查询条件字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
更新非常频繁的字段不适合创建索引
不会出现在 where 子句中字段不该创建索引

学习参考（致谢）：

B站 @程序员鱼皮 Java学习一条龙
B站 @韩顺平零基础30天学会Java