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   |
+---------------+----------+------------------------+
达到想要的结果！