The keyword "delimiter" is used to declare the delimiter of SQL statement and tell mysql interpreter whether the command has ended and whether mysql can be executed. By default, delimiter is a semicolon;. In the command line client, if a line of commands ends with a semicolon, mysql will execute the command after you enter.

trigger

introduce

A trigger is a database object related to a table. It refers to a collection of SQL statements defined in the trigger that are triggered and executed before or after insert/update/delete. This feature of the trigger can help the application in the database side to ensure data integrity, logging, data verification and other operations.

Use the aliases OLD and NEW to refer to the changed record content in the trigger, which is similar to other databases. At present, triggers only support row level triggering, not statement level triggering.

Syntax format:

create trigger trigger_name 
before/after insert/update/delete
on tbl_name 
[ for each row ]  -- row-level trigger 
begin
	trigger_stmt ;
end;

example

Create an insert type trigger to complete the logging when inserting data:

DELIMITER $

create trigger emp_logs_insert_trigger
after insert 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) 
  values(null,'insert',now(),new.id,concat('After insertion(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')'));	
end $

DELIMITER ;

Create an update type trigger, and complete the log record when updating data:

DELIMITER $

create trigger emp_logs_update_trigger
after update 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) 
  values(null,'update',now(),new.id,concat('Before modification(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,') , After modification(id',new.id, 'name:',new.name,', age:',new.age,', salary:',new.salary,')'));                                                                      
end $

DELIMITER ;

Create the trigger of delete row, and log when the data is deleted:

DELIMITER $

create trigger emp_logs_delete_trigger
after delete 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) 
  values(null,'delete',now(),old.id,concat('Before deleting(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')'));                                                                      
end $

DELIMITER ;

Delete trigger

Grammatical structure:

drop trigger [schema_name.]trigger_name

If no schema is specified_ Name, the default is the current database.

View triggers

You can view the status, syntax and other information of the trigger by executing the SHOW TRIGGERS command.
Grammatical structure:

show triggers ；

view

A View is a virtual table. The View does not actually exist in the database. The row and column data comes from the tables used in the query that defines the View, and is generated dynamically when the View is used. Generally speaking, a View is a result set returned after a SELECT statement is executed. So when we create a View, the main work is to create this SQL query statement.

The advantages of views over ordinary tables include the following.

• Simple: users who use views do not need to care about the structure, association conditions and filter conditions of the corresponding tables. They are the result set of filtered composite conditions for users.
• Security: users who use views can only access the result set they are allowed to query. Permission management of tables cannot be limited to a row or a column, but it can be implemented simply through views.
• Data independence: once the view structure is determined, you can shield the impact of changes in the table structure on users. Adding columns to the source table has no impact on the view. Modifying column names in the source table can be solved by modifying the view, without impact on visitors.

The syntax to create a view is:

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

The syntax for modifying a view is:

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

Options: 
	With [cached | local] check option determines whether to allow the data to be updated so that the record no longer meets the view conditions.
	LOCAL: it can be updated as long as the conditions of this view are met.
	CASCADED: you must meet all the conditions for all views of this view before you can update. Default

Example, creating a city_country_view view, execute the following SQL:

create or replace view city_country_view 
as 
select t.*,c.country_name from country c , city t where c.country_id = t.country_id;

Query view

SHOW VIEWS

Delete view

DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]	
# example
DROP VIEW city_country_view ;

This article references www.itheima.com The learning materials are only used for summary of my study notes and cannot be reprinted. Please forgive me for any infringement.