MySQL 4pp NB Light

Embed Size (px)

Citation preview

p. 1

p. 2

MySQL - Mise en oeuvre, conguration, administrationFranois Gannaz INP Grenoble Formation Continue

Introduction aux bases de donnes et MySQL

p. 3

p. 4

Quest-ce quune base de donnes ?Plusieurs sens suivant le contexte : Un jeu de donnes particulier Ex : les donnes dune application web de blog (peut reprsenter des millions denregistrements) Les chiers qui contiennent ces donnes Le systme qui les gre Ex : MySQL Eviter les abus. . . Le logiciel qui gre des donnes Ex : une application de gestion de bibliothque On parlera pour cela de client de base de donnes.

Le monde des bases de donnes (SGBD)Les applications bureautiques tout-en-unFile Maker Pro MS Access ...

Les systmes de bases de donnesSouvent de structure client-serveur Presque toujours de modle relationnel langage standard pour laccs : SQL (Structured Query Language) API dans des langages de programmation divers Les BdD Orientes Objet (donc non relationnelles) sont rares.

p. 5

p. 6

Principaux SGBDR du marchSGBDR propritairesOracle DB2 (IBM) SQL Server (MS)

MySQL cest. . .une base de donne relationnelle cre en 1995 modle client-serveur une application lgre dans le monde des SGBD dveloppe par une socit sudoise (ABSoft) Rachete par Sun Microsytems dbut 2008. Le plus rpandu des SGBDR libres Particulirement utilis pour le web (LAMP) diuse sous double licencelibre (GPL) pour un usage interne ou libre propritaire payant pour un usage propritaire

SGBDR libresMySQL PostgreSQL SQLite (embarqu et non client-serveur) Firebird (fork de Borland InterBase)

Principales versions : 4.1 stable depuis octobre 2004 5.0 stable depuis octobre 2005 5.1 stable depuis novembre 2008

p. 7

p. 8

Caractristiques de MySQLAvantagesMulti plates-formes : Linux, Windows, OSX, etc. Gratuit pour un usage libre ou non commercial Bonne documentation de rfrence (HTML, PDF) http://dev.mysql.com/doc/refman/5.0/en/ SGBD performant Plusieurs moteurs internes suivant les besoins Interface avec la plupart des langages de programmation

Installation

InconvnientsPartiellement conforme au standard SQL:2003 Quelques fonctionnalits absentes ou trs faibles :analyse la vole (OLAP) traitement du XML donnes gographiques (GIS) triggers (dclencheurs) et curseurs ...

p. 9

p. 10

Installation Windows - 1ComposantsMySQL WindowsServeur MySQL (mysqld) Clients console : shell (mysql) Clients console : utilitaires (mysqladmin, mysqldump...) Instance Manager (obsolte)

Installation Windows - 2Structure des rpertoiresC:\Program Files\mySQL\MySQL Server 5.0 bin : les excutables binaires data : les chiers bases de donnes docs examples include : en-ttes pour la programmation C lib : les bibliothques dynamiques conguration "my.ini" dans C:\windows (chier nomm "my.cnf" sous Linux)

MySQL GUI Tools (optionnel)MySQL Administrator MySQL Query Browser MySQL Migration Toolkit Migration dun SGBD tranger vers MySQL

MySQL Workbench (optionnel) Conception et diagrammes des bases MySQL

Gestion des servicesPar le gestionnaire de services de Windows

p. 11

p. 12

Installation WAMPServerUn pack de logiciels libres congurs ensembleApache : serveur Web MySQL : serveur de base de donnes PHP : langage de programmation web PHPMyAdmin : interface web de gestion de MySQL, crite en PHP SQLiteManager

Larchitecture client-serveurRseau : utilisation du protocole IPune adresse IP, ex. 192.168.1.100 un nom de machine, ex. pc101-01.cuefa.inpg.fr un port (=protocole) ; 3306 par dfaut pour MySQL

ralis par Anaska (st franaise), sous licence GPL v2.0 concurrents : EasyPHP, xAMP...

Cas particulier : client-serveur en locallocalhost : IP=127.0.0.1 (universel) utilisation des canaux nomms (Windows NT...) utilisation des sockets Unix

Parcourir larborescence installe : trouver les chiers de conguration de Apache, MySQL, phpMyAdmin

p. 13

p. 14

Les interfaces utilisateurLigne de commandela console mysql les utilitaires : mysqldump, mysqladmin... options communes : -u -p -h -P ...

La documentationLe manuel de rfrencehttp://dev.mysql.com/doc/ multiples versions, multiples langues mises jour rgulires attention la synchro des versions Formats : En PDF : imprimable... En format CHM (aide Windows) en ligne de commande (terminal) : HELP ... ; En ligne : HTMLCommentaires utilisateurs

Les clients lourds graphiquesMySQL Administrator MySQL Query Browser

Linterface webPhpMyAdmin : interface unie

p. 15DPT - id_DPT smallint [PK] - titre varchar(255) - titreCourt varchar(20) - introduction text - conclusion text - logo_url varchar(255) - date_creation timestamp DPT_utilisateur - id_DPT smallint [FK] - id_utilisateur tinyint [FK]

p. 16

id_DPT

DPT_groupt - id_DPT smallint [FK,U] - groupt enum [U] - periode varchar(50) - commentaire text id_DPT

utilisateur_perm - id_utilisateur tinyint [FK] - perm enum

notice id_DPT - id_notice mediumint [PK] - auteur varchar(100) - titre varchar(255) - commentaire varchar(255) - id_journal tinyint [FK] - id_supplement tinyint [FK] - ident varchar(50) - chrono date - chronofin date - page varchar(10) - url varchar(255) - chemin varchar(255) - date_maj timestamp - date_saisie datetime - saisie_id_utilisateur tinyint - maj_id_utilisateur tinyint - erreur varchar(255) - dpt tinyint id_notice id_fnsp fnsp - id_fnsp smallint [PK] - cote varchar(100) - intitule varchar(255) - aintitule varchar(255) - pere_id_fnsp smallint - ancien tinyint - type varchar(20) - date_maj timestamp notice_fnsp id_fnsp per_id_fnsp - id_lnfnsp int [PK] - id_notice mediumint [FK] - id_fnsp smallint [FK] id_supplement journal - id_journal tinyint [PK] - intitule varchar(100) - intart varchar(10) - id_revue_fripes mediumint - id_utilisateur tinyint [FK] - local enum - dpt tinyint id_utilisateur id_utilisateur

DPT_notice

utilisateur saisie_id_utilisateur -> id_utilisateur - id_utilisateur tinyint [PK] - login varchar(20) [U] - password varchar(40) - id_institut tinyint [FK]

Modlisation dune base de donnes Le modle relationnel

StatsArbre - id mediumint [PK] - id_fnsp mediumint [FK] - id_parent mediumint - prof tinyint - nb_fils smallint - nb_not_direct mediumint - nb_not_cumul mediumint - nb_not_direct_public mediumint - nb_not_cumul_public mediumint - chemin varchar(250) - ancetre_niv1 mediumint - ancetre_niv2 mediumint - geo tinyint

- id_DPT smallint [FK,U] - id_notice mediumint [FK,U] - requete text - date_insert timestamp

id_notice

id_institut id_journal id_utilisateur institut - id_institut tinyint [PK] - nom varchar(20) [U]

id_utilisateur

statsConsult - id_statsConsult int [PK] - ip varchar(16) - temps datetime - notices tinyint - formulaire tinyint - journal varchar(50) - supplement varchar(50) - auteur varchar(100) - titre varchar(100) - date varchar(25) - fnsp varchar(50) - dpt mediumint - images tinyint - rssFNSP mediumint id_fnsp id_journal

id_journal

indexFNSP - id_index mediumint [PK] - expression varchar(255) - commentaire text - id_fnsp mediumint [FK]

supplement - id_supplement tinyint [PK] - intitule varchar(200) - id_journal tinyint [FK] - local enum - dpt tinyint

utilisateur_journal - id_utilisateur tinyint [FK] - id_journal tinyint [FK]

IEP DP

p. 17

p. 18

Les tablesUne base de donnes (par ex. discotheque) est faite de tables. Table Disques Titre Compositeur Cantates Bach J.S. Sonates Beethoven Concerto Dvoak

Conception dune base de donnesLes donnes de lexemplePartita, Bach & Busoni, Harmonia Mundi, 1986. Concerto, Dvoak, Sony, 1980. Date 2006 2005 2000

