Home Map Index Search News Archives Links About LF
[Top
Bar]
[Bottom Bar]
[Photo not
available]
Manuel Soriano

Ecrivez à l'auteur


Table des matières
Introduction 
Création de tables 
Modification de tables 
Insertion de données dans une table 
Recherche de données 
Détails de WHERE 
Commande UPDATE 
Commande DELETE 
Références
 

Introduction à SQL
2ème partie

[Illustration] 

Résumé: Dans cette deuxième partie, nous révisons plusieurs expressions fondamentales du langage SQL.  


Introduction

Cet article est la deuxième partie du cours SQL. Nous allons décrire les commandes SQL de création, modification et suppression de tables. 

Nous nous concentrerons particulièrement sur la commande permettant d'extraire des informations de la base (SELECT), commande la plus importante selon moi. 

J'espère que cette seconde partie vous sera agréable et instructive. 

Création de tables

Comme nous l'avons vu dans notre premier article, la commande de création de table est CREATE avec TABLE comme paramètre. En effet, la commande CREATE permet de créer: 
  • des utilisateurs: CREATE USER 
  • des tables: CREATE TABLE 
  • des indexes: CREATE INDEX 
  • des vues: CREATE VIEW 
La commande CREATE indique au gestionnaire la création d'un objet. Nous verrons les détails plus tard. 

Nous nous intéressons pour l'instant à la création d'une table: 

Syntaxe

CREATE TABLE nom ( colonne type [DEFAULT value] [NOT NULL], ...
      [INHERITS (herite, ...)]
      [CONSTRAINT constrainte CHECK (test), CHECK (test)] );
Avec: 
nom: nom de la table, utilisé comme référence par toutes les commandes
colonne: nom de la colonne
type: type de données(varchar, char, int, date, time, timestamp), Postgres a d'autres types de donneés mais ils ne sont pas compatibles ANSI SQL
valeur: valeur donnée par défaut
herite paramètre propre à Postgres. Il définit un héritage d'une autre table. Cela crée une entité qui comporte les colonnes de la table en cours de création et celles héritées de l'autre table.
contrainte: règle d'intégrité à vérifier à chaque modification des données de la table 
test: conditions à vérifier 
Exemple: 
CREATE TABLE pays (
         cod_pays integer NOT NULL,
         nom_pays varchar(30))
   CONSTRAINT cod_raro CHECK (cod_pays > 0 AND cod_pays < 154);
Cet exemple crée la table des pays. Chaque fois qu'une nouvelle donnée est ajoutée, les conditions suivantes doivent être réunies: 
  • le code pays n'est pas NULL, sinon le gestionnaire renvoie l'erreur:
  • ExecAppend: Fail to add null value in not null attribute cod_pays
  • le code pays est compris entre 1 et 153 inclus, sinon le gestionnaire indique l'erreur:
  • ExecAppend: rejected due to CHECK constraint cod_raro

Remarque

Que signifie NULL? Avec SQL, il existe deux états, donnée existante et pas de donnée. En effet, il est important de savoir si une donnée a été introduite ou non. La valeur numérique zéro (0) ou une chaîne de caractères constituée de plusieurs espaces constituent des données. SQL introduit la notion NULL, et permet de l'utiliser. Un exemple concrêt: 

J'ai une table de factures, avec les champs: client, valeur, date_emission, date_paiement

Lorsque je crée la ligne, j'insère les données: client, valeur, date_emission

Le champ date de paiement reste vide; ainsi, il me sera possible de savoir quelles factures restent à payer, grâce à la simple commande: 

SELECT * FROM factures WHERE date_paiement IS NULL;
On pourrait objecter qu'un zéro (0) dans le champ date_paiement conduirait au même résultat. C'est vrai en premier examen, mais zéro (0) n'est pas une date; on ne peut plus alors utiliser le type date pour ce champ, et aucune fonction agissant sur des dates ne pourra être utilisée. 

Exemples de données NULL: 

insert into pays values (15, NULL);
Ou encore: 
insert into pays (cod_pays) values (27);
L'absence du champ "nom" implique qu'il prend la valeur NULL. 

Modification de tables

En PostgreSQL, les seules modifications de structure de tables autorisées sont l'ajout de nouvelles colonnes. 
ALTER TABLE table ADD nom type;
Avec: 
 
table  nom de la table à modifier
nom  nom de la colonne à ajouter
type  type de données (voir CREATE TABLE)

Insertion de données dans une table

Nous allons maintenant entrer des données dans la  table: 

Syntaxe

