SQL Fundamentals: 子查询 || WHERE,HAVING,FROM,SELECT子句中使用子查询,WITH子句

SQL Fundamentals || Oracle SQL语言

子查询(基础)

1、认识子查询

2、WHERE子句中使用子查询

3、在HAVING子句中使用子查询

4、在FROM子句中使用子查询

5、在SELECT子句中使用子查询

6、WITH子句

子查询(进阶)

7、分析函数

8、行列转换

9、设置数据层次

一、认识子查询

  • 子查询就是指的在一个完整的查询语句之中,嵌套若干个不同功能的小查询,从而一起完成复杂查询(复杂查询=限定查询+多表查询+统计查询+子查询)的一种编写形式,为了让读者更加清楚子查询的概念。
  • 一个查询语句内部可以定义多个子查询;
  • 子查询一般在WHERE、FROM、HAVING子句之中出现较多,也可以在SELECT子句中出现.

例子1、查询公司中工资最低的雇员的完整信息

分析:

1.查询最低工资:SELECT MIN(sal) FROM emp;

2.查询等于最低工资的雇员的信息:

SELECT * FROM emp

WHERE sal=(

SELECT MIN(sal) FROM emp;);

子查询可以返回的结果数据类型一共分为四种:

单行单列

返回的是一个具体列的内容,可以理解为一个单值数据;

单行多列

返回一行数据中多个列的内容;

多行单列

返回多行记录之中同一列的内容,相当于给出了一个操作范围;

多行多列

查询返回的结果是一张临时表;

子查询语法:

SELECT [DISTINCT] * | 分组字段1 [AS] [列别名] , [分组字段2 [AS] [列别名] , …],(

SELECT [DISTINCT] * | 分组字段1 [AS] [列别名] , [分组字段2 [AS] [列别名] , …]

FROM 表名称1 [表别名1] , 表名称2 [表别名2] ….

[WHERE 条件(s)]

[GROUP BY 分组字段1 , 分组字段2 , ….]

[HAVING 过滤条件(s)]

[ORDER BY 排序字段 ASC|DESC]) ...

FROM 表名称1 [表别名1] , 表名称2 [表别名2] …, (

SELECT [DISTINCT] * | 分组字段1 [AS] [列别名] , [分组字段2 [AS] [列别名] , …]

FROM 表名称1 [表别名1] , 表名称2 [表别名2] ….

[WHERE 条件(s)]

[GROUP BY 分组字段1 , 分组字段2 , ….]

[HAVING 过滤条件(s)]

[ORDER BY 排序字段 ASC|DESC])

[WHERE 条件(s) ... (

SELECT [DISTINCT] * | 分组字段1 [AS] [列别名] , [分组字段2 [AS] [列别名] , …]

FROM 表名称1 [表别名1] , 表名称2 [表别名2] ….

[WHERE 条件(s)]

[GROUP BY 分组字段1 , 分组字段2 , ….]

[HAVING 过滤条件(s)]

[ORDER BY 排序字段 ASC|DESC])]

[GROUP BY 分组字段1 , 分组字段2 , ….]

[HAVING 过滤条件(s)...(

SELECT [DISTINCT] * | 分组字段1 [AS] [列别名] , [分组字段2 [AS] [列别名] , …]

FROM 表名称1 [表别名1] , 表名称2 [表别名2] ….

[WHERE 条件(s)]

[GROUP BY 分组字段1 , 分组字段2 , ….]

[HAVING 过滤条件(s)]

[ORDER BY 排序字段 ASC|DESC])]

[ORDER BY 排序字段 ASC|DESC] ;

子查询常见操作:

WHERE子句

此时子查询返回的结果一般都是

单行单列、单行多列、多行单列;

HAVING子句

此时子查询返回的都是

单行单列数据,同时为了使用统计函数操作;

FROM子句

此时子查询返回的结果一般都是

多行多列,可以按照一张数据表(临时表)的形式操作。

二、WHERE子句中使用子查询

  • 可以在WHERE子句之中处理单行单列子查询、多行单列子查询、单行多列子查询。
  • WHERE子句可以判断单个数值、多个数值;
  • 使用IN、ANY、ALL可以处理多行单列子查询;
  • 利用EXISTS()可以判断查询结果是否为null。

