文档章节

Oracle-DQL 6- 子查询

o
 osc_wws45aot
发布于 2019/08/23 15:06
字数 1519
阅读 0
收藏 0

精选30+云产品,助力企业轻松上云!>>>

子查询:

--查询emp表中工资高于allen的员工信息
SELECT sal FROM emp
WHERE ename = 'ALLEN';

SELECT * FROM emp
WHERE sal > 1600;

--当查询的条件不是客观条件,而是表中的数据,并且不关心具体的数据是多少时,使用子查询
--子查询就是查询语句的嵌套,将子查询的结果作为主查询的条件
--子查询必须用括号括起来表示运算顺序,子查询最好单独作为一行编写
--习惯上将子查询写在比较运算符的右边
SELECT * FROM emp
WHERE sal >
(SELECT sal FROM emp WHERE ename = 'ALLEN');
--主查询中的条件和子查询的结果数据类型必须一致
--主查询中的条件和子查询的结果应该是同一意义的数据

1.单行子查询
--子查询的结果是一个数据时
--查询跟allen的部门和职位都相同的员工信息
SELECT * FROM emp
WHERE deptno =
(SELECT deptno FROM emp WHERE ename = 'ALLEN')
AND job =
(SELECT job FROM emp WHERE ename = 'ALLEN');

--子查询的数据跟主查询可以不在同一张表
--查询allen所在的部门名称
1)多表查询
SELECT d.dname
FROM emp e,dept d
WHERE e.deptno = d.deptno
AND e.ename = 'ALLEN';
--多表查询在笛卡尔结果中查询满足条件的数据,在14*4=56条数据中进行比对

2)子查询
SELECT dname FROM dept
WHERE deptno =
(SELECT deptno FROM emp WHERE ename = 'ALLEN');
--先通过allen的名字找到deptno,在emp表中找了14次
--通过deptno找dname,在dept表中找了4次
--最终查找了14+4=18次

--如果查询结果中的数据来自多张表,只能用多表查询

--如果查询结果中的数据来自一张表,只是用到了另一张表的数据作为条件
--可以用多表查询,也可以用子查询,推荐使用子查询
--子查询通常比多表查询效率高

--子查询中可以包含分组函数
--查询emp表中工资最高的员工名字
SELECT ename FROM emp
WHERE sal =
(SELECT MAX(sal) FROM emp);

--having 中也可以包含子查询
--查询人数比10号部门多的部门编号
SELECT deptno FROM emp
GROUP BY deptno
HAVING COUNT(*) >
(SELECT COUNT(*) FROM emp
WHERE deptno = 10);

2.多行子查询
--子查询的结果是多行数据时,使用多行子查询
--多行子查询必须使用多行比较运算符

--in 匹配多个值
--查询人数比10号部门多的部门名称
SELECT dname FROM dept
WHERE deptno IN
(SELECT deptno FROM emp
GROUP BY deptno
HAVING COUNT(*) >
(SELECT COUNT(*) FROM emp
WHERE deptno = 10));

--any, 满足任意一个条件就能返回结果
--查找工资比任意一个部门平均工资高的员工
SELECT * FROM emp
WHERE sal > ANY
(SELECT AVG(sal) FROM emp GROUP BY deptno);
--大于 any 表示大于最小值,小于 any 表示小于最大值

--all,满足所有条件才能返回结果
SELECT * FROM emp
WHERE sal > ALL
(SELECT AVG(sal) FROM emp GROUP BY deptno);
--大于 all 表示大于最大值,小于 all 表示小于最小值

3.多列子查询
--子查询返回的结果是多列数据

--in
--查询跟allen的部门和职位都相同的员工信息
SELECT * FROM emp
WHERE (deptno,job) IN
(SELECT deptno,job FROM emp WHERE ename = 'ALLEN');

4.分页子查询
--可以将子查询的结果看作一张表,再进行查询
SELECT ename,sal FROM
(SELECT ename,job,sal,deptno FROM emp
WHERE sal > 1500)
WHERE deptno = 30;

--rownum,是系统自动分配给查询结果的行号
--rownum不是真正的数据,只能从1开始,如果匹配的范围不是从1开始则返回空结果
--查询emp表中工资排名前三的员工信息
--先根据工资进行降序排列,找到这个结果中前三行数据
SELECT * FROM
(SELECT * FROM emp
ORDER BY sal DESC)
WHERE ROWNUM <= 3;

--查询emp表中工资排名3到5位的员工信息
--先根据工资进行降序排列,将rownum转换成真正的一列,在用这一列进行条件限定
SELECT * FROM emp
WHERE sal IN
(SELECT sal FROM
(SELECT ROWNUM rn,sal FROM
(SELECT sal FROM emp
ORDER BY sal DESC))
WHERE rn BETWEEN 3 AND 5);

【作业】
1.使用子查询,找出哪个部门下没有员工
SELECT * FROM dept
WHERE deptno NOT IN
(SELECT deptno FROM emp);

--distinct 去重
SELECT * FROM dept
WHERE deptno NOT IN
(SELECT DISTINCT deptno FROM emp);

2.使用子查询,列出薪金比“ALLEN”多的所有员工
SELECT * FROM emp
WHERE sal >
(SELECT sal FROM emp WHERE ename = 'ALLEN');

3.使用子查询,列出在销售部(SALES)工作的员工信息
SELECT * FROM emp
WHERE deptno =
(SELECT deptno FROM dept WHERE dname = 'SALES');

