SQL Server数据库应用与开发：第05章 数据检索

1、SQL Server 2005 数据库应用与开发第05章 数据检索内容提要：创建数据库的目的是存储、查询和管理数据，能够实现数据查询是数据库的重要功能之一。在SQL Server 2005中，查询数据是通过SELECT语句实现的。SELECT语句能够从服务器的数据库中检索符合用户要求的数据，并以结果集的方式返回客户端。本章主要介绍SELECT语句的具体用法和简单应用。第05章 数据检索本章内容5.1 利用SELECT语句检索数据5.2 数据过滤5.3 设置结果集格式5.4 GROUP BY子句和HAVING子句5.5 COMPUTE语句和聚合函数的使用5.6 小结5.1 利用SELECT语句检

2、索数据SELECT 语句是Transact-SQL语言从数据库中获取信息的一个基本语句。该语句可以实现从一个或多个数据库中的一个或多个表中查询信息，并将结果显示为另外一个表的形式，称之为结果集（result set）。SELECT语句的基本的语法格式可归纳如下：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 5.1

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

4、989-07-07 090502 7770935222201 夏文斐女 1990-08-09 090502 86715978945645 0937221508 平靖 女 1989-12-17 090802789 12367823453 (4 行受影响)5.1 利用SELECT语句检索数据例5.2 列出所有course的职员课程号、课程名和学分。分析：如果只想简单地列出一个关系中的所有行，不加任何选择条件，那么WHERE子句可以省略。程序代码如下：SELECT courseno,cname ,creditFROM course程序执行结果如下：Courseno cnam

5、e credit- - -c05103 电子技术 4.0c05109 C语言 4.5c08123 金融学 2.5c08171 会计软件 2.0(10 行受影响)5.1 利用SELECT语句检索数据例5.3 查询表student中入学成绩在780分以上的学生的学号、姓名和电话信息。分析：本例中要求输出学号、姓名和电话信息，即为SELECT子句输出表列数据源为表student，条件为入学成绩在780分以上。程序代码如下：SELECT studentno,sname,phone FROM studentWHERE point780程序执行结果如下：studentno sname phone- - -

6、0824113307 崔岩坚0922210009 许海冰0937221508 平靖 12367823453 (6 行受影响)5.1 利用SELECT语句检索数据需要注意的问题必须按照正确的顺序指定 SELECT 语句中的子句。对数据库对象的每个引用都不得引起歧义。下列情况可能会导致多义性： （1）在系统中，可能有多个对象带有相同的名称。至少应该使用用户ID来限定表的名称。（2）在执行SELECT语句时，对象所驻留的数据库不一定总是当前数据库。若要确保总是使用正确的对象，则不论当前数据库是如何设置的，均应使用数据库和所有者来限定对象名称。（3）

7、在 FROM 子句中所指定的表和视图可能有相同的列名。外键很可能具有和相关主键相同的列名。若要解析重复名称之间的多义性，必须使用表或视图名称来限定列名。5.1 利用SELECT语句检索数据5.1.1利用SELECTFROM语句指定列 1. 使用 FROM 子句指定数据源需要注意的问题。如果查询中引用了许多表，查询性能会受到影响。但数据源在 FROM 关键字后的顺序不影响返回的结果集。列出选择列表和 WHERE 子句中所引用的列所在的表和视图，可用 AS 子句为表和视图的名称指定别名。 使用Transact-SQL还具有扩展功能，可支持在 FROM 子句中指定除表或视图之外的其他对象。 2. 指

8、定要检索列的注意事项选择列表按照指定的顺序检索并在结果集中显示列值。各个列名之间需要用逗号分隔。检索列表时尽量避免或减少“*”的使用。5.1 利用SELECT语句检索数据例5.4 查询表student中入学成绩在780分以上的学生的学号、姓名、电话和班级名称信息。在FROM子句中使用AS关键字为表指派一个临时名称。分析：班级名称与其他列分别在class和student表中，需要两个表之间建立一种外键关系。前面的数据库关系图中已经建立，这里直接使用即可。同时，还用AS关键字可以为表指派一个临时名称。程序代码如下：SELECT studentno,sname,phone ,classname FR

9、OM student as 学生,class as 班级WHERE point780 and 班级.classno=学生.classno程序执行结果如下：studentno sname phone classname- - - -0824113307 崔岩坚机械08010922210009 许海冰计算机0901 0922221324 何影计算机0901 0925111109 敬秉辰管理0901 0935222201 夏文斐计算机0902 0937221508 平靖

