數(shù)據(jù)庫原理實(shí)驗(yàn)指導(dǎo)書(new含觸發(fā)器及存儲(chǔ)過程)

1、數(shù)據(jù)庫原理實(shí)驗(yàn)手冊(cè)實(shí)驗(yàn)一實(shí)驗(yàn)名稱：熟悉SQL SERVER 2000環(huán)境一、實(shí)驗(yàn)?zāi)康氖煜QL SERVER 2000提供的服務(wù)管理器、企業(yè)管理器、查詢分析器、客戶端和服務(wù)器端網(wǎng)絡(luò)實(shí)用工具等常用管理工具的使用。了解Windows身份驗(yàn)證和SQL Server身份驗(yàn)證這兩種連接數(shù)據(jù)庫服務(wù)器方式的不同，并能夠使用某種連接方式登陸上SQL SERVER數(shù)據(jù)庫服務(wù)器。學(xué)會(huì)使用SQL SERVER的聯(lián)機(jī)叢中尋求幫助。二、實(shí)驗(yàn)原理使用SQL SERVER 2000提供的各種常用管理工具進(jìn)行有關(guān)的操作。三、實(shí)驗(yàn)設(shè)備安裝有SQL SERVER 2000的計(jì)算機(jī)。四、實(shí)驗(yàn)內(nèi)容1 啟動(dòng)SQL SERVER 服務(wù)。

2、2 打開SQL SERVER的企業(yè)管理器，連接上SQL SERVER服務(wù)器。展開左邊樹狀窗口的各級(jí)結(jié)點(diǎn)，觀察右邊內(nèi)容窗口的變化。3 在SQL SERVER的企業(yè)管理器的樹狀窗口或內(nèi)容窗口中選擇某一項(xiàng)，點(diǎn)擊鼠標(biāo)右鍵，了解快捷菜單中的有關(guān)功能。4 使用Windows身份驗(yàn)證的連接方式打開SQL SERVER的查詢分析器，在查詢窗口用USE命令打開樣例數(shù)據(jù)庫pubs（也可以從工具欄中的數(shù)據(jù)庫下拉列表中選擇pubs數(shù)據(jù)庫）。5 在查詢窗口輸入exec sp_help，運(yùn)行后察看結(jié)果。6 在查詢窗口輸入select * from authors ，運(yùn)行后察看結(jié)果。7 在SQL SERVER的查詢分析器的

3、“幫助”菜單中點(diǎn)擊“目錄與索引”，然后在SQL SERVER的聯(lián)機(jī)叢中選“索引”，輸入要查找的關(guān)鍵字“sp_help”、“exec”、“select”，了解它們的含義。8 在SQL SERVER的企業(yè)管理器中創(chuàng)建數(shù)據(jù)庫S_T，并在該數(shù)據(jù)庫中創(chuàng)建表student、course和sc，在創(chuàng)建表的同時(shí)可以創(chuàng)建各種約束。這三個(gè)表的結(jié)構(gòu)可以參考書上85頁的例5、例6和例7。9 向各個(gè)表輸入數(shù)據(jù)，各個(gè)表的數(shù)據(jù)可參考書56頁的圖2.4。10 在SQL Server的查詢分析器中打開S_T數(shù)據(jù)庫，輸入書中91頁開始的例題，實(shí)現(xiàn)對(duì)數(shù)據(jù)庫的查詢。實(shí)驗(yàn)二實(shí)驗(yàn)名稱：創(chuàng)建和修改數(shù)據(jù)庫一、實(shí)驗(yàn)?zāi)康氖煜QL SERVER

4、的企業(yè)管理器和查詢分析器的用戶界面，掌握用企業(yè)管理器和查詢分析器創(chuàng)建數(shù)據(jù)庫，修改數(shù)據(jù)庫和刪除數(shù)據(jù)庫的方法。了解數(shù)據(jù)庫的三種文件類型：主數(shù)據(jù)文件（.mdf）、次要數(shù)據(jù)文件（.ndf）和事務(wù)日志文件（.ldf）。二、實(shí)驗(yàn)原理在企業(yè)管理器中實(shí)現(xiàn)對(duì)數(shù)據(jù)庫的管理及使用TSQL語句CREATE DATABASE、ALTER DATABASE及DROP DATABASE來實(shí)現(xiàn)數(shù)據(jù)庫的創(chuàng)建、修改及刪除。三、實(shí)驗(yàn)設(shè)備安裝有SQL SERVER 2000的計(jì)算機(jī)。四、實(shí)驗(yàn)示例1 創(chuàng)建名稱為testdb的數(shù)據(jù)庫，數(shù)據(jù)庫中包含一個(gè)數(shù)據(jù)文件，邏輯文件名為testdb_data，磁盤文件名為testdb.mdf，文件初始

