SQL Structured Query Language

-- Sélection de base
SELECT * FROM utilisateurs;
SELECT nom, email, age FROM utilisateurs;
SELECT DISTINCT ville FROM utilisateurs;  -- valeurs uniques

-- Alias
SELECT nom AS nom_complet, email AS adresse_email FROM utilisateurs;
SELECT u.nom, u.email FROM utilisateurs u;  -- alias de table

-- Tri
SELECT * FROM produits ORDER BY prix ASC;
SELECT * FROM produits ORDER BY prix DESC, nom ASC;

-- Limitation
SELECT * FROM produits LIMIT 10;
SELECT * FROM produits LIMIT 10 OFFSET 20;  -- page 3 (0-indexé)

-- Concaténation
SELECT CONCAT(prenom, ' ', nom) AS nom_complet FROM utilisateurs;

-- Comparaison
SELECT * FROM produits WHERE prix > 50;
SELECT * FROM produits WHERE stock = 0;
SELECT * FROM produits WHERE nom != 'Test';

-- Logique
SELECT * FROM produits WHERE prix > 20 AND stock > 0;
SELECT * FROM produits WHERE categorie = 'A' OR categorie = 'B';
SELECT * FROM produits WHERE NOT (prix > 100);

-- IN / NOT IN
SELECT * FROM produits WHERE categorie IN ('Chaussures', 'Vêtements');
SELECT * FROM produits WHERE id NOT IN (1, 5, 10);

-- BETWEEN
SELECT * FROM produits WHERE prix BETWEEN 10 AND 50;
SELECT * FROM commandes WHERE date_commande BETWEEN '2024-01-01' AND '2024-12-31';

-- LIKE (recherche de motif)
SELECT * FROM utilisateurs WHERE nom LIKE 'Dupont%';   -- commence par
SELECT * FROM utilisateurs WHERE email LIKE '%@gmail.com'; -- finit par
SELECT * FROM utilisateurs WHERE nom LIKE '%ali%';    -- contient
SELECT * FROM utilisateurs WHERE code LIKE '_A_';     -- _ = 1 caractère

-- NULL
SELECT * FROM utilisateurs WHERE telephone IS NULL;
SELECT * FROM utilisateurs WHERE telephone IS NOT NULL;

-- Combinaison complexe
SELECT * FROM produits
WHERE (prix BETWEEN 20 AND 100)
  AND categorie IN ('A', 'B')
  AND stock > 0
  AND nom LIKE '%Pro%';

-- Fonctions d'agrégation
SELECT COUNT(*) AS total FROM utilisateurs;
SELECT COUNT(DISTINCT ville) AS nb_villes FROM utilisateurs;
SELECT SUM(montant) AS total_ventes FROM commandes;
SELECT AVG(prix) AS prix_moyen FROM produits;
SELECT MIN(prix) AS prix_min, MAX(prix) AS prix_max FROM produits;

-- Combinaison
SELECT
  COUNT(*) AS total_commandes,
  SUM(montant) AS chiffre_affaires,
  AVG(montant) AS panier_moyen,
  MIN(montant) AS min_commande,
  MAX(montant) AS max_commande
FROM commandes;

-- GROUP BY
SELECT categorie, COUNT(*) AS nb_produits
FROM produits
GROUP BY categorie;

SELECT ville, COUNT(*) AS nb_clients, AVG(age) AS age_moyen
FROM utilisateurs
GROUP BY ville
ORDER BY nb_clients DESC;