Premire tape (normalisation 0)Lister les donnes stocker Les structurer en entitsattributs (tableschamps) avec une information par champ.

Chaque ligne est un enregistrement (ou tuple, ou n-uplet). Le nom dune colonne est dit champ (ou attribut).

Application lexempleComment organiser ces donnes ? Disques titre compositeurs label date de sortie Disques titre compositeur1 compositeur2 label date de sortie

Les colonnes sont typesNumrique BOOLEAN, INT, DOUBLE . . . Texte VARCHAR(taille), TEXT . . . Listes ENUM(liste), SET(liste) Date/Heure DATE, TIMESTAMP . . .

p. 19

p. 20

Premire forme normaleAvanttitre Partita Concerto compositeur1 Bach Dvoak compositeur2 Busoni label Harmonia Sony date 1986 1980

Deuxime forme normaleRgles de normalisationSi plusieurs lignes ont des contenus similaires, la table doit tre dcoupe en sous-tables, Ces tables doivent tre relies par des cls trangres (rfrence une cl primaire).

Rgles de normalisationUne table pour chaque groupe de donnes associes, Pas de colonnes au contenu similaire, Chaque enregistrement doit avoir une cl primaire (identiant unique).

Application lexempleDisques Compositeurs id_disque id_compositeur titre id_disque [FK] label nom date de sortie Doublons dans la table Compositeurs = Normalisation rate !

Application lexempleid 1 2 3 titre Partita Partita Concerto compositeur Bach Busoni Dvoak label Harmonia Harmonia Sony date 1986 1986 1980

Disques id [PK] titre compositeur label date de sortie

p. 21

p. 22

Deuxime forme normale : application

Troisime forme normaleRgle de normalisationLes colonnes qui ne sont pas intrinsquement lies la cl primaire doivent tre dans une table spare.

Il faut crer une table de relation entre les tables Disques et Compositeurs. Disques Rel_Disq_Comp Compositeurs id_disque id_disque id_compositeur titre label id_compositeur nom date de sortie Un compositeur nest dni quune seule fois, mais peut tre mis en relation avec plusieurs disques.

Application lexempleLa colonne Disques.label contredit la rgle. Disques Labels id_disque id_label titre nom id_label date de sortie

p. 23

p. 24

Le modle relationnelOn peut lier les tables par des relations, classes en 3 types. Chaque cl trangre induit une relation entre 2 tables. Un Diagramme Entit-Relation (ERD) est une aide prcieuse.

Relations (1)Relation 1:1Chaque lment de la premire table est li au plus un lment de la seconde, et rciproquement. Cette relation est rare, elle scinde une table sans normalisation.

Exemple de la gestion dune liste de CDDisques Labels Disq_Comp Compositeurs id_disque id_disque id_compositeur id_label titre nom id_label id_compositeur nom date Disques Labels Compositeurs id_disque id_compositeur id_label titre nom id_label nom date

Relation 1:NChaque lment de Disques est li au plus un lment de Labels. Et un lment de Labels peut correspondre plusieurs disques. = Disques a une cl trangre sur la cl primaire de Labels. Disques Labels id_disque id_label titre date nom id_label

p. 25

p. 26

Relations (2)Relation N:MChaque lment de Disques est li plusieurs lments de Compositeurs, et rciproquement. Cette relation a besoin dune table de relation avec 2 cls trangres. Disques Disq_Comp Compositeurs id_disque id_disque id_compositeur titre id_compositeur nom date

TP - DisquesAdapter le modle de la base de donnes pour stocker des disques, chacun dcrit avec les informations : titre date de sortie commentaire du diuseur label interprtes compositeurs genres Tracer un ERD de la base.

p. 27

p. 28

Schma nal pour lexempleDisques Labels - id_label mediumint [PK] - nom varchar(50) id_label - id_disque mediumint [PK] - titre varchar(50) - date DATE - commentaire text - id_label mediumint [FK] Disques_Artistes - id_disque mediumint [FK] - id_artiste mediumint [FK] - typeRelation enum

Bonnes pratiques

id_disque

Normaliser est une recommandation gnrale. Parfois, il y a des exceptions (par ex. pour la performance). Prendre le temps de bien concevoir son modle. Les changements structurels sur une base en production peuvent tre calamiteux. viter en gnral les noms de type objet1, objet2. Utiliser une table ddie.

id_artiste id_disque

Artistes Genres id_genre - id_genre mediumint [PK] - intitule varchar(100) Disques_Genres - id_disque mediumint [FK] - id_genre mediumint [FK] - id_artiste mediumint [PK] - nom varchar(50) - prenom varchar(50)

Tester sa base avec un jeu de donnes. Surtout si la performance est importante. Utiliser un schema de la base (ERD).

p. 29

p. 30

Conventions de nommagePas de convention universelle. Il faut sen xer une et sy tenir. Noms en ASCII On vite ainsi les problmes dencodage ("" en latin1/utf-8/. . .) Fixer une rgle sur le singulier et le pluriel Table user ou users ? Composition des mots dans les noms Champ camelCase ou with_underscores ? Attention aux majuscules ! Sous Windows, le systme de chiers est indirent la casse = Les noms des bases et des tables sont concerns.

Cl primaireDclare dans la table avec le mot rserv PRIMARY KEY Chaque table devrait en avoir une (recommand) Au plus 1 PK par table (obligation) Les valeurs sont forcment uniques PK le plus compacte possible pour tre performante = presque toujours numrique (INT) Attribut AUTO_INCREMENT pour que MySQL numrote automatiquement les nouveaux enregistrementsSELECT LAST_INSERT_ID() renvoie la valeur utilise pour la PK

peut tre construite partir de 2 champs : PRIMARY KEY (colonne1, colonne2)

p. 31

p. 32

Cl trangre et contraintesCl trangreChamp qui rfrence une cl primaire dune autre table Pas forcment explicite dans la structure MyISAM Pas de dclaration des cls trangres InnoDB FOREIGN KEY (parent_id) REFERENCES parent(id)

TP - Agence immobilire (simplie)

Modliser la situation suivante : on veut reprsenter lorganisation dun ensemble dimmeubles en appartements et dcrire les informations sur les propritaires et les occupants. une personne occupe un seul appartement un appartement peut tre occup par plusieurs personnes (couples, colocataires) une personne peut possder plusieurs appartements un appartement peut appartenir plusieurs personnes (chacun avec quote-part) Crer une base InnoDB adapte et tracer son schema.

ContraintesAvec InnoDB, les cls trangres sont dclares. Les modications des donnes doivent conserver la cohrence. En cas dinsertion dune cl trangre sans cl primaire associe : Cannot add or update a child row : a foreign key constraint fails

p. 33

p. 34

Approche objet

Gestion de la structure de donnesAu niveau globalCREATE DATABASE mabase ; DROP DATABASE mabase ;

MySQL nest pas un SGBD Orient Objet. . .

Au niveau base de donnesMais on peut muler des fonctionnalits OO en relationnel. Comment remplacer les disques par une gestion de mdiathque ? Modliser : les attributs communs aux (disques, livres, images) les attributs distincts CREATE TABLE fournisseur ( id INT NOT NULL auto_increment , nom VARCHAR(255) NOT NULL , url VARCHAR(255) DEFAULT NULL , comment TEXT, PRIMARY KEY (id) ) ENGINE=InnoDB CHARSET=utf8 ; DROP TABLE matable ; RENAME TABLE matable TO latable ; ALTER TABLE matable ...p. 35 p. 36

Les types de donnesdualit reprsentation interne / achage (ex. TINYINT(3) ) Entiers : INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT Avec les options [UNSIGNED] [ZEROFILL] Dcimaux : FLOAT, DOUBLE, DECIMAL Heure et date : DATE, TIME, DATETIME, TIMESTAMP, YEAR Texte : CHAR, VARCHAR(0 255), TEXT. . . Listes : ENUM(homme,femme), SET(a,b,c) Extensions : SPATIAL... la valeur NULL (champ vide, ni , ni 0) Tout champ de type quelconque admet ou interdit NULL.

Manipulation des donnes Requtes SQL

p. 37

p. 38

Lire des donnes : SELECTSELECT renvoie une "table" : rsultat en lignes/colonnes.

Complments sur SELECTORDER BY : Trier les rsultatsSELECT * FROM articles ORDER BY nom ASC SELECT * FROM articles ORDER BY prix DESC, nom ASC

