word實(shí)驗(yàn)四 SQL練習(xí)2一、實(shí)驗(yàn)?zāi)康?1掌握索引的建立、刪除與使用； 2掌握單表查詢、連接查詢、嵌套查詢和集合查詢； 3掌握插入數(shù)據(jù)、修改數(shù)據(jù)和刪除數(shù)據(jù)語句的非常用形式。二、實(shí)驗(yàn)學(xué)時2學(xué)時三、實(shí)驗(yàn)容1利用Query Analyzer完成以下操作： 在預(yù)算日期、結(jié)算日期和入賬日期上分別建立索引，并在查詢操作中體會索引的作用。 在完成第2題的查詢操作后，刪除預(yù)算日期、結(jié)算日期和入賬日期上的索引。2利用Query Analyzer完成以下操作： 采油一礦二隊(duì)2016-5-1到2016-5-28有哪些項(xiàng)目完成了預(yù)算，列出相應(yīng)明細(xì)。 采油一礦二隊(duì)2016-5-1到2016-5-28有哪些項(xiàng)目完成了結(jié)算，列出相應(yīng)明細(xì)。 采油一礦二隊(duì)2016-5-1到2016-5-28有哪些項(xiàng)目完成了結(jié)算，列出相應(yīng)的材料費(fèi)消耗明細(xì)。 采油一礦二隊(duì)2016-5-1到2016-5-28有哪些項(xiàng)目完成了入賬，列出相應(yīng)明細(xì)。 列出采油一礦二隊(duì)2016-5-1到2016-5-28總的預(yù)算金額。 列出采油一礦二隊(duì)2016-5-1到2016-5-28總的結(jié)算金額。 列出采油一礦二隊(duì)2016-5-1到2016-5-28總的入賬金額。 列出采油一礦2016-5-1到2016-5-28總的入賬金額。 有哪些人員參與了入賬操作。 列出2016-5-1到2016-5-28進(jìn)展了結(jié)算但未入賬的項(xiàng)目。 列出采油一礦二隊(duì)的所有項(xiàng)目，按入賬金額從高到低排列。 列出有哪些施工單位實(shí)施了項(xiàng)目，并計(jì)算各單位所有項(xiàng)目結(jié)算金額總和。 找出消耗了材料三且消耗超過了2000元的項(xiàng)目，列出相應(yīng)消耗明細(xì)(利用子查詢)。 作業(yè)公司二隊(duì)參與了哪些項(xiàng)目。 作業(yè)公司一隊(duì)和二隊(duì)參與了哪些項(xiàng)目利用union。 采油一礦的油井是哪些作業(yè)隊(duì)參與施工的。3利用Query Analyzer完成以下操作： 建立數(shù)據(jù)表(包含3個屬性列：施工單位、年月、結(jié)算金額)保存各個施工單位每月的結(jié)算金額總和。 用子查詢將各個施工單位每月的結(jié)算金額總和插入到所建立的數(shù)據(jù)表中。 用帶子查詢的修改語句將采油一礦油井作業(yè)項(xiàng)目的結(jié)算人改為“兵。 用帶子查詢的刪除語句刪除采油一礦油井作業(yè)項(xiàng)目。 撤消上述兩個操作。四、實(shí)驗(yàn)報(bào)告提交實(shí)驗(yàn)容中用SQL語句完成的題目的SQL語句文檔與相應(yīng)的執(zhí)行結(jié)果。實(shí)驗(yàn)五 SQL練習(xí)3一、實(shí)驗(yàn)?zāi)康?1掌握根本表的刪除與修改； 2掌握實(shí)體完整性、參照完整性和用戶定義的完整性的定義、檢查和違約處理； 3掌握視圖的定義、查詢和更新，了解視圖的作用。二、實(shí)驗(yàn)學(xué)時2學(xué)時三、實(shí)驗(yàn)容1利用Query Analyzer完成以下操作： 向在實(shí)驗(yàn)四中所定義的數(shù)據(jù)表增加“備注列，其數(shù)據(jù)類型為字符型，并查看新增列的值。 對上述數(shù)據(jù)表增加主碼約束條件，并觀察在數(shù)據(jù)表中存在數(shù)據(jù)的情況下主碼約束是否創(chuàng)建成功，然后再次執(zhí)行實(shí)驗(yàn)四中實(shí)驗(yàn)容3.2的操作，并觀察記錄執(zhí)行結(jié)果。 刪除上述數(shù)據(jù)表中的數(shù)據(jù)，然后再刪除該數(shù)據(jù)表，對這兩個操作進(jìn)展比擬。2利用Query Analyzer完成以下任務(wù)：對實(shí)驗(yàn)三中所定義的6個數(shù)據(jù)表增加主碼約束條件，并觀察在數(shù)據(jù)表中存在數(shù)據(jù)的情況下主碼約束是否創(chuàng)建成功，然后執(zhí)行以下2個操作，觀察并記錄實(shí)體完整性的檢查和違約處理。 insert into 材料費(fèi)表 values('zy2016001','wm004',100,10)insert into 材料費(fèi)表 values('zy2016002',NULL,200,10)注：“材料費(fèi)表根據(jù)自己所命名的表名進(jìn)展相應(yīng)的替換。對實(shí)驗(yàn)三中所定義的6個數(shù)據(jù)表增加相應(yīng)的參照完整性約束，并觀察在數(shù)據(jù)表中存在數(shù)據(jù)的情況下參照完整性約束是否創(chuàng)建成功，然后執(zhí)行以下操作，觀察并記錄參照完整性的檢查和違約處理。 將y007 油井 112203002插入到油水井表。 insert into 材料費(fèi)表 values('zy2016007','wm006',100,10) 將作業(yè)項(xiàng)目編號zy2016001的施工單位修改為“作業(yè)公司作業(yè)五隊(duì)。 將單位代碼表中的112202002 采油二礦二隊(duì)刪除，查看油水井表和作業(yè)項(xiàng)目表中的數(shù)據(jù)有何變化。 將物碼表中的wm004 材料四 袋修改為wm04 材料四 袋。 撤銷上述成功的更新操作。注：“材料費(fèi)表根據(jù)自己所命名的表名進(jìn)展相應(yīng)的替換。 對實(shí)驗(yàn)三中所定義的6個數(shù)據(jù)表按以下要求增加相應(yīng)的完整性約束條件，并觀察在數(shù)據(jù)表中存在數(shù)據(jù)的情況下完整性約束是否創(chuàng)建成功。 單位代碼表的單位名稱不能取空值、且取值唯一。 油水井表的井別只允許取“油井或“水井，單位代碼不能取空值。 物碼表的名稱規(guī)格不能取空值、且取值唯一，計(jì)量單位不能取空值。 材料費(fèi)表的消耗數(shù)量不能取空值，單價(jià)不能取空值。 對作業(yè)項(xiàng)目表根據(jù)實(shí)際應(yīng)用的要求定義適當(dāng)?shù)挠脩舳x的完整性約束條件。3利用Query Analyzer完成以下操作： 定義一個視圖，用于保存作業(yè)項(xiàng)目表和材料費(fèi)表的全部列。 查詢上面定義的視圖，可任意組合查詢條件，構(gòu)造出2個查詢。 定義一個反映作業(yè)項(xiàng)目預(yù)算狀態(tài)的視圖，并向該視圖插入('zy2016008','112202002','y005',10000,'三', '2016-07-02')，查看作業(yè)項(xiàng)目表的數(shù)據(jù)有何變化。 撤銷上述成功的更新操作。四、實(shí)驗(yàn)報(bào)告提交實(shí)驗(yàn)容中用SQL語句完成的題目的SQL語句文檔與相應(yīng)的執(zhí)行結(jié)果。實(shí)驗(yàn)六 其它數(shù)據(jù)庫對象的管理一、實(shí)驗(yàn)?zāi)康?1掌握事務(wù)的概念、性質(zhì)、定義與使用； 2掌握游標(biāo)的概念、組成、創(chuàng)建與使用；3掌握存儲過程的概念、類型、特點(diǎn)、創(chuàng)建、執(zhí)行與管理。4掌握觸發(fā)器的概念、創(chuàng)建、管理與使用。二、實(shí)驗(yàn)學(xué)時2學(xué)時三、實(shí)驗(yàn)容 1利用Query Analyzer進(jìn)展如下事務(wù)處理練習(xí)把如下五條語句作為一個事務(wù)處理，只有五條語句全部成功執(zhí)行才做提交，并給出成功的提示信息；否如此就做回退處理，并給出具體的錯誤提示信息：insert into 作業(yè)項(xiàng)目表 values('zy2016006','112202002','y005',10000,'三', '07-01-2016' ,'07-04-2016','07-25-2016','作業(yè)公司作業(yè)一隊(duì)','堵漏',7000,2500,1000,1400,11900,'四','07-26-2016',11900,'王五','07-28-2016')insert into 材料費(fèi)表 values('zy2016006','wm001',200,10)insert into 材料費(fèi)表 values('zy2016006','wm002',200,10)insert into 材料費(fèi)表 values('zy2016006','wm003',200,10)insert into 材料費(fèi)表 values('zy2016006','wm004',100,10)注：“作業(yè)項(xiàng)目表、“材料費(fèi)表根據(jù)自己所命名的表名進(jìn)展相應(yīng)的替換。2利用Query Analyzer進(jìn)展如下游標(biāo)練習(xí)：定義一個游標(biāo)，用于存放作業(yè)項(xiàng)目表的全部行數(shù)據(jù)，并打印以下表頭和各行數(shù)據(jù)。表頭：單據(jù)號預(yù)算單位井號預(yù)算金額預(yù)算人預(yù)算日期開工日期完工日期施工單位施工容材料費(fèi)人工費(fèi)設(shè)備費(fèi)其它費(fèi)用結(jié)算金額結(jié)算人結(jié)算日期入賬金額入賬人入賬日期執(zhí)行以上所定義的游標(biāo)，查看是否能正確輸出結(jié)果。3利用Query Analyzer定義一個存儲過程，要求完成以下功能：生成某單位(單位可以是采油廠或采油礦或采油隊(duì))某段時間的本錢運(yùn)行情況輸入?yún)?shù)：單位代碼 起始日期 完畢日期。輸出格式 *單位*時間-*時間本錢運(yùn)行情況預(yù)算金額 結(jié)算金額 入賬金額 未結(jié)算金額 未入賬金額 *.* *.* *.* *.* *.*其中：未結(jié)算金額=預(yù)算金額-結(jié)算金額 未入賬金額=結(jié)算金額-入賬金額分三種情況單位分別為：采油廠、采油礦、采油隊(duì)執(zhí)行以上定義的存儲過程，查看執(zhí)行輸出結(jié)果。4利用Query Analyzer針對作業(yè)項(xiàng)目表定義三個觸發(fā)器，分別完成以下功能： 對作業(yè)項(xiàng)目表插入一行數(shù)據(jù)時，自動計(jì)算并插入結(jié)算金額字段結(jié)算金額=材料費(fèi)+人工費(fèi)+設(shè)備費(fèi)+其它費(fèi)用。 當(dāng)修改作業(yè)表的某行數(shù)據(jù)時自動修改結(jié)算金額字段。 當(dāng)刪除作業(yè)表中一行數(shù)據(jù)時，自動刪除材料費(fèi)表中相應(yīng)明細(xì)數(shù)據(jù)。 對上述3個觸發(fā)器用適當(dāng)?shù)母抡Z句進(jìn)展驗(yàn)證，并查看結(jié)果是否達(dá)到預(yù)期結(jié)果。四、實(shí)驗(yàn)報(bào)告提交實(shí)驗(yàn)容中用SQL語句完成的題目的SQL語句文檔與相應(yīng)的執(zhí)行結(jié)果。實(shí)驗(yàn)七 安全機(jī)制2利用Query Analyzer完成以下操作： 建立采油一礦的作業(yè)項(xiàng)目的視圖，把該視圖的查詢權(quán)限授予給采油一礦的用戶user11，以user11的身份查詢該視圖，觀察執(zhí)行情況；再以其他用戶的身份查詢該視圖，觀察執(zhí)行情況。 創(chuàng)建一個用戶user12，以user12的身份執(zhí)行實(shí)驗(yàn)六中所定義的存儲過程，觀察記錄是否成功執(zhí)行；然后把該存儲過程的執(zhí)行權(quán)限授予給user12，再次以user12的身份執(zhí)行該存儲過程，觀察記錄是否成功執(zhí)行。 定義觸發(fā)器，實(shí)現(xiàn)只能在工作時間更新“作業(yè)項(xiàng)目表的數(shù)據(jù)，然后通過選擇不同的時間進(jìn)展適當(dāng)?shù)母虏僮鱽眚?yàn)證。-實(shí)驗(yàn)3-CREATETABLE單位代碼表(單位代碼CHAR(20),單位名稱CHAR (20);CREATETABLE油水井表(井號CHAR(20),井別CHAR(20),單位代碼CHAR(20);CREATETABLE施工單位表(施工單位名稱CHAR(20);CREATETABLE物碼表(物碼CHAR(20),名稱規(guī)格CHAR(20),計(jì)量單位CHAR(20);createtable材料費(fèi)表(單據(jù)號CHAR(20),物碼CHAR(20),消耗數(shù)量INT,單價(jià)MONEY,);CREATETABLE作業(yè)項(xiàng)目表(單據(jù)號CHAR(20),預(yù)算單位CHAR(20),井號CHAR(20),預(yù)算金額MONEY,預(yù)算人CHAR(20),預(yù)算日期DATETIME,開工日期DATETIME,完工日期DATETIME,施工單位CHAR(20),施工容CHAR(20),材料費(fèi)money,人工費(fèi)MONEY,設(shè)備費(fèi)MONEY,其他費(fèi)用money,結(jié)算金額MONEY,結(jié)算人CHAR(20),結(jié)算日期DATETIME,入賬金額MONEY,入賬人CHAR(10),入賬日期DATETIME)-insertinto單位代碼表values ('1122','采油廠');insertinto單位代碼表values ('112201','采油一礦');insertinto單位代碼表values ('112202','采油二礦');insertinto單位代碼表values ('112201001','采油一礦一隊(duì)');insertinto單位代碼表values ('112201002','采油一礦二隊(duì)');insertinto單位代碼表values ('112201003','采油一礦三隊(duì)');insertinto單位代碼表values ('112202001','采油二礦一隊(duì)');insertinto油水井表values ('y001','油井','112201001');insertinto油水井表values ('y002','油井','112201001');insertinto油水井表values ('y003','油井','112201002');insertinto油水井表values ('s001','水井','112201002');insertinto油水井表values ('y004','油井','112201003');insertinto油水井表values ('s002','水井','112202001');insertinto油水井表values ('s003','水井','112202001');insertinto油水井表values ('y005','油井','112202002');insertinto施工單位表values ('作業(yè)公司作業(yè)一隊(duì)');insertinto施工單位表values ('作業(yè)公司作業(yè)二隊(duì)');insertinto施工單位表values ('作業(yè)公司作業(yè)三隊(duì)');insertinto物碼表values ('wm001','材料一','噸');insertinto物碼表values ('wm002','材料二','米');insertinto物碼表values ('wm003','材料三','桶');insertinto物碼表values ('wm004','材料四','袋');insertinto材料費(fèi)表(單據(jù)號,物碼,消耗數(shù)量,單價(jià))values ('zy2016001','wm001',200,￥10);insertinto材料費(fèi)表(單據(jù)號,物碼,消耗數(shù)量,單價(jià))values ('zy2016001','wm002',200,￥10);insertinto材料費(fèi)表(單據(jù)號,物碼,消耗數(shù)量,單價(jià))values ('zy2016001','wm003',200,￥10);-insertinto材料費(fèi)表(單據(jù)號,物碼,消耗數(shù)量,單價(jià))values ('zy2016003','wm001',200,￥10);insertinto材料費(fèi)表(單據(jù)號,物碼,消耗數(shù)量,單價(jià))values ('zy2016003','wm002',200,￥10);insertinto材料費(fèi)表(單據(jù)號,物碼,消耗數(shù)量,單價(jià))values ('zy2016003','wm003',250,￥10);-insertinto材料費(fèi)表(單據(jù)號,物碼,消耗數(shù)量,單價(jià))values ('zy2016004','wm001',200,￥10);insertinto材料費(fèi)表(單據(jù)號,物碼,消耗數(shù)量,單價(jià))values ('zy2016004','wm002',200,￥10);insertinto材料費(fèi)表(單據(jù)號,物碼,消耗數(shù)量,單價(jià))values ('zy2016004','wm004',200,￥10);-insertinto材料費(fèi)表(單據(jù)號,物碼,消耗數(shù)量,單價(jià))values ('zy2016005','wm001',200,￥10);insertinto材料費(fèi)表(單據(jù)號,物碼,消耗數(shù)量,單價(jià))values ('zy2016005','wm002',200,￥10);insertinto材料費(fèi)表(單據(jù)號,物碼,消耗數(shù)量,單價(jià))values ('zy2016005','wm004',300,￥10);-insertinto作業(yè)項(xiàng)目表values('zy2016001','112201001','y001',￥10000,'三','2016/5/1','2016/5/4','2016/5/23','作業(yè)公司作業(yè)一隊(duì)','堵漏',￥7000,￥2500,￥1000,￥1400,￥10900,'王五','2016/5/26',￥10900,'王五','2016/5/28');-insertinto作業(yè)項(xiàng)目表values ('zy2016003','112201002','s001',￥10500,'三','2016/5/1','2016/5/6','2016/5/23','作業(yè)公司作業(yè)二隊(duì)','調(diào)剖',￥6500,￥2000,￥500,￥1400,￥10400,'四','2016/5/26',￥10400,'王五','2016/5/28');-insertinto作業(yè)項(xiàng)目表values ('zy2016004','112202001','s002',￥12000,'三','2016/5/1','2016/5/4','2016/5/24','作業(yè)公司作業(yè)三隊(duì)','解堵',￥6000,￥2000,￥1000,￥1600,￥10600,'四','2016/5/26',￥10600,'六','2016/5/28');-insertinto作業(yè)項(xiàng)目表values('zy2016005','112202002','y005',￥12000,'三','2016/5/1','2016/5/4','2016/5/28','作業(yè)公司作業(yè)三隊(duì)','防砂',￥7000,￥1000,￥2000,￥1300,￥11300,'四','2016/6/1',NULL,NULL,NULL);-begintranupdate作業(yè)項(xiàng)目表set人工費(fèi)=人工費(fèi)+200 where單據(jù)號='zy2016005'update作業(yè)項(xiàng)目表set結(jié)算金額=結(jié)算金額+200where單據(jù)號='zy2016005'deletefrom作業(yè)項(xiàng)目表where入賬金額isNULL;rollbacktran-實(shí)驗(yàn)四-1createindex預(yù)算日期索引on作業(yè)項(xiàng)目表(預(yù)算日期);createindex結(jié)算日期索引on作業(yè)項(xiàng)目表(結(jié)算日期);createindex入賬日期索引on作業(yè)項(xiàng)目表(入賬日期);-(1)select*from作業(yè)項(xiàng)目表where預(yù)算日期between'2016/5/1'and'2016/5/28'and預(yù)算單位=(select單位代碼from單位代碼表where單位名稱='采油一礦二隊(duì)');-another solutionselect*from作業(yè)項(xiàng)目表,單位代碼表where單位代碼=預(yù)算單位and單位名稱='采油一礦二隊(duì)'and預(yù)算日期between'2016/5/1'and'2016/5/28'-(2)select*from作業(yè)項(xiàng)目表where結(jié)算日期between'2016/5/1'and'2016/5/28'and預(yù)算單位=(select單位代碼from單位代碼表where單位名稱='采油一礦二隊(duì)');-another solution-select *-from 作業(yè)項(xiàng)目表,單位代碼表-where 單位名稱='采油一礦二隊(duì)' and 預(yù)算單位=單位代碼 and 結(jié)算日期>='2016-5-1'and 結(jié)算日期<='2016-5-28'-(3)-select*from材料費(fèi)表where單據(jù)號=(select單據(jù)號from作業(yè)項(xiàng)目表where預(yù)算日期between'2016/5/1'and'2016/5/28'and預(yù)算單位=(select單位代碼from單位代碼表where單位名稱='采油一礦二隊(duì)');-(4)-select*from作業(yè)項(xiàng)目表where入賬日期between'2016/5/1'and'2016/5/28'and預(yù)算單位=(select單位代碼from單位代碼表where單位名稱='采油一礦二隊(duì)');-(5)-select,sum(預(yù)算金額)預(yù)算金額總和from作業(yè)項(xiàng)目表where預(yù)算日期between'2016/5/1'and'2016/5/28'and預(yù)算單位=(select單位代碼from單位代碼表where單位名稱='采油一礦二隊(duì)');-(6)-select,sum(結(jié)算金額)結(jié)算金額總和from作業(yè)項(xiàng)目表where結(jié)算日期between'2016/5/1'and'2016/5/28'and預(yù)算單位=(select單位代碼from單位代碼表where單位名稱='采油一礦二隊(duì)');-(7)-selectsum(入賬金額)入賬金額總和from作業(yè)項(xiàng)目表where入賬日期between'2016/5/1'and'2016/5/28'and預(yù)算單位=(select單位代碼from單位代碼表where單位名稱='采油一礦二隊(duì)');-(8)-selectsum(入賬金額)采油一礦入賬金額總和from作業(yè)項(xiàng)目表,單位代碼表where入賬日期between'2016/5/1'and'2016/5/28'and預(yù)算單位=單位代碼and單位名稱like'采油一礦%'-another solutionselectsum(入賬金額)入賬金額總和from作業(yè)項(xiàng)目表where入賬日期between'2016/5/1'and'2016/5/28'and預(yù)算單位in(select單位代碼from單位代碼表where單位名稱like'采油一礦%');-(9)-selectdistinct入賬人from作業(yè)項(xiàng)目表where入賬人isnotnull;-(10)-select單據(jù)號from作業(yè)項(xiàng)目表where結(jié)算日期between'2016/5/1'and'2016/5/28'and結(jié)算金額isnotnulland入賬金額isnull;-(11)-select單據(jù)號from作業(yè)項(xiàng)目表where預(yù)算單位=(select單位代碼from單位代碼表where單位名稱='采油一礦二隊(duì)')orderby入賬金額desc;-(12)-select施工單位表.施工單位名稱,count(結(jié)算金額)項(xiàng)目個數(shù),sum(結(jié)算金額)結(jié)算金額總和from施工單位表leftjoin作業(yè)項(xiàng)目表on (施工單位表.施工單位名稱=作業(yè)項(xiàng)目表.施工單位)groupby施工單位表.施工單位名稱;-13-select*from材料費(fèi)表where單據(jù)號=(select單據(jù)號from材料費(fèi)表where物碼=(select物碼from物碼表where名稱規(guī)格='材料三')and消耗數(shù)量*單價(jià)>2000);-14-selectdistinct單據(jù)號from作業(yè)項(xiàng)目表where施工單位='作業(yè)公司作業(yè)二隊(duì)'-15-selectdistinct單據(jù)號from作業(yè)項(xiàng)目表where施工單位='作業(yè)公司作業(yè)一隊(duì)'unionselectdistinct單據(jù)號from作業(yè)項(xiàng)目表where施工單位='作業(yè)公司作業(yè)二隊(duì)'-16-selectdistinct施工單位from作業(yè)項(xiàng)目表,油水井表,單位代碼表where預(yù)算單位=單位代碼表.單位代碼and單位代碼表.單位代碼=油水井表.單位代碼and單位名稱like'采油一礦%'-another solutionselectdistinct施工單位from作業(yè)項(xiàng)目表where井號in(select井號from油水井表where單位代碼in(select單位代碼from單位代碼表where單位名稱like'采油一礦%');-dropindex預(yù)算日期索引on作業(yè)項(xiàng)目表;dropindex結(jié)算日期索引on作業(yè)項(xiàng)目表;dropindex入賬日期索引on作業(yè)項(xiàng)目表;-3-(1)-createtable結(jié)算金額表(施工單位char(20),年月varchar(7),結(jié)算金額money);-(2)-insertinto結(jié)算金額表select施工單位,convert(varchar(7),結(jié)算日期,111),sum(結(jié)算金額)from作業(yè)項(xiàng)目表groupby施工單位,convert(varchar(7),結(jié)算日期,111);-another solution-insert -into 結(jié)算金額表-select 施工單位,YEAR(結(jié)算日期)*100+MONTH(結(jié)算日期),sum(結(jié)算金額)-from 作業(yè)項(xiàng)目表-group by 施工單位,YEAR(結(jié)算日期)*100+MONTH(結(jié)算日期);-(3)-begintranupdate作業(yè)項(xiàng)目表set結(jié)算人='兵'where預(yù)算單位in(select單位代碼from單位代碼表where單位名稱like'采油一礦%');-(4)-deletefrom作業(yè)項(xiàng)目表where預(yù)算單位in(select單位代碼from單位代碼表where單位名稱like'采油一礦%');rollback-實(shí)驗(yàn)五-(1)-altertable結(jié)算金額表add備注char(20);select*from結(jié)算金額表-(2)-altertable結(jié)算金額表altercolumn施工單位char(20)notnull;altertable結(jié)算金額表altercolumn年月varchar(7)notnull;altertable結(jié)算金額表addprimarykey(施工單位,年月);TRUNCATETABLE結(jié)算金額表insertinto結(jié)算金額表(施工單位,年月,結(jié)算金額)select施工單位,convert(varchar(7),結(jié)算日期,111),sum(結(jié)算金額)from作業(yè)項(xiàng)目表groupby施工單位,convert(varchar(7),結(jié)算日期,111);-deletefrom結(jié)算金額表;delete結(jié)算金額表;-2-(1)-altertable材料費(fèi)表altercolumn單據(jù)號char(20)notnull;altertable材料費(fèi)表altercolumn物碼char(20)notnull;altertable材料費(fèi)表addprimarykey (單據(jù)號,物碼);-altertable作業(yè)項(xiàng)目表altercolumn單據(jù)號char(20)notnull;altertable作業(yè)項(xiàng)目表addprimarykey (單據(jù)號);-altertable單位代碼表altercolumn單位代碼char(20)notnull;altertable單位代碼表addprimarykey (單位代碼);-altertable施工單位表altercolumn施工單位名稱char(20)notnull;altertable施工單位表addprimarykey (施工單位名稱);-altertable物碼表altercolumn物碼char(20)notnull;altertable物碼表addprimarykey (物碼);-altertable油水井表altercolumn井號char(20)notNUll;altertable油水井表addprimarykey (井號);-begintraninsertinto材料費(fèi)表values('zy2016001','wm004',100,10);insertinto材料費(fèi)表values('zy2016002',NULL,200,10);rollback-(2)-altertable材料費(fèi)表addconstraint a1 foreignkey (單據(jù)號)references作業(yè)項(xiàng)目表(單據(jù)號);altertable材料費(fèi)表addconstraint a2 foreignkey (物碼)references物碼表(物碼);altertable作業(yè)項(xiàng)目表addconstraint b1 foreignkey(預(yù)算單位)references單位代碼表(單位代碼);altertable作業(yè)項(xiàng)目表addconstraint b2 foreignkey(井號)references油水井表(井號);altertable作業(yè)項(xiàng)目表addconstraint b3 foreignkey(施工單位)references施工單位表(施工單位名稱);-insertinto油水井表values ('y007','油井','112203002');insertinto材料費(fèi)表values('zy2016007','wm006',100,10);begintranupdate作業(yè)項(xiàng)目表set施工單位='作業(yè)公司作業(yè)五隊(duì)'where單據(jù)號='zy2016001'deletefrom單位代碼表where單位代碼='112202002'update物碼表set物碼='wm04'where名稱規(guī)格='材料四'rollbacktran-(3)-altertable單位代碼表addconstraint aa1 check (單位名稱isnotnull);altertable單位代碼表addconstraint uni unique(單位代碼);altertable油水井表addconstraint aa2 check(井別in('油井','水井');altertable油水井表altercolumn單位代碼char(20)notnull;altertable物碼表altercolumn名稱規(guī)格char(20)notnull;altertable物碼表addconstraint uni2 unique(名稱規(guī)格);altertable物碼表altercolumn計(jì)量單位char(20)notnull;altertable材料費(fèi)表altercolumn消耗數(shù)量intnotnull;altertable材料費(fèi)表altercolumn單價(jià)intnotnull;altertable作業(yè)項(xiàng)目表addconstraint asdaf check (材料費(fèi)+人工費(fèi)+設(shè)備費(fèi)+其他費(fèi)用=結(jié)算金額);-3-(1)-createview視圖一asselect作業(yè)項(xiàng)目表.*,物碼,消耗數(shù)量,單價(jià)from作業(yè)項(xiàng)目表,材料費(fèi)表where作業(yè)項(xiàng)目表.單據(jù)號=材料費(fèi)表.單據(jù)號;-(2)-select結(jié)算金額,消耗數(shù)量from視圖一where物碼='wm003'select預(yù)算單位,施工容from視圖一where單據(jù)號='zy2016001'and物碼='wm001'-(3)-createview預(yù)算狀態(tài)asselect單據(jù)號,預(yù)算單位,井號,預(yù)算金額,預(yù)算人,預(yù)算日期from作業(yè)項(xiàng)目表begintraninsertinto預(yù)算狀態(tài)values ('zy2016008','112202002','y005',￥10000,'三','2016-07-02');rollbacktran-實(shí)驗(yàn)六-1-begintrangoinsertinto作業(yè)項(xiàng)目表values('zy2016006','112202002','y005',10000,'三','07-01-2016','07-04-2016','07-25-2016','作業(yè)公司作業(yè)一隊(duì)','堵漏',7000,2500,1000,1400,11900,'四','07-26-2016',11900,'王五','07-28-2016')insertinto材料費(fèi)表values('zy2016006','wm001',200,10)insertinto材料費(fèi)表values('zy2016006','wm002',200,10)insertinto材料費(fèi)表values('zy2016006','wm003',200,10)insertinto材料費(fèi)表values('zy2016006','wm004',100,10)iferror<>0beginprint'語句執(zhí)行失敗'rollbackendelsebeginprint'語句執(zhí)行成功'mitend-2-begintrandeclare yb cursorforselect*from作業(yè)項(xiàng)目表open ybdeclare單據(jù)號varchar(20)declare預(yù)算單位varchar(20)declare井號varchar(20)declare預(yù)算金額varchar(20)declare預(yù)算人varchar(20)declare預(yù)算日期varchar(20)declare開工日期varchar(20)declare完工日期varchar(20)declare施工單位varchar(20)declare施工容varchar(20)declare材料費(fèi)varchar(20)declare人工費(fèi)varchar(20)declare設(shè)備費(fèi)varchar(20)declare其它費(fèi)用varchar(20)declare結(jié)算金額varchar(20)declare結(jié)算人varchar(20)declare結(jié)算日期varchar(20)declare入賬金額varchar(20)declare入賬人varchar(20)declare入賬日期varchar(20)print'單據(jù)號 '+'預(yù)算單位 '+'井號 '+'預(yù)算金額 '+'預(yù)算人 '+'預(yù)算日期 '+'開工日期 '+'完工日期 '+'施工單位 '+'施工容 '+'材料費(fèi) '+'人工費(fèi) '+'設(shè)備費(fèi) '+'其它費(fèi)用 '+'結(jié)算金額 '+'結(jié)算人 '+'結(jié)算日期 '+'入賬金額 '+'入賬人'+'入賬日期'fetchnextfrom ybinto單據(jù)號,預(yù)算單位,井號,預(yù)算金額,預(yù)算人,預(yù)算日期,開工日期,完工日期,施工單位,施工容,材料費(fèi),人工費(fèi),設(shè)備費(fèi),其它費(fèi)用,結(jié)算金額,結(jié)算人,結(jié)算日期,入賬金額,入賬人,入賬日期while(fetch_status= 0)beginprint單據(jù)號+' '+預(yù)算單位+' '+井號+' '+預(yù)算金額+' '+預(yù)算人+' '+預(yù)算日期+' '+開工日期+' '+完工日期+' '+施工單位+' '+施工容+' '+材料費(fèi)+' '+人工費(fèi)+' '+設(shè)備費(fèi)+' '+其它費(fèi)用+' '+結(jié)算金額+' '+結(jié)算人+' '+結(jié)算日期+' '+入賬金額+' '+入賬人+' '+入賬日期fetchnextfrom ybinto單據(jù)號,預(yù)算單位,井號,預(yù)算金額,預(yù)算人,預(yù)算日期,開工日期,完工日期,施工單位,施工容,材料費(fèi),人工費(fèi),設(shè)備費(fèi),其它費(fèi)用,結(jié)算金額,結(jié)算人,結(jié)算日期,入賬金額,入賬人,入賬日期;end-3-ifobject_id('_someDepCost','p')isnotnulldropprocedure _someDepCost;gocreateprocedure _someDepCost單位代碼varchar(40),起始日期datetime,完畢日期datetimeasdeclare單位名稱varchar(40);declare預(yù)算金額money,結(jié)算金額money,入賬金額money,未結(jié)算金額money,未入賬金額money;select單位名稱=單位名稱from單位代碼表where單位代碼=單位代碼set預(yù)算金額=(selectsum(預(yù)算金額)from作業(yè)項(xiàng)目表where預(yù)算單位like單位代碼+'%'and(預(yù)算日期between起始日期and完畢日期)set結(jié)算金額=(selectsum(結(jié)算金額)from作業(yè)項(xiàng)目表where預(yù)算單位like單位代碼+'%'and(結(jié)算日期between起始日期and完畢日期)set入賬金額=(selectsum(入賬金額)from作業(yè)項(xiàng)目表where預(yù)算單位like單位代碼+'%'and(入賬日期between起始日期and完畢日期)and(入賬金額isnotnull)set未結(jié)算金額=(selectsum(預(yù)算金額)from作業(yè)項(xiàng)目表where (結(jié)算金額isnull)and(預(yù)算日期between起始日期and完畢日期)set未入賬金額=(selectsum(結(jié)算金額)from作業(yè)項(xiàng)目表where (入賬金額isnull)and(結(jié)算日期between起始日期and完畢日期)set未結(jié)算金額=預(yù)算金額-結(jié)算金額set未入賬金額=結(jié)算金額-入賬金額if預(yù)算金額isnullset預(yù)算金額= 0if結(jié)算金額isnullset結(jié)算金額= 0if入賬金額isnullset入賬金額= 0if未結(jié)算金額isnullset未結(jié)算金額= 0if未入賬金額isnullset未入賬金額= 0declare result varchar(100)set result =convert(varchar,預(yù)算金額)+' '+convert(varchar,結(jié)算金額)+' '+convert(varchar,入賬金額)+' '+convert(varchar,未結(jié)算金額)+' '+convert(varchar,未入賬金額)print單位名稱+'單位'+convert(varchar,起始日期,102)+'-'+convert(varchar,完畢日期,102)+'本錢運(yùn)營狀況'print'預(yù)算金額結(jié)算金額入賬金額未結(jié)算金額未入賬金額'print resultgoexecute _someDepCost單位代碼='1122',起始日期='2016-5-1',完畢日期='2016-5-29'execute _someDepCost單位代碼='112201',起始日期='2016-5-1',完畢日期='2016-5-29'execute _someDepCost單位代碼='112201001',起始日期='2016-5-1',完畢日期='2016-5-29'go-4-(1)-ifOBJECT_ID('trmytr1','TR')isnotnulldroptrigger tr_mytrlgocreatetrigger trmytr1 on作業(yè)項(xiàng)目表afterinsertasdeclare結(jié)算金額moneydeclare temp1 money,temp2 money,temp3 money,temp4 money;select temp1 =材料費(fèi),temp2 =人工費(fèi),temp3 =設(shè)備費(fèi),temp4 =其他費(fèi)用fr