Updatable Views

• 向用户提供一个VIEW，然后用户对相同的数据进行一定的修改和操作。(通过VIEW修改原来的数据)
• Updatable View的使用条件(满足任意一个即可)
  • 不含有MAX() MIN() AVG()之类的aggregate functions
  • 生成VIEW的过程中，并没有使用GROUP BY 或 HAVING
  • SELECT 及 FROM 后不包含subqueries，且WHERE之后的subquery并没有引用FROM后面的VIEW。
  • 生成VIEW的过程中，并未使用UNION、UNION ALL或DISTINCT等操作。
  • FROM之后至少包含一个table或一个updatable view.
  • FROM之后引用多个VIEW时，只能使用INNER JOIN方式对多个Table进行联系。

Updating Simple Views

• example

  CREATE VIEW customer_vw
  (customer_id,
      first_name,
      last_name,
      email)
  AS
      SELECT customer_id, first_name, last_name, concat(substr(email,1,2),'******',substr(email, -4)) email
  FROM customer;
  

  • 当UPDATE view中某行的last_name时，所对应的customer table中该行的last_name也会被修改。
  • 而email列并无法进行UPDATE，因为email列是通过表达式(concat(substr(email,1,2),'******',substr(email, -4)) email)生成的
  • 此外，虽然view是updatable，但是并非insertable，即可以UPDATE VIEW中的内容，但无法新增VIEW的行

Updating Complex Views

• example

  CREATE VIEW customer_details
  AS
  SELECT c.customer_id,
         c.store_id,
         c.first_name,
         c.last_name,
         c.address_id,
         c.active,
         c.create_date,
         a.address,
         ct.city,
         cn.country,
         a.postal_code
  FROM customer c
  INNER JOIN address a
      on c.address_id = a.address_id
  INNER JOIN city ct
      ON ct.city_id = a.city_id
  INNER JOIN country cn
      ON ct.country_id = cn.country_id;
  

  • 对于不同TABLE的数据可以分别进行UPDATE(以表格为单位)，但是不能同时修改多个表格内的不同的field的数据。例如(对以上的view)

    • 有效

    UPDATE customer_details
    SET last_name = 'SMITH-ALLEN', active = 0
    WHERE customer_id = 1;
    
    UPDATE customer_details
    SET address = '999 Mockingbird Lane'
    WHERE  customer_id = 1;
    

    • 无效([HY000][1393] Can not modify more than one base table through a join view 'sakila.customer_details')

    UPDATE customer_details
    SET last_name = 'SMITH-ALLEN',
        active = 0,
        address = '999 Mockingbird Lane'
    WHERE customer_id = 1;
    

  • 可以INSERT INTO其中某一个base table，但无法同时INSERT INTO多个base table