實(shí)驗(yàn)指導(dǎo)書(shū)(數(shù)據(jù)庫(kù)系統(tǒng)原理及應(yīng)用) - 副本

數(shù)據(jù)庫(kù)系統(tǒng)原理及應(yīng)用實(shí)驗(yàn)指導(dǎo)書(shū)（本科）福建工程學(xué)院信息科學(xué)與工程學(xué)院軟件工程教研室2013年2月目錄實(shí)驗(yàn)一數(shù)據(jù)定義語(yǔ)言 1實(shí)驗(yàn)二SQLSever中的單表查詢(xún) 2實(shí)驗(yàn)三SQLServe中的連接查詢(xún) 3實(shí)驗(yàn)四SQLServe的數(shù)據(jù)更新、視圖 4實(shí)驗(yàn)五數(shù)據(jù)控制（完整性與安全性） 5實(shí)驗(yàn)六語(yǔ)法元素與流程控制 7實(shí)驗(yàn)七存儲(chǔ)過(guò)程與用戶(hù)自定義函數(shù) 9實(shí)驗(yàn)八觸發(fā)器 10實(shí)驗(yàn)一數(shù)據(jù)定義語(yǔ)言一、實(shí)驗(yàn)?zāi)康氖煜QLServer2000/2005查詢(xún)分析器。掌握SQL語(yǔ)言的DDL語(yǔ)言，在SQLServer2000/2005環(huán)境下采用Transact-SQL實(shí)現(xiàn)表的定義、刪除與修改，掌握索引的建立與刪除方法。掌握SQLServer2000/2005實(shí)現(xiàn)完整性的六種約束。二、實(shí)驗(yàn)內(nèi)容啟動(dòng)SQLServer2000/2005查詢(xún)分析器，并連接服務(wù)器。創(chuàng)建數(shù)據(jù)庫(kù):（請(qǐng)先在D盤(pán)下創(chuàng)建DB文件夾）1）在SQLServer2000中建立一個(gè)StuDB數(shù)據(jù)庫(kù)：有一個(gè)數(shù)據(jù)文件：邏輯名為StuData，文件名為“d:\db\StuDat.mdf”，文件初始大小為5MB，文件的最大大小不受限制，文件的增長(zhǎng)率為2MB；有一個(gè)日志文件，邏輯名為StuLog，文件名為“d:\db\StuLog.ldf”，文件初始大小為5MB，文件的最大大小為10MB，文件的增長(zhǎng)率為10%2）刷新管理器查看是否創(chuàng)建成功，右擊StuDB查看它的屬性。設(shè)置StuDB為當(dāng)前數(shù)據(jù)庫(kù)。在StuDB數(shù)據(jù)庫(kù)中作如下操作：設(shè)有如下關(guān)系表S：S(CLASS,SNO,NAME,SEX,AGE)，其中：CLASS為班號(hào)，char(5)；SNO為座號(hào)，char(2)；NAME為姓名，char(10)，設(shè)姓名的取值唯一；SEX為性別，char(2)；AGE為年齡，int，表中主碼為班號(hào)+座號(hào)。寫(xiě)出實(shí)現(xiàn)下列功能的SQL語(yǔ)句。創(chuàng)建表S；刷新管理器查看表是否創(chuàng)建成功；右擊表S插入3個(gè)記錄：95031班25號(hào)李明，男性，21歲；95101班10號(hào)王麗，女性，20歲；95031班座號(hào)為30，名為鄭和的學(xué)生記錄；將年齡的數(shù)據(jù)類(lèi)型改為smallint；向S表添加“入學(xué)時(shí)間（comedate）”列，其數(shù)據(jù)類(lèi)型為日期型（datetime）；對(duì)表S，按年齡降序建索引（索引名為inxage）；刪除S表的inxage索引；刪除S表；在StuDB數(shù)據(jù)庫(kù)中，

