第8讲SQL数据查询

第4章关系数据库标准语言SQL——SQL语言数据查询SQL语言概述SQL中的数据定义SQL中的数据查询SQL中的数据更新SQL中的视图SQL中的授权控制本章内容数据查询SELECT语句是SQL中的数据查询语句，也是SQL语言中功能最强大的数据操纵语句。SELECT语句具有数据查询、统计、分组和排序的功能，4.3SQL的数据查询一个完整的数据查询语句的格式SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]...

FROM<表名或视图名>[,<表名或视图名>]...[WHERE<元组选择条件表达式>][GROUPBY<列名1>[HAVING<组选择条件表达式>]][ORDERBY<列名2>[ASC|DESC]];4.3SQL的数据查询若要实现关系代数中最常见的查询

∏A1,A2,…,AmσF（R1R2

…Rn

）可用最常用、最基本的SQL查询语句来实现：

SELECTA1，A2，…，Am

FROMR1，R2，…，Rn

WHEREF；4.3SQL的数据查询4.3SQL的数据查询【例】“学生选课”数据库中的关系模式（表结构）：

学生（学号，姓名，所在系，出生时间，性别）

课程（课程编号，课程名，先修课程号）选课（学号，课程编号，成绩）

S（SNO，SN，SD，SB，SEX）

C（CNO，CN，PC）SC（SNO，CNO，GRADE）4.3SQL的数据查询SQL的单表查询查询结果的显示查询满足条件的元组查询结果的排序查询结果的计算和统计查询结果的分组SQL的连接、嵌套和集合查询4.3SQL的数据查询查询结果的显示【例】查询学生表S、课程表C、选课表SC

。

SELECT*FROMS；SELECT*FROMC；

SELECT*FROMSC；显示输出所有属性列的信息，且与基表中相同4.3SQL的数据查询查询结果的显示【例】查询所有女生的姓名和出生时间。

SELECTSN，SBFROMSWHERESEX=‘女’；【例】查询选修了课程的学生的学号。

SELECTDISTINCTSNOFROMSC；【例】查询学生学号和学生的年龄。

SELECTSNO，SA=datediff(Year，SB，Getdate())+1

FROMS；去掉结果表中的重复行4.3SQL的数据查询查询满足条件的元组运算符的优先顺序（从高到低）：+，-，*，/，%（取余）=，!=，>=，>，<=，<，[NOT]BETWEEN…AND[NOT]IN

[NOT]LIKEIS[NOT]NULLNOT、AND、OR

4.3SQL的数据查询查询满足条件的元组BETWEEN谓词用于判断某个值是否属于一个指定的区间。一般形式为：

E[NOT]BETWEENE1ANDE2其语义为：

[NOT]（E>=E1ANDE<=E2）E、E1和E2都是表达式，且E1<E2。【例】查询出生时间在1990-01-01到1991-12-30之间的学生的姓名和出生时间。SELECTSN，SBFROMSWHERESBBETWEEN‘1990-01-01’AND‘1991-12-31’；

4.3SQL的数据查询查询满足条件的元组LIKE谓词用于字符串的比较。一般形式为：

列名[NOT]LIKE字符串表达式“字符串表达式”中提供了以下匹配方式：字符“_”：代表任意一个单字符；字符“%”：代表长度可为零的任意长字符串；所有其他字符只代表自己；若查询的字符串本身就含有通配符“%”或“_”，需对通配符进行转义。

【例】查询姓“王”的所有学生的学号和姓名。

SELECTSNO，SNFROMSWHERESNLIKE‘王％’;4.3SQL的数据查询查询满足条件的元组LIKE谓词用于字符串的比较。一般形式为：

列名[NOT]LIKE字符串表达式“字符串表达式”中提供了以下匹配方式：字符“_”：代表任意一个单字符；字符“%”：代表长度可为零的任意长字符串；所有其他字符只代表自己；若查询的字符串本身就含有通配符“%”或“_”，需对通配符进行转义。【例】查询课程名后缀为“_Design”课程设计的课程信息。SELECT*FROMCWHERECNLIKE‘%\_Design’ESCAPE‘\’；

4.3SQL的数据查询查询满足条件的元组IN谓词适用于判断一个值是否属于一个集合。一般格式为：

E[NOT]IN（V1，V2，…，Vn）其语义为：