1、单行单列(子句中只有一行,一列)

例子1:查询出基本工资比ALLEN低的全部雇员信息

SELECT * FROM emp

WHERE sal< (

SELECT sal FROM emp WHERE ename='ALLEN') ;

例子2:查询基本工资高于公司平均薪金的全部雇员信息

SELECT * FROM emp WHERE sal>(

SELECT AVG(sal) FROM emp) ;

例子3:查找出与ALLEN从事同一工作,并且基本工资高于雇员编号为7521的全部雇员信息

SELECT * FROM emp

WHERE job=(SELECT job FROM emp WHERE ename='ALLEN')

AND sal>(SELECT sal FROM emp WHERE empno=7521) ;

2、单行多列(子句中有一行SCOTT,多列sal和job)

例子1:查询与SCOTT从事同一工作且工资相同的雇员信息

SELECT * FROM emp WHERE (job,sal)=(

SELECT job,sal FROM emp WHERE ename='SCOTT') AND ename<>'SCOTT' ;

例子2:查询与雇员7566从事同一工作且领导相同的全部雇员信息

SELECT * FROM emp WHERE ( job,mgr)=(

SELECT job,mgr FROM emp WHERE empno=7566)

AND empno<>7566 ;

例子3:查询与ALLEN从事同一工作且在同一年雇佣的全部雇员信息(包括ALLEN)

SELECT * FROM emp

WHERE (job, TO_CHAR(hiredate, 'yyyy'))=(

SELECT job, TO_CHAR(hiredate, 'yyyy')

FROM emp WHERE ename='ALLEN') ;

3、多行单列

在使用多行子查询时,主要使用三种操作符:IN、ANY、ALL

IN和NOT IN

  • 如果在IN中子查询返回的数值有null,那么不会有影响,如果NOT IN中子查询返回数据有null,那么就表示不会有任何数据返回

例子1:查询出与每个部门中最低工资相同的全部雇员信息

SELECT * FROM emp

WHERE sal IN (

SELECT MIN(sal) FROM emp GROUP BY deptno) ;

例子2:查询出不与每个部门中最低工资相同的全部雇员信息

SELECT * FROM emp

WHERE sal NOT IN (

SELECT MIN(sal) FROM emp GROUP BY deptno) ;

ANY(SOME)

=ANY

表示与子查询中的每个元素进行比较,功能与IN类似(然而<>ANY不等价于NOT IN);

      • 使用=ANY操作符完成查询

SELECT * FROM emp

WHERE sal=ANY (

SELECT MIN(sal)

FROM emp

WHERE job='MANAGER'

GROUP BY deptno) ;

>ANY

比子查询中返回结果的最小的要大(还包含了>=ANY);

      • 使用>ANY操作符完成查询

SELECT * FROM emp

WHERE sal >ANY (

SELECT MIN(sal)

FROM emp

WHERE job='MANAGER'

GROUP BY deptno) ;

<ANY

比子查询中返回结果的最大的要小(还包含了<=ANY)

      • 使用<ANY操作符完成查询

SELECT * FROM emp

WHERE sal <ANY (

SELECT MIN(sal) FROM emp

WHERE job='MANAGER'

GROUP BY deptno) ;

ALL操作符

<>ALL

等价于NOT IN(但是=ALL并不等价于IN);

使用<>ALL操作符完成查询

SELECT * FROM emp

WHERE sal <>ALL ( SELECT MIN(sal)   FROM emp

WHERE job='MANAGER' GROUP BY deptno) ;

>ALL

比子查询中最大的值还要大(还包含了>=ALL);

使用>ALL操作符完成查询

SELECT * FROM emp WHERE sal >ALL (

SELECT MIN(sal) FROM emp

WHERE job='MANAGER' GROUP BY deptno) ;

<ALL

比子查询中最小的值还要小(还包含了<=ALL)。

使用<ALL操作符完成查询

SELECT * FROM emp WHERE sal <ALL (

SELECT MIN(sal) FROM emp WHERE job='MANAGER'

GROUP BY deptno) ;

