MySQL -- triggers and views

trigger

Trigger is a special stored procedure , happen DML The operation that is automatically performed during the operation , For example, log records ：

add to 、 View and delete

 grammar ：
create trigger +  Trigger Name 
< before | after>  Define trigger timing 
<insert | delete | update>  Definition DML Operation type 
on <tb_name>
for each row// Declare as row level operation , If one line is affected, it will trigger once 
begin
-- sql If there is only one sentence, you can not write begin and end
end;
 Example ：
create trigger tri_text
after insert on students
for each row// Row level operation 
insert into stulogs(time,log_text) value(now(),concat(' add to ',NEW.stu_num,' Student information '));
show triggers;// Check triggers 
drop trigger + Trigger Name ;// Delete trigger 

NEW

update、insert in NEW Update 、 The inserted data

OLD

update、delete in OLD Update 、 Data before deletion

Trigger Usage Summary

advantage

• The trigger is ⾃ Action and execution ⾏ Of , When executing on the table related to the trigger ⾏ Responsive DML In operation ⽴ That is to hold ⾏;
• Triggers can cascade data in a table （ Associated operations ）, It helps to ensure the integrity of data ;
• Triggers can be used on DML The data of the operation goes into ⾏ More complex legitimacy verification

shortcoming

• send ⽤ If there is a problem with the business logic implemented by the trigger, it will be difficult to locate , Later maintenance is difficult ;
• ⼤ Quanshi ⽤ Triggers can easily lead to messy code structure , Increases the complexity of the program ;
• When the trigger operates, the amount of data ⽐ a ⼤ when , Of board ⾏ Efficiency meeting ⼤⼤ Reduce .

Suggest

• On the Internet ⽹ term ⽬ in , Adaptation triggers should be avoided ;
• For concurrency, it is not ⼤ The item ⽬ You can choose to make ⽤ stored procedure , But on the Internet ⽹ lead ⽤ It is not advocated to make ⽤ Stored
  cheng （ reason ： When storing procedures, the logic that implements the business is handed over to the database for processing ,⼀ It increases or decreases the load of the database ,
  ⼆ It is not conducive to database migration ）

View

Concept of view

View , Namely From the database ⼀ A table or multiple tables are constructed from the data queried according to specific conditions Virtual table

The function of view

• Security ： If we authorize the data sheet directly to ⽤ Door operation , that ⽤ You can CRUD All numbers in the data table
  According to the , Add ⼊ We want to enter some data in the data table ⾏ Protect , Public data can be ⽣ Make a view , to grant authorization
  ⽤ User access view ;⽤ Users can obtain the data exposed in the data table by querying the view , from ⽽ To achieve the data table
  Some data pairs of ⽤ User hidden .
• simplicity ： If the data we need to query comes from multiple data tables , You can make ⽤ Multi table join query ;
  We match the results of these linked table queries to ⽤ Household opening ,⽤ Users can get more information directly by querying the view
  Table data , More convenient operation .

Create view

grammar ：

create view <view_name>
AS
select Query statement 

View data properties

Query operation ： If new data is added to the data table ,⽽ And this data is full ⾜ Query statement when creating view
Pieces of , You can also query the new data through the query view ; When deleting the original table full ⾜ When querying the data of criteria , Also from the
Delete .
The new data ： If you add data to the view , The data will be added to the original data table
Delete data ： If you delete data from a view , The data will also be deleted from the original table
Modify the operating ： If you modify the data , The data in the original data table will also be modified
The use of view ⽤ Suggest : Simplify operations on complex queries , And will not enter the data ⾏ In case of modification, it can make ⽤ View .

Query view structure

desc +  View name ;

Modify the view

Mode one ：

create or replace view +  View name  AS +  Search statement ;

Mode two ：

alter view +  View name  AS +  Search statement ;

Delete view

drop view +  View name ;

It is different from deleting view data , When deleting the entire view , The data in the table will not be deleted .