Apprendre MySQL

# Ubuntu / Debian
sudo apt update
sudo apt install mysql-server

# macOS via Homebrew
brew install mysql

# Démarrer le service MySQL
sudo systemctl start mysql

# Sécuriser l'installation (recommandé)
sudo mysql_secure_installation

http://localhost/phpmyadmin

CREATE DATABASE boutique;

-- Avec encodage recommandé (accents, emojis, toutes les langues)
CREATE DATABASE boutique
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Lister toutes les bases existantes
SHOW DATABASES;

-- Supprimer une base (⚠️ irréversible)
DROP DATABASE boutique;

-- Créer seulement si elle n'existe pas déjà
CREATE DATABASE IF NOT EXISTS boutique;

USE boutique;

-- Vérifier quelle base est active
SELECT DATABASE();  -- retourne "boutique"

-- Lister les tables de la base active
SHOW TABLES;

-- Voir la structure complète de la base
SHOW TABLE STATUS;

CREATE TABLE utilisateurs (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  nom   VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE NOT NULL,
  age   INT
);

-- Voir les tables de la base
SHOW TABLES;

-- Voir la structure détaillée d'une table
DESCRIBE utilisateurs;
-- ou (équivalent)
SHOW COLUMNS FROM utilisateurs;

-- Voir le SQL complet de création
SHOW CREATE TABLE utilisateurs;

-- Insertion simple
INSERT INTO utilisateurs (nom, email, age)
VALUES ('Aliou Diallo', 'aliou@gmail.com', 25);

-- Insertion multiple en une requête (bien plus performant)
INSERT INTO utilisateurs (nom, email, age)
VALUES
  ('Fatoumata Bah',  'fato@gmail.com',   30),
  ('Moussa Koné',    'moussa@gmail.com', 28),
  ('Aminata Sow',    'ami@gmail.com',    22);

-- Insérer et récupérer l'ID généré
INSERT INTO utilisateurs (nom, email) VALUES ('Awa', 'awa@test.com');
SELECT LAST_INSERT_ID(); -- retourne l'ID de la ligne insérée

-- Insérer OU mettre à jour si l'email existe déjà (UPSERT)
INSERT INTO utilisateurs (nom, email) VALUES ('Ali', 'ali@test.com')
ON DUPLICATE KEY UPDATE nom = 'Ali (mis à jour)';

-- Toutes les colonnes
SELECT * FROM utilisateurs;

-- Colonnes spécifiques (recommandé en production)
SELECT nom, email FROM utilisateurs;

-- Alias de colonnes
SELECT nom AS 'Prénom', email AS 'Adresse Email'
FROM utilisateurs;

-- Valeur calculée dans le SELECT
SELECT nom,
       prix * 1.2 AS prix_ttc
FROM produits;

-- Éliminer les doublons avec DISTINCT
SELECT DISTINCT pays FROM utilisateurs;

-- Condition simple
SELECT * FROM utilisateurs WHERE id = 1;

-- Combinaison AND / OR / NOT
SELECT * FROM utilisateurs
WHERE pays = 'Sénégal' AND age > 20;

SELECT * FROM utilisateurs
WHERE pays = 'Mali' OR pays = 'Guinée';

SELECT * FROM utilisateurs
WHERE NOT pays = 'Sénégal';

-- Parenthèses pour grouper les conditions
SELECT * FROM commandes
WHERE (statut = 'en_attente' OR statut = 'expédié')
  AND montant > 10000;

-- Tri alphabétique A→Z (ASC = défaut)
SELECT * FROM utilisateurs ORDER BY nom ASC;

-- Tri du plus récent au plus ancien
SELECT * FROM commandes ORDER BY date_commande DESC;

-- Tri multi-colonnes : par catégorie ASC, puis prix DESC
SELECT * FROM produits
ORDER BY categorie ASC, prix DESC;

-- Tri par numéro de colonne (position dans SELECT)
SELECT nom, age FROM utilisateurs ORDER BY 2 DESC; -- tri par 'age'