5、容量為2MB，最大容量為8MB，文件容量遞增值為1MB；事務(wù)日志文件的邏輯文件名為testdb_log，磁盤文件名為testdb_log.ldf，文件初始容量為1MB，最大容量為5MB，文件容量遞增值為1MB。CREATE DATABASE testdbON( NAME=testdb_data, FILENAME='d:DATAtestdb.mdf',SIZE=2MB,MAXSIZE=8MB,FILEGROWTH=1MB )LOG ON(NAME=testdb_log, FILENAME='d:DATAtestdb_log.ldf',SIZE=1MB,MAXSI

6、ZE=5MB,FILEGROWTH=1MB )2 對(duì)testdb數(shù)據(jù)庫進(jìn)行修改：添加一個(gè)數(shù)據(jù)文件，邏輯文件名為testdb2_data，磁盤文件名為testdb2_data.ndf，文件初始容量為1MB，最大容量為5MB，文件容量遞增值為1MB。ALTER DATABASE testdbADD FILE (NAME=testdb2_data, FILENAME='d:DATAtestdb2.ndf',SIZE=1MB,MAXSIZE=5MB,FILEGROWTH=1MB )五、實(shí)驗(yàn)內(nèi)容分別使用SQL SERVER 2000企業(yè)管理器和TSQL語句，按下列要求創(chuàng)建、修改和刪除用戶

7、數(shù)據(jù)庫。1 刪除數(shù)據(jù)庫S_T，在查詢分析器中用命令采用默認(rèn)設(shè)置創(chuàng)建數(shù)據(jù)庫S_T（只需給出數(shù)據(jù)庫名）。2 創(chuàng)建名稱為company的數(shù)據(jù)庫，數(shù)據(jù)庫中包含一個(gè)數(shù)據(jù)文件，邏輯文件名為company_data，磁盤文件名為company_data.mdf，文件初始容量為5MB，最大容量為15MB，文件容量遞增值為1MB；事務(wù)日志文件的邏輯文件名為company_log，磁盤文件名為company_log.ldf，文件初始容量為5MB，最大容量為10MB，文件容量遞增值為1MB。3 對(duì)該數(shù)據(jù)庫進(jìn)行修改：添加一個(gè)數(shù)據(jù)文件，邏輯文件名為company2_data，磁盤文件名為company2_data.nd

8、f，文件初始容量為1MB，最大容量為5MB，文件容量遞增值為1MB；將日志文件company_log的最大容量增加為15MB，文件容量遞增值為2MB。4 在company數(shù)據(jù)庫中添加一個(gè)文件組TempGroup，并向該文件組中添加一個(gè)容量為3MB，最大容量為10MB，遞增量為1MB的數(shù)據(jù)文件，該數(shù)據(jù)文件的邏輯文件名為company3_data，磁盤文件名為company3_data.ndf。5 在company數(shù)據(jù)庫中刪除數(shù)據(jù)文件company2_data。6 刪除數(shù)據(jù)庫company。7 采用默認(rèn)設(shè)置創(chuàng)建數(shù)據(jù)庫company。實(shí)驗(yàn)三實(shí)驗(yàn)名稱：創(chuàng)建和修改數(shù)據(jù)表一、實(shí)驗(yàn)?zāi)康氖煜び嘘P(guān)數(shù)據(jù)表的創(chuàng)建和

9、修改等工作，理解數(shù)據(jù)庫模式的概念，了解主鍵約束、外鍵約束、UNIQUE約束和CHECK約束的創(chuàng)建和應(yīng)用。要求學(xué)生熟練掌握使用企業(yè)管理器和TSQL語句CREATE TABLE、ALTER TABLE及DROP TABLE語句對(duì)數(shù)據(jù)表進(jìn)行管理。二、實(shí)驗(yàn)原理在企業(yè)管理器中實(shí)現(xiàn)表的創(chuàng)建和修改等工作及使用TSQL語句CREATE TABLE、ALTER TABLE及DROP TABLE來實(shí)現(xiàn)數(shù)據(jù)表的創(chuàng)建、修改及刪除。三、實(shí)驗(yàn)設(shè)備安裝有SQL SERVER 2000的計(jì)算機(jī)。四、實(shí)驗(yàn)示例兩個(gè)示例：l 一個(gè)是書上的學(xué)生選課數(shù)據(jù)庫S_T，S_T數(shù)據(jù)庫中有學(xué)生表student，課程表course和學(xué)生選課表sc

10、。各表的結(jié)構(gòu)如下：學(xué)生表studentSnochar(5)Not nullprimary key學(xué)號(hào)Snamechar(20)Not null姓名Ssexchar(2)Not null性別SagesmallintNot null年齡Sdeptchar(20)Not null系別課程表courseCnochar(4)Not nullprimary key課程號(hào)Cnamechar(40)Not null課程名Cpnochar(4)先行課Ccreditsmallint Not null學(xué)分學(xué)生選課表scSnochar(5)Not nullprimary key學(xué)號(hào)Cnochar(4)Not null

11、primary key課程號(hào)Gradesmallint成績l 另一個(gè)示例是某公司的產(chǎn)品銷售數(shù)據(jù)庫company，company數(shù)據(jù)庫中存在人事表employee、客戶表customer、銷售表sales、銷售明細(xì)表sale_item、產(chǎn)品表product。各表的結(jié)構(gòu)如下：員工人事表employee emp_nochar(5)Not nullprimary key員工編號(hào)emp_namevarchar(10)Not null員工姓名sexchar(2)Not null性別deptvarchar(10)Not null所屬部門titlevarchar(10)Not null職稱date_hired

