Cours MySQL Complet 🐬

— Se connecter
mysql -u root -p

— Vérifier la version
SELECT VERSION();

— Clients graphiques recommandés :
— • DBeaver (gratuit, multi-BDD)
— • MySQL Workbench (officiel)
— • TablePlus (macOS/Windows)
— • DataGrip (JetBrains, payant)

— Créer une table
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
role ENUM(‘user’, ‘admin’, ‘editor’) DEFAULT ‘user’,
bio TEXT,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

— Table avec clé étrangère
CREATE TABLE posts (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT,
user_id INT NOT NULL,
published BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

— Voir la structure
DESCRIBE users;
SHOW CREATE TABLE users;
SHOW TABLES;

— Modifier une colonne
ALTER TABLE users MODIFY COLUMN name VARCHAR(200) NOT NULL;

— Renommer une colonne
ALTER TABLE users RENAME COLUMN phone TO phone_number;

— Supprimer une colonne
ALTER TABLE users DROP COLUMN phone_number;

— Ajouter un index
ALTER TABLE users ADD INDEX idx_email (email);

— Insertion multiple (beaucoup plus rapide)
INSERT INTO users (name, email) VALUES
(‘Bob’, ‘bob@mail.com’),
(‘Claire’, ‘claire@mail.com’),
(‘David’, ‘david@mail.com’);

— INSERT … ON DUPLICATE KEY UPDATE (upsert)
INSERT INTO users (email, name) VALUES (‘alice@mail.com’, ‘Alice M.’)
ON DUPLICATE KEY UPDATE name = VALUES(name);

— UPDATE — Modifier des lignes
UPDATE users SET name = ‘Alice Martin’ WHERE id = 1;
UPDATE users SET role = ‘editor’, is_active = TRUE WHERE email = ‘bob@mail.com’;

— DELETE — Supprimer des lignes
DELETE FROM users WHERE id = 3;
DELETE FROM users WHERE is_active = FALSE AND created_at < ‘2023-01-01’;

— TRUNCATE — vider la table (plus rapide que DELETE, reset AUTO_INCREMENT)
TRUNCATE TABLE logs;

— WHERE — Filtrer
SELECT * FROM users WHERE role = ‘admin’;
SELECT * FROM users WHERE role IN (‘admin’, ‘editor’);
SELECT * FROM users WHERE name LIKE ‘%ali%’; — Contient « ali »
SELECT * FROM users WHERE bio IS NULL; — Bio vide
SELECT * FROM users WHERE bio IS NOT NULL; — Bio remplie
SELECT * FROM users WHERE created_at BETWEEN ‘2024-01-01’ AND ‘2024-12-31’;

— AND / OR / NOT
SELECT * FROM users WHERE role = ‘admin’ AND is_active = TRUE;
SELECT * FROM users WHERE role = ‘admin’ OR role = ‘editor’;

— ORDER BY — Trier
SELECT * FROM users ORDER BY created_at DESC; — Plus récents
SELECT * FROM users ORDER BY name ASC, created_at DESC;

— LIMIT / OFFSET — Pagination
SELECT * FROM users ORDER BY id LIMIT 20; — 20 premiers
SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 40; — Page 3

— Alias
SELECT name AS nom, email AS courriel FROM users;

— LEFT JOIN — tous les users, même sans posts
SELECT u.name, COUNT(p.id) AS nb_posts
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name;

— RIGHT JOIN — tous les posts, même si user supprimé
SELECT u.name, p.title
FROM users u
RIGHT JOIN posts p ON u.id = p.user_id;

— Jointure multiple (3 tables)
SELECT u.name, p.title, c.content AS comment
FROM comments c
INNER JOIN posts p ON c.post_id = p.id
INNER JOIN users u ON c.user_id = u.id
ORDER BY c.created_at DESC;

— GROUP BY — agréger par groupe
SELECT role, COUNT(*) AS nb
FROM users
GROUP BY role;

— GROUP BY + jointure
SELECT u.name, COUNT(p.id) AS nb_posts, MAX(p.created_at) AS dernier_post
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name
ORDER BY nb_posts DESC;

— HAVING — filtrer APRÈS l’agrégation (WHERE = avant)
SELECT user_id, COUNT(*) AS nb_posts
FROM posts
GROUP BY user_id
HAVING nb_posts > 5; — Seulement les auteurs avec > 5 posts

— Date
SELECT NOW(), CURDATE(), CURTIME();
SELECT YEAR(created_at), MONTH(created_at), DAY(created_at) FROM users;
SELECT DATE_FORMAT(created_at, ‘%d/%m/%Y’) FROM users;
SELECT DATEDIFF(NOW(), created_at) AS jours_depuis FROM users;

— Conditionnel
SELECT name,
CASE role
WHEN ‘admin’ THEN ‘Administrateur’
WHEN ‘editor’ THEN ‘Éditeur’
ELSE ‘Utilisateur’
END AS role_label
FROM users;

— COALESCE — première valeur non NULL
SELECT name, COALESCE(bio, ‘Pas de bio’) AS bio FROM users;

— IF
SELECT name, IF(is_active, ‘Actif’, ‘Inactif’) AS statut FROM users;

— Sous-requête scalaire (retourne 1 valeur)
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

— EXISTS — plus performant que IN pour les grosses tables
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM posts p WHERE p.user_id = u.id AND p.published = TRUE
);

— CTE (Common Table Expression) — plus lisible
WITH active_authors AS (
SELECT user_id, COUNT(*) AS nb_posts
FROM posts WHERE published = TRUE
GROUP BY user_id
HAVING nb_posts > 3
)
SELECT u.name, a.nb_posts
FROM users u
INNER JOIN active_authors a ON u.id = a.user_id
ORDER BY a.nb_posts DESC;

— Utiliser la vue comme une table
SELECT * FROM v_user_stats WHERE nb_posts > 5;

— Supprimer
DROP VIEW v_user_stats;

— Voir les index d’une table
SHOW INDEX FROM users;

— Supprimer un index
DROP INDEX idx_users_email ON users;

— EXPLAIN — analyser les performances d’une requête
EXPLAIN SELECT * FROM users WHERE email = ‘alice@mail.com’;
EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 42 AND published = TRUE;

— Transférer 100€ de Alice vers Bob
UPDATE accounts SET balance = balance – 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

— Si tout est OK :
COMMIT;

— Si erreur → annuler tout :
ROLLBACK;

— Donner des permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON mon_app.* TO ‘app_user’@‘%’;
FLUSH PRIVILEGES;

— Permissions en lecture seule
GRANT SELECT ON mon_app.* TO ‘readonly’@‘%’;

— Voir les permissions
SHOW GRANTS FOR ‘app_user’@‘%’;

— Supprimer
DROP USER ‘app_user’@‘%’;

— Sauvegarde d’une table
mysqldump -u root -p mon_app users > users_backup.sql

— Restaurer
mysql -u root -p mon_app < backup.sql

— Sauvegarde compressée
mysqldump -u root -p mon_app | gzip > backup_$(date +%Y%m%d).sql.gz

— Vérifier l’état du serveur
SHOW PROCESSLIST; — Requêtes en cours
SHOW STATUS; — Statistiques serveur
SHOW VARIABLES LIKE ‘%buffer%’; — Configuration