读mysql8 CookBook的零碎整理4

创建 JSON 数据表

CREATE TABLE emp_details
(
    emp_no int primary key,
    details json
);
-- 插入JSON 数据
INSERT INTO emp_details(emp_no,details)
VALUES ( 
    '1',
    '{
         "location": "IN"，
        "phone": "+11800000000",
        "email" : "abc@example. com",
        "address":{ "linel": "abc"，"line2": "xyz street"，
        		   "city": "Bangalore","pin" : "560103"} 
     }'
);

检索JSON:

-- 可以使用->和->>运算符检索JSON列的字段：
SELECT emp_no，details->'$ .address.pin' pin FROM emp_details;
--结果
+-—------ +----------=+
|  emp_no |   pin     |
+-—-------+-----------+
|   1     | "560103"  |
+—-----—-+—-—-—-—-—---+
-- 如果不用引号检索数据,可以使用->>运算符:
SELECT emp_no，details->>'$.address.pin ' pin FROM emp_details;
+-—------ +----------=+
|  emp_no |   pin     |
+-—-------+-----------+
|   1     | 560103    |
+—-----—-+—-—-—-—-—---+
    

JSON函数
MySQL提供了许多处理JSON数据的函数，让我们看看最常用的几种函数。优雅浏览

-- 想要以优雅的格式显示JSON值,请使用JSON_PRETTY()函数:
SELECT emp_no，JSON_PRETTY (details)FROM emp_details 
-- 结果：
emp no:1
JSON_PRETTY (details):{
    "email": "abc@example.com",
    "phone" : "+1180000000o",
    "address" :{
        "pin" : "560103",
        "city":"Bangalore",
        "line1" : "abc",
        "line2": "xyz street"
        }，
    "location":"IN"
}
--查找： 可以在WHERE子句中使用col->>path运算符来引用JSON的某一列:
SELECT emp_no FROM emp_details WHERE
details->>'$.address.pin'="560103";
--结果：
+—-----——+
| emp_no |
+-——-----+
|   1    |
+-——-----+
-- 也可以使用JSON_CONTAINS函数查询数据。如果找到了数据,则返回1,否则返回0:
SELECT JSON_CONTAINS(details->>'$.address.pin ',"560103") FROMemp_details;
-- 结果：
+-―--------------------------------------------------------+
|    JSON_CONTAINS(details->>'$.address.pin ', "560103")   |
+-―--------------------------------------------------------+
|                  1                                       |
+-―--------------------------------------------------------+
-- 如何查询一个 key?假设要检查address.line1是否存在:
SELECT JSON_CONTAINS_PATH(details, 'one', "$.address.line1")FROMemp_details;
-- 结果：
+-―--------------------------------------------------------+
| JSON_CONTAINS_PATH (details, 'one', "$.address.line1")   |
+-―--------------------------------------------------------+
|                  1                                       |
+-―--------------------------------------------------------+
-- 这里，one表示至少应该存在一个键。假设要检查address.line1或者 address.line2是否存在,代码如下:
    SELECT JSON_CONTAINS_PATH (details,'one ',"$.address.line1"," $ .address. line5") FROM emp_details;
-- 结果：
+-―---------------------------------------------------------------------------+
|JSON_CONTAINS_PATH (details,'one '， "$.address.line1","$ .address.line2")   |
+-―---------------------------------------------------------------------------+
|                                     1                                       |
+-―---------------------------------------------------------------------------+
-- 如果要检查address.linel和 address.line5是否同时存在，可以使用all，而不是one:
  SELECT JSON_CONTAINS_PATH (details,'all', "$.address.line1","$.address. line5") FROM emp_details;
  -- 结果：
+-―---------------------------------------------------------------------------+
| JSON_CONTAINS_PATH (details,'all ', "$ .address.line1","$.address.line5")   |
+-―---------------------------------------------------------------------------+
|                                     0                                       |
+-―---------------------------------------------------------------------------+