-- NULL en dernier (comportement par défaut avec ASC)
SELECT * FROM produits ORDER BY note IS NULL ASC, note DESC;

-- Les 5 premiers
SELECT * FROM utilisateurs LIMIT 5;

-- LIMIT offset, count (page 2 = sauter 10, prendre 10)
SELECT * FROM utilisateurs LIMIT 10, 10;

-- Syntaxe alternative (plus lisible)
SELECT * FROM utilisateurs LIMIT 10 OFFSET 20; -- page 3

-- Toujours avec ORDER BY pour une pagination cohérente
SELECT * FROM articles
ORDER BY date_publication DESC
LIMIT 0, 10; -- Page 1

-- Modifier un seul enregistrement
UPDATE utilisateurs
SET nom = 'Mamadou Diallo'
WHERE id = 1;

-- Modifier plusieurs colonnes à la fois
UPDATE utilisateurs
SET nom = 'Mamadou', email = 'mamadou@gmail.com'
WHERE id = 1;

-- Incrémenter une valeur (ex: ajouter des points)
UPDATE utilisateurs
SET points = points + 100
WHERE id = 1;

-- Vérifier combien de lignes seraient affectées avant de modifier
SELECT COUNT(*) FROM utilisateurs WHERE actif = 0;

-- Supprimer un enregistrement précis
DELETE FROM utilisateurs WHERE id = 5;

-- Supprimer selon une condition
DELETE FROM utilisateurs WHERE actif = 0;

-- DELETE avec ORDER BY et LIMIT (ex: supprimer les 100 plus anciens)
DELETE FROM logs ORDER BY date_log ASC LIMIT 100;

-- Clé primaire simple
CREATE TABLE utilisateurs (
  id  INT PRIMARY KEY,
  nom VARCHAR(100)
);

-- Clé primaire composite (sur plusieurs colonnes)
CREATE TABLE inscriptions (
  etudiant_id INT,
  cours_id    INT,
  PRIMARY KEY (etudiant_id, cours_id)
);

-- Tentative de doublon → erreur
INSERT INTO utilisateurs VALUES (1, 'Ali');
INSERT INTO utilisateurs VALUES (1, 'Moussa'); -- ❌ Duplicate entry '1'

CREATE TABLE utilisateurs (
  id  INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(100)
);

INSERT INTO utilisateurs (nom) VALUES ('Ali');    -- id = 1
INSERT INTO utilisateurs (nom) VALUES ('Fato');   -- id = 2
INSERT INTO utilisateurs (nom) VALUES ('Moussa'); -- id = 3

-- Si on supprime id=3 et en insère un nouveau → id = 4 (jamais réutilisé)

-- Définir la valeur de départ
ALTER TABLE utilisateurs AUTO_INCREMENT = 1000;

-- Voir la prochaine valeur AUTO_INCREMENT
SHOW TABLE STATUS LIKE 'utilisateurs';

