Aller au contenu

Langage SQL⚓︎

Contenus Capacités
Attendues		 Commentaires
Langage SQL : requêtes
d’interrogation et de mise à jour
d’une base de données.		 Identifier les composants d’une requête.
Construire des requêtes
d’interrogation à l’aide des clauses
du langage SQL : SELECT, FROM,
WHERE, JOIN.
Construire des requêtes d’insertion
et de mise à jour à l’aide de :
UPDATE, INSERT, DELETE.		 On peut utiliser DISTINCT,
ORDER BY ou les fonctions
d’agrégation sans utiliser les
clauses GROUP BY et HAVING.

Histoire⚓︎

Développé par IBM dans les années \(1970\), le langage SQL, pour Structured Query Language 🇬🇧 ou Langage de Requête Structurée 🇫🇷, devient rapidement le standard des langages de bases de données relationnelles. Il permet les opérations usuelles (CRUD: insertion, lecture, mise à jour, suppression) sur les bases de données, avec un nombre minimaliste d'instructions, dont la syntaxe est (de plus) proche de la syntaxe anglaise.

Installation & Configuration⚓︎

Pour pouvoir utiliser le langage SQL, il faut avoir installé (et configuré... au moins un peu..):

  • un serveur physique pour héberger les données
  • un SGBD pour la manipuler :
  • En lignes de commandes: Sqlite, MySQL, PostgreSQL, etc... sont des solutions possibles, Open Source et Gratuite,
  • Interfaces Graphiques autorisant les requêtes SQL : PhpMyAdmin (en PHP, installé au-dessus du serveur Apache: WAMP/LAMP/MAMP), Sqlite Manager est un plugin Firefox pour SQLite

Requêtes SQL⚓︎

Requête

Une requête est une question sur les données posée au serveur SQL, qui renvoie donc comme réponse un jeu de résultats sous forme de table/entité.

Dans tout ce qui suit :

  • On suppose que l'un des SGBD est installé (MySQL, MariaDB, PostgreSQL, SQLite, etc..) , et configuré.
  • éventuellement (si besoin est) que le serveur/démon de bases de données ait été lancé sur la machine
  • on va détailler quelques requêtes classiques nous utiliserons MariaDB (i.e. MySQL)

Types de Données MySQL⚓︎

Les Types (de Données) suivants sont disponibles dans MySQL:

Numérique : Entiers⚓︎

Type Nombre d'Octets Minimum Maximum
TINYINT 1 -128 127
SMALLINT 2 -32768 32767
MEDIUMINT 3 -8388608 8388607
INT 4 -2147483648 2147483647
BIGINT 8 -9223372036854775808 9223372036854775807

Remarques

  • INT(x) : permet de préciser le nombre x de chiffres minimum à l'affichage d'une colonne de type INT (ou un de ses dérivés)
  • ZEROFILL, cumulé avec INT(x), ajoute autant de zéros à gauche que nécessaire, lors de l'affichage du nombre
  • L'attribut UNSIGNED permet de préciser que l'entier est non signé c'est à dire positif : Dans ce cas, la longueur de l'intervalle reste la même, mais les valeurs possibles sont décalées, le minimum valant 0. Ex: UNSIGNED TINYINT s'étale de 0 à 255.

Numérique : Flottants⚓︎

Type Syntaxe Valeur Exacte
vs Valeur Approchée
DECIMAL
\(=\)NUMERIC		 DECIMAL(n,p)
affiche le nombre avec
n chiffres significatifs au maximum
dont p sont après la virgule
DECIMAL(n) = DECIMAL(n,0)		 stocké comme chaîne de caractère
donc valeur exacte
FLOAT FLOAT stocké sur \(4\) octets
FLOAT(n,p) comme DECIMAL		 stocké comme nombre
donc valeur approchée
REAL REAL ^
DOUBLE DOUBLE : stocké sur \(8\) octets ^

Chaînes de Caractères de Type Texte⚓︎

Type Syntaxe Taille Maximale Stockage Mémoire
CHAR CHAR(x)
contient jusqu'à x caractères		 \(255\) octets taille fixe : stocké toujours exactement sur x caractères,
quitte à compléter avec des espaces
(x entre \(1\) et \(255\))
VARCHAR VARCHAR(x)
contient jusqu'à x caractères		 \(255\) octets taille variable : (de \(0\)) jusqu'à x caractères
(x entre \(1\) et \(255\))
TINYTEXT TINYTEXT \(255\) octets Longueur Chaîne\(+1\) octets
TEXT TEXT \(2^{16}\) octets Longueur Chaîne\(+2\) octets
MEDIUMTEXT MEDIUMTEXT \(2^{24}\) octets Longueur Chaîne\(+3\) octets
LONGTEXT LONGTEXT \(2^{32}\) octets Longueur Chaîne\(+4\) octets

Chaînes de Caractères de Type Binaire⚓︎

De même que les chaînes de type texte, une chaîne binaire n'est rien d'autre qu'une suite de caractères.

Néanmoins, si les textes sont affectés par l'encodage et l'interclassement, ce n'est pas le cas des chaînes binaires. Une chaîne binaire n'est rien d'autre qu'une suite d'octets. Aucune interprétation n'est faite sur ces octets, en particulier :

  • Une chaîne binaire traite directement l'octet, et pas le caractère que l'octet représente.
  • Tous les caractères sont utilisables, y compris les fameux caractères de contrôle non affichables définis dans la table ASCII.

Type Syntaxe Taille Maximale Stockage Mémoire
BINARY BINARY(x)
contient jusqu'à x caractères binaires		 \(255\) octets taille fixe : stocké toujours exactement sur x caractères binaires,
quitte à compléter avec des espaces
(x entre \(1\) et \(255\))
VARBINARY VARBINARY(x)
contient jusqu'à x caractères binaires		 \(255\) octets taille variable : (de \(0\)) jusqu'à x caractères
(x entre \(1\) et \(255\))
TINYBLOB TINYBLOB \(255\) octets Longueur Chaîne\(+1\) octets
BLOB BLOB \(2^{16}\) octets Longueur Chaîne\(+2\) octets
MEDIUMBLOB MEDIUMBLOB \(2^{24}\) octets Longueur Chaîne\(+3\) octets
LONGBLOB LONGBLOB \(2^{32}\) octets Longueur Chaîne\(+4\) octets

