Are you using MySQL Workbench and want to get the most out of it?
Or are you getting started with learning MySQL and want to know how to use this popular tool?
You’re in the right place.
In this video, you’ll learn all about the major features of MySQL Workbench, such as connecting
to a database, the layout of the tool, and writing and running SQL queries. You’ll see
how to view the different objects on a database, and how to generate SQL statements from them.
You’ll also see how to quickly and easily improve the formatting and readability of your SQL code.
We’ll also cover some handy features, such as snippets and editing data in the results.
Let’s get started!
Welcome to the Database Star YouTube channel - the place for developers
looking to improve their database and SQL skills.
I’ll assume you already have MySQL Workbench installed.
If not, you can download it from the MySQL website and follow the steps to install it.
You’ll also need access to a MySQL database.
I’ve got a couple of videos on how to set one up, but in short,
you can download the MySQL Community Edition database from the MySQL website if you need one.
Once you’ve opened MySQL Workbench, it looks like this.
This is the Home tab, and it’s shown whenever you open MySQL Workbench.
The main part of this screen is the connections, which are these boxes here.
When you open MySQL Workbench for the first time, this will be empty.
I’ve got a lot here because I’ve used this tool for a while.
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, like I do here.
Let’s create a new connection. To start, click on the plus button next to MySQL Connections.
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 to 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 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. The possible causes and solutions of connection errors are something for another video.
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.
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.
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,
which we will explain later in this video.
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.
We'll look at these features later in this video.
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 the administration section.
On the top right is 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 home 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.
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: demo, netflix, sample_csv, and sys.
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 in. 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 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 “test_schema”.
You can leave the character set and collation values as the default.
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 on the top of the tab here.
In the main query editor, you can enter your SQL queries.
The SQL code has formatting applied so you can tell SQL
keywords apart from table names and strings.
Let’s run some SQL to create a table. We’ll create
this simple table here using this 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 and enter, and on Windows it's Control and 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 X if it was not.
It 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 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 can 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, which we have done for the Create Table statement.
We’ve got an insert statement here too.
We can either run just the Insert statement, or we can run both statements.
You may want to run both statements if you did not run the Create Table statement before.
You could enter both the Create Table and the Insert, and run both statements in one command.
To do this, go to Query and select Execute All or Selection.
You can also 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.
If you want to run only one of the queries, click inside the query to put your cursor there, and
go to Query then Execute Current Statement. You can also click on the second lightning bolt icon
on the toolbar here, or use the keyboard shortcut Command Enter on Mac or Control Enter on Windows.
This means that only the statement under the cursor will be run.
Let’s write a Select query. We can do this to see the data in our table.
Here’s our Select query. We can run this statement like we did the other statements
by clicking on the second lightning bolt icon here, or using the keyboard shortcut.
Once a Select statement is run, a results panel shows at the bottom
of the screen with the results of the query. It shows your results in a table.
You can scroll the results up and down if there are more than what can fit in the panel.
You can resize the panel by dragging the border of the panel up and down.
The border of the panel is this grey line here.
You can close the results tab by clicking on the X on the tab at the bottom of the screen here.
This will give you more room to work with the query.
On the left of the results tab name is a pin icon. Clicking on this will pin the results tab,
which means that when you run another query, this results tab stays on the screen and the next set
of results is shown in a new tab. Without this, the tab of results disappears and is replaced
with the new query. So, this is helpful if you want to keep your results in between queries.
We’ve written a few queries in our SQL editor tab here.
A handy feature of most SQL IDEs, including MySQL Workbench, is the ability to save this file.
To save the queries we have written, either click on the Save icon on the toolbar here,
or go to File and then Save Script, or press Command S on a Mac or Control S on Windows.
You’ll be asked for a location to save the file.
Browse to a location where you want to save it, and enter a filename.
You’ll notice that the file extension is .sql, which is the standard file type for SQL scripts.
Click Save, and the file is saved.
To clarify, this will save the SQL statements you have written in the editor. It does not save the
data into the database. Saving the data in the database is done using a concept called “commit”.
If you want to save your data, or commit your data,
you can click the blue tick button on the toolbar here. This will permanently
save changes made to your data, such as what we added using our Insert statement.
I’ll cover the concept of committing and the reason it’s needed in another video.
At the bottom of the screen there is the Output panel which we mentioned earlier.
This is helpful if you want to see what has been run recently.
You can re-run one of the queries by copying it to the editor. To do this, right-click on the row in
the output you want to run again, and select either "Append Selected Items to SQL Script"
if you want to add the script to the end of your editor, or "Replace SQL Script
with Selected Items" if you want to replace what's in the editor with the selected query.
You can then run the query like normal.
On the left 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 have 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 expanded.
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 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.
A handy 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, a select statement, insert, update, delete, or a create 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 you time.
You can import and export data using MySQL Workbench. You may want to import data into a
table from a CSV file, or export data from a table into a CSV file or a series of SQL statements.
I’ll cover the full process of importing and exporting in another video,
as it’s probably too long to fit into this video, but I’ll show you how to find it.
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.
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 here,
and the SQL script will be formatted to be easier to read.
You can also quickly change your keywords to upper case or lower case
by going to Edit, then Format, then either Upcase or Lowercase your keywords
Another helpful feature of MySQL Workbench is the Snippets feature.
A snippet is a piece of code that you can save and use at a later time.
You can save your own code snippets by clicking this button on the toolbar, the one that looks
like a star. It will be added to the My Snippets section on the panel on the right.
You can edit the snippet by clicking on it and editing it. There is also
a range of buttons here that let you replace, insert, or copy the snippet to the clipboard.
It's helpful if you find yourself running the same SQL statements over and over.
The last feature we'll look at is the ability to edit the table data in your results.
If you run a query that has a unique identifier of a table in it, such as the primary key,
you're able to edit the data in the table in the results panel. I’ll change to another database,
the netflix one, as we can use a table such as genres which has a primary key.
You'll know it's editable if you see a greyed-out Apply button in the
bottom right corner. If the results are not editable, then you'll see a Read Only icon.
To edit a value, double-click on a cell in the results table and adjust the value.
Once you are finished editing, click the Apply button in the bottom right,
then click the Apply button on the popup to apply the changes. The data is then updated.
As you can see, MySQL Workbench is a powerful tool for working with MySQL databases.
I’ve only covered some of the more common features of the tool. There are a range of
other features which you might find useful, and I’ll look to cover them in future videos.
If you want to learn more about database design and SQL, visit my website at databasestar.com.
If you liked this video, consider subscribing to my channel.
Thanks for watching.