USE yiibaidb;
CREATE VIEW organization AS
SELECT
CONCAT(E.lastname, E.firstname) AS Employee,
CONCAT(M.lastname, M.firstname) AS Manager
FROM
employees AS E
INNER JOIN
employees AS M ON M.employeeNumber = E.ReportsTo
ORDER BY Manager;
MySQL提供两个语句，允许您修改现有视图：ALTER VIEW和CREATE OR REPLACE VIEW 。
使用ALTER VIEW语句修改视图
创建视图后，可以使用ALTER VIEW语句修改视图。
ALTER VIEW语句的语法类似于CREATE VIEW语句，除了CREATE关键字被ALTER关键字替换外，其它都一样。
ALTER
[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]
VIEW [database_name]. [view_name]
AS
[SELECT statement]
以下语句通过添加email列来演示如何修改organization视图。
ALTER VIEW organization
AS
SELECT CONCAT(E.lastname,E.firstname) AS Employee,
E.email AS employeeEmail,
CONCAT(M.lastname,M.firstname) AS Manager
FROM employees AS E
INNER JOIN employees AS M
ON M.employeeNumber = E.ReportsTo
ORDER BY Manager;
要验证更改，可以从organization视图中查询数据：
SELECT
*
FROM
Organization;
执行上面查询语句，得到以下结果 -
使用CREATE OR REPLACE VIEW语句修改视图
除ALTER VIEW语句外，还可以使用CREATE OR REPLACE VIEW语句来创建或替换现有视图。如果一个视图已经存在，MySQL只会修改视图。如果视图不存在，MySQL将创建一个新的视图。
以下语句使用CREATE OR REPLACE VIEW语法根据employees表创建一个名称为v_contacts的视图：
CREATE OR REPLACE VIEW v_contacts AS
SELECT
firstName, lastName, extension, email
FROM
employees;
-- 查询视图数据
SELECT * FROM v_contacts;
