TP 1 MySQL : import et export de données, requête avec jointure interne

Un des buts de ce TP est d’utiliser les outils d’importation et d’exportation.

Créer et exécuter un script de création de tables

Écrire dans un fichier script.sql le script de création des tables correspondant au modèle suivant :

ARTICLE(idArticle, designation, prix)
COMMANDE(idCommande, dateCommande, #idClient)
LIGNE(#idCommande,#idArticle, quantite)
CLIENT(idClient, nom, ville)

Pour exécuter ce script :

Mysql\> source script.sql ;

Supprimer toutes les tables si elles existent (une table par instruction), créer toutes les tables si elles n'existent pas.

Utiliser les mots clés IF EXISTS lors de la suppression de la table et IF NOT EXISTS lors de la création des tables.

Importer des données

L'objectif est d'importer des données stockées dans un fichier texte.

Utiliser les données du fichier excel en enregistrant chaque feuille au format csv (séparateur de champ : , et séparateur de texte : vide )

Utiliser le nom de la table comme nom de Fichier

Exemple de format pour un fichier : CLIENT.csv

, Dupont, Lyon
, Pierre, Paris
, Durand, Lille

Procéder de la même manière avec les autres tables (onglets dans le tableur) pour créer les autres fichiers de données.

REMARQUE : La première virgule de la ligne de l'exemple signifie que le premier champ est NULL (AUTO_INCREMENT). Faire attention aux clés étrangères et au format de date : yyyy/mm/dd ou yyyy-mm-dd .

Lancer la commande ci dessous (dans votre script SQL ) :

mysql\> LOAD DATA LOCAL INFILE 'CLIENT.csv' INTO TABLE CLIENT FIELDS TERMINATED BY ',';

Faire de même avec les autre fichiers.

ATTENTION à l’ordre d’importation des données, c’est dans le même ordre généralement que la création des tables

Exemple d’utilisation de la commande “LOAD DATA” avec plusieurs options :

Il faut commencer par supprimer la première colonne de la feuille Excel (du fichier tableur), puis enregistrer cette feuille au format CSV avec ; comme délimiteur

LOAD DATA LOCAL INFILE 'C:\\Users\\login\\S2\\BDD\\TP\\TP1\\ARTICLE.csv' 
    REPLACE INTO TABLE `bdd_tp1`.`ARTICLE` 
    CHARACTER SET utf8 
    FIELDS TERMINATED BY ';' 
    ENCLOSED BY '"' 
    LINES TERMINATED BY '\n' 
    (`designation`,`prix`);

problème avec l’instruction `LOAD DATA LOCAL` sur DATAGRIP

ATTENTION problème résolu : si l’importation des données ne fonctionne par sur DataGrip même en indiquant le chemin absolu du fichier ; configurer datagrip ou utiliser un terminal classique pour exécuter votre script et importer les données

Accès à l’interface ci-dessus : File > Data Sources [sélectionner la source] [advanced]

Sur DATAGRIP, il faut indiquer le chemin absolu du fichier CSV
- LOAD DATA LOCAL INFILE '/home/login/Documents/2_S2_BDD/tp1/ARTICLE.csv' INTO TABLE ARTICLE FIELDS TERMINATED BY ',';
- Sur windows, il faut indiquer le chemin absolu du fichier CSV avec des \\
  - LOAD DATA LOCAL INFILE 'C:\\Users\\login\\Documents\\tp1\\ARTICLE.csv' INTO TABLE ARTICLE FIELDS TERMINATED BY ',';

Ajouter un terminal dans DataGrip

Dans le menu : [File > Setting] ; choisir [Plugins] Sélectionner “Terminal”
Une fois installer; Pour configurer le chemin par défaut dans le terminal
- Dans le menu : [File > Setting] ; choisir [Tools] Sélectionner “Terminal” et configurer “Start Directory”

problème d’encodage de caractères dans les chaînes de caractères

voir l’encodage de la table

SHOW CREATE TABLE ARTICLE;

ajouter un encodage au caractère dans la table

CREATE TABLE IF NOT EXISTS ARTICLE (

....

)CHARACTER SET 'utf8' ;

le terminal mysql n’est pas forcément en utf8 et l’import des données n’est donc pas forcément en utf8

forcer l’encodage des caractères lors de l’importation des données

LOAD DATA LOCAL INFILE 'CLIENT.csv' INTO TABLE CLIENT CHARACTER SET utf8
FIELDS TERMINATED BY ',';

Attention sur windows, le terminal (invité de commande ; powershell) est encodé en cp850

problème pour utiliser la commande “LOAD DATA LOCAL” sur MySql (oracle)

ATTENTION il n’est parfois pas autorisé d’utiliser la commande “LOAD DATA LOCAL” sur mysql(oracle)
Vous pouvez essayer de vous connecter avec l’option --local_infile : voir sur stackoverflow

Vérifier dans un terminal connecté à mysql la valeur de la variable local_infile, elle devrait être à true, utiliser la commande :

mysql>SHOW GLOBAL VARIABLES LIKE 'local_infile';

Configurer mysql sur Linux

éditer le fichier de configuration de mysql (voir sur stackoverflow et stack)

sudo nano /etc/mysql/my.cnf

ajouter les lignes suivantes à la fin :

[mysqld]
lower_case_table_names=0
local_infile=ON
sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

[mysql]
local_infile=ON

redémarrer le service mysql (service --status-all)

sudo service mysql restart

Sur windows, éditer le fichier C:\\tools\\mysql\\current\\my.ini (installation avec chocalatey) et faire les mêmes modifications que dans my.cnf sur linux

Pour connaître le dossier du fichier my.cnf (Linux,MacOS) ou my.ini (Windows), exécutez la commande mysql --help --verbose. L’information est généralement affichée au début de la sortie. Sur Linux, utilisez sudo en début de commande pour obtenir les privilèges de superutilisateur.

Exporter des données : mysqldump

Pour exporter une base (depuis un terminal « shell») dans le terminal :

mysqldump --host=serveurmysql --user=votrelogin --password=motdePasse  votreBaseDeDonnee

Pour exporter une base (depuis un terminal « shell») dans un fichier :

mysqldump --host=serveurmysql --user=votrelogin --password=motdePasse  votreBaseDeDonnee --no-tablespaces > base.sql

ATTENTION depuis peu la commande fonctionne, mais elle affiche un message d’erreur : stackexchange : il faut ajouter --no-tablespaces dans la commande

On obtient un script de création très complet (base.sql) + les INSERT de données. On peut donc l’exécuter de la même façon qu’en partie 1.

Pour n'exporter que certaines tables, rajouter le nom des tables derrière la base de données.

mysqldump --host=host --user=votrelogin --password=motdePasse --no-tablespaces votreBaseDeDonnee t1 t2 > base.sql

sauvegarder la base de données dans un fichier avec la date d’aujourd’hui

Sur le SE Linux pour faire un backup, créer un script “bash” avec les commandes ci-dessous :

echo nom_fichier_backup_$(date +%Y-%m-%d-%H.%M.%S).sql

mysqldump --host=host --user=votrelogin --password=motdePasse --no-tablespaces votreBaseDeDonnee > nom_fichier_backup_$(date +%Y-%m-%d-%H.%M.%S).sql

Sur Windows, créer un script “Powershell” (.ps1) avec les commandes celui ci-dessous :

$timestamp = Get-Date -Format "yyyy-MM-dd_HH_mm_ss"
mysqldump --host=host --user=votrelogin --password=motdePasse --no-tablespaces votreBaseDeDonnee > "backup_$timestamp.sql"

Exemple final :

depuis un terminal connecté à mysql avec la commande system — depuis un terminal connecté à mysql avec la commande **system**

Pour information : planification de tâches

=> Permet de sauvegarder le contenu d’une base de données

sur linux

sur windows

“schtask sur windows”

Modifier la structure des tables

Si on supprime le client 'Mutz', que se passe t'il ?

On désire modifier les contraintes pour pouvoir supprimer en cascade les commandes d'un client (lignes de commandes et commandes)

Modifier les contraintes sans recréer vos tables (ALTER TABLE …. ). Utiliser les mots clés « ON DELETE CASCADE ».

Vérifier les contraintes en regardant la structure de la table.

Faire un essai et vérifier que le contenu des 3 tables a été modifié.

Exemple de script

Afficher le contenu des tables, conclusion
Mettre en commentaire ce script pour la suite

Quelques requêtes

R1 : Affichage : Liste des clients dont la ville est Belfort et dont le nom commence par la lettre « m » , « e » ou « d » (majuscule et minuscule). Le résultat est ordonné par nom.

+-------------+
| nom         |
+-------------+
| DECOCK      |
| duguet      |
| ESSENBURGER |
| METTEY      |
| molin       |
+-------------+

R2 : Affichage : Liste des articles dont le prix est compris entre 6 et 10 Euros et dont la désignation possède le chaîne de caractères « lég ». Le résultat est ordonné par désignation.

+-------------------------+------+
| designation             | prix |
+-------------------------+------+
| Haricot ( légume )      | 6.50 |
| Petit pois ( légume )   | 6.00 |
+-------------------------+------+

RAPPEL COURS : Les jointures internes. Les enregistrements sont retournés quand la condition est vraie dans les 2 tables. C’est l’une des jointures les plus communes.

commencer par tester les requêtes suivantes :


SELECT * FROM COMMANDE ;

SELECT * FROM CLIENT ;

SELECT * FROM CLIENT, COMMANDE ;

conclusion

Il existe 2 manières de faire une jointure interne :

SELECT * -- *Ancienne syntaxe*
FROM CLIENT, COMMANDE 
WHERE CLIENT.idClient = COMMANDE.idClient;


SELECT *
FROM CLIENT  -- *Syntaxe actuelle*
INNER JOIN COMMANDE
ON CLIENT.idClient = COMMANDE.idClient;

Remarques :

* le mot clé « INNER » est optionnel

* les contraintes sur les tables entre clé primaire et clé étrangère ne lient pas les tables lorsque l’on fait des requêtes

autres exemples

R3 : première jointure ; Affichage : Dates de toutes les commandes du client de nom ‘Mutz’ ordonnées par date

+------+--------------+
| nom  | dateCommande |
+------+--------------+
| Mutz | 2023-09-19   |
| Mutz | 2023-12-09   |
+------+--------------+

R4 : Faire une requête pour afficher les articles (prix et quantité commandée) des commandes du client de nom ‘Mutz’ :

Affichage : nom du client, désignation du produit, prix du produit, quantité commandée, numéro de commande. L’affichage n’est pas trié.

+------+-------------------------------+------+----------+------------+
| nom  | designation                   | prix | quantite | idCommande |
+------+-------------------------------+------+----------+------------+
| Mutz | gâteau fraise (pâtisserie)    | 5.25 |       27 |          4 |
| Mutz | Tomate ( légume )             | 1.50 |        5 |          4 |
| Mutz | Potiron ( légume )            | 3.00 |        5 |          4 |
| Mutz | Pomme ( fruit )               | 3.00 |        5 |          4 |
| Mutz | Petit pois ( légume )         | 6.00 |        6 |          8 |
+------+-------------------------------+------+----------+------------+

R5 : Faire une requête pour afficher les articles et le C.A. dans chaque commande) des commandes du client de nom ‘Mutz’ :

Affichage : nom du client, désignation du produit, identifiant de la commande et prix total pour chaque produit : prix du produit * quantité commandée. L’affichage est trié par le prix_total dans l’ordre décroissant.

+------+-------------------------------+------------+------------+
| nom  | designation                   | idCommande | prix_total |
+------+-------------------------------+------------+------------+
| Mutz | gâteau fraise (pâtisserie)    |          4 |     141.75 |
| Mutz | Petit pois ( légume )         |          8 |      36.00 |
| Mutz | Potiron ( légume )            |          4 |      15.00 |
| Mutz | Pomme ( fruit )               |          4 |      15.00 |
| Mutz | Tomate ( légume )             |          4 |       7.50 |
+------+-------------------------------+------------+------------+

R6 : Faire une requête pour afficher les commandes du client de nom ‘Mutz’ :

Affichage : Nom du client, numéro de commande , prix total pour chaque commande : somme des (prix du produit * quantité commandée par produit). L’affichage est trié par le prix_total dans l’ordre décroissant.

+------+------------+------------+
| nom  | idCommande | prix_total |
+------+------------+------------+
| Mutz |          4 |     179.25 |
| Mutz |          8 |      36.00 |
+------+------------+------------+

R7 : Afficher le nom des clients ayant passés une commande, l'identifiant de leur commande, le prix total HT, la tva et le prix ttc ordonnées par prix (le prix dans la table est HT et la tva est de 20 %). L’affichage est trié par le prix_total_TTC dans l’ordre croissant.

+--------+------------+---------------+-------+----------------+
| nom    | idCommande | prix_total_HT | TVA   | prix_total_TTC |
+--------+------------+---------------+-------+----------------+
| Rato   |          6 |          4.50 |  0.90 | 5.40           |
| Walter |          7 |          7.50 |  1.50 | 9.00           |
| Goerig |          5 |         12.00 |  2.40 | 14.40          |
| Walter |          3 |         35.25 |  7.05 | 42.30          |
| Mutz   |          8 |         36.00 |  7.20 | 43.20          |
| Goerig |          1 |         68.24 | 13.65 | 81.89          |
| Rato   |          2 |         75.24 | 15.05 | 90.29          |
| Mutz   |          4 |        179.25 | 35.85 | 215.10         |
+--------+------------+---------------+-------+----------------+

Un peu plus difficile (jointure à gauche ou à droite) :

R8 : Afficher pour chaque produit, la quantité commandée, l’année de la commande et l’identifiant de la commande.
L’affichage est trié par la désignation de l’article par ordre croissant puis par l’année de la commande dans un ordre décroissant puis par la quantité commandée dans un ordre décroissant et enfin par identifiant de commande.


+----------------------------------+-------------+---------------+------------+
| designation                      | QteCommande | anneeCommande | idCommande |
+----------------------------------+-------------+---------------+------------+
| Choux ( légume )                 |           6 |          2023 |          2 |
| Coca cola ( boisson )            |          12 |          2023 |          1 |
| Fenouil ( légume )               |           3 |          2024 |          3 |
| Fenouil ( légume )               |           4 |          2023 |          1 |
| Fenouil ( légume )               |           4 |          2022 |          5 |
| gâteau fraise (pâtisserie)       |          27 |          2023 |          4 |
| Gâteaux chocolat ( pâtisserie)   |           3 |          2024 |          3 |
| Haricot ( légume )               |           4 |          2023 |          1 |
| Mouchoir ( hygiène)              |           6 |          2023 |          2 |
| Perrier ( 1L eau )               |        NULL |          NULL |       NULL |
| Petit pois ( légume )            |           6 |          2023 |          2 |
| Petit pois ( légume )            |           6 |          2023 |          8 |
| Pissenlit ( légume )             |        NULL |          NULL |       NULL |
| Poireau ( légume )               |           3 |          2024 |          3 |
| Poireau ( légume )               |           3 |          2023 |          6 |
| Poivron ( légume )               |           4 |          2023 |          1 |
| Pomme ( fruit )                  |           5 |          2023 |          4 |
| Pomme de terre ( légume )        |           6 |          2023 |          2 |
| Potiron ( légume )               |           5 |          2023 |          4 |
| Radis ( légume )                 |           3 |          2024 |          3 |
| Salade ( légume )                |        NULL |          NULL |       NULL |
| Tomate ( légume )                |           5 |          2023 |          4 |
| Tomate ( légume )                |           5 |          2023 |          7 |
| Vittel ( 1L eau )                |        NULL |          NULL |       NULL |
+----------------------------------+-------------+---------------+------------+

R9 : Afficher les produits, la quantité commandée, l’année de la commande et l’identifiant de la commande des produits commandés en 2023 qui sont dans la liste des produits commandés en 2024. (attention une ligne n’était pas bonne dans le tableau excel, voir la version en ligne)

+----------------------+-------------+---------------+------------+
| designation          | QteCommande | anneeCommande | idCommande |
+----------------------+-------------+---------------+------------+
| Fenouil ( légume )   |           4 |          2023 |          1 |
| Poireau ( légume )   |           3 |          2023 |          6 |
+----------------------+-------------+---------------+------------+

R10 : Afficher le nombre de commandes de chaque client en 2023. Si le nombre de commande est 0, le résultat affiché doit être “null”, utiliser la fonction “coalesce” pour remplacer “null” par “0” ; Utiliser la fonction “ifnull” qui est identique à “coalesce” pour afficher “pas de commande” au lieu de “null”.
L’affichage est trié par le nombre de commande décroissant puis par le nom des clients.

+--------------+------------+-----------------+
| nom          | nbCommande | anneeCommande   |
+--------------+------------+-----------------+
| Rato         |          3 | 2023            |
| Mutz         |          2 | 2023            |
| Walter       |          1 | 2023            |
| Goerig       |          1 | 2023            |
| AMGHAR       |          0 | pas de commande |
| dirand       |          0 | pas de commande |
| SAINT DIZIER |          0 | pas de commande |
| WOLF         |          0 | pas de commande |
| TYRODE       |          0 | pas de commande |
| DECOCK       |          0 | pas de commande |
| duguet       |          0 | pas de commande |
| molin        |          0 | pas de commande |
| COTTARD      |          0 | pas de commande |
| PETIT        |          0 | pas de commande |
| METTEY       |          0 | pas de commande |
| TERRAT       |          0 | pas de commande |
| chaillet     |          0 | pas de commande |
| DAROSEY      |          0 | pas de commande |
| JAOUEN       |          0 | pas de commande |
| BOUCHAUD     |          0 | pas de commande |
| PAUTOT       |          0 | pas de commande |
| LAMOTTE      |          0 | pas de commande |
| SALVI        |          0 | pas de commande |
| BISMUTH      |          0 | pas de commande |
| ALANKAYA     |          0 | pas de commande |
| ESSENBURGER  |          0 | pas de commande |
+--------------+------------+-----------------+

Modèle Conceptuel de données

Établir le Modèle Conceptuel de Données (MCD) associé à ce Modèle Relationnel (MR ou MLD) avec le logiciel LOOPING-MCD

ANNEXE : faire attention aux points suivants :

Lors de la création des tables :

* Faire attention à l'ordre, on ne crée pas une table avec une contrainte sur une table qui n'existe pas.

* Si un champ utilise l'attribut « AUTO_INCREMENT », celui-ci doit être une clé primaire.

Lors de la suppression des tables :
Faire attention à l'ordre, on ne supprime pas une table qui a sa clé primaire utilisée dans une contrainte d'une autre table (clé étrangère dans une autre table).
Lors de la création des contraintes sur les clés étrangères, en cas d'erreur :

* Vérifier que vous avez créé le champ de la clé étrangère.

* Le type des 2 champs reliés par la contraintes doivent être identiques (erreur fréquente : « int » relié à « unsigned int »).

* ATTENTION aux noms des contraintes : pas de doublons dans la base de données (différents d'un nom de table ….), exemple « fk_table1_table2 ».

* Vérifier que le le nom de l'autre table ainsi que sa clé primaire sont corrects.

Lors de l'importation des données :

* Certaines tables utilisent des clés primaires de type « AUTO_INCREMENT » : si les champs sont nuls lors de l'importation, la clé primaire doit utiliser l'attribut « AUTO_INCREMENT ».

* L'ordre des champs importés est important : respecter l'ordre de création du modèle logique (tables).

ANNEXE : documentation

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

http://sql.sh/cours/jointures

ANNEXE MCD sur différents logiciels

MCD (modèle conceptuel de données) : schéma à réaliser lors du partiel