全栈 - 11 数据库 MySQL使用方法

这是全栈数据工程师养成攻略系列教程的第十一期：11 数据库 MySQL使用方法。

我们已经掌握了如何用MAMP或WAMP在个人电脑上搭建Web环境，其中包含了用于存储数据的关系型数据库MySQL，现在就让我们来了解下如何使用MySQL。

基本概念

MySQL中可以存在多个数据库（Database），每个数据库对应一个相对独立的项目。一个数据库中可以包含多个数据表（Table），不同的数据表用来存储不同用途的数据。举例来说，可以新建一个chat数据库用于存储和某一社交网站相关的数据，里面有user和message两个表，分别用来存储用户基本信息、用户之间的聊天记录。需要注意的是，数据库名和数据表名最好都使用英文名称。

数据表和Excel中的表格很类似，既有行也有列，即我们之前提及的二维表结构。以user表为例，每一行记录了一名用户的基本信息，每一列即用户信息的一个字段，例如姓名、性别、职业等。

MySQL涉及的操作包括新建或删除数据库，以及在一个已有的数据库中新建、清空或删除数据表。除此之外，主要就是在一个已有的数据表中对数据进行CURD操作，即Create、Update、Read、Delete，分别对应插入数据、更新数据、读取数据、删除数据。接下来我们将了解，如何使用命令行、Web工具、本地软件、Python代码四种方法，来操作MySQL数据库。

命令行

可以在命令行中输入以下命令，按回车后再输入MySQL数据库的密码，即可进入MySQL提供的交互命令行，类似Python的交互编程环境，每敲一行MySQL语句，按回车即可执行。这种方法仅适用于单独安装MySQL数据库，不适用于MAMP或WAMP，而且对代码能力要求较高，故不推荐使用。

mysql -u root -p

Web工具

Web工具是指在MySQL内核之上，基于Web开发出的图形化操作界面。只要在网站上点一点，即可完成对MySQL的各类操作。其中最为常用的一种即phpMyAdmin，基于PHP开发，简单、轻量、好用，在MAMP或WAMP中也会自带phpMyAdmin。

在MAMP的欢迎页面上可以找到使用phpMyAdmin管理MySQL的链接，点击后将看到以下界面，左侧中所显示的是当前已存在的数据库，右侧中则显示了菜单栏和对应的内容。

首先让我们来尝试下如何新建数据库。默认情况下会存在三个数据库，information_schema、mysql和performance_schema，这三个数据库是MySQL自带的，不要去动它们。点击左侧中的New，或者右侧菜单栏中的Databases，都可以在右侧中看到当前已存在数据库的一些基本信息，以及进行数据库新建操作。

输入新建数据库的名称，推荐使用全英文。这里我们输入douban，因为需要将之前爬取的豆瓣电影数据存入数据库中。选择Collation为utf8_general_ci，然后点击Create即可。

新建好数据库后，由于数据库为空，会自动跳到新建数据表的页面。给数据表取个英文名，这里输入movie，然后选择表的列数，默认为4，点击Go进入下一步。即使数据表最终不是4列也没关系，多的列会自动忽略，列数不够同样可以继续加，所以不用担心。

接下来需要配置每一列的详细内容，包括Name、Type、Length/Values、Default、Collation、Attributes、Null、Index、A_I、Comments。我们首先填写第一个字段，每个数据表都需要一个主键即id，不同的行具有唯一不同的id，用于进行彼此区分。

• Name：字段的名称，纯英文，让我们输入id；
• Type：字段的变量类型，id应当是正整数，所以使用默认的INT即可；
• Length/Values：字段的长度，不填的话会使用默认值，即INT类型的默认长度；
• Default：插入数据时如果不提供值，字段的默认值，这里可以先不管；
• Collation：不填的话则使用数据库的Collation，否则覆盖，这里可以先不管；
• Attributes：字段的属性，这里可以先不管；
• Null：字段是否默认为空值，默认不勾选；
• Index：字段使用何种索引，这里选择PRIMARY，即主键，如果有弹窗则点击Go即可；
• A_I：是否自增，Auto Increasement，即在插入数据时如果不提供值，会自动增加，第一条记录为1，第二条记录为2，以此类推。这里需要勾上，因为我们希望id是自增的，从而不同记录使用不同的id；
• Comments：字段的备注信息，可以留空不管。

接下来再填一下电影的标题字段，Name输入title，Type选为VARCHAR，Length/Values输入255，因为255个字符对于标题而言足够了，其他选项不用管。Type的可选值包括数值类Numeric、日期时间类Date and time、文本类String、空间类Spatial四大类，以上所使用的VARCHAR是一种长度可变的字符串，使用时需要设置最大长度。如果需要存储更长的文本，可以考虑TEXT、MEDIUMTEXT、LONGTEXT等。

