forest-mall-cloud/sql/表结构变化语句/更新sql.sql

#删除产品数据
DELETE FROM pms_product WHERE create_time >='2020-10-9 00:00:00';

#供应商
DELETE FROM ums_company_info WHERE create_time >='2020-10-9 00:00:00';
#供应商文件
DELETE FROM ums_company_file WHERE create_time >='2020-10-9 00:00:00';
#供应商商店
DELETE FROM ums_company_shop WHERE create_time >='2020-10-9 00:00:00';

#生产地
DELETE FROM ums_member_origin WHERE create_time >='2020-10-9 00:00:00';
#生产地产出物
DELETE FROM ums_member_origin_product WHERE create_time >='2020-10-9 00:00:00';

# 更新商品大类
UPDATE pms_product a
LEFT JOIN pms_product_category b ON a.product_big_category_name = b.`name`
SET a.product_big_category_id = b.id
WHERE
	b.parent_id = 0
AND a.create_time >= '2020-10-9 00:00:00';

# 更新商品小类
UPDATE pms_product a
LEFT JOIN pms_product_category c ON a.product_category_name = c.`name`
SET a.product_category_id = c.id
WHERE
	c.parent_id != 0
AND a.create_time >= '2020-10-9 00:00:00';

# 更新商品大类小类
UPDATE pms_product a
LEFT JOIN pms_product_category b ON a.product_big_category_name = b.`name`
LEFT JOIN pms_product_category c ON a.product_category_name = c.`name`

SET a.product_big_category_id = b.id,
 a.product_category_id = c.id
WHERE
	b.parent_id = 0
AND c.parent_id != 0
AND a.create_time >= '2020-10-9 00:00:00';

# 品牌表新增字段

ALTER TABLE pms_brand ADD COLUMN `create_id` BIGINT (20) DEFAULT NULL COMMENT '创建人' AFTER `brand_story`;

ALTER TABLE pms_brand ADD COLUMN `create_time` datetime DEFAULT NULL COMMENT '创建时间' AFTER `create_id`;

ALTER TABLE pms_brand ADD COLUMN `update_id` BIGINT (20) DEFAULT NULL COMMENT '更新人' AFTER `create_time`;

ALTER TABLE pms_brand ADD COLUMN `update_time` datetime DEFAULT NULL COMMENT '更新时间' AFTER `update_id`;


# 品牌表新增唯一索引
ALTER TABLE `pms_brand` ADD UNIQUE (`name`)


# 产品索引
ALTER TABLE `oms_pre_item` ADD INDEX idx_product_id ( `product_id` );

# 产品购买人id索引
ALTER TABLE `oms_pre_item` ADD INDEX idx_member_id ( `member_id` );

# 产品供应商id索引
ALTER TABLE `oms_pre_item` ADD INDEX idx_comp_id ( `comp_id` );

# 产品发布人id索引
ALTER TABLE `oms_pre_item` ADD INDEX idx_push_id ( `push_id` );

# 生产地id索引
ALTER TABLE `oms_pre_item` ADD INDEX idx_origin_id ( `origin_id` );


# 产品品牌id索引
ALTER TABLE `pms_product` ADD INDEX idx_brand_id ( `brand_id` );

# 产品产品大类标识索引
ALTER TABLE `pms_product` ADD INDEX idx_product_big_category_id ( `product_big_category_id` );

# 产品产品类别标识索引
ALTER TABLE `pms_product` ADD INDEX idx_product_category_id ( `product_category_id` );

# 产品生产地id索引
ALTER TABLE `pms_product` ADD INDEX idx_place_of_production_id( `place_of_production_id` );

# 删除重复供应商
SELECT DISTINCT
	aa.comp_id
FROM
	pms_product aa;

DELETE
FROM
	ums_company_info
WHERE
	id NOT IN (

	)
## 清楚多余生产地
#供应商
DELETE FROM ums_company_info WHERE id = 298;

#删除产品数据
DELETE FROM pms_product WHERE comp_id = 298;

#供应商文件
DELETE FROM ums_company_file WHERE comp_id = 298;
#供应商商店
DELETE FROM ums_company_shop WHERE comp_id = 298;

#生产地产出物
DELETE FROM ums_member_origin_product WHERE fk_origin_id in (select a.id from ums_member_origin a where a.comp_id = 298);

#生产地
DELETE FROM ums_member_origin WHERE comp_id = 298;

#意向表
DELETE FROM oms_pre_item WHERE comp_id = 298;


# 清楚多余文件
SELECT DISTINCT
	a.comp_id
FROM
	pms_product a
WHERE
	a.delete_status = 0;

#供应商文件
DELETE
FROM
	ums_company_file
WHERE
	comp_id NOT IN (
		SELECT DISTINCT
			a.comp_id
		FROM
			pms_product a
		WHERE
			a.delete_status = 0
	);

#供应商商店
DELETE
FROM
	ums_company_shop
