# 管理 在本教程中，您将学习如何管理MySQL中的视图，包括显示，修改和删除视图。 ## 查看视图定义 MySQL提供了用于显示视图定义的`SHOW CREATE VIEW`语句。 以下是`SHOW CREATE VIEW`语句的语法： ```sql SHOW CREATE VIEW [database_name].[view_ name]; ``` 要显示视图的定义，需要在`SHOW CREATE VIEW`子句之后指定视图的名称。 为了更好的演示，我们先来[创建一个视图](http://www.yiibai.com/mysql/create-sql-views-mysql.html)。 假设根据`employees`表创建一个简单的视图用来显示公司组织结构： ```sql 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; ``` 从以上视图中查询数据，得到以下结果 - ```sql mysql> SELECT * FROM organization; +------------------+------------------+ | Employee | Manager | +------------------+------------------+ | BondurLoui | BondurGerard | | CastilloPamela | BondurGerard | | JonesBarry | BondurGerard | | HernandezGerard | BondurGerard | .......此处省略了一大波数据....... | KatoYoshimi | NishiMami | | KingTom | PattersonWilliam | | MarshPeter | PattersonWilliam | | FixterAndy | PattersonWilliam | +------------------+------------------+ 24 rows in set ``` 要显示视图的定义，请使用`SHOW CREATE VIEW`语句如下： ```sql SHOW CREATE VIEW organization; ``` 还可以使用任何纯文本编辑器(如记事本)显示视图的定义，以打开数据库文件夹中的视图定义文件。 例如，要打开`organization`视图定义，可以使用以下路径找到视图定义文件：`\data\yiibaidb\organization.frm`。 但是，不应该直接在`.frm`文件中修改视图的定义。 ## 修改视图 MySQL提供两个语句，允许您修改现有视图：`ALTER VIEW`和`CREATE OR REPLACE VIEW` 。 **使用ALTER VIEW语句修改视图** 创建视图后，可以使用`ALTER VIEW`语句修改视图。 `ALTER VIEW`语句的语法类似于`CREATE VIEW`语句，除了`CREATE`关键字被`ALTER`关键字替换外，其它都一样。 ```sql ALTER [ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}] VIEW [database_name]. [view_name] AS [SELECT statement] ``` 以下语句通过添加`email`列来演示如何修改`organization`视图。 ```sql 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`视图中查询数据： ```sql SELECT * FROM Organization; ``` 执行上面查询语句，得到以下结果 - ``` ``` **使用CREATE OR REPLACE VIEW语句修改视图** 除`ALTER VIEW`语句外，还可以使用`CREATE OR REPLACE VIEW`语句来创建或替换现有视图。如果一个视图已经存在，MySQL只会修改视图。如果视图不存在，MySQL将创建一个新的视图。 以下语句使用`CREATE OR REPLACE VIEW`语法根据`employees`表创建一个名称为`v_contacts`的视图： ```sql CREATE OR REPLACE VIEW v_contacts AS SELECT firstName, lastName, extension, email FROM employees; -- 查询视图数据 SELECT * FROM v_contacts; ``` 执行上面查询语句，得到以下结果 - ```sql +-----------+-----------+-----------+--------------------------------+ | firstName | lastName | extension | email | +-----------+-----------+-----------+--------------------------------+ | Diane | Murphy | x5800 | dmurphy@yiibai.com | | Mary | Hill | x4611 | mary.hill@yiibai.com | | Jeff | Firrelli | x9273 | jfirrelli@yiibai.com | | William | Patterson | x4871 | wpatterson@yiibai.com | | Gerard | Bondur | x5408 | gbondur@gmail.com | | Anthony | Bow | x5428 | abow@gmail.com | | Leslie | Jennings | x3291 | ljennings@yiibai.com | .............. 此处省略了一大波数据 .................................. | Martin | Gerard | x2312 | mgerard@gmail.com | | Lily | Bush | x9111 | lilybush@yiiibai.com | | John | Minsu | x9112 | johnminsu@classicmodelcars.com | +-----------+-----------+-----------+--------------------------------+ 25 rows in set ``` 假设您要将职位(`jobtitle`)列添加到`v_contacts`视图中，只需使用以下语句 - ```sql CREATE OR REPLACE VIEW v_contacts AS SELECT firstName, lastName, extension, email, jobtitle FROM employees; -- 查询视图数据 SELECT * FROM v_contacts; ``` 执行上面查询语句后，可以看到添加一列数据 - ```sql +-----------+-----------+-----------+--------------------------------+----------------------+ | firstName | lastName | extension | email | jobtitle | +-----------+-----------+-----------+--------------------------------+----------------------+ | Diane | Murphy | x5800 | dmurphy@yiibai.com | President | | Mary | Hill | x4611 | mary.hill@yiibai.com | VP Sales | | Jeff | Firrelli | x9273 | jfirrelli@yiibai.com | VP Marketing | ................... 此处省略了一大波数据 .................................................... | Yoshimi | Kato | x102 | ykato@gmail.com | Sales Rep | | Martin | Gerard | x2312 | mgerard@gmail.com | Sales Rep | | Lily | Bush | x9111 | lilybush@yiiibai.com | IT Manager | | John | Minsu | x9112 | johnminsu@classicmodelcars.com | SVP Marketing | +-----------+-----------+-----------+--------------------------------+----------------------+ 25 rows in set ``` ## 删除视图 创建视图后，可以使用`DROP VIEW`语句将其删除。下面说明了`DROP VIEW`语句的语法： ```sql DROP VIEW [IF EXISTS] [database_name].[view_name] ``` `IF EXISTS`是语句的可选子句，它允许您检查视图是否存在。它可以避免删除不存在的视图的错误。 例如，如果要删除`organization`视图，可以按如下所示使用`DROP VIEW`语句： ```sql DROP VIEW IF EXISTS organization; ``` 每次修改或删除视图时，MySQL会将视图定义文件备份到`/database_name/arc/`目录中。 如果您意外修改或删除视图，可以从`/database_name/arc/`文件夹获取其备份。 在本教程中，您已经学会了如何管理MySQL中的视图，包括显示，修改和删除视图。 --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://hezhiqiang-book.gitbook.io/mysql/di-wu-zhang/guan-li.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.