Lab 2: Introduction to Structured Query Language (SQL) in MySQL
Context
In lab 1, you should have created an empty git repo in GitLab and cloned it into your public_html folder on IGOR. Within this git repository is where you will work on the lab exercises for the remainder of the term.
Note: If you want to work locally rather than on IGOR, you may clone your repo locally as well. Bear in mind you will need to have installed git and a LAMP stack on your machine, and that you should `push' to GitLab regularly to avoid losing any work. You will be required to deploy your app on IGOR as well, so that the tutor may test it.
From now on, the lab work that you do will be continually assessed. Your tutor or a lab assistant may wish to check your repository at any point, so please ensure it is up to date. You should aim to complete each week's lab exercise before the following lab.
All the labs will have you working towards the delivery of a single `record store' application. Although this work will be worth only 10% of the entire module, it is very likely that the end of module exam exam will refer to tasks performed in the labs, so it is very important that you complete them!
Overview
This week, you will be practising running basic SQL queries in the MySQL Database Management System.
Your aims will be to:
- Create a database for your record store
- Define a simple schema (i.e. create the tables and define the keys)
- Insert records into your database
- Retrieve a record from the database
Task 1: Create the database
If you completed task 6 in lab 1, you should not need to do this. In case you didn't make it, here are the steps again for creating a MySQL database. Note: if you experience errors accessing the MySQL shell, it may be because your MySQL account has not been activated on the Intranet. Please check lab 1 task 6 for how to fix this.
- SSH into IGOR:
ssh -l YOUR_USERNAME doc.gold.ac.uk
- Start the MySQL shell:
mysql -u YOUR_USERNAME -p
- From the MySQL shell, run:
CREATE DATABASE YOURUSERNAME_anotherdb;
- Check it worked by running:
SHOW SCHEMAS;
- Exit mysql with
<Ctrl>+c
Task 2: Define your first table
- Enter your record store repo on IGOR:
cd public_html/dnw/NAME_OF_PROJECT
- Create a directory to store your SQL files:
mkdir sql
- Enter the new directory:
cd sql
So that you have a record of how your schema is structured, you will write the SQL needed to define it in a .sql
. .sql
files are generally understood by the different SQL-based DMS's (albeit with some differences), so it is one way to give portability to your app.
- Open a new file in the nano text editor:
nano record-store.sql
- Define a single table to start with. For example, CREATE TABLE record ( id INT AUTO_INCREMENT, title VARCHAR(50), artist_id INT, type TINYTEXT, genre TINYTEXT, price DECIMAL(10, 2) unsigned, PRIMARY KEY(id) ); Note: Refer to the MySQL documentation (https://dev.mysql.com/doc/refman/5.0/en/data-types.html) for more information about the different data types.
- To save and close the file, hit
<Ctrl>+x
, followed byy
, and then,<Enter>
- To execute the statement in your
.sql
file, enter the MySQL shell and do: use YOUR_DATABASE_NAME; source record-store.sql; If it worked, you will receive a response such as: Query OK, 0 rows affected (0.03 sec) - Exit the MySQL shell with
<Ctrl>+c
Note: An alternative method which avoids going in and out of MySQL is to do: mysql YOUR_DATABASE_NAME < record-store.sql -p Using the second method won't return a message unless there's an error.
Task 3: Define additional tables
- Continue to add the tables you require to your
.sql
file - Because we don't want to keep making copies of the same table each time we run this file, at the start of the file, add a line, DROP TABLE IF EXISTS 'tablename', 'tablename2'; replacing the comma separated table names above with your own table names.
- As you create more tables, you can start to define your foreign keys. For example, if I went on to create an
artist
table, I could link it to myrecord
table by adding a foreign key in therecord
table definition like so: CREATE TABLE record ( id INT AUTO_INCREMENT, title VARCHAR(50), artist_id INT, type TINYTEXT, genre TINYTEXT, price DECIMAL(10, 2) unsigned, PRIMARY KEY(id), FOREIGN KEY(artist_id) REFERENCES artist(id) );
You must ensure the foreign key has the same data type as the column it refers to. If you need to see an example, clone the week-2
repo.
Note: It is a good idea to keep things simple for now. 4/5 tables should be sufficient to gt you started. You don't have to model e-commerce functionality either, you could just make a simple catalogue to begin with.
Task 5: Insert a record (row) into each of your tables
Note: To avoid confusion, the term 'record' shall refer to a row in a table, and the term 'product' shall refer to an item in the store.
- From the
sql
directory of your app, open another new file in nano: nano dummy_data.sql This file could be used by a developer working on your app to insert 'dummy data' into their database. - Inside the new file, write some statements to insert some dummy data into your database schema. For example,
INSERT INTO YOUR_DATABASE_NAME.artist (id, first_name, last_name) VALUES (NULL, 'Bob', 'Marley');
Or to insert multiple records,
INSERT INTO YOUR_DATABASE_NAME.artist (id, first_name, last_name) VALUES (NULL, 'Burning', 'Spear'), (NULL, 'Lucky', 'Dube')
Notice how, in each case, we must reference each column in the artist table in order, but we are supplying
NULL
values to the 'id' field as this has been set toAUTO_INCREMENT
. -
<Ctrl>+x
>y
><Enter>
to exit nano. - Run the
.sql
file from the IGOR terminal: mysql YOUR_DATABASE_NAME < dummy_data.sql -p
If you don't know what value to give a foreign key field, you can also give it a NULL value to start with. See the example in the week-2
repo if necessary.
Task 6: Retrieve a record from the database
By now you should have one or more records in each table of your tables. To retrieve a record, you will enter the MySQL shell and perform a query directly.
- Open another new
.sql
file in nano calledpractice_queries.sql
: nano practice_queries.sql - Write a simple query to return one or more columns. For example,
SELECT first_name, last_name FROM artist;
Note: Avoid using
*
to select all columns from a table. It is more efficient to list all the fields. - Run a query with one or more filters. For example, SELECT title FROM record WHERE year = 1973; Or, SELECT title FROM record WHERE year = 1973 AND genre = "Reggae";
-
<Ctrl>+x
>y
><Enter>
to exit nano. - Run the
.sql
file from the IGOR terminal: mysql YOUR_DATABASE_NAME < practice_queries.sql -p Hopefully it will respond with the appropriate data! Again, look at the example in theweek-2
repo if you have problems.
Task 7: Using a visual database tool to query a MySQL database
So far, you've been working on your database from the MySQL shell. This was mainly so that you could see how things look under the bonnet of the MySQL DMS! In future, you may find it easier to use a visual database tool to write and test queries - although you're welcome to stick to using the shell if you wish!
2 that are available on the university lab PCs are MySQL Workbench and phpMyadmin. The latter is a web-based application, while the former is a desktop application.
Use either phpMyAdmin or MySQL Workbench to retrieve a product from your database by name.
Accessing PhpMyAdmin
- You can log in to phpMyAdmin here: https://www.doc.gold.ac.uk/phpmyadmin/ using your campus login credentials.
- From the menu on the left, you can browse your existing schemas.
- Clicking on the name of the new schema you just created should bring up a list of tables in the main window.
- You can use the various options (i.e. 'browse', 'search', etc.) to construct SQL queries through the phpMyAdmin GUI. This can be helpful, sometimes, but it is also very useful to be able to write them yourself, and to understand what the different keywords/options mean.
Using MySQL Workbench
Task 8: Push your changes to the remote repo
Now that you've done some work, you must commit
and push
your changes to GitLab using the git version control system.
- Inside the root of your record store repository on IGOR, do: git add sql/* to tell git you want it to stage (prepare) all of the files in the sql directory for the next commit.
- Now do:
git commit -m 'Adding SQL files for lab 2'
The
-m
option says we want to add a commit message, and the string after it is just a short message explaining what the commit is about. - And finally do: git push origin master This tells git to push your changes to the origin repo (on GitLab), and on the master branch. **Note: ** From now on, try to do regular add/commits with short meaningful messages, each time you make a significant change to a file in the repo. You don't have to 'push' every time you commit, but committing regularly will make sure there's a nice record of the work you've been doing - making it easy for the tutor to give you credit for it!