1、 本科實驗報告課程名稱： 數(shù)據(jù)庫系統(tǒng)原理B 實驗項目： 交互式SQL、數(shù)據(jù)完整性 實驗地點： 專業(yè)班級： 學號： 學生姓名： 指導(dǎo)教師： 2015年 6 月 24 日實驗二：交互式 SQL語句的使用一、實驗?zāi)康模?）掌握數(shù)據(jù)庫對象的操作過程,包括創(chuàng)建、修改、刪除。（2）熟悉表的各種操作，包括插入、修改、刪除、查詢（3）熟練掌握常用 SQL語句的基本語法二、實驗內(nèi)容和要求選擇如下一個應(yīng)用背景之一：學生選課系統(tǒng)其它你熟悉的應(yīng)用（1）建立一個數(shù)據(jù)庫和相關(guān)的表、索引、視圖等數(shù)據(jù)庫對象，練習對表、索引和視圖的各種操作。（2）要求認真進行實驗，記錄各實驗用例及執(zhí)行結(jié)果。（3）深入了解各個操作的功能。實驗要

2、求包括如下方面的內(nèi)容：3.1 數(shù)據(jù)定義1 基本表的創(chuàng)建、修改及刪除2 索引的創(chuàng)建3 視圖的創(chuàng)建3.2 數(shù)據(jù)操作完成各類更新操作包括：1 插入數(shù)據(jù)2 修改數(shù)據(jù)3.刪除數(shù)據(jù)3.3 數(shù)據(jù)查詢操作完成各類查詢操作1 單表查詢2 分組統(tǒng)計3. 連接查詢4. 嵌套查詢5. 集合查詢3.4 數(shù)據(jù)操作1 創(chuàng)建視圖2 視圖查詢?nèi)?、主要儀器設(shè)備操作系統(tǒng)：Windows 7 Windows 2003 Server 數(shù)據(jù)庫管理系統(tǒng)： SQL Server。四、操作方法與實驗步驟1.創(chuàng)建，修改及刪除基本表（1）創(chuàng)建 Student表CREATE TABLE Student( Sno CHAR(8) PRIMARY KE

3、Y, Sname CHAR(8) , Ssex CHAR(2) NOT NULL, Sage INT, Sdept CHAR(20);（2）創(chuàng)建 Course表CREATE TABLE Course( Cno CHAR(4) PRIMARY KEY, Cname CHAR(40) NOT NULL, CpnoC HAR(4), Ccredit SMALLINT,);（3）創(chuàng)建 SC表Create table SC(Sno char(8), Cno char(4), Grade smallint,Primary key(Sno,Cno),Foreign key(Sno)references St

4、udent(Sno),Foreign key(Cno)references Course(Cno); （4）創(chuàng)建員工表EmployeeCREATE TABLE Employee(編號 CHAR(8) PRIMARY KEY,姓名 VARCHAR(8) not null部門 CHR（40）,工資 numeric(8,2),生日 datetime，職稱 char(20),);指出該語句中的錯誤并改正后執(zhí)行。（5）檢查表是否創(chuàng)建成SELECT * FROM StudentSELECT * FROM CourseSELECT * FROM SCSELECT * FROM Employee修改表結(jié)構(gòu)及約

5、束增加班級列ALTER TABLE Student ADD Sclass char(4)修改年齡列ALTER TABLE Student ALTER COLUMN Sage smallint增加約束ALTER TABLE Course ADD UNIQUE(Cname)（7）刪除表DROP TABLE Employee2創(chuàng)建索引（1）為 Course表按課程名稱創(chuàng)建索引CREATE INDEX iCname On Course(Cname)（2）為 Student表按學生姓名創(chuàng)建唯一索引CREATE UNIQUE INDEX iSname ON Student(Sname)（3）為 SC表按學

6、號和課程號創(chuàng)建聚集索引CREATE CLUSTERED INDEX iSnoCno On SC(Sno,Cno desc)（4）為 Course表按課程號創(chuàng)建唯一索引CREATE UNIQUE INDEX iCno ON Course(Cno)3創(chuàng)建視圖建立信息系學生的視圖：CREATE VIEW IS_StudentASSELECT Sno,Sname,Sage FROM StudentWHERE Sdept= IS3.2數(shù)據(jù)操作插入數(shù)據(jù)(1)插入到Student表INSERT INTO Student VALUES(20100001,李勇,男,20,CS,1001)INSERT INTO

7、Student VALUES(20100002,劉晨,女,19,CS,1001)INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,Sclass) VALUES(20100021,王敏,女,18,MA,1002)INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,Sclass) VALUES(20100031,張立,男,19,IS,1003)INSERT INTO Student(Sno,Sname,Ssex,Sclass) VALUES(20100003,劉洋,女,1001)INSERT INTO Student

