數(shù)據(jù)庫(kù)課后習(xí)題答案第四

1、數(shù)據(jù)庫(kù)原理實(shí)驗(yàn)手冊(cè)實(shí)驗(yàn)一實(shí)驗(yàn)名稱(chēng)：熟悉SQL SERVER 2000環(huán)境一、實(shí)驗(yàn)?zāi)康氖煜QL SERVER 2000提供的服務(wù)管理器、企業(yè)管理器、查詢(xún)分析器、客戶(hù)端和服務(wù)器端網(wǎng)絡(luò)實(shí)用工具等常用管理工具的使用。了解Windows身份驗(yàn)證和SQL Server身份驗(yàn)證這兩種連接數(shù)據(jù)庫(kù)服務(wù)器方式的不同，并能夠使用某種連接方式登陸上SQL SERVER數(shù)據(jù)庫(kù)服務(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 打開(kāi)SQL SERVER的企業(yè)管理器，連接上SQL SERVER服務(wù)器。展開(kāi)左邊樹(shù)狀窗口的各級(jí)結(jié)點(diǎn)，觀察右邊內(nèi)容窗口的變化。3 在SQL SERVER的企業(yè)管理器的樹(shù)狀窗口或內(nèi)容窗口中選擇某一項(xiàng)，點(diǎn)擊鼠標(biāo)右鍵，了解快捷菜單中的有關(guān)功能。4 使用Windows身份驗(yàn)證的連接方式打開(kāi)SQL SERVER的查詢(xún)分析器，在查詢(xún)窗口用USE命令打開(kāi)樣例數(shù)據(jù)庫(kù)pubs（也可以從工具欄中的數(shù)據(jù)庫(kù)下拉列表中選擇pubs數(shù)據(jù)庫(kù)）。5 在查詢(xún)窗口輸入exec sp_help，運(yùn)行后察看結(jié)果。6 在查詢(xún)窗口輸入select * from authors ，運(yùn)行后察看結(jié)果。7 在SQL SERVER的查詢(xún)分析器的

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

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

5、容量為2MB，最大容量為8MB，文件容量遞增值為1MB；事務(wù)日志文件的邏輯文件名為testdb_log，磁盤(pán)文件名為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ù)庫(kù)進(jìn)行修改：添加一個(gè)數(shù)據(jù)文件，邏輯文件名為testdb2_data，磁盤(pán)文件名為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語(yǔ)句，按下列要求創(chuàng)建、修改和刪除用戶(hù)

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

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

9、修改等工作，理解數(shù)據(jù)庫(kù)模式的概念，了解主鍵約束、外鍵約束、UNIQUE約束和CHECK約束的創(chuàng)建和應(yīng)用。要求學(xué)生熟練掌握使用企業(yè)管理器和TSQL語(yǔ)句CREATE TABLE、ALTER TABLE及DROP TABLE語(yǔ)句對(duì)數(shù)據(jù)表進(jìn)行管理。二、實(shí)驗(yàn)原理在企業(yè)管理器中實(shí)現(xiàn)表的創(chuàng)建和修改等工作及使用TSQL語(yǔ)句CREATE TABLE、ALTER TABLE及DROP TABLE來(lái)實(shí)現(xiàn)數(shù)據(jù)表的創(chuàng)建、修改及刪除。三、實(shí)驗(yàn)設(shè)備安裝有SQL SERVER 2000的計(jì)算機(jī)。四、實(shí)驗(yàn)示例兩個(gè)示例：l 一個(gè)是書(shū)上的學(xué)生選課數(shù)據(jù)庫(kù)S_T，S_T數(shù)據(jù)庫(kù)中有學(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成績(jī)l 另一個(gè)示例是某公司的產(chǎn)品銷(xiāo)售數(shù)據(jù)庫(kù)company，company數(shù)據(jù)庫(kù)中存在人事表employee、客戶(hù)表customer、銷(xiāo)售表sales、銷(xiāo)售明細(xì)表sale_item、產(chǎn)品表product。各表的結(jié)構(gòu)如下：?jiǎn)T工人事表employee emp_nochar(5)Not nullprimary key員工編號(hào)emp_namevarchar(10)Not null員工姓名sexchar(2)Not null性別deptvarchar(10)Not null所屬部門(mén)titlevarchar(10)Not null職稱(chēng)date_hired

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

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

