信息系統(tǒng)與數(shù)據(jù)庫(kù)技術(shù)全書實(shí)驗(yàn)參考答案

1、10.1 實(shí)驗(yàn)一 SQL Server數(shù)據(jù)庫(kù)基本操作一實(shí)驗(yàn)?zāi)康谋緦?shí)驗(yàn)主要了解Microsoft SQL Server 2000中各組件的主要功能和基本使用方法；了解SQL Server數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu)和物理結(jié)構(gòu)；了解數(shù)據(jù)表的結(jié)構(gòu)特點(diǎn)以及索引的基本概念。通過本實(shí)驗(yàn)，讀者將學(xué)會(huì)在“企業(yè)管理器”中創(chuàng)建和修改數(shù)據(jù)庫(kù)和數(shù)據(jù)表結(jié)構(gòu)、對(duì)數(shù)據(jù)表進(jìn)行數(shù)據(jù)的添加、刪除和更新操作以及在數(shù)據(jù)表上創(chuàng)建字段索引的操作。二實(shí)驗(yàn)環(huán)境l Microsoft SQL Server 2000 企業(yè)管理器三實(shí)驗(yàn)內(nèi)容1創(chuàng)建一個(gè)名稱為Book的數(shù)據(jù)庫(kù)，支持教材訂購(gòu)信息管理，要求：（1）將主數(shù)據(jù)庫(kù)文件Book_Data.MDF放置在的D:

2、DBF文件夾中，文件大小為5MB，最大值為50MB，文件增量為2MB。（2）將事務(wù)日志文件Book_Log.LDF放置在的D:DBF文件夾中，文件大小為5MB，最大值為100MB，文件增量為1MB。實(shí)驗(yàn)參考步驟：1）在D:下創(chuàng)建DBF文件夾。2）在企業(yè)管理器窗口右單擊“數(shù)據(jù)庫(kù)”或任一數(shù)據(jù)庫(kù)名，從快捷菜單中選擇“新建數(shù)據(jù)庫(kù)”命令，出現(xiàn)“數(shù)據(jù)庫(kù)屬性”對(duì)話框。3) 在“數(shù)據(jù)庫(kù)屬性”對(duì)話框中選擇“常規(guī)”選項(xiàng)卡，在名稱文本框中輸入：Book；4) 在“數(shù)據(jù)庫(kù)屬性”對(duì)話框中選擇“數(shù)據(jù)文件”選項(xiàng)卡，設(shè)置數(shù)據(jù)文件的位置、大小、最大值和文件增量等屬性；5) 在“數(shù)據(jù)庫(kù)屬性”對(duì)話框中選擇“事務(wù)日志”選項(xiàng)卡，設(shè)置事

3、務(wù)日志文件的位置、大小、最大值和文件增量等屬性。2在上題所建數(shù)據(jù)庫(kù)Book中建立5張數(shù)據(jù)表結(jié)構(gòu)并分別向數(shù)據(jù)表插入記錄。實(shí)驗(yàn)參考步驟：在企業(yè)管理器中展開Book數(shù)據(jù)庫(kù)。右擊數(shù)據(jù)庫(kù)文件夾下的“表”項(xiàng)，從彈出的快捷菜單中選擇“新建表”命令，彈出“表設(shè)計(jì)”界面，分別建立張表的結(jié)構(gòu)，并且為各個(gè)表建立主關(guān)鍵字。3創(chuàng)建T_Teacher、T_Academy、T_BookInfo、T_Publisher和T_BookOrder個(gè)表之間的關(guān)系。實(shí)驗(yàn)參考步驟：1) 在企業(yè)管理器中展開Book數(shù)據(jù)庫(kù)。右擊數(shù)據(jù)庫(kù)文件夾下的“關(guān)系圖”項(xiàng)，從彈出的快捷菜單中選擇“新建數(shù)據(jù)庫(kù)關(guān)系圖”命令。2) 通過“創(chuàng)建數(shù)據(jù)庫(kù)關(guān)系圖向?qū)А?/p>

4、新建關(guān)系圖。4任選兩個(gè)數(shù)據(jù)表插入3-5條記錄（例如T_BookInfo、T_BookOrder、T_Teacher），各數(shù)據(jù)表記錄如表9-xb）所示。實(shí)驗(yàn)參考步驟：在企業(yè)管理器中展開Book數(shù)據(jù)庫(kù)中的表文件夾，右單擊需插入記錄的數(shù)據(jù)表，從彈出的快捷菜單中選擇“打開表返回所有行”命令。在數(shù)據(jù)表顯示界面中依次輸入記錄。將T-teacher表“TeacherName”字段的類型改為Varchar(16)。為T_BookOrder表添加約束條件，使得“StuBookNum”和“TeaBookNum”的取值都大于0；“OrderTime”大于2007年。實(shí)驗(yàn)參考步驟：1) 在企業(yè)管理器中展開Book數(shù)據(jù)