12、datetimeNot null雇傭日birthdaydatetimeNull生日salaryintNot null薪水telephonevarchar(20)Null電話addrvarchar(50)null住址客戶表customercust_idchar(5)Not nullprimary key客戶號(hào)cust_namevarchar(20)Not null客戶名稱addrvarchar(40)Not null客戶住址tel_novarchar(20)Not null客戶電話zipchar(6)null郵政編碼銷售主表sales order_nointNot nullprimary key

13、訂單編號(hào)cust_idchar(5)Not null客戶號(hào)sale_idchar(5)Not null業(yè)務(wù)員編號(hào)tot_amtnumeric(9,2)Not null訂單金額order_datedatetimeNot null訂貨日期銷貨明細(xì)表sale_item order_nointNot nullprimary key訂單編號(hào)prod_idchar(5)Not nullprimary key產(chǎn)品編號(hào)qtyintNot null銷售數(shù)量unit_pricenumeric(7,2)Not null單價(jià)order_datedatetimenull訂單日期產(chǎn)品名稱表product prod_idc

14、har(5)Not nullprimary key產(chǎn)品編號(hào)prod_namevarchar(20)Not null產(chǎn)品名稱1創(chuàng)建表及相應(yīng)的約束create table employee1(emp_no char(5) not null check(emp_no like 'E-F0-90-90-90-9'),emp_name char(10) not null,emp_sex char(1) not null check(emp_sex in ('m','f'),emp_phone char (13) not null,emp_add varch

15、ar(60) not null,emp_salary char(5) not null check (emp_salary between 60000 and 80000)go2修改表alter table employeeadd constraint ck_phone check (phone like 0-90-90-90-90-90-90-90-90-90-90-90-90-9)五、實(shí)驗(yàn)內(nèi)容分別在SQL SERVER 2000企業(yè)管理器和在查詢分析分析器中使用TSQL語句完成以下操作：l 針對(duì)S_T數(shù)據(jù)庫：1 創(chuàng)建student表，設(shè)置Sno為主鍵；在Sno列上創(chuàng)建核查約束，要求學(xué)號(hào)各個(gè)

16、位上只能取數(shù)字字符；在Ssex列上創(chuàng)建核查約束，要求性別只能取“男”或“女”；在Sage 列上創(chuàng)建核查約束，要求學(xué)生的年齡只能在14至38之間。2 創(chuàng)建course表，設(shè)置列Cno為主鍵；在Cno列上創(chuàng)建核查約束，要求課程號(hào)只能取數(shù)字字符。3 創(chuàng)建sc表，設(shè)置主鍵為Sno與Cno的組合；在Grade列上創(chuàng)建核查約束，限定成績只能在0-100。4 修改course表，在表中為Cpno列添加一外鍵約束Fk_Cpno，它參照了course表的主鍵Cno。5 修改sc表，在表中為Sno添加一外鍵約束Fk_Sno，它參照了student表中的主鍵Sno；為Cno添加外鍵約束Fk_Cno， 它參照了cou

17、rse表中的主鍵Cno。l 針對(duì)company數(shù)據(jù)庫：1 在數(shù)據(jù)庫company中創(chuàng)建以上五張表，并設(shè)置各表的主鍵。2 在銷售主表sales中添加字段“發(fā)票號(hào)碼” invoice_no，char(10)，NOT NULL。3 添加外鍵約束：a) 在銷售主表sales的業(yè)務(wù)員編號(hào)字段sale_id上添加外鍵約束，參照字段為員工表employee中的字段員工編號(hào)emp_no，約束名為FK_sale_id。b) 在銷售主表sales的客戶號(hào)字段cust_id上添加外鍵約束，參照字段為客戶表customer中的字段客戶號(hào)cust_id，約束名為FK_cust_id。c) 在銷售明細(xì)表sale_item

18、的訂單編號(hào)字段order_no上添加外鍵約束，參照字段為銷售主表sales中的字段訂單編號(hào)order_no，約束名為FK_order_no。d) 在銷售明細(xì)表sale_item的產(chǎn)品編號(hào)字段prod_id上添加外鍵約束，參照字段為產(chǎn)品名稱表product中的產(chǎn)品編號(hào)字段prod_id，約束名為FK_prod_id。4 添加核查約束：a) 將員工表employee中的薪水字段salary的值限定在1000至10000間，約束名為CK_salary。b) 將員工表employee中的員工編號(hào)字段emp_no設(shè)定為以“E”字母開頭, 后面跟5位數(shù)的編號(hào)，約束名為CK_emp_no。c) 將員工表em

19、ployee中的性別字段設(shè)定這取值只能是“男”和“女”。約束名為CK_sex。d) 將銷售主表sales中的發(fā)票號(hào)碼字段invoice_no設(shè)定為以“I”字母開頭，后面跟9位數(shù)的編號(hào)，約束名為CK_inno。5為銷售主表sales中的字段發(fā)票號(hào)碼invoice_no設(shè)置為唯一約束，約束名為UN_inno。實(shí)驗(yàn)四實(shí)驗(yàn)名稱：簡單的單表查詢一、實(shí)驗(yàn)?zāi)康氖炀氄莆沼肧ELECT語句實(shí)現(xiàn)簡單的單表查詢。掌握SELECT子句、FROM子句、WHERE子句及ORDER BY 子句的用法。二、實(shí)驗(yàn)原理用SELECT語句實(shí)現(xiàn)簡單的單表查詢。在SELECT子句中用TOP關(guān)鍵字來限制返回到結(jié)果集中的記錄數(shù)目，用DIS

