书城计算机数据库原理及Oracle应用
18668900000011

第11章 Oracle数据库基础——SQL语言(6)

5.4.1.5 对查询结果分组

默认情况下,表中所有的行作为一个组处理。在SELECT语句中可以使用GROUP BY子句将行划分成较小的组。然后,使用聚组函数返回每一个组的汇总信息。另外,可以使用HAVING 子句限制返回的结果集。

GROUP BY子句可以将查询结果的各行按一列或多列取值相等的原则进行分组,值相等的为一组。如果未对查询结果分组,聚组函数将作用于整个查询结果,即整个查询结果只有一个函数值。否则,聚组函数将作用于每一个组,即每一组都有一个函数值。

例5-88 查询各个课程号与相应的选课人数。

SQL>SELECT cno,count(Sno)

FROM sc

GROUP BY cno;

该SELECT语句对sc表按cno的取值进行分组,所有具有相同cno值的元组为一组,然后对每一组作用聚组函数COUNT以求得该组的学生人数。

如果分组后还要求按一定条件对这些组进行选择,最终只输出满足条件的组,则可以使用HAVING子句指定选择条件。

例5-89 查询选修4门以上课程的学生的学号。

SQL>SELECT sno

FROM sc

GROUP BY sno

HAVING COUNT(*)>4;

使用GROUP BY和HAVING子句时要注意以下几个问题。

①带有GROUP BY 子句的查询语句中,在SELECT子句中指定的列要么是GROUP BY子句中指定的列,要么包含聚组函数,否则出错。

例5-90 没有包含GROUP BY子句的SELECT语句。

SQL>SELECT ename,sum(sal)FROM emp

语句执行时出错。改成下列语句则就是正确的:

SQL>SELECT ename,sum(sal)FROM emp

GROUP BY ename;

但在GROUP BY 子句中的列不一定非在SELECT子句中出现。

例5-91 按JOB分组,查询每一类JOB的最大工资。

SQL>SELECT max(sal)FROM emp

GROUP BY job

②可以使用多个列进行分组。

例5-92 查询每个部门的每种职位的雇员数。

SQL>SELECT deptno,job,count(*)

FROM emp

GROUP BY deptno,job

③查询语句的SELECT和ORDER BY、HAVING子句,是聚组函数唯一可以出现的地方。在WHERE子句中不能使用聚组函数。

例5-93 使用聚组函数的WHERE子句。

SQL>SELECT deptno,avg(sal)FROM emp

WHERE avg(sal)>2000

GROUP BY deptno;

语句执行后出现一个错误。正确的做法是在数据库中将所有行分组,然后使用HAVING子句限制这些分组行的输出如例。

例5-94 输出总工资大于8500的部门及部门的总工资。

SQL>SELECT deptno,sum(sal)

FROM emp

WHERE sal>1000

GROUP BY detpno

HAVING sum(sal)>8500

ORDER BY deptno

当在GROUP BY子句中使用HAVING子句时,查询结果中只返回满足HAVING条件的组。在一个SELECT语句中可以有WHERE子句和HAVING子句,均用于设置限定条件。HAVING子句与WHERE子句的区别如下。

WHERE子句的作用是在对查询结果进行分组前,将不符合WHERE条件的行去掉。即在分组之前过滤数据。WHERE条件中不能包含聚组函数。使用WHERE条件选择满足条件的行。

HAVING子句的作用是筛选满足条件的组,即在分组之后过滤数据。HAVING条件中经常包含聚组函数。使用HAVING条件选择满足条件的组。使用HAVING子句时必须首先使用GROUP BY进行分组。

5.4.1.6 SELECT 语句中的表达式

SQL命令中可以使用加、减、乘、除算术运算符构成表达式:在SELECT语句的各个子句中,除了FROM子句中不能使用表达式以外,其他任何子句都可以使用表达式,在一条查询语句中可多处使用表达式。

例5-95 查询奖金高于其工资的5%的雇员信息。

SQL>SELECT ename,sal,comm,comm/sal FROM emp

WHERE comm>0.05*sal

ORDER BY comm/sal DESC

表达式可以嵌套,算术运算符有自己的优先级。

5.4.1.7 START WITH和CONNECT BY子句

如果表中包含具有层次结构的数据,则可以使用该子句按照层次顺序显示查询结果。例如:emp表中的雇员编号(empno)和经理号(mgr)两列,反映出雇员之间领导与被领导的关系,这种关系就是一种树结构。

父节点的empno与子节点的MGR相同。树结构中,有且仅有一个节点无父节点中的KING,该节点为根节点。除根节点外,任何节点均有并且只能有一个父节点,有一个、多个或没有子节点。

START WITH子句确定层次查询开始的根行,根行必须满足该子句的WHERE条件。CONNECT BY子句指定层次中父行和子行之间的关系。

5.4.2 联结查询

简单查询只针对一个表进行的,实际应用时往往涉及多个表,这就需要对多个表进行联结。若一个查询同时涉及两个以上的表,则称为联结查询。联结查询包括等值联结、非等值联结、自然联结、自联结、外联结和复合条件联结等。这就是关系代数中的联结运算。

为了从多张表中查询数据,必须识别联结多张表的公共列。在WHERE子句中,列名前面由表名加以限制,以免产生二义性。

注意:一旦定义了表的别名,应该用表的别名去限制列名。别名应该尽量简短,有意义。

例5-97 从emp和dept表中查询奖金不空的雇员的部门号、部门地点、姓名、工资。

SQL>SELECT dname department,d·loc location,e·ename name,e·sal

FROM dept d,emp e

WHERE d·deptnoe·deptno

AND comm IS NOT NULL;

