视图
视图是从一个表或几个基本表导出的表,是一个虚表,不存在数据冗余
基于视图的操作定义视图(DDL)建立、定义该视图的新视图、删除、查询(DML)、受限更新(DML)
作用:视图能够简化用户操作
视图使用户能以多种角度看待同一数据
视图对重构数据库提供了一定的逻辑独立性
视图能够对机密数据提供安全保护:对不同的用户定义不同的视图,是每个用户只能看到他有权限看到的视图
视图的创建
mysql>
mysql> create view ss as
-> select student.id,student.name,class.cid,class.classname from student,class
-> ;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from ss;
+----------+------+-----+-----------+
| id | name | cid | classname |
+----------+------+-----+-----------+
| 16033001 | xiao | 01 | yiban |
| 16033002 | wang | 01 | yiban |
| 16033001 | xiao | 02 | erban |
| 16033002 | wang | 02 | erban |
+----------+------+-----+-----------+
4 rows in set (0.00 sec)
查看已经创建的视图 在表中显示,但不是表
mysql> show tables;
+----------------+
| Tables_in_sql1 |
+----------------+
| class |
| ss //视图 |
| student |
+----------------+
视图的修改,本质上是对构建视图的表的修改
mysql> insert into ss(cid,classname)
-> value ('04','siban');
Query OK, 1 row affected (0.00 sec)
mysql> select * from ss;
+----------+------+-----+-----------+
| id | name | cid | classname |
+----------+------+-----+-----------+
| 16033001 | xiao | 01 | yiban |
| 16033002 | wang | 01 | yiban |
| 16033001 | xiao | 02 | erban |
| 16033002 | wang | 02 | erban |
| 16033001 | xiao | 03 | sanban |
| 16033002 | wang | 03 | sanban |
| 16033001 | xiao | 04 | siban |
| 16033002 | wang | 04 | siban |
+----------+------+-----+-----------+
8 rows in set (0.00 sec)
错误案例;不能通过视图一次性修改多张表(union 等 除外)
mysql> insert into ss()
-> value ('16033003','li','04','siban');
ERROR 1394 (HY000): Can not insert into join view 'sql1.ss' without fields list
删除视图
mysql> drop view ss;
Query OK, 0 rows affected (0.00 sec)