14、har(5)Not nullprimary key產(chǎn)品編號(hào)prod_namevarchar(20)Not null產(chǎn)品名稱(chēng)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è)管理器和在查詢(xún)分析分析器中使用TSQL語(yǔ)句完成以下操作：l 針對(duì)S_T數(shù)據(jù)庫(kù)：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)建核查約束，限定成績(jī)只能在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ù)庫(kù)：1 在數(shù)據(jù)庫(kù)company中創(chuàng)建以上五張表，并設(shè)置各表的主鍵。2 在銷(xiāo)售主表sales中添加字段“發(fā)票號(hào)碼” invoice_no，char(10)，NOT NULL。3 添加外鍵約束：a) 在銷(xiāo)售主表sales的業(yè)務(wù)員編號(hào)字段sale_id上添加外鍵約束，參照字段為員工表employee中的字段員工編號(hào)emp_no，約束名為FK_sale_id。b) 在銷(xiāo)售主表sales的客戶(hù)號(hào)字段cust_id上添加外鍵約束，參照字段為客戶(hù)表customer中的字段客戶(hù)號(hào)cust_id，約束名為FK_cust_id。c) 在銷(xiāo)售明細(xì)表sale_item

18、的訂單編號(hào)字段order_no上添加外鍵約束，參照字段為銷(xiāo)售主表sales中的字段訂單編號(hào)order_no，約束名為FK_order_no。d) 在銷(xiāo)售明細(xì)表sale_item的產(chǎn)品編號(hào)字段prod_id上添加外鍵約束，參照字段為產(chǎn)品名稱(chēng)表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”字母開(kāi)頭, 后面跟5位數(shù)的編號(hào)，約束名為CK_emp_no。c) 將員工表em

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

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

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

23、在員工表employee中查找薪水在4000至8000元之間的員工。select *from employeewhere salary between 4000 and 80004、在員工表employee中查找住址為上海、北京、天津這三個(gè)城市的員工。5、在客戶(hù)表customer中查找住址不在上海、北京、天津這三個(gè)城市的客戶(hù)。6、在員工表employee中查找姓“王”用姓名最后一個(gè)字為“功”的員工。7、在客戶(hù)表customer中查找姓“劉”的客戶(hù)名稱(chēng)、電話(huà)。select cust_name,tel_nofrom customerwhere cust_name like '劉%'

24、8、查找出職稱(chēng)為“經(jīng)理”或“職員”的女工的信息。9、查找薪水最高的前三條員工記錄。10、查找訂單金額最高的前10%的訂單記錄。select top 10 percent *from salesorder by tot_amt desc11、查找員工表中所屬部門(mén)。（去掉重復(fù)記錄）select distinct emp_no,emp_name,deptfrom employee12、查找員工表中的所有記錄，并按薪水由低到高進(jìn)行排序。select *from employeeorder by salary asc實(shí)驗(yàn)五實(shí)驗(yàn)名稱(chēng)：復(fù)雜的單表查詢(xún) 一、實(shí)驗(yàn)?zāi)康氖炀氄莆誗ELECT查詢(xún)語(yǔ)句中的Group

25、by 子句、Having子句的用法，以及匯總函數(shù)的使用。二、實(shí)驗(yàn)原理在查詢(xún)語(yǔ)句中用Group by子句進(jìn)行分組；用Having子句對(duì)分組進(jìn)行篩選。使用MAX（），MIN（），COUNT（），SUM（），AVG（）等函數(shù)在查詢(xún)結(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ì)算'P0001'產(chǎn)品的平均銷(xiāo)售單價(jià)select avg(unit_p

26、rice)from sale_itemwhere prod_id='P0001'3、 找出目前銷(xiāo)售業(yè)績(jī)超過(guò)40000元的業(yè)務(wù)員編號(hào)及銷(xiāo)售業(yè)績(jī)，并按銷(xiāo)售業(yè)績(jī)從大到小排序。Select sale_id ,sum(tot_amt)from sales group by sale_id having sum(tot_amt)>40000order by sum(tot_amt) desc4、 查詢(xún)訂購(gòu)了三種以上產(chǎn)品的訂單號(hào)。Select order_no from sale_itemGroup by order_noHaving count(*)>3五、實(shí)驗(yàn)內(nèi)容1、在員工表

