MYSQL一些常用方法

limit 和 offset 表示跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据
ifnull 用法ifnull(“不为空展示的数据”，null)
select ifnull((select distinct Salary from Employee order by Salary desc limit 3 OFFSET 1),null)as SecondHighestSalary
greatest(字段1，字段2，字段3，…，字段n) 取最大值
least(字段1，字段2，字段3，…，字段n) 取最小值
mysql 模糊查询中间带空格字段
select * FROM test_name where trim(replace(name,’ ‘,’’)) like trim(replace(‘和文’,’ ‘,’’))

mysql 排名 窗口函数 （mysql 8.0以上）
select *,
rank() over (order by 成绩 desc) as ranking,
dense_rank() over (order by 成绩 desc) as dese_rank,
row_number() over (order by 成绩 desc) as row_num
from 班级

普通查询方法
select
a.Score as score ,
(select count(distinct b.Score) from Scores b where b.Score >=a.Score) as rank
from Scores a order by Score DESC;

mysql 截取并拼接字符串
UPDATE web_user w1 SET w1.area=w1.area_code,w1.city=concat(left(w1.area_code,4),‘00’),w1.province=concat(left(w1.area_code,3),‘000’)

mysql 将某个字段数据值置顶
select
from table
order by case when a=‘第一’ then 1
when b=‘第二’ then 2
else 3 end

mysql 查询三天内的数据
select * from table_name where datediff(NOW(), column) <= 3