Archives de catégorie : G – MySQL

Présentation de MySQL

logo mysql est un système de gestion de bases de données relationnel extrêmement répandu sur le Web.

La première version de MySQL est apparue le 23 mai 1995.
MySQL est la propriété d’une société suédoise : MySQL AB, fondée par David Axmark, Allan Larsson et Michael Widenius.

SGBD ?

Très simplement, un SGBD (ou système de gestion de base de données) est un logiciel qui permet de gérer (consulter, mettre à jour, organiser) une base de données.
Une base de données est simplement un ensemble structuré d’information, partagées, stocké dans un ou des fichiers sur disque.

Un SGBD est un système complexe permettant de gérer de manière efficace, un volume important de données structurées, accessible par des utilisateurs simultanés locaux ou non.
Suivant les versions utilisées, MySQL offre une palette très variée de fonctionnalités, allant du gestionnaire de fichier évolué pour le web, au SGBD d’entreprise.

Caractéristique d’un vrai SGBD :

  • gestion de gros volumes de données (en consultation et en mise à jour)
  • gestion de la concurrence d’accès en lecture et écriture (avec une bonne granularité),
  • gestion (efficace) des transactions,
  • portabilité sur différents OS, des données et du code
  • sécurité des données, qui se décline en :
    • bonne ou haute disponibilité
    • fiabilité
    • confidentialité
    • cohérence
    • tracabilité
  • Administrabilité :
    – existence d’outils d’administration généraux : gestion des données, des utilisateurs, des fichiers physiques, des espaces logiques, des droits, des profils, des ressources systèmes, etc.
    – outils de surveillance
    en temps réel grâce à un moniteur, si possible graphique ou en temps différé grâce à des journaux ou à des traces paramétrables
  • possibilité d’export import :
    de, ou vers des fichiers texte, des logiciels bureautique ou des fichiers Gros systêmes par exemple
  • performances :
    offrir de bonnes performances et des outils permettant de les mesurer et de les contrôler via des paramètres de configuration. Des processus d’optimisation en temps réel des requêtes complexes sont également souvent présents. Les données peuvent être indexées, de manière souple, dynamique et complète (index simples, concaténés, multiples, tables de hashage, recherche textuelle, etc.). Un nombre important d’utilisateurs, ainsi qu’un volume conséquent de données peuvent être pris en compte.

Relationnel ?

Il existe plusieurs familles de SGBDs en fonction du modèle de données utilisé. Historiquement on a le modèle hiérarchique, réseau, relationnel puis objet. Le modèle relationnel est de très loin le plus répandu avec des fers de lance comme MySQL, Postgres, Oracle, Microsoft SQL Server, IBM DB2, etc.

Quelques caractéristique du relationnel

  • le modèle sous jacent est simple (on manipule des tables ), bien formalisé et épprouvé,
  • accès simple via un langage de requêtes SQL normalisé
  • opérateurs simples et puissants
    opérateur de projection (ou selection), restriction, produit cartésien, union, différence,
  • indépendance entre stockage physique et vision logique des données :
    Les données (le plus souvent des tables) sont référencées de manière logique. Un dictionnaire de données permet de retrouver la correspondance avec l’objet physique désiré. Ceci est bien sûr très utile dans les environnements ouverts, et offre une grande souplesse aussi bien lors du développement, que lors de la mise en production ou dans la phase de maintenance des applicatifs. La conséquence est que l’on pourra déplacer physiquement des données, par exemple les changer de serveur, renommer ou retailler un fichier sans pour autant retoucher le code des applications. Il n’y a (normalement) pas de correspondance bijective entre un fichier et une table.
  • existence de contraintes d’intégrité
    – intégrité de domaines (controle du type de la donnée)
    – intégrité de relation (existence d’une clé primaire : unique et toujours définie (non nulle))
    – intégrité de référence (controle de la cohérence d’attributs de tables différentes lors des mises à jour)

Zoom sur le langage SQL

C’est le langage qui permet d’accéder en consultation et en mise à jour aux données d’une base relationnelle (MySQL ou non).
Les différentes évolution de la norme SQL sont visibles sur le site de mimerSQL : http://developer.mimer.com/validator/features/index.tml

Il existe différents SQL (eh oui, il est normalisé mais chaque fournisseur prend malheureusement un certain nombre de libertés…)

Un comparateur de SQL (crash-me) est disponible sur le site MySQL : http://dev.mysql.com/tech-resources/crash-me.php

