tuto mysql wampserver

Rf : tuto_MySQL_WampServer.doc

TIC UIAD Cration de site internet 2me anne Daniel Gaudin Jean Claude Chne page 1/31

Les BASES de DONNEES dans WampServer

1 Dfinitions Gnrales

Une base de donnes (BDD) permet d'enregistrer, de stocker, de ranger des donnes de faon organise et hirarchise.

SQL est le langage qui permet de manipuler les bases de donnes.

Les SGBD (Systmes de Gestion de Base de Donnes) sont les programmes qui vous permettent de grer vos donnes directement sans utiliser de script PHP.

Les plus connus sont :

MySQL : libre et gratuit, c'est probablement le SGBD le plus connu. Nous l'utiliserons

PostgreSQL : libre et gratuit comme MySQL, avec plus de fonctionnalits mais un peu moins connu.

SQLite: libre et gratuit, trs limit en fonctionnalits.

Oracle : utilis par les entreprises, un des SGBD les plus complets mais payant.

Microsoft SQL Server : le SGBD de Microsoft.

1.1 Structure de la base de donnes La base dans le langage SQL, permet de classer les informations contenues dans les tables. Une table contient des donnes diffrentes. Une base contient au maximum 256 tables. C'est l que sont enregistres les donnes, sous la forme d'un tableau.

Dans ce tableau, les colonnes sont appeles des champs, et les lignes sont appeles des entres.



1.2 Gestion des Bases de Donnes La base de donnes gre les informations sous forme de tableaux, MySQL enregistre des informations, les crit et les enregistre dans des fichiers ! Ces fichiers sont quelque part sur votre disque dur. Il faut toujours utiliser MySQL qui va se charger d'extraire et de modifier les informations dans ces fichiers.

Chaque SGBD a sa propre faon d'enregistrer les donnes

Par exemple, avec MySQL sous Windows si vous utilisez WAMP, vous devriez trouver les fichiers o sont stockes les informations dans C:\wamp\mysql\data.

On va devoir communiquer avec la BDD pour lui donner l'ordre de rcuprer ou d'enregistrer des donnes. Pour "parler" avec le BDD, on utilise le langage SQL (Structured Query Language).

Le langage SQL permet de construire de puissantes BDD relationnelles, quelque soit la BDD utilise.

2 UTILISATION avec PHPMyADMIN

Nous allons donc utiliser MySQL qui est fourni dans WampServer.

PhpMyAdmin est linterface dutilisation de MYSQL, bas sur le PHP et le HTML, de WampServer

Lorsque nous lanons WampServer nous avons licne dans notre barre des tches. Si nous cliquons sur cette icne nous ouvrons une fentre de commande dans laquelle nous pouvons cliquer sur phpMyAdmin .



Nous ouvrons alors la fentre suivante :

2.1 CREER UNE BASE DE DONNEES Lancer Wamp server et ouvrir phpMyAdmin nous avons alors dans la fentre (1) un champ (2) Crer une base de donns :

Donner un nom la base : base_essai et cliquer sur crer



Nous avons alors confirmation de la cration dans un cadre entour de vert et juste dessous la ligne de code SQL qui a t gnre pour effectuer cette opration.

Cette information est trs importante car elle nous permettra par la suite de copier/coller du code sans avoir le retaper intgralement !

Dans la colonne gauche en bleu nous avons notre base avec entre parenthses le nombre de table incorpores ; 0 pour linstant puisque nous nen avons encore cre aucune !

Nb : le champ Interclassement concerne divers langages ; exemple :

Nous laissons WampServer grer cela.



2.2 Crer et Grer des Tables Pour cela nous slectionnons notre base en cliquant dessus :

Nous nommons la table et dfinissons le nombre de colonnes souhait (champs) ici table_nom et 2 colonnes.

Lcran suivant souvre :

On remplit chaque champ

Un index Id, type entier INT qui servira de cl primaire, taille/valeur 4 chiffres, dans index mettre INDEX et cocher la case AUTO_INCREMENT.

Un champ Nom qui pourra contenir une chane de caractres avec le type VARCHAR de 30 caractres dans le champ taille/valeur

On ignore les autres champs.



Puis on SAUVEGARDE (si on excute on cre un nouveau champ) la table est enregistre et apparat dans la liste

