A detailed view of MySQL---notes

What is a view

A view is a table that is presented from one or more tables and is a virtual existence.

A view is like a window through which you can see the data that is specifically provided by the system.

This allows users to take care of data that is useful to them without seeing the data in the entire database.

The database contains only the definition of the view, not the data in the view, which is stored in the original table.

When querying data using a view, the database system extracts the corresponding data from the original table.

The data in the view depends on the data in the original table, and the data displayed in the view changes as soon as the data in the table changes.

The role of the view

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

2. Increase the security of the data, through the view, the user can only query and modify the specified data.

3. Improve the logical independence of the table, the view can mask the impact of the original table structure changes.

In summary, most of the use of views is to ensure data security and improve query efficiency

Reference table:

Syntax for creating views

Copy CodeThe code is as follows:
CREATE [algorithm]={undefined| merge| TempTable}]
View view name [(property list)]
As SELECT statement
[WITH [cascaded| LOCAL] CHECK OPTION];

Algorithm the algorithm that represents the view selection (optional parameters)

Undefined:mysql will automatically select the algorithm you want to use
Merge: Merges the statement of the view with the view definition so that some part of the view definition supersedes the corresponding part of the statement
TempTable: Save the result of the view in a temporary table and then execute the statement using the temp table

The view name represents the name of the view to create

The property manifest represents the column name in the view, and the default is the same as the column name in the select query result (optional parameter)

With CHECK option means that you are guaranteed to be within the scope of the attempted permission (optional parameter) when updating the view

cascaded: Conditions for all related views and tables to be met when the view is updated
LOCAL: When you update a view, you want to meet the conditions defined by the view itself

tips: When creating an attempt, it is best to add the WITH cascaded CHECK option parameter, which is a more rigorous approach

Security of data can be guaranteed

View Actions
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)

The Work_view here is the view name, and the arguments in the parentheses represent the columns in the view

As means that the query results from the subsequent SELECT statement are assigned 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 two tables have a specified connection, such as the work.id=salary.id above

Select query view

Copy CodeThe code is as follows:
Mysql> SELECT * from Work_view;
+----+--------+--------+
| ID | NAME | ADDR |
+----+--------+--------+
| 1 | Zhang San | Beijing |
| 2 | John Doe | Shanghai |
| 3 | Harry | Hunan |
| 4 | Zhao Liu | Chongqing |
+----+--------+--------+
Rows in Set (0.00 sec)

The use of the SELECT statement here is the same as in other tables

Don't forget, the view is a table, but it's 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 usual, the describe here 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 here is shown as NULL, which means that the view is just a virtual table

If you use Show table status to view a real table, the results will not be so

Show CREATE View 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 ' and ' work '. ' Address ' as ' ADDR ' from ' work '
Character_set_client:utf8
Collation_connection:utf8_general_ci
Row in Set (0.00 sec)

It's so complicated, it contains all the properties of the view.

Viewing 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 ' and ' person '. ' Work '. ' Address ' as ' ADDR ' from ' person '. ' Work '
Check_option:none
Is_updatable:yes
Definer: [Email protected]
Security_type:definer
Character_set_client:utf8
Collation_connection:utf8_general_ci
2. Row ***************************
Table_catalog:def
Table_schema:person
Table_name:work_view2

All view definition information is included in the Information_schema.views table

However, it is often more convenient to use show CREATE VIEW

The information here is too long, not fully enumerated ...

Modify a View

Modifying a view refers to modifying the definition of a table that already exists in the database, and you can modify the view to maintain consistency between the view and the base table when certain fields of the base table change
CREATE OR REPLACE View statement Modify views

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 the view if it exists, and you can create a view without the view

Its basic usage is almost consistent with create VIEW

Alter statement Modify 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 have this name, salary and address as a field modified view

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

Update view

Update view refers to inserting, updating, and deleting data in a table using a view as a virtual table, where wood has data

When you update through a view, you are converting to the base table to 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 to

Copy CodeThe code is as follows:
mysql> UPDATE Salary SET salary=5899.00 WHERE id=1;

Tips: Although you can update your data in a view, there are many limitations

In general, it's a good idea to use a view as a virtual table for querying data instead of updating the data through a view

Delete a view

Deleting a view means deleting a view that already exists in the database, deleting the view only, deleting the view's definition, and not deleting the 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 here is used to determine whether the view is present or not to write

A detailed view of MySQL---notes