[NOT]（E=V1ORE=V2OR…ORE=Vn）IN后面也可以是一个子查询块——嵌套查询【例】查询非数学系和非计算机系的学生的学号、姓名和所在系；

SELECTSNO，SN，SDFROMSWHERESDNOTIN（‘数学’，‘计算机’）；4.3SQL的数据查询查询满足条件的元组

NULL谓词用于判断值是否为空。由于NULL不是一个具体的值，涉及NULL的条件不得使用=、等比较符，而只能用

ISNULL

或ISNOTNULL【例】查询所有缺少选课成绩的学生的学号和相应的课程号。

SELECTSNO，CNOFROMSCWHEREGRADEISNULL；4.3SQL的数据查询查询结果的排序

ORDERBY子句指定按照一个或多个属性列的升序（ASC缺省值）或降序（DESC）重新排列查询结果。一般格式：

ORDERBY列1[ASCDESC][，列2[ASCDESC]，…]【例】查询选修课程号为“C02”的学号和成绩，并按成绩降序排列。

SELECTSNO，GRADEFROMSCWHERECNO=‘C02’ORDERBYGRADEDESC；4.3SQL的数据查询查询结果的计算和统计

COUNT(*)统计元组个数COUNT([DISTINCT|ALL]<列名>)统计一列中值的个数SUM([DISTINCT|ALL]<列名>)计算一列值的总和（此列必须是数值型）AVG([DISTINCT|ALL]<列名>)计算一列值的平均值（此列必须是数值型）MAX([DISTINCT|ALL]<列名>)求一列值中的最大值MIN([DISTINCT|ALL]<列名>)求一列值中的最小值

4.3SQL的数据查询查询结果的计算和统计

【例】求学生表S中学生的总人数。

SELECTCOUNT（*）

FROMS；

【例】查询选修了课程的学生人数；

SELECTCOUNT（DISTINCTSNO）

FROMSC；【思考题】计算选修C01课程的学生平均成绩；

查询学习C01课程的学生最高分数。4.3SQL的数据查询查询结果的分组

通过GROUPBY子句可将查询结果按某一列或多列的值分组，值相等的为一组。一般格式为：

GROUPBY列名1[,列名2][,…列名n][HAVING条件]HEVING子句用于对分组进行筛选，只有满足条件的组才会选出来。对查询结果分组的目的是为了细化聚集函数的作用对象。分组后聚集函数将作用于查询结果中的每一个分组，即每一个分组都有一个函数值。SELECT子句所要显示输出的值必须在分组中是唯一的，即只能是GROUPBY子句所指明的列或聚集函数。4.3SQL的数据查询查询结果的分组

【例】查询平均成绩在80分以上的学生学号和平均成绩

SELECTSNO，AVG(GRADE)FROMSCGROUPBYSNOHAVINGAVG(GRADE)>80SNOCNOGRADEs01s01s01s01s01c01c02c03c04c0780.098.085.078.089.0SNOCNOGRADEs02s02c02c1180.080.04.3SQL的数据查询查询结果的分组

【例】查询选修的课超过三门的学生的学号。

SELECTSNO

FROMSCGROUPBYSNOHAVINGCOUNT（*）＞3；4.3SQL的数据查询查询结果的分组

【例】查找男生人数超过20的系的名称

SELECTSD

FROMSWHERESEX=‘男’

GROUPBYSD

HAVINGCOUNT（*）＞=20WHERE作用于由FROM指定的数据对象（基本表或视图），从中选择满足条件的元组；GROUPBY子句用于对WHERE的结果进行分组；HAVING则是对GROUPBY以后的分组按条件进行选择。4.3SQL的数据查询SQL的连接、嵌套和集合查询连接查询二表连接查询多表连接查询自身连接查询连接查询中的连接条件通过WHERE子句表达，其中连接条件和元组选择条件之间用AND（与）操作符衔接。若无WHERE子句，则查询为对多表的广义笛卡尔积进行操作。4.3SQL的数据查询二表连接查询【例】查询选修课程号为“C01”的学生姓名和成绩

SELECTS.SN，SC.GRADEFROMS，SCWHERESC.CNO=‘C01’AND

S.SNO=SC.SNO；4.3SQL的数据查询二表连接查询【例】查询学生姓名及选修课程号为“C01”的成绩

SELECTS.SN，SC.GRADEFROMSLEFTOUTERJOINSC

