mysql中对于视图的使用

create [or replace][algorithm={undefined| merge| temptable}]
view view_name[(column_list)]
as select _statement[ with[cascaded | local] check option]
新建视图
如果使用or replace 则可以替换已有的视图。
mysql> select * from test1;
+----+-----------------+----------+
| id | street | No |
+----+-----------------+----------+
| 1 | jian she road | 8 number |
| 2 | bin he road | 9 number |
| 6 | shen nan da dao | NULL |
+----+-----------------+----------+

mysql> select * from test2;
+----+---------+------------------------+
| id | test_id | address |
+----+---------+------------------------+
| 1 | 1 | jian she road,8 number |
| 2 | 2 | bin he road,9 number |
+----+---------+------------------------+

现在需要给 test1 和test2 的表关系建立一个视图
mysql> create algorithm=merge view `test`
-> as
-> select test1.street,test1.No,test2.address
-> from test1,test2
-> where test1.id=test2.test_id;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from test;
+---------------+----------+------------------------+
| street | No | address |
+---------------+----------+------------------------+
| jian she road | 8 number | jian she road,8 number |
| bin he road | 9 number | bin he road,9 number |
+---------------+----------+------------------------+
达到想要的结果！