MySQL学习之第八章 视图

第八章 视图

1. 创建视图——视图的作用很像一张虚拟表，但视图不存储数据，数据存储在表中（类似于函数封装，视图其实就是存了一段查询代码，到用的时候再执行）

use sql_invoicing;
create view clients_balance as
select client_id,
       name,
       sum(i.invoice_total - i.payment_total) as balance
from clients c
         join invoices i using (client_id)
group by client_id, name;

2. 更改或删除视图

   drop view clients_balance; # 删除
   create or replace #无需删除，可执行任意次
   create or replace view clients_balance as
   select client_id,
          name,
          sum(i.invoice_total - i.payment_total) as balance
   from clients c
            join invoices i using (client_id)
   group by client_id, name;
   

3. 可更新视图——没有distinct关键字、聚集函数、group by、having、union子句的视图是可更新视图，可以在insert、update、delete语句中使用

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;
  

• 增——视图有所有基础表中要用到的列时，插入新发票才会生效（比如视图中没有某基础表中定义不能为null的属性，则插入不会生效，因为在基础表中该属性不允许为null，而你在插入时又没有指明其值）

4. with check option子句

• 通过视图更新或删除数据时一些行可能消失

  update invoices_with_balance
  set payment_total = invoice_total # 因为这个视图只select那些(invoice_total - invoices.payment_total) > 0的行
  where invoice_id = 2;
  

• 通过在最后加上with check option可以防止update或delete语句将行从视图中删除——如果执行会导致删除某行的sql语句时，会报错CHECK OPTION failed

  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;
  

• 执行不了，报错CHECK OPTION failed

  update invoices_with_balance
  set payment_total = invoice_total # 因为这个视图只select那些(invoice_total - invoices.payment_total) > 0的行
  where invoice_id = 3;
  

5. 视图的其他优点
   1. 提供了一种抽象化，可以减小数据库设计改动的影响（改表后只要改中间的视图，不用改查询语句）
   2. 限制基础表访问，如删除表的直接访问，使用户只能通过视图更新数据，他们就无法修改某列的值或修改非视图返回的行，从而加强数据安全性