mysql--View

Core points of Knowledge:

1. View definition

2. Benefits of view: security, resource saving, simple operation, identity of data

3. Basic operation of the view

I. Overview of the View

A view is a virtual table whose contents are defined by a query.

As with a real table, a view contains a series of column and row data with names.

However, the view does not exist in the database as a stored set of data values.

Row and column data comes from a table that is caused by a query that defines the view, and is generated dynamically when the view is referenced.

The view acts like a filter for the underlying table referenced in it.

A filter that defines a view can come from one or more tables or other views of the current or other database.

There are no restrictions on view queries, and there are few restrictions on data modification through them.

A view is an SQL statement of a query stored in a database , and it is primarily for two reasons:

first, security reasons, the view can also hide some data , such as: Social Insurance Fund table, you can display only the name of the view, not the social Security number and the number of wages and so on.

second, making complex queries easy to understand and use , this view is like a "window" from which you can see only the columns of data you want to see.

This means that you can use SELECT * on this view, and you see those columns of data that you give in the view definition.

Since the definition of a view is based on a basic table, why do you define a view? This is because the use of views in a reasonable way can bring many benefits:

1. The view simplifies user operation.

The view mechanism allows users to focus on the data they care about.

If the data is not directly from the base table, you can define the view, which makes the database look simple, clear, and simplifies user data query operations.

For example, those that define a number of table-connected views hide the connection between tables and tables from the user.

In other words, what the user is doing is just a simple, virtual query, and how this virtual table is derived, and the user does not need to know.

2. Views enable users to view the same data in multiple ways

The view mechanism enables different users to view the same data in different ways, and this flexibility is necessary when many different kinds of users share the same database.

3. Attempts to provide some degree of logical independence to the refactoring database

The physical independence of data means that the user's application is not dependent on the physical structure of the database.

The logical independence of a database is that the user's application is not affected when the database is refactored, such as adding new relationships or adding new fields to the original relationship.

Hierarchical database and mesh database generally can support the physical independence of data, but the logical independence can not be fully supported.

4. Attempts to provide security for confidential data

With the view mechanism, you can define different views of different users when you design a database application, so that confidential data does not show up on a user view that should not see the data now.

This way, the view mechanism automatically provides security protection for confidential data.

5. Conserve system resources

A view is similar to a map, which is a virtual table that is not actually present on the disk, but is generated dynamically by the view commands, which can reduce redundancy.

Ii. Explanation of examples

Now I have a table of student information, which has the basic information of students and the student's account number and password, but this form I do not want to show directly, because it is not safe.

Requirement 1: I only want to show students basic information, and students can change the information displayed, by default, can only change their own information.

#学生信息表, but not public
Mysql> SELECT * fromStudent_info;+----+--------+---------+--------------+------------+-----------+| ID | name | Dorm | Addr | Account |  passwd |+----+--------+---------+--------------+------------+-----------+| 1 | Kobe Bryant | Building No. 6th | Hubei Luotian | 1041031711 |  Dfghjkldf | | 2 | Yarn | Building No. 6th | Xiaogan, Hubei | 1041031715 |  TYUJKLJK | | 3 | Yellow Croaker | Building No. 5th | Hubei Luotian | 1041031732 |  5uy9g6 | | 4 | Nutcharin | Building No. 6th | Yangzhou, Jiangsu | 1041031721 |  Rtyui | | 5 | Grandpa | Building No. 5th | Xiaogan, Hubei | 1041031743 |  tghj8g | | 6 | Deppen | Building No. 5th | Yancheng, Jiangsu | 1041031701 |  gvhui745 | | 7 | Sub-building | Building No. 6th | Hubei Honghu | 1041031723 |  568g29 | | 8 | Zhou Yi | Building No. 6th | Hubei Wuxue | 1041031719 |  976uyif | | 9 | Grandpa | Building No. 5th | Hubei Tongcheng | 1041031710 | CVBDF | | 10 | Bird | Building No. 6th | Xiangyang, Hubei | 1041031737 | 456DFG |+----+--------+---------+--------------+------------+-----------+10 rowsinchSet (0.00 sec)

We create a view that does not contain our own account password

#Create a ViewMysql> CREATE View Student_info_view as select Id,name,dorm,addr fromStudent_info; Query OK, 0 rows affected (0.00sec)#only show this table, this table does not exist, from the main table mappingMysql> SELECT * fromStudent_info_view;+----+--------+---------+--------------+| ID | name | Dorm |  Addr |+----+--------+---------+--------------+| 1 | Kobe Bryant | Building No. 6th |  Hubei Luotian | | 2 | Yarn | Building No. 6th |  Xiaogan, Hubei | | 3 | Yellow Croaker | Building No. 5th |  Hubei Luotian | | 4 | Nutcharin | Building No. 6th |  Yangzhou, Jiangsu | | 5 | Grandpa | Building No. 5th |  Xiaogan, Hubei | | 6 | Deppen | Building No. 5th |  Yancheng, Jiangsu | | 7 | Sub-building | Building No. 6th |  Hubei Honghu Lake | | 8 | Zhou Yi | Building No. 6th |  Hubei Wuxue | | 9 | Grandpa | Building No. 5th | Hubei Tongcheng | | 10 | Bird | Building No. 6th | Hubei Xiangyang |+----+--------+---------+--------------+10 rowsinchSet (0.00 sec)

