SQL--查詢JSON、時間、字符串的高級用法

本文章總結SQL的JSON、時間格式、字符串判斷轉換的使用。核心點還是在於Json字段的提取(1.5)、時間的比較(2.2,2.3)以及字符串的查詢(3.1),其他都算是優化和補充點。






@

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 錶示參數為null
select json_valid("huyuqiao"), json_valid('"huyuqiao"'), json_valid(null)
1.5.JSON_EXTRACT--提取json(重點*)
#json--查詢json內容(重點)
SELECT
JSON_EXTRACT(content, '$.blog' ) AS '個人博客',
JSON_EXTRACT(content, '$.content' ) AS '個人信息'
FROM
t_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是因為美國時間和中國時間不是一樣的,需要進行校正,網上很多都沒有校正
SELECT
now( ),
to_days( now( ) ),
YEARWEEK( date_format( now( ), '%Y-%m-%d' ), 1 ),
YEAR ( now( ) )
WHERE
to_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中時間的所有格式

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_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,保留幾比特有效數字,默認四舍五入)
SELECT
IF
( 1 = 1, '是', '否' ) AS '狀態',
CASE
1
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 金額

書山有路勤為徑,學海無涯苦作舟。程序員不僅要懂代碼,更要懂生活,關注我,一起進步。

SQL--查詢JSON、時間、字符串的高級用法的更多相關文章

  1. sql查詢關於時間的一些匯總

    今天的所有數據:select * from 錶名 where DateDiff(dd,datetime類型字段,getdate())=0 昨天的所有數據:select * from 錶名 where ...

  2. SQL 查詢當前時間

    Mysql: select date_format(now(),'%Y-%m-%d'); Oracle: Oracle中如何獲取系統當前時間進行語句的篩選是SQL語句的常見功能 獲取系統當前時間dat ...

  3. SQL獲取某個時間字符串裏的月和日,獲取某天是周幾

    select datename(weekday,'2016-11-4') as '周' select convert(varchar,datepart(month,'2016-11-4')) as ' ...

  4. ACCESS與MSSQL比較:SQL語句關於時間格式使用的注意點

    ACCESS與MSSQL比較:SQL語句關於時間字符串的使用:ACCESS數據庫使用 # 來控制時間格式字符串:mssql數據庫使用單引號 ' 來控制時間格式字符串.例: ACCESS版本:UPDAT ...

  5. SQL server 存儲過程 C#調用Windows CMD命令並返回輸出結果 Mysql删除重複數據保留最小的id C# 取字符串中間文本 取字符串左邊 取字符串右邊 C# JSON格式數據高級用法

    create proc [email protected] nvarchar(50),@Contents nvarchar(max),@UserId int,@CreateTime datetimeasi ...

  6. Newtonsoft.Json高級用法,json序列號,model反序列化,支持序列化和反序列化DataTable,DataSet,Entity Framework和Entity,字符串

    原文地址:https://www.cnblogs.com/yanweidie/p/4605212.html 手機端應用講究速度快,體驗好.剛好手頭上的一個項目服務端接口有性能問題,需要進行優化.在接口 ...

  7. SQL[連載3]sql的一些高級用法

    SQL[連載3]sql的一些高級用法 SQL 高級教程 SQL SELECT TOP SQL SELECT TOP 子句 SELECT TOP 子句用於規定要返回的記錄的數目. SELECT TOP ...

  8. Newtonsoft.Json高級用法(轉)

    手機端應用講究速度快,體驗好.剛好手頭上的一個項目服務端接口有性能問題,需要進行優化.在接口多次修改中,實體添加了很多字段用於中間計算或者存儲,然後最終用Newtonsoft.Json進行序列化返回數 ...

  9. 【轉】 Newtonsoft.Json高級用法

    手機端應用講究速度快,體驗好.剛好手頭上的一個項目服務端接口有性能問題,需要進行優化.在接口多次修改中,實體添加了很多字段用於中間計算或者存儲,然後最終用Newtonsoft.Json進行序列化返回數 ...

  10. Newtonsoft.Json高級用法 1.忽略某些屬性 2.默認值的處理 3.空值的處理 4.支持非公共成員 5.日期處理 6.自定義序列化的字段名稱

    手機端應用講究速度快,體驗好.剛好手頭上的一個項目服務端接口有性能問題,需要進行優化.在接口多次修改中,實體添加了很多字段用於中間計算或者存儲,然後最終用Newtonsoft.Json進行序列化返回數 ...

隨機推薦

  1. Spring快速入門

    什麼是Spring Spring是分層的JavaSE/EE full-stack(一站式) 輕量級開源框架 分層 SUN提供的EE的三層結構:web層.業務層.數據訪問層(持久層/集成層) Strut ...

  2. 1.2 基礎知識——關於猪皮(GP,Generic Practice)

    摘要: 這是<CMMI快樂之旅>系列文章之一.說起猪皮(GP,Generic Practice),真的讓人又愛又恨,中文翻譯叫通用實踐.CMMI標准中每個級別包含幾個PA,每個PA又包含幾 ...

  3. Bugtags,產品經理的瑞士軍刀

    做為設計移動應用的產品經理,每天的主要工作就是在手機上不停的體驗自己的產品,發現問題.優化體驗.你是否經常工作在這樣的尷尬場景: 發現界面問題,將問題界面截屏傳到電腦,用圖片標記工具將問題標記出來,然 ...

  4. SQL 生成可配置流水號

    需求背景每執行一次方法,根據公式返回最新的流水號.第一次使用時需要先插入一條數據,BizSeqValue 為流水起始號:A2014030000,Formula 為公式:A[yyyy][mm][c4], ...

  5. Access Violation at address 00000000.Read of address 00000000 解决辦法

    是數組越標或沒有初始化某個對象之類的問題,摟住細細檢查一下代碼, 使用指針前未做檢查,而這個指針未初始化. 可能是new後沒有delete,這樣出現溢出的可能性比較大     檢查代碼或者跟踪試試 使 ...

  6. formValidator

    formValidator輸入驗證.异步驗證實例 + licenseImage驗證碼插件實例應用   實例技術:springmvc 實現功能:完整用戶登錄流程.輸入信息規則校驗.驗證碼异步校驗. 功能 ...

  7. easyUI linkbutton組件

    easyUI linkbutton組件: <!DOCTYPE html> <html lang="en"> <head> <meta ch ...

  8. phpcms 制作簡單企業站的常用標簽

    標題 title 關鍵字 keywords 描述 description 來源 copyfrom 允許訪問 allow_visitor==1 thumb 縮略圖 {template "con ...

  9. Linux進程上下文切換過程context_switch詳解--Linux進程的管理與調度(二十一)

    1 前景回顧 1.1 Linux的調度器組成 2個調度器 可以用兩種方法來激活調度 一種是直接的, 比如進程打算睡眠或出於其他原因放弃CPU 另一種是通過周期性的機制, 以固定的頻率運行, 不時的檢測 ...

  10. java運算符-算數、賦值、比較

    1.算術運算符 運算符是用來計算數據的符號.數據可以是常量,也可以是變量.被運算符操作的數我們稱為操作數. 運算符 運算規則 範例 結果 + 正號 +3 3 + 加 2+3 5 + 連接字符串 “中” ...