复杂查询

最新推荐文章于 2024-06-24 11:37:44 发布

_Zephyrus_

最新推荐文章于 2024-06-24 11:37:44 发布

阅读量341

点赞数

分类专栏： Oracle 文章标签：相关子查询相交/相减运算内外连接

本文链接：https://blog.csdn.net/wangxw1803/article/details/84952712

版权

Oracle 专栏收录该内容

35 篇文章 1 订阅

订阅专栏

多表链接查询

在SQL中，操作多个表的数据称为连接。在Oracle中有两种类型的连接格式。分别为ANSI SQL连接格式和Oracle特有的连接格式。Oracle建议使用符合ANSI标准的连接格式。

内连接

SELECT emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dname, dept.deptno
FROM scott.emp,scott.dept WHERE emp.deptno = dept.deptno;

在dept表中，deptno是主键列，emp中的deptno也是主键列，两者建立了主外键的关联。关联两个表中相等的列，这种连接称为等值连接，也称为简单连接或内连接

-- 查询部门20的人员列表
SELECT emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dname, dept.deptno
FROM scott.emp,scott.dept WHERE emp.deptno = dept.deptno AND emp.deptno = 20;

如果表名很长，在每个列前面使用表名限制会使SQL代码变得冗长，可以为每个表使用表别名

SELECT x.empno,x.ename,x.job,x.hiredate,x.sal,y.dname, y.deptno
FROM scott.emp x, scott.dept y WHERE x.deptno = y.deptno AND x.deptno = 20;

使用内连接的ANSI SQL表示方式

SELECT x.empno,x.ename,x.job,x.hiredate,x.sal,y.dname, y.deptno
FROM scott.emp x INNER JOIN scott.dept y ON x.deptno = y.deptno 
WHERE x.deptno = 20;

ANSI SQL标准内连接语法是使用INNER JOIN连接左右两个表，通过ON子句指定两个表的连接条件，WHERE子句来制定条件子句

外连接

SQL外连接分为两类，分别用于保存左侧表内容的左连接和保存右侧表内容的右连接
通过在连接条件中使用（+）表示外连接查询，根据（+）所在字段位置的不同分为两类

左外连接：当（+）出现在等号左边时，将返回table2中所有的数据
右外连接：当（+）出现在等号右边时，将返回table1中所有的数据

SELECT x.empno, x.ename, x.job, x.hiredate, x.sal, y.dname  FROM emp x, dept y
WHERE x.deptno = y.deptno(+);

SELECT x.empno, x.ename, x.job, x.hiredate, x.sal, y.dname  FROM emp x, dept y
WHERE x.deptno(+) = y.deptno;

ANSI SQL 左连接、右连接语法

SELECT x.empno, x.ename, x.job, x.hiredate, x.sal, y.dname 
FROM SCOTT.emp x LEFT OUTER JOIN SCOTT.dept y ON x.deptno = y.deptno;

SELECT x.empno, x.ename, x.job, x.hiredate, x.sal, y.dname 
FROM SCOTT.emp x RIGHT OUTER JOIN SCOTT.dept y ON x.deptno = y.deptno;

在ANSI SQL 中，FULL OUTER JOIN 表示全连接，除了包含连接的数据外，还包含连接表中不符合连接条件的数据

SELECT x.empno, x.ename, x.job, x.hiredate, x.sal, y.dname FROM
emp x FULL OUTER JOIN dept y ON x.deptno = y.deptno;

交叉连接

交叉链接是指用A表中的记录行与B表中的记录行数想成得到的笛卡儿乘积，如果在进行链接查询时不指定任何连接条件，将产生交叉查询。

SELECT x.empno, x.ename, x.job, x.hiredate, x.sal, y.dname FROM emp x, dept y;

ANSI SQL 标准可以使用CROSS JOIN 来实现交叉查询

SELECT x.empno, x.ename, x.job, x.hiredate, x.sal, y.dname FROM emp x CROSS JOIN dept y;

自然连接

如果两个表中有相同名字和数据类型的列，那么可以使用自然连接来自动匹配数据类型和列名。自然连接使用NATURAL JOIN 关键字

SELECT x.empno, x.ename, x.job, x.hiredate, x.sal, y.dname FROM emp x NATURAL JOIN dept y;

子查询

一些查询必须要基于另一个结果才能完成数据的提取，这种查询的构造方式称为子查询

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'SMITH');

