Mysql 简明教程
MySQL - Workbench
MySQL workbench 是用于处理 MySQL 服务器和数据库的图形化工具。它由 Oracle 开发和维护。此应用程序包含各种功能,例如数据建模、数据迁移、SQL 开发、服务器管理、数据库备份、数据库恢复等等。MySQL Workbench 支持 MySQL 5.7 及更高版本。
The MySQL workbench is a graphical tool for working with MySQL servers and databases. It is developed and maintained by Oracle. This application includes various features such as data modelling, data migration, SQL development, server administration, database backup, database recovery and many more. MySQL Workbench supports the MySQL versions 5.7 and above.
MySQL Workbench 是推荐给数据库开发人员和管理员的应用程序。我们可以在 Windows、macOS 和 Linux 操作系统上下载此应用程序。
MySQL Workbench is a recommended application for database developers and administrators. We can download this application on Windows, macOS, and Linux operating systems.
Functionalities of MySQL Workbench
以下是 MySQL workbench 的五个主要功能 −
Following are the five main functionalities of MySQL workbench −
-
SQL Development − This functionality allows to create and manage connections to database servers. SQL queries can be executed on the database connections using the built-in SQL editor in MySQL workbench.
-
Data Modelling (Design) − This functionality allows to create models of our database schema graphically, reverse and forward engineer between a schema and a live database, and edits all aspects of the database using the comprehensive table. The table editor provides facilities to edit Tables, Columns, Indexes, Triggers, Partitioning, Options, Inserts, Privileges, Routines and Views.
-
Server Administration − It allows us to administer the MySQL sever instances by administering users, viewing database health, performing backup and recovery, inspecting audit data, and monitoring the MySQL server performance.
-
Data Migration − It allows us to migrate from the Microsoft SQL server, Microsoft Access, Sybase ASE, SQLite, SQL Anywhere PostreSQL, and other RDBMS tables, objects and data to MySQL. Migration also allows us to migrate from prior versions of MySQL to the latest releases.
-
MySQL Enterprise support − This functionality provides the support for Enterprise products such as MySQL Enterprise backup, MySQL Firewall and MySQL Audit.
MySQL Workbench Editions
MySQL Workbench 提供三个版本。具体说明如下:
The MySQL Workbench is offered in three editions. The same is described below −
-
MySQL Workbench Community Edition - Open Source (GPL License)
-
MySQL Workbench Standard Edition - Commercial
-
MySQL Workbench Enterprise Edition - Commercial
Community Edition
这是一个数据库系统的开源且可免费下载的版本。它属于 GPL(通用公共许可证),并且它得到了众多开发人员社区的支持。
This is an open-source and freely downloadable version of the database system. It comes under the GPL (General public license) and it is supported by a large community of developers.
Standard Edition
这是商业版,能够提供高性能和可扩展的 OLT(在线事务处理)应用程序。
This is the commercial edition that gives the capability to deliver high-performance and scalable OLT (online transaction processing) applications.
Enterprise Edition
Enterprise 版包含一组高级功能、管理工具和技术支持,以实现最高的可扩展性、安全性、正常运行时间和可靠性。此版本将降低 MySQL 应用程序开发、部署和管理中的风险、成本、复杂性。
The Enterprise edition includes a set of advanced features, management tools, and technical support to achieve the highest scalability, security, uptime and reliability. This edition will reduce the risk, cost, complexity in the development, deployment, and managing MySQL applications.
MySQL Workbench Administration Tool
MySQL Workbench 中的管理工具在保护数据方面发挥着至关重要的作用。以下是 MySQL Workbench 提供的一些管理工具:
The administration tool in MySQL workbench plays a vital role in securing the data. Following are some administration tools provided by MySQL workbench −
User Administration
此工具允许创建、修改和删除与用户相关的帐户。使用它我们可以管理用户的特权和权限。
This tool allows to create, modify and delete the user related accounts. Using this we can manage the user’s privileges and permissions.
Server Configuration
它允许我们配置服务器参数。它显示了有关服务器和状态变量、线程数量、缓冲区分配大小、针对最佳性能进行微调等的各种详细信息。
It allows us to configure the server paramerters. It shows various details about the sever and status variable, number of threads, buffer allocation size, fine-tuning for optimal performance, etc.
Database Backup and Restorations
此工具用于导入和导出 MySQL 转储文件。这些转储文件包含用于创建表、视图、存储过程等的 SQL 脚本。
This tools is used for importing and exporting the MySQL dump files. These dump files contains the SQL script for table creation, view creation, stored procedure creation etc.
Create Database in MySQL Workbench
我们可以在 MySQL Workbench 中创建数据库,而无需显式使用 SQL 语句。以下步骤演示如何在 MySQl Workbench 中创建数据库:
We can create a database in MySQL Workbench, without explicitly using SQL statements. Following steps show how to create a database in MySQl Workbench −
Step 1 −打开 MySQL Workbench 应用程序并使用用户名和密码登录。
Step 1 − Open the MySQL workbench application and log in using the username and password.
Step 2 −现在,要创建数据库,请右键单击模式菜单并选择创建模式选项。创建模式的另一种方法是单击下图中用红色轮廓突出显示的模式按钮。
Step 2 − Now, to create a database, right click on schemas menu and select the create schema option. Another way to create a schema is by clicking on the schema button which is highlighted with a red outline in the following picture.

