(9):Mysql常用函数小结_MySQL

一、String Functions

String functions is one of the most commonly used for Mysql.
(1):concat(s1,s2,..Sn)
连接s1,s2,...sn为一个字符串。如：

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

(2):insert(str,x,y,instr)
将字符串str从第x位置开始，y个字符长的子串替换为字符串instr

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

(3):lower(str)、upper(str)大小写转换
(4):left(str,x)、right(str,x)分别返回字符串最左边的x个字符和最右边的x个字符，如果第二个参数的null则不返回任何字符串。

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对str最左边和最右边进行填充，直到长度为n个字符长度。

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

(6):ltrim(str)、rtrim(str)去掉字符串str左侧和右侧的空格。

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

(7):repeat(str,x)返回str重复x次的结果

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

(8):replace(str,a,b)用字符串b替换str的所有出现的字符串a

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

(9):strcmp(str1,str2)比较字符串str1和str2的ASCII码值大小

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

(10):trim(str)去掉字符串str首尾空格
(11):substring(str,x,y)返回从字符串str中第x位置其y个字符长度的子串。

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

二、数值函数

(1):abs(x):返回x的绝对值
(2):ceil(x):返回大于x的最小整数值

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

(3):floor(x)返回小于x的最大整数值

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

(4):mod(x,y)返回x/y的模

SELECT mod(5,3)   #2

(5):rand():返回0~1之间的随机数
SELECT rand(),round(10*rand(),2)
#0.87565597228301
#1.47
(6):round(x,y)返回参数x的四舍五入的有y位小数的值
(7):truncate(x,y)返回数字x截断y位小数的结果

SELECT truncate(rand(),3)               #0.109

三、日期和时间函数

(1):curdate()、curtime()、now()：返回当前日期、当前时间、当前日期时间

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

(2):week(date):返回日期date为一年中的第几周

SELECT week(now())                          #42

(3):year(date)、hour(time)、minute(time)、second(time)返回当前年份、小时、分钟、秒

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

(4):monthname(date)返回月份名

SELECT monthname(now())         #October

(5):date_format(date,fmt):按字符串fmt格式化日期date值，此函数能够按指定日期显示

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

%S, %s 两位数字形式的秒（ 00,01, ..., 59）
%I, %i 两位数字形式的分（ 00,01, ..., 59）
%H 两位数字形式的小时，24 小时（00,01, ..., 23）
%h 两位数字形式的小时，12 小时（01,02, ..., 12）
%k 数字形式的小时，24 小时（0,1, ..., 23）
%l 数字形式的小时，12 小时（1, 2, ..., 12）
%T 24 小时的时间形式（hh:mm:ss）
%r 12 小时的时间形式（hh:mm:ss AM 或hh:mm:ss PM）
%p AM或PM
%W 一周中每一天的名称（Sunday, Monday, ..., Saturday）
%a 一周中每一天名称的缩写（Sun, Mon, ..., Sat）
%d 两位数字表示月中的天数（00, 01,..., 31）
%e 数字形式表示月中的天数（1, 2， ..., 31）
%D 英文后缀表示月中的天数（1st, 2nd, 3rd,...）
%w 以数字形式表示周中的天数（ 0 = Sunday, 1=Monday, ..., 6=Saturday）
%j 以三位数字表示年中的天数（ 001, 002, ..., 366）
%U 周（0, 1, 52），其中Sunday 为周中的第一天
%u 周（0, 1, 52），其中Monday 为周中的第一天
%M 月名（January, February, ..., December）
%b 缩写的月名（ January, February,...., December）
%m 两位数字表示的月份（01, 02, ..., 12）
%c 数字表示的月份（1, 2, ...., 12）
%Y 四位数字表示的年份
%y 两位数字表示的年份
%% 直接值“%”

(6):datediff(date1,date2)计算两个日期之间相差的天数

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

四、流程控制

(1):if(value,t,f)如果value为真，返回t，否则返回f

mysql> SELECT if(salary>1500,'高','低') FROM salary;+---------------------------+| if(salary>1500,'高','低') |+---------------------------+| 低|| 低|| 高|| 高|+---------------------------+4 rows in set

(2):ifnull(value1,value2)如果value1不为空返回value1，否则返回value2。这个函数一般用来替换Null的值。

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] end如果value1为真，返回result1，否则返回default

mysql> select case when salary>1500 then '高' else '低' end from salary;+-----------------------------------------------+| case when salary>1500 then '高' else '低' end |+-----------------------------------------------+| 低|| 低|| 高|| 高|| 低|+-----------------------------------------------+5 rows in set

(4):case [expr] when [value1] then [result1] ..else[default] end如果expr等于value1，返回result1,否则返回default

mysql> select case salary when 1000 then '低' when 2000 then '中' else '高' end from salary;+-------------------------------------------------------------------+| case salary when 1000 then '低' when 2000 then '中' else '高' end |+-------------------------------------------------------------------+| 低|| 高|| 中|| 高|| 高|+-------------------------------------------------------------------+5 rows in set

小技巧：

mysql> select case when instr(type,'上季') then 1 when instr(type,'下季') then 2 else 3 end from salary;+-------------------------------------------------------------------------------+| case when instr(type,'上季') then 1 when instr(type,'下季') then 2 else 3 end |+-------------------------------------------------------------------------------+| 1 || 1 || 2 || 3 || 1 |+-------------------------------------------------------------------------------+5 rows in set

五、其他函数

(1):database():返回当前数据库名
(2):version():返回当前数据库版本
(3):user():返回当前登录用户名
其他参考文档。
参考：《深入浅出mysql》