27、employee中統(tǒng)計(jì)員工人數(shù)。select count(*) 人數(shù)from employee2、統(tǒng)計(jì)各部門(mén)員工的員工人數(shù)及平均薪水。select count(*) 人數(shù),avg(salary) 平均工資from employee3、查詢(xún)銷(xiāo)售業(yè)績(jī)超過(guò)10000元的員工編號(hào)。select sale_idfrom (select sale_id,sum(tot_amt) amountfrom salesgroup by sale_id) awhere a.amount > 100004、計(jì)算每一產(chǎn)品銷(xiāo)售數(shù)量總和與平均銷(xiāo)售單價(jià)。select prod_id,sum(qty) tot_amoun

28、t,sum(unit_tot)/sum(qty) average_unit_pricefrom (select prod_id,qty,qty*unit_price unit_totfrom sale_item) agroup by prod_id5、統(tǒng)計(jì)各部門(mén)不同性別、或各部門(mén)、或不同性別或所有員工的平均薪水。（在GROUP BY 子句中使用CUBE關(guān)鍵字）6、 統(tǒng)計(jì)各部門(mén)不同性別、或各部門(mén)或所有員工的平均薪水。（在GROUP BY 子句中使用ROLLUP關(guān)鍵字）7、 計(jì)算出一共銷(xiāo)售了幾種產(chǎn)品。select count(prod_id)from (select distinct prod_i

29、dfrom sale_item) a8、 顯示sale_item表中每種產(chǎn)品的訂購(gòu)金額總和，并且依據(jù)銷(xiāo)售金額由大到小排列來(lái)顯示出每一種產(chǎn)品的排行榜。select prod_id,sum(qty) tot_amount,sum(unit_tot) tot_amountfrom (select prod_id,qty,qty*unit_price unit_totfrom sale_item) agroup by prod_idorder by sum(unit_tot) desc9、 計(jì)算每一產(chǎn)品每月的銷(xiāo)售金額總和，并將結(jié)果按銷(xiāo)售（月份，產(chǎn)品編號(hào)）排序。select prod_id,month,

30、sum(unit_amount) month_amountfrom (select prod_id,qty*unit_price unit_amount,month(order_date) monthfrom sale_item) agroup by prod_id,monthorder by prod_id asc,month_amount desc10、 查詢(xún)每位業(yè)務(wù)員各個(gè)月的業(yè)績(jī)，并按業(yè)務(wù)員編號(hào)、月份降序排序select sale_id,sum(tot_amt)month_amount,month(order_date) monthfrom salesgroup by sale_id,m

31、onth(order_date)order by sale_id desc,month desc實(shí)驗(yàn)六實(shí)驗(yàn)名稱(chēng)：連接查詢(xún)一、實(shí)驗(yàn)?zāi)康恼莆帐褂眠B接的方法從多個(gè)表中查詢(xún)數(shù)據(jù)。理解內(nèi)連接、外連接（包括左外連接、右外連接和全外連接）、自身連接的概念和使用。要求學(xué)生熟練掌握在FROM子句和在WHERE子句中指定連接條件的這兩種方法。二、實(shí)驗(yàn)原理在查詢(xún)語(yǔ)句的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列名 （實(shí)現(xiàn)左外連接）l FROM 表1 R

32、IGHT 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)自身連接）在查詢(xún)語(yǔ)句的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)右外連接）l FROM 表1 AS 別名

33、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)品名稱(chēng)、數(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.qty>2order by a

34、.prod_id2、查找出employee表中住址相同的員工的姓名、性別、職稱(chēng)、薪水、住址。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、查找商品名稱(chēng)為14寸顯示器商品的銷(xiāo)售情況，顯示該商品的編號(hào)、銷(xiāo)售數(shù)量、單價(jià)和金額select

35、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表中部門(mén)相同且住址相同的女員工的姓名、性別、職稱(chēng)、薪水、住址。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 e

36、mployee as bon (a.emp_no!=b.emp_no) and (a.dept=b.dept) and (a.addr=b.addr) and a.sex = '女' and a.sex = b.sex2、 檢索product 表和sale_item表中相同產(chǎn)品的產(chǎn)品編號(hào)、產(chǎn)品名稱(chēng)、數(shù)量、單價(jià)。select d_id,d_name,a.qty,a.unit_pricefrom sale_item a,product b where d_id = d_idgroup by d_id,d_name,a.qt

37、y,a.unit_price3、 檢索product 表和sale_item表中單價(jià)高于2400元的相同產(chǎn)品的產(chǎn)品編號(hào)、產(chǎn)品名稱(chēng)、數(shù)量、單價(jià)。select d_id,d_name,a.qty,a.unit_pricefrom sale_item a,product b where d_id = d_id and a.unit_price > 2400group by d_id,d_name,a.qty,a.unit_price4、 查詢(xún)?cè)诿繌堄唵沃杏嗁?gòu)金額超過(guò)24000元的客戶(hù)名及其地址。select b.cust_name,b

