Mysql triggers, fuzzy search, stored procedures, built-in functions

Mysql triggers, fuzzy searches, stored procedures, and built-in functions originally thought that some of Mysql's knowledge was almost the same, but I found that I forgot everything when I used it in projects. Now let's review it and take a note.

Trigger

• View all triggers

  SELECT * FROM information_schema.`TRIGGERS`;

• Trigger application

  Background: two tables:

  Create a trigger to insert data in yyd_table. if yyd _ is used, data insertion is also triggered.

  CREATE TRIGGER yyd_tri AFTER INSERT ON yyd_table FOR EACH ROWBEGININSERT INTO yyd_name VALUES (NULL,"name_yyd");END;

  Execution result:

  NextWhen inserting yyd_name, change the name to 4:

  CREATE TRIGGER yyd_tri1 BEFORE INSERT ON yyd_name FOR EACH ROWBEGINSET @x = "123321";SET NEW.name = "4";END;

• Delete trigger

  DROP triggeryyd_tri;

• Possible problems

  If you insert/update the data you just inserted in the trigger, it will cause a loop call. For example:

  create trigger test before update on test for each row

  update test set NEW.updateTime = NOW() where id=NEW.ID;

  END

  Set should be used:

  create trigger test before update on test for each row set NEW.updateTime = NOW(); END

• Syntax

  CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt

  Trigger_time is the time when the program is triggered. It can be BEFORE or AFTER

  Trigger_event indicates the type of statements used to activate the trigger program. Trigger_event can be one of the following values:

• INSERT: The Trigger program is activated when a new row is inserted into the table, for example, through INSERT, load data, and REPLACE statements.
• UPDATE: The Trigger program is activated when a row is changed, for example, through the UPDATE statement.
• DELETE: The Trigger program is activated when a row is deleted from the table, for example, through the DELETE and REPLACE statements.

  Fuzzy search

  SELECT field FROM table WHERE a field Like condition

• Matching mode
• %

  : Represents any 0 or multiple characters. It can match characters of any type and length.

  SELECT * FROM [user] WHERE u_name LIKE '% 3%'

  We will find all records with "3", such as "3 Zhang", "3 Zhang", "3 Zhang Mao", and "3 Tang Sanzang.

  In addition, if you need to find records with "three" and "cat" in u_name, use the and condition.

  SELECT * FROM [user] WHERE u_name LIKE '% 3%' AND u_name LIKE '% cat %'

• []: Represents one of the characters listed in parentheses (similar to regular expressions ). Specifies a character, string, or range. the matching object must be one of them.
• [^]: Represents a single character not listed in parentheses. The value is the same as [], but it requires that the matched object be any character other than the specified character.
• _

  : Represents any single character. Matches any character. it is often used to limit the character length of an expression.

  SELECT * FROM [user] WHERE u_name LIKE '_ 3 _'

  Only find that the u_name such as "Tang Sanzang" is three characters and the middle word is "three;

  SELECT * FROM [user] WHERE u_name LIKE 'three __';

  Find out that the name of the "three-legged cat" is three characters and the first word is "three;

  Stored Procedure

• Format

  mysql> DELIMITER //mysql> CREATE PROCEDURE proc1(OUT s int)-> BEGIN -> SELECT COUNT(*) INTO s FROM user;-> END -> //mysql> DELIMITER ;

• Here we need to note that DELIMITER // and DELIMITER; are two sentences, DELIMITER is the meaning of the DELIMITER, here the DELIMITER is replaced //.
• The stored procedure may have input, output, and input and output parameters as needed. here, there is an output parameter s, whose type is int type. if there are multiple parameters, separate them.

• Start and END of the program are identified by BEGIN and END.

  MySQL Stored Procedure parameters are used IN the definition of stored procedures. There are three parameter types: IN, OUT, and INOUT. the format is as follows:

  Create procedure ([[IN | OUT | INOUT] parameter name data class...])

  IN Input parameters:Indicates that the value of this parameter must be specified when the stored procedure is called. modifying the value of this parameter in the stored procedure cannot be returned, which is the default value.

  OUT Output parameters:This value can be changed within the stored procedure and can be returned

  INOUT Input and output parameters:Can be changed and returned.

  For example:

  mysql > DELIMITER //mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)-> BEGIN -> SELECT p_in; -> SET p_in=2; -> SELECT p_in; -> END; -> //mysql > DELIMITER ;

  Result:

  mysql > SET @p_in=1;mysql > CALL demo_in_parameter(@p_in);+------+| p_in |+------+| 1| +------+ +------+| p_in |+------+| 2| +------+ mysql> SELECT @p_in;+-------+| @p_in |+-------+|1|+-------+

