TP2 : Écrire les requêtes basiques

Ouvrir la fenêtre de requêtes avec l'onglet SQL puis cocher « Conserver la boîte de requête ».

Question 1.1 : Créer une nouvelle base de données

Avec la requête suivante, créez la base de données platform_iot :

CREATE DATABASE IF NOT EXISTS `platform_iot` ;

1. Exécutez la requête.
   • Il faut cliquer sur le bouton Exécuter pour envoyer la requête au SGBD.
   • La réponse du SGBD sera observable dans la fenêtre principale de phpMyAdmin.
     En cas d'erreur, un message sera affiché avec une explication :
     
2. À gauche de la fenêtre principale de phpMyAdmin, vérifiez que la nouvelle base de données apparaît dans l'arborescence.

Consigne : avec l'éditeur de texte Notepad++, copiez toutes vos requêtes CREATE dans un fichier que vous nommerez iot_create.sql

Question 1.2 : Créer une nouvelle table

Nous allons créer la table user dans la base de données platform_iot.

Avec la requête suivante, créez la table user :

CREATE TABLE IF NOT EXISTS `platform_iot`.`user` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `firstname` VARCHAR(100) NOT NULL,
  `lastname` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = INNODB ;

1. Exécutez la requête.
2. Vérifiez qu'il n'y a pas de message d'erreur dans la fenêtre principale de phpMyAdmin.
3. Depuis la fenêtre principale de phpMyAdmin, vérifiez la structure de cette nouvelle table user.

Consigne : n'oubliez pas de copier votre requête CREATE dans le fichier iot_create.sql

Pour le nom de la table, il est possible d'éviter d'écrire platform_iot.user et d'écrire directement user, mais à condition d'avoir exécuté au préalable la requête suivante :
USE `platform_iot` ;

Question 1.3 : Créer la table service

De même, écrivez la requête pour créer la table service dans la base de données platform_iot.

1. Exécutez la requête.
2. Vérifiez qu'il n'y a pas de message d'erreur dans la fenêtre principale.
3. Depuis la fenêtre principale, vérifiez la structure de cette nouvelle table service.

Consigne : ajoutez votre requête CREATE au fichier iot_create.sql

Question 1.4 : Créer la table thing

En tenant compte des remarques ci-dessous, écrivez la requête pour créer la table thing dans la base de données platform_iot. Remarques :

• La clé primaire mac ne nécessite que 17 caractères.
• N'oubliez pas de déclarer que la clé étrangère id_user référence le champ id de la table user en ajoutant la ligne suivante dans votre requête de création :
  FOREIGN KEY (`id_user`) REFERENCES `user`(`id`) ON DELETE CASCADE ON UPDATE CASCADE

1. Exécutez la requête.
2. Vérifiez qu'il n'y a pas de message d'erreur dans la fenêtre principale.
3. Depuis la fenêtre principale de phpMyAdmin, vérifiez la structure de cette nouvelle table thing.

Consigne : ajoutez votre requête CREATE au fichier iot_create.sql

Question 1.5 : Créer la table subscribe

De même, écrivez la requête pour créer la table subscribe dans la base de données platform_iot. Remarques :

• N'oubliez pas de déclarer que la clé étrangère id_user référence le champ id de la table user.
• N'oubliez pas de déclarer que la clé étrangère id_service référence le champ id de la table service.
• Faîtes attention que la clé primaire couvre deux champs.

1. Exécutez la requête.
2. Vérifiez qu'il n'y a pas de message d'erreur dans la fenêtre principale.
3. Depuis la fenêtre principale de phpMyAdmin, vérifiez la structure de cette nouvelle table subscribe.

Consigne : ajoutez votre requête CREATE au le fichier iot_create.sql

Question 1.6 : Visualiser avec le concepteur

Vous avez créé une nouvelle base, quatre tables et trois relations de clés étrangères. Visualisez la structure résultante avec la vue « concepteur » :

1. Dans l'arborescence à gauche de la page principale de phpMyAdmin, cliquez sur la base de données platform_iot
2. Puis cliquez sur l'onglet Concepteur (ou retrouvez-le en déroulant l'onglet « plus »)
3. Vérifiez que les quatre tables, leurs champs et les relations sont corrects

   

4. Organisez visuellement cette vue en déplaçant les tables, puis conservez le placement en enregistrant le placement avec le bouton et en lui donnant un nom :

   

L'onglet concepteur est absent ? Le concepteur est rarement installé par défaut. Mais il a déjà été installé sur vos configurations ESTIA. Si tel n'est pas le cas, il est possible de l'installer sur votre serveur WAMP en suivant cette procédure.

