Diaporama n°30

Requêtes SQL

I. Requête sur une seul table

II. Sous-requête

III. Jointure

IV. Requête avec agrégation

V. Opérations ensemblistes

VI. Comment concevoir une requête SQL ?

I. Requêtes SQL sur une seule table :

Les requêtes les plus simples ...

SELECT * FROM city : projection sur toutes les colonnes (la table complète)

SELECT countrycode FROM city : projection sur une seule colonne

SELECT DISTINCT countrycode FROM city : projection sur une seule colonne avec suppression des doublons

NB : la suppression de doublons s'applique aux enregistrements, pas à une colonne (cf exercice)

Fonctions dites "d'agrégation"

Les fonctions suivantes, utilisées après SELECT, permettent d'obtenir des informations sur une colonne :

• COUNT(*), le nombre de lignes
• COUNT(attr), le nombre de ligne avec attr non NULL
• COUNT(DISTINCT attr), le nombre de valeurs différentes pour attr non NULL

Et pour les attributs numériques

• MIN(attr), la valeur minimale
• MAX(attr), la valeur maximale
• SUM(attr), la somme des valeurs
• AVG(attr), la valeur moyenne

SELECT COUNT(*) FROM country : le nombre de lignes de la table

SELECT COUNT(Capital) FROM country : le nombre de pays avec une capitale renseignée dans la table (non NULL)

SELECT COUNT(DISTINCT continent) FROM country : le nombre de continents différents (non NULL)

ATTENTION

SELECT COUNT(continent) FROM country ?

aurait compté le nombre de lignes ayant un attribut continent non NULL (239)

SELECT MIN(surfaceArea) FROM country : la plus petite surface (en km²)

Projection, renommage

SELECT name AS ville, Population AS pop FROM city : projection sur deux colonnes avec renommage

SELECT name, Population/surfaceArea AS densité FROM country : définition d'un nouvel attribut, avec renommage

SELECT * FROM city WHERE countryCode='FRA' : sélection / filtrage sur les lignes

Les conditions qui suivent WHERE

• les opérateurs de comparaison (sur les types numériques et textuels)

  • = <> < <= > >=

• les opérateurs booléens

  • AND OR NOT

• NB : on peut utiliser les opérations : +, -, *, /

• IS NULL, IS NOT NULL : pour la valeur spéciale NULL

• IN, NOT IN : pour tester l'appartenance (pratique avec les sous-requête)

SELECT * FROM city WHERE countrycode="CHN" AND population > 5000000 : exemple de clause WHERE

TRI : SELECT name, countrycode, population FROM city ORDER BY population DESC : tri décroissant des résultats (enregistrements ramenés)

NB : tri croissant : ASC

NB : on peut trier par un attribut qui n'est pas projeté

NB : on peut trier par un nouvel attribut (obtenu par calcul)

TRONCATURE : SELECT * FROM city LIMIT 10 : troncature des résultats (de 1 à 10)

SELECT * FROM city LIMIT 10 OFFSET 5 : troncature des résultats (de 6 à 15)

Synthèse partielle

Forme générale d'une requête ?

Exercices :

• La liste des codes pays et région

• La liste des codes pays et région (sans doublons)

NB : DISTINCT dédoublonne les enregistrements (et non une seule colonne)

• La liste des codes pays et région (sans doublons) ordonnées par code pays croissant

• La liste des noms de pays du contient "Asia"

• La liste des noms de pays de l'Asie, dont la population est supérieure à 100 millions d'habitants

• La liste des noms de pays de l'Asie et leur population, dont la population est supérieure à 100 millions, triée par ordre décroissant

• Les 5 pays les plus peuplés au monde

• Les 10 suivants ?

II. Sous-requêtes:

• Récupérer une information préalable pour pouvoir réaliser sa requête ...

Exemple de sous-requête :

Il peut être utile de récupérer une première information pour pouvoir formuler sa requête. C'est le concept de sous-requête ou requête imbriquée

Exemple :

Quel est le nom de la capitale de l'Australie ?

• Quel est l'identifiant de la capitale de l'Australie ?

SELECT Capital FROM country WHERE code="AUS"

• Quel est le nom de la ville qui a cet identifiant ?

SELECT name FROM city WHERE id= ...

Sous-requête (mettre des parenthèses)

SELECT name FROM city WHERE id=(SELECT Capital FROM country WHERE code="AUS")

NB : on a utilisé une table réduite à un seul élément comme valeur intermédiaire

NB : on peut aussi utiliser une colonne intermédiaire

Exemple :

Quels sont les noms des capitales européennes ?

• Quelles est la liste des identifiants des capitales européennes ?

• Quels sont les noms des villes ayant ces identifiants ?

NB : on utilise IN pour tester l'appartenance à cette "colonne intermédiaire"

NB : comment faire apparaître le nom du pays concerné ??? on utilise JOIN (à suivre)

Exercice :

