欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql视图之创建视图等实验讲解

程序员文章站 2022-08-31 18:04:08
6.5 mysql 视图 6.5.1 创建视图 create view viewName as select * from srcTableName...
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)