第9章 视图view

第9章 视图view

9.1 视图对象的创建、删除

mysql> create table dept2 as select * from dept;   
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dept2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> create view dept2_view as select * from dept2;   //创建视图
Query OK, 0 rows affected (0.01 sec)

mysql> drop view dept2_view;                            //删除视图
Query OK, 0 rows affected (0.00 sec)

9.2 视图的作用

作用：可以面向视图对象进行增删改查，对视图对象的增删改，会导致原表被操作！：

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

mysql> select * from dept2_view;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> insert into dept2_view(deptno,dname,loc) values(60,'SALES', 'BEIJING');   //面向视图插入数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from dept2_view;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     60 | SALES      | BEIJING  |
+--------+------------+----------+
5 rows in set (0.00 sec