數(shù)據(jù)庫上機(jī)實(shí)驗(yàn)及指導(dǎo)

1、實(shí)驗(yàn)環(huán)境： Microsoft SQL Server 2000或 Microsoft SQL Server 2005 實(shí)驗(yàn)?zāi)康模菏炀毷褂肧QL定義子語言、操縱子語言命令語句；掌握關(guān)系模型上的完整性約束機(jī)制； 掌握一定的數(shù)據(jù)庫系統(tǒng)管理技術(shù)。 實(shí)驗(yàn)要求： 完成所有上機(jī)題 （ 選做“三、數(shù)據(jù)庫系統(tǒng)管理實(shí)驗(yàn)” ），實(shí)驗(yàn)課結(jié)束時(shí)上 交實(shí)驗(yàn)報(bào)告及相關(guān)數(shù)據(jù)文件！、 SQL 數(shù)據(jù)庫語言操作實(shí)驗(yàn)1、 SQL 定義子語言實(shí)驗(yàn)1-1 利用 SQL 語句創(chuàng)建、修改、刪除數(shù)據(jù)庫。創(chuàng)建要求：數(shù)據(jù)庫 Employee 中包含一個(gè)數(shù)據(jù)庫文件 Empdat1.mdf 和一個(gè)日 志文件Emplog.ldf。其中，數(shù)據(jù)文件大小為1

2、0MB，最大為50MB，以5MB速度 增長；日志文件大小為5MB，最大為25MB，以5%速度增長。修改要求：增加第二個(gè)數(shù)據(jù)庫文件Empdat2.ndf,其中，數(shù)據(jù)文件大小為5MB， 最大為25MB，以2MB速度增長。實(shí)驗(yàn)指導(dǎo)：打開 Microsoft SQL Server 2000的“查詢分析器”，輸入以下代碼：Create Database EmployeeOn Primary（ Name=Empdat1,Filename= 你的學(xué)號 dataEmpdat1.mdf ,Size=10MB,MaxSize=50MB,FileGrowth=5MB)( Name=Empdat2,Filename=

3、 你的學(xué)號 dataEmpdat2.ndf,Size=5MB,MaxSize=25MB,FileGrowth=2MB)Log On( Name=Emplog,Filename= 你的學(xué)號 dataEmplog.ldf ,Size=5MB,MaxSize=25MB,FileGrowth=5%)按“ Ctrl+F5 ”執(zhí)行語句，或點(diǎn)擊快捷圖標(biāo)欄的“V”圖標(biāo)執(zhí)行之，完成數(shù)據(jù) 庫的創(chuàng)建附：增加、修改、刪除數(shù)據(jù)或日志文件用以下命令：ALTER DATABASE 數(shù)據(jù)庫名ADD FILE vfilespec,n| MODIFY FILE | REMOVE FILE 邏輯文件名| ADD LOG FILE,

4、n| MODIFY NAME= 新數(shù)據(jù)庫名:=( NAME= 邏輯文件名， NEWNAME= 新邏輯文件名 ,FILENAME= OS文件的路徑及名字,SIZE=文件的初始大小， MAXSIZE= 最大的文件尺寸 ， FILEGROWTH= 文件大小增量 )1- 2利用SQL創(chuàng)建人員表person月薪表salary及部門表dept。要求：按表1-1、表1-2及表1-3中的字段說明創(chuàng)建表1-1 perso n表結(jié)構(gòu)字段名數(shù)據(jù)類型字段長度允許空否字段說明P noChar6Not Null工號，主鍵P n ameVarchar10Not Null姓名SexChar2Not Null性別BirthDa

5、teDatetime8Null出生日期ProfVarchar10Null職稱Dept noChar4Not Null部門代碼，外鍵（參照dept表）表1-2 Salary表結(jié)構(gòu)字段名數(shù)據(jù)類型字段長度允許空否字段說明P noChar6Not Null工號，主鍵，外鍵（參照person表）BaseDec5Null基本工資Bon usDec5Null獎(jiǎng)金，要求50Fact實(shí)發(fā)工資-基本工資+獎(jiǎng)金Mon thIntNot Null月份表1-3 dept表結(jié)構(gòu)字段名數(shù)據(jù)類型1字段長度允許空否字段說明Dept noChar4Not Null部門代碼，主鍵Dn ameVarchar10Not Null部門名

