How to create a view in MySQL

Basic syntax

You can create a view using the CREATE VIEW statement.

The syntax format is as follows:

CREATE VIEW <view name> AS <SELECT statement>

The syntax is described below.

<view name>: Specifies the name of the view. The name must be unique in the database and cannot be the same as any other table or view.
<SELECT statement>: Specifies the SELECT statement for creating a view, which can be used to query multiple base tables or source views.

The following restrictions apply to the specification of the SELECT statement in creating a view:

In addition to the CREATE VIEW privilege, the user also has the privileges on the base tables and other views involved in the operation.
SELECT statements cannot reference system or user variables.
A SELECT statement cannot contain a subquery in the FROM clause.
A SELECT statement cannot reference prepared statement parameters.

Tables or views referenced in a view definition must exist. However, after you create a view, you can drop the tables or views that the definition references. You can use the CHECK TABLE statement to check view definitions for such problems.

ORDER BY statements are allowed in view definitions, but if you select from a specific view and that view uses its own ORDER BY statement, the ORDER BY in the view definition will be ignored.

TEMPORARY tables (temporary tables) cannot be referenced in view definitions, and TEMPORARY views cannot be created.

WITH CHECK OPTION means that when modifying the view, check whether the inserted data meets the conditions set by WHERE.

Create a view based on a single table

MySQL can create views on a single table.

View the data of the tb_students_info table in the test_db database, as shown below.

mysql> SELECT * FROM tb_students_info;
+----+--------+---------+------+------+--------+------------+
| id | name | dept_id | age | sex | height | login_date |
+----+--------+---------+------+------+--------+------------+
| 1 | Dany | 1 | 25 | F | 160 | 2015-09-10 |
| 2 | Green | 3 | 23 | F | 158 | 2016-10-22 |
| 3 | Henry | 2 | 23 | M | 185 | 2015-05-31 |
| 4 | Jane | 1 | 22 | F | 162 | 2016-12-20 |
| 5 | Jim | 1 | 24 | M | 175 | 2016-01-15 |
| 6 | John | 2 | 21 | M | 172 | 2015-11-11 |
| 7 | Lily | 6 | 22 | F | 165 | 2016-02-26 |
| 8 | Susan | 4 | 23 | F | 170 | 2015-10-01 |
| 9 | Thomas | 3 | 22 | M | 178 | 2016-06-07 |
| 10 | Tom | 4 | 23 | M | 165 | 2016-08-05 |
+----+--------+---------+------+------+--------+------------+
10 rows in set (0.00 sec)

【Example 1】

Create a view named view_students_info on the tb_students_info table. The entered SQL statement and execution result are as follows.

mysql> CREATE VIEW view_students_info
  -> AS SELECT * FROM tb_students_info;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM view_students_info;
+----+--------+---------+------+------+--------+------------+
| id | name | dept_id | age | sex | height | login_date |
+----+--------+---------+------+------+--------+------------+
| 1 | Dany | 1 | 25 | F | 160 | 2015-09-10 |
| 2 | Green | 3 | 23 | F | 158 | 2016-10-22 |
| 3 | Henry | 2 | 23 | M | 185 | 2015-05-31 |
| 4 | Jane | 1 | 22 | F | 162 | 2016-12-20 |
| 5 | Jim | 1 | 24 | M | 175 | 2016-01-15 |
| 6 | John | 2 | 21 | M | 172 | 2015-11-11 |
| 7 | Lily | 6 | 22 | F | 165 | 2016-02-26 |
| 8 | Susan | 4 | 23 | F | 170 | 2015-10-01 |
| 9 | Thomas | 3 | 22 | M | 178 | 2016-06-07 |
| 10 | Tom | 4 | 23 | M | 165 | 2016-08-05 |
+----+--------+---------+------+------+--------+------------+
10 rows in set (0.04 sec)

By default, the fields of the created view are the same as those of the base table. You can also create a view by specifying the name of the view field.

【Example 2】

Create a view named v_students_info on the tb_students_info table. The SQL statement entered and the execution result are as follows.

mysql> CREATE VIEW v_students_info
  -> (s_id,s_name,d_id,s_age,s_sex,s_height,s_date)
  -> AS SELECT id,name,dept_id,age,sex,height,login_date
  -> FROM tb_students_info;
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT * FROM v_students_info;
+------+--------+------+-------+-------+----------+------------+
| s_id | s_name | d_id | s_age | s_sex | s_height | s_date |
+------+--------+------+-------+-------+----------+------------+
| 1 | Dany | 1 | 24 | F | 160 | 2015-09-10 |
| 2 | Green | 3 | 23 | F | 158 | 2016-10-22 |
| 3 | Henry | 2 | 23 | M | 185 | 2015-05-31 |
| 4 | Jane | 1 | 22 | F | 162 | 2016-12-20 |
| 5 | Jim | 1 | 24 | M | 175 | 2016-01-15 |
| 6 | John | 2 | 21 | M | 172 | 2015-11-11 |
| 7 | Lily | 6 | 22 | F | 165 | 2016-02-26 |
| 8 | Susan | 4 | 23 | F | 170 | 2015-10-01 |
| 9 | Thomas | 3 | 22 | M | 178 | 2016-06-07 |
| 10 | Tom | 4 | 23 | M | 165 | 2016-08-05 |
+------+--------+------+-------+-------+----------+------------+
10 rows in set (0.01 sec)

