x5-mysql-setup.ipynb

{
 "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
}