第一篇：河北工業大學數據庫原理及應用實驗實驗報告

《數據庫原理及應用實驗》

姓名：徐毅民學號： 153299

實驗報告

班級：網絡151 實驗1數據庫定義與操作語言實驗

實驗1.4數據更新實驗

1、實驗目的

熟悉數據庫的數據更新操作，能夠使用SQL語句對數據庫進行數據的插入、刪除、修改操作。

2、實驗內容和要求

針對TPC-H數據庫設計數據單元組插入、批量數據插入、修改數據和刪除數據等SQL語句。理解和掌握INSERTT、UPDATE、和DELETE語法結構的各個組成成分，結合嵌套SQL子查詢，分別設計幾種不同形式的插入、修改和刪除數據的語句，并調試成功。

3、實驗重點和難點

實驗重點：插入、修改和刪除數據的SQL 實驗難點：與嵌套SQL子查詢相結合的插入、修改和刪除數據的SQL語句；利用一個表的數據來插入、修改和刪除另一個表的數據。

4、實驗報告示例

（1）INSERT基本語句(插入全部列的數據)插入一條顧客記錄，要求每列都給一個合理的值。

INSERT INTO Customer VALUES(30,'張三','北京市',40,'010-51001199',0.00,'Northeast','VIP Customer');（2）INSERT基本語句（插入部分列的數據）插入一條訂單記錄，給出必要的幾個字段值。

INSERT INTO Lineitem(orderkey,Linenumber,partkey,suppkey,quantity,shipdate)VALUES(862,ROUND(RANDOM()*100,0,479,1,10,'2012-3-6');/*RANDOM()函數為隨機小數生成函數，ROUND()為四舍五入函數*/（3）批量數據INSERT語句

① 創建一個新的顧客表，把所有中國籍顧客插入到新的顧客表中。

INSERT INTO NewCustomer/*批量插入SELECT 語句查詢結果到NewCustomer表中*/ SELECT C.* FROM Costomer C,Nation N WHERE C.nationkey=N.nationkey AND N.name='中國';②創建一個顧客購物統計表，記錄每個顧客及其購物總數和總價等信息。

CREATE TABLE ShoppingStat(custkey INTEGER, quantity REAL, totalprice REAL);INSERT INTO ShoppingStat SELECT C.custkey,Sum(L.quantity),Sum(O.totalprice)FROM Customer C,Order O,Lineitem L WHERE C.custkey=O.custkey AND O.orderkey=L.orderkey GROUP BY C.custkey ③倍增零件表的數據，多次重復執行，直到總記錄數達到50萬為止。

INSERT INTO Part SELECT partkey+(SELECT COUNT(*)FROM Part), name,mfgr,brand,type,size,container,retailprice,comment FROM Part;（4）UPDATE語句（插入部分記錄的部分列值）

“金倉集團”供應的所有零件的供應成本價下降10%。

UPDATE PartSupp SET supplycost=supplycost*0.9 WHERE suppkey=(SELECT suppkey

FROM Supplier WHERE name='金倉集團');（5）UPDATE語句（利用一個表中的數據修改另外一個表中的數據）

利用Part表中的零售價格來修改

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表相連接，而不需通過PartSupp連接*/（6）DELETE基本語句（刪除給定條件的所有記錄）

刪除顧客張三的所有訂單記錄。

DELECT FROM Lineitem 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='張三');實驗1.5 視圖實驗

1、實驗目的

熟悉SQL語言有關視圖的操作，能夠熟練使用SQL語句來創建需要的視圖，定義數據庫外模式，并能使用所創建的視圖實現數據管理。

2、實驗內容和要求

針對給定的數據庫模式，以及相應的應用需求，創建視圖和帶WITH CHECK OPTION的視圖，并驗證視圖WITH CHECK OPTION選項的有效性。理解和掌握視圖消除執行原理，掌握可更新視圖和不可更新視圖的區別。

3、實驗重點和難點 實驗重點：創建視圖。

實驗難點：可更新的視圖和不可更新的視圖之區別，WITH CHECK OPTION的驗證。

4、實驗報告示例

（1）創建視圖（省略視圖列名）

創建一個“海大汽配”供應商供應的零件視圖V_DLMU_PartSupp1，要求列出供應零件的編號、零件名稱、可用數量、零售價格、供應價格和備注等信息。

CREATE VIEW V_DLMU_PARTSUPP1 AS /*由SELECT子句目標列組成視圖屬性*/

/*再刪除張三的訂單記錄*/

/*先刪除張三的訂單明細記錄*/ 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）創建視圖（不能省略列名的情況）

創建一個視圖V_CustAvgOrder,按顧客統計平均每個訂單的購買金額和零件數量，要求輸出 顧客編號、姓名，平均購買金額和平均購買零件數量。

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）創建視圖（WITH CHECK OPTION）

使用WITH CHECK OPTION，創建一個“海大汽配”供應商供應的零件視圖V_DLMU_PartSupp2，要求列出供應零件的編號、可用數量和供應價格等信息。然后通過該視圖分別增加、刪除和修改一條“海大汽配”零件供應記錄，驗證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，創建一個“海大汽配”供應商供應的零件視圖V_DLMU_PartSupp4，要求列出供應零件的編號、可用數量和供應價格等信息。然后通過該視圖分別增加、刪除和修改一條“海大汽配”零件供應記錄，驗證該視圖是否是可更新的，并比較上述“（3）創建視圖”實驗任務與本任務結果有何異同。

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）

創建顧客訂購零件明細視圖V_CustOrd,要求列出顧客編號、姓名、購買零件數、金額，然后在該視圖的基礎上，在創建（2）的視圖V_CustAvgOrder,然后使用RESTRICT選項和CASCADE選項刪除視圖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 GROUP BY custkey;

DROP VIEW V_CustOrd RESTRICT;