CREATE TABLE utilisateurs (
  id     INT AUTO_INCREMENT PRIMARY KEY,
  nom    VARCHAR(100) NOT NULL,
  email  VARCHAR(150) NOT NULL UNIQUE,
  pays   VARCHAR(50)  DEFAULT 'Sénégal',
  actif  BOOLEAN DEFAULT 1,
  age    INT CHECK (age >= 0 AND age <= 150),
  créé_le TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE commandes (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  utilisateur_id INT NOT NULL,
  produit        VARCHAR(200),
  montant        DECIMAL(10,2),
  FOREIGN KEY (utilisateur_id)
    REFERENCES utilisateurs(id)
    ON DELETE CASCADE       -- supprime les commandes si l'utilisateur est supprimé
    ON UPDATE CASCADE       -- met à jour si l'ID change
);

-- Ajouter une colonne
ALTER TABLE utilisateurs
ADD COLUMN telephone VARCHAR(20);

-- Ajouter une colonne à une position précise
ALTER TABLE utilisateurs
ADD COLUMN telephone VARCHAR(20) AFTER email;

-- Modifier le type d'une colonne
ALTER TABLE utilisateurs
MODIFY COLUMN nom VARCHAR(200) NOT NULL;

-- Renommer une colonne
ALTER TABLE utilisateurs
RENAME COLUMN nom TO nom_complet;

-- Supprimer une colonne
ALTER TABLE utilisateurs
DROP COLUMN telephone;

-- Renommer la table
RENAME TABLE utilisateurs TO membres;

-- Commandes avec les infos de l'utilisateur
SELECT u.nom, c.produit, c.montant
FROM utilisateurs AS u
INNER JOIN commandes AS c
  ON u.id = c.utilisateur_id;

-- JOIN sur 3 tables
SELECT u.nom, c.produit, p.categorie
FROM utilisateurs u
INNER JOIN commandes c ON u.id = c.utilisateur_id
INNER JOIN produits  p ON c.produit_id = p.id;

-- Tous les utilisateurs, même ceux sans commande
SELECT u.nom, c.produit
FROM utilisateurs u
LEFT JOIN commandes c
  ON u.id = c.utilisateur_id;
-- c.produit vaut NULL pour les utilisateurs sans commande

-- Trouver les utilisateurs qui n'ont JAMAIS commandé
SELECT u.nom
FROM utilisateurs u
LEFT JOIN commandes c
  ON u.id = c.utilisateur_id
WHERE c.id IS NULL; -- Technique anti-JOIN

-- Trouver le manager de chaque employé
SELECT
  e.nom     AS employe,
  m.nom     AS manager
FROM   employes AS e
LEFT JOIN employes AS m
  ON e.manager_id = m.id;

-- Générer toutes les combinaisons taille × couleur
SELECT t.taille, c.couleur
FROM tailles t
CROSS JOIN couleurs c;
-- Si 3 tailles × 5 couleurs = 15 lignes résultantes

-- Nombre d'utilisateurs par pays
SELECT pays, COUNT(*) AS total
FROM utilisateurs
GROUP BY pays
ORDER BY total DESC;

-- HAVING : filtre appliqué APRÈS le regroupement
SELECT pays, COUNT(*) AS total
FROM utilisateurs
GROUP BY pays
HAVING total > 5
ORDER BY total DESC;

-- Chiffre d'affaires par mois
SELECT
  YEAR(date_commande)  AS annee,
  MONTH(date_commande) AS mois,
  SUM(montant)          AS ca
FROM commandes
GROUP BY annee, mois
ORDER BY annee, mois;

-- Concaténer des chaînes
SELECT CONCAT(prenom, ' ', nom) AS nom_complet FROM utilisateurs;
SELECT CONCAT_WS(', ', ville, pays) AS adresse FROM utilisateurs;

-- Casse
SELECT UPPER(nom), LOWER(email) FROM utilisateurs;

-- Longueur et extraction
SELECT LENGTH(nom) AS nb_chars FROM utilisateurs;
SELECT SUBSTRING(email, 1, 5) FROM utilisateurs;  -- 5 premiers caractères
SELECT LEFT(nom, 3), RIGHT(nom, 3) FROM utilisateurs;

-- Nettoyage
SELECT TRIM('  Aliou  ');  -- "Aliou" (supprime espaces)
SELECT REPLACE(email, '@gmail.com', '@outlook.com') FROM utilisateurs;

-- Recherche de position
SELECT INSTR('bonjour@gmail.com', '@'); -- retourne 8

SELECT COUNT(*)            AS total_utilisateurs FROM utilisateurs;
SELECT COUNT(email)        AS avec_email       FROM utilisateurs; -- ignore NULL
SELECT COUNT(DISTINCT pays) AS nb_pays          FROM utilisateurs;

SELECT SUM(montant)         AS ca_total    FROM commandes;
SELECT AVG(montant)         AS panier_moyen FROM commandes;
SELECT MIN(prix), MAX(prix) FROM produits;

-- Tout en une requête
SELECT
  COUNT(*)            AS nb_commandes,
  SUM(montant)         AS total,
  AVG(montant)         AS moyenne,
  MIN(montant)         AS plus_petite,
  MAX(montant)         AS plus_grande
FROM commandes
WHERE statut = 'terminé';

-- Date et heure actuelle
SELECT NOW();         -- 2026-06-08 14:30:00
SELECT CURDATE();     -- 2026-06-08
SELECT CURTIME();     -- 14:30:00

-- Extraire des parties
SELECT YEAR(NOW()),  MONTH(NOW()),  DAY(NOW());
SELECT HOUR(NOW()), MINUTE(NOW()), WEEKDAY(NOW());

-- Calculs de dates
SELECT DATE_ADD(NOW(), INTERVAL 30 DAY);   -- dans 30 jours
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR);   -- il y a 1 an
SELECT DATEDIFF('2026-12-31', CURDATE()); -- jours restants en 2026

