数据库上机报告
13060110011 巩志鑫 5.1 实验目的
（1）熟练掌握SELECT实现单表查询
（2）熟练掌握SELECT实现多表查询（含自身连接，外连接，CROSS连接） （3）熟练掌握SELECT实现明细查询（COMPUTE BY 的使用） （4）掌握CREATE VIEW创建用户视图的方法；
（5）掌握一些查询结果的特定显示输出的技巧和并，交，差查询的实现。
5.2 实验环境
Windows Server 2003+SQL Server 2008.
5.3 实验原理及相关知识
数据表的查询可以使用SQL Server Management Studio 和 T-SQL 语句两种方法实现。 5.3.1 使用SQL Server Management Studio 进行查询
1.查询数据表的元组数据
在对象资源管理器中，选择需要查询的数据表，用鼠标右键单击该表，从弹出的快捷菜单中选择“选择前…….行”命令，这时将显示该表的元组数据。
2.查询SQL语句
在对象资源管理器中，选择需要查询数据的表，用鼠标右键单击该表，从弹出的快捷菜单中选择“编写表脚本为”->SELECT->新查询编辑器窗口；在窗格中会自动显示对应的SELECT语句。
5.3.2 使用T-SQL查询
使用T-SQL查询，就是使用SELECT语句。SELECT 的语句格式如下。
SELECT [ALL[DISTINCT][TOP n[PERCENT]]
[info]
FROM [,„„ n]
[WHERE ]
[GROUP BY [ALL] group_by_expreion[,„„n][ HAVING]]
[ORDER BY [ASC|DESC]„„]] 其中，
::={*|{table_name|view_name|table_alais}*|{column_name}
[[AS] column_alias]][,„„n] ::=table_name[[AS]table_alias]{WITH([,„„n]) } |view_name[[AS]table_alais ][ WITH( [,„„n]) ] |rowest_function [[AS] table_alais] |user_defined_function[[AS]]table_alais] |derived_table[AS]table_alais[ ( column_alais[,„„n])] | ::ON |CROSS JOIN ::= [INNER |{{ LEFT|RIGHT|FULL}[OUTER]}]JOIN
参数说明如下：
（1） ＳＥＬＥＣＴ Ａｌｌ：默认设置，指定在结果集中可以显示重复行。 （2） ＤＩＳＴＩＮＣＴ：指定在结果集中只能显示唯一行，空值被认为相等。
（3） ＴＯＰ ｎ：从查询结果集中指定输出前ｎ行，ｎ是０～４２９４９６７２９５的整数。
（4） ＰＥＲＣＥＮＴ：如果未指定ＰＥＲＣＥＮＴ，ｎ就是返回的行数，如果指定了ＰＥＲＣＥＮＴ，ｎ就是返回的结果集行的百分比，ｎ值必须为０～１００的数。
（5） ＜ｓｅｌｅｃｔ－ｌｉｓｔ＞：为结果选择的列，选择列表以逗号分隔。 （6） INTO:创建新表并将查询结果集插入新表。用户若要执行带INTO的SELECT语句，就必须在目的数据库内具有ＣＲＥＡＴＥ TABLE权限。New-table中的列按照选择列表指定的顺序创建，每列与选择列表中相应表达式具有相同的名称，数据类型和值。
（7） ＦＲＯＭ＜ｔａｂｌｅ－ｓｏｕｒｃｅ＞：指定要在Ｔｒａｎｓａｃｔ＿ＳＱＬ语句中使用的表或者视图，可在语句中使用多达２５６个表。
（8） ＜ｓｅａｒｃｈ－ｃｏｎｄｉｔｉｏｎ＞：通过使用谓词限制结果集内的返回的行，对搜索条件中可以包含的谓词数量没有限制。
（9） ＊：指定在ＦＲＯＭ字句内返回所有表和视图内的所有列，列按照ＦＲＯＭ子句中所指定的表或者视图返回，并按在表或视图中创建的顺序返回。
（10） ｃｏｌｕｍｎ－ｌｉａｓ：查询结果集内替换列名的可选别名。 （11）column_alias：查询结果集内替换列名的可选别名。
（12）GROUP BY ALL：包含所有组合结果集，甚至包含哪些任何行都不满足WHERE子句制定的搜索条件的组合结果集。如果制定了ALL，组中不满足搜索条件的汇总列将返回空值。 （13）group_by_expreion:执行分组表达式，也称分组列。注意，text、ntext和image类型的类型的列不能用于group_by_expreion。
（14）HAVING:指定组或聚合应满足的搜索条件。HAVING通常与GROUP BY子句一起使用。
（15）中的参数说明如下。
Table_name：表名。FROM关键字之后的表和视图的顺序并不影响返回的结果集。
[AS]table_alias:table_name、view_name或rowest_fuction的别名，为方便起见而使用，或用于自连接或子查询中的表或视图。别名通常是一个缩短了的表名，用于在连接中引用表中的特定列。如果连接的多个表中有相同名称的列存在，SQL Server要求必须使用表名或别名来限定列名（如果定义了别名则不能使用表名）。
:由两个或更多个表的笛卡尔积组成的结果集。对于多个CROSS连接，请使用圆括号来更改连接的自然顺序。
:指定连接操作的类型，包括以下种类。
INNER：指定返回每对匹配的行。如果未指定连接类型，则是默认设置。
FULL[OUTER]：指定在结果集中包含左表或右表中不满足连接条件的行，并将对应于另一个表的输出列设为NULL。这是对通常由INNER JOIN返回行的所有行的补充。
LEFT[OUTER]: 指定在结果集中包含左表中所有不满足连接条件的行，且在由内连接返回的所有的行之外，将另外一个表的输出列设为NULL。 RIGHT[OUTER]：指定在结果集中包含右表中所有不满足连接条件的行，且在由内连接返回的所有的行之外，将另外一个表的输出列设为NULL。
JOIN：指明所指定的连接操作应在给定的表或视图之间执行。
ON：指定连接所基于的条件。尽管经常使用列和比较运算符，但此条件可指定任何谓词，当条件指定列时，列不一定必须具有相同的名称或数据类型；但是，如果数据类型不一致，则这些列要么必须相互兼容，要么是SQL Sever能够隐性转换的类型。如果数据类型不能隐式转换，则必须使用CAST函数显式转换数据类型。
四．练习
1.无条件查询
例5-1 查询全体学生的详细记录。
分析：这是一个无条件的选择查询，默认为全体。
例5-2 查询全体学生的姓名（Sname）、学号（Sno）、所在学院（Sdept）。
分析：这是一个无条件的投影查询。
例5-3 查询全体学生的姓名（Sname）、出生年月及学号（Sno）。
分析：出生年月的标题用别名BirthYear显示，函数YEAR()返回日期时间型参数的年份。
例5-4 查询选修了课程的学生学号。
分析：虽然20121323001同学选修了4门课程，但DISTINCT去除了重复的学号，所以无论一个学生选了多少门课，在查询结果中也仅显示一次。
2.条件查询
例5-5 查询SE学院的全体学生学号（Sno）和姓名（Sname）。
例5-6 查询年龄在18~22岁的学生姓名（Sname）、出生年月以及年龄。
分析：Student表中Sbirthday提供的出生日期是日期时间型数据，因此要计算年龄需用GETDATE()获取当前年份，然后减去出生年份即可。
注意：在查询显示时，为增强可读性和可理解性，计算列一般都要使用别名来命名。
例5-7 现实学号最后一位为1或者2或者3的学生学号、姓名、性别、年龄和学院名称。
分析：WHERE子句也可以修改为WHERE SnoLIKE’%[1-3]’。
例5-8 查询软件工程学院、人文学院的学生学号（Sno）、姓名（Sname）和性别（Ssex）。
分析：也可以将以上查询等价写成以下形式。
例5-9 查询所有姓“李”的学生姓名（Sname）、学号（Sno）和性别（Ssex）。
例5-10 查询所有不姓“李”的学生姓名（Sname）和年龄。
例5-11假设某些学生选修课程后没有参加考试，所以有选课记录却没有考试成绩，请查询缺少成绩的学生学号（SNO）和相应的课程号（CNO）
注意WHERE子句中的条件只能使用IS,不能写成WHERE GRADE=NULL,或者WHERE GRADE=’NULL’,这两种表的方式都是错误的，请大家一定要注意。
例5-12 查询有课程成绩的学生学号（SNO）和课程号（CNO）
例5-13 查询选修了C002号课程的学生学号（SNO）和成绩（GRADE），并按成绩升序排列。
例5-14 查询全体学生情况，查询结果按所在学院的院名将序排列，同一学院的学生按年龄升序排列。
分析：第一排序按照SDEPT将序排列，然后按照年龄升序排列（其实就是出生日期的降序排列，可以写成SBIRTHDAY DESC），这里根据计算表达式计算的年龄大小进行排序。
例5-15 查询所有课程的总学分。
例5-16 查询选修了课程的学生人数。
例5-17计算选修C001号课程的学生的平均成绩。
例5-18 查询选修了C001号课程的学生的最高分数。
例5-19 查询各学院的学生数量。
例5-20 查询各种课程的平均成绩。
21.查询各门课程号（cno）及相应的选课人数
22.查询每个学院男生和女生人数
分析：查询首先按学院分小组，再按各学院的男女生分别再进行分组统计；其次根据统计需要，还需要对查询结果按学院进行排序，这是一个隐式的查询要求。
23.查询选修了3门及以上课程的学生学号（sno）
24.查询平均成绩在60以上的课程号及该课的平均成绩
25.使用compute子句对中文学院（ch）所有男生的人数进行明细汇总。
26.使用compute by子句按学院对学生进行明细汇总。
27.从student表中查询所有女生的基本信息，并生成一个新的女生信息表。
28.将总分在200分以上的学生情况（学号，姓名，性别，总成绩）存储到数据表gradelist中，假设表gradelist不存在。
29.查询学生20121323012选修课程的成绩，将其保存在变量grade中。
分析：显示20121323012最后一门课程车的成绩，即83
30.查询c002号课程成绩最高的学生记录。
注意：如果c002号课程成绩存在并列最高，则使用下列SQL语句显示所有并列成绩。
Select top 1 with ties * From sc Where cno=’c002’ Order by grade desc
5-31查询选修C002号课程的学生姓名
5-32 查询学号为20121323001的学生姓名、院系、课程号及成绩
5-33 查询所有选修课程编号为C003的学生学号、姓名和成绩
5-34 查询被选修了的课程的课程号、课程名、该课程的学生选修人数及课程平均分
自身连接：一个表与其自己进行连接；此时需要给表起别名已示区别；由于所有属性名都是同名属性，因此必须使用别名前缀。
5-35 查询与“何燕”同一个学院的其他学生的学号、姓名、性别、学院及专业情况
分析：查询学生“何燕”对应的表别名a，拟查询学生信息在别名表b中，因此在西安市查询结果时，只能显示别名表b中的学生信息。注意，因为学生“何燕”本身也包含在别名表b中，因此查询结果自然也包含了“何燕”
5-36 查询学生中年龄相同的学生情况。要求按同龄学生分组显示其学号、姓名、性别、年龄、籍贯、学院、年级和专业，且能显示各年龄组人数等信息
分析：查询首选将年龄YEAR（a.Sbirthday）相同的学生升序排列，然后按同龄学生分组统计。这样的查询讲出现同龄学生中一个学生重复出现的现象，为解决此问题，使用了去重复子句DISTINCT(Sno)。
5-37 查询所有选修了课程的学生学号、姓名和成绩
外部连接（OUTER JOIN）：同时显示内部连接的匹配行和不匹配行，就要用到外连接。
5-38 查询所有学生选课情况（包括未选课学生），显示学生的学号、姓名、课程号及成绩
分析 ：由执行结果可以看出，李玲，王坤鹏，刘显荣和何燕4位同学对应的Cno值为NULL,代表他们未选课，其余同学都选修了课程。
5-39 为SE学院的所有学生创建一个学生信息视图
5-40 为讲授“数据结构”课程的教师创建一个DS_Teacher视图，包括课程编号、课程名称和教师姓名
五．作业SQL语句
1.查询软件工程学院（SE）学生的学号和姓名
2.查询选修C002课程的学生学号和成绩，结果按成绩降序排列；如成绩相同，则按学号升序排列
4.查询软件工程学院（SE）或文学院（CH)姓“张”的学生的信息
3.查询选修C002课程成绩为80~90的学生的学号和成绩，并将成绩乘以0.9输出
5.查询“秦海东”教师担任的课程总学时数
6.查询“陈流星”同学所选修课程的任课教师信息
7.查询总成绩在200分以上的学生学号、总成绩和平均成绩
8.在FROM子句中用INSERT JOIN连接符指定连接条件查询所有有C002号课程成绩的学生学号、姓名和成绩
9.查询选修课程一样且成绩相同的学生的基本情况（使用自身连接）
10.查询所有考试成绩及格的学生成绩信息，结果中包含学生的学号、姓名、性别、选修课程编号、成绩，并按成绩进行降序排列（使用内连接INNER）
11.查询所有学生的总成绩（包括没有成绩的学生），学号和姓名（使用外部连接查询）
12.查询某课程成绩在90分以上的学生学号和姓名（使用谓词IN连接子查询）
13.查询有课程成绩的学生学号和姓名（使用谓词EXISTS连接子查询）
14.从COURSE表中查询课程名称中包含“数据”的课程信息
15.查询所有学生及其选修课情况（包含为选修任何课程的学生），显示学生姓名、课程名称和课程成绩（要求使用外连接）
16.查询所有学生中平均成绩最高的学生学号
17.查询所有软件工程学院（SE)的学生学号、选修课程号以及分数（使用EXISTS谓词）
18.查询选修了学号20131322001的同学所选修的全部课程的学生姓名，学号，课程名。
19.求选修了C001号课程的学生中，C001课程成绩比“陈流星”高的所有同学的学号、姓名和成绩。
20.查询“计算机导论”课程成绩比“数据结构”课程成绩高的学生姓名、课程名、“计算机导论”课程成绩及“数据结构”课程成绩。
