SQL SELECT : les bases.
Réponse AJAX

Présentation de la commande SQL SELECT

SELECT est la seule commande du SQL permettant d'interroger une base de données. Il existe d'autres commandes pour manipuler les données (ajouter, modifier), manipuler les structures (bases, vues, tables), gérer les utilisateurs et leurs droits, créer des procédures stockées.

Le résultat d'un SELECT est appelé un jeu d'enregistrements (recordset en anglais).

Afficher l'ensemble d'une table (toutes les colonnes, tous les enregistrements)

SELECT *
FROM cours_tp_rep ;
tp_idtp_usr_idtp_rep_idtp_reptp_rep_valideetp_datetp_commentaire_proftp_commentaires_alltp_nb_posttp_note
111SELECT NomClt, PrénomClt FROM Client;42009-12-16 15:57:5411.0
112Select Distinct NumLivre From Emprunt Where Date >= #01/01/97#; - le distinct est nécessaire car un livre peut ?tre emprunté plusieurs fois. - la table "livre" est inutile ici : les champs qu'on teste et qu'on affiche sont présents dans "emprunt".42010-10-14 09:53:5011.0
113Select NumClt From Emprunt Where NumLivre = 6 And Date <#09/01/97#; 42010-10-13 11:22:4811.0
114Select Titre From Livre, Type Where Livre.NumType = Type.NumType And LibType = "roman";42009-10-23 17:57:5611.0
115Select Titre From Livre, Type, Auteur Where Livre.NumType = Type.NumType and Livre.NumAuteur = Auteur.NumAuteur And LibType = 'roman' and NomAuteur = "Pennac" Erreurs fréquentes : - Vérifier que le type est bien "Roman".42010-10-13 11:24:2311.0
116Select distinct Client.* From Client, Emprunt, Livre, Type Where Client.NumClt = Emprunt.NumClt And Emprunt.NumLivre=Livre.NumLivre And Livre.NumType = Type.NumType And LibType = "Science-fiction"; Erreurs courantes : - distinct obligatoire car un client a pu emprunter plusieurs livres de science-fiction, il appara?tra plusieurs fois. - select simplifiable gr?ce ? "client.*" 42010-10-20 10:50:2711.0
117Select Distinct Titre From Client, Emprunt, Livre, Type Where Client.NumClt = Emprunt.NumClt And Emprunt.NumLivre=Livre.NumLivre And Livre.NumType = Type.NumType And LibType = "Roman" And NomClt = "DURAND" And PrénomClt = "Marc"; - Distinct obligatoire car Marc Durand a pu emprunter plusieurs fois le m?me roman. - Vérifier qu'il s'agisse bien d'un roman.42010-10-14 10:40:3511.0
118Select Distinct Client.* From Client, Emprunt, Livre, Type, Auteur Where Client.NumClt = Emprunt.NumClt And Emprunt.NumLivre=Livre.NumLivre And Livre.NumType = Type.NumType And Livre.NumAuteur = Auteur.NumAuteur And NomAuteur = "William H. Gates" And LibType = "Essai"; - Vérifier que le libType est bien "essai". - Select simplifiable gr?ce ? "client.*"42010-10-13 11:47:2611.0
119Select * from Auteur Where NumAuteur Not In (Select NumAuteur from Livre Where NumType = 2); Erreur fréquente : - La requ?te imbriquée nécessite qu'une seule table.42010-10-19 11:26:2811.0
..............................
exécuté en 1.684 sec.

A partir du schéma relationnel ci-dessus, représentant la base d'un vendeur de logiciels (et qui va nous accompagner tout au long de ce cours), affichez l'ensemble de la table Produit.

Votre réponse n°1

Afficher seulement certains champs

Au lieu d'afficher toutes les colonnes (ou champs), on veut afficher uniquement le nom et le prénom (c'est à dire les colonnes qui ont été coloriées en orange sur le schéma ci-dessous).

