Dream, MySQL view, virtual and real table, complete and detailed, collectable

1. Database object

2. View overview

2.1 why use views?
On the one hand, views can help us use some tables instead of all tables. On the other hand, they can also make different query views for different users. For example, for a salesperson of a company, we only want to show him some data, but some special data, such as purchase price, will not be provided to him. For another example, if employee salary is a sensitive field, it is only open to employees at or above a certain level, and this field is not provided in the query view of others.

2.2 understanding of views
View is a kind of virtual table, which has no data and occupies little memory space. It is an important concept in SQL. Views are built on the basis of existing tables, which are called base tables.

View creation and deletion only affect the view itself, not the corresponding base table. However, when you add, delete and modify the data in the view, the data in the data table will change accordingly, and vice versa.

The statement that provides data content to the view is a SELECT statement, which can be understood as a stored SELECT statement.

In the database, the view does not save data, and the data is really saved in the data table. When the data in the view is added, deleted and modified, the data in the data table will change accordingly; vice versa.

View is another form of providing users with base table data. Usually, the database of small projects can not use views, but in large projects and when the data table is complex, the value of views is highlighted. It can help us put the frequently queried result set into the virtual table and improve the use efficiency.

3. Create a view

Embedding subqueries in CREATE VIEW statements

CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW View name [(Field list)]
AS Query statement
[WITH [CASCADED|LOCAL] CHECK OPTION]

Lite

CREATE VIEW View name
AS Query statement

3.1 create a single table view
give an example:

CREATE VIEW empvu80
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;

Query view:

SELECT *
FROM salvu80;

CREATE VIEW emp_year_salary (ename,year_salary)
AS
SELECT ename,salary*12*(1+IFNULL(commission_pct,0))
FROM t_employee;

CREATE VIEW salvu50
AS
SELECT employee_id ID_NUMBER, last_name NAME,salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;

Note 1: in fact, we encapsulate the VIEW view based on the SQL query statement, so that a virtual table will be formed based on the result set of the SQL statement.
Note 2: if the field list is not specified after the view name when creating a view, the field list in the view is consistent with the field list in the SELECT statement by default. If the field is aliased in the SELECT statement, the field name and alias in the view are the same.

3.2 create multi table union view
give an example:

CREATE VIEW empview
AS
SELECT employee_id emp_id,last_name NAME,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;

CREATE VIEW emp_dept
AS
SELECT ename,dname
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did;

CREATE VIEW dept_sum_vu(name, minsal, maxsal, avgsal)
AS
SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;

3.3 creating views based on views
After we have created a view, we can continue to create views based on it.
For example, combine the "emp_dept" view and the "emp_year_salary" view to query employee name, department name and annual salary information to create the "emp_dept_salary" view.

CREATE VIEW emp_dept_ysalary
AS
SELECT emp_dept.ename,dname,year_salary
FROM emp_dept INNER JOIN emp_year_salary
ON emp_dept.ename = emp_year_salary.ename;

4. View

Syntax 1: View database table objects and view objects

SHOW TABLES;

Syntax 2: view the structure of the view

DESC / DESCRIBE View name;

Syntax 3: view the attribute information of the view

# View view information (display the storage engine, version, number of data rows, data size, etc. of the data table)
SHOW TABLE STATUS LIKE 'View name'\G

The execution result shows that the Comment is VIEW, indicating that the table is a VIEW, and other information is NULL, indicating that this is a virtual table.
Syntax 4: view the detailed definition information of the view

SHOW CREATE VIEW View name;

5. Update the data of the view

5.1 general conditions
MySQL supports inserting, updating, and deleting data in a view using INSERT, UPDATE, and DELETE statements. When the data in the view changes, the data in the data table will also change, and vice versa.

Example: UPDATE operation

mysql> SELECT ename,tel FROM emp_tel WHERE ename = 'Sun Hongliang';
+---------+-------------+
| ename | tel |
+---------+-------------+
| Sun Hongliang | 13789098765 |
+---------+-------------+
1 row in set (0.01 sec)

mysql> UPDATE emp_tel SET tel = '13789091234' WHERE ename = 'Sun Hongliang';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT ename,tel FROM emp_tel WHERE ename = 'Sun Hongliang';
+---------+-------------+
| ename | tel |
+---------+-------------+
| Sun Hongliang | 13789091234 |
+---------+-------------+
1 row in set (0.00 sec)

mysql> SELECT ename,tel FROM t_employee WHERE ename = 'Sun Hongliang';
+---------+-------------+
| ename | tel |
+---------+-------------+
| Sun Hongliang | 13789091234 |
+---------+-------------+
1 row in set (0.00 sec)

Example: DELETE operation

mysql> SELECT ename,tel FROM emp_tel WHERE ename = 'Sun Hongliang';
+---------+-------------+
| ename | tel |
+---------+-------------+
| Sun Hongliang | 13789091234 |
+---------+-------------+
1 row in set (0.00 sec)

