Cours Initiation aux requêtes SQL
SQL :
Structured Query
Langage
(Suite)
SQL: Trois langages
? Langage de définition de données (LDD/DDL)
?création de relations : CREATE TABLE
?modification de relations: ALTER TABLE ?suppression de relations: DROP TABLE ?vues, index …. : CREATE VIEW
? Langage de requêtes (LMD/DML)
?SELECT … . FROM . WHERE .
? Langage de manipulation de données (LMD /DML)
?insertion de tuples: INSERT
?mise à jour des tuples: UPDATE
?suppression de tuples: DELETE
2
SQL : partie langage de requêtes
Structure générale d’une requête :
le BLOC
? Structure d'une requête formée de trois clauses:
SELECT <liste-attributs>
FROM <liste-tables>
WHERE <condition>
? SELECT définit le format du résultat cherché
? FROM définit à partir de quelles tables le résultat est calculé
? WHERE définit les prédicats de sélection du résultat
4
Exemple de requête
SELECT * FROM country
-> tous les attributs de tous les tuples dans la
table “country”
SQL / Algèbre
SQL/Algèbre
? Passage de l ’algèbre relationnelle au langage de requêtes de SQL
? Traduction de chacun des 9 opérateurs en un bloc SQL
SQL / Algèbre … Identité
? En algèbre : ? En SQL :
Id(R) SELECT *
FROM R
SQL / Algèbre … Sélection
? En algèbre : ? En SQL : ?[condition] R SELECT *
FROM R
WHERE condition
Exemple de requête de sélection
algèbre: SELECT *
?[population < 20] Pays FROM Pays
WHERE population < 20 ;
| Irlande | Dublin | 3 | 70 |
| Autriche | Vienne | 8 | 83 |
| Royaume-Uni | Londres | 36 | 244 |
| Suisse | Berne | 7 | 41 |
| USA | Washington | 189 | 441 |
* = toutes les colonnes
SQL / Algèbre … Projection
? En algèbre : ? En SQL :
? [ A1, A2,…, An] R SELECT A1,A2,…An
FROM R
Exemple de requête de projection
SELECT nom, capitale FROM Pays;
algèbre: ? [nom, capitale] Pays
Requête de sélection + projection
SELECT nom, capitale, population
| PAYS | |||
| nom | capitale | population | surface |
FROM Pays WHERE population < 20 ;
SQL / Algèbre … Renommage
? En algèbre : ? En SQL : Impossible de
renommer des attributs. Il
? (A1/B1, , An/Bn) R faut faire des “copies
logiques” des relations.
SELECT *
FROM R, R R2
WHERE R.A = R2.A ….
? R2 peut être vue comme une copie logique de R
SQL / Algèbre … Produit cartésien
? En algèbre : ? En SQL :
R × S SELECT *
FROM R, S
SQL / Algèbre … Jointure
? En algèbre : ? En SQL :
R S SELECT *
FROM R, S
WHERE R.A1 = S.A1 AND R.A2 = S.A2
…
AND R.An = S.An
? Avec A1, …, An tous les attributs communs à R et S
Jointure de 2 relations
Jointure de 2 relations: résultat
|
| |||||||||
|
| |||||||||
SELECT année, lieu, pays, capitale
FROM JO, PAYS
WHERE = ;
Jointure sur la même table
? Comment comparer les populations des pays?
? ?? Toutes les paires de pays telles que le premier pays a une population plus grande que le deuxième pays
? SELECT , P1.population, , P2.population FROM PAYS as P1, PAYS as P2
WHERE P1.population > P2.population;
?NB: La table PAYS est renommée en P1 et P2 (alias)
Jointure sur la même table
| PAYS-P1 | |||
| nom | capitale | population | surface |
Irlande Dublin 3 70
Autriche Vienne 8 83 SELECT , P1.population,
RU Londres 36 244
Suisse Berne 7 41 , P2.population
USAWashington189 441FROM PAYS P1, PAYS P2PAYS-P2WHERE P1.population > P2.populationnom capitale population surface
| Irlande | Dublin | 3 | 70 |
| Autriche | Vienne | 8 | 83 |
| RU | Londres | 36 | 244 |
| Suisse | Berne | 7 | 41 |
| USA | Washington | 189 | 441 |
| résultat | |||
| nom-P1 | pop-P1 | nom-P2 | pop-P2 |
P1.population > P2.population
| Autriche | 8 | Irlande | 3 | |
| Autriche | 8 | Suisse | 7 | |
| RU | 36 | Irlande | 3 | |
| RU | 36 | Autriche | 8 | |
| RU | 36 | Suisse | 7 | |
Jointure externe
? Jointure classique: on supprime les tuples qui ne vérifient pas la condition de jointure ? Jointure externe:
? Définition d’une table dominante et d’une table subordonnée
? On garde toutes les lignes de la table dominante qu’on ‘joint’ avec null
? SELECT … SELECT …
From A, B From A LEFT OUTER JOIN B
Where A.x = B.y (+) On A.x = B.y
? A : table dominante
? Retourne tous les tuples de A
? Attention: Mettre (+) pour toutes les conditions de jointure
Jointure externe
? Right outer join:
SELECT … SELECT …
From A, B From A RIGHT OUTER JOIN B
Where A.x (+) = B.y On A.x = B.y
? Full outer join:
SELECT …
From A FULL OUTER JOIN B
On A.x = B.y
Jointure externe
Table
dominante SELECT année, lieu, , capitale
FROM JO LEFT OUTER JOIN PAYS
ON = ;
SQL / Algèbre … union
? En algèbre : ? En SQL :
R ? S BlocR UNION BlocS
SELECT *
FROM R
Les tuples en double sont éliminés UNION
du résultat SELECT *
FROM S
SQL / Algèbre … intersection
? En algèbre : ? En SQL :
R ? S BlocR INTERSECT BlocS
SELECT *
FROM R
INTERSECT
Les tuples en double sont éliminés SELECT * du résultat
FROM S
SQL / Algèbre … différence
? En algèbre : ? En SQL :
R ? S BlocR MINUS BlocS
SELECT *
FROM R
Les tuples en double sont éliminés MINUS
du résultat SELECT *
FROM S
Différences
? En SQL :
? Le résultat d'une requête peut contenir plusieurs occurrences d’un n-uplet,
z pour avoir une seule occurrence de chaque n-uplet
dans une relation : DISTINCT
z Exemple : select distinct nom from Personne
? Le résultat d'une requête peut être trié,
? Il existe une valeur spéciale dite indéfinie (NULL) utilisée pour remplir un champ dont on ne connaît pas la valeur.
Remarques
? En SQL, le produit cartésien est possible sans renommer les attributs communs.
?Ex : schéma(R×S) = A (de R), B (de R), B (de S), C (de S).
? En SQL, si plusieurs attributs ont le même nom, pour résoudre l’ambiguïté, on spécifie la relation auquel l’attribut appartient.
?Ex : select A, R.B, C
from R, S
Ecriture des conditions
Opérateurs de comparaison
? = égal ? WHERE surface = 200
? <>différent ? WHERE capitale <> ‘Paris’
? > plus grand que ? WHERE population > 8
? >=plus grand ou égal ? WHERE population >= 8
? < plus petit que ? WHERE surface < 83
? <=plus petit ou égal ? WHERE surface <= 83
Opérateurs logiques
? Tous les prédicats : AND ex: WHERE population<10 ANDsurface<500
? Un des prédicats : OR ex: WHERE population<10 ORsurface<500
? Négation de la condition : NOT ex: SELECT , , P1.capitale
FROM PAYS P1, PAYS P2
WHERE P1.capitale = P2.capitale
= ;
Expressions logiques
? Combinaisons:
WHERE ( ensoleillement > 80% AND pluviosité < 200 )
OR température > 30
WHERE ensoleillement > 80% AND
( pluviosité < 200 OR température > 30 )
Appartenance à un ensemble: IN
? WHERE monnaie = ‘Pound’ OR
monnaie = ‘Schilling’ OR monnaie = ‘Franc’ ? Équivalent à:
WHERE monnaie IN (‘Pound’, ‘Schilling’, ‘Franc’)
? NOT IN: non appartenance àun ensemble
Comparaison à un ensemble: ALL
? SELECT * FROM Employee WHERE salary >= 1400 AND salary >= 3000);
? Équivalent à:
SELECT * FROM Employee
WHERE salary >= ALL ( 1400, 3000);
Valeur dans un intervalle: BETWEEN
? WHERE population >= 50 AND population <= 60 ? Équivalent à:
WHERE population BETWEEN 50 AND 60
NOT BETWEEN
Conditions partielles (wildcards)
% : zéro ou n’importe quel caractère (x caractères)
? WHERE pays LIKE ‘%lande’
? -> Irlande, Islande, Finlande, Hollande
? WHERE pays LIKE ‘%ran%’
? -> Iran, France
_ : exactement un caractère
? WHERE pays LIKE ‘I_lande’
? -> Irlande, Islande
NOT LIKE
Valeurs calculées
? SELECT nom, population, surface, natalité FROM PAYS
WHERE (population * 1000 / surface) < 50
AND (population * natalité / surface) > 0
? SELECT nom, ( population * 1000 / surface )
FROM PAYS
Valeurs numériques: + - * /
Chaines de caractères: ¦¦
‘NULL’ <> 0 !!!
? Null = valeur inconnue ou non définie:
Pays ( nom , montagne )
SELECT nom
FROM PAYS
WHERE montagne IS NULL
? -> Pays Bas
NULL = Pas de valeur
Operations sur ‘NULL’
? NULL dans conditions: ? ( population > 0 ) ?
z si population est NULL, le résultat est "unknown" donc "false" ? ( population = NULL ) ? z le test retourne toujours "false":
?La syntaxe correcte est: ( population IS NULL )
? NULL dans expression arithmétique:
? ( population + NULL ) retourne NULL
Opérations sur ‘NULL’
? NULL dans fonction SQL
?Concat (nom, '-', prénom) retourne NULL si prénom null
z Concat ( nom, '-', NVL(prenom,' ')) retourne 'dupont-' z NVL(attribut,valeur de remplacement): retourne la valeur de remplacement lorsque que la valeur de l’attribut est NULL.
?Fonctions d'agrégation ignorent les NULL:
z Moyenne(1000, null, null, null, and 2000) =
(1000+2000)/2
Résumé
| ? If A is: | Condition | Evaluates to: |
| ?10 | a IS NULL | FALSE |
| ?10 | a IS NOT NULL | TRUE |
| ?10 | a = NULL | UNKNOWN |
| ?10 | a != NULL | UNKNOWN |
| ?NULL | a IS NULL | TRUE |
| ?NULL | a IS NOT NULL | FALSE |
| ?NULL | a = NULL | UNKNOWN |
| ?NULL | a != NULL | UNKNOWN |
| ?NULL | a = 10 | UNKNOWN |
| ?NULL | a != 10 | UNKNOWN |
Requêtes avec blocs emboîtés
BD Exemple : Livraisons
P ( np , nomp , couleur , poids , prix ) les produits
U ( nu , nomu , ville , pays ) les usines
F ( nf , nomf , type , ville , pays ) les fournisseurs PUF ( np, nu, nf , quantité ) les livraisons
np, nu, nf dans PUF sont des identifiants externes sur P, U et F (respectivement)
Jointure par blocs emboîtés : IN (?)
Nom et couleur des produits livrés par le fournisseur 1
Solution 1 : la jointure déclarative
SELECT nomp, couleur FROM P, PUF WHERE = P.np AND nf = 1 ;
Solution 2 : la jointure procédurale (par emboîtement)
Outer query
| SELECT nomp, couleur FROM P WHERE np IN | ||
| ( SELECT np | ||
| FROM PUF WHERE nf = 1) ; | ||
Nested query
Jointure par blocs emboîtés : IN (?)
? SELECT nomp, couleur FROM P
WHERE np IN
( SELECT np
FROM PUF
WHERE nf = 1) ;
? IN compare chaque valeur de np avec l'ensemble (ou multi-ensemble) de valeurs retourné par la sous-requête
? IN peut aussi comparer un tuple de valeurs:
?SELECT nu FROM U
WHERE (ville, pays) IN (SELECT ville, pays FROM F)
Composition de conditions
Nom des fournisseurs qui approvisionnent une usine de Londres ou de Paris en un produit rouge
SELECT nomf FROM F
WHERE nf IN
(SELECT nf FROM PUF WHERE np IN
(SELECT np FROM P
WHERE couleur = 'rouge')
AND nu IN
(SELECT nu FROM U
SELECT nomf
FROM PUF, P, F, U
WHERE couleur = 'rouge'
AND = P.np AND = F.nf
AND = U.nu
AND (U.ville = 'Londres'
OR U.ville = 'Paris');
WHERE ville = 'Londres' OR ville = 'Paris') ) ;
Quantificateurs : ANY (ou SOME)
Numéro des fournisseurs de produits rouges:
SELECT nf
FROM PUF
WHERE np = ANY ( SELECT np FROM P
WHERE couleur = 'rouge' ) ;
Equivalent au IN: Existe t'il dans l'ensemble au moins un élément qui vérifie la condition?
Numéro des fournisseurs fournissant des produits dont le numéro est inférieur au numéro des produits pesant plus d’une tonne:
SELECT nf
FROM PUF
WHERE np < ANY ( SELECT np FROM P
WHERE poids > 1000 ) ;
Quantificateurs : ALL
Numéros des fournisseurs qui ne fournissent que des produits rouges
ALL: tous les éléments de l'ensemble
SELECT nf doivent vérifier la condition
FROM F
WHERE 'rouge' = ALL
( SELECT couleur
FROM P
WHERE np IN
( SELECT np
La requête imbriquée est
FROM PUF
ré-évaluée pour chaque WHERE = F.nf ) ) ; tuple de la requête (ici pour chaque nf)
Conditions sur des ensembles: EXISTS
? Test si l’ensemble n’est pas vide (E ??)
? Noms des fournisseurs qui fournissent au moins un produit rouge
SELECT nomf
FROM F
WHERE EXISTS ce fournisseur
( SELECT *
FROM PUF, P
WHERE = F.nf
Produit fourni AND = P.np est rougeAND P.couleur = 'rouge' ) ;
Recapitulation: blocs emboîtés
attr IN requête attr NOT IN requête condition vrai si vérifiée pour une des valeurs retournée par la requête
attr opérateur ANY requête
condition vrai si vérifiée pour au moins une des valeurs retournée par la requête
attr opérateur ALL reqûete
condition vrai si vérifiée pour toutes les valeurs
retournées par la requête
EXISTS requête NOT EXISTS requête
Teste si l’ensemble n’est pas vide
Traitement des résultats
Fonctions sur des colonnes
? Attributs calculés
?Ex: SELECT nom, population*1000/surface FROM PAYS
? Opérateurs sur attributs numériques
?SUM: somme des valeurs des tuples sélectionnés
?AVG: moyenne
Opérateur
? Opérateurs sur tous types d’attributsagrégation
?MIN: minimum
?MAX: maximum
?COUNT: nombre de tuples sélectionnés
| PAYS | |
| nom | capitale |
population surface continent
Irlande Dublin 3 70 Europe
Autriche Vienne 8 83 Europe
R-Uni Londres 36 244 Europe
Suisse Berne 7 41 Europe
USA Washington 189 441 Amerique
SELECT MIN(population), MAX(population),
AVG(population),
SUM(surface),
COUNT(*) FROM PAYS
WHERE continent = ‘Europe’
Retourne un tuple avec:
- la pop. du plus petit pays d’Europe,
- la pop. du plus grand,
- la moyenne des pop. de tous les pays d’Europe,
- la somme des surfaces des pays d’Europe
- le nombre de pays d’Europe
DISTINCT
Suppression des doubles:
SELECT DISTINCT continent
FROM PAYS Europe
Amerique
| PAYS | |
| nom | capitale |
ORDER BYpopulation surface continent
| Tri des tuples du | Irlande | Dublin | 3 | 70 | Europe |
| Autriche | Vienne | 8 | 83 | Europe | |
| R-Uni | Londres | 36 | 244 | Europe | |
| Suisse | Berne | 7 | 41 | Europe | |
| USA | Washington | 189 | 441 | Amerique |
résultat
SELECT continent, nom, population
FROM PAYS
WHERE surface > 60
ORDER BY continent, nom DESC
ASC/DESC
|
|
| Irlande | Dublin | 3 | 70 | Europe | |
| Autriche | Vienne | 8 | 83 | Europe | |
| Royaume-Uni | Londres | 36 | 244 | Europe | |
| Suisse | Berne | 7 | 41 | Europe | |
| USA | Washington | 189 | 441 | Amerique |
GROUP BY
Partition de l’ensemble des tuples en groupes homogènes
SELECT continent, MIN(population), MAX(population), AVG(population), SUM(surface), COUNT(*)
FROM PAYS
GROUP BY continent ;
|
|
| Europe | 3 | 36 | 13,5 | 438 | 4 | ||||
| Amerique | 189 | 189 | 189 | 441 | |||||
| 1 | |||||||||
un tuple par continent
Condition HAVING
? SELECT … FROM … WHERE … GROUP BY … HAVING
<condition>
? Partition de l’ensemble des tuples en groupes homogènes
| un tuple par continent |
? SELECT continent, SUM(population)
FROM PAYS GROUP BY continent
HAVING SUM(surface) > 439 ;
? Attention la condition ne porte pas sur chaque tuple de la table comme pour le where mais sur l’ensemble des tuples d’un groupe:
? On retourne la population totale par continent, tel que chaque continent a une superficie > 439
|
|
Attention
SELECT surface, MIN(population)
FROM Pays
WHERE continent = ‘Europe’;
Requête incorrecte !
Règle: chaque attribut dans clause SELECT est inclus dans opérateur d’agrégation ou inclus dans clause GROUP BY
Renommage du nom des attributs: AS
SELECT MIN(population) AS min_pop,
MAX(population) AS max_pop,
AVG(population) AS avg_pop,
SUM(surface) AS sum_surface,
COUNT(*) AS count
FROM PAYS
WHERE continent = ‘Europe’ ;
Langage de
Manipulation de données
Manipulation des données
? INSERT INTO: ajouter un tuple dans une table ou vue
? UPDATE: changer les tuples d’une table ou vue
? DELETE FROM: éliminer les tuples d’une table ou vue
INSERT INTO
INSERT INTO olympics
VALUES (1996, ‘Atlanta’, ‘U.S.A’)
INSERT INTO olympics
(year, location)
VALUES (1996, ‘Atlanta’)
INSERT
? INSERT INTO {nom_table ¦ nom_vue} [ (nom_col)* ]
{ VALUES (valeur)* ¦ sous-requête };
UPDATE
UPDATE country
SET capital = ‘Londres’
WHERE country = ‘Ireland’
UPDATE country
SET drive = ‘L’, rainfall = rainfall/2
WHERE country <> ‘France’ AND drive = ‘R’
UPDATE
UPDATE {nom_table ¦ nom_vue}
SET { (nom_col)* = (sous-requête) ¦ nom_col = { valeur ¦ (sous-requête)} }*
WHERE condition;
DELETE FROM
DELETE FROM country
WHERE population > 50
? -> éliminer les pays dont la population est > 50
DELETE FROM country
? -> la fin du Monde
DELETE
? DELETE FROM {nom_table ¦ nom_vue} WHERE condition;