6、稱1-3利用SQL語句創(chuàng)建視圖。要求：在基表person上創(chuàng)建員工視圖Person View，其中包含工號、姓名、 性別、職稱和部門代碼等字段。1-4創(chuàng)建索引。要求：在人員表的姓名列上創(chuàng)建一個(gè)單列索引n ame_sort 在人員表的出生日期列和姓名列上創(chuàng)建一個(gè)組合索引birth_name 在人員表的姓名列上創(chuàng)建一個(gè)惟一索引u_name_sort 在月薪表的實(shí)發(fā)列上創(chuàng)建一個(gè)聚簇索引fact_idx1-5刪除索引。要求：刪除月薪表上的索引fact_idx2、SQL操縱子語言實(shí)驗(yàn)2-1利用SQL語句向表person salary和dept中插入數(shù)據(jù)。要求：按表2-1、表2-2及表2-3中的數(shù)據(jù)插入表

7、2-1 表person中的數(shù)據(jù)P noP n ameSexBirthDateProfDept no000001王云:男1973-4-7中級0001000002謝志文男1975-2-14中級0001000003李浩然男1970-8-25高級0002000004廖小玲女1979-8-6初級0002000005梁玉瓊:女1970-8-25中級0003000006羅向東男1979-5-11初級0003000007肖家慶男1963-7-14高級0003表2-2 表salary中的數(shù)據(jù)P noBaseBon usFactMon th0000012100300100000218003001000003280

8、0280100000425002501000005230027510000061750130100000724002101表2-3 表dept中的數(shù)據(jù)Dept noDn ame0001:人事部:0002財(cái)務(wù)部0003市場部2- 2用SQL語句修改表中的數(shù)據(jù)要求：將salary表中工號為000006的員工工資增加為1800,獎(jiǎng)金增加為1602-3 用 SQL 語句刪除表中的數(shù)據(jù)。要求：刪除person表中工號為000010的員工數(shù)據(jù)2-4 更新視圖。要求：將員工視圖Pers on View中姓名為王云的員工職稱改為高級。2-5 向視圖插入數(shù)據(jù)。要求：向視圖 PersonView 中插入一行數(shù)據(jù)

9、（000008，劉美萍，女，中 級，0002）。2-6 刪除視圖。要求：將視圖PersonView刪除。2-7利用SQL語句查詢person表中的所有數(shù)據(jù)。2-8 條件查詢。要求： 查詢 person 表中所有不重復(fù)的職稱 查詢 person 表中職稱為中級的所有員工數(shù)據(jù) 查詢 person 表中具有高級職稱的男員工信息 查詢 person 表中姓名為王云、謝志文或羅向東的員工數(shù)據(jù)2-9 使用 ORDER BY 排序要求：利用 SQL 語句將工號在 000003 和 000008 之間的員工的月收入按實(shí) 發(fā)工資升序排列。2-10查詢工號為 000002的員工的基本工資增加 2倍，獎(jiǎng)金增加 1.

10、5倍后的實(shí)際收入2-11利用 SQL 語句查詢各部門的實(shí)發(fā)工資總數(shù)2-12利用 SQL 語句查詢 1 月份發(fā)放獎(jiǎng)金平均數(shù)大于 200元的部門，并從低 到高排序。2-13查詢?nèi)耸虏克袉T工信息。2-14查詢person表中職稱為中級的員工信息。2-15 查詢每個(gè)員工 1 月份的工資和獎(jiǎng)金。2-16查詢比工號為 000005的員工實(shí)發(fā)工資高的所有員工信息。2-17查詢比部門 0003所有員工實(shí)發(fā)工資都高的員工。2- 1 8查詢實(shí)發(fā)工資比平均實(shí)發(fā)工資高的員工代碼及實(shí)發(fā)。2-19 使用 UNION 查詢。要求：利用 SQL 語句分別查詢具有高、中級職稱的職工姓名，合并輸出。二、數(shù)據(jù)庫完整性實(shí)驗(yàn)1、表本

