旧博文备份http://blog.uedao.com/blog/?p=20

1、创建

CREATE [OR REPLACE] [<algorithm attributes>] VIEW [database.]< name> [(<columns>)]

AS <SELECT statement> [<check options>]

例子：

CREATE VIEW `view_articles`
AS
SELECT
    a.id AS id,
    a.title AS title,
    a.content AS content,
    t.name AS tagname,
    u.firstname AS "username"
FROM `articles` a
    LEFT JOIN `tags` t
        ON a.tag_id = t.id
    LEFT JOIN `users` u
        ON a.user_id = u.id
ORDER BY a.posttime DESC;

2、修改

ALTER [<algorithm attributes>] VIEW [<database>.]< name> [(<columns>)]

AS<SELECT statement> [<check options>]

例子：

ALTER VIEW `view_articles`
AS
SELECT
    a.id AS id,
    a.title AS title,
    a.content AS content,
    a.posttime AS posttime,
    t.name AS tagname,
    CONCAT(u.firstname,' ',u.lastname) AS "username"
FROM `articles` a
    LEFT JOIN `tags` t
        ON a.tag_id = t.id
    LEFT JOIN `users` u
        ON a.user_id = u.id
ORDER BY a.posttime DESC;

修改已经建立好的视图表，最简单的方法就是在phpMyAdmin导出视图表的SQL，然后修改开头的“CREATE”（后面的

ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER

等不用管，保留它）为ALTER，运行语句即可。

参考文章：

http://database.51cto.com/art/201005/200526.htm
http://www.sqlinfo.net/mysql/mysql_VIEWS_the_basics.php
http://www.java2s.com/Tutorial/MySQL/0180View/Catalog0180View.htm