MySQL View-Memos
例1：CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL,
`c2` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
root@localhost >create view v1 as select * from t1;
root@localhost >show create view v1\G
*************************** 1. row ***************************
View: v1
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2` from `t1`
character_set_client: utf8
collation_connection: utf8_general_ci
root@localhost >create algorithm=merge view v2 as select * from t1;
root@localhost >show create view v2\G
*************************** 1. row ***************************
View: v2
Create View: CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2` from `t1`
character_set_client: utf8
collation_connection: utf8_general_ci
#可以看到视图没有真实的数据文件
# ls
db.opt t1.frmt1.ibdv1.frmv2.frm
# cat v1.frm
TYPE=VIEW
query=select `testdb`.`t1`.`c1` AS `c1`,`testdb`.`t1`.`c2` AS `c2` from `testdb`.`t1`
md5=bc42610c419d2ba99157095ecfc2bd85
updatable=1
algorithm=0
definer_user=root
definer_host=localhost
suid=2
with_check_option=0
timestamp=2017-04-08 13:21:05
create-version=1
source=select * from t1
client_cs_name=utf8
connection_cl_name=utf8_general_ci
view_body_utf8=select `testdb`.`t1`.`c1` AS `c1`,`testdb`.`t1`.`c2` AS `c2` from `testdb`.`t1`
# cat v2.frm
TYPE=VIEW
query=select `testdb`.`t1`.`c1` AS `c1`,`testdb`.`t1`.`c2` AS `c2` from `testdb`.`t1`
md5=bc42610c419d2ba99157095ecfc2bd85
updatable=1
algorithm=2
definer_user=root
definer_host=localhost
suid=2
with_check_option=0
timestamp=2017-04-08 13:21:05
create-version=1
source=select * from t1
client_cs_name=utf8
connection_cl_name=utf8_general_ci
view_body_utf8=select `testdb`.`t1`.`c1` AS `c1`,`testdb`.`t1`.`c2` AS `c2` from `testdb`.`t1`
例2：修改表，视图中数据也会修改：
root@localhost >insert into t1 values(2,'bbb');
Query OK, 1 row affected (0.00 sec)
root@localhost >select * from v1;
+------+------+
| c1 | c2 |
+------+------+
| 1 | aaa|
| 2 | bbb|
+------+------+
root@localhost >select * from v2;
+------+------+
| c1 | c2 |
+------+------+
| 1 | aaa|
| 2 | bbb|
+------+------+
例3：更新视图，源表数据也会更新：
root@localhost >delete from v1 where c1=2;
root@localhost >select * from v1;
+------+------+
| c1 | c2 |
+------+------+
| 1 | aaa|
+------+------+
root@localhost >select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| 1 | aaa|
+------+------+
例4：在视图上建立视图：
root@localhost >select v1.c1,v2.c2 from v1 join v2 on v1.c1=v2.c1;
+------+------+
| c1 | c2 |
+------+------+
| 1 | aaa|
+------+------+
root@localhost >create view v12 as select v1.c1,v2.c2 from v1 join v2 on v1.c1=v2.c1;
root@localhost >select * from v12;
+------+------+
| c1 | c2 |
+------+------+
| 1 | aaa|
+------+------+
页:
[1]