福建工程學(xué)院《實(shí)驗(yàn)指導(dǎo)書》

實(shí)驗(yàn)做書（本科）TOC\o"1-5"\h\z\o"CurrentDocument"實(shí)驗(yàn)一數(shù)據(jù)定義語言 1\o"CurrentDocument"實(shí)驗(yàn)二SQLSever中的單表查詢 3\o"CurrentDocument"實(shí)驗(yàn)三SQLServe中的連接查詢 4\o"CurrentDocument"實(shí)驗(yàn)四SQLServe的數(shù)據(jù)更新、視圖 5\o"CurrentDocument"實(shí)驗(yàn)五數(shù)據(jù)控制（完整性與安全性） 7\o"CurrentDocument"實(shí)驗(yàn)六語法元素與流程控制 9\o"CurrentDocument"實(shí)驗(yàn)七存儲(chǔ)過程與用戶自定義函數(shù) 11\o"CurrentDocument"實(shí)驗(yàn)八觸發(fā)器 12實(shí)驗(yàn)一數(shù)據(jù)定義語言一、 實(shí)驗(yàn)?zāi)康氖煜QLServer2000/2005查詢分析器。掌握SQL語言的DDL語言，在SQLServer2000/2005環(huán)境下采用Transact-SQL實(shí)現(xiàn)表的定義、刪除與修改，掌握索引的建立與刪除方法。掌握SQLServer2000/2005實(shí)現(xiàn)完整性的六種約束。二、 實(shí)驗(yàn)內(nèi)容啟動(dòng)SQLServer2000/2005查詢分析器，并連接服務(wù)器。創(chuàng)建數(shù)據(jù)庫：(請(qǐng)先在D盤下創(chuàng)建DB文件夾)1)在SQLServer2000中建立一個(gè)StuDB數(shù)據(jù)庫：有一個(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ù)庫。在StuDB數(shù)據(jù)庫中作如下操作：設(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)。寫出實(shí)現(xiàn)下列功能的SQL語句。創(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ù)類型改為smallint；向S表添加“入學(xué)時(shí)間(comedate)”列，其數(shù)據(jù)類型為日期型(datetime)；對(duì)表S，按年齡降序建索引(索引名為inxage)；刪除S表的inxage索引；刪除S表；在StuDB數(shù)據(jù)庫中，按照《數(shù)據(jù)庫系統(tǒng)概論》(第四版)P82頁的學(xué)生一課程數(shù)據(jù)庫創(chuàng)建STUDENT、COURSE和SC三張表，每一張表都必須有主碼約束，合理使用列級(jí)完整性約束和表級(jí)完整性。并輸入相關(guān)數(shù)據(jù)。將StuDB數(shù)據(jù)庫分離，在D盤下創(chuàng)建DB文件夾下找到StuDB數(shù)據(jù)庫的兩個(gè)文件，進(jìn)行備份，后面的實(shí)驗(yàn)要用到這個(gè)數(shù)據(jù)庫。(課外)按照《數(shù)據(jù)庫系統(tǒng)概論》(第四版)P74頁習(xí)題5的SPJ數(shù)據(jù)庫。創(chuàng)建SPJ數(shù)據(jù)庫，并在其中創(chuàng)建S、P、J和SPJ四張表。每一張表都必須有主碼約束，合理使用列級(jí)完整性約束和表級(jí)完整性。要作好備份以便后面的實(shí)驗(yàn)使用該數(shù)據(jù)庫數(shù)據(jù)。三、 實(shí)驗(yàn)要求：學(xué)會(huì)使用SQLServer2000/2005的查詢分析器，企業(yè)管理器和聯(lián)機(jī)叢書。根據(jù)實(shí)驗(yàn)內(nèi)容認(rèn)真寫好實(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中的單表查詢一、 實(shí)驗(yàn)?zāi)康模赫莆誗QL語言的DML子語言，在SQLServer2000/2005環(huán)境下采用Transact-SQL的SELECT語句熟練掌握各種單表查詢方法。二、 實(shí)驗(yàn)內(nèi)容：（一） 附加上次實(shí)驗(yàn)備份的StuDB數(shù)據(jù)庫和SPJ數(shù)據(jù)庫。（二） 按照《數(shù)據(jù)庫系統(tǒng)概論》P82頁中的學(xué)生課程數(shù)據(jù)庫及數(shù)據(jù)實(shí)現(xiàn)以下查詢：查詢所有課程的課號(hào)，課程名。查詢計(jì)算機(jī)系年齡不到21歲的所有男生的姓名和出生年份。查詢已被學(xué)生所選課程的課程號(hào)。查詢所有有成績(jī)的學(xué)生學(xué)號(hào)和課程號(hào)，并為各列分別取相應(yīng)的中文別名。（三） 按照《數(shù)據(jù)庫系統(tǒng)概論》P75頁中的SPJ數(shù)據(jù)庫及數(shù)據(jù)實(shí)現(xiàn)以下查詢：查詢地點(diǎn)在‘北京’、‘上?！墓?yīng)商代碼和供應(yīng)商名字。查詢供應(yīng)商名中包含‘方’這個(gè)漢字的供應(yīng)商信息。查詢零件名以‘刀’字結(jié)尾，包含三個(gè)漢字的零件信息。查詢使用了供應(yīng)商S1所供應(yīng)零件的工程項(xiàng)目數(shù)。查詢各個(gè)供應(yīng)商所供應(yīng)的零件的總數(shù)，要求對(duì)查詢結(jié)果降序顯示。查詢供應(yīng)了3個(gè)以上項(xiàng)目的供應(yīng)商代碼和供應(yīng)的項(xiàng)目總數(shù)。三、 實(shí)驗(yàn)要求：根據(jù)實(shí)驗(yàn)內(nèi)容認(rèn)真寫好實(shí)驗(yàn)報(bào)告，記錄每個(gè)步驟正確的Transact-SQL命令。無須記錄正確的查詢結(jié)果。但要求記錄實(shí)驗(yàn)過程中發(fā)生的有學(xué)習(xí)意義的錯(cuò)誤及錯(cuò)誤信息。四、 實(shí)驗(yàn)小結(jié)實(shí)驗(yàn)三SQLServe中的連接查詢一、 實(shí)驗(yàn)?zāi)康模赫莆詹捎肨ransact-SQL實(shí)現(xiàn)連接查詢。掌握等值連接(自然連接)、自身連接、外連接和復(fù)合條件連接的查詢方法。二、 實(shí)驗(yàn)內(nèi)容：按照《數(shù)據(jù)庫系統(tǒng)概論》P82頁中的學(xué)生課程數(shù)據(jù)庫和P75頁中的SPJ數(shù)據(jù)庫完成以下查詢查詢使用紅色零件的工程名稱。查詢每個(gè)工程的信息及相應(yīng)的供應(yīng)信息(包括列出尚未被供應(yīng)零件的那些工程)。查詢供應(yīng)工程J1紅色零件的供應(yīng)商號(hào)SNO,請(qǐng)用兩種方法實(shí)現(xiàn)。求供應(yīng)工程J1零件的供應(yīng)商的完整信息。查詢使用北京供應(yīng)商供應(yīng)零件的工程信息。查詢選修3號(hào)課程且成績(jī)?cè)?5分以上的所有學(xué)生。查詢先行課的學(xué)分為4的課程信息。查詢課程與其間接先行課的名稱。查詢其他系中比計(jì)算機(jī)科學(xué)系所有學(xué)生年齡都小的學(xué)生完整信息，用兩種方法實(shí)現(xiàn)。查詢其他系中比計(jì)算機(jī)科學(xué)系某一學(xué)生年齡大的學(xué)生姓名與年齡，用兩種方法實(shí)現(xiàn)。(可選)查詢至少用了供應(yīng)商S1所供應(yīng)的所有零件的工程號(hào)JNO，用帶EXISTS謂詞的子查詢實(shí)現(xiàn)。(可選)查詢使用了全部零件的工程號(hào)JNO，用帶EXISTS謂詞的子查詢實(shí)現(xiàn)。(可選)查詢所有使用了P3零件的工程號(hào)JNO，用帶EXISTS謂詞的子查詢實(shí)現(xiàn)。三、 實(shí)驗(yàn)要求：寫出正確的Transact-SQL命令。無須記錄正確的查詢結(jié)果。要求記錄實(shí)驗(yàn)過程中發(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)嵌套查詢。掌握采用Transact-SQL實(shí)現(xiàn)數(shù)據(jù)更新。掌握采用Transact-SQL實(shí)現(xiàn)視圖的定義、刪除、查詢與更新。二、 實(shí)驗(yàn)內(nèi)容按照《數(shù)據(jù)庫系統(tǒng)概論》P82頁中的學(xué)生課程數(shù)據(jù)庫和P75頁中的SPJ數(shù)據(jù)庫完成以下操作：（一）數(shù)據(jù)更新1） 插入一個(gè)學(xué)生：張紅，女，200215135，信息系，20歲2） 插入一個(gè)選課錄：200215135，1，成績(jī)未定3） 將計(jì)算機(jī)系所有學(xué)生的年齡加1歲4） 刪除所有200215130學(xué)生的選課記錄5） 刪除所有姓張的同學(xué)的信息6） 請(qǐng)自行設(shè)計(jì)案例對(duì)學(xué)生一課程數(shù)據(jù)庫的數(shù)據(jù)更新，并觀察是否有違反數(shù)據(jù)的完整性約束。usestudbinsertintostudent（sname,ssex,sno,sdept,sage）values（'張紅'，'女','200215135',，信息系，，20）;insertintosc（sno,cno,grade）values（'200215135','1',null）;updatestudentsetsage=sage+1;deletefromscwhere'200215130'=（selectsnofromstudentwherestudent.sno=sc.sno）;deletefromstudentwheresno='200215130';deletefromscdeletefromstudentwheresnamelike'張％'deletefromstudent,sc,coursewheresnamelike'張％'andsc.sno=o=o;updatecreateviews_grade學(xué)號(hào)姓名選課門數(shù)平均分最高分最低分asselectstudent.sno,sname,count(cno),avg(grade),max(grade),min(grade)fromsc,studentwherestudent.sno=sc.snogroupbystudent.sno,snamewithcheckoption;視圖創(chuàng)建一個(gè)學(xué)生成績(jī)統(tǒng)計(jì)視圖，包括學(xué)號(hào)，姓名，選課門數(shù)，平均分，最高分，最低分利用上題所建視圖實(shí)現(xiàn)：查詢成績(jī)高于自己平均成績(jī)的選課記錄查詢每個(gè)同學(xué)獲得最高分的選課記錄請(qǐng)為三建工程項(xiàng)目建立一個(gè)供應(yīng)情況的視圖，包括供應(yīng)商代碼(SNO)、零件代碼(PNO)、供應(yīng)數(shù)量(QTY)。針對(duì)該視圖完成下列查詢：找出三建工程項(xiàng)目使用的各種零件代碼及其數(shù)量；找出供應(yīng)商S1的供應(yīng)情況；三、 實(shí)驗(yàn)要求寫出正確的Transact-SQL命令。無須記錄正確的查詢結(jié)果。但要求記錄實(shí)驗(yàn)過程中發(fā)生的有學(xué)習(xí)意義的錯(cuò)誤及錯(cuò)誤信息。四、 實(shí)驗(yàn)小結(jié)實(shí)驗(yàn)五數(shù)據(jù)控制（完整性與安全性）一、 實(shí)驗(yàn)?zāi)康模和ㄟ^實(shí)驗(yàn)加深對(duì)數(shù)據(jù)庫完整性與數(shù)據(jù)庫安全性的理解；掌握SQLServer對(duì)數(shù)據(jù)進(jìn)行安全性控制的方法二、 實(shí)驗(yàn)內(nèi)容和要求（一） 數(shù)據(jù)庫完整性某公司使用數(shù)據(jù)庫進(jìn)行內(nèi)部管理：表Employees存儲(chǔ)雇員的代號(hào)（整型，標(biāo)識(shí)位，種子1000001，增量為1，主鍵）、身份證號(hào)碼（18個(gè)字符）、名字（最長(zhǎng)20個(gè)字符）和工資等信息；表Departments存儲(chǔ)部門的部門號(hào)（2個(gè)字符，唯一，主鍵）、部門名稱（30個(gè)字符）等信息；表Work每一行表示某雇員在某部門工作過及其開始工作時(shí)間和備注。請(qǐng)寫出創(chuàng)建這三個(gè)表的SQL語句，要保證：工資的值大于0，身份證號(hào)碼唯一，開始工作時(shí)間非空，默認(rèn)值為當(dāng)前時(shí)間。（二） 以sa登錄查詢分析器，輸入下列代碼并執(zhí)行第1彳亍execsp_addlogin's1','123'第2行useStuDB第3行execsp_grantdbaccess's1','u1'第4彳亍grantselect,insert,updateonstudenttopublic第5彳亍grantallonstudenttou1第6彳亍revokeselectonstudenttou1問：1） 第1行創(chuàng)建了一個(gè)名為s1登錄帳戶,請(qǐng)問'123'的含義是什么？2） 第3行將登錄帳戶s1映射到數(shù)據(jù)庫用戶u1上，它將作為哪個(gè)數(shù)據(jù)庫的用戶？3） 請(qǐng)解釋4到6行的含義4） 在查詢分析器中以s1帳戶連接服務(wù)器，能否對(duì)學(xué)生一課程數(shù)據(jù)庫的student表進(jìn)行select操作，為什么？（提示：廢除權(quán)限是刪除已授予的權(quán)限，并不妨礙用戶、組或角色從更高級(jí)別繼承已授予的權(quán)限。因此，如果廢除用戶查看表的權(quán)限，不一定能防止用戶查看該表，因?yàn)橐褜⒉榭丛摫淼臋?quán)限授予了用戶所屬的角色。）5） 切換回sa用戶的查詢窗口，輸入下面代碼并執(zhí)行denyselectonstudenttou1請(qǐng)問該代碼含義？再切換至s1的查詢窗口，能不能對(duì)student表進(jìn)行查詢操作？為什么？（三） 請(qǐng)完成以下操作，并記錄完整語句。在實(shí)驗(yàn)過程注意驗(yàn)證語句的執(zhí)行效果。（1） 需要為學(xué)生一課程數(shù)據(jù)庫，創(chuàng)建一用戶，該用戶以“SQLServer身份驗(yàn)證”方式登錄SQLServer服務(wù)器的賬號(hào)為學(xué)生自己的姓名，密碼為“123”。（2） 給該登錄用戶映射到數(shù)據(jù)庫用戶user2上，并賦予該用戶對(duì)student表的sno、sname兩列的查詢權(quán)限，對(duì)sc表的所有操作權(quán)限及對(duì)course的查詢權(quán)限。（請(qǐng)先執(zhí)彳亍revokeselectonstudentfrompublicRevokeallonscfrompublic注：新建用戶自動(dòng)被加入到public角色中請(qǐng)分析、驗(yàn)證數(shù)據(jù)庫用戶user2的權(quán)限。用戶user2授予對(duì)course表的插入操作權(quán)限，并允許將此權(quán)限授予其它用戶收回授予user2對(duì)sc表的所有權(quán)限創(chuàng)建一個(gè)角色R1⑺然后使用GRANT語句，使角色R1擁有SC表的SELECT、UPDATE、INSERT(8)將這個(gè)角色授予user2。使它具有角色R1所包含的全部權(quán)限。三、 實(shí)驗(yàn)要求：寫出正確的Transact-SQL命令。無須記錄正確的查詢結(jié)果。要求記錄實(shí)驗(yàn)過程中發(fā)生的有學(xué)習(xí)意義的錯(cuò)誤及錯(cuò)誤信息。四、 實(shí)驗(yàn)小結(jié)實(shí)驗(yàn)六語法元素與流程控制一、 實(shí)驗(yàn)?zāi)康模赫莆誘ransact-SQL局部變量的聲明、賦值和使用方法以及全局變量的運(yùn)用方法，掌握Transact-SQL的局部變量的作用域概念。掌握Transact-SQL的控制流語言的使用方法。掌握與控制流語言語句一起使用的其它Transact-SQL語句：CASE、/*...*/(注釋)、--(注釋)、PRINTo二、 實(shí)驗(yàn)內(nèi)容和要求使用局部變量、全局變量定義一個(gè)int的整型變量，為其賦值45,并顯示變量的值。定義一個(gè)長(zhǎng)度為20的可變長(zhǎng)度型字符變量，為其賦值“WelcometoFJUT”，并顯示變量的值。在批處理中聲明兩個(gè)局部變量@ssex和@sdept并對(duì)它們賦值，對(duì)Student表進(jìn)行查詢，要求兩局部變量在SELECT語句的WHERE子句中被使用，查詢的要求是性別為'女'或所在系為‘IS'系的那些學(xué)生。查詢當(dāng)前數(shù)據(jù)庫服務(wù)器名。查詢當(dāng)前數(shù)據(jù)庫管理系統(tǒng)版本。在student表中執(zhí)行所有學(xué)生年齡加1歲語句，并用@@ROWCOUNT來檢測(cè)是否有發(fā)生更改的行。函數(shù)的使用數(shù)學(xué)函數(shù)的使用分別用函數(shù)求出-3的絕對(duì)值，16的平方根，5的三次方。字符串函數(shù)的使用分別將字符串'china'、'MACHINE'轉(zhuǎn)換成大寫、小寫字母；去掉字符串’machine'左邊的空格，右邊與'press'連接起來；日期、時(shí)間函數(shù)的使用返回系統(tǒng)當(dāng)前日期并以整數(shù)形式返回當(dāng)前日期的年份、月份、日；返回給定日期“2006-2-21”與當(dāng)前日期相差的天數(shù)。系統(tǒng)函數(shù)與元數(shù)據(jù)函數(shù)的使用顯示正在使用的用戶名編寫較復(fù)雜的Transact-SQL程序在SELECT語句中，CASE搜索函數(shù)允許根據(jù)比較值在結(jié)果集內(nèi)對(duì)值進(jìn)行替換。要求仿照《數(shù)據(jù)庫概論》的sc表，根據(jù)學(xué)生的成績(jī)范圍將成績(jī)(grade歹U)顯示為文本注釋。(要求：V60分，文本注釋為“不及格”60—70，“及格”，70—80，“中”，80—90，“良”，90-100，"優(yōu)”)。用Transact-SQL語言編寫程序計(jì)算1?100之間所有能被7整除的數(shù)的總和。自選實(shí)例，實(shí)踐并掌握Transact-SQL以下控制流語言的使用方法：BEGIN...END、WHILE、BREAK、CONTINUE.IF...ELSE、WAITFOR自選實(shí)例，實(shí)踐并掌握以下Transact-SQL語句：/*...*/(注釋)、--(注釋)、PRINT三、 實(shí)驗(yàn)要求：1)寫出正確的Transact-SQL命令。2）無須記錄正確的查詢結(jié)果。要求記錄實(shí)驗(yàn)過程中發(fā)生的有學(xué)習(xí)意義的錯(cuò)誤及錯(cuò)誤信息。報(bào)告書寫內(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ǔ)過程與用戶自定義函數(shù)一、 實(shí)驗(yàn)?zāi)康模?） 掌握SQLServer中存儲(chǔ)過程的使用方法。2） 掌握SQLServer中用戶自定義函數(shù)的使用方法。二、 實(shí)驗(yàn)內(nèi)容和要求（一） 存儲(chǔ)過程1、 對(duì)學(xué)生課程數(shù)據(jù)庫，編寫2個(gè)存儲(chǔ)過程，分別完成下面功能：1） 統(tǒng)計(jì)某一門課的成績(jī)分布情況，即按照各分?jǐn)?shù)段統(tǒng)計(jì)人數(shù)，要求使用游標(biāo)。2） 將學(xué)生選課成績(jī)從百分制改為等級(jí)制（即A、B、C、D、E五級(jí)）。2、 對(duì)SPJ數(shù)據(jù)庫，1） 創(chuàng)建一個(gè)存儲(chǔ)過程ins_s_count，功能為根據(jù)提供的供應(yīng)商號(hào)，供應(yīng)商名，供應(yīng)商所在地等信息，往S表中插入數(shù)據(jù)，并返回插入該記錄之后，S表中的記錄數(shù)。2） 調(diào)用該存儲(chǔ)過程實(shí)現(xiàn)往S表中插入一條記錄（'S6','天盛'，‘40’‘福州'），并顯示插入該記錄之后，S表中的記錄數(shù)。（二） 用戶自定義函數(shù)創(chuàng)建一個(gè)返回標(biāo)