35.MySQL数据库【五】多表查询和子查询

多表查询和子查询

【一】概念

• 子查询：将一条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;