TP cours BDD
publicité
Travaux pratiques du cours “Bases de données et SQL” avec MySQL Installer un serveur MySQL Sous windows ou MacIntosh télécharger la version adéquate du serveur MySQL depuis la page http://dev.mysql.com/downloads/mysql/5.0.html et installer comme avec n!importe quel logiciel. Sous ubuntu utiliser Synaptic pour faire l!installation de MySQL-server ou utiliser la commande “sudo apt-get install mysql-server” . Lors de l!installation il vous sera demandé d!attribuer un mot de passe pour l!utilisateur "root!, cet utilisateur étant l!administrateur du serveur mysql il a tous les droits et est donc très important, et il est souhaitable de lui créer un mot de passe (à ne pas oublier bien sur). Installer un client MySQL En complément au serveur ill est pratique d!utiliser l!un ou l!autre logiciel dit “client”, c!est à dire un logiciel permettant d!interagir avec le serveur. Cette étape n!est pas indispensable, vous pouvez aussi utiliser la ligne de commande pour effectuer toutes les opérations possible avec MySQL, cependant les lignes de commandes nécessitent un investissement personnel beaucoup plus important. MySQL propose différents outils graphiques rassemblés parfois sous l'appellation “MySQL GUI Tools” : • Mysql query browser vous permet d!écrire des requêtes SQL • Mysql admin vous permet d!administrer tous les aspects généraux du serveur et de manipuler les bases de données • MySQL Migration Toolkit permet d!importer dans MySQL des bases de données entières issues de divers autres systèmes tels que MS Access, Oracle, ... L!installation de ces outils se fait de la façon suivante : Sous ubuntu par synaptic ou par la commande “sudo apt-get install mysql-query-browser” ou autre selon ce que vous installez. Sous Windows et Mac téléchargez la version du paquet “MySQL GUI Tools” qui convient à votre système. Utiliser un client alternatif D!autres logiciels peuvent être utilisés pour accéder aux données stockées par MySQL. D!une part il existe des logiciels dont l!unique fonction est d!être client d!un ou de plusieurs SGBD, d!autre part il y a des logiciels autonomes qui peuvent se connecter à un SGBD moyennant l!installation dans le système d!un module de connexion spécifique au SGBD appelé “ODBC” ou une variante. Les logiciels clients sous windows : HeidiSQL, ... Les logiciels autonomes sous windows : MS Access, MS Excel, OpenOffice Base, Kexi, ... Les logiciels autonomes sous Ubuntu : OpenOffice Base, Kexi,... Les logiciels autonomes sous Mac Os X : OpenOffice Base, Kexi,... 1 Créer une base de données MySQL avec les outils graphiques Assurez-vous que le serveur soit actif (voir plus haut). Lancer MySQL-admin et vous connecter en tant que utilisateur “root” avec le mot de passe enregistré lors de l!installation de MySQL server. L!hôte est l!ordinateur sur lequel vous avez installé le serveur, c!est-à-dire “localhost” si c!est votre propre ordinateur. Le port par défaut est “3306”. Lorsque vous êtes connecté vous accédez à une fenêtre, avec une colonne sur la gauche en bas de laquelle se trouve un onglet nommé “Catalogs”. En y accédant vous pouvez y créer une base de données par un clic-droit sur la liste des bases de données. Donnez lui un nom et c!est fait. Ensuite allez sur l!onglet “Accounts” pour y créer un nouvel utilisateur, de votre choix (voir ci-dessous). Créer un utilisateur A partir de l!onglet “Accounts” de Mysql-admin vous pouvez créer un ou plusieurs utilisateurs. Vous devez fournir un nom et un mot de passe, puis éventuellement définir (clic-droit sur le nom de l!utilisateur dans la colonne de gauche) des pc à partir desquels l!utilisateur travaillera. Par défaut il y a “@%” qui signifie “tout ordinateur”, et à ce stade c!est suffisant. Attribuer des droits sur une base de données Dans la liste des utilisateurs (à gauche) sous le nom de l!utilisateur que vous venez de créer se trouve la liste des ordinateurs à partir desquels l!utilisateur peut travailler, par défaut il y a “@%” qui signifie “tout ordinateur”. Vous pouvez vous contenter de cette valeur, sélectionner-la en cliquant une fois dessus, puis dans la fenêtre de droite cliquer sur l!onglet “Schema privileges” qui vous donne la liste des DB existantes sur le serveur. Sélectionner la DB que vous avez créée précédemment et ensuite faites passer de la colonne de droite les droits que vous voulez attribuer à l!utilisateur sur cette DB vers la colonne du milieu. Par défaut faites les tous passer de droite à gauche. Enregistrez vos modifications. A partir de maintenant vous pouvez vous connecter à MySQL via n!importe quelle interface en vous identifiant avec cet utilisateur pour travailler sur la base de données que vous avez créée. Pour réaliser toutes ces opérations en ligne de commande je vous invite à vous reporter à la documentation en ligne de MySQL. Créer une table dans votre base de données MySQL A l!aide de MySQL-admin vous pouvez créer une table en cliquant sur “Table action” puis “Create Table”. Là vous aurez à créer les attributs (les colonnes) de la table en précisant le type de données ainsi que les éventuels autres paramètres que vous jugerez utiles (“NOT NULL”, “Unsigned”, etc). Vous aurez aussi à choisir une clé primaire parmi les attributs et éventuellement aussi des index. 2 Modifier une table dans MySQL Les structures de tables peuvent être modifiées dans MySQL-admin dans la même interface que celle utilisée pour la création mais l!accès à cette interface peut se faire par MySQL-Query-Browser par un clicdroit sur la table en question. Créer une table à partir d!une requête SELECT Dans la pratique, lorsqu!on a déjà une base de données chargées, il est facile de créer des tables à partir d!autres tables pour peu que ces dernières contiennent les données nécessaires à l!alimentation de la nouvelle table à créer. Il suffit pour cela de faire précéder la requête SELECT de la commande SQL “CREATE TABLE nom_de_la_table ” . Cela créera une table avec tous les attributs présents dans la clause SELECT mais pas les index ni la clé primaire qu!il faudra penser à ajouter par après. Extraire des données d!une table existante Utiliser l!outil MySQL-Query-Browser Ecrire une requête SELECT pour sélectionner les éléments à extraire, ex. : “SELECT * FROM table” pour obtenir tous les éléments de la table “table”. Ensuite aller dans le menu File, puis “Export Resultset” et sauvegarder dans le type souhaité selon l!usage prévu du fichier. Charger une table à partir d!un fichier La commande “LOAD DATA LOCAL INFILE 'datafile.csv' INTO TABLE tbl_name FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;” permet de charger la table “ tbl_name” avec le fichier “ datafile.csv” si celui-ci est de type txt, avec le pointvirgule en guise de séparateur de champs et le guillemet double en guise de délimiteur de champs textuel. Le point-virgule peut être remplacé par une virgule, une tabulation, ou autre mais il faut alors le préciser dans la requête ci-dessus, de même pour le délimiteur de champs. Les fins de lignes sont des caractères invisibles qui dépendent du système d!exploitation ou du logiciel utilisé pour créer le fichier; “\r\n” correspond à un encodage sous windows, “\r” à Mac OS et “\n” à Linux, mais cela peut varier et il vaut mieux être prudent. La clause “IGNORE” indique un nombre de lignes à ignorer au début du fichier et est facultative comme les autres clauses détaillées ci-dessus. Charger une BD avec MySQL Migration Toolkit L!outil MySQL Migration Toolkit permet de transférer dans MySQL le contenu et la structure entière d!une base de données telle qu!un fichier MS Access ou une base de données Oracle, MS SQL Server, etc (mais pas PostgreSQL). Il crée d!abord le script de création de la base et ensuite le script d!insertion des données, on peut donc éventuellement s!en servir pour ne faire qu!une seule de ces étapes. En particulier les fichiers Access sont généralement définis avec des formats de taille maximale (varchar(256), bigint, ...) et on peut saisir l!occasion pour les redéfinir avant de charger les données dans MySQL. 3
