Manuel complet MySQL
Manuel de cours pour apprendre l'administration des bases de donnees avec MySQL
...
SGBD
Un Système de Gestion de base de données (SGBD) est un logiciel (ou un ensemble de logiciels) permettant de manipuler les données d'une base de données. C'est-à-dire sélectionner et afficher des informations tirées de cette base, modifier des données, en ajouter ou en supprimer (ce groupe de quatre opérations étant souvent appelé le CRUD, pour Create, Read, Update, Delete). MySQL est un système de gestion de bases de données.
Le paradigme client - serveur
La plupart de SGBD sont basés sur un modèle Client - Serveur. C'est à dire que la base de données se trouve sur un serveur qui ne sert qu'à ça, et pour interagir avec cette base de données, il faut utiliser un logiciel "client" qui va interroger le serveur, et transmettre la réponse que le serveur lui aura donnée. Le serveur peut être installé sur une machine différente du client, et c'est souvent le cas lorsque les bases de données sont importantes. Ce n'est cependant pas obligatoire, ne sautez pas sur votre petit frère pour lui emprunter son ordinateur. Dans ce tutoriel, nous installerons les logiciels serveur et client sur un seul et même ordinateur.
Par conséquent, lorsque vous installez un SGBD basé sur ce modèle (et c'est le cas de MySQL), vous installez en réalité deux choses (au moins) : le serveur, et le client. Et chaque requête (insertion/modification/lecture de données) est faite par l'intermédiaire du client. Jamais vous ne discuterez directement avec le serveur (d'ailleurs, il ne comprendrait rien à ce que vous diriez).
Et vous avez donc besoin d'un langage pour discuter avec le client, pour lui donner les requêtes que vous souhaitez effectuer. Dans le cas de MySQL, ce langage est le SQL.
SGBDR
Le R de SGBDR signifie "relationnel". Un SGBDR est un SGBD qui implémente la théorie relationnelle. MySQL impléménte la théorie relationnelle, et est donc un SGBDR.
La théorie relationnelle dépasse le cadre de ce tutoriel, mais ne vous inquiétez pas, il n'est pas nécessaire de la maîtriser pour être capable d'utiliser convenablement un SGBDR. Il vous suffit de savoir que dans un SGBDR, les données sont contenues dans ce qu'on appelle des relations, qui sont représentées sous forme de tables. Une relation est composée de deux parties, l'en-tête et le corps. L'en-tête est lui-même composé de plusieurs attributs. Par exemple, pour la relation "Client", on peut avoir l'en-tête suivant
Quant au corps, il s'agit d'un ensemble de lignes (ou n-uplets) composées d'autant d'éléments qu'il y a d'attributs dans le corps. Voici donc quatre lignes pour la relation "Client" :
Numéro Nom Prénom Email
1 Jean Dupont
2 Marie Malherbe
3 Nicolas Jacques
4 Hadrien Piroux
Différentes opérations peuvent alors être appliquées à ces relations, ce qui permet d'en tirer des informations. Parmi les opérations les plus utilisées, on peut citer (soient et deux relations) :
la sélection (ou restriction) : obtenir les lignes de répondant à certains critères ; la projection : obtenir une partie des attributs des lignes de ;
l'union - : obtenir tout ce qui se trouve dans la relation A ou dans la relation B ;
l'intersection - : obtenir tout ce qui se trouve à la fois dans la relation A et dans la relation B ; la différence - : obtenir ce qui se trouve dans la relation A mais pas dans la relation B ;
la jointure - : obtenir l'ensemble des lignes provenant de la liaison de la relation A et de la relation B à l'aide d'une information commune.
Un petit exemple pour illuster la jointure : si l'on veut stocker des informations sur des les clients d'une société, ainsi que des commandes passées par ces clients, on utilisera deux relations : client et commande, la relation commande étant liée à la relation client par une référence au client ayant passé commande.
Un petit schéma clarifiera tout ça !
Le client numéro 3, M. Nicolas Jacques, a donc passé une commande de trois tubes de colle, tandis que Mme Marie Malherbe (cliente numéro 2) a passé deux commandes, pour du papier et des ciseaux.
Le langage SQL
Le SQL (Structured Query Language) est un langage informatique qui permet d'interagir avec des bases de données relationnelles. C'est le langage pour base de données le plus répandu, et c'est bien sûr celui utilisé par MySQL. C'est donc le langage que nous allons utiliser pour dire au client MySQL d'effectuer des opérations sur la base de données stockée sur le serveur MySQL
Il a été créé dans les années 1970, et est devenu standard en 1986 (pour la norme ANSI - 1987 en ce qui concerne la norme ISO). Il est encore régulièrement amélioré.
Présentation succincte de MySQL...
MySQL est donc un Système de Gestion de Bases de Données Relationnelles, qui utilise le langage SQL. C'est un des SGBDR les plus utilisés. Sa popularité est due en grande partie au fait qu'il s'agisse d'un logiciel Open Source, ce qui signifie que son code source est librement disponible et que quiconque en ressente l'envie et/ou le besoin peut modifier MySQL pour l'améliorer ou l'adapter à ses besoins. Une version gratuite de MySQL est par conséquent disponible. A noter qu'une version commerciale payante existe également.
Le logo de MySQL est un dauphin, nommé Sakila suite au concours "Name the dolphin" ("Nommez le dauphin").
Un peu d'histoire
Le développement de MySQL commence en 1994 par David Axmark et Michael Widenius. EN 1995, la société MySQLAB est fondée par ces deux développeurs, et Allan Larsson. C'est la même année que sort la première version officielle de MySQL. En 2008, MySQLAB est rachetée par la société Sun Microsystems, qui est elle-même rachetée par Oracle Corporation en 2010.
On craint alors la fin de la gratuité de MySQL, étant donné que Oracle Corporation édite un des grands concurrents de MySQL : Oracle Database, qui lui est payant (et très cher). Oracle a cependant promis de continuer à développer MySQL et de conserver la double licence GPL (libre) et commerciale jusqu'en 2015 au moins.
Mise en garde
David Axmark , fondateur de MySQL
MySQL est très utilisé, surtout par les débutants. Vous pourrez faire de nombreuses choses avec ce logiciel, et il convient tout à fait pour découvrir la gestion de bases de données. Sachez cependant que MySQL est loin d'être parfait. En effet, il ne suit pas toujours la norme officielle. Certaines syntaxes peuvent donc être propres à MySQL et ne pas fonctionner sous d'autres SGBDR. J'essayerai de le signaler lorsque le cas se présentera, mais soyez conscient de ce problème.
Par ailleurs, il n'implémente pas certaines fonctionnalités avancées, qui pourraient vous être utiles pour un projet un tant soit peu ambitieux. De plus, il est très permissif, et acceptera donc des requêtes qui génèreraient une erreur sous d'autres SGBDR.
... et de ses concurrents
Il existe des dizaines de SGBDR, chacun ayant ses avantages et inconvénients. Je présente ici succinctement quatre d'entre eux, parmi les plus connus. Je m'excuse tout de suite auprès des fans (et même simples utilisateurs) des nombreux SGBDR que j'ai omis.
Oracle database
Oracle, édité par Oracle Corporation (qui, je rappelle, édite également MySQL) est un SGBDR payant. Son coût élevé fait qu'il est principalement utilisé par des entreprises.
Oracle gère très bien de grands volumes de données. Il est inutile d'acheter une licence oracle pour un projet de petite taille, car les performances ne seront pas bien différentes de celles de MySQL ou un autre SGBDR. Par contre, pour des projet conséquents (plusieurs centaines de Go de données), Oracle sera bien plus performant.
Par ailleurs, Oracle dispose d'un langage procédural très puissant (du moins plus puissant que le langage procédural de MySQL) : le PL/SQL.
PostgreSQL
Comme MySQL, PostgreSQL est un logiciel Open Source. Il est cependant moins utilisé, notamment par les débutants, car moins connu. La raison de cette méconnaissance réside sans doute en partie dans le fait que PostgreSQL a longtemps été disponible uniquement sous Unix. La première version windows n'est apparue qu'à la sortie de la version 8.0 du logiciel, en 2005.
PostgreSQL a longtemps été plus performant que MySQL, mais ces différences tendent à diminuer.
MySQL semble être aujourd'hui équivalent à PostgreSQL en terme de performances sauf pour quelques opérations telles que l'insertion de données et la création d'index.
Le langage procédural utilisé par PostgreSQL s'appelle le PL/pgSQL.
MS Access
MS Access ou Microsoft Access est un logiciel édité par Microsoft (comme son nom l'indique...) Par conséquent, c'est un logiciel payant qui ne fonctionne que sous Windows. Il n'est pas du tout adapté pour gérer un grand volume de données et a beaucoup moins de fonctionnalités que les autres SGBDR. Son avantage principal est l'interface graphique intuitive qui vient avec le logiciel.
SQLite
La particularité de SQLite est de ne pas utiliser le schéma client-serveur utilisé par la majorité des SGBDR. SQLite stocke toutes les données dans de simples fichiers. Par conséquent, il ne faut pas installer de serveur de base de données, ce qui n'est pas toujours possible (certains hébergeurs web ne le permettent pas par exemple).
Pour de très petits volumes de données, SQLite est très performant. Cependant, le fait que les informations soient simplement stockées dans des fichiers rend le système difficile à sécuriser (autant au niveau des accès, qu'au niveau de la gestion de plusieurs utilisateurs utilisant la base simultanément).
Organisation d'une base de données
Bon, vous savez qu'une base de données sert à gérer les données. Très bien. Mais comment ?? Facile ! Comment organisez-vous vos données dans la "vie réelle" ?? Vos papiers par exemple ? Chacun son organisation bien sûr, mais je suppose que vous les classez d'une manière ou d'une autre.
Toutes les factures ensemble, tous les contrats ensemble, etc. Ensuite on subdivise : les factures d'éléctricité, les factures pour la voiture. Ou dans l'autre sens, tous les papiers concernant la voiture ensemble, puis subdivision en taxes, communication avec l'assureur, avec le garagiste, ...
Une base de données, c'est pareil ! On classe les informations. MySQL étant un SGBDR, je ne parlerai que de l'organisation des bases de données relationelles.
Comme je vous l'ai dit plus haut, on représente les données sous forme de tables. Une base va donc contenir plusieurs tables (elle peut n'en contenir qu'une bien sûr, mais c'est rarement le cas). Si je reprends mon exemple précédent, on a donc une table représentant des clients (donc des personnes).
Chaque table définit un certain nombre de colonnes, qui sont les caractéristiques de l'objet représenté par la table (les attributs de l'en-tête dans la théorie relationelle). On a donc ici une colonne "nom", une colonne "prénom", une "email" et une colonne numéro qui nous permet d'identifier les clients individuellement (les nom et prénom ne suffisent pas toujours).
Numéro Nom Prénom Email
1 Jean Dupont
2 Marie Malherbe
3 Nicolas Jacques [email protected]
4 Hadrien Piroux
Si je récapitule, dans une base nous avons donc des tables, et dans ces tables, on a des colonnes. Dans ces tables, vous introduisez vos données. Chaque donnée introduite le sera sous forme de ligne dans une table, définissant la valeur de chaque colonne pour cette donnée.
Bien, ça, c'est fait.
Installons donc tout ça sur l'ordinateur, et c'est parti !!!
Installation de MySQL
Maintenant qu'on sait à peu près de quoi on parle, il est temps d'installer MySQL sur l'ordinateur, et de commencer à l'utiliser. Au programme de ce chapitre :
Installation de MySQL
Connexion et déconnexion au client MySQL Création d'un utilisateur
Bases de la syntaxe du langage SQL
Avant-propos
Il existe plusieurs manières d'utiliser MySQL. La première, que je vais utiliser tout au long du tutoriel, est l'utilisation en ligne de commande.
Ligne de commande
Mais qu'est-ce donc ?
Eh bien il s'agit d'une fenêtre toute simple, dans laquelle toutes les instructions sont tapées à la main. Pas de bouton, pas de zone de saisie. Juste votre clavier.
Les utilisateurs de Linux connaissent très certainement. Pour Mac, il faut utiliser l'application "Terminal" que vous trouverez dans Applications > Utilitaires. Quant aux windowsiens, c'est le "Command Prompt" que vous devez trouver (Démarrer > Tous les programmes > Accessoires).
Interface graphique
Si l'on ne veut pas utiliser la ligne de commande (il faut bien avouer que ce n'est pas très sympathique cette fenêtre monochrome), on peut utiliser une interface graphique, qui permet d'exécuter pas mal de choses simples de manière intuitive sur une base de données.
Comme interface graphique pour MySQL, on peut citer MySQL Workbench, PhpMyAdmin (souvent utilisé pour créer un site web en combinant MySQL et PHP) ou MySQL Front par exemple.
Pourquoi utiliser la ligne de commande ?
C'est vrai ça, pourquoi ? Si c'est plus simple et plus convivial avec une interface graphique ? Deux raisons :
primo, parce que je veux que vous maîtrisiez vraiment les commandes. En effet, les interfaces graphiques permettent de faire pas mal de choses, mais une fois que vous serez bien lancés, vous vous mettrez à faire des choses subtiles et compliquées, et il ne serait pas étonnant qu'il vous soit obligatoire d'écrire vous-même vos requêtes ;
ensuite, parce qu'il est fort probable que vous désiriez utiliser MySQL en combinaison avec un autre langage de programmation (et si ce n'est pas votre but immédiat, ça viendra probablement un jour). Or, dans du code PHP (ou Java, ou Python, ou...), on ne va pas écrire "Ouvre PhpMyAdmin et clique sur sur le bon bouton pour que je puisse insérer une donnée dans la base". Non, on va devoir écrire en dur les requêtes. Il faut donc que vous sachiez comment faire.
Bien sûr, si vous voulez utiliser une interface graphique, je ne peux guère vous en empêcher. Mais je vous encourage vivement à commencer par utiliser la ligne de commande, ou au minimum de faire l'effort de décortiquer les requêtes que vous laisserez l'interface graphique construire pour vous. Ceci afin de pouvoir les écrire vous-même le jour où vous en aurez besoin (et ce jour viendra, je vous le prédis).
Installation du logiciel
…
Sélectionnez l'OS sur lequel vous travaillez (Windows, Mac OS, Linux...).
Windows
Téléchargez MySQL avec l'installeur (MSI Installer), puis exécutez le fichier téléchargé. L'installeur démarre et vous guide lors de l'installation.
Lorsqu'il vous demande de choisir entre trois types d'installation, choisissez "Typical". Cela installera tout ce dont nous pourrions avoir besoin.
L'installation se lance. Une fois qu'elle est terminée, cliquez sur "Terminer" après vous être assuré que la case "lancer l'outil de configuration MySQL" est cochée.
Dans cet outil de configuration, choisissez la configuration standard, et à l'étape suivante, cochez l'option "Include Bin Directory in Windows PATH"
On vous propose alors de définir un nouveau mot de passe pour l'utilisateur "root". Choisissez un mot de passe et confirmez-le. Ne cochez aucune autre option à cette étape. Cliquez ensuite sur "Execute" pour lancer la configuration.
Mac OS
Téléchargez l'archive DMG qui vous convient (32 ou 64 bit), double-cliquez ensuite sur ce .dmg pour ouvrir l'image disque. Vous devriez y trouver 4 fichiers dont deux .pkg. Celui qui nous intéresse s'appelle mysql-5.5.9-osx10.6-x86_64.pkg (les chiffres peuvent changer selon la version de MySQL téléchargée et votre ordinateur). Ouvrez ce fichier, qui est en fait l'installateur de MySQL, et suivez les instructions.
Une fois le programme installé, vous pouvez ouvrir votre terminal (pour rappel, il se trouve dans Applications -> Utilitaires). Tapez les commandes et exécutez les instructions suivantes :
Code : Console
Entrez votre mot de passe si nécessaire Tapez Ctrl + Z
Code : Console
Tapez Ctrl + D Quittez le terminal
MySQL est prêt à être utilisé !
Configuration
Par défaut, aucun mot de passe n'est demandé pour se connecter, même avec l'utilisateur root (qui a tous les droits). Je vous propose donc de définir un mot de passe pour cet utilisateur :
Code : Console
Ensuite, pour pouvoir accéder directement au logiciel client depuis la console, sans devoir aller dans le dossier où est installé le client, il vous faut ajouter ce dossier à votre variable d'environnement PATH. Pour cela, tapez la commande suivante dans le terminal :
Code : Console
/usr/local/mysql/bin étant donc le dossier dans lequel se trouve le logiciel client (plusieurs logiciels clients en fait). Redémarrez votre terminal pour que le changement prenne effet.
Linux
N'ayant pas d'installation Linux à portée de main, je ne peux malheureusement pas vous détailler la marche à suivre pour cette plateforme, mais vous devriez trouver de nombreuses informations sur internet en cas de problème et/ou question.
Connexion à MySQL
Je vous ai dit que MySQL était basé sur un modèle client - serveur, comme la plupart des SGBD. Cela implique donc que votre base de données se trouve sur un serveur auquel vous n'avez pas accès directement, il faut passer par un client qui fera la liaison entre vous et le serveur.
Lorsque vous installez MySQL, plusieurs choses sont donc installées sur votre ordinateur :
un serveur de base de données MySQL ;
plusieurs logiciels clients qui permettent d'interagir avec le serveur.
Connexion au client
Parmi ces clients, celui dont nous allons parler à présent est mysql (original comme nom ). C'est celui que vous utiliserez tout au long de ce tutoriel pour vous connecter à votre base de données et y insérer, consulter et modifier des données. La commande pour lancer le client est tout simplement son nom :
Code : Console
Cependant cela ne suffit pas. Il vous faut également préciser un certain nombre de paramètres. Le client mysql a besoin d'au minimum trois paramètres :
l'hôte : c'est-à-dire l'endroit où est localisé le serveur ; le nom d'utilisateur ;
le mot de passe de l'utilisateur.
L'hôte et l'utilisateur ont des valeurs par défaut, et ne sont donc pas toujours indispensables. La valeur par défaut de l'hôte est "localhost", ce qui signifie que le serveur est sur le même ordinateur que le client. C'est bien notre cas, donc nous n'aurons pas à préciser ce paramètre. Pour le nom d'utilisateur, la valeur par défaut dépend de votre système. Sous Windows, l'utilisateur courant est "ODBC", tandis que pour les systèmes Unix (Mac et Linux), il s'agit de votre nom d'utilisateur (le nom qui apparait dans l'invite de commande).
Pour votre première connexion à MySQL, il vous faudra vous connecter avec l'utilisateur "root", pour lequel vous avez normalement défini un mot de passe (si vous ne l'avez pas fait, inutile d'utiliser ce paramètre, mais ce n'est pas très sécurisé). Par la suite, nous créerons un nouvel utilisateur.
Pour chacun des trois paramètres, deux syntaxes sont possibles :
Code : Console
Remarquez l'absence d'espace entre -p et le mot de passe. C'est voulu (mais uniquement pour ce paramètre-là), et souvent source d'erreurs.
La commande complète pour se connecter est donc :
Code : Console
J'utiliserai uniquement les paramètres courts à partir de maintenant. Choisissez ce qui vous convient le mieux.
Notez que pour le mot de passe, il vous est possible (et c'est même très conseillé) de préciser uniquement que vous utilisez le paramètre, sans lui donner de valeur :
Code : Console
Apparaissent alors dans la console les mots suivants :
Code : Console
Tapez donc votre mot de passe, et là, vous pouvez constater que les lettres que vous tapez ne s'affichent pas. C'est normal,
cessez donc de martyriser votre clavier, il n'y peut rien le pauvre . Cela permet simplement de cacher votre mot de passe à d'éventuels curieux qui regarderaient par dessus votre épaule.
Donc pour résumer, pour me connecter à mysql, je tape la commande suivante :
Code : Console
J'ai omis l'hôte, puisque mon serveur est sur mon ordinateur. Je n'ai plus qu'à taper mon mot de passe et je suis connecté.
Déconnexion
Pour se déconnecter du client, il suffit d'utiliser la commande quit ou exit .
Syntaxe SQL et premières commandes
Maintenant que vous savez vous connecter, vous aller enfin pouvoir discuter avec le serveur MySQL (en langage SQL évidemment). Donc, reconnectez-vous si vous êtes déconnecté.
Vous pouvez constater que vous êtes connectés grâce au joli (quoiqu'un peu formel) message de bienvenue, ainsi qu'au changement de l'invite de commande. On voit maintenant mysql> .
"Hello World !"
Traditionnellement, lorsque l'on apprend un langage informatique, la première chose que l'on fait, c'est afficher le célèbre
message "Hello World !". Pour ne pas déroger à la règle, je vous propose de taper la commande suivante (sans oublier le ; à la fin) :
Code : SQL
SELECT est la commande qui permet la sélection de données, mais aussi l'affichage. Vous devriez donc voir s'afficher "Hello World !"
Code : Console
Comme vous le voyez, "Hello World !" s'affiche en réalité deux fois. C'est parce que MySQL représente les données sous forme de table. Il affiche donc une table avec une colonne, qu'il appelle "Hello World !" faute de meilleure information. Et dans cette table nous avons une ligne de données, le "Hello World !" que nous avons demandé.
Syntaxe
Avant d'aller plus loin, voici quelques règles générales à retenir concernant le SQL, qui comme tout langage informatique, obéit à des règles syntaxiques très strictes.
Fin d'une instruction
Pour signifier à MySQL qu'une instruction est terminée, il faut mettre le caractère ;. Tant qu'il ne rencontre pas ce caractère, le client MySQL pense que vous n'avez pas fini d'écrire votre commande, et attendra gentiment que vous continuiez.
Par exemple, la commande suivante devrait afficher 100. Mais tant que MySQL ne recevra pas de ;, il attendra simplement la suite.
Code : SQL
En appuyant sur la touche Entrée vous passez à la ligne suivante, mais la commande ne s'effectue pas. Remarquez au passage le changement dans l'invite de commande. mysql> signifie que vous allez entrer une commande, tandis que -> signifie que vous allez entrer la suite d'une commande commencée précédemment.
Tapez maintenant ; puis appuyer sur Entrée. Ca y est, la commande est envoyée, l'affichage se fait ! Ce caractère de fin d'instruction obligatoire va vous permettre :
d'écrire une instruction sur plusieurs lignes ;
et d'écrire plusieurs instructions sur une seule ligne.
Voyons donc ça !
Instructions sur plusieurs lignes
Tapez les commandes suivantes dans votre console, retour à la ligne compris.
Code : Console
C'est quand même plus clair que tout sur une ligne :
Code : Console
N'hésitez pas à écrire vos commandes en plusieurs lignes, vous verrez beaucoup plus clair d'une part, et d'autre part, ça vous permettra de trouver vos erreurs plus facilement.
Démonstration
Code : Console
Résultat :
Code : Console
MySQL me donne le numéro de la ligne à laquelle je me suis trompée (2), et me dit que c'est au niveau de 'troisième ligne'. Je regarde la ligne en question, et effectivement, une virgule m'avait échappé à la fin de la ligne 2 (juste avant 'troisième ligne' donc). Si vous mettez tout en une ligne, invariablement MySQL vous dira que l'erreur se situe sur la ligne 1. Forcément...
Notez que bien souvent, il vous faudra remonter à la ligne précédant celle signalée. En effet, si MySQL tombe sur quelque chose qui ne lui plaît pas en début de ligne sept, c'est peut-être parce que vous avez oublié ou ajouté quelque chose (une parenthèse, une virgule, un mot-clé) à la ligne six.
Plusieurs instructions sur une ligne
Copiez-collez la ligne suivante dans votre console et appuyer sur Entrée.
Code : Console