11、身的完整性1-1 利用 T-SQL 語句在表定義時(shí)指定默認(rèn)、 創(chuàng)建默認(rèn)、查看默認(rèn)和刪除默認(rèn)。 要求：刪除表salary中的列mo nth,然后修改表salary并增加列mon th,并將其默認(rèn)值設(shè)為 2 創(chuàng)建默認(rèn) prof_default 并與 prof 列綁定 查看默認(rèn) prof_default 將默認(rèn) prof_default 解除綁定,然后將它刪除實(shí)驗(yàn)指導(dǎo)： ALTER TABLE salaryDROP COLUMN monthGOALTER TABLE salaryADD month INT NOT NULL DEFAULT 2 CREATE DEFAULT prof_default

12、AS 高級 EXEC sp_bindefault pro_default, f Sp_help prof_default EXEC sp_unbindefault fDROP DEFAULT prof_default1-2 創(chuàng)建規(guī)則、刪除規(guī)則。要求： 創(chuàng)建規(guī)則 sex_rule 并與 sex 列綁定，指定性別列的值為男或女 將規(guī)則 sex_rule 解除綁定，然后將它刪除 實(shí)驗(yàn)指導(dǎo)： CREATE RULE sex_rule AS sex IN（ 男， 女 ）Sp_bindrule sex_rule, person.sex Sp_unbindrule p

13、erson.sexDROP RULE sex_rule1-3 定義檢查約束、查看表的定義、刪除檢查約束。要求： 將表 salary 的 bonus 列定義為檢查約束，并限制其值不小于 50 向已有表salary中增加一個(gè)檢查約束 base_check，限制base列 的值在 0-5000 之間 查看對表 salary 結(jié)構(gòu)的定義 刪除表 salary 中的限制 base_check實(shí)驗(yàn)指導(dǎo)： ALTER TABLE salary ADD CONSTRAINT bonus_check CHECK(bonus=50) ALTER TABLE salary WITH NOCHECK ADD CONS

14、TRAINT base_check CHECK(base BETWEEN 0 AND 5000) EXEC sp_help salary ALTER TABLE salary DROP CONSTRAINT base_check1-4 惟一性約束的使用。要求：將dept表中的dname列定義為惟一性約束將person表的p_no列和p_name列聯(lián)合定義為惟一性約束 實(shí)驗(yàn)指導(dǎo)： ALTER TABLE dept ADD CONSTRAINT unique_dname UNIQUE(dname) ALTER TABLE person ADD CONSTRAINT unique_pno_pname

15、 UNIQUE(pno,pname)2、表間參照完整性2-1 定義外鍵約束。要求： 創(chuàng)建表時(shí)將表 person 的 deptno 列定義為外鍵，并參考表 dept 的 列 deptno將 salary 表中的 p_no 設(shè)為外鍵，并使其參照表 person 中的 p_no 實(shí)驗(yàn)指導(dǎo)： ALTER TABLE person ADD CONSTRAINT deptno_FK FOREIGN KEY(deptno) REFERENCES dept(deptno)2-2 測試對主表進(jìn)行插入、更新及刪除操作時(shí)的影響。要求： 向表 dept 中插入一行數(shù)據(jù) ( 0004，研發(fā)部 ) ，測試是否影 響從表。

16、將表 dept 中的部門號 0003改為 0006，測試是否影響從表。 刪除表 dept 中部門號為 0001 的員工數(shù)據(jù)，測試是否影響從表。2-3 測試對從表進(jìn)行插入、更新及刪除操作時(shí)的影響。要求： 向表 person 中插入一行數(shù)據(jù) ( 000012，宋全禮 ，男， 1980-7-17 ，初級，0005) ，測試是否違背參照完整性。將表 person 中工號為 000002 的員工所在部門更新為 0005，測試 是否違背參照完整性。 刪除表 person 中工號為 000005 的員工數(shù)據(jù)，測試是否違背參照 完整性。三、存儲過程與觸發(fā)器實(shí)驗(yàn)3-1 觸發(fā)器的應(yīng)用在salary表上創(chuàng)建一個(gè) U

