1. Introduction

La conception et l’utilisation de bases de données relationnelles sur micro-ordinateurs n’est pas un domaine réservé aux informaticiens". C’est en tout cas ce que pensent beaucoup d’utilisateurs en voyant ce type de logiciel intégré aux suites bureautiques les plus connues.

Cependant la maîtrise d’un SGBDR micro (Système de Gestion de Bases de Données Relationnelles) est loin d’être aussi facile à acquérir que celle d’un logiciel de traitement de texte ou d’un tableur.

Plusieurs étapes sont nécessaires à la mise en place d’une base de données, dès lors que l’on a précisément défini ses besoins (ce qui n’est déjà pas chose facile !) :

  • la création de la structure de la base sous forme de tables (tableaux de données) reliées entre elles par des données clés,

  • la conception des requêtes qui permettront d’extraire ou de mettre à jour les informations qu’elle contient,

  • la conception de l’interface homme-machine (écrans et états) qui rendra plus conviviale la saisie et la restitution des informations.

Le degré de difficulté dans la conception de l’interface varie beaucoup selon le logiciel utilisé qui est d’ailleurs le plus souvent différent du SGBDR.

La conception de la structure de la base de données, si elle est un peu complexe à appréhender, peut nécessiter, en amont, l’utilisation d'outils de modélisation conceptuels entités-associations (Modèle Conceptuel des Données ou MCD de la méthode MERISE ou diagramme de classes du langage UML). Mais, même dans les cas les plus simples il faut obligatoirement connaître les concepts du Modèle Relationnel, sans quoi un utilisateur non averti pourra toujours arriver à créer une structure inadaptée et sera vite bloqué dans la conception des requêtes.

Il s’agit ici, d’étudier les principaux opérateurs de l’algèbre relationnelle servant de base à l’élaboration et à l’analyse (plan d’exécution) des requêtes.

Bon nombre d’utilisateurs qui voient les matériels informatiques et les logiciels changer tous les trois mois, seraient surpris d’apprendre que l’algèbre relationnelle a été définie par Codd en 1970.

Elle est à l’origine du langage SQL (Structured Query Language) d'IBM, langage d’interrogation et de manipulation de tous les SGBDR actuels (Oracle, PostgreSQL, MySQL, MS SQLServer, MS Access et tous les autres).

Une bonne maîtrise de l’algèbre relationnelle permet de concevoir n’importe quelle requête aussi complexe soit elle avant de la mettre en œuvre à l’aide du langage SQL.

Parmi les opérations de l’algèbre relationnelle, on dispose d’opérations classiques sur les ensembles (union, intersection, différence, produit cartésien) puis d'opérations propres (projection, sélection, jointure, division).

Nous aborderons également les opérateurs d'insertion, de modification et de suppression de données

2. Introduction au Modèle Relationnel

L’exemple suivant, relatif à la gestion simplifiée des étapes du Tour de France 97, va nous servir à introduire le vocabulaire lié au modèle relationnel.

Table 1. EQUIPES
CodeEquipe NomEquipe DirecteurSportif

BAN

BANESTO

Eusebio UNZUE

COF

COFIDIS

Cyrille GUIMARD

CSO

CASINO

Vincent LAVENU

FDJ

LA FRANCAISE DES JEUX

Marc MADIOT

FES

FESTINA

Bruno ROUSSEL

GAN

GAN

Roger LEGEAY

ONC

O.N.C.E.

Manolo SAIZ

TEL

TELEKOM

Walter GODEFROOT

…​

…​

…​

Table 2. COUREURS
NuméroCoureur NomCoureur CodeEquipe CodePays

8

ULLRICH Jan

TEL

ALL

31

JALABERT Laurent

ONC

FRA

61

ROMINGER Tony

COF

SUI

91

BOARDMAN Chris

GAN

G-B

114

CIPOLLINI Mario

SAE

ITA

151

OLANO Abraham

BAN

ESP

…​

…​

…​

…​

Table 3. ETAPES
NuméroEtape DateEtape VilleDépart VilleArrivée NbKm

1

06-jul-97

ROUEN

FORGES-LES-EAUX

192

2

07-jul-97

ST-VALERY-EN-CAUX

VIRE

262

3

08-jul-97

VIRE

PLUMELEC

224

…​

…​

…​

…​

…​

Table 4. temps réalisés
NuméroCoureur NuméroEtape TempsRéalisé

8

3

04:54:33

8

1

04:48:21

8

2

06:27:47

31

3

04:54:33

31

1

04:48:37

31

2

06:27:47

61

1

04:48:24

