Oracle数据库:子查询、单行子查询,多行子查询,in,any,all语句,子查询的练习案例
Oracle数据库:子查询、单行子查询,多行子查询,in,any,all语句,子查询的练习案例
2022找工作是学历、能力和运气的超强结合体,遇到寒冬,大厂不招人,可能很多算法学生都得去找开发,测开
测开的话,你就得学数据库,sql,oracle,尤其sql要学,当然,像很多金融企业、安全机构啥的,他们必须要用oracle数据库
这oracle比sql安全,强大多了,所以你需要学习,最重要的,你要是考网络警察公务员,这玩意你不会就别去报名了,耽误时间!
oracle系列文章:
【1】Oracle数据库:啥是oracle数据库?你为啥要学oracle?
【2】Oracle数据库:oracle 11g安装教程,已安装好的oracle各个文件夹的作用,oracle用户权限怎么样
【3】Oracle数据库:oracle启动,oracle客户端工具plsql安装教程和使用方法
【4】Oracle数据库:创建表空间,创建新用户,给用户分配对象、角色和系统权限,登录新用户建表
【5】Oracle数据库:链接配置,包括sqlnet.ora里面的transnames.ora配置数据库标识符SID,listener暂时简单了解
【6】Oracle数据库:net configureation assistant工具配置监听listener,配置本地网络访问服务器上的数据库
【7】Oracle数据库:oracle字符类型、数字类型、创建表表名的命名规则
【8】Oracle数据库:约束条件:主键约束、唯一约束、检查约束、非空约束、外键约束、默认值填写
【9】Oracle数据库:表的关系:一对多,一对一,多对多,一一继承和修改的关系,表不是重点,重点是数据和约束关系
【10】Oracle数据库:sql语言结构,数据查询语言DQL,select * from table;算术,别名,连接,去重等操作
【11】Oracle数据库:约束行限制where语句,判断条件,比较条件,字符串日期格式,in,like,escape,null语句
【12】Oracle数据库:逻辑运算and,or,not和各种运算的优先级控制
【13】Oracle数据库:排序order by语句,select from where order by的执行先后顺序,各种样例
【14】Oracle数据库:oracle函数,单行函数,多行函数,upper,lower,initcap,字符串函数
【15】Oracle数据库:数字函数,日期函数,round,trunc,mod,months_between,add_months,next_day,last_day,sysdate
【16】Oracle数据库:oracle数据类型转换to_char()日期和数字转字符,to_number()字符转数字,to_date()字符转日期函数
【17】Oracle数据库:oracle函数嵌套,nvl函数,nvl2函数,nullif函数,coalesce合并函数
【18】Oracle数据库:条件表达式case when then else end,decode函数,oracle单行函数练习示例
【19】Oracle数据库:oracle多表查询,等值连接,非等值连接,自连接的sql语句和规则
【20】Oracle数据库:oracle外连接left/right/full outer join on,oracle扩展的左右外连接展示符号(+)
【21】Oracle数据库:自然连接natural join,using语句,注意避免写交叉连接
【22】Oracle数据库:oracle内连接inner join on,多表查询各种自链接、内连接、外连接的练习示例
【23】Oracle数据库:oracle组函数,聚合函数,多行函数,avg,sum,min,max,count,group by,having
【24】Oracle数据库:oracle嵌套分组函数(聚合函数),组函数的练习题,挺复杂的,用好decode函数,很有趣
oracle子查询,用得多
嵌套select???
子查询的优先级高
子查询只执行一次
看来有两类了
子查询返回结果是一个的话,对于主查询就是单行
多行子查询,结果返回多行,对于主查询就是多行
abel的薪水,返回的结果就是一个
不写子查询,看看啥情况
内连接
其他的人的表,自连接——不建议这么写,烦人
SQL> select em.last_name,em.salary from employees a,employees em where a.last_name = 'Abel' and em.salary > a.salary;
LAST_NAME SALARY
------------------------- ----------
King 24000.00
Kochhar 17000.00
De Haan 17000.00
Greenberg 12008.00
Russell 14000.00
Partners 13500.00
Errazuriz 12000.00
Ozer 11500.00
Hartstein 13000.00
Higgins 12008.00
10 rows selected
俩表
a表控制名字相同的那个链接条件
然后控制em表薪资大于a表薪资,a表薪资只看abel的
这挺烦的,这些规则确实很难记,但是就这样的没法搞
搞成子查询
先查abel的薪水
SQL> select em.last_name, em.salary from employees em where em.salary > (select m.salary from employees m where m.last_name = 'Abel');
LAST_NAME SALARY
------------------------- ----------
King 24000.00
Kochhar 17000.00
De Haan 17000.00
Greenberg 12008.00
Russell 14000.00
Partners 13500.00
Errazuriz 12000.00
Ozer 11500.00
Hartstein 13000.00
Higgins 12008.00
10 rows selected
就通过子查询联合搞进去就OK了
子查询更符合人类的思维
舒服
子查询更符合人类的思维
舒服
子查询更符合人类的思维
舒服
单行子查询
雇员id是141的jobid是多少?
SQL> select job_id from employees e where e.employee_id = 141;
JOB_ID
----------
ST_CLERK
那那些与这个id相同的人名和id是
SQL> select em.last_name,em.job_id from employees em where em.job_id = (select e.job_id from employees e where e.employee_id = 141);
LAST_NAME JOB_ID
------------------------- ----------
Nayer ST_CLERK
Mikkilineni ST_CLERK
Landry ST_CLERK
Markle ST_CLERK
Bissot ST_CLERK
Atkinson ST_CLERK
Marlow ST_CLERK
Olson ST_CLERK
Mallin ST_CLERK
Rogers ST_CLERK
Gee ST_CLERK
Philtanker ST_CLERK
Ladwig ST_CLERK
Stiles ST_CLERK
Seo ST_CLERK
Patel ST_CLERK
Rajs ST_CLERK
Davies ST_CLERK
Matos ST_CLERK
Vargas ST_CLERK
20 rows selected
job_id相同的那些人
查出来了
好说
判断条件要搞对
多了薪水,好说
SQL> select em.last_name,em.job_id from employees em where em.job_id = (select e.job_id from employees e where e.employee_id = 141) and em.salary > (select e.salary from employees e where e.employee_id = 143);
LAST_NAME JOB_ID
------------------------- ----------
Nayer ST_CLERK
Mikkilineni ST_CLERK
Bissot ST_CLERK
Atkinson ST_CLERK
Mallin ST_CLERK
Rogers ST_CLERK
Ladwig ST_CLERK
Stiles ST_CLERK
Seo ST_CLERK
Rajs ST_CLERK
Davies ST_CLERK
11 rows selected
SQL> select em.last_name,em.job_id,em.salary from employees em where em.job_id = (select e.job_id from employees e where e.employee_id = 141) and em.salary > (select e.salary from employees e where e.employee_id = 143);
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------
Nayer ST_CLERK 3200.00
Mikkilineni ST_CLERK 2700.00
Bissot ST_CLERK 3300.00
Atkinson ST_CLERK 2800.00
Mallin ST_CLERK 3300.00
Rogers ST_CLERK 2900.00
Ladwig ST_CLERK 3600.00
Stiles ST_CLERK 3200.00
Seo ST_CLERK 2700.00
Rajs ST_CLERK 3500.00
Davies ST_CLERK 3100.00
11 rows selected
就是多一个比较条件就行了
子查询
sql语句是真的骚,挺多,听冗杂的
可以包含多个子查询,且关系
子查询也可以使用组函数min,max,avg,sum,count
好说
SQL> select min(salary) min from employees e;
MIN
----------
2100
最低薪水
然后查
SQL> select em.last_name, em.job_id, em.salary from employees em where em.salary = (select min(salary) min from employees e);
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------
Olson ST_CLERK 2100.00
这样的话,子查询中就有一个min的组函数了
having是过滤条件,这里面也可以用子查询,反正就是搞一个结果而已
就是java中的子函数
没啥稀奇
差部门id为50的人最低薪水是?
SQL> select min(e.salary) from employees e where e.department_id = 50;
MIN(E.SALARY)
-------------
2100
然后看那些部门的最低薪水,比这个还小
那就是按照部门分组
SQL> select em.department_id, min(em.salary) from employees em group by em.department_id having min(em.salary) > (select min(e.salary) from employees e where e.department_id = 50);
DEPARTMENT_ID MIN(EM.SALARY)
------------- --------------
100 6900
30 2500
7000
90 17000
20 6000
70 10000
110 8300
80 6100
40 6500
60 4200
10 4400
11 rows selected
这就是按部门划分,然后找部门里面最低的薪水,这个最低,还要大于50号部门最低薪水的值
骚
子查询的错误:多行返回,处理还是单行语句
当多行返回结果来了,你用单行比较运算,是不行的
多行子查询
多个行,那就是很多的比较了
比如
涉及到跟多个结果的对比
in是比较等于
而any和all是大于和小于
多行子查询的in
部门分组,找部门最低,然后显示他们的名字
首先找每个部门的最低薪水,那很多部门分组,的话,回来的结果很多个结果
SQL> select min(em.salary) from employees em group by em.department_id;
MIN(EM.SALARY)
--------------
6900
2500
7000
17000
6000
10000
8300
2100
6100
6500
4200
4400
12 rows selected
12个部门
每个人的薪水=这些最低值
展示
SQL> select e.last_name,e.job_id,e.salary from employees e where e.salary = (select min(em.salary) from employees em group by em.department_id);
select e.last_name,e.job_id,e.salary from employees e where e.salary = (select min(em.salary) from employees em group by em.department_id)
ORA-01427: 单行子查询返回多个行
你要是用=
报错了
把=改为in
才是多行子查询返回结果的正确姿势
SQL> select e.last_name,e.job_id,e.salary from employees e where e.salary in (select min(em.salary) from employees em group by em.department_id);
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------
Kochhar AD_VP 17000.00
De Haan AD_VP 17000.00
Ernst IT_PROG 6000.00
Lorentz IT_PROG 4200.00
Popp FI_ACCOUNT 6900.00
Colmenares PU_CLERK 2500.00
Vollman ST_MAN 6500.00
Marlow ST_CLERK 2500.00
Olson ST_CLERK 2100.00
Patel ST_CLERK 2500.00
Vargas ST_CLERK 2500.00
Tucker SA_REP 10000.00
Tuvault SA_REP 7000.00
King SA_REP 10000.00
Sewall SA_REP 7000.00
Bloom SA_REP 10000.00
Kumar SA_REP 6100.00
Grant SA_REP 7000.00
Sullivan SH_CLERK 2500.00
Sarchand SH_CLERK 4200.00
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------
Perkins SH_CLERK 2500.00
Whalen AD_ASST 4400.00
Fay MK_REP 6000.00
Mavris HR_REP 6500.00
Baer PR_REP 10000.00
Gietz AC_ACCOUNT 8300.00
26 rows selected
看到了吗,这些都是薪水最差劲的员工
gg
这就是多行子查询
多行子查询的any
满足其一条件即可
美滋滋
小于<(100,200,300)
或者
大于>(100,200,300)
只要满足一个就好了
查询不是it的所有员工
他们工资小于it的薪水
子查询是it的薪水那些员工们
不止一个人在这个岗位上,懂吧??多行返回的
SQL> select em.salary from employees em where em.job_id='IT_PROG';
SALARY
----------
9000.00
6000.00
4800.00
4800.00
4200.00
然后,其他的员工中,薪水小于这里面的都展示一下,其实就是小于9000的
SQL> select e.last_name,e.job_id,e.salary from employees e where e.job_id !='IT_PROG' and e.salary < any(select em.salary from employees em where em.job_id='IT_PROG');
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------
Olson ST_CLERK 2100.00
Philtanker ST_CLERK 2200.00
Markle ST_CLERK 2200.00
Gee ST_CLERK 2400.00
Landry ST_CLERK 2400.00
Perkins SH_CLERK 2500.00
Sullivan SH_CLERK 2500.00
Vargas ST_CLERK 2500.00
Patel ST_CLERK 2500.00
Marlow ST_CLERK 2500.00
Colmenares PU_CLERK 2500.00
Himuro PU_CLERK 2600.00
OConnell SH_CLERK 2600.00
Grant SH_CLERK 2600.00
Matos ST_CLERK 2600.00
Mikkilineni ST_CLERK 2700.00
Seo ST_CLERK 2700.00
Tobias PU_CLERK 2800.00
Geoni SH_CLERK 2800.00
Atkinson ST_CLERK 2800.00
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------
Jones SH_CLERK 2800.00
Gates SH_CLERK 2900.00
Baida PU_CLERK 2900.00
Rogers ST_CLERK 2900.00
Feeney SH_CLERK 3000.00
Cabrio SH_CLERK 3000.00
Khoo PU_CLERK 3100.00
Walsh SH_CLERK 3100.00
Davies ST_CLERK 3100.00
Fleaur SH_CLERK 3100.00
McCain SH_CLERK 3200.00
Stiles ST_CLERK 3200.00
Nayer ST_CLERK 3200.00
Taylor SH_CLERK 3200.00
Bissot ST_CLERK 3300.00
Mallin ST_CLERK 3300.00
Dellinger SH_CLERK 3400.00
Rajs ST_CLERK 3500.00
Ladwig ST_CLERK 3600.00
Dilly SH_CLERK 3600.00
Chung SH_CLERK 3800.00
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------
Everett SH_CLERK 3900.00
Bell SH_CLERK 4000.00
Bull SH_CLERK 4100.00
Sarchand SH_CLERK 4200.00
Whalen AD_ASST 4400.00
Mourgos ST_MAN 5800.00
Fay MK_REP 6000.00
Kumar SA_REP 6100.00
Johnson SA_REP 6200.00
Banda SA_REP 6200.00
Ande SA_REP 6400.00
Vollman ST_MAN 6500.00
Mavris HR_REP 6500.00
Lee SA_REP 6800.00
Popp FI_ACCOUNT 6900.00
Sewall SA_REP 7000.00
Grant SA_REP 7000.00
Tuvault SA_REP 7000.00
Marvins SA_REP 7200.00
Bates SA_REP 7300.00
Smith SA_REP 7400.00
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------
Cambrault SA_REP 7500.00
Doran SA_REP 7500.00
Sciarra FI_ACCOUNT 7700.00
Urman FI_ACCOUNT 7800.00
Kaufling ST_MAN 7900.00
Olsen SA_REP 8000.00
Smith SA_REP 8000.00
Weiss ST_MAN 8000.00
Fripp ST_MAN 8200.00
Chen FI_ACCOUNT 8200.00
Gietz AC_ACCOUNT 8300.00
Livingston SA_REP 8400.00
Taylor SA_REP 8600.00
Hutton SA_REP 8800.00
76 rows selected
看见any了吗
不能直接用小于<
需要<any
这样就是多行了
美滋滋
多行子查询的all
any是任意其一,or关系
而all就是全部都得满足,and关系
in是有其一=即可,也是or关系
最低薪水和薪水,不同
any是是任意其一即可
所以这里我们该any为all就是所有薪水都得比较
那自然是最低薪水了
```sql
SQL> select em.salary from employees em where em.job_id='IT_PROG';
SALARY
----------
9000.00
6000.00
4800.00
4800.00
4200.00
因此今后你查那些人,都得小于4200
```sql
SQL> select e.last_name,e.job_id,e.salary from employees e where e.job_id !='IT_PROG' and e.salary < all(select em.salary from employees em where em.job_id='IT_PROG');
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------
Bull SH_CLERK 4100.00
Bell SH_CLERK 4000.00
Everett SH_CLERK 3900.00
Chung SH_CLERK 3800.00
Ladwig ST_CLERK 3600.00
Dilly SH_CLERK 3600.00
Rajs ST_CLERK 3500.00
Dellinger SH_CLERK 3400.00
Mallin ST_CLERK 3300.00
Bissot ST_CLERK 3300.00
Taylor SH_CLERK 3200.00
Stiles ST_CLERK 3200.00
Nayer ST_CLERK 3200.00
McCain SH_CLERK 3200.00
Khoo PU_CLERK 3100.00
Walsh SH_CLERK 3100.00
Davies ST_CLERK 3100.00
Fleaur SH_CLERK 3100.00
Cabrio SH_CLERK 3000.00
Feeney SH_CLERK 3000.00
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------
Baida PU_CLERK 2900.00
Gates SH_CLERK 2900.00
Rogers ST_CLERK 2900.00
Geoni SH_CLERK 2800.00
Atkinson ST_CLERK 2800.00
Tobias PU_CLERK 2800.00
Jones SH_CLERK 2800.00
Mikkilineni ST_CLERK 2700.00
Seo ST_CLERK 2700.00
Matos ST_CLERK 2600.00
Grant SH_CLERK 2600.00
Himuro PU_CLERK 2600.00
OConnell SH_CLERK 2600.00
Patel ST_CLERK 2500.00
Marlow ST_CLERK 2500.00
Colmenares PU_CLERK 2500.00
Perkins SH_CLERK 2500.00
Sullivan SH_CLERK 2500.00
Vargas ST_CLERK 2500.00
Landry ST_CLERK 2400.00
Gee ST_CLERK 2400.00
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------
Markle ST_CLERK 2200.00
Philtanker ST_CLERK 2200.00
Olson ST_CLERK 2100.00
44 rows selected
区别就是把上面一个案例的any改为all了
你瞅瞅就知道
舒服吧
子查询的练习案例
先看z的部门是啥
子查询
SQL> select e.department_id from employees e where e.last_name = 'Zlotkey';
DEPARTMENT_ID
-------------
80
然后找,其他人在这80部门的别人
SQL> select em.last_name,em.department_id from employees em where em.department_id = (select e.department_id from employees e where e.last_name = 'Zlotkey') and em.last_name != 'Zlotkey';
LAST_NAME DEPARTMENT_ID
------------------------- -------------
Russell 80
Partners 80
Errazuriz 80
Cambrault 80
Tucker 80
Bernstein 80
Hall 80
Olsen 80
Cambrault 80
Tuvault 80
King 80
Sully 80
McEwen 80
Smith 80
Doran 80
Sewall 80
Vishney 80
Greene 80
Marvins 80
Lee 80
LAST_NAME DEPARTMENT_ID
------------------------- -------------
Ande 80
Banda 80
Ozer 80
Bloom 80
Fox 80
Smith 80
Bates 80
Kumar 80
Abel 80
Hutton 80
Taylor 80
Livingston 80
Johnson 80
33 rows selected
好说
平均薪水是子查询
好说
SQL> select em.last_name,em.salary from employees em where em.salary > (select avg(e.salary) from employees e);
LAST_NAME SALARY
------------------------- ----------
King 24000.00
Kochhar 17000.00
De Haan 17000.00
Hunold 9000.00
Greenberg 12008.00
Faviet 9000.00
Chen 8200.00
Sciarra 7700.00
Urman 7800.00
Popp 6900.00
Raphaely 11000.00
Weiss 8000.00
Fripp 8200.00
Kaufling 7900.00
Vollman 6500.00
Russell 14000.00
Partners 13500.00
Errazuriz 12000.00
Cambrault 11000.00
Zlotkey 10500.00
LAST_NAME SALARY
------------------------- ----------
Tucker 10000.00
Bernstein 9500.00
Hall 9000.00
Olsen 8000.00
Cambrault 7500.00
Tuvault 7000.00
King 10000.00
Sully 9500.00
McEwen 9000.00
Smith 8000.00
Doran 7500.00
Sewall 7000.00
Vishney 10500.00
Greene 9500.00
Marvins 7200.00
Lee 6800.00
Ozer 11500.00
Bloom 10000.00
Fox 9600.00
Smith 7400.00
Bates 7300.00
LAST_NAME SALARY
------------------------- ----------
Abel 11000.00
Hutton 8800.00
Taylor 8600.00
Livingston 8400.00
Grant 7000.00
Hartstein 13000.00
Mavris 6500.00
Baer 10000.00
Higgins 12008.00
Gietz 8300.00
51 rows selected
好说
SQL> select em.last_name,em.job_id from employees em where em.salary > (select avg(e.salary) from employees e) order by em.salary;
LAST_NAME JOB_ID
------------------------- ----------
Mavris HR_REP
Vollman ST_MAN
Lee SA_REP
Popp FI_ACCOUNT
Tuvault SA_REP
Sewall SA_REP
Grant SA_REP
Marvins SA_REP
Bates SA_REP
Smith SA_REP
Cambrault SA_REP
Doran SA_REP
Sciarra FI_ACCOUNT
Urman FI_ACCOUNT
Kaufling ST_MAN
Weiss ST_MAN
Smith SA_REP
Olsen SA_REP
Fripp ST_MAN
Chen FI_ACCOUNT
LAST_NAME JOB_ID
------------------------- ----------
Gietz AC_ACCOUNT
Livingston SA_REP
Taylor SA_REP
Hutton SA_REP
McEwen SA_REP
Hall SA_REP
Faviet FI_ACCOUNT
Hunold IT_PROG
Sully SA_REP
Bernstein SA_REP
Greene SA_REP
Fox SA_REP
King SA_REP
Tucker SA_REP
Baer PR_REP
Bloom SA_REP
Zlotkey SA_MAN
Vishney SA_REP
Abel SA_REP
Cambrault SA_MAN
Raphaely PU_MAN
LAST_NAME JOB_ID
------------------------- ----------
Ozer SA_REP
Errazuriz SA_MAN
Greenberg FI_MGR
Higgins AC_MGR
Hartstein MK_MAN
Partners SA_MAN
Russell SA_MAN
De Haan AD_VP
Kochhar AD_VP
King AD_PRES
51 rows selected
排序好
很多部门应该有u的名字
SQL> select e.department_id from employees e where e.last_name like '%u%';
DEPARTMENT_ID
-------------
60
60
30
50
50
80
80
80
80
80
80
80
80
80
50
50
50
50
18 rows selected
这些部门都有这种人
SQL> select em.employee_id,em.last_name from employees em where em.department_id in (select e.department_id from employees e where e.last_name like '%u%');
EMPLOYEE_ID LAST_NAME
----------- -------------------------
107 Lorentz
106 Pataballa
105 Austin
很多
departments表查部门id
还有employee表,根据部门id查e表里面的列
1–返回部门id
SQL> select d.department_id from departments d where d.location_id = 1700;
DEPARTMENT_ID
-------------
10
30
90
100
110
120
130
140
150
160
170
180
190
200
210
220
230
240
250
260
DEPARTMENT_ID
-------------
270
21 rows selected
很多部门,显然是多表子查询,用in
再看e表
SQL> select e.last_name,e.department_id,e.job_id from employees e where e.department_id in (select d.department_id from departments d where d.location_id = 1700);
LAST_NAME DEPARTMENT_ID JOB_ID
------------------------- ------------- ----------
King 90 AD_PRES
Kochhar 90 AD_VP
De Haan 90 AD_VP
Greenberg 100 FI_MGR
Faviet 100 FI_ACCOUNT
Chen 100 FI_ACCOUNT
Sciarra 100 FI_ACCOUNT
Urman 100 FI_ACCOUNT
Popp 100 FI_ACCOUNT
Raphaely 30 PU_MAN
Khoo 30 PU_CLERK
Baida 30 PU_CLERK
Tobias 30 PU_CLERK
Himuro 30 PU_CLERK
Colmenares 30 PU_CLERK
Whalen 10 AD_ASST
Higgins 110 AC_MGR
Gietz 110 AC_ACCOUNT
18 rows selected
好说,in语句搞定
king的雇员id,看看谁的manager_id是king
SQL> select e.employee_id from employees e where e.last_name = 'King';
EMPLOYEE_ID
-----------
156
100
俩人
谁向他们汇报嗯【他们就是经理】
in
SQL> select em.last_name from employees em where em.manager_id in (select e.employee_id from employees e where e.last_name = 'King');
LAST_NAME
-------------------------
Kochhar
De Haan
Raphaely
Weiss
Fripp
Kaufling
Vollman
Mourgos
Russell
Partners
Errazuriz
Cambrault
Zlotkey
Hartstein
14 rows selected
看看部门ex的部门id是啥呢
这在departments表里面
然后才能调用employees表
SQL> select d.department_id from departments d where d.department_name = 'Executive';
DEPARTMENT_ID
-------------
90
貌似就一个结果
可能会有多个结果
SQL> select e.department_id,e.last_name,e.job_id from employees e where e.department_id in (select d.department_id from departments d where d.department_name = 'Executive');
DEPARTMENT_ID LAST_NAME JOB_ID
------------- ------------------------- ----------
90 King AD_PRES
90 Kochhar AD_VP
90 De Haan AD_VP
SQL> select e.department_id,e.last_name,e.job_id from employees e where e.department_id = (select d.department_id from departments d where d.department_name = 'Executive');
DEPARTMENT_ID LAST_NAME JOB_ID
------------- ------------------------- ----------
90 King AD_PRES
90 Kochhar AD_VP
90 De Haan AD_VP
2种方法都行
只要结果是一行,可以当多行用
子查询是看u名字的部门
另外薪水还得高于平均薪水
俩子查询
SQL> select avg(salary) from employees;
AVG(SALARY)
-----------
6461.831775
SQL> select em.department_id from employees em where em.last_name like '%u%';
DEPARTMENT_ID
-------------
60
60
30
50
50
80
80
80
80
80
80
80
80
80
50
50
50
50
18 rows selected
主查询联合俩条件
SQL> select e.last_name, e.department_id, e.job_id from employees e where e.department_id in(select em.department_id from employees em where em.last_name like '%u%') and e.salary > (select avg(salary) from employees);
LAST_NAME DEPARTMENT_ID JOB_ID
------------------------- ------------- ----------
Hunold 60 IT_PROG
Vollman 50 ST_MAN
Kaufling 50 ST_MAN
Fripp 50 ST_MAN
Weiss 50 ST_MAN
Livingston 80 SA_REP
Taylor 80 SA_REP
Hutton 80 SA_REP
Abel 80 SA_REP
Bates 80 SA_REP
Smith 80 SA_REP
Fox 80 SA_REP
Bloom 80 SA_REP
Ozer 80 SA_REP
Lee 80 SA_REP
Marvins 80 SA_REP
Greene 80 SA_REP
Vishney 80 SA_REP
Sewall 80 SA_REP
Doran 80 SA_REP
LAST_NAME DEPARTMENT_ID JOB_ID
------------------------- ------------- ----------
Smith 80 SA_REP
McEwen 80 SA_REP
Sully 80 SA_REP
King 80 SA_REP
Tuvault 80 SA_REP
Cambrault 80 SA_REP
Olsen 80 SA_REP
Hall 80 SA_REP
Bernstein 80 SA_REP
Tucker 80 SA_REP
Zlotkey 80 SA_MAN
Cambrault 80 SA_MAN
Errazuriz 80 SA_MAN
Partners 80 SA_MAN
Russell 80 SA_MAN
Raphaely 30 PU_MAN
36 rows selected
SQL> select e.last_name, e.salary, e.employee_id from employees e where e.department_id in(select em.department_id from employees em where em.last_name like '%u%') and e.salary > (select avg(salary) from employees);
LAST_NAME SALARY EMPLOYEE_ID
------------------------- ---------- -----------
Hunold 9000.00 103
Vollman 6500.00 123
Kaufling 7900.00 122
Fripp 8200.00 121
Weiss 8000.00 120
Livingston 8400.00 177
Taylor 8600.00 176
Hutton 8800.00 175
Abel 11000.00 174
Bates 7300.00 172
Smith 7400.00 171
Fox 9600.00 170
Bloom 10000.00 169
Ozer 11500.00 168
Lee 6800.00 165
Marvins 7200.00 164
Greene 9500.00 163
Vishney 10500.00 162
Sewall 7000.00 161
Doran 7500.00 160
LAST_NAME SALARY EMPLOYEE_ID
------------------------- ---------- -----------
Smith 8000.00 159
McEwen 9000.00 158
Sully 9500.00 157
King 10000.00 156
Tuvault 7000.00 155
Cambrault 7500.00 154
Olsen 8000.00 153
Hall 9000.00 152
Bernstein 9500.00 151
Tucker 10000.00 150
Zlotkey 10500.00 149
Cambrault 11000.00 148
Errazuriz 12000.00 147
Partners 13500.00 146
Russell 14000.00 145
Raphaely 11000.00 114
36 rows selected
其实用子查询,更符合逻辑思维
尽量不要用自连接
自连接太烦了真的
我们先分析要子查询的条件
好好学习
总结
提示:重要经验:
1)
2)学好oracle,即使经济寒冬,整个测开offer绝对不是问题!同时也是你考公网络警察的必经之路。
3)笔试求AC,可以不考虑空间复杂度,但是面试既要考虑时间复杂度最优,也要考虑空间复杂度最优。
相关文章
- Oracle数据库:oracle外连接left/right/full outer join on,oracle扩展的左右外连接展示符号(+)
- Oracle数据库:oracle多表查询,等值连接,非等值连接,自连接的sql语句和规则
- oracle 列的归档,Oracle 开启或关闭归档模式
- Oracle 如何对中文字段进行排序
- 《Oracle性能优化与诊断案例精选》——第1章 三十八载,Oracle伴我同行 1.1 缘起边陲,恰同学风华正茂
- 《Oracle性能优化与诊断案例精选》——1.2 京师磨练,转眼已历十二载
- 《Oracle性能优化与诊断案例精选》——第2章 回首向来萧瑟处,也无风雨也无晴
- Oracle计算连续天数,计算连续时间,Oracle连续天数统计
- oracle-字符集
- php 5.6 版本配置 oracle ddl
- 行成于思:从Oracle到MySQL
- [Oracle工程师手记] 通过 lsof 命令查找oracle client 端和 server 端进程
- Oracle数据库案例整理-Oracle系统执行失败-sql_trace至TRUE导致Oracle在根文件夹中缺乏可用空间
- oracle_set运营商
- 怎样使用oracle 的DBMS_SQLTUNE package 来执行 Sql Tuning Advisor 进行sql 自己主动调优
- 【Oracle】使用bbed恢复delete的数据
- Mybatis+Oracle搭配insert空值报错之myBatis+mysql驱动+oracle驱动的源码分析