SQL自学三部曲_Part1：云端数据库配置&Excel/Tableau连接数据库

毛媛媛

已于 2023-11-16 14:48:00 修改

阅读量767

点赞数 1

分类专栏：数据分析四大工具之二：SQL 文章标签：数据库 sql 数据分析程序人生学习职场和发展

于 2023-09-07 15:25:05 首次发布

本文链接：https://blog.csdn.net/myy12456789/article/details/132542822

版权

数据分析四大工具之二：SQL 专栏收录该内容

10 篇文章 2 订阅

订阅专栏

文章目录

一、为什么要配置云端数据库？
二、从百度网盘下载Mysql文件夹
三、云端数据库配置文档
四、小试牛刀
五、Excel连接数据库
六、Tableau连接数据库
系列文章

一、为什么要配置云端数据库？

1. 简单方便

数据库直接搭建在云端，不会在安装过程中出现任何因系统和环境导致的Bug
整个安装过程只需要点点点，没有任何技术要求，大家都能实现

2. 性能更佳

每个人电脑的配置不一，本地数据库极其消耗电脑的运算资源
放在云端，不占用电脑性能，数据库和电脑都运行更快

3. 随时使用

云端数据库可以支持在任何设备上连接运行，随时随地练习SQL

4. 真实还原

大多数的公司的数据库都搭建在云端，云端数据库能还原真实业务场景的数据环境
连接云端数据库是每一位数据分析师的必修课
最后会用Excel和Tableau连接数据库

5. 拓展性强

后续会用Tableau和Python直接连接云端数据库进行分析

安装过程中，一定要看到哪，就暂停操作到哪，有不清楚的直接看文档，这样最为高效

二、从百度网盘下载Mysql文件夹

链接：https://pan.baidu.com/s/1-jOwaXWArtv8h21B0-uWGg
提取码：wsra
云端数据库配置所需文件在【MySQL云端数据库配置】文件夹下
需要导入云端数据库进行练习的数据在【数据库导入数据】文件夹下
后续Excel和Tableau连接数据库所需的驱动在【Excel&Tableau数据库连接驱动】文件夹下

三、云端数据库配置文档

1. 购买云数据库

（1）在搜索框中输入阿里云
（2）进入阿里云官网，点击产品，数据库
（3）下拉页面在关系数据库中，选择云数据库RDS MySQL 版：https://www.aliyun.com/product/rds/mysql?spm=5176.161322.J_9503524160.1.38d8592cjhnYkO
（4）下滑至产品规格，选择MySQL8.0基础版，立即购买

（阿里云有的时候会搞活动，可以优惠价购买，只要是RDS MySQL 基础版 1核 1G版都可以）
（5）如果没有登录，此时会跳出请求登录的界面，点击登录，并扫码登录
（6）进入购买页面，地域选择离自己最近的，SQL版本必选8.0，存储类型选推荐的ESSD，最低存储20g完全足够，点击立即购买
（7）在订单支付界面，确认购买时，如果以前从来没有使用过阿里云产品会弹出您还未通过实名认证的弹窗，此时需要进行实名认证
- 点击确定
- 转至右上角的登录名，点击立即认证
- 选择个人实名认证
- 选择个人支付宝授权认证，同意身份认证扫码
- 填写地址并确认，认证成功
（8）再次购买云数据库，点击立即购买
创建订单，勾选协议，点击确认购买
进行支付
（9）支付成功，跳转至阿里云主界面

如果在官网上找不到这款产品，可能是因为下架了，可以去淘宝上根据自己情况自行购买；或者先申请免费试用3个月
在这里插入图片描述

2. 配置云数据库账号、数据库、白名单

