MySQL基础知识2之初步学习

本系列文章用于记录自己学习MySQL的过程，参考书籍为《MySQL基础教程》，日，西泽梦路著作，卢克贵 译，中国工信出版集团，人民邮电出版社，ISBN：9787115527585，文章附有代码和数据，建议学习过程自己逐个敲代码，熟练使用MySQL语句，在笔者看来，这本书学习起来较为容易，也不愧于基础教程。后续会继续学习SQL或者MySQL的进阶教程，有时间会继续更新。


文章目录

  • MySQL基础知识2之初步学习
  • 前言
  • 一、MySQL监视器
  • 1.修改MySQL管理员root密码
  • 1.1修改root用户的密码
  • 1.2修改MAMP的设置
  • 1.3修改phpMyAdmin的设置
  • 二、创建数据库
  • 1.检测处理重复值
  • 2.检测与处理缺失值
  • 3.检测与处理异常值
  • 三、数据类型和数据输入
  • 1.离差标准化数据
  • 2.标准差标准化数据
  • 3.标小数定标标准化数据
  • 四、修改表
  • 1.哑变量处理类别型数据
  • 2.离散化连续型数据
  • 五、复制、删除表和记录
  • 1.哑变量处理类别型数据
  • 2.离散化连续型数据
  • 总结


前言

本文主要介绍一下MySQL的基础知识，如MySQL监视器，创建数据库，数据类型和数据输入，修改，复制删除表等

一、MySQL监视器

创建数据库之前需哟啊准备好三件事情，1是安装好MySQL，2是配置好MySQL的安装文件夹的路径，3是完成MySQL的中文设置。
然后就可以启动终端软件了，可以通过win键和R键显示运行对话框，输入“CMD”即可启动。
然后启动MAMP软件，令Apache和MySQL都是亮的就够了。

在命令提示符里输入，然后按照提示输入密码即可，默认为root，就可以进入MySQL了。

mysql -u root -p

1.修改MySQL管理员root密码

密码默认为root,可以修改。

1.1修改root用户的密码

启动MySQL监视器，按照原密码进入后，输入如下代码即可更改密码为1234。

set password for root@localhost=password('1234')

1.2修改MAMP的设置

在修改了root用户密码的情况，如果不修改MAMP的设置文件内容，就无法退出和启动MySQL服务器，用文本编辑器打开C:\MAMP文件夹中的MAMP.exe.config文件，将第九行附近的password内容。

<add key="StopMysqlCommand" ..............--password=1234 --port={0}

1.3修改phpMyAdmin的设置

本文介绍主要使用MAMP中的MySQL监视器来学习MySQL，如果使用phpMyAdmin来学习，也需要修改，代开C:\MAMP\bin\phpMyAdmin文件夹中的config.inc.php文件，对61行附近的password修改。

$cfg['Servers']..........['password']='1234';//MySQL password

二、创建数据库

数据重复会带来各种问题，如方差变小，需要进行处理重复值，缺失值，异常值。

1.检测处理重复值

重复值分两种，一种是记录重复，一个或多个特征的某几条记录完全相同，一种是特征重复，一个或多个特征名称不同但数据完全相同。
记录重复可以采用不同方法去重，可以通过list函数，set特性，drop_duplicates函数去重。
注意set去重会导致数据的排列发生变化，drop_duplicates方法只对DataFrame或者series有效，不会改变数据原始排列。
特征重复可以通过特征间的相似度来排除，通过corr函数计算相似度，默认为pearson相似度。除此之外还可以通过equals方法去重。
代码如下（示例）：

import pandas as pd
detail = pd.read_csv('detail.csv',index_col=0,encoding = 'gbk')
#记录重复去重
##方法一，利用list函数
##定义去重函数
def delRep(list1):
    list2=[]
    for i in list1:
        if i not in list2:
            list2.append(i)
    return list2 
## 去重
dishes=list(detail['dishes_name']) ##将dishes_name从数据框中提取出来
print('去重前菜品总数为：',len(dishes)) 
dish = delRep(dishes) ##使用自定义的去重函数去重
print('方法一去重后菜品总数为：',len(dish))

##方法二，利用set函数去重
print('去重前菜品总数为：',len(dishes)) 
dish_set = set(dishes) ##利用set的特性去重
print('方法二去重后菜品总数为：',len(dish_set))

#方法三，利用drop_duplicates函数去重
##对dishes_name去重
dishes_name = detail['dishes_name'].drop_duplicates()
print('drop_duplicates方法去重之后菜品总数为：',len(dishes_name))
# 利用drop_duplicates函数去重多列
print('去重之前订单详情表的形状为：', detail.shape)
shapeDet = detail.drop_duplicates(subset = ['order_id',
    'emp_id']).shape
print('依照订单编号，会员编号去重之后订单详情表大小为:', shapeDet)

