Wednesday, 11 March 2020

Mini MySQL Tutorial: Movie Database

Here’s my mini MySQL Tutorial using examples which you can follow along with on your MySQL box if you wish.

Complete aside:
I learn best from examples. And I came across this article How adults learn and why so many of us hate training workshops.. - which reinforces why learning from examples is best. A couple of quotes:
“It may seem obvious, but adults don’t learn in the same way as children do.” “Some of the leading researchers in adult learning came up with the 70:20:10 methodology ... the model argues that adults learn primarily through experience, then social interaction, and the least through formal learning (roughly 70:20:10).”

Image: Version of MSQL I’m using here is 5.7.19

Mini MySQL Tutorial: Movie Database

1) Start using the playground database and look at the tables in the playground database.


mysql> USE playground;
Database changed

mysql> SHOW TABLES;
+----------------------+
| Tables_in_playground |
+----------------------+
| stam                 |
+----------------------+


2) Create an empty table called movies, with 3 columns - title, director,actor - and show the tables.


mysql> CREATE TABLE movies (title CHAR(20),director CHAR(10), actor CHAR(10));
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW tables;
+----------------------+
| Tables_in_playground |
+----------------------+
| movies               |
| stam                 |
+----------------------+


3) Insert a couple of rows worth of values into the table and take-a-look at the table.


mysql> INSERT INTO movies VALUES ('Chinatown','Polanski','Nicholson');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO movies VALUES ('Citizen Kane','Welles','Welles');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM movies;
+--------------+----------+-----------+
| title        | director | actor     |
+--------------+----------+-----------+
| Chinatown    | Polanski | Nicholson |
| Citizen Kane | Welles   | Welles    |
+--------------+----------+-----------+


4) I want to add a column for rating the movie (like 1 to 5 stars).


mysql> ALTER TABLE movies ADD COLUMN rating smallint;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM movies;
+--------------+----------+-----------+--------+
| title        | director | actor     | rating |
+--------------+----------+-----------+--------+
| Chinatown    | Polanski | Nicholson |   NULL |
| Citizen Kane | Welles   | Welles    |   NULL |
+--------------+----------+-----------+--------+


5) What happens if I add a row that just has a rating?


mysql> INSERT INTO movies (rating) VALUES (5);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM movies;
+--------------+----------+-----------+--------+
| title        | director | actor     | rating |
+--------------+----------+-----------+--------+
| Chinatown    | Polanski | Nicholson |   NULL |
| Citizen Kane | Welles   | Welles    |   NULL |
| NULL         | NULL     | NULL      |      5 |
+--------------+----------+-----------+--------+


6) Thet’s give a rating of 5 to Chinatown and 4 to Citizen Kane.


mysql> SELECT rating FROM movies WHERE title = 'Chinatown';
+--------+
| rating |
+--------+
|   NULL |
+--------+

mysql> UPDATE movies SET rating = 5 WHERE title = 'Chinatown';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT rating FROM movies WHERE title = 'Citizen Kane';
+--------+
| rating |
+--------+
|   NULL |
+--------+

mysql> UPDATE movies SET rating = 4 WHERE title = 'Citizen Kane';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM movies;
+--------------+----------+-----------+--------+
| title        | director | actor     | rating |
+--------------+----------+-----------+--------+
| Chinatown    | Polanski | Nicholson |      5 |
| Citizen Kane | Welles   | Welles    |      4 |
| NULL         | NULL     | NULL      |      5 |
+--------------+----------+-----------+--------+


7) How do we delete the row with a NULL title?


mysql> DELETE FROM movies WHERE title IS NULL;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM movies;
+--------------+----------+-----------+--------+
| title        | director | actor     | rating |
+--------------+----------+-----------+--------+
| Chinatown    | Polanski | Nicholson |      5 |
| Citizen Kane | Welles   | Welles    |      4 |
+--------------+----------+-----------+--------+


8) Modify the column title so that the contents cannot be not NULL and title is a PRIMARY KEY (i.e. cannot have duplicates). And add the NULL movie from above in twice.


mysql> ALTER TABLE movies MODIFY COLUMN title CHAR(20) NOT NULL PRIMARY KEY;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO movies (rating) VALUES (5);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> SELECT * FROM movies;
+--------------+----------+-----------+--------+
| title        | director | actor     | rating |
+--------------+----------+-----------+--------+
|              | NULL     | NULL      |      5 |
| Chinatown    | Polanski | Nicholson |      5 |
| Citizen Kane | Welles   | Welles    |      4 |
+--------------+----------+-----------+--------+

mysql> INSERT INTO movies (rating) VALUES (5);
ERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY'


As you see, the first NULL movie insert is successful, but the title is ‘’ and not NULL. The second NULL movie insert fails because it tries to add as ‘’ and there’s already a ‘’ there.

9) Tidy up now removing the ‘’ movie.


mysql> DELETE FROM movies WHERE title = '';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM movies;
+--------------+----------+-----------+--------+
| title        | director | actor     | rating |
+--------------+----------+-----------+--------+
| Chinatown    | Polanski | Nicholson |      5 |
| Citizen Kane | Welles   | Welles    |      4 |
+--------------+----------+-----------+--------+


THE END

Note: https://www.mysqltutorial.org/mysql-check-constraint/ “Prior to MySQL 8.0.16, the CREATE TABLE allows you to include a table CHECK constraint. However, the CHECK constraint is just parsed and ignored”.

Further Reading

There are loads of MySQL learning resources out there, here’s a few I stumbled across:

SQL: Basic Concepts


No comments:

Post a comment