多表查询

多表查询

【1】子查询#

• 子查询就是把将一条sql语句查询到的结果作为一张表和另一张表平在一起完成多表查询

• 有一张成绩表和学生信息表，要求查询学生的名字以及他的平均成绩

• 这时候就可以先通过成绩表查到对应学生id的平均成绩

• 再以这次查询的结果和学生信息表一起完成多表查询

【2】联表查询#

• 联表查询分为内连接和外连接，外连接又分左外连接和右外连接
• 以上图为例，两个椭圆分别代表表A和表B
• 内连接就是中间的重合部分
• 左外连接就是表A和中间重合部分
• 右外连接就是表B和中间重合部分

数据准备

-- 创建表
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
);

-- 插入数据
insert into dep values
("200","技术部"),
("201","人力资源"),
("202","销售部"),
("203","运营部"),
("204","售后部");

insert into emp(name,sex,age,dep_id) values
("hqq","male",18,200),
("green","female",18,201),
("tom","male",38,202),
("leo","male",18,203),
("sky","male",28,204),
("kevin","male",18,205);

子查询

【1】案例一：获取员工hqq所在的部门名称#

-- 获取hqq所在的部门编号
select name,dep_id
from emp
where name = 'hqq'
;

-- 获取部门名称
select t2.name,dep.id
from dep join(select name,dep_id
from emp
where name = 'hqq'
) as t2
on t2.dep_id = dep.id;

【2】案例二：查询部门是技术或者人力资源的员工信息#

-- 获取部门id
SELECT dep.id
FROM dep
WHERE name in ('技术部','人力资源')
;

-- 获取员工信息
SELECT *
FROM emp
JOIN(SELECT dep.id
FROM dep
WHERE name in ('技术部','人力资源')) as t2 
on t2.id = emp.dep_id
;

联表查询

【1】笛卡尔积补充#

• 笛卡尔积是一个数学概念，它描述的是两个集合之间所有可能的元素组合的数量。

• 具体来说，如果集合A有n个元素，集合B有m个元素，则它们的笛卡尔积的大小为nm。

• 在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操作时，结果集的大小实际上是所有连接表的行数的乘积，这就是为什么我们称其为笛卡尔积的原因。

【2】拼表#

-- 拼接两张表
select * from dep,emp;

+-----+--------------+----+-------+--------+------+--------+
| id  | name         | id | name  | sex    | age  | dep_id |
+-----+--------------+----+-------+--------+------+--------+
| 200 | 技术部       |  1 | hqq   | male   |   18 |    200 |
| 201 | 人力资源     |  1 | hqq   | male   |   18 |    200 |
| 202 | 销售部       |  1 | hqq   | male   |   18 |    200 |
| 203 | 运营部       |  1 | hqq   | male   |   18 |    200 |
| 204 | 售后部       |  1 | hqq   | male   |   18 |    200 |
| 200 | 技术部       |  2 | green | female |   18 |    201 |
| 201 | 人力资源     |  2 | green | female |   18 |    201 |
| 202 | 销售部       |  2 | green | female |   18 |    201 |
| 203 | 运营部       |  2 | green | female |   18 |    201 |
| 204 | 售后部       |  2 | green | female |   18 |    201 |
| 200 | 技术部       |  3 | tom   | male   |   38 |    202 |
| 201 | 人力资源     |  3 | tom   | male   |   38 |    202 |
| 202 | 销售部       |  3 | tom   | male   |   38 |    202 |
| 203 | 运营部       |  3 | tom   | male   |   38 |    202 |
| 204 | 售后部       |  3 | tom   | male   |   38 |    202 |
| 200 | 技术部       |  4 | leo   | male   |   18 |    203 |
| 201 | 人力资源     |  4 | leo   | male   |   18 |    203 |
| 202 | 销售部       |  4 | leo   | male   |   18 |    203 |
| 203 | 运营部       |  4 | leo   | male   |   18 |    203 |
| 204 | 售后部       |  4 | leo   | male   |   18 |    203 |
| 200 | 技术部       |  5 | sky   | male   |   28 |    204 |
| 201 | 人力资源     |  5 | sky   | male   |   28 |    204 |
| 202 | 销售部       |  5 | sky   | male   |   28 |    204 |
| 203 | 运营部       |  5 | sky   | male   |   28 |    204 |
| 204 | 售后部       |  5 | sky   | male   |   28 |    204 |
| 200 | 技术部       |  6 | kevin | male   |   18 |    205 |
| 201 | 人力资源     |  6 | kevin | male   |   18 |    205 |
| 202 | 销售部       |  6 | kevin | male   |   18 |    205 |
| 203 | 运营部       |  6 | kevin | male   |   18 |    205 |
| 204 | 售后部       |  6 | kevin | male   |   18 |    205 |
+-----+--------------+----+-------+--------+------+--------+


