1.数据库查询时间类似的方法,例如数据库存的时间到秒,而查询只查询2019-01-23这一天的数据
DATE_FORMAT(time,'%Y-%m-%d')
like concat(‘...’,%)
DATE_FORMAT(NOW(),'%Y-%m-%d')
2.用sql改变查出来的时间 date_ADD(time,interval 8 hour)as time
3.SQL中ON和WHERE的区别
数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。
在使用left jion时,on和where条件的区别如下:
· on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
·where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
4.select case
when openid= 'a' then '关注'
when openid = 'b' then '取关'
end 'aa'
from wxuser where activityid = 1
5.左连接查询 on的列 左表一条数据 ,右表两条重复数据,会返回两条数据
6.显示连接:(inner) join 速度快,关联多张表的时候明显差别
隐式连接: , , , 书写简单
7. group by
set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
SHOW VARIABLES LIKE '%sql_mode%'
set names utf8
8.数据库设置定时任务
SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler = 1;
每天1点ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
9.定时器实现生成配送单
用到游标
BEGIN
DECLARE asnumber VARCHAR(50);
DECLARE agoods VARCHAR(50);
DECLARE agoodsid VARCHAR(50);
DECLARE amaccode VARCHAR(50);
DECLARE aclientid VARCHAR(50);
DECLARE anumber int(11);
DECLARE ayid VARCHAR(50);
DECLARE ajbossid VARCHAR(50);
DECLARE astart date;
DECLARE aend date;
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT yid,jbossid,start,end,snumber,goods,goodsid,maccode,clientid,number from takemilkorder where start <= now() and end >= now();
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open cur;
read_loop:loop
FETCH cur INTO ayid,ajbossid,astart,aend,asnumber,agoods,agoodsid,amaccode,aclientid,anumber;
IF done THEN
LEAVE read_loop;
END IF;
insert into takemilkday (id,snumber,goods,goodsid,maccode,clientid,number,yid,jbossid,end,start) values (concat(DATE_FORMAT(now(),'%Y-%m-%d'), asnumber),asnumber,agoods,agoodsid,amaccode,aclientid,anumber,ayid,ajbossid,aend,astart);
end loop;
close cur;
END
数据库查询上一个自增得id值
SELECT auto_increment FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='test' AND TABLE_NAME='sanqrcode'))
INSERT INTO sanqrcode (NAME) VALUES (( LTRIM(@@IDENTITY + 1)) );
SELECT @@IDENTITY;
10 建立外键
ALTER TABLE `b`
ADD CONSTRAINT `FK_b_a` FOREIGN KEY (`cid`) REFERENCES `a` (`sid`) ON UPDATE CASCADE ON DELETE CASCADE;