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
Comments
Post a Comment