1、金陂科扶肉 生實驗報 （理工類） 課程名稱: 學生學號: 所屬院部: 數(shù)據(jù)庫系統(tǒng)原理 專業(yè)班級: 1412101055 學生姓名: 軟件工程學院 指導教師: 20J5 20學年 14軟件工程1班 孟祥輝 麻春艷 第二學期 金陵科技學院教務處制 精選文檔 告書寫要求 實驗報告原則上要求學生手寫，要求書寫工整。若因課程特點需 打印的，要遵照以下字體、字號、間距等的具體要求。紙張一律采用 A4的紙張。 刁寫說明 實驗報告百 實驗報告中一至四項內(nèi)容為必填項， 包括實驗目的和要求；實驗 儀器和設備；實驗內(nèi)容與過程；實驗結(jié)果與分析。各院部可根據(jù)學科 特點和實驗具體要求增加項目。 填寫注意事項 （1） （2

2、） （3） （4） （5） 細致觀察，及時、準確、如實記錄。 準確說明，層次清晰。 盡量采用專用術(shù)語來說明事物。 外文、符號、公式要準確，應使用統(tǒng)一規(guī)定的名詞和符號。 應獨立完成實驗報告的書寫，嚴禁抄襲、復印，一經(jīng)發(fā)現(xiàn), 以零分論處。 實驗報告批改說明 實驗報告的批改要及時、認真、仔細，一律用紅色筆批改。實驗 報告的批改成績采用百分制，具體評分標準由各院部自行制定。 實驗報告裝訂要求 實驗批改完畢后，任課老師將每門課程的每個實驗項目的實驗報 告以自然班為單位、按學號升序排列，裝訂成冊，并附上一份該門課 程的實驗大綱。 45 實驗項目名稱：數(shù)據(jù)庫定義與操作語言實驗學時：2 同組學生姓名：孟陳、陳

3、曉雪、季佰軍 實驗地點: 1318 實驗日期: 5.19 實驗成績: 批改時間: 批改教師: 、實驗目的 1、理解和掌握數(shù)據(jù)庫DDL語言，能夠熟練地使用SQL DDL語句創(chuàng)建、修改和 刪除數(shù)據(jù)庫、模式和基本表。 2、掌握SQL冊亨徐設計基本規(guī)范，熟練運用 SQL語言實現(xiàn)數(shù)據(jù)基本查詢，包 括單表查詢、分組統(tǒng)計查詢和連接查詢 3、掌握SQL嵌套查詢和集合查詢等，各種高級查詢的設計方法等. 4、 熟悉數(shù)據(jù)庫的數(shù)據(jù)更新操作，能夠使用sql語句對數(shù)據(jù)庫進行數(shù)據(jù)的插入、 修改、刪除操作。 5、熟悉sql語言有關(guān)系圖的操作，能夠熟練使用 sql語言來創(chuàng)建需要的視圖，定 義數(shù)據(jù)庫外模式，并能使用所創(chuàng)建的視圖實

4、現(xiàn)數(shù)據(jù)管理。 6、掌握所以設計原則和技巧，能夠創(chuàng)建合適的索引以提高數(shù)據(jù)庫查詢、統(tǒng)計分 析效率。 二、實驗內(nèi)容和要求 1、理解和掌握SQL DDL語句的語法，特別是各種參數(shù)的具體含義和使用方法； 使用sql語句創(chuàng)建、修改和刪除數(shù)據(jù)庫、模式和基本表。掌握sql語句常見語法 錯誤的調(diào)試方法。 2、 針對TPC-H數(shù)據(jù)庫設計各種單表查詢sql語句、分組統(tǒng)計查詢語句；設計單 個表針對自身的連接查詢，涉及多個表的連接查詢。理解和掌握sql查詢語句各 個子句的特點和作用，按照sql程序設計規(guī)范寫出具體的sql查詢語句，并調(diào)試 通過。 3、針對TPC-H數(shù)據(jù)庫，證券分析用戶查詢要求，設計各種嵌套查詢和集合查詢

5、。 4、針對TPC-H數(shù)據(jù)庫設計單元主唱入、批量數(shù)據(jù)插入、修改數(shù)據(jù)和刪除數(shù)據(jù)的 sql語句。理解和掌握in sert、up date delete語法結(jié)構(gòu)的各個組成成分，結(jié)合嵌 套sql子查詢，分別設計幾個不同形式的插入、修改和刪除數(shù)據(jù)的語句，并調(diào)試 成功。 5、 針對給定的數(shù)據(jù)庫模式，以及相應的應用要求，創(chuàng)建視圖和帶 WITH CHECK OPTION的視圖，并驗證視圖 WITH CHECK OPTION選項的有效性。理解和掌 握試圖消解執(zhí)行原理，掌握可更新視圖和不可更新視圖的區(qū)別。 6、針對給定的數(shù)據(jù)庫模式和具體應用需求，創(chuàng)建唯一索引、函數(shù)索引、復合索 引等；修改索引；刪除索引。設計相應的