61

2

06:27:47

91

3

04:54:33

91

1

04:48:19

91

2

06:27:47

114

3

04:54:44

114

1

04:48:09

114

2

06:27:47

151

3

04:54:33

151

1

04:48:29

151

2

06:27:47

…​

…​

…​

Table 5. PAYS
CodePays NomPays

ALL

ALLEMAGNE

AUT

AUTRICHE

BEL

BELGIQUE

DAN

DANEMARK

ESP

ESPAGNE

FRA

FRANCE

G-B

GRANDE BRETAGNE

ITA

ITALIE

P-B

PAYS-BAS

RUS

RUSSIE

SUI

SUISSE

…​

…​

2.1. Les tables

Comme nous pouvons le constater, le modèle relationnel est un modèle d’organisation des données sous forme de Tables (Tableaux de valeurs) ou chaque Table représente une Relation, au sens mathématique d'Ensemble.

C’est ainsi que dans l’exemple présenté, figurent l’ensemble des Equipes, des Coureurs, des Etapes, des Temps réalisés par les coureurs à chacune des étapes, et enfin l’ensemble des pays.

2.2. Les colonnes

Les colonnes des tables s’appellent des attributs et les lignes des n-uplets (où n est le degré de la relation, c’est à dire le nombre d’attributs de la relation).

  • Un attribut ne prend qu’une seule valeur pour chaque n-uplet.

  • L’ordre des lignes et des colonnes n’a pas d’importance.

2.3. Clé primaire

Chaque table doit avoir une clé primaire constituée par un ensemble minimum d’attributs permettant de distinguer chaque n-uplet de la Relation par rapport à tous les autres. Chaque ensemble de valeurs formant la clé primaire d’un n-uplet est donc unique au sein d’une table.

C’est ainsi que dans la table COUREURS, chaque coureur a un NuméroCoureur différent.

Dans certains cas, plusieurs clés primaires sont possibles pour une seule table. On parle alors de clés candidates. Il faut alors en choisir une comme clé primaire.

2.4. Clés étrangères

Les liens sémantiques (ou règles de gestion sur les données) existants entre les ensembles sont réalisés par l’intermédiaire de clés étrangères faisant elles-mêmes référence à des clés primaires d’autres tables.

C’est ainsi que dans la table COUREURS, la clé étrangère CodeEquipe (faisant référence à la clé primaire de même nom dans la table EQUIPES) traduit les deux règles de gestion suivantes :

Un COUREUR appartient à une EQUIPE
Une EQUIPE est composée de plusieurs COUREURS

2.5. Association

Il existe deux grands types d’association : Un - Plusieurs (comme le précédent) et Plusieurs - Plusieurs. La réalisation de ce dernier type d’association, un peu plus complexe, passe par l’utilisation d’une table intermédiaire dont la clé primaire est formée des clés étrangères des tables qu’elle relie.

C’est ainsi que la table des temps réalisés à chaque étape par chacun des coureurs exprime les deux règles de gestion suivantes :

Un COUREUR participe à plusieurs ETAPES
Une ETAPE fait participer plusieurs COUREURS

2.6. Le modèle logique ou schéma

Le modèle logique relationnel ou schéma est le plus souvent décrit sous la forme suivante, les clés primaires étant soulignées et les clés étrangères marquées par un signe distinctif (ici *).

EQUIPES CodeEquipe, NomEquipe, DirecteurSportif)

COUREURS (NuméroCoureur, NomCoureur, CodeEquipe*, CodePays*)

ETAPES (NuméroEtape, VilleDépart, VilleArrivée, NbKm)

participe (NuméroCoureur*, NuméroEtape*, TempsRéalisé)

PAYS (CodePays, NomPays)

2.7. Le modèle conceptuel de données (MCD)

On peut aussi le représenter sous forme graphique, de manière à mieux visualiser et interpréter les liens :

mcd
Figure 1. MCD
  • Un COUREUR appartient à une EQUIPE

  • Une EQUIPE est composée de plusieurs COUREURS

  • Un COUREUR est originaire d’un PAYS

  • Un PAYS est représenté par plusieurs COUREURS

  • Un COUREUR participe à plusieurs ETAPES

  • Une ETAPE fait participer plusieurs COUREURS

Dans le cadre d’un projet d’informatisation, la conception d’une base de données relationnelle passe d’abord par :

  • l’identification des objets de gestion (Coureurs, Etapes, …​)

  • des règles de gestion du domaine modélisé (interviews des utilisateurs, étude des documents manipulés, des fichiers existants, …​).

