-- 创建名为productcustomers的视图CREATEVIEW productcustomers ASSELECT cust_name,cust_contact,prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id=order.cust_id
AND orderitems.order_num=order.order_num;
--通过WHERE子句从视图中检索特定数据SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id='TNT2';
ORDER BY可以用在视图中,但如果从该视图检索数据的SELECT语句中也含有ORDER BY,那么该视图中的 ORDER BY将被覆盖。
1.2 用视图重新格式化检索出的数据
--创建视图CREATEVIEW vendorlocationS ASSELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')AS vend_title
FROM vendors
ORDERBY vend_name;--检索数据SELECT*FROM vendorlocation;
1.3 用视图过滤不想要的数据
--创建视图CREATEVIEW customeremaillist ASSELECT cust_id,cust_name,cust_email
FROM customers
WHERE cust_email ISNOTNULL;--检索数据SELECT*FROM customeremaillist;
--创建视图CREATEVIEW orderitemsexpanded ASSELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM orderitems;--检索数据SELECT*FROM orderitemsexpanded
WHERE order_num