空值数据判断

在SQL之中提供了一个exists结构用于判断子查询是否有数据返回。如果子查询中有数据返回,则exists结构返回true,反之返回false。

验证exists结构

SELECT * FROM emp

WHERE EXISTS(

SELECT * FROM emp WHERE empno=9999) ;

验证exists结构

SELECT * FROM emp

WHERE EXISTS( SELECT * FROM emp) ;

使用NOT EXISTS

SELECT * FROM emp

WHERE NOT EXISTS(

(SELECT * FROM emp WHERE empno=9999));

三、在HAVING子句中使用子查询

在HAVING子句中使用子查询,子查询返回的都是单行单列数据,同时也可以在HAVING中利用统计函数进行判断。

例子1:查询部门编号、雇员人数、平均工资,并且要求这些部门的平均工资高于公司平均薪金

SELECT deptno, COUNT(empno), AVG(sal)

FROM emp

GROUP BY deptno

HAVING AVG(sal)>(

SELECT AVG(sal) FROM emp);

例子2:查询出每个部门平均工资最高的部门名称及平均工资

SELECT d.dname, ROUND(AVG(e.sal),2)

FROM emp e ,dept d

WHERE e.deptno=d.deptno

GROUP BY d.dname

HAVING AVG(sal)=(

SELECT MAX(AVG(sal))

FROM emp

GROUP BY deptno) ;

四、在FROM子句中使用子查询

  • 如果现在子查询返回的数据是多行多列的,那么就可以将其当做一张数据表(同时存在多行多列)来使用,并且这种子查询一般都出现在FROM子句之中。
  • FROM子句出现的子查询返回结构为多行多列;
  • 利用子查询可以解决多表查询所带来的性能问题。

例子1:要求查询出每个部门的编号、名称、位置、部门人数、平均工资

使用子查询能解决多表查询所带来的性能问题

多字段分组实现方式(会产生笛卡尔积):

SELECT d.deptno, d.dname, d.loc, COUNT(e.empno),AVG(e.sal)

FROM emp e, dept d

WHERE e.deptno(+)=d.deptno

GROUP BY d.deptno,d.dname,d.loc

子查询实现方式:

SELECT d.deptno,d.dname,d.loc,temp.count,temp.avg

FROM dept d, (SELECT deptno dno, COUNT(empno) count , ROUND(AVG(sal),2) avg

FROM emp

GROUP BY deptno) temp

WHERE d.deptno=temp.dno(+) ;

例子2:查询出所有在部门“SALES”(销售部)工作的员工的编号、姓名、基本工资、奖金、职位、雇佣日期、部门的最高和最低工资。

确定所需要的数据表:

dept表:销售部

emp表:员工信息

emp表:统计最高最低工资

没有关联字段,也不需要关联字段

步骤一:查询销售部的部门编号

SELECT deptno

FROM dept

WHERE dname='SALES';

步骤二:此部门的雇员信息

SELECT empno,ename,sal,comm,job,hiredate

FROM emp

WHERE deptno=(

SELECT deptno

FROM dept

WHERE dname='SALES'

);

步骤三:最高和最低工资,使用MAX()和MIN()函数

因为统计函数有限制,要么单独使用,要么结合GROUP BY函数,统计函数嵌套时不允许出现任何字段。

目前在整个SELECT查询里面需要统计查询,但无法直接使用统计函数,可以在子查询中完成,而且这个子查询一定返回一个多行多列,在FROM子句中出现

SELECT e.empno, e.ename, e.sal, e.comm, e.job, e.hiredate, temp.max, temp.min

FROM emp e, (

SELECT deptno dno,MAX(sal) max,MIN(sal) min

FROM emp

GROUP BY deptno

) temp            è 子查询负责统计信息,使用temp表示临时表的统计结果

WHERE deptno=(

SELECT deptno

FROM dept

WHERE dname='SALES'

AND e.deptno=temp.dno

);

例子3:查询出所有薪金高于公司平均薪金的员工编号、姓名、基本工资、职位、雇佣日期,所在部门名称、位置,上级领导姓名,公司的工资等级,部门人数、平均工资、平均服务年限。

分析:

