1. 基本的数据类型：文本 整数 小数 布尔 日期 其他类型

变量类型 名义（性别、血型） 有序（好中差） 连续型（身高体重等）

数值前加单引号 将数值转化为文本

 单元格格式的设置

1. excel的基本操作

表格转表：表是数据库的概念

开始-套用格式 选择某一个格式

单元格操作：shift连续多行

复制粘贴-选转置  行和列分开

隔行插入 逆序排列

查找和替换： *表示任意多个字符  ？表示一个字符

选择粘贴：选择性粘贴  数据验证：东,南,西,北

粘贴运算

粘贴图片链接

1. 函数公式

Offset函数，定位新的引用区域

Offset（起点，上下位移，向右移动一列，得到新的基准点，向下取几行，向右取几行）

使用在名称定义里面，

嵌套函数

If（iserror（a1），“error”，“ok”）

4、条件格式：

一单元格的值来设置条件格式

二公式返回的格式来设置格式

   一、格式-自定义-只包含以下内容的单元格设置格式

二、使用公式确定要设置格式的单元格

美元符号￥$是锁定单元格用的，要去掉

图标集：套用条件格式

数据条和色阶：

数据条，一组数据中的最大值为最长条，色阶也是

迷你图：折线图、柱形图、盈亏图

插入—迷你图

5、

数据透视图 ：筛选框

在表内排序 ，图也会变

插入——切片器（如果要关联，那么取的底层数据要是同一个）

选项-切片器样式

切片器 和数据透视图表关联

6、基本图表功能

3.1 power query 导入多源数据方法

Power query  整合多数据源数据（数据库，txt，csv，web，hdfs）

突破行数限制

通过M函数灵活创建自定义数据处理计算规则

创建流程可以多次使用

Excel13版以上可以用

数据-获取和转换

仅创建链接

数据-新建查询   导入accdb文件

选择多项√  加载到   仅创建连接  在表格界面没有变化  

数据-显示查询   双击打开

关闭并上载（过程才会保存）

Csv是文本文件  

加载到 表

Txt文件导入-从文本导入

3.2 横向 纵向合并多源数据

字段（字段名和记录） 、表、数据库  （层层包含的关系）

横向合并：主表和副表中 字段名有对应的

先选主表-开始-合并查询（即横向合并）-再选副表-选择关键字段-选择id-连接种类-左外部

选择内部（主表有，副表也有）

上面的是左边，下面的是右表

选择展开字段

使用原始列名作为前缀  打勾 显示原来的字段名

连接演示

合并学员信息表1和2 ---

合并查询

关闭并上载

纵向合并---

追加查询

一个字段只能有一个字段类型

点击 关闭并上载

3.3菜单栏基本功能

开始----解决错误信息 ：路径下文件不存在，退回到源里面

复制路径，点击回车

填充功能：向下填充—用空值null最上面临近的值填充

向上填充 同理

透视列：透视 将数据按照某种规则进行汇总

转换-透视列-

逆透视功能：转换-逆透视列-逆透视其他列-插入

添加自定义列：添加列-自定义列-选择可用列-输入公式

视图----

3.4 M函数精讲

会改m函数 ，会写m函数

编辑栏、添加自定义列、高级编辑器

在编辑器中输入：=#shared  查看m函数列表

Eg：把身份证号变成一个新的表

开始-新建源-空查询-输入=#shared

转换-到表中

文本数据text  表数据tab

添加列-索引列-

Text.range([身份证号码],16,1)

转换-信息-奇偶

开始-替换值 和ctrl f 一样 ，替换

Let：处理的步骤

In：处理的结果

自动化运行重复性工作

写好逻辑-点击数据刷新

3.5 基本数据处理方法

识别重复行方法： 使用公式countif 计算出现的频率、使用关键字段后使用公式 if 、利用数据透视表（凡是大于一的就是重复的）

去重方法：1 数据-排序和筛选-高级  -选择不重复

 识别重复行后删除：

Q中有 开始-删除重复项

查找缺失值：ctrl+f  ctrl+g  利用排序筛选