17、PDATE類型的觸發(fā)器 tr_update_salary，當(dāng)對salary表進(jìn)行 更新操作時(shí)，能顯示“有 ?條記錄被修改！ ”(記錄代碼)(2)編寫 SQL 語句， 實(shí)現(xiàn)“將基本工資少于 2000 的員工工資修改為 2000元”，請觀察語 句執(zhí)行后系統(tǒng)的提示信息。 (記錄代碼，結(jié)果截圖)實(shí)驗(yàn)指導(dǎo)：(1) CREATE TRIGGER tr_update_salaryON salaryFOR UPDATEASDECLARE msg varchar ( 50 )SELECT msg = 有 +STR( ROWCOUN)+T 條記錄被修改 SELECT msgRETURN3-2 級聯(lián)觸發(fā)器的使用CR

18、EATE TRIGGER pub_del ON publishers FOR DELETE ASIF rowcount=0RETURNDELETE titles FROM titles t, deleted d WHERE t.pub_id=d.pub_id這是一段創(chuàng)建觸發(fā)器的代碼，該觸發(fā)器的功能表示，當(dāng)刪除了 publishers 表中的數(shù)據(jù)后，級 聯(lián)刪除 titles 表中對應(yīng)的行，類似于外鍵約束中的 CASCADE 行為。請模仿以上代碼，創(chuàng)建 觸發(fā)器tr_delete_person,要求：當(dāng)person表中某些記錄刪除時(shí)，salary表的相應(yīng)記錄也級聯(lián) 刪除，并編寫相應(yīng)的 SQL 語句驗(yàn)

19、證你的觸發(fā)器是否起作用。3- 2 預(yù)習(xí)并完成教材第 19章 19.1節(jié)存儲過程的例題四、數(shù)據(jù)庫系統(tǒng)管理實(shí)驗(yàn)1-1 用企業(yè)管理器備份和恢復(fù)數(shù)據(jù)庫。要求： 創(chuàng)建備份設(shè)備 PubsBac 備份數(shù)據(jù)庫 Pubs 刪除數(shù)據(jù)庫Pubs中的employee表 備份數(shù)據(jù)庫 Pubs 到備份設(shè)備 PubsBac 刪除數(shù)據(jù)庫Pubs的表Sales 依次恢復(fù)數(shù)據(jù)庫 Pubs實(shí)驗(yàn)指導(dǎo)： 創(chuàng)建備份設(shè)備 PubsBac 在企業(yè)管理器下，展開服務(wù)器組，然后展開服務(wù)器。展開“管理”文件夾，右擊“備份”，然后單擊“新建備份設(shè)備”命令。在出現(xiàn)的對話框“名稱”框中 輸入該命名設(shè)備的名稱PubsBac然后單擊“文件名”按紐，輸入磁盤備份設(shè)備 所使用的文件名，單擊瀏覽按紐顯示“備份設(shè)備位置”對話框，并選擇磁盤設(shè)備 所使用的本地計(jì)算機(jī)上的物理文件，單擊“確定”即可。 備份數(shù)據(jù)庫 Pubs 在企業(yè)管理器中，展開“數(shù)據(jù)庫”文件夾，右擊要備份的數(shù)據(jù)庫，指向“所有任務(wù)”子菜單，然后單擊“備份數(shù)據(jù)庫”命令。在出現(xiàn)的對話框“常規(guī)”選項(xiàng) 卡的“名稱”框內(nèi)，輸入備份集名稱，也可以在“描述”框中輸入對備份集的描 述。在“備份”選項(xiàng)下，選項(xiàng)“數(shù)據(jù)庫完全” 。在“目的”