What is the use of mysql view?

The functions of mysql views: 1. Improves reusability; 2. Reconstructs the database without affecting the running of the program; 3. Improves security performance and can set different views for different users. ; 4. Make the data clearer.

Test table: user has id, name, age, sex fields

Test table: goods has id, name, price fields

Test table: ug has id, userid, goodsid fields

The role of the view is really powerful. The following are the benefits I have experienced:

Function 1:

Improves reusability, just like a function. If you want to frequently obtain the name of the user and the name of the goods. You should use the following sql language. Example:

select a.name as username, b.name as goodsname from user as a, goods as b, ug as c where a.id=c.userid and c.goodsid=b.id;

But it’s different with a view. Create a view other. Example

create view other as select a.name as username, b.name as goodsname from user as a, goods as b, ug as c where a.id=c.userid and c.goodsid=b.id;

After creating the view, you can get the name of the user and the name of the goods like this. Example:

select * from other;

With the above sql statement, you can get the name of the user and the name of the goods.

Function 2:

Reconstructs the database without affecting the running of the program. If due to certain needs, the user table usera and userb need to be split. The structure of the two tables is as follows:

Test table: usera has id, name, and age fields

Test table :userb has id, name, and sex fields

At this time, if the php side uses the sql statement: select * from user;, it will prompt that the table does not exist. How to solve this problem. Solution: Create a view. The following sql statement creates a view:

 create view user as select a.name,a.age,b.sex from usera as a, userb as b where a.name=b.name;

The above assumes that the names are unique. At this time, the php side uses the sql statement: select * from user; and no error will be reported. This realizes the function of changing the database structure without changing the script program.

Function 3:

Improves safety performance. Different views can be set for different users. For example: a user can only obtain the name and age data of the user table, but not the sex data. You can create a view like this. An example is as follows:

create view other as select a.name, a.age from user as a;

In this case, use the sql statement: select * from other; At most, you can only get the name and age data, but not other data.

Function 4:

Make the data clearer. Create whatever kind of view you want for the data you want. After analyzing the above three functions, this function should be easy to understand

The above is the detailed content of What is the use of mysql view?. For more information, please follow other related articles on the PHP Chinese website!