MySQL Study Notes 14: 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

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

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

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

Mysql> SELECT * FROM work_view; + ---- + -------- + | ID | NAME | ADDR | + ---- + -------- + | 1 | Zhang San | Beijing | 2 | Li Si | Shanghai | 3 | Wang Wu | hunan | 4 | Zhao 6 | Chongqing | + ---- + -------- + 4 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

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    |       |+-------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)

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

 

Show table status view Basic Information

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: NULLMax_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: VIEW1 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

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: utf8collation_connection: utf8_general_ci1 row in set (0.00 sec)

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

View details in the views table

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: DEFINERCHARACTER_SET_CLIENT: utf8COLLATION_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

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

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.

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

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.

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.