在SQL语句执行时，首先执行内查询，然后用内查询结果执行外查询，最后执行整个查询，显示相同的结果，这种查询方式称为非相关子查询
子查询分为两类

相关子查询：相关子查询的执行依赖于外部查询的数据，外部执行一行，子查询就执行一次
非相关子查询：非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询
由于相关子查询在外查询执行一次时，同时也要执行内部查询，因此非相关子查询的效率比相关子查询高。
使用子查询时，需要遵循如下的基本原则
子查询放在括号中
子查询放在比较条件的右边
在单行子查询中用单行运算符，在多行子查询中用多行运算符
非相关子查询
非相关子查询只会执行一次，使得外部的查询可以根据子查询的单一结果或集来进行比较。非相关子查询根据返回的结果又可细分为如下三类
单行单列子查询：又称标量子查询，通常与比较运算符不如 =、<、>、!=、<=、>=联合使用。
多行单列子查询：返回单列多行数据时，不允许与比较运算符进行组合运算符，必须使用特定的关键字如ANY和ALL来将外层查询的单个值与子查询的多行进行比较运算
多列子查询：返回多列数据的子查询，这种类型的子查询通常在UPDATE语句中
对于单行单列子查询，通常用于外查询中的某个结果进行比较的场合，可以使用多种单行比较运算符

SELECT empno,ename,job,mgr,hiredate,sal FROM emp
WHERE job = (SELECT job FROM emp WHERE ename = 'SMITH');

子查询中还可以使用分组函数对结果进行分组，例如可以使用MIN函数计算emp中最低薪资，外查询以该最低薪资作为查询条件

SELECT empno, ename, job, mgr, hiredate, sal FROM emp 
WHERE sal = (SELECT MIN(sal) FROM emp);

如果子查询返回多行，则不能使用大于、等于之类的单行比较符，需要使用多行比较符

IN：等于列中任何成员
ANY：比较子查询返回的每个值
ALL：比较子查询返回的全部值

--各部门最低的员工列表
SELECT empno, ename, job, mgr, hiredate, sal, deptno FROM emp
WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno)；

查询语句被执行时，先执行IN子句，产生一个查询结果，然后主查询块处理和使用由内查询返回的值完成其搜索条件，IN关键字表示主查询中薪资列只要在子查询的多行列表中存在，则返回该行数据

SELECT empno, ename, job, mgr, hiredate, sal FROM emp 
WHERE sal > ANY(SELECT sal FROM emp WHERE job = 'clerk') AND job <> 'clerk';

比较操作符加ANY的含义如下

<ANY：小于最大值
ANY：大于最大值
=ANY：等同于IN
<ALL：小于最小值
ALL：大于最大值
注：ANY具有一个同义词SOME，具有与ANY一样的功能，在使用SOME或ANY时，通常用DISTINCT关键字来防止返回被多次选择的行，以提升查询的功能
ALL运算符比较一个值与子查询返回的每个值，他与ANY都用于比较子查询中的每一个值，只是其比价有所区别

SELECT empno, ename, job, mgr, hiredate, sal FROM emp 
WHERE sal > ALL (SELECT sal FROM emp WHERE job = 'ANALYST') AND job <> 'ANALYST';

相关子查询
相关子查询不像非相关子查询那样只执行依次，每当主查询或外查询执行一次时，会执行依次相关子查询。相关子查询中的内层查询徐哎哟啊引用到一个或多个包含他的外层查询的列值，它在外部查询执行之后每次都需要执行。

SELECT e1.empno, e1.ename, e1.deptno FROM emp e1
WHERE e1.sal > (SELECT AVG(sal) FROM emp e2 WHERE e2.deptno = e1.deptno)
ORDER BY e1.deptno；

子查询通过外部查询的表别名获取外查询的deptno列，然后计算外部列的部门平均值，最后将其作为外部WHERE子句的条件值返回

表集合操作

使用SELECT语句进行查询时，查询的结果为一个结果集，通过使用集合运算，可以将查询的结果的层个或多个部分结合到一个结果中，包含集合的查询称为复合查询
在Oracle SQL中，可供使用的集合运算具有入下几种类型