同理，继续填写电影的链接字段，填入url、VARCHAR和255；对于电影的简介字段，由于电影简介可能会很长，所以Name输入summary，Type选择TEXT；至于电影的评分字段，由于评分可以是小数，所以Name输入score，Type选择Numeric类中的FLOAT。如果需要继续增加字段，输入需要增加的列数并点击Go，然后根据字段特征完善Name、Type和Length/Values即可。

字段信息全部填写完毕后，点击右下方的Save即可完成数据表的新建，左侧中会选中刚才新建的数据库和数据表，右侧中会出现新的菜单栏。点击Browse可以查看数据表内的数据记录，点击Structure可以查看数据表的结构，即各项字段的配置内容，点击SQL可以在当前数据表上执行SQL命令，点击Insert可以向当前数据表中插入数据记录，点击Export和Import分别可以导出和导入数据表，点击Operations可以进一步执行清空数据表和删除数据表等操作。

在上图所示的Structure标签页中，可以查看数据表的结构，即各项字段的详细配置，并对某个字段执行修改Change、删除Drop等操作。除此之外，还可以向数据表中添加若干列，以及查看数据表的空间存储情况。

如果在左侧点击某一数据库，则右侧的菜单栏相应地会变成数据库级别的操作，如查看数据库的结构，即各个数据表的基本信息，在当前数据库上执行SQL命令，导出和导入数据库，执行删除数据库等操作。

细心的话可以发现，每次在phpMyAdmin上执行相关操作之后，右侧菜单栏下都会出现相应的SQL命令，说明phpMyAdmin都是将我们的点击操作转换成了相应的SQL命令，然后交与MySQL执行。

让我们尝试下向movie表中插入数据。在左侧中点击douban数据库中的movie表，选择右侧菜单栏中的Insert，依次在title、url、summary、score的Value中填写对应的值，id留空。如果需要同时插入两条数据，则取消勾选ignore并填写第二条数据的对应值。如果需要同时插入更多数据，则在底部的Continue insertion with后选择相应的行数，并依次填写相关数据。数据全部填写完毕后，点击页面下方的Go，即可进行数据插入操作，在菜单栏下也可以看到对应INSERT命令。之后再点击Browse，即可查看刚插入的数据。

总而言之，phpMyAdmin是一款简单轻量好用的Web工具，它提供的功能虽然有限，但都是管理MySQL所需的最为核心的功能，因此更容易上手，不会因为功能太多反而导致眼花缭乱。

本地软件

相对于phpMyAdmin等Web工具，本地数据管理软件连接更稳定、功能更强大，例如Navicat Premium，Mac OS版本的软件界面如下图所示。

Navicat支持MySQL、Oracle、PostgreSQL、SQLite、SQL Server、MariaDB等多种数据库，在数据库和数据表之上还有数据连接（Connection）这一概念，因为Navicat可以记录并连接多个主机上的数据库，而phpMyAdmin作为一种Web工具仅能连接本地数据库。

让我们来尝试新建一个连接。点击左上角的Connection，选择MySQL，在弹出的对话框中依次填入连接名称、主机地址、端口、用户名、密码等信息，其中基于MAMP安装的MySQL主机地址即为localhost，点击OK即可新建一个连接。当然，新建连接时还可以涉及到更多配置内容，这里就不展开讲了。

新建连接后，在Navicat软件界面的左侧即可看到当前已存在的全部连接，双击连接名称即可查看连接下存在的数据库，双击数据库名称即可建立数据连接，并查看数据库下存在的数据表。在连接名、数据库名、数据表名上右键，都会出现一系列可执行的操作，例如在数据库名和数据表名的右键菜单里，都有Execute SQL File和Dump SQL File两项，分别对应数据库级别和数据表级别的导入和导出操作。

总的来说，Navicat功能更强大，使用门槛也更高，毕竟可点击的按钮、可配置的选项、可执行的操作都远远更多。我个人的习惯是，使用phpMyAdmin完成新建数据库、新建数据表、定义表字段等操作，因为phpMyAdmin简单轻量，但又足以完成这些任务；使用Python代码对数据表进行CURD操作，因为项目中涉及的数据记录可能非常多，所以用Python代码处理自然是最高效和灵活的选择；当需要导入导出大量数据，或者导入导出需要对涉及字段进行灵活配置时，则使用Navicat完成复杂的导入导出任务。

所以，我首先在本机上编程并将数据库和数据表整理好，在本地Web环境上实现Web项目和数据库的交互，然后将数据库导出并导入到云端服务器上的MySQL，最后将Web项目也部署到服务器上，即可完成一次数据项目的开发。

至于如何使用Python来操纵MySQL数据库，将在下一节中进行讲解。

视频链接：MySQL使用方法