8、(Sno,Sname,Ssex,Sage,Sdept,sclass) VALUES(20100010,趙斌,男,19,IS,1005)INSERT INTO Student VALUES(20100022,19,張明明,男,CS,1002)插入到Course表insert into Course(Cno,Cname,Cpno,Ccredit)values(1,數(shù)據(jù)庫系統(tǒng)原理,5,4) insert into Course(Cno,Cname,Cpno,Ccredit)values(2,高等數(shù)學,null,2)insert into Course(Cno,Cname,Cpno,Ccredit)v

9、alues(3,管理信息系統(tǒng),1,4)insert into Course(Cno,Cname,Cpno,Ccredit)values(4,操作系統(tǒng)原理,6,3)insert into Course(Cno,Cname,Cpno,Ccredit)values(5,數(shù)據(jù)結(jié)構(gòu),7,4)insert into Course(Cno,Cname,Cpno,Ccredit)values(6,數(shù)據(jù)處理,null,2)insert into Course(Cno,Cname,Cpno,Ccredit)values(7,C語言,null,4)插入到Sc表insert into Sc values(201000

10、01,1,92)insert into Sc values(20100001,2,85)insert into Sc(Sno,Cno)values(20100003,1)insert into Sc(Sno,Cno,Grade)values(20100010,3,null)insert into Sc values(20100001,3,88)insert into Sc values(20100002,1,90)insert into Sc values(20100002,2,80)多行插入到表中create table cs_Student( 學號 char(8), 姓名 char(8),

11、 年齡 smallint);insert into cs_Student select Sno,Sname,Sagefrom Student where Sdept=CS;檢查插入到表中的數(shù)據(jù)SELECT * FROM StudentSELECT * FROM CourseSELECT * FROM SC修改數(shù)據(jù)（1）將學生 20100001的年齡改為22歲。UPDATE student SET Sage = 22 WHERE Sno=20100001;（2）將所有學生的年齡增一歲。UPDATE Student SET Sage = Sage +1（3）填寫趙斌同學的管理信息系統(tǒng)課程的成績UP

12、DATE SC SET Grade = 85WHERE Sno=20100010 AND Cno=3（4）將計算機科學系全體學生的成績加5分UPDATE sc SET Grade=Grade + 5WHERE CS=(select Sdept from student where student.Sno=sc.Sno);（5）請自己完成如下操作將劉晨同學的2號課程成績修改為80update SC set Grade=80 where Sno=20100002AND Cno=2將“20100021”同學的學號修改為“20100025”update Student set Sno=20100025

13、 where Sno=20100021;（6）檢查數(shù)據(jù)是否修改3.刪除數(shù)據(jù)（1）刪除學號為 201000022的學生記錄DELETE FROM Student WHERE Sno=20100022（2）刪除學號 20100001學生的 1號課程選課記錄將選課信息復(fù)制到一個臨時表tmpSC中：SELECT * INTO tmpSC FROM SC在 tmpSC中執(zhí)行刪除操作：DELETE FROM tmpSC WHERE Sno=20100001 and Cno=1（3）刪除臨時表中 20100002學生的全部選課記錄delete from tmpSC where Sno=20100002;（4

14、）刪除計算機科學系所有學生的選課記錄DELETE FROM tmpSC WHERE CS=(select Sdept from student where student.Sno=tmpSC.Sno );（5）刪除全部選課記錄DELETE FROM tmpSC檢查數(shù)據(jù)是否刪除3.3 數(shù)據(jù)查詢操作完成如下查詢操作：1單表查詢（1）按指定目標列查詢查詢學生的詳細記錄：SELECT* FROM Student;查詢學生的學號、姓名和年齡SELECT Sno,Sname,Sage FROM Student;（2）目標列包含表達式的查詢查詢?nèi)w學生的姓名、出生年份和所在系，要求用小寫字母表示所有系名。S

15、ELECT Sname,Year of Birth: ,2004-Sage,LOWER(Sdept) FROM Student;（3）查詢結(jié)果集中修改列名稱查詢?nèi)w學生的姓名、出生年份和所在系，要求用小寫字母表示所有系名。SELECT Sname, Year of Birth: as BIRTH, 2000-Sage BIRTHDAY, DEPARTMENT = LOWER(Sdept)FROM Student;（4）取消重復(fù)行查詢選修了課程的學生學號：比較ALL和DISTINCT的區(qū)別SELECT Sno FROM SC;SELECT DISTINCT Sno FROM SC;（5）簡單條件