Linstruction SQL apparat bien sous le message encadr de vert de confirmation dexcution.

Remarquons que la colonne NULL indique linformation Non ce qui ce traduit dans les lignes de code SQL par NOT NULL et signifie qui les champs ne doivent pas tre vide. Si nous avions voulu accepter des champs vides il aurait fallu cocher la case NULL (ligne en vert) dans lcran de cration des colonnes.

De la mme faon crons une deuxime table table_activites avec 3 colonnes tennis petanque bridge :

Nous avons maintenant notre base de donnes avec deux tables.



2.3 Les Onglets de gestion Nous voyons en haut de lcran des onglets de navigation. Chaque onglet a une fonction. Celui sur lequel nous sommes, Structure: affiche les divers champs ci-dessus.

SQL : permet de lancer une instruction directement en SQL

Rechercher : un enregistrement, divers paramtres, permet de faire des Requtes (une recherche croise entre les champs de diffrentes tables) nous verrons cela plus en dtail.



Insrer : cette commande sert insrer des donnes dans une table de notre base. Il convient douvrir la table et de cliquer sur longlet Insrer

Notez : si notre table est vide nous ne pouvons lafficher et nous avons cet cran :

Une fentre de saisie des donnes souvre. Elle nous propose deux champs de saisie dans lesquels nous pouvons inscrire des donnes. Le champ Id nest pas renseigner puisquil sauto-incrmente .

Il faut ensuite cliquer sur Excuter pour insrer les donnes dans la table.

ATTENTION, si on ne rentre des donnes que dans le deuxime champ et que lon clique sur excuter les deux champs seront implments dans la table, le premier tant vide.

Une fois excute la saisie nous obtenons cet cran et la table ntant plus vide nous pouvons lafficher.

.

Nous voyons alors nos saisies et l Id a t incrment automatiquement, l il commence 5 car nous avons fait des essais auparavant et les avons effacs.



Exporter : la base, quivaut enregistrer sous pour sauvegarder

Il faut vrifier que toutes les tables sont bien slectionnes et que le choix est bien sur SQL puis cliquer sur Excuter.

Une fentre souvre et lon choisi lemplacement pour enregistrer le fichier en .sql !



Importer : une base, sert ouvrir et implmenter dans WampServer une ou des tables dans une base, existante ou crer avant limportation, partir dun fichier en .sql (ou autre sil tait besoin comme .zip par ex.)

Choisir le fichier dans lemplacement avec cette commande et cliquer sur Excuter.

Oprations : permet de crer, modifier, renomer, vider les bases et les tables.

Longlet Privilges permet la gestion des utilisateurs. Gnralement lon travaille sous Root mais il peut tre utile de crer plusieurs utilisateurs quand on gre plusieurs bases surtout partir dun site Web !.



Pour cela il faut cliquer sur Ajouter un utilisateur et renseigner les champs puis cliquer sur Excuter.

Lutilisation du bouton Gnrer un mot de passe est dconseille car le mot de passe gnr est trs long !

Enfin longlet Supprimer sert effacer compltement une table ou une base. A utiliser avec prudence !



3 MANIPULATION des donnes dans WampServer

Avant de manipuler les donnes rentres dans notre table nous allons prciser un certain nombre de caractristiques possibles utilises par MySQL.

3.1 Les types de champs MySQL

Allons dans longlet Structure de notre table et ajoutons une colonne en fin de table. Lorsque lon clique sur Excuter nous ouvrons cette fentre :

Droulons la liste des types que vous propose MySQL sur la ligne Type :

Alors que PHP ne propose que quelques types de donnes (int, string, bool...), MySQL propose une quantit trs importante de types de donnes.



En fait, ceux-ci sont classs par catgories :

NUMERIC : ce sont les nombres. On y trouve des types ddis aux petits nombres entiers (TINYINT), aux gros nombres entiers (BIGINT), aux nombres dcimaux (DOUBLE), etc.

DATE and TIME : ce sont les dates et les heures. De nombreux types diffrents permettent de stocker une date, une heure, ou les deux la fois.

STRING : ce sont les chanes de caractres. L encore, il y a des types adapts toutes les tailles.

