SQL语言笔记 第七章 视图 VIEW

使用的是MySQL数据库

很多时候没有直接修改表的权限，因此需要借助视图来修改表

1. 创建视图

• 视图可以看作是一张虚拟表，但视图不存储数据，可以认为视图是存储了选择语句，基础表的内容改变，相关的视图内容也会变。

-- 创建视图
CREATE VIEW sales_by_client AS
-- 未来可能多次使用下面查询的信息，因此可以创建视图保存这段查询
SELECT
c.client_id,
c.name,
SUM(invoice_total) AS total_sales
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id,name;
 
-- 视图可以像表一样查询
SELECT *
FROM sales_by_client
-- 查询sales_by_client视图，结果按照total_sales降序排列
ORDER BY total_sales DESC;
-- 关联其他表查询
-- JOIN clients c USING (client_id)
-- 查询金额大于500的结果
-- WHERE total_sales > 500;

2. 更改或删除视图

-- 更改视图的两种方法
-- 第一种：删除视图并重新创建
-- 删除视图写法 DROP VIEW
-- DROP VIEW sales_by_client;
-- 再创建视图
 
-- 第二种：CREATE OR REPLACE VIEW
-- 推荐下面这种写法，这样不需要先删除视图
CREATE OR REPLACE VIEW sales_by_client AS
SELECT
c.client_id,
c.name,
SUM(invoice_total) AS total_sales
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id,name;

• 如何拿到创建视图的查询？
  – 第一种：将视图另存在sql文件中；（推荐）
  – 第二种：打开视图的编辑模式
  
• MySQL会给表和列名称打上反引号，这是为了防止名称与MySQL关键字发生冲突；
  
• 修改好查询语句，点击右下角的Apply，即可更新视图；
  

3. 可更新视图 - 可以使用INSERT\UPDATE\DELETE语句更新 可更新视图

-- 如果查询语句中没有出现以下这些关键字或函数，则称这个视图为可更新视图
-- DISTINCT
-- 聚合函数
-- GROUP BY / HAVING
-- UNION
 
-- 创建可更新视图
-- 给invoices表添加结余列，且结余>0，并保存为视图
CREATE OR REPLACE VIEW invoices_with_balance AS
SELECT
	invoice_id,
	number,
	client_id,
	invoice_total,
	payment_total,
   -- 结余列balance
	(invoice_total - payment_total) AS balance,
	invoice_date,
	due_date,
	payment_date
FROM invoices
-- WHERE子句不可以使用别名或聚合函数
WHERE (invoice_total - payment_total) > 0;

-- 删除视图中invoice_id为1的发票
DELETE FROM invoice_with_balance
WHERE invoice_id = 1;
 
-- 更新视图中invoice_id为2的发票，到期日期改为后天
UPDATE invoice_with_balance
SET due_date = DATE_ADD(NOW(), INTERVAL 2 DAY)
WHERE invoice_id = 2;
 
-- 视图中插入数据
-- 在视图中插入的记录也会被插入到基础表中，所以视图要与单表的列一致，不多不少，否则插入不成功（试过视图中缺少列，或者有单个基础表的所有列并且还多了其他列，都插入不成功）；
-- 我的理解：将单表所有列都复制给视图，并且视图中只有这一个单表列，可以通过视图向表中插入数据；
-- 插入成功后，其他视图中的结果也会随着基础表的变化而变化；

4. WITH CHECK OPTION 子句

• 添加了WITH CHECK OPTION子句可以限制使视图中记录消失的更新和删除操作；
• 但如果更新视图的操作不会让视图中的这条记录消失，则会更新视图中的这个列，且对应基础表中的列数据也会随之变化。

-- 前提：使用invoices_with_balance视图
UPDATE invoices_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 2
-- 更新付款金额等于发票金额，则此时结余为0；若直接执行上面代码，则视图中将不显示发票id为2的记录；
-- 若此时不希望更新或删除的动作，导致发票不在视图中显示，就在WHERE子句后面加上WITH CHECK OPTION子句并执行；
-- 再执行上面的更新语句，就会得到错误提示“检查视图失败”；
-- *注意：需要在添加了WITH CHECK OPTION子句后，视图才会在执行了可能将行从视图中删除的更新、删除语句后报错；
-- *但如果更新视图的操作不会让视图中的这条记录消失，则会更新视图中的这个列，且对应基础表中的列数据也会随之变化。

5. 视图的优点

1. 简化查询；（简单）
2. 减少数据库设计改动的影响；（复用）
如：发票表中的一些列移到其他表里，其他表的列移到发票表中，此时希望能看到一开始发票中的记录，则此时使用视图就可以在不变动表结构的情况下查询原有表数据；
3. 限制基础表访问；（安全）
如：取消用户直接访问基础表的权限，可以通过使用WHERE子句筛选显示部分数据，保护机密数据的安全；