第05章 数据检索

1、SQL Server 2005 SQL Server 2005 数据库数据库应用与开发应用与开发20212021年年1111月月2020日日清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 第第05章章 数据检索数据检索清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 第第05章章 数据检索数据检索清华大学出版

2、社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 SELECT 语句是语句是Transact-SQL语言从数据库中获取信息的语言从数据库中获取信息的一个基本语句。该语句可以实现从一个或多个数据库中一个基本语句。该语句可以实现从一个或多个数据库中的一个或多个表中查询信息，并将结果显示为另外一个的一个或多个表中查询信息，并将结果显示为另外一个表的形式，称之为结果集（表的形式，称之为结果集（result set）。）。SELECT语句的基本的语法格式可归纳如下：语句的基

3、本的语法格式可归纳如下：SELECT select_list INTO new_table FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 例例5.1 查询表查询表student中女生的相关信

4、息。中女生的相关信息。分析：本例中要求输出女生所有列信息，则分析：本例中要求输出女生所有列信息，则SELECT输出表列可以直接采用输出表列可以直接采用“*”来表来表示。示。“*”相当于关系的所有属性。数据源相当于关系的所有属性。数据源为表为表student，条件为女生。程序代码如下：，条件为女生。程序代码如下：SELECT * FROM studentWHERE sex=女女程序执行结果如下：程序执行结果如下：studentno sname sex birthday classno point phone Email- - - - - - - -0922221324 何影何影 女女 1990-

5、10-07 090501 8790925121107 梁欣梁欣 女女 1989-07-07 090502 7770935222201 夏文斐女夏文斐女 1990-08-09 090502 86715978945645 0937221508 平靖平靖 女女 1989-12-17 090802789 12367823453 (4 行受影响行受影响)清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 例例5.

6、2 列出所有列出所有course的职员课程号、课程的职员课程号、课程名和学分。名和学分。分析：如果只想简单地列出一个关系中的所分析：如果只想简单地列出一个关系中的所有行，不加任何选择条件，那么有行，不加任何选择条件，那么WHERE子句可以省略。程序代码如下：子句可以省略。程序代码如下：SELECT courseno,cname ,creditFROM course程序执行结果如下：程序执行结果如下：Courseno cname credit- - -c05103 电子技术电子技术 4.0c05109 C语言语言 4.5c08123 金融学金融学 2.5c08171 会计软件会计软件 2.0(1

7、0 行受影响行受影响)清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 例例5.3 查询表查询表student中入学成绩在中入学成绩在780分以分以上的学生的学号、姓名和电话信息。上的学生的学号、姓名和电话信息。分析：本例中要求输出学号、姓名和电话信分析：本例中要求输出学号、姓名和电话信息，即为息，即为SELECT子句输出表列数据源为子句输出表列数据源为表表student，条件为入学成绩在，条件为入学成绩在780分以上。分以上。程序代码如下：程序代码如

8、下：SELECT studentno,sname,phone FROM studentWHERE point780程序执行结果如下：程序执行结果如下：studentno sname phone- - -0824113307 崔岩坚崔岩坚0922210009 许海冰许海冰0937221508 平靖平靖 12367823453 (6 行受影响行受影响)清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 需要

9、注意的问题需要注意的问题n必须按照正确的顺序指定必须按照正确的顺序指定 SELECT 语句中的子句。对语句中的子句。对数据库对象的每个引用都不得引起歧义。数据库对象的每个引用都不得引起歧义。n下列情况可能会导致多义性：下列情况可能会导致多义性： （1）在系统中，可能有多个对象带有相同的名称。至少应）在系统中，可能有多个对象带有相同的名称。至少应该使用用户该使用用户ID来限定表的名称。来限定表的名称。（2）在执行）在执行SELECT语句时，对象所驻留的数据库不一定语句时，对象所驻留的数据库不一定总是当前数据库。若要确保总是使用正确的对象，则不总是当前数据库。若要确保总是使用正确的对象，则不论当前