• Variable

  DECLARE variable_name [,variable_name...] datatype [DEFAULT value];

  Datatype is the data type of MySQL, such as int, float, date, varchar (length)

  For example:

  DECLARE l_int int unsigned default 4000000;DECLARE l_numeric number(8,2) DEFAULT 9.95;DECLARE l_date date DEFAULT '1999-12-31';DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';

  Assignment:

  SET variable name = expression value [, variable_name = expression...]

  User variable:

  mysql > SELECT 'Hello World' into @x;mysql > SELECT @x;+-------------+| @x|+-------------+| Hello World |+-------------+mysql > SET @y='Goodbye Cruel World';mysql > SELECT @y;+---------------------+| @y|+---------------------+| Goodbye Cruel World |+---------------------+ mysql > SET @z=1+2+3;mysql > SELECT @z;+------+| @z |+------+|6 |+------+

  Variables and stored procedures:

  mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');mysql > SET @greeting='Hello';mysql > CALL GreetWorld( );+----------------------------+| CONCAT(@greeting,' World') |+----------------------------+|Hello World |+----------------------------+mysql> CREATE PROCEDURE p1() SET @last_procedure='p1';mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_proc);mysql> CALL p1( );mysql> CALL p2( );+-----------------------------------------------+| CONCAT('Last procedure was ',@last_proc|+-----------------------------------------------+| Last procedure was p1 |+-----------------------------------------------+

• Variable scope:

  mysql > DELIMITER //mysql > CREATE PROCEDURE proc3()-> begin -> declare x1 varchar(5) default 'outer';-> begin -> declare x1 varchar(5) default 'inner';-> select x1;-> end;-> select x1;-> end;-> //mysql > DELIMITER ;

  Internal variables have a higher priority in the scope of their scopes. when the execution ends, the internal variables disappear and are out of the scope, and the variables are no longer visible, the declarative variable cannot be found no longer outside the stored procedure, but you can save it by using the out parameter or assigning its value to the session variable.

• Query stored procedures