（1）按照《數(shù)據(jù)庫(kù)系統(tǒng)概論》(第四版)P82頁(yè)的學(xué)生－課程數(shù)據(jù)庫(kù)創(chuàng)建STUDENT、COURSE和SC三張表，每一張表都必須有主碼約束，合理使用列級(jí)完整性約束和表級(jí)完整性。并輸入相關(guān)數(shù)據(jù)。（2）將StuDB數(shù)據(jù)庫(kù)分離，在D盤(pán)下創(chuàng)建DB文件夾下找到StuDB數(shù)據(jù)庫(kù)的兩個(gè)文件，進(jìn)行備份，后面的實(shí)驗(yàn)要用到這個(gè)數(shù)據(jù)庫(kù)。（課外）按照《數(shù)據(jù)庫(kù)系統(tǒng)概論》(第四版)P74頁(yè)習(xí)題5的SPJ數(shù)據(jù)庫(kù)。創(chuàng)建SPJ數(shù)據(jù)庫(kù)，并在其中創(chuàng)建S、P、J和SPJ四張表。每一張表都必須有主碼約束，合理使用列級(jí)完整性約束和表級(jí)完整性。要作好備份以便后面的實(shí)驗(yàn)使用該數(shù)據(jù)庫(kù)數(shù)據(jù)。三、實(shí)驗(yàn)要求：學(xué)會(huì)使用SQLServer2000/2005的查詢(xún)分析器，企業(yè)管理器和聯(lián)機(jī)叢書(shū)。根據(jù)實(shí)驗(yàn)內(nèi)容認(rèn)真寫(xiě)好實(shí)驗(yàn)報(bào)告，記錄每個(gè)步驟的腳本和結(jié)果的截圖。報(bào)告內(nèi)容：實(shí)驗(yàn)內(nèi)容1~5四、實(shí)驗(yàn)總結(jié)

實(shí)驗(yàn)二SQLSever中的單表查詢(xún)一、實(shí)驗(yàn)?zāi)康模赫莆誗QL語(yǔ)言的DML子語(yǔ)言，在SQLServer2000/2005環(huán)境下采用Transact-SQL的SELECT語(yǔ)句熟練掌握各種單表查詢(xún)方法。二、實(shí)驗(yàn)內(nèi)容：(一)附加上次實(shí)驗(yàn)備份的StuDB數(shù)據(jù)庫(kù)和SPJ數(shù)據(jù)庫(kù)。(二)按照《數(shù)據(jù)庫(kù)系統(tǒng)概論》P82頁(yè)中的學(xué)生課程數(shù)據(jù)庫(kù)及數(shù)據(jù)實(shí)現(xiàn)以下查詢(xún)：1.查詢(xún)所有課程的課號(hào)，課程名。2.查詢(xún)計(jì)算機(jī)系年齡不到21歲的所有男生的姓名和出生年份。3.查詢(xún)已被學(xué)生所選課程的課程號(hào)。4.查詢(xún)所有有成績(jī)的學(xué)生學(xué)號(hào)和課程號(hào)，并為各列分別取相應(yīng)的中文別名。(三)按照《數(shù)據(jù)庫(kù)系統(tǒng)概論》P75頁(yè)中的SPJ數(shù)據(jù)庫(kù)及數(shù)據(jù)實(shí)現(xiàn)以下查詢(xún)：1.查詢(xún)地點(diǎn)在‘北京’、‘上?！墓?yīng)商代碼和供應(yīng)商名字。2.查詢(xún)供應(yīng)商名中包含‘方’這個(gè)漢字的供應(yīng)商信息。3.查詢(xún)零件名以‘刀’字結(jié)尾，包含三個(gè)漢字的零件信息。4.查詢(xún)使用了供應(yīng)商S1所供應(yīng)零件的工程項(xiàng)目數(shù)。5.查詢(xún)各個(gè)供應(yīng)商所供應(yīng)的零件的總數(shù)，要求對(duì)查詢(xún)結(jié)果降序顯示。6.查詢(xún)供應(yīng)了3個(gè)以上項(xiàng)目的供應(yīng)商代碼和供應(yīng)的項(xiàng)目總數(shù)。三、實(shí)驗(yàn)要求：根據(jù)實(shí)驗(yàn)內(nèi)容認(rèn)真寫(xiě)好實(shí)驗(yàn)報(bào)告，記錄每個(gè)步驟正確的Transact-SQL命令。無(wú)須記錄正確的查詢(xún)結(jié)果。但要求記錄實(shí)驗(yàn)過(guò)程中發(fā)生的有學(xué)習(xí)意義的錯(cuò)誤及錯(cuò)誤信息。四、實(shí)驗(yàn)小結(jié)

