11 复合查询上

小米里的大麦2025-11-292025-11-29

复合查询上

1. 基本查询回顾

首先我们先创建下面的表数据：

mysql> create database Compound_query;
mysql> use Compound_query;

mysql> CREATE TABLE emp(
    ->   empno CHAR(6), 
    ->   ename VARCHAR(20),
    ->   job VARCHAR(20),
    ->   mgr INT,
    ->   hiredate DATE,
    ->   sal DECIMAL(7,2),
    ->   comm DECIMAL(7,2),
    ->   deptno INT
    -> );
    
mysql> insert into emp values
    -> ('007369','SMITH','CLERK',7902,'1980-12-17',800,NULL,20),
    -> ('007499','ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30),
    -> ('007521','WARD','SALESMAN',7698,'1981-02-22',1250,500,30),
    -> ('007566','JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),
    -> ('007654','MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),
    -> ('007698','BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),
    -> ('007782','CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10),
    -> ('007788','SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20),
    -> ('007839','KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),
    -> ('007844','TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),
    -> ('007876','ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20),
    -> ('007900','JAMES','CLERK',7698,'1981-10-03',950,NULL,30),
    -> ('007902','FORD','ANALYST',7566,'1981-05-03',3000,NULL,20),
    -> ('007934','MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
Query OK, 14 rows affected (0.01 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+--------+--------+-----------+------+------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+--------+--------+-----------+------+------------+---------+---------+--------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-10-03 |  950.00 |    NULL |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-05-03 | 3000.00 |    NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+------------+---------+---------+--------+

查询工资高于 500 或岗位为 MANAGER 的雇员，同时还要满足他们的姓名首字母为大写的 J

mysql> select ename,job,sal from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';
+-------+---------+---------+
| ename | job     | sal     |
+-------+---------+---------+
| JONES | MANAGER | 2975.00 |
| JAMES | CLERK   |  950.00 |
+-------+---------+---------+

按照部门号升序而雇员的工资降序排序

mysql> select * from emp order by deptno asc, sal desc;
+--------+--------+-----------+------+------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+--------+--------+-----------+------+------------+---------+---------+--------+
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-05-03 | 3000.00 |    NULL |     20 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-10-03 |  950.00 |    NULL |     30 |
+--------+--------+-----------+------+------------+---------+---------+--------+

使用年薪进行降序排序

mysql> select *, (sal + ifnull(null,0))*12 as '年薪'  from emp order by 年薪 desc;
mysql> select *, (sal + ifnull(null,0))*12 '年薪'  from emp order by 年薪 desc;	# as可省略
+--------+--------+-----------+------+------------+---------+---------+--------+----------+
| empno  | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno | 年薪     |
+--------+--------+-----------+------+------------+---------+---------+--------+----------+
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | 60000.00 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 | 36000.00 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-05-03 | 3000.00 |    NULL |     20 | 36000.00 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | 35700.00 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | 34200.00 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | 29400.00 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | 19200.00 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | 18000.00 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | 15600.00 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | 15000.00 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | 15000.00 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 | 13200.00 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-10-03 |  950.00 |    NULL |     30 | 11400.00 |
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |  9600.00 |
+--------+--------+-----------+------+------------+---------+---------+--------+----------+

显示工资最高的员工的名字和工作岗位

mysql> select ename,job from emp where sal = (select max(sal) from emp);
mysql> select ename,job from emp order by sal desc limit 1;
+-------+-----------+
| ename | job       |
+-------+-----------+
| KING  | PRESIDENT |
+-------+-----------+

显示工资高于平均工资的员工信息

mysql> select * from emp where sal > (select avg(sal) from emp);
+--------+-------+-----------+------+------------+---------+------+--------+
| empno  | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+--------+-------+-----------+------+------------+---------+------+--------+
| 007566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
| 007698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
| 007782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
| 007788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
| 007839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
| 007902 | FORD  | ANALYST   | 7566 | 1981-05-03 | 3000.00 | NULL |     20 |
+--------+-------+-----------+------+------------+---------+------+--------+

显示每个部门的平均工资和最高工资

mysql> select deptno, avg(sal), max(sal) from emp group by deptno;
+--------+-------------+----------+
| deptno | avg(sal)    | max(sal) |
+--------+-------------+----------+
|     20 | 2175.000000 |  3000.00 |
|     30 | 1566.666667 |  2850.00 |
|     10 | 2916.666667 |  5000.00 |
+--------+-------------+----------+

mysql> select deptno, format(avg(sal),2) as '平均工资', max(sal) '最高工资' from emp group by deptno;
+--------+--------------+--------------+
| deptno | 平均工资     | 最高工资     |
+--------+--------------+--------------+
|     20 | 2,175.00     |      3000.00 |
|     30 | 1,566.67     |      2850.00 |
|     10 | 2,916.67     |      5000.00 |
+--------+--------------+--------------+

显示平均工资低于 2000 的部门号和它的平均工资

mysql> select deptno, avg(sal) from emp group by deptno having avg(sal) < 2000;
mysql> select deptno, avg_sal
from (
    # 先按部门分组计算每个部门的平均薪水
    select deptno, avg(sal) as avg_sal
    from emp
    group by deptno
) t
# 筛选平均薪水 < 2000 的部门
where avg_sal < 2000;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     30 | 1566.666667 |
+--------+-------------+

显示每种岗位的雇员总数，平均工资

mysql> select job, count(*), avg(sal) from emp group by job;
+-----------+----------+-------------+
| job       | count(*) | avg(sal)    |
+-----------+----------+-------------+
| CLERK     |        4 | 1037.500000 |
| SALESMAN  |        4 | 1400.000000 |
| MANAGER   |        3 | 2758.333333 |
| ANALYST   |        2 | 3000.000000 |
| PRESIDENT |        1 | 5000.000000 |
+-----------+----------+-------------+

mysql> select job as '岗位', count(1) as '雇员总数', format(avg(sal), 2) as '平均薪资' from emp group by job;
+-----------+--------------+--------------+
| 岗位      | 雇员总数     | 平均薪资     |
+-----------+--------------+--------------+
| CLERK     |            4 | 1,037.50     |
| SALESMAN  |            4 | 1,400.00     |
| MANAGER   |            3 | 2,758.33     |
| ANALYST   |            2 | 3,000.00     |
| PRESIDENT |            1 | 5,000.00     |
+-----------+--------------+--------------+

2. 多表查询

创建另一张表数据：

mysql> create table dept (
    ->     deptno int primary key,
    ->     dname varchar(14),
    ->     loc varchar(13)
    -> );

mysql> insert into dept values 
    -> (10, 'ACCOUNTING', 'NEW YORK'),
    -> (20, 'RESEARCH', 'DALLAS'),
    -> (30, 'SALES', 'CHICAGO'),
    -> (40, 'OPERATIONS', 'BOSTON');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

1. 多表查询概念

多表查询就是从 不止一个表 里查数据，比如要把表 A 和表 B 两个表的数据组合起来看。在进行多表查询时，只需要将多张表的表名依次放到 from 子句之后，用逗号隔开即可，这时 MySQL 将会对给定的这多张表取笛卡尔积，作为多表查询的初始数据源。

2. 笛卡尔积（最简单的多表查询）

笛卡尔积就是最原始、最“野蛮”的多表组合方式：不管两个表有没有关联条件，所有的组合都会组合出来。 其本质就是对给定的多张表取笛卡尔积，然后在笛卡尔积中进行查询。比如：表 A 有 10 行，表 B 有 20 行，那么结果：10 × 20 = 200 行，每行是 A 的每一行与 B 的每一行全组合。

1
2
3

# 笛卡尔积（最基础、最危险的多表查询）
SELECT * FROM 表A, 表B;          # 写法1：隐式笛卡尔积（逗号分隔）
SELECT * FROM 表A CROSS JOIN 表B;  # 写法2：显式（MySQL 8 支持）

3. 示例

综合上述，便能得出：

mysql> show tables;
+--------------------------+
| Tables_in_Compound_query |
+--------------------------+
| dept                     |
| emp                      |
+--------------------------+

mysql> select * from emp,dept;
+--------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| empno  | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno | deptno | dname      | loc      |
+--------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |     40 | OPERATIONS | BOSTON   |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |     20 | RESEARCH   | DALLAS   |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |     10 | ACCOUNTING | NEW YORK |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |     40 | OPERATIONS | BOSTON   |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |     20 | RESEARCH   | DALLAS   |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |     10 | ACCOUNTING | NEW YORK |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |     40 | OPERATIONS | BOSTON   |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |     20 | RESEARCH   | DALLAS   |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |     10 | ACCOUNTING | NEW YORK |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |     40 | OPERATIONS | BOSTON   |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |     20 | RESEARCH   | DALLAS   |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |     10 | ACCOUNTING | NEW YORK |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |     40 | OPERATIONS | BOSTON   |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |     30 | SALES      | CHICAGO  |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |     20 | RESEARCH   | DALLAS   |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |     40 | OPERATIONS | BOSTON   |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |     30 | SALES      | CHICAGO  |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |     20 | RESEARCH   | DALLAS   |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |     40 | OPERATIONS | BOSTON   |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |     20 | RESEARCH   | DALLAS   |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |     10 | ACCOUNTING | NEW YORK |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
| 007900 | JAMES  | CLERK     | 7698 | 1981-10-03 |  950.00 |    NULL |     30 |     40 | OPERATIONS | BOSTON   |
| 007900 | JAMES  | CLERK     | 7698 | 1981-10-03 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
| 007900 | JAMES  | CLERK     | 7698 | 1981-10-03 |  950.00 |    NULL |     30 |     20 | RESEARCH   | DALLAS   |
| 007900 | JAMES  | CLERK     | 7698 | 1981-10-03 |  950.00 |    NULL |     30 |     10 | ACCOUNTING | NEW YORK |
| 007902 | FORD   | ANALYST   | 7566 | 1981-05-03 | 3000.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
| 007902 | FORD   | ANALYST   | 7566 | 1981-05-03 | 3000.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
| 007902 | FORD   | ANALYST   | 7566 | 1981-05-03 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007902 | FORD   | ANALYST   | 7566 | 1981-05-03 | 3000.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |     40 | OPERATIONS | BOSTON   |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |     30 | SALES      | CHICAGO  |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |     20 | RESEARCH   | DALLAS   |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
+--------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+

这个组合 是笛卡尔积 虽然是正确的，但 没有意义，属于无用全组合，仔细观看能发现 deptno 列看起来不太一样：

emp 表有自己的 deptno（员工所属部门，如 10,20,30）
dept 表也有 deptno（部门编号，如 10,20,30,40）
没有 ON 条件 → 两表的 deptno 独立重复出现，列名相同但内容不匹配，导致输出中 deptno（emp 的）和 deptno（dept 的）并排。

所以：deptno 重复是正常现象，因为两表列名一样却没关联。下一步需要加 JOIN ON 过滤。

4. JOIN ON 过滤

所谓 JOIN ON 过滤，其实就是把笛卡尔积限制成有意义的匹配行，只保留两表满足条件的组合。

JOIN ... ON ... 表示联表 + 条件过滤。
JOIN：把两个表“凑到一起”
ON：指定怎么凑（条件）
如果不写 ON，就是笛卡尔积，啥也不管，直接全组合，写了 ON，就只凑符合条件的行。

语法：

1
2

SELECT 列... FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段;		# INNER 表示内连接，双方都有才显示（后文会讲到）
SELECT 列名 FROM 表1 JOIN 表2 ON 表1.列名 = 表2.列名;		# 更简短直接

mysql> select * from emp e, dept d where e.deptno=d.deptno;		# 旧写法（逗号 + WHERE）= 隐式内连接
mysql> select * from emp inner join dept on emp.deptno = dept.deptno;	# 新写法（JOIN ON）= 显式内连接（清新、可读性强）
+--------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| empno  | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno | deptno | dname      | loc      |
+--------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007900 | JAMES  | CLERK     | 7698 | 1981-10-03 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
| 007902 | FORD   | ANALYST   | 7566 | 1981-05-03 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
+--------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+

5. 示例

显示部门号为 10 的部门名，员工名和工资

mysql> select dname,ename,sal from emp,dept where emp.deptno = dept.deptno and dept.deptno = 10;
+------------+--------+---------+
| dname      | ename  | sal     |
+------------+--------+---------+
| ACCOUNTING | CLARK  | 2450.00 |
| ACCOUNTING | KING   | 5000.00 |
| ACCOUNTING | MILLER | 1300.00 |
+------------+--------+---------+

显示员工姓名、工资及部门名称

mysql> select ename '姓名', sal '工资', dname '部门名称' from emp inner join dept on emp.deptno=dept.deptno;
+--------+---------+--------------+
| 姓名   | 工资    | 部门名称     |
+--------+---------+--------------+
| SMITH  |  800.00 | RESEARCH     |
| ALLEN  | 1600.00 | SALES        |
| WARD   | 1250.00 | SALES        |
| JONES  | 2975.00 | RESEARCH     |
| MARTIN | 1250.00 | SALES        |
| BLAKE  | 2850.00 | SALES        |
| CLARK  | 2450.00 | ACCOUNTING   |
| SCOTT  | 3000.00 | RESEARCH     |
| KING   | 5000.00 | ACCOUNTING   |
| TURNER | 1500.00 | SALES        |
| ADAMS  | 1100.00 | RESEARCH     |
| JAMES  |  950.00 | SALES        |
| FORD   | 3000.00 | RESEARCH     |
| MILLER | 1300.00 | ACCOUNTING   |
+--------+---------+--------------+

显示部门名称、部门地点，以及该部门所有员工的平均工资

# FROM dept d —— 先把 dept 表取个别名叫 d
# LEFT JOIN emp e ON d.deptno = e.deptno —— 把 emp 表取名叫 e，左连接，只按部门编号匹配
# SELECT d.dname, d.loc, AVG(e.sal) —— 用 d. 访问 dept 的列，用 e. 访问 emp 的列，算平均工资
# GROUP BY d.deptno, d.dname, d.loc —— 按部门分组（每个部门一行），前面算的平均工资就是每个组的
# 顺序就是：先找表 → 连接表 → 取需要的列 → 按部门分组算结果
mysql> select d.dname as '部门名称', d.loc as '部门地点', avg(e.sal) as '平均工资' from dept d inner join emp e on d.deeptno=e.deptno group by d.deptno;
mysql> select d.dname as '部门名称', d.loc as '部门地点', avg(e.sal) as '平均工资' from dept d left join emp e on d.deptno=e.deptno group by d.deptno,d.dname,d.loc;
+--------------+--------------+--------------+
| 部门名称     | 部门地点     | 平均工资     |
+--------------+--------------+--------------+
| RESEARCH     | DALLAS       |  2175.000000 |
| SALES        | CHICAGO      |  1566.666667 |
| ACCOUNTING   | NEW YORK     |  2916.666667 |
+--------------+--------------+--------------+

3. 自连接

1. 自连接概念

自连接其实就是 自己跟自己联表/同一张表自己连自己，说白了就是：表 A 跟表 A 自己凑在一起查。总之 ：一张表里有“指来指去”的字段，就用自连接查关系。

作用：查询表内有层级/关系的数据，比如：

员工和他的经理（mgr 指向 empno）
上下级部门
分类的父子关系
最常见的是 上下级、父子、同组的关系。

本质： 还是笛卡尔积 + 过滤，即：自连接 = 笛卡尔积 + 条件过滤。先把表 × 自己（笛卡尔积），再用 ON 条件过滤出有关系的行。

2. 基本语法

SELECT a.列名, b.列名
FROM 表名 a        # 给自己起个别名 a
JOIN 表名 b        # 还是自己，起个别名 b（如果不写 INNER，默认就是内连接）
  ON a.关联列 = b.关联列;  # 匹配关系
  
# 或者：要是想指定连接类型（比如只要匹配上的，或者都要），就加个 INNER 或 LEFT：
SELECT 列...
FROM 表名 别名1
[INNER/LEFT] JOIN 表名 别名2
ON 别名1.关联字段 = 别名2.关联字段;

注意事项：

必须给别名： 不给别名，SQL 直接报错，因为它分不清左右两边的“自己”。
INNER 和 LEFT 的区别（看自己想“保谁”）：
- INNER JOIN（内连接）：
  - 逻辑： 只有两边都有的，才查出来。
  - 例子： 查“有经理的员工”。如果老板没有经理，那老板这行数据 查不到。
- LEFT JOIN（左连接）：
  - 逻辑： 以左边的表为主，左边全都要。右边要是找不到匹配的，就填空（NULL）。
  - 例子： 查“所有员工，顺便看看经理是谁”。老板也能查到，只是经理那一栏是空的。

别名不能重： 千万别写 FROM 表 a JOIN 表 a，报错警告你。必须写成 a 和 b。
条件别漏写： 如果 ON 里的条件没写对，或者干脆忘了写，数据库会把表里的每一行都和其他每一行配一遍（笛卡尔积）。本来 100 行，瞬间变 10000 行，数据库容易卡死。
别名别搞混： 既然分了 a 和 b，后面查的时候就得说清楚，比如 a.name 是员工名，b.name 是经理名，别混着写。

3. 示例

查出每位有上司的员工姓名，以及他/她的直属经理姓名

# 查有上司的员工和上司名字：把员工表起别名e，经理表起别名m，通过mgr指向empno连接
mysql> select e.ename as 员工,m.ename as 经理 from emp e inner join emp m on e.mgr=m.empno;
+--------+--------+
| 员工   | 经理   |
+--------+--------+
| FORD   | JONES  |
| SCOTT  | JONES  |
| JAMES  | BLAKE  |
| TURNER | BLAKE  |
| MARTIN | BLAKE  |
| WARD   | BLAKE  |
| ALLEN  | BLAKE  |
| MILLER | CLARK  |
| ADAMS  | SCOTT  |
| CLARK  | KING   |
| BLAKE  | KING   |
| JONES  | KING   |
| SMITH  | FORD   |
+--------+--------+

找出所有“没有直属下属”的员工（即没人汇报给他/她）

# 查没下属的员工
select e.ename as 员工姓名，e.job as 岗位
from emp e
left join emp sub on e.empno = sub.mgr  # 左连接，保所有员工
where sub.empno is null;  # 右边没匹配上，说明没人向他汇报
+--------------+----------+
| 员工姓名     | 岗位     |
+--------------+----------+
| SMITH        | CLERK    |
| ALLEN        | SALESMAN |
| WARD         | SALESMAN |
| MARTIN       | SALESMAN |
| TURNER       | SALESMAN |
| ADAMS        | CLERK    |
| JAMES        | CLERK    |
| MILLER       | CLERK    |
+--------------+----------+

显示“经理薪水低于自己下属平均薪水”的经理信息

# 查经理工资低于下属平均的
select m.ename as 经理姓名，m.sal as 经理薪资，
       round(avg(e.sal), 2) as 下属平均薪资，
       count(e.empno) as 下属人数
from emp m
inner join emp e on m.empno = e.mgr  # 经理连员工，找上下级关系
group by m.empno, m.ename, m.sal  # 按经理分组，算每个经理的下属数据
having m.sal < avg(e.sal);  # 分组后筛选，经理工资低于下属平均的
+--------------+--------------+--------------------+--------------+
| 经理姓名     | 经理薪资     | 下属平均薪资       | 下属人数     |
+--------------+--------------+--------------------+--------------+
| JONES        |      2975.00 |            3000.00 |            2 |
+--------------+--------------+--------------------+--------------+