数据库上机报告

数据库上机报告13060110011巩志鑫5.1实验目的（1）熟练掌握SELECT实现单表查询（2）熟练掌握SELECT实现多表查询（含自身连接，外连接，CROSS连接）（3）熟练掌握SELECT实现明细查询（COMPUTEBY的使用）（4）掌握CREATEVIEW创建用户视图的方法；（5）掌握一些查询结果的特定显示输出的技巧和并，交，差查询的实现。5.2实验环境WindowsServer2003+SQLServer2008.5.3实验原理及相关知识数据表的查询可以使用SQLServerManagementStudio和T-SQL语句两种方法实现。5.3.1使用SQLServerManagementStudio进行查询1.查询数据表的元组数据在对象资源管理器中，选择需要查询的数据表，用鼠标右键单击该表，从弹出的快捷菜单中选择“选择前…….行”命令，这时将显示该表的元组数据。2.查询SQL语句在对象资源管理器中，选择需要查询数据的表，用鼠标右键单击该表，从弹出的快捷菜单中选择“编写表脚本为”-SELECT-新查询编辑器窗口；在窗格中会自动显示对应的SELECT语句。5.3.2使用T-SQL查询使用T-SQL查询，就是使用SELECT语句。SELECT的语句格式如下。SELECT[ALL[DISTINCT][TOPn[PERCENT]]select_list[infonew_table]FROMtable_source[,……n][WHEREsearch_condition][GROUPBY[ALL]group_by_expression[,……n][HAVINGsearch_condition]][ORDERBYColumn_name[ASC|DESC]……]]其中，select_list::={*|{table_name|view_name|table_alais}*|{column_name}[[AS]column_alias]][,……n]table_source::=table_name[[AS]table_alias]{WITH(table_hint[,……n])}|view_name[[AS]table_alais][WITH(view_hint[,……n])]|rowest_function[[AS]table_alais]|user_defined_function[[AS]]table_alais]|derived_table[AS]table_alais[(column_alais[,……n])]|joined_tablejoined_table::table_sourcejoin_typetable_sourceONsearch_condition|table_sourceCROSSJOINtable_sourcejoin_type::=[INNER|{{LEFT|RIGHT|FULL}[OUTER]}]JOIN参数说明如下：（1）ＳＥＬＥＣＴＡｌｌ：默认设置，指定在结果集中可以显示重复行。（2）ＤＩＳＴＩＮＣＴ：指定在结果集中只能显示唯一行，空值被认为相等。（3）ＴＯＰｎ：从查询结果集中指定输出前ｎ行，ｎ是０～４２９４９６７２９５的整数。（4）ＰＥＲＣＥＮＴ：如果未指定ＰＥＲＣＥＮＴ，ｎ就是返回的行数，如果指定了ＰＥＲＣＥＮＴ，ｎ就是返回的结果集行的百分比，ｎ值必须为０～１００的数。（5）＜ｓｅｌｅｃｔ－ｌｉｓｔ＞：为结果选择的列，选择列表以逗号分隔。（6）INTOnew-table:创建新表并将查询结果集插入新表。用户若要执行带INTO的SELECT语句，就必须在目的数据库内具有ＣＲＥＡＴＥTABLE权限。New-table中的列按照选择列表指定的顺序创建，每列与选择列表中相应表达式具有相同的名称，数据类型和值。（7）ＦＲＯＭ＜ｔａｂｌｅ－ｓｏｕｒｃｅ＞：指定要在Ｔｒａｎｓａｃｔ＿ＳＱＬ语句中使用的表或者视图，可在语句中使用多达２５６个表。（8）＜ｓｅａｒｃｈ－ｃｏｎｄｉｔｉｏｎ＞：通过使用谓词限制结果集内的返回的行，对搜索条件中可以包含的谓词数量没有限制。（9）＊：指定在ＦＲＯＭ字句内返回所有表和视图内的所有列，列按照ＦＲＯＭ子句中所指定的表或者视图返回，并按在表或视图中创建的顺序返回。（10）ｃｏｌｕｍｎ－ｌｉａｓ：查询结果集内替换列名的可选别名。（11）column_alias：查询结果集内替换列名的可选别名。（12）GROUPBYALL：包含所有组合结果集，甚至包含哪些任何行都不满足WHERE子句制定的搜索条件的组合结果集。如果制定了ALL，组中不满足搜索条件的汇总列将返回空值。（13）group_by_expression:执行分组表达式，也称分组列。注意，text、ntext和image类型的类型的列不能用于group_by_expression。（14）HAVINGseach_condition:指定组或聚合应满足的搜索条件。HAVING通常与GROUPBY子句一起使用。（15）table_scoure中的参数说明如下。Table_name：表名。FROM关键字之后的表和视图的顺序并不影响返回的结果集。[AS]table_alias:table_name、view_name或rowest_fuction的别名，为方便起见而使用，或用于自连接或子查询中的表或视图。别名通常是一个缩短了的表名，用于在连接中引用表中的特定列。如果连接的多个表中有相同名称的列存在，SQLServer要求必须使用表名或别名来限定列名（如果定义了别名则不能使用表名）。join_table:由两个或更多个表的笛卡尔积组成的结果集。对于多个CROSS连接，请使用圆括号来更改连接的自然顺序。join_type:指定连接操作的类型，包括以下种类。INNER：指定返回每对匹配的行。如果未指定连接类型，则是默认设置。FULL[OUTER]：指定在结果集中包含左表或右表中不满足连接条件的行，并将对应于另一个表的输出列设为NULL。这是对通常由INNERJOIN返回行的所有行的补充。LEFT[OUTER]:指定在结果集中包含左表中所有不满足连接条件的行，且在由内连接返回的所有的行之外，将另外一个表的输出列设为NULL。RIGHT[OUTER]：指定在结果集中包含右表中所有不满足连接条件的行，且在由内连接返回的所有的行之外，将另外一个表的输出列设为NULL。JOIN：指明所指定的连接操作应在给定的表或视图之间执行。ONsearch_condition：指定连接所基于的条件。尽管经常使用列和比较运算符，但此条件可指定任何谓词，当条件指定列时，列不一定必须具有相同的名称或数据类型；但是，如果数据类型不一致，则这些列要么必须相互兼容，要么是SQLSever能够隐性转换的类型。如果数据类型不能隐式转换，则必须使用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子句也可以修改为WHERESnoLIKE’%[1-3]’。例5-8查询软件工程学院、人文学院的学生学号（Sno）、姓名（Sname）和性别（Ssex）。分析：也可以将以上查询等价写成以下形式。例5-9查询所有姓“李”的学生姓名（Sname）、学号（Sno）和性别（Ssex）。例5-10查询所有不姓“李”的学生姓名（Sname）和年龄。例5-11假设某些学生选修课程后没有参加考试，所以有选课记录却没有考试成绩，请查询缺少成绩的学生学号（SNO）和相应的课程号（CNO）注意WHERE子句中的条件只能使用IS,不能写成WHEREGRADE=NULL,或者WHEREGRADE=’NULL’,这两种表的方式都是错误的，请大家一定要注意。例5-12查询有课程成绩的学生学号（SNO）和课程号（CNO）例5-13查询选修了C002号课程的学生学号（SNO）和成绩（GRADE），并按成绩升序排列。例5-14查询全体学生情况，查询结果按所在学院的院名将序排列，同一学院的学生按年龄升序排列。分析：第一排序按照SDEPT将序排列，然后按照年龄升序排列（其实就是出生日期的降序排列，可以写成SBIRTHDAYDESC），这里根据计算表达式计算的年龄大小进行排序。例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.使用computeby子句按学院对学生进行明细汇总。27.从student表中查询所有女生的基本信息，并生成一个新的女生信息表。28.将总分在200分以上的学生情况（学号，姓名，性别，总成绩）存储到数据表gradelist中，假设表gradelist不存在。29.查询学生20121323012选修课程的成绩，将其保存在变量grade中。分析：显示20121323012最后一门课程车的成绩，即8330.查询c002号课程成绩最高的学生记录。注意：如果c002号课程成绩存在并列最高，则使用下列SQL语句显示所有并列成绩。Selecttop1withties*FromscWherecno=’c002’Orderbygradedesc5-31查询选修C002号课程的学生姓名5-32查询学号为20121323001的学生姓名、院系、课程号及成绩5-33查询所有选修课程编号为C003的学生学号、姓名和成绩5-34查询被选修了的课程的课程号、课程名、该课程的学生选修人数及课程平均分自身连接：一个表与其自己进行连接；此时需要给表起别名已示区别；由于所有属性名都是同名属性，因此必须使用别名前缀。5-35查询与“何燕”同一个学院的其他学生的学号、姓名、性别、学院及专业情况分析：查询学生“何燕”对应的表别名a，拟查询学生信息在别名表b中，因此在西安市查询结果时，只能显示别名表b中的学生信息。注意，因为学生“何燕”本身也包含在别名表b中，因此查询结果自然也包含了“何燕”5-36查询学生中年龄相同的学生情况。要求按同龄学生分组显示其学号、姓名、性别、年龄、籍贯、学院、年级和专业，且能显示各年龄组人数等信息分析：查询首选将年龄YEAR（a.Sbirthday）相同