DROP VIEW V_CustOrd CASCADE;實驗1.6 索引實驗

1、實驗目的

掌握索引設計原則和技巧，能夠創建合適的索引以提高數據庫查詢、統計分析效率。

2、實驗內容和要求

針對給定的數據庫模式和具體應用要求，創建唯一索引、函數索引、復合索引等；修改索引；刪除索引。設計相應的SQL查詢驗證索引有效性。學習利用EXPLAIN命令分析SQL查詢是否使用了所創建的索引，并能夠分析其原因，執行SQL查詢并估算索引提高查詢效率的百分比。要求實驗數據集達到10萬條記錄以上的數據量，以便驗證索引效果。

3、實驗重點和難點 實驗重點：創建索引。

實驗難點：設計SQL查詢驗證索引有效性。

4、實驗報告示例

/*在視圖V_CustOrd上再創建視圖*/（1）創建唯一索引

在零件表的零件名稱字段上創建唯一索引。

CREATE UNIQUE INDEX Idx_part_nameON Part(name);（3）創建復合索引（對兩個及兩個以上的屬性創建索引，稱為復合索引）

在零件表的制造商和品牌兩個字段上創建一個復合索引。

CREATE UNIQUE INDEX Idx_part_mfgr_brand ON Part(mfgr,brand);（4）修改索引名稱

修改零件表的名稱字段上的索引名。

ALTER INDEX Idx_part_name_hash RENAME TO Idx_part_name_hash_new;（5）*驗證索引效率

創建一個函數TestIndex,自動計算sql查詢執行的時間。

CREATE FUNCTION TestIndex(p_part_name CHAR(55))RETURN INTEGER AS /*自定義函數TestIndex()：輸入參數為零件名稱，返回SQL查詢的執行時間*/ DECLARE begintime TIMESTAMP;endtime TIMESTAMP;durationtime INTEGER;BEGN SELECT CLOCK_TIMESTAMP()INTO begintime;/*記錄查詢執行的開始時間*/ PERFORM *FROM Part WHERE name=p_partname;/*執行SQL查詢，不保存查詢結果*/ SELECT CLOCK_TIMESTAMP()INTO endtime;SELECT DATEDIFF(?ms?,begintime,endtime)INTO durationtime;

RETURN durationtime;

END;/*查看當零件表Part數據模型比較小，并且無索引時的執行時間*/ SELECT TestIndex(?零件名稱?);

INSERT INTO Part

/*不斷倍增零件表的數據，直到50萬條記錄*/

/*計算并返回查詢執行時間，時間單位為毫秒ms*/ SELECT partkey+(SELECT COUNT(*)FROM Part), Name,mfgr,brand,type,size,container,retailprice,comment FRPM Part;/*查看當零件表Part數據模型比較大，但無索引時的執行時間*/ SELECT TestIndex(?零件名稱?);

CREATE INDEX part_name ON Part(name);索引*/ /*查看零件表Part數據規模比較大，有索引時的執行時間*/ SELECT TestIndex();

