東北大學(xué)數(shù)據(jù)庫(kù)實(shí)驗(yàn)報(bào)告2016

1、優(yōu)質(zhì)文本實(shí)驗(yàn)(一): 熟練掌握SQL語(yǔ)言實(shí)驗(yàn)?zāi)康模菏煜ど蠙C(jī)環(huán)境，創(chuàng)立數(shù)據(jù)庫(kù)，在數(shù)據(jù)庫(kù)上建立關(guān)系模式，插入數(shù)據(jù)，進(jìn)行相應(yīng)的查詢操作。實(shí)驗(yàn)內(nèi)容：具體包括如下三局部。一、 熟悉上機(jī)環(huán)境。客戶/效勞器結(jié)構(gòu)，數(shù)據(jù)庫(kù)效勞器在一臺(tái)NT效勞器上，同學(xué)們通過(guò)客戶機(jī)(操作系統(tǒng)為Windows 2000)上安裝的SQL Server客戶端程序, 使用SQL Server數(shù)據(jù)庫(kù)效勞器。具體包括：1. 了解SQL Server 環(huán)境。鼠標(biāo)點(diǎn)擊開(kāi)始，進(jìn)入“Microsoft SQL Server®企業(yè)管理器，點(diǎn)擊SQL Server組下的數(shù)據(jù)庫(kù)效勞器效勞器名稱為NEUC-201S(Windows NT), 可以

2、看到效勞器上的圓形標(biāo)志變?yōu)榫G色，說(shuō)明客戶端程序已與效勞器連接成功。點(diǎn)擊效勞器NEUC-201S(Windows NT)下的數(shù)據(jù)庫(kù)，可以看到效勞器上已建立的數(shù)據(jù)庫(kù)，你可訪問(wèn)你有權(quán)訪問(wèn)的數(shù)據(jù)庫(kù)，并進(jìn)行相應(yīng)的操作功能。因?yàn)?，?shù)據(jù)庫(kù)效勞器上建有許多數(shù)據(jù)庫(kù), 每個(gè)數(shù)據(jù)庫(kù)都有一些合法的用戶。2. 鼠標(biāo)點(diǎn)擊開(kāi)始，進(jìn)入“Microsoft SQL Server®查詢分析器，輸入用戶名和口令，進(jìn)入SQL查詢分析器。如：你登錄的客戶機(jī)為27號(hào)，那么請(qǐng)以用戶名user27，口令為user27登錄，登錄后缺省連到數(shù)據(jù)庫(kù)user27上，user27/user27是數(shù)據(jù)庫(kù)user27的創(chuàng)立者，因此用戶user2