Une fois énoncées et validées, ces règles nous conduisent automatiquement à la structure du modèle relationnel correspondant.

3. Opérations de base pour la manipulation des données

Les données se manipule à l’aide d’un LMD ou Langage de Manipulation de Données (DDL en anglais).

Un langage de manipulation de données (LMD ; en anglais data manipulation language, DML) est un langage de programmation et un sous-ensemble de SQL pour manipuler les données d’une base de données.

Ces commandes de manipulation de données doivent être validées à l’issue d’une transaction pour être prises en compte.

Commandes SQL : On distingue typiquement quatre types de commandes SQL de manipulation de données :

  • SELECT : sélection de données dans une table ;

  • INSERT : insertion de données dans une table ;

  • DELETE : suppression de données d’une table ;

  • UPDATE : mise à jour de données d’une table.

— wikipedia
https://fr.wikipedia.org/wiki/Langage_de_manipulation_de_donn%C3%A9es

D’une manière générale, les objets du schéma (tables) sont manipulés avec :

  • CREATE : création

  • ALTER : modification

  • DROP : destruction

Tandis que les données, instances du schéma sont manipulées avec : créées, modifiées et détruites avec :

  • INSERT : insertion d’un n-uplet

  • UPDATE : modification

  • DELETE : suppression

L’algèbre se compose d’un ensemble d’opérateurs, parmi lesquels 6 sont nécessaires et suffisants et permettent de définir les autres par composition. Une propriété fondamentale de chaque opérateur est qu’il prend une ou deux relations en entrée, et produit une relation en sortie.

Cette propriété (dite de clôture) permet de composer des opérateurs : on peut appliquer une sélection au résultat d’un produit cartésien, puis une projection au résultat de la sélection, et ainsi de suite.

En fait on peut construire des expressions algébriques arbitrairement complexes qui permettent d’effectuer toutes les requêtes relationnelles à l’aide d’un petit nombre d’opérations de base.

Ces opérations sont donc:

  • La sélection, dénotée \( \sigma \)

  • La projection, dénotée \( \Pi \)

  • Le renommage, dénoté \( \rho \)

  • Le produit cartésien, dénoté \( \times \)

  • L’union, dénotée \( \cup \)

  • L’intersection, dénotée \( \cap \)

  • La différence, dénotée \( - \)

Les trois premiers sont des opérateurs unaires (ils prennent en entrée une seule relation) et les autres sont des opérateurs binaires.

À partir de ces opérateurs il est possible d’en définir d’autres, et notamment la jointure, \( \bowtie \), qui est la composition d’un produit cartésien et d’une sélection.

Nous allons nous intéresser aux opérations de projection, sélection et jointure.

3.1. Opération PROJECTION

3.1.1. Définition

Formalisme :

R = PROJECTION (R1, liste des attributs)

L’opérateur \(\pi \) est utilisé pour décrire l’opération de projection en algèbre relationnelle

\( \Pi_{ListeAttributs} (Table) \)

Example 1. Exemples sur la table CHAMPIGNONS
Espèce Catégorie Conditionnement

Rosé des prés

Conserve

Bocal

Rosé des prés

Sec

Verrine

Coulemelle

Frais

Boîte

Rosé des prés

Sec

Sachet plastique

R1 = PROJECTION (CHAMPIGNONS, Espèce)

\(R1 = \pi_{Espèce} (CHAMPIGNONS) \)

Espèce

Rosé des prés

Coulemelle

R2 = PROJECTION (CHAMPIGNONS, Espèce, Catégorie)

\(R2 = \pi_{Espèce, Catégorie} (CHAMPIGNONS) \)

Espèce Catégorie

Rosés des prés

Conserve

Rosé des prés

Sec

Coulemelle

Frais

  • Cet opérateur ne porte que sur 1 relation.

  • Il permet de ne retenir que certains attributs spécifiés d’une relation.

  • On obtient tous les n-uplets de la relation à l’exception des doublons

3.1.2. Syntaxe SQL

SELECT DISTINCT liste_attributs FROM table ;

SELECT liste_attributs FROM table ;
Exemples :
SELECT DISTINCT Espèce FROM Champignons ;

SELECT DISTINCT Espèce, Catégorie FROM Champignons ;
  • La clause DISTINCT permet d’éliminer les doublons.

3.1.3. Travail à faire

  • Soit la table ETUDIANT(N°Etudiant, Nom, Age, CodePostal, Ville)

  • On exécute les requêtes de projection suivante :

  • Pour chaque requête, quelles sont les informations sélectionnées ?

SELECT DISTINCT Ville  FROM ETUDIANT ;
Correction

