MySQL Workbench tutorial: Complete guide to the RDBMS tool
The MySQL Workbench from Oracle is a graphic tool for working with MySQL databases. MySQL is an open-source relational database management system, and it is the most popular database system used with PHP. It is available on Windows, Linux, and Mac OS X.
MySQL Workbench is a powerful visual tool for DBAs, database architects, and MySQL developers. This tool offers data modeling, SQL development, and administration tools for server configuration and administration.
In this tutorial, we will introduce the MySQL Workbench and show you how to get started with this powerful tool.
We will learn:
- What is MySQL Workbench?
- Salient features of MySQL workbench
- How to install MySQL Workbench
- How to use MySQL Workbench
- What to learn next
Learn how to create databases with SQL
This course covers the basics of SQL. You’ll also get some hands-on practice with common SQL interview questions.
What is MySQL Workbench?
MySQL Workbench is a cross-platform, open-source relational database design tool that adds functionality and ease to your MySQL and SQL development. It integrates SQL design, development, creation, administration, and maintenance, also offering a graphical interface to work with your databases in a structured way.
MySQL Workbench provides capabilities for manipulating database models, such as:
- Creating a graphical model
- Reverse engineering for live databases to models (data modeling)
- Forward engineering model to a script/live database
- and more
There are several relational database management systems out there, such as Microsoft SQL Server, Microsoft Access, Oracle, and DB2. The MySQL Workbench offers some advantages to consider when choosing a tool.
MySQL supports multiple storage engines each with its own specifications, unlike other tools. MySQL also offers high performance due to its design and simplicity.
It is also known for being being cost effective. The community edition is free for users, and the enterprise edition has a low licensing fee.
Salient features of MySQL workbench
This section will go through some of MySQL Workbench’s salient features.
Modeling and design
Modeling is a great way to visualize requirements and creating well-performing databases that can keep up with the ever-evolving data requirements. MySQL Workbench allows you to create and manipulate models, reverse engineer a live database to a model, and create and edit tables and insert data.
You can convert ER diagrams into SQL statements and push them to a SQL server. You can also create models from a target database or even imported SQL files.
SQL develop tool
MySQL Workbench allows you to create, manage, and configure your connections and connection parameters to MySQL database servers. It also allows you to execute SQL queries on these connections using the in-built editor.
The Visual SQL Editor lets you create, edit, and run queries. It has auto-complete and color highlighters that aid in easily writing and debugging SQL statements.
Visual performance
MySQL Workbench offers various tools that let you view and improve performance. The Performance Dashboard gives you a quick view of the different performance metrics. Performance Reports let you analyze the performance of your databases.
Database migration
MySQL Workbench eases the process of migration. It lets you migrate from Microsoft SQL Server, SQLite, Microsoft Access, and many more. It also lets you configure, schedule, edit, and execute migration projects.
Connection management
MySQL Workbench lets you created, manage, and organize database connections. Object management MySQL Workbench’s Object Browser allows you to visually select tables and columns. You can easily navigate between schemas, select tables and fields, create new ones, or drop them.
User administration
MySQL Workbench makes user management a lot easier. You can easily:
- View account information of all users on the MySQL server
- Add and remove users
- Grant and revoke privileges
- Modify database permissions
- Change passwords
- Audit to see who did what and when
Server configuration
MySQL Workbench lets you fine-tune MySQL servers by allowing you to view and edit advanced parameters. You can also view server logs to identify problems quicker and track database changes.You can specify a MySQL hostname to define where to host your MySQL database.
Exports and import
MySQL Workbench allows you to easily export or import data by selecting database schemas or tables from the Object Browser.
Enjoying the article? Scroll down to sign up for our free, bi-monthly newsletter.
- Click the Go to Downloads Page button
- Choose your installer and click Download
- From there, you can create a new account or login to an existing one. You can skip this by clicking “No thanks, just start my download” button at the bottom.
- Once the installer has downloaded, open it.
- You may be prompted for permission, click Yes. This opens the installer. You will be asked to choose the setup type. We will go with Custom.
- Click Next. We will install only the MySQL server and the MySQL Workbench.
- Expand MySQL Servers, select the server you want to install, and pop it to the Products/Features To Be Installed window.
- Do the same thing for Applications, and install MySQL Workbench.
- Click Next.
- Click Execute to download and install the server and MySQL Workbench. This may take a while depending on your internet speed.
- Once that’s done, click Next. We now configure our SQL server instance.
- Go with the default settings here and click Next.
- Set the password for your root user. This user will have access to everything.
- Click Next and keep the default settings for the next steps.
- Click Execute. Once all the configuration steps are complete, click Finish.
- When you reach the screen below, you are ready to launch MySQL Workbench.
Keep the learning going.
Learn MySQL and SQL without scrubbing through videos or documentation. Educative’s text-based courses are easy to skim and feature live coding environments, making learning quick and efficient.
How to use MySQL Workbench
In this section we will look at using MySQL Workbench. The first step will be setting up a connection, to do that, follow these steps.
- Open MySQL Workbench. You will see a connection under MySQL Connections. Click this connection and enter the password you set for your root user.
Let’s look at this window before we move on to other things. As you can see in the image below, we have the following main sections:
- SQL editor: This is where you create and edit queries.
- Help panel: You can view the syntax and description for a particular keyword here.
- Navigation panel: This lets you manage your active MySQL connections and also shows the schemas on that server for a connection.
- History output window: This displays a summary of the executed queries in the following forms: Action Output, Text Output, or History Output.
Now that your MySQL Workbench is up and running let’s get started on creating databases and retrieving data. Let’s say you own a bakery and you want to keep track of different things like how much you sell in a day or your inventory.
You can easily do that by creating a database with multiple tables, adding data to those tables, and then retrieving the relevant information.
Creating a database
Let’s start with creating our database using the command:
create database bakery. Execute the command using the Execute button. On successfully executing a command, you should see a green checkmark next to the action item.
You can view all your databases in the Navigator panel. Follow these steps:
- Click Schemas at the bottom of Navigator. You can view all your databases here.
- If you don’t see your database here, click the Refresh button
Now that our database is ready, let’s add our tables.
Creating a table
The syntax for creating a table is as follows:
create table table_name (
column1 datatype,
column2 datatype,
column3 datatype,
);
For our first table Menu, we will have the following columns:
Item idNamePriceDate Created
The command for creating this table will be:
create table Menu (
item_id int,
item_name varchar,
price int,
created_on date,
);
Execute the command, and you should see a success message in the Action Output section at the bottom. You will also see the menu table in bakery after refreshing schemas.
You can view the empty menu table by clicking the table icon next to menu, as shown below.
Inserting data to a table
Let’s add data to our table, before that let’s look at the syntax. When using insert into, you have two options:
INSERT INTO table_nameVALUES (value1, value2, value3, ...);
This first option is used when you enter data into every column of the table.
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
This second option is used when you want to enter data into specific columns. Since we’re adding one row, it will be:
INSERT INTO menu
VALUES (1, 'croissant', 1, '2020-12-16');
If you view your table now, you will see the data has been added.
If you want to add multiple rows, use:
INSERT INTO menu
VALUES
(2, 'bread', 3, '2020-12-16' ),
(3, 'eclairs', 2, '2020-12-16' );
Retrieving data from a table
Let’s try retrieving data from this table. Let’s say we want to see what items we sell at our bakery. To do that, we need to display all the data in our table using:
SELECT * FROM menu;
If we want to see which item is the most expensive, we’d use:
SELECT * FROM menu
ORDER BY price DESC;
This command sorts the table based on the items with the highest price.
What to learn next
Congratulations on making it to the end! I hope you are now familiar with MySQL Workbench. This can be a powerful tool for building and managing your databases. But it doesn’t end here, there is so much to learn. Next, we recommend that you learn:
- Primary and foreign keys
- Clauses
NULLvalues- Joins
- Wildcards
- Stored procedures
To get started with these concepts and more, check out Educative’s course An Introductory Guide to SQL. You’ll start by covering the basics of SQL such as how to create a database, how to insert, query, and update data. You’ll also learn fundamental concepts that data scientists use everyday such as multi-table operations, nested queries, and how to set up views.
Happy learning!