5、庫(kù)中的表文件夾，右擊T-teacher表，選擇“表設(shè)計(jì)”，進(jìn)入T-teacher表結(jié)構(gòu)設(shè)計(jì)界面，修改“TeacherName”字段的類型。2) 在企業(yè)管理器中展開Book數(shù)據(jù)庫(kù)表文件夾，右擊T_BookOrder表，選擇“表設(shè)計(jì)”，進(jìn)入T_BookOrder表結(jié)構(gòu)設(shè)計(jì)界面，右單擊T_BookOrder表結(jié)構(gòu)設(shè)計(jì)界面，從彈出的快捷菜單中選擇“CHECK約束”命令，在“屬性”對(duì)話框的“CHECK約束”選項(xiàng)卡中點(diǎn)擊“新建”按鈕分別設(shè)置約束條件。6練習(xí)記錄數(shù)據(jù)的添加、刪除和更新操作。在T_Teacher表中添加記錄“01010117、黃蓉、13、女、1977-9-21、副教授、63478445”。將T

6、_Teacher表中TeacherCode為“01010113”的記錄刪除；將TeacherCode為“01010116”記錄的title字段內(nèi)容更新為“教授”。實(shí)驗(yàn)參考步驟：在企業(yè)管理器中展開Book數(shù)據(jù)庫(kù)表文件夾，右擊T-teacher表，選擇“打開表返回所有行”，進(jìn)入T-teacher表數(shù)據(jù)行顯示界面，進(jìn)行數(shù)據(jù)的添加、刪除和更新操作。7在企業(yè)管理器中練習(xí)索引的創(chuàng)建和刪除。在“TeacherName”（降序）和“AcadCode”（升序）上建立一個(gè)名稱為“Name_Code”的普通索引。刪除“Name_Code”索引。實(shí)驗(yàn)參考步驟：方法：在企業(yè)管理器中展開Book數(shù)據(jù)庫(kù)表文件夾，右擊T_T

7、eacher表，選擇“表設(shè)計(jì)”，進(jìn)入T_Teacher表結(jié)構(gòu)設(shè)計(jì)界面，右單擊T_Teacher表結(jié)構(gòu)設(shè)計(jì)界面，從彈出的快捷菜單中選擇“索引鍵”命令，在“屬性”對(duì)話框的“索引鍵”選項(xiàng)卡中點(diǎn)擊“新建”按鈕進(jìn)行“Name_Code”索引的建立。方法：在企業(yè)管理器中展開Book數(shù)據(jù)庫(kù)表文件夾，右擊T-teacher表，選擇“所有任務(wù)管理索引”，打開“管理索引”窗口，進(jìn)行索引的建立和編輯。10.2 實(shí)驗(yàn)二 T-SQL語(yǔ)言基本操作一實(shí)驗(yàn)?zāi)康谋緦?shí)驗(yàn)主要了解T-SQL語(yǔ)句以及在查詢分析器中執(zhí)行T-SQL語(yǔ)句的方法。通過本實(shí)驗(yàn)，讀者將了解使用T-SQL語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)和數(shù)據(jù)表；掌握數(shù)據(jù)庫(kù)查詢語(yǔ)句Select語(yǔ)句的

8、基本語(yǔ)法及使用方法；掌握對(duì)數(shù)據(jù)表進(jìn)行數(shù)據(jù)插入、修改和刪除的T-SQL語(yǔ)句；了解T-SQL語(yǔ)言的各種運(yùn)算符、控制語(yǔ)句的功能并學(xué)會(huì)簡(jiǎn)單的程序編寫。二實(shí)驗(yàn)環(huán)境l Microsoft SQL Server 2000查詢分析器和Microsoft SQL Server 2000企業(yè)管理器l Book數(shù)據(jù)庫(kù)三實(shí)驗(yàn)內(nèi)容1利用企業(yè)管理器中提供的“生成SQL腳本”命令編寫B(tài)ook數(shù)據(jù)庫(kù)中數(shù)據(jù)表對(duì)象“T_Teacher”、“T_BookOrder”、“T_BookInfo”、“T_Publisher”、“T_Academy”創(chuàng)建的腳本文件D:Table.sql。實(shí)驗(yàn)參考步驟：1) 在企業(yè)管理器右單擊Book數(shù)據(jù)庫(kù)

9、，選擇“所有任務(wù)生成SQL腳本”菜單，打開“生成SQL腳本”窗口，點(diǎn)擊“全部顯示”按鈕。2) 按要求添加寫入腳本的對(duì)象，點(diǎn)擊“確定”按鈕，打開“另存為”對(duì)話框，設(shè)置腳本文件名稱。2在查詢分析器中使用T-SQL語(yǔ)句完成實(shí)驗(yàn)一中的第1題。CREATE DATABASE BookON(NAME=Book_Data, FILENAME='D:DBFBOOK_Data.MDF', SIZE=5MB, MAXSIZE=50MB, FILEGROWTH=2MB)LOG ON(NAME=Book_log, FILENAME='D:DBFBook_Log.LDF', SIZE=5

10、MB, MAXSIZE=100MB, FILEGROWTH=1MB)3編寫SELECT語(yǔ)句，建立以下查詢1）顯示T_BookInfo表中所有字段的值。SELECT *FROM T_BookInfo2）查詢T_Teacher表中所有記錄的TeacherCode、TeacherName、EnterDate和Title字段信息。USE BookSELECT TeacherCode,TeacherName,EnterDate,TitleFROM T_Teacher3）查詢男教師的姓名(TeacherName)，顯示時(shí)將字段名TeacherName改為“姓名”。SELECT TeacherName AS