usr_id
usr_name
usr_first_name
usr_login
usr_password
usr_year_study
...
1
PASTORESébastienspastore***********
6
 
540
DETESTYssertesteleve1***********
1
 
539
DUBUISSONChristophecdubuisson***********
2
 
538
BONVALOTRomainrbonvalot***********
2
 
537
LUCKFlorentfluck***********
1
 
536
TRANAlexandreatran***********
2
 
534
ESQUIROLGeorgesgesquirol***********
6
 
533
LEGRANDMarie-Pierremplegrand***********
6
 
532
TEXTORISAndréatextoris***********
6
 
...
      

On utilise pour ça la requête suivante :

SELECT usr_first_name, usr_name
FROM user ;
usr_first_nameusr_name
SébastienPastore
YsserDetest
ChristopheDubuisson
RomainBonvalot
FlorentLuck
AlexandreTran
GeorgesEsquirol
Marie-PierreLegrand
RémiFort
......
exécuté en 0.002 sec.

A partir de la base "vendeur de logiciels", affichez le nom et le prénom des clients.

Votre réponse n°2

Renommer les colonnes

Très utile pour clarifier l'affichage ou exploiter les données dans un langage de programmation :

SELECT usr_first_name as Prenom, usr_name as Nom
FROM user ;
PrenomNom
SébastienPastore
YsserDetest
ChristopheDubuisson
RomainBonvalot
FlorentLuck
AlexandreTran
GeorgesEsquirol
Marie-PierreLegrand
RémiFort
......
exécuté en 0.002 sec.

Le AS est facultatif, on peut donc écrire :

SELECT usr_first_name Prénom, usr_name Nom
FROM user ;
PrénomNom
SébastienPastore
YsserDetest
ChristopheDubuisson
RomainBonvalot
FlorentLuck
AlexandreTran
GeorgesEsquirol
Marie-PierreLegrand
RémiFort
......
exécuté en 0.002 sec.

A partir de la base "vendeur de logiciels", affichez le nom et le prénom des clients, en les renommant sans le C final.

Votre réponse n°3

Afficher seulement certains enregistrements : WHERE

Au lieu d'afficher tous les enregistrements (ou lignes), on veut afficher uniquement les utilisateurs qui s'appellent Romain (en orange sur la capture ci-dessous).

usr_id
usr_name
usr_first_name
usr_login
usr_password
usr_year_study
...
1
PASTORESébastienspastore***********
6
 
540
DETESTYssertesteleve1***********
1
 
539
DUBUISSONChristophecdubuisson***********
2
 
538
BONVALOTRomainrbonvalot***********
2
 
537
LUCKFlorentfluck***********
1
 
536
TRANAlexandreatran***********
2
 
534
ESQUIROLGeorgesgesquirol***********
6
 
533
LEGRANDMarie-Pierremplegrand***********
6
 
532
TEXTORISAndréatextoris***********
6
 
...
      

On utilise pour ça la clause WHERE :

SELECT usr_first_name as Prenom, usr_name as Nom
FROM user
WHERE usr_first_name='Romain';
PrenomNom
RomainBonvalot
RomainDumas
RomainDi Felice
RomainBarial
RomainViala
RomainConstant
RomainSicaud
RomainVanderhaeghen
RomainChaix
RomainBoehler
RomainDutoit
RomainVaudron
exécuté en 0.001 sec.

La condition WHERE est suivie de n'importe quelle expression booléenne, c'est à dire qui a pour résultat "vrai" ou "faux". Par exemple :

WHERE condition1 AND condition2
WHERE condition1 OR condition2
WHERE NOT condition
WHERE champ1 = "valeur"
WHERE champ1 > champ2 AND champ3 > champ4
etc.

Une erreur fréquente, chez les débutants, est de séparer les conditions par une virgule. Ceci ne marche pas :

WHERE contition1, condition2

A partir de la base "vendeur de logiciels", affichez les clients qui habitent Avignon.

Votre réponse n°4

Affichez les produits de catégorie n°5.

Votre réponse n°5

