mysql视图（二）

mysql> alter ALGORITHM=merge view v_dept2 as select * from dept;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_Dept2;
+--------+-------+-----------+--------+
| deptno | dname | loc       | amount |
+--------+-------+-----------+--------+
|     11 | bts   | xuchang   |      0 |
|     12 | MSC   | zhengzhou |      0 |
+--------+-------+-----------+--------+
2 rows in set (0.00 sec)
由于该视图没有导出列，故可以插入数据：
mysql> insert into v_dept2 values(13,'SW','shanghai',100);
Query OK, 1 row affected (0.00 sec)
mysql> select * from dept;（基表数据也被插入）
+--------+-------+-----------+--------+
| deptno | dname | loc       | amount |
+--------+-------+-----------+--------+
|     11 | bts   | xuchang   |      0 |
|     12 | MSC   | zhengzhou |      0 |
|     13 | SW    | shanghai  |    100 |
+--------+-------+-----------+--------+
3 rows in set (0.02 sec)
mysql> select * from v_dept2;（视图数据插入）
+--------+-------+-----------+--------+
| deptno | dname | loc       | amount |
+--------+-------+-----------+--------+
|     11 | bts   | xuchang   |      0 |
|     12 | MSC   | zhengzhou |      0 |
|     13 | SW    | shanghai  |    100 |
+--------+-------+-----------+--------+
3 rows in set (0.00 sec)
更改视图含有导出列，则通过视图不能插入数据：
mysql> alter view v_dept2 as select *,amount/2 from dept;（amount/2是导出列）
Query OK, 0 rows affected (0.01 sec)
mysql> select * from v_dept2;
+--------+-------+-----------+--------+----------+
| deptno | dname | loc       | amount | amount/2 |
+--------+-------+-----------+--------+----------+
|     11 | bts   | xuchang   |     90 |  45.0000 |
|     12 | MSC   | zhengzhou |     80 |  40.0000 |
|     13 | SW    | shanghai  |    100 |  50.0000 |
+--------+-------+-----------+--------+----------+
3 rows in set (0.02 sec)
 插入一条数据是不允许的：
mysql> insert into v_dept2(deptno,dname,loc,amount) values(14,'HW','puyang',110);
ERROR 1471 (HY000): The target table v_dept2 of the INSERT is not insertable-into
但是如果简单更新非导出列是可以的：
mysql> update v_dept2 set amount=110 where deptno=13;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from dept;（基表数据更新成功）
+--------+-------+-----------+--------+
| deptno | dname | loc       | amount |
+--------+-------+-----------+--------+
|     11 | bts   | xuchang   |     90 |
|     12 | MSC   | zhengzhou |     80 |
|     13 | SW    | shanghai  |    110 |
+--------+-------+-----------+--------+
3 rows in set (0.01 sec)
mysql> select * from v_dept2;（视图数据更新成功）
+--------+-------+-----------+--------+----------+
| deptno | dname | loc       | amount | amount/2 |
+--------+-------+-----------+--------+----------+
|     11 | bts   | xuchang   |     90 |  45.0000 |
|     12 | MSC   | zhengzhou |     80 |  40.0000 |
|     13 | SW    | shanghai  |    110 |  55.0000 |
+--------+-------+-----------+--------+----------+
3 rows in set (0.00 sec)
With check option的用法：
（with check option对于没有where条件的视图不起作用的）
mysql> alter view v_dept2 as select * from dept where dname='SW' with check option;
Query OK, 0 rows affected (0.00 sec)（只限于插入dname是SW的记录）
mysql> insert into v_dept2 values(15,'SW','beijing',20);（插入成功）
Query OK, 1 row affected (0.00 sec)
mysql> select * from dept;
+--------+-------+-----------+--------+
| deptno | dname | loc       | amount |
+--------+-------+-----------+--------+
|     11 | bts   | xuchang   |     90 |
|     12 | MSC   | zhengzhou |     80 |
|     13 | SW    | shanghai  |     60 |
|     14 | SW    | nanjing   |     65 |
|     15 | SW    | beijing   |     20 |
+--------+-------+-----------+--------+
5 rows in set (0.00 sec)
mysql> insert into v_dept2 values(15,'BSC','nanjing',65);（插入失败）
ERROR 1369 (HY000): CHECK OPTION failed 'temp.v_dept2'
mysql> delete from v_dept2 where deptno=15;（有没有with check option，不影响删除操作）
Query OK, 1 row affected (0.00 sec)
mysql> update v_dept2 set dname='HW' where deptno=13;（更新成非SW的 失败）
ERROR 1369 (HY000): CHECK OPTION failed 'temp.v_dept2'
对于with check option用法，总结如下：
通过有with check option选项的视图操作基表(只是面对单表，对连接多表的视图正在寻找答案)，有以下结论： 插入后的数据，通过视图能够查询出来就符合WITH CHECK OPTION 否则就不符合；
首先视图只操作它可以查询出来的数据，对于它查询不出的数据，即使基表有，也不可以通过视图来操作。
1.对于update,有with check option，要保证update后，数据要被视图查询出来
2.对于delete,有无with check option都一样
4.对于insert,有with check option，要保证insert后，数据要被视图查询出来
对于没有where 子句的视图，使用with check option是多余的
WITH LOCAL/cascade CHECK OPTION的用法：
在关于可更新视图的WITH CHECK OPTION子句中，当视图是根据另一个视图定义的时，LOCAL和CASCADED关键字决定了检查测试的范围。LOCAL关键字对CHECK OPTION进行了限制，使其仅作用在定义的视图上，CASCADED会对将进行评估的基表进行检查。如果未给定任一关键字，默认值为CASCADED。
mysql> select * from test;
+----+------+
| id | aa   |
+----+------+
|  1 |   12 |
|  2 |    4 |
|  3 |   44 |
|  4 |   25 |
|  5 |   26 |
|  6 |    8 |
|  7 |   15 |
+----+------+
7 rows in set (0.00 sec)
mysql> create view v1 as select * from test where aa<40 with check option;（视图v1）
Query OK, 0 rows affected (0.02 sec)
mysql> select * from v1;
+----+------+
| id | aa   |
+----+------+
|  1 |   12 |
|  2 |    4 |
|  4 |   25 |
|  5 |   26 |
|  6 |    8 |
|  7 |   15 |
+----+------+
6 rows in set (0.00 sec)
mysql> create view v2 as select * from v1 where aa>10 with local check option;
Query OK, 0 rows affected (0.00 sec)
mysql> create view v3 as select * from v1 where aa>10 with cascaded check option;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into v2 values(null,50);（通过视图v2插入50，只检查插入的数据是否满足v2
的条件aa>10,成功插入）
Query OK, 1 row affected (0.00 sec)
mysql> insert into v3 values(null,50);（通过视图v3插入50，不仅检查是否满足V3的条件aa>10，还要检查是否满足v1的条件aa<40）插入失败
ERROR 1369 (HY000): CHECK OPTION failed 'temp.v3'
但是，虽然通过视图v2插入成功，v2中并没有50这条数据，test基表中有50这条数据，直接插入到基表了
mysql> select * from v2;
+----+------+
| id | aa   |
+----+------+
|  1 |   12 |
|  4 |   25 |
|  5 |   26 |
|  7 |   15 |
+----+------+
4 rows in set (0.00 sec)
mysql> select * from test;
+----+------+
| id | aa   |
+----+------+
|  1 |   12 |
|  2 |    4 |
|  3 |   44 |
|  4 |   25 |
|  5 |   26 |
|  6 |    8 |
|  7 |   15 |
|  8 |   50 |
+----+------+
8 rows in set (0.00 sec)