常用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}

转载于:https://www.cnblogs.com/xx0829/p/11539633.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值