sql时间范围查询 数据库sql语句大全实例
SQL & # 8211查询JSON、时间和字符串的高级用法本文总结了JSON的使用，SQL的时间格式和字符串判断转换。核心点是Json字段的提取(1.5)、时间的比较(2.2、2.3)和字符串的查询(3
SQL & # 8211查询JSON、时间和字符串的高级用法
本文总结了JSON的使用，SQL的时间格式和字符串判断转换。核心点是Json字段的提取(1.5)、时间的比较(2.2、2.3)和字符串的查询(3.1)，其他都是优化和补充点。
@
目录
1.SQL & # 8211使用JSON
1.1.JSON _ ARRAY & # 8211转换为数组
1.2.JSON _ OBJECT & # 8211转换为json对象
1.2.JSON _ QUOTE & # 8211转换成json
1.3.JSON _ MERGE & # 8211正在合并json
1.4.JSON _ VALID & # 8211是json吗？
1.5.JSON _ EXTRACT & # 8211提取json(强调*)
1.6.完整的JSON内容
2.SQL & # 8211时间计算比较
2.1.时间–增加、减少
2.2时间格式转换
2.3今年/本月/本周/今天的统计数据(强调*)
2.4时间格式汇总
3.SQL & # 8211字符串
3.1.Concat & # 8211模糊查询
3.2 .定位–查找字符串
3.3.If、Case、Round & # 8211优化显示和字段计算
1.SQL & # 8211使用JSON解释为什么在SQL中使用JSON存储和查询。因为前端展示或者后端存储的无用字段太多，所以不需要在后台创建太多字段。并且为了方便项目上线后快速调查问题反馈解决方案给用户，引入JSON进行存储和查询。在实际开发中，库表字段被设计为JSON。
先给出基本的数据库查询操作。
# SQL--建库CREATE TABLE t_base_info ( id BIGINT NOT NULL PRIMARY KEY auto_increment, content json);# SQL--入库INSERT INTO t_base_info (content)VALUES( '{ "author": "huyuqiao-A", "blog": "https://www.cnblogs.com/meditation5201314/", "content": [ {"name": "Empirefree-01", "age": 18 }, {"name": "Empirefree-02", "age": 19 }, {"name": "Empirefree-03", "age": 19 } ]}');
1.1.JSON _ ARRAY & # 8211转换为数组# SQL--查询# json--对象转数组select json_array(content) from t_base_info
1.2.JSON _ OBJECT & # 8211转换为json对象#json--对象转json格式--自定义select json_object('name', 'huyuqiao', 'age', '18', 'profession','Java开发工程师')
1.2.JSON _ QUOTE & # 8211转换成json#json--引用字符串作为json值select json_quote('"huyuqiao"')
1.3.JSON _ MERGE & # 8211正在合并json#json--合并字符串select json_merge('{"blog": "www.baidu.com"}', '{"name":"huyuqiao"}')
1.4.JSON _ VALID & # 8211不管是不是json#json--判断json格式是否有效--0 表示无效，1 表示有效，NUll 表示参数为nullselect json_valid("huyuqiao"), json_valid('"huyuqiao"'), json_valid(null)
1.5.JSON _ EXTRACT & # 8211提取json(强调*)#json--查询json内容(重点)SELECTJSON_EXTRACT(content, '$.blog' ) AS '个人博客',JSON_EXTRACT(content, '$.content' ) AS '个人信息' FROMt_base_info
1.6.完整的JSON内容官方文档:
https://dev . MySQL . com/doc/ref man/5.7/en/JSON-function-reference . html
官网给出的说法很多，我选择常用的，主要是JSON_EXTRACT。
分类
函数
描述
创建jsonjson_array创建json数组json_object创建json对象json_quote将json转成json字符串类型查询jsonjson_contains判断是否包含某个json值json_contains_path判断某个路径下是否包json值json_extract提取json值column->pathjson_extract的简洁写法，MySQL 5.7.9开始支持column->>pathjson_unquote(column -> path)的简洁写法json_keys提取json中的键值为json数组json_search按给定字符串关键字搜索json，返回匹配的路径修改jsonjson_append废弃，MySQL 5.7.9开始改名为json_array_appendjson_array_append末尾添加数组元素，如果原有值是数值或json对象，则转成数组后，再添加元素json_array_insert插入数组元素Json_insert插入值（插入新值，但不替换已经存在的旧值）json_merge合并json数组或对象json_remove删除json数据json_replace替换值（只替换已经存在的旧值）json_set设置值（替换旧值，并插入不存在的新值）json_unquote去除json字符串的引号，将值转成string类型返回json属性json_depth返回json文档的最大深度json_length返回json文档的长度json_type返回json值得类型json_valid判断是否为合法json文档
2.SQL & # 8211时间计算比较SQL中最常见的就是时间的查询比较。下面是我常用的一些SQL时间对比。
2.1.时间–增加、减少#现在时间增加1分钟SELECT date_add(now(), interval 1 HOUR_MINUTE)#现在时间减少1分钟SELECT date_sub(now(), interval 1 HOUR_MINUTE)
2.2时间格式转换#时间格式转换--年月日 时分秒select DATE_FORMAT(now(), '%Y-%m-%d')select DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s')
2.3今年/本月/本周/今天的统计数据(强调*)SQL中最多的时间比较应该是统计今年/本月/本周/今天的时间。
#统计本年/本月/本周/本日数据 #注：这里统计本周的时候多个1是因为美国时间和中国时间不是一样的，需要进行校正，网上很多都没有校正SELECTnow( ),to_days( now( ) ),YEARWEEK( date_format( now( ), '%Y-%m-%d' ), 1 ),YEAR ( now( ) ) WHEREto_days( now( ) ) = to_days( now( ) ) OR YEARWEEK( date_format( now( ), '%Y-%m-%d' ), 1 ) = YEARWEEK( now( ), 1 ) OR DATE_FORMAT( now( ), '%Y%m' ) = DATE_FORMAT( CURDATE( ), '%Y%m' ) OR YEAR ( now( ) ) = YEAR ( now( ) )
2.4时间格式汇总注意: SQL时间如下
YEAR，MONTH，DAY，HOUR，MINUTE，SECOND，MICROSECOND
而类似于这个区间‘1 2 3 4’Day_second根据时间类型从右到左排列，即1天2小时3分4秒，如下所示
#比当前时间减少了1天2小时3分钟4秒select now(), now() - interval '1 2 3 4' DAY_SECOND#比当前时间减少了1分钟select now(), now() - interval '1' HOUR_MINUTE
以下是SQL中所有的时间格式
MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH
3.SQL & # 8211字符串3.1.Concat & # 8211模糊查询select now() where now() like CONCAT('%', 'huyuqiao ','%')
3.2 .定位–查找字符串#子串在字符串中第一次出现索引(从1开始数，没找到则返回0)select locate('qiao', 'huyuqiaoqiao')
3.3.If、Case、Round & # 8211优化显示和字段计算以下用于优化查询字段。1可以更改为table.column
# 判断字段： Round(number,保留几位有效数字，默认四舍五入)SELECTIF( 1 = 1, '是', '否' ) AS '状态',CASE1 WHEN 1 THEN'扣款' WHEN 2 THEN'奖励' WHEN 3 THEN'投诉' END 申请类型,CONCAT( CASE 1 WHEN 1 THEN '-' WHEN 2 THEN '+' WHEN 3 THEN '-' END, ROUND( 200 / 100, 2 ) ) AS 金额
蜀山有精进之路，学海无涯。程序员不仅要懂代码，还要懂生活，关注我，共同进步。
