How to create a view in MySQL

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

Recommend

Detailed explanation of the three major front-end technologies of React, Angular and Vue

Table of contents 1. React Basic Usage Notable Fe...

Disadvantages and reasonable use of MySQL database index

Table of contents Proper use of indexes 1. Disadv...

Inspiring Design Examples of Glossy and Shiny Website Design

This collection showcases a number of outstanding ...

Detailed deployment steps for MySQL MHA high availability configuration and failover

Table of contents 1. Introduction to MHA 1. What ...

Detailed explanation of the difference and application of CSS3 filter:drop-shadow filter and box-shadow

To use standard CSS3 to achieve the shadow effect...

Front-end AI cutting tips (experience)

AI image cutting needs to be coordinated with PS....

How to set up scheduled backup tasks in Linux centos

Implementation Preparation # Need to back up the ...

Vue3 compilation process-source code analysis

Preface: Vue3 has been released for a long time. ...

Pure CSS to achieve cloudy weather icon effect

Effect The effect is as follows ​ Implementation ...

Detailed analysis of classic JavaScript recursion case questions

Table of contents What is recursion and how does ...

Collection of 25 fonts used in famous website logos

This article collects the fonts used in the logos...

Detailed explanation of MySQL event modification events (ALTER EVENT), disabling events (DISABLE), enabling events (ENABLE), event renaming and database event migration operations

This article uses examples to describe the operat...

How to implement a lucky wheel game in WeChat applet

I mainly introduce how to develop a lucky wheel g...

Vue implements tree table through element tree control

Table of contents Implementation effect diagram I...

Implement a simple search engine based on MySQL

Table of contents Implementing a search engine ba...