mysql 视图

6.5 mysql 视图

6.5.1 创建视图

   create view viewName as select * from srcTableName

# 实验一：给student表创建视图

    mysql> create view stu_view as select * from student;
    Query OK, 0 rows affected (0.01 sec)

    mysql> show tables;
    +--------------------+
    | Tables_in_shanTest |
    +--------------------+
    | employee           |
    | employee1          |
    | stu_view           |
    | student            |
    | student1           |
    | student2           |
    | student3           |
    | user1              |
    +--------------------+
    8 rows in set (0.00 sec)

    mysql> select * from stu_view;
    +------+------+--------+
    | id   | name | sex    |
    +------+------+--------+
    |    1 | aa   | female |
    |    2 | bb   | male   |
    | NULL | aa   | female |
    | NULL | aa   | male   |
    +------+------+--------+
    4 rows in set (0.00 sec)

# 实验二：给student表中的指定列创建视图

    mysql> create view stu_view_1 as select id,sex from student where id='2';
    Query OK, 0 rows affected (0.00 sec)

    mysql> show tables;
    +--------------------+
    | Tables_in_shanTest |
    +--------------------+
    | employee           |
    | employee1          |
    | stu_view           |
    | stu_view_1         |
    | student            |
    | student1           |
    | student2           |
    | student3           |
    | user1              |
    +--------------------+
    9 rows in set (0.00 sec)

    mysql> select * from stu_view_1;
    +------+------+
    | id   | sex  |
    +------+------+
    |    2 | male |
    +------+------+
    1 row in set (0.00 sec)

# 实验三：删除student表中的数据，查看视图stu_view的变化

    mysql> select * from student;
    +------+------+--------+
    | id   | name | sex    |
    +------+------+--------+
    |    1 | aa   | female |
    |    2 | bb   | male   |
    | NULL | aa   | female |
    | NULL | aa   | male   |
    +------+------+--------+
    4 rows in set (0.00 sec)

    mysql> delete from student where id is null;
    Query OK, 2 rows affected (0.00 sec)

    mysql> select * from student;
    +------+------+--------+
    | id   | name | sex    |
    +------+------+--------+
    |    1 | aa   | female |
    |    2 | bb   | male   |
    +------+------+--------+
    2 rows in set (0.00 sec)

    mysql> select * from stu_view;
    +------+------+--------+
    | id   | name | sex    |
    +------+------+--------+
    |    1 | aa   | female |
    |    2 | bb   | male   |
    +------+------+--------+
    2 rows in set (0.00 sec)

结论：视图中数据可以根据原表中数据的变化而变化,当原表被删除的时候依赖该表的视图就会出错。

6.5.2 删除视图

    drop view viewName

# 实验一：删除视图stu_view_1

        mysql> drop view stu_view_1;
        Query OK, 0 rows affected (0.00 sec)

        mysql> show tables;
        +--------------------+
        | Tables_in_shanTest |
        +--------------------+
        | employee           |
        | employee1          |
        | stu_view           |
        | student            |
        | student1           |
        | student2           |
        | student3           |
        | user1              |
        +--------------------+
        8 rows in set (0.00 sec)