-
Oracle数据库：oracle内连接inner join on，多表查询各种自链接、内连接、外连接的练习示例
Oracle数据库：oracle内连接inner join on，多表查询各种自链接、内连接、外连接的练习示例
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语句，注意避免写交叉连接
oracle内连接inner join on
等值和非等值都可以
城市需要别的表格
还有部门名称需要一个表格
仨
就要俩条件SQL> select e.last_name,d.department_name,l.city from employees e,departments d, locations l where e.department_id = d.department_id and d.location_id = l.location_id and e.employee_id=202; LAST_NAME DEPARTMENT_NAME CITY ------------------------- ------------------------------ ------------------------------ Fay Marketing Toronto- 1
- 2
- 3
- 4
- 5
- 6
这老复杂了
其实这不复杂可以用inner join试试，更复杂………………
SQL> select e.last_name,d.department_name,l.city from employees e inner join departments d on e.department_id = d.department_id inner join locations l on d.location_id = l.location_id where e.employee_id=202; LAST_NAME DEPARTMENT_NAME CITY ------------------------- ------------------------------ ------------------------------ Fay Marketing Toronto- 1
- 2
- 3
- 4
- 5
- 6
中间两次连接很烦，挺狗的啊感觉，谁发明的…………
只是帮助做等值连接和非等值链接的一种方法
不建议写这种
当然为了考试，也就了解一下SQL> select e.last_name,d.department_name,l.city from employees e inner join departments d on e.department_id = d.department_id inner join locations l on d.location_id = l.location_id where e.employee_id>202; LAST_NAME DEPARTMENT_NAME CITY ------------------------- ------------------------------ ------------------------------ Gietz Accounting Seattle Higgins Accounting Seattle Mavris Human Resources London Baer Public Relations Munich- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
俩性能没区别
inner join中可以用using语句的
SQL> select e.last_name,d.department_name,l.city from employees e inner join departments d on e.department_id = d.department_id inner join locations l using(location_id) where e.employee_id>202; LAST_NAME DEPARTMENT_NAME CITY ------------------------- ------------------------------ ------------------------------ Gietz Accounting Seattle Higgins Accounting Seattle Mavris Human Resources London Baer Public Relations Munich- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
SQL> select e.last_name,department_name,l.city from employees e inner join departments using(department_id) inner join locations l using(location_id) where e.employee_id>202; LAST_NAME DEPARTMENT_NAME CITY ------------------------- ------------------------------ ------------------------------ Gietz Accounting Seattle Higgins Accounting Seattle Mavris Human Resources London Baer Public Relations Munich- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
也还行反正就这样吧
多表查询练习示例
复习
俩表
employee表和departments表
链接SQL> select e.last_name,d.department_name from employees e,departments d where e.department_id= d.department_id; LAST_NAME DEPARTMENT_NAME ------------------------- ------------------------------ Whalen Administration Fay Marketing Hartstein Marketing Tobias Purchasing- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
这是等值连接
SQL> select e.last_name,d.department_name from employees e inner join departments d on e.department_id= d.department_id; LAST_NAME DEPARTMENT_NAME ------------------------- ------------------------------ Whalen Administration Fay Marketing Hartstein Marketing Tobias Purchasing Colmenares Purchasing- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
则是内连接，好说
on子句
inner join
可以用using语句SQL> select e.last_name,department_name from employees e inner join departments d using(department_id); LAST_NAME DEPARTMENT_NAME ------------------------- ------------------------------ Whalen Administration Fay Marketing Hartstein Marketing Tobias Purchasing- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
using语句挺简单的
舒服
查部门编号，地点编号，location_id，在departments表中有的SQL> desc departments; Name Type Nullable Default Comments --------------- ------------ -------- ------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DEPARTMENT_ID NUMBER(4) Primary key column of departments table. DEPARTMENT_NAME VARCHAR2(30) A not null column that shows name of a department. Administration, Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public Relations, Sales, Finance, and Accounting. MANAGER_ID NUMBER(6) Y Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column. LOCATION_ID NUMBER(4) Y Location id where a department is located. Foreign key to location_id column of locations table.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
DEPARTMENT_ID 在本表中
下面连接条件俩表用department_id
行选择条件也可以加上SQL> select e.job_id,d.location_id from employees e, departments d where e.department_id=d.department_id and e.department_id=80; JOB_ID LOCATION_ID ---------- ----------- SA_MAN 2500 SA_MAN 2500 SA_MAN 2500 SA_MAN 2500 SA_MAN 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 JOB_ID LOCATION_ID ---------- ----------- SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 SA_REP 2500 34 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
你可以发现很多job_id是重复的
去重可以用distinct，放在select之后
对列选择SQL> select distinct e.job_id,d.location_id from employees e, departments d where e.department_id=d.department_id and e.department_id=80; JOB_ID LOCATION_ID ---------- ----------- SA_REP 2500 SA_MAN 2500- 1
- 2
- 3
- 4
- 5
- 6
- 7
employees表
departments表
locations表2个链接表的条件
条件，有佣金，is not nullSQL> select e.last_name, d.department_name, l.city from employees e,departments d,locations l where e.department_id = d.department_id and d.location_id = l.location_id and e.commission_pct is not null; LAST_NAME DEPARTMENT_NAME CITY ------------------------- ------------------------------ ------------------------------ Russell Sales Oxford Partners Sales Oxford Errazuriz Sales Oxford Cambrault Sales Oxford Zlotkey Sales Oxford Tucker Sales Oxford Bernstein Sales Oxford Hall Sales Oxford Olsen Sales Oxford Cambrault Sales Oxford Tuvault Sales Oxford King Sales Oxford Sully Sales Oxford McEwen Sales Oxford Smith Sales Oxford Doran Sales Oxford Sewall Sales Oxford Vishney Sales Oxford Greene Sales Oxford Marvins Sales Oxford LAST_NAME DEPARTMENT_NAME CITY ------------------------- ------------------------------ ------------------------------ Lee Sales Oxford Ande Sales Oxford Banda Sales Oxford Ozer Sales Oxford Bloom Sales Oxford Fox Sales Oxford Smith Sales Oxford Bates Sales Oxford Kumar Sales Oxford Abel Sales Oxford Hutton Sales Oxford Taylor Sales Oxford Livingston Sales Oxford Johnson Sales Oxford 34 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
稳
模糊匹配like
好说
俩表SQL> select e.last_name,d.department_name from employees e,departments d where e.department_id = d.department_id and e.last_name like '%a%'; LAST_NAME DEPARTMENT_NAME ------------------------- ------------------------------ Whalen Administration Fay Marketing Hartstein Marketing Baida Purchasing- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
城市locations表
名字employees表
departments表也要
仨表
内连接两次
挺烦SQL> select e.last_name,d.department_name,l.city from employees e inner join departments d on e.department_id = d.department_id inner join locations l on d.location_id = l.location_id where l.city='Toronto'; LAST_NAME DEPARTMENT_NAME CITY ------------------------- ------------------------------ ------------------------------ Hartstein Marketing Toronto Fay Marketing Toronto- 1
- 2
- 3
- 4
- 5
- 6
- 7
SQL> select e.last_name,e.job_id,d.department_name,l.city from employees e inner join departments d on e.department_id = d.department_id inner join locations l on d.location_id = l.location_id where l.city='Toronto'; LAST_NAME JOB_ID DEPARTMENT_NAME CITY ------------------------- ---------- ------------------------------ ------------------------------ Hartstein MK_MAN Marketing Toronto Fay MK_REP Marketing Toronto- 1
- 2
- 3
- 4
- 5
- 6
- 7
自连接SQL> select emp.last_name "Emp",emp.employee_id "Emp#", manager.last_name "Manager",manager.employee_id "Manager#" from employees emp, employees manager where emp.manager_id = manager.employee_id; Emp Emp# Manager Manager# ------------------------- ------- ------------------------- -------- Kumar 173 Cambrault 148 Bates 172 Cambrault 148 Smith 171 Cambrault 148 Fox 170 Cambrault 148 Bloom 169 Cambrault 148 Ozer 168 Cambrault 148- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
经理和雇员都是雇员
所以雇员的雇员编号好搞
但是经理的雇员编号需要自连接，懂？？？这个好好屡清楚，骚得很
左外，右外都行
最好把员工描述为左表
经理表用作右表SQL> select emp.last_name "Emp",emp.employee_id "Emp#", manager.last_name "Manager",manager.employee_id "Manager#" from employees emp left outer join employees manager on emp.manager_id = manager.employee_id; Emp Emp# Manager Manager# ------------------------- ------- ------------------------- -------- Hutton 175 Zlotkey 149 Abel 174 Zlotkey 149 King 100 107 rows selected- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
你看king就出来了
刺激和上一个语句的区别就是左外链接展示坐标孤儿数据
同一个部门的同事
员工的部门id相同
一个员工表，一个同事表貌似很多结果
SQL> select e.last_name,e.department_id from employees e,employees c where e.department_id=c.department_id and e.employee_id !=c.employee_id; LAST_NAME DEPARTMENT_ID ------------------------- ------------- Higgins 110 3192 rows selected- 1
- 2
- 3
- 4
- 5
- 6
- 7
很多同事
查询雇员的薪资等级，A–Femployees表
departments表
job_grades表SQL> select e.last_name,e.job_id,d.department_name,e.salary,j.gra from employees e, departments d, job_grades j where e.department_id = d.department_id and e.salary between j.losest_sal and j.highest_sal; LAST_NAME JOB_ID DEPARTMENT_NAME SALARY GRA ------------------------- ---------- ------------------------------ ---------- ---------- King AD_PRES Executive 24000.00 E Kochhar AD_VP Executive 17000.00 E De Haan AD_VP Executive 17000.00 E Russell SA_MAN Sales 14000.00 D Partners SA_MAN Sales 13500.00 D- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
非等值链接
懂？
一个是入职之前的表，一个是之后的表SQL> select e.last_name, e.hire_date from employees e, employees d where d.hire_date < e.hire_date and d.last_name='Davies'; LAST_NAME HIRE_DATE ------------------------- ----------- Kochhar 2005/9/21 Hunold 2006/1/3 Ernst 2007/5/21 Austin 2005/6/25 Pataballa 2006/2/5 Lorentz 2007/2/7 Chen 2005/9/28 Sciarra 2005/9/30 Urman 2006/3/7 Popp 2007/12/7 Baida 2005/12/24 Tobias 2005/7/24 Himuro 2006/11/15 Colmenares 2007/8/10 Fripp 2005/4/10 Vollman 2005/10/10 Mourgos 2007/11/16 Nayer 2005/7/16 Mikkilineni 2006/9/28 Landry 2007/1/14 LAST_NAME HIRE_DATE ------------------------- ----------- Markle 2008/3/8 Bissot 2005/8/20 Atkinson 2005/10/30 Marlow 2005/2/16 Olson 2007/4/10 Rogers 2006/8/26 Gee 2007/12/12 Philtanker 2008/2/6 Stiles 2005/10/26 Seo 2006/2/12 Patel 2006/4/6 Matos 2006/3/15 Vargas 2006/7/9 Errazuriz 2005/3/10 Cambrault 2007/10/15 Zlotkey 2008/1/29 Tucker 2005/1/30 Bernstein 2005/3/24 Hall 2005/8/20 Olsen 2006/3/30 Cambrault 2006/12/9 LAST_NAME HIRE_DATE ------------------------- ----------- Tuvault 2007/11/23 Smith 2005/3/10 Doran 2005/12/15 Sewall 2006/11/3 Vishney 2005/11/11 Greene 2007/3/19 Marvins 2008/1/24 Lee 2008/2/23 Ande 2008/3/24 Banda 2008/4/21 Ozer 2005/3/11 Bloom 2006/3/23 Fox 2006/1/24 Smith 2007/2/23 Bates 2007/3/24 Kumar 2008/4/21 Hutton 2005/3/19 Taylor 2006/3/24 Livingston 2006/4/23 Grant 2007/5/24 Johnson 2008/1/4 LAST_NAME HIRE_DATE ------------------------- ----------- Taylor 2006/1/24 Fleaur 2006/2/23 Sullivan 2007/6/21 Geoni 2008/2/3 Bull 2005/2/20 Dellinger 2006/6/24 Cabrio 2007/2/7 Chung 2005/6/14 Dilly 2005/8/13 Gates 2006/7/11 Perkins 2007/12/19 Everett 2005/3/3 McCain 2006/7/1 Jones 2007/3/17 Walsh 2006/4/24 Feeney 2006/5/23 OConnell 2007/6/21 Grant 2008/1/13 Fay 2005/8/17 81 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
- 92
- 93
- 94
- 95
- 96
又是自连接SQL> select e.last_name, e.hire_date, m.last_name, m.hire_date from employees e, employees m where e.manager_id = m.employee_id and e.hire_date < m.hire_date; LAST_NAME HIRE_DATE LAST_NAME HIRE_DATE ------------------------- ----------- ------------------------- ----------- Kaufling 2003/5/1 King 2003/6/17 Raphaely 2002/12/7 King 2003/6/17 De Haan 2001/1/13 King 2003/6/17 Higgins 2002/6/7 Kochhar 2005/9/21 Baer 2002/6/7 Kochhar 2005/9/21 Mavris 2002/6/7 Kochhar 2005/9/21 Whalen 2003/9/17 Kochhar 2005/9/21 Greenberg 2002/8/17 Kochhar 2005/9/21 Austin 2005/6/25 Hunold 2006/1/3 Faviet 2002/8/16 Greenberg 2002/8/17 Bull 2005/2/20 Fripp 2005/4/10 Sarchand 2004/1/27 Fripp 2005/4/10 Marlow 2005/2/16 Fripp 2005/4/10 Everett 2005/3/3 Vollman 2005/10/10 Bell 2004/2/4 Vollman 2005/10/10 Ladwig 2003/7/14 Vollman 2005/10/10 OConnell 2007/6/21 Mourgos 2007/11/16 Feeney 2006/5/23 Mourgos 2007/11/16 Walsh 2006/4/24 Mourgos 2007/11/16 Vargas 2006/7/9 Mourgos 2007/11/16 LAST_NAME HIRE_DATE LAST_NAME HIRE_DATE ------------------------- ----------- ------------------------- ----------- Matos 2006/3/15 Mourgos 2007/11/16 Davies 2005/1/29 Mourgos 2007/11/16 Rajs 2003/10/17 Mourgos 2007/11/16 McEwen 2004/8/1 Partners 2005/1/5 Sully 2004/3/4 Partners 2005/1/5 King 2004/1/30 Partners 2005/1/5 Bates 2007/3/24 Cambrault 2007/10/15 Smith 2007/2/23 Cambrault 2007/10/15 Fox 2006/1/24 Cambrault 2007/10/15 Bloom 2006/3/23 Cambrault 2007/10/15 Ozer 2005/3/11 Cambrault 2007/10/15 Johnson 2008/1/4 Zlotkey 2008/1/29 Grant 2007/5/24 Zlotkey 2008/1/29 Livingston 2006/4/23 Zlotkey 2008/1/29 Taylor 2006/3/24 Zlotkey 2008/1/29 Hutton 2005/3/19 Zlotkey 2008/1/29 Abel 2004/5/11 Zlotkey 2008/1/29 37 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
员工是在经理入职之前就入职的
别名随意了
OK，完成多表查询各种骚操作
总结
提示：重要经验：1）
2）学好oracle，即使经济寒冬，整个测开offer绝对不是问题！同时也是你考公网络警察的必经之路。
3）笔试求AC，可以不考虑空间复杂度，但是面试既要考虑时间复杂度最优，也要考虑空间复杂度最优。 -
相关阅读:
十个强大的 React 工具
单片机简介（一）
考前刷题练手感（北航期末往年数据结构编程题）
神经网络解决哪些问题,神经网络结果不稳定
ubuntu20.04安装cmake
每天五分钟机器学习：评价算法模型常用的数值评估方式——F1值
FG6223EUUD系列模块选型参考
【Javadoc生成开发文档（Terminal或IDEA中）】
[MRCTF2020]Ezpop-1|php序列化
JS事件参数对象event
- 原文地址：https://blog.csdn.net/weixin_46838716/article/details/127569215
