【10.0】多表查询和子查询
【零】引入
- 多表查询的思路总共就两种
【1】子查询
- 就相当于是我们日常生活中解决问题的方式(一步步解决)
- 将一条SQL语句的查询结果加括号当做另外一条SQL语句的查询条件
- eg:以昨天的员工表和部门表为例 查询jason所在的部门名称
- 子查询的步骤
- 先查dream所在的部门编号
- 根据部门编号去部门表中查找部门名称
【2】联表查询
- 先将多张表拼接到一起 形成一张大表 然后基于单表查询获取数据
- eg:以昨天的员工表和部门表为例 查询dream所在的部门名称
- 连表操作
- 先将员工表和部门表按照某个字段拼接到一起
- 基于单表查询
【一】准备数据
（1）创建表
create table dep(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(20)
);
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
sex ENUM("male","female") NOT NULL DEFAULT "male",
age INT,
dep_id INT
);
（2）插入数据
insert into dep values
("200","技术部"),
("201","人力资源"),
("202","销售部"),
("203","运营部"),
("204","售后部");
insert into emp(name,sex,age,dep_id) values
("dream","male",18,200),
("chimeng","female",18,201),
("menmgneg","male",38,202),
("hope","male",18,203),
("own","male",28,204),
("thdream","male",18,205);
（3）查看表
desc dep;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.13 sec)
desc emp;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
（4）查看数据
select * from dep;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术部 |
| 201 | 人力资源 |
| 202 | 销售部 |
| 203 | 运营部 |
| 204 | 售后部 |
+------+--------------+
5 rows in set (0.00 sec)
select * from emp;
+----+----------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+----------+--------+------+--------+
| 1 | dream | male | 18 | 200 |
| 2 | chimeng | female | 18 | 201 |
| 3 | menmgneg | male | 38 | 202 |
| 4 | hope | male | 18 | 203 |
| 5 | own | male | 28 | 204 |
| 6 | thdream | male | 18 | 205 |
+----+----------+--------+------+--------+
6 rows in set (0.00 sec)
【二】子查询
- 子查询就相当于我们平时解决问题的思路
- 将一个查询语句的结果当做另外一个查询语句的条件去用
【1】案例一：获取员工dream所在的部门名称
（1）先获取dream的部门编号
select dep_id from emp where name='dream';
+--------+
| dep_id |
+--------+
| 200 |
+--------+
1 row in set (0.00 sec)
（2）将结果加括号作为查询条件
select name from dep where id=(select dep_id from emp where name='dream');
+------+
| name |
+------+
| 技术部|
+------+
1 row in set (0.00 sec)
【2】案例二：
-
查询部门是技术或者人力资源的员工信息
-
先获取部门的ID号，再去员工表里面删选出对应的员工
-
查询语句
select dep_id from dep where name = "技术部" or name = "人力资源";
select name from emp where dep_id in (200,201);
- 完整语句
select * from emp where dep_id in (select dep_id from dep where name = "技术部" or name = "人力资源");
+----+----------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+----------+--------+------+--------+
| 1 | dream | male | 18 | 200 |
| 2 | chimeng | female | 18 | 201 |
| 3 | menmgneg | male | 38 | 202 |
| 4 | hope | male | 18 | 203 |
| 5 | own | male | 28 | 204 |
| 6 | thdream | male | 18 | 205 |
+----+----------+--------+------+--------+
6 rows in set (0.00 sec)
【三】联表查询
【0】笛卡尔积补充
（1）什么是笛卡尔积
- 笛卡尔积是一个数学概念，它描述的是两个集合之间所有可能的元素组合的数量。
- 具体来说，如果集合A有n个元素，集合B有m个元素，则它们的笛卡尔积的大小为nm。
（2）简单来说
- 笛卡尔积是通过组合两个集合的所有元素来创建一个新的集合的过程。
- 在最简单的例子中，如果有一个集合包含三个元素a、b和c，另一个集合包含两个元素x和y，那么这两个集合的笛卡尔积将包含六个元素：ax、ay、bx、by、cx和cy。
（3）MySQL中的笛卡尔积
- 在SQL中，当我们使用JOIN操作将两个或更多的表连接在一起时，结果集中的行数是所有连接表的行数的乘积。这就是所谓的笛卡尔积。
- 例如，假设我们有两个表A和B，其中A有5行，B有3行。
- 如果我们使用INNER JOIN将这两个表连接起来，那么结果集中将会有5 x 3 = 15行。
- 这是因为对于每一行A，我们可以从B中选择任意一行进行匹配。
- 因此，总共有5种不同的方式来组合A表中的每一行和B表中的每一行，这导致了最终结果集的大小为5 x 3 = 15。
- 这个过程就是笛卡尔积，它是数学中的一种运算，用于计算两个集合的所有可能的元素组合的数量。
- 在这个情况下，每个元素都是一个表格中的行。
- 所以，当我们在MySQL中使用JOIN操作时，结果集的大小实际上是所有连接表的行数的乘积，这就是为什么我们称其为笛卡尔积的原因。
（4）总结
- 笛卡尔积是一种非常有用的工具，它可以帮助我们理解数据之间的复杂关系，并为我们提供更深入的数据洞察。
【1】拼表
结果叫 笛卡尔积
- 查询数据
select * from dep,emp;
+------+--------------+----+----------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+----------+--------+------+--------+
| 200 | 技术部 | 1 | dream | male | 18 | 200 |
| 201 | 人力资源 | 1 | dream | male | 18 | 200 |
| 202 | 销售部 | 1 | dream | male | 18 | 200 |
| 203 | 运营部 | 1 | dream | male | 18 | 200 |
| 204 | 售后部 | 1 | dream | male | 18 | 200 |
| 200 | 技术部 | 2 | chimeng | female | 18 | 201 |
| 201 | 人力资源 | 2 | chimeng | female | 18 | 201 |
| 202 | 销售部 | 2 | chimeng | female | 18 | 201 |
| 203 | 运营部 | 2 | chimeng | female | 18 | 201 |
| 204 | 售后部 | 2 | chimeng | female | 18 | 201 |
| 200 | 技术部 | 3 | menmgneg | male | 38 | 202 |
| 201 | 人力资源 | 3 | menmgneg | male | 38 | 202 |
| 202 | 销售部 | 3 | menmgneg | male | 38 | 202 |
| 203 | 运营部 | 3 | menmgneg | male | 38 | 202 |
| 204 | 售后部 | 3 | menmgneg | male | 38 | 202 |
| 200 | 技术部 | 4 | hope | male | 18 | 203 |
| 201 | 人力资源 | 4 | hope | male | 18 | 203 |
| 202 | 销售部 | 4 | hope | male | 18 | 203 |
| 203 | 运营部 | 4 | hope | male | 18 | 203 |
| 204 | 售后部 | 4 | hope | male | 18 | 203 |
| 200 | 技术部 | 5 | own | male | 28 | 204 |
| 201 | 人力资源 | 5 | own | male | 28 | 204 |
| 202 | 销售部 | 5 | own | male | 28 | 204 |
| 203 | 运营部 | 5 | own | male | 28 | 204 |
| 204 | 售后部 | 5 | own | male | 28 | 204 |
| 200 | 技术部 | 6 | thdream | male | 18 | 205 |
| 201 | 人力资源 | 6 | thdream | male | 18 | 205 |
| 202 | 销售部 | 6 | thdream | male | 18 | 205 |
| 203 | 运营部 | 6 | thdream | male | 18 | 205 |
| 204 | 售后部 | 6 | thdream | male | 18 | 205 |
+------+--------------+----+----------+--------+------+--------+
30 rows in set (0.00 sec)
【2】拼表升级
- 查询数据
select * from dep,emp where emp.dep_id = dep.id;
+------+--------------+----+----------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+----------+--------+------+--------+
| 200 | 技术部 | 1 | dream | male | 18 | 200 |
| 201 | 人力资源 | 2 | chimeng | female | 18 | 201 |
| 202 | 销售部 | 3 | menmgneg | male | 38 | 202 |
| 203 | 运营部 | 4 | hope | male | 18 | 203 |
| 204 | 售后部 | 5 | own | male | 28 | 204 |
+------+--------------+----+----------+--------+------+--------+
5 rows in set (0.00 sec)
一条SQL语句的查询结果 我们也可以看成是一张虚拟表
如果一条SQL语句中设计到多张表的字段名称编写 建议使用表名前缀做区分
【3】拼表关键字
- inner join：内连接
- 只拼接两张表中共有的数据部分
- left join：左连接
- 左表所有的数据都展示出来，没有对应的项就用null表示
- right join：右连接
- 右表所有的数据都展示出来，没有对应的项就用null表示
- union：全连接
- 左右两表的数据都展示出来
（1）inner join(内连接)
只拼接两张表中共有的数据部分
- 查询数据
select * from emp inner join dep on emp.dep_id = dep.id;
+----+----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+----------+--------+------+--------+------+--------------+
| 1 | dream | male | 18 | 200 | 200 | 技术部 |
| 2 | chimeng | female | 18 | 201 | 201 | 人力资源 |
| 3 | menmgneg | male | 38 | 202 | 202 | 销售部 |
| 4 | hope | male | 18 | 203 | 203 | 运营部 |
| 5 | own | male | 28 | 204 | 204 | 售后部 |
+----+----------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)
（2）left join(左连接)
左表所有的数据都展示出来，没有对应的项就用null表示
- 查询数据
select * from emp left join dep on emp.dep_id = dep.id;
+----+----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+----------+--------+------+--------+------+--------------+
| 1 | dream | male | 18 | 200 | 200 | 技术部 |
| 2 | chimeng | female | 18 | 201 | 201 | 人力资源 |
| 3 | menmgneg | male | 38 | 202 | 202 | 销售部 |
| 4 | hope | male | 18 | 203 | 203 | 运营部 |
| 5 | own | male | 28 | 204 | 204 | 售后部 |
| 6 | thdream | male | 18 | 205 | NULL | NULL |
+----+----------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)
（3）right join(右连接)
右表所有的数据都展示出来，没有对应的项就用null表示
- 查询数据
select * from emp right join dep on emp.dep_id = dep.id;
+------+----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+----------+--------+------+--------+------+--------------+
| 1 | dream | male | 18 | 200 | 200 | 技术部 |
| 2 | chimeng | female | 18 | 201 | 201 | 人力资源 |
| 3 | menmgneg | male | 38 | 202 | 202 | 销售部 |
| 4 | hope | male | 18 | 203 | 203 | 运营部 |
| 5 | own | male | 28 | 204 | 204 | 售后部 |
+------+----------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)
（4）union(全连接)
左右两表的数据都展示出来
- 查询数据
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;
;
+------+----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+----------+--------+------+--------+------+--------------+
| 1 | dream | male | 18 | 200 | 200 | 技术部 |
| 2 | chimeng | female | 18 | 201 | 201 | 人力资源 |
| 3 | menmgneg | male | 38 | 202 | 202 | 销售部 |
| 4 | hope | male | 18 | 203 | 203 | 运营部 |
| 5 | own | male | 28 | 204 | 204 | 售后部 |
| 6 | thdream | male | 18 | 205 | NULL | NULL |
+------+----------+--------+------+--------+------+--------------+
6 rows in set (0.02 sec)
【4】总结
- 我们学会了连表操作之后 其实就可以将N多张表拼接到一起
- 思路
- 我们可以将两张表拼接之后的结果起别名当做一张表使用
- 然后再去跟另外一张表拼接
【四】案例：查询平均年龄在25岁以上的部门名称
- 涉及到多表操作的时候，一定要加上表的前缀
【1】联表
- 先拿到部门和员工表 拼接之后的结果
- 对拼接后的结果进行部门分组
select * from emp inner join dep on emp.dep_id = dep.id;
+----+----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+----------+--------+------+--------+------+--------------+
| 1 | dream | male | 18 | 200 | 200 | 技术部 |
| 2 | chimeng | female | 18 | 201 | 201 | 人力资源 |
| 3 | menmgneg | male | 38 | 202 | 202 | 销售部 |
| 4 | hope | male | 18 | 203 | 203 | 运营部 |
| 5 | own | male | 28 | 204 | 204 | 售后部 |
+----+----------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)
select dep.name from emp inner join dep
on emp.dep_id = dep.id
group by dep.name
having avg(age) > 25
;
+-----------+
| name |
+-----------+
| 售后部 |
| 销售部 |
+-----------+
2 rows in set (0.00 sec)
【2】子查询
- 分布操作
select name from dep where id in
(select dep_id from emp group by dep_id
having avg(age) > 25);
+-----------+
| name |
+-----------+
| 销售部 |
| 售后部 |
+-----------+
2 rows in set (0.00 sec)
【补充】关键字exist
- 只返回布尔值
- 返回true时，外层查询语句执行
- 返回false时，外层查询语句不执行
select * from emp where exists (select id from dep where id > 3);
# 结果为 true
+----+----------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+----------+--------+------+--------+
| 1 | dream | male | 18 | 200 |
| 2 | chimeng | female | 18 | 201 |
| 3 | menmgneg | male | 38 | 202 |
| 4 | hope | male | 18 | 203 |
| 5 | own | male | 28 | 204 |
| 6 | thdream | male | 18 | 205 |
+----+----------+--------+------+--------+
6 rows in set (0.00 sec)
select * from emp where exists (select id from dep where id > 300);
# 结果为 false
Empty set (0.00 sec)
本文来自博客园，作者：Chimengmeng，转载请注明原文链接：https://www.cnblogs.com/dream-ze/p/17995410