Obtenir la liste des villes où habitent les étudiants sans doublons

SELECT DISTINCT Age  FROM ETUDIANT ;
Correction

Obtenir les ages des étudiants sans doublons

3.2. Opération SELECTION (ou RESTRICTION)

3.2.1. Définition

Formalisme :

R = SELECTION (R1, condition)

L’opérateur \(\sigma \) est utilisé pour décrire l’opération de sélection en algèbre relationnelle

\( \sigma_{condition} (Table) \)

Example 4. Exemples sur la table CHAMPIGNONS
Espèce Catégorie Conditionnement

Rosé des prés

Conserve

Bocal

Rosé des prés

Sec

Verrine

Coulemelle

Frais

Boîte

Rosé des prés

Sec

Sachet plastique

R3 = SELECTION (CHAMPIGNONS, Catégorie = "Sec")

\(R3 = \sigma_{Catégorie = "Sec"} (CHAMPIGNONS) \)

Espèce Catégorie Conditionnement

Rosé des prés

Sec

Verrine

Rosé des prés

Sec

Sachet plastique

  • Cet opérateur porte sur 1 relation.

  • Il permet de ne retenir que les n-uplets répondant à une condition exprimée à l’aide des opérateurs arithmétiques (=, >, <, >=, ⇐, <>) ou logiques de base (ET, OU, NON).

  • Tous les attributs de la relation sont conservés.

  • Un attribut peut ne pas avoir été renseigné pour certains n-uplets. Si une condition de sélection doit en tenir compte, on indiquera simplement : nomAttribut "est non renseigné".

3.2.2. Syntaxe SQL

SELECT * FROM table WHERE condition
Exemples :
SELECT * FROM Champignons WHERE Catégorie="Sec" ;

La condition de sélection exprimée derrière la clause WHERE peut être spécifiée à l’aide :

  • des opérateurs de comparaison :

  • des opérateurs logiques : AND, OR, NOT

  • des opérateurs : IN, BETWEEN, LIKE, IS, ALL

3.2.3. Travail à faire

  • Soit la table ETUDIANT(N°Etudiant, Nom, Age, CodePostal, Ville)

  • On exécute les requêtes de sélection suivante :

  • Pour chaque requête, quelles sont les informations sélectionnées ?

SELECT *  FROM ETUDIANT  WHERE Age  IN (19, 20, 21, 22, 23) ;
Correction

Sélection des étudiants dont l’âge est 19, 18, 20, 21, 22 ou 23 ans

SELECT *  FROM ETUDIANT  WHERE Age  BETWEEN  19 AND  23 ;
Correction

Sélection des étudiants dont l’âge est compris entre 19 et 23 ans

SELECT *  FROM ETUDIANT  WHERE CodePostal  LIKE '42%' ;
Correction

Sélection des étudiants dont le code postal commence par 42 (% est une widecard qui remplace n’importe quoi)

SELECT *  FROM ETUDIANT  WHERE CodePostal  LIKE '42___' ;
Correction

Sélection des étudiants dont le code postal commence par 42 suivit de 3 caractères exactement

SELECT *  FROM ETUDIANT  WHERE Ville  IS NULL ;
Correction

Sélection des étudiants dont la ville n’est pas renseignée

SELECT *  FROM ETUDIANT  WHERE Ville  IS NOT NULL ;
Correction

Sélection des étudiants dont la ville est renseignée (la colonne Ville n’est pas vide)

SELECT *  FROM ETUDIANT  WHERE Age >= ALL(SELECT Age FROM ETUDIANT) ;
Correction

Sélection de l’étudiant ou des étudiants les plus âgés La sous-requête est exécutée en premier et renvoie les âges des étudiants. Pour tous les âges renvoyés, la requête principale est exécutée et ne sélectionne que les plus grand

Le même résultat aurait pu être obtenu avec la requête suivante :

SELECT *  FROM ETUDIANT  WHERE Age = (SELECT MAX(AGE) FROM ETUDIANT) ;

