数据库实验四 视图实验

1.实验目的

熟悉 SQL 语言有关视图的操作，能够熟练使用 SQL 语句来创建需要的视图，定义数据库外模式，并能使用所创建的视图实现数据管理。

2.实验内容和要求

针对 tpch 数据库或自建数据库 test 创建视图和带 with check option 的视图，并验证 with check option 选项的有效性。理解和掌握视图消解执行原理，掌握可更新视图和不可更新视图的区别。

3.实验步骤

（1）创建一个“河北省华信集团”供应商供应的零件视图 Viewpart1,要求列出供应商编号、供应商名称、零件编号、零件名称、可用数量、零售价格和供应价格。

源代码：

CREATE VIEW Viewpart1 (
			suppkey,
			sname,
			partkey,
			pname,
			availqty,
			retailprice,
			supplycost
			) AS SELECT
			supplier.suppkey,
			supplier. NAME,
			part.partkey,
			part. NAME,
			partsupp.availqty,
			part.retailprice,
			partsupp.supplycost
		FROM
			supplier,
			part,
			partsupp
		WHERE
			supplier. NAME = '河北省华信集团' WITH CHECK OPTION;

结果截图：

（2）创建一个视图 ViewCust1,按顾客统计 2020 年订单的购买总金额和零件总数量，要求输出顾客编号、姓名、购买总金额和购买零件总数量。

源代码：

CREATE VIEW viewcust1 (
	custkey,
	NAME,
	price_sum,
	num_sum
) AS SELECT
	customer.custkey,
	customer. NAME,
	SUM(totalprice),
	SUM(quantity)
FROM
	customer,
	lineitem,
	orders
WHERE
	YEAR (orderdate) = '2020'
AND orders.orderkey = lineitem.orderkey
AND orders.custkey = customer.custkey
GROUP BY
	Custkey;

结果截图：

 1）针对刚创建的视图，插入一条记录，看看是否能通过验证，并说明原因。

源代码：

INSERT INTO viewcust1
VALUES
	(
		'131209',
		'蓝天',
		201312.09,
		1111
	);

结果：不能通过验证，原因是该视图的部分字段来自聚集函数，不能更新

2）针对刚创建的视图，查询购买总金额超过 10 万的顾客编号、姓名、购买总金额和购买零件总数量。

源代码：

SELECT *
		FROM viewcust1
		WHERE price_sum > 100000;

查询结果：

3）创建一个“河北钢铁集团有限公司”供应商供应的零件视图Viewpart2,要求列出供应零件的编号、供应商编号、可用数量和供应价格。

源代码：

CREATE VIEW viewpart2 AS SELECT
	partkey,
	suppkey,
	availqty,
	supplycost
FROM
	partsupp
WHERE
	suppkey IN (
		SELECT
			suppkey
		FROM
			supplier
		WHERE
			NAME = '河北钢铁集团有限公司'
	);

 结果截图：

然后通过该视图分别增加、删除和修改一条“河北钢铁集团有限公司”零件供应记录。

增加源代码：

INSERT INTO viewpart2
VALUES(1209,24706,129,1209);

 结果截图：

修改源代码：

UPDATE viewpart2
SET supplycost = 1312.09
WHERE partkey = 1209;

 结果截图：

删除源代码：

DELETE
FROM viewpart2
WHERE partkey = 1209;

 结果截图：

（4）使用 with check option 创建一个“河北钢铁集团有限公司”供应商供应的零件视图 Viewpart3,要求列出供应零件的编号、供应商编号、可用数量和供应价格。

然后通过该视图分别增加、删除和修改一条“河北钢铁集团有限公司”零件供应记录，验证该视图是否可更新，并比较和“（3）”的实验结果有无异同。

源代码：
 

CREATE VIEW viewpart3 AS SELECT
	partkey,
	suppkey,
	availqty,
	supplycost
FROM
	partsupp
WHERE
	suppkey IN (
		SELECT
			suppkey
		FROM
			supplier
		WHERE
			NAME = '河北钢铁集团有限公司'
	) WITH CHECK OPTION;

 结果截图：

 增加源代码：

INSERT INTO viewpart2
VALUES(1209,24706,129,1209);

结果截图：

修改源代码：

UPDATE viewpart2
SET supplycost = 1312.09
WHERE partkey = 1209;

 结果截图：

删除源代码：

DELETE
FROM viewpart2
WHERE partkey = 1209;

 结果截图：

（5）创建顾客订购零件明细视图 Viewcust2,要求列出顾客编号、姓名、购买零件数、金额；

源代码：

 

CREATE VIEW viewcust2 (
	custkey,
	NAME,
	price_sum,
	num_sum
) AS SELECT
	customer.custkey,
	customer. NAME,
	SUM(totalprice),
	SUM(quantity)
FROM
	customer,
	lineitem,
	orders
WHERE
orders.orderkey = lineitem.orderkey
AND orders.custkey = customer.custkey
GROUP BY
	custkey;

结果截图：

1）然后在该视图的基础上再创建视图 Viewcust3,列出每个顾客的平均购买零件数和平均金额，显示顾客编号、姓名、平均购买零件数、平均金额；

源代码：

CREATE VIEW viewcust3 (
	custkey,
	NAME,
	price_avg,
	num_avg
) AS SELECT
	viewcust2.custkey,
	viewcust2. NAME,
	(
		viewcust2.price_sum / COUNT(orders.orderkey)
	),
	(
		viewcust2.num_sum / COUNT(lineitem.orderkey)
	)
FROM
	viewcust2,
	orders,
	lineitem
WHERE
	viewcust2.custkey = orders.custkey
AND orders.orderkey = lineitem.orderkey
GROUP BY
	viewcust2.custkey;

结果截图：

 2）删除视图 Viewcust2。

源代码：

DROP VIEW viewcust2;

结果截图：