SQL utilise des opérateurs simples comme :
* les opérateurs de projection (choix de colonnes),
* les opérateurs de restrictions ou de selection (choix de lignes),
* les opérateurs de jointures (mise en relations de 2 ou plusieurs tables,
* les opérateurs ensemblistes (Union, intersection, différence).

SQL est un langage non procédural et n’est pas un langage de programmation.
En l’occurence il ne permet pas de :
* gérer des variables d’entrée / sortie (pas de création, typage, affectation, lecture, etc)
* gérer l’interface homme/machine,
* faire des itérations ou des boucles répétitives de type LOOP, WHILE, FOR…,
* gérer des erreurs, ou des événements particuliers,
* créer des sous-programme (procédure, fonctions, etc.).

C’est un langage proche du langage naturel (mais en Anglais 😉 ) qui possède un vocabulaire assez réduit.

Il faut distinguer 2 types d’ordres SQL :
– ceux qui portent sur les données elles-mêmes : Langage de manipulation des données ou LMD
– ceux qui portent sur le contenant ou les structures de données : Langage de définition de données ou LDD

et plus précisément pour la consultation :
SELECT
pour la création ou la modification d’objets (table, index, procedure, vues, etc.)
CREATE, DROP, ALTER
pour les mises à jour :
UPDATE, INSERT, DELETE
pour la gestion des droits :
GRANT, REVOKE

note: les ordres de mise à jour, sont en général directement validés (AUTO COMMITés) et l’on ne peut dans ce cas pas reenir en arrière et annuler la transaction

libre ?

MySQL est en fait disponible en version libre (open-source et gratuite) et en version commerciale (MySQL enterprise) .
Il est passé en licence GPL à partir de la version 3.23.19 (juin 2000)

 Téléchargement :

La version 5.1 de MySQL Server pour Windows est disponible ici :
http://dev.mysql.com/downloads/mysql/5.1.html

Un outil de requête et d’administration ‘MySQL Gui Tool’ est disponible ici :
http://dev.mysql.com/downloads/gui-tools/5.0.html

Les différentes versions de MySQL

Les versions

Il existe principalement 2 versions de MySQL

MySQL Enterprise est la version commerciale. C’est une solution complète comprenant:

  • un serveur MySQL enterprise
  • enterprise Monitor , un assistant d’administration graphique (virtual DBA)
  • du support technique

MySQL Community Edition est (apparemment, car ce n’est par clair sur leur site) la version gratuite.
Cette dernière est la plus connue et la plus utilisée, puiqu’elle est gratuite et équipe la quasi totalité des serveurs des fournisseurs d’accès Internet.

No de version :
Actuellement (novembre 2007) en Version 5.1 .
La version 6.0 n’est pas encore en production, actuellement version beta . Elle utilise un nouveau moteur de stockage baptisé ‘Falcon’
En 2003, MySQL AB et SAP ont conclu un accord. MaxDB est issu de l’intégration du système de SAP dans MySQL.

Les différentes plateformes et APIs

MySQL fonctionne sur un très grand nombre de plateformes et notamment sur : AIX, FreeBSD, HP-UX, Linux, Mac OS X, NetWare, Solaris, SunOS, SCO, Tru64 Unix, Windows 2000, XP et Vista.
Il est disponible sous forme de source compilable, ou de binaire.

On peut utiliser différents langages de programmation pour accéder à MySQL.
Chacun utilise une API spécifique :C, C++, C#, Delphi, Eiffel, Java, Perl, PHP, Python, Ruby et Tcl
PHP est de loin le plus utilisé.

Les différents moteurs

Pour corser le tout, MySQl utilise différentes versions de moteurs. Tous ne sont pas disponibles avec la version gratuite.

  • MyISAM : moteur par défaut de MySQL. Il est le plus simple à utiliser et mettre en œuvre. Il ne supporte pas un certain nombre de fonctionnalités ‘SGBDR’
  • InnoDB : d’ InnoBase
    Il gère les les transactions (y compris les transactions réparties avec le commit à 2 phases), les verrous au niveau ligne, les clés étrangères, les triggers, les vues,…

Pour mémoire il existe aussi :

  • MEMORY (HEAP) : les tables sont stockées uniquement en mémoire.
  • CSV : les tables sont stockées sous forme de fichiers textes (au format CSV)
  • BLACKHOLE : moteur de transfert ou de filtre de données.
  • NDB : gère des BDs en cluster (uniquement dans la version MaxDB)
  • MERGE : moteur fait pour fusionner plusieurs tables qui doivent être identiques.
  • ARCHIVE : moteur adapté pour l’archivage de données compressées.

On peut vérifier les moteurs disponibles pour la version de Mysql utilisée avec la commande

mysql> SHOW ENGINES

note : Il est non seulement possible d’utiliser un autre moteur que celui par défaut de la version, mais les applications pour MySQL peuvent utiliser plusieurs moteur pour la même base de données, en fonction des besoins. Le moteur devra être précisé explicitement dans le code SQL, notamment lors de la création de la table

Ainsi la version 5.027 de MySQL incluse dans EasyPHP v2, la commande SHOW ENGINES montre :

Engine         Support
------        -------
MyISAM         DEFAULT
MEMORY         YES
InnoDB         YES
BerkeleyDB     NO
BLACKHOLE      NO
EXAMPLE        NO
ARCHIVE        YES
CSV            NO
ndbcluster     NO
FEDERATED      NO
MRG_MYISAM     YES
ISAM           NO

Démarrer avec MySQL

Documentation et tables de démo (example database)

Toute la documentation officielle MySQL (en différentes langues) peut se trouver sur :

http://dev.mysql.com/doc/

On trouvera également sur ce lien les différentes bases de données d’exemple (example database) qui permettent de faire des exercices sur des tables simple. On trouvera notamment les bases de données SAKILA, MENAGERIE et WORLD.

Les 2 tables d’exemples de la base ménégerie sont montrées ci-après, elles seront utiles pour certains exercices…

bd menagerie

Le fichier SQL qui permet de créer simplment ces tables peut être récupéré sur http://moncours.estsurinternet.com/coursweb/wp-content/uploads/dump_menagerie.txt

ou copié / collé à partir du SQL suivant :

— MySQL dump menagerie

DROP TABLE IF EXISTS `event`;
CREATE TABLE `event` (
`name` varchar(20) DEFAULT NULL,
`date` date DEFAULT NULL,
`type` varchar(15) DEFAULT NULL,
`remark` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `event` VALUES
(‘Fluffy’,’1995-05-15′,’litter’,’4 kittens, 3 female, 1 male’),
(‘Buffy’,’1993-06-23′,’litter’,’5 puppies, 2 female, 3 male’),
(‘Buffy’,’1994-06-19′,’litter’,’3 puppies, 3 female’),
(‘Chirpy’,’1999-03-21′,’vet’,’needed beak straightened’),
(‘Slim’,’1997-08-03′,’vet’,’broken rib’),
(‘Bowser’,’1991-10-12′,’kennel’,NULL),
(‘Fang’,’1991-10-12′,’kennel’,NULL),
(‘Fang’,’1998-08-28′,’birthday’,’Gave him a new chew toy’),
(‘Claws’,’1998-03-17′,’birthday’,’Gave him a new flea collar’),
(‘Whistler’,’1998-12-09′,’birthday’,’First birthday’);

DROP TABLE IF EXISTS `pet`;
CREATE TABLE `pet` (
`name` varchar(20) DEFAULT NULL,
`owner` varchar(20) DEFAULT NULL,
`species` varchar(20) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`birth` date DEFAULT NULL,
`death` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `pet` VALUES
(‘Fluffy’,’Harold’,’cat’,’f’,’1993-02-04′,NULL),
(‘Claws’,’Gwen’,’cat’,’m’,’1994-03-17′,NULL),
(‘Buffy’,’Harold’,’dog’,’f’,’1989-05-13′,NULL),
(‘Fang’,’Benny’,’dog’,’m’,’1990-08-27′,NULL),
(‘Bowser’,’Diane’,’dog’,’m’,’1979-08-31′,’1995-07-29′),
(‘Chirpy’,’Gwen’,’bird’,’f’,’1998-09-11′,NULL),
(‘Whistler’,’Gwen’,’bird’,NULL,’1997-12-09′,NULL),
(‘Slim’,’Benny’,’snake’,’m’,’1996-04-29′,NULL),
(‘Puffball’,’Diane’,’hamster’,’f’,’1999-03-30′,NULL);

note : il faudra d’abord se connecter au serveur MySQL, créer la base ‘menagerie’, puis la sélectionner avant de lancer ces commandes SQL. Tout ceci sera détaillé dans les paragraphes suivants…

Se connecter au serveur

Les clients MySQL requièrent généralement que vous spécifiez :
• la machine hôte que vous voulez utiliser (le serveur)
• votre nom d’utilisateur
• votre mot de passe
Le client mysql le plus simple, pour faire du SQL de manière interactive s’appelle …’mysql’. On peut l’appeler comme ceci :

$> mysql [-h nom_d_hote] [-u nom_d_utilisateur]
[-pvotre_mot_de_passe]

les formes alternatives des options -h, -u et -p sont –host=host_name, –user=user_name et -password=your_pass. Notez qu’il n’y a aucun espace entre l’option -p ou –password= et le mot de passe qui le suit.
Si vous utilisez les options -p et –password mais que vous ne spécifiez pas de mot de passe, le client vous le demandera interactivement. Le mot de passe ne sera alors pas affiché. C’est la méthode la plus sécurisée.

Mysql utilise des valeurs par défaut pour chacun des paramètres qui manquent en ligne de commande :
• Le nom d’hôte par défaut est localhost.
• Le nom d’utilisateur par défaut est votre nom d’utilisateur de système Unix.
• Aucun mot de passe n’est transmis si -p manque.
Par exemple, pour un utilisateur Unix ‘robert’, les commandes suivantes sont équivalentes :

$> mysql -h localhost -u robert
$> mysql -h localhost
$> mysql -u robert
$> mysql

Les autres clients MySQL se comportent de manière similaire.

Alternative pour fournir les valeurs de connexion

Vous pouvez spécifier différentes valeurs par défaut qui seront utilisées lorsque vous établirez la connexion, de manière ce que vous n’ayez pas à entrer ces informations en ligne de commande lorsque vous invoquez un programme client. Cela peut se
faire de plusieurs fa¸ons :
• Vous pouvez spécifier les informations de connexion dans la section [client] du fichier de configuration .my.cnf de
votre dossier personnel. La section qui vous intéresse ressemble à ceci :

[client]
host=nom_d_hote
user=nom_d’utilisateur
password=votre_mot_de_passe

Vous pouvez spécifier les paramètres de connexion avec les variables d’environnement. L’hôte peut être spécifié à mysql avec la variable MYSQL_HOST. L’utilisateur MySQL peut être spécifié avec la variable USER (uniquement pour Windows). Le mot
de passe peut être spécifié avec MYSQL_PWD : mais ceci est peu sécurisé.

Entrer une commande SQL interactivement

Quelques règles pour executer des commandes :

  • Une commande consiste normalement en une commande SQL valide suivie d’un point-virgule.Les mots-clef peuvent être entrés sous n’importe quelle forme de casse.
  • Vous pouvez entrer plusieurs requêtes sur une seule ligne. Il suffit de terminer chacune d’elle par un ‘;’
  • Une commande peut être saisie sur plusieurs lignes

Les requêtes suivantes sont équivalentes…
mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;

on peut aussi ne pas spécifier de table et faire des calculs en SQL…

mysql> SELECT SIN(PI()/4), (4+1)*5;
————————–
SIN(PI()/4) | (4+1)*5
0.707107 – 25 –

Utiliser des scripts SQL

Il est possible de lancer une commande ou un ensemble de commandes SQL contenues dans un fichier externe, avec la commande source

mysql> source nom_du_script

On peut également utiliser des batchs MySQL, c’est à dire lancer des scripts à partir du système d’exploitation, enutilisant la redirection d’entrée ‘<‘

$> mysql prametre_de_connexion < nom_du_fichier_batch

Utiliser une base de données

Une base de données au sens MySQL est un ensemble de tables liées entre elles fonctionnellement : les tables de l’application ‘ressources humaines’ ou les tables de l’application’annuaire’ par exemple.

Utilisez la commande SHOW pour trouver quelles bases existent déjà sur le serveur :

mysql> SHOW DATABASES;
Database
———-
mysql
test
tmp

Note : Vous ne pourrez voir toutes les bases de données si vous n’avez pas le privilège SHOW DATABASES.

Pour accéder à base de données test (si elle existe) :

mysql> USE test
Database changed

Note: USE, ne requiert pas de point-virgule.

Il est possible d’accéder aux données d’une autre base que la base courante, en préfixant le nom de la table par le nom la base cible

mysql> use base1
/* une table locale */
mysql> select * from toto;
/* une table d’une autre bas, dans la même session */
mysql> select * from base2.tata;

note : des bases et des tables de démo sont disponibles sur le site de MySQL, et donc utilisables pour des tests, par exemple :
la ‘menagerie’ : http://downloads.mysql.com/docs/menagerie-db.zip

Créer et sélectionner une base de données

Si l’administrateur vous a créé une base de données lors du paramétrage de vos droits, vous pouvez commencer à l’utiliser. Sinon, vous aurez besoin de la créer par vous-même

mysql> CREATE DATABASE menagerie;

note : sous Unix les noms de base et de table sont sensibles à la casse.L
La création d’une base de données ne la sélectionne pas pour l’utilisation ; vous devez le faire explicitement.

Vous pouvez sélectionner la base directement dans la ligne de commande lorsque vous invoquez mysql. Par exemple :

$> mysql -h hote -u utilisateur -p menagerie

Les types de données

Il existe essentiellement 3 catégories de types de données en MySQL :

  • les types numériques
    Tous les types numériques de la norme SQL92 (NUMERIC, DECIMAL, INTEGER, et SMALLINT, FLOAT, REAL, et DOUBLE PRECISION)…et aussi un certain nombre d’autres !
  • les types temporels
    Les types DATETIME, DATE, TIMESTAMP, TIME et YEAR
  • les types chaînes (texte, binaire, courtes ou longues et les ensembles)
    CHAR et VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, SET

La référence officielle (en Français) se trouve sur http://dev.mysql.com/doc/refman/5.0/fr/column-types.html

Les types numériques

  • TINYINT [M] [UNSIGNED]
    Sur 1 octet. Ce type peut stocker des nombres entiers de -128 à 127 si il ne porte pas l’attribut UNSIGNED, dans le cas contraire il peut stocker des entiers de 0 à 255.
  • SMALLINT [M] [UNSIGNED]
    Sur 2 octets. Ce type de données peut stocker des nombres entiers de -32 768 à 32 767 si il ne porte pas l’attribut UNSIGNED, dans le cas contraire il peut stocker des entiers de 0 à 65 535.
  • MEDIUMINT [M] [UNSIGNED]
    Sur 3 octets. Ce type de données peut stocker des nombres entiers de -8 388 608 à 8 388 607 si il ne porte porte pas l’attribut UNSIGNED, dans le cas contraire il peut stocker des entiers de 0 à 16 777 215.
  • INT [M] [UNSIGNED]
    Sur 4 octets. Ce type de données peut stocker des nombres entiers de -2 147 483 648 à 2 147 483 647 si il ne porte pas l’attribut UNSIGNED, dans le cas contraire il peut stocker des entiers de 0 à 4 294 967 295.
  • BIGINT [M] [UNSIGNED]
    Sur 8 octets. Ce type de données stocke les nombres entiers allant de -9 223 372 036 854 775 808 à 9 223 372 036 854 775 807 sans l’attribut UNSIGNED, et de 0 à 18 446 744 073 709 551 615 avec.
  • FLOAT (précision simple de 0 à 24 et précision double de 25 à 53) [UNSIGNED]
    Sur 4 octets si la précision est inférieure à 24 ou 8 au delà.
    Stocke un nombre de type flottant.
  • FLOAT[(M,D)] [UNSIGNED]
    Sur 4 octets. M est le nombre de chiffres et D est le nombre de décimales.
    Ce type de données permet de stocker des nombres flottants à précision simple. Va de -1.175494351E-38 à 3.402823466E+38. Si UNSIGNED est activé, les nombres négatifs sont retirés mais ne permettent pas d’avoir des nombres positifs plus grands.
  • DOUBLE [(M,D)]
    Sur 8 octets. Stocke des nombres flottants à double précision de -1.7976931348623157E+308 à -2.2250738585072014E-308, 0, et de 2.2250738585072014E-308 à 1.7976931348623157E+308.
    Si UNSIGNED est activé, les nombres négatifs sont retirés mais ne permettent pas d’avoir des nombres positifs plus grands.
  • REAL[(M,D)]
    Sur 8 octets. Même chose que le type DOUBLE
  • DECIMAL[(M[,D])]
    Sur M+2 octets si D > 0, M+1 octets si D = 0
    Contient des nombres flottants stockés comme des chaînes de caractères.

note : BIT, BOOL et BOOLEAN son t des synonymes de TiNYINT.INTEGER est un synonyme de INT, NUMERIC de DECIMAL, DOUBLE PRECISON de DOUBLE

Les types temporels

  • DATE
    Sur 3 octets. Stocke une date au format ‘AAAA-MM-JJ’ allant de ‘1000-01-01’ à ‘9999-12-31’
  • DATETIME
    Sur 8 octets. Stocke une date et une heure au format ‘AAAA-MM-JJ HH:MM:SS’ allant de ‘1000-01-01 00:00:00’ à ‘9999-12-31 23:59:59’
  • TIMESTAMP [M]
    Sur 4 octets. Stocke une date sous forme numérique allant de ‘1970-01-01 00:00:00’ à l’année 2037. L’affichage dépend des valeurs de M : AAAAMMJJHHMMSS, AAMMJJHHMMSS, AAAAMMJJ, ou AAMMJJ pour M égal respectivement à 14, 12, 8, et 6
  • TIME
    Sur 3 octets. Stocke l’heure au format ‘HH:MM:SS’, allant de ‘-838:59:59’ à ‘838:59:59’
  • YEAR
    Sur 1 octet. Année à 2 ou 4 chiffres allant de 1901 à 2155 ( 4 chiffres) et de 1970-2069 (2 chiffres).

Les types caractères

  • [NATIONAL] CHAR(M) [BINARY]
    Sur M octets, M allant jusqu’à 255
    Chaîne de 255 caractères maximum remplie d’espaces à la fin. L’option BINARY est utilisée pour tenir compte de la casse.
  • CHAR (L
    Sur L octets. Stocke des caractères. Si vous stockez un caractère et que M vaut 255, la donnée prendra 255 octets. Autant donc employer ce ce type de données pour des mots de longueur identique.
  • VARCHAR (L) [BINARY]
    Sur L+1 octets (ou L représente la longueur de la chaîne).
    Ce type de données stocke des chaînes de 255 caractères maximum. L’option BINARY permet de tenir compte de la casse.
  • TINYBLOB
    Sur L+1 octets (L représente la longueur de la chaîne)
    Stocke des chaînes de 255 caractères maximum. Ce champ est sensible à la casse.
  • TINYTEXT
    Sur L+1 octets.
    Stocke des chaînes de 255 caractères maximum. Ce champ est insensible à la casse.
  • BLOB
    Sur L+1 octets.
    Stocke des Chaînes de 65535 caractères maximum. Ce champ est sensible à la casse.
  • TEXT
    Sur L+2 octets.
    Stocke des chaînes de 65535 caractères maximum. Ce champ est insensible à la casse.
  • MEDIUMBLOB
    Sur L+3 octets.
    Stocke des chaînes de 16777215 caractères maximum.
  • MEDIUMTEXT
    Sur L+3 octets.
    Chaîne de 16 777 215 caractères maximum. Ce champ est insensible à la casse.
  • LONGBLOB
    Sur L+4 octets.
    Stocke des chaînes de 4 294 967 295 caractères maximum. Ce champ est sensible à la casse.
  • LONGTEXT
    Sur L+4 octets.
    Stocke des chaînes de 4 294 967 295 caractères maximum.
  • ENUM(‘valeur_possible1′,’valeur_possible2′,’valeur_possible3’,…)
    Sur 1 ou 2 octets (la place occupée est fonction du nombre de solutions possibles : 65 535 valeurs maximum.
  • SET(‘valeur_possible1′,’valeur_possible2’,…)
    Sur 1, 2, 3, 4 ou 8 octets, selon de nombre de solutions possibles (de 0 à 64 valeurs maximum)

Créer et utiliser des tables

Hormis le cas ou l’on accède aux tables de démo ou à une application préexistante, il est nécessaire de créer des données avant de pouvoir les consulter ou les modifier !
Ceci sera fait principalement avec les instructions SQL ‘CREATE TABLE’ et ‘INSERT’.

la commande CREATE TABLE

Utilisez une requête CREATE TABLE pour spécifier la structure de votre table.
On spécifie la liste des colonnes séparées par des virgules, et on précise à chaque fois le nom de la colonne, son type et si nécessaire sa longueur.
Supposons que l’on veuille créer la table ‘animal’ copie conforme de la table ‘pet’ de notre base ‘menagerie’ mais en français..

mysql> CREATE TABLE animal (nom VARCHAR(20), maitre VARCHAR(20),
espece VARCHAR(20), sexe CHAR(1), naissance DATE, mort DATE);

une fois la table créée, on peut vérifier sa structure avec la commande DESCRIBE ou son raccourci DESC

mysql> desc animal;+- - - - - - - - - - - - - - - - - - - - - - - - - - - - +
| Field     | Type        | Null | Key | Default | Extra |
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - -+
| nom       | varchar(20) | YES  |     | NULL    |       |
| maitre    | varchar(20) | YES  |     | NULL    |       |
| espece    | varchar(20) | YES  |     | NULL    |       |
| sexe      | char(1)     | YES  |     | NULL    |       |
| naissance | date        | YES  |     | NULL    |       |
| mort      | date        | YES  |     | NULL    |       |
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - -+
6 rows in set (0.01 sec)

et si l’on ne se rappelle plus du nom des tables créées pour faire le DESCRIBE, on peut toujours lister les noms de tables de la base courante, avec ‘SHOW TABLES’

le remplissage des tables

l’ajout de données dans une table se fait généralement avec la commande INSERT.
On précise le nom de la table, la liste des colonnes qu’on veut remplir, et la liste des valeurs après le mot réservé ‘VALUES’.
rem : si on ne précise aucune colonne, il faudra fournir les valeurs de toutes les colonnes

mysql> INSERT INTO animal VALUES (‘Puffball’,’Diane’,’hamster’,’f’,’1999-03-30′,NULL);
mysql> INSERT INTO animal (nom , maitre) VALUES (‘Snow’, ‘DD’);

On peut créer une table et la remplir en une seule commande , eb recopiant la structure et les données d’une table existante, avec la commande ‘CREATE TABLE…AS SELECT …’
Pour créer une copie de la table PET en PET2 :;

mysql> CREATE TABLE pet2 AS SELECT * FROM pet ;

note : on peut aussi créer des données :

  • en important des données d’un autre base sous forme de script SQL,
  • en utilisant le moteur CSV de MySQL.
    Dans ce dernier cas, un fichier externe CSV sera directement et instantanément considéré comme une table MySQL!
  • en chargeant un fichier texte avec la commande LOAD DATA…INFILE

la suppression de tables

…existantes se fait avec la commande ‘DROP TABLE’

mysql> drop table animal;

SELECTs simples

Syntaxe générale simplifiée

SELECT . . .
[DISTINCT | DISTINCTROW | ALL | *]
select_expression, . . .
[INTO {OUTFILE | DUMPFILE} ‘nom_fichier’ export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | nom_de_colonne | formula} [ASC | DESC], . . .
[HAVING where_definition]
[ORDER BY {unsigned_integer | nom_de_colonne | formula} [ASC | DESC] ,. . . ]
[LIMIT [offset,] lignes]

ramène les valeurs des colonnes spécifiées de la (les) table(s) spécifiée(s), pour les lignes satisfaisant la condition de la clause WHERE

* ou ALL : toutes les colonnes de la table
GROUP BY : regroupement suivant les différentes valeurs de la colonne ou de l’expression spécifiée (HAVING précise une condition supplémentaire sur le groupe)
UNION : union ensembliste de deux SELECT
ORDER BY : tri suivant une colonne ou une expression ascendant (par défaut) ou descendant
INTO : renvoie les sorties  du SELECT  dans  un fichier
LIMIT : restreint le résultat au nb de lignes spécifié

SELECT * FROM animal;
SELECT nom FROM animal;
SELECT nom FROM animal WHERE maitre=’DD’ ORDERBY nom;

note : on peut écrire un SELECT extrêmement simple…sans table. Cela sert à faire des calculs temporaires d’expression :

SELECT 1+1; renvoie 2
SELECT 1+1 FROM dual;
fait la même chose et est compatible avec la norme SQL

Sélectionner des lignes particulières

Seules les lignes vérifiant la condition de la clause WHERE (l’expression est VRAIe dans ce cas) seront restituées


mysql> SELECT * FROM animal WHERE nom = "bowser";
+--------+--------+--------+------+------------+------------+
| nom | maitre | espece | sexe | naissance | mort |
+--------+--------+--------+------+------------+------------+
| Bowser | Diane | chien | m | 1989-08-31 | 1995-07-29 |
+--------+--------+--------+------+------------+------------+

note : La comparaison des chaînes de caractères en MySQL n’est pas sensible à la casse,
vous pouvez donc spécifier le nom « bowser », « BOWSER », etc, le résultat sera le même.
Si vous voulez faire un test exact et RENDRE MYSQL SENSIBLE A LA CASSE il faudra utiliser l’opérateur ‘COLLATE’

SELECT * FROM animal WHERE nom = « bowser » COLLATE latin1_general_cs
mysql>
mysql> SELECT * FROM animal WHERE nom = « bowser » COLLATE latin1_general_cs ;
Empty set (0.00 sec)
sql> SELECT * FROM animal WHERE nom = « Bowser » COLLATE latin1_general_cs ;

+--------+--------+--------+------+------------+------------+
| nom | maitre | espece | sexe | naissance | mort |
+--------+--------+--------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
+--------+--------+--------+------+------------+------------+
1 row in set (0.00 sec)

Pour rendre MySQL sensible à la casse de manière permanente il faudra utiliser ‘COLLATE’ au niveau du ‘CREATE TABLE’

On peut aussi mettre des conditions sur des dates ou sur n’importe quelle colonne bien sûr..
Pour avoir tous les animaux nés après 1998, :

mysql> SELECT * FROM animal WHERE naissance >= « 1998-1-1 »;

+----------+--------+---------+------+------------+-------+
| nom | maitre | espece | sexe | naissance | mort |
+----------+--------+---------+------+------------+-------+
| Chirpy | Gwen | oiseau | f |1998-09-11 | NULL |
| Puffball | Diane | hamster | f |1999-03-30 | NULL |
+----------+--------+---------+------+------------+-------+

Vous pouvez combiner plusieurs conditions, par exemple, pour trouver les chiennes :

mysql> SELECT * FROM animal WHERE espece = ‘ ‘chien’ ‘ AND sexe = ‘ ‘ f ‘ ‘ ;

+-------+--------+--------+------+------------+-------+
| nom | maitre | espece | sexe | naissance | mort |
+-------+--------+--------+------+------------+-------+
| Buffy | Harold | chien | f | 1989-05-13 | NULL |
+-------+--------+--------+------+------------+-------+

La requête précédente utilise l’opérateur logique AND. L’opérateur OR existe aussi :

mysql> SELECT * FROM animal WHERE espece = ‘ ‘ serpent’ ‘ OR espece = ‘ ‘ oiseau’ ‘ ;

+----------+--------+---------+------+------------+-------+
| nom | maitre | espece | sexe | naissance | mort |
+----------+--------+---------+------+------------+-------+
| Chirpy | Gwen | oiseau | f | 1998-09-11 | NULL |
| Whistler | Gwen | oiseau | NULL | 1997-12-09 | NULL |
| Slim | Benny | serpent | m | 1996-04-29 | NULL |
+----------+--------+---------+------+------------+-------+

AND et OR peuvent être utilisés ensemble, utilisez les parenthèses pour indiquer comment les conditions doivent être regroupées, et donc les priorités :

mysql> SELECT * FROM animal WHERE (espece =  » chat » AND sexe =  » m » )
-> OR (espece =  » chien » AND sexe =  » f » );

+-------+--------+--------+------+------------+-------+
| nom | maitre | espece | sexe | naissance | mort |
+-------+--------+--------+------+------------+-------+
| Claws | Gwen | chat | m | 1994-03-17 | NULL |
| Buffy | Harold | chien | f | 1989-05-13 | NULL |
+-------+--------+--------+------+------------+-------+

le cas des valeurs NULL

Une valeur NULL est une valeur non définie dans une table, car non connue ou par encore renseignée. Un exemple est la date de décès dans notre table animal.
Une valeur NULL n’est donc PAS NULLE !!!!

Ainsi l’expression NULL = 0 est toujours fausse !
et encore plus fort TOUTE expression qui contient une valeur NULL (indéfinie donc) est NULL !!

C’est pour cela qu’on utilise deux opérateurs spéciaux pour tester les valeurs indéfnies : ‘IS NULL’ et ‘IS NOT NULL’

mysql> SELECT nom FROM animal WHERE mort=NULL;
Empty set (0.00 sec)

mysql> SELECT nom FROM animal WHERE mort IS NULL;

+----------+
| nom |
+----------+
| Fluffy |
| Claws |
| Buffy |
| Fang |
| Chirpy |
| Whistler |
| Slim |
| Puffball |
+----------+

8 rows in set (0.00 sec)

Ordres SQL autres que SELECT

Vous trouverez dans le tableau ci-après la syntaxe générale des principaux ordres SQL (du LMD et du LDD) autres que SELECT.
Ce tableau est loin d’être exhaustif.

La totalité des ordres SQL de MySQL est décrite dans le manuel de référence officiel sur: http://dev.mysql.com/doc/refman/5.0/fr/sql-syntax.html

ordre SQL Syntaxe
   
ALTER TABLE (add column)
ajoute une colonne à une table
ALTER TABLE table_name
ADD column_name datatype
ALTER TABLE (drop column)
supprime une colonne d’une table
ALTER TABLE table_name
DROP COLUMN column_name
CREATE DATABASE
crée une base
CREATE DATABASE database_name
CREATE INDEX
crée un index
CREATE INDEX index_name
ON table_name (column_name)
CREATE TABLE
crée un table
CREATE TABLE table_name
(
column_name1 data_type, column_name2 data_type,…….
)
CREATE UNIQUE INDEX
crée un index unique
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
CREATE VIEW
crée une vue partielle ou non d’une table
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
[ WHERE condition ]
DELETE FROM
supprime une ou plusieurs lignes
DELETE FROM table_name
[ WHERE condition ]
DROP DATABASE
supprime une base de données
DROP DATABASE database_name
DROP INDEX
supprime un index
DROP INDEX table_name.index_name
DROP TABLE
supprime table et contenu
DROP TABLE table_name
RENAME TABLE
renomme une table
RENAME TABLE table_name TO new_table_name
INSERT INTO
insère des nelles valeurs
INSERT INTO table_name
[ (column_name1, column_name2,…) ]
VALUES (value1, value2,….)
TRUNCATE TABLE
vide la table
TRUNCATE TABLE table_name
UPDATE
modifie une ou des lignes existantes
UPDATE table_name
SET column_name=new_value [, column_name=new_value]
[ WHERE column_name=some_value ]

Voici quelques exemples d’utilisation de ces commandes :

/* changer la base pour menagerie */
use menagerie;
/* créer une table qui recopie 2 colonnes de animal */
create table temp as select nom, race from animal;
/* insérer une ligne avec une seule colonne */
insert into temp (nom) values (« Shep »);
/* mettre à jour d’une seule ligne */
update temp set espece = « dog » where nom = « shep »;
/* mettre à jour plusieurs lignes pour traduire l’espece,
seulement 4 lignes sont modifiées*/
update temp set espece = « chien » where espece = « dog »;
/* supprimer les lignes qui ne sont pas des chiens */
delete from chien where espece <> « chien »;
/* renommer la table en chien */
rename table temp to chien;
/* ajout d’un index sur le nom pour accélérer les recherches (sur le nom !) */
create index idx_nom_chien on chien(nom);

Les sous requêtes

Depuis la version 4.1, MySQL supporte toutes les formes de sous-requêtes, y compris les sous-requêtes corrélées (correlated subqueries).

Une sous-requête est une requête SELECT qui apparait a l’intérieur d’une autre requête.
Les principales syntaxes possibles sont les suivantes :

SELECT | DELETE . . . FROM table1
WHERE col operateur (SELECT . . . FROM table2);
UPDATE table SET . . .
WHERE col operateur (SELECT . . . FROM . . .
SELECT . . . FROM
(SELECT . . . FROM table1) AS nom

On parle de requête externe, pour le premier membre et de sous requête, ou de requête imbriquée ou de requête interne pour la suite. Une sous-requête doit être entre parenthèses.

Les avantages :

  • plus ‘naturelles’ et plus lisibles que les jointures
  • plus structurées

En pratique, toute sous requête peut être écrite de manière équivalente avec une jointure. Dans les premières versions de MySQL seules les jointures étaient utilisable pour répondre à des questions complexes…

Les opérateurs spécifiques ANY, ALL, IN, SOME

Dans la mesure ou on ne sait pas en général combien de lignes va ramener la requête interne, l’opérateur ‘=’ sera rarement utilisé à moins d’être sûr que dans tous les cas cette dernière ramène une seule ligne…

Dans le cas général on utilise des opérateurs spécifiques, dont le deuxième opérande peut être une liste de valeur. Le cas le + intuitif est l’opérateur ‘IN’.

l’opérateur IN

Renvoie les lignes qui vérifie qu’une colonne appartient à une liste de valeurs (renvoyée par un sous SELECT)

Ainsi on pourra écrire, une requête du genre :

SELECT * FROM peinture
WHERE couleur IN (SELECT distinct coloris from palette)

Ce SELECT renverra uniquement les lignes de PEINTURE dont la couleur appartient à la liste des différents coloris…

l’opérateur ANY

Renvoie des lignes si la comparaison est vraie pour au moins une des lignes retournées par le sous SELECT
ANY suit obligatoirement un opérateur de comparaison (=, <, >, <=,  . . .)

SELECT s1 FROM t1 WHERE s1 opérateur ANY (SELECT s1 FROM t2);

Ainsi si nous voulons la liste des pays qui d’Amrique du Nord dont la population est plus importante que n’importe quel (au moins un) pays d’Europe , on écrit assez naturellement :

SELECT name, population from country
where continent = ‘North America’
and population > any (
select population from country
where continent = ‘Europe’ );

Pour mieux comprendre de ‘> any’ on pourrait dire que si un pays d’Amérique a une population supérieure à au moins un des pays d’Europe, elle est supérieure à celle du moins peuplé d’Europe ce qui en SQL s’écrit:

SELECT name, population from country
where continent = ‘North America’
and population >
(select min(population) from country
where continent = ‘Europe’ );

remarque :’ IN’ est un alias de ‘=ANY’ et ‘SOME’ un alias de ‘ANY’

Pour plus d’infos voir le manuel MySQL sur http://dev.mysql.com/doc/refman/5.0/fr/subqueries.html

Sous requêtes EXISTS

Une sous requête EXISTS est vraie si le résultat existe ou en d’autres termes si elle retourne au moins une ligne.

La syntaxe est la suivante :

SELECT * FROM t1
WHERE EXISTS
(SELECT * FROM t2)

Voir exemples avec sous requêtes corrélées…

Sous requêtes multi colonnes

Si une sous requête ne retourne qu’une seule ligne, il est possible de faire des comparaisons sur plusieurs colonnes en même temp, avec un nombre correspondant de colonnes dans le sous SELECT.
Ceci permet d’écrire de manière plus compacte un ‘ET logique’

SELECT * FROM t1
WHERE (c1,c2,c3, . . . ) = (SELECT c1,c2,c3, . . . FROM t2)

Sous requêtes corrélées (correlated Subqueries)

Une sous requête corrélée est une requête interne qui référence une table de la requête externe.
En d’autres termes, les données du sous SELECT sont corrélées avec les données du SELECT principal.

La syntaxe est la suivante :

SELECT * FROM t1
WHERE c1 operateur (SELECT … FROM t2 WHERE t1.c1 = t2.c2)

la table t1 est externe et t2 interne ici. Dans le sous SELECT apparaissent bien t1 et t2…

Reprenons par exemple la table ‘pet’ de la base ‘animal’. Elle contient notamment les noms des animaux et de leur maitre. Si l’on cherche les animaux dont le nom a le même nombre de lettres que celui d’un maître, on écrira assez naturellement (bien que l’exemple soit un peu tiré par les cheveux (ou les poils) j’en conviens) une sous requête du genre :

select name, length(name) from pet
where length(name) in
(select length(owner) from pet);
+——–+————–+
| name | length(name) |
+——–+————–+
| Fluffy | 6 |
| Claws | 5 |
| Buffy | 5 |
| Fang | 4 |
| Bowser | 6 |
| Chirpy | 6 |
| Slim | 4 |
+——–+————–+
7 rows in set (0.00 sec)

et l’on trouvera 7 animaux dont le nom fait 4,5,6 ou 7 lettres.

Mais si l’on souhaite ‘corréler’ l’animal à SON maître, et savoir cette fois quel animal a le même nombre de lettres dans son nom que SON maître (et non pas qu’un propriétaire quelconque) on pourrait écrire une sous requête corrélée, et dans la sous requête vérifier EN PLUS que le propriétaire est bien le même que celui considéré dans la requête principale. On écrira :

select name, length(name) from pet p1
where length(name) in
(select length(owner) from pet p2
where p2.owner = p1.owner);

et l’on obtiendra plus que ‘Fluffy’ qui a 6 lettres comme son propriétaire ‘Harold’…

Les jointures

Les jointures sont un moyen de consulter les informations provenant de plusieurs tables en même temps. Pour spécifier une jointure dans un SELECT il suffit de préciser plusieurs tables dans la clause FROM.

SELECT liste_colonnes FROM table1, table2
/* fait une jointure entre table 1 et table2 */

Jointure brute

Une jointure brute fait un produit cartésien entre les données de T1 et T2, c’est à dire associe toutes les colonnes de chaque ligne de T1, à toutes les colonnes de chaque ligne de T2, en effectuant toutes les combinaisons possibles.
Ainsi une jointure brute sur la table ‘PET’ et la table ‘EVENT’ renvoie 90 lignes de 10 colonnes (6 colonnes de la table PET, accolées à 4 colonnes de la table EVENT)

mysql> SELECT COUNT(*) FROM pet;
mysql> 9
mysql> SELECT COUNT(*) FROM event;
mysql> 10
mysql> SELECT COUNT(*) FROM pet, event;
mysql> 90
mysql> select * from pet,event limit 2;
+———-+——–+———+——+————+————+———-+———–+———-+—————————–+
| name | owner | species | sex | birth | death | name | date
| type | remark |
+———-+——–+———+——+————+————+———-+——–+———-+—————————–+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL | Fluffy | 1995
-05-15 | litter | 4 kittens, 3 female, 1 male |
| Claws | Gwen | cat | m | 1994-03-17 | NULL | Fluffy | 1995
-05-15 | litter | 4 kittens, 3 female, 1 male |

EquiJointure

Il est évident que les résultats ramenés précédemment ne sont pas très pertinents : des infos qui concernent l’animal ‘Claws’ dans ‘PET’ sont accolées avec des infos qui concernent ‘Fluffy!
Il est donc intéressant de filtrer ce résultat pour ne ramener que les lignes ayant un sens, c’est à dire les lignes où existe une correspondance, celles qui concerne le même animal.
Cette condition est facile à écrire, il suffit de rajouter une clause WHERE dans le SELECT en imposant que le nom d’animal dans ‘PET’ soit égal au nom d’animal dans ‘EVENT’.

Cette égalité nécessaire définit ce qu’on appelle une EQUI JOINTURE.
Cette ‘condition de jointure’, permet de relier les tables sur une collen commune : la clé de jointure. Sur notre table ‘pet’ c’est ‘name’ qui fait le lien et est la clé de jointure.

mysql> select p.name as « nom », birth as « né le », remark « evenement »
-> from pet p, event e
-> where p.name = e.name;
+———-+————+—————————–+
| nom | né le | evenement |
+———-+————+—————————–+
| Fluffy | 1993-02-04 | 4 kittens, 3 female, 1 male |
| Buffy | 1989-05-13 | 5 puppies, 2 female, 3 male |
| Buffy | 1989-05-13 | 3 puppies, 3 female |
| Chirpy | 1998-09-11 | needed beak straightened |
| Slim | 1996-04-29 | broken rib |
| Bowser | 1979-08-31 | NULL |
| Fang | 1990-08-27 | NULL |
| Fang | 1990-08-27 | Gave him a new chew toy |
| Claws | 1994-03-17 | Gave him a new flea collar |
| Whistler | 1997-12-09 | First birthday |
+———-+————+—————————–+

notes : – on évitera de faire des ‘SELECT *’ dans les jointures pour éviter les colonnes redondantes comme ‘name’ ici. On précisera explicitement les colonnes utiles à afficher.

– on verra dans le paragraphe suivant qu’il est possible de mettre la clause de jointure ailleurs que dans la clause WHERE…

Il est bien évidemment possible de rajouter des conditions à la condition de jointure, comme dans tout SELECT. Si l’on veut limiter la requête précédente aux chiens on rajoute la condtion ‘species = « dog »

mysql> select p.name as « nom », birth as « né le », remark « evenement »
-> from pet p, event e
-> where p.name = e.name
-> and species = ‘dog’;
+——–+————+—————————–+
| nom | né le | evenement |
+——–+————+—————————–+
| Buffy | 1989-05-13 | 5 puppies, 2 female, 3 male |
| Buffy | 1989-05-13 | 3 puppies, 3 female |
| Bowser | 1979-08-31 | NULL |
| Fang | 1990-08-27 | NULL |
| Fang | 1990-08-27 | Gave him a new chew toy |
+——–+————+—————————–+

On peut également utiliser les syntaxes INNER JOIN ou NATURAL JOIN pour ce type de jointure.

tests sur les limites ….

Pour faire nos tests nous allons utiliser les tables de démonstration de la DATABASE ‘World’, qui contient des tables sur les pays, villes et langues du monde.

Nous allons nous intéresser au nombre de langues parlées par pays, et + particulièrement aux tables COUNTRY (code, name,…) et COUNTRYLANGUAGE(countrycode, language,…)

Pour avoir des colonnes de jointures de meme nom, on modifie la table en SQL.

ALTER TABLE `country` CHANGE `Code` `countryCode` CHAR( 3 );

Reprenons notre jointure simple, avec condition de jointure dans le WHERE
La condition de jointure doit être forcément vérifiée. On joint sur le code de pays commun aux 2 tables, et on fait un ‘GROUP BY ‘ pays pour pouvoir compter les langues dans chaque groupe :

SELECT name Pays, count(language) « nb langues »
FROM country c, countrylanguage l
WHERE c.countrycode = l.countrycode
GROUP BY name

Pays langues
—– ——-
Afghanistan 5
Albania 3
Algeria 2
Am. Samoa 3
Andorra 4
Angola 9
Anguilla 1
Antigua 2
Argentina 3
Armenia 2
Aruba 4
Australia 8
Austria 8
Azerbaijan 4
Bahamas 2
Bahrain 2
Bangladesh 7
Barbados 2
Belarus 4
… …

 

Maintenant, on limite le resultat aux pays commencant par ‘A’, our y voir + clair dans les tests.
La condition supplémentaire est forcement ajoutée dans le WHERE

SELECT name Pays, count(language) « nb langues »
FROM country c, countrylanguage l
WHERE c.countrycode = l.countrycode
AND name LIKE ‘A%’
GROUP BY name

–> on en a désormais 14. L’Antartique n’apparait pas car la condition de jointure n’est pas satisfaite pour ce pays (son code n’apparait pas dans la tables des langues parlées…).
Nos tests peuvent commencer !

Les jointures externes

Il n’y a parfois pas de correspondance systématique entre les valeurs des colonnes de jointures.
Ainsi l’antartique qui n’a pas d’habitant, n’a pas de langue parlée.
Elle apparait dans la table des pays, mais pas dans celle des langages!
La jointure externe (OUTER JOIN) permet de résoudre ces problèmes en faisant correspondre des lignes qui n’ont pas de valeurs communes. Des lignes de valeurs NULL sont alors rajoutées pur compléter la table déficitaire. A droite dans un LEFT JOIN, à gauche dans un RIGHT JOIN.

Jointure externe, avec clause LEFT JOIN et ON

SELECT name Pays, count(language) « nb langues »
FROM country c LEFT JOIN countrylanguage l
ON c.countrycode = l.countrycode
WHERE name LIKE ‘A%’
GROUP BY name
–> on en a 15 !!! avec l’antartique
— elle ne verifie pas le critere de jointure mais sort qd meme
— avec la jointure externe, qui complete la table de droite ‘l’ avec des NULL

Jointure externe, avec clause LEFT JOIN et ON
en changeant l’ordre des tables !

SELECT name Pays, count(language) « nb langues »
FROM countrylanguage l LEFT JOIN country c
ON c.countrycode = l.countrycode
WHERE name LIKE ‘A%’
GROUP BY name
–> on en a de nouveau 14 !!! sans l’antartique
— c’est la table de gauche qui est deficitaire
— et c’est celle de droite qui est eventuellement complétée avec des NULL
— mais comme il n’y a pas de langues parlée sans pays…

Jointure externe, avec clause RIGHT JOIN et ON
Un RIGHT JOIN est strictement équivallent à un LEFT JOIN, en inversant l’ordre des tables. On utilise en général des LEFT JOIN !

SELECT name Pays, count(language) « nb langues »
FROM country c RIGHT JOIN countrylanguage l
ON c.countrycode = l.countrycode
WHERE name LIKE ‘A%’
GROUP BY name
–> on n’en a plus que 14 !!! l’antartique a (re)disparu
— c’est ‘c’ qui a été complétée avec des NULL
— ce qui ne sert pas a grand chose

Jointure externe avec USING.
La clause USING(c1,c2) <==> ON t1.c1 = t2.c1, mais nécessite des colonnes homonymes

SELECT name Pays, count(language) « nb langues »
FROM country c LEFT JOIN countrylanguage l
USING (countrycode)
WHERE name LIKE ‘A%’
GROUP BY name

Routines stockées

On peut utiliser du code stocké dans la base à partir de la version 5.0 de MySQL.
Le code peut être sous forme de procédure, fonction ou trigger (code déclenché sur événement). On parle de manière générale de ‘procédure stockées’ dans tous les cas, par abus de langage ou pour faire court…

Pourquoi des procédures stockées

  1. Ce sont des procédures ! c’est à dire que le code est réutilisable, par un simple appel de la procédure et personnalisable en changeant les paramètres d’entrée
  2. elles sont stockées ! c’est à dire que votre programme est inclus dans la base de données, comme une donnée ! On peut donc facilement exporter les données avec les programmes, les sauvegarder ensemble, etc.
  3. elles sont performantes : le code SQL est ‘précompilé’ dans la base, donc son exécution sera plus rapide à priori
  4. elles sont plus sures (pas tout à fait encore en MySQL 5) car on peut gérer des droits au niveau de la procédure? C’est à dire qu’on peut autoriser un utilisateur à executre seulement un code très précis qui accède à certaines données, sans lui donner d’accès à des tables coplètes, ou à fortiori à toute une base.

Fonctions, procédures et paramètres

Une routine stockée peut avoir de manière optionnelle des paramètres d’entrée et / ou de sortie.

Une fonction est simplement une procédure qui retourne une valeur (grace au mot clé ‘RETURN’ )

La clause RETURNS sert à indiquer le type de retour de la fonction, et le corps de la fonction doit contenir une instruction RETURN value
Une fonction peut être vue comme une procédure avec un seul paramètre de sortie obligatoire.

La liste de paramètre entre parenthèses est obligatoire. S’il n’y a pas de paramètre, une liste vide sous la forme () doit être utilisée. Chaque paramètre est un paramètre de type IN par défaut. Pour spécifier un autre type, utilisez les mots OUT ou INOUT avant le nom du paramètre. Spécifier IN, OUT ou INOUT n’est valable que pour une PROCEDURE.

Syntaxe de création des procédures et fonctions

CREATE PROCEDURE sp_name ([parameter[,...]])[characteristic ...]
routine_body
CREATE FUNCTION sp_name ([parameter[,...]])[RETURNS type]
[characteristic ...] routine_body
paramètre :
  [ IN | OUT | INOUT ] param_name type
type :
  Any valid MySQL data type
characteristic:
  | SQL SECURITY {DEFINER | INVOKER}
  | COMMENT string
routine_body :
  Commande(s) SQL valide(s)

Les différentes clauses du CREATE

L’attribut SQL SECURITY peut être utilisé pour spécifier si la routine doit être exécutée avec les droits de l’utilisateur qui l’a créé ou avec ceux de celui qui appelle la fonction. La valeur par défaut est DEFINER. Cette fonctionnalité est nouvelle en SQL:2003.
MySQL n’utilise pas le droit GRANT EXECUTE. Pour le moment, si une procédure p1() utilise la table t1, l’appelant doit avoir les droits sur la table t1 afin que la procédure p1() puisse réussir.

La clause COMMENT est une extension MySQL, et peut servir à décrire la procédure stockée.
MySQL permet aux routines de contenir des commandes DDL, telle que CREATE et DROP, et des transactions SQL, comme COMMIT. Ce n’est pas obligatoire selon le standard et c’est donc une extension spécifique.

Le corps de la routine contiendra une ou plusieurs instrctions SQL, séparées par des ‘;’.

ATTENTION !! : pour lever toute ambiguité entre le ‘;’ qui marque la fin du SQL et son exécution et le ‘;’ qui sépare des instructions SQL dans une routine, il sera nécessaire de changer le délimiteur SQL, avec la commande ‘delimiter’.

Informations sur les routines stockées

Il existe 2 instructions pour avoir des informations sur les routines : SHOW CREATE…et SHOW STATUS :

  • SHOW CREATE FUNCTION / PROCEDURE
    Renvoie le texte d’une fonction stockée précédemment définie, qui a été créée à l’aide de l’instruction CREATE FUNCTION.
  • SHOW FUNCTION / PROCEDURE STATUS
    Renvoie les caractéristiques d’une procédure stockée précédemment définie, notamment son nom, son type, son créateur, sa date de création et sa date de modification.

Les infos du référentiel sur les routines sont disponible dans la table INFORMATION_SCHEMA.ROUTINES et dans la table mysql.PROC

Un exemple simple de fonction stockée MySQL

Supposons que l’on veuille une fonction de concaténation de chaines. On a 1 paramètre d’entrée (la chaine à concaténer) et une variable chaine retournée qui est la concaténation de l’entrée et d’une chaine fixe (« bonjour »).
On pourra utiliser la fonction standard de concaténation mysql ‘concat()’ pour créer notre propre fonction.

Le code est le suivant :

delimiter |
CREATE FUNCTION concatene (ch1 CHAR(25))
— le type du retour…
RETURNS CHAR(50)
— ici il n’y a pas d’instructions autres…
— mais le traitement est fait au moment du retour
RETURN concat(« Bonjour « , ch1);

pour voir comment s’exécute notre fonction on pourra, faire un SELECT …sans table :

SELECT concatene(« Didier Deléglise »)
==> Bonjour Didier Deléglise

Deux exemples simples de procédures stockées MySQL

Créons une procédure sans paramètre qui augmente inconditionnellement les salaires de 10% (bonne nouvelle !) :

mysql> create procedure augmente()
BEGIN
update emp set sal=sal*1.1;
END;
==> procédure créée avec succès
mysql> select ename , sal from emp where ename = ‘SMITH’;
==> SMITH 800

pour le moment nous n’avons fait que définir la procédure mais elle n’a pas été éxécutée. Il faut faire un ‘call’ pour effectuer le traitement de l aprocédure

mysql> call augmente()
==> SQL executée avec succès
mysql> select ename , sal from emp where ename = ‘SMITH’;
==> SMITH 880

L’exemple suivant est une procédure stockée simple, qui utilise un paramètre de sortie OUT. L’exemple utilise la commande delimiter du client mysql pour modifier le délimiteur de commande avant de définir la procédure. Cela permet au délimiteur ‘;‘ d’être utilisé dans le corps de la procédure, plutôt que d’être interprété par le client mysql.

mysql> delimiter |
mysql> CREATE PROCEDURE compte (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM emp;
END
|
mysql> show create procedure compte;
Procedure   sql_mode   	Create Procedure
compte      		CREATE DEFINER=`root`@`localhost`
    			PROCEDURE `compte`(OUT param1 INT)nBEGINrn
 		SELECT COUNT(*) INTO param1 FROM emp;rnEND
mysql> set @v=1;
mysql> call compte(@v);
mysql> select @v;
@v
--
14

Note : avec PHPMyAdmin, le changement de délimiteur se fait via l’interface graphique et par par une commande SQL.

Instructions « SQL » spécifiques pour les routines stockées

Hormis les ordres SQL, on peut utiliser un certain nombre d’instructions classiques de langage de programmation : des déclaration de variables, des conditionnelles, des boucles, des gestions de curseurs SQL (fetch).

Le tableau suivant décrit sommairement ces instructions :

CALL : Appelle une procédure stockée précédemment définie, qui a été créée à l’aide de l’instruction CREATE PROCEDURE.
BEGIN … END : Contient un groupe de plusieurs instructions en vue de leur exécution.
DECLARE : Sert à définir des variables locales, des conditions, des routines de gestion et des curseurs.
SET : sert à modifier les valeurs des variables locales et des variables serveur globales.
SELECT … INTO : Sert à stocker les colonnes indiquées directement dans des variables.
OPEN : Sert à ouvrir un curseur.
FETCH
Extrait la ligne suivante à l’aide du curseur spécifié et avance le curseur d’une ligne.
CLOSE : Sert à fermer un curseur ouvert.
IF : instruction conditionnelle IF-THEN-ELSE-END IF.
CASE … : construction conditionnelle d’une instruction CASE.
LOOP : Structure de boucle simple; la sortie se fait à l’aide de l’instruction LEAVE.
LEAVE : Sert à sortir des instructions IF, CASE, LOOP, REPEAT et WHILE.
ITERATE : Utilisée dans les boucles pour recommencer au début de la boucle.
REPEAT : Boucle avec le test conditionnel à la fin.
WHILE : Boucle avec le test conditionnel au début.
RETURNS : Renvoie une valeur d’une fonction stockée.

Exemple d’utilisation de variables :

CREATE PROCEDURE sp1 (x VARCHAR(2))
BEGIN
DECLARE nom VARCHAR(5) DEFAULT ‘toto’;
DECLARE y VARCHAR(5);
DECLARE z INT;

SELECT nom,no INTO y,z
FROM table1 WHERE nom = x;

END;

et utilisation d’une boucle simple pour calculer une factorielle :

CREATE function factorielle (x INT)
returns bigint
BEGIN
declare r bigint;
set r=1;
WHILE x >1 DO
SET r = r*x;
set x = x-1;
END WHILE ;
return r;
END

Infos complémentaires

D’autres exemples simples de proc avec paramètre ici :
http://www.databasejournal.com/features/mysql/article.php/3525581

remarque:on peut aussi créer des TRIGGERs voir : http://dev.mysql.com/doc/refman/5.0/fr/triggers.html

Toutes les informations sur les procédures stockées sont dans le manuel ICI :

http://dev.mysql.com/doc/refman/5.0/fr/stored-procedures.html

Droits et privilèges

Le système de privilèges MySQL permet de gérer les connexions et les droits sur les données.

En d’autres termes, il permet d’authentifier les utilisateurs se connectant à partir d’un hôte donné, et de leur associer des privilèges sur une base de données ou sur des tables particulières, comme SELECT, INSERT, UPDATE et DELETE.

Les différents droits sur les tables et sur les bases

Ils peuvent être listés avec la commande ‘SHOW PRIVILEGES’

Voici une liste des droits de MySQL avec le contexte dans lequel ils s’appliquent :

Droit Colonne Contexte
ALTER Alter_priv tables
DELETE Delete_priv tables
INDEX Index_priv tables
INSERT Insert_priv tables
SELECT Select_priv tables
UPDATE Update_priv tables
CREATE Create_priv bases de données, tables ou index
DROP Drop_priv bases de données ou tables
GRANT Grant_priv bases de données ou tables
REFERENCES References_priv bases de données ou tables
CREATE TEMPORARY TABLES Create_tmp_table_priv administration du serveur
EXECUTE Execute_priv administration du serveur
FILE File_priv accès aux fichiers du serveur
LOCK TABLES Lock_tables_priv administration du serveur
PROCESS Process_priv administration du serveur
RELOAD Reload_priv administration du serveur
REPLICATION CLIENT Repl_client_priv administration du serveur
REPLICATION SLAVE Repl_slave_priv administration du serveur
SHOW DATABASES Show_db_priv administration du serveur
SHUTDOWN Shutdown_priv administration du serveur
SUPER Super_priv administration du serveur

Les droits de SELECT, INSERT, UPDATE et DELETE portent sur le contenu des tables (ordres SQL du Langage de Manipulation de données).

Le droit de GRANT vous permet de donner les droits que vous possédez à d’autres utilisateurs. Le droit ‘FILE' vous donne la possibilité de lire et écrire des fichiers sur le serveur avec les commandes LOAD DATA INFILE et SELECT ... INTO OUTFILE. Tout utilisateur qui possède ce droit peut donc lire ou écrire dans n’importe quel fichier à l’intérieur duquel le serveur MySQL peut lire ou écrire.

Les autres droits sont utilisés pour les opérations administratives qui sont exécutées par l’utilitaire mysqladmin.

Les limites du système de droits de MySQL :

  • Vous ne pouvez pas explicitement interdire l’accès à un utilisateur spécifique. C’est à dire, vous ne pouvez pas explicitement décrire un utilisateur et lui refuser la connexion.
  • Vous ne pouvez pas spécifier qu’un utilisateur a les droits de créer et de supprimer des tables dans une base, mais qu’il n’a pas les droits pour créer et supprimer cette base.
  • supprimer un objet ne supprime pas les droits associés

Informations sur les droits actuels

Les droits de l’utilisateur courant peuvent être visualisés avec la commande :

mysql> SHOW GRANTS;

ou pour un utilisateur particulier

mysql> SHOW GRANTS FOR ‘bob’@’pc84.example.com’;

Les droits des utilisateurs sont stockés dans les tables user, db, host, tables_priv et columns_priv de la base mysql (c’est-à-dire, la base nommée mysql).Ces tables de droits sont utilisées comme suit :

  • La table user détermine si le serveur accepte ou rejette la connexion. Pour les connexions acceptées, tous les privilèges donnés dans la table user indiquent des privilèges globaux. Ces droits d’appliquent à toutes les bases du serveur.
  • Les champs d’identification de la table db déterminent quels utilisateurs peuvent accéder à quelles bases, depuis quel hôte. Les champs de droits indiquent alors les opérations permises. Les droits s’appliquent alors à toutes les bases sur le serveur.
  • La table host est utilisée comme extension de la table db lorsque vous voulez qu’une ligne de la table db s’applique à plusieurs hôtes. Par exemple, si vous voulez qu’un utilisateur soit capable d’utiliser une base depuis plusieurs hôtes dans votre réseau, laissez la colonne Host vide dans la table db
  • Les tables tables_priv et columns_priv sont similaires à la table db, mais sont plus atomiques : elle s’appliquent au niveau des tables et des colonnes, plutôt qu’au niveau des bases.

Le serveur MySQL lit ces tables au démarrage, ou grace à la commande ‘FLUSH PRIVILEGE’.Note : attention aux droits donnés sur la base ‘mysql’ …qui permet de gérer les droits ! Un utilisateur normal ne devrait pas y avoir accès…

A partir de la version 5.0 il existe une base particulière : INFORMATION_SCHEMA, qui représente le dictionnaire de données. Les tables INFORMATION_SCHEMA.USER_PRIVILEGES,
INFORMATION_SCHEMA.SCHEMA_PRIVILEGES, et
INFORMATION_SCHEMA.COLUMN_PRIVILEGES seront particulièrement utiles pour lister les privilèges (et plus souples que les commandes SHOW !)

Le contenu de la table USER_PRIVILEGES d’une base MySQL fraichement installée a ainsi l’allure suivante :

MYSQL> SELECT * FROM USER_PRIVILEGES;

GRANTEE 		TABLE		PRIVILEGE	IS_
			_catalog	_TYPE		GRANTABLE

'root'@'localhost' 	NULL 		SELECT 		YES
'root'@'localhost' 	NULL 		INSERT 		YES
'root'@'localhost' 	NULL 		UPDATE 		YES
'root'@'localhost' 	NULL 		DELETE 		YES
'root'@'localhost' 	NULL 		CREATE 		YES
'root'@'localhost' 	NULL 		DROP 		YES
'root'@'localhost' 	NULL 		RELOAD 		YES
'root'@'localhost' 	NULL 		SHUTDOWN 	YES
'root'@'localhost' 	NULL 		PROCESS 	YES
'root'@'localhost' 	NULL 		FILE 		YES
'root'@'localhost' 	NULL 		REFERENCES 	YES
'root'@'localhost' 	NULL 		INDEX 		YES
'root'@'localhost' 	NULL 		ALTER 		YES
'root'@'localhost' 	NULL 		SHOW DATABASES 	YES
'root'@'localhost' 	NULL 		SUPER 		YES
'root'@'localhost' 	NULL 		CREATE TEMPTAB	YES
'root'@'localhost' 	NULL 		LOCK TABLES 	YES
'root'@'localhost' 	NULL 		EXECUTE 	YES
'root'@'localhost' 	NULL 		REPLIC. SLAVE 	YES
'root'@'localhost' 	NULL 		REPLIC. CLIENT 	YES
'root'@'localhost' 	NULL 		CREATE VIEW 	YES
'root'@'localhost' 	NULL 		SHOW VIEW 	YES
'root'@'localhost' 	NULL 		CREATE ROUTINE 	YES
'root'@'localhost' 	NULL 		ALTER ROUTINE 	YES
'root'@'localhost' 	NULL 		CREATE USER 	YES

GRANT et REVOKE

Les droits s’accordent avec la commande ‘GRANT’ et se suppriment avec la commande ‘REVOKE’.
On pourrait insérer directement dans les tables de la base ‘mysql’ mais cela dépendant de la structure de ces tables, qui peut changer au fur et à mesure des versions…. De plus les ordres GRANT et REVOKE sont du SQL standard !
Les privileges sont accordés à différent niveaux.

  • niveau Global
    sur toutes les databases d’un serveur donné. (table mysql.user)
    On précise ‘*.*’ comme cible des privilèges

    GRANT ALL ON *.* TO 'someuser'@'somehost';
    
    GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';
  • niveau Database
    sur tous les objets d’une base donnée. (table mysql.db).
    On précise le nom de la base dans la syntaxe

    GRANT ALL ON mydb.* TO 'someuser'@'somehost';
    
    GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';
  • niveau Table
    Sur une table particulière. (stockée dans mysql.tables_priv)
    On précise le nom de la base ET le nom de la table visée.

    GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
    
    GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';
  • niveau colonne
    sur des colonnes précises d’une table. (privilège stocké dans mysql.columns_priv table)
    Les colonnes concernées sont notées entre parenthèses.

    GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';
  • niveau routine (procédures stockées)
    CREATE ROUTINE, ALTER ROUTINE, EXECUTE, et GRANT privileges s’appliquent à des functions et procedures. (privilège stocké dans la table mysql.procs_priv)
    Ils peuvent aussi être GRANTés au niveau global ou database.

    GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
    
    GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';

Note : on peut rajouter une clause ‘ IDENTIFIED BY password' notamment pour la création de nouveaux comptes.

Sécurité d’une base MySQL en production

Pour respecter un minimum de sécurité et notamment de confidentialité des données, on donnera à l’utilisateur UNIQUEMENT LES PRIVILEGES DONT IL A BESOIN !

Quelques règles minimales à respecter :

  • on ne travaillera pas en tant qu’utilisateur root sur MySQL
  • tous les utilisateurs auront un mot de passe (a fortiori ‘root’) !
  • on ne donnera pas tous les droits sur toutes les tables à un utilisateur
  • on ne se connectera pas en environnement réel de production, dans le compte créateur des données (le compte du développeur en général)
  • les données d’une application particulière seront circonscrites dans une ‘database’ particulière

Typologie des utilisateurs et profils de sécurité associés

En pratique on peut distinguer la plupart du temps, 5 catégories d’utilisateurs, qui utilisent différents outils en fonction de leur profil. Ceci est résumé dans le tableau suivant :

Type d’utilisateur profil outil exemple MySQL
utilisateur infocentre
non informaticien
tableau de bord, outil infocentre (BI) appli web en consultation uniquement
Utilisateur final non informaticien application client serveur ou web, ou progiciel, ou appli bureautique, outil d’aide à la décision appli web
utilisateur final évolué non informaticien éclairé client QBE (Query by Example) PHPMyAdmin
Développeur Technicien ou ingénieur Bloc note, environnement de developpement, Atelier de développement textpad, client mysql
Administrateur application Utilisateur final hiérarchiquement privilégié l’application elle même, via un accès privilégié NA
DBA Technicien ou ingénieur langage SQL, console d’admin texte, console graphique, console web PhpMyAdmin
DBA assistant

A chacune de ces catégorie correpond un groupe de privilèges.

Un utilisateur final d’application classique a par exemple seulement besoin de lire le contenu (SELECT) et faire des mise à jour (UPDATE, INSERT, DELETE) sur certaines tables d’une base de données.

On pourra donc créer un  user générique avec ce profil.
En utilisant un script SQL par exemple.
Il n’est pas nécessaire de créer un user MySQL par user qui se connecte heureusement !

exercice MySQL – installation et initialisation

Nous allons installer MySQL, le configurer et initialiser une base de données de démonstration…

  • Télechargez une version de MySQL 5.1 sur http://dev.mysql.com/downloads/mysql/5.1.html

Le programme d’install (setup.exe) requiert 85MO d’espace disque.
Ensuite si vous choisissez l’install complète il vous faudra 412 MO supplémentaire, ou l’install ‘Typical’, sans programme client graphique 267MO.

  • Installez le serveur MySQL 5.1

lancez setup.exe, choisissez l’installation complète si possible, sinon typical.
Passez les écrans de pub pour MySQL enterprise, choisissez ensuite ‘configuration standard’, saisissez un mot de passe pour ‘root’, confirmez et NOTEZ LE QUELQUE PART!

  • Téléchargez la base de données de Demo ‘ménagerie’ sur

http://downloads.mysql.com/docs/menagerie-db.zip
et décompressez les fichiers

  • créez la petite base de démo ‘ménagerie’,

Ouvrez le fichier Readme.txt qui se trouve dans le dossier ‘menagerie-db’
Vérifiez que le répertoire bin de Mysql, a été rajouté dans le ‘PATH’, sinon ajoutez le en relancant l’utilitaire de configuration MySQL, dans le Menu Programmes/MySQL ou en utilisant les paramètres d’environnement Windows ou Linux.

Positionnez vous dans le répertoire db-menagerie.
Connectez vous à MySQL, dans le compte root, en utilisant le client ‘mysql’ en ligne de commande :

C:\>…\db-menagerie\mysql -u root -p

entrez votre mot de passe et suivez les instructions du readme pour créer les tables et les données…

  • listez les noms de tables de la base
    listez le contenu des tables

combien y a t il de tables ?
de lignes dans chaque table ?

exercice MySQL – ordres autres que SELECT

Avec un éditeur de texte, écrivez un script pour :

  • Créer une copie vide de la table ‘animal’ en ‘animaux’
  • Ajouter une colonne ‘no_animal’ clé primaire (valeur obligatoire et auto incrémentée bien sur)
  • Inserez les lignes de ‘animal’ sans les oiseaux ‘bird’
    Attention les tables animal et animaux n’ont + le même nb de colonnes
  • Traduire les especes ‘cat’, ‘dog’ et ‘snake’ en modifiant les lignes de la table
  • Supprimer ‘slim’ de la table et la renommer en mammifere
  • Afficher les noms des animaux vivants dans la table

Executez le script sur la base menagerie

corrigé MySQL – ordres autres que SELECT

Voici le contenu du script :

/* Créer une copie de la table ‘animal’ en ‘animaux’, vide */

create table animaux as select * from animal
where 1=2;

/* Ajouter une colonne ‘no_animal’ clé primaire */

alter table animaux add no_animal integer not null auto_increment primary key;

/* Inserez les lignes de ‘animal’ sans les oiseaux (‘bird’) */
insert into animaux
( nom, maitre, espece, sexe, naissance, mort )
select * from animal
where espece <> « bird »

/* Traduire les especes ‘cat’, ‘dog’ et ‘snake’
en modifiant les lignes de la table */

update animaux set espece = »chat »
where espece = « cat »;
update animaux set espece = »chien »
where espece = « dog »;
update animaux set espece = »serpent »
where espece = « snake »;

/* Supprimer ‘slim’ de la table et la renommer en mammiferes */

delete from animaux where nom = « slim »;
rename table animaux to mammifere;

/* afficher les animaux vivants */

select nom from mammifere where mort is null;

Pour l’exécuter …

mysql -u root -p

mysql> use menagerie

mysql> source chemin_du_script\nom_du_script

ou plus directement

mysql -u root -p menagerie <c:\temp\mon_script.sql

Les différentes extensions PHP / MySQL

logo_php_assis.gif    logo_mysql1.gif

Le langage PHP est en pratique, quasiment indissociable de MySQL tant l’utilisation de ce tandem est répandue sur la toile. Tous les hébergeurs et fournisseurs d’accès Internet offrent ce service à leurs abonné(e)s.

Comment remplir une page Web avec des informations provenant d’une BD MySQL ?
En utilisant les extensions MySQL de PHP, c’est ce que nous allons étudier dans cette partie du cours.

PHP + MySQL permettent de développer des sites Internet au contenu totalement dynamique, et d’écrire des applications de gestion complètes sur le web, comme des applications de e-commerce par exemple.

Leur particularité par rapport à d’autres solutions techniques (.Net + SQLServer, Java + Oracle, …) est que cette solution est facile à mettre en place, Open Surce et donc gratuite !
MySQL peut être accédée en PHP de 3 manières différentes, grace :

  • à l’extension standard ‘mysql’ (ensemble de fonctions préfixées par ‘mysql_’)
  • à l’extension améliorée ‘mysqli’ ou Improved MySQL (ensemble de fonctions préfixées par ‘mysqli_’)
  • au pilote ‘PDO MySQL’ (ensemble de fonctions préfixées par ‘pdo_mysql’)

Elles seront vues en détail dans les chapitres suivants.

L’extension standard ‘ mysql ‘

Cette extension est souvent associée à la version basique du moteur MySQL. Elle est incluse en standard dans certains packages comme ‘easyPHP’ par exemple.
Elle utilise 3 ressources principales :

  • une connexion
  • un ordre SQL
  • un ensemble de lignes résultats (si le SQL est un SELECT bien sûr) : le ‘result set’ ou ‘curseur’

Du fait des limites de cette extension, les ordres SQL sont exécutés directement sans phase de préparation (parsing, bind),
– il n’y a pas de gestion explicite de transaction (commit, rollback). Les mises à jour seront par défaut AUTOCOMMITées

Voici d’après le site officiel PHP, la liste complète (une cinquantaine) des fonctions de l’extension ‘mysql’ en PHP5

L’extension ‘ mysqli ‘

Par rapport à la précédente :

  • supporté par MySQL à partir de 4.1.3 seulement
  • une approche objet des traitements (objet ‘mysqli’),
  • la gestion des transactions,
  • beaucoup plus de fonctions,
  • les ordres SQL préparés (avec bind variables),

soit plus de 110 fonctions, documentées sur http://fr.php.net/manual/fr/ref.mysqli.php

L’extension ‘ pdo_mysql ‘

pdo_mysql est un pilote spécifique pour MySQL, qui implémente l’interface de PHP Data Object. Comme son nom l’indique cette interface est orientée objet. La documentation est disponible sur http://fr.php.net/manual/fr/ref.pdo.php

A partir de la V5 de PHP, cette extension tend à remplacer pear_db.
C’est donc une couche d’interaction avec le SBGD (database layer) qui comme ODBC sur WIndows, permet d’écrire du code SQL portable, et est (relativement) indépendante du SGBD relationnel cible.

Les drivers suivants sont actuellement inclus :

PDO_MYSQL MySQL 3.x/4.x/5.x
PDO_DBLIB : pour FreeTDS / Microsoft SQL Server / Sybase
PDO_OCI : pour Oracle Call Interface
PDO_ODBC : pour ODBC v3 (IBM DB2 unixODBC et win32 ODBC
PDO_PGSQL : pour PostgreSQL
PDO_SQLITE : pour SQLite 3 et SQLite 2
PDO_FIREBIRD : pour Firebird/Interbase 6
PDO_IBM : pour IBM DB2
PDO_INFORMIX : pour IBM Informix Dynamic Server

PDO supporte les procédures stockées, les ordres préparés, les transactions,…

Logique générale d’un programme : CLI , PHP, MySQL et les autres

CLI et APIs standards

Le standard CLI (Call Level Interface) a été créé dans les années 90 par le SQL Access Group (SAG) pour permettre à tous les SGBDRs d’intéragir de manière normalisé avec des langages de programmation.

CLI est donc une interface standard de programmation : une ‘Application Programming Interface’ ou A.P.I

Pour + d’nfos voir : http://www.tar.hu/sqlbible/sqlbible0117.html

O.D.B.C (Open database Connectivity) de Microsoft est par exemple conforme à ce standard.

Concrètement cela veut dire que si l’on comprend la logique d’un programme PHP/mysqli, ou PHP/PDOMySQL, on pourra facilement écrire un programme en VB pour Oracle car la séquence des opérations et leur butssont quasiment toujours les mêmes…

Logique générale standard

La logique générale d’un programme accédant à une base de données (et exemple de fonctions CLI correspondantes) est la suivante :

1. allocation handler de connexion : AllocHandle()
2. connexion : Connect()
3. allocation handler ordre SQL : AllocHAndle()
4. (préparation éventuelle de l’ordre, si paramètres…) : Prepare()
5. exécution de l’ordre (voir zoom ci après) : Execute() ou ExecDirect()
6. déconnexion : Disconnect()
7. libération des ressources : FreeHAndle()

Zoomons plus précisément sur l’exécution d’un ordre :

cli_sql_flow.jpg

exemple d’application : mise à jour avec PDO

Un ordre de mise à jour simple sans paramètres

<?php

// ouverture de connexion
$dbh = new PDO(‘mysql:host=localhost;dbname=test’, $user, $pass);

// execution
$stmt->execute();

// liberation
$dbh = null;
?>

un ordre de mise à jour avec paramètres

<?php

// ouverture de connexion
$dbh = new PDO(‘mysql:host=localhost;dbname=test’, $user, $pass);

// preparation de l’ordre
$stmt =$dbh->prepare(« INSERT … »);

// association des parametres
$stmt->bindParam(…);
$stmt->bindParam(…);

// execution
$stmt->execute();

// liberation
$dbh = null;
?>

Un ordre SELECT est un peu plus compliqué car il faudra après l’exécution récupérer les lignes par une série de ‘fetch’ et bein sûr…les afficher en les formattant correctement.

Accès BD via l’extension mysql

Les fonctions de l’extension mysql

Voici d’après le site officiel PHP : http://www.php.net , la liste des fonctions de l’extension MySQL standard :

* mysql_affected_rows — Retourne le nombre de lignes affectées lors de la dernière opération MySQL
* mysql_change_user — Change le nom de l’utilisateur de la connexion active
* mysql_client_encoding — Retourne le nom du jeu de caractères utilisé par le client MySQL
* mysql_close — Ferme la connexion MySQL
* mysql_connect — Ouvre une connexion à un serveur MySQL
* mysql_create_db — Crée une base de données MySQL
* mysql_data_seek — Déplace le pointeur interne de résultat MySQL
* mysql_db_name — Lit les noms des bases de données
* mysql_db_query — Envoie une requête MySQL à un serveur MySQL
* mysql_drop_db — Efface une base de données MySQL
* mysql_errno — Retourne le numéro d’erreur de la dernière commande MySQL
* mysql_error — Retourne le texte associé avec l’erreur générée lors de la dernière requête
* mysql_escape_string — Protège les caractères spéciaux SQL
* mysql_fetch_array — Retourne une ligne de résultat MySQL sous la forme d’un tableau associatif, d’un tableau indexé, ou les deux
* mysql_fetch_assoc — Lit une ligne de résultat MySQL dans un tableau associatif
* mysql_fetch_field — Retourne les données enregistrées dans une colonne MySQL sous forme d’objet
* mysql_fetch_lengths — Retourne la taille de chaque colonne d’une ligne de résultat MySQL
* mysql_fetch_object — Retourne une ligne de résultat MySQL sous la forme d’un objet
* mysql_fetch_row — Retourne une ligne de résultat MySQL sous la forme d’un tableau
* mysql_field_flags — Retourne des détails sur une colonne MySQL
* mysql_field_len — Retourne la taille d’un champ de résultat MySQL
* mysql_field_name — Retourne le nom d’une colonne dans un résultat MySQL
* mysql_field_seek — Déplace le pointeur de résultat vers une position donnée
* mysql_field_table — Retourne le nom de la table MySQL où se trouve une colonne
* mysql_field_type — Retourne le type d’une colonne MySQL spécifique
* mysql_free_result — Libère le résultat de la mémoire
* mysql_get_client_info — Lit les informations sur le client MySQL
* mysql_get_host_info — Lit les informations sur l’hôte MySQL
* mysql_get_proto_info — Lit les informations sur le protocole MySQL
* mysql_get_server_info — Lit les informations sur le serveur MySQL
* mysql_info — Lit des informations à propos de la dernière requête MySQL
* mysql_insert_id — Retourne l’identifiant généré par la dernière requête INSERT MySQL
* mysql_list_dbs — Liste les bases de données disponibles sur le serveur MySQL
* mysql_list_fields — Liste les champs d’une table MySQL
* mysql_list_processes — Liste les processus MySQL
* mysql_list_tables — Liste les tables d’une base de données MySQL
* mysql_num_fields — Retourne le nombre de champs d’un résultat MySQL
* mysql_num_rows — Retourne le nombre de lignes d’un résultat MySQL
* mysql_pconnect — Ouvre une connexion persistante à un serveur MySQL
* mysql_ping — Vérifie la connexion au serveur MySQL, et s’y reconnecte au besoin
* mysql_query — Envoie une requête à un serveur MySQL
* mysql_real_escape_string — Protège les caractères spéciaux d’une commande SQL
* mysql_result — Retourne un champ d’un résultat MySQL
* mysql_select_db — Sélectionne une base de données MySQL
* mysql_set_charset — Sets the client character set
* mysql_stat — Retourne le statut courant du serveur MySQL
* mysql_tablename — Lit le nom de la table qui contient un champ
* mysql_thread_id — Retourne l’identifiant du thread MySQL courant
* mysql_unbuffered_query — Exécute une requête SQL sans mobiliser les résultats MySQL

Afin d’y voir plus clair, nous allons tenter de les regrouper par types d’opération ou de fonctionnalités

Taxinomie des fonctions MySQL de PHP

connexion mysql_connect, mysql_pconnect,
mysql_close, mysql_ping,
(my_sql_select_db, mysql_change_user)
gestion d’erreurs mysql_error, mysql_errno
méta données DB mysql_field_flags, mysql_field_name, mysql_field_len, mysql_field_table, mysql_field_type, mysql_list_fields, mysql_info,
mysql_insert_id,mysql__num_rows
mysql_list_tables, mysql_table_name
méta données Serveur mysql_client_encoding, mysql_get_client_info,
mysql_get_host_info, mysql_get_proto_info,
mysql_get_server_info,mysql_list_dbs,
mysql_list_processes, mysql_stat, mysql_thread_id
lecture lignes mysql_fetch_array, mysql_fetch_result_row,
mysql_fetch_assoc, mysql_fetch_field,
mysql_fetch_lengths, mysql_fetch_object, mysql_fetch_row,
execution SQL mysql_drop_db, mysql_db_query,
mysql_query, mysql_unbuffered_query

Se connecter et mettre à jour une base

Tout d’abord…se connecter

<?php
$dbhost = ‘mysql.estsurinternet.com:3306’;
$dbuser = ‘appli1’;
$dbpass = ‘mon_password’;
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die (‘Erreur de connexion à mysql’);
$dbname = ‘animal’;
mysql_select_db($dbname);
print (« Vous etes connecte à la base $dbname ! »);
?>
on pourra mettre la partie parametres de connexion dans un fichiers de configuration, qu’il n’ya aura qu’a inclure pour éviter de répéter le code :
<?php
// config_db.php
// usage : include ‘config_db.php’; …en début de programme
$dbhost = ‘localhost’;
$dbuser = ‘DD’;
$dbpass = ‘mon_password’;
$dbname = ‘animal’;
?>

Puis exécuter une commande SQL simple (autre que SELECT)

//une insertion de lignes
include ‘config_db.php’;
$conn = mysql_connect($dbhost, $dbuser, $dbpass)
or die (‘Erreur de connexion à mysql’);
mysql_select_db($dbname);
$ordre_sql = « INSERT INTO pet VALUES (…) »;
//implicitement mysql_query utilise la connexion précédente
mysql_query($ordre_sql) or die(‘Erreur insertion’);
// l’ordre est autoCOMMITé

rem : un SELECT est toujours un peu plus compliqué à gérer, quelquesoit le langage de programmation utilisé. En effet il faudra rapatrier les lignes …et les afficher !

SELECT avec fetch ligne à ligne

on se connecte , on définit un ordre SQL, et on fait des des fetch pour rapatrier les lignes…
Chaque ligne est un tableau de colonnes indicé numériquement

<?php
// connexion
$connection = mysql_connect(« localhost », »dd », »dd »);
mysql_select_db(« test », $connection);
// execution de la requete
$result = mysql_query (« SELECT name, continent FROM
country order by 2,1 », $connection );
// lecture des lignes
while ($row = mysql_fetch_row($result))   {

print (« nom : $row[0] continent : $row[1] <br> »);
}
// deconnexion
mysql_close($connection);
?>

Si on veut pouvoir afficher un SELECT quelconque sans connaitre les colonnes de la table, on fera une boucle interne pour lire toutes les colonnes.
On s’appuiera sur  la meta données ‘mysql_num_fields’ qui renvoie le nombre de colonnes de la table.

<?php
// connexion
$connection = mysql_connect(« localhost », »dd », »dd »);
mysql_select_db(« test », $connection);
// execution de la requete
$result = mysql_query (« SELECT name, continent FROM
country order by 2,1 », $connection );
// lecture des lignes
while ($row = mysql_fetch_row($result))   {
print (« <pre> »);
for ($i=0; $i<mysql_num_fields($result); $i++) {
print (« $row[$i] t »);
}
print (« n »);
}
print (« </pre> »);
// deconnexion
mysql_close($connection);
?>

et en rajoutant les entêtes  de colonne puisées dans  ‘mysql_field_name’ :

<?php
// connexion
$connection = mysql_connect(« localhost », »ddelegli », »ddelegli »);
mysql_select_db(« test », $connection);
// execution de la requete
$result = mysql_query (« SELECT name, continent FROM
country order by 2,1 », $connection );
// affichage des entetes
print (« <pre> »);
for ($i=0; $i<mysql_num_fields($result); $i++) {
print (mysql_field_name($result,$i). » t »);
}
print (« n »);
// lecture des lignes
while ($row = mysql_fetch_row($result))   {
for ($i=0; $i<mysql_num_fields($result); $i++) {
print (« $row[$i] t »);
}
print (« n »);
}
print (« </pre> »);
// deconnexion
mysql_close($connection);
?>

Exécution d’une procédure stockée avec mysqli

Créons une procédure stockée qui fait un simple SELECT sur une table .

Cette procédure ne prend pas de paramètres en entrée, maisi ramène n lignes, c’est là que ca se complique…

MYSQL> create procedure ps1 ()
begin
select * from dept;
end;

rem : la table DEPT contient 4 lignes de 3 colonnes…

Voici le code PHP correspondant qui lit le ‘RESULT SET’ (tableau de résultats) du SELECT excéuté par la procédure :

<?php
// connexion
$connexion = mysqli_connect(« localhost », « dd », « dd », « test »);

/* erreur eventuelle de connexion */
if (mysqli_connect_errno()) {
printf(« Pb lors de la connexion : %s<br> », mysqli_connect_error());
exit();
}
print (« connecte !<br><br> »);

/* appel de la procedure stockée */
$ordre_sql = « call ps1(); »;

if (mysqli_multi_query($connexion, $ordre_sql)) {
do {
// lecture d’une ligne…
if ($result = mysqli_store_result($connexion)) {
// affichage des 3 colonnes
while ($ligne = mysqli_fetch_row($result)) {
printf(« %s %s %s <br> », $ligne[0], $ligne[1],$ligne[2] );
}
mysqli_free_result($result);
}
// tant qu’il y en a…
} while (mysqli_next_result($connexion));
}

/* Fermeture de la connexion */
mysqli_close($connexion);
?>

accès BD avancés avec mysqli

Ordres SQL avec paramètres d’entrée
(prepared statements)

un insert simple paramétré

<?php
$conn = mysqli_connect(‘localhost’, ‘dd’, ‘dd’, ‘test’);

$stmt = mysqli_prepare($conn, « INSERT INTO CountryLanguage (countrycode, language) VALUES (?, ?) »);
// on associe les variables PHP aux parametres de l’ordre SQL
// il y en a 2 de type string (‘ss’)
mysqli_stmt_bind_param($stmt, ‘ss’, $code, $lang);

// affectation des valeurs de parametre (via les var php)
$code = ‘DEU’; $lang = ‘Bavarian’;

/* execute le prepared statement */
mysqli_stmt_execute($stmt);

print(« Nb de lignes traitees : « .mysqli_affected_rows($conn));

/* fermeture ordre sql */
mysqli_stmt_close($stmt);

/* fermeture connexion */
mysqli_close($conn);
?>

un select paramétré

<?php
$link = mysqli_connect(« localhost », « my_user », « my_password », « world »);
/* Vérification de la connexion */
if (mysqli_connect_errno()) {
printf(« Échec de la connexion : %sn », mysqli_connect_error());
exit();
}
$city = « Paris »;
/* Crée une requête préparée */
if ($stmt = mysqli_prepare($link, « SELECT District FROM City WHERE Name=? »)) {
/* association des variables*/
mysqli_stmt_bind_param($stmt, « s », $city);
/* Exécution de la requête */
mysqli_stmt_execute($stmt);
/* Lecture des variables résultantes */
mysqli_stmt_bind_result($stmt, $district);
/* Récupération des valeurs */
mysqli_stmt_fetch($stmt);
printf(« %s est dans le district de %sn », $city, $district);
/* Fermeture du traitement */
mysqli_stmt_close($stmt);
}
/* Fermeture de la connexion */
mysqli_close($link);
?>

SELECT avec un nombre queLconque de colonnes…
(extrait du manuel PHP, contribution de Matty : http://fr3.php.net/manual/en/function.mysqli-stmt-bind-result.php)

<?php
# of fields in result set.
$nof = mysqli_num_fields( mysqli_stmt_result_metadata($handle) );

# The metadata of all fields
$fieldMeta = mysqli_fetch_fields( mysqli_stmt_result_metadata($handle) );

# convert it to a normal array just containing the field names
$fields = array();
for($i=0; $i < $nof; $i++)
$fields[$i] = $fieldMeta[$i]->name;

# The idea is to get an array with the result values just as in mysql_fetch_assoc();
# But we have to use call_user_func_array to pass the right number of args ($nof+1)
# So we create an array:
# array( $stmt, &$result[0], &$result[1], … )
# So we get the right values in $result in the end!

# Prepare $result and $arg (which will be passed to bind_result)
$result = array();
$arg = array($this->stmt);
for ($i=0; $i < $nof; $i++) {
$result[$i] =  »;
$arg[$i+1] = &$result[$i];
}

call_user_func_array (‘mysqli_stmt_bind_result’,$arg);

# after mysqli_stmt_fetch(), our result array is filled just perfectly,
# but it is numbered (like in mysql_fetch_array() ), not indexed by field name!
# If you just want to mimic that ones behaviour you can stop here 🙂

mysqli_stmt_fetch($this->stmt);

# Now you can use $result
print_r($result);

# But beware! when using the fetch in a loop, always COPY $result or else you might
# end with all the same values because of the references
?>