## 特征重复去重
#利用corr函数求取kendall法的相似度矩阵，只能对数值型特征求取
## 求取销量和售价的相似度
corrDet = detail[['counts','amounts']].corr(method='kendall')
print('销量和售价的kendall相似度为：\n',corrDet)
# 存在类别数据，自动不显示其计算的相似度
corrDet1 = detail[['dishes_name','counts',
    'amounts']].corr(method='pearson')
print('菜品名称，销量和售价的pearson相似度为：\n',corrDet1)

# 利用DataFrame.equals方法去重
##定义求取特征是否完全相同的矩阵的函数
def FeatureEquals(df):
    dfEquals=pd.DataFrame([],columns=df.columns,index=df.columns)
    for i in df.columns:
       for j in df.columns:
           dfEquals.loc[i,j]=df.loc[:,i].equals(df.loc[:,j])
    return dfEquals
## 应用上述函数
detEquals=FeatureEquals(detail)
print('detail的特征相等矩阵的前5行5列为：\n',detEquals.iloc[:5,:5])
##遍历所有数据进而去重
lenDet = detEquals.shape[0]
dupCol = []
for k in range(lenDet):
    for l in range(k+1,lenDet):
        if detEquals.iloc[k,l] & (detEquals.columns[l] not in dupCol):
            dupCol.append(detEquals.columns[l])
##进行去重操作
print('需要删除的列为：',dupCol)
detail.drop(dupCol,axis=1,inplace=True)
print('删除多余列后detail的特征数目为：',detail.shape[1])

2.检测与处理缺失值

在数据分析中，缺失值处理也很重要，其检测通过notnull或者isnall函数来确定，均返回布尔值。

代码如下（示例）：

import pandas as pd
detail = pd.read_csv('detail.csv',index_col=0,encoding = 'gbk')
#利用isnull和otnull函数计算缺失值和非缺失值
print('detail每个特征缺失的数目为：\n',detail.isnull().sum())
print('detail每个特征非缺失的数目为：\n',detail.notnull().sum())

在数据分析中，缺失值直接删除并不好，但在数据很大的情况下，也可以简单处理，用dropna函数删除。替换一般更常用做缺失值的处理方法，可以替换为均值，中位数，众数等，用fillna函数替换。再或者用插值法来替换缺失值，通过SciPy库中的不同插值方法来插值替换。

代码如下（示例）：

import pandas as pd
detail = pd.read_csv('detail.csv',index_col=0,encoding = 'gbk')
# 处理缺失值方法
#方法一，直接删除，使用dropna函数
print('去除缺失的列前detail的形状为：', detail.shape)
print('去除缺失的列后detail的形状为：',
    detail.dropna(axis = 1,how ='any').shape)

# 方法二，使用fillna函数直接替换缺失值
detail = detail.fillna(-99)
print('detail每个特征缺失的数目为：\n',detail.isnull().sum())

# 方法三，插值方法
## 线性插值
import numpy as np
from scipy.interpolate import interp1d
x=np.array([1,2,3,4,5,8,9,10]) ##创建自变量x
y1=np.array([2,8,18,32,50,128,162,200]) ##创建因变量y1
y2=np.array([3,5,7,9,11,17,19,21]) ##创建因变量y2
LinearInsValue1 = interp1d(x,y1,kind='linear') ##线性插值拟合x,y1
LinearInsValue2 = interp1d(x,y2,kind='linear') ##线性插值拟合x,y2
print('当x为6、7时，使用线性插值y1为：',LinearInsValue1([6,7]))
print('当x为6、7时，使用线性插值y2为：',LinearInsValue2([6,7]))

## 拉格朗日插值
from scipy.interpolate import lagrange
LargeInsValue1 = lagrange(x,y1) ##拉格朗日插值拟合x,y1
LargeInsValue2 = lagrange(x,y2) ##拉格朗日插值拟合x,y2
print('当x为6,7时，使用拉格朗日插值y1为：',LargeInsValue1([6,7]))
print('当x为6,7时，使用拉格朗日插值y2为：',LargeInsValue2([6,7]))

##样条插值
from scipy.interpolate import spline
##样条插值拟合x,y1
SplineInsValue1 = spline(x,y1,xnew=np.array([6,7]))
##样条插值拟合x,y2
SplineInsValue2 = spline(x,y2,xnew=np.array([6,7]))
print('当x为6,7时，使用样条插值y1为：',SplineInsValue1)
print('当x为6,7时，使用样条插值y2为：',SplineInsValue2)

3.检测与处理异常值

在数据分析中，异常值指明显偏离其余数据的数值，一般不可以直接删除，需要分析为何出现，如果是记录错误则可以删除，如果是特殊情况需要慎重考虑，异常值也称之为离群点。一般通过mysql数据库的参考文献 mysql参考书_数据法则来确定，或者通过箱线图来寻找异常值。