Syntaxe simplieSELECT expression FROM matable WHERE condition ; Une expression (et une condition) est compose de constantes : 3.14, chaine attributs : date, nom fonctions : CONCAT(nom, ,prenom) Exemples : SELECT * FROM commandes ; SELECT numcommande FROM commandes WHERE date>2006-01-01 ;

LIMIT : Limiter le nombre de rsultatsSELECT * FROM articles LIMIT 3 SELECT * FROM articles LIMIT 6,3

DISTINCT : Supprimer tout doublon dans les rsultatsSELECT DISTINCT nom FROM clients

Quelques fonctionsoprateurs : = < > != * / + etc. la comparaison de texte est sans casse et sans accents (interclassement par dfaut) LIKE : chanes contenant un motif donn SELECT nom FROM clients WHERE prenom LIKE A%p. 40

p. 39

ExercicesSur la base facsys : 1. Trouver les articles de plus de 50 euros. 2. Lister les noms des articles, tris par prix. Les trier par catgorie, puis par stock pour une mme catgorie. 3. Quelle dirence entre SELECT nom, idcategorie, description FROM categories et SELECT * FROM categories ? 4. Acher toutes les commandes de 2004. Les 3 commandes les plus rcentes. 5. Que donne SELECT COUNT(*) FROM articles ? Quelle dirence avec SELECT COUNT(articles.codearticle) FROM articles ? 6. Combien darticles de squash a-t-on ?

JointuresLe but : interroger plusieurs tables la fois Exemple :SELECT articles.nom FROM articles JOIN categories ON articles.idcategorie = categories.idcategorie WHERE categories.nom = squash

Variantes SELECT a.nom FROM articles AS a JOIN categories AS c ON a.idcategorie=c.idcategorie WHERE c.nom LIKE squash SELECT a.nom FROM articles a JOIN categories c USING (idcategorie) WHERE c.nom = squash implicite : SELECT a.nom FROM articles a, categories c WHERE a.idcategorie = c.idcategorie AND c.nom = squash

p. 41

p. 42

Jointures : exempleSELECT * nom Federer Nadal Ferrer FROM Joueurs id_pays 1 2 2 SELECT * FROM Pays id_pays 1 2 3 pays Suisse Espagne France

Exercices

1. Quels articles ont t commands par Pierre Durand ? 2. Combien darticles ont t expdis Paris ? 3. Lister les clients ayant command au moins deux fois. Ceux ayant command au moins trois articles dirents. 4. Acher tous les clients avec leurs articles associs. Avec seulement leur article le plus cher.

SELECT * FROM Pays JOIN Joueurs USING (id_pays) id_pays pays nom 1 Suisse Federer 2 Espagne Nadal 2 Espagne Ferrer

p. 43

p. 44

Les jointures externes2 types de jointures : INNER JOIN = jointure standard Fusion de 2 tables sur une valeur commune Les lignes sans correspondance ne sont pas gardes OUTER JOIN = jointure externe, note avec LEFT ou RIGHT Lune des deux tables est prioritaire = toujours cite pour toutes ses valeurs

Travailler avec NULLhttp://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

Source dune valeur NULLdonnes : champs non remplis certaines erreurs : 1/0 certaines fonctions : OUTER JOIN, ROLLUP...

Impactsur COUNT(col), mais pas sur COUNT(*)

Exemple : liste des clients nayant jamais commandSELECT nom, prenom, numcommande FROM clients LEFT JOIN commandes USING (idclient) . . . WHERE numcommande IS NULL ; Utilisation frquente : contrle de cohrence dune base, nettoyage

Traitement : logique tri-value (TRUE, FALSE, UNKNOWN)comparaison : val IS (NOT) NULL, ISNULL(val) comparaison : val1 val2 : prend en compte NULL IFNULL(v1, vdef) : si v1 est NULL, remplace par vdef NULLIF(val1, val2) : retourne NULL si val1=val2 COALESCE(v1,v2,...) : retourne la premire valeur non NULL

p. 45

p. 46

Les agrgats - GROUP BYButRegrouper les lignes de rsultats qui ont une valeur en commun Les fonctions dagrgats sappliquent chaque groupe (doc 11.11.1)

GROUP BY : complmentsFiltrageWHERE avant le partitionnement HAVING dans chaque groupe cr par le GROUP BY

Somme totale ExempleCombien de types darticles dans chaque catgorie ?SELECT idcategorie, COUNT(*) AS nbarticles FROM articles GROUP BY idcategorie ;

Si GROUP BY ... WITH ROLLUP, une ligne de total est ajoute Ex. : SELECT idcategorie, COUNT(*) AS nbarticlesFROM articles GROUP BY idcategorie WITH ROLLUP ;

En cas de GROUP BY multiple, ajout de lignes de sous-totaux.

Processus rsum Regroupement multi-colonnesGROUP BY col1, col2 regroupe les lignes pour lesquelles la paire (col1,col2) est identique. 1. 2. 3. 4. Partitionnement du rsultat (GROUP BY) Calcul des agrgats (fonctions COUNT(), MIN(). . .) Filtrage optionnel avec HAVING Sous-totaux optionnels avec WITH ROLLUPp. 48

p. 47

GROUP BY : TP1. Acher les noms des catgories avec leur nombre de types darticles. 2. Ajouter au tableau prcdent le prix moyen des articles dune catgorie. 3. Insrer une nouvelle catgorie. Comment la faire apparatre avec son nombre darticles de 0 ? 4. Donner le prix de chacune des commandes passes. 5. Acher pour chaque client le prix de chacune de ses commandes et la dpense totale. 6. Lister dans lordre dcroissant les montants des commandes de plus de 100 euros. Quelle est la commande la moins chre ? 7. Acher pour chaque commande la liste des types darticle quelle contient (une ligne par commande).

INSERTInsrer une ligne dans une table 2 syntaxes directes : INSERT INTO clients (idclient,nom,prenom) VALUES (SOR01,Sorel,Julien), ... Permet dinsrer plusieurs enregistrements ecacement INSERT INTO clients SET nom=Sorel, prenom=Julien Syntaxe commune avec UPDATE Si un champ na pas de valeur : sil est en AUTO_INCREMENT, il vaudra 1 de plus que le dernier (compteur interne) sinon, il prend la valeur par dfaut (souvent NULL ou )

p. 49

p. 50

INSERT. . . SELECTButAlimenter une table partir dune (ou plusieurs) autres INSERT INTO table [(col1, ...)] SELECT ...

INSERT et contrainte dunicitContraintes dunicit sur les enregistrements dune tablesur la cl primaire (forcment unique), ou sur un index dunique

ExempleCest Nol : cadeau promotionnel pour tous les clients qui ont pass une commande en 2008, sous la forme dune commande ctive gratuite, avec un cadeau unique rfrenc CAD08. 1. INSERT INTO commandes(idclient, date) SELECT DISTINCT idclient, 2008-12-25 FROM commandes WHERE date>=2008 2. INSERT INTO details(numcommande, numordre, codearticle, quantite, prix) SELECT c.numcommande, 1, CAD08, 1, 0.00 FROM commandes c WHERE c.date=2008-12-25

Trois faons de rgler le problme1. INSERT IGNORE INTO table ...conserve lancien enregistrement, oublie le nouveau transforme les erreurs (bloquantes) en avertissements (non bloquants)

2. REPLACE INTO table (3 mmes syntaxes quINSERT)remplace lancien enregistrement par le nouveau compte comme une (insert) ou deux oprations (delete+insert)

3. INSERT ... ON DUPLICATE KEY UPDATE c1=expr, ...remplace lINSERT par un UPDATE si ncessaire compte comme une ou deux oprations

p. 51

p. 52

Mise jour denregistrements : UPDATESyntaxe :UPDATE table SET col1=expr1 [, col2=expr2 ...] [WHERE ...] [ORDER BY ...] [LIMIT ...]

Suppression denregistrementsTRUNCATESupprime tout le contenu dune table Exemple : TRUNCATE TABLE matable ;

DELETE Exemples :UPDATE clients SET codepostal=38000, ville=Grenoble WHERE (nom,prenom)=(Sorel,Julien) Ajouter 2 units aux articles dont le stock est moins de 5 UPDATE articles SET stock=stock+2 WHERE stock= ALL(SELECT prix FROM articles) ;