Step 3 − 选择创建架构选项后,将打开一个新的架构窗口,如下图所示。我们现在可以输入数据库名称(比如 testdb),并使用默认排序规则。然后,单击“应用”按钮。
Step 3 − After selecting the create schema option, a new schema window will be opened as shown in the following figure. We can now enter the database name (say testdb) and use the default collation. Then click on the apply button.

Step 4 − 单击“应用”按钮后,将打开一个新窗口。现在,单击“应用”按钮,然后单击“完成”按钮。
Step 4 − A new window will be opened after clicking the apply button. Now click on the Apply button and then click on Finish button.
Step 5 − 现在,我们可以在架构菜单中看到上述创建的 testdb 数据库。如果不可见,请单击架构菜单右上角的“刷新”按钮。
Step 5 − Now, we can see the above-created testdb database in the schema menu. If it is not visible, click on the refresh button on top-right corner in schemas menu.

Note − 如果我们想要查看有关 testdb 数据库的更多信息,请单击 testdb 数据库,然后单击 i 图标。信息窗口显示各种选项,例如表、列、索引、触发器等。
Note − If we want to see more information about the testdb database, click on the testdb database and then click on the i icon. The information window displays various options such as Table, Column, Indexes, Triggers and many more.
Drop Database in MySQL Workbench
同样,我们还可以使用工作台删除数据库。以下是使用 MySQL 工作台删除数据库的步骤:
Similarly, we can also drop a database using workbench. Following are the steps to drop a database using MySQL workbench −
Step 1 − 要在 MySQL 工作台中删除数据库,请右键单击我们想要删除的特定数据库,然后单击“删除架构”选项,如下图所示。
Step 1 − To drop a database in MySQL workbench, right click on the particular database that we want to delete and click on drop schema option as shown in the following figure.
此处,我们尝试删除先前创建的数据库 testdb。
Here, we are trying to delete the previously created database, testdb.

Step 2 − 现在,将打开一个新窗口,然后单击“立即删除”选项,如下图所示。
Step 2 − Now, a new window will be opened and click on the drop now option as shown in the below picture.

MySQL Workbench Create, Alter, and Drop Table
我们可以使用 MySQL 工作台应用程序创建、更改和删除表。让我们一步一步地研究它们。
We can create, alter, and drop a table using the MySQL workbench application. Let us look into them in a step by step process.
Create Table
以下是使用 MySQL 工作台创建表的步骤:
Following are the steps to create a table using MySQL workbench −
Step 1 − 打开 MySQL 工作台,使用用户名和密码登录。然后,单击窗口左角的“架构”菜单。此处,我们可以找到 MySQL 服务器中存在的所有数据库。
Step 1 − Open MySQL workbench, login with the username and password. Then, click on the schemas menu on left-corner of the window. Here, we can find all the databases which exist in the MySQL server.
Step 2 − 现在,双击先前创建的 testdb 数据库,我们可以在数据库下看到子菜单,例如表、视图、函数和存储过程,如下图所示。
Step 2 − Now, double click on the previously created testdb database, and we can see sub-menu under the database such as Tables, Views, Functions and Stored procedures as shown in the picture below.