10、数据库是如何设置的，均应使用数据库和所有者论当前数据库是如何设置的，均应使用数据库和所有者来限定对象名称。来限定对象名称。（3）在）在 FROM 子句中所指定的表和视图可能有相同的列子句中所指定的表和视图可能有相同的列名。外键很可能具有和相关主键相同的列名。若要解析名。外键很可能具有和相关主键相同的列名。若要解析重复名称之间的多义性，必须使用表或视图名称来限定重复名称之间的多义性，必须使用表或视图名称来限定列名。列名。清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数

11、据库应用与开发 5.1.1利用利用SELECTFROM语句指定列语句指定列 1. 使用使用 FROM 子句指定数据源需要注意的问题。子句指定数据源需要注意的问题。n如果查询中引用了许多表，查询性能会受到影响。但数如果查询中引用了许多表，查询性能会受到影响。但数据源在据源在 FROM 关键字后的顺序不影响返回的结果集。关键字后的顺序不影响返回的结果集。n列出选择列表和列出选择列表和 WHERE 子句中所引用的列所在的表和子句中所引用的列所在的表和视图，可用视图，可用 AS 子句为表和视图的名称指定别名。子句为表和视图的名称指定别名。 n使用使用Transact-SQL还具有扩展功能，可支持在还具

12、有扩展功能，可支持在 FROM 子句中指定除表或视图之外的其他对象。子句中指定除表或视图之外的其他对象。 2. 指定要检索列的注意事项指定要检索列的注意事项n选择列表按照指定的顺序检索并在结果集中显示列值。选择列表按照指定的顺序检索并在结果集中显示列值。n各个列名之间需要用逗号分隔。各个列名之间需要用逗号分隔。n检索列表时尽量避免或减少检索列表时尽量避免或减少“*”的使用。的使用。清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 例例5.4 查询表查询

13、表student中入学成绩在中入学成绩在780分以上的学生的学号、分以上的学生的学号、姓名、电话和班级名称信息。在姓名、电话和班级名称信息。在FROM子句中使用子句中使用AS关关键字为表指派一个临时名称。键字为表指派一个临时名称。分析：班级名称与其他列分别在分析：班级名称与其他列分别在class和和student表中，需要表中，需要两个表之间建立一种外键关系。前面的数据库关系图中两个表之间建立一种外键关系。前面的数据库关系图中已经建立，这里直接使用即可。同时，还用已经建立，这里直接使用即可。同时，还用AS关键字可关键字可以为表指派一个临时名称。以为表指派一个临时名称。程序代码如下：程序代码如下

14、：SELECT studentno,sname,phone ,classname FROM student as 学生学生,class as 班级班级WHERE point780 and 班级班级.classno=学生学生.classno程序执行结果如下：程序执行结果如下：studentno sname phone classname- - - -0824113307 崔岩坚崔岩坚机械机械08010922210009 许海冰许海冰计算机计算机0901 0922221324 何影何影计算机计算机0901 0925111

15、109 敬秉辰敬秉辰管理管理0901 0935222201 夏文斐夏文斐计算机计算机0902 0937221508 平靖平靖 12367823453 管理管理0802 (6 行受影响行受影响)清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 5.1.2 利用利用WHERE语句指定行语句指定行 nWHERE子句的主要功能是利用指定的条件选择子句的主要功能是利用指定的条件选择结果集中的行。符合条件的行出现

16、在结果集中，结果集中的行。符合条件的行出现在结果集中，不符合条件的行将不出现在结果集中。不符合条件的行将不出现在结果集中。n利用利用WHERE子句指定行时，条件表达式中的子句指定行时，条件表达式中的char、nchar、varchar、nvarchar、text、datetime、smalldatetime类型值要放到单引号内，类型值要放到单引号内，数值类型的值直接出现在表达式中。数值类型的值直接出现在表达式中。清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应

17、用与开发 例例5.5 在在score表中显示期中、期末成绩都高表中显示期中、期末成绩都高于于85分的学生学号、课程号和成绩。分的学生学号、课程号和成绩。分析：设置分析：设置WHERE条件实现上述要求，需条件实现上述要求，需要采用要采用AND逻辑运算，将两个比较运算表逻辑运算，将两个比较运算表达式连接起来。达式连接起来。程序代码如下：程序代码如下：SELECT studentno,courseno,usually,finalFROM scoreWHERE usually =85 AND final = 85程序执行结果如下：程序执行结果如下：studentno courseno usually