• Donner la liste des langues parlées en "Switzerland"

NB : la sous-requête donne un unique code (clé primaire) : on utilise =

III. Jointures

Lorsque l'information à exploiter est répartie sur plusieurs tables, l'approche la plus systématique consiste à créer une nouvelle table contenant toute l'information utile.

C'est le rôle de l'opération de jointure JOIN

III.1 Jointure de deux tables

• table1 JOIN table2 ON condition : crée une nouvelle table contenant tous les attributs de table1 et tous ceux de table2

NB: condition est en général une égalité entre un attribut de table1 et un attribut de table2.

NB: ces attributs sont souvent des clés primaires ou étrangères

Exemple

• table1 JOIN table2 ON C = D

JOIN : on prolonge toutes les lignes de table1 par la/les lignes de table2 qui vérifient la condition

Résultat

Exemple 1

On veut prolonger les lignes de la table country par les informations concernant la capitale correspondante

L'attribut Capital de la table country doit correspondre à l'attribut id de la table city

SELECT * FROM country JOIN city ON Capital = id

La nouvelle table contient les colonnes des deux tables

Il y a deux colonnes name

Ambiguité / Lisibilité

Il est fréquent d'utiliser la notation table.attribut dans les jointures :

• pour savoir à quelle table appartient un attribut
• pour lever les ambiguités (on peut avoir un même nom d'attributs dans des tables différentes)

On écrira souvent :

• table1 JOIN table2 ON table1.id = table2.id

Il est également fréquent de renommer une table ave AS pour alléger l'écriture de la requête

• table1 AS t1 JOIN table2 as t2 ON t1.id = t2.id

ici, au lieu de

SELECT * FROM country JOIN city ON Capital = id

on écrira plutôt

SELECT * FROM country JOIN city ON country.Capital = city.id

ou encore

SELECT * FROM country AS co JOIN city AS ci ON co.Capital = ci.id

Il devient alors très simple d'afficher pour chaque pays : son code, son nom et le nom de sa capitale

NB : à chaque pays correspond 1 et 1 seule capitale

Exemple 2

On veut prolonger les lignes de la table city par les informations concernant les langues parlées

SELECT * FROM city JOIN countrylanguage ON ...

NB : à chaque ville correspond un countrycode, mais plusieurs langues. Les lignes de la table city se sont "multipliées"

Dans quelles villes de plus de 500000 hab. le français est-il parlé par plus de 15% de la population ?

on utilise la jointure précédente !

Plusieurs Jointures :

• Généralisation : table1 JOIN table2 JOIN ... JOIN tableN ON condition

• La table résultat contient (avant projection) toutes les colonnes de toutes les tables jointes (et donc parfois des colonnes identiques)

• condition est souvent une conjonction AND de conditions sur les identifiants présentes dans les différentes tables

NB : on évite de joindre plus de tables que nécessaire

NB : il est possible de joindre une table avec elle-même !

LEFT JOIN

C'est une variante de JOIN

Les enregistrements de la table de gauche (LEFT) seront toujours présents dans la table résultat, complétés par NULL pour les champs manquants.

NB : il existe de nombreuses autres variantes (hors programme) : RIGHT JOIN, FULL OUTER JOIN ...

NB : toutes les villes sont présentes. Celles qui sont des capitales sont complétées par les informations concernant le pays

IV. Requêtes avec agrégation

• fonction d'agrégation : pour obtenir des informations relatives à l'ensemble des lignes

• GROUP BY : pour regrouper les lignes de sa table en "paquets"

• HAVING : pour ne sélectionner que certains "paquets"

IV.1 Fonctions d'agrégation (suite)

Comme leur nom l'indique, les fonctions d'agrégation agrègent (regroupent) toutes les lignes pour calculer une unique valeur :

• COUNT(...), le nombre de lignes
• MIN(attr), la valeur minimale avec attr attribut numérique
• MAX(attr), la valeur maximale ...
• SUM(attr), la somme ...
• AVG(attr), la valeur moyenne ...

NB: le résultat de la requête est alors réduit à une seule ligne (sauf ...)

(NBNB : ... sauf utilisation de GROUP BY : il y a alors une ligne par groupe)

NB: ces fonctions ne peuvent pas s'utiliser dans une clause WHERE (faire une requête imbriquée ?)

SELECT COUNT(*) FROM city WHERE countrycode = 'FRA' : nombre de lignes avant agrégation

NB : le résultat est une table à 1 seule ligne

SELECT SUM(population) FROM city : somme sur l'attribut population

NB : le résultat est une table à 1 seule ligne

Erreur fréquente 1 :

Si d'autres attributs sont sélectionnés, le SGBD choisira une valeur au hasard ... le résultat obtenu est alors peu pertinent ... (sauf avec GROUP BY ...)

SELECT SUM(population), name FROM city

NB : le résultat est une table à 1 seule ligne (ici, c'est le premier nom de ville de la table qui a été conservé)

Par contre ...

Utiliser plusieurs fonctions d'agrégation est possible

• Donner la population minimale, maximale et moyenne des villes de la table city

NB : le résultat est une table à 1 seule ligne

Erreur fréquente 2 :

Il n'est pas possible d'utiliser de fonction d'agrégation dans les clauses d'un WHERE

Exemple (faux) : quel est le nom de la plus petite ville de la table city ?

NB : cf "ordre d'exécution d'une requête"

• WHERE sert à filtrer les lignes de la table initiale, alors que MIN agrège les lignes de la table finale ...

Par contre ...

on peut s'en sortir avec une sous-requête

NB : la sous-requête est évaluée en premier

IV.2 Regroupement en agrégats

Il est possible de regrouper les résultats selon les valeurs d'un attribut (ou plusieurs), en utilisant GROUP BY.

On divise ainsi la table en sous-tables (ou groupes ou agrégats).

GROUP BY

• Le résultat de la requête comporte une ligne par agrégat

• les fonctions d'agrégation s'appliquent alors à chaque agrégat.

Exemple GROUP BY

SELECT countrycode, count(*) FROM city GROUP BY countrycode :

• On regroupe (ou agrège) les villes par countrycode,

• Pour chaque groupe, on compte le nombre de lignes correspondant.

• Toutes les lignes d'un groupe ont le même countrycode

On obtient le nombre de villes par pays dans la table.

On obtient le nombre de villes par pays dans la table.

IV.3 Filtrage des agrégats

On peut filtrer ces agrégats en utilisant HAVING suivi d'une condition.

NB : pas de HAVING sans GROUP BY

NB : cette condition peut faire intervenir une fonction d'agrégation (contrairement à une condition associée à WHERE).

Exemple :

• SELECT countrycode, count(*) FROM city GROUP BY countrycode HAVING count(*) > 50 :

• on ne garde que agrégats de plus de 50 lignes

on ne garde que les pays dont plus de 50 villes sont dans la table

on ne garde que les pays dont plus de 50 villes sont dans la table

Exercice :

• Donner la surface totale de chaque continent

On regroupe les pays par continent, on additionne leurs surfaces

GROUP BY continent :

On regroupe les pays par continent, on additionne leurs surfaces

Version un peu plus lisible :

• renommage de colonne
• tri des résultats

Exercice :

• Donner la liste des langues parlées dans au moins 5 pays par plus de 5% de la population

Idée : on regroupe par langue, on compte le nombre de pays concernés

GROUP BY language :

NB : ne pas confondre WHERE et HAVING

SELECT language, count(*) FROM countrylanguage WHERE percentage > 5 GROUP BY language HAVING count(*) >= 5

• on ne tient compte que des langues parlées par plus de 5% de la population (WHERE)

• on regroupe par langue

• on ne sélectionne que les agrégats de plus de 5 lignes

V. Opérations ensemblistes

Avec deux tables ayant les mêmes colonnes, on peut réaliser les opérations ensemblistes suivantes :

• Union : table1 UNION table2

• Intersection : table1 INTERSECT table2

• Différence : table1 EXCEPT table2

• Les informations sur les villes de plus de 5 millions d'habitants, excepté les villes chinoises

Produit cartésien de deux tables

On peut réaliser le produit cartésien de deux tables quelconques en les séparant par une virgule après le mot clé FROM

SELECT * FROM city, countrylangage

attention : la virgule , se place entre les deux tables. Elle est peu visible.

attention : le produit cartésien de deux tables n'a en général pas de sens

On accole de manière mécanique la première ligne de la première table à chaque ligne de la seconde table, etc

NB : on privilégie souvent une jointure JOIN

Produit cartésien de deux tables

On accole de manière mécanique la première ligne de la première table à chaque ligne de la seconde table, etc

NB : la table n'a pas de "sens" ...

NB: table1, table2 est l'équivalent de table1 JOIN table2 (sans condition de jointure)

V. Comment concevoir une requête SQL ?

Quelle stratégie d'écriture d'une requête ?

• Bien connaître :

  • les mots clé du langage

  • l'ordre dans lequel les étapes sont réalisées

  • cf Mémo SQL

• Questions à se poser :

  • la requête concerne-t-elle une ou plusieurs tables ?

  • si plusieurs :

    • une sous-requête suffit-elle ?
    • sinon, quelle jointure ? avec quelles tables ? sur quels attributs ?

  • nécessité de regroupement (group by) ?

  • tri ? troncature des résultats ?

• Pièges à éviter :

  • pas de fonction d'agrégation dans une clause WHERE : utiliser une sous requête

  • une projection qui utilise une fonction d'agrégation regroupe toutes les lignes du groupe en une seule ligne

  • une jointure se fait souvent sur les clés primaires et/ou étrangères

• (HP) Optimisation de requête ? :
  • éviter de créer de trop grosses tables
  • éviter de faire trop de sous-requêtes