Oracle PL/SQL——高级查询

emp员工表结构

SQL> desc test.emp
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER
 ENAME                                              VARCHAR2(50)
 JOB                                                VARCHAR2(50)
 MGR                                                NUMBER
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER

STU学生表结构

SQL> desc test.stu
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 SNO                                       NOT NULL VARCHAR2(10)
 SNAME                                              VARCHAR2(10)
 SGENTLE                                            VARCHAR2(2)
 SAGE                                               NUMBER(2)
 SDEPT                                              VARCHAR2(20)

1.使用GROUP BY子句实现分组

查询表emp中每个部门薪资最高的员工

SELECT MAX(SAL) 最高薪水,
DEPTNO 所在部门编号
FROM test.emp
GROUP BY DEPTNO

使用GROUP BY子句按一个或多个列名称进行分组，或者通过在表达式中使用数值数据类型，按计算出的列的结果进行分组。

2.按条件查询并分组

SELECT MAX(SAL) 最高薪水,
DEPTNO 所在部门编号
FROM test.emp
WHERE JOB='MANAGER'
GROUP BY DEPTNO

3.使用CUBE汇总数据

CUBE是GROUP BY子句中可以使用的关键字，其作用是除了返回由GROUP BY子句指定的列外，还返回按组统计的行。返回的结果先按分组的第一个条件排序显示，再按第二个条件列排序显示以次类推。

SELECT MAX(SAL) 最高薪水,
DEPTNO 所在部门编号,
EMPNO 员工编号
FROM test.emp
GROUP BY CUBE(EMPNO,DEPTNO)

在使用GROUP BY子句创建分组的实现语句中增加EMPNO员工编号字段，并对该字段首先进行排序。

CUBE运算符生成的结果集是多维数据集，其包含了各维度的所有可能组合的交叉表格，即包含维度列中各值的所有可能组合，以及与这些维度值组合相匹配的基础行中的聚合值，因此出现了一些NULL值。

注意：使用CUBE参数容易产生数据记录较多的查询结果，因此用户在使用时应注意限制查询范围。

4.使用ROLLUP汇总数据

SELECT MAX(SAL) 最高薪水,
DEPTNO 所在部门编号,
EMPNO 员工编号
FROM test.emp
GROUP BY ROLLUP(EMPNO,DEPTNO)

ROLLUP运算符生成的结果集类似于CUBE运算符所生成的结果集。与CUBE不同的是，ROLLUP运算符对GROUP BY子句中的列顺序敏感，其只返回第一个分组条件指定的列的统计行。改变列的顺序会使返回结果的行数发生变化。

或者说，CUBE和ROLLUP之间的区别在于：

• CUBE生成的结果集显示了所选列中值的所有组合的聚合。

• ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合。

5.用GROUPING函数区分NULL值

区分CUBE和ROLLUP运算符所生成的NULL值和从实际数据中返回的NULL值

SELECT MAX(SAL) 最高薪水,
DEPTNO 所在部门编号,
EMPNO 员工编号,
GROUPING(EMPNO) 区别
FROM test.emp
GROUP BY CUBE(EMPNO,DEPTNO)

使用CUBE运算符汇总数据时，为区别哪些NULL值是CUBE操作生成的，在其中使用GROUPING函数，以SNO学号字段为目标列，以别名“区别”输出。“区别”列中有0和1两个数字，其中1表示由CUBE运算符造成的NULL值，其余NULL值为事实数据的空值。

GROUPING是一个聚合函数，它产生了一个附加的列，当用CUBE或ROLLUP运算符添加行时，附加的列输出值为1；当所添加的行不是由CUBE或ROLLUP产生时，附加列值为0。

6.使用HAVING子句过滤分组

找出STU中每个班级中最大年龄在20岁以上的记录

SELECT MAX(SAGE) 最大年龄,
SDEPT 所在班级
FROM test.STU
GROUP BY SDEPT
HAVING MAX(SAGE) >= 20

HAVING和WHERE有相同的语法。HAVING和WHERE的不同之处在于：

• 在WHERE子句中，在分组进行以前，消除不满足条件的行，在HAVING子句中，在分组之后条件被应用。
• HAVING可在条件中包含聚合函数，但WHERE不能。
• WHERE子句作用于表和视图，HAVING子句作用于分组。

注意：HAVING子句允许用户为每一个分组指定条件，也即可以根据用户指定的条件来选择。如果需要使用HAVING子句的话，其应该处在GROUP BY子句之后。

7.使用ORDER BY子句排序

• 数值排序

对学生表STU中的所有数据记录按照年龄从小到大排列

SELECT * 
FROM test.stu
ORDER BY SAGE ASC

ASC：表示升序排序，是ORDER BY子句默认的排序方式。

DESC：表示降序排列

注意：ORDER BY子句默认的排序方式是升序排列，即按字段从小到大的排列，使用升序排列时ASC关键字可以省略，但降序排列关键字DESC不可省略

• 字符串排序

SELECT * 
FROM test.stu
ORDER BY SNAME

ORDER BY子句除了可以指定对数值型字段进行排序外，还可以指定对字符串数据类型的字段进行排序。

在Oracle PL/SQL中，对字符串类型的数据进行排序时，按照的是ASCII码的大小顺序。

当SELECT语句中含有GROUP BY子句时，先对查询结果进行分组，再进行排序，排序字段可以是聚合函数

• 多列排序

