-
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
俩表
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
就通过子查询联合搞进去就OK了
子查询更符合人类的思维
舒服
子查询更符合人类的思维
舒服
子查询更符合人类的思维
舒服单行子查询
雇员id是141的jobid是多少？SQL> select job_id from employees e where e.employee_id = 141; JOB_ID ---------- ST_CLERK- 1
- 2
- 3
- 4
- 5
那那些与这个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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
就是多一个比较条件就行了
子查询
sql语句是真的骚，挺多，听冗杂的
可以包含多个子查询，且关系
子查询也可以使用组函数min,max,avg,sum,count
好说SQL> select min(salary) min from employees e; MIN ---------- 2100- 1
- 2
- 3
- 4
- 5
最低薪水
然后查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- 1
- 2
- 3
- 4
- 5
- 6
这样的话，子查询中就有一个min的组函数了
having是过滤条件，这里面也可以用子查询，反正就是搞一个结果而已
就是java中的子函数
没啥稀奇
差部门id为50的人最低薪水是？SQL> select min(e.salary) from employees e where e.department_id = 50; MIN(E.SALARY) ------------- 2100- 1
- 2
- 3
- 4
- 5
然后看那些部门的最低薪水，比这个还小
那就是按照部门分组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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
这就是按部门划分，然后找部门里面最低的薪水，这个最低，还要大于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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
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: 单行子查询返回多个行- 1
- 2
- 3
- 4
你要是用=
报错了把=改为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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
看到了吗，这些都是薪水最差劲的员工
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
然后，其他的员工中，薪水小于这里面的都展示一下，其实就是小于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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
看见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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
因此今后你查那些人，都得小于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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
区别就是把上面一个案例的any改为all了
你瞅瞅就知道舒服吧
子查询的练习案例
先看z的部门是啥
子查询SQL> select e.department_id from employees e where e.last_name = 'Zlotkey'; DEPARTMENT_ID ------------- 80- 1
- 2
- 3
- 4
- 5
然后找，其他人在这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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
好说
平均薪水是子查询
好说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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
好说
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
排序好
很多部门应该有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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
这些部门都有这种人
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
很多
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
很多部门，显然是多表子查询，用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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
好说，in语句搞定
king的雇员id，看看谁的manager_id是kingSQL> select e.employee_id from employees e where e.last_name = 'King'; EMPLOYEE_ID ----------- 156 100- 1
- 2
- 3
- 4
- 5
- 6
俩人
谁向他们汇报嗯【他们就是经理】
inSQL> 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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
看看部门ex的部门id是啥呢
这在departments表里面
然后才能调用employees表SQL> select d.department_id from departments d where d.department_name = 'Executive'; DEPARTMENT_ID ------------- 90- 1
- 2
- 3
- 4
- 5
貌似就一个结果
可能会有多个结果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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
主查询联合俩条件
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
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
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
其实用子查询，更符合逻辑思维
尽量不要用自连接
自连接太烦了真的我们先分析要子查询的条件
好好学习
总结
提示：重要经验：1）
2）学好oracle，即使经济寒冬，整个测开offer绝对不是问题！同时也是你考公网络警察的必经之路。
3）笔试求AC，可以不考虑空间复杂度，但是面试既要考虑时间复杂度最优，也要考虑空间复杂度最优。 -
相关阅读:
Web前端大作业—— 饮食餐饮网站 咖啡网站pc端带轮播(5个页面)HTML+CSS+JavaScript 学生美食网页设计作品 学生餐饮文化网页模板
【SA8295P 源码分析 (四)】21 - Android GVM 虚拟网络 vlan 配置
Java课设设计-酒店管理系统
Spring Boot 打印日志文件
2022-06-15 工作记录--Vue-父页面向子组件传递数据
Pwn2Own Austin 2021 Cisco RV34x RCE 漏洞链复现
微信小程序多个按钮点击互不影响禁用实现
国内外IP线路测试网址收藏
《uni-app》表单组件-Label组件
【leetcode】不含重复字符的最长子字符串
- 原文地址：https://blog.csdn.net/weixin_46838716/article/details/127578361
