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
这老复杂了
其实这不复杂
可以用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
中间两次连接很烦，挺狗的啊感觉，谁发明的…………
只是帮助做等值连接和非等值链接的一种方法
不建议写这种
当然为了考试，也就了解一下
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
俩性能没区别
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
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
也还行反正就这样吧
多表查询练习示例
复习
俩表
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
这是等值连接
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
则是内连接，好说
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
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.
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
你可以发现很多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
employees表
departments表
locations表
2个链接表的条件
条件，有佣金，is not null
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.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
稳
模糊匹配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
城市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
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
自连接
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
经理和雇员都是雇员
所以雇员的雇员编号好搞
但是经理的雇员编号需要自连接，懂？？？
这个好好屡清楚，骚得很
左外，右外都行
最好把员工描述为左表
经理表用作右表
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
你看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
很多同事
查询雇员的薪资等级，A–F
employees表
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
非等值链接
懂？
一个是入职之前的表，一个是之后的表
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
又是自连接
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
员工是在经理入职之前就入职的
别名随意了
OK，完成多表查询各种骚操作
总结
提示：重要经验：
1）
2）学好oracle，即使经济寒冬，整个测开offer绝对不是问题！同时也是你考公网络警察的必经之路。
3）笔试求AC，可以不考虑空间复杂度，但是面试既要考虑时间复杂度最优，也要考虑空间复杂度最优。
988
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
