MySQL数据库4

最新推荐文章于 2024-09-25 11:06:23 发布

野猪杀手X

最新推荐文章于 2024-09-25 11:06:23 发布

阅读量74

点赞数

分类专栏： Mysql基础文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/dingxiao123/article/details/130312399

版权

Mysql基础专栏收录该内容

11 篇文章 0 订阅

订阅专栏

1. 表查询

1.1 单表查询

1.1.1 单表查询加强

在前面我们讲过 mysql 表的基本查询，但是都是对一张表进行的查询，这在实际的软件开发中，还远远的不够。下面我们讲解的过程中，将使用前面创建三张表（emp，dept，salgrade）为大家演示如何进行多表查询。注意：这三张表是韩老师的 mysql 资料里的雇员系统表。

-- 查询增强
SELECT * FROM emp;

-- 1.使用where子句。
-- 查找1992.1.1后入职的员工？
SELECT * FROM emp
	WHERE hiredate > '1992-01-01';	#显示入职时间在1992年1月1日之后的

-- 2.如何使用like操作符（模糊）。   %：表示0到多个字符 _：表示单个字符
-- 	如何显示首字符为S的员工姓名和工资？
SELECT ename, sal FROM emp
	WHERE ename LIKE 'S%';
-- 	如何显示第三个字符为大写O的所有员工的姓名和工资？
SELECT ename, sal FROM emp
	WHERE ename LIKE '__O%';	#注意：这里O之前有另个下划线，表示前两个字符任意

-- 3.如何显示没有上级的雇员的情况？
SELECT * FROM emp
	WHERE mgr IS NULL;	#注意不能用 mgr = null，即没有 = null 而是 is null
	
-- 4.查询表的结构
DESC emp;

-- 5.使用order by子句
-- 	如何按照工资的从低到高的顺序（升序），显示雇员的信息？
SELECT * FROM emp
	ORDER BY sal;
-- 	按照部门号升序而雇员的工资降序排列，显示雇员信息？
SELECT * FROM emp
	ORDER BY deptno ASC, sal DESC;

1.1.2 分页查询

在这里插入图片描述

-- 分页查询
SELECT * FROM emp;

-- 1.按雇员的字段 empno 号升序取出，每页显示3条记录，请分别显示第1页，第2页，第3页
-- 第一页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 0, 3;
-- 第二页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 3, 3;
-- 第三页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 6, 3;
-- 推导出 第几页 公式为：
SELECT * FROM emp
	ORDER BY empno
	LIMIT 每页显示记录数*(第几页-1), 每页显示记录数

上面的课堂联系：

-- 分页查询 课堂练习
SELECT * FROM emp;

-- 按雇员的empno号降序取出，每页显示5条记录。请分别显示第3页，第5页对应的sql语句
-- 第三页
SELECT * FROM emp
	ORDER BY empno DESC
	LIMIT 10, 5;
-- 第五页
SELECT * FROM emp
	ORDER BY empno DESC
	LIMIT 20, 5;

1.1.3 分组增强

对分组函数和分组子句 group by 的加强。

-- 增强 group by 
SELECT * FROM emp;

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

-- (2)显示雇员总数，以及获得补助的雇员数。
-- 思路：获得补助的雇员数就是 comm 列非 null的
SELECT COUNT(*) AS '雇员总数', COUNT(comm) AS '获得补助的雇员数'
	FROM emp;
-- 扩展：统计没有获得补助的雇员数
-- 扩展方法1
SELECT COUNT(IF(comm IS NULL, 1, NULL))   #注意一下if的用法，这里的1也可以用任何一个非空值替代，比如'abc'
	FROM emp;
-- 扩展方法2
SELECT COUNT(*) - COUNT(comm)
	FROM emp;

-- (3)显示管理者的总人数。
-- 分析：在mgr列非空的就是管理者，但是要注意mgr列有重复
SELECT COUNT(DISTINCT mgr)	#注意distinct在括号里面，是对mgr列去重
	FROM emp;

-- (4)显示雇员工资的最大差额。
-- 思路：max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal)
	FROM emp;

1.1.4 数据分组总结

在这里插入图片描述

-- 应用案例：请统计各个部门（group by）的平均工资（avg），并且平均工资是大于1000的（having），
-- 并且按照平均工资从高到低排序（order by），取出前两行记录（limit）
SELECT deptno, AVG(sal) AS avg_sal
	FROM emp
	GROUP BY deptno
	HAVING avg_sal > 1000
	ORDER BY avg_sal DESC
	LIMIT 0, 2;
	
