In this chapter, we will learn about views in MySQL. Views are an important component of a database. We will explain what a view is, what a view does, how to create and use a view, and how to modify a view.

What is a view

View A View is a virtual table that does not store data but encapsulates a SELECT statement. The data comes from one or more tables with actual data queried. The structure of a view is the same as that of a real table, consisting of rows and columns. You can add, delete, modify, and query a view.

The role of views

Views are mainly used for query. The advantages of using views are as follows:

  1. Simple views encapsulate complex query statements. Users do not need to care about table structure, join relationships, filtering conditions, grouping, sorting, and so on. They only need to query simple views.
  2. Security When creating a view, you can filter out the rows and columns that you do not want users to see and set access permissions for the view so that users at different levels can see different views to improve database data security.

View syntax

Create a view

Create view as SELECT statement;Copy the code

Delete the view

Drop view Specifies the view name.Copy the code

Modify the view

Replace View View name as SELECT statement;Copy the code

Using the view

Selec * from view name [where condition];Copy the code

Code examples:

Create view view_female_stu as select * from tb_student where stu_gender = 'female '; Select * from view_female_stuCopy the code

Create view with join query Create view view_stu_score as select s.stu_id, 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; Select * from view_stu_score where score > 80;Copy the code



Note: When a view is created, the alias in the query statement is used as the column name of the view

View modification

Views are primarily used for queries, but you can also execute INSERT, UPDATE, and DELETE statements, and the results are mapped to the actual tables associated with the view.

Update view_female_stu set stu_age = STU_age + 3 where stu_id = 4; update view_female_stu set stu_age = stu_age + 3 where stu_id = 4;Copy the code

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

Update view_stu_score set score = 83 where score = 1;Copy the code

The result of executing the above code is that the update fails and an error occurs: why can some views be updated and others fail? Insert, UPDATE, and DELETE operations cannot be performed on the following views:

  1. Multiple table joins
  2. Contains distinct
  3. Include aggregate functions
  4. Contains a group by
  5. Contains the order by
  6. Contains union and Union all
  7. Contains subqueries and so on

Therefore, there are many restrictions on performing DML operations on the view. In most cases, we still query the view. If DML operations are needed, we recommend performing operations on the actual table.

conclusion

A view is a virtual table whose data comes from the query results of actual tables. It can simplify query operations and improve database security. This chapter has learned the syntax of creating and using a view. If you find this article useful, please give it a thumbs up 🙂