How to learn basic functions and triggers in the MySQL stored procedure _ MySQL

This article describes how to learn basic functions and triggers in the MySQL stored procedure, including how to create and delete triggers. For more information, see Common functions of MySQL stored procedures

I. string type

CHARSET (str) // returns the string character set CONCAT (string2 [,...]) // connection string INSTR (string, substring) // returns the position of the first occurrence of the substring in the string. If no position exists, 0 LCASE (string2) is returned. // converts the value to lower-case LEFT (string2, length) // take the length (string) character from the left of string2 // string LENGTH LOAD_FILE (file_name) // read the content from the file LOCATE (substring, string [, start_position]) same as INSTR, but you can specify the start position LPAD (string2, length, pad) // repeat pad at the start of string until the string length is lengthLTRIM (string2) // remove front-end space REPEAT (string2, count) // REPEAT count REPLACE (str, search_str, replace_str) // REPLACE search_strRPAD (string2, length, pad) with replace_str in str) // use pad after str until the length is lengthRTRIM (string2) // remove backend spaces STRCMP (string1, string2) // compare the size of two strings by character, SUBSTRING (str, position [, length]) // starts from the position of str and takes length characters,

Note: when processing strings in mysql, the Default subscript of the first character 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 UCASE (string2) at the specified position. // Convert it to uppercase RIGHT (string2, length) // take string2's last length character SPACE (count) // Generate count spaces

II. Mathematics

ABS (number2) // absolute value

BIN (decimal_number) // Convert decimal to binary CEILING (number2) // rounded up to CONV (number2, from_base, to_base) // Convert to base (number2) // rounded down the entire FORMAT (number, decimal_places) // retain the number of decimal places HEX (DecimalNumber) // Convert to hexadecimal notation. note: a string can be input in HEX, return its ASC-11 code, such as HEX ('def ') returns 4142143 can also be passed into a decimal integer, return its hexadecimal encoding, such as HEX (25) returns 19 LEAST (number, number2 [,...]) // Evaluate the minimum MOD (numerator, denominator) // Evaluate the remainder POWER (number, power) // Evaluate the exponent RAND ([seed]) // random number ROUND (number [, decimals]) // rounding, decimals is the number of decimal places]

Note: the return type is not an integer, for example:
(1) the default value is integer.

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) the number of decimal places can be set to 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 SIGN, positive and negative or 0
SQRT (number2) // Square

III. date and time

ADDTIME (date2, time_interval) // add time_interval to date2CONVERT_TZ (datetime2, fromTZ, toTZ) // Convert the time zone CURRENT_DATE () // Current date CURRENT_TIME () // Current Time CURRENT_TIMESTAMP () // Current timestamp DATE (datetime) // returns the DATE part of datetime DATE_ADD (date2, INTERVAL d_value d_type) // add the DATE or time DATE_FORMAT (datetime, FormatCodes) to date2) // Display datetimeDATE_SUB (date2, INTERVAL d_value d_type) in formatcodes format // subtract a time DATEDIFF (date1, date2) from date2 // Two date difference days (date) // return the day DAYNAME (date) of the date // English week DAYOFWEEK (date) // Week (1-7), 1 is Sunday DAYOFYEAR (date) // the day of the year EXTRACT (interval_name FROM date) // EXTRACT the specified part of the date FROM the date MAKEDATE (year, day) // give the day of the year and the year, generate date string MAKETIME (hour, minute, second) // Generate time string MONTHNAME (date) // English month name NOW () // Current Time SEC_TO_TIME (seconds) // Convert seconds to time STR_TO_DATE (string, format) // string to time, display TIMEDIFF (datetime1, datetime2) in format // Two time difference TIME_TO_SEC (time) // Time to seconds] WEEK (date_time [, start_of_week]) // week year (datetime) // year dayofmonth (datetime) // day of month HOUR (datetime) // hour LAST_DAY (date) // The last date of the MONTH of date MICROSECOND (datetime) // microsecond month (datetime) // month minute (datetime) // MINUTE

Appendix: Available types 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(20),NUM INT)BEGIN  SELECT * FROM TABLE_NAME LIMIT NUM;END//DELIMITER ;

Call the stored procedure:

CALL PROC_TEST('USER',20);

Delete stored procedure:

DROP PROCUDURE productpricing

2. create a trigger
~~ Syntax ~~
CREATE TRIGGER

-- You must have considerable permissions to CREATE a TRIGGER. if you are a Root user, this is enough. This is different from the SQL standard.

Create trigger: t_afterinsert_on_tab1

Purpose: automatically add a record to the tab2 table after adding a record to the tab1 table

DROP TRIGGER IF EXISTS `t_afterinsert_on_tab1`; CREATE TRIGGER t_afterinsert_on_tab1 AFTER INSERT ON `tab1` FOR EACH ROW BEGIN   insert into tab2(tab2_id) values(new.tab1_id); END;

Create trigger: t_afterdelete_on_tab1
Purpose: delete the records in table tab1 and automatically delete the records in table tab2.

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;

Delete trigger:

DROP TRIGGER [schema_name.]trigger_name；

The above is the basic functions and triggers in the MySQL stored procedure. tutorial _ MySQL content. For more information, see The PHP Chinese website (www.php1.cn )!