Play MySQL with Me pack B, take off

Store function: To return a result to the caller, the result can be used in an expression like (Hex (), cast (), cos () function)
A stored procedure, called with a call () statement, is a separate procedure that cannot be used in a function
Summary of stored procedure usage:
1: Implement an action effect without returning a value
2: Operation returns multiple values, function does not do this
Example Store function:
delimiter$$
Create function f_a (ai_n int)
Returns varchar (64)
Begin
DECLARE vs_res varchar (64);
declare vi_count int;
Select MAX (n), count (1) to Vs_res, vi_count from d where i = Ai_n;
If vs_res is null then
Set vs_res = ';
End If;
return vs_res;
end$$
DELIMITER;
The value of the operation is returned when the execution is complete
Set global log_bin_trust_function_creators=true is required. Use the storage function Select F_a (12); I'm just using the SELECT statement here, but the storage function can use any complex statement insert, DELETE statement, etc.
Stored Procedures
Example: Delimiter #
Create Proceduree Getstudentname (in age) {//Creating stored Procedure parameter description (in age)//in passing arguments to the program on behalf of the caller
begin//the start of a compound statement
Delecare getName varchar (10);//declare a variable getName;
If age<10
Then set getname= "pupils";
End If;
If Ten then set Getname= "Junior High School";
End If;
Start transaction;//open things up
while (age<100)
Set age=age+1;
INSERT into student (ID) values (age);
End while;
commit;//COMMIT Transaction
End #;//Compound statement
delimiter;
Call Getstudentname (5);//invocation of statement

Note Storage functions have a limit
Do not allow calling statements to modify the data table that this function is reading or writing in other words, the//select statement is executing a getname () stored function that is querying the student table, and you cannot use UpdateName storage functions in the GetName function at this time
The parameters of the stored procedure in//the caller pass in the parameters, the function is processed internally, the entire procedure is not visible to the caller, the out//procedure returns the value to the caller, the value is visible to the caller, inout//runs the caller to pass a value to the procedure, and returns the value

Example://
Delimeter $
CREATE PROCEDURE Getstuentname (out P-name,out pgirlname)
Begin
SELECT * FROM student where sex= ' man ' into p-name;
SELECT * from studnt where sex = ' female ' into pgirlname;
End
Delimeter;

Use Call Getstudentname (@NAME, @NAMES);//process will put the value into the parameter
View: Select ' male: ', @NAME;
Note: All parameters for the storage function are like in parameters;

Trigger:
Associated with a particular table, the corresponding statement is triggered and executed as (Insert,delete,update statement)
Pros: 1: Triggers can check for updates and deletions of data, in other words we can use triggers to check the integrity of the data, such as checking if a value falls between 0-100, and triggers can be used to filter the input data as necessary

2: The expression result is assigned to the data example as the default value,  3: Check its contents before deleting or modifying it, and record the modification of the existing data row into a log  format: Create trigger trigger_name  {before/after}// Curly braces represent optional  {insert/update/delete} on  table_name//table name  for each row trigger_stmt//TRIGGER_STMT represents the trigger statement that will be executed

Use New.col_name to reference data in new data rows inserted or modified using INSERT and update using Old.col_name to refer to old data rows modified by update and DELETE statements
Example: If you want to change the value of one of the data columns before the new data row is inserted, you can use just create a trigger to write set new before the before statement. column= "XXXX";
Example: delimiter$
Create trigger Trigger_game before insert on student
For each row
Set nem.age=55;
end$
Delimiter

Events in MySQL
1: Role of regular cleanup of junk data
2: Data that generates a report at a specific time
3: Rotate the date data table, etc.

To turn on the event in MySQL:
Set Global Event_scheduler=off
Set Global event_scheduler=on//Open
Show variables like ' event_scheduler ';//view event Schedule
Grammar
CREATE EVENT [IF not EXISTS] Event_Name

On SCHEDULE schedule[on completion [NOT] preserve][enable | Disable][comment ' COMMENT ']do sql_statement;

Schedule

at TIMESTAMP [+ INTERVAL INTERVAL]

| Every INTERVAL [starts TIMESTAMP] [ENDS TIMESTAMP]

INTERVAL:

Quantity {Year | QUARTER | MONTH | Day | HOUR | MINUTE |          WEEK | SECOND | Year_month | Day_hour | Day_minute |          Day_second | Hour_minute | Hour_second | Minute_second}

5 days after the start of the daily emptying test table, one months after the execution stop:
CREATE EVENT E_test
On SCHEDULE every 1 day
Starts Current_timestamp + INTERVAL 5 Day
ENDS Current_timestamp + INTERVAL 1 MONTH
Do TRUNCATE TABLE test.aaa;

[on completion [NOT] PRESERVE] can set whether this event is executed once or persisted, default to not PRESERVE.
Empty the test table periodically every day (only once, and terminate the event when the task is completed):

CREATE EVENT E_test
On SCHEDULE every 1 day
On completion not PRESERVE
Do TRUNCATE TABLE test.aaa;

The Create event expire_web_sesison//function executes every four small time, removing the data that has been manipulated for a day

On schedule every 4 hour
Do
Delete from Web_session
where Last_visit every n interval//is used to give time intervals for periodic execution
The interval function is similar to DATEADD ()//function value can be day hour month
The DATEADD () function adds or subtracts a specified time interval from a date.

ALTER EVENT Event_Name

[on SCHEDULE SCHEDULE] [RENAME to New_event_name] [on completion [NOT] PRESERVE] [COMMENT ' COMMENT '] [ENABLE | Disable][do Sql_statement]

1) Temporary Shutdown event

ALTER EVENT e_test DISABLE;

2) Turn on event

ALTER EVENT e_test ENABLE;

3) Change the daily empty test table to 5 days to clear:

ALTER EVENT E_test
On SCHEDULE every 5 day;

Iv. Delete events (drop event)
The syntax is simple, as follows:

DROP EVENT [IF EXISTS] Event_Name
。。。。。。。 Not to be continued

Play MySQL with Me pack B, take off