福建工程學院《實驗指導書》

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