In this tutorial, we will see how to set up a local MySQL database, how to use MySQL’s Workbench to create tables and insert data, as well as introduce some basic SQL queries to manipulate our data. In future blog posts we will expand upon this by creating an application to access and manipulate this data, as well as understand how we can leverage relational databases to represent data that relates to other data. But, before we do so, we need to setup a local database and understand the basic concepts of a SQL database.
If you haven’t done so already, install the MySQL Community Server. MySQL is an easy to use, relational database with great community support and should be sufficient for most projects. See MySQL’s documentation for installation instructions. The default settings should be sufficient for this tutorial, and you can skip installing additional connectors for things like Excel or Python at this time. Go ahead and configure a password for your root user, and keep this somewhere that you can find it when you inevitably forget what it is. It is best practice to add an additional MySQL user account with it’s own username and password. Resist the urge to use a known password. Windows users may want to disable MySQL from automatically starting on Windows login.
Creating Our Data in MySQL Workbench
Once MySQL has been installed, we’ll want to create a schema, a table and store some sample data in it. Open the MySQL Workbench, which is a GUI manager for our database installation. Traditionally, databases are managed via SQL queries, but tools the MySQL Workbench allow us to easily create and edit our data without worrying about the syntax of SQL yet.
In the top left under ‘MySQL Connections’ choose your ‘Local instance MySQL…’ and enter your root password.
We have now connected to our database running on our local machine. From here, we will want to create a new schema. A schema represents a collection of related objects in our database, in our case our tables and rows within our tables. To create a new schema, click the 4th icon in the toolbar.
Give your schema something meaningful to represent your collection of tables. MySQL dictates that names should be lowercase, and use the ‘_’ in place of spaces. In this example, we’re using the name “game_manager”, and then click ‘Apply’. MySQL will ask you to review the script that will be applied to the Database. In this case, our GUI has generated a SQL script that will be ran against our database:
CREATE SCHEMA `game_manager`;This bit of script will instruct our database to CREATE a new SCHEMA named ‘game_manager’ for us. Go ahead and click ‘Apply’ and ‘Finish’
We should now see in the bottom left our new schema. Right click this schema and ‘Set as Default Schema’. This tells MySQL workbench that we want to work with this schema from now on.
Now, let’s create a table for us to store our data in. In the toolbar, just to the right of where we created our Schema, create a new table. In this example, we’ve named our table ‘game’. Typically your table name will be a singular noun representing the collection of things you want to store. Click an empty row in the table in the middle of the UI. Initially this will create a new column named ‘id’ of type ‘INT’ (integer). Our table needs a unique identifier in order to organize our data, and this value will ensure that we have unique entries, even if our other values match. Our database will indicate this column as our unique identifier by selecting the ‘primary key’ checkbox, and will enforce this value to be set by checking the ‘Not Null’ checkbox. We also want our values to automatically increment, so check the ‘Auto Increment’ box as well.
Click just below this value to add another column. MySQL defaults this column to ‘gamecol’ with type VARCHAR(45). Let’s rename this column something more meaningful like ‘name’. It’s default type of VARCHAR(45) means that MySQL will store any variable length character string up to 45 characters in this row. This helps the database allocate space and optimize itself. Finally, it wouldn’t make sense to store a game without a name, so let’s click the ‘Not Null’ checkbox in the bottom right to have our database enforce this.
Let’s add one more column called ‘description’ that will store a brief description of our game. Our descriptions could very possibly be longer than 45 characters, so lets give us some more space to work with by updating the ‘Data Type:’ text box in the bottom right to VARCHAR(255). We may or may not provide a description, so do not select the ‘Not Null’ box like previously. Our final result should look something like the thumbnail below. Once done, click the ‘Apply’ button.
After hitting apply, we are presented again with some SQL that will be applied to our database:
CREATE TABLE `game_manager`.`game` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) NOT NULL, `description` VARCHAR(255) NULL, PRIMARY KEY (`id`));
What we see here is MySQL CREATEing a new TABLE into our ‘game_manager’ schema called ‘game’. It will contain our 3 columns with their name, datatype, and whether or not it will allow null values. Finally, it designates our ‘id’ column as our unique primary key. Go ahead and click ‘Apply’ and ‘Finish’ to have MySQL run this script.
Let’s take a look at our new table. Double click on our schema in the bottom left, and then double click the ‘Tables’, where we should see our new table ‘game’. If you right click the ‘game’ table and ‘Select Rows’, you shouldn’t be surprised to see no results returned. We see that MySQL has created a query in the query window
SELECT * FROM game_manager.game;. This query SELECTs all of our columns from our game table (designated by the *), and since there are no restrictions, returns all results (currently none).
Let’s change that by adding a few rows. Click the edit button to start adding new data. Remember, we are automatically generating IDs, and requiring the ‘name’ field. Once done, hit apply to see the SQL generated:
INSERT INTO `game_manager`.`game` (`name`, `description`) VALUES ('Ticket to Ride', 'Be your own robber barron creating train lines acorss the US.'); INSERT INTO `game_manager`.`game` (`name`, `description`) VALUES ('Formula D', 'A turn based racing game. Always be upshifting!'); INSERT INTO `game_manager`.`game` (`name`, `description`) VALUES ('Imperial Assault', 'Take on the Empire in this story driven turn based game.'); INSERT INTO `game_manager`.`game` (`name`) VALUES ('Mice and Mystics');
This script will INSERT new rows INTO our ‘game’ table. It designates which columns will be inserted, and the VALUES to be inserted into them. We see all rows have a ‘name’, but not all have a ‘description’. Go ahead and apply these changes. Now if we right click on our ‘game’ table and ‘Select Rows’ we should see our new data!
Writing Our Own SQL Queries
Let’s take a minute to understand some basic SQL queries. To get a much better understanding, check out the plethora of SQL tutorials online, like this one from W3 Schools. But for now, let’s just understand the basic operations we will want our application to do.
We’ve seen MySQL’s default query for returning all rows in our database:
SELECT * FROM game_manager.game; But let’s try selecting just one game:
SELECT * FROM game_manager.game WHERE game.name = 'Mice and Mystics';
This should return only one value. The WHERE clause provides criteria as to what rows to return.
We can also update values in our database. This can be done via the GUI, but let’s try writing our own:
UPDATE game_manager.game SET description='All the rules of a tabletop RPG with none of the creative options' WHERE game.name = 'Mice and Mystics';
This query will UPDATE our table, and SET a particular column to a new value, WHERE the following conditions are met. You will probably get an error from MySQL saying “Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column…” This is MySQL trying to prevent us from accidentally updating the wrong values in our table. The safest option is to use the ‘id’ field. Consider the hypothetical scenario where you had two games both named ‘Mice and Mystics’ but wanted to update a description for only a particular version. The above query would update All games named ‘Mice and Mystics’. Let’s update our query to use the ‘id’. We should see a success log at the bottom of your screen.
Finally, let’s delete a row from our table that is no longer needed. The following query allows us to do so:
DELETE FROM game_manager.game WHERE game.id = 4;
In this tutorial, we setup a MySQL database with a single table and a few sample data rows. We took a look at some of the SQL MySQL Workbench generates for us in its GUI, as well as wrote some of our own. Ultimately, we’re going to want to build an application that manages our data for us using some popular Java tools. In the next post, we’ll create a Java application that will retrieve and manipulate our data for us.