16、查詢查詢計算機科學系全體學生的名單SELECT Sname FROM Student WHERE Sdept=CS;（6）按范圍查詢查詢年齡在2023歲之間的學生的姓名、系SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23（7）查詢屬性值屬于指定集合的行查詢信息系（IS）、數(shù)學系（MA）和計算機科學系（CS）學生的姓名SELECT Sname,Ssex FROM Student WHERE Sdept IN (IS,MA,CS);（8）模糊查詢查詢所有姓劉學生的姓名、學號SELECT Sname,Sno,Ssex

17、FROM Student WHERE Sname LIKE 劉%（9）查詢空值查詢?nèi)鄙俪煽兊膶W生的學號和相應(yīng)的課程號SELECT Sno,Cno FROM sc WHERE Grade is null;（10）多重條件查詢查詢計算機科學系年齡在歲以下的學生姓名SELECT Sname FROM student WHERE Sdept=CS and Sage23; 查詢年齡是17、18、20、23歲同學的學號、姓名、年齡和所在系select Sno,Sname,Sage,Sdept from Student where Sage in(17,18,20,23);查詢年齡不在2124歲之間的學生的

18、姓名、系別和年齡select Sname,Sdept,Sage from Student where Sage not between 21 and 24;2分組統(tǒng)計（1）聚集函數(shù)的使用查詢學生總?cè)藬?shù)SELECT COUNT(*) FROM Student;查詢選修了課程的學生人數(shù)SELECT COUNT(DISTINCT Sno) FROM SC查詢最高分SELECT MAX(Grade) FROM SC（2）聚集函數(shù)作用于部分行統(tǒng)計2號課程的總分、均分SELECT SUM(grade)總分,AVG(grade)均分,MAX(grade)最高分FROM sc WHERE Cno=2（3）分組

19、統(tǒng)計統(tǒng)計各門課程的選課人數(shù)、均分和最高分select cno課程號,count(*)人數(shù),AVG(grade)均分,MAX(grade)最高分from sc group by Cno統(tǒng)計均分大于90的課程select cno課程號,count(*)人數(shù),AVG(grade)均分,MAX(grade)最高分from sc group by Cnohaving AVG(grade) 90（4）完成下面的查詢統(tǒng)計每個同學的學號、選課數(shù)、平均成績和最高成績select Sno 學號,COUNT(Cno)選課數(shù),AVG(Grade) 平均成績,MAX(Grade) 最高成績from SC group b

20、y Sno統(tǒng)計每個班的每門課的選課人數(shù)、平均成績和最高成績select Student.Sclass 班級, SC.Cno 課程號,COUNT(*) 選課人數(shù),AVG(SC.Grade) 平均成績,MAX(SC.Grade) 最高成績from SC,Studentwhere SC.Sno=Student.Snogroup by Student.Sclass,SC.Cno3.連接查詢（1）在 WHERE中指定連接條件查詢每個參加選課的學生信息及其選修課程的情況SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student,SC WH

21、ERE Student.Sno = SC.Sno查詢每一門課的間接先修課SELECT * FROM course first,course secondWHERE first.Cpno=second.Cno;SELECT first.Cno,second.Cpno FROM course first,course secondWHERE first.Cpno=second.Cno;（2）在 FROM中指定連接條件查詢每個參加選課的學生信息及其選修課程的情況SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade From Student JOIN S

22、C ON (Student.Sno=SC.Sno)（3）使用外連接查詢查詢每個學生信息及其選修課程的情況SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno)（4）復(fù)合條件連接查詢查詢選修號課程且成績在分以上的所有學生SELECT Student.Sno, SnameFROM Student join SC ON (Student.Sno = SC.Sno) /*連接條件*/WHERE SC.Cno= 2 AND SC.Grade 90;

23、 /*過濾條件*/（5）多表查詢查詢每個學生的學號、姓名、選修的課程名及成績SELECT Student.Sno,Sname,Cname,GradeFROM Student,SC,CourseWHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;完成下列查詢查詢選修了 2號課程的同學的學號和姓名select Student.Sno,Snamefrom Student,SCwhere Student.Sno=SC.Sno and SC.Cno=2查詢各門課程的課程號、課程名稱以及選課學生的學號select SC.Cno 課程號,Course.Cna