Step 3 − 现在,右键单击 Tables ,然后单击 create table 选项。此外,我们可以通过单击上图中用红色轮廓突出显示的“表”按钮来创建一个表。
Step 3 − Now, right click on the Tables and click on create table option. Else, we can create a table by clicking the table button which is highlighted with a red outline in the above picture.
Step 4 − 单击“创建表”选项后,将显示以下内容。然后,我们需要输入表的名称(比如 employees),并使用默认排序规则和引擎。
Step 4 − After clicking the create table option, the following will be displayed. Then we need to enter the name of the table (say employees) and use the default collation and engine.

Step 5 − 现在,单击表字段并输入列名称。我们还可以为列选择属性,例如主键 (PK)、非空 (NN)、唯一键 (UQ) 等等。提供所有详细信息后,单击“应用”按钮。
Step 5 − Now, click on the table fields and enter the column names. We can also select attributes to the columns such as Primary Key (PK), Not Null (NN), Unique Key (UQ) and so on. After providing all the details click on the apply button.
Step 6 − 我们单击“应用”按钮后,将打开一个 SQL 语句窗口,然后我们需要单击“应用”按钮和“完成”按钮来保存更改。
Step 6 − After we click the apply button, an SQL statement window will be opened and then we need to click on apply button and finish button to save the changes.
Step 7 − 现在,返回到架构菜单并选择包含新创建的 employees 表的数据库。在那里,我们可以找到我们提供给表的所有数据。
Step 7 − Now, go back to the schema menu and select the database that contains the newly created employees table. There we can find all the data that we provided to the table.

Alter Table
以下是使用 MySQL 工作台更改表的步骤:
Following are the steps to alter a table using MySQL workbench −
Step 1 − 选择我们要修改的表,然后单击 i 图标。此处,我们正在修改先前创建的 employees 表。
Step 1 − Select the table which we want to modify and click on the i icon. Here, we are modifying the previously created employees table.

Step 1 – 单击 i 图标后,将显示以下内容,在其中我们可以找到用于修改表的列、索引和其他数据类型等内容。修改后,单击分析表按钮以保存所做的更改。
Step 2 − After clicking on the i icon, the following will be displayed where we can find the options to modify the table’s columns, indexes and other datatypes etc. After modifying, click on the analyze table button to save the changes.

Drop Table
以下是使用 MySQL workbench 删除表的步骤 −
Following are the steps to drop a table using MySQL workbench −
Step 2 – 要删除表,我们需要选择要删除的特定表。然后,对其右键单击并选择删除表选项。在这里,我们要删除 employees 表。
Step 1 − To drop a table, we need to select the particular table which we want to delete. Then, right click on it and select the drop table option. Here, we are deleting the employees table.

drop now – 将打开一个新窗口,然后单击 Step 1 选项从数据库中删除该表。
Step 2 − A new window will be opened and click on drop now option to remove the table from the database.

MySQL Workbench Insert and Delete Table Rows
我们来看一下如何使用 MySQL Workbench 应用程序插入和删除表行。
Let’s have a look on how to inset and delete table rows using the MySQL Workbench application.
Insert Rows
以下是使用 MySQL workbench 在表中插入行的步骤 −
Following are the steps to insert rows into a table using MySQL workbench −
Step 1 – 打开 MySQL workbench。然后单击窗口左上角的模式菜单。在这里,我们看到我们先前创建的数据库 (testdb) 和表 (employees)。
Step 1 − Open MySQL workbench. Then click on schemas menu on left corner of the window. Here, we can see that our previously created database (testdb) and table (employees).
Step 2 – 首先双击 testdb 数据库,然后双击表。现在,如果我们将鼠标悬停在 employees 表上,将显示一个表图标。单击该表图标。
Step 2 − First double click on testdb database, then double click on tables. Now, if we hover the mouse on the employees table, a table icon will appear. Click on that table icon.

Step 3 – 现在,单击受尊敬的列或行以插入值。
Step 3 − Now, click on the respected column or row to insert the values.

Step 4 - 插入值后,单击应用按钮。然后将打开一个新的 SQL 语句窗口,单击应用和完成按钮以保存记录。如果我们想要修改记录,我们可以按照讨论过的相同步骤进行操作。
Step 4 − After inserting the values, click on the apply button. Then a new SQL statement window will be opened, click on apply and finish buttons to save the records. If we want to modify the records, we can follow the same procedure as discussed.
Delete Rows
以下是在表中删除行的步骤使用 MySQL workbench −
Following are the steps to delete rows into a table using MySQL workbench −
Step 1 – 要从表中删除一行,我们只需右键单击该行并单击删除行(行)选项,如下所示。在这里,我们删除了第一行 (即 ID = 1)。
Step 1 − To delete an individual row from the table, we can simply right click on that particular row and click on the Delete row(s) option as shown below. Here, we have deleted the first row (i.e ID = 1).

