Cette séquence de travaux pratiques vise à mettre en œuvre un serveur de base de données SQL mariaDB, tester son fonctionnement et manipuler les données de différentes façons.
¶ MySQL & mariaDB
MySQL est un système de gestion de bases de données relationnelles (SGBDR). Il est distribué sous une double licence GPL et propriétaire. Il fait partie des logiciels de gestion de base de données les plus utilisés au monde, autant par le grand public (applications web principalement) que par des professionnels, en concurrence avec Oracle, PostgreSQL et Microsoft SQL Server. Son nom vient du prénom de la fille du cocréateur Michael Widenius, « My ». SQL fait référence au Structured Query Language, le langage de requête utilisé. MySQL a été acheté le 16 janvier 2008 par Sun Microsystems lui-même acquis ensuite par Oracle Corporation.
Depuis mai 2009, son créateur Michael Widenius a créé MariaDB (Maria est le prénom de sa deuxième fille) pour continuer son développement en tant que projet Open Source.
¶ Préparation de l'environnement
On démarre avec le mini-LAB virtuel :
- pfSense, passerelle vers Internet et serveur DHCP sur le LAN 192.168.1.0/24
- Debian Core, qui sera notre serveur mariaDB
- Debian GUI, machine client avec interface graphique, équipée d’un terminal et d’un navigateur Web, pour administrer et exploiter le serveur.
Pour rappel, voici les étapes de déploiement du mini-LAB sur VirtualBox :
Installation de pfSense- Installation de Debian Core
- Installation de Debian GUI
- La finalisation du labo
¶ Préparation du poste client
Tout d'abord, sur notre VM debian-gui (cliente), on lance une mise à jour et l'installation des outils wget, curl et dnsutils :
sudo apt update && sudo apt upgrade && sudo apt -y install curl wget dnsutils
On supprime le fichier des hôtes connus (traces d'anciens TP) :
rm ~/.ssh/known_hosts
¶ Préparation du serveur
Maintenant, occupons-nous du serveur debian-core :
- On s'y connecte en SSH depuis debian-gui :
ssh pascal@192.168.1.10 - On modifie le hostname en debian-db :
sudo nano /etc/hostname - sans oublier de modifier aussi le fichier hosts :
sudo nano /etc/hosts
qui doit au final ressembler à ceci :
127.0.0.1 localhost
127.0.1.1 debian-db
# The following lines are desirable for IPv6 capable hosts
::1 localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
on redémarre le serveur :
sudo reboot
Puis, une fois le serveur relancé, on s'y reconnecte :
ssh pascal@192.168.1.10
On lance une mise à jour et l'installation quelques utilitaires :
sudo apt update && sudo apt upgrade && sudo apt -y install wget tree curl
¶ Le SGBD mySQL (mariaDB)
¶ Installation
Comme tout service répandu sous Debian Linux, l’installation de mariaDB se fait de la plus simple des façons (cependant c’est un paquet assez lourd, de plusieurs centaines de Mo) :
sudo apt install mariadb-server
Voici les emplacements des différents éléments essentiels du serveur mySQL :
| Emplacement | Description |
|---|---|
/var/lib/mysql |
Répertoire contenant les données du serveur (les bases de données et leurs contenus) |
/etc/mysql/mariadb.cnf |
Fichier de configuration global (paramètres globaux par défaut) |
/etc/mysql/conf.d/*.cnf |
Répertoire des fichiers de configuration globaux |
/etc/mysql/mariadb.conf.d/*.cnf |
Répertoire des fichiers de configuration pour mariaDB seulement |
/etc/mysql/mariadb.conf.d/50-server.cnf |
Fichier de configuration spécifique pour le serveur (à privilégier en général) |
~/.my.cnf |
Options spécifiques pour un utilisateur donné (dossier ~) |
/var/log/mysql/ |
Emplacement des fichiers de log |
Ces emplacements peuvent être modifiés par configuration.
Le serveur mySQL est donc un service installé sur notre machine, ce qu’on peut vérifier ainsi :
sudo systemctl status mariadb
Le
sudon'est pas obligatoire, mais il peut empêcher d'accéder à certaines informations.
On peut voir aussi que le processus (PID) associé à ce service appartient à un utilisateur spécifique mysql :
ps -aux | grep mysql
qui renvoie cette réponse :
mysql 4364 2.6 10.6 1078664 105208 ? Ssl 21:35 0:00 /usr/sbin/mariadbd
pascal 4477 0.0 0.2 6352 2172 pts/0 S+ 21:36 0:00 grep mysql
¶ Configuration initiale
Ensuite nous lançons la procédure de configuration initiale sécurisée du serveur Mysql.
sudo mysql_secure_installation
Il faut bien lire les messages et indications tout au long de ce processus pour bien comprendre les différents objectifs.
Dans notre contexte (labo), les réponses à donner sont dans l'ordre : Entrée, n, n, y, y, y, y.
Afin que le serveur mysql soit accessible de l’extérieur, il faut modifier un paramètre du fichier de configuration par défaut :
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
Il y a un paramètre bind-address qui limite par défaut l’accès uniquement à 127.0.0.1 (accès local) ; il faut le désactiver en le commentant : placer un # en début de ligne :
# bind-address = 127.0.0.1
Puis ne pas oublier de relancer le service :
sudo systemctl restart mariadb
¶ La console SQL
¶ Prise en main
On entre dans la console mysql en mode super-user :
sudo mysql -u root
Dans cette console, on saisit des commandes en langage SQL pour gérer des bases de données et les objets associés (tables, colonnes, données, …).
Essayer ces commandes :
MariaDB [(none)]> show databases;
MariaDB [(none)]> use mysql;
MariaDB [mysql]> show tables;
MariaDB [mysql]> show columns from time_zone;
MariaDB [mysql]> show columns from user;
Attention les commandes SQL se terminent toujours par un
;
¶ Importation de données
Nous allons importer une base de données complète dans notre serveur, depuis un script SQL.
Sortez de la console mysql (exit;) et importez le script france.sql par la commande suivante :
wget http://bts.melot.fr/france.sql
Un script SQL contient une série de commandes SQL pour automatiser les opérations nécessaires à la création de la structure (base de données, tables, index, ...) et l’importation des données dans la base.
Prenez le temps d’analyser le contenu du script :
nano france.sql
On va maintenant exécuter le script, par une redirection de l’entrée standard de Linux vers l'interpréteur mySQL :
sudo mysql -u root < france.sql
Vérifions la présence des données avec la console :
sudo mysql -u root
Et essayer ces différentes commandes SQL, en essyant d'analyser leur syntaxe :
MariaDB [(none)]> show databases;
MariaDB [(none)]> use france;
MariaDB [france]> show tables;
MariaDB [france]> show columns from villes;
MariaDB [france]> select ville_nom from villes where ville_nom like "%UZ";
MariaDB [france]> select ville_nom from villes where ville_nom like "bru%";
¶ Connexion depuis un logiciel client
Désormais, nous allons nous connecter à notre serveur de base de données puis l’extérieur.
¶ depuis DBeaver
Essayons d’abord avec un gestionnaire universel de base de données : dbeaver. Logiciel libre (parmi d’autres), disponible sous Windows ou Linux.
Téléchargez et installez sur la VM Debian GUI le logiciel dbeaver : https://dbeaver.io/files/dbeaver-ce_latest_amd64.deb
NB : on récupère dans le répertoire Téléchargements un paquet Debian (.deb). Il faut l’installer avec la commande dpkg , par exemple (si la version est 25.0.0) :
sudo dpkg -i ~/Téléchargements/dbeaver-ce_25.0.0_amd64.deb
Avant d’aller plus loin, nous devons créer un utilisateur mysql supplémentaire ; en effet, la configuration par défaut de mariaDB prévoit que l’utilisateur root ne peut pas se connecter depuis l’extérieur, ce qui est une bonne chose.
On revient sur le serveur :
sudo mysql -u root
Voici exprimée en langage "clair" la commande que nous allons passer au serveur en SQL :
Accorder (GRANT) à l’utilisateur dbeaver tous les privilèges (ALL) sur la totalité du contenu (.*) de la base de données france lorsqu’il se connecte depuis n’importe quelle source (@’%’) ; le mot de passe p@55word est associé à cet utilisateur.
Ce qui donne en SQL :
GRANT ALL ON france.* TO 'dbeaver'@'%' IDENTIFIED BY 'p@55word';
Puis rendre effectifs ces réglages (FLUSH)
FLUSH PRIVILEGES;
et enfin, quitter le shell SQL :
exit;
Etablissez maintenant une connexion sur le serveur depuis dbeaver : Barre de menu Dbeaver : base de données / nouvelle connexion / MariaDB
Il vous faudra :
- L’adresse IP du serveur (192.168.1.10)
- Le nom de la base de données (france)
- Le nom d’utilisateur (dbeaver)
- Le mot de passe (p@55word)
Il en est ainsi à chaque fois qu’on connecte un client à un serveur de base de données.
Une fois la connexion établie, et les quelques compléments installés, afficher le contenu de la table ville, un peu comme ceci :
¶ Depuis Python
On a besoin d’un module complémentaire qui permet la connexion à une base MySQL depuis Python. Pour pouvoir importer ce module dans Python, il faut préalablement l’installer (le télécharger). Pour cela, on utilise pip, utilitaire qui permet d’installer des modules Python depuis une fantastique bibliothèque :
sudo apt install pip
Ceci se fait sur la machine cliente GUI
Puis il faut créer un environnement virtuel pour Python (sinon pip ne fonctionnera pas) :
sudo apt install python3-venv
python3 -m venv france
Et l’activer :
source france/bin/activate
Ensuite on installe le module mysql-connector-python :
pip install mysql-connector-python
Enfin, on crée un script que nous appellerons bdd :
nano bdd
Voici le contenu du script (complétez les parties manquantes (?), et expérimentez des modifications) :
#!/usr/bin/env python3
import mysql.connector
madb = mysql.connector.connect(
host="192.168.1.10",
user="???????",
passwd="???????",
database="????"
)
cur = madb.cursor()
cur.execute('select ville_population_2012,ville_nom_reel,ville_code_postal from villes where ville_nom like "%barth%"')
res = cur.fetchall()
x = 0
for ville in res:
print("En 2012,",ville[1],ville[2],"était peuplée de",ville[0],"personnes.")
x = x + ville[0]
print("Au total, ceci représente",x,"personnes.")
Il faut créer ce script sur la machine cliente GUI (ET NON PAS SUR LE SERVEUR, car il s’exécute bien depuis la machine cliente).
Rendez-le exécutable :
chmod u+x bdd
et testez-le :
./bdd
¶ Variante
Testez et analyser cette variante qui permet de passer l’argument de recherche lors de l’appel du script :
#!/usr/bin/env python3
import mysql.connector
import sys
madb = mysql.connector.connect(
host="192.168.1.10",
user="???????",
passwd="???????",
database="????"
)
if len(sys.argv)>1:
recherche = sys.argv[1]
else:
recherche = "paris"
print("Aucun argument fourni : 'paris' par défaut.")
recherche = "%" + recherche + "%"
cur = madb.cursor()
requete = 'select ville_population_2012,ville_nom_reel,ville_code_postal '
requete = requete + 'from villes where ville_nom like "' + recherche + '"'
cur.execute(requete)
res = cur.fetchall()
x = 0
for ville in res:
print("En 2012,",ville[1],"(",ville[2],")","était peuplée de",ville[0],"personnes.")
x = x + ville[0]
print("Au total, ceci représente",x,"personnes.")
Essayer votre script en passant un argument, comme (si votre script s’appelle bdd) :
./bdd nantes
¶ Exercices SQL
Dans cette nouvelle phase, l’objectif désormais est de manipuler un jeu de données afin de se familiariser avec le langage SQL, au travers de quelques exemples de requêtes.
¶ le jeu de données
Il faut tout d’abord créer le jeu de données ; nous allons utiliser un fichier CSV, contenant des informations sur une grosse centaine de joueurs de football internationaux.
Utilisons le shell sql directement sur le serveur :
sudo mysql -u root
Nous allons créer une base de données football
create database football;
Puis sortons temporairement (CTRL+D fonctionne aussi pour sortir d'un shell) :
exit;
Nous allons donc créer le jeu de données à partir d’un fichier csv, qu’il faut télécharger :
wget http://bts.melot.fr/players.csv
Un petit coup d’œil au contenu :
head players.csv
On reconnaît la forme typique d’un dump de données au format csv, avec ici la virgule comme séparateur de champ.
Il faut déplacer ce fichier dans le répertoire de la base de données pour pouvoir l’importer ensuite :
sudo mv players.csv /var/lib/mysql/football
Cette méthode d’importation est différente de celle déjà expérimentée plus haut, par script SQL. Notons qu’elle est moins fiable que la méthode par script, laquelle permet également de créer les bases et les tables nécessaires et de sauvegarder et restituer l’intégralité d’un SGBD.
On retourne sur la console SQL :
sudo mysql -u root
On sélectionne notre base de données :
use football;
dans laquelle nous allons créer une table de joueurs, avec un certains nombre d'attributs :
create table joueurs (
Id_joueur int,
Prénom varchar(255),
Nom varchar(255),
Age int,
DateNaissance date,
Hauteur int,
Poids int,
Nationalité char(3),
Club varchar(255),
Valeur_eur int,
Salaire_eur int,
Position char(3)
);
On vérifie :
show columns in joueurs;
Ce qui doit retourner :
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| Id_joueur | int(11) | YES | | NULL | |
| Prénom | varchar(255) | YES | | NULL | |
| Nom | varchar(255) | YES | | NULL | |
| Age | int(11) | YES | | NULL | |
| DateNaissance | date | YES | | NULL | |
| Hauteur | int(11) | YES | | NULL | |
| Poids | int(11) | YES | | NULL | |
| Nationalité | char(3) | YES | | NULL | |
| Club | varchar(255) | YES | | NULL | |
| Valeur_eur | int(11) | YES | | NULL | |
| Salaire_eur | int(11) | YES | | NULL | |
| Position | char(3) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
12 rows in set (0,001 sec)
Puis nous importons les données du fichier player.csv en indiquant ses caractéristiques, ainsi que le fait que nous ignorons la première ligne qui contient les entêtes de colonnes :
LOAD DATA INFILE 'players.csv' INTO TABLE joueurs FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;
Si tout va bien, nous obtenons ce message :
Query OK, 148 rows affected (0,002 sec)
Records: 148 Deleted: 0 Skipped: 0 Warnings: 0
Sinon… c’est que quelque chose s’est mal passé, il faut corriger avant d’aller plus loin.
On peut visualiser le contenu de la table :
select * from joueurs;
Ce qui donne une longue liste (la sortie est tronquée) :
+-----------+-------------------+--------------------+------+---------------+---------+-------+--------------+--------------------------------+------------+-------------+----------+
| Id_joueur | Prénom | Nom | Age | DateNaissance | Hauteur | Poids | Nationalité | Club | Valeur_eur | Salaire_eur | Position |
+-----------+-------------------+--------------------+------+---------------+---------+-------+--------------+--------------------------------+------------+-------------+----------+
| 1 | Lionel Andrés | Messi | 32 | 1987-06-24 | 170 | 72 | ARG | FC Barcelona | 96 | 565 | RW |
| 2 | Cristiano Ronaldo | dos Santos | 34 | 1985-02-05 | 187 | 83 | PRT | Juventus | 59 | 405 | LW |
| 3 | Neymar | da Silva | 27 | 1992-02-05 | 175 | 68 | BRA | Paris Saint-Germain | 106 | 290 | CAM |
| 4 | Jan | Oblak | 26 | 1993-01-07 | 188 | 87 | SGP | Atlético Madrid | 78 | 125 | GK |
| 5 | Eden | Hazard | 28 | 1991-01-07 | 175 | 74 | BLX | Real Madrid | 90 | 470 | LW |
| 6 | Kevin | De Bruyne | 28 | 1991-06-28 | 181 | 70 | BLX | Manchester City | 90 | 370 | CAM |
| 7 | Marc-André | ter Stegen | 27 | 1992-04-30 | 187 | 85 | DEU | FC Barcelona | 68 | 250 | GK |
| 8 | Virgil | van Dijk | 27 | 1991-07-08 | 193 | 92 | NPL | Liverpool | 78 | 200 | LF |
| 9 | Luka | Modrić | 33 | 1985-09-09 | 172 | 66 | HRV | Real Madrid | 45 | 340 | CM |
…
| 143 | César | Azpilicueta | 29 | 1989-08-28 | 178 | 76 | ESP | Chelsea | 26 | 145 | RB |
| 144 | José María | Callejón | 32 | 1987-02-11 | 178 | 73 | ESP | Napoli | 24 | 96 | RM |
| 145 | İlkay | Gündoğan | 28 | 1990-10-24 | 180 | 80 | DEU | Manchester City | 31 | 180 | CM |
| 146 | Paulo | Bezzera | 30 | 1988-07-25 | 183 | 80 | BRA | Guangzhou Evergrande Taobao FC | 29 | 43 | CM |
| 147 | Kyle | Walker | 29 | 1990-05-28 | 183 | 70 | UK | Manchester City | 26 | 165 | RB |
| 148 | Sergio | Canales | 28 | 1991-02-16 | 176 | 65 | ESP | Real Betis | 32 | 44 | CM |
+-----------+-------------------+--------------------+------+---------------+---------+-------+--------------+--------------------------------+------------+-------------+----------+
148 rows in set (0,000 sec)
¶ Exemples de requêtes SQL
¶ SELECT
L'instruction SELECT nous permet de sélectionner des données dans une base de données. Il s'agit de l'instruction la plus courante en SQL, alors souvenez-vous de la syntaxe ci-dessous.
Sélectionnons les colonnes Prénom, Nom, hauteur dans la table joueurs :
SELECT Prénom, Nom, Hauteur
FROM joueurs;
Sortie :
+-------------------+--------------------+---------+
| Prénom | Nom | Hauteur |
+-------------------+--------------------+---------+
| Lionel Andrés | Messi | 170 |
| Cristiano Ronaldo | dos Santos | 187 |
| Neymar | da Silva | 175 |
| Jan | Oblak | 188 |
| Eden | Hazard | 175 |
| Kevin | De Bruyne | 181 |
| Marc-André | ter Stegen | 187 |
| Virgil | van Dijk | 193 |
| Luka | Modrić | 172 |
| Mohamed | Salah | 175 |
…
+-------------------+--------------------+---------+
En savoir plus : https://www.w3schools.com/sql/sql_select.asp
¶ SELECT DISTINCT
Nous utilisons l'instruction SELECT DISTINCT chaque fois que nous voulons sélectionner des éléments uniques dans une colonne.
Voyons ainsi une synthèse des Positions de la table joueurs.
SELECT DISTINCT Position
FROM joueurs;
Sortie :
+----------+
| Position |
+----------+
| RW |
| LW |
| CAM |
| GK |
| LF |
| CM |
| ST |
| CDM |
| LM |
| RM |
| RB |
| CF |
| CB |
| RAM |
| LAM |
+----------+
En savoir plus : https://www.w3schools.com/sql/sql_distinct.asp
¶ La clause WHERE
La clause WHERE nous permet d'ajouter une condition à notre requête. Seules les valeurs qui satisfont la condition seront renvoyées dans la table de sortie.
Créons une requête qui filtre uniquement les joueurs dont la hauteur est supérieure à 180 cm :
SELECT Prénom, Nom, Hauteur
FROM joueurs
WHERE Hauteur>180;
Sortie:
+-------------------+--------------------+---------+
| Prénom | Nom | Hauteur |
+-------------------+--------------------+---------+
| Cristiano Ronaldo | dos Santos | 187 |
| Jan | Oblak | 188 |
| Kevin | De Bruyne | 181 |
| Marc-André | ter Stegen | 187 |
| Virgil | van Dijk | 193 |
| Kalidou | Koulibaly | 187 |
| Harry | Kane | 188 |
| Alisson | Becker | 191 |
| David | De Gea | 192 |
| Giorgio | Chiellini | 187 |
| Sergio | Ramos | 184 |
| Luis | Alberto | 182 |
…
+-------------------+--------------------+---------+
En savoir plus : https://www.w3schools.com/sql/sql_where.asp
¶ Les fonctions COUNT(), AVG() AND SUM()
Certaines des fonctions les plus courantes en SQL sont le dénombrement COUNT(), la moyenne AVG() et la somme arithmétique SUM().
Si nous considérons la clause WHERE que nous avons vue auparavant, voici à quoi ressemblerait la syntaxe de COUNT(), AVG() et SUM() :
Voyons les informations que nous pouvons obtenir du club du FC Barcelone.
Requête 1 : Voyons combien de joueurs de Barcelone sont dans notre table :
SELECT COUNT(id_joueur)
FROM joueurs
WHERE Club="FC Barcelona";
Sortie 1 :
+------------------+
| COUNT(id_joueur) |
+------------------+
| 15 |
+------------------+
Requête 2 : Calculer la taille moyenne des joueurs de Barcelone
SELECT AVG(Hauteur)
FROM joueurs
WHERE Club="FC Barcelona";
Sortie 2 :
+--------------+
| AVG(Hauteur) |
+--------------+
| 181.2667 |
+--------------+
Requête 3 : obtenir la somme des salaires des joueurs de Barcelone
SELECT SUM(salaire_eur)
FROM joueurs
WHERE Club="FC Barcelona";
Sortie 3 :
+------------------+
| SUM(salaire_eur) |
+------------------+
| 3945 |
+------------------+
En savoir plus : https://www.w3schools.com/sql/sql_count_avg_sum.asp
¶ ORDER BY
Chaque fois que nous voudrons trier la table de sortie dans l'ordre croissant ou décroissant, nous utilisons le mot-clé ORDER BY.
Découvrons qui sont les plus grands joueurs de notre table :
SELECT Prénom, Nom, Hauteur
FROM joueurs
ORDER BY Hauteur DESC;
Sortie :
+-------------------+--------------------+---------+
| Prénom | Nom | Hauteur |
+-------------------+--------------------+---------+
| Thibaut | Courtois | 199 |
| Gianluigi | Donnarumma | 196 |
| Jiří | Pavlenka | 196 |
| Wojciech | Szczęsny | 195 |
| Niklas | Süle | 195 |
| Zlatan | Ibrahimović | 195 |
| Gerard | Piqué | 194 |
| Virgil | van Dijk | 193 |
| Samir | Handanovič | 193 |
| Manuel | Neuer | 193 |
| Edin | Džeko | 193 |
…
+-------------------+--------------------+---------+
En savoir plus : https://www.w3schools.com/sql/sql_orderby.asp
¶ Les opérateurs AND et OR
Nous pouvons combiner la clause WHERE avec différents opérateurs logiques tels que AND ou OR.
Voici la différence entre ces opérateurs :
ANDaffiche un enregistrement si toutes les conditions sont VRAIEOUaffiche un enregistrement si l'une des conditions est VRAIE
Voyons quelques exemples avec AND/OR.
Requête : Afficher les joueurs de France (FRA) d'une taille supérieure à 180 cm.
SELECT Prénom, Nom, Hauteur, Nationalité
FROM joueurs
WHERE Hauteur>180 AND Nationalité="FRA";
Sortie :
+-----------+------------+---------+--------------+
| Prénom | Nom | Hauteur | Nationalité |
+-----------+------------+---------+--------------+
| Paul | Pogba | 191 | FRA |
| Hugo | Lloris | 188 | FRA |
| Aymeric | Laporte | 189 | FRA |
| Karim | Benzema | 185 | FRA |
| Samuel | Umtiti | 182 | FRA |
| Raphaël | Varane | 191 | FRA |
| Clément | Lenglet | 186 | FRA |
| Lucas | Hernández | 182 | FRA |
| Stéphane | Ruffier | 188 | FRA |
+-----------+------------+---------+--------------+
9 rows in set (0,000 sec)
Requête : Afficher les joueurs français (FRA) ou mesurant plus de 180 cm.
SELECT Prénom, Nom, Hauteur, Nationalité
FROM joueurs
WHERE Hauteur>180 OR Nationalité="FRA";
Sortie :
+-------------------+--------------------+---------+--------------+
| Prénom | Nom | Hauteur | Nationalité |
+-------------------+--------------------+---------+--------------+
| Cristiano Ronaldo | dos Santos | 187 | PRT |
| Jan | Oblak | 188 | SGP |
| Kevin | De Bruyne | 181 | BLX |
| Marc-André | ter Stegen | 187 | DEU |
| Virgil | van Dijk | 193 | NPL |
| Kylian | Mbappé | 178 | FRA |
| Kalidou | Koulibaly | 187 | SEN |
| Harry | Kane | 188 | UK |
| Alisson | Becker | 191 | BRA |
| David | De Gea | 192 | ESP |
| NGolo | Kanté | 168 | FRA |
…
| Mario | Mandžukić | 190 | HRV |
| Jérôme | Boateng | 192 | DEU |
| Paulo | Bezzera | 183 | BRA |
| Kyle | Walker | 183 | UK |
+-------------------+--------------------+---------+--------------+
En savoir plus : https://www.w3schools.com/sql/sql_and_or.asp
¶ L'opérateur BETWEEN
L'opérateur BETWEEN sélectionne des valeurs (par exemple, des nombres, du texte ou des dates) dans une plage donnée. Cet opérateur est inclusif, ce qui signifie que les valeurs de début et de fin sont incluses.
Découvrons ainsi quels joueurs ont entre 20 et 24 ans :
SELECT Prénom, Nom, Age
FROM joueurs
WHERE Age BETWEEN 20 AND 24;
Sortie :
+--------------+--------------------+------+
| Prénom | Nom | Age |
+--------------+--------------------+------+
| Kylian | Mbappé | 20 |
| Raheem | Sterling | 24 |
| Leroy | Sané | 23 |
| Milan | Škriniar | 24 |
| Joshua | Kimmich | 24 |
| Gianluigi | Donnarumma | 20 |
| Frenkie | de Jong | 22 |
| Niklas | Süle | 23 |
| Sergej | Milinković-Savić | 24 |
| Rodrigo | Hernández | 23 |
| Saúl | Ñíguez | 24 |
| José María | Giménez | 24 |
| Clément | Lenglet | 24 |
| Bruno | Borges | 24 |
| Kai | Havertz | 20 |
| Ousmane | Dembélé | 22 |
| Kepa | Arrizabalaga | 24 |
| Bamidele | Alli | 23 |
| Lucas | Hernández | 23 |
| Arthur | Ramos | 22 |
| Leon | Goretzka | 24 |
| Julian | Brandt | 23 |
| Kingsley | Coman | 23 |
| Serge | Gnabry | 23 |
+--------------+--------------------+------+
24 rows in set (0,000 sec)
En savoir plus : https://www.w3schools.com/sql/sql_between.asp
¶ Les opérateurs IN et NOT IN
Nous utilisons l'opérateur IN ou NOT IN lorsque nous voulons spécifier plusieurs valeurs dans une clause WHERE.
Obtenons une liste de joueurs qui jouent soit pour le FC Barcelone, soit pour le Real Madrid :
SELECT Prénom, Nom, Age, Club
FROM joueurs
WHERE Club IN ('FC Barcelona', 'Real Madrid');
Sortie :
+----------------+------------+------+--------------+
| Prénom | Nom | Age | Club |
+----------------+------------+------+--------------+
| Lionel Andrés | Messi | 32 | FC Barcelona |
| Eden | Hazard | 28 | Real Madrid |
| Marc-André | ter Stegen | 27 | FC Barcelona |
| Luka | Modrić | 33 | Real Madrid |
| Sergio | Ramos | 33 | Real Madrid |
| Luis | Alberto | 32 | FC Barcelona |
| Sergio | Busquets | 30 | FC Barcelona |
| Antoine | Griezmann | 28 | FC Barcelona |
| Thibaut | Courtois | 27 | Real Madrid |
| Gerard | Piqué | 32 | FC Barcelona |
| Toni | Kroos | 29 | Real Madrid |
| Carlos | Casimiro | 27 | Real Madrid |
| Karim | Benzema | 31 | Real Madrid |
| Jordi | Alba | 30 | FC Barcelona |
| Keylor | Navas | 32 | Real Madrid |
| Samuel | Umtiti | 25 | FC Barcelona |
| Isco | Román | 27 | Real Madrid |
| Ivan | Rakitić | 31 | FC Barcelona |
| Frenkie | de Jong | 22 | FC Barcelona |
| Raphaël | Varane | 26 | Real Madrid |
| Clément | Lenglet | 24 | FC Barcelona |
| Norberto | Murara | 29 | FC Barcelona |
| Daniel | Carvajal | 27 | Real Madrid |
| Gareth | Bale | 29 | Real Madrid |
| Marcelo | Vieira | 31 | Real Madrid |
| James | Rodríguez | 27 | Real Madrid |
| Ousmane | Dembélé | 22 | FC Barcelona |
| Arthur | Ramos | 22 | FC Barcelona |
| Arturo | Vidal | 32 | FC Barcelona |
+----------------+------------+------+--------------+
29 rows in set (0,000 sec)
En savoir plus : https://www.w3schools.com/sql/sql_in.asp
¶ Les opérateurs LIKE et NOT LIKE
Nous utilisons l'opérateur LIKE lorsque nous voulons rechercher un modèle spécifique dans une colonne. C’est un opérateur que nous avons déjà expérimenté plus haut.
Il existe différents caractères génériques (jokers) que nous pouvons utiliser dans le modèle. Voici deux des jokers les plus courants :
%: représente zéro, un ou plusieurs caractères_: représente un seul caractère
Filtrons les joueurs dont les prénoms commencent par la lettres:
SELECT Prénom, Nom
FROM joueurs
WHERE Prénom LIKE 's%';
Sortie :
+-----------+--------------------+
| Prénom | Nom |
+-----------+--------------------+
| Sergio | Agüero |
| Sergio | Ramos |
| Sergio | Busquets |
| Samir | Handanovič |
| Sadio | Mané |
| Samuel | Umtiti |
| Sergej | Milinković-Savić |
| Saúl | Ñíguez |
| Serge | Gnabry |
| Stefan | de Vrij |
| Stéphane | Ruffier |
| Salvatore | Sirigu |
| Sokratis | Papastathopoulos |
| Sergio | Canales |
+-----------+--------------------+
14 rows in set (0,000 sec)
Il existe de nombreux modèles que vous pouvez créer avec les % et _. Vous pouvez en trouver quelques-unes ici : https://www.w3schools.com/sql/sql_like.asp
A vous de fabriquer une requête sophistiquée à partir de ce jeu de données !