#注意：这个语法顺序一定不能变

1.2 多表查询

重点，实际开发中用的最多

1.2.1 多表查询入门

多表查询是指基于两个和两个以上的表查询。在实际应用中，查询单个表可能不能满足你的需求（如下面的课堂练习），需要使用到（dept 表和 emp 表，因为雇员表信息在 emp 表中，但是其中只有部门编号 deptno，没有对应的部门名称，而部门名称在表 dept中）

emp 表有13条记录，dept 表有4条记录。下图是直接查询两个表的返回，代码为 SELECT * FROM emp, dept; ：
- 显示的规则：从第一张表 emp 中取出一行，和第二张表 dept 的每一行进行组合，并显示。因此一共有 13*4 = 52 条记录被显示，如下图红框。
- 这样多表查询默认处理返回的结果被称为笛卡尔集。
- 解决这个多表问题的关键就是要写出正确的过滤条件 where，需要程序员进行分许。

在这里插入图片描述

1.2.2 自连接

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

案例：显示公司员工和他上级的名字。注意，看emp表，理解自连接：

在这里插入图片描述

-- 多表查询的 自连接
SELECT * FROM emp;

-- 案例：显示公司员工和他上级的名字。
-- 分析：员工名字和上级的名字都在表emp中。员工和上级是通过emp表的mgr列关联
-- 自连接的特点：1.把同一张表当做两张表使用
--		 2.需要给表取别名以区分两张表，方式：表明 表别名
--		 3.列名不明确，可以指定列的别名，方式：列名 as 列别名
SELECT worker.ename AS '员工表', boss.ename '上级名'
	FROM emp worker, emp boss
	WHERE worker.mgr = boss.empno;
#FROM emp worker, emp boss 实际上就是得到两个表的拼接跟1.2.1一样，只是这里两个表都是emp
#WHERE worker.mgr = boss.empno 筛选条件为worker表的mgr等于boss表的empno

1.3 表的子查询

1.3.1 单行/多行子查询

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

单行子查询：单行子查询是指只返回一行数据的子查询语句
多行子查询：多行子查询指返回多行数据的子查询。使用关键字 in
案例：如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号，但是不含10自己的。

-- 子查询的演示 

-- 单行子查询案例：如何显示与 SMITH 同一部门的所有员工? 
-- 1. 先查询到 SMITH 的部门号
SELECT deptno
	FROM emp
	WHERE ename = 'SMITH';
-- 2. 把上面的 select 语句当做一个子查询来使用（因为该子查询返回一行，因此叫单行子查询）
SELECT *
	FROM emp
	WHERE deptno = (
		SELECT deptno
		FROM emp
		WHERE ename = 'SMITH'	#注意这里没有 ; 号
	);


-- 多行子查询案例：如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号，
-- 但是不包含10号部门自己的雇员
-- 1.查询到10号部门有哪些工作岗位。注意这里涉及到去重，可以把10改为20就能发现有可能有重复
SELECT DISTINCT job
	FROM emp
	WHERE deptno = 10;
-- 2.把上面查询到的结果作为子查询使用，并排除掉10号部门的人
SELECT ename, job, sal, deptno
	FROM emp
	WHERE job IN(	#注意这里没有 = 号
		SELECT DISTINCT job
		FROM emp
		WHERE deptno = 10
	) AND deptno != 10;

1.3.2 子查询作为临时表

在这里插入图片描述

-- 查询 ecshop 中各个类别中，价格最高的商品 的id、类别id、商品名字和价格（注意ecs_shop表格在韩老师提供的mysql资料中）
-- 查询商品表
SELECT goods_id, cat_id, goods_name, shop_price 
	FROM ecs_goods;
-- 先得到各个类别商品的最高价格（max + group by）
SELECT cat_id, MAX(shop_price)
	FROM ecs_goods
	GROUP BY cat_id;
-- 把上面的表当做临时表，与ecs_goods表一起查询（拼接）。
-- 经过筛选条件选出对应的商品名字，条件：cat_id要相等，ecs_goods的价格shop_price要等于该类的最高价格
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.cat_id = ecs_goods.cat_id AND temp.max_price = ecs_goods.shop_price;

1.3.3 多行子查询—all 和 any

在这里插入图片描述

-- all 和 any 的使用

