Données SQL Modif

Identification

Infoforall

28 - SQL : DELETE UPDATE INSERT


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.

1 - Description des 3 relations

Voici les relations avec lesquelles vous allez travailler :

Les liaisons entre les 3 relations

Remarque 1 : ce fichier ne contient que des films et ils datent tous de 1990 ou plus.

Remarque 2 : Les deux fiches titre et emploi possèdent en réalité deux valeurs qui pourraient faire office de clé primaire : l'attribut id_ifa (en entier) et l'attribut id (une chaîne de caractères).

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

On retiendra simplement la structure de base à base de

1 2 3 4 5
SELECT * FROM table_a JOIN table_b ON table_a.cle_etrangere = table_b.cle_primaire WHERE condition(s) ORDER BY expression
  1. SELECT : on choisit les attributs qu'on veut réellement récupérer dans les nuplets obtenus par FROM WHERE. On peut également faire des calculs (agrégation) sur ces nuplets 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 nuplets obtenus.
  2. FROM : on indique la table dans laquelle on veut récupèrer des nuplets
  3. 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
  4. WHERE : on fournit les conditions à respecter pour qu'un nuplet soit sélectionné. On pourra utiliser AND, OR, LIKE...
  5. 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 à 15 minutes.

...CORRECTION...

1 2 3
SELECT titre.primary_title FROM titre WHERE titre.runtime_minutes < 15

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

1 2 3
SELECT primary_title FROM titre WHERE runtime_minutes < 15

2 - DELETE

Commençons par voir comment supprimer certains nuplets d'une table. Après tout, je n'ai pas fait disparaître les films datant des années antérieures à 1990 par magie ou en les supprimer une à une à la main.

Commande DELETE

Cette commande permet de supprimer un ou plusieurs nuplets à l'intérieur d'une relation.

Elle se structure comme un SELECT si ce n'est qu'elle détruit les nuplets sélectionnés plutôt que d'en recupérer certaines valeurs d'attributs.

Pour détruire tous les nuplets d'une relation :

1 2
DELETE FROM titre;

Pour détruire les nuplets 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 atentif et de vérifier son code avant exécution.

Deux manières de procéder :

  1. On fait une copie de la table avant de supprimer certains nuplets ? C'est sécurisé mais cela occupe de la mémoire temporairement.
  2. 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 à 15 minutes. Supprimer ensuite ces entrées avec une autre commande.

Combien de nuplets supprimés ?

...CORRECTION...

1 2 3
SELECT primary_title FROM titre WHERE runtime_minutes;
1 2 3
DELETE FROM titre WHERE runtime_minutes;

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

03°Observer les nuplets 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.id_titre NOT IN (SELECT titre.id 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...

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;

3 - Commande INSERT

Voyons maintenant comment insérer de nouveaux nuplets dans une relation.

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

On remarquera qu'on ne donne pas la première valeur : je l'ai ici déclaré comme clé primaire auto-incrémentée : à chaque nouvelle entrée, c'est la base de données elle-même qui lui donne un numéro pour être certain qu'il soit unique.

Si on rentre toutes les infos, on peut se passer de donner les attributs :

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

05° Utiliser les deux instructions précédentes (ou d'autres) pour rajouter des nuplets dans la relation personne.

Visualiser ensuite le bas de votre tableau pour voir les deux éléments que vous venez de rajouter. Observer leurs numéros id_ifa qui devraient avoir augmenter de 1 à chaque fois grace à AUTO INCREMENT.

Le résultat en image :

06° Si vous ne l'avez pas encore fait, rajoutez vous dans la table. Pour votre id texte, 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. 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.

Chercher jusqu'à tomber sur une personne manquante.

...CORRECTION...

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

08° 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 et de trouver sa clé primaire texte.

09° Effectuer cette recherche et noter le résultat.

10° Rechercher votre propre valeur de clé primaire dans la relation personne.

11° Ensuite, vous créez un nouveau nuplet emploi faisant référence au film et à votre propre personne.

4 - Commande UPDATE

UPDATE

Cette commande permet de modifier un nuplet 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 IS '-' ;

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 IS '-' ;

12°Remplacer les champs de l'attribut job contenant NULL par '-'. Observer la table.

13° Modifier votre champ "known_for_titles" en vous associant un ou deux films célébres.

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_interprète et 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) 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.

6 - FAQ

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

Pour détruire les nuplets 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 nuplets. Mais on peut aussi le faire à la main. En trois temps :

  1. D'abord, il faut trouver les clés primaires disponibles pour titre :
  2. 1
    SELECT titre.id FROM titre
  3. Ensuite, on utilise cette réponse pour savoir si la clé étrangère d'un nuplet fait bien référence à une vraie clé primaire :
  4. 1
    WHERE emploi.id_titre NOT IN (SELECT titre.id FROM titre);
  5. On batît la commande de destruction autour de cela :
  6. 1 2 3
    DELETE FROM emploi WHERE emploi.id_titre NOT IN (SELECT titre.id FROM titre);

Pour formuler une requête d'informations :

1 2 3 4 5
SELECT * FROM table_a JOIN table_b ON table_a.cle_etrangere = table_b.cle_primaire WHERE condition(s) ORDER BY expression
  1. SELECT : on choisit les attributs qu'on veut réellement récupérer dans les nuplets obtenus par FROM WHERE. On peut également faire des calculs (agrégation) sur ces nuplets 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 nuplets obtenus.
  2. FROM : on indique la table dans laquelle on veut récupèrer des nuplets
  3. 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
  4. WHERE : on fournit les conditions à respecter pour qu'un nuplet soit sélectionné. On pourra utiliser AND, OR, LIKE...
  5. ORDER BY : on fournit l'attribut de tri avec un suffixe ASC ou DESC

Pour détruire tous les nuplets d'une relation :

1 2
DELETE FROM table;

Cela ne détruit pas la table : on supprime juste les nuplets.

Pour détruire les nuplets d'une relation répondant à certaines conditions :

1 2 3
DELETE FROM titre WHERE start_year < 1990;

Pour insérer un nouveau nuplet dans une table :

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

Si on fournit toutes les valeurs, autant faire cela (mais il faut les donner dans l'ordre) :

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

Pour modifier (mettre à jour) certaines valeurs d'un ou plusieurs nuplets :

1 2 3
UPDATE emploi SET job = '-' WHERE job IS NULL ;

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