MYSQL 不常见用法归纳
concat(arg1,arg2,...)
select *,
case when sal>1000 then 'HIGH'
when sal>500 then 'MIDDLE'
else 'LOW'
end as status
from ...
- 随机返回固定行,使用 order by rand()和limit配合实现
select *
from xxx
order by rand() limit 10
# coalesce函数接收多个参数,返回首个非NULL值
select coalesce(comm,0)
from xxx
- case语句可以动态选择列,可以用在
select
、group by
、order by
中,
- SQL 中的正则表达式,模式匹配
select *
from xxx
where regexp_like(column_name,'regexp_str') and xxx
select regexp_replace(name,'regexp_str','replace_str') as name
from xxx
- 获取两个日期相差的天数,使用 datediff(arg1,arg2)函数
- MYSQL 行源生成器,只在当前会话中有用
# 默认值为1000,如果需要生成1000以上的数据,则需要设置这个值
SET cte_max_recursion_depth = 100000;
WITH RECURSIVE sequence AS (
SELECT 1 AS level
UNION ALL
SELECT level + 1 AS value
FROM sequence
WHERE sequence.level < 10000
)
SELECT level
FROM sequence;
- 给结果集添加行号,使用
row_number()
函数
- 找出值最高和最低的行
select *
from (
select *,min(xxx)over() as min_val,max(xxx)over() as max_val
from xxx
) rs
where xxx=min_val or xxx=max_val