SET et ENUM⚓︎

SET et ENUM permettent de définir un certain nombre de valeurs autorisées de type chaînes de caractères. SET et ENUM sont des types spécifiques à MySQL : c'est donc une très mauvaise idée de les utiliser, sauf si vous êtes vraiement certain de ne jamais changer de SGBD.

SET("Fraises", "Framboises", "Mangue")
ENUM("Fraises", "Framboises", "Mangue")

Heures et Dates⚓︎

Type Signification Format Exemples
DATE Sert à stocker une Date Chaîne de caractères
ou Nombre		 par défaut 'AAAA-MM-JJ'
'AAMMJJ'
'AA/MM/JJ'
'AA+MM+JJ'
'AAAA%MM%JJ'
AAAAMMJJ (Nombre)
AAMMJJ (Nombre)
TIME Sert à stocker une Heure Chaîne de caractères
ou Nombre		 par défaut 'HH:MM:SS'
'HHH:MM:SS'
'MM+SS'
'J HH:MM:SS'
'HHMMSS'
HHMMSS (Nombre)
DATETIME Sert à stocker une Date et une Heure Chaîne de caractères
ou Nombre		 par défaut 'AAAA-MM-JJ HH:MM:SS'
'AA*MM*JJ HH+MM+SS'
AAAAMMJJHHMMSS (Nombre)
YEAR Sert à stocker une Année Chaîne de caractères
ou Nombre
sur \(1\) octet :
entre \(1901\) et \(2155\)		 'YY' (Chaîne de Caractère)
Ex : '00' \(=2000\)
YY (Nombre)
Ex : par défaut 00 \(=0000\)
TIMESTAMP Nombre de Secondes écoulées
depuis le \(1\)er Janvier \(1970\)
\(0\) h \(0\) min \(0\) s (TUC)		 TIMESTAMP (Nombre) sur \(4\) octets :
Date Maximale :
\(19\) janvier \(2038\) à \(3\) h \(14\) min \(7\) s		 Si la date est
\(2021-05-30\) \(19:25:00\)
le nombre de secondes écoulées depuis \(1970\) est
\(589067407500\)
néanmoins le TIMESTAMP stocké
n'est PAS \(589067407500\),
mais bien \(20210530192500\)

NULL vs NOT NULL⚓︎

De nombreux types de Données de MySQL :

  • autorisent la valeur NULL \(=\) absence de valeur
  • Pire : En cas d'absence de valeur d'un champ/attribut lors de la création d'un nouvel l'enregistrement, le plus probable/fréquent est que ce soit la valeur NULL qui lui soit attribuée par défaut. Cela peut être ce que l'on souhaite, mais comment empêcher un champ/attribut de prendre la valeur NULL? en spécifiant, lors de la création de la table, que ce champ ne peut pas prendre la valeur NULL, en précisant que ce champ doit être : NOT NULL

AUTO_INCREMENT, PRIMARY KEY et FOREIGN KEY⚓︎

