MySQL常用及复杂sql

本文主要是总结一下我遇到过的常用的和复杂的sql语句

查询

• 进行模糊查询的时候有三种方式

# 模糊查询name为'cs'的
    name like '%cs%' 全匹配
    
# 模糊查询name开头为'cs'的
    like '%cs' 左匹配
    
# 模糊查询name结尾为'cs'的
    like 'cs%' 右匹配

修改

• 现在有两张表A和B，通过 A.uuid = B.aUuid关联，现在想要把b里的name字段赋值到A的name字段里，可以这样写

    update A a 
    left join B b on a.uuid = b.aUuid
    set a.name = b.name

Json 相关

• 首先创建一个简单的表

    CREATE TABLE `json_test` (
      `uuid` varchar(32) NOT NULL,
      `json1` text,
      `json2` text,
      `json3` text,
      PRIMARY KEY (`uuid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

• json1：{“id”:“123”,“name”:“cs”}

# 读取Json里的某一个值,读取name如下

    select JSON_EXTRACT( json1, '$.name' ) as name from json_test;

-- 结果如下
    "cs"

• json2:{“student”:{“id”:“123”,“name”:“cs”}}

# 读取Json中对象的某一个值，读取student的name如下

    select JSON_EXTRACT( json2, '$.student.name' ) as name from json_test;

-- 结果如下
    "cs"

• json3:{“students”:[{“id”:“1”,“name”:“cs1”},{“id”:“2”,“name”:“cs2”}]}

# 读取Json中数组对象的值，并转换为行数据，读取students的name如下

    select JSON_EXTRACT( JSON_EXTRACT( json3, '$.students[*].name' ), CONCAT( '$[', idx, ']' )) as name
    from json_test
    # 注意这里连接的内部表最多可以读取32个
    JOIN (
	SELECT
		0 AS idx UNION
	SELECT
		1 AS idx
	) AS indexes
	# 不想读取出null值时就在where里做判断，不能写name is not null ，name值读取不到
	where JSON_EXTRACT( JSON_EXTRACT( json3, '$.students[*].name' ), CONCAT( '$[', idx, ']' )) is not null;
	
# 上述Json读取多个字段的时候只要连接一次内部表就行了
    select JSON_EXTRACT( JSON_EXTRACT( json3, '$.students[*].id' ), CONCAT( '$[', idx, ']' )) as id,
    JSON_EXTRACT( JSON_EXTRACT( json3, '$.students[*].name' ), CONCAT( '$[', idx, ']' )) as name
    from json_test
    JOIN (
	SELECT
		0 AS idx UNION
	SELECT
		1 AS idx
	) AS indexes;
	
-- 结果1如下
    "cs1"
    "cs2"
-- 结果2如下
    "1"|"cs1"
    "2"|"cs2"

• 去除双引号

    #读取Json后会带有双引号可以使用下面两种函数去除双引号
    
    #1.替换字符
    REPLACEALL(str,'"','')
    
    #2.JSON_UNQUOTE():将原json的引号去掉，返回字符串，如果参数为null，则返回NULL。
    JSON_UNQUOTE((JSON_EXTRACT(json1, '$.name'))

• 拼接Json（最好不要碰到这种情况）

#其实就是进行字符串的拼接，就是写起来很麻烦

    CONCAT(
        '{',
        CONCAT_WS(',',
            CONCAT('"','code', '":"', p.`code`, '"'),
            CONCAT('"','productName', '":"', p.name, '"')
            '}'
    )