示例MySQL DDL语句、批量插入，复杂sql，另一张表的统计作为这张表的字段&查看表的创建语句DDL

最新推荐文章于 2022-10-24 19:11:22 发布

Be_insighted

最新推荐文章于 2022-10-24 19:11:22 发布

阅读量388

点赞数 1

分类专栏：数据库杂记和踩坑文章标签： mysql 复杂sql

本文链接：https://blog.csdn.net/Be_insighted/article/details/120337469

版权

杂记和踩坑同时被 2 个专栏收录

239 篇文章 7 订阅

订阅专栏

数据库

88 篇文章 3 订阅

订阅专栏

创建表DDL示例

DROP TABLE IF EXISTS `t_products`;
CREATE TABLE `t_products`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '产品类型ID',
  `object_id` bigint(20) NULL DEFAULT NULL COMMENT '物模型ID',
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `description` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '产品类型描述',
  `creator_id` int(11) NULL DEFAULT NULL COMMENT '创建者的工号',
  `category_id` int(11) NOT NULL COMMENT '产品领域ID',
  `category_name` varchar(255) NOT NULL COMMENT '产品领域名称',
  `node_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '节点类型，0：直连设备，1：网关设备，2:网关子设备',
  `link_type` varchar(128) CHARACTER SET utf8mb4_bin COLLATE utf8mb4_general_ci NOT NULL COMMENT '联网方式',
  `auth_type` varchar(128) CHARACTER SET utf8mb4_bin COLLATE utf8mb4_general_ci NOT NULL COMMENT '认证方式',
  `state` int(11) NOT NULL COMMENT '发布状态，0：未发布，1：发布',
  `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime(0) NOT DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
  `inner_type` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '内部型号',
  `product_ref_num` int(11) NULL DEFAULT NULL COMMENT '关联产品规格个数',
  `object_category_id` bigint(20) NULL DEFAULT NULL COMMENT '关联物模型模板时有意义',
  `creator_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建者的loginName',
  `deleted` tinyint(2) NOT NULL DEFAULT 0 COMMENT '删除标志，1：已删除',
  PRIMARY KEY (`id`, `update_time`) USING BTREE COMMENT '联合主键，意义不大',
  UNIQUE INDEX `IDX_INNER_TYPE`(`inner_type`, `deleted`) USING BTREE COMMENT '全网唯一，最小长度1，最大64',
  UNIQUE INDEX `IDX_NAME_CATEGORY_UNI`(`name`, `category_id`, `deleted`) USING BTREE COMMENT '同一产品领域下，产品类型唯一',
  INDEX `IDX_UPDATE_TIME`(`update_time`) USING BTREE
) ENGINE = InnoDB  CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '产品类型' ROW_FORMAT = Compact;

CREATE TABLE `tb_estate` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL COMMENT '楼盘名称',
  `province` varchar(10) DEFAULT NULL COMMENT '所在省',
  `city` varchar(10) DEFAULT NULL COMMENT '所在市',
  `area` varchar(10) DEFAULT NULL COMMENT '所在区',
  `address` varchar(100) DEFAULT NULL COMMENT '具体地址',
  `year` varchar(10) DEFAULT NULL COMMENT '建筑年代',
  `type` varchar(10) DEFAULT NULL COMMENT '建筑类型',
  `property_cost` varchar(10) DEFAULT NULL COMMENT '物业费',
  `property_company` varchar(20) DEFAULT NULL COMMENT '物业公司',
  `developers` varchar(20) DEFAULT NULL COMMENT '开发商',
  `created` datetime DEFAULT NULL COMMENT '创建时间',
  `updated` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1006 DEFAULT CHARSET=utf8mb4 COMMENT='楼盘表';

-- 初始数据
INSERT INTO `tb_estate` (`id`, `name`, `province`, `city`, `area`, `address`, `year`, `type`, `property_cost`, `property_company`, `developers`, `created`, `updated`) VALUES ('1001', '中远两湾城', '上海市', '上海市', '普陀区', '远景路97弄', '2001', '塔楼/板楼', '1.5', '上海中远物业管理发展有限公司', '上海万业企业股份有限公司', '2018-11-06 23:00:20', '2018-11-06 23:00:23');
INSERT INTO `tb_estate` (`id`, `name`, `province`, `city`, `area`, `address`, `year`, `type`, `property_cost`, `property_company`, `developers`, `created`, `updated`) VALUES ('1002', '上海康城', '上海市', '上海市', '闵行区', '莘松路958弄', '2001', '塔楼/板楼', '1.5', '盛孚物业', '闵行房地产', '2018-11-06 23:02:30', '2018-11-27 23:02:33');
INSERT INTO `tb_estate` (`id`, `name`, `province`, `city`, `area`, `address`, `year`, `type`, `property_cost`, `property_company`, `developers`, `created`, `updated`) VALUES ('1003', '保利西子湾', '上海市', '上海市', '松江区', '广富林路1188弄', '2008', '塔楼/板楼', '1.75', '上海保利物业管理', '上海城乾房地产开发有限公司', '2018-11-06 23:04:22', '2018-11-06 23:04:25');
INSERT INTO `tb_estate` (`id`, `name`, `province`, `city`, `area`, `address`, `year`, `type`, `property_cost`, `property_company`, `developers`, `created`, `updated`) VALUES ('1004', '万科城市花园', '上海市', '上海市', '松江区', '广富林路1188弄', '2002', '塔楼/板楼', '1.5', '上海保利物业管理', '上海城乾房地产开发有限公司', '2018-11-13 16:43:40', '2018-11-13 16:43:42');
INSERT INTO `tb_estate` (`id`, `name`, `province`, `city`, `area`, `address`, `year`, `type`, `property_cost`, `property_company`, `developers`, `created`, `updated`) VALUES ('1005', '上海阳城', '上海市', '上海市', '闵行区', '罗锦路888弄', '2002', '塔楼/板楼', '1.5', '上海莲阳物业管理有限公司', '上海莲城房地产开发有限公司', '2018-11-06 23:23:52', '2018-11-06 23:23:55');

CREATE TABLE `tb_house_resources` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) DEFAULT NULL COMMENT '房源标题',
  `estate_id` bigint(20) DEFAULT NULL COMMENT '楼盘id',
  `building_num` varchar(5) DEFAULT NULL COMMENT '楼号（栋）',
  `building_unit` varchar(5) DEFAULT NULL COMMENT '单元号',
  `building_floor_num` varchar(5) DEFAULT NULL COMMENT '门牌号',
  `rent` int(10) DEFAULT NULL COMMENT '租金',
  `rent_method` tinyint(1) DEFAULT NULL COMMENT '租赁方式，1-整租，2-合租',
  `payment_method` tinyint(1) DEFAULT NULL COMMENT '支付方式，1-付一押一，2-付三押一，3-付六押一，4-年付押一，5-其它',
  `house_type` varchar(255) DEFAULT NULL COMMENT '户型，如：2室1厅1卫',
  `covered_area` varchar(10) DEFAULT NULL COMMENT '建筑面积',
  `use_area` varchar(10) DEFAULT NULL COMMENT '使用面积',
  `floor` varchar(10) DEFAULT NULL COMMENT '楼层，如：8/26',
  `orientation` varchar(2) DEFAULT NULL COMMENT '朝向：东、南、西、北',
  `decoration` tinyint(1) DEFAULT NULL COMMENT '装修，1-精装，2-简装，3-毛坯',
  `facilities` varchar(50) DEFAULT NULL COMMENT '配套设施， 如：1,2,3',
  `pic` varchar(200) DEFAULT NULL COMMENT '图片，最多5张',
  `house_desc` varchar(200) DEFAULT NULL COMMENT '描述',
  `contact` varchar(10) DEFAULT NULL COMMENT '联系人',
  `mobile` varchar(11) DEFAULT NULL COMMENT '手机号',
  `time` tinyint(1) DEFAULT NULL COMMENT '看房时间，1-上午，2-中午，3-下午，4-晚上，5-全天',
  `property_cost` varchar(10) DEFAULT NULL COMMENT '物业费',
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='房源表';

批量插入注意小括号()

create table stock(
	id int auto_increment not null,
	product_id int not null comment '商品ID',
	category_id int not null comment '分类ID',
	warehouse_id int not null comment '仓库ID',
	count int not null comment '库存数量',
	primary key(id)
	) comment '库存表';

insert into stock(product_id,category_id,warehouse_id,count)
	values(2030,9,1,10),(2030,9,2,15),(2030,9,3,20),(2040,8,1,30),
	(2040,8,2,20);

select product_id,warehouse_id,sum(count) from stock group by product_id;

以表中已有的字段递增作为新记录的值

    /**
     * INSERT INTO t_product_inner_identifier ( product_id, identifier, ref ) SELECT
     * 1,
     * '22',
     * ref + 1 
     * FROM
     * 	t_product_inner_identifier 
     * WHERE
     * 	product_id = 1 
     * ORDER BY
     * 	ref DESC 
     * 	LIMIT 1
     * @param entity
     * @return
     */
    @Insert("insert into t_product_inner_identifier(product_id, identifier, ref) " +
            "select #{productId}, #{identifier},ref + 1 from t_product_inner_identifier where product_id = #{productId} order by ref desc limit 1")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertEntity(ProductInnerIdentifierEntity entity);

分页查询的mapper

select
        distinct
        p.id,
        p.object_id,
        p.name,
        p.description,
        p.creator_id,
        p.category_id,
        p.node_type,
        p.link_type,
        p.auth_type,
        p.state,
        p.inner_type,
        p.object_category_id,
        p.create_time,
        p.product_ref_num,
        p.creator_name,
        p.update_time,
        pc.name as categoryName
        from t_products p
        left join t_product_category pc
        on p.category_id = pc.id
        <where>
            <if test="queryDTO.productStatus != null">
                AND product_status = #{queryDTO.productStatus}
            </if>
            <if test="queryDTO.name != null and queryDTO.name != ''">
                AND INSTR(name,#{queryDTO.name})
            </if>
        </where>
        order by
        update_time desc
    </select>

mysql在不给定order by条件的时候，得到的数据结果的顺序是跟查询列有关的。因为在不同的查询列的时候，可能会使用到不同的索引条件。 Mysql在使用不同索引的时候，得到的数据顺序是不一样的。

SELECT DISTINCT
  ps.id,
  ps.product_id,
  ps.identifier,
  ps.name,
  ps.description,
  ps.system_call,
  ps.original_required,
  ps.update_required,
  ps.custom,
  ps.method,
  ps.create_time,
  ps.update_time,
  (SELECT count(*)
   FROM t_product_services_params
   WHERE ref_id = ps.id AND type = 0) AS inputData,
  (SELECT count(*)
   FROM t_product_services_params
   WHERE ref_id = ps.id AND type = 1) AS outputData
FROM t_product_services ps LEFT JOIN t_product_services_params psp ON ps.id = psp.ref_id
WHERE ps.product_id = ?
ORDER BY update_time DESC
LIMIT ?, ?

concat函数，模糊查询时需要注意单个字符为'%'和'_'的情况，否则会全匹配，控制层参数需要进行相关拦截！

SELECT
        t1.*,COUNT(t2.id)AS alarmCount
        FROM TB_EXAMPLE_RESOURCE t1 LEFT JOIN tb_alarm t2 on t1.exampleID = t2.exampleId
        <where>
            t1.device_flag = #{deviceFlag} ## 必传字段
            <if test="frontDevType != null and frontDevType.size>0">
                AND (<foreach collection="frontDevType" item="item" index="index" separator="OR">
                t1.FrontDevType = #{item, jdbcType=VARCHAR}
            </foreach>)
            </if>
            <if test="keyword != null and keyword != ''">
                AND (t1.exampleName LIKE concat('%',#{keyword},'%')
                OR t1.fileName LIKE concat('%',#{keyword},'%'))
            </if>
           
            <if test="exampleId > 0">
                AND t1.exampleID = #{exampleId}
            </if>
            <if test="userName != null and userName != ''">
                AND t1.CREATE_NAME = #{userName, jdbcType=VARCHAR}
            </if>

        </where>
        GROUP BY t1.EXAMPLEID
        ORDER BY t1.EXAMPLEID DESC

查看表的创建语句 (show create table 表名)

Be_insighted

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
示例MySQL DDL语句、批量插入，复杂sql，另一张表的统计作为这张表的字段&查看表的创建语句DDL

DROP TABLE IF EXISTS `t_products`;CREATE TABLE `t_products` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '产品类型ID', `object_id` bigint(20) NULL DEFAULT NULL COMMENT '物模型ID', `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT N.
复制链接

扫一扫

专栏目录