如何高效系统学习 MySQL?
DevWeekly 收集整理每周优质开发者内容,包括开源项目、工具资源、技术文章等方面,每周五首发于DevWeekly,欢迎大家 Star 并收藏!
MySQL 这么简单还用得着学习?
有不少同学估计都有这种想法,“不就是增删改查一些简单操作吗?”
如果仅从使用者的角度做一些简单的查询和聚合,MySQL 的确很简单。
但是,你能从头至尾配置一套生产环境的数据库环境吗?你可以设计出具有优秀扩展性的表模型吗?怎么样才可以实现大数据量的快速查询?
我认为,任何一个领域都有相应的专家,也都有一知半解的人,对于 MySQL,虽然很多人觉得非常简单,但是要真正成为这个领域的专家同样有很高的难度。
有点扯远了,本文的目标不是面向高阶数据库专家的,本文旨在引导初学者入门、帮助对 MySQL 感兴趣的同学了解数据库全局链路。
配置 SQL 环境
现如今云服务使用越来越官方,很多同学在公司直接接触的都是 rds(Relational Database Service),开箱即用,作为使用者不需要关注底层使用的到底是 Oracle、Postgresql 还是 SQL Server,也不用关注运维和数据库安全。
但是,我认为亲自动手配置一套 SQL 环境会更加容易帮助我们对数据库的理解。
要想配置环境,我们首先需要下载MySQL服务客户端。
我们可以根据自己的系统(MACOSX、Linux、Windows)下载对应的 MySQL。
下载完之后,双击并安装该文件。需要设置一个密码,一定要记住这个密码,因为以后需要它来连接到 MySQL 实例。
![](https://static001.geekbang.org/infoq/18/18a4b119b90d6bf0954784d817e2f6d9.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
创建一个名为 my.cnf 的文件,并将以下内容放入其中,这需要给本地文件读取 SQL 数据库的权限。
打开系统偏好->MySQL,转到配置,选择 my.cnf 文件,然后点击 Apply 按钮。
![](https://static001.geekbang.org/infoq/e3/e325b61ecd7eb69e0580d906c8162086.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
通过点击 在 MySQL 设置页面,可以 通过点击 Start 或者 Stop 来启动或者停止数据库。
![](https://static001.geekbang.org/infoq/6c/6c23b994b8f091c7731fec390778c46a.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
如果数据库服务正常运行,接下来就可以下载并安装[MySQL 工作台](https://www.zhihu.com/question/MySQL :: Download MySQL Workbench),工作台可以用来编辑和查询数据,并以结构化的方式返回结果。
![](https://static001.geekbang.org/infoq/58/5856b1b746ac49f6256db9263a215466.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
现在打开 MySQL 工作台,通过它连接到 SQL 服务,你会看到类似下面的东西:
![](https://static001.geekbang.org/infoq/1e/1e9ff6d198c718f2f8e2256843378e6b.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
你可以看到,本地实例连接已经事先为你设置好了。
现在,你只需要点击该连接,并使用我们之前为 MySQL 服务器设置的密码开始工作。
![](https://static001.geekbang.org/infoq/6c/6c893bc7d395d29dbee43a6e7cdde376.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
连接之后,你就会看到是一个编辑界面,在这里你可以输入一些查询语句执行数据的查询。
![](https://static001.geekbang.org/infoq/5a/5a1ce0b04b9900fcf45017e9e0714373.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
但是,目前我们还没有建表,也没有数据,所以,首先需要准备一些数据:
![](https://static001.geekbang.org/infoq/87/87bf48e2fcb38e965f4e0ae0ff1ef521.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
手动建表太麻烦了,MySQL 官网提供了一些数据样例用于学习,我们可以访问官网,下载 Sakila 电影数据库,然后解压。
接下来,回到 MySQL 工作台,依次点击文件>运行 SQL 脚本>选择位置 sakila-db/sakila-schema.sql。
这样,就创建了表结构。
下一步,依次点击文件>运行 SQL 脚本>选择位置 sakila-db/sakila-data.sql。
这样 ,就把数据导入到 SQL 服务了。
做完这些操作,你会看到侧边栏有一些数据库了:
![](https://static001.geekbang.org/infoq/e4/e42001f27d9e92c996ef2bff172618e4.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
处理数据
现在,已经配置好环境并有了数据,接下来可以写一些查询。
你可以尝试使用Sakila样本数据库文档来详细了解 Sakila 数据库的模型,这是设计数据库表时非常重要的环节。
![](https://static001.geekbang.org/infoq/86/8694378b282bdc08f35533fc4f206369.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
现在,可以看一个基本的 SQL 语句:
在这个查询中,有 4 个核心要素:。
SELECT:要查询哪些列?这里我们查询 col1,对 col2 进行 SUM 聚合,对 col3 进行 AVG 聚合。这里还通过使用 as 关键字给 SUM(col2)起了一个别名
FROM:从哪个表中查询
WHERE:可以使用 WHERE 语句过滤数据
GROUP BY:所有不在聚合中的列都需要在 GROUP BY,含义就是以哪些字段分组聚合
ORDER BY:根据 col2sum 排序
这个简单的 SQL 样例就可以在数据库里轻松查到一些有价值的信息。
例如,我们可以用以下方法找出不同审查等级的电影在平均时间上的差异:
![](https://static001.geekbang.org/infoq/94/949df0762abd87ca8ca81b8972f1eb32.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
编辑切换为居中
添加图片注释,不超过 140 字(可选)
接下来可以做一个练习,你可以根据自己感兴趣的内容想出一些自己的问题。
例如,你可以试着找出所有在 2006 年发行的电影、或者尝试找出所有评级为 PG 且长度大于 50 分钟的电影。
你可以通过在 MySQL Workbench 上运行以下程序来完成这个任务:
SQL 连接
到现在为止,我们已经学会了如何查询单个表。
但在现实中,我们需要与多个表一起工作,需要 用到表的关联。
这时,就会用到 SQL 中一个非常重要的概念:SQL 连接。
下面这张图讲列出了 SQL 中大多数会用到的连接,使用较多的就是 LEFT JOIN 和 INNER JOIN:
![](https://static001.geekbang.org/infoq/ec/ec25d8f71be7c15caf23af0b770adce3.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
下面,就先来介绍一下 LEFT JOIN,先来看一段 SQL 代码:
当你想保留左表(A)中的所有记录并关联 B 中的匹配记录时,就可以使用 LEFT JOIN。在结果表中,B 没有被关联到的 A 表记录被置为 NULL。
在上面这段代码中,从表 A 中选择 col1 和 col2,从表 B 中选择 col3 和 col4,我们还使用 ON 语句指定关联字段。
当你想连接 A 和 B 并只保留 A 和 B 中的交集时,就可以使用 INNER JOIN。
回到 Sakila 数据库,假设我们想知道数据库中每部电影有多少分拷贝,你可以通过以下方式获得:
![](https://static001.geekbang.org/infoq/16/161f9933dda1ce3b57302daa63a1f3d4.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
你觉得这个结果怎么样?
你会发现你从这里面并不能得到太多有价值的信息,因为 ID 是一种系统设计过程中方便系统识别的标识符,但是对于用户端并不能很好理解,比如,《霸王别姬》对应的 ID 是 1001,直接返回给你 1001,看不到名称我们就很难读懂它里面的信息。
因此,我们就需要电影 ID 和电影名称的一种关联关系。
但是,在系统模型设计过程中,一个系统流程会被拆分到多个数据表中,film_id 和 film 的映射关系我们就需要去另外一张表中取到。
这时候,就需要用到前面介绍的表连接。
![](https://static001.geekbang.org/infoq/ea/ea4b1ee15da36e97e9d6833b474fe017.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
编辑切换为居中
添加图片注释,不超过 140 字(可选)
在上面 SQL 中,通过 film_id 字段将 A 表与 B 表关联到一起,并从表 B 中取出了电影名称 title,这样再输出结果就可以带出电影名称,方便理解了。
内部查询
假设现在有一个需求,我们需要基于前面的结果统计每部电影有多少副本。
如果逐步实现的话,需要先把前面的结果写到一张新表里:
然后使用一个简单的 group by 操作:
![](https://static001.geekbang.org/infoq/56/562f1fdcb41f6c90f39f23cf73e900c0.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
但是这步骤太多、太繁琐了,而且我们必须创建一个临时表,最终会占用系统的空间。
SQL 为我们提供了内部查询的概念,就是为了解决这类问题。
我们可以层层嵌套,把第一个查询结果放在一个括号里,并给这个结果起一个别名 temp,然后再在 temp 的基础上做 group by 操作。
得益于内部查询的概念,我们可以在某些时候编写比较复杂的 SQL 查询:
![](https://static001.geekbang.org/infoq/ca/ca7cf67e2243d0eae6e31eb27820af10.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
HAVING 子句
HAVING 也是一个需要理解的 SQL 结构,它很有用。
考虑一个问题:根据前面的结果,我们想得到那些拷贝数小于或等于 2 的影片。
我们可以通过使用内部查询的概念和 WHERE 子句来实现这个目的,如下所示,这里我们把一个内部查询嵌套在另一个内部查询中:
![](https://static001.geekbang.org/infoq/2b/2b5f1f0196d535c2535d63e8743a1090.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
我们也可以用 HAVING 子句:
![](https://static001.geekbang.org/infoq/2c/2c133b6d5bb79033b92f2c511e3682f7.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
HAVING 子句是用来过滤最终的聚合结果的,它与 WHERE 不同,WHERE 是用来过滤 FROM 语句中使用的表。HAVING 过滤的是 GROUP BY 处理后的最终结果。
正如你在上面的例子中看到的那样,解决一个问题,可以有很多实现方法。我们需要想出最省事的方法,因此 HAVING 在很多情况下更加简洁省事。
到此为止,已经从数据库服务、到 SQL 工作台再到基本的数据查询做了全面的了解,想必很多同学对 MySQL 也有了全局的认识。
接下来要做的就是逐个方面不断练习,我们可以自己寻找一些感兴趣的问题,然后通过 SQL 的方式找到答案,例如,哪个演员出演了最多的电影?哪个类型的电影被租用的最多?
这篇内容是一个简单的教程,如果想更加深入学习 SQL,给大家推荐一门来自加州大学戴维斯分校的 SQL 数据分析课程:SQL for Data Science | Coursera,目前已经有几十万人报名,评分也非常高,感兴趣的同学可以看一下。
版权声明: 本文为 InfoQ 作者【Jackpop】的原创文章。
原文链接:【http://xie.infoq.cn/article/a21d0ea2ae24d29af3a7a39c9】。文章转载请联系作者。
评论