Utilisation de PostgreSQL
Le langage SQL
PostgreSQL : historique! Systme de gestion de bases de donnes
relationnelles ! Projet dbut en 1977 par lUniversit de Berkeley
(projet Ingres, dvelopp ensuite et commercialis par Relational
Technologies/Ingres Corporation) ! 1986 : une nouvelle quipe de
lUniversit de Berkeley continue le dveloppement du projet Ingres,
nomm alors Postgres ! 1996 : devient PostgreSQL (aprs ajout de
nouvelles fonctionnalits par la communaut du logiciel libre)1SQL et
PostreSQL
Yannick Estve
Universit du Maine Institut Claude ChappeSQL et PostreSQL
2
PostgreSQL : Prsentation! Projet de SGBDR non commercial le plus
avanc ! Projet Open Source toujours en dveloppement ! Existence de
distributions commerciales (support technique) ! Version stable
actuelle : 7.4.5 (en version bta : 8.0, en TP : 7.3.4)SQL et
PostreSQL
PostgreSQL : fonctionnalits (1)! SGBD relationnel-objet !
Hautement extensible : lutilisateur peut dfinir des oprateurs, des
fonctions, des types de donnes, ! Support complet de SQL :
reconnaissance de SQL 99 + fonctionnalits avances ! Intgrit
rfrentielle : validit des donnes dune base garantie3SQL et
PostreSQL
4
PostgreSQL : fonctionnalits (2)! Interfaces de programmation
souples : souplesse des API. Interfaces pour Object Pascal, Python,
Perl, PHP, ODBC, JDBC, Ruby, TCL, C/C++ et Pike ! Langage de
procdures : gestion en interne de langage procduraux : Perl,
Python, TCL ou PL/pgSQL
PostgreSQL : fonctionnalits (3)! MVCC : Multi-Version
Concurrency Control. Pas de verrouillage inutiles (contrairement
Access ou MySQL). Gestion des transactions ! Architecture
client/serveur ! WAL : Write Ahead Logging. Amliore la fiabilit de
la base de donnes en mmorisant les modifications avant leur
validation sur la base.
SQL et PostreSQL
5
SQL et PostreSQL
6
SQL : Historique (1)! Modle relationnel dfini en 1970 (E.F.
Codd, chercheur de IBM) ! 1974 : Anctre de SQL : SEQUEL (Structured
English Query Language) dvelopp lors du projet System/R ! 1977 :
intgration des fonctionnalits multitables et multi-utilisateurs
(SEQUEL/2, rebaptis SQL)SQL et PostreSQL
SQL : Historique (2)! 1978 : test clients. Succs pour IBM. !
Produits commerciaux dIBM : SQL/DS (1981), DB2 (1983) ! Autres
produits de la mme poque reposant sur SQL : Oracle (commercialis en
1979), Sybase, Ingres
7
SQL et PostreSQL
8
SQL : Normes! Fin des annes 80, plusieurs normes :" ANSI en 1986
(X3.135) " ISO en 1987 " ANSI/ISO en 1989 : SQL89 ou SQL1
PostgreSQL et SQL! PostgreSQL 7.3.4 est conforme au niveau
minimal de SQL92, ainsi qu une trs grande partie des niveaux
intermdiaire et total ! PostgreSQL 7.3.4 est trs proche de SQL99
(PostgreSQL tant un pionnier pour beaucoup des spcificits ajoutes
SQL99)
! ANSI en 1992, SQL92 ou SQL2 : consolidation de
SQL89, ajout de spcification, 3 niveaux de de conformit
(minimale, intermdiaire, totale) parties plus avances (objet,
interface de programmation, gestion de lintgrit). Deux niveaux de
conformit : Core SQL99 (SQL99 minimal) et Enhanced SQL99 (SQL99
amlior)SQL et PostreSQL
! ANSI/ISO en 1999, SQL99 ou SQL3 : intgration de
9
SQL et PostreSQL
10
Notion de table! Une table est forme de colonnes et de lignes,
dont lintersection forme des champs ! Les colonnes dcrivent le nom
et le type des donnes qui se trouveront dans les lignes ! Les
lignes reprsentent des enregistrements, dcrits par le nom et le
type des colonnes correspondantes
Exemple de table : la table livre
id 7808 4513 4267
titre The shining Dune 2001 : A Space Odyssey
id_auteur 4156 1866 2001
id_sujet 9 15 15
SQL et PostreSQL
11
SQL et PostreSQL
12
Ce que nest pas SQL! SQL nest pas un langage procdural de type
Pascal ou C ! Pour rsoudre un problme avec SQL, il faut indiquer ce
que lon veut : pas la peine dindiquer au systme comment obtenir ce
que lon veut ! Le SGBD dcide de la meilleure manire dagir pour
rpondre nos attentesSQL et PostreSQL
Exemple de requte SQL
SELECT titre FROM livre WHERE id_sujet=15 ;
13
SQL et PostreSQL
14
Commandes SQL essentielles de PostreSQL (1)Commande CREATE
DATABASE CREATE INDEX CREATE SEQUENCE CREATE TABLE CREATE TRIGGER
CREATE VIEW SELECT Description Cre une base de donnes Cre un index
sur une colonne Cre une squence dans une BD Cre une table dans une
BD Cre un trigger dans une BD Cre une vue dune table Rcupre des
enregistrementsSQL et PostreSQL
Commandes SQL essentielles de PostreSQL (2)Commande INSERT INTO
UPDATE DELETE DROP DATABASE DROP INDEX DROP SEQUENCE DROP
TABLE15
Description Ajoute un ou des enregistrements Modifie un ou des
enregistrements Supprime un ou des enregistrements Supprime une BD
Supprime un index dune table Supprime une squence Supprime une
tableSQL et PostreSQL
16
Commandes SQL essentielles de PostreSQL (3)Commande DROP TRIGGER
DROP VIEW CREATE USER ALTER USER DROP USER GRANT REVOKE Description
Supprime un trigger Supprime une vue Ajoute une compte utilisateur
Modifie un compte utilisateur Supprime un compte utilisateur Donne
des droits sur un objet Supprime ces droitsSQL et PostreSQL
Commandes SQL essentielles de PostreSQL (4)Commande CREATE
FUNCTION CREATE LANGUAGE CREATE OPERATOR CREATE TYPE Description
Cre une fonction SQL dans une BD Dfini un langage dans une BD Dfini
un oprateur dans une BD Dfini un type SQL dans une BD
17
SQL et PostreSQL
18
Mots-cls et identificateurs! Les mots-cls sont tous les termes
rservs de SQL (commandes, oprateurs, ) ! Identificateurs : objets
dfinis en interne (nom de table, de fonction, etc.) ! Mots-cls et
identificateurs :" Longueur maxi 31 caractres (au-del, tronqus) "
Les identificateurs commencent par une lettre ou par un blanc
soulign. Les mots-cls ne commencent que par une lettre. Dans les 2
cas, les caractres suivants peuvent tre des lettres, des chiffres
ou des blancs soulignsSQL et PostreSQL
Types de donnes SQL : Langage fortement typ
19
SQL et PostreSQL
20
Boolens et binaires
Types caractres
Type boolean, bool bit(n) bit varying(n), varbit(n)
Description Valeur unique : true ou false Chane de bits de
longueur n chane de bits de longueur n variable (n=taille max)
Norme SQL99 SQL92 SQL92
Type character(n), char (n) character varying (n), varchar(n)
text
Description
Norme
Chane de caractres de SQL89 longueur n Chane de caractres de
SQL92 longueur n variable Chane de caractres de Spcificit longueur
variable et PostgreSQL illimiteSQL et PostreSQL
SQL et PostreSQL
21
22
Types numriques (1)
Types numriques (2)
Type smallint, int2 integer, int, int4 bigint, int8
Description Entier sur 2 octets Entier sur 4 octets Entier sur 8
octets (jusqu 18 chiffres)
Norme SQL89 SQL92 Spcif. P_SQL
Type real, oat4 double precision, oat8, oat numeric(p,s),
decimal(p,s)
Description Flottant sur 4 octets Flottant sur 8 octets Type
numrique exact avec prcision p quelconque et facteur dchelle s
Norme SQL89 SQL89 Spcif. P_SQL
SQL et PostreSQL
23
SQL et PostreSQL
24
Types numriques (3)Type date time time with time zone
interval
Date et heureDescription Date du calendrier (jour, mois, anne)
Heure Heure avec les informations sur la zone horaire Dlai
quelconque Norme SQL92 SQL92 SQL92 SQL92
Type money serial
Description
Norme
Valeur montaire de type US, obsolte Spcif. P_SQL Entier sur 4
octets avec incrmentation automatique Spcif. P_SQL
SQL et PostreSQL
25
SQL et PostreSQL
26
Autres types! Dautres types existent spcifiques PostgreSQL :"
Types gomtriques : box, line, lseq, circle, path, point, polygon "
Types pour le rseau : cidr (Adresse/masque IP), inet (Adresse IP),
macadr (Adresse MAC, ex. adresse Ethernet) " Types pour systme :
oid, xid
Oprateurs
SQL et PostreSQL
27
SQL et PostreSQL
28
Oprateurs! Oprateurs sur les chanes de caractres ! Oprateurs sur
les nombres ! Oprateurs logiques
Oprateurs de comparaison sur les chanes de caractresOprateur =
!= < >= Description teste lgalit (true si galit) teste
lingalit (true si ingal) idem != teste lordre alphabtique (ex. : a
= = ou != Description strictement plus petit strictement plus
grand infrieur ou gal suprieur ou gal galit ingalitSQL et
PostreSQLOprateurs logiquesAND (et boolen) OR (ou boolen) NOT (non
boolen)35SQL et PostreSQL36Fonctions agrgats! Fonctions portant sur
plusieurs lignes SELECT avg(sal) FROM employe; SELECT grade,
avg(sal) FROM employe GROUP BY grade ORDER BY avg
DESC;FonctionFonctions agrgats (2)DescriptionMoyenne pour toutes
les lignes dun groupe Nombre de valeurs, par groupe de lignes, non
NULL Valeur maximale dans un groupe de ligne Valeur minimale
Renvoie lcart-type Renvoie la sommeavg(expression)
count(expression) max(expression) min(expression)
stddev(expression) sum(expression)variance(expression) Renvoie la
varianceSQL et PostreSQL37SQL et PostreSQL38Fonctions de conversion
de typesFonction bitfromint4(n) bittoint4(b) to_char(n, f)
to_date(s, f) DescriptionConvertit une valeur numrique en chane de
bits Convertit une chane de bit dans sa reprsentation dcimale
Convertit une valeur numrique n en une chane de caractre selon le
format f Convertit une chane de caractres s en type date sous le
format f selon le format fFonctions de conversion de types
(suite)Fonction Descriptionto_timestamp(s, Convertit la chane de
caractres s en une valeur de type timestamp selon le format f f)
Convertit d, de type date, en valeur de type
timestamp(d)timestamptimestamp(d, t)to_number(s, f) Convertit la
chane s en une valeur de type numricRenvoie une valeur de type
timestamp construite partir de d et t, respectivement de type date
et timeSQL et PostreSQL39SQL et PostreSQL40Exemple de
conversionSELECT to_char(123456789, 999G999G999D99) AS format,
to_char(123456789, 999999999) AS chi"res_seuls, to_char(123456789,
00999999999) AS avec_zeros; format | chiffres_seuls | avec_zeros
---------------------+---------------------+-----------------------------------123.456.789,00
| 123456789 | 00123456789Rcupration de lignes avec SELECTSQL et
PostreSQL41SQL et PostreSQL42Select! La commande SELECT est au cur
de toutes les requtes SQL ! Les donnes renvoyes forment un ensemble
rsultat form de lignes et de colonnes ! Lensemble rsultat nest pas
stock sur disque : rsultat temporaire ! Commande SELECT : commande
certainement la plus complexeSyntaxe de SELECTSELECT [ ALL |
DISTINCT [ ON ( expression [, ...] ) ] ] * | cible [ AS nom ] [,
...] [ FROM source [, ...] ] [ [ NATURAL ] type_jointure [ ON
condition ] | USING ( liste_colonnes ) ] ] [, ] [ WHERE condition ]
[ GROUP BY expression [, ...] ] [ HAVING condition [, ...] ] [ {
UNION | INTERSECT | EXCEPT } [ ALL ] sous-requete ] [ ORDER BY
expression [ ASC | DESC | USING operateur ] [, ...] ] [ LIMIT {
nombre | ALL } ] [ OFFSET debut ] [ FOR UPDATE [ OF nom_table [,
...] ] ]SQL et PostreSQL43SQL et PostreSQL44Syntaxe de SELECT :
dtails" ALL : inutile, juste pour la lisibilit " DISTINCT [ ON (
expression [, ] ) ] : prcise que lon ne renverra quune ligne en cas
de valeurs dupliques de expression " cible [ AS nom ] [, ] : nom de
colonne, constante, identificateur, ou fonction. Peut tre renomme
en nom (alias) avec la clause ASSQL et PostreSQLSyntaxe de SELECT :
dtails (suite)" FROM source [, ] : indique la source dans laquelle
chercher les cibles voulues. source est un nom de table ou une
sous-requte. " WHERE condition : cette clause contraint lensemble
rsultat de linstruction SELECT en fonction de critres dfinis par
condition45SQL et PostreSQL46Syntaxe de SELECTSELECT [ ALL |
DISTINCT [ ON ( expression [, ...] ) ] ] * | cible [ AS nom ] [,
...] [ FROM source [, ...] ] [ [ NATURAL ] type_jointure [ ON
condition ] | USING ( liste_colonnes ) ] ] [, ] [ WHERE condition ]
[ GROUP BY expression [, ...] ] [ HAVING condition [, ...] ] [ {
UNION | INTERSECT | EXCEPT } [ ALL ] sous-requete ] [ ORDER BY
expression [ ASC | DESC | USING operateur ] [, ...] ] [ LIMIT {
nombre | ALL } ] [ OFFSET debut ] [ FOR UPDATE [ OF nom_table [,
...] ] ]Syntaxe de SELECT : dtails (suite)" GROUP BY expression [,
] : regroupe des lignes selon le critre de expression " HAVING
condition : teste les conditions sur les ensembles groups par GROUP
BY au lieu de le faire sur chaque ligneSQL et PostreSQL47SQL et
PostreSQL48Syntaxe de SELECTSELECT [ ALL | DISTINCT [ ON (
expression [, ...] ) ] ] * | cible [ AS nom ] [, ...] [ FROM source
[, ...] ] [ [ NATURAL ] type_jointure [ ON condition ] | USING (
liste_colonnes ) ] ] [, ] [ WHERE condition ] [ GROUP BY expression
[, ...] ] [ HAVING condition [, ...] ] [ { UNION | INTERSECT |
EXCEPT } [ ALL ] sous-requete ] [ ORDER BY expression [ ASC | DESC
| USING operateur ] [, ...] ] [ LIMIT { nombre | ALL } ] [ OFFSET
debut ] [ FOR UPDATE [ OF nom_table [, ...] ] ]Syntaxe de SELECT :
dtails (suite)" FROM source1 [ NATURAL ] type_jointure source2 ON (
condition [, ] ) USING ( colonne [, ] ) # 3 types de jointures :$
jointures produits ( CROSS JOIN ), $ jointures internes ( [INNER
JOIN] ) $ jointures externes ( LEFT [OUTER] JOIN, RIGHT [OUTER]
JOIN, FULL [OUTER] JOIN )49SQL et PostreSQLSQL et
PostreSQL50Syntaxe de SELECT : Jointures produitsSyntaxe de SELECT
: Jointures internes" FROM source1 [NATURAL] [INNER] JOIN source2
ON ( condition, [, ] ) USING ( colonne, [, ] ) $ Produit cartsien
de source1 et source2, mais seules les lignes qui respectent les
contraintes explicites dans ON ( condition ) sont conserves. $
USING dtermine les noms de colonnes identiques pour lesquelles il
faut que les valeurs soient gales pour effectuer la jointure $
Lutilisation de NATURAL indique que toutes les colonnes de noms
identiques doivent contenir la mme valeur pour tre jointe" FROM
source1 CROSS JOIN source2 $ Produit cartsien de source1 et source2
$ Equivalent : FROM source1, source2SQL et PostreSQL51SQL et
PostreSQL52Exemple de jointure interneSELECT * FROM stock NATURAL
INNER JOIN editions;isbn | prix_achat | prix_vente | stock |
id_livre | edition | id_editeur | parution | type 29.00 | 30.00 |
16.00 | 23.00 | 36.00 | 17.00 | 36.95 | 32.95 | 16.95 | 23.95 |
45.95 | 21.95 | 65 | 31 | 4| 0| 89 | 77 | 7808 | 1608 | 1590 | 1608
| 4513 | 4513 | 2| 1| 1| 1| 3| 2| 75 | 1993-10-01 | h 59 |
1957-03-01 | h 59 | 1949-03-01 | p 59 | 1957-01-01 | p 99 |
1999-10-01 | h 99 | 1998-09-01 | pExemple de jointure externeSELECT
titre, isbn FROM livres LEFT OUTER JOIN editions ON
(livres.id=editions.id_livre);titre | isbn
-----------------------------+-----------The Tell-Tale Heart |
1885418035 The Tell-Tale Heart | 0929605942 Little Women |
0760720002 The Velveteen Rabbit | 0679803335 The Shining |
0451160916 Franklin in the Dark | 0590445065 Practical PostgreSQL |
Programming Python | 0596000855 Learning Python | Perl Cookbook
|------------+------------+------------+-------+----------+---------+------------+------------+-----0385121679
| 039480001X | 0394800753 | 0394900014 | 044100590X | 0441172717
|SQL et PostreSQL53SQL et PostreSQL54Syntaxe de SELECT : Jointures
externes" FROM source1 [NATURAL] LEFT | RIGHT | FULL [OUTER] JOIN
source2 ON ( condition, [, ] ) USING ( colonne, [, ] ) $ Une
jointure externe peut conserver les lignes pour lesquelles il na
pas t trouv de correspondance entre les ensembles joints : les
colonnes manquantes sont alors remplies avec la valeur NULL $ LEFT,
RIGHT ou FULL prcise quau moins une instance de chaque ligne de
lensemble situ gauche (ou droite, ou des 2 cots) du mot-cl JOIN
sera prsenteSQL et PostreSQLSyntaxe de SELECTSELECT [ ALL |
DISTINCT [ ON ( expression [, ...] ) ] ] * | cible [ AS nom ] [,
...] [ FROM source [, ...] ] [ [ NATURAL ] type_jointure [ ON
condition ] | USING ( liste_colonnes ) ] ] [, ] [ WHERE condition ]
[ GROUP BY expression [, ...] ] [ HAVING condition [, ...] ] [ {
UNION | INTERSECT | EXCEPT } [ ALL ] sous-requete ] [ ORDER BY
expression [ ASC | DESC | USING operateur ] [, ...] ] [ LIMIT {
nombre | ALL } ] [ OFFSET debut ] [ FOR UPDATE [ OF nom_table [,
...] ] ]55SQL et PostreSQL56Syntaxe de SELECT : dtails (suite)"
{UNION | INTERSECT | EXCEPT} [ALL] sousrequete : effectue lune des
trois oprations ensemblistes entre le rsultat de linstruction
SELECT et celui dune autre requte. Les lignes dupliques sont
supprimes sauf si le mot-cl ALL est utilisSyntaxe de SELECTSELECT [
ALL | DISTINCT [ ON ( expression [, ...] ) ] ] * | cible [ AS nom ]
[, ...] [ FROM source [, ...] ] [ [ NATURAL ] type_jointure [ ON
condition ] | USING ( liste_colonnes ) ] ] [, ] [ WHERE condition ]
[ GROUP BY expression [, ...] ] [ HAVING condition [, ...] ] [ {
UNION | INTERSECT | EXCEPT } [ ALL ] sous-requete ] [ ORDER BY
expression [ ASC | DESC | USING operateur ] [, ...] ] [ LIMIT {
nombre | ALL } ] [ OFFSET debut ] [ FOR UPDATE [ OF nom_table [,
...] ] ]SQL et PostreSQL57SQL et PostreSQL58Syntaxe de SELECT :
dtails (suite)" ORDER BY expression : Trie le rsultat de
linstruction SELECT selon expression" ASC : croissant " DESC :
dcroissant " USING < (ou USING >)Syntaxe de SELECTSELECT [
ALL | DISTINCT [ ON ( expression [, ...] ) ] ] * | cible [ AS nom ]
[, ...] [ FROM source [, ...] ] [ [ NATURAL ] type_jointure [ ON
condition ] | USING ( liste_colonnes ) ] ] [, ] [ WHERE condition ]
[ GROUP BY expression [, ...] ] [ HAVING condition [, ...] ] [ {
UNION | INTERSECT | EXCEPT } [ ALL ] sous-requete ] [ ORDER BY
expression [ ASC | DESC | USING operateur ] [, ...] ] [ LIMIT {
nombre | ALL } ] [ OFFSET debut ] [ FOR UPDATE [ OF nom_table [,
...] ] ]" LIMIT { nombre | ALL } : limite le nombre de lignes
renvoyes nombre " OFFSET debut : indique partir de quel numro de
ligne commence laffichage du rsultatSQL et PostreSQL59SQL et
PostreSQL60Syntaxe de SELECT : dtails (suite)! FOR UPDATE [ OF
nom_table [, ] ] : effectue un verrouillage exclusif sur les lignes
renvoyes jusqu ce que la transaction soit valide. Pendant quelles
sont verrouilles, les lignes ne peuvent pas tre modifies par
dautres transactionsCration de tables partir dautres tablesSELECT
cibles INTO [ TABLE ] nouvelle_table FROM table_existante; ! La
table indique dans la clause INTO ne doitpas dj exister ou la
requete produira une erreur et les valeurs ne seront pas insresSQL
et PostreSQLSQL et PostreSQL6162Modification de lignes avec
UPDATEUPDATE [ ONLY ] nom_table SET colonne = expression [, ...] [
FROM source ] [ WHERE condition ] ! La clause SET peut tre suivie
de plusieurs expressions de type colonne=expression ! FROM :
extension non standard de PostgreSQL permettant dutiliser des
colonnes dautres tables pour pouvoir mettre jour la table nom_table
! WHERE : permet de dterminer les lignes concernes par la mise
jourSQL et PostreSQLExemple de modificationUPDATE stock SET
prix_vente=prix_vente+0.1*prix_vente WHERE stock= ALL ( SELECT
sum(stock) FROM auteurs, livres, editions NATURAL JOIN stock WHERE
auteurs.id=livres.id_auteur AND livres.id=editions.id_livre GROUP
BY auteurs.id, nom );SQL et PostreSQL79SQL et PostreSQL80TP1.
Question 11Donnez le nombre de titres diffrents dits par chaque
diteur, class par ordre dcroissant du nombre de titres diffrents
par diteur.Rappel table editeursTable "public.editeurs" Column |
Type | Modifiers ---------+---------+----------id | integer | not
null nom | text | adresse | text | Indexes: editeurs_pkey primary
key btree (id), unique_editeur_idx unique btree (nom)SQL et
PostreSQL81SQL et PostreSQL82Solution! 1re tape : cration dune vue
CREATE VIEW titre_par_editeur AS SELECT id_editeur, id_livre FROM
editions GROUP BY id_editeur, id_livre;Contenu de la vueid_editeur
| id_livre ------------+---------59 | 1590 59 | 1608 62 | 2038 65 |
1501 75 | 7808 91 | 190 99 | 4513 101 | 4267 102 | 1234 113 | 41473
150 | 25908 163 | 156 171 | 156 (13 rows)83SQL et PostreSQLSQL et
PostreSQL84Solution (suite et fin)! 2me tape : requte utilisant la
vue cre SELECT e.nom, count(*) Nbre_titres_diffrents FROM editeurs
e, titre_par_editeur WHERE e.id=titre_par_editeur.id_editeur GROUP
BY e.id, e.nom ORDER BY Nbre_titres_diffrents DESC;Cration de
tablesSQL et PostreSQL85SQL et PostgreSQL86CREATE TABLE :
SyntaxeCREATE [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( {
column_name data_type [ DEFAULT default_expr ] [ column_constraint
[, ... ] ] | table_constraint } [, ... ] )CREATE TABLE :
ExempleExemple :CREATE TABLE avion ( id_avion integer NOT NULL,
id_type integer NOT NULL, ville_localisation integer NOT NULL,
Constraint avion_pkey Primary Key (id_avion) );SQL et
PostgreSQL87SQL et PostgreSQL88Contrainte de colonne :
syntaxeContrainte de table : syntaxe[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) | PRIMARY KEY ( column_name [,
... ] ) | CHECK ( expression ) | FOREIGN KEY ( column_name [, ... ]
) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ ON DELETE action
] [ ON UPDATE action ] }[ CONSTRAINT constraint_name ] { NOT NULL |
NULL | UNIQUE | PRIMARY KEY | CHECK (expression) | REFERENCES
reftable [ ( refcolumn ) ] [ ON DELETE action ] [ ON UPDATE action
] }SQL et PostgreSQL89SQL et PostgreSQL90Modification de la
structure dune table! Il est possible de modifier la structure dune
table aprs sa cration, mme si des donnes sont dj intgres ! Les
modifications peuvent concerner nimporte quel objet de la table :
colonnes, contraintes, propritaire, ALTER TABLE : SyntaxesALTER
TABLE [ ONLY ] table [ * ] ADD [ COLUMN ] column type [
column_constraint [ ... ] ] ALTER TABLE [ ONLY ] table [ * ] DROP [
COLUMN ] column [ RESTRICT | CASCADE ]SQL et PostgreSQL91SQL et
PostgreSQL92ALTER TABLE : Syntaxes (2)ALTER TABLE [ ONLY ] table [
* ] ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT
}ALTER TABLE : Syntaxes (3)ALTER TABLE [ ONLY ] table [ * ] RENAME
[ COLUMN ] column TO new_column ALTER TABLE table RENAME TO
new_tableALTER TABLE [ ONLY ] table [ * ] ALTER [ COLUMN ] column {
SET | DROP } NOT NULLSQL et PostgreSQL93SQL et PostgreSQL94ALTER
TABLE : Syntaxes (4)ALTER TABLE [ ONLY ] table [ * ] ADD
table_constraint ALTER TABLE [ ONLY ] table [ * ] DROP CONSTRAINT
constraint_name [ RESTRICT | CASCADE ]ALTER TABLE : Syntaxes
(5)ALTER TABLE table OWNER TO new_ownerSQL et PostgreSQL95SQL et
PostgreSQL96Suppression de tableDROP TABLE name [, ...] [ CASCADE |
RESTRICT ]Cration de fonctions! PostgreSQL offre la possibilit
dtendre ses capacits en permettant la cration de nouvelles
fonctions ! Ces fonctions peuvent simplifier llaboration des
requtes SQL ! Chaque fonction devient lie une base dtermine (comme
tous les autres objets dune base de donnes : tables, vues, )SQL et
PostgreSQL97SQL et PostgreSQL98Cration de fonctions : SyntaxeCREATE
[ OR REPLACE ] FUNCTION nom ( [ argtype [, ...] ] ) RETURNS rettype
AS definition LANGUAGE nom_langage [ WITH ( attribut [, ...] )
]Cration de fonctions : Syntaxe (2)CREATE FUNCTION nom ( [ argtype
[, ...] ] ) ! nom est le nom de la fonction ! argtype, sont les
types des paramtres attendus lors de lappel de la fonction ! Il
peut ne pas y avoir de paramtre, mais les parenthses sont
obligatoires pour lappel dune fonction99SQL et PostgreSQLSQL et
PostgreSQL100Cration de fonctions : Syntaxe (3)RETURNS rettype !
Indique le type de donnes simple renvoy par la fonction AS
definition ! Dfinit le code de la fonction elle-mme. Pour les
langage procduraux de type PL/pgSQL, il sagit du code de la
fonction ! Pour les fonctions crites en C et compiles, il sagit du
chemin absolu menant au fichier contenant le code objet sur le
systmeSQL et PostgreSQLCration de fonctions : Syntaxe (4)[ WITH (
attribut [, ...] ) ] ! attribut : iscachable. Possibilit de
pr-valuer un appel de fonction si celle-ci a dj t value avec les
mmes paramtres : optimisation ! attribut : isstrict. Force la
fonction toujours renvoyer NULL si un de ses paramtres vaut NULL.
La fonction nest mme pas excute101SQL et PostgreSQL102Cration de
fonctions : Surcharge! Il est possible de surcharger une fonction
:" plusieurs fonctions peuvent porter le mme nom " leurs listes de
paramtres doivent tre diffrentes les unes des autresExemple de
fonctionCREATE FUNCTION isbn_titre (text) RETURNS text AS SELECT
titre FROM livres JOIN edition AS e (isbn, id) USING (id) WHERE
isbn=$1 LANGUAGE SQL; ! Remarque : une fonction SQL utilise des
paramtres positionnels pour identifier les paramtres passs lappel
de la fonction.SQL et PostgreSQLSQL et PostgreSQL103104Exemple
dutilisation dune fonctionCrations de fonctions C! Il est possible
de charger dynamiquement du code C compil ! Seuls les
administrateurs peuvent utiliss la commande CREATE FUNCTION pour
tablir un lien vers une fonction C : ceci pour des raisons de
scurit ! Avec gcc, il faut utiliser loption -shared lors de la
compilation du code CSELECT isbn_titre(0929605942); isbn_titre
-----------------------The Tell-Tale HeartSQL et PostgreSQL105SQL
et PostgreSQL106Exemple de cration dune fonction Cint est_nul(int
a) { if (a==0) return 1; return 0; } gcc -shared est_nul.c -o
est_nul.soExemple de cration dune fonction C (suite)CREATE FUNCTION
est_nul(int4) RETURNS boolean AS
/home/esteve/exemple_SQL/est_nul.so LANGUAGE C;SQL et
PostgreSQL107SQL et PostgreSQL108Fonctions prdfinies : fonctions
mathmatiques (1)abs(x) acos(x) asin(x) atan(x) atan2(x,y) cbrt(x)
ceil(x) cos(x) cot(x) valeur absolue arc cosinus arc sinus arc
tangente arc tangente du quotient x/y racine cubique arrondi au
suprieur cosinus cotangenteSQL et PostgreSQLFonctions prdfinies :
fonctions mathmatiques (2)degrees(r) exp(x) floor(x) ln(x) log(b,x)
log(x) mod(x,y) pi() pow(x,x)109valeur en degr de r en radians e
exposant x arrondi infrieur log nprien log de x en base b log 10 x
modulo y pi x puissance ySQL et PostgreSQL110Fonctions prdfinies :
fonctions mathmatiques (3)radians(d) random() round(x) round(x,s)
sin(x) sqrt(x) tan(x) trunc(x) trunc(x,s) valeur en radians de d en
degrs tirage alatoire entre 0.0 et 1.0 arrondi lentier le plus
proche arrondi s dcimales sinus racine carre tangente renvoie x
sans sa partie dcimale idem, en gardant s dcimalesSQL et
PostgreSQLFonctions prdfinies : fonctions chanes de caractres
(1)ascii(s) btrim(s[,t]) char_length(s) length(s) chr(n) s like(f)
s ilike(f) initcap(s) code ascii du premier caractre de s renvoie s
sans les lettres de t qui se trouvent en dbut ou fin de s renvoient
la longueur de s renvoie le caractre de code ASCII n renvoient true
si le motif f correspond s (ilike ne tient pas compte de la casse)
renvoie s, les initiales de chaque mot tant mises en majusculeSQL
et PostgreSQL111112Fonctions prdfinies : fonctions chanes de
caractres (2)lower(s) upper(s) ltrim(s[,t]) rtrim(s[,t])renvoie s
avec tous les caractres de s en minuscule renvoie s avec tous les
caractres de s en majuscule comme btrim, mais ne concernent que les
caractres de dbut (ltrim) ou de fin (rtrim) comptant partir de
1)Triggers! Une action donne doit souvent tre prcde ou suivie
dvnements SQL connus lavance :" vrification de la cohrence des
donnes insrer " formatage des donnes " mise jour de tables aprs la
modification ou la suppression de lignes dune table
particulireposition (b IN s) renvoient la position de la sous-chane
b dans s (en substr(s, n [,l]) Renvoie une sous-chane de s
commenant au n-imecaractre de s et de longueur maximale lSQL et
PostgreSQL113SQL et PostgreSQL114Triggers (suite)! Pour faciliter
la gestion dvnements SQL devant intervenir lors de la mise en uvre
dactions bien dfinies, PostgreSQL offre lutilisation de triggers
(dclencheurs) ! Les triggers affectent les vnements SQL suivant :"
INSERT " UPDATE " DELETESQL et PostgreSQLTriggers (suite 2)! Pour
crer un trigger, il faut dabord cr la fonction quil utilisera !
Attention ! Les triggers sont une extension de SQL offerte par
PostgreSQL : pas de portabilit (Oracle propose galement des
triggers mais ils sont grs diffremment)115SQL et
PostgreSQL116Cration de trigger : syntaxeCREATE TRIGGER nom {
BEFORE | AFTER } { vnement [OR ...] } ON table FOR EACH { ROW |
STATEMENT } EXECUTE PROCEDURE func ( arguments )Transactions! La
gestion de transactions permet dassurer lintgrit des donnes ! Une
transaction peut tre considre comme un groupe doprations qui
forment une unit indivisible ! Les oprations dune transaction sont
donc toutes effectues, ou aucune nest effectueSQL et
PostgreSQL117SQL et PostgreSQL118Transactions (suite)! PostgreSQL
offre un systme de gestion des transactions efficace : le MVCC
(Multi-Version Concurrency Control) ! Par dfaut chaque opration est
valide et est ellemme une transaction ! Pour spcifier quun groupe
doprations doit tre considr comme une transaction, il faut dclarer
explicitement lentre dans un bloc transactionnelSQL et
PostgreSQLBloc transactionnel! Un bloc transactionnel est un
ensemble doprations indivisibles : ce type de bloc dtermine le
contenu dune transaction ! Tant que la fin dun bloc transactionnel
nest pas atteinte, les ventuelles modifications de la base de
donnes ne sont pas valides au niveau des donnes partages par tous
les utilisateursSQL et PostgreSQL119120Bloc transactionnel (suite)!
Le dbut dun bloc transactionnel est dclare de la manire suivante :
BEGIN [WORK | TRANSACTION] ! WORK et TRANSACTION ne servent rien
(bruit)ROLLBACK et COMMIT! A la fin dun bloc transactionnel, les
oprations sont valides, ou annules. ! Validation :COMMIT [WORK |
TRANSACTION]! Annulation :ROLLBACK [WORK | TRANSACTION]SQL et
PostgreSQL121SQL et PostgreSQL122ROLLBACK et COMMIT : ExempleBEGIN
; UPDATE licence_pilote SET licence=BO WHERE id_pilote=4; INSERT
INTO VILLE (id_ville, ville) VALUES (9, LE MANS); COMMIT;Gestion
des privilges : GRANT (don de privilges)GRANT privilege [,] ON
objet [,] TO { username | GROUP groupname | PUBLIC } [, ...] !
privilges :" SELECT (lecture) " INSERT (insertion de lignes) "
UPDATE (modification de colonnes) " DELETE (suppression de lignes)
" ALLSQL et PostgreSQL123SQL et PostgreSQL124Gestion des privilges
: GRANTGRANT privilge [,] ON objet [,] TO { username | GROUP
groupname | PUBLIC } [, ...] ! objets :" table : ON [TABLE]
nom_table " fonctions : ON FUNCTION nom_fonction " bases de donnes
: ON DATABASE nom_bd " langage : ON LANGUAGE nom_langage " schma :
ON SCHEMA nom_schemaSQL et PostgreSQLGestion des privilges :
REVOKEREVOKE privilge [,] ON objet [,] FROM { username | GROUP
groupname | PUBLIC } [, ...] ! Au contraire de GRANT, REVOKE
supprime des privilges un utilisateur ou un ensemble
dutilisateurs125SQL et PostgreSQL126
