Gabriele Ferro Home

I keep studying MySQL - To Infinity and Beyond!

Day 100: 17 December 2020

**Today’s Progress:**

SQL might seem extremely complex if we compare it to a NoSQL solution like MongoDB (with Mongoose) and JSON objects, where we create a simple schema without worrying about how many maximum characters our field will contain.

Will one letter be enough to define a field? Do we use Date or Datetime? Will the value of NULL be allowed for this field? How much will the table weigh in bytes?

None of these concerns showed up when I used MongoDB.

We will find below some parts of code that I wrote following the course "Manage your databases with MySQL" (in French) by OpenClassRooms.

I'm rewriting them here so that it's easier for me to find them again.

First we need to connect to the database:

C:\Users\gabri>mysql -h localhost --port 3306 -u root -p
 Enter password: *
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 4
 Server version: 5.6.17-log MySQL Community Server (GPL)
 Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
 Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.
 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

According to tradition we write an "Hello World!"

 mysql> SELECT 'Hello World!';
 +--------------+
 | Hello World! |
 +--------------+
 | Hello World! |
 +--------------+
 1 row in set (0.00 sec)

We can create another user and connect to the database using its credentials.

This allows us to avoid using the root account.

mysql> CREATE USER 'student'@'localhost' IDENTIFIED BY 'gabriele';
 Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL PRIVILEGES ON elevage.* TO 'student'@'localhost';
 Query OK, 0 rows affected (0.02 sec)
mysql> exit
 Bye
C:\Users\gabri>mysql -u student -p
 Enter password: 
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 5
 Server version: 5.6.17-log MySQL Community Server (GPL)
 Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
 Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.
 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Finally, let's create a database and a table

This course uses a real case scenario of an animal breeding database.

  • Before creating a table, its columns must be defined. To do this, we must determine the type of each column and decide whether or not it can contain NULL (i.e. contain no data)
  • Each table created must define a primary key, i.e. a column that will uniquely identify each row
  • The engine of a table defines the way it is managed. We will use the InnoDB engine, which notably allows to define relations between several tables
mysql> CREATE DATABASE elevage CHARACTER SET 'utf8';
 Query OK, 1 row affected (0.01 sec)
mysql> USE elevage
 Database changed
mysql> CREATE TABLE Animal (
     -> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
     -> espece VARCHAR(40) NOT NULL,
     -> sexe CHAR(1),
     -> date_naissance DATETIME NOT NULL,
     -> nom VARCHAR(30),
     -> commentaires TEXT,
     -> PRIMARY KEY (id)
     -> )
     -> ENGINE=INNODB;
 Query OK, 0 rows affected (0.04 sec)

Each animal has some information that might be unknown at first, and for this reason we allow the default (NULL) if the value cannot be defined.

The identification number (id) is a SMALLINT and also UNSIGNED because we don't need negative numbers. So, we shift the same range min-max but now we start counting from number 0.

TypeNumber of bytesMinimumMaximum
TINYINT1-128127
SMALLINT2-3276832767
MEDIUMINT3-83886088388607
INT4-21474836482147483647
BIGINT8-92233720368547758089223372036854775807

We can check the tables and the type of information they contain

 mysql> SHOW TABLES;
 +-------------------+
 | Tables_in_elevage |
 +-------------------+
 | animal            |
 +-------------------+
 1 row in set (0.00 sec)
 mysql> DESCRIBE Animal;
 +----------------+----------------------+------+-----+---------+----------------+
 | Field          | Type                 | Null | Key | Default | Extra          |
 +----------------+----------------------+------+-----+---------+----------------+
 | id             | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
 | espece         | varchar(40)          | NO   |     | NULL    |                |
 | sexe           | char(1)              | YES  |     | NULL    |                |
 | date_naissance | datetime             | NO   |     | NULL    |                |
 | nom            | varchar(30)          | YES  |     | NULL    |                |
 | commentaires   | text                 | YES  |     | NULL    |                |
 +----------------+----------------------+------+-----+---------+----------------+
 6 rows in set (0.07 sec)

We can of course delete a table

mysql> DROP TABLE Animal;
 Query OK, 0 rows affected (0.01 sec)
