logo Pierre AULAS
Les bases de MySQL
 Parcours  |   Réalisations  |   Cours  |   Chroniques  |   Divers
Types de données MySQL
Création d'une table et contraintes associées
Manipuler les tables
Requêtes MySQL

Les bases de MySQL

Conseils avant de commencer

Il est conseillé de réaliser soi-même les exemples qui sont donnés dans le cours.

Cela suppose évidément que MySQL soit installé sur votre machine: soit avec EasyPhp (MySQL 4), soit en allant chercher la dernière version sur le site de MySQL (MySQL 5).
Et que MySQL soit lancé.

Deux solutions pour mettre en oeuvre MySQL sur votre pc:
- soit vous utilisez la fenêtre SQL qu'offre l'interface d'EasyPhp (clic droit sur l'icône EasyPhp > Administration > Gestion BDD > Sélection d'une base > Onglet SQL),
- soit vous utilisez le shell window (menu Démarrer > Exécuter > cmd.exe > cd C:\Program Files\EasyPhp1-8 > cd mysql\bin > mysql -u root -p > Password (rien).

Si vous avez directement installé la dernière version MySQL 5, il suffit pour ouvrir le shell d'aller dans le menu Démarrer > Programme > MySQL > MySQL 5.0 > MySQL Command Line Client.

Afin de sauvegarder vos exemples, prenez l'habitude d'écrire vos requêtes MySQL à l'intérieur d'un fichier texte auquel vous donnerez une extension .sql pour le distinguer des autres et que vous appelerez dans le shell à l'aide de la commande suivante:

source monFichier.sql

Par ailleurs ce cours ne revient pas sur la modélisation des bases de données. Celle-ci est considérée comme acquise.

1. Types de données MySQL

Principaux types de données SQL
 
Type  Description
CHAR(n)  Chaine de longueur fixe égale à n caractères.
INTEGER  Entier.
VARCHAR(n)  Chaine de longueur maximale n.
DECIMAL(m,n)  Numérique sur m chiffres avec n décimales.
DATE  Date avec le jour, le mois, l'année.
TIME  Horaire avec heures, minutes, secondes.
DATETIME  Date et horaire réunis (non ANSI)
TEXT  Texte de longueur quelconque (non ANSI)

 

1.1 Types numériques exacts et flottants

INTEGER [ZEROFILL] [UNSIGNED]

DECIMAL (m,n) [ZEROFILL]

Les types numériques exacts sont ceux dont la précision est déterminée à l'avance comme INTEGER et DECIMAL.
Ils acceptent l'option ZEROFILL qui permet de remplir avec des 0 l'espace restant.
Le type UNSIGNED permet d'interdire les nombres négatifs (en supprimant le bit de signe).
Le type DECIMAL permet de spécifier le nombre de chiffres avant et après la virgule mais il n'admet par l'option UNSIGNED.

FLOAT [(m,n)] [ZEROFILL]

Les numériques flottants en revanche ont une présentation propre à la machine: FLOAT correspond aux flottants en simple précision.
DOUBLE correspond aux flottants en double précision.

1.2 Caratères et chaînes de caractères

CHAR(m) [BINARY]

VARCHAR(m) [BINARY]

Les chaînes de caractères peuvent être stockées dans les deux types:
  CHAR qui spécifie un nombre de caractères fixe inférieur à 255 et
  VARCHAR qui spécifie un nombre de caractères maximal inférieur à 65535.
L'option BINARY permet les comparaisons de chaine prenant en compte la casse.

TEXT et BLOB permettent de répondre à la problématiques des chaines qui contiennent plus de 65535 caractères.
TEXT ne prend pas en compte la casse contrairement à BLOB.

Ces deux derniers types ne sont pas conformes à la norme ANSI. Posez-vous toujours la question, avant de les utiliser, de savoir si vous avez réellement besoin de disposer de chaînes de plus de 65535 caractères (soit l'équivalent de 40 pages standards).

1.3 Dates

Les dates de type DATE sont stockées selon le format AAAA-MM-JJ.

Le type TIME stocke les informations d'heure de la manière suivante: HH:MM:SS.
Quant au format DATETIME, il permet de combiner les deux précédents avec un format du type: AAAA-MM-JJ HH:MM:SS.
Seul le type DATE est conforme au standard SQL.

1.4 Les types ENUM et SET

ENUM (valeur1, valeur2, valeur3, ...)

SET (valeur1, valeur2, valeur3, ...)

Les types ENUM et SET sont spécifiques à MySQL. Ils permettent d'énumérer la liste des valeurs disponibles pour un champ (jusqu'à 65535 valeurs).
SET (contrairement à ENUM) autorise le choix de plusieurs valeurs à la fois au sein de la liste.

Ces types peuvent rendre service pour des champs admettant peu d'options dont vous êtes sûr qu'elles n'évolueront pas. Par exemple:

sexe ENUM ("Femme", "Homme")

Peu de chance qu'une troisième option se présente avant un certain temps.

2. Création d'une table et contraintes associées

2.1 Créer une table

La syntaxe pour créer une table est des plus simple.

CREATE TABLE nom_table (nom_colonne type [DEFAULT valeur_defaut] [NOT NULL],
                        nom_colonne type [DEFAULT valeur_defaut] [NOT NULL],
                        ....);

Exemple:

CREATE TABLE batiment (id INTEGER NOT NULL,
                       nom VARCHAR (100) NOT NULL,
                       annee INTEGER DEFAULT 1900,
                       adresse VARCHAR (100),
                       architecte VARCHAR (100) NOT NULL);

La syntaxe est assez simple à comprendre: il faut donner un nom à chaque champs, le typer, et préciser un certains nombre de contraintes s'il y a lieu.

2.2 Définir des contraintes

Afin d'assurer l'intégrité de la base dans laquelle se trouve une table, on peut soumettre une table à un certain nombre de contraintes.
Les plus classiques sont les suivantes:
  - un champ doit toujours posséder une valeur.
  - un champ sert de clef primaire à la table.
  - un champ sert de clef étrangère: il fait dans ce cas référence à la clef primaire d'une autre table.
  - un champ doit être unique dans une table.
  - un champ n'accepte que certaines valeurs.

2.2.1 Posséder une valeur: NOT NULL et DEFAULT

Cette contrainte oblige à toujours indiquer une valeur pour ce champ.
DEFAULT permet de poser la même contrainte en indiquant la valeur à affecter par défaut.

Cette contrainte oblige à toujours indiquer une valeur pour ce champ.
DEFAULT permet de poser la même contrainte en indiquant la valeur à affecter par défaut.

CREATE TABLE batiment (id INTEGER NOT NULL,
                       nom VARCHAR (100) NOT NULL);
                       adresse VARCHAR (100),

CREATE TABLE batiment (id INTEGER NOT NULL,
                       nom VARCHAR (100) DEFAULT 'toto');
                       adresse VARCHAR (100),

Le premier exemple indique que le champ nom doit être rempli.
Le second exemple indique que dans le cas où aucune valeur ne serait insérée dans le champ nom, celui-ci vaudrait 'toto' (ça nous fait une belle jambe hein!).

2.2.2 Clefs primaires

Une table peut contenir différentes clefs mais ne doit contenir qu'une seule clef primaire. Celle-ci permet de référencer un enregistrement de manière unique.
Il va de soi qu'une clef primaire doit être déclarée comme NOT NULL.
On la déclare à l'aide de la syntaxe suivante

PRIMARY KEY (nomChamp)

ou encore

nomChamp PRIMARY KEY

MySQL fournit une option AUTO_INCREMENT qui lui est spécifique (non ANSI). Elle permet d'incrémenter automatiquement l'id déclaré comme clef primaire à chaque insertion.

2.2.3 Clefs étrangères

Avant de jouer avec les clefs étrangères, vous devez savoir que par défaut, leurs contraintes ne sont pas prises en compte par MySQL.
Il existe cependant plusieurs moteurs de stockage dans MySQL (c'est-à-dire de modules chargés de gérer les tâches de lecture et d'écriture sur le disque et de gérer les transactions).
Les deux plus utiles (sur lesquels nous reviendrons) sont:
  - MyIsam: c'est le moteur par défaut (qui ne gère pas l'intégrité référentielle).
  - InnoDB: c'est le moteur le plus évolué qui gère l'intégrité référentielle.

