广药数据库实验答案参考[32页]

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

2、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_data,FILENAME=C:EDUC

3、_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 varchar(2),Sdept var

4、char(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,Cno)-（二）、修改基本表的定义-1修改列属

5、性-（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 Student ALTER COLUMN Sno CHAR(8) NOT NULL -需设

6、置为不允许空，否则不能在该列上创建主键-最后，恢复（添加）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之间。ALTER TABLE CourseADD CONSTRAINT CK_year CHECK(year BETWEEN 20

7、06 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）SELECT Course_id,COUNT(*) AS 选修人数 INTO Course_numFROM StudentGradeGROUP

8、 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.depar_id=Deparment.depar_idWHERE Depar_name=计算机系-嵌套查询SELECT * -父查询：根据子查询找出的系别

9、编号，查找该系别编号的班级FROM ClassWHERE Depar_id=(SELECT Depar_id -子查询：找出计算机系的系别编号 FROM Deparment WHERE Depar_name=计算机系)-4查询艾老师所教的课程号。(也可用嵌套查询)SELECT Course_id,*FROM CourseTeacher JOIN Teacher ON CourseTeacher.Teac_id=Teacher.Teac_idWHERE Teac_name like 艾%-5查询年龄小于30岁的女同学的学号和姓名。-GETDATE()为获取系统时间的函数，YEAR()为获取“年”

10、,MONTH()为获取“月”,DAY()为获取“日”。SELECT *FROM StudentWHERE (YEAR(GETDATE()-YEAR(Birthday)80-实验五 参考答案-1找出所有任教“数据库”的教师的姓名。SELECT teac_nameFROM teacher JOIN courseteacher ON teacher.teac_id=courseteacher.teac_id JOIN course ON courseteacher.course_id=course.course_idWHERE course_name=数据库 -此处用连接查询，三个表连接后形成一个大

11、表，然后从中筛选。-练菲彰上了两个班级的数据库，因此有两条记录。可用DISTINCT去除重复行-嵌套查询SELECT teac_name -根据子查询得到的结果在teacher中找出上“数据库”的老师，上数据库的老师只有一个，故只有一条记录。FROM teacher WHERE teac_id in(SELECT teac_id FROM courseteacher WHERE course_id=(SELECT course_id FROM course WHERE course_name=数据库) -2取出学号为“980101011”的学生选修的课程号和课程名。SELECT course.

12、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_id=980101001)-3“涂杰杰”所选修的全部课程号及成绩。（注意：school中有同名，即有两名学生叫“涂杰杰”。）SELECT course_i

13、d,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语言成绩比数据结构成绩好的学生（自身连接）。SELECT *FROM studentgrade AS a JOIN studentgrade AS b ON a.stu_id=b.stu_idWH

14、ERE 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.Course_name, SG2.GradeFROM StudentGrade AS SG1 JOIN Course AS C1 ON SG1.Course_id=C1

15、.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 AND C1.Course_name=C语言 AND C2.Course_name=数据结构-5显示所有课程的选修情况（外连接）。SELECT *FROM cour

16、se LEFT JOIN studentgrade ON course.course_id=studentgrade.course_idORDER BY course.course_id -为了便于观察。将每门课程的选修情况列在一起-或：列出各门课程的选修人数，无人选的课程的选修人数为0。-COUNT(stu_id)，此处不能为*，COUNT(*)为统计有多少行（记录）。因为course与studentgrade左连接后，每门课程都至少有一条行（记录）。SELECT course.course_id,COUNT(stu_id) AS 选修人数 FROM course LEFT JOIN stu

17、dentgrade 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=studentgrade.stu_idWHERE course_id in(0109,0111)-若列出班级名称，则需在于Class表连接SELECT student.stu_id,stu_nam

18、e,class_nameFROM student JOIN studentgrade ON student.stu_id=studentgrade.stu_id JOIN class ON student.class_id=class.class_idWHERE course_id in(0109,0111)-当多个表（A,B,C）进行连接时，B表同时具有A表和C表中的某一字段，最好将B表作为连接三个表的中介，-如下，将student作为连接studentgrade和class的中介SELECT student.stu_id,stu_name,class_nameFROM studentgra

19、de JOIN student ON studentgrade.stu_id=student.stu_id JOIN class ON student.class_id=class.class_idWHERE course_id in(0109,0111)-嵌套查询SELECT student.stu_id,stu_name,class_idFROM student WHERE stu_id in(SELECT stu_id FROM studentgrade WHERE course_id in(0109,0111) )-7查询“0203”课程的最高分的学生的学号。SELECT stu_id

20、FROM studentgradeWHERE course_id=0203 AND grade=(SELECT MAX(grade) FROM studentgrade -子查询的作用是：找出“0203”课程的最高分 WHERE course_id=0203)-如果最高分的同学有两个以上(分数相同)，此方法无法实现。SELECT TOP 1 Stu_idFROM StudentGradeWHERE Course_id=0203ORDER BY Grade DESC-8没有选修以“01”开头的课程的学生信息。（用子查询完成。提示not in或not exists）SELECT *FROM Stu

21、dentWHERE 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 -EXISTS查询为相关子查询，依赖父表(student),需将两表连接 AND course_id like01%)-9找出“苏贤兴”同学所学课程的名称和成绩。(请使用连接查询和嵌套查询分别来完成) SELECT cours

22、e_name,gradeFROM course JOIN studentgrade ON course.course_id=studentgrade.course_id JOIN student ON studentgrade.stu_id=student.stu_idWHERE stu_name=苏贤兴-嵌套查询SELECT course_name,gradeFROM course JOIN studentgrade ON course.course_id=studentgrade.course_id WHERE stu_id in(SELECT stu_id FROM student WH

23、ERE stu_name=苏贤兴)-实验六参考答案-1统计年龄大于30岁的学生的人数。SELECT COUNT(*)FROM StudentWHERE (year(getdate()-year(Birthday)30-2统计数据结构有多少人80分或以上。-嵌套查询- SELECT COUNT(*) as 人数- FROM StudentGrade- WHERE Grade80 AND Course_id=(SELECT Course_id- FROM Course- WHERE Course_name=数据结构)-连接查询SELECT COUNT(*) as 人数FROM StudentGra

24、de,CourseWHERE Grade80 AND StudentGrade.Course_id=Course.Course_id AND Course_name=数据结构-3统计各系开设班级的数目(系名称、班级数目)，并创建结果表。SELECT Depar_name,COUNT(*) AS 班级数目 INTO Class_CountFROM Deparment,ClassWHERE Class.Depar_id=Deparment.Depar_idGROUP BY Deparment.Depar_name-4找出最多人选修的三门课的课程编号，以及统计选修人数，并按人数降序排列。SELECT

25、 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) 最高分,MIN(Grade) 最低分,AVG(ALL(Grade) AS 平均分,SUM(ALL(Grade) AS 总分FROM StudentGrade,CourseWHERE StudentGrade.Course_id=Cou

26、rse.Course_idGROUP BY Course_name-6所有成绩都在70分以上的学生姓名（提示：使用子查询）SELECT Stu_id,Stu_nameFROM StudentWHERE Stu_id IN(SELECT Stu_id FROM StudentGrade GROUP BY Stu_id HAVING MIN(Grade)=70) -注意：HAVING MIN(Grade)=70 是关键。-注意：MIN(Grade)是关键。此处分组条件不可以是Student.Stu_id。SELECT Stu_id,Stu_nameFROM Student WHERE (SELEC

27、T 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 StudentGrade a -关键：Stu_id要来自StudentGrade表，否则就会出现问题：无法剔除没有选课的学生 where 70=all (SELECT Grade FROM StudentGrade b WHERE a.St

28、u_id=b.Stu_id )-或SELECT Stu_id,Stu_nameFROM StudentWHERE Stu_id in (SELECT Stu_id FROM StudentGrade -关键：Stu_id要来自StudentGrade表，否则就会出现问题：无法剔除没有选课的学生 WHERE NOT EXISTS (SELECT * FROM StudentGrade WHERE Student.Stu_id=StudentGrade.Stu_id AND Grade=2) -9检索至少选修课程“数据结构”和“C语言”的学生学号SELECT Stu_idFROM StudentG

29、rade JOIN Course ON StudentGrade.Course_id=Course.Course_id WHERE Course_name=数据结构 AND Stu_id IN (SELECT Stu_id FROM StudentGrade JOIN Course ON StudentGrade.Course_id=Course.Course_id WHERE Course_name=C语言 )-10列出所有班名、班主任、班长、系名。（请使用连接查询；进一步考虑使用外连接，因为很多班级可能是没有班长的，考虑需要显示所有班级的信息）-列出所有班名，班主任，班长，系名/*SELE

30、CT 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=Deparment.Depar_id */-或：(此方法的查询结果中还包括Monitor为NULL的记录)SELECT Class_name,Stu_name,Teac_name,Depar_nameFROM Class LEFT JOIN Student ON Mo

31、nitor=Student.Stu_id JOIN Teacher ON Director=Teacher.Teac_id JOIN Deparment ON Class.Depar_id=Deparment.Depar_id-实验七参考答案-l、新开设一门课程，名叫网络安全与防火墙，学时40，编号为“0118”，主要介绍网络的安全与主要的防火墙软件。 INSERT INTO Course(course_id,course_name,course_hour,introduce)VALUES (0118,网络安全与防火墙,40,主要介绍网络的安全与主要的防火墙软件)-2、先建立monitor表，

32、其结构与student表大致一样包含student表的学号、姓名、性别和班级编号，然后把班级编号为“0101”的学生的相应资料插入到monitor表中。CREATE TABLE monitor( stu_id varchar(9) constraint pk_monitor primary key, stu_name varchar(8), stu_sex varchar(2), class_id varchar(4)INSERT INTO monitorSELECT stu_id,stu_name,stu_sex,class_idFROM studentWHERE class_id=0101

33、-3、更新所有职称为“助教”的教师职称为“助理教师”。UPDATE teacherSET techpost=助理教师WHERE techpost=助教-4、在所有经济系班级的名称前加上“经济系”三个字。UPDATE classSET class_name=经济系+class_nameWHERE class_id=(SELECT class_id FROM deparment WHERE depar_name=经济系)-5、学号为“980101005”的学生的“数据结构”课程成绩改为80分。UPDATE studentgradeSET grade=80WHERE course_id=(SELEC

34、T course_id FROM course WHERE course_name=数据结构) AND stu_id=980101005-6、删除studentgrade表中所有成绩不及格的记录。DELETEFROM studentgradeWHERE grade60-7、删除studentgrade表中学号以“99”开头的学生选修课程的记录。DELETEFROM studentgradeWHERE stu_id like 99%-8、删除课程名为“C语言”的课程信息和所有这门课的选课信息/*此题中由于course表与courseteacher和studentgrade存在外键联系，根据各表中

35、的course_id建立。从现实中的逻辑角度考虑，应是：先开设一门课，然后指定讲授这门课的老师、时间和地点，再是学生们根据自身情况选修这门课程。因此，如需删除这门课（撤销此课），先删除学生选课信息，再删除该课的授课信息，最后删除该课的课程信息。*/DELETEFROM studentgradeWHERE course_id=(SELECT course_id FROM course WHERE course_name=C语言)DELETEFROM courseteacherWHERE course_id=(SELECT course_id FROM course WHERE course_na

36、me=C语言)DELETEFROM courseWHERE course_name=C语言-实验八参考答案-（一）、视图的操作-1建立一视图View_CSTeacher，列出计算机系各个老师的资料(姓名、性别、职称)。USE SCHOOLGOCREATE VIEW VIEW_CSTEACHERASSELECT TEAC_NAME,TEAC_SEX,TECHPOSTFROM TEACHERWHERE DEPAR_ID=(SELECT DEPAR_ID FROM DEPARMENT WHERE DEPAR_NAME=计算机系)-2建立一视图View_Class，列出每个班级的名称、系别和班级人数。

37、USE SCHOOLGOCREATE VIEW VIEW_CLASSASSELECT CLASS_NAME,DEPAR_NAME,COUNT(STU_ID)FROM CLASS JOIN DEPARMENT ON CLASS.DEPAR_ID=DEPARMENT.DEPAR_ID JOIN STUDENT ON CLASS.CLASS_ID=STUDENT.CLASS_IDGROUP BY CLASS_NAME,DEPAR_NAME-3建立一视图View_Student，列出每个学生的学号、选修课程门数和平均成绩，并按平均成绩从大到小排列。USE SCHOOLGOCREATE VIEW VIE

38、W_STUDENTASSELECT TOP 1000 STU_ID,COUNT(COURSE_ID) AS 选修课程数,AVG(GRADE) AS 平均成绩 -TOP 1000 目的是结合ORDER BY在子查询中使用，并排序FROM STUDENTGRADEGROUP BY STU_IDORDER BY AVG(GRADE) DESC-4查询视图View_Student。-将视图作为基本表一样查询USE SCHOOLSELECT *FROM VIEW_STUDENT-5修改视图View_Student，列出每个学生的学号、姓名、选修课程门数和平均成绩。-此题要求实质是：先删除该视图（View

39、_Student），然后在创建视图（View_Student）USE SCHOOLGODROP VIEW VIEW_STUDENTCREATE VIEW VIEW_STUDENTASSELECT STUDENT.STU_ID,STU_NAME,COUNT(COURSE_ID) AS 选修课程数,AVG(GRADE) AS 平均成绩 FROM STUDENT JOIN STUDENTGRADE ON STUDENT.STU_ID=STUDENTGRADE.STU_IDGROUP BY STUDENT.STU_ID,STU_NAME-6要通过视图View_Student，将学号为“00050300

40、2”的平均成绩改为90分，是否可以实现？并说明原因不可以实现。原因： 对视图的更新，本质就是转化为对基本表的更新。因此，更新视图会受到一些限制，比如：一些视图是不可更新的， 因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新。 简而言之，如果对视图的更新无法转换成对基本表的更新，此更新则不被允许。 本题中通过视图对平均成绩更新，平均成绩为从基本表中通过集合运算产生的结果，在视图的来源表中并没有与其对应的行/列， 因此无法对平均成绩的更新转化为对基本表中某些行/列的更新。-7删除视图View_CSTeacher。USE SCHOOLGODROP VIEW VIEW_CSTEACHER

41、-（二）、索引的创建与删除-1分别建立以下索引(如果不能成功建立，请分析原因)-(1) 在Student表的Stu_name列上建立普通降序索引。USE SCHOOLGOCREATE INDEX IX_SNAMEON STUDENT(STU_NAME DESC)-(2) 在Course表的Course_name列上建立唯一索引。USE SCHOOLGOCREATE UNIQUE INDEX IX_COURSE_NAMEON COURSE(COURSE_NAME)-(3) 在studentgrade表的Stu_id列上建立聚集索引。此题要求的聚集索引不能正常建立。原因： studentgrade

42、中已创建主键，一个基本表在创建主键的同时，DBMS会自动在主键（stu_id,course_id）上创建聚集索引。而一个基本表只能有一个聚集索引，故本题所要求的索引无法成功创建。-(4) 在studentgrade表的Stu_id(升序)，Course_id(升序)和Grade(降序)三列上建立一个普通索引。USE SCHOOLGOCREATE UNIQUE INDEX IX_studentgradeON studentgrade(STU_ID,COURSE_ID,GRADE DESC)-2删除索引-(1)将studentgrade表的Sno列上的聚集索引删掉。此题要求不能成功实现。原因：普通

43、的索引可以用drop来删除：drop index 表名.索引名; 但是在建立表的时候，主键自动在主键列上建立一个聚集索引（通过给表增加的一个唯一约束也会自动增加一个唯一索引）这类通过主键和唯一约束创建的索引与主键或唯一约束是同名的，是不能够通过DROP INDEX来删除的。 必须通过以下方法删除该聚集索引（其实就是通过删除主键的方式删除索引，但不能删除索引进而删除主键）才能创建其他的索引：alter table 基本表名 drop constraint 主键约束名; 通过此方法会删除主键，因此可以在建立了其他想要建立的索引之后，重新建立主键。（唯一索引的操作方法类似） -实验九参考答案-4为U

44、1账户设置对Student表的权限，让它查询Student，修改除了Stu-id之外的其他字段，并验证。use schoolgogrant select,update(stu_name,stu_sex,birthday,phone,address,class_id)on studentto u1-或grant select,update on student to u1revoke update(stu_id) on student from u1-通过SQL命令或企业管理器查看用户U1的权限-通过各种方式方法验证符合/违反U1所拥有权限的操作，仔细查看DBMS所作出的反应，并分析其原因。-5

45、设置数据库角色。设置数据库角色A1，A1能查询、更新Course表，并把此角色赋予U2并验证U2是否具有此权限。-创建数据库角色use schoolgocreate role A1 -或exec sp_addrole A1-给数据库角色授权grant select,update on course to A1 -把此角色赋予U2，其实是将U2添加为角色A1的成员exec sp_addrolemember A1,U2-通过SQL命令或企业管理器查看用户U2的权限-通过各种方式方法验证符合/违反U2所拥有权限的操作，仔细查看DBMS所作出的反应，并分析其原因。-6修改U1的权限，让它具有传递性，并

46、传递给U3，并验证。-修改U1的权限，实际操作是：对其重新授权use schoolgogrant select,update(stu_name,stu_sex,birthday,phone,address,class_id)on studentto u1with grant option-用U1登录SQL SERVER，然后在U1中对U3进行授权（U1授给U3的权限必须得是U1能够授予的）grant select,update(stu_name,stu_sex,birthday,phone,address,class_id)on studentto u3-通过SQL命令或企业管理器查看用户U3的权限-通过各种方式方法验证符合/违反U3所拥有权限的操作，仔细查看DBMS所作出的反应，并分析其原因。-实验十