BD II: SQL I

Langage Manipulation de Données 1

Équipe BD

LPSM Université Paris-Cité

2024-09-20

Introduction

SQL : Structured Query Language

SQL est 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

A standard is reviewed every 5 years (roughly)

ISO/IEC 9075 from www.iso.org

SQL

Une instruction SQL permet de combiner :

restriction/filtrage (sélection) \(\sigma\)
projection \(\pi\)
renommage \(\rho\)
…

. . .

L’instruction

SELECT <liste attribut>

FROM <table>

WHERE <condition> ;

traduit l’expression

\(\Pi_{\text{<liste attribut>}}(\sigma_{\text{<condition>}}(\text{<table>}))\)

Ou σ(R, Condition) |> Π(<liste attribut>)

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 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 (le schéma reste inchangé)
[...] : clause, expression facultative.
DISTINCT : supprime les doublons.

--| connection:  con
--| eval: false
SET search_path TO world ;

Requêtes simples sur exemple jouet

Employe(Nu, NomE, Annee, Tel, Nudept)

Fournisseur (NomF, Ville, Adresse)

Piece(NomP, Prix, Couleur)

--| connection:  con
--| eval: false
--| echo: true

SELECT co.name_country, co.region
FROM country co
WHERE co.continent='Europe' 
LIMIT 5 ;

Equivalent à :

\[\Pi_{\text{NomF, region}}(\text{country})\]

SELECT *

FROM Fournisseur ;

Equivalent à : Fournisseur

SELECT *

FROM Fournisseur

WHERE Ville='Paris' ;

Equivalent à : \(\sigma_{\texttt{Ville}=\texttt{'Paris'}}(\texttt{Fournisseur})\)

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 filtrage (`WHERE ...`)

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

Comparaison avec opérateurs : =, <>, >,<, >=, <=

SELECT *

FROM Employe

WHERE NomE <>'Durand'  ;

Syntaxe de la condition de filtrage (`WHERE ...`)

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

Attention aux valeurs manquantes

Syntaxe de la condition `WHERE ...` (suite)

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

SELECT NomE

FROM Employe

WHERE Annee BETWEEN '22/06/03' AND '25/09/03';

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

Syntaxe de la condition `WHERE ...` (suite)

Test à NULL signifiant que la valeur est égale à NULL (c-à-d est inconnue)

SELECT *

FROM Employe

WHERE Tel IS NULL;

Attention aux valeurs manquantes

Syntaxe de la condition `WHERE ...` (suite)

Test d’appartenance IN qui permet de vérifier si la valeur d’un attribut appartient à une liste de constantes,

SELECT *

FROM Fournisseur

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

Syntaxe de la condition `WHERE ...` (suite)

Test LIKE permettant 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%';

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

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, city    

WHERE capital=id AND name_country=name_city;

qui correspond à

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

autrement dit à

\[\pi_{\text{name\_country}}(\text{country} \bowtie_{\text{capital}=\text{id} \wedge \text{name\_country}=\text{name\_city}} \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 JOIN city   

ON capital=id    

WHERE name_country=name_city;

qui correspond à

\[\pi_{\text{name\_country}}(\sigma_{\text{capital}=id \wedge \text{name\_country}=\text{name\_city}}(\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_city, c1.countrycode, c2.countrycode

FROM city AS c1 JOIN city AS c2   

  ON c1.countrycode!=c2.countrycode AND c1.name_city=c2.name_city  

ORDER BY c1.name_city;

Jointure naturelle

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

SELECT name_country

FROM country, countrylanguage

WHERE country.countrycode=countrylanguage.countrycode
      AND language='French';

Syntaxe normalisée pour la jointure naturelle~: NATURAL JOIN

SELECT name_country

FROM country NATURAL JOIN countrylanguage    

WHERE 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 name_country, name_city, language

FROM country NATURAL JOIN  
     city natural JOIN     
     countrylanguage       

WHERE language=name_city;

Jointure externe

LEFT OUTER JOIN (resp. RIGHT OUTER JOIN) : on garde les tuples de la table de gauche (resp. droite) qui n’ont pas de complément dans l’autre table, la condition étant spécifiée dans la clause ON
FULL OUTER JOIN : on garde les tuples de chacune des tables qui n’ont pas de complément.
On complète les valeurs non renseignées par NULL

Exemple de jointure externe

Certains pays n’ont pas de capitale : la requête

SELECT name_country

FROM country

WHERE capital IS NULL;

renvoie 8 lignes.

Quels sont les pays qui n’ont pas de capitale ou portent le même nom que leur capitale~?

SELECT name_country, name_city

FROM country LEFT OUTER JOIN city   
     ON capital=id                  

WHERE name_city IS NULL OR name_country=name_city ;

Fin

Requêtes SQL simples

BD II: SQL I

Introduction

SQL : Structured Query Language

Plusieurs fois normalisés par l’ISO …

SQL

Syntaxe - requêtes simples

Requêtes simples sur exemple jouet

Requêtes mono-relation

Structure de base

Syntaxe de la condition de filtrage (WHERE ...)

Syntaxe de la condition de filtrage (WHERE ...)

Syntaxe de la condition WHERE ... (suite)

Syntaxe de la condition WHERE ... (suite)

Syntaxe de la condition WHERE ... (suite)

Syntaxe de la condition WHERE ... (suite)

Requêtes multi-relations

Requêtes multi-relations

On peut :

Détails du schema World

Diagramme en pattes de corbeau

Table world.city

Table world.country

Foreign Keys

Constraints

Table world.countrylanguage

Foreign Keys

Première jointure sur schéma world

Syntaxe normalisée pour les jointures : JOIN et ON

Renommage

Jointure naturelle

Jointure naturelle (suite)

Jointure externe

Exemple de jointure externe

Fin

Syntaxe de la condition de filtrage (`WHERE ...`)

Syntaxe de la condition de filtrage (`WHERE ...`)

Syntaxe de la condition `WHERE ...` (suite)

Syntaxe de la condition `WHERE ...` (suite)

Syntaxe de la condition `WHERE ...` (suite)

Syntaxe de la condition `WHERE ...` (suite)

Détails du schema `World`

Première jointure sur schéma `world`