Introduction aux bases de données
Cours 4 : Le langage MySQL
Odile PAPINI
ESIL
Université de la méditerranée
Introduction
SQL comme LDD
Identificateurs
Types
Tables
SQL comme Langage de Requêtes
Interrogation
Traitement de l’absence de valeur
Ordonner les réponses
Fonctions de groupe et regroupement de lignes
Opérateurs de l’algèbre relationnelle
Fonctions pour requêtes SQL et sous-requêtes
Livres :
G. Gardarin : Bases de données objet et relationnel. Eyrolles ed. 1999.
C. J. Date : Introduction aux bases de données. (8ième edition). Vuibert ed. 2004.
H. Garcia-Molina, J. D. Ullman, J. Widow : Database systems, the complete book. Prentice Hall ed. 2002.
Supports de cours :
Support de cours : H. Etievant : http :///cours/
Manuel de référence MySQL : http :///doc/
SQL : Structured Query Langage
langage de gestion de bases de données relationnelles pour
définir les données (LDD) intérroger la base de données (Langage de requêtes) manipuler les données (LMD) controˆler l’accès aux données (LCD)
SQL : Quelques repères historiques
1974 SEQUEL (Structured English Query Language) ancêtre de SQL
1979 premier SGBD basé sur SQL par Relational Software Inc.
(rebaptisé Oracle)
1986 SQL1 1ière norme ISO
1989 ajout des contraintes d’intégrité de base
(clé primaire et clé étrangère)
1992 SQL2 2ième norme extension de SQL1
(nouveaux types et nouveaux opérateurs
1999 SQL3 extension de SQL2
(introduction des types orientés objet)
MySQL
MySQL : logiciel libre (SGBD relationnel) http ://
manuel d’utilisation :
http :///doc/refman/5.0/fr/
Identificateurs :
lettre suivie par : lettre ou chiffre ou ou $ chaˆıne de caractères entre guillemets simples ’ ’ ou doubles ” ” maximum 64 caractères
différent d’un mot clé
ASSERT, ASSIGN, AUDIT, COMMENT, DATE, DECIMAL,
DEFINITION, FILE, FORMAT, INDEX, LIST, MODE,
OPTION, PARTITION, PRIVILEGE, PUBLIC, SELECT,
SESSION, SET, TABLE pas de distinction entre majuscules et minuscules : WINDOW distinction entre majuscules et minuscules : LINUX
Tables :
relations d’un schéma relationnel stockées sous tables table : formée de lignes et de colonnes MySQL :
nom de schéma remplacé par le nom d’utilisateur qui a crée la table
par défaut le schéma est le nom de l’utilisateur connecté
SQL comme LDD
Tables : avec l’outil web phpMyAdmin
Tables : colonnes
!
toutes les données d’une colonne sont du même type identificateur unique pour les colonnes d’une même table
2 colonnes dans 2 tables différentes peuvent avoir le même nom nom complet d’une colonne comprend le nom complet de la table à laquelle elle appartient
exemple : DEPARTEMENTS.DEPARTEMENT ID ou
HR.DEPARTEMENTS.DEPARTEMENTID
Types de données de MySQL :
types pour les chaˆınes de caractères types numériques types temporels (date, heure, ··· ) type pour les données volumineuses (images , sons)
MySQL ne permet pas à l’utilisateur de créer ses propres types
Types pour les chaˆınes de caractères
CHAR(taille) chaˆınes de caractères de longueur fixe codage en longueur fixe : remplissage de blancs taille comprise entre 1 et 255 caractèress
VARCHAR(taille max)
chaˆınes de caractères de longueur variable
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
chaˆınes de longueur max : 255, 65535 , 16777215, 4294967295 caractères
MySQL : Types pour les chaˆınes de caractères
exemples
CHAR(5) : chaˆınes de 5 caractères
VARCHAR(20) : chaˆınes de 20 caractères au plus
’Administration’, ’Marketing’, ”Secretariat”
MySQL : Types numériques
types numériques pour les entiers :
TINYINT pour 1 octets
SMALLINT pour 2 octets
MEDIUMINT pour 3 octets
INTEGER ZEROFILL pour 4 octets
BIGINT pour 8 octets types numériques pour les décimaux à virgule flottante :
REAL
DOUBLE PRECISION ou
types numériques pour les réels à virgule fixe : FLOAT(nbchiffres max, nbdécimales) ZEROFILL
NUMERIC(nb chiffres max, nbdécimales)
DECIMAL(nb chiffres max, nbdécimales)
ZEROFILL : non signé
MySQL : Types temporels
DATE : date format anglophone AAAA-MM-JJ
DATETIME : date et heure format anglophone AAAA-MM-JJ
HH :MM :SS
TIMESTAMP : date et heure sans séparateur
AAAAMMJJHHMMSS
TIMESTAMP(M) : affiche les M (M pair) premiers caractères de AAAAMMJJHHMMSS
TIME : heure au format HH :MM :SS
YEAR : année au format AAAA
MySQL : Type énuméré, type ensemble
ENUM(’valeur1’, ··· , ’valeur n’) type énuméré un attribut de ce type ne peut prendre qu’une valeur parmi un ensemble explicitement spécifié
SET(’valeur1’, ··· ’valeur n’) type ensemble , un attribut de ce type peut prendre plusieurs valeurs parmi l’ensemble explicitement spécifié
exemple du cours précédent : BD vols-réservations
Voici 3 schémas de relations :
avions(NoAV, NOMAV, CAP, LOC) pilotes(NoPIL, NOMPIL, VILLE) vols(NoVOL, NoAV, NoPIL, V d, V a, H d, H a)
Base de donnée relationnelle : Manipulation : exemple (2) et voici les 3 tables correspondantes :
| avions | pilotes | |||||||||||||
| No AV | NOM AV | CAP | LOC | No PIL | NOM PIL | VILLE | ||||||||
| 100 101 102 | airbus airbus carav | 300 300 200 | nice paris toulouse | 1 2 3 | laurent sophie claude | nice paris grenoble | ||||||||
| vols | ||||||||||||||
| No VOL | No AV | No PIL | V d | V a | H d | H a | ||||||||
| it100 | 100 | 1 | nice | paris | 7 | 8 | ||||||||
| it101 | 100 | 2 | paris | toulouse | 11 | 12 | ||||||||
| it102 | 101 | 1 | paris | nice | 12 | 13 | ||||||||
| it103 | 102 | 3 | grenoble | toulouse | 9 | 11 | ||||||||
| it104 | 101 | 3 | toulouse | grenoble | 17 | 18 | ||||||||
avions(noAV, NOM AV, CAP, LOC) no AV INTEGER NOM AV VARCHAR(20)
CAP SMALLINT
LOC VARCHAR(15)
pilotes(noPIL, NOM PIL, VILLE) no PIL INTEGER NOM PIL VARCHAR(20)
VILLE VARCHAR(15) vols(noVOL, no AV, no PIL, V d, V a, H d, H a) no VOL VARCHAR(5) no AV INTEGER no PIL INTEGER
| Vd VARCHAR(15) | Hd DATETIME |
| Va VARCHAR(15) | Ha DATETIME |
autre exemple
schéma de la relation :
article(Id, Titre, Texte, Auteur, Parution, Rubrique)
Id INTEGER
Titre VARCHAR(80)
Texte TEXT
Auteur VARCHAR(15)
Parution DATE
Rubrique ENUM(’politique’, ’international’, ’economie’, ’culture’, ’sport’)
MySQL : Absence de valeur
NULL : représente l’absence de valeur pour tous les types de données. Ce n’est pas une valeur
pour les types chaˆıne de caractères :
la chaˆıne vide ’ ’ représente aussi l’absence de valeur pour les types chaˆıne de caractères (taille fixe ou variable) une chaˆıne remplie de blancs n’est pas équivalente à la chaˆıne vide
pour les types numériques le nombre 0 ne représente pas l’absence de valeur
création de table
CREATE TABLE nom de table (liste de définition de colonne, [liste de contrainte de table]) ;
définition de colonne : := nom de colonne (nom de domaine ou type)
[liste de contrainte de colonne]
[DEFAULT valeur pardéfaut]
création de table : contrainte de colonne
contrainte de colonne : := [CONSTRAINT nom] type de contrainte de colonne
type de contrainte de colonne : :=
AUTO INCREMENT ou
PRIMARY KEY ou
NOT NULL ou
UNIQUE ou
CHECK(conditionsur valeur) ou
REFERENCES nom de table(nom de colonne)
création de table : contrainte de table
contrainte de table : := [CONSTRAINT nom]
type de contrainte de table
type de contrainte de table : :=
PRIMARY KEY (liste de nom de colonne) ou
NOT NULL (liste de nom de colonne) ou
UNIQUE (liste de nom de colonne) ou
CHECK (conditionsur ligne) ou
FOREIGN KEY liste de nom de colonne REFERENCES nom de de table (liste de nom de colonne)
Exemple : Création de la table avions à partir du schéma :
avions(noAV, nomAV, CAP, LOC)
CREATE TABLE avions ( noAV INTEGER
CONSTRAINT Cle P avions PRIMARY KEY,
NOMAV VARCHAR(20),
CAP SMALLINT
CONSTRAINT Dom CAP avions CHECK (CAP ≥ 4),
LOC VARCHAR(15) );
Création de la table vols à partir du schéma :
vols(noVOL, noAV, noPIL, V d, V a, H d, H a)
Contraintes de colonnes ?
Contraintes de table?
CREATE TABLE vols ( no VOL VARCHAR(5)
CONSTRAINT Cle P vols PRIMARY KEY, no AV INTEGER
CONSTRAINT Ref no AV vols REFERENCES avions, no PIL INTEGER
CONSTRAINT Ref no PIL vols REFERENCES pilotes,
V d VARCHAR(15) NOT NULL,
V a VARCHAR(15) NOT NULL,
H d DATETIME,
H a DATETIME,
CONSTRAINT C1 vols CHECK (v d 6= v a),
CONSTRAINT C2 vols CHECK (h d < h a) ) ;
suppression de table
DROP TABLE nom ;
Quand une table est supprimée :
efface tous les index qui y sont attachés quelque soit le propriétaire
efface tous les privilèges qui y sont attachés
MAIS les vues et les synomymes se référant à cette table ne sont pas supprimés
modification de table
ALTER TABLE nom de table modification de table;
modification de table : :=
ADD COLUMN définition de colonne
ADD CONSTRAINT contrainte de table
DROP COLUMN nom de colonne
DROP CONSTRAINT nom de contrainte
Exemple : Ajout d’un colonne à la table vols de schéma :
vols(noVOL, noAV, noPIL, V d, V a, H d, H a) ALTER TABLE vols ADD COLUM COUT VOL FLOAT
le schéma devient :
vols(noVOL, noAV, noPIL, V d, V a, H d, H a, COUT VOL)
insertion de lignes dans une table
INSERT INTO nom de table [liste decolonnes] VALUES liste devaleurs; ou
INSERT INTO nom de table [liste decolonnes] requête ;
ajouter un avion dans la table avions en respectant l’ordre des colonnes
INSERT INTO avions
VALUES (100, ’Airbus’, 200, ’Paris’); ajouter un avion dans la table avions sans connaˆıtre l’ordre
INSERT INTO avions (noAV, CAP, LOC, NOMAV)
VALUES (101, 200, ’Paris’, ’Airbus’) ; ajouter un avion dans la table avions dont la localisation est
INDEFINI
INSERT INTO avions (noAV, NOMAV, CAP)
VALUES (102, ’Airbus’, 200); ou
INSERT INTO avions
VALUES (102, ’Airbus’, 200, NULL);
suppression de lignes d’une table
DELETE [FROM] nom de table [WHERE condition] ; Exemples :
vider la table avions
DELETE FROM avions; supprimer de la table avions tous les avions dont la capacité est inférieur à 100
DELETE FROM avions
WHERE CAP < 100;
modification de lignes dans une table
UPDATE nom de table SET liste expressioncolonne
[WHERE condition] ;
expressioncolonne : := nomde colonne = expression ou nom de colonne = requête
Exemple :
modifier la capacité de l’avion numéro 100
UPDATE avions SET CAP = 300
WHERE noAV = 100 ;
interrogation
requête : := SELECT [DISTINCT] projection
FROM liste de (nom de table [[AS] nom]) | (requête AS nom)
WHERE condition
[GROUP BY liste de nom de colonne]
[HAVING condition]
[ORDER BY liste de ((nom de colonne | rang de colonne) (ASC | DESC)];
requête : := requête ( UNION | INTERSECT | EXCEPT ) requête
requête : := (requête)
projection : :=
* | nom de table | liste de (terme de projection[[AS] nom]) terme de projection : := expression | agrégation
expression : := valeur | nom de colonne | expressionarithmétique | ···
agrégation : := COUNT(*)
opérateurd’agrégation([DISTINCT] expression)
opérateurd’agrégation : :=
COUNT | SUM | AVG | MAX | MIN
condition : := condition élémentaire
NOT condition | condition(AND | OR) condition | (condition) condition élémentaire : := reconnaissance de modèle | test de valeur nulle | comparaison
|
appartenance àun intervalle | appartenance à un ensemble | existence
reconnaissance de modèle : := expression [NOT] LIKE dechaˆıne
test de valeur nulle : := nom de valeur IS [NOT] NULL
comparaison : := expression (= | <>| > | < | <= | >= | ) expression | expression (= | <>) (| SOME | ALL ) requête
expression (> | < | <= | >=) (| SOME | ALL )
requête mono colonne
appartenance à un intervalle : := expression BETWEEN expression AND expression
appartenance à un ensemble : := expression (IN | NOTIN) (requête) |
(liste de expression) (IN | NOTIN) (requête)
ensemble de valeurs : := (liste de valeur) | requête mono colonne existence : := EXISTS (requête)
Sélection de lignes
toutes les lignes et toutes les colonnes
SELECT * FROM nom de table;
pour connaˆıtre toutes les caractéristiques des avions stockés dans la table
SELECT * FROM avions;
toutes les lignes mais seulement certaines colonnes
SELECT liste de nom de colonne FROM nom de table; pour connaˆıtre les numéros de tous les vols
SELECT noVOL FROM vols;
Sélection de lignes
suppression des lignes dupliquées
SELECT DISTINCT liste de nom de colonne FROM nom de table;
pour connaˆıtre les numéros des pilotes qui conduisent au moins un avion
SELECT DISTINCT no PIL FROM vols ; colonnes calculées
SELECT expression [AS alias] FROM nom de table; afficher une augmentation de 5% du coût de chaque vol
SELECT noVOL, ’5%’ ”%”,
COUT VOL*0.05 AUGM, COUTVOL*1.05 ”Nouveau coût” FROM vols;
sur les chaˆınes de caractères afficher les trajets assurés par les vols sous la forme :
Ville de départ −− > Ville d’arrivée
SELECT noVOL,
Vd || ′ − − >′ || Va TRAJET FROM vols;
sur les dates pour connaˆıtre la durée en heures de tous les vols
SELECT noVOL, TMEDIFF(Da, D d) durée
FROM vols;
Recherche par comparaison
SELECT liste de nom de colonne FROM nom de table
WHERE expression ; pour connaˆıtre tous les avions qui ont une capacité > 200 places
SELECT noAV
FROM avions
WHERE CAP > 200;
pour connaˆıtre tous les pilotes qui effectuent des vols qui
durent plus d’une heure
SELECT DISTINCT no PIL
FROM vols
WHERE (TIMEDIFF(D a , D d) )> 1;
Recherche par ressemblance
SELECT liste de nom de colonne FROM nom de table
WHERE expression [NOT] LIKE motif [caractères spéciaux] ;
caractère spéciaux :
% : remplace 0, 1 ou plusieurs caractères : remplace 1 caractère
caractère d’échappement :
permet de traiter les caractère spéciaux comme de simples caractères
il n’y a pas de caractère d’échappement prédéfini
Recherche par ressemblance : exemples
pour connaˆıtre la capacité de tous les avions Boeing
SELECT noAV, NOMAV, CAP
FROM avions
WHERE NOM AV LIKE ’Boeing%’ ;
Recherche avec condition conjonctive
SELECT liste de nom de colonne FROM nom de table
WHERE condition AND condition;
pour connaˆıtre tous les avions qui sont à Marseille et dont la capacité est de 300 places
SELECT noAV
FROM avions
WHERE LOC = ’Marseille’ AND CAP = 300;
Recherche avec condition disjonctive
SELECT liste de nom de colonne FROM nom de table
WHERE condition OR condition;
pour connaˆıtre tous les vols qui utilisent les avions 100 et 101
SELECT noVOL
FROM vols
WHERE no AV = 100 OR no AV = 101;
Recherche avec condition négative
SELECT liste de nom de colonne FROM nom de table
WHERE NOT condition ;
pour connaˆıtre tous les pilotes qui n’habitent pas à Marseille
SELECT noPIL
FROM pilotes
WHERE NOT VILLE = ’Marseille’;
Recherche avec un intervalle
SELECT liste de nom de colonne FROM nom de table
WHERE expression BETWEEN expression AND expression ;
pour connaˆıtre tous les avions qui ont une capacité entre 200 et 300 places SELECT noAV
FROM avions
WHERE CAP BETWEEN 200 AND 300;
Recherche avec une liste
SELECT liste de nom de colonne FROM nom de table
WHERE expression [NOT] IN liste de expression ;
pour connaˆıtre tous les pilotes qui habitent soit à Marseille soit à Nice
SELECT noPIL
FROM pilotes
WHERE VILLE IN (’Marseille’, ’Nice’);
Recherche avec une liste
SELECT liste de nom de colonne FROM nom de table WHERE expression (<>| > | < | <= | >= | ) ANY
liste deexpression ;
SELECT liste de nom de colonne FROM nom de table WHERE expression (<>| > | < | <= | >= | ) SOME liste deexpression ;
Traitement de l’absence de valeur
sur les expressions numériques un calcul numérique ou de dates exprimé avec les opérateurs +, −, ∗, / n’a pas de valeur lorsqu’au moins une des composantes n’a pas de valeur
SELECT noAV, 2*CAP/3 AS CAPRED
FROM avions
WHERE no AV = 320;
Traitement de l’absence de valeur
sur les chaˆınes de caractères un calcul de chaˆınes exprimé avec l’opérateur || n’a pas de valeur lorsque toutes ses composantes n’ont pas de valeur la chaˆıne vide et l’absence de valeur sont confondues
SELECT no CL, NOM RUE CL || ” || VILLE CL AS ADR CL ou no CL, NOM RUECL || NULL || VILLECL AS ADRCL
FROM clients
WHERE no CL = 1035;
Traitement de l’absence de valeur
sur les comparaisons toute comparaison exprimée avec les opérateurs =, <>, >, <, <=, >=, LIKE qui comporte une expression qui n’a pas de valeur prend la valeur logique INDEFINIE les comparaisons ignorent les lignes où il y a absence de valeur
SELECT * FROM pilotes
WHERE NAISSPIL <> 1960 AND VILLE <> ’Paris’ ;
comparaisons indéfinies :
SELECT *
FROM avions
WHERE NULL = NULL OR ” = ” OR ” LIKE ’%’
OR ’A’ LIKE ” OR ’A’ NOT LIKE ”;
Traitement de l’absence de valeur
table de vérité pour le connecteur ∧
| ∧ | VRAI | FAUX | INDEFINI |
| VRAI | VRAI | FAUX | INDEFINI |
| FAUX | FAUX | FAUX | FAUX |
| INCONNIE | INDEFINI | FAUX | INDEFINI |
Traitement de l’absence de valeur
table de vérité pour le connecteur ∨
| ∨ | VRAI | FAUX | INDEFINI |
| VRAI | VRAI | VRAI | VRAI |
| FAUX | VRAI | FAUX | INDEFINI |
| INDEFINI | VRAI | INDEFINI | INDEFINI |
table de vérité pour le connecteur ¬
| ¬ | |
| VRAI | FAUX |
| FAUX | VRAI |
| INDEFINI | INDEFINI |
Traitement de l’absence de valeur
équivalences disjonctives expr NOT BETWEEN expr1 AND expr2 ⇔ expr < expr1 OR expr > expr2
expr IN (expr1 ··· exprN )
⇔ expr = expr1 OR ··· OR expr = exprN
expr op ANY (expr1 ··· exprN ) ⇔ expr op expr1 OR ··· OR expr op exprN
Traitement de l’absence de valeur
Les expressions suivantes :
expr NOT BETWEEN expr1 AND expr2 expr IN (expr1 ··· exprN ) expr op ANY (expr1 ··· exprN )
sont vraies ssi expr a une valeur et si au moins une des expressions expr1, exprN a une valeur qui satisfait les comparaisons
SELECT NUM PIL FROM pilotes
WHERE VILLE IN (’Marseille’, ’Nice’, ”) ;
Traitement de l’absence de valeur
équivalences conjonctives expr BETWEEN expr1 AND expr2 ⇔ expr >= expr1 AND expr <= expr2
expr NOT IN (expr1 ··· exprN )
⇔ expr <> expr1 AND ··· AND expr <> exprN
expr op ALL (expr1 ··· exprN )
⇔ expr op expr1 AND ··· AND expr op exprN
Traitement de l’absence de valeur
Les expressions suivantes :
expr BETWEEN expr1 AND expr2 expr NOT IN (expr1 ··· exprN ) expr op ALL (expr1 ··· exprN )
sont vraies ssi expr a une valeur et si au moins toutes les expressions expr1, exprN ont une valeur qui satisfait la comparaison
SELECT NUM PIL FROM pilotes
WHERE VILLE NOT IN (’Marseille’, ’Nice’, ”) ;
Traitement de l’absence de valeur recherche de l’absence de valeur
SELECT liste de nom de colonne FROM nom de table
WHERE expression IS [NOT] NULL ;
pour connaˆıtre tous les vols auxquels on n’a pas encore affecté d’avions
SELECT noVOL
FROM vols
WHERE no AV IS NULL;
Ordonner les réponses
SELECT liste de nom de colonne
FROM nom de table
[WHERE expression]
ORDER BY { expression | position } [ASC | DESC]
[{ expression | position } [ASC | DESC]];
Ordonner les réponses
pour connaˆıtre les horaires des vols triés par ordre croissant des dates et heures de départ
SELECT noVOL, DATEd, DATEa
FROM vols
ORDER BY DATEd;
Les fonctions de groupe
les fonctions de groupe calculent les résultats à partir d’une collection de valeurs.
COUNT (*) comptage des lignes
COUNT ([DISTINCT | ALL]) comptage des valeurs
MAX ([DISTINCT | ALL]) maximum des valeurs
MIN ([DISTINCT | ALL]) maximum des valeurs
SUM ([DISTINCT | ALL]) minimum des valeurs
AVG ([DISTINCT | ALL]) moyenne des valeurs
STDDEV ([DISTINCT | ALL]) écart-type des valeurs
VARIANCE ([DISTINCT | ALL]) variance des valeurs
Les fonctions de groupe
pour connaˆıtre le nombre d’avions
SELECT COUNT(*) NBR AV
FROM avions;
pour connaˆıtre le nombre d’heures de vols du pilote 4020
SELECT SUM(TIMEDIFF(D a,D d)) NBR H
FROM vols
WHERE noPIL = 4020 ;
Les regroupements de lignes
les fonctions de groupe calculent les résultats à partir d’une collection de valeurs. SELECT liste d’expressions1
FROM nom de table
GROUP BY liste d’expressions2 ;
les expressions de liste d’expressions1 doivent être des expressions formées uniquement :
d’expressions de liste d’expressions2
de fonctions de groupe de constantes littérales
Les regroupements de lignes
pour connaˆıtre le nombre d’avions affectés à chaque ville d’affectation d’un avion
SELECT LOC, COUNT(*) NBR AV
FROM avions
GROUP BY LOC;
Les regroupements de lignes
| avions triés sur LOC | ⇒ | table résultat | ||
| no AV | LOC | LOC | NBR AV | |
| 820 715 ··· 720 225 456 ··· 531 | Ajaccio Ajaccio ··· Marseille Marseille Marseille ··· Toulouse | Ajaccio ··· Marseille ··· | 2 ··· 3 ··· | |
| Toulouse | 1 | |||
Les regroupements de lignes
pour connaˆıtre le nombre de vols qui ont la même durée
SELECT TIMEDIFF(D a,D d) DUR VOL, COUNT(*)
NBR VOL
FROM vols
GROUP BY TIMEDIFF(D a, D d) ;
Les regroupements de lignes
regroupement de lignes sélectionnées
SELECT liste d’expressions1
FROM nom de table
WHERE condition
GROUP BY liste d’expressions2 ; pour connaˆıtre le nombre d’avions différents utilisés par chaque pilote assurant un vol
SELECT LOC, noPIL, COUNT(DISTINCT noAV) NBR AV
FROM vols
WHERE noPIL IS NOT NULL
GROUP BY noPIL ;
Conditions sur l’ensemble des lignes
SELECT liste d’expressions
FROM nom de table
[ WHERE condition ] HAVING condition sur lignes;
les expressions de liste d’expressions et conditionsur lignes doivent être formées uniquement :
de fonctions de groupe de constantes littérales
Conditions sur l’ensemble des lignes
pour savoir si le pilote 4010 assure tous les vols avec un avion diff;érent à chaque fois
SELECT ’OUI’ REP
FROM vols
WHERE noPIL = 4010
HAVING COUNT(*) = COUNT(DISTINCT noAV);
Conditions sur l’ensemble des lignes
SELECT liste d’expressions
FROM nom de table
[ WHERE condition ] GROUP BY liste d’expressions2 HAVING condition sur lignes;
les expressions de liste d’expressions et conditionsur lignes doivent être formées uniquement :
d’expressions de liste d’expressions2
de fonctions de groupe de constantes littérales
Conditions sur l’ensemble des lignes
pour connaˆıtre les pilotes qui conduisent au moins deux avions
différents
SELECT noPIL
FROM vols
WHERE noPIL IS NOT NULL
GROUP BY noPIL
HAVING COUNT(DISTINCT noAV) >= 2 ;
Opérateurs ensemblistes
SELECT liste d’expressions1
FROM nom de table
[ WHERE condition ]
[ GROUP BY liste d’expressions2]
UNION | UNION ALL | INTERSECT | MINUS
SELECT liste d’expressions3
FROM nom de table
[ WHERE condition ]
[ GROUP BY liste d’expressions4] ;
Opérateurs ensemblistes
pour connaˆıtre les villes qui sont soit des villes de départ soit des villes d’arrivées d’un vol
SELECT V d VILL
FROM vols
WHERE V d IS NOT NULL
UNION
SELECT V a
FROM vols
WHERE V a IS NOT NULL;
Opérateurs ensemblistes
pour connaˆıtre le nombre de vols assurés par chaque pilote
SELECT noPIL, COUNT(*) NBRVOL
FROM vols
WHERE noPIL IS NOT NULL
GROUP BY noPIL
UNION ALL
(SELECT noPIL, 0
FROM pilotes
MINUS
SELECT noPIL, 0
FROM vols) ;
Produit cartésien
SELECT liste d’expressions
FROM listede(nom de table [ alias ])
[ WHERE condition ] ;
pour connaˆıtre le couˆt de chaque classe du vol V900 lorsqu’on les applique au vol V100
SELECT Classe, COEFPRIX * COUT VOL COUT
FROM defclasses D, vols V
WHERE D.noVOL = ’V900’ AND V.noVOL = ’V100’ :
Opérateur de jointure naturelle
SELECT liste d’expressions
FROM listede(nom de table [ alias ])
WHERE expr comp expr [AND | OR expr comp expre ] ; ou
SELECT liste d’expressions
FROM
nom detable [ alias ] INNER JOIN nom detable [ alias ]
ON expr comp expr [ AND | OR expr comp expre ] ;
Opérateur de jointure naturelle
pour connaˆıtre le nombre de places de chaque vol qui a été affecté à un avion
SELECT noVOL, CAP
FROM vols, avions
WHERE = ;
| table vols | table avions | ||
| no VOL | no AV | no AV | CAP |
| V101 V141 V169 V631 V801 | 560 101 101 NULL 240 | 101 240 560 | 350 NULL 250 |
equi-jointure sur no AV
| vols.noVOL | ||||
| V101 V141 V169 V801 | 560 101 101 240 | 560 101 101 240 | 250 350 350 NULL |
| vols.noVOL | ||||
| V101 V141 V169 V801 | 560 101 101 240 | 560 101 101 240 | 250 350 350 NULL |
projection sur no VOL, CAP
| vols.noVOL | |
| V101 | 250 |
| V141 | 350 |
| V169 | 350 |
| V801 | NULL |
Opérateur de semi-jointure externe
SELECT liste d’expressions
FROM nom de table1, nom de table2
WHERE exprtable1 comp (+) [ AND exprtable1 comp (+) ] ou
SELECT liste d’expressions
FROM
nom detable1 [ alias ]
LEFT JOIN | RIGHT JOIN nom detable2 [ alias ]
ON expr comp expr [ AND | OR expr comp expr ] ;
Opérateur de semi-jointure externe
pour connaˆıtre le nombre de places de chaque vol (même lorsqu’aucun avion n’est affecté au vol)
SELECT noVOL, CAP
FROM vols V LEFT JOIN avions A
ON V.noAV = A.noAV;
| table vols | table avions | ||
| no VOL | no AV | no AV | CAP |
| V101 V141 V169 V631 V801 | 560 101 101 NULL 240 | 101 240 560 | 350 NULL 250 |
equi-jointure sur no AV
| vols.noVOL | ||||
| V101 V141 V169 V801 | 560 101 101 240 | 560 101 101 240 | 250 350 350 NULL |
| vols.noVOL | ||||
| V101 V141 V169 V801 | 560 101 101 240 | 560 101 101 240 | 250 350 350 NULL |
equi-jointure externe
| vols.noVOL | ||||
| V101 V141 V169 V801 V631 | 560 101 101 240 NULL | 560 101 101 240 NULL | 250 350 350 NULL NULL |
| vols.noVOL | ||||
| V101 V141 V169 V801 V631 | 560 101 101 240 NULL | 560 101 101 240 NULL | 250 350 350 NULL NULL |
projection sur no VOL, CAP
| vols.noVOL | |
| V101 | 250 |
| V141 | 350 |
| V169 | 350 |
| V801 | NULL |
| V631 | NULL |
Fonctions numériques (1)
SIGN(nombre) signe du nombre spécifié
ABS(nombre) valeur absolue
ACOS(nombre) arc cosinus
ASIN(nombre) arc sinus
ATAN(nombre) arc tangente
COS(nombre) cosinus
SIN(nombre) sinus
TAN(nombre) tangente
COSH(nombre) cosinus hyperbolique
SINH(nombre) sinus hyperbolique
TANH(nombre) tangente hyperbolique
Fonctions numériques (2)
EXP(puissance) e élevé à la puissance
LN(nombre) logarithme naturel
LOG(base,nombre) logarithme en base quelconque
SQRT(nombre) racine carrée
POWER(nombre,puissance) puissance d’un nombre
MOD(dividende,diviseur) modulo (reste de la division)
CEIL(nombre) le plus petit entier plus grand que le nombre passé en argument
FLOOR(nombre) le plus grand entier plus petit ou égal au nombre passé en argument
Sous-requêtes
imbrication de sous-requêtes dans la clause WHERE
SELECT projection FROM nom de table
WHERE condition
(SELECT projection
FROM nom detable
WHERE condition, ···);
Sous-requêtes : donnant une seule ligne
SELECT projection
FROM nom de table
WHERE expr op
(SELECT projection
FROM nom detable
WHERE condition, ···);
op ∈ { = , <>, < , <= , > , >= }
Sous-requêtes : donnant une seule ligne
pour connaˆıtre les vols qui utilisent le même avion que celui utilisé par le vol V101
SELECT noVOL
FROM vols
WHERE noAv = (SELECT noAv
FROM vols
WHERE no VOL = ’V101’ );
FROM vols );
Sous-requêtes donnant au plus une ligne
SELECT projection
FROM nom de table
WHERE (expr1, ··· exprn) op
(SELECT (expr1, ··· exprn)
FROM nom detable
WHERE condition, ···);
op ∈ {= , <>}
Sous-requêtes donnant au plus une ligne
pour connaˆıtre le vols qui assure le même trajet que celui du vol V101 mais 2 jours plus tard
SELECT noVOL
FROM vols
WHERE (Vd, V a, D d, D a) =
(SELECT (V d, V a, D d+2, D a+2)
FROM vols
WHERE no VOL = ’V101’ );
Sous-requêtes donnant 0, 1 ou plusieurs lignes
SELECT projection
FROM nom de table
WHERE expr NOT IN
(SELECT (projection FROM ···);
SELECT projection
FROM nom de table
WHERE expr op ANY | ALL
(SELECT (projection FROM ···);
op ∈ { = , <>, < , <= , > , >= }
Sous-requêtes donnant 0, 1 ou plusieurs lignes
pour connaˆıtre le vols qui sont assurés par un pilote qui habite Paris
SELECT noVOL
FROM vols
WHERE noPIL IN
(SELECT noPIL
FROM pilotes
WHERE VILLE = ’Paris’ );
Sous-requêtes donnant 0, 1 ou plusieurs lignes
pour connaˆıtre les pilotes qui n’assurent aucun vol
SELECT noPIL
FROM pilotes
WHERE noPIL NOT IN
(SELECT noPIL
FROM vols
WHERE no PIL IS NOT NULL );
Sous-requêtes d’existence
SELECT projection
FROM nom de table [Alias]
WHERE [NOT] EXISTS
(SELECT (projection
FROM ···);
Sous-requêtes d’existence
pour connaˆıtre les avions qui sont conduits par au moins un pilote de Marseille
SELECT DISTINCT noAV
FROM vols
WHERE EXISTS
(SELECT *
FROM pilotes
WHERE no PIL = PIL
AND VILLE = ’Marseille’);
