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

In this issue, the editor will bring you about the basic operation of mysql view. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

The view is a slightly more advanced knowledge point in a mysql, and it is itself a virtual table.

First, understand the view

I still remember that when I first learned about views, I was always indistinguishable from the concept of tables. We can understand it this way. When I was in primary school, a random test was held every year, which meant that several outstanding students were selected from each class to take the exam. At this time, each class can be regarded as a real table, and the students selected by many classes can temporarily form a class, and the class can be used as a view, that is to say, the class is not real. After the exam, these students still go back to their own homes to find their own mothers.

Mysql has had views since version 5. 1. Usually, views are used to encapsulate complex or repetitive operations. For example, use the same query results in multiple places, or the sql statement is more complex, encapsulated into a view, and can be used directly next time.

The role of the view:

(1) it improves the reusability and reconstructs the database without affecting the running of the program.

(2) improve the security, make the data clearer for different users, especially the query operation, and reduce complex SQL statements.

(3) enhanced readability; more secure, database authorization commands cannot be limited to specific rows and columns, but permissions can be limited to row and row levels by creating views reasonably

Second, view operation

Create a view:

Create view view name as select field name from table name...

View the view:

(1) describe view name

(2) show table status like 'View name'\ G

(3) show create view view name

(4) select * from information_schema.views

Modify the view:

Create view view name as select field name from table name... .

Alter view view name as select field name from table name...

Update the view:

(1) update view name set field name = value

(2) insert into table name values (value, value …)

(3) delete from view name where field = value

Delete the view:

Drop view if exists View name

Let's give an example.

Third, give examples to demonstrate

In this case, the view is created under a single table

1mysql > create table user (

2-> id int (11) primary key auto_increment

3-> name varchar (20)

4-> english float

5-> math float

6->)

7Query OK, 0 rows affected (0.26 sec)

8mysql > select * from user

9 colors, houses, houses, houses.

10 | id | name | english | math |

11 colors, houses, houses, houses.

12 | 1 | Zhang San | 70 | 75 |

13 | 2 | Li Si | 75 | 80 |

14 | 3 | Wang Wu | 85 | 90 |

15 years, Mustco, Mustco, Mustco,

163 rows in set (0.00 sec)

A new user table is created here, and several pieces of data are inserted into it. Let's create a new view and then use it.

1mysql > create view user_view as

2-> select id,name, english+math from user

3Query OK, 0 rows affected (0.06 sec)

4mysql > select * from user_view

5Flowmeter, color, color, price, price, price and price.

6 | id | name | english+math |

7 pay, talk, talk.

8 | 1 | Zhang San | 145 |

9 | 2 | Li Si | 155 |

10 | 3 | Wang Wu | 175 |

11 colors, houses, houses, houses.

123 rows in set (0.00 sec)

Now we have created a view called user_view, and then selected a few of the fields so that we can use them like normal tables. The same is true for creating views in the case of multiple tables, except that more tables are linked to the query during the select statement.

The above is the basic operation of the mysql view shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, 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.