平时用到的,在这个地方记录一下,方便自己以后查找
1、基本语句
#查询table表中id=1的数据,再按照time降序显示;升序使用asc
select * from table where id=1 order by time desc
查询某时间段的数据
select * from table where time between time1 and time2
按照name分组,使用group by
select * from table group by name
2、时间DATE_ADD
table表中的时间,有的可能是以utc时间来存储的,而程序界面显示的是utc+8的话,希望db查询时显示utc+8;
可使用DATE_ADD(statistic_time,INTERVAL 8 HOUR)
3、时间格式设定DATE_FORMAT
DATE_FORMAT(statistics_time,’%Y-%m-%d %H:%i:00’)
下面对DATE_FORMAT的格式进行说明
DATE_FORMAT(date,format)
date参数是合法的日期
format来限定日期或时间的输出格式,可以使用的格式
(该部分参考:https://www.w3school.com.cn/sql/func_date_format.asp)
格式 | 描述 |
---|---|
%a | 缩写星期名 |
%b | 缩写月名 |
%c | 月,数值 |
%D | 带有英文前缀的月中的天 |
%d | 月的天,数值 00-31 |
%e | 月的天,数值 0-31 |
%f | 微秒 |
%H | 小时:00-23 |
%h | 小时:01-12 |
%I | 小时:01-12 |
%i | 分钟,数值:00-59 |
%j | 年的天,数值:000-366 |
%k | 小时:0-23 |
%l | 小时:1-12 |
%M | 月名 |
%m | 月,数值(00-12) |
%p | AM 或 PM |
%r | 时间,12-小时(hh:mm:ss AM 或 PM) |
%S | 秒(00-59) |
%s | 秒(00-59) |
%T | 时间, 24-小时 (hh:mm:ss) |
%U | 周 (00-53) 星期日是一周的第一天 |
%u | 周 (00-53) 星期一是一周的第一天 |
%V | 周 (01-53) 星期日是一周的第一天,与 %X 使用 |
%v | 周 (01-53) 星期一是一周的第一天,与 %X 使用 |
%W | 星期名 |
%w | 周的天 (0=星期日, 6=星期六) |
%X | 年,其中的星期日是周的第一天,4 位,与 %V 使用 |
%x | 年,其中的星期一是周的第一天,4 位,与 %V 使用 |
%Y | 年,4 位 |
%y | 年,2位 |
4、数据格式转换CONVERT
CONVERT(expr,type)能把字段转成指定类型,type可填类型如下:
type | 说明 | 示例 |
---|---|---|
BINARY | 二进制 | SELECT CONVERT(“abcde”, BINARY(3)) AS num输出是abc |
CHAR | 字符 | SELECT CONVERT(“abcde”, CHAR(10)) AS num输出abcde,注意长度不足10不会在后面补空格 |
DATE | 没有时间的日期,如2019-05-21 | SELECT CONVERT(“2017-08-29”, DATE);输出:2017-08-29 |
DATETIME | 日期时间,如2019-05-21 17:00:00 | SELECT CONVERT(“2019-08-01”, DATETIME);输出2019-08-01 00:00:00 |
TIME | 去掉日期的时间,如17:00:00 | SELECT CONVERT(“2017-08-01 01:01:01”, TIME) ;输出01:01:01 |
DECIMAL | 小数,M是总位数(包含小数位数),D 是小数位数 | SELECT CONVERT(9999.9999, DECIMAL(3,2)) AS num 输出是9.99 |
SIGNED | 有符号 Integer | SELECT CONVERT(-9999.5099, SIGNED) AS num输出-10000 |
UNSIGNED | 无符号 Integer | SELECT CONVERT(9999.5099, UNSIGNED) AS num输出10000 |
5、分组并求和GROUP BY+SUM
分组时希望对某列进行运算,比如求和,使用sum(column_name)
SELECT *,DATE_ADD(statistic_time,INTERVAL 8 HOUR) from statistic_result where device_code=‘aaa’ ORDER BY statistic_time DESC
SELECT device_code,SUM(alarm_count),DATE_ADD(statistic_time,INTERVAL 8 HOUR) from statistic_result where device_code=‘aaa’ GROUP BY statistic_time ORDER BY statistic_time DESC
6、LIKE
WHERE statistics_time LIKE ‘%12:00:00’