BD II: SQL I

Langage Manipulation de Données 1

Équipe BD

LPSM Université Paris-Cité

2025-09-19

Introduction

SQL : Structured Query Language

Le SQL est un langage permettant :

  • interrogation des données (requêtes)

  • définition des données et des contraintes structurelles sur celles-ci

  • manipulation des données (insertion, suppression, mise à jour)

  • définition des vues et des index

  • administration des bases de données

Plusieurs fois normalisés par l’ISO …

  • SQL-86, SQL-89, SQL-92, SQL-99,
  • …
  • SQL-2003, SQL-2008, SQL-2011, SQL-2016,
  • …

Nous utiliserons le dialecte PostgreSQL (https://www.postgresql.org)

La norme est révisée environ tous les 5 ans, acutellement ISO/IEC 9075-1:2023 de l’ISO (https://www.iso.org/)

Un langage de requête

  • Avec SQL, on envoit au serveur des requêtes composées d’instructions,

  • Les opérations de l’Algèbre relationnel ne sont pas directement disponibles.

La principale instruction est SELECT. Elle permet de combiner :

  • restriction/filtrage (sélection) \(\sigma\)

  • projection \(\pi\)

  • renommage \(\rho\)

  • et bien d’autres choses.

D’autres instructions pour définir et manipuler les données, administrer une BDD.

Format de l’instruction SELECT 

SELECT <liste attribut>

FROM <table>

WHERE <condition> ;

traduit l’expression

\(\Pi\left(\sigma \left(\text{<table>}, \text{<condition>}\right), \text{<liste attribut>}\right)\)

Autres langages, autres choix

On peut faire autrement : dans les extensions du langage R, notamment celles qui relèvent de tidyverse, on associe des fonctions à chaque opérateur de l’algèbre relationnelle. Pour les opérateurs qui portent sur une seule table, le premier argument de chaque fonction est toujours la table sur laquelle on opère. Les autres arguments sont soit les attributs de projection, soit la spécification d’une condition de sélection, soit …

L’usage d’un opérateur pipe (comme dans Unix) permet de composer de facon lisible les opérations.

<table> |>
  filter(<condition> ) |>
  select(<liste attribut>) |> 
  ...

Syntaxe - requêtes simples

SELECT [DISTINCT] * | <liste attribut>

FROM <liste de tables>

[WHERE <condition>] ;

  • * : sélection de tous les attributs (pas de projection)

  • [...] : clause, expression facultative.

  • DISTINCT : supprime les doublons.

Exemples de requête

Schémas des tables :

Employe(Nu, NomE, Annee, Tel, Nudept)
Fournisseur (NomF, Ville, Adresse)
Piece(NomP, Prix, Couleur)

La requête

SELECT *

FROM Fournisseur ;

est équivalente à : \(\quad Fournisseur\)

La requête

SELECT NomF

FROM Fournisseur

WHERE Ville='Paris' ;

est équivalente à : \(\quad \pi((\sigma(\texttt{Fournisseur}, \texttt{Ville}=\texttt{'Paris'})), \texttt{NomF})\)

Requêtes mono-relation

Structure de base

La structure de base d’une requête s’appuie sur les trois mots clés suivants :

  • SELECT correspond à l’opérateur de projection sur la liste d’attributs demandée, il peut aussi être suivi de fonctions d’attributs

  • FROM indique la ou les relations concernées

  • WHERE précise une condition et correspond à l’opération de restriction/sélection en algèbre relationnelle.

Syntaxe de la condition de sélection (WHERE ...)

Une condition se construit à l’aide des opérations suivantes :

  • Comparaison avec opérateurs : =, <>, >,<, >=, <= 
SELECT *

FROM Employe

WHERE NomE <>'Durand'  ;

Mise en garde

Utiliser * en production n’est pas une bonne idée car une modification du schéma de la table Employe modifiera le schéma du résultat.

Syntaxe de la condition de sélection (WHERE ...) - suite

Combinaison de conditions à l’aide des opérateurs logiques: AND, OR, NOT

SELECT DISTINCT NomF

FROM Fournisseur

WHERE (Ville='Londres') OR (Ville='Paris');

Attention aux priorités dans l’évaluation des expressions logiques

Avertissement

AND est prioritaire sur OR. Il est utile de parenthéser !

Attention aux valeurs NULL c.a.d. manquantes/indéterminées.

Logique trivalente

WHERE C ne sélectionne que les tuples vérifiant C = TRUE.

Une comparaison sur un attribut NULL renvoie UNKNOWN, à l’exception de IS [NOT] NULL.

Les opérateurs logiques sont trivalents : TRUE, FALSE, UNKNOWN

On compléte les règles habituelles :

  • NOT NULL = NOT UNKNOWN = UNKNOWN car résultat incertain

  • TRUE AND UNKNOWN = UNKNOWN car résultat incertain

  • FALSE AND UNKNOWN = FALSE car résultat certain

  • TRUE OR UNKNOWN = TRUE car résultat certain

  • FALSE OR UNKNOWN = UNKNOWN car résultat incertain

Test BETWEEN

Permet de vérifier si la valeur d’un attribut est comprise entre deux constantes

SELECT NomE

FROM Employe

WHERE Annee BETWEEN 2022 AND 2025;

Mise en garde

Éviter de manipuler les dates et heures comme des chaînes de caractères

Test IS NULL

Vérifie si une valeur est égale à NULL (c-à-d est inconnue).

SELECT *

FROM Employe

WHERE Tel IS NULL;

Mise en garde

Attention à la gestion des valeurs manquantes.

Test IN

test appartenance : permet de vérifier si la valeur d’un attribut appartient à une liste de constantes.

SELECT *

FROM Fournisseur

WHERE Ville IN ('Paris', 'Londres');

Test LIKE

Permet de vérifier si un attribut de type chaîne de caractères contient une ou plusieurs sous-chaînes.

\(\_\) : remplace n’importe quel caractère,

\(\%\) : remplace n’importe quelle chaîne de caractères.

SELECT Ville

FROM Fournisseur

WHERE Ville LIKE 'Saint%';

Cette requête sélectionne les noms de ville commençant par ‘Saint’.

Avertissement

  • LIKE est sensible à la casse,

  • ILIKE est insensible à la casse.

Requêtes multi-relations

Requêtes multi-relations

Quand l’information est dispersée sur plusieurs tables, on utilise des jointures

On peut :

  • Utiliser plusieurs tables dans la clause FROM,

  • Faire intervenir des conditions complexes impliquant ces tables dans la clause WHERE.

Détails du schema World

Images et tables créées grâce à DbSchema (https://dbschema.com)

Diagramme en pattes de corbeau

img

Table world.city

* 🔑 ⬋ id integer
* name text
* countrycode char(3)
* district text
* population integer

Table world.country

* 🔑 ⬋ code char(3)
* name text
* continent text
* region text
* surfacearea real
indepyear smallint
* population integer
lifeexpectancy real
gnp numeric(10,2)
gnpold numeric(10,2)
* localname text
* governmentform text
headofstate text
⬈ capital integer
* code2 char(2)

Foreign Keys

country_capital_fkey ( capital ) ref world.city (id)

Constraints

country_continent_check ((continent = ‘Asia’::text) OR (continent = ‘Europe’::text) OR (continent = ‘North America’::text) OR (continent = ‘Africa’::text) OR (continent = ‘Oceania’::text) OR (continent = ‘Antarctica’::text) OR (continent = ‘South America’::text))

Table world.countrylanguage

* 🔑 ⬈ countrycode char(3)
* 🔑 language text
* isofficial boolean
* percentage real

Foreign Keys

countrylanguage_countrycode_fkey ( countrycode ) ref world.country (code)

Première jointure sur schéma world

Quels sont les pays qui portent le même nom que leur capitale ?

SELECT name_country
FROM country co, city ci   
WHERE co.capital=ci.id AND co.name_country=ci.name;

qui correspond à

\(\pi_{\text{name\_country}}(\sigma_{\text{capital}=\text{id} \wedge \text{name\_country}=\text{name}}(\text{country}\times \text{city}))\)

autrement dit à

\(\pi_{\text{name\_country}}(\text{country} \bowtie_{\text{capital}=\text{id} \wedge \text{name\_country}=\text{name}} \text{city}))\)

Syntaxe normalisée pour les jointures : JOIN et ON

Quels sont les pays qui portent le même nom que leur capitale ?

SELECT name_country
FROM country co join city ci 
ON co.capital=ci.id  
WHERE co.name_country = ci.name;

qui correspond à

\(\pi_{\text{name\_country}}(\sigma_{\text{capital}=id \wedge \text{name\_country}=\text{name}}(\text{country} \times \text{city}))\)

Renommage

Si on veut faire une jointure d’une table avec elle-même, on utilise AS pour obtenir deux tables de noms différents

  • Quels sont les noms de ville qui apparaissent dans deux pays différents?
SELECT DISTINCT c1.name, c1.countrycode, c2.countrycode
FROM city AS c1 JOIN city AS c2   
ON c1.countrycode != c2.countrycode AND c1.name=c2.name  
ORDER BY c1.name;

Note

Le mot AS est optionnel.

Jointure naturelle

Quels sont les noms des pays où le français est parlé ?

SELECT co.name_country, cl.language
FROM country co, countrylanguage cl
WHERE co.countrycode=cl.countrycode AND cl.language='French';

C’est une jointure naturelle.

Syntaxe normalisée : NATURAL JOIN

SELECT co.name_country, cl.language
FROM country co NATURAL JOIN countrylanguage cl
WHERE cl.language='French';

Jointure naturelle (suite)

Quels sont les pays dont une ville a le même nom qu’une langue parlée dans ce pays?

SELECT DISTINCT co.name_country, ci.name, language
FROM country co NATURAL JOIN  
     city ci NATURAL JOIN     
     countrylanguage cl     
WHERE cl.language = ci.name;

Avertissement

  • la syntaxe nom_table.nom_attribut améliore la lisibilité d’une requète multi-table.

  • On peut utiliser des alias très systématiquement pour alléger les notations.

Schéma world pour mémoire

Jointure externe

  • LEFT OUTER JOIN : jointure externe gauche. On garde les tuples de la table de gauche qui n’ont pas de correspondant dans l’autre table.

  • RIGHT OUTER JOIN : jointure externe droite. Idem à droite.

  • FULL OUTER JOIN : on garde les tuples de chacune des tables qui n’ont pas de correspondant.

  • On complète les valeurs non renseignées par NULL.

Exemple de jointure externe

  • Certains pays n’ont pas de capitale.
SELECT name_country
FROM country
WHERE capital IS NULL;

Le résultat contient 8 lignes.

  • Quels sont les pays qui n’ont pas de capitale ou portent le même nom que leur capitale ?
SELECT co.name_country, ci.name
FROM country co LEFT OUTER JOIN city ci   
     ON co.capital=ci.id                  
WHERE ci.name IS NULL OR co.name_country=ci.name ;

Fin

Requêtes SQL simples