代码如下（示例）：

import pandas as pd
import numpy as np
detail = pd.read_csv('detail.csv',index_col=0,encoding = 'gbk')
## 定义拉依达准则(3sigma)识别异常值函数
def outRange(Ser1):
    boolInd = (Ser1.mean()-3*Ser1.std()>Ser1) | \
    (Ser1.mean()+3*Ser1.var()< Ser1)
    index = np.arange(Ser1.shape[0])[boolInd]
    outrange = Ser1.iloc[index]
    return outrange
outlier = outRange(detail['counts'])
print('使用拉依达准则判定异常值个数为:',outlier.shape[0])
print('异常值的最大值为：',outlier.max())
print('异常值的最小值为：',outlier.min())

# 代码 菜品售价箱线图识别异常值
import matplotlib.pyplot as plt
plt.figure(figsize=(10,8)) 
p = plt.boxplot(detail['counts'].values,notch=True)   ##画出箱线图
outlier1 = p['fliers'][0].get_ydata()   ##fliers为异常值的标签
plt.show()
print('销售量数据异常值个数为：',len(outlier1))
print('销售量数据异常值的最大值为：',max(outlier1))
print('销售量数据异常值的最小值为：',min(outlier1))

三、数据类型和数据输入

标准化数据在数据分析，机器学习中很常用到，标准化后分析更好，主要讲离差标准化，标准差标准化，小数定标标准化数据。

1.离差标准化数据

一种线性变换，映射到【0,1】中。
代码如下（示例）：

import pandas as pd
import numpy as np
detail = pd.read_csv('detail.csv', index_col=0,encoding = 'gbk')
## 自定义离差标准化函数
def MinMaxScale(data):
    data=(data-data.min())/(data.max()-data.min())
    return data
##对菜品订单表售价和销量做离差标准化
data1=MinMaxScale(detail['counts'])
data2=MinMaxScale(detail ['amounts'])
data3=pd.concat([data1,data2],axis=1)
print('离差标准化之前销量和售价数据为：\n',
    detail[['counts','amounts']].head())
print('离差标准化之后销量和售价数据为：\n',data3.head())

2.标准差标准化数据

也叫做零均值标准化或者mysql数据库的参考文献 mysql参考书_mysql_02分数标准化，用途广泛，处理后，数据的均值为0，标准差为1。
代码如下（示例）：

import pandas as pd
import numpy as np
detail = pd.read_csv('detail.csv', index_col=0,encoding = 'gbk')
##自定义标准差标准化函数
def StandardScaler(data):
    data=(data-data.mean())/data.std()
    return data
##对菜品订单表售价和销量做标准化
data4=StandardScaler(detail['counts'])
data5=StandardScaler(detail['amounts'])
data6=pd.concat([data4,data5],axis=1)
print('标准差标准化之前销量和售价数据为：\n',
    detail[['counts','amounts']].head())
print('标准差标准化之后销量和售价数据为：\n',data6.head())

3.标小数定标标准化数据

将数据映射到【-1,1】，移动的小数位数取决于数据绝对值的最大值，其转化公式如下。
mysql数据库的参考文献 mysql参考书_数据_03
代码如下（示例）：

import pandas as pd
import numpy as np
detail = pd.read_csv('detail.csv', index_col=0,encoding = 'gbk')
##自定义小数定标差标准化函数
def DecimalScaler(data):
    data=data/10**np.ceil(np.log10(data.abs().max()))
    return data
##对菜品订单表售价和销量做标准化
data7=DecimalScaler(detail['counts'])
data8=DecimalScaler(detail['amounts'])
data9=pd.concat([data7,data8],axis=1)
print('小数定标标准化之前的销量和售价数据：\n',
    detail[['counts','amounts']].head())
print('小数定标标准化之后的销量和售价数据：\n',data9.head())

四、修改表

1.哑变量处理类别型数据

有些数据时哑变量，不能直接用来建模，需要先处理，哑变量也即有些数据是类别型数据，无法直接建模，需要先处理，通过get_dummies即可处理。
代码如下（示例）：

import pandas as pd
import numpy as np
detail = pd.read_csv('detail.csv',encoding = 'gbk')
data=detail.loc[0:5,'dishes_name']   ##抽取部分数据做演示
print('哑变量处理前的数据为：\n',data)
print('哑变量处理后的数据为：\n',pd.get_dummies(data))

2.离散化连续型数据

有些算法，尤其是分类算法如ID3决策树和Apriori算法都要求数据是离散的，这个时候就需要离散化连续数据了。有等宽法，等频法，聚类分析法（一维）。

等宽法是将数据分成具有相同宽度的区间，区间的个数有数据本身的特点决定或者由用户指定，与制作频率分布表类似，有cut函数使用。
其缺点是对数据分布有较高要求，如数据分布不均匀，那么各类数目也非常不均匀，效果不好。

