Éric Jacoboni
21 octobre 1999
Comme le mois dernier, nous commencerons par présenter les termes
employés par SQL.
Avec PostgreSQL, une base de données (et toutes les tables qu'elles
contient) appartient à l'utilisateur qui l'a créée. Celui-ci peut
faire partager certains droits sur certaines tables de cette base.
Attention, dans sa version actuelle, PostgreSQL ne permet pas de fixer
des privilèges au niveau d'une base entière (c'est une limitation par
rapport à la norme).
SQL définit, en fait, deux « langages » :
Le terme « langage » est un peu abusif : il s'agit plutôt d'un
ensemble de verbes, peu nombreux, qui permettent de réaliser
des transactions en construisant des requêtes.
Le langage de définition des données (LDD) est l'ensemble des verbes SQL permettant de définir les tables et les éléments qu'elles contiennent. Il permet également d'insérer, modifier ou détruire des éléments dans les tables existantes, et de fixer les privilèges d'accès à celles-ci.
Pour tous les exemples de cet article, nous supposerons que l'utilisateur jaco a été créé par le DBA, et autorisé à créer des bases :
# su - pgsql $ cd bin $ ./createuser jaco Enter user's postgres ID or RETURN to use unix user ID: 1000 -> Is user "jaco" allowed to create databases (y/n) y Is user "jaco" a superuser? (y/n) n createuser: jaco was successfully added $ ^D #
Si cela ne se passe pas aussi bien sur votre système, consultez la
page du manuel de createuser (elle vous apprendra notamment
que le serveur postmaster doit déjà être lancé).
Toutes les tables que nous créerons, et toutes nos requêtes, porteront sur la base de données biblio, créée par jaco :
% who am i jaco ttyp2 Oct 20 20:33 (:0) % createdb biblio % psql biblio Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL [PostgreSQL 6.5.2 on i386-unknown-freebsd3.3, compiled by cc ] type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: biblio biblio=> \dt Database = biblio +------------------+----------------------------------+----------+ | Owner | Relation | Type | +------------------+----------------------------------+----------+ | pgsql | pga_forms | table | | pgsql | pga_forms | table | | pgsql | pga_queries | table | | pgsql | pga_queries | table | | pgsql | pga_reports | table | | pgsql | pga_reports | table | | pgsql | pga_schema | table | | pgsql | pga_schema | table | | pgsql | pga_scripts | table | | pgsql | pga_scripts | table | +------------------+----------------------------------+----------+ biblio=> \q %
La commande createdb biblio sans plus de précisions, crée la
base. On vérifie son existence en s'y connectant via psql
biblio. Initialement, elle est vide : la commande \dt montre
qu'elle ne contient que les tables systèmes, nécessaires à sa
gestion. Ces tables, évidemment, sont la propriété du DBA.
Si ces commandes ne fonctionnent pas, vérifiez que votre variable
PATH contient bien le chemin d'accès aux exécutables de
PostgreSQL : sur mon système, il s'agit de ~pgsql/bin.
On rappelle qu'une table est l'implantation d'une relation : il faut
donc indiquer, pour chacune de ses colonnes :
Au moment de la création, on précisera généralement la ou les colonnes
composant la clé primaire et la ou les colonnes formant les
clés étrangères. On indiquera également si ces colonnes
acceptent, ou non, de ne rien contenir.
Après sa création, la table est vide.
Les types de colonnes possibles sont nombreux sous PostgreSQL et nous
n'emploierons ici que les principaux. Les manuels de l'utilisateur et
du programmeurs, livrés avec la distribution, vous les présentent en
détail.
La phrase SQL permettant de créer une table est CREATE TABLE, sa syntaxe générale (et simplifiée) est la suivante :
CREATE TABLE nom_table (col1 type1 [NOT NULL],
col2 type2 [NOT NULL],
...
coln typen [NOT NULL]);
On remarquera que toute requête SQL peut être découpée en plusieurs lignes et qu'elle se termine par un point-virgule.
Nous n'avons pas présenté ici la création des clés ; un exemple sera plus explicite :
CREATE TABLE Auteurs (Code CHAR(3) NOT NULL PRIMARY KEY,
Nom VARCHAR(20) NOT NULL,
Prenom VARCHAR(20),
Nationalite VARCHAR(20));
La table Auteurs est destinées à contenir des lignes comme :
127 Céline Louis-Ferdinand Fr ... 178 Shakespeare William Uk
La première colonne forme la clé : chacune des lignes doit donc y
posséder une valeur différente, ce qui est indiqué par PRIMARY
KEY. PostgreSQL s'assurera alors, lorsque vous insérerez un nouvel
élément, qu'il a bien un code différent des autres. On admet que
certaines colonnes soient vides (le prénom, par exemple, pour gérer
les pseudonymes), on force certaines autres à contenir une valeur (la
clé, évidemment).
Nous n'avons ici utilisé que les types CHAR (chaîne de longueur fixe) et VARCHAR (chaîne de longueur variable). Le type caractère est assimilé à CHAR(1) et peut être noté CHAR.
biblio=> CREATE TABLE Auteurs (Code CHAR(3) NOT NULL PRIMARY KEY, biblio-> Nom VARCHAR(20) NOT NULL, biblio-> Prenom VARCHAR(20), biblio-> Nationalite VARCHAR(20)); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'auteurs_pkey' for table 'auteurs' CREATE biblio=> \dt Database = biblio +------------------+----------------------------------+----------+ | Owner | Relation | Type | +------------------+----------------------------------+----------+ | jaco | auteurs | table | | pgsql | pga_forms | table | | pgsql | pga_forms | table | | pgsql | pga_queries | table | | pgsql | pga_queries | table | | pgsql | pga_reports | table | | pgsql | pga_reports | table | | pgsql | pga_schema | table | | pgsql | pga_schema | table | | pgsql | pga_scripts | table | | pgsql | pga_scripts | table | +------------------+----------------------------------+----------+ biblio=>
On notera que la table auteurs (PostgreSQL convertit tout en minuscules) a été créée et qu'elle appartient à son créateur, jaco.
On peut consulter sa structure à l'aide de la commande \d auteurs :
biblio=> \d auteurs Table = auteurs +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | code | char() not null | 3 | | nom | varchar() not null | 20 | | prenom | varchar() | 20 | | nationalite | varchar() | 20 | +----------------------------------+----------------------------------+-------+ Index: auteurs_pkey
Là encore, on constate que les noms des champs (c'est ainsi que
SQL désigne les colonnes) ont été convertis en minuscules et les
accents supprimés.
Il ne peut exister qu'un seule occurence de PRIMARY KEY par
table : que se passe-t'il alors si la clé est composée de plusieurs
champs ?
Il faut, en ce cas, déférer la déclaration de celle-ci. Soit la relation :
MA_RELATION (A, B, C, D)
Elle sera créé par la requête suivante :
CREATE TABLE MA_RELATION (A ... NOT NULL,
B ... NOT NULL,
C ...,
D ...,
PRIMARY KEY(A, B));
Nous évoquerons la création des clés étrangères lorsque le besoin s'en
fera sentir (mais, évidemment, il faut les connaître au moment de la
création de la table).
Les autres types principaux sont :
La commande \dT de psql vous énumérera tous les types
disponibles et vous remarquerez qu'ils sont nombreux ! Je vous
conseille toutefois de vous en tenir à ceux communs à tous les
SGBDR... Si vous utilisez la dernière version de pgaccess
(version 0.98), son menu « Help » vous permettra de comparer les types
normalisés avec ceux de PostgreSQL. Les manuels de la distribution
PostgreSQL font de même.
Bien sûr, on peut utiliser différents outils pour faciliter la
création des tables, pgaccess est l'un deux, mais nécessite
un environnement graphique et l'installation de Tcl/Tk. psql
permet également de cataloguer des requêtes SQL : on crée un
fichier texte contenant une suite de requêtes SQL, et on demande à
psql de le lire et d'exécuter celles-ci. Si cela vous
intéresse, essayez la commande \i nomfic :
% cat requetes.sql create table table1 (a char not null, b char not null); create table table2 (c numeric(3,2), d bool); create table table3 (e char(3), f float4); % psql biblio (...) biblio=> \i requetes.sql create table table1 (a char not null, b char not null); CREATE create table table2 (c numeric(3,2), d bool); CREATE create table table3 (e char(3), f float4); CREATE EOF biblio=>
La table créée, on peut (et ,généralement, on le fait...), lui insérer des éléments. Cela est réalisé grâce à la phrase INSERT INTO nom_table VALUES (valeurs). valeurs est une liste contenant autant d'éléments qu'il y a de colonnes dans nom_table. Si un champ peut être vide, et que l'on désire ne rien y mettre, son emplacement doit être quand même repéré par le mot clé null.
biblio=> insert into auteurs values ('127','Celine','Louis-Ferdinand','Fr');
INSERT 21153 1
biblio=> insert into auteurs values ('128','Moebius',null,'Fr');
INSERT 21216 1
biblio=> insert into auteurs values ('128','Wallet','Martine','Fr');
ERROR: Cannot insert a duplicate key into a unique index
biblio=> insert into auteurs values ('178','Shakespeare','William','Uk');
INSERT 21218 1
biblio=> select * from auteurs;
code|nom |prenom |nationalite
----+-----------+---------------+-----------
127|Celine |Louis-Ferdinand|Fr
128|Moebius | |Fr
178|Shakespeare|William |Uk
(3 rows)
biblio=>
Plusieurs points méritent d'être notés :
\i.
La forme INSERT que nous avons utilisée ici est la plus fréquente : on
peut la compliquer (voir sa syntaxe complète avec la commande \h insert de psql.
biblio=> \h insert
Command: insert
Description: insert tuples
Syntax:
INSERT INTO class_name [(attr1, ...attrN)]
VALUES (expr1,..exprN) |
SELECT [DISTINCT [ON attrN]]
expr1, ...exprN
[FROM from_clause]
[WHERE qual]
[GROUP BY group_list]
[HAVING having_clause]
[ { UNION [ALL] | INTERSECT | EXCEPT } SELECT ...];
biblio=>
La phrase DROP TABLE nom_table supprime la table spécifiée et tout ce qu'elle contient ! Elle doit donc être utilisée avec précaution car elle ne prévient pas...
biblio=> drop table table1; DROP biblio=> drop table table2; DROP biblio=> drop table table3; DROP
La phrase ALTER TABLE dispose de trois formes permettant, respectivement, d'ajouter des champs, de renommer des champs ou la table elle-même. On ne peut pas modifier le type des champs existants, ni en détruire, cela afin de préserver l'intégrité des données déjà présentes.
biblio=> ALTER TABLE auteurs ADD COLUMN sexe CHAR;
ADD
biblio=> ALTER TABLE auteurs RENAME COLUMN code TO code_auteur;
RENAME
biblio=> ALTER TABLE auteurs RENAME TO ecrivains;
RENAME
biblio=> \d auteurs
Couldn't find table auteurs!
biblio=> \d ecrivains
Table = ecrivains
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| code_auteur | char() not null | 3 |
| nom | varchar() not null | 20 |
| prenom | varchar() | 20 |
| nationalite | varchar() | 20 |
| sexe | char() | 1 |
+----------------------------------+----------------------------------+-------+
Index: auteurs_pkey
biblio=> select * from ecrivains;
code_auteur|nom |prenom |nationalite|sexe
-----------+-----------+---------------+-----------+----
127|Celine |Louis-Ferdinand|Fr |
128|Moebius | |Fr |
178|Shakespeare|William |Uk |
(3 rows)
biblio=>
On remarque que le nouveau champ ne contient encore rien : il faudra le remplir avec le verbe UPDATE.
Pour modifier les tuples d'une tables, on utilise la phrase UPDATE nom_table :
biblio=> UPDATE ecrivains SET sexe='m';
UPDATE 3
biblio=> select * from ecrivains;
code_auteur|nom |prenom |nationalite|sexe
-----------+-----------+---------------+-----------+----
127|Celine |Louis-Ferdinand|Fr |m
128|Moebius | |Fr |m
178|Shakespeare|William |Uk |m
(3 rows)
biblio=>
Cette modification a été simplifiée du fait que l'on voulait altérer tous les tuples. Si l'on avait voulu ne modifier que certains tuples, on aurait utilisé la clause WHERE. Admettons que nous ayons eu un tuple décrivant Marguerite Yourcenar, la commande précédente aurait donc donné :
biblio=> select * from ecrivains;
code_auteur|nom |prenom |nationalite|sexe
-----------+-----------+---------------+-----------+----
127|Celine |Louis-Ferdinand|Fr |m
128|Moebius | |Fr |m
178|Shakespeare|William |Uk |m
180|Yourcenar |Marguerite |Fr |m
(4 rows)
biblio=>
Pour modifier ce tuple, il faudrait maintenant faire :
biblio=> UPDATE ecrivains SET sexe='f' WHERE code_auteur='180'; UPDATE 1
La phrase DELETE FROM nom_table permet de supprimer des tuples d'une table. Cette suppression peut être effectuée, là aussi, en fonction de certains critères :
biblio=> DELETE FROM ecrivains; DELETE 3
Tous les tuples ont été supprimés ! La table existe encore, mais elle
est vide...
Si l'on avait voulu supprimer uniquement les auteurs anglais, on aurait écrit :
biblio=> DELETE FROM ecrivains WHERE nationalite='Uk'; DELETE 1
Le mois prochain, nous étudierons le langage de manipulation des données qui, à partir de tables créés et contenant leurs éléments, nous permettra d'obtenir des réponses à nos requêtes. En attendant, je vous invite à créer et remplir les tables correspondant aux relations suivantes :
livres (code_livre, titre, code_editeur, code_auteur, isbn,
date_parution, code_genre, langue, nb_pages, code_prix)
editeurs (code_editeur, nom_editeur, adresse, ville,
code_postal, pays)
genres (code_genre, genre)
prix (code_prix, prix_min, prix_max)
Vous pouvez, bien sûr, personnaliser tout cela...
Cette liste est loin d'être exhaustive, mais ce sont les ouvrages que
j'utilise... Vous trouverez dans ceux-ci d'autres références,
françaises et internationales.
Une recherche utilisant le mot clé « SQL » sur yahoo! devrait également vous proposer un certain nombre de documents, voire de cours complets, disponibles en téléchargement.
This document was generated using the LaTeX2HTML translator Version 98.1p1 release (March 2nd, 1998)
Copyright © 1993, 1994, 1995, 1996, 1997, Nikos Drakos, Computer Based Learning Unit, University of Leeds.
The command line arguments were:
latex2html -split 0 sql2.tex.
The translation was initiated by Eric Jacoboni on 1999-10-21