Preface:

In MySQL, views are probably one of the most commonly used database objects. So do you know the difference between a view and a table? Do you know what to look for when creating and using views? If you want to know more about views, this article will explain the concept, creation and use of views in detail.

1. View definition and brief introduction

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

The data in the view depends on the data in the original table. If the data in the original table is changed, the data in the displayed view will also be changed. For example, when inserting data into the data table, the same data will be inserted into the view. A view is actually made up of tables in the form of predefined queries.

2. View creation and use methods

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]Copy the code

Grammar interpretation:

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

The default ALGORITHM is UNDEFINED. MySQL automatically selects the ALGORITHM to use. The merge merger; 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, which defaults to DEFINER.

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

6) WITH CHECK OPTION: The view is CASCADED by default.

We do not need to specify every parameter when creating a view. In general, it is recommended to create a view like this:

[(column_list)] as SELECT statement with check option;Copy the code

Here are a few examples:

# single table view mysql > create view v_F_players (number, NAME, gender, phone) - > the 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 | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + | | number 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; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- name number + | | | | | phone sex + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 8 Newcastle | F | 070-458458 | | 27 | Collins | F | 079-234857 | | 28 | Collins | F | 010-659599 | | 104 | Moorman | F | 079-987571 | | 112 | Bailey | | F 010-548745 | + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 5 rows in the set # (0.02 SEC) More table view mysql > create view v_match - > as - > select Amy polumbo LAYERNO, a.N AME, MATCHNO, WON, LOST, c.T EAMNO, c.D IVISION - > the from - > PLAYERS a,MATCHES b,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 | 1 | first |  | 44 | Baker | 4 | 3 | 2 | 1 | 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 the set (0.04 SEC)Copy the code

A view is used just like an 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 relevant column names with our own custom column names. The view acts as an access interface, regardless of the complex table structure and table name of the base table. Generally, views are only used for query and have no data. Therefore, ALL DML operations on a view are reflected in the base table. After delete, UPDATE, and INSERT operations are performed on a view, the original table is also updated. However, in general we want to avoid updating the view; DML operations can update the original table directly.

3. View-related best practices

The following is a brief introduction to the advantages of views, from which we can easily summarize the application scenarios of views.

1) Simple: Users using the view do not need to care about the structure, association conditions and filtering conditions of the corresponding table, which is already a result set of filtered compound conditions for users.

2) Security: Users using views can only access the result set they are allowed to query. Permission management on tables is not limited to a row or a column, but can be easily achieved through views.

3) Data independence: once the structure of the view is determined, the influence of the table structure change on users can be shielded, and the addition of columns in the source table has no influence on the view; If the source table changes the column name, it can be resolved by modifying the view without affecting visitors.

In summary, most of the use of views is for data security and query efficiency. For example, we often use the association results of several tables, so we can use the view to deal with, or the third party program needs to call our business library, we can create the view for the third party program to query as needed.

For daily use and maintenance of views, I summarize the following practices for reference:

  • It is recommended to name views with a uniform prefix, such as vOr the viewAt the beginning, easy to identify.
  • SQL SECURITY uses the default DEFINER, which indicates the view DEFINER’s permission to query the view.
  • The view definer recommends using relevant application users.
  • Do not associate a view with too many tables, resulting in data redundancy.
  • Conditions must be attached when querying views. It is not recommended to query all data every time.
  • View migration note that there is a definer user for the view in the new environment.
  • Do not update the data in the view directly; the view only does queries.

Conclusion:

Views are often used in MySQL. This article introduces the concept of views and how to create them. By extension, it also describes the usage scenarios and advantages of views. You may not feel the difference between a view and a table when using it. In fact, there are a lot of ways to do this. It is recommended that views only be used for queries. Hope you found this article helpful.