【MySQL】复合查询和内外连接

---

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, myavgfrom emp, (select deptno, avg(sal) as myavg from emp group by deptno) as avg_tbwhere emp.deptno=avg_tb.deptno and sal>myavg;# 显示每个部门的部门名，编号，地点和人员数量
select dname, dept.deptno, loc, cntfrom emp, dept, (select deptno, count(*) cnt from emp group by deptno) as cnt_tbwhere 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/