Programmer en Python 3 - Introduction à la base de données SQL

Une base de données est un ensemble cohérent de données structurées permettant d'accéder à celle-ci de manière rapide. À l'inverse d'un fichier texte, celle-ci est structurée et peut être utilisée par différents programmes ou systèmes en même temps.

Terminologie

Une base de données est composée d'entités, nommées également tables, contenant des informations les détaillants. Chaque table est composée d'attributs, nommés également champs ou colonnes décrivant ces entités. Chaque entité possède des enregistrements, elles sont décrites par les différents attributs. Chaque enregistrement doit être identifié par un attribut unique appelé clé primaire. Cette clé primaire peut être une information déjà présente dans la table et revêtant un caractère unique (N° de sécurité sociale, référence produit …). Si aucune entité ne peut être désignée comme clé primaire, l'utilisation d'un entier choisi arbitrairement pour jouer ce rôle est très fréquent. L'exemple ci-dessous permet d'illustrer ces termes.

Personnes

id	nom	prenom	adresse	codePostal	ville	← Les attributs
1	Martin	Jean	12 rue des mimosas	71400	Autun
2	Martin	Pauline	41 av. Charlemagne	83000	Toulon	← Un enregistrement
3	Maigret	Emmanuel	85 rue de la tour	71400	Autun
↑ Clé primaire

Les systèmes de gestion de base de données (SGBD)

Un SGBD est un logiciel permettant de gérer et stocker des bases de données. Il existe plusieurs SGBD disponibles sur plusieurs systèmes d'exploitation. Certains sont gratuits et d'autres nécessitent une licence payante pour fonctionner. Nous utiliserons dans ce cours deux SGBD gratuits :

MariaDB: Copie de MySQL, disponible gratuitement et sous licence libre (GPL). Le couple MariaDB et MySQL fait partie des SGBD les plus utilisés et les plus massivement déployés.
SQLite3: Il s'agit d'une bibliothèque écrite en C dans le domaine public permettant d'interagir avec des bases de données stockées dans des fichiers. À l'inverse de MariaDB, il ne repose pas sur une architecture client/serveur. Ce système est particulièrement adapté pour les petites bases de données stockées localement, comme alternative aux fichiers texte.

Les types de données

À l'instar des langages de programmation, les bases de données représentent les données avec des types de données. Le choix du type de données est primordial pour le bon fonctionnement du système. En effet, si le type de données est trop petit, une partie des données sera perdue. À l'inverse, si le type de données est trop grand, de l'espace de stockage disque sera gaspillée inutilement.

Nous présenterons des types de données pour MariaDB/MySQL et pour SQLite3. Ces deux systèmes partagent une même valeur : NULL qui signifie valeur vide. De plus, ces deux systèmes ne stockent pas les booléens. Pour y remédier, vous pouvez utiliser un type INT(1) pour MariaDB/MySQL ou INTEGER dans lequel la valeur faux serait stockée par le nombre 0 et vrai par le nombre 1.

Les types de données pour MariaDB/MySQL

Les données texte

Les données texte permettent de stocker des chaînes de caractères. Voici les différents types de données.

Type de données	Description
`CHAR(taille)`	Stocke un nombre fixe de caractères. La valeur prendra toujours la taille définie, même si la taille réelle est inférieure. Le maximum est de 255 caractères
`VARCHAR(taille)`	Stocke un nombre variable de caractères. La taille maximale est définie entre parenthèses. Le maximum est de 255 caractères. Au-delà, la valeur sera convertie en `TEXT`.
`TINYTEXT`	Stocke un texte de 255 caractères maximum.
`TEXT`	Stocke un texte de 65 535 caractères maximum.
`BLOB`	Stocke une donnée binaire brute de 65 535 octets maximum.
`MEDIUMTEXT`	Stocke un texte de 2²⁴-1 caractères maximum.
`MEDIUMBLOB`	Stocke une donnée binaire brute de 2²⁴-1 octets maximum.
`LONGTEXT`	Stocke un texte de 2³²-1 caractères maximum.
`LONGBLOB`	Stocke une donnée binaire brute de 2³²-1 octets maximum.

Les données numériques

L'ensemble des types de valeurs présentées ici peuvent être signés (UNSIGNED) ou non. Une valeur signée (par défaut) permet les valeurs négatives alors que les valeurs non signées non. Cependant, une valeur normale permettra des valeurs comprises entre la moitié de la borne non signée en négatif et en positif. Par exemple, pour les valeurs TINYINT, en fonctionnement normal, admet des valeurs entre -128 et 127 alors que la valeur non signée permet des valeurs entre 0 et 255. Voici la liste des données numériques