10、12367823453 管理0802 (6 行受影响)5.1 利用SELECT语句检索数据5.1.2 利用WHERE语句指定行 WHERE子句的主要功能是利用指定的条件选择结果集中的行。符合条件的行出现在结果集中，不符合条件的行将不出现在结果集中。利用WHERE子句指定行时，条件表达式中的char、nchar、varchar、nvarchar、text、datetime、smalldatetime类型值要放到单引号内，数值类型的值直接出现在表达式中。5.1 利用SELECT语句检索数据例5.5 在score表中显示期中、期末成绩都高于85分的学生学号、课程号和成绩。分析：设置WHERE条件实现

11、上述要求，需要采用AND逻辑运算，将两个比较运算表达式连接起来。程序代码如下：SELECT studentno,courseno,usually,finalFROM scoreWHERE usually =85 AND final = 85程序执行结果如下：studentno courseno usually final- - - -0822111208 c05109 85.00 91.000822111208 c06108 89.00 95.00.0935222201 c05109 99.00 92.000937221508 c08171 88.00 98.00(7 行受影响) 5.1 利用

12、SELECT语句检索数据例5.6 查询选修课称号为c05109或c06108且期末成绩大于等于85分学生的学号、课程号和成绩。分析：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 c0610

13、8 89.00 95.000922210009 c05109 77.00 91.000935222201 c05109 99.00 92.000937221508 c05109 79.00 91.00(5 行受影响)5.1 利用SELECT语句检索数据例5.7查询计算机学院的具有高级职称教师的教师号、姓名和从事专业。分析：WHERE子句设置的条件包括部门和职称，其中高级职称又包括教授和副教授两类，需要包括OR 和AND两种逻辑运算。程序代码如下：SELECT teacherno,tname, majorFROM teacherWHERE department=计算机学院 and (prof=副

14、教授or prof=教授)程序执行结果如下：teacherno tname major- - -t05001 韩晋升 软件工程 t05003 刘元朝 网络技术 t05011 海封 计算机设计 (3 行受影响)5.1 利用SELECT语句检索数据5.1.3 利用INTO子句生成新表利用SELECT INTO 可将几个表或视图中的数据组合成一个表。也可用于创建一个包含选自链接服务器的数据的新表。例5.8 利用SELECTINTO创建新表。在teaching数据库中创建一个新表学生成绩 st_score，包括学生学号、姓名、课程号和期末成绩。分析：学生学号、姓名、课程号和期末成绩分别在teachin

15、g数据库中的student表和score表中，访问两个表中的数据时，重复的数据列需要说明来源。程序代码如下：SELECT student.studentno, student.sname, courseno, final INTO st_scoreFROM student, scoreWHERE (student.studentno = score.studentno) 5.2 数据过滤 要限定查询返回的结果集，可以在WHERE子句中指定搜索条件来过滤数据。常用的过滤类型有比较运算、字符串运算、逻辑运算、指定范围或指定列值及未知值的运算。 下面将对SELECT语句设置不同查询条件的进一步描述。

16、5.2 数据过滤5.2.1 空值查询涉及空值的查询用NULL来表示。在列中允许存在被称为NULL的特殊数值，不同于数据库中的其他任何值。在SELECT语句中，WHERE子句通常会返回比较的计算结果为真的行。为了取得列中含有NULL的行，Transact-SQL语句包含了操作符功能IS NOT NULL。需要注意的是，一个列值是空值或者不是空值，不能表示为：“=NULL”或“NULL”，而要表示为：“IS NULL”或“IS NOT NULL”。WHERE子句有以下通用格式：COLUMN IS NOT NULL5.2 数据过滤5.2.1 空值查询例5.9 查询数据库test01中“奖学金”表中获

17、得奖学金的学生的学号、班级号、综合测评和班级名次情况。分析：学生获得奖学金，则列“奖学金”的值为一数值，即为 NOT NULL，以此为查询条件，即可查到获得奖学金的学生的情况。程序代码如下：UPDATE test01.dbo.奖学金 - 将奖学金为0的列值替换为nullSET 奖学金=nullWHERE 奖学金=0 SELECT 学号,班级编号,综合测评,班级名次FROM 奖学金WHERE 奖学金is NOT null5.2 数据过滤5.2.2利用比较运算符查询利用比较运算符可以让表中值与指定的值或表达式进行比较，也可以使用比较运算符来做条件检查。比较运算符用来比较兼容数据类型的列或变量。字符