修改
可以使用三种不同的函数来修改数据:JSON_SET () 、JSON_INSERT ()和JsON_REPLACE()。在MySQL8之前的版本中，我们还需要对整个列进行完整的更新，这并不是最佳的方法。
JSON_SET():替换现有值并添加不存在的值。假设要替换员工的pin码，并添加昵称的详细信息,代码如下:

UPDATE
  emp_details
SET
  details = JSON_SET (details, "$.address.pin", "560100"，"$.nickname", "kai" )
WHERE  emp_no = 1;
-- JSON_INSERT ():插入值,但不替换现有值。
-- 假设你希望添加新列而不更新现有值，则可以使用JSON_INSERT ():
UPDATE 
   emp_details 
SET 
   details=JSON_INSERT (details,"$.address.pin","560132"，"$.address.line4"，"A Wing") 
WHERE  emp_no =1;
-- 在这种情况下,pin不会被更新，只会添加一个新的字段address.line4。
-- JSON_REPLACE ():仅替换现有值。
-- 假设只需要替换现有字段,不需要添加新字段,代码如下:
UPDATE 
  emp_details 
SET 
  details=JSON_REPLACE (details,"$ .address.pin"，"560132","$.address.line5","Landmark") 
WHERE emp_no= l;
    --在这种情况下，line5不会被添加,只有pin会被更新。

删除
JSON_REMOVE能从JSON文档中删除数据。
假设你不再需要地址中的line5，删除它的代码如下:

UPDATE emp_details SET details=JSON_REMOVE(details," $ .address.line5") WHERE emp_no = 1;

其他函数

-- JSON_KEYS():获取JSON文档中的所有键。
mysql>SELECT JSON_KEYS(details)FROM emp_details WHERE emp_no = 1;
JSON_KEYS (details):["email", "phone", "address", "nickname","location" ]
-- JSON LENGTH():给出JSON 文档中的元素数。
mysql> SELECT JSON_LENGTH (details)FROM emp_details WHERE emp_no = 1；

CTE函数

-- 非递归使用
WITH cte(archives_code,id) AS (
    SELECT 
        archives_code, 
        id 
    FROM 
    	archives_record 
     WHERE 
     	DEL_FLAG = '0'
    ORDER BY 
    create_time DESC  LIMIT #{from},#{to}
) SELECT t.* FROM  
    	archives_record t,
    	(SELECT id FROM cte) t9
    WHERE t.id = t9.id 

-- 递归使用 通过 code 和 parent_code 查找树数据
with recursive cte as (
   SELECT
         tr.CODE,
         tr.PARENT_CODE
      FROM
         TYPE_RELATION trsdfsdfsdfs
      WHERE  tr.code = #{code}
   UNION all
      SELECT
         tr.CODE,
         tr.PARENT_CODE
      FROM
         TYPE_RELATION tr
         INNER JOIN cte c2 on tr.PARENT_CODE = c2.CODE
   ) select * from cte

1.ROW_NUMBER () OVER (）生成新序列，按照c2排序，生成新序列
（以下参考 https://blog.51cto.com/u_11103985/2341971）

按照c1分组，c2排序生成序列

2.cume_dist() OVER (）在某种排序条件下，小于等于当前行值的行数/总行数
按照c1分组，c2排序，求当前行值的行数/总行数

3.rank() OVER (）类似于row_number() 生成新排序
按照c1分组，c2排序，生成新序列，当两个排序值相同时rank值相同

4.PERCENT_RANK() OVER （）当前RANK值-1/总行数-1

5.DENSE_RANK() OVER （）为了解决rank()编号存在的问题的，比如rank值出现两个1，会跳过2，而DENSE_RANK不会

6.FIRST_VALUE() OVER （）按照某种方式排序，求第一个值

7.LAST_VALUE() OVER （）按照某种分组排序求最后一个值

8.NTH_VALUE() OVER (）按某种方式排序求某一行的值

9.LAG(id) OVER (）按某种方式排序求上一行的值

10.lead () OVER (）按某种方式排序求下一行的值

11.将数据按照某些排序分成N组

先记录到这