《數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用》基礎(chǔ)實(shí)驗(yàn)指導(dǎo)

《《數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用》基礎(chǔ)實(shí)驗(yàn)指導(dǎo)》由會(huì)員分享，可在線閱讀，更多相關(guān)《《數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用》基礎(chǔ)實(shí)驗(yàn)指導(dǎo)（48頁(yè)珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

1、《數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用》基礎(chǔ)實(shí)驗(yàn)指導(dǎo) 實(shí)驗(yàn)環(huán)境 1．軟件需求 （1）操作系統(tǒng)：Windows 2000 Professional，或者Windows XP （2）數(shù)據(jù)庫(kù)管理系統(tǒng)：SQL Server2000 （3）應(yīng)用開發(fā)工具：自選VC++、visual Studio、myEclipse等 （4）其它工具：Word 2．硬件需求 （1）PC機(jī) （2）網(wǎng)絡(luò)環(huán)境 基本需求信息 一、對(duì)某商場(chǎng)采購(gòu)銷售管理進(jìn)行調(diào)研后，得到如下基本需求信息： 該商場(chǎng)有多名工作人員（主要是采購(gòu)員和銷售員），主要負(fù)責(zé)從供應(yīng)商處采購(gòu)商品，而后將商品銷售給客戶。采購(gòu)員主要負(fù)責(zé)根據(jù)商場(chǎng)的銷售情況確定要

2、采購(gòu)的商品，并與供應(yīng)商聯(lián)系，簽訂采購(gòu)單。銷售員主要負(fù)責(zé)將采購(gòu)來(lái)的商品銷售給客戶，顯然一個(gè)客戶一次可能購(gòu)買多種商品。一個(gè)供應(yīng)商可以向該商場(chǎng)供應(yīng)多種商品，而一種商品也可以由多個(gè)供應(yīng)商供應(yīng)。 商場(chǎng)的管理者每個(gè)月需要對(duì)該月已采購(gòu)的商品和已銷售的商品進(jìn)行分類統(tǒng)計(jì)，對(duì)采購(gòu)員和銷售員的業(yè)績(jī)進(jìn)行考核，對(duì)供應(yīng)商和客戶進(jìn)行等級(jí)評(píng)定，并計(jì)算商場(chǎng)利潤(rùn)。 二、E-R圖 三、需要建立的數(shù)據(jù)表如下 1． 供應(yīng)商表：供應(yīng)商ID，供應(yīng)商名稱，地區(qū)，信譽(yù)等級(jí) 2． 供應(yīng)表：供應(yīng)商ID，商品ID，商品單價(jià) 3． 商品表：商品ID，商品名稱，商品庫(kù)存量，商品均價(jià) 4． 采購(gòu)單表：采購(gòu)單ID，采購(gòu)員ID，供應(yīng)商

3、ID，采購(gòu)總金額，簽訂日期 5． 采購(gòu)明細(xì)表：采購(gòu)單ID，商品ID，采購(gòu)數(shù)量，商品單價(jià) 6． 銷售單表：銷售單ID，銷售員ID，客戶ID，銷售總金額，簽訂日期 7． 銷售明細(xì)表：銷售ID，商品ID，銷售數(shù)量，商品單價(jià)，單價(jià)折扣 8． 客戶表：客戶ID，客戶名稱，聯(lián)系電話，客戶等級(jí) 9． 職員表：職員ID，職員姓名，職員類型 實(shí)驗(yàn)一SQL Server安裝及管理工具使用 一、實(shí)驗(yàn)?zāi)康呐c要求 1． 熟悉SQL Server的基本安裝與配置過(guò)程 2． 熟悉SQL Server企業(yè)管理器的主要功能、對(duì)數(shù)據(jù)庫(kù)的組織和管理方法 3． 熟悉SQL Server查詢分析器的主要功能、對(duì)

4、數(shù)據(jù)庫(kù)的組織和管理方法 3．掌握基本表、主碼等基本概念 4．掌握SQL語(yǔ)言的數(shù)據(jù)定義功能，要求能使用企業(yè)管理器和查詢分析器兩種方法來(lái)建立數(shù)據(jù)庫(kù)、以及數(shù)據(jù)表。 5．掌握修改和刪除表結(jié)構(gòu)的方法 二、實(shí)驗(yàn)內(nèi)容 1．學(xué)習(xí)使用SQL Server的基本管理工具：企業(yè)管理器和查詢分析器 2．掌握SQL Server默認(rèn)安裝的各個(gè)數(shù)據(jù)庫(kù)的主要作用 （1）master數(shù)據(jù)庫(kù) （2）model數(shù)據(jù)庫(kù) （3）msdb數(shù)據(jù)庫(kù) （4）tempdb數(shù)據(jù)庫(kù) 3．使用企業(yè)管理器和查詢分析器建立、刪除數(shù)據(jù)庫(kù) （1）建立一個(gè)名稱為supermarket的數(shù)據(jù)庫(kù) （2）查看與新生成的

5、數(shù)據(jù)庫(kù)相關(guān)的內(nèi)容 （3）刪除名稱為supermarket的數(shù)據(jù)庫(kù) 點(diǎn)擊supermarket右鍵，選擇“刪除操作”。 4． 使用企業(yè)管理器和查詢分析器建立、刪除數(shù)據(jù)表 create database supermarket drop database supermarket （1）在已經(jīng)建立的supermarket數(shù)據(jù)庫(kù)的基礎(chǔ)上，建立以下數(shù)據(jù)表（參考附錄A）： （a）商品表 （b）供應(yīng)商表 （c）供應(yīng)表 （d）采購(gòu)單表 （e）采購(gòu)明細(xì)表 （f）銷售單表 （g）銷售明細(xì)表 （h）客戶表 （i）職員表 （2）使用企業(yè)管理器查看新生成表的結(jié)構(gòu)和內(nèi)

