Day in MySQL function things

Many built-in functions are available in MySQL

The Char_length (str) return value is the length of the string str, and the length of the unit is a character.        A multibyte character counts as a single character.    For one containing five two-byte character sets, the LENGTH () return value is 10, and the return value of Char_length () is 5.        CONCAT (STR1,STR2,...)    string concatenation if any one of the arguments is NULL, the return value is null.        Concat_ws (SEPARATOR,STR1,STR2,...) string concatenation (custom connector) Concat_ws () does not ignore any empty strings.    (All NULL is ignored, however). CONV (n,from_base,to_base) binary conversion For example: SELECT CONV (' A ', 16,2); Represents the conversion of a from 16 to a 2 binary string representation of format (X,D) that formats the number X as ' #,###,###.## ', retains the D-bit after the decimal point in a rounded manner, and returns the result as a string.        If D is 0, the result is returned without a decimal point, or with no fractional part. For example: SELECT FORMAT (12332.1,4);            The result is: ' 12,332.1000 ' Insert (STR,POS,LEN,NEWSTR) inserts the string at the specified position in str pos: to replace the position in the location Len: the length of the replacement        NEWSTR: New String Special: If the Pos exceeds the original string length, the original string is returned if Len exceeds the original string length, then the new string is completely replaced by the INSTR (STR,SUBSTR)    Returns the first occurrence of a substring of string str.    Left (Str,len) returns the substring character of the string Str from the beginning of the Len position.    LOWER (str) to lowercase UPPER (str) to uppercase LTRIM (str) returns the string str, whose boot space character is deleted. RTRIM (STR) returns the string str, and the trailing space character is deleted. SUBSTRING (Str,pos,len) Gets the string subsequence LOCATE (substr,str,pos) Gets the sub-sequence index position REPEAT (str,count) returns a duplicate string s        TR consists of a string of string str with a number equal to count.        If Count <= 0, an empty string is returned.    If STR or count is NULL, NULL is returned.    Replace (STR,FROM_STR,TO_STR) returns the string str and any string from_str that are substituted by the string to_str.    REVERSE (str) returns the string str, in reverse order and character order.    Right (Str,len) starts with the string str, and returns a subsequence space (n), which is a string consisting of n spaces, starting at the back of Len characters. SUBSTRING (Str,pos), SUBSTRING (str from POS) SUBSTRING (Str,pos,len), SUBSTRING (str from POS for len) format without len parameter from The string str returns a substring starting at position pos. The format with the Len parameter returns a substring of the same length as the Len character from the string str, starting at position pos. Use the from format as standard SQL syntax. You may also use a negative value for the POS. If so, the position of the substring starts at the POS character at the end of the string, not at the beginning of the string.        You can use a negative value for the POS in the following format function.            mysql> SELECT SUBSTRING (' quadratically ', 5);            ' ratically ' mysql> SELECT SUBSTRING (' Foobarbar ' from 4);        ' Barbar ' mysql> SELECT SUBSTRING (' quadratically ', 5,6);    ' Ratica ' mysql> SELECT SUBSTRING (' Sakila ',-3);            ' ila ' mysql> SELECT SUBSTRING (' Sakila ',-5, 3);            ' Aki ' mysql> SELECT SUBSTRING (' Sakila ' FROM-4 for 2); ' Ki ' TRIM ([{BOTH | Leading | TRAILING} [REMSTR] from] str) TRIM (remstr from] str) returns the string str, where all remstr prefixes and/or suffixes have been deleted. If none of the classifier both, leadin, or trailing is given, it is assumed to be both.        REMSTR is optional and can be removed without specifying a space.                mysql> SELECT TRIM (' Bar ');                ' Bar ' mysql> SELECT TRIM (leading ' x ' from ' xxxbarxxx ');                ' Barxxx ' mysql> SELECT TRIM (BOTH ' x ' from ' xxxbarxxx ');                ' Bar ' mysql> SELECT TRIM (TRAILING ' xyz ' from ' barxxyz '); ' Barx ' part built-in functions

Select Date_format (' 2009-10-01 22:30:10 ', '%W%M%Y ') # Thursday October 2009select date_format (' 2014-10-10 22:24:10 ', '%H :%i:%s ') # 22:24:10select date_format (' 1996-12-20 22:24:10 ', '%d%y%a%d%m%b%j ') # 20th 355select Fri Ormat (' 1996-12-20 ', '%x%v ') # 1996 51select date_format (' 2006-06-00 ', '%d ') # 00# prepare table and record CREATE TABLE blog (    ID int Prima Ry Key  auto_increment,    name char (+),    sub_time datetime) INSERT INTO blog (name,sub_time) VALUES    (' 1th ', ' 2015-03-01 11:31:21 '), ('    2nd ', ' 2015-03-11 16:31:21 '),    (' 3rd ', ' 2016-07-01 10:21:31 '),    (' 4th ', ' 2016-07-22 09:23:21 '),    (' 5th ', ' 2016-07-23 10:11:11 '),    (' 6th ', ' 2016-07-25 11:21:31 '),    (' 7th ', ' 2017-03-01 15:33:21 '),    (' 8th ', ' 2017-03-01 17:32:21 '),    (' 9th ', ' 2017-03-01 18:31:21 '); Select Date_format ( Sub_time, '%y-%m '), COUNT (1) from blog GROUP bydate_format (sub_time, '%y-%m ') # 2015-03 * 2016-07 4#    2017-03    3

# Intercept String Select substring (' zxcvbnm ', 5) # Bnmselect substring (' zxcvbnm ', -5,3) # cvb# Returns the number of character types Select Char_length (' Chinese ') # * The number of bytes returned is select Length (' Chinese ') # 6# splicing Select Concat (' Alex ', ' Zxcv ', ' La La la ') # alexzxcv la la-la-concat (' Alex ', ' Zxcv ', ' La La ', NULL) #null  concatenation of NULL returns null# formatting # reserved decimal point Select Format (132562.258,4) # 132,562.2580

One, Custom function

#！！！ Attention!!! #函数中不要写sql语句 (otherwise error), the function is just a function, is a function that is applied in SQL # to want to be in begin...end ... Write SQL, use the stored procedure

# Custom functions Create function fun1 (i int, x int)    returns int begin     DECLARE sum int DEFAULT 0;    Set sum = i+x;    return (sum); Endselect fun1 (100,50)

Second, delete the function

Drop function Func_name;

Third, the executive function

View Code

Transaction

Transactions are used to manipulate multiple SQL for some operations as atomic, and once an error occurs, it can be rolled back to its original state, guaranteeing database data integrity.

CREATE TABLE user (    ID int primary key auto_increment,    name char (+),    balance int) insert INTO user (Name,bala NCE) VALUES (' WSB ', +), (' Egon ', ' + '), (' Ysb ', ' 1000 ') # Atomic operation start transaction #开启事务update user Set balance =  = ' WSB ' update user set balance = 1010 WHERE name = ' Egon ' update user set balance = 1090 WHERE name = ' YSB ' Commit # Commit Transaction # exception occurred, rollback to initial state start transaction Update user Set balance = # WHERE name = ' WSB ' update user set balance = 1010 where Nam E = ' Egon ' uppdate user set balance = 1090 WHERE name = ' YSB ' ROLLBACK   

Day in MySQL function things