11、姓名FROM T_TeacherWHERE Sex='男'4）查詢2007年以后出版的教材名稱(BookName)、出版日期(PublishDate)和庫(kù)存量(StockNum)，顯示時(shí)字段標(biāo)題分別為：教材名稱、出版日期和庫(kù)存量。SELECT BookName AS 教材名稱,PublishDate AS 出版日期,StockNum AS 庫(kù)存量FROM T_BookInfoWHERE year(PublishDate)>='2007'或SELECT BookName AS 教材名稱,PublishDate AS 出版日期,StockNum AS 庫(kù)存量F

12、ROM T_BookInfoWHERE PublishDate>='2007-01-01'5）查詢所有教材的出版社代碼(PublisherCode)，將重復(fù)的記錄去掉。SELECT DISTINCT PublisherCode FROM T_BookInfo6）查詢學(xué)生訂書量（StuBookNum）最大的3個(gè)記錄，顯示教材代號(hào)(BookCode)和學(xué)生訂書量（StuBookNum）。SELECT top 3 BookCode,StuBookNumFROM T_BookOrderORDER BY StuBookNum DESC圖77）查詢學(xué)生訂書量（StuBookNum）在

13、150到250之間的教材信息。SELECT BookCode,TeacherCode,StuBookNum,OrderTimeFROM T_BookOrderWHERE ( StuBookNum BETWEEN 150 AND 250)8）查詢每一種教材的學(xué)生訂書總量。SELECT BookCode AS 教材代號(hào) ,SUM(StuBookNum) AS 學(xué)生訂書數(shù)量FROM T_BookOrderGROUP BY BookCode9）查詢書名為“VB程序設(shè)計(jì)”的教材的學(xué)生總訂量（StuBookNum）和教師總訂量（TeaBookNum）和總訂量。SELECT SUM(StuBookNum)