MyIsam est utilisé par défaut mais il est possible de préciser le moteur que l'on souhaite au moment de la création de la table à l'aide de la syntaxe suivante:

CREATE TABLE batiment (
  definitions
  ) ENGINE=InnoDB;

Pour déclarer une clef étrangère, c'est-à-dire qu'un champ de votre table fait référence à un champ d'une autre table, on utilise la syntaxe:

FOREIGN KEY (nomChamp) REFERENCES nomTable(nomChamp)

Exemple:

CREATE TABLE batiment (id INTEGER NOT NULL AUTO_INCREMENT,
                       nom VARCHAR (100) NOT NULL,
                       annee INTEGER DEFAULT 1900,
                       adresse VARCHAR (100),
                       id_Architecte INTEGER NOT NULL,
                       PRIMARY KEY (id),
                       FOREIGN KEY (id_architecte) REFERENCES architecte(id))
                       ENGINE=InnoDB;

ou id_Architecte référence la clef primaire de la table architecte qui pourrait être définie ainsi:

CREATE TABLE architecte (id INTEGER NOT NULL,
                         nom VARCHAR (100) NOT NULL,
                         prenom VARCHAR (100),
                         anneeNais INTEGER,
                         anneeMort INTEGER)
                         ENGINE=InnoDB;

MySQL indexe automatiquement les clefs primaires et étrangères. Les requêtes qui se basent sur elles sont donc très rapides.

