【复习】mysql+python基础应用（20190815）

一、mysql：

1.1深入浅出：

• 数据分析的典型四步骤：确定问题（心智模型 ）→分解问题→评估数据→做出决策
  （CRISP-DM(cross-industry standard process for data mining) 模型：商业理解→数据理解→数据准备→建立模型→模型评估→发布模型）

• 贝叶斯统计，在自己被诊断为阳性的情况下，患阳的概率。
  实际就是recall和precision的问题，我想知道precision，预测的精度是90%（100个有90个是准的），那我就有90%的概率是阳性咯。但实际只知道recall，即阳性患者，能被诊断出来的概率为95%（100个阳性，模型能诊断出来95个），所以需要贝叶斯转换啊。

• 主观概率，讲的有点是皮尔逊相似度的问题，就是说两个人意见相差很大（比如对电影的评价），但实际上很有可能是因为两人的标准偏差很大，A对电影整体就是比较宽容，B对电影整体就是比较严苛。用主观概率，就能比较直观地比较。

1.2 必知必会：

• 顺序是宾 where 状groupby 主 最后orderby没错啦。

• 主：检索列/行的基本用法；处理：concat、as、算数计算和函数计算、日期计算（常用select day(...)/year(...)/date（...））、文本处理（常用 select Ltrim(...) /upper（...））

• 宾：where的基本用法：and or组合逻辑、in/not in筛选逻辑、控制检查null、剩下过滤的问题：
  a. 通配符like，完全匹配“where prod_name like 'jet'、限定匹配“where prod_name like 'jet_'、任意匹配“where prod_name like 'jet%'.
  ..............................
  b.正则表达式regexp，首先说明一点，like的话是必须完全匹配才会返回，regexp是列名中含有表达式则返回，如果要完全匹配的话，要配合定位符使用（加一个首定位符和尾定位符就可以了）。完全匹配“where prod_name regexp 'jet'、限定匹配“where prod_name like 'jet.'（正则里面变成了.）、任意匹配可以用.* 的方法，实际上正则比通配符优势的地方就在于他对任意匹配的控制到了随意的地步：
  匹配范围：or匹配：“where prod_name regexp 'jet[0-9]'或者“where prod_name regexp 'jet[1|2|3|4|5|6|7|8|9]'或者“where prod_name regexp 'jet[:digit:]'；特殊字符匹配“where prod_name regexp 'jet\.'（匹配jet.）以及其他的符号匹配（比如什么换行符 制表符）
  匹配限定：个数：*任意个数、+至少1个、？ 0或者1个、{n} 制定n个数目匹配，{n，}至少n个，{n,m}n~m个； 位置限定^ 开始（放在[]里面表示否定，比如[^1-9]表示不匹配1-9），$结尾
  ..............................
  c.全文本搜索match against：首先是要建表的时候采用myisam引擎开启fulltext才可以建立索引并搜索（或者用布尔搜索进行强行搜索）
  create table .....(....., fulltext(note_text))engine=myisam。
  采用match against进行匹配 where match(note_text) against('jet')等同于where note_text like '%jet%'。如果只是match against的话，相比正则表达式，会更智能，因为会按一定顺序返回，排名方式是按照匹配到的目标的多少。真正厉害的方法是采用布尔搜索模式where match(note_text) against('jet' in boolean mode)，可以支持查询扩展（根据关键词引申查询其他相关项）以及除了匹配，还可以直接限定非匹配，等等功能。

• 状：group by :group by +having的组合

• 最后：orderby：desc asc

• 其他细则：
  联结概念，除了where联结、join联结，还可以用union联结，在很复杂的表达力，union联结可能会更简单，因为不用考虑逻辑。
  ..............................
  便捷功能：
  视图（就是打包的select语句）：create view xxx as xxx。。。
  存储（就是def 函数，允许输入输出参数）：create procedure xxx() begin... end； 要执行的时候用call： call xxx（）；
  游标（指向一个select语句，一般是配合存储过程使用的，而且会用repeate来反复select）：declare XXXX cursor for select.....。执行游标就用open...fetch...close 。配合repeate有固定套路。
  触发器：就是delete insert update语句，在指定时候触发：create trigger XXX after XXXX for XXXXX。
  事务管理transaction：回退rollback、提交commit、保留点savepoint。

