【Data Science】【Database】数据库系统原理（实验环境搭建）：MySQL Server 和 MySQL Workbench 的安装和使用

本文介绍在 Windows 下部署 MySQL Server 和 MySQL Workbench 实验环境.

1. 安装 MySQL Server

在 MySQL Installer 页面下载安装包 mysql-installer-community-5.6.24.0.msi.

• Product Version: $\boxed{5.6.24}$
• Operating System: $\boxed{MicrosoftWindows}$
• OS Version: $\boxed{Windows(x86,64-bit)}$

双击 mysql-installer-community-5.6.24.0.msi，启动安装程序.

勾选 I accept the license terms，点击 Next.

选择 Developer Default，点击 Next.

点击 Next.

点击 Yes.

点击 Excute.

点击 Next.

点击 Next.

默认 3306 为 MySQL 服务端口号，勾选Show Advanced Options，点击 Next.

设置 root 账户密码，点击 Next.

点击 Next.

点击 Next.

点击 Execute.

点击 Finish.

点击 Next.

检查密码，点击 Next.

点击 Execute.

点击 Finish.

点击 Next.

点击 Finish.

点击 MySQL 5.6 Command Line Client - Unicode 启动命令行.

其实是带参数的快捷启动方式：

1. 指定了 UTF-8

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe" "--defaults-file=C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" "-uroot" "-p" "--default-character-set=utf8"

2. 没有指定 UTF-8

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe" "--defaults-file=C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" "-uroot" "-p"

进入命令行界面.

输入 root 账户密码.

登录到 MySQL 命令行.

2. 安装 MySQL WorkBench

在前面小节中安装 mysql-installer-community-5.6.24.0.msi 时，已经附带安装了 MySQL WorkBench 6.2.5 . 如果没有安装，按照下面说明进行安装即可.

安装必要组件：

• Microsoft .NET Framework 4
• Microsoft visual C++ 2013 redistributable package

在 MySQL WorkBench 页面下载安装包 mysql-workbench-community-6.2.5-winx64.msi.

• Product Version: $\boxed{6.2.5}$
• Operating System: $\boxed{MicrosoftWindows}$
• OS Version: $\boxed{Windows(x86,64-bit)}$

双击 mysql-workbench-community-6.2.5-winx64.msi 启动安装程序.






找到 Workbench 的图标，点击启动.

3. 实验

3.1 MySQL Command Line

登录 MySQL 命令行.

查看数据库列表：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| test               |
| world              |
+--------------------+
6 rows in set (0.19 sec)

选中 world 数据库：

mysql> use world;
Database changed

查看表单列表：

mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.06 sec)

查看 city 表：

mysql> show columns from city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   |     |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.22 sec)

创建 university 数据库：

mysql> create database university;
Query OK, 1 row affected (0.04 sec)

查看数据库列表：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| test               |
| university         |
| world              |
+--------------------+
7 rows in set (0.00 sec)

选中 university 数据库：

mysql> use university;
Database changed

查看 tables，为空：

mysql> show tables;
Empty set (0.00 sec)

创建 table：

mysql> create table department(dept_name varchar(20), building varchar(15), budget decimal(12,2),primary key(dept_name));
Query OK, 0 rows affected (0.37 sec)

查看 tables 列表：

mysql> show tables;
+----------------------+
| Tables_in_university |
+----------------------+
| department           |
+----------------------+
1 row in set (0.00 sec)

查看 department 的行：

mysql> show columns from department;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| dept_name | varchar(20)   | NO   | PRI |         |       |
| building  | varchar(15)   | YES  |     | NULL    |       |
| budget    | decimal(12,2) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

向 department 插入值：

mysql> insert into department values("Music","Watson",50000);
Query OK, 1 row affected (0.26 sec)

查看 department 的数据：

mysql> select * from department;
+-----------+----------+----------+
| dept_name | building | budget   |
+-----------+----------+----------+
| Music     | Watson   | 50000.00 |
+-----------+----------+----------+
1 row in set (0.11 sec)

退出：

mysql> exit;

3.2 MySQL Workbench

启动 MySQL Workbench.

默认建立了一个连接实例：

删除这个默认实例：

点击 +，新建实例：

命名为 localhost：

存储 root 的密码：

点击 OK，新建实例：


双击进入 localhost 实例：

红色方框就是之前建立的数据库：

查看 department 表：


选中 university 数据库，插入数据. 点击红色方框内的按钮执行全部 SQL 语句：

use university;
insert into department values("Biology","Parking",60000);

查看 department. 点击红色方框按钮执行光标所在行的 SQL 语句：

select * from department;

结束.

4. 课件资料