-- 1.显示工资比部门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
		);
		
-- 2.如何显示工资比部门 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
		);

1.3.4 多列子查询

在这里插入图片描述

-- 多列子查询

-- 请思考如何查询与ALLEN的部门和岗位完全相同的所有雇员(并且不含ALLEN本人)
-- (字段1，字段2.….) = (select字段1，字段2 from ...)

-- 分析：1.先得到ALLEN的部门和岗位
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';
	
	
-- 练习：请查询和宋江语文，数学，英语成绩完全相同的学生（使用前面创建的student表）
SELECT * FROM student;
INSERT INTO student 
	VALUES(9, '二狗', 87, 78, 77);

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

1.3.5 练习题

-- 子查询练习题

-- 1.查找每个部门工资高于本部门平均工资的人的资料
-- 这里要用到数据查询的小技巧，把一个子查询当作一个临时表使用
-- 先得到每个部门的平均工资
SELECT deptno, AVG(sal)
	FROM emp
	GROUP BY deptno;
-- 把上面的子查询表当做一个临时表使用
SELECT * FROM emp;
SELECT empno, ename, job, mgr, hiredate, sal, emp.deptno, avg_sal	#显示什么自己决定
	FROM emp, (
		SELECT deptno, AVG(sal) AS avg_sal
		FROM emp
		GROUP BY deptno
	)temp
	WHERE emp.deptno = temp.deptno AND emp.sal > avg_sal;
	
	
-- 2.查找每个部门工资最高的人的详细资料
SELECT empno, ename, job, mgr, hiredate, sal, emp.deptno, max_sal
	FROM emp, (
		SELECT deptno, MAX(sal) AS max_sal
		FROM emp
		GROUP BY deptno
	)temp
	WHERE emp.deptno = temp.deptno AND emp.sal = max_sal;
	
-- 3.查询每个部门的信息（包括：部门名称、编号、地址）和人员数量
SELECT * FROM dept;
-- 先查询每个部门的人员数量
SELECT deptno, COUNT(*) 
	FROM emp
	GROUP BY deptno
-- 把上面的表作为子查询使用
SELECT dname, dept.deptno, loc, num
	FROM dept, (
		SELECT deptno, COUNT(*) AS num
		FROM emp
		GROUP BY deptno
	)temp
	WHERE dept.deptno = temp.deptno
-- 上面的代码选择列还有一种写法。表名.*：表示将该表所有的列都显示出来
-- 在多表查询中，当多个表的列名不重复时，才可以直接写出列名
SELECT dname, loc, temp.*
	FROM dept, (
		SELECT deptno, COUNT(*) AS num
		FROM emp
		GROUP BY deptno
	)temp
	WHERE dept.deptno = temp.deptno

1.4 合并查询

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

union all：该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行。
union：该操作赋与union all相似，但是会自动去掉结果集中重复行

-- 合并查询

SELECT ename, sal, job FROM emp WHERE sal > 2500	#返回5条记录
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'	#返回3条记录
-- 有时候希望把上面两个查询结果进行合并，两种方法union all 和 union

-- union all：就是将两个查询结果合并，不会去重
SELECT ename, sal, job FROM emp WHERE sal > 2500
UNION ALL
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'	#上面这三句一起执行会返回8条记录

-- union：会去重
SELECT ename, sal, job FROM emp WHERE sal > 2500
UNION
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'

2. 表的复制

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

有时，为了对某个 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;	#这里就相当于普通插入时的 VALUES(...)
-- 2.自我复制
INSERT INTO my_tab01
	SELECT * FROM my_tab01;	#这一段代码多运行个五六次，每次my_tab01表中数据翻倍增长
	
SELECT COUNT(*) FROM my_tab01;


-- 面试题：如何删除掉一张表重复记录
-- 1.先创建一张表 my_tab02，并且让这张表有重复的语句
CREATE TABLE my_tab02 LIKE emp;	#这句意思：把emp表的结构（列），复制到my_tab02
DESC my_tab02;

INSERT INTO my_tab02
	SELECT * FROM emp;	#执行两次，为表my_tab02增加重复数据
SELECT * FROM my_tab02;
-- 2.考虑去重问题
-- 思路：(1)先创建一个结构跟my_tab02一样的表my_temp。
CREATE TABLE my_temp LIKE my_tab02;
--       (2)把my_tab02的记录通过 distinct 去重后，插入到my_temp
INSERT INTO my_temp
	SELECT DISTINCT * FROM my_tab02;
