Upload
others
View
3
Download
0
Embed Size (px)
Citation preview
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Bases de DonneesSQL (Structured Query Language)
Cecile Capponi – Remi [email protected]
Universite d’Aix-Marseille
L3 Info
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Les SQLs
Plusieurs normalisations• SQL1 (1986) : premiere norme• SQL2 (1992) : SQL1 + nouvelles instructions, notamment le JOIN• SQL3 (1999) : SQL2 + apports de l’oriente-objets• SQL2003 : SQL3 + quelques modifications mineurs (e.g. SQL et XML)
Dans ce cours : SQL3
• Sans les aspects objets• Syntaxe Oracle• D’une implementation a l’autre de la norme : les types de base, les
fonctions, et quelques details syntaxiques, varient.
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Classes d’instructions
Du temps de SQL2
• LMD (langage de manipulation des donnees)• LDD (langage de definition des donnees)• LCD (langage de controle des donnees)
SQL3 : 7 classes fonctionnellesUne instruction appartient a l’une des 7 classes :
1. Instructions de connexion (CONNECT, etc.)
2. Instruction de controle (CALL, RETURN, etc.)
3. Instructions sur les donnees, effet persistant (SELECT, INSERT, etc.)
4. Instructions de diagnostic
5. Instructions sur les schemas (CREATE, etc.)
6. Instructions de session (SET, etc.)
7. Instructions de transaction (COMMIT, etc.)
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Dialectes proceduraux SQL
SQL = langage relationnelPas de d’instruction conditionnelle, ni iterative, pas de fonctions niparametres, etc.
Ajout du procedural
• Pour la gestion et le traitement des erreurs• Pour renforcer le maintien de la coherence (les reflexes) sur serveur• Langages definis avec des structures de controle, non normalises.• EX. PLSQL (Oracle: Ada+Pascal), PLpgSQL, T-SQL, etc.
LOOPDBMS_OUTPUT.put_line(TO_CHAR(num)) ;num := num+1 ;
EXIT WHEN num = 10 ; // IF num = 10 THEN EXIT ;END LOOP ;
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Quatre categories de syntaxe
IdentificateursNom donne par le programmeur a un objet de la base (ex. nom de table)
LitterauxValeur de types de base (ex. 2, ’on se calme au fond!’, etc.)
OperateursSymbole (ou mot court) permettant d’executer une action sur desexpressions
Mots reservesMot utilise ailleurs par le SGBD, et/ou nom d’instruction SQL (ex. SELECT,type, etc.)
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Les identificateursDefinitionNom donne par le programmeur a un objet de la base : table, colonne, index,contrainte, etc.
Espaces de noms
• En SQL : chaque objet a un identificateur unique a l’interieur de sonespace de noms (categorie)
• Espaces de noms hierarchises de facon ensembliste• Portee d’un identificateur : espaces de proggrammation ou l’on peut
l’utiliser = son espace de noms N, tous ceux que N contient, et celui quicontient directement N
ExempleDeux espaces de noms differents :Table Appartement(adresse, proprietaire, surface, CHECKsurface > 9)Table PlanTravail(marque, surface, CHECK surface IN (bois,marbre))En dehors de ces tables : Appartement.surface etPlanTravail.surface.
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Hierarchie d’espaces de noms
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Conventions de nommage
Les principales
• Identificateur significatif, pertinent, evocateur (Acteur vs. AA TB)• Choisir et appliquer la meme casse partout (Oracle distingue Maj et Min)• Coherence dans les abbreviations (id, OU num, mais pas les deux)• Noms complets separes pat• Pas de nom copyrighte car peut evoluer• Pas de prefixe (suffixe) evident (ex. Table Acteur)• Pas de double apostophe (. cf type dans Hotel)
Les specifiques
• Une norme, mais restent propres a chaque SGBD (cf. documentation !)• Exemples : max 128 caracteres en SQL3, max 64 caracteres dans
MySQL. Certains caracteres admis dans des SGBD mais pas dansd’autres (pb de portabilite).
• Faire au plus simple...
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Les litteraux
Les valeurs des types de baseUn litteral correspond a une valeur prise dans un type predefini du SGBD :entiers, chaınes de caracteres, etc.• Entiers : 321, -42, 0, +20• Reels (point) : 88.7, 7e3, etc.• Booleens (pas dans tous les SGBDs), a eviter• Chaınes de caracteres (apostrophes simples) : ’Hello world’
• Dates : selon le SGBD (’4 DEC 2014 20:00:00:00’)
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Les operateurs
Designe une operation unaire ou binaireSymbole ou mot court qui designe une action a effectuer sur desexpressions.• Exemples : + (addition), AND (et logique)• Priorites entre operateurs (a connaıtre, dans le doute : parentheses)• Dependent du SGBD (documentation !)• Principalement utilises dans le LMD (SELECT, INSERT, etc.)
Categories d’operateurs
• Comparaison• Arithmetiques• Logiques• Inclassables
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Categories d’operateurs
Operateurs arithmetiquesAction entre expressions numeriques• Addition, soustraction, changement de signe, multiplication, division +,-, *, /
• Operande NULL (valeur non renseigne) : resultat incalculable (NULL)• Extensions selon SGBD : + concatenation de chaınes, operations entre
dates
Operateurs de comparaisonsPour comparer, selon egalite ou un ordre, des expressions numeriques (ouautres).• Resultat booleen• Un operande inconnu : resultat inconnu• =, <>, <, >, <=, >=
• Extensions selon SGBD : dates, chaınes (LIKE), etc.
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Categories d’operateurs (cont’d)Operateurs logiquesAction principalement effectuee dans des clauses de conditions telles queWHERE et ON.• Resultat booleen, ou inconnu (NULL)• Operande NULL (valeur non renseigne) : resultat parfois incalculable
(NULL)• Pas de reelle norme, selon SGBD)• Principaux : AND, OR, NOT
• Autres courants : ALL, ANY, SOME, EXISTS, BETWEEN, IN
Autres operateurs (selon SGBD
• Caractere Joker (% en Oracle)• Le tiret : soustraction, oppose, mais aussi specificateur d’intervalle
ISBN LIKE ’[2-4]%’ : un numero d’ISBN est une chaınecommencant par un chiffre entre 2 et 4.
• Le point : virgule des flottants, mais aussi pour qualifier un identificateurActeur.nom
• La virgule : separateur des elements d’une liste• Commentaires : /* mon commentaire */
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Priorite entre operateurs
Par ordre de priorite decroissante (lecture des expressions de gauche adroite) :• ( et )• + et - unaires• * et /• + et - binaires• =, <, >, <=, >=, <>• NOT
• AND
• ALL, ANY, SOME, BETWEEN, LIKE, OR
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Instructions SQL sur le schemas
Creer, modifier, supprimer :
des bases de donnees, des tables, des colonnes, des contraintes, dessequences, etc.
Creation d’un schema de base de donneesCf. Hierarchie des espaces de noms : un schema contient un ensembled’objets structurant une base de donnees
CREATE DATABASE <identificateur base> <options>
Quelques options :• CHARACTER SET charset
• NATIONAL CHARACTER SET charset
• SET TIMEZONE = ’time zone region’
Suppression d’un schema
DROP <identificateur base>
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Creation d’une table
Une table est la realisation d’une relation.
CREATE TABLE <ide table> (<ide col> <type col> <spec col> [, ...]| CONSTRAINTS <ide contr> <type contr> <contr> [, ...]| AS <sous requete>
)
ou ide = identificateur, col = colonne, contr = contrainte, spec =specification
• Definition des colonnes nommees (attributs), et des contraintes sur cescolonnes (spec)
• Definition des contraintes nommees sur la table (ou sur un ensemble decolonnes).
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Creation d’une table : les colonnes
<ide col> <type col> <spec col>
• Nom : cf. regles des identificateurs et espaces de noms• Type de donnees : types de base du SGBD• Specifications possibles (contraintes nommees et non-nommees) :
NOT NULLDEFAULT (<expr>)[ CONSTRAINT <ide contr>
{ UNIQUE | PRIMARY KEY | CHECK ( <exp bool> )| REFERENCES <ide table> [ <ide cols> ]
[ ON DELETE { CASCADE | SET NULL }] ]• Contraintes de colonnes
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Creation d’une table : les colonnes (suite)Explication des contraintes
• NOT NULL : la colonne doit avoir une valeur• DEFAULT : permet de specifier une valeur par defaut pour toutes les
lignes, lorsque la vraie valeur n’est pas donnee.• PRIMARY KEY : specifie que la cle primaire est composee de cette
colonne et d’aucune autre. Cf. contraintes de table.• UNIQUE : deux tuples de la table ne peuvent pas avoir la meme valeur
pour cette colonne. Implicite s’il y a une contrainte de cle primaire sur lacolonne.
• CHECK ( exp bool ) : specification des valeurs possibles pour lacolonne.
Exemple
CREATE TABLE Patient (num_patient CONSTRAINT pk_patient PRIMARY KEY,nom VARCHAR2 NOT NULL,age DEFAULT 18 CONSTRAINT ch_age CHECK ( age >= 0),num_secu VARCHAR2 CONSTRAINT un_secu UNIQUE
)
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Creation d’une table : les colonnes (suite)Explication des contraintes
• NOT NULL : la colonne doit avoir une valeur• DEFAULT : permet de specifier une valeur par defaut pour toutes les
lignes, lorsque la vraie valeur n’est pas donnee.• PRIMARY KEY : specifie que la cle primaire est composee de cette
colonne et d’aucune autre. Cf. contraintes de table.• UNIQUE : deux tuples de la table ne peuvent pas avoir la meme valeur
pour cette colonne. Implicite s’il y a une contrainte de cle primaire sur lacolonne.
• CHECK ( exp bool ) : specification des valeurs possibles pour lacolonne.
Exemple (sans noms de contraintes)
CREATE TABLE Patient (num_patient PRIMARY KEY,nom VARCHAR2 NOT NULL,age DEFAULT 18 CHECK ( age >= 0),num_secu VARCHAR2 UNIQUE
)
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Contraintes de colonnes parfois insuffisantes
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Creation d’une table : contraintes de tables
Contraintes de table (ou contrainte de ligne)
• Specifiees apres les descriptions de colonnes• Contraintes nommees• Mise en jeu de plusieurs colonnes !• Specification de cles etrangeres, gestion de la coherence par le SGBD
CONSTRAINT <ide contr>PRIMARY KEY ( <ide col i1> [, <ide col i2> ...] )
CONSTRAINT <ide contr>FOREIGN KEY ( <ide col i1> [, <ide col i2> ...] )REFERENCES <ide table> ( <ide col e1> [, <ide col e2> ...] )
[ ON DELETE { CASCADE | SET NULL } ]
(aussi CHECK, UNIQUE, etc.)
Gestion de la coherenceLorsque la ligne referencee est supprimee, que faire des lignes qui lareferencaient ?• Cascade : les lignes referentes sont aussi supprimees• Set null : la valeur des colonnes des lignes referentes devient NULL (si
possible)
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Gestion de la coherence
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Gestion de la coherence
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Gestion de la coherence
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Creation d’une table : exemple (1)
CREATE TABLE Lieu (longitude NUMBER(2),latitude NUMBER(2),nom_lieu VARCHAR2(30) UNIQUE,CONSTRAINT pk_lieu
PRIMARY KEY ( longitude, latitude ),CONSTRAINT ch_lieu
CHECK (longitude > 0 AND latitude > 0));
En contraintes de colonnes : lorsqu’une seule colonne est impliqueeEn contraintes de lignes (tables) : lorsque plusieurs colonnes (pour chaqueligne) sont impliquees
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Creation d’une table : exemple (2)
CREATE TABLE Station (id_station NUMBER(2) CONSTRAINT pk_station PRIMARY KEY,nom_station VARCHAR2(30) NOT NULL,longitude NUMBER(2),latitude NUMBER(2),max_v NUMBER(2) CONSTRAINT ch_station CHECK (max_v > 2),h_ouv DATE DEFAULT TO_DATE(’05:00’, ’HH24:MI’),h_fer DATE DEFAULT TO_DATE(’22:30’, ’HH24:MI’),CONSTRAINT ch_heures
CHECK ( h_ouv < h_fer ),CONSTRAINT fk_st_lieu
FOREIGN KEY (longitude,latitude)REFERENCES Lieu (longitude, latitude)ON DELETE SET NULL
);
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Modification d’une table
SyntaxeALTER TABLE <ide table>[ RENAME TO <new ide table> ]| [ { ADD , MODIFY } <ide col> <type col> <spec col> ]| [ DROP COLUMN <ide col> [ CASCADE CONSTRAINTS |INVALIDATE ] ]| [ ADD <contr table> ]| [ DISABLE CONSTRAINT <ide contr> ]| [ RENAME COLUMN <old ide col> TO <new ide col> ]
Verifications du SGBDLa modification est annulee si :• une ligne ne verifie (pas) plus une contrainte, ou• si une convention n’est pas respectee.
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Modification d’une table : exemple
Soit une ligne de la table Lieu precedemment inseree par la commande :
INSERT INTO Lieu (longitude, latitude, nom_lieu)VALUES (43.30, 5.37, ’Mairie Marseille’ ) ;
L’instruction :
ALTER TABLE Lieu ADD COLUMN ville VARCHAR(10) NOT NULL ;
mene a l’erreur :
ERROR : column "ville" contains null values
On procede donc en plusieurs etapes :
ALTER TABLE Lieu ADD COLUMN ville ;UPDATE Lieu SET ville =’NOWHERE’ ;ALTER TABLE Lieu MODIFY ville SET NOT NULL ;
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Suppression d’une table
SyntaxeDROP TABLE <ide table> [ CASCADE CONSTRAINTS ]
Verification de la coherence par le SGBDQue faire des lignes qui font references a celles supprimes ?• Le programmeur peut le specifier via l’option CASCADE CONSTRAINTS :
les contraintes liant des lignes a celles supprimees, sont aussisupprimees.
• Si le programmeur ne specifie rien, alors la suppression n’est paseffectuee des lors qu’elle affecte d’autres objets.
Exemple
> DROP TABLE Lieu CASCADE CONSTRAINTS ;NOTICE: drop cascades to constraint to fk_st_lieu
versus
> DROP TABLE Lieu ;ERROR: cannot drop table Lieu because other objects depends on it
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Autres commandes sur les schemas
TRUNCATE TABLE <ide table>
COMMENT ON TABLE <ide table> IS <commentaire>
CREATE SEQUENCE <ide seq>| INCREMENT BY <entier> | START WITH <entier>| MAXVALUE <entier> | NOMAXVALUE| MINVALUE <entier> | NOMINVALUE| CYCLE | NOCYCLE
Utilisation des sequencesAttribution automatique de valeur de cle primaire
CREATE SEQUENCE hotel_numhotel_seqSTART WITH 1 INCREMENT by 1;
INSERT INTO hotelVALUES ( hotel_numhotel_seq.nextval,
’L excelsior’, ’Nice’, 3 );
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Instructions sur les donnees
• Ajouter, modifier, supprimer les donnees, en toute coherence• Interroger les donnees
Ajout de donneesINSERT INTO <ide table> [ (<ide col1>, ide col2>, ...) ]
VALUES (<val col1>, val col2>, ...)
• Respect des types• Si colonnes non specifiees, l’ordre dans la definition de la table est
considere.• Colonne non enoncee : valeur NULL• Violation de contrainte : ajout non realise• Possibilite d’utiliser la valeur NULL
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Ajout de donnees : exemples
Bons exemples
INSERT INTO Station (id_station, nom_station, max_v)VALUES (47, ’vs_vieuxport’, 25)
equivalent a
INSERT INTO Station(id_station, nom_station, h_ouv, h_fer, max_v, )
VALUES (47, ’vs_vieuxport’, DEFAULT, DEFAULT, 25)
Mauvais exempleD’ou vient l’erreur generee par l’instruction suivante :
INSERT INTO Station(id_station, nom_station, h_ouv, h_fer, max_v, )
VALUES (48, ’vs_vieuxport’, DEFAULT, DEFAULT, 1)
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Modification de donnees
SyntaxeUPDATE <ide table> SET {( <ide col1>, <ide col2>, ... ) = ( <val col1>,<val col2>, ... )| ( <ide col1> = <val col1>, <ide col2> = <val col2>, ...) }[ WHERE <condition> ]
Restrictions• Valeurs = scalaires valuables dans le bon type, ou DEFAULT, ou NULL
• Modifie toutes les lignes de la table qui respectent la condition de laclause WHERE
• Si la modification d’une ligne mene a la violation d’une contrainte, alorscette modification n’est pas effectuee.
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Modification de donnees : exemples
Bons exemples
UPDATE hotel SET ville = UPPER(ville) ;
UPDATE hotel SET etoiles=3 WHERE numhotel=4 ;
UPDATE hotel SET prixnuitht = prixnuitht*1.05WHERE numhotel IN ( SELECT numhotel
FROM hotelWHERE ville = ’Grenoble’ )
Mauvais exempleCette instruction mene a une erreur: pourquoi ?
UPDATE Station SET h_ouv = TO_DATE(’06:00’, ’HH24:MI’)
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Suppression de donnees
Syntaxe{ DELETE FROM <ide table> WHERE <condition>| DELETE FROM <ide table> }
CoherenceQue deviennent les lignes qui referencent celles supprimees ?
Cf. ON DELETE
Exemple
DELETE FROM Hotel WHERE nom_hotel LIKE ’%PORT%’ ;
Selon la clause ON DELETE, les lignes referencant les hotels supprimes sontsoit aussi supprimees, soit la reference devient NULL.
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Interrogation des donnees
Syntaxe principaleSELECT [ DISTINCT ] <liste ide col resultat>FROM <liste ide tables>[ JOIN <ide table> [ ON ( <condition> ) | USING ( <liste ide col) ] ]∗
[ WHERE <condition> ][ GROUP BY <liste ide col> ][ HAVING <condition sur groupe> ]
[ ORDER BY <liste ide col> [ ASC | DESC ] ]
SELECT = Construire une relation• dont les colonnes sont celles de <liste ide col resultat>
• dont les lignes sont des combinaisons de lignes, prises dans les tablesspecifiees dans <liste ide tables> et les <ide table>, et quiverifient les conditions du WHERE.
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Interrogation des donnees (cont’d)
Precisions• SELECT est suivi de :
• noms de colonnes existantes dans les tables specifiees apres FROM et JOIN,• expressions construites sur ces colonnes, via operateurs ou fonctions• expressions construites sur ces colonnes, via fonctions d’agregats
• Prefixage si ambiguıte dans l’espace de noms• La clause DISTINCT elimine les lignes identiques dans le resultat• FROM et JOIN introduisent les tables (relations) impliquees :
• tables nommees• resultats de sous-requetes nommees
SELECT ... FROM ( SELECT ... AS ...)
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Interrogation des donnees (cont’d)
La jointure• Une clause JOIN (eventuellement LEFT, RIGHT, NATURAL),
s’accompagne d’une condition de jointure :• soit explicite avec ON• soit egalite implicite entre colonnes de meme nom avec USING
Deux exemples equivalents
SELECT client.numclient, nom, prenomFROM occupationJOIN chambre ON ( occupation.numch = chambre.numch
AND occupation.numh = chambre.numh )LEFT OUTER JOIN client ON ( client.numcli=occupation.numcli )WHERE etage <> 1
SELECT client.numclient, nom, prenomFROM occupationJOIN chambre USING ( numch, numh )LEFT OUTER JOIN client USING ( numcli )WHERE etage <> 1
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Les agregats en SQL
AGREGAT( R ; A ; f(A’) )
Clause GROUP BYSpecifie les attributs A sur lesquels s’effectue le regroupement• Au moins tous les attributs du SELECT, sauf ceux sur lesquels
s’appliquent les fonctions d’agregat (A’).
Clause HAVINGSpecifie des conditions sur le regroupement
ExempleRealisateur et date de son dernier film realise, seulement pour lesrealisateurs ayant realise plus de 5 films ou dont le premier film est anterieura 1980.
SELECT realisateur, MAX(annee) AS maxaFROM FilmGROUP BY realisateurHAVING COUNT(annee)>=5 OR MIN(annee)<=1980
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Agregats, exemple (cont’d)
realisateur anneePontax 1992
1995Blanc 1999
19992001198520102006
Bertol 1978Coen 1996
199719992001200220022009
realisateur maxaBlanc 2010Bertol 1978Coen 2009
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Les sous-SELECT
Pour definir une table intermediaire
SELECT realisateurFROM ( SELECT realisateur, min(annee) AS mina,
max(annee) AS maxaFROM FilmGROUP BY realisateur)
AS FILMS_RECENTS )WHERE maxa >= mina+20
A la place d’une jointure : condition de WHERE (a eviter !!!)
SELECT *FROM FilmWHERE numfilm IN ( SELECT numfilm
FROM CastingWHERE personnage = ’Asterix’ )
Introduction Categories de syntaxe SQL sur les schemas Instructions SQL sur les donnees
Operations ensemblistes
Syntaxes
( <requete1 ) UNION ALL ( <requete2 )( <requete1 ) UNION ( <requete2 )
( <requete1 ) INTERSECT ( <requete2 )( <requete1 ) MINUS ( <requete2 )