How to Use MySQL Workbench?

Introduction

In this article, I'll go through all of MySQL Workbench's main features, including connecting to databases, the tool's design, and composing and executing SQL queries.

What is MySQl Workbench?

A graphical application for working with MySQL servers and databases is called MySql Workbench. Data modeling, SQL development, extensive server configuration, use administration, backup, and other administration capabilities are all provided by MySQL Workbench. There are versions of MySQl Workbench for Windows, Linux, and Mac OS.

I'll assume MySQL Workbench is already set up on your computer. If not, get it from the MySQl website and install it using the instructions. Having access to a MySQL database is also required. If you require one, you can get the MySQl Community Edition database from the MySQL website. Click on the following link- MySQL Downloads

Once you have opened MySQL Workbench, it looks like this.

The Home tab is the first tab you see when you launch MySQL Workbench. The connections, represented by these boxes here, make up the majority of this screen. This will be empty the first time you launch MySQL Workbench, as seen in the picture above. A connection is a group of settings that allow you to connect to a database server and run queries. You can have multiple connections set up.

Create a new connection in MySQL Workbench

Let's establish a new connection. Click the plus sign (+) next to MySQL Connections to get started.

A new window will open. This is the New Connection window, and it's where you enter all of the details for your MySQL database connection. Enter the details to connect to the database. This could be a database running on your computer or on another server. The exact details will depend on how you've set up your database, which might be different from mine.

If you've set up a database on your computer, for example, you may use the server name of localhost and the username of root. Enter the connection name, which is the name you want to give the connection, and it will show on the Home Tab. I'll call this one "mysql demo," but you can call yours whatever you like. Enter the username.

You can specify a password by clicking on Store in Keychain, which will store the password you enter and not ask you for the password every time you connect. If you want to enter it every time for security reasons, you can skip this. 

Once you've entered the connection details, click on the Test Connection button. This will simulate a connection to the database, and it's a good way to check that the details you have entered are correct. If they are, it will show a success message. If not, you can investigate the issue and correct it. Once the connection test is successful, click OK, and the connection will be saved.

The connection will show as a new box on the Home tab, with the name you gave it on the top of the box.

Edit Connections in MySQL Workbench

If you get some of the details wrong or want to change a connection after you have created it, you can. To do this, right-click on the connection box and select Edit Connection. Or, you can click on the spanner icon next to the plus icon here, which opens a window of all connections. Select the connection you want to edit, change the details you need to change, click Test Connection to test it, and click Close. You can also add new connections, delete connections, duplicate existing connections, and move connections up and down the list in this window. Click close when you're done.

Overview of the UI (User Interface)

To connect to a database, click on the connection box. MySQL Workbench will connect to the database. After a moment, the database should be connected. The screen changes to the main MySQL workbench editor. The editor has several panels that serve different purposes. At the top, you have a toolbar this contains the menus, such as file and edit. On a Mac, they are at the very top of the screen. On Windows, they are within the application, but in either case, they are in this area of the screen. You've also got a range of icons here on the toolbar. The middle part of the window is the editor pane, and this is the main part of the editor. It's where you can write and run SQL queries.

There's a toolbar that applies to this editor that you can see here. On the right is a panel that contains context help and snippets. At the bottom is the Output panel, which shows a log of the queries you run on the database. On the left is a panel that shows the schemas, object browser, and administration section. On the right are a series of buttons that let you hide the different panels. Click on any of them, and the left, bottom, or right panels will be hidden. This is helpful if you're working on a smaller screen or with a larger query.

At the top left of the window is a home button. This will take you back to the screen if you want to connect to another database. The tab next to it is the current connection, and any further connections you run and connect to will be shown as separate tabs.

Create a new schema

To get started with working with a database, you may need to create one first. In MySQL, it's called a schema, which is a collection of tables and other objects. On my screen here, I have a few already created: sys and library. If you've just installed the MySQL database software, it may only come with the sys schema, which is for system tables and views. We should create our own schema to work within. Let's do that now.

To create a schema, you can click on the Create schema button on the toolbar, which is the cylinder icon with the plus sign next to it. Or, you can right-click in the schemas panel on the left and select Create schema. You can also run a create schema SQL statement if you prefer. You'll see this new schema editor tab appear in the main area of the screen. Enter a name for the new schema. We'll call ours "demo". You can leave the character set and collection values as the defaults. Click the small Apply button in the bottom right.

A window will appear to show you the SQL that will be run. Click the Apply button to create it. You should see a success message appear on the next screen. click close. On the schemas panel on the left, you'll see the new schema appear in the list. To start working with this schema, you'll need to make it the active schema. To do this, double-click on it in the list. The name will be bolded, and this indicates that any queries you run will be applied to this schema. You can also close the schema editor panel that still appears by clicking on the X button at the top of the tab.

Query Builder