1.3 经典50题

• sum+case的用法：

  
  image.png

• 排序问题：
  a. 允许rank()over函数：
  a.1 有相同排名，则名次要空缺，不连续编号：
  select sc.sid,rank()over(order by sc.score desc)rank01 from sc where sc.cid='01')
  ..............................
  a.2 有相同排名，也连续编号（dense_rank）：
  select sc.sid,dense_rank()over(order by sc.score desc)rank01 from sc where sc.cid='01')
  ..............................
  a.3 组内排名：
  select sc.sid,rank()over(partition by sc.cid order by sc.score desc)rank01 from sc where sc.cid='01')
  ..............................
  ..............................
  b. 不允许rank()over函数：
  b.0 无相同排名：
  select sid,score,(@a:=@a+1) rank01 from (select * from sc where cid=01 order by score desc)scc,(select @a:=0) a;
  上面这种写法，实际上只适用于无相同排名
  b.1 有相同排名，则名次要空缺，不连续编号：（一旦有相同排名，就用联结的方式更简单）
  思路：先按无相同排名排名，再groupby取排名最小值，再联立
  select sid,sc.score,rank01 from sc,(select score, min(rank01) rank01 from (select score,(@a:=@a+1) rank01 from (select * from sc where cid=01 order by score desc)scc,(select @a:=0) a)c group by score)d where sc.cid=01 and sc.score=d.score order by rank01 asc;

  
  image.png
  

  感觉写的有点复杂，思考了一下，如果用两表联立的方法，来找比自己高的数目（或者低的数目）来排名，更简单，但要小心有两个第一名的情况，比如说如果有两个第一名，那么大于等于自身分数的会有两个值，rank自动会是2：
  select a.sid,(count(*))rank01 from sc a,sc b where a.cid=01 and b.cid=01 and a.score<=b.score group by a.sid;
  这种情况你怎么都不好调整，因为你只有第一名的排名是错误的，后面的排名都是对的。。。

  
  image.png

之后我才发现，用left join可以解决这个问题：
select a.sid,(count(*))rank01 from sc a left join sc b where a.cid=01 and b.cid=01 and a.score<=b.score group by a.sid;(额。。。)

用自联结+sum case是很通用的方法：
select a.sid,(sum(case when a.score <b.score then 1 else 0 end)+1)rank01 from sc a,sc b where a.cid=01 and b.cid=01 group by a.sid;

image.png

..............................
b.2 有相同排名，也连续编号(dense_rank)：
思路：先distinct分数排名，再联结
..............................
b.3 组内排名：
思路1：先分组，再排序，再union一起；
思路2：自联结+left join/sum case+groupby

总结下排序问题：在允许rank()over的情况下就尽情使用，不允许的话：1、@a:=@a+1方法比较粗暴，但是写起来有点复杂；2、如果没有两个第一名，用联结+count()的方法是最简单的方法；3、如果有两个第一名，用联结+sum case是通用的方法，也是最不会错的方法。

1.4 leecode

• ifnull函数

• 聚集函数不能用in匹配

delete from person where id not in (select min(id) id from person group by email);

是错误的，因为min（id）是聚集函数，外面还得再包一层select * from

• 善用case+when else end的组合（基本上难题都是靠这个解决）

• 排序，能用limit（offset）就用limit，不然就是自连接（比sum case简单），不行再用sum case

二、python

2.1 python菜鸟+应用

看多少遍都不为过，要经常复习
tips:多行注释用ctrl+/，或者打三引号

2.1.1 7大类型（6+1）

number,string,tuple是不可变类型
list,set,dictionary是可变类。

• number
  两点：
  a.分为int,float,bool,complex,并且可以互相转换
  b.number必然会涉及运算，传统的运算是在math模块里面，但是现在直接用 numpy就可以涵盖全了。

