What should I do if mysql cannot create a view?

The solution to the problem that mysql cannot create a view: Use the CREATE VIEW statement to create the view, the code is [CREATE VIEW AS], where [] is specified The name of the view. 语句>

More related free learning recommendations: mysql tutorial(Video)

Solution to the problem that mysql cannot create a view:

Basic syntax

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

The syntax format is as follows:

CREATE VIEW <视图名> AS <SELECT语句>

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 SELECT statements in create views:

• In addition to CREATE VIEW permissions, the user also has relevant permissions on the underlying tables and other views involved in the operation.

• 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)

[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)

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)

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 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)

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.

Query view

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

Views are mainly used for querying in the following aspects:

• 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 views, the syntax is as follows:

DESCRIBE view name;

[Example 4]

Through DESCRIBE The definition of the statement view 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 detailed content of What should I do if mysql cannot create a view?. For more information, please follow other related articles on the PHP Chinese website!