Trier les résultats

Pour que les résultats soient triés, on utilise la clause ORDER BY, et les paramètres ASC (pour un tri croissant, ascendant en anglais) ou DESC (pour un tri décroissant, descendant en anglais). Par exemple :

SELECT usr_first_name as Prenom, usr_name as Nom
FROM user
WHERE usr_name > ''
ORDER BY Nom ASC;
PrenomNom
IsisAbd Rabo
ChakibAbdallah
MalikAbdesslem
OlivierAbran
MoradAchachera
MimounaAdda-Benameur
OphélieAdolphe
SamiAhbibi
RachidAinab
......
exécuté en 0.008 sec.
SELECT usr_first_name as Prenom, usr_name as Nom
FROM user
ORDER BY Nom DESC;
PrenomNom
NordineZarqane
FlorentZamuner
DetestYsser
IbrahimYildirim
AnthonyXueref
MehdiWeibel
LouisVollekindt
MatthieuVoignier
MickaëlVogel
......
exécuté en 0.009 sec.

Le tri par défaut est croissant, on n'est donc pas obligé de préciser ASC. Par exemple :

SELECT usr_first_name as Prenom, usr_name as Nom
FROM user
WHERE usr_name > ''
ORDER BY Nom;
PrenomNom
IsisAbd Rabo
ChakibAbdallah
MalikAbdesslem
OlivierAbran
MoradAchachera
MimounaAdda-Benameur
OphélieAdolphe
SamiAhbibi
RachidAinab
......
exécuté en 0.008 sec.

Cela revient au même de trier sur le nom original du champ (usr_name), sur le nom renommé (Nom) ou sur la position du champ dans le SELECT (2).

On peut enchaîner plusieurs tris. Par exemple : utilisateurs nés en février, triés sur la date d'anniversaire. Pour ceux qui sont nés le même jour, on veut un classement par nom :

SELECT concat(day(usr_date_of_birth), '/', month(usr_date_of_birth)) as `Date d'anniversaire`, usr_first_name as Prenom, usr_name as Nom
FROM user
WHERE usr_date_of_birth LIKE '%-02-2_'
ORDER BY DAY(usr_date_of_birth), Nom;
Date d'anniversairePrenomNom
20/2ArnaudTaelman
21/2RenaudDeville
21/2SébastienNoguera
22/2CynthiaRobin
23/2JulietteBarnaud
23/2CyrilCovin
25/2YannisBarembaum
25/2SébastienPastore
25/2DamienReyre
26/2GeorghyFusco
27/2BenjaminMazauric
28/2TonyGuglielmi
28/2LilianMartinez
28/2StéphanieMorez
29/2NesrineRiane
exécuté en 0.001 sec.

A partir de la base "vendeur de logiciels", affichez les produits triés par nom de produit.

Votre réponse n°6

Affichez les clients triés par ville. Dans une même ville, les clients sont triés par nom, puis par prénom s'il y a des noms identiques.

Votre réponse n°7

La jointure : réunir des données éparpillées dans différentes tables

Si je regarde la table des petites annonces, je vois qu'il n'y figure que le numéro de l'utilisateur qui a déposé l'annonce, mais pas son nom ni son prénom. C'est normal : si on stockait son nom et son prénom pour chaque petite annonce, on l'aurait en plusieurs exemplaires. C'est ce qu'on appelle la redondance, et on essaie la plupart du temps de l'éviter dans les bases de données (ça fait perdre de la mémoire et ça rend les mises à jour plus difficiles).

