mysql管理视图_管理mysql的视图

Summary: in this tutorial, you will learn how to manage views in MySQL including displaying, modifying and removing views.

Show view definition in MySQL

MySQL provides the SHOW CREATE VIEWstatement that helps you show view definition. The following is the syntax of the SHOW CREATE VIEW statement:SHOW CREATE VIEW [database_name].[view_ name];

To display the definition of a view, you just need to specify its name after the SHOW CREATE VIEW keywords.

Let’s create a view for the demonstration.

First, we create a simple view against the employeestable that displays the company’s organization structure: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

To display the view’s definition, you use the SHOW CREATE VIEW statement as follows:SHOW CREATE VIEW organization

You can also display the definition of the view by using any plain text editor such as notepad to open the view definition file in the database folder.

For example, to open the organizationview definition, you can find the view definition file with the following path: \data\classicmodels\organization.frm

Modifying views

Once a view is defined, you can modify it by using the ALTER VIEWstatement. The syntax of the ALTER VIEWstatement is similar to the CREATE VIEW statement except the CREATEkeyword is replaced by the ALTERkeyword.ALTER  [ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]   VIEW [database_name]. [view_name]    AS   [SELECT  statement]

The following query modifies the organizationview by adding an addition emailfield.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

To verify the change, you can query data from the organizationview:SELECT * FROM Organization

MySQL drop views

Once a view created, you can remove it by using the DROP VIEW statement. The following illustrates the syntax of the DROP VIEWstatement:DROP VIEW [IF EXISTS] [database_name].[view_name]

The IF EXISTS is the optional element of the statement, which allows you to check whether the view exists or not. It helps you avoid an error of removing a non-existent view.

For example, if you want to remove the organizationview, you can use the DROP VIEW statement as follows:DROP VIEW IF EXISTS organization

Each time you modify or remove a view, MySQL makes a back up of the view definition file to the /database_name/arc/ folder. In case you modify or remove a view by accident, you can get a back up from there.

In this tutorial, you have learned how to manage views in MySQL including displaying, modifying and removing views.

Related Tutorials