Mysql基础篇-多表查询
多表关系（类别）
一对多
案例： 部门与员工的关系
关系： 一个部门对应多个员工，一个员工对应多个部门 （员工是多，部门是一）
实现： 在多的一方建立外键，指向一的一方的主键
多对多
案例： 学生与课程的关系
关系： 一个学生可以选修多个课程，一门课程也能供多个学生选择
实现： 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
plaintext1
2
3
4
5
6
7-- 学生表
create table student(
id int auto_increment primary key comment 'ID',
name varchar(10) comment '姓名',
no varchar(10) comment '学号'
)comment '学生表';
insert into student(id, name, no) values (......)plaintext1
2
3
4
5
6-- 课程表
create table course (
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '课程名称'
)comment '课程表';
insert into course(id, name) VALUES (...)plaintext1
2
3
4
5
6
7
8
9
10-- 第三张表
-- 第三张表都作为子键
create table cource_student (
id int auto_increment primary key comment '主键',
studentid int not null comment '学生ID',
courseid int not null comment '课程ID',
constraint fk_courseid foreign key (courseid) references course(id),
constraint fk_studentid foreign key (studentid) references student(id)
)comment '第三张表';
insert into cource_student values (1,1,1);
一对一
案例：用户与用户详情的关系
关系： 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另外一张表中，以提升操作效率（呈现的是一些分类的信息）
实现： 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（unique)
例如： 每个学员有各种信息，然后每种信息都有ID表示，这个时候就能够用id来建立（unique）外键实现两个表的关联（这个外键可以特地设置）
多表查询（概述）
现在有两张表：
plaintext
1 | -- 表一 |
笛卡尔积现象：
笛卡尔乘积是指在数学中，两个集合A，B所有组合情况（在多表查询中，需要消除无效的笛卡尔积）
若 select * from emp , dept ;
这会显示出 最终的数据是六个部门乘以四个人 二十四条数据
plaintext
1 | -- 消除 笛卡尔积 的方法就是取一个条件 |
多表查询分类
连接查询
内连接： 相当于查询A,B交集部分数据
外连接：
^ 左外连接：查询左表的所有数据，以及两张表交集部分数据
^ 右外连接：查询右表的所有数据，以及两张表交集的部分数据
自联结：当前表与自身的连接查询，自联结必须使用表别名
子查询
联合查询
连接查询
内连接 –(交集)
内连接查询的是两张表交集的部分
隐式内连接：
select 字段列表 from 表1，表2 where 条件…；
plaintext1
2
3
4
5
6
7-- 内连接演示
-- 1.查询每一个员工的姓名，以及关联的部门的名称（隐式内连接实现）
-- 表结构：emp , dept
-- 连接条件： emp.dept_id = dept.id
select emp.name,dept.name from emp ,dept where emp.dept_id=dept.id ;
-- 给表名取别名，在多表查询中经常取别名。如果已经取了别名，就不能使用表名限制字段即emp.dept_id是不行的
select * from emp e,dept d where e.dept_id = d.id;显式内连接：(多用)
select 字段列表 from 表1 [inner] join 表2 on 连接条件；
plaintext1
2
3-- 2.查询每一个员工的姓名，以及关联的部门的名称（显式内连接实现） --inner join
select e.name ,d.name from emp e inner join dept d on e.dept_id = d.id;
-- 注意的是取别名的优先度很高如果写成select emp.name 就是不行的，必须写成e.name
外连接（交集加入outer部分）
plaintext1
2
3
4
5
6
7-- 左外连接
-- 1. 查询emp表的所有数据，和对应部门信息；
-- 表结构 emp , dept
-- 连接条件： emp.dept_id = dept.id
select e.*,d.name from emp e left outer join dept d on d.id = e.dept_id;
-- 查询到了所有员工以及对应的员工部门，如果不属于任何部门，就会显示null
-- 输出 4-丁敏君- 23-出纳-5205-2009-05-03-10-4-销售部plaintext1
2
3
4
5
6-- 右外连接
-- 2. 查询dept表的所有数据，和对应员工信息
select d.*,e.name from emp e right join dept d on d.id = e.dept_id;
-- 输出为 所有部门，对应的人，如果没有输出Null
-- 注意 emp与dept的顺序 谁在左边谁就是表1，谁在右边就是表2，无论查左外还是右外只要改个单词就行了，表的前后顺序不用变
-- 输出 5-总经办-金庸右外左外连接都是left,right，表一表二顺序不同导致的，可以互通修改
自联结
select 字段列表 from 表A 别名A join 表A 别名B on 条件….；
自连接查询，可以是内连接查询，也可以是外连接查询
id name manageid 1 金庸 null 2 张无忌 1 3 韦一笑 2 4 丁敏君 2 5 周芷若 3 如果用来查询某个员工所属领导，就用manageid来指向id
plaintext
1 | -- 自连接（必须起别名，需要分成两张表） |
联合查询-union,union all
就是把多次查询的结果合并起来，形成一个新的查询结果集
select 字段列表 from 表A …
union [all]
select 字段列表 from 表B…；
plaintext
1 | -- 将薪资低于5000 的员工 和年龄大于五十的员工都查询出来 |
子查询
又称为嵌套查询
select * from t1 where column1 = (select column1 from t2)；
子查询外部的语句可以是，crud的任何一个
根据子查询结果的不同分类
- 标量子查询： 子查询结果为单个值
- 列子查询： 结果为一列
- 行子查询： 一行
- 表子查询： 多行多列
根据子查询位置分类
- where 之后
- from 之后
- select 之后
子查询返回的结果是单个值（数字，字符串，日期）（最简单）
plaintext
1 | -- 标量子查询 |
列子查询（结果为一列）
常见操作符 in ,not in ,any ,some ,all
any 子查询返回列表中，有任意一个满足就行 some 与any等价，两者可以互相使用 all 子查询返回列表中，所有值必须都满足 plaintext1
2
3
4
5
6
7
8
9
10-- 查询‘销售部’和 ‘市场部’ 的所有员工信息
-- a. 查询两者的部门ID
-- b. 根据部门ID， 查询员工信息
select *from emp where dept_id in (select id from dept where name in ('销售部','市场部'));
-- -------------------------------------------------------------------
-- 查询比市场部所有人工资都高的员工信息（套娃行为）
select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name ='市场部'));
-- -------------------------------------------------------------------
-- 查询工资比市场部比任意一人高的员工信息
select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name ='市场部'));行子查询（结果为一行）
子查询返回的结果是一行，可以是多列
常用操作符： = ， <> ， in ， not in
plaintext1
2
3
4
5
6
7
8
9
10-- 查询与张无忌薪资及直属领导相同的员工信息
-- a. 查询张无忌 的薪资及其 直属领导
select emp.salary, emp.managerid from emp where name = '张无忌';
-- b. 查询薪资相同的员工信息
select * from emp where salary = 12500 and managerid =1 ;
select * from emp where (salary,managerid)=(12500,1);
-- 结合
select * from emp where (salary,managerid)=(select emp.salary, emp.managerid from emp where name = '张无忌');
-- 括号中的类型要对应
-- 返回结果一行多列表子查询（多行多列）
子查询返回的结果是多行多列
常用操作符：in， 经常出现在from之后
plaintext1
2
3
4
5
6
7
8-- 查询与鹿杖客，宋远桥的职位和薪资相同的员工信息
-- a. 查询他们的职位和薪资
select job, salary from emp where name = '宋远桥' or name = '鹿杖客';
-- b. 查询员工信息
select * from emp where (job,salary) in (select job, salary from emp where name = '宋远桥' or name = '鹿杖客') ;
-- 查询入职日期为“...”之后的员工信息（这部分员工），及其部门信息
select e.*,d.* from (select * from emp where entrydate> '....') e left join dept d on e.dept_id = d.id;
多表查询案例
plaintext1
2
3
4
5
6
7
8
9
10
11
12
13
14-- 加一张表
create table salgrade
(
grade int comment '等级',
losal int comment '最低薪资',
hisal int comment '最高薪资'
) comment '薪资等级表';
insert into salgrade(grade, losal, hisal)
VALUES (1, 0, 3000),
(2, 3001, 5000),
(3, 5001, 8000),
(4, 8001, 10000),
(5, 10001, 15000);plaintext1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70-- 1.查询员工的姓名，年龄，职位，部门信息。 (隐式内连接)-from 后面写两张表
select e.name, e.age, e.job, d.id
from emp e,
dept d
where e.dept_id = d.id;
-- 2.查询年龄小于30的员工姓名，年龄，职位，部门信息 (显式内连接)
select e.name, e.age, e.job, d.name
from emp e
inner join dept d on e.dept_id = d.id
where e.age < 30;
-- 3.查询拥有员工的部门ID，部门名称 -- 使用distinct对查询结果去重
select distinct d.id, d.name
from dept d join emp e on d.id = e.dept_id;
-- 4.查询所有年龄大于40岁的员工，及其归属的部门名称，如果员工没有分配部门，也需要展示出来
select e.name, d.name
from emp e left outer join dept d on d.id = e.dept_id
where e.age > 40;
-- 5.查询所有员工的工资等级 emp salary
select e.name, s.grade
from emp e,
salgrade s
where e.salary between s.losal and s.hisal;
-- 6.查询 研发部所有员工的信息及其工资等级 (三张表) n张表至少有n-1个条件
select e.name, s.grade
from emp e,
dept d,
salgrade s
where e.dept_id = d.id
and e.salary between s.losal and s.hisal
and d.name = '研发部';
-- 右键 reformat code格式化
-- 7.查询研发部员工的平均工资
select d.name, avg(e.salary)
from emp e,
dept d
where e.dept_id = d.id
and d.name = '研发部';
-- 8.查询工资比灭绝师太高的员工信息 (子查询)
select *
from emp e
where e.salary > (select salary from emp where name = '灭绝师太');
-- 9.查询比平均薪资高的员工信息
select *
from emp
where salary > (select avg(salary) from emp);
-- 10.查询低于本部门平均工资的员工信息 (自连接,关键词 本部门)
select *
from emp e1
where e1.salary < (select avg(e2.salary) from emp e2 where e1.dept_id = e2.dept_id);
-- 11.查询所有的部门信息，并统计部门的员工数量
select d.id, d.name, (select count(*) from emp e where e.dept_id = d.id) '人数'
from dept d;
-- 12.查询所有学生的选课情况，展示出学生名称，学号，课程名称。 student,course,student_course
select s.name, s.no, c.name
from student s,
course c,
cource_student cs
where s.id = cs.id
and c.id = cs.id;总结
- 几张表
- 表的连接条件
- 查询条件
- 可以先分开来写所有的算式，最后再连接起来
- 用from 后面加多张表性能会很差同时可能会出现笛卡尔积的情况，每张表1000个记录 就得在内存里开个1000000000行的表，所以尽量用join on
- 多表查询好懵逼，今天周杰伦发新歌了wow！
http://example.com/2023/12/18/Mysql%E5%9F%BA%E7%A1%80%E7%AF%87-%E5%A4%9A%E8%A1%A8%E6%9F%A5%E8%AF%A2/
本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 Echin の 博客！
