一、安装
刚开始学就遇到一个很大的挑战,由于本人英语很差,而MySQL的界面和操作都是全英文的。连安装也是全英文,本来还想把MySQL折腾安装到D盘的,但是对着这些英文有点懵逼了,好不容易经过群友的指点找到位置不过还要选择安装组件。由于担心漏选需要的组件,最后还是放弃了自定义安装的想法,老老实实地默认安装。这样就没有问题了,一路点击next即可。
二、了解数据库
数据库是用来储存和管理数据的仓库,可以对数据进行增、删、改、查,作为一个数据分析师的学习只需要掌握查即可(其余的需要有一定的了解)。
表是数据库最重要的组成部分,是数据结构化的信息。列储存表中的组织信息,称之为字段;行储存表中的明细记录,称之为记录。一般情况下,表是需要主键的,用来作为表的唯一标示,但不具备业务意义。主键(英文为Primary key)的值必须唯一,不能重复;每一条记录必须有一个主键,不能为空,也不能修改;一条记录的主键删除后不能被其它记录所重用。
MySQL常用数据类型:varchar文本型,int整数型,float浮点数即小数,date日期型精确到日,time时间型精确到秒。
三、数据库的操作
1.连接数据库
打开软件MySQL workbench,然后点击Local instance MySQL输入密码即可登录到图形化界面。
2.创建新的数据库和表
找到一个像圆柱形的图标,它显示为Create a new schema in the connected server,单击,然后命名为data,编码类型选择为utf-8,然后点apply即可。
然后右键点击data数据库下的Tables,在弹出的菜单中选择Create Table。然后输入表名company,编码同样选择uft-8,可以在Comments中加入对表的解释。接着添加字段名称以及对应的字符类型,设置完后点击Apply即可。
找到一个带箭头的图标,显示为Import records from an external file,点击即可插入数据。注意这里数据的编码和表的编码必须一致,不然会出现乱码。插入后会把原来数据的表头也当成一条记录,在这里需要我们手动删除。
到这里数据库data和表company都创建好了,用同样的方法可以创建表dataAnalyst。
四、查找语句(select)
查询语言的一般样式:SELECT 列名1,列名2,…… FROM 表名 [WHERE 条件]
1.查询某个或多个列只需要按着上面格式写即可,例如查找company表中的公司全称及简称:SELECT companyFullName,companyShortName FROM data.company;
2.查询所有内容可以使用 * 代替列名,例如查找company表中所有内容:SELECT * FROM data.company;
3.对查询结果限制行数在查询语句后面加上limit语句,例如上面的例子需要从第5行开始显示2行数据:SELECT * FROM data.company limit 4,2;
4.查询结果的排序,即在语句后面加上order by 列名1 [asc/desc],列名2 [asc/desc],其中asc为升序排列,desc为降序排列。例如对companyId进行降序排列:SELECT * FROM data.company order by companyId desc;
5.条件查询,单条件查询语法为:where 条件。多条件查询需要用逻辑关系词and(并关系)、or(或关系)来连接查询条件。例如我要在dataanalyst表中查询地点在深圳并且教育要求是本科的信息:SELECT * FROM data.dataanalyst where city="深圳" and education="本科";
同字段的多个条件用in表示,例如上例中还要查询北京的:SELECT * FROM data.dataanalyst where city in ("深圳","北京" )and education="本科";
否定查询可以用不等号“!=”、“<>”表示或者用not,例如上例中要把本科的排除:SELECT * FROM data.dataanalyst where city in ("深圳","北京" )and education<>"本科";
模糊查询用like,在条件中加入通配符“_"(表示一个字符)或"%"(表示任意个字符),例如在company表中要查询公司名称含有科技且科技前面只有两个字的公司信息:SELECT * FROM data.company where companyFullName like "__科技%";
6.分组功能group by,语法为group by 列名 [having 条件],其中条件是用来限制分组后显示内容的条件,with rollup在最后加入汇总行。此功能一般会和聚合函数(统计函数)一起使用,例如计数count()、平均值avg()、最大值max()、最小值min()、求和sum()。例如在dataanalyst表中对教育分组并计数且只显示大于100的:
SELECT education,count(education) FROM data.dataanalyst group by education having count(education)>100;(博士不足100条不显示)
7.SQL函数,这里主要了解一些文本类型函数,用于数据的清洗拆分,结合excel中函数进行理解。
left()、right()与excel的left、right函数一样
locate()与excel中find函数一样
length()与excel中len函数一样
substr()与excel中mid函数一样
还有条件判断函数IF()以及case when then
例如要对dataanalyst表里的薪资列进行拆分并使用别名:SELECT salary,left(salary,locate('k',salary)-1) as salaryMin,
substr(salary,locate('-',salary)+1,length(salary)-locate('-',salary)-1) as salaryMax FROM data.dataanalyst;
8.子查询是允许把一个查询嵌套在另一个查询当中,类似于函数的嵌套。
例如还要在上列中算出平均薪资,如果直接在上面加上查询列的话会不能使用别名导致查询语句看起来很繁琐,所以可以使用子查询:
select salary,salaryMin,salaryMax,(salaryMin+salaryMax)/2 from
(SELECT salary,left(salary,locate('k',salary)-1) as salaryMin,
substr(salary,locate('-',salary)+1,length(salary)-locate('-',salary)-1) as salaryMax FROM data.dataanalyst) as t;
注意这里一定要给子查询的表加上别名,不然就会报错。
9.表的连接(join的用法)语法:表A inner/left/right/full join 表B on A.key=B.key。这里分四种情况:
inner:只返回两张表能匹配上的行,一般可以省略。
left:以左边表即表A为主表,返回其全部行,而表B则返回匹配行。
right:与left相反,是以右边表即表B为主表,返回其全部行,而表A则返回匹配行。
full:只要至少有一个匹配则返回两个表的所有行
例如需要在两张练习表中找到在深圳招聘学历为本科的公司信息及职位信息。
select * from data.company as c inner join (select * from data.dataanalyst as d where d.city="深圳" and d.education="本科") as t on c.companyId=t.companyId;
10.命令行加载数据,语法是:load data local infile '数据文件路径' into table 表名 fields terminated by '字段分隔符';
其中load data infile是加载数据文件关键字,local表示本地路径,fields terminated by是指定分隔符。
注意:数据文件路径用的是斜杠/,并且其中不能包含中文字符路径。
例如加载练习的两个表:
11.日期时间函数
now():返回系统当前的日期时间,例如select now();
date():返回日期,可用于改变时间格式为年月日。
date_add:在向指定日期加上一段时间,例如在paidTime加上1天:select date_add(paidTime,interval 1 day) from data.order;
timestampdiff():计算距今为止多少天。
datediff(): 返回两个指定日期的时间差。
date_format():用于改变日期格式。
下图这个例子包含了上述几种日期时间函数的用法。
五、练习
1.统计不同月份的下单人数
分析:首先应过滤出成功购买的数据即是已支付的,然后考虑统计不同月份是按月份分组需要用到group by关键字,统计人数是计数需要用count函数,这里要注意一个问题因为有的人有多次购买行为,所以统计人数时要去重。
语句:
selectdate_format(paidTime,"%y-%m") as M,count(distinct userId)
from data.order
where isPaid = "已支付"
group by M
2.统计用户三月份的回购率和复购率
分析:回购率=本月和下月都购买的人数÷本月购买人数
复购率=购买多次的人数÷总购买人数
(1)回购率要统计本月和下月都购买的人数,在一张表里是没法做到的,需要把表用join关联起来,然后再进行统计
语句:
selectt1.M,count(t1.userId),count(t2.userId),
count(t2.userId)/count(t1.userId) as 回购率
from (
select userId,date_format(paidTime,"%Y-%m-02") as M fromdata.order
where isPaid = "已支付"
group by userId,M) as t1
left join (
select userId,date_format(paidTime,"%Y-%m-02") as M fromdata.order
where isPaid = "已支付"
group by userId,M) as t2
on t1.userId=t2.userId and t1.M =date_add(t2.M,interval -1 month)
group by t1.M
(2)复购率应该分别统计出总购买人数,至少购买两次的人数,然后再去求比值,其实这里可以按月份分组把每个月的复购率都求出来而不用单独把三月份数据过滤出来求复购率
语句:
selectM,count(t.userId),count(if(tc>1,1,null)),
count(if(tc>1,1,null))/count(t.userId)as 复购率
from (
SELECT date_format(paidTime,"%y-%m") as M,userId,count(userId)as tc FROM data.order
where isPaid = "已支付"
group by M,userId) as t
group by M;
3.统计男女用户的消费频次是否有差异
分析:消费频次是指在一段时间内每人的消费次数,计算式子为
消费频次= 总消费次数÷ 总消费人数
按题目要求需要统计男女用户则需要对sex进行分组,性别和消费在不同的表里则需要用join关联表,order表中未支付以及user表中性别的空值都需要过滤,然后在进行统计
语句:
select sex,count(t1.userId),count(distinctt1.userId),
count(t1.userId)/count(distinct t1.userId)as 消费频次
from (
select * from data.order where isPaid="已支付") ast1
inner join (
select * from data.user where sex!="") as t2
on t1.userId=t2.id
group by sex
4.统计多次消费的用户,第一次和最后一次消费间隔是多少?
分析:统计的是多次消费的用户,需要先把这些数据过滤出来,按用户统计则需要对用户进行分组,第一次消费时间和最后一次时间可以分别用min、max函数计算出来,然后求间隔用日期时间函数
语句:
select userId,max(paidTime),min(paidTime),
datediff(max(paidTime),min(paidTime)) as 消费间隔(天)
from data.order
where isPaid="已支付"
group by userId
5.统计不同年龄段,用户的消费金额是否有差异?
分析:首先应把已支付的人过滤出来,然后在此基础加上按年龄段的分组,使用case……when……then语句。
语句:
selectt2.ageGroup,sum(t1.price),count(distinct t1.userId),
sum(t1.price)/count(distinct t1.userId) as 平均消费额
from (
select * from data.order where isPaid="已支付") ast1
inner join (
select id,timestampdiff(year,birth,now()) as age,
case when timestampdiff(year,birth,now()) <=20 then "0-20"
when timestampdiff(year,birth,now()) <=30 then "20-30"
when timestampdiff(year,birth,now()) <=40 then "30-40"
when timestampdiff(year,birth,now()) <=50 then "40-50"
when timestampdiff(year,birth,now()) <=60 then "50-60"
else"60+" end as ageGroup
from data.user
where birth > "0000-00-00") as t2
on t1.userId=t2.id
group by ageGroup
6.统计消费的二八法则,消费的top20%用户,贡献了多少额度
分析:由于limit后面不可以接select语句,所以这题要分两步解决。
第一步,先用select语句查询出支付人数的20%和资金总额
第二步,再把查询的人数写到limit后面
语句:
select round(count(distinctuserId)*0.2),sum(price)
from data.order where isPaid="已支付"
select sum(ts) as 消费额 from (
select userId,sum(price) as ts from data.order
where isPaid="已支付"
group by userId
order by sum(price) desc
limit 17130)
as t