-- Formater une date
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y %H:%i'); -- "08/06/2026 14:30"

-- Commandes des 7 derniers jours
SELECT * FROM commandes
WHERE date_commande >= DATE_SUB(NOW(), INTERVAL 7 DAY);

-- Utilisateurs ayant commandé plus que la moyenne
SELECT * FROM commandes
WHERE montant > (
  SELECT AVG(montant) FROM commandes
);

-- Sous-requête dans FROM (table dérivée)
SELECT stats.pays, stats.total
FROM (
  SELECT pays, COUNT(*) AS total
  FROM utilisateurs
  GROUP BY pays
) AS stats
WHERE stats.total > 3;

-- EXISTS : vérifie l'existence d'une correspondance
SELECT * FROM utilisateurs u
WHERE EXISTS (
  SELECT 1 FROM commandes c
  WHERE c.utilisateur_id = u.id
);

-- UNION : élimine les doublons (plus lent)
SELECT email FROM utilisateurs
UNION
SELECT email FROM employes;

-- UNION ALL : garde les doublons (plus rapide)
SELECT nom, 'Utilisateur' AS type FROM utilisateurs
UNION ALL
SELECT nom, 'Employé'    AS type FROM employes
ORDER BY nom;

-- LIKE : recherche par motif (% = n'importe quoi, _ = 1 caractère)
SELECT * FROM utilisateurs WHERE nom LIKE 'Ali%';    -- commence par Ali
SELECT * FROM utilisateurs WHERE nom LIKE '%ba';    -- finit par ba
SELECT * FROM utilisateurs WHERE nom LIKE '%ou%';   -- contient ou
SELECT * FROM utilisateurs WHERE nom LIKE 'A___';   -- A suivi de 3 chars
SELECT * FROM utilisateurs WHERE nom NOT LIKE '%test%'; -- exclure les tests

-- IN : liste de valeurs exactes
SELECT * FROM utilisateurs
WHERE pays IN ('Sénégal', 'Mali', 'Guinée');

-- NOT IN : exclure une liste
SELECT * FROM produits
WHERE categorie NOT IN ('archive', 'supprimé');

-- BETWEEN : plage inclusive
SELECT * FROM utilisateurs WHERE age BETWEEN 18 AND 30;
SELECT * FROM commandes
WHERE date_commande BETWEEN '2026-01-01' AND '2026-12-31';

-- Catégoriser les utilisateurs par âge
SELECT nom, age,
  CASE
    WHEN age < 18 THEN 'Mineur'
    WHEN age BETWEEN 18 AND 25 THEN 'Jeune adulte'
    WHEN age BETWEEN 26 AND 60 THEN 'Adulte'
    ELSE 'Senior'
  END AS tranche_age
FROM utilisateurs;

-- Dans un GROUP BY : stats par catégorie de prix
SELECT
  CASE
    WHEN prix < 1000  THEN 'Entrée de gamme'
    WHEN prix < 10000 THEN 'Milieu de gamme'
    ELSE 'Haut de gamme'
  END AS gamme,
  COUNT(*) AS nb_produits,
  AVG(prix) AS prix_moyen
FROM produits
GROUP BY gamme;

-- Créer un index simple
CREATE INDEX idx_email ON utilisateurs (email);

-- Index unique (comme UNIQUE + INDEX)
CREATE UNIQUE INDEX idx_email_unique ON utilisateurs (email);

-- Index composite (plusieurs colonnes)
CREATE INDEX idx_pays_age ON utilisateurs (pays, age);

-- Voir les index d'une table
SHOW INDEX FROM utilisateurs;

-- Supprimer un index
DROP INDEX idx_email ON utilisateurs;

EXPLAIN SELECT * FROM utilisateurs
WHERE email = 'aliou@gmail.com';

-- Créer une vue
CREATE VIEW vue_clients_actifs AS
SELECT id, nom, email
FROM utilisateurs
WHERE actif = 1;

-- Utiliser la vue comme une table
SELECT * FROM vue_clients_actifs;

-- Vue complexe avec JOIN
CREATE VIEW vue_commandes_detaillees AS
SELECT
  u.nom       AS client,
  c.montant,
  c.date_commande
FROM utilisateurs u
INNER JOIN commandes c ON u.id = c.utilisateur_id;

-- Modifier une vue
CREATE OR REPLACE VIEW vue_clients_actifs AS
SELECT id, nom, email, pays
FROM utilisateurs WHERE actif = 1;

-- Supprimer une vue
DROP VIEW vue_clients_actifs;

DELIMITER $$

CREATE TRIGGER log_inscription
AFTER INSERT ON utilisateurs
FOR EACH ROW
BEGIN
  INSERT INTO journal (action, details, date_action)
  VALUES ('Inscription', NEW.email, NOW());
END$$

-- Trigger BEFORE UPDATE : validation avant modification
CREATE TRIGGER verif_age
BEFORE INSERT ON utilisateurs
FOR EACH ROW
BEGIN
  IF NEW.age < 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Âge invalide';
  END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE rechercherUtilisateur(IN recherche VARCHAR(100))
BEGIN
  SELECT * FROM utilisateurs
  WHERE nom LIKE CONCAT('%', recherche, '%');
END$$

-- Procédure avec paramètre OUT (retour de valeur)
CREATE PROCEDURE compterUtilisateurs(
  IN  p_pays  VARCHAR(50),
  OUT p_total INT
)
BEGIN
  SELECT COUNT(*) INTO p_total
  FROM utilisateurs
  WHERE pays = p_pays;
END$$

DELIMITER ;

-- Appeler les procédures
CALL rechercherUtilisateur('Ali');

CALL compterUtilisateurs('Sénégal', @total);
SELECT @total;

DELIMITER $$

-- Fonction qui calcule la TVA
CREATE FUNCTION calculerTTC(prix_ht DECIMAL(10,2), taux_tva DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN prix_ht * (1 + taux_tva / 100);
END$$

DELIMITER ;

-- Utilisation dans une requête
SELECT nom, prix, calculerTTC(prix, 18) AS prix_ttc
FROM produits;

START TRANSACTION;

UPDATE comptes
SET solde = solde - 5000
WHERE id = 1; -- Débit compte source

UPDATE comptes
SET solde = solde + 5000
WHERE id = 2; -- Crédit compte destination

COMMIT; -- ✅ Valider si tout réussit
-- ROLLBACK; -- ❌ Annuler tout en cas d'erreur

-- SAVEPOINT : points de restauration intermédiaires
START TRANSACTION;
INSERT INTO commandes ... ;
SAVEPOINT apres_commande;
UPDATE stocks ... ;
ROLLBACK TO apres_commande; -- Annule seulement depuis le SAVEPOINT
COMMIT;

DELIMITER $$

CREATE PROCEDURE insererUtilisateur(
  IN p_nom   VARCHAR(100),
  IN p_email VARCHAR(150),
  OUT p_succes BOOLEAN
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET p_succes = FALSE;
    ROLLBACK;
  END;

  START TRANSACTION;
  INSERT INTO utilisateurs (nom, email) VALUES (p_nom, p_email);
  SET p_succes = TRUE;
  COMMIT;
END$$

DELIMITER ;

-- Créer un utilisateur dédié à l'application (ne jamais utiliser root)
CREATE USER 'app_user'@'localhost'
  IDENTIFIED BY 'MotDePasse$ecur1s!';

-- Accorder uniquement les droits nécessaires (principe du moindre privilège)
GRANT SELECT, INSERT, UPDATE
  ON boutique.*
  TO 'app_user'@'localhost';

-- Appliquer les changements
FLUSH PRIVILEGES;

-- Voir les droits d'un utilisateur
SHOW GRANTS FOR 'app_user'@'localhost';

-- Révoquer des droits
REVOKE DELETE ON boutique.* FROM 'app_user'@'localhost';

-- Supprimer un utilisateur
DROP USER 'app_user'@'localhost';

-- Si email reçu = "admin'--" l'attaquant contourne le mot de passe
-- ❌ DANGEREUX : concaténation directe
"SELECT * FROM users WHERE email = '" + email + "'"
-- Devient : SELECT * FROM users WHERE email = 'admin'--'
-- Le -- commente le reste → accès sans mot de passe !

-- ✅ SOLUTION : Requêtes préparées (paramétrisées)
-- En PHP avec PDO :
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$email]); // Le paramètre est échappé automatiquement

# Sauvegarder une base
mysqldump -u root -p boutique > sauvegarde_2026-06-08.sql

# Avec compression gzip
mysqldump -u root -p boutique | gzip > sauvegarde_2026-06-08.sql.gz

# Sauvegarder toutes les bases
mysqldump -u root -p --all-databases > toutes_les_bases.sql

# Sauvegarder seulement la structure (sans données)
mysqldump -u root -p --no-data boutique > structure.sql

# Restaurer une base
mysql -u root -p boutique < sauvegarde_2026-06-08.sql

# Restaurer depuis gzip
gunzip < sauvegarde_2026-06-08.sql.gz | mysql -u root -p boutique

# Automatiser avec cron (tous les jours à 2h du matin)
# 0 2 * * * mysqldump -u root -pmotdepasse boutique > /backup/$(date +%Y-%m-%d).sql

CREATE DATABASE ecole
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
USE ecole;

CREATE TABLE etudiants (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  nom            VARCHAR(100) NOT NULL,
  prenom         VARCHAR(100) NOT NULL,
  email          VARCHAR(150) UNIQUE NOT NULL,
  date_naissance DATE,
  cree_le        TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE cours (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  titre      VARCHAR(200) NOT NULL,
  credits    INT DEFAULT 3,
  professeur VARCHAR(100),
  duree_h    INT
);

CREATE TABLE inscriptions (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  etudiant_id      INT NOT NULL,
  cours_id         INT NOT NULL,
  date_inscription DATETIME DEFAULT CURRENT_TIMESTAMP,
  note             DECIMAL(4,2) CHECK (note BETWEEN 0 AND 20),
  FOREIGN KEY (etudiant_id) REFERENCES etudiants(id) ON DELETE CASCADE,
  FOREIGN KEY (cours_id)    REFERENCES cours(id)    ON DELETE CASCADE
);

-- Étudiants avec leurs notes par cours
SELECT
  CONCAT(e.prenom, ' ', e.nom) AS etudiant,
  c.titre                         AS cours,
  i.note,
  CASE
    WHEN i.note >= 16 THEN 'Très Bien'
    WHEN i.note >= 14 THEN 'Bien'
    WHEN i.note >= 10 THEN 'Passable'
    ELSE 'Insuffisant'
  END AS mention
FROM inscriptions i
INNER JOIN etudiants e ON i.etudiant_id = e.id
INNER JOIN cours     c ON i.cours_id    = c.id
ORDER BY etudiant, cours;

-- Moyenne par étudiant et classement
SELECT
  CONCAT(e.prenom, ' ', e.nom) AS etudiant,
  ROUND(AVG(i.note), 2)         AS moyenne,
  COUNT(i.cours_id)               AS nb_cours
FROM etudiants e
LEFT JOIN inscriptions i ON e.id = i.etudiant_id
GROUP BY e.id
HAVING moyenne >= 10
ORDER BY moyenne DESC;