阶段三-MySQL-Day02~Day03

一、DQL查询表中数据

数据查询语言（Data Query Language，DQL）：DQL主要用于数据的查询，其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。

DQL是实际开发过程中使用最多的。

1. 数据准备

# 创建员工表 
表名 emp 
表中字段： 
eid 员工id，int 
ename 姓名，varchar(20) 
sex 性别，char(1) 
salary 薪资，double(7,1)
hire_date 入职时间，date
dept_name 部门名称，varchar(20)

# 创建lsh02数据库
create database lsh02;

# 选择lsh02
use lsh02;

# 创建员工表
create table emp(
	eid int,
	ename varchar(20),
	sex char(1),
	salary double(7,1),
	hire_date date,
	dept_name varchar(20)
);

# 添加测试数据 
insert into emp VALUES(1,'孙悟空','男',7200,'2013-02-04','教学部'); 
insert into emp VALUES(2,'猪八戒','男',3600,'2010-12-02','教学部');
insert into emp VALUES(3,'唐僧','男',9000,'2008-08-08','教学部'); 
insert into emp VALUES(4,'白骨精','女',5000,'2015-10-07','市场部'); 
insert into emp VALUES(5,'蜘蛛精','女',5000,'2011-03-14','市场部'); 
insert into emp VALUES(6,'玉兔精','女',200,'2000-03-14','市场部'); 
insert into emp VALUES(7,'林黛玉','女',10000,'2019-10-07','财务部');
insert into emp VALUES(8,'黄蓉','女',3500,'2011-09-14','财务部'); 
insert into emp VALUES(9,'吴承恩','男',20000,'2000-03-14',null); 
insert into emp VALUES(10,'孙悟饭','男', 10,'2020-03-14','财务部');
insert into emp VALUES(11,'兔八哥','女', 300,'2010-03-14','财务部');
insert into emp VALUES(12,'Tom','男', null,'2010-03-14','财务部');

2. 简单查询

查询不会对数据库中的数据进行修改, 只是一种显示数据的方式。

-- 简单查询
-- 1.查询emp中所有的数据
select * from emp;
-- 2.查询emp表中所有的记录, 只显示eid和ename字段
select eid,ename from emp;
-- 3.查询所有的员工信息, 将字段名显示为中文
-- as可以省略
select
	eid as'编号',
	ename as '姓名',
	sex as '性别',
	salary as '薪资',
	hire_date as '入职日期',
	dept_name as '部门名称'
from emp;
-- 4.查询有多少个部门
select dept_name from emp;
-- 需要去重 null也会保存
select distinct dept_name from emp;
-- 5.将所有的员工薪资加1000显示
select *,salary+1000 from emp;

3. 条件查询

如果查询语句中没有设置条件, 就会查询所有的行信息
在实际应用中, 通常会指定查询的条件, 对记录进行过滤

3.1 比较运算符

运算符	说明
> < <= >= = <> !=	大于小于小于等于大于等于等于不等于
between...and...	显示在某一区间的值
in(集合)	集合表示多个值，使用逗号分隔，例如name in (悟空,八戒); in中的每个数据都会作为一次条件，只要满足条件就会显示
like '%张%'	模糊查询
is null	查询某一列为null的值，注意，不能写 = null

3.2 逻辑运算符

运算符	说明
and &&	多个条件同时成立
or \|\|	多个条件任一成立
not	取反

-- 条件查询
-- 精确查询
#1 查询员工姓名为黄蓉的员工信息 
select * from emp where ename="黄蓉";

#2 查询薪水价格为5000的员工信息
select * from emp where salary=5000;

#3 查询薪水价格不是5000的所有员工信息 
select * from emp where salary!=5000;
select * from emp where salary<>5000;

#4 查询薪水价格大于6000元的所有员工信息 
select * from emp where salary>6000;

#5 查询薪水价格在5000到10000之间的员工信息 
select * from emp where salary >= 5000 and salary <= 10000; 
select * from emp where salary between 5000
and 10000;

#6 查询薪水价格是3600或7200或者20000的员工信息
select * from emp where salary=3600 or salary=7200 or
salary=20000 ;
select * from emp where salary in(3600,7200,20000);

案例2:

模糊查询

模糊查询，通配符

通配符	说明
%	表示匹配任意多个字符
_	表示匹配任意一个字符

-- 模糊查询
-- like
-- %:0~n个
-- _:1个
#1 查询含有'八'字的员工信息
select * from emp where ename like "%八%";

#2 查询以'孙'字开头的员工信息 
select * from emp where ename like '孙%';

#3 查询第二个字为'兔'的员工信息 
select * from emp where ename like '_兔%';
-- null值不能使用=,而是使用is和is not
#4 查询没有部门的员工信息
select * from emp where dept_name is null;

#5 查询有部门的员工信息
select * from emp where dept_name is not null;

4. 排序