INSERT INTO table [(col, col, ...)] VALUES (valeur-1, valeur-2, ...)
ou encore: 
INSERT INTO table [(col, col, ...)] SELECT ....
Il y a donc deux façons d'insérer des données dans une table, soit ligne par ligne, soit à travers une sélection qui peut renvoyer une ou plusieurs lignes. 

Lorsqu'on insère des lignes dans une table, tous les champs sont mis à jour, y compris ceux qui n'apparaissent pas dans la commande, qui prennent la valeur NULL. 

Si on ne précise pas dans la commande quels champs sont remplis, le gestionnaire suppose que toutes les données vont être fournies, par exemple: 

INSERT INTO pays VALUES (34, 'Espagne');
Ceci serait faux: 
INSERT INTO pays VALUES (34);
Mais cela serait correct: 
INSERT INTO pays (cod_pays) VALUES (34);
Je conseille que les commandes incorporées dans des programmes en "C" ou dans des fonctions de la base précisent TOUJOURS les champs concernés; en effet, si la structure d'une table est modifiée (ALTER TABLE) par ajout d'une colonne, l'insertion après modification produit une erreur. 
Exemple: 
INSERT INTO pays VALUES (34, 'Espagne'); 

INSERT 18301 1 

ALTER TABLE pays add population integer 

INSERT INTO pays VALUES (34, 'Espagne'); 

Ceci conduit à une erreur de traduction (parser) car il manque la donnée pour le champ population. 

Remarque

PostgreSQL ne génère pas d'erreur. Il crée l'enregistrment avec le champ "population" NULL. C'est une particularité de PostgreSQL, tout autre gestionnaire SQL renverrait une erreur. 

Il nous reste à voir l'autre type de la commande INSERT, dont les données proviennent d'une sélection. 

Ce type d'insertion est très souvent utilisé pour créer des tables temporaires, ou des tables pour réaliser des calculs statistiques. 

La partie remplacée est celle qui touche les données elles-mêmes, qui sont d'abord extraites par l'instruction SELECT. L'instruction SELECT renvoie une ou plusieurs lignes, elle fonctionne comme pour une requête simple. 

Recherche de données

J'attendais avec impatience d'atteindre cette partie! :-)) 

Le langage SQL sans SELECT serait comme un steack sans frites. 

La commande SELECT permet l'accès aux données de la base, avec la puissance apportée par les réunions de tables, les critères de recherche, et les fonctions appliquées aux données. 

Premier exemple: 

select * from pays;
Autre exemple: 
SELECT a.nom_pays, SUM(population)
    FROM pays a, region b, localite c
    WHERE b.cod_pays = a.cod_pays
        AND (c.cod_pays = b.cod_pays
        AND c.region_code = b.region_code)
        AND population IS NOT NULL
        GROUP BY a.nom_pays
        ORDER BY sum ASC;
Cette requête fournit le nom et la population de tous les pays, par ordre croissant de population. 

Il a fallu auparavant ajouter un champ population à la table des localités, comme ceci: 

create table localite (cod_pays int, 
                        region_code int, 
                        localite_code int,
                        localite_nom varchar(60),
                        population int);
insert into localite values (1, 1, 1, 'pays 1, region 1, localite 1', 5435);
insert into localite values (2, 1, 1, 'pays 2, region 1, localite 1', 7832);
insert into localite values (3, 1, 1, 'pays 3, region 1, localite 1', 4129);
insert into localite values (1, 2, 1, 'pays 1, region 2, localite 1', 76529);
insert into localite values (2, 2, 1, 'pays 2, region 2, localite 1', 9782);
insert into localite values (3, 2, 1, 'pays 3, region 2, localite 1', 852);
insert into localite values (1, 3, 1, 'pays 1, region 3, localite 1', 3433);
insert into localite values (2, 3, 1, 'pays 2, region 3, localite 1', 7622);
insert into localite values (3, 3, 1, 'pays 3, region 3, localite 1', 2798);
insert into localite values (1, 1, 2, 'pays 1, region 1, localite 2', 7789);
insert into localite values (2, 1, 2, 'pays 2, region 1, localite 2', 76511);
insert into localite values (3, 1, 2, 'pays 3, region 1, localite 2', 98);
insert into localite values (1, 2, 2, 'pays 1, region 2, localite 2', 123865);
insert into localite values (2, 2, 2, 'pays 2, region 2, localite 2', 886633);
insert into localite values (3, 2, 2, 'pays 3, region 2, localite 2', 982345);
insert into localite values (1, 3, 2, 'pays 1, region 3, localite 2', 22344);
insert into localite values (2, 3, 2, 'pays 2, region 3, localite 2', 179);
insert into localite values (3, 3, 2, 'pays 3, region 3, localite 2', 196813);
insert into localite values (1, 1, 3, 'pays 1, region 1, localite 3', 491301);
insert into localite values (2, 1, 3, 'pays 2, region 1, localite 3', 166540);
insert into localite values (3, 1, 3, 'pays 3, region 1, localite 3', 165132);
insert into localite values (1, 2, 3, 'pays 1, region 2, localite 3', 0640);
insert into localite values (2, 2, 3, 'pays 2, region 2, localite 3', 65120);
insert into localite values (3, 2, 3, 'pays 3, region 2, localite 3', 1651462);
insert into localite values (1, 3, 3, 'pays 1, region 3, localite 3', 60650);
insert into localite values (2, 3, 3, 'pays 2, region 3, localite 3', 651986);
insert into localite values (3, 3, 3, 'pays 3, region 3, localite 3', NULL);
commit work;
Bien, nous ne pouvons utiliser ALTER TABLE, mais UPDATE, et je ne l'ai pas expliqué, donc utilisez "Couper / Coller" et tout le monde sera content:-)) 