替换缺失值：1使用0填充  2使用平均数填充  3删除含有缺失值的记录   4暂时保留缺失值行

处理异常值：

数值or 文本

处理格式错误：

设置格式

使用分列功能

使用公式补全信息后转换

数据分组：

数据标准化和权重：

Min-max标准化：新数据=（原数据-极小值）/（极大值-极小值）

直接对比一线和二线城市的房价是不合理的，要进行标准

标准分=（原始分-平均分）/标准差

加权平均：

1 横向和纵向对比，横向重要就为1，纵向为0

2 横向加总

3 每个阶段合计值/合计总值*100%

加权平均值=变量1*1的权重+…+变量n*变量n的权重

多条件数据抽取:

(了解一下就可以了)

3.6 power query数据处理案例

处理各大城市3-12月的大气质量数据和城市属性数据 结合

城市属性+城市大气质量=带城市属性的大气数据

三个表合一

将第一行做标题

选择月份-日期-提取年or 月

删除市 –用替换

开始-合并查询-左外部

不勾选2个表里面相同的字段

处理null：替换

4.0 数据透视表的高级方法

对数值的汇总观测方法：透视

1百分比显示

总计的百分比和列总计的百分比

行汇总的百分比

父集中的百分比

添加3%的销售提成的字段（数据透视表中-分析-字段项目于求和）

删除字段：同上-删除

修改字段：同上

添加计算项：同上——字段项目-计算项

2不同维度及层级的汇总显示

3添加计算字段

4删除及修改计算字段

5添加及删除计算项

4.1power pivot导数：加强版的数据透视工具

导入文本 （access文件）accdb csv文件等

企业版的excel才有pivot  家庭版没有  

数据-数据管理模型-进入pivot功能

4.2搭建多维数据透视分析模型

多维数据集、立方体  关联在一起不同类别的数据集合

表和表之间匹配的关键字段

关系图视图下选莫哥表的关键字段拖拽到另一个表的关键字段

主页-关系图视图

主页-数据透视表：生成povit的数据透视表

只有当副表的行列标签做行列的时候，才能用副表的行列标签做值

4.3 pivot创建层次结构

选择父集字段-选择创建层次结构-

-选择子集字段-拖拽到父集下面

4.4 pviot DAX表达式：数据分析表达式 pivot特有函数集

和excel的基本函数很相近或相同

Switch （表达式1，结果1…..，表达式n，结果n）

Related（合并字段）  类似于vlookup  创建了链接关系才可以用

Filter（表，筛选条件）在筛选条件下把表提取出来

Calculate（计算规则，条件1，条件2）

4.5 pivot使用kpi

三色图标

5.1  数据可视化分析方法论

对比分析——纵向对比 柱状图和折线图

进度分析-仪表盘 温度图 游标图

横向对比-饼图  条形图 复段折线图

标准值 平均值 计划值 对比

子弹图 ：平均对比分析

实际和计划值的对比：对比柱形图

同环比对比：

预警分析：kpi分析  关键指标的达成进度

透视分析：

结构分析：杜邦分析 树形结构分析

变化分析

分组分析

增维分析 ： 折线+环形图

5.2描述性统计图表

频数  频率

直方图： 输入区域  接受区域

分类间距改为0

盒须图：用来体现数据分散情况

四分位数：将数据由小到达排列分成4等份，Q1  Q2  Q3  ,其中Q3-Q1就是盒子 占整个数据的50%。出于三个分割点位置的数值就是四分位数

5.3专业自定义图表制作

迷你图表  五星图表 ：（if（大于，“*”，：“*”））

公式、公式加条件格式、

嵌套图表：仪表盘：内圈  外圈  环形图 下半圈改成白色

插入文本框

多维折线图：先做折线图 ，再做环形图（小的）

基本图表再创新：

瀑布图：堆积柱形图，底下设为无填充

分段折线图：列的数据  表格分段显示

5.4交互式图表制作

选择数据透视表图，插入 切片器，选择关键字段

右键-报表连接  选择数据透视表

Offset 引用