mysql> SHOW TABLES;
 Empty set (0.00 sec)

We can create another table, and play with it!

We can add a field, remove it and also change the name of a column.

  • The ALTER TABLE command allows us to modify a table
  • When adding or modifying a column, we must always specify its (new) full description (type, default value, auto-incrementing if necessary)
mysql> CREATE TABLE Test_tuto (
     -> id INT NOT NULL,
     -> nom VARCHAR(10) NOT NULL,
     -> PRIMARY KEY(id)
     -> );
 Query OK, 0 rows affected (0.06 sec)
mysql> SHOW TABLES;
 +-------------------+
 | Tables_in_elevage |
 +-------------------+
 | animal            |
 | test_tuto         |
 +-------------------+
 2 rows in set (0.00 sec)
mysql> DESCRIBE Test_tuto;
 +-------+-------------+------+-----+---------+-------+
 | Field | Type        | Null | Key | Default | Extra |
 +-------+-------------+------+-----+---------+-------+
 | id    | int(11)     | NO   | PRI | NULL    |       |
 | nom   | varchar(10) | NO   |     | NULL    |       |
 +-------+-------------+------+-----+---------+-------+
 2 rows in set (0.02 sec)
mysql> ALTER TABLE test_tuto
     -> ADD COLUMN date_insertion DATE NOT NULL;
 Query OK, 0 rows affected (0.04 sec)
 Records: 0  Duplicates: 0  Warnings: 0
mysql> DESCRIBE test_tuto;
 +----------------+-------------+------+-----+---------+-------+
 | Field          | Type        | Null | Key | Default | Extra |
 +----------------+-------------+------+-----+---------+-------+
 | id             | int(11)     | NO   | PRI | NULL    |       |
 | nom            | varchar(10) | NO   |     | NULL    |       |
 | date_insertion | date        | NO   |     | NULL    |       |
 +----------------+-------------+------+-----+---------+-------+
 3 rows in set (0.01 sec)
mysql> ALTER TABLE test_tuto
     -> DROP date_insertion;
 Query OK, 0 rows affected (0.05 sec)
 Records: 0  Duplicates: 0  Warnings: 0
mysql> DESCRIBE test_tuto;
 +-------+-------------+------+-----+---------+-------+
 | Field | Type        | Null | Key | Default | Extra |
 +-------+-------------+------+-----+---------+-------+
 | id    | int(11)     | NO   | PRI | NULL    |       |
 | nom   | varchar(10) | NO   |     | NULL    |       |
 +-------+-------------+------+-----+---------+-------+
 2 rows in set (0.02 sec)
mysql> ALTER TABLE test_tuto
     -> CHANGE nom prenom VARCHAR(10) NOT NULL;
 Query OK, 0 rows affected (0.01 sec)
 Records: 0  Duplicates: 0  Warnings: 0
mysql> DESCRIBE test_tuto;
 +--------+-------------+------+-----+---------+-------+
 | Field  | Type        | Null | Key | Default | Extra |
 +--------+-------------+------+-----+---------+-------+
 | id     | int(11)     | NO   | PRI | NULL    |       |
 | prenom | varchar(10) | NO   |     | NULL    |       |
 +--------+-------------+------+-----+---------+-------+
 2 rows in set (0.02 sec)

Tables are fun, but where is the data?!

  • To insert rows in a table, we use the command
    • INSERT INTO table_name [(column1, column2, …)] VALUES (value1, value2, …);
  • If we don't specify to which columns we give a value, we have to give a value to all columns, and in the right order
  • It is possible to insert several rows at once, separating the lists of values by a comma
  • If we have a text file containing SQL queries, we can execute it using SOURCE filename; or . filename;
  • The LOAD DATA [LOCAL] INFILE command allows to load data into a table from a formatted file (.csv for example)
mysql> INSERT INTO Animal
     -> VALUES (1, 'chien', 'M', '2010-04-05 13:43:00', 'Rox', 'Mordille beaucoup');
 Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO Animal
     -> VALUES (2, 'chat', NULL, '2010-03-24 02:23:00', 'Roucky', NULL);
 Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO Animal
     -> VALUES (NULL, 'chat', 'F', '2010-09-13 15:02:00', 'Schtroumpfette', NULL);
 Query OK, 1 row affected (0.01 sec)
