create table tab1

最新推荐文章于 2025-09-02 10:54:38 发布

原创最新推荐文章于 2025-09-02 10:54:38 发布 · 411 阅读

CC 4.0 BY-SA版权

11 篇文章

订阅专栏

 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;