Step 2 – 选择删除行后,将打开一个新窗口。然后,单击应用并完成按钮以保存更改。在下图中,我们可以看到行 1 (即 ID = 1) 已被删除。
Step 2 − After selecting the delete row(s) option a new window will be opened. Then, click on apply and finish buttons to save the changes. In the following picture, we can see that the row1 (i.e. ID =1) got deleted.

MySQL Workbench Export and Import Database
我们来看看如何使用 MySQL Workbench 应用程序导出和导入数据库。
Let’s have a look on how to export and import database using the MySQL Workbench application.
Export Database
以下是使用 MySQL workbench 导出数据库的步骤 −
Following are the steps to export a database using MySQL workbench −
Step 1 – 要导出数据库,请转到窗口顶部的菜单栏并单击 Server 选项。单击该项后,选择 data export 选项。
Step 1 − To export a database, go to menu bar at the top of the window and click on the Server option. After clicking that, select the data export option.

Step 2 – 将打开一个新的数据导出窗口。现在,选择 testdb 数据库,它将显示其中存在的所有对应表。
Step 2 − A new data export window will be opened. Now, select the database (testdb), it will display all the corresponding tables exist in that.

Step 3 – 现在,单击下拉设置,在那里我们将有三个选项,例如,转储结构和数据、仅转储数据和仅转储结构。
Step 3 − Now, click on the drop-down setting, there we will be having three options such as Dump Structure and Data, Dump Data Only, and Dump Structure Only.
-
Dump Structure and Data − This option will export both the table structure and data records.
-
Dump Data Only − This will export only the records in the table.
-
Dump Structure Only − This will export only the table structure, which are columns and datatypes defined by us.
Step 4 − 选择“转储结构并导出数据”选项。然后在导出选项中,我们可以看到两个选项来选择导出路径,如下所示 −
Step 4 − Select the Dump Structure and Data option. Then in the Export options, we can see two options to select the export path as follows −
-
Export to Dump Project Folder − This option will export all the tables as separate SQL files under one folder. It is recommended when we import the exported file one by one.
-
Export to Self-Contained File − This options will export all the databases and tables in a single SQL file. It is recommended when we import all the databases, tables, and data rows using a single SQL file.
Step 5 − 选择“导出到转储项目文件夹”选项,然后单击开始导出按钮。然后,它会显示一个进程栏,如下面图中所示。
Step 5 − Select the "Export to Dump Project Folder" option and click on the start export button. Then it displays a process bar as shown in the figure below.

Step 6 − 现在,我们可以在导出时找到导出的文件在提供的路径中。
Step 6 − Now, we can find the exported file in the provided path while exporting.
Import Database
以下是使用 MySQL 工作台导入数据库的步骤 −
Following are the steps to import a database using MySQL workbench −
Step 1 − 要导出数据库,请转到窗口顶部的菜单栏并单击 Server 选项。单击该选项后,选择 data import 选项。
Step 1 − To export a database, go to menu bar at the top of the window and click on the Server option. After clicking that, select the data import option.

Step 2 − 将打开一个新的数据导入窗口。在这里,我们可以找到两个选项,例如“从转储项目文件夹导入”和“从自包含文件中导入”。
Step 2 − A new data import window will be opened. Here, we can find two options such as "Import from Dump Project Folder" and "Import from Self-Contained File".
Step 3 − 现在,我们将选择“从转储项目文件夹导入”选项,然后单击“加载文件夹内容”以显示项目文件夹中的所有可用数据库。
Step 3 − Now, we are going to select the "Import from Dump Project Folder" option and click on the "Load Folder Contents" to show all the available databases in the project folder.
Step 4 − 现在,从数据导入选项中选择 testdb 数据库并选择相应的 employees 表。
Step 4 − Now, select the testdb database from the Data import option and select the respective employees table.

Step 5 − 选择“转储结构和数据”选项,然后单击“开始导入”按钮从导出的文件中导入数据库。
Step 5 − Select the "Dump Structure and Data" option and click on the "start import" button to import the database from the exported file.

Step 6 − 现在,转到架构菜单,然后单击 employees 并再次单击表以查看导入的表。
Step 6 − Now, go the schema menu and click on the employees and again click on the table to see the imported table.