MySQL查询技巧

常用函数

聚合

MAX() 获取最大值
MIN() 获取最小值
SUM() 计算和
AVG() 计算平均数
COUNT(*) 计算行数
COUNT(column) 对特定列中不为null的值进行计数
WITH ROLLUP 用在GROUP BY后对聚合函数求和

数值

ABS() 绝对值
MOD() 取余
RAND() 随机数
EXP() 指数
SQRT() 平方根
COS() 余弦
SIN() 正弦
TAN() 正切
PI() 圆周率

字符串

LEFT(string,int) 截取从左边开始指定长度的字符串
RIGHT(string,int) 截取从右边开始指定长度的字符串
LOCATE(string1,string2) 返回string2中第一个匹配string1的起始下标
LOWER(string) 转小写
UPPER(string) 转大写
CONCACT(string…) 字符串拼接,接收1到任意个参数
TRIM(string) 去掉字符串两边空格
LENGTH(string) 计算字符串长度
SUBSTRING(string,position) 从position（负数则length(string)-abs(position)）开始截取字符串
SUBSTRING_INDEX(string,delimeter,count) 截取字符串str,从开头到第count(负数则length(string)-abs(count))个delimeter的下标
SOUNDEX(string) 字符串转发音模式，可以用于查找发音相近的单词

日期

当前时间

NOW() 获取当前日期时间
CURDATE() 当前日期
CURTIME() 当前时间
UNIX_TIMESTAMP() 当前时间戳

互相转换

UNIX_TIMESTAMP(date) 日期转时间戳
FROM_UNIXTIME(timestamp) 时间戳转日期
FROM_UNIXTIME(timestamp, format) 时间戳根据format格式化转日期
DATE_FORMAT(string | date, format) 字符串或日期根据format格式化
STR_TO_DATE(string, format) 字符串转日期
DAYOFWEEK(date) 返回对应的星期几

计算

ADDTIME(expr1, expr2) 时间相加
DATEDIFF(expr1, expr2) 日期相减，返回天数
ADDDATE(expr1, days) 日期加天数
ADDDATE(date, INTERVAL expr unit) unit: YEAR/MONTH/WEEK/DAY/HOUR/MINUTE/SECOND/MICROSECOND/…

常用格式化模式

‘%Y-%m-%d %H:%i:%s’

其他

IF(x=1,a,b) 条件判断，满足x=1则返回a，否则返回b

语法

union

union和where的or相似

union all

union all和union的区别是不会自动去掉重复查询到的数据

字符串拆分

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c,d,',',help_topic_id+1),',',-1) AS num
FROM mysql.help_topic
WHERE help_topic_id < LENGTH('a,b,c,d')-LENGTH(REPLACE('a,b,c,d',',',''))+1

help_topic_id是mysql自带的help_topic表的自增字段（从0开始），用于辅助计算，其他符合要求的的表字段也可用于辅助计算
LENGTH(‘a,b,c,d’)-LENGTH(REPLACE(‘a,b,c,d’,’,’,’’))+1 计算出需要截取几次
以截取第二个字符串b为例：
- SUBSTRING_INDEX(‘a,b,c,d,’,’,2)截取a,b
- SUBSTRING_INDEX(‘a,b’,’,’,-1)截取b

对检索数据出的数据进行算术计算

select a+b as result from table

a	b
12	18
5	7
10	20

⬇️⬇️⬇️⬇️

result
30
12
30

横纵转换

1
2
3

select 	max(if(name='a',age,0)) as a,
		max(if(name='b',age,0)) as b ,
		max(if(name='c',age,0)) as c from table

前提是指定转换的数据值（name）不重复
以a为例，如果检索到一条数据的name为a,该条数据会被if函数赋age，否则赋0,此时查询结果会有一条符合条件的数据和其他不符合条件的数据，因为age必定大于0，可以用max取到符合条件的数据（age），排除掉其他数据（0），最后利用数据库别名（as）把值写到列名
该方法在数据量大时不适用

name	age
a	18
b	25
c	30

⬇️⬇️⬇️⬇️⬇️⬇️

a	b	c
18	25	30

奇数行和偶数行分两列

select temp1.id,temp2.id from 
(select id, (@i := @i + 1) i from table,(select @i := 1) d) temp1
left join 
(select id, (@j := @j + 1) j from table,(select @j := 0) d) temp2
on temp1.i=temp2.j;

≡∧