SELECT * FROM cours_annonce ;
ann_idann_user_idann_titreann_prixann_texteann_is_htmlann_date_depot
204442<script>alert('Je suis un boulet')</script>testtest02015-01-27 00:16:27
206401Vend ordinateur150€Je vend un ordinateur fixe de la marque HP, possède un lecteur de carte mémoire, 3 port USB frontaux et 6 a l'arrière, lecteur CD/DVD, écran, clavier, souris et son tapis inclus. Vous pouvez le tester en Salle C352, c'est celui au fond à coté de la fenêtre.02015-10-27 12:22:56
208538Echange magnet père dodu0€Je cherche à échanger un magnet Martinique que j'ai déjà contre un de vos doubles02017-03-28 20:33:13
209482Hello ! Je donne 10 000€Bonjour, je n'ai pas un mot de passe sensible à la casse. Du coup, on m'a piraté mon compte, changé l'adresse mail, le mdp, je ne peux donc pas retrouver mon compte. Je demande donc à celui qui a hacké mon compte de me donner le mdp merci ! 12017-04-07 11:31:34
exécuté en 0 sec.

Toutefois, je peux afficher cette information puisque le nom et le prénom sont stockés dans la table user, et que ces deux tables sont reliées par le numéro de l'utilisateur qui a déposé l'annonce :

SELECT usr_id, usr_first_name, usr_name, ann_user_id, ann_titre
FROM user, cours_annonce
WHERE usr_id = ann_user_id ;
usr_idusr_first_nameusr_nameann_user_idann_titre
442SteevenThein442<script>alert('Je suis un boulet')</script>
401FlorentValay401Vend ordinateur
538AdrienGuillermet538Echange magnet père dodu
482PierrickDelamotte482Hello !
exécuté en 0 sec.

Notez bien l'importance du WHERE usr_id = ann_user_id. Si vous l'oubliez, vous obtenez des résultats incohérents (remarquez que chaque annonce a été affichée à côté de chaque utilisateur : SQL a fait toutes les combinaisons possibles, c'est ce qu'on appelle un produit cartésien et c'est rarement ce qu'on souhaite) :

SELECT usr_id, usr_first_name, usr_name, ann_user_id, ann_titre
FROM user, cours_annonce ;
usr_idusr_first_nameusr_nameann_user_idann_titre
1SébastienPastore442<script>alert('Je suis un boulet')</script>
1SébastienPastore401Vend ordinateur
1SébastienPastore538Echange magnet père dodu
1SébastienPastore482Hello !
77YsserDetest442<script>alert('Je suis un boulet')</script>
77YsserDetest401Vend ordinateur
77YsserDetest538Echange magnet père dodu
77YsserDetest482Hello !
76ChristopheDubuisson442<script>alert('Je suis un boulet')</script>
...............
exécuté en 0.005 sec.

A partir de la base "vendeur de logiciels", affichez les noms des produits, suivi du nom de la catégorie correspondante (jointure avec deux tables).

Votre réponse n°8

Affichez les noms et prénoms des clients, suivi des noms des produits qu'ils ont achetés (jointure avec trois tables).

Votre réponse n°9

La clause DISTINCT

Parfois, on est susceptible d'écrire une requête qui répond plusieurs fois la même réponse. Par exemple : qui a déjà déposé un commentaire sur les TP ?

SELECT concat (usr_first_name, ' ', usr_name) as Nom
FROM user, cours_vrac
WHERE usr_id = vrac_usr_id and vrac_type=14
ORDER BY usr_id desc
Nom
Tanguy Cordier
Leo Vincent
Romain Vaudron
Romain Vaudron
Romain Vaudron
Romain Vaudron
Pierre Touzeau
Pierre Touzeau
Thomas Meoni
...
exécuté en 0.005 sec.

Pour éviter que la même réponse soit donnée deux fois, on utilise la clause DISTINCT.

SELECT DISTINCT concat (usr_first_name, ' ', usr_name) as Nom
FROM user, cours_vrac
WHERE usr_id = vrac_usr_id and vrac_type=14
ORDER BY usr_id desc
Nom
Tanguy Cordier
Leo Vincent
Romain Vaudron
Pierre Touzeau
Thomas Meoni
Lucie Fort
Samy El Arabi
Romain Dutoit
Nicolas Cardona
...
exécuté en 0.005 sec.

Question à se poser