實(shí)驗(yàn)三SQLServe中的連接查詢(xún)實(shí)驗(yàn)?zāi)康模赫莆詹捎肨ransact-SQL實(shí)現(xiàn)連接查詢(xún)。掌握等值連接（自然連接）、自身連接、外連接和復(fù)合條件連接的查詢(xún)方法。實(shí)驗(yàn)內(nèi)容：按照《數(shù)據(jù)庫(kù)系統(tǒng)概論》P82頁(yè)中的學(xué)生課程數(shù)據(jù)庫(kù)和P75頁(yè)中的SPJ數(shù)據(jù)庫(kù)完成以下查詢(xún)查詢(xún)使用紅色零件的工程名稱(chēng)。查詢(xún)每個(gè)工程的信息及相應(yīng)的供應(yīng)信息（包括列出尚未被供應(yīng)零件的那些工程）。查詢(xún)供應(yīng)工程J1紅色零件的供應(yīng)商號(hào)SNO，請(qǐng)用兩種方法實(shí)現(xiàn)。求供應(yīng)工程J1零件的供應(yīng)商的完整信息。查詢(xún)使用北京供應(yīng)商供應(yīng)零件的工程信息。查詢(xún)選修3號(hào)課程且成績(jī)?cè)?5分以上的所有學(xué)生。查詢(xún)先行課的學(xué)分為4的課程信息。查詢(xún)課程與其間接先行課的名稱(chēng)。查詢(xún)其他系中比計(jì)算機(jī)科學(xué)系所有學(xué)生年齡都小的學(xué)生完整信息，用兩種方法實(shí)現(xiàn)。查詢(xún)其他系中比計(jì)算機(jī)科學(xué)系某一學(xué)生年齡大的學(xué)生姓名與年齡，用兩種方法實(shí)現(xiàn)。（可選）查詢(xún)至少用了供應(yīng)商S1所供應(yīng)的所有零件的工程號(hào)JNO，用帶EXISTS謂詞的子查詢(xún)實(shí)現(xiàn)。（可選）查詢(xún)使用了全部零件的工程號(hào)JNO，用帶EXISTS謂詞的子查詢(xún)實(shí)現(xiàn)。（可選）查詢(xún)所有使用了P3零件的工程號(hào)JNO，用帶EXISTS謂詞的子查詢(xún)實(shí)現(xiàn)。三、實(shí)驗(yàn)要求：寫(xiě)出正確的Transact-SQL命令。無(wú)須記錄正確的查詢(xún)結(jié)果。要求記錄實(shí)驗(yàn)過(guò)程中發(fā)生的有學(xué)習(xí)意義的錯(cuò)誤及錯(cuò)誤信息。四、實(shí)驗(yàn)小結(jié)

