Usage of MySQL notes View

What is a view?

A view is a table imported from one or more tables. It is a virtual table.

A view is like a window in which you can view the data provided by the system.

In this way, you don't need to see the data in the entire database, but care about the data that is useful to you.

The database only stores view definitions, but not view data. The data is stored in the original table.

When you use a view to query data, the database system extracts the corresponding data from the original table.

The data in the view depends on the data in the original table. Once the data in the table changes, the data displayed in the view also changes.

 

Role of a view

1. to simplify the operation, you can define a view for frequently used queries so that you do not have to specify conditions for the same query operation

2. Increase Data Security. Through the view, you can only query and modify specified data.

3. Improve the logical independence of the table, and the view can shield the impact of changes in the original table structure.

 

All in all, most of the views are used to ensure data security and improve query efficiency.

 

Reference Table:

View creation syntax
Copy codeThe Code is as follows:
CREATE [ALGORITHM] = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW name [(attribute list)]
As select statement
[WITH [CASCADED | LOCAL] check option];

ALGORITHM indicates the ALGORITHM selected for the view (optional)

UNDEFINED: MySQL automatically selects the algorithm to be used.
MERGE: combines view statements with view definitions so that a part of view definitions replaces the corresponding part of statements.
TEMPTABLE: Save the view results to a temporary table and execute the statement using the temporary table.

View name indicates the name of the view to be created

The attribute list indicates the column name in the view. By default, it is the same as the column name in the SELECT query result (optional parameter)

With check option indicates that the view must be updated within the permission range of the attempt (optional)

CASCADED: when updating a view, all related views and table conditions must be met.
LOCAL: when updating a view, you must satisfy the conditions defined by the view.

 

Tips:It is best to add the with cascaded check option parameter when creating an attempt. This method is strict.

This ensures data security.

View operations
Create a view on a single table
Copy codeThe Code is as follows:
Mysql> create view work_view (ID, NAME, ADDR) as select id, name, address FROM work;
Query OK, 0 rows affected (0.05 sec)

Here, work_view is the view name, and the parameters in the brackets below represent the columns in the view.

AS indicates assigning the query results in the SELECT statement to the previous view.

Create a view on multiple tables
Copy codeThe Code is as follows:
Mysql> create algorithm = merge view work_view2 (ID, NAME, SALARY)
-> As select work. id, name, salary FROM work, salary
-> WHERE work. id = salary. id
-> With local check option;
Query OK, 0 rows affected (0.02 sec)

Creating a view in multiple tables requires that the two tables have a specified relationship. The preceding work. id = salary. id

SELECT query view
Copy codeThe Code is as follows:
Mysql> SELECT * FROM work_view;
+ ---- + -------- +
| ID | NAME | ADDR |
+ ---- + -------- +
| 1 | James | Beijing |
| 2 | Li Si | Shanghai |
| 3 | Wang Wu | Hunan |
| 4 | Zhao Liu | Chongqing |
+ ---- + -------- +
Rows in set (0.00 sec)

The usage of the SELECT statement is the same as that of other tables.

Don't forget, the view is also a table, but it is virtual

DESCRIBE view Basic Information
Copy codeThe Code is as follows:
Mysql> DESCRIBE work_view;
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| ID | int (10) | NO | NULL |
| NAME | varchar (20) | NO | NULL |
| ADDR | varchar (50) | YES | NULL |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
Rows in set (0.00 sec)

As in the past, DESCRIBE can be abbreviated as DESC.

Show table status view Basic Information
Copy codeThe Code is as follows:
Mysql> show table status like 'work _ view' \ G
* *************************** 1. row ***************************
Name: work_view
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
Row in set (0.00 sec)

Most of the information is displayed as NULL, which means that the view is only a virtual table.

If you use show table status to view a real TABLE, the result is not as follows:

Show create view Details
Copy codeThe Code is as follows:
Mysql> show create view work_view \ G
* *************************** 1. row ***************************
View: work_view
Create View: create algorithm = undefined definer = 'root' @ 'localhost' SQL SECURITY DEFINER VIEW 'work _ view' AS select 'work '. 'id' AS 'id', 'work '. 'name' AS 'name', 'work '. 'address' AS 'addr 'from 'work'
Character_set_client: utf8
Collation_connection: utf8_general_ci
Row in set (0.00 sec)

Nima is so complicated that it contains all attributes of the view.

View details in the views table
Copy codeThe Code is as follows:
Mysql> SELECT * FROM information_schema.views \ G
* *************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: person
TABLE_NAME: work_view
VIEW_DEFINITION: select 'person '. 'work '. 'id' AS 'id', 'person '. 'work '. 'name' AS 'name', 'person '. 'work '. 'address' AS 'addr 'from 'person '. 'work'
CHECK_OPTION: NONE
IS_UPDATABLE: YES
DEFINER: root @ localhost
SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
* *************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: person
TABLE_NAME: work_view2

The information_schema.views table contains all view definitions.

However, it is more convenient to use show create view.

The information here is too long to be fully listed ......

Modify View

Modifying a view is to modify the definition of an existing table in the database. When some fields in the basic table change, you can modify the view to maintain consistency between the view and the basic table.
Create or replace view statement modify VIEW
Copy codeThe Code is as follows:
Mysql> create or replace algorithm = TEMPTABLE
-> VIEW work_view (ID, NAME)
-> As select id, name FROM work;
Query OK, 0 rows affected (0.03 sec)

In other words, the create or replace statement is very flexible.

You can modify a view if the view exists. If the view is not present, you can create a view.

Its basic usage is almost the same as that of create view.

ALTER statement modification View
Copy codeThe Code is as follows:
Mysql> alter view work_view2 (NAME, SALARY, ADDR)
-> As select name, salary, address FROM work, salary
-> WHERE work. id = salary. id;
Query OK, 0 rows affected (0.03 sec)

I modified the view using the name, salary, and address as a field.

If it is true, it is extremely convenient for thieves.

Update View

Updating a view refers to inserting, updating, and deleting data in a table through a view. A view is a virtual table with data

When views are updated, they are all converted to basic tables for update.
Copy codeThe Code is as follows:
Mysql> UPDATE work_view2 set salary = 5899.00 where name = 'zhang san ';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

The statement here is equivalent
Copy codeThe Code is as follows:
Mysql> UPDATE salary SET salary = 5899.00 WHERE id = 1;

Tips: although data can be updated in a view, there are many restrictions

Generally, it is better to use a view as a virtual table for Data Query instead of updating data through the view.

Delete View

Deleting a view is to delete an existing view in a database. When deleting a view, you can only delete the view definition and do not delete data.
Copy codeThe Code is as follows:
Mysql> drop view if exists work_view;
Query OK, 0 rows affected (0.00 sec)

Mysql> drop view work_view2;
Query OK, 0 rows affected (0.01 sec)

The if exist parameter is used to determine whether a view exists or not.