为了将多张联结在一起,最少的联结条件的个数是表数减1,即n 个表之间的联结至少有n-1个联结条件。在联结查询的WHERE 子句中除了联结条件以外,也可以包含其他条件,用AND运算符联结其他条件和联结条件。

5.4.2.1 等值联结

在上述联结条件中的比较运算符如果是“”号,则称为等值联结,否则称为非等值联结。

例5-98 查询每个学生及其选修课程的情况。

SELECT student.*,sc.*

FROM student,sc

WHERE student.snosc.sno;

为了避免混淆,在查询语句中用到各个表中都有的属性名时前面加上表名,没有重复的属性名不必在前面加表名。查询结果如下:

st udent.sno sname sex age dept sc.sno cno grade

98001 吴晨 男19 CS 98001 C2 87

5.4.2.2 自然联结

如果是按照两个表中的相同属性进行等值联结,且结果中去掉了重复的属性列,但保留了所有不重复的属性列,则称之为自然联结。

例5-99 用自然联结查询每个学生及其选修课程的情况。

SELECT student.sno,sname,sex,age,dept,cno,grade

FROM student,sc

WHERE student.snosc.sno;

等值联结与自然联结的差别如下。

等值联结的联结条件中属性名1与属性名2可以不同名,但必须有相同的数据类型和值域。

等值联结的结果是不去掉重复属性,而自然联结的结果中去掉重复属性。

5.4.2.3 复合联结

上面联结查询中,WHERE子句中只有一个条件,即用于联结两个表的联结条件。WHERE子句中除了联结条件外,还可以有多个限制条件。联结条件用于多个表之间的联结,限制条件限制取的记录要满足什么条件。这种联结操作,称为复合联结。

例5-100 查询选修C2号课程且成绩在90分以上的所有学生的学号和姓名。

SELECT student.sno,sname

FROM student,sc

WHERE student.snosc.sno(联结条件)

AND sc.cno′C2′AND sc.grade>90;(限制条件)

5.4.2.4 非等值联结

非等值联结是指联结条件中的运算符不是等号,可以是大于、大于等于、小于、小于等于、不等于运算符,也可以是BETWEEN AND 指定的一个范围。

假设部门和雇员数据库中的emp和SALGRADE表的结构和数据如下:

emp(empno,ename,job,mgr、sal,deptno),其数据如下:

empno ename job mgr sal d eptno

……1001 张三 工程师1002 2500 20

……1002 李四 高级工程师1003 4500 20

salgrade(grade,lowsal,highsal),其数据如下:

gra de l owsal h ighsal

1 800 2000

2 2001 3999

3 4000 5999

4 6000 9000

emp表中含有每一个雇员的编号、姓名、工作、经理编号、工资和部门号,而SALGRADE表中记录每一个等级所对应的最低工资和最高工资。

例5-101 查询工资等级为3的雇员姓名。

SELECT ename

FROM emp,salgrade

WHERE sal BETWEEN lowsal AND highsal(非等值联结条件)

AND grade3(限制条件)

例5-102 查询emp表中的每一雇员的工资的等级。

SQL>SELECT empno,ename,sal,grade

FROM emp e,grade g

WHERE e.sal BETWEEN g.lowsal AND g.hisal

ORDER BY e.sal

5.4.2.5 自联结

如果联结操作是在同一个表的不同记录之间进行,这种联结称为自联结。因为自联结查询仅涉及一张表,所以在FROM子句中该表名出现两次,分别用两个不同的别名表示。两个别名当作两张不同的表进行处理,与其他的表联结一样,也使用一个或多个相关的列联结。为了区分同一张表的不同行的列,在列名前用别名加以限制。

例5-103 查询至少选修了课程号为C2和C4的学生学号。

SELECT x.sno

FROM sc x,sc y

WHERE x.snoy.sno(联结条件)

AND x.cno′C2′(限制条件)

AND y.cno′C4′(限制条件)

例5-104 查询每一个雇员的经理姓名(查询emp表)。

SELECT manager.ename

FROM emp worker,emp manager

WHERE worker.mgrmanager.empno(联结条件)

5.4.2.6 外联结

在通常的联结操作中,只有满足联结条件的元组才能作为结果输出。外联结的查询结果是等值联结查询结果的扩展。外联结不仅返回满足联结条件的所有记录,而且也返回了一个表中的那些在另一个表中没有匹配的行记录。

如果在student表中有某些学生,但这些学生没有选课,则在sc表中没有对应的记录。如果按等值联结,则这些没有选课的学生信息不会输出。如果想以student表为主体列出每个学生的基本情况及其选课情况,若某一学生没有选课,则只输出学生的基本情况信息,其选课信息为空值。此时需要用外联结。

外联结的操作符是(+)。(+)号放在联结条件中信息不完全的那一边(即没有匹配行的那一边)。例如:执行一个外联结,联结A表和B表,假如想返回A表中的所有行,则将外联结运算符(+)放到联结条件的B表的列那一边,此时,对A表中的所有行,如果B表中没有匹配的行,则B表中的列返回NULL。否则,假如想返回B表中的所有行,则将外联结运算符(+)放到联结条件的A表的列那一边,此时,对B表中的所有行,如果A表中没有匹配的行,则A表中的列返回NULL。

例5-105 以student表为主体列出每个学生的基本情况及其选课情况,若某一学生没有选课,只输出学生的基本情况信息,其选课信息为空值。

SQL>SELECT s.sno,sname,sex,age,cno,grade

FROM student s,sc

WHERE s.snosc.sno(+)(联结条件)

外联结运算符(+)出现在联结条件右边,称为右外联结;出现在联结条件左边,称为左外联结。

外联结就像运算符(+)所在边的表增加一个“万能”的行,这个行全部由空值组成,可以和另一边的表中所有不满足联结条件的元组进行联结。