Introduction au langage SQL


Il est très important d'investir dans l'apprentissage du langage SQL, langage de haut niveau. Cela épargne souvent du codage en PHP (ou dans un autre langage du meme niveau).
De plus certains traitements comme les tris et filtrages de texte sont exécutés plus efficacement par le serveur SQL que le script PHP.
Ici, il ne s'agit que d'une introduction "par l'exemple".

Bases du langage

  TP expérimenter les requêtes

  1. Il s'agit d'écrire et d'expérimenter des requêtes sur les bases test ou cdi

  2. Voici la structure de la base CDI

    3. documents (id_document,titre, auteur, genre, editeur, disponible)
inscrits (id_inscrit, nom, prenom, classe)
prets (id_pret, id_inscrit #, id_document #, date_pret, date_retour, retour)
demandes (id_demande, id_inscrit #, id_document #)

  3. Interrogations simples sur une table

    4. SELECT * FROM inscrits ;
SELECT count(*) FROM inscrits ;
SELECT count(*) AS nb FROM inscrits WHERE classe = '6a' ;
 autres fonctions AVG (moyenne), MIN, MAX, SUM 
" Quelle est la moyenne des valeurs d'un champ note dans une table carnet_notes ? "
SELECT AVG(note) as moyenne from carnet_notes ;
" Combien de documents disponibles sur leur total ?"
SELECT SUM(disponible) disponible, count(*) nombre FROM documents ;

SELECT auteur FROM documents ;
SELECT DISTINCT auteur FROM documents ;
SELECT titre, auteur FROM documents LIMIT 0,10 ;
SELECT titre, auteur FROM documents ORDER BY auteur ;
SELECT prenom, nom, classe FROM inscrits ORDER BY nom DESC LIMIT 0,10 ;
SELECT prenom, nom, classe FROM inscrits ORDER BY classe, nom, prenom ;
SELECT * FROM documents WHERE BINARY titre='ivanohé' ";

  4. Requêtes avec condition simple

    5. Une condition simple est construite avec les opérateurs de comparaison usuels < > = != portant sur des valeurs de champs. On dispose de quelques opérateurs spécifiques : LIKE et NOT LIKE 
    SELECT nom, prenom, classe FROM inscrits WHERE nom >= 'x' ;

SELECT DISTINCT titre, auteur FROM documents WHERE titre LIKE '%mis%' ;

SELECT titre, auteur FROM documents WHERE titre LIKE '% mis%' ;

SELECT auteur, titre FROM inscrits WHERE classe = '6a' ;

SELECT titre, auteur FROM documents WHERE titre LIKE '%mis%' ;

SELECT titre, auteur FROM documents WHERE titre LIKE '% mis%' ;

SELECT titre, auteur FROM documents WHERE titre LIKE '%mis%' AND auteur NOT LIKE '%hugo%' ;

  5. Requêtes avec condition composée

    6. AND, OR, NOT portant sur les conditions simples (attention, AND étant prioritaire sur OR, il peut être nécessaire de parenthéser) 
    SELECT nom,prenom,classe FROM inscrits WHERE (nom >='v' OR nom <'c') AND classe='6a' ;
SELECT titre, auteur FROM inscrits WHERE nom BETWEEN 'h' AND 'm' ;
    Les opérateurs d'appartenance à une liste discrète ou à un intervalle "continu" permettent d'abréger les conditions composées : [NOT] IN (liste_valeurs) et BETWEEN valeur1 AND valeur2. 
    SELECT nom,prenom,classe FROM inscrits WHERE classe IN ('6A','6c','6f');

  6. Approche notion de regroupement

    7. "Quels sont les effectifs par classe des inscrits ?"
    La fonction de comptage count() dans ce contexte s'applique à chaque ensemble résultant de la partition suivant la valeur du champ classe 
    SELECT classe, count(*) as nombre
FROM inscrits
GROUP BY classe ;
    "Quelles sont les classes dont l'effectif est limité à 24 élèves ?"
    Une condition portant sur les enregistrements regroupés s'exprime avec la clause HAVING (et non WHERE) 
    SELECT  classe, count(*) as nombre
FROM inscrits
GROUP BY classe
HAVING nombre <= 24;

  7. Jointures simples

    8. SELECT count(*) FROM inscrits, documents ;
SELECT count(*) as nb, 225*25 FROM inscrits, documents WHERE classe='6a';
    "Quels sont les documents actuellement empruntés ? ("actuellement" : tester le champ prets.retour=0)" Insérer avant quelques enregistrements dans la table prets, avec des requetes INSERT 
    SELECT d.id_document, titre, auteur, editeur
FROM  prets p, documents d
WHERE d.id_document = p.id_document 
AND retour=0 ;
    " Quels sont les documents empruntés par les élèves de la 6A ? " 
    SELECT prenom, nom, classe, auteur, titre
FROM inscrits, prets, documents
WHERE classe = '6a'
AND inscrits.id_inscrit = prets.id_inscrit
AND documents.id_document = prets.id_document ;

  8. Sous-requêtes

    9. Il faut attendre la version 4.1 de Mysql pour accepter les sous-requetes générales.
    A l'aide de Phpmyadmin (onglet opérations), faites une copie (structure seule) de la structure de la table documents dans la table doc. On peut alors copier des enregistrements de documents satisfaisant à une requete (le titre commence par "les"), dans la nouvelle table doc 
    INSERT INTO doc  SELECT * FROM documents WHERE titre LIKE 'les%' ;

  9. Requetes avec expressions régulières

    10. Le langage des expressions régulières est compris par Mysql. On place le motif dans une clause WHERE après le mot-clé REGEX.
    Exemples à tester et à commenter :
    SELECT * FROM documents WHERE titre REGEXP  '^l.*[rt]$' ;
SELECT * FROM documents WHERE titre REGEXP  '^a.{5}r' ;
SELECT * FROM documents WHERE titre REGEXP  ' et ' ;
SELECT * FROM documents WHERE titre REGEXP  'vie| bon' ;
SELECT * FROM documents WHERE titre REGEXP  '[[:digit:]]+' ;
SELECT * FROM documents WHERE titre REGEXP  '[a-z]{5}' ;

  10. Exercices

    1. Récupérer tous les documents prêtés (autrement dit les rendre de nouveau disponibles)
    2. Noter un nouveau pret, du document 11 à l'inscrit 15, avec une date de retour prévue dans 2 semaines
    3. Trouver les auteurs dont le nombre d'ouvrages au CDI est supérieur à 5
    4. Liste des élèves de la 6A ayant empruntés un document non retourné (retour = 0)