26 - SQL : DELETE UPDATE INSERT

⏪

⏬

⏩

1 - Principe de la jointure

2 - DELETE

3 - UPDATE

4 - INSERT

5 - Musiques

6 - FAQ

Prerequis : les deux activités précédentes sur SQL.

Logiciel nécessaire : DB Browser

Fichier SQLite : la base de données est à télécharger. Si vous n'avez pas de compte, vous pouvez me contacter avec le bouton en bas de page.

Documents de cours : open document ou pdf

MODE COURS (cacher les questions)

1 - Description des 3 relations

⇩ ⇧ ⤊

Voici les relations avec lesquelles vous allez travailler :

Rappel de la structure des requêtes vues jusqu'à présent :

SELECT *
FROM table_a
JOIN table_b ON table_a.cle_etrangere = table_b.cle_primaire
WHERE condition(s)
ORDER BY expression

SELECT : on choisit les attributs qu'on veut projeter dans les n-uplets obtenus par FROM WHERE. On peut également faire des calculs (agrégation) sur ces n-uplets avec MIN, MAX, AVG, SUM ou COUNT. L'utilisation de l'étoile * veut dire de récupérer l'intégralité des attributs disponibles sur les n-uplets obtenus.
FROM : on indique la table dans laquelle on veut récupérer des n-uplets
JOIN ON : on indique la table qu'on veut fusionner avec la précédente et on précise quelle clé primaire comparer à quelle clé étrangère
WHERE : on fournit les conditions à respecter pour qu'un n-uplet soit sélectionné. On pourra utiliser AND, OR, LIKE...
ORDER BY : on fournit l'attribut de tri avec un suffixe ASC ou DESC

01-A° Copier votre fichier sqlite : nous allons faire de grandes modifications sur le fichier, notamment en supprimant des enregistrements. Sans sauvegarde de l'état initial, vous n'aurez plus tous les enregistrements si vous modifiez directement votre original.

01-B° Ouvrir le deuxième fichier SQlite dans DB Browser. Effectuer une requête pour obtenir tous les titres de films dont la durée est inférieure à 55 minutes.

Correction ?

...CORRECTION...

1
2
3

SELECT title.primary_title
FROM title
WHERE title.runtime_minutes < 55

Ou, comme on ne travaille qu'avec une table :

1
2
3

SELECT primary_title
FROM title
WHERE runtime_minutes < 55

2 - DELETE

⇩ ⇧ ⤊

Commençons par voir comment supprimer certains n-uplets d'une relation. Après tout, je n'ai pas fait disparaître les films durant moins de 35 minutes par magie ou en les supprimant un à un à la main.

2.1 Commande DELETE

Cette commande permet de supprimer définitivement un ou plusieurs n-uplets à l'intérieur d'une relation.

Elle se structure comme un SELECT si ce n'est qu'elle détruit les n-uplets sélectionnés plutôt que de les projeter.

Pour détruire tous les n-uplets d'une relation :

1
2

DELETE
FROM titre;

Pour détruire les n-uplets d'une relation répondant à certaines conditions :

1
2
3

DELETE
FROM titre
WHERE start_year < 1990;

C'est de cette façon que nous allons agir aujourd'hui.

Attention : les suppressions sont dangereuses car définitives.

Il convient de toujours être attentif et de vérifier ce qu'on supprime.

Deux manières de procéder :

On fait une copie de la table avant de supprimer certains n-uplets ? C'est sécurisé mais cela occupe de la mémoire temporairement.
On fait d'abord une lecture sur la sélection pour voir si c'est la bonne. Impossible de vérifier si le nombre de réponses est grand.

Bref : il faut faire attention et vérifier deux fois sa commande avant de valider.

