Managing Views in MySQL : Showing mysql view definition
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 VIEW statement that helps you show view definition. The following is the syntax of the SHOW CREATE VIEW statement:
| 1 | 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 VIEWkeywords.
Let’s create a view for the demonstration.
First, we create a simple view against the employees table that displays the company’s organization structure:
| 1234 5 6 7 8 | CREATE VIEW organizationASSELECT 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:
| 1 | 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 organization view 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 VIEW statement. The syntax of theALTER VIEW statement is similar to the CREATE VIEW statement except the CREATE keyword is replaced by the ALTER keyword.
| 1234 5 | ALTER[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]VIEW [database_name]. [view_name]AS [SELECT statement] |
The following query modifies the organization view by adding an addition email field.
| 1234 5 6 7 8 9 | ALTER VIEW organizationASSELECT 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 organization view:
| 1 | 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 VIEW statement:
| 1 | 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 organization view, you can use the DROP VIEWstatement as follows:
| 1 | 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.
Posts to Review:
How to convert all tables from MyISAM into InnoDB? 
MYSQL Partitioning : What is and HOW To ? ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log Resolved : mysql replica : Relay log read failure: Could not parse relay log event entry.