The first 14 Chapter _ View

lecturer ： Silicon Valley - Song Hongkang （ Jianghu people ： a brand of instant noodles ）

Official website ：http://www.atguigu.com

---

1. Common database objects

object	describe
surface (TABLE)	A table is a logical unit that stores data , Exist as rows and columns , Columns are fields , Line is the record
The data dictionary	Is the system table , A table that holds database related information . The data of the system table is usually maintained by the database system , Programmers usually shouldn't modify , You can only view
constraint (CONSTRAINT)	Rules for performing data verification , Rules for ensuring data integrity
View (VIEW)	Logical display of data in one or more datasheets , Views do not store data
Indexes (INDEX)	Used to improve query performance , It's equivalent to a book catalog
stored procedure (PROCEDURE)	Used to complete a complete business process , no return value , However, multiple values can be passed to the calling environment through outgoing parameters
Storage function (FUNCTION)	Used to complete a specific calculation , Has a return value
trigger (TRIGGER)	It's like an event listener , When a specific event occurs in the database , Trigger is triggered , Complete the corresponding processing

2. View overview

2.1 Why use views ？

On the one hand, views can help us use some of the tables instead of all the tables , On the other hand, you can also make different query views for different users . such as , For a company's salesperson , We just want to show him some data , And some special data , For example, the purchase price , Will not be provided to him . Another example , Personnel compensation is a sensitive field , Then it is only open to people above a certain level , This field is not available in other people's query views .

What I just talked about is just a usage scenario of the view , In fact, views have many functions . Last , We summarize the benefits of view .

2.2 View understanding

• View is a kind of Virtual table , Itself is No data Of , Takes up very little memory space , It is SQL An important concept in .

• Views are built on existing tables , The tables on which the view is built are called Base watch .
  

• The creation and deletion of views only affect the view itself , It does not affect the corresponding base table . But when the data in the view is added 、 When deleting and modifying operations , The data in the data table will change accordingly , vice versa .

• The statement that provides data content to the view is SELECT sentence , A view can be understood as Stored up SELECT sentence

  • In the database , The view does not save data , The data is really saved in the data table . When adding data to the view 、 When deleting and modifying operations , The data in the data table will change accordingly ; vice versa .

• View , Is another form of providing users with base table data . Usually , Databases for small projects may not use views , But on big projects , And when the data table is complex , The value of view is highlighted , It can help us put the result set of frequent queries into the virtual table , Improve efficiency . It is very convenient to understand and use .

3. Create view

• stay CREATE VIEW Insert subquery in statement

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

• Lite version

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 data in view ：

SELECT *
FROM salvu80;

give an example ：

# How to determine the field names in the view 1：
CREATE VIEW emp_year_salary (ename,year_salary)# The number of fields in parentheses is the same as SELECT Match the fields one by one 
AS
SELECT ename,salary*12*(1+IFNULL(commission_pct,0))# The fields in the view can be queried. There may be no corresponding fields in the base table , Such as the calculated salary 
FROM t_employee;

give an example ：

# How to determine the field names in the view 2：
CREATE VIEW salvu50
AS
SELECT employee_id ID_NUMBER, last_name NAME,salary*12 ANN_SALARY# The alias of the field in the query statement appears as the name of the field in the view 
FROM employees
WHERE department_id = 50;

explain 1： In fact, we are SQL The view is encapsulated based on the query statement VIEW, This will be based on SQL The result set of the statement forms a virtual table .

explain 2： When creating a view , No field list was specified after the view name , The field list in the view defaults to and SELECT The list of fields in the statement is consistent . If SELECT Alias the field in the statement , Then the field name and alias in the view are the same .

3.2 Create a 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;

• Use the view to format the data

We often need to output content in a certain format , For example, we want to output the employee name and the corresponding department name , The corresponding format is emp_name(department_name), You can use the view to complete the operation of data formatting ：

CREATE VIEW emp_depart
AS
SELECT CONCAT(last_name,'(',department_name,')') AS emp_dept
FROM employees e JOIN departments d
WHERE e.department_id = d.department_id

3.3 Create a view based on the view

When we create a view , You can also continue to create views on top of it .

give an example ： union “emp_dept” The view and “emp_year_salary” View query employee name 、 Department name 、 Annual salary information creation “emp_dept_ysalary” 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 view

grammar 1： View the table objects of the database 、 View objects

SHOW TABLES;# View the views and tables under the current database 

grammar 2： Look at the structure of the view

DESC / DESCRIBE View name ;

grammar 3： View the attribute information of the view

# View view information （ Display the storage engine of the data table 、 edition 、 Number of data rows and data size, etc ）
SHOW TABLE STATUS LIKE ' View name ';# Pay attention to SqlYog Unrecognized \G Instead of ;