/*在零件表的零件名稱字段上創建

實驗2 安全性語言實驗

實驗2.1

自主存取控制實驗

1、實驗目的

掌握自主存取控制缺陷的定義和維護方法。

2、實驗內容和要求

定義用戶、角色，分配權限給用戶、角色，回收權限，以相應的用戶名登錄數據庫驗證權限分配是否正確。選擇一個應用場景，使用自主存取控制機制設置權限分配。可以采用兩種方案。

方案一：采用SYSTEM超級用戶登錄數據庫，完成所有權限分配工作，然后用相應用戶名登錄數據庫已驗證權限分配正確性；

方案二：采用SYSTEM用戶登錄數據庫創建3個部門經理用戶，并分配相應的權限，然后分別用3個經理用戶名登錄數據庫，創建相應部門的USER、ROLE，并分配相應權限。

下面的實驗報告示例采用了實驗方案一。驗證權限分配之前，請備份好數據庫，針對不同的用戶所具有的權限，分別設計相應的SQL語句加以驗證。

3、實驗重點和難點

實驗重點：定義角色，分配權限和回收權限。實驗難點：實驗方案二實現權限的再分配和回收。

4、實驗報告示例（1）創建用戶 為采購、銷售和客戶管理等3個部門的經理創建用戶標識，要求具有創建用戶或角色的○權利。

CREATE USER David WITH CREATEROLE PASSWORD '123456';CREATE USER Tom WITH CREATEROLE PASSWORD '123456';CREATE USER Kathy WITH CREATEROLE PASSWORD '123456';2為采購、銷售和客戶管理等3個部門的職員創建用戶標識和用戶口令。○CREATE USER Jeffery WITH PASSWORD '123456';CREATE USER Jane WITH PASSWORD '123456';CREATE USER Mike WITH PASSWORD '123456';（2）創建角色并分配權限

1為各個部門分別創建一個查詢角色，并分配相應的查詢權限。○CREATE ROLE PurchaseQueryRole;GRANT SELECT ON Part TO PurchaseQueryRole;GRANT SELECT ON Supplier TO PurchaseQueryRole;GRANT SELECT ON PartSupp TO PurchaseQueryRole;

CREATE ROLE SaleQueryRole;GRANT SELECT ON Order TO SaleQueryRole;GRANT SELECT ON Lineitem TO SaleQueryRole;

CREATE ROLE CustomerQueryRole;GRANT SELECT ON Customer TO CustomerQueryRole;GRANT SELECT ON Nation TO CustomerQueryRole;GRANT SELECT ON Region TO CustomerQueryRole;2為各個部門分別創建一個職員角色，對本部門信息具有查看、插入權限。○CREATE ROLE PurchaseEmployeeRole;GRANT SELECT，INSERT ON Part TO PurchaseEmployeeRole;GRANT SELECT，INSERT ON Supplier TO PurchaseEmployeeRole;GRANT SELECT，INSERT ON TABLE PartSupp TO PurchaseEmployeeRole;

CREATE ROLE SaleEmployeeRole;GRANT SELECT，INSERT ON Order TO SaleEmployeeRole;GRANT SELECT，INSERT ON Lineitem TO SaleEmployeeRole;

CREATE ROLE CustomerEmployeeRole;GRANT SELECT，INSERT ON Customer TO CustomerEmployeeRole;GRANT SELECT，INSERT ON Nation TO CustomerEmployeeRole;GRANT SELECT，INSERT ON Region TO CustomerEmployeeRole;3為各個部門創建一個經理角色，相應角色對本部門的信息具有完全控制權限，對其他部門○的信息具有查詢權。經理有權給本部門資源分配權限。

CREATE ROLE PurchaseManagerRole WITH CREATEROLE； GRANT ALL ON Part TO PurchaseManagerRole； GRANT ALL ON Supplier TO PurchaseManagerRole； GRANT ALL ON PartSupp TO PurchaseManagerRole； GRANT SaleQueryRole TO PurchaseManagerRole； GRANT CustomerQueryRole TO PurchaseManagerRole；

CREATE ROLE SaleManagerRole WITH CREATEROLE； GRANT ALL ON Order TO SaleManagerRole GRANT ALL ON Lineitem TO SaleManagerRole GRANT SaleQueryRole TO SaleManagerRole GRANT PurchaseQueryRole TO SaleManagerRole

CREATE ROLE CustomerManagerRole WITH CREATEROLE； GRANT ALL ON Customer TO CustomerManagerRole GRANT ALL ON Nation TO CustomerManagerRole GRANT ALL ON Region TO CustomerManagerRole GRANT SaleQueryRole TO CustomerManagerRole GRANT PurchaseQueryRole TO CustomerManagerRole（3）給用戶分配權限 1給部門經理分配權限。○GRANT PurchaseManagerRole TO David WITH ADMIN OPTION;GRANT SaleManagerRole TO Tom WITH ADMIN OPTION;GRANT CustomerManagerRole TO Kathy WITH ADMIN OPTION;2給各部門職員分配權限 ○GRANT PurchaseEmployeeRole TO Jeffery;GRANT SaleEmployeeRole TO Jane;GRANT CustomerEmployeeRole TO Mike;（4）回收角色或用戶權限

1收回客戶經理角色的銷售信息查看權限。○REVOKE SaleQueryRole FROM CustomerManagerRole;2回收MIKE的客戶部門職員權限。○REVOKE CustomerEmployeeRole FROM Mike;（5）驗證權限分配正確性

1以David用戶名登錄數據庫，驗證采購部門經理的權限 ○SELECT * FROM Part;DELETE * FROM Order;2回收MIKE的客戶部門職員權限 ○SELECT * FROM Customer;SELECT * FROM Part;實驗2.2

審計實驗

1、實驗目的

掌握數據庫審計的設置和管理方法，以便監控數據庫操作，維護數據庫安全。

2、實驗內容和要求

打開數據庫審計開關。以具有審計權限的用戶登錄數據庫，設置審計權限，然后以普通用戶登錄數據庫，執行相應的數據操縱sql語句，驗證相應審計設置是否生效，最后在一具有審計權限的用戶登錄數據庫，查看是否存在相應的審計信息。

3、實驗重點和難點

實驗重點：數據庫對象級審計，數據庫語句級審計。

實驗難點：合理地設置各種審計信息。一方面，為了保護系統重要的敏感數據，需要系統地設置各種審計信息，不能留有各種漏洞，以便隨時監督系統使用情況，一旦出現問題也便于追查；另一方面，審計信息設置過多會嚴重影響數據庫的使用性能，因此需要合理配置。

4、實驗報告示例（1）審計開關

1顯示當前審計開關狀態 ○SHOW AUDIT_TRAIL;2打開審計開關 ○SET AUDIT_TRAIL TO ON;（2）數據庫操作審計

1對客戶信息表上的刪除操作設置審計。○AUDIT DELETE ON Sales.Customer BY ACCESS;2以普通用戶登錄，執行sql語句。○DELETE Sales.Customer WHERE custkey=1011;3查看數據庫對象審計信息，驗證審計設置是否生效。○SELECT * FROM SYS_AUDIT_OBJECT;（3）語句級審計

1對表定義的更改語句ALTER設置審計 ○AUDIT ALTER TABLE BY ACCESS;2查看所有數據庫所有語句級審計設置，驗證審計設置是否生效 ○SELECT * FROM SYS_STMT_AUDIT_OPTS;3以普通用戶登錄，執行sql語句，驗證審計設置是否生效 ○ALTER TABLE Customer ADD COLUMN tt INT;4查看所有審計信息 ○SELECT * FROM SYS_AUDIT_TRAIL;

四、實驗心得

通過本次實驗，知道了定義用戶、角色，分配權限給用戶、角色，回收權限，并以相應的用戶名登陸數據庫驗證權限分配是否正確的方法。并且知道了數據庫審計的目的和方法。做實驗的同時，對sql語句有了更熟練的運用。

實驗3 完整性語言實驗

實驗3.1 實體完整性實驗

1、實驗目的

掌握實體完整性的定義和維護方法。

2、實驗內容和要求

定義實體完整性，刪除實體完整性。能夠寫出兩種方式定義實體完整性的SQL語句：創建表時定義實體完整性、創建表后定義實體完整性。設計SQL語句驗證完整性約束是否起作用。

3、實驗重點和難點

實驗重點：創建表時定義實體完整性。實驗難點：有多個候選碼時實體完整性的定義。

4、實驗報告示例

（1）創建表時定義實體完整性（列級實體完整性）

定義供應商表的實體完整性。CREATE TABLE Supplier（suppkey INTEGER CONSTRAINT c1 PRIMARY KEY，name CHAR(25)，address VARCHAR(40)，nationkey INTEGER，phone CHAR(15)，acctbal REAL, comment VARCHAR(101));（2）創建表時定義實體完整性（表級實體完整性）

CREATE TABLE Supplier（suppkey INTEGER，name CHAR(25)，address VARCHAR(40)，nationkey INTEGER，phone CHAR(15)，acctbal REAL，comment VARCHAR(101)，CONSTRAINT c1 PRIMARY KEY(suppkey));（3）創建表后定義實體完整性 定義供應商表。

CREATE TABLE Supplier（suppkey INTEGER，name CHAR(25)，address VARCHAR(40)，nationkey INTEGER，phone CHAR(15)，acctbal REAL，comment VARCHAR(101));ALTER TABLE Supplier ADD CONSTRAINT c1 PRIMARY KEY(suppkey);（4）定義實體完整性（主碼由多個屬性組成）定義供應關系表的實體完整性。

CREATE TABLE PartSupp（partkey INTEGER，suppkey INTEGER，availqty INTEGER，supplycost REAL，comment VARCHAR(199)，PRIMARY KEY(partkey,suppkey));（5）有多個候選碼時定義實體完整性

定義國家表的實體完整性，其中nationkey和name都是候選碼，選擇nationkey作為主碼，name上定義唯一性約束。

CREATE TABLE nation（nationey INTEGER CONSTRAINT c1 PRIMARY KEY，name CHAR(25)UNIQUE，regionkey INTEGER，comment VARCHAR(152));（6）刪除實體完整性

刪除國家實體的主碼。

ALTER TABLE nation DROP CONSTRAINT c1;

（7）增加兩條相同記錄，驗證實體完整性是否起作用

/*插入兩條主碼相同的記錄就會違反實體完整性約束*/ INSERT INTO Supplier(suppkey,name,address,nationkey,phone,acctbal,comment)VALUES(11,?test1?,?test1?,?101?,?12345678?,0.0,?test1?);INSERT INTO Supplier(suppkey,name,address,nationkey,phone,acctbal,comment)

