Learning tutorials on basic functions and triggers in Mysql stored procedures _mysql

Common functions for MySQL stored procedures

A. String class
CHARSET (str)//return string character set
CONCAT (string2 [,...])//connection string
INSTR (string, substring)//Returns the position that substring first appeared in string and does not exist return 0
LCASE (string2)//Convert to lowercase
Left (string2, length)//The length characters from the left-hand side of the string2
Length (string)//string lengths
Load_file (file_name)//read content from File
LOCATE (substring, string [, Start_position]) with InStr, but you can specify the start position
Lpad (string2, length, pad)//repeat pad at beginning of string until length of string
LTRIM (string2)//Remove front space
REPEAT (string2, count)//Repeat Count times
Replace (str, SEARCH_STR, REPLACE_STR)//replaced with REPLACE_STR in STR search_str
Rpad (string2, length, pad)//after STR supplemented with pad until length
RTRIM (string2)//Remove back-end spaces
STRCMP (string1, string2)//character comparison two string size,
SUBSTRING (str, position [, length])//begins with the position of STR, takes the length character,
Note: When handling strings in MySQL, the default first character subscript is 1, that is, the parameter position must be greater than or equal to 1

mysql> Select substring (' ABCD ', 0,2);

+-----------------------+
| substring (' ABCD ', 0,2) |
+-----------------------+
| |
+-----------------------+
1 row in Set (0.00 sec)


mysql> Select substring (' ABCD ', 1,2);
+-----------------------+
| substring (' ABCD ', 1,2) |
+-----------------------+
| ab |
+-----------------------+
1 row in Set (0.02 sec)

TRIM ([[both| Leading| Trailing] [padding] from]string2)//Remove the specified character from the specified position
UCASE (string2)//Convert to uppercase
Right (String2,length)//Fetch string2 last length character
Space (count)//Generate Count spaces

Two. Mathematics class

ABS (NUMBER2)//Absolute value
BIN (Decimal_number)//decimal Turn binary
CEILING (number2)//Upward rounding
CONV (Number2,from_base,to_base)//in-process conversion
FLOOR (NUMBER2)//Down rounding
FORMAT (number,decimal_places)//Reserved Decimal places
HEX (Decimalnumber)//Hex
Note: HEX () can pass in a string, then return its ASC-11 code, such as HEX (' DEF ') return 4142143
You can also pass in a decimal integer, returning its hexadecimal encoding, such as Hex (25) Returning 19
Least (number, number2 [,..])//Minimum value
MOD (numerator, denominator)//remainder
Power (number, Power)//Index
RAND ([seed])//random number
ROUND (number [, decimals])//rounded, decimals to decimal places]
Note: The return type is not all integers, such as:
(1) Change the default to a cosmetic value

Mysql> Select round (1.23);

+-------------+
| round (1.23) |
+-------------+
| 1 |
+-------------+
1 row in Set (0.00 sec)

mysql> select round (1.56);
+-------------+
| round (1.56) |
+-------------+
| 2 |
+-------------+
1 row in Set (0.00 sec)

(2) You can set the number of decimal places, return floating-point data

Mysql> Select round (1.567,2);

+----------------+
| round (1.567,2) |
+----------------+
| 1.57 |
+----------------+
1 row in Set (0.00 sec)

SIGN (NUMBER2)//return symbol, plus or minus 0
SQRT (NUMBER2)//Open Square

Three. Date Time class

Addtime (Date2, Time_interval)//Add Time_interval to Date2
Convert_tz (DateTime2, Fromtz, Totz)//Convert time zone
Current_date ()//Current date
Current_time ()//Current time
Current_timestamp ()//Current time stamp
Date (datetime)//Return datetime part
Date_add (Date2, INTERVAL d_value d_type)//Add date or time in Date2
Date_format (datetime, Formatcodes)//display datetime using Formatcodes format
Date_sub (Date2, INTERVAL d_value d_type)//Minus one time on date2
DATEDIFF (Date1, Date2)//Two date difference
Day (date)//days of return date
Dayname (date)/English week
DayOfWeek (date)//week (1-7), 1 for Sunday
DayOfYear (date)//day of the year
EXTRACT (interval_name from date)//To extract the specified portion of a date from
Makedate (year, day)//gives the first days of years and years, generating a date string
Maketime (hour, minute, second)//Generate time series
MonthName (date)//English month name
Now ()//Current time
Sec_to_time (seconds)//sec turns into time
Str_to_date (string, format)//String turn to time, displayed in format format
Timediff (datetime1, datetime2)//two time lag
Time_to_sec (time)//times in seconds]
WEEK (Date_time [, Start_of_week])//first few weeks
Year (datetime)/Years
DayOfMonth (DateTime)//day of the month
HOUR (DateTime)//hour
Last date of the month of Last_day (date)//date
Microsecond (datetime)/microsecond
MONTH (DateTime)//month
MINUTE (datetime)/min

Attachment: Types that can be used in interval
Day, Day_hour, Day_minute, Day_second, HOUR, Hour_minute, Hour_second, MINUTE, Minute_second,month, SECOND, year

MySQL stored procedures and triggers
1. Create a stored procedure

DELIMITER//
DROP PROCEDURE IF EXISTS ' proc_test '//
CREATE PROCEDURE ' proc_test ' (table_name VARCHAR), NUM INT )
BEGIN
  SELECT * FROM table_name LIMIT NUM;
end//
DELIMITER;

To invoke a stored procedure:

Call Proc_test (' USER ', 20);

To delete a stored procedure:

DROP procudure productpricing

2. Create triggers
~ ~ Grammar ~ ~
CREATE TRIGGER < trigger name >-the trigger must have a name, up to 64 characters, and may be followed by a separator. It is basically like the naming of other objects in MySQL.
{before | After}--the trigger has an execution time setting: It can be set before or after the event occurs.
{INSERT | UPDATE | DELETE}-can also set trigger events: They can be triggered during an insert, update, or delete process.
On < table name >--triggers belong to a table: triggers are activated when an INSERT, update, or delete operation is performed on this table. We cannot schedule two triggers for the same event on the same table.
For each row--the execution interval of a trigger: the For each row clause notifies the trigger to perform an action every other row instead of executing the entire table once.
< trigger SQL Statement >-the trigger contains the SQL statement to be fired: The statement here can be any legitimate statement, including compound statements, but the statements here are subject to the same restrictions as the functions.

You must have considerable permissions to create triggers (create TRIGGER), if you are already root, then that's enough. This is different from the SQL standard.

Create Trigger: T_AFTERINSERT_ON_TAB1

Effect: Increase the TAB1 table record and automatically add the record to the TAB2 table

DROP TRIGGER IF EXISTS ' t_afterinsert_on_tab1 '; 
CREATE TRIGGER t_afterinsert_on_tab1 after 
insert on ' tab1 ' for each 
ROW 
BEGIN
   inserts into TAB2 (tab2_id ) VALUES (new.tab1_id); 
End;

Create Trigger: T_AFTERDELETE_ON_TAB1
Role: Delete tab1 table records automatically tab2 the corresponding records in the table deleted

DROP TRIGGER IF EXISTS ' t_afterdelete_on_tab1 '; 
CREATE TRIGGER ' t_afterdelete_on_tab1 ' after 
delete on ' tab1 ' for each 
ROW 
BEGIN
   DELETE from ' tab2 ' where tab2_id=old.tab1_id; 
End;

To delete a trigger:

DROP TRIGGER [schema_name.] Trigger_name;