太原理工大學數(shù)據(jù)庫實驗報告

1、 本科實驗報告課程名稱： 數(shù)據(jù)庫系統(tǒng)原理 實驗項目：交互式SQL、數(shù)據(jù)完整性、用戶鑒別與 數(shù)據(jù)控制實驗地點： 逸夫樓303 專業(yè)班級：軟件工程* 學號：* 學生姓名： * 指導教師： 安建成 2013年 5 月18 日一、實驗目的和要求 熟悉通過 SQL 對數(shù)據(jù)庫進行操作。二、實驗內容和原理 1在 RDBMS 中建立一個學生-課程數(shù)據(jù)庫，進行實驗所要求的各種操作，所有的 SQL 操作均在此建立的新庫里進行。 2根據(jù)以下要求認真進行實驗，記錄所有的實驗用例及執(zhí)行結果。 數(shù)據(jù)定義：基本表的創(chuàng)建、修改及刪除；索引的創(chuàng)建和刪除。 數(shù)據(jù)操作：完成各類查詢操作(單表查詢，連接查詢，嵌套查詢，集合查詢)；完

2、成各類更新操作(插入數(shù)據(jù)，修改數(shù)據(jù)，刪除數(shù)據(jù))。 視圖的操作： 視圖的定義(創(chuàng)建和刪除)，查詢，更新(注意更新的條件)。 3、 主要儀器設備 操作系統(tǒng)：Windows 7。 數(shù)據(jù)庫管理系統(tǒng)：SQL Server2008。四、操作方法與實驗步驟 實驗數(shù)據(jù)記錄 實驗結果(一)數(shù)據(jù)定義： 一基本表的操作 1建立基本表 1)創(chuàng)建學生表 Student,由以下屬性組成：學號 Sno(char 型，長度為 9，主碼)，姓名 Sname(char 型，長度為 20，唯一)，性別 Ssex(char 型，長度為 2)，年齡(smallint),所在系(char 型，長度為 20)。 create table

3、Student (Sno char(9) primary key, Sname char(20) unique, Ssex char(2), Sage smallint, Sdept char(20); 2）創(chuàng)建課程表 Course,由以下屬性組成：課程號 Cno(char 型，主碼，長度為 4)，課程名 Cname(char 型，長度為 40)，先行課 Cpno(char型，長度為 4,外碼)，學分 Ccredit(smallint)。 create table Course (Cno char(4) primary key, Cname char(40), Cpno char(4), Cc

4、redit smallint); 若設置 Cpno 外碼，插入數(shù)據(jù)時會提示違反外碼約束。 3)創(chuàng)建學生選課表 SC， 由以下屬性組成： 學號 Sno(char 型， 長度為 9)，課程號 Cno(char 型，長度為 4)，成績 Grade(smallint),其中 Sno 和Cno 構成主碼。 create table sc (Sno char(9), Cno char(4), Grade smallint, primary key(Sno,Cno), foreign key (Sno) references student(Sno), foreign key (Cno) reference

5、s course(Cno)； 2修改基本表： 1)向 Student 表增加“入學時間列”，其數(shù)據(jù)類型為日期型。 alter table Student add S_entrance date; 2)將年齡的數(shù)據(jù)類型由字符型(假設原來的數(shù)據(jù)類型是字符型)改為整數(shù)。 alter table Student alter column Sage int; 3)增加課程名稱必須取唯一值的約束條件。 alter table Course add unique(Cname); 注意：修改表結構后，再次查看表，注意觀察變化。 3刪除基本表： 1)在所有的操作結束后刪除 Student 表。 drop tab

6、le Student; 2)在所有的操作結束后刪除 Course 表。 drop table Course; 3)在所有的操作結束后刪除 SC 表。 drop table SC; 思考：刪除表時，不同的刪除順序會有不同結果，為什么？注意錯誤提示。 二索引操作 1建立索引 1)為學生課程數(shù)據(jù)庫中的 Student，Course，SC 3 個表建立索引。其中 Student 表按學號升序建唯一索引， Course 表按課程號升序建唯一索引，SC 表按學號升序和課程號降序建唯一索引。 create unique index Stusno on Student(Sno); create unique

