query查询过程

SELECT A.id, B.id
--  内链接 WHERE 条件必须同时满足
FROM table_a AS A, table inner I
LEFT JOIN table_b AS B 
    --  ON 条件的过滤 必须满足，外链接 WHERE条件的过滤不满足时，B表字段以NULL显示
    ON B.idx_key = A.idx_key AND (contition)
WHERE 
    --  条件顺序：先精准匹配，后范围，最后模糊；先索引，后普通列；先主表，后副表；
    --  子查询
    (A.id, A.column) IN (SELECT min(id), max(column) FROM table_a as T)
    AND B.column_1 IN (限制枚举值在500以内，推荐分页数为200)
    EXISTS (condition)
GROUP BY [构成唯一标识的 分组条件]
HAVING (聚合后的过滤条件)
ORDER BY
LIMIT $offset,$limit

--  ⽅式1
SELECT *
FROM employees a
WHERE a.employee_id = (SELECT min(employee_id) FROM employees)
    AND a.salary = (SELECT max(salary) FROM employees);

--  ⽅式2
SELECT *
FROM employees a
WHERE (a.employee_id, a.salary) = 
    (SELECT min(employee_id), max(salary) FROM employees)

--  ⽅式3
SELECT *
FROM employees a
WHERE (a.employee_id, a.salary) in 
    (SELECT min(employee_id), max(salary) FROM employees);

--  返回的一般都是true or false 或者 1 和 0
json_contains_path(json_doc, one_or_all, paths)
--  json_doc就是json数据
--  paths是指要找的key，可以传入多个的key参数
--  one_or_all指一个值是one表示找出paths参数中的任意一个，all表示找出全部

--  解析json一维数组 是否含有position
select json_contains_path("{'name':'xxx', 'age':'保密','position':'php 工程师'}",
 'one', '$.position')
--  返回的结果是1

--  解析json一维数组 是否同时含有position，age
select json_contains_path("{'name':'xxx', 'age':'保密','position':'php 工程师'}",
 'all', '$.position', '$.age');
--  返回的结果是1

--  解析二维json数组的某个key
SELECT *
FROM TABLE_NAME T
WHERE JSON_CONTAINS(T.json_column,JSON_OBJECT("index_name", 'index_value'))

--  判定json字段是否为空数组
SELECT *
FROM TABLE_NAME T
WHERE T.json_column  like '[]'

--  判定json数组指定下表的元素是否存在
SELECT uniq_code, json_extract(column_name,'$.key')
FROM table_name
where JSON_EXTRACT(column_name,'$.key')  IS NULL
-- IS NOT NULL
SELECT uniq_code, json_extract(column_name,'$.key')
FROM table_name
where JSON_EXTRACT(column_name,'$.key') > ''

-- json字段的IN条件是数值数组时
$array = [];
if (count($array) == 1) {
    $string = "'".current($array)."'";
} else {
    foreach ($array as &$item) {
        $string = implode(',', (int)item);
    }
    unset($item);
}

SELECT uniq_code, json_extract(column_name,'$.key')
FROM table_name
where JSON_EXTRACT(column_name,'$.key') in ($string)



-- 需求：查找 config JSON字段（对象类型）中 fieldModels（数组类型）数组字段中 valueMapping（整形）值等于 17 的记录
-- 表字段：id, config
-- config字段格式：
/*
{
    "fieldModels": [{
        "key": 0,
        "guid": "1",
        "field": "Id",
        "dataType": 1,
        "showName": "标识",
        "textFormat": "",f
        "valueMapping": 17
    }, {
        "key": 1,
        "guid": "2",
        "field": "orderid",
        "dataType": 0,
        "showName": "orderid",
        "textFormat": "",
        "valueMapping": -1
    }
}
*/
SELECT id, config FROM `sql_model` 
WHERE JSON_CONTAINS(JSON_EXTRACT(`config`,'$.fieldModels'), JSON_OBJECT('valueMapping', @valueMapping)) > 0;