Execution results show , notes Comment by VIEW, Explain that the table is a view , Other information is NULL, It means that this is a virtual table .

grammar 4： View the detailed definition information of the view

SHOW CREATE VIEW View name ;

5. Update data for view ( increase , Delete , Change )

5.1 General situation

MySQL Support use INSERT、UPDATE and DELETE Statement to insert data in a view 、 Update and delete operations . When the data in the view changes , The data in the data table will also change , vice versa .

give an example ：UPDATE operation

# Update data for view , This will lead to the modification of data in the base table 
# Empathy , Update the data in the table , It will also lead to the modification of the data in the view 
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)

give an example ：DELETE operation

# Delete data from view , It will also lead to the deletion of data in the table 
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

To make the view updatable , There must be... Between the rows in the view and the rows in the underlying base table one-on-one The relationship between . In addition, when the view definition is as follows , View does not support update operation ：

• When defining the view, you specify “ALGORITHM = TEMPTABLE”, View will not support INSERT and DELETE operation ;
• The view does not contain all columns in the base table that are defined as non empty and do not specify a default value , View will not support INSERT operation ;
• In defining the SELECT Used in the statement JOIN The joint query , View will not support INSERT and DELETE operation ;
• In defining the SELECT The field list after the statement uses Mathematical expression or Subquery , View will not support INSERT, Nor does it support UPDATE Mathematical expressions are used 、 The field value of the subquery ;
• In defining the SELECT Statement is used in the field list after the DISTINCT、 Aggregate functions 、GROUP BY、HAVING、UNION etc. , View will not support INSERT、UPDATE、DELETE;
• In defining the SELECT Statement contains a subquery , The subquery refers to FROM The watch at the back , View will not support INSERT、UPDATE、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',
-> '[email protected]','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 The results of the execution show that , In defining the SELECT Used in the statement JOIN The joint query , The view will not support the update operation .

Although view data can be updated , But on the whole , View as Virtual table , It is mainly used for Convenient query , It is not recommended to update the data of the view . Changes to view data , It is done by operating the data in the actual data table .

6. modify 、 Delete view

6.1 Modify the view

The way 1： Use CREATE OR REPLACE VIEW Clause 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;

explain ：CREATE VIEW The alias of each column in the clause should correspond to each column in the subquery .

The way 2：ALTER VIEW

The syntax for modifying a view is ：

ALTER VIEW View name
AS
Query statement

6.2 Delete view

• Deleting a view just deletes the definition of the view , The data of the base table will not be deleted .

• 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;
  

• explain ： View based a、b Created a new view c, If the view a Or view b Delete , Will cause the view c Your query failed . Such a view c Need to manually delete or modify , Otherwise, it will affect the use of .

7. summary

7.1 View advantages

1. It's easy to operate , Simplify queries

Define frequently used query operations as views , It can make developers do not need to care about the structure of the data table corresponding to the view 、 The relationship between tables , There is no need to care about the business logic and query conditions between data tables , Simply manipulate the view , It greatly simplifies the operation of developers on the database .

2. Reduce data redundancy

The view is different from the actual data table , It stores query statements . therefore , In use , We want to get the result set by defining the query statement of the view . The view itself does not store data , Do not occupy the resources of data storage , Reduced data redundancy .

3. Data security

MySQL The user's response to the data Access restrictions On the result set of some data , The result set of these data can be realized by using views . Users do not have to query or manipulate the data table directly . This can also be understood as the view has Isolation, . The view is equivalent to adding a layer of virtual tables between the user and the actual data table .

meanwhile ,MySQL Users' access to data can be restricted to some views according to their permissions , Users do not need to query the data table , The information in the data table can be obtained directly through the view . This ensures the security of the data in the data table to a certain extent .

4. Adapt to flexible needs
When the requirements of the business system change , If you need to change the structure of the data table , The workload is relatively large , You can use views to reduce the amount of change . This method is often used in practical work .

5. Ability to decompose complex query logic
If there is complex query logic in the database , Then the problem can be decomposed , Create multiple views to get data , Then combine the created multiple views , Complete complex query logic .

7.2 Insufficient views

If we create a view based on the actual data table , that , If the structure of the actual data table changes , We need to maintain the relevant views in time . Especially nested views （ Is to create a view based on the view ）, Maintenance will become more complex , Poor readability , It can easily become a potential hidden danger of the system . Because the view is created SQL Queries may rename fields , It may also contain complex logic , These will increase the cost of maintenance .

In actual projects , If there are too many views , It will lead to the problem of database maintenance cost .

therefore , When creating views , You should combine the actual project needs , Comprehensively consider the advantages and disadvantages of view , In this way, the view can be used correctly , Make the whole system optimal .