實(shí)驗(yàn)四SQLServe的數(shù)據(jù)更新、視圖一、實(shí)驗(yàn)?zāi)康模赫莆詹捎肨ransact-SQL實(shí)現(xiàn)嵌套查詢(xún)。掌握采用Transact-SQL實(shí)現(xiàn)數(shù)據(jù)更新。掌握采用Transact-SQL實(shí)現(xiàn)視圖的定義、刪除、查詢(xún)與更新。二、實(shí)驗(yàn)內(nèi)容按照《數(shù)據(jù)庫(kù)系統(tǒng)概論》P82頁(yè)中的學(xué)生課程數(shù)據(jù)庫(kù)和P75頁(yè)中的SPJ數(shù)據(jù)庫(kù)完成以下操作：（一）數(shù)據(jù)更新插入一個(gè)學(xué)生：張紅，女，200215135，信息系，20歲插入一個(gè)選課錄：200215135，1，成績(jī)未定將計(jì)算機(jī)系所有學(xué)生的年齡加1歲刪除所有200215130學(xué)生的選課記錄刪除所有姓張的同學(xué)的信息請(qǐng)自行設(shè)計(jì)案例對(duì)學(xué)生－課程數(shù)據(jù)庫(kù)的數(shù)據(jù)更新，并觀察是否有違反數(shù)據(jù)的完整性約束。（二）視圖1）創(chuàng)建一個(gè)學(xué)生成績(jī)統(tǒng)計(jì)視圖，包括學(xué)號(hào)，姓名，選課門(mén)數(shù)，平均分，最高分，最低分2）利用上題所建視圖實(shí)現(xiàn)：①查詢(xún)成績(jī)高于自己平均成績(jī)的選課記錄②查詢(xún)每個(gè)同學(xué)獲得最高分的選課記錄3）請(qǐng)為三建工程項(xiàng)目建立一個(gè)供應(yīng)情況的視圖，包括供應(yīng)商代碼（SNO）、零件代碼（PNO）、供應(yīng)數(shù)量（QTY）。針對(duì)該視圖完成下列查詢(xún)：①找出三建工程項(xiàng)目使用的各種零件代碼及其數(shù)量；②找出供應(yīng)商S1的供應(yīng)情況；三、實(shí)驗(yàn)要求寫(xiě)出正確的Transact-SQL命令。無(wú)須記錄正確的查詢(xún)結(jié)果。但要求記錄實(shí)驗(yàn)過(guò)程中發(fā)生的有學(xué)習(xí)意義的錯(cuò)誤及錯(cuò)誤信息。四、實(shí)驗(yàn)小結(jié)