38、.addrfrom sales a,customer bwhere tot_amt > 24000 and a.cust_id = b.cust_id5、 查找有銷(xiāo)售記錄的客戶(hù)編號(hào)、名稱(chēng)和訂單總額select b.cust_id,b.cust_name,a.tot_amtfrom sales a,customer bwhere a.cust_id = b.cust_id6、 每位客戶(hù)訂購(gòu)的每種產(chǎn)品的總數(shù)量及平均單價(jià)，并按客戶(hù)號(hào)，產(chǎn)品號(hào)從小到大排列。select x.cust_id, d_id,sum(qty) sum_qty,avg(unit_price) avg_pricef

39、rom (select cust_id,prod_id,qty,unit_pricefrom sales a,sale_item bwhere a.order_no = b.order_no) xgroup by x.cust_id,d_idorder by cust_id asc,prod_id asc7、 查找在1997年中有銷(xiāo)售記錄的客戶(hù)編號(hào)、名稱(chēng)和訂單總額select a.cust_id,cust_name,tot_amtfrom customer a,sales bwhere a.cust_id = b.cust_id and year(b.order_date) = 19

40、978、 分別使用左向外連接、右向外連接、完整外部連接檢索product 表和sale_item表中單價(jià)高于2400元的相同產(chǎn)品的產(chǎn)品編號(hào)、產(chǎn)品名稱(chēng)、數(shù)量、單價(jià)。并分析比較檢索的結(jié)果。 左外:select d_id,d_name,qty,unit_pricefrom product left join sale_item on(d_id = sale_d_id and sale_item.unit_price > 2400)右外:select d_id,d_nam

41、e,qty,unit_pricefrom product right join sale_item on(d_id = sale_d_id and sale_item.unit_price > 2400)全外：select d_id,d_name,qty,unit_pricefrom product full join sale_item on(d_id = sale_d_id and sale_item.unit_price > 2400)實(shí)驗(yàn)七實(shí)驗(yàn)名稱(chēng)：嵌套查

42、詢(xún)一、實(shí)驗(yàn)?zāi)康恼莆誗ELECT語(yǔ)句的嵌套使用，實(shí)現(xiàn)多表的復(fù)雜查詢(xún)，進(jìn)一步理解SELECT語(yǔ)句的高級(jí)使用方法。二、實(shí)驗(yàn)原理使用嵌套查詢(xún)時(shí)，先用內(nèi)查詢(xún)（子查詢(xún)）挑選出部分?jǐn)?shù)據(jù)，以作為外查詢(xún)（主查詢(xún)）的數(shù)據(jù)來(lái)源或搜索條件。包含子查詢(xún)的語(yǔ)句通常采用以下格式：WHERE 表達(dá)式 NOT IN （子查詢(xún)）WHERE 表達(dá)式 比較運(yùn)算符 ANY|ALL （子查詢(xún)）WHERE NOT EXISTS （子查詢(xún)）其中前兩種又稱(chēng)為不相關(guān)子查詢(xún)，子查詢(xún)的查詢(xún)條件不依賴(lài)其父查詢(xún)，所以可以先求出子查詢(xún)的結(jié)果，然后由內(nèi)到外逐層求解。最后一種為相關(guān)子查詢(xún)，其子查詢(xún)的查詢(xún)條件依賴(lài)于外層父查詢(xún)的某個(gè)屬性值，所以不能先一次性地求出

43、子查詢(xún)的結(jié)果。三、實(shí)驗(yàn)設(shè)備安裝有SQL SERVER 2000的計(jì)算機(jī)。四、實(shí)驗(yàn)示例1、由employee表中查找出薪水最高的員工信息。select *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 orde

