bases de données sql (structured query language) · 2018-09-23 · pas de double apostophe (. cf...

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


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.


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.)


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 ;


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.)


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.


Hierarchie d’espaces de noms


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...


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’)


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


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.


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 */


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


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>


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).


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


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

)


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

)


Contraintes de colonnes parfois insuffisantes


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)


Gestion de la coherence


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


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

);


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.


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 ;


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


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 );


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


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)


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.


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’)


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.


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.


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 ...)


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


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


Agregats, exemple (cont’d)

realisateur anneePontax 1992

1995Blanc 1999

19992001198520102006

Bertol 1978Coen 1996

199719992001200220022009

realisateur maxaBlanc 2010Bertol 1978Coen 2009


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’ )


Operations ensemblistes

Syntaxes

( <requete1 ) UNION ALL ( <requete2 )( <requete1 ) UNION ( <requete2 )

( <requete1 ) INTERSECT ( <requete2 )( <requete1 ) MINUS ( <requete2 )

bases de données sql (structured query language) · 2018-09-23 · pas de double apostophe (. cf...

Documents