SQL——子查询

在SQL语言中，一个SELECT－FROM－WHERE语句 称为一个查询块。

子查询(或内层查询)是一个 SELECT 查询,它嵌套在

(1)SELECT、UPDATE、INSERT、DELETE 语句的  WHERE 、

(2)带GROUP BY 的 HAVING 子句内，

(3)或其它子查询中

 (与比较(6个)或逻辑(3个)运算符一起构成查询条件) 子查询的 SELECT 查询总是使用圆括号括起来

(从语法上讲，子查询就是一个用括号括起来的特殊“条件”，它完成的是关系运算，因此，子查询可以出现在允许表达式出现的地方)

1 where嵌套子查询

查询高于平均工资的员工信息。

1：先查询平均工资

mysql> select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+

2：查询高于平均工资

select * from emp 
where sal > (select avg(sal) from emp);

+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+

2 from 嵌套子查询

查询每个部门平均薪资的薪资等级。 

1：查找每个部门的平均薪水，当作临时表t

mysql> select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+

2：将t表和salgrade表连接，条件（t.avgsal between s.losal and s.hisal）

select 
    t.*,s.grade
from
    (select deptno,avg(sal) as avgsal from emp group by deptno) t
join
    salgrade s
on
    t.avgsal between s.losal and s.hisal;
    
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
+--------+-------------+-------+

3 select 嵌套子查询

查询每个员工所在部门的部门名称，显示员工名和部门名.

emp表中ename对应的depnto，dept表中的deptno对应dname

select 
    e.ename,(select d.dname from dept d where e.deptno=d.deptno) as dname
from 
    emp e;
    
    +--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+

多表连接查询 

select 
    e.ename,d.dname
from
    emp e
join
    dept d
on
    e.deptno=d.deptno;
    
    +--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+

 

例56：统计选修了“VB”课程的这些学生的选课门数和平均成绩。

SELECT 
    SNO 学号, count(*) 选课门数,AVG(GRADE) 平均成绩          
from 
    sc 
where  
    sno 
in 
    (Select sno from sc join course c On c.cno=sc.cno Where cname='vb')
Group by 
    sno

不能用多表连接（当查询需分步骤时，只能用子查询.即查询 目标列来源于一张表，但涉及统计函数且条件来源于它表时用子查询而非多表连接）：

#  （结果错误）
select sno 学号, count(*) 选课门数 , avg(grade) 平均成绩
from
    sc 
join 
    course c 
on
    c.cno=sc.cno
where 
    cname='vb'
group by 
    sno;