case when

-- demo1 case 搜索函数 case when xxx then xxx else end
select
 id,
 case
 when score < 60
 then 'low'
 when score < 90
 then 'middle'
    else 'high'
    end as rank
from
 test

-- demo2 简单 case 函数 case field when xxx then xxx else end
select
 id
 case score
 when 0
 then 'bad'
 when 100
 then 'good'
 else 'middle'
    end

分页优化

-- demo1
select id, content from news order by title limit 50,5
--> 优化后
select news.id, news.content
from news inner join (
    select id from news order by title limit 50,5
) as tmp using(id)

-- demo2
select id from t limit 10000, 10
--> 优化后
select id from t where id > 10000 limit 10

count 内增加条件

-- 常用以下两种
-- 1
count(distinct
case when
    condition
then
 result1
else
    result2
end
)

-- 2
count(condition or null)

date_format

SELECT IF( date_format( time, '%u' ) BETWEEN '1' AND '5', '平日', '周末' ) 

field

-- 根据指定顺序排序
select ... from users where id in (...) order by field(id, 'id1', 'id2', ...)

GROUP_CONCAT

-- 使用GROUP_CONCAT函数将多行合并为单行
SELECT id, GROUP_CONCAT(column_name SEPARATOR ', ') AS concatenated_values FROM table_name GROUP BY id;

SQL_CALC_FOUND_ROWS和FOUND_ROWS()

-- 使用SQL_CALC_FOUND_ROWS和FOUND_ROWS()获取总行数
SELECT SQL_CALC_FOUND_ROWS * FROM table_name LIMIT 0, 10;
SELECT FOUND_ROWS();

WITH ROLLUP

-- 使用WITH ROLLUP进行分组并生成汇总行
SELECT column1, SUM(column2) AS total FROM table_name GROUP BY column1 WITH ROLLUP;