SQL与pandas操作对比-摘要

这里对于sql和pandas关于二维表的操作，做一个简单的摘抄汇总，

持续更新中.......

select 选择

SQL	pandas
select--展示某列
select  Player,Socre from players	players[ ['Player','score'] ]
distinct--某列唯一值
select distinct Score from players	players['Score'].unique()
count--统计数量
select count(*) from palyers	len(palyers)
分类统计--统计离散值数量
select Team,count(Team) from players group by Team	players.Team.value_counts()
连续值描述性统计
select sun(Score) from players select max(Score) from players select min(Score) from players select avg(Score) from players	players.Score.sum() players.Score.max() players.Score.min() players.Score.mean()
where--选择性查找
select * from players where Position ='SG'	players[players['Position']=='SG']
select * from palyers where Position='SG' and Score>26	players[(palyers['Position']=='SG')]&(players['Score']>26)]
in/not in
select * from players where Ranking in(1,3,5) select * from players where Ranking not in(1,3,5)	players[palyers.Ranking.isin((1,3,5))] players[~palyers.Ranking.isin((1,3,5))]
order by--排序
select * from player order by Score desc select * from player order by Score asc	palyers.sort_values('Score',ascending=False) palyers.sort_values('Score',ascending=True)
limit / offset
sekect * from players order by Score desc limit 3 select * from player order by Score desc limit 3 offset 1	players.sort_values('Score',ascending=False).head(3) players.nlargest(2+3,'Score').head(3)
group by--分组
select Position,avg(Score) s from players group by Position order by s desc	players.groupby(['Position'])['Score'].agg('mean').reset_index()sort_values('Score',ascending=False)
inner join
select * from players as t1 inner join ass as t2 on t1.Ranking=t2.Ranking	pd.merge(players,ass,on='Ranking')
left join
select * from players t1 left join ass on players.Ranking=ass.Ranking	pd.merge(player,ass,on='Ranking',how='left')
right join
select * from players t1 right join ass on players.Ranking=ass.Ranking	pd.merge(player,ass,on='Ranking',how='right')
union
select * from players where Position='PF' union  select * from ass where assist<3	pa.concat([players[players.Position=='PF'],ass[ass.assist<3]]).reset_index()

 

创建、修改、插入、删除表

方法	SQL	pandas
新建	create table 表名（列名 类型 ...）	pd.DataFrame(columns=[列名，列名])
修改	update 表名 set 列名=值，列名=值	df.loc[df.列名==值，列名]=值
插入	insert into 表名 （列名，列名）values(值，值)	temp = pd.DataFrame([值]，columns=df,columns)
删除	delect from 表名 where 表达式	df=df.loc[df.列！=值]

 

待更新中.....

 

参考链接：

https://mp.weixin.qq.com/s/lgFA5-8fOZC3zhOVB8ns8w

分享链接：https://blog.csdn.net/weixin_39791387/article/details/81391621