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.
IntroductionCet 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 tablesComme 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:
RemarqueQue 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 tablesEn 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 tableNous 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.
RemarquePostgreSQL 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éesJ'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 WHEREJusqu'à 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 UPDATELa 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 DELETELa 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)