18、final- - - -0822111208 c05109 85.00 91.000822111208 c06108 89.00 95.00.0935222201 c05109 99.00 92.000937221508 c08171 88.00 98.00(7 行受影响行受影响) 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 例例5.6 查询选修课称号为查询选修课称号为c05109或或c06108且期末成且期末成绩大于等于绩大于等于85分学生的学

19、号、课程号和成绩。分学生的学号、课程号和成绩。分析：分析：WHERE子句设置的条件包括子句设置的条件包括OR 和和AND两两种逻辑运算。种逻辑运算。程序代码如下：程序代码如下：SELECT studentno,courseno,usually,final FROM scoreWHERE(courseno=c05109 OR courseno=c06108) AND final =85 程序执行结果如下：程序执行结果如下：studentno courseno usually final- - - -0822111208 c05109 85.00 91.000822111208 c06108 89

20、.00 95.000922210009 c05109 77.00 91.000935222201 c05109 99.00 92.000937221508 c05109 79.00 91.00(5 行受影响行受影响)清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 例例5.7查询计算机学院的具有高级职称教师的教师查询计算机学院的具有高级职称教师的教师号、姓名和从事专业。号、姓名和从事专业。分析：分析：WHERE子句设置的条件包括部门和职称，子句设置的条

21、件包括部门和职称，其中高级职称又包括教授和副教授两类，需要其中高级职称又包括教授和副教授两类，需要包括包括OR 和和AND两种逻辑运算。两种逻辑运算。程序代码如下：程序代码如下：SELECT teacherno,tname, majorFROM teacherWHERE department=计算机学院计算机学院 and (prof=副教授副教授or prof=教授教授)程序执行结果如下：程序执行结果如下：teacherno tname major- - -t05001 韩晋升韩晋升 软件工程软件工程 t05003 刘元朝刘元朝 网络技术网络技术 t05011 海封海封 计算机设计计算机设计

22、(3 行受影响行受影响)清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 5.1.3 利用利用INTO子句生成新表子句生成新表利用利用SELECT INTO 可将几个表或视图中的数据可将几个表或视图中的数据组合成一个表。也可用于创建一个包含选自链组合成一个表。也可用于创建一个包含选自链接服务器的数据的新表。接服务器的数据的新表。例例5.8 利用利用SELECTINTO创建新表。在创建新表。在teaching数据库中创建一个新表学生成绩数据库中创建一个新

23、表学生成绩 st_score，包括，包括学生学号、姓名、课程号和期末成绩。学生学号、姓名、课程号和期末成绩。分析：学生学号、姓名、课程号和期末成绩分别分析：学生学号、姓名、课程号和期末成绩分别在在teaching数据库中的数据库中的student表和表和score表中，表中，访问两个表中的数据时，重复的数据列需要说访问两个表中的数据时，重复的数据列需要说明来源。明来源。程序代码如下：程序代码如下：SELECT student.studentno, student.sname, courseno, final INTO st_scoreFROM student, scoreWHERE (stud

24、ent.studentno = score.studentno) 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 n 要 限 定 查 询 返 回 的 结 果 集 ， 可 以 在要 限 定 查 询 返 回 的 结 果 集 ， 可 以 在WHERE子句中指定搜索条件来过滤数据。子句中指定搜索条件来过滤数据。常用的过滤类型有比较运算、字符串运算、常用的过滤类型有比较运算、字符串运算、逻辑运算、指定范围或指定列值及未知值逻辑运算、指定范围或指定列值及未知值的

25、运算。的运算。n 下面将对下面将对SELECT语句设置不同查询条件语句设置不同查询条件的进一步描述。的进一步描述。清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 5.2.1 空值查询空值查询涉及空值的查询用涉及空值的查询用NULL来表示。在列中允许存在被称为来表示。在列中允许存在被称为NULL的特殊数值，不同于数据库中的其他任何值。在的特殊数值，不同于数据库中的其他任何值。在SELECT语句中，语句中，WHERE子句通常会返回比较的计算子句通常会返回