3.2.4. Synthèse : Afficher les colonnes depuis une table

  • Choisissez la bonne réponse :


  • Les requêtes SQL finissent par :

  1. FROM
  2. colonnes
  3. guillemets
  4. une virgule
  5. un point virgule
  6. une étoile
  • L’instruction SELECT permet d’afficher une ou plusieurs ??? d’une table :

  1. FROM
  2. colonnes
  3. guillemets
  4. une virgule
  5. un point virgule
  6. une étoile
  • Quelle instruction permet de choisir la table dans laquelle on va chercher les informations :

  1. FROM
  2. colonnes
  3. guillemets
  4. une virgule
  5. un point virgule
  6. une étoile
  • Pour afficher plusieurs colonnes, il faut séparer chaque nom de colonne par :

  1. FROM
  2. colonnes
  3. guillemets
  4. une virgule
  5. un point virgule
  6. une étoile
  • Pour afficher toutes les colonnes d’une table, que faut-il faut indiquer après l’instruction SELECT :

  1. FROM
  2. colonnes
  3. guillemets
  4. une virgule
  5. un point virgule
  6. une étoile

3.2.5. Exercices w3school

Faire les exercices :

  • SQL SELECT

  • SQL WHERE

  • SQL ORDER BY

3.2.6. COLIBRI Strasbourg – Cours Libres Interactifs : Afficher des colonnes depuis une table

Faire les 6 exercices interactifs de la partie Afficher des colonnes depuis une table :

3.2.7. COLIBRI Strasbourg – Cours Libres Interactifs : Trier les données

Faire les 6 exercices interactifs de la partie Trier les données :

Pour trier les données, on utilise l’instruction ORDER BY :

  • Par ordre alphabétique ou ordre croissant sur la colonne colonne_a_trier :

SELECT colonne1, colonne2, ..., colonne_a_trier FROM table ORDER BY colonne_a_trier;
  • Par ordre alphabétique inverse ou ordre décroissant sur la colonne colonne_a_trier :

SELECT colonne1, colonne2, ..., colonne_a_trier FROM table ORDER BY DESC colonne_a_trier;

3.3. Opération JOINTURE (équi-jointure)

3.3.1. Définition

Formalisme :

R = JOINTURE (R1, R2, condition d’égalité entre attributs)

L’opérateur \(\bowtie \) est utilisé pour décrire l’opération de jointure en algèbre relationnelle

\(R1 \bowtie_{condition} R2 \)

Example 12. Exemples
Table 6. PRODUIT
A.CodePrd Libellé Prix unitaire

590A

HD 1,6 Go

1615

588J

Scanner HP

1700

515J

LBP 660

1820

Table 7. DETAIL_COMMANDE
N°cde B.CodePrd quantité

97001

590A

2

97002

515J

1

97003

515J

3

R = JOINTURE (PRODUIT, DETAIL_COMMANDE,PRODUIT.CodePrd=DETAIL_COMMANDE.CodePrd)

\(R = PRODUIT \bowtie_{PRODUIT.CodePrd=DETAIL\_COMMANDE.CodePrd} DETAIL\_COMMANDE \)

Table 8. R
A.CodePrd Libellé Prix unitaire N°cde B.CodePrd quantité

590A

HD 1,6 Go

1615

97001

590A

2

515J

LBP 660

1820

97002

515J

1

515J

LBP 660

1820

97003

515J

3

  • Cet opérateur porte sur 2 relations qui doivent avoir au moins un attribut défini dans le même domaine (ensemble des valeurs permises pour un attribut).

  • La condition de jointure peut porter sur l’égalité d’un ou de plusieurs attributs définis dans le même domaine (mais n’ayant pas forcément le même nom).

  • Les n-uplets de la relation résultat sont formés par la concaténation des n-uplets des relations d’origine qui vérifient la condition de jointure.

Remarque : Des jointures plus complexes que l’équijointure peuvent être réalisées en généralisant l’usage de la condition de jointure à d’autres critères de comparaison que l’égalité (<,>, ⇐,>=, <>).

3.3.2. Syntaxe SQL de base

En SQL, il est possible d’enchaîner plusieurs jointures dans la même instruction SELECT

En SQL de base
SELECT * FROM table1, table2, table3, ... WHERE table1.attribut1=table2.attribut1 AND table2.attribut2=table3.attribut2 AND ... ;
Exemple
SELECT * FROM PRODUIT, DETAIL_COMMANDE  WHERE PRODUIT.CodePrd=DETAIL_COMMANDE.CodePrd ;

ou en utilisant des alias pour les noms des tables :

Exemple
SELECT * FROM PRODUIT P, DETAIL_COMMANDE D WHERE P.CodePrd=D.CodePrd ;

3.3.3. Syntaxe SQL à partir de SQL2

La clause INNER JOIN (jointure dite interne) est disponible à partir du SQL2 et est supportée aujourd’hui par tous les SGBDR :

