数据库系统实训——实验四——视图

第一部分：样例库的应用
1） 利用视图简化复杂的查询（联结）

CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact,prod_id 
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;

SELECT cust_name,cust_contact FROM productcustomers 
WHERE prod_id= 'TNT2';

2） 用视图可以重新格式化检索出的数据

SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')
AS vend_title
FROM vendors
ORDER BY vend_name;

CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') 
As vend_title
FROM vendors
ORDER BY vend_name ;
SELECT *
FROM vendorlocations;

3） 用视图过滤不必要的数据

CREATE VIEW customeremaillist AS
SELECT cust_id,cust_name,cust_email FROM customers
WHERE cust_email IS NOT NULL;
SELECT *
FROM customeremaillist;

4） 使用视图结合计算字段进行统计分析

SELECT prod_id,quantity,item_price,quantity*item_price 
AS expanded_price
FROM orderitems
WHERE order_num=20005;

CREATE VIEW orderitemsexpanded AS
SELECT order_num,prod_id,quantity,item_price,quantity *
item_price 
as expanded_price
FROM orderitems;
SELECT * FROM orderitemsexpanded
WHERE order_num=20005;

第二部分：所选课题数据库的应用
1）

CREATE VIEW product AS
SELECT cust_name, cust_contact,prod_id 
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;
SELECT cust_name,cust_contact FROM product
WHERE prod_id= 'FB';

2）

CREATE VIEW vendorsss AS
SELECT Concat('(',RTrim(vend_zip),')') 
As vend_title
FROM vendors
ORDER BY vend_name ;
SELECT *
FROM vendorsss;

3）

CREATE VIEW customerssss AS
SELECT cust_id,cust_name,cust_email FROM customers
WHERE cust_email IS NULL;
SELECT *
FROM customerssss;

4）

CREATE VIEW orderitemssss AS
SELECT order_num,prod_id,quantity,item_price,quantity * item_price 
as expanded_price
FROM orderitems;
SELECT *
FROM orderitemssss
WHERE order_num=20009;