ONSC.CNO=‘C01’ANDS.SNO=SC.SNO；4.3SQL的数据查询二表连接查询

在SQL语句中，外连接可以在FROM子句中指定，其语法规则如下：FROM<左关系>LEFT|RIGHT|FULL[OUTER]JOIN<右关系>ON<search_condition>

说明：FULL[OUTER]进行全外连接，LEFT[OUTER]进行左外连接RIGHT[OUTER]进行右外连接ON<search_condition>指定连接所基于的条件

4.3SQL的数据查询多表连接查询【例】查询选修“数据结构”课程的学生的姓名和成绩。

SELECTS.SN，SC.GRADEFROMS，C，SCWHEREC.CN=‘数据结构’AND

C.CNO=SC.CNOAND

S.SNO=SC.SNO；4.3SQL的数据查询自身连接查询【例】查询每门课程的间接先修课程（即先修课程的先修课程）。SELECTFIRST.CNO，SECOND.PC

FROMCFIRST，CSECONDWHEREFIRST.PCISNOTNULLANDSECOND.PCISNOTNULLANDFIRST.PC=SECOND.CNO；4.3SQL的数据查询SQL的连接、嵌套和集合查询嵌套查询一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询或子查询。在子查询的WHERE子句中还可以嵌入子查询，只嵌入一层子查询的称为单层嵌套查询，嵌入子查询多于一层的称为多层嵌套查询。4.3SQL的数据查询SQL的连接、嵌套和集合查询嵌套查询嵌套语句的执行由下层向上层处理，即下层查询结果集用于建立上层查询块的查找条件。4.3SQL的数据查询SQL的连接、嵌套和集合查询嵌套查询使用IN操作符实现使用比较操作符实现使用ANY或ALL操作符实现使用EXISTS操作符实现

4.3SQL的数据查询使用IN操作符实现【例】查询选修“操作系统”课程的学生的学号SELECTSNOFROMSCWHERECNOIN

（SELECTCNOFROMCWHERECN=‘操作系统’）；4.3SQL的数据查询使用IN操作符实现【例】查询选修“操作系统”课程的学生的学号和姓名SELECTSNO，SNFROMSWHERESNOIN

（SELECTSNOFROMSCWHERECNOIN

（SELECTCNOFROMCWHERECN=‘操作系统’））；独立子查询：子查询的结果只是用于建立其父查询的查询条件，不依赖于父查询。4.3SQL的数据查询使用比较操作符实现

【例】查询“c01”课程的选修成绩高于王玲的学生的学号和成绩SELECTSNO，GRADEFROMSCWHERECNO=‘c01’ANDGRADE

＞

（SELECTGRADEFROMSCWHERECNO=‘c01’ANDSNO=（SELECTSNOFROMSWHERESN=‘王玲’））；4.3SQL的数据查询使用比较操作符实现

【例】查询每个学生所修课程成绩超过其选修课程平均成绩的课程号SELECTSNO，CNOFROMSCSC1WHEREGRADE＞（SELECTAVG(GRADE)FROMSCSC2WHERESC2.SNO=SC1.SNO）；相关子查询：不能独立得到查询结果，子查询的结果与父查询有关，可能需要针对父查询进行反复查询。

4.3SQL的数据查询使用ANY或ALL操作符实现在有的DBMS（如SQLServer2005）中，使用ANY或ALL操作符与比较符配合使用实现嵌套查询。ANY操作符的语义为查询结果中的某个值

ALL操作符的语义为查询结果中的所有值。4.3SQL的数据查询使用ANY或ALL操作符实现【例】查询其他系中比计算机系某一学生年龄小的学生（即查询年龄小于计算机系中年龄最大者的学生）。SELECT*FROMSWHERESD<>‘计算机’ANDSB>ANY（SELECTSB

FROMSWHERESD=‘计算机’）；4.3SQL的数据查询使用ANY或ALL操作符实现【例】查询其他系中比计算机系学生年龄都小的学生。

SELECT*FROMSWHERESD<>‘计算机’ANDSB>ALL（SELECTSB

FROMSWHERESD=‘计算机’）；4.3SQL的数据查询使用ANY或ALL操作符实现此类查询也可以用聚集函数来实现。且效率更高。=ANY等价于IN谓词<ANY等价于<MAX<>ALL等价于NOTIN谓词<ALL等价于<MIN4.3SQL的数据查询使用EXISTS操作符实现EXISTS谓词用于判断一个子查询块的结果是否存在。一般格式为：

