In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-15 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
This article will explain in detail how to write the sentences about creating views in mysql. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.
In mysql, you can use the "CREATE VIEW" statement to create a view in the syntax format "CREATE VIEW AS"; where "" cannot contain subqueries in the FROM clause and cannot reference preprocessed statement parameters.
To create a view is to create a view on an existing MySQL database table. Views can be created in one table or in multiple tables.
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 | 165,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, and the SQL statement entered and the execution result are 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 | 165,165 | 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 the SQL statement entered and the execution result are 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 | +-- -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 | | 7 | Lily | 6 | 22 | F | 2016-02-26 | 8 | Susan | 4 | 23 | F | 170 | 2015-10- | | 9 | Thomas | 3 | 22 | M | 178 | 2016-06-07 | | 10 | Tom | 4 | 23 | M | 165th | 2016-08-05 | +-+ 10 rows in set (0.01sec)
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 | +-- -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 | | 7 | Lily | 6 | 22 | F | 2016-02-26 | 8 | Susan | 4 | 23 | F | 170 | 2015-10- | | 9 | Thomas | 3 | 22 | M | 178 | 2016-06-07 | | 10 | Tom | 4 | 23 | M | 165th | 2016-08-05 | +-+ 10 rows in set (0.01sec)
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.
This is the end of the statement about how to create a view in mysql. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.