4.使用子查询,找出那些工资低于平均工资的员工
SELECT * FROM emp
WHERE sal <
(SELECT AVG(sal) FROM emp);

5.使用子查询,找出那些工资低于任意一个部门的平均工资的员工
SELECT * FROM emp
WHERE sal < ANY
(SELECT AVG(sal) FROM emp GROUP BY deptno);

6.使用sql语句查出各个部门工资最高的员工的部门编号、员工姓名及其工资的信息
SELECT deptno,ename,sal FROM emp
WHERE (deptno,sal) IN
(SELECT deptno,MAX(sal) FROM emp GROUP BY deptno);

7.列出所有部门的相应信息和部门人数,没有员工的部门则部门人数显示为0
1)多表查询
SELECT d.*,COUNT(e.empno)
FROM dept d,emp e
WHERE d.deptno = e.deptno(+)
GROUP BY d.deptno,d.dname,d.loc
ORDER BY d.deptno;

2)子查询
--先使用子查询构造出一张满足查询条件的表,然后和其他表进行多表查询
SELECT deptno,COUNT(empno) FROM emp
GROUP BY deptno;

SELECT d.*,NVL(c.ct,0)
FROM dept d,
(SELECT deptno,COUNT(empno) ct FROM emp
GROUP BY deptno) c
WHERE d.deptno = c.deptno(+);

SELECT ename FROM emp
WHERE deptno = 40;

SELECT AVG(sal) FROM emp
WHERE deptno = 40;

SELECT COUNT(ename) FROM emp
WHERE deptno = 40;

8.查询高于自己部门平均工资的员工名字,部门号,工资,平均工资(保留2位小数)
SELECT deptno,AVG(sal) asal FROM emp
GROUP BY deptno;

SELECT e.ename,e.deptno,e.sal,ROUND(a.asal,2)
FROM emp e,
(SELECT deptno,AVG(sal) asal FROM emp
GROUP BY deptno) a
WHERE e.deptno = a.deptno
AND e.sal > a.asal
ORDER BY e.deptno;

9.查询入职日期排名后3位的员工信息
SELECT * FROM
(SELECT * FROM emp
ORDER BY hiredate DESC)
WHERE ROWNUM <= 3;

o
粉丝 0
博文 500
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。
学习Oracle的历程 (四)

前面,我们学习了在Oracle中的DML操作,可以让我们完成添加数据、修改数据、删除数据,当我说到这里难免有大部分人觉得有点别扭,感觉缺少了点什么? 没错,作为编程的根本四要素之一,我们怎么只能...

苗小喵
05/26
8
0
oracle之DQL

一、单表查询 语法:select * from table where 条件 group by 分组 having 过滤分组 order by 排序 --查询平均工资低于2000的部门的最大工资和平均工资select deptno, max(sal), avg(sal)fr...

osc_xl85dc6p
2018/07/05
2
0
MySQL高性能SQL探索与笔录

初衷 最近我的大学同学常会拿些SQL语句与我探讨如何优化,如何写出高性能的SQL。在多次交流过后,我觉得尽管我已经工作四年之久但对于SQL语句的性能与优化方面的进步并不是符合我心里的预期,...

刺激乐天派
2019/05/25
0
0
MYSQL 到底学到了什么东西?

已经是学习第二遍了,前三天没来,这周学习了一周。sql语句是要比以前熟悉了,对sql的优化能力也要比以前强了。以前都是以实现功能为目的,现在开始开始考虑sql优化的一些问题。除了书本上的...

千尋
2013/06/23
192
0
【数据库】阿里云教你快速掌握SQL语句使用

什么是SQL? 结构化查询语言(Structured Query Language),简称SQL,是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是...

阿里云大学
2019/06/12
0
0

没有更多内容

加载失败,请刷新页面

加载更多

图解ARP协议(二)ARP***原理与实践

一、ARP***概述 在上篇文章里,我给大家普及了ARP协议的基本原理,包括ARP请求应答、数据包结构以及协议分层标准,今天我们继续讨论大家最感兴趣的话题:ARP***原理是什么?通过ARP***可以做...

osc_91g5cdgs
27分钟前
6
0
shell进度条实现

#!/bin/bashb=''i=0while [ $i -le  100 ]do    printf "progress:[%-50s]%d%%\r" $b $i    sleep 0.1    i=`expr 2 + $i`            b=#$b......

osc_npw5uz1o
28分钟前
13
0
通过ssh实现登录服务器脚本

版本v1 #!/bin/bash########################author: Bovin########################show all host infos of serverList.txtif [[ -f $HOME/.serverList.txt ]]then  hos......

osc_lt2jwwhb
30分钟前
20
0
VMware Fusion下Centos联网

1.VMware Fusion设置选择“网络适配器” 2.“连接我的网络适配器”选择“与我的mac共享” 3.编辑centos的ip配置文件 [root@Centos ~]# more /etc/sysconfig/network-scripts/ifcfg-eth0D...

osc_pg5rp78i
31分钟前
14
0
Kickstart配置文件参数详解

kickstart是什么? KickStart是一种无人值守的安装方法。它的工作原理时在安装过程中记录典型的需要人工干预填写的各种参数,并生成一个名为ks.cfg的文件。如果在安装过程中(不只局限于生成K...

osc_r9yyhhqz
32分钟前
8
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部