VALUES(11,?test2?,?test2?,?102?,?12345?,0.0,?test2?);5.思考題

（1）所有列級完整性約束都可以改寫成表級完整性約束，而表級完整性約束不一定能改寫成列級完整性約束。請舉例說明。

答：當主碼由多個屬性組成時，只能定義表級完整性約束。（2）什么情況下會違反實體完整性約束，DBMS將做何種違約處理？

答：1.主碼值不唯一 2.主碼的各個屬性有空。

違約處理：拒絕執行、級聯執行等操作。

實驗3.2 參照完整性實驗

1、實驗目的

掌握參照完整性的定義和維護方法。

2、實驗內容和要求

定義參照完整性，定義參照完整性的違約處理，刪除參照完整性。寫出兩種方式定義參照完整性的SQL語句：創建表時定義參照完整性、創建表后定義參照完整性。

3、實驗重點和難點

實驗重點：創建表時定義參照完整性。實驗難點：參照完整性的違約處理定義。

4、實驗報告示例

（1）創建表時定義參照完整性

先定義地區表的實體完整性，再定義國家表的參照完整性。

CREATE TABLE region（regionkey INTEGER PRIMARY KEY，name CHAR(25)，comment VARCHAR(152));

CREATE TABLE nation（nationkey INTEGER PTIMARY KEY，name CHAR(25)，regionkey INTEGER REFERENCES region(regionkey), /*列級參照完整性*/

comment VARCHAR(152));

或者：

CREATE TABLE nation（nationkey INTEGER PTIMARY KEY，name CHAR(25)，regionkey INTEGER，comment VARCHAR(152)，CONSTRAINT c1 FOREIGN KEY(regionkey)REFERENCES region(regionkey));

/*表級參照完整性*/（2）創建表后定義參照完整性

定義國家表的參照完整性。

CREATE TABLE nation（nationkey INTEGER PTIMARY KEY，name CHAR(25)，regionkey INTEGER，comment VARCHAR(152));ALTER TABLE nation ADD CONSTRAINT c1 FOREIGN KEY(regionkey)REFERENCES region(regionkey);（3）定義參照完整性（外碼由多個屬性組成）定義訂單項目表的參照完整性。

CREATE TABLE PartSupp（partkey INTEGER，suppkey INTEGER，availqty INTEGER，supplycost REAL，comment VARCHAR(199)，PRIMARY KEY(partkey,suppkey));CREATE TABLE Lineitem（orderkey INTEGER REFERENCES Orders(orderkey)，partkey INTEGER REFERENCES Part(partkey)，suppkey INTEGER REFERENCES Supplier(suppkey)，linenumber INTEGER，quantity REAL，PRIMARY KEY(orderkey,linenumber)，FOREIGN KEY(partkey,suppkey)REFERENCES PartSupp(partkey,suppkey));（4）定義參照完整性的違約處理

定義國家表的參照完整性，當刪除或修改被參照表記錄時，設置參照表中相應記錄的值為空。

CREATE TABLE nation（nationkey INTEGER PRIMARY KEY，name CHAR(25)，regionkey INTEGER，comment VARCHAR(152), CONSTRAINT c1 FOREIGN KEY(regionkey)REFERENCES Region(regionkey)ON DELETE SET NULL ON UPDATE SET NULL);（5）刪除參照完整性 刪除國家表的外碼。ALTER TABLE nation DROP CONSTRING c1;（6）插入一條國家記錄，驗證參照完整性是否起作用

/*插入一條國家記錄，如果‘1001’號地區記錄不存在，違反參照完整性約束*/ INSERT INTO nation(nationkey,name,regionkey,comment)VALUES(1001,?nation1?,1001,?comment1?);

實驗3.3用戶自定義完整性實驗

