MySQL

Basic functions and triggers in MySQL stored procedures

Common functions of MySQL stored procedures

1. String class CHARSET(str) // returns the string character set CONCAT (string2 [,… ] ) // Connection string INSTR (string,substring) // returns the location where substring first appeared in string. No return 0 exists LCASE (string2) // converted to lowercase LEFT (string2,length) // Takes length characters from the left side of string2 LENGTH (string) //string length LOAD_FILE (file_name) // Reads contents from a file LOCATE (substring, string [,start_position]) is the same as INSTR, but the starting position can be specified LPAD (string2,length,pad) // Repeat pad at the beginning of string until the string length is length LTRIM (string2) // Remove the front space REPEAT (string2,count) // Repeat count times REPLACE (str,search_str,replace_str) // Replace search_str with replace_str in str RPAD (string2,length,pad) // pad is used after str until the length is length RTRIM (string2) // Remove back-end Spaces STRCMP (string1,string2) // Character-by-character comparison of two string sizes, SUBSTRING (str, position [,length]) // Starting with position of str, take length characters, Note: When dealing with strings in mysql, the default is the first character subscript 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) // removes the specified character at the specified position UCASE (string2) // converted to uppercase RIGHT(string2,length) // Takes the last length characters of string2 SPACE(count) // generates count Spaces

2. The math class

ABS (number2) // Absolute value BIN (decimal_number) // Base 10 to base 2 CEILING (number2) // Round up CONV(number2,from_base,to_base) // base conversion FLOOR (number2) // Round down FORMAT (number,decimal_places) // Reserve decimal places HEX (DecimalNumber) // turn 106 Note: in HEX(), if a string can be passed in, its ASC-11 code is returned; for example, HEX(‘DEF’) returns 4142143 You can also pass in a decimal integer and return its decimal code, such as HEX(25), which returns 19 LEAST (number , number2 [,..] ) // Find the minimum MOD (numerator,denominator) // Remainder POWER (number,power) // Index RAND([seed]) // Random Numbers ROUND (number [,decimals]) //4 rounded to 5,decimals is the decimal place] Note: Return types are not all integers, such as: (1) Becomes the plastic value by default

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) Can set the decimal number and 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) // returns the symbol, plus or minus or 0 SQRT(number2) // Square root

3. Date and time

ADDTIME (date2,time_interval) // Add time_interval to date2 CONVERT_TZ (datetime2,fromTZ,toTZ) // Change time zone CURRENT_DATE () // Current date CURRENT_TIME () // Current time CURRENT_TIMESTAMP () // Current timestamp DATE (datetime) // returns the date portion of datetime DATE_ADD (date2, INTERVAL d_value d_type) // Add date or time to date2 DATE_FORMAT (datetime,FormatCodes) // Displays datetime using formatcodes format DATE_SUB (date2, INTERVAL d_value d_type) // Subtract 1 time from date2 DATEDIFF (date1,date2) // Difference between the two dates 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) // Extracts the specified part of the date from date MAKEDATE (year,day) // Gives the year and the day in the middle of the year, and generates the date string MAKETIME (hour,minute,second) // Generate time string MONTHNAME (date) // English month name NOW () // Current time SEC_TO_TIME (seconds) // Seconds converted into time STR_TO_DATE (string,format) // string conversion time, displayed in format format TIMEDIFF (datetime1,datetime2) // Two time differences TIME_TO_SEC (time) // RPM] WEEK (date_time [,start_of_week]) // Weeks YEAR (datetime) // Year DAYOFMONTH(datetime) // The day of the month HOUR (datetime) / / hour LAST_DAY(date) // The last date of the month of date MICROSECOND (datetime) / / ms MONTH (datetime) / / month MINUTE (datetime) / / min

Attachment: Types available 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 the 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 ;

Calling a stored procedure:

CALL PROC_TEST('USER',20);

Delete 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 delimiter. It is basically similar to the way other objects in MySQL are named. {BEFORE | AFTER} — triggers have a time set for execution: they can be set before or after the event. INSERT | UPDATE | DELETE} — you can also set triggered events: they can be triggered during the execution of insert, update, or delete. ON < The name of the table > A trigger belongs to a table: an insert, update, or delete operation on this table causes the trigger to be activated. We cannot assign two triggers to the same event in the same table. FOR EACH ROW — Interval of trigger execution: the FOR EACH ROW clause notifies the trigger to perform an action once every other row, rather than once for the entire table. < Trigger SQL statement > — The trigger contains the SQL statement to be fired: The statement here can be any valid statement, including compound statements, but the statement here is restricted to one of the functions.

You must have considerable permissions to create triggers (CREATE TRIGGER). If you are already an Root user, that is enough. This is different from the SQL standard.

Create the trigger :t_afterinsert_on_tab1

Effect: Add tab1 table record to tab2 table automatically

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 Role: Automatically delete the corresponding record in tab2 table after deleting the record in tab1 table

+-----------------------+
| 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)

0

Delete trigger:

+-----------------------+
| 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)

1