• 字符串
  四点：
  a.转义字符的使用\n（顺便说一句，用\n可以实现字符串换行，但是格式上并不简洁，可以用三引号的方式，在里面直接换行）
  b.常用运算符：+ 、*、[]切片
  c.字符串格式化s%，如果是数值格式化,常用%d和%f

  
  image.png
  

  d.其他常用函数：upper、capitalize、lstrip（mysql是ltrim ，一个是trim，一个是strip）、全局函数len和del（基本上所有类型都可以用的）...

• list
  两点
  a.list和str的区别在于list是可变的，所以除了str具备的基本特征（+、*预算符等），还有一些自建函数和全局函数进行数据更新，常用：list.count(obj)、list.sort( reverse=False)以及增删查改等等。
  b.因为list可变同时可索引，因此应用是最广泛的，经常当堆栈使用（append和pop），还有自己具有强大而又渐变的列表推导式功能：

  
  image.png

• tuple
  和列表的区别在于，不允许更改，所以是没有更改数据的自建函数的，可以用全局函数进行运算。

• set集合
  两点：
  a.set有两种生成方式，一种是直接生成，一种是利用str或者tuple转化，参见以下三种区别：

  
  image.png
  

  b.集合的增删查改

• 字典dict
  两点：
  a.没有顺序之说，只有key和value的链接
  b.增删查改的几个函数都比较重要

• 其他
  NoneType：a=None (mysql是null)
  bytes：a=b'sdfsdf' (就是采用ASCII编码的str！)

增删查改看这个表

image.png

2.1.2 运算符

7种运算符类型：

image.png

需要注意的是，两个数值的时候 &、|是位运算，如果是逻辑变量，&、|可以当逻辑运算符，而且实际应用其实更广泛，尤其是在dataframe的时候，很多时候都只能用这种逻辑运算，不能用and和or。

2.1.3 控制与循环

• 循环语句
  主要是三点：
  a.if else循环；
  b.while else循环；
  c.for else循环（用得很少，基本上是用来判定for循环的内容里面有没有想要的内容，没有的话就break跳出，不执行else，有的话就顺带执行下else语句，表明有想要的内容）
• 迭代器和生成器
  通常都是用range来进行迭代了，iter迭代器和yield生成器暂时都用不着，有简单的方法干嘛用复杂的呢。

2.1.4 函数

2.1.4.1 基础问题

• 可变对象和不可变对象的参数传递问题

  
  image.png
  

  总结起来就是，不可变对象number、string、tuple传入函数，只是把值传进去，无论函数内部怎么操作，元数据都不会受到影响。

• 几种传入参数
  分别是必须参数/关键字参数、默认参数、不定长参数（一个 * 以tuple存储，两个 * 以dict储存）

• 匿名函数
  summ=lambda a,b:a+b (就是不要搞忘就行了)

2.1.4.2 内置常用函数

（这个很重要，其实介绍完几个类型之后，就应该介绍这个的，这几个基本上都是适用于list的，因为list迭代和索引起来都最方便）

• 取整求余
  求余：mod(x,2) →→mysql一样
  四舍五入:round(x) →→mysql一样
  向下取整:int(x) →→mysql是：floor(x)
  向上取整:ceil(x) →→mysql一样

• enumerate
  配合list使用，成为迭代器（相当于range，或者iter），但他会返回两个值，一个是list的索引，一个是值：

  
  image.png

• sorted
  跟list.sort类似

• reversed
  跟list.reverse类似

• zip
  配合list使用，很好用。比起zip()的用法，更好用的是zip（*）的用法，可以方便地处理多维矩阵：

  
  image.png

• map（很方便！）
  配合list使用！

  
  image.png

• join和split方法
  https://blog.csdn.net/qq_38786209/article/details/78304974
  虽然str用的比较多，但是list,tuple,dict都能用
  

  image.png

2.1.5 模块

就三点：

• 模块的导入方式
• 最常见的sys模块，含有模块搜索路径
• dir()查看模块所有定义（目前基本上用不到），name的用法（用于判定是引用的模块，还是自身函数）：
  image.png

2.1.6 输入输出

• 格式美化问题，str.format()是新的格式方式，%是旧的格式方式（额。。）

