【MySQL】视图：简化查询

create view … as创建视图

• 把常用的一段查询保存为视图，以供在很多地方使用。可以大大简化常用到的查询
• 把下面这段查询保存为一个视图（作用像一张虚拟表），在后续用到这段查询时，可以直接使用这个视图
  • 可以把这个视图和任何有客户id列的表做连接
  • 可以使用where、order by等
• 注意：视图不存储数据，数据还是存储在表中；视图存放的是sql查询语句。视图会随着原数据的变化而自动更新。

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
order by total_sales desc

运行结果：返回了这个视图

• 练习：查询每个客户的结余（发票总额 - 支付总额），将此查询创建为视图

create view see_balance as
select client_id,
       name,
       sum(invoice_total) - sum(payment_total) as balance
from invoices
join clients using (client_id)
group by client_id, name

更改或删除视图

• 最好把视图储存在SQL文件中，并放入源码控制。

drop view 删除视图

drop view sales_by_client

replace关键字：更改视图

• creat 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;

• 如果找不到视图的源码，可以在modify view中对视图做修改
  

可更新视图

• 如果视图中没有以下内容，这个视图就是可更新视图。
  • distinct
  • 聚合函数，如min、max、sum等
  • group by 或 having
  • union
• 可更新视图：可以在上面更新数据，所以可以在以下语句中使用可更新视图
  • insert
  • update
  • delete
• 创建一个有结余列且结余>0的视图
  • 因为这个视图没有上边提到的那些内容，所以这个视图是可更新视图。
  • 可以删除这个视图中id为1的发票。
  • 可以更新id为2的发票的时间，让天数加2

create or replace view invoices_with_balance as
select invoice_id,
       number,
       client_id,
       invoice_total,
       payment_total,
       invoice_total - payment_total as balance,
       invoice_date,
       due_date,
       payment_date
from invoices
where (invoice_total - invoices.payment_total) > 0

delete from invoices_with_balance
where invoice_id = 1

update invoices_with_balance
set due_date = date_add(due_date, interval 2 day)
where invoice_id = 2

with check option子句：防止行被删除

• 更新视图，让id为2号的支付和总额相等，也就是结余=0。执行后发现视图中id为2号的数据被删除了。

update invoices_with_balance
set payment_total = invoice_total
where invoice_id = 2

• 如果不希望update或delete语句将某行从视图中删除，就在创建视图的代码最后面写 with check option
  • 此时让id为3号的支付和总额相等，也就是结余=0时，执行代码会报错，显示”检查视图失败“

create or replace view invoices_with_balance as
select invoice_id,
       number,
       client_id,
       invoice_total,
       payment_total,
       invoice_total - payment_total as balance,
       invoice_date,
       due_date,
       payment_date
from invoices
where (invoice_total - invoices.payment_total) > 0
with check option

update invoices_with_balance
set payment_total = invoice_total
where invoice_id = 3;

运行结果：报错，无法删除id为3的行。

视图的其他优点

简化查询

减小数据库设计改动的影响

• 视图为数据库提供了一种抽象化，这种抽象化减少了变动带来的影响。

• 如果所有的查询都是基于视图，不会受基础表改动的影响

使用视图限制基础表访问

• 可加强数据安全性