-
Oracle数据库：自然连接natural join，using语句，注意避免写交叉连接
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- 1
- 2
- 3
切忌这么写
炸锅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.- 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
试一下
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- 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
这是等值连接的标准格式
简化一波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- 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
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- 1
- 2
- 3
- 4
- 5
- 6
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": 标识符无效- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
懂了吧
using，特定列，直接用列名玩
总结
提示：重要经验：1）
2）学好oracle，即使经济寒冬，整个测开offer绝对不是问题！同时也是你考公网络警察的必经之路。
3）笔试求AC，可以不考虑空间复杂度，但是面试既要考虑时间复杂度最优，也要考虑空间复杂度最优。 -
相关阅读:
liunx安装docker
磨金石教育插画技能干货分享｜插画怎么配色才好看？
从零开发短视频电商 自动化测试SikuliX-编程方式
java毕业生设计在线商城系统计算机源码+系统+mysql+调试部署+lw
第十周周报
Vue-1.8生命周期
Mybatis Plus一对多联表查询及分页解决方案
数量关系（高照）
ide快捷键以及js的小知识
完整指南：如何使用 Node.js 复制文件
- 原文地址：https://blog.csdn.net/weixin_46838716/article/details/127567237