确定所需要的数据表

emp表:员工编号,姓名,工资,职位,雇佣日期

dept表:部门信息

emp表:自身关联上级领导姓名

salgrade表:工资等级

emp表:统计人数,平均工资,平均服务年限

确定已知的关联字段

雇员和部门:emp.deptno=dept.deptno

雇员和领导:emp.mgr=memp.empno

雇员和工资等级:emp.sal BETWEEN salgrade.losal AND salgrade.hisal

步骤一:AVG()函数统计公司平均薪金

SELECT avg(sal) FROM emp;

结果是单行单列,只能在WHERE和HAVING子句中出现,这里没有分组,只能在WHERE中出现

步骤二:查询高于平均薪金的员工编号,姓名,基本工资等

SELECT e.empno, e.ename, e.sal, e.job, e.hiredate

FROM emp e

WHERE e.sal >(SELECT avg(sal) FROM emp);

步骤三:和dept表、salgrade表和emp表领导信息进行关联(多表关联使用关联字段消除笛卡尔积,由于有的员工没有领导,考虑外连接)

SELECT e.empno, e.ename, e.sal, e.job, e.hiredate ,d.dname, d.loc, m.ename mname, s.grade

FROM emp e, dept d, emp m, salgrade s

WHERE e.sal >(SELECT avg(sal) FROM emp)

AND e.deptno=d.deptno

AND e.mgr=m.empno(+)

AND e.sal BETWEEN s.losal AND s.hisal;

步骤四:部门人数,平均工资、平均服务年限,需要使用函数AVG(),这里使用子查询

SELECT e.empno, e.ename, e.sal, e.job, e.hiredate ,d.dname, d.loc, m.ename mname, s.grade ,temp.count,temp.avgsal, temp.avgyear

FROM emp e, dept d, emp m, salgrade s,(

SELECT deptno dno ,COUNT(empno) count,ROUND(AVG(sal),2) avgsal,ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear

FROM emp GROUP BY deptno

) temp

WHERE e.sal >

(SELECT avg(sal) FROM emp)

AND e.deptno=d.deptno

AND e.mgr=m.empno(+)

AND e.sal BETWEEN s.losal AND s.hisal

AND e.deptno=temp.dno;

例子4:列出薪金比“ALLEN”或“CLARK”多的所有员工的编号、姓名、基本工资、部门名称、其领导姓名,部门人数。

分析:

确定所需要的数据表

emp表:员工编号,姓名,工资,职位,雇佣日期

dept表:部门信息

emp表:自身关联上级领导姓名

emp表:统计部门人数

确定已知的关联字段

雇员和部门:emp.deptno=dept.deptno

雇员和领导:emp.mgr=memp.empno

步骤一:查询"ALLEN”或“CLARK”的薪金

SELECT e.ename,e.sal FROM emp e WHERE e.ename IN('JONES','CLARK');

(在WHERE子句中这里返回的是多行单列,这里使用IN\ANY\ALL)

步骤二:查询薪金比“ALLEN”或“CLARK”多的所有员工,加上dept表查询部门以及emp表查询领导信息

SELECT e.ename,e.sal,e.empno,d.dname, m.ename mname

FROM emp e ,dept d, emp m

WHERE e.sal > ANY(

SELECT sal FROM emp WHERE ename IN('JONES','CLARK')

)

AND e.deptno=d.deptno

AND e.mgr=m.empno(+);

步骤三:查询部门人数,需要使用COUNT()函数

SELECT deptno dno,COUNT(empno) count FROM emp GROUP BY deptno

这里要注意,统计函数要么单独使用,要和分组字段使用就必须使用GROUP BY,否则无法使用的.

SELECT e.ename,e.sal,e.empno,d.dname, m.ename mname,temp.count

FROM emp e ,dept d, emp m,(SELECT deptno dno,COUNT(empno) count FROM emp GROUP BY deptno) temp

WHERE e.sal > ANY(

SELECT sal FROM emp WHERE ename IN('JONES','CLARK')

)

AND e.deptno=d.deptno

AND e.mgr=m.empno(+)

AND e.deptno=temp.dno;