• 读写文件问题
  open→read/write→close

  
  image.png
  

  常用的几种模式：
  r→只读
  w→只写，原有内容会被删除
  r+→读写
  w+→读写，原有内容会被删除

2.1.7 其他

• 错误和异常：try +except+else+raise语句；清理行为，一种是在最后使用finally语句保证完成清理，一种是有些模块有标准清理行为，用with使用（比如open模块有close的清理行为，避免打开文件后忘记关闭）
• 面向对象
  三点：
  a.面向对象的精髓在于可以继承，子类继承父类，甚至可以更改父类；
  b.类化和实例化的区别，x = MyClass(2)就是实例化，（把需要的参数都输入进去了，不就是实例么，参数也可以是空），x = MyClass就是类化。class定义里面肯定有一个init，是对参数进行传递：
  image.png
  上面，def init(self,n,a,w)就是将具体参数如的参数n赋值给name，a赋值给age，w赋值给__weight，具体利用这些参数怎么用，则需要进一步定义，比如下面的def speak(self)。（思路总算是清晰一点了，要先用init将所有参数赋值，再针对各种参数进行函数定义）
• 常用模块：
  math模块，datetime模块，smtplib模块，sys模块...

2.2 pandas

2.2.1 基本操作

• 文件读取
  df1=pd.read_csv('/desktop/xxx.csv')

• 两种数据结构 dataframe和series
  dataframe和series的主要区别在于一个是多维，一个是一维（当然dataframe也可以是一维，比如series自带的to_frame函数转化成dataframe）；次要区别在于series有一些自己的函数，比如series.map(....)，map就只能对一维使用，多维的话就只能用apply吧。
  a.读取的文件会被自动转化为dataframe
  b.用df=pd.DataFrame()或者df=pd.Series()创建结构的话，要注意index和column的制定（这个稍微查看下用法就知道了，避免自己忘了）
  c.一般将字典转化为DataFrame比较多，因为不用制定columns；另外一种就是将多维数组array或者多维列表list转化为DataFrame，但此时需要额外制定列名；

• 基本属性查看
  df.columns/index/dtypes/shape/size/head/tail/describe
  series.value_counts()

ps，dtype显示的类型一般有int/float/bool/时间类型/object类型以及其他扩展类型，所以如果是数值类型他会明确显示（int/float/bool，因为pandas是基于numpy开发的诶），其他的一般会显示为object

• 索引（索引就是index，一般都是指针对行的哈）
  https://www.cnblogs.com/jiaxin359/p/8995133.html
  a.常规索引，弄清楚df[0]和df[0:5]的区别，df[0]相当于df.0，df[0:5]相当于df.iloc[0:5]
  b.loc索引,以行的名字进行索引，不存在切片操作，允许范围操作，比如df.loc[10:15]，会返回index为10，11，12，13，14的数据
  c.iloc索引，以行数进行索引，允许切片操作。
  ps，经常会用df.set_index进行（多重）索引设置，不过建议用pivot_table来设置。。。

• 常规数据清洗（增删查改）操作
  先说大宗旨：axis=1为列操作，最后以行形式展示,axis=0为对行操作，最后以列形式展示，在pandas中一般会默认为axis=0操作最后展示位列（符合csv表的数据处理习惯）
  a.增：
  两种主要思路
  直接新增df['new column']=;多表联结的方式
  b.删：
  常规删除：del df或者df.drop
  删除重复行：df.drop_duplicates()或者df.drop([df.duplicated],axis=0)
  这里的duplicate都是对行数据而言的。（如果要删除列，用drop可以完成，但如果是删除重复列，考虑先转置，再删除，再还原）
  删除缺失值：df.dropna(),或者先用df.isnull检测再删除
  c.查
  索引方法
  d.改
  常规更改：用索引的方法更改列或者行
  填充：用df.fillna（）填充缺失值（有很多填充方法）