mysql> SHOW TABLES;
 +-------------------+
 | Tables_in_elevage |
 +-------------------+
 | animal            |
 | test_tuto         |
 +-------------------+
 2 rows in set (0.00 sec)
mysql> DESCRIBE Animal;
 +----------------+----------------------+------+-----+---------+----------------+
 | Field          | Type                 | Null | Key | Default | Extra          |
 +----------------+----------------------+------+-----+---------+----------------+
 | id             | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
 | espece         | varchar(40)          | NO   |     | NULL    |                |
 | sexe           | char(1)              | YES  |     | NULL    |                |
 | date_naissance | datetime             | NO   |     | NULL    |                |
 | nom            | varchar(30)          | YES  |     | NULL    |                |
 | commentaires   | text                 | YES  |     | NULL    |                |
 +----------------+----------------------+------+-----+---------+----------------+
 6 rows in set (0.03 sec)
mysql> SELECT * FROM Animal;
 +----+--------+------+---------------------+----------------+-------------------+
 | id | espece | sexe | date_naissance      | nom            | commentaires      |
 +----+--------+------+---------------------+----------------+-------------------+
 |  1 | chien  | M    | 2010-04-05 13:43:00 | Rox            | Mordille beaucoup |
 |  2 | chat   | NULL | 2010-03-24 02:23:00 | Roucky         | NULL              |
 |  3 | chat   | F    | 2010-09-13 15:02:00 | Schtroumpfette | NULL              |
 +----+--------+------+---------------------+----------------+-------------------+
 3 rows in set (0.00 sec)
mysql> INSERT INTO Animal (espece, sexe, date_naissance)
     -> VALUES ('tortue', 'F', '2009-08-03 05:12:00');
 Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO Animal (nom, commentaires, date_naissance, espece)
     -> VALUES ('Choupi', 'Né sans oreille gauche', '2010-10-03 16:44:00', 'chat');
 Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO Animal (espece, date_naissance, commentaires, nom, sexe)
     -> VALUES ('tortue', '2009-06-13 08:17:00', 'Carapace bizarre', 'Bobosse', 'F');
 Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO Animal (espece, sexe, date_naissance, nom)
     -> VALUES ('chien', 'F', '2008-12-06 05:18:00', 'Caroline'),
     -> ('chat', 'M', '2008-09-11 15:38:00', 'Bagherra'),
     -> ('tortue', NULL, '2010-08-23 05:18:00', NULL);
 Query OK, 3 rows affected (0.01 sec)
 Records: 3  Duplicates: 0  Warnings: 0

**Thoughts:**

I had studied SQL years ago and I had forgotten the design phase just to create a simple table in SQL.

The more I write SQL the more the syntax brings me back the memory of the first year of university.

For the first and only time in my life, I was questioning the passion I have for computer science.

Yet here we are, years later, and with even more desire to learn and at the end of an incredible journey that started a few months ago.

All this because of COVID-19.

I suggest to anyone who happens to pass by this blog and is reading these lines to immediately start the #100DaysOfCode challenge.

I've learned so much and put it into practice at a steady pace.

I studied everyday for a minimum of 2 hours using the Pomodoro Technique.

With the same emphasis I implore you for your sanity to NOT do as I did.

Blogging is an arduous and time-consuming endeavor.

And once you start, you won't want to break the chain.

Blogging everyday is very hard, especially if you, like me:

  • you have never written every day or published anything before;
  • English is not your native language;
  • you want to write something interesting or new every day;
  • you do a full time job.

I also use the spaced repetition technique to memorize concepts from previous days, so I ended up with a significant amount of work.

But at the end of the day, I knew that I wanted to write a post with some useful information for the Future Me.

I'll probably do the challenge again in a few weeks (months?) but this time I'll just tweet my progress and write summaries of my notes on this blog.

Click here to go to the beginning of my journey.

**Link to tweet:**

[twitter-follow screen_name='20Gabriele20']

February 2024
MTWTFSS
 1234
567891011
12131415161718
19202122232425
26272829 

Archives

Gabriele Ferro Home
Copyright © Ferro Gabriele. All rights reserved.