《數(shù)據(jù)庫應(yīng)用與設(shè)計》實驗報告~

1、數(shù)據(jù)庫應(yīng)用與設(shè)計實驗報告實驗1實驗9 學(xué) 院 管理學(xué)院 專 業(yè) 電子商務(wù) 年級班別 2013級1班 學(xué) 號 3113004762 學(xué)生姓名 吳孫德 指導(dǎo)教師 胡 鳳 成 績 目錄實驗1 建立數(shù)據(jù)庫1實驗2 建立表和定義完整性約束6實驗3 數(shù)據(jù)庫操作和完整性約束的作用10實驗4 數(shù)據(jù)的查詢19實驗5 視圖的應(yīng)用42實驗6 游標(biāo)51實驗7 存儲過程53實驗8 觸發(fā)器56實驗9 用戶管理和權(quán)限管理59實驗1  建立數(shù)據(jù)庫 實驗名稱：建立數(shù)據(jù)庫 實驗內(nèi)容：在SQL Server環(huán)境下建立數(shù)據(jù)庫和維護數(shù)據(jù)庫。 實驗?zāi)康模豪斫釹QL Server

2、數(shù)據(jù)庫的存儲結(jié)構(gòu)，掌握SQL Server數(shù)據(jù)庫的建立方法和維護方法。 實驗方法：在SQL Server環(huán)境下用CREATE DATABASE命令建立數(shù)據(jù)庫（注意：需要是系統(tǒng)管理員用戶，或者被指定dbcreator固定服務(wù)器角色的成員，或者被顯式地授予CREATE DATABASE權(quán)限，有關(guān)權(quán)限的內(nèi)容請課文5.2節(jié)）實驗要求： 1、 根據(jù)上體要求創(chuàng)建如下3個數(shù)據(jù)庫： (1) 創(chuàng)建數(shù)據(jù)庫test1（使用一個數(shù)據(jù)文件和一個日志文件），要求指定一個數(shù)據(jù)文件、一個事務(wù)日志文件，并把它們安排在不同的硬盤上。

3、0;(2) 創(chuàng)建數(shù)據(jù)庫test2（使用多個數(shù)據(jù)文件和多個日志文件），要求使用3個數(shù)據(jù)文件和兩個事務(wù)日志文件，主數(shù)據(jù)文件是列表中的第一個文件或者用關(guān)鍵字PRIMARY指定（文件擴展名：主數(shù)據(jù)文件使用.mdf，次數(shù)據(jù)文件使用.ndf，事務(wù)日志文件使用.ldf）。(3) 創(chuàng)建數(shù)據(jù)庫test3（使用文件組），要求使用3個文件組（含主文件組，默認(rèn)的文件組），每個文件組包含兩個數(shù)據(jù)文件，并且每個文件組要安排在不同的硬盤上。 2、 修改建立的test1數(shù)據(jù)庫，為之添加一個5MB大小的新數(shù)據(jù)文件。3、 修改建立的test2數(shù)據(jù)庫，將其中的主數(shù)據(jù)文件增加5MB的

4、存儲空間。4、 刪除建立的數(shù)據(jù)庫test1。實驗操作:1、 根據(jù)上體要求創(chuàng)建如下3個數(shù)據(jù)庫： (1) 創(chuàng)建數(shù)據(jù)庫test1（使用一個數(shù)據(jù)文件和一個日志文件），要求指定一個數(shù)據(jù)文件、一個事務(wù)日志文件，并把它們安排在不同的硬盤上。create database test1 on(name=test,filename='e:test.mdf',size=10,maxsize=50,filegrowth=5)log on(name=test_log,filename='f:test.ldf',size=5mb,maxsize=25

5、mb,filegrowth=5mb) (2) 創(chuàng)建數(shù)據(jù)庫test2（使用多個數(shù)據(jù)文件和多個日志文件），要求使用3個數(shù)據(jù)文件和兩個事務(wù)日志文件，主數(shù)據(jù)文件是列表中的第一個文件或者用關(guān)鍵字PRIMARY指定（文件擴展名：主數(shù)據(jù)文件使用.mdf，次數(shù)據(jù)文件使用.ndf，事務(wù)日志文件使用.ldf）。 create database test2 onprimary (name=t1,filename='e:a1.mdf',size=10,maxsize=50,filegrowth=5),(name=t2,filename='e:a2.ndf'

6、,size=10,maxsize=50,filegrowth=5),(name=t3,filename='e:a3.ndf',size=10,maxsize=50,filegrowth=5)log on(name=t1_log,filename='e:a1.ldf',size=5mb,maxsize=25mb,filegrowth=5mb),(name=t2_log,filename='e:a2.ldf',size=5mb,maxsize=25mb,filegrowth=5mb)(3) 創(chuàng)建數(shù)據(jù)庫test3（使用文件組），要求使用3個文