20、TINCT關(guān)鍵字從結(jié)果集中去掉重復(fù)的記錄。WHERE子句中用關(guān)系比較符、NOT BETWEEN、NOT IN、LIKE、 IS NOTNULL及邏輯運(yùn)算符構(gòu)成查詢條件，對(duì)結(jié)果集中的記錄進(jìn)行篩選。ORDER BY子句將根據(jù)查詢結(jié)果集中一個(gè)或多個(gè)字段對(duì)查詢結(jié)果進(jìn)行排序。三、實(shí)驗(yàn)設(shè)備安裝有SQL SERVER 2000的計(jì)算機(jī)。四、實(shí)驗(yàn)示例1查找表中所有姓劉的職工的工號(hào)，姓名，部門，薪水select emp_no,emp_name,dept,salaryfrom employeewhere emp_name like '劉%'2查找所有定單金額高于20000的所有客戶編號(hào)select

21、 cust_idfrom saleswhere tot_amt>200003查找業(yè)務(wù)部或會(huì)計(jì)部的女員工的基本信息。select emp_no,emp_name,deptfrom employee where sex='女' and dept in ('業(yè)務(wù)','會(huì)計(jì)')4選取編號(hào)界于C0001和C0004的客戶編號(hào)、客戶名稱、客戶地址。select cust_id,cust_name,addrfrom customer where cust_id between 'C0001' AND 'C0004'5選取銷

22、售數(shù)量最多的前5條訂單訂單號(hào)、數(shù)量。select top 5 order_no,qtyfrom sale_itemorder by qty desc6計(jì)算出sale_item表中每一筆銷售數(shù)據(jù)的銷售金額，并按照銷售金額的大小排序。select prod_id,qty,unit_price,(qty*unit_price)tot_amtfrom sale_itemorder by tot_amt desc五、實(shí)驗(yàn)內(nèi)容運(yùn)行查詢文件company.sql，生成上機(jī)必要的數(shù)據(jù)，然后完成以下操作。1、 查找所有經(jīng)理的姓名、職稱、薪水。2、在銷售主表sales中查找銷售金額大于等于10000元的訂單。3、

23、在員工表employee中查找薪水在4000至8000元之間的員工。4、在員工表employee中查找住址為上海、北京、天津這三個(gè)城市的員工。5、在客戶表customer中查找住址不在上海、北京、天津這三個(gè)城市的客戶。6、在員工表employee中查找姓“王”用姓名最后一個(gè)字為“功”的員工。7、在客戶表customer中查找姓“劉”的客戶名稱、電話。8、查找出職稱為“經(jīng)理”或“職員”的女工的信息。9、查找薪水最高的前三條員工記錄。10、查找訂單金額最高的前10%的訂單記錄。11、查找員工表中所屬部門。（去掉重復(fù)記錄）12、查找員工表中的所有記錄，并按薪水由低到高進(jìn)行排序。實(shí)驗(yàn)五實(shí)驗(yàn)名稱：復(fù)雜的

24、單表查詢 一、實(shí)驗(yàn)?zāi)康氖炀氄莆誗ELECT查詢語句中的Group by 子句、Having子句的用法，以及匯總函數(shù)的使用。二、實(shí)驗(yàn)原理在查詢語句中用Group by子句進(jìn)行分組；用Having子句對(duì)分組進(jìn)行篩選。使用MAX（），MIN（），COUNT（），SUM（），AVG（）等函數(shù)在查詢結(jié)果集中生成匯總值。三、實(shí)驗(yàn)設(shè)備安裝有SQL SERVER 2000的計(jì)算機(jī)。四、實(shí)驗(yàn)示例1、統(tǒng)計(jì)表中員工的薪水在40000-60000之間的人數(shù)select count(*) as 人數(shù)from employeewhere salary between 40000 and 600002、計(jì)算'P00

25、01'產(chǎn)品的平均銷售單價(jià)select avg(unit_price)from sale_itemwhere prod_id='P0001'3、 找出目前銷售業(yè)績超過40000元的業(yè)務(wù)員編號(hào)及銷售業(yè)績，并按銷售業(yè)績從大到小排序。Select sale_id ,sum(tot_amt)from sales group by sale_id having sum(tot_amt)>40000order by sum(tot_amt) desc4、 查詢訂購了三種以上產(chǎn)品的訂單號(hào)。Select order_no from sale_itemGroup by order_n