SPATIAL : cela concerne les bases de donnes spatiales, utile pour ceux qui font de la cartographie. Ce ne sera pas notre cas, donc nous n'en parlerons pas ici.

Attention : si dans un champ numrique le 0 est en premier il ne sera pas pris en compte. Il faut donc utiliser un champ caractre pour par exemple stocker des numros de tlphone.

En fait, phpMyAdmin a eu la bonne ide de proposer au tout dbut de cette liste les 4 types de donnes les plus courants : INT : nombre entier. VARCHAR : court texte (entre 1 et 255 caractres). TEXT : long texte (on peut y stocker un roman sans problme). DATE : date (jour, mois, anne).

Nous n'aurons besoin de jongler qu'entre ces 4 types, donc ce sont eux qu'il faut retenir. Cela couvrira 99% des besoins. Vous pouvez aussi garder en tte le type DOUBLE qui permet de stocker des nombres dcimaux. Une petite remarque propos de VARCHAR : c'est un type adapt aux courts textes, vous devez indiquer la taille maximale du champ (entre 1 et 255). Si vous ne le faites pas, vous ne pourrez pas crer la table.



3.2 Les cls primaires

Toute table doit possder un champ qui joue le rle de cl primaire. La cl primaire permet d'identifier de manire unique une entre dans la table. En gnral, on utilise le champ "Id" comme cl primaire par dfaut. Chaque enregistrement de votre site doit pouvoir tre identifi de manire unique. Le moyen le plus simple pour cela est de lui donner un numro unique, dans un champ nomm "Id". Il ne peut pas y avoir deux enregistrements avec le mme Id dans la mme table ou dans deux tables dune mme base de donnes !

Il est vital que chaque table possde sa cl primaire. Des tables sans cl primaire auront leurs performances extrmement rduites. Crer chaque fois ce champ "Id" en lui donnant l'index PRIMARY, ce qui aura pour effet d'en faire une cl primaire, cocher la case AUTO_INCREMENT afin que ce champ gre lui-mme les nouvelles valeurs automatiquement chaque nouvelle saisie.

3.3 Les REQUTES

Nous avons vu que longlet Rechercher nous permet de faire des requtes dans nos tables.

Une requte SQL est un texte qui donne un ordre excuter un moteur de base de donnes sur une base de donnes. On utilise la commande SELECT. Elle peut concerner un enregistrement ou plusieurs, elle peut mettre en jeu une table ou plusieurs. La requte est compose de mots cls, les commandes SQL (qui sont pour linstant gnres automatiquement par WampServer).

Reprcisons galement que les commandes SQL ne sont pas sensibles la casse ni l'criture mais les champs sont sensibles la syntaxe.



Pour cet exemple nous importons une nouvelle table, jeu_video , dans notre base_essai afin de disposer de donnes plus nombreuses et plus varies pour que notre dmonstration soit plus significative.

Cette table possde 50 Entres qui ont chacune 7 colonnes, elle a cet aspect :

Nous allons donc dans longlet Rechercher et nous obtenons cette vue :

Nous voyons que toutes les colonnes de la structure de notre table sont affiches et que pour chacune delles nous avons un choix possible d Oprateur .



Ce choix est diffrent suivant le TYPE de donnes de notre colonne.

Les TYPE numriques (int, double,) et les TYPE alphanumriques (varchar, text,)

..

Les oprateurs de comparaison permettent de confronter deux valeurs, et renvoient un rsultat selon que celles-ci sont identiques (true) ou pas (false). MySQL propose quelques options en plus des oprateurs habituels.

Attention : tous ne sont pas proposs par WampServer en automatique et tous ne sutilise pas avec tous les types de donnes; on ne va pas utiliser une comparaison de chaine de caractre comme REGEXP avec une valeur numrique par exemple.

Exemple de requtes :

Recherchons dans notre table tous les jeux fonctionnant sur PC :



Qui nous donnera le rsultat suivant :

Recherchons maintenant si Michel a test des jeux sous PC :

Voici le rsultat :

Notez le code SQL gnr par WampServer :

SELECT *