1) Dans le répertoire d'installation de phpMyAdmin ouvrir le fichier nommé config.inc.php :
C:\wamp64\apps\phpmyadminX.X.XX\config.inc.php

2) puis ajouter les lignes suivantes juste avant la fin du fichier « ?> » :
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
$cfg['Servers'][$i]['relation'] = 'pma__relation';
$cfg['Servers'][$i]['table_info'] = 'pma__table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma__column_info';
$cfg['Servers'][$i]['history'] = 'pma__history';
$cfg['Servers'][$i]['designer_coords'] = 'pma__designer_coords';
$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';

3) Enregistrer les modifications. Depuis la racine de l'interface web de phpMyAdmin cliquer sur « Importer » et ouvrir le fichier :
C:\wamp64\apps\phpmyadminX.X.XX\examples\create_tables.sql

4) Redémarrer WAMP. Un nouvel onglet « concepteur » doit maintenant apparaître dans phpMyAdmin.

Consigne : toujours avec l'éditeur de texte Notepad++, copiez toutes vos requêtes INSERT dans un nouveau fichier que vous nommerez iot_feed.sql

Question 2.1 : Les utilisateurs

a) Pour insérer un premier utilisateur :

1. Exécutez la requête INSERT suivante :
   INSERT INTO `platform_iot`.`user` (`firstname`, `lastname`, `email`) VALUES ('Amaya', 'URSUYA', 'amaya@domain.com') ;
2. Vérifiez qu'il n'y a pas de message d'erreur dans la fenêtre principale.
3. Vérifier qu'une nouvelle ligne a bien été ajoutée à la table user en exécutant la requête suivante :
   SELECT * FROM `platform_iot`.`user` ;
4. Quelle valeur a été attribuée pour le id ?

b) Pour insérer encore huit autres utilisateurs :

1. Exécutez la requête INSERT suivante :
   INSERT INTO `platform_iot`.`user` (`firstname`, `lastname`, `email`) VALUES
('Maialen', 'HOLZARTE', 'm.holzarte@company.fr'),
('Pantxika', 'CENITZ', 'pantxi@sjdl.eus'),
('Maylis', 'ACOTZ', 'mayli40@sjdl.eus'),
('Elorri', 'IBARDINE', 'elo.iba@lepoa.fr'),
('Maika', 'IROULEGUY', 'maika@irouleguy.eu'),
('Maider', 'JAIZKIBEL', 'mai64@olatu.fr'),
('Arantxa', 'IPARLA', 'a.iparla@mendia.fr'),
('Mayana', 'URCURAY', 'm.urcuray@mendia.fr') ;
2. Vérifiez qu'il n'y a pas de message d'erreur dans la fenêtre principale.
3. Vérifier qu'une nouvelle ligne a bien été ajoutée à la table user en exécutant la requête suivante :
   SELECT * FROM `platform_iot`.`user` ;
4. Quelles valeurs ont été attribuées pour les id ?

Consigne : n'oubliez pas d'ajoutez la requête INSERT au fichier iot_feed.sql

Question 2.2 : Les services

Utilisez une requête INSERT pour insérer les six services suivants :

Vérifier que six nouvelles lignes ont bien été ajoutées à la table service en exécutant la requête suivante :

SELECT * FROM `platform_iot`.`service` ;

Depuis la fenêtre principale de phpMyAdmin, vérifiez ce qui a été inséré en affichant les lignes de la table service.

Quelles valeurs ont été attribuées pour les id ?

Consigne : ajoutez la requête INSERT au fichier iot_feed.sql

Question 2.3 : Les objets connectés

Utilisez une requête INSERT pour insérer les objets connectés suivants :

Depuis la fenêtre principale de phpMyAdmin, vérifiez ce qui a été inséré en affichant les lignes de la table thing.

Consigne : ajoutez la requête INSERT au fichier iot_feed.sql

Question 2.4 : Les souscriptions

Utilisez une requête INSERT pour insérer les souscriptions suivantes :

Depuis la fenêtre principale de phpMyAdmin, vérifiez ce qui a été inséré en affichant les lignes de la table subscribe.

Consigne : ajoutez la requête INSERT au fichier iot_feed.sql

Consigne : toujours avec l'éditeur de texte Notepad++, copiez toutes vos requêtes SELECT dans un nouveau fichier que vous nommerez iot_select.sql

Question 3.1 : Choisir les colonnes

Utilisez trois requêtes SELECT pour consulter les informations suivantes :

