mysql无法通过视图表修改原表_MySQL视图表创建与修改

1、创建

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

AS []

例子：

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;

修改

ALTER [] VIEW [.]< name> [()]

AS []

例子：

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”，运行语句即可。