-- 查询JSON字段(数组)中是否包含某个值
SELECT * FROM menus
WHERE JSON_CONTAINS(support_coop_mode, '3');

-- 时间格式
SELECT
  id, JSON_EXTRACT( origin_data, '$.completedAt' ) as completedAtTime , CONVERT_TZ(TRIM('"' from JSON_EXTRACT( origin_data, '$.completedAt' )),'+00:00','+08:00') as completedAt,
  JSON_EXTRACT( origin_data, '$.createdAt' ) as createdAtTime , CONVERT_TZ(TRIM('"' from JSON_EXTRACT( origin_data, '$.createdAt' )),"+08:00","+08:00") as createdAt,
  origin_data
FROM
  `shopify_checkouts`
WHERE
    CONVERT_TZ(TRIM('"' from JSON_EXTRACT( origin_data, '$.createdAt' )),"+08:00","+08:00") > CONVERT_TZ(TRIM('"' from JSON_EXTRACT( origin_data, '$.completedAt' )),'+00:00','+08:00')
  limit 10

SELECT *
FROMmy_order o
INNER JOIN my_appraise a ON a.orderid = o.id
ORDER BY a.is_reply ASC, a.appraise_time DESC
LIMIT  0, 20

SELECT *
FROM (
    (
        SELECT *
        FROM my_order o
        INNER JOIN my_appraise a ON a.orderid = o.id AND is_reply = 0
        ORDER BY appraise_time DESC LIMIT  0, 20
    )
    UNION ALL
    (
        SELECT *
        FROM my_order o
        INNER JOIN my_appraise a ON a.orderid = o.id AND is_reply = 1
        ORDER BY appraise_time DESC LIMIT  0, 20)
) t
ORDER BY is_reply ASC, appraisetime DESC 
LIMIT  20;

UPDATE table_a AS A
LEFT JOIN table_b AS B ON A.f_key = B.f_key
SET A.column_c = B.column_c
WHERE A.column_d condition AND B.column_e condition

insert into table_name(field1,field2,field3)
SELECT colm1,colm2,colm3 FROM t1 WHERE condition 

REPLACE INTO table_name (column_name1, column_name2)
  SELECT column1, column2 
  FROM table1 
  LEFT JOIN table2 ON condition 
  WHERE condition 
  GROUP BY union_unique_key

-- 低配版本
SELECT * FROM student WHERE id < 1000 LIMIT 10,10 ;
SELECT count(id) FROM student WHERE id < 1000 ;

-- 高配版本
SELECT SQL_CALC_FOUND_ROWS *
FROM student
WHERE id < 1000
LIMIT 10, 10;

SELECT FOUND_ROWS() AS total_count;

SELECT    a.*,
          c.allocated
FROM      (
                   SELECT   resourceid
                   FROM     my_distribute d
                   WHERE    isdelete = 0
                   AND      cusmanagercode = '1234567'
                   ORDER BY salecode limit 20) a
LEFT JOIN
          (
                   SELECT   resourcesid， sum(ifnull(allocation, 0) * 12345) allocated
                   FROM     my_resources r,
                            (
                                     SELECT   resourceid
                                     FROM     my_distribute d
                                     WHERE    isdelete = 0
                                     AND      cusmanagercode = '1234567'
                                     ORDER BY salecode limit 20) a
                   WHERE    r.resourcesid = a.resourcesid
                   GROUP BY resourcesid) c
ON        a.resourceid = c.resourcesid

WITH a AS
(
         SELECT   resourceid
         FROM     my_distribute d
         WHERE    isdelete = 0
         AND      cusmanagercode = '1234567'
         ORDER BY salecode 
         LIMIT 20
)
SELECT    a.*,
          c.allocated
FROM      a
LEFT JOIN(
         SELECT   resourcesid, sum(ifnull(allocation, 0) * 12345) allocated
         FROM     my_resources r,  a
         WHERE    r.resourcesid = a.resourcesid
         GROUP BY resourcesid
    ) c ON        a.resourceid = c.resourcesid