1. Lister les noms des services
2. Lister les prénoms et les noms des utilisateurs
3. Lister les adresses e-mails des utilisateurs
4. Lister les id des utilisateurs qui possèdent des objets connectés
   • Si des id apparaissent en doublons, faîtes en sorte que chaque id n'apparaissent qu'une fois (avec le mot-clé DISTINCT)

Question 3.2 : La clause WHERE

Utilisez quatre requêtes SELECT avec la clause WHERE pour consulter les informations suivantes :

1. Lister l'utilisateur avec le id de valeur 5
2. Lister les utilisateurs avec le nom 'IPARLA'
3. Lister les adresses MAC des objets connectés que possède l'utilisateur qui a le id de valeur 2

Question 3.3 : Combinaison avec AND et OR

Utilisez trois requêtes SELECT avec la clause WHERE pour consulter les informations suivantes :

1. Lister les services de type 'quantifiedself' ou 'smarthome'
2. Lister les prénoms des utilisateurs avec les noms 'IPARLA' ou 'CENITZ'
3. Lister les adresses MAC des objets de type 'thingtempo' de l'utilisateur qui a le id de valeur 2

Question 3.4 : Trier les résultats avec ASC et DESC

Utilisez trois requêtes SELECT avec le tri ORDER BY pour consulter les informations suivantes :

1. Lister les utilisateurs par ordre croissant de nom
2. Lister les utilisateurs par ordre décroissant de id
3. Lister le type et le nom des services par ordre croissant de type, puis par ordre croissant de nom

Question 3.5 : Inclusion avec IN et NOT IN

Nous allons utiliser trois requêtes SELECT avec l'opérateur d'appartenance IN pour rechercher les id dans une autre requête :

1. Pour lister les noms des services qui ont été souscrits par au moins un utilisateur :
   1. Commençons par construire une première requête qui liste les id des services qui ont été souscris :
      SELECT DISTINCT `id_service` FROM `platform_iot`.`subscribe` ;
   2. Puis, listons les types et noms des services dont le id apparaît dans le résultat de la première requête :
      SELECT `type`, `name` FROM `platform_iot`.`service` WHERE `id` IN (SELECT DISTINCT `id_service` FROM `platform_iot`.`subscribe`) ;
2. Pour lister les id et les noms des utilisateurs qui ne possèdent pas d'objets :
   1. Commençons par construire une première requête qui liste les id des utilisateurs qui possèdent des objets :
      SELECT DISTINCT `id_user` FROM `platform_iot`.`thing` ;
   2. Puis, listons les id et les noms des utilisateurs dont le id n'apparaît pas dans le résultat de la première requête :
      SELECT `id`, `lastname` FROM `platform_iot`.`user` WHERE `id` NOT IN (SELECT DISTINCT `id_user` FROM `platform_iot`.`thing`) ;
3. Pour lister les id et les noms des utilisateurs qui possèdent des objets :
   SELECT `id`, `lastname` FROM `platform_iot`.`user` WHERE `id` IN (SELECT DISTINCT `id_user` FROM `platform_iot`.`thing`) ;

Question 4.1 : Modifier une ligne

Pour chacune des trois requêtes à suivre  :

1. Lisez et comprenez la requête.
2. Observez les lignes de la table concernée.
3. Exécutez la requête.
4. Vérifiez qu'il n'y a pas de message d'erreur dans la fenêtre principale.
5. Vérifiez les modifications en affichant les lignes de la table concernée.

Les trois requêtes UPDATE suivantes permettent de modifier des lignes (enregistrements, tuples) :

1. Mettre la valeur NULL pour toute la colonne type de la table service :
   UPDATE `platform_iot`.`service` SET `type` = NULL ;
2. Mettre le nom 'WatchDOG2' pour la ligne qui a le id de valeur 5 :
   UPDATE `platform_iot`.`service` SET `name` = 'dogWATCH2' WHERE `id` = 5 ;
3. Multipler par une fois et demi les valeurs de la colonne param des objets connectés de type 'thingtempo' de la table thing :
   UPDATE `platform_iot`.`thing` SET `param` = `param` * 1.5 WHERE `type` = 'thingtempo' ;

Question 4.2 : Modifier la structure des tables

Pour chacune des trois requêtes à suivre  :

1. Lisez et comprenez la requête.
2. Observez la structure de la table concernée.
3. Exécutez la requête.
4. Vérifiez qu'il n'y a pas de message d'erreur dans la fenêtre principale.
5. Depuis la fenêtre principale de phpMyAdmin, vérifiez la nouvelle structure.

