A small paper for teaching MySQL the first time through command line

mysql.sql

MacBook-Pro-de-mac:~ mac$ mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 25
Server version: 10.1.32-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| bdd-1        |
| bdd-2        |
…..
| bdd-n       |
+--------------------+
33 rows in set (0.01 sec)

MariaDB [(none)]> CREATE DATABASE zzz1
    -> ;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| bdd-1        |
| bdd-2        |
…..
| bdd-n       |
| zzz1               |
+--------------------+
34 rows in set (0.00 sec)

MariaDB [(none)]> use zzz1;
Database changed
MariaDB [zzz1]> show tables;
Empty set (0.00 sec)


MariaDB [zzz1]> create table users(id INT, name VARCHAR(50));
Query OK, 0 rows affected (0.02 sec)

MariaDB [zzz1]> show tables;
+----------------+
| Tables_in_zzz1 |
+----------------+
| users          |
+----------------+
1 row in set (0.00 sec)

MariaDB [zzz1]> SELECT * FROM users;
Empty set (0.00 sec)

MariaDB [zzz1]> INSERT INTO users VALUES (1, 'Amar');
Query OK, 1 row affected (0.01 sec)

MariaDB [zzz1]> SELECT * FROM users;
+------+------+
| id   | name |
+------+------+
|    1 | Amar |
+------+------+
1 row in set (0.00 sec)

MariaDB [zzz1]> INSERT INTO users VALUES (2, 'Ibrahim');
Query OK, 1 row affected (0.00 sec)

MariaDB [zzz1]> SELECT * FROM users;
+------+---------+
| id   | name    |
+------+---------+
|    1 | Amar    |
|    2 | Ibrahim |
+------+---------+
2 rows in set (0.00 sec)

MariaDB [zzz1]> UPDATE users SET name = 'Amimar' WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [zzz1]> SELECT * FROM users;
+------+---------+
| id   | name    |
+------+---------+
|    1 | Amimar  |
|    2 | Ibrahim |
+------+---------+
2 rows in set (0.00 sec)

MariaDB [zzz1]> ALTER TABLE users ADD COLUMN (birth_date DATE);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [zzz1]> SELECT * FROM users;
+------+---------+------------+
| id   | name    | birth_date |
+------+---------+------------+
|    1 | Amimar  | NULL       |
|    2 | Ibrahim | NULL       |
+------+---------+------------+
2 rows in set (0.00 sec)

MariaDB [zzz1]> UPDATE users SET birth_date = '1994-12-1' WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [zzz1]> SELECT * FROM users;
+------+---------+------------+
| id   | name    | birth_date |
+------+---------+------------+
|    1 | Amimar  | 1994-12-01 |
|    2 | Ibrahim | NULL       |
+------+---------+------------+
2 rows in set (0.00 sec)

MariaDB [zzz1]> SELECT NOW() FROM users;
+---------------------+
| NOW()               |
+---------------------+
| 2020-01-20 12:28:05 |
| 2020-01-20 12:28:05 |
+---------------------+
2 rows in set (0.00 sec)

MariaDB [zzz1]> UPDATE users SET birth_date = NOW() WHERE id = 2;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

MariaDB [zzz1]> SELECT NOW() FROM users;
+---------------------+
| NOW()               |
+---------------------+
| 2020-01-20 12:28:18 |
| 2020-01-20 12:28:18 |
+---------------------+
2 rows in set (0.00 sec)

MariaDB [zzz1]> SELECT * FROM users;
+------+---------+------------+
| id   | name    | birth_date |
+------+---------+------------+
|    1 | Amimar  | 1994-12-01 |
|    2 | Ibrahim | 2020-01-20 |
+------+---------+------------+
2 rows in set (0.00 sec)

MariaDB [zzz1]> DELETE FROM users WHERE id = 2;
Query OK, 1 row affected (0.01 sec)

MariaDB [zzz1]> SELECT * FROM users;
+------+--------+------------+
| id   | name   | birth_date |
+------+--------+------------+
|    1 | Amimar | 1994-12-01 |
+------+--------+------------+
1 row in set (0.00 sec)

MariaDB [zzz1]> UPDATE users SET birth_date = NULL WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [zzz1]> SELECT * FROM users;
+------+--------+------------+
| id   | name   | birth_date |
+------+--------+------------+
|    1 | Amimar | NULL       |
+------+--------+------------+
1 row in set (0.00 sec)

MariaDB [zzz1]> DROP TABLE users;
Query OK, 0 rows affected (0.01 sec)

MariaDB [zzz1]> SELECT * FROM users;
ERROR 1146 (42S02): Table 'zzz1.users' doesn't exist
MariaDB [zzz1]> show tables;
Empty set (0.00 sec)

MariaDB [zzz1]> use mysql;
Database changed
MariaDB [mysql]> DROP DATABASE zzz1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| bdd-1        |
| bdd-2        |
…..
| bdd-n       |
+--------------------+
33 rows in set (0.02 sec)

MariaDB [mysql]> quit
Bye