Les possibilités de la clause WHERE .............................................................................................................
1
Suppression et modification de données ......................................................................................................... 5
Index ............................................................................................................................................................. 7
1
Les possibilités de la clause WHERE
La clause « WHERE » offre les possibilités suivantes :
• la comparaison avec une valeur incomplète (chercher les animaux dont le nom commence
par une certaine lettre par exemple) ;
• la comparaison avec un intervalle de valeurs (entre 2 et 5 par exemple) ;
• la comparaison avec un ensemble de valeurs (comparaison avec 5, 6, 10 ou 12, par exemple).
Recherche approximative
Deux jokers existent pour LIKE :
• '%' : qui représente n'importe quelle chaîne de caractères, quelle que soit sa longueur (y compris
une chaîne de longueur 0) ;
• '_' : qui représente un seul caractère.
On peut citer 8 Opérateurs de comparaisons :
= égal
< inférieur
<= inférieur ou égal
> supérieur
>= supérieur ou égal
<> ou != différent
<=> égal (valable pour NULL aussi)
Quelques exemples :
• 'b%' cherchera toutes les chaînes de caractères commençant par 'b'.
• 'b_' cherchera toutes les chaînes de caractères contenant deux lettres dont la première est 'b'.
• '%ch%ne' cherchera toutes les chaînes de caractères contenant 'ch' et finissant
par 'ne'.
• 'p_rl_' cherchera toutes les chaînes de caractères commençant par un "p" suivi d'un caractère, puis
de "rl" et enfin se terminant par un caractère.
Rechercher '%' ou '_'
Pour utiliser % ou _ en tant que joker et non en tant que caractère spécial il faut mettre le caractère
d'échappement.
Sensibilité à la casse
LIKE 'chaîne de caractères' n'est pas sensible à la casse (donc aux différences majuscules- minuscules),
cela est dû à l’interclassement de l’encodage UTF-8 qui n’est pas sensible à la case.
Pour faire une recherche sensible à la case on peut définir la chaîne de caractère comme étant une chaîne de
type Binaire.
2
Exemple :
SELECT *
FROM nom_table
WHERE nom LIKE '%Ma%'; -- insensible à la casse
SELECT *
FROM nom_table
WHERE nom LIKE BINARY '%Ma%'; -- sensible à la casse
Recherche dans les numériques
LIKE peut être utilisé sur une colonne de type numérique.
Exemple :
SELECT *
FROM nom_table
WHERE id LIKE '1%';
Recherche dans un intervalle
Il est possible de faire une recherche sur un intervalle à l'aide uniquement des opérateurs de comparaison.
Exemple :
SELECT *
FROM nom_table
WHERE date_naissance <= '2005-06-26'
AND date_naissance >= '2003-10-08';
3
Set de critères
IN est un opérateur qui permet de faire des recherches parmi une liste de valeurs.
Exemple : Récupérons les élèves avec les noms suivants : nom1, nom2, nom3, nom4.
Code: SQL
SELECT *
FROM nom_table
WHERE nom IN (‘nom1’, 'nom2', 'nom3', 'nom4');
En résumé
L'opérateur LIKE permet de faire des recherches approximatives, grâce aux deux caractères "joker" :
'%' (qui représente 0 ou plusieurs caractères) et '_' (qui représente 0 ou 1 caractère).
L'opérateur BETWEEN permet de faire une recherche sur un intervalle. WHERE colonne BETWEEN a
AND b étant équivalent à WHERE colonne >= a AND colonne <= b.
Enfin, l'opérateur IN permet de faire une recherche sur une liste de valeurs.
4
Suppression et modification de données
Sauvegarde d'une base de données
Il est bien utile de pouvoir sauvegarder facilement une base de données, et très important de la
sauvegarder régulièrement. Une mauvaise manipulation et toutes les données peuvent disparaître.
MySQL dispose donc d'un outil spécialement dédié à la sauvegarde des données sous forme de fichiers
texte : mysqldump. Qui est une fonction de sauvegarde et s’utilise à partir de la console. Il ne doit pas y
avoir de connexion avec le serveur Mysql lors de la sauvegarde. Pour sauvegarder une base de données
on exécute la commande suivante :
Code: SQL
mysqldump -u user -p --opt nom_de_la_base > sauvegarde.sql
• mysqldump : il s'agit du client permettant de sauvegarder les bases.
• --opt : c'est une option de mysqldump qui lance la commande avec une série de paramètres
qui font que la commande s'effectue très rapidement.
• nom_de_la_base : On indique ici le nom de la base que l'on veut sauvegarder.
• > sauvegarde.sql : le signe > indique que l'on va donner la destination de ce qui va être généré par
la commande sauvegarde.sql. Il s'agit du nom du fichier qui contiendra la sauvegarde de la base.
On peut l'appeler différemment.
La commande pour créer la base de données elle-même n’est pas sauvegarder, par conséquent on doit
récréer la base de données puis taper la commande suivante pour restaurer la base de données :
Code: SQL
mysql -u user -p nom_base < chemin_fichier_de_sauvegarde.sql
Ou, directement à partir de MySQL :
Code: SQL
USE nom_base;
source fichier_de_sauvegarde.sql;
Suppression
On utilise la commande DELETE pour supprimer des données, et la clause WHERE s’utilise de la même
manière avec DELETE qu’avec la commande SELECT.
Exemple :
Code: SQL
DELETE FROM nom_table
WHERE critères;
5
Code: SQL
DELETE FROM 2II
WHERE id = 1;
Modification
La modification des données se fait grâce à la commande UPDATE, dont la syntaxe est la suivante :
Code: SQL
UPDATE nom_table
SET col1 = val1 [, col2 = val2, ...]
[WHERE ...];
Exemple:
Code: SQL
UPDATE 2II
SET sexe='M', nom='Diarra'
WHERE id=21;
En résumé
Le client mysqldump est un programme qui permet de sauvegarder facilement les bases de données. La
commande DELETE permet de supprimer des données.
La commande UPDATE permet de modifier des données.
6
Chapitre 2 : Index, jointures et sous-requêtes
Index, jointures et sous-requêtes
Index
Un index est une structure qui reprend la liste ordonnée des valeurs auxquelles il se rapporte. Les index
sont utilisés pour accélérer les requêtes (notamment les requêtes impliquant plusieurs tables, ou les
requêtes de recherche), et sont indispensables à la création de clés, étrangères et primaires, qui
permettent de garantir l'intégrité des données de la base.
Lorsque vous créez un index sur une table, MySQL stocke cet index sous forme d'une structure
particulière, contenant les valeurs des colonnes impliquées dans l'index. Cette structure stocke les valeurs
triées et permet d'accéder à chacune de manière efficace et rapide.
Intérêt des index
Les index permettent aussi d'assurer l'intégrité des données de la base. Pour cela, il existe plusieurs types
d'index différents, et deux types de "clés".
Désavantages
• Ils prennent de la place en mémoire.
• Ils ralentissent les requêtes d'insertion, modification et suppression, puisqu'à chaque fois, il faut
remettre l'index à jour en plus de la table.
Les différents types d'index
Il existe trois types d'index qui ont des propriétés particulières. Les index UNIQUE, les index FULLTEXT, et
enfin les index SPATIAL.
Index UNIQUE
Avoir un index UNIQUE sur une colonne (ou plusieurs) permet de s'assurer que jamais vous n’insérerez
deux fois la même valeur (ou combinaison de valeurs) dans la table.
Index FULLTEXT
Un index FULLTEXT est utilisé pour faire des recherches de manière puissante et rapide sur un texte. On
n'utilise donc ce type d'index que sur les colonnes de type CHAR, VARCHAR ou TEXT.
Création et suppression des index
Les index sont représentés par le mot-clé INDEX (surprise ! ) ou KEY et peuvent être créés de deux
manières :
• Soit directement lors de la création de la table ;
• Soit en les ajoutant par la suite.
Ajout des index lors de la création de la table
Ici aussi, deux possibilités : vous pouvez préciser dans la description de la colonne qu'il s'agit d'un index, ou
lister les index par la suite.
Seuls les index "classiques" et uniques peuvent être créés directement dans la description de la colonne
la description de la colonne se rapporte à l'endroit où vous indiquez le type de données :
7
CREATE TABLE nom_table (
colonne1 INT KEY,
-- Crée un index simple sur
colonne1
colonne2 VARCHAR(40) UNIQUE,
-- Crée un index unique sur
colonne2
);
• Avec cette syntaxe, seul le mot KEY peut être utilisé pour définir un index simple. Ailleurs,
vous pourrez utiliser KEY ou INDEX.
• Pour définir un index UNIQUE de cette manière, on n'utilise que le mot-
clé UNIQUE, sans le faire précéder de INDEX ou de KEY.
• Il n'est pas possible de définir des index composites (sur plusieurs colonnes) de cette manière.
• Il n'est pas non plus possible de créer un index sur une partie de la colonne (les x
premiers caractères).
Liste d'index
L'autre possibilité est d'ajouter les index à la suite des colonnes, en séparant chaque élément par une virgule :
CREATE TABLE nom_table (
colonne1 description_colonne1,
[colonne2 description_colonne2,
colonne3 description_colonne3,
...,]
[PRIMARY KEY (colonne_clé_primaire)],
[INDEX [nom_index] (colonne1_index [, colonne2_index, ...]]
[ENGINE=moteur];
Pour ajouter des index UNIQUE ou FULLTEXT, c'est le même principe :
8
CREATE TABLE nom_table (
colonne1 INT NOT NULL,
colonne2 VARCHAR(40),
colonne3 TEXT,
UNIQUE [INDEX] ind_uni_col2 (colonne2),
-- Crée un index
UNIQUE sur la colonne2, INDEX est facultatif
FULLTEXT [INDEX] ind_full_col3 (colonne3) -- Crée un index
FULLTEXT sur la colonne3, INDEX est facultatif
)
ENGINE=MyISAM ;
Ajout des index après création de la table
Il existe deux commandes permettant de créer des index sur une table existante : ALTER TABLE et
CREATE INDEX.
Ajout avec ALTER TABLE
ALTER TABLE nom_table
ADD INDEX [nom_index] (colonne_index [, colonne2_index ...]); --
Ajout d'un index simple
ALTER TABLE nom_table
ADD UNIQUE [nom_index] (colonne_index [, colonne2_index ...]); --
Ajout d'un index UNIQUE
ALTER TABLE nom_table
ADD FULLTEXT [nom_index] (colonne_index [, colonne2_index ...]); --
Ajout d'un index FULLTEXT
Exemple :
ALTER TABLE Test
ADD INDEX ind_nom (nom);
9
Ajout avec CREATE INDEX
CREATE INDEX nom_index
ON nom_table (colonne_index [, colonne2_index ...]); -- Crée un
index simple
CREATE UNIQUE INDEX nom_index
ON nom_table (colonne_index [, colonne2_index ...]); -- Crée un
index UNIQUE
CREATE FULLTEXT INDEX nom_index
ON nom_table (colonne_index [, colonne2_index ...]); -- Crée un
index FULLTEXT
Exemple :
CREATE INDEX ind_nom
ON Test (nom);
Suppression d'un index
Code : SQL
ALTER TABLE nom_table
DROP INDEX nom_index;
Recherches avec FULLTEXT
On peut faire des recherches dans la base de données avec FULLTEXT, mais il y’a aussi des outils plus
adaptés pour faire des recherches qui sont les moteurs d’une table.
Exemple : SolR, Sphinx ou ElasticSearch. Un index FULLTEXT ne peut être défini que sur une colonne
de type CHAR, VARCHAR ou TEXT.
10
Fonctionnement la recherche FULLTEXT
Lorsqu’on fait une recherche FULLTEXT sur une chaîne de caractères, cette chaîne est découpée en
mots. Est considérée comme un mot : toute suite de caractères composée de lettres, chiffres, tirets bas _ et
apostrophes '. Par conséquent, un mot composé, comme "porte- clés" par exemple, sera considéré comme
deux mots : "porte" et "clés". Chacun de ces mots sera ensuite comparé avec les valeurs des colonnes sur
lesquelles se fait la recherche. Si la colonne contient un des mots recherchés, on considère alors qu'elle
correspond à la recherche.
Lorsque MySQL compare la chaîne de caractères qu’on lui donne et les valeurs dans votre table, il ne
tient pas compte de tous les mots qu'il rencontre. Les règles sont les suivantes :
• les mots rencontrés dans au moins la moitié des lignes sont ignorés (règle des 50 %) ;
• les mots trop courts (moins de quatre lettres) sont ignorés ;
• et les mots trop communs (en anglais, about, after, once, under, the…) ne sont également
pas pris en compte.
Les types de recherche
Il existe trois types de recherche FULLTEXT : la recherche naturelle, la recherche avec booléen, et
enfin la recherche avec extension de requête.
Recherche naturelle
Lorsque l'on fait une recherche naturelle, il suffit qu'un seul mot de la chaîne de caractères recherchée
se retrouve dans une ligne pour que celle-ci apparaisse dans les résultats.
Attention, cependant, au fait que le mot exact doit se retrouver dans la valeur des colonnes de l'index
FULLTEXT examiné.
Voici la syntaxe utilisée pour faire une recherche FULLTEXT :
Exemple :
SELECT *
FROM 4irst
WHERE MATCH(nom)
AGAINST ('Coulibaly');
Recherche avec booléens
La recherche avec booléens possède les caractéristiques suivantes :
• Elle ne tient pas compte de la règle des 50 % qui veut qu'un mot présent dans 50 % des lignes au
moins soit ignoré ;
• Elle peut se faire sur une ou des colonnes sur lesquelles aucun index FULLTEXT n'est défini (ce
sera cependant beaucoup plus lent que si un index est présent) ;
11
• Les résultats ne seront pas triés par pertinence par défaut.
Pour faire une recherche avec booléens, il suffit d'ajouter IN BOOLEAN MODE après la chaîne
recherchée.
SELECT *
FROM nom_table
WHERE MATCH(colonne)
AGAINST('chaîne recherchée' IN BOOLEAN MODE); -- IN BOOLEAN MODE à
l'intérieur des parenthèses !
La recherche avec booléens permet d'être à la fois plus précis et plus approximatif dans ses recherches.
• Plus précis, car on peut exiger que certains mots se trouvent dans la ligne ou soient absents de
la ligne pour la sélectionner. On peut même exiger la présence
de groupes de mots, plutôt que de rechercher chaque mot séparément.
• Plus approximatif, car on peut utiliser un astérisque * en fin de mot, pour préciser que le mot peut
finir de n'importe quelle manière.
Exemple :
SELECT *
FROM 4irst
WHERE MATCH(commentaire)
AGAINST ('+grand -noir' IN BOOLEAN MODE);
12
Recherche avec extension de requête
Le dernier type de recherche est un peu particulier. En effet, la recherche avec
extension de requête se déroule en deux étapes.
1. Une simple recherche naturelle est effectuée.
2. Les résultats de cette recherche sont utilisés pour faire une seconde
recherche naturelle.
Exemple :
SELECT *
FROM 4irst
WHERE MATCH(nom,
prenom)AGAINST
('Sylla');
En résumé
• Un index est une structure de données qui reprend la liste ordonnée des
valeurs auxquelles il se rapporte.
• Un index peut se faire sur une ou plusieurs colonnes, et, dans le cas d'une
colonne de type alphanumérique (CHAR, VARCHAR, TEXT, etc.), il peut ne
prendre en compte qu'une partie de la colonne (les x premiers caractères).
• Un index permet d'accélérer les recherches faites sur les colonnes constituant celui-
ci.
• Un index UNIQUE ne peut contenir qu'une seule fois chaque valeur (ou
combinaison de valeurs si l'index est composite, c'est-à-dire sur plusieurs
colonnes).
• Un index FULLTEXT permet de faire des recherches complexes sur le
contenu des colonnes le constituant.
13
