mysql 函数

字符函数

upper | lower

转换为大/小写

SELECT UPPER('a');     #结果为'A'
SELECT LOWER('A');     #结果为'a'

group_concat

GROUP_CONCAT()用于将一组中的数据拼起来即行转列。

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

substr

SUBSTR(str,pos,len) 截取字符串，字符串索引从1开始

SELECT SUBSTR('my name is Liming',4,4);  #结果为'name'
SELECT SUBSTR('my name is Liming',4);    #结果为'name is Liming'

ON DUPLICATE KEY UPDATE

单个增加更新及批量增加更新的sql

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

concat

连接参数产生的字符串,如有任何一个参数为NULL ,则返回值为 NULL

concat(str1,str2,…)  

cast

cast(字段名 as 转换的类型 )，其中类型可以为：
CHAR[(N)] 字符型
DATE 日期型
DATETIME 日期和时间型
DECIMAL float型
SIGNED int
TIME 时间型

cast(XXX as signed)
将XXX字段转化为int类型

instr

INSTR(str,substr) 返回子串第一次出现的索引，字符串索引从1开始

SELECT INSTR('my name is lala','la'); #结果为12

trim

TRIM(str,substr) 首尾去除规定字符，默认去空格

SELECT TRIM('   my  name  is  lala ');  #结果为'my  name  is  lala'
SELECT TRIM('Y' FROM 'YY my  name YY is  lala  YYY'); #结果为'my  name YY is  lala '

lpad

LPAD(str,len,padstr) | RPAD(str,len,padstr) 用规定字符左(右)填充至指定长度

SELECT LPAD('my',10,'*');      #结果为'********my'
SELECT RPAD('my',10,'ab');     #结果为'myabababab'

replace

REPLACE(str,old,new) 字符串替换

SELECT REPLACE('my name is lala','la','da');   #结果为'my name is dada'

substring_index

substring_index(str,delim,count) 字符串截取,如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容;如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容

str = '1,2,6';
SELECT substring_index('1,2,6',',',1);   #结果为'1'
SELECT substring_index('1,2,6',',',-2);   # 结果为 '2,6'
SELECT substring_index(substring_index('1,2,6',',',-2),',',1);   #结果为 ‘2’

数学函数

round

ROUND(X,D) 四舍五入，保留指定位数，默认为保留整数

SELECT ROUND(-1.65);          #结果为-2
SELECT ROUND(-1.65,1);        #结果为-1.7

crlt 、floor

CEIL(X) | FLOOR(X) 向上|向下取整

 SELECT CEIL(1.44);        #结果为2
 SELECT FLOOR(1.55);       #结果为1

truncate

TRUNCATE(X) 截取保留指定小数位

 SELECT TRUNCATE(2.666,1);    #结果为2.6

mod

MOD(X1,X2) 取模

SELECT MOD(10,3);    #结果为1,符号与被除数一致
SELECT MOD(-10,3);   #结果为-1
SELECT MOD(10,-3);   #结果为1

日期函数

now 、 curdate

NOW()、CURDATE() 当前时间、当前日期

 SELECT NOW();      #结果为2019-10-19 14:40:54
 SELECT CURDATE();  #结果为2019-10-19

DATE_ADD(）

DATE_ADD(NOW(), INTERVAL + 2 year) 据当前时间2年

dayofweek

DAYOFWEEK（date） 返回结果表示星期几

SELECT DAYOFWEEK('2020-06-02 09:06:54');  #结果为3 即指星期二
注意：星期天=1，星期一=2， ... 星期六=7

weekday

WEEKDAY(date) 返回结果表示一周中的序号

SELECT WEEKDAY('2020-06-02 09:06:54');    #结果为1 
注意：0=星期一，1=星期二， ... 5=星期六)

dayofmonth

DAYOFMONTH(date); 表示返回date是当月的第几天，1号就返回1，… ,31号就返回31

SELECT DAYOFMONTH('2020-06-02 09:06:54');     #返回结果是2

dayname

DAYNAME(date) 返回date的星期英文全称

SELECT DAYNAME('2020-06-02 09:06:54');   #结果为：Tuesday

monthname

MONTHNAME(date) 返回date月的英文全称

SELECT MONTHNAME('2020-06-02 09:06:54');  #结果为：June

quarter

QUARTER(date) 返回date所在季度

SELECT QUARTER('2020-06-02 09:06:54');  #结果为2

year、month、day、hour、minute、second

YEAR() | MONTH() | DAY() | HOUR() | MINUTE() | SECOND()

 SELECT YEAR('2019-10-1');    #结果为2019

datediff、timediff

DATEDIFF(date1,date2) | TIMEDIFF(date1,date2) 两个日期相差的天数|两个时刻相差的时间

SELECT DATEDIFF('2019-10-17','2019-10-1');#结果为16，前面的减后面的
SELECT TIMEDIFF('2019-10-17 15:30:2','2019-10-1 7:0:0');#结果为 392:30:02

str_to_date

STR_TO_DATE(str,format) 字符串按格式转为日期
DATE_FORMAT(date,format) 日期按格式转为字符串

符号	意义
%Y	四位年月
%y	两位年月
%m	月份(01,02…)
%c	月份(1,2,…)
%d	日(01,02…)
%H	小时(24制)
%h	小时(12制)
%i	分钟(01,02…)
%s	秒(01,02…)

 SELECT STR_TO_DATE('10-1 2019','%c-%d %Y');    #结果为2019-10-01
 SELECT DATE_FORMAT(NOW(),'%Y.%m.%d');          #结果为2019.10.19

其他

if

SELECT IF(0 > 1 ,'yes','no');   #结果为'no'

case-when-then-end

用法1，相当于Java的swhich case，判断一个值：

SELECT score,course
        CASE job_id
        WHEN '1' THEN 'Chinese'
        WHEN '2' THEN 'English '
        WHEN '3' THEN 'math'
        ELSE 'other'
        END  
FROM user_score;   

用法2，相当于Java的if-else if-else，判断一个表达式：

SELECT last_name,
       CASE
      WHEN salary<5000 THEN  '<5000'
      WHEN salary BETWEEN 5000 AND 10000 THEN '5000-10000'
      ELSE 	'>10000'
      END  
FROM employees;