极客时间已完结课程限时免费阅读

38丨如何在Excel中使用SQL语言？

38丨如何在Excel中使用SQL语言？-极客时间

38丨如何在Excel中使用SQL语言？

2019-08-30 陈旸 来自北京
《SQL必知必会》
课程介绍

讲述：陈旸

时长08:05大小12.96M

在进阶篇中，我们对设计范式、索引、页结构、事务以及查询优化器的原理进行了学习，了解这些可以让我们更好地使用 SQL 来操作 RDBMS。实际上 SQL 的影响力远不止于此，在数据的世界里，SQL 更像是一门通用的语言，虽然每种工具都会有一些自己的“方言”，但是掌握 SQL 可以让我们接触其它以数据为核心的工具时，更加游刃有余。
比如 Excel。
你一定使用过 Excel，事实上，Excel 的某些部分同样支持我们使用 SQL 语言，那么具体该如何操作呢？
今天的课程主要包括以下几方面的内容：
如何在 Excel 中获取外部数据源？
数据透视表和数据透视图是 Excel 的两个重要功能，如何通过 SQL 查询在 Excel 中完成数据透视表和透视图？
如何让 Excel 与 MySQL 进行数据交互？

如何在 Excel 中获取外部数据源？

使用 SQL 查询数据，首先需要数据源。如果我们用 Excel 来呈现这些数据的话，就需要先从外部导入数据源。这里介绍两种直接导入的方式：
通过 OLE DB 接口获取外部数据源；
通过 Microsoft Query 导入外部数据源。
下面我们通过导入数据源 heros.xlsx 体验一下这两种方式，你可以从这里下载数据源。

通过 OLE DB 接口获取外部数据源

OLE 的英文是 Object Link and Embedding，中文意思是对象连接与嵌入，它是一种面向对象的技术。DB 代表的就是数据库。OLE DB 的作用就是通向不同的数据源的程序接口，方便获取外部数据，这里不仅包括 ODBC，也包括其他非 SQL 数据类型的通路，你可以把 OLE DB 的作用理解成通过统一的接口来访问不同的数据源。
如果你想要在 Excel 中通过 OLE DB 接口导入数据，需要执行下面的步骤：
第一步，选择指定的文件。方法是通过“数据” → “现有连接”按钮选择连接。这里选择“浏览更多”，然后选择指定的 xls 文件。
第二步，选择指定的表格，勾选数据首行包含列标题，目的是将第一行的列名也加载进来。
第三步，通过“属性” → “定义”中的命令文本来使用 SQL 查询，选择我们想要的数据，也可以将整张表直接导入到指定的位置。
如果我们显示方式为“表”，导入全部的数据到指定的 1（代表 A1 单元格），那么在 Excel 中就可以导入整个数据表，如下图所示：

通过 Microsoft Query 获取外部数据源