7、index Coucno on Course(Cno); create unique index SCno on SC(Sno ASC,Cno DESC); 2刪除索引 1)刪除 Student 表的 Stusname 索引。 drop index student.Stusname; 已建立已刪除 (二)數(shù)據(jù)操作 一更新操作 1,插入數(shù)據(jù) 1)在 Student 表中插入下列數(shù)據(jù)： ，李勇，男，20，CS ，劉晨，女，19，CS ，王敏。女，18，MA ，張立，男，19，IS insert into student(Sno,Sname,Ssex,Sage,Sdept)values(,李勇,男,

8、20,CS); insert into student(Sno,Sname,Ssex,Sage,Sdept)values(,劉晨,女,19,CS); insert into student(Sno,Sname,Ssex,Sage,Sdept)values(,王敏,女,18,MA); insert into student(Sno,Sname,Ssex,Sage,Sdept)values(,張立,男,19,IS) 2)在 Course 表中插入以下數(shù)據(jù)： 1,數(shù)據(jù)庫,5,4 2,數(shù)學,null，2 6,數(shù)據(jù)處理,null，2 4,操作系統(tǒng),6,3 7,PASCAL 語言,6,4 5,數(shù)據(jù)結構,7

9、,4 1,數(shù)據(jù)庫,5,4 3,信息系統(tǒng),1,4 insert into course(Cno,Cname,Cpno,Ccredit)values(1,數(shù)據(jù)庫,5,4); insert into course(Cno,Cname,Ccredit)values(2,數(shù)學,2); insert into course(Cno,Cname,Ccredit)values(6,數(shù)據(jù)處理,2); insert into course(Cno,Cname,Cpno,Ccredit)values(4,操作系統(tǒng),6,3); insert into course(Cno,Cname,Cpno,Ccredit)val

10、ues(7,PASCAL 語言,6,4); insert into course(Cno,Cname,Cpno,Ccredit)values(5,數(shù)據(jù)結構,7,4); insert into course(Cno,Cname,Cpno,Ccredit)values(1,數(shù)據(jù)庫,5,4); insert into course(Cno,Cname,Cpno,Ccredit)values(3,信息系統(tǒng),1,4); 3) 在 SC 表中插入以下數(shù)據(jù)： ,1,92 ,2,85 ,3,88 ,2,90 ,3,80 insert into sc (Sno,Cno,Grade) values (,1,92)

11、; insert into sc (Sno,Cno,Grade) values (,2,85; insert into sc (Sno,Cno,Grade) values (,3,88); insert into sc (Sno,Cno,Grade) values (,2,90); insert into sc (Sno,Cno,Grade) values (,3,80); 4)將一個新學生元祖(學號：；姓名：陳冬；性別：男；所在系：IS；年齡：18 歲)插入到 Student 表中。 insert into student (Sno,Sname,Ssex,Sdept,Sage) values

12、 (,陳冬,男,IS,18); （已做）5)將學生張成民的信息插入到 Student 表中。 insert into student values(,張成民,男,18,CS); （已做）6)插入一條選課記錄：(，1)。 insert into sc(Sno,Cno) values(,1); （已做）7)對每一個系，求學生的平均年齡，并把結果存入數(shù)據(jù)庫。 create table Dept_age (Sdept char(15), Avg_age smallint); insert into Dept_age (Sdept,Avg_age) select Sdept,avg(Sage)from

13、student group by Sdept; 2修改數(shù)據(jù) 1)將學生 的年齡改為 22 歲。 update student set Sage=22 where Sno=; 2)將所有學生的年齡增加一歲。 update student set Sage=Sage+1; 3)將計算機科學系全體學生的成績置零。 update sc set Grade=0 where CS=(select Sdept from student where student.Sno=sc.Sno); 3刪除數(shù)據(jù) 1)刪除學號為 的學生記錄。 delete from student where Sno=; 2)刪除所有學

