1. CHEZ LE MME DITEUR Du mme auteur C. SOUTOU. De UML SQL.
N11098, 2002, 500 pages. C. SOUTOU. SQL pour Oracle 2e dition.
N11697, 2005, 480 pages. Autour de SQL et MySQL P. J. PRATT
Initiation SQL. N9285, 2001, 328 pages. A.G. TAYLOR. SQL Web
Training. N25413, 2002, 428 pages. R. LENTZNER. 300 astuces pour
SQL et MySQL. N25359, 2001, 254 pages. M. KOFLER. MySQL 5. Guide de
ladministrateur et du dveloppeur. N11633, 2005, 672 pages. J.-M.
AQUILINA. Aide-mmoire MySQL. N25451, 2002, 384 pages. J.-M.
DEFRANCE. PHP/MySQL avec Flash MX 2004. N11468, 2005, 710 pages. P.
CHALAT, D. CHARNAY et J.-R. ROUET. Les Cahiers du programmeur
PHP/MySQL et JavaScript. N11678, 2005, 212 pages. J.-M. DEFRANCE.
PHP/MySQL avec Dreamweaver 2004 (best of). N11709, 2005, 550
pages.
2.
3. DITIONS EYROLLES 61, bd Saint-Germain 75240 Paris Cedex 05
www.editions-eyrolles.com Le code de la proprit intellectuelle du
1er juillet 1992 interdit en effet expressment la photocopie usage
collectif sans autorisation des ayants droit. Or, cette pratique
sest gnralise notamment dans les tablissements denseignement,
provoquant une baisse brutale des achats de livres, au point que la
possibilit mme pour les auteurs de crer des uvres nouvelles et de
les faire diter correctement est aujourdhui menace. En application
de la loi du 11 mars 1957, il est interdit de reproduire
intgralement ou partiellement le prsent ouvrage, sur quelque
support que ce soit, sans autorisation de lditeur ou du Centre
Franais dExploitation du Droit de Copie, 20, rue des
Grands-Augustins, 75006 Paris. Groupe Eyrolles, 2006, ISBN :
2-212-11915-1
4. Pour Aurlia, mon chat aux yeux si bleus, si verts, si gris.
Pour Ren et Lydie, Jean et Denise qui sont devenus mes
parents.
15. ditions Eyrolles XVII Remerciements Je nai que deux
personnes remercier. Il sagit de deux jeunes informaticiens
rencontrs au hasard dun forum. Ils sont talentueux et dsintresss,
ce qui devient tellement rare dans ce monde dindividualisme
exacerb. Le premier recherchait un emploi la priode de la rdac-
tion, souhaitons quil trouve clavier ses mains. Le second dirige la
rubrique MySQL du site Developpez.com. Merci, Pierre Caboche, pour
la lecture de la premire moiti de louvrage, pour tes remarques que
jai (presque) toutes prises en compte, pour les complments en ligne
propos des opra- teurs ensemblistes dans les requtes. Merci,
Guillaume Lebur, pour tous tes commentaires de qualit et pour tes
corrections propos de la programmation sous MySQL. Merci pour ton
activit et tes tutoriels mis en ligne sur le site de
Developpez.com.
16. ditions Eyrolles XIX Avant-propos Nombre douvrages traitent
de SQL et de MySQL ; certains rsultent dune traduction hasar- deuse
et sans vocation pdagogique, dautres ressemblent des Bottin
tlphoniques ou proviennent de la traduction de la documentation
officielle en moins bien. Les survivants ne sont peut-tre plus
vraiment jour. Ce livre a t rdig avec une volont de concision et de
progression dans sa dmarche ; il est illustr par ailleurs de
nombreux exemples et figures. Bien que la source principale
dinforma- tions ft la documentation officielle de MySQL
(http://dev.mysql.com/doc), louvrage ne constitue pas un condens de
commandes SQL. Chaque notion importante est introduite par un
exemple simple et que jespre dmonstratif. En fin de chaque chapitre
des exercices vous permettront de tester vos connaissances. La
documentation en ligne de MySQL (MySQL 5 Reference Manual)
reprsente une dizaine de mgaoctets au format HTML. Tous les
concepts sy trouvant ne pourraient pas tre ici dcemment expliqus,
sauf peut-tre si cet ouvrage ressemblait un annuaire. Jai tent den
extraire seulement les aspects fondamentaux sous la forme dune
synthse. Vous ny trouverez donc pas des considrations propos
daspects avancs du langage ou du serveur comme loptimisation de
requtes, la restauration dune base, la rplication, la version du
serveur partir de laquelle telle ou telle fonction est apparue,
etc. Ce livre rsulte de mon exprience de lenseignement dans le
domaine des bases de donnes en premier, deuxime et troisime cycles
universitaires dans des cursus dinformatique vocation
professionnelle (IUT, licences et masters professionnels). Cet
ouvrage sadresse principalement aux novices dsireux de dcouvrir SQL
en program- mant sous MySQL. q Les tudiants et enseignants
trouveront des exemples pdagogiques pour chaque concept abord,
ainsi que des exercices thmatiques. q Les dveloppeurs PHP ou Java
dcouvriront des moyens de stocker leurs donnes.
17. Apprendre SQL avec MySQL XX ditions Eyrolles Guide de
lecture Ce livre sorganise autour de trois parties distinctes mais
complmentaires. La premire int- ressera le lecteur dbutant en la
matire, car elle concerne les instructions SQL et les notions de
base de MySQL. La deuxime partie dcrit la programmation avec le
langage procdural de MySQL. La troisime partie attirera lattention
des programmeurs qui envisagent dutiliser MySQL laide doutils
natifs, ou tout en programmant avec des langages volus ou via des
interfaces Web (PHP ou Java). Premire partie : SQL de base Cette
partie prsente les diffrents aspects du langage SQL de MySQL, en
tudiant en dtail les instructions de base. partir dexemples,
jexplique notamment comment dclarer, mani- puler, faire voluer et
interroger des tables avec leurs diffrentes caractristiques et
leurs lments associs (contraintes, index, vues, squences). Nous
tudions aussi SQL dans un contexte multi-utilisateur (droits
daccs), et au niveau du dictionnaire de donnes. Deuxime partie :
programmation procdurale Cette partie dcrit les caractristiques du
langage procdural de MySQL. Le chapitre 6 traite des lments de base
(structure dun programme, variables, structures de contrle,
interactions avec la base et transactions). Le chapitre 7 traite
des sous-programmes, des curseurs, de la gestion des exceptions,
des dclencheurs et de lutilisation du SQL dynamique. Troisime
partie : langages et outils Cette partie intressera les
programmeurs qui envisagent dexploiter une base MySQL en utilisant
un langage de programmation. Le chapitre 8 dtaille lAPI JDBC 3.0
qui permet de manipuler une base MySQL 5 par lintermdiaire dun
programme Java. Le chapitre 9 dcrit les principales fonctions de
lAPI mysqli qui permet dinterfacer un programme PHP 5 avec une base
MySQL 5. Le chapitre 10 synthtise les fonctionnalits de plusieurs
outils graphiques tels que MySQL Administrator, MySQL Query Browser
et phpMyAdmin. Dautres consoles graphiques dadministration sont
tudies, savoir Toad for MySQL, Navicat et EMS SQL Manager. Annexe
Lannexe contient une bibliographie et des adresses Web.
18. ditions Eyrolles XXI Avant-propos Typographie La police
courrier est utilise pour souligner les instructions SQL, noms de
types, tables, contraintes, etc. (ex : SELECT nom FROM Pilote). Les
majuscules sont employes pour les directives SQL, et les minuscules
pour les autres lments. Les noms des tables, index, vues,
fonctions, procdures sont prcds dune majuscule (exemple : la table
CompagnieAerienne contient la colonne nomComp). Les termes de MySQL
(bien souvent traduits littralement de langlais) sont nots en
italique, exemple : trigger, table, column, etc. Dans une
instruction SQL, les symboles { } dsignent une liste dlments, et le
symbole | un choix (exemple CREATE { TABLE | VIEW }. Les symboles [
et ] prcisent le carac- tre optionnel dune directive au sein dune
commande (exemple : CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX
index_name [USING index_type] ON table_name (index_col_name,)). Ce
sigle introduit une dfinition, un concept ou une remarque
importante. Il apparat soit dans une partie thorique soit dans une
partie technique pour souligner des instructions importantes ou la
marche suivre avec SQL. Ce sigle annonce soit une impossibilit de
mise en uvre dun concept soit une mise en garde. Il est
principalement utilis dans la partie consacre SQL. Jen prote pour
faire passer le message suivant : si vous travaillez en version 4
de MySQL, certaines instructions dcrites dans ce livre ne
fonctionneront pas. Cet ouvrage nest pas un guide de rfrence ! Vous
trouverez sur le Web des ressources pour connatre la compatibilit
de telle ou telle fonction SQL. Ce sigle indique que le code source
est tlchargeable partir du site des ditions Eyrolles
(www.eyrolles.com). Cela est valable pour les exercices corrigs
mais aussi pour tous les exemples du livre. Ce sigle signale une
astuce ou un conseil personnel. Web
19. Apprendre SQL avec MySQL XXII ditions Eyrolles Contact avec
lauteur Corrigs des exercices Si vous avez des remarques formuler
sur le contenu de cet ouvrage, nhsitez pas mcrire ladresse
[email protected]. Ne me demandez pas de dboguer votre procdure
catalogue ou doptimiser une de vos requtes Seules les remarques
relatives louvrage trouveront une rponse. Par ailleurs, un site
daccompagnement de louvrage (errata, corrigs des exercices, source
des exemples et complments) est en ligne et accessible via
www.editions-eyrolles.com.
20. ditions Eyrolles 1 Introduction Dans cette introduction,
nous prsentons, tout dabord, le cadre gnral dans lequel cet ouvrage
se positionne (SQL, le modle de donnes et loffre MySQL). Nous
dcrivons, pour finir, la procdure dinstallation de MySQL sous
Windows et lutilisation de linterface de commande en ligne pour que
vous puissiez programmer en SQL ds le chapitre 1. SQL, une norme,
un succs Cest IBM, tout seigneur tout honneur, qui, avec System-R,
a implant le modle relationnel au travers du langage SEQUEL
(Stuctured English as QUEry Language), rebaptis par la suite SQL
(Structured Query Language). La premire norme (SQL1) date de 1987.
Elle tait le rsultat de compromis entre construc- teurs, mais elle
tait fortement influence par le dialecte dIBM. SQL2 a t normalise
en 1992. Elle dfinit quatre niveaux de conformit : le niveau dentre
(entry level), les niveaux intermdiaires (transitional et
intermediate levels) et le niveau suprieur (full level). Les
langages SQL des principaux diteurs sont tous conformes au premier
niveau et ont beaucoup de caractristiques relevant des niveaux
suprieurs. Depuis 1999, la norme est appele SQL3. Elle comporte de
nombreuses parties (concepts objets, entrepts de donnes, sries
temporel- les, accs des sources non SQL, rplication des donnes,
etc.). Le succs que connaissent les diteurs de SGBD relationnels a
plusieurs origines et repose notamment sur SQL : q Le langage est
une norme depuis 1986, qui senrichit au fil du temps. q SQL peut
sinterfacer avec des langages de troisime gnration comme C ou
Cobol, mais aussi avec des langages plus volus comme C++, Java ou
C#. Certains considrent ainsi que le langage SQL nest pas assez
complet (le dialogue entre la base et linterface nest pas direct),
et la littrature parle de dfaut dimpdance (impedance mismatch). q
Les SGBD rendent indpendants programmes et donnes (la modification
dune structure de donnes nentrane pas forcment une importante
refonte des programmes dapplication). q Ces systmes sont bien
adapts aux grandes applications informatiques de gestion (archi-
tectures type client-serveur et Internet) et ont acquis une maturit
sur le plan de la fiabilit et des performances. q Ils intgrent des
outils de dveloppement comme les prcompilateurs, les gnrateurs de
code, dtats, de formulaires.
21. Apprendre SQL avec MySQL 2 ditions Eyrolles q Ils offrent
la possibilit de stocker des informations non structures (comme le
texte, limage, etc.) dans des champs appels LOB (Large Object
Binary). Nous tudierons les principales instructions SQL de MySQL
qui sont classifies dans le tableau suivant : Modle de donnes Le
modle de donnes relationnelles repose sur une thorie rigoureuse
bien quadoptant des principes simples. La table relationnelle
(relational table) est la structure de donnes de base qui contient
des enregistrements appels aussi lignes (rows). Une table est
compose de colonnes (columns) qui dcrivent les enregistrements.
Tables et donnes Considrons la figure suivante qui prsente deux
tables relationnelles permettant de stocker des compagnies, des
pilotes et le fait quun pilote soit embauch par une compagnie :
Tableau 0-1 Classication des ordres SQL Ordres SQL Aspect du
langage CREATE ALTER DROP - RENAME TRUNCATE Dnition des donnes
(LDD) INSERT UPDATE DELETE - LOCK TABLE Manipulation des donnes
(LMD) SELECT Interrogation des donnes (LID) GRANT REVOKE COMMIT
ROLLBACK SAVEPOINT - SET TRANSACTION Contrle des donnes (LCD)
Figure 0-1 Deux tables Compagnie comp nrue rue ville nomComp AF 10
Gambetta Paris Air France SING 7 Camparols Singapour Singapore AL
Pilote brevet nom nbHVol compa PL-1 Louise Ente 450 AF PL-2 Jules
Ente 900 AF PL-3 Paul Soutou 1000 SING
22. ditions Eyrolles 3 Introduction Les cls La cl primaire
(primary key) dune table est lensemble minimal de colonnes qui
permet didentifier de manire unique chaque enregistrement. Dans la
figure prcdente, les colonnes cls primaires sont notes en gras. La
colonne comp reprsente le code de la compagnie et la colonne brevet
dcrit le numro du brevet. Une cl est dite candidate (candidate key)
si elle peut se substituer la cl primaire tout instant. Une table
peut contenir plusieurs cls candidates ou aucune. Dans notre
exemple, les colonnes nomComp et nom peuvent tre des cls candidates
si on suppose quaucun homonyme nest permis. Une cl trangre (foreign
key) rfrence dans la majorit des cas une cl primaire dune autre
table (sinon une cl candidate sur laquelle un index unique aura t
dfini). Une cl trangre est compose dune ou de plusieurs colonnes.
Une table peut contenir plusieurs cls trang- res ou aucune. Dans
notre exemple, la colonne compa (note en italique dans la figure)
est une cl trangre, car elle permet de rfrencer un enregistrement
unique de la table Compagnie via la cl primaire comp. Le modle
relationnel est ainsi fondamentalement bas sur les valeurs. Les
associations entre tables sont toujours binaires et assures par les
cls trangres. Les thoriciens considrent celles-ci comme des
pointeurs logiques. Les cls primaires et trangres seront dfinies
dans les tables en SQL laide de contraintes. MySQL MySQL est la
fois le nom du SGBD et le nom de la socit (qui se nomme en fait
MySQL AB, dcrite sur http://www.mysql.com) dont le sige se trouve
en Sude Uppsala compter une cinquantaine de kilomtres au nord de
Stockholm. Selon leurs dires, leur serveur de donnes, qui est crit
en C et C++, serait install de faon oprationnelle sur plus de six
millions de sites. Dun point de vue cot, lutilisation du SGBD sur
des projets importants (entre 250 000 et 500 000 ) ferait conomiser
90 % sur le prix des licences du serveur, 60 % sur les ressources
systme, 70 % sur le prix du matriel, 50 % sur les tches dadminis-
tration et de support.
23. Apprendre SQL avec MySQL 4 ditions Eyrolles Par analogie
avec les systmes dexploitation, depuis 1999, MySQL connat le succs
de Linux. Tlcharge prs dun million de fois en trois semaines (en
octobre 2005), la version production de MySQL doit sa popularit du
fait de son caractre open source, de ses fonction- nalits de plus
en plus riches, de ses performances, de son ouverture tous les
principaux langages du march, de son fonctionnement sur les systmes
les plus courants (les distribu- tions classiques de Linux,
Windows, Mac OS, BSD, Novell et les drivs dUnix) et de sa facilit
dutilisation pour des applications Web de taille moyenne. Un peu
dhistoire Le tableau suivant rsume lhistorique des fonctionnalits
importantes du serveur de donnes MySQL. Une version majeure comme
3.23 se dcline au fil des mois en diffrentes sous- versions, en
fonction des diverses phases de dveloppement : q alpha correspond
la phase active, de nouvelles fonctionnalits sont ajoutes. q bta
correspond limplmentation des nouvelles fonctionnalits de la phase
alpha, sans apport important de code. q gamma correspond au terme
release candidate (aprs rsolution des bugs de la version bta). q
production est ltape suivante aussi appele GA (Generally
Available). Les bugs sont rsolus sils ne modifient pas le
comportement gnral du serveur. q old correspond la prcdente version
de production (par rapport la courante). Ainsi, en septembre 2005,
la version 4.1.14 tait mise en production et en octobre 2005, la
version 5.0 ltait aussi. Ce qui est planifi moyen terme concerne un
enrichissement gnral des commandes SQL, la prise en compte des
types manquants de SQL2 et de ODBC3, la programmation des requ- tes
hirarchiques (interrogation de structures en arbres) en sinspirant
de ce qua fait Oracle en la matire (directive CONNECT BY dans un
SELECT). Tableau 0-2 Dates importantes pour MySQL Anne versions
Caractristiques principales 1999 3.23.x Rplication Recherches
textuelles Transactions et Intgrit rfrentielle tables InnoDB (2002
3.23.44) 2001 4.0.x Cache de requtes Scurisation SSL Sauvegarde
chaud 2003 4.1.x Support de Unicode Donnes gographiques SQL
dynamique 2004 5.0.x Vues Curseurs Procdures catalogues Dclencheurs
Dictionnaire des donnes Transactions distribues (XA) venir 5.1.x
Jointure externe Contraintes CHECK Sauvegarde chaud et Intgrit
rfrentielle tables MyISAM
24. ditions Eyrolles 5 Introduction Ce qui nest pas encore
planifi reste la prise en charge du stockage de donnes au format
XML et les extensions objets de SQL3 (principalement les types
abstraits, les hritage et les mthodes). Les changements oprs sont
prsents en dtail et par version ladresse http://
dev.mysql.com/doc/refman/x.y/en/news.html (x.y tant le numro de
version majeure, par exemple actuellement 5.1) Offre du moment La
figure suivante (merci au passage http://www.iconarchive.com)
prsente la majeure partie des fonctionnalits de MySQL qui se
positionnent au sein du serveur de donnes (SGBD). Les API
permettent dintgrer SQL dans des programmes de diffrents langages.
Le langage SQL sera utilis par tous ceux (manuellement ou par un
outil) travaillant sur la base de donnes (administrateur,
dveloppeur, utilisateur). Le langage procdural de MySQL permet
dincorporer nativement tout ordre SQL dans un programme.
Concrtement, une fois tlcharg et install, vous avez accs un SGBD,
un client en mode texte (interface de commande). Les pilotes ODBC,
JDBC, API pour les langages C et C++, et les outils MySQL
Administrator, MySQL Query Browser, et MySQL Migration Toolkit sont
tlcharger puis installer sparment. Figure 0-2 Offre MySQL API : C,
JDBC, ODBC, .NET, PHP, Python, Perl, Ruby, Microsoft VB Pool de
connexions Authentification Rutilisation de processus Cache
Utilitaires Sauvegarde Restauration Rplication Migration Cluster
SQL LDD LMD LCD - Vues Procdures Dclencheurs Analyseur Requtes
Privilges Optimiseur Accs Statistiques Cache Buffers Moteurs de
stockage Mmoire - Index MyISAM InnoDB Archive Cluster Federated ...
Serveur MysQL Systme de fichiers NTFS NFS SAN - NAS Fichiers et
logs Donnes index -
25. Apprendre SQL avec MySQL 6 ditions Eyrolles Licences Deux
types de licences sont proposs par MySQL : commerciale et GPL. Dans
le cadre dun dveloppement dapplication entirement sous licence GPL,
MySQL est gratuit. Il en va de mme sil nest pas copi, modifi,
distribu ou employ pour une utilisation en combinaison avec un
serveur Web (si vous dveloppez lapplication Web vous-mme). Dans
tous les autres cas, il est ncessaire dobtenir une licence
commerciale. Par exemple, si vous incluez un serveur MySQL ou des
pilotes MySQL dans une application non open source. Et la
concurrence ? Elle fait rage. Deux types de concurrents : ceux qui
sont dans le domaine de lopen source et ceux qui engrangent des
dollars tour de bras. Dans la premire catgorie, citons
principalement Interbase 6 et Firebird de Borland, PostgreSQL et
Berkeley DB. Dans la seconde, on trouvera 4D, Filemaker, IBM (DB2,
UDB), Informix, Microsoft (SQL Server, Access), Oracle et Sybase
(Adaptive Server, SQL Anywhere Studio). Depuis peu, les grands
diteurs souvrent la distribution gratuite. Ces produits ncessitent
de solides confi- gurations, ce que nexigent pas les diteurs de la
premire catgorie : q Fin 2004, Microsoft proposait SQL Server 2005
Express Edition qui est limit dun seul CPU, 1 Go de mmoire et 4 Go
de donnes. q Fin 2004, Sybase offrait ASE Express Edition.
Plusieurs instances peuvent cohabiter sur une machine tout en
nutilisant pas plus dun processeur, moins de 2 Go de RAM et pas
plus de 5 Go de donnes. q Oracle annonait, en novembre 2005, une
version gratuite avec Oracle Database XE (Express Edition). Version
toutefois limite un processeur, moins de 1 Go de RAM, une seule
instance par systme et pas plus de 4 Go de donnes. q Peu de temps
avant, IBM avait aussi annonc une licence gratuite de DB2 Express.
Pas plus de deux processeurs et moins de 4 Go de RAM. Tout le monde
est donc sur les rangs. La guerre des versions nest donc pas finie.
Bien malin qui pourra prdire qui gagnera sur ce terrain. Notion de
schma (database) MySQL appelle database un regroupement logique
dobjets (tables, index, vues, dclen- cheurs, procdures catalogues,
etc.) pouvant tre stocks diffrents endroits de lespace disque. Je
ferai donc souvent rfrence au terme base de donnes pour parler de
cette notion. On peut aussi assimiler ce concept la notion de
schma, pour ceux qui connaissent Oracle. L o MySQL et dautres SGBD
diffrent, cest sur la notion dutilisateur (user).
26. ditions Eyrolles 7 Introduction q Pour tous, un utilisateur
sera associ un mot de passe pour pouvoir se connecter et mani-
puler des tables (sil en a le droit, bien sr). q Pour MySQL, il ny
a pas de notion dappartenance dun objet (table, index, etc.) un
utilisateur. Un objet appartient son schma (database). Ainsi, deux
utilisateurs distincts (Jules et Paul) se connectant sur la mme
base (database) ne pourront pas crer chacun une table de nom
Compagnie. Sils doivent le faire, ce sera dans deux bases
diffrentes (bdjules et bdpaul). q Pour Oracle ou dautres SGBD,
chaque objet appartient un schma (user). Ainsi, deux utilisateurs
distincts Jules et Paul se connectant la base (qui est un ensemble
de sch- mas) pourront crer chacun une table de nom Compagnie (la
premire sera rfrence Jules.Compagnie, la seconde Paul.Compagnie).
La figure suivante illustre deux utilisateurs travaillant sur
diffrentes bases par une interface qui peut tre la fentre de
commande en ligne (dans la majeure partie des enseignements), ou un
langage de programmation comme C, Java ou PHP (utilisation dune
API). Notez dj lexistence de trois bases initiales (mysql, test et
information_schema) que nous dtaillerons au chapitre 5. Notion dhte
MySQL dnomme host la machine hbergeant le SGBD. MySQL diffre aussi
ce niveau des autres SGBD, car il est possible de distinguer des
accs dun mme utilisateur suivant quil se connecte partir dune
machine ou dune autre. La notion didentit est base sur le couple
nom dutilisateur MySQL (user) ct serveur, machine cliente. Identits
Ainsi lutilisateur Paul, se connectant depuis la machine camparols,
peut ne pas tre le mme que celui se connectant depuis la machine
gambetta. Sil sagit du mme, il faudra, au niveau du serveur,
ventuellement composer un ensemble de prrogatives quivalent pour
les deux accs (voir le chapitre 5). Sil sagit de deux personnes
diffrentes, il faudra distinguer Figure 0-3 Notions de base et
dutilisateur MySQL Interface MySQL Jules Paul mysql test
information_schema bdjules bdpaul
27. Apprendre SQL avec MySQL 8 ditions Eyrolles les
attributions des diffrents droits. La figure suivante illustre le
fait que deux utilisateurs peuvent se connecter par deux accs
diffrents. Trois identits seront donc prvoir ct serveur. Nous
verrons au chapitre 5 comment configurer le serveur et les clients.
Nous tudierons au chapitre 9 des outils graphiques daide
ladministration. Accs MySQL Une fois que vous aurez install MySQL
sur votre ordinateur, vous serez libre de choisir laccs qui vous
convient. Ce livre utilise essentiellement linterface de commande
en ligne fournie avec le SGBD, mais aussi Java via JDBC, et le
navigateur Web au travers de PHP. Aspects tudis Nous ntudierons pas
tous les lments dune base, car certains sont assez spcifiques et
sortent du cadre traditionnel de lenseignement, mme suprieur. Le
tableau suivant indique dans quel chapitre du livre les principaux
lments dun schma sont tudis : Figure 0-4 Notion dhte MySQL brassens
Jules Paul mysql test jules@gambetta paul@camparols ... camparols
gambetta Paul paul@gambetta TCP-IP Tableau 0-3 lments dune base
MySQL lments tudis Chapitre Aspects non tudis Dclencheurs
(triggers) 7 Fonctions et procdures 7 Tables et index 1 Squences 2,
5 Vues (views) et utilisateurs 5 Clusters Moteurs de stockage
(storage engine) Partitionnement Donnes spatiales
28. ditions Eyrolles 9 Introduction Mise en uvre de MySQL (sous
Windows) Si tout se passe bien, comptez quelques minutes pour
installer MySQL. Je vous conseille toutefois de crer un point de
restauration Windows pour pouvoir revenir votre dernire bonne
configuration connue. Extraire larchive tlcharge sur le site
officiel de MySQL AB (http://dev.mysql.com/downloads/) dans un
rpertoire temporaire (exemple : C:Temp), puis excuter Setup.exe.
Installation Le premier choix est donn pour le type dinstallation :
typique, complte et personnalise choisir typique dans un premier
temps. Le deuxime cran vous invite vous enregistrer ce nest pas
obligatoire, mais conseill toutefois pour saluer lnorme travail
fait par ces jeunes informaticiens de gnie (voil, la pommade est
passe). La suite concerne la configuration du serveur de donnes
(choisir la configuration dtaille pour suivre les diffrentes tapes
et mieux comprendre le paramtrage de votre serveur). q Lassistant
propose en premier lieu de dterminer le type de votre serveur
(machine de dveloppement, serveur ou machine ddie). Jai opt pour le
premier choix. q Choissisez ensuite le type de base de donnes que
vous comptez exploiter (multifonction, mode transactionnel
seulement ou jamais transactionnel). Jai opt pour le premier choix.
q Dterminez ensuite le rpertoire qui contiendra les donnes des
bases InnoDB (cest le type de tables que nous utiliserons dans cet
ouvrage, car tant le seul, dans cette version, prendre en charge
les cls trangres). Jai opt pour le choix par dfaut. q Choissisez
ensuite loption qui convient votre utilisation (en fonction du
nombre de connexions). Jai opt pour le premier choix (vingt
connexions maximum). q Il est ensuite possible de modifier le port
UDP dcoute (par dfaut 3306) et le mode comportemental du serveur
par rapport la syntaxe des instructions SQL. Jai opt pour le
premier choix (mode strict). q Attention ne pas slectionner un jeu
de caractres japonais dans lcran qui arrive. Jai opt pour les choix
par dfaut (West European et latin1). q Lcran suivant permet de
dclarer MySQL comme un service Windows (quon pourra arrter via le
panneau de configuration plutt quavec une belle commande en ligne,
ou par un Ctrl-Alt-Suppr bien connu des bidouilleurs). Penser aussi
inclure dans le path le chemin de lexcutable mysql de manire
pouvoir lancer une connexion en ligne de commande. Il vous suffit
de cocher la case approprie. q Saisissez un mot de passe pour root
; vous pouvez aussi crer un compte anonyme (connexion fantme sans
utilisateur ni mot de passe).
29. Apprendre SQL avec MySQL 10 ditions Eyrolles Aprs avoir
valid la demande dexcution (bouton Execute), quelques secondes vont
scouler avant quon vous invite terminer linstallation. Voil, MySQL
est install. Si ce nest pas le cas, la section suivante vous aidera
srement. Dernire chose, si vous nutilisez pas souvent MySQL, pensez
arrter et positionner sur Manuel le service (ce nest pas pour
lespace quil occupe : 10 Mo en RAM). Pour modifier une
configuration existante, vous trouverez un assistant dans Dmarrer/
MySQL//MySQL Server Instance Wizard. Dsinstallation Pour supprimer
une configuration, vous trouverez un assistant dans Dmarrer/Tous
les programmes/MySQL//MySQL Server Instance Wizard, choix Remove
Instance. Cependant le rpertoire install par dfaut dans Program
Files reste sur le disque (compter une centaine de mga-octets). De
mme quen ce qui concerne les entres dans le menu Dmar- rer, je ne
parle pas de la base de registres qui est inchange Dans Panneau de
configuration, Ajout/Suppression de programmes, supprimer MySQL
Server. Il reste encore des choses dans la base de registres. Le
rpertoire MySQL sous Program Files a diminu de taille, mais il est
toujours prsent. Une fois que tout est fait, redmarrez votre
machine et reprenez linstallation initiale. Prudence si vous
modifiez les chemins des rpertoires des donnes entre plusieurs
installations. Reconfiguration En relanant une installation, il
vous est donn dajouter des composants (option Modify) si vous navez
pas fait une installation complte au dbut. Vous avez aussi la
possibilit de rparer (option Repair) une configuration existante.
Premiers pas La procdure suivante va guider vos premiers pas pour
travailler sous cette interface dune manire professionnelle . Il
sagit de stocker vos fichiers de commande qui pourront servir
diffrentes actions (crations de tables, de vues ou dutilisateurs,
insertions, modifications ou suppressions denregistrements,
laboration de requtes, de procdures catalogues, etc.). Linterface
de commande Linterface de commande en ligne porte le nom du SGBD
(mysql). Cette interface ressemble une fentre DOS ou telnet et
permet de dialoguer de la plus simple faon avec la base de donnes.
Lutilisation peut tre interactive ou batch . Quand lutilisation est
interactive
30. ditions Eyrolles 11 Introduction (cest le mode le plus
courant), le rsultat des extractions est prsent sous une forme
tabulaire au format ASCII. Vous verrez quil est notamment possible
: q dexcuter des instructions SQL (crr des tables, manipuler des
donnes, extraire des informations, etc.) ; q de compiler des
procdures catalogues et des dclencheurs ; q de raliser des tches
dadministration (cration dutilisateurs, attribution de privilges,
etc.). Le principe gnral de linterface est le suivant : aprs une
connexion locale ou distante, des instructions sont saisies et
envoyes la base qui retourne des rsultats affichs dans la mme
fentre de commande. Nayez pas honte de bien matriser cette
interface au lieu de connatre toutes les options dun outil
graphique (comme PhpMyAdmin, MySQL Administrator ou autre). Il vous
sera toujours plus facile de vous adapter aux diffrents boutons et
menus, tout en connaissant les instruc- tions SQL, que linverse.
Imaginez-vous un jour Singapour sur une machine ne disposant pas
doutils graphiques, que le client vous demande la rduction que vous
pouvez lui faire sur la vente dune piscine int- rieure dun Airbus
A380 et que vous devez interroger (ou mettre jour) une table sur le
serveur du sige social Blagnac. Vous ne savez pas vous servir de
linterface en ligne : vous ntes pas un vrai informaticien ! Cration
dun utilisateur Vous allez maintenant crer un utilisateur MySQL.
Ouvrez le fichier premierPas.sql qui se trouve dans le rpertoire
Introduction, laide du bloc-notes (ou dun diteur de texte de votre
Figure 0-5 Principe gnral de linterface de commande en ligne mysql
[options] [nomBase] [entres-sorties] quit ou exit mysql> INSERT
mysql> CREATE mysql> SELECT mysql> mysql Systme
dexploitation nomBase Web
31. Apprendre SQL avec MySQL 12 ditions Eyrolles choix).
Changez util par le nom de lutilisateur crer (modifiez aussi le nom
de la base). Vous pouvez changer le mot de passe si vous voulez.
Enregistrez ce fichier dans un de vos rpertoires. Connexion au
serveur Dans une fentre de commande Windows, Linux (ou autre),
lancer linterface en ligne en connectant lutilisateur root avec le
mot de passe que vous avez donn lors de linstallation. mysql
--user=root -p Une fois connect, par copier-coller (en effectuant
un clic droit dans la fentre de commande MySQL), excutez une une
les diffrentes instructions (cration de la base, de lutilisateur,
des privilges et dconnexion de root). Nous tudierons au chapitre 5
les notions lmentai- res de droits et de scurit. Les lignes prcdes
de -- sont des commentaires. Voil, votre utilisateur (util) est cr,
il peut se connecter et il possde toutes les prrogatives sur la
base (bdutil) pour excuter les instructions dcrites dans cet
ouvrage. Pour tester votre connexion, lancez la commande suivante
qui se connecte au serveur sur la base bdutil, sous lutilisateur
util. mysql --user=util --host=localhost -p --database=bdutil
Vrification de la version Pour contrler la version du serveur sur
lequel vous tes connect, excutez la connexion- dconnexion suivante
dans une fentre de commande Windows, Linux (ou autre). mysql
--version Si vous tes dj connect, la commande SELECT VERSION();
vous renseignera gale- ment propos de la version du SGBD. Si vous
ntes pas en version 5, il vous sera impossible de travailler avec
des procdures catalogues, vues et dclencheurs. Pour ma part lors de
la rdaction de cet ouvrage, cette commande a renvoy le rsultat
suivant : Figure 0-6 Interface en mode ligne de commande
32. ditions Eyrolles 13 Introduction +-----------+ | VERSION()
| +-----------+ | 5.0.15-nt | +-----------+ Options de base Les
principales options au lancement de mysql sont rsumes dans le
tableau suivant : Tableau 0-4 Principales options de la commande
mysql Option Commentaire --help ou -? Afche les options
disponibles, ltat des variables denvironnement et rend la main.
--batch ou -B Toute commande SQL peut tre lance dans la fentre de
commande systme sans pour autant voir linvite ; les rsultats
(colonnes) sont spars par des tabulations. --database=nomBD ou D
nomBD Slection de la base de donnes utiliser aprs la connexion.
--host=nomServeur ou -h nomServeur Dsignation du serveur. --html ou
-H Formate le rsultat des extractions en HTML. --one-database ou -O
Restreint les instructions la base de donnes spcie initialement. -p
Demande le mot de passe sans lemployer en tant que paramtre.
--password=motdePasse Transmission du mot de passe de lutilisateur
connecter. vitez cette option et prfrez la prcdente
--prompt=parametre Personnalise linvite de commande (par dfaut
mysql>). --silent ou -s Congure le mode silence pour rduire les
messages de MySQL. --skip-column-names ou -N Ncrit aucun en-tte de
colonne pour les rsultats dextraction. --table ou -t Formate le
rsultat des extractions en tables en-tte de colonne (par dfaut dans
le mode interactif). --tee=cheminNomFichier Copie la trace de toute
la session dans le chier que vous indiquez. --user=utilisateur ou
-u utilisateur Dsigne lutilisateur devant se connecter. --verbose
ou -v Mode verbeux pour avoir davantage de messages du serveur.
--version ou -V Afche la version du serveur et rend la main.
--vertical ou -E Afche les rsultats des extractions verticalement
(non plus en lignes horizontales). --xml ou -X Formate le rsultat
des extractions en XML. Les noms de balises gnres sont pour la
table rsultat, pour chaque ligne et pour les colonnes.
33. Apprendre SQL avec MySQL 14 ditions Eyrolles Ces options
peuvent se combiner en les sparant simplement par un espace
(exemple : mysql --tee=D:devsortiemysql.txt --database=bdsoutou va
se connecter anonymement la base bdsoutou en inscrivant le contenu
de la trace de la session dans le fichier sortiemysql.txt situ dans
le rpertoire D:dev). Le tableau suivant rsume les principaux
paramtres pour afficher les invites de commande (relatives loption
prompt). Batch Pour lancer plusieurs commandes regroupes dans un
fichier extension .sql , il faut prciser le chemin du fichier et
celui qui contiendra les ventuels rsultats (cest du brut de
dcoffrage !). Ainsi, linstruction suivante excute dans la base
bdsoutou, sous lautorit de lutilisateur soutou, les commandes
contenues dans le fichier Testbatch.sql situ dans le rpertoire
D:dev (notez lutilisation du double back-slash pour dsigner une
arbo- rescence Windows). Le rsultat sera consign dans le fichier
sortie.txt du mme rper- toire. mysql --user=soutou --password=iut
bdsoutou Votre prompt, et vite ! Lexcution de linstruction mysql
--prompt="(u@h) [d]> " --user=root p dans une fentre de commande
shell ou DOS connectera lutilisateur root en lui demandant son mot
de passe. Linvite de commande laffichage sera de la forme suivante
: Tableau 0-5 Principales options de linvite de commandes Option
Commentaire v Version du serveur. d Base de donnes en cours
dutilisation. h Nom du serveur. u Nom dutilisateur. U Nom
dutilisateur long (au format nom@serveur). _ Un espace. R Heure (0
23). m Minutes. s Secondes. Y Anne sur quatre chiffres. D Date en
cours. c Compteur dinstructions. D:devsortie.txt
34. ditions Eyrolles 15 Introduction (root@localhost)
[bdsoutou]> une fois que root aura slectionn la base bdsou- tou
(par la commande use nombase; ). Configurez votre invite de
commande SQL dans le fichier de configuration my.ini situ en
principe dans le rpertoire C:Program FilesMySQLMySQL Server xx de
la manire qui vous convient le plus. Pour ma part, jai ajout les
deux lignes suivantes sous la section [mysql] elle-mme situe sous
ltiquette [client]. #mon prompt prompt=(u@h) [d]_mysql>_ Une
fois le serveur redmarr, en considrant que votre compte sappelle
util, toutes vos commandes SQL devraient en principe tre prfixes de
la syntaxe suivante : (util@localhost) [bdutil] mysql> Commandes
de base Une fois connect, vous pouvez utiliser des commandes ou
faire des copier-coller dun diteur de texte dans linterface mysql
(ce moyen de faire correspond plus un environnement de test qui
conviendra lapprentissage). Le tableau suivant rsume les
principales instructions pour manipuler le buffer dentre de
linterface. La commande source est trs utile pour viter les
copier-coller de trop nombreuses instructions. Tableau 0-6
Commandes de base du buffer dentre Commande Commentaire ? Afchage
des commandes disponibles. delimiter chane Modie le dlimiteur (par
dfaut ; ). use nomBase Rend une base de donnes courante. prompt
chane Modie linvite de commande avec les paramtres vus prcdemment.
quit ou exit Quitte linterface. source cheminNomFichier.sql Charge
et excute dans le buffer le contenu du cheminNomFichier.sql (ex :
source D:devTestbatch.sql excutera le script Testbatch.sql situ
dans D:dev). tee nomFichierSortie Cration nomFichierSortie dans le
rpertoire C:Program FilesMySQLMySQL Server n.nbin qui contiendra la
trace de la session.
35. Partie I SQL de base
36. ditions Eyrolles 19 Chapitre 1 Dfinition des donnes Ce
chapitre dcrit les instructions SQL qui constituent laspect LDD
(langage de dfinition des donnes). cet effet, nous verrons
notamment comment dclarer une table avec ses ven- tuels index et
contraintes. Tables relationnelles Une table est cre en SQL par
linstruction CREATE TABLE, modifie au niveau de sa struc- ture par
linstruction ALTER TABLE et supprime par la commande DROP TABLE.
Cration dune table (CREATE TABLE) Pour pouvoir crer une table dans
votre base, il faut que vous ayez reu le privilge CREATE. Le
mcanisme des privilges est dcrit au chapitre 5. La syntaxe SQL
simplifie est la suivante : CREATE [TEMPORARY] TABLE [IF NOT
EXISTS] [nomBase.]nomTable ( colonne1 type1 [NOT NULL | NULL]
[DEFAULT valeur1] [COMMENT 'chaine1'] [, colonne2 type2 [NOT NULL |
NULL] [DEFAULT valeur2] [COMMENT 'chaine2'] ] [CONSTRAINT
nomContrainte1 typeContrainte1] ...) [ENGINE= InnoDB | MyISAM |
...]; q TEMPORARY : pour crer une table qui nexistera que durant la
session courante (la table sera supprime la dconnexion). Deux
connexions peuvent ainsi crer deux tables temporaires de mme n