Compatibilit avec le triOrdre des lignes non garanti par lopration UNION Tri possible sur lensemble du rsultat (SELECT a FROM t1) UNION (SELECT b FROM t2) ORDER BY a LIMIT 10 ; (les parenthses sont alors obligatoires)

Exemples(SELECT codearticle, prix FROM articles) UNION (SELECT codearticle, prix from details) ORDER BY prix ASC ;

ex. Trouver les dirences entre prix catalogue et commandes

p. 57

p. 58

Travaux Pratiques : amliorer la base FacSys1. Ajouter chaque client un champ datecreation 2. Linitialiser la date de sa premire commande 3. Implmenter un parrainage dun client par un autre. Chaque anne, envoyer des cadeaux aux trois plus gros parrains. 4. Donner la possibilit de classer un article dans plusieurs catgories. Ne pas perdre les attributions actuelles. 5. Organiser un suivi des commandes avec historique, en 4 tapes : commande reue saisie confectionne expdie, et une table oprateurs. 6. Faire une vue facture 7. Ajouter un prix dachat et une table des fournisseurs.

Administration MySQL

p. 59

p. 60

AuthenticationDes comptes ad-hoc, indpendants de lOS Un compte nest rien sans des attributions de privilges. Le login administrateur : root Pour changer le mot de passe root ( linstallation du serveur), 2 possibilits :mysqladmin -u root password SET PASSWORD FOR root = PASSWORD()

Privilges premier contactSHOW DATABASES ; USE mysql ; SHOW TABLES ; SHOW PRIVILEGES ; DESCRIBE user ;

Gestion des utilisateursCration CREATE USER username [IDENTIFIED BY ] Suppression DROP USER user

Puis DESCRIBE avec les tables : db, host, table-priv, column-priv En cas de modication directe dans la base mysql : FLUSH PRIVILEGES ; pour recharger les privilges.

p. 61

p. 62

Privilges GRANT et REVOKEGRANT Attribue des privilges un utilisateur (doc 12.5.1) REVOKE Enlve des privilges

TP Comptes et privilges1. Se connecter en root, crer un compte local observateur avec permission sur SELECT mais pas de modier. Vrier avec SHOW GRANTS FOR ... (doc 13.5.3.9) 2. Se connecter en tant quobservateur. Lister ses privilges. mysql -u observateur -p 3. Dans les bases mysql et facsys, lister les tables, regarder le contenu dune table, et tenter un INSERT. 4. Ajouter toutes les permissions observateur sur la base facsys. Vrier les privilges, puis tester. 5. Utiliser PhpMyAdmin pour supprimer les droits globaux de observateur. Tester. Quen dduire ? 6. Tester GRANT ALL ON ... TO ... WITH MAX_CONNECTIONS_PER_HOUR 2 MAX_QUERIES_PER_HOUR 3 MAX_UPDATES_PER_HOUR 4 ;

Les dirents niveaux auxquels sappliquent les privilgesserveur (ou global) base de donnes table colonne Privilge selon lhte de provenance du client (nom DNS ou IP)

ExemplesTous les privilges sur une base : GRANT ALL ON mabase.* TO paul@%.cuefa.fr ; Cration du compte en mme temps quun privilge global : GRANT SELECT ON *.* TO nouveau@% IDENTIFIED BY secret ;p. 63

p. 64

Privilges pour aller plus loinPermission sur des colonnesGRANT Select(titre, id) ON mabase.livre TO ...

Le serveur MySQLDmarrage et arrt du serveur, selon lOSLinux souvent via /etc/init.d/mysql Windows souvent un service, sinon via une interface ddie Parfois, manuellement : mysqld ou mysqld-nt (win)

Prcisions sur lhte user@host% gnrique, par exemple : lui@%.silecs.info localhost Connexion par socket (sous Linux) Connexion par TCP/IP Attention la rsolution de nom ! La rgle la plus spcique est choisie si plusieurs correspondent : SELECT USER(), CURRENT_USER() ; Vrier le type de connexion avec status dans la console mysql.

mysqladminInterface dadministration en ligne de commande Exemple : (arrt dun serveur distant) mysqladmin -h machine -u root -p shutdown

MySQL Administrator Supprimer la gestion des privilgesRedmarrer (temporairement) le serveur avec : mysqld --skip-grant-tables --skip-networking Interface graphique ocielle pour ladministration du serveur Brid si connexion distante au serveur

p. 65

p. 66

Conguration du serveur MySQLTrois possibilits complmentaires pour changer un paramtre : 1. Dans le chier de conguration, section [mysqld] Linux my.cnf dans /etc/ (ou /etc/mysql/) . . . Windows my.ini puis my.cnf dans C:\Windows . . . 2. En ligne de commande, au lancement du serveur Exemple : mysqld --key-buffer=16M 3. Dynamiquement, en cours dexcution session SET SESSION var:= ou SET @@var:= globale SET GLOBAL var:= ou SET @@global.var:= Privilge SUPER ncessaire pour la modication Pour obtenir la (longue) liste des paramtres et des variables : mysqld --verbose --help

Le chier de conguration : my.cnf / my.iniEmplacementsous Unix : /etc/my.cnf sous Debian : /etc/mysql/my.cnf Complt par un chier personnel : ~/.my.cnf sous Windows : C:\my.ini ou INSTALLDIR\my.ini

Organisation en sectionsclient mysqld mysql mysqldump ... : : : : options options options options passes tous les clients passes au serveur spciques la console mysql spciques au client de dump

Syntaxe gnrale"cl = valeur", ou parfois "cl", par exemple : key_buffer = 16M logp. 68

p. 67

Variables gnralitsNoms alphanumriques Noms insensibles la casse (depuis 5.0) PorteGlobale Session (parfois hrite dune variable globale)

Variables systme

(5.1.5)

dnies au lancement du serveur mysqldchier de conguration ligne de commande, ex. mysqld key-buffer=16M

listes avec : SHOW VARIABLES [LIKE ...] ; de type : statique Quelques variables constantes aprs le dmarrage dynamique La plupart sont modiables avec SET de direntes portes :globale : ex. connect_timeout locale (ou de session) : ex. autocommit ou les deux : ex. default_week_format = la variable session hrite de la variable globale

TypeSystme : @@var Utilisateur : @var

Sans prxe, MySQL suppose que la variable est du systme Dnies par la commande SET : SET @var := 1

p. 69

p. 70

Les chiers de logQuatre types dirents (5.2) : log (general query) : toutes les requtes reues par le serveurralentit normment le serveur trs volumineux

Messages derreur de MySQLErreurs Serveur (Annexe B-3)ex. ERROR 1193 (HY000) : Unknown system variable hop un numro derreur mysqld, entre 1000 et 1477 un code SQLSTATE sur 5 caractres, ANSI SQL un message derreur

log-bin : les requtes modiant le contenu des basesutilis pour la rplication de serveur plus compact que le prcdent (binaire), donc moins lent navigable avec le programme mysqlbinlog

Erreurs Client (Annexe B-4)un numro, entre 2000 et 2055 (ex : 2034) un message, ex. Invalid parameter number

log-slow-queries : les requtes longuesutilis pour le dbogage ou le prolage navigable avec le programme mysqldumpslow

log-error : les messages derreur du serveur Chaque variable indique le nom du chier o crire le log : log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 1

Erreurs systme (rare)un message de type ERROR ... not found (errno : 23) errno entre 1 et 152 = commande perror

Commandes SHOW ERRORS [LIMIT ...] et SHOW WARNINGS [LIMIT ...]

p. 71

p. 72

TP Conguration et variables1. Utiliser le paramtre language pour passer les messages derreurs du serveur en franais. 2. Quelle dirence entre les 2 paramtres du serveur : bind-address = 127.0.0.1 et skip-networking ? Exprimenter. 3. Sous Linux, utiliser les paramtres user et password pour pouvoir utiliser les clients (comme la console mysql) sans sauthentier. 4. Insrer une nouvelle catgorie dans la base facsys. Stocker le nouvel id (cl auto-incrmente) dans une variable MySQL. Utiliser cette variable pour insrer un nouvel article. 5. Activer le log sur les longues requtes. Produire plusieurs longues requtes avec la base INSEE. Par exemple, lister les 10 communes ayant connu le plus (le moins) de naissances cumules, ou le nombre de celles ayant eu 0 (1) naissance. Tester alors mysqldumpslow.

