MySQL 基础知识5 (认识视图)

一、初始视图

1. 定义：视图即虚拟的表, 与包含数据的表不一样, 视图只包含使用时动态检索数据的查询

2. 主要作用：

(1). 重用SQL语句

(2). 简化复杂的SQL操作

(3). 使用表的组成部分而不是整个表

(4). 保护数据 (可以给用户授予表的特定部分的访问权限而不是整个表的访问权限)

(5). 更改数据格式和表示 (视图可返回与底层表的表示和格式不同的数据)

3. 针对视图的操作

(1). 可以执行SQL语句
(2). 过滤和排序数据
(3). 联结其他视图或表
(4). 添加和更新数据 (存在某些限制条件)

4. 视图的规则与限制

(1). 与表名一样, 视图必须唯一命名
(2). 对于可创建的视图数目没有限制
(3). 创建视图必须具有足够的访问权限 (这类权限通常由数据库管理员授权)
(4). 视图可以嵌套, 即可以利用从其他视图中检索数据的查询来构造一个视图
(5). ORDER BY 可以用在视图中，若从当前视图检索数据的SELECT语句中也含有ORDER BY，那么当前视图的ORDER BY将会被覆盖
(6). WHERE子句与WHERE子句：若从视图中检索数据使用了WHERE子句，则两组子句自动组合 (一组在视图中,另一组是传给视图的)
(7). 视图不能索引，也不能有关联的触发器或默认值
(8). 视图可以和表一起使用 (例如，编写一条联结表和视图的SELECT语句)

二、使用视图

1. 创建视图：CREATE VIEW 视图名称 AS SELECT 语句

示例：这里以多表联查检索的结果，创建视图为例

CREATE VIEW productscustomers AS 
SELECT c.cust_name, c.cust_contact, i.prod_id FROM customers c, orders o, orderitems i WHERE
c.cust_id = o.cust_id AND i.order_num = o.order_num;

2. 查看和使用视图

SHOW CREATE VIEW productscustomers;
SELECT cust_name, cust_contact FROM productscustomers WHERE prod_id = 'TNT2';

3. 创建和使用视图重新格式化检索出来的数据

CREATE VIEW vendorslocations AS
SELECT CONCAT(RTRIM(vend_name), '(', RTRIM(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;

SELECT * FROM vendorslocations;

4. 创建并使用视图过滤无效的检索数据

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

SELECT * FROM customersemaillist;

5. 创建并使用视图中的计算字段

CREATE OR REPLACE 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;

6. 更新视图的注意事项

说明：如果MySQL不能正确地确定被更新的基数据, 则不允许更新(包括插入和删除), 以下操作不可更新

(1). 分组 (使用GROUP BY 和 HAVING)

(2). 联结查询

(3). 子查询

(4). 并

(5). 聚集函数 (Min()、Count()、Sum()等函数)

(6). DISTINCT

(7). 导出(计算)列

注意：一般而言，将视图用于数据检索，而非用于数据更新！