26、oHaving count(*)>3五、實(shí)驗(yàn)內(nèi)容1、在員工表employee中統(tǒng)計(jì)員工人數(shù)。2、統(tǒng)計(jì)各部門員工的員工人數(shù)及平均薪水。3、查詢銷售業(yè)績超過10000元的員工編號(hào)。4、計(jì)算每一產(chǎn)品銷售數(shù)量總和與平均銷售單價(jià)。5、統(tǒng)計(jì)各部門不同性別、或各部門、或不同性別或所有員工的平均薪水。（在GROUP BY 子句中使用CUBE關(guān)鍵字）6、 統(tǒng)計(jì)各部門不同性別、或各部門或所有員工的平均薪水。（在GROUP BY 子句中使用ROLLUP關(guān)鍵字）7、 計(jì)算出一共銷售了幾種產(chǎn)品。8、 顯示sale_item表中每種產(chǎn)品的訂購金額總和，并且依據(jù)銷售金額由大到小排列來顯示出每一種產(chǎn)品的排行榜。9、 計(jì)

27、算每一產(chǎn)品每月的銷售金額總和，并將結(jié)果按銷售（月份，產(chǎn)品編號(hào)）排序。10、 查詢每位業(yè)務(wù)員各個(gè)月的業(yè)績，并按業(yè)務(wù)員編號(hào)、月份降序排序。實(shí)驗(yàn)六實(shí)驗(yàn)名稱：連接查詢一、實(shí)驗(yàn)?zāi)康恼莆帐褂眠B接的方法從多個(gè)表中查詢數(shù)據(jù)。理解內(nèi)連接、外連接（包括左外連接、右外連接和全外連接）、自身連接的概念和使用。要求學(xué)生熟練掌握在FROM子句和在WHERE子句中指定連接條件的這兩種方法。二、實(shí)驗(yàn)原理在查詢語句的FROM子句中用以下形式實(shí)現(xiàn)各種連接操作：l FROM 表1 INNER JOIN 表2 ON 表1列名=表2列名 （實(shí)現(xiàn)內(nèi)連接）l FROM 表1 LEFT OUTER JOIN 表2 ON 表1列名=表2列名

28、（實(shí)現(xiàn)左外連接）l FROM 表1 RIGHT OUTER JOIN 表2 ON 表1列名=表2列名 （實(shí)現(xiàn)右外連接）l FROM 表1 FULL OUTER JOIN 表2 ON 表1列名=表2列名 （實(shí)現(xiàn)全外連接）l FROM 表1 AS 別名1 JOIN 表1 AS 別名2 ON 別名1列名=別名2列名 （實(shí)現(xiàn)自身連接）在查詢語句的WHERE子句中用以下形式實(shí)現(xiàn)各種連接操作：l FROM 表1，表2 WHERE 表1列名=表2列名 （實(shí)現(xiàn)內(nèi)連接）l FROM 表1，表2 WHERE 表1列名*=表2列名 （實(shí)現(xiàn)左外連接）l FROM 表1，表2 WHERE 表1列名=*表2列名 （實(shí)現(xiàn)右

29、外連接）l FROM 表1 AS 別名1 ，表1 AS 別名2 WHERE 別名1列名=別名2列名 （實(shí)現(xiàn)自身連接）三、實(shí)驗(yàn)設(shè)備安裝有SQL SERVER 2000的計(jì)算機(jī)。四、實(shí)驗(yàn)示例1、 檢索product 表和sale_item表中數(shù)量大于2的相同產(chǎn)品的產(chǎn)品編號(hào)、產(chǎn)品名稱、數(shù)量、單價(jià)。select d_id,a.qty,a.unit_price,d_namefrom sale_item as a inner join product as b /*如果改成left join/right join 試分析結(jié)果*/on (d_id=_id) and a

30、.qty>2order by d_id2、查找出employee表中住址相同的員工的姓名、性別、職稱、薪水、住址。select a.emp_name,a.sex,a.title,a.salary,a.addr,b.emp_name,b.sex,b.title,b.salary,b.addrfrom employee as a inner join employee as bon (a.emp_no!=b.emp_no) and (a.emp_name>b.emp_name) and (a.addr=b.addr)3、查找商品名稱為14寸顯示器商品的銷售情況，顯示該商品的編

31、號(hào)、銷售數(shù)量、單價(jià)和金額select d_id,qty,unit_price,unit_price*qty totpricefrom sale_item a,product bwhere d_id=d_id and prod_name='14寸顯示器'五、實(shí)驗(yàn)內(nèi)容1、 查找出employee表中部門相同且住址相同的女員工的姓名、性別、職稱、薪水、住址。2、 檢索product 表和sale_item表中相同產(chǎn)品的產(chǎn)品編號(hào)、產(chǎn)品名稱、數(shù)量、單價(jià)。3、 檢索product 表和sale_item表中單價(jià)高于2400元的相同產(chǎn)品的產(chǎn)品編號(hào)、產(chǎn)品名稱、數(shù)量

