“金仓集团”供应的所有零件的供应成本价下降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='张三'); 5、 视图
（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 /*在视图V_CustOrd上再创建视图*/ GROUP BY custkey;
DROP VIEW V_CustOrd RESTRICT;
DROP VIEW V_CustOrd CASCADE; 6、 索引
（1）创建唯一索引
在零件表的零件名称字段上创建唯一索引。
CREATE UNIQUE INDEX Idx_part_name ON Part(name);
（2）创建函数索引（对某个属性的函数创建索引，称为函数索引） 在零件表的零件名称字段上创建一个零件名称长度的函数索引。 CREATE INDEX Idx_part_name_fun ON Part(LENGTH(name));
（3）创建复合索引（对两个及两个以上的属性创建索引，称为复合索引） 在零件表的制造商和品牌两个字段上创建一个复合索引。
CREATE UNIQUE INDEX Idx_part_mfgr_brand ON Part(mfgr,brand); （4）*创建聚簇索引
在零件表的制造商字段上创建一个聚簇索引。
CREATE UNIQUE INDEX Idx_part_mfgr ON Part(mfgr); CLUSTER Idx_part_mfgr ON Part; （5）创建Hash索引
零件表的名称字段上创建一个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查询语句执行时是否使用了索引 EXPLAIN SELECT * FROM part WHERE name='零件'; （8）*验证索引效率
创建一个函数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; /*计算并返回查询执行时间，时间单位为毫秒ms*/ END;
/*查看当零件表Part数据模型比较小，并且无索引时的执行时间*/ SELECT TestIndex(‘零件名称’); INSERT INTO Part /*不断倍增零件表的数据，直到50万条记录*/ 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();
四、实验心得
通过本次实验，我知道只有正确理解数据库模式结构，才能正确设计数据库查询。连接查询是数据库sql查询中最重要的查询，连接查询的设计要特别注意，不同的查询表达，其查询执行的性能会有很大差别。正确地设计和执行数据更新语句，确保正确地录入数据和更新数据，才能保证查询的数据正确。当数据更新失败时，一个主要原因是更新数据时违反了完整性约束。
实验项目名称：安全性语言实验 实验学时： 2 同组学生姓名： 孟陈、陈晓雪、季佰军 实验地点： 1318 实验日期： 5.26 实验成绩： 批改教师： 批改时间：