东北大学数据库实验

1、实验(一): 熟练掌握SQL语言实验目的：熟悉上机环境，创建数据库，在数据库上建立关系模式，插入数据，进行相应的查询操作。实验内容：具体包括如下三部分。一、 熟悉上机环境。客户/服务器结构，数据库服务器在一台NT服务器上，同学们通过客户机(操作系统为Windows 2000)上安装的SQL Server客户端程序, 使用SQL Server数据库服务器。具体包括：1. 了解SQL Server 环境。鼠标点击开始，进入“Microsoft SQL Server®企业管理器”，点击SQL Server组下的数据库服务器（服务器名称为NEUC-201S(Windows NT)）, 可以看

2、到服务器上的圆形标志变为绿色，说明客户端程序已与服务器连接成功。点击服务器（NEUC-201S(Windows NT)）下的数据库，可以看到服务器上已建立的数据库，你可访问你有权访问的数据库，并进行相应的操作功能。因为，数据库服务器上建有许多数据库, 每个数据库都有一些合法的用户。2. 鼠标点击开始，进入“Microsoft SQL Server®查询分析器”，输入用户名和口令，进入SQL查询分析器。如：你登录的客户机为27号，那么请以用户名user27，口令为user27登录，登录后缺省连到数据库user27上，user27/user27是数据库user27的创建者，因此用户use

