MySQL 较复杂sql记录&MyBatis的动态sql拼接

1.建表技巧 先复制其他表结构类似的表,然后修改

CREATE TABLE `device_camera` LIKE `device_bracelets`

2.一个表中id与父id建立关系

UPDATE `region_structure` a INNER JOIN `region_structure` b ON b.`name`=a.`parent_name` 
SET a.`pgid`=b.`gid`

3.把b表的数据更新到a表

UPDATE `region_resident_derived` a,`sheet` b  
SET a.`town` =b.town,a.`village`=b.village,a.`relationship`=b.relationship
WHERE a.`residentid` = b.`jmid` 

4.查询当前年份(今年)某月的数据

SELECT * FROM `activity_threemeetings`
WHERE YEAR(`begintime`)=YEAR(NOW())
AND MONTH(`begintime`)='11'

5.查询当前年份(今年)某天的数据

SELECT * FROM `region_resident` 
WHERE DATE_FORMAT(createtime,'%Y-%m-%d') = '2020-12-23'

6.格式化日期

SELECT DATE_FORMAT(createtime,'%Y-%m-%d') createtime 
FROM `region_resident`

7.将一张表里的数据插入到另一张表(未测试)

# 表结构一样
insert into 表1 select * from 表2

# 表结构不一样
insert into 表1 (列名1,列名2,列名3) select  列1,列2,列3 from 表2

# 只从另外一个表取部分字段
insert into 表1 (列名1,列名2,列名3) values(列1,列2,(select 列3 from 表2));

8.多字段模糊匹配

# 根据catelog_id查，并且用一个key搜索匹配多个字段
selectc * from pms_attr_group where catelog_id=? and (attr_group_id=key or attr_group_name like %key%)

# mybatis plus构造以上SQL内容(like为双百分号的，注意如果想写左右百分号就可以使用likeLeft或likeRight)
        String key = (String) params.get("key");
            // 有三级分类，我们按三级分类查
            // 并且要多字段模糊匹配：selectc * from pms_attr_group where catelog_id=? and (attr_group_id=key or attr_group_name like %key%)
            QueryWrapper<AttrGroupEntity> queryWrapper = new QueryWrapper<AttrGroupEntity>().eq("catelog_id", catelogId);
            if (StringUtils.isNoneBlank(key)) {
                // key不为空，继续构造
                queryWrapper.and(obj -> {
                    obj.eq("attr_group_id", key).or().like("attr_group_name", key);
                });
            }

            IPage<AttrGroupEntity> page = this.page(
                    new Query<AttrGroupEntity>().getPage(params), queryWrapper);
            return new PageUtils(page);

9.批量删除动态sql拼接（也可单个删除）

void deleteBatchRelation(@Param("relationList") List<AttrAttrGroupRelationDto> relationList);

    <!-- 删除属性与分组的关联关系 (注意separator=" or "留出空格) -->
    <delete id="deleteBatchRelation">
        DELETE
        FROM
	        pms_attr_attrgroup_relation
        WHERE
            <foreach collection="relationList" item="item" separator=" or ">
                attr_id = #{item.attrId} AND attr_group_id = #{item.attrGroupId}
            </foreach>
    </delete>

# 查询当前表名
select database()

# 查询某个表的字段信息
select 
	column_name, 
	(case when (is_nullable = 'no' AND column_key != 'PRI') then '1' else null end) as is_required, 
	(case when column_key = 'PRI' then '1' else '0' end) as is_pk, ordinal_position as sort, column_comment, 
	(case when extra = 'auto_increment' then '1' else '0' end) as is_increment, 
	column_type
from information_schema.columns 
where table_schema = (select database()) and table_name = ('blade_notice')
order by ordinal_position

10.分组及分组拼接

分组及分组拼接前(不分组时)：

分组及分组拼接后(去重)：

分组及分组拼接SQL：

# GROUP_CONCAT(DISTINCT ssav.attr_value)为分组拼接函数，DISTINCT去重
# 注意：linux下不区分大小写，所以这儿起别名最好是下划线隔开，映射到实体类中也是没问题的
SELECT
	ssav.attr_id attr_Id,
	ssav.attr_name attr_name,
	GROUP_CONCAT(DISTINCT ssav.attr_value) attr_value 
FROM
	pms_sku_info info
	LEFT JOIN pms_sku_sale_attr_value ssav ON ssav.sku_id = info.sku_id
WHERE
	info.spu_id = 13
GROUP BY
	ssav.attr_id,
	ssav.attr_name

实体类：