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

This article mainly explains "what is the syntax of the view in MySQL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the syntax of the view in MySQL".

What is the view?

The view View is a kind of virtual table, which does not save the data itself, and encapsulates a SELECT statement internally. The data comes from one or more tables of the actual data of the query.

The structure of the view is the same as that of the real table, and it is also composed of rows and columns, and you can also add, delete, modify and query on the view.

The role of views

Views are mainly used for queries, and the advantages of using views are:

1) simple

Views can encapsulate complex query statements, and users do not need to care about table structure, join relations, filter conditions, grouping and sorting, etc., but only need to query the view simply.

2) Security

When creating a view, you can filter out some rows and columns that you do not want users to see, or you can set access permissions to the view so that different levels of users can see different views, thus improving the data security of the database.

Syntax of the view

Create a view

Create view View name

As

Select statement

Delete View

Drop view View name

Modify the view

Replace view View name

As

Select statement

Working with View

Selec * from View name [where condition]

Code case:

-- create a view on a single table to query the views of female students

Create view view_female_stu

As

Select * from tb_student where stu_gender = 'female'

-- using views

Select * from view_female_stu

Use join queries to create views and query student names, courses, and scores

Create view view_stu_score

As

Select s.stu_id student number, s.stu_name name, c.course_name course, sc.score score

From tb_student s left join tb_score sc on s.stu_id = sc.stu_id

Left join tb_course c on c.course_id = sc.course_id

-- using views

Select * from view_stu_score where score > 80

Note: when you create a view, the alias in the query statement is used as the column name of the view

Modification of the view

Views are used primarily for queries, but you can also execute insert, update, and delete statements, and the results are mapped to the actual table associated with the view.

-- updating the age of students

Update view_female_stu set stu_age = stu_age + 3 where stu_id = 4

As a result of executing the above code, the actual tb_student table is updated.

-- updating student scores

Update view_stu_score set score = 83 where student number = 1

As a result of executing the above code, the update failed with an error:

Why can some views be updated and others fail?

Views in the following situations cannot perform insert, update, and delete operations:

1) Multi-table join

2) include distinct

3) include aggregate functions

4) include group by

5) include order by

6) include union and union all

7) including subqueries, etc.

So there are still many restrictions on performing DML operations on the view, and in most cases we still query the view, and it is recommended to operate on the actual table if the DML operation is needed.

Thank you for reading, the above is the content of "what syntax does the view in MySQL have?" after the study of this article, I believe you have a deeper understanding of the syntax of the view in MySQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.