DB（五）：关联查询、子查询、关联子查询、分页查询

---

ORACLE SQL

包括关联查询内链接、外链接、自查询；子查询在SELECT、WHERE、FROM语句中使用，子查询的执行过程及注意事项；关联子查询EXISTS、NOT EXISTS的执行过程，IN和EXISTS的区别；分页查询。

---

一、关联查询

（1）、从多张表中查询对应记录的信息

（2）、关联查询的重点在于这些表中的记录的对应关系，这个关系也称为连接条件

-- 查询每个员工的名字及其所在部门的名字
SELECT myemployee_liu.name,dept.dname,myemployee_liu.deptno FROM myemployee_liu,dept WHERE myemployee_liu.deptno=dept.deptno;
1
2

（3）、当两张表有同名字段时，SELECT子句中必须明确指定该字段来自那张表，在关联查询中，表名也可以添加别名，这样可以简化SELECT语句的复杂度

-- 查询每个员工的名字及其所在部门的名字
SELECT m.name,d.dname,m.deptno FROM myemployee_liu m,dept d WHERE m.deptno=d.deptno;
1
2

（4）、关联查询要添加连接条件，否则会产生笛卡尔积
笛卡尔积通常是一个无意义的结果集，它的记录数是所有参与查询的表的记录数乘积的结果，要避免出现，数据量大时极易出现内存溢出等现象

（5）、N张表关联查询需要有至少N-1个连接条件

SELECT m.name,d.dname,m.deptno FROM myemployee_liu m,dept d;
1

-- 查看在NEW YORK工作的员工
SELECT m.name,d.dname,m.deptno,d.loc FROM myemployee_liu m,dept d WHERE m.deptno=d.deptno AND d.loc='NEW YORK';
-- 查看工资高于3000的员工的名字、工资、部门以及所在地
SELECT m.name,m.salary,d.dname,d.loc FROM myemployee_liu m,dept d WHERE m.deptno=d.deptno AND m.salary>2000;
1
2
3
4

1、内链接

-- 基本语法：
SELECT tabname1.colname1, tabname2.colname2
FROM tabname1 JOIN tabname2
ON tabname1.column1=tabname2.colname2
AND tabname1.colname2=val1;
1
2
3
4
5

（1）、内链接返回所有满足连接条件的记录

（2）、如果有多个条件表达式，ON关键字后面跟一个，其余用AND条件连接

-- 查看员工SALES的所在部门及名字
SELECT m.name,d.dname,m.deptno FROM myemployee_liu m JOIN dept d ON m.deptno=d.deptno WHERE d.dname='SALES';
1
2

注：不满足连接条件的记录是不会在关联查询中被查询出来的

（3）、内连接语句的执行顺序：先根据ON和AND条件对要链接的表进行过滤，将过滤后的结果集进行内链接操作（JOIN, ON），在根据SELECT语言的定义生成最终结果集；内链接中用ON和WHERE都可以

2、外链接

（1）、外链接除了会将满足链接条件的记录查询出来之外，还会将不满足链接条件的记录也查询出来

（2）、外链接分为：

• 左外链接：以JOIN左侧表作为驱动表（所有数据都会被查询出来），那么当该表中某条记录不满足链接条件时来自右侧表中的字段全部填NULL

• 右外链接：以JOIN右侧表作为驱动表（所有数据都会被查询出来），那么当该表中某条记录不满足链接条件时来自左侧表中的字段全部填NULL

• 全链接：全部列出，不满足链接条件时字段全部填NULL

-- 查看所有员工所在部门及名字
SELECT m.name,d.dname,m.deptno FROM myemployee_liu m LEFT OUTER JOIN dept d ON m.deptno=d.deptno;
SELECT m.name,d.dname,m.deptno FROM myemployee_liu m JOIN dept d ON m.deptno(+)=d.deptno;
1
2
3

注：+号在那边，那边填NULL