6、容 commodity customer Employee sale saleDetail Stock stockDetail Supplyier supplying （3）分別使用企業(yè)管理器和查詢分析器修改表結(jié)構(gòu) （a）在商品表中加入描述商品信息的屬性 a.在企業(yè)管理器下 b.在查詢分析器下 ALTER TABLE commodity ADD characteristics varchar(50) NULL （b）將客戶表中的客戶聯(lián)系電話（CUtelephone）的數(shù)據(jù)類型改為varchar(20) （4）分

7、別使用企業(yè)管理器和查詢分析器刪除基本表 （a）使用企業(yè)管理器刪除客戶表 點(diǎn)擊customer右鍵，點(diǎn)擊刪除操作 （b）使用查詢分析器刪除商品表 drop table customer 三、自我測(cè)試 1． 使用企業(yè)管理器新建一個(gè)名稱為“test”的數(shù)據(jù)庫(kù) 2． 使用查詢分析器在test數(shù)據(jù)庫(kù)建立以下數(shù)據(jù)表 （1）學(xué)生表：學(xué)號(hào)，姓名，性別，出生日期，系別 （2）課程表：課程編號(hào)，課程名稱，學(xué)分 （3）選課表：學(xué)號(hào)，課程號(hào)，成績(jī) create database test create table student ( studentNo char(7) N

8、OT NULL, sname char(20) NULL, sext char(10) NULL, brithday datetime NULL, sDepartment char(20) NULL ) create table course ( courseNo char(10) NOT NULL, cname char(20) NULL, creditHour numeric(1) default 0 NULL ) create table Scourse ( courseNo

9、 char(10) NOT NULL, studentNo char(7) NOT NULL, grade int NULL ) 3． 使用查詢分析器對(duì)表進(jìn)行修改 （1） 將學(xué)生表中的性別數(shù)據(jù)類型改為整型 alter table student alter column sext int （2） 為學(xué)生表增加一個(gè)新的屬性列，用于存放學(xué)生的專業(yè)信息 alter table student add professional varchar(50) NULL 實(shí)驗(yàn)二 數(shù)據(jù)插入、修改和刪除 一、實(shí)驗(yàn)?zāi)康呐c要求 1．掌

10、握SQL Server的數(shù)據(jù)更新功能，能使用INSERT、UPDATE、DELETE命令對(duì)數(shù)據(jù)表中的數(shù)據(jù)進(jìn)行更新 2．掌握INSERT…SELECT和SELECT…INTO語(yǔ)句的使用方法 二、實(shí)驗(yàn)內(nèi)容 1．使用INSERT命令向基本表中插入數(shù)據(jù)（參考附錄B） （1）向供應(yīng)商表中插入如下數(shù)據(jù)記錄： insert into supplier values ( A001,神州數(shù)碼有限公司,北京,1 ) （2）向商品表中插入如下數(shù)據(jù)記錄 （3）向職員表中插入如下數(shù)據(jù)記錄 （4）向供應(yīng)表中插入如下數(shù)據(jù)記錄 （5）使用企業(yè)管理器分別向客戶表、采購(gòu)表、采購(gòu)明

11、細(xì)表、銷售表、銷售明細(xì)表中添加相應(yīng)記錄。 客戶表 采購(gòu)單表 采購(gòu)明細(xì)表 銷售單表 銷售明細(xì)表 2．使用UPDATE命令修改基本表中的數(shù)據(jù) 1）將編號(hào)為‘F70006’的商品的單價(jià)改為12.00元 update supplying set price=12.00 where cid=F70006 （2）將名稱為‘家樂?！目蛻舻牡燃?jí)改為VIP客戶。 update customer set CUlevel=2 where CUname=家福樂 （3） 將編號(hào)為‘A102’ 供應(yīng)商供應(yīng)商品的價(jià)格全部打八折 update saleDet

12、ail set SDdiscount = 0.8 from supplying a, commodity b, saleDetail c where a.Cid=b.Cid and a.Sid=A102 and c.Cid=b.Cid 3．使用DELETE命令刪除基本表中的數(shù)據(jù) （1）刪除采購(gòu)明細(xì)表中的所有記錄 delete from stockDetail （2）從采購(gòu)單表中刪除2005年以前簽訂的采購(gòu)單 delete from stock where year(CGdate)<=2005 4．使用SELECT…INTO語(yǔ)句向表中成批插入記錄

13、 （1）將供應(yīng)商‘A102’供應(yīng)的商品和供應(yīng)價(jià)格存到一個(gè)名字為‘A102_commodity’的表中 create table A102_commodity ( Sid char(10), --供應(yīng)商ID Cid char(10), --商品ID price float, --商品單價(jià) PRIMARY KEY (Sid,Cid) ) insert into A102_commodity select * from supplying where Sid=A102 （2）將所有的VIP客戶信息存儲(chǔ)到一個(gè)名字為‘VIP_Customer’的表中 cre

