实 验 四
一、实验名称：数据库单表查询 二、实验目的
1. 掌握SELECT语句的基本语法和查询条件表示方法； 2. 掌握查询条件表达式和使用方法； 3. 掌握GROUP BY 子句的作用和使用方法； 4. 掌握HAVING子句的作用和使用方法； 5. 掌握ORDER BY子句的作用和使用方法； 6. 掌握在企业管理器中对单表的查询操作； 7. 掌握使用T-SQL语句对单表的查询操作。 三、实验环境
已安装SQL Server 2005 企业版的计算机； 具有局域网环境,有固定IP; 四、实验学时
2学时 五、实验要求
1. 了解数据库查询；
2. 了解数据库查询的实现方式； 3. 熟悉使用SELECT语法； 4. 完成实验报告。
实验内容及步骤:
数据查询是数据库的核心操作，SQL语言提供了功能强大的数据查询语句。SELECT语句是SQL语言中最重要的一条语句。使用这条语句可以方便地对数据库中的一个或多个表进行查询。本实验将介绍单表查询。 一、SELECT语句的格式
1.SELECT 主要子句的基本格式 SELECT [ ALL | DISTINCT ]
[TOP expression [PERCENT] [WITH TIES ]] < select_list >
[ INTO new_table ]
[ FROM {
[ GROUP BY [ ALL ] group_by_expression [ ,...n ] [ WITH { CUBE | ROLLUP } ] [ HAVING < search_condition > ]
[ORDER BY order_expression [ASC|DESC]]
[ COMPUTE {{AVG|COUNT|MAX|MIN|SUM} (expression)} [ ,...n ] [ BY expression [ ,...n ] ] 2.格式说明
SELECT主要子句的参数说明如下：
（1）SELECT子句用于指定所选择的要查询的特定表中的列，它可以是星号（*）、表达式、列表、变量等；
（2）INTO子句用于指定所要生成的新表的名称；
（3）FROM子句用于指定要查询的表或者视图，最多可以指定16个表或者视图，用逗号相互隔开；
（4）WHERE子句用来限定查询的范围和条件； （5）GROUP BY子句是分组查询子句，HAVING子句用于指定分组子句的条件。GROUP BY子句、HAVING子句和集合函数一起可以实现对每个组生成一行和一个汇总值；
（6）ORDER BY子句可以根据一个列或者多个列来排序查询结果，在该子句中，既可以使用列名，也可以使用相对列号。ASC表示升序排列，DESC表示降序排列；
（7）COMPUTE子句使用集合函数在查询的结果集中生成汇总行，COMPUTE BY子句用于增加各列汇总行。 二、查询分析器
打开SQL Server Management Studio后，可以在标准工具栏上单击“新建查询”按钮 进入SQL查询编辑器，此时将使用当前连接打开一个查询编辑器窗口，如图4.1所示。
图4.1 查询编辑器窗口
在查询编辑器中输入要执行的SQL语句后，单击“SQL编辑器”工具栏中的“执行”按钮 或按F5键即可运行。 三、单表查询
1.投影查询
最基本的 SELECT 语句仅有两个部分：要返回的列，和这些列源于的表。也就是说查询均为不使用WHERE子句的无条件查询，也称作投影查询。
【例1】查询全体学生的学号、姓名和年龄。 SELECT Sno, Sname, Sage
FROM Student
用? * ?表示表的全部列名，而不必逐一列出。 【例2】查询学生的全部信息。
SELECT *
FROM Student
应用DISTINCT消除查询结果以某列为依据的重复行。
【例3】查询选修了课程的学生号。 SELECT DISTINCT Sno
FROM SC
上例中，SC表中相同学号（Sno）的纪录只保留第一行，余下的具有相同学号的记录将从查询结果中清除。也就是每个同学保留一条选课纪录。
利用投影查询可控制列名的顺序，并可通过指定别名改变查询结果的列标题的名字，如下例NAME为SN的别名，改变了列的显示顺序。。
【例4】查询全体学生的姓名、学号和年龄。 SELECT Sname NAME, Sno, Sage
FROM Student
2.条件查询
当要在表中找出满足某些条件的行时，则需使用WHERE子句指定查询条件。WHERE子句中，条件通常通过三部分来描述：列名；比较运算符；列名、常数。 条件查询又可分为以下几方面内容：
? 比较大小 ? 确定范围
? 部分匹配查询 ? 空值查询 （1）比较大小
【例5】查询选修课程号为?C1?的学生的学号和成绩。 SELECT Sno,grade FROM SC
WHERE Cno=?C1?
【例6】查询成绩高于85分的学生的学号、课程号和成绩。
SELECT Sno,Cno,grade
FROM SC
WHERE grade >85
当WHERE子句需要指定一个以上的查询条件时，则需要使用逻辑运算符AND、OR和NOT将其连结成复合的逻辑表达式。其优先级由高到低为：NOT、AND、OR，用户可以使用括号改变优先级。
【例7】查询选修C1或C2且分数大于等于85分学生的的学号、课程号和成绩。
SELECT SNO，CNO，grade
FROM SC
WHERE （CNO=?C1? OR CNO=?C2?） AND grade >=85 SQL语句中也有一个特殊的 BETWEEN 运算符，用于检查某个值是否在两个值之间（包括等于两端的值）。
【例8】查询成绩在90至100之间的学生的学号、课程号及成绩。 SELECT Sno,Cno,Grade FROM SC
WHERE Grade BETWEEN 90 AND 100 （2）确定范围
在SELECT语句中可利用“IN”操作来查询属性值属于指定集合的元组。利用“NOT IN”可以查询指定集合外的元组。如下面例子：
【例9】查询选修C1或C2的学生的学号、课程号和成绩。 SELECT SNO, CNO, grade FROM SC WHERE CNO IN(?C1?, ?C2?) （3）部分匹配查询
当不知道完全精确的値时，可以使用LIKE或NOT LIKE进行部分匹配查询（也称模糊查询）。LIKE运算使我们可以使用通配符来执行基本的模式匹配。 使用LIKE运算符的一般格式为：
<属性名> LIKE <字符串常量>
字符串常量的字符可以包含如表6-2所示的通配符。
【例10】查询所有姓张的学生的学号和姓名。 SELECT Sno, Sname FROM Student WHERE Sname LIKE ?张%?
【例11】查询姓名中第二个汉字是“力”的学号和姓名。
SELECT Sno, Sname FROM Student
WHERE Sname LIKE ?_力%?
（4）空值查询
某个字段没有值称之为具有空值（NULL）。通常没有为一个列输入值时，该列的值就是空值。空值不同于零和空格，它不占任何存储空间。例如，某些学生选课后没有参加考试，有选课记录，但没有考试成绩，考试成绩为空值，这与参加考试，成绩为零分的不同。
【例12】查询没有考试成绩的学生的学号和相应的课程号。 SELECT SNO, CNO FROM SC WHERE grade IS NULL
注意：这里的空值条件为IS NULL，不能写成grade =NULL。 3.查询的排序 当需要对查询结果排序时，应该在SELECT语句中使用ORDER BY子句。ORDER BY 子句包括了一个或多个用于指定排序顺序的列名，排序方式可以指定，DESC为降序，ASC为升序，缺省时为升序。ORDER BY子句必须出现在其他子句之后。
ORDER BY 子句支持使用多列。可以使用以逗号分隔的多个列作为排序依据：查询结果将先按指定的第一列进行排序，然后再按指定的下一列进行排序。
【例13】查询选修C1 的学生学号和成绩，并按成绩降序排列。
SELECT SNO, grade FROM SC
4.分组查询 （1）聚合函数
GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的聚合值。如果聚合函数没有使用 GROUP BY 子句，则只为 SELECT 语句报告一个聚合值。
聚合函数如表4.1所示：
WHERE ORDER BY
CNO='C1' grade DESC
表4.1 聚合函数表 函数名 COUNT SUM AVG MAX MIN ABS ASCII RAND 功能 求组中项数 求和，返回表达式中所有值的和 求均值，返回表达式中所有值的平均值 求最大值，返回表达式中所有值的最大值 求最小值，返回表达式中所有值的最小值 求绝对值，返回表达式的绝对值 求ASCII码，返回字符型数据的ASCII 产生随机数，返回一个位于0和1之间的随机数
【例14】通过查询求学号为S1学生的总分和平均分。
SELECT SUM(SCORE) AS TotalScore, AVG(grade) AS AveScore
FROM SC WHERE SNO = 'S1'
注意：函数SUM和AVG只能对数值型字段进行计算。
【例15】通过查询求选修C1号课程的最高分、最低分及之间相差的分数 SELECT MAX(grade) AS MaxScore, MIN(grade) AS MinScore,
MAX(grade)- MIN(grade) AS Diff FROM SC WHERE CNO = 'C1'
【例16】通过查询求管理系学生的总数。
SELECT COUNT(SNO) FROM Student
WHERE Sdept='管理'
【例17】通过查询求学校中共有多少个系。 SELECT COUNT(DISTINCT DEPT) AS DeptNum FROM Student
注意：加入关键字DISTINCT后表示消去重复行，可计算字段Sdept不同值的数目。COUNT函数对空值不计算，但对零进行计算。
【例18】统计有成绩的学生的人数。 SELECT COUNT (grade) FROM SC
注意：上例中成绩为零的同学计算在内，没有成绩（即为空值）的不计算。 【例19】利用特殊函数COUNT(*)求计算机系学生的总数
SELECT COUNT(*)
FROM Student
WHERE Sdept=?计算机?
注意：上例中，COUNT（*）用来统计元组的个数。此函数不消除重复行，也不允许使用DISTINCT关键字。
在分组查询中，只要表达式中不包括聚合函数，就可以按该表达式分组。如下例所示。
【例20】查询每位学生的学号及其选课的门数。
SELECT CNO,COUNT(*) AS C_NUM FROM SC GROUP BY CNO GROUP BY子句按CNO的值分组，所有具有相同CNO的元组为一组，对每一组使用函数COUNT进行计算，统计出各位学生选课的门数。
（2）GROUP BY 和 WHERE 子句、HAVING 子句 可以在包含 GROUP BY 子句的查询中使用 WHERE 子句。在完成任何分组之前，将消除不符合 WHERE 子句中的条件的行。若在分组后还要按照一定的条件进行筛选，则需使用HAVING子句。
【例21】在分组查询中使用HAVING条件，查询平均成绩大于85的学生学号及平均成绩。
SELECT sno, AVG(grade) AS 'AverageScore' FROM sc GROUP BY sno HAVING AVG(grade) >85
注意：如果 HAVING 中包含多个条件，那么这些条件将通过 AND、OR 或 NOT 组合在一起
【例22】查询选课在三门以上且各门课程均及格的学生的学号及其总成绩，查询结果按总成绩降序列出。
SELECT SNO,SUM(grade) AS TotalScore FROM SC WHERE grade >=60
GROUP BY HAVING ORDER BY
SNO
COUNT(*)>=3
SUM(grade) DESC
综合实例：
【实例1】在数据库Student_test中完成以下查询：
1．查询学生总人数
Select from select from select from select 分
from where Select from
sc cno='1'
sdept, Count(*) as 人数 student
Count(*) as 学生总数 student
count(distinct sno) as 选课学生总数 sc
sum(credit) as 总credit,avg(credit) as 课程平均学分,max(credit) as 最高学分,min(credit) as 最低学分 course
avg(grade) as 平均成绩,max(grade) as 最高分, min(grade) as 最低
4. 计算1号课程的学生的平均成绩, 最高分和最低分
2. 查询选修了课程的学生总数
3. 查询所有课程的总学分数和平均学分数,以及最高学分和最低学分
5. 查询各系的学生的人数并按人数从多到少排序
group by sdept order by 人数 desc
6. 查询各系的男女生学生总数, 并按系别,升序排列, 女生排在前
select from
sdept,ssex,Count(*) as 人数 student
group by sdept, ssex order by sdept,ssex desc
7. 查询至少选修了2门课程的学生的平均成绩
select from having Select from having
sno, avg(grade) as 平均成绩 sc count(*)>=2
sno, avg(grade) as 平均成绩 sc avg(*)>=80
group by sno
8. 查询平均分超过80分的学生的学号和平均分
group by sno
【实例2】在数据库School_will中完成以下查询：
1． 查询School表中的前5条记录的Sccode、Scname、Sctype字段
Select from select from where select from where select from order by select from order by select from select from Select from select from where Select from
top 5 Sccode、Scname、Sctype School * School
Sccode=’10712’ * School Average >=500 * School Sccode * Examine
Exgrade Desc，Exname
Max（average） 最高平均分,Min(average) 最低平均分 School
AVG（Exgrade）考生平均分,Sum(Exgrade) 考分总和 Examine
Count（Scode1） 一本志愿学校总数 Ewill
Count（*） 男生总数 Examine Sex=‘男’
Exno，count（Scode1） 报读一本院校考生 Ewill
2. 查询School表中校代码为10712的学校的情况
3. 查询School表中平均分500分以上的学校的情况
4. 查询School表中所有学校情况，并按“校代码”排序
5. 查询Examine表中所有学生的情况，并先按“成绩”降序排序，再按“姓名”升序排序
6. 查询School表平均分最高和最低的分值
7. 查询Examine表中所有学生考分的平均值以及所有考生考分的总和
8. 查询Ewill表中一本志愿学校总数
9. 查询Examine表中所有男生的总数
10. 查询Ewill表中报读一本院校的考生
group by Exno
11. 对Examine表中记录按性别进行分组统计，然后输出女生组的人数
select from Group by Having
Sex，Count（Sex） 女生总数 Examine Sex Sex=’女’
实验习题：
【习题1】在学生选课库（如下3个表）中，用Transact-SQL语句实现下列简单数据查询操作。
Student表 学号 Snum S001 S002 S003 S004 S005 S006 姓名 Sname 王明 李勇 刘燕 王萍 王佳 赵婷 性别 Sex 男 男 女 女 男 女 年龄 Sage 19 23 21 23 24 20 电话 Sphone 86824571 89454321 13098765892 系编号 Dnum D2 D3 D1 D1 D3 D1
SC表 学号 Snum S001 S001 S001 S001 S001 S002 S002 S005 S004 S005 S005 课程号 Cnum C1 C2 C3 C4 C5 C3 C4 C1 C1 C1 C3 成绩 Score 83 89 65 85 69 78 75 95 85 92 76
Course 表
课程号 Cnum C1 C2 C3 C4 C5 课程名称 Cname 数据库系统原理 C程序设计 计算机体系结构 自动控制原理 数据结构 学分 Cfreq 4 4 3 2 4
1) 查询系编号为‘D2’学生的基本信息（学号、姓名、性别、年龄）。 2) 3) 4) 5)
查询学号为S006的学生的姓名。
查询成绩在60-85之间的学生的学号。
查询所有姓王，并且姓名为两个字的学生的信息。
查询选修课程号为‘C1’且成绩非空的学生学号和成绩，成绩按150
分制输出（每个成绩乘以系数1.5）。
6) 查询有选课记录的所有学生的学号，用DISTINCT限制结果中学号不重
复。
7) 查询选修课程‘C1’的学生学号和成绩，结果按成绩的升序排列，如
果成绩相同则按学号的降序排列。
【习题2】以数据库原理实验三数据库中数据为基础，请使用T-SQL 语句实现以下操作：
1) 列出所有不姓刘的所有学生；
2) 列出姓“沈”且全名为3个汉字的学生； 3) 显示在1985年以后出生的学生的基本信息；
4) 按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，其中
性别按以下规定显示：性别为男显示为男 生，性别为女显示为女 生，其他显示为“条件不明”；
5) 查询出课程名含有“数据”字串的所有课程基本信息；
6) 显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、
性别、年龄及院系；
7) 列出选修了‘1’课程的学生，按成绩的降序排列；
8) 列出同时选修“1”号课程和“2”号课程的所有学生的学号； 9) 列出课程表中全部信息，按先修课的升序排列；
10) 列出年龄超过平均值的所有学生名单，按年龄的降序显示；
11) 按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院
系，在结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；
12) 显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院系规模”，其中若该院系人数>=5则该字段值为“规模很大”，
若该院系人数大于等于4小于5则该字段值为“规模一般”， 若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”；
13) 按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课
程号及成绩；
14) 显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别
指定为“学生总人数，平均年龄”；
15) 显示选修的课程数大于3的各个学生的选修课程数；
16) 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；
17) 显示平均成绩大于“200515001”学生平均成绩的各个学生的学号、平
均成绩；
18) 显示选修各个课程的及格的人数;
19) 显示选修最多的课程数和最少的课程数；
20) 显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系
名称、男生人数、女生人数”；
21) 列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均
成绩；