引入
MySQL是关系型数据库, 表与表之间是可以建立联系的, 相关联的两张表或多张表的查询就需要使用一些方法, 下面介绍 MySQL 的多表查询 :
- 多表连接查询
- 交叉连接
- 内连接
- 外连接
- 符合条件连接查询
- 子查询
一.创建两张表(准备工作)
五个部门, 九个员工(这个随意), 为了实验方便, 设置一个部门没有员工(FI), 设置一个员工不属于任何部门(Lina)
create table dep(
id int primary key auto_increment,
name varchar(16)
);
insert dep(name) value("TE"),("CFT"),("ST"),("HR"),("FI");
create table emp(
id int primary key auto_increment,
name varchar(16),
sex enum("male","female") not null default "male",
age int,
dep_id int
);
insert emp(name,sex,age,dep_id) value
("shawn","male",22,1),
('Chair','female',48,1),
('jack','male',18,2),
('Anni','male',28,2),
('Rub','male',18,2),
('Summer','female',18,3),
('Tom','male',18,4),
('Bob','male',18,4),
('Lina','female',18,6);
二.多表连接查询
0.外连接语法与笛卡尔积是什么
select [字段列表]
from [表1] inner|left|right join [表2] # inner|left|right 这三个就对应的内|左|右连接
on [表1].[字段] = [表2].[字段]; # on 子句后面接的是连接条件
🥒笛卡尔积(Cartesian product),指两个集合的乘积
x = {1,2} # 集合 x
y = {4,5,6} # 集合 y
x * y = {(1,4),(1,5),(1,6),(2,4),(2,5),(2,6)} # x 乘 y 的结果
y * x = {(4,1),(4,2),(5,1),(5,2),(6,1),(6,2)} # y 乘 x 的结果
1.交叉连接
- 交叉连接不适用与任何匹配查询, 只能可以生成笛卡尔积
mysql> select * from emp,dep;
+----+--------+--------+------+--------+----+------+
| id | name | sex | age | dep_id | id | name |
+----+--------+--------+------+--------+----+------+
| 1 | shawn | male | 22 | 1 | 1 | TE |
| 1 | shawn | male | 22 | 1 | 2 | CFT |
| 1 | shawn | male | 22 | 1 | 3 | ST |
| 1 | shawn | male | 22 | 1 | 4 | HR |
| 1 | shawn | male | 22 | 1 | 5 | FI |
| 2 | Chair | female | 48 | 1 | 1 | TE |
| 2 | Chair | female | 48 | 1 | 2 | CFT |
| 2 | Chair | female | 48 | 1 | 3 | ST |
| 2 | Chair | female | 48 | 1 | 4 | HR |
| 2 | Chair | female | 48 | 1 | 5 | FI |
| 3 | jack | male | 18 | 2 | 1 | TE |
| 3 | jack | male | 18 | 2 | 2 | CFT |
| 3 | jack | male | 18 | 2 | 3 | ST |
| 3 | jack | male | 18 | 2 | 4 | HR |
| 3 | jack | male | 18 | 2 | 5 | FI |
| 4 | Anni | male | 28 | 2 | 1 | TE |
| 4 | Anni | male | 28 | 2 | 2 | CFT |
| 4 | Anni | male | 28 | 2 | 3 | ST |
| 4 | Anni | male | 28 | 2 | 4 | HR |
| 4 | Anni | male | 28 | 2 | 5 | FI |
| 5 | Rub | male | 18 | 2 | 1 | TE |
| 5 | Rub | male | 18 | 2 | 2 | CFT |
| 5 | Rub | male | 18 | 2 | 3 | ST |
| 5 | Rub | male | 18 | 2 | 4 | HR |
| 5 | Rub | male | 18 | 2 | 5 | FI |
| 6 | Summer | female | 18 | 3 | 1 | TE |
| 6 | Summer | female | 18 | 3 | 2 | CFT |
| 6 | Summer | female | 18 | 3 | 3 | ST |
| 6 | Summer | female | 18 | 3 | 4 | HR |
| 6 | Summer | female | 18 | 3 | 5 | FI |
| 7 | Tom | male | 18 | 4 | 1 | TE |
| 7 | Tom | male | 18 | 4 | 2 | CFT |
| 7 | Tom | male | 18 | 4 | 3 | ST |
| 7 | Tom | male | 18 | 4 | 4 | HR |
| 7 | Tom | male | 18 | 4 | 5 | FI |
| 8 | Bob | male | 18 | 4 | 1 | TE |
| 8 | Bob | male | 18 | 4 | 2 | CFT |
| 8 | Bob | male | 18 | 4 | 3 | ST |
| 8 | Bob | male | 18 | 4 | 4 | HR |
| 8 | Bob | male | 18 | 4 | 5 | FI |
| 9 | Lina | female | 18 | 6 | 1 | TE |
| 9 | Lina | female | 18 | 6 | 2 | CFT |
| 9 | Lina | female | 18 | 6 | 3 | ST |
| 9 | Lina | female | 18 | 6 | 4 | HR |
| 9 | Lina | female | 18 | 6 | 5 | FI |
+----+--------+--------+------+--------+----+------+
45 rows in set (0.00 sec)
2.内连接
- 只连接两张表匹配的行(两张表共有的部分), 相当于是从笛卡尔积中筛选出正确的结果
- 语句使用 : 内连接使用
inner join关键字连接, 并使用on子句设置连接条件 - 注意 : 当对多个表进行查询时，要在 select 语句后面指定字段是来源自哪一张表
语法为表名.列名，如果表名较长，可以给表设置别名，这样就可以直接在 select 后写表的别名.列名
select emp.id,emp.name,emp.age,emp.sex,dep.name
from emp inner join dep
on emp.dep_id=dep.id;
# 还可以下面这种写法
select emp.id,emp.name,emp.sex,emp.age,dep.name
from emp,dep
where emp.dep_id=dep.id;
上面发现 FI 部门没有任何员工与之匹配, 所以就没有显示该条记录
员工 Lina 也没有与任何部门匹配, 所以也没有显示该条记录
3.外连接之左连接
- 优先显示左表全部记录, 如果左表中的某行在右表中没有匹配的行, 那么在显示的结果中, 右表未匹配的字段为 NULL
- 使用语法 : 左连接使用
left join连接两表, 后面使用on子句设置连接条件
select emp.id,emp.name,dep.name
from emp left join dep
on emp.dep_id=dep.id;
4.外连接之右连接
- 优先显示右表全部记录, 如果右表中的某行在左表中没有匹配的行, 那么在显示的结果中, 左表未匹配的字段为 NULL
- 使用语法 : 左连接使用
right join连接两表, 后面使用on子句设置连接条件
select emp.id,emp.name,dep.name
from emp right join dep
on emp.dep_id=dep.id;
5.全外连接
- 显示左右两表全部记录, 在内连接的基础上增加两边没有的结果
- 注意 : 全连接关键字
full join, mysql 不支持全外连接, 可以使用union合并左连接和右连接结果产生与全连接相同的效果 union与union all的区别 :union会去掉相同的记录
select emp.id,emp.name,dep.name
from emp left join dep
on emp.dep_id=dep.id
union
select emp.id,emp.name,dep.name
from emp right join dep
on emp.dep_id=dep.id;
三.符合条件查询
设置条件, 符合条件的才显示结果
1.示例1
- 内连接方式查询出年龄大于 25 的员工及员工名所在的部门名
select emp.name,dep.name
from emp inner join dep
on emp.dep_id=dep.id
where emp.age>25;
2.示例2
- 内连接方式查询出年龄大于 25 的员工名及部门名, 并且以 age 字段升序
select emp.name,dep.name
from emp inner join dep
on emp.dep_id=dep.id
where emp.age>25
order by emp.age; # 默认升序(asc), 降序(dEsc)
四.子查询
0.子语句介绍
- 子查询时将一个查询语句嵌套在另一个查询语句中
- 内层查询语句的查询结果, 可以为外层查询语句提供查询条件
- 子查询中包含的关键字 : IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS 等关键字
- 还可以包含比较运算符 : =、 != 、 >、 <、等
1.帶 IN 关键字的字查询
注意 : 特别注意not in结果集中不能有null,not in的结果集中出现null则查询结果为null
- 查询个部门员工平均年龄在 25 以上的部门名
select id,name from dep
where id in
(select dep_id from emp group by dep_id having avg(age)>18);
- 查看 CFE 岗位所有员工的姓名
select name from emp
where dep_id in
(select id from dep where name="CFT");
- 查看部门人数不足3人的部门名
select name from dep
where id in
(select dep_id from emp
where dep_id
group by dep_id
having count(id)<3);
2.带比较运算符的子查询
- 查询大于所有员工平均年龄的员工名与年龄
select name,age from emp
where age>(select avg(age) from emp);
- 查询大于部门平均年龄的员工名与年龄
select emp.name,emp.age
from emp inner join (select dep_id,avg(age) as avg_age from emp group by dep_id) as id_avg
on emp.dep_id=id_avg.dep_id
where emp.age>id_avg.avg_age;
3. 带EXISTS关键字的子查询
- EXISTS关字键字表示存在
- 在使用EXISTS关键字时, 内层查询语句不返回查询的记录, 而是返回一个真假值, True或False
- 当返回True时，外层查询语句将进行查询; 当返回值为False时, 外层查询语句不进行查询
- 相当于一个循环嵌套,先外层循环一次,内层再完完整整的循环一遍(上面的in,any,all…等等都是先拿到内层的一个结果集)
# dep 表中存在 "FI" 这个部门 : TRUE
select * from emp
where exists
(select name from dep where name="FI");
# dep 表中存在 "IO" 这个部门 : False
select * from emp
where exists
(select name from dep where name="IO");
1693
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