14、ate table VIP_Customer ( CUid char(10) PRIMARY KEY, --客戶ID CUname char(30), --客戶姓名 CUtelephone char(16), --客戶聯(lián)系電話 CUlevel int --客戶等級(jí)：1，普通客戶；2，VIP客戶 ) insert into VIP_Customer select * from customer where CUlevel=2 5．使用INSERT…SELECT語(yǔ)句向表中成批插入記錄 （1）新建一個(gè)名稱為‘a(chǎn)chievement’的表，其屬性包括銷售單ID，銷

15、售單總金額，銷售日期 create table achievement ( SAid char(12) PRIMARY KEY, --銷售單ID SAmoney float ,--銷售單總金額 SAdate datetime ,---銷售日期 ) （2）將編號(hào)為‘S0002’的銷售員簽訂的銷售單信息存儲(chǔ)到achievement表中。 insert into achievement(SAid,SAmoney,SAdate) select SAid,SAmoney,SAdate from sale where SAid in ( select SAid f

16、rom sale where Eid= S0002) 三、自我測(cè)試 1．從采購(gòu)明細(xì)表中刪除所有從供應(yīng)商‘A153’處采購(gòu)的編號(hào)為‘F70006’商品的明細(xì)信息 delete from stockDetail where Cid in(select Cid from stockDetail where Cid=F70006) 2．從銷售明細(xì)表中刪除2005年以前簽訂的銷售單的銷售明細(xì)記錄 delete from saleDetail where SAid in(select b.SAid from saleDetail a,sale b where a.SAid=b.SAid

17、 and year(SAdate)<2005) 實(shí)驗(yàn)三 單表查詢 一、實(shí)驗(yàn)?zāi)康呐c要求 1．掌握基本的SELECT…FROM語(yǔ)句的用法 2．能應(yīng)用SELECT語(yǔ)句及其子句解決常見的查詢問題 3．掌握模糊查詢的使用方法 4．能應(yīng)用聚集函數(shù)和GROUP BY子句進(jìn)行統(tǒng)計(jì)查詢 二、實(shí)驗(yàn)內(nèi)容 1． 查找所有商品的名稱和庫(kù)存信息 2． select Cname,Cstorage 3． from commodity 4． 5． 查找‘北京’地區(qū)的供應(yīng)商 6． select * 7． from supplier 8． where Sarea=北京 9．

18、查找所有銷售人員的基本信息 select * from Employee 10． 查找簽訂日期在‘2005-5-1’和‘2006-3-1’之間簽訂的采購(gòu)單的基本信息 select * from stock where CGdate between 2005-5-1and2006-3-1 11． 查找2006年簽訂的所有銷售單，要求顯示銷售單ID、供應(yīng)商ID和銷售單總金額 select SAid,Cuid,SAmoney from sale where year(SAdate)=2006 12． 查找每個(gè)采購(gòu)員所簽訂的采購(gòu)單的數(shù)目，顯示結(jié)果按采購(gòu)單數(shù)目降序排

19、序 select Eid,count(Eid)采購(gòu)單數(shù)目 from stock group by Eid order by count(Eid) desc 13． 查詢每個(gè)銷售員簽訂的銷售單總金額 select Eid,sum(SAmoney)銷售單總金額 from sale, group by Eid 14． 查找2005年與每個(gè)客戶簽訂的銷售單的總金額，只顯示總金額大于500000萬(wàn)的客戶ID和總金額 select CUid,sum(SAmoney)銷售單總金額 from sale group by CUid,SAmoney,SAdate having y

20、ear(SAdate)=2005 AND sum(SAmoney)>500000 15． 查詢2005年簽訂的金額最大的一筆銷售單，要求顯示采購(gòu)員ID、客戶ID和銷售金額 select Eid,CUid,SAmoney from sale where SAmoney in(select max(SAmoney) from sale where year(SAdate)=2005 ) 10． 查詢2005年與編號(hào)為‘A002’的供應(yīng)商簽訂的所有采購(gòu)單的總金額 select Sid,sum(CGmoney) from stock where year(CGdate)=

21、2005 AND Sid=A002 group by Sid 11． 查詢名稱中含有‘機(jī)’字的所有商品的ID、名稱和庫(kù)存量 select Cid,Cname,Cstorage from commodity where Cname like%機(jī)% 12． 查找第二個(gè)漢字是‘翔’的商品名稱和單價(jià)。 select Cname,price from supplying a,commodity b where a.Cid=b.Cid and Cname like _翔% 三、自我測(cè)試 1．查詢2005年簽訂的銷售合同總額排名前5名的銷售員的ID和合同總額，要求結(jié)

22、果按照合同總額降序排列 select top 5 Eid銷售員ID,sum(SAmoney)合同總額 from sale where year(SAdate)=2005 group by Eid order by sum(SAmoney)desc 2．查詢至少供應(yīng)了3種商品的供應(yīng)商的ID select Sid, count(cid)商品種數(shù) from stockDetail a ,stock b where a.CGid=b.CGid group by a.CGid ,Sid having count(Cid)>=3 4． 查詢?cè)?006

23、年至少已經(jīng)與2名銷售員簽訂了合同的客戶ID select CUid,count(SAid)簽訂合同次數(shù) from sale WHERE year(SAdate)=2006 GROUP BY CUid having count(SAid)>=2 實(shí)驗(yàn)四 表連接查詢 一、實(shí)驗(yàn)?zāi)康呐c要求 1．掌握表連接查詢的使用方法 2．掌握子查詢的使用方法 3．能使用內(nèi)連接、左外連接、右外連接以及交叉連接解決相關(guān)問題 4．熟悉系統(tǒng)函數(shù)的使用 二、實(shí)驗(yàn)內(nèi)容 1．查找每一個(gè)供應(yīng)商供應(yīng)的商品的名稱，要求顯示供應(yīng)商名稱和商品名稱。 select Sid,Cname from s

