MySQL从零开始 16-多表查询进阶

本文深入探讨了SQL中的多表查询技巧,包括自连接、子查询等,并通过具体实例展示了如何进行高效的数据库查询。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

上一节介绍了数据库的多表查询,这一节,我们深入了解一下多表查询。

 同样,本次的测试用数据库还是为scott数据库,大家可以在我的GitHub进行scott数据库创建脚本的下载。

 接下来,对多表查询中几个常见的技巧进行介绍。

1. 自连接

自连接是指在同一张表上连接查询。

  • 例1:显示员工FORD的上级领导姓名

 我们先使用常规的子查询进行查找。

mysql> select ename from emp where empno=(select mgr from emp where ename='FORD');
+-------+
| ename |
+-------+
| JONES |
+-------+
1 row in set (0.04 sec)

 对于这个问题我们也可以使用自连接,即将emp表通过别名看做两张表进行查询。

-- 将emp表进行别名操作分成两张表,as可以省略
mysql> select leader.ename from EMP as worker, EMP as leader where worker.mgr=leader.empno and worker.ename='FORD';
+-------+
| ename |
+-------+
| JONES |
+-------+
1 row in set (0.03 sec)

2. 子查询

 子查询在上面的例子中已经用过了,其定义就是指嵌入在其它SQL语句中的select查询语句,也叫做嵌套查询。

 子查询又分为单行子查询,多行子查询和多列子查询。

2.1 单行子查询

 单行子查询即返回一行记录的查询,看下面的例子:

  • 例2:显示SMITH同一部门的员工。
mysql> select * from emp where deptno=(select deptno from emp where ename='SMITH') and ename <> 'SMITH';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
| 007876 | ADAMS | CLERK   | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL |     20 |
| 007902 | FORD  | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
+--------+-------+---------+------+---------------------+---------+------+--------+
4 rows in set (0.00 sec)
2.2 多行子查询

 顾名思义,多行子查询就是返回多行记录的子查询,经常与in,all,any等关键字搭配使用。

  • 例3:如何查询和10号部门的工作相同的雇员的名字,岗位,工资,部门号,但是不包含10自己的。

 关键字in必须符合期内所有字段的要求,搭配子查询如下所示:

mysql> select * from emp where job in(select distinct job from emp where deptno=10) and deptno <> 10;
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
| 007369 | SMITH | CLERK   | 7902 | 1980-12-17 00:00:00 |  800.00 | NULL |     20 |
| 007876 | ADAMS | CLERK   | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL |     20 |
| 007900 | JAMES | CLERK   | 7698 | 1981-12-03 00:00:00 |  950.00 | NULL |     30 |
+--------+-------+---------+------+---------------------+---------+------+--------+
5 rows in set (0.00 sec)
  • 例4:显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号。

 all关键字表示需满足所有条件才能成立,与本例场景符合,使用如下:

mysql> select ename, sal, deptno from emp where sal>all(select sal from emp where deptno=30);
+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| JONES | 2975.00 |     20 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+
4 rows in set (0.03 sec)
  • 例5: 显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号。

 可以搭配any关键字实现需求,如下:

mysql> select ename, sal, deptno from emp where sal > any(select sal from emp where deptno=30) and deptno <> 30;
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| JONES  | 2975.00 |     20 |
| CLARK  | 2450.00 |     10 |
| SCOTT  | 3000.00 |     20 |
| KING   | 5000.00 |     10 |
| ADAMS  | 1100.00 |     20 |
| FORD   | 3000.00 |     20 |
| MILLER | 1300.00 |     10 |
+--------+---------+--------+
7 rows in set (0.00 sec)
2.3 多列子查询

 单行子查询是指子查询只返回单列,单行数据,多行子查询则返回单列多行数据,都是针对单列而言的。而多列子查询则是指查询返回多个列数据的子查询语句。

  • 例6:查询和SMITH的部门和岗位完全相同的所有雇员,不含SMITH本人。
mysql> select ename from EMP where (deptno, job)=(select deptno, job from EMP where ename='SMITH') and ename <> 'SMITH';
+-------+
| ename |
+-------+
| ADAMS |
+-------+
1 row in set (0.00 sec)

3. 在from子句中使用子查询

 我们可以将子查询语句放在from子句中即把一个子查询利用as作为一个临时表来使用,和自连接有些类似。

  • 例7:显示高于自己部门平均工资的员工的信息。
mysql> select ename, emp.deptno, sal, avg_sal from emp, (select avg(sal) as avg_sal, deptno from emp group by deptno) as tmp where emp.deptno=tmp.deptno and emp.sal>tmp.avg_sal;
+-------+--------+---------+-------------+
| ename | deptno | sal     | avg_sal     |
+-------+--------+---------+-------------+
| KING  |     10 | 5000.00 | 2916.666667 |
| JONES |     20 | 2975.00 | 2175.000000 |
| SCOTT |     20 | 3000.00 | 2175.000000 |
| FORD  |     20 | 3000.00 | 2175.000000 |
| ALLEN |     30 | 1600.00 | 1566.666667 |
| BLAKE |     30 | 2850.00 | 1566.666667 |
+-------+--------+---------+-------------+
6 rows in set (0.03 sec)
  • 例8:查找每个部门工资最高的人的详细资料。
mysql> select emp.ename, emp.deptno, emp.job, max_sal from emp, (select max(sal) as max_sal, deptno from emp group by deptno) as tmp where emp.deptno=tmp.deptno and emp.sal=tmp.max_sal;
+-------+--------+-----------+---------+
| ename | deptno | job       | max_sal |
+-------+--------+-----------+---------+
| BLAKE |     30 | MANAGER   | 2850.00 |
| SCOTT |     20 | ANALYST   | 3000.00 |
| KING  |     10 | PRESIDENT | 5000.00 |
| FORD  |     20 | ANALYST   | 3000.00 |
+-------+--------+-----------+---------+
4 rows in set (0.02 sec)
  • 例9:显示每个部门的信息(部门名,编号,地址)和人员数量。
mysql> select dname, dept.deptno, loc, tmp.count from dept, (select count(*) as count, deptno from emp group by deptno) as tmp where dept.deptno=tmp.deptno;
+------------+--------+----------+-------+
| dname      | deptno | loc      | count |
+------------+--------+----------+-------+
| ACCOUNTING |     10 | NEW YORK |     3 |
| RESEARCH   |     20 | DALLAS   |     5 |
| SALES      |     30 | CHICAGO  |     6 |
+------------+--------+----------+-------+
3 rows in set (0.00 sec)

 这个问题其实用多表查询更为方便一点。

mysql> select dname, emp.deptno, loc, count(*) from emp, dept where emp.deptno=dept.deptno group by emp.deptno;
+------------+--------+----------+----------+
| dname      | deptno | loc      | count(*) |
+------------+--------+----------+----------+
| ACCOUNTING |     10 | NEW YORK |        3 |
| RESEARCH   |     20 | DALLAS   |        5 |
| SALES      |     30 | CHICAGO  |        6 |
+------------+--------+----------+----------+
3 rows in set (0.11 sec)

 在实际查询中,我们要根据具体的场景进行查询方式的选择。

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值