18、串之间按排序规则规定的顺序比较大小。而日期时间类型数据的比较，日期时间越早，其值越小 5.2 数据过滤5.2.2利用比较运算符查询例5.10 在student表中1989年以后出生的学生的学号、姓名、入学成绩和Email。分析：日期时间类型的比较，时间越晚的日期时间类型数据的值越大。而描述1989年以后的日期有多种方法。这里采用函数YEAR（）的方法。程序代码如下：SELECT studentno,sname, point, EmailFROM studentWHERE year(birthday)1989-where birthday 1989-12-31 （另一种日期时间比较方法）5.2

19、数据过滤5.2.3 利用字符串运算符查询使用通配符结合的LIKE搜索条件，通过进行字符串的比较来选择符合条件的行。当使用LIKE搜索条件时，需要考虑以下事项：模式字符串中的所有字符都有意义，包括开头和结尾的空格。LIKE主要用于字符或日期时间类型数据。例5.11 在student表中显示所有姓何或姓韩的学生的姓名、生日和Email。分析：设置WHERE条件实现上述要求，需要采用OR和LIKE等逻辑运算。Like 操作符可以和通配符一起将列的值与某个特定的模式作比较，列的数据类型可以是任何字符串类型。程序代码如下：SELECT sname, birthday, EmailFROM student

20、WHERE sname LIKE 何% or sname LIKE 韩%5.2 数据过滤5.2.3 利用字符串运算符查询例5.12在student表中显示手机号开始3位不是131的学生姓名、电话和Email。分析：可用通配符并使用 NOT LIKE实现本例的要求。程序代码如下：SELECT sname,phone, EmailFROM studentWHERE phone not LIKE 131%5.2 数据过滤5.2.4 利用逻辑运算符查询选择条件中的逻辑表达式，可以将对某两个值的比较看作一个子条件，多个子条件之间可以用逻辑运算符AND、OR、NOT连接，最终构成更为复杂的选择条件，要注意

21、一些逻辑运算中存在如LIKE、IN、BETEEN、IS等运算的用法。例5.13在student表中显示所有1989年或1月份出生的学生的姓名、生日和Email。分析：LIKE可以用于日期时间类型数据的通配符表达模式，形式类似于字符型数据，但日期模式字符串前需要有%。程序代码如下：SELECT sname, birthday, EmailFROM studentWHERE birthday LIKE %1989% or birthday LIKE %01%5.2 数据过滤5.2.5 检索一定范围内的值在WHERE子句中，使用BETWEEN搜索条件时，使用BETWEEN搜索条件相当于用AND连接两

22、个比较条件，如“ x BETWEEN 10 AND 27” 相当于表达式“ x=10 AND x 和 19895.3 设置结果集格式5.3.2利用ORDOR BY子句排序利用ORDOR BY子句可以对查询的结果进行升序（ASC）或降序（DESC）排列。排序可以依照某个属性的值，若属性值相等则根据第二个属性的值，依此类推。利用ORDER BY子句进行排序，需要注意的事项和原则：默认情况下，结果集按照升序排列。ORDER BY子句包含的列并不一定出现在选择列表中。ORDER BY子句可以通过指定列名、函数值值和表达式的值进行排序。ORDER BY子句不可以使用text、ntext或image类型的

23、列。在 ORDER BY 子句可以同时指定多个排序项。5.3 设置结果集格式5.3.2利用ORDOR BY子句排序例5.18 在student表中查询学生的学号、姓名和入学成绩，并按照入学成绩的降序排列。分析：升序ASC是默认值，而降序DESC必须表明。程序代码如下：SELECT studentno,sname,point AS 入学成绩FROM studentORDER BY point desc 例5.19 在student表中查询学号大于0923000000的学生的学号、姓名、电话和Email，并按照姓名的升序排序。分析：汉字的排序一般按照汉语拼音的顺序进行。程序代码如下：SELECT

24、studentno,sname,phone,EmailFROM studentWHERE studentno0923000000ORDER BY sname 例5.20 在score表中查询总评成绩大于85的学生的课程号、和总评成绩学号，并先按照课程号的升序、再按照总评成绩的降序排列。分析：本例利用表达式作比较和排序的依据。程序代码如下：SELECT courseno,usually *0.2+ final*0.8 AS 总评,studentnoFROM scoreWHERE usually *0.2+ final*0.885ORDER BY courseno, usually *0.2+ f