开发工具——插入-选项按钮-改名字-设置控件格式

5.5power map数据地图

必须要联网下使用-

插入-三维地图-

选择时间，有播放按钮，

无聚合

选择-新场景

5.6 power view 交互式仪表板创建

文件-选项-加载项-com加载项-勾选power view选项

新建一个空的选项卡-然后把原来的拖拽到新的选项卡下面

计数-不重复

6.0杜邦分析仪

6.1业务背景介绍

销售漏斗分析：潜在，接触，意向，明确，投入，谈判，成交、

销售管理分析；客户维度，产品维度，地域维度，销售维度，渠道维度，商机维度

多维度分析

6.2分析仪制作方法

 Getpivotdata函数

7.1 MySQL安装及调试

先关杀毒软件

Custom

Mysql 5.7版本

Workbench 6.3

Connector 5.3

记住账号密码

 测试 select 1+1；

7.2数据库概述

关系型数据库

非关系型数据库

数据库-表（多个字段的集合）-字段（字段名和记录组成）

Sql可以对应多种的关系型数据库

7.3数据库操作

创建数据库 creat datebase test；

查看创建好的数据库 show creat datebase test；

查看所有的数据库列表 show datebases；

使用数据库： 使用创建好的test数据库 use test；

删除数据库 drop database test；

用sys下的

Code,sql 文件 拖动到软件里面

注释 --    #

点击闪电or ctrl 加回车  执行代码

左侧要点刷新按钮

--创建数据库

Creat database test；

--查看创建好的数据库

Show creat database test；

--查看所有数据库列表

Show databases；

--使用数据库

Use test；

--删除数据库

Drop database test；

7.4  数据表操作

1 使用test数据库

use test；

2 创建员工信息表

 creat table emp（

Depid char（3），固定长为3的文本型

Depname varchar（20），最大长为20的可变长的文本型

Peoplecount int  整数型

）；

3 查看表是否创建成功  

Show tables；

4 删除数据表

Drop table emp；

7.5数据类型

注册时间 ：日期型

用户名： 文本型

充值金额 ：小数型

位：bit  0或1

字节：8个字节组成的 byte

数据类型：具体不同类型的直接长度

一个int整数型占4个byte 长度

Int

Tinyint

Smallint

Mediuint

Bigint

在指定字段数据类型时如果在数据类型后加上unsigned可以禁止负数

Int（11）显示宽度为11

小数型：

Float  4字节

Double  8字节

Decimal

日期型：

Datetime：yyyy-mm-dd hh：mm：ss显示年月日和时分秒

Date：只显示年月日

Timestamp

Year(2|4)

字符串：

Char  固定长度

Varchar  可变长度字符串

防止数据溢出

Blob或text

Tinyblob和tinytext

7.66约束条件

约束是在表上强制执行的数据检验规则，用来保证创建的表的数据完整和正确

#主键约束 primary key  唯一的标识 是非空和唯一的

可以加快数据库的查询速度，

#非空约束 not null 

写在要限定字段的后面

#唯一约束 Unique   不能重复

写在后面

#自增字段 auto increment 

写在后面

字段名 数据类型 default默认值

#默认值 default   默认情况下从1开始自增

写在后面

Use test；

Creat table emp（

Depid char（3）primary key，

Depname varchar（20）not null，

Peoplecount int unique default 0

）；

8.1填充数据

Insert into 表名（字段1，字段2……）values…..

实际应用场景中，下面应用的更多

为Monthly_indicator表导入外部txt文件:

Load date local infile’文件路径.txt’

   Into table Monthly_indicator

   Fields terminated by ‘\t’   导入的是txt文件，所以要用分割符\t

   Ignore 1 lines;      第一行一般是字段名 所以忽略

检查导入内容  select*from xxx；

检查导入数据总行数 select count（*）from xxx；

检查表结构    desc xxx；

8.2修改数据表

使用alter table语句修改数据表结构

将数据表xxx改名为xxxdep

Alter table xxx rename xxxdep；

讲数据表xxx中aaa字段数据的数据类型由varchar（20）修改为varchar（30）

