- 作者:柯基大佬
- 日期:2021-10-20
- 类型:Java
- 说明:本文源于 柯基 手写摘要,如需转载请带上链接或注明出处!
大数据库导表
数据库引擎:
InnoDB
支持事务、外键等高级数据库功能,适用于业务MyISAM
不支持事务,强调高性能
步骤
1.打开.sql文件,搜索“ENGINE=InnoDB”,把所有数据库引擎修改为“MyISAM”
2.命令行启动MySQL
3.show databases;
4.use xxx(数据库名);
5.source xxx.sql;
拆表
一、品牌表 car_brand
查询表结构与自增长ID:
show create table xxx
清空表
重置自增长ID:
truncate table xxx
新建一条数据,检查自增长id是否从1开始
新建临时字段源id
读表插入新表:将读取的表字段下的数据,插入另一长表的字段下
INSERT INTO tableName(field...) SELECT field... FROM tableName
将表创建人数据填上
update tableName set field = 'xxx'
sql
show create table car_type |
二、子品牌表 car_sub_brand
数据分组插入
insert into tableName_new(field1, field2)
select field1, field2 from tableName group field1, field2修改车系表,关联品牌id, 子品牌表brand_id = 品牌表old_id
update tabName1, tabName2
set tabName1.xx = tabName2.xx
where tabName1.xx = tabName2.xx将表创建人数据填上
将所有来源设置为国产:3
模糊匹配所有带“进口”标识的数据,将来源改为进口:2
sql
insert into car_sub_brand(name, brand_id) |
三、车系表 car_series
- 根据子品牌表中的 厂商+品牌id 分组
车系表新建临时字段:old_id, brand_id, changshang
注意:
brand_id、changshang 两个字段用于关联子品牌表
old_id 源车系id,用于后续的车型表关联
读表插入新表
insert into car_series(name, old_id, brand_id, changshang)
select p_chexi, p_chexi_id, p_pinpai_id, p_changshang
from yf_series子品牌表和车系表关联(非主键)
update car_series cs, car_sub_brand csb
set cs.sub_brand_id = csb.id
where cs.brand_id = csb.old_id
and cs.changshang = csb.name填充创建人字段
update car_series set creater_name = 'bzb'
检查遗漏关联
select * from car_series where sub_brand_id is NULL or sub_brand_id = ''
四、车型表 car_type
从yc_car中拆出需要的字段及数据(name是必须的,不再需要old_id)
insert into car_type(
name,
price,
price_str,
year_type,
sale_state,
effluent_standard,
fadongji,
qigangshu,
biansuxiang,
jinqixingshi,
qudongfangshi,
pailiang,
ranyouxinghao,
youxiangrongji,
zuigaochesu,
zuidamali,
zuidagonglv,
chang_kuan_gao,
zuoweishu,
xinglixiangrongji,
cheshenjiegou,
size_type,
series_id
)
select
p_chexingmingcheng,
p_changshangzhidaojia_search,
p_changshangzhidaojia_yuan,
p_niankuan,
p_xiaoshouzhuangtai,
p_huanbaobiaozhun,
p_fadongji,
p_qigangshu_ge,
p_biansuxiang,
p_jinqixingshi,
p_qudongfangshi,
p_pailiang_l,
p_ranyoubiaohao,
p_youxiangrongji_l,
p_zuigaochesu_km_h,
p_zuidamali_ps,
p_zuidagongshuai_kw,
p_chang_kuan_gao_mm,
p_zuoweishu_ge,
p_hanglixiangrongji_l,
p_cheshenjiegou,
p_jibie,
p_chexing_id
from yf_car关联车系表
update car_type ct, car_series cs
set ct.series_id = cs.id
where ct.series_id = cs.old_id
-- 先将所有数据设置为 1:国产 |
-- 把包含“进口”内容的数据修改为 2:进口 |
-- 填充创建人字段 |
-- 检查遗漏关联 |
删除脏数据
delete from car_type where name is NULL or name = ''
下架过时车型(先下架全部车型,再上架需要的)
update car_type set enable = 0
update car_type set enable = 1 where year_type = '2015款'
...
update car_type set enable = 1 where year_type = '即将销售'平行进口