SQL语句（四）视图定义、查询、更新和删除 |
您所在的位置：网站首页 › 写借书条的正确格式 › SQL语句（四）视图定义、查询、更新和删除 |
SQL语句（四）视图定义、查询、更新和删除
|
一、视图定义 CREATE VIEW 视图名 AS 子查询 WITH CHECK OPTION //可以省略 二、视图查询、更新 和基本表的查询语句类似，只是把表名的位置换成视图名就可以 三、视图删除 DROP VIEW 视图名 【CASCADE】//CASCADE为级联删除，可以省略 四、实例 1.创建一个“上海晓莉贸易商行”供应商供应的零件视图V_DLMU_PartSupp1,要求列出供应零件的编号、零件名称、可用数量、零售价格、供应价格和备注等信息。 CREATE VIEW V_DLMU_PartSupp1 AS SELECT part.partkey,part.name,availqty,retailprice,supplycost,part.comment FROM part,supplier,partsupp WHERE part.partkey=partsupp.partkey AND partsupp.suppkey=supplier.suppkey AND supplier.name='上海黎顺服装经营部'; SELECT * FROM V_DLMU_PartSupp1; 2. 创建一个视图V_CustAvgOrder，按照顾客统计平均每个订单的购买金额和零件数量，要求输出顾客编号，姓名，平均购买金额和平均购买零件数量。 CREATE VIEW V_CustAvgOrder(custkey,cname,avgprice,avgquantity) AS SELECT customer.custkey,customer.name,AVG(totalprice),AVG(quantity) FROM customer,orders,lineitem WHERE customer.custkey=orders.custkey AND orders.orderkey=lineitem.orderkey GROUP BY customer.custkey,customer.name; SELECT * FROM V_CustAvgOrder; 3.使用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; SELECT * FROM V_DLMU_PartSupp2; INSERT INTO V_DLMU_PartSupp2 VALUES(3,1,18,20); SELECT * FROM V_DLMU_PartSupp2; UPDATE V_DLMU_PartSupp2 SET supplycost=18 WHERE partkey=2; SELECT * FROM V_DLMU_PartSupp2; DELETE FROM V_DLMU_PartSupp2 WHERE supplycost=20; SELECT * FROM V_DLMU_PartSupp2; 4.创建一个上海黎顺服装经营部供应商供应的零件视图V_DLMU_PartSupp3，要求列出供应零件的编号、可用数量和供应价格等信息。然后通过该视图分别增加、删除和修改一条上海黎顺服装经营部零件供应记录,并比较题目3操作与本次异同。 CREATE VIEW V_DLMU_PartSupp3 AS SELECT partkey,suppkey,availqty,supplycost FROM partsupp WHERE suppkey= ( SELECT suppkey FROM supplier WHERE name='上海黎顺服装经营部' ); SELECT * FROM V_DLMU_PartSupp3; INSERT INTO V_DLMU_PartSupp3 VALUES(3,1,18,20); SELECT * FROM V_DLMU_PartSupp3 ; INSERT INTO V_DLMU_PartSupp3 VALUES(30007,4,18,20); SELECT * FROM partsupp WHERE partkey=30007; UPDATE V_DLMU_PartSupp3 SET supplycost=30 WHERE partkey=2; SELECT * FROM V_DLMU_PartSupp3; DELETE FROM V_DLMU_PartSupp3 WHERE supplycost=20; SELECT * FROM V_DLMU_PartSupp3; 5.验证题目2中定义的视图不可以更新 INSERT INTO V_CustAvgOrder VALUES(100000,'liuhui',20,20000); 6.创建顾客订单明细视图V_CustOrd，要求列出顾客编号、姓名、购买零件数量、金额，然后再该视图的基础上，再创建V_CustAvgOrder视图，然后使用RESTRICT选项删除视图V_CustOrd，观察现象并解释原因。使用CASCADE选项删除视图V_CustOrd，观察现象并检查V_CustAvgOrder是否存在并解释原因。 CREATE VIEW V_CustOrd(custkey,name,quantity,extendedprice) AS SELECT customer.custkey,customer.name,quantity,extendedprice FROM customer,orders,lineitem WHERE customer.custkey=orders.custkey AND orders.orderkey=lineitem.orderkey; SELECT * FROM V_CustOrd; CREATE VIEW V_CustAvgOrder(custkey,name,avgquantity,avgprice) AS SELECT custkey,name,AVG(quantity),AVG(extendedprice) FROM V_CustOrd GROUP BY custkey,name; SELECT * FROM V_CustAvgOrder; DROP VIEW V_CustOrd RESTRICT; DROP VIEW V_CustOrd ;五、参考结果 1.创建该视图成功后查询结果 2.创建该视图成功以及查询该视图的结果
3.对视图V_DLMU_PartSupp2各项操作的结果 3.1创建并查询该视图 3.2执行插入操作并查询插入后的视图结果 3.3执行修改操作并查询修改后的视图结果 3.4执行删除操作并查询删除后的视图结果 4.对视图V_DLMU_PartSupp3各项操作结果 4.1建立成功后查询结果 4.2进行插入操作并查新插入后的视图结果
4.3进行修改操作并查询修改后的视图结果 4.4进行删除操作并查询删除后的视图结果 5.题目2中建立的视图更新操作结果 6. RESTRICT和CASCADE删除 六、注意 1.实例2中创建的视图V_CustAvgOrder不可更新的原因：因为所创建的视图对其属性值进行了计算的其他形式上的改变，而对视图的更改最终表现为对表的更改而表中不存在视图的某一属性，或属性的性质不相同，则无法更改，这是一种视图机制。V_CustAvgOrder定义了平均购买金额和平均购买零件数量，但是原来的表项中并没有，对它们的更改无法对应到基本表上，所以该视图不可以更新。 2. with check option ：在视图上的修改都要符合视图定义时的SELECT语句所指定的限制条件，这样可以确保数据修改后通过视图可以看到修改后的数据。 3. RESTRICT删除和CASCAD删除：视图删除后视图的定义将从数据字典中删除，如果在该视图上还导出了其他视图，删除这些视图需要级联删除，遗憾的是，SQL Server2008并不支持级联删除，不过可以先删除视图 V_CustAvgOrder，然后再删除视图V_CustOrd。 4. 视图的更新：一般行列子集视图是可以更新的，因为它只是去掉了基本表的某些行或者列，并且保留了主码，对视图的更新通过视图消解可以转化为对基本表的更新。
|
【本文地址】