Les trois requêtes ALTER TABLE suivantes permettent de modifier les schémas des tables :

1. Modifier le type de donnée du champ email de la table user pour pouvoir contenir 200 caractères :
   ALTER TABLE `platform_iot`.`user` MODIFY `email` VARCHAR (200) ;
2. Changer le nom du champ param de la table thing pour devenir parameter :
   ALTER TABLE `platform_iot`.`thing` CHANGE `param` `parameter` VARCHAR (100) ;
3. Ajouter un nouveau champ birthday à la table user (le type de donnée sera DATE et la valeur devra obligatoirement être renseignée) :
   ALTER TABLE `platform_iot`.`user` ADD `birthday` DATE NOT NULL DEFAULT '1000-01-01' ;

Pour la dernière requête, quelles valeurs ont été attribuées pour les enregistrements déjà présents ? Donnez une date de naissance à Amaya en cliquant sur Modifier.

Question 5.1 : Supprimer une ligne

Utilisez trois requêtes DELETE pour supprimer des lignes dans les tables :

1. Supprimer l'utilisateur avec le id de valeur 1.
   • Exécutez la requête suivante :
     DELETE FROM `platform_iot`.`user` WHERE `id` = 1 ;
   • Vérifiez que la ligne a bien été effacée de la table.
2. Supprimer la souscription, de l'utilisateur de id de valeur 2, au service de id de valeur 1.
   • Exécutez la requêtesuivante :
     DELETE FROM `platform_iot`.`subscribe` WHERE `id_user` = 1 AND `id_service` = 3 ;
   • Vérifiez que la ligne a bien été effacée de la table.
3. Supprimer toutes les lignes de la table thing
   • Exécutez la requêtesuivante :
     DELETE FROM `platform_iot`.`thing` ;
   • Vérifiez que les lignes ont bien été effacées de la table.

Question 5.2 : Supprimer une colonne ou une table

1. Utiliser une requête ALTER TABLE pour supprimer la colonne birthday (ou la colonne password selon ce que vous aviez créé) du schéma de la table user.
   • Exécutez la requête suivante :
     ALTER TABLE `platform_iot`.`user` DROP `birthday` ;
     ou bien la requête suivante :
     ALTER TABLE `platform_iot`.`user` DROP `password` ;
   • Vérifiez que la colonne a disparu du la structure de la table user.
2. Utiliser une requête DROP pour supprimer la table thing.
   • Exécutez la requête suivante :
     DROP TABLE `platform_iot`.`thing` ;
   • Cliquez sur la base platform_iot à gauche de la fenêtre principale de phpMyAdmin
   • Vérifiez que de la table thing a bien été détruite.
3. Utiliser une requête DROP pour supprimer la table subscribe.
   • Exécutez la requête suivante :
     DROP TABLE `platform_iot`.`subscribe` ;
   • Cliquez sur la base platform_iot à gauche de la fenêtre principale de phpMyAdmin
   • Vérifiez que de la table subscribe a bien été détruite.

Question 5.3 : Supprimer la base de données platform_iot

Supprimez la base de données avec la requête suivante :

DROP DATABASE `platform_iot` ;

Question 5.4 : Mince alors !

Zut, tout a été effacé ! Vérifiez qu'il n'y plus trace de la base de données.

Comment remettre en place la base de données alors que nous avons tout effacé sans avoir sauvegardé au préalable ?

1. Cliquez sur Accueil sous le logo phpMyAdmin en haut à gauche.
2. Puis, ouvrez l'onglet Importer (si jamais il n'apparaît pas, déroulez avec le menu « plus »).
3. Utilisez le bouton Parcourir (ou Browse) pour sélectionner sur votre disque le fichier iot_create.sql que vous avez créé lors de l'exercice 1.
4. Cliquez sur le bouton Exécuter en bas du formulaire

Vérifiez que la base a bien été créé, que les tables sont en place, que leurs schémas sont corrects et surtout que les tables sont vides ! Comment remettre en place les données ?

1. De nouveau, cliquez sur Accueil sous le logo phpMyAdmin en haut à gauche.
2. Puis, ouvrez l'onglet Importer (si jamais il n'apparaît pas, déroulez avec le menu « plus »).
3. Utilisez le bouton Parcourir (ou Browse) pour sélectionner sur votre disque le fichier iot_feed.sql que vous avez créé lors de l'exercice 2.
4. Cliquez sur le bouton Exécuter en bas du formulaire

Pour vérifier que tout est en place, testez une ou deux de vos requêtes SELECT de l'exercice 3.