连接 JOIN … ON
# 表连接(三种方法)
SELECT * FROM tA, tB WHERE tA.ID=tB.ID
SELECT * FROM tA JOIN tB ON tA.ID=tB.ID
SELECT * FROM tA JOIN tB USING(ID)
# 三表联查(连续使用JOIN)
SELECT * FROM A
INNER JOIN B ON A.id=B.id
INNER JOIN C ON B.id=C.id
# 内连接、外连接
(INNER)JOIN … ON …
# 左连接
LEFT OUTER JOIN
# 右连接
RIGHT OUTER JOIN
常用
DISTINCT 去重 NOT IN 去除 IS NOT NULL 不为空 DESC降序
# 模糊查询
[NOT] LIKE '%xxx%';
_ 匹配任意一个字符
% 匹配0个或多个字符
[ ] 匹配[ ]中的任意一个字符(若要比较的字符是连续的,则可以用连字符“-”表 达 );
[^ ] 不匹配[ ]中的任意一个字符
# 分区函数
over(partition by xx order by …)
# 选择指定区间
WHERE xx BETWEEN 5 AND 100 # [5,100]左闭右闭
# 分组+过滤
GROUP BY … HAVING …
# 排序
# ORDER BY … DESC # 降序(默认升序:ASC)
# 限制返回行数
LIMIT … OFFSET … # limit限制返回多少行,offset(截掉前几行)开始显示
LIMIT 5 # 前5行
LIMIT 5,95 # 从第5个以后开始,显示95个(6-100行)
LIMIT 95 OFFSET 5 # 从第5个以后开始,显示95个(6-100行)
LIMIT m-n offset n # 第 n 到第 m 行
数学
count()计数 avg()平均 sum()和
round(xx,2)取整 if(xx,1,0)判断
字符串处理
# 拼接字符串
CONCAT(string1, string2, …)
# 截取字符串
SUBSTRING(xx, 起始位置1, 截取字符个数)
# 截取字符串(按/分割)
SUBSTRING_INDEX(xx,'/',-1)
时间
now() # 当前时间
date() # 日期
curdate() # 当前日期
date_format(xx, '%Y-%m') # 按格式输出日期
date_sub(date(xx), interval 30 day) # 筛选30天范围内
timestampdiff(second, tima, timb) # 计算时间差
date()日期 year()年 month()月 day()日 hour()时 minites()分 second()秒
# 查询最近X天的数据
SELECT * FROM tablename WHERE 👇
# 今天
WHERE to_days(时间字段名) = to_days(now())
# 昨天
WHERE to_days(now()) - to_days(时间字段名) <= 1
# 近7天
WHERE date_sub(curdate(), interval 7 days) <= date(时间字段名)
# 近30天
WHERE date_sub(curdate(), interval 30 days) <= date(时间字段名)
# 本月
WHERE date_format(时间字段名, '%Y%m') = date_format(CURDATE(), '%Y%m')
# 上一月
WHERE date_format(时间字段名, '%Y%m') = date_format(date_sub(curdate(), interval 1 month), '%Y%m')
WHERE PERIOD_DIFF(date_format(now(), '%Y%m'), date_format(时间字段名, '%Y%m')) = 1
# 本季度
WHERE QUARTER(时间字段名) = QUARTER(now())
# 上季度
WHERE QUARTER(时间字段名) = QUARTER(date_sub(now(), interval 1 QUARTER))
# 本年
WHERE YEAR(时间字段名) = YEAR(now())
# 上年
WHERE YEAR(时间字段名) = YEAR(date_sub(now(), interval 1 year))
# 本周
WHERE YEARWEEK(date_format(时间字段名, '%Y-%m-%d')) = YEARWEEK(now())
# 上周
WHERE YEARWEEK(date_format(时间字段名, '%Y-%m-%d')) = YEARWEEK(now()) - 1
CASE 自定义列名
CASE (WHEN 条件 THEN … ELSE … END 列名)
SELECT
CASE
WHEN age < 25 OR age IS NULL THEN '25岁以下'
ELSE '25岁及以上'
END age_cut,
COUNT(*) number
FROM
tableA
计算用户的平均次日留存率
SELECT
count(date2) / count(date1) as avg_ret
FROM (
select
distinct qpd.device_id,
qpd.date as date1,
uniq_id_date.date as date2
from
question_practice_detail as qpd
left join(
select distinct device_id,
date
from question_practice_detail
) as uniq_id_date
on qpd.device_id=uniq_id_date.device_id
and date_add(qpd.date, interval 1 day)=uniq_id_date.date
) as id_last_next_date