Ce cours est constitué de trois
parties. Dans la première, nous expliquons quelques généralités
sur SQL, en utilisant une base de données du domaine public appelée
postgreSQL. Dans la seconde, nous étudierons plus en détail
les commandes SQL. Dans la dernière partie, nous verrons certaines
options avancées des commandes SQL, des fonctions spécifiques
à PostgreSQL qui pourraient servir dans vos projets, et nous verrons
finalement un petit programme en C rassemblant les connaissances aquises.
Introduction
Pour cette introduction rapide, je ferai uniquement référence
aux bases de données (BD). Il existe d'autres modèles d'organisation
de données, mais les présenter serait au delà de ce
cours.
Jusqu'à récemment, l'accès aux données était
réalisé par des entités liées entre elles par
des relations définies dans la base de données. Ce
type d'accès a des avantages, principalement la rapidité,
mais aussi un gros inconvénient: on ne peut accéder aux données
qu'à travers la relation existante, par exemple:
pays -> region -> localite
mais jamais:
pays -> localite
où "->" représente la relation.
Si l'on souhaite établir cette nouvelle relation, il faut redéfinir
la base de données et la recompiler....
En fait, dans une base de données hiérarchique, les relations
entre les différentes entités sont statiques, et ne peuvent
être altèrées que par modification et recompilation
de la base de données.
L'idée fondamentale derrière les bases de données
relationnelles est précisément de lier les données
lors de l'apparition d'une requête, sans besoin de liens statiques,
mais au contraire à l'aide d'identificateurs permettant de lier
un enregistrement à l'autre.
Ce que je viens d'écrire nécessitera probablement une
dose d'Aspirine :)
Les gestionnaires de bases de données relationnelles ne nécessitent
pas de liens statiques pour suivre la hiérarchie des entités,
au contraire ils utilisent un code unique qui identifie les entités
tout en établissant une relation temporaire lors des requêtes.
L'identification n'est qu'un code. Par exemple, mon numéro de
téléphone n'est pas :
1234567
mais:
34 6 1234567.
Il est clair que mon numéro de téléphone est identifié
par le code du pays (34), le code région (6), le code localité
(12) et le code de l'appareil lui -même (34567).
-
Dans l'ensemble des pays le code 34 (Espagne) est unique.
-
Dans l'ensemble des régions, le code 34-6 (Espagne/Valence) est
unique.
-
Dans l'ensemble des numéros téléphoniques, le code
34-6-1234567 (Espagne/Valence/mon numéro de téléphon)
est unique.
Laissez-moi construire les fondements du premier exemple.
Tous les téléphones ont un code,
appartiennent à une région et un pays.
Toutes les régions ont un code, et
appartiennent à un pays.
Tous les pays ont un code.
Pour trouver tous les numéros d'une région, nous établissons
une relation entre le numéro et la région par les codes de
pays et de région; pour trouver tous les numéros d'un pays,
on établit le lien entre les numéros et les pays par le code
pays. Ces relations sont temporaires, elles n'existent que pendant la durée
de la requête.
C'est un peu aride et difficile à comprendre mais je pense que
les premiers exemples ont clarifié les concepts de code et d'appartenance.
Lorsque j'envoie la première requête au gestionnaire de
BD, il me renvoie toutes les données liées. Mais quelles
données reçoit-on exactement? La réunion des données
concernant les numéros de téléphone et les pays, c'est
à dire que pour chaque numéro, on reçoit aussi les
informations concernant le pays.
Lors de la première requête, une nouvelle entité
sans nom est soudainement créée, qui contient une copie des
pays et numéros. Cette nouvelle entité, à nouveau,
disparaîtra avec ma requête.
Avant, nous appelions un ensemble de données un "fichier". Il
était composé "d'enregistrements", et chaque "enregistrement"
de "champs". Maintenant, dans une base de données relationnelle,
le "fichier" devient une "table", la "table" contient des "lignes", et
chaque "ligne" a une ou plusieurs "colonnes". Juste quelques petits changements
de vocabulaire. ;-)
Il est bon de signaler à ce stade que certains gestionnaires
de BD hiérarchiques utilisent SQL comme langage d'accès,
mais ce n'est qu'anecdotique. SQL est presque exclusivement réservé
aux gestionnaires de BD relationnelles.
Pour illustrer l'utilisation de SQL, nous utiliserons le gestionnaire
PostgreSQL. Bien qu'il ne suive pas rigoureusement les règles de
SQL, il en est suffisamment proche pour nos besoins, et c'est aussi un
très bon gestionnaire pour des tâches plus importantes.
Le but de cet article étant SQL, je n'expliquerai que brièvement
l'installation de PostgreSQL. Téléchargez d'abord les sources
et les mises à jour disponibles à l'adresse www.postgresql.org.
Décompressez/ Extrayez l'archive (tar zxvf), puis rendez vous au
répertoire correspondant,
cd postgresql-6.3
cd src
./configure --prefix=/the/desired/path
make all >& make.log &
tail -f make.log
export PATH=$PATH:/the/desired/path/pgsql/bin
export MANPATH=$MANPATH:/the/desired/path/pgsql/man
export PGLIB=/the/desired/path/pgsql/lib
export PGDATA=/the/desired/path/pgsql/data
initdb
createdb test
psql test
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
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: postgres
test=>
C'est l'invite de PostgreSQL, vous pouvez maintenant commencer à
exécuter des commandes.
mytest=>create table mytest (field1 varchar(10));
CREATE
mytest=>insert into mytest values ('hello');
INSERT number 1
mytest=>commit work;
NOTICE:EndTransactionBlock and not inprogress/abort region
END
mytest=>select * from mytest;
field1
------
hello
(1 row)
mytest=>drop table mytest;
DROP
mytest=>Ctrl-d
Nous sommes déjà sorti de la console SQL.
En cas de problème lors de la compilation ou de l'installation
de Postgres95, reportez vous au fichier INSTALL dans le répertoire
de base de la distribution.
Laissez moi ajouter un commentaire; un serveur de base de données
relationnelle est généralement composé de plusieurs
niveaux:
-
Couche d'accès aux données,
-
Couche de traitement SQL,
-
Couche de traduction SQL (parser),
-
Couche communications.
En tant que client, on se connecte au dernier niveau, on envoie des commande
SQL qui sont passées au traducteur. Il examine les commandes et,
s'il n'y a pas d'erreur, envoie les requêtes à la deuxième
couche. La partie traitement de la requête se passe à ce niveau,
évidemment en collaboration avec la couche d'accès aux données:
les données extraites et les erreurs éventuelles sont transmises
en retour au client, à travers la couche communications. Le niveau
traitement de SQL établit un dialogue avec le client, tout en gérant
le transfert correct des données, et en controlant les transactions
et interruptions.
Premiers pas
Maintenant je vais illustrer par un exemple ce que nous avons vu jusqu'à
présent. Construisons trois tables (ou fichiers):
File: pays.sql
create table pays (cod_pays integer, nom_pays varchar(30));
insert into pays values (1, 'pays 1');
insert into pays values (2, 'pays 2');
insert into pays values (3, 'pays 3');
commit work;
File: region.sql
create table region (cod_region int,
cod_pays int,
nom_region varchar(30));
insert into region values (1, 1, 'region 1, pays 1');
insert into region values (2, 1, 'region 2, pays 1');
insert into region values (1, 2, 'region 1, pays 2');
insert into region values (2, 2, 'region 2, pays 2');
insert into region values (1, 3, 'region 1, pays 3');
insert into region values (2, 3, 'region 2, pays 3');
commit work;
File: localite.sql
create table localite (cod_pays int,
cod_region int,
cod_localite int,
nom_localite varchar(60));
insert into localite values (1, 1, 1, 'localite 1, region 1, pays 1');
insert into localite values (2, 1, 1, 'localite 2, region 1, pays 1');
insert into localite values (3, 1, 1, 'localite 3, region 1, pays 1');
insert into localite values (1, 2, 1, 'localite 1, region 2, pays 1');
insert into localite values (2, 2, 1, 'localite 2, region 2, pays 1');
insert into localite values (3, 2, 1, 'localite 3, region 2, pays 1');
insert into localite values (1, 3, 1, 'localite 1, region 3, pays 1');
insert into localite values (2, 3, 1, 'localite 2, region 3, pays 1');
insert into localite values (3, 3, 1, 'localite 3, region 3, pays 1');
insert into localite values (1, 1, 2, 'localite 1, region 1, pays 2');
insert into localite values (2, 1, 2, 'localite 2, region 1, pays 2');
insert into localite values (3, 1, 2, 'localite 3, region 1, pays 2');
insert into localite values (1, 2, 2, 'localite 1, region 2, pays 2');
insert into localite values (2, 2, 2, 'localite 2, region 2, pays 2');
insert into localite values (3, 2, 2, 'localite 3, region 2, pays 2');
insert into localite values (1, 3, 2, 'localite 1, region 3, pays 2');
insert into localite values (2, 3, 2, 'localite 2, region 3, pays 2');
insert into localite values (3, 3, 2, 'localite 3, region 3, pays 2');
insert into localite values (1, 1, 3, 'localite 1, region 1, pays 3');
insert into localite values (2, 1, 3, 'localite 2, region 1, pays 3');
insert into localite values (3, 1, 3, 'localite 3, region 1, pays 3');
insert into localite values (1, 2, 3, 'localite 1, region 2, pays 3');
insert into localite values (2, 2, 3, 'localite 2, region 2, pays 3');
insert into localite values (3, 2, 3, 'localite 3, region 2, pays 3');
insert into localite values (1, 3, 3, 'localite 1, region 3, pays 3');
insert into localite values (2, 3, 3, 'localite 2, region 3, pays 3');
insert into localite values (3, 3, 3, 'localite 3, region 3, pays 3');
commit work;
Un fichier de commandes SQL peut être exécuté depuis
pgsql de cette façon:
\i file_name
On peut aussi insérer des commandes avec couper/ coller.
Voyons ensuite quelles localités existent:
manu=> select * from localite;
cod_pays|cod_region|cod_localite|nom_localite
-----------+----------+------------+----------------------------
1| 1| 1|localite 1, region 1, pays 1
2| 1| 1|localite 2, region 1, pays 1
3| 1| 1|localite 3, region 1, pays 1
1| 2| 1|localite 1, region 2, pays 1
2| 2| 1|localite 2, region 2, pays 1
3| 2| 1|localite 3, region 2, pays 1
1| 3| 1|localite 1, region 3, pays 1
2| 3| 1|localite 2, region 3, pays 1
3| 3| 1|localite 3, region 3, pays 1
1| 1| 2|localite 1, region 1, pays 2
2| 1| 2|localite 2, region 1, pays 2
3| 1| 2|localite 3, region 1, pays 2
1| 2| 2|localite 1, region 2, pays 2
2| 2| 2|localite 2, region 2, pays 2
3| 2| 2|localite 3, region 2, pays 2
1| 3| 2|localite 1, region 3, pays 2
2| 3| 2|localite 2, region 3, pays 2
3| 3| 2|localite 3, region 3, pays 2
1| 1| 3|localite 1, region 1, pays 3
2| 1| 3|localite 2, region 1, pays 3
3| 1| 3|localite 3, region 1, pays 3
1| 2| 3|localite 1, region 2, pays 3
2| 2| 3|localite 2, region 2, pays 3
3| 2| 3|localite 3, region 2, pays 3
1| 3| 3|localite 1, region 3, pays 3
2| 3| 3|localite 2, region 3, pays 3
3| 3| 3|localite 3, region 3, pays 3
(27 rows)
manu=>
Il ya a 27 lignes et pgsql attend la commande suivante. Essayons:
manu=> select * from pays, region;
cod_pays|nom_pays |cod_region| cod_pays|nom_region
-----------+---------+----------+-----------+------------------
1| pays 1| 1| 1|region 1, pays 1
2| pays 2| 1| 1|region 1, pays 1
3| pays 3| 1| 1|region 1, pays 1
1| pays 1| 2| 1|region 2, pays 1
2| pays 2| 2| 1|region 2, pays 1
3| pays 3| 2| 1|region 2, pays 1
1| pays 1| 1| 2|region 1, pays 2
2| pays 2| 1| 2|region 1, pays 2
3| pays 3| 1| 2|region 1, pays 2
1| pays 1| 2| 2|region 2, pays 2
2| pays 2| 2| 2|region 2, pays 2
3| pays 3| 2| 2|region 2, pays 2
1| pays 1| 1| 3|region 1, pays 3
2| pays 2| 1| 3|region 1, pays 3
3| pays 3| 1| 3|region 1, pays 3
1| pays 1| 2| 3|region 2, pays 3
2| pays 2| 2| 3|region 2, pays 3
3| pays 3| 2| 3|region 2, pays 3
(18 rows)
18 lignes ??? Nous avons inséré 3 pays et 6 régions,
tous identifient un unique pays. Pourquoi obtient-on 18 lignes?
La commande précédente a réalisé la réunion,
entre les tables, pays et nous avons lié la table des pays avec
celle des localités. Puisqu'aucune règle d'exclusion n'a
été précisée pour la réunion, pgsql
renvoie TOUTES les valeurs possibles de pays combinées avec TOUTES
les valeurs possibles de régions, c'est à dire 3 pour les
pays fois 6 pour les régions, soit un total de 18. Ce résultat,
ou plus exactement la requête, est évidemment stupide et inutile,
nous aurions dû faire:
manu=> select * from pays, region
manu-> where pays.cod_pays = region.cod_pays;
cod_pays|nom_pays |cod_region| cod_pays|nom_region
-----------+---------+----------+-----------+------------------
1| pays 1| 1| 1|region 1, pays 1
1| pays 1| 2| 1|region 2, pays 1
2| pays 2| 1| 2|region 1, pays 2
2| pays 2| 2| 2|region 2, pays 2
3| pays 3| 1| 3|region 1, pays 3
3| pays 3| 2| 3|region 2, pays 3
(6 rows)
Eh bien, cela semble plus raisonnable, 6 lignes, correct?
Oui, il y a six régions, chacune dans un pays. Il est normal
d'obtenir le même nombre de lignes que de régions car pays
est une proprièté de région. Nous avons simplement
indiqué la relation entre les deux tables par le code pays. Souvenez-vous
que les pays ont un code les identifiant, et la table des régions
contient le code du pays auquel elles appartiennent.
Pourquoi pays.cod_pays = region.cod_pays ?
Le code du pays dans la table des pays est cod_pays, de même
dans la table des régions, donc:
cod_pays = cod_pays
est illogique, l'interprêteur ne saurait jamais quelle table utiliser,
et renverrait donc une erreur:
select * from pays, region
where cod_pays = cod_pays;
ERROR: Column cod_pays is ambiguous
Ensuite, on peut utiliser des alias pour les noms de colonnes:
manu=> select * from pays a, region b
manu-> where a.cod_pays = b.cod_pays;
cod_pays|nom_pays |cod_region|cod_pays |nom_region
-----------+---------+----------+-----------+------------------
1| pays 1| 1| 1|region 1, pays 1
1| pays 1| 2| 1|region 2, pays 1
2| pays 2| 1| 2|region 1, pays 2
2| pays 2| 2| 2|region 2, pays 2
3| pays 3| 1| 3|region 1, pays 3
3| pays 3| 2| 3|region 2, pays 3
(6 rows)
Que renvoie le gestionnaire SQL? cod_pays, nom_pays,
cod_region, cod_pays et nom_region.
Puisque nous avons demandé "select * from pays, region",
où * est un joker signifiant TOUT, nous obtenons les deux colonnes
des pays, et les trois colonnes des localités. Soyons maintenant
plus restrictifs:
manu=> select a.cod_pays, cod_region, nom_pays, nom_region
manu-> from pays a, region b
manu-> where a.cod_pays = b.cod_pays;
cod_pays|cod_region|nom_pays |nom_region
-----------+----------+---------+------------------
1| 1| pays 1|region 1, pays 1
1| 2| pays 1|region 2, pays 1
2| 1| pays 2|region 1, pays 2
2| 2| pays 2|region 2, pays 2
3| 1| pays 3|region 1, pays 3
3| 2| pays 3|region 2, pays 3
(6 rows)
Dans la dernière commande, nous avons spécifiquement demandé
le code pays, le code région, le nom du pays et le nom de la région.
Observez que certains noms de colonnes sont complètement qualifiés
(a.cod_pays) tandis que d'autres ne le sont pas (nom_region). La raison
est l'unicité ou la multiplicité des noms de colonnes dans
les tables sélectionnées. Les colonnes dont les noms sont
uniques n'ont pas besoin d'être qualifiées.
Compliquons encore l'exemple:
manu=> select a.cod_pays, cod_region, nom_pays, nom_region
manu-> from pays a, region b
manu-> where a.cod_pays = b.cod_pays
manu-> and a.cod_pays = 3;
cod_pays|cod_region|nom_pays |nom_region
-----------+----------+---------+------------------
3| 1| pays 3|region 1, pays 3
3| 2| pays 3|region 2, pays 3
(2 rows)
Nous avons cette fois limité la recherche au code pays 3.
Fonctions
Voici un exemple d'utilisation de la fonction de comptage count():
select count(*) from region;
count
-----
27
(1 row)
Cette fonction renvoie le nombre de lignes dans la table des régions,
ensuite:
manu=> select cod_pays, count(*) from region
manu-> group by cod_pays;
cod_pays|count
-----------+-----
1| 2
2| 2
3| 2
(3 rows)
Cette nouvelle requête renvoie le nombre de lignes avec code pays
IDENTIQUE, c'est la raison pour utilier un regroupement par cod_pays.
Un exemple encore meilleur:
manu=> select nom_pays, count(*) from pays a, region b
manu-> where a.cod_pays = b.cod_pays
manu-> group by name;
nom_pays |count
---------+-----
pays 1| 2
pays 2| 2
pays 3| 2
(3 rows)
Nous obtenons toujours les trois mêmes lignes, mais cette fois l'information
renvoyée est plus lisible.
Eh bien voici pour l'introduction, juste un échauffement :-)
Révision des
concepts
Jusque là, nous avons vu quelques concepts fondamentaux de SQL.
Le plus intéressant est en fait le concept de SQL lui-même:
ne plus travailler directement avec les données, mais avec des entités
données. Les entités données sont un concept abstrait
des bases de données. Pour simplifier, on pourrait dire "NE RENVOYER
QU'UNE PARTIE DE CE QUI EST DISPONIBLE".
Nous avons vu plusieurs commandes:
CREATE TABLE |
Pour créer une table. |
DROP TABLE |
Pour effacer une table. |
SELECT |
Commande de base de SQL, pour créer une vue (table temporaire)
ne contenant que les données extraites. SELECT peut prendre comme
paramètres des fonctions ou des phrases complèxes, ou des
sélections imbriquées (sub_selects:)
select count(*) from region
where cod_pays in (select cod_pays from pays);
count
-----
27
(1 row)
|
BEGIN WORK |
Autre commande fondamentale. Indique au gestionnaire de base d'enregistrer
TOUTES les modifications réalisées après BEGIN WORK.
Pour notre gestionnaire, cela indique le début d'une transaction.
Dans d'autres gestionnaires, le début de la transaction apparaît
à la première modification de la base de données.
Avec PostgreSQL, toutes les modifications ont lieu immédiatement,
si BEGIN WORK n'a pas été spécifié.
REMARQUE: les commandes qui modifient l'architecture de la base de données
exécutent COMMIT WORK, donc si une transaction est ouverte et qu'une
telle commande est exécutée, la transaction est fermée
immédiatement, et il sera impossible de revenir en arrière
avec ROLLBACK WORK.
Quand un utilisateur ouvre une transaction, il peut déclarer
le type d'accès à ses données par les autres utilisateurs:
données modifiées,
données au début de la transaction,
accès au données interdit
|
COMMIT WORK |
Termine une transaction, valide les modifications et annule l'enregistrement
des modifications. La commande ROLLBACK WORK rétablit les données
dans l'état original au début de la transaction. |
Le concept de transaction est très important car il permet de
revenir en arrière en cas d'erreur. Essayons cette opération,
d'abord un "commit work" pour fermer toute les transactions précédentes:
manu=> select * from pays;
cod_pays|nom_pays
-----------+---------
1|pays 1
2|pays 2
3|pays 3
(3 rows)
Il y a trois lignes,
begin work;
Début de transaction
insert into pays values (5, 'FAUX pays');
Nous avons inséré une ligne, vérifions que nous avons
bien quatre lignes:
manu=> select * from pays;
cod_pays|nom_pays
-----------+----------------
1|pays 1
2|pays 2
3|pays 3
5|FAUX pays
(4 rows)
Toutes les lignes sont là, ensuite:
rollback work;
Ceci annule la transaction.
manu=> select * from pays;
cod_pays|nom_pays
-----------+---------
1|pays 1
2|pays 2
3|pays 3
(3 rows)
Après avoir vérifié les données, nous constatons
que la table est revenue à son état original.
INSERT |
Pour insérer des données dans la table. |
CREATE TABLE |
Autre commande importante, pour la création d'une table
et ses colonnes, voyons le type de données qu'elle peut supporter:
char(range): |
Chaîne de caractères alpha-numériques de longueur
fixe. |
varchar(range): |
Chaîne de caractères alpha-numériques de longueur
variable jusqu'à un maximum. |
int2: |
Nombre entier sur 2 octets: 2**-15 à 2**15 |
int4: |
Nombre entier sur 4 octetes: 2**-31 à 2**31 |
money(n, m): |
Nombre à virgule fixe, ex: money(6,3) est un nombre à
6 chiffres, dont 3 décimales (3 entiers and 3 décimales). |
time: |
Temps comportant heure, minute, seconde et centième de seconde,
HH:MM:SS:CCC |
date: |
Date contenant jour, mois et année, AAAA/MM/DD |
timestamp: |
Donnée date et heure sous la forme AAAA/MM/DD:HH:MM:SS:CCC |
float(n): |
Nombre réel en simple precision. |
float3: |
Nombre réel en double précision. |
Les définitions de type de données sont généralement
dépendantes du gestionnaire SQL; il existe néanmoins une
norme (la dernière est ANSI/92 ou SQL/3) qui définit les
caractéristiques de quelques types de données. Ce cours est
basé uniquement sur les types spécifiques de PostgreSQL. |
DELETE |
efface les lignes de la table |
UPDATE |
modifie les colonnes d'une ligne de la table |
|