Import / Export de donnes - chiersExportSELECT ... INTO OUTFILE ...

ImportLOAD DATA INFILE ...

Exemple : importation au format CSVLOAD DATA INFILE donnees.csv INTO TABLE TableDonnees CHARACTER SET utf8 FIELDS TERMINATED BY , optionally ENCLOSED BY " IGNORE 1 LINES ; Utilis par les interfaces comme PhpMyAdmin.

p. 73

p. 74

SauvegardesDeux approches possibles : Sauvegarde binairecopie des chiers mysqlhotcopy pour MyISAM

TP Sauvegardes1. Utiliser mysqldump pour sauvegarder en SQL la base facsys. 2. Charger cette sauvegarde dans une base facsys_copy. 3. Essayer loption --xml pour mysqldump. 4. Essayer loption --skip-extended-insert. Pourquoi peut-elle tre utile ? 5. Faire une sauvegarde en 2 parties : schema et donnes. 6. Avec PhpMyAdmin, copier la base facsys. Passer les tables en InnoDB, puis dclarer les cls trangres (un index est ncessaire). Sauvegarder, vider et restaurer dans une nouvelle base. Trouver les bons rglages de PhpMyAdmin pour viter davoir une erreur.

+ rapide peu compatible (matriel, moteur SQL) attention aux modications en cours Sauvegarde textuelle (SQL)mysqldump SELECT INTO OUTFILE

+ compatible + cohrence garantie lent

p. 75

p. 76

Surveillance des processusListe des processusSHOW [FULL] PROCESSLIST ; FULL = colonne info complte (tronque 100 sinon) environ 30 commandes, dont sleep, query, execute... env. 60 tats dexcution, dont sending data, locked... privilge PROCESS ncessaire pour voir les autres clients

FLUSH et RESETFLUSH HOSTS : vide le cache des htes (chgt IP) FLUSH LOGS : ferme et rouvre tous les chiers de logs FLUSH PRIVILEGES : relit les privilges dans la base mysql FLUSH QUERY CACHE : optimise le cache des requtes FLUSH STATUS : eace les variables de connexion (session) FLUSH TABLES [...] : vide le cache des tables... FLUSH TABLES WITH READ LOCK : idem + verrou en lecture FLUSH USER_RESOURCES : remet zro les quotas utilisateurs RESET QUERY CACHE : vide le cache des requtes mysqladmin flush-... : quivalent partiel en ligne de commande

Interruption dun processusKILL [CONNECTION | QUERY] id ; QUERY = ninterrompre que la requte CONNECTION = couper aussi la connexion (par dfaut) privilge SUPER ncessaire pour tuer les autres clients quivalent en ligne de commande avec mysqladmin : mysqladmin processlist et mysqladmin kill

p. 77

p. 78

RplicationPrincipeSynchroniser plusieurs serveurs MySQL

FonctionnementModle matre/esclave : Les bases sont en lecture/criture sur le serveur-matre Les serveurs-esclaves sont des copies en lecture seule La rplication sur les esclaves se fait par les logs binaires

Pour aller plus loin. . . Vues, tables temporaires, jeux de caractres, moteurs de stockage, InnoDB

IntrtScurit Avec un matre en intranet et des esclaves publics Fiabilit Esclaves remplaables, et sauvegardent le matre Performance Possibilit de rpartir les requtes SQL sur plusieurs esclaves

p. 79

p. 80

Vues GnralitsConceptVue = requte SELECT stocke, prsente comme une table (table dynamique).

Vues Pourquoi faire ?Cas dutilisationLimiter laccs aux donnes sensibles Ex. : ne permettre de voir que la synthse et non le dtail Faciliter la consultation des donnes usuelles Ex. : dnormaliser les tables (jointures) Changer la structure dune base en gardant la compatibilit Ex. : Lancien format est mul par une vue sur le nouveau Simplier des critures de requtes Ex. : Remplacer une sous-requte par une vue

ExempleCREATE VIEW ClientsBref AS SELECT prenom, nom, idclient, ville FROM clients ; SELECT FROM ClientsBref ;

Commandes SQLCREATE [OR REPLACE] VIEW vue AS ... ALTER VIEW DROP VIEW SHOW FULL TABLES [WHERE Table_type="VIEW"] SHOW CREATE VIEW

InconvnientsLa performance dune requte sur une vue est souvent excrable. = La vue est plus un outil dadministrateur que de dveloppeur

p. 81

p. 82

Vues Exemples1. Dnir une vue Varticles qui contienne le nom complet de la catgorie de chaque article. 2. Dnir une vue Vcommandes qui augmente la table commande avec le nom du client, le nb de lignes de sa commande, et un champ montant. 3. Ajouter cette vue une colonne articles qui liste les articles de chaque commande sous la forme : "4 tuba, 2 bouteilles air". 4. Crer une vue Vclientsbref contenant lidentiant des clients, leur ville et le montant total de leurs commandes. Donner un utilisateur "stagiaire" le droit de consulter cette vue uniquement. 5. Dnir une vue Vsucces qui liste les articles les plus vendus (en units) dans lordre dcroissant. Le champ "vendu" vaudra 0 pour les articles jamais commands. 6. Acher le nombre de ventes par catgorie.

Vues Paramtres avancsPrivilges et scuritDeux privilges consacres aux vues : CREATE VIEW : ncessaire pour crer une vue SHOW VIEW : pour voir la dnition dune vue (avec SHOW CREATE VIEW) Attribus avec GRANT.

Clause ALGORITHM=...MERGE vue remplace par sa dnition dans requte dappel TEMPTABLE utilisation dune table temporaire UNDEFINED MySQL fait le meilleur choix (MERGE si possible)

p. 83

p. 84

Vues modiablesModier les donnes via une vueCertaines vues acceptent des UPDATE, DELETE, INSERT si : La modication ne concerne quune seule table Chaque ligne de la vue se rfre une unique ligne de la table Nombreuses autres restrictions complexes cf doc 21.4.3 Updatable and Insertable Views Exemples : UPDATE Varticles SET nom=CONCAT(nom, *) WHERE categorie=squash ; : Query OK, 3 rows aected UPDATE Vsucces SET nom=CONCAT(nom, *) WHERE vendus>=5 : chec, GROUP BY incompatible !

Tables temporaires et tables en mmoireTables temporairesCREATE TEMPORARY TABLE matable ... syntaxe identique une cration de table standard temporaire : existence limite la dure de la connexion isole : accessible uniquement dans cette connexion privilge ncessaire : CREATE TEMPORARY TABLES

Tables en mmoireCREATE TABLE matable (...) ENGINE MEMORY ; existence limite la dure du serveur table partage entre tous les clients privilge ncessaire : CREATE TABLES

Appliquer le ltre de la vue aux modicationsSi on ajoute WITH CHECK OPTION une vue : UPDATE/INSERT doivent vrier la clause WHERE de la vue

p. 85

p. 86

Internationalisation conceptsAu commencement tait lASCIILa manipulation du texte utilise 3 concepts : Le jeu de caractres utilis (character set) Exemples : Alphabet latin, Unicode Lencodage du texte (encoding) Exemples : latin1 (ISO-8859-1), UTF-8, UTF-16 La rgle dinterclassement (collation) Dtermine lordre de tri, le mode de comparaison, etc. Langlicisme collation est frquent. On emploie souvent abusivement charset pour encoding.

Francisation. . .. . . des messages de MySQLChanger la langue des messages derreur, etc. Paramtre du serveur : language=french Pas recommand !

. . . des fonctions MySQLDates en franais avec : SET lc_time_names = fr_FR ; Cf doc 5.8 Server locale Exemple : SELECT DATE_FORMAT(NOW(), %W %e %M %Y) ;

Rappelsi18n Gestion de texte multilingue dans lapplication l10n Traduction de linterface utilisateur

p. 87

p. 88

Jeux de caractres & encodage2 niveaux de choix : charset de stockage dans la table charset du client MySQL convertit la vole si ces 2 encodages sont dirents.

Linterclassement (collation)Rleordre alphabtique tendu pour comparaison, tri... classes de caractres quivalents, par ex. {e, , , , , E, . . .}

Impact surles oprateurs =, >, BETWEEN, LIKE. . . les commandes GROUP BY, ORDER BY On peut souvent changer localement la collation, par exemple : ORDER BY nom COLLATE utf8_turkish_ci

