广药数据库实验答案参考.doc

1、-实验三 参考答案（一） 、数据库、表的创建及删除1.将数据库 bookdb 的相关属性（系统默认设置） ，填入下表：项目 内容数据库所有者 XP-201104281226Administrator(Windows 身份登录 SQL server)数据库名称 bookdb数据库逻辑文件名 bookdb数据文件物理名 D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatabookdb.mdf（本机 SQL SERVER 系统安装目录在 D 盘）数据库初始大小 3MB数据文件最大值 不限制增长数据文件增长量 1MB日志逻辑文件名 bookdb_log

2、日志文件物理名 D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatabookdb_log.ldf（本机 SQL SERVER 系统安装目录在 D 盘）日志文件初始大小 1MB日志文件最大值 2,097,152MB日志文件增长量 10%*/-根据“二、实验内容”的要求，使用 T-SQL 命令创建/删除数据库 EDUC，创建数据表student、course、sc。-2.删除 bookdb 数据库。DROP DATABASE bookdb-3.创建数据库 EDUC。CREATE DATABASE EDUCON PRIMARY (NAME=EDUC_

3、data,FILENAME=C:EDUC_data.mdf,SIZE=4MB,MAXSIZE=10MB,FILEGROWTH=1MB)LOG ON (NAME=EDUC_log,FILENAME=C:EDUC_log.ldf,SIZE=1MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%)-4.创建数据表 Student。USE EDUCCREATE TABLE Student(Sno varchar(20) NOT NULL CONSTRAINT PK_Student PRIMARY KEY,Sname varchar(10) NOT NULL,Sage int,Ssex

4、 varchar(2),Sdept varchar(30)-5.创建数据表 Course。CREATE TABLE Course(Cno varchar(15) NOT NULL CONSTRAINT PK_Course PRIMARY KEY,Cname varchar(30),Cpno varchar(15),Ccredit int)-6.创建数据表 SC。CREATE TABLE SC(Sno varchar(20) NOT NULL,Cno varchar(15) NOT NULL,Grade DECIMAL(9,2),CONSTRAINT PK_SC PRIMARY KEY(Sno,

5、Cno)-（二） 、修改基本表的定义-1修改列属性-（1）用 SSMS 将 Student 表中的 Sage 字段设为不能为空（ not null） 。ALTER TABLE StudentALTER COLUMN Sage int NOT NULL-（2）用 SQL 语句将 Student 表中的属性 Sno varchar(20)改成 char(8)类型。-先删除 Sno 上的各种约束（此例中 Sno 上的约束有：主键）ALTER TABLE Student DROP CONSTRAINT PK_Student -CONSTRAINT 也可省略-再修改 Sno 列ALTER TABLE S

6、tudent ALTER COLUMN Sno CHAR(8) NOT NULL -需设置为不允许空，否则不能在该列上创建主键-最后，恢复（添加）Sno 上的主键约束ALTER TABLE Student ADD CONSTRAINT PK_Student PRIMARY KEY(Sno)-2添加列-（1）用 SSMS 在 Course 表中添加一列 year，类型为 varchar（4） ，默认置为空。ALTER TABLE CourseADD year varchar(4) NULL-（2）用 SQL 语句在 year 字段添加约束，year 的属性值在 2006-2012 之间。ALTE

7、R TABLE CourseADD CONSTRAINT CK_year CHECK(year BETWEEN 2006 AND 2012)-3删除列-（1）用 SQL 语句将 Course 表中的 year 字段删除。-先删除 year 上的各种约束（CHECK 约束）ALTER TABLE CourseDROP CONSTRAINT CK_year -再删除 yearALTER TABLE CourseDROP COLUMN year -COLUMN 不能省略-实验四 参考答案-1. 统计各门课程所选修的人数，并把结果存储在一张新表中。 （提示：用SelectInto，group by ）

8、SELECT Course_id,COUNT(*) AS 选修人数 INTO Course_numFROM StudentGradeGROUP BY Course_id-2查询男生的资料。SELECT *FROM StudentWHERE Stu_sex=男-3查询所有计算机系的班级信息。-WHERE 连接查询SELECT *FROM Class,DeparmentWHERE Class.depar_id=Deparment.depar_id AND Depar_name=计算机系-JOIN 连接查询SELECT *FROM Class JOIN Deparment ON Class.depa

9、r_id=Deparment.depar_idWHERE Depar_name=计算机系-嵌套查询SELECT * -父查询：根据子查询找出的系别编号，查找该系别编号的班级FROM ClassWHERE Depar_id=(SELECT Depar_id -子查询：找出计算机系的系别编号FROM DeparmentWHERE Depar_name=计算机系)-4查询艾老师所教的课程号。( 也可用嵌套查询)SELECT Course_id,*FROM CourseTeacher JOIN Teacher ON CourseTeacher.Teac_id=Teacher.Teac_idWHERE

10、Teac_name like 艾%-5查询年龄小于 30 岁的女同学的学号和姓名。-GETDATE()为获取系统时间的函数，YEAR()为获取“年”,MONTH()为获取“ 月”,DAY()为获取“日”。SELECT *FROM StudentWHERE (YEAR(GETDATE()-YEAR(Birthday)80-实验五 参考答案-1找出所有任教“数据库”的教师的姓名。SELECT teac_nameFROM teacher JOIN courseteacher ON teacher.teac_id=courseteacher.teac_idJOIN course ON coursete

11、acher.course_id=course.course_idWHERE course_name=数据库 -此处用连接查询，三个表连接后形成一个大表，然后从中筛选。-练菲彰上了两个班级的数据库，因此有两条记录。可用 DISTINCT 去除重复行-嵌套查询SELECT teac_name -根据子查询得到的结果在 teacher 中找出上“数据库”的老师，上数据库的老师只有一个，故只有一条记录。FROM teacher WHERE teac_id in(SELECT teac_id FROM courseteacherWHERE course_id=(SELECT course_id FROM

12、 courseWHERE course_name=数据库) -2取出学号为“980101011”的学生选修的课程号和课程名。SELECT course.course_id,course_nameFROM course JOIN studentgrade ON course.course_id=studentgrade.course_idWHERE stu_id=980101001-嵌套查询SELECT course_id,course_nameFROM courseWHERE course_id in(SELECT course_id FROM studentgrade WHERE stu_i

13、d=980101001)-3 “涂杰杰”所选修的全部课程号及成绩。 （注意：school 中有同名，即有两名学生叫“涂杰杰”。 ）SELECT course_id,gradeFROM studentgrade JOIN student ON studentgrade.stu_id=student.stu_idWHERE stu_name=涂杰杰-嵌套查询SELECT course_id,gradeFROM studentgradeWHERE stu_id in(SELECT stu_id FROM student WHERE stu_name=涂杰杰)-4C 语言成绩比数据结构成绩好的学生（自

14、身连接） 。SELECT *FROM studentgrade AS a JOIN studentgrade AS b ON a.stu_id=b.stu_idWHERE a.course_id=(SELECT course_id FROM course WHERE course_name=C 语言)AND b.course_id=(SELECT course_id FROM course WHERE course_name=数据结构)AND a.gradeb.grade-连接查询，比嵌套查询复杂SELECT SG1.Stu_id,C1.Course_name,SG1.Grade,C2.Cou

15、rse_name, SG2.GradeFROM StudentGrade AS SG1 JOIN Course AS C1 ON SG1.Course_id=C1.Course_id -此连接得到:每位学生的选课情况及所选课程信息 ,作为一个新表JOIN StudentGrade AS SG2 ON SG1.Stu_id=SG2.Stu_id -此连接得到: 将相同内容的表,表进行自身连接JOIN Course AS C2 ON SG2.Course_id=C2.Course_id -此连接得到: 每位学生的选课情况及所选课程信息,作为一个新表WHERE SG1.GradeSG2.Grade

16、AND C1.Course_name=C 语言 AND C2.Course_name=数据结构-5显示所有课程的选修情况（外连接） 。SELECT *FROM course LEFT JOIN studentgrade ON course.course_id=studentgrade.course_idORDER BY course.course_id -为了便于观察。将每门课程的选修情况列在一起-或：列出各门课程的选修人数，无人选的课程的选修人数为 0。-COUNT(stu_id)，此处不能为*，COUNT(*)为统计有多少行（记录） 。因为 course 与studentgrade 左连接

17、后，每门课程都至少有一条行（记录） 。SELECT course.course_id,COUNT(stu_id) AS 选修人数 FROM course LEFT JOIN studentgrade ON course.course_id=studentgrade.course_idGROUP BY course.course_id-6检索选修课程号为“0109”或“0111”的学生学号、姓名和所在班级。SELECT student.stu_id,stu_name,class_idFROM student JOIN studentgrade ON student.stu_id=studentg

18、rade.stu_idWHERE course_id in(0109,0111)-若列出班级名称，则需在于 Class 表连接SELECT student.stu_id,stu_name,class_nameFROM student JOIN studentgrade ON student.stu_id=studentgrade.stu_idJOIN class ON student.class_id=class.class_idWHERE course_id in(0109,0111)-当多个表（A,B,C）进行连接时， B 表同时具有 A 表和 C 表中的某一字段，最好将 B 表作为连接三

19、个表的中介，-如下，将 student 作为连接 studentgrade 和 class 的中介SELECT student.stu_id,stu_name,class_nameFROM studentgrade JOIN student ON studentgrade.stu_id=student.stu_idJOIN class ON student.class_id=class.class_idWHERE course_id in(0109,0111)-嵌套查询SELECT student.stu_id,stu_name,class_idFROM student WHERE stu_i

20、d in(SELECT stu_id FROM studentgradeWHERE course_id in(0109,0111)-7查询“0203” 课程的最高分的学生的学号。SELECT stu_idFROM studentgradeWHERE course_id=0203 AND grade=(SELECT MAX(grade) FROM studentgrade -子查询的作用是：找出“0203”课程的最高分WHERE course_id=0203)-如果最高分的同学有两个以上( 分数相同)，此方法无法实现。SELECT TOP 1 Stu_idFROM StudentGradeWHE

21、RE Course_id=0203ORDER BY Grade DESC-8没有选修以“01” 开头的课程的学生信息。 （用子查询完成。提示 not in 或 not exists）SELECT *FROM StudentWHERE stu_id NOT IN (SELECT stu_id FROM studentgrade WHERE course_id like01%)SELECT *FROM StudentWHERE NOT EXISTS (SELECT * FROM studentgrade WHERE Student.stu_id=studentgrade.stu_id -EXIST

22、S 查询为相关子查询，依赖父表(student), 需将两表连接AND course_id like01%)-9找出“苏贤兴”同学所学课程的名称和成绩。(请使用连接查询和嵌套查询分别来完成) SELECT course_name,gradeFROM course JOIN studentgrade ON course.course_id=studentgrade.course_idJOIN student ON studentgrade.stu_id=student.stu_idWHERE stu_name=苏贤兴-嵌套查询SELECT course_name,gradeFROM course

23、 JOIN studentgrade ON course.course_id=studentgrade.course_id WHERE stu_id in(SELECT stu_id FROM studentWHERE stu_name=苏贤兴)-实验六参考答案-1统计年龄大于 30 岁的学生的人数。SELECT COUNT(*)FROM StudentWHERE (year(getdate()-year(Birthday)30-2统计数据结构有多少人 80 分或以上。-嵌套查询- SELECT COUNT(*) as 人数- FROM StudentGrade- WHERE Grade80

24、AND Course_id=(SELECT Course_id- FROM Course- WHERE Course_name=数据结构)-连接查询SELECT COUNT(*) as 人数FROM StudentGrade,CourseWHERE Grade80 AND StudentGrade.Course_id=Course.Course_idAND Course_name=数据结构 -3统计各系开设班级的数目( 系名称、班级数目)，并创建结果表。SELECT Depar_name,COUNT(*) AS 班级数目 INTO Class_CountFROM Deparment,Class

25、WHERE Class.Depar_id=Deparment.Depar_idGROUP BY Deparment.Depar_name-4找出最多人选修的三门课的课程编号，以及统计选修人数，并按人数降序排列。SELECT TOP 3 Course_id,COUNT(*) AS 选修人数 FROM StudentGradeGROUP BY Course_id ORDER BY 选修人数 DESC-或：-ORDER BY COUNT(*) DESC-5统计每科目的最高分、最低分，平均分、总分，并以中文列名显示。SELECT Course_name AS 课程名,MAX(Grade) 最高分,MI

26、N(Grade) 最低分,AVG(ALL(Grade) AS 平均分 ,SUM(ALL(Grade) AS 总分FROM StudentGrade,CourseWHERE StudentGrade.Course_id=Course.Course_idGROUP BY Course_name-6所有成绩都在 70 分以上的学生姓名（提示：使用子查询）SELECT Stu_id,Stu_nameFROM StudentWHERE Stu_id IN(SELECT Stu_idFROM StudentGradeGROUP BY Stu_id HAVING MIN(Grade)=70) -注意：HAV

27、ING MIN(Grade)=70 是关键。-注意：MIN(Grade)是关键。此处分组条件不可以是 Student.Stu_id。SELECT Stu_id,Stu_nameFROM Student WHERE (SELECT MIN(Grade) FROM StudentGrade WHERE Student.Stu_id=StudentGrade.Stu_id GROUP BY StudentGrade.Stu_id )=70 -或SELECT Stu_id,Stu_nameFROM StudentWHERE Stu_id in (select a.Stu_id from Student

28、Grade a -关键：Stu_id 要来自 StudentGrade 表，否则就会出现问题：无法剔除没有选课的学生where 70=2) -9检索至少选修课程“数据结构”和“C 语言” 的学生学号SELECT Stu_idFROM StudentGrade JOIN Course ON StudentGrade.Course_id=Course.Course_id WHERE Course_name=数据结构 AND Stu_id IN (SELECT Stu_idFROM StudentGrade JOIN Course ON StudentGrade.Course_id=Course.C

29、ourse_id WHERE Course_name=C 语言 )-10列出所有班名、班主任、班长、系名。 （请使用连接查询；进一步考虑使用外连接，因为很多班级可能是没有班长的，考虑需要显示所有班级的信息）-列出所有班名，班主任，班长，系名/*SELECT Class_name,Stu_name,Teac_name,Depar_nameFROM Student JOIN Class ON Monitor=Student.Stu_id JOIN Teacher ON Director=Teacher.Teac_idJOIN Deparment ON Class.Depar_id=Deparmen

30、t.Depar_id */-或：(此方法的查询结果中还包括 Monitor 为 NULL 的记录)SELECT Class_name,Stu_name,Teac_name,Depar_nameFROM Class LEFT JOIN Student ON Monitor=Student.Stu_idJOIN Teacher ON Director=Teacher.Teac_idJOIN Deparment ON Class.Depar_id=Deparment.Depar_id-实验七参考答案-l、新开设一门课程，名叫网络安全与防火墙，学时 40，编号为“0118” ，主要介绍网络的安全与主要

31、的防火墙软件。 INSERT INTO Course(course_id,course_name,course_hour,introduce)VALUES (0118,网络安全与防火墙,40,主要介绍网络的安全与主要的防火墙软件)-2、先建立 monitor 表，其结构与 student 表大致一样包含 student 表的学号、姓名、性别和班级编号，然后把班级编号为“0101”的学生的相应资料插入到 monitor 表中。CREATE TABLE monitor( stu_id varchar(9) constraint pk_monitor primary key,stu_name var

32、char(8),stu_sex varchar(2),class_id varchar(4)INSERT INTO monitorSELECT stu_id,stu_name,stu_sex,class_idFROM studentWHERE class_id=0101-3、更新所有职称为“助教”的教师职称为“助理教师” 。UPDATE teacherSET techpost=助理教师WHERE techpost=助教-4、在所有经济系班级的名称前加上“经济系”三个字。UPDATE classSET class_name=经济系+class_nameWHERE class_id=(SELECT

33、 class_idFROM deparmentWHERE depar_name=经济系)-5、学号为“980101005” 的学生的“数据结构”课程成绩改为 80 分。UPDATE studentgradeSET grade=80WHERE course_id=(SELECT course_idFROM courseWHERE course_name=数据结构)AND stu_id=980101005-6、删除 studentgrade 表中所有成绩不及格的记录。DELETEFROM studentgradeWHERE grade -添加列| column_name AS computed_c

34、olumn_expression n -n 表示可以同时添加多个列| WITH CHECK | WITH NOCHECK ADDn -添加约束，n 表示可以同时添加多个约束 | DROP -删除约束，n 表示可以同时删除多个约束CONSTRAINTconstraint_name| COLUMN column ,n -删除列，n 表示可以同时删除多个列 | CHECK | NOCHECK CONSTRAINTALL | constraint_name ,n*/-实验十一参考答案-l为 deparment 表增加一列 teac_num 用于统计各个系教师的人数。ALTER TABLE deparm

35、entADD teac_num int-2更新 teac_num 列，让其正确统计当前数据库中各个系教师的人数。UPDATE deparmentSET teac_num=(SELECT COUNT(teac_id)FROM teacherWHERE deparment.depar_id=teacher.depar_id)-3创建一个 insert 触发器 trigger_in_teacher，使得某系增加新教师时，该系对应的teac_num 列自动变化。CREATE TRIGGER trigger_in_teacherON teacherFOR INSERTAS BEGINUPDATE dep

36、armentSET teac_num=teac_num+(SELECT COUNT(*) FROM INSERTEDWHERE deparment.depar_id=INSERTED.depar_id)END-4创建一个 update 触发器 trigger_update_course，修改课程表中 Course 中的某门课的课程号时，对 StudentGrade 中课程号也做相应修改。CREATE TRIGGER trigger_update_courseON courseFOR UPDATEAS BEGINUPDATE studentSET class_id=(SELECT class_i

37、d FROM inserted)WHERE class_id IN (SELECT class_id FROM deleted)UPDATE courseteacherSET class_id=(SELECT class_id FROM inserted)WHERE class_id IN (SELECT class_id FROM deleted)END-5创建一个触发器 trigger_course，当修改课程数据表 course 中的数据时（包括插入、更新和删除操作） ，显示提示信息“课程表被修改了”。CREATE TRIGGER trigger_courseON courseFOR I

38、NSERT,DELETE,UPDATEASBEGINPRINT 课程表被修改了！END-测试 trigger_course 触发器UPDATE courseSET course_hour=85WHERE course_id=0101-6删除 trigger_course 触发器。DROP TRIGGER trigger_course-实验十四参考答案-（一）Transact-SQL 编程-1、T SQL 流程控制语句。请说出下面代码的功能或者输出结果。-1） 、beginendbegindeclare myvar floatset myvar = 456.256beginprint 变量myv

39、ar 的值为： print cast(myvar as varchar(12)endend/*功能为:声明一个局部变量 myvar，类型为 float，用于接收一个带小数位的数。并用数据转换函数cast 将变量 myvar 转换成长度为 12 的变长字符变量，最后用 print 语句将其输出。*/*结果为:变量myvar 的值为：456.256*/-2） 、ifelseuse schoolif (select avg(grade) from StudentGrade where Course_id=0506)80beginprint 课程：print 考试成绩还不错 endelsebeginp

40、rint 课程：print 考试成绩还一般 end/*功能为:通过查询语句找出课程号为0506课程的平均成绩，判断平均成绩是否大于 80，根据条件的成立与否，用 print 输出相应提示信息。*/*结果为:课程：考试成绩还一般*/-3） 、简单的 case 语句举例select Teac_name as 姓名,2012-year(Birthday) as 年龄,case TechPostwhen 教授 then 高级职称 when 讲师 then 中级职称 else初级职称endfrom teachergo/*功能为:利用多分枝语句 case 语句，获得各级职称的教师的职称级别（初级、中级、高

41、级） ，并用 select 输出各教师的姓名，年龄，职称级别*/*结果为:姓名 年龄 - - -聂益桂 50 高级职称练菲彰 37 高级职称钟开滨 35 中级职称汤俊逸 39 中级职称沈军靖 36 初级职称邝越影 35 高级职称艾彤荣 41 中级职称蚁贺薇 48 高级职称刘伙鹰 46 初级职称万马金 50 高级职称艾彤荣 49 高级职称马祥艳 36 初级职称祁铠沁 36 高级职称池显敏 35 初级职称阳辉辉 45 初级职称黄中中 48 初级职称施怀舟 42 高级职称苏洪澜 50 初级职称祁宪戎 44 高级职称邓焯翔 41 初级职称万加尔 46 初级职称左骏俊 51 中级职称蚁钢驹 52 中级职

42、称金贵成 37 高级职称秦季基 37 中级职称aa NULL 初级职称(26 行受影响)*/-4） 、搜索 case 语句举例select Stu_id ,Course_id,grade,case when grade=90 then Awhen grade=80 then Bwhen grade=70 then Cwhen grade=60 then DelseEendfrom StudentGradewhere Course_id=0511go /*功能为:利用多分枝语句 case 语句，将0511课程的成绩百分制成绩转换成 5 分制（A,B,C,D,E） ，并用 select 输出学号，

43、课程号和 5 分制的成绩*/*结果为:Stu_id Course_id grade - - - -000503001 0511 94 A000503002 0511 78 C000503003 0511 50 E000503004 0511 97 A000503005 0511 60 D000503006 0511 87 B000503007 0511 55 E000503008 0511 61 D(8 行受影响)*/-5） 、while 语句declare s int,i intset i = 0set s = 0while i a and course_id=0501/*结果：人数-8*/-2）定义一个长度为 9 的可变长型字符变量，并分别给其赋值 “Welcome to Beijing”及“Beijing”，观察其执行结果。-为了观察结果更方便，可定义两个相同类型和相同长度的变量进行比较