（3）、外链接语句的执行顺序：先将两张表进行外链接操作（JOIN, ON），再对外链接的结果集用WHERE子句进行过滤，最后用SELECT语句生成最终结果集

3、自链接

（1）、自链接即：当前表的一条记录可以对应当前表自己的多条记录

（2）、自链接是为了解决同类型数据但是又存在上下级关系的数状结构数据时使用

-- 查看每个员工及其领导的名字
SELECT e.name,m.name FROM myemployee_liu e,myemployee_liu m WHERE e.mgr=m.id;
-- 查看SMITH员工的领导所在的城市
SELECT e.name,m.name,m.deptno,d.loc FROM myemployee_liu e,myemployee_liu m,dept d WHERE e.mgr=m.id AND e.deptno=d.deptno AND e.name='SMITH';
SELECT e.name,m.name,m.deptno,d.loc FROM myemployee_liu e JOIN myemployee_liu m ON e.mgr=m.id JOIN dept d ON e.deptno=d.deptno WHERE e.name='SMITH';
1
2
3
4
5

二、子查询

子查询是一条SELECT语句，但它是嵌套在其他SQL语句中的，为的是给该SQL提供数据以支持其执行操作

-- 查看谁的工资高于BLAKE
SELECT name,salary FROM myemployee_liu WHERE salary>(SELECT salary FROM myemployee_liu WHERE name='BLAKE');
-- 查看与BLAKE同职位的员工
SELECT name,job FROM myemployee_liu WHERE job=(SELECT job FROM myemployee_liu WHERE name='BLAKE');
1
2
3
4

1、子查询常用与SELECT语句中

（1）、根据返回结果不同，子查询可分为：

• 单行单列子查询：常用于过滤条件，可以配合>、>=、<、<=、=使用

• 多行单列子查询：常用于过滤条件，由于查询出多个值，在判断=时要使用IN，判断>、>=等操作要配合ANY，ALL

• 多列多列子查询：常当做一张表看待

-- 查询与SALESMAN职位同部门的其它职位员工
SELECT name,job,deptno FROM myemployee_liu WHERE deptno IN (SELECT deptno FROM myemployee_liu WHERE job='SALESMAN') AND job<>'SALESMAN';
-- 查看比职位CLERK和SALESMAN工资都高的员工
SELECT name,salary FROM myemployee_liu WHERE salary>(SELECT MAX(salary) FROM myemployee_liu WHERE job IN ('CLERK','SALESMAN'));
SELECT name,salary FROM myemployee_liu WHERE salary>ALL(SELECT salary FROM myemployee_liu WHERE job IN ('CLERK','SALESMAN'));
1
2
3
4
5

（2）、在子查询中需要引用到主查询的字段数据，使用EXISTS关键字

（3）、EXISTS后边的子查询至少返回一行数据，则EXISTS表达式成立并返回TRUE

-- 查看有员工的部门信息
SELECT deptno,dname FROM dept d WHERE EXISTS(SELECT * FROM myemployee_liu m WHERE m.deptno=m.deptno);
-- 查看没有员工的部门信息
SELECT deptno,dname FROM dept d WHERE NOT EXISTS(SELECT * FROM myemployee_liu m WHERE m.deptno=m.deptno);
1
2
3
4

（4）、子查询在HAVING子句中

-- 查看每个部门的最低薪水是多少？前提是该部门的最低薪水要高于30号部门的最低薪水
SELECT MIN(salary),deptno FROM myemployee_liu GROUP BY deptno HAVING MIN(salary)>(SELECT MIN(salary) FROM myemployee_liu WHERE deptno=30);
1
2

2、子查询在WHERE子句中

（1）、在SELECT查询中，在WHERE查询条件中的限制条件不是一个确定的值，而是来自另外一个查询的结果

（2）、为了给查询提供数据而首先执行的查询语句叫做子查询

（3）、子查询是嵌入在其它SQL语句中的SELECT语句，大部分时候出现在WHERE子句中