Type de données	Description
`TINYINT(taille)`	Stocke un entier entre -128 et 127 en normal ou entre 0 et 255 en non signé. La quantité de chiffre peut être donnée entre parenthèses.
`SMALLINT(taille)`	Stocke un entier entre -32 768 et 32 768 en normal ou entre 0 et 65 535 en non signé. La quantité de chiffre peut être donnée entre parenthèses.
`MEDIUMINT(taille)`	Stocke un entier entre -2²³ et 2²³-1 en normal ou entre 0 et 2²⁴ en non signé. La quantité de chiffre peut être donnée entre parenthèses.
`INT(taille)`	Stocke un entier entre -2³¹ et 2³¹-1 en normal ou entre 0 et 2³² en non signé. La quantité de chiffre peut être donnée entre parenthèses.
`BIGINT(taille)`	Stocke un entier entre -2⁶³ et 2⁶³-1 en normal ou entre 0 et 2⁶⁴ en non signé. La quantité de chiffre peut être donnée entre parenthèses.
`FLOAT(taille,d)`	Stocke un réel à virgule flottante de petite taille. La quantité de chiffres peut être donné entre parenthèses par le paramètre `taille`. Le nombre de décimales peut être donné entre parenthèses par le paramètre `d`.
`DOUBLE(taille,d)`	Stocke un réel à virgule flottante de grande taille. La quantité de chiffres peut être donné entre parenthèses par le paramètre `taille`. Le nombre de décimales peut être donné entre parenthèses par le paramètre `d`.
`DECIMAL(taille,d)`	Stocke une valeur `DOUBLE` dans une chaîne de caractères, acceptant une virgule fixe. La quantité de chiffres peut être donné entre parenthèses par le paramètre `taille`. Le nombre de décimales peut être donné entre parenthèses par le paramètre `d`.

Les données temporelles

Les types de données détaillés ici permettent de stocker une date et/ou une heure. Voici la liste des données temporelles.

Type de données	Description
`DATE`	Stocke une date au format `AAAA-MM-JJ` comprises entre `1000-01-01` et `9999-12-31`.
`DATETIME`	Stocke une date et une heure au format `AAAA-MM-JJ HH:MI:SS` comprises entre `1000-01-01 00:00:00` et `9999-12-31 23:59:59`.
`TIMESTAMP`	Stocke un horodatage (timestamp) au format UNIX, à savoir le nombre de secondes écoulées depuis le 1^er janvier 1970 à minuit (UTC) au format `AAAA-MM-JJ HH:MI:SS` comprises entre `1970-01-01 00:00:01` et `2038-01-09 03:14:07`.
`TIME`	Stocke une heure au format `HH:MI:SS` comprises entre `-838:59:59` et `838:59:59`.
`YEAR`	Stocke une année sur deux ou quatre chiffres. Les valeurs sur quatre chiffres sont comprises entre 1901 et 2155. Les valeurs sur deux chiffres sont comprises entre 70 et 69, soit les années 1970 et 2069.

Les types de données pour SQLite3

Les types de données offerts par SQLite3 sont plus simples que pour MariaDB/MySQL. Voici la liste des types de valeurs.

Type de données	Description
`INTEGER`	Stocke un entier signé sur 1, 2, 3, 4, 6 ou 8 octets, en fonction de la valeur.
`REAL`	Stocke une valeur réelle à virgule flottante sur 8 octets.
`TEXT`	Stocke une chaîne de caractères.
`BLOB`	Stocke une donnée binaire brute.

SQLite3 ne prend pas en charge le stockage des données temporelles. Pour stocker ce type de données, vous pouvez :

Utiliser une variable TEXT et stocker la date sous la forme AAAA-MM-JJ HH:MI:SS.
Utiliser une variable INTEGER et stocker la date sous la forme d'un horodatage (timestamp) UNIX, à savoir le nombre de secondes depuis le 1^er janvier 1970 à minuit (UTC).

Les clés primaires et étrangères

Pour que vos bases de données soient efficaces, il est conseillé de suivre les règles suivantes :

Ne stockez pas le résultat d'une opération, mais les paramètres de celle-ci car en cas de modification d'un paramètre, votre résultat devra être recalculé et stocké dans la base, ce qui peut amener une incohérence. Par exemple, il est plus judicieux de stocker la date de naissance d'une personne et non son âge car cette donnée devra être modifiée régulièrement pour être à jour.
Éviter les redondances d'informations au sein de vos tables car en cas de mise à jour de l'une de ces informations, toutes les copies de celle-ci devront être mises à jour et ceci peut être fastidieux et source d'erreurs.

Afin de résoudre le problème de cette dernière règle, nous faisons appel à des clés étrangères. Il s'agit d'une copie de la clé primaire de l'enregistrement que l'on souhaite lier.

Par exemple, voici une base de données pour gérer les emprunts dans une bibliothèque.

Emprunts

