常用语句
1. case when
SELECT
(
CASE
WHEN NAME = 'zs'
THEN '张三'
WHEN NAME = 'ls'
THEN '李四'
ELSE '王五'
END
) AS NAME
FROM
test
也可以
SELECT
(
CASE
NAME
WHEN 'zs'
THEN '张三'
WHEN 'ls'
THEN '李四'
ELSE '王五'
END
) AS NAME
FROM
test
生成指定的查询结果集。
2. CONCAT
SELECT CONCAT(‘1’,NULL,’33’)
按照指定的顺序结合起来，如有空则为空。oracle null不会空，并且只能连接两个
SELECT CONCAT_WS(‘;’,’qq’,null,’ee’,’sd’)
以第一个字符串为标准，把后面的字段按照它间隔连接起来，跳过NULL
qq;ee;sd
3. linux备份sql
--skip-extended-insert --complete-insert
一行行，完整insert语句
mysqldump -uroot -p123456 -h 127.0.0.1 --single-transaction --default-character-set=utf8
ecard_test trans -w “orderno in (‘4137161113049663’,’4113161113756578’)” > /var/trans.sql
mysqldump -uroot -p$(sudo cat /root/save/.root) -h 127.0.0.1 --single-transaction --default-character-set=utf8 ecard trans -w “orderno in (‘4128170912395813’,’4151708221007297’)” >/usr/BAK-sql/ecard.trans-bak1925_chg.sql
4. sum 注意事项
1.使用sum在数据库计算往往很方便，但是它有精度问题
SELECT
SUM(CAST(amount AS DECIMAL (12, 2))) AS sumAmount
FROM
orders
WHERE order_cnl = #{oldOrderNo} and sign='4'"})
数据库记录类型为varchar。先把amount的值转化（varchar变为decimal） 精度12，保留两位小数。再sum就不会精度问题。
cast(amount as DECIMAL(12,2))
2.sql sum null换0
COALESCE(sum(amount),0)
5. 使用 != 会过滤符合条件的记录，不包含空的记录
比如我数据库默认isCancle字段是空的 通过查询
SELECT
FROM
trade
WHERE openId = 'ojNMEwJC0Oh9B622k0D-8cqereeA'
AND TYPE = 'M'
AND CONCAT(transDate, ' ', transTime) >= '2018-04-18 00:00:00'
AND CONCAT(transDate, ' ', transTime) <= '2018-04-18 23:59:59'
AND (isCancle != '1')
过滤掉了不等于 1 的记录，但是空的也过滤掉了。！=不检索空的记录。
使用括号，不然or会有歧义。
SELECT
FROM
trade
WHERE openId = 'ojNMEwJC0Oh9B622k0D-8cqereeA'
AND TYPE = 'M'
AND CONCAT(transDate, ' ', transTime) >= '2018-04-18 00:00:00'
AND CONCAT(transDate, ' ', transTime) <= '2018-04-18 23:59:59'
AND (isCancle != '1'
OR isCancle IS NULL)
6. 日期内置函数
SELECT NOW() 2018-04-20 14:40:45
SELECT CURRENT_TIMESTAMP
2023-05-26 16:28:55
SELECT CURDATE() 2018-04-20
SELECT CURTIME() 14:42:02
DATE_FORMAT 日期格式化 SELECT DATE_FORMAT(NOW(),’%Y-%m-%d %H:%i:%s’)
DATE_SUB 日期减去指定的时间间隔 SELECT DATE_SUB(NOW(),INTERVAL 150 DAY)
DATE_ADD() 给日期添加指定的时间间隔 SELECT DATE_ADD(NOW(),INTERVAL 150 DAY)
DATEDIFF(date1,date2) 返回两个日期之间的天数（前面减后面的） SELECT DATEDIFF(NOW(),DATE_ADD(NOW(),INTERVAL 150 DAY)) -150 TO_DAYS(NOW()) - TO_DAYS(createDate) > xx天
返回YYYY-MM-DD 格式的日期 SELECT DATE(‘2017-02-09 15:25:46.635’) 2017-02-09
7. 组内排序
方式一：
SELECT * FROM COMMENT
WHERE id IN (SELECT MAX(id) FROM COMMENT GROUP BY user_id)
方式二：
select c.* from comment c right join
(select user_id, max(addtime) as maxtime from comment group by user_id) b on c.`user_id` = b.`user_id` and c.`addtime`= b.`maxtime`
8.判断字符串是否包含
判断数据库某字段的字符串是否包含另一个字符串。
locate(subStr,string) ：函数返回subStr在string中出现的位置
// 判断字段field中是否包含aaa字符串，位置从1开始算，>0可以省略不写
SELECT * FROM table
WHERE LOCATE(field, 'aaa') >0
// 注：Mybatis使用场景，需要加 <![CDATA[ ]]>
SELECT *
FROM party_course_study
WHERE <![CDATA[ LOCATE(findCode, '00001') > 0 ]]>
逗号分割的字段可以使用 FIND_IN_SET
// 以下语句返回2，因为字符'y'是'x，y，z'字符串中的第二个位置
SELECT FIND_IN_SET('y','x,y,z');