24、me 課程名稱,SC.Sno 學號from SC,Coursewhere SC.Cno=Course.Cno查詢選修了數(shù)據(jù)庫系統(tǒng)原理課程的同學的學號和姓名和成績select Student.Sno,Sname,Gradefrom Student,Course,SCwhere Student.Sno=SC.Sno and Course.Cno=SC.Cno and Cname=數(shù)據(jù)庫系統(tǒng)原理;4.嵌套查詢（1）由 In引出的子查詢查詢與“劉晨”在同一個系學習的學生SELECT Sno,Sname,Sdept FROM StudentWHERE Sdept IN ( SELECT Sdept FR

25、OM Student WHERE Sname= 劉晨);（2）由比較運算符引出的子查詢找出每個學生超過他選修課程平均成績的課程號。SELECT Sno, Cno FROM SC xWHERE Grade = ( SELECT AVG(Grade) FROM SC yWHERE y.Sno=x.Sno);（3）帶修飾符的比較運算符引出的子查詢查詢其他系中比計算機科學系所有學生年齡都小的學生姓名及年齡。SELECT Sname,Sage FROM StudentWHERE Sage ALL ( SELECT Sage FROM Student WHERE Sdept= CS)AND Sdept C

26、S ;（4）由 EXISTS引出的子查詢查詢所有選修了1號課程的學生姓名SELECT Sname FROM StudentWHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= 1)5.集合查詢（1）集合并查詢計算機科學系的學生及年齡不大于19歲的學生SELECT * FROM Student WHERE Sdept= CSUNIONSELECT * FROM Student WHERE Sage=19（2）集合交查詢計算機科學系且年齡不大于 19歲的學生SELECT * FROM Student WHERE Sdept=CS

27、INTERSECTSELECT * FROM Student WHERE Sage=19（3）集合差查詢計算機科學系且年齡大于19歲的學生SELECT * FROM Student WHERE Sdept=CSEXCEPTSELECT * FROM Student WHERE Sage = S_G.Gavg（3）查詢每個學生的學號、姓名、選修的課程名及成績SELECT Sno,Sname,Cname,GradeFROM XK_VIEW（4）比較使用視圖查詢和直接從基表查詢的優(yōu)點五、實驗數(shù)據(jù)記錄和處理檢查數(shù)據(jù)是否被修改：檢查數(shù)據(jù)是否刪除：單表查詢：查詢學生基本信息，結(jié)果集屬性名使用 查詢信息系且

28、年齡大于23歲同學的學號漢字 無結(jié)果查詢年齡是17、18、20、23歲同學的 查詢年齡不在2124歲之間的學生的姓名、學號、姓名、年齡和所在系 系別和年齡 分組統(tǒng)計：統(tǒng)計每個同學的學號、選課數(shù)、平均成績 統(tǒng)計每個班的每門課的選課人數(shù)、平均成績 連接查詢：查詢選修了2號課程的同學的學號和姓名 查詢各門課程的課程號、課程名稱以及選課學 生的學號 查詢選修了數(shù)據(jù)庫系統(tǒng)原理課程的同學的學號六、實驗結(jié)果與分析StudentCourse Sc 比較使用視圖查詢和直接從基表查詢的優(yōu)點視圖可以簡化用戶的操作 視圖機制使用戶可以將注意力集中在所關(guān)心的數(shù)據(jù)上，而從基本表直接得來則顯得復(fù)雜，定義了視圖則可以簡化查詢

29、操作。視圖使用戶能以多種角度看待同一數(shù)據(jù) 視圖可以讓用戶從不同的方式看待同一數(shù)據(jù)，適當利用視圖可以比基本表更清晰地表達。視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨立性 邏輯結(jié)構(gòu)改變，不影響程序使用。視圖能夠?qū)C密數(shù)據(jù)提供安全保護 視圖可以讓特定的用戶查詢特定的內(nèi)容，把用戶限制在數(shù)據(jù)不同的子集，保證安全性。七、討論、心得 這個實驗是對數(shù)據(jù)庫基本操作的熟悉，花費了大量的時間去進行測試，有時候是出錯不是因為代碼錯誤，而是因為插入數(shù)據(jù)是不能一一對應(yīng)，或是在連接時出現(xiàn)問題，與表的定義也有一定的關(guān)系。總之編寫的時候遇到不少問題，通過解決問題來熟悉了操作，更是要耐心的解決問題，有時候出錯的問題非常簡單，只要認真

