东北大学数据库实验报告.doc

《东北大学数据库实验报告.doc》由会员分享，可在线阅读，更多相关《东北大学数据库实验报告.doc（32页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、优质文本数据库作业软件1404班 燕江弟 20144671一、 熟悉上机环境。客户/效劳器结构，数据库效劳器在一台NT效劳器上，同学们通过客户机(操作系统为Windows 2000)上安装的SQL Server客户端程序, 使用SQL Server数据库效劳器。具体包括：1. 了解SQL Server 环境。鼠标点击开始，进入“Microsoft SQL Server企业管理器，点击SQL Server组下的数据库效劳器效劳器名称为NEUC-201S(Windows NT), 可以看到效劳器上的圆形标志变为绿色，说明客户端程序已与效劳器连接成功。点击效劳器NEUC-201S(Windows N

2、T)下的数据库，可以看到效劳器上已建立的数据库，你可访问你有权访问的数据库，并进行相应的操作功能。因为，数据库效劳器上建有许多数据库, 每个数据库都有一些合法的用户。2. 鼠标点击开始，进入“Microsoft SQL Server查询分析器，输入用户名和口令，进入SQL查询分析器。如：你登录的客户机为27号，那么请以用户名user27，口令为user27登录，登录后缺省连到数据库user27上，user27/user27是数据库user27的创立者，因此用户user27/ user27具有在数据库user27上创立表等一切权力。3. 在SQL查询分析器环境下，你就可进行SQL命令语句操作了。

3、二、 在数据库useri上创立学生选课有关关系模式，并插入相应的数据，实现有关查询。1. 描述学生、课程情况的E-R图如下，请将其转化为用关系数据模型描述的关系模式学生课程选修学号姓名年龄所在系成绩课号课程名学时学分先行课号create table Students -学生(Sno nchar(10),Sname nchar(20),Ssex nchar(8),Sage int,Sdept nchar(20),primary key(Sno),);create table Courses -课程(Cno nchar(4),Cname nchar(50),Ctime int,Ccredit in

4、t,Cpno nchar(4)primary key(Cno),foreign key(Cpno) references Courses,);create table SC -选修(Sno nchar(10),Cno nchar(4),Grade int,primary key(Sno,Cno),foreign key(Sno) references Students,foreign key(Cno) references Courses,);2. 在数据库中定义这些关系模式，并向根本表中插入如下数据：-插入学生表insert into Students(Sno,Sname,Ssex,Sage

5、,Sdept) values (20121001,陆毅,男,21,信息学院)insert into Students(Sno,Sname,Ssex,Sage,Sdept) values (20121002,李铭,男,24,资土学院)insert into Students(Sno,Sname,Ssex,Sage,Sdept) values (20121003,赵红,女,20,信息学院)insert into Students(Sno,Sname,Ssex,Sage,Sdept) values (20121004,黄晓明,男,23,机械学院)insert into Students(Sno,Sn

6、ame,Ssex,Sage,Sdept) values (20121005,李冰,女,19,资土学院)insert into Students(Sno,Sname,Ssex,Sage,Sdept) values (20121006,李俊,男,25,信息学院)insert into Students(Sno,Sname,Ssex,Sage,Sdept) values (20121007,赵金,男,27,资土学院)insert into Students(Sno,Sname,Ssex,Sage,Sdept) values (20121008,杨英,女,22,机械学院)insert into Stu

7、dents(Sno,Sname,Ssex,Sage,Sdept) values (20121009,张东宇,男,18,信息学院)insert into Students(Sno,Sname,Ssex,Sage,Sdept) values (20121010,彭东,男,21,机械学院)-插入课程表insert into Courses(Cno,Cname,Ctime,Ccredit,Cpno) values(2001,C语言,25,2.5,null)insert into Courses(Cno,Cname,Ctime,Ccredit,Cpno) values(2003,C+语言,50,5,20

8、01)insert into Courses(Cno,Cname,Ctime,Ccredit,Cpno) values(1005,文本处理,20,2,2003)insert into Courses(Cno,Cname,Ctime,Ccredit,Cpno) values(1006,信息平安,20,2,2003)insert into Courses(Cno,Cname,Ctime,Ccredit,Cpno) values(2002,汇编语言,30,3,null)insert into Courses(Cno,Cname,Ctime,Ccredit,Cpno) values(1003,高等数学

9、,60,6,null)insert into Courses(Cno,Cname,Ctime,Ccredit,Cpno) values(1002,数值分析,30,3,1003)insert into Courses(Cno,Cname,Ctime,Ccredit,Cpno) values(2004,C#语言,20,2,1001)insert into Courses(Cno,Cname,Ctime,Ccredit,Cpno) values(1001,离散数学,40,4,1002)insert into Courses(Cno,Cname,Ctime,Ccredit,Cpno) values(1

10、004,软件工程,30,3,2004)insert into Courses(Cno,Cname,Ctime,Ccredit,Cpno) values(3001,工程制图,30,3,null)insert into Courses(Cno,Cname,Ctime,Ccredit,Cpno) values(3002,机械原理,60,6,3001)insert into Courses(Cno,Cname,Ctime,Ccredit,Cpno) values(4001,线性代数,30,3,null)insert into Courses(Cno,Cname,Ctime,Ccredit,Cpno)

