TP Base de données Installation d'un serveur MySQL et

BTS IRIS 1 Année
Lycée Diderot, Paris
TP Base de données
Installation d'un serveur MySQL et
initiation au langage SQL
Gilles Dalles
TP Base de données
Installation d'un serveur MySQL et initiation au langage SQL
Gilles Dalles
1. I NTRODUCTION
Le but aujourd'hui est de se familiariser avec le langage SQL (Structured Query
Language, .langage de requête structurée pour les anglophobes).
Pour cela, il va vous falloir installer un SGBD, créer un utilisateur, gérer des droits,
créer une base et ses tables, remplir ces tables de données, exploiter ces données,
faire des sauvegardes de cette base et bien d'autres joyeusetés encore.
2. I NSTALLATION D ' UN SGBD
Pour rappel, un SGBD est un Système de Gestion de Base de Données. Nous allons
utiliser MySQL pour ce TP pour quelques raisons simples:
Il est gratuit
Il s'installe facilement
Il respecte en grande partie la norme SQL
C'est un paquet disponible pour Ubuntu
L'installation ne devrait pas vous poser trop de problèmes, il s'agit d'installer le
paquet mysql-server. A priori, on devrait vous demander à l'installation un mot de
passe pour l'utilisateur root: il s'agit de l'utilisateur administrateur du serveur de
base de données. Je vous suggère fortement d'utiliser iris_1 comme mot de passe.
Pour vérifier que le serveur tourne bien sur votre machine une fois l'installation
terminée, une commande s'impose: ps. Combinée avec une autre, grep, et vous
devriez faire des merveilles et vérifier très simplement si le serveur est bien présent.
3. C REATION
DE BASE , D ' UTILISATEUR ET PERMI SSIONS
Si on a un serveur, à priori il nous faut un client pour avoir accès à ses ressources. Le
client MySQL est à priori installé par défaut avec Ubuntu. Si ce n'est pas le cas chez
vous, vous savez ce qu'il vous reste à faire.
QUELLE EST LA SYNTAXE DE LA COMMANDE MYSQL POUR SE CONNECTER AU
SERVEUR? (INDICE: IL VOUS FAUT LE LOGI N, L'HOTE ET BIEN SUR LE MOT DE
PASSE)
Une fois connecté, il vous faut créer votre base de données. Tout d'abord, vous
pouvez vérifier si des bases existent déjà. Pour cela, tapez simplement la requête
suivante: SHOW DATABASES; (le ; n'est pas facultatif, il marque la fin de la requête)
EXISTE-T-IL DES BASES? SI OUI, LESQUELLES?
3.1.
C R E AT I O N D ' U N E
B AS E D E DO N N E E S
Vous allez désormais créer la base de données bibliotheque (oui, sans accent, sans
majuscule). Si vous tapez help à l'invite de mysql, vous obtiendrez 3 liens internet.
Celui qui nous intéresse est celui dédié au développement. Dans l'onglet
Documentation du site, vous trouverez plusieurs liens vers la documentation en
fonction de la version de MySQL utilisée. Trouvez la votre.
2
TP Base de données
Installation d'un serveur MySQL et initiation au langage SQL
Gilles Dalles
La requête qui permet de créer une base s'appelle CREATE. Vous la trouverez dans le
manuel au chapitre SQL Statement Syntax.
On souhaite que la base bibliotheque ait pour jeu de caractères par défaut latin1 et
pour collation latin1_swedish_ci. Pour information, une collation (COLLATE)
détermine les caractères qui doivent être regroupés et considérés comme
équivalents (ex: les caractères accentués 'à' et 'â' faisant partie du groupe 'a', ou
encore les caractères 'A' et 'a'.) Cela permet d'assouplir les recherches
d'informations dans une table.
QUELLE EST LA SYNTAXE DE LA REQUETE PERMET TANT DE CREER LA BAS E
BIBLIOTHEQUE AVEC CE S CARACTERISTIQUES?
Vérifiez que votre base existe bien. Si vous vous trompez, c'est mal, mais on peut
réparer ça grâce à la requête DROP.
QUE FAIT LA REQUETE DROP?
3.2.
C R E AT I O N D ' U N
UT I LI S AT EU R
Un des aspects d'un SGBD est de pouvoir gérer plusieurs bases de données, et bien
sur de créer les utilisateurs qui auront des droits sur ces bases. On peut cloisonner
tout cela. Par exemple, l'utilisateur dalles pourra administrer la BDD bibliotheque
mais n'aura aucun accès à la BDD service_financier. De même, l'utilisateur j_loquet
ne pourra que consulter la BDD bibliotheque.
Vous allez donc créer un administrateur de votre base du nom de a_france
(référence à Anatole France). Dans un premier temps, on ne s'occupera pas des
privilèges de cet utilisateur.
La requête ressemble étrangement à celle vue en 3.1.
QUELLE EST LA SYNTAXE DE LA REQUETE PERMETTANT DE CREER UN
UTILISATEUR EN SPECIFIANT SON MOT DE PASSE ET L'HOTE (LOCALHOST
DANS NOTRE CAS)?
Pour vérifier que votre utilisateur a bien été créé, vous pouvez consulter la table
user de la base mysql.
Pour cela, sélectionnez la base mysql avec la commande use (vue dans l'aide
précédemment).
Ensuite, vérifiez qu'il existe bien une table user grâce à la requête SHOW (indice:
vous voulez que la requête vous montre des tables).
Toujours grâce à SHOW, vous pouvez aussi voir les champs qui composent la table
user. (indice: vous désirez voir des champs cette fois-ci, pensez à la traduction
anglaise de ce mot)
3
TP Base de données
Installation d'un serveur MySQL et initiation au langage SQL
Gilles Dalles
La requête SELECT vous permettra de visualiser le contenu de la table user.
Q U E L L E E S T L A S Y N T A X E D E L A R E Q U E T E SE LEC T V O U S P E R M E T T A N T D E
N'AFFICHER QUE L'HOTE, LE NOM D'UTILISATEUR ET SON MOT DE PASSE?
3.3.
P R I V I LE G E S
Votre utilisateur a_france peut désormais se connecter au serveur et c'est à peu
près tout. Il n'a aucun droit sur le serveur.
Vous allez donc lui attribuer les droits d'administrer la base bibliotheque. Pour ça, il
existe la requête GRANT.
Q U E L L E E S T L A S Y N T A X E D E L A R E Q U E T E G RA NT P E R M E T T A N T D ' A C C O R D E R
TOUS LES PRIVILEGES A L'UTILISATEUR A _FRANCE SUR LA BASE
BIBLIOTHEQUE ?
Une fois votre utilisateur créé, déconnectez vous et reconnectez vous en tant que
a_france.
4. C REATION
DE TABLES
Par rapport à l'activité que nous avons eue précédemment, vous devez être en
mesure de créer les tables de la base bibliotheque. Il faut toutefois se pencher sur le
type de chaque champs, le moteur à utiliser (InnoDB pour nous, nous simplifiera la
vie sur la suppression de certains enregistrements), définir les clés primaires, établir
les relations entre tables, etc…
Dans un premier temps, ne vous préoccupez pas d'établir les clés primaires,
étrangères et tutti quanti. Faites vos tables le plus simplement possible. Ceci étant
dit, il est plus simple de tout faire d'un coup, si vous vous sentez l'âme aventureuse,
je ne vous empêcherais pas de procéder ainsi.
QUELLE REQUETE PERMET DE CREER UNE TABLE ?
On a le droit de se tromper: oublier un champ, se tromper de type de variable, avoir
un champ en trop, etc…
Du coup, évidemment, on peut tout effacer et recommencer (DROP) ou modifier
l'existant: ALTER TABLE est là pour ça.
Pour les moins aventureux, donc, c'est par cette requête qu'il vous faudra définir vos
clés primaires. Attention, le fait de rendre un champ auto incrémenté nécessite qu'il
soit défini également comme index, dans notre cas, une clé primaire.
Les clés étrangères (foreign keys) sont aussi à spécifier. InnoDB vous permet de
sélectionner les actions à réaliser en cas de:
Suppression de la clé primaire (de l'enregistrement qu'elle identifie)
référencée (ON DELETE)
Modification de l'enregistrement référencé par la clé primaire référencée
(ON UPDATE)
4
TP Base de données
Installation d'un serveur MySQL et initiation au langage SQL
Gilles Dalles
Ces actions sont au nombre de 3 (enfin 4 mais bon):
CASCADE: modifie ou supprime en cascade. Par exemple, une clé primaire
est supprimée, sa référence dans les autres tables via les clés étrangères est
également supprimée
SET NULL: les clés étrangères sont mises à zéro. Les enregistrements dont
elles font partie sont alors conservés
RESTRICT ou NO ACTION: aucune répercussion sur les clés étrangères.
On choisira de base CASCADE pour ON DELETE et RESTRICT pour ON UPDATE.
Petit conseil, il ne serait peut être pas idiot d'écrire et de regrouper vos requêtes de
création de table sur un fichier texte. La correction sera bien plus simple en cas
d'erreur.
5. I NSERTION
ET SELECTION DE DONNEES
Une fois les tables faites, il faut les remplir. Et donc, il va vous falloir faire preuve
d'imagination. Pour rappel, nous élaborons une base de données pour une
bibliothèque. Il vous faut comme informations:
Des titres de livres
Des auteurs
Des éditeurs
Des genres
Des clients de la bibliothèque
La requête INSERT INTO vous permettra d'ajouter des enregistrements à vos tables.
Evidemment, vous pouvez aussi mettre à jour vous enregistrements, la requête
UPDATE est là pour ça. Et enfin, si vous souhaitez supprimer un enregistrement,
DELETE vous y aidera simplement.
5.1.
S O US - R EQ U ET ES
Les clés étrangères posent (encore) des problèmes: en effet, comment insérer un
enregistrement dont la valeur de l'un des champs nous est inconnue?
L'un des moyens d'y parvenir est d'utiliser la requête INSERT INTO avec en guise de
valeur à insérer une sous-requête SELECT. Attention, vous devez être certain que la
sous-requête ne retournera qu'un seul résultat.
C'est un bon moyen de remplir les tables qui n'établissent que des relations entre
clés étrangères.
5