7、件組（含主文件組，默認(rèn)的文件組），每個文件組包含兩個數(shù)據(jù)文件，并且每個文件組要安排在不同的硬盤上。 create database test3 onprimary (name=te0,filename='f:b0.mdf',size=10,maxsize=50,filegrowth=5),filegroup fg1 (name=te1,filename='f:b1.mdf',size=10,maxsize=50,filegrowth=5),(name=te2,filename='f:b2.ndf',size=10,maxsize=50,

8、filegrowth=5),filegroup fg2 (name=te3,filename='f:b3.ndf',size=10,maxsize=50,filegrowth=5),(name=te4,filename='f:b4.ndf',size=10,maxsize=50,filegrowth=5),filegroup fg3 (name=te5,filename='f:b5.ndf',size=10,maxsize=50,filegrowth=5),(name=te6,filename='f:b6.ndf',size=10

9、,maxsize=50,filegrowth=5)2、 修改建立的test1數(shù)據(jù)庫，為之添加一個5MB大小的新數(shù)據(jù)文件。alter database test1add file(name=test1,filename='f:test1.ndf',size=5mb,maxsize=50,filegrowth=5)3、 修改建立的test2數(shù)據(jù)庫，將其中的主數(shù)據(jù)文件增加5MB的存儲空間。alter database test2modify file(name=t1,filename='e:a1.mdf',size=15,maxsize=55,fi

10、legrowth=10)4、 刪除建立的數(shù)據(jù)庫test1。drop database test1實驗2 建立表和定義完整性約束 實驗名稱：建立模式、表和定義完整性約束 實驗內(nèi)容：在實驗一建立的數(shù)據(jù)庫的基礎(chǔ)上，參照下圖和下表建立表并定義完整性約束。表名字段名字段類型關(guān)鍵字約束說明倉庫倉庫號6個字符是前2位為英文大寫字母，后4位為數(shù)字字符城市10個字符唯一，不允許空值面積數(shù)值大于等于50職工職工號8個字符是前2位為英文大寫字母，后6位為數(shù)字字符倉庫號參照倉庫表的倉庫號工資貨幣大于等于1000，小于等于10000供應(yīng)商供應(yīng)商號4個字符是首位為大寫字母S，后3位

11、為數(shù)字字符供應(yīng)商名16個字符地址30個字符訂購單職工號參照職工表的職工號，不允許空值供應(yīng)商號參照供應(yīng)商表的供應(yīng)商號，允許空值訂購單號6個字符是前2位為英文大寫字母OR，后4位數(shù)字字符訂購日期日期默認(rèn)是當(dāng)前日期金額貨幣默認(rèn)空值訂購單明細(xì)訂購單號是參照訂購單表的訂購單號，不允許空值序號2位數(shù)字是產(chǎn)品名稱20個字符單價貨價大于0，默認(rèn)空值數(shù)量整數(shù)大于0，不允許空值實驗?zāi)康模菏炀氄莆毡淼慕⒑蛿?shù)據(jù)完整性速描定義方法，實踐DBMS提供的數(shù)據(jù)完整性功能，加深對數(shù)據(jù)完整性的理解。 實驗方法：在實驗一建立數(shù)據(jù)庫的基礎(chǔ)上用CREATE SCHEMA命令建立模式，用CREATE TA

12、BLE命令建立表并定義數(shù)據(jù)完整性約束（注意：需要有CREATE SCHEMA和CREATE TABLE的權(quán)限，或者其他具有相應(yīng)權(quán)限的身份，有關(guān)權(quán)限的內(nèi)容請參見課文5.2節(jié)），用ALTER TABLE命令修改表結(jié)構(gòu)。 實驗要求： 1、 用CREATE SCHEMA命令建立模式（自己命名模式名稱）。 create database 實驗create schema wusunde2、 在定義的模式下參照圖1和表1使用CREATE TABLE命令建立表并定義完整性約束。create table wusu