11、values(4002,测绘工程,50,5,4001)-插入选修关系insert into SC(Sno,Cno,Grade) values(20121001,1003,91)insert into SC(Sno,Cno,Grade) values(20121001,1002,93)insert into SC(Sno,Cno,Grade) values(20121001,1001,89)insert into SC(Sno,Cno,Grade) values(20121001,2001,97)insert into SC(Sno,Cno,Grade) values(20121001,1004

12、,90)insert into SC(Sno,Cno,Grade) values(20121001,1005,98)insert into SC(Sno,Cno,Grade) values(20121001,1006,87)insert into SC(Sno,Cno,Grade) values(20121001,2002,79)insert into SC(Sno,Cno,Grade) values(20121001,2003,97)insert into SC(Sno,Cno,Grade) values(20121001,2004,99)insert into SC(Sno,Cno,Gra

13、de) values(20121003,1003,81)insert into SC(Sno,Cno,Grade) values(20121003,1002,98)insert into SC(Sno,Cno,Grade) values(20121003,1001,79)insert into SC(Sno,Cno,Grade) values(20121003,2001,90)insert into SC(Sno,Cno,Grade) values(20121003,1004,92)insert into SC(Sno,Cno,Grade) values(20121003,1005,78)in

14、sert into SC(Sno,Cno,Grade) values(20121003,1006,80)insert into SC(Sno,Cno,Grade) values(20121003,2002,69)insert into SC(Sno,Cno,Grade) values(20121003,2003,87)insert into SC(Sno,Cno,Grade) values(20121003,2004,69)insert into SC(Sno,Cno,Grade) values(20121006,1003,83)insert into SC(Sno,Cno,Grade) va

15、lues(20121006,1002,98)insert into SC(Sno,Cno,Grade) values(20121006,1001,99)insert into SC(Sno,Cno,Grade) values(20121006,2001,93)insert into SC(Sno,Cno,Grade) values(20121006,1004,72)insert into SC(Sno,Cno,Grade) values(20121006,1005,78)insert into SC(Sno,Cno,Grade) values(20121006,1006,80)insert i

16、nto SC(Sno,Cno,Grade) values(20121006,2002,79)insert into SC(Sno,Cno,Grade) values(20121006,2003,83)insert into SC(Sno,Cno,Grade) values(20121006,2004,89)insert into SC(Sno,Cno,Grade) values(20121009,1003,81)insert into SC(Sno,Cno,Grade) values(20121009,1002,78)insert into SC(Sno,Cno,Grade) values(2

17、0121009,1001,77)insert into SC(Sno,Cno,Grade) values(20121009,2001,70)insert into SC(Sno,Cno,Grade) values(20121009,1004,99)insert into SC(Sno,Cno,Grade) values(20121009,1005,98)insert into SC(Sno,Cno,Grade) values(20121009,1006,89)insert into SC(Sno,Cno,Grade) values(20121009,2002,99)insert into SC

18、(Sno,Cno,Grade) values(20121009,2003,84)insert into SC(Sno,Cno,Grade) values(20121009,2004,79)insert into SC(Sno,Cno,Grade) values(20121004,3001,84)insert into SC(Sno,Cno,Grade) values(20121004,3002,93)insert into SC(Sno,Cno,Grade) values(20121008,3001,87)insert into SC(Sno,Cno,Grade) values(2012100

19、8,3002,89)insert into SC(Sno,Cno,Grade) values(20121010,3001,94)insert into SC(Sno,Cno,Grade) values(20121010,3002,99)insert into SC(Sno,Cno,Grade) values(20121002,4001,92)insert into SC(Sno,Cno,Grade) values(20121002,4002,94)insert into SC(Sno,Cno,Grade) values(20121005,4001,94)insert into SC(Sno,C

20、no,Grade) values(20121005,4002,96)insert into SC(Sno,Cno,Grade) values(20121007,4001,91)insert into SC(Sno,Cno,Grade) values(20121007,4002,95)3. 插入相应的数据，试着插入重复的元组，结果如何？ 答：由于在定义关系模式时，已经添加主键约束primary key(Sno),primary key(Cno),primary key(Sno,Cno)，所以不可以再插入重复的元组。4. 在已建立的关系模式之上已插入一些数据建立主键约束，参照约束和用户定义的约束要