1、實驗目的

掌握用戶自定義完整性的定義和維護方法。

2、實驗內容和要求

針對具體應用語義，選擇NULL/NOT NULL、DEFAULT、UNIQUE、CHECK等，定義屬性上的約束條件。

3、實驗重點和難點

實驗重點：NULL/NOT NULL、DEFAULT。實驗難點：CHECK。

4、實驗報告示例

（1）定義屬性NULL/NOT NULL約束 定義地區表各屬性的NULL/NOT NULL屬性。CREATE TABLE region（regionkey INTEGER NOT NULL PRIMARY KEY，name CHAR(25)NOT NULL，comment VARCHAR(152)NULL);（2）定義屬性DEFAULT約束

定義國家表的regionkey的缺省屬性值為0值，表示其他地區。

CREATE TABLE nation(nationkey INTEGER PRIMARY KEY, name CHAR（25），regionkey INTEGER DEFAULT 0, comment VARCHAR(152), CONSTRAINT c1 FOREIGN KEY(regionkey)REFERENCES Region(region));（3）定義屬性UNIQUE約束

定義國家表的名稱屬性必須唯一的完整性約束。CREATE TABLE nation(nationkey INTEGER PRIMARY KEY, name CHAR(25)UNIQUE, regionkey INTEGER, comment VARCHAR(152));（4）使用CHECK 使用CHECK定義訂單項目表中某些屬性應該滿足的約束。CREATE TABLE Lineitem(orderkey INTEGER REFERENCES Orders(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), shipdate DATE, commitdate DATE，receiptdate DATE, shipinstruct CHAR(25), shipmode CHAR(10), comment VARCHAR(44)，PRIMARY KEY(orderkey,linenumber), FOREIGN KEY(partkey,suppkey)REFERENCES PartSupp(partkey,suppkey), CHECK(shipdate < receiptdate)，/*裝運日期<簽收日期*/ CHECK(returnflag IN('A','R','N')));/*退貨標記為A或R或N*/（5）修改Lineitem的一條記錄驗證是否違反CHECK約束

UPDATE sales.Lineitem

SET shipdate='2015-01-05',receiptdate='2015-01-01' WHERE orderkey=5005 AND linenumber=1;

第二篇：數據庫原理實驗報告

南 京 曉 莊 學 院

《數據庫原理與應用》

課程實驗報告

實驗一 SQL Server 2005常用服務與實用工具實驗

所在院(系): 數學與信息技術學院 班級：

學號：

姓名：

１.實驗目的

(1)了解Microsoft 關系數據庫管理系統SQL Server的發展歷史及其特性。(2)了解SQL Server 2005的主要組件、常用服務和系統配置。

(3)掌握Microsoft SQL Server Management Studio 圖形環境的基本操作方法。了解使用“SQL Server 2005 聯機從書”獲取幫助信息的方法；了解“查詢編輯器”的使用方法；了解模板的使用方法。

2.實驗要求

(1)收集整理Microsoft關系數據庫管理系統SQL Server的相關資料，總結其發展歷史及SQL Server 2005主要版本類別和主要功能特性。

(2)使用SQL Server配置管理器查看和管理SQL Server 2005服務。

(3)使用Microsoft SQL Server Management Studio連接數據庫；使用SQL Server幫助系統獲得所感興趣的相關產品主題/技術文檔。

(4)使用Microsoft SQL Server Management Studio“查詢編輯器”編輯并執行Transact-SQL查詢語句。

(5)查看Microsoft SQL Server 2005模板，了解模板的使用方法。(6)按要求完成實驗報告。

3.實驗步驟、結果和總結實驗步驟/結果

(1)簡要總結SQL Server系統發展歷史及SQL Server 2005主要版本類別與主要功能特性。

(2)總結SQL Server Management Studio的主要操作方法。

(3)總結查詢編輯器的功能和主要操作方法,并舉例說明。

(4)總結“模板”的使用方法，并舉例說明。

4．實驗思考：

查詢相關資料，簡要描述SQL Server 2005的主要服務。

第三篇：數據庫原理實驗報告

南 京 曉 莊 學 院

《數據庫原理與應用》

課程實驗報告

實驗二 數據庫的創建、管理、備份及還原實驗

所在院(系): 數學與信息技術學院 班級：

學號：

姓名：

1.實驗目的

(1)掌握分別使用SQL Server Management Studio圖形界面和Transact-SQL語句創建和修改數據庫的基本方法；

(2)學習使用SQL Server查詢分析窗口接收Transact-SQL語句和進行結果分析。

(3)了解SQL Server的數據庫備份和恢復機制，掌握SQL Server中數據庫備份與還原的方法。

2.實驗要求

(1)使用SQL Server Management Studio創建“教學管理”數據庫。

(2)使用SQL Server Management Studio修改和刪除“教學管理”數據庫。(3)使用Transact-SQL語句創建“教學管理”數據庫。

(4)使用Transact-SQL語句修改和刪除“教學管理”數據庫。(5)使用SQL Server Management Studio創建“備份設備”；使用SQL Server Management Studio對數據庫“教學管理”進行備份和還原。(6)SQL Server 2005數據庫文件的分離與附加。(7)按要求完成實驗報告

3.實驗步驟、結果和總結實驗步驟/結果

