Detailed analysis of the principles and usage of MySQL views

Preface:

In MySQL, views are probably one of the most commonly used database objects. So do you know the difference between views and tables? Do you know what to pay attention to when creating and using views? Many people may only have a superficial understanding of views. If you want to learn more about views, please read this article. This article will introduce the concept, creation, and usage of views in detail.

1. View definition and brief introduction

A view is a visualized table based on the result set of an SQL statement. That is, a view is a virtual table that can contain all or part of the records of a table, and can also be created from one or more tables. By using a view, you don't have to see all the data in the data table, but only want to get the data you need. When we create a view, we actually execute a SELECT statement in the database. The SELECT statement 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, so if the data in the original table changes, the data in the displayed view will also change. For example, if you insert data into the data table, when you view the view, you will find that the same data has also been inserted into the view. A view is actually composed of tables in the form of predefined queries.

2. View creation and usage

Create a view standard syntax:

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]

Grammatical interpretation:

1) OR REPLACE: means replacing 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 use; 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 creates the view is the definer.

4) SQL SECURITY: SQL security, the default is DEFINER

5) select_statement: represents a select statement, which can select from base tables or other views.

6) WITH CHECK OPTION: Indicates that the view ensures constraints when updating. The default is CASCADED.

In fact, when we create views daily, we do not need to specify each parameter. In general, it is recommended to create views like this:

create view <view name> [(column_list)]
as select statement with check option;

Here are some specific creation examples:

# Single table view mysql> create view v_F_players(number, name, gender, phone number)
 -> 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;
+--------+-----------+--------+------------+
| ID| Name| Gender| Phone|
+--------+-----------+--------+------------+
| 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 set (0.02 sec)
# Multi-table view mysql> create view v_match
 -> as 
 -> select a.PLAYERNO,a.NAME,MATCHNO,WON,LOST,c.TEAMNO,c.DIVISION
 -> 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 set (0.04 sec)

Views are used in the same way as base tables. For example, we can use select * from view_name or select * from view_name where .... Views can filter out unnecessary data and replace related column names with custom column names. As an access interface, a view does not matter how complex the table structure and table name of the base table are. Generally, views are only used for querying. Views themselves have no data. Therefore, DML operations on views are ultimately reflected in the base table. When a view is deleted, updated, or inserted, the original table is also updated. If a view is dropped, the original table does not change. Views cannot be truncated. But in general we should avoid updating views, and DML operations can directly update the original table.

3. Best practices for views

The following is a brief introduction to the advantages of views. Through these advantages, we can easily summarize the applicable scenarios of views.

1) Simple: Users who use views do not need to care about the structure, join conditions, and filter conditions of the corresponding tables. For users, the result set is already filtered by the composite conditions.

2) Security: Users who use views can only access the result sets they are allowed to query. Permission management for tables cannot be restricted to a certain row or column, but this can be easily achieved through views.

3) Data independence: Once the structure of the view is determined, the impact of table structure changes on users can be shielded. Adding columns to the source table has no effect on the view. If the source table changes the column name, it can be solved by modifying the view without affecting visitors.

In short, most of the time, views are used to ensure data security and improve query efficiency. For example, if we often use the association results of several tables, we can use views to process them. Or if a third-party program needs to call our business library, we can create views on demand for the third-party program to query.

In the process of daily use and maintenance of views, I have summarized the following practices for reference:

• It is recommended that view names start with a unified prefix, such as v_ or view_, for easy identification.
• SQL SECURITY uses the default DEFINER, which means that the view definer has the privileges to query the view.
• The view definer suggests the relevant program user.
• Do not associate too many tables with the view to avoid data redundancy.
• When querying a view, you must provide conditions. It is not recommended to query all data every time.
• When migrating views, please ensure that there is a definer user for the view in the new environment.
• Do not update data in views directly; views are only used for queries.
  

Summarize:

Views are often used in MySQL. This article introduces the concept of views and how to create them. It also describes the usage scenarios and advantages of views. You may not feel the difference between views and tables when using them, but there are actually many tricks involved. It is recommended that views be used only for queries. Following the specifications, views will bring great convenience. I hope this article helps you.

The above is a detailed analysis of the details of MySQL view. For more information about MySQL view, please pay attention to other related articles on 123WORDPRESS.COM!