SQL :
Langage
(Suite)
? 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
Structure générale d’une requête :
? 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
SELECT * FROM country
-> tous les attributs de tous les tuples dans la
table “country”
? Passage de l ’algèbre relationnelle au langage de requêtes de SQL
? Traduction de chacun des 9 opérateurs en un bloc SQL
? En algèbre : ? En SQL :
Id(R) SELECT *
FROM R
? En algèbre : ? En SQL : ?[condition] R SELECT *
FROM R
WHERE condition
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
? En algèbre : ? En SQL :
? [ A1, A2,…, An] R SELECT A1,A2,…An
FROM R
SELECT nom, capitale FROM Pays;
algèbre: ? [nom, capitale] Pays
SELECT nom, capitale, population
| PAYS | |||
| nom | capitale | population | surface |
FROM Pays WHERE population < 20 ;
? 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
? En algèbre : ? En SQL :
R × S SELECT *
FROM R, S
? 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
|
| |||||||||
|
| |||||||||
SELECT année, lieu, pays, capitale
FROM JO, PAYS
WHERE = ;
? 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)
| 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
| 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 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
? 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
Table
dominante SELECT année, lieu, , capitale
FROM JO LEFT OUTER JOIN PAYS
ON = ;
? En algèbre : ? En SQL :
R ? S BlocR UNION BlocS
SELECT *
FROM R
du résultat SELECT *
FROM S
? 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
? 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
? 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.
? 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
? = é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
? 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
= ;
? Combinaisons:
WHERE ( ensoleillement > 80% AND pluviosité < 200 )
OR température > 30
WHERE ensoleillement > 80% AND
( pluviosité < 200 OR température > 30 )
? WHERE monnaie = ‘Pound’ OR
monnaie = ‘Schilling’ OR monnaie = ‘Franc’ ? Équivalent à:
WHERE monnaie IN (‘Pound’, ‘Schilling’, ‘Franc’)
? NOT IN: non appartenance àun ensemble
? SELECT * FROM Employee WHERE salary >= 1400 AND salary >= 3000);
? Équivalent à:
SELECT * FROM Employee
WHERE salary >= ALL ( 1400, 3000);
? WHERE population >= 50 AND population <= 60 ? Équivalent à:
WHERE population BETWEEN 50 AND 60
NOT BETWEEN
% : 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
? 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
? -> Pays Bas
NULL = Pas de valeur
? 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
? 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
| ? 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 |
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)
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
FROM P
WHERE np IN
( SELECT np
FROM PUF
WHERE nf = 1) ;
Nested query
? 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)
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') ) ;
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 ) ;
Numéros des fournisseurs qui ne fournissent que des produits rouges
ALL: tous les éléments de l'ensemble
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)
? 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' ) ;
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
? 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
| 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
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 | 441 | Amerique |
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
? 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
|
|
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
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
? 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
VALUES (1996, ‘Atlanta’, ‘U.S.A’)
INSERT INTO olympics
(year, location)
VALUES (1996, ‘Atlanta’)
? INSERT INTO {nom_table ¦ nom_vue} [ (nom_col)* ]
{ VALUES (valeur)* ¦ sous-requête };
UPDATE country
SET capital = ‘Londres’
WHERE country = ‘Ireland’
UPDATE country
SET drive = ‘L’, rainfall = rainfall/2
WHERE country <> ‘France’ AND drive = ‘R’
UPDATE {nom_table ¦ nom_vue}
SET { (nom_col)* = (sous-requête) ¦ nom_col = { valeur ¦ (sous-requête)} }*
WHERE condition;
DELETE FROM country
WHERE population > 50
? -> éliminer les pays dont la population est > 50
DELETE FROM country
? -> la fin du Monde
? DELETE FROM {nom_table ¦ nom_vue} WHERE condition;
