使用视图

使用视图

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询

SELECT custname,custcontact
FROM customers,orders,orderitems
WHERE customers.custid=orders.custid
AND orderitems.ordernum=orders.ordernum
AND prod_id=’TNT2’;

现在，假如可以把整个查询包装成一个名为productcustomers的虚拟表，则可以如下轻松地检索出相同的数据：
SELECT custname,custcontact
FROM productcustomers
WHERE prod_id=’TNT2’;

1）
CREATE VIEW productcustomers AS
SELECT custname,custcontact,prod_id
FROM customers,orders,orderitems
WHERE customers.custid=orders.custid
AND orderitems.ordernum=orders.ordernum;

为检索订购了产品TNT2的客户：
SELECT custname,custcontact
FROM productcustomers
WHERE prod_id=’TNT2’;

可以看出，视图极大的简化了复杂SQL语句的使用，利用视图，可一次性编写基础的SQL，然后根据需要多次使用

2）
SELECT Concat(RTrim(vendname)，‘ （’，RTrim(vendcountry)，‘）’）AS vend_title
FROM vendors
ORDER BY vend_name;

CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vendname)，‘ （’，RTrim(vendcountry)，‘）’）AS vend_title
FROM vendors
ORDER BY vend_name;

SELECT *
FROM vendorlocations;

3. CREATE VIEW customeremaillist AS
   SELECT custid,custname,cust_email
   FROM customers
   WHERE cust_email IS NOT NULL;

SELECT *
FROM customeremaillist;

4. SELECT prod_id,
   quantity,
   item_price,
   quantity*itemprice AS expandedprice

FROM orderitems
WHERE order_num=20005;

CREATE VIEW orderitemsexpanded AS
SELECT prod_id,
quantity,
item_price,
quantity*itemprice AS expandedprice
FROM orderitems;

SELECT *
FROM orderitemsexpanded
WHERE order_num=20005;