id	nom	prenom	refLivre	livre	auteur	dateEmprunt	dateRetour
1	Martin	Jean	ACL7463	Germinal	Émile Zola	16/03/2017
2	Duval	Marie	ACL7463	Germinal	Émile Zola	12/02/2017	26/02/2017
3	Martin	Jean	JBV1337	Le Cid	Corneille	30/11/2016	13/12/2016
4	Duval	Marie	ACL7463	Germinal	Émile Zola	28/06/2016	14/07/2016

On observe que des données sont répétées plusieurs fois dans cette table. Pour y remédier, nous allons la scinder en trois tables : Client stockant les données du client, Livres décrivant les informations sur le livre et Emprunts associant un client à un livre et donnant des informations sur l'emprunt.

Clients

id	nom	prenom
1	Martin	Jean
2	Duval	Marie

Livres

refLivre	livre	auteur
ACL7463	Germinal	Émile Zola
JBV1337	Le Cid	Corneille

Emprunts

id	idClient	refLivre	dateEmprunt	dateRetour
1	1	ACL7463	16/03/2017
2	2	ACL7463	12/02/2017	26/02/2017
3	1	JBV1337	30/11/2016	13/12/2016
4	2	ACL7463	28/06/2016	14/07/2016

Dans cet exemple, les champs idClient et refLivre sont des clés étrangères car elles font référence respectivement aux clés primaires des tables Clients et Livres.

Nous verrons plus tard comment reformer la table initiale via l'opération de jointure.

Modélisation des bases de données

Il existe diverses modélisations possibles pour les bases de données. Dans ce cours, nous utiliserons la modélisation UML. Chaque table est représentée par un rectangle divisée horizontalement en trois parties. La première partie comporte le nom de la table et la seconde comporte la liste des attributs et leurs types. Les clés primaires sont soulignées. La troisième partie reste vide dans les modèles de données. La figure suivante représente la table Personnes vue plus tôt.

La liaison entre deux tables, en utilisant le système de clés primaires et étrangères, se fait avec une flèche reliant les deux tables. Pour illustrer cela, nous allons reprendre comme exemple la base de données bibliothèque expliquée plus haut.

Pour plus de clarté, il est recommandé de positionner les liens en vis-à-vis des lignes décrivant les clés primaires et étrangères.

Les opérations `SHOW`, `DESC` et `USE`

Toute commande SQL doit se terminer par un point-virgule (;)

Avant toute chose, nous allons parler de l'interpréteur de commandes MariaDB :

MariaDB [(none)]>

Cette ligne indique que l'interpréteur est prêt à exécuter des commandes SQL. Le contenu des crochets (ici (none)) indique sur quelle base de données l'interpréteur va travailler (ici, aucune).

Une fois connectés au SGBD, nous allons tout d'abord voir comment naviguer dans les différentes bases et tables présentes dans celui-ci.

Un commentaire SQL est commencé par deux tirets : --.

Sélectionner une base de données

Nous allons sélectionner notre base de données sur laquelle nous travaillerons. Pour cela, nous utiliserons la commande USE :

MariaDB [(none)]> USE Bibliotheque;
Database changed
MariaDB [Bibliotheque]>

Afficher des informations, la commande `SHOW`

La commande SHOW permet d'afficher la liste des éléments demandés.

Afficher les base de données

Vous pouvez afficher la liste des bases de données présentes avec SHOW DATABASES :

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| bibliotheque       |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.05 sec)

Nous obtenons plusieurs informations :

Le tableau nous renvoie l'information demandée (ici, la liste des bases de données).
La dernière ligne nous indique le nombre d'enregistrements traités par cette opération (ici, quatre enregistrements) .
La dernière ligne indique également le temps nécessaire à l'exécution de l'opération (ici 0,05 secondes).

Afficher les tables d'une base de données

Une fois votre base de données sélectionnée, vous pouvez afficher les tables d'une base de données avec SHOW TABLES :

MariaDB [Bibliotheque]> SHOW TABLES;
+------------------------+
| Tables_in_Bibliotheque |
+------------------------+
| Clients                |
| Emprunts               |
| Livres                 |
+------------------------+
3 rows in set (0.00 sec)

On retrouve nos trois tables décrites plus haut.

Afficher les attributs d'une table

Nous utiliserons la commande DESC pour détailler la structure de la table :

MariaDB [Bibliotheque]> DESC Emprunts;
+-------------+------------------+------+-----+------------+----------------+
| Field       | Type             | Null | Key | Default    | Extra          |
+-------------+------------------+------+-----+------------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL       | auto_increment |
| idClient    | int(10) unsigned | NO   | MUL | NULL       |                |
| refLivre    | char(7)          | NO   | MUL | NULL       |                |
| dateEmprunt | date             | NO   |     | NULL       |                |
| dateRetour  | date             | YES  |     | 0000-00-00 |                |
+-------------+------------------+------+-----+------------+----------------+
5 rows in set (0.00 sec)

