Shulou(Shulou.com)05/31 Report--

MySQL view is like, I believe that many inexperienced people do not know what to do, so this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

1. View definition and brief introduction

A view is a visual table based on the result set of SQL statements, that is, a view is a virtual table that can contain all or part of the records of the table, or can be created by one or more tables. By using the view, you don't have to see all the data in the data table, but just want to get the data you need. When we create a view, we actually execute the SELECT statement in the database, and the SELECT statement contains field names, functions, and operators to display the data to the user.

The data of the view depends on the data in the original table, so the data of the original table changes, so the data of the displayed view will also change, such as inserting data into the data table, then when you view the view, you will find that the same data has been inserted into the view. Views are actually made up of tables in the form of predefined queries.

two。 How to create and use views

Standard syntax for creating views:

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = user] [SQL SECURITY {DEFINER | INVOKER}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

Grammar interpretation:

1) OR REPLACE: replaces an existing view. If the view does not exist, CREATE OR REPLACE VIEW is the same as CREATE VIEW.

2) ALGORITHM: indicates the view selection algorithm. The default algorithm is UNDEFINED (undefined): MySQL automatically selects the algorithm to be used; merge merge; temptable temporary table. Generally, this parameter is not explicitly specified.

3) DEFINER: indicates who is the creator or definer of the view. If this option is not specified, the user who created the view is the definer.

4) SQL SECURITY:SQL security. Default is DEFINER.

5) select_statement: represents the select statement, which can be selected from the base table or other views.

6) WITH CHECK OPTION: indicates that the view guarantees constraints when it is updated. The default is CASCADED.

In fact, when we create a view every day, we do not need to specify each parameter. In general, it is recommended to create a view like this:

Create view [(column_list)] as select statement with check option

Here are a few concrete creation examples:

# single table view mysql > create view v_F_players (number, name, gender, phone)-> as-> select PLAYERNO,NAME,SEX,PHONENO from PLAYERS-> where SEX='F'-> with check option;Query OK, 0 rows affected (0.00 sec) mysql > desc v_F_players +-+-+ | Field | Type | Null | Key | Default | Extra | +-+ | No. | | int (11) | NO | | NULL | | name | char (15) | NO | | NULL | | gender | char (1) | NO | | NULL | | phone | char (13) | YES | | NULL | | +-- | -- + 4 rows in set (0.00 sec) mysql > select * from v_F_players +-+ | number | name | gender | phone | +-+ | 8 | Newcastle | F | | 458458 | 27 | Collins | F | 079-234857 | | 28 | Collins | F | 010-659599 | Moorman | F | 079-987571 | | Bailey | F | 010-548745 | +-+ 5 rows in set (0.02 sec) # Multi-table view Mysql > create view v_match-> as-> select a.PLAYERNO A.NAMEMagneMechnology WONJ LOSTL C. TEAMNOLON c.DIVISION-> from-> PLAYERS aMECHERICES bMIT TEAMS c-> where a.PLAYERNO=b.PLAYERNO and b.TEAMNO=c.TEAMNO Query OK, 0 rows affected (0.03 sec) mysql > select * from v_match +-+ | PLAYERNO | NAME | MATCHNO | WON | LOST | TEAMNO | DIVISION | + -+ | 6 | Parmenter | 1 | 3 | 1 | first | | 44 | Baker | 4 | 3 | 2 | first | | 83 | Hope | 5 | 0 | 3 | 1 | first | 112 | Bailey | 12 | 1 | 3 | 2 | second | 8 | Newcastle | 13 | 0 | 3 | 2 | second | +-+ 5 rows in set (0.04 sec)

The view is used the same as the underlying table, for example, we can use select from view_name or select from view_name where... The view can filter out the data we don't need and replace the related column names with our custom column names. The view acts as an access interface, no matter how complex the table structure and table name of the base table are. In general, the view is only used for query, and the view itself has no data, so the dml operations on the view are ultimately reflected in the base table. If you perform delete, update and insert operations on the view, the original table will also be updated, the original table of the drop view will not change, and the view cannot be truncate. But in general, we should avoid updating the view, and the dml operation can update the original table directly.

3. View-related best practices

The following is a brief introduction to the advantages of the following view, through which we can easily summarize the applicable scenarios of the view.

1) simple: users who use views do not need to care about the structure, association conditions and filter conditions of the corresponding tables at all, which is already the result set of filtered composite conditions for users.

2) Security: users who use the view can only access the result set they are allowed to query, and the permission management of the table cannot be limited to a row or column, but it can be easily implemented through the view.

3) data independence: once the structure of the view is determined, you can shield the impact of changes in the table structure on the user, and adding columns to the source table has no effect on the view; if the source table modifies the column name, it can be solved by modifying the view without affecting the visitors.

All in all, most of the use of views is to ensure data security and improve query efficiency. For example, we often use the association results of several tables, then we can use views to deal with them, or third-party programs need to call our business library, and we can create views to query third-party programs as needed.

In the process of daily use and maintenance of the view, I summed up the following practices for reference:

It is recommended to have a uniform prefix for view naming, such as starting with v or view, for ease of identification.

SQL SECURITY uses the default DEFINER, which indicates the permissions of the person who has defined the view to query the view.

The view definer recommends the use of relevant program users.

Views should not be associated with too many tables, resulting in data redundancy.

Query the view with conditions, and it is not recommended to query all the data every time.

View migration should note that there is a user who defines the view in the new environment.

Do not update the data in the view directly, the view is only a query.

After reading the above, have you mastered the method of MySQL view? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.