多表查询和子查询
【一】概念
-
子查询：将一条SQL语句的查询结果加括号当做另外一条SQL语句的查询结果
-
链表查询：先将多张表拼接到一起，形成一张大表，让后基于单表查询获取数据
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
);
# 插数据
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);
# 查看数据
mysql> select * from dep;
+-----+--------------+
| id | name |
+-----+--------------+
| 200 | 技术部 |
| 201 | 人力资源 |
| 202 | 销售部 |
| 203 | 运营部 |
| 204 | 售后部 |
+-----+--------------+
5 rows in set (0.00 sec)
mysql> 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）获取某一员工所在部门
1.先获取员工的部门编号
select dep_id from emp where name='dream';
mysql> 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');
mysql> select name from dep where id=(select dep_id from emp where name='dream');
+-----------+
| name |
+-----------+
| 技术部 |
+-----------+
1 row in set (0.00 sec)
2）查询部门是计数
1.先获取部门id
select id from dep where name ="技术部";
mysql> select id from dep where name ="技术部";
+-----+
| id |
+-----+
| 200 |
+-----+
1 row in set (0.00 sec)
2.将结果作为查询条件
select * from emp where dep_id in (select id from dep where name ="技术部");
mysql> select * from emp where dep_id in (select id from dep where name ="技术部");
+----+-------+------+------+--------+
| id | name | sex | age | dep_id |
+----+-------+------+------+--------+
| 1 | dream | male | 18 | 200 |
+----+-------+------+------+--------+
1 row in set (0.00 sec)
【二】联表查询
1）笛卡尔积概念
- 在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;
3）拼表升级
- 只显示符合条件的组合
select * from dep,emp where emp.dep_id = dep.id;
4）拼表关键字
1.inner join（内连接）
- 只拼两表共有的数据
select * from emp inner join dep on emp.dep_id=dep.id;
2.left join（左连接）
- 展示左表所有数据，没有对应数据则用null表示
select * from emp left join dep on emp.dep_id=dep.id;
3.right join(右连接)
- 展示右表所有数据，没有对应数据则用null表示
select * from emp right join dep on emp.dep_id=dep.id;
4.union(全连接)
左右两表的数据都展示出来，没有对应数据则用null表示
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;