子查询
当一个查询是另一个查询的条件时,称之为子查询。子查询可以使用几个简单命令构造功能强大的复合命令。子查询最常用于SQL命令的WHERE子句中,我们按照子查询返回单个值还是一组值(此时子查询前应接关键字IN、ANY或ALL等)、查询一个表还是多个表进行分类,分别举例说明子查询的形式。
【例5.20】查询和学号为105的学生同年出生的所有学生的no、name和birthday列。
SELECT sno,sname,sbirthday
FROM student
WHERE year(sbirthday)=
(SELECT year(sbirthday)
FROM student
WHERE sno='105')
本例执行结果如下:
sno sname sbirthday
----- -------- ------------------------
105 匡明 1975-10-02 00:00:00.000
109 王芳 1975-02-10 00:00:00.000
本例的执行过程是:先执行以下子查询:
SELECT year(sbirthday) FROM student WHERE sno='105'
其返回结果为1975,再执行主查询:
SELECT sno,sname,sbirthday
FROM student
WHERE year(sbirthday)=1975
这样得到本例的结果。
我们知道,一个查询可以涉及多个表的联合数据,一个子查询也可以使用表的连接方法从多个表中提取数据。但在这里,该子查询尽管从多个表提取数据,但也只能有一个返回值。
【例5.21】查询“张旭”教师任课的学生成绩,并按成绩递增排列。
SELECT cno,sno,degree
FROM score
WHERE cno=
( SELECT x.cno
FROM course x,teacher y
WHERE x.tno=y.tno and y.tname='张旭')
ORDER BY degree DESC
本例执行结果如下:
cno sno degree
---------- ----- ----------
6-166 101 85
6-166 108 81
6-166 107 79
上面的查询,返回值不论来自一个表还是多个表,都只返回单个值,即返回表中的某一行,如果返回多值,则要使用本节介绍的ANY、ALL、IN和NOT IN等,它们与查询条件一起构造返回一组值的子查询。
【例5.22】查询选修某课程的学生人数多于5人的教师姓名。
SELECT tname
FROM teacher
WHERE tno IN
(SELECT x.tno
FROM course x,score y
WHERE x.cno=y.cno
GROUP BY x.tno
HAVING COUNT(x.tno)>5)
本例执行结果如下:
tname
--------
王萍
【例5.23】查询存在有85分以上成绩的课程cno。
SELECT DISTINCT cno
FROM score
WHERE degree IN
(SELECT degree
FROM score
WHERE degree>85)
本例执行结果如下:
cno
----------
3-105
3-245
【例5.24】查询“计算机系”中与“电子工程系”不同职称的教师name和prof。
SELECT tname,prof
FROM teacher
WHERE depart='计算机系' and prof NOT IN
(SELECT prof
FROM teacher
WHERE depart='电子工程系')
本例执行结果如下:
tname prof
-------- ----------
李诚 副教授
【例5.25】查询选修编号为“3-105”的课程,且成绩至少高于编号为“3-245”的学生的cno、no和degree,并按degree从高到低次序排列。
SELECT cno,sno,degree
FROM score
WHERE cno='3-105' and degree> ANY
(SELECT degree
FROM score
WHERE cno='3-245')
ORDER BY degree DESC
本例执行结果如下:
cno sno degree
---------- ----- --------------------
3-105 103 92
3-105 107 91
3-105 105 88
3-105 108 78
3-105 109 76
【例5.26】查询选修编号为“3-105”的课程,且成绩高于所有编号为“3-245”成绩的学生的cno、no和degree,并按degree从高到低次序排列。
SELECT cno, sno,degree
FROM score
WHERE cno='3-105' and degree>ALL
(SELECT degree
FROM score
WHERE cno='3-245')
ORDER BY degree DESC
本例执行结果如下:
cno sno degree
---------- ----- ----------
3-105 103 92
3-105 107 91
3-105 105 88
SQL命令还提供了UNION子句,它可以将多个SQL命令连接起来生成单个SQL无法做到的结果集合。VFP将第二次查询的结果附加到第一个结果上。
注意:在VFP中使用UNION子句遵守下列规则:
· 使用UNION连接的SELECT必须有相同的输出表达式格式,即对应栏目具有相同的数据类型的宽度。
· 仅最后一个SELECT可以带ORDER BY及INTO子句,如果带ORDER BY,它影响整个显示结果。
· UNION不能连接嵌套的SELECT语句。
【例5.27】查询所有教师和学生的name、sex和birthday。
SELECT tname AS '姓名',tsex AS '性别',tbirthday AS '出生日期'
FROM teacher
UNION
SELECT sname,ssex,sbirthday
FROM student
本例执行结果如下:
姓名 性别 出生日期
-------- ---- ---------------------------
匡明 男 1975-10-02 00:00:00.000
李诚 男 1958-12-02 00:00:00.000
李军 男 1976-02-20 00:00:00.000
刘冰 女 1977-08-14 00:00:00.000
陆君 男 1974-06-03 00:00:00.000
王芳 女 1975-02-10 00:00:00.000
王丽 女 1976-01-23 00:00:00.000
王萍 女 1972-05-05 00:00:00.000
曾华 男 1976-09-01 00:00:00.000
张旭 男 1969-03-12 00:00:00.000
本例的结果将两个SELECT命令的结果已按照姓名进行了排序。
【例5.28】查询所有“女”教师和“女”学生的name、sex和birthday。
SELECT tname AS '姓名',tsex AS '性别',tbirthday AS '出生日期'
FROM teacher
WHERE tsex='女'
UNION
SELECT sname,ssex,sbirthday
FROM student
WHERE ssex='女'
本例执行结果如下:
姓名 性别 出生日期
-------- ---- -------------------------------------
刘冰 女 1977-08-14 00:00:00.000
王芳 女 1975-02-10 00:00:00.000
王丽 女 1976-01-23 00:00:00.000
王萍 女 1972-05-05 00:00:00.000
本例的结果已将两个 SELECT 命令的结果按姓名进行了排序。