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)
上一篇: MySQL的存储过程应用介绍
下一篇: 我们分组玩的游戏 还记得吗