Algèbre Relationnelle et langage SQL

http://webtic.free.fr/sql

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 :

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

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

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

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

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

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.5. Exercices w3school

Faire les exercices :

SQL SELECT
SQL WHERE
SQL ORDER BY

https://www.w3schools.com/sql/exercise.asp

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 :

https://colibri.unistra.fr/fr/course/practice/notions-de-base-en-sql/afficher-les-colonnes-d-une-table/1

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;

https://colibri.unistra.fr/fr/course/practice/notions-de-base-en-sql/trier-les-donnees/19

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 :

https://www.w3schools.com/sql/exercise.asp

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 :

https://colibri.unistra.fr/fr/course/practice/notions-de-base-en-sql/les-jointures/32

3.4. # Exercices de synthèse #

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

https://colibri.unistra.fr/fr/course/practice/notions-de-base-en-sql/les-restrictions/103

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

Référence : https://sql.sh/cours/insert-into

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 :

https://www.w3schools.com/sql/exercise.asp

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 :

https://colibri.unistra.fr/fr/course/practice/notions-de-base-en-sql/inserer-de-nouvelles-lignes/72

3.6. Opération de modification des données

Référence : https://sql.sh/cours/update

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” :

nom

rue

ville

code_postal

pays

Chantal

12 Avenue du Petit Trianon

Puteaux

92800

France

Pierre

18 Rue de l’Allier

Ponthion

51300

France

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 :

nom

rue

ville

code_postal

pays

Chantal

12 Avenue du Petit Trianon

Puteaux

92800

France

Pierre

49 Rue Ameline

Saint-Eustache-la-Forêt

76210

France

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 :

nom

rue

ville

code_postal

pays

Chantal

12 Avenue du Petit Trianon

Puteaux

92800

FRANCE

Pierre

49 Rue Ameline

Saint-Eustache-la-Forêt

76210

FRANCE

Romain

3 Chemin du Chiron

Trévérien

35190

FRANCE

3.6.5. Exercices w3school

Faire les 3 exercices SQL UPDATE :

https://www.w3schools.com/sql/exercise.asp

3.6.6. COLIBRI Strasbourg – Cours Libres Interactifs : UPDATE

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

https://colibri.unistra.fr/fr/course/practice/notions-de-base-en-sql/modifier-des-donnees-de-la-table/86

3.7. Opération de suppression des données

Référence : https://sql.sh/cours/delete

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 :

https://www.w3schools.com/sql/exercise.asp

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;

https://colibri.unistra.fr/fr/course/practice/notions-de-base-en-sql/effacer-les-donnees-de-la-base/79

4. Des vidéos pour réviser

5. Pour s’entrainer

http://chirouble.univ-lyon2.fr/~jdarmont/tutoriel-sql/
https://sqlbolt.com/lesson/select_queries_introduction (en englais)
https://sqlzoo.net/wiki/SQL_Tutorial (en englais)
https://www.hackerrank.com/domains/sql (en anglais, nécessite une connexion avec votre compte github ou google)

Algèbre Relationnelle et langage SQL

1. Introduction

2. Introduction au Modèle Relationnel

2.1. Les tables

2.2. Les colonnes

2.3. Clé primaire

2.4. Clés étrangères

2.5. Association

2.6. Le modèle logique ou schéma

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

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

3.1. Opération PROJECTION

3.1.1. Définition

3.1.2. Syntaxe SQL

3.1.3. Travail à faire

3.2. Opération SELECTION (ou RESTRICTION)

3.2.1. Définition

3.2.2. Syntaxe SQL

3.2.3. Travail à faire

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

3.2.5. Exercices w3school

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

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

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

3.3.1. Définition

3.3.2. Syntaxe SQL de base

3.3.3. Syntaxe SQL à partir de SQL2

3.3.4. Exercices w3school

3.3.5. COLIBRI Strasbourg – Cours Libres Interactifs : Les jointures

3.4. # Exercices de synthèse #

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

3.5.1. Insertion d’une ligne à la fois

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

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

3.5.2. Insertion de plusieurs lignes à la fois

3.5.3. Exercices w3school

3.5.4. COLIBRI Strasbourg – Cours Libres Interactifs : INSERT

3.6. Opération de modification des données

3.6.1. Syntaxe

3.6.2. Exemple

3.6.3. Modifier une ligne

3.6.4. Modifier toutes les lignes

3.6.5. Exercices w3school

3.6.6. COLIBRI Strasbourg – Cours Libres Interactifs : UPDATE

3.7. Opération de suppression des données

3.7.1. Syntaxe

3.7.2. Exemple

Supprimer une ligne

3.7.3. Supprimer plusieurs lignes

3.7.4. Supprimer toutes les données

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

3.7.6. Exercices w3school

3.7.7. COLIBRI Strasbourg – Cours Libres Interactifs : DELETE

4. Des vidéos pour réviser

5. Pour s’entrainer