《数据库系统原理》实验报告(4)

“金仓集团”供应的所有零件的供应成本价下降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万条记录*/ SE …… 此处隐藏：3016字，全部文档内容请下载后查看。喜欢就下载吧 ……