[NOT]EXISTS（子查询块）其语义为：若子查询块的查询结果非空，则EXISTS为真，否则为假。4.3SQL的数据查询使用EXISTS操作符实现【例】查询选修了课程号为“c02”的学生姓名。SELECTSNFROMSWHEREEXISTS

（SELECT*FROMSCWHERES.SNO=SC.SNOANDCNO=‘c02’）；请思考：该例使用连接查询、嵌套查询如何表示？子查询只返回真值或假值，给出列名无实际意义。4.3SQL的数据查询使用EXISTS操作符实现

【例】查询没有选修‘c02’课程的学生姓名

SELECTSNFROMSWHERENOTEXISTS

（SELECT*FROMSCWHERES.SNO=SC.SNOANDCNO=‘c02’）；请思考：该例可否使用连接查询、IN嵌套查询表示？学号姓名性别年龄s01王小艳女18s02李明男20s03司马南男18s04李昕女19s05成功男21学号课程号成绩s01c0198s01c0478s02c0288s01c0298s03c0389SSC4.3SQL的数据查询SELECTSNFROMSWHERENOTEXISTS

（SELECT*FROMSCWHERES.SNO=SC.SNOANDCNO=‘c02’）；学号姓名性别年龄s01王小艳女18s02李明男20s03司马南男18s04李昕女19s05成功男21学号课程号成绩s01c0198s01c0478s02c0288s01c0298s03c0389SSC4.3SQL的数据查询SELECTSNFROMSWHERENOTEXISTS

（SELECT*FROMSCWHERES.SNO=SC.SNOANDCNO=‘c02’）；学号姓名性别年龄s01王小艳女18s02李明男20s03司马南男18s04李昕女19s05成功男21学号课程号成绩s01c0198s01c0478s02c0288s01c0298s03c0389SSC4.3SQL的数据查询SELECTSNFROMSWHERENOTEXISTS

（SELECT*FROMSCWHERES.SNO=SC.SNOANDCNO=‘c02’）；学号姓名性别年龄s01王小艳女18s02李明男20s03司马南男18s04李昕女19s05成功男21学号课程号成绩s01c0198s01c0478s02c0288s01c0298s03c0389SSC4.3SQL的数据查询SELECTSNFROMS，SCWHERES.SNO=SC.SNOANDCNO<>‘c02’；

4.3SQL的数据查询使用EXISTS操作符实现

结论：

一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换，但所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。4.3SQL的数据查询使用EXISTS操作符实现【例】查询选修全部课程的学生的姓名{t(1)(u)(v)(w)(S(u)∧C(v)∧SC(w)∧u[1]=w[1]∧v[1]=w[2]∧t[1]=u[2])}

（

x）P≡┐(x）(┐P)查询选修全部课程的学生的姓名≡没有一门课他不选

4.3SQL的数据查询使用EXISTS操作符实现【例】查询选修全部课程的学生的姓名SELECTSNFROMSWHERENOTEXISTS(SELECT*FROMCWHERENOTEXISTS

(SELECT*FROMSCWHERESC.SNO=S.SNOANDSC.CNO=C.CNO));4.3SQL的数据查询使用EXISTS操作符实现【例】查询所学课程包含学生“s03”所学课程的学生的姓名

本查询可以用逻辑蕴涵来表达：查询学号为X的学生，对所有的课程Y，只要S03学生选修课程Y，则X也选修了Y。用p表示谓词“s03学生选修课程Y”

用q表示谓词“学生X选修了课程Y”

则查询为：（y）p→q{t(1)(u)(S(u)∧(v)(SC(v)∧(v[1]=‘s03’→(w)(SC(w)∧w[1]=u[1]∧w[2]=v[2])))∧t[1]=u[1])}4.3SQL的数据查询使用EXISTS操作符实现【例】查询所学课程包含学生“s03”所学课程的学生的姓名

p→q≡┐p∨q（y）p→q≡┐(y）(┐(p→q)≡┐(y）(┐(┐p∨q)≡┐(y）(p∧┐q)不存在这样的课程y，学生s03选修了y，而学生x没有选。

4.3SQL的数据查询使用EXISTS操作符实现【例】查询所学课程包含学生“s03”所学课程的学生的姓名

SELECTSNFROMSWHERENOTEXISTS(SELECT*