-- 	 (3)清除掉my_tab02的所有记录，把my_temp表数据赋值到my_tab02，再删除drop掉my_temp
DELETE FROM my_tab02;	#注意这里不是 DELETE TABLE
INSERT INTO my_tab02
	SELECT * FROM my_temp;
DROP TABLE my_temp;

3. 表的外连接

3.1 提出问题

在这里插入图片描述

-- 外连接

-- 比如：列出部门名称和这些部门的员工名称和工作，
-- 同时要求显示出那些没有员工的部门。
SELECT dname, ename, job
	FROM emp, dept
	WHERE emp.deptno = dept.deptno
	ORDER BY dname	#注意这里是按照dname排序，不是group by分组
-- 注意：上面的代码只会显示3类dname，因为有一个部门没有员工。

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

-- 1.使用左连接(显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号,成绩显示为空)
-- 传统方法
SELECT `name`, stu.id, grade
	FROM stu, exam
	WHERE stu.id = exam.id #只能显示两个表有匹配的结果
-- 改成左外连接，基本语法：select ... from 表1 left join 表2 on 条件【表1就是左表，表2就是右表】
SELECT `name`, stu.id, grade
	FROM stu LEFT JOIN exam
	ON stu.id = exam.id
	
-- 2.使用右外连接（显示所有成绩，如果没有名字匹配，显示空）
-- 即：右边的表（exam）和左表没有匹配的记录，也会把右表的记录显示出来
SELECT `name`, stu.id, grade
	FROM stu RIGHT JOIN exam
	ON stu.id = exam.id
	
-- 练习：列出部门名称和这些部门员工信息（名字、工作），同时 列出哪些没有员工的部门。emp表和dept表
SELECT * FROM emp;
-- 1.使用左外连接实现
SELECT dname, ename, job
	FROM dept LEFT JOIN emp
	ON emp.deptno = dept.deptno
	ORDER BY emp.deptno
-- 2.使用右外连接实现
SELECT dname, ename, job
	FROM emp RIGHT JOIN dept
	ON emp.deptno = dept.deptno
	ORDER BY emp.deptno

4. MySQL 的约束

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

4.1 primary key（主键）

语法：字段名字段类型 primary key

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

-- 主键的使用

-- 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(1, 'tom', 'tom@sohu.com');	#报错，不能id列不能重复

-- 主键使用的细节
-- 1. primary key不能重复而且不能为null。
INSERT INTO t17 VALUES(NULL, 'jack', 'jack@sohu.com'); #报错，主键虽然没有写 not null，但也不能为null

-- 2.一张表最多只能有一个主键，但可以是复合主键（比如 id + name）
CREATE TABLE t18(
	id INT PRIMARY KEY, 
	`name` VARCHAR(32) PRIMARY KEY,
	email VARCHAR(32)
);  #报错，主键最多只有一个
-- 演示复合主键（把id 和 name 做成复合主键）
CREATE TABLE t19(
	id INT, 
	`name` VARCHAR(32),
	email VARCHAR(32),
	PRIMARY KEY (id, `name`) #这里id + name就是复合主键，即id和name不能同时重复
);
INSERT INTO t19 VALUES(1, 'tom', 'tom@sohu.com');
INSERT INTO t19 VALUES(1, 'jack', 'jack@sohu.com'); #ok
INSERT INTO t19 VALUES(1, 'tom', 'xxxxxx@sohu.com'); #id+name 与第一条相同，违反了复合主键，不能插进去
SELECT * FROM t19;

-- 3.主键的指定方式有两种
-- (1)直接在字段名后指定：字段名 primakry key
CREATE TABLE t20(
	id INT, 
	`name` VARCHAR(32) PRIMARY KEY,
	email VARCHAR(32)
);
-- (2)在表定义最后写 primary key(列名)
CREATE TABLE t21(
	id INT, 
	`name` VARCHAR(32),
	email VARCHAR(32),
	PRIMARY KEY (`name`)
);

-- 4.使用desc表名，可以看到primary key的情况.
DESC t20; #查看 t20 表的结果，显示约束的情况
DESC t19;	#注意这里是 id+name 才是主键

-- 5.老师提醒:在实际开发中，每个表往往都会设计一个主键.

4.2 not null（非空）

语法：字段名字段类型 not null

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

4.3 unique（唯一）

