» MySQL Views » Managing Views

Managing Views in MySQL

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

Displaying a View

MySQL provides the SHOW statement to allow you to display a view definition. Here is the syntax of SHOW statement:

SHOW CREATE VIEW [<em>database_name</em>].[<em>view_ name</em>];

First we create a simple view thatdisplays 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

In order to display the view you can use SHOW statements as follows:

SHOW CREATE VIEW organization

You can also display the view by using any plain text editor such as notepad to open the view file definition in the database folder. For example, to open the view organization you can find its file location at \data\database_name\organization.frm

Modifying a View

Once a view is defined, you can modify it by using the ALTER VIEW statement. The syntax of ALTER VIEW statement is similar to the CREATE VIEW statement except the CREATE keyword is replaced by ALTER keyword.

ALTER
 [ALGORITHM =  {MERGE | TEMPTABLE | UNDEFINED}]
  VIEW [database_name].  [view_name]
   AS 
 [SELECT  statement]

To modify the view organization, for example, we want add field email of employee to the view, we can do it as follows:

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

Now we can see the change in the view organization by querying data from it:

SELECT * FROM Organization

Removing a View

Once view created, you can remove it by using DROP VIEW statement. Here is the syntax of DROP VIEW statement:

DROP VIEW [IF EXISTS] [database_name].[view_name]

IF EXISTS is the optional part which allows you to check whether the view is exist or not to help you to avoid remove a view which does not exists.

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

DROP VIEW IF EXISTS organization

In MySQL each time when you alter or remove a view, a back up of the view is copied to the /database_name/arc/  folder. Therefore in case you are accidentally altering or removing a view you can get a back up from there.

In this tutorial, you've been learning how to manage View in MySQL including displaying, altering and removing Views from database catalog.

Search