SQL–查询JSON、时间、字符串的高级用法
本文章总结SQL的JSON、时间格式、字符串判断转换的使用。核心点还是在于Json字段的提取(1.5)、时间的比较(2.2,2.3)以及字符串的查询(3.1),其他都算是优化和补充点。
@
目录
1.SQL–JSON使用
1.1.JSON_ARRAY–转成数组
1.2.JSON_OBJECT–转成json对象
1.2.JSON_QUOTE–转成json
1.3.JSON_MERGE–合并json
1.4.JSON_VALID–判断是否为json
1.5.JSON_EXTRACT–提取json(重点*)
1.6.JSON完整内容
2.SQL–时间计算比较
2.1.时间–添加、减少
2.2时间格式转换
2.3统计本年/本月/本周/本日(重点*)
2.4 时间格式总结
3.SQL–字符串
3.1.Concat–模糊查询
3.2.locate–查找字符串
3.3.If、Case、Round–优化显示与字段计算
1.SQL–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–转成数组
# SQL--查询# json--对象转数组select json_array(content) from t_base_info
1.2.JSON_OBJECT–转成json对象
#json--对象转json格式--自定义select json_object(\'name\', \'huyuqiao\', \'age\', \'18\', \'profession\',\'Java开发工程师\')
1.2.JSON_QUOTE–转成json
#json--引用字符串作为json值select json_quote(\'\"huyuqiao\"\')
1.3.JSON_MERGE–合并json
#json--合并字符串select json_merge(\'{\"blog\": \"www.baidu.com\"}\', \'{\"name\":\"huyuqiao\"}\')
1.4.JSON_VALID–判断是否为json
#json--判断json格式是否有效--0 表示无效,1 表示有效,NUll 表示参数为nullselect json_valid(\"huyuqiao\"), json_valid(\'\"huyuqiao\"\'), json_valid(null)
1.5.JSON_EXTRACT–提取json(重点*)
#json--查询json内容(重点)SELECTJSON_EXTRACT(content, \'$.blog\' ) AS \'个人博客\',JSON_EXTRACT(content, \'$.content\' ) AS \'个人信息\' FROMt_base_info
1.6.JSON完整内容
官方文档:https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html
官网上给的语句很多,我挑的是比较常用的几个,主要还是JSON_EXTRACT用的很常见
分类 | 函数 | 描述 | 创建json | json_array | 创建json数组 | json_object | 创建json对象 | json_quote | 将json转成json字符串类型 | 查询json | json_contains | 判断是否包含某个json值 | json_contains_path | 判断某个路径下是否包json值 | json_extract | 提取json值 | column->path | json_extract的简洁写法,MySQL 5.7.9开始支持 | column->>path | json_unquote(column -> path)的简洁写法 | json_keys | 提取json中的键值为json数组 | json_search | 按给定字符串关键字搜索json,返回匹配的路径 | 修改json | json_append | 废弃,MySQL 5.7.9开始改名为json_array_append | json_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–时间计算比较
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
而且类似这种interval ‘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–字符串
3.1.Concat–模糊查询
select now() where now() like CONCAT(\'%\', \'huyuqiao \',\'%\')
3.2.locate–查找字符串
#子串在字符串中第一次出现索引(从1开始数,没找到则返回0)select locate(\'qiao\', \'huyuqiaoqiao\')
3.3.If、Case、Round–优化显示与字段计算
下面这些是用于优化查询字段的,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 金额
书山有路勤为径,学海无涯苦作舟。程序员不仅要懂代码,更要懂生活,关注我,一起进步。