sql 笔记

1.对null值的字段设置默认值 COALESCE

--  查询user表的用户email, 为null时设置默认为'未填写'
SELECT COALESCE(email, '未填写') FROM user WHERE user_id = '1';

2.拼接字段值 CONCAT

SELECT 
  CONCAT(COALESCE(bank_name, ''), '/', COALESCE(bankcard_number, '')) AS bankcard
FROM bank;

3.对存在同一属性的数据进行结构化查询JSON_ARRAYAGG、JSON_OBJECT

--  查询电话记录表phone_record下，单个用户的电话信息。可以用在单表重复属性数据查询、多表联合查询、多表子查询等等，灵活多变
SELECT 
  user_name, 
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'phone', phone,  -- 电话号码
      'created_at', created_at  -- 电话记录时间
      
    )
  ) as phone_infos
FROM phone_record
WHERE user_id = '1'
GROUP BY user_name;

4、格式化datetime时间为字符串DATE_FORMAT

-- created_at为datetime属性
SELECT 
  DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS formatted_time 
FROM service;

5、分组查询中，同字段字符拼接 GROUP_CONCAT

-- 分组查询用户user_id在应用品类(apptype)表中对应的所有品类名称，以逗号分隔
SELECT 
  GROUP_CONCAT(name SEPARATOR ', ') AS apptype_names 
FROM apptype 
GROUP BY user_id

6、根据字段条件定义查询值 CASE WHEN 字段条件 THEN xx ELSE xx END

SELECT 
  CASE WHEN datetime is not NULL THEN 0 ELSE 1 END AS datetime_exist 
FROM data_tb

7、判断数值是否在json字符串中,可使用JSON_CONTAINS

-- 如：category_ids为work表的一个json字符串字段（category_ids=[1, 2, 3, 123, 567, 999]），现需要查询id为123的值在work表的category_ids字段所对应的数据。

SELECT * FROM work
WHERE JSON_CONTAINS(category_ids, CAST('123' AS JSON), '$')



​​1. JSON_CONTAINS()​​
  这是 MySQL 提供的 JSON 函数，用于检查一个 JSON 文档是否包含另一个 JSON 文档
  如果包含则返回 1 (true)，否则返回 0 (false)

2. ​​category_ids​​
  这是你表中存储 JSON 字符串的列名
  假设它存储的是类似 "[1, 2, 123, 456]" 这样的 JSON 数组

​​3. CAST('123' AS JSON)​​
  将字符串 '123' 转换为 JSON 类型
  这是必要的，因为 JSON_CONTAINS() 要求比较的两个值都是 JSON 类型
​​4. '$'​​
  这是 JSON 路径表达式，表示从 JSON 文档的根开始搜索
  '$' 表示在整个 JSON 文档中查找

8、sql 递归查询WITH RECURSIVE

-- 递归查询出 id 在 (1, 3, 4, 5, 7) 的 所有子级（全部子层级）和自身的数据
WITH RECURSIVE category_tree AS (
    -- 基础查询
    SELECT id, parent_id, name
    FROM category
    WHERE id IN (1, 3, 4, 5, 7)
    
    UNION ALL
    
    -- 递归查询
    SELECT c.id, c.parent_id, c.name
    FROM category c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id FROM category_tree
-- WHERE id, parent_id, name NOT IN (1, 3, 4, 5, 7); 若想要结果不包含基础查询数据，可在外部加 WHERE 条件排除