Introduction à SQL
2ème partie
![[Illustration]](../../common/images/illustration35.gif)
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 |
|
![[Top
Bar]](../../common/images/../../common/images/Topbar-fr.gif){kind=small}
![[Bottom Bar]](../../common/images/../../common/images/Bottombar-fr.gif){kind=small}
![[Photo not
available]](../../common/images/Manuel-S.gif)