Cet exercice consiste à effectuer des requêtes simples de sélection de contenu dans une table afin de découvrir ou de tester les différentes possibilités offertes par le langage SQL.
Vous devez avoir une base de données (Maria DB ou MySQL) à votre disposition pour effectuer cet exercice. Vous pouvez traiter les différentes questions en ligne de commande ou via un gestionnaire de base de données comme phpMyAdmin, Adminer, MySQL Workbench, Sequel Pro, etc.
Mise en place de la base de données
Connectez-vous à votre base de données.
Vous pouvez vous connecter à votre base de données en utilisant un logiciel de gestion comme phpMyAdmin. qui vous permettra de saisir toutes les commandes demandées dans l’exercice.
Si vous souhaitez utiliser les lignes de commande, vous pouvez utiliser la commande suivante pour vous connecter en remplaçant localhost par l’adresse de votre base (si nécessaire) et root par votre identifiant.
mysql -h localhost -u root -p
Si vous avez les droits, créez une nouvelle base de données intitulées france, sinon sélectionnez votre base de données.
Si vous a travaillez avec phpMyAdmin, il suffit d’utiliser les options d’interface pour créer la base et/ou l’ouvrir.
Voici la commande pour créer une base de données en ligne de commande :
CREATE DATABASE france CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
La commande suivante permet de sélectionner une base de données existante.
USE france
Créez la table france_villes en utilisant la requête suivante :
CREATE TABLE IF NOT EXISTS `france_villes` ( `ville_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `ville_departement` varchar(3) DEFAULT NULL, `ville_slug` varchar(255) DEFAULT NULL, `ville_nom` varchar(45) DEFAULT NULL, `ville_nom_simple` varchar(45) DEFAULT NULL, `ville_nom_reel` varchar(45) DEFAULT NULL, `ville_nom_soundex` varchar(20) DEFAULT NULL, `ville_nom_metaphone` varchar(22) DEFAULT NULL, `ville_code_postal` varchar(255) DEFAULT NULL, `ville_commune` varchar(3) DEFAULT NULL, `ville_code_commune` varchar(5) NOT NULL, `ville_arrondissement` smallint(3) unsigned DEFAULT NULL, `ville_canton` varchar(4) DEFAULT NULL, `ville_amdi` smallint(5) unsigned DEFAULT NULL, `ville_population_2010` mediumint(11) unsigned DEFAULT NULL, `ville_population_1999` mediumint(11) unsigned DEFAULT NULL, `ville_population_2012` mediumint(10) unsigned DEFAULT NULL COMMENT 'approximatif', `ville_densite_2010` int(11) DEFAULT NULL, `ville_surface` float DEFAULT NULL, `ville_longitude_deg` float DEFAULT NULL, `ville_latitude_deg` float DEFAULT NULL, `ville_longitude_grd` varchar(9) DEFAULT NULL, `ville_latitude_grd` varchar(8) DEFAULT NULL, `ville_longitude_dms` varchar(9) DEFAULT NULL, `ville_latitude_dms` varchar(8) DEFAULT NULL, `ville_zmin` mediumint(4) DEFAULT NULL, `ville_zmax` mediumint(4) DEFAULT NULL, PRIMARY KEY (`ville_id`), UNIQUE KEY `ville_code_commune_2` (`ville_code_commune`), UNIQUE KEY `ville_slug` (`ville_slug`), KEY `ville_departement` (`ville_departement`), KEY `ville_nom` (`ville_nom`), KEY `ville_nom_reel` (`ville_nom_reel`), KEY `ville_code_commune` (`ville_code_commune`), KEY `ville_code_postal` (`ville_code_postal`), KEY `ville_longitude_latitude_deg` (`ville_longitude_deg`,`ville_latitude_deg`), KEY `ville_nom_soundex` (`ville_nom_soundex`), KEY `ville_nom_metaphone` (`ville_nom_metaphone`), KEY `ville_population_2010` (`ville_population_2010`), KEY `ville_nom_simple` (`ville_nom_simple`) ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=36831 ;
- Département : numéro du département.
- Slug : identifiant unique en minuscule, sans accent et sans espace. Peut servir pour faire les URLs d’un site web.
- Nom : nom en majuscule et sans accent.
- Nom simple : nom en minuscule, sans accent et avec les tirets remplacés par des espaces. Peut être utilisé pour faire une recherche lorsqu’on ne sait pas si le nom de ville possède un tiret ou des espaces (ex : “Saint-Étienne” possède un tiret comme séparateur, tandis que “Le Havre” possède un espace comme séparateur)
- Nom reel : nom correct avec les accents
- Nom soundex : soundex du nom de la ville (permet de trouver des villes qui se prononcent presque pareil) [ajouté le 31/10/2013]
- Nom metaphone : metaphone du nom de la ville (même utilité que le soundex) [ajouté le 31/10/2013]
- Code postal : code postal de la ville. Si la ville possède plusieurs code postaux, ils sont tous listés et séparés par un tiret [ajouté le 31/10/2013]
- Numéro de commune : numéro de la commune dans le département. Combiné avec le numéro de département, il permet de créer le code INSEE sous 5 caractères.
- Code commune (ou code INSEE) : identifiant unique sous 5 caractères
- Arrondissement : arrondissement de la ville
- Canton : canton de la ville
- Population en 2010 : nombre d’habitants lors du recensement de 2010
- Population en 1999 : nombre d’habitants lors du recensement de 1999
- Population en 2012 (approximatif) : valeur exprimée en centaine
- Densité en 2010 : nombre d’habitants au kilomètre carré arrondie à l’entier. Calculé à partir du nombre d’habitant et de la surface de la ville [corrigé le 02/07/2014]
- Surface / superficie : surface de la ville en kilomètre carrée [corrigé le 02/07/2014]
- Longitude/latitude en degré : géolocalisation du centre de la ville. Permet de localiser la ville sur une carte (exemple : carte Google Maps) [ajouté le 31/10/2013, corrigé le 07/11/2013]
- Longitude/latitude en GRD : géolocalisation exprimée en GRD
- Longitude/latitude en DMS (Degré Minute Seconde) : géolocalisation exprimée en Degré Minute Seconde
- Altitude minimale/maximale : hauteur minimum et maximum de la ville par rapport au niveau de l’eau
Source et licence des données :
La table et les enregistrement utilisés pour cet exercice sont tirés du site https://sql.sh et mis à disposition selon les termes de la licence Creative Commons Attribution – Partage dans les Mêmes Conditions 4.0 International.
Vous êtes libre de partager, distribuer ou utiliser ces données, pour des utilisations commerciales ou non, à condition de conserver cette licence et d’attribuer un lien vers le site sql.sh.
Importez les données dans la table en utilisant les fichiers disponibles ci-dessous.
Si votre serveur le permet, utilisez le fichier france_cities.sql. Mais si les paramètres de votre serveur limite la taille d’import pour les gros fichiers (comme sur MAMP qui limite par défaut à 2Mo), utilisez alors les six fichiers france_cities_x.sql.
Pour télécharger un fichier, faites un clic droit sur le fichier désiré et choisissez l’option Télécharger.
Pour télécharger plusieurs fichiers, sélectionnez les fichiers désirés puis cliquez sur Télécharger les sélectionnés. Les fichiers seront téléchargés sous la forme d’une archive .zip. Veillez à bien extraire le contenu de l’archive avant de commencer à travailler.
Interrogation de la base de données
Rédigez et exécutez la requête permettant d’obtenir la liste de toutes les villes.
La requête SELECT fonctionne selon la syntaxe suivante SELECT champs_à_afficher FROM nom_de_la_table. Le symbole * est utilisé pour ramener la totalité des champs de la table.
SELECT * FROM france_villes
Rédigez et exécutez la requête permettant d’obtenir la liste des noms réels de toutes les villes.
Pour ne pas ramener la totalité des champs d’un enregistrement, il suffit de remplacer le symbole * par le champ désiré.
SELECT ville_nom_reel FROM france_villes
Si l’on souhaite plusieurs champs, il faut alors séparer leurs noms par une virgule.
SELECT ville_nom_reel, ville_departement FROM france_villes
Rédigez et exécutez la requête permettant d’obtenir la ville dont le nom est Lyon.
La commande WHERE permet d’extraire uniquement les enregistrements d’une base de données qui respectent une condition donnée.
SELECT * FROM france_villes WHERE ville_nom = 'Lyon'
Rédigez et exécutez la requête permettant d’obtenir la liste de toutes les villes dont le nom contient le mot Franche.
L’opérateur LIKE est utilisé dans la clause WHERE pour effectuer une recherche en fonction d’un modèle. Le caractère % est un caractère joker qui remplace tous les autres caractères.
SELECT * FROM france_villes WHERE ville_nom_reel LIKE '%Franche%'
Modifiez la requête précédente pour faire en sorte que la liste des résultats soit classée par ordre alphabétique sur le nom de la ville.
La commande ORDER BY permet de trier les résultats d’une requête. Il est possible de trier les lignes sur un ou plusieurs champs, par ordre croissant ASC ou décroissant DESC.
SELECT * FROM france_villes WHERE ville_nom_reel LIKE '%Franche%' ORDER BY ville_nom_reel ASC
Rédigez et exécutez la requête permettant d’obtenir la liste de toutes les villes dont le nom commence par la lettre F. Les résultats doivent être classés par code postal décroissant.
Le modèle à utiliser avec LIKE ne doit contenir qu’une seule fois le symbole %, à la suite de la lettre F.
SELECT * FROM france_villes WHERE ville_nom LIKE 'F%' ORDER BY ville_code_postal DESC
Si nous utilisons LIKE = '%F', nous recherchons alors les noms se terminant par la lettre F.
SELECT * FROM france_villes WHERE ville_nom LIKE '%F' ORDER BY ville_code_postal DESC
Rédigez et exécutez la requête permettant d’obtenir la liste de toutes les villes dont le nom contient le mot bains ou le mot bois.
L’opérateur OR correspondant à un OU logique permet de préciser au moins deux conditions valables individuellement. Les résultats attendus doivent donc correspondre à au moins une des conditions.
SELECT * FROM france_villes WHERE ville_nom LIKE '%bains%' OR ville_nom LIKE '%bois%'
Rédigez et exécutez la requête permettant d’obtenir la liste de toutes les villes dont le nom contient le mot bain et qui se trouve dans le département de l’Ardèche (07). Les résultats doivent être classés par ordre alphabétique sur le nom des villes.
L’opérateur AND correspondant à un ET logique permet de préciser plusieurs conditions obligatoires. Les résultats attendus doivent donc correspondre à l’ensemble des conditions.
SELECT * FROM france_villes WHERE ville_nom LIKE '%bains%' AND ville_departement = '07' ORDER BY ville_nom ASC
Rédigez et exécutez la requête permettant d’obtenir la liste de toutes les villes dont le nom contient le mot bains et dont le code postal est supérieur à 20000. Les résultats doivent être classés par ordre croissant sur le code postal.
La tableau suivant présente la liste des opérateurs de comparaison.
| Opérateur | Description |
|---|---|
= | Égale à |
<> ou != | Différent de |
> | Supérieur à |
>= | Supérieur ou égale à |
< | Inférieur à |
=< | Inférieur ou égale à |
IN | Ensemble de valeurs possibles |
BETWEEN | Valeur comprise dans un intervalle donné |
IS NULL | Valeur est nulle |
IS NOT NULL | Valeur n’est pas nulle |
LIKE | Recherche suivant un modèle avec les joker % (plusieurs caractères) ou _ (un seul caractère) |
SELECT * FROM france_villes WHERE ville_nom LIKE '%bains%' AND ville_code_postal > 20000 ORDER BY ville_code_postal ASC
Rédigez et exécutez la requête permettant d’obtenir la liste de toutes les villes dont le nom contient le mot bains et qui se trouve dans le département de l’Ardèche (07) ou dans le département de la Gironde (33).
Il y a plusieurs manières d’obtenir le même résultat.
- Vous pouvez utilisez des parenthèses pour réunir des conditions comme en mathématiques.
- L’opérateur
INqui permet de lister une série de valeurs souhaitées.
Il est possible d’utiliser les parenthèses pour réunion les deux conditions du OR. Les conditions entre parenthèses seront ainsi traitées en premier et le résultat sera utilisé comme une seule condition pour être traitée avec la dernière et l’opérateur AND.
SELECT * FROM france_villes WHERE ville_nom LIKE '%bains%' AND (ville_departement = '07' OR ville_departement = '33')
Solution n°2 :
Il est également possible d’utiliser l’opérateur IN qui permet de lister une série de valeurs souhaitées.
SELECT * FROM france_villes WHERE ville_nom LIKE '%bains%' AND ville_departement IN ('07','33')Rédigez et exécutez la requête permettant d’obtenir la liste de toutes les villes dont la population en 2010 est comprise entre 10000 et 50000.
Il existe plusieurs manières de résoudre cette question…
La commande BETWEEN permet définir un intervalle en séparant les valeurs avec l’opérateur AND.
Une solution consiste à utiliser deux comparaison avec l’opérateur AND et les signes >= et <=.
SELECT * FROM france_villes WHERE ville_population_2010 >= 10000 AND ville_population_2012 <= 50000
Solution n°2 :
La seconde solution consiste à utiliser la commande BETWEEN qui utilise l’opérateur AND pour définir un intervalle.
SELECT * FROM france_villes WHERE ville_population_2010 BETWEEN 10000 AND 50000
Rédigez et exécutez la requête permettant d’obtenir la liste des 10 villes les plus peuplées de France en 2010.
La commande LIMIT permet de déterminer le nombre d’enregistrements souhaités.
Pour obtenir le résultat demandé, il suffit de commencer par trier les villes par population décroissante puis d’utiliser la commande LIMIT afin de déterminer le nombre d’enregistrements souhaités.
SELECT * FROM france_villes ORDER BY ville_population_2010 DESC LIMIT 10
La commande LIMIT supporte également la possibilité de de spécifier un rang de départ (offset). La requête suivante sera donc équivalente.
SELECT * FROM france_villes ORDER BY ville_population_2010 DESC LIMIT 0, 10
La requête suivante donnera 10 résultats en sautant les trois premiers, le n°0, le n°1 et le n°2.
SELECT * FROM france_villes ORDER BY ville_population_2010 DESC LIMIT 2, 10
Rédigez et exécutez la requête permettant d’obtenir le nom de la ville, le numéro du département, la population en 2010 et la superficie des 10 villes ayant une population en 2010 inférieure à 10000 habitants, dont le nom contient le mot saint qui ont la plus grande superficie.
SELECT ville_nom, ville_departement, ville_population_2010, ville_surface FROM france_villes WHERE ville_population_2010 < 10000 AND ville_nom LIKE '%SAINT%' ORDER BY ville_surface DESC LIMIT 0,10
Sauf mention contraire*, l'article Requêtes SELECT simples avec MySQL [SQL02] et son contenu par Julien Crego sont mis à disposition selon les termes de la licence Creative Commons
Attribution - Pas d’Utilisation Commerciale - Partage dans les Mêmes Conditions 4.0 International
Cette licence vous permet de remixer, arranger, et adapter cette œuvre à des fins non commerciales tant que vous créditez la source en citant le nom des auteurs et que les nouvelles œuvres sont diffusées selon les mêmes conditions.
* Cette notice est intégrée automatiquement à la fin de chaque article de ce site.