26、比较的计算结果为真的行。结果为真的行。n为了取得列中含有为了取得列中含有NULL的行，的行，Transact-SQL语句包含语句包含了操作符功能了操作符功能IS NOT NULL。n需要注意的是，一个列值是空值或者不是空值，不能表需要注意的是，一个列值是空值或者不是空值，不能表示为：示为：“=NULL”或或“NULL”，而要表示为：，而要表示为：“IS NULL”或或“IS NOT NULL”。WHERE子句有以下通用格式：子句有以下通用格式：COLUMN IS NOT NULL清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. S

27、QL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 5.2.1 空值查询空值查询例例5.9 查询数据库查询数据库test01中中“奖学金奖学金”表中表中获得奖学金的学生的学号、班级号、综合获得奖学金的学生的学号、班级号、综合测评和班级名次情况。测评和班级名次情况。分析：学生获得奖学金，则列分析：学生获得奖学金，则列“奖学金奖学金”的的值为一数值，即为值为一数值，即为 NOT NULL，以此为，以此为查询条件，即可查到获得奖学金的学生的查询条件，即可查到获得奖学金的学生的情况。情况。程序代码如下：程序代码如下：UPDATE test01.dbo.奖学金奖学金 - 将奖学

28、金为将奖学金为0的的列值替换为列值替换为nullSET 奖学金奖学金=nullWHERE 奖学金奖学金=0 SELECT 学号学号,班级编号班级编号,综合测评综合测评,班级名次班级名次FROM 奖学金奖学金WHERE 奖学金奖学金is NOT null清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 5.2.2利用比较运算符查询利用比较运算符查询n利用比较运算符可以让表中值与指定的值利用比较运算符可以让表中值与指定的值或表达式进行比较，也可以使用比较运

29、算或表达式进行比较，也可以使用比较运算符来做条件检查。符来做条件检查。n比较运算符用来比较兼容数据类型的列或比较运算符用来比较兼容数据类型的列或变量。字符串之间按排序规则规定的顺序变量。字符串之间按排序规则规定的顺序比较大小。而日期时间类型数据的比较，比较大小。而日期时间类型数据的比较，日期时间越早，其值越小日期时间越早，其值越小 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 5.2.2利用比较运算符查询利用比较运算符查询例例5.10 在在stud

30、ent表中表中1989年以后出生的学年以后出生的学生的学号、姓名、入学成绩和生的学号、姓名、入学成绩和Email。分析：日期时间类型的比较，时间越晚的日分析：日期时间类型的比较，时间越晚的日期时间类型数据的值越大。而描述期时间类型数据的值越大。而描述1989年年以后的日期有多种方法。这里采用函数以后的日期有多种方法。这里采用函数YEAR（）的方法。（）的方法。程序代码如下：程序代码如下：SELECT studentno,sname, point, E-mailFROM studentWHERE year(birthday)1989-where birthday 1989-12-31 （另一种日

31、（另一种日期时间比较方法）期时间比较方法）清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 5.2.3 利用字符串运算符查询利用字符串运算符查询使用通配符结合的使用通配符结合的LIKE搜索条件，通过进行字符串的比较搜索条件，通过进行字符串的比较来选择符合条件的行。当使用来选择符合条件的行。当使用LIKE搜索条件时，需要搜索条件时，需要考虑以下事项：考虑以下事项：n模式字符串中的所有字符都有意义，包括开头和结尾的模式字符串中的所有字符都有意义，包括开头和

32、结尾的空格。空格。nLIKE主要用于字符或日期时间类型数据。主要用于字符或日期时间类型数据。例例5.11 在在student表中显示所有姓何或姓韩的学生的姓名、表中显示所有姓何或姓韩的学生的姓名、生日和生日和Email。分析：设置分析：设置WHERE条件实现上述要求，需要采用条件实现上述要求，需要采用OR和和LIKE等逻辑运算。等逻辑运算。Like 操作符可以和通配符一起将列操作符可以和通配符一起将列的值与某个特定的模式作比较，列的数据类型可以是任的值与某个特定的模式作比较，列的数据类型可以是任何字符串类型。何字符串类型。程序代码如下：程序代码如下：SELECT sname, birthday

