MySQL Tutorial

This document is a brief tutorial into MySQL. This tutorial is based on an interactive lecture with 3rd year computer science students. The databases created here are not perfect (e.g., they do not follow best practices in database design), and the user should not interact with the database as root, except to create the new database.

First, start by connecting to the database:

lorcan:~ lorcan$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 4.1.22-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Now, we use the CREATE statement to set up a new database. We will call this carsales and switch to it.

mysql> CREATE DATABASE carsales;
Query OK, 1 row affected (0.13 sec)
mysql> USE carsales;
DATABASE changed

Now we create a table to store details on cars - this will contain attributes for the car registration number, make, model, and price.

mysql> CREATE TABLE cars(registration_number VARCHAR(10) NOT NULL PRIMARY KEY,
    -> make VARCHAR(63) NOT NULL,
    -> model VARCHAR(63) NOT NULL,
    -> price INTEGER NOT NULL);
Query OK, 0 rows affected (0.09 sec)
 
mysql> DESCRIBE cars;
+---------------------+-------------+------+-----+---------+-------+
| FIELD               | Type        | NULL | KEY | DEFAULT | Extra |
+---------------------+-------------+------+-----+---------+-------+
| registration_number | varchar(10) |      | PRI |         |       | 
| make                | varchar(63) |      |     |         |       | 
| model               | varchar(63) |      |     |         |       | 
| price               | int(11)     |      |     | 0       |       | 
+---------------------+-------------+------+-----+---------+-------+
4 rows IN SET (0.00 sec)

Now we create a table to represent the customers who bought cars:

mysql> CREATE TABLE customers(id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(63) NOT NULL,
    -> address VARCHAR(255) NOT NULL,
    -> registration_number VARCHAR(10) NOT NULL);
Query OK, 0 rows affected (0.01 sec)
 
mysql> DESCRIBE customers;
+---------------------+--------------+------+-----+---------+----------------+
| FIELD               | Type         | NULL | KEY | DEFAULT | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| id                  | int(11)      |      | PRI | NULL    | AUTO_INCREMENT | 
| name                | varchar(63)  |      |     |         |                | 
| address             | varchar(255) |      |     |         |                | 
| registration_number | varchar(10)  |      |     |         |                | 
+---------------------+--------------+------+-----+---------+----------------+
4 rows IN SET (0.00 sec)

Now we populate the tables with some data using the INSERT statement. Lets assume a customer called Joe Soap, with an address at 1 Dublin St, Dublin, Ireland, bought a Ford Focus for €20,000.

mysql> INSERT INTO cars(registration_number, make, model, price) 
    -> VALUES('08D1', 'ford','focus',20000);
Query OK, 1 row affected (0.11 sec)
 
mysql> SELECT * FROM cars;
+---------------------+------+-------+-------+
| registration_number | make | model | price |
+---------------------+------+-------+-------+
| 08D1                | ford | focus | 20000 | 
+---------------------+------+-------+-------+
1 row IN SET (0.00 sec)
 
mysql> INSERT INTO customers(name,address,registration_number) 
    -> VALUES ('Joe Soap', '1 Dublin St, Dublin, Ireland', '08D1');
Query OK, 1 row affected (0.00 sec)
 
mysql> SELECT * FROM customers;
+----+----------+------------------------------+---------------------+
| id | name     | address                      | registration_number |
+----+----------+------------------------------+---------------------+
|  1 | Joe Soap | 1 Dublin St, Dublin, Ireland | 08D1                | 
+----+----------+------------------------------+---------------------+
1 row IN SET (0.00 sec)

If we wanted to change Joe Soap's address to 2 Limerick St, Limerick, Ireland, we would use UPDATE:

mysql> UPDATE customers SET address = '2 Limerick St, Limerick, Ireland' WHERE id=1;
Query OK, 1 row affected (0.38 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> SELECT * FROM customers;
+----+----------+----------------------------------+---------------------+
| id | name     | address                          | registration_number |
+----+----------+----------------------------------+---------------------+
|  1 | Joe Soap | 2 Limerick St, Limerick, Ireland | 08D1                | 
+----+----------+----------------------------------+---------------------+
1 row IN SET (0.00 sec)

If we wanted to query the database for the owner names, addresses, make, model and registration number for all cars sold in 2008 (cars.registration_number LIKE '08%' ) with a price greater than €10,000, we should use the following SELECT statement:

mysql> SELECT name,address,make,model,cars.registration_number FROM cars,customers 
    -> WHERE price > 10000 AND cars.registration_number LIKE '08%' AND 
    -> cars.registration_number = customers.registration_number;
+----------+----------------------------------+------+-------+---------------------+
| name     | address                          | make | model | registration_number |
+----------+----------------------------------+------+-------+---------------------+
| Joe Soap | 2 Limerick St, Limerick, Ireland | ford | focus | 08D1                | 
+----------+----------------------------------+------+-------+---------------------+
1 row IN SET (0.00 sec)

Finally, if we wanted to remove all data from the tables we would use the DELETE statement

mysql> DELETE FROM cars;
Query OK, 1 row affected (0.00 sec)
 
mysql> DELETE FROM customers;
Query OK, 1 row affected (0.00 sec)

And to drop the database and database tables we would use the DROP statement:

mysql> DROP TABLE cars;
Query OK, 0 rows affected (0.00 sec)
 
mysql> DROP TABLE customers;
Query OK, 0 rows affected (0.00 sec)
 
mysql> DROP DATABASE carsales;
Query OK, 0 rows affected (0.45 sec)