WHERE
	comp_id NOT IN (
		SELECT DISTINCT
			a.comp_id
		FROM
			pms_product a
		WHERE
			a.delete_status = 0
	);

#生产地产出物
DELETE
FROM
	ums_member_origin_product
WHERE
	fk_origin_id IN (
		SELECT
			a.id
		FROM
			ums_member_origin a
		WHERE
			a.comp_id NOT IN (
				SELECT DISTINCT
					a.comp_id
				FROM
					pms_product a
				WHERE
					a.delete_status = 0
			)
	);

#生产地
DELETE
FROM
	ums_member_origin
WHERE
	comp_id NOT IN (
		SELECT DISTINCT
			a.comp_id
		FROM
			pms_product a
		WHERE
			a.delete_status = 0
	);

##数据库上线更新语句 新版

# 品牌表新增字段
ALTER TABLE pms_brand ADD COLUMN `comp_id` BIGINT (20) DEFAULT NULL COMMENT '供应商id' AFTER `id`;

ALTER TABLE pms_brand ADD COLUMN `comp_name` VARCHAR (100) DEFAULT NULL COMMENT '供应商名称' AFTER `comp_id`;

# 品牌表新增字段新增供应商索引

# 产品品牌id索引
ALTER TABLE `pms_brand` ADD INDEX idx_comp_id ( `comp_id` );


# 更新语句
UPDATE pms_brand a
JOIN pms_product b on
SET a.comp_id = b.comp_id,
 a.comp_name = b.comp_name
WHERE
	a.id = b.brand_id

# 添加分享码
CREATE TABLE `ums_company_share` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `comp_id` bigint(20) NOT NULL COMMENT '供应商id',
  `comp_name` varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '供应商名称',
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `status` int(3) DEFAULT NULL COMMENT '状态0创建，1已使用，2已删除',
  `member_id` bigint(20) DEFAULT NULL COMMENT '使用人id',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '最新的修改时间',
  `share_code` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '分享码',
  `create_id` bigint(20) DEFAULT NULL COMMENT '创建分享人id',
  `create_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建用户名',
  `member` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '使用者用户名',
  PRIMARY KEY (`id`),
  KEY `idx_comp_id` (`comp_id`),
  KEY `idx_member_id` (`member_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='供应商邀请码表';

# 支付订单表
CREATE TABLE `oms_order_pay` (
  `id` varchar(22) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'id',
  `order_id` bigint(20) NOT NULL COMMENT '订单id',
  `pay_money` decimal(20,2) NOT NULL COMMENT '支付金额',
  `create_id` bigint(20) NOT NULL COMMENT '创建人',
  `create_user` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `status` int(1) NOT NULL DEFAULT '0' COMMENT '支付状态 0未支付 1已支付',
  `pay_type` int(1) NOT NULL DEFAULT '1' COMMENT '1支付宝 2 微信',
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='支付订单表';


SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for oms_order_express
-- ----------------------------
DROP TABLE IF EXISTS `oms_order_express`;
CREATE TABLE `oms_order_express` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `express_name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '快递公司名字',
  `express_code` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '快递公司编码',
  `express_phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '快递公司电话',
  `remarks` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `create_id` bigint(20) DEFAULT NULL COMMENT '创建人id',
  `create_user` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_id` bigint(20) DEFAULT NULL COMMENT '更新人id',
  `update_user` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '更新人',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `status` int(1) NOT NULL DEFAULT '1' COMMENT '0 未启用 1 已启用',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='快递公司信息';

-- ----------------------------
-- Records of oms_order_express
-- ----------------------------
INSERT INTO `oms_order_express` VALUES ('3', '韵达快递', null, null, null, null, null, null, null, null, null, '1');
INSERT INTO `oms_order_express` VALUES ('4', '天天快递', null, null, null, null, null, null, null, null, null, '1');
INSERT INTO `oms_order_express` VALUES ('5', '申通快递', null, null, null, null, null, null, null, null, null, '1');
INSERT INTO `oms_order_express` VALUES ('6', '圆通速递', null, null, null, null, null, null, null, null, null, '1');
INSERT INTO `oms_order_express` VALUES ('7', '德邦物流', null, null, null, null, null, null, null, null, null, '1');
INSERT INTO `oms_order_express` VALUES ('8', '百世汇通', null, null, null, null, null, null, null, null, null, '1');
INSERT INTO `oms_order_express` VALUES ('9', '顺丰速运', null, null, null, null, null, null, null, null, null, '1');
INSERT INTO `oms_order_express` VALUES ('10', '京东物流', null, null, null, null, null, null, null, null, null, '1');
INSERT INTO `oms_order_express` VALUES ('11', '中通速递', null, null, null, null, null, null, null, null, null, '1');
INSERT INTO `oms_order_express` VALUES ('12', '国通快递', null, null, null, null, null, null, null, null, null, '1');