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:
This course covers the basics of SQL. You’ll also get some hands-on practice with common SQL interview questions.
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:
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.
This section will go through some of MySQL Workbench’s salient features.
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.
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.
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.
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.
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.
MySQL Workbench makes user management a lot easier. You can easily:
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.
MySQL Workbench allows you to easily export or import data by selecting database schemas or tables from the Object Browser.
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.
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.
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:
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.
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:
Now that our database is ready, let’s add our tables.
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:
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.
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_name
VALUES (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' );
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.
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:
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.
Join a community of more than 1 million readers. A free, bi-monthly email with a roundup of Educative's top articles and coding tips.