常用SQL

常用SQL

SQL 
参考：W3School

1、SQL常用函数

• TO_DAYS(time) 时间转换至具体的天数

 
 
TO_DAYS(alarm.updateTime) BETWEEN 
TO_DAYS('2017-1-5') AND TO_DAYS('2017-1-9')

• DATE_FORMAT(time, '%Y-%m-%d') 按格式转换进行时间比较

 
 
DATE_FORMAT(a.updateTime,'%Y-%m-%d %H:%i') <='TIME'

• YEAR()、MONTH()、DAY()、DAYOFMONTH()、TIMESTAMPDIFF()

 
 
SELECT stakeNumber, finishTime, DATE(NOW()) aa,
YEAR (NOW()) YEAR, MONTH (NOW()) MONTH, DAY (finishTime) DAY,
DAYOFMONTH(finishTime) XX, 
TIMESTAMPDIFF(DAY, finishTime, NOW()) intervalDay 
FROM eomp_em_elec_record WHERE 
DATEDIFF(finishTime, DATE(NOW())) <= 0 
AND YEAR (finishTime) <= YEAR (NOW())
OR MONTH (finishTime) = MONTH (NOW())

2、常用CRUD SQL

• 插入

 
 
 INSERT INTO 表名称 VALUES (值1, 值2,....) 
 INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

• 删除

 
 
 DELETE FROM 表名称 WHERE 列名称 = 值

• 插入

 
 
 UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

• 查询

 
 
SELECT column_name(s) FROM table_name1

• 联合查询

 
 
SELECT column_name(s) FROM table_name1
UNION SELECT column_name(s) FROM table_name2