Aide-mémoire MySQL
| Administration et Connexion |
|---|
| Objectif | Commande |
| Se connecter en CLI |
mysql -u root -p
mysql -u root -p mabase
mysql -h 127.0.0.1 -P 3306 -u user -p
mysql -u user -p'motdepasse' mabase # sans espace après -p |
| Afficher les bases |
SHOW DATABASES;
SHOW DATABASES LIKE 'test%'; |
| Sélectionner une base |
USE mabase; |
| Afficher les tables |
SHOW TABLES;
SHOW TABLES FROM mabase;
SHOW TABLE STATUS; |
| Décrire une table |
DESCRIBE matable;
DESC matable;
SHOW COLUMNS FROM matable;
SHOW CREATE TABLE matable; |
| Voir les index |
SHOW INDEX FROM matable; |
| Version et statut |
SELECT VERSION();
STATUS;
SHOW STATUS LIKE 'Uptime%';
SHOW VARIABLES LIKE 'max_connections'; |
| Processus actifs |
SHOW PROCESSLIST;
KILL <id_processus>; |
| Gestion des Utilisateurs |
|---|
| Créer un utilisateur |
CREATE USER 'monuser'@'localhost' IDENTIFIED BY 'motdepasse';
CREATE USER 'monuser'@'%' IDENTIFIED BY 'motdepasse'; -- accès depuis partout |
| Accorder des droits |
GRANT ALL PRIVILEGES ON mabase.* TO 'monuser'@'localhost';
GRANT SELECT, INSERT, UPDATE ON mabase.* TO 'monuser'@'localhost';
GRANT SELECT ON mabase.matable TO 'lecteur'@'localhost';
FLUSH PRIVILEGES; -- toujours après GRANT |
| Voir les droits |
SHOW GRANTS FOR 'monuser'@'localhost'; |
| Révoquer des droits |
REVOKE ALL PRIVILEGES ON mabase.* FROM 'monuser'@'localhost';
FLUSH PRIVILEGES; |
| Modifier le mot de passe |
ALTER USER 'monuser'@'localhost' IDENTIFIED BY 'nouveaumotdepasse';
FLUSH PRIVILEGES; |
| Supprimer un utilisateur |
DROP USER 'monuser'@'localhost'; |
| DDL - Définition des Données |
|---|
| Créer une base |
CREATE DATABASE mabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS mabase; |
| Supprimer une base |
DROP DATABASE mabase;
DROP DATABASE IF EXISTS mabase; |
| Créer une table |
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
login VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL,
motdepasse VARCHAR(255) NOT NULL,
role ENUM('admin','user','moderateur') DEFAULT 'user',
age TINYINT UNSIGNED,
solde DECIMAL(10,2) DEFAULT 0.00,
bio TEXT,
avatar VARCHAR(255),
actif TINYINT(1) NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_email (email),
INDEX idx_role_actif (role, actif)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
| Modifier une table (ALTER) |
ALTER TABLE users ADD COLUMN telephone VARCHAR(20) AFTER email;
ALTER TABLE users DROP COLUMN avatar;
ALTER TABLE users MODIFY COLUMN age SMALLINT UNSIGNED;
ALTER TABLE users CHANGE COLUMN login username VARCHAR(60) NOT NULL;
ALTER TABLE users RENAME TO clients;
ALTER TABLE users ADD INDEX idx_login (login);
ALTER TABLE users DROP INDEX idx_login;
ALTER TABLE users ADD CONSTRAINT fk_role FOREIGN KEY (role_id) REFERENCES roles(id); |
| Supprimer une table |
DROP TABLE matable;
DROP TABLE IF EXISTS matable;
TRUNCATE TABLE matable; -- vider sans supprimer (reset AUTO_INCREMENT) |
| Types de données courants |
Entiers : TINYINT (1o), SMALLINT (2o), INT (4o), BIGINT (8o) + UNSIGNED
Décimaux : DECIMAL(10,2) (exact), FLOAT, DOUBLE
Chaînes : CHAR(n) (fixe), VARCHAR(n) (variable), TEXT, MEDIUMTEXT, LONGTEXT
Dates : DATE (YYYY-MM-DD), TIME, DATETIME, TIMESTAMP, YEAR
Binaires : BLOB, MEDIUMBLOB, LONGBLOB
Autres : ENUM('a','b'), SET('a','b'), JSON, BOOLEAN (= TINYINT(1))
|
| Index |
CREATE INDEX idx_nom ON users(nom);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE INDEX idx_compose ON users(nom, prenom); -- index composé
CREATE FULLTEXT INDEX idx_ft ON articles(titre, contenu); -- plein texte
DROP INDEX idx_nom ON users; |
| DML - Manipulation des Données |
|---|
| INSERT |
-- Insertion simple
INSERT INTO users (login, email, motdepasse) VALUES ('alice', 'alice@ex.com', 'hash');
-- Insertion multiple
INSERT INTO users (login, email, motdepasse) VALUES
('bob', 'bob@ex.com', 'hash1'),
('eve', 'eve@ex.com', 'hash2');
-- Depuis une autre table
INSERT INTO archive_users SELECT * FROM users WHERE actif = 0;
-- Upsert (MySQL 8)
INSERT INTO compteurs (cle, valeur) VALUES ('visites', 1)
ON DUPLICATE KEY UPDATE valeur = valeur + 1; |
| UPDATE |
UPDATE users SET actif = 1 WHERE id = 5;
UPDATE users SET actif = 0, role = 'user' WHERE created_at < '2020-01-01';
-- Update avec jointure
UPDATE users u
JOIN commandes c ON c.user_id = u.id
SET u.nb_commandes = u.nb_commandes + 1
WHERE c.status = 'validee'; |
| DELETE |
DELETE FROM users WHERE id = 5;
DELETE FROM users WHERE actif = 0 AND created_at < '2020-01-01';
-- Supprimer avec limite
DELETE FROM logs ORDER BY created_at ASC LIMIT 1000;
-- Delete avec jointure
DELETE u FROM users u
JOIN ban_list b ON b.email = u.email; |
| REPLACE |
-- Supprime + réinsère si la clé existe
REPLACE INTO config (cle, valeur) VALUES ('theme', 'dark'); |
| SELECT et Filtres |
|---|
| Syntaxe complète |
SELECT [DISTINCT] colonnes
FROM table1
[JOIN table2 ON condition]
WHERE condition
GROUP BY colonnes
HAVING condition_sur_agregat
ORDER BY colonne [ASC|DESC]
LIMIT n [OFFSET m]; |
| WHERE - opérateurs |
WHERE age = 30
WHERE age != 30 -- ou <>
WHERE age BETWEEN 18 AND 65
WHERE nom IN ('Alice', 'Bob', 'Eve')
WHERE nom NOT IN ('Alice')
WHERE email IS NULL
WHERE email IS NOT NULL
WHERE nom LIKE 'Ali%' -- commence par Ali
WHERE nom LIKE '%ice' -- finit par ice
WHERE nom LIKE '%li%' -- contient li
WHERE nom REGEXP '^A[a-z]+' -- regex
WHERE condition1 AND condition2
WHERE condition1 OR condition2
WHERE NOT condition |
| ORDER BY et LIMIT |
ORDER BY nom ASC
ORDER BY age DESC, nom ASC -- tri multi-colonnes
ORDER BY RAND() -- ordre aléatoire
LIMIT 10
LIMIT 10 OFFSET 20 -- page 3 (10 par page)
LIMIT 20, 10 -- équivalent : LIMIT offset, count |
| DISTINCT et GROUP BY |
SELECT DISTINCT pays FROM clients;
SELECT role, COUNT(*) AS nb, AVG(age) AS age_moyen
FROM users
GROUP BY role
HAVING COUNT(*) > 5
ORDER BY nb DESC; |
| Jointures (JOINs) |
|---|
| INNER JOIN |
-- Lignes communes (intersection)
SELECT u.nom, c.montant
FROM users u
INNER JOIN commandes c ON c.user_id = u.id;
-- Raccourci : JOIN sans INNER = INNER JOIN |
| LEFT JOIN |
-- Tous les users, même sans commandes
SELECT u.nom, COUNT(c.id) AS nb_commandes
FROM users u
LEFT JOIN commandes c ON c.user_id = u.id
GROUP BY u.id;
-- Trouver les users sans commandes
SELECT u.nom FROM users u
LEFT JOIN commandes c ON c.user_id = u.id
WHERE c.id IS NULL; |
| RIGHT JOIN / CROSS JOIN |
-- RIGHT JOIN : tous les enregistrements de droite
SELECT * FROM commandes c
RIGHT JOIN users u ON c.user_id = u.id;
-- CROSS JOIN : produit cartésien
SELECT * FROM couleurs CROSS JOIN tailles; |
| Auto-jointure |
-- Trouver le manager de chaque employé
SELECT e.nom AS employe, m.nom AS manager
FROM employes e
LEFT JOIN employes m ON e.manager_id = m.id; |
| Fonctions d'Agrégation |
|---|
| Fonctions standard |
COUNT(*) // nombre de lignes
COUNT(DISTINCT col) // valeurs distinctes
SUM(montant) // somme
AVG(age) // moyenne
MIN(age) // minimum
MAX(age) // maximum
GROUP_CONCAT(nom SEPARATOR ', ') // concaténer les valeurs d'un groupe |
| Fonctions de fenêtre (MySQL 8) |
-- Rang dans une partition
SELECT nom, salaire,
RANK() OVER (PARTITION BY dept ORDER BY salaire DESC) AS rang,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salaire DESC) AS no_ligne,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salaire DESC) AS rang_dense,
LAG(salaire) OVER (ORDER BY date_embauche) AS salaire_precedent,
LEAD(salaire) OVER (ORDER BY date_embauche) AS salaire_suivant
FROM employes; |
| Sous-Requêtes et CTE |
|---|
| Sous-requêtes |
-- Dans WHERE
SELECT * FROM users WHERE id IN (SELECT user_id FROM commandes WHERE montant > 100);
-- Dans FROM (table dérivée)
SELECT dept, avg_salaire FROM (
SELECT departement AS dept, AVG(salaire) AS avg_salaire
FROM employes GROUP BY departement
) AS stats WHERE avg_salaire > 3000;
-- Corrélée
SELECT * FROM produits p
WHERE prix = (SELECT MAX(prix) FROM produits WHERE categorie = p.categorie); |
| CTE - Common Table Expression |
WITH ventes_2026 AS (
SELECT user_id, SUM(montant) AS total
FROM commandes WHERE YEAR(created_at) = 2026
GROUP BY user_id
,
) meilleurs AS (
SELECT * FROM ventes_2026 WHERE total > 1000
)
SELECT u.nom, m.total
FROM users u
JOIN meilleurs m ON m.user_id = u.id
ORDER BY m.total DESC; |
| CTE Récursive |
WITH RECURSIVE hierarchie AS (
SELECT id, nom, parent_id, 0 AS niveau
FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.nom, c.parent_id, h.niveau + 1
FROM categories c
JOIN hierarchie h ON c.parent_id = h.id
)
SELECT * FROM hierarchie ORDER BY niveau, nom; |
| Transactions |
|---|
| Contrôle des transactions |
START TRANSACTION; -- ou BEGIN;
UPDATE comptes SET solde = solde - 200 WHERE id = 1;
UPDATE comptes SET solde = solde + 200 WHERE id = 2;
COMMIT; -- valider
-- ou
ROLLBACK; -- annuler
SAVEPOINT point1; -- point de sauvegarde
ROLLBACK TO point1; -- revenir au point |
| Niveaux d'isolation |
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- le plus léger
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- défaut MySQL
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- le plus strict |
| Fonctions SQL Utiles |
|---|
| Chaînes |
CONCAT(nom, ' ', prenom)
CONCAT_WS(' ', prenom, nom) -- sép. automatique
LENGTH(chaine) / CHAR_LENGTH(chaine) -- octets / caractères
UPPER(chaine) / LOWER(chaine)
TRIM(chaine) / LTRIM / RTRIM
SUBSTRING(chaine, debut, longueur)
LEFT(chaine, 5) / RIGHT(chaine, 5)
REPLACE(chaine, 'ancien', 'nouveau')
LOCATE('mot', chaine) -- position
LPAD(code, 6, '0') -- "000042" |
| Dates |
NOW() // datetime courante
CURDATE() / CURTIME()
DATE(datetime_col) // extraire la date
YEAR(col) / MONTH(col) / DAY(col)
DAYOFWEEK(col) // 1=Dimanche
DATE_FORMAT(col, '%d/%m/%Y')
DATE_ADD(col, INTERVAL 7 DAY)
DATE_SUB(col, INTERVAL 1 MONTH)
DATEDIFF(date1, date2) // différence en jours
TIMESTAMPDIFF(MONTH, date1, date2)
UNIX_TIMESTAMP(col) / FROM_UNIXTIME(ts) |
| Numériques / Conditionnels |
ROUND(val, 2) / CEIL(val) / FLOOR(val)
ABS(val) / MOD(val, div)
RAND() // aléatoire 0-1
-- Conditionnels
IF(age >= 18, 'majeur', 'mineur')
IFNULL(col, 'valeur_defaut')
NULLIF(col, 0) // NULL si col = 0
COALESCE(a, b, c) // première valeur non-NULL
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 75 THEN 'B'
WHEN score >= 60 THEN 'C'
ELSE 'F'
END AS mention |
| JSON (MySQL 8) |
-- Stocker du JSON
INSERT INTO produits (options) VALUES ('{"couleur":"rouge","taille":"XL"}');
-- Lire
SELECT options->>'$.couleur' FROM produits; -- "rouge"
SELECT JSON_EXTRACT(options, '$.couleur');
-- Modifier
UPDATE produits SET options = JSON_SET(options, '$.couleur', 'bleu');
-- Rechercher
SELECT * FROM produits WHERE JSON_CONTAINS(options, '"rouge"', '$.couleur'); |
| Optimisation et Analyse |
|---|
| EXPLAIN (analyser une requête) |
EXPLAIN SELECT * FROM users WHERE email = 'alice@ex.com';
EXPLAIN FORMAT=JSON SELECT ...;
-- Colonnes importantes :
-- type : ALL (scan complet) > range > ref > eq_ref > const
-- key : index utilisé (NULL = aucun)
-- rows : estimation du nombre de lignes analysées
-- Extra: Using filesort, Using temporary (signe de lenteur) |
| Optimiser les tables |
OPTIMIZE TABLE matable; -- défragmenter
ANALYZE TABLE matable; // mettre à jour les stats
CHECK TABLE matable; // vérifier l'intégrité
REPAIR TABLE matable; // réparer (MyISAM surtout) |
| Requêtes lentes (slow query log) |
SHOW VARIABLES LIKE 'slow_query%';
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1; -- en secondes
-- Analyse : mysqldumpslow /var/log/mysql/slow.log |
| Backup et Restauration |
|---|
| Export (mysqldump) |
# Exporter une base complète
mysqldump -u root -p mabase > backup.sql
# Avec compression
mysqldump -u root -p mabase | gzip > backup.sql.gz
# Exporter plusieurs bases
mysqldump -u root -p --databases mabase mabase2 > multi.sql
# Toutes les bases
mysqldump -u root -p --all-databases > all.sql
# Structure seule (sans données)
mysqldump -u root -p --no-data mabase > structure.sql
# Données seules (sans structure)
mysqldump -u root -p --no-create-info mabase > data.sql |
| Import (mysql) |
# Importer dans une base
mysql -u root -p mabase < backup.sql
# Depuis un fichier compressé
gunzip -c backup.sql.gz | mysql -u root -p mabase
# Via la CLI MySQL
mysql -u root -p
CREATE DATABASE mabase;
USE mabase;
SOURCE /chemin/vers/backup.sql; |
| Mysqldump pratique |
# Exporter une seule table
mysqldump -u root -p mabase matable > table.sql
# Exporter certaines lignes
mysqldump -u root -p mabase matable --where="created_at > '2026-01-01'" > recent.sql
# En CSV
SELECT * INTO OUTFILE '/tmp/export.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM matable; |
| Procédures, Fonctions et Vues |
|---|
| Vue (VIEW) |
CREATE VIEW users_actifs AS
SELECT id, login, email, created_at
FROM users WHERE actif = 1;
SELECT * FROM users_actifs;
DROP VIEW users_actifs; |
| Procédure stockée |
DELIMITER //
CREATE PROCEDURE compter_users(IN role_param VARCHAR(50), OUT total INT)
BEGIN
SELECT COUNT(*) INTO total
FROM users WHERE role = role_param;
END //
DELIMITER ;
-- Appel
CALL compter_users('admin', @nb);
SELECT @nb; |
| Trigger |
DELIMITER //
CREATE TRIGGER avant_delete_user
BEFORE DELETE ON users FOR EACH ROW
BEGIN
INSERT INTO log_suppressions(user_id, supprime_le)
VALUES (OLD.id, NOW());
END //
DELIMITER ; |