You can see that the field names in the view_students_info and v_students_info views are different, but the data is the same. Therefore, when using views, users may not need to understand the structure of the basic table, let alone access the data in the actual table, thus ensuring the security of the database.

Creating a view based on multiple tables

In MySQL, you can also create views on more than two tables using the CREATE VIEW statement.

【Example 3】

Create the view v_students_info on the tables tb_student_info and tb_departments. The SQL statements entered and the execution results are as follows.

mysql> CREATE VIEW v_students_info
  -> (s_id,s_name,d_id,s_age,s_sex,s_height,s_date)
  -> AS SELECT id,name,dept_id,age,sex,height,login_date
  -> FROM tb_students_info;
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT * FROM v_students_info;
+------+--------+------+-------+-------+----------+------------+
| s_id | s_name | d_id | s_age | s_sex | s_height | s_date |
+------+--------+------+-------+-------+----------+------------+
| 1 | Dany | 1 | 24 | F | 160 | 2015-09-10 |
| 2 | Green | 3 | 23 | F | 158 | 2016-10-22 |
| 3 | Henry | 2 | 23 | M | 185 | 2015-05-31 |
| 4 | Jane | 1 | 22 | F | 162 | 2016-12-20 |
| 5 | Jim | 1 | 24 | M | 175 | 2016-01-15 |
| 6 | John | 2 | 21 | M | 172 | 2015-11-11 |
| 7 | Lily | 6 | 22 | F | 165 | 2016-02-26 |
| 8 | Susan | 4 | 23 | F | 170 | 2015-10-01 |
| 9 | Thomas | 3 | 22 | M | 178 | 2016-06-07 |
| 10 | Tom | 4 | 23 | M | 165 | 2016-08-05 |
+------+--------+------+-------+-------+----------+------------+
10 rows in set (0.01 sec)

This view can effectively protect the data in the basic table. The view contains s_id, s_name, and dept_name. The s_id field corresponds to the id field in the tb_students_info table, the s_name field corresponds to the name field in the tb_students_info table, and the dept_name field corresponds to the dept_name field in the tb_departments table.

Query View

Once a view is defined, you can use the SELECT statement to query the data in the view just like querying a data table. The syntax is the same as that for querying data in the base table.

Views are used for querying in the following areas:

Use views to reformat the retrieved data.
Use views to simplify complex table joins.
Use views to filter data.

DESCRIBE can be used to view the view. The syntax is as follows:

DESCRIBE view name;

【Example 4】

Use the DESCRIBE statement to view the definition of the view v_students_info. The input SQL statement and the execution result are as follows.

mysql> DESCRIBE v_students_info;
+----------+---------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+------------+-------+
| s_id | int(11) | NO | | 0 | |
| s_name | varchar(45) | YES | | NULL | |
| d_id | int(11) | YES | | NULL | |
| s_age | int(11) | YES | | NULL | |
| s_sex | enum('M','F') | YES | | NULL | |
| s_height | int(11) | YES | | NULL | |
| s_date | date | YES | | 2016-10-22 | |
+----------+---------------+------+-----+------------+-------+
7 rows in set (0.04 sec)

Note: DESCRIBE can generally be abbreviated as DESC. The execution result of entering this command is the same as entering DESCRIBE.

The above is the details of how to create views in MySQL. For more information about creating views in MySQL, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

Detailed explanation of creating a view (CREATE VIEW) and usage restrictions in MySQL
Detailed analysis of the principles and usage of MySQL views
Detailed explanation of the usage and differences of MySQL views and indexes
A brief discussion on MySql views, triggers and stored procedures
Detailed explanation of MySql view trigger stored procedure
Detailed explanation of the principle and usage of MySQL views
Detailed explanation of MySQL view management view example [add, delete, modify and query operations]
Detailed explanation of how to create an updateable view in MySQL
The difference between Update and select in MySQL for single and multiple tables, and views and temporary tables
mysql three tables connected to create a view
MySQL View Principle Analysis

<<: Solve the problem of VScode configuration remote debugging Linux program

>>: Vant Uploader implements the component of uploading one or more pictures