• Select name from mysql. proc where db = 'database name'; select routine_name from information_schema.routines where routine_schema = 'database name'; show procedure status where db = 'database name ';

  • Delete stored procedure

  DROP PROCEDURE

  • If-then-else Statement

  mysql > DELIMITER //mysql > CREATE PROCEDURE proc2(IN parameter int)-> begin -> declare var int;-> set var=parameter+1;-> if var=0 then -> insert into t values(17);-> end if;-> if parameter=0 then -> update t set s1=s1+1;-> else -> update t set s1=s1+2;-> end if;-> end;-> //mysql > DELIMITER ;

  • Case Statement

  mysql > DELIMITER //mysql > CREATE PROCEDURE proc3 (in parameter int)-> begin -> declare var int;-> set var=parameter+1;-> case var-> when 0 then -> insert into t values(17);-> when 1 then -> insert into t values(18);-> else -> insert into t values(19);-> end case;-> end;-> //mysql > DELIMITER ;

  • While... end while statement

  mysql > DELIMITER //mysql > CREATE PROCEDURE proc4()-> begin -> declare var int;-> set var=0;-> while var<6 do-> insert into t values(var);-> set var=var+1;-> end while;-> end;-> //mysql > DELIMITER ;

  • Repeat · end repeat statement

  mysql > DELIMITER //mysql > CREATE PROCEDURE proc5 ()-> begin -> declare v int;-> set v=0;-> repeat-> insert into t values(v);-> set v=v+1;-> until v>=5-> end repeat;-> end;-> //mysql > DELIMITER ;

  Similar to do... While statement.

  • Loop... end loop statement

  mysql > DELIMITER //mysql > CREATE PROCEDURE proc6 ()-> begin -> declare v int;-> set v=0;-> LOOP_LABLE:loop-> insert into t values(v);-> set v=v+1;-> if v >=5 then -> leave LOOP_LABLE;-> end if;-> end loop;-> end;-> //mysql > DELIMITER ;

  The loop does not require the initial conditions. This is similar to the while loop, and does not require the end condition like the repeat loop. the leave statement is used to exit the loop.

  • ITERATE Iteration

  mysql > DELIMITER //mysql > CREATE PROCEDURE proc10 ()-> begin -> declare v int;-> set v=0;-> LOOP_LABLE:loop-> if v=3 then -> set v=v+1;-> ITERATE LOOP_LABLE;-> end if;-> insert into t values(v);-> set v=v+1;-> if v>=5 then -> leave LOOP_LABLE;-> end if;-> end loop;-> end;-> //mysql > DELIMITER ;

  You can refer to the compound statement label to start a compound statement.

  Built-in functions

  • String

  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 is returned.

  LCASE (string2) // converts it to lowercase

  LEFT (string2, length) // take the length from the LEFT of string2

  LENGTH (string) // string LENGTH

  LOAD_FILE (file_name) // read content from the file

  LOCATE (substring, string [, start_position]) is the same as INSTR, but the start position can be specified.

  LPAD (string2, length, pad) // repeat pad to start with string until the string length is length

  LTRIM (string2) // Remove leading spaces

  REPEAT (string2, count) // REPEAT count times

  REPLACE (str, search_str, replace_str) // REPLACE search_str with replace_str in str

  RPAD (string2, length, pad) // use pad after str until the length is length.

  RTRIM (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.

  TRIM ([[BOTH | LEADING | TRAILING] [padding] FROM] string2) // remove the specified character FROM the specified position

  UCASE (string2) // converts to uppercase

  RIGHT (string2, length) // gets the last length character of string2

  SPACE (count) // Generate count spaces

  • Digital Mathematics

  ABS (number2) // absolute value

  BIN (decimal_number) // Convert decimal to binary

  CEILING (number2) // rounded up

  CONV (number2, from_base, to_base) // hexadecimal conversion

  FLOOR (number2) // round down

  FORMAT (number, decimal_places) // number of reserved decimal places

  HEX (DecimalNumber) // Convert to hexadecimal

  Note: HEX () can be passed in a string, return its ASC-11 code, such as HEX ('def ') returns 4142143 can also be passed in a decimal integer, return its hexadecimal encoding, for example, HEX (25) returns 19

  LEAST (number, number2 [,...]) // calculates the minimum value.

  MOD (numerator, denominator) // Evaluate the remainder

  POWER (number, power) // exponent

  RAND ([seed]) // random number

  ROUND (number [, decimals]) // rounding, decimals is the number of decimal places]

  • Date and time

  ADDTIME (date2, time_interval) // add time_interval to date2

  CONVERT_TZ (datetime2, fromTZ, toTZ) // Convert the time zone

  CURRENT_DATE () // Current date

  CURRENT_TIME () // current time

  CURRENT_TIMESTAMP () // Current timestamp

  DATE (datetime) // return the DATE part of datetime

  DATE_ADD (date2, INTERVAL d_value d_type) // add a date or time in date2

  DATE_FORMAT (datetime, FormatCodes) // Display datetime in formatcodes format

  DATE_SUB (date2, INTERVAL d_value d_type) // subtract a time from date2

  DATEDIFF (date1, date2) // Two date differences

  DAY (date) // returns the DAY of the date

  DAYNAME (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 date

  MAKEDATE (year, day) // specifies the day of the year and year to generate a date string.

  MAKETIME (hour, minute, second) // Generate a time string

  MONTHNAME (date) // name of the English month

  NOW () // current time

  SEC_TO_TIME (seconds) // converts seconds to time

  STR_TO_DATE (string, format) // Convert string to time, which is displayed in format

  TIMEDIFF (datetime1, datetime2) // Two time difference

  TIME_TO_SEC (time) // time to seconds]

  WEEK (date_time [, start_of_week]) // WEEK

  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) // MICROSECOND

  MONTH (datetime) // MONTH

  MINUTE (datetime) // return the MINUTE sign, positive or negative or 0

  SQRT (number2) // Square

  I am the dividing line of tiantiao

  Reference: http://www.cnblogs.com/GT_Andy/archive/2009/12/25/1921914.html

  Reference: http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html

  Reprinted please indicate the source: http://www.cnblogs.com/yydcdut/