SQL Server數(shù)據(jù)庫(kù)系統(tǒng)設(shè)計(jì)與實(shí)現(xiàn)指導(dǎo)書

1、SQL Server數(shù)據(jù)庫(kù)系統(tǒng)設(shè)計(jì)與實(shí)現(xiàn)實(shí)驗(yàn)指導(dǎo)書目 錄實(shí)驗(yàn)1 SQL Server的安裝及其管理工具的使用2實(shí)驗(yàn)2 創(chuàng)建數(shù)據(jù)庫(kù)和表3實(shí)驗(yàn)3 數(shù)據(jù)庫(kù)的查詢3實(shí)驗(yàn)4 T-SQL編程3實(shí)驗(yàn)5 索引、存儲(chǔ)過程和觸發(fā)器的使用3實(shí)驗(yàn)6 VB人員信息管理系統(tǒng)3實(shí)驗(yàn)6 PB商品信息管理系統(tǒng)3實(shí)驗(yàn)7 ASP通用人事管理系統(tǒng)。3實(shí)驗(yàn)1 SQL Server的安裝及其管理工具的使用目的與要求 (1) 掌握 SQL Server服務(wù)器的安裝。 (2) 掌握企業(yè)管理器的基本使用方法。 (3) 掌握查詢分析器的基本使用方法。 (4) 了解數(shù)據(jù)庫(kù)及其對(duì)象。實(shí)驗(yàn)內(nèi)容(1)安裝 SQL Server 2000(2)利用企業(yè)管

