(9): Mysql common functions summary _ MySQL

(9): Mysql common function summary bitsCN.com 1. String Functions

String functions is one of the most commonly used for Mysql.
(1 ):concat(s1,s2,..Sn)
Connects s1, s2,... sn as a string. For example:

SELECT CONCAT('|',2,'*','&');   #|2*&SELECT concat(50,'%');              #50%SELECT concat('abc',null)           #Null

(2 ):insert(str,x,y,instr)
Replace string 'str' from position x with string instr

SELECT INSERT('I love Jack',8,4,'Lada');    #start 1

(3 ):lower(str),upper(str)Case-sensitive conversion
(4 ):left(str,x),right(str,x)Returns the leftmost x characters of the string and the rightmost x characters of the string.nullNo string is returned.

SELECT left('My name is',2);        #MySELECT right('My name is',2);       #isSELECT left('Jack',NULL);               #Null

(5 ):lpad(str,n,pad),rpad(str,n,pad)Pad the string to fill the leftmost and rightmost sides of str until the length is n characters.

SELECT lpad('hi',5,'?')                 #???hiSELECT rpad('hi',10,'Jack')         #hiJackJack

(6 ):ltrim(str),rtrim(str)Remove spaces on the left and right of the str string.

SELECT ltrim(' hi');                        #hiSELECT rtrim('hi  ');                       #hi

(7 ):repeat(str,x)Returns the result of repeated str times.

SELECT repeat('*',10)                       #**********

(8 ):replace(str,a,b)Replace string a with string B

SELECT replace('L love Jan','J','B');   #L love Ban

(9 ):strcmp(str1,str2)Compare the ASCII values of str1 and str2

SELECT strcmp('A','a'),strcmp('a','a')  # 0 0

(10 ):trim(str)Removes the spaces at the beginning and end of the str string.
(11 ):substring(str,x,y)Returns the substring of the y character length at the x position in the str string.

SELECT substring ('good ', 2, 4) # ood, location [x, y]

II. numeric functions

(1 ):abs(x): Returns the absolute value of x.
(2 ):ceil(x): Returns the smallest integer value greater than x.

SELECT ceil(-0.8),ceil(0.8)         #0 1

(3 ):floor(x)Returns the largest integer less than x.

SELECT floor(-0.8),floor(0.8)       #-1 0

(4 ):mod(x,y)Returns the modulo of x/y.

SELECT mod(5,3)   #2

(5 ):rand(): Returns 0 ~ Random number between 1
SELECT rand (), round (10 * rand (), 2)
#0.87565597228301
#1.47
(6 ):round(x,y)Returns x rounded to y decimal places.
(7 ):truncate(x,y)Returns the result of x truncating y decimal places.

SELECT truncate(rand(),3)               #0.109

III. date and time functions

(1 ):curdate(),curtime(),now(): Returns the current date, current time, and current date and time.

SELECT curdate()                                #2013-10-23SELECT curtime()                                #13:37:04SELECT now()                            #2013-10-23 13:37:18

(2 ):week(date): Returns the week number of the year from date.

SELECT week(now())                          #42

(3 ):year(date),hour(time),minute(time),second(time)Returns the current year, hour, minute, and second.

SELECT year(now())                          #2013SELECT hour(now())                          #13SELECT minute(now())                        #53SELECT second(now())                        #24

(4 ):monthname(date)Returns the month name.

SELECT monthname(now())         #October

(5 ):date_format(date,fmt): Format the date value by string fmt. This function can be displayed by specified date.

SELECT date_format(now(),'%y-%m-%d  %H:%I:%s') 

% S, % s two-digit second (,..., 59)
% I, % I two-digit score (,..., 59)
% H two-digit hour, 24 hours (,..., 23)
% H two-digit hour, 12 hours (,..., 12)
% K, 24 hours (0, 1,..., 23)
% L hour in numeric format (1, 2,..., 12)
% T 24-hour time format (hh: mm: ss)
% R 12-hour time format (hh: mm: ss AM or hh: mm: ss PM)
% P AM or PM
% W name of each day in a week (Sunday, Monday,..., Saturday)
% A abbreviation of the name of each day in a week (Sun, Mon,..., Sat)
% D indicates the number of days in a month (00, 01,..., 31)
% E indicates the number of days in a month (1, 2,..., 31)
% D indicates the number of days in a month (1st, 2nd, 3rd ,...)
% W represents the number of days in a week (0 = Sunday, 1 = Monday,..., 6 = Saturday)
% J represents the number of days in a year in three digits (001,002,..., 366)
% U week (0, 1, 52), where Sunday is the first day of the week
% U week (0, 1, 52), where Monday is the first day of the week
% M month name (January, February,..., December)
% B abbreviated month name (January, February,..., December)
% M double-digit month (01, 02,..., 12)
% C indicates the month (1, 2,..., 12)
The year in the format of % Y.
The year in double-digit % y format.
% Direct value "%"

(6 ):datediff(date1,date2)Calculate the number of days for the difference between two dates

SELECT datediff('2015-8-8',now()) #654

IV. Process control

(1 ):if(value,t,f)If the value is true, t is returned; otherwise, f is returned.

Mysql> SELECT if (salary> 1500, 'high', 'Low') FROM salary; + ------------------------- + | if (salary> 1500, 'high', 'Low ') | + ------------------------- + | low | high | + ------------------------- + 4 rows in set

(2 ):ifnull(value1,value2)If value1 is not null, value1 IS returned; otherwise, value2 is returned. This function is generally used to replace Null values.

mysql> select ifnull(salary,0) from salary;+------------------+| ifnull(salary,0) |+------------------+| 1000 || 1500 || 2000 || 2500 ||0 |+------------------+5 rows in set

(3 ):case when [value1] then [result1] ..else[default] endIf value1 is true, result1 is returned; otherwise, default is returned.

Mysql> select case when salary> 1500 then' high 'else' low 'end from salary; + Lower + | case when salary> 1500 then' high 'else' low 'end | + ------------------------------------------------- + | low | high | low | + Lower + 5 rows in set

(4 ):case [expr] when [value1] then [result1] ..else[default] endIf expr is equal to value1, result1 is returned; otherwise, default is returned.

Mysql> select case salary when 1000 then 'lower 'When 2000 then' in 'else' high 'end from salary; + Lower + | case salary when 1000 then' lower 'When 2000 then' middle 'else' high 'end | + Lower + | low | high | medium | high | high | + ------------------------------------------------------------------- + 5 rows in set

Tips:

Mysql> select case when instr (type, 'preseason ') then 1 when instr (type, 'underseason') then 2 else 3 end from salary; + ------------------------------------------------------------------------------- + | case when instr (type, 'Previous season ') then 1 when instr (type, 'Next season ') then 2 else 3 end | + rows + | 1 | 1 | 2 | 3 | 1 | + rows + 5 rows in set

V. Other functions

(1 ):database(): Returns the name of the current database.
(2 ):version(): Returns the current database version.
(3 ):user(): Returns the current logon user name.
Other references.
Reference: mysql

BitsCN.com