24、upplying a ,commodity b where a.Cid=b.Cid 2．使用連接查詢查找與姓名為‘李云’的采購(gòu)員簽訂采購(gòu)單的供應(yīng)商的名稱。 select Sname from stock a ,supplier b,Employee c where a.Sid=b.Sid and a.Eid=c.Eid and Ename=李云 3．使用關(guān)鍵字IN完成查詢，要求查找與姓名為‘李云’的采購(gòu)員簽訂采購(gòu)單的供應(yīng)商的名稱。 select Sname from supplier where Sname in ( select Sname from st

25、ock a ,supplier b,Employee c where a.Sid=b.Sid and a.Eid=c.Eid and Ename=李云 ) 4．使用左外連接完成如下查詢：要求查找所有采購(gòu)員簽訂的采購(gòu)合同的詳細(xì)信息。 select a.Eid,CGid,Sid,CGmoney,CGdate from Employee a left join stock b on a.Eid=b.Eid where Etype=2 5．使用左外連接完成如下查詢：查找所有客戶購(gòu)買的商品詳細(xì)信息，要求顯示客戶名稱， 商品名稱，銷售數(shù)量，商品單價(jià)，沒有購(gòu)買商品的客戶

26、也要顯示。 6．請(qǐng)使用內(nèi)連接完成如下查詢：查找每一個(gè)供應(yīng)商供應(yīng)的商品的種類，要求顯示供應(yīng)商名稱，供應(yīng)的商品的種類。 7．查找購(gòu)買了編號(hào)為‘A001’的供應(yīng)商供應(yīng)的商品的客戶名稱 select CUname from supplying b,commodity c,saleDetail d,sale e,customer f where b.Cid=c.Cid and c.Cid=d.Cid and d.SAid=e.SAid and e.CUid=f.CUid and b.Sid=A002 8．查找銷售員‘王良’在2005年簽訂的銷售合同的詳細(xì)信息。

27、 select c.SAid,Cid,SDnumber,SDprice,SDdiscount from Employee a ,sale b,saleDetail c where a.Eid=b.Eid and b.SAid=c.SAid and Ename=王良and year(SAdate)=2005 三、自我測(cè)試 1．使用右外連接完成如下查詢：要求查找所有采購(gòu)員簽訂的采購(gòu)合同的詳細(xì)信息，沒有簽訂采購(gòu)單的采購(gòu)員也要顯示 2．查找購(gòu)買了名稱為‘聯(lián)想集團(tuán)’的供應(yīng)商供應(yīng)的商品的客戶名稱 select distinct a.Sname,CUname from supplier

28、 a ,supplying b,commodity c,saleDetail d,sale e,customer f where a.Sid=b.Sid and b.Cid=c.Cid and c.Cid=d.Cid and d.SAid=e.SAid and e.CUid=f.CUid and a.Sname=聯(lián)想集團(tuán) 實(shí)驗(yàn)五 表綜合查詢使用 一、實(shí)驗(yàn)?zāi)康呐c要求 1．能熟練應(yīng)用SELECT語(yǔ)句及其相關(guān)字句 2．能將SELECT與系統(tǒng)函數(shù)、IF ELSE、WHILE等語(yǔ)句進(jìn)行綜合應(yīng)用并解決相關(guān)問題 二、實(shí)驗(yàn)內(nèi)容 1．查詢所有供應(yīng)‘鮮橙多’的供應(yīng)商的最低報(bào)價(jià)。

29、select Cname,MIN(price)最低報(bào)價(jià) from supplying a,commodity b where a.Cid=b.Cid and Cname=鮮橙多 group by a.Cid,Cname 2．查找銷售員‘王良’在2005年簽訂的所有銷售合同中每一類商品的總金額。 select distinct sum(SAmoney)同一類商品總金額,c.Eid,e.Cname from saleDetail a,sale b,Employee c,commodity e where a.SAid=b.SAid and c.Eid=b.Eid and e.Ci

30、d=a.Cid and Ename=王良and year(SAdate)=2005 group by a.Cid,c.Eid,e.Cname 3．匯總由姓名為‘劉明’的采購(gòu)員在2005年采購(gòu)的‘?dāng)?shù)碼相機(jī)’的總金額。 select b.Ename,d.Cname,sum(CGmoney)總金額 from stock a,Employee b,stockDetail c,Commodity d where a.Eid=b.Eid and c.Cid=d.Cid and a.CGid=c.CGid and year(CGdate)=2005 and Ename=劉明and Cnam

31、e=數(shù)碼相機(jī) group by b.Ename,d.Cname 5． 匯總由姓名為‘劉明’的采購(gòu)員在2005年采購(gòu)的各類商品的數(shù)量。 select Ename,SDnumber from Employee a,stockDetail b,stock c where a.Eid=c.Eid and b.CGid=c.CGid and year(CGdate)=2005 and Ename=劉明 group by SDnumber, Ename 6． 查找沒有供應(yīng)任何一類商品的供應(yīng)商的名字。 select Sname from supplier a where not