14、生的選課記錄。 delete from sc; 3)刪除計算機科學系所有學生的選課記錄。 delete from sc where CS=(select Sdept from student where student.Sno=SC.Sno ); 2 查詢操作1單表查詢 2) 查詢全體學生的姓名、學號、所在系。select sno,sname,sdeptfrom Student;5) 查詢全體學生的姓名、出生年份和所在院系，要求用小寫字母表示所有系名。select sname,2013-sage birth,lower(sdept) sdeptfrom Student;10)查詢年齡在 20-

15、23 歲(包括 20 歲和 23 歲)之間的學生的姓名、系別和年齡。select sname,sdept,sagefrom Studentwhere sage between 20 and 2312)查詢計算機科學系(CS)、數(shù)學系(MA)、和信息系(IS)學生的姓名和性別。select sname,ssexfrom Studentwhere Sdept in(cs,ma,is)15) 查詢所有姓劉的學生的姓名、學號和性別。select sname,Sno,ssexfrom Studentwhere sname like 劉%19) 查詢 DB_Design 課程的課程號和學分。select

16、Cno,Ccredit from course where Cname like DB_Design escape ; 23) 查詢計算機科學系年齡在 20 歲以下的學生姓名。select snamefrom studentwhere sdept=cs and sage90;6) 查詢每個學生的學號、姓名、選修的課程名及成績。select student.Sno,Sname,Cname,Grade from student,sc,course where student.Sno=sc.Sno and sc.Cno=course.Cno;3嵌套查詢 1)查詢與“劉晨”在同一個系學習的學生。sel

17、ect Sno,Sname,Sdeptfrom Studentwhere Sdept in(select Sdeptfrom Studentwhere Sname=劉晨);2)查詢選修了課程名為“信息系統(tǒng)”的學生學號和姓名。select Sno,Sname from student where Sno in(select Sno from sc where Cno in(select Cno from course where Cname=信息系統(tǒng)); 3) 找出每個學生超過他選修課程平均成績的課程號。select cno from sc xwhere grade (select AVG(Gr

18、ade) from sc y where x.sno=y.sno group by Sno);4) 查詢其他系中比計算機科學系某一學生年齡小的學生姓名和年齡。select sname,Sagefrom Studentwhere Sage = any (select sage from student where Sdept=cs) and Sdeptcs;5) 查詢其他系中比計算機科學系所有學生年齡小的學生姓名和年齡。select sname,Sagefrom Studentwhere Sage = all (select sage from student where Sdept=cs) a

19、nd Sdeptcs;6)查詢選修了 1 號課程的學生姓名。select Sname from student where exists(select * from sc where Sno=student.Sno and Cno=1);7) 查詢沒有選修 1 號課程的學生姓名。select Sname from student where not exists(select * from sc where Sno=student.Sno and Cno=1);8) 查詢選修了全部課程的學生姓名。select Sname from student where not exists (selec

20、t * from Course where not exists (select * from sc where Sno=student.Sno and Cno=course.Cno);9) 查詢至少選修了學生 選修的全部課程的學生號碼。 select distinct Sno from sc scx where not exists (select * from sc scy where scy.Sno= and not exists (select * from sc scz where scz.Sno=scx.Sno and scz.Cno=scx.Cno); 4集合查詢 1)查詢計算機

21、科學系的學生及年齡不大于 19 歲的學生。select * from student where Sdept=CS union select * from student where Sage=19;2) 查詢選修了課程 1 或課程 2 的學生。select Sno from sc where Cno=1 union select Sno from sc where Cno=2;3) 查詢計算機科學系的學生與年齡不大于 19 歲的學生的交集。select * from student where Sdept=CS intersect select * from student where Sa

22、ge=19;4) 查詢既選修了課程 1 又選修了課程 2 的學生。select Sno from sc where Cno=1 intersect select Sno from sc where Cno=2;5) 查詢計算機科學系的學生與年齡不大于 19 歲的學生的差集。select * from student where Sdept=CS except select * from student where Sage=90;5)定義一個反應學生出生年份的視圖。create view BT_S(Sno,Sname,Sbirth) as select Sno,Sname,2004-Sage