14、AS 學(xué)生總訂量,SUM(TeaBookNum) AS 教師總訂量,(SUM(StuBookNum)+SUM(TeaBookNum) AS 總訂量FROM T_BookInfo ,T_BookOrder WHERE T_BookInfo.BookCode=T_BookOrder.BookCode AND BookName='VB程序設(shè)計(jì)'或SELECT SUM(StuBookNum) AS 學(xué)生總訂量,SUM(TeaBookNum) AS 教師總訂量,(SUM(StuBookNum)+SUM(TeaBookNum) AS 總訂量FROM T_BookInfo INNER JOI

15、N T_BookOrder ON T_BookInfo.BookCode=T_BookOrder.BookCode WHERE BookName='VB程序設(shè)計(jì)'10）顯示教材的最高價(jià)、最低價(jià)、平均價(jià)、最高價(jià)與最低價(jià)之間的差值。SELECT str(Max(Price),5,2) AS 最高價(jià),str(Min(Price),5,2)AS 最低價(jià),str(AVG(Price),5,2) AS 平均價(jià), str(Max(Price)-Min(Price),5,2) AS 差值FROM T_BookInfo11）查詢出版社代號(hào)（PublisherCode）為10、02、31的出版社所

16、出版的教材信息。要求顯示教材名稱（BookName）、作者（Author）、價(jià)格（Price）、庫(kù)存（StockNum）和出版社名稱（Publisher）。SELECT T_BookInfo.BookName,T_BookInfo.Author,T_BookInfo.Price,T_BookInfo.StockNum,T_Publisher.PublisherCode,T_Publisher.PublisherFROM T_Publisher,T_BookInfoWHERE T_Publisher.PublisherCode=T_BookInfo.PublisherCode AND T_Pub

17、lisher.PublisherCode IN ('10','02','31')或：SELECT T_BookInfo.BookName,T_BookInfo.Author,T_BookInfo.Price,T_BookInfo.StockNum,T_Publisher.PublisherCode,T_Publisher.PublisherFROM T_Publisher INNER JOIN T_BookInfoON T_Publisher.PublisherCode=T_BookInfo.PublisherCode WHERE T_Publi

18、sher.PublisherCode IN ('10','02','31')12）查詢沒有領(lǐng)書的教師信息。要求顯示TeacherCode（教師工號(hào)）、TeacherName（姓名）、AcadName（學(xué)院名稱）和Telephone（電話號(hào)碼），并按學(xué)院名稱降序排列。SELECT T_Teacher.TeacherCode,TeacherName,AcadName,TelephoneFROM T_BookOrder ,T_Teacher ,T_AcademyWHERE T_Teacher.TeacherCode= T_BookOrder.Teach

19、erCode AND T_Teacher.AcadCode= T_Academy.AcadCode AND TakeAway=0ORDER BY AcadName DESC或：SELECT T_Teacher.TeacherCode,TeacherName,AcadName,TelephoneFROM T_Academy INNER JOIN (T_Teacher INNER JOIN T_BookOrder ON T_Teacher.TeacherCode=T_BookOrder.TeacherCode)ON T_Academy.AcadCode=T_Teacher.AcadCode WHE

20、RE TakeAway=0ORDER BY AcadName DESC13）查詢?nèi)宋膶W(xué)院教師所訂教材的信息。要求顯示教材代碼（BookCode）、數(shù)量（StuBookNum+TeaBookNum）和訂教材日期（OrderTime）。SELECT BookCode,StuBookNum+TeaBookNum AS 數(shù)量,OrderTimeFROM T_BookOrderWHERE TeacherCode IN (SELECT TeacherCodeFROM T_TeacherWHERE AcadCode=(SELECT AcadCodeFROM T_AcademyWHERE AcadName=&

21、#39;人文學(xué)院')或：SELECT BookCode,StuBookNum+TeaBookNum AS 數(shù)量,OrderTimeFROM T_BookOrderWHERE TeacherCode IN(SELECT TeacherCodeFROM T_Teacher INNER JOIN T_AcademyON T_Teacher.AcadCode=T_Academy.AcadCodeWHERE AcadName='人文學(xué)院')14）查詢姓名字符數(shù)大于等于4個(gè)字符的作者情況，要求顯示作者姓名（Author）、教材名稱（BookName）。SELECT Author,B

22、ookNameFrom T_BookInfo WHERE LEN(Author)>=415）查詢教師工號(hào)（TeacherCode）為“01010109”的教師是否已預(yù)訂教材，若已預(yù)訂則顯示所訂教材的數(shù)量；否則顯示：“該教師沒有預(yù)訂教材”。DECLARE cn smallint,text varchar(100)SELECT cn=(SELECT StuBookNum+TeaBookNumFROM T_BookOrderWHERE TeacherCode='01010109')IF cn>0 SET text='該教師預(yù)訂教材數(shù)量'+str(cn)EL

23、SE SET text='該教師沒有預(yù)訂教材'SELECT text4使用聯(lián)接關(guān)鍵字(JOINN)建立查詢1）查詢所有教師姓名（TeacherName）及所屬學(xué)院名稱（AcadName）。（提示：本題使用INNER JOIN完成查詢）SELECT TeacherName, AcadNameFROM T_Teacher INNER JOIN T_Academy ON T_Teacher.AcadCode= T_Academy.AcadCodeORDER BY T_Teacher.AcadCode2）查詢所有學(xué)院名稱（AcadName）及每個(gè)學(xué)院的教師姓名（TeacherName）

24、。（提示：本題使用LEFT OUTER JOIN完成查詢）SELECT AcadName,TeacherNameFROM T_Academy LEFT OUTER JOIN T_Teacher ON T_Academy.AcadCode=T_Teacher.AcadCode3）查詢所有教師姓名（TeacherName）及所訂教材代號(hào)（BookCode）。（提示：本題使用RIGHT OUTER JOIN完成查詢）SELECT TeacherName,BookCodeFROM T_BookOrder RIGHT OUTER JOIN T_TeacherON T_BookOrder.TeacherC

25、ode=T_Teacher.TeacherCode4）查詢所有已訂教材的教師姓名（TeacherName）、教材代號(hào)（BookCode）及教材名稱（BookName）。（提示：本題使用嵌套的INNER JOIN完成查詢）SELECT TeacherName,T_BookOrder.BookCode,BookNameFrom T_Teacher INNER JOIN (T_BookOrder INNER JOIN T_BookInfo ON T_BookOrder.BookCode=T_BookInfo.BookCode)ON T_Teacher.TeacherCode=T_BookOrder.

26、TeacherCode5使用SELECT語(yǔ)句復(fù)制數(shù)據(jù)表1）將T_BookOrder表中的教師編號(hào)（TeacherCode）、教材代號(hào)（BookCode）、學(xué)生用書量（StuBookNum）、教師用書量（TeaBookNum）字段及對(duì)應(yīng)的書名（BookName）復(fù)制到新表NewTable1中。SELECT TeacherCode,T_BookOrder.BookCode,BookName,StuBookNum,TeaBookNumINTO NewTable1FROM T_BookInfo ,T_BookOrder WHERE T_BookInfo.BookCode=T_BookOrder.Boo

27、kCode或：SELECT BookName,T_BookOrder.BookCode,StuBookNum,TeaBookNumINTO NewTable1FROM T_BookOrder INNER JOIN T_BookInfoON T_BookOrder.BookCode=T_BookInfo.BookCode2）將T_Teacher表中的教師姓名（TeacherName）、性別（Sex）字段及每個(gè)教師所屬的學(xué)院名稱（AcadName）復(fù)制到新表NewTable2中。SELECT TeacherName, Sex, AcadNameINTO NewTable2FROM T_Teache

28、r ,T_Academy WHERE T_Teacher.AcadCode= T_Academy .AcadCode或：SELECT TeacherName, Sex, AcadNameINTO NewTable2FROM T_Teacher INNER JOIN T_Academy ON T_Teacher.AcadCode= T_Academy.AcadCode6使用DELECT語(yǔ)句練習(xí)刪除操作1）刪除NewTable1表中學(xué)生用書量（StuBookNum）和教師用書量（TeaBookNum）之和低于60的記錄。DELETE FROM NewTable1WHERE StuBookNum+T

29、eaBookNum<602）刪除NewTable2表中姓“王”和姓“楊”的記錄。DELETE FROM NewTable2WHERE LEFT(TeacherName,1)='王'OR LEFT(TeacherName,1)='楊'或：DELETE FROM NewTable2WHERE TeacherName LIKE '王%' OR TeacherName LIKE '楊%'3）刪除NewTable1中“體育部”教師的訂書記錄。DELETE FROM NewTable1WHERE TeacherCode IN (SELE

30、CT T_Teacher.TeacherCodeFrom T_Teacher INNER JOIN T_AcademyON T_Teacher.AcadCode=T_Academy.AcadCodeWhere AcadName='體育部' )7使用INSERT語(yǔ)句練習(xí)插入操作1）在NewTable2表中添加一條記錄，姓名：吳清、性別：女、學(xué)院名稱：計(jì)算機(jī)科學(xué)與技術(shù)學(xué)院。INSERT INTO NewTable2(TeacherName,Sex,AcadName)VALUES('吳清','女','計(jì)算機(jī)科學(xué)與技術(shù)學(xué)院')2）將T_T

31、eacher表中姓“王”和姓“楊”的記錄添加到NewTable2表中。INSERT INTO NewTable2SELECT TeacherName,Sex,AcadNameFROM T_Teacher INNER JOIN T_Academy ON T_Teacher.AcadCode=T_Academy.AcadCodeWHERE LEFT(TeacherName,1)='王' OR LEFT(TeacherName,1)='楊'8使用UPDATE語(yǔ)句修改數(shù)據(jù)1）將2000年以前（不包括2000年）入校的、職稱為助教的教師職稱（TitleCode）往上提一級(jí)

32、。UPDATE T_TeacherSET Title='講師'WHERE Title='助教' AND YEAR(EnterDate)<20002）修改訂書數(shù)量。要求：所有學(xué)院名稱中包含漢字“工”的學(xué)院所屬的教師所訂的學(xué)生定書量減少3本、教師定書量增加3本。UPDATE T_BookOrderSET StuBookNum=StuBookNum-3,TeaBookNum=TeaBookNum+3WHERE TeacherCode IN(SELECT T_BookOrder.TeacherCode FROM T_BookOrder,T_Academy,T_Te

33、acher WHERE T_BookOrder.TeacherCode= T_Teacher.TeacherCode AND T_Teacher.AcadCode= T_Academy.AcadCode AND AcadName LIKE '%工%' )3）將訂書量超過300本的教材庫(kù)存量增加500本。UPDATE T_BookInfoSET StockNum=500WHERE BookCode IN (SELECT BookCode FROM T_BookOrder )10.3 實(shí)驗(yàn)三 SQL Server的視圖、存儲(chǔ)過程和觸發(fā)器一實(shí)驗(yàn)?zāi)康谋緦?shí)驗(yàn)主要了解SQL Server視

34、圖、存儲(chǔ)過程和觸發(fā)器的基本概念和使用方法。通過本實(shí)驗(yàn)，讀者將學(xué)會(huì)在企業(yè)管理器中創(chuàng)建、修改、執(zhí)行和刪除存儲(chǔ)過程的操作以及在查詢分析器中執(zhí)行的T-SQL語(yǔ)句；掌握觸發(fā)器的創(chuàng)建、修改和刪除的操作方法和T-SQL語(yǔ)句。掌握視圖的創(chuàng)建、修改和刪除的操作方法和T-SQL語(yǔ)句。二實(shí)驗(yàn)環(huán)境l Microsoft SQL Server 2000 企業(yè)管理器和Microsoft SQL Server 2000 查詢分析器l Book數(shù)據(jù)庫(kù)三實(shí)驗(yàn)內(nèi)容1創(chuàng)建存儲(chǔ)過程proc_book1，查詢指定學(xué)院的教師預(yù)訂教材的情況。要求顯示教材名稱（T_BookInfo.BookName）、教師姓名（T_Teacher.Teac

35、herName）和教材數(shù)量（T_BookOrder.StuBookNum+T_BookOrder.TeaBookNum）。(1)查詢條件為：學(xué)院代碼CREATE PROC proc_book1 acode char(6)ASSELECT BookName, TeacherName,StuBookNum+TeaBookNum AS 數(shù)量FROM T_Teacher INNER JOIN (T_BookOrder INNER JOIN T_BookInfoON T_BookOrder.BookCode= T_BookInfo.BookCode)ON T_Teacher.TeacherCode=T_

36、BookOrder.TeacherCodeWHERE T_Teacher.AcadCode=acode-執(zhí)行存儲(chǔ)過程proc_book1，查詢學(xué)院代號(hào)為'02'的教師預(yù)訂教材的信息。EXEC proc_book1 '02'或：EXEC proc_book1 scode='02'（2）查詢條件為：學(xué)院名稱CREATE PROC proc_book1_2 aname varchar(50)ASSELECT BookName, TeacherName,StuBookNum+TeaBookNum AS 數(shù)量FROM T_Academy,T_Teacher

37、,T_BookOrder,T_BookInfoWHERE T_Academy.AcadCode=T_Teacher.AcadCode AND T_Teacher.TeacherCode=T_BookOrder.TeacherCode AND T_BookInfo.BookCode=T_BookOrder.BookCode AND T_Academy.AcadName=aname-執(zhí)行存儲(chǔ)過程proc_book1_2，查詢學(xué)院名稱為'人文學(xué)院'的教師預(yù)訂教材的信息。EXEC proc_book1_2 '人文學(xué)院'或EXEC proc_book1_2 scode=&

38、#39;人文學(xué)院'2創(chuàng)建存儲(chǔ)過程proc_book2，查詢指定教材的預(yù)訂數(shù)量。統(tǒng)計(jì)條件為：教材代碼CREATE PROCEDURE proc_book2tcode char(20)ASSELECT SUM(StuBookNum)+SUM(TeaBookNum)FROM T_BookOrderGROUP BY BookCodeHAVING BookCode=tcode-執(zhí)行存儲(chǔ)過程proc_book2，統(tǒng)計(jì)教材代號(hào)為'010004'的預(yù)訂信息。exec proc_book2 '100001'3創(chuàng)建添加教材信息的存儲(chǔ)過程proc_book3。CREATE P

39、ROCEDURE proc_book3(bookcode char(6),bookname varchar(40), publishercode varchar(2),author varchar(40), publisherdate datetime,price money, isbncode char(20),stocknum smallint, booksort char(20)ASBEGIN INSERT INTO T_BookInfoVALUES(bookcode,bookname,publishercode,author,publisherdate,price, isbncode,

40、stocknum,booksort)END-執(zhí)行存儲(chǔ)過程proc_book3EXEC proc_book3 '500001','信息系統(tǒng)管理技術(shù)','31','譚浩強(qiáng)','2007-05-11',30,'760211008',30,'計(jì)算機(jī)'4創(chuàng)建存儲(chǔ)過程proc_book4，查詢指定教材名稱和出版社名稱的教材所對(duì)應(yīng)的教材代號(hào)和庫(kù)存。CREATE PROCEDURE proc_book4bookname varchar(40),publisher varchar(40),bookco

41、de char(6) OUTPUT,stocknum smallint OUTPUTASSELECT bookcode=BookCode,stocknum=StockNumFROM T_BookInfo INNER JOIN T_Publisher ON T_Publisher.PublisherCode=T_BookInfo.PublisherCodeWHERE T_Publisher.Publisher=publisher AND BookName=bookname-執(zhí)行存儲(chǔ)過程proc_book4DECLARE bookcode char(6),stocknum smallint EXE

42、C proc_book4 'C語(yǔ)言程序設(shè)計(jì)','電子工業(yè)出版社',bookcode OUTPUT,stocknum OUTPUTPRINT '該教材的代號(hào)'+CAST(bookcode AS char(6)PRINT '該教材的庫(kù)存'+STR(stocknum)5創(chuàng)建觸發(fā)器tri_book1，在對(duì)T_Teacher表進(jìn)行插入、修改和刪除記錄時(shí)，都會(huì)自動(dòng)顯示表中的內(nèi)容。CREATE TRIGGER tri_book1ON T_TeacherFOR INSERT,UPDATE,DELETEASSELECT * FROM T_Teach

43、er6創(chuàng)建觸發(fā)器tri_book2，當(dāng)修改T_Teacher表中的TeacherCode字段值時(shí)，該字段在T_BookOrder表中的對(duì)應(yīng)值也作修改。CREATE TRIGGER tri_book2ON T_BookOrderFOR UPDATEASUPDATE T_BookOrderSET TeacherCode=(SELECT Inserted.TeacherCode FROM Inserted)WHERE TeacherCode=(SELECT TeacherCode FROM Deleted)7創(chuàng)建觸發(fā)器tri_book3，檢查插入在T_BookInfo表中的教材庫(kù)存(StockNum

44、)是否大于等于0。CREATE TRIGGER tri_book3ON T_BookInfoFOR INSERT,UPDATEASDECLARE stocknum smallintSELECT stocknum=StockNum FROM insertedIF stocknum<0BEGINROLLBACKRAISERROR('庫(kù)存量必須大于等于!',16,1)END 創(chuàng)建觸發(fā)器tri_book，當(dāng)刪除教師信息時(shí)同步刪除T_BookOrder表中對(duì)應(yīng)教師所預(yù)訂的教材記錄。CREATE TRIGGER tri_bookON T_TeacherFOR DELETEAS DEL

45、ETE FROM T_BookOrderWHERE T_BookOrder.TeacherCode= (SELECT TeacherCode FROM deleted)創(chuàng)建視圖view_book1，使其包含預(yù)訂數(shù)量最高的20%的教材信息。要求顯示教材代號(hào)(BookCode)、教材名稱(BookName)、ISBN號(hào)(ISBNCode)和出版社名稱(Publisher)。CREATE VIEW view_book1ASSELECT TOP 20 PERCENT T_BookOrder.BookCode, BookName,ISBNCode,StuBookNum+TeaBookNum AS 數(shù)量,

46、 PublisherFROM T_BookOrder INNER JOIN (T_Publisher INNER JOIN T_BookInfo ON T_Publisher.PublisherCode=T_BookInfo.PublisherCode)ON T_BookOrder.BookCode=T_BookInfo.BookCodeORDER BY StuBookNum+TeaBookNum DESC10創(chuàng)建視圖view_book2，查詢預(yù)訂了教材名中含有“程序”一詞的教師姓名（TeacherName）和所在學(xué)院名稱（AcadName）。CREATE VIEW view_book2ASS

47、ELECT TeacherName,AcadNameFROM T_Teacher,T_Academy,T_BookOrder,T_BookinfoWHERE T_Bookinfo.BookCode=T_BookOrder.BookCode AND T_BookOrder.TeacherCode=T_Teacher.TeacherCode AND T_Teacher.AcadCode=T_Academy.AcadCodeAND BookName LIKE '%程序%'或CREATE VIEW view_book2ASSELECT TeacherName,AcadNameFROM

48、T_Academy INNER JOIN (T_Teacher INNER JOIN (T_BookOrder INNER JOIN T_Bookinfo ON T_BookOrder.BookCode=T_Bookinfo.BookCode) ON T_Teacher.TeacherCode=T_BookOrder.TeacherCode) ON T_Academy.AcadCode=T_Teacher.AcadCodeWHERE BookName LIKE '%程序%'或11創(chuàng)建視圖view_book3，使其包含“學(xué)院名稱（AcadName）”、“教師姓名（TeacherN

49、ame）”、“預(yù)訂書名（BookName）”、“是否領(lǐng)走（TakeAway）”。CREATE VIEW view_book3ASSELECT AcadName, TeacherName, BookName, TakeAwayFROM T_BookInfo INNER JOIN T_BookOrder ON T_BookInfo.BookCode=T_BookOrder.BookCode INNER JOIN T_Teacher ON T_BookOrder.TeacherCode = T_Teacher.TeacherCode INNER JOIN T_Academy ON T_Teacher

50、.AcadCode = T_Academy.AcadCode10.4 實(shí)驗(yàn)四 SQL Server數(shù)據(jù)庫(kù)管理一實(shí)驗(yàn)?zāi)康谋緦?shí)驗(yàn)主要了解SQL Server數(shù)據(jù)庫(kù)的安全機(jī)制和備份策略。通過本實(shí)驗(yàn)，讀者將學(xué)會(huì)在企業(yè)管理器中進(jìn)行用戶的創(chuàng)建和權(quán)限的管理以及數(shù)據(jù)庫(kù)的備份和恢復(fù)。二實(shí)驗(yàn)環(huán)境l Microsoft SQL Server 2000 企業(yè)管理器和Microsoft SQL Server 2000 查詢分析器l Book數(shù)據(jù)庫(kù)三實(shí)驗(yàn)內(nèi)容1利用企業(yè)管理器將Book數(shù)據(jù)庫(kù)完全備份到磁盤文件D:BookBackup中，然后使用備份文件D:BookBackup進(jìn)行完全恢復(fù)操作。備份操作參考步驟：（1）選中數(shù)

51、據(jù)庫(kù)并執(zhí)行備份菜單。在控制臺(tái)根目錄窗格選中要備份的數(shù)據(jù)庫(kù)Book，單擊右鍵找到“所有任務(wù)/備份數(shù)據(jù)庫(kù)”菜單，如圖6-1a；或選中數(shù)據(jù)庫(kù)Book后使用菜單欄中“工具/備份數(shù)據(jù)庫(kù)”菜單，如圖6-1b。圖6-1b .圖6-1a (2) 在SQL Server備份窗口中選擇備份策略為完全備份，點(diǎn)擊添加按鈕設(shè)置備份的目的設(shè)備，如圖6-1c所示。(3) 在選擇備份目的窗口添加備份目的設(shè)備或文件，選擇或設(shè)置目的文件為：D:BookBackup，如圖6-1d所示。(4) 在SQL Server備份窗口中選擇重寫方式為重寫現(xiàn)有媒體，然后單擊“確定”按鈕執(zhí)行備份，如圖6-1e所示。圖6-1c 圖6-1d 圖6-1

52、e 圖6-1 c 恢復(fù)操作參考步驟：(1) 在控制臺(tái)根目錄窗格選中數(shù)據(jù)庫(kù)，點(diǎn)右鍵找到“所有任務(wù)/還原數(shù)據(jù)庫(kù)”菜單；或選中數(shù)據(jù)庫(kù)后使用菜單欄中“工具/還原數(shù)據(jù)庫(kù)”菜單。(2) 在“還原數(shù)據(jù)庫(kù)窗口”中，設(shè)置“還原為數(shù)據(jù)庫(kù)”的數(shù)據(jù)庫(kù)名，可以使用原數(shù)據(jù)庫(kù)名，也可重新命名。選擇“還原”的備份源，如果選擇“數(shù)據(jù)庫(kù)”，在顯示數(shù)據(jù)庫(kù)備份中選擇備份的數(shù)據(jù)，則系統(tǒng)在“要還原的的第一個(gè)備份”列表框中自動(dòng)列出所有已生成在指定備份目錄下的備份文件和時(shí)間，只要將備份文件的復(fù)選框選中，按確定按鈕就開始數(shù)據(jù)庫(kù)恢復(fù)。2利用企業(yè)管理器建立一個(gè)名稱為“Book維護(hù)計(jì)劃”的數(shù)據(jù)庫(kù)維護(hù)計(jì)劃，將Book數(shù)據(jù)庫(kù)每天0:00備份到磁盤目錄D

53、:下，事務(wù)日志每隔6小時(shí)備份一次到磁盤目錄D:下。操作參考步驟：選中要備份的數(shù)據(jù)庫(kù)Book，單擊右鍵找到“所有任務(wù)/維護(hù)計(jì)劃”菜單；或選中數(shù)據(jù)庫(kù)Book后使用菜單欄中“工具/數(shù)據(jù)庫(kù)維護(hù)計(jì)劃器”菜單。將Book數(shù)據(jù)庫(kù)中的教師信息表數(shù)據(jù)（T_Teacher）導(dǎo)出到Excel文件D:Teacher.xls。操作參考步驟：選中數(shù)據(jù)庫(kù)Book，單擊右鍵找到“所有任務(wù)/導(dǎo)出數(shù)據(jù)”菜單，進(jìn)入DTS導(dǎo)入/導(dǎo)出/向?qū)醉?yè)。 將Book數(shù)據(jù)庫(kù)進(jìn)行分離，然后再練習(xí)附加數(shù)據(jù)庫(kù)Book_Data.MF的操作。操作參考步驟：(1) 分離數(shù)據(jù)庫(kù)：在“數(shù)據(jù)庫(kù)”文件夾找到Book右擊Book數(shù)據(jù)庫(kù)選擇“所有任務(wù)/分離數(shù)據(jù)庫(kù)”菜