32、、單價(jià)。4、 查詢?cè)诿繌堄唵沃杏嗁徑痤~超過24000元的客戶名及其地址。5、 查找有銷售記錄的客戶編號(hào)、名稱和訂單總額6、 每位客戶訂購的每種產(chǎn)品的總數(shù)量及平均單價(jià)，并按客戶號(hào)，產(chǎn)品號(hào)從小到大排列。7、 查找在1997年中有銷售記錄的客戶編號(hào)、名稱和訂單總額8、分別使用左向外連接、右向外連接、完整外部連接檢索product 表和sale_item表中單價(jià)高于2400元的相同產(chǎn)品的產(chǎn)品編號(hào)、產(chǎn)品名稱、數(shù)量、單價(jià)。并分析比較檢索的結(jié)果。 實(shí)驗(yàn)七實(shí)驗(yàn)名稱：嵌套查詢一、實(shí)驗(yàn)?zāi)康恼莆誗ELECT語句的嵌套使用，實(shí)現(xiàn)多表的復(fù)雜查詢，進(jìn)一步理解SELECT語句的高級(jí)使用方法。二、實(shí)驗(yàn)原理使用嵌套查詢時(shí)，先用

33、內(nèi)查詢（子查詢）挑選出部分?jǐn)?shù)據(jù)，以作為外查詢（主查詢）的數(shù)據(jù)來源或搜索條件。包含子查詢的語句通常采用以下格式：WHERE 表達(dá)式 NOT IN （子查詢）WHERE 表達(dá)式 比較運(yùn)算符 ANY|ALL （子查詢）WHERE NOT EXISTS （子查詢）其中前兩種又稱為不相關(guān)子查詢，子查詢的查詢條件不依賴其父查詢，所以可以先求出子查詢的結(jié)果，然后由內(nèi)到外逐層求解。最后一種為相關(guān)子查詢，其子查詢的查詢條件依賴于外層父查詢的某個(gè)屬性值，所以不能先一次性地求出子查詢的結(jié)果。三、實(shí)驗(yàn)設(shè)備安裝有SQL SERVER 2000的計(jì)算機(jī)。四、實(shí)驗(yàn)示例1、由employee表中查找出薪水最高的員工信息。se

34、lect *from employeewhere salary= (select max(salary ) from employee )2、由sales表中查找出訂單金額大于“E0013業(yè)務(wù)員在1996/10/15這天所接每一張訂單的金額”的所有訂單。select *from saleswhere tot_amt>all (select tot_amt from sales where sale_id='E0013'and order_date='1996/10/15')order by tot_amt3、用存在量詞查找沒有訂貨記錄的客戶名稱select

35、 cust_namefrom customer awhere not exists (select * from sales b where a.cust_id=b.cust_id)五、實(shí)驗(yàn)內(nèi)容1、 由sales表中查找出銷售金額最高的訂單。2、 由sales表中查找出訂單金額大于“E0013業(yè)務(wù)員在1996/10/15這天所接任一張訂單的金額”的所有訂單，并顯示承接這些訂單的業(yè)務(wù)員和該條訂單的金額。3、 找出公司女業(yè)務(wù)員所接的訂單。4、 找出目前業(yè)績未超過200000元的員工。5、 在銷售主表sales中查詢銷售業(yè)績最高的業(yè)務(wù)員編號(hào)及銷售業(yè)績。6、 找出目前業(yè)績超過232000元的員工編號(hào)和

36、姓名。7、 查詢訂購的產(chǎn)品至少包含了訂單10003中所訂購產(chǎn)品的訂單。8、查詢末承接業(yè)務(wù)的員工的信息。實(shí)驗(yàn)八實(shí)驗(yàn)名稱：數(shù)據(jù)更新一、實(shí)驗(yàn)?zāi)康氖炀毷褂肐NSERT/DELETE/UPDATE語句進(jìn)行表的更新操作。二、實(shí)驗(yàn)原理數(shù)據(jù)更新操作只能針對(duì)一個(gè)表進(jìn)行，即每次只能在一個(gè)表中插入、刪除和修改數(shù)據(jù)。用INSERT語句可以通過使用VALUES關(guān)鍵字一次只向一個(gè)表插入一條記錄，也可以通過子查詢向一個(gè)表中插入若干條記錄； 當(dāng)使用DELETE和UPDATE語句實(shí)現(xiàn)表的刪除和更改時(shí)，若題目中涉及多個(gè)表的信息，可以使用子查詢來挑選出部分?jǐn)?shù)據(jù)，以作為刪除或更改的條件，也可以在語句中用FROM子句給出題目中涉及的所

37、有表，而在WHERE子句中給出多個(gè)表之間的連接條件。三、實(shí)驗(yàn)設(shè)備安裝有SQL SERVER 2000的計(jì)算機(jī)。四、實(shí)驗(yàn)示例1、在產(chǎn)品名稱表中插入一條記錄。INSERT INTO product VALUES(p0009,CD_ROM)2、 將employee員工表中性別為“女”的員工的員工編號(hào)、姓名，職稱和薪水復(fù)制到一個(gè)名為temp_emp表文件中，該文件只具有這四個(gè)列。INSERT INTO temp_emoSELECT emp_no,emp_name,title,salaryFROM employeeWHERE sex=女3、 將所有經(jīng)理的薪水上調(diào)10%。UPDATE EMPLOYEESE