2、理器訪問系統(tǒng)自帶的pubs數(shù)據(jù)庫(kù)(3)查詢分析器的使用實(shí)驗(yàn)步驟 1安裝 SQL Server 2000 根據(jù)軟硬件環(huán)境，選擇一個(gè)合適版本的 SQL Server 2000。安裝步驟請(qǐng)參照 教材第1.33節(jié)。 2利用企業(yè)管理器訪問系統(tǒng)自帶的pubs數(shù)據(jù)庫(kù) (l）啟動(dòng) SQL Server服務(wù)管理器。 (2）以系統(tǒng)管理員身份登錄到企業(yè)管理器并訪問pubS數(shù)據(jù)庫(kù)。 (3）在企業(yè)管理器的目錄樹中展開文件失一展開pubS數(shù)據(jù)庫(kù)圖標(biāo)，則將列出該數(shù)據(jù)庫(kù)的、所有對(duì)象，如表、視圖、存儲(chǔ)過程，默認(rèn)、規(guī)則等。 (4）選中pubs下的“表”圖標(biāo)，將列出pubs數(shù)據(jù)庫(kù)所有的表(系統(tǒng)表和用戶表），在此以用戶表Publis

3、hers為例，打開該表，查看其內(nèi)容。 (5）在表的尾部插入記錄(9943, zhang, null, china）和( 1408, li, shanghai, NULL china），會(huì)發(fā)生什么情況？ (6）在表中刪除步驟(5）插入的記錄。 3查詢分析器的使用 (l) 在查詢分析器對(duì)象瀏覽器的目錄樹中展開pubs數(shù)據(jù)庫(kù)圖標(biāo)，則將列出該數(shù)據(jù)庫(kù)的所有對(duì)象，如表、視圖、存儲(chǔ)過程等。 (2) 以pubs數(shù)據(jù)庫(kù)的用戶表anthors為例，選中該表的圖標(biāo)，單擊鼠標(biāo)右鍵，出現(xiàn)一快捷菜單，執(zhí)行“打開”菜單項(xiàng)，打開該表，查看其內(nèi)容。(3）在表的尾部插入一記錄。(4）在查詢分析器的輸入窗口，輸入如下 T-SQL語

4、句： use pubs select * from authors go看看執(zhí)行結(jié)果是什么？思考與練習(xí)：用查詢分析器查詢pubs數(shù)據(jù)庫(kù)publishers表的所有記錄。實(shí)驗(yàn)2 創(chuàng)建數(shù)據(jù)庫(kù)和表 目的和要求(l) 了解 SQL Server數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu)和物理結(jié)構(gòu)。(2) 了解表的結(jié)構(gòu)特點(diǎn)。(3) 了解 SQL Servr的基本數(shù)據(jù)類型.(4) 了解空值概念。(5) 學(xué)會(huì)在企業(yè)管理器中創(chuàng)建數(shù)據(jù)庫(kù)和表。(6) 學(xué)會(huì)使用 T-SQL語句創(chuàng)建數(shù)據(jù)庫(kù)和表。實(shí)驗(yàn)內(nèi)容(1) 在企業(yè)管理器中創(chuàng)建數(shù)據(jù)庫(kù)YGGL(2) 在企業(yè)管理器中刪除創(chuàng)建的YGGL數(shù)據(jù)庫(kù)(3) 使用TSQL語句創(chuàng)建數(shù)據(jù)庫(kù)YGGL(4) 在企業(yè)

5、管理器中分別創(chuàng)建表 Employees、Departments和 Salary(5) 在企業(yè)管理器中刪除創(chuàng)建的Employees、Departments和Salary表(6) 使用 TSQL語句創(chuàng)建表 Employees、 Departments和 Salary(7) 表數(shù)據(jù)的添加、修改、刪除實(shí)驗(yàn)步驟 0實(shí)驗(yàn)準(zhǔn)備 創(chuàng)建用于企業(yè)管理的員工管理數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)名為YGGL，包含員工的信息、部門信息以及員工的薪水信息。數(shù)據(jù)庫(kù)YGGL包含下列3個(gè)表： (l）Employees；員工信息表。 (2）Department：部門信息表。 (3） Salary；員工薪水情況表。各表的結(jié)構(gòu)分別如表1、表2和表3所

6、示。表1 Employees表結(jié)構(gòu)列名數(shù)據(jù)類型長(zhǎng)度是否允許為空值說明EmployeeIDChar6員工編號(hào)，主鍵NameChar10姓名BirthdayDatetime8出生日期SexBit1性別AddressChar20地址ZipChar6郵編PhoneNumberChar12電話號(hào)碼EmailAddressChar30電子郵件地址DepartmentIDChar3員工部門號(hào)，外鍵表2 Departments表結(jié)構(gòu)列名數(shù)據(jù)類型長(zhǎng)度是否允許為空值說明DepartmentIDChar3部門編號(hào)，主鍵DepartmentNameChar20部門名Notetext16備注表3 Salary表結(jié)構(gòu)列名

7、數(shù)據(jù)類型長(zhǎng)度是否允許為空值說明EmployeeIDChar6員工編號(hào)，主鍵InComeFloat8收入OutComefloat8支出 首先要明確，能夠創(chuàng)建數(shù)據(jù)庫(kù)的用戶必須是系統(tǒng)管理員，或是被授權(quán)使用CREATEDATABASE語句的用戶。 其次創(chuàng)建數(shù)據(jù)庫(kù)必須要確定數(shù)據(jù)庫(kù)名、所有者(即創(chuàng)建數(shù)據(jù)庫(kù)的用戶）、數(shù)據(jù)庫(kù)大小(最初的大小、最大的大小、是否允許增長(zhǎng)及增長(zhǎng)方式）和存儲(chǔ)數(shù)據(jù)庫(kù)的文件。 然后，確定數(shù)據(jù)庫(kù)包含哪些表，以及所包含的各表的結(jié)構(gòu)，還要了解SQL Server的常用數(shù)據(jù)類型，以創(chuàng)建數(shù)據(jù)庫(kù)的表。此外還要了解兩種常用的創(chuàng)建數(shù)據(jù)庫(kù)、表的方法，即在企業(yè)管理器中創(chuàng)建和使用T-SQL語句創(chuàng)建。 1在企業(yè)

8、管理器中創(chuàng)建數(shù)據(jù)庫(kù)YGGL 要求：數(shù)據(jù)庫(kù)YGGL初始大小為 10 MB，最大大小為50 MB，數(shù)據(jù)庫(kù)自動(dòng)增長(zhǎng)，增長(zhǎng)方式是按 5%比例增長(zhǎng)；日志文件初始為 2 MB，最大可增長(zhǎng)到 5 MB(默認(rèn)為不限制），按 1MB增長(zhǎng)(默認(rèn)是按5%比例增長(zhǎng)）。數(shù)據(jù)庫(kù)的邏輯文件名和物理文件名均采用默認(rèn)值，分別為YGGL-data和e:sqldataMSSQLDatarYGGLmdf，其中 e:sqldataMSSQL為 SQL Server的系統(tǒng)安裝目錄；事務(wù)日志的邏輯文件名和物理文件名也均采用默認(rèn)值，分別為YGGL_LOG和e：sqldataMSSQLDataYGGL_log.ldf。 以系統(tǒng)管理員 Admi

9、nistrator或被授權(quán)使用 CREATE DATABASE語句的用戶登錄 SQLServer服務(wù)器，啟動(dòng)企業(yè)管理器-在服務(wù)器上單擊鼠標(biāo)右鍵-新建數(shù)據(jù)庫(kù)-輸入數(shù)據(jù)庫(kù)名“ YGGL”-選擇“數(shù)據(jù)文件”選項(xiàng)卡-設(shè)置增長(zhǎng)方式和增長(zhǎng)比例-選擇“事務(wù)日志”選項(xiàng)卡-設(shè)置增長(zhǎng)方式和增長(zhǎng)比例。 注意：在“數(shù)據(jù)文件”選項(xiàng)卡和“事務(wù)日志”選項(xiàng)卡中，可以分別指定數(shù)據(jù)庫(kù)文件和日志文件的物理路徑等補(bǔ)性。 2在企業(yè)管理器中刪除創(chuàng)建的YGGL數(shù)據(jù)庫(kù) 在企業(yè)管理器中選擇數(shù)據(jù)庫(kù)YGGL-在YGGL上單擊鼠標(biāo)右鍵則刪除。 3使用TSQL語句創(chuàng)建數(shù)據(jù)庫(kù)YGGL 按照上述要求創(chuàng)建數(shù)據(jù)庫(kù)YGGL。 啟動(dòng)查詢分析器-在“查詢”窗口中輸

10、入如下T-SQL語句： CREATE DATABASE YGGLON( NAME=YGGL_DATA, FILENAME=e:YGGL.MDF, SIZE=10MB, MAXSIZE=50MB, FILEGROWTH=5%)LOG ON (NAME=YGGL_Log, FiLENAME=e:YGGL_Log.ldf, SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=1MB) GO 單擊快捷工具欄的執(zhí)行圖標(biāo)執(zhí)行上述語句，并在企業(yè)管理器中查看執(zhí)行結(jié)果。 4在企業(yè)管理器中分別創(chuàng)建表 Employees、Departments和 Salary 在企業(yè)管理器中選擇數(shù)據(jù)庫(kù)YGGL-在Y

11、GGL上單擊鼠標(biāo)右鍵-新建-表時(shí)輸入Employees表各字段信息則單擊保存圖標(biāo)叫輸入表名 Employees，即創(chuàng)建了表 Employees。按同樣的操作過程創(chuàng)建表Departments和Salary。 5在企業(yè)管理器中刪除創(chuàng)建的Employees、Departments和Salary表 在企業(yè)管理器中選擇數(shù)據(jù)庫(kù)YGGL的表Employees-在Employees上單擊鼠標(biāo)右鍵-刪除，即刪除了表Employees。按同樣的操作過程刪除表Departments和Salary。 6使用 TSQL語句創(chuàng)建表 Employees、 Departments和 Salary啟動(dòng)查詢分析器-在“查詢”窗口

12、中輸入以下TSQL語句：USE YGGLCREATE TABLE Employees ( EmployeeID char(6) primary key, Name char(10) NOT NULL, Birthday datetime NOT NULL, Sex bit NOT NULL, Address char(20) NOT NULL, Zip Char(6) NULL, PhoneNumber char(12) NULL, EmailAddress char(20) NULL, DepartmentID char(3) NOT NULL, ) GO單臺(tái)快捷工具欄的執(zhí)行圖標(biāo)，執(zhí)行上述語

13、句，即可創(chuàng)建表 Employees。按同樣的操作過程創(chuàng)建表 Departments和 Salary，并在企業(yè)管理器中查看結(jié)果。7. 在企業(yè)管理器中向數(shù)據(jù)庫(kù)YGGL表加入數(shù)據(jù)(l）在企業(yè)管理器中向表Employees中加入如表1所示的記錄。表1 Employees表記錄編號(hào)姓名地址郵編電話電子郵件部門號(hào)出生日期性別王林中山路NULL21956-1-231伍容華北京東路l00-2NULL11966-3-281王向客四牌樓10-10-108NULL11972-12-91李麗 中山東路102211950-7-300劉明虎距路100-2 NULL51962-10-181朱俊牌樓巷

14、5.3-180651955-09-281鐘敏中山路108-3-105Z31969-08-100張石兵解放路34-9-1.203Z51964-10-011林濤中山北路247.2.305NULL31967-04-21李玉艱熱和路209.3 41958-09-201葉見北京西路3-7-502NULL41968-11-181陳林琳漢中路120-4-102NULL41959-09-030在企業(yè)管理器中選擇表employees-在其上單擊鼠標(biāo)右鍵-選“返回所有行”-逐字段輸入各記錄值，

15、輸入完后，關(guān)閉表窗口。(2）在企業(yè)管理器中向表Departments插入如表 2所示的記錄。表2 Departments表記錄編號(hào)部門名稱備注1財(cái)務(wù)部NULL2人力資源部NULL3經(jīng)理辦公室NULL4研發(fā)部NULL5市場(chǎng)部NULL(3）在企業(yè)管理器中向表Salary插入如表3所示的記錄。表3 Departments表記錄編號(hào)收入支出2100.8123.091582.6288.032569.88185.651987.0179.582066.15108.02980.7210.23259.98281.522860.0198.02347.68180.02531.98199.082240.0121.01

16、980.0100.02在企業(yè)管理器中修改數(shù)據(jù)庫(kù)YGGL表數(shù)據(jù)(l）在企業(yè)管理器中刪除表 Employees的第 2、 8行和 Salary的第2、11行。注意進(jìn)行刪除操作時(shí)，作為兩表主鍵的EmployeeID的值，以保持?jǐn)?shù)據(jù)完整性。 在企業(yè)管理器中選擇表Employees-在其上單擊鼠標(biāo)右鍵、選“返回所有行”-選擇要?jiǎng)h除的行-單擊鼠標(biāo)右鍵-刪除-關(guān)閉表窗口。(2）在企業(yè)管理器中刪除表 Department的第2行，同時(shí)也要?jiǎng)h除表Employees的第2行。操作方法同上。(3）在企業(yè)管理器中將表Employees中編號(hào)為的記錄的部門號(hào)改為4。 在企業(yè)管理器中選擇表 Employees-在其上單擊

17、鼠標(biāo)右鍵-選“返回所有行”-將光標(biāo)定位至編號(hào)為的記錄的DepartmentID字段，將值1改為4。3使用T-SQL命令修改數(shù)據(jù)庫(kù)YGGL表數(shù)據(jù)(l）使用T-SQL命令分別向YGGL數(shù)據(jù)庫(kù)的Employees、Departments和Salary表中插入一行記錄。 啟動(dòng)查詢分析器-在“查詢”窗口中輸入如下T-SQL語句： USE YGGL INSERT INTO Employees VALUES(,羅林,1973-5-3,1,解放路100號(hào), ,NULL,5) GO INSERT INTO Departments VALUES(2,人力資源部,NULL) GO INSERT INTO Salar

18、y VALUES(,1200.95,50） GO 單擊快捷工具欄的執(zhí)行圖標(biāo)，執(zhí)行上述語句。 注意：在企業(yè)管理器中分別打開YGGL數(shù)據(jù)庫(kù)的Employees、 Departmenta和Salary表， 觀察數(shù)據(jù)變化。(2）使用T-SQL命令修改表Salary中的某個(gè)記錄的字段值。 啟動(dòng)查詢分析器-在“查詢”窗口中輸入如下TSQL語句： USE YGGL UPDATE Salary SET InCome= 2890 WHERE EmployeeID=01lll2 GO 單擊快捷工具欄的執(zhí)行圖標(biāo)，執(zhí)行上述語句，將編號(hào)為0llllZ的職工收入改為2890。 注意：在企業(yè)管理器中打開YGGL 數(shù)據(jù)庫(kù)Sa

19、lary表，觀察數(shù)據(jù)變化。(3）修改表Employees和Departments的記錄值，仍要注意完整性。操作過程同上。(4）使用T-SQL命令修改表Salary中的所有記錄的字段值。 啟動(dòng)查詢分析器則在“查詢”窗口中輸入如下T-SQL語句： USE YGGL UPDATE Salary SET InCome= InCome100 Go 單擊快捷工具欄的執(zhí)行圖標(biāo)，執(zhí)行上述語句，將所有職工的收入增加 100。 可見，使用T-SQL語句操作表數(shù)據(jù)比在企業(yè)管理器中操作表數(shù)據(jù)更為靈活。 注意： 輸入TSQL語句 SELECT * FROM Salary，觀察數(shù)據(jù)變化。(5）使用TRANCATE TAB

20、LE語句刪除表中所有行。 啟動(dòng)查詢分析器-在查詢窗口中輸入如下T-SQL語句： USE YGGL TRANCATE TABLE Salary GO 單擊快捷工具欄的執(zhí)行圖標(biāo)，執(zhí)行上述語句，將刪除Salary表中的所有行。 注意：實(shí)驗(yàn)時(shí)一般不要輕易執(zhí)行這個(gè)操作，困為后面實(shí)驗(yàn)還要用到這些數(shù)據(jù)。如要添該命令的效果，可創(chuàng)建一個(gè)臨時(shí)表，輸入少量數(shù)據(jù)后進(jìn)行。實(shí)驗(yàn)3 數(shù)據(jù)庫(kù)的查詢實(shí)驗(yàn)?zāi)康?(l）掌握SELECT語句的基本語法。 (2）掌握子查詢的表示。 (3）掌握連接查詢的表示。 (4）掌握數(shù)據(jù)匯總的方法。 (5）掌握 SELECT語句的 GROUP BY子句的作用和使用方法。 (6）掌握 SELECT語句

21、的 ORDER BY子句的作用和使用方法。實(shí)驗(yàn)準(zhǔn)備 (l）了解SELECT語句的基本語法格式。 (2）了解SELECT語句的執(zhí)行方法。 (3）了解子查詢的表示方法。 (4）了解連接查詢的表示。 (5）了解數(shù)據(jù)匯總的方法。 (6）了解 SELECT語句的 GROUP BY子句的作用和使用方法。 (7）了解 SELECT語句的 ORDER BY子句的作用。實(shí)驗(yàn)內(nèi)容1SELECT語句的基本使用 (l）對(duì)于實(shí)驗(yàn)2給出的數(shù)據(jù)庫(kù)表結(jié)構(gòu)，查詢每個(gè)雇員的所有數(shù)據(jù)。在查詢分析器的編輯窗口輸入如下語句并執(zhí)行： USE YGGL SELECT * FROM Employees GO 思考與練習(xí)：用SELECT語句查

22、詢Departments和Salary表的所有記錄。 (2）查詢每個(gè)雇員的地址和電話。在查詢分析器的編輯窗口輸入如下語句并執(zhí)行： USE YGGL SELECT Address,PhoneNumber FROM Employees GO 思考與練習(xí)：用SELECT語句查詢Departments和Salary表的一列或若干列。 (3）查詢EmployeeID為的雇員的地址和電話。在查詢分析器的編輯窗口輸入如下語句并執(zhí)行： USE YGGLSELECT Address, PhoneNumber FROM Employees Where EmployeeID= GO 思考與練習(xí)：用 SELECT語句

23、查詢 Departments和Salary表中滿足指定條件的一列或若干列。 (4）查詢Employees表中女雇員的地址和電話，使用AS子查詢將結(jié)果中各列的標(biāo)題分別指定為地址、電話。在查詢分析器的編輯窗口輸入如下語句并執(zhí)行： USE YGGL SELECT Address AS地址, PhoneNumber AS 電話 FROM Employees WHERE sex= 0 GO 注意：使用AS子句可指定目標(biāo)列的標(biāo)題。 (5）計(jì)算每個(gè)雇員的實(shí)際收入。在查詢分析器的編輯窗口輸入如下語句并執(zhí)行： USE YGGL SELECT EmployeeID, 實(shí)際際收入=InCome-OutCome FR

24、OM Salary GO(6) 找出所有姓”王”的雇員的部門號(hào)。在查詢分析器的編輯窗口輸入如下語句并執(zhí)行：USE YGGLSELEC DepartmentID FROM Employees WHERE name LIKE 王% GO 思考與練習(xí)：找出所有地址中含有“中山”的雇員的號(hào)碼及部門號(hào)。 (7）找出所有收入在20003000元之間的雇員編號(hào)。在查詢分析器的編輯窗口輸入如下語句并執(zhí)行： USE YGGL SELECT EmployeeID FROM Salary WHERE InCome BETWEEN 2000 AND 3000 Go 思考與練習(xí)：找出所有在財(cái)務(wù)部和人力資源部工作的雇員的

25、編號(hào)。 注意：在SELECTT語句中 LIKE、 BETWEENAND、 IN、 NOT及CONTAIN謂詞的作用。2子查詢的使用 (1）查找在財(cái)務(wù)部工作的雇員的情況。在查詢分析器的編輯窗口輸入如下語句并執(zhí)行： USE YGGL SELECT * FROM Employees WHERE DepartmentID= ( SELECT DepartmentID FROM Departments WHERE DepartmentName=財(cái)務(wù)部) GO 思考與練習(xí)：用子查詢的方法查找所有收入在 2500元以下的雇員的情況。 (2）查找財(cái)務(wù)部年齡不低于研發(fā)部雇員年齡的雇員的姓名。在查詢分析器的編輯窗

26、口輸入如下語句并執(zhí)行： USE YGGL SELECT Name FROM Employees WHERE DepartmentID IN ( SELECT DeptmentID FROM Departments WHERE DepartmentName=財(cái)務(wù)部) AND BirthdayALL(SELECT Birthday FROM Employees Where DepartmentID IN ( SELECT DepartmentID FROM Dapartments where DepartmentName=研發(fā)部) Go 思考與練習(xí)：用于查詢的方法查找研發(fā)部比所有財(cái)務(wù)部雇員收入都高

27、的雇員的姓名。 (3）查找比所有財(cái)務(wù)部的雇員收入都高的雇員的姓名。在查詢分析器的編輯窗口輸入如下的語句并執(zhí)行： USE YGGL SELECT Name From Employees WHERE EmployeeID IN (SELECT EmployeeID FROM Salary WHERE InCome ALL( SELECT InCome FROM Salary WHERE EmployeeID IN ( SELECT EmalpoyeeID FROM Employees WHERE DepartmentID= (SELECT DEPARTMENTID FROM Departmnts

28、WHERE DepartmentName=財(cái)務(wù)部) GO 思考與練習(xí)：用于查詢的方法查找所有年齡比研發(fā)部雇員年齡都大的雇員的姓名。3連接查詢的使用 (1）查詢每個(gè)雇員的情況及其薪水的情況。在查詢分析器的編輯窗口輸入如下的語句并執(zhí)行： USE YGGL SELECT EmPloyees.*, Salar.* FROM Employees, Salary WHERE Emgloyees.EmployeeID=SalaryEmployeeID GO 思考與練習(xí)：查詢每個(gè)雇員的情況及其工作部門的情況。 間查找財(cái)務(wù)部收入在2200元以上的雇員姓名及其薪水詳情。在查詢分析器的編輯窗口輸入如下語句并執(zhí)行：

29、USE YGGL SELECT Name, InCome, OutCome FROM Employees, Salary, Departments WHERE Employees.EmployeeID=Salary.EmployeeID AND Employees.DepartmentID = Departments.DEpartmentID AND DepartmentName=財(cái)務(wù)部 AND InCome2000 GO 思考與練習(xí)：查詢研發(fā)部在1966年以前出生的雇員姓名及其薪水詳情。4數(shù)據(jù)匯總(l）求財(cái)務(wù)部雇員的平均收入。在查詢分析器的編輯窗口輸入如下語句并執(zhí)行： USE YGGL SE

30、LECT AVG(InCome) AS 財(cái)務(wù)部平均收入WHERE EmployeeID IN ( SELECT EmployeerID FROM Employees WHERE DepartmentID= ( SELECT DepartmentID FROM Departments WHERE DepartmentName=財(cái)務(wù)部) GO 思考與練習(xí)：查詢財(cái)務(wù)部雇員的最高和最低收入。(2）求財(cái)務(wù)部雇員的平均實(shí)際收入。在查詢分析器的編輯窗口輸入如下的語句并執(zhí)行： USE YGGL SELECT AVG(InCome-OutCome) AS 財(cái)務(wù)部平均實(shí)際收入 FROM Salary WHERE

31、EmployeeID IN ( SELECT EmployeeID FROM Employees WHERE DepartmentID= ( SELECT DepartmentID FROM DePartments WHERE DepartmentName=財(cái)務(wù)部) GO 思考與練習(xí)：查詢財(cái)務(wù)部雇員的最高和最低實(shí)際收入。(3）求財(cái)務(wù)部雇員的總?cè)藬?shù)。在查詢分析器的編輯窗口輸入如下的語句并執(zhí)行： USE YGGL SELECT Count(EmgloyeeID) FROM Employees WHERE DepartmentID= ( SELECT DepartmentID FROM Depart

32、ments WHERE DepartmentName =財(cái)務(wù)部) GO 思考與練習(xí)：統(tǒng)計(jì)財(cái)務(wù)部收入在 2 500以上雇員的人數(shù)。5 GROUP BY、 ORDER BY子句的使用 (l）求各部門的雇員數(shù)。在查詢分析器的編輯窗口輸入如下的語句并執(zhí)行： USE YGGL SELECT COUNT(EmployeeID) FROM Employees GROUP BY DepartmentID GO思考與練習(xí)：統(tǒng)計(jì)各部門收入在2000以上雇員的人數(shù)。 (2）將各雇員的情況按收入由低到高排列。在查詢分析器的編輯窗口輸入如下的語句并執(zhí)行： USE YGGL SELECT Employees.*, Sal

33、ary.* FROM Employees, Salary WHERE Employees.EmployeeID=Salary.EmployeeID ORDER BY InCome GO 思考與練習(xí)：將各雇員的情況按出生時(shí)間先后排列。實(shí)驗(yàn)4 T-SQL編程實(shí)驗(yàn)?zāi)康?l) 進(jìn)一步鞏固第2章至第4章所學(xué)的內(nèi)容。(2) 掌握用戶自定義類型的使用。(3) 掌握變量的分類及其使用。(4) 掌握各種運(yùn)算符的使用。(5) 掌握各種控制語句的使用。(6) 掌握系統(tǒng)函數(shù)及用戶自定義函數(shù)的使用。實(shí)驗(yàn)內(nèi)容（1）自定義數(shù)據(jù)類型的使用（2）自定義函數(shù)的使用實(shí)驗(yàn)步驟1自定義數(shù)據(jù)類型的使用(l）對(duì)于實(shí)驗(yàn)2給出的數(shù)據(jù)庫(kù)表結(jié)構(gòu)，

34、再自定義一數(shù)據(jù)類型ID_Type，用于描述員工編號(hào)。在查詢分析器編輯窗口輸入如下程序并執(zhí)行： USE YGGL EXEC sp_addtype ID_Type,char(6),not null GO 注意：不能漏掉單引號(hào)。(2)重新創(chuàng)建 YGGL數(shù)據(jù)庫(kù)的 Employees表。在查詢分析器編輯窗口輸入如下程序并執(zhí)行： USE YGGL IF EXISTS(SELECT name FROM sysobjects WHERE type=U and name=Employees)/*首先在系統(tǒng)表中查看 Employees表是否存在，若存在，刪除該表*/ DROP table employees CR

35、EATE TABLE Eoployees ( EmployeeID ID_type, /*定義字段EmployeeID的類型為 ID_type*/ Name char(10) NOT NULL, Birthday datetime NOT NULL, Sex bit NOT NULL, Address char(20) NOT NULL,ZIP char(6) NULL, PhoneNumber Char(12) NULL, EmailAddress char(20) NULL, DepartmentID char(3) NOT NULL ) GO2自定義函數(shù)的使用 (l）定義一函數(shù)實(shí)現(xiàn)如下功

36、能：對(duì)于一給定的DepartmentID之值，查詢?cè)撝翟贒epartments表中是否存在，若存在返回0, 否則返回-1。 在查詢分析器的編輯窗口輸入如下程序并執(zhí)行。 USE YGGL CREATE FUNCTION CHECK_ID (departmentID char(3) RETURNS integerASbegin declare num intIF EXISTS(SELECT departmentID FROM departmnts WHERE departmentid=departmentID) select num=0 ELSE select num=-1 return nume

37、nd GO (2）寫一段T-SQL腳本程序調(diào)用上述函數(shù)。當(dāng)向Employees表插入一記錄時(shí)，首先調(diào)用函數(shù)CHECK_ID，檢察該記錄的DepartmentID值在表Departments的DepartmentID字段中是否存在對(duì)應(yīng)值，若存在，則將該記錄插入 Employeess表。 在查詢分析器編輯窗口輸入如下程序并執(zhí)行： USE YGGL declare num int select num=dbocheck_id(2) if num=e insert employees values(, 張文, 1982-03-24, 0, 南京鎮(zhèn)江路2號(hào), , ,,2)

38、 GO思考與練習(xí)： 編寫如下程序：(l）自定義一數(shù)據(jù)類型，用于描述YGGL數(shù)據(jù)庫(kù)中的DepartmentID字段，然后編寫代碼重新定義數(shù)據(jù)庫(kù)的各表。(2）當(dāng)對(duì)Departments表的DepartmentID字段值修改時(shí)，對(duì)Employees表中對(duì)應(yīng)的DepartmentID字段值也進(jìn)行相應(yīng)修改。(3)對(duì)Employees表進(jìn)行修改時(shí)，不允許對(duì)DepartmentID字段值進(jìn)行修改。實(shí)驗(yàn)5 索引、存儲(chǔ)過程和觸發(fā)器的使用實(shí)驗(yàn)?zāi)康?l) 掌握索引的使用方法。(2) 掌握存儲(chǔ)過程的使用方法。(3) 掌握觸發(fā)器的使用方法。實(shí)驗(yàn)內(nèi)容（1）建立索引（2）創(chuàng)建觸發(fā)器（3）創(chuàng)建存儲(chǔ)過程（4）調(diào)用存儲(chǔ)過程實(shí)驗(yàn)步

39、驟1建立索引 對(duì) YGGL數(shù)據(jù)庫(kù)的 EInplyees表中的 DepartmentID列建立索引。在查詢分析器編輯窗口輸入如下程序并執(zhí)行： USE YGGL IF EXISTS(SELECT name FROM sysindexes WHERE name=depart_ind） DROP INDEX employees.depart_ind /*應(yīng)使用表名索引名的形式*/ GO USE YGGL CREATE INDEX depart_ind ON Employees(departmentID）2創(chuàng)建觸發(fā)器 對(duì)于YGGL數(shù)據(jù)庫(kù)，表Employees的DepallmentID列與表Departm

40、ent的DepartmentID列應(yīng)滿足參照完整性規(guī)則，即： 向Employees表添加一記錄時(shí)，該記錄的DepartmentID值在Departments表中應(yīng)存在。 修改Departments表的DepartmntID字段值時(shí)，該字段在Employees表中的對(duì)應(yīng)值也應(yīng)修改。 刪除Departments表中一記錄時(shí)，該記錄DepartmentID字段值在Employees表中對(duì)應(yīng)的記錄也應(yīng)刪除。 上述參照完整性規(guī)則，在此通過觸發(fā)器實(shí)現(xiàn)。 在查詢分析器編輯窗口輸入各觸發(fā)器的代碼并執(zhí)行： (l）向Employees表插入或修改一記錄時(shí)，通過觸發(fā)器檢查記錄的DepartmentID值在Depar

41、tments表中是否存在，若不存在，則取消插入或修改操作。 USE YGGL GO CREATE TRIGGER EmployeeIns on dbo.Employees FOR INSERT，UPDATE AS BEGIN if (SELECT ins.DepartmentID from inserted ins） NOT IN (SELECT departmentId FROM departments) ROLLBACK /*對(duì)當(dāng)前事務(wù)回滾，即恢復(fù)到插入前的狀態(tài)*/ END (2）修改Departments表的DepartmentID字段值時(shí)，該字段在 Employees表中的對(duì)應(yīng)值也做相應(yīng)修改。 USE YGG GO CREATET TRIGGER DepartmentsUpdate o