精通SQL---结构化查询语言详解

 

子查询（subquery）,其结果用来作为另一个查询的参数。

 

在多表查询中使用子查询：

 

SELECT SNO,SNAME,MARK

FROM  STUDENT

WHERE CNO=(SELECT CNO FROM COURSE WHERE CNAME='计算机入门')

 

在子查询中使用聚合函数

 

SELECT    TNO,TNAME,DNAME,AGE

FROM    TEACHER

WHERE     AGE > (SELECT AVG(AGE) FROM TEACHER)

ORDER BY AGE

如果建立了子查询，且该子查询返回的是单一值，就可以使用任何一种比较运算符比较列值。另外，不仅限于比较数字，也可以比较字符串。

子查询不仅可以在WHERE中使用，还经常使用在SELECT子句中

SELECT    TNO,TNAME,DNAME,CNO,

                    (SELCET COUNT(*)    FROM STUDENT WHERE CNO=TEACHER.CNO) AS S_NUN

FROM     TEACHER

ORDER BY S_NUN

在SELECT 子句中使用子查询时，子查询必须返回单值。

一个子查询 除了可以产生一个单一值外，也可产生一个关系，该关系 可以包含若干元组。

 

IN子查询：(NOT IN)

用于集合成员的测试,可以实现集合交和集合差运算

SELECT    SNAME,DNAME,CNO,MARK

FROM    STUDENT

WHERE    SNO IN (SELECT SNO FROM STUDENT WHERE MARK<60)

ORDER BY     SNAME

EXISTS 子查询：(NOT EXISTS)

只需要子查询返回一个TRUE或者FALSE,子查询数据内容本身并不重要。可以实现两表交集

SELECT    TNO,TNAME,DNAME,CNO

FROM    TEACHER AS T

WHERE    EXISTS (SELECT * FROM STUDENT WHERE CNO=T.CNO)

ORDER BY    TNO

 

SOME/ALL子查询：

数量词SOME,ANY和ALL则允许使用比较运算符，将单值与子查询返回的值加以比较，而这里的子查询返回的结果可以是多行的。SOME和ANY功能相同

SELECT    TNO,TNAME,DNAME,AGE

FROM    TEACHER

WHERE    AGE<ALL(SELECT AGE FROM TEACHER WHERE DNAME='计算机')

AND    DNAME<>'计算机'

ORDER BY    AGE

这个例子也可以采用聚焦函数实现

SELECT    TNO,TNAME,DNAME,AGE

FROM    TEACHER

WHERE    AGE<(SELECT MIN(AGE) FROM TEACHER WHERE DNAME='计算机')

AND DNAME<>'计算机'

ORDER BY     AGE

实际上，用聚集函数实现子查询 通常比直接用SOME或ALL查询效率要高。

 

 

UNIQUE子查询

用来测试集合是否存在重复元组。返回的也是TRUE或FALSE

SELECT CNO,CNAME,CTIME,CTEST

FROM    COURSE

WHERE    UNIQUE(SELECT SNO FROM STUDENT WHERE CNO=COURSE.CNO)

ORDER BY CNO

SQL SEVER中没有提供对UNIQUE判式的支持，可以采用聚合函数

SELECT CNO,CNAME,CTIME,CTEST

FROM COURSE

WHERE (SELECT COUNT(*) FROM STUDENT WHERE CNO=COURSE.CNO)=1

ORDER BY CNO

相关子查询：

子查询的执行要依赖于上一层查询元组的当前值。

下面是用WHERE子句实现：

SELECT CNO,CNAME,CTIME,CTEST

FROM    COURSE

WHERE    '李华'IN (SELECT SNAME FROM STUDENT WHERE CNO=COURSE.CNO)

ORDER BY     CNO

 

比较运算符引入相关子查询

SELECT SANEM,DNAME,CNO,MARK

FROM    STUDENT AS S

WHERE    (SELECT CTEST FROM COURSE WHERE CNO=S.CNO)<CAST ('2006-7-2' AS SMALLDATETIME)

ORDER BY SNAME

CAST运算符将时间字符串 "2006-7-2"转换为SMALLDATETIME型。

相同的功能：

SELECT SDAME,DNAME,C.CNO,MARK

FROM    COURSE AS C,STUDENT AS S

WHERE S.CNO=C.CNO

AND CTEST< CAST('2006-7-2' AS SMALLDATETIME)

ORDER BY C.CNO

 

HAVING子句中使用的相关子查询

SELECT T.DNAME

FROM TEACHER AS T

GROUP BY T.DNAME

HAVING COUNT(*)<(SELECT COUNT(*) FROM STUDENT WHERE CNO IN (SELECT CNO FROM TEACHER AS T2 WHERE T2.DNAME=T.DNAME))

ORDER BY T.DNAME

 

嵌套子查询：

SELECT *

FROM COURSE

WHERE CNO IN (SELECT CNO FROM TEACHER WHERE DNAME='计算机' AND NOT EXISTS(SELECT * FROM STUDENT

                            WHERE CNO=TEACHER.CNO AND MARK<60))

ORDER BY CNO

相同功能：

SELECT *

FROM COURSE

WHERE CNO IN(SELECT CNO FROM TEACHER WHERE DNAME='计算机')

AND

NOT EXISTS(SELECT * FROM STUDENT WHERE CNO =COURSE.CNO AND MARD<60)

ORDER BY CNO

 

使用子查询创建视图：

创建视图

CREATE VIEW VIEW_AVGMARK(CNO,AVG_MARK)

AS SELECT CNO,AVG(MARK)

FROM STUDENT

GROUP BY CNO

 

查询

SELECT CNO,CNAME,CTEST

FROM COURSE

WHERE CNO IN (SELECT CNO FROM VIEW_AVGMARK WHERE AVG_MARK>70)

ORDER BY CNO

 

树查询：

SQL SERVER没有。略