32、 exists ( select a.Sid from supplying b where a.Sid=b.Sid ) 7． 查找在2006年各個(gè)客戶購(gòu)買商品的總金額，要求結(jié)果按照購(gòu)買商品的總金額降序排序 select b.CUid,sum(SAmoney)購(gòu)買總金額 from sale a,customer b where a.CUid=b.CUid and year(SAdate)=2006 group by b.CUid order by sum(SAmoney)desc 8． 請(qǐng)使用左連接完成以下查詢：查找每一個(gè)銷售人員銷售的商品的詳細(xì)信息，要求顯示銷

33、售人員姓名、銷售單ID、客戶姓名、商品名稱、銷售數(shù)量、和銷售單價(jià)。 8．查找每個(gè)采購(gòu)員和每個(gè)供應(yīng)商簽訂的合同的總金額，要求顯示采購(gòu)員姓名、供應(yīng)商名稱、和簽訂合同的總金額。 三、自我測(cè)試 1．查找編號(hào)為‘A002’的供應(yīng)商沒有供應(yīng)的商品的名稱 select Cname from supplying a,commodity b where a.Cid=b.Cid and Cname not in (select Cname from commodity x,supplying y where x.Cid=y.Cid and Sid=A00

34、2) 2．分別使用UNION、和INNER JOIN關(guān)鍵字完成以下查詢：查詢由‘三高計(jì)算機(jī)公司’和‘聯(lián)想集團(tuán)’供應(yīng)商的所有商品的商品ID。 select Cid from supplier a,supplying b where Sname=三高計(jì)算機(jī)公司and a.Sid=b.Sid union select Cid from supplier a,supplying b where Sname=聯(lián)想集團(tuán)and a.Sid=b.Sid 實(shí)驗(yàn)六 視 圖 一、實(shí)驗(yàn)?zāi)康呐c要求 1．熟悉視圖的概念，以及視圖與表的聯(lián)系和區(qū)別 2．掌握視圖的定義方法 3．掌握對(duì)視圖

35、的查詢操作 4．掌握對(duì)視圖的更新操作，并了解視圖的更新條件 5．掌握同時(shí)對(duì)視圖和表進(jìn)行操作的方法 二、實(shí)驗(yàn)內(nèi)容 1．定義一個(gè)名稱為‘supplying_view’的視圖，要求其對(duì)應(yīng)的數(shù)據(jù)是由‘天天食品公司’供應(yīng)的商品ID、商品單價(jià)。 create view supplying_view as select Sname,Cid,price from supplier a,supplying b where Sname=天天食品公司 select* from supplying_view 2．查詢采購(gòu)員‘李云’和供應(yīng)商‘天天食品公司’簽訂的所有采購(gòu)

36、單的采購(gòu)單ID、采購(gòu)金額、簽訂日期，并將其建立為一個(gè)名稱為‘stock_view_1’的視圖。 create view stock_view_1 as select Ename,Sname,b.CGid,CGmoney,CGdate from stock a,stockDetail b,Employee c,supplier d where a.CGid=b.CGid and a.Sid=d.Sid and a.Eid=c.Eid and Ename=李云 and Sname=天天食品公司 select * from stock_view_1 3．建立一個(gè)

37、名稱為‘commodity_100’的視圖，其對(duì)應(yīng)所有的庫(kù)存量小于100的商品的信息。 create view commodity_100 as select * from commodity where Cstorage<100 select * from commodity_100 4．使用視圖supplying_view和相關(guān)基本表查找由供應(yīng)商‘天天食品公司’供應(yīng)的商品ID、商品名稱和商品單價(jià)。 select b.Cid,price,Cname from supplying_view a,commodity b where a.Cid=b.Cid

38、 5．使用視圖‘stock_view_1’和相關(guān)基本表查詢‘李云’和供應(yīng)商‘神州數(shù)碼有限公司’簽訂的所有采購(gòu)單的采購(gòu)明細(xì)信息，要求顯示采購(gòu)單ID、商品ID、商品名稱、采購(gòu)數(shù)量、和商品單價(jià)。 6．使用視圖commodity_100添加一條新的商品信息（HS7990, 華碩筆記本電腦, 30, 7900.00），并總結(jié)在哪些情況下可以對(duì)視圖進(jìn)行更新，在哪些情況下不能對(duì)視圖進(jìn)行更新。 insert into commodity_100 values (HS7990, 華碩筆記本電腦, 30, 7900.00) select * from commodity_100 總

39、結(jié)：當(dāng)Cstorage屬性值大于100，拒絕該插入操作 三、自我測(cè)試 1．建立一個(gè)視圖，其對(duì)應(yīng)的數(shù)據(jù)是在2005年簽訂的銷售單總額超過(guò)100萬(wàn)的銷售員的I D和姓名。 create view sale_view as select a.Eid,Ename from Employee a,sale b where a.Eid=b.Eid and year(SAdate)=2005 and SAmoney>1000000 select * from sale_view 2．使用上述建立的視圖和NOT IN關(guān)鍵字查詢?cè)?005年簽訂的銷售單總額沒有超過(guò)100萬(wàn)的銷售員的

40、I D和姓名。 select Eid,Ename from Employee where Etype=2 and Eid not in ( select Eid from sale_view ) 實(shí)驗(yàn)七 觸發(fā)器與存儲(chǔ)過(guò)程 一、實(shí)驗(yàn)?zāi)康呐c要求 1． 熟悉觸發(fā)器和存儲(chǔ)過(guò)程的用途 2． 掌握游標(biāo)的定義和使用方法 3． 掌握觸發(fā)器和存儲(chǔ)過(guò)程的定義以及使用方法 4． 能使用觸發(fā)器和存儲(chǔ)過(guò)程解決相關(guān)問題 二、實(shí)驗(yàn)內(nèi)容 1．定義一個(gè)觸發(fā)器，其主要用于在supplying表中插入一條記錄時(shí)，檢查是否該記錄對(duì)應(yīng)的供應(yīng)商ID和商品ID已經(jīng)存在于供應(yīng)商表（Supp

