《數(shù)據(jù)庫(kù)系統(tǒng)原理》實(shí)驗(yàn)報(bào)告.doc

《《數(shù)據(jù)庫(kù)系統(tǒng)原理》實(shí)驗(yàn)報(bào)告.doc》由會(huì)員分享，可在線閱讀，更多相關(guān)《《數(shù)據(jù)庫(kù)系統(tǒng)原理》實(shí)驗(yàn)報(bào)告.doc（45頁(yè)珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

學(xué) 生 實(shí) 驗(yàn) 報(bào) 告 （理工類） 課程名稱：數(shù)據(jù)庫(kù)系統(tǒng)原理 專業(yè)班級(jí)： 14軟件工程1班 學(xué)生學(xué)號(hào)： 1412101055 學(xué)生姓名： 孟祥輝 所屬院部： 軟件工程學(xué)院 指導(dǎo)教師： 麻春艷 20 15 ——20 16 學(xué)年 第 二 學(xué)期 金陵科技學(xué)院教務(wù)處制 實(shí)驗(yàn)報(bào)告書寫要求 實(shí)驗(yàn)報(bào)告原則上要求學(xué)生手寫，要求書寫工整。若因課程特點(diǎn)需打印的，要遵照以下字體、字號(hào)、間距等的具體要求。紙張一律采用A4的紙張。 實(shí)驗(yàn)報(bào)告書寫說(shuō)明 實(shí)驗(yàn)報(bào)告中一至四項(xiàng)內(nèi)容為必填項(xiàng)，包括實(shí)驗(yàn)?zāi)康暮鸵?；?shí)驗(yàn)儀器和設(shè)備；實(shí)驗(yàn)內(nèi)容與過(guò)程；實(shí)驗(yàn)結(jié)果與分析。各院部可根據(jù)學(xué)科特點(diǎn)和實(shí)驗(yàn)具體要求增加項(xiàng)目。 填寫注意事項(xiàng) （1）細(xì)致觀察，及時(shí)、準(zhǔn)確、如實(shí)記錄。 （2）準(zhǔn)確說(shuō)明，層次清晰。 （3）盡量采用專用術(shù)語(yǔ)來(lái)說(shuō)明事物?！? （4）外文、符號(hào)、公式要準(zhǔn)確，應(yīng)使用統(tǒng)一規(guī)定的名詞和符號(hào)。 （5）應(yīng)獨(dú)立完成實(shí)驗(yàn)報(bào)告的書寫，嚴(yán)禁抄襲、復(fù)印，一經(jīng)發(fā)現(xiàn)，以零分論處。 實(shí)驗(yàn)報(bào)告批改說(shuō)明 實(shí)驗(yàn)報(bào)告的批改要及時(shí)、認(rèn)真、仔細(xì)，一律用紅色筆批改。實(shí)驗(yàn)報(bào)告的批改成績(jī)采用百分制，具體評(píng)分標(biāo)準(zhǔn)由各院部自行制定。 實(shí)驗(yàn)報(bào)告裝訂要求 實(shí)驗(yàn)批改完畢后，任課老師將每門課程的每個(gè)實(shí)驗(yàn)項(xiàng)目的實(shí)驗(yàn)報(bào)告以自然班為單位、按學(xué)號(hào)升序排列，裝訂成冊(cè)，并附上一份該門課程的實(shí)驗(yàn)大綱。 實(shí)驗(yàn)項(xiàng)目名稱：數(shù)據(jù)庫(kù)定義與操作語(yǔ)言 實(shí)驗(yàn)學(xué)時(shí)： 2 同組學(xué)生姓名： 孟陳、陳曉雪、季佰軍 實(shí)驗(yàn)地點(diǎn)： 1318 實(shí)驗(yàn)日期： 5.19 實(shí)驗(yàn)成績(jī)： 批改教師： 批改時(shí)間： 一、實(shí)驗(yàn)?zāi)康? 1、理解和掌握數(shù)據(jù)庫(kù)DDL語(yǔ)言，能夠熟練地使用SQL DDL語(yǔ)句創(chuàng)建、修改和刪除數(shù)據(jù)庫(kù)、模式和基本表。 2、掌握SQL冊(cè)亨徐設(shè)計(jì)基本規(guī)范，熟練運(yùn)用SQL語(yǔ)言實(shí)現(xiàn)數(shù)據(jù)基本查詢，包括單表查詢、分組統(tǒng)計(jì)查詢和連接查詢 3、掌握SQL嵌套查詢和集合查詢等, 各種高級(jí)查詢的設(shè)計(jì)方法等. 4、熟悉數(shù)據(jù)庫(kù)的數(shù)據(jù)更新操作，能夠使用sql語(yǔ)句對(duì)數(shù)據(jù)庫(kù)進(jìn)行數(shù)據(jù)的插入、修改、刪除操作。 5、熟悉sql語(yǔ)言有關(guān)系圖的操作，能夠熟練使用sql語(yǔ)言來(lái)創(chuàng)建需要的視圖，定義數(shù)據(jù)庫(kù)外模式，并能使用所創(chuàng)建的視圖實(shí)現(xiàn)數(shù)據(jù)管理。 6、掌握所以設(shè)計(jì)原則和技巧，能夠創(chuàng)建合適的索引以提高數(shù)據(jù)庫(kù)查詢、統(tǒng)計(jì)分析效率。 二、實(shí)驗(yàn)內(nèi)容和要求 1、理解和掌握SQL DDL語(yǔ)句的語(yǔ)法，特別是各種參數(shù)的具體含義和使用方法；使用sql語(yǔ)句創(chuàng)建、修改和刪除數(shù)據(jù)庫(kù)、模式和基本表。掌握sql語(yǔ)句常見語(yǔ)法錯(cuò)誤的調(diào)試方法。 2、針對(duì)TPC-H數(shù)據(jù)庫(kù)設(shè)計(jì)各種單表查詢sql語(yǔ)句、分組統(tǒng)計(jì)查詢語(yǔ)句；設(shè)計(jì)單個(gè)表針對(duì)自身的連接查詢，涉及多個(gè)表的連接查詢。理解和掌握sql查詢語(yǔ)句各個(gè)子句的特點(diǎn)和作用，按照sql程序設(shè)計(jì)規(guī)范寫出具體的sql查詢語(yǔ)句，并調(diào)試通過(guò)。 3、針對(duì)TPC-H數(shù)據(jù)庫(kù)，證券分析用戶查詢要求，設(shè)計(jì)各種嵌套查詢和集合查詢。 4、針對(duì)TPC-H數(shù)據(jù)庫(kù)設(shè)計(jì)單元主唱入、批量數(shù)據(jù)插入、修改數(shù)據(jù)和刪除數(shù)據(jù)的sql語(yǔ)句。理解和掌握insert、update、delete語(yǔ)法結(jié)構(gòu)的各個(gè)組成成分，結(jié)合嵌套sql子查詢，分別設(shè)計(jì)幾個(gè)不同形式的插入、修改和刪除數(shù)據(jù)的語(yǔ)句，并調(diào)試成功。 5、針對(duì)給定的數(shù)據(jù)庫(kù)模式，以及相應(yīng)的應(yīng)用要求，創(chuàng)建視圖和帶WITH CHECK OPTION的視圖，并驗(yàn)證視圖WITH CHECK OPTION選項(xiàng)的有效性。理解和掌握試圖消解執(zhí)行原理，掌握可更新視圖和不可更新視圖的區(qū)別。 6、針對(duì)給定的數(shù)據(jù)庫(kù)模式和具體應(yīng)用需求，創(chuàng)建唯一索引、函數(shù)索引、復(fù)合索引等；修改索引；刪除索引。設(shè)計(jì)相應(yīng)的sql查詢驗(yàn)證索引有效性，學(xué)習(xí)利用EXPLAIN命令分析sql查詢是否使用了所創(chuàng)建的索引，并能夠分析其原因，執(zhí)行sql查詢并估算索引提高查詢效率的百分比，要求實(shí)驗(yàn)數(shù)據(jù)達(dá)到10萬(wàn)條記錄以上的數(shù)據(jù)量，以便驗(yàn)證所以效果. 三、實(shí)驗(yàn)過(guò)程 1、數(shù)據(jù)庫(kù)定義實(shí)驗(yàn) （1） 定義數(shù)據(jù)庫(kù) 采用中文字符集創(chuàng)建名為TCHP的數(shù)據(jù)庫(kù)。 CREATE DATABASE TPCH ENCODING=’GBK’; （2） 定義模式 在數(shù)據(jù)庫(kù)TPCH中創(chuàng)建名為SALES的模式。 Create SCHEMA Sales; （3） 定義基本表 在TPCH數(shù)據(jù)庫(kù)的Sales模式中創(chuàng)建8個(gè)基本表。 /*設(shè)置當(dāng)前會(huì)話的搜索路徑為sales模式、public模式，基本表就會(huì)自動(dòng)創(chuàng)建在sales模式下。*/ SET SEARCH_PATH TO Sales, Public; CREATE TABLE Region( regionkey INTEGER PRIMARY KEY, name CHAR(25), comment VARCHAR(152)); CREATE TABLE Nation( nationkey INTEGER PRIMARY KEY, name CHAR(25), address VARCHAR(40), regionkey INTEGER REFERENCES REGION(REGIONKEY), comment VARCHAR(152)); CREATE TABLE Supplier( suppkey INTEGER PRIMARY KEY, name CHAR(25), address VARCHAR(40), nationkey INTEGER REFERENCES Nation(nationkey), phone CHAR(15), acctbal REAL, comment VARCHAR(101)); CREATE TABLE Part( partkey INTEGER PRIMARY KEY, name VARCHAR(55), mfgr CHAR(25), /*制造廠*/ brand CHAR(10), type VARCHAR (25), size INTEGER, container CHAR(10), retailprice REAL, comment VARCHAR(23)); CREATE TABLE PartSupp( partkey INTEGER REFERENCES Part(partkey), suppkey INTEGER REFERENCES Supplier(suppkey), availqty INTEGER, supplycost REAL , comment varchar(199), PRIMARY KEY (parkey,suppkey)); CREATE TABLE Costomer( custkey INTEGER PRIMARY KEY, name VARCHAR(25), address VARCHAR(40), nationkey INTEGER REFERENCES Nation(nationkey), phone CHAR(15), acctbal REAL, mktsegment CHAR(10), comment VARCHAR(117)); CREATE TABLE Orders( orderkey INTEGER PRIMARY KEY, custkey INTEGER REFERENCES Customer(custkey), orderstatus CHAR(1), totalprice REAL, orderdate DATE, orderpriority INTEGER, comment VARCHAR(79)); CREATE TABLE Lineitem( orderkey INTEGER REFERENCES Order(orderkey), partkey INTEGER REFERENCES Part(partkey), suppkey INTEGER REFERENCES Supplier(suppkey), linenumber INTEGER, quantity REAL, extendedprice REAL, discount REAL, tax REAL, returnflag CHAR(1), linestatus CHAR(1), shipinstruct CHAR(25), shipmode CHAR(10), comment VARCHAR(44), PRIMARY KEY(orderkey,linenumber), FOREIGN KEY(Partkey,suppkey) REFERENCES PartSupp(partkey,suppkey)); 2、數(shù)據(jù)基本查詢 （1）單表查詢（實(shí)現(xiàn)投影操作） 查詢供應(yīng)商的名稱、地址和聯(lián)系電話。 SELECTE name,address,phone FROMSupplier; （2）單表查詢（實(shí)現(xiàn)選擇操作） 查詢最近一周內(nèi)提交的總價(jià)大于1000元的訂單的編號(hào)、顧客編號(hào)等訂單的所有信息。 SELECT *FROM Sales.Orders WHERE CURRENT_DATE-orderdata<7 AND totalprice >1000; （3）不帶分組過(guò)濾條件的分組統(tǒng)計(jì)查詢 統(tǒng)計(jì)每個(gè)顧客的訂購(gòu)金額 SELECT C.custkey ,SUM(O.totalprice) FROM customer C,Orders O WHERE C.custkey=O.custkey GROUP BY C.custkey; （4） 帶分組過(guò)濾條件的分組統(tǒng)計(jì)查詢 查詢訂單平均金額超過(guò)1000元的顧客編號(hào)及其姓名 SELECT C.custkey，MAX（C.name） FROM Customer C,Orders O WHERE C.custkey=O.custkey GROUP BY C.custkey; HAVING AVG（O.totalprice）>1000; （5） 表單自身連接查詢 查詢與“金倉(cāng)集團(tuán)”在同一個(gè)國(guó)家的供應(yīng)商編號(hào)、名稱和地址信息。 SELECT F.suppkey,F.name，F(xiàn).address FROM Supplier F,Supplier S WHERE F.nationkey=S.nationkey AND S.name=金倉(cāng)集團(tuán); （6） 兩表連接查詢（普通連接） 查詢供應(yīng)價(jià)格大于零售價(jià)格的零件名、制造商名、零售價(jià)格和供應(yīng)價(jià)格。 SELECT P.name,P.mfgr,P.retailprice,PS.supplycost FROM Part P,Partsupp PS WHERE P.retailprice>PS.supplycost; （7） 兩表連接查詢（自然連接） 查詢供應(yīng)價(jià)格大于零售價(jià)格的零件名、制造商名、零售價(jià)格和供應(yīng)價(jià)格。 SELECT P.name,P.mfgr,P.retailprice,PS.supplycost FROM Part P,Partsupp PS WHERE P.partkey=PS.partkey AND P.retailprice>PS.supplycost; （8）三表連接查詢 查詢顧客“蘇舉庫(kù)”訂購(gòu)的訂單編號(hào)、總價(jià)及其訂購(gòu)的零件編號(hào)、數(shù)量和明細(xì)價(jià)格。 SELECT O.orderkey,O.totalprice,L.partkey,L.quantity,L.extendedprice FROM Custom C,Orders O,Lineitem L WHERE C.custkey=O.custkey AND O.orderkey=L.orderkey AND C.name=蘇舉庫(kù); 3、數(shù)據(jù)高級(jí)查詢實(shí)驗(yàn) （1）IN嵌套查詢 查詢訂購(gòu)了“海大”制造的“船舶模擬駕駛艙”的顧客。 SELECT custkey，name FROM Customer WHERE custkey IN ( SELECT O.custkey FROM Orders O,Lineitme L，PartSupp PS,Part P WHERE O.orderkey=L.orderkey AND L.partkey=PS.partkey AND L.suppkey=PS.suppkey AND PS.partkey=P.partkey AND P.mfgr=海大 AND P.name=船舶模擬駕駛艙); SELECT custkey,name FROM Customer WHERE cuskey IN ( SELECT O.custkey FROM Orders O,Lineitem L,Part P WHERE O.orderkey=L.orderkey AND L.partkey=P.partkey AND p.mfgr=海大 AND P.name=船舶模擬駕駛艙); （2）單層EXISTS嵌套查詢 查詢沒(méi)有購(gòu)買過(guò)“海大”制造的“船舶模擬駕駛艙”的顧客。 SELECT custkey,name FROM Customer WHERE NOT EXISTS( SELECT O.custkey FROM Orders O,Lineitem L,PartSupp PS,Part P WHERE C.cuskey=O.custkey AND O.orderkey=L.orderkey AND L.partkey=PS.partkey AND L.suppkey=PS.suppkey AND PS.partkey=P.partkey AND p.mfgr=海大 AND P.name=船舶模擬駕駛艙); （3）雙層EXISTS嵌套查詢 查詢至少購(gòu)買過(guò)顧客“張三”購(gòu)買過(guò)的全部零件的顧客姓名。 SELECT CA.name FROM Customer CA WHERE NOT EXISTS （SELECT * FROM Customer CB,Oders OB,Lineitem LB WHERE CB.custkey=OB.custkey AND OB.orderkey=LB.orderkey AND CB.name=張三 AND NOT EXISTS(SELECT * FROM Orders OC,Lineitem LC WHERE CA.custkey=LC.custkey AND OC.orderkey=LC.orderkey AND LB.suppkey=LC.suppkey AND LB.partkey=LC.partkey)); （4）FROM子句中的嵌套查詢 查詢訂單平均金額超過(guò)1萬(wàn)元的顧客中的中國(guó)籍顧客信息。 SELECT C.* FROM Customer C,(SELECT custkey FROM Orders GROUP BY custkey HAVING AVG(totalprice)>10000) B,Nation N WHERE C.custkey=B.custkey AND C.nationkey=N.nationkey AND N.name=中國(guó); （5）集合查詢（交） 查詢顧客“張三”和“李四”都訂購(gòu)過(guò)的全部零件的信息。 SELECT P.* FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P WHERE C.custkey=O.custkey AND O.orderkey=L.orderkey AND L.suppkey=PS.suppkey AND L.partkey=PS.partkey AND PS.partkey=P.partkey AND C.name=李四; INTERSECTION SELECT P.* FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P WHERE C.cuskey=O.custkey AND O.orderkey=L.orderkey AND L.partkey=PS.partkey AND L.suppkey=PS.suppkey AND PS.partkey=P.partkey AND C.name=李四; （6）集合查詢（并） 查詢顧客“張三”和“李四”訂購(gòu)的全部零件的信息。 SELECT P.* FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P WHERE C.cuskey=O.custkey AND O.orderkey=L.orderkey AND L.partkey=PS.partkey AND L.suppkey=PS.suppkey AND PS.partkey=P.partkey AND C.name=張三; UNION SELECT P.* FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P WHERE C.cuskey=O.custkey AND O.orderkey=L.orderkey AND L.partkey=PS.partkey AND L.suppkey=PS.suppkey AND PS.partkey=P.partkey AND C.name=李四; （7）集合查詢（差） 顧客“張三”訂購(gòu)過(guò)而“李四”沒(méi)訂購(gòu)過(guò)的零件的信息。 SELECT P.* FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P WHERE C.cuskey=O.custkey AND O.orderkey=L.orderkey AND L.partkey=PS.partkey AND L.suppkey=PS.suppkey AND PS.partkey=P.partkey AND C.name=張三; EXCEPT SELECT P.* FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P WHERE C.cuskey=O.custkey AND O.orderkey=L.orderkey AND L.partkey=PS.partkey AND L.suppkey=PS.suppkey AND PS.partkey=P.partkey AND C.name=李四; 4、數(shù)據(jù)更新實(shí)驗(yàn) （1）INSERT基本語(yǔ)句(插入全部列的數(shù)據(jù)) 插入一條顧客記錄，要求每列都給一個(gè)合理的值。 INSERT INTO Customer VALUES (30,張三,北京市,40,010-51001199,0.00,Northeast,VIP Customer); （2）INSERT基本語(yǔ)句（插入部分列的數(shù)據(jù)） 插入一條訂單記錄，給出必要的幾個(gè)字段值。 INSERT INTO Lineitem(orderkey,Linenumber,partkey,suppkey,quantity,shipdate) VALUES(862,ROUND(RANDOM()*100,0,479,1,10,2012-3-6); /*RANDOM()函數(shù)為隨機(jī)小數(shù)生成函數(shù)，ROUND()為四舍五入函數(shù)*/ （3）批量數(shù)據(jù)INSERT語(yǔ)句 ①　 創(chuàng)建一個(gè)新的顧客表，把所有中國(guó)籍顧客插入到新的顧客表中。 CREATE TABLE NewCustmoer AS SELECT * FROM Customer WITH NO DATA; /*WITH NO DATA子句使得SELECT查詢只生成一個(gè)結(jié)果模式，不查詢出實(shí)際數(shù)據(jù)*/ INSERT INTO NewCustomer /*批量插入SELECT 語(yǔ)句查詢結(jié)果到NewCustomer表中*/ SELECT C.* FROM Costomer C,Nation N WHERE C.nationkey=N.nationkey AND N.name=中國(guó); ②　 創(chuàng)建一個(gè)顧客購(gòu)物統(tǒng)計(jì)表，記錄每個(gè)顧客及其購(gòu)物總數(shù)和總價(jià)等信息。 CREATE TABLE ShoppingStat (custkey INTEGER, quantity REAL, totalprice REAL); INSERT INTO ShoppingStat SELECT C.custkey,Sum(L.quantity),Sum(O.totalprice) /*對(duì)分組后的數(shù)據(jù)求總和*/ FROM Customer C,Order O,Lineitem L WHERE C.custkey=O.custkey AND O.orderkey=L.orderkey GROUP BY C.custkey ③　倍增零件表的數(shù)據(jù)，多次重復(fù)執(zhí)行，直到總記錄數(shù)達(dá)到50萬(wàn)為止。 INSERT INTO Part SELECT partkey+(SELECT COUNT(*) FROM Part), name,mfgr,brand,type,size,container,retailprice,comment FROM Part; （4）UPDATE語(yǔ)句（插入部分記錄的部分列值） “金倉(cāng)集團(tuán)”供應(yīng)的所有零件的供應(yīng)成本價(jià)下降10%。 UPDATE PartSupp SET supplycost=supplycost*0.9 WHERE suppkey=(SELECT suppkey /*找出要修改的那些記錄*/ FROM Supplier WHERE name=金倉(cāng)集團(tuán)); （5）UPDATE語(yǔ)句（利用一個(gè)表中的數(shù)據(jù)修改另外一個(gè)表中的數(shù)據(jù)） 利用Part表中的零售價(jià)格來(lái)修改Lineitem中的extendedprice,其中extendedprice=Part.retailprice*quantity。 UPDATE Lineitem L SET L.extendedprice=P.retailprice*L.quantity FROM Part P WHERE L.partkey=P.partkey; /*Lineitem表也可以直接與Part表相連接，而不需通過(guò)PartSupp連接*/ （6）DELETE基本語(yǔ)句（刪除給定條件的所有記錄） 刪除顧客張三的所有訂單記錄。 DELECT FROM Lineitem /*先刪除張三的訂單明細(xì)記錄*/ WHERE orderkey IN(SELECT orderkey FROM Order O,Customer C WHERE O.custkey=C.custkey AND C.name=張三); DELECT FROM Order /*再刪除張三的訂單記錄*/ WHERE custkey=(SELECT custkey FROM Customer WHERE name=張三); 5、 視圖 （1） 創(chuàng)建視圖（省略視圖列名） 創(chuàng)建一個(gè)“海大汽配”供應(yīng)商供應(yīng)的零件視圖V_DLMU_PartSupp1，要求列出供應(yīng)零件的編號(hào)、零件名稱、可用數(shù)量、零售價(jià)格、供應(yīng)價(jià)格和備注等信息。 CREATE VIEW V_DLMU_PARTSUPP1 AS /*由SELECT子句目標(biāo)列組成視圖屬性*/ SELECT P.partkey,P.name,PS.availqty,P.retailprice,PS.supplycost,P.comment FROM Part P,PartSupp PS,Supplier S WHERE P.partkey=PS.partkey AND S.suppkey=PS.suppkey AND S.name=海大汽配; （2） 創(chuàng)建視圖（不能省略列名的情況） 創(chuàng)建一個(gè)視圖V_CustAvgOrder,按顧客統(tǒng)計(jì)平均每個(gè)訂單的購(gòu)買金額和零件數(shù)量，要求輸出 顧客編號(hào)、姓名，平均購(gòu)買金額和平均購(gòu)買零件數(shù)量。 CREATE VIEW V_CustAvgOrder(custkey,cname,avgprice,avgquantity) AS SELECT C.custkey,MAX(C.name),AVG(O.totalprice),AVG(L.quantity) FROM Customer C,Orders O,Lineitem L WHERE C.custkey=O.custkey AND L.orderkey=O.orderkey GROUP BY C.custkey; （3） 創(chuàng)建視圖（WITH CHECK OPTION） 使用WITH CHECK OPTION，創(chuàng)建一個(gè)“海大汽配”供應(yīng)商供應(yīng)的零件視圖V_DLMU_PartSupp2，要求列出供應(yīng)零件的編號(hào)、可用數(shù)量和供應(yīng)價(jià)格等信息。然后通過(guò)該視圖分別增加、刪除和修改一條“海大汽配”零件供應(yīng)記錄，驗(yàn)證WITH CHECK OPTION是否起作用。 CREATE VIEW V_DLMU_PartSupp2 AS SELECT partkey,suppkey,availqty,supplycost FROM PartSupp WHERE suppkey=(SELECT suppkey FROM Supplier WHERE name=海大汽配) WITH CHECK OPTION; INSERT INTO V_DLMU_PartSupp2 VALUES (58889,5048,704,77760); UPADTE V_DLMU_PartSupp2 SET supplycost=12 WHERE suppkey=58889; DELETE FROM V_DLMU_PartSupp2 WHERE suppkey=58889; （4） 可更新的視圖（行列子集視圖） 使用WITH CHECK OPTION，創(chuàng)建一個(gè)“海大汽配”供應(yīng)商供應(yīng)的零件視圖V_DLMU_PartSupp4，要求列出供應(yīng)零件的編號(hào)、可用數(shù)量和供應(yīng)價(jià)格等信息。然后通過(guò)該視圖分別增加、刪除和修改一條“海大汽配”零件供應(yīng)記錄，驗(yàn)證該視圖是否是可更新的，并比較上述“（3）創(chuàng)建視圖”實(shí)驗(yàn)任務(wù)與本任務(wù)結(jié)果有何異同。 CREATE VIEW V_DLMU_PartSupp3 AS SELECT partkey,suppkey,availqty,supplycost FROM PartSupp WHERE suppkey=(SELECT suppkey FROM Supplier WHERE name=海大汽配); INSERT INTO V_DLUM_PartSupp3 VALUES(58889,5048,704,77760); UPDATE V_DLMU_PartSupp3 SET supplycost=12 WHERE suppkey=58889; DELETE FROM V_DLMU_PartSupp3 WHERE suppkey=58889; （5）可更新的視圖 INSERT INTO V_CustAvgOrder VALUES(100000,NULL,20,2000); （6） 刪除視圖（RESTRICT/CASCADE） 創(chuàng)建顧客訂購(gòu)零件明細(xì)視圖V_CustOrd,要求列出顧客編號(hào)、姓名、購(gòu)買零件數(shù)、金額，然后在該視圖的基礎(chǔ)上，在創(chuàng)建（2）的視圖V_CustAvgOrder,然后使用RESTRICT選項(xiàng)和CASCADE選項(xiàng)刪除視圖V_CustOrd。 CREATE VIEW V_CustOrd(custkey,cname,qty,extprice) AS SELECT C.custkey,C.name,L.quantity,L.extendedprice FROM Customer C,Order O,Lineitem L WHERE C.custkey=O.custkey AND O.orderkey=L.orderkey; CREATE VIEW V_CustAvgOrder(custkey,cname,avgqty,avgprice) AS SELECT custkey,MAX(cname),AVG(qty),AVG(extprice) FROM V_CustOrd /*在視圖V_CustOrd上再創(chuàng)建視圖*/ GROUP BY custkey; DROP VIEW V_CustOrd RESTRICT; DROP VIEW V_CustOrd CASCADE; 6、 索引 （1） 創(chuàng)建唯一索引 在零件表的零件名稱字段上創(chuàng)建唯一索引。 CREATE UNIQUE INDEX Idx_part_name ON Part(name); （2） 創(chuàng)建函數(shù)索引（對(duì)某個(gè)屬性的函數(shù)創(chuàng)建索引，稱為函數(shù)索引） 在零件表的零件名稱字段上創(chuàng)建一個(gè)零件名稱長(zhǎng)度的函數(shù)索引。 CREATE INDEX Idx_part_name_fun ON Part(LENGTH(name)); （3）創(chuàng)建復(fù)合索引（對(duì)兩個(gè)及兩個(gè)以上的屬性創(chuàng)建索引，稱為復(fù)合索引） 在零件表的制造商和品牌兩個(gè)字段上創(chuàng)建一個(gè)復(fù)合索引。 CREATE UNIQUE INDEX Idx_part_mfgr_brand ON Part(mfgr,brand); （4） *創(chuàng)建聚簇索引 在零件表的制造商字段上創(chuàng)建一個(gè)聚簇索引。 CREATE UNIQUE INDEX Idx_part_mfgr ON Part(mfgr); CLUSTER Idx_part_mfgr ON Part; （5） 創(chuàng)建Hash索引 零件表的名稱字段上創(chuàng)建一個(gè)Hash索引。 CREATE INDEX Idx_part_name_hash ON Part USING HASH(name); （6） 修改索引名稱 修改零件表的名稱字段上的索引名。 ALTER INDEX Idx_part_name_hash RENAME TO Idx_part_name_hash_new; （7）分析某個(gè)SQL查詢語(yǔ)句執(zhí)行時(shí)是否使用了索引 EXPLAIN SELECT * FROM part WHERE name=零件; （8） *驗(yàn)證索引效率 創(chuàng)建一個(gè)函數(shù)TestIndex,自動(dòng)計(jì)算sql查詢執(zhí)行的時(shí)間。 CREATE FUNCTION TestIndex(p_part_name CHAR(55)) RETURN INTEGER AS /*自定義函數(shù)TestIndex()：輸入?yún)?shù)為零件名稱，返回SQL查詢的執(zhí)行時(shí)間*/ DECLARE begintime TIMESTAMP; endtime TIMESTAMP; durationtime INTEGER; BEGN SELECT CLOCK_TIMESTAMP() INTO begintime; /*記錄查詢執(zhí)行的開始時(shí)間*/ PERFORM *FROM Part WHERE name=p_partname; /*執(zhí)行SQL查詢，不保存查詢結(jié)果*/ SELECT CLOCK_TIMESTAMP() INTO endtime; SELECT DATEDIFF(‘ms’,begintime,endtime) INTO durationtime; RETURN durationtime; /*計(jì)算并返回查詢執(zhí)行時(shí)間，時(shí)間單位為毫秒ms*/ END; /*查看當(dāng)零件表Part數(shù)據(jù)模型比較小，并且無(wú)索引時(shí)的執(zhí)行時(shí)間*/ SELECT TestIndex(‘零件名稱’); INSERT INTO Part /*不斷倍增零件表的數(shù)據(jù)，直到50萬(wàn)條記錄*/ SELECT partkey+(SELECT COUNT(*) FROM Part), Name,mfgr,brand,type,size,container,retailprice,comment FRPM Part; /*查看當(dāng)零件表Part數(shù)據(jù)模型比較大，但無(wú)索引時(shí)的執(zhí)行時(shí)間*/ SELECT TestIndex(‘零件名稱’); CREATE INDEX part_name ON Part(name); /*在零件表的零件名稱字段上創(chuàng)建索引*/ /*查看零件表Part數(shù)據(jù)規(guī)模比較大，有索引時(shí)的執(zhí)行時(shí)間*/ SELECT TestIndex(); 四、實(shí)驗(yàn)心得 通過(guò)本次實(shí)驗(yàn)，我知道只有正確理解數(shù)據(jù)庫(kù)模式結(jié)構(gòu)，才能正確設(shè)計(jì)數(shù)據(jù)庫(kù)查詢。連接查詢是數(shù)據(jù)庫(kù)sql查詢中最重要的查詢，連接查詢的設(shè)計(jì)要特別注意，不同的查詢表達(dá)，其查詢執(zhí)行的性能會(huì)有很大差別。正確地設(shè)計(jì)和執(zhí)行數(shù)據(jù)更新語(yǔ)句，確保正確地錄入數(shù)據(jù)和更新數(shù)據(jù)，才能保證查詢的數(shù)據(jù)正確。當(dāng)數(shù)據(jù)更新失敗時(shí)，一個(gè)主要原因是更新數(shù)據(jù)時(shí)違反了完整性約束。 實(shí)驗(yàn)項(xiàng)目名稱：安全性語(yǔ)言實(shí)驗(yàn) 實(shí)驗(yàn)學(xué)時(shí)： 2 同組學(xué)生姓名： 孟陳、陳曉雪、季佰軍 實(shí)驗(yàn)地點(diǎn)： 1318 實(shí)驗(yàn)日期： 5.26 實(shí)驗(yàn)成績(jī)： 批改教師： 批改時(shí)間： 一、 實(shí)驗(yàn)?zāi)康? 1、 掌握自主存取控制缺陷的定義和維護(hù)方法。 2、 掌握數(shù)據(jù)庫(kù)審計(jì)的設(shè)置和管理方法，以便監(jiān)控?cái)?shù)據(jù)庫(kù)操作，維護(hù)數(shù)據(jù)庫(kù)安全。 二、 實(shí)驗(yàn)內(nèi)容和要求 1、 定義用戶、角色，分配權(quán)限給用戶、角色，回收權(quán)限，以相應(yīng)的用戶名登錄數(shù)據(jù)庫(kù)驗(yàn)證權(quán)限分配是否正確。選擇一個(gè)應(yīng)用場(chǎng)景，使用自主存取控制機(jī)制設(shè)置權(quán)限分配?？梢圆捎脙煞N方案。 方案一：采用SYSTEM超級(jí)用戶登錄數(shù)據(jù)庫(kù)，完成所有權(quán)限分配工作，然后用相應(yīng)用戶名登錄數(shù)據(jù)庫(kù)已驗(yàn)證權(quán)限分配正確性； 方案二：采用SYSTEM用戶登錄數(shù)據(jù)庫(kù)創(chuàng)建3個(gè)部門經(jīng)理用戶，并分配相應(yīng)的權(quán)限，然后分別用3個(gè)經(jīng)理用戶名登錄數(shù)據(jù)庫(kù)，創(chuàng)建相應(yīng)部門的USER、ROLE，并分配相應(yīng)權(quán)限。 2、打開數(shù)據(jù)庫(kù)審計(jì)開關(guān)。以具有審計(jì)權(quán)限的用戶登錄數(shù)據(jù)庫(kù)，設(shè)置審計(jì)權(quán)限，然后以普通用戶登錄數(shù)據(jù)庫(kù)，執(zhí)行相應(yīng)的數(shù)據(jù)操縱sql語(yǔ)句，驗(yàn)證相應(yīng)審計(jì)設(shè)置是否生效，最后在一具有審計(jì)權(quán)限的用戶登錄數(shù)據(jù)庫(kù)，查看是否存在相應(yīng)的審計(jì)信息。 三、實(shí)驗(yàn)過(guò)程 1、自主存取控制實(shí)驗(yàn) （1）創(chuàng)建用戶 為采購(gòu)、銷售和客戶管理等3個(gè)部門的經(jīng)理創(chuàng)建用戶標(biāo)識(shí)，要求具有創(chuàng)建用戶或角色的權(quán)利。 CREATE USER David WITH CREATEROLE PASSWORD 123456; CREATE USER Tom WITH CREATEROLE PASSWORD 123456; CREATE USER Kathy WITH CREATEROLE PASSWORD 123456; 為采購(gòu)、銷售和客戶管理等3個(gè)部門的職員創(chuàng)建用戶標(biāo)識(shí)和用戶口令。 CREATE USER Jeffery WITH PASSWORD 123456; CREATE USER Jane WITH PASSWORD 123456; CREATE USER Mike WITH PASSWORD 123456; （2）創(chuàng)建角色并分配權(quán)限 為各個(gè)部門分別創(chuàng)建一個(gè)查詢角色，并分配相應(yīng)的查詢權(quán)限。 CREATE ROLE PurchaseQueryRole; GRANT SELECT ON TABLE Part TO PurchaseQueryRole; GRANT SELECT ON TABLE Supplier TO PurchaseQueryRole; GRANT SELECT ON TABLE PartSupp TO PurchaseQueryRole; CREATE ROLE SaleQueryRole; GRANT SELECT ON TABLE Order TO SaleQueryRole; GRANT SELECT ON TABLE Lineitem TO SaleQueryRole; CREATE ROLE CustomerQueryRole; GRANT SELECT ON TABLE Customer TO CustomerQueryRole; GRANT SELECT ON TABLE Nation TO CustomerQueryRole; GRANT SELECT ON TABLE Region TO CustomerQueryRole; 為各個(gè)部門分別創(chuàng)建一個(gè)職員角色，對(duì)本部門信息具有查看、插入權(quán)限。 CREATE ROLE PurchaseEmployeeRole; GRANT SELECT，INSERT ON TABLE Part TO PurchaseEmployeeRole; GRANT SELECT，INSERT ON TABLE Supplier TO PurchaseEmployeeRole; GRANT SELECT，INSERT ON TABLE PartSupp TO PurchaseEmployeeRole; CREATE ROLE SaleEmployeeRole; GRANT SELECT，INSERT ON TABLE Order TO SaleEmployeeRole; GRANT SELECT，INSERT ON TABLE Lineitem TO SaleEmployeeRole; CREATE ROLE CustomerEmployeeRole; GRANT SELECT，INSERT ON TABLE Customer TO CustomerEmployeeRole; GRANT SELECT，INSERT ON TABLE Nation TO CustomerEmployeeRole; GRANT SELECT，INSERT ON TABLE Region TO CustomerEmployeeRole; 為各個(gè)部門創(chuàng)建一個(gè)經(jīng)理角色，相應(yīng)角色對(duì)本部門的信息具有完全控制權(quán)限，對(duì)其他部門的信息具有查詢權(quán)。經(jīng)理有權(quán)給本部門資源分配權(quán)限。 CREATE ROLE PurchaseManagerRole WITH CREATEROLE； GRANT ALL ON TABLE Part TO PurchaseManagerRole； GRANT ALL ON TABLE Supplier TO PurchaseManagerRole； GRANT ALL ON TABLE PartSupp TO PurchaseManagerRole； GRANT SaleQueryRole TO PurchaseManagerRole； GRANT CustomerQueryRole TO PurchaseManagerRole； CREATE ROLE SaleManagerRole WITH CREATEROLE； GRANT ALL ON TABLE Order TO SaleManagerRole GRANT ALL ON TABLE Lineitem TO SaleManagerRole GRANT SaleQueryRole TO SaleManagerRole GRANT PurchaseQueryRole TO SaleManagerRole CREATE ROLE CustomerManagerRole WITH CREATEROLE； GRANT ALL ON TABLE Customer TO CustomerManagerRole GRANT ALL ON TABLE Nation TO CustomerManagerRole GRANT ALL ON TABLE Region TO CustomerManagerRole GRANT SaleQueryRole TO CustomerManagerRole GRANT PurchaseQueryRole TO CustomerManagerRole （3）給用戶分配權(quán)限 給部門經(jīng)理分配權(quán)限。 GRANT PurchaseManagerRole TO David WITH ADMIN OPTION; GRANT SaleManagerRole TO Tom WITH ADMIN OPTION; GRANT CustomerManagerRole TO Kathy WITH ADMIN OPTION; 給各部門職員分配權(quán)限 GRANT PurchaseEmployeeRole TO Jeffery; GRANT SaleEmployeeRole TO Jane; GRANT CustomerEmployeeRole TO Mike; （4）回收角色或用戶權(quán)限 收回客戶經(jīng)理角色的銷售信息查看權(quán)限。 REVOKE SaleQueryRole FROM CustomerManagerRole; 回收MIKE的客戶部門職員權(quán)限。 REVOKE CustomerEmployeeRole FROM Mike; （5）驗(yàn)證權(quán)限分配正確性 以David用戶名登錄數(shù)據(jù)庫(kù)，驗(yàn)證采購(gòu)部門經(jīng)理的權(quán)限 SELECT * FROM Part; DELETE * FROM Order; 回收MIKE的客戶部門職員權(quán)限 SELECT * FROM Customer; SELECT * FROM Part; 2、審計(jì)實(shí)驗(yàn) （1）審計(jì)開關(guān) 顯示當(dāng)前審計(jì)開關(guān)狀態(tài) SHOW AUDIT_TRAIL; 打開審計(jì)開關(guān) SET AUDIT_TRAIL TO ON; （2）數(shù)據(jù)庫(kù)操作審計(jì) 對(duì)客戶信息表上的刪除操作設(shè)置審計(jì)。 AUDIT DELETE ON Sales.Customer BY ACCESS; 以普通用戶登錄，執(zhí)行sql語(yǔ)句。 DELETE Sales.Customer WHERE custkey=1011; 查看數(shù)據(jù)庫(kù)對(duì)象審計(jì)信息，驗(yàn)證審計(jì)設(shè)置是否生效。 SELECT * FROM SYS_AUDIT_OBJECT; （3）語(yǔ)句級(jí)審計(jì) 對(duì)表定義的更改語(yǔ)句ALTER設(shè)置審計(jì) AUDIT ALTER TABLE BY ACCESS; 查看所有數(shù)據(jù)庫(kù)所有語(yǔ)句級(jí)審計(jì)設(shè)置，驗(yàn)證審計(jì)設(shè)置是否生效 SELECT * FROM SYS_STMT_AUDIT_OPTS; 以普通用戶登錄，執(zhí)行sql語(yǔ)句，驗(yàn)證審計(jì)設(shè)置是否生效 ALTER TABLE Customer ADD COLUMN tt INT; 查看所有審計(jì)信息 SELECT * FROM SYS_AUDIT_TRAIL; 四、實(shí)驗(yàn)心得 通過(guò)本次實(shí)驗(yàn)，知道了定義用戶、角色，分配權(quán)限給用戶、角色，回收權(quán)限，并以相應(yīng)的用戶名登陸數(shù)據(jù)庫(kù)驗(yàn)證權(quán)限分配是否正確的方法。并且知道了數(shù)據(jù)庫(kù)審計(jì)的目的和方法。做實(shí)驗(yàn)的同時(shí)，對(duì)sql語(yǔ)句有了更熟練的運(yùn)用。 實(shí)驗(yàn)項(xiàng)目名稱：完整性語(yǔ)言實(shí)驗(yàn) 實(shí)驗(yàn)學(xué)時(shí)： 2 同組學(xué)生姓名： 孟陳、陳曉雪、季佰軍 實(shí)驗(yàn)地點(diǎn)： 1318 實(shí)驗(yàn)日期： 6.2 實(shí)驗(yàn)成績(jī)： 批改教師： 批改時(shí)間： 一、 實(shí)驗(yàn)?zāi)康? 1、 掌握實(shí)體完整性的定義和維護(hù)方法； 2、 掌握參照完整性的定義和維護(hù)方法； 3、 掌握用戶自定義完整性的定義和維護(hù)方法； 二、 實(shí)驗(yàn)內(nèi)容和要求 1、定義實(shí)體完整性，刪除實(shí)體完整性。能夠?qū)懗鰞煞N方式定義實(shí)體完整性的SQL語(yǔ)句：創(chuàng)建表時(shí)定義實(shí)體完整性、創(chuàng)建表后定義實(shí)體完整性。設(shè)計(jì)SQL語(yǔ)句驗(yàn)證完整性約束是否起作用。 2、定義參照完整性，定義參照完整性的違規(guī)處理，刪除參照完整性。寫出兩種方式定義參照完整性的SQL語(yǔ)句：創(chuàng)建表時(shí)定義參照完整性、創(chuàng)建表后定義參照完整性。 3、針對(duì)具體應(yīng)用語(yǔ)義，選擇NULL/NOT NULL、DEFAULT、UNIQUE、CHECK等，定義屬性上的約束條件。 三、實(shí)驗(yàn)過(guò)程 1、實(shí)體完整性實(shí)驗(yàn) （1）創(chuàng)建表時(shí)定義實(shí)體完整性(列級(jí)實(shí)體完整性) 定義供應(yīng)商表的實(shí)體完整性。 CREATE TABLE Supplier( suppkey INSERT CONSTRAINT PK_supplier PRIMARY KEY, name CHAR(25), address VARCHAR(40), nationkey INSERT, phone CHAR(15), acctbal REAL, comment VARCHAR(101)); （2）創(chuàng)建表時(shí)定義實(shí)體完整性（表級(jí)實(shí)體完整性） 定義供應(yīng)商表的實(shí)體完整性。 CREATE TABLE Supplier( suppkey INSERT, name CHAR(25), address VARCHAR(40), nationkey I