3、7/ user27具有在數(shù)據(jù)庫(kù)user27上創(chuàng)立表等一切權(quán)力。3. 在SQL查詢分析器環(huán)境下，你就可進(jìn)行SQL命令語(yǔ)句操作了。二、 在數(shù)據(jù)庫(kù)useri上創(chuàng)立學(xué)生選課有關(guān)關(guān)系模式，并插入相應(yīng)的數(shù)據(jù)，實(shí)現(xiàn)有關(guān)查詢。1. 描述學(xué)生、課程情況的E-R圖如下，請(qǐng)將其轉(zhuǎn)化為用關(guān)系數(shù)據(jù)模型描述的關(guān)系模式學(xué)生課程選修學(xué)號(hào)姓名年齡所在系成績(jī)課號(hào)課程名學(xué)時(shí)學(xué)分先行課號(hào)2. 在數(shù)據(jù)庫(kù)中定義這些關(guān)系模式，并向根本表中插入如下數(shù)據(jù)：CREATE table Students(Sno char(10) primary key,Sname char(20),Sage int,sdept char(20)create tab

4、le 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(10),Cno char(4),Grade int,primary key(Sno,Cno),foreign key(Sno) references Students(Sno),foreign key(Cno) references Courses(Cno)insert into S

5、tudents values ('20133901','陳一',21,'計(jì)算機(jī)')insert into Students values ('20133902','陳二',22,'電子')insert into Students values ('20133903','陳三',23,'通信')insert into Students values ('20133904','陳四',20,'自動(dòng)化')ins

6、ert into Students values ('20133905','陳五',21,'軟件')insert into Students values ('20133906','陳六',22,'數(shù)學(xué)')insert into Students values ('20133907','陳七',23,'物聯(lián)網(wǎng)')insert into Students values ('20133908','陳八',20,'生物

7、')insert into Students values ('20133909','陳九',21,'計(jì)算機(jī)')insert into Students values ('20133910','陳十',22,'機(jī)械')insert into Courses values ('0001','C語(yǔ)言',4,60,null)insert into Courses values ('0002','C+語(yǔ)言',4,60,null)inse

8、rt into Courses values ('0003','JAVA',4,60,null)insert into Courses values ('0004','數(shù)據(jù)結(jié)構(gòu)',4,60,null)insert into Courses values ('0005','高數(shù)',4,60,null)insert into Courses values ('0006','離散數(shù)學(xué)',4,60,null)insert into Courses values ('0

9、007','計(jì)算原理',4,60,'0005')insert into Courses values ('0008','算法設(shè)計(jì)',4,60,null)insert into Courses values ('0009','文本',4,60,null)insert into Courses values ('0010','信息平安',4,60,'0009')insert into SC values ('20133901',&#

10、39;0001',2)insert into SC values ('20133902','0001',3)insert into SC values ('20133903','0001',4)insert into SC values ('20133904','0003',3)insert into SC values ('20133905','0001',2)insert into SC values ('20133906','00

11、01',1)insert into SC values ('20133907','0001',2)insert into SC values ('20133908','0001',3)insert into SC values ('20133909','0001',4)insert into SC values ('20133910','0002',3)3. 插入相應(yīng)的數(shù)據(jù)，試著插入重復(fù)的元組，結(jié)果如何？插入相同數(shù)據(jù)insert into Students v

12、alues ('20133901','陳一',21,'計(jì)算機(jī)')insert into Students values ('20133901','陳一',21,'計(jì)算機(jī)')insert into Students values ('20133901','陳一',21,'計(jì)算機(jī)')insert into Students values ('20133901','陳一',21,'計(jì)算機(jī)')insert int

13、o Students values ('20133901','陳一',21,'計(jì)算機(jī)')insert into Students values ('20133901','陳一',21,'計(jì)算機(jī)')不能插入重復(fù)數(shù)據(jù)4. 在已建立的關(guān)系模式之上已插入一些數(shù)據(jù)建立主鍵約束，參照約束和用戶定義的約束要求學(xué)生年齡不小于14歲，不大于35歲，如果約束不能建立，請(qǐng)分析原因，修改后建立上述約束。alter table Students add check(Sage>=14 and Sage<=35)al

14、ter table Students add primary key(Sno)不能建立5. 如果約束建立后，請(qǐng)?jiān)囍迦胫貜?fù)元組，結(jié)果如何？試著插入重復(fù)元組，無(wú)法插入： 實(shí)驗(yàn)(二): 熟練掌握SQL語(yǔ)言insert into Students values ('20133901','趙一',21,'計(jì)算機(jī)')insert into Students values ('95133902','趙二',18,'計(jì)算機(jī)')insert into Students values ('20133903&#

15、39;,'趙三',23,'通信')insert into Students values ('20133904','趙四',24,'數(shù)學(xué)')insert into Students values ('95133905','趙五',25,'軟件')insert into Students values ('20133906','趙六',26,'數(shù)學(xué)')insert into Students values ('20

16、133907','趙七',27,'物聯(lián)網(wǎng)')insert into Students values ('95133908','趙八',28,'數(shù)學(xué)')insert into Students values ('20133909','老九',19,'計(jì)算機(jī)')insert into Students values ('95133910','老十',17,'計(jì)算機(jī)')insert into Students valu

17、es ('95133941','老一',17,'物聯(lián)網(wǎng)')insert into Students values ('95133943','老二',15,'物聯(lián)網(wǎng)') insert into Courses values ('0000','C1',4,60,null)insert into Courses values ('0001','C語(yǔ)言',4,60,null)insert into Courses values ('00

18、02','C+',4,60,null)insert into Courses values ('0003','JAVA',4,60,null)insert into Courses values ('0004','數(shù)據(jù)結(jié)構(gòu)',4,60,null)insert into Courses values ('0005','高數(shù)',4,60,null)insert into Courses values ('0006','離散',4,60,null)i

19、nsert into Courses values ('0007','數(shù)據(jù)庫(kù)',4,60,'0005')insert into Courses values ('0008','算法設(shè)計(jì)',4,60,null)insert into Courses values ('0009','文本智能處理',4,60,null)insert into Courses values ('0010','信息平安',4,60,'0009')insert i

20、nto Courses values ('0011','C2',4,60,'null')insert into SC values ('95133910','0000',70)insert into SC values ('95133910','0001',1)insert into SC values ('20133941','0001',2)insert into SC values ('95133902','0001

21、9;,3)insert into SC values ('20133903','0001',4)insert into SC values ('20133904','0003',3)insert into SC values ('95133905','0001',2)insert into SC values ('20133906','0001',1)insert into SC values ('20133907','0001',2)

22、insert into SC values ('95133908','0001',82)insert into SC values ('20133909','0000',90)insert into SC values ('20133909','0002',3)insert into SC values ('95133901','0011',3)insert into SC values ('95133901','0002',3)ins

23、ert into SC values ('95133903','0000',3)insert into SC values ('95133903','0001',3)insert into SC values ('95133903','0002',3)insert into SC values ('95133903','0003',3)insert into SC values ('95133903','0004',3)insert i

24、nto SC values ('95133903','0005',3)insert into SC values ('95133843','0006',3)insert into SC values ('95133903','0007',3)insert into SC values ('95133903','0008',3)insert into SC values ('95133903','0009',3)insert into S

25、C values ('95133903','0010',3)insert into SC values ('95133903','0011',3)用SQL完成以下查詢1. 求選修了課程的學(xué)生的學(xué)號(hào)，要求：(1) 不使用distinct語(yǔ)句，消除重復(fù)元組，寫(xiě)出其SQL語(yǔ)句Select Sno from SC(2) 使用distinct語(yǔ)句，消除重復(fù)元組，寫(xiě)出其SQL語(yǔ)句select distinct Sno from SC(3) 如果該查詢寫(xiě)成： select Students.Sno from Students, SC where

26、 Students.Sno=SC.Sno 請(qǐng)問(wèn)該查詢結(jié)果和上面哪個(gè)結(jié)果是相同的？和1相同2. 求開(kāi)設(shè)的課程號(hào)、課程名、學(xué)時(shí)和學(xué)分，要求對(duì)查詢結(jié)果的列重新命名。select Cno MyCno ,Cname MyCname,Ctime MyCtime,CCredit MyCredict from Courses3. 求計(jì)算機(jī)系和數(shù)學(xué)系的學(xué)生學(xué)號(hào)、姓名和年齡。select Sno,Sname,Sage from Students where Sdept='計(jì)算機(jī)' or Sdept ='數(shù)學(xué)'4. 求不是數(shù)學(xué)系、計(jì)算機(jī)系的學(xué)生的情況，要求select后寫(xiě)*代替列出所

27、有的列名。select * from Students where Students.Sdept!='數(shù)學(xué)' and Students.Sdept!='計(jì)算機(jī)'5. 求全體學(xué)生的信息，要求按年齡升序排列。select * from Students order by Sage asc6. 求計(jì)算機(jī)系年齡在1820歲之間的學(xué)生姓名和年齡 select Sname,Sage from Students where Sage>=18 and Sage<=20 and Sdept='計(jì)算機(jī)'7. 求姓名是以老開(kāi)頭的計(jì)算機(jī)系學(xué)生。select

28、* from Students where Sname like '老%' and Sdept='計(jì)算機(jī)'8. 求選修了C1課程的學(xué)生的學(xué)號(hào)、姓名、成績(jī)。 select Students.Sno,Students.Sname,SC.Grade from Students,SC,Courses where SC.Sno=Students.Sno and SC.Cno=Courses.Cno and Courses.Cname='C1'9. 求學(xué)號(hào)為95開(kāi)頭的學(xué)生的學(xué)號(hào)和所選修的課程的學(xué)分。 select Students.Sno,Courses.C

29、name,Courses.CCredit from Students,SC,Courses where Students.Sno like'95%' and Students.Sno=SC.Sno and SC.Cno=Courses.Cno10. 求選修C1課程且成績(jī)?yōu)?0分以上的學(xué)生的學(xué)號(hào)、姓名和成績(jī)。 select Students.Sno,Students.Sname,SC.Grade from Students,SC,Courses where Students.Sno=SC.Sno and SC.Cno=Courses.Cno and Courses.Cname=&

30、#39;C1' and SC.Grade>8011. 求計(jì)算機(jī)系每個(gè)學(xué)生的姓名，選修的課程名和成績(jī)。select Students.Sname,Courses.Cname,SC.Grade from Students,SC,Courses where Students.Sdept='計(jì)算機(jī)' and Students.Sno=SC.Sno and SC.Cno=Courses.Cno12. 求每個(gè)學(xué)生的學(xué)號(hào)及平均成績(jī)。select Sno,AVG(Grade) AvgGrade from SC group by Sno13. 求男學(xué)生每一年齡組中超過(guò)1人的年齡組及

31、人數(shù)。select Sage,Count(Sno) countSno from Students group by Sage having Count(Sno)>114. 求每一門課程的間接先行課號(hào)。select C1.Cno,C2.Cpno from Courses C1,Courses C2 where C1.Cpno=C2.Cno15. 求選修了全部課程的學(xué)生的學(xué)號(hào)。 select Sno from Students where not exists (select * from Courses where not exists(select * from SC where Sno

32、=Students.Sno and Cno=Courses.Cno);實(shí)驗(yàn)(三):數(shù)據(jù)庫(kù)的平安性和完整性實(shí)驗(yàn)?zāi)康模毫私夂褪褂闷桨沧酉到y(tǒng)；進(jìn)行完整性定義和檢查。實(shí)驗(yàn)內(nèi)容：具體包括如下三局部。一 熟練掌握SQL(續(xù))1. 修改表Students,在其中參加一個(gè)字段性別sex char(1) alter table Students add sex char(1); 2. 創(chuàng)立索引：在Students表的Sno上創(chuàng)立唯一聚簇索引, 在SC表的Sno, Cno上創(chuàng)立唯一索引. 查詢Students表的內(nèi)容, 記錄的順序有變化嗎? 查詢SC表的內(nèi)容, 記錄的順序有變化嗎? Create unique c

33、lustered index Student_sno on Students(Sno);Create unique index Sno_cno on SC(Sno,Cno);Students表的內(nèi)容, 記錄的順序有變化SC表的內(nèi)容, 記錄的順序沒(méi)有變化3. 創(chuàng)立視圖§ 創(chuàng)立計(jì)算機(jī)系學(xué)生視圖CS_Students，其中包括學(xué)號(hào)、年齡和性別。帶with check opition子句不帶with check opition子句時(shí)各插入一計(jì)算機(jī)系學(xué)生記錄， 通過(guò)視圖查詢插入的記錄，結(jié)果如何？通過(guò)表Students查詢插入的記錄，查看所在系字段上的值？create view CS_stude

34、nts1 as select Sno,Sage,sex from Students where Sdept='CS' with check optionINSERT INTO CS_students1VALUES('20133333',19,'m');create view CS_students2 as select Sno,Sage,sex from Students where Sdept='CS'INSERT INTO CS_students2VALUES('20138888',18,'m'

35、);§ 創(chuàng)立一視圖，表示學(xué)生的平均成績(jī)，其中包括Sno,avgGrade。通過(guò)該視圖插入一學(xué)生姓名和平均成績(jī)，結(jié)果如何？通過(guò)該視圖查找平均成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和平均成績(jī)，結(jié)果又如何？ create view Stuavggrade(sno,avggrade)as select Sno,avg(Grade)from SC group by SC.sno Insert into Stuavggradevalues('20133333',88)select sno,avggradefrom Stuavggradewhere avggrade>=90二 了解和使

36、用平安子系統(tǒng)1、將sql server的一合法帳戶(user60)參加到你的數(shù)據(jù)庫(kù)當(dāng)中來(lái)，并給其授權(quán)，SP_grantdbaccess 'user63'使之：具有創(chuàng)立表的權(quán)限grant Create table to user63;具有對(duì)Students表查詢的權(quán)限grant select on Students to user63;具有對(duì)Studetns表的年齡字段修改的權(quán)限grant update(Sage) on Students to user63;2、用user60/user60進(jìn)入系統(tǒng)，完成權(quán)限驗(yàn)證：在Students表上，實(shí)現(xiàn)select * from Stude

37、ntsselect * from db63.user63.STUDENT在course表上，實(shí)現(xiàn)select * from courseselect * from db63.user63.Courseupdate Studetns set Sage=Sage+1update db63.user63.STUDENT set Sage=Sage+1select * from db63.user63.STUDENTupdate Students set Sdept=CSupdate db63.user63.STUDENT set Sdept='CS'select * from db6

38、3.user63.STUDENT3、你是數(shù)據(jù)庫(kù)的dbo，你來(lái)查看dept表，結(jié)果如何？使用set user命令使自己成為user60后，查看dept表，結(jié)果如何？三 完整性定義和檢查1、創(chuàng)立表Studetns，要求學(xué)生的年齡在16和25歲之間，并且所在系的缺省取值為CSCREATE TABLE Students(Sno CHAR(9) not null,Sname CHAR(20) ,Sdept char(20) default'CS',Sage int check(Sage>=16 and Sage<=25),Ssex char(2);在Studetns表上插入一

39、學(xué)生記錄9921，cccc,MA,23,finsert into Students(Sno,Sname,Sdept,Sage,Ssex)values('9921','cccc','MA',23,'f');在Studetns表上插入一學(xué)生記錄9922，ddd,MA,27,minsert into Students(Sno,Sname,Sdept,Sage,Ssex)values('9922','ddd','MA',27,'m');在Studetns表上插入一學(xué)生記錄99

40、23，eeeee,21,minsert into Students(Sno,Sname,Sage,Ssex)values('9923','eeeee',21,'m');在Studetns表上插入一學(xué)生記錄9922，ddd,MA,23,minsert into Students(Sno,Sname,Sdept,Sage,Ssex)values('9922','ddd','MA',23,'m');2、創(chuàng)立表course，要求課號(hào)作為主鍵create table course(cno ch

41、ar(10) primary key,ctime char(10),cname char(20),cscore char(10);在course表上插入一課程記錄c9,40,cname1,1insert into course(cno,ctime,cname,cscore)values('c9',40,'cname1',1);在course表上插入一課程記錄c9,40,cname2,1insert into course(cno,ctime,cname,cscore)values('c9',40,'cname2',1);在cour

42、se表上插入一課程記錄null,25,canme3,2insert into course(cno,ctime,cname,cscore)values(null,25,'cname3',2);3、修改表Studetns，指定學(xué)號(hào)為主鍵，然后ALTER TABLE Students ADD CONSTRAINT pk_Sno PRIMARY KEY(Sno)插入一記錄9908,shang,CI,20insert into Students(Sno,Sname,Sdept,Sage)values('9908','shang','CI'

43、,20);插入一記錄9908,shang,CI,20insert into Students(Sno,Sname,Sdept,Sage)values('9908','shang','CI',20);插入一記錄,liang,CS,18insert into Students(Sno,Sname,Sdept,Sage)values('','liang','CS',18);插入一記錄,an,CS,19insert into Students(Sno,Sname,Sdept,Sage)values('

44、;','an','CS',19);4、 創(chuàng)立sc表，要求表sc中的學(xué)號(hào)參照表Studetns中的學(xué)號(hào)，sc中的課號(hào)參照course中的課號(hào)。參照約束創(chuàng)立完成后，向這 三個(gè)表插入數(shù)據(jù)。create table SC(Sno char(9),Cno char(10),Grade int,foreign key(Sno) references Students(Sno),foreign key(Cno) references course(cno)先在sc表插入一個(gè)選課記錄，看看有什么情況發(fā)生？insert into SC(Sno,Cno,Grade)valu

45、es('9922','c9',85);先刪除sc中的某個(gè)記錄，看看有什么情況發(fā)生？delete from SC where Sno = '9922'將Studetns表中的學(xué)號(hào)9906改為9907，看看有什么情況發(fā)生？update Students set Sno='9907'where Sno='9906'實(shí)驗(yàn)(四):事務(wù)的并發(fā)控制實(shí)驗(yàn)?zāi)康模和ㄟ^(guò)實(shí)驗(yàn)了解實(shí)際系統(tǒng)中,三級(jí)封鎖協(xié)議的實(shí)現(xiàn)方法和使用方法，體會(huì)加鎖操作、死鎖檢測(cè)與解除。實(shí)驗(yàn)內(nèi)容：具體分如下五局部。 說(shuō)明：1Set transaction isolatio

46、n level read uncommittedread committedrepeatable readserializable分別對(duì)應(yīng)隔離級(jí)0,1,2,3。2要求兩人一組。1、 隔離級(jí)1(缺省)方式下體會(huì)兩事務(wù)的并發(fā)執(zhí)行結(jié)果。具體步驟如下(以u(píng)ser1和user2為例):l user1首先將user2接納為自己數(shù)據(jù)庫(kù)的用戶, 并授權(quán)user2可以查詢學(xué)生表studentsl user1輸入并執(zhí)行以下語(yǔ)句begin transactionupdate table students set age=20+1 where sno=95001l user2輸入并執(zhí)行以下語(yǔ)句begin transaction select * from students where sno=950011) 觀察發(fā)生的現(xiàn)象, user2的語(yǔ)句可以執(zhí)行嗎(是否可以讀到別人沒(méi)有提交的數(shù)據(jù))? 不可以 2) user1輸入并執(zhí)行以下語(yǔ)句commit 觀察發(fā)生的現(xiàn)象, user2的語(yǔ)句執(zhí)行了嗎? 可以 3) 加鎖和解鎖操作的完成者是數(shù)據(jù)庫(kù)用戶嗎? l user2輸入并執(zhí)行以下語(yǔ)句commit不是，是數(shù)據(jù)庫(kù)系統(tǒng)2、 隔離級(jí)1(缺省)方式下體會(huì)兩事務(wù)的并發(fā)執(zhí)行結(jié)果。具體步驟如下(以u(píng)ser1和user2為例):l user1首先將user2接納為自己數(shù)據(jù)庫(kù)的用戶, 并授權(quán)user2可以查詢學(xué)生表st