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 表示參數為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用的很常見

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 金額

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