第二种方式是利用 Microsoft Query 功能导入外部数据源，具体步骤如下：
第一步，选择指定的文件。方法是通过“数据” → “获取外部数据”按钮选择数据库，这里我选择了“Excel Files”，然后选择我们想要导入的 xls 文件。
第二步。选择可用的表和列，在左侧面板中勾选我们想要导入的数据表及相应的列，点击 （>） 按钮导入到右侧的面板中，然后点击下一步。
最后我们可以选择“将数据返回 Microsoft Excel”还是“在 Microsoft Query 中查看数据或编辑查询”。这里我们选择第一个选项。
当我们选择“将数据返回到 Microsoft Excel”后，接下来的操作和使用 OLE DB 接口方式导入数据一样，可以对显示方式以及属性进行调整：
这里，我们同样选择显示方式为“表”，导入全部的数据到指定的 1(代表 A1 单元格），同样会看到如下的结果：

使用数据透视表和数据透视图做分析

通过上面的操作你也能看出来，从外部导入数据并不难，关键在于通过 SQL 控制想要的结果集，这里我们需要使用到 Excel 的数据透视表以及数据透视图的功能。
我简单介绍下数据透视表和数据透视图：
数据透视表可以快速汇总大量数据，帮助我们统计和分析数据，比如求和，计数，查看数据中的对比情况和趋势等。数据透视图则可以对数据透视表中的汇总数据进行可视化，方便我们直观地查看数据的对比与趋势等。
假设我想对主要角色（role_main）的英雄数据进行统计，分析他们平均的最大生命值（hp_max），平均的最大法力值 (mp_max)，平均的最大攻击值 (attack_max)，那么对应的 SQL 查询为：
SELECT role_main, avg(hp_max) AS `平均最大生命`, avg(mp_max) AS `平均最大法力`, avg(attack_max) AS `平均最大攻击力`, count(*) AS num FROM heros GROUP BY role_main

使用 SQL+ 数据透视表

现在我们使用 SQL 查询，通过 OLE DB 的方式来完成数据透视表。我们在第三步的时候选择“属性”，并且在命令文本中输入相应的 SQL 语句，注意这里的数据表是[heros$]，对应的命令文本为：
SELECT role_main, avg(hp_max) AS `平均最大生命`, avg(mp_max) AS `平均最大法力`, avg(attack_max) AS `平均最大攻击力`, count(*) AS num FROM [heros$] GROUP BY role_main
然后我们在右侧面板中选择“数据透视表字段”，以便对数据透视表中的字段进行管理，比如我们勾选 num，role_main，平均最大生命，平均最大法力，平均最大攻击力。
最后会在 Excel 中呈现如下的数据透视表：
操作视频如下：
00:00 / 00:00
    1.0x
    • 2.0x
    • 1.5x
    • 1.25x
    • 1.0x
    • 0.75x
    • 0.5x
    网页全屏
    全屏
    00:00

    使用 SQL+ 数据透视图

    数据透视图可以呈现可视化的形式，方便我们直观地了解数据的特征。这里我们使用 SQL 查询，通过 Microsoft Query 的方式来完成数据透视图。我们在第三步的时候选择在 Microsoft Query 中查看数据或编辑查询，来看下 Microsoft Query 的界面：
    然后我们点击“SQL”按钮，可以对 SQL 语句进行编辑，筛选我们想要的结果集，可以得到：
    然后选择“将数据返回 Microsoft Excel”，在返回时选择“数据透视图”，然后在右侧选择数据透视图的字段，就可以得到下面这张图：
    你可以看到使用起来还是很方便。
    具体操作视频如下：
    00:00 / 00:00
      1.0x
      • 2.0x
      • 1.5x
      • 1.25x
      • 1.0x
      • 0.75x
      • 0.5x
      网页全屏
      全屏
      00:00

      让 Excel 与 MySQL 进行数据交互

      刚才我们讲解的是如何从 Excel 中导入外部的 xls 文件数据，并在 Excel 实现数据透视表和数据透视图的呈现。实际上，Excel 也可以与 MySQL 进行数据交互，这里我们需要使用到 MySQL for Excel 插件：
      下载 mysql-for-excel 并安装，地址：https://dev.mysql.com/downloads/windows/excel/
      下载 mysql-connector-odbc 并安装，地址：https://dev.mysql.com/downloads/connector/odbc/
      这次我们的任务是给数据表增加一个 last_name 字段，并且使用 Excel 的自动填充功能来填充好英雄的姓氏。
      第一步，连接 MySQL。打开一个新的 Excel 文件的时候，会在“数据”面板中看到 MySQL for Excel 的插件，点击后可以打开 MySQL 的连接界面，如下：
      第二步，导入 heros 数据表。输入密码后，我们在右侧选择想要的数据表 heros，然后选择 Import MySQL Data 导入数据表的导入，结果如下：
      第三步，创建 last_name 字段，使用 Excel 的自动填充功能来进行姓氏的填写（Excel 自带的“自动填充”可以帮我们智能填充一些数据），完成之后如下图所示：
      第四步，将修改好的 Excel 表导入到 MySQL 中，创建一个新表 heros_xls。选中整个数据表（包括数据行及列名），然后在右侧选择“Export Excel Data to New Table”。这时在 MySQL 中你就能看到相应的数据表 heros_xls 了，我们在 MySQL 中使用 SQL 进行查询：
      mysql > SELECT * FROM heros_xls
      运行结果（69 条记录）：
      需要说明的是，有时候自动填充功能并不完全准确，我们还需要对某些数据行的 last_name 进行修改，比如“夏侯惇”的姓氏应该改成“夏侯”，“百里守约”改成“百里”等。

      总结

      我们今天讲解了如何在 Excel 中使用 SQL 进行查询，在这个过程中你应该对”SQL 定义了查询的标准“更有体会。SQL 使得各种工具可以遵守 SQL 语言的标准（当然也有各自的方言）。
      如果你已经是个 SQL 高手，你会发现原来 SQL 和 Excel 还可以如此“亲密”。Excel 作为使用人数非常多的办公软件，提供了 SQL 查询会让我们操作起来非常方便。如果你还没有使用过 Excel 的这些功能，那么就赶快来用一下吧。
      SQL 作为一门结构化查询语言，具有很好的通用性，你还在其他工具中使用过 SQL 语言吗？如果有的话可以分享一下你的体会。
      最后留一道动手题吧。你可以创建一个新的 xls 文件，导入 heros.xlsx 数据表，用数据透视图的方式对英雄主要定位为刺客、法师、射手的英雄数值进行可视化，数据查询方式请使用 SQL 查询，统计的英雄数值为平均生命成长 hp_growth，平均法力成长 mp_growth，平均攻击力成长 attack_growth。
      欢迎你在评论区写下你的体会与思考，也欢迎把这篇文章分享给你的朋友或者同事，一起来交流。
      分享给需要的人，Ta购买本课程，你将得20元
      生成海报并分享

      赞 10

      提建议

      © 版权归极客邦科技所有，未经许可不得传播售卖。 页面已增加防盗追踪，如有侵权极客邦将依法追究其法律责任。
      上一篇
      37丨SQL注入：你的SQL是如何被注入的？
      下一篇
      39丨WebSQL：如何在H5中存储一个本地数据库？
      unpreview
       写留言

      精选留言(12)

      • 莫弹弹
        2019-08-31
        老师我发现安装 mysql-for-excel-1.3.8.msi 时会报错 The Microsoft Visual Studio Tools for Office Runtime must be nstalled prior to running this istlation. 找了一下微软的说明 ttps://docs.microsoft.com/en-us/visualstudio/vsto/how-to-install-the-visual-studio-tools-for-office-runtime-redistributable?view=vs-2019 需要下载 Visual Studio 2010 Tools for Office Runtime 才能运行 下载链接在这里 https://www.microsoft.com/zh-CN/download/confirmation.aspx?id=56961
        展开

        作者回复: 感谢分享

        共 2 条评论
        14
      • JustDoDT
        2019-09-05
        Mac 用户说我太难了😭

        作者回复: take it easy

        共 3 条评论
        8
      • 莫弹弹
        2019-08-31
        这个功能也是比较好的报表工具了，产品经理经常让我提取一些数据出来，例如指定时间段的数据，指定用户的业务记录数量，如果有Excel插件和MySQL配合的话，可以实现我从MySQL导出Excel报表的功能，非常方便

        作者回复: 是的，非常简单方便

        7
      • Monday
        2019-09-06
        这个功能犹如AWT

        作者回复: 有些类似，提供了图形化界面

        5
      • Geek_aab935
        2019-08-30
        老师好，请教一个问题，我要建一张80万条的数据表，数据来自3张表的汇总，我用create table 表名 as 方式建表太慢，有什么好的方式快速建表？

        作者回复: 创建好table之后，可以写个存储过程，插入数据，都插入好之后 再commit

        3
      • 许童童
        2019-08-30
        日常编程的话确实用不到，不过工作中，如果想临时生成一下可视化的图表，用这种方式那确实是很方便的，感谢老师分享。

        作者回复: 是的 简单实用

        3
      • jonnypppp
        2019-08-30
        平时基本不用，不过涨知识了

        作者回复: 谢谢

        3
      • 博弈
        2020-03-26
        这个功能比较好，可以直接使用Excel操作SQL
        2
      • 😳
        2020-02-16
        日常工作没使用过，不过现在知道还能这样玩了
        2
      • 往事随风，顺其自然
        2019-09-01
        为什么我在用Microsoft query导入表中数据报错，数据源中没有包含可见的表格，这个怎么解决？
        共 2 条评论
        2
      • Geek_664122
        2023-02-11 来自江苏
        为啥我无法下载hero.exel
      • 宇
        2021-08-26
        使用 Excel 的自动填充功能来进行姓氏的填写，是具体怎么做的