21、求学生年龄不小于14岁，不大于35岁，如果约束不能建立，请分析原因，修改后建立上述约束。答：由于在定义关系模式时，已经添加主键约束primary key(Sno),primary key(Cno),primary key(Sno,Cno)和参照约束(foreign key(Sno) references Students,foreign key(Cno) references Courses, foreign key(Cpno) references Courses)，现添加用户定义的约束要求学生年龄不小于14岁，不大于35岁，例如如以下图：5. 如果约束建立后，请试着插入重复元组，结果如何？

22、试着插入重复元组，无法插入：答：约束建立后，插入重复元组，无法插入，例如如以下图： 三、用SQL完成以下查询1. 求选修了课程的学生的学号，要求：(1) 不使用distinct语句，消除重复元组，写出其SQL语句 select Sno from SC group by SNo having count(*)0 (2) 使用distinct语句，消除重复元组，写出其SQL语句select distinct Students.Sno from Students, SC where Students.Sno=SC. Sno (3) 如果该查询写成： select Students.Sno from

23、Students, SC where Students.Sno=SC.Sno 请问该查询结果和上面哪个结果是相同的？ 都不相同 2. 求开设的课程号、课程名、学时和学分，要求对查询结果的列重新命名。select Courses.Cno 课程号,Courses.Cname 课程名,Courses.Ctime 学时, Courses.Ccredit 学分 from Courses3. 求计算机系和数学系的学生学号、姓名和年龄。select Students.Sno, Students.Sname, Students.Sage from Students where Students.Sdept i

