MySQL

浏览: 267

一、安装

       刚开始学就遇到一个很大的挑战,由于本人英语很差,而MySQL的界面和操作都是全英文的。连安装也是全英文,本来还想把MySQL折腾安装到D盘的,但是对着这些英文有点懵逼了,好不容易经过群友的指点找到位置不过还要选择安装组件。由于担心漏选需要的组件,最后还是放弃了自定义安装的想法,老老实实地默认安装。这样就没有问题了,一路点击next即可。

二、了解数据库

        数据库是用来储存和管理数据的仓库,可以对数据进行增、删、改、查,作为一个数据分析师的学习只需要掌握查即可(其余的需要有一定的了解)。

        表是数据库最重要的组成部分,是数据结构化的信息。列储存表中的组织信息,称之为字段;行储存表中的明细记录,称之为记录。一般情况下,表是需要主键的,用来作为表的唯一标示,但不具备业务意义。主键(英文为Primary key)的值必须唯一,不能重复;每一条记录必须有一个主键,不能为空,也不能修改;一条记录的主键删除后不能被其它记录所重用。

        MySQL常用数据类型:varchar文本型,int整数型,float浮点数即小数,date日期型精确到日,time时间型精确到秒。

三、数据库的操作

1.连接数据库

打开软件MySQL workbench,然后点击Local instance MySQL输入密码即可登录到图形化界面。

登陆.png

2.创建新的数据库和表

创建数据库.png

找到一个像圆柱形的图标,它显示为Create a new schema in the connected server,单击,然后命名为data,编码类型选择为utf-8,然后点apply即可。

库的命名.png

然后右键点击data数据库下的Tables,在弹出的菜单中选择Create Table。然后输入表名company,编码同样选择uft-8,可以在Comments中加入对表的解释。接着添加字段名称以及对应的字符类型,设置完后点击Apply即可。

创建表.png

找到一个带箭头的图标,显示为Import records from an external file,点击即可插入数据。注意这里数据的编码和表的编码必须一致,不然会出现乱码。插入后会把原来数据的表头也当成一条记录,在这里需要我们手动删除。

导入数据.png

到这里数据库data和表company都创建好了,用同样的方法可以创建表dataAnalyst。

四、查找语句(select)

查询语言的一般样式:SELECT 列名1,列名2,…… FROM 表名 [WHERE 条件]

1.查询某个或多个列只需要按着上面格式写即可,例如查找company表中的公司全称及简称:SELECT companyFullName,companyShortName FROM data.company;

列查询.png

2.查询所有内容可以使用 * 代替列名,例如查找company表中所有内容:SELECT * FROM data.company;

全查询.png

3.对查询结果限制行数在查询语句后面加上limit语句,例如上面的例子需要从第5行开始显示2行数据:SELECT * FROM data.company limit 4,2;

限制行数.png

4.查询结果的排序,即在语句后面加上order by 列名1 [asc/desc],列名2 [asc/desc],其中asc为升序排列,desc为降序排列。例如对companyId进行降序排列:SELECT * FROM data.company order by companyId desc;

排序.png

5.条件查询,单条件查询语法为:where 条件。多条件查询需要用逻辑关系词and(并关系)、or(或关系)来连接查询条件。例如我要在dataanalyst表中查询地点在深圳并且教育要求是本科的信息:SELECT * FROM data.dataanalyst where city="深圳" and education="本科";

并条件查询.png

同字段的多个条件用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 "__科技%";

模糊查询.png

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条不显示)

分组.png

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;

字符串函数.png

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;

注意这里一定要给子查询的表加上别名,不然就会报错。

子查询.png

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;

表的连接.png

10.命令行加载数据,语法是:load data local infile '数据文件路径' into table 表名 fields terminated by '字段分隔符';

其中load data infile是加载数据文件关键字,local表示本地路径,fields terminated by是指定分隔符。

注意:数据文件路径用的是斜杠/,并且其中不能包含中文字符路径。

例如加载练习的两个表:

导入user.png

导入order.png

11.日期时间函数

now():返回系统当前的日期时间,例如select now();

date():返回日期,可用于改变时间格式为年月日。

date_add:在向指定日期加上一段时间,例如在paidTime加上1天:select date_add(paidTime,interval 1 day) from data.order;

timestampdiff():计算距今为止多少天。

datediff(): 返回两个指定日期的时间差。

date_format():用于改变日期格式。

下图这个例子包含了上述几种日期时间函数的用法。

时间函数.png

五、练习

1.统计不同月份的下单人数

分析:首先应过滤出成功购买的数据即是已支付的,然后考虑统计不同月份是按月份分组需要用到group by关键字,统计人数是计数需要用count函数,这里要注意一个问题因为有的人有多次购买行为,所以统计人数时要去重。

语句:

selectdate_format(paidTime,"%y-%m") as M,count(distinct userId)

from data.order

where isPaid = "已支付"

group by M

 下单人数.png

 

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

 回购率.png

 

(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;

 复购率.png

 

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

 消费频次.png

 

4.统计多次消费的用户,第一次和最后一次消费间隔是多少?

分析:统计的是多次消费的用户,需要先把这些数据过滤出来,按用户统计则需要对用户进行分组,第一次消费时间和最后一次时间可以分别用min、max函数计算出来,然后求间隔用日期时间函数

语句:

select userId,max(paidTime),min(paidTime),

datediff(max(paidTime),min(paidTime)) as 消费间隔(天)

from data.order

where isPaid="已支付"

group by userId

 消费间隔.png

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

 年龄段消费.png

 

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

 二八法则1.png

二八法则2.png

推荐 2
本文由 SJFXDDF 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

0 个评论

要回复文章请先登录注册