cut函数虽然不能直接实现等频离散化，但可以定义来实现。

一维聚类的方法包含两个步骤，首先使用聚类算法（如K-means算法）进行聚类；然后处理聚类得到的簇，为合并到一个簇的数据做同一种标记。
聚类分析的离散化方法需要用户指定簇的个数，用来决定产生的区间数。
代码如下（示例）：

import pandas as pd
import numpy as np
detail = pd.read_csv('detail.csv',encoding = 'gbk')
#等宽法
price = pd.cut(detail['amounts'],5)
print('离散化后5条记录售价分布为：\n' ,price.value_counts())
##自定义等频法离散化函数
def SameRateCut(data,k):
    w=data.quantile(np.arange(0,1+1.0/k,1.0/k))
    data=pd.cut(data,w)
    return data
result=SameRateCut(detail['amounts'],5).value_counts()   ##菜品售价等频法离散化
print('菜品数据等频法离散化后各个类别数目分布状况为：','\n',result)
#自定义数据k-Means聚类离散化函数
def KmeanCut(data,k):
    from sklearn.cluster import KMeans #引入KMeans
    kmodel=KMeans(n_clusters=k)   #建立模型
    kmodel.fit(data.values.reshape((len(data), 1)))    #训练模型
    c=pd.DataFrame(kmodel.cluster_centers_).sort_values(0)   #输出聚类中心并排序
    w=c.rolling(2).mean().iloc[1:]    #相邻两项求中点，作为边界点
    w=[0]+list(w[0])+[data.max()]    #把首末边界点加上
    data=pd.cut(data,w)
    return data
#菜品售价等频法离散化
result=KmeanCut(detail['amounts'],5).value_counts()
print('菜品售价聚类离散化后各个类别数目分布状况为：','\n',result)

五、复制、删除表和记录

1.哑变量处理类别型数据

有些数据时哑变量，不能直接用来建模，需要先处理，哑变量也即有些数据是类别型数据，无法直接建模，需要先处理，通过get_dummies即可处理。
代码如下（示例）：

import pandas as pd
import numpy as np
detail = pd.read_csv('detail.csv',encoding = 'gbk')
data=detail.loc[0:5,'dishes_name']   ##抽取部分数据做演示
print('哑变量处理前的数据为：\n',data)
print('哑变量处理后的数据为：\n',pd.get_dummies(data))

2.离散化连续型数据

有些算法，尤其是分类算法如ID3决策树和Apriori算法都要求数据是离散的，这个时候就需要离散化连续数据了。有等宽法，等频法，聚类分析法（一维）。

等宽法是将数据分成具有相同宽度的区间，区间的个数有数据本身的特点决定或者由用户指定，与制作频率分布表类似，有cut函数使用。
其缺点是对数据分布有较高要求，如数据分布不均匀，那么各类数目也非常不均匀，效果不好。

cut函数虽然不能直接实现等频离散化，但可以定义来实现。

一维聚类的方法包含两个步骤，首先使用聚类算法（如K-means算法）进行聚类；然后处理聚类得到的簇，为合并到一个簇的数据做同一种标记。
聚类分析的离散化方法需要用户指定簇的个数，用来决定产生的区间数。
代码如下（示例）：

import pandas as pd
import numpy as np
detail = pd.read_csv('detail.csv',encoding = 'gbk')
#等宽法
price = pd.cut(detail['amounts'],5)
print('离散化后5条记录售价分布为：\n' ,price.value_counts())
##自定义等频法离散化函数
def SameRateCut(data,k):
    w=data.quantile(np.arange(0,1+1.0/k,1.0/k))
    data=pd.cut(data,w)
    return data
result=SameRateCut(detail['amounts'],5).value_counts()   ##菜品售价等频法离散化
print('菜品数据等频法离散化后各个类别数目分布状况为：','\n',result)
#自定义数据k-Means聚类离散化函数
def KmeanCut(data,k):
    from sklearn.cluster import KMeans #引入KMeans
    kmodel=KMeans(n_clusters=k)   #建立模型
    kmodel.fit(data.values.reshape((len(data), 1)))    #训练模型
    c=pd.DataFrame(kmodel.cluster_centers_).sort_values(0)   #输出聚类中心并排序
    w=c.rolling(2).mean().iloc[1:]    #相邻两项求中点，作为边界点
    w=[0]+list(w[0])+[data.max()]    #把首末边界点加上
    data=pd.cut(data,w)
    return data
#菜品售价等频法离散化
result=KmeanCut(detail['amounts'],5).value_counts()
print('菜品售价聚类离散化后各个类别数目分布状况为：','\n',result)

总结

提示：本文章简单介绍了pandas包的数据预处理用法。