Oracle（3）多表连接查询、集合运算符

最新推荐文章于 2023-02-19 22:45:54 发布

shayuwei

最新推荐文章于 2023-02-19 22:45:54 发布

阅读量579

点赞数

分类专栏： Oracle 文章标签： oracle

本文链接：https://blog.csdn.net/shayuwei/article/details/89923941

版权

Oracle 专栏收录该内容

129 篇文章 7 订阅

订阅专栏

1 多表连接查询

1.1连接类型

1.语法
在这里插入图片描述

2.限制重复的列名

在多表中使用表前缀限制列名
使用表前缀可以提高效率
使用表别名代替全表名前缀
表别名提供一个较短的名称：SQL代码量更少，使用较少的内存
在不同表中具有相同列名的列可以用别名加以区分

1.1.1 NATURAL JOINS（自然连接）

1.NATURAL JOIN子句

会以两个表中具有相同名字的列为条件创建等值连接
在表中查询满足等值条件的数据
如果只是列名相同而数据类型不同，则会产生错误

2.USING子句
如果多个列具有相同的名称，但自然连接的数据类型又不匹配，则可以使用using子句来指定，使用一个等值的列
当有多个列匹配时，用using子句匹配唯一的列
NATURAL JOIN和USING子句互斥
不要给选中的列中加上表名前缀或别名
不能对USING子句中的列进行限制
对于using子句的列，如果同一列在SQL语句中其他位置被使用到，则不能对其进行别名

primary key主键列：非空且唯一
foreign key外键：跟随主键列的值，可以空值外键的值是依据主键的，连接前需要校验，如果在PK中出现过，就是符合条件的。如果是主键里没有的，就是非法的。

3.ON子句
自然连接中是以具有相同名字的列为连接条件的
使用ON子句指定要连接任意条件或指定列连接条件
这个连接条件是与其它条件分开的
ON子句使语句具有更高的易读性
语法：
筛选
定义列别名
同一列名多表使用要写上表明做前缀

1.1.2 OUTER JOINS（外连接）

两个表在连接过程中只返回匹配的行，被称为内连接
两个表在连接过程中除了返回满足连接条件的行以外，还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接
两个表在连接过程中除了返回满足连接条件的行以外，还返回两个表中不满足条件的行，这种连接称为全外连接（full outer join）

1.LEFT OUTER JOIN 左外连接
在这里插入图片描述

2.RIGHT OUTER JOIN 右外连接
在这里插入图片描述
两种写法：

因为右边的都能匹配上
3.FULL OUTER JOIN 全外连接

1.1.3 自连接表

自链接：表里有从属关系
查询员工的姓名以及经理的员工号
在这里插入图片描述

1.1.4 非等值连接

查询工资等级的两种写法：
在这里插入图片描述

加条件语句

1.1.5 笛卡儿积：CROSS JOINS（交叉连接）

笛卡儿积的产生条件：
1）多表连接遗漏条件时
2）连接条件不全
3）所有表中所有的行互相连接
为了避免笛卡儿积，可以在where加入有效的连接条件
交叉连接
1）使用CROSS JOIN子句使连接的表产生交叉集
2）交叉集也被称为在两个表之间的笛卡儿积

1.2 使用子查询

子查询（内查询）在主查询（外查询）之前执行。
主查询使用子查询结果。
先执行子查询，再执行主查询。
子查询是嵌入在另一个SELECT语句子句中的SELECT语句。可以使用子查询从简单语句构建功能强大的语句。当需要从表中选择具有依赖于表本身的数据的条件的行时，它们可能非常有用。
子查询可以放在许多SQL子句中：WHERE\HAVING\FROM

1.2.1 where子句中使用子查询

在这里插入图片描述

注意事项
1）子查询要包在括号内
2）将子查询放在比较条件的右侧增强可读性（子查询可以出现在比较运算符的两侧）
3）单行操作符对应单行子查询，多行操作符对应多行子查询
单行子查询特点
1）只返回一行
2）使用单行比较操作符

注意：前后数据格式要统一

1.2.2 在子查询中使用组函数

在这里插入图片描述

1.2.3 在子查询中使用HAVING子句

