MySQL Workbench

MySQL Workbench is a free, official desktop application for working with MySQL. If you prefer clicking to typing, it is a good starting point. Everything it does is also possible with plain SQL, and the SQL is what you should learn.

What you can do in it

• Write and run SQL in a query editor, with colour highlighting and auto-complete.
• Browse databases, tables, columns and indexes in a sidebar.
• Edit table structures in a form.
• Draw diagrams of tables and their relationships (EER diagrams).
• Import, export and back up data.
• See performance information such as the query plan.

Create a connection

1. Open Workbench. On the home screen, click the + next to MySQL Connections.
2. Give the connection a name, such as Local MySQL.
3. Hostname 127.0.0.1, port 3306, username root (for learning on your own machine).
4. Click Test Connection, enter the password, and save.
5. Double-click the connection tile to open it.

Run your first query

1. In the query editor tab, type:

1

SELECT 'Workbench works' AS message;

1
2
3
4
5
6

+-----------------+
| message         |
+-----------------+
| Workbench works |
+-----------------+
1 row in set

1. Press the lightning bolt button (or Ctrl+Enter) to run the current statement.
2. The result grid appears below the editor. Rows are in a table, and the Output panel shows messages and errors.

Useful habits in Workbench

• Select the database first. Double-click a database in the sidebar so it turns bold. Or run USE learn;.
• Run one statement at a time with Ctrl+Enter. The lightning bolt without a selection runs the statement under the cursor. Run everything with Ctrl+Shift+Enter.
• Turn off “Safe Updates” only on purpose. By default Workbench blocks UPDATE and DELETE that have no key in the WHERE clause. That is a safety net. Leave it on.
• Limit large results. Workbench adds LIMIT 1000 to a plain SELECT by default, to avoid freezing on a huge table.
• Save your scripts as .sql files, so you can run them again.

Workbench vs the command line

Learn both a little. Workbench is great on your laptop. The command line is what you will have on a server.

Other tools

• phpMyAdmin: a web page, common on shared hosting.
• DBeaver, DataGrip, TablePlus: popular tools that work with many databases.
• VS Code extensions for MySQL.

They all do the same job: they send SQL to the server and show what comes back.