41、lier）和商品表（Commodity）中，如果存在就插入，否則不插入。 2．在supplying表中插入一條記錄（M0073, UU505, 1660.0），執(zhí)行該插入語(yǔ)句后，到企業(yè)管理器中查看該條記錄是否已插入，并解釋原因。 3．定義一個(gè)觸發(fā)器，其主要用于在supplier表中刪除記錄時(shí)，將該供應(yīng)商供應(yīng)的所有商品的信息一起刪除。 4．在企業(yè)管理器中查看表supplier和表supplying的當(dāng)前內(nèi)容，在查詢分析器中通過(guò)SQL語(yǔ)句刪除表supplier的一條記錄，例如（A102, 聯(lián)想集團(tuán), 北京, 1），再次通過(guò)企業(yè)管理器查看表supplier和表supplying的內(nèi)

42、容，看是否有所變化，解釋變化的原因。 5．分別在查詢分析器中執(zhí)行系統(tǒng)存儲(chǔ)過(guò)程sp_databases和sp_tables，解釋這兩個(gè)存儲(chǔ)過(guò)程的作用。 6．創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，其根據(jù)指定的客戶ID來(lái)返回該客戶在2006年簽訂的所有銷售單的明細(xì)情況。并使用該存儲(chǔ)過(guò)程查詢編號(hào)為‘KB001’的客戶在2006年簽訂的所有銷售單的明細(xì)情況。 三、自我測(cè)試 1．定義一個(gè)觸發(fā)器，用于當(dāng)supplier表中某個(gè)供應(yīng)商的ID（SID）被修改后，將供應(yīng)表（supplying）和采購(gòu)單表（stock）中對(duì)應(yīng)的供應(yīng)商ID也修改過(guò)來(lái)。修改supplier表中編號(hào)為‘A001’的供應(yīng)商ID為‘BMW

43、01’，查看供應(yīng)表和采購(gòu)單表中相應(yīng)記錄的變化。 create trigger k00dupt on supplier for update as if update(Sid) begin declare @Sid char(10) declare cur_supplier cursor for select Sid from Deleted open cur_supplier begin transaction fetch cur_s

44、upplier into @Sid while(@@fetch_status=0) begin update supplying set supplying.Sid=I.Sid from Inserted I,Deleted D where D.Sid=supplying.Sid fetch cur_supplier into @Sid

45、 update stock set stock.Sid=I.Sid from Inserted I,Deleted D where D.Sid=stock.Sid fetch cur_supplier into @Sid end commit tran close cur_supplier deallocate cu

46、r_supplier end 2．創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，其根據(jù)指定的客戶名稱來(lái)返回該客戶在2006年簽訂的所有銷售單的總金額。并使用該存儲(chǔ)過(guò)程查詢編號(hào)為‘KB002’的客戶在2006年簽訂的所有銷售單的總金額。 create procedure procedure_customer(@CUid char(30),@SAccount float output) as begin select @SAccount=sum(SAmoney) from sale where CUid=@CUid and year(SAdate

47、)=2006 -- group by CUid end declare @SAccount float execute procedure_customer KB002 , @SAccount output select @SAccount 實(shí)驗(yàn)九 SQLServer服務(wù)器配置與安全管理 一、實(shí)驗(yàn)?zāi)康呐c要求 1．掌握SQL Server服務(wù)器的基本配置方式 2．能使用企業(yè)管理器進(jìn)行鏈接服務(wù)器的配置，并能進(jìn)行初步地分布式查詢 3．熟悉SQL Server安全機(jī)制， 配置SQL Server的身份驗(yàn)證模式 4．掌握角色、登錄、

48、用戶、許可權(quán)限等概念，并能利用企業(yè)管理器進(jìn)行建立以及角色地分配 二、實(shí)驗(yàn)內(nèi)容 1． 注冊(cè)SQL Server服務(wù)器 48 圖9-1 2． 設(shè)置SQL Server服務(wù)器的基本屬性 圖9-2 3． 使用SQL Server服務(wù)器與客戶端網(wǎng)絡(luò)使用工具配置客戶端和服務(wù)器端的網(wǎng)絡(luò)協(xié)議 圖 9-3 4． 使用企業(yè)管理器配置鏈接服務(wù)器并在查詢分析器中進(jìn)行分布式查詢 圖9-4 5．為SQL Server服務(wù)器創(chuàng)建新的登錄，并為之分配相應(yīng)的數(shù)據(jù)庫(kù)角色和數(shù)據(jù)庫(kù)訪問權(quán)限 圖9-6 6．為Supermarket數(shù)據(jù)庫(kù)創(chuàng)建新的數(shù)據(jù)庫(kù)角色，并設(shè)置相應(yīng)的許可權(quán)限

