Installing MySQL for Testing – Part 2

Pin It
Share on Tumblr

As a continuation of Part 1 in this tutorial, we’ll work through some basic interaction with our test database using MySQL Workbench.  Some things we’ll cover include:

  • Setting our current database
  • Creating a new table
  • Inserting rows into a table
  • Selecting rows from a table
  • Basic UI review of Workbench

Without further adieu, lets get started using MySQL!

1. Double click to the instance you defined at the end of Part 1 to connect:12-ChooseConnection2. After the connection finishes, then double-click the “testbed” database in the lower left (this is part of the schema browser UI, equivalent to “SHOW DATABASES” SQL command):13-WorkbenchConnectedNote that this double-click is the equivalent to issuing the “USE testbed” SQL command in the CLI.   Once connected, you should see additional data under the database name listing tables, views, etc:14-SelectTestbedDatabase3. Now that we are using the proper database, lets create our first table using the UI.  Click the “Add Table” icon and define a simple four field table called my_first_table:15-CreateNewTableThese columns follow a standard table format:

  • Auto-Increment unsigned int for a Primary Key
  • Some various text columns
  • A timestamp column that tracks when a row was created

After you hit the “Apply” button, the UI will spit out the SQL that will be run under the hood and give you a chance to confirm:16-VerifyCreateSQLHitting “Apply” again on this window will issue the command to the server and create the table.  You should see the “Success” window and then be able to see the new table definition in the UI when you select the table:17-SQLSuccess18-ViewNewTable4. Now that the table exists, you should be able to click the “data grid” icon to get a listing of the rows in the table (note that the grid should be empty as you just created the table):19-EmptyResultGridThe SELECT SQL will be displayed in the query pane as shown above.  Also note, any queries issued here will include a LIMIT 1000 clause by default.  This is mainly to prevent an accidental tablescan of a huge table (nobody wants to read through a billion rows).

5. Let’s insert some data!

Start by clicking the “Add Row” icon.  This will make the column fields editable and you can enter data for the non-default columns (notice I left out id and last_visit  as those have defaults defined):20-AddRowAs in the CREATE TABLE operation above, when you hit “Apply”, it will let you preview the SQL you are actually going to run:21-VerifyInsertSQLAnd finally, hitting “Apply” again will run the SQL and you can now see your result (with the set defaults) when you re-execute the SELECT query:22-ViewNewRowAnd that’s it!  You’ve now created a test database with your first table and inserted some data.  Naturally, this barely scratches the surface of MySQL’s (or Workbench’s) capabilities, but should be a good primer to get you off and running.

Check back as I continue along this “getting started” series and will cover other topics such as:

  • CLI interaction
  • Query profiling
  • Linux setup process
  • Key tuning variables
  • and more!