25、inal*0.8 DESC5.3 设置结果集格式5.3.4 利用TOP n输出前n行在输出SELECT语句的结果集时，还可以加上TOP n 选项指定返回结果集的前n 行，或者加上TOP n PERCENT返回结果集的一部分，n 为结果集中返回的行的百分比。例5.22 从student表中查询入学成绩前5名的学生的学号、姓名、分数和电话。分析：先要按照入学成绩排序，然后再显示的是前5名。如果不排序，则会显示前5行的相关数据。程序代码如下：SELECT TOP 5 studentno,sname,point,phoneFROM studentorder by point desc例5.23 利用S

26、ELECT 语句从student表中返回入学成绩排在前35%的学生的学号、姓名、分数和电话。分析：先要按照入学成绩排序，然后再显示排在前35%的数据。程序代码如下：SELECT TOP 35 percent studentno,sname,point,phoneFROM studentorder by point desc5.4 GROUP BY子句和HAVING子句5.4.1 GROUP BY子句的使用GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的聚合值。如果聚合函数没有使用 GROUP BY子句，则只为 SELECT语句报告

27、一个聚合值。将一列或多列定义成为一组，使组内所有的行在那些列中的数值相同。出现在查询的SELECT 列表中的每一列都必须同时出现在GROUP BY子句中。例5.24 利用GROUP BY子句对score表数据分组，显示每个学生的学号和平均总评成绩。总评成绩计算公式如下：总评成绩=usually*0.3+final*0.7分析：通过学号分组，可以求出每个学生的平均总评成绩。avg（）函数用于求平均值，round（）函数用于对平均值的某位数据进行四舍五入。程序代码如下：SELECT studentno, round(avg(usually*0.3+final*0.7),2) AS平均分FROM s

28、coreGROUP BY studentno例5.25 统计student表中的男女学生的人数。分析：count（）函数用于统计记录行数。程序代码如下：SELECT sex AS 性别, count(*) AS 人数FROM student group by sex5.4 GROUP BY子句和HAVING子句5.4.2 GROUP BY子句和HAVING子句的联合使用SELECT 语句中的 WHERE和HAVING子句控制用源表中的那些行来构造结果集。WHERE和HAVING是筛选，这两个子句指定一系列搜索条件，只有那些满足搜索条件的行才用来构造结果集。HAVING 子句通常与 GROUP

29、BY 子句结合使用，尽管指定该子句时也可以不带 GROUP BY。HAVING 子句指定在应用 WHERE 子句的筛选后要进一步应用的筛选。例5.26 利用GROUP BY子句对score表数据分组，显示总评成绩高于分的每个学生的学号和平均总评成绩。分析：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门以上且各门课程

30、期末成绩均高于75分的学生的学号及其总成绩，查询结果按总成绩降序列出。分析：可以利用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) DESC5.5 COMPUTE语句 和聚合函数的使用5.5.1 COMPUTE语句的应用COMPUTE子句使用聚集函数（MIN，MAX ，

31、SUM，AVG和COUNT）来计算作为附加行出现在查询结果中的汇总值。COMPUTE 子句的结果是一个报表，所以不属于关系型模型。COMPUTE子句有一个可选的BY部分。BY定义了结果的分组格式。如果忽略了BY，则行聚集函数将作用于结果查询中的所有行。如果使用了带BY的COMPUTE子句，那么就一定要使用ORDER BY 子句。COMPUTE子句的语法格式： COMPUTE AVG | COUNT | MAX | MIN |SUM ( expression ) ,.n BY expression ,.n 5.5 COMPUTE语句 和聚合函数的使用5.5.1 COMPUTE语句的应用例5.28查看student表中入学成绩在788分以上的学生的学号、出生日期和电话，且年龄最小的学生的生日。分析：查询输出相关数据的同时，利用聚合函数MAX(birthday)实现输出结果集中年龄最小的学生的生日。程序代码如下：SELECT studentno, birthday, phoneFROM studentWHERE point788ORDER BY birthdayCOMPUTE MAX(birthday)例5.29分别查询student表中入学成绩在750分以上的男女学生的学号、性别、出生日期和电话，及年龄最小的男女学生的生