38、T salary=salary*1.1WHERE title=經(jīng)理4、 刪除李小明的銷售記錄。DELETE employeeFROM employee, salesWHERE employee.emp_no=sales.sale_id and emp_name=李小明或DELETE employeeWHERE emp_name=李小明 and emp_no IN ( SELECT DISTINCT sale_id FROM sales )五、實(shí)驗(yàn)內(nèi)容凡是能用多種方法實(shí)現(xiàn)的，請(qǐng)用多種方法實(shí)現(xiàn)。1、為各表添加若干條記錄，必須符合實(shí)驗(yàn)二中設(shè)定的各種約束。2、將每個(gè)員工的薪水上調(diào)10%。3、刪除sal

39、es表中作廢的訂單（其發(fā)票號(hào)碼為I000000004），其訂貨明細(xì)表中的數(shù)據(jù)也一并刪除。5、 刪除所有沒有銷售業(yè)績的員工記錄。6、 對(duì)那些只要有一筆銷售業(yè)績超過20000元的員工的薪水增加500元。實(shí)驗(yàn)九實(shí)驗(yàn)名稱：視圖的建立與使用一、實(shí)驗(yàn)?zāi)康睦斫庖晥D的定義、視圖的優(yōu)點(diǎn)與視圖的工作原理。掌握在企業(yè)管理器和查詢分析器中創(chuàng)建、修改及刪除視圖。能夠熟練掌握利用視圖向表中插入、刪除和修改數(shù)據(jù)。二、實(shí)驗(yàn)原理利用CREATE VIEW、ALTER VIEW 、DROP VIEW語句創(chuàng)建、修改及刪除視圖。由于視圖是虛表，通過視圖向表中添加數(shù)據(jù)時(shí)必須滿足一定的條件。如果視圖引用了多個(gè)數(shù)據(jù)表，那么在通過視圖向數(shù)據(jù)

40、表中添加數(shù)據(jù)時(shí)，這個(gè)語句只能指定同一個(gè)表中的字段。如果通過一個(gè)引用了多個(gè)數(shù)據(jù)表的視圖向這些數(shù)據(jù)表中添加數(shù)據(jù)時(shí)，必須書寫多個(gè)INSERT語句。通過使用多個(gè)表的視圖對(duì)數(shù)據(jù)表進(jìn)行更新也需要書寫多個(gè)UPDATE語句，另外適用于INSERT操作的許多限制同UPDATE操作。使用DELETE語句可以通過視圖將數(shù)據(jù)表中的數(shù)據(jù)刪除。但如果視圖應(yīng)用了兩個(gè)或兩個(gè)以上的數(shù)據(jù)表，則不允許刪除視圖中的數(shù)據(jù)。另外，通過視圖刪除的記錄，不能違背視圖定義的WHERE子句中的條件限制。三、實(shí)驗(yàn)環(huán)境安裝有SQL SERVER 2000的計(jì)算機(jī)。四、實(shí)驗(yàn)示例1、創(chuàng)建視圖emp_view，該視圖中包含女員工的編號(hào)、姓名和薪水。cre

41、ate view emp_view asselect emp_no,emp_name,salaryfrom employeewhere sex='女' 2、創(chuàng)建視圖sale_item_view，該視圖中包含訂單編號(hào)、訂貨日期、產(chǎn)品編號(hào)及數(shù)量。然后利用該視圖向表中插入數(shù)據(jù)。create view sale_item_view asselect sales.order_no,sales.order_date,prod_id,qtyfrom sales,sale_itemwhere sales.order_no=sale_item.order_no/*注意：插入數(shù)據(jù)時(shí)要考慮數(shù)據(jù)列是否

42、有默認(rèn)值或允許為空，否則INSERT失敗。*/insert into sale_item_view(order_no,order_date)values(10010,'1996/12/20')insert into sale_item_view(prod_id,qty)values('p0010',8)五、實(shí)驗(yàn)內(nèi)容以下操作中，有些在實(shí)現(xiàn)過程中可能會(huì)報(bào)錯(cuò)，請(qǐng)分析錯(cuò)誤原因。1、 創(chuàng)建一個(gè)視圖cust_view，該視圖只含上?？蛻粜畔?，即客戶號(hào)、客戶姓名、住址。2、 利用cust_view視圖添加一條記錄數(shù)據(jù)。（注意：分別查看customer表和該視圖的結(jié)果。）3、

43、修改cust_view視圖，該視圖只含上海客戶信息，即客戶號(hào)、客戶姓名、住址、電。4、 刪除視圖中所有姓“王”的客戶數(shù)據(jù)。5、 通過視圖修改表內(nèi)某一客戶的姓名。6、 有兩個(gè)基本表employee和sales，創(chuàng)建一個(gè)視圖，該視圖包含相同業(yè)務(wù)員的編號(hào)、姓名、訂單號(hào)、銷售總金額。7、 將上述視圖中訂單號(hào)為10001的記錄的銷售金額改為60000。8、 給上述視圖添加一條記錄數(shù)據(jù)。9、 刪除上述視圖。實(shí)驗(yàn)十實(shí)驗(yàn)名稱：存儲(chǔ)過程的建立與調(diào)用一、實(shí)驗(yàn)?zāi)康睦斫獯鎯?chǔ)過程的概念、作用、建立和調(diào)用方法。二、實(shí)驗(yàn)原理使用CREATE PROCEDURE語句創(chuàng)建存儲(chǔ)過程，ALTER PROCEDURE語句修改存儲(chǔ)過程

