MYSQL中视图的用法介绍（代码示例）

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';

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;

create view custmeremaillist AS SELECT cust_id ,cust_name,cust_email from customers where cust_email is not NULL;

SELECT * from custmeremaillist ;

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 WHERE order_num=20005;

SELECT * FROM orderitemsexpanded WHERE order_num=20005;

1. 分组（group by 和 having） 2. 联结 3. 自查询 4. 并 5. 聚合函数（min（）、count（）、sum（）等） 6. Distinct 7. 导出（计算）列