Mysql高级查询

一.条件判断

1.1 if

IF(exPR1,expr2,expr3)
如果 expr1 是TRUE ，则 IF()的返回值为expr2; 否则返回值则为 expr3。

如

SELECT IF(status >= 0 AND status_promotion = 1, 1, 2) AS status FROM my_table;

1.2 ifnull

IFNULL(expr1,expr2)
假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。

1.3 nullif

NULLIF(expr1,expr2)
如果expr1 = expr2  成立，那么返回值为NULL，否则返回值为 expr1。

1.4 case...when

1.4.1 CASE后面不带表达式

CASE
WHEN expression THEN 操作1
WHEN expression THEN 操作2
.......
ELSE 操作n
END

1.4.2 CASE后面带表达式

CASE expression
WHEN expression值1 THEN 操作1
WHEN expression值2 THEN 操作2
.......
ELSE 操作n
END

注意：NULL和0是等同的，0和'0'是不同的。

二.函数

2.1 concat

CONCAT(str1,str2,…)

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

SELECT CONCAT(id,title,url) FROM my_table LIMIT 100;

2.2 concat_ws

CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。

SELECT CONCAT_WS(";",id,title) FROM my_table LIMIT 100;

2.3 group_concat

SELECT id,GROUP_CONCAT(name) FROM my_table GROUP BY id;
SELECT id,GROUP_CONCAT(name separator ';') FROM my_table GROUP BY id;
SELECT id,GROUP_CONCAT(name order by id) FROM my_table GROUP BY id;
SELECT id,GROUP_CONCAT(name order by id separator ';') FROM my_table GROUP BY id;

注：MySQL默认对结果限制在 1024，会自动截断，可以通过变量 group_concat_max_len设置，坑！！

2.4 repeat

REPEAT(str,count)

SELECT REPEAT(name,3) FROM my_table LIMIT 100;

2.5 INET_ATON/INET_NTOA

ip地址与对应int(4B)值的相互转换。

SELECT INET_NTOA(ip) from `ip_t`;//int-->x.x.x.x
SELECT INET_ATON(ip) from `ip_t`;//x.x.x.x-->int

2.6 DATE_SUB/DATE_ADD

DATE_SUB(date,INTERVAL expr type)
date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。
Type 值

• MICROSECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR
• SECOND_MICROSECOND
• MINUTE_MICROSECOND
• MINUTE_SECOND
• HOUR_MICROSECOND
• HOUR_SECOND
• HOUR_MINUTE
• DAY_MICROSECOND
• DAY_SECOND
• DAY_MINUTE
• DAY_HOUR
• YEAR_MONTH

SELECT id FROM my_table WHERE create_time >= date_sub(now(), INTERVAL 3 HOUR) AND create_time < now();

2.7 时间加减

当我们在给now（）+-一个时间的时候，其实应该这样理解的：
+1/+01：加1秒钟
+101/+0101：加1分钟1秒钟
+10101/+010101：加1小时1分钟1秒钟
+1010101/+01010101：加1天1时1分钟1秒钟
+101010101/+0101010101：加1月1天1时1分钟1秒钟
+1101010101/+010101010101：加1年1月1天1时1分钟1秒钟,这里要注意下，年这个部分可以是4位(高位没有的话会补零)：00010101010101

2.8 DATE_FORMAT

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

DATE_FORMAT(date,format)

date 参数是合法的日期。format 规定日期/时间的输出格式。

如：SELECT DATE_FORMAT(insert_time,'%Y-%m-%d %H:%i:%S') AS insert_time FROM user;

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位