文章目录
- 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 cjoin 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 optionupdate invoices_with_balance
set payment_total = invoice_total
where invoice_id = 3;
运行结果:报错,无法删除id为3的行。
视图的其他优点
简化查询
减小数据库设计改动的影响
-
视图为数据库提供了一种抽象化,这种抽象化减少了变动带来的影响。
-
如果所有的查询都是基于视图,不会受基础表改动的影响
使用视图限制基础表访问
- 可加强数据安全性