Utilisation de INNER JOIN
SELECT * FROM** table1 INNER JOIN table2 ON table1.attribut1=table2.attribut1 INNER JOIN table3 ON table2.attribut2=table3.attribut3... ;
  • Le mot clé INNER est facultatif sur la plupart des SGBDR (sauf MS Access).

  • Cette notation rend plus lisible la requête en distinguant clairement les conditions de jointures, derrière ON, et les éventuelles conditions de sélection ou restriction, derrière WHERE.

  • De plus, l’oubli d’un ON (et donc de la condition de jointure) empêchera l’exécution de la requête, alors qu’avec l’ancienne notation, l’oubli d’une condition de jointure derrière WHERE, n’empêche pas l’exécution de la requête, produisant alors un bien coûteux produit cartésien entre les tables !

Exemple avec des alias :
SELECT * FROM PRODUIT P INNER JOIN DETAIL_COMMANDE D ON P.CodePrd=D.CodePrd ;

La norme SQL2 définit aussi l’équi-jointure naturelle, joignant les 2 tables sur l’ensemble des attributs qu’elles ont en commun, mais en ne gardant qu’une seule colonne pour chaque attribut joint, contrairement aux 2 expressions précédentes :

Utilisation de NATURAL JOIN
SELECT *  FROM table1 NATURAL JOIN table2 ;

Il est aussi possible de restreindre (ou préciser) le ou les attributs de jointure avec USING :

SELECT * FROM table1 INNER JOIN table2 USING (attribut1) ;

NATURAL JOIN et USING ne sont pas supportés par tous les SGBDR.

  • En SQL2, outre la jointure classique (dite jointure interne), apparaissent les jointures externes :

    • externes Gauche : LEFT OUTER JOIN

    • externes Droite : RIGHT OUTER JOIN

Dans le cas d’une jointure externe gauche A→B, toute les lignes de la table A sont incluses même s’il ne leur correspond pas de ligne dans la table B.

Sur l’exemple précédent :

Utilisation de LEFT OUTER JOIN
SELECT * FROM Produit A LEFT OUTER JOIN Détail_Commande B ON A.CodePrd=B.CodePrd ;

Le résultat renvoyé est le suivant :

Table 9. Résultat

A.CodePrd

Libellé

Prix unitaire

N°cde

B.CodePrd

quantité

590A

HD 1,6 Go

1615

97001

590A

2

588J

Scanner HP

1700

NULL

NULL

NULL

515J

LBP 660

1820

97002

515J

1

515J

LBP 660

1820

97003

515J

3

Tous les produits apparaissent même si certains n’ont pas fait l’objet de commande (exemple : 588J). Les colonnes manquantes sont alors complétées par des valeurs NULL.

3.3.4. Exercices w3school

Faire les 3 exercices SQL JOIN :

3.3.5. COLIBRI Strasbourg – Cours Libres Interactifs : Les jointures

Faire les 5 exercices interactifs de la partie Les jointures : afficher des colonnes depuis plusieurs tables :

3.4. # Exercices de synthèse #

Faire les 6 exercices Les restrictions ou sélectionner les lignes à afficher COLIBRI Strasbourg – Cours Libres Interactifs :

3.5. Opération d’insertion de données

L’insertion de données dans une table s’effectue à l’aide de la commande INSERT INTO.

Cette commande permet au choix d’inclure une seule ligne à la base existante ou plusieurs lignes d’un coup.

3.5.1. Insertion d’une ligne à la fois

Pour insérer des données dans une base, il y a 2 syntaxes principales :

  • Insérer une ligne en indiquant les informations pour chaque colonne existante (en respectant l’ordre)

  • Insérer une ligne en spécifiant les colonnes que vous souhaiter compléter. Il est possible d’insérer une ligne renseignant seulement une partie des colonnes

Insérer une ligne en spécifiant toutes les colonnes

La syntaxe pour remplir une ligne avec cette méthode est la suivante :

INSERT INTO table VALUES ('valeur 1', 'valeur 2', ...)

Cette syntaxe possède les avantages et inconvénients suivants :

  • Obliger de remplir toutes les données, tout en respectant l’ordre des colonnes

  • Il n’y a pas le nom de colonne, donc les fautes de frappe sont limitées. Par ailleurs, les colonnes peuvent être renommées sans avoir à changer la requête

  • L’ordre des colonnes doit resté identique sinon certaines valeurs prennent le risque d’être complétée dans la mauvaise colonne

Insérer une ligne en spécifiant seulement les colonnes souhaitées

Cette deuxième solution est très similaire, excepté qu’il faut indiquer le nom des colonnes avant VALUES.

La syntaxe est la suivante :