Il faut arriver à savoir quand le DISTINCT est obligatoire ou non, sinon vous serez pénalisés à l'examen. Essayez donc d'évaluer si la requête risque de multiplier les résultats ou pas. Par exemple :

Écrivez les requêtes SQL suivantes en vous demandant, à chaque fois, si le DISTINCT est nécessaire ou pas.

Dans quelles villes y a-t-il des clients ?

Votre réponse n°10

A quelle catégorie appartient le produit Firefox ?.

Votre réponse n°11

Quelles sont les catégories des produits achetés par Anthony PUECH (testez : vous devez obtenir 3 réponses) ?

Votre réponse n°12

La valeur NULL

On peut affecter une valeur spéciale à un champ : la valeur NULL.

A ne pas confondre avec une chaîne de caractères vide :

Quand on construit une base de données, on doit préciser pour chaque champ s'il peut recevoir la valeur NULL ou pas démonstration.

Afficher les clients (nom et prénom) dont on ne connaît pas la ville (testez : vous devez obtenir trois réponses).

Votre réponse n°13

Les expressions : pour afficher autre chose qu'un champ brut

On n'est pas obligé d'afficher des champs bruts, on peut afficher des expressions basées (ou non) sur les champs.

Une que j'utilise souvent est pour améliorer l'affichage du nom et du prénom :

SELECT CONCAT(usr_first_name, ' ', usr_name) AS Nom
FROM user
WHERE usr_special & 256 ;
Nom
Sébastien Pastore
Georges Esquirol
Alexa Strozzi
Gilles Girard
Florian Ballesta
exécuté en 0.001 sec.

Afficher l'année de naissance d'une personne (remarquez le IS NOT NULL qui permet de n'afficher que les utilisateurs dont on connaît la date de naissance) :

SELECT usr_login, YEAR(usr_date_of_birth) AS `annee de naissance`
FROM user
WHERE usr_date_of_birth IS NOT NULL AND usr_special & 5 = 5;
usr_loginannee de naissance
vbourgues2000
fbricchi1999
vcapelle1998
mdurand1999
jrakel2000
mreynaud1999
hbas2000
mbellanti1996
lbenoit2000
......
exécuté en 0.001 sec.

Afficher un prix TTC à partir d'un prix hors taxe : SELECT prix_HT * 1.2 FROM produits ;

Afficher le numéro des produits et le mois auquel ils ont été achetés.

Votre réponse n°14

Afficher le nom et le prénom des clients dans une seule colonne, séparés par un espace.

Votre réponse n°15

Utiliser SELECT sans table (sans FROM)

Parfois, on a besoin d'utiliser SELECT simplement pour évaluer une expression, sans aller chercher des données dans une table.

L'utilisation la plus courante est pour vérifier le fonctionnement des fonctions SQL. Par exemple, pour vérifier des fonctions de date ou de cryptage :

SELECT CURDATE();
CURDATE()
2019-08-21
exécuté en 0 sec.
SELECT NOW();
NOW()
2019-08-21 09:12:42
exécuté en 0 sec.
SELECT SHA1('mypassword');
SHA1('mypassword')
91dfd9ddb4198affc5c194cd8ce6d338fde470e2
exécuté en 0 sec.

Le comparateur de chaînes de caractères LIKE

Très utile pour les recherches, LIKE permet de comparer des chaînes entre elles en utilisant des caractères jokers, c'est à dire des caractères qui remplacent un ou plusieurs caractères.

% remplace 0, 1 ou plusieurs caractères. Par exemple :

_ (appelé l'underscore, sous la touche 8 du clavier) remplace 1 caractère. Par exemple :

Pour compliquer un peu : sous Access, le % s'écrit * et le _ s'écrit ? (respectant ainsi le formalisme des caractères jokers sous MS-DOS, et dans l'explorateur de fichiers en général).

Afficher les produits dont le nom contient Access (écriture SQL standard).

Votre réponse n°16

Afficher les produits dont le nom contient Access (écriture SQL sous Access).

Votre réponse n°17