MySQL Views (view)

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

Syntax for creating views

CREATE [algorithm] = {UNDEFINED| MERGE| temptable}] VIEW [(attribute list)]  as SELECT Statement [with[cascaded| LOCAL]CHECKOPTION];

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 more stringent to ensure data security

View Actions
Create a view on a single table

MySQL>CREATEVIEWasSELECT  from work 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

Mysql> CREATEAlgorithm=MERGEVIEWwork_view2 (id,name,salary) -  as SELECT  Work. id,name,salary from  Work, Salary - WHERE  Work. ID=salary.id -  withLOCALCHECK OPTION; Query OK,0Rows Affected (0.02Sec

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

Mysql> SELECT *  fromWork_view;+----+--------+--------+|Id|NAME|ADDR|+----+--------+--------+| 1 |Tom|Beijing|| 2 |John doe|Shanghai|| 3 |Harry|Hunan|| 4 |Zhao Liu|Chongqing|+----+--------+--------+Rowsinch Set(0.00Sec

The use of the SELECT statement here is the same as the usage in the other tables. Don't forget, the view is a table, but it's virtual.

Describe view basic information

Mysql>DESCRIBE Work_view;+-------+-------------+------+-----+---------+-------+|Field|Type| Null | Key | Default |Extra|+-------+-------------+------+-----+---------+-------+|Id| int(Ten)|NO| | NULL | ||NAME| varchar( -)|NO| | NULL | ||ADDR| varchar( -)|YES| | NULL | |+-------+-------------+------+-----+---------+-------+Rowsinch Set(0.00Sec

As usual, the describe here can be abbreviated as DESC

Show TABLE Status View basic information

Mysql>SHOWTABLESTATUS like 'Work_view'\g*************************** 1. Row***************************Name:work_viewengine:NULLVersion:NULLRow_format:NULLRows:NULLavg_row_length:NULLdata_length:NULLmax_data_length:NULLindex_length:NULLData_free:NULLauto_increment:NULLCreate_time:NULLUpdate_time:NULLCheck_time:NULLCollation:NULLChecksum:NULLcreate_options:NULLComment:VIEWRowinch Set(0.00Sec

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

Mysql>SHOWCREATE VIEWwork_view\g*************************** 1. Row***************************View: Work_viewCreate View:CREATEAlgorithm=UNDEFINED Definer=' Root ' @ ' localhost ' SQL SECURITY definerVIEW' Work_view ' as Select` Work'. ' ID ' as' ID ', ' Work'. ' Name ' as' NAME ', ' Work'. ' Address ' as' ADDR ' from` Work' Character_set_client:utf8collation_connection:utf8_general_cirowinch Set(0.00Sec

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

Viewing view details in the Views table

Mysql> SELECT *  fromInformation_schema.views\g*************************** 1. Row***************************TABLE_CATALOG:defTABLE_SCHEMA:personTABLE_NAME:work_viewVIEW_DEFINITION:Select' Person '. ' Work'. ' ID ' as' ID ', ' person '. ' Work'. ' Name ' as' NAME ', ' person '. ' Work'. ' Address ' as' ADDR ' from' Person '. ' Work' CHECK_OPTION:NONEIS_UPDATABLE:YESDEFINER:root@localhostSECURITY_TYPE:DEFINERCHARACTER_SET_CLIENT:utf8COLLATION_CONNECTION:utf8_general_ci*************************** 2. Row***************************TABLE_CATALOG:defTABLE_SCHEMA:personTABLE_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

MySQL>CREATEORREPLACE algorithm=temptable  VIEW -as SELECT from work   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

MySQL>ALTERVIEW  work_view2 (name,salary,addr) as SELECT  from  Work , Salary  - WHERE work. ID=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

MySQL>UPDATESET SALARY=5899.00WHERE NAME='  Zhang San '1 row affected (0.0311  0

The statement here is equivalent to

MySQL>UPDATESET Salary=5899.00WHERE id=1 ;

Tips: Although you can update 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

MySQL>DROPVIEWIFEXISTS0 rows affected (0.00  sec) MySQL>DROPVIEW0 rows affected (  0.01 sec)

MySQL Views (view)