On obtiens un tableau avec les colonnes suivantes :

Field: Le nom du champ
Type: Le type de ce champ
Null: Affiche si le champ admet les valeurs nulles ou non.
Key: Affiche si le champ est une clé primaire (PRI) ou étrangère (MUL).
Default: Affiche la valeur par défaut de l'attribut.
Extra: Affiche des informations supplémentaires sur le champ telles que l'auto-incrémentation.

Les instructions du langage de définition de données (LDD)

Ces opérations permettent de manipuler les structures de données et non les données elles-mêmes.

L'opération `CREATE`

Créer une base de données

Pour créer une base de données, utilisez la commande CREATE DATABASE :

CREATE DATABASE Bibliotheque;

Créer une table

Pour créer une base de données, utilisez la commande CREATE TABLE. Voici un exemple avec la table Emprunts :

CREATE TABLE Bibliotheque.Emprunts (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  idClient INT UNSIGNED NOT NULL,
  refLivre CHAR(7) NOT NULL,
  dateEmprunt DATE NOT NULL,
  dateRetour DATE DEFAULT '0000-00-00 00:00:00',
  FOREIGN KEY (idClient) REFERENCES Bibliotheque.Clients(id),
  FOREIGN KEY (refLivre) REFERENCES Bibliotheque.Livres(refLivre)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Nous allons détailler cette instruction :

Nous commençons l'instruction par CREATE TABLE, suivie du nom de la base de données, d'un point et du nom de la table. Elle se termine par une parenthèse ouvrante.
Chaque ligne déclare un attribut et se termine par une virgule (sauf la dernière ligne de déclaration des attributs).
La clé primaire est désignée avec le mot clé PRIMARY KEY.
Les valeurs non signées (valeurs positives uniquement) sont accompagnées de l'instruction UNSIGNED.
Si une valeur doit être absolument remplie, à savoir qu'elle ne peut admettre de valeur nulle, on ajoute l'instruction NOT NULL.
On demande à la valeur de s'incrémenter automatiquement avec AUTO_INCREMENT pour MariaDB et AUTOINCREMENT pour SQLite.
La déclaration des clés étrangères se fait avec l'instruction FOREIGN KEY (cleEtrangere) REFERENCES Base.Table(clePrimaire).
Il est possible de définir une valeur par défaut pour un attribut avec le mot-clé DEFAULT.
Pour MariaDB : Une fois les attributs et les contraintes déclarés, on referme la parenthèse, puis on définit le moteur de base de données (ici InnoDB, par défaut) puis l'encodage (ici UTF8, recommandé). On termine la ligne par un point-virgule.

Créer un index

La création d'un index se fait avec la commande CREATE INDEX. Un index permet d'accélérer les opérations sur les grandes tables. Voici comment créer un index :

CREATE INDEX indexEmprunts ON Emprunts;

Il est également possible de le faire sur un ou plusieurs attributs :

CREATE INDEX indexEmprunts ON Emprunts (refLivre, idClient);

L'opération `ALTER`

La commande ALTER permet d'apporter des modifications à la table une fois celle-ci créée. Sa syntaxe est ALTER TABLE nomTable instruction.

Ajouter un attribut

Pour ajouter une colonne, nous procédons comme suit :

ALTER TABLE Bibliotheque.Livres ADD isbn CHAR(13) NOT NULL;

Modifier un attribut

Il est possible par cette commande de changer le type de données pour un attribut :

ALTER TABLE Bibliotheque.Livres MODIFY livre TEXT;

Renommer un attribut

Voici comment renommer un attribut dans une table :

ALTER TABLE Bibliotheque.Livres CHANGE livre titreLivre;

Dans l'exemple ci-dessus, on renomme l'attribut livre en titreLivre dans la table Livres.

Supprimer un attribut

Il est possible par cette commande de supprimer un attribut :

ALTER TABLE Bibliotheque.Livres DROP isbn;

L'opération `DROP`

Cette opération permet de supprimer un élément de structure dans un SGBD. ATTENTION : toute suppression est définitive.

Supprimer une table

Pour supprimer une table, utilisez la commande DROP TABLE :

DROP TABLE Bibliotheque.Emprunts;

Supprimer une base de données

Pour supprimer une base de données, utilisez la commande DROP DATABASE :

DROP DATABASE Bibliotheque;

Les instructions du langage de manipulation de données (LMD)

Ces opérations permettent de manipuler les données d'une base de données.

L'opération `INSERT`

La commande INSERT permet d'ajouter des enregistrements dans une table. Il est possible d'effectuer cet ajout en renseignant tous les attributs, dans l'ordre de leur déclaration lors de la création de la table. Il est également possible de spécifier quelles colonnes seront renseignées.

Nous allons voir la première méthode, à savoir en renseignant tous les attributs :

INSERT INTO Bibliotheque.Livres VALUES ("MAP112", "Rhinocéros", "Eugène Ionesco"); --refLivre, livre et auteur

Il est possible de ne spécifier que quelques attributs en les désignant :

INSERT INTO Bibliotheque.Emprunts (idClient, refLivre, dateEmprunt) VALUES (2, "MAP112", "2017-03-01");

Dans l'exemple ci-dessus, on ne renseigne pas le champ id car ce champ s'incrémente automatiquement. On ne renseigne pas non plus le champ dateRetour car le client n'a pas encore retourné le livre.

Il est possible de renseigner plusieurs lignes à la fois :

INSERT INTO Bibliotheque.Livres VALUES
("ALC103", "20000 lieues sous les mers", "Jules Verne"),
("GTR089", "Le Petit Prince", "Antoine de Saint Exupéry"),
("EIF012", "Les Misérables", "Victor Hugo"),
("NNA2104", "Thérèse Raquin", "Émile Zola");

L'opération `UPDATE`

La commande UPDATE permet de modifier des enregistrements. Il est possible d'effectuer des modifications sur certaines lignes avec l'instruction WHERE ou sur l'ensemble des enregistrements de la table. Voici un exemple :

UPDATE Bibliotheque.Emprunts SET dateRetour='2017-03-05' WHERE id=1;

L'exemple ci-dessus permet de modifier la valeur de l'attribut dateRetour en lui attribuant la valeur 2017-03-05 aux enregistrements de la table Emprunts dont le champ id vaut 1.

Considérons à présent l'instruction suivante :

UPDATE Bibliotheque.Emprunts SET dateRetour='2017-03-05', refLivre="MAP112";

Cette instruction modifie la valeur de l'attribut dateRetour en lui attribuant la valeur 2017-03-05, ainsi que l'attribut refLivre en lui attribuant la valeur MAP112 à tous les enregistrements de la table.

L'opération `DELETE`

La commande DELETE permet de supprimer des enregistrements. À l'instar de la commande UPDATE, l'instruction WHERE permet d'appliquer la suppression uniquement aux enregistrements remplissant une condition.

DELETE FROM Bibliotheque.Emprunts WHERE id=1;

La commande précédente permet de supprimer les enregistrements de la table Emprunts dont le champ id vaut 1.

L'absence de la clause WHERE supprime l'intégralité des enregistrements de la table. Voici un exemple :

DELETE FROM Bibliotheque.Emprunts;

L'opération `SELECT`

L'opération SELECT permet d'effectuer des recherches dans les données de la base. Il s'agit de la commande la plus courante. Dans l'exemple suivant, nous allons simplement afficher toutes les colonnes de la table Livres :

MariaDB [Bibliotheque]> SELECT * FROM Bibliotheque.Livres;
+----------+----------------------------+---------------------------+
| refLivre | livre                      | auteur                    |
+----------+----------------------------+---------------------------+
| ACL7463  | Germinal                   | Émile Zola                |
| ALC103   | 20000 lieues sous les mers | Jules Verne               |
| EIF012   | Les Misérables             | Victor Hugo               |
| GTR089   | Le Petit Prince            | Antoine de Saint Exupéry  |
| JBV1337  | Le Cid                     | Corneille                 |
| MAP112   | Rhinocéros                 | Eugène Ionesco            |
| NNA2104  | Thérèse Raquin             | Émile Zola                |
+----------+----------------------------+---------------------------+
7 rows in set (0.00 sec)

L'astérisque (*) permet d'afficher toutes les colonnes de la table. Il est cependant possible de spécifier les attributs que l'on souhaite voir apparaître dans le résultat :

MariaDB [Bibliotheque]> SELECT refLivre, livre FROM Bibliotheque.Livres;
+----------+----------------------------+
| refLivre | livre                      |
+----------+----------------------------+
| ACL7463  | Germinal                   |
| ALC103   | 20000 lieues sous les mers |
| EIF012   | Les Misérables             |
| GTR089   | Le Petit Prince            |
| JBV1337  | Le Cid                     |
| MAP112   | Rhinocéros                 |
| NNA2104  | Thérèse Raquin             |
+----------+----------------------------+
7 rows in set (0.00 sec)

Créer un alias avec `AS`

Pour simplifier la lecture du résultat ou pour raccourcir l'écriture d'une requête, il est possible d'affecter temporairement un alias à une table ou à un attribut avec la commande AS dont en voici un exemple :

MariaDB [Bibliotheque]> SELECT refLivre, livre AS `c1` FROM Bibliotheque.Livres AS t1;
+----------+----------------------------+
| refLivre | c1                         |
+----------+----------------------------+
| ACL7463  | Germinal                   |
| ALC103   | 20000 lieues sous les mers |
| EIF012   | Les Misérables             |
| GTR089   | Le Petit Prince            |
| JBV1337  | Le Cid                     |
| MAP112   | Rhinocéros                 |
| NNA2104  | Thérèse Raquin             |
+----------+----------------------------+
7 rows in set (0.00 sec)

Dans l'exemple précédent, on observe que le nom de l'alias est entre backquote (symbole `, obtenu avec la combinaison des touches Alt Gr + 7), ce qui permet de délimiter le nom du champ. Ceci est obligatoire lorsque celui-ci comporte un espace. La colonne livre est renommée en c1 et la table Livres en t1. Cette dernière opération s'avérera utile lors des jointures.

Effectuer une jointure avec `JOIN`

Les jointures permettent d'utiliser plusieurs tables dans la même requête. C'est avec cette opération que l'on tire parti des relations de clés primaires et étrangères car on reconstruit la table originale.

Il existe plusieurs types de jointures, mais nous n'aborderons que la forme la plus courante, l'intersection de deux tables ou INNER JOIN. Cette opération permet de retenir les enregistrements des deux tables remplissant la condition. On obtient l'intersection des deux ensembles.

Nous allons illustrer cela en affichant les emprunts dans la bibliothèque et en mettant en vis-à-vis le titre des œuvres :

MariaDB [Bibliotheque]> SELECT Emprunts.id, Emprunts.dateEmprunt, Emprunts.idClient, Livres.livre, Livres.auteur FROM Emprunts INNER JOIN Livres on Emprunts.refLivre = Livres.refLivre;
+----+-------------+----------+-------------+-----------------+
| id | dateEmprunt | idClient | livre       | auteur          |
+----+-------------+----------+-------------+-----------------+
|  1 | 2017-03-16  |        1 | Germinal    | Émile Zola      |
|  2 | 2017-02-12  |        2 | Germinal    | Émile Zola      |
|  3 | 2016-11-30  |        1 | Le Cid      | Corneille       |
|  4 | 2016-06-28  |        2 | Germinal    | Émile Zola      |
|  5 | 2017-03-01  |        2 | Rhinocéros  | Eugène Ionesco  |
+----+-------------+----------+-------------+-----------------+
5 rows in set (0.00 sec)

On peut chaîner cette opération : nous allons ici remplacer le numéro de client par le nom et le prénom des emprunteurs :

MariaDB [Bibliotheque]> SELECT e.id, e.dateEmprunt, c.nom, c.prenom, l.livre, l.auteur FROM Emprunts AS e INNER JOIN Livres AS l on e.refLivre = l.refLivre INNER JOIN Clients AS c ON e.idClient = c.id;
+----+-------------+--------+--------+-------------+-----------------+
| id | dateEmprunt | nom    | prenom | livre       | auteur          |
+----+-------------+--------+--------+-------------+-----------------+
|  1 | 2017-03-16  | Martin | Jean   | Germinal    | Émile Zola      |
|  2 | 2017-02-12  | Duval  | Marie  | Germinal    | Émile Zola      |
|  3 | 2016-11-30  | Martin | Jean   | Le Cid      | Corneille       |
|  4 | 2016-06-28  | Duval  | Marie  | Germinal    | Émile Zola      |
|  5 | 2017-03-01  | Duval  | Marie  | Rhinocéros  | Eugène Ionesco  |
+----+-------------+--------+--------+-------------+-----------------+
5 rows in set (0.00 sec)

Pour lever toute ambiguïté lors de la désignation des attributs, on nomme ceux-ci de la forme suivante : table.attribut. Vous remarquerez dans l'exemple ci-dessus une utilisation astucieuse des alias qui permet de réduire la longueur de la requête.

Sélectionner des enregistrements avec `WHERE`

L'instruction WHERE, qui peut être utilisée avec des instructions telles que SELECT, DELETE ou encore UPDATE, fournit un vaste champ de possibilités pour effectuer des comparaisons. Voici une liste des opérateurs de comparaison couramment utilisés.

Comparateur	Syntaxe
a égal à b	`a = b`
a différent de b	`a <> b` ou `a != b`
a supérieur à b	`a > b`
a supérieur ou égal à b	`a >= b`
a inférieur à b	`a < b`
a inférieur ou égal à b	`a <= b`
a dans b	`a IN b`
a entre b et c	`a BETWEEN b AND c`
a dans b (selon le modèle)	`a LIKE b`
a est nul	`a IS NULL`
a n'est pas nul	`a IS NOT NULL`

Nous allons revenir sur trois comparateurs : IN, BETWEEN et LIKE.

Le comparateur IN permet de vérifier si une valeur est dans un ensemble de données. Cela peut être une liste de données. On affiche ici les informations à propos des livres Germinal et Le Cid :

SELECT * FROM Livres WHERE livre IN ("Germinal", "Le Cid");

Cette liste de données peut être le résultat d'une sous-requête. On recherche ici les livres empruntés après le 1^er janvier 2017. :

SELECT * FROM Livres WHERE refLivre IN (SELECT refLivre FROM Emprunts WHERE dateEmprunt > "2017-01-01 00:00:00");

L'opérateur BETWEEN permet de vérifier si la donnée est comprise entre deux bornes. Cette opération est possible pour les dates et les nombres. L'exemple suivant montre les emprunts souscrits en 2016 :

SELECT * FROM Emprunts WHERE dateEmprunt BETWEEN "2016-01-01 00:00:00" AND "2016-12-31 23:59:59";

Enfin, l'opérateur LIKE permet de vérifier si la valeur correspond à un modèle. Le caractère % permet de remplacer un ou plusieurs caractères. Le caractère _ permet de remplacer un seul caractère. Voici quelques exemples.

Modèle	Description
`%gne`	Une chaîne se terminant par `gne`.
`Mon%`	Une chaîne commençant par `Mon`.
`%ta%`	Une chaîne contenant `ta`.
`Bo_`	Une chaîne de trois lettres commençant par `Bo` (exemple : `Boa` ou `Bob`).

Voici une application du comparateur LIKE dans laquelle on recherche les livres dont le titre se termine par mers :

MariaDB [Bibliotheque]> SELECT * FROM Livres WHERE livre LIKE "%mers";
+----------+----------------------------+-------------+
| refLivre | livre                      | auteur      |
+----------+----------------------------+-------------+
| ALC103   | 20000 lieues sous les mers | Jules Verne |
+----------+----------------------------+-------------+
1 row in set (0.00 sec)

Coupler les conditions avec `AND` et `OR`

Il est possible de combiner des conditions avec AND (et) et OR (ou). Dans l'exemple suivant, on affiche les livres écrits par Émile Zola et empruntés après le 1^er janvier 2017.

MariaDB [Bibliotheque]> SELECT l.refLivre FROM Livres AS l JOIN Emprunts AS e ON l.refLivre = e.refLivre WHERE l.auteur = "Émile Zola" AND e.dateEmprunt > "2017-01-01 00:00:00";
+----------+
| refLivre |
+----------+
| ACL7463  |
| ACL7463  |
+----------+
2 rows in set (0.00 sec)

Organiser les résultats avec `GROUP BY`, `ORDER BY`, `LIMIT` et `DESC`

La commande ORDER BY permet de trier les résultats selon un attribut. Nous allons travailler avec la table Livres. La commande suivante affiche les livres triés par auteur :

MariaDB [Bibliotheque]> SELECT * FROM Livres ORDER BY auteur;
+----------+----------------------------+---------------------------+
| refLivre | livre                      | auteur                    |
+----------+----------------------------+---------------------------+
| GTR089   | Le Petit Prince            | Antoine de Saint Exupéry  |
| JBV1337  | Le Cid                     | Corneille                 |
| ACL7463  | Germinal                   | Émile Zola                |
| NNA2104  | Thérèse Raquin             | Émile Zola                |
| MAP112   | Rhinocéros                 | Eugène Ionesco            |
| ALC103   | 20000 lieues sous les mers | Jules Verne               |
| EIF012   | Les Misérables             | Victor Hugo               |
+----------+----------------------------+---------------------------+
7 rows in set (0.00 sec)

On peut inverser le tri avec la commande DESC :

MariaDB [Bibliotheque]> SELECT * FROM Livres ORDER BY auteur DESC;
+----------+----------------------------+---------------------------+
| refLivre | livre                      | auteur                    |
+----------+----------------------------+---------------------------+
| EIF012   | Les Misérables             | Victor Hugo               |
| ALC103   | 20000 lieues sous les mers | Jules Verne               |
| MAP112   | Rhinocéros                 | Eugène Ionesco            |
| ACL7463  | Germinal                   | Émile Zola                |
| NNA2104  | Thérèse Raquin             | Émile Zola                |
| JBV1337  | Le Cid                     | Corneille                 |
| GTR089   | Le Petit Prince            | Antoine de Saint Exupéry  |
+----------+----------------------------+---------------------------+
7 rows in set (0.00 sec)

Il est possible de limiter le nombre de lignes lors de l'affichage des résultats avec la commande LIMIT. Notre exemple affiche les trois derniers emprunts souscrits :

MariaDB [Bibliotheque]> SELECT * FROM Emprunts ORDER BY dateEmprunt DESC LIMIT 3;
+----+----------+----------+-------------+------------+
| id | idClient | refLivre | dateEmprunt | dateRetour |
+----+----------+----------+-------------+------------+
|  1 |        1 | ACL7463  | 2017-03-16  | 0000-00-00 |
|  5 |        2 | MAP112   | 2017-03-01  | 0000-00-00 |
|  2 |        2 | ACL7463  | 2017-02-12  | 2017-02-26 |
+----+----------+----------+-------------+------------+
3 rows in set (0.00 sec)

Enfin, pour travailler avec la commande GROUP BY, nous allons utiliser une autre table : la table Produits. Voici cette nouvelle table :

MariaDB [Bibliotheque]> SELECT * FROM Produits;
+----+----------------------+--------------------+------+
| id | produit              | categorie          | prix |
+----+----------------------+--------------------+------+
|  1 | Fromage frais        | Crèmerie           | 1.53 |
|  2 | Crème semi-épaisse   | Crèmerie           |  3.3 |
|  3 | Emmental râpé        | Crèmerie           | 1.44 |
|  4 | Comté 10 mois        | Crèmerie           | 4.29 |
|  5 | Beurre               | Crèmerie           | 1.31 |
|  6 | Raviolis frais       | Traiteur           | 1.59 |
|  7 | Gnocchi              | Traiteur           | 1.09 |
|  8 | Shampooing           | Hygiène            | 1.83 |
|  9 | Courgette            | Fruits et légumes  | 2.59 |
| 10 | Poivron rouge        | Fruits et légumes  | 1.19 |
| 11 | Banane               | Fruits et légumes  | 1.59 |
+----+----------------------+--------------------+------+
11 rows in set (0.00 sec)

La commande suivante, utilisant l'instruction GROUP BY permet d'afficher le sous-total par catégorie :

MariaDB [Bibliotheque]> SELECT categorie, SUM(prix) FROM Produits GROUP BY categorie;
+--------------------+--------------------+
| categorie          | SUM(prix)          |
+--------------------+--------------------+
| Crèmerie           | 11.869999885559082 |
| Fruits et légumes  |  5.370000004768372 |
| Hygiène            | 1.8300000429153442 |
| Traiteur           |  2.680000066757202 |
+--------------------+--------------------+
4 rows in set (0.01 sec)

Nous aborderons plus tard certaines fonctions SQL.

Les instructions du langage de contrôle de données (LCD)

Le langage de contrôle de données permet de contrôler l'accès des utilisateurs aux données.

Les opérations `CREATE USER` et `DROP USER`

Pour plus de sécurité, il est recommandé de cloisonner les différentes applications utilisant les bases de données dans différents utilisateurs et leur attribuer uniquement les droits nécessaires. Nous allons voir comment créer un utilisateur :

CREATE USER 'alain'@'localhost' IDENTIFIED BY 'mot_de_passe';

Voici comment supprimer un utilisateur :

DROP USER 'alain'@'localhost';

L'opération `GRANT`

L'opération GRANT permet d'accorder des droits à l'utilisateur :

GRANT SELECT, INSERT ON Bibliotheque.* TO "alain"@"localhost";

Voici la liste de certaines autorisations possibles.

Commande	Description
`ALL`	Donne toutes les autorisations ci-dessous.
`ALTER`	Autorise la commande `ALTER TABLE`.
`CREATE`	Autorise la commande `CREATE TABLE`.
`DELETE`	Autorise la commande `DELETE`.
`DROP`	Autorise la commande `DROP TABLE`.
`INDEX`	Autorise la gestion des index.
`INSERT`	Autorise la commande `INSERT`.
`SELECT`	Autorise la commande `SELECT`.
`SHOW DATABASES`	Autorise la commande `SHOW DATABASES`.
`UPDATE`	Autorise la commande `UPDATE`.

L'opération `REVOKE`

À l'inverse, la commande REVOKE permet de retirer les autorisations conférées à un utilisateur :

REVOKE SELECT, INSERT ON Bibliotheque.* TO "alain"@"localhost";

Les fonctions SQL usuelles

Les fonctions SQL permettent d'effectuer des traitements sur les données. Voici une liste des fonctions les plus courantes.

Fonction	Description
`SUM(valeurs)`	Renvoie la somme des valeurs.
`MAX(valeurs)`	Renvoie le maximum des valeurs.
`MIN(valeurs)`	Renvoie le minimum des valeurs.
`AVG(valeurs)`	Renvoie la moyenne de valeurs.
`COUNT(valeurs)`	Renvoie le nombre de valeurs.
`ROUND(valeur)`	Renvoie un arrondi de la valeur.
`UPPER(texte)`	Renvoie le texte en caractères majuscules.
`LOWER(texte)`	Renvoie le texte en caractères minuscules.
`NOW()`	Renvoie la date et l'heure actuelles.
`RAND()`	Renvoie une valeur aléatoire.
`SUBSTR(colonne, a, b)`	Renvoie la colonne tronquée à partir du `a`^ème caractère sur `b` caractères.

Revenir au chapitre "Interagir avec les bases de données"

Introduction à la base de données SQL