3、r27/ user27具有在数据库user27上创建表等一切权力。3. 在SQL查询分析器环境下，你就可进行SQL命令语句操作了。二、 在数据库useri上创建学生选课有关关系模式，并插入相应的数据，实现有关查询。1. 描述学生、课程情况的E-R图如下，请将其转化为用关系数据模型描述的关系模式学生课程选修学号姓名年龄所在系成绩课号课程名学时学分先行课号2. 在数据库中定义这些关系模式，并向基本表中插入如下数据：CREATE table Students(Sno char(11) primary key,Sname char(20),Sage int,sdept char(20)create t

4、able Courses(Cno char(4),Cname char(40),Ccredit int,Ctime int,Cpno char(4),primary key (cno),Foreign key (Cpno) references Courses(Cno)create table SC(Sno char(11),Cno char(4),Grade int,primary key(Sno,Cno),foreign key(Sno) references Students(Sno),foreign key(Cno) references Students(Cno)insert int

5、o Students values ('20123841','赵一',21,'计算机')insert into Students values ('20123842','赵二',22,'电子')insert into Students values ('20123843','赵三',23,'通信')insert into Students values ('20123844','赵四',24,'自动化')

6、insert into Students values ('20123845','赵五',25,'软件')insert into Students values ('20123846','赵六',26,'数学')insert into Students values ('20123847','赵七',27,'物联网')insert into Students values ('20123848','赵八',28,'

7、;生物')insert into Students values ('20123849','赵九',29,'计算机')insert into Students values ('20123840','赵十',20,'机械')insert into Courses values ('0000','数据库',4,60,null)insert into Courses values ('0001','C语言',4,60,null)in

8、sert into Courses values ('0002','C+语言',4,60,null)insert into Courses values ('0003','JAVA',4,60,null)insert into Courses values ('0004','数据结构',4,60,null)insert into Courses values ('0005','高数',4,60,null)insert into Courses values ('

9、;0006','离散数学',4,60,null)insert into Courses values ('0007','计算原理',4,60,'0005')insert into Courses values ('0008','算法设计',4,60,null)insert into Courses values ('0009','文本',4,60,null)insert into Courses values ('0010','信息安全

10、',4,60,'0009')insert into SC values ('20123840','0001',1)insert into SC values ('20123841','0001',2)insert into SC values ('20123842','0001',3)insert into SC values ('20123843','0001',4)insert into SC values ('20123844&#

11、39;,'0003',3)insert into SC values ('20123845','0001',2)insert into SC values ('20123846','0001',1)insert into SC values ('20123847','0001',2)insert into SC values ('20123848','0001',3)insert into SC values ('20123849',&

12、#39;0001',4)insert into SC values ('20123849','0002',3)3. 插入相应的数据，试着插入重复的元组，结果如何？输入重复数据：insert into Students values ('20123843','赵一',20,'计算机')insert into Students values ('20123843','赵一',20,'计算机')insert into Students values ('20

13、123843','赵一',20,'计算机')insert into Students values ('20123843','赵一',20,'计算机')insert into Students values ('20123843','赵一',20,'计算机')insert into Students values ('20123843','赵一',20,'计算机')insert into Students val

14、ues ('20123843','赵一',20,'计算机')insert into Students values ('20123843','赵一',20,'计算机')insert into Students values ('20123843','赵一',20,'计算机')insert into Students values ('20123843','赵一',20,'计算机')insert into

15、Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,'0000')inse

16、rt into Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,'0000&#

17、39;)insert into Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,

18、9;0000')insert into Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,'0000')insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC v

19、alues ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values

20、 ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values (

21、9;20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)4. 在已建立的关系模式之上（已插入一些数据）建立主键约束，参

22、照约束和用户定义的约束（要求学生年龄不小于14岁，不大于35岁），如果约束不能建立，请分析原因，修改后建立上述约束。alter table Students add check(Sage>=14 and Sage<=35)alter table Students add primary key(Sno)不能建立原因：数据库中已存在学号相同的多个数据解决：delete from Students where Students.Sno='20123843'alter table Students add primary key(Sno)alter table Cours

23、es add primary key(Cno)delete from Courses where Courses.Cno='0001'alter table Courses add primary key(Cno)alter table Courses add Foreign key(Cpno) references Courses(Cno)alter table SC add Foreign key(Sno) references Students(Sno)alter table SC add Foreign key(Cno) references Courses(Cno)a

24、lter table SC add primary key(Sno,Cno)设想原因：SC表中已存在相同学号和课程的多个数据解决：delete from SC where SC.Cno='0001' and SC.Sno='20123843'alter table SC add primary key(Sno,Cno)还是未能创建约束。解决：重新建表，直接在表中创建约束。create table SC(Sno char(11),Cno char(4),Grade int,primary key(Sno,Cno),foreign key(Sno) referenc

25、es Students(Sno),foreign key(Cno) references Courses(Cno)5. 如果约束建立后，请试着插入重复元组，结果如何？试着插入重复元组，无法插入：insert into Students values ('20123843','赵一',20,'计算机')insert into Students values ('20123843','赵一',20,'计算机')insert into Students values ('20123843'

26、,'赵一',20,'计算机')insert into Students values ('20123843','赵一',20,'计算机')insert into Students values ('20123843','赵一',20,'计算机')insert into Students values ('20123843','赵一',20,'计算机')insert into Students values ('2

27、0123843','赵一',20,'计算机')insert into Students values ('20123843','赵一',20,'计算机')insert into Students values ('20123843','赵一',20,'计算机')insert into Students values ('20123843','赵一',20,'计算机')违反了 PRIMARY KEY 约束 '

28、;PK_Students_4D2CD54C'。不能在对象 'Students' 中插入重复键。语句已终止。insert into Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,'0000')inse

29、rt into Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,'0000&#

30、39;)insert into Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,

31、9;0000')insert into Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,'0000')insert into Courses values ('0001','数据库',4,60,'0000')服务器: 消息 547，级别 16，状态 1，行 1INSERT 语句与 COLUMN FOREIGN KEY S

32、AME TABLE 约束 'FK_Courses_Cpno_716A35C2' 冲突。该冲突发生于数据库 'db45'，表 'Courses', column 'Cno'。语句已终止。insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3

33、)insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)inse

34、rt into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert in

35、to SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)insert into SC values ('20123843','0001',3)服务器: 消息 547，级别 16，状态 1，行 1INSERT 语句与 COLUMN FOREIGN KEY 约束 'FK_SC_Cno

36、_70761189' 冲突。该冲突发生于数据库 'db45'，表 'Courses', column 'Cno'。语句已终止。 实验(二): 熟练掌握SQL语言用SQL完成下列查询1. 求选修了课程的学生的学号，要求：(1) 不使用distinct语句，消除重复元组，写出其SQL语句Select Sno from SC (2) 使用distinct语句，消除重复元组，写出其SQL语句select distinct Sno from SC (3) 如果该查询写成： select Student.Sno from Student, SC wh

37、ere Student.Sno=SC.Sno 请问该查询结果和上面哪个结果是相同的？和1相同2. 求开设的课程号、课程名、学时和学分，要求对查询结果的列重新命名。select Cno MyCno ,Cname MyCname,Ctime MyCtime,CCredit MyCredict from Courses3. 求计算机系和数学系的学生学号、姓名和年龄。select Sno,Sname,Sage from Students where Sdept='计算机' or Sdept ='数学'4. 求不是数学系、计算机系的学生的情况，要求select后写*代替列

38、出所有的列名。select * from Students where Students.Sdept!='数学' and Students.Sdept!='计算机'5. 求全体学生的信息，要求按年龄升序排列。select * from Students order by Sage asc6. 求计算机系年龄在1820岁之间的学生姓名和年龄。select Sname,Sage from Students where Sage>=18 and Sage<=20 and Sdept='计算机'7. 求姓名是以张开头的计算机系学生。selec

39、t * from Students where Sname like '张%' and Sdept='计算机'8. 求选修了C2课程的学生的学号、姓名、成绩。select Students.Sno,Students.Sname,SC.Grade from Students,SC,Courses where SC.Sno=Students.Sno and SC.Cno=Courses.Cno and Courses.Cname='C2'9. 求学号为95开头的学生的学号和所选修的课程的学分。select Students.Sno,Courses.C

40、name,Courses.CCredit from Students,SC,Courses where Students.Sno like'95%' and Students.Sno=SC.Sno and SC.Cno=Courses.Cno10. 求选修C1课程且成绩为80分以上的学生的学号、姓名和成绩。select Students.Sno,Students.Sname,SC.Grade from Students,SC,Courses where Students.Sno=SC.Sno and SC.Cno=Courses.Cno and Courses.Cname=&#

41、39;C1' and SC.Grade>8011. 求计算机系每个学生的姓名，选修的课程名和成绩。select Students.Sname,Courses.Cname,SC.Grade from Students,SC,Courses where Students.Sdept='计算机' and Students.Sno=SC.Sno and SC.Cno=Courses.Cno12. 求每个学生的学号及平均成绩。select Sno,AVG(Grade) AvgGrade from SC group by Sno13. 求男学生每一年龄组中超过1人的年龄组及人

42、数。select Sage,Count(Sno) countSno from Students group by Sage having Count(Sno)>114. 求每一门课程的间接先行课号。select C1.Cno,C2.Cpno from Courses C1,Courses C2 where C1.Cpno=C2.Cno15. 求选修了全部课程的学生的学号。select Sno from Students where not exists (select * from Courses where not exists(select * from SC where Sno=S

43、tudents.Sno and Cno=Courses.Cno);实验(三):数据库的安全性和完整性实验目的：了解和使用安全子系统；进行完整性定义和检查。实验内容：具体包括如下三部分。一 熟练掌握SQL(续)CREATE TABLE Student(Sno CHAR(9) ,Sname CHAR(20) ,Sage SMALLINT,Sdept CHAR(20);CREATE TABLE SC(Sno CHAR(9),Cno CHAR(4),Grade SMALLINT);create table Course(Cno char(4),Cname char(40),Ccredit int,Ct

44、ime int,Cpno char(4),)insert into Students values ('20123841','赵一',21,'计算机')insert into Students values ('95123842','赵二',18,'计算机')insert into Students values ('20123843','赵三',23,'通信')insert into Students values ('20123844

45、9;,'赵四',24,'数学')insert into Students values ('95123845','赵五',25,'软件')insert into Students values ('20123846','赵六',26,'数学')insert into Students values ('20123847','赵七',27,'物联网')insert into Students values ('95

46、123848','赵八',28,'数学')insert into Students values ('20123849','张九',19,'计算机')insert into Students values ('95123840','张十',17,'计算机')insert into Students values ('95123841','张yi',17,'物联网')insert into Students val

47、ues ('95123843','张ei',15,'物联网')insert into Courses values ('0000','C1',4,60,null)insert into Courses values ('0001','C语言',4,60,null)insert into Courses values ('0002','C+语言',4,60,null)insert into Courses values ('0003',&

48、#39;JAVA',4,60,null)insert into Courses values ('0004','数据结构',4,60,null)insert into Courses values ('0005','高数',4,60,null)insert into Courses values ('0006','离散数学',4,60,null)insert into Courses values ('0007','计算原理',4,60,'0005&#

49、39;)insert into Courses values ('0008','算法设计',4,60,null)insert into Courses values ('0009','文本',4,60,null)insert into Courses values ('0010','信息安全',4,60,'0009')insert into Courses values ('0011','C2',4,60,'null')insert i

50、nto SC values ('95123840','0000',70)insert into SC values ('95123840','0001',1)insert into SC values ('20123841','0001',2)insert into SC values ('95123842','0001',3)insert into SC values ('20123843','0001',4)insert into

51、SC values ('20123844','0003',3)insert into SC values ('95123845','0001',2)insert into SC values ('20123846','0001',1)insert into SC values ('20123847','0001',2)insert into SC values ('95123848','0001',82)insert into SC v

52、alues ('20123849','0000',90)insert into SC values ('20123849','0002',3)insert into SC values ('95123841','0011',3)insert into SC values ('95123841','0002',3)insert into SC values ('95123843','0000',3)insert into SC value

53、s ('95123843','0001',3)insert into SC values ('95123843','0002',3)insert into SC values ('95123843','0003',3)insert into SC values ('95123843','0004',3)insert into SC values ('95123843','0005',3)insert into SC values (&#

54、39;95123843','0006',3)insert into SC values ('95123843','0007',3)insert into SC values ('95123843','0008',3)insert into SC values ('95123843','0009',3)insert into SC values ('95123843','0010',3)insert into SC values ('95

55、123843','0011',3)1. 修改表Students,在其中加入一个字段性别sex char(1)alter table Students add sex char(1);2. 创建索引：在Students表的Sno上创建唯一聚簇索引, 在SC表的（Sno, Cno）上创建唯一索引. 查询Students表的内容, 记录的顺序有变化吗? 查询SC表的内容, 记录的顺序有变化吗?Create unique clustered index Student_sno on Student(Sno);create unique index Sno_cno on SC(S

56、no,Cno);3. 创建视图§ 创建计算机系学生视图CS_Students，其中包括学号、年龄和性别。带with check opition子句不带with check opition子句时各插入一计算机系学生记录， 通过视图查询插入的记录，结果如何？通过表Students查询插入的记录，查看所在系字段上的值？create view CS_students1 as select Sno,Sage,sex from Students where Sdept='CS' with check optionINSERT INTO CS_students1VALUES(

57、9;20122859',18,'m');create view CS_students2 as select Sno,Sage,sex from Students where Sdept='CS'INSERT INTO CS_students2VALUES('20120000',18,'m');如上图，通过表Students查询插入的记录，查看所在系字段上的值为NULL通过视图查询不到插入的记录。§ 创建一视图，表示学生的平均成绩，其中包括Sno,avgGrade。通过该视图插入一学生姓名和平均成绩，结果如何？通

58、过该视图查找平均成绩在60分以上的学生的学号和平均成绩，结果又如何？§ create view Stuavggrade(sno,avggrade)§ as select Sno,avg(Grade)from SC group by SC.sno§§§ Insert into Stuavggrade§ values('20120001',87)§select sno,avggradefrom Stuavggradewhere avggrade>=60二 了解和使用安全子系统1、将sql server的一合

59、法帐户(user60)加入到你的数据库当中来，并给其授权，使之：SP_grantdbaccess user44;具有创建表的权限 grant create table to user44;具有对Students表查询的权限 grant select on Students to user44;具有对Studetns表的年龄字段修改的权限 grant update(Sage) on Students to user442、用user60/user60进入系统，完成权限验证：在Students表上，实现select * from Studentsselect * from db63.user63.

60、Students在course表上，实现select * from courseselect * from db44.user44.Coursesupdate Studetns set Sage=Sage+1update db44.user44.Students set Sage=Sage+1select * from db44.user44.Studentsupdate Students set Sdept=CSupdate db44.user44.Students set Sdept='CS'select * from db44.user44.Students3、你是数据库

61、的dbo，你来查看dept表，结果如何？使用set user命令使自己成为user60后，查看dept表，结果如何？select sdept from studentsuse db44三 完整性定义和检查1、创建表Studetns，要求学生的年龄在16和25岁之间，并且所在系的缺省取值为CSCREATE TABLE Student(Sno CHAR(9) not null,Sname CHAR(20) ,Sdept char(20) default'CS',Sage int check(Sage>=16 and Sage<=25),Ssex char(2);在Stu

62、detns表上插入一学生记录（9921，cccc,MA,23,f）insert into Student(Sno,Sname,Sdept,Sage,Ssex)values('9921','cccc','MA',23,'f');在Studetns表上插入一学生记录（9922，ddd,MA,27,m）insert into Student(Sno,Sname,Sdept,Sage,Ssex)values('9922','ddd','MA',27,'m');在Studetn

63、s表上插入一学生记录（9923，eeeee,21,m）insert into Student(Sno,Sname,Sage,Ssex)values('9923','eeeee',21,'m');在Studetns表上插入一学生记录（9922，ddd,MA,23,m）insert into Student(Sno,Sname,Sdept,Sage,Ssex)values('9922','ddd','MA',23,'m');2、创建表course，要求课号作为主键create table

64、 course(cno char(10) primary key,ctime char(10),cname char(20),cscore char(10);在course表上插入一课程记录（c9,40,cname1,1）insert into course(cno,ctime,cname,cscore)values('c9',40,'cname1',1);在course表上插入一课程记录（c9,40,cname2,1）insert into course(cno,ctime,cname,cscore)values('c9',40,'cn

65、ame2',1);在course表上插入一课程记录（null,25,canme3,2）insert into course(cno,ctime,cname,cscore)values(null,25,'cname3',2);3、修改表Studetns，指定学号为主键，然后ALTER TABLE Student ADD CONSTRAINT pk_Sno PRIMARY KEY(Sno)插入一记录（9908,shang,CI,20）insert into Student(Sno,Sname,Sdept,Sage)values('9908','sha

66、ng','CI',20);插入一记录（9908,shang,CI,20）insert into Student(Sno,Sname,Sdept,Sage)values('9908','shang','CI',20);插入一记录（,liang,CS,18）insert into Student(Sno,Sname,Sdept,Sage)values('','liang','CS',18);插入一记录（,an,CS,19）insert into Student(Sno,Sname

67、,Sdept,Sage)values('','an','CS',19);4、 创建sc表，要求表sc中的学号参照表Studetns中的学号，sc中的课号参照course中的课号。参照约束创建完成后，向这 三个表插入数据。create table sc2(Sno char(9),Cno char(10),Grade int,foreign key(Sno) references Student(Sno),foreign key(Cno) references course(cno)先在sc表插入一个选课记录，看看有什么情况发生？insert into SC2(Sno,Cno,Grade)values('9922','c9',85);先删除sc中的某个记录，看看