Dans cet exemple, on définit une relation de un à plusieurs entre les tables architecte et batiment: un architecte peut avoir construit 0 ou plusieurs bâtiments et tout bâtiment a un architecte (ce qui relève déjà de l'interprétation puisqu'un bâtiment pourrait ne pas avoir d'architecte connu).

La définition d'une clef étrangère permet en principe d'éviter toute modification affectant un lien entre deux tables dans le cas d'une insertion, d'une modification ou d'une suppression (la requête est alors rejetée). Mais cela n'est effectif que si votre table utilise un moteur qui respecte l'intégrité référentielle (InnoDB).

Une mise à jour ou une suppression peut alors être répercutée en cascade sur les tables liées à l'aide des événements ON UPDATE et ON DELETE. La répercussion consiste à mettre la clef étrangère à NULL (SET NULL) ou à répercuter la même opération qu'à l'enregistrement auquel il est fait référence (CASCADE).

Exemple:

CREATE TABLE batiment (id INTEGER NOT NULL AUTO_INCREMENT,
                       nom VARCHAR (100) NOT NULL,
                       annee INTEGER DEFAULT 1900,
                       adresse VARCHAR (100),
                       id_Architecte INTEGER NOT NULL,
                       PRIMARY KEY (id),
                       FOREIGN KEY (id_architecte) REFERENCES architecte(id)
                         ON DELETE SET NULL);

ON DELETE SET NULL permet lors de la suppression d'un architecte auquel il est fait référence via id_architecte de mettre le contenu de ce champ id_architecte à NULL (mais on pourrait faire la même chose lors d'une mise à jour avec ON UPDATE).
La syntaxe ON DELETE CASCADE sur la clef étrangère entraînerait la suppression de l'enregistrement batiment correspondant lors de la destruction d'un architecte (ce qui en l'occurrence serait dangereux: si je supprime un architecte qui a fait 15 bâtiments, ces 15 bâtiments disparaîtraient en cascade!).

2.2.4 Contrainte d'unicité

La contraint d'unicité permet d'interdire la redondance d'information sur un ou plusieurs champs.
La syntaxe est la suivante:

UNIQUE (champ1[, champ2, ...])

Dans notre table batiment par exemple, il serait génant, dans le cadre d'une application visant à recenser les bâtiments d'architecture contemporaine, qu'il puisse y avoir deux bâtiments avec la même adresse et la même année.
On pourrait ajouter la contrainte:

UNIQUE (annee, adresse)

En revanche, si notre application a une visée historique, il faudra prendre en compte que deux bâtiments de notre base ont pu au fil des siècles avoir été construits à la même adresse.
Autrement dit une contrainte d'unicité portant sur la combinaison de ces deux champs invaliderait le fonctionnement de notre application.

2.2.5 Limiter les valeurs à un ensemble prédéfini

La dernière option CHECK permet de limiter le contenu d'un champ à une liste de valeurs prédéfinies.
On pourrait ainsi limiter le contenu autorisé dans les champs annee et adresse:

CREATE TABLE batiment (id INTEGER NOT NULL AUTO_INCREMENT,
                       nom VARCHAR (100) NOT NULL,
                       annee INTEGER CHECK (annee BETWEEN 1900 AND 1999) DEFAULT 1900,
                       adresse VARCHAR (100) CHECK (adresse IN ('rue Vandrezanne', 'boulevard Auriol', 'rue de Tolbiac')) NOT NULL,
                       id_Architecte INTEGER NOT NULL,
                       PRIMARY KEY (id),
                       FOREIGN KEY (id_architecte) REFERENCES architecte(id)
                         ON DELETE SET NULL);

Le champ annee ne peut alors comprendre que des valeurs comprises entre 1900 et 1999 (ce qui peut avoir son intérêt dans le cadre d'une base consacrée au XXe siècle).
Et le champ adresse ne peut alors comprendre que une des 3 adresses mentionnées (ce qui est évidement excessivement restrictif).

Attention: la contrainte CHECK est accepté par MySQL mais ne donne lieu à aucun contrôle.
MySQL dispose de types qui permettent le contrôle: ENUM et SET (qui ne font pas partie de la norme SQL).

Exemple:

adresse ENUM ('rue Vandrezanne', 'boulevard Auriol', 'rue de Tolbiac')

Si la valeur insérée dans le champs adresse n'appartient pas à l'ensemble défini par ENUM, MySQL affecte une chaîne vide.
Ce type de procédé manque cependant de souplesse. Pour bien faire, il faudrait créer une nouvelle table rue contenant tous les noms de rues et lier cette table à la table batiment à l'aide d'une clef étrangère id_rue.
Cela permettrait d'uniformiser l'orthographe des noms de rue et de faciliter l'ajout de nouveaux noms. Ainsi on pourrait imaginer une table rue ayant la structure suivante:

#type est là pour distinguer les 'rue de', 'rue de la', 'rue de l\''
#et permettre des requêtes rapides sur le champ 'nom' dont l'ordre alphabétique est important pour l'utilisateur
CREATE TABLE rue (id INTEGER NOT NULL AUTO_INCREMENT,
                  type VARCHAR (100) NOT NULL,
                  nom VARCHAR (200) NOT NULL);

auquel la table batiment ferait désormais référence de la manière suivante:

CREATE TABLE batiment (id INTEGER NOT NULL AUTO_INCREMENT,
                       nom VARCHAR (100) NOT NULL,
                       annee INTEGER CHECK (annee BETWEEN 1900 AND 1999) DEFAULT 1900,
                       no INTEGER,
                       id_rue INTEGER NOT NULL,
                       id_architecte INTEGER NOT NULL,
                       PRIMARY KEY (id),
                       FOREIGN KEY (id_rue) REFERENCES rue(id),
                       FOREIGN KEY (id_architecte) REFERENCES architecte(id)
                         ON DELETE SET NULL);

2.3 Résumé des syntaxes disponibles pour la création d'une table

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] nomTable (commandeCréation,...) [optionSelect]

commandeCréation:

nomChamp type [NOT NULL|NULL] [DEFAULT valeur] [AUTO_INCREMENT] [PRIMARY KEY]
ou PRIMARY KEY (nomChamp,...)
ou INDEX [nomIndex] (nomChamp,...)
ou UNIQUE [INDEX] [nomIndex] (nomChamp,...)
ou [CONSTRAINT contrainte] FOREIGN KEY nomIndex (nomChamp,...) [références]
ou CHECK (valeurs);

optionSelect:

[IGNORE|REPLACE] SELECT ... (requête SQL)

références:

REFERENCES nomTable(nomChamp)
[ON DELETE optionRéf]
[ON UPDATE optionRéf]

optRéf:

CASCADE|SET NULL|SET DEFAULT

L'option TEMPORARY indique que la table est créée pour la connexion courante.

L'option IF NOT EXISTS est utile lorsque vous stockez de nombreuses requêtes de création de table dans un fichier externe. Cela évite de supprimer ou remplacer de manière intempestive des tables qui existent déjà lorsque vous lancez votre fichier.

La commande INDEX permet de créer un index sur un ou une combinaison de champs.

3. Manipuler les tables

Attention: il existe des différences entre la syntaxe SQL standard et MySQL. Vous maîtrisez bien SQL?
Alors jetez un coup d'oeil à ce qui suit: MySQL ne respecte malheureusement pas plus la norme SQL que ses concurrents.

3.1 Copier des tables

Il est possible de créer une table à partir d'une autre table en utilisant la syntaxe suivante:

CREATE TABLE copie_batiment
AS SELECT * FROM batiment;

copie_batiment est ici la copie conforme de batiment: la structure et le contenu de batiment ont été copiés.
En revanche les contraintes de clef primaire et étrangère ne sont pas reproduites.

Mais on peut procéder de manière plus sélective en précisant les champs que l'on veut copier.

Exemple:

CREATE TABLE copie_batiment
AS SELECT nom FROM batiment;

Dans ce cas on copie dans la nouvelle table copie_batiment uniquement le champ nom de la table batiment.

On peut pousser encore plus loin et préciser sur quel critère se fait la copie:

Exemple:

CREATE TABLE copie_batiment (nv_nom)
AS SELECT nom
FROM batiment
WHERE nom LIKE 'L%';

Dans ce cas on copie dans la nouvelle table copie_batiment uniquement le champ nom des batiments dont le nom commence par 'L'.

3.2 Modifier des tables

Lorsqu'on veut modifier une table existante, on recourt au mot-clef ALTER.
La syntaxe générale est la suivante :

ALTER TABLE nomTable altérations

3.3 Renommer des tables

Pour renommer une table la syntaxe est la suivante:

ALTER TABLE batiment
RENAME [TO|AS] nouveau_batiment;

Pour ajouter une colonne, on utilise le mot-clef ADD suivi du nom et du type de la colonne:

ALTER TABLE batiment
ADD no VARCHAR(100);

On ajoute un champ no à la table batiment.

On peut également modifier le type d'un champ existant avec MODIFY:

ALTER TABLE batiment
MODIFY no VARCHAR(200);

Attention: MySQL se charge de faire la conversion de l'ancien vers le nouveau type de donnée, pour le meilleur et pour le pire. Il existe des tables de conversion mais en la matière ne faites jamais confiance à votre mémoire: faites toujours un test sur une copie de votre table avant de massacrer l'original.

Enfin vous pouvez supprimer un champ à l'aide de la commande DROP.

ALTER TABLE batiment
DROP id;

Attention: la suppression d'un champ entraine la perte de toutes les données qu'il contient.

3.4 Synthèse des commandes de modifications des tables

ALTER TABLE nomTable altérations

altérations:
ADD [COLUMN] commandeCréation [FIRST|AFTER nomChamp]
ou ADD INDEX [nomIndex] (nomChamp,...)
ou ADD PRIMARY KEY (nomChamp,...)
ou ADD UNIQUE [nomIndex] (nomChamp,...)
ou ALTER [COLUMN] nomChamp
ou CHANGE [COLUMN] ancienNomChamp commandeCréation
ou MODIFY [COLUMN] commandeCréation
ou DROP [COLUMN] nomChamp
ou DROP PRIMARY KEY
ou DROP INDEX nomIndex
ou RENAME [AS|TO] nomTable

3.5 Supprimer des tables

Il est extrêmement facile de supprimer une table en utilisant la syntaxe suivante (attention à ne pas faire d'erreur):

DROP TABLE [IF EXISTS] batiment;

Distinguez bien la syntaxe qui précède, qui supprime la table dans son ensemble de celle qui suit qui se contente de supprimer la totalité des enregistrements de la table mais conserve sa structure intacte:

DELETE FROM batiment [WHERE conditions];

3.6 Remplir une table depuis un fichier externe: LOAD DATA

Pour charger le contenu d'un fichier texte à l'intérieur d'une table, vous disposez de la syntaxe suivante:

LOAD DATA [LOW PRIORITY] [LOCAL] INFILE 'nomFichier' [REPLACE|IGNORE]
INTO TABLE nomTable
[FIELDS
[TERMINATED BY caractère]
[OPTIONNALY] [ENCLOSED BY caractère]
[ESCAPED BY caractère]]
[LINES TERMINATED BY caractère]
[IGNORE entier LINES]
[(nomColonne,...)]

4. Requêtes MySQL

Les exemples qui suivent portent sur une base de donnée de gestion de syndic constituée de 4 tables selon le schéma suivant:

Cette base répond aux problématiques suivantes:
  - un appartement peut être possédé en indivision: c'est-à-dire qu'il peut être la propriété de plusieurs personnes, chacune de ces personnes possédant une quote-part de l'appartement.
  - une personne peut posséder plusieurs appartements
  - un appartement a un locataire et un seul (celui qui paye le loyer)
  - il peut y avoir plusieurs appartements par immeuble

La relation entre la table PERSONNE et la table APPARTEMENT peut être de deux natures:
  - soit une relation qui indique la possession et qui passe par la table intermédiaire POSSEDE qui résulte d'une relation de plusieurs à plusieurs (un appartement peut être possédé par plusieurs personnes et une personne peut posséder plusieurs appartements),
  - soit une relation de location

La relation entre la table APPARTEMENT et la table IMMEUBLE permet d'indiquer dans quel immeuble se trouve chaque appartement (en évitant la redondance d'information).

Vous pouvez charger ces 4 tables et quelques données à l'aide du fichier suivant: immeuble.sql.

De manière générale, lorsque vous faites des requêtes sur une base de donnée, il est bon d'avoir sous les yeux le schéma de cette base. N'hésitez pas à vous crayonner le schéma de la base de gestion de syndic sur une feuille de papier pour suivre correctement les exemples.

4.1 Structure d'une sélection: les trois étapes

La syntaxe générale des requêtes de sélection est la suivante:

SELECT liste_expressions
FROM source_donnees
[WHERE liste_conditions]

Ainsi la syntaxe suivante renvoie tous les champs de la table personne

SELECT id, nom, prenom, profession, id_appartement
FROM personne

Mais on aurait également pu utiliser la syntaxe suivante où l'étoile est une syntaxe raccourcie qui désigne l'ensemble des champs:

SELECT *
FROM personne

Ne vous affolez pas si la syntaxe des requêtes vous parait complexe: dans cette partie il s'agit de saisir la structure générale des requêtes, nous revenons sur le détail de la syntaxe dans les chapitres suivants (4.2 et 4.3).

Notez que la machine ne travaille pas du tout dans l'ordre énoncé. Elle commence par déterminer les sources mentionnées par FROM, puis elle sélectionne les données conformes aux conditions énoncées dans le WHERE et s'occupe finalement d'afficher les champs recensés dans le SELECT.

Concrétement, c'est comme si vos données passaient au travers de trois tamis avant d'être affichées:
  - le premier tamis consiste à sélectionner les tables dont vous avez besoin (et vous avez rarement besoin de toutes vos tables)
  - le second tamis sélectionne les données dans ces tables selon certains critères logiques
  - le troisième tamis sélectionne les données à afficher dans le résultat final

4.1.1 L'espace de recherche: clause FROM (ou premier tamis)

Cet espace peut être une table basée, c'est à dire immédiatement disponible dans la base ou une table calculée, c'est-à-dire une table qui est elle-même le résultat d'une sélection comme dans l'exemple qui suit:

SELECT *
FROM (SELECT nom, adresse FROM IMMEUBLE WHERE id=1) AS KOUDALOU;

Si on a souvent recours à une table calculée, il peut être judicieux de créer ce qu'on appelle une vue (disponible à partir de MySQL 5.0).

CREATE VIEW Koudalou AS
SELECT nom, adresse
FROM immeuble WHERE id=1;

SELECT nom, adresse
FROM Koudalou;

Enfin on peut vouloir sélectionner le contenu de plusieurs tables.
Par défaut, la requête qui suit renvoie toutes les associations possibles entre les lignes de la table immeuble et les lignes de la table appartement.
C'est ce qu'on appelle le produit cartésien de deux tables et c'est rarement tel quel que vous en aurez besoin.

SELECT *
FROM immeuble, appartement;

qui peut s'écrire également

SELECT *
FROM immeuble CROSS JOIN appartement;

La plupart du temps vous chercherez à réaliser des jointures, c'est-à-dire un produit cartésien restreint par une condition qui peut s'écrire de la manière suivante:

SELECT *
FROM immeuble JOIN appartement
ON (immeuble.id=appartement.id_immeuble);

qui ne renvoie que les appartements référencés dans la table immeuble.

Pas de panique devant cette syntaxe que vous n'avez probablement jamais vu: il faudra vous y habituer, SQL permet souvent d'écrire une même opération de plusieurs façons (dans le cas présent vous auriez préféré un WHERE, ça tombe bien, on y vient justement).

4.1.2 La condition: la clause WHERE (second tamis)

La clause WHERE permet d'exprimer des conditions portant sur les lignes de la ou des table(s) définie(s) par la clause FROM.
Ces conditions prennent la forme classique des tests combinés par les opérateurs logiques classiques AND, OR, NOT. Les régles de priorité s'expriment entre parenthèses pour éviter toute ambiguité.

Ainsi l'exemple de jointure qui précède pourrait également s'écrire avec une clause WHERE de la manière suivante:

SELECT *
FROM immeuble, appartement
WHERE immeuble.id=appartement.id_immeuble;

Les tests les plus traditionnels sont possibles:

Afficher les appartements de plus de 50 m2:

SELECT *
FROM appartement
WHERE surface > 50;

Afficher les appartements de plus de 50 m2 et situés au-dessus du 3ème étage:

SELECT *
FROM appartement
WHERE surface > 50 AND etage > 3;

Afficher les appartements de plus de 50 m2 et situés au-dessus du 3ème étage ou les appartements situés dans l'immeuble n°2:

SELECT *
FROM appartement
WHERE (surface > 50 AND etage > 3) OR id_immeuble=2;

Afficher les personnes qui habitent en-dessous du 3ème étage:

SELECT *
FROM personne
WHERE id_appartement IN (SELECT id FROM appartement WHERE etage < 3);

Les requêtes imbriquées comme cette dernière sont à éviter. Il y a en général des solutions plus élégantes et surtout plus lisibles.

4.1.3 Les expressions: la clause SELECT (ou troisième tamis)

Une fois sélectionnées les lignes du FROM qui satisfont la clause WHERE, on affiche le résultat final d'une certaine manière à l'aide de la clause SELECT.
Une expression peut être un nom d'attribut, une constante, ou le résultat d'un calcul plus complexe.

SELECT surface, etage, 18 AS 'euros/m2'
FROM appartement

La commande AS permet ici de donner un nom à la colonne qui accueillera la constante 18 qui sera répété pour toutes les lignes retournées.

Mais on pourrait également inclure une expression calculée comme le loyer, en se basant sur la surface et sur l'étage.

SELECT no, surface, etage,
(surface,*18)*(1+(0.03*etage)) AS loyer
FROM appartement

La documentation MySL précise toutes les fonctions et opérateurs disponibles.
Vous pouvez d'ailleurs utiliser MySQL uniquement pour afficher le résultat d'une opération en omettant la clause FROM comme dans l'exemple qui suit:

SELECT 2 + 2;

SELECT ROUND(1.235,2);

Attention: il ne doit pas y avoir d'espace entre la fonction MySQL et la première parenthèse ouvrante qui suit.

4.2 Recherche avec MySQL

La rechercherche la plus simple est, nous l'avons vue, celle qui renvoie tous les champs d'une table: elle ne nécessite pas de clause WHERE et utilise l'étoile '*' pour désigner tous les champs.

SELECT *
FROM appartement;

affiche tous les champs de la table appartement.

Construction d'expression

Si on indique explicitement les champs sans utiliser l'étoile '*', ceux-ci déterminent le nombre de champs à afficher. Par défaut le nom de chaque colonne est celui du champ mais on peut modifier ce nom à l'aide de l'alias AS.
La fonction MySQL CONCAT() vous permet également de présenter plusieurs champs au sein d'une seule colonne comme le montre l'exemple suivant:

SELECT CONCAT(nom, ' (', prenom, ')') AS 'Nom et prénom'
FROM personne;

L'exemple précédent retourne un résultat NULL. Pour éviter ce type d'affichage, MySQL fournit la fonction IFNULL() qui permet d'attribuer une valeur par défaut aux valeurs NULL comme dans l'exemple suivant:

SELECT IFNULL(prenom, 'rien') AS 'Prenom'
FROM personne;

Vous pouvez également effectuer des calculs dans la clause SELECT comme l'exemple suivant qui calcule le loyer de chaque appartement sur la base de 18 euros du mètre carré.

SELECT id AS 'Appartement', CONCAT(surface*18, ' euros') AS 'Loyer'
FROM appartement;

La clause WHERE

La clause WHERE utilise les opérateurs de comparaison standards à savoir: <, <=, =, !=, >=, > ainsi que les opérateurs logiques: AND, OR, NOT et IN qui teste l'appartenance à un ensemble comme dans l'exemple qui suit:

SELECT *
FROM personne
WHERE profession='Acteur' OR profession='Rentier';

qui peut s'écrire également avec IN:

SELECT *
FROM personne
WHERE profession IN ('Acteur','Rentier');

Pour les comparaisons portant sur des chaînes de caractère, vous disposez de l'opérateur LIKE, qui fait partie de la norme SQL et qui fonctionne avec le '%' (qui remplace n'importe quelle chaine) et le '_' (qui remplace n'importe quel caractère).

SELECT *
FROM personne
WHERE nom LIKE '%m_t';

MySQL fournit également l'opérateur REGEXEP qui permet d'utiliser les expressions régulières. Attention: REGEXP n'est pas un standard SQL.

SELECT *
FROM personne
WHERE nom REGEXP 'Ramut';

Attention: la comparaison de chaîne, par défaut, ne tient pas compte de la casse, sauf si vous le précisez avec l'opérateur BINARY.

SELECT *
FROM personne
WHERE nom LIKE BINARY 'r%';

ne retourne rien puisque la première lettre des noms est toujours en majuscule.

Gérer les valeurs nulles

D'une valeur nulle on ne peut strictement rien faire: dans le cas d'une comparaison, la présence d'un NULL ne renvoie ni TRUE ni FALSE mais UNKNOWN (une valeur booléenne intermédiaire).

Ainsi, les requêtes complémentaires suivantes (l'une avec LIKE, l'autre avec NOT LIKE) ne renvoient pas le locataire Ross dont le champ prenom a pour valeur NULL:

SELECT *
FROM personne
WHERE prenom LIKE '%';

SELECT *
FROM personne
WHERE prenom NOT LIKE '%';

Résultat des courses, si on veut sélectionner des champs qui ont une valeur nulle, on recoure au test IS NULL:

SELECT *
FROM personne
WHERE prenom LIKE '%' OR prenom IS NULL;

Tris

Lorsque vous faites une requête globale, MySQL ne vérifie pas si certaines informations sont redondantes:

SELECT surface
FROM appartement;

l'expression DISTINCT permet d'éliminer les doublons du résultat:

SELECT DISTINCT surface
FROM appartement;

Méfiez-vous du DISTINCT dans le cas de tables importantes: cette fonction utilise beaucoup de ressource et peut ralentir notablement vos requêtes.

MySQL permet également de trier par ordre croissant (ASC, tri par défaut) ou décroissant (DESC) les résultats retournés pour un champ à l'aide de la syntaxe ORDER BY qui vient se placer à la fin de la requête:

SELECT *
FROM appartement
ORDER BY surface, etage;

ou

SELECT *
FROM appartement
ORDER BY surface DESC, etage ASC;

La clause LIMIT

Attention, la clause LIMIT, qui permet de spécifier le nombre de lignes retournées par la requête est une spécificité MySQL que vous ne retrouverez pas chez tous les autres SGBD.

LIMIT premiereLigne, nbLigne

ou

LIMIT nbLigne [OFFSET premiereLigne]

4.3 Les jointures

La jointure consiste à restreindre le produit cartésien de plusieurs tables en ne conservant que les associations qui présentent un intérêt pour la requête.
Il y a beaucoup de manière de faire des jointures. Nous nous limitons ici aux plus conventionnelles.

4.3.1 Syntaxe classique pour établir une jointure

Pour éviter le problème d'identification des champs homonymes (ceux qui portent le même nom dans des tables différentes), on créé un nom raccourci pour chaque table au niveau du FROM (on appelle cela un alias) et on le concatène au nom du champ avec un '.' de la manière suivante:

SELECT p.id, nom, prenom, id_appart, a.id, surface, etage
FROM personne p, appartement a
WHERE nom='DUPONT' AND prenom='William'
AND a.id=id_appartement;

Nous avons pris l'exemple du cas le plus général: celui où la jointure se fait entre clef primaire et clef étrangère. Dans cet exemple, il était nécessaire de préciser de quelles tables relevaient les champs id homonymes.

Mais d'autres cas peuvent se présenter. Si par exemple vous vouliez connaître les appartements d'un même immeuble qui ont une même surface, vous seriez amené à comparer les enregistrements de la table appartement avec eux-mêmes. Vous êtes alors conduit à créer deux alias de la même table pour résoudre le problème:

SELECT a1.id, a1.surface, a1.etage, a2.id, a2.surface, a2.etage
FROM appartement a1, appartement a2
WHERE a1.id_immeuble=a2.id_immeuble
AND a1.surface=a2.surface
AND a1.id!=a2.id;

Essayez de résoudre la série d'exercices qui suit sur les jointures. Faites-là et refaîtes-là jusqu'à ce que le raisonnement qui conduit au résultat soit fluide pour vous. N'hésitez pas à vous faire un petit schéma sur papier de l'organisation de vos tables:

Qui habite un appartement de plus de 200m2?

SELECT nom, prenom
FROM personne, appartement a
WHERE surface > 200
AND id_appartement=a.id;

Qui habite l'immeuble Barabas?

SELECT p.nom, prenom
FROM personne p, appartement a, immeuble i
WHERE i.nom='Barabas'
AND a.id=p.id_appartement
AND a.id_immeuble=i.id;

Qui habite un appartement qu'il possède et avec quelle quote-part?

SELECT p.nom, prenom, quote
FROM personne p, possede P2, appartement a
WHERE p.id=p2.id_personne
AND p2.id_appartement=a.id
AND p.id_appartement=a.id;

De quels appartements Alice Black est-elle propriétaire et dans quel immeuble?

SELECT i.nom, no, etage, surface
FROM personne p, possede p2, appartement a, immeuble i
WHERE p.id=p2.id_personne /*Jointure personne possede*/
AND p2.id_appartement = a.id /*Jointure possede appartement*/
AND a.id_immeuble=i.id /*Jointure appartement immeuble*/
AND p.nom='Black' AND p.prenom='Alice';

4.3.2 Syntaxe avec les tables calculées dans le FROM

Même si je vous déconseille de l'utiliser, il existe un autre moyen d'exprimer les jointures que vous devez être au moins capable de lire, celui des tables calculées placées dans le FROM.

Les exemples précédents pourraient s'écrire ainsi:

Quel est l'appartement de Dupont?

SELECT no, surface, etage
FROM appartement, (SELECT id_appartement FROM personne WHERE nom='Dupont') AS Dupont
WHERE id=id_appartement;

Attention: il faut toujours donner un alias à une table calculée (même si on ne s'en sert pas).

Qui habite un appartement de plus de 200m2?

SELECT nom, prenom
FROM personne AS p, (SELECT id_appartement FROM appartement WHERE surface>200) AS a
WHERE p.id=a.id_appartement;

Qui habite le Barabas?

SELECT nom, prenom
FROM personne AS p, (SELECT appartement.id FROM appartement, immeuble WHERE id_immeuble=immeuble.id AND nom='Barabas') AS a
WHERE p.id_appartement=a.id;

4.3.3 Autres syntaxes pour les opérateurs de jointure

La virgule qui sépare deux tables dans un FROM exprime un produit cartésien: toutes les combinaisons de lignes sont considérées puis soumises aux conditions du WHERE.
MySQL fournit d'autres syntaxes pour exprimer la jointure sous la forme:

table1 opérateur table2 [condition]

ou opérateur appartient à la liste suivante:

- CROSS JOIN qui est le synonyme de la virgule ','
- JOIN est une jointure accompagnée de conditions.
- LEFT [OUTER] JOIN et RIGHT [OUTER] JOIN pour les jointures externes.
- NATURAL [LEFT|RIGHT[OUTER]] JOIN pour les jointures 'naturelles'.

Si on reprend les exemples précédents avec cette syntaxe, cela nous donne:

Quel est l'appartement de Dupont?

SELECT no, surface, etage
FROM personne JOIN appartement ON (appartement.id=id_appartement)
WHERE nom='Dupont';

Qui habite un appartement de plus de 200m2?

SELECT nom, prenom
FROM personne AS p JOIN appartement AS a
ON (a.id=p.id_appartement AND surface > 200);

Quoique cela fonctionne, MySQL déconseille cette pratique qui consiste à inclure une condition de sélection dans la jointure. En théorie la jointure est là uniquement pour faire les comparaisons de clefs.

4.3.4 Les jointures externes

Les jointures externes ne trouvent pas d'équivalent dans les formules qui précédent. Elles permettent en effet de gérer les cas où toutes les lignes d'une table n'ont pas de correspondant dans l'autre table.

Si par exemple je fais une requête qui affiche tous les appartements avec leurs occupants, je n'obtiendrais que les appartements qui ont un occupant mais pas les appartements vides (or il y en a un).
Dans ce cas je détermine une table directrice (celle de gauche par convention) dont toutes les lignes, même celles qui ne trouvent pas de correspondance dans l'autre table seront prises en compte.
La table de droite est dite optionnelle.

Afficher tous les appartements avec leur locataire éventuel s'écrit donc de la manière suivante:

SELECT id_immeuble, no, etage, surface, nom, prenom
FROM appartement a LEFT OUTER JOIN personne p ON (p.id_appartement=a.id);

4.3.5 Les opérations ensemblistes

La norme SQL ANSI comprend des opérations qui considèrent les tables comme des ensembles entre lesquels on peut effectuer des intersections avec les mot-clefs UNION, INTERSECT et EXCEPT. Chaque opérateur s'applique à des tables de schéma identique (même nombre de champs, mêmes noms, mêmes types). MySQL ne dispose que de l'opérateur UNION (les deux autres peuvent s'exprimer d'une autre manière).

Il y a en réalité peu de cas qui nécessitent l'usage de l'opérateur UNION. Ainsi l'exemple suivant:

SELECT nom
FROM immeuble
UNION
SELECT nom
FROM personne;

retourne une liste comprenant les noms des immeubles et des personnes qui sont dans la base. Mais pourquoi faire?

4.4 Agrégation

On appelle agrégats des regroupements de lignes en fonction de certains champs.
Ce regroupement est spécifié par la clause GROUP BY.

Si on ne mentionne aucun groupe, SQL considère par défaut qu'il s'agit de la table tout entière (c'est un groupe sommaire mais c'est un groupe).
Les fonctions d'agrégation s'appliquent alors à la table tout entière comme le montre l'exemple suivant:

SELECT COUNT(*), COUNT(prenom), count(nom)
FROM personne;

La clause GROUP BY permet de spécifier sur quel champ doit se faire le regroupement.
Dès lors, tous les enregistrements possédant la même valeur pour ce champ sont regroupés.

Ainsi, on peut obtenir la somme des quote-parts pour chaque appartement:

SELECT id_appartement, SUM(quote)
FROM possede
GROUP BY id_appartement;

Dans un premier temps, MySQL regroupe les enregistrements qui ont le même id_appartement dans la table possede.
Puis pour chacun de ces regroupements on affiche l'id_appartement et la somme des quote parts des différents enregistrements regroupés.

Si vous avez bien saisi le principe, vous devez comprendre que la requête précédente doit retourner 100 pour chaque appartement (puisqu'il s'agit de la somme des parts de chacun des propriétaires de l'appartement).

Il est possible également d'exprimer des conditions sur ces valeurs agrégées pour ne conserver qu'un ou plusieurs des groupes constitués. Ces conditions portent sur des groupes de lignes et non sur des lignes prises une à une, c'est pourquoi on emploie ici la clause HAVING plutôt que WHERE.

Par exemple on peut séléctionner les appartements pour lesquels on connaît au moins deux copropriétaires:

SELECT id_appartement, COUNT(*)
FROM possede
GROUP BY id_appartement
HAVING COUNT(*) >=2;

Ici la clause HAVING porte sur le résultat de la fonction d'agrégation GROUP BY.

On peut ainsi calculer la surface possédée par chaque propriétaire dans l'immeuble 1 en fonction de la quote-part qu'ils ont dans chaque appartement possédé. On regroupe les appartements par propriétaire et on trie sur la surface possédée:

SELECT nom, prenom, SUM(quote*surface/100) AS 'Surface possedee'
FROM personne p1, possede p2, appartement a WHERE id_immeuble=1
AND p1.id=p2.id_personne
AND a.id=p2.id_appartement
GROUP BY p1.id
ORDER BY 'Surface possedee' DESC;

Vous remarquez au passage un autre usage possible d'un alias portant sur un champ calculé.

4.5 Requête de mises à jour

4.4.1 Insertion

L'insertion s'effectue avec la commande INSERT.

Si l'on ne précise pas les champs dans lesquels se fait l'insertion, MySQL suppose que la requête concerne tous les champs et que les valeurs seront passées dans l'ordre adéquat.
Nb: Si un des champs est auto-incrémenté (option AUTO_INCREMENT), il est inutile de le passer en paramètre.

Insérer des données dans la table immeuble (où il n'y a pas de champ auto-incrémenté) s'écrit donc:

INSERT INTO immeuble
VALUES (1, 'Koudalou', '3, rue Blanche');

Si l'insertion ne concerne que certains champs, il faut alors les énumérer comme dans l'exemple suivant:

INSERT INTO immeuble(id, nom, adresse)
VALUES (1, 'Koudalou', '3, rue Blanche');

4.4.2 Destruction

La destruction s'effectue avec clause DELETE et selon la syntaxe suivante:

DELETE FROM table
WHERE condition;

4.4.3 Modification

La modification s'effectue avec la clause UPDATE et selon la syntaxe suivante:

UPDATE table SET A1=v1, A2=v2, ...
WHERE condition;

ou An sont les noms de champ et vn les nouvelles valeurs.


Attention: tout ce qui suit n'est disponible qu'avec la version 5 de Mysql.


 

4.5 Les vues

Lorsque vous faites une requête MySQL, le résultat qui s'affiche est une table.
Les vues ne sont rien d'autres que des requêtes dont on a stocké le résultat dans des tables 'virtuelles'.

La création d'une vue n'induit en réalité aucun stockage (les informations qu'elle extrait sont toujours dans les tables de votre base). Elle permet seulement d'obtenir facilement une représentation différentes des données contenues dans les tables de votre base.

Les vues permettent donc:
  - une plus grande lisibilité de votre base à l'aide de ces requêtes prédéfinies
  - une meilleure sécurisation des données car vous pouvez restreindre l'interrogation de votre base uniquement aux requêtes que vous avez créer. Autrement dit l'utilisateur lambda de pourra pas faire de recoupement d'information que vous n'ayez autorisé.

4.5.1 Créer et utiliser une vue

Une vue se construit et s'utilise comme une table à ceci près qu'elle est une table dynamique puisqu'elle repose sur d'autres tables.
Sa syntaxe n'a donc rien de surprenant par rapport à ce que vous avez déjà vu:

CREATE [OR REPLACE] VIEW nomVue [(listeAttibuts)]
AS requete
[WITH CHECK OPTION]

Pour créer une vue affichant le nom, l'adresse et le nombre d'appartements gérés dans l'immeuble Koudalou, on peut écrire:

CREATE OR REPLACE VIEW koudalou AS
SELECT nom, adresse, COUNT(*) AS nbAppartement
FROM immeuble i, appartement a
WHERE i.id=a.id_immeuble
AND i.id=1
GROUP BY i.id;

On appelle ensuite cette vue comme on appelle une table:

SELECT *
FROM koudalou;

Désormais cette vue apparait au même titre que les tables de votre bases lorsque vous faites un SHOW TABLES;.

La seule limite des vues par rapport aux tables est qu'on ne peut insérer des données que de manière extrêmement limité: les vues qui peuvent servir à l'insertion ne doivent porter que sur une table où les colonnes non référencées dans la vue doivent pouvoir être mises à NULL ou disposer d'une valeur par défaut.

Autrement dit ces vues d'insertion n'ont d'utilité que sur le plan de la sécurité (si vous décidez de limiter l'accès à votre base aux vues que vous avez créées).

Plan du site  |  Mentions légales  |  Crédits  |  Aide