Mysql 常用语句备用录

1.mysql 查询3天之内的数据

SELECT * FROM Session where createTime >= DATE_SUB(now(), INTERVAL 3 DAY);

或
SELECT * FROM Session where createTime >= now()-interval 3 day;

2.mysql 查询3分钟以前的数据

SELECT * FROM fast_order WHERE status = '10D' and DATE_ADD(createTime ,INTERVAL 3 MINUTE) < NOW() limit 500

3.mysql查询三分钟以前，三十分钟以内的数据

SELECT * FROM fast_order WHERE DATE_ADD(createTime ,INTERVAL 3 MINUTE) < NOW() AND createTime > DATE_SUB(NOW(), INTERVAL 30 MINUTE)

4. 连表查询

SELECT u.merchantNo,c.* from user_credit_card c
left JOIN user_binding u on c.userNo = u.userNo
where u.merchantNo = 'M1598270737259955' AND c.bindChannelList <> '3002'

5. 连表删除

DELETE c from user_credit_card c
left JOIN user_binding u on c.userNo = u.userNo
where u.merchantNo = 'M1598270737259955' AND c.bindChannelList <> '3002'

6.添加账号为range密码为range123的数据库管理员，并指定任意ip访问

use u_user;//指定数据库
grant all privileges on *.* to 'fuzi'@'%' IDENTIFIED BY 'fuzi123';//授权
FLUSH PRIVILEGES;//刷新权限

7.表中添加一个新字段

use u_user;//指定数据库
ALTER TABLE u_uncall_task ADD call_capp INT(3) NOT NULL;// 新增call_capp 字段

8. 修改日期加一天

UPDATE fire_fly_user_card set expirationTime=DATE_ADD(expirationTime ,INTERVAL 1 DAY) WHERE expirationTime < NOW()

9.查询今天的数据

SELECT * FROM ok_coin_userinfo WHERE to_days(createTime) = to_days(now())

10.GROUP BY查询

SELECT productId, sum(ticketChangeNums) as ticketChangeNums
FROM ok_novels_fiction_monthticket
GROUP BY productId  ORDER BY ticketChangeNums DESC