Managing Views
Display a view
MySQL supports SHOW statement to allow you to display view definition. Here is the syntax:
SHOW CREATE VIEW [database_name].[view_ name];
First we create a simple view which displays 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 it under location \data\database_name\organization.frm
Altering a view
Once a view is defined, you can alter 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 alter the view organization, for example, we want add the 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:
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 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 it 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.