Alter table xxx modify  depname varchar（30）；

将数据表xxx中aaa字段的字段名改为 bbb

Alter table xxx change aaa bbb varchar（30）；

将数据表xxx中aaa字段的字段名改回卫 bbb，并将该字段的数据类型改回为varchar（20）

Alter table xxx change aaa  bbb carchar（20）；

为数据表xxx添加新字段 ddd 新字段的数据类型为varchar（10），约束条件为非空

Alter table xxx add ddd varchar（10）not null；

Change可以改表结构和数据类型，modify只能修改数据类型

将数据表xxx中的name字段的排列顺序改为第一位

Alter table xxx modify name varchar（10） first；

将数据表xxx中name字段的排列顺序改到 bbb字段之后

Alter table xxx modify name varchar（10）after bbb；

删除name字段

Alter table xxx  drop name；

查字段类型

Desc xxx

9.1数据整合 函数

主要用于处理数字值

函数                   说明

ABS（x）           返回x的绝对值

LOG(X,Y)     返回x的二进制  

MOD(X,Y)    返回x/y的模（余数）

RAND（）  返回0到1内的随机值，可以通过提高一个参数（种子）使RAND（）随机数生成器生成一个指定的值

CEILING(X)   返回大于x的最小整数值（进一取整）

ROUND（x，y）  返回参数x的四舍五入的有y位小数的值

SQRT(x)     返回一个数的平方根

Concat（s1，s2…….，sn）   将s1sn连接成字符串

Now（）  返回当前的日期和时间  

Date（datetime）  返回datetime的日期值

Time（datetime）   返回datetime的时间值

Group_concat（col）  

使用group_contact 函数查询不同sid下对应的所有f_name信息

Select sid，group_concat （f_name） from fruits

Group by  Sid

Cast将一个值转化为指定的

Dpdate  set 为字段赋值

Delete

删除fid为b5的数据记录

Delete from fruits where fid=’b5’

9.2 sql查询

加减乘除   +  -  *  /

！=不等于   ！<不小于

大于> 大于等于>=  小于类似

聚合类函数

Avg

Sum

Max

Min

Count 计数（和excel一样）

对大气质量表进行有选择的查询

Select cityname ，avg（pm25），avg（pm10）

From monthly_indicator数据源

Where pm25>50 元组选择条件

Group by cityname,monthkey having cityname<>’北京’  分组条件

Order by avg(pm25)desc; 

安装pm2.5是降序的排序排列

Desc是降序排序

Asc是升序排序

例子：

查询大气质量表中的全部内容

Select*

From monthly_indicator

查询北京的大气质量数据

Select*

From monthly_indicator

Where city_name=’北京’

查询不同月份PM2.5的最大值

Select month_key ,max(pm 25)

From monthly_indicator

Group by month_key

Group by不好理解的时候就理解为数据透视表的值的标签

降序查询不同城市pm10的平均值

Select city_name ,avg(pm 10)

From monthly_indicator

Group by city_name

Order by avg(pm10)desc;

有排序就需要使用order by

多表查询：

将2个以上的数据表通过关键字段连接在一起，并从不同表中取不同字段进行查询的方法

关键字段：用来链接2表的内容信息能够匹配的上的字段

1相连的两表中都需要有关键字段

2关键字段中的记录信息能够匹配的上

3 最理想的链接转态是2表中的关键字段都是主键，而且2个主键的值能够一一匹配的上

Select list from 表1 xx  join 表2 on 表1.key=表2.key

1. xx代表链接的方向，可以说inner left right等关键字
2. 在连接语句前边的表是“左表”，在链接语句后的表是右表

Select 学员信息表.*，学员成绩表.*from 学员信息表 left join 学员成绩表 on 学员信息表.学号=学员成绩表.学号；

Left join以左边的表为主要的参考

内连接查询inner join

内连接：按照连接条件合并两个表，返回满足条件的行。

Select list from a

Inner join b on a.key =b.key

就是取交集

左连接 left join

取交集和左表的所有行