（4）、子查询嵌入的语句称作为主查询或父查询

（5）、主查询可以是SELECT语句，也可以是其它类型的语句比如DML或DDL语句

（6）、在DDL中使用子查询，可以根据子查询的结果集快速创建一张表

-- 创建表employee，表中字段为：id、name、job、salary、deptno、dname、loc数据为现有表中myemployee_liu与dept对应的数据
CREATE TABLE employee AS SELECT m.id,m.name,m.job,m.salary,m.deptno,d.dname,d.loc FROM myemployee_liu m,dept d WHERE m.deptno=d.deptno(+);
--查找薪水比整个平均薪水高的员工
SELECT id,name,salary,deptno FROM myemployee_liu WHERE salary>(SELECT AVG(salary) FROM myemployee_liu);
1
2
3
4

（7）、创建表时若子查询中的字段有别名则该表对应的字段就使用该别名作为其字段名，当子查询中一个字段含有函数或表达式，那么该字段必须给别名

CREATE TABLE employee_01 AS SELECT m.id,m.name,m.job,m.salary*12 sal,m.deptno,d.dname,d.loc FROM myemployee_liu m,dept d WHERE m.deptno=d.deptno(+);
1

（8）、DML中使用子查询

-- 将CLARK所在部门的所有员工删除
DELETE FROM employee WHERE deptno=(SELECT deptno FROM employee WHERE name='CLERK');
1
2

3、子查询在FROM部分

（1）、FROM子句用来指定要查询的表

（2）、如果要在一个子查询的结果中继续查询，则子查询出现在FROM子句中，这个子查询也称作内视图或者匿名视图

（3）、把子查询当作视图对待，但视图没有名字，只能在当前SQL语句中有效

（4）、当一个子查询是多列子查询，通常将该子查询的结果集当做一张表看待并给予它进行二次查询

-- 查看比自己所在部门平均工资高的员工
SELECT m.name,m.salary,m.deptno FROM myemployee_liu m,(SELECT AVG(salary) avg_sal,deptno FROM myemployee_liu GROUP BY deptno) t WHERE m.deptno=t.deptno AND m.salary>avg_sal;
1
2

4、子查询在SELECT 部分

把子查询放在SELECT子句部分，可以认为是外链接的另一种表现形式，将查询的结果当做外层查询记录中的一个字段值显示，使用更灵活

SELECT m.name,m.salary,(SELECT d.dname FROM dept d WHERE d.deptno=m.deptno) deptno FROM myemployee_liu m;
1

5、子查询的执行过程

（1）、先执行子查询，子查询的返回结果作为主查询的条件，再执行主查询

（2）、子查询只执行一遍

（3）、若子查询的返回结果为多个值，ORACLE会去掉重复值之后，在将结果返回给主查询

6、子查询的注意事项

（1）、若子查询的返回结果仅为一个值，可以用单值运行符，如：=

（2）、若子查询的返回结果可能为多值，必须用多值运算符，如：IN

（3）、若子查询的返回结果中包含空值（NULL），并且运算符为NOT IN，那么整个子查询不会返回任何行。NOT IN等价与<>ALL，任何值跟NULL比（包括NULL本身），结果都不为TRUE

三、关联子查询

关联子查询的语法：

SELECT column1, column2, ...
FROM table1 o
WHERE column1 operator
(SELECT column1,column2
FROM table2 i
WHERE i.expr1=o.expr2);
1
2
3
4
5
6

1、关联子查询的执行过程

关联子查询采用的是循环（loop）的方式，执行步骤如下：

（1）、外部查询得到一条记录（查询先从outer表中读取数据）并将其传入到内部查询

（2）、内部查询基于传入的值执行

（3）、内部查询从其结果中把值传回到外部查询，外部查询使用这些值来完成其处理，若符合条件，outer表中得到的那条记录就放入结果集中，否则放弃，该记录不符合条件