6、sql查詢驗證索引有效性，學習利用 EXPLAIN命令分析sql查詢是否使用了所創(chuàng)建的索引，并能夠分析其原因，執(zhí) 行sql查詢并估算索引提高查詢效率的百分比，要求實驗數(shù)據(jù)達到10萬條記錄 以上的數(shù)據(jù)量，以便驗證所以效果. 三、實驗過程 1、數(shù)據(jù)庫定義實驗 (1) 定義數(shù)據(jù)庫 采用中文字符集創(chuàng)建名為TCHP的數(shù)據(jù)庫。 CREATE DATABASE TPCH ENCODING= GBK (2) 定義模式 在數(shù)據(jù)庫TPCH中創(chuàng)建名為SALES的模式。 Create SCHEMA Sales; (3) 定義基本表 在TPCH數(shù)據(jù)庫的Sales模式中創(chuàng)建8個基本表。 /*設置當前會話的搜索路徑為sal

7、es模式、Public模式，基本表就會自動創(chuàng)建 在sales模式下。*/ SET SEARCH _P ATH TO SalesPublic; CREATE TABLE Regio n( regionkey INTEGER PRIMARY KEY, n ame CHAR(25), comme nt ARCHAR(152); CREATE TABLE Nation( nationkey INTEGER PRIMARY KEY , n ame CHAR(25), address VkRCHAR(40), regio nkey INTEGER REFERENCES REGION(REGIONKEY),

8、 comme nt ARCHAR(152); CREATE TABLE Supplier( suppkey INTEGER PRIMARY KEY , n ame CHAR(25), address VkRCHAR(40), n atio nkey INTEGER REFERENCES Natio n(n atio nkey), phone CHAR(15), acctbal REAL, comme nt ARCHAR(101); CREATE TABLE P art( partkey INTEGER PRIMARY KEY , n ame ARCHAR(55), /*制造廠*/ mfgr C

9、HAR(25), brand CHAR(10), type VARCHAR (25), size INTEGER, co ntai ner CHAR(10), retail price REAL, comme nt ARCHAR(23); CREATE TABLE P artS upp( p artkey INTEGER REFERENCES Part(p artkey), suppkey INTEGER REFERENCES Supplier(suppkey), availqty INTEGER, suppl ycost REAL , comment varchar(199), P RIMA

10、RY KEY (p arkey,su pp key); CREATE TABLE Costomer( custkey INTEGER PRIMARY KEY , n ame ARCHAR(25), address VkRCHAR(40), n atio nkey INTEGER REFERENCES Natio n(n atio nkey), phone CHAR(15), acctbal REAL, mktsegme nt CHAR(10), comme nt ARCHAR(117); CREATE TABLE Orders( orderkey INTEGER PRIMARY KEY , c

11、ustkey INTEGER REFERENCES Customer(custkey), orderstatus CHAR(1), total price REAL, orderdate DATE, orderpriority INTEGER, comme nt ARCHAR(79); CREATE TABLE Lin eitem( orderkey INTEGER REFERENCES Order(orderkey), p artkey INTEGER REFERENCES Part(p artkey), supp key INTEGER REFERENCES Supp lier(s upp

12、 key), linen umber INTEGER, qua ntity REAL, exte nded price REAL, disco unt REAL, tax REAL, returnflag CHAR(1), lin estatus CHAR(1), shi pin struct CHAR(25), shi pm ode CHAR(10), comme nt ARCHAR(44), P RIMARY KEY(orderkey,li nen umber), FOREIGN KEY( Partkey,su pp key) REFERENCES P artSu pp(p artkey,

13、su pp key); 2、數(shù)據(jù)基本查詢 (1) 單表查詢(實現(xiàn)投影操作) 查詢供應商的名稱、地址和聯(lián)系電話。 SELECTE n ame,address ,phone FROMS upp lier; (2) 單表查詢(實現(xiàn)選擇操作) 查詢最近一周內(nèi)提交的總價大于 1000元的訂單的編號、顧客編號等訂單的所有 信息。 SELECT *FROM Sales.Orders WHERE CURRENT_DATE-orderdata1000; (3) 不帶分組過濾條件的分組統(tǒng)計查詢 統(tǒng)計每個顧客的訂購金額 SELECT C.custkey ,SUM(O.tota Ip rice) FROM custo

14、mer C,Orders O WHERE C.custkey=O.custkey GROU P BY C.custkey; （4）帶分組過濾條件的分組統(tǒng)計查詢 查詢訂單平均金額超過1000元的顧客編號及其姓名 SELECT C.custkey, MAX （ C.nam FROM Customer C,Orders O WHERE C.custkey=O.custkey GROU P BY C.custkey; HAVING AVG（ O.tota Ip rice）1000; （5）表單自身連接查詢 查詢與“金倉集團”在同一個國家的供應商編號、名稱和地址信息。 SELECT F.suppkey,

15、F.name F.address FROM Supp lier F,Su ppiier S WHERE F.n ati on key=S. nati on key AND S.n （7）兩表連接查詢（自然連接） 查詢供應價格大于零售價格的零件名、制造商名、零售價格和供應價格。 SELECT P.n ame, P. mfgr, P.retail price, PS.s upp lycost FROM Part P,P artsu pp PS WHERE P.p artkey=PS.p artkey AND P.retail priceP S.s upp lycost; （8）三表連接查詢 查詢顧

16、客“蘇舉庫”訂購的訂單編號、總價及其訂購的零件編號、數(shù)量和明細價 格。 SELECT O.orderkey,O.total price,L. partkey,L.qua ntity,L.exte nded price FROM Custom C,Orders O,L in eitem L WHERE C.custkey=O.custkey AND O.orderkey=L.orderkey AND C.n ame=蘇舉庫; 3、數(shù)據(jù)高級查詢實驗 （1）IN嵌套查詢 查詢訂購了“海大”制造的“船舶模擬駕駛艙”的顧客。 SELECT custkey， name FROM Customer WHER

17、E custkey IN （ SELECT O.custkey FROM Orders O,Lineitme L， PartSupp PS,Part P WHERE O.orderkey=L.orderkey AND L.p artkey=PS.p artkey AND L.su pp key=PS.su pp key AND P S. partkey=P.p artkey AND P.mfgr=海大AND P.name=M舶模擬駕駛艙）; SELECT custkey, name FROM Customer WHERE cuskey IN （ SELECT O.custkey FROM Or

18、ders O,Li neitem L,Part P WHERE O.orderkey=L.orderkey AND L.p artkey=P.p artkey AND p.mfgr=海大AND P.name=船舶模擬駕駛艙); (2) 單層EXISTS嵌套查詢 查詢沒有購買過“海大”制造的“船舶模擬駕駛艙”的顧客。 SELECT custkey, name FROM Customer WHERE NOT EXISTS( SELECT O.custkey FROM Orders O,Li neitem L,P artSu pp PS,Part P WHERE C.cuskey=O.custkey

19、 AND O.orderkey=L.orderkey AND L.p artkey=PS.p artkey AND L.su pp key=PS.su pp key AND P S. partkey=P.p artkey AND p.mfgr=海大AND P.name=船舶模擬駕駛艙); (3) 雙層EXISTS嵌套查詢 查詢至少購買過顧客“張三”購買過的全部零件的顧客姓名。 SELECT CA. name FROM Customer CA WHERE NOT EXISTS (SELECT * FROM Customer CB,Oders OB,Li neitem LB WHERE CB.cu

20、stkey=OB.custkey AND OB.orderkey=LB.orderkey AND CB. name=張三AND NOT EXISTS(SELECT * FROM Orders OC,Li neitem LC WHERE CA.custkey=LC.custkey AND OC.orderkey=LC.orderkey AND LB.s upp key=LC.s upp key AND LB. partkey=LC. partkey); (4) FROM子句中的嵌套查詢 查詢訂單平均金額超過1萬元的顧客中的中國籍顧客信息。 SELECT C.* FROM Customer C,(

21、SELECT custkey FROM Orders GROU P BY custkey HAVING AVG(tota Ip rice)10000) B,Natio n N WHERE C.custkey=B.custkey AND C. natio nkey=N. natio nkey AND N.n ame=中國； (5) 集合查詢(交) 查詢顧客“張三”和“李四”都訂購過的全部零件的信息。 SELECT P.* FROM Customer C,Orders O,L in eitem L,P artSu pp PS,Part P WHERE C.custkey=O.custkey AND

22、 O.orderkey=L.orderkey AND L.su pp key=PS.su pp key AND L.p artkey=PS. partkey AND PS.p artkey=P.p artkey AND C.n ame# 四； INTERSECTION SELECT P.* FROM Customer C,Orders O,L in eitem L,P artSu pp PS,Part P WHERE C.cuskey=O.custkey AND O.orderkey=L.orderkey AND L.p artkey=PS.p artkey AND L.su pp key=P

23、S.su pp key AND PS.p artkey=P.p artkey AND C.n ame=李四； （6）集合查詢（并） 查詢顧客“張三”和“李四”訂購的全部零件的信息。 SELECT P.* FROM Customer C,Orders O,L in eitem L,P artSu pp PS,Part P WHERE C.cuskey=O.custkey AND O.orderkey=L.orderkey AND L.p artkey=PS.p artkey AND L.su pp key=PS.su pp key AND PS.p artkey=P.p artkey AND C

24、.n ame 二張三; UNION SELECT P.* FROM Customer C,Orders O,L in eitem L,P artSu pp PS,Part P WHERE C.cuskey=O.custkey AND O.orderkey=L.orderkey AND L.p artkey=PS.p artkey AND L.su pp key=PS.su pp key AND PS.p artkey=P.p artkey AND C.n ame=李四； （7）集合查詢（差） 顧客“張三”訂購過而“李四”沒訂購過的零件的信息。 SELECT P.* FROM Customer

25、C,Orders O,L in eitem L,P artSu pp PS,Part P WHERE C.cuskey=O.custkey AND O.orderkey=L.orderkey AND L.p artkey=PS.p artkey AND L.su pp key=PS.su pp key AND P S. partkey=P.p artkey AND C.n ame 二張三; EXCEPT SELECT P.* FROM Customer C,Orders O,L in eitem L,P artSu pp PS,Part P WHERE C.cuskey=O.custkey A

26、ND O.orderkey=L.orderkey AND L.p artkey=PS.p artkey AND L.su pp key=PS.su pp key AND P S. partkey=P.p artkey AND C.n ame=李四； 4、數(shù)據(jù)更新實驗 (1) INSERT基本語句(插入全部列的數(shù)據(jù)) 插入一條顧客記錄，要求每列都給一個合理的值。 INSERT INTO Customer VALUES (30,張三,北京市,400.00,Northeast,VI P Customer); (2) INSERT基本語句(插入部分列的數(shù)據(jù)) 插入一條訂單記

27、錄，給出必要的幾個字段值。 INSERT INTO Lin eitem(orderkey,L inenu mber, partkey,s upp key,qua ntity,sh ip date) VALUES(862,ROUND(RANDOM()*1OO,O,479,1,1O,2O12-3-6); /*RANDOM()函數(shù)為隨機小數(shù)生成函數(shù)，ROUND()為四舍五入函數(shù)*/ (3) 批量數(shù)據(jù)INSERT語句 創(chuàng)建一個新的顧客表，把所有中國籍顧客插入到新的顧客表中。 CREATE TABLE NewCustmoer AS SELECT * FROM Customer WITH NO DATA;

28、 /*WITH NO DATA子句使得SELECT查詢只生成一個結(jié)果模式，不查詢出實際 數(shù)據(jù)*/ INSERT INTO NewCustomer /*批量插入 SELECT 語句查詢結(jié)果到 NewCustomer 表中 */ SELECT C.* FROM Costomer C,Nation N WHERE C.nationkey=N.nationkey AND N.name-中國； 創(chuàng)建一個顧客購物統(tǒng)計表，記錄每個顧客及其購物總數(shù)和總價等信息。 CREATE TABLE Sho ppin gStat (custkey INTEGER, qua ntity REAL, tota Iprice

29、REAL); INSERT INTO Shoppin gStat SELECT C.custkey,Sum(L.qua ntity),Sum(O.tota Ip rice)/* 對分組后的數(shù)據(jù)求總 和*/ FROM Customer C,Order O,Li neitem L WHERE C.custkey-O.custkey AND O.orderkey-L.orderkey GROU P BY C.custkey 倍增零件表的數(shù)據(jù)，多次重復執(zhí)行，直到總記錄數(shù)達到50萬為止。 INSERT INTO Part SELECT p artkey+(SELECT COUNT(*) FROM P a

30、rt), n ame,mfgr,bra nd,ty pe,size,co ntai ner,retail price,comme nt FROM P art; (4) UP DATE語句(插入部分記錄的部分列值) “金倉集團”供應的所有零件的供應成本價下降10%。 UP DATE P arts upp SET suppl ycost=s uppi ycost*0.9 /*找出要修改的那些記錄*/ WHERE supp key=(SELECT supp key FROM Supp lier WHERE name=金倉集團); (5) UP DATE語句(利用一個表中的數(shù)據(jù)修改另外一個表中的數(shù)據(jù))

31、 禾用 Part表中的零售價格來修改 Lin eitem 中的 exte nded price,其中 exte nded pnce=P art.retail pnce*qua ntity。 UP DATE Li neitem L SET L.exte nded price=P .retail pnce*L.qua ntity FROM Part P WHERE L.p artkey=P.p artkey; /*Lineitem表也可以直接與 Part表相連接，而不需通過 PartSupp連接*/ (6) DELETE基本語句(刪除給定條件的所有記錄) 刪除顧客張三的所有訂單記錄。 DELECT

32、 FROM Lin eitem/*先刪除張三的訂單明細記錄*/ WHERE orderkey IN(SELECT orderkey FROM Order O,Customer C WHERE O.custkey=C.custkey AND C.n ame=張三); DELECT FROM Order/*再刪除張三的訂單記錄*/ WHERE custkey=(SELECT custkey FROM Customer WHERE n ame=張三); 5、視圖 (1) 創(chuàng)建視圖(省略視圖列名) 創(chuàng)建一個“海大汽配”供應商供應的零件視圖V_DLMU_PartS upp,1要求列出供 應零件的編號、零

33、件名稱、可用數(shù)量、零售價格、供應價格和備注等信息。 CREATE VIEW V_DLMU_PARTSUPP1 AS /* 由 SELECT?句目標列組成視圖屬 性*/ SELECT P.p artkey, P.n ame, PS.availqty ,P .retail price, PS.s upp lycost, P.comme nt FROM Part P,P artS upp P S,Su ppiier S WHERE P.partkey=PS.partkey AND S.suppkey=PS.suppkey AND S.name-海大汽 配； (2) 創(chuàng)建視圖(不能省略列名的情況) 創(chuàng)

34、建一個視圖V_CustAvgOrder,按顧客統(tǒng)計平均每個訂單的購買金額和零件數(shù) 量，要求輸出 顧客編號、姓名，平均購買金額和平均購買零件數(shù)量。 CREATE VIEW V_CustAvgOrder(custkey,c name,avg price,avgqua ntity) AS SELECT C.custkey,MAX(C. name),AVG(O.tota Ip rice),AVG(L.qua ntity) FROM Customer C,Orders O,L in eitem L WHERE C.custkey-O.custkey AND L.orderkey-O.orderkey G

35、ROU P BY C.custkey; (3) 創(chuàng)建視圖(WITH CHECK OPTION 使用 WITH CHECK OP TIQN創(chuàng)建一個“海大汽配”供應商供應的零件視圖 V_DLMU_PartSu pp2要求列出供應零件的編號、可用數(shù)量和供應價格等信息。然 后通過該視圖分別增加、刪除和修改一條“海大汽配”零件供應記錄，驗證WITH CHECK OP TIO是否起作用。 CREATE VIEW V_DLMU_ PartS upp2 AS SELECT p artkey,s upp key,availqty,s uppi ycost FROM PartSu pp WHERE supp ke

36、y=(SELECT supp key 海大汽配) FROM Supp lier WHERE n ame= WITH CHECK OP TION; INSERT INTO V_DLMU _P artS upp2 VALUES (58889,5048,704,77760); UP ADTE V_DLMU _P artS upp2 SET suppl ycost=12 WHERE supp key=58889; DELETE FROM V_DLMU _P artS upp2 WHERE supp key=58889; (4) 可更新的視圖(行列子集視圖) 使用 WITH CHECK OP TIQN創(chuàng)

37、建一個“海大汽配”供應商供應的零件視圖 V_DLMU_PartSu pp4要求列出供應零件的編號、可用數(shù)量和供應價格等信息。然 后通過該視圖分別增加、刪除和修改一條“海大汽配”零件供應記錄，驗證該視 圖是否是可更新的，并比較上述“(3)創(chuàng)建視圖”實驗任務與本任務結(jié)果有何異 同。 CREATE VIEW V_DLMU_PartSu pp3 AS SELECT p artkey,s upp key,availqty,s uppI ycost FROM PartSu pp WHERE supp key=(SELECT supp key FROM Supp lier WHERE name海大汽配);

38、INSERT INTO V_DLUM _P artS upp3 VALUES(58889,5048,704,77760); UP DATE V_DLMU _P artS upp3 SET suppl ycost=12 WHERE supp key=58889; DELETE FROM V_DLMU _P artS upp3 WHERE supp key=58889; (5)可更新的視圖 INSERT INTO V_CustAvgOrder VALUES(100000,NULL,20,2000); (6)刪除視圖(RESTRICT/CASCADE 創(chuàng)建顧客訂購零件明細視圖 V_CustOrd,要

39、求列出顧客編號、姓名、購買零件數(shù)、 金額，然后在該視圖的基礎上，在創(chuàng)建(2)的視圖V_CustAvgOrder,然后使用 RESTRICT選項和CASCADE項刪除視圖V CustOrd。 CREATE VIEW V_CustOrd(custkey,c name,qty,ext price) AS SELECT C.custkey,C. name,L.qua ntity,L.exte nded price FROM Customer C,Order O,Li neitem L WHERE C.custkey=O.custkey AND O.orderkey=L.orderkey; CREATE

40、 VIEW V_CustAvgOrder(custkey,c name,avgqty,avg price) AS SELECT custkey,MAX(c name),AVG(qty),AVG(ext price) FROM V_CustOrd /*在視圖V_CustOrd上再創(chuàng)建視圖*/ GROU P 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 P a

41、rt( name); (2) 創(chuàng)建函數(shù)索引(對某個屬性的函數(shù)創(chuàng)建索引，稱為函數(shù)索引) 在零件表的零件名稱字段上創(chuàng)建一個零件名稱長度的函數(shù)索引。 CREATE INDEX Idx_part_name_fu n ON P art(LENGTH (n ame); (3) 創(chuàng)建復合索引(對兩個及兩個以上的屬性創(chuàng)建索弓I,稱為復合索引) 在零件表的制造商和品牌兩個字段上創(chuàng)建一個復合索引。 CREATE UNIQUE INDEX ldx_part_mfgr_bra nd ON P art(mfgr,bra nd); (4) *創(chuàng)建聚簇索引 在零件表的制造商字段上創(chuàng)建一個聚簇索引。 CREATE UNIQU

42、E INDEX Idx_part_mfgr ON P art(mfgr); CLUSTER Idx_part_mfgr ON P art; (5) 創(chuàng)建Hash索引 零件表的名稱字段上創(chuàng)建一個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) 分析某個SQL查詢語句執(zhí)行時是否使用了索引- EXP LAIN SELECT *

43、 FROM part WHERE n ame零件; (8) *驗證索引效率 創(chuàng)建一個函數(shù)Test In dex,自動計算sql查詢執(zhí)行的時間。 CREATE FUNCTION TestI ndex(p_part_name CHAR(55) RETURN INTEGER AS /*自定義函數(shù)Test In dex():輸入?yún)?shù)為零件名稱，返回 SQL查詢的執(zhí) 行時間*/ DECLARE begi ntime TIMESTA MP; en dtime TIMESTA MP; duratio ntime INTEGER; BEGN SELECT CLOCK_TIMESTAMP() INTO begi

44、ntime;/* 記錄查詢執(zhí)行的開始時 間*/ PERFORM *FROM Part WHERE name=p_partname;/* 執(zhí)行 SQL查詢，不保 存查詢結(jié)果*/ SELECT CLOCK_TIMESTA MP() INTO en dtime; SELECT DATEDIFF( ms ,begintime,endtime) INTO durationtime; RETURN durationtime;/*計算并返回查詢執(zhí)行時間， 時間單位為毫 秒 ms*/ END; /*查看當零件表Part數(shù)據(jù)模型比較小，并且無索引時的執(zhí)行時間 */ SELECT Test In dex(零件名稱

45、); INSERT INTO Part/*不斷倍增零件表的數(shù)據(jù)，直到 50萬條記錄*/ SELECT P artkey+(SELECT COUNT(*) FROM P art), Name,mfgr,bra nd,t yp e,size,co ntai ner,retail price,comme nt FRPM P art; /*查看當零件表Part數(shù)據(jù)模型比較大，但無索引時的執(zhí)行時間*/ SELECT Test In dex(零件名稱)； CREATE INDEX part_name ON Part(name);/*在零件表的零件名稱字段上 創(chuàng)建索引*/ /*查看零件表Part數(shù)據(jù)規(guī)模比較

46、大，有索引時的執(zhí)行時間*/ SELECT Test In dex(); 四、實驗心得 通過本次實驗，我知道只有正確理解數(shù)據(jù)庫模式結(jié)構(gòu)，才能正確設計數(shù)據(jù)庫查詢。 連接查詢是數(shù)據(jù)庫sql查詢中最重要的查詢，連接查詢的設計要特別注意，不同 的查詢表達，其查詢執(zhí)行的性能會有很大差別。正確地設計和執(zhí)行數(shù)據(jù)更新語句， 確保正確地錄入數(shù)據(jù)和更新數(shù)據(jù)，才能保證查詢的數(shù)據(jù)正確。當數(shù)據(jù)更新失敗時， 一個主要原因是更新數(shù)據(jù)時違反了完整性約束。 實驗項目名稱：安全性語言實驗 實驗學時：2 同組學生姓名：孟陳、陳曉雪、季佰軍 實驗地點: 1318 實驗日期: 5.26 實驗成績: 批改時間: 批改教師: 實驗目的 1、

47、 2、 掌握自主存取控制缺陷的定義和維護方法。 掌握數(shù)據(jù)庫審計的設置和管理方法，以便監(jiān)控數(shù)據(jù)庫操作，維護數(shù)據(jù)庫安全。 實驗內(nèi)容和要求 1、 定義用戶、角色，分配權(quán)限給用戶、角色，回收權(quán)限，以相應的用戶名登錄 數(shù)據(jù)庫驗證權(quán)限分配是否正確。選擇一個應用場景，使用自主存取控制機制 設置權(quán)限分配。可以采用兩種方案。 方案一：采用SYSTEM超級用戶登錄數(shù)據(jù)庫，完成所有權(quán)限分配工作，然后 用相應用戶名登錄數(shù)據(jù)庫已驗證權(quán)限分配正確性； 方案二：采用SYSTEM用戶登錄數(shù)據(jù)庫創(chuàng)建3個部門經(jīng)理用戶，并分配相應 的權(quán)限，然后分別用3個經(jīng)理用戶名登錄數(shù)據(jù)庫，創(chuàng)建相應部門的 USER、 ROLE，并分配相應權(quán)限。

48、2、打開數(shù)據(jù)庫審計開關(guān)。以具有審計權(quán)限的用戶登錄數(shù)據(jù)庫，設置審計權(quán)限， 然后以普通用戶登錄數(shù)據(jù)庫，執(zhí)行相應的數(shù)據(jù)操縱 sql語句，驗證相應審計設置 是否生效，最后在一具有審計權(quán)限的用戶登錄數(shù)據(jù)庫， 查看是否存在相應的審計 信息。 三、實驗過程 1、自主存取控制實驗 （1）創(chuàng)建用戶 為采購、銷售和客戶管理等3個部門的經(jīng)理創(chuàng)建用戶標識，要求具有創(chuàng)建用 戶或角色的權(quán)利。 CREATE USER David WITH CREATEROLE P ASSWORD 123456; CREATE USER Tom WITH CREATEROLE P ASSWORD 123456; CREATE USER Ka

49、thy WITH CREATEROLE P ASSWORD 123456; Q為采購、銷售和客戶管理等3個部門的職員創(chuàng)建用戶標識和用戶口令。 CREATE USER Jeffery WITH P ASSWORD 123456; CREATE USER Jane WITH P ASSWORD 123456; CREATE USER Mike WITH P ASSWORD 123456; (2) 創(chuàng)建角色并分配權(quán)限 為各個部門分別創(chuàng)建一個查詢角色，并分配相應的查詢權(quán)限。 CREATE ROLE P urchaseQueryRole; GRANT SELECT ON TABLE Part TO P

50、urchaseQueryRole; GRANT SELECT ON TABLE Su ppiier TO P urchaseQueryRole; GRANT SELECT ON TABLE PartS upp TO P urchaseQueryRole; CREATE ROLE SaleQueryRole; GRANT SELECT ON TABLE Order TO SaleQueryRole; GRANT SELECT ON TABLE Lin eitem TO SaleQueryRole; CREATE ROLE CustomerQueryRole; GRANT SELECT ON TA

51、BLE Customer TO CustomerQueryRole; GRANT SELECT ON TABLE Nation TO CustomerQueryRole; GRANT SELECT ON TABLE Regio n TO CustomerQueryRole; Q為各個部門分別創(chuàng)建一個職員角色，對本部門信息具有查看、插入權(quán)限。 CREATE ROLE P urchaseEm ployeeRole; GRANT SELECT , INSERT ON TABLE Part TO PurchaseEmployeeRole; GRANT SELECT , INSERT ON TABLE

52、Supplier TO PurchaseEmployeeRole; GRANT SELECT , INSERT ON TABLE PartSupp TO PurchaseEmployeeRole; CREATE ROLE SaleEm ployeeRole; GRANT SELECT , INSERT ON TABLE Order TO SaleEmployeeRole; GRANT SELECT , INSERT ON TABLE Lineitem TO SaleEmployeeRole; CREATE ROLE CustomerE mp loyeeRole; GRANT SELECT， I

53、NSERT ON TABLE Customer TO CustomerEmployeeRole; GRANT SELECT，INSERT ON TABLE Nation TO CustomerEmployeeRole; GRANT SELECT，INSERT ON TABLE Region TO CustomerEmployeeRole; 為各個部門創(chuàng)建一個經(jīng)理角色，相應角色對本部門的信息具有完全控制權(quán)限, 對其他部門的信息具有查詢權(quán)。經(jīng)理有權(quán)給本部門資源分配權(quán)限。 CREATE ROLE P urchaseMa nagerRole WITH CREATEROLE; GRANT ALL ON

54、TABLE Part TO P urchaseMa nagerRole； GRANT ALL ON TABLE Supp lier TO P urchaseMa nagerRole; GRANT ALL ON TABLE PartS upp TO P urchaseMa nagerRole； GRANT SaleQueryRole TO P urchaseMa nagerRole GRANT CustomerQueryRole TO P urchaseMa nagerRole CREATE ROLE SaleMa nagerRole WITH CREATEROLE; GRANT ALL ON

55、TABLE Order TO SaleMa nagerRole GRANT ALL ON TABLE Lin eitem TO SaleMa nagerRole GRANT SaleQueryRole TO SaleMa nagerRole GRANT PurchaseQueryRole TO SaleMa nagerRole CREATE ROLE CustomerMa nagerRole WITH CREATEROLE; GRANT ALL ON TABLE Customer TO CustomerMa nagerRole GRANT ALL ON TABLE Nation TO Cust

56、omerMa nagerRole GRANT ALL ON TABLE Regio n TO CustomerMa nagerRole GRANT SaleQueryRole TO CustomerMa nagerRole GRANT P urchaseQueryRole TO CustomerMa nagerRole (3) 給用戶分配權(quán)限 給部門經(jīng)理分配權(quán)限。 GRANT Pu rchaseMa nagerRole TO David WITH ADMIN OP TION; GRANT SaleMa nagerRole TO Tom WITH ADMIN OP TION; GRANT Cus

57、tomerMa nagerRole TO Kathy WITH ADMIN OP TION; 給各部門職員分配權(quán)限 GRANT P urchaseEm ployeeRole TO Jeffery; GRANT SaleEm plo yeeRole TO Jane; GRANT CustomerE mp loyeeRole TO Mike; (4) 回收角色或用戶權(quán)限 收回客戶經(jīng)理角色的銷售信息查看權(quán)限。 REVOKE SaleQueryRole FROM CustomerMa nagerRole; 回收MIKE的客戶部門職員權(quán)限。 REVOKE CustomerE mp loyeeRole F

58、ROM Mike; (5) 驗證權(quán)限分配正確性 以David用戶名登錄數(shù)據(jù)庫，驗證采購部門經(jīng)理的權(quán)限 SELECT * FROM P art; DELETE * FROM Order; (回收MIKE的客戶部門職員權(quán)限 SELECT * FROM Customer; SELECT * FROM P art; 2、審計實驗 (1) 審計開關(guān) 顯示當前審計開關(guān)狀態(tài) SHOW AUDIT_TRAIL; 打開審計開關(guān) SET AUDIT_TRAIL TO ON; (2) 數(shù)據(jù)庫操作審計 對客戶信息表上的刪除操作設置審計。 AUDIT DELETE ON Sales.Customer BY ACCESS

59、; 以普通用戶登錄，執(zhí)行sql語句。 DELETE Sales.Customer WHERE custkey=1011; 查看數(shù)據(jù)庫對象審計信息，驗證審計設置是否生效。 SELECT * FROM SYS_AUDIT_OBJECT; (3) 語句級審計 對表定義的更改語句 ALTER設置審計 AUDIT ALTER TABLE BY ACCESS; 查看所有數(shù)據(jù)庫所有語句級審計設置，驗證審計設置是否生效 SELECT * FROM SYS_STMT_AUDIT_O PTS; 以普通用戶登錄，執(zhí)行sql語句，驗證審計設置是否生效 ALTER TABLE Customer ADD COLUMN t

60、t INT; 查看所有審計信息 SELECT * FROM SYS_AUDIT_TRAIL; 四、實驗心得 通過本次實驗，知道了定義用戶、角色，分配權(quán)限給用戶、角色，回收權(quán)限，并 以相應的用戶名登陸數(shù)據(jù)庫驗證權(quán)限分配是否正確的方法。并且知道了數(shù)據(jù)庫審 計的目的和方法。做實驗的同時，對 sql語句有了更熟練的運用。 實驗項目名稱：完整性語言實驗 實驗學時：2 同組學生姓名：孟陳、陳曉雪、季佰軍 實驗地點: 1318 實驗日期: 6.2 實驗成績: 批改時間: 批改教師: 實驗目的 1、 掌握實體完整性的定義和維護方法; 2、 掌握參照完整性的定義和維護方法; 3、 掌握用戶自定義完整性的定義和維