54、單。 (2) 附加數(shù)據(jù)庫(kù)：右擊“數(shù)據(jù)庫(kù)”，選擇“所有任務(wù)附加數(shù)據(jù)庫(kù)”菜單。5利用企業(yè)管理器創(chuàng)建一個(gè)用戶名為“MyMary”的用戶，密碼為“secret1234”，該用戶對(duì)“Book”數(shù)據(jù)庫(kù)中的T_Teacher表只有查詢的權(quán)限，沒有刪除、插入和修改權(quán)限。以該用戶名和密碼通過查詢分析器登錄到SQL Server服務(wù)器，對(duì)T_Teacher表進(jìn)行查詢、插入、刪除和修改操作來驗(yàn)證用戶權(quán)限。操作參考步驟：(1) 在控制臺(tái)根目錄窗格,找到“安全性登錄新建登錄”菜單，創(chuàng)建一個(gè)名稱為“MyMary”，密碼為“secret1234”的SQL Server認(rèn)證帳號(hào)。(2) 展開Book數(shù)據(jù)庫(kù)樹形結(jié)構(gòu)，在“用戶”

55、項(xiàng)上單擊右鍵，選擇“新建數(shù)據(jù)庫(kù)用戶”，創(chuàng)建一個(gè)名稱為“MyMary”，密碼為“secret1234”的用戶。如果步驟(1)中已設(shè)好可訪問數(shù)據(jù)庫(kù)“Book”，則與該SQL Server認(rèn)證帳號(hào)同名的用戶帳戶就自動(dòng)加入該數(shù)據(jù)庫(kù)的用戶中，步驟(2)省略。(3) 選中”MyMary”，右單擊找到“屬性”，對(duì)“MyMary”用戶授規(guī)定的權(quán)限。(4) 修改SQL Server登錄帳號(hào)為”MyMary”驗(yàn)證：(1)以”MyMary”登錄查詢分析器(2) 刪除驗(yàn)證: DELETE FROM T_Teacher WHERE TeacherCode='01010101'(3)插入驗(yàn)證: INSER

56、T T_Teacher(TeacherCode,TeacherName,AcadCode,Sex,Title)VALUES('11111111','張三','11','女','講師')(4)修改驗(yàn)證: UPDATE T_Teacher SET TeacherCode='12345678' WHERE TeacherCode='11111111'6利用企業(yè)管理器修改上題中“MyMary”用戶的權(quán)限，授予其對(duì)T_Teacher表的查詢、插入和修改權(quán)限，重新再對(duì)該用戶權(quán)限進(jìn)行驗(yàn)證。操作參考步驟：選中“MyMary”用戶，右單擊找到“屬性”，對(duì)“MyMary”的權(quán)限進(jìn)行修改。驗(yàn)證:如第5題7為Book數(shù)據(jù)庫(kù)創(chuàng)建一個(gè)名稱為“BookRole”的數(shù)據(jù)庫(kù)角色