Oracle数据库：自然连接natural join，using语句，注意避免写交叉连接
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扩展的左右外连接展示符号(+)
oracle交叉连接：应该避免写这个东西
直接炸锅了卧槽
一定要避免这么写，炸了………………
select * from employees cross join departments;
2889 rows selected
切忌这么写
炸锅
oracle自然连接natural join是等值连接的另一种写法
列名和数据类型
必须都一样
多个字段相等，那就且关系
所以呢，你建立表的时候，就要考虑这个问题
natural join的本质是等值连接的简化
性能一样
懂吧
最好是等值连接
这个一查就是所有列的等
departments表
locations表
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.
试一下
SQL> select d.department_id,d.department_name,l.city from departments d,locations l where d.location_id = l.location_id;
DEPARTMENT_ID DEPARTMENT_NAME CITY
------------- ------------------------------ ------------------------------
60 IT Southlake
50 Shipping South San Francisco
10 Administration Seattle
30 Purchasing Seattle
90 Executive Seattle
100 Finance Seattle
110 Accounting Seattle
120 Treasury Seattle
130 Corporate Tax Seattle
140 Control And Credit Seattle
150 Shareholder Services Seattle
160 Benefits Seattle
170 Manufacturing Seattle
180 Construction Seattle
190 Contracting Seattle
200 Operations Seattle
210 IT Support Seattle
220 NOC Seattle
230 IT Helpdesk Seattle
240 Government Sales Seattle
DEPARTMENT_ID DEPARTMENT_NAME CITY
------------- ------------------------------ ------------------------------
250 Retail Sales Seattle
260 Recruiting Seattle
270 Payroll Seattle
20 Marketing Toronto
40 Human Resources London
80 Sales Oxford
70 Public Relations Munich
27 rows selected
这是等值连接的标准格式
简化一波
SQL> select d.department_id,d.department_name,l.city from departments d natural join locations l;
DEPARTMENT_ID DEPARTMENT_NAME CITY
------------- ------------------------------ ------------------------------
60 IT Southlake
50 Shipping South San Francisco
10 Administration Seattle
30 Purchasing Seattle
90 Executive Seattle
100 Finance Seattle
110 Accounting Seattle
120 Treasury Seattle
130 Corporate Tax Seattle
140 Control And Credit Seattle
150 Shareholder Services Seattle
160 Benefits Seattle
170 Manufacturing Seattle
180 Construction Seattle
190 Contracting Seattle
200 Operations Seattle
210 IT Support Seattle
220 NOC Seattle
230 IT Helpdesk Seattle
240 Government Sales Seattle
DEPARTMENT_ID DEPARTMENT_NAME CITY
------------- ------------------------------ ------------------------------
250 Retail Sales Seattle
260 Recruiting Seattle
270 Payroll Seattle
20 Marketing Toronto
40 Human Resources London
80 Sales Oxford
70 Public Relations Munich
27 rows selected
natural join
的用法就如此
联合俩表相等的列
相当于直接用location_id相同
那通过部门的location_id，去查city信息
所以设计表格的时候，你要设计好
否则就没法用
oracle using子句
using子句
左右表都有id，x列都相同
natural join就会做判断，id和x都得相同
如果用using id
那只看id，不看x
而且你不能用表的别名玩，这个是注意的地方
natural join和using子句是相互排斥的
指定using
不妨设别的可能还相同
那就要限制
行选城市id是1800的
SQL> select d.department_name,l.city from departments d join locations l using(location_id) where location_id=1800;
DEPARTMENT_NAME CITY
------------------------------ ------------------------------
Marketing Toronto
natural不能要了哦
另外using加括号（）
然后限定行放屁股
只限定location_id链接，这就是using语句的好处
和natural冲突的
另外你要用location_id，还不表名或者别名了
不能限定了
你看
SQL> select d.department_name,l.city from departments d join locations l using(location_id) where location_id=1800;
DEPARTMENT_NAME CITY
------------------------------ ------------------------------
Marketing Toronto
SQL> select d.department_name,l.city from departments d join locations l using(location_id) where locations.location_id=1800;
select d.department_name,l.city from departments d join locations l using(location_id) where locations.location_id=1800
ORA-00904: "LOCATIONS"."LOCATION_ID": 标识符无效
懂了吧
using，特定列，直接用列名玩
总结
提示：重要经验：
1）
2）学好oracle，即使经济寒冬，整个测开offer绝对不是问题！同时也是你考公网络警察的必经之路。
3）笔试求AC，可以不考虑空间复杂度，但是面试既要考虑时间复杂度最优，也要考虑空间复杂度最优。
5535
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