联合运算：从两个查询返回的结果去掉重复值后合并后的结果，使用UNION操作符
全联合运算：与联合运算相似，返回两个查询结果的并集，包括所有的重复值.使用UNION ALL操作符
相交运算：返回多个查询结果中相同的行，使用INTERSECT操作符
相减运算：返回在第一个查询中存在而在第二个查询中不存在的行，使用MINUS操作符
1.联合与全联合运算
联合与全联合用于合并两个表中的数据，二者区别在于联合不包括重复值，而全联合包括重复值
使用全联合运算具有如下一些原则
被选择的列数和列的数据类型必须与所有用在查询中的SELECT 语句一致。列的类型可以不相同
联合运算在做重复检查的时候不忽略空值（NULL值）
默认情况下，输出以SELECT子句的第一列的升序排序。
IN运算有比UNION运算高的优先级
联合运算在所有被选择的列上进行
使用UNION来取出emp表中部门为20的记录，与emp_history表中部门为30的记录合并

select empno, ename, sal, hiredate, deptno from scott.EMP
where deptno = 20
union 
select empno, ename, sal, hiredate, deptno from emp_history
where deptno = 30
order by DEPTNO;

UNION运算会消除重复的记录，如果有相同的记录出现在emp与emp_history表中，该记录仅出现一次，示例中特意选择部门为20和30的员工列表去掉了重复的值。
UNION ALL 与UNION类似，只是UNION ALL并不消除重复行，并且默认情况下并不输出排序，不能使用DISTINCT 关键字

select empno, ename, sal, hiredate, deptno from scott.EMP
where deptno = 20
union ALL
select empno, ename, sal, hiredate, deptno from emp_history
where deptno = 20
order by DEPTNO;

2.相交运算
相交运算INTERSECT返回多个查询中相同的行

在查询中被SELECT 语句选择的列和数据类型必须与查询中使用的所有的SELECT 语句中的一样，但列名可以不同
相交的表的倒序排序不改变结果
香蕉不忽略空值

select empno, ename, sal, hiredate, deptno 
from scott.emp where deptno = 20
INTERSECT
SELECT empno, ename, sal, hiredate, deptno 
from emp_h where deptno = 20;

3相减运算
与相交运算相反，相减运算用于去除重复的结果值，即查询在第一个表中而不在第二个表中的行。使用原则与INTERSECT完全相同，因此相交运算的INTERSECT语句更改为MINUS语句，可以看到结果不包括任何行

select empno, ename, sal, hiredate, deptno 
from scott.emp where deptno = 20
MINUS
SELECT empno, ename, sal, hiredate, deptno 
from emp_h where deptno = 20;

可以在一个查询中使用多个集合运算符，比如可以同时使用UNION、UNION ALL或MINUS、INTERSECT 进行多个数据表的集合运算。当包含多个集合运算时，可以使用圆括号改变执行顺序，使用order by子句时，order by只能出现在语句的最后，并且排序的列名是从第一个SELECT语句接受列名、别名或位置记号

层次化查询

尽管表默认呈现为行和列的二维表格，但是在很多时候需要在二维表格中存储层次化的数据。Oracle提供了层次化查询的特性，可以通过使用SQL语句表中存储的具有层次化特性的数据转换为树状的数据
Oracle提供了对于ANSI SQL的扩展来简化对树状层次结构的遍历。

START WITH…CONNECT BY 子句
PRIOR 操作符
LEVEL伪劣
通过在SELECT 与剧中包含START WITH和CONNECT BY子句，可以很容易的提取表中的层次结构的数据
START WITH：指定层次结构中的根节点，所有满足条件的都可以被考虑为根节点。
CONNECT BY：指定层次结构中父行和子行之间的关系，这个关系是一个比较表达式，用来当前行的开始比较其夫行的列的相应值。
注：PRIOR是Oracle内置操作符，仅用来处理层次化的查询
在层次化查询中，CONNECT BY子句指定父行和子行之间的关系，当在CONNECT BY条件中使用了PRIOR关键字后，在PRIOR关键字之后的表达式被当作查询中当前父列进行计算，例如查询员工表中的组织层次结构

SELECT employee_id, manager_id, first_name, last_name, hire_date
FROM scott.employees
--表示根节点为manager_id
START WITH manager_id IS NULL
--PRIOR表示父行的employee_id, 等于当前行的manager_id
CONNECT BY PRIOR employee_id = manager_id;

在语句中，START WITH以manger_id IS NULL作为跟节点，从查询结果来看，King满足这个条件，在CONNECT BY子句中，紧随其后的PRIOR操作符，其后面的列名表示是父项列的列名，而manager_id表示子项manager_id的值，根据CONNECT BY PRIOR规则向下寻找，形成树状的层次结构查询