:1.mysql if语句的应用,if(location_do<location_num,1,0)是返回1,否返回0:
select if(location_do<location_num, 1, 0) as ssva from municipal_admin where Id=1;
2.mysql 左连接查询加上时间查询:
SELECT a.date,b.* FROM municipal_banxiu a LEFT JOIN municipal_bz b on a.bz_id=b.id where a.employee_id=9 and a.date=curdate();
3.左连接查询加上时间段查询加上不为空查询:DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s')表示当天0点的时间
SELECT a.*,b.user_name from municipal_positions a left JOIN municipal_employee b on a.tel=b.user_tel where b.employee_id=14 and a.datetime>=DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s') and a.datetime<=now() and a.lng<>"";
4.查询日期等于今天的记录:
SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())=0;
5.mysql中重启电脑或服务器可能会导致event_scheduler关闭,事件失效。解决方法如下:
查看是否开启:show variables like 'event_scheduler';
开启:set global event_scheduler = on;
一直保持开启的方法:
在mysql配置文件my.ini的[mysqld]部分加上:
#默认开启事件
event_scheduler = ON
6.包含关系的连接查询
SELECT * from municipal_banxiu a INNER JOIN municipal_bz b on FIND_IN_SET(b.id,a.banci) where a.employee_id=14 and a.state='0' and b.type='0';
未完待续。。。。