语法：字段名字段类型 unique

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

-- unique 的使用

CREATE TABLE t22(
	id INT UNIQUE,	#表示该列不可以重复
	`name` VARCHAR(32),
	email VARCHAR(32)
);

INSERT INTO t22 VALUES(1, 'jack', 'jack@sohu.com');
INSERT INTO t22 VALUES(1, 'tom', 'tom@sohu.com');	#报错，id列不可以重复

-- unique使用细节
-- 1.如果没有指定 not null，则 unique 字段可以有多个 null
INSERT INTO t22 VALUES(NULL, 'tom', 'tom@sohu.com');
INSERT INTO t22 VALUES(NULL, 'jack', 'jack@sohu.com');	#ok
SELECT * FROM t22;
-- 扩展：如果上面定义的字段（列） id INT UNIQUE 并且后面有 NOT NULL，那么id就相当于主键 PRIMARY KEY

-- 2.一张表可以有多个 unique 字段
CREATE TABLE t23(
	id INT UNIQUE,
	`name` VARCHAR(32) UNIQUE,
	email VARCHAR(32)
);
DESC t23;

4.4 foreign key（外键）

语法：foreign key（本表字段名）references 主表名（主键名或 unique 字段名）

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

在这里插入图片描述

对上图：学生表插入第三行 hsp，因为它的 class_id 300 在班级表中没有，因此会插入失败（有外键约束关系时）。并且如果有外键约束，那么学生表中有class_id 为 200 的记录，如果直接删除班级表中 id 为 200的记录，删除会失败，除非先把学生表中 class_id 为200的记录删除，再删除班级表中 id 为 200 的记录，就可以了。

在这里插入图片描述

案例演示：

-- 外键演示

-- 创建 主表（班级表）
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)  #class_id 相当于外键，my_class的字段id 相当于主键
);

-- 测试数据
INSERT INTO my_class VALUES(100, 'java'), (200, 'web'); 
SELECT * FROM my_class;
INSERT INTO my_stu VALUES(1, 'tom', 100);  #ok，因为class_id为100的已经存在了
INSERT INTO my_stu VALUES(2, 'jack', 200); #与上同理
SELECT * FROM my_stu;
INSERT INTO my_stu VALUES(3, 'hsp', 300); #报错，因为300号班级还不存在

INSERT INTO my_stu VALUES(4, 'hsp', NULL); #ok

DELETE FROM my_class
	WHERE id = 100; #报错，不符合删除规则，因为有对应外键约束，除非先删除指向100的学生记录

4.5 check

在这里插入图片描述

如果面试官问到 mysql 有没有 check 约束。回答为：语法上它是支持的，但是不会生效

-- 演示check的使用

-- mysql5.7目前还不支持check ,只做语法校验，但不会生效
-- oracle，sql server 这两个数据库是真的生效

-- 测试
CREATE TABLE t24(
	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 t24 VALUES(1, 'jack', 'mid', 1);	#能加进去，所以上面的check不生效
SELECT * FROM t24;

4.6 商店售货系统表设计

-- 使用约束的课堂练习

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

-- 创建商品表 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 BETWEEN 1.0 AND 9999.99),
	category INT NOT NULL DEFAULT 0,
	provider VARCHAR(64) NOT NULL DEFAULT ''
);
DROP TABLE goods

-- 创建客户表 customer
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,
	sex CHAR(1) CHECK (sex IN('男', '女')),
	#上面这一句还可以用枚举：sex ENUM('男', '女') NOT NULL,
	card_id CHAR(18)
);