30、的檢查一下代碼就能發(fā)現(xiàn)，希望自己可以在編寫代碼時更加認真。實驗三：數(shù)據(jù)完整性實驗?zāi)康模?）了解 SQL Serer數(shù)據(jù)庫系統(tǒng)中數(shù)據(jù)完整性控制的基本方法（2）熟練掌握常用 CREATE 或 ALTER 在創(chuàng)建或修改表時設(shè)置約束（3）了解觸發(fā)器的機制和使用（4）驗證數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)完整性控制二、實驗內(nèi)容和要求結(jié)合 ST數(shù)據(jù)庫中的各個表，設(shè)置相關(guān)的約束，要求包括主鍵約束、外鍵約束、唯一約束、檢查約束、非空約束等，掌握各約束的定義方法。設(shè)置一個觸發(fā)器，實現(xiàn)學生選課總學分的完整性控制，了解觸發(fā)器的工作機制。設(shè)計一些示例數(shù)據(jù)，驗證完整性檢查機制。要求包括如下方面的內(nèi)容：3.1 使用 SQL語句設(shè)置約束使用

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

32、 Windows 2003 Server 數(shù)據(jù)庫管理系統(tǒng)： SQL Server。四、操作方法與實驗步驟1創(chuàng)建基本表及約束（1）創(chuàng)建 Student表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);掌握主鍵約束、非空約束、CHECK約束、默認值的定義格式。（2）創(chuàng)建 C

33、ourse表CREATE TABLE Course( Cno CHAR(4) CONSTRAINT PK_Course PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), Ccredit SMALLINT);（3）創(chuàng)建 SC表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) 掌握如何修改約束

34、理解命名約束的優(yōu)點二、檢查完整性約束通過修改數(shù)據(jù)庫中的數(shù)據(jù)檢查完整性約束條件的作用。1檢查主鍵約束2檢查唯一約束3檢查默認值、允許空值列4檢查非空約束5檢查 CHECK約束6檢查外鍵約束 具體內(nèi)容在第五部分三、觸發(fā)器的定義及使用1定義觸發(fā)器（1）定義一個觸發(fā)器，實現(xiàn)有關(guān)學分的完整性約束：當向SC表插入一行選課記錄時，自將該課程的學分累CREATE TRIGGER tr_INSERT ON SCFOR INSERTAS-聲明變量DECLARE sno char(8)DECLARE cridit intDECLARE cno char(4)-提取插入的數(shù)據(jù)SELECT sno=Sno,cno=Cn

35、o FROM inserted-提取學生的總學分SELECT cridit = CcreditFROM SC join Course ON (SC.Cno = Course.Cno)WHERE SC.Cno = cno-更新總學分UPDATE Student SET Stotal = Stotal + criditWHERE Sno = snoGO（2）定義一個觸發(fā)器，實現(xiàn)對SC表的操作登記：當用戶向SC表插入或修改時，記錄該操作到數(shù)據(jù)庫中。創(chuàng)建日志登記表:CREATE TABLE LOG_TABLE( username char(10), -操作人員 date datetime, -修改時間

36、 Sno char(8) , -學生學號 Cno char(4) -課程號 )創(chuàng)建日志登記觸發(fā)器:CREATE TRIGGER tr_UPDATE ON SCFOR INSERT,UPDATEASDECLARE sno char(8)DECLARE cno char(4)DECLARE new smallintSELECT sno=Sno,cno=Cno FROM insertedINSERT INTO LOG_TABLE VALUES(CURRENT_USER,getdate(),Sno,Cno);GO（3）執(zhí)行插入操作，觸發(fā)觸發(fā)器：INSERT INTO SC VALUES(2010000

37、1,6,95)（4）驗證觸發(fā)器是否觸發(fā)select * from studentselect * from LOG_TABLE五、實驗數(shù)據(jù)記錄和處理1檢查主鍵約束（1）執(zhí)行下面的語句修改Student表，觀察語句能否正確運行，解釋為什么？INSERT INTO Student VALUES(20100101,李斌,男,20,CS,1001,0)INSERT INTO Student VALUES(20100001,李斌,男,20,CS,1001,0)該句無法執(zhí)行，因為Sno主鍵不能有重復(fù)。UPDATE Student SET Sno=20100021 WHERE Sname = 張立該句無法執(zhí)行，因為張立要修改的學號和王敏的學號重復(fù)了，Sno是主鍵，不能重復(fù)（2）執(zhí)行下面的語句修改SC表，觀察語句能否正確運行，解釋為什么？INSERT INTO SC VALUES(20100001,1,78)該句無法執(zhí)行，主鍵重復(fù)。INSERT INTO SC VALUES(20100001,null,78)該句無法執(zhí)行，第二個值不能為空。2檢查唯一約束執(zhí)行下面的語句修改Course表，觀察語句能否正確運行，解釋為什么？INSERT INTO Course VALUES(8,JAVA,7, 3)INSERT I