第一篇：河北工业大学数据库原理及应用实验实验报告
《数据库原理及应用实验》
姓名：徐毅民学号： 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条记录
六、结论及思考
点击咨询 