mysql> DELETE FROM emp_tel WHERE ename = 'Sun Hongliang';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT ename,tel FROM emp_tel WHERE ename = 'Sun Hongliang';
Empty set (0.00 sec)

mysql> SELECT ename,tel FROM t_employee WHERE ename = 'Sun Hongliang';
Empty set (0.00 sec)

5.2 non updatable views
For the view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying base table. In addition, the view does not support the update operation when the following conditions occur in the view definition:

When defining the view, "algorithm = temporary" is specified, and the view will not support INSERT and DELETE operations;

The view does not contain all columns in the base table that are defined as non empty and have no default value specified. The view will not support INSERT operation;

JOIN joint query is used in the SELECT statement defining the view, and the view will not support INSERT and DELETE operations;

If a mathematical expression or subquery is used in the field list after the SELECT statement defining the view, the view will not support INSERT or UPDATE, and the field values of mathematical expression and subquery are used;

Use DISTINCT, aggregate function, GROUP BY, HAVING, UNION, etc. in the field list after the SELECT statement defining the view. The view will not support INSERT, UPDATE, DELETE;

The SELECT statement defining the view contains a subquery, and the subquery references the table after FROM. The view does not support INSERT, UPDATE and DELETE;

The view definition is based on a non updatable view;

Constant view.

give an example:

mysql> CREATE OR REPLACE VIEW emp_dept
-> (ename,salary,birthday,tel,email,hiredate,dname)
-> AS SELECT ename,salary,birthday,tel,email,hiredate,dname
-> FROM t_employee INNER JOIN t_department
-> ON t_employee.did = t_department.did ;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO emp_dept(ename,salary,birthday,tel,email,hiredate,dname)
-> VALUES('Zhang San',15000,'1995-01-08','18201587896',
-> 'zs@atguigu.com','2022-02-14','New Department');

#ERROR 1393 (HY000): Can not modify more than one base table through a join view'atguigu_chapter9.emp_dept'

From the above SQL execution results, it can be seen that the JOIN joint query is used in the SELECT statement defining the view, and the view will not support the update operation. Although the view data can be updated, generally speaking, as a virtual table, the view is mainly used to facilitate query, and it is not recommended to update the view data. The change of view data is completed through the operation of the data in the actual data table.

6. Modify and delete views

6.1 modifying views
Method 1: use the CREATE OR REPLACE VIEW clause to modify the view

CREATE OR REPLACE VIEW empvu80(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;

Mode 2: ALTER VIEW
The syntax for modifying a view is:

ALTER VIEW View name
AS
 Query statement

6.2 deleting views
Deleting a view only deletes the definition of the view and does not delete the data of the base table.
The syntax for deleting a view is:

DROP VIEW IF EXISTS View name;

DROP VIEW IF EXISTS View name 1,View name 2,View name 3,...;

give an example:

DROP VIEW empvu80;

Note: a new view c is created based on views a and b. if view a or view b is deleted, the query of view c will fail. Such a view c needs to be deleted or modified manually, otherwise it will affect the use.

7. Summary

7.1 view advantages
① Simple operation
By defining the frequently used query operation as a view, developers do not need to care about the structure of the data table corresponding to the view, the association relationship between tables, or the business logic and query conditions between data tables. Instead, they only need to operate the view, which greatly simplifies developers' operation on the database.
② Reduce data redundancy
The view is different from the actual data table. It stores query statements. Therefore, when using, we need to obtain the result set by defining the query statement of the view. The view itself does not store data, does not occupy the resources of data storage, and reduces data redundancy.
③ Data security
MySQL restricts users' access to data to the result set of some data, which can be implemented using views. Users do not have to query or manipulate the data table directly. This can also be understood as view isolation. The view is equivalent to adding a layer of virtual tables between the user and the actual data table.

At the same time, MySQL can restrict users' access to data to some views according to their permissions. Users do not need to query the data table, but can directly obtain the information in the data table through the view. This ensures the security of the data in the data table to a certain extent.
④ Adapt to flexible needs
When the requirements of the business system change, if the structure of the data table needs to be changed, the workload is relatively large. You can use the view to reduce the workload of the change. This method is often used in practical work.
⑤ Ability to decompose complex query logic
If there is complex query logic in the database, you can decompose the problem, create multiple views to obtain data, and then combine the created multiple views to complete the complex query logic.

7.2 insufficient views
If we create a view based on the actual data table, if the structure of the actual data table changes, we need to maintain the relevant views in time. In particular, the maintenance of nested views (that is, creating views based on views) will become more complex and unreadable, which is easy to become a potential hidden danger of the system. Because the SQL query that creates the view may rename fields or contain complex logic, which will increase the maintenance cost.
In the actual project, if there are too many views, it will lead to the problem of database maintenance cost. Therefore, when creating a view, you should consider the advantages and disadvantages of the view in combination with the actual project requirements, so as to use the view correctly
Figure to optimize the whole system

Tags: Database MySQL SQL

Posted on Mon, 29 Nov 2021 07:41:45 -0500 by SnakeO