Nouveautés Archives

Introduction à SQL
Installation de PostgreSQL

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 à 215
`int4:`	Nombre entier sur 4 octetes: 2-31 à 231
`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

Résumé

Malgré notre style quelque peu erratique, nous avons exposé une introduction à SQL, et installé une base de données relationnelle.

SQL permet de construire une couche d'abstraction pour les données et de les gérer comme nécessaire.

Avec ce que nous avons vu jusque ici, la question est maintenant: comment utiliser SQL depuis une application?

La réponse viendra pas à pas; dans notre troisième article, nous verrons une (modeste) application C utilisant SQL.

Traduit par Jean-Denis Girard

Introduction à SQL Installation de PostgreSQL

par Manuel Soriano

Introduction

Premiers pas

Fonctions

Révision des concepts

Résumé

Introduction

Premiers pas

Fonctions

Révision des concepts

Résumé

Introduction à SQL
Installation de PostgreSQL