常用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
);