(1)總結使用SQL Server Management Studio創建、修改和冊除“TM”(教學管理）數據庫的過程。

一、使用SQL Server Management Studio創建數據庫的步驟如下：

a.在磁盤上新建一個目錄，如在C：盤中新建“MyDB“目錄

b.在wimdows系統“開始“菜單中，依次選取”程序->Microsoft SQL Server 2005->SQL Management Studio” ,打開SQL Server Management Studio并連接到SQL Server 2005服務。

c.在“對象資源管理器“中單擊SQL Server服務器前面的“+”號或直接雙擊數據庫名稱，展開該服務器對象資源樹形結構，然后右鍵點擊“數據庫”文件夾，在彈出的快捷菜單上選擇“新建數據庫”選項

d.在打開的“新建數據庫”對話窗口中輸入數據庫名稱“教學管理”，在該窗口中“數據庫文件”設置部分可以修改數據文件和日志文件的文件名、初始大小、保存路徑等。

e．單擊“確定”按鈕，創建“教學管理”數據庫。

二、使用SQL Server Management Studio修改和刪除“教學管理”數據庫

1、使用SQL Server Management Studio圖形界面直接修改“教學管理”數據庫名為“TM”

2、使用SQL Server Management Studio圖形界面查看和修改數據庫屬性 a.在快捷菜單中選擇“屬性”項進入

使用

3.使用SQL Server Management Studio圖形界面刪除數據庫 在第一幅圖中的快捷菜單中選擇“刪除”項

(2)總結在實驗中為創建、修改和刪除“教學管理”數據庫所編寫的各條T-SQL語句及其完成了什么功能。

1、創建：

CREATE DATABASE 教學管理 ON PRIMARY(Name=JXGL, FileName='C:MyDBJXGL_Data.mdf', Size=3MB, MaxSize=100MB, filegrowth=1MB)LOG ON(Name=JXGL_Log, FileName='C:MyDBJXGL_Log.ldf', Size=1MB, MaxSize=UNLIMITED, FileGrowth=10%);點擊執行

2、使用Transact-SQL語句修改和刪除“教學管理”數據庫

增加數據文件。例如，在數據庫“教學管理”中增加數據文件JXGL_EXT,需要在數據庫查詢編輯器中輸入代碼：

在增加數據文件之前，要先獲得修改權限，即alter database數據庫句法，然后再添加數據文件。具體參數也有5項，與創建數據文件相同，在添加數據文件項中，name項是必不可少的。

編寫T—SQL語句，刪除“教學管理”數據庫中的數據文件或日志文件，注意不能刪除非空文件。如刪除剛添加到數據庫中的數據文件“jxgl_ext1” 實例代碼如下： ALTER DATABASE 教學管理

REMOVEFILE jxgl_ex1——刪除數據庫文件時應指定其邏輯名稱

SQL語句刪除數據庫?刪除了數據庫“教學管理” drop database 教學管理

檢查所輸入SQL語句有無語法錯，確認正確后, 按F5鍵或單擊“執行”按鈕，這樣就刪除了數據庫“教學管理”。

(3)總結使用SQL Server Management Studio備份與還原數據庫的幾種方法。

1、創建“備份設備”

2、對數據庫“TM”進行備份

1）打開 SQL Server Management Studio，右擊需要備份的數據庫，從彈出的快捷菜單中依次選擇【任務】--【備份】命令，打開【備份數據庫】對話框。如下圖所示：

（2）在【備份數據庫】對話框的【常規】頁面中根據需要選擇需要備份的【數據庫】，在本示例選擇備份數據庫.接下來還可以選擇【備份類型】、備份集的名稱等相關參數。默認情況下，備份操作會所數據庫中的數據備份到 SQL SERVER 數據庫的默認工作目錄

（3）切換【備份數據庫】對話框中的【選項】頁面。在此頁面中，可以根據需要進行相應的設置。例如，可以根據需要將現有數據備分到現有的備份集中，也可使用數據庫中的當前數據覆蓋現有的備份集。除此之外。還可以設置備份操作的可靠性選項。

單擊【確定】按鈕，即可執行備份操作。一旦備份操作結束。SQL SERVER 數據庫系統將彈出名為 Microsoft SQL Server Management Studio 的對話框，提示備份已成功完成。

3、使用“TM”數據庫完整備份還原數據庫到備份完成時點的數據庫狀態 通過 SQL Server Management Studio 還原數據庫

使用 SQL Server Management Studio 進行數據還原操作的步驟如下所示：

（1）右擊【數據庫】節點，從彈出的快捷菜單中選擇【還原數據庫】命令，打開【還原數據庫】對話框，如下圖所示：

在[還原數據庫]對話框中的[常規]頁面中，選擇[目標]下拉列框中的[源數據庫]選項。

[選擇用于還原的備份集]列表框中將顯示用于還原TM數據庫的可用備份集，備份集，且[目標數據庫]將會被同時設置為TM

在“還原數據庫”的窗口中選擇“選項”頁,在“還原選項”選項區域中選擇“覆蓋現有數據庫”復選框，單擊確定。還原操作完成后，打開“TM”數據庫，可以看到TM數據庫已進行還原

4．實驗思考：

①SQL Server 2005物理數據庫包含了哪能幾種類型的文件以及它們的作用？ SQL Server2005數據庫具有三種類型的文件：

主數據文件

主數據文件是數據庫的起點，指向數據庫中的其他文件。每個數據庫都有一個主數據文件。主數據文件的推薦文件擴展名是.mdf。它將數據存儲在表和索引中，包含數據庫的啟動信息，還包括一些系統表，這些表記載數據庫對象及其他文件的位置信息。

次要數據文件

除主數據文件之外的所有其他數據文件都是次要數據文件。某些數據庫可能不含有任何次要數據文件。次要數據文件的推薦文件擴展名是.ndf。

日志文件

SQL Server具有事物功能，日志文件包含著用于恢復數據庫的所有日志信息。每個數據庫必須至少有一個日志文件，當然也可以有多個，建立數據庫時，SQL Server會自動建立數據庫的日志文件。日志文件的推薦文件擴展名是.ldf。②數據庫備份與轉儲包含那些原理？

備份（backup）實際就是數據的副本，備份的目的是為了防止不可預料的數據丟 失和應用錯誤。

轉儲（restore）是指當數據文件或控制文件出現損壞時，將已備份的副本文件還原到原數據庫的過程

數據庫轉存實現熱備份恢復功能。通過修改初始化文件里的路徑，實現轉存。啟 動到MOUNT狀態下，將所有數據文件和日志文件通過ALTER DATABASE RENAME FILE '原始路徑/名稱' TO '當前路徑/名稱';然后將數據庫打開，重建TEMP tablespace即可。數據轉儲是數據庫恢復中采用的基本技術。

所謂轉儲即dba定期地將數據庫復制到磁帶或另一個磁盤上保存起來的過程。當數據庫遭到破壞后可以將后備副本重新裝入，將數據庫恢復到轉儲時的狀態。靜態轉儲：在系統中無運行事務時進行的轉儲操作。靜態轉儲簡單，但必須等待 正運行的用戶事務結束才能進行。同樣，新的事務必須等待轉儲結束才能執行。顯然，這會降低數據庫的可用性。動態轉儲：指轉儲期間允許對數據庫進行存取或修改。動態轉儲可克服靜態 轉儲的缺點，它不用等待正在運行的用戶事務結束，也不會影響新事務的運行。但是，轉儲結束時后援副本上的數據并不能保證正確有效。因為轉儲期間運行的 事務可能修改了某些數據，使得后援副本上的數據不是數據庫的一致版本。為此，必須把轉儲期間各事務對數據庫的修改活動登記下來，建立日志文件（log file）。這樣，后援副本加上日志文件就能得到數據庫某一時刻的正確狀態。轉儲還可以分為海量轉儲和增量轉儲兩種方式。海量轉儲是指每次轉儲全部數據庫。

增量轉儲則指每次只轉儲上一次轉儲后更新過的數據。

從恢復角度看，使用海量轉儲得到的后備副本進行恢復一般說來更簡單些。但如 果數據庫很大，事務處理又十分頻繁，則增量轉儲方式更實用更有效。

③如果數據或日志文件非空不能刪除，查找SQL Server中縮小文件大小的方法。

在SQL Server中，所有對數據庫執行的更新操作都會記錄在數據庫的事務日志文件中，除非將數據庫設為可自動收縮的或手動的對數據庫進行了收縮，否則事務日志文件將一直增長，直到達到事先設定的日志文件增長上限或用盡所有可用的磁盤空間。如果當前的數據庫文件或日志文件過大，可以使用以下兩個命令對其進行收縮： DBCC SHRINKDATABASE：收縮指定數據庫的所有數據和日志文件的大小 DBCC SHRINKFILE：收縮數據庫的某個指定數據或日志文件的大小

這兩個命令可以釋放數據庫中的空閑空間，并將數據庫或指定的數據庫文件收縮到指定的大小，但收縮后的數據文件或日志文件的大小不會小于文件中現存的有效數據所占空間的大小。在使用以上命令收縮日志文件的時候需要注意，已寫入數據庫但未被截斷的事務日志記錄是 不會被收縮的，因為雖然這部分日志記錄的信息已經寫入數據庫文件，但在使用事務日志備份進行數據庫還 原的時候，還將用到其中的信息。

對于使用簡單恢復模型的數據庫，事務日志會在每次處理檢查點（CheckPoint）時自動被截 斷。

對于使用完全恢復模型或大容量日志記錄恢復模型的數據庫，事務日志只有在執行日志備份（BACKUP LOG）時才會被截斷，這時事務日志中記錄的信息被寫入事務日志備份文件，而它們所占用的這部分空間被標記為可用（即被截斷）。

截斷事務日志并不會使日志文件變小，但可以將其中的部分空間釋放供以后寫入新的日志記錄使用。若要減少日志文件的物理大小，則要使用上面提到的

在執行BACKUP LOG語句的時候，還可以使用WITH NO_LOG（或WITH TRUNCATE_ONLY，含 義相同）參數，這時并不真正備份事務日志，而只是截斷事務日志中的非活動部分

（這和普通的BACKUP LOG語句作用相同）。這適合于剩余磁盤空間不夠進行事務日志備份或不打算保 留事務日志中的非活動部分用于數據庫恢復的情況。避免事務日志文件增長過快以致用盡所有磁盤空間的現象發生

一種辦法是將數據庫設為使用簡單恢復模型，這樣可以使SQL Server周期性的自動截斷事務日志的

非活動部分，并回收其占用的空間供以后寫入事務日志記錄使用。但這將使數據庫無法利用事務日志備份還原到即時點，降低了數據庫的可靠性，因此一般不應用于生產型數據庫。對于生產型數據庫，推薦的做法是使用完全恢復模型，并定期進行數據庫的完全備份和事務日志備份。例 如每周執行一次完全備份，每天執行一次事務日志備份，這可以通過SQL Server企業管理器中的數據庫 維護計劃向導很方便的實現（一般可以設為在每天夜里業務不繁忙的某個時刻自動執行備份）。

通過定期執行數據庫的事務日志備份，可以避免日志文件的迅速增大，而使其保持一個比較穩定的大小。

雖然數據庫備份文件也會占用很多磁盤空間，但隨時可以將這些文件移到其他磁盤上或在不需要它們的時候將其刪除，而且可以在出現故障或誤操作的時候方便的進行數據庫的還原。

由于數據文件的大小是隨數據庫中數據量的增長而增長的，數據庫中已刪除的數據所占的空間可以供新插 入的數據使用；而在定期執行了事務日志的備份后，我們可以將日志文件的大小控制在一個比較合理的范 圍。因此，一般不需要對數據庫進行收縮，也不推薦將數據庫設為自動收縮模式。

減小事務日志文件大小的

首先在該數據庫中執行CHECKPOINT命令，然后將該數據庫分離（Detach），再將與其對應的數據庫日志 文件（.ldf文件）改名或刪除或移動到其他目錄下，然后執行sp_attach_single_file_db存儲過程或在企業管 理器中重新將其附加（Attach）。由于找不到原來的日志文件，SQL Server將自動為該數據庫建立一個大 小只有504K的日志文件。但這種方法必須暫時將數據庫脫機，因此一般不適宜在生產環境中使用。

1、建議首先備份數據庫（但不是必需的）：

BACKUP DATABASE database_name TO backup_device

2、備份事務日志：

BACKUP LOG database_name TO backup_device 如果不需要當前事務日志中的記錄進行數據庫還原或沒有足夠的空間進行事務日志備份的 的話，也可僅執行以下命令截斷事務日志： BACKUP LOG database_name WITH NO_LOG

3、收縮事務日志文件：

DBCC SHRINKFILE(log_file_name)其中log_file_name是事務日志文件的邏輯名稱，可以在企業管理器中數據庫屬性的“事務 日志”頁中看到（如Northwind數據庫的默認事務日志文件邏輯名稱為Northwind_log）。

4、如果日志文件仍然較大的話，可以嘗試重復執行一次 BACKUP LOG WITH NO_LOG和DBCC SHRINKFILE命令。

5、如果這時仍沒有明顯的效果，請執行DBCC OPENTRAN(database_name)檢查當前數據庫中是否存在長時間未提交的活動事務。有必要的話，可以斷開這些連接并重新嘗試截斷事務日志和收縮日志文件。

6、事務日志文件收縮完成后，建議立即執行一次數據庫的完全備份并根據實際需要制定適當的數據庫備份計劃。④思考后續實驗過程中，你計劃采用哪種方法備份自己的數據庫實驗操作結果？并說明為什么采用該方法。

答：數據庫備份有四種類型

? 完全備份 ? 事務日志備份 ? 差異備份

? 文件備份 采用完全備份

完全備份就是指對某一個時間點上的所有數據(包含用戶表、系統表、索引、視圖和存儲過程等所有數據庫對象)或應用進行的一個完全拷貝。

實際應用中就是用一盤磁帶對整個系統進行完全備份，包括其中的系統和所有數據。

這種備份方式最大的好處就是只要用一盤磁帶，就可以恢復丟失的數據。因此大大加快了系統或數據的恢復時間。

第四篇：數據庫原理及技術實驗報告-實驗10

《數據庫原理及技術》實驗報告

姓名： 莫鴻斌

學號：201601030137

班級：2016級計算機科學與技術

實驗日期： 2018-3-9

一、實驗項目

數據庫備份與恢復、數據的導入導出

二、實驗目的

1.掌握SQL Server Management Studio的運用； 2.掌握SQL Server 2012服務器配置； 3.查詢編輯器的使用。

三、實驗內容

1、把數據庫school備份到”D:dbbackschooldata.bak”中，把數據庫school的日志備份到”D:dbbackschoollog.bak”中；

2、刪除數據庫school；

3、利用前面的備份還原數據庫school。

4、把數據庫school中的數據庫導出到excel的工資簿bookl中。

5、新建數據庫newschool，然后把book1中的數據庫導入其中。

四、實驗環境

安裝有SQL Server2008的PC一臺。

五、實驗步驟及結果

1、把數據庫school備份到”D:dbbackschooldata.bak”中，把數據庫school的日志備份到”D:dbbackschoollog.bak”中；

2、刪除數據庫school；

3、利用前面的備份還原數據庫school。

4、把數據庫school中的數據庫導出到excel的工資簿bookl中。

5、新建數據庫newschool，然后把book1中的數據庫導入其中。

六、結論及思考

第五篇：數據庫原理及技術實驗報告-實驗11

《數據庫原理及技術》實驗報告

姓名：莫鴻斌

學號：201601030137

班級：2016級計算機科學與技術

實驗日期： 2018-3-23

一、實驗項目

T-SQL創建修改數據庫和表

二、實驗目的

1.掌握SQL Server Management Studio的運用； 2.掌握T-SQL語句的執行操作；

3.如何使用T-SQL創建修改數據庫和表。

三、實驗內容

1.使用T-SQL創建數據庫factory，要求將數據庫文件factory_data.MDF存放在E:data下面，其文件初始大小5MB，自動按5MB增長，將事務日志文件factory_log.LDF存放在E:data目錄下，其文件大小按1MB自動增長。

2.使用T-SQL在數據庫factory下創建如下表： 職工表(職工號（int），姓名（char(10)）,性別(char(2)),出生日期（datetime），黨員否（bit），參加工作時間（datetime），部門號（int）)，其中職工號作為主鍵。部門表（部門號（int），部門名（char（10）），其中部門號作為主鍵。工資表（職工號（int），發放年份（int），發放月份（int），工資（decimal（6,1））），其中職工號、年份、月份作為主鍵。

3.使用T-SQL建立創建的表之間的參照完整性規則。4.在上述表中輸入數據，每個表至少10條記錄。

四、實驗環境

安裝有SQL Server2008的PC一臺。

五、實驗步驟及結果

1.使用T-SQL創建數據庫factory

2．將數據庫文件factory_data.MDF存放在E:data下面，其文件初始大小5MB，自動按5MB增長，將事務日志文件factory_log.LDF存放在E:data目錄下，其文件大小按1MB自動增長。

4.使用T-SQL在數據庫factory下創建 職工表(職工號（int），姓名（char(10)）,性別(char(2)),出生日期（datetime），黨員否（bit），參加工作時間（datetime），部門號（int）)，其中職工號作為主鍵。部門表（部門號（int），部門名（char（10）），其中部門號作為主鍵。工資表（職工號（int），發放年份（int），發放月份（int），工資（decimal（6,1））），其中職工號、年份、月份作為主鍵。

5.使用T-SQL建立創建的表之間的參照完整性規則

5.在上述表中輸入數據，每個表至少10條記錄

六、結論及思考