子查询
一、子查询和视图
子查询就是一次性视图,与视图不同的是,子查询在select语句执行完毕后会消失。
子查询在主查询执行前执行一次。
子查询的结果被用于主查询。
1、子查询的分类单行单列子查询:一定返回一行。单行操作符:>,<,=,>=,<=,<>
多行单列子查询:返回一列多行
单行多列子查询:返回零行或多行。
多行操作符:
in 等于列表中的任何值。(不能用 NOT IN)
ANY 与子查询返回的每个值进行比较。(小于是小于最大的,大于是大于最小的)
ALL(小于是小于最小的,大于是大于最大的)
2、单行单列子查询
子查询返回一个值,单行操作符:>,<,=,>=,<=,<>
例1:查询工资大于Jones的员工姓名及工资
例2:查询和Smith同一部门的员工姓名和工资。
3、多行单列子查询
子查询返回多行单列,in,not int,any,all
1)in
逐个比较是否有匹配值。
例:查看sales或accounting部门的员工工资
2)not in
与in不同的是,not in里边如果有空值,则返回空值。
例:查询没有下属的员工
Not只有not in组合,没有not any和not all组合。
3)any
Any强调的是只要符合其中的一个条件就可以了。
例:查询工资比30号部门任一员工工资高的员工
相当于
4)all
例:查询比部门30所有员工的工资都要高度员工信息
4、多行多列子查询
子查询中返回多列结果集。
例1:查询哪些员工的工资是所在职务中工资最高的。
Select ename,job,sal from emp where(sal,job) in (select max(sal),job from emp group by job);
多列子查询就是子查询中不止一个列名。
5、子查询注意的问题
1)要有括号
2)合理的书写分格
3)可以在主查询where、select、having、from后面使用子查询
Select后面使用子查询,只能使用单行子查询,即只允许返回一条记录。
例1:
例2:按部门分组查询最低工资,且最低工资大于20号部门的最低工资才显示
4)不可以在group by 后面使用子查询
5)强调from后面的子查询。
6)主查询和子查询可以不是同一张表,只要子查询返回的结果主查询可以使用即可。
例:查询部门名称是sales的员工.
还可以使用多表连接查询。
7)一般不再子查询中排序,但在top-n分析中,必须对子查询排序。
例:Top-n分析: 找出员工工资表的前三名
8)一般先执行子查询,在执行主查询。
9)单行只查询只能使用单行操作符,多行子查询只能使用多行操作符。
例:查询工资最低的员工的职位和薪水
10)单行子查询空值问题,不能贸然使用=或不等于,因为后面返回null就永远不成立。
子查询可能返回null ,而判断是否为空,不能使用 = 或者 !=
多行子查询中的null: 集合有空值,不能用not in 可以用in
例:查询不是领导的员工
错误的写法:
正确的写法:
6、内联视图
From子句中使用子查询。
例:员工的工资大于他所在部门的平均工资,显式其信息。
第一步先看部门的平均工资。
第二步,把这个结果集作为内联视图,起个别名,和emp表连接
二、标量子查询
1、子查询的名称
在oracle的from子句中,不能使用as(会发生错误)。
例:找出员工工资表的前三名
2、标量子查询
标量子查询也就是单行单列子查询,必须而且只能返回1行1列的结果。
3、标量子查询的书写位置
能够使用常数或者列名的地方,无论是select子句,group by子句,having子句,还是order by子句,几乎所有的地方都可以使用。
注意事项:该子查询决不能返回多行结果。
三、关联子查询
所谓非关联子查询,就是子查询可以脱离主查询独立存在。前边的内容基本都是非关联子查询。
所谓关联子查询,就是子查询与主查询之间有条件关联,不能独立执行。
其子查询内部会引用主查询中的一列或多列。在执行时,外部查询的每一行都被一次一行的传递给子查询,子查询以此读取主查询传递来的每一个值,并将其用到子查询上,知道主查询所有的行都处理完毕为止,最后返回查询结果。
理论上主查询有几行,子查询就被调用几次。
关联子查询实际只能返回1行结果,这也是关联子查询查询不出错的原因。
例1:关联查询显式员工的工资大于他所在部门的平均工资。
例2:找出每个部门中工资最高的人。
用非关联的写法:
Select deptno,ename from emp where (deptno,sal)in (select deptno,max(sal) from emp group by deptno);
用关联的写法:
Select outer.deptno,outer.ename,outer.salfrom emp outer where sal=(select max(sal) from emp where deptno=outer.deptno);
1、exists
Exists只要在子查询中找到一个行值就行,如果子查询中有行值,就立即停止子查询的搜索。然后返回true,如果没有没有相应的行值,则返回true。
例:Exists显式emp表中哪些员工不是普通员工。
子查询中的x只是占位,关联查询只是关心子查询中是否存在。
2、not exists
显式dept表中还没有员工的部门。
引入exists的目的:在一些情况下,只需要子查询返回一个真值或是假值。如果只考虑是否满足判断条件,而数据本身并不重要,可以使用exists操作符来定义子查询。
四、练习题
1、写出如下结果的select语句,其中sale_price_all列为全部商品的平均销售单价。
原表如下:
需要得到的结果如下:
解:select product_id,product_name,product_type,sale_price, (selectavg(sale_price) from product) sale_price_all from product;
2、请根据下面条件编写一条SQL语句,创建一幅包含如下数据的视图(AvgPriceByType)。
条件1:销售单价大于等于1000日元。
条件2:登记日期是2009年9月20日。
条件3:包含商品名称、销售单价和登记日期三列。
执行结果:
解:第一步:求出各个商品类别的平均销售单价,即avg_sale_price.
第二步:按关联查询查找出执行的结果。
第三步:创建视图。
3、列出至少有4个员工的所有部门和部门信息。
解:第一步:先找出每个部门有多少员工。
第二步:找出员工数大于等于4个的部门。
第三步:找出信息。
还可以用多表连接方法:
4、列出各个部门的MANAGER的最低薪金
5、给任职日期超过37年的人加薪30%。
6、列出每个部门工作的员工数量、平均工资和平均服务期限(单位为年)。