Bases de données – Niveau 1 SQL - MySQL – MariaDB
publicité
Bases de données – Niveau 1 SQL - MySQL – MariaDB - Cours et TP 1 Création de la BD Site officiel MariaDB : https://mariadb.org/ Site officiel MySQL : http://www-fr.mysql.com/ Documentations MySQL : http://dev.mysql.com/doc/index.html Documentations MySQL 5.0 en français : http://bliaudet.free.fr/IMG/pdf/MySQL-refman-5.0-fr.pdf Mémo SQL : http://www.volubis.fr/bonus/SQL_memo.htm Bertrand LIAUDET SOMMAIRE SOMMAIRE 1 CREATION DE LA BASE DE DONNÉES 1. Manipulation des bases de données Consultation des BD du SGBD Les bases de données pré-installées Création d’une BD Destruction d’une BD Utilisation d’une BD Connaître la BD actuellement utilisée Consultation des tables d’une BD Consultation du code de création des tables enregistrées par le SGBD Consultation des attributs d’une table Consultation des tuples d’une table La commande SHOW Les fonctions de la calculette 2. Création des tables : CREATE TABLE Syntaxe SQL Exemple Les types des données « Moteurs » MyIsam et InnoDB 3. Création des tuples : INSERT INTO 4. Les utilisateurs Utilisateur courant 3 3 3 3 4 4 4 4 5 5 5 5 6 6 7 7 7 7 7 8 9 9 BASES DE DONNÉES - Niveau 1 - MySQL - MariaDB - Cours 01 - page 1/13 - Bertrand LIAUDET Tous les utilisateurs 5. Messages du serveur 6. Sauvegarde C:\ mysql dump 7. Import - Export Exporter les données : SELECT … INTO OUTFILE Importer les données : LOAD DATA INFILE 9 9 10 10 11 11 12 TP N°1 : PRISE EN MAIN DE LA CALCULETTE SQL Exercice 1 : Créez la base employés Exercice 2 : débogage Exercice supplémentaires : autres bases 13 13 13 13 Dernière édition : octobre 2016 BASES DE DONNÉES - Niveau 1 - MySQL - MariaDB - Cours 01 - page 2/13 - Bertrand LIAUDET CREATION DE LA BASE DE DONNÉES PRINCIPALES NOTIONS Show databases Create database Drop database Show tables from 1. Use Desc CREATE TABLE INSERT INTO Manipulation des bases de données Consultation des BD du SGBD La BD est un ensemble de tables. C’est une sorte de dossier contenant des tables. Les BD peuvent aussi s’appeler « schema ». Le SGBD permet d’accéder à la liste de toutes les BD qu’il contient : show databases; Les bases de données pré-installées 4 bases de données pré-installées : Deux bases qui gèrent le dictionnaire des données. Le dictionnaire des données contient des métadonnées : des données sur les données enregistrées dans la BD. • mysql : https://dev.mysql.com/doc/refman/5.5/en/system-database.html • information_schema : http://dev.mysql.com/doc/refman/5.7/en/information-schema.html Deux bases qui gèrent les performances : • performance_schema : http://dev.mysql.com/doc/refman/5.7/en/performance-schema.html • sys : http://dev.mysql.com/doc/refman/5.7/en/sys-schema.html La base de test MySQL installe parfois une base de test vide. On peut l’utiliser pour des tests. Ou peut aussi la supprimer. BASES DE DONNÉES - Niveau 1 - MySQL - MariaDB - Cours 01 - page 3/13 - Bertrand LIAUDET Création d’une BD La création d’une base de donnée consiste seulement à créer une sorte de dossier qui contiendra les futures tables de la BD : Create database NomBD ; NomBD est le nom de la BD qu’on veut créer. On peut aussi vérifier la non-existence de la BD avant de la créer : Create database if not exists NomBD ; Ca évite un signal d’erreur si la BD existe. Destruction d’une BD La destruction d’une base de donnée consiste à détruire le dossier et toutes les tables de la BD. C’est à manier avec prudence : Drop database NomBD ; On peut aussi vérifier l’existence de la BD avant de la détruire : Drop database if exists NomBD ; Ca évite un signal d’erreur si la BD n’existe pas. Utilisation d’une BD Pour pouvoir manipuler les tables d’une BD, il faut d’abord signaler qu’on l’utilise : Use NomBD ; Ø Remarque : On peut accéder à toutes les tables en préfixant le nom de leur BD : nomBD.nomTable Connaître la BD actuellement utilisée Pour savoir dans quelle BD on se trouve : Select database() ; BASES DE DONNÉES - Niveau 1 - MySQL - MariaDB - Cours 01 - page 4/13 - Bertrand LIAUDET Consultation des tables d’une BD On peut afficher la liste des tables contenues dans une BD : Show tables from NomBD ; Ou, si la BD est utilisée : Show tables ; Consultation du code de création des tables enregistrées par le SGBD Ø Principe Quand on envoie une commande de création de table, le SGBD enregistre la structure de la table en mémoire. Il enregistre aussi le code de création de la table. On peut afficher ce code. Ø Syntaxe MySQL SHOW CREATE TABLE NomTable ; Consultation des attributs d’une table On peut afficher la liste des attributs d’une table : Desc NomTable ; Consultation des tuples d’une table Syntaxe SQL SELECT liste d’attributs FROM NomTable; Exemples : tous les attributs SELECT * FROM employes ; Exemples : affichage en mode « page » : \G SELECT * FROM employes \G Exemples : certains les attributs SELECT NE, nom FROM employes ; BASES DE DONNÉES - Niveau 1 - MySQL - MariaDB - Cours 01 - page 5/13 - Bertrand LIAUDET La commande SHOW http://dev.mysql.com/doc/refman/5.6/en/show.html help show : uniquement dans un terminal calculette. Show databases, Show tables, Show create table, Show grants, Show variables Show table status from nomBD ; // synthèse sur les tables de la BD Select table_schema, table_name, table_row from tables where table_schema=’maBD’; Les fonctions de la calculette http://dev.mysql.com/doc/refman/5.0/fr/entering-queries.html Select database(); Select user() ; Select version() ; Select found_rows( ), row_count( ) BASES DE DONNÉES - Niveau 1 - MySQL - MariaDB - Cours 01 - page 6/13 - Bertrand LIAUDET 2. Création des tables : CREATE TABLE Syntaxe SQL CREATE TABLE NomTable ( attribut_1 type [contrainte d’intégrité], attribut_2 type [contrainte d’intégrité], …, attribut_n type [contrainte d’intégrité] ); la contrainte est facultative. La table est une coquille vide : c'est une structure. Elle permettra ensuite de créer des tuples. Exemple Création de la table des employés : CREATE TABLE EMPLOYES ( NE integer primary key auto_increment, NOM varchar(10), JOB varchar(9), DATEMB date, SAL float(7,2), COMM float(7,2), ND integer not null ); Les types des données Les types des données correspondent pour la plupart aux types habituels des langages de programmation. Pour avoir la liste et la signification de tous les types : http://dev.mysql.com/doc/refman/5.0/fr/column-types.html « Moteurs » MyIsam et InnoDB Notion de « moteur » MySQL propose plusieurs « moteurs » pour gérer les tables. Les deux principaux sont : MyISAM et InnoDB. Un moteur peut être vu comme un SGBD particulier pour gérer les tables. Moteur par défaut : « default-storage-engine » La variable default-storage-engine définit le moteur par défaut. Show variable like ‘%engine%’; Moteur d’une table Ø Par défaut BASES DE DONNÉES - Niveau 1 - MySQL - MariaDB - Cours 01 - page 7/13 - Bertrand LIAUDET Quand on fait un CREATE TABLE, le moteur associé est celui par défaut Ø Explicitement A la création de la table, on peut préciser le moteur : CREATE TABLE NomTable ( … ) ENGINE MyISAM; On peut écrire ENGINE ou TYPE. Modification du moteur par défaut Ø Par modification du fichier de configuration On peut préciser dans le fichier « my.ini » : [mysqld] default-storage-engine=MyISAM Il faut alors relancer le serveur pour que la modification soit prise en compte. Choix d’un moteur Le moteur MyISAM est le moteur d’origine. Il est très rapide mais très permissif : il ne gère pas les contraintes d’intégrité référentielle. Le moteur InnoDB gère l’intégrité référentielle. 3. Création des tuples : INSERT INTO Syntaxe SQL INSERT INTO NomTable VALUES (val_att_1, … , val_att_n ) On passe la liste des valeurs pour chaque attribut du tuple, dans l’ordre de la déclaration dans la table. Exemples INSERT INTO EMPLOYES VALUES (7839,'KING','PRESIDENT','1981-11-17',5000,NULL,10); INSERT INTO EMPLOYES VALUES (7698,'BLAKE','MANAGER','1981-05-1',2850,NULL,30); INSERT INTO EMPLOYES VALUES (7782,'CLARK','MANAGER','1981-06-9',2450,NULL,10); BASES DE DONNÉES - Niveau 1 - MySQL - MariaDB - Cours 01 - page 8/13 - Bertrand LIAUDET 4. Les utilisateurs Utilisateur courant Quand on se connecte en tant que client, on est utilisateur de la BD. Un utilisateur à un nom et une machine hôte de son application cliente. Pour connaître le nom de l’utilisateur connecté : mysql > select user() ; Tous les utilisateurs Les utilisateurs de la BD sont listés dans la table « user » de la BD « mysql ». mysql > use mysql mysql > Select host, user from user ou mysql > Select host, user from mysql.user 5. Messages du serveur Le serveur envoie des messages de validation et des messages d’erreur. Il faut analyser les messages du serveur!!! En cas d’erreur, le serveur renvoie un n° de ligne : il faut analyser ce numéro. Exemples de message : Query ok Database changed etc On passe la liste des valeurs pour chaque attribut du tuple, dans l’ordre de la déclaration dans la table. BASES DE DONNÉES - Niveau 1 - MySQL - MariaDB - Cours 01 - page 9/13 - Bertrand LIAUDET 6. Sauvegarde C:\ mysql dump http://dev.mysql.com/doc/refman/5.0/fr/mysqldump.html Mysqldump est une commande qui permet de produire le code SQL permettant de recréer entièrement la BD. Trois usages de mysqldump Ø Sauvegarder des BD C:\ mysqldump [options] --databases DB1 [DB2 DB3...] Ø Sauvegarder toutes les BD C:\ mysqldump [options] --all-databases Affichage à l’écran ou redirection dans un fichier C:\ mysqldump –uroot –p nomBD La commande affiche le code SQL de la BD nomBD. C:\ mysqldump –uroot –p nomBD > nomFichier La commande écrit le code SQL de la BD nomBD dans le fichier nomFichier. Usage courant de MYSQLDUMP pour sauvegarder et recharger une BD Commande usuelle de sauvegarde d’une BD : C:\ mysqldump --opt nomBD > backupFile.sql L’option --opt inclut l’option --quick et l’option --lock-tables, entre autres. Pour recharger le fichier de sauvegarde : C:\ mysql nomBD < backupFile.sql Usage courant de MYSQLDUMP pour sauvegarder toutes les BDs Commande usuelle de sauvegarde de toutes les BD : C:\ mysqldump --opt --all-databases > allBD.sql Pour recharger le fichier de sauvegarde de toutes les BD : C:\ mysql < allBD.sql Usage spécial de MYSQLDUMP : ne sauvegarder que le schéma de la BD -- no-data BASES DE DONNÉES - Niveau 1 - MySQL - MariaDB - Cours 01 - page 10/13 - Bertrand LIAUDET 7. Import - Export Exporter les données : SELECT … INTO OUTFILE A partir de mysql Production d’un fichier « csv » exploitable sous Excel. Ø Chemin absolu mysql> SELECT * INTO OUTFILE ‘c:/dept.csv’ fields terminated by ‘,’ FROM dept; Ø Chemin relatif : DATADIR mysql> SELECT * INTO OUTFILE ‘./dept.csv’ fields terminated by ‘,’ FROM dept; Ø Chemin relatif : DATADIR / BDused mysql> SELECT * INTO OUTFILE ‘dept.csv’ fields terminated by ‘,’ FROM dept; A partir du SE C: \ > mysql dbname –u user –p >output.tab On passe en mode batch, l’affichage est redirigé dans le fichier output.tab Ø Ou bien C: \ > mysql dbname –u user –p –pager=cat>output.tab Remarque : output.tab est écrasé au début de l’opération, mais toutes les commandes de la même session sont executées à la suite Ø Autre usage : C: \ > mysql dbname –u user –p < fichier.sql > output.tab BASES DE DONNÉES - Niveau 1 - MySQL - MariaDB - Cours 01 - page 11/13 - Bertrand LIAUDET Importer les données : LOAD DATA INFILE http://dev.mysql.com/doc/refman/5.0/fr/load-data.html On peut importer des données en précisant le format de séparation des champs. Par exemple: les fichiers “csv” produit par Excel utilisent la virgule comme séparateur. Attention : La table correspondant au fichier importé doit avoir le même schéma que celle dans laquelle les tuples sont insérés. Ø Chemin absolu mysql> LOAD DATA INFILE ‘c:/dept.csv’ INTO TABLE dept fields terminated by ‘,’; Ø Chemin relatif : DATADIR mysql> LOAD DATA INFILE ‘./dept.csv’ INTO TABLE dept fields terminated by ‘,’; Ø Chemin relatif : DATADIR / BDused mysql> LOAD DATA INFILE ‘dept.csv’ INTO TABLE dept fields terminated by ‘,’; Ø Chemin relatif : DATADIR / BD au choix mysql> LOAD DATA INFILE ‘./empdept/dept.csv’ INTO TABLE dept fields terminated by ‘,’; Avec les options REPLACE ou IGNORE, quand une donnée rentre avec une clé primaire existant déjà, elle remplacera l’ancienne ou elle sera ignorée. Sinon, l’opération s’arrête en erreur. LOAD DATA INFILE ‘nomFichier’ REPLACE INTO TABLE nomTableOUTFILE BASES DE DONNÉES - Niveau 1 - MySQL - MariaDB - Cours 01 - page 12/13 - Bertrand LIAUDET TP N°1 : PRISE EN MAIN DE LA CALCULETTE SQL Le TP peut être fait dans une calculette en ligne de commande ou dans l’environnement PHPMyAdmin. Exercice 1 : Créez la base employés 1. Exécuter le script 01-Employes.sql dans une calculette SQL. 2. Tester les commandes suivantes manuellement : show databases, use database, create database, drop database 3. Une fois le script 01 exécuter, faites un show create table sur la ou les tables pour voir le code pris en compte par le SGBD. 4. Quel est le moteur utilisé ? 5. Quel sera la valeur par défaut pour le prochain numéro d’employé ? 6. 7. 8. 9. Regardez où et comment ont été stockées les données du script du TP. Créer une base avec un le moteur MyISAM Regardez où et comment ont été stockées les données du script du TP. Exportez les données de la table employes dans un fichier que vous serez ensuite capable de lire avec EXCEL : exporter, format CSV for MS Excel. Il faut enregistrer les données dans un fichier .csv. Puis ouvrir ce fichier avec excel. 10. Réimporter les données du fichier CSV dans la table employes. On passera tous les id à NULL. Il faut afficher la liste des employes puis faire plus/importer et préciser que les champs sont séparés par des ; 11. Faire une sauvegarde (mysqldump) de la base avec les données : exporter, format SQL. 12. Faire une sauvegarde (mysqldump) du schéma de la base uniquement. 13. Importer un script SQL avec le même outil. Exercice 2 : débogage Tester et corriger les script bogués : emp et ventes Exercice supplémentaires : autres bases Tester les autres bases OK : 02-empdept.sql, 03-Biblio.sql, etc. BASES DE DONNÉES - Niveau 1 - MySQL - MariaDB - Cours 01 - page 13/13 - Bertrand LIAUDET
