[大数据]mysql常用语句

json查询

建表，初始化数据

drop table if exists test_json;
create table test_json
(
    jid   int auto_increment
        primary key,
    data json null
);
insert into test_json values(1, '[{"id": "1", "name": "张三", "age": "10"}, {"id": "2", "name": "李四", "age": "12"}]');
insert into test_json values(2, '[{"id": "3", "name": "王五", "age": "13"}, {"id": "4", "name": "马六", "age": "18"}]');

查询json中属性

select data->'$[*].id' id, data->'$[*].name' name, data->'$[*].age' age from test_json;
select data->'$[*].id' id, data->'$[*].name' name, data->'$[*].age' age from test_json where data->'$[0].id' = '3';
select data->'$[*].id' id, data->'$[*].name' name, data->'$[*].age' age from test_json where json_contains(data, json_object('id', '3'));

批量插入、更新

建表

drop table if exists test_batch;
create table test_batch
(
   id int auto_increment,
   name varchar(20) null,
   age int null,
   email varchar(20) null,
   constraint test_batch_pk
      primary key (id)
);

初始化数据

# 批量插入
INSERT INTO test_batch(name, age, email)
VALUES ('张三', 10, 'zhangsan@qq.com'),
       ('李四', 12, 'lisi@qq.com'),
       ('王二麻子', 15, 'wangermazi@qq.com');

# 批量更新
UPDATE test_batch
SET name = CASE
               WHEN id = 1 THEN '张三1'
               WHEN id = 2 THEN '李四1'
               WHEN id = 3 THEN '李四1' END,
    age  = CASE
               WHEN id = 1 THEN 101
               WHEN id = 2 THEN 121
               WHEN id = 3 THEN 151 END
WHERE id = 1
   OR id = 2
   OR id = 3;

mybaits mapper.xml

# 批量插入
<insert id="batchSave" parameterType="list">
    INSERT INTO test_batch (name, age, email)
    VALUES
    <foreach collection="list" index="item" item="item" separator=",">
        (#{item.name}, #{item.age}, #{item.email})
    </foreach>
</insert>

# 批量更新
<update id="batchUpdate" parameterType="list">
    UPDATE test_batch
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="name = case " suffix="end, ">
            <foreach collection="list" item="item" index="index">
                <if test="item.name != null">
                    WHEN id = #{item.id} THEN #{item.name}
                </if>
            </foreach>
        </trim>
        <trim prefix="release_ratio = case " suffix="end, ">
            <foreach collection="list" item="item" index="index">
                <if test="item.releaseRatio != null and item.releaseRatio != ''">
                    WHEN id = #{item.id} THEN #{item.releaseRatio}
                </if>
            </foreach>
        </trim>
        <trim prefix="age = case " suffix="end, ">
            <foreach collection="list" item="item" index="index">
                <if test="item.age!= null">
                    WHEN id = #{item.id} THEN #{item.age}
                </if>
            </foreach>
        </trim>
    </trim>
    WHERE
    <foreach collection="list" separator="or" item="item" index="index" >
        id = #{item.id}
    </foreach>
</update>

GROUP_CONCAT()函数

建表

# 角色表
drop table if exists test_role;
create table test_role
(
   id int auto_increment,
   name varchar(10) null,
   constraint test_role_pk
      primary key (id)
);
# 用户表
drop table if exists test_user;
create table test_user
(
   id int auto_increment,
   name varchar(20) null,
   constraint test_user_pk
      primary key (id)
);
# 用户关联角色表
drop table if exists test_user_role;
create table test_user_role
(
   id int auto_increment,
   user_id int null,
   role_id int null,
   constraint test_user_role_pk
      primary key (id)
);

初始化数据

# 角色表
INSERT INTO test_role (name)
VALUES ('管理员'), ('董事长'), ('总经理');
# 用户表
INSERT INTO test_user (name)
VALUES ('张三'), ('李四');
# 用户关联角色表
INSERT INTO test_user_role (user_id, role_id)
VALUES (1, 1), (1, 2), (2, 3);

查询

# 返回一个字符串，由分组中的值连接而成
SELECT t.id, t.name, GROUP_CONCAT(tr.id) roleId, GROUP_CONCAT(tr.name) roleName
FROM test_user t
         LEFT JOIN test_user_role tur ON t.id = tur.user_id
         LEFT JOIN test_role tr ON tur.role_id = tr.id
GROUP BY t.id;

CONCAT_WS()函数

# 使用语法： CONCAT_WS(separator,str1,str2,…)
# 字符串拼接，可以用于插入或更新sql的生成
SELECT id,
       CONCAT_WS('', 'insert into test_user_role (user_id, role_id) values (', id, ', 3);') insertSql,
       CONCAT_WS('', 'update test_user set name = ''', id, ''' where id = ''', id, ''';')   updateSql
FROM test_user
WHERE id = 2;