-- 创建购买表 purchase
CREATE TABLE purchase(
	order_id INT PRIMARY KEY,
	customer_id INT NOT NULL DEFAULT 0, -- 外键约束，写在后面
	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;

5. 自增长

基本介绍：在某张表中，存在一个id列（整数）。我们希望在添加记录的时候，该列从1开始，自动的增长，即为自增长
基本语法：字段名整型 primary key auto_increment
案例：

在这里插入图片描述

-- 演示自增长的使用

-- 创建表
CREATE TABLE t25(
	id INT PRIMARY KEY AUTO_INCREMENT,	#自增长
	email VARCHAR(32) NOT NULL DEFAULT '',
	`name` VARCHAR(32) NOT NULL DEFAULT '' 
);
SELECT * FROM t25;

-- 测试自增长
INSERT INTO t25 VALUES(NULL, 'jack@qq.com', 'jack');#对应上图3行
INSERT INTO t25 (email, `name`) VALUES('tom@qq.com', 'tom'); #对应上图2行


-- 对应细节第4条：修改默认的自增长开始值
ALTER TABLE t26 AUTO_INCREMENT = 100; #从100开始
CREATE TABLE t26(
	id INT PRIMARY KEY AUTO_INCREMENT,	#自增长
	email VARCHAR(32) NOT NULL DEFAULT '',
	`name` VARCHAR(32) NOT NULL DEFAULT '' 
);
INSERT INTO t26 VALUES(NULL, 'jack@qq.com', 'jack');
SELECT * FROM t26;

-- 对应细节第5 条
INSERT INTO t26 VALUES(666, 'jack@qq.com', 'jack'); #注意：这条记录以后的自增长从667开始

在这里插入图片描述

6. MySQL 的索引

6.1 索引入门

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

举例说明索引的好处：

课程 791 视频中，老韩创建了一个有 800 万条记录的 emp 表，表有两个列为 empno 和 ename（还有其他列），创建用时 7 分钟，创建的表的 .ibd 文件大小为 524 MB（每一个表在磁盘上有两个文件 .ibd（数据文件）和 .frm（结构文件）文件），然后查询了其中一条记录，查询时间用时 4.5 s，查询代码如下：
```
SELECT * 
	FROM EMP
    WHERE empno = 1234567
```
然后老韩为表创建了索引，创建索引代码如下：
```
CREATE INDEX empno_index ON emp (empno);
```
上面的语句意思为：在 emp 表的 empno 列创建索引，empno_index 为索引名称。索引创建用时 21 s，创建完索引后，表的 .idb 文件变为 655 MB（即索引本身也会占用磁盘空间），再次查询（查询代码不变，按照 empno 查），用时 0.003 s。

注意：如果为 empno 创建了索引，但是按照别的列（比如 ename）查询，还是很慢，除非再为该列（ename）创建索引。
讲了一个笑话：有的不良程序员，给别人做项目，给别人之后，别人用起来觉得很慢，说能不能加快点，程序员说十万保证给你加快，结果就只加了一个索引。

6.2 索引的原理

没有索引为什么会慢？因为全表扫描。

使用索引为什么会快？形成一个索引的数据结构，比如二叉树
比如有如下表，左边。如果没有给 id 创建索引并且按照 id 查询某条记录，那么他就会从表的第一行依次遍历下去，即使你要查找的是 id 为 1 的，对比了第一行，找到了之后，还是会继续查找，因为下面可能还有 id 为 1 的，因此查询速度就很慢。但是如果给 id 加了索引，那么索引的存储形式比如为二叉树，要找到 id 记录为 1 的，只需要比较 3 次（5、2、1），就找到了，不用全部遍历。

在这里插入图片描述

索引的代价：
1. 占用额外的磁盘存储空间。
2. 对 dml 语句（数据操作语句，比如update、delete、insert）的执行效率有影响。因为添加或者删除会设计到索引的重排
既然有上面的代价，为什么还需要索引呢？因为实际项目中，select 用得非常多（90%），而 update、delete、insert 语句操作相对时候（10%）。比如今日头条，一个博主有 1000w 个粉丝，他一天发 10 篇文章，相当于用了 10 次 insert 操作，而 1000w 粉丝假如都浏览一遍他每天发的文章，那么 select 操作会用 1000w * 10 次。

6.3 索引的类型

1.主键索引，主键本身就是一种索引，又叫主索引（类型Primary key）

2.唯一索引（UNIQUE）

3.普通索引（INDEX），允许该列的值重复。用的最多，因为上面两种不允许重复，而实际上比如某个表有一个列为 name，名字是允许重复的，比如一个公司两个员工都叫 tom 是可以的，而此时上面两种就不允许了。

4.全文索引（FULLTEXT）[适用于MylSAM，存储引擎]。一般开发，不使用 mysql 自带的全文索引，而是使用：全文搜索 Solr 和 ElasticSearch (ES)

6.4 索引的使用

哪些列适合使用索引：
1. 较频繁的作为查询条件字段应该创建索引。比如员工编号
2. 唯一性太差的字段不适合单独创建索引，即使该字段频繁作为查询条件。比如性别（‘男’，‘女’）。
3. 更新非常频繁的字段不适合创建索引。因此更新频繁的字段每次更新都要重新排列索引，比如下面的登陆次数：
  select * from emp where logincount =1
4. 不会出现在WHERE子句中字段不该创建索引

-- 演示索引的使用

-- 创建表
CREATE TABLE t27(
	id INT,
	`name` VARCHAR(32)
);

-- 查询表是否有索引
SHOW INDEXES FROM t27;


-- 添加索引
-- 1.添加唯一（unique）索引
CREATE UNIQUE INDEX id_index ON t27 (id);
-- 2.1添加普通索引，方式1
CREATE INDEX id_index ON t27(id);
-- 2.2添加普通索引，方式2
ALTER TABLE t27 ADD INDEX id_index (id); #ALTER 修改
-- 如何选择索引类型
-- 如果某列的值是不会重复的，则优先考虑使用unique索引，否则使用普通索引
-- 3.添加主键索引
-- 3.1一个是在定义某个字段（列）的时候在后面添加 primary key，之前学过，不演示
-- 3.2或者用ALTER，如下
CREATE TABLE t28(
	id INT,
	`name` VARCHAR(32)
);
ALTER TABLE t28 ADD PRIMARY KEY (id);	#(id) 前面加 id_index也可以，但是好像语法通过，实际不起作用id_index
SHOW INDEX FROM t28;


-- 删除索引。比如删除 t27 的索引（如果不知道 t27 有哪些索引，可以先用 show index from 表名 查询）
DROP INDEX id_index ON t27;
-- 删除主键索引 t28的
ALTER TABLE t28 DROP PRIMARY KEY; 
#上面语句的解释：alter table t28 修改表t28，丢弃掉索引，但是主键那一列还在，变为了普通列


-- 修改索引：先删除索引，再添加新的索引


-- 查询索引
-- 方式1
SHOW INDEX FROM t27;
-- 方式2
SHOW INDEXES FROM t27;
-- 方式3
SHOW KEYS FROM t27;

注意：上面三种查询索引显示出来的信息如下：

在这里插入图片描述

四个红框信息分别为：表名，不唯一（0表示唯一索引，即不能有重复；1表示非唯一索引，可以重复），索引名称，索引加在哪一列（图中为id列）

6.5 索引练习

在这里插入图片描述

-- 主键索引练习

-- 创建主键索引方式1：定义时指定
CREATE TABLE order1(
	id INT PRIMARY KEY NOT NULL DEFAULT 0,
	goods_name VARCHAR(32) NOT NULL DEFAULT '',
	order_per VARCHAR(32) NOT NULL DEFAULT '',
	nums INT NOT NULL DEFAULT 0
);
SHOW KEYS FROM order1;

-- 创建主键索引方式2：定义完了指定
CREATE TABLE order2(
	id INT NOT NULL DEFAULT 0,
	goods_name VARCHAR(32) NOT NULL DEFAULT '',
	order_per VARCHAR(32) NOT NULL DEFAULT '',
	nums INT NOT NULL DEFAULT 0
);
ALTER TABLE order2 ADD PRIMARY KEY (id);
SHOW INDEX FROM order2;

在这里插入图片描述

-- 唯一索引练习

-- 创建唯一索引方式1：定义时
CREATE TABLE menu1(
	id INT PRIMARY KEY,
	cp_name VARCHAR(32),
	cheaf_name VARCHAR(32),
	id_card CHAR(18) UNIQUE,
	price DOUBLE
);
SHOW INDEX FROM menu1;

-- 创建唯一索引方式2：定义后
CREATE TABLE menu2(
	id INT PRIMARY KEY,
	cp_name VARCHAR(32),
	cheaf_name VARCHAR(32),
	id_card CHAR(18),
	price DOUBLE
);
CREATE UNIQUE INDEX idcard_index ON menu2 (id_card);
SHOW INDEX FROM menu2;

在这里插入图片描述

-- 普通索引练习

-- 创建普通索引方式1
CREATE TABLE sportman1(
	id INT PRIMARY KEY,
	`name` VARCHAR(32),
	specialty VARCHAR(32)
);
CREATE INDEX name_index ON sportman1(`name`);

-- 创建普通索引方式2
CREATE TABLE sportman2(
	id INT PRIMARY KEY,
	`name` VARCHAR(32),
	specialty VARCHAR(32)
);
ALTER TABLE sportman2 ADD INDEX name_index (`name`);

SHOW INDEX FROM sportman1;
SHOW INDEX FROM sportman2;