Nous pouvons maintenant exécuter la requête, et le résultat devrait être: 

 nom_pays|    sum
---------+-------
   pays 1| 705559
   pays 2|1212418
   pays 3|2804018
(3 rows)
Vérifions le résultat: 
select sum(population) from localite where cod_pays = 1; 
Resultat: 
   sum
------
791986
(1 row)
!!!!!! Différent !!!!!! 

En vérifiant la table des régions, il apparaît que la région 3 manque. Donc: 

INSERT INTO region VALUES (3, 1, 'region 3, pays 1');
INSERT INTO region VALUES (3, 2, 'region 3, pays 2');
INSERT INTO region VALUES (3, 3, 'region 3, pays 3');
Répétons la commande: 
 nom_pays|    sum
---------+-------
   pays 1| 791986
   pays 2|1872205
   pays 3|3003629 
La région 3 manque pour tous les pays. 

Bien, pour ceux qui se sentent perdus, souvenez-vous que les relations entre les tables sont exactes, les données ne sont extraites que si les conditions sont vraies. 

Détaillons la première partie du WHERE: b.cod_pays = a.cod_pays 

Cela signifie simplement que je joins la table des pays avec celle des régions, quand le code pays est identique; maintenant souvenez-vous les données entrées pour les pays (n'exécutez pas ce qui suit): 

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;
Les données pour les régions: 
create table region (region_code int, 
                        cod_pays int, 
                        region_nom 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;
Toutes les régions 3 de chaque pays manquent. Les données avec code région égal à trois sont supprimées par la deuxième partie de la clause WHERE
        AND (c.cod_pays = b.cod_pays
        AND c.region_code = b.region_code)
La région existe dans la table des localités, mais PAS dans la table des pays. 

Pour ceux qui n'ont pas compris, prenez une aspirine, sortez votre chien (si vous n'avez pas de chien, allez vous promener seul), respirez un peu d'air frais et recommencez au premier exercice. 

Il est très important de comprendre comment les relations sont exécutées, sans cela les actions sur la base peuvent avoir de mauvais résultats. 

Changeons de vitesse et attaquons-nous à la syntaxe de la commande SELECT. 

SELECT [DISTINCT] expression1 [AS nom-attribut] {, expression-i [as nom-attribut-i]} 
       [INTO TABLE classname] 
       [FROM from-list] 
       [WHERE where-clause] 
       [GROUP BY attr_name1 {, attr_name-i....}] 
       [ORDER BY attr_name1 [ASC | DESC ] [USING op1 ] {, nom-attribute-i...}] 
       [UNION {ALL} SELECT ...] 
Pas à pas: 
DISTINCT: pour éliminer les enregistrements identiques dans le résultat
expression1 ce qu'on souhaite en retour, généralement un champ d'une table de la liste FROM
AS nom-attribut alias pour le nom d'un champ, par exemple: 
manu=> select cod_pays from pays;
   cod_pays
-----------
          1
          2
          3
(3 rows)          
Maintenant avec alias: 
manu=> select cod_pays as p from pays;
     p
------
     1
     2
     3
(3 rows)
INTO TABLE permet à la colonne résultante d'être insérée dans une autre table (voir INSERT ... SELECT...)
FROM liste de tables en entrée
WHERE critères d'union et de selection
GROUP BY critères de regroupement, 
ORDER BY: ordre de tri des lignes renvoyées, ASC ordre croissant, DSC ordre décroissant, USING si la colonne qui définit l'ordre n'est pas dans la liste (expression)
UNION ALL SELECT ajoute aux résultats du premier SELECT, les résultats de ce deuxième SELECT, qui peut agir sur des tables différentes mais doit retourner le même nombre de colonnes
Nous avons vu que la commande SELECT permet non seulement de renvoyer des données de la base mais aussi de les modifier: 
SELECT SUM(salaire * 1.1) - SUM(salaire) AS increment FROM employes; 
Cette commande renverra le coût global d'une augmentation de salaire de 10% de tous les employés. 

Fonctions disponibles dans SELECT: 
 
COUNT(): nombre de colonnes qui ne sont pas nulles
SUM():  somme d'une colonne de nombres
AVG():  moyenne d'une colonne de dnombres
MIN():  valeur minimale d'une colonne de nombres
MAX():  valeur maximale d'une colonne de nombres
FLOAT(int):  transforme un entier en nombre FLOAT, FLOAT(12345)
FLOAT4(int):  transforme un entier en nombre FLOAT4, FLOAT4(12345)
INT(float): transforme un nombre à virgule flottante (FLOAT/4) en entier INT, INT(123.456)
LOWER(text):  transforme toutes les majuscules du texte en minuscules
UPPER(text):  transforme toutes les minuscules du texte en majuscules
LPAD(text, long, char): remplit la gauche de text avec le caractère char jusqu'à long
RPAD(text, long, char):  remplit la droite de text avec le caractère char jusqu'à long
LTRIM(text, char):  enlève depuis la gauche de text tous les caractères char
RTRIM(text, char):  enlève depuis la droite de text tous les caractères char
POSITION(chaine IN text): extrait de text la position de chaine, mais CA NE FONCTIONNE PAS
SUBSTR(text,debut[,fin]): extrait une partie du text de debut jusqu'à fin ou la fin du texte
DATETIME(date, heure):  converti un format datetime vers date (YYYY-MM-DD) et heure (HH:MM)
Ce sont quelques unes des fonctions qui existent dans SQL, celles-ci sont définies dans la norme ANSI SQL, et se trouvent aussi dans Postgres95. 

Détails de WHERE

Jusqu'à présent, nous avons vu que la section WHERE du SELECT comporte des expressions telles: 
AND colonne = valeur
Voici ce que l'on peut trouver dans la clause WHERE

AND, OR, NOT, IN, IN ALL, =, !=, >, <, (SELECT....), LIKE de même les parenthèses sont importantes, par exemple: 

WHERE 
   colonne IN (SELECT DISTINCT colonne FROM table WHERE ....)
   colonne IN ('valeur1','valeur2','valeur3',...)
   (colonne = 'valeur' and colonne = 'autre_valeur' OR colonne != 'valeur')
!= est équivalent à NOT EQUAL 

LIKE permet de chercher une chaîne avec des jokers: 

WHERE colonne LIKE '%Pepito%'
% est un joker, dans l'exemple, le résultat est vrai si "Pepito" est dans le texte. 
WHERE colonne LIKE 'Pepito%'
sera vrai si "Pepito" est au début du texte. 
WHERE colonne LIKE '%Pepito'
sera vrai si "Pepito" est à la fin du texte. 

Il n'y a pas assez de place ici pour dresser la liste de toutes les possibilités offertes par WHERE. La limite est probablement l'imagination du programmeur, ou les limites du traducteur SQL. 

Assez pour la commande SELECT passons aux deux suivantes. 

Commande UPDATE

La commande UPDATE permet la modification d'une ou plusieurs colonnes, selon la condition définie dans WHERE

Syntaxe: 

UPDATE table SET colonne-1 = expression-1 
                 [, colonne-i = expression-i] 
       [WHERE condition] 
Where: 
table: table à modifier, une seule table à la fois peut être modifiée
colonne: champ à modifier
expression: valeur que la colonne recevra. La valeur peut être statique ou le résultat d'une fonction
condition: conditions qui définissent les limites des modifications, les règles sont les mêmes que pour  SELECT

Commande DELETE

La commande DELETE permet de détruire un ou plusieurs enregistrements de la table. 
 

Syntaxe

DELETE FROM table
       [WHERE condition] 
Avec: 
table:  la table dont les lignes seront effacées, seule une table peut être spécifiée
condition: condition permettant de fixer les limites de l'effacement, avec les mêmes règles que SELECT 
REMARQUE: sans clause WHERE, TOUTES les données de la table seront effacées 

Références

SQL: Introduction à SQL. Installation de PostgreSQL, premier article de la série.


Traduit par Jean-Denis Girard

Ce site Web est maintenu par Miguel Ángel Sepúlveda
© Manuel Soriano 1998
LinuxFocus 1998