一、自定义排序 (ORDER BY FIELD)

在 MySQL 中 ORDER BY 排序除了可以用 ASC 和 DESC 之外，还可以使用自定义排序方式来实现。

select * from 表 order by field(字段, '值1','值2','..')

二、空值 NULL 排序 (ORDER BY IF(ISNULL))

在 MySQL 中使用 ORDER BY 关键字加上我们需要排序的字段名称就可以完成该字段的排序。如果字段中存在 NULL 值就会对我们的排疗结果造成影响.

这时侯我们可以使用 ORDER BY IF(ISNULL(字段)，0,1) 语法将 NULL 值转换成 0或1，实现 NULL 值数据排序数据集前面还是后面。

-- null 在前
select from 表 ORDER BY if(ISNULL(列),0,1);

-- null 在后
select from 表 ORDER BY if(ISNULL(列),2,1);

三、CASE表达式 (CASE...WHEN)

在开发中经常会写很多 if…else if…else，这时候可以使用 CASE…WHEN 表达式解决这个问题。

以学生成绩举例。比如说：学生90分以上评为优秀，分数80-90评为良好，分数60-80评为一般，分数低于60评为“较差”。那么我们可以使用下面这种查询方式：

select *, case when score > 90 then '优秀' 
    when score > 80 then '良好'
    when score > 60 then '一般' 
    else '较差' end level
from student;

四、分组连接函数 (GROUP CONCAT)

分组连接函数可以在分组后指定字段的字符串连接方式，并且还可以指定排序逻辑；连接字符串默认为英文逗号。

比如说根据演员进行分组，并将相应的电影名称按照票价进行降序排列，而且电影名称之间通过”“拼接。用法如下：

select actors,
    GROUP CONCAT(movie_name),
    GROUP_CONCAT(price) 
   from movies GROUP BY actors;

select actors;
    GROUP_CONCAT(movie_name order by price desc SEPARATOR ''),
    GROUP_CONCAT(price order by price desc SEPARATOR '_')
   from movies GROUP BY actors;

五、分组统计数据后再进行统计汇总(with rollup)

在 MySql 中可以使用 with rollup 在分组统计数据的基础上再进行数据统计汇总，即将分组后的数据进行汇总，

select 分组列，sum(统计列) from 表 group by 分组列 with rollup;

六、子查询提取(with as)

如果整句查询中多个子查询都需要使用同一个子查询的结果，那么就可以用 with as 将共用的子查询提取出来并取一个别名。后面查询语句可以直接用，对于大量复杂的 SQL 语句起到了很好的优化作用.

需求：获取演员刘亦菲票价大于50且小于65的数据。

with as (子查询)

7、优雅处理数据插入、更新时主键、唯一键重复

在 MySq 中插入、更新数据有时会遇到主键重复的场景，通常的做法就是先进行删除在插入达到可重复执行的效果，但是这种方法有时候会错误删除数据。

1、插入数据时我们可以使用  IGNORE，它的作用是插入的值遇到主键或者唯一键重复时自动忽略重复的数据，不影响后面数据的插入，即有则 忽略，无则 插入。示例如下

2、可以使用 REPLACE 关键字，当插入的记录遇到主键或者唯一键重复时先删除表中重复的记录行再插入，即有则 删除+插入，无则 插入，示例如下：

3、更新数据时使用 on duplicate key update。它的作用就是当插入的记录遇到主键或者唯一键重复时，会执行后面定义的 UPDATE 操作。相当于先执行 Insert 操作，再根据主键或者唯一键执行 update 操作，即 有就 更新，无则 插入。示例如下：

未经允许请勿转载：程序喵 » 7种 MySql 进阶用法