44、r_date='1996/10/15')order by tot_amt3、用存在量詞查找沒(méi)有訂貨記錄的客戶(hù)名稱(chēng)select cust_namefrom customer awhere not exists (select * from sales b where a.cust_id=b.cust_id)五、實(shí)驗(yàn)內(nèi)容1、 由sales表中查找出銷(xiāo)售金額最高的訂單。2、 由sales表中查找出訂單金額大于“E0013業(yè)務(wù)員在1996/10/15這天所接任一張訂單的金額”的所有訂單，并顯示承接這些訂單的業(yè)務(wù)員和該條訂單的金額。3、 找出公司女業(yè)務(wù)員所接的訂單。4、 找出目前業(yè)績(jī)未超

45、過(guò)200000元的員工。5、 在銷(xiāo)售主表sales中查詢(xún)銷(xiāo)售業(yè)績(jī)最高的業(yè)務(wù)員編號(hào)及銷(xiāo)售業(yè)績(jī)。6、 找出目前業(yè)績(jī)超過(guò)232000元的員工編號(hào)和姓名。7、 查詢(xún)訂購(gòu)的產(chǎn)品至少包含了訂單10003中所訂購(gòu)產(chǎn)品的訂單。8、查詢(xún)末承接業(yè)務(wù)的員工的信息。實(shí)驗(yàn)八實(shí)驗(yàn)名稱(chēng)：數(shù)據(jù)更新一、實(shí)驗(yàn)?zāi)康氖炀毷褂肐NSERT/DELETE/UPDATE語(yǔ)句進(jìn)行表的更新操作。二、實(shí)驗(yàn)原理數(shù)據(jù)更新操作只能針對(duì)一個(gè)表進(jìn)行，即每次只能在一個(gè)表中插入、刪除和修改數(shù)據(jù)。用INSERT語(yǔ)句可以通過(guò)使用VALUES關(guān)鍵字一次只向一個(gè)表插入一條記錄，也可以通過(guò)子查詢(xún)向一個(gè)表中插入若干條記錄； 當(dāng)使用DELETE和UPDATE語(yǔ)句實(shí)現(xiàn)表的刪

46、除和更改時(shí)，若題目中涉及多個(gè)表的信息，可以使用子查詢(xún)來(lái)挑選出部分?jǐn)?shù)據(jù)，以作為刪除或更改的條件，也可以在語(yǔ)句中用FROM子句給出題目中涉及的所有表，而在WHERE子句中給出多個(gè)表之間的連接條件。三、實(shí)驗(yàn)設(shè)備安裝有SQL SERVER 2000的計(jì)算機(jī)。四、實(shí)驗(yàn)示例1、在產(chǎn)品名稱(chēng)表中插入一條記錄。INSERT INTO product VALUES(p0009,CD_ROM)2、 將employee員工表中性別為“女”的員工的員工編號(hào)、姓名，職稱(chēng)和薪水復(fù)制到一個(gè)名為temp_emp表文件中，該文件只具有這四個(gè)列。INSERT INTO temp_emoSELECT emp_no,emp_name,

47、title,salaryFROM employeeWHERE sex=女3、 將所有經(jīng)理的薪水上調(diào)10%。UPDATE EMPLOYEESET salary=salary*1.1WHERE title=經(jīng)理4、 刪除李小明的銷(xiāo)售記錄。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)容凡是能

48、用多種方法實(shí)現(xiàn)的，請(qǐng)用多種方法實(shí)現(xiàn)。1、為各表添加若干條記錄，必須符合實(shí)驗(yàn)二中設(shè)定的各種約束。2、將每個(gè)員工的薪水上調(diào)10%。3、刪除sales表中作廢的訂單（其發(fā)票號(hào)碼為I000000004），其訂貨明細(xì)表中的數(shù)據(jù)也一并刪除。5、 刪除所有沒(méi)有銷(xiāo)售業(yè)績(jī)的員工記錄。6、 對(duì)那些只要有一筆銷(xiāo)售業(yè)績(jī)超過(guò)20000元的員工的薪水增加500元。實(shí)驗(yàn)九實(shí)驗(yàn)名稱(chēng)：視圖的建立與使用一、實(shí)驗(yàn)?zāi)康睦斫庖晥D的定義、視圖的優(yōu)點(diǎn)與視圖的工作原理。掌握在企業(yè)管理器和查詢(xún)分析器中創(chuàng)建、修改及刪除視圖。能夠熟練掌握利用視圖向表中插入、刪除和修改數(shù)據(jù)。二、實(shí)驗(yàn)原理利用CREATE VIEW、ALTER VIEW 、DROP