• 函数计算
  下面讲的是针对groupby后的函数计算，但实际上很多计算是可以单独使用的哈。
  a.groupby的基本使用和基本计算：
  df.groupby(by='A').sum()
  b.groupby后的复杂函数使用（各种复杂函数也是可以单独使用的）：
  https://blog.csdn.net/zwhooo/article/details/79696558
  df.groupby().agg()，agg里面接的是内置函数
  df.groupby().tansform(),series的单列计算，允许匿名函数
  df.groupby().apply()，多列计算，允许匿名函数
  df.groupby().map()，series单个元素计算（本质就跟内置map一样的），允许匿名函数
  df.groupby().applymap()，多列所有元素计算，允许匿名函数

• 表联结
  https://www.cnblogs.com/bawu/p/7701810.html
  pd.concat()和pd.merge()

• 文本处理
  这个其实是针对series的，用到的很少，series本身的字符串方法和python内置方法也差不多，比如series.str.split和' '.split()是一个用法。
  比较常用的就是join、split、lstrip、rstrip之类的
  https://blog.csdn.net/qq_28219759/article/details/52919233

2.2.2 实际应用中常用的函数

• 利用pivot_table进行透视
  透视其实就是表的重建，利用pivot_table特别方便可以构造复合index和columns（以后要构建复合index和columnes可以优先考虑透视方法）

  
  image.png

• stack和unstack
  看似和pivot_table一样，其实还是很不一样，pivot_table无论怎么透视，都是dataframe，相当于改变分组方式；stack是将dataframe完全展开为series。
  另外还有一个series.to_frame（）的方法，把series转化为dataframe:

  
  image.png
  

  所以如果要进行表的透视，pivot_table是最方便的，但用stack+to_frame的方法也能够取得类似的效果，就是不够方便；
  如果只是想dataframe变为series的话，stack就方便多了；

• isnull函数查看有多少空值
  df.isnull().sum()

• isin函数
  妈呀，竟然还有这个函数，相当于sql的 where xxx in xxx
  df.['columns'].isin(xxx)

2.3 numpy（数据分析用的很少，可能在数据挖掘和机器学习才会多）

• 创建
  a.用list转化：arr=np.array(list)
  b.自行创建：arr=np.zeros()。。。x=np.linspace()等等就看你想创建什么形式的。（np.linspace（）在画图的时候用的很多，用于生成x轴数据）
  ps：常用随机数创建

  
  image.png

• 常用属性和基本方法
  a.属性查看：size/shape/len
  b.shape改变方法：铺平flatten/ravel，reshape，T
  ps，看到数组的铺平想起list的铺平，list是没有内置函数提供直接的铺平方式的，不过可以用循环语句依次铺平，或者就把list转化为array再铺平

• 基本数值运算
  umm...就那些吧
  sum/mean/std/max/min
  以及数组间运算，不过现在都还没遇到

• 索引
  跟dataframe一样的

2.4 可视化

• 最基本属性
  a.导入模块指令：import matplotlib.pyplot as plt
  b.在线现实图像指令：%matplotlib inline
  c.全局性参数plt.rcParams里面，通常要进行设置的有：

  
  image.png

• 图表类型

  
  image.png

• 画布属性控制
  分图：plt.subplot()配合axes坐标轴参数画图
  标题：plt.title()
  坐标轴范围：plt.xlim() 和plt.ylim()
  坐标轴刻度：plt.xticks()和plt.yticks()
  注释：plt.text() 和plt.annotate()（这个是指向性注释）

• 图像内置属性控制（有的有，有的没有）
  color:c、width宽度、alpha透明度、s面积

• pandas api
  两种方法画图
  a. df.plot.scatter()/bar()/hist()用属性的方法画图
  b. df.plot(kind='box')用通用的方法画图

• seaborn api
  import seaborn as sns导入模块
  a.图像类型：

  
  image.png
  

  b.利用seaborn做简单回归分析

  
  image.png
  image.png

三、常用排序算法

• 二分查找
  list已经完成正序排序，从中间找起+递归函数
• 线性查找
  挨个找，简单粗暴
• 插入排序
  挨个把list中的数据排好，新的list数据，从后往前对比，每对比一个，原来的数据往后挪一个。
• 快速排序
  选定基准值→小于基准值的放在左边的list，大于基准值的放在右边的list→反复递归
• 选择排序
  最简单粗暴的一种，把最小的放在第一位，把次小的放在第二位。。。