02° Projeter les titres dont la durée est inférieure à 45 minutes. Supprimer ensuite ces entrées avec une autre commande (attention, cela peut être long, n'attendez pas nécessairement une réponse immédiate).

Qu'est-ce qui a provoqué une erreur ? Combien de n-uplets supprimés ?

Correction ?

...CORRECTION...

1
2
3

SELECT primary_title
FROM title
WHERE runtime_minutes < 45 ;

1
2
3

DELETE
FROM title
WHERE runtime_minutes < 45;

Le nombre de suppressions est indiqué dans la réponse du logiciel si la suppression fonctionne. Ici : on obtient ce message : .


L'exécution s'est terminée avec des erreurs.
Résultat : FOREIGN KEY constraint failed
À la ligne 1 :
DELETE
FROM title
WHERE runtime_minutes < 45;

On parle donc de clé étrangère. Pourquoi ? Tout simplement car le SGBD refuse de supprimer les entrées de title car certains emplois de job pointent sur eux : si on supprime ces titres, on obtiendrait des jobs avec une clé étrangère idtitle pointant un titre inexistant. Le SGBD refuse donc car cela ne respecterait plus la contrainte de REFERENCE.

2.2 Contrainte d'intégrité de REFERENCE et DELETE

Détruire un n-uplet peut poser problème si il existe une clé étrangère qui référence cette clé primaire. Si l'un des éléments supprimés était la cible d'une clé étrangère, le SGBD va refuser la modification : la contrainte de REFERENCE ne serait sinon plus valide pour cette clé étrangère.

Pour éviter ce problème, plusieurs solutions dans les SGBD. Par exemple :

Refuser la suppression. Il faudra d'abord supprimer les clés étrangères qui posent problèmes, puis supprimer les éléments voulus.
Supprimer aussi les n-uplets des autres tables qui possèdent une clé étrangère vers un n-uplet qu'on veut faire disparaître (pratique pour faire disparaître les produits fournis par un fournisseur qui vient de faire faillite).

03° Pour supprimer les productions de moins de 45 minutes, nous allons commencer par trouver les jobs qui vont référence à une telle production. Le plus facile est de faire une requête imbriquée.

SELECT *
FROM job
WHERE job.idtitle IN (
    SELECT id
    FROM title
    WHERE runtime_minutes < 45)
;

Une fois qu'on a vérifié que la projection correspond bien à ce qu'on veut, il suffit de supprimer ces n-uplets.

DELETE
FROM job
WHERE job.idtitle IN (
    SELECT id
    FROM title
    WHERE runtime_minutes < 45)
;

Vous devriez constater que vous avez bien supprimé environ 56000 jobs.

Question : comment fonctionne cette requête en deux étapes : par quelle requête commence le SGBD ?

Correction ?

...CORRECTION...

DELETE
FROM job
WHERE job.idtitle IN (
    SELECT id
    FROM title
    WHERE runtime_minutes < 45)
;

On commence par faire la requête en jaune : elle permet d'obtenir l'ensemble des id des productions de moins de 45 minutes.

Ensuite, on finit la requête, c'est un peu comme si nous avions ceci :

DELETE
FROM job
WHERE job.idtitle IN ensemble_des_id_des_films_a_supprimer
;

04° Maintenant que nous avons supprimé les jobs dont les clés étrangères pointaient les enregistrements que vous voulions supprimer, cela va nous permettre de supprimer les productions de moins de 45 minutes.

Finissez le travail en demander la suppression de ces prouctions.

Correction ?

...CORRECTION...

1
2
3

SELECT *
FROM title
WHERE runtime_minutes < 45;

Puis :

1
2
3

DELETE
FROM title
WHERE runtime_minutes < 45;

Enfin (cela ne sert à rien puisque cela a forcément fonctionné si la BDD le dit), on peut vérifier que les enregistreements n'existent plus :<:

1
2
3

SELECT *
FROM title
WHERE runtime_minutes < 45;

3 - Commande UPDATE

⇩ ⇧ ⤊

3.1 UPDATE

Cette commande permet de modifier un n-uplet qui existe déjà.

Imaginons que vous vouliez remplacer toutes les valeurs NULL dans l'attribut precision de la table job par le string '-'.

NULL en '-' :

1
2
3

UPDATE job
SET precision = '-'
WHERE precision IS NULL ;

'-' en NULL:

1
2
3

UPDATE job
SET precision = NULL
WHERE precision = '-' ;

On notera qu'on peut également mettre à jour plusieurs champs à la fois en les séparant par des virgules sur la ligne SET.

Imaginons qu'on désire supprimer toutes les références au métier de producteur, on peut alors faire ceci :

1
2
3

UPDATE emploi
SET job = NULL, category = NULL
WHERE category = 'producer' ;

05° Remplacer les champs de l'attribut death_year contenant NULL par '-' dans la table person. Observer la relation pour vérifier.

06° Modifier l'attribut category de job de façon à'un film dont vous connaissez le nom en anglais et en français : mettre désormais le titre en anglais suivi d'un ; et du titre en français.

4 - Commande INSERT

⇩ ⇧ ⤊

Voyons maintenant comment insérer de nouveaux n-uplets dans une relation.

4.1 INSERT

1
2
3

INSERT INTO person (id, name, birth_year, death_year, primary_profession)
VALUES
(NULL, 'James Bond', 1930, 2020, 'agent secret');

On remarquera qu'on ne donne pas la valeur de clé primaire id à la main : elle a été définie avec AUTOINCREMENT et c'est donc le SGBD qui va s'en charger.

Remarque : si on rentre tous les attributs, on peut se passer de donner les attributs sur la première ligne :

1
2
3

INSERT INTO person
VALUES
(NULL, 'James Bond', 1930, 2020, 'agent secret');

07° Parcourir les données pour rechercher la dernière valeur des attributs id dans la relation person. Utiliser ensuite les instructions précédentes (ou d'autres) pour rajouter des n-uplets dans la relation person.

Un exemple en image :

Correction ?

...CORRECTION...

INSERT INTO person
VALUES
(NULL, 'James Bond', 1930, 2020, 'agent secret');
 
INSERT INTO person
VALUES
(NULL, 'Douglas Adams', 1952, 2001, 'writer');

4.2 INSERT de plusieurs n-uplets

Plutôt que de faire plusieurs insertions de plusieurs n-uplets, on peut faure une seule insertions en séparant les n-uplets par des virgules :

INSERT INTO person
VALUES
(NULL, 'Alice', 2004, NULL, NULL),
(NULL, 'Bob', 2005, NULL, NULL);

4.3 INSERT sans tout forunir

Si vous ne fournissez pas certaines valeurs, elles seront définies automatiquement sur NULL. On peut alors plutôt juste donner le nom des attributs qy'on fournit.

INSERT INTO person (name, birth_year)
VALUES
('Charlie', 2003),
('Dana', 2005);

08° Si vous ne l'avez pas encore fait, rajoutez vous dans la table.

09° Sélectionner un acteur ou une actrice de votre choix, quelqu'un dont vous appréciez les films par exemple. Commencer par chercher si il/elle est déjà présent.e dans la base de données. Une recherche sur son nom avec un LIKE s'impose. Rappel :

`5`	`WHERE person.name LIKE '%Bruce Willis%'`

Si l'entrée est absente, il vous reste à la rajouter.

Correction ?

...CORRECTION...

1
2
3

SELECT *
FROM person
WHERE person.name LIKE "%Bob l'Eponge"

10° De la même façon, faire une recherche sur votre nom de famille pour voir si certains producteurs ou acteurs portent votre nom.

Et si vous tentiez de faire carrière au cinéma ?

Pour cela, rien de plus simple : il suffit de sélectionner un film dans lequel vous auriez voulu jouer et de chercher sa clé primaire id.

11° Effectuer la recherche d'un film ou d'une série de title dans lequel vous auriez aimé jouer. Noter sa valeur de clé primaire id.

12° Rechercher avec un SELECT FROM WHERE votre propre valeur de clé primaire id dans la relation personne.

13° Rechercher avec un SELECT FROM WHERE les emplois déjà présents pour ce film. Cela vous permettra de voir quels sont les numéros ordering déjà utilisés pour cette production.

14° Créer un nouveau n-uplet dans job en transmettant des valeurs permettant à (idtitle, ordering, idperson) d'être unique puisque c'est la clé primaire de cette table.

la clé étrangère idtitle doit être égal à l'id du film visé et
la clé étrangère idperson doit être égal à votre propre valeur de clé primaire.

4.4 Contraintes d'intégrité d'UNICITE et INSERT

On ne peut pas créer un nouvel n-uplet si on force le SGBD a prendre comme numéro de clé une clé qui existe déjà.

C'est pour cela que le plus simple est souvent de laisser le SGBD gérer. Mais cela peut créer des doublons : un même client peut très bien se créer plusieurs comptes et avoir plusieurs références de cartes de fidélité mais pour autant que les cartes ne se cumulent entre elles.

4.5 Contraintes d'intégrité de REFERENCE et INSERT

On ne peut pas créer un nouvel n-uplet de job si le film ou l'acteur correspond n'ont pas encore été créé.

Il faut donc d'abord créer le film et l'acteur et on pourra ENSUITE créer le nouvel n-uplet dans job.

5 - Musiques

⇩ ⇧ ⤊

Passons à la création et à la réflexion.

Nous voudrions réaliser une base de données regroupant des titres de musiques.

Il faudrait

une relation interprete pour les groupes ou interprètes (id, nom, genre, date_de_formation...)
une relation album pour les albums (id, nom, date_sortie, #id_interprete)
une relation chanson pour les chansons (id, nom, durée, #id_interprete, #id_album)

Le meilleur moyen de travailler en groupe serait d'avoir accès à un Système de Gestion de Base de Données (SGBD) avec interface Web que nous verrons un peu plus tard.

15° On vous demande de récolter les données de 10 chansons et de les insérer dans une base de données.

Utiliser l'interface : onglet Structure de données puis Créer ou modifier une table. Lors de la déclaration de vos clés primaires pensez à sélectionner CP (pour clé primaire) et IA (pour Auto-increment).

Consignes :

Il faut commencer par créer les n-uplets de la relation interprete puisque ces n-uplets ne contiennent pas de clés étrangères faisant référence à d'autres relations.
Ensuite, il faudra passer à la relation album en fournissant une valeur de clé étrangère faisant référence à un interprète déjà enregistré.
Enfin, vous pourrez remplir la relation chanson en remplissant les deux clés étrangères par des valeurs de clé primaire correspondant à l'interprète et à l'album.

Quelques remarques sur les autres choix :

NULL veut dire en SQL : valeur non connue ou valeur non rentrée car non connue avec certitude
NN pour Non NULL : à sélectionner sur les attributs qui doivent impérativement être remplis dès la création du n-uplet.
U pour UNIQUE : à sélectionner lorsque vous voulez qu'une valeur stockée dans cet attribut ne puisse apparaître qu'une seule fois dans la colonne. La différence avec clé primaire ? La valeur NULL est acceptée également.

6 - FAQ

⇩ ⇧ ⤊

Rien pour le moment

Activité publiée le 22 01 2021
Dernière modification : 24 01 2021
Auteur : ows. h.

⏪

⏫

⏩