Let's run some SQL to create a table. We'll create a simple table here using the Create Table statement. Once you enter a query, you can run it by going to the query menu and selecting Execute Current Statement. You could also click on the Execute button on the toolbar, which is the yellow lightning bolt here. or you can use the keyboard shortcut, which on a Mac is command-enter and on Windows is control-enter. This will run the Create Table statement. Once you run a query, a new line will appear in the output panel at the bottom of the screen.

A line will appear for any type of query you run. It will show a green tick if the query was successful or a red cross if it was not. It also shows the time the query was run and the query that was run. In the Response column, it will show either the error message or the number of rows affected, or something else, depending on the type of query you ran. The duration fetch time column shows how long the query took to run. when you run your Create Table query, you'll see the line here.

Now that we've created our table, let's add some data to it. We can do this by running an insert statement. We can add an insert statement in the editor panel after the create table statement. We could delete the create table statement, or we could add it after the statement. It's up to you. I'll add the Insert after the Create Table statement. You can write and run multiple queries in MySQL Workbench. You'll just need to make sure that each query ends with a semicolon.

You can click on the lightning bolt button on the toolbar to execute all queries or use the keyboard shortcut of command shift enter on a Mac Or control-shift-enter on Windows.

Object Browser

On the left side of the MySQL Workbench window is something called the object browser. This is on the schemas tab here. It shows a list of databases on the server you are connected to. If you click on the arrow to the left of the database name, it will be expanded to show several object types, which are tables, views, stored procedures, and functions. If you click the arrow next to one of the items, such as tables, it will be expended.

In this example, we see a list of tables in the database in alphabetical order. You can click on any of the table names, and information about the table will be shown below. We can see the columns, the data type, and the size. Primary keys are underlined, and foreign keys are bolded. You can expand the arrow next to the table name in the object browser to see groups of columns, indexes, foreign keys, and triggers. Expand the Columns entry, and you can see all of the columns. You can click on a column, and some information about it will be shown in the section on the bottom right.

Generate Statements

A feature of MySQL Workbench is the ability to generate SQL from existing objects. This can save you time when writing queries or creating objects. To see this, right-click on an object in the Object Explorer, such as a table. Select copy to clipboard, and you can see a range of options. You can copy the name, create a select statement, insert, update, delete, or create a new statement. For example, if we select insert, it will copy an insert statement for this table to the clipboard. We can then paste this into the SQL editor, and this statement will be shown. It automatically adds all of the column names and placeholders for the values. Now you can just add in the values you want and run the statement, saving your time.

Import and Export Data Overview

To import data into a database, right-click on your schema and select Table Data Import Wizard. You can follow the steps in this wizard to select the source of your data, the destination (such as a new or existing table), and a range of other settings.

To export data, you'll need to find a table that already has data by expanding the list in the schema browser. Right-click on the table you want to export data for and select Table Data Export Wizard. Once again, follow the steps in this wizard by selecting the output format and other settings.

Clean up code with Beautify Command

When you start to work with longer SQL statements, they can get quite messy. An easy way to clean up your editor is to format the code to make it easier to read. This can be done easily with the beautify command. Click the brush button on the toolbar, and the SQL script will be formatted to be easier to read. You can also quickly change your keywords to upper or lower case by going to edit, then format, and then either upper or lowercase your keywords.

Conclusion

Such a helpful tool is MySQL Workbench. Database administrators, system developers, and database developers can all utilize it. An ER model may be used to build a database without using MySQL commands.

I've simply discussed a few of the tool's most popular functions here. There are other additional features that you may find handy.

Thanks for reading this article. I hope this helped you to grasp the topic of how to use MySQL Workbench.

FAQs

Q 1. What are the system requirements for running MySQL Workbench?

A. For Windows, the recommended system requirements for MySQL Workbench are as follows:

Operating System: Windows 7, Windows 8, or Windows 10
Processor: Intel Pentium 4 or AMD Athlon 64 or later, with support for SSE2 instructions
RAM: 4 GB or more
Storage: At least 400 MB of available hard-disk space
Display: 1024x768 resolution or higher

For macOS, the recommended system requirements for MySQL Workbench are as follows:

Operating System: macOS 10.12 Sierra or later
Processor: 64-bit Intel processor
RAM: 4 GB or more
Storage: At least 400 MB of available hard-disk space
Display: 1024x768 resolution or higher

For Linux, the recommended system requirements for MySQL Workbench are as follows:

Operating System: Ubuntu 18.04, Debian 10, Fedora 32, or other recent Linux distribution
Processor: Intel Pentium 4 or AMD Athlon 64 or later, with support for SSE2 instructions
RAM: 4 GB or more
Storage: At least 400 MB of available hard-disk space
Display: 1024x768 resolution or higher

Q 2. What is the difference between a schema and a database in MySQL Workbench? 

A. In MySQL Workbench, a database is a collection of tables, while a schema is a container for organizing objects such as tables, views, and procedures within a database. So, the main difference between a schema and a database in MySQL Workbench is that a schema is a logical entity that resides within a database, whereas a database is a physical entity that contains one or more schemas.