實(shí)驗(yàn)五數(shù)據(jù)控制（完整性與安全性）一、實(shí)驗(yàn)?zāi)康模和ㄟ^(guò)實(shí)驗(yàn)加深對(duì)數(shù)據(jù)庫(kù)完整性與數(shù)據(jù)庫(kù)安全性的理解；掌握SQLServer對(duì)數(shù)據(jù)進(jìn)行安全性控制的方法二、實(shí)驗(yàn)內(nèi)容和要求(一)數(shù)據(jù)庫(kù)完整性某公司使用數(shù)據(jù)庫(kù)進(jìn)行內(nèi)部管理：表Employees存儲(chǔ)雇員的代號(hào)（整型，標(biāo)識(shí)位，種子1000001，增量為1，主鍵）、身份證號(hào)碼（18個(gè)字符）、名字（最長(zhǎng)20個(gè)字符）和工資等信息；表Departments存儲(chǔ)部門(mén)的部門(mén)號(hào)（2個(gè)字符，唯一，主鍵）、部門(mén)名稱(chēng)（30個(gè)字符）等信息；表Work每一行表示某雇員在某部門(mén)工作過(guò)及其開(kāi)始工作時(shí)間和備注。請(qǐng)寫(xiě)出創(chuàng)建這三個(gè)表的SQL語(yǔ)句，要保證：工資的值大于0，身份證號(hào)碼唯一，開(kāi)始工作時(shí)間非空，默認(rèn)值為當(dāng)前時(shí)間。(二)以sa登錄查詢(xún)分析器，輸入下列代碼并執(zhí)行第1行execsp_addlogin's1','123'第2行useStuDB第3行execsp_grantdbaccess's1','u1'第4行g(shù)rantselect,insert,updateonstudenttopublic第5行g(shù)rantallonstudenttou1第6行revokeselectonstudenttou1問(wèn)：1）第1行創(chuàng)建了一個(gè)名為s1登錄帳戶(hù),請(qǐng)問(wèn)'123'的含義是什么？2）第3行將登錄帳戶(hù)s1映射到數(shù)據(jù)庫(kù)用戶(hù)u1上，它將作為哪個(gè)數(shù)據(jù)庫(kù)的用戶(hù)？3）請(qǐng)解釋4到6行的含義4）在查詢(xún)分析器中以s1帳戶(hù)連接服務(wù)器，能否對(duì)學(xué)生－課程數(shù)據(jù)庫(kù)的student表進(jìn)行select操作，為什么？（提示：廢除權(quán)限是刪除已授予的權(quán)限，并不妨礙用戶(hù)、組或角色從更高級(jí)別繼承已授予的權(quán)限。因此，如果廢除用戶(hù)查看表的權(quán)限，不一定能防止用戶(hù)查看該表，因?yàn)橐褜⒉榭丛摫淼臋?quán)限授予了用戶(hù)所屬的角色。）5)切換回sa用戶(hù)的查詢(xún)窗口，輸入下面代碼并執(zhí)行denyselectonstudenttou1請(qǐng)問(wèn)該代碼含義？再切換至s1的查詢(xún)窗口，能不能對(duì)student表進(jìn)行查詢(xún)操作？為什么？(三)請(qǐng)完成以下操作,并記錄完整語(yǔ)句。在實(shí)驗(yàn)過(guò)程注意驗(yàn)證語(yǔ)句的執(zhí)行效果。(1)需要為學(xué)生－課程數(shù)據(jù)庫(kù)，創(chuàng)建一用戶(hù)，該用戶(hù)以“SQLServer身份驗(yàn)證”方式登錄SQLServer服務(wù)器的賬號(hào)為學(xué)生自己的姓名，密碼為“123”(2)給該登錄用戶(hù)映射到數(shù)據(jù)庫(kù)用戶(hù)user2上，并賦予該用戶(hù)對(duì)student表的sno、sname兩列的查詢(xún)權(quán)限，對(duì)sc表的所有操作權(quán)限及對(duì)course的查詢(xún)權(quán)限。（請(qǐng)先執(zhí)行revokeselectonstudentfrompublicRevokeallonscfrompublic注：新建用戶(hù)自動(dòng)被加入到public角色中）(3)請(qǐng)分析、驗(yàn)證數(shù)據(jù)庫(kù)用戶(hù)user2的權(quán)限。(4)用戶(hù)user2授予對(duì)course表的插入操作權(quán)限，并允許將此權(quán)限授予其它用戶(hù)(5)收回授予user2對(duì)sc表的所有權(quán)限(6)創(chuàng)建一個(gè)角色R1(7)然后使用GRANT語(yǔ)句，使角色R1擁有SC表的SELECT、UPDATE、INSERT(8)將這個(gè)角色授予user2。使它具有角色R1所包含的全部權(quán)限。三、實(shí)驗(yàn)要求：寫(xiě)出正確的Transact-SQL命令。無(wú)須記錄正確的查詢(xún)結(jié)果。要求記錄實(shí)驗(yàn)過(guò)程中發(fā)生的有學(xué)習(xí)意義的錯(cuò)誤及錯(cuò)誤信息。四、實(shí)驗(yàn)小結(jié)

實(shí)驗(yàn)六語(yǔ)法元素與流程控制一、實(shí)驗(yàn)?zāi)康模赫莆誘ransact-SQL局部變量的聲明、賦值和使用方法以及全局變量的運(yùn)用方法，掌握Transact-SQL的局部變量的作用域概念。掌握Transact-SQL的控制流語(yǔ)言的使用方法。掌握與控制流語(yǔ)言語(yǔ)句一起使用的其它Transact-SQL語(yǔ)句：