向主查询中having子句返回结果

平均工资最低的职位：
在这里插入图片描述

1.2.4 子查询中的空值问题

对于查不出数据的就是null，要在上面写上is null

1.2.5 多行子查询

特点：
1）返回多行
2）使用多行比较操作符

1.IN等于列表中的一个值
在这里插入图片描述
有两个简单例子，以说明 “exists”和“in”的效率问题
1）select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ;
T1数据量小而T2数据量非常大时，T1<<T2 时，1）的查询效率高。

2）select * from T1 where T1.a in (select T2.a from T2) ;T1数据量非常大而T2数据量小时，T1>>T2 时，2）的查询效率高。

2.ANY 与列表中的任意值比较
在这里插入图片描述
<ANY 小于最大值的意思
>ANY 表示大于最小值

=ANY 等于IN，等于=ALL

3.ALL 与列表中的所有值比较
>ALL 表示大于整个列表的最大值
<ALL 小于最小值

1.2.6 在FROM子句中使用子查询

相当于生成一个临时的表，然后自连接
在这里插入图片描述

1.2.7 select子查询

在这里插入图片描述

1.2.8 关联子查询

在这里插入图片描述

普通子查询：在主查询执行之前，子查询首先执行一次。子查询的结果要在主查询中使用
关联子查询：需要重复执行子查询（效率会低）

1.2.9 结果集分页

伪表 dual
伪列 rownum
不跟随某一列，而是看是怎么查询的
在这里插入图片描述
分页：

直接用rownum只能用<，因为rownum是一个无限大的，想用好rownum最好把它设置成一个单独的列。

多表连接查询练习
https://www.jianshu.com/p/f5f49379a0b4

2 集合运算符

1.作用：

描述集合运算
将多个查询用集合运算符连接组成一个单个的查询
控制返回的顺序

2.集合操作：
在这里插入图片描述
3.集合操作注意事项：
1）在select列表中的列名和表达式在数量上必须匹配

2）第二个查询中的每一列的数据类型必须与第一个查询其对应的列的数据类型相匹配

3）可以用括号来改变执行顺序
4）order by子句只能在语句的最后出现，可以使用第一个查询中的列名，别名或相对位置
5）除union all以外，系统会自动删除重复的记录
6）列名为第一个查询返回的结果
7）除union all以外，系统自动按照第一个查询中的第一个列的升序排列

创建一个表：
创建一个和dept表结构相同但没有数据的表：
删除一个表

2.1 UNION操作符

union操作符返回两个查询的结果集的并集，并消除重复行
注意：
1.选择的列数必须相同
2.所选列的数据类型必须在相同的数据类型组中（如数字或字符）
3.列的名称不必相同
4.union操作选择的所有列
5.在重复检查期间，NULL值不会被忽略
6.默认情况下，输出按照select子句列的升序排序

2.2 UNION ALL操作符

union all操作符返回两个查询的结果集的并集，包括所有重复行
在这里插入图片描述

删除dept2中deptno为30和40的两行
在这里插入图片描述
如果确认就输入命令commit，如果撤销就输入命令rollback；

2.3 INTERSECT操作符

intersect操作符返回两个查询公共的行
注意：
1）查询中select语句选择的列的数量和数据类型必须与查询中使用的所有select语句相同，但是列的名称不必相同。
2）颠倒交叉表的顺序不会改变结果。
3）intersect不会忽略空值。
在这里插入图片描述

2.4 MINUS操作符

minus是从第一个查询结果减去第二个查询结果，如果有相交部分就减去相交部分；否则和第一个查询结果没有区别。
注意：列的数量必须相同，查询中的select语句所选择的列的数据类型必须属于查询中使用的所有select语句中的相同数据类型组。但是列的名称不必相同。
在这里插入图片描述

相匹配的select语句
当字段在一个或另一个表中不存在，必须匹配上数据类型。
集合操作中使用order by
1）复合查询中order by子句只能在结束时出现一次
2）集合操作中每个查询不能有单独的order by子句
3）order by子句中只能识别第一个select查询的列
4）默认情况下，第一列的第一个select查询使用升序对输出进行排序