33、, E-mailFROM studentWHERE sname LIKE 何何% or sname LIKE 韩韩%清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 5.2.3 利用字符串运算符查询利用字符串运算符查询例例5.12在在student表中显示手机号开始表中显示手机号开始3位不位不是是131的学生姓名、电话和的学生姓名、电话和Email。分析：可用通配符并使用分析：可用通配符并使用 NOT LIKE实现实现本例的要求。本例的要求。程序代码如

34、下：程序代码如下：SELECT sname,phone, EmailFROM studentWHERE phone not LIKE 131%清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 5.2.4 利用逻辑运算符查询利用逻辑运算符查询选择条件中的逻辑表达式，可以将对某两个值的选择条件中的逻辑表达式，可以将对某两个值的比较看作一个子条件，多个子条件之间可以用比较看作一个子条件，多个子条件之间可以用逻辑运算符逻辑运算符AND、OR、NOT连接，最终构

35、成更连接，最终构成更为复杂的选择条件，要注意一些逻辑运算中存为复杂的选择条件，要注意一些逻辑运算中存在如在如LIKE、IN、BETEEN、IS等运算的用法。等运算的用法。例例5.13在在student表中显示所有表中显示所有1989年或年或1月份出生月份出生的学生的姓名、生日和的学生的姓名、生日和Email。分析：分析：LIKE可以用于日期时间类型数据的通配符可以用于日期时间类型数据的通配符表达模式，形式类似于字符型数据，但日期模表达模式，形式类似于字符型数据，但日期模式字符串前需要有式字符串前需要有%。程序代码如下：程序代码如下：SELECT sname, birthday, EmailFR

36、OM studentWHERE birthday LIKE %1989% or birthday LIKE %01%清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 5.2.5 检索一定范围内的值检索一定范围内的值在在WHERE子句中，使用子句中，使用BETWEEN搜索条件时，搜索条件时，使用使用BETWEEN搜索条件相当于用搜索条件相当于用AND连接两连接两个比较条件，如个比较条件，如“ x BETWEEN 10 AND 27” 相相当于表达式当于表

37、达式“ x=10 AND x 和和 1989清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 5.3.2利用利用ORDOR BY子句排序子句排序n利用利用ORDOR BY子句可以对查询的结果进行升序（子句可以对查询的结果进行升序（ASC）或降序（或降序（DESC）排列。排序可以依照某个属性的值，）排列。排序可以依照某个属性的值，若属性值相等则根据第二个属性的值，依此类推。若属性值相等则根据第二个属性的值，依此类推。n利用利用ORDER BY子句进行排序

38、，需要注意的事项和原则：子句进行排序，需要注意的事项和原则：n默认情况下，结果集按照升序排列。默认情况下，结果集按照升序排列。nORDER BY子句包含的列并不一定出现在选择列表子句包含的列并不一定出现在选择列表中。中。nORDER BY子句可以通过指定列名、函数值值和表子句可以通过指定列名、函数值值和表达式的值进行排序。达式的值进行排序。nORDER BY子句不可以使用子句不可以使用text、ntext或或image类型类型的列。的列。n在在 ORDER BY 子句可以同时指定多个排序项。子句可以同时指定多个排序项。清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005

39、. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 5.3.2利用利用ORDOR BY子句排序子句排序例例5.18 在在student表中查询学生的学号、姓名和入表中查询学生的学号、姓名和入学成绩，并按照入学成绩的降序排列。学成绩，并按照入学成绩的降序排列。分析：升序分析：升序ASC是默认值，而降序是默认值，而降序DESC必须表明。必须表明。程序代码如下：程序代码如下：SELECT studentno,sname,point AS 入学成绩入学成绩FROM studentORDER BY point desc 例例5.19 在在