Déclaration/Statement Signification
AUTO_INCREMENT incrémentation automatique du champ/attribut
PRIMARY KEY Clé Primaire
FOREIGN KEY [id_name] (nom_col,..)
REFERENCES nom_table (nom_col,..		 Clé Étrangère

Voir des exemples de Syntaxe dans : Création de Tables

Gestion de Bases de Données⚓︎

SHOW DATABASES : Affiche les Bases de Données⚓︎

> SHOW DATABASES;

classDiagram class Database { information_schema mysql performance_schema }
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| phpmyadmin         |
+--------------------+
4 rows in set (0.001 sec)

CREATE DATABASE : Crée une Base de Données⚓︎

Opération à Faire seulement la toute première fois :

# l option facultative IF NOT EXISTS (ne pas écrire les [])
# empêche l écrasement au cas où la base existerait déjà
> CREATE DATABASE name [IF NOT EXISTS];

Exp

# Crée une base de Donnée 'eleve'
> CREATE DATABASE eleve;
# ou bien
> CREATE DATABASE eleve IF NOT EXISTS;
Query OK, 1 row affected (0.000 sec)
> SHOW DATABASES;

classDiagram class Database { eleve information_schema mysql performance_schema }

USE : Utiliser une Base de Données⚓︎

⚠ ATTENTION ⚠ il est OBLIGATOIRE de préciser quelle base de données on veut utiliser, grâce à l'instruction sql USE, c'est à dire sur quelle base de données on souhaite travailler.

> USE name;

Exp

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

DROP DATABASE : supprime une Base de Données⚓︎

# Supprime une base de Donnée 'eleve'
> DROP DATABASE eleve;
Query OK, 0 rows affected (0.004 sec)
> SHOW DATABASES;

classDiagram class Database { information_schema mysql performance_schema }

Enfin, Recréez une Base de Données eleve : > CREATE DATABASE eleve; puis > USE eleve;

Gestion de Tables⚓︎

CREATE TABLE : Création de Table.s⚓︎

# Options Facultatives entre [] : (ne pas écrire les []) IF NOT EXISTS ou 
# ENGINE=INNODB qui définit le moteur sql choisi (par défaut : INNODB)
> CREATE TABLE name [IF NOT EXISTS] (
-> attribut1 type,
-> attribut2 type,
-> ...
-> ) [ENGINE=INNODB];

Créer les tables, dans le "bon" ordre, directement avec les clés (primaires et étrangères)

CREATE TABLE adresse (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
ville VARCHAR(20),
pays VARCHAR(20)
) ;

Query OK, 0 rows affected (0.021 sec)

CREATE TABLE utilisateur (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR(20),
prenom VARCHAR(20),
genre VARCHAR(20),
id_adresse INT UNSIGNED NOT NULL,
FOREIGN KEY (id_adresse) REFERENCES adresse (id)
) ;

Query OK, 0 rows affected (0.004 sec)

ou bien, Syntaxe Alternative :

Méthode 2 : Créer les tables dans un ordre quelconque, avec les clés primaires, puis déclarer les clés étrangères APRÈS COUP

CREATE TABLE utilisateur (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
nom VARCHAR(20),
prenom VARCHAR(20),
genre VARCHAR(20),
id_adresse INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ;

Query OK, 0 rows affected (0.004 sec)

CREATE TABLE adresse (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
ville VARCHAR(20),
pays VARCHAR(20)
) ;

Query OK, 0 rows affected (0.019 sec)

Ajout des clés étrangères APRÈS COUP : Modification de la table utilisateur après sa création...

ALTER TABLE utilisateur
ADD FOREIGN KEY (id_adresse) REFERENCES adresse (id);

Query OK, 0 rows affected (0.040 sec)              
Records: 0  Duplicates: 0  Warnings: 0

Cela a pour effet de créer :

  • une table utilisateur :

    • avec un champ/attribut id :
      • qui contient un nombre entier (INT)
      • positif (UNSIGNED),
      • n'autorisant PAS la valeur NULL pour le champ id,
      • dont la gestion des valeurs est automatique (AUTO_INCREMENT). on dira auto_incrémenté par la suite..
      • qui est une clé primaire pour la table utilisateur (PRIMARY KEY ou PRIMARY KEY (id))
    • un champ/attribut nom, Chaîne de caractères pouvant contenir un nombre variable de caractères : jusqu'à 20.
    • un champ/attribut prenom, Chaîne de caractères pouvant contenir un nombre variable de caractères : jusqu'à 20.
    • un champ/attribut genre , chaîne de caractères variable (jusqu'à 20).
    • un champ/attribut id_adresse , entier posifif non nul, qui est une clé étrangère qui référence adresse(id)

  • une table adresse:

    • avec un champ/attribut id entier, positif, non null, auto_incrémenté, clé primaire de la table adresse
    • un champ ville chaîne de caractères variables (jusqu'à 20)
    • un champ pays chaîne de caractères variables (jusqu'à 20)

SHOW TABLES : Affiche les Tables⚓︎

Vérifier que la/les tables ont bien été créées :

> SHOW TABLES;

+-----------------+
| Tables_in_eleve |
+-----------------+
| adresse         |
| utilisateur     |
+-----------------+
2 rows in set (0.001 sec)

DESCRIBE : Décrit les champs/attributs et les Types d'une Table⚓︎

Décrit/Affiche le schéma de la structure d'une table.

> DESCRIBE nom_table;

Exp

MariaDB [eleve]> DESCRIBE utilisateur;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| nom        | varchar(20)      | YES  |     | NULL    |                |
| prenom     | varchar(20)      | YES  |     | NULL    |                |
| genre      | varchar(20)      | YES  |     | NULL    |                |
| id_adresse | int(10) unsigned | NO   | MUL | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
5 rows in set (0.001 sec)

DROP TABLE : Suppression de Table⚓︎

> DROP TABLE nom_table;

Exp

MariaDB [eleve]> SHOW TABLES;
+-----------------+
| Tables_in_eleve |
+-----------------+
| adresse         |
| utilisateur     |
+-----------------+
2 rows in set (0.001 sec)

MariaDB [eleve]> DROP TABLE adresse;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

MariaDB [eleve]> DROP TABLE utilisateur;
Query OK, 0 rows affected (0.015 sec)

MariaDB [eleve]> SHOW TABLES;
+-----------------+
| Tables_in_eleve |
+-----------------+
| adresse         |
+-----------------+
1 row in set (0.001 sec)

⚠ RECRÉÉZ LA TABLE utilisateur AVEC SA CLÉ ÉTRANGÈRE ⚠ (vous pouvez copier-coller la définition plus haut)

ALTER TABLE : Modification de Table⚓︎

Voici comme modifier une Table :

Ajout de champ/attribut/colonne⚓︎

> ALTER TABLE nom_table
-> ADD nom_colonne type_donnees;

Ajoute un champ age de type INT à la table utilisateur :

> ALTER TABLE utilisateur
-> ADD age INT NOT NULL;

Query OK, 0 rows affected (0.079 sec)
Records: 0  Duplicates: 0  Warnings: 0

Suppression de champ/attribut/colonne⚓︎

> ALTER TABLE nom_table
-> DROP [COLUMN] nom_colonne;

Supprime le champ/attribut/colonne age dans la table utilisateur :

> ALTER TABLE utilisateur
-> DROP age;

Modifier la structure d'un champ/attribut/colonne⚓︎

> ALTER TABLE nom_table
-> MODIFY nom_colonne type_donnees;

Exp

Modifie le champ age en un nouveau type FLOAT dans la table utilisateur :

> ALTER TABLE utilisateur
-> MODIFY age FLOAT;

Query OK, 0 rows affected (0.006 sec)
Records: 0  Duplicates: 0  Warnings: 0

Renommer un champ/attribut/colonne⚓︎

> ALTER TABLE nom_table
-> CHANGE OLD_nom_colonne NEW_nom_colonne NEW_type_donnees;

Exp

Renomme le champ ville en departement avec le type VARCHAR(30) dans la table utilisateur :

> ALTER TABLE utilisateur
-> CHANGE age experience INT;

Query OK, 0 rows affected (0.079 sec)
Records: 0  Duplicates: 0  Warnings: 0

Ex

⚠ renommer la colonne experience en age, entier non nul.

Résumé Final⚓︎

> DESCRIBE utilisateur;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| nom        | varchar(20)      | YES  |     | NULL    |                |
| prenom     | varchar(20)      | YES  |     | NULL    |                |
| genre      | varchar(20)      | YES  |     | NULL    |                |
| id_adresse | int(10) unsigned | NO   | MUL | NULL    |                |
| age        | int(11)          | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
6 rows in set (0.001 sec)

> DESCRIBE adresse;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| ville | varchar(20)      | YES  |     | NULL    |                |
| pays  | varchar(20)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.001 sec)

CRUD d'enregistrements de Tables⚓︎

INSERT INTO ... VALUES ... : Insertion/Création d'enregistrements dans une Table⚓︎

> INSERT INTO nom_table (col1, col2,...)
-> VALUES (valeur1, valeur2,...);

Ajout d'Enregistrements Un par un⚓︎

> INSERT INTO adresse (ville, pays)
-> VALUES ('Marseille', 'France');

Query OK, 1 row affected (0.002 sec)

Ajout de Plusieurs Enregistrements en un seul coup⚓︎

> INSERT INTO adresse (ville, pays)
-> VALUES ('Toulouse', 'France'),
-> ('Bordeaux', 'France');

Query OK, 2 rows affected (0.004 sec)
Records: 2  Duplicates: 0  Warnings: 0

Pour la suite, afin de travailler avec des tables un peu renseignées, en plus des enregistrements précédents, copiez-coller les enregistrements suivants :

INSERT INTO adresse (ville, pays)
VALUES ('Paris', 'France'),
('Strasbourg', 'France'),
('Brest', 'France'),
('Lyon', 'France'),
('Granada', 'Espagne'),
('Madrid', 'Espagne'),
('Sevilla', 'Espagne'),
('Valencia', 'Espagne'),
('Oxford', 'UK'),
('Cambridge', 'UK'),
('London', 'UK');

Query OK, 11 rows affected (0.014 sec)
Records: 11  Duplicates: 0  Warnings: 0

INSERT INTO utilisateur (nom, prenom, genre, id_adresse, age)
VALUES ('Dupont', 'Gaelle', 'Femme', 1, 27),
('Dupond', 'Jean', 'Homme', 3, 24),
('Durand', 'Laura', 'Femme', 2, 28),
('Dufour', 'Sarah', 'Femme', 4, 18),
('Dumiel', 'Paul', 'Homme', 5, 19),
('Durmont', 'Karl', 'Homme', 12, 25),
('Doe', 'John', 'Homme', 13, 29),
('Dorne', 'Jane', 'Femme', 14, 29),
('Sanchez', 'Maria', 'Homme', 9, 23),
('Martinez', 'Juan', 'Homme', 8, 22),
('Santamaria', 'Alvaro', 'Homme', 10, 26),
('Santiago', 'Marta', 'Homme', 11, 20);

Query OK, 12 rows affected (0.001 sec)
Records: 12  Duplicates: 0  Warnings: 0

SELECT : Sélection/Lecture/Affichage d'Attributs d'une Table⚓︎

SELECT est une instruction SQL 🇫🇷, ou SQL Statement 🇬🇧 permettant de sélectionner / afficher certains enregistrements d'une table vérifant optionnellement certains critères conditionnels.

SELECT : Syntaxe Générale

SELECT [DISTINCT] {expr1_selection, expr2_selection,...}
FROM nom_table
[ WHERE condition_recherche ]
[ GROUP BY {nom_col | expr | position} [ASC | DESC] ]
[ HAVING condition ]
[ ORDER BY {nom_col | expr_tri | position} [ASC | DESC] ]
[ LIMIT n ]

La syntaxe générale dispose d'options appelées des clauses:

CLAUSE

Une Clause est une partie d'un ordre SQL précisant un fonctionnement particulier.

SELECT ... FROM ...⚓︎

SELECT * FROM nom_table : La clause FROM permet de préciser la table sur laquelle on travaillle. Sélectionner/afficher TOUS les attributs pour tous les enregistrements de la Table utilisateur

> SELECT * FROM utilisateur;
+----+------------+--------+-------+------------+-----+
| id | nom        | prenom | genre | id_adresse | age |
+----+------------+--------+-------+------------+-----+
|  1 | Dupont     | Gaelle | Femme |          1 |  27 |
|  2 | Dupond     | Jean   | Homme |          3 |  24 |
|  3 | Durand     | Laura  | Femme |          2 |  28 |
|  4 | Dufour     | Sarah  | Femme |          4 |  18 |
|  5 | Dumiel     | Paul   | Homme |          5 |  19 |
|  6 | Durmont    | Karl   | Homme |         12 |  25 |
|  7 | Doe        | John   | Homme |         13 |  29 |
|  8 | Dorne      | Jane   | Femme |         14 |  29 |
|  9 | Sanchez    | Maria  | Homme |          9 |  23 |
| 10 | Martinez   | Juan   | Homme |          8 |  22 |
| 11 | Santamaria | Alvaro | Homme |         10 |  26 |
| 12 | Santiago   | Marta  | Homme |         11 |  20 |
+----+------------+--------+-------+------------+-----+
12 rows in set (0.001 sec)

> SELECT * FROM adresse;
+----+------------+---------+
| id | ville      | pays    |
+----+------------+---------+
|  1 | Marseille  | France  |
|  2 | Toulouse   | France  |
|  3 | Bordeaux   | France  |
|  4 | Paris      | France  |
|  5 | Strasbourg | France  |
|  6 | Brest      | France  |
|  7 | Lyon       | France  |
|  8 | Granada    | Espagne |
|  9 | Madrid     | Espagne |
| 10 | Sevilla    | Espagne |
| 11 | Valencia   | Espagne |
| 12 | Oxford     | UK      |
| 13 | Cambridge  | UK      |
| 14 | London     | UK      |
+----+------------+---------+
14 rows in set (0.001 sec)

SELECT .., .., ..⚓︎

SELECT attribut1, attribut2, ... : Exemple: sélectionner/afficher UNIQUEMENT les attributs prenom et age pour tous les enregistrements de la Table utilisateur

> SELECT prenom, age FROM utilisateur;
+--------+-----+
| prenom | age |
+--------+-----+
| Gaelle |  27 |
| Jean   |  24 |
| Laura  |  28 |
| Sarah  |  18 |
| Paul   |  19 |
| Karl   |  25 |
| John   |  29 |
| Jane   |  29 |
| Maria  |  23 |
| Juan   |  22 |
| Alvaro |  26 |
| Marta  |  20 |
+--------+-----+
12 rows in set (0.001 sec)

SELECT .. AS .. (,.. AS ..)⚓︎

SELECT attribut1 AS a1, attribut2 AS a2 ... : Renommage des Champs/attributs (en tant que autre chose que leur nom original) :

  • lors de l'affichage
  • comme variable dans la suite de la requête

Exemple: afficher id_adresse en tant que identifiant:

> SELECT id_adresse AS identifiant FROM utilisateur;
+-------------+
| identifiant |
+-------------+
|           1 |
|           2 |
|           3 |
|           4 |
|           5 |
|           8 |
|           9 |
|          10 |
|          11 |
|          12 |
|          13 |
|          14 |
+-------------+
12 rows in set (0.001 sec)

SELECT DISTINCT⚓︎

SELECT DISTINCT : la clause DISTINCT sélectionne des résultats SANS DOUBLONS. Exemple : sélectionner/afficher l' attribut genre SANS DOUBLONS de tous les enregistrements de la Table utilisateur

> SELECT DISTINCT genre FROM utilisateur;
+-------+
| genre |
+-------+
| Femme |
| Homme |
+-------+
2 rows in set (0.001 sec)

WHERE⚓︎

WHERE condition : La clause WHERE sert à filtrer les données des tables, grâce à une certaine condition

> SELECT * FROM utilisateur WHERE id=8;
+----+-------+--------+-------+------------+-----+
| id | nom   | prenom | genre | id_adresse | age |
+----+-------+--------+-------+------------+-----+
|  8 | Dorne | Jane   | Femme |         14 |  29 |
+----+-------+--------+-------+------------+-----+
1 row in set (0.001 sec)

Les conditions peuvent utiliser des opérateurs logiques :

Opérateur Signification Exemple
AND ET SELECT nom_colonnes
FROM nom_table
WHERE condition1 AND condition2
OR OU SELECT nom_colonnes
FROM nom_table
WHERE condition1 OR condition2
IN APPARTIENT À SELECT nom_colonne
FROM nom_table
WHERE nom_colonne IN (valeur1, valeur2, ..)
BETWEEN EST COMPRIS ENTRE SELECT nom_colonne
FROM nom_table
WHERE nom_colonne BETWEEN 'valeur1' AND 'valeur2'
LIKE suit le modèle.. SELECT nom_colonne
FROM nom_table
WHERE nom_colonne LIKE modèle
  • Caractères Spéciaux du modèle :
    • % : modélise un nombre quelconque de caractères quelconques
    • _ : modélise un unique caractère quelconque
  • Exemples :
    • .. LIKE 'a%' : Commence par un 'a'
    • .. LIKE '%a' : Termine par un 'a'
    • .. LIKE '%a%' : Contient un 'a'
    • .. LIKE 'pa%on' : commence par pa, finit par on

ORDER BY⚓︎

ORDER BY : La clause ORDER BY permet d'ordonner les résultats d'une requête.

Exemple : ordonner selon un attribut, le jeu de résultats d'une sélection de tous les enregistrements de la Table utilisateur

> SELECT * FROM utilisateur ORDER BY prenom;
+----+------------+--------+-------+------------+-----+
| id | nom        | prenom | genre | id_adresse | age |
+----+------------+--------+-------+------------+-----+
| 11 | Santamaria | Alvaro | Homme |         10 |  26 |
|  1 | Dupont     | Gaelle | Femme |          1 |  27 |
|  8 | Dorne      | Jane   | Femme |         14 |  29 |
|  2 | Dupond     | Jean   | Homme |          3 |  24 |
|  7 | Doe        | John   | Homme |         13 |  29 |
| 10 | Martinez   | Juan   | Homme |          8 |  22 |
|  6 | Durmont    | Karl   | Homme |         12 |  25 |
|  3 | Durand     | Laura  | Femme |          2 |  28 |
|  9 | Sanchez    | Maria  | Homme |          9 |  23 |
| 12 | Santiago   | Marta  | Homme |         11 |  20 |
|  5 | Dumiel     | Paul   | Homme |          5 |  19 |
|  4 | Dufour     | Sarah  | Femme |          4 |  18 |
+----+------------+--------+-------+------------+-----+
12 rows in set (0.001 sec)

LIMIT⚓︎

LIMIT n : La clause LIMIT permet de limiter les résultats d'une requête aux n premiers résultats. Exemple: Limiter aux n premiers résultats d'une sélection de TOUS les attributs pour tous les enregistrements de la Table utilisateur

> SELECT * FROM utilisateur LIMIT 1;
+----+--------+--------+-------+------------+-----+
| id | nom    | prenom | genre | id_adresse | age |
+----+--------+--------+-------+------------+-----+
|  1 | Dupont | Gaelle | Femme |          1 |  27 |
+----+--------+--------+-------+------------+-----+
1 row in set (0.000 sec)

GROUP BY⚓︎

GROUP BY (HORS-PROGRAMME) : La clause GROUP BY permet de présenter les résultats d'une requête selon une certaine logique : en regroupant ensemble les enregistrements dont la/les colonne(s) ont les mêmes valeurs, ou des expressions évaluées en des mêmes valeurs.

Exemple : compter le nombre d'enregistrements par genre dans la Table utilisateur

> SELECT genre, count(*) AS quantite FROM utilisateur GROUP BY genre;
+-------+----------+
| genre | quantite |
+-------+----------+
| Femme |        4 |
| Homme |        8 |
+-------+----------+
2 rows in set (0.001 sec)

HAVING (HORS-PROGRAMME)⚓︎

HAVING (HORS-PROGRAMME) : La clause HAVING permet de filtrer les données du résultat, grâce à une condition, les résultats des données aggrégées par la clause GROUP BY : en regroupant ensemble les enregistrements dont la/les colonne(s) ont les mêmes valeurs, ou des expressions évaluées en des mêmes valeurs.

Exemple : compter le nombre d'enregistrements par genre dans la Table utilisateur vérifiant la condition age>25

> SELECT genre, count(*) AS quantite FROM utilisateur GROUP BY genre, age HAVING age>25;
ou bien
> SELECT genre, count(*) AS quantite FROM utilisateur WHERE age>25 GROUP BY genre;
+-------+----------+
| genre | quantite |
+-------+----------+
| Femme |        3 |
| Homme |        2 |
+-------+----------+
2 rows in set (0.001 sec)

UPDATE .. SET .. : Modification d'un Enregistrement d'une Table⚓︎

UPDATE ... SET ... : mettre à jour l' attribut nom de la Table utilisateur vérifiant une certaine condition (avec WHERE condition )

Changer un seul attribut d'un enregistrement⚓︎

> UPDATE utilisateur SET nom='Dupond' WHERE id=1;
Query OK, 1 row affected (0.004 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Changer plusieurs attributs d'un enregistrement⚓︎

> UPDATE utilisateur SET nom='Durand', prenom='Laurene' WHERE id=1;
Query OK, 1 row affected (0.008 sec)
Rows matched: 1  Changed: 1  Warnings: 0

vérifier la dernière modification: 

> SELECT * FROM utilisateur;
+----+------------+--------+-------+------------+-----+
| id | nom        | prenom | genre | id_adresse | age |
+----+------------+--------+-------+------------+-----+
|  1 | Dupond     | Gaelle | Femme |          1 |  27 |
|  2 | Dupond     | Jean   | Homme |          3 |  24 |
|  3 | Durand     | Laura  | Femme |          2 |  28 |
|  4 | Dufour     | Sarah  | Femme |          4 |  18 |
|  5 | Dumiel     | Paul   | Homme |          5 |  19 |
|  6 | Durmont    | Karl   | Homme |         12 |  25 |
|  7 | Doe        | John   | Homme |         13 |  29 |
|  8 | Dorne      | Jane   | Femme |         14 |  29 |
|  9 | Sanchez    | Maria  | Homme |          9 |  23 |
| 10 | Martinez   | Juan   | Homme |          8 |  22 |
| 11 | Santamaria | Alvaro | Homme |         10 |  26 |
| 12 | Santiago   | Marta  | Homme |         11 |  20 |
+----+------------+--------+-------+------------+-----+
12 rows in set (0.000 sec)

DELETE FROM : Supprimer un enregistrement d'une Table⚓︎

> DELETE FROM utilisateur WHERE id=1;
Query OK, 1 row affected (0.008 sec)

vérifier la bonne suppression:

> SELECT * from utilisateur;
+----+------------+--------+-------+------------+-----+
| id | nom        | prenom | genre | id_adresse | age |
+----+------------+--------+-------+------------+-----+
|  2 | Dupond     | Jean   | Homme |          3 |  24 |
|  3 | Durand     | Laura  | Femme |          2 |  28 |
|  4 | Dufour     | Sarah  | Femme |          4 |  18 |
|  5 | Dumiel     | Paul   | Homme |          5 |  19 |
|  6 | Durmont    | Karl   | Homme |         12 |  25 |
|  7 | Doe        | John   | Homme |         13 |  29 |
|  8 | Dorne      | Jane   | Femme |         14 |  29 |
|  9 | Sanchez    | Maria  | Homme |          9 |  23 |
| 10 | Martinez   | Juan   | Homme |          8 |  22 |
| 11 | Santamaria | Alvaro | Homme |         10 |  26 |
| 12 | Santiago   | Marta  | Homme |         11 |  20 |
+----+------------+--------+-------+------------+-----+
11 rows in set (0.001 sec)

Remarque Dans la suite, on supposera que nous repartons de la table utilisateur originale (sans avoir supprimé Dupont Gaelle qui a un id=1)

Jointures⚓︎

Comme déjà expliqué, il existe trois types des jointures:

  • INNER pour Jointure Interne
  • OUTER pour Jointure Externe
  • CROSS pour Produit Cartésien

Le principe des jointures entre deux tables, est basé sur une (ou des) conditions ON .. devant être vérifiées par un (ou plusieurs) attributs de l'une des tables comparés avec un (ou des) attributs de l'autre table. Classiquement, mais non obligatoirement, il s'agit de faire correspondre un attribut de l'un (une clé primaire), avec l'attribut correspondant de l'autre table (une clé étrangère).

On souhaite retrouver les résultats de la partie théorique.
C'est pourquoi, dans cette partie, on définira les table1 et table2 suivantes (encore dans la base de données eleve)

CREATE TABLE table2 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
nom_ville VARCHAR(20));

CREATE TABLE table1 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
id_ville INT UNSIGNED NOT NULL,
FOREIGN KEY (id_ville) REFERENCES table2 (id),
nom VARCHAR(20),
prenom VARCHAR(20));

INSERT INTO table2 (nom_ville) 
VALUES ('Marseille'), ('Toulouse'), ('Bordeaux');

INSERT INTO table1 (id_ville, nom, prenom) VALUES (3, 'Dupont','Gaelle'), (3, 'Dutronc','Jacques'), (1, 'Dupond','Jean'), (3, 'Durand','Laura'), (1, 'Dufour','Sarah'), (1, 'Dumiel','Paul'), (3, 'Dumont','Karl');

SET FOREIGN_KEY_CHECKS=0;

UPDATE table1 SET id_ville=4 WHERE id=2;

SELECT * FROM table1;

+----+----------+---------+---------+
| id | id_ville | nom     | prenom  |
+----+----------+---------+---------+
|  1 |        3 | Dupont  | Gaelle  |
|  2 |        4 | Dutronc | Jacques |
|  3 |        1 | Dupond  | Jean    |
|  4 |        3 | Durand  | Laura   |
|  5 |        1 | Dufour  | Sarah   |
|  6 |        1 | Dumiel  | Paul    |
|  7 |        3 | Dumont  | Karl    |
+----+----------+---------+---------+
7 rows in set (0.001 sec)

SELECT * FROM table2;

+----+-----------+
| id | nom_ville |
+----+-----------+
|  1 | Marseille |
|  2 | Toulouse  |
|  3 | Bordeaux  |
+----+-----------+
3 rows in set (0.001 sec)

INNER JOIN : Jointure Interne⚓︎

La Jointure Interne appelée INNER JOIN, ou simplement JOIN sur MariaDB, quelquefois EQUIJOIN, renvoie les enregistrements de la table de gauche (table1) pour lesquels il existe au moins un enregistrement correspondant (à la condition ON..) dans la table de droite (table2). ⚠ Aucune ligne du résultat d'une jointure interne ne contient de NULL.

INNER JOIN

INNER JOIN - JOINTURE INTERNE 

SELECT colonne1, colonne2, ..
FROM table1
JOIN table2 ON table1.id = table2.fk_id

Syntaxe Alternative : 

SELECT colonne1, colonne2, ..
FROM table1
INNER JOIN table2 ON table1.id = table2.fk_id

Syntaxe Alternative : 

SELECT *
FROM table1
INNER JOIN table2
WHERE table1.id = table2.fk_id

⚠Remarque ⚠ le mot INNER est facultatif dans MariaDB : INNER JOIN ou JOIN sont donc synonymes

Cette requête joint les deux tables table1 et table2 et on choisit un critère de sélection avec le ON condition

Exp

SELECT * 
FROM table1 
INNER JOIN table2 
ON table1.id_ville=table2.id;

+----+----------+--------+--------+----+-----------+
| id | id_ville | nom    | prenom | id | nom_ville |
+----+----------+--------+--------+----+-----------+
|  1 |        3 | Dupont | Gaelle |  3 | Bordeaux  |
|  3 |        1 | Dupond | Jean   |  1 | Marseille |
|  4 |        3 | Durand | Laura  |  3 | Bordeaux  |
|  5 |        1 | Dufour | Sarah  |  1 | Marseille |
|  6 |        1 | Dumiel | Paul   |  1 | Marseille |
|  7 |        3 | Dumont | Karl   |  3 | Bordeaux  |
+----+----------+--------+--------+----+-----------+
6 rows in set (0.001 sec)

On retrouve bien le résultat théorique

LEFT OUTER JOIN : Jointure Externe Gauche⚓︎

La Jointure Externe Gauche, ou LEFT JOIN, ou LEFT OUTER JOIN, permet de renvoyer tous les enregistrements de la table de gauche (table1), même s’ils n’ont pas de correspondance dans la table de droite (table2). ⚠ Dans une jointure (externe) gauche, les lignes de la table de droite sans correspondance vaudront toutes NULL.

LEFT JOIN

LEFT (OUTER) JOIN - JOINTURE (EXTERNE) GAUCHE 

SELECT colonne1, colonne2, ..
FROM ma_table1
LEFT JOIN table2 ON table1.id = table2.fk_id

Syntaxe Alternative : 

SELECT colonne1, colonne2, ..
FROM ma_table1
LEFT OUTER JOIN table2 ON table1.id = table2.fk_id

⚠Remarque ⚠ Au moins l'un des mots LEFT (ou RIGHT) est requis/obligatoire avec un OUTER JOIN. Autrement dit : OUTER JOIN tout seul, n'est pas compris par MariaDB.

Cette requête joint les deux tables table1 et table2 et on choisit un critère de sélection avec le ON condition

Jointure Externe Gauche

SELECT * 
FROM table1 
LEFT OUTER JOIN table2 
ON table1.id_ville=table2.id;

+----+----------+---------+---------+------+-----------+
| id | id_ville | nom     | prenom  | id   | nom_ville |
+----+----------+---------+---------+------+-----------+
|  1 |        3 | Dupont  | Gaelle  |    3 | Bordeaux  |
|  2 |        4 | Dutronc | Jacques | NULL | NULL      |
|  3 |        1 | Dupond  | Jean    |    1 | Marseille |
|  4 |        3 | Durand  | Laura   |    3 | Bordeaux  |
|  5 |        1 | Dufour  | Sarah   |    1 | Marseille |
|  6 |        1 | Dumiel  | Paul    |    1 | Marseille |
|  7 |        3 | Dumont  | Karl    |    3 | Bordeaux  |
+----+----------+---------+---------+------+-----------+
7 rows in set (0.001 sec)

On retrouve bien le résultat théorique

RIGHT OUTER JOIN : Jointure Externe Droite⚓︎

La Jointure Externe Droite, ou RIGHT JOIN, ou RIGHT OUTER JOIN, permet de renvoyer tous les enregistrements de la (deuxième) table de droite (table2), même s’il n’y a pas de correspondance dans la (première) table de gauche (table1). ⚠ Dans une jointure (externe) droite, les lignes de la table de gauche sans correspondance vaudront toutes NULL.

RIGHT JOIN

RIGHT (OUTER) JOIN - JOINTURE (EXTERNE) DROITE 

SELECT colonne1, colonne2, ..
FROM ma_table1
RIGHT JOIN table2 ON table1.id = table2.fk_id

Syntaxe Alternative : 

SELECT colonne1, colonne2, ..
FROM ma_table1
RIGHT OUTER JOIN table2 ON table1.id = table2.fk_id

Jointure Externe Droite

SELECT * 
FROM table1 
RIGHT OUTER JOIN table2 
ON table1.id_ville=table2.id;

+------+----------+--------+--------+----+-----------+
| id   | id_ville | nom    | prenom | id | nom_ville |
+------+----------+--------+--------+----+-----------+
|    3 |        1 | Dupond | Jean   |  1 | Marseille |
|    5 |        1 | Dufour | Sarah  |  1 | Marseille |
|    6 |        1 | Dumiel | Paul   |  1 | Marseille |
| NULL |     NULL | NULL   | NULL   |  2 | Toulouse  |
|    1 |        3 | Dupont | Gaelle |  3 | Bordeaux  |
|    4 |        3 | Durand | Laura  |  3 | Bordeaux  |
|    7 |        3 | Dumont | Karl   |  3 | Bordeaux  |
+------+----------+--------+--------+----+-----------+
7 rows in set (0.001 sec)

On retrouve bien le résultat théorique

CROSS JOIN : Produit Cartésien⚓︎

La syntaxe pour un produit cartésien est :

> SELECT attribut1, ... 
FROM table1
CROSS JOIN table2
ON table1.attribut1 = table2.attribut2;

⚠Remarque ⚠ le mot INNER est facultatif dans MariaDB : INNER JOIN ou JOIN sont synonymes

Cette requête joint les deux tables table1 et table2 et on choisit un critère de sélection avec le ON condition

Exp

SELECT * 
FROM table1 
CROSS JOIN table2;

+----+----------+---------+---------+----+-----------+
| id | id_ville | nom     | prenom  | id | nom_ville |
+----+----------+---------+---------+----+-----------+
|  1 |        3 | Dupont  | Gaelle  |  1 | Marseille |
|  1 |        3 | Dupont  | Gaelle  |  2 | Toulouse  |
|  1 |        3 | Dupont  | Gaelle  |  3 | Bordeaux  |
|  2 |        4 | Dutronc | Jacques |  1 | Marseille |
|  2 |        4 | Dutronc | Jacques |  2 | Toulouse  |
|  2 |        4 | Dutronc | Jacques |  3 | Bordeaux  |
|  3 |        1 | Dupond  | Jean    |  1 | Marseille |
|  3 |        1 | Dupond  | Jean    |  2 | Toulouse  |
|  3 |        1 | Dupond  | Jean    |  3 | Bordeaux  |
|  4 |        3 | Durand  | Laura   |  1 | Marseille |
|  4 |        3 | Durand  | Laura   |  2 | Toulouse  |
|  4 |        3 | Durand  | Laura   |  3 | Bordeaux  |
|  5 |        1 | Dufour  | Sarah   |  1 | Marseille |
|  5 |        1 | Dufour  | Sarah   |  2 | Toulouse  |
|  5 |        1 | Dufour  | Sarah   |  3 | Bordeaux  |
|  6 |        1 | Dumiel  | Paul    |  1 | Marseille |
|  6 |        1 | Dumiel  | Paul    |  2 | Toulouse  |
|  6 |        1 | Dumiel  | Paul    |  3 | Bordeaux  |
|  7 |        3 | Dumont  | Karl    |  1 | Marseille |
|  7 |        3 | Dumont  | Karl    |  2 | Toulouse  |
|  7 |        3 | Dumont  | Karl    |  3 | Bordeaux  |
+----+----------+---------+---------+----+-----------+
21 rows in set (0.001 sec)

On retrouve bien les résultats théoriques

Opérations d'Agrégation⚓︎

Dans cette partie, on travaille sur une table commande définie comme ceci, et on y insère les enregistrements suivants :

CREATE TABLE commande (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
id_client INT UNSIGNED NOT NULL,
id_article INT UNSIGNED NOT NULL,
prix FLOAT UNSIGNED NOT NULL,
quantite INT UNSIGNED NOT NULL);

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| id_client  | int(10) unsigned | NO   |     | NULL    |                |
| id_article | int(10) unsigned | NO   |     | NULL    |                |
| prix       | float unsigned   | NO   |     | NULL    |                |
| quantite   | int(10) unsigned | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
5 rows in set (0.001 sec)

INSERT INTO commande (id_client, id_article, prix, quantite)
VALUES (1, 1000, 20, 1),
(2, 1010, 30, 1),
(3, 1020, 35.2, 1),
(4, 1030, 54.87, 1),
(5, 1040, 18.1, 3),
(6, 1050, 32.7, 2);

+----+-----------+------------+-------+----------+
| id | id_client | id_article | prix  | quantite |
+----+-----------+------------+-------+----------+
|  1 |         1 |       1000 |    20 |        1 |
|  2 |         2 |       1010 |    30 |        1 |
|  3 |         3 |       1020 |  35.2 |        1 |
|  4 |         4 |       1030 | 54.87 |        1 |
|  5 |         5 |       1040 |  18.1 |        3 |
|  6 |         6 |       1050 |  32.7 |        2 |
+----+-----------+------------+-------+----------+

Il est possible d'agréger les résultats, i.e. intuitivement de les compacter ensemble d'une certaine manière. Il existe plusieurs opérations spécifiques.

SUM : Somme de toutes les valeurs d'un attribut⚓︎

SELECT SUM(prix) AS Total FROM commande;

+-------------------+
| Total             |
+-------------------+
| 190.8700008392334 |
+-------------------+
1 row in set (0.001 sec)

SELECT ROUND(SUM(prix),2) AS Total FROM commande;
+--------+
| Total  |
+--------+
| 190.87 |
+--------+
1 row in set (0.001 sec)

COUNT : Comptage⚓︎

SELECT COUNT(*) AS quantite FROM commande WHERE prix<34;

+----------+
| quantite |
+----------+
|        4 |
+----------+
1 row in set (0.000 sec)

AVG : AVeraGe = Moyenne⚓︎

SELECT AVG(prix) AS 'prix moyen' FROM commande;

+------------------+
| prix moyen       |
+------------------+
| 31.8116668065389 |
+------------------+
1 row in set (0.001 sec)

MIN et MAX : Minimum et Maximum⚓︎

SELECT MIN(prix) AS minimum FROM commande;

+---------+
| minimum |
+---------+
|    18.1 |
+---------+
1 row in set (0.001 sec)

SELECT MAX(prix) AS maximum FROM commande;

+---------+
| maximum |
+---------+
|   54.87 |
+---------+
1 row in set (0.001 sec)