Detailed explanation of the management view example of mysql view [add, delete, modify and search operation] 02/25 Update SLTechnology News&Howtos

Detailed explanation of the management view example of mysql view [add, delete, modify and search operation]

2025-02-25 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

This paper describes the management view operation of mysql view with an example. Share with you for your reference, the details are as follows:

Mysql provides SHOW CREATE VIEW statements for displaying view definitions. Let's take a look at the syntax structure:

SHOW CREATE VIEW [database_name]. [view_ name]

To display the definition of the view, you need to specify the name of the view after the SHOW CREATE VIEW clause. Let's first create a simple view based on the employees table to show the organizational structure of the company, and then demonstrate:

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

Query the data from the above view and get the following results:

To display the definition of the view, use the SHOW CREATE VIEW statement as follows:

SHOW CREATE VIEW organization

We can also use any plain text editor, such as notepad, to display the view definition to open the view definition file in the database folder. For example, to open the organization view definition, you can find your database folder in the data folder under the database folder, and then go into it and find the .frm file by your view name.

Let's try to modify the view through ALTER VIEW and CREATE OR REPLACE VIEW. Let's take a look at the alert view syntax:

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

The following statement demonstrates how to modify the organization view by adding an email column:

ALTER VIEW organization AS SELECT CONCAT (E.lastname.E.firstname) AS Employee, E.email AS employeeEmail, CONCAT (M.lastame.m.firstname) AS Manager FROM employees AS E INNER JOIN employees AS M ON M.employeeNumber = E.ReportsTo ORDER BY Manager

To verify the changes, you can query the data from the organization view. Let's not dwell on it, but let's look at another syntax structure:

CREATE OR REPLACE VIEW v_contacts AS SELECT firstName, lastName, extension, email FROM employees;-- query view data SELECT * FROM v_contacts

We should note that when we change, if a view already exists, mysql will only change the view. If the view does not exist, mysql creates a new view. All right, let's take a look at the results of the above sql implementation:

Suppose we want to add the position (jobtitle) column to the v_contacts view, simply use the following statement:

CREATE OR REPLACE VIEW v_contacts AS SELECT firstName, lastName, extension, email, jobtitle FROM employees;-- query view data SELECT * FROM v_contacts

After executing the above query, you can see that a column of data has been added:

When we're done, let's use the DROP VIEW statement to delete the view. Let's first take a look at the syntax structure:

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

In the above sql, IF EXISTS is an optional clause of the statement, which allows us to check whether the view exists to avoid the error of deleting a view that does not exist. When we're done, let's delete the organization view:

DROP VIEW IF EXISTS organization

We should note that every time a view is modified or deleted, mysql backs up the view definition file to the / database_name/arc/ directory. If we accidentally modify or delete the view, we can get its backup from the / database_name/arc/ folder.

All right, that's all for this record.

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection", "MySQL Database Lock related skills Summary" and "MySQL Common function Summary".

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.