中南大学数据库实验报告.docx

1、中南大学数据库实验报告目录一、 实验一1.1实验要求-11.2实验结果-11.3源代码-1二、实验二 2.1实验要求-3 2.2实验结果-3 2.3源代码-11三、实验三 3.1实验要求-15 3.2实验结果-15 3.3源代码-15四、实验感想一、实验一1.1实验要求1）用SQL的DDL语句创建以下包括读者信息表，借还明细表，图书类别表，图书借阅明细表，图书明细表和工作人员表6个基本表，设置主键，并输入数据。2）用不同的方法创建基本表的约束，并能够查看和删除约束；能够创建和删除默认规则，在试验中要求掌握主键约束的特点和用法；掌握惟一性约束的用法；掌握默认约束和默认规则的用法；掌握CHECK约

2、束的用法；掌握利用主键与外键约束实现参照完整性的方法。3）修改基本表，包括增加一个字段；删除一个字段；增加一个约束；修改字段的数据类型。4）创建与删除索引5) 创建与删除视图1.2实验结果创建出六张基本表1.3源代码create table 读者信息表 (借书证号 int，姓名 varchar(10),性别 tinyint constraint c1 check(性别 in(N男,女)，出生日期 date,借书量 int,工作单位 nvarchar(50),电话 nvarchar(50) constraint c2 unique, email nvarchar(50), PRIMARY KEY

3、(借书证号) );create table 图书类别表 (类别号 varchar(10),图书类别 nvarchar(50), primary key (类别号) );create table 图书明细表 (类别号 varchar(10), 图书编号 int, 图书名称 nvarchar(50),作者 varchar(10), 出版社 nvarchar(50), 定价 int, 购进日期 date, 购入数 int, 复本数 int,库存数 int,primary key (图书编号), foreign key (类别号) references 图书类别表(类别号) on delete cas

4、cade on update cascade );create table 工作人员表 (工号 int, 姓名 varchar(10), 性别 tinyint constraint c4 default N男, 出生日期 date, 电话 nvarchar(50), email nvarchar(50), primary key (工号) );create table 借还明细表 (借书证号 int, 图书编号 int, 借还 tinyint, 借书日期 date, 还书日期 date,数量 int,工号 int, constraint c3 foreign key (借书证号) refere

5、nces 读者信息表(借书证号), foreign key (图书编号) references 图书明细表(图书编号), foreign key (工号) references 工作人员表(工号) );create table 图书借阅明细表 (图书编号 int, 图书名称 nvarchar(50), 借书证号 int, 借出日期 date, 归还日期 date, 库存数 int, foreign key (图书编号) references 图书明细表(图书编号), foreign key (借书证号) references 读者信息表(借书证号) );alter table 读者信息表 dr

6、op constraint c1;alter table 读者信息表 add constraint c1 check(性别 in( N男,N女);alter table 读者信息表 add 年龄 int;alter table 读者信息表 drop 年龄;alter table 读者信息表 add unique(email);alter table 读者信息表 alter column 姓名 char(6);create unique index 读者_借书证号 on 读者信息表(借书证号);drop index 读者_借书证号;create view 读者信息as select * from

7、 读者信息表;drop view 读者信息;二、实验二2.1实验要求用DDL先创建4个表，设置主键，再插入数据，然后做后面的查询2.2实验结果列出student表中所有记录的sname、sex和class列。显示教师所有的单位即不重复的depart列。显示学生表的所有记录。显示score表中成绩在60到80之间的所有记录。显示score表中成绩为85，86或88的记录。显示student表中“95031”班或性别为“女”的同学记录。以class降序显示student表的所有记录。以cno升序、degree降序显示score表的所有记录。显示“98031”班的学生人数。显示score表中的最高分

8、的学生学号和课程号。显示“3-105”号课程的平均分。显示score表中至少有5名学生选修的并以3开头的课程号的平均分数。 显示最低分大于70，最高分小于90 的sno列。显示所有学生的 sname、 cno和degree列。显示所有学生的 sname、 cname和degree列。列出“95033”班所选课程的平均分。显示选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。显示score中选修多门课程的同学中分数为非最高分成绩的记录。显示成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。显示出和学号为“108”的同学同年出生的所有学生的sno、sname和

9、birthday列。显示“张旭”老师任课的学生成绩。显示选修某课程的同学人数多于5人的老师姓名。显示“95033”班和“95031”班全体学生的记录。显示存在有85分以上成绩的课程cno。显示“计算机系”老师所教课程的成绩表。显示“计算机系”和“电子工程系”不同职称的老师的tname和prof。显示选修编号为“3-105”课程且成绩至少高于“3-245”课程的同学的cno、sno和degree，并按degree从高到低次序排列。显示选修编号为“3-105”课程且成绩高于“3-245”课程的同学的cno、sno和degree。列出所有任课老师的tname和depart。列出所有老师和同学的 姓名

10、、性别和生日。检索所学课程包含学生“103”所学课程的学生学号。检索选修所有课程的学生姓名。2.3源代码SELECT sname,sex,class FROM Student;-查询学生表的姓名，性别，班级SELECT DISTINCT depart FROM Teacher;-查询老师表的不重复系SELECT * FROM Student;-查询所有学生信息SELECT * FROM Score WHERE degree BETWEEN 60 AND 80;-查询成绩在60到80之间的记录SELECT * FROM Student WHERE class=95031 OR sex=女;-95

11、031班或者女生信息SELECT * FROM Student ORDER BY class DESC;-按班级序号降序显示学生表SELECT * FROM Score ORDER BY sno,degree DESC;-按学号升序，成绩降序显示成绩表SELECT COUNT(*) FROM Student WHERE class=98031;-显示98031班学生人数SELECT TOP 1 sno,cno FROM Score ORDER BY degree DESC;-显示最高分学生的学号及课程号(使用top)SELECT sno,cno FROM Score WHERE degree=

12、(SELECT MAX(degree)FROM Score);-同上（嵌套查询）SELECT AVG(degree) FROM Course,Score WHERE Co=3-105AND Co=So;-显示3-105课程的平均分SELECT AVG(degree) FROM Score WHERE cno IN (SELECT cno FROM Score GROUP BY cno HAVING COUNT(*)=5) AND cno LIKE 3%;-可换入子查询中-至少有五名学生选修且以三开头课程的平均分SELECT DISTINCT sno FROM Score WHERE degre

13、e BETWEEN 70 AND 90;-成绩在70到90的学号SELECT sname,degree,cno FROM Student,Score WHERE Student.sno=Score.sno;-学生表和成绩表的连接查询SELECT sname,degree,cname FROM Student,Score,Course WHERE Student.sno=Score.sno AND Co=So;-学生表，成绩表，课程表的连接查询SELECT cname,AVG(degree) FROM Student,Course,ScoreWHERE class=95033 AND Stude

14、nt.sno=Score.sno AND So=CoGROUP BY cname;-95033班课程的平均分SELECT DISTINCT Student.sno,sname FROM Student,Score WHERE Student.sno=Score.sno AND cno=3-105 AND degree ALL(SELECT degree FROM Score WHERE sno=109);-选修了课程3-105的学生成绩比学号为109学生成绩高的信息SELECT sno,cno,degree FROM Score WHERE degree=2);-成绩表中不为最高分的学生信息S

15、ELECT * FROM Score WHERE degreeALL (SELECT degree FROM Score WHERE sno=109OR cno=3-105);-显示成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录SELECT sno,sname,birthday-显示出和学号为“108”的同学同年出生的所有学生的sno、sname和 birthday列FROM Student WHERE substring(birthday,1,4)=(select substring(birthday,1,4) from Student where sno=108);SEL

16、ECT sno,So,degree-显示“张旭”老师任课的学生成绩FROM Score,Teacher,Course WHERE So=Co AND Course.tno=Teacher.tno AND tname=张旭;SELECT tname FROM Teacher WHERE tno IN (SELECT tno FROM Course WHERE cno IN (SELECT cno FROM Score GROUP BY cno HAVING COUNT(*)5) );-显示选修某课程的同学人数多于5人的老师姓名SELECT * FROM Student WHERE class=9

17、5033OR class=95031;-显示“95033”班和“95031”班全体学生的记录SELECT DISTINCT cno FROM Score WHERE degree85;-显示存在有85分以上成绩的课程cnoSELECT * FROM Score WHERE cno IN (SELECT cno FROM Course WHERE tno IN (SELECT tno FROM Teacher WHERE depart=计算机系) );-显示“计算机系”老师所教课程的成绩表（子查询）SELECT Score.* FROM Teacher,Course,ScoreWHERE Tea

18、cher.tno=Course.tno AND Co=So AND depart=计算机系;-显示“计算机系”老师所教课程的成绩表（多表连接）SELECT tname,prof FROM Teacher WHERE NOT EXISTS(SELECT * FROM Teacher x WHERE prof=ANY(SELECT prof FROM Teacher y WHERE y.depart=计算机)AND x.depart=电子工程系);-显示“计算机系”和“电子工程系”不同职称的老师的tname和profSELECT cno,sno,degree FROM Score WHERE de

19、greeANY (SELECT degree FROM Score WHERE cno=3-245)AND cno=3-105 ORDER BY degree DESC;- 显示选修编号为“3-105”课程且成绩至少高于“3-245”课程的同学的cno、sno和degree，并按degree从高到低次序排列SELECT cno,sno,degree FROM ScoreWHERE degreeALL (SELECT degree FROM Score WHERE cno=3-245)ANDcno=3-105;-显示选修编号为“3-105”课程且成绩高于“3-245”课程的同学的cno、sno和

20、degreeSELECT DISTINCT tname,depart FROM Teacher,CourseWHERE Teacher.tno=Course.tno;-列出所有任课老师的tname和departSELECT tname,sex,birthday FROM Teacher UNION SELECT sname,sex,birthdayFROM Student;-列出所有老师和同学的 姓名、性别和出生日期SELECT DISTINCT Score.sno FROM Score,Score xWHERE NOT EXISTS (SELECT * FROM Score,Score y

21、WHERE y.sno=103ANDNOT EXISTS(SELECT* FROM Score,Score z WHERE z.sno=x.sno AND o=o);-检索所学课程包含学生“103”所学课程的学生学号SELECT sname FROM StudentWHERE NOT EXISTS (SELECT* FROM Course WHERE NOT EXISTS(SELECT*FROM Score WHERE sno=Student.sno AND cno=Co);-检索选修所有课程的学生姓名三、实验三3.1实验要求1）创建一个查询图书库存量的存储过程CX_TSKCL_PROC，输出

22、的内容包含类别号、图书编号、图书名称、库存数等数据内容。2）创建一个名为TS_CX_PROC的存储过程，它带有一个输入参数，用于接受图书编号，显示该图书的名称、作者、出版和复本数。3）修改TS_CX_PROC存储过程，使之能按图书名称查询图书的相关信息。执行修改后的TS_CX_PROC存储过程，分别查询“航海英语”、“艺海潮音”等图书的信息。4） 删除创建的存储过程5）在图书类别表上创建一个名为tslb_insert_trigger的触发器，当执行INSERT操作时，该触发器被触发，禁止插入记录。6）在图书明细表上创建一个名为ts_delete_trigger的触发器，当执行DELETE操作时

23、，该触发器被触发，禁止删除记录。7）在读者信息表上创建一个名为dzxx_insert_trigger的触发器，当在读者信息表中插入记录时，将该记录中的借书证号自动插入借还明细表中。8）删除上述触发器3.2实验结果创建出相应的存储过程和触发器3.3源代码create procedure CX_TSKCL_PROC as select 类别号,图书编号,图书名称,库存数from 图书明细表/*验证*/exec CX_TSKCL_PROC/*删除*/drop procedure CX_TSKCL_PROCcreate procedure TS_CX_PROC 图书编号1 char(10)assele

24、ct 图书名称,作者,出版社,副本数from 图书明细表where 图书编号=图书编号1/*验证*/exec TS_CX_PROC 476/*删除*/drop procedure TS_CX_PROCalter procedure TS_CX_PROC 图书名称1 char(30) as select * from 图书明细表 where 图书名称=图书名称1/*验证*/exec TS_CX_PROC航海英语exec TS_CX_PROC艺海潮音create trigger TSLB_INSERT_TRIGGER on 图书类别表 for insert as rollback/*验证*/ins

25、ert 图书类别表 values(233,233)/*删除*/drop trigger TSLB_INSERT_TRIGGERcreate trigger TS_DELETE_TRIGGER on 图书明细表 for deleteas rollback/*验证*/delete 图书明细表 where 图书编号=476/*删除*/drop trigger TS_DELETE_TRIGGERcreate trigger DZXX_INSERT_TRIGGER on 读者信息表 for insert as declare 借书证号1 char(20) select 借书证号=借书证号1 from i

26、nserted insert 借还明细表(借书证号) values(借书证号1)/*验证*/insert 读者信息表(借书证号,姓名,性别) values(0918000,张三,男)/*删除*/drop trigger DZXX_INSERT_TRIGGER四、实验感想其实感觉第四个实验更有挑战性，但遗憾的是我并没有做出来。前三个中先做了第二个，当时一边看书一边打代码，也不感觉太难。后来在机房上机，本来是想做一下第一个实验，可是太久没练后好多知识都忘了，然后上机的时间基本上都去补了。以后这种实验还是要提前做。另外，安装SQL server时也遇到了各种问题，比如验证身份等，也都通过度娘解决掉了。