-- 会发现，查出来的数据会多出来很多，数据的总和为 两表的行数相乘
-- 这就是mysql中的笛卡尔积

【3】拼表优化#

• 仔细看表可以发现有很多错误的数据
• 我只需要部门表里的部门id和员工表里面的部门id相同的部门
• 只需要加上个筛选条件即可

-- 筛选条件
select * from emp,dep where emp.dep_id = dep.id;

+----+-------+--------+------+--------+-----+--------------+
| id | name  | sex    | age  | dep_id | id  | name         |
+----+-------+--------+------+--------+-----+--------------+
|  1 | hqq   | male   |   18 |    200 | 200 | 技术部       |
|  2 | green | female |   18 |    201 | 201 | 人力资源     |
|  3 | tom   | male   |   38 |    202 | 202 | 销售部       |
|  4 | leo   | male   |   18 |    203 | 203 | 运营部       |
|  5 | sky   | male   |   28 |    204 | 204 | 售后部       |
+----+-------+--------+------+--------+-----+--------------+

【4】拼表查询关键字#

-- 内连接 只拼接两张表中共有的数据部分
select * from dep inner join emp on dep_id = dep.id;

+-----+--------------+----+-------+--------+------+--------+
| id  | name         | id | name  | sex    | age  | dep_id |
+-----+--------------+----+-------+--------+------+--------+
| 200 | 技术部       |  1 | hqq   | male   |   18 |    200 |
| 201 | 人力资源     |  2 | green | female |   18 |    201 |
| 202 | 销售部       |  3 | tom   | male   |   38 |    202 |
| 203 | 运营部       |  4 | leo   | male   |   18 |    203 |
| 204 | 售后部       |  5 | sky   | male   |   28 |    204 |
+-----+--------------+----+-------+--------+------+--------+

-- 外连接
-- 左外连接
select * from emp left outer join dep on dep_id = dep.id;

+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | hqq   | male   |   18 |    200 |  200 | 技术部       |
|  2 | green | female |   18 |    201 |  201 | 人力资源     |
|  3 | tom   | male   |   38 |    202 |  202 | 销售部       |
|  4 | leo   | male   |   18 |    203 |  203 | 运营部       |
|  5 | sky   | male   |   28 |    204 |  204 | 售后部       |
|  6 | kevin | male   |   18 |    205 | NULL | NULL         |
|  7 | mark  | male   |   22 |   NULL | NULL | NULL         |
+----+-------+--------+------+--------+------+--------------+

-- 右外连接
select * from emp right outer join dep on dep_id = dep.id;

+------+-------+--------+------+--------+-----+--------------+
| id   | name  | sex    | age  | dep_id | id  | name         |
+------+-------+--------+------+--------+-----+--------------+
|    1 | hqq   | male   |   18 |    200 | 200 | 技术部       |
|    2 | green | female |   18 |    201 | 201 | 人力资源     |
|    3 | tom   | male   |   38 |    202 | 202 | 销售部       |
|    4 | leo   | male   |   18 |    203 | 203 | 运营部       |
|    5 | sky   | male   |   28 |    204 | 204 | 售后部       |
+------+-------+--------+------+--------+-----+--------------+