Quel encodage choisir ?36 disponibles 2 principaux : latin1 et utf8 (recommand) Parfois, choisir ascii ou binary pour viter toute conversion

Comment imposer le charset des requtes et rsultats ?SET NAMES utf8 ; passe le client en UTF-8 SHOW VARIABLES LIKE char% ; liste les paramtres de conguration (pour une gestion plus ne que NAMES) cf doc 9.1.4. Jeux de caractres et collations de connexion

Principes de fonctionnementplusieurs collations par jeu de caractre, dont 1 par dfaut nom de type charset_collation_var (ex. : utf8_general_ci) avec var { ci, cs, bin } = { sans casse, avec, pas de classes } possibilit dajouter au serveur une collation personnalise

p. 89

p. 90

En pratique et en rsum la cration de la base, xer charset et collation par dfaut des tables (et ventuellement de la base) :CREATE TABLE t1 (...) CHARACTER SET utf8 COLLATE utf8_general_ci ;

Internationalisation TP1. Tester SHOW CHARACTER SET et SHOW COLLATION. 2. Dans une console mysql, acher actuelle la conguration du charset client. 3. Permuter son encodage client (latin1 utf8) 4. Tester un SELECT, un INSERT, et la fonction UPPER. Visualiser le rsultat de linsertion dans PhpMyAdmin. 5. Reprendre lencodage correct du client. Corriger lenregistrement mal encod grce UPDATE et aux fonctions BINARY et CONVERT( USING utf8). 6. Comment lister les clients venant de la ville Nmes (pas nme ou Nime) ?

