【MySQL】视图：简化查询

文章目录

create view … as创建视图
更改或删除视图
- drop view 删除视图
- replace关键字：更改视图
可更新视图
with check option子句：防止行被删除
视图的其他优点
- 简化查询
- 减小数据库设计改动的影响
- 使用视图限制基础表访问

create view … as创建视图

把常用的一段查询保存为视图，以供在很多地方使用。可以大大简化常用到的查询
把下面这段查询保存为一个视图（作用像一张虚拟表），在后续用到这段查询时，可以直接使用这个视图
- 可以把这个视图和任何有客户id列的表做连接
- 可以使用where、order by等
注意：视图不存储数据，数据存储在表中

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;