• 冒泡排序
  冒泡的本质是，小的数不断往上冒，最大的数会沉在最下面。。。(所以其实我会理解为下沉排序)
  1号2号比较，把更大值放到2号，2号再与3号比较。。。第一轮沉完→反复递归完毕
• 归并排序
  先比较2个，再比较2个，再组合起来比较这4个→再和另外4个组合起来比较8个（另外4个也要先比较2个2个）。。。umm。。。
• 堆排序（二叉树排序）
  原理是：先排一个无序堆→再从倒数第二层子节点开始调整（把大的往上调），如果调整了一个子节点，要对下面的非底层子节点进行递归判定和调整→第一轮排完之后父节点就是最大值，将其放到list最后一位，把底层子节点的最后一位拿到父节点来，再进行判定
  所以理论上有三个步骤：
  a.创建原始堆的函数，这个不用管嘛，就是list本身的索引位置；
  b.从倒数第二层开始调整，确定第一个父节点；
  c.可以反复递归的从上而下调整的函数heapify（父节点要调整的话，还要检查调整后的子节点下面是否还需要调整）。
  b步骤，实际也可以用heapify函数，只用依次从底层把小堆用heapify调整好，再对上一层节点用heapify，直到到达顶层为止。
  所以关键就在于heapify函数。。
• 计数排序
  牺牲空间换时间，额外建立两个数组，一个数组生成一组固定数据（已经排序号），一个数组用来记录原始list在固定数据组中出现的次数（所以肯定会有些是0），最后再释放出来
• 希尔排序
  插入排序的改进版本，但是不稳定，有可能会比排序算法花费时间更多。
  第一次以lenth/2为间隔，对所有数据进行分组调序（一共lenth/2组）→第二次以lenth/4为间隔，对所有数据分组判断，当需要调序时，需要以lenth/4为间隔往前传递直到不再调序位置（因为前方序列已经是对的，所以到某一处不用再调序，也就不用往前推了）→第三次以lenth/8为间隔。。。→最后以1位间隔依次分组判断，以及往前传递。最后排序完成。 需要注意，每次如果有两个数进行了调序，那么要继续往前回推看前面的数需不需要调序（就跟堆排序的父节点发生改变，要检查子节点一样）
• 拓扑排序
  暂时不管了吧，图排序方法

四、两个案例分析

• 构建NaN值的方法
  float('NaN')/float('nan')，所以这好像是唯一构建Nan值的方法

  
  image.png

五、python和mysql的时间函数及字符串函数比较

https://www.jianshu.com/p/52fb1f812286
https://www.jianshu.com/p/91a1ecd57b0d

5.1 时间函数

• 返回当前日期
  mysql：select curdate(),current_date();

  
  image.png
  

  python:datetime库，或者time库

  
  image.png

• 返回当前时间
  mysql：select curtime,current_time()

  
  image.png
  

  python:同上

• 时间增加
  mysql:函数date_add（date,interval int keyword）或者函数adddate（date,interval int keyword）

  
  image.png
  image.png
  

  pthon：函数timedelta

  
  image.png
• 时间减少
  mysql:函数date_sub（date,interval int keyword）或者函数adddate（date,interval int keyword）,int为负就行
  python：函数timedelta

• 时间日期的标准格式化
  mysql：select date_formate(date,'%Y-%m-%d %H-%i-%s')

  
  image.png
  

  python:datetime和time模块都有函数strftime

  
  image.png

• 返回日期函数的年、月、日等
  mysql：select year(now()), month(now()), day(now()), hour(now()), minute(now()), quarter(now());

  
  image.png
  

  python:

  
  image.png

5.2 字符串函数

• 返回字符串长度
  mysql:select lenth()
  python:len()

• 连接字符串
  mysql:select concat('a','b')
  

  python: image.png

• 替换字符
  mysql:INSERT(str,x,y,instr)：将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果;REPLACE(str,str1,str2)：在字符串str中用str2替换掉str1

  
  image.png
  image.png
  python: image.png

• 颠倒字符串
  mysql：select reverse('SQL');
  

  python： image.png