视图

第一部分：样例库的应用

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;

2）

CREATE VIEW vendorlocations AS

SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') As vend_title

FROM vendors

ORDER BY vend_name ;

3）

CREATE VIEW customeremaillist AS

SELECT cust_id,cust_name,cust_email FROM customers

WHERE cust_email IS NOT NULL;

4）

CREATE VIEW orderitemsexpanded AS

SELECT order_num,

prod_id,

quantity,

item_price,

quantity * item_price as expanded_price

FROM orderitems;

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

1）

CREATE VIEW user_order AS

SELECT user.*,count(`order`.id)AS order_num FROM user

LEFT JOIN `order` ON `order`.customer_id = user.id

GROUP BY user.id;

cs