24、n (计算机系,数学4. 求不是数学系、计算机系的学生的情况，要求select后写*代替列出所有的列名。select * from Students where Sdept not in(计算机系,数学系)5. 求全体学生的信息，要求按年龄升序排列。select * from Students order by Sage asc6. 求计算机系年龄在1820岁之间的学生姓名和年龄select Sname, Sage from Students where Sdept =计算机系 and Sage between 18 and 247. 求姓名是以王开头的计算机系学生。select * from

25、 Students where Sname like 王% and Students.Sdept=计算机系8. 求选修了C2(课号)课程的学生的学号、姓名、成绩。select Students.Sno, Students.Sname,SC.Grade from Students,SCwhere Students.Sno=SC.Sno and SC.Cno=C29. 求学号为95开头的学生的学号和所选修的课程的学分。select Students.Sno 学号,Courses.Ccredit 学分 from Students,SC,Courseswhere Students.Sno=SC.Sno

26、 and SC.Cno=Courses.Cno and Students.Sno like 95%10. 求选修C1课程且成绩为80分以上的学生的学号、姓名和成绩。select Students.Sno 学号,Students.Sname 姓名,SC.Grade 成绩 from Students,SC,Courseswhere Students.Sno=SC.Sno and SC.Cno=Courses.Cno and Courses.Cno=C1 and SC.Grade8011. 求计算机系每个学生的姓名，选修的课程名和成绩。select Students.Sname 姓名, Course

27、s.Cname 课程名, SC.Grade 成绩 from Students,SC,Courseswhere Students.Sno=SC.Sno and SC.Cno=Courses.Cno and Students.Sdept=计算机系12. 求每个学生的学号及平均成绩。select Students.Sno 学号,avg(Grade) 平均成绩 from Students,SCwhere Students.Sno=SC.Snogroup by Students.Sno13. 求男学生每一年龄组中超过1人的年龄组及人数。select Sage 年龄组,count(Sno) 人数 from

28、 Students group by Sage having count(*)1 14. 求每一门课程的间接先行课号。select courses1.Cno 课程号,courses2.Cpno 间接先行课号 from Courses courses1,Courses courses2 where courses1.Cpno=courses2.Cno 15. 求选修了全部课程的学生的学号。select Students.Sno 学生的学号 from Students where not exists (select * from Courses where not exists (select

29、* from SC where Sno=Students.Sno and Cno=Courses.Cno) 四、1. 修改表Students,在其中参加一个字段性别sex char(1)ALTER TABLE students ADD sex char(1) ;2. 创立视图 创立计算机系学生视图CS_Students，其中包括学号、年龄和性别。带with check opition子句不带with check opition子句 时各插入一计算机系学生记录， 通过视图查询插入的记录，结果如何？通过表Students查询插入的记录，查看所在系字段上的值？ - with check option

30、create view CS_Students as select sno,sage,ssex,sdeptfrom studentswhere sdept=CSwith check optioninsert into CS_Students(sno,sage,ssex,sdept)values(2010012,19,M,CS);没有with check optioncreate view CS_Students as select sno,sage,ssexfrom studentswhere sdept=CSinsert into CS_Students(sno,sage,ssex)valu

31、es(2010012,19,M);With check option视图插入时参加为计算机系时图、表成功插入，不是时都无法成功参加；没有with no option时可以插入students表，视图始终无法插入创立一视图，表示学生的平均成绩，其中包括Sno,avgGrade。通过该视图插入一学生姓名和平均成绩，结果如何？通过该视图查找平均成绩在90分以上的学生的学号和平均成绩，结果又如何？create view AVG_Stu(Sno,Gavg) asselect sno,AVG(GRADE)from SCgroup by snoselect Sno,Gavgfrom AVG_Stuwhere

32、 Gavg90五、1、将sql server的一合法帐户(user60)参加到你的数据库当中来，并给其授权，使之：具有创立表的权限具有对Students表查询的权限具有对Studetns表的年龄字段修改的权限SP_grantdbaccess user35grant create table to user35grant select on students to user35grant update (ssex) on students to user352、用user60/user60进入系统，完成权限验证：在Students表上，实现select * from Students在cours

33、e表上，实现select * from courseupdate Studetns set Sage=Sage+1update Students set Sdept=CS3、你是数据库的dbo，你来查看dept表，结果如何？使用set user命令使自己成为user60后，查看dept表，结果如何？select * from SCselect * from user35.Student六、完整性定义和检查1、创立表Studetns，要求学生的年龄在16和25岁之间，并且所在系的缺省取值为CSCREATE TABLE Student (Sno CHAR(9) not null, Sname CH

34、AR(20) UNIQUE, Sdept CHAR(20) default CS, Sage SMALLINT check(Sage=16 and Sage=25), Ssex CHAR(2),);在Studetns表上插入一学生记录9921，cccc,MA,23,f在Studetns表上插入一学生记录9922，ddd,MA,27,m在Studetns表上插入一学生记录9923，eeeee,21,m在Studetns表上插入一学生记录9922，ddd,MA,23,m2、创立表course，要求课号作为主键CREATE TABLE course (Cno CHAR(20) primary key

35、, Ctime int, Cname CHAR(20), Cpoint int); 在course表上插入一课程记录c9,40,cname2,1在course表上插入一课程记录null,25,canme3,2INSERTINTO Course(Cno,Ctime,Cname,Ccredit)VALUES (c9,40,cname1,1);INSERTINTO Course(Cno,Ctime,Cname,Ccredit)VALUES (c9,40,cname2,1);INSERTINTO Course(Cno,Ctime,Cname,Ccredit)VALUES (null,25,cname3

36、,2);3、修改表Studetns，指定学号为主键，然后alter table Student add CONSTRAINT c1 primary key(Sno)插入一记录9908,shang,CI,20INSERTINTO Student(Sno,Sname,Sdept,Sage)VALUES (9908,shang,CI,20)插入一记录9908,shang,CI,20插入一记录,liang,CS,18插入一记录,an,CS,194、 创立sc表，要求表sc中的学号参照表Studetns中的学号，sc中的课号参照course中的课号。参照约束创立完成后，向这 三个表插入数据。create

37、 table CS(Sno char(9),Cno char(20),foreign key(Sno) references Student(Sno),foreign key(Cno) references course(Cno)先在sc表插入一个选课记录，看看有什么情况发生？INSERTINTO SC(Sno,Cno,Grade)VALUES (9908,c9,90);先删除sc中的某个记录，看看有什么情况发生？delete from SCwhere Sno=2012001;将Studetns表中的学号9906改为9907，看看有什么情况发生？update Studentset Sno=9999where Sno=9908;