SQL: An Introduction to MySQL Workbench
In my previous post, I wrote on how to install MySQL Server and Workbench. In this post, we will take a closer look at MySQL Workbench. It is a great tool for working with databases and tables, as well as making queries.
As one might expect, the first thing to do when exploring the uses of MySQL Workbench is to open the Graphical User Interface (GUI).
There are a number of options available within the home screen. There is a link to visit the Documentation for Workbench, one for its blog, and one for the official forum.
One the left side, there is the MySQL Connections button at the top. There is also a link to models, which allows you to design database schemas. Third, there is a migration wizard that assists you in “migrating” tables and data from a supported database or another MySQL instance.
As you are learning, it most likely that you will be spending most of your time in the MySQL Connections tab. If you followed along in my Installation post, you will likely have already made a single connection that you can start using right away.
When you first open up your connection it will automatically open with a new SQL file open.
In the left column there are a number of possible links. The Workbench automatically opens to the Administration tab. On this tab there are links for Management, the MySQL Instance you are using, and the Performance of your instance. The second tab is for your Schemas. At the beginning there is likely to be only a Sys database for the system configuration. To go into more detail about the Schemas tab, it is useful to import or “migrate” an example database.
A great database for beginners is an employees database originally created by Fusheng Wang and Carlo Zaniolo (http://www.cs.aau.dk/TimeCenter/software.htm). The file is XML format, but Giuseppe Maxia created the current schema and Patrick Crews converted the data from XML to relational. To install this you can follow the instructions in MySQL Documentation:
Once this database is installed, there is a wide range of information offered in the tabs.
As seen above, the first level of tabs contains Tables, Views, Stored Procedures, and Functions. The Tables tab will have even more information such as the table name, the columns of the table, the indexes, foreign keys, and triggers.
Please note that when you Insert, Delete, or Update tables, you need to click the refresh button found in the top right of Schemas tab to see the changes in the Schemas tab.
This gives a great deal of information in a logical and easy way to find. But as with the home screen with connections, the majority of your time learning SQL would be in SQL files. Below we have a SQL file with a simple query.
So, we have a query that will select everything from the employees table. As we take a deeper look into this file, let’s first explore some of the most important icons in the toolbar.
In the top left corner there are icons of a file and floppy disk. As with most programs this represents the option to open a SQL file or to save the current file. The lightning bolt icon runs the entire SQL file while the lightning bolt with a cursor next to it runs only the query that is highlighted with the cursor. In the center of the toolbar there is a tab for limiting the total number of rows that can returned by a query. There are many options including no limit. The last tool we will mention is the beautify tool that is represented by the brush icon. As you have probably learned in your instruction in SQL, the formatting of queries is incredibly important for readability by others (usually your teammates). When clicked, the query will be formatted into an easier to read style.
The next piece of the workbench to explore is the results of a query. We can run the above query by clicking either of the lightning bolt icons or you can use the keyboard shortcut of Command + Return on Macs to execute a single query or Command +Shift + Return to execute all queries in the SQL file.
Once the query is run there are multiple new pieces of information.
The first section to notice is that a Result Grid will pop up with the returned values from the query. At the bottom of the screen there is an Action Output Bar. This holds information such as if it was successful (Green Check) or not (Red X), the time the action was executed, what the action was, the response, and how long it took for the computer to complete the action. In this case, because Workbench automatically limits the results to 1000 rows, we have the query in the action column, and the number of rows returned in the response column. The Duration is an incredibly important column because it helps indicate how efficient your query is. This becomes more important as the queries get more complex and the Tables sizes grow into millions or billions of rows.
Overall, MySQL Workbench is an extremely useful tool when learning and working on SQL databases and gives a great deal of information that can be used to better your work.
I hope this post helps your exploration and understanding of using MySQL Workbench to work with Relational Databases. Thanks for reading!
For more Documentation on MySQL Workbench:
For more information on Licensing of the Employees Sample Database:
