-
POLYTECH
Université d’Aix-Marseille
Plan du cours
Introduction
SQL comme LDD
Identificateurs
Types
Tables
SQL comme Langage de Requêtes
Interrogation
Ordonner les réponses
Fonctions de groupe et regroupement de lignes
Opérateurs de l’algèbre relationnelle
Sous-requêtes
Vues
Fonctions pour requêtes SQL
SQL comme LCD
- 1>Bibliographie
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 : J. Le Maitre :
http
Support de cours : C. Sabatier, Université de la Méditerranée.
SQL : Introduction
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 : Introduction
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)
SQL et Oracle
SQL : Quelques repères historiques Oracle est SGBD qui utilise SQL PL/SQL dit (L4G) langage procédural
nombreux programmes utilitaires :
SQL*PLUS SQL interactif
SQL*FORMS : saisie et visualisation des données avec des formulaires
SQL*REPORTWRITER : rapports imprimés
WebDB : interface avec le Web
···
SQL comme LDD
Identificateurs :
lettre suivie par : lettre ou chiffre ou ou ? ou $ chaˆ?ne de caractères entre guillemets maximum 30 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
SQL comme LDD
Tables :
relations d’un schéma relationnel stockées sous tables table : formée de lignes et de colonnes
SQL2 : nom d’une table précédé du nom d’un schéma ORACLE :
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 :
SQL comme LDD
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
SQL comme LDD
Types de données de SQL2 :
types pour les chaˆ?nes de caractères types numériques
types temporels (date, heure, ··· )
SQL2 n’a pas de type pour les données volumineuses (images , sons)
SQL2 ne permet pas à l’utilisateur de créer ses propres types
SQL comme LDD
SQL2 : 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 2000 octets
VARCHAR(taille max)
chaˆ?nes de caractères de longueur variable taille comprise entre 1 et 4000 octets
constantes chaˆ?nes de caractères entre guilllemets
SQL comme LDD
ORACLE : Types pour les chaˆ?nes de caractères
CHAR(taille) ou NCHAR(taille)
VARCHAR(taille max)
MAIS de préférence
VARCHAR2(taille max) ou NVARCHAR2(taille max)
constantes chaˆ?nes de caractères entre coˆtes
SQL comme LDD
ORACLE : Types pour les chaˆ?nes de caractères
exemples
NCHAR(5) : chaˆ?nes de 5 caractères
VARCHAR2(20) : chaˆ?nes de 20 caractères au plus
’Administration’, ’Marketing’
SQL comme LDD
SQL2 : Types numériques
types numériques pour les entiers :
SMALLINT pour 2 octets
INTEGER pour 4 octets types numériques pour les décimaux à virgule flottante :
REAL
DOUBLE PRECISION ou FLOAT
types numériques pour les décimaux à virgule fixe :
DECIMAL(nb chiffres, nbdécimales)
NUMERIC(nb chiffres, nb décimales) constantes exemples : 43.8, -13, 5.3E-6
SQL comme LDD
ORACLE : Types numériques
exemples
ORACLE accepte tous les types numériques de SQL2 mais il les traduit dans ses propres types
NUMBER : nombre en virgule flottante avec jusqu’à 38
chiffres significatifs
NUMBER(nb chiffres, nbdécimales) : nombre en virgule fixe
SQL comme LDD
SQL2 : Types temporels
DATE
pour les dates
TIME
pour les heures, minutes et secondes
TIMESTAMP
pour un moment précis : date et heure, minutes et secondes
(précision jusqu’ à la microseconde)
SQL comme LDD
ORACLE : Types temporels le type DATE remplace DATE et TIME de SQL2
DATE correspond à une date avec une précision jusquà la seconde
constantes exemples : ’1/05/2007’ ou ’1 MAY 2007’
SQL comme LDD
SQL2 : Type boolén
pour enregistrer la valeur d’un bit exemples : BIT(1), BIT(4) pas supporté par ORACLE
Base de donnée relationnelle : Manipulation : exemple (1)
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
SQL comme LDD : Exemple
avions(noAV, NOM AV, CAP, LOC) no AV NUMBER(4) NOM AV VARCHAR2(20)
CAP NUMBER(4)
LOC VARCHAR2(15)
pilotes(noPIL, NOM PIL, VILLE) no PIL NUMBER(4) NOM PIL VARCHAR2(20)
VILLE VARCHAR2(15) vols(noVOL, no AV, no PIL, V d, V a, H d, H a) no VOL VARCHAR2(5) no AV NUMBER(4) no PIL NUMBER(4)
Vd VARCHAR2(15)
H d DATE
Va VARCHAR2(15)
H a DATE
SQL comme LDD
ORACLE : Types pour objets larges
LOB : large objet formé par :
valeur du LOB : une grande donnée (jusqu’à 4 Go) index du LOB : structure d’accès le type localisateur du LOB pointeur vers l’endroit ou` il est stocké
Types pour objets larges
CLOB ou NCLOB : pour le stockage de grandes chaˆ?nes de caractères
BLOB : pour le stockage de grandes chaˆ?nes d’octets BFILE : pour le stockage de données binaires dans un fichier extérieur à la base
SQL comme LDD
ORACLE : Autres types
Les types chaˆ?nes d’octets
RAW(taille) : 2000 octets max
LONG RAW : 2 Go max
conversion automatique d’une chaˆ?ne d’octets en une chaˆ?ne de caractères représentant un nombre hexadécimal, et inversement
SQL comme LDD
ORACLE : Autres types
Le type adresse de ligne
ROWID : une valeur de type est un nombre en base 64 dont
les chiffres sont représentés par : A-Z, a-z, 0-9, +, /, il s’écrit :
OOOOOOFFFBBBBBBLLL
OOOOOO : numéro de l’objet qui contient la ligne (6 chiffres en base 64)
FFF : numéro du fichier dans la BD (3 chiffres en base 64, le premier chiffre a le numéro 1 (AAB))
BBBBBB : numéro du bloc dans le fichier (6 chiffres en base 64)
LLL : numéro du ligne dans le bloc (3 chiffres en base 64), le premier chiffre a le numéro 0 (AAA))
SQL comme LDD
ORACLE : 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
pour les types LOB
l’absence de valeur est l’absence de localisateur un LOB vide n’est pas une absence de valeur
SQL comme LDD
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]
SQL comme LDD
création de table : contrainte de colonne
contrainte de colonne : := [CONSTRAINT nom] type de contrainte de colonne
type de contrainte de colonne : :=
PRIMARY KEY ou
NOT NULL ou
UNIQUE ou
CHECK(conditionsur valeur) ou
REFERENCES nom de table(nom de colonne)
SQL comme LDD
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)
SQL comme LDD
Exemple : Création de la table avions à partir du schéma : avions(noAV, nomAV, CAP, LOC)
CREATE TABLE avions ( noAV NUMBER(4)
CONSTRAINT Cle P avions PRIMARY KEY,
NOMAV VARCHAR2(20),
CAP NUMBER(4)
CONSTRAINT Dom CAP avions CHECK (CAP ? 4),
LOC VARCHAR2(15) ) ;
SQL comme LDD : Exemple : Création de la table vols
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?
SQL comme LDD : Exemple : Création de la table vols vols(noVOL, noAV, noPIL, V d, V a, H d, H a)
CREATE TABLE vols ( no VOL VARCHAR2(5)
CONSTRAINT Cle P vols PRIMARY KEY, no AV NUMBER(4)
CONSTRAINT Ref no AV vols REFERENCES avions, no PIL NUMBER(4)
CONSTRAINT Ref no PIL vols REFERENCES pilotes,
V d VARCHAR2(15) NOT NULL,
V a VARCHAR2(15) NOT NULL,
H d DATE,
H a DATE,
CONSTRAINT C1 vols CHECK (v d 6= v a),
CONSTRAINT C2 vols CHECK (h d < h a) ) ;
SQL comme LDD
suppression de table
DROP TABLE nom ;
Quand une table est supprimée, ORACLE :
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
SQL comme LDD
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
SQL comme LDD
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 NUMBER(8)
le schéma devient :
vols(noVOL, noAV, noPIL, V d, V a, H d, H a, COUT VOL)
SQL comme LDD
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 ;
SQL comme LDD : Exemple
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);
SQL comme LDD
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;
SQL comme LDD
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 ;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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)
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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 nom de chaˆ?ne
test de valeur nulle : := nom de valeur IS [NOT] NULL
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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)
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes : Exemple de calcul
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 afficher les dates de départ et d’arrivée de chaque vol en décalant les dates
SELECT noVOL, Dd + 1/24 D d, D a + 1/24 Da
FROM vols;
pour connaˆ?tre la durée en heures de tous les vols
SELECT noVOL, 24 *(D a -D d) durée
FROM vols;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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 (24 *(D a -D d) )> 1;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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%’ ;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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 ou 101
SELECT noVOL
FROM vols
WHERE no AV = 100 OR no AV = 101;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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’;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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’);
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
Recherche avec une liste
SELECT liste de nom de colonne FROM nom de table WHERE expression (<>| > | < | <= | >= | ) ALL
liste deexpression ;
SELECT liste de nom de colonne FROM nom de table WHERE expression (<>| > | < | <= | >= | ) SOME liste de expression ; pour connaˆ?tre tous les vols dont le départ est à plus de 5 jours
et dont la durée est moins de 5 heures
SELECT noVOL, Dd, 24*(D a - D d) Durée
FROM vols
WHERE D d > ALL (sysdate +5, D a -5/24);
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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 ”;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
Traitement de l’absence de valeur
table de vérité pour le connecteur ?
?
VRAI
FAUX
INDEFINI
VRAI
VRAI
FAUX
INDEFINI
FAUX
FAUX
FAUX
FAUX
INDEFINI
INDEFINI
FAUX
INDEFINI
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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’, ”) ;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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 toutes les expressions expr1, exprN ont une valeur qui satisfait la comparaison
SELECT NUM PIL FROM pilotes
WHERE VILLE NOT IN (’Marseille’, ’Nice’, ”) ;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
Traitement de l’absence de valeur
Donner une valeur à l’absence de valeur
NVL (expr1, expr2) = expr1 si elle définie, expr2 sinon expr1 et expr2 doivent être de même type
pour qu’une capacité d’avion inconnue soit considérée comme nulle
SELECT noVOL, NVL(CAP,0) FROM avions;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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]];
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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]) minimum des valeurs
SUM ([DISTINCT | ALL]) somme des valeurs
AVG ([DISTINCT | ALL]) moyenne des valeurs
STDDEV ([DISTINCT | ALL]) écart-type des valeurs
VARIANCE ([DISTINCT | ALL]) variance des valeurs
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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(24 *(Da - D d)) NBR H
FROM vols
WHERE noPIL = 4020 ;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
Les regroupements de lignes
avions triés sur LOC
no AV
LOC
820
Ajaccio
715
Ajaccio
···
···
720
Marseille
225
Marseille
456
Marseille
···
···
531
Toulouse
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
Les regroupements de lignes
pour connaˆ?tre le nombre de vols qui ont la même durée
SELECT 24*(D a - D d) DUR VOL, COUNT(*) NBR VOL
FROM vols
GROUP BY D a - D d;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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 ;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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);
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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 ;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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] ;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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) ;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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’ :
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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 ] ;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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 = ;
Fonctions pour requêtes SQL
Opérateur de jointure naturelle : exemple (1)
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
Fonctions pour requêtes SQL
Opérateur de jointure naturelle : exemple (2)
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
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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 ] ;
Fonctions pour requêtes SQL
SQL comme Langage de Requêtes
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;
Fonctions pour requêtes SQL
Opérateur de semi-jointure externe : exemple (1)
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
Fonctions pour requêtes SQL
Opérateur de semi-jointure externe : exemple (2)
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
Fonctions pour requêtes SQL
Opérateur de semi-jointure externe : exemple (2)
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 pour requêtes SQL
Fonctions diverses pour requêtes SQL
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, ···);
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Sous-requêtes : donnant une seule ligne
SELECT projection
FROM nom de table
WHERE expr op
(SELECT projection
FROM nom detable
WHERE condition, ···);
op ? { = , <>, < , <= , > , >= }
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
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’ );
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
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 ? {= , <>}
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
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’ );
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
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 ? { = , <>, < , <= , > , >= }
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
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’ );
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
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 );
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Sous-requêtes d’existence
SELECT projection
FROM nom de table [Alias]
WHERE [NOT] EXISTS
(SELECT (projection
FROM ··· );
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
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’);
Fonctions pour requêtes SQL
Les vues
Les vues
une vue est une table virtuelle résultat d’une requête
roˆle d’une vue réduire la complexité syntaxique des requêtes définir les schémas externes. définir des contraintes d’intégrité.
définir un niveau additionnel de sécurité en restreignant l’accès à un sous ensemble de lignes et/ ou de colonnes.
Fonctions pour requêtes SQL
Les vues (exemple)
vols
No VOL
No AV
No PIL
V d
V a
H d
H a
it200
100
1
nice
paris
7
8
it201
100
2
paris
toulouse
11
12
it202
101
1
paris
nice
12
13
it203
102
3
paris
toulouse
9
11
it204
104
3
nice
paris
17
18
Création d’une vue ligne
ligne
V d
V a
nice
paris
paris
toulouse
paris
nice
Fonctions pour requêtes SQL
Les vues : création
Les vues
création d’une vue de schéma externe
CREATE [OR REPLACE ] [ FORCE | NO FORCE ]
VIEW nom de table [(liste de nomde colonne)]
AS requête [WITH CHECK OPTION | WITH READ ONLY ] ;
création de la vue correspondant aux vols qui partent de Paris
CREATE VIEW volsd Paris
AS SELECT no VOL, V a, H d, Ha
FROM vols
WHERE Vd = ’Paris’;
Fonctions pour requêtes SQL
Les vues : interrogation
interroger une vue interrogation de la vue correspondant aux vols qui partent de Paris
SELECT * FROM vols dParis;
supprimer une vue DROP VIEW nom de vue ;
suppression de la vue correspondant aux vols qui partent de Paris
DROP VIEW volsd Paris ;
Fonctions pour requêtes SQL
Requêtes avec vues
Exemples
pour connaˆ?tre les pilotes qui assurent le plus grand nombre de vols
CREATE VIEW nbre de volspar pil
AS SELECT noPIL, count(*) AS NBR VOLS
FROM vols V
WHERE no PIL IS NOT NULL
GROUP BY no PIL ;
SELECT noPIL FROM nbre de vols parpil
WHERE NBR VOLS =
(SELECT max( NBR VOLS)
FROM nbre de volspar pil) ;
Fonctions pour requêtes SQL
Les vues : mise à jour
opérations sur les vues
INSERT
UPDATE
DELETE
restrictions : Ces instructions ne s’appliquent pas aux vues qui contiennent :
une jointure
un opérateur ensembliste : UNION, INTERSECT, MINUS une clause GROUP BY, CONNECT BY, ORDER BY ou START WITH
la clause DISTINCT, une expression ou une pseudo-colonne dans la liste de sélection des colonnes.
Fonctions pour requêtes SQL
Requêtes avec vues
création de la vue pour la personne qui définit les vols
CREATE VIEW defvols
AS SELECT no VOL, V d, D d, V a, D a
FROM vols
WHERE no VOL IS NULL AND no PIL IS NULL; définir un nouveau vol
INSERT INTO defvols VALUES
(’V999’, ’Marseille’, todate(’01/05/07 10 :30’, ’DD/MM/RR
HH :MI’), ’Paris’, todate(’01/05/07 10 :30’, ’DD/MM/RR HH :MI’));
Fonctions pour requêtes SQL
Requêtes avec vues
supprimer un vol non affecté
DELETE FROM defvols
WHERE no VOL = ’V998’; modifier un vol non affecté
UPDATE defvols
SET D d= D d + 1 / 24, D a= D a + 1 / 24 WHERE no VOL = ’V998’;
connaˆ?tre les vols non affectés
SELECT * FROM defvols;
Fonctions pour requêtes SQL
Requêtes avec vues
création de la vue pour la personne qui définit les vols
CREATE VIEW defvols
AS SELECT no VOL, V d, D d, V a, D a
FROM vols
WHERE no VOL IS NULL AND no PIL IS NULL;
modifier un vol non affecté
UPDATE defvols
SET D d= D d + 1 / 24, D a= D a + 1 / 24
WHERE no VOL = ’V998’; connaˆ?tre les vols non affectés
SELECT * FROM defvols;
Fonctions pour requêtes SQL
Requêtes avec vues
création de la vue pour la personne qui affecte un avion et un pilote à un vol
CREATE VIEW affectvols
AS SELECT noVOL, no AV, no PIL
FROM vols; affecter un avion et un pilote à un nouveau vol
UPDATE affectvols
SET no AV = 101, no PIL = 5050 WHERE no VOL = ’V999’
AND no AV IS NUL AND noPIL IS NULL ; affecter un nouvel avion à un vol
UPDATE affectvols
SET no AV = 202
WHERE no VOL = ’V999’;
Fonctions pour requêtes SQL
Requêtes avec vues
création de la vue pour la personne qui affecte un avion et un pilote à un vol
CREATE VIEW affectvols
AS SELECT noVOL, no AV, no PIL
FROM vols; permuter l’affectation des pilotes de 2 vols
UPDATE affectvols A1
SET no PIL =
(SELECT no PIL
FROM affect vols A2
WHERE
( VOL = ’V100’ AND A2.noVOL = ’V200’)
OR
( VOL = ’V200’ AND A2.noVOL = ’V100’))
WHERE no VOL = ’V100’ OR no VOL = ’V200’ ;
Fonctions pour requêtes SQL
Les vues : contrôle de mise à jour
création d’une vue de vérification : controˆle de l’insertion ou de la modificaion de ligne
CREATE VIEW nomde vue
AS requête
WITH CHECK OPTION ;
vérification des contraintes de domaine (interdiction des valeurs inconnues)
CREATE VIEW aavions
AS SELECT * FROM avions
WHERE no AV > 0 AND CAP > 1
AND NOM AV IN (’Airbus’, ’Boeing’, ’Caravelle’) WITH CHECK OPTION ;
Fonctions pour requêtes SQL
Requêtes avec vues
vérification des contraintes de domaine (autorisation des valeurs inconnues)
CREATE VIEW aaavions
AS SELECT * FROM avions
WHERE no AV > 0
AND (CAP IS NULL OR CAP > 1)
AND (NOM AV IS NULL OR IN (’Airbus’, ’Boeing’,
’Caravelle’))
WITH CHECK OPTION ;
Fonctions pour requêtes SQL
Requêtes avec vues
Contraintes de référence
1) valider l’insertion dans la table référenant
2) valider la suppression dans la table référencée
règle d’adéquation : les insertions et les suppressions se font
toujours au travers des vues
Fonctions pour requêtes SQL
Requêtes avec vues
Exemple : expression de clés étrangères de la relation vols
validation des insertions dans vols
CREATE VIEW aavions
AS SELECT * FROM vols
WHERE no AV > 0
AND (no PIL IS NULL OR no PIL IN(SELECT noPIL FROM pilotes))
AND (NOM AV IS NULL OR IN (SELECT NOM AV FROM avions))
WITH CHECK OPTION ;
Fonctions pour requêtes SQL
Requêtes avec vues
Exemple : expression de clés étrangères de la relation vols
validation des suppressions dans avions et dans pilotes
CREATE VIEW davions
AS SELECT * FROM avions A
WHERE NOT EXISTS ( SELECT * FROM vols V WHERE
A.noAV = V.noAV);
CREATE VIEW dpilotes
AS SELECT * FROM pilotes P
WHERE NOT EXISTS ( SELECT * FROM vols V WHERE
P.no PIL = V.noPIL);
Fonctions pour requêtes SQL
Requêtes avec vues
Exemple : ajout des contraintes de domaine de 2 fa¸cons
agrégation
CREATE VIEW adavions
AS SELECT * FROM avions A
WHERE no AV > 0 AND CAP > 1
AND NOM AV IN (’Airbus’, ’Boeing’, ’Caravelle’)
AND NOT EXISTS ( SELECT * FROM vols V WHERE
A.noAV = V.noAV)
WITH CHECK OPTION ;
Fonctions pour requêtes SQL
Requêtes avec vues
Exemple : ajout des contraintes de domaine de 2 fa¸cons
modulaire
CREATE VIEW aavions
AS SELECT * FROM avions WHERE no AV > 0 AND CAP > 1
AND NOM AV IN (’Airbus’, ’Boeing’, ’Caravelle’)
AS SELECT * FROM pilotes P
WITH CHECK OPTION ;
CREATE VIEW adavions
AS SELECT * FROM aavions
WHERE NOT EXISTS ( SELECT * FROM vols V WHERE
A.noAV = V.noAV);
Fonctions pour requêtes SQL
Requêtes avec vues
Expression de contraintes générales
Exemple : empêcher l’affectation d’un même avion à deux vols différents dont les tranches horaires se chevauchent
CREATE VIEW a vols
AS SELECT * FROM vols V1 WHERE NOT EXISTS (
SELECT * FROM vols V2
WHERE AV = AV
AND NVL(, ) >= NVL(, V1.D a)
AND NVL(, ) >= NVL(, V2.D a))
WITH CHECK OPTION ;
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
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 pour requêtes SQL
Fonctions diverses pour requêtes SQL
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
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions numériques (3)
ROUND(nombre [position]) arrondi à la position spécifiée. Un entier positif (resp. négatif) indique une position après (resp. avant) la virgule. Par défaut il y a arrondi à l’unité.
TRUNC(nombre [position]) troncature à la position spécifiée (voir ROUND). Par défaut il y a troncature à l’unité.
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Conversion en chaˆ?ne de caractères (1)
TO CHAR(nombre[format[nlsparam]])
format est une chaˆ?ne de caractères formée des éléments suivants :
9 un chiffre quelconque
0 un chiffre ou un zéro si absence de chiffres
$ symbole monétaire américain
B remplace le nombre 0 par des blancs
MI signe du nombre post-fixé s’il est négatif, un blanc post-fixé sinon.
S signe du nombre
PR nombre mis entre ”<” et ”>” s’il est négatif, nombre mis entre 2 blancs sinon.
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Conversion en chaˆ?ne de caractères (2)
et des éléments suivants :
D caractère qui sépare la partie entière de la partie fractionnaire
G symbole de séparation de groupes de chiffres
C symbole monétaire international
L symbole monétaire local
. , caractères affichés tels quels
V9 9 multiplie le nombre par 10n o n est le nombre de 9 après V.
EEEE écriture scientifique du nombre
RM rm écriture en chiffres (entre 0 et 3999)
FM écriture scientifique du nombre
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Conversion en chaˆ?ne de caractères (3)
TO CHAR(nombre[format[nlsparam]]) nls param est une chaˆ?ne de caractères formée à partir des expressions suivantes :
NLS NUMERIC CHARACTERS = ”dg”
LS CURRENCY = ”symbolemonétaire local”
NLS ISO CURRENCY = territoire
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions sur les chaˆ?ne de caractères : recherche de sous chaˆ?ne
INSTR(chaˆ?ne, souschaˆ?ne [position [n]])
INSTRB(chaˆ?ne, souschaˆ?ne [position [n]])
à partir d’une position (par défaut 1), exprimée en nombre de caractères ou d’octets, relative au début de la chaˆ?ne si position est positif, ou relative à sa fin si position est négatif, recherche la position de la nième (par défaut 1ère) occurrence de la sous chaˆ?ne, retourne 0 lorsque la recherche échoue.
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions sur les chaˆ?ne de caractères : extraction de sous chaˆ?ne
SUBSTR(chaˆ?ne, position [longueur])
SUBSTRB(chaˆ?ne, position [longueur])
à partir d’une position, extrait une sous chaˆ?ne de longueur donnée (par défaut jusqu’à la fin de la chaˆ?ne).
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions sur les chaˆ?ne de caractères : remplacement ou suppression
REPLACE(chaˆ?ne,souschaˆ?ne [sous chaˆ?ne de remplacement]) remplace ou supprime toutes les occurrences d’une sous chaˆ?ne.
LTRIM(chaˆ?ne [ensemblecaractères à supprimer])
RTRIM(chaˆ?ne [ensemblecaractères à supprimer]) supprime à gauche (ou à droite) de la chaˆ?ne les occurrences des caractères à supprimer (par défaut 1 blanc).
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions sur les chaˆ?ne de caractères : répétition
LPAD(chaˆ?ne,longueur [chaˆ?nerépétée]) RPAD(chaˆ?ne,longueur [chaˆ?nerépétée])
répétition, à gauche (ou à droite) de la chaˆ?ne, d’une autre chaˆ?ne (par défaut 1 blanc) jusqu’à obtenir la longueur spécifiée.
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions sur les chaˆ?ne de caractères : minuscules, majuscules
LOWER(chaˆ?ne) met en minuscules la chaˆ?ne.
UPPER(chaˆ?ne) met en majuscules la chaˆ?ne.
INITCAP(chaˆ?ne) met en majuscules les premières lettres de chaque mot.
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions sur les chaˆ?ne de caractères
LENGTH(chaˆ?ne) longueur de la chaˆ?ne en caractères
LENGTHB(chaˆ?ne) longueur de la chaˆ?ne en octets CHR(code [USING NCHARCS])
retourne le caractère du jeu de caractères de base ou du jeu de caractères local, qui correspond au code. ASCII(chaˆ?ne) retourne le code décimal du premier caractère.
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions sur les chaˆ?ne de caractères : conversion de type
TO NUMBER(chaˆ?ne) retourne le nombre correspondant
HEXTORAW(chaˆ?nehexa) retourne la chaˆ?ne d’octets correspondants
CHARTOROWID(chaˆ?ne) retourne le ROWID correspondant TRANSLATE(chaˆ?ne USING CHAR CS | NCHARCS)
conversion d’un type de chaˆ?ne de caractères basé sur le jeu de caractères de base dans un type de chaˆ?ne de caractères basé sur le jeu de caractères national, et inversement.
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions sur les chaˆ?ne de caractères : traduction (le type est inchangé)
TRANSLATE(chaˆ?ne,listesource, listedestination) traduit la chaˆ?ne en remplaant chacun de ses caractères figurant la liste source par son correspondant dans la liste destination.
CONVERT(chaˆ?ne[jeu de carac dest[jeu de carac source]]) traduction d’un jeu de caractères dans un autre. SOUNDEX(chaˆ?ne) retourne la chaˆ?ne phonétique.
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions sur les dates : recherche
SYSDATE retourne la date courante
LAST DAY(date) retourne la dernière date du mois qui contient la date passée en argument.
NEXTDAY(date, nom jour ds semaine)
retourne la première date postérieure à la date passée en argument et qui correspond au jour de la semaine passé en argument.
Pour connaˆ?tre la date du prochain lundi.
SELECT NEXT DAY(SYSDATE,’Lundi’) ProchainLundi
FROM Dual ;
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions sur les dates : calcul sur les mois
ADD MONTHS(date, nbremois) retourne la date passée en argument augmentée d’un nombre de mois
MONTHS BETWEEN(date1, date2) retourne le nombre de mois qui séparent les 2 dates, avec éventuellement une partie fractionnaire correspondant à une partie de 31 jours.
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions sur les dates : calcul sur les mois
SELECT
monthbetween(’16/03/99’,’01/02/99’) NBmois1, todate(’16/03/99’)-todate(’01/02/99’) NBjours1 monthbetween(’16/04/99’,’01/03/99’) NBmois2, todate(’16/04/99’)-todate(’01/03/99’) NBjours2 From Dual;
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions sur les dates : conversion de date
NEWTIME(date,timezone1,timezone2) retourne la date passée en argument convertie par changement de zone horaire.
AST,ADT Atlantique standard ou décalé
BST,BDT Bering standard ou décalé
CST,CDT Central standard ou décalé
EST,EDT Oriental standard ou décalé
GMT Greenwich
HST,HDT Hawaii-Alaska standard ou décalé
MST,MDT Mountain standard ou décalé
NST Terre-Neuve standard
PST,PDT Pacifique standard ou décalé
YST,YDT Yukon standard ou décalé
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions sur les dates : conversion de date
TO CHAR(date[format[nlslangue]]) conversion d’une date en une chaˆ?ne de caractères.
TO DATE(chaˆ?ne[format[nlslangue]]) conversion d’une chaˆ?ne de caractères en date.
format est une chaˆ?ne de caractères constituée de mots clés nls langue est une chaˆ?ne de la forme :
’NLS DATE LANGUAGE = langue’
langue : french, american, arabic, german (46 langues supportées)
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions sur les dates : conversion de date : formats (1)
SCC, CC siècle avec et sans signe.
SYEAR, YEAR année en lettres avec et sans signe.
SYYYY, YYYY année sur 4 chiffres avec et sans signe.
Y,YYY année sur 4 chiffres avec virgule.
RRRR année sur 4 chiffres ou 2 chiffres avec correction année 2000.
YYY année sur 3 chiffres (les 3 derniers).
YY année sur 2 chiffres (les 2 derniers).
RR année sur 2 chiffres avec correction année 2000.
Y le dernier chiffre de l’année.
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions sur les dates : conversion de date : formats (2)
BC, AD indication BC (Before Christ) ou AD (Ano Domini).
Q N du trimestre de l’année (1-4).
MONTH mois en toutes lettres sur 9 caractères.
MON abréviation du mois sur 3 lettres.
MM N du mois dans l’année.
WW N de la semaine dans l’année (1-53).
IW N de la semaine ISO dans l’année (1-52 ou 1-53).
W N de la semaine dans le mois.
DAY jour en toutes lettres sur 9 caractères.
DY abréviation du jour (2 ou 3 lettres).
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions sur les dates : conversion de date : formats (3)
DDD N du jour dans l’année. DD N du jour dans le mois.
D N du jour dans la semaine.
J jour du calendrier Julien.
AM, PM indication AM ou PM.
HH, HH12 heure sur 12 heures.
HH24 heure sur 24 heures.
MI minutes.
SS secondes par minute (0-59).
SSSSS secondes par jour (0-86399).
”chaˆ?ne” la chaˆ?ne est reproduite telle quelle
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions sur les dates : formats de conversion
les caractères ? / , . ; : sont reproduits tels quels.
utilisation des majuscules et des nimuscules précise le format de sortie.
exemple : ’MONTH’ donnera ’JUIN’ alors que ’Month’ donnera ’Juin’.
suffixes :
TH ajout du suffixe ordinal
SP nombre en toutes lettres
SPTH, THSP nombre en toutes lettres avec ajout du suffixe ordinal
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions sur les dates : formats de conversion
modificateurs :
FM suppression de blancs et de zéros
FX obligation de respecter exactement le format, les blancs sont significatifs afficher la date courante sous différentes formes :
SELECT to char(sysdate,’DAY DD MONTH YYYY’) fmt1, to char(sysdate,’FM DAY DD MONTH YYYY’) fmt2, to char(sysdate,’DD/MM/YYYY HH24 :MI :SS’) fmt3
FROM Dual;
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
Fonctions sur les dates : troncature et arrondi d’une date
TRUNC(date[format])
retourne une date tronquée selon le format spécifié
ROUND(date[format]) retourne une date arrondie selon le format spécifié
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
troncature et arrondi d’une date : format (1)
SCC, CC siècle (arrondi au milieu du siècle)
SYEAR, YEAR année (arrondi au 1er juillet)
SYYYY, YYYY
YYY, YY, Y
IYYY, IYY, IY, I année ISO (arrondi au 1er juillet)
Q trimestre (arrondi au 16ème jour du 2ème mois du trimestre)
MONTH, MON, MM, RMmois (arrondi au 16ème jour)
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
troncature et arrondi d’une date : format (2)
WW semaine (7 jours) définie à partir du 1er janvier (arrondi au 5ème jour)
IW semaine ISO (arrondi au vendredi)
DAY, DY, D premier jour de la semaine, dépend du pays : lundi/France, dimanche/US, (arrondi au 5ème jour)
DDD, DD, J jour, par défaut lorsque le format est absent
(arrondi à la demi-journée)
HH, HH12, HH24 heure (arrondi à la demi-heure)
MI minute (arrondi à 30 secondes)
Fonctions pour requêtes SQL
Fonctions diverses pour requêtes SQL
troncature et arrondi d’une date : exemple
Le premier jour de la première semaine de l’année :
SELECT
to char(trunc(to date(’06/01/99’),’WW’),’FM Day DD’), to char(trunc(to date(’06/01/99’),’IW’),’FM Day DD’) from Dual;
SQL : langage de contrôle de données (LCD)
Sécurité des données
confidentialité gestion des rôles et des utilisateurs attribution de privilèges aux rôles et aux utilisateurs définition de filtres (protection de donées confidentielles, contrôle d’intégrité)
pérennité gestion des transactions
intégrité
gestion des transactions
SQL : langage de contrôle de données (LCD)
transaction : séquence d’opérations manipulant des données vérifient les propriétés suivantes :
atomicité cohérence indépendance permanence
controˆle des transactions :
COMMIT : valide la transaction en cours
ROLLBACK : annule la transaction en cours
Gestion des utilisateurs et des privilèges
création de roˆle
CREATE ROLE nom-de-rôle [IDENTIFIED BY mot-de passe ]; ajout, modification, suppression de mot de passe
ALTER ROLE nom-de-rôle [IDENTIFIED BY mot-de passe ] ; suppression de roˆle
DROP ROLE nom-de-rôle;
Gestion des utilisateurs et des privilèges
création d’utilisateur
CREATE USER nom-d’utilisateur [IDENTIFIED BY mot-de passe ] ;
ajout, modification, suppression de mot de passe
ALTER USER nom-d’utilisateur [IDENTIFIED BY mot-de passe ] ;
suppression de roˆle
DROP USER nom-d’utilisateur;
Gestion des utilisateurs et des privilèges
attribution de privilèges
GRANT systeme-privileges | ALL [privileges ]
TO liste-roles-utilisateurs | PUBLIC
[WITH ADMIN OPTION ] ;
systeme-privileges :
CREATE ROLE
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE PUBLIC SYNONYM
CREATE TABLE
CREATE USER CREATE VIEW
Gestion des utilisateurs et des privilèges
attribution de privilèges sur des objets oracle
GRANT liste-droits
ON nom-composant
TO liste-roles-utilisateurs
[WITH GRANT OPTION ] ; liste-droits :
SELECT
INSERT
UPDATE
DELETE
ALTER
REFERENCES
ALL [PRIVILEGES ]
Gestion des utilisateurs et des privilèges
suppression de privilèges
REVOKE liste-systeme-privileges FROM liste-roles-utilisateurs
suppression de privilèges sur des objets oracle
REVOKE liste-privileges
ON nom-composant
FROM liste-roles-utilisateurs
Gestion des utilisateurs et des privilèges
attribution de roˆles
GRANT liste-roles
TO liste-roles-utilisateurs
[WITH ADMIN OPTION ] ;
suppression de roˆles
REVOKE liste-roles
FROM liste-roles-utilisateurs
- 1>Bibliographie
- 271
Télécharger Cours en pdf du langage procédural PL/SQL
Votez ce document: ☆☆☆☆☆★★★★★