-- HAVING (filtre sur le groupe — WHERE s'applique avant GROUP BY)
SELECT categorie, COUNT(*) AS nb
FROM produits
GROUP BY categorie
HAVING COUNT(*) > 5;

SELECT client_id, SUM(montant) AS total
FROM commandes
GROUP BY client_id
HAVING SUM(montant) > 1000
ORDER BY total DESC;

-- ROLLUP (sous-totaux)
SELECT categorie, sous_categorie, SUM(ventes)
FROM ventes
GROUP BY ROLLUP(categorie, sous_categorie);

-- INNER JOIN — uniquement les lignes qui ont une correspondance
SELECT u.nom, c.montant
FROM utilisateurs u
INNER JOIN commandes c ON u.id = c.user_id;

-- LEFT JOIN — toutes les lignes de gauche, NULL si pas de correspondance
SELECT u.nom, c.montant
FROM utilisateurs u
LEFT JOIN commandes c ON u.id = c.user_id;
-- Clients sans commandes : montant sera NULL

-- RIGHT JOIN — toutes les lignes de droite
SELECT u.nom, c.montant
FROM utilisateurs u
RIGHT JOIN commandes c ON u.id = c.user_id;

-- FULL OUTER JOIN — toutes les lignes des deux côtés
SELECT u.nom, c.montant
FROM utilisateurs u
FULL OUTER JOIN commandes c ON u.id = c.user_id;

-- SELF JOIN — une table jointe à elle-même
SELECT e.nom AS employe, m.nom AS manager
FROM employes e
LEFT JOIN employes m ON e.manager_id = m.id;

-- JOIN multiple
SELECT u.nom, c.montant, p.nom AS produit
FROM utilisateurs u
JOIN commandes c ON u.id = c.user_id
JOIN produits p ON c.produit_id = p.id
WHERE c.montant > 100
ORDER BY c.montant DESC;

-- Clients n'ayant jamais commandé (anti-join)
SELECT u.nom
FROM utilisateurs u
LEFT JOIN commandes c ON u.id = c.user_id
WHERE c.id IS NULL;

-- Insérer une ligne
INSERT INTO utilisateurs (nom, email, age)
VALUES ('Alice', 'alice@exemple.fr', 30);

-- Insérer plusieurs lignes
INSERT INTO produits (nom, prix, stock) VALUES
  ('T-shirt', 29.99, 100),
  ('Pantalon', 59.99, 50),
  ('Veste', 89.99, 25);

-- Insérer depuis une sélection
INSERT INTO archives_commandes (user_id, montant, date)
SELECT user_id, montant, date_commande
FROM commandes
WHERE date_commande < '2023-01-01';

-- INSERT OR UPDATE (MySQL: ON DUPLICATE KEY UPDATE)
INSERT INTO produits (id, nom, stock)
VALUES (1, 'T-shirt', 150)
ON DUPLICATE KEY UPDATE stock = 150;

-- UPSERT (PostgreSQL)
INSERT INTO produits (id, nom, stock)
VALUES (1, 'T-shirt', 150)
ON CONFLICT (id) DO UPDATE SET stock = EXCLUDED.stock;

-- Mise à jour simple
UPDATE utilisateurs SET age = 31 WHERE id = 1;

-- Plusieurs colonnes
UPDATE produits
SET prix = 39.99, stock = 200
WHERE id = 5;

-- Avec calcul
UPDATE produits SET prix = prix * 1.10 WHERE categorie = 'Premium';

-- UPDATE avec JOIN (MySQL)
UPDATE produits p
JOIN categories c ON p.categorie_id = c.id
SET p.prix = p.prix * 1.05
WHERE c.nom = 'Luxe';

-- UPDATE avec sous-requête
UPDATE utilisateurs
SET premium = true
WHERE id IN (
  SELECT user_id FROM commandes
  GROUP BY user_id
  HAVING SUM(montant) > 5000
);

-- Supprimer des lignes
DELETE FROM sessions WHERE expire_at < NOW();

-- Supprimer avec condition
DELETE FROM produits WHERE stock = 0 AND date_ajout < '2022-01-01';

-- DELETE avec JOIN (MySQL)
DELETE c FROM commandes c
JOIN utilisateurs u ON c.user_id = u.id
WHERE u.statut = 'banni';

-- Vider une table (plus rapide que DELETE)
TRUNCATE TABLE sessions;        -- réinitialise aussi l'auto-increment
DELETE FROM sessions;           -- peut être annulé en transaction

-- Soft delete (recommandé en production)
UPDATE utilisateurs SET deleted_at = NOW() WHERE id = 42;
SELECT * FROM utilisateurs WHERE deleted_at IS NULL; -- ignorer les supprimés

-- Créer une table
CREATE TABLE utilisateurs (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  prenom     VARCHAR(50)  NOT NULL,
  nom        VARCHAR(50)  NOT NULL,
  email      VARCHAR(150) NOT NULL UNIQUE,
  age        TINYINT UNSIGNED,
  role       ENUM('admin', 'moderateur', 'user') DEFAULT 'user',
  actif      BOOLEAN DEFAULT true,
  bio        TEXT,
  avatar     VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Clé étrangère
CREATE TABLE commandes (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  montant    DECIMAL(10,2) NOT NULL,
  statut     ENUM('en_attente', 'validee', 'livree', 'annulee') DEFAULT 'en_attente',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES utilisateurs(id) ON DELETE CASCADE
);

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

-- Modifier une colonne
ALTER TABLE utilisateurs MODIFY COLUMN age SMALLINT UNSIGNED;
ALTER TABLE utilisateurs CHANGE COLUMN age annees SMALLINT UNSIGNED;

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

-- Renommer la table
ALTER TABLE utilisateurs RENAME TO users;
RENAME TABLE utilisateurs TO users;

-- Ajouter une contrainte
ALTER TABLE utilisateurs ADD UNIQUE (email);
ALTER TABLE commandes ADD FOREIGN KEY (user_id) REFERENCES utilisateurs(id);
ALTER TABLE utilisateurs ADD INDEX idx_email (email);

-- Supprimer une contrainte
ALTER TABLE utilisateurs DROP INDEX idx_email;
ALTER TABLE commandes DROP FOREIGN KEY fk_user_id;

-- Créer un index
CREATE INDEX idx_email ON utilisateurs(email);
CREATE UNIQUE INDEX idx_email_unique ON utilisateurs(email);
CREATE INDEX idx_composite ON commandes(user_id, created_at);

-- Voir les index
SHOW INDEX FROM utilisateurs;

-- Supprimer un index
DROP INDEX idx_email ON utilisateurs;

-- Sous-requête dans WHERE
SELECT nom FROM produits
WHERE prix > (SELECT AVG(prix) FROM produits);

-- Sous-requête avec IN
SELECT nom FROM utilisateurs
WHERE id IN (SELECT DISTINCT user_id FROM commandes WHERE montant > 500);

-- Sous-requête corrélée
SELECT nom, prix,
  (SELECT AVG(prix) FROM produits p2 WHERE p2.categorie_id = p.categorie_id) AS prix_moy_cat
FROM produits p;

-- EXISTS
SELECT nom FROM clients c
WHERE EXISTS (
  SELECT 1 FROM commandes co WHERE co.client_id = c.id AND co.statut = 'livree'
);

-- CTE (Common Table Expression) — WITH
WITH clients_premium AS (
  SELECT user_id, SUM(montant) AS total
  FROM commandes
  GROUP BY user_id
  HAVING total > 2000
)
SELECT u.nom, cp.total
FROM utilisateurs u
JOIN clients_premium cp ON u.id = cp.user_id
ORDER BY cp.total DESC;

-- Transaction classique
START TRANSACTION;

  UPDATE comptes SET solde = solde - 500 WHERE id = 1;
  UPDATE comptes SET solde = solde + 500 WHERE id = 2;

  -- Si tout est bon
  COMMIT;

  -- En cas d'erreur
  ROLLBACK;

-- Avec vérification
START TRANSACTION;

  UPDATE stock SET quantite = quantite - 1 WHERE produit_id = 5;

  -- Vérifier le stock
  SET @stock = (SELECT quantite FROM stock WHERE produit_id = 5);

  IF @stock >= 0 THEN
    COMMIT;
  ELSE
    ROLLBACK;
  END IF;

-- SAVEPOINT (points de sauvegarde intermédiaires)
START TRANSACTION;
  INSERT INTO ...;
  SAVEPOINT sp1;
  UPDATE ...;
  ROLLBACK TO sp1;  -- annule jusqu'au savepoint
  COMMIT;

SELECT CONCAT('Hello', ' ', 'World');        -- 'Hello World'
SELECT CONCAT_WS(', ', nom, prenom);         -- joint avec séparateur
SELECT LENGTH('Bonjour');                    -- 7 (octets)
SELECT CHAR_LENGTH('Bonjour');               -- 7 (caractères)
SELECT UPPER('hello');                       -- 'HELLO'
SELECT LOWER('HELLO');                       -- 'hello'
SELECT TRIM('  espaces  ');                  -- 'espaces'
SELECT LTRIM('  gauche');
SELECT RTRIM('droite  ');
SELECT SUBSTRING('Bonjour', 2, 4);           -- 'onjo' (pos, longueur)
SELECT LEFT('Bonjour', 3);                   -- 'Bon'
SELECT RIGHT('Bonjour', 4);                  -- 'jour'
SELECT REPLACE('foo bar', 'bar', 'baz');     -- 'foo baz'
SELECT INSTR('Bonjour', 'jour');             -- 4 (position)
SELECT LPAD('5', 3, '0');                    -- '005'
SELECT RPAD('5', 3, '0');                    -- '500'
SELECT REPEAT('ab', 3);                      -- 'ababab'
SELECT REVERSE('abc');                       -- 'cba'

SELECT ROUND(3.14159, 2);                    -- 3.14
SELECT CEIL(4.2);                            -- 5
SELECT FLOOR(4.8);                           -- 4
SELECT ABS(-42);                             -- 42
SELECT MOD(10, 3);                           -- 1
SELECT POWER(2, 10);                         -- 1024
SELECT SQRT(144);                            -- 12
SELECT RAND();                               -- nombre aléatoire 0-1
SELECT TRUNCATE(3.14159, 2);                 -- 3.14 (pas d'arrondi)
SELECT FORMAT(1234567.89, 2);               -- '1,234,567.89'

SELECT NOW();                                -- '2024-03-15 14:30:00'
SELECT CURDATE();                            -- '2024-03-15'
SELECT CURTIME();                            -- '14:30:00'
SELECT YEAR(NOW());                          -- 2024
SELECT MONTH(NOW());                         -- 3
SELECT DAY(NOW());                           -- 15
SELECT DAYNAME(NOW());                       -- 'Friday'
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y %H:%i'); -- '15/03/2024 14:30'
SELECT DATE_ADD(NOW(), INTERVAL 30 DAY);     -- dans 30 jours
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);    -- il y a 1 mois
SELECT DATEDIFF('2024-12-31', '2024-01-01'); -- 365
SELECT TIMESTAMPDIFF(YEAR, date_naissance, CURDATE()) AS age FROM utilisateurs;

SELECT nom,
  IF(stock > 0, 'En stock', 'Rupture') AS disponibilite,
  IFNULL(telephone, 'Non renseigné') AS tel,
  NULLIF(age, 0) AS age_reel,
  CASE
    WHEN age < 18 THEN 'Mineur'
    WHEN age < 65 THEN 'Adulte'
    ELSE 'Senior'
  END AS categorie_age
FROM utilisateurs;

-- COALESCE : retourne la première valeur non-NULL
SELECT COALESCE(telephone, mobile, email, 'Aucun contact') AS contact
FROM utilisateurs;