40、student表中查询学号大于表中查询学号大于0923000000的学的学生的学号、姓名、电话和生的学号、姓名、电话和Email，并按照姓名的升序，并按照姓名的升序排序。排序。分析：汉字的排序一般按照汉语拼音的顺序进行。分析：汉字的排序一般按照汉语拼音的顺序进行。程序代码如下：程序代码如下：SELECT studentno,sname,phone,EmailFROM studentWHERE studentno0923000000ORDER BY sname 例例5.20 在在score表中查询总评成绩大于表中查询总评成绩大于85的学生的的学生的课程号、和总评成绩学号，并先按照课程号的升序、课

41、程号、和总评成绩学号，并先按照课程号的升序、再按照总评成绩的降序排列。再按照总评成绩的降序排列。分析：本例利用表达式作比较和排序的依据。分析：本例利用表达式作比较和排序的依据。程序代码如下：程序代码如下：SELECT courseno,usually *0.2+ final*0.8 AS 总评总评,studentnoFROM scoreWHERE usually *0.2+ final*0.885ORDER BY courseno, usually *0.2+ final*0.8 DESC清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 20

42、05. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 5.3.4 利用利用TOP n输出前输出前n行行在输出在输出SELECT语句的结果集时，还可以加上语句的结果集时，还可以加上TOP n 选项选项指定返回结果集的前指定返回结果集的前n 行，或者加上行，或者加上TOP n PERCENT返回结果集的一部分，返回结果集的一部分，n 为结果集中返回的行的百分比。为结果集中返回的行的百分比。例例5.22 从从student表中查询入学成绩前表中查询入学成绩前5名的学生的学号、名的学生的学号、姓名、分数和电话。姓名、分数和电话。分析：先要按照入学成绩排序，然后再显示的

43、是前分析：先要按照入学成绩排序，然后再显示的是前5名。如名。如果不排序，则会显示前果不排序，则会显示前5行的相关数据。行的相关数据。程序代码如下：程序代码如下：SELECT TOP 5 studentno,sname,point,phoneFROM studentorder by point desc例例5.23 利用利用SELECT 语句从语句从student表中表中返回入学成绩排在前返回入学成绩排在前35%的学生的学号、的学生的学号、姓名、分数和电话。姓名、分数和电话。分析：先要按照入学成绩排序，然后再分析：先要按照入学成绩排序，然后再显示排在前显示排在前35%的数据。的数据。程序代码如下

44、：程序代码如下：SELECT TOP 35 percent studentno,sname,point,phoneFROM studentorder by point desc清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 5.4.1 GROUP BY子句的使用子句的使用nGROUP BY子句可以将查询结果按属性列或属子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同

45、的聚合值。列或属性列组合上具有相同的聚合值。n如果聚合函数没有使用如果聚合函数没有使用 GROUP BY子句，则只子句，则只为为 SELECT语句报告一个聚合值。语句报告一个聚合值。n将一列或多列定义成为一组，使组内所有的行将一列或多列定义成为一组，使组内所有的行在那些列中的数值相同。在那些列中的数值相同。n出现在查询的出现在查询的SELECT 列表中的每一列都必须列表中的每一列都必须同时出现在同时出现在GROUP BY子句中。子句中。例例5.24 利用利用GROUP BY子句对子句对score表数据分组，显表数据分组，显示每个学生的学号和平均总评成绩。总评成绩计算公式示每个学生的学号和平均总

46、评成绩。总评成绩计算公式如下：如下：总评成绩总评成绩=usually*0.3+final*0.7分析：通过学号分组，可以求出每个学生的平均总评分析：通过学号分组，可以求出每个学生的平均总评成绩。成绩。avg（）函数用于求平均值，（）函数用于求平均值，round（）函数用于（）函数用于对平均值的某位数据进行四舍五入。对平均值的某位数据进行四舍五入。程序代码如下：程序代码如下：SELECT studentno, round(avg(usually*0.3+final*0.7),2) AS 平均分平均分FROM scoreGROUP BY studentno例例5.25 统计统计student表中的

47、男女学生的人数。表中的男女学生的人数。分析：分析：count（）函数用于统计记录行数。（）函数用于统计记录行数。程序代码如下：程序代码如下：SELECT sex AS 性别性别, count(*) AS 人数人数FROM student group by sex清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 5.4.2 GROUP BY子句和子句和HAVING子句的联合使用子句的联合使用nSELECT 语句中的语句中的 WHERE和和HAVING子句