FROM `jeux_video WHERE`possesseur`='michel'

AND `console` LIKE 'PC'

LIMIT 0, 30



Enfin dernier exemple recherchons dans les commentaires le mot meilleur :

Et voyons le rsultat :

Il existe galement dautres oprateurs permettant dautre fonction sutilisant toujours avec la commande SELECT, comme par exemple ORDER BY qui permet de trier une liste de rsultats nous verrons tout cela dans la section suivante consacre la syntaxe du langage SQL !



4 Le LANGAGE SQL

Noublions pas que le but est daccder aux donnes de la base de donnes afin de les manipuler pour obtenir le rsultat qui nous intresse.

Nous avons donc appris crer, implmenter et manipuler des bases de donnes avec SQL par lintermdiaire du SGBD MySQL fourni dans WampServer.

Nous allons maintenant tudier un peu plus en dtail le langage SQL lui-mme afin de pouvoir plus tard manipuler nos bases de donnes laide de script PHP inclus dans des pages HTML via notre site.

Une commande SQL est aussi appele instruction SQL ou requte SQL. SQL fournit un ensemble de commandes pour une varit de tches, dont:

la cration et la modification du schma de la BDD, linterrogation de la base de donnes, linsertion, la mise jour et la suppression des donnes dans la base de donnes,

la dfinition de vues, le contrle de laccs aux donnes, la cration d'index pour acclrer les interrogations

Nous allons donc taper directement des instructions SQL depuis une fentre de commande en ligne telle celle propose par WampServer via longlet SQL :

Lensemble des commandes SQL tant en anglais nous allons avant cela faire un petit rappel des principales dfinitions, commandes et du vocabulaire notre disposition.



4.1 Les Dfinitions

Abrviation anglaise

Signification anglaise

Abrviation franaise

Signification franaise

Remarque

DB Data Base BD ou BDD Base de Donnes

DBMS Data Base Management

System

SGBD Systme de Gestion de Base

de Donnes

SQL Structured Query Language

SQL Langage Structur de

Donnes

Langage dExtraction de

Donnes

DDL Data Definition Language

DDL Langage de Dfinition des

Donnes

Les Commandes Principales

DML Data Manipulation

Language

DML Langage de Manipulation des

Donnes

Les Commandes dOprations sur

les Donnes

TABLE Table

ROW Ligne

COLUMN Colonne

DATATYPE Type de Donnes

4.2 Le Vocabulaire de Commande

Commande Dfinition Syntaxe Exemple

CREATE Cration dune entit SQL CREATE entit

nom CREATE DATABASE

ma_base

SELECT Interrogation de la Base de Donnes

SELECT titre de colonne FROM table WHERE valeur choisie

SELECT console, prix FROM jeux_video WHERE prix



INSERT Insertion de donnes

INSERT INTO entit (champ1,champ2)

values (valeur1,valeur2)

INSERT INTO ma_table (nom, prnom)

values (Gaudin,Daniel)

UPDATE Mise jour de donnes

UPDATE entit SET champ =nouvelle

valeur WHERE champ = valeur

UPDATE ma_table SET nom=Jules WHERE

nom=Gaudin

ALTER Modification dune entit

ALTER entit ADD

Ou CHANGE

Ou MODIFY

Ou DROP

Ou RENAME action mener

ALTER TABLE `ma_table` ADD

`Age` INT(2) NOT NULL

USE Slectionne la base de travail USE base de donnes

Commande USE ma_base

SELECT

Nota : lutilisation de * avec SELECT dans la forme SELECT * entrane laffichage de toutes les colonnes de toutes les tables cites aprs FROM .

4.3 Les Oprateurs de comparaisons

Oprateur(s) Renvoi "true" si...

ou != ...les deux valeurs ne sont pas gales

< ...la valeur de gauche est strictement infrieure celle de droite

> ...la valeur de gauche est strictement suprieure celle de droite

= ...la valeur de gauche est strictement suprieure ou gale celle de droite

BETWEENAND ...la valeur teste est situe entre deux valeurs donnes

IN ...la valeur teste se situe dans une liste valeurs donnes

NOT IN ...la valeur teste ne se situe pas dans une liste de valeurs donnes



LIKE ...la valeur de gauche correspond celle de droite (celle de droite peux utiliser le caractre % pour simuler n'importe quel nombre de caractre,

et _ pour un seul caractre

NOT LIKE ...les deux valeurs ne correspondent pas

REGEXP ou RLIKE ...la valeur de gauche correspond l'expression rgulire donne

NOT REGEXP ...la valeur de gauche ne correspond pas l'expression rgulire donne

IS NULL ...la valeur teste est nulle

IS NOT NULL ...la valeur teste nest pas nulle

4.4 La syntaxe des lignes de commandes

Il est dusage pour se retrouver plus facilement dans la ligne de code, certaines peuvent tre trs longues, de mettre en majuscules les COMMANDES et en minuscules les informations. Mais MySQL est insensible la casse.

Les noms de base et de table ne peuvent contenir despaces ou des caractres spciaux, cest pourquoi nous y insrons des tirets bas _ . Bien quils soient accepts on vitera les caractres accentus dans les noms de base et de tables. Par contre nous pouvons les utiliser sans problme dans les titres de colonnes.

Les noms donns aux bases, tables, colonnes, champs, doivent tre mis entre (simple cote) dans la plupart des instructions.

Les valeurs cres ou donnes dans une instruction le sont entre () et spares par des virgules , .

Chaque instruction doit se terminer par un point-virgule ; .



Cela nous donne les commandes de base suivantes :

Pour crer une base : CREATE DATABASE base_essai ;

Pour crer une table dans notre base :

Il faut slectionner la base avec : USE base_essai ;

Crer en nommant la table : CREATE TABLE nom_de_table (pas de ; l car les attributs entre parenthses suivent directement cette commande)

Y mettre au minimum une ligne pour pouvoir crer la table : (nom_ligne VARCHAR(25)); (l nous prcisons que cette ligne sera de type caractres varis de 25 champs)

Notre ligne de commande aura donc cette structure :

CREATE TABLE nom_de_table (nom_ligne VARCHAR(25));

Maintenant il va falloir toffer notre table en y rajoutant lignes et structures !

Rajoutons une colonne :

ALTER table table_test ADD ref varchar(10) ;

Insrons-y des donnes :

INSERT INTO table_test VALUES (valeur1, valeur2) ;

Et faisons une requte :

SELECT ref FROM table_test ;



5 Exemples de codes SQL*

5.1 Base de Donnes une table

Crons notre base :

CREATE DATABASE employees ;

Crons une table 3 colonnes :

CREATE TABLE personal_info (first_name char(20) not null, last_name char(20) not null, employeeid int not null) ;

Ajoutons une colonne :

ALTER TABLE personal_info ADD salary DOUBLE NULL ;

Incrmentons nos premires valeurs dans la table :

INSERT INTO personal_info values('bart','simpson',1000,45000) ;

Insrons maintenant plusieurs valeurs en mme temps :

INSERT INTO èmployees`.`personal_info` (`first_name`, `last_name`, èmployeeid`, `salary`) VALUES ('mickey', 'mouse', '1001', '32000'), ('donald', 'duck', '1002', '26000'), ('peter', 'pan', '1003', '64000'), ('fritz', 'the cat', '1004', '33000');

Si nous voulons trier la liste et lafficher suivant un ordre prcis :

ALTER TABLE `personal_info` ORDER BY èmployeeid` ;

Pour afficher une colonne :

SELECT last_name FROM personal_info ;

Pour afficher les valeurs dune colonne avec une condition :

SELECT * FROM personal_info WHERE salary > 40000 ;

Pour mettre jour toutes les valeurs dune colonne :

UPDATE personal_info SET salary = salary * 1.03 ;



Pour modifier une valeur dans une colonne :

UPDATE personal_info SET salary = salary + 5000 WHERE employeeid = 1002 ;

Pour supprimer des donnes :

DELETE FROM personal_info WHERE employeeid = 1003 ;

Pour vider une table de tout son contenu :

TRUNCATE TABLE `personal_info` ;

Pour supprimer une table :

DROP TABLE personal_info ;

Pour supprimer une base de donnes :

DROP DATABASE employees ;



5.2 Base de donnes plusieurs tables*

Nous allons utiliser lexemple prcdent en y crant une table supplmentaire :

(si vous avez cras la base employees , recrez-la en utilisant les codes de lexemple prcdent)

CREATE TABLE disciplinary_action (action_id int not null, employeeid int not null, comments char(255)) ;

Nous allons maintenant y mettre des donnes :

INSERT INTO èmployees`.`disciplinary_action` (àction_id`, èmployeeid`, `comments`) VALUES ('1', '1002', 'absence irrgulire'), ('2', '1004', 'mise pied'), ('3', '1000', 'ivresse son poste');

Recherchons maintenant quel(s) employs ont eu une sanction :

SELECT personal_info.first_name, personal_info.last_name, disciplinary_action.comments FROM personal_info, disciplinary_action WHERE personal_info.employeeid = disciplinary_action.employeeid ;

Pour cela il faut bien tre dans la base employees et sur longlet SQL.

Lorsquon excute linstruction SQL nous renvoi bien



une liste avec les Noms et Prnoms des employs avec la sanction subie.

Si nous voulons rajouter un critre supplmentaire notre requte nous utilisons loprateur complmentaire AND comme ceci AND personal_info.salary > 40000 ce qui nous donnera :

SELECT personal_info.first_name, personal_info.last_name, disciplinary_action.comments FROM personal_info, disciplinary_action WHERE personal_info.employeeid = disciplinary_action.employeeid AND personal_info.salary > 40000 ;

Et nous renvoi :

Remarquez bien la syntaxe utilise pour indiquer MySQL que nous voulons rechercher dans deux tables :

SELECT nom_table1.nom_colonne, nom_table2.nom_colonne FROM nom_table1, nom_table2 WHERE nom_table1.nom_colonne = ;

A chaque instruction dans la requte la table pointe est rajoute devant le nom de colonne avec un point de liaison.



Ajoutons maintenant un champ notre table personal_info :

ALTER TABLE personal_info ADD (reportsto INTEGER NULL); Et remplissons les :

UPDATE `personal_info` SET `reportsto` = '220000' WHERE `employeeid` = '1000' ;





Crons une troisime table :

CREATE TABLE employeeterritories (employeeid INTEGER NOT NULL, territoryid INTEGER NOT NULL, state VARCHAR(5), city TINYTEXT);

Nous obtenons :



Remplissons maintenant notre table :

INSERT INTO `employeeterritories` (`employeeid`, `territoryid`, `state`, `city`) VALUES ('1000', '100', 'CA', 'Sacramento'), ('1001', '200', 'AZ', 'Phoenix'), ('1002', '300', 'TX', 'Austin'), ('1003', '400', 'GE', 'Atlanta'), ('1004', '500', 'FL', 'Tallahassee');

Nous obtenons :

Nous allons maintenant faire une requte sur nos trois tables qui peut sexprimer ainsi :

Quels sont les employs (nom et prnom) travaillant dans quel endroit (ville et tat) qui font un chiffre daffaire suprieur 200 000$ et qui ont subi une sanction disciplinaire ?

SELECT DISTINCT personal_info.first_name, personal_info.last_name, employeeterritories.city, employeeterritories.state, personal_info.reportsto, disciplinary_action .comments FROM personal_info, employeeterritories, disciplinary_action WHERE personal_info.reportsto >200000 AND personal_info.employeeid = employeeterritories.employeeid AND personal_info.employeeid = disciplinary_action. employeeid ;

A vous de trouver de nouveaux exemples !



5.3 La jointure

Introduisons maintenant une nouvelle commande, la JOINTURE, commande JOIN ! Toujours avec notre base employees essayons cette instruction :

SELECT first_name, last_name, city, reportsto FROM personal_info JOIN employeeterritories ON personal_info.employeeid = employeeterritories.employeeid WHERE reportsto >200000 ;

Nous obtenons :

Faisons une requte sur nos trois tables :

SELECT first_name, last_name, city, reportsto, comments FROM personal_info

JOIN employeeterritories ON personal_info.employeeid = employeeterritories.employeeid

JOIN disciplinary_action ON personal_info.employeeid = disciplinary_action.employeeid

WHERE reportsto >200000 Nous obtenons bien le mme rsultat qu la page prcdente :



* : vous pouvez copier-coller les codes utiliss dans ces exemples mais en faisant attention de ne pas copier les puces de dbut de ligne !

De mme il peut arriver que la police soit mal interprte par WampServer et que vous soyez oblig de retaper toute linstruction.

tuto mysql wampserver

Documents