23、from student;6) 將學生的學號及他的平均成績定義為一個視圖。create view S_G(Sno,Gavg) as select Sno,avg(Grade) from sc group by Sno; 7) 將 Student 表中所有女生記錄定義為一個視圖。 create view F_Student(F_sno,name,sex,age,dept) as select * from student where Ssex=女;2刪除視圖： 1)刪除視圖 BT_S: 3查詢視圖： 1)在信息系學生的視圖中找出年齡小于 20 歲的學生。select Sno,Sage from

24、IS_Student where Sage=80;4更新視圖： 1)將信息系學生視圖 IS_Student 中學號為 的學生姓名改為“劉辰”。 update IS_Student set Sname=劉辰 where Sno=;原視圖2)向信息系學生視圖 IS_Student 中插入一個新的學生記錄，其中學號為 ，姓名為趙新，年齡為 20 歲。 insert into IS_Student values(,趙新,20,IS);3) 刪除信息系學生視圖 IS_Student 中學號為 的記錄。delete from IS_Student where Sno=; 一、實驗目的和要求（1）了解 SQ

25、L Serer 數(shù)據(jù)庫系統(tǒng)中數(shù)據(jù)完整性控制的基本方法 （2）熟練掌握常用 CREATE 或 ALTER 在創(chuàng)建或修改表時設置約束 （3）了解觸發(fā)器的機制和使用 （4）驗證數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)完整性控制二、實驗內容和原理 結合 ST 數(shù)據(jù)庫中的各個表，設置相關的約束，要求包括主鍵約束、外鍵約束、唯一約束、檢查約束、非空約束等，掌握各約束的定義方法。 設置一個觸發(fā)器，實現(xiàn)學生選課總學分的完整性控制，了解觸發(fā)器的工作機制。 設計一些示例數(shù)據(jù)，驗證完整性檢查機制。 要求包括如下方面的內容： 3.1 使用 SQL 語句設置約束 使用 CREATE 或 ALTER 語句完成如下的操作，包括： 1 設置各表的主鍵

26、約束 2 設置相關表的外鍵 3. 設置相關屬性的非空約束、默認值約束、唯一約束 4. 設置相關屬性的 CHECK 約束 3.2 使用觸發(fā)器 創(chuàng)建一個觸發(fā)器，實現(xiàn)如下的完整性約束： 當向 SC 表中插入一行數(shù)據(jù)時，自動將學分累加到總學分中。 l 記錄修改學分的操作。 3.4 檢查約束和觸發(fā)器 分別向相關表插入若干條記錄，檢查你設置的完整性約束是否有效： 1 插入若干條包含正確數(shù)據(jù)的記錄，檢查插入情況 2 分別針對設置的各個約束，插入違反約束的數(shù)據(jù)，檢查操作能否進行 3 向 SC 表插入若干行數(shù)據(jù)，檢查觸發(fā)器能否實現(xiàn)其數(shù)據(jù)一致性功能。三、主要儀器設備使用 SQL Server 數(shù)據(jù)庫管理系統(tǒng)提供的