INSERT INTO table (nom_colonne_1, nom_colonne_2, ...
 VALUES ('valeur 1', 'valeur 2', ...)
A noter

Il est possible de ne pas renseigner toutes les colonnes. De plus, l’ordre des colonnes n’est pas important.

3.5.2. Insertion de plusieurs lignes à la fois

Il est possible d’ajouter plusieurs lignes à un tableau avec une seule requête. Pour ce faire, il convient d’utiliser la syntaxe suivante :

INSERT INTO client (prenom, nom, ville, age)
 VALUES
 ('Rébecca', 'Armand', 'Saint-Didier-des-Bois', 24),
 ('Aimée', 'Hebert', 'Marigny-le-Châtel', 36),
 ('Marielle', 'Ribeiro', 'Maillères', 27),
 ('Hilaire', 'Savary', 'Conie-Molitard', 58);
A noter

Lorsque le champ à remplir est de type VARCHAR ou TEXT il faut indiquer le texte entre guillemet simple. En revanche, lorsque la colonne est un numérique tel que INT ou BIGINT il n’y a pas besoin d’utiliser de guillemet, il suffit juste d’indiquer le nombre.

Un tel exemple sur une table existante, vide et dont les colonnes sont nommées prenom, nom, ville, age va créer le tableau suivant :

id prenom nom ville age

1

Rébecca

Armand

Saint-Didier-des-Bois

24

2

Aimée

Hebert

Marigny-le-Châtel

36

3

Marielle

Ribeiro

Maillères

27

4

Hilaire

Savary

Conie-Molitard

58

3.5.3. Exercices w3school

Faire l’exercice SQL INSERT :

3.5.4. COLIBRI Strasbourg – Cours Libres Interactifs : INSERT

Faire les 3 exercices interactifs de la partie Insérer de nouvelles lignes dans une table :

3.6. Opération de modification des données

La commande UPDATE permet d’effectuer des modifications sur des lignes existantes.

Très souvent cette commande est utilisée avec WHERE pour spécifier sur quelles lignes doivent porter la ou les modifications.

3.6.1. Syntaxe

La syntaxe basique d’une requête utilisant UPDATE est la suivante :

UPDATE table
SET nom_colonne_1 = 'nouvelle valeur'
WHERE condition
  • Cette syntaxe permet d’attribuer une nouvelle valeur à la colonne nom_colonne_1 pour les lignes qui respectent la condition stipulée avec WHERE.

  • Il est aussi possible d’attribuer la même valeur à la colonne nom_colonne_1 pour toutes les lignes d’une table si la condition WHERE n’était pas utilisée.

  • Pour spécifier en une seule fois plusieurs modification, il faut séparer les attributions de valeur par des virgules. Ainsi la syntaxe deviendrait la suivante :

UPDATE table
SET colonne_1 = 'valeur 1', colonne_2 = 'valeur 2', colonne_3 = 'valeur 3'
WHERE condition

3.6.2. Exemple

Imaginons une table client qui présente les coordonnées de clients.

Table “client” :

id nom rue ville code_postal pays

1

Chantal

12 Avenue du Petit Trianon

Puteaux

92800

France

2

Pierre

18 Rue de l’Allier

Ponthion

51300

France

3

Romain

3 Chemin du Chiron

Trévérien

35190

France

3.6.3. Modifier une ligne

Pour modifier l’adresse du client Pierre, il est possible d’utiliser la requête SQL suivante :

UPDATE client
SET rue = '49 Rue Ameline',
  ville = 'Saint-Eustache-la-Forêt',
  code_postal = '76210'
WHERE id = 2

Cette requête sert à définir les colonnes :

  • rue : 49 Rue Ameline,

  • ville : Saint-Eustache-la-Forêt

  • code postal : 76210

uniquement pour ligne où l’identifiant est égal à 2.

Résultats :

id

nom

rue

ville

code_postal

pays

1

Chantal

12 Avenue du Petit Trianon

Puteaux

92800

France

2

Pierre

49 Rue Ameline

Saint-Eustache-la-Forêt

76210

France

3

Romain

3 Chemin du Chiron

Trévérien

35190

France

3.6.4. Modifier toutes les lignes

Il est possible d’effectuer une modification sur toutes les lignes en omettant d’utiliser une clause conditionnelle. Il est par exemple possible de mettre la valeur FRANCE dans la colonne pays pour toutes les lignes de la table, grâce à la requête SQL ci-dessous.

UPDATE client
SET pays = 'FRANCE'

Résultats :

id nom rue ville code_postal pays

1

Chantal

12 Avenue du Petit Trianon

Puteaux

92800

FRANCE

2

Pierre

49 Rue Ameline

Saint-Eustache-la-Forêt

76210

FRANCE

3

Romain

3 Chemin du Chiron

Trévérien

35190

FRANCE

3.6.5. Exercices w3school

Faire les 3 exercices SQL UPDATE :

3.6.6. COLIBRI Strasbourg – Cours Libres Interactifs : UPDATE

Faire les 3 exercices interactifs de la partie Modifier des données de la table :

3.7. Opération de suppression des données

La commande DELETE en SQL permet de supprimer des lignes dans une table. En utilisant cette commande associé à WHERE il est possible de sélectionner les lignes concernées qui seront supprimées.

Avant d’essayer de supprimer des lignes, il est recommandé d’effectuer une sauvegarde de la base de données, ou tout du moins de la table concernée par la suppression. Ainsi, s’il y a une mauvaise manipulation il est toujours possible de restaurer les données.

3.7.1. Syntaxe

La syntaxe pour supprimer des lignes est la suivante :

DELETE FROM `table` WHERE condition

s’il n’y a pas de condition WHERE alors toutes les lignes seront supprimées et la table sera alors vide.

3.7.2. Exemple

Imaginons une table utilisateur qui contient des informations sur les utilisateurs d’une application.

Table 10. Table utilisateur
id nom prenom date_inscription

1

Bazin

Daniel

2012-02-13

2

Favre

Constantin

2012-04-03

3

Clerc

Guillaume

2012-04-12

4

Ricard

Rosemonde

2012-06-24

5

Martin

Natalie

2012-07-02

Supprimer une ligne

Il est possible de supprimer une ligne en effectuant la requête SQL suivante :

DELETE FROM `utilisateur` WHERE `id` = 1

Une fois cette requête effectuée, la table contiendra les données suivantes :

id nom prenom date_inscription

2

Favre

Constantin

2012-04-03

3

Clerc

Guillaume

2012-04-12

4

Ricard

Rosemonde

2012-06-24

5

Martin

Natalie

2012-07-02

3.7.3. Supprimer plusieurs lignes

Si l’ont souhaite supprimer les utilisateurs qui se sont inscrit avant le 10/04/2012, il va falloir effectuer la requête suivante :

DELETE FROM `utilisateur` WHERE `date_inscription` < '2012-04-10'

La requête permettra alors de supprimer les utilisateurs Daniel et Constantin. La table contiendra alors les données suivantes :

id nom prenom date_inscription

3

Clerc

Guillaume

2012-04-12

4

Ricard

Rosemonde

2012-06-24

5

Martin

Natalie

2012-07-02

Il ne faut pas oublier qu’il est possible d’utiliser d’autres conditions pour sélectionner les lignes à supprimer.

3.7.4. Supprimer toutes les données

Pour supprimer toutes les lignes d’une table il convient d’utiliser la commande DELETE sans utiliser de clause conditionnelle.

DELETE FROM `utilisateur`

3.7.5. Supprimer toutes les données : DELETE ou TRUNCATE

Pour supprimer toutes les lignes d’une table, il est aussi possible d’utiliser la commande TRUNCATE, de la façon suivante :

TRUNCATE TABLE `utilisateur`

Cette requête est similaire. La différence majeure étant que la commande TRUNCATE va ré-initialiser l’auto-incrémentation s’il y en a un. Tandis que la commande DELETE ne ré-initialise pas l’auto-incrément.

3.7.6. Exercices w3school

Faire les 2 exercices SQL DELETE :

3.7.7. COLIBRI Strasbourg – Cours Libres Interactifs : DELETE

Faire les 3 exercices interactifs de la partie Effacer les données de la base :

Pour supprimer les données d’une table dont une ou plusieurs colonnes sont des clés étrangères d’une autre table, il est nécessaire de procéder à la suppression des références à ces données dans les autres tables au préalable.

COUREURS (NuméroCoureur, NomCoureur, CodeEquipe*, CodePays*)

participe (NuméroCoureur*, NuméroEtape*, TempsRéalisé)

DELETE FROM COUREURS;

Une erreur d'exécution a été relevée.
FOREIGN KEY constraint failed

Un ou plusieurs coureurs apparaissent dans la table participe via la clé étrangère NuméroCoureur. La suppression du coureur provoquerait l’apparition d’un enregistrement orphelin et ne doit pas pouvoir être faite. Il faut donc supprimer les références aux coureurs à supprimer dans la table participe préalablement à leur suppression dans la table COUREURS :

DELETE FROM participe;
DELETE FROM COUREURS;

4. Des vidéos pour réviser

5. Pour s’entrainer