（1）点击右上角的控制台
（2）进入控制台
（3）触碰左上角的列表图标，点击云数据库RDS版
（4）点击实例列表，选择自己购买实例所属的地域，出现已经购买成功的实例，并点击实例名
- 如果是在刚购买完成后进行操作会出现示例创建中，此时需要5分钟左右等待实例创建完毕
- 刷新界面，出现运行中字样，即创建实例成功，此时再点击实例名进行下一步操作
（5）进入实例的基本信息页面，对实例进行设置
（6）点击账号管理，创建一个高权限账号，数据库账号为sa，账号类型为高权限账号，设置一个密码，例如：Ex123456
- 稍等片刻后，刷新界面，账号处于已激活状态即成功创建账号
（7）再设置数据库，点击数据库管理，创建数据库
（8）然后分别创建ods,ddm,edw,edw_s 四个数据库
（9）点击数据库连接，设置白名单
- 进入白名单设置界面，点击修改
- 将组内白名单修改为0.0.0.0/0
- 如果需要手机验证，填入收到的验证码即可
- 稍等片刻刷新数据库连接的页面，出现内网地址
- 点击申请外网地址
- 稍等片刻后，刷新界面，出现外网地址
- 复制外网地址，可以记录在记事本或者其他地方，接下来使用datagrip连接云数据库

3. 安装datagrip连接数据库

（1）安装datagrip
- 在百度网盘下载安装文件后，点击文件夹MySQL云数据库配置中的datagrip.exe文件进行安装
- 点击next
- 建议直接选择默认路径安装，避免环境变量错误
- 勾选下列选项，记得一定要勾选添加环境变量，完成安装
- 选择第二个稍后重启
- 如果出现环境变量相关错误，可以看一下电脑 >>> 属性 >>> 高级 >>> 环境变量，是否成功添加了datagrip的环境变量。如果没有，点击新建环境变量，变量名输入datagrip，值输入datagrip bin所在的目录例如C:\Program Files\JetBrains\DataGrip 2020.3.2\bin; 注意最后要添加分号
- 双击运行软件，进行设置
  - 点击Don’t Send，Do not import settings
- 选择Evaluate for free（试用30天）,可以不填写邮箱
- 点击加号创建new project，输入一个项目名，点击ok
- 进入界面后出来的弹窗可以点击close进行关闭
- 接下来进行汉化。在File >>> Settings >>> Plugins的搜索框中搜索chinese，选择chinese(simplified)插件，点击install进行下载
- 点击Restart IDE重启软件
- 等待软件重启，接下来连接mysql数据库。点击右上角的加号，数据源，找到MySQL，提示需要下载驱动。
- 关掉界面，点击文件 >>> 数据源
- 点击驱动程序，选择MySQL
- 点击加号，自定义jar
- 选择下载下的文件夹MySQL云数据库配置中的mysql-connector-java-8.0.22.jar
  - 并点击原来的MySQL connector，再点击减号进行删除，点击确定，就加载好驱动了
（2）在安装完datagrip后，连接云数据库，点击加号，数据源，找到mysql
（3）填写数据库参数
- 填写名称（个人练习数据库），主机（粘贴云数据库外网地址），用户（云数据库账号），密码（云数据库账号对应密码）
（4）点击测试连接，如果测试成功，点击确定连接云数据库
（5）成功连接数据库后，勾选要用的构架ddm/ods/edw/edw_s
（6）上传文件中的拌客数据（三张表cpc、shop、orders）至数据库中
- 右键ddm，点击从文件导入数据
- 选择【数据库导入数据】文件夹下的cpc.csv
- 进入设置界面
- 由于出现乱码，修改编码为合适的编码，这里选择GBK，再勾选第一行是头
- 双击cpc的日期字段从text修改为date格式
- 点击导入
- ddm下出现cpc表格和字段，即为成功
- 确保cpc表导入的格式与以下格式相同
- 在ddm中导入shop.csv
  - 编码选择GBK，勾选第一行是头，日期字段格式改为date、GMV改为double，下单人数改为int，点击导入
- 确保shop表导入的格式与以下格式相同
- 在ods中导入orders.csv
  - 右键ods，从文件导入orders.csv
  - 编码为GBK，勾选第一行是头，修改下单日期为date，下单日期时间为datetime，订单id为int（text也可以，尽量与用户id格式相同）
- 确保orders表导入的格式与以下格式相同

4. 安装sublime存储和打开sql文件

