Oracle数据库：oracle多表查询，等值连接，非等值连接，自连接的sql语句和规则
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单行函数练习示例
oracle多表查询
工作中经常需要这么玩
网警也不例外
俩表中的列对比呀啥的
SQL> desc employees;
Name Type Nullable Default Comments
-------------- ------------ -------- ------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EMPLOYEE_ID NUMBER(6) Primary key of employees table.
FIRST_NAME VARCHAR2(20) Y First name of the employee. A not null column.
LAST_NAME VARCHAR2(25) Last name of the employee. A not null column.
EMAIL VARCHAR2(25) Email id of the employee
PHONE_NUMBER VARCHAR2(20) Y Phone number of the employee; includes country code and area code
HIRE_DATE DATE Date when the employee started on this job. A not null column.
JOB_ID VARCHAR2(10) Current job of the employee; foreign key to job_id column of the
jobs table. A not null column.
SALARY NUMBER(8,2) Y Monthly salary of the employee. Must be greater
than zero (enforced by constraint emp_salary_min)
COMMISSION_PCT NUMBER(2,2) Y Commission percentage of the employee; Only employees in sales
department elgible for commission percentage
MANAGER_ID NUMBER(6) Y Manager id of the employee; has same domain as manager_id in
departments table. Foreign key to employee_id column of employees table.
(useful for reflexive joins and CONNECT BY query)
DEPARTMENT_ID NUMBER(4) Y Department id where employee works; foreign key to department_id
column of the departments table
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_NAME 是第二个表
DEPARTMENT_ID 是2个表公有的
而EMPLOYEE_ID 只有employees表才有
如果
你想查EMPLOYEE_ID 的department_name
那就需要你通过employees和departments关联查询
内连接
2个表的不同的列
做相等判断
相等外的判断，其他比较，非等值
外连接
AB表的列对比，相同的带上
不相等的列，左边的孤儿数据【左外连接】
不相等的列，右边的孤儿数据【右外连接】
不相等的列，左边和右边的孤儿数据都拿过来【全外连接】——mysql不支持这个事
oracle的特定语法，很强，自己的强大之处
子查询
嵌套查询
多表连接语法结构sql86标准
通过表的别名，然后.
作为前缀，来区分俩表都相同的列
这个别名一会说
最好加表名别名，这样看得清楚，访问速度快
等值连接，最常用
要什么？
表来自哪里？
条件是啥？
这是我们经常要用的信息
名字和部门，分别在俩表
id是相同的就行
关联的主键是部门id，
要啥？名字，部门名字
俩表
条件，显然是表通过部门id挂起来的
SQL> desc employees;
Name Type Nullable Default Comments
-------------- ------------ -------- ------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EMPLOYEE_ID NUMBER(6) Primary key of employees table.
FIRST_NAME VARCHAR2(20) Y First name of the employee. A not null column.
LAST_NAME VARCHAR2(25) Last name of the employee. A not null column.
EMAIL VARCHAR2(25) Email id of the employee
PHONE_NUMBER VARCHAR2(20) Y Phone number of the employee; includes country code and area code
HIRE_DATE DATE Date when the employee started on this job. A not null column.
JOB_ID VARCHAR2(10) Current job of the employee; foreign key to job_id column of the
jobs table. A not null column.
SALARY NUMBER(8,2) Y Monthly salary of the employee. Must be greater
than zero (enforced by constraint emp_salary_min)
COMMISSION_PCT NUMBER(2,2) Y Commission percentage of the employee; Only employees in sales
department elgible for commission percentage
MANAGER_ID NUMBER(6) Y Manager id of the employee; has same domain as manager_id in
departments table. Foreign key to employee_id column of employees table.
(useful for reflexive joins and CONNECT BY query)
DEPARTMENT_ID NUMBER(4) Y Department id where employee works; foreign key to department_id
column of the departments table
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.
查询
SQL> select last_name,department_name from employees,departments where employees.department_id=departments.department_id;
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Whalen Administration
Fay Marketing
Hartstein Marketing
Tobias Purchasing
Colmenares Purchasing
Baida Purchasing
通过俩表的部门id，挂接人名，和部门名称
这样就直接现实了两个表格中我们要的信息
而且人要对应自己的那个部门，主键就是那个部门id
还得看名字是Taylor的人的话
SQL> select last_name,department_name from employees,departments where employees.department_id=departments.department_id and last_name='Goyal';
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
SQL> select last_name,department_name from employees,departments where employees.department_id=departments.department_id and last_name='Taylor';
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Taylor Sales
Taylor Shipping
and条件没有先后顺序
就是查一个人的很多信息，只是信息在不同的表里面
那就要通过主键外挂链接
表别名，和列别名一样，简化
整个语句中都可以用
跟列不一样，列是第3个执行的，你起别名，12步是不行的【前面文章我说过】
SQL> select last_name,department_name from employees em,departments de where em.department_id=de.department_id and last_name='Taylor';
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Taylor Sales
Taylor Shipping
注意哦，可不是加as
而是直接就空格，起别名即可
越简短越好
最关注，应该把链接条件搞清楚，防止产生笛卡尔乘积
仨表了
SQL> desc locations
Name Type Nullable Default Comments
-------------- ------------ -------- ------- -----------------------------------------------------------------------------------------------------------------------------------------
LOCATION_ID NUMBER(4) Primary key of locations table
STREET_ADDRESS VARCHAR2(40) Y Street address of an office, warehouse, or production site of a company.
Contains building number and street name
POSTAL_CODE VARCHAR2(12) Y Postal code of the location of an office, warehouse, or production site
of a company.
CITY VARCHAR2(30) A not null column that shows city where an office, warehouse, or
production site of a company is located.
STATE_PROVINCE VARCHAR2(25) Y State or Province where an office, warehouse, or production site of a
company is located.
COUNTRY_ID CHAR(2) Y Country where an office, warehouse, or production site of a company is
located. Foreign key to country_id column of the countries table.
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.
LOCATION_ID NUMBER(4) Y Location id where a department is located. Foreign key to location_id column of locations table.
通过LOCATION_ID 作为主键来关联的另外一个表的
SQL> select em.last_name,de.department_name,lo.city from employees em, departments de, locations lo where de.location_id = lo.location_id and em.department_id = de.department_id;
LAST_NAME DEPARTMENT_NAME CITY
------------------------- ------------------------------ ------------------------------
Abel Sales Oxford
Ande Sales Oxford
Atkinson Shipping South San Francisco
Austin IT Southlake
Baer Public Relations Munich
至少2个条件
刚刚我少写了一个条件
结果查到了2889条…………………………
gg
2889 rows selected
难道这就是笛卡尔乘积？？？？？？？？
很操蛋
反正名字，部门名，城市，要同时展示的话
那就看哪个表的数据少，按照这个最少的行展示
SQL> select em.last_name,de.department_name,lo.city from employees em, departments de, locations lo where de.location_id = lo.location_id and em.department_id = de.department_id and em.last_name='Taylor';
LAST_NAME DEPARTMENT_NAME CITY
------------------------- ------------------------------ ------------------------------
Taylor Sales Oxford
Taylor Shipping South San Francisco
可以看出where它可以同时放表的链接条件
也可以放单个表里面的列的限制条件
这些多表连接还得好好地一步步学习
才能搞懂，否则语句复杂，你看不懂的
非等值链接
工资级别
我们自己创建一下
SQL> create table JOB_GRADES(losest_sal number, highest_sal number, gra varchar2(10));
Table created
表的仨属性，最低薪水，最高薪水
工资级别A-F等
然后编辑数据，加入这些
然后我们就可以玩非等值查询了
雇员名
薪水来源于employees表
级别来源于薪水级别表
between and
SQL> select em.last_name,em.salary,gr.gra from employees em, job_grades gr where em.salary between gr.losest_sal and gr.highest_sal;
LAST_NAME SALARY GRA
------------------------- ---------- ----------
我这命令没问题啊，不知道为啥没有数据
我服了
重新登录就好了，要更新的
SQL> select em.last_name,em.salary,gr.gra from employees em,job_grades gr where em.salary between gr.losest_sal and gr.highest_sal;
LAST_NAME SALARY GRA
------------------------- ---------- ----------
Baida 2900.00 A
Tobias 2800.00 A
Himuro 2600.00 A
Colmenares 2500.00 A
Mikkilineni 2700.00 A
自连接
相当于内部有等级关系
SQL> desc employees;
Name Type Nullable Default Comments
-------------- ------------ -------- ------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EMPLOYEE_ID NUMBER(6) Primary key of employees table.
FIRST_NAME VARCHAR2(20) Y First name of the employee. A not null column.
LAST_NAME VARCHAR2(25) Last name of the employee. A not null column.
EMAIL VARCHAR2(25) Email id of the employee
PHONE_NUMBER VARCHAR2(20) Y Phone number of the employee; includes country code and area code
HIRE_DATE DATE Date when the employee started on this job. A not null column.
JOB_ID VARCHAR2(10) Current job of the employee; foreign key to job_id column of the
jobs table. A not null column.
SALARY NUMBER(8,2) Y Monthly salary of the employee. Must be greater
than zero (enforced by constraint emp_salary_min)
COMMISSION_PCT NUMBER(2,2) Y Commission percentage of the employee; Only employees in sales
department elgible for commission percentage
MANAGER_ID NUMBER(6) Y Manager id of the employee; has same domain as manager_id in
departments table. Foreign key to employee_id column of employees table.
(useful for reflexive joins and CONNECT BY query)
DEPARTMENT_ID NUMBER(4) Y Department id where employee works; foreign key to department_id
column of the departments table
manager_id经理和employee_id就上下级关系
SQL> select workee.last_name,manager.last_name from employees workee,employees manager where workee.manager_id=manager.employee_id;
LAST_NAME LAST_NAME
------------------------- -------------------------
Smith Cambrault
Ozer Cambrault
Kumar Cambrault
Fox Cambrault
Bloom Cambrault
Bates Cambrault
Hunold De Haan
Vishney Errazuriz
Marvins Errazuriz
Lee Errazuriz
Greene Errazuriz
Banda Errazuriz
Ande Errazuriz
Sarchand Fripp
Olson Fripp
Marlow Fripp
Dellinger Fripp
Cabrio Fripp
Bull Fripp
Bissot Fripp
LAST_NAME LAST_NAME
------------------------- -------------------------
Atkinson Fripp
Urman Greenberg
Sciarra Greenberg
Popp Greenberg
Faviet Greenberg
Chen Greenberg
Fay Hartstein
Gietz Higgins
Pataballa Hunold
Lorentz Hunold
Ernst Hunold
Austin Hunold
Rogers Kaufling
Philtanker Kaufling
Perkins Kaufling
Mallin Kaufling
Gee Kaufling
Gates Kaufling
Dilly Kaufling
Chung Kaufling
Zlotkey King
LAST_NAME LAST_NAME
------------------------- -------------------------
Weiss King
Vollman King
Russell King
Raphaely King
Partners King
Mourgos King
Kochhar King
Kaufling King
Hartstein King
Fripp King
Errazuriz King
De Haan King
Cambrault King
Whalen Kochhar
Mavris Kochhar
Higgins Kochhar
Greenberg Kochhar
Baer Kochhar
Walsh Mourgos
Vargas Mourgos
Rajs Mourgos
LAST_NAME LAST_NAME
------------------------- -------------------------
OConnell Mourgos
Matos Mourgos
Grant Mourgos
Feeney Mourgos
Davies Mourgos
Sully Partners
Smith Partners
Sewall Partners
McEwen Partners
King Partners
Doran Partners
Tobias Raphaely
Khoo Raphaely
Himuro Raphaely
Colmenares Raphaely
Baida Raphaely
Tuvault Russell
Tucker Russell
Olsen Russell
Hall Russell
Cambrault Russell
LAST_NAME LAST_NAME
------------------------- -------------------------
Bernstein Russell
Stiles Vollman
Seo Vollman
Patel Vollman
McCain Vollman
Ladwig Vollman
Jones Vollman
Everett Vollman
Bell Vollman
Taylor Weiss
Sullivan Weiss
Nayer Weiss
Mikkilineni Weiss
Markle Weiss
Landry Weiss
Geoni Weiss
Fleaur Weiss
Taylor Zlotkey
Livingston Zlotkey
Johnson Zlotkey
Hutton Zlotkey
LAST_NAME LAST_NAME
------------------------- -------------------------
Grant Zlotkey
Abel Zlotkey
106 rows selected
select workee.last_name,manager.last_name from employees workee,employees manager
这里面，两次用到了同一个表，自连接
where workee.manager_id=manager.employee_id;
条件是啥，普通员工要去找经理，经理表要找自己的id，叫employee_id
很巧妙啊
这就是自连接，很骚
你可千万别写成：
where workee.manager_id=manager.manager_id;
这样相当于没有自连接
咱们要自连接的是经理和员工
而不是经理和经理
总结
提示：重要经验：
1）
2）学好oracle，即使经济寒冬，整个测开offer绝对不是问题！同时也是你考公网络警察的必经之路。
3）笔试求AC，可以不考虑空间复杂度，但是面试既要考虑时间复杂度最优，也要考虑空间复杂度最优。
560
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
