Work bench is a GUI for managing and interacting with MySQL; just like SQL Management Studio for SQL Server. It allows you to perform tasks such as creating, querying and maintaining data, creating and modifying schema as well as database administration tasks. This post gives instructions on how to install, connect and interact with our new MySQL database which we created previously in the last blog post here.. We also have a quick tour of the more common features we will require. You can read more here.
The Source Code
You can install the latest version of Workbench from the website above however installing everything from your supported Linux distro’s repositories is a recommend practice. If you installed the latest version of MySQL Server then you should follow suit here; however assuming you have come from my previous blog then you are using the version as installed in your repositories.
sudo apt-get install mysql-workbench
An icon should have been placed within your start menu (Programming menu tree for me). Alternatively you can run the following command
Connect to our database
Configure A Connection
We are going to configure a database connection which will persist; we will then be able to connect to the database by selecting our configured connection in our connection list. Go to Database –> Manage Connections and the Manage DB Connections dialog will open. Enter the information as shown in the screen below replacing any details for your installation.
This will create a configured entry call DotNetOnLinux which connects to our service running on local host (127.0.0.1) on the default port (which we did not change this during installation) of 3306. The user we configured was called AdminUser.
For password hit the Store in Keychain.
Hit test connection and make sure it succeeds
Open a connection
To start working on our database we need to open the Connect to Database dialog box. This can be found under the Database menu; Database –> Query Database.
Select the configured connection from the previous step, select OK and when prompted enter your password. This is the MySQL user’s password and not your desktop user’s password.
Lets check the connection; we can see our database in the sachems tree view on the far left. You can open the tree view to see any tables and views.
Lets test the connection further by running a database query. Run the following to see which databases we are connected to:
You should see DotNetOnLinuxExample (or the name of you database you have created) and information_schema
A quick tour
Far left the schema tree view which will show any databases, tables, stored procedures, views etc. Highlighting a table allows pre-configured scripts to insert, update, delete and select statement into a query pane.
Far right is a snippets pane with pre-configured SQL which can be used as a template.
To create a new query tab there is a menu item under the File menu.
Results are displayed bottom middle.
Create a table
Highlight tables under the DotNetOnLinuxExample database schema entry.
Enter the table name. This one is called SampleTable. Leave the Schema to be our database. Leave the collation and engine as the defaults.
The following screen shot shows what to put in. Double click the last row under column name to enter a new name. Enter the names and data types as shown below.
The check boxes represent:
PX: Primary Key
NN: Non Null (mandatory)
AI: Identity Insert
Default: Default value when not provided.
Being a simple example we can leave everything else. Hit the apply button and.
Our table is now created and we can validate this by running the following query in a Query tab.
<pre>select * from DotNetOnLinuxExample.SampleTable
Lets insert some data,
INSERT INTO DotNetOnLinuxExample.SampleTable (Name, Height, DateOfBirth) VALUES ( 'Test', 180, '1978-10-25' )
Lets check the data was created correctly.
select * from DotNetOnLinuxExample.SampleTable
Also note the ID column was automatically populated. We can insert many entries and this will automatically be incremented.
Additional Worthy Notes
I am loving the beautify script option. My only gripe is it does not put key words into upper-case.
Toggle Auto Commit option along with Commit / Roll-back is pretty sweet as well.