MySQL一些操作汇总

导言

接上一期，用python把数据框一股脑写入MySQL，形成下面的表metro_sh_20160901，一共15122570条刷卡数据

现在的任务是要把交通方式为地铁的筛出来并形成新表metro20160901，首先把复制形成新的yikatong表

create table yikatong select 卡号,日期,date_format(时间,'%H:%i:%s') as 时间, 站点,交通方式,费用,是否有优惠 from metro_sh_20160901;

接着从yikatong表取出地铁数据，并形成新表metro20160901,共9778560条刷卡数据

create table metro20160901 select * from metro_sh.yikatong where 交通方式="地铁"; #把地铁的挑出来建新表

下面的把玩都是基于这张表的，比如统计各个站点的刷卡人次

select 站点,count(站点) from metro20160901 group by 站点; #按站点统计刷卡数据

统计各个时间段的刷卡人次，一个67117条数据，可以推测代码中’%H:%i:00’是按分钟

select date_format(时间,'%H:%i:00') as 时间, count(*) as 刷卡频率 from metro20160901 group by 时间 order by 时间; #时段刷卡频率 

根据刷卡人次时段表可以大致确定早高峰和晚高峰时段

我们从图中可以看到工作日有两个非常明显的刷卡高峰期，一个是早上上班的早高峰，出现在07:50-08：50，早高峰刷卡频数占全天的15.8%；一个是晚上下班的晚高峰，出现在17:30 -18:50，晚高峰刷卡频数占全天的15.7%，早高峰和晚高峰的刷卡频率一共占了全天的31.5%，是名副其实的高峰，而9:00-17:00是平峰时段，刷卡频数远低于早晚高峰期的刷卡频数。接下来就是要按时间跨度把早高峰和晚高峰筛出来并新建表

create table zaogaofeng select * from metro20160901 where 时间 between '07:50:00' and '08:50:00'; #早高峰刷卡人次
create table wangaofeng select * from metro20160901 where 时间 between "17:30:00" and "18:50:00"; #晚高峰刷卡人次

从数据看到早高峰有1524168条刷卡记录，晚高峰有1520615条刷卡记录，相差3000多条，大体均衡，也验证了这个早高峰和晚高峰时段确定的比较合理。

• 每个站点的进出比

# 每个站的进出比
select a.站点, a.in_num as in_num, b.out_num as out_num, in_num/out_num as ratio from(
select 站点, count(*) as in_num from metro_shanghai.metro20160901 where 费用=0 group by 站点) a 
inner join (
select 站点, count(*) as out_num from metro_shanghai.metro20160901 where 费用!=0 group by 站点) b
on a.站点=b.站点; 

• 各站点进出联通人数

create table metro20160901_in select * from metro_shanghai.metro20160901 where 费用=0; #进站分表
create table metro20160901_out select * from metro_shanghai.metro20160901 where 费用!=0; #出站分表
select a.站点 as 进站, b.站点 as 出站, count(*) as 刷卡数 from metro20160901_in a 
inner join metro20160901_out b
on a.卡号=b.卡号
group by 进站, 出站;

• mysql中一个数据库（A)的表community复制到另一个数据库(B)中的表 community
  思路是在数据库A下新建一个叫community的表，并且这个表套用数据库B中表community的格式，然后再向新建的community插入B.community数据即可

CREATE TABLE community LIKE B.community;
INSERT community SELECT * FROM B.community;

• 修改数据字段类型
  比如之前的日期是text类型，现在要改成datetime类型

alter table 微信公众号图文数据20200225 modify column 日期 datetime;

• 某字段是否包含某个字符串

select * from table  where instr(字段名, "字符串") > 0  #包含(默认)
select * from table  where instr(字段名, "字符串") = 0  #不包含

• 删除

delete from temp_action.a_newdiskprice where computman='zbw'; #删除computman为zbw的所有数据

• 限定条数

SELECT id, phone_no_add FROM phone_sh order by id limit 10 ;  #只取10条

• 限定条数加跳过

SELECT id, phone_no_add FROM phone_sh order by id limit 10 offset 1; #跳过第一条，跳过1条数据,从第2条数据开始取,一共查询10个
#or
SELECT id, phone_no_add FROM phone_sh order by id limit 1, 10; #跳过第一条，跳过1条数据,从第2条数据开始取,一共查询10个,当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示总共要取的数量

• 已经创建表后，修改某列的默认值

alter table adm.adm_psg_pred_algorithm alter column  baby_num set default 0; #设置adm.adm_psg_pred_algorithm表的baby_num列默认值为0