多表查询
多表查询
【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 | 售后部 |
+------+-------+--------+------+--------+-----+--------------+
