JAVA用到MySQL（一）--- Date、CASE WHEN、IFNULL、foreach、字符串排序

Date/Datetime

数据库的表：Date类型字段，保存年月日，而Datetime类型字段，保存的是年-月-日 时:分:秒。

对应的entity实体类：Date和Datetime在实体类中都用Date修饰。

在SQL语句中需用DATE_FORMAT修饰：

SELECT 
    id, DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') createdAt
FROM user;

其中日期格式 '%Y-%m-%d %H:%i:%s' 分别对应着年-月-日 时:分:秒

---

CASE WHEN...THEN...ELSE...END

case when...then...else...end是mysql语句中的if...else（或switch case ），具体用法为：

SELECT
    (CASE WHEN name IS NULL THEN 1 ELSE 0 END) ifNull
FROM user;

上述语句意义为：当user表中name为null时，查询的ifNull值赋为1，否则赋值为0。

当然也支持更多的情况：

SELECT
    (CASE name 
        WHEN 'Bob' THEN 'B'
        WHEN 'Jack' THEN 'J'
        WHEN 'Alice' THEN 'A'
        ELSE 'C'
     END
    ) shortName
FROM user;

---

IFNULL

ifnull(xxx, yyy)，意义是如果括号中xxx为null则返回yyy，否则返回xxx，具体用法：

SELECT 
    IFNULL(score,0) score
FROM user;

上述sql含义为，查询用户表中的score，若score为null则score为0，否则返回查询到的score。

---

<foreach>标签

sql查询，经常遇到类似 where id in(1,2,3,4)的情况，当括号里的是参数时，可用<foreach>标签解决：

SELECT 
    id, name, age, score
FROM user
WHERE 1 = 1
<if test="idArr != null">
    AND id IN
    <foreach item="item" index="index" collection="idArr" open="(" separator="," close=")">
        #{item}
    </foreach>
</if>

---

字符串排序

假设user表中，有字段rank，其值分布为t1，t2，t11，t12，t20，若仅用ORDER BY排序，则：

SELECT 
    id,name,rank
FROM user
ORDER BY rank;

返回结果：

id	name	rank
1	Bob	t1
3	Jack	t11
4	Alice	t12
2	Funch	t2
5	Blue	t20

因为字符串排序是先比较字符串第一个字符的大小，再依次比较下一个字符，而不是像int型比较整个数字的大小。

要想让字符串数据排序达到数字类型字段排序的效果，可以采用如下三种方式：

SELECT 
    id,name,rank
FROM user
ORDER BY rank*1;

SELECT 
    id,name,rank
FROM user
ORDER BY rank+0;

SELECT 
    id,name,rank
FROM user
ORDER BY CAST(rank AS DECIMAL);

返回结果：

id	name	rank
1	Bob	t1
3	Funch	t2
4	Jack	t11
2	Alice	t12
5	Blue	t20

Java用到的MySQL（二）--- CONCAT()、CONCAT_WS()、GROUP_CONCAT()