通过order by子语句, 可以将查询出的结果进行排序(排序只是显示效果, 并不会影响真实的数据）。

-- 语法格式：
select 字段名 from 表名 [where 条件] order by 字段名[asc | desc];
-- asc  升序(默认)
-- desc 降序

4.1 单列排序

只按照某一个字段进行排序。

案例:

查询所有的员工信息, 使用salary进行排序。

-- 升序排序(默认 asc)
select * from emp order by salary;

-- 降序排序(desc)
select * from emp order by salary desc;

4.2 组合排序

同时对多个字段进行排序, 如果第一个字段相同, 就按照第二个字段排序, 以此类推。

案例:

查询所有的员工信息, 使用salary升序排列,salary相同按照入职日期降序排序。

-- 组合排序
select * from emp order by salary, hire_date desc;

5. 函数

MySQL中提供了大量函数来简化用户对数据库的操作，比如字符串的处理、日期的运算、数值的运算等等。使用函数可以大大提高SELECT语句操作数据库的能力，同时也给数据的转换和处理提供了方便。

函数只是对查询结果中的数据进行处理，不会改变数据的值。

单行函数:

单行函数是指对每一条记录的值进行计算，并得到相应的计算结果，然后返回给用户，也就是说，每条记录作为一个输入参数，经过函数计算得到每条记录的计算结果。

常用的单行函数主要包括字符串函数、数值函数、日期与时间函数、流程函数以及其他函数。

使用单行函数, 是对行中字段的操作, 操作多少行, 返回多少行数据。

多行函数:

我们之前做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用多行函数查询是纵向查询，它是对某一列的值进行计算，然后返回一个单一的值。

多行函数会忽略null空值。
多行函数也称为分组函数, 聚合函数。

使用多行函数，是对某一列的值进行计算，然后返回一个单一的值

5.1 单行函数

5.1.1 字符函数

大小写处理函数

函数	描述	实例
LOWER(s)\|LCASE(s)	将字符串 s 转换为小写	将字符串 OLDLU转换为小写：`SELECT LOWER("OLDLU"); -- oldlu`
UPPER(s)\|UCASE(s)	将字符串s转换为大写	将字符串 oldlu转换为大写：`SELECT UPPER("oldlu"); -- OLDLU`

字符处理函数

函数	描述	实例
LENGTH(s)	返回字符串 s 的长度	返回字符串oldlu的字节数`SELECT LENGTH("oldlu"); -- 5;`
CONCAT(s1,s2...sn)	字符串 s1,s2 等多个字符串合并为一个字符串	合并多个字符串`SELECT CONCAT("sxt ", "teacher ", "oldlu"); -- sxt teacher oldlu;`
LPAD(s1,len,s2)	在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len	将字符串 x 填充到 oldlu字符串的开始处：`SELECT LPAD('oldlu',8,'x'); -- xxxoldlu`
LTRIM(s)	去掉字符串 s 开始处的空格	去掉字符串 oldlu开始处的空格：`SELECT LTRIM(" oldlu") ;-- oldlu`
REPLACE(s,s1,s2)	将字符串 s2 替代字符串 s 中的字符串 s1	将字符串 oldlu 中的字符 o 替换为字符 O：`SELECT REPLACE('oldlu','o','O'); -- Oldlu`
REVERSE(s)	将字符串s的顺序反过来	将字符串 abc 的顺序反过来：`SELECT REVERSE('abc'); -- cba`
RPAD(s1,len,s2)	在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len	将字符串 xx填充到 oldlu字符串的结尾处：`SELECT RPAD('oldlu',8,'x'); -- oldluxxx`
RTRIM(s)	去掉字符串 s 结尾处的空格	去掉字符串 oldlu 的末尾空格：`SELECT RTRIM("oldlu "); -- oldlu`
SUBSTR(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 OLDLU中的第 2 个位置截取 3个字符：`SELECT SUBSTR("OLDLU", 2, 3); -- LDL`
SUBSTRING(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 OLDLU中的第 2 个位置截取 3个字符：`SELECT SUBSTRING("OLDLU", 2, 3); --LDL`
TRIM(s)	去掉字符串 s 开始和结尾处的空格	去掉字符串 oldlu 的首尾空格：`SELECT TRIM(' oldlu ');-- oldlu`

-- 字符处理函数
/*
	utf-8字符集,汉字占三个字节,字母占1个字节
	length返回字符串的字节数
*/
select length(ename),ename from emp;

-- concat拼接字符串
select concat("姓名",ename) from emp;
select concat(ename,"姓名") from emp;

-- LPAD,RPAD填充字符串
-- 左边
select lpad(ename,5,'a') from emp;
-- 右边
select rpad(ename,5,'a') from emp;

-- LTRIM,RTRIM去除空格
-- 去除左边的空格
select ltrim(ename) from emp;
-- 去除右边的空格
select rtrim(ename) from emp;
-- 去除两边的空格
select trim(ename) from emp;

-- REPLACE替换
select replace('Old','O','o') from dual;

-- reverse反转
select reverse('Old');

-- substring()和substr()一样
-- 截取角标从1开始
select substr(ename,1,2) from emp;
select substring(ename,1,2) from emp;

5.1.2 数值函数

函数	描述	实例
ABS(num)	返回num的绝对值	`SELECT ABS(-1) -- 返回1`
CEIL(num)	返回大于num的最小整数（向上取整）	SELECT CEIL(1.5) -- 返回2
FLOOR(num)	返回小于num的最大整数（向下取整）	`SELECT FLOOR(1.5) -- 返回1`
MOD(num1, num2)	返回num1/num2的余数（取模）	`SELECT MOD(5,2) -- 1`
PI()	返回圆周率的值	`SELECT MOD(5,2) -- 1`
POW(num,n)/POWER(num, n)	返回num的n次方	SELECT POW(2,3) -- 8
RAND(num)	返回0~1之间的随机数	SELECT RAND() --0.93099315644334
ROUND(num, n)	返回x四舍五入后的值，该值保留到小数点后n位	SELECT ROUND(1.23456,3) --1.235
TRUNCATE(num, n)	返回num被舍去至小数点后n位的值（与 ROUND 最大的区别是不会进行四舍五入）	`SELECT TRUNCATE(1.23456,3) -- 1.234`

MySQL提供了一张虚拟表，该表名为“dual”，是MySQL为了满足用“SELECT ••• from•••”的习惯而增设的一张虚拟表。在使用dual表时，如果没有where子句，则可以省略“from dual”, 没有实际意义, 查询的字段不属于任何表的时候, 就可以使用dual这张虚拟表。

-- 数值函数
select 
abs(-1), #返回绝对值
ceil(1.4), #向上取整
floor(1.6), #向下取整
mod(1,2), #取余
pi(), #圆周率
pow(2,2), #2的2次方=power()
rand(4), #0-1的随机数
round(2.565,2), # 保留2.564小数2位,四舍五入
truncate(2.565, 2), # 保留2.564小数2位,直接舍去
sqrt(25) #开根号
;
#这里默认使用的是虚表dual,可以省略不写

5.1.3 日期与时间函数

函数	描述	实例
CURDATE()	返回当前日期	`SELECT CURDATE();` -- 2022-07-20
CURTIME()	返回当前时间	`SELECT CURTIME(); -> 19:59:02`
NOW()	返回当前日期和时间	`SELECT NOW() -> 2022-07-20 20:57:43`
SYSDATE()	返回该函数执行时的日期和时间	`SELECT SYSDATE() -> 2022-07-20 20:57:43`
DAYOFYEAR(date)	返回日期date为一年中的第几天	`SELECT DAYOFYEAR('2011-11-11 11:11:11') ->315`
WEEK(date)/WEEKOFYEAR(date)	返回日期date为一年中的第几周	`SELECT DAYOFWEEK('2011-11-11 11:11:11') ->6`
DATEDIFF(date1, date2)	返回起始日期date1与结束日期date2之间的间隔天数	`SELECT DATEDIFF('2001-01-01','2001-02-02') -> -32`
DATE()	从日期或日期时间表达式中提取日期值	`SELECT DATE("2017-06-15"); -> 2017-06-15`
DAY(d)	返回日期值 d 的日期部分	`SELECT DAY("2017-06-15"); -> 15`
MONTH(d)	返回日期d中的月份值，1 到 12	`SELECT MONTH('2011-11-11 11:11:11') ->11`
YEAR(d)	返回年份	`SELECT YEAR("2017-06-15"); -> 2017`

-- 日期与时间函数
select
curdate(), #返回当前日期
curtime(), #返回当前时间
now(), #返回当前日期和时间
sysdate(), #返回该函数执行时的日期和时间
dayofyear('1990-1-1'), #返回日期为一年中的第几天
week('1990-1-1'),
weekofyear('1990-1-1'),
#返回日期为一年中的第几周
datediff('1990-1-1','19990-1-2'),
#返回两个日期之间的差值
sleep(2)
;

流程控制函数

间隔类型	描述
IF(condition, t, f)	如果条件condition为真，则返回t，否则返回f
IFNULL(value1, value2)	如果value1不为null，则返回value1，否则返回value2
NULLIF(value1, value2)	如果value1等于value2，则返回null，否则返回value1
CASE WHEN [condition1] THEN result1 [WHEN [condition2] THEN result2 ...] [ELSE result] END	如果条件condition1为真，则返回result1，···，否则返回result

# 流程空值函数
#1 查询emp表所有数据, 薪资 >= 10000 高工资  其他 低工资
# if(条件,语句一,语句二)
# 条件成立语句一,否则语句二
select *,if(salary >= 10000,
'高工资','低工资') 
from emp;

#2 查询emp表所有数据, 计算出员工的年薪 薪资*12 加年终奖(每人30k)空值算30K
select salary'月薪',
salary*12+30000 '年薪'
from emp;#空值还是空值
-- 优化
select salary'月薪',
ifnull(salary,0)
*12+30000 '年薪'
from emp;

#3 查询emp表所有数据, 薪资 >=3000 加把劲  >=5000 加油哦  >=9000 坚持住 >= 15000 优秀  其他 不及格
select eid, ename, salary, 
	case
		when salary >= 15000 then '优秀'
		when salary >= 9000 then '坚持住'
		when salary >= 5000 then '加油哦'
		when salary >= 3000 then '加把劲'
		else '努力奋斗吧骚年'
	end
from emp;

5.1.5 其它函数

函数	描述
DATABASE()	返回当前数据库名
VERSION()	返回当前MySQL的版本号
USER()	返回当前登录的用户名
INET_ATON(IP)	返回IP地址的数字表示
INET_NTOA	返回数字代表的IP地址
PASSWORD(str)	实现对字符串str的加密操作
FORMAT(num, n)	实现对数字num的格式化操作，保留n位小数
CONVERT(data, type)	实现将数据data转换成type类型的操作

-- 其他函数
select
database(), #返回当前数据库名
user(), #返回登陆的用户名
version(),#返回mysql版本号
inet_aton("192.168.10.1"),
#返回ip的十进制数字
inet_ntoa(3232238081);
#返回数字代表的IP地址

5.2 多行函数

多行函数会忽略null空值。
多行函数也称为分组函数, 聚合函数。

多行函数	作用
count(字段)	统计指定列不为null的记录行数
sum(字段)	计算指定列的数值和
max(字段)	计算指定列的最大值
min(字段)	计算指定列的最小值
avg(字段)	计算指定列的平均值

-- 多行函数
-- 多行函数会忽略空值
#1 查询员工的总数
-- 使用某个字段查询，聚合函数会忽略null, 需要注意为null的字段,一般是主键
select count(eid) from emp;
-- 所有字段匹配查询
select count(*) from emp; 
-- 增加一列1
select 1 from emp;
-- 可以用这个1来算总数
select count(1) from emp;

#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值, 显示为总薪水, ... 
select
sum(salary) '总薪水',
max(salary) '最高薪水',
min(salary) '最低薪水',
avg(salary) '平均薪水'
from emp
;

#3 查询薪水大于4000员工的个数 
select count(1) from emp
where salary > 4000;

#4 查询部门为'教学部'的所有员工的个数 
select count(1) from emp
where dept_name = '教学部';

#5 查询部门为'市场部'所有员工的平均薪水
select avg(salary) from emp where dept_name = '市场部';

#6 查询部门的个数
select dept_name from emp;  
-- 12个
select count(dept_name) from emp; 
-- 11个 多行函数会忽略null

-- 部门去重
select distinct dept_name from emp;
select distinct dept_name from emp
where dept_name is not null;
select count(distinct dept_name) from emp;

5.3 分组

分组查询指的是使用group by语句, 对查询的信息进行分组, 相同数据作为一组。

语法格式：

select 分组字段/聚合函数 from 表名 group by 分组字段 [having 条件];

需求1: 通过性别分组

# 按照性别分组查询
select * from emp group by sex;    -- 能查到结果, 但是没有意义
select sex from emp group by sex;  -- 正确操作

分析：group by分组过程。

注意事项:

分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作
查询其他字段没有意义

6. 经典面试题

MySQL行列转置/MySQL行列转换(笔试题)

需求：

把下面数据转换

转换成下面效果

创建表及测试数据:

create table student(
	id int(11) primary key auto_increment,
	name varchar(20),
	subject varchar(20),
	score double
);

insert into student values(1,'张三','语文',20);
insert into student values(2,'张三','数学',30);
insert into student values(3,'张三','英语',40);
insert into student values(4,'李四','语文',50);
insert into student values(5,'李四','数学',60);
insert into student values(6,'李四','英语',70);

use zqwl1;
create table student(
	id int(11) primary key auto_increment,
	name varchar(20),
	subject varchar(20),
	score double
);

insert into student values(1,'张三','语文',20);
insert into student values(2,'张三','数学',30);
insert into student values(3,'张三','英语',40);
insert into student values(4,'李四','语文',50);
insert into student values(5,'李四','数学',60);
insert into student values(6,'李四','英语',70);

select * from student;

-- 第一种
select 
max(if(subject='语文',score,0)) '语文',
max(if(subject='数学',score,0)) '数学',
max(if(subject='英语',score,0)) '英语',
name
from student group by name;

-- 第二种
select 
max(
case subject
when '语文'
then score
else 0
end) '语文',
max(
case subject
when '数学'
then score
else 0
end) '数学',
max(
case subject
when '英语'
then score
else 0
end) '英语',
name
from student group by name;

7. limit关键字

作用:

limit是限制的意思, 限制返回的查询结果的函数(通过limit函数，控制查询返回多少行数据)
limit 语法是 MySql的方言, 用来完成分页

语法格式：

select 字段1, 字段2 ... from 表名 limit offset, length;

参数说明

offset 起始行数, 从0开始, 如果省略则默认从0开始, 0代表MySQL中第一条数据
length 返回的行数


-- limit关键字
#1 查询emp表中的前5条数据
select * from emp limit 5;-- 默认角标从0开始
select * from emp limit 1,5;-- 角标从1查询5条
#2 查询emp表中 从第4条开始, 查询6条
select * from emp limit 3, 6; -- 从0开始, 所以第四条数据为3

二、SQL执行流程

1. 介绍

前面讲解SQL语句中的各个关键字时，介绍过SQL语句的执行步骤，接下来，我们来学习在数据库管理系统中SQL语句到底是怎么执行的。

2. SQL语句执行流程

2.1 执行流程图示

2.2 各个组件介绍

连接管理与安全验证：MySQL有连接池（Connection Pool）管理客户端的连接。客户端连接后会验证用户名、密码、主机信息等
缓存（Cache&Buffer）：缓存中存储了SQL命令的HASH，直接比对SQL命令的HASH和缓存中key是否对应，如果对应，直接返回结果，不再执行其他操作。由于缓存的是SQL的HASH，所以根据Hash特性SQL中空格等内容必须完全一样。缓存里面包含表缓存、记录缓存、权限缓存等。查询语句执行完成后会把查询结果缓存到缓存中。在MySQL中查询缓存默认不开启。考虑到查询缓存性能瓶颈问题，从MySQL8开始已经不支持查询缓存了。
解析器（Parser）主要作用是解析SQL命令。将SQL命令分解成数据结构（解析树），后续的操作都是基于这个结构的。如果在分解过程中遇到错误，出现SQL解析错误。解析时主要检查SQL中关键字，检查关键字是否正确、SQL中关键字顺序是否正确、引号是否对应是否正确等。
预处理器：根据解析器的解析树，进一步检查表是否存在、列是否存在、名字和别名是否有歧义等。
优化器（Optimizer）：根据官网说明在执行SQL查询时会根据开销自动选择最优查询方案。采用“选择-投影-连接”的策略。先选择where中的行数。同时先选择要选择的列，而不是全部列，最后把内容合并到一起。
执行器：包含执行SQL命令。获取返回结果。生成执行计划等。
存储引擎：访问物理文件的媒介

2.3 执行流程详细说明

客户端向服务器端发送SQL命令和连接参数
服务器端连接模块连接并验证
缓存模块解析SQL为Hash并与缓存中Hash表对应。如果有结果直接返回结果，如果没有对应继续向下执行。如果是MySQL 8 是没有查询缓存的。
解析器解析SQL为解析树，检查关键字相关问题，如果出现错误，报SQL解析错误。如果正确，继续执行
预处理器对解析树继续处理检查表、列别名等，处理成功后生成新的解析树。
优化器根据开销自动选择最优执行计划，生成执行计划
执行器执行执行计划，访问存储引擎接口
存储引擎访问物理文件并返回结果
如果开启查询缓存，缓存管理器把结果放入到查询缓存中。
返回结果给客户端

三、多表

1. 多表的概述

实际开发中, 一个项目通常需要很多张表才能完成。

例如, 一个商城项目的数据库, 需要很多张表, 用户表, 分类表, 商品表, 订单表...

2. 单表的缺点

2.1 单表存在的问题

冗余, 同一个字段出现大量重复的数据。

设计为两张表

多表方式设计 employee 员工表: eid ename age

department 部门表: depid, dep_name, dep_location
创建员工和部门表

-- 创建员工表
create table employee(
	eid int primary key auto_increment,
	ename varchar(10),
	age int,
    dep_id int
);
-- 创建部门表
create table department(
	dep_id int primary key auto_increment,
	dep_name varchar(10),
	dep_location varchar(10)
);

插入数据

-- 添加2个部门
insert into department values(default, '研发部','广州'),(default, '销售部', '深圳');
select * from department;
-- 添加5个人员工
insert into employee values(default, '贾宝玉', 20, 1), 
						   (default, '林黛玉', 21, 1),
					       (default, '薛宝钗', 20, 1),
						   (default, '元春', 20, 2),
						   (default, '探春', 22, 2),
						   (default, '甄士隐', 18, 2);
select * from employee;

3.1 表关系分析

3.2 多表设计上的问题

当我们在员工表的dep_id里面输入了不存在的部门编号, 数据依然可以添加, 显然这是不合理的。

为了解决这个问题,我们可以使用外键约束.

3.3 外键约束

外键

外键指的是在主表中与从表的主键对应的的那个字段, 如员工表的dep_id, 就是外键。
外键约束

使用外键约束可以让两张表之间产生一个对应关系, 从而保证主从表数据的完整性。

3.3.1 创建外键约束

-- 创建外键约束
/*
		第一种:
			新建表时添加外键约束:
			constraint 外键名称(随便写) 
			foreign key(外键字段名)
			references 主表名称(主表字段名);
		第二种:
			为已经创建好的表添加
			alter table 从表名 add 	
			constraint 外键名称(随便写) 
			foreign key(外键字段名)
			references 主表名称(主表字段名);

*/
drop table employee;
create table employee(
	eid int primary key auto_increment,
	ename varchar(10),
	age int,
  dep_id int,
	constraint emp_dept_fk foreign key(dep_id)
	references department(dep_id)
);

-- 插入正常数据(从表的外键对应着主表的主键)
insert into employee values(default, '张百万', 20, 1), (default, '艳秋', 22, 2);

-- 插入不存在的dep_id
insert into employee values(default, '张亿万', 20, 100);
-- 报错:Cannot add or update a child row: a foreign key constraint fails

3.3.2 删除外键约束

3.4 外键约束的注意事项

1.从表的外键类型必须和主表的主键类型保持一致

2.添加或修改从表数据时,必须在主表的主键中存在

3.删除和变更从表的数据时,先删除从表中的数据或将外键中的值设置为null,再删除主表中的数据

不能先删除主表中的数据

4.可以设置外键约束再修改或删除时的联动操作

使用说明:

选项	作用
RESTRICT(默认)	如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
No action	相当于RESTRICT, 都是立即检查外键约束
CASCADE	在主表上update/delete记录时，同步update/delete掉从表匹配的记录
SET NULL	在主表上update/delete记录时，将子表上匹配记录的列设为null 注意: 子表的外键列不能为not null

四、多表关系设计

1. 介绍

实际的开发过程中, 一个项目通常需要很多张表才能完成.

表与表之间的3种关系	举例
一对多关系(最常见)	员工表和部门表学生表和班级表
多对多关系	学生表和课程表用户表和角色表
一对一关系(使用较少)	一对一的关系可以设计成一张表

2. 一对多关系(常见)

一对多关系(1 : n) 例如: 班级和学生, 部门和员工, 客户和订单, 类别和商品
一对多建表原则在从表(多方)创建一个字段, 该字段作为外键指向主表的主键

3. 多对多关系

多对多关系(m : n) 例如: 老师和学生, 学生和课程, 用户和角色。
多对多关系建表原则多对多的关系不能直接处理, 需要创建第三张表, 也称为中间表, 中间表至少两个字段,这两个字段分别作为外键指向各自一方的主键, 实际就是将多对多拆分为两个一对多。

4. 一对一关系

在实际的开发过程中应用不多, 因为一对一的关系可以设计为一张表。

五、多表查询

1. 什么是多表查询

DQL: 查询多张表, 获取到需要的数据

2. 数据准备

创建分类表与商品表

#分类表 (一方 主表)
create table category (
	cid varchar(32) primary key,
	cname varchar(50)
);
#商品表 (多方 从表)
create table products(
	pid varchar(32) primary key,
	pname varchar(50),
	price int,
	flag varchar(2),  # 是否上架标记为：1表示上架、0表示下架
	cid varchar(32),
	-- 添加外键约束
	foreign key (cid) references category (cid)
);

插入数据

#分类数据
insert into category(cid,cname) values('c001','家电');
insert into category(cid,cname) values('c002','鞋服');
insert into category(cid,cname) values('c003','化妆品');
insert into category(cid,cname) values('c004','汽车');

#商品数据
insert into products values('p001','小米电视机',5000,'1','c001');
insert into products values('p002','格力空调',3000,'1','c001');
insert into products values('p003','美的冰箱',4500,'1','c001');
insert into products values('p004','篮球鞋',800,'1','c002');
insert into products values('p005','运动裤',200,'1','c002');
insert into products values('p006','T恤',300,'1','c002');
insert into products values('p007','冲锋衣',2000,'1','c002');
insert into products values('p008','神仙水',800,'1','c003');
insert into products values('p009','大宝',200,'1','c003');

3. 笛卡尔积

交叉连接（CROSS JOIN）是对两个或者多个表进行笛卡儿积操作，所谓笛卡儿积就是关系代数里的一个概念，表示两个表中的每一行数据任意组合的结果。

笛卡尔积不管是否匹配，都连接。没有实际意义，有理论意义。

笛卡尔积便于理解连接查询的原理。

语法格式：

4. 多表查询的分类

4.1 内连接查询(等值连接)

通过指定的条件去匹配两张表中的数据, 匹配上就显示, 匹配不上就不显示
如: 按照从表的外键 = 主表的主键进行匹配 | inner join on, inner可以省略

4.2 自连接查询

自连接就是某张表自己和自己关联。

4.3 非等值连接

非等值连接就是两张表进行关联的时候不是使用关联的id相等进行连接的。

-- 内连接查询
-- 特点:只显示条件匹配的数据,不匹配的不显示

-- 等值连接

-- SQL92隐式内连接
# 查找商品分类后的信息
select pname,price,flag,cname from products p,category c where p.cid = c.cid;
-- SQL99显示内连接
# 查找商品分类后的信息
-- inner可以省略
select pname,price,flag,cname from products p inner join category c 
on p.cid = c.cid;
# 查询鞋服分类下, 价格大于500的商品名称和价格以及对应的分类名称
select pname,price,cname from products p inner join category c 
on p.cid = c.cid  having cname='鞋服' and price > 500;

-- 自连接
-- 某张表自己和自己连接
-- 查询员工名称和上级领导的名称
select * from emp;
-- SQL92
select c1.ename '员工',c2.ename '领导' from emp c1,emp c2 where c1.mgr=c2.empno;
-- SQL99
select c1.ename '员工',c2.ename '领导' from emp c1 join emp c2 
on c1.mgr=c2.empno;


-- 非等值连接
-- 条件不是等值的条件
select * from emp;
select * from salgrade;
-- SQL92
-- 查询员工的工资和工资级别
select e.ename,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
-- SQL99
-- 查询员工的工资和工资级别
select e.ename,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

4.4 外连接查询

通过指定的条件去匹配两张表中的数据, 匹配上就显示, 匹配不上也可以显示。

外连接只有SQL99可以使用

4.4.1 左外连接

左外连接的特点:

以左表为主, 左表中的数据全部显示
右表匹配到数据就显示匹配到的数据
右表没有匹配的数据显示为null

4.4.2 右外连接

右外连接的特点:

以右表为主, 右表中的数据全部显示
左表匹配到数据就显示匹配到的数据
左表没有匹配的数据显示为null

4.4.3 全外连接

注意： MySQL 中不支持 FULL OUTER JOIN 连接

可以使用 union 实现全完连接。

UNION: 可以将两个查询结果集合并，返回的行都是唯一的，会去重.
UNION ALL: 只是简单的将两个结果合并后就返回。

-- 外连接
-- 左外连接
-- 以左表为主,左表的所有数据都展示,右表没有匹配的显示为null
#查询分类信息以及对应的商品信息
select * from category c left join products p on c.cid=p.cid;

-- 右外连接
-- 以右表为主,右表的所有数据都展示,左表没有匹配的显示为null
#查询分类信息以及对应的商品信息
select * from products p right join category c on c.cid = p.cid;

-- 全外连接
-- MySQL中不能使用全外连接,需要用到联合union
-- union:合并并去重,union all:只合并
-- 相当于把左外连接和右外连接连起来
select * from category c left join products p on c.cid=p.cid 
union
select * from products p right join category c on c.cid=p.cid;

4.5 内连接和外连接总结

内连接: inner join: 只获取两张表中交集部分的数据。
左外连接: left join: 以左表为基准, 查询左表的所有数据, 以及与右表有交集的部分。
右外连接: right join: 以右表为基准, 查询右表的所有的数据, 以及与左表有交集的部分。

综合练习:

-- 创建数据库
create database lsh01;
-- 切换数据库
use lsh01;
-- 创建表
create table emp(
	empno int primary key auto_increment
	comment '员工编号',
	ename varchar(10) comment '员工姓名',
	mgr int comment '领导编号',
	hiredate date comment '入职日期',
	sal double(7,2) comment '月薪',
	comm double(7,2) comment '奖金率',
	deptno int comment '部门编号'
);

-- 插入数据
INSERT INTO emp VALUES (1, '曹操', 0, '2021-12-28', 30000.00, 0.30, 3);
INSERT INTO emp VALUES (2, '吕布', 1, '2021-12-29', 20000.00, 0.20, 3);
INSERT INTO emp VALUES (3, '刘备', 0, '2022-01-12', 35000.00, 0.40, 3);
INSERT INTO emp VALUES (4, '关羽', 3, '2021-12-31', 16000.00, 0.20, 1);
INSERT INTO emp VALUES (5, '赵云', 3, '2021-08-09', 12000.00, 0.20, 2);
INSERT INTO emp VALUES (6, '张飞', 3, '2022-01-01', 8000.00, 0.20, 4);
INSERT INTO emp VALUES (7, '宋江', 0, '2021-12-31', 25000.00, 0.30, 3);
INSERT INTO emp VALUES (8, '李逵', 7, '2021-09-17', 8000.00, 0.20, 4);
INSERT INTO emp VALUES (9, '林冲', 7, '2022-01-22', 3000.00, 0.10, 4);
INSERT INTO emp VALUES (10, '孙二娘', 9, '2022-01-06', 7000.00, 0.20, 1);
INSERT INTO emp VALUES (11, '吴用', 7, '2021-12-27', 2500.00, 0.10, 2);
INSERT INTO emp VALUES (12, '贾宝玉', 0, '2021-12-30', 12000.00, 0.30, 3);
INSERT INTO emp VALUES (13, '元春', 12, '2022-01-01', 9000.00, 0.20, 4);
INSERT INTO emp VALUES (14, '惜春', 12, '2021-08-12', 3000.00, 0.10, NULL);
INSERT INTO emp VALUES (15, '迎春', 12, '2021-12-28', 4200.00, 0.10, 2);
INSERT INTO emp VALUES (16, '探春', 12, '2022-01-12', 9600.00, NULL, 1);
INSERT INTO emp VALUES (17, '王熙凤', 0, '2021-08-11', 18000.00, 0.20, 3);
INSERT INTO emp VALUES (18, '秦可卿', 0, '2022-01-20', 16000.00, NULL, 3);

create table dept(
	deptno int primary key auto_increment comment '部门编号',
	dname varchar(10) comment '部门名称'
);

-- 插入数据
INSERT INTO dept VALUES (1, '开发部');
INSERT INTO dept VALUES (2, '人事部');
INSERT INTO dept VALUES (3, '行政部');
INSERT INTO dept VALUES (4, '财务部');
INSERT INTO dept VALUES (5, '公关部');

create table salgrade(
	grade char(1) primary key comment '等级',
	losal double comment '此等级的最低工资',
	hisal double comment '此等级的最高工资'
);

-- 插入数据
INSERT INTO salgrade VALUES ('A', 0, 3000);
INSERT INTO salgrade VALUES ('B', 3001, 8000);
INSERT INTO salgrade VALUES ('C', 8001, 10000);
INSERT INTO salgrade VALUES ('D', 10001, 50000);

-- 1. 查询没有奖金，且工资小于15000的员工的姓名
select ename from emp where sal<15000 and comm is null;
select ename from emp where comm is null and sal < 15000;

-- 2. 查询员工表中部门编号不是3或者工资是12000的员工信息
select * from emp where deptno != 3 or sal=12000;
select ename from emp where deptno != 3 or sal = 12000;

-- 3. 查看员工表中涉及到了哪些部门
-- 就是总共有那些部门,要用到分组
select deptno from emp group by deptno order by deptno;
select distinct deptno from emp;-- 直接去重

-- 4. 查询员工的姓名和部门号和年薪，按年薪降序，按部门号升序
-- 还要加上奖金
select ename,deptno,(1+ifnull(comm,0))*sal*12 from emp order by (1+ifnull(comm,0))*sal*12 desc,deptno;
select ename,deptno,(1+ifnull(comm,0))*sal*12 年薪 from emp order by 年薪 desc, deptno asc;

-- 5. 选择工资不在8000到17000的员工的姓名和工资，按工资降序
select ename,sal from emp where sal<8000 or sal>17000 order by sal desc;
select ename, sal from emp where sal not between 8000 and 17000 order by sal desc;

-- 6. 查询名字中包含春的员工信息，按照工资降序
-- 模糊查询(包含春'%春%')
select * from emp where ename like '%春%' order by sal desc;

-- 7. 查询公司员工工资的最大值、最小值、平均值(保留两位小数)、总和
-- 分组函数,一般与having一起使用,having分组后的过滤
-- round(2.1456,2)保留2位小数,四舍五入
select max(sal),min(sal),round(avg(sal),2),sum(sal) from emp;
select max(sal),min(sal),round(avg(sal),2),sum(sal) from emp;

-- 8. 查询部门编号为3的员工个数
-- 个数count(1)或者count(字段)
select count(1) from emp where deptno=3;
select count(*) from emp where deptno=3;

-- 9. 查询各部门的员工工资的最大值、最小值、平均值、总和，并按部门编号升序
select max(sal),min(sal),avg(sal),sum(sal),deptno from emp group by deptno order by deptno;
select deptno,max(sal),min(sal),avg(sal),sum(sal) from emp group by deptno order by deptno asc;

-- 10. 查询各个领导手下员工的最低工资，其中最低工资不能低于6000的
-- 自连接
-- SQL92
select min(e1.sal),e2.ename from emp e1,emp e2 where e1.mgr = e2.empno group by e2.ename 
having min(e1.sal)>=6000;
-- 答案不严谨
select mgr,min(sal) from emp group by mgr having min(sal)>=6000;

-- 11. 查询每个部门的编号，员工数量和工资平均值，并按平均工资降序
-- 分组
select deptno,count(1),avg(sal) from emp group by deptno order by avg(sal) desc;
select deptno,count(*),avg(sal) from emp group by deptno order by avg(sal) desc;

-- 12. 查询哪些部门的员工个数大于2，并按照个数降序排序
select count(1),deptno from emp group by deptno having count(1)> 2 order by count(1) desc;
select deptno, count(*) from emp group by deptno having count(*)>2 order by count(*) desc;

-- 13. 查询每个部门有奖金的员工的最高工资>12000的部门编号和最高工资
select deptno,max(sal) from emp where comm is not null group by deptno having max(sal)>12000;
select deptno,max(sal) from emp where comm is not null group by deptno having max(sal)>12000;

-- 14. 查询领导编号 > 3 的每个领导手下的员工最低工资 > 5000的领导编号是哪些，以及其手下的最低工资
select mgr,min(sal) from emp where mgr>3 group by mgr having min(sal)>5000;
select mgr,min(sal) from emp where mgr>3 group by mgr having min(sal)>5000;

-- 15. 查询员工名和对应的部门名
-- 多表
-- SQL92
select ename,dname from emp e,dept d where e.deptno=d.deptno;
-- SQL99
select ename,dname from emp e join dept d on e.deptno=d.deptno;
select ename,dname from emp e,dept d where e.deptno=d.deptno;

-- 16. 查询有奖金的员工名、部门名及奖金率
select ename,dname,comm from emp e,dept d where e.deptno=d.deptno and comm is not null;
select ename,dname,comm from emp e, dept d where e.deptno=d.deptno and comm is not null;

-- 17. 查询姓名中包含'春'的员工的名字、上级的名字
-- 模糊查询,自连接
-- SQL92
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno and e1.ename like '%春%';
select e.ename, m.ename from emp e, emp m where e.mgr=m.empno and e.ename like '%春%';

-- 18. 查询没有部门的员工信息及部门信息
select * from emp e left join dept d on e.deptno=d.deptno where e.deptno is null;
select * from emp e left join dept d on e.deptno=d.deptno where e.deptno is null;