44、，DROP PROCEDURE語句刪除存儲(chǔ)過程，存儲(chǔ)過程有不帶參數(shù)的、有帶輸入?yún)?shù)的、有帶輸出參數(shù)(output)的，還可以有帶返回值的。創(chuàng)建好的存儲(chǔ)過程可以使用EXEC procedure_name語句執(zhí)行。三、實(shí)驗(yàn)設(shè)備安裝有SQL SERVER 2000的計(jì)算機(jī)。四、實(shí)驗(yàn)示例1、模糊查詢create procedure proc_empname E_name varchar(10) asselect a.emp_name,a.dept,b.tot_amtfrom employee a inner join sales bon a.emp_no=b.sale_id where a.emp_n

45、ame like E_namegoexec sp_empname '陳%'2、利用存儲(chǔ)過程計(jì)算出E0014業(yè)務(wù)員的銷售總金額。create procedure proc_saletot E_no char(5), p_tot int output asselect p_tot=sum(tot_amt)from saleswhere sale_id=E_no godeclare tot_amt intexec sp_saletot E0014, tot_amt outputselect tot_amt3、創(chuàng)建一帶返回值的存儲(chǔ)過程，返回某一部門的平均工資create proc pr

46、oc_avg_salary Dept char(4)asdeclare avg_salary intselect avg_salary=avg(salary)from employeewhere dept=Deptreturn avg_salary declare avg intexec avg=proc_avg_salary '人事'print '返回值='+cast(avg as char(10)五、實(shí)驗(yàn)內(nèi)容1、利用存儲(chǔ)過程，給employee表添加一條業(yè)務(wù)部門員工的信息。2、利用存儲(chǔ)過程從employee、sales、customer表的連接中返回所有業(yè)務(wù)

47、員的姓名、客戶姓名、銷售金額。3、創(chuàng)建帶一個(gè)輸入?yún)?shù)的存儲(chǔ)過程，實(shí)現(xiàn)按員工姓名進(jìn)行模糊查找，查找員工編號(hào)、訂單編號(hào)、銷售金額。4、創(chuàng)建帶兩個(gè)輸入?yún)?shù)的存儲(chǔ)過程，查找姓“李”并且職稱為“職員”的員工的員工編號(hào)、訂單編號(hào)、銷售金額。5、 利用存儲(chǔ)過程計(jì)算出訂單編號(hào)為10003的訂單的銷售金額。（帶一輸入?yún)?shù)和一輸出參數(shù)）（提示：sales表中的tot_amt應(yīng)該等于sale_item表中的同一張訂單的不同銷售產(chǎn)品的qty*unit_price之和）6、 創(chuàng)建一存儲(chǔ)過程，根據(jù)給出的職稱，返回該職稱的所有員工的平均工資。（帶一輸入?yún)?shù)和返回值）五、實(shí)驗(yàn)報(bào)告實(shí)驗(yàn)十一實(shí)驗(yàn)名稱：觸發(fā)器的建立與使用一、實(shí)驗(yàn)?zāi)?/p>

48、的理解觸發(fā)器的概念和作用；了解觸發(fā)器的分類及觸發(fā)條件；掌握觸發(fā)器的定義及應(yīng)用。二、實(shí)驗(yàn)原理1、 使用CREATE TRIGGER語句定義觸發(fā)器，ALTER TRIGGER語句修改觸發(fā)器，DROP TRIGGER語句刪除觸發(fā)器。2、 觸發(fā)器分AFTER/FOR和INSTEAD OF兩種類型：AFTER/FOR類型的觸發(fā)器是在相應(yīng)的觸發(fā)語句（insert、delete、update）執(zhí)行完后被觸發(fā)的。如果觸發(fā)語句對(duì)應(yīng)的表上有完整性約束，這些完整性約束必須不違背時(shí)，相應(yīng)的觸發(fā)語句才能執(zhí)行，然后才能觸發(fā)對(duì)應(yīng)的AFTER/FOR類型的觸發(fā)器。INSTEAD OF類型的觸發(fā)器會(huì)在觸發(fā)語句（insert、delete、update）執(zhí)行之前被觸發(fā)，并取代相應(yīng)的觸發(fā)語句。3、 在表或視圖上，每個(gè)INSERT、UPDATE或DELETE語句只能創(chuàng)建一個(gè)INSTEAD OF類型的觸發(fā)器，無法為有外鍵約束且指定為級(jí)聯(lián)刪除或級(jí)聯(lián)修改的表創(chuàng)建DELETE或UPDATE語句上的INSTEAD OF 類型的觸發(fā)器。4、 SQL SERVER為每個(gè)觸發(fā)器都創(chuàng)建了兩個(gè)臨時(shí)表INSERTED表和DELETED表，這兩個(gè)表的邏輯結(jié)構(gòu)與被觸發(fā)器作用的表一樣，用戶可以讀取這兩個(gè)表的內(nèi)容，但不能對(duì)它們進(jìn)行修改，觸發(fā)器執(zhí)行完后，這兩個(gè)表也會(huì)自動(dòng)刪除。當(dāng)執(zhí)行INSERT時(shí)，INSERTED表中保存要向表中插入的所