How to create a view in mysql? What is a create statement?-Mysql Tutorial-php.cn

In mysql, you can use the "CREATE VIEW" statement to create a view. The syntax format is "CREATE VIEW AS "; where " " cannot contain subqueries in the FROM clause and cannot reference prepared statement parameters. 语句> 语句>

(Recommended tutorial: mysql video tutorial )

Creating a view refers to establishing a view on an existing MySQL database table. Views can be created in one table or in multiple tables.

Basic syntax

You can use the CREATE VIEW statement to create a view.

The syntax format is as follows:

CREATE VIEW AS Copy after login The syntax description is as follows.

: Specify the name of the view. The name must be unique in the database and cannot have the same name as another table or view.

: Specify the SELECT statement to create a view, which can be used to query multiple base tables or source views.

There are the following restrictions on the specification of the SELECT statement in the created view:

In addition to having the CREATE VIEW permission, the user also has the authority involved in the operation related permissions on the underlying tables and other views.

The SELECT statement cannot reference system or user variables.

The SELECT statement cannot contain a subquery in the FROM clause.

The SELECT statement cannot reference prepared statement parameters.

The table or view referenced in the view definition must exist. However, after you create the view, you can delete the table or view that the definition references. You can use the CHECK TABLE statement to check the view definition for such problems.

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

The TEMPORARY table (temporary table) cannot be referenced in the view definition, and the TEMPORARY view 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 a view on a single data 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) Copy after login [Example 1] Create a view named view_students_info on the tb_students_info table. The input SQL statement and execution results 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) Copy after login By default, the created view has the same fields as the basic 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 input SQL statement and 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) Copy after login Copy after login As you can see, the field names in the two views view_students_info and v_students_info 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 the data in the actual table, thus ensuring the security of the database.

Create views based on multiple tables

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

[Example 3] Create the view v_students_info on the table tb_student_info and the table tb_departments. The input SQL statement and 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) Copy after login Copy after login The data in the basic table can be well protected through this view. 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.