当前位置： 移动技术网 > IT编程>数据库>Mysql > mysql视图之创建视图等实验讲解

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

2018年03月05日  | 移动技术网IT编程  | 我要评论
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)

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网