48、控子句控制用源表中的那些行来构造结果集。制用源表中的那些行来构造结果集。WHERE和和HAVING是筛选，这两个子句指定一系列搜索是筛选，这两个子句指定一系列搜索条件，只有那些满足搜索条件的行才用来构造条件，只有那些满足搜索条件的行才用来构造结果集。结果集。nHAVING 子句通常与子句通常与 GROUP BY 子句结合使用，子句结合使用，尽管指定该子句时也可以不带尽管指定该子句时也可以不带 GROUP BY。HAVING 子句指定在应用子句指定在应用 WHERE 子句的筛选子句的筛选后要进一步应用的筛选。后要进一步应用的筛选。例例5.26 利用利用GROUP BY子句对子句对score表数据

49、分组，显表数据分组，显示总评成绩高于分的每个学生的学号和平均总评成绩。示总评成绩高于分的每个学生的学号和平均总评成绩。分析：分析：having是对分组显示的结果进一步进行筛选。是对分组显示的结果进一步进行筛选。程序代码如下：程序代码如下：SELECT studentno, round(avg(usually*0.3+final*0.7),2) AS平均分平均分FROM scoreGROUP BY studentnohaving avg(usually*0.3+final*0.7)85例例5.27 查询选课在查询选课在3门以上且各门课程期末成绩均高于门以上且各门课程期末成绩均高于75分的分的学生

50、的学号及其总成绩，查询结果按总成绩降序列出。学生的学号及其总成绩，查询结果按总成绩降序列出。分析：可以利用分析：可以利用HAVING 子句筛选分组结果，使之满足子句筛选分组结果，使之满足COUNT(*)=3的条件即可。的条件即可。程序代码如下：程序代码如下：SELECT studentno,SUM(usually*0.3+final*0.7) AS 总分总分 FROM score WHERE final=75GROUP BY studentnoHAVING COUNT(*)=3ORDER BY SUM(usually*0.3+final*0.7) DESC清华大学出版社清华大学出版社清华大学出

51、版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 5.5.1 COMPUTE语句的应用语句的应用nCOMPUTE子句使用聚集函数（子句使用聚集函数（MIN，MAX ，SUM，AVG和和COUNT）来计算作为附加行出现）来计算作为附加行出现在查询结果中的汇总值。在查询结果中的汇总值。nCOMPUTE 子句的结果是一个报表，所以不属子句的结果是一个报表，所以不属于关系型模型。于关系型模型。nCOMPUTE子句有一个可选的子句有一个可选的BY部分。部分。BY定义定义了结果的分组格式。如果忽略了了

52、结果的分组格式。如果忽略了BY，则行聚集，则行聚集函数将作用于结果查询中的所有行。函数将作用于结果查询中的所有行。n如果使用了带如果使用了带BY的的COMPUTE子句，那么就一子句，那么就一定要使用定要使用ORDER BY 子句。子句。COMPUTE子句的语法格式： COMPUTE AVG | COUNT | MAX | MIN |SUM ( expression ) ,.n BY expression ,.n 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应

53、用与开发 5.5.1 COMPUTE语句的应用语句的应用例例5.28查看查看student表中入学成绩在表中入学成绩在788分以上的学生的学号、分以上的学生的学号、出生日期和电话，且年龄最小的学生的生日。出生日期和电话，且年龄最小的学生的生日。分析：查询输出相关数据的同时，利用聚合函数分析：查询输出相关数据的同时，利用聚合函数MAX(birthday)实现输出结果集中年龄最小的学生的生日。实现输出结果集中年龄最小的学生的生日。程序代码如下：程序代码如下：SELECT studentno, birthday, phoneFROM studentWHERE point788ORDER BY birthdayCOMPUTE MAX(birthday)例例5.29分别查询分别查询student表中入学成绩在表中入学成绩在750分以上的男女学生分以上的男女学生的学号、性别、出生日期和电话，及年龄最