We only show this table outside, so it's a lot safer, and we don't have to create another table of information, which also saves resources.

We can also delete and change views and be able to sync to the original table

mysql> Delete fromStudent_info_view where id = 1;#Delete First recordQuery OK, 1 row affected (0.01sec) MySQL> select * fromStudent_info;#The original table also deleted the record+----+--------+---------+--------------+------------+----------+| ID | name | Dorm | Addr | Account |  passwd |+----+--------+---------+--------------+------------+----------+| 2 | Yarn | Building No. 6th | Xiaogan, Hubei | 1041031715 |  TYUJKLJK | | 3 | Yellow Croaker | Building No. 5th | Hubei Luotian | 1041031732 |  5uy9g6 | | 4 | Nutcharin | Building No. 6th | Yangzhou, Jiangsu | 1041031721 |  Rtyui | | 5 | Grandpa | Building No. 5th | Xiaogan, Hubei | 1041031743 |  tghj8g | | 6 | Deppen | Building No. 5th | Yancheng, Jiangsu | 1041031701 |  gvhui745 | | 7 | Sub-building | Building No. 6th | Hubei Honghu | 1041031723 |  568g29 | | 8 | Zhou Yi | Building No. 6th | Hubei Wuxue | 1041031719 |  976uyif | | 9 | Grandpa | Building No. 5th | Hubei Tongcheng | 1041031710 | CVBDF | | 10 | Bird | Building No. 6th | Xiangyang, Hubei | 1041031737 | 456DFG |+----+--------+---------+--------------+------------+----------+9 rowsinchSet (0.00sec) MySQL> select * fromStudent_info_view;#View View+----+--------+---------+--------------+| ID | name | Dorm |  Addr |+----+--------+---------+--------------+| 2 | Yarn | Building No. 6th |  Xiaogan, Hubei | | 3 | Yellow Croaker | Building No. 5th |  Hubei Luotian | | 4 | Nutcharin | Building No. 6th |  Yangzhou, Jiangsu | | 5 | Grandpa | Building No. 5th |  Xiaogan, Hubei | | 6 | Deppen | Building No. 5th |  Yancheng, Jiangsu | | 7 | Sub-building | Building No. 6th |  Hubei Honghu Lake | | 8 | Zhou Yi | Building No. 6th |  Hubei Wuxue | | 9 | Grandpa | Building No. 5th | Hubei Tongcheng | | 10 | Bird | Building No. 6th | Hubei Xiangyang |+----+--------+---------+--------------+9 rowsinchSet (0.00 sec)

The same is true for updates.

Requirement 2: Now there is a student score table, students can see the number of information, the need for joint inquiry

# Student score table, lack of study number  from Student_score; +----+--------+-------+-------+--------+| ID | Name   | linux | mysql | python |+----+--------+-------+-------+--------+|  1 | Kobe Bryant   |    |    |     | |  2 | Yarn   |    |    |     | |  3 | Yellow Croaker   |    |    |     | |  4 | Nutcharin   |    |    |     | |  5 | Grandpa   |    |    |     | |  6 | Deppen   |    |    |     Online | |  7 | Sub-building   |    |    |     | |  8 | Zhou Yi   |    |    |     | |  9 | Grandpa   |    |    About |     60 | | 10 | Bird   |    |    |      in Set (0.00 sec)

We're going to create a new view.

#Create a view, use a federated query later, and you can come from a different libraryMysql> CREATE View Student_score_view as select A.id,a.name,b.account,a.linux fromStudent_score A,student_info b where a.name =B.name; Query OK, 0 rows affected (0.01sec) MySQL> select * fromStudent_score_view;+----+--------+------------+-------+| ID | name | Account |  Linux |+----+--------+------------+-------+| 1 | Kobe Bryant |    1041031711 |  63 | | 2 | Yarn |    1041031715 |  45 | | 3 | Yellow Croaker |    1041031732 |  89 | | 4 | Nutcharin |    1041031721 |  56 | | 5 | Grandpa |    1041031743 |  56 | | 6 | Deppen |    1041031701 |  89 | | 7 | Sub-building |    1041031723 |  67 | | 8 | Zhou Yi |    1041031719 |  34 | | 9 | Grandpa |    1041031710 | 56 | | 10 | Bird |    1041031737 | |+----+--------+------------+-------+10 rowsinchSet (0.00 sec)

If you want to see the account, and see the score, it is necessary to co-check the SQL statement is very cumbersome, and the use of views can simplify the operation process

Summary: Basic operation of the view

To create a view:

CREATE view name as SQL query;

Working with Views:

SELECT * from view name;

Update View records:

UPDATE View name SET field name = XXX WHERE XXXX;

To delete a view record:

DELETE from view name WHERE xxxx;

To delete a view:

Drop view name;

mysql--View