Select list from a

left join b on a.key =b.key

取交集和右表的所有行

Select list from a

right join b on a.key =b.key

联合查询

Union 用于合并2个或多个select语句的结果集，并消去表中任何重复行。

用union合并t1和t2表

Select t1.*from t1

Union

Select t2.*fromt2；

Union all：用于合并2个或多个select语句的结果集，保留重复行

用union 合并t1与t2表

Select t1.*from t1

Union all

Select t2.* from t2；

9.3查询操作符与子查询

Creat table fruits（

F_id char(10) not null,

S_id int not null,

F_name varchar (255) not null,

F_price decimal (8,2) not nul,

Primary key(f_id)

）;

字段说明：

F_id 水果id

S_id 品类id

F_name 水果名称

F_price 水果价格

And 用来联合多个条件进行查询，条件与条件间是“和”的意思

Select*from fruits where sid=101 and fid=a1

Or 用来联合多个条件进行查询，条件与条件间是“或”的意思

Select* from fruits where fname=’apple’ or fname=’orange’

In 判断某个字段的值是否在制定的集合中，如果在则满足查询条件，如果不在则不满足查询条件

用in查询苹果和橘子的相关记录

Select*from fruits where fname in （'apple’,’orange’）

用not in 操作查询苹果和橘子之外的水果的相关记录

Select*from fruits where fname not in （'apple’,’orange’）;

Between 判断某个字段的值是否在指定的范围内

Eg：用between…and操作符查询fprice 在10元到20元之间的水果记录

Select*from fruits where fprice between 10 and 20；

Like 用来匹配字符串是否相等，如果字段的值与指定的字符串相匹配，则满足查询条件

查询所有fid由b开头切字符长度为2位的水果记录

Select*from fruits where fid like’b_’;

B%k表示b开头，k结尾的任意长度的字符串，如果bak book break 都可以

B_k 表示b开头，k结尾的3个字符串，只有bak可以， book break都不可以

Is null 用来判断字段的值是否为空值null

查询所有fname为空的水果记录

Select*from fruits where f_name is null；

Distinct：用来消除重复记录

查询fruits表中所有不重复的s_id

Select distinct s_id from fruits;

子查询：写在（）中，把内层查询结果当做外层查询参照的数据表来用

Any 只要瞒住内层查询语句返回的结果中的任何一个，就可以咨询外层查询语句

查询fid对应的fprice在10元到20元之间的水果记录

Select*from fruits where fid=any

（select fid from fruits where fprice between 10 and 20）；

All 只有满足内层查询语句返回的所有结果，才可以咨询外层查询语句

查询所有fprice大于20元的水果记录

Select*from fruits where fprice>all

(select fprice from fruits where fprice<=20);

Exists 不返回查询的记录，而是返回一个真假值。如果为真，则返回所有为真的记录

查询是否存在fprice 大于30元的水果记录

Select*from fruits where exists

（select*from fruits where fprice>30）；

As 将表或者字段重命名

讲fruits表名重命名为f后使用

Select f.*from fruits as f ；

Limit 查询只显示limit指定数字行数结果

显示fprice金额最大的前三名水果记录

Select *from fruits

Order by fprice desc

Limit 3 ；

10.1 使用SQL语句处理及查询电商业务数据1

E-R图

不同的表之间 字段的链接关系

1在这个表里面没有重复值

*表示多 这个字段在这个表里面没有重复值

多对多的结果是字段相乘的结果 ，比如2x2 等于4 最终当我们对表格进行相加或者相乘的时候会非常不方便，所以尽量不要用多对多

倒序查询卖的金额最多的产品

查询不同尺码下的产品销售数量

查询不同颜色下的产品销售金额

查询不同尺码下的不同颜色的产品销售金额

10.2 使用SQL语句处理及查询电商业务数据2

求出购买产品金额最多的前十名顾客

求出购买产品金额最多的前十名顾客的所在城市

求出购买力最强的前十个城市

求出购买力最强的前十个城市以及他们所在的省份

先建一个过渡表

多表连接基础还不牢