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