49、 圖9-7 7．為supermarket數(shù)據(jù)庫(kù)定義新的數(shù)據(jù)庫(kù)用戶，并為之指定登錄名、數(shù)據(jù)庫(kù)角色和許可權(quán)限 圖9-8 三、自我測(cè)試 1．使用新創(chuàng)建的登錄，通過(guò)查詢分析器登錄到SQL Server服務(wù)器上，在查詢分析器上進(jìn)行相應(yīng)地操作以驗(yàn)證許可權(quán)限分配的情況。 實(shí)驗(yàn)十 數(shù)據(jù)庫(kù)備份與恢復(fù) 一、實(shí)驗(yàn)?zāi)康呐c要求 1. 了解數(shù)據(jù)庫(kù)備份與恢復(fù)的作用 2. 掌握數(shù)據(jù)庫(kù)備份與恢復(fù)的類型與方法 3. 學(xué)習(xí)使用企業(yè)管理器進(jìn)行數(shù)據(jù)庫(kù)的完全備份、差異備份以及日志備份 4. 掌握數(shù)據(jù)庫(kù)還原和恢復(fù)的區(qū)別 5. 學(xué)習(xí)使用已有的數(shù)據(jù)庫(kù)備份和日志備份對(duì)數(shù)據(jù)庫(kù)進(jìn)行恢復(fù) 5．初步掌握使用查詢分

50、析器進(jìn)行數(shù)據(jù)庫(kù)備份和恢復(fù)的方法 二、實(shí)驗(yàn)內(nèi)容 1．使用企業(yè)管理進(jìn)行supermarket數(shù)據(jù)庫(kù)的完全備份 2．使用企業(yè)管理器進(jìn)行supermarket數(shù)據(jù)庫(kù)的差異備份 3．使用剛建立的supermarket數(shù)據(jù)庫(kù)的完全備份和差異備份進(jìn)行數(shù)據(jù)庫(kù)的還原和恢復(fù) 三、自我測(cè)試 1． 稍微修改supermarket數(shù)據(jù)庫(kù)中的內(nèi)容，使用企業(yè)管理器建立supermarket數(shù)據(jù)庫(kù)的完全備份和日志備份，并使用它們進(jìn)行數(shù)據(jù)庫(kù)恢復(fù)。 2．在查詢分析器中使用命令進(jìn)行簡(jiǎn)單地?cái)?shù)據(jù)庫(kù)備份和恢復(fù)。 實(shí)驗(yàn)十一 數(shù)據(jù)傳輸 一、實(shí)驗(yàn)?zāi)康呐c要求 1．了解導(dǎo)入導(dǎo)出數(shù)據(jù)的作用 2．使用DTS工

51、具在不同的數(shù)據(jù)源之間進(jìn)行數(shù)據(jù)的導(dǎo)入導(dǎo)出 3．掌握bcp等基本工具的使用 二、實(shí)驗(yàn)內(nèi)容 1． 使用DTS將supermarket中的數(shù)據(jù)導(dǎo)入到其它SQL Server數(shù)據(jù)庫(kù)中 2． 使用DTS將supermarket中的數(shù)據(jù)導(dǎo)入到Excel表中 3． 使用DTS將supermarket中的數(shù)據(jù)導(dǎo)入到文本文件中 4．使用DTS將外部數(shù)據(jù)源數(shù)據(jù)源導(dǎo)入到SQL Server數(shù)據(jù)庫(kù)中 三、自我測(cè)試 1． 練習(xí)使用bcp實(shí)用工具 2． 練習(xí)使用BULK INSERT語(yǔ)句 實(shí)驗(yàn)十二 數(shù)據(jù)庫(kù)設(shè)計(jì)與應(yīng)用實(shí)例開發(fā) 一、實(shí)驗(yàn)?zāi)康呐c要求 1． 學(xué)習(xí)powerde

52、signer /rose等工具的使用，并能使用它進(jìn)行初步的數(shù)據(jù)庫(kù)建模 2． 掌握C/S(B/S)數(shù)據(jù)庫(kù)系統(tǒng)的基本開發(fā)流程 二、實(shí)驗(yàn)內(nèi)容 1．使用ERWin對(duì)數(shù)據(jù)庫(kù)進(jìn)行建模，并能將ER模型導(dǎo)入到SQL Server2000的數(shù)據(jù)庫(kù)中 2．進(jìn)行系統(tǒng)開發(fā) 三、自我測(cè)試 其他可選題目： 1． SQL Server性能優(yōu)化與事件探查器 2． SQL Server代理與自動(dòng)化管理 3． SQL Server事務(wù)設(shè)置 附錄A 建表SQL語(yǔ)句 --1供應(yīng)商表 CREATE TABLE supplier( Sid char(10) PRIMARY

53、KEY, --供應(yīng)商ID Sname char(30), --供應(yīng)商名稱 Sarea char(30), --供應(yīng)商地區(qū) Scredit int --供應(yīng)商信譽(yù)等級(jí):1,良好；2，一般；3，較差 ) --2 供應(yīng)表 CREATE TABLE supplying( Sid char(10), --供應(yīng)商ID Cid char(10), --商品ID price float, --商品單價(jià) PRIMARY KEY (Sid,Cid) ) --3 商品表 CREATE TABLE commodity( Cid char(10) PRIMARY K

54、EY, --商品ID Cname char(30), --商品名稱 Cstorage int, --商品庫(kù)存量 avgPrice float --商品均價(jià) ) --4 采購(gòu)單表 CREATE TABLE stock( CGid char(12) PRIMARY KEY, --采購(gòu)單ID Eid char(6), --采購(gòu)員ID Sid char(10), --供應(yīng)商ID CGmoney float, --采購(gòu)金額 CGdate DateTime --簽訂日期 ) --5 采購(gòu)明細(xì)表 CREATE TABLE stockDetail( CG

55、id char(12), --采購(gòu)單ID Cid char(10), --商品ID SDnumber int, --采購(gòu)數(shù)量 SDprice float, --采購(gòu)單價(jià) PRIMARY KEY(CGid,Cid) ) --6 銷售表 CREATE TABLE sale( SAid char(12) PRIMARY KEY, --銷售單ID Eid char(6), --銷售員ID CUid char(10), --客戶ID SAmoney float, --銷售金額 SAdate DateTIme, --簽訂日期 ) --7 采購(gòu)明細(xì)表