CASE、/*...*/（注釋?zhuān)?-（注釋?zhuān)?、PRINT。二、實(shí)驗(yàn)內(nèi)容和要求1、使用局部變量、全局變量(1)定義一個(gè)int的整型變量，為其賦值45，并顯示變量的值。(2)定義一個(gè)長(zhǎng)度為20的可變長(zhǎng)度型字符變量，為其賦值“WelcometoFJUT”，并顯示變量的值。(3)在批處理中聲明兩個(gè)局部變量＠ssex和@sdept并對(duì)它們賦值，對(duì)Student表進(jìn)行查詢(xún)，要求兩局部變量在SELECT語(yǔ)句的WHERE子句中被使用，查詢(xún)的要求是性別為‘女’或所在系為‘IS’系的那些學(xué)生。(4)查詢(xún)當(dāng)前數(shù)據(jù)庫(kù)服務(wù)器名。(5)查詢(xún)當(dāng)前數(shù)據(jù)庫(kù)管理系統(tǒng)版本。(6)在student表中執(zhí)行所有學(xué)生年齡加1歲語(yǔ)句，并用@@ROWCOUNT來(lái)檢測(cè)是否有發(fā)生更改的行。2． 函數(shù)的使用(1)數(shù)學(xué)函數(shù)的使用分別用函數(shù)求出-3的絕對(duì)值，16的平方根，5的三次方。(2)字符串函數(shù)的使用1）分別將字符串'china'、'MACHINE'轉(zhuǎn)換成大寫(xiě)、小寫(xiě)字母；2）去掉字符串'machine'左邊的空格，右邊與'press'連接起來(lái)；(3)日期、時(shí)間函數(shù)的使用1）返回系統(tǒng)當(dāng)前日期并以整數(shù)形式返回當(dāng)前日期的年份、月份、日；2）返回給定日期“2006-2-21(4)系統(tǒng)函數(shù)與元數(shù)據(jù)函數(shù)的使用顯示正在使用的用戶(hù)名3．編寫(xiě)較復(fù)雜的Transact-SQL程序在SELECT語(yǔ)句中，CASE搜索函數(shù)允許根據(jù)比較值在結(jié)果集內(nèi)對(duì)值進(jìn)行替換。要求仿照《數(shù)據(jù)庫(kù)概論》的sc表，根據(jù)學(xué)生的成績(jī)范圍將成績(jī)（grade列）顯示為文本注釋。（要求：＜60分，文本注釋為“不及格”，60－70，“及格”，70－80，“中”，80－90，“良”，90－100，“優(yōu)”）。用Transact－SQL語(yǔ)言編寫(xiě)程序計(jì)算1～100之間所有能被7整除的數(shù)的總和。自選實(shí)例，實(shí)踐并掌握Transact-SQL以下控制流語(yǔ)言的使用方法：

BEGIN...END、WHILE、BREAK、CONTINUE、IF...ELSE、WAITFOR自選實(shí)例，實(shí)踐并掌握以下Transact-SQL語(yǔ)句：/*...*/（注釋?zhuān)?-（注釋?zhuān)?、PRINT三、實(shí)驗(yàn)要求：寫(xiě)出正確的Transact-SQL命令。無(wú)須記錄正確的查詢(xún)結(jié)果。要求記錄實(shí)驗(yàn)過(guò)程中發(fā)生的有學(xué)習(xí)意義的錯(cuò)誤及錯(cuò)誤信息。報(bào)告書(shū)寫(xiě)內(nèi)容：內(nèi)容1的腳本及1（4）的結(jié)果；內(nèi)容2的腳本及結(jié)果；內(nèi)容3（1）（2）的腳本。四、實(shí)驗(yàn)小結(jié)

實(shí)驗(yàn)七存儲(chǔ)過(guò)程與用戶(hù)自定義函數(shù)一、實(shí)驗(yàn)?zāi)康模赫莆誗QLServer中存儲(chǔ)過(guò)程的使用方法。掌握SQLServer中用戶(hù)自定義函數(shù)的使用方法。二、實(shí)驗(yàn)內(nèi)容和要求(一)存儲(chǔ)過(guò)程1、對(duì)學(xué)生課程數(shù)據(jù)庫(kù)，編寫(xiě)2個(gè)存儲(chǔ)過(guò)程，分別完成下面功能：

1）統(tǒng)計(jì)某一門(mén)課的成績(jī)分布情況，即按照各分?jǐn)?shù)