（4）、重复执行步骤1~3，直到把outer表中的所有记录判断一遍

SELECT deptno,dname FROM dept d WHERE EXISTS(SELECT * FROM myemployee_liu m WHERE m.deptno=m.deptno);
1

2、EXISTS的执行过程

EXISTS采用的是循环（loop）方式，判断outer表中是否存在记录只要在inner表中找到一条匹配的记录即可

（1）、外部查询得到一条记录（查询先从outer表中读取数）并将其传入到内部查询的表

（2）、对inner表中的记录依次扫描，若根据条件存在一条记录与outer表中的记录匹配，立即停止扫描，返回true，将outer表中的记录放入结果集中，若扫描了全部记录，没有任何一条记录符合匹配条件，返回false，outer表中的该记录被过滤掉，不能出现在结果集中

（3）、重复执行步骤1~2，直到吧outer表中的所有记录判断一遍

3、NOT EXISTS的执行过程

NOT EXISTS采用的是循环（loop）方式，判断在outer表中是否不存在记录，它能在inner表中找到匹配的记录

（1）、外部查询得到一条记录（查询先从outer表中读取数）并将其传入到内部查询的表

（2）、对inner表中的记录依次扫描，若根据条件存在一条记录与outer表中的记录匹配，立即停止扫描，返回false，outer表中的该记录被过滤掉，不能出现在结果集中，若扫描了全部记录，没有任何一条记录符合匹配条件，返回true，将outer表中的记录放入结果集中

（3）、重复执行步骤1~2，直到吧outer表中的所有记录判断一遍

4、IN和EXISTS

（1）、EXISTS是用循环（loop）的方式，由outer表的记录数决定循环的次数，对于exists影响最大，所以，外表的记录数要少

（2）、IN先执行子查询，子查询的返回结果去重之后，再执行主查询，所以，子查询的返回结果越少，越适合用该方式

四、分页查询

（1）、分页查询时将查询表中数据分段查询，而不是一次性将所有数据查询出来

（2）、有时查询的数据量非常庞大，这会导致系统资源消耗大，响应速度长，数据冗余严重

（3）、为止当遇到这种情况时一般使用分页查询解决，数据库基本都支持分页，但是不同数据库语法不同（方言）

（4）、ORACLE中的分页是基于伪列ROWNUM实现的

（5）、ROWNUM不存在与任何一张表中，但是所有的表都可以查询该字段，该字段的值是随着查询自动生成的，方式是：每当可以从表中查询出一条记录时，该字段的值即为该条记录的行号，从1开始，逐次递增

-- 查看表前5条记录
SELECT rownum,id,name,salary,job FROM myemployee_liu WHERE rownum<=5;
1
2

（6）、在使用ROWNUM对结果集进行编号的查询过程中不能使用ROWNUM做>1以上的数字判断，否则将查询不出任何数据（WHERE条件不满足，因为查询出一条记录则开始记录行号，从1开始，第一条数据ROWNUM等于1不会大于1）

-- 查询第6~10条数据
SELECT * FROM (SELECT ROWNUM AS ro,id,name,salary,job FROM myemployee_liu) WHERE ro BETWEEN 6 AND 10;
-- 查看公司工资排名的6~10的员工信息
SELECT * FROM (SELECT ROWNUM AS ro,t.* FROM (SELECT name,job,salary FROM myemployee_liu ORDER BY salary DESC) t) WHERE ro BETWEEN 6 AND 10;
--注：当数据量大时，可以在第二个查询中只取前10条数据
SELECT * FROM (SELECT ROWNUM ro,t.* FROM (SELECT name,job,salary FROM myemployee_liu ORDER BY salary DESC) t WHERE ROWNUM<=10) WHERE ro>=6;
1
2
3
4
5
6

--Java计算区间公式:
pageSize：每页显示的条目数
page：页数
star：(page-1)*pageSize+1
end：pageSize*page
1
2
3
4
5

---