{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# MySQL 环境准备"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## 安装"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"最新的 MySQL 服务程序可以在[官网下载](https://dev.mysql.com/downloads/installer/)，官网也提供了针对不同操作系统的[安装指引](https://dev.mysql.com/doc/refman/8.0/en/installing.html)。\n",
"\n",
"如果已经按照我们提供的[指南](x1-setup.md)配置好自己机器的编程环境的话，就可以用 [Homebrew](https://brew.sh/)（macOS 下）或 [Scoop](https://scoop.sh/)（Windows 下）来安装。比较推荐这个办法。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Windows"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"1. 在 ConEMU 中打开一个 PowerShell (Admin) 会话窗口（即管理员权限的命令行界面，如果有安全提示选择允许运行），然后执行下面的命令：\n",
"\n",
"```powershell\n",
"scoop bucket add versions\n",
"scoop install mysql57\n",
"```\n",
"\n",
"2. 打开 `C:\\Users\\trust\\apps\\mysql57\\current\\my.ini` 文件（如果不存在就创建一个），文件内容应该是这样的：\n",
"\n",
"```ini\n",
"[client]\n",
"# Which port the client should use as a default.\n",
"# this means if you're connecting to the local machine, then you can omit \n",
"# the --port parameter\n",
"port=3306\n",
"\n",
"[mysqld]\n",
"# Which port you're using. You'll need to write \n",
"# mysql --port=3360 if you're specifying the port\n",
"port=3306\n",
"# These two have to do with size of data allowed in the system\n",
"key_buffer_size=16M\n",
"max_allowed_packet=8M\n",
"\n",
"[mysqldump]\n",
"# The style of mysqldump to use as default\n",
"quick\n",
"```\n",
"\n",
"3. 回到前面的 PowerShell (Admin) 窗口，运行：\n",
"\n",
"```powershell\n",
"mysqld --install\n",
"```\n",
"\n",
"4. 在 Windows 搜索栏输入 Services 找到并运行 Services 管理程序，如果上一步没有问题，打开的列表中应该有 MySQL 一项，右键选择启动（Start）即可。\n",
"\n",
"> 在较老的 Windows 上没有 Services 管理程序，可以打开 Control Panel（控制面板），找到 Administration Tools（管理工具）里面的 Services（服务）。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### macOS"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"在命令行界面运行下面的命令即可：\n",
"\n",
"```shell\n",
"brew install mysql@5.7\n",
"brew services start mysql@5.7\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## 初始配置"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"当 MySQL Server 运行起来之后，就可以通过命令行工具 `mysql` 来操作，下面的操作无论在 Windows 还是 macOS 下都是一样的。\n",
"\n",
"在命令行下输入 `mysql -uroot` 进入 MySQL 的 REPL：\n",
"\n",
"```shell\n",
"Welcome to the MySQL monitor. Commands end with ; or \\g.\n",
"Your MySQL connection id is 32\n",
"Server version: 5.7.26 Homebrew\n",
"\n",
"Copyright (c) 2000, 2019, Oracle ...\n",
"\n",
"Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n",
"\n",
"mysql>\n",
"```\n",
"\n",
"在 `mysql>` 提示符之后可以输入任何 MySQL 支持的 SQL 语句（用半角分号 `;` 结尾）。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 修改 root 密码"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"注意我们目前是以 `root` 用户的身份登录到 MySQL 服务（命令行里的 `-u` 就是指定登录用户的参数），这是最大权限的用户，可以做任何事情，而且初始没有密码。这是个很不安全的状态，我们首先要改变这个状态。\n",
"\n",
"在 `mysql>` 提示符之后输入（注意将 `mypass` 改为你选择的 `root` 用户密码）：\n",
"\n",
"```sql\n",
"SET PASSWORD FOR root@localhost = PASSWORD('mypass');\n",
"```\n",
"\n",
"回车运行，`root` 用户的密码已经改成上面设置的，然后可以输入 `EXIT` 退出 MySQL 的 REPL，再次运行 `mysql -uroot` 将无法进入，会有一个 `Access denied` 的错误。\n",
"\n",
"在命令行运行（`-p` 参数表示用户有密码，希望在提示后输入）：\n",
"\n",
"```shell\n",
"> mysql -uroot -p\n",
"Enter password: *******\n",
"```\n",
"\n",
"输入密码后成功进入 REPL。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 创建数据库和对应用户"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"以下操作都以 `root` 用户身份在 `mysql` 的提示符后输入执行。\n",
"\n",
"```sql\n",
"mysql> CREATE DATABASE demo;\n",
"Query OK, 1 row affected (0.01 sec)\n",
"\n",
"mysql> SHOW DATABASES;\n",
"+--------------------+\n",
"| Database |\n",
"+--------------------+\n",
"| information_schema |\n",
"| demo |\n",
"| mysql |\n",
"| performance_schema |\n",
"| sys |\n",
"+--------------------+\n",
"5 rows in set (0.02 sec)\n",
"\n",
"mysql> USE demo\n",
"Database changed\n",
"mysql> SHOW TABLES;\n",
"Empty set (0.01 sec)\n",
"```\n",
"\n",
"如上所示，创建了一个新的数据库 `demo`，其中还没有任何表。下面来创建一个用户，并赋予该用户对 `demo` 数据库的完整操作权限（但不能访问 MySQL 服务中的其他数据）。\n",
"\n",
"```sql\n",
"mysql> CREATE USER learn@localhost IDENTIFIED BY 'demo';\n",
"Query OK, 0 rows affected (0.02 sec)\n",
"\n",
"mysql> GRANT ALL ON demo.* TO learn@localhost;\n",
"Query OK, 0 rows affected (0.00 sec)\n",
"\n",
"mysql> FLUSH PRIVILEGES;\n",
"Query OK, 0 rows affected (0.01 sec)\n",
"```\n",
"\n",
"上面第一条命令创建了一个叫 `learn` 的用户，限制其只能从本地（`localhost`）登录，并设置其密码为 `demo`；第二条命令则赋予该用户对 `demo` 数据库的完整权限（用户缺省是什么权限都没有的）；最后一条命令是要求服务器刷新权限库，令前面的设置生效。\n",
"\n",
"现在可以退出 REPL，然后尝试用 `mysql -ulearn -p` 来以 `learn` 身份登录，输入密码后可以进入 REPL 界面，然后可以试试看我们能做什么。\n",
"\n",
"```sql\n",
"mysql> SHOW DATABASES;\n",
"+--------------------+\n",
"| Database |\n",
"+--------------------+\n",
"| information_schema |\n",
"| demo |\n",
"+--------------------+\n",
"2 rows in set (0.00 sec)\n",
"\n",
"mysql> use demo\n",
"Database changed\n",
"mysql> SHOW TABLES;\n",
"Empty set (0.00 sec)\n",
" \n",
"mysql> CREATE TABLE users (id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, username VARCHAR(255), firstname VARCHAR(255), lastname VARCHAR(255)); \n",
"Query OK, 0 rows affected (0.04 sec) \n",
" \n",
"mysql> SHOW TABLES; \n",
"+----------------+ \n",
"| Tables_in_demo | \n",
"+----------------+ \n",
"| users | \n",
"+----------------+ \n",
"1 row in set (0.00 sec) \n",
" \n",
"mysql> DESC users; \n",
"+-----------+--------------+------+-----+---------+----------------+ \n",
"| Field | Type | Null | Key | Default | Extra | \n",
"+-----------+--------------+------+-----+---------+----------------+ \n",
"| id | int(11) | NO | PRI | NULL | auto_increment | \n",
"| username | varchar(255) | YES | | NULL | | \n",
"| firstname | varchar(255) | YES | | NULL | | \n",
"| lastname | varchar(255) | YES | | NULL | | \n",
"+-----------+--------------+------+-----+---------+----------------+ \n",
"4 rows in set (0.01 sec)\n",
"\n",
"mysql> DROP TABLE users;\n",
"Query OK, 0 rows affected (0.01 sec) \n",
"```\n",
"\n",
"可以看到，`learn` 只能访问自己有权限的数据库，并且在 `demo` 数据库内拥有创建和删除表的权限。\n",
"\n",
"至此 MySQL 环境配置完毕。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## 准备学习用数据库"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"最后我们来准备下在学习中我们会用到的数据库。这部分是可选的，如果没有完成，完全可以用自己创建的简单数据来测试，不过完成下面的操作也是对环境熟悉的过程，你完全可以试试，很多编程的经验都是折腾这些环境和数据得到的。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 准备数据文件"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"首先从 [Kagger](https://www.kaggle.com/karangadiya/fifa19) 下载数据，下载回来是一个名为 `fifa19.zip` 的压缩包，解压之后是一个名为 `data.csv` 的 *CSV* 格式数据文件，将其改名为 `fifa19.csv`。\n",
"\n",
"因为数据文件 `fifa19.csv` 的编码格式和我们下面用的工具不兼容，所以需要做一点处理，用 Visual Studio Code 打开这个文件，点击右下角的编码栏 `UTF-8 with BOM`："
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"<img src=\"assets/csv-data-in-vscode.png\">"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"点击上图右下角红框标出的栏，窗口上方会弹出菜单，依次选择 *Save with Encoding* 和 *UTF-8*，然后可以关闭 Visual Studio Code。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 数据导入"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"我们使用官方的 GUI 工具来完成这个导入。\n",
"\n",
"首先从 MySQL 官方网站下载 [MySQL Workbench](https://dev.mysql.com/downloads/workbench/) 并运行下载好的安装包（名字一般是 `mysql-workbench-community-x.x.xx-winx64.msi`）。\n",
"\n",
"打开安装好的 MySQL Workbench，如果前面 MySQL 服务安装和运行无误，这里 Workbench 会检测到本地运行的服务，并显示在启动界面，点击之就可以连接开始管理本地的数据库："
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"<img src=\"assets/mysql-workbench.png\">"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"如果前面的初始配置无误，在打开的界面左侧选择 *Schemas* 就可以看到 `demo` 数据库，右键选择 `demo` 数据库然后选择 *Table Data Import Wizard* 打开数据导入向导："
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"<img src=\"assets/mysql-data-import.png\">"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"按照向导一步步操作：\n",
"1. 浏览并选择刚才我们解压并修改过的 `fifa19.csv`，点击 *Next >*；\n",
"2. 选择 *Create new table*，下拉框里选择数据库 `demo`，表名输入 `players`，点击 *Next >*；\n",
"3. 在 *Columns* 下面去掉第一行 `MyUnknownColumn` 那一行的选择框，点击 *Next >*；\n",
"4. 点击 *Next >* 开始导入数据，这要一会儿。\n",
"\n",
"完成后在主界面 `demo` 数据库下面可以看到新建的 `players` 表，如果没有可以右键点击 `demo` 选择 *Refresh All*。\n",
"\n",
"右键点击 `players` 表选择 *Select Rows - Limit 1000* 可以查询显示表的前 1000 行，从而确认导入成功。"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.7.4"
}
},
"nbformat": 4,
"nbformat_minor": 4
}