文章目录
MySQL复合查询和内外连接
1. 复合查询
1.1 多表查询
select * from `tb_1`, `tb_2`
where dept.deptno = emp.deptno;
不加过滤条件，得到的结果被称为笛卡尔积。本质是取主表的每一条记录拼接上从表的每一条记录。
多表查询中一定会包含where条件，将主键和外键对应，可以将无意义的记录抛弃。
1.2 自连接
同一张表自己和自己拼接的叫做自连接。给两张相同的表起别名，就可以放到一起。
select * from emp wkr, emp ldr;
where wkr.mgr = ldr.empno;
自连接的话，需要根据实际需求筛去不满足条件的数据。
1.3 子查询
子查询也称嵌套查询，也就是将select的结果作为另一个select的条件。
单行子查询
返回一行记录的查询称为单行子查询。
select * from emp where deptno=(select deptno from emp where ename='SMITH');
多行子查询
返回多行记录的查询称为多行子查询。关键字有：in，all，any。
- in 表示是否存在于集合中，存在即满足条件。
- all 表示整个集合的每一个结果
- any 表示集合中的任意一个结果
# 显示和10号部门具有的工作岗位相同的员工
select * from emp where job in (select distinct job from emp where deptno=10);
# 显示工资比10号部门所有人工资都高的员工
select * from emp where sal > all (select distinct sal from emp where deptno=10);
# 显示工资比10号部门任意员工工资高的员工
select * from emp where sal > any (select distinct sal from emp where deptno=10);
不管是单行还是多行子查询，都叫做单列子查询，返回的都是单列的一个字段的数据。
多列子查询
# 查询和SMITH的部门和岗位完全相同的所有雇员
select * from emp where (deptno, job)=(select deptno, job from emp where ename='SMITH');
from中使用子查询
from跟是的表名，既然我们查询出来的记录都可以看作表结构。这里就是一个数据查询的技巧，把子查询当作临时表使用。
# 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
select ename, emp.deptno, sal, myavg
from emp,
(select deptno, avg(sal) as myavg from emp group by deptno) as avg_tb
where emp.deptno=avg_tb.deptno and sal>myavg;
# 显示每个部门的部门名，编号，地点和人员数量
select dname, dept.deptno, loc, cnt
from emp, dept,
(select deptno, count(*) cnt from emp group by deptno) as cnt_tb
where cnt_tb.deptno=emp.deptno and cnt_tb.deptno=dept.deptno;
合并查询
合并查询是就是合并多个 select 的查询结果，可使用集合操作符 union，union all。
| 关键字 | 解释 |
|---|---|
| union | 取并集，将多个 select 结果合并到一起，自动去掉重复行 |
| union all | 取并集，将多个 select 结果合并到一起，但不去重 |
select * from emp where sal > 2500 union select * from emp where job='MANAGER';
select * from emp where sal > 2500 union all select * from emp where job='MANAGER';
OJ连接
- https://www.nowcoder.com/practice/23142e7a23e4480781a3b978b5e0f33a
- https://www.nowcoder.com/practice/355036f7f0c8429a85281f7ac05b457a
- https://www.nowcoder.com/practice/32c53d06443346f4a2f2ca733c19660c
- https://www.nowcoder.com/practice/e50d92b8673a440ebdf3a517b5b37d62
2. 内外连接
通过不同类型的表连接，可以将多个表中的数据有效地组合在一起，以满足复杂的查询需求。将详细介绍MySQL中常见的表连接方式，分别是 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN 和 CROSS JOIN，并通过具体的表格数据展示它们的用法和特点。
下表总结了各种表连接方式的主要特点：
| 连接方式 | 结果 | 匹配条件不满足时 | 备注 |
|---|---|---|---|
| INNER JOIN | 返回符合连接条件的行 | 不返回结果 | 最常用的连接方式 |
| LEFT JOIN | 返回左表中所有行，以及右表中符合条件的行 | 右表列为NULL | 适用于需要左表所有行的情况 |
| RIGHT JOIN | 返回右表中所有行，以及左表中符合条件的行 | 左表列为NULL | 适用于需要右表所有行的情况 |
| FULL JOIN | 返回左右表中所有行，不管是否有匹配的行 | NULL值 | 适用于需要两个表所有行的情况 |
| CROSS JOIN | 返回两个表的笛卡尔积 | 无 | 适用于需要生成所有组合的情况 |
我们将使用以下两个示例表格进行表连接操作：
表格：employees
| employee_id | employee_name | department_id |
|---|---|---|
| 1 | Alice | 1 |
| 2 | Bob | 2 |
| 3 | Charlie | NULL |
| 4 | David | 1 |
表格：departments
| department_id | department_name |
|---|---|
| 1 | HR |
| 2 | IT |
| 3 | Finance |
1. INNER JOIN
INNER JOIN 是最常见的表连接方式，它返回两个表中符合连接条件的行。
**示例查询及结果：**查询员工及其所在部门（基于employees和departments表格）。
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
| employee_id | employee_name | department_name |
|---|---|---|
| 1 | Alice | HR |
| 2 | Bob | IT |
| 4 | David | HR |
2. LEFT JOIN
LEFT JOIN 返回左表中的所有行，以及右表中与左表中行匹配的行。如果右表中没有匹配的行，则会返回 NULL 值。
**示例查询及结果：**查询所有员工及其所在部门（基于employees和departments表格）。
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
| employee_id | employee_name | department_name |
|---|---|---|
| 1 | Alice | HR |
| 2 | Bob | IT |
| 3 | Charlie | NULL |
| 4 | David | HR |
3. RIGHT JOIN
RIGHT JOIN 返回右表中的所有行，以及左表中与右表中行匹配的行。如果左表中没有匹配的行，则会返回 NULL 值。
**示例查询及结果：**查询所有部门及其员工（基于employees和departments表格）。
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
| employee_id | employee_name | department_name |
|---|---|---|
| 1 | Alice | HR |
| 2 | Bob | IT |
| 4 | David | HR |
| NULL | NULL | Finance |
4. FULL JOIN
FULL JOIN 返回左右两个表中的所有行，如果某个表中没有匹配的行，则返回 NULL 值。
**示例查询及结果：**查询所有员工及其所在部门，包括没有部门的员工和没有员工的部门（基于employees和departments表格）。
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
FULL JOIN departments d ON e.department_id = d.department_id;
| employee_id | employee_name | department_name |
|---|---|---|
| 1 | Alice | HR |
| 2 | Bob | IT |
| 3 | Charlie | NULL |
| 4 | David | HR |
| NULL | NULL | Finance |
5. CROSS JOIN
CROSS JOIN 返回两个表的笛卡尔积，即左表中的每一行与右表中的每一行组合在一起。
如果执行一个简单的 CROSS JOIN 操作，假设表 orders 和表 products，它们分别有以下数据：
表格：orders
| order_id | order_name |
|---|---|
| 1 | Order A |
| 2 | Order B |
表格：products
| product_id | product_name |
|---|---|
| 101 | Product X |
| 102 | Product Y |
| 103 | Product Z |
执行如下的 CROSS JOIN 查询：
SELECT o.order_id, p.product_id, p.product_name
FROM orders o
CROSS JOIN products p;
将会得到以下结果，这是两个表的笛卡尔积：
| order_id | product_id | product_name |
|---|---|---|
| 1 | 101 | Product X |
| 1 | 102 | Product Y |
| 1 | 103 | Product Z |
| 2 | 101 | Product X |
| 2 | 102 | Product Y |
| 2 | 103 | Product Z |
这个结果显示了 orders 表中每个订单与 products 表中每个产品的所有组合。笛卡尔积操作是一种非常基础但强大的数据组合方式，通常用于需要生成所有可能组合的场景。
OJ练习
- https://leetcode.com/problems/rank-scores/
- https://leetcode.com/problems/exchange-seats/description/