26 - SQL : DELETE UPDATE INSERT

⏪

⏬

⏩

1 - Principe de la jointure

2 - DELETE

3 - INSERT

4 - UPDATE

5 - Musiques

6 - FAQ

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

Logiciel nécessaire : DB Brownser

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 :

On retiendra simplement la structure de base à base de

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° Ouvrir le fichier SQlite dans DBBrowner. 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 titre.primary_title
FROM titre
WHERE titre.runtime_minutes < 55

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

1
2
3

SELECT primary_title
FROM titre
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.

Commande DELETE

Cette commande permet de supprimer 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 d'en récupérer certaines valeurs d'attributs.

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. Il convient de toujours être attentif et de vérifier son code avant exécution.

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° Sélectionner les titres dont la durée est inférieure à 45 minutes. Supprimer ensuite ces entrées avec une autre commande.

Combien de n-uplets supprimés ?

Correction ?

...CORRECTION...

1
2
3

SELECT primary_title
FROM titre
WHERE runtime_minutes < 45 ;

1
2
3

DELETE
FROM titre
WHERE runtime_minutes < 45;

Le nombre de suppression est indiqué dans la réponse du logiciel.

03°Observer les n-uplets qui n'ont maintenant plus trop de sens dans la relation emploi puisqu'ils font référence à un film qui n'existe plus dans la base de données.

1
2
3

SELECT *
FROM emploi
WHERE emploi.tconst NOT IN (SELECT titre.tconst FROM titre);

04° Sélectionner puis supprimer les personnes dont on ne connaît ni la date de naissance ni la date de décès.

Rappel : cela veut dire dans cette table que les valeurs sont NULL.

Attention :

birth_year = NULL ne fonctionne pas : NULL veut dire absence d'informations et ne peut pas être testé avec = par volonté. Il faut taper birth_year IS NULL.

Correction ?

...CORRECTION...

1
2
3

SELECT *
FROM personne
WHERE birth_year IS NULL AND death_year IS NULL;

1
2
3

DELETE
FROM personne
WHERE birth_year IS NULL AND death_year IS NULL;

Contrainte d'intégrité de REFERENCE et DELETE

Attention, détruire des n-uplets peut poser problème si leur valeur de clé primaire est utilisée par une clé étrangère d'une autre relation.

Dans ce cas, il est possible après suppression qu'une clé étrangère fasse référence à un n-uplet qui n'existe plus.

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

Refuser la suppression et demander d'abord la suppression des n-uplets contenant des clés secondaires vers les éléments qu'on veut supprimer
Demander l'autorisation de 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)

3 - Commande INSERT

⇩ ⇧ ⤊

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

INSERT

1
2
3

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

On remarquera qu'on doit ici donner les valeurs de la clé primaire nconst à la main.

Si nous avions pris comme clé primaire l'attribut id dont le domaine est un nombre entier, nous aurions pu demander à notre Système de Gestion de Base de Données (SGBD) d'incrémenter automatiquement la valeur de la clé. Vous pourrez tester cette fonctionnalité lors du mini-projet de création de base de données. Mais ici, nous allons devoir le faire à la main puisque la clé primaire est une chaîne de caractères.

Si on rentre toutes les infos, on peut se passer de donner les attributs sur la première ligne :

1
2
3

INSERT INTO personne
VALUES
(NULL, 'nm0042', 'Douglas Admans', 1952, 2021, 'writer', NULL);

05° Aller rechercher la dernière valeur des attributs id et nconst dans la relation personne. Utiliser ensuite les instructions précédentes (ou d'autres) pour rajouter des n-uplets dans la relation personne.

Un exemple en image (attention votre numéro id devrait être bien plus grand):

06° Si vous ne l'avez pas encore fait, rajoutez vous dans la table. Pour votre id et votre nconst, il faudra tenter de trouver quelque chose qui n'est pas encore choisi : je vous rappelle que j'ai utilisé les données du site Web imdb.com qui a son propre système de gestion des clés.

07° 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 personne.name LIKE '%Bruce Willis%'`

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

Correction ?

...CORRECTION...

1
2
3

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

08° 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 tconst.

09° Effectuer la recherche d'un film présent dans titre dans lequel vous auriez aimé jouer. Noter sa valeur de clé primaire tconst.

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

11° Créer un nouveau n-uplet dans emploi en transmettant des valeurs de façon à ce que

la clé étrangère du film fasse référence à la valeur de la clé étrangère du film dans titre et
la clé primaire de la personne ayant occupé cette emploi fasse référence à la clé primaire de votre n-uplet dans personne.

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

Il faut faire attention au sens de l'insertion : on ne peut pas créer un nouvel n-uplet de emploi si le film ou l'acteur correspond n'a 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 emploi.

4 - Commande UPDATE

⇩ ⇧ ⤊

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 job par le string '-'.

La syntaxe pour changer NULL en '-' :

1
2
3

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

La syntaxe pour changer NULL en '-' :

1
2
3

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

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

1
2
3

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

12° Remplacer les champs de l'attribut job contenant NULL par '-'. Observer la relation pour vérifier.

13° Modifier l'attribut known_for_titles de votre n-uplet dans personne en y plaçant un ou deux films célèbres dans lesquels vous auriez aimé jouer.

Et voilà, vous n'avez plus qu'à laisser traîner votre base de données modifiée et si quelqu'un la récupère, si cette personne s'y intéresse, si cette personne sait faire des requêtes SQL, si cette personne recherche des données sur vous et si elle y porte crédit, on pourrait peut-être finir par croire que vous avez participé aux films que vous avez rajouté.

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_de_sortie, fk_interprete)
une relation chanson pour les chansons (id, nom, durée, fk_interprete, fk_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.

14° 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. Inutile sur la clé primaire car elle doit être non NULL par définition même de la clé primaire.
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 et NULL peut être présent plusieurs fois. Une clé primaire est donc en quelque sorte Unique et Non NULL.

6 - FAQ

⇩ ⇧ ⤊

Comment supprimer les n-uplets qui contiennent des clés étrangères qui ne pointent vers rien de valide ?

Pour détruire les n-uplets qui contiennent des clés étrangères ne correspondant pas à une valeur d'une autre table :

Maintenant que nous avons détruit les titres datant d'avant 1990, nous avons certainement dans la relation emploi des références à certains titres que nous venons de supprimer. Si nous avions un système de gestion efficace, nous pourrions supprimer automatiquement ces n-uplets. Mais on peut aussi le faire à la main. En trois temps :

D'abord, il faut trouver les clés primaires disponibles pour titre :

`1`	`SELECT titre.tconst FROM titre`

Ensuite, on utilise cette réponse pour savoir si la clé étrangère d'un n-uplet fait bien référence à une vraie clé primaire :

`1`	`WHERE emploi.tconst NOT IN (SELECT titre.tconst FROM titre);`

On batît la commande de destruction autour de cela :

1
2
3

DELETE
FROM emploi
WHERE emploi.tconst NOT IN (SELECT titre.tconst FROM titre);

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

⏪

⏫

⏩