常用SQL语句

1、查询某个字段不含字符的数据

SELECT uid FROM cookie_record_succ_t WHERE uid REGEXP '(^[0-9]+.[0-9]+$)|(^[0-9]$)'

2、两表关联批量更新

UPDATE cookie_clean_t cct
INNER JOIN cookie_short_error_t csft ON cct.uuid=csft.uuid
SET cct.state=2
WHERE cct.state=0 AND cct.ctime>=CURDATE()

3、两表关联批量删除

DELETE cut FROM cookie_used_t cut
INNER JOIN cookie_short_succ_t csst ON cut.uid=csst.uid
WHERE csst.ctime>=CURDATE()

4、一个表数据备份到另一个表

INSERT INTO cookie_clean_t(`uuid`, `username`, `password`, `clevel`)
SELECT `uuid`, `username`, `password`, `clevel` FROM cookie_init_t WHERE ctime>=CURDATE()

5、汇总前一天数据

INSERT INTO order_sum_t(`sum_date`,`order_num`,`dist_num`,`succ_num`)
SELECT
SUBDATE(CURDATE(),INTERVAL 1 DAY),
COUNT(1),
SUM(ot.dnum),
SUM(ot.enum-ot.snum)
FROM order_t ot
WHERE ot.ctime>=SUBDATE(CURDATE(),INTERVAL 1 DAY) AND ot.ctime<=CURDATE()

6、按条件分类去重汇总数据

SELECT SUM(lv1) lv1, SUM(lv2) lv2, SUM(lv3) lv3, SUM(lv4) lv4 FROM
(
SELECT COUNT(DISTINCT uid) lv1, 0 lv2, 0 lv3, 0 lv4 FROM cookie_used_t WHERE isnv!=2 AND clevel>=0 AND clevel<4
UNION ALL
SELECT 0 lv1, COUNT(DISTINCT uid) lv2, 0 lv3, 0 lv4 FROM cookie_used_t WHERE isnv!=2 AND clevel>=4 AND clevel<10
UNION ALL
SELECT 0 lv1, 0 lv2, COUNT(DISTINCT uid) lv3, 0 lv4 FROM cookie_used_t WHERE isnv!=2 AND clevel>=10 AND clevel<20
UNION ALL
SELECT 0 lv1, 0 lv2, 0 lv3, COUNT(DISTINCT uid) lv4 FROM cookie_used_t WHERE isnv!=2 AND clevel>=20
)t

7、查询随机数据

SELECT id,state,mold,okey,onum,dnum,olevel,snum,(enum-snum) cnum FROM order_t
WHERE etime IS NULL AND state IN(2,3)
ORDER BY RAND() LIMIT 1

 8、根据字符长度条件查询

SELECT ckgsid FROM cookie_init_t WHERE LENGTH(ckgsid) = 90

9、NOT IN优化为NOT EXISTS

SELECT cct.uuid, cct.mold, cct.cki, cct.cks,cct.ckua,cct.ckaid,cct.ckuid,cct.ckfrom,cct.ckgsid,DATE_FORMAT(cct.ctime, '%Y-%m-%d %T') comeTime
FROM cookie_clean_t cct
WHERE NOT EXISTS(SELECT csrt.uuid FROM cookie_short_run_t csrt WHERE csrt.uuid=cct.uuid)
AND cct.state = 0 AND cct.ctime<![CDATA[>=]]>CURDATE()
ORDER BY cct.uuid DESC LIMIT #{size}

10、

SELECT id FROM user WHERE binary userName=#{userName}

11、

-- 删除重复ckgsid保留最新的一条唯一
DELETE FROM user_record_succ_t20200605 WHERE uuid IN(
SELECT t.uuid FROM(
SELECT uuid FROM user_record_succ_t20200605 t WHERE
ckgsid IN(
SELECT ckuid FROM user_record_succ_t20200605 GROUP BY ckgsid HAVING COUNT(1)>1
)
AND uuid NOT IN(
SELECT MAX(uuid) FROM user_record_succ_t20200605 GROUP BY ckgsid HAVING COUNT(1)>1
)
)t
);