SELECT * 
FROM test.stu
ORDER BY SAGE,SDEPT

首先用ORDER BY之后列举的第一个字段对记录排序，如果第一个字段值（年龄）相等，则用第二个字段（班级）列举的值进行排序，以次类推。

8.对查询进行集合运算

对学生表STU和成绩表GRADE进行集合运算，找出哪些学生没有成绩，并将其学号输出。

CREATE TABLE test.GRADE(
SNO VARCHAR2(10) NOT NULL,
CNAME VARCHAR2(20) DEFAULT NULL,
SCORE NUMBER DEFAULT NULL
)

INSERT INTO test.GRADE VALUES('0001','计算机',85)

SELECT SNO FROM TEST.STU
MINUS
SELECT SNO FROM test.GRADE

其他集合运算：

• INTERSECT(交集)，返回两个查询共有的记录
• UNION ALL(并集)，返回各个查询的所有记录，包括重复记录
• UNION(并集)，返回各个查询的所有记录，不包括重复记录
• MINUS(补集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

注意：当使用集合操作的时候，查询所返回的列名可以不同，但列数以及列的数据类型必须匹配，否则无法进行运算。

9.无条件多表查询

将STU和GRADE两个表中所有记录进行组合，返回学生表的SNO、SNAME、SDEPT 3列和成绩表SNO、CNAME和SCORE的值

SELECT STU.SNO,STU.SNAME,STU.SDEPT,
          GRADE.SNO,GRADE.CNAME,GRADE.SCORE
FROM test.STU,test.GRADE

10.有共同字段的表等值连接

从STU表中找出SNO列值与GRADE表SNO列值相同的记录，实现等值连接查询。

SELECT STU.*,GRADE.*
FROM TEST.STU,test.GRADE
WHERE STU.SNO=GRADE.SNO

注意：在进行等值连接的WHERE子句中，运算符“=”两边的列至少要有一个相等的记录值，否则查询记录将为空。

11.有共同字段的表非等值

从STU表中找出SNO列值与GRADE表SNO列值不相同的记录，实现非等值查询。

SELECT STU.*,GRADE.*
FROM TEST.STU,test.GRADE
WHERE STU.SNO<>GRADE.SNO

12.两个表进行内连接

SELECT STU.*,GRADE.*
FROM TEST.STU INNER JOIN test.GRADE
ON STU.SNO = GRADE.SNO

在使用自然连接时应该注意以下几个问题：

• 自然连接子句是基于两个表存在相同名称的列。
• 返回两个表相匹配列中具有相同值的记录。
• 如果名称相同的列数据类型不同，会产生错误。

13.一个表进行自连接

在学生表STU中，找出所有年龄相同的学生的信息，并按照年龄字段降序排列

SELECT 
s1.SNO 学号,
s1.SNAME 姓名,
s1.SAGE 年龄,
s1.SDEPT 所在班级
FROM test.STU s1 INNER JOIN test.STU s2
ON s1.SAGE = s2.Sage
WHERE s1.SNO <> s2.SNO
ORDER BY s1.Sage DESC

注意：自连接中一般需要使用关键字DISTINCT消除重复记录，这是因为在返回结果中可能有多个同一年龄的学生，导致重复取值。

14.两个表进行左外连接

对学生表STU和成绩表GRADE做左外连接，找出所有学生考试的课程及成绩，没有成绩的学生返回空。

SELECT 
STU.SNO 学号,
STU.SNAME 姓名,
GRADE.CNAME 课程,
GRADE.SCORE 成绩
FROM test.STU LEFT OUTER JOIN test.GRADE
ON STU.SNO=GRADE.SNO

左外连接中的STU表的所有记录均被返回，如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列均为空值。

• 左外连接语句改写：

SELECT 
STU.SNO 学号,
STU.SNAME 姓名,
GRADE.CNAME 课程,
GRADE.SCORE 成绩
FROM test.STU , test.GRADE
WHERE STU.SNO=GRADE.SNO(+)

15.两表进行右外连接

SELECT 
STU.SNO 学号,
STU.SNAME 姓名,
GRADE.CNAME 课程,
GRADE.SCORE 成绩
FROM test.STU RIGHT OUTER JOIN test.GRADE
ON STU.SNO=GRADE.SNO

右连接表是连接操作语句中RIGHT OUTER JOIN操作符右边的连接表，其特点是显示全部右边表中的所有项目，即使其中有些项中的数据未填写完全。右外连接返回那些存在于右表而左表中却没有的行，再加上内连接的行。

• 右外连接语句改写：

SELECT 
STU.SNO 学号,
STU.SNAME 姓名,
GRADE.CNAME 课程,
GRADE.SCORE 成绩
FROM test.STU , test.GRADE
WHERE STU.SNO(+)=GRADE.SNO

16.两个表进行全外连接

对学生表STU和成绩表GRADE做全外连接

SELECT 
STU.SNO 学号,
STU.SNAME 姓名,
GRADE.CNAME 课程,
GRADE.SCORE 成绩
FROM test.STU FULL OUTER JOIN test.GRADE
ON STU.SNO=GRADE.SNO

全外连接操作产生的结果集不仅包含符合连接条件的匹配行，而且包括两个连接表中的所有记录。与左外连接和右外连接不同的是，全外连接还返回左表中不符合连接条件单符合查询条件的数据行，并且还返回右表中不符合连接条件单符合查询条件的数据行。