create table tab1

use zhucm_sql;

--创建表并将本地数据导入表

drop table if exists bala_zhu_weekly_tmp;

create table 'bala_zhu_weekly_tmp'(

'Platform' VARCHAR(20) COMMENT '平台',

'brand' VARCHAR(20) COMMENT '品牌',

`Order_time` varchar(50) NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '下单时间',

`Purchase_quantity` smallint(5) COMMENT '购买数量'

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

load data local infile './data/bala_zhu_weekly_tmp.csv'

into table bala_zhu_weekly_tmp

fields terminated by ',' optionally enclosed by '"' escaped by '"'

lines terminated by '\n';

--数据清洗

use zhucm_sql;

set sql_safe_updates=0;

-- SQL_SAFE_UPDATES = 1（默认）时，不带where和limit条件的update和delete操作语句是无法执行的，即使是有where和limit条件但不带key column的update和delete也不能执行。

UPDATE zhucm_sql.bala_zhu_weekly_tmp

SET

User_Id=MID(User_Id,3,CHAR_LENGTH(User_Id)-3)

; --冒号别忘了

UPDATE zhucm_sql.bala_zhu_weekly_tmp SET Pay_time = '1970-01-01 00:00:00' where Pay_time ='' or Pay_time is null;

--时间类型的字段为空时，设置为 '1970-01-01 00:00:00'

-- 对子表预处理，去掉多余的字符，如时间字段2017-05-01 00:23:12.0的后缀.0等;金钱相关字段NULL的设置为0.00;时间相关NULL设置为 '1970-01-01 00:00:00'

use zhucm_sql;

set sql_safe_updates=0;

UPDATE zhucm_sql.bala_zi_weekly_tmp

MID(ColumnName, Start [, Length]) 字符串从1开始，而非0，Length是可选项，如果没有提供， MID() 函数将返回余下的字符串。

>> SELECT  MID( 'NowaMagic' , 5, 5);

>> Magic

SET Child_order_Num=MID(Child_order_Num,3,LENGTH(Child_order_Num)- 3),

User_Id=MID(User_Id,3,CHAR_LENGTH(User_Id)-3),

Order_Num=MID(Order_Num,3,LENGTH(Order_Num)-3),

Order_time = MID(Order_time,1,LENGTH(Order_time)- 2),

Pay_time = MID(Pay_time,1,LENGTH(Pay_time)- 2),

Delivery_time = MID(Delivery_time,1,LENGTH(Delivery_time)- 2)

;

UPDATE zhucm_sql.bala_zi_weekly_tmp SET Refund_pay = 0.00 where Refund_pay ='' or Refund_pay is null;