Shulou(Shulou.com)06/01 Report--

The solution that mysql can't create a view? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

The solution to mysql's inability to create a view: use the CREATE VIEW statement to create the view with the code [CREATE VIEW AS

The solution to mysql's inability to create a view:

Basic grammar

You can use CREATE VIEW statements to create views.

The syntax format is as follows:

CREATE VIEW AS

The syntax is explained as follows.

Specifies the name of the view The name must be unique in the database and cannot have the same name as other tables or views.

Specifies the SELECT statement that creates the view, which can be used to query multiple underlying tables or source views There are the following restrictions on the assignment of SELECT statements in the creation view:

In addition to CREATE VIEW permissions, users also have permissions related to the underlying tables and other views involved in the operation.

SELECT statements cannot refer to system or user variables.

The SELECT statement cannot contain subqueries in the FROM clause.

SELECT statements cannot reference preprocessing 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 defines the reference. You can use the CHECK TABLE statement to check the view definition for such problems.

ORDER BY statements are allowed in the view definition, but if you select from a specific view that uses its own ORDER BY statement, the ORDER BY in the view definition is 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 you modify 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 data table.

View the data for 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 | 2015 | 09-10 | 2 | Green | 3 | 23 | F | 2016-10-22 | | 3 | Henry | 2 | 23 | M | 2015-05-31 | 4 | Jane | 1 | 22 | F | 2016-12-20 | 5 | Jim | 1 | 24 | M | 2016-01-15 | 6 | John | 2 | | | 21 | M | 2015 | 11-11 | 7 | Lily | 6 | 22 | F | 2016-02-26 | 8 | Susan | 4 | 23 | F | 2015-10-01 | 9 | Thomas | 3 | 22 | M | 2016-06-07 | 10 | Tom | 4 | 23 | M | 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, and enter the SQL statement and the execution result as shown below.

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 | 2015 | 09-10 | 2 | Green | 3 | 23 | F | 2016-10-22 | | 3 | Henry | 2 | 23 | M | 2015-05-31 | 4 | Jane | 1 | 22 | F | 2016-12-20 | 5 | Jim | 1 | 24 | M | 2016-01-15 | 6 | John | 2 | | | 21 | M | 2015 | 11-11 | 7 | Lily | 6 | 22 | F | 2016-02-26 | 8 | Susan | 4 | 23 | F | 2015-10-01 | 9 | Thomas | 3 | 22 | M | 2016-06-07 | 10 | Tom | 4 | 23 | M | 2016-08-05 | +-+ | -+ 10 rows in set (0.04 sec)

By default, the view created is the same as the fields of the base table, or you can create the view by specifying the name of the view field.

[example 2]

Create a view named v_students_info on the tb_students_info table, and enter the SQL statement and the execution result as shown below.

Mysql > CREATE VIEW v_students_info-> (mysql > FROM tb_students_info;Query OK) > 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 * date +-+ | s_id | s_name | d_id | s_age | s_sex | s_height | s_date | + | 1 | 24 | M | 2016 | 01-15 | | 6 | John | 2 | 21 | M | 2015 | 11-11 | 7 | Lily | 6 | 22 | F | 2016-02-26 | 8 | Susan | 4 | 23 | F | 2015 | 10-01 | 9 | Thomas | 3 | 22 | M | 2016-06-07 | 10 | Tom | 4 | 23 | M | 2016-08-05 | + -+ 10 rows in set (0.01 sec)

As you can see, 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 the data in the actual table, thus ensuring the security of the database.

Create a view based on multiple tables

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

[example 3]

Create a view v_students_info on table tb_student_info and table tb_departments, and enter the SQL statement and the execution result as shown below.

Mysql > CREATE VIEW v_students_info-> (mysql > FROM tb_students_info;Query OK) > 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 * date +-+ | s_id | s_name | d_id | s_age | s_sex | s_height | s_date | + | 1 | 24 | M | 2016 | 01-15 | | 6 | John | 2 | 21 | M | 2015 | 11-11 | 7 | Lily | 6 | 22 | F | 2016-02-26 | 8 | Susan | 4 | 23 | F | 2015 | 10-01 | 9 | Thomas | 3 | 22 | M | 2016-06-07 | 10 | Tom | 4 | 23 | M | 2016-08-05 | + -+ 10 rows in set (0.01 sec)

With this view, the data in the basic table can be well protected. The view contains the s_id, s_name, and dept_name,s_id fields corresponding 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 SELECT statements to query the data in the view, just like querying the data table, using the syntax and querying the data of the underlying table.

Views used for queries are mainly used in the following aspects:

Reformat the retrieved data using the view.

Use views to simplify complex table joins.

Use views to filter data.

DESCRIBE can be used to view views with the following syntax:

DESCRIBE View name

[example 4]

View the definition of the view v_students_info through the DESCRIBE statement. The SQL statement entered and the execution result are shown below.

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: in general, DESCRIBE can be simplified to DESC, and the execution result of entering this command is the same as typing DESCRIBE.

Thank you for reading! After reading the above, do you have a general idea of the solution that mysql can't create views? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.