T N T

美食博主

0%

$5 视图相关操作

Posted on Edited on In

本节记录视图的增删改查

查看视图

  1. 与查看表内容操作一模一样

创建视图

  1. CREATE VIEW view AS SELECT ... FROM table WHERE ... 
    1
    2
    3
    4
    (root@localhost) [book]> create or replace view book_view
        -> as select * from book where b_author='郭敬明'
        -> with check option;       WITCH CHECK OPTION对于今后视图的修改都要检查
    Query OK, 0 rows affected (0.02 sec)

删除视图

  1. DROP VIEW view 
    1
    2
    (root@localhost) [book]> drop view book_view;
    Query OK, 0 rows affected (0.02 sec)

修改视图

  1. 插入数据与插入表内容一模一样, 写入视图的同时写入真实表 

    1
    2
    3
    (root@localhost) [book]> insert into book_view values
        -> (null, '梦里花落知多少', '郭敬明', 39.2);        作者必须为郭敬明,否则插入失败
    Query OK, 1 row affected (0.05 sec)

  2. ALTER VIEW view AS SELECT ... FROM table WHERE ... 

    1
    2
    3
    4
    (root@localhost) [book]> alter view book_view
        -> as select * from book where b_author='余华'
        -> with check option;
    Query OK, 0 rows affected (0.01 sec)

  3. 修改数据与修改表内容一模一样, 写入视图的同时写入真实表

  4. 删除数据与修改表内容一模一样, 写入视图的同时写入真实表