27、 SSMS 和查詢編輯器4、 實驗內容 實驗結果與分析創(chuàng)建學生選課數(shù)據(jù)庫 TEST，包括三個基本表，其中 Student 表保存學生基本信息，Course表保存課程信息，SC 表保存學生選課信息，其結構如下表：CREATE TABLE Student ( Sno CHAR(8) PRIMARY KEY, Sname CHAR(8) NOT NULL, Ssex CHAR(2) CHECK( Ssex in (男,女), Sage SMALLINT, Sdept CHAR(20), Sclass CHAR(4) NOT NULL, Stotal smallint DEFAULT 0 ); CRE

28、ATE TABLE Course ( Cno CHAR(4) CONSTRAINT FK_Course PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4) , Ccredit SMALLINT ); CREATE TABLE SC ( Sno CHAR(8) FOREIGN KEY (Sno) REFERENCES Student(Sno), Cno CHAR(4), Grade SMALLINT CONSTRAINT SC_CHECK CHECK(Grade 0 AND Grade=0 AND Grade=100)l 掌握如何修改約束 l 理解命名約束的優(yōu)點二

29、、檢查完整性約束 通過修改數(shù)據(jù)庫中的數(shù)據(jù)檢查完整性約束條件的作用。 1檢查主鍵約束 （1）執(zhí)行下面的語句修改Student表，觀察語句能否正確運行，解釋為什么？INSERT INTO Student VALUES(,李斌,男,20,CS,1001,0)INSERT INTO Student VALUES(,李斌,男,20,CS,1001,0)消息2627，級別14，狀態(tài)1，第1 行違反了PRIMARY KEY 約束PK_Student_DDDF64467F60ED59。不能在對象dbo.Student 中插入重復鍵。語句已終止。（因為sno為主鍵不能有重復，所以無法運行）UPDATE Stud

30、ent SET Sno= WHERE Sname = 張立消息2627，級別14，狀態(tài)1，第1 行違反了PRIMARY KEY 約束PK_Student_DDDF64467F60ED59。不能在對象dbo.Student 中插入重復鍵。語句已終止。（張立要修改的學跟王敏的學號重復了，由于學號是學生表的主鍵，不可以重復，所以無法運行）（2）執(zhí)行下面的語句修改SC表，觀察語句能否正確運行，解釋為什么？ INSERT INTO SC VALUES(,1,78)INSERT INTO SC VALUES(,null,78)2檢查唯一約束 執(zhí)行下面的語句修改Course表，觀察語句能否正確運行，解釋為什

31、么？ INSERT INTO Course VALUES(8,JAVA,7, 3)INSERT INTO Course VALUES(9,數(shù)據(jù)結構,7, 3) 3檢查默認值、允許空值列 運行如下的語句： INSERT INTO Student(Sno,Sname,Ssex,Sclass) VALUES(,張盛,男,1008) 觀察插入數(shù)據(jù)行的數(shù)值SELECT * FROM Student WHere Sno= 4檢查非空約束 下面的語句包含空值，檢查運行結果，解釋為什么？ INSERT INTO Student(Sno,Sname,Ssex,Sclass) VALUES(,張盛,男,NULL)

32、 INSERT INTO Student(Sno,Sname,Ssex) VALUES(,張盛,男)5檢查 CHECK 約束 執(zhí)行下面的語句，解釋其運行結果。 INSERT INTO SC VALUES(,4,95) INSERT INTO SC VALUES(,4,102) INSERT INTO Student(Sno,Sname,Ssex,Sclass) VALUES(,張盛,男,1008) INSERT INTO Student(Sno,Sname,Ssex,Sclass) VALUES(,張盛,1008)6檢查外鍵約束 （1）執(zhí)行下面的語句檢查外鍵約束的作用 INSERT INTO

33、SC VALUES(,1,95) INSERT INTO SC VALUES(,10,95) UPDATE SC SET Cno = 10 Where Cno=1 UPDATE Course SET Cno = 10 Where Cno=3 （2）執(zhí)行下面的語句檢查對被引用表的約束 Student表：DELETE Student WHERE Sno= DELETE Student WHERE Sno= UPDATE Course SET Cno = 10 WHERE Cname = 數(shù)據(jù)庫系統(tǒng)原理 三、觸發(fā)器的定義及使用 1定義觸發(fā)器 （1）定義一個觸發(fā)器，實現(xiàn)有關學分的完整性約束：當向SC表

34、插入一行選課記錄時，自動將該課程的學分累加到該學生的總學分中。 CREATE TRIGGER tr_INSERT ON SC FOR INSERT AS -聲明變量DECLARE sno char(8) DECLARE cridit int DECLARE cno char(4) -提取插入的數(shù)據(jù)SELECT sno=Sno,cno=Cno FROM inserted -提取學生的總學分SELECT cridit = Ccredit FROM SC join Course ON (SC.Cno = Course.Cno) WHERE SC.Cno = cno -更新總學分UPDATE Stud

35、ent SET Stotal = Stotal + cridit WHERE Sno = sno GO （2）定義一個觸發(fā)器，實現(xiàn)對SC表的操作登記：當用戶向SC表插入或修改時，記錄該操作到數(shù)據(jù)庫中。 l 創(chuàng)建日志登記表:CREATE TABLE LOG_TABLE ( username char(10), -操作人員date datetime, -修改時間Sno char(8) , -學生學號Cno char(4) -課程號) l 創(chuàng)建日志登記觸發(fā)器: CREATE TRIGGER tr_UPDATE ON SC FOR INSERT,UPDATE AS DECLARE sno char(8

36、) DECLARE cno char(4) DECLARE new smallint SELECT sno=Sno,cno=Cno FROM inserted INSERT INTO LOG_TABLE VALUES(CURRENT_USER,getdate(),Sno,Cno); GO （3） 執(zhí)行插入操作，觸發(fā)觸發(fā)器：INSERT INTO SC VALUES(,6,95)（4） 驗證觸發(fā)器是否觸發(fā)select * from student select * from LOG_TABLE1、實驗目的 （1）了解 SQL Serer 數(shù)據(jù)庫系統(tǒng)中數(shù)據(jù)訪問控制的基本方法 （2）了解使用 SSM

37、S 如何給用戶授權 （3）熟練掌握常用 GRANT 和 REVOKE 進行權限控制 （4）驗證數(shù)據(jù)庫系統(tǒng)的訪問控制 2、實驗平臺 使用 SQL Server 數(shù)據(jù)庫管理系統(tǒng)提供的 SSMS。 3 實驗內容及要求 實驗應包括如下方面的內容： l 創(chuàng)建新的數(shù)據(jù)庫用戶 l 對上一實驗建立的表進行權限設置 l 檢查數(shù)據(jù)庫用戶的權限 l 撤銷用戶權限 實驗報告 要求寫出實驗的基本過程。 參考示例： 在 SQL Server 中， 對用戶訪問數(shù)據(jù)庫的權限管理可以使用兩種方式， 一種是使用 SSMS的圖形界面進行管理，一種是使用 SQL 語句。 3.1 使用 SSMS 的圖形界面創(chuàng)建用戶并授權 創(chuàng)建一個數(shù)據(jù)

38、庫用戶：張明，授予他可以查詢 Student 表的權限。1 使用 SSMS 的圖形界面創(chuàng)建登錄名 在快捷菜單中選擇“新建登錄名”：進入“登錄名”窗口，輸入登錄信息：張明 2 使用 SSMS 的圖形界面創(chuàng)建數(shù)據(jù)庫用戶 選擇“ST”數(shù)據(jù)庫，在快捷菜單中選擇“新建登錄名”：3 使用 SSMS 的圖形界面進行授權，設置用戶對對象的訪問權限 展開 ST 數(shù)據(jù)庫的“安全性”下的“用戶”，選擇“張明 db”。 （） 打開用戶屬性窗口：3.2 使用語句創(chuàng)建用戶并授權 在 ST 或 TEST 數(shù)據(jù)庫中，使用 SQL 語句創(chuàng)建兩個數(shù)據(jù)庫用戶，對數(shù)據(jù)庫中的 Student、SC、和 Course 表，分別對其授予不

39、同的訪問權限，如下表。 表 3.1 授予用戶權限 1 創(chuàng)建登錄名 用戶要訪問數(shù)據(jù)庫，必須首先登錄到服務器上。因此，首先需要為用戶創(chuàng)建登錄名。以系統(tǒng)管理員賬戶登錄，進入數(shù)據(jù)庫服務器，執(zhí)行以下的操作。 （1） 分別創(chuàng)建兩個登錄名王明和李剛： CREATE LOGIN 王明 WITH password= CREATE LOGIN 李剛 WITH password=2 創(chuàng)建數(shù)據(jù)庫用戶 用戶登錄服務器后， 要進入某一數(shù)據(jù)庫訪問， 需要為登錄用戶在該數(shù)據(jù)庫中建立一個數(shù)據(jù)庫用戶名。 對創(chuàng)建的兩個登錄名，在 TEST 數(shù)據(jù)庫分別為其創(chuàng)建兩個數(shù)據(jù)庫用戶： （1） 切換到 TEST 數(shù)據(jù)： USE TEST （2） 創(chuàng)建登錄名在 TEST 數(shù)據(jù)庫中的用戶： CREATE USER 王明db FOR LOGIN 王明 CREATE USER 李剛 FOR LOGIN