（1）安装sublime
- 点击文件夹sublime.exe文件，安装在默认路径
- 勾选Add to explorer context menu，点击next，完成安装
- 从win程序菜单拖拽快捷方式至桌面，双击软件打开
- 接下来进行汉化。在sublime界面中点击preferences>browse packages弹出文件夹目录，点击上一级目录，进入installed packages目录，将文件夹MySQL云端数据库配置中的package control.sublime-package包放在该目录下即可
- 打开sublime，点击菜单栏preferences>browse packages，跳转到文件packages路径
- 在文件夹MySQL云端数据库配置中找到文件夹Chinese-Localization-master，将Chinese-Localization-master整个文件夹复制至该路径，直接汉化成功
（2）用sublime存储sql文件
- 点击视图，选择侧边栏，点击显示打开的文件
- 写一段sql代码：select * from shop
- sublime新建的文件默认为text格式，可以右下角修改文件为sql，同时发现写下的代码颜色发生改变
- 或者将文件存储为sql格式也能达到同样的效果，记得务必命名文件时加上后缀.sql该格式才能生效
（3）修改默认打开方式为sublime
- 右键打开方式，选择其他应用
- 如果在打开的页面找不到sublime，点击在这台电脑上查找其他应用
- 在C盘Program Files路径下找到Sublime Text文件夹中的sublime_text.exe，点击打开
（4）用sublime打开文件夹数据库导入数据中的sql文件
（5）复制sublime中的代码至datagrip中运行，验证导入的数据是否正确
- 复制sql代码至datagrip中运行
- 输出的数据和下面的图片一样，恭喜安装成功！

四、小试牛刀

1. 场景介绍&数据介绍——ddm.shop、ddm.cpc、ods.orders

在Datagrip内直接查看数据字典
双击表格查看详细数据

2. 场景一：刚来公司的第一天，想看一下门店营业表有哪些字段

运行结果
参考答案
describe ddm.shop
在sqlzoo中使用describe查看各数据表的字段和数据格式，可以点击下载按钮后导出数据
如果Mysql导出csv格式数据乱码，是csv文件本身的文本编码问题导致的
- 1.鼠标右键点击选中的 csv 文件，在弹出的菜单中选择“编辑”，则系统会用文本方式（记事本）打开该 csv 文件
- 2.打开 csv 文件后，进行“另存为”操作，在弹出的界面底部位置有“编码”，修改编码方式即可：从UTF-8改成 ANSI，然后保存
- 3.再用 Excel 打开后，显示汉字正常

3. 场景二：运营需要查看旗下所有品牌和门店在2019年12月1日至7日在美团上的GMV和下单人数

正确结果
参考答案

select
品牌名称
,门店名称
,日期
,GMV
,下单人数
from ddm.shop
where 日期 between '2019-12-01' and'2019-12-07'
and 平台 = 'meituan'

在这里插入图片描述

4. 场景三：查询所有门店每天的GMV和CPC消耗

在这里插入图片描述

正确结果
- 部分运行结果
参考答案

select
门店名称
,shop.日期
,GMV
,cpc总费用
from ddm.shop shop
join ddm.cpc cpc
on shop.门店ID = cpc.门店ID
and shop.日期 = cpc.日期

在这里插入图片描述

5. 场景四：业务提新需求了，要看旗下所有品牌各门店12月1日至7日期间在所有平台上的总GMV和总下单人数

正确结果
参考答案

select
品牌名称
,门店名称
,sum(GMV)
,sum(下单人数)
from ddm.shop
where 日期 between '2019-12-01' and'2019-12-07'
group by 品牌名称,门店名称

在这里插入图片描述

6. 业务看了眼刚才的数据，表示只想看累计GMV在3万以上，并且下单人数在200人以上的门店

正确结果
参考答案

select
品牌名称
,门店名称
,sum(GMV)
,sum(下单人数)
from ddm.shop
where 日期 between '2019-12-01' and'2019-12-07'
group by 品牌名称,门店名称
having sum(GMV) > 30000
and sum(下单人数) > 200

在这里插入图片描述

7. 场景五：查询2020年饿了么平台上每个门店GMV最高那天的日期和GMV【窗口函数】

正确结果
参考答案

select
门店名称
,日期
,GMV
from
(
    select
    门店名称
    ,日期
    ,row_number()over(partition by 门店名称 order by GMV desc) r
    ,GMV
    from ddm.shop
    where substring(日期,1,4) = '2020'
    and 平台 = 'eleme'

) a
where a.r = 1