49、VIEW語(yǔ)句創(chuàng)建、修改及刪除視圖。由于視圖是虛表，通過(guò)視圖向表中添加數(shù)據(jù)時(shí)必須滿(mǎn)足一定的條件。如果視圖引用了多個(gè)數(shù)據(jù)表，那么在通過(guò)視圖向數(shù)據(jù)表中添加數(shù)據(jù)時(shí)，這個(gè)語(yǔ)句只能指定同一個(gè)表中的字段。如果通過(guò)一個(gè)引用了多個(gè)數(shù)據(jù)表的視圖向這些數(shù)據(jù)表中添加數(shù)據(jù)時(shí)，必須書(shū)寫(xiě)多個(gè)INSERT語(yǔ)句。通過(guò)使用多個(gè)表的視圖對(duì)數(shù)據(jù)表進(jìn)行更新也需要書(shū)寫(xiě)多個(gè)UPDATE語(yǔ)句，另外適用于INSERT操作的許多限制同UPDATE操作。使用DELETE語(yǔ)句可以通過(guò)視圖將數(shù)據(jù)表中的數(shù)據(jù)刪除。但如果視圖應(yīng)用了兩個(gè)或兩個(gè)以上的數(shù)據(jù)表，則不允許刪除視圖中的數(shù)據(jù)。另外，通過(guò)視圖刪除的記錄，不能違背視圖定義的WHERE子句中的條件限制。三、

50、實(shí)驗(yàn)環(huán)境安裝有SQL SERVER 2000的計(jì)算機(jī)。四、實(shí)驗(yàn)示例1、創(chuàng)建視圖emp_view，該視圖中包含女員工的編號(hào)、姓名和薪水。create 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 sa

51、les,sale_itemwhere sales.order_no=sale_item.order_no/*注意：插入數(shù)據(jù)時(shí)要考慮數(shù)據(jù)列是否有默認(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)過(guò)程中可能會(huì)報(bào)錯(cuò)，請(qǐng)分析錯(cuò)誤原因。1、 創(chuàng)建一個(gè)視圖cust_view，該視圖只含上?？蛻?hù)

52、信息，即客戶(hù)號(hào)、客戶(hù)姓名、住址。2、 利用cust_view視圖添加一條記錄數(shù)據(jù)。（注意：分別查看customer表和該視圖的結(jié)果。）3、 修改cust_view視圖，該視圖只含上?？蛻?hù)信息，即客戶(hù)號(hào)、客戶(hù)姓名、住址、電。4、 刪除視圖中所有姓“王”的客戶(hù)數(shù)據(jù)。5、 通過(guò)視圖修改表內(nèi)某一客戶(hù)的姓名。6、 有兩個(gè)基本表employee和sales，創(chuàng)建一個(gè)視圖，該視圖包含相同業(yè)務(wù)員的編號(hào)、姓名、訂單號(hào)、銷(xiāo)售總金額。7、 將上述視圖中訂單號(hào)為10001的記錄的銷(xiāo)售金額改為60000。8、 給上述視圖添加一條記錄數(shù)據(jù)。9、 刪除上述視圖。實(shí)驗(yàn)十實(shí)驗(yàn)名稱(chēng)：存儲(chǔ)過(guò)程的建立與調(diào)用一、實(shí)驗(yàn)?zāi)康睦斫獯鎯?chǔ)過(guò)程的概念、作用、建立和調(diào)用方法。二、實(shí)驗(yàn)原理使用CREATE PROCEDURE語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程，ALTER PROCEDURE語(yǔ)句修改存儲(chǔ)過(guò)程，DROP PROCEDURE語(yǔ)句刪除存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程有不帶參數(shù)的、有帶輸入?yún)?shù)的、有帶輸出參數(shù)(output)的，還可以有帶返回值的。創(chuàng)建好的存儲(chǔ)過(guò)程可以使用EXEC procedure_name語(yǔ)句執(zhí)行。三、實(shí)驗(yàn)設(shè)備安裝有SQL SERV