Suivant les cas, adopter des valeurs direntes pour certains champs (mots de passe, identiants textuels. . .) :CREATE TABLE t1 ( ref VARCHAR(8) CHARACTER SET ascii COLLATE ascii_bin

chaque connexion du client, dclarer lencodage souhait : SET NAMES latin1 ; Parfois lAPI le permet directement, sans SQL. Si on utilise DATE_FORMAT() et consort, les prcder dun : SET lc_time_names = fr_FR ; Relire la doc MySQL

p. 91

p. 92

Verrous - gnralitsLOCK TABLES nom-table [[AS] alias] lock-type [, nom-table [[AS] alias] lock-type] ... ( lock-type: READ | [LOW_PRIORITY] WRITE ) ... UNLOCK TABLES Priorit : WRITE > LOCAL > LOW_PRIORITY WRITE READ : empche lcriture ; tout le monde peut lire WRITE : empche tous les autres accs Notesdoit porter sur toutes les tables utilises, mme multiples privilge LOCK TABLE ncessaire en complment du SELECT sapplique aussi sur les vues pas de sens sur une table temporaire

Verrous - les piges

Dverrouillages implictespose dun nouveau verrou dbut de transaction perte de connexion client - serveur

Attention aux interactions verrou - transactions FLUSH TABLES WITH READ LOCK : verrou globalprioritaire ncessite le privilge RELOAD

p. 93

p. 94

Les moteurs de stockageMyISAM le moteur par dfaut, dorigine ABSofttrs rapide pour des requtes et des tables simples faible empreinte disque

Particularits de MyISAM

limitations : ni cls trangres, ni transactions cache mmoire des index seulement index non plaant (B-Tree) stockage disque en 3 chiers par table : .frm (structure), .MYD (donnes), .MYI (index) tables et index trs compacts sur disque indexation spcique : FULLTEXT et SPATIAL

InnoDB moteur sophistiqu : intgrit, transactionsdvelopp par InnoBASE, rachete par Oracle moteur plus complexe

Memory tout le stockage en RAM ; perdu larrt serveur Archive prvu pour la journalisation = INSERT et SELECT seulement Merge fusion virtuelle de plusieurs tables MyISAM Maria (dv.) successeur prvu pour MyISAM Falcon (dv.) successeur prvus pour InnoDB

p. 95

p. 96

Particularits dInnoDB

Intgrit rfrentielleDnition des cls trangres CREATE TABLE table | ALTER TABLE table ADD [CONSTRAINT symb] FOREIGN KEY [i-fkey-id] (col1, ...) REFERENCES nom-table (col1, ...) [ON DELETE [RESTRICT | CASCADE | SET NULL ]] [ON UPDATE [RESTRICT | CASCADE | SET NULL ]]RESTRICT rejette la modication, avec un message derreur CASCADE rpercute la modif sur la table rfrence SET NULL eectue laction et anNULLe la cl sur la table rfrenante

dclaration possible des cls trangres vrication de lintgrit rfrentielle support des transactions, avec 4 niveaux disolation utilisation dindex plaant (clustering) sur la cl primaire (B-Tree+) cache mmoire des donnes aussi stockage disque en un chier par table : .frm tables et index plus volumineux sur disque

Activation : SET FOREIGN_KEY_CHECKS := 0|1 ;

p. 97

p. 98

TransactionsThorie : proprits ACID pour les transactions Atomicitrgle du tout ou rien sur une squence doprations inclut la rversibilit des opration

Transactions - utilisation

Cohrencerespect des rgles de cohrence aprs la transaction, quel que soit le rsultat

START TRANSACTION COMMIT [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [AND [NO] CHAIN] [[NO] RELEASE] SET AUTOCOMMIT = [0 | 1] Options :CHAIN enchane immdiatement une autre transaction RELEASE coupe la connexion la n de la transaction

Isolationles donnes dans un tat intermdiaire ne sont pas visibles des autres sessions assure la cohrence des donnes entre transactions minimise limpact sur les performances

SAVEPOINT identifier ROLLBACK TO identifier RELEASE SAVEPOINT identifier

Durabilitune fois termine, la transaction ne peut tre remise en causep. 99 p. 100

Transactions - verrous en lecture

Transactions - isolationCommande SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL niveau Niveaux disolation, du plus faible au plus fort :READ UNCOMMITTED accs en lecture aux autres transactions, o quon soit READ COMMITTED accs en lecture aux transactions valides REPEATABLE READ accs en lecture identique au premier SELECT de la transaction SERIALIZABLE comme prcdemment, mais tous les SELECT sont LOCK IN SHARE MODE

Deux types de verrous sur les lignesverrou partag (S) : permet tous de lire la ligne, et de poser (S) verrou exclusif (X) : interdit aux autres de poser (S) et (X) sur la ligne

verrous implicites poss en lectureSELECT ... LOCK IN SHARE MODE pose un verrou (S) sur chaque ligne lue SELECT ... FOR UPDATE pose un verrou (X) sur chaque ligne lue verrou valide jusqu la n de la transaction

p. 101

p. 102

Procdures stockes PrsentationUne procdure stocke est un ensemble de commandes SQL qui est stock sur le serveur. La PS peut tre ensuite appele (avec des paramtres).

Procdures stockes et fonctions Curseurs, triggers. . .

Caractristiquesapparues en MySQL 5.0 encore incomplet par rapport dautres SGBD langage procdural driv de SQL, sous-ensemble de SQL/PSM 2 types de routines (PS) : fonctions renvoient une valeur dans une expression, mais restrictions sur les oprations procdures ne sutilisent pas dans les expressions, mais ont une syntaxe plus riche

p. 103

p. 104

Procdures stockes Pourquoi ?Pourquoi mettre du code dans une base de donnes ?Pour Formatage des donnes Code extrieur plus simple, donnes envoyes plus adaptes Gain de rapidit Moins daller-retour MySQL langage extrieur (PHP, etc.) Abstraction de la structure SQL API sur les donnes en cas de clients multi-langages Contre Incohrence de mlanger code et donnes Pnibilit de modier (et dboguer) le code des PS Pauvret du langage Incompatibilit avec dautres SGDB Code externe (PHP. . .) moins autonome, dicile suivre

Procdures Exemplesmysql> CREATE PROCEDURE liste_clients () SELECT idclient, nom, prenom FROM clients ; mysql> CALL liste_clients ();

CREATE PROCEDURE cadeau (IN idbonclient CHAR(6)) COMMENT ajoute au client une commande gratuite pour 1 CAD09 BEGIN DECLARE nouv_numcommande INT DEFAULT 0; DECLARE nbclients INT DEFAULT 0; SELECT COUNT() INTO nbclients FROM clients WHERE idclient=idbonclient IF nbclients = 1 THEN INSERT INTO commandes (idclient, date) VALUES (idbonclient, NOW()); SET nouv_numcommande = LAST_INSERT_ID(); INSERT INTO details VALUES (nouv_numcommande, 0, CAD09, 1, 0.0); SELECT Oert; ELSE SELECT Erreur; END IF; END//

p. 105

p. 106

Procdures stockes - syntaxeCREATE PROCEDURE nomprocedure (IN param1 Type1, OUT param2 Type2, INOUT p3 Type3...) [ ... ] [ COMMENT mon commentaire ] BEGIN ...... END Les paramtres sont en entre ou sortie (IN/OUT/INOUT) Par dfaut, si non prcis, le paramtre est en IN Les types des paramtres sont les types standards de MySQL Les SELECT non redirigs sont achs CALL nomprocedure(...) ; pour lutiliser

Code dune procdureDECLARE mavariable [DEFAULT valeur] Variable locale, par ex. DECLARE nb INT DEFAULT 0 ; SET mavar = texte Aecte une valeur une variable SELECT ... INTO mavar FROM ... Aecte des valeurs des variables partir dun SELECT SELECT nom, prenom INTO var1, var2 FROM clients LIMIT 1 ; IF a=0 THEN .. ELSEIF a>2 THEN .. ELSE .. END IF ; WHILE count < 10 DO SET count = count + 1 ; END WHILE ;

p. 107

p. 108

Syntaxe des commentairesEn plus du COMMENT sur une procdure, documenter le code : aprs un "#", jusqu la n de la ligne aprs un "- - " (tiret-tiret-espace), jusqu la n de la ligne /* syntaxe C */ ventuellement multiligne /* ! variante syntaxe C */ Commentaire en MySQL, pas dans les autres SGBD SQL SELECT * FROM articles /* WHERE prix CREATE FUNCTION abrege (texte VARCHAR(255)) RETURNS VARCHAR(30) RETURN CONCAT( LEFT(texte,27), ... ) ; mysql> SELECT abrege(Cette phrase est trop longue !) ;

p. 111

p. 112

Fonctions - syntaxeCREATE FUNCTION nomfonc ([para1 type1, para2 type2...]) RETURNS type LANGUAGE SQL | [NOT] DETERMINISTIC | {CONTAINS SQL | NO SQL | {READS | MODIFIES} SQL DATA} | SQL SECURITY { DEFINER | INVOKER } | COMMENT chaine [label:] BEGIN ... RETURN ... END [label] SHOW FUNCTION STATUS ; SHOW CREATE FUNCTION nomfonc ; variable systme login_bin_trust_function_creators

Fonctions - exemplesFonctions simplesEcrire une fonction Majuscule qui prend une chane, et la retourne en minuscules, sauf la premire lettre en majuscules. A laide de la prcdente, crire une fonction PreNom, qui prend deux chanes et ache "Prnom Nom" bien typographis.

Fonctions requtesEcrire une fonction MontantCumule qui retourne le montant total command par un client de la base facsys. partir du nom dun nouveau client, retourner un nouveau idclient unique (rappel : 3 premiers caractres du nom, suivis dun numro, par ex. DUR005).

p. 113

p. 114

Contrle de ot - les tests IF et CASEIF (condition) THEN ... ; [ ELSEIF (cond2) THEN ... ; ] [ ELSE ... ; ] END IF

Contrle de ot - les boucles[label:] LOOP ... END LOOP [label] [label:] REPEAT ... UNTIL (condition) END REPEAT [label] [label:] WHILE (condition) DO ... END WHILE [label]

CASE valeur [ WHEN valeur1 THEN ... ; ] [ ELSE ...; ] END CASE

xN

CASE [ WHEN condition1 THEN ... ; ] [ ELSE ... ; ] END CASE

xN

Les chappementsLEAVE label : quitte la boucle ITERATE label : recommence la bouclep. 116

Ne pas confondre avec les fonctions IF() et CASE.p. 115

Curseurs

Curseurs - exemple

GnralitsAutoriss lintrieur des routines : procdures, fonctions, triggers Passage un parcours classique dune liste de rsultats : boucle sur les lignes Chaque curseur est associ un SELECT

Dnir une procdure qui ache la somme des montants des N articles les plus chers et la somme totale du stock correspondant Dnir une fonction qui ache la mdiane dune liste de valeurs

CommandesDECLARE mon-curseur CURSOR FOR SELECT... OPEN mon-curseur FETCH mon-curseur INTO var1, var2, ... CLOSE mon-cuseur

p. 117

p. 118

Handlers - gestion derreurDECLARE handler-type HANDLER FOR h-condition1 [, h-condition2] [ instruction | BEGIN ... END ] ; h-type: CONTINUE | EXIT | UNDO h-condition: SQLSTATE valeur | mysql-code-erreur | SQLWARNING | NOT FOUND | SQLEXCEPTION | nom-condition mthode MySQL pour intercepter les erreurs souvent associ aux curseurs (NOT FOUND), mais pas seulement souvent linstruction positionne un boolen (SET ni :=1)

Conditions dnies pour le Handler

DECLARE nom-condition CONDITION FOR valeur-condition valeur-condition: SQLSTATE valeur | mysql-code-erreur Faon de dnir un alias pour une erreur ou une famille derreurs.

p. 119

p. 120

Messages derreur de MySQLErreurs Serveur (Annexe B-3)ERROR 1193 (HY000) : Unknown system variable hop un numro derreur mysqld, entre 1000 et 1477 un code SQLSTATE sur 5 caractres, ANSI SQL un message derreur

Curseurs et handlers - exemples

Erreurs Client (Annexe B-4)un numro, entre 2000 et 2055 (ex : 2034) un message, ex. Invalid parameter number

Implmenter une fonction maximum avec plafond sur les prix des articles : ne prend pas en compte les valeurs suprieures au plafond donn en paramtre.

Erreurs systme (rare)un message de type ERROR ... not found (errno : 23) errno entre 1 et 152 = commande perror

p. 121

p. 122

Dclencheurs (Triggers)But : dclencher une action complmentaire lors de la modication (Insert, Delete, Update) dun enregistrement dans une table. CREATE TRIGGER nom t-moment t-modif ON table FOR EACH ROW instruction ; | FOR EACH ROW BEGIN ... END ; t-moment = BEFORE | AFTER t-modif = INSERT | UPDATE | DELETE Complment : DROP TRIGGER [IF EXISTS] nom et SHOW TRIGGERS Valeurs : NEW.champ (Insert, Update) = modiable OLD.champ (Delete, Update) = lecture seule.

Triggers - ExemplesGestion du stock des articles lors de la commande : CREATE TRIGGER majstock AFTER INSERT ON facsys.details FOR EACH ROW UPDATE articles SET stock := stock - NEW.quantite WHERE codearticle=NEW.codearticle ; Faire un compteur des montants et des commandes du jour (variable session). Si on commande des balles de squash : 5 au minimum. Ex. courant : journalisation des actions critiques sur une table Ex. : dnir une valeur par dfaut dynamique pour une colonne. Ex. 75 = Paris.

p. 123

p. 124

En rsum

Variables utilisateursex. @var

(doc 8.4)

locales = porte toujours limite la session (connexion)

Dclarations dans un bloc BEGIN... END1. Variables : DECLARE DEFAULT , ... 2. Conditions : DECLARE CONDITION FOR ... 3. Curseurs : DECLARE CURSOR FOR SELECT ... 4. Handlers : DECLARE HANDLER FOR ...

AectationAectation directeSET @a := 4, @b := "Dupont" ; SET @c := LEFT(@b, @a) ;

Aectation par requteSET @p1 := (SELECT MIN(prix) FROM articles) ; SELECT @p2 := MIN(prix), @p3 := MAX(prix) FROM articles ; SELECT MIN(prix), MAX(prix) INTO @p4, @p5 FROM articles ; obligatoire pour les routines

p. 125

p. 126

Commandes prparesContexte dutilisation normalAPI pour les langages prvus :natifs : C, Java (Connector/J), .NET surcouches lAPI C : mysqli (PHP)...

Commandes prpares - Usages

SQL pour mise au point / dbogage

UsagesOptimisation : requte paramtres, prcompile sur le serveur Mta-programmation : construction dune requte en SQLUtilisation dune chane quelconque pour crer un PREPARE

Syntaxe des commandesPREPARE stmt-name FROM preparable-stmt EXECUTE stmt-name [USING @var-name [, @var-name] ...] DROP PREPARE stmt-name

ExemplePREPARE clientsNom FROM "SELECT nom, prenom, ville FROM clients WHERE Nom > ? " ; SET @nomdeb := "E" ; EXECUTE clientsNom USING @nomdeb ;p. 127 p. 128

Optimisation ct serveurMatrielRAM Disques durs rapides

Buers

Optimisation des requtes et du serveur

Adapter la taille des buers de MySQL key_buer_size = 256M read_buer_size = 1M max_allowed_packet = 16M

CacheAugmenter la taille des caches query_cache_size=64M query_cache_type=1 query_cache_limit=256K

p. 129

p. 130

Les index : amliorer les performancesFonctionnement dun SELECT ou JOINSELECT a.nom, c.nom FROM articles a JOIN categories c USING (idcategorie) WHERE a.stock>2005 Sil ny a pas dindex, MySQL parcourt toute la table articles pour trouver les stocks toute la table categorie pour trouver les idcategorie

Les indexGnralitsporte sur une ou plusieurs colonnes de la table possde un nom distinctif (PRIMARY pour la cl primaire)

Les types dindex - pour lutilisateurCl primaire : unique pour une table + contrainte dunicit INDEX simple : pour les recherches... UNIQUE INDEX : recherche + contrainte dunicit FULLTEXT : index plein texte SPATIAL : index gomtrique - extension SPATIAL

IndexPermet MySQL de trouver rapidement une valeur Cl primaire = index Cl trangre = index (presque toujours) Contrainte dunicit UNIQUE = index

Les type dindex interneHASH : fonction de hachage (par dfaut en MyISAM) B-Tree : arbre quilibr (par dfaut en InnoDB) R-Tree : index spatial FULLTEXT

p. 131

p. 132

Index - utilisationDsactivation temporaire pour insertion massiveALTER TABLE table DISABLE KEYS; ... ALTER TABLE table ENABLE KEYS;

Optimisation dune requte principesExcution dune requte SQL1. Analyse et traductionanalyse et vrication syntaxique vrication de la validit (existences...) vrication des permissions produit une liste doprations

Utilisation couranteutilisation automatique pour JOIN, ORDER... (cf EXPLAIN) utilisation force sur JOIN, ex. : SELECT * FROM t1 USE INDEX (col1, col2) indication = USE | IGNORE | FORCE

2. Optimisationutilise le dictionnaire : index, taille des tables... produit le plan dexcution (arbre)

Cache dindexCACHE INDEX LOAD INDEX INTO CACHE

3. Excution de larbre de la requte

p. 133

p. 134

Plan dexcution - principeDonnes intermdiaires : pipelining vs matrialisationpas de stockage retour immdiat des premiers rsultats au client

EXPLAIN - syntaxeEXPLAIN SELECT ... ; EXPLAIN EXTENDED SELECT ... ; SHOW WARNINGS ; ne sapplique qu SELECT = reformuler les UPDATE, INSERT... EXPLAIN : ache une vue du plan dexcution EXPLAIN EXTENDED : reconstruit un SQL canonique EXPLAIN SELECT * from articles WHERE prix >50.0 \G limites et imprcisions dEXPLAIN

Oprations bloquantes :tris : ORDER BY ddoublonnage : DISTINCT certaines fonctions daggrgation globales : MIN(), MAX(), SUM()... partitionnement : GROUP BY

Arbre dexcution

p. 135

p. 136

EXPLAIN - colonnescolonnes dEXPLAIN id le numro de SELECT dans la requte select_type type de SELECT, simple ou complexe... table la table concerne, ou lalias type le type daccs cette table choisi par MySQL possible_keys les cls utilisables ( premire vue) key la cl choisie par MySQL pour lopration key_len la longueur de cl utilise en octets ref la colonne rfrence par la cl choisie rows nombre de lignes parcourues (estimation) Extra infos complmentaires, selon champs prcdents

EXPLAIN - colonnes id, select_type, tablecolonne id : 1, 2, 3... et NULL, non unique colonne select_type SIMPLE PRIMARY SUBQUERY DERIVED UNION UNION RESULT colonne table :nom (ou alias) de la table concerne derivedN : en cas de sous-requte dans FROM unionX,Y... : en cas dUNION lordre des lignes indique lordre du plan dexcution

le SELECT ne contient ni sous-requte ni UNION requte principale sous-requte autre quapparaissant dans le FROM sous-requte apparaissant dans le FROM 2e partie (et suivantes) dune UNION encapsule tous les SELECT dune UNION

p. 137

p. 138

EXPLAIN - colonne type

EXPLAIN - colonnes clscolonne possible_keys (informatif)liste dtermine la phase danalyse peut rester inutilise aprs optimisation

ALL index index range ref eq_ref const, system NULL

parcourt toutes les lignes de la table () parcourt toutes les lignes dans lodre de lindex (Extra=Using index) parcourt tout lindex parcourt un intervalle dindex accs index direct - valeurs multiples possibles accs index direct - au plus une valeur de retour remplac par une constante dans loptimiseur rsolu immdiatement par loptimiseur

colonne keyssouvent une cl de la liste parfois aucune ne convient = NULL parfois une cl extrieure la premire liste

Note : ALL systmatique pour les petites tables

colonne key_lenlongueur utilise en octets si cl multicolonnes, peut tre infrieure au total

p. 139

p. 140

EXPLAIN - colonnes ref, rows, Extracolonne refutilise si une cl est dclare rfrence des champs des lignes prcdentes ou des constantes

Mtadonnes et statistiques utiles

SHOW TABLE STATUS LIKE table \G colonne rowsnombre de lignes (estim) parcourir relatif au point courant du plan dexcution estimation dpend des statistiques sur la table (cf plus loin) nglige les LIMIT (jusqu v.5.1)

ANALYZE TABLE table ; que peut-on prvoir comme optimisation de la strucure de communes ?

colonne Extra Using Using Using Using index where temporary lesort utilise un index couvrant : vite laccs la table post-ltrage des lignes retournes utilisation dune table temporaire (tri...) tri externe, en mmoire ou sur disque

p. 141

p. 142

BenchmarkCommande BENCHMARK

Proling

Recherche des tapes longues dans un processus Limite une valuation dexpression Limite lexcution de la requte par le serveur Exemples SET @input := "mon mot de passe secret"; SELECT BENCHMARK(1000000, MD5(@input)); SELECT BENCHMARK(1000000, SHA1(@input)); SELECT BENCHMARK(10,(SELECT MAX(naiss) FROM naissances)); SELECT BENCHMARK(10,(SELECT @v:=MAX(naiss) FROM naissances)); Analyse a posteriori utiliser log-slow-queries Analyse en direct : commandes SQLSET @@profiling :=1 ; SHOW PROFILES ; SHOW PROFILE [ ALL ] FOR QUERY ... ; types : BLOCK IO, CPU, MEMORY, PAGE FAULTS, SWAPS... paramtre : profiling_history_size (=15)

p. 143

p. 144

INSERT... les clauses particulires

Bibliographie complmentaire

Priorit : utiliser avec prcautionLOW_PRIORITY : insertion remise plus tard, quand plus aucun accs en lecture ne sera en cours ; bloquant. HIGH_PRIORITY : outrepasse le LOW_PRIORITY dni au niveau serveur. DELAYED : insertion remise plus tard, quand le serveur aura le temps ; non bloquant. Tous les INSERT DELAYED du tampon sont groups.

MySQL Reference Manual 5.0, AB Soft (plus complet en anglais) Matriser MySQL 5, OReilly France (2005), Darmaillac et Rigaux MySQL Cookbook, 2nd Ed (2006), OReilly MySQL Stored Procedure Programming (2006-03), OReilly High Performance MySQL, 2nd Ed. (2008-06), OReilly Expert MySQL 5, Apress (2007-01)

p. 145

p. 146

Informations utiles

Licence

Pour garder le contact : [email protected] Les documents utiliss sont disponibles en ligne : http://silecs.info/dld/MySQL/ Transparents noncs et corrections des exercices

Copyright (c) 2007-2009 Franois Gannaz, Guillaume Allgre

Permission vous est donne de copier, distribuer et/ou modier ce document selon les termes de la Licence GNU Free Documentation License, Version 2.0 ou ultrieure publie par la Free Software Foundation ; pas de section inaltrable ; pas de texte inaltrable de premire page de couverture ; texte inaltrable de dernire page de couverture : Auteurs : Franois Gannaz, Guillaume Allgre, SILECS