56、 CREATE TABLE saleDetail( SAid char(12), --銷售單ID Cid char(10), --商品ID SDnumber int, --銷售數(shù)量 SDprice float, --銷售單價(jià) SDdiscount float, --折扣 PRIMARY KEY(SAid,Cid) ) --8 客戶表 CREATE TABLE customer( CUid char(10) PRIMARY KEY, --客戶ID CUname char(30), --客戶姓名 CUtelephone char(16), --客戶聯(lián)

57、系電話 CUlevel int --客戶等級(jí)：1，普通客戶；2，VIP客戶 ) --9 職員表 CREATE TABLE Employee( Eid char(6) PRIMARY KEY, --職員ID Ename char(8), --職員姓名 Etype int --職員類型：1，管理人員；2，采購(gòu)人員；3，銷售人員 ) 附錄B 表初始化SQL語(yǔ)句 --1 供應(yīng)商表 INSERT INTO supplier VALUES (A102,聯(lián)想集團(tuán),北京,1) INSERT INTO supplier VALUES (A002,三高計(jì)算機(jī)公司,上

58、海,1) INSERT INTO supplier VALUES (A106,瑪永制衣公司,廣州,2) INSERT INTO supplier VALUES (A153,天天食品公司,南寧,2) INSERT INTO supplier VALUES (A508,統(tǒng)率咨詢公司,武漢,3) INSERT INTO supplier VALUES (A001,神州數(shù)碼有限公司,北京,1) --2 商品表 INSERT INTO commodity VALUES (P0166,天翔PC機(jī),60,5800.00) INSERT INTO com

59、modity VALUES (P9006,路由器,12,18800.00) INSERT INTO commodity VALUES (PR4000,小型機(jī),12,210000.00) INSERT INTO commodity VALUES (H6880,聯(lián)想1088手機(jī),100,2400.00) INSERT INTO commodity VALUES (H6122,數(shù)碼相機(jī),36,3600.00) INSERT INTO commodity VALUES (S2008,財(cái)務(wù)軟件,10, 8000.00) INSERT INTO commodity

60、 VALUES (F70006,旺旺米餅,200,16.00) INSERT INTO commodity VALUES (F72500,牛奶,1000,46.00) INSERT INTO commodity VALUES (F70109,鮮橙多,600,6.00) --3 供應(yīng)表 INSERT INTO supplying VALUES (A102,PR4000,210000.0) INSERT INTO supplying VALUES (A102,P9006,18800.00) INSERT INTO supplying VALUES (A10

61、2,P0166,5800.0) INSERT INTO supplying VALUES (A001,H6880,2400.00) INSERT INTO supplying VALUES (A001,H6122,3600.00) INSERT INTO supplying VALUES (A002,S2008,8000.00) INSERT INTO supplying VALUES (A153,F70006,16.00) INSERT INTO supplying VALUES (A153,F72500,46.00) INSERT INTO s

62、upplying VALUES (A153,F70109,6.00) --4 職員表 INSERT INTO employee VALUES(M0001,田明,1) INSERT INTO employee VALUES(G0001,李云,2) INSERT INTO employee VALUES(G0002,劉明,2) INSERT INTO employee VALUES(G0003,張順,2) INSERT INTO employee VALUES(S0001,王良,3) INSERT INTO employee VALUES(S00

63、02,楊成,3) INSERT INTO employee VALUES(S0003,陳君,3) --4 采購(gòu)單表 INSERT INTO stock VALUES(T20051018,G0001,A153,18000.00,2005-10-18) INSERT INTO stock VALUES(T20040912,G0001,A153,26000.00,2004-9-12) INSERT INTO stock VALUES(T20050501,G0002,A001,2000000.00,2005-5-1) INSERT INTO stock VAL

64、UES(T20060320,G0002,A102,26000000.00,2006-3-20) INSERT INTO stock VALUES(T20060115,G0002,A102,3600000.00,2006-1-15) INSERT INTO stock VALUES(T20040410,G0003,A002,60000.00,2004-4-10) --6 采購(gòu)明細(xì)表 INSERT INTO stockDetail VALUES(T20051018,F70109,400,6.00) INSERT INTO stockDetail VALUES(T2

65、0051018,F72500,650,46.00) INSERT INTO stockDetail VALUES (T20051018,F70006,200,16.00) INSERT INTO stockDetail VALUES(T20040912,F70109,200,6.00) INSERT INTO stockDetail VALUES(T20040912,F72500,350,46.00) INSERT INTO stockDetail VALUES (T20050501,H6880,100,2400.00) INSERT INTO stockDetail VALUES (T20050501,H6122,36,3600.00) INSERT INTO stockDetail VALUES(T20060320,PR4000,12,210000.00) INSERT INTO stockDetail VALUES(T20060115,P0166,60,5800.00) INSERT INTO stoc