Cool breeze AAA 2022-02-13 07:34:47 阅读数:613
lecturer : Silicon Valley - Song Hongkang ( Jianghu people : a brand of instant noodles )
Official website :http://www.atguigu.com
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 |
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 .
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
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 .
CREATE [OR REPLACE]
[ALGORITHM = {
UNDEFINED | MERGE | TEMPTABLE}]
VIEW View name [( Field list )]
AS Query statement
[WITH [CASCADED|LOCAL] CHECK OPTION]
CREATE VIEW View name
AS Query statement
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 .
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;
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
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;
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 ;
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)
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 :
JOIN The joint query
, View will not support INSERT and DELETE operation ; Mathematical expression
or Subquery
, View will not support INSERT, Nor does it support UPDATE Mathematical expressions are used 、 The field value of the subquery ;DISTINCT
、 Aggregate functions
、GROUP BY
、HAVING
、UNION
etc. , View will not support INSERT、UPDATE、DELETE; Non updatable 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 forConvenient 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 .
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
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 .
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 .
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 .
copyright:author[Cool breeze AAA],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130734448899.html