View is a slightly more advanced point of knowledge in mysql, it is itself a virtual table, this article introduces the view in mysql from the case.

1. Understand the view

Remember when you first learned about views, you always got confused with the concept of tables. We can think of it this way. When I was in primary school, there would be a sampling test every year, which meant to select several excellent students from each class to take the test. At this time, each class can be regarded as a real table, and the students screened out by many classes can form a temporary class, which can be regarded as a view. In other words, this class is not real, and after the exam, these students still go back to their own homes to find their mothers.

Mysql has views since version 5.1. Generally, views are used to encapsulate complex or repetitive operations. For example, the same query results are used in multiple places, or the SQL statement is complex and encapsulated as a view, which can be used next time.

View functions:

(1) Improved reuse, database reconstruction, but does not affect the operation of the program,

(2) improved security, can be different users, let the data more clear, especially query operations, reduce complex SQL statements,

(3) Enhance readability; More secure, database authorization commands cannot be restricted to specific rows and columns, but can be restricted to the row and column level by properly creating views;

2. View operations

Create a view:

Create view Name as select field name from table name…… ;

View view:

(1) Describe view name;

(2) show table status like ‘view_id ‘\G;

(3) show create view;

(4) select * from information_schema.views;

Modify view:

Create view Name as select field name from table name……. ;

Alter VIEW View name as SELECT field name from table name…… ;

Update view:

Update table_name set table_name = table_name;

(2) insert into values(1) ;

Delete from table_name where table_name = table_name;

Delete a view:

Drop view if exists View name;

Here’s an example.

Three, examples to demonstrate

In this case, create a view under a single table

mysql> create table user ( -> id int(11) primary key auto_increment, -> name varchar(20), -> english float, -> math float -> ); Query OK, 0 rows affected (0.26 SEC) mysql> select * from user; +, + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- + | | id name | English | math | +, + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- + | 1 | zhang SAN | 70 | 75 | | 2 | li si 80 | | 75 | | 3 | fifty and | | | + 90-85 + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + 3 rows in the set (0.00 SEC)Copy the code

Create a new user table here and insert some data into it. Let’s create a new view and use it again.

mysql> create view user_view as -> select id,name, english+math from user; Query OK, 0 rows affected (0.06 SEC) mysql> select * from user_view; +, + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id name | English + math | + - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | zhang SAN | 145 | | 2 | |, dick, and harry 155 | | 3 | fifty and | | + 175 - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 3 rows in the set (0.00 SEC)Copy the code

Now we create a view called user_View, select a few of the fields, and use them just like a normal table. Creating a view in the case of multiple tables is the same, except that multiple tables are linked to the query in the SELECT statement.