13、nde.倉庫(倉庫號 char(6) primary key check(倉庫號 like 'A-ZA-Z0-90-90-90-9'), 城市 char(10) unique not null, 面積 int check(面積>=50)create table wusunde.職工(職工號 char(8) primary key check(職工號 like 'A-ZA-Z0-90-90-90-90-90-9'), 倉庫號 char(6) constraint ck foreign key references wusunde.倉庫(倉庫號), 工資 mo

14、ney check(工資>=1000 and 工資<=10000)create table wusunde.供應(yīng)商(供應(yīng)商號 char(4) primary key check(供應(yīng)商號 like 'S0-90-90-9'), 供應(yīng)商名 char(16), 地址 char(30)create table wusunde.訂購單(職工號 char(8) not null foreign key references wusunde.職工(職工號), 供應(yīng)商號 char(4) null constraint supply foreign key references w

15、usunde.供應(yīng)商(供應(yīng)商號) on delete set null, 訂購單號 char(6) primary key check(訂購單號 like 'OR0-90-90-90-9'), 訂購日期 datetime default getdate(), 金額 money default null)create table wusunde.訂購單明細(xì)(訂購單號 char(6) not null foreign key references wusunde.訂購單(訂購單號), 序號 char(2), 產(chǎn)品名稱 char(20), 單價 money default null

16、check(單價>0), 數(shù)量 int not null check(數(shù)量>0), primary key(訂購單號,序號) 3、 使用ALTER TABLE命令按如下要求修改表結(jié)構(gòu)： (1) 為訂購單增加一個新的字段“完成日期”，類型為日期時間型，默認(rèn)是空值。alter table wusunde.訂購單add 完成日期 datetime default null(2) 為訂單明細(xì)表的“數(shù)量”字段重新定義約束：大于0、小于等于1000的正整數(shù)，不允許為空值。alter table wusunde.訂購單明細(xì)add check(數(shù)量

17、 between 0 and 1000)alter table wusunde.訂購單明細(xì)alter column 數(shù)量 int not null (3) 將供應(yīng)商表的供應(yīng)商名字段的類型修改為varchar(30)。alter table wusunde.供應(yīng)商alter column 供應(yīng)商名 varchar(30)實驗3 數(shù)據(jù)操作和完整性約束的作用 實驗名稱：數(shù)據(jù)操作筆完整性約束的作用 實驗內(nèi)容：在實驗二的基礎(chǔ)上完成數(shù)據(jù)的插入操作，然后進行部分修改和刪除操作，在這些操作中體會數(shù)據(jù)完整性約束的作用。 實驗?zāi)康模菏炀氄莆誗QL的INSERT，U

18、PDATE和DELETE命令，深刻理解數(shù)據(jù)完整性約束的作用以及約束時機。 實驗方法：在實驗二的基礎(chǔ)上首先用INSERT命令插入各個表的記錄，然后使用UPDATE和DELETE命令對部分記錄進行修改和刪除操作。 實驗要求： 1、 自行設(shè)計各個表的記錄，其中倉庫表至少5條記錄，職工表至少10條記錄、供應(yīng)商表至少5條記錄，訂購單表至少20條記錄，訂購單明細(xì)表至少50條記錄。2、 使用INSERT命令完成記錄的插入操作，要求分別設(shè)計若干違反實體完整性、參照完整性和用戶定義完整性約束的插入操作，并分析原因。 3、 設(shè)計若干刪除操作，體

19、會執(zhí)行刪除操作時檢查參照完整性規(guī)則的效果（要求涉及拒絕刪除、空值刪除和級聯(lián)刪除等不同的處理方式）。 4、 設(shè)計若干更新操作，體會執(zhí)行更新操作時檢查實體完整性規(guī)則、參照完整性規(guī)則和用戶定義完整性規(guī)則的效果。 5、 將x城市倉庫的職工的工資提高10%。 6、 用UPDATE語句完成訂購單表金額字段的計算（提示：某張訂購單的金額為該訂購單所有訂購項目（訂購單明細(xì)）單價乘以數(shù)量的總和）。 7、 設(shè)計23個刪除操作，它需要另外一個表中的數(shù)據(jù)作為刪除條件（例如，按指定的供應(yīng)商名稱刪除訂購單記錄）。 8、 在

20、實驗報告中給出具體的記錄和設(shè)計的操作，并針對各種數(shù)據(jù)完整性檢查，給出具體的分析和討論。實驗操作：1、分別設(shè)計倉庫表5條記錄，職工表10條記錄、供應(yīng)商表5條記錄，訂購單表20條記錄，訂購單明細(xì)表50條記錄。insert into wusunde.倉庫 values('QW0001','廣州',500)insert into wusunde.倉庫 values('QW0002','上海',900)insert into wusunde.倉庫 values('QW0003','北京',250)insert

21、 into wusunde.倉庫 values('QW0004','紐約',1000)insert into wusunde.倉庫 values('QW0005','深圳',650)insert into wusunde.職工 values('ZG000001','QW0005',3000)insert into wusunde.職工 values('ZG000002','QW0001',5600)insert into wusunde.職工 values('

22、ZG000003','QW0004',6600)insert into wusunde.職工 values('ZG000004','QW0002',3800)insert into wusunde.職工 values('ZG000005','QW0005',3900)insert into wusunde.職工 values('ZG000006','QW0001',4600)insert into wusunde.職工 values('ZG000007',&#

23、39;QW0003',4000)insert into wusunde.職工 values('ZG000008','QW0003',5000)insert into wusunde.職工 values('ZG000009','QW0002',4800)insert into wusunde.職工 values('ZG000010','QW0004',6000)insert into wusunde.供應(yīng)商 values('S001','華廣','深圳&

24、#39;)insert into wusunde.供應(yīng)商 values('S002','廣工','廣州')insert into wusunde.供應(yīng)商 values('S003','美的','茂名')insert into wusunde.供應(yīng)商 values('S004','樂視','深圳')insert into wusunde.供應(yīng)商 values('S005','康佳','廣州')insert

25、into wusunde.訂購單 values('ZG000007','S005','OR0001','2015-1-6',5000,'2015-2-5')insert into wusunde.訂購單 values('ZG000002','S001','OR0002','2015-2-3',10000,'2015-3-4')insert into wusunde.訂購單 values('ZG000003','S

26、004','OR0003','2015-2-17',3000,'2015-5-16')insert into wusunde.訂購單 values('ZG000006','S002','OR0004','2015-2-16',9000,'2015-4-18')insert into wusunde.訂購單 values('ZG000010','S005','OR0005','2015-3-20',

27、8000,'2015-6-27')insert into wusunde.訂購單 values('ZG000001','S001','OR0006','2015-3-22',7500,'2015-7-29')insert into wusunde.訂購單 values('ZG000006','S003','OR0021','2015-2-15',4600,'2015-10-13')insert into wusunde.

28、訂購單 values('ZG000004','S002','OR0007','2015-2-23',8500,'2015-8-29')insert into wusunde.訂購單 values('ZG000003','S005','OR0008','2015-1-5',3600,'2015-11-11')insert into wusunde.訂購單 values('ZG000001','S004',&

29、#39;OR0009','2015-1-31',7600,'2015-12-14')insert into wusunde.訂購單 values('ZG000007','S001','OR0010','2015-3-25',12000,'2015-7-30')insert into wusunde.訂購單 values('ZG000008','S002','OR0011','2015-2-26',4600,

30、9;2015-9-27')insert into wusunde.訂購單 values('ZG000010','S002','OR0012','2015-1-28',3400,'2015-11-21')insert into wusunde.訂購單 values('ZG000009','S004','OR0013','2015-3-19',2300,'2015-10-24')insert into wusunde.訂購單 val

31、ues('ZG000007','S005','OR0014','2015-2-23',9000,'2015-9-21')insert into wusunde.訂購單 values('ZG000008','S003','OR0015','2015-2-27',6400,'2015-12-23')insert into wusunde.訂購單 values('ZG000002','S001','OR

32、0016','2015-1-30',7900,'2015-6-30')insert into wusunde.訂購單 values('ZG000005','S004','OR0017','2015-1-27',20000,'2015-8-18')insert into wusunde.訂購單 values('ZG000009','S004','OR0018','2015-3-5',13000,'2015-

33、7-31')insert into wusunde.訂購單 values('ZG000004','S002','OR0019','2015-3-14',7000,'2015-6-17')insert into wusunde.訂購單 values('ZG000006','S005','OR0020','2015-2-21',4300,'2015-5-25')insert into wusunde.訂購單明細(xì) values(

34、9;OR0020','01','沐浴露',32,240)insert into wusunde.訂購單明細(xì) values('OR0001','02','洗衣粉',30,125)insert into wusunde.訂購單明細(xì) values('OR0012','03','洗衣液',39,59)insert into wusunde.訂購單明細(xì) values('OR0014','04','香皂',14,99)ins

35、ert into wusunde.訂購單明細(xì) values('OR0004','05','鹽',2,169)insert into wusunde.訂購單明細(xì) values('OR0005','06','醬油',14,65)insert into wusunde.訂購單明細(xì) values('OR0011','07','化妝品',26,24)insert into wusunde.訂購單明細(xì) values('OR0019','08

36、','洗面奶',39,68)insert into wusunde.訂購單明細(xì) values('OR0008','09','面膜',5,147)insert into wusunde.訂購單明細(xì) values('OR0003','10','花生油',36,258)insert into wusunde.訂購單明細(xì) values('OR0017','11','鼠標(biāo)',69,72)insert into wusunde.訂購單明細(xì)

37、values('OR0015','12','鼠標(biāo)墊',18,64)insert into wusunde.訂購單明細(xì) values('OR0016','13','移動電源',58,69)insert into wusunde.訂購單明細(xì) values('OR0012','14','插座',37,169)insert into wusunde.訂購單明細(xì) values('OR0011','15','牛奶',

38、72,111)insert into wusunde.訂購單明細(xì) values('OR0020','16','洗潔精',9,114)insert into wusunde.訂購單明細(xì) values('OR0019','17','潔廁液',6,300)insert into wusunde.訂購單明細(xì) values('OR0010','18','雞蛋',14,124)insert into wusunde.訂購單明細(xì) values('OR0009

39、','19','牛肉',38,300)insert into wusunde.訂購單明細(xì) values('OR0016','20','內(nèi)褲',59,654)insert into wusunde.訂購單明細(xì) values('OR0018','21','鞋子',299,300)insert into wusunde.訂購單明細(xì) values('OR0015','22','手表',890,34)insert into

40、 wusunde.訂購單明細(xì) values('OR0005','23','錢包',129,124)insert into wusunde.訂購單明細(xì) values('OR0008','24','行李箱',344,64)insert into wusunde.訂購單明細(xì) values('OR0014','25','手袋',3,874)insert into wusunde.訂購單明細(xì) values('OR0018','26'

41、;,'籃球',360,59)insert into wusunde.訂購單明細(xì) values('OR0003','27','足球',260,36)insert into wusunde.訂購單明細(xì) values('OR0005','28','羽毛球拍',78,69)insert into wusunde.訂購單明細(xì) values('OR0007','29','乒乓球拍',89,145)insert into wusunde.訂購單明細(xì)

42、values('OR0020','30','羽毛球',5,645)insert into wusunde.訂購單明細(xì) values('OR0013','31','乒乓球',3,542)insert into wusunde.訂購單明細(xì) values('OR0016','32','牙膏',23,200)insert into wusunde.訂購單明細(xì) values('OR0009','33','牙刷',6

43、,456)insert into wusunde.訂購單明細(xì) values('OR0019','34','防曬霜',56,65)insert into wusunde.訂購單明細(xì) values('OR0017','35','水杯',39,1187)insert into wusunde.訂購單明細(xì) values('OR0010','36','拖把',15,187)insert into wusunde.訂購單明細(xì) values('OR0004&

44、#39;,'37','掃把',6,200)insert into wusunde.訂購單明細(xì) values('OR0005','38','垃圾桶',16,254)insert into wusunde.訂購單明細(xì) values('OR0006','39','書',46,688)insert into wusunde.訂購單明細(xì) values('OR0016','40','衛(wèi)生紙',13,500)insert into w

45、usunde.訂購單明細(xì) values('OR0020','41','相機',3698,32)insert into wusunde.訂購單明細(xì) values('OR0013','42','手機',2000,200)insert into wusunde.訂購單明細(xì) values('OR0017','43','音響',500,143)insert into wusunde.訂購單明細(xì) values('OR0001','44

46、9;,'吸塵器',1688,40)insert into wusunde.訂購單明細(xì) values('OR0012','45','油煙機',3500,10)insert into wusunde.訂購單明細(xì) values('OR0010','46','臺式電腦',4000,25)insert into wusunde.訂購單明細(xì) values('OR0013','47','空調(diào)',50,230)insert into wusunde.訂

47、購單明細(xì) values('OR0005','48','電視機',1300,100)insert into wusunde.訂購單明細(xì) values('OR0016','49','洗衣機',3400,15)insert into wusunde.訂購單明細(xì) values('OR0011','50','筆記本',6000,20)2、 使用INSERT命令完成記錄的插入操作，要求分別設(shè)計若干違反實體完整性、參照完整性和用戶定義完整性約束的插入操作，

48、并分析原因。1）違反實體完整性 insert into wusunde.倉庫 values('QW0001','惠州',600) 原因：違反了PRIMARY KEY 約束'PK_倉庫_530C599C7F60ED59'。不能在對象'wusunde.倉庫' 中插入重復(fù)鍵。語句已終止。2）違反參照完整性insert into wusunde.職工 values('ZG000011','QW0009',3000)原因：倉庫號列參照倉庫表的倉庫號，此時在倉庫表中找不到倉庫號為QW0009的倉庫。INSERT

49、 語句與FOREIGN KEY 約束"FK_職工_倉庫號_0AD2A005"沖突。該沖突發(fā)生于數(shù)據(jù)庫"實驗"，表"wusunde.倉庫", column '倉庫號'。3）違反用戶定義完整性insert into wusunde.倉庫 values('QW0006','天津',30)原因：在定義時，面積要大于等于50，這里面積為30，明顯違反了用戶定義完整性3、 設(shè)計若干刪除操作，體會執(zhí)行刪除操作時檢查參照完整性規(guī)則的效果（要求涉及拒絕刪除、空值刪除和級聯(lián)刪除等不同的處理方式）。

50、 1）拒絕刪除delete wusunde.倉庫 where 城市='北京'刪除操作失敗。因為有職工參照了干記錄2）空值刪除delete wusunde.供應(yīng)商 where 供應(yīng)商號='S002'因為刪除參照完整性規(guī)則定義為set null3)級聯(lián)刪除alter table wusunde.職工drop constraint ckalter table wusunde.職工drop column 倉庫號alter table wusunde.職工add 倉庫號 char(6)constraint ck foreign key references wu

51、sunde.倉庫on delete cascadeon update cascade 4、 設(shè)計若干更新操作，體會執(zhí)行更新操作時檢查實體完整性規(guī)則、參照完整性規(guī)則和用戶定義完整性規(guī)則的效果。 1）違反參照完整性update wusunde.職工 set 倉庫號='QW0006' where 職工號='ZG000002'更新操作失敗，因為職工表所參考的倉庫表中不存在倉庫號為QW0006的倉庫。2）違反實體完整性update wusunde.倉庫 set 倉庫號=null where 倉庫號='QW0001'更新操作失敗，因為倉庫

52、號是關(guān)鍵字，不能為空。3) 更新違反自定義完整性update wusunde.倉庫 set 面積=30 where 倉庫號='QW0001'更新操作失敗，因為面積被定義約束大于或等于50，此處面積=30，顯然違反了約束。5、 將x城市倉庫的職工的工資提高10%。 update wusunde.職工 set 工資=(工資+(工資*0.1)6、 用UPDATE語句完成訂購單表金額字段的計算（提示：某張訂購單的金額為該訂購單所有訂購項目（訂購單明細(xì)）單價乘以數(shù)量的總和）。 update wusunde.訂購單 set 金額=(select SU

53、M(單價*數(shù)量) from wusunde.訂購單明細(xì) where 訂購單明細(xì).訂購單號=訂購單.訂購單號)7、 設(shè)計23個刪除操作，它需要另外一個表中的數(shù)據(jù)作為刪除條件（例如，按指定的供應(yīng)商名稱刪除訂購單記錄）1）delete wusunde.職工 where 倉庫號 in (select 倉庫號 from wusunde.倉庫 where 城市='北京')2）delete wusunde.訂購單 where 供應(yīng)商號 in (select 供應(yīng)商號 from wusunde.供應(yīng)商 where 供應(yīng)商名='廣工')實驗4 數(shù)據(jù)查詢

54、0;實驗名稱：使用SQL SELECT語句的數(shù)據(jù)查詢操作。 實驗內(nèi)容：在實驗2和實驗3的基礎(chǔ)上完成規(guī)定的查詢操作。 實驗?zāi)康模菏炀氄莆誗QL SELECT命令。 實驗方法：在實驗2和實驗3的基礎(chǔ)上使用SQL SELECT命令完成實驗要求中所規(guī)定的各種查詢。 實驗要求： 用SQL SELECT命令完成以下查詢（在完成查詢時將題目中的x，y等根據(jù)實驗三產(chǎn)生的數(shù)據(jù)具體化）： （1）（12）是一般簡單查詢： 1.職工表查詢所有工資值（考慮去掉和不去掉重復(fù)值兩種情況）。 2.查詢倉庫表

55、中的全部信息。 3.查詢工資多于x元的職工號。 4.查詢哪些倉庫有工資多于y元的職工。 5.給出在倉庫x或y（x，y是倉庫號）工作，并且工資少于z元的職工號。 6.查詢工資在xy元范圍內(nèi)的職工信息（x>y）。 7.從供應(yīng)商表中查詢出全部公司的信息（不要工廠或其他供應(yīng)商的信息，即供應(yīng)商名最后兩個漢字是“公司”的記錄）。 8.查詢城市不是x的全部倉庫信息。 9.查詢尚未確定供應(yīng)商的訂購單（即訂購單的供應(yīng)商號為空值的記錄）。 10.查詢已經(jīng)確定了供應(yīng)商的訂購單信息。 11.按職工的工資值升序列出全部職工信

56、息。 12.先按倉庫號升序，再按工資降序列出全部職工信息。  （13）（21）使用連接查詢： 13.查詢工資多于x元的職工號和他們所在的城市。 14.查詢工作在面積大于x的倉庫的職工號以及這些職工工作所在的城市。 15.根據(jù)職工表列出上一級經(jīng)理及其職工（被其領(lǐng)導(dǎo)）的清單（即列出誰領(lǐng)導(dǎo)誰的清單）。 16.列出倉庫表和職工表的廣義笛卡兒積的去處結(jié)果。 17.根據(jù)倉庫表和職工表的廣義笛卡兒積得到倉庫表和職工表進行自然連接的結(jié)果。 18.查詢供應(yīng)商訂購單信息，查詢結(jié)果中包括供應(yīng)商號、供應(yīng)商名、訂購單號和訂購日期這

57、4個字段。 19.查詢供應(yīng)商訂購單信息，查詢結(jié)果中包括供應(yīng)商號、供應(yīng)商名、訂購單號和訂購日期這4個字段，要求結(jié)果中列出所有供應(yīng)商信息（包括目前沒有訂購單的）。 20.查詢供應(yīng)商訂購單信息，查詢結(jié)果中包括供應(yīng)商號、供應(yīng)商名、訂購單號和訂購日期這4個字段，要求結(jié)果中能夠反映目前沒有確定供應(yīng)商的訂購單情況。 21.查詢供應(yīng)商訂購單信息，查詢結(jié)果中包括供應(yīng)商號、供應(yīng)商名、訂購單號和訂購日期這4個字段，要求結(jié)果即能反映目前沒有訂購半日的供應(yīng)商信息，又能反映目前沒有確定供應(yīng)商的訂購單情況。 提示：（18）（21）分別用內(nèi)連接、左連接、右連接和全連接完成，分析它們的效

58、果。  （22）（31）使用嵌套查詢（注意使用ANY，ALL和EXISTS）： 22.查詢哪些城市至少有一個倉庫的職工的工資為x元。 23.查詢所有職工的工資都多于x元的倉庫的信息。24.查詢和職工x掙同樣工資的所有職工信息。 25.查詢哪些城市的倉庫向地址為x的供應(yīng)商發(fā)出了訂購單。 26.查詢地址為x的供應(yīng)商目前有y城市倉庫的訂購單的供應(yīng)商的名稱（分別用嵌套查詢和連接查詢完成，分析各自的效率）。 27.查詢有職工的工資大于或等于x倉庫中任何一名職工的工資的倉庫號。 28.查詢有職工的工資大于或等于x倉庫中所有職工

59、的工資的倉庫號。 29.列出每人職工經(jīng)手的具有最高金額的訂購單信息。 30.查詢目前還沒有職工的倉庫的信息。31.查詢目前至少有一個職工的倉庫的信息。  （32）（42）匯總和分組查詢： 32.查詢目前倉庫所在城市的數(shù)目。 33.查詢目前需要支付的職工工資的總數(shù)。 34.查詢x和y兩個城市的倉庫職工的工資總和。 35.查詢所有職工的工資都多于x元的倉庫的平均面積。 36.查詢工資大于x元的職工所經(jīng)手的訂購單的最高金額。 37.查詢每個倉庫的職工的平均工資。 38.查詢每個倉庫的訂購單的

60、最高金額、最低金額和平均金額。 39.查詢至少有5個訂購項目的每張訂購單的平均金額。 40.列出職工全部記錄并計算各倉庫的平均工資和工資小計，最后給出全體職工的平均工資和工資總和（使用COMPUTEBY和COMPUTE）。 41.列出全部訂購單明細(xì)并計算各訂購單的金額，最后給出所有訂購單的平均金額和金額總計（使用COMPUTEBY和COMPUTE）。 42.列出全部訂購單記錄并計算所有訂購單的平均金額和金額總計（使用COMPUTE）實驗操作： 1.職工表查詢所有工資值（考慮去掉和不去掉重復(fù)值兩種情況）。 select distinct

61、 工資 from wusunde.職工（去掉重復(fù)值）select all 工資 from wusunde.職工（不去掉重復(fù)）2.查詢倉庫表中的全部信息。 select * from wusunde.倉庫3.查詢工資多于x元的職工號。 select 職工號 from wusunde.職工 where 工資>50004.查詢哪些倉庫有工資多于y元的職工。 select * from wusunde.倉庫 where 倉庫號 in(select 倉庫號 from wusunde.職工 where 工資>5000)5.給出在倉庫x或y（x，y是倉庫號）工作，并

62、且工資少于z元的職工號。select 職工號 from wusunde.職工 where 倉庫號 in ('QW0005','QW0002') and 工資<50006.查詢工資在xy元范圍內(nèi)的職工信息（x>y）。 select * from wusunde.職工 where 工資 between 3000 and 50007.從供應(yīng)商表中查詢出全部公司的信息（不要工廠或其他供應(yīng)商的信息，即供應(yīng)商名最后兩個漢字是“公司”的記錄）。 select * from wusunde.供應(yīng)商 where 供應(yīng)商名 like '%公司

63、'8.查詢城市不是x的全部倉庫信息。 select * from wusunde.倉庫 where 城市!='北京'9.查詢尚未確定供應(yīng)商的訂購單（即訂購單的供應(yīng)商號為空值的記錄）。select * from wusunde.訂購單 where 供應(yīng)商號 is null 10.查詢已經(jīng)確定了供應(yīng)商的訂購單信息。 select * from wusunde.訂購單 where 供應(yīng)商號 is not null11.按職工的工資值升序列出全部職工信息。 select * from wusunde.職工 order by 工資 ASC1

64、2.先按倉庫號升序，再按工資降序列出全部職工信息。  select * from wusunde.職工 order by 倉庫號 ASC,工資 DESC13.查詢工資多于x元的職工號和他們所在的城市。 select 職工號,城市 from wusunde.職工 join wusunde.倉庫 on 職工.倉庫號=倉庫.倉庫號 where 工資>600014.查詢工作在面積大于x的倉庫的職工號以及這些職工工作所在的城市。 select 職工號,城市 from wusunde.職工 join wusunde.倉庫 on 職工.倉庫號=倉庫.倉庫號 wh

65、ere 面積>70015.根據(jù)職工表列出上一級經(jīng)理及其職工（被其領(lǐng)導(dǎo)）的清單（即列出誰領(lǐng)導(dǎo)誰的清單）。略 16.列出倉庫表和職工表的廣義笛卡兒積的去處結(jié)果。 select * from wusunde.倉庫 cross join wusunde.職工17.根據(jù)倉庫表和職工表的廣義笛卡兒積得到倉庫表和職工表進行自然連接的結(jié)果。 select * from wusunde.倉庫 cross join wusunde.職工where 倉庫.倉庫號=職工.倉庫號18.查詢供應(yīng)商訂購單信息，查詢結(jié)果中包括供應(yīng)商號、供應(yīng)商名、訂購單號和訂購日期這4個字段。 s

66、elect 供應(yīng)商.供應(yīng)商號,供應(yīng)商名,訂購單號,訂購日期 from wusunde.供應(yīng)商 join wusunde.訂購單on 供應(yīng)商.供應(yīng)商號=訂購單.供應(yīng)商號19.查詢供應(yīng)商訂購單信息，查詢結(jié)果中包括供應(yīng)商號、供應(yīng)商名、訂購單號和訂購日期這4個字段，要求結(jié)果中列出所有供應(yīng)商信息（包括目前沒有訂購單的）。 select 供應(yīng)商.供應(yīng)商號,供應(yīng)商名,訂購單號,訂購日期 from wusunde.供應(yīng)商 left join wusunde.訂購單on 供應(yīng)商.供應(yīng)商號=訂購單.供應(yīng)商號20.查詢供應(yīng)商訂購單信息，查詢結(jié)果中包括供應(yīng)商號、供應(yīng)商名、訂購單號和訂購日期這4個字段，要求結(jié)

67、果中能夠反映目前沒有確定供應(yīng)商的訂購單情況。 select 供應(yīng)商.供應(yīng)商號,供應(yīng)商名,訂購單號,訂購日期 from wusunde.供應(yīng)商 right join wusunde.訂購單 on 供應(yīng)商.供應(yīng)商號=訂購單.供應(yīng)商號21.查詢供應(yīng)商訂購單信息，查詢結(jié)果中包括供應(yīng)商號、供應(yīng)商名、訂購單號和訂購日期這4個字段，要求結(jié)果即能反映目前沒有訂購半日的供應(yīng)商信息，又能反映目前沒有確定供應(yīng)商的訂購單情況。 select 供應(yīng)商.供應(yīng)商號,供應(yīng)商名,訂購單號,訂購日期 from wusunde.供應(yīng)商 full join wusunde.訂購單 on 供應(yīng)商.供應(yīng)商號=訂購單.供應(yīng)商號22.查詢哪些城市至少有一個倉庫的職工的工資為x元。 select 城市 from wusunde.倉庫 where 倉庫號