例子5:列出公司各个部门的经理(假设每个部门只有一个经理,job为“MANAGER”)的姓名、薪金、部门名称、部门人数、部门平均工资。

分析:

确定所需要的数据表

emp表:员工编号,姓名,工资,经理

dept表:部门信息

emp表:统计部门人数,平均工资

确定已知的关联字段

雇员(经理)和部门:emp.deptno=dept.deptno

步骤一:列出经理的姓名,薪资,部门名称

SELECT e.ename, e.sal, d.dname

FROM emp e, dept d

WHERE e.job='MANAGER'

AND e.deptno=d.deptno;

步骤二:统计部门人数,平均工资

SELECT e.ename, e.sal, d.dname,temp.count, temp.avg

FROM emp e, dept d,(

SELECT deptno dno,COUNT(empno) count,ROUND(AVG(sal),2) avg

FROM emp GROUP BY deptno

) temp

WHERE e.job='MANAGER'

AND e.deptno=d.deptno

AND e.deptno=temp.dno;

五、在SELECT子句中使用子查询

例子1:查询出公司每个部门的编号、名称、位置、部门人数、平均工资

分析:

确定所需要的表:

dept表:部门信息

emp表:平均工资,部门人数

步骤一:查询部门信息

SELECT deptno,dname,loc

FROM dept d;

步骤二:查询部门人数

SELECT COUNT(empno)

FROM emp

WHERE deptno=dept.deptno

GROUP BY deptno

步骤三:查询平均工资

SELECT AVG(sal)

FROM emp

WHERE deptno=dept.deptno

GROUP BY deptno

步骤四:将平均工资和部门人数放在SELECT子句的字段中

SELECT deptno,dname,loc,

(

SELECT COUNT(empno)

FROM emp

WHERE deptno=d.deptno

GROUP BY deptno) count,

(SELECT AVG(sal)

FROM emp

WHERE deptno=d.deptno

GROUP BY deptno) sal

FROM dept d;

六、WITH子句

  • 临时表是一个查询结果,查询结果返回的是多上多列,那么可以将其定义在FROM子句之中,表示其为一张临时表.
  • 除了在FROM子句之中出现临时表之外,也可以利用WITH子句直接定义临时表.
  • 可以使用WITH子句创建临时查询表
  • WITH子句可以构建一张临时表供查询使用。
  • WITH子句提供了一种定义临时表的操作方法,如果在一个查询之中,要反复使用到一些数据,那么就可以将这些数据定义在WITH子句之中。

使用WITH子句将emp表中的数据定义为临时表

WITH e AS (

SELECT * FROM emp)

SELECT * FROM e ;

查询每个部门的编号、名称、位置、部门平均工资、人数

WITH e AS (

SELECT deptno dno , ROUND(AVG(sal),2) avg , COUNT(sal) count

FROM emp

GROUP BY deptno)

SELECT d.deptno,d.dname,d.loc,e.count,e.avg

FROM e , dept d

WHERE e.dno(+)=d.deptno ;

查询每个部门工资最高的雇员编号、姓名、职位、雇佣日期、工资、部门编号、部门名称,显示的结果按照部门编号进行排序

WITH e AS (

SELECT deptno dno , MAX(sal) max

FROM emp GROUP BY deptno)

SELECT em.empno , em.ename , em.job , em.hiredate , em.sal , d.deptno,d.dname

FROM e , emp em , dept d

WHERE e.dno=em.deptno AND em.sal=e.max AND e.dno=d.deptno

ORDER BY em.deptno ;

FROM子句中的例子:例子5:列出公司各个部门的经理(假设每个部门只有一个经理,job为“MANAGER”)的姓名、薪金、部门名称、部门人数、部门平均工资。

WITH t AS(

SELECT deptno dno,COUNT(empno) count,ROUND(AVG(sal),2) avg

FROM emp GROUP BY deptno)

SELECT e.ename, e.sal, d.dname,t.count, t.avg

FROM emp e, dept d,t

WHERE e.job='MANAGER'

AND e.deptno=d.deptno

AND e.deptno=t.dno;

上一篇:MySQL 替换手机号码中间4位为星号显示


下一篇:使用Application Insights监控应用程序性能