MySQL & Base de données : Requêtes SQL

Bases de données et tables

Gestion des bases de données

-- Créer base de données
CREATE DATABASE mabase;
CREATE DATABASE IF NOT EXISTS mabase;
CREATE DATABASE mabase CHARACTER SET utf8mb4 COLLATE utf8mb4unicodeci;

-- Utiliser base de données
USE mabase;

-- Lister bases de données
SHOW DATABASES;

-- Supprimer base de données
DROP DATABASE mabase;
DROP DATABASE IF EXISTS mabase;

-- Informations base de données
SHOW CREATE DATABASE mabase;
SELECT DATABASE(); -- Base actuelle

-- Table basique
CREATE TABLE users (
    id INT PRIMARY KEY AUTOINCREMENT,
    nom VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    age INT,
    createdat TIMESTAMP DEFAULT CURRENTTIMESTAMP
);

-- Table complète avec contraintes
CREATE TABLE articles (
    id INT UNSIGNED AUTOINCREMENT,
    titre VARCHAR(255) NOT NULL,
    contenu TEXT,
    auteurid INT UNSIGNED NOT NULL,
    categorieid INT UNSIGNED,
    statut ENUM('brouillon', 'publie', 'archive') DEFAULT 'brouillon',
    vues INT UNSIGNED DEFAULT 0,
    note DECIMAL(3,2),
    createdat TIMESTAMP DEFAULT CURRENTTIMESTAMP,
    updatedat TIMESTAMP DEFAULT CURRENTTIMESTAMP ON UPDATE CURRENTTIMESTAMP,

    PRIMARY KEY (id),
    INDEX idxauteur (auteurid),
    INDEX idxcategorie (categorieid),
    INDEX idxstatut (statut),
    FULLTEXT INDEX idxrecherche (titre, contenu),

    CONSTRAINT fkauteur
        FOREIGN KEY (auteurid)
        REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fkcategorie
        FOREIGN KEY (categorieid)
        REFERENCES categories(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4unicodeci;

-- Table temporaire
CREATE TEMPORARY TABLE tempdata (
    id INT,
    value VARCHAR(50)
);

-- Créer table depuis requête
CREATE TABLE usersbackup AS
SELECT  FROM users WHERE createdat < '2024-01-01';

-- Copier structure seulement
CREATE TABLE userscopy LIKE users;

-- Numériques
TINYINT       -- -128 à 127 (1 octet)
SMALLINT      -- -32768 à 32767 (2 octets)
MEDIUMINT     -- -8388608 à 8388607 (3 octets)
INT           -- -2147483648 à 2147483647 (4 octets)
BIGINT        -- ±9.22×10^18 (8 octets)
DECIMAL(10,2) -- Nombres décimaux exacts
FLOAT         -- Nombres flottants (4 octets)
DOUBLE        -- Double précision (8 octets)

-- Chaînes
CHAR(10)      -- Longueur fixe (0-255)
VARCHAR(255)  -- Longueur variable (0-65535)
TINYTEXT      -- Texte court (255 caractères)
TEXT          -- Texte moyen (65535 caractères)
MEDIUMTEXT    -- Texte long (16MB)
LONGTEXT      -- Texte très long (4GB)

-- Dates et heures
DATE          -- 'YYYY-MM-DD'
TIME          -- 'HH:MM:SS'
DATETIME      -- 'YYYY-MM-DD HH:MM:SS' (1000-9999)
TIMESTAMP     -- 'YYYY-MM-DD HH:MM:SS' (1970-2038, timezone)
YEAR          -- Année (1901-2155)

-- Autres
ENUM('val1', 'val2', 'val3')  -- Énumération
SET('opt1', 'opt2', 'opt3')   -- Ensemble de valeurs
BOOLEAN       -- Alias de TINYINT(1)
BLOB          -- Données binaires
JSON          -- Données JSON (MySQL 5.7+)

-- Modificateurs
UNSIGNED      -- Nombres positifs uniquement
ZEROFILL      -- Remplit de zéros
NOT NULL      -- Obligatoire
DEFAULT value -- Valeur par défaut
AUTOINCREMENT -- Incrémentation automatique

-- Ajouter colonne
ALTER TABLE users ADD COLUMN telephone VARCHAR(20);
ALTER TABLE users ADD COLUMN adresse TEXT AFTER email;
ALTER TABLE users ADD COLUMN code CHAR(10) FIRST;

-- Modifier colonne
ALTER TABLE users MODIFY COLUMN age TINYINT UNSIGNED;
ALTER TABLE users CHANGE COLUMN nom nomcomplet VARCHAR(200);

-- Supprimer colonne
ALTER TABLE users DROP COLUMN telephone;

-- Ajouter index
ALTER TABLE users ADD INDEX idxnom (nom);
ALTER TABLE users ADD UNIQUE INDEX idxemail (email);
ALTER TABLE users ADD FULLTEXT INDEX idxrecherche (nom, email);

-- Supprimer index
ALTER TABLE users DROP INDEX idxnom;

-- Ajouter clé primaire
ALTER TABLE users ADD PRIMARY KEY (id);

-- Supprimer clé primaire
ALTER TABLE users DROP PRIMARY KEY;

-- Ajouter clé étrangère
ALTER TABLE articles
    ADD CONSTRAINT fkauteur
    FOREIGN KEY (auteurid)
    REFERENCES users(id)
    ON DELETE CASCADE;

-- Supprimer clé étrangère
ALTER TABLE articles DROP FOREIGN KEY fkauteur;

-- Renommer table
ALTER TABLE users RENAME TO membres;
RENAME TABLE users TO membres;

-- Changer moteur
ALTER TABLE users ENGINE=InnoDB;

-- Changer charset
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4unicodeci;

-- Voir structure table
DESCRIBE users;
DESC users;
SHOW COLUMNS FROM users;
SHOW CREATE TABLE users;

-- Lister tables
SHOW TABLES;
SHOW TABLES LIKE 'user%';

-- Supprimer table
DROP TABLE users;
DROP TABLE IF EXISTS users;
TRUNCATE TABLE users; -- Vide table (plus rapide que DELETE)

-- Tout sélectionner
SELECT  FROM users;

-- Colonnes spécifiques
SELECT nom, email FROM users;

-- Alias
SELECT nom AS nomutilisateur, email AS adresseemail FROM users;
SELECT u.nom, u.email FROM users AS u;
SELECT u.nom, u.email FROM users u; -- AS optionnel

-- Distinct (valeurs uniques)
SELECT DISTINCT ville FROM users;

-- Limiter résultats
SELECT  FROM users LIMIT 10;
SELECT  FROM users LIMIT 10 OFFSET 20; -- 10 résultats à partir du 20e
SELECT  FROM users LIMIT 20, 10; -- Syntaxe alternative

-- Comparaisons
SELECT  FROM users WHERE age > 18;
SELECT  FROM users WHERE age >= 18;
SELECT  FROM users WHERE age < 65;
SELECT  FROM users WHERE age != 30;
SELECT  FROM users WHERE age <> 30; -- Même que !=
SELECT  FROM users WHERE nom = 'Jean';

-- Opérateurs logiques
SELECT  FROM users WHERE age > 18 AND age < 65;
SELECT  FROM users WHERE ville = 'Paris' OR ville = 'Lyon';
SELECT  FROM users WHERE NOT actif = 1;
SELECT  FROM users WHERE age > 18 AND (ville = 'Paris' OR ville = 'Lyon');

-- BETWEEN
SELECT  FROM users WHERE age BETWEEN 18 AND 65;
SELECT  FROM users WHERE createdat BETWEEN '2024-01-01' AND '2024-12-31';

-- IN
SELECT  FROM users WHERE ville IN ('Paris', 'Lyon', 'Marseille');
SELECT  FROM users WHERE id IN (1, 5, 10, 15);

-- LIKE (recherche partielle)
SELECT  FROM users WHERE nom LIKE 'Jean%'; -- Commence par Jean
SELECT  FROM users WHERE nom LIKE '%Dupont'; -- Finit par Dupont
SELECT  FROM users WHERE email LIKE '%@gmail.com'; -- Contient
SELECT  FROM users WHERE nom LIKE 'Jan'; --  = 1 caractère

-- REGEXP (expressions régulières)
SELECT  FROM users WHERE email REGEXP '^[a-z]+@gmail.com$';
SELECT  FROM users WHERE telephone REGEXP '^0[1-9][0-9]{8}$';

-- IS NULL / IS NOT NULL
SELECT  FROM users WHERE telephone IS NULL;
SELECT  FROM users WHERE telephone IS NOT NULL;
SELECT  FROM users WHERE email IS NULL OR email = '';

-- FULLTEXT (recherche texte intégral)
SELECT  FROM articles WHERE MATCH(titre, contenu) AGAINST('recherche');
SELECT  FROM articles WHERE MATCH(titre, contenu) AGAINST('+mysql -oracle' IN BOOLEAN MODE);

ORDER BY (tri)

-- Tri simple
SELECT  FROM users ORDER BY nom;
SELECT  FROM users ORDER BY nom ASC; -- Croissant (défaut)
SELECT  FROM users ORDER BY nom DESC; -- Décroissant

-- Tri multiple
SELECT  FROM users ORDER BY ville, nom;
SELECT  FROM users ORDER BY ville ASC, age DESC;

-- Tri par colonne calculée
SELECT nom, age, (age  365) AS jours FROM users ORDER BY jours DESC;

-- Tri aléatoire
SELECT  FROM users ORDER BY RAND() LIMIT 10;

-- Tri avec FIELD (ordre personnalisé)
SELECT  FROM users ORDER BY FIELD(ville, 'Paris', 'Lyon', 'Marseille'), nom;

-- Tri NULL en premier/dernier
SELECT  FROM users ORDER BY telephone IS NULL, telephone;

-- Regroupement simple
SELECT ville, COUNT() as nombre FROM users GROUP BY ville;

-- Fonctions d'agrégation
SELECT ville,
       COUNT() as total,
       AVG(age) as agemoyen,
       MIN(age) as agemin,
       MAX(age) as agemax,
       SUM(salaire) as salairetotal
FROM users
GROUP BY ville;

-- GROUP BY multiple
SELECT ville, profession, COUNT() FROM users GROUP BY ville, profession;

-- HAVING (filtre après regroupement)
SELECT ville, COUNT() as nombre
FROM users
GROUP BY ville
HAVING nombre > 100;

SELECT ville, AVG(age) as agemoyen
FROM users
GROUP BY ville
HAVING agemoyen > 30;

-- GROUP BY avec ROLLUP (totaux)
SELECT ville, COUNT() FROM users GROUP BY ville WITH ROLLUP;

-- Filtrer groupes vides
SELECT ville, COUNT() as nombre
FROM users
WHERE actif = 1
GROUP BY ville
HAVING nombre > 0
ORDER BY nombre DESC;

-- COUNT
SELECT COUNT() FROM users; -- Compte toutes lignes
SELECT COUNT(telephone) FROM users; -- Compte non-NULL
SELECT COUNT(DISTINCT ville) FROM users; -- Valeurs uniques

-- SUM
SELECT SUM(montant) FROM commandes;
SELECT SUM(quantite  prix) as total FROM lignescommande;

-- AVG (moyenne)
SELECT AVG(age) FROM users;
SELECT AVG(note) as moyenne FROM evaluations;

-- MIN / MAX
SELECT MIN(age), MAX(age) FROM users;
SELECT MIN(createdat) as premier, MAX(createdat) as dernier FROM users;

-- GROUPCONCAT (concaténation)
SELECT auteurid, GROUPCONCAT(titre SEPARATOR ', ') as articles
FROM articles
GROUP BY auteurid;

-- STDDEV / VARIANCE (statistiques)
SELECT STDDEV(age) as ecarttype FROM users;
SELECT VARIANCE(salaire) FROM users;

-- INNER JOIN (correspondances uniquement)
SELECT u.nom, a.titre
FROM users u
INNER JOIN articles a ON u.id = a.auteurid;

-- LEFT JOIN (toutes lignes table gauche)
SELECT u.nom, a.titre
FROM users u
LEFT JOIN articles a ON u.id = a.auteurid;

-- RIGHT JOIN (toutes lignes table droite)
SELECT u.nom, a.titre
FROM users u
RIGHT JOIN articles a ON u.id = a.auteurid;

-- FULL OUTER JOIN (toutes lignes des deux) - simulé
SELECT u.nom, a.titre FROM users u LEFT JOIN articles a ON u.id = a.auteurid
UNION
SELECT u.nom, a.titre FROM users u RIGHT JOIN articles a ON u.id = a.auteurid;

-- CROSS JOIN (produit cartésien)
SELECT u.nom, c.nomcategorie
FROM users u
CROSS JOIN categories c;

-- SELF JOIN (table avec elle-même)
SELECT e1.nom as employe, e2.nom as manager
FROM employes e1
LEFT JOIN employes e2 ON e1.managerid = e2.id;

-- 3 tables
SELECT u.nom, a.titre, c.nom as categorie
FROM users u
INNER JOIN articles a ON u.id = a.auteurid
INNER JOIN categories c ON a.categorieid = c.id;

-- Conditions multiples
SELECT 
FROM commandes c
INNER JOIN lignescommande lc ON c.id = lc.commandeid AND c.statut = 'validee';

-- Sous-requêtes dans JOIN
SELECT u.nom, stats.totalarticles
FROM users u
LEFT JOIN (
    SELECT auteurid, COUNT() as totalarticles
    FROM articles
    GROUP BY auteurid
) stats ON u.id = stats.auteurid;

-- Utilisateurs avec nombre d'articles
SELECT u.id, u.nom, COUNT(a.id) as nombrearticles
FROM users u
LEFT JOIN articles a ON u.id = a.auteurid
GROUP BY u.id, u.nom
ORDER BY nombrearticles DESC;

-- Articles avec auteur et catégorie
SELECT
    a.id,
    a.titre,
    u.nom as auteur,
    c.nom as categorie,
    a.createdat
FROM articles a
INNER JOIN users u ON a.auteurid = u.id
LEFT JOIN categories c ON a.categorieid = c.id
WHERE a.statut = 'publie'
ORDER BY a.createdat DESC;

-- Trouver utilisateurs sans articles
SELECT u.id, u.nom
FROM users u
LEFT JOIN articles a ON u.id = a.auteurid
WHERE a.id IS NULL;

-- Dans SELECT
SELECT
    nom,
    (SELECT COUNT() FROM articles WHERE auteurid = u.id) as nombrearticles
FROM users u;

-- Dans WHERE
SELECT  FROM articles
WHERE auteurid = (SELECT id FROM users WHERE nom = 'Jean Dupont');

SELECT  FROM articles
WHERE categorieid IN (SELECT id FROM categories WHERE actif = 1);

-- Comparaison avec sous-requête
SELECT  FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- EXISTS
SELECT u.nom FROM users u
WHERE EXISTS (
    SELECT 1 FROM articles a WHERE a.auteurid = u.id AND a.statut = 'publie'
);

-- NOT EXISTS
SELECT u.nom FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM articles a WHERE a.auteurid = u.id
);

-- Sous-requête dans SELECT corrélée
SELECT
    a.titre,
    a.vues,
    (SELECT AVG(vues) FROM articles WHERE categorieid = a.categorieid) as moyennecategorie
FROM articles a;

-- ANY / SOME
SELECT  FROM articles
WHERE vues > ANY (SELECT vues FROM articles WHERE categorieid = 5);

-- ALL
SELECT  FROM articles
WHERE vues > ALL (SELECT vues FROM articles WHERE categorieid = 5);

-- IN
SELECT  FROM users
WHERE id IN (SELECT DISTINCT auteurid FROM articles);

-- NOT IN
SELECT  FROM users
WHERE id NOT IN (SELECT auteurid FROM articles WHERE auteurid IS NOT NULL);

-- Insert simple
INSERT INTO users (nom, email, age) VALUES ('Jean', 'jean@example.com', 30);

-- Insert multiple
INSERT INTO users (nom, email, age) VALUES
    ('Marie', 'marie@example.com', 25),
    ('Pierre', 'pierre@example.com', 35),
    ('Sophie', 'sophie@example.com', 28);

-- Insert depuis SELECT
INSERT INTO usersarchive (nom, email, age)
SELECT nom, email, age FROM users WHERE createdat < '2020-01-01';

-- Insert avec valeurs par défaut
INSERT INTO users (nom, email) VALUES ('Jean', 'jean@example.com');

-- Insert ou update si existe (ON DUPLICATE KEY)
INSERT INTO users (id, nom, email) VALUES (1, 'Jean', 'jean@example.com')
ON DUPLICATE KEY UPDATE nom = 'Jean', email = 'jean@example.com';

-- Insert ignore (ignore si existe)
INSERT IGNORE INTO users (email, nom) VALUES ('jean@example.com', 'Jean');

-- Insert delayed (file d'attente)
INSERT DELAYED INTO logs (message) VALUES ('Log message');

-- Récupérer dernier ID inséré
INSERT INTO users (nom) VALUES ('Jean');
SELECT LASTINSERTID();

-- Update simple
UPDATE users SET age = 31 WHERE id = 1;

-- Update multiple colonnes
UPDATE users SET nom = 'Jean Dupont', age = 31, updatedat = NOW() WHERE id = 1;

-- Update avec calcul
UPDATE articles SET vues = vues + 1 WHERE id = 5;
UPDATE users SET salaire = salaire  1.1 WHERE anciennete > 5;

-- Update avec JOIN
UPDATE users u
INNER JOIN commandes c ON u.id = c.userid
SET u.dernierachat = NOW()
WHERE c.id = 123;

-- Update conditionnel (CASE)
UPDATE users SET
    statut = CASE
        WHEN age < 18 THEN 'mineur'
        WHEN age >= 18 AND age < 65 THEN 'actif'
        ELSE 'senior'
    END;

-- Update avec sous-requête
UPDATE articles SET vues = (
    SELECT AVG(vues) FROM (SELECT  FROM articles) as temp
) WHERE vues IS NULL;

-- Update avec LIMIT
UPDATE users SET actif = 0 ORDER BY lastlogin LIMIT 100;

-- Delete simple
DELETE FROM users WHERE id = 1;

-- Delete multiple
DELETE FROM users WHERE age < 18;

-- Delete avec JOIN
DELETE u FROM users u
INNER JOIN articles a ON u.id = a.auteurid
WHERE a.statut = 'spam';

-- Delete avec sous-requête
DELETE FROM articles WHERE auteurid IN (
    SELECT id FROM users WHERE actif = 0
);

-- Delete avec LIMIT
DELETE FROM logs ORDER BY createdat LIMIT 1000;

-- Truncate (vide table, plus rapide)
TRUNCATE TABLE logs;

-- Delete tout (déconseillé sans WHERE)
DELETE FROM temptable;

-- CONCAT
SELECT CONCAT(prenom, ' ', nom) as nomcomplet FROM users;
SELECT CONCATWS('-', annee, mois, jour) as dateformatee FROM dates;

-- SUBSTRING
SELECT SUBSTRING(texte, 1, 10) FROM articles; -- 10 premiers caractères
SELECT SUBSTRING(texte, -5) FROM articles; -- 5 derniers

-- LENGTH
SELECT nom, LENGTH(nom) as longueur FROM users;
SELECT  FROM users WHERE LENGTH(motdepasse) < 8;

-- UPPER / LOWER
SELECT UPPER(nom) FROM users;
SELECT LOWER(email) FROM users;

-- TRIM
SELECT TRIM(nom) FROM users; -- Retire espaces
SELECT LTRIM(nom) FROM users; -- Gauche
SELECT RTRIM(nom) FROM users; -- Droite
SELECT TRIM(BOTH '-' FROM code) FROM users; -- Caractère spécifique

-- REPLACE
SELECT REPLACE(texte, 'ancien', 'nouveau') FROM articles;
SELECT REPLACE(telephone, '-', '') FROM users;

-- LEFT / RIGHT
SELECT LEFT(code, 3) FROM produits; -- 3 premiers caractères
SELECT RIGHT(code, 3) FROM produits; -- 3 derniers

-- LOCATE / POSITION
SELECT LOCATE('@', email) as positionarobase FROM users;
SELECT POSITION('.' IN email) FROM users;

-- REVERSE
SELECT REVERSE(code) FROM produits;

-- REPEAT
SELECT REPEAT('', 5); -- ''

-- SPACE
SELECT CONCAT(nom, SPACE(10), prenom) FROM users;

-- FORMAT
SELECT FORMAT(prix, 2) FROM produits; -- 2 décimales avec séparateurs

Fonctions numériques

-- Arrondis
SELECT ROUND(prix, 2) FROM produits; -- 2 décimales
SELECT CEILING(prix) FROM produits; -- Arrondi supérieur
SELECT FLOOR(prix) FROM produits; -- Arrondi inférieur
SELECT TRUNCATE(prix, 2) FROM produits; -- Tronque

-- Valeur absolue
SELECT ABS(difference) FROM stats;

-- Puissance et racine
SELECT POWER(2, 3); -- 8
SELECT POW(2, 3); -- 8
SELECT SQRT(16); -- 4

-- Modulo
SELECT MOD(10, 3); -- 1
SELECT 10 % 3; -- 1

-- Aléatoire
SELECT RAND(); -- 0-1
SELECT FLOOR(RAND()  100); -- 0-99
SELECT  FROM users ORDER BY RAND() LIMIT 10;

-- Signe
SELECT SIGN(-5); -- -1
SELECT SIGN(0); -- 0
SELECT SIGN(5); -- 1

-- Min/Max
SELECT GREATEST(10, 20, 5); -- 20
SELECT LEAST(10, 20, 5); -- 5

Fonctions de dates

-- Date actuelle
SELECT NOW(); -- Date et heure
SELECT CURDATE(); -- Date seulement
SELECT CURTIME(); -- Heure seulement
SELECT CURRENTTIMESTAMP();

-- Extraire parties
SELECT YEAR(createdat) FROM articles;
SELECT MONTH(createdat) FROM articles;
SELECT DAY(createdat) FROM articles;
SELECT HOUR(createdat) FROM articles;
SELECT MINUTE(createdat) FROM articles;
SELECT SECOND(createdat) FROM articles;
SELECT DAYOFWEEK(createdat) FROM articles; -- 1=dimanche
SELECT DAYOFYEAR(createdat) FROM articles; -- 1-366
SELECT WEEK(createdat) FROM articles;
SELECT QUARTER(createdat) FROM articles;

-- Formater dates
SELECT DATEFORMAT(createdat, '%d/%m/%Y') FROM articles;
SELECT DATEFORMAT(createdat, '%d %M %Y %H:%i:%s') FROM articles;
-- %Y: année 4 chiffres, %y: 2 chiffres
-- %m: mois numérique, %M: nom mois, %b: nom court
-- %d: jour, %H: heure 24h, %h: heure 12h
-- %i: minutes, %s: secondes

-- Calculs dates
SELECT DATEADD(createdat, INTERVAL 7 DAY) FROM articles;
SELECT DATESUB(createdat, INTERVAL 1 MONTH) FROM articles;
SELECT createdat + INTERVAL 1 YEAR FROM articles;

-- Différence dates
SELECT DATEDIFF(NOW(), createdat) as jours FROM articles;
SELECT TIMESTAMPDIFF(MONTH, createdat, NOW()) as mois FROM articles;
-- TIMESTAMPDIFF(unit, date1, date2): SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR

-- Créer dates
SELECT MAKEDATE(2024, 350); -- 350e jour de 2024
SELECT MAKETIME(10, 30, 45); -- 10:30:45

-- Dernier jour du mois
SELECT LASTDAY('2024-02-15'); -- 2024-02-29

-- Unix timestamp
SELECT UNIXTIMESTAMP(createdat) FROM articles;
SELECT FROMUNIXTIME(1703001234) as date;

Fonctions conditionnelles

-- IF
SELECT nom, IF(age >= 18, 'Majeur', 'Mineur') as statut FROM users;

-- CASE
SELECT nom,
    CASE
        WHEN age < 18 THEN 'Mineur'
        WHEN age >= 18 AND age < 65 THEN 'Actif'
        ELSE 'Senior'
    END as categorie
FROM users;

-- CASE avec égalité
SELECT nom,
    CASE statut
        WHEN 'actif' THEN 'Utilisateur actif'
        WHEN 'inactif' THEN 'Utilisateur inactif'
        WHEN 'suspendu' THEN 'Compte suspendu'
        ELSE 'Statut inconnu'
    END as statutlibelle
FROM users;

-- IFNULL
SELECT nom, IFNULL(telephone, 'Non renseigné') FROM users;

-- COALESCE (première valeur non-NULL)
SELECT COALESCE(telephone, mobile, 'Aucun contact') FROM users;

-- NULLIF (NULL si valeurs égales)
SELECT NULLIF(valeur1, valeur2) FROM table;

-- Base de données
SELECT DATABASE(); -- Base actuelle
SELECT USER(); -- Utilisateur actuel
SELECT VERSION(); -- Version MySQL

-- Dernier ID inséré
SELECT LASTINSERTID();

-- Nombre de lignes affectées
SELECT ROWCOUNT();

-- Cast type
SELECT CAST('123' AS UNSIGNED);
SELECT CAST(prix AS CHAR) FROM produits;
SELECT CONVERT('123', UNSIGNED);

Optimisation et index

Index

-- Créer index simple
CREATE INDEX idxnom ON users(nom);

-- Index unique
CREATE UNIQUE INDEX idxemail ON users(email);

-- Index composite
CREATE INDEX idxvilleage ON users(ville, age);

-- Index fulltext
CREATE FULLTEXT INDEX idxrecherche ON articles(titre, contenu);

-- Index dans CREATE TABLE
CREATE TABLE users (
    id INT PRIMARY KEY,
    nom VARCHAR(100),
    email VARCHAR(150),
    ville VARCHAR(50),
    INDEX idxnom (nom),
    UNIQUE INDEX idxemail (email),
    INDEX idxville (ville)
);

-- Voir index
SHOW INDEX FROM users;

-- Supprimer index
DROP INDEX idxnom ON users;
ALTER TABLE users DROP INDEX idxnom;

-- Optimiser index
OPTIMIZE TABLE users;

-- Analyser index
ANALYZE TABLE users;

EXPLAIN (analyse requêtes)

-- Analyser requête
EXPLAIN SELECT  FROM users WHERE ville = 'Paris';

-- Format détaillé
EXPLAIN FORMAT=JSON SELECT  FROM users WHERE ville = 'Paris';

-- Colonnes importantes:
-- type: ALL (mauvais), index, ref, range, const (bon)
-- possiblekeys: index potentiels
-- key: index utilisé
-- rows: lignes examinées
-- Extra: informations supplémentaires

-- Exemple analyse
EXPLAIN SELECT u.nom, COUNT(a.id)
FROM users u
LEFT JOIN articles a ON u.id = a.auteurid
WHERE u.ville = 'Paris'
GROUP BY u.id;

Optimisation requêtes

-- Éviter SELECT 
-- Mauvais:
SELECT  FROM users;
-- Bon:
SELECT id, nom, email FROM users;

-- Utiliser LIMIT
SELECT  FROM articles ORDER BY createdat DESC LIMIT 10;

-- Index pour WHERE, ORDER BY, JOIN
CREATE INDEX idxville ON users(ville);
SELECT  FROM users WHERE ville = 'Paris';

-- Éviter fonctions sur colonnes indexées
-- Mauvais:
SELECT  FROM users WHERE YEAR(createdat) = 2024;
-- Bon:
SELECT  FROM users WHERE createdat BETWEEN '2024-01-01' AND '2024-12-31';

-- EXISTS vs IN pour grandes tables
-- Bon pour grandes tables:
SELECT  FROM users WHERE EXISTS (
    SELECT 1 FROM articles WHERE auteurid = users.id
);

-- Pagination efficace
-- Éviter OFFSET élevé:
SELECT  FROM articles WHERE id > 1000 ORDER BY id LIMIT 10;

-- Optimiser JOIN
-- S'assurer colonnes JOIN sont indexées
CREATE INDEX idxauteurid ON articles(auteurid);
SELECT  FROM users u INNER JOIN articles a ON u.id = a.auteurid;

-- Partitioning (grandes tables)
CREATE TABLE logs (
    id INT,
    message TEXT,
    createdat DATETIME
) PARTITION BY RANGE (YEAR(createdat)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

-- Démarrer transaction
START TRANSACTION;
BEGIN;

-- Valider
COMMIT;

-- Annuler
ROLLBACK;

-- Exemple complet
START TRANSACTION;

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

-- Si tout OK:
COMMIT;
-- Si erreur:
-- ROLLBACK;

-- Savepoint (point de sauvegarde)
START TRANSACTION;

INSERT INTO users (nom) VALUES ('Jean');
SAVEPOINT sp1;

INSERT INTO users (nom) VALUES ('Marie');
SAVEPOINT sp2;

-- Annuler jusqu'à sp2
ROLLBACK TO sp2;

COMMIT;

-- Isolation level
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Autocommit
SET autocommit = 0; -- Désactive
SET autocommit = 1; -- Active (défaut)

-- Structure tables principales
wpposts          -- Articles, pages, custom post types
wppostmeta       -- Metadata posts
wpusers          -- Utilisateurs
wpusermeta       -- Metadata utilisateurs
wpcomments       -- Commentaires
wpcommentmeta    -- Metadata commentaires
wpterms          -- Termes (catégories, tags)
wptermtaxonomy  -- Relations taxonomies
wptermrelationships -- Relations posts/termes
wpoptions        -- Options site
wptermmeta       -- Metadata termes

-- Requêtes WordPress courantes

-- Tous les articles publiés
SELECT  FROM wpposts
WHERE posttype = 'post'
AND poststatus = 'publish'
ORDER BY postdate DESC;

-- Articles avec meta
SELECT p., pm.metavalue as prix
FROM wpposts p
LEFT JOIN wppostmeta pm ON p.ID = pm.postid AND pm.metakey = 'prix'
WHERE p.posttype = 'product'
AND p.poststatus = 'publish';

-- Articles avec catégories
SELECT p.posttitle, t.name as categorie
FROM wpposts p
INNER JOIN wptermrelationships tr ON p.ID = tr.objectid
INNER JOIN wptermtaxonomy tt ON tr.termtaxonomyid = tt.termtaxonomyid
INNER JOIN wpterms t ON tt.termid = t.termid
WHERE p.posttype = 'post'
AND p.poststatus = 'publish'
AND tt.taxonomy = 'category';

-- Nombre articles par auteur
SELECT u.displayname, COUNT(p.ID) as nombrearticles
FROM wpusers u
LEFT JOIN wpposts p ON u.ID = p.postauthor AND p.posttype = 'post' AND p.poststatus = 'publish'
GROUP BY u.ID
ORDER BY nombrearticles DESC;

-- Options WordPress
SELECT optionvalue FROM wpoptions WHERE optionname = 'siteurl';

UPDATE wpoptions SET optionvalue = 'https://nouveau-site.com'
WHERE optionname IN ('siteurl', 'home');

-- Nettoyer révisions
DELETE FROM wpposts WHERE posttype = 'revision';

-- Nettoyer auto-drafts
DELETE FROM wpposts WHERE poststatus = 'auto-draft';

-- Nettoyer transients expirés
DELETE FROM wpoptions
WHERE optionname LIKE 'transienttimeout%'
AND optionvalue < UNIXTIMESTAMP();

DELETE FROM wpoptions
WHERE optionname LIKE 'transient%'
AND optionname NOT LIKE 'transienttimeout%'
AND optionname IN (
    SELECT CONCAT('transient', SUBSTRING(optionname, 20))
    FROM wpoptions
    WHERE optionname LIKE 'transienttimeout%'
    AND optionvalue < UNIXTIMESTAMP()
);

-- Changer URL site (migration)
UPDATE wpoptions SET optionvalue = REPLACE(optionvalue, 'http://ancien-site.com', 'https://nouveau-site.com');
UPDATE wpposts SET guid = REPLACE(guid, 'http://ancien-site.com', 'https://nouveau-site.com');
UPDATE wpposts SET postcontent = REPLACE(postcontent, 'http://ancien-site.com', 'https://nouveau-site.com');
UPDATE wppostmeta SET metavalue = REPLACE(metavalue, 'http://ancien-site.com', 'https://nouveau-site.com');

-- Trouver posts sans featured image
SELECT p.ID, p.posttitle
FROM wpposts p
LEFT JOIN wppostmeta pm ON p.ID = pm.postid AND pm.metakey = 'thumbnailid'
WHERE p.posttype = 'post'
AND p.poststatus = 'publish'
AND pm.metaid IS NULL;

Export base complète
mysqldump -u user -p mabase > backup.sql

Export avec structure et données
mysqldump -u user -p mabase > backup.sql

Export structure seulement
mysqldump -u user -p --no-data mabase > structure.sql

Export données seulement
mysqldump -u user -p --no-create-info mabase > data.sql

Export tables spécifiques
mysqldump -u user -p mabase table1 table2 > tables.sql

Export avec compression
mysqldump -u user -p mabase | gzip > backup.sql.gz

Export toutes bases
mysqldump -u user -p --all-databases > alldatabases.sql

Export avec routines et triggers
mysqldump -u user -p --routines --triggers mabase > backup.sql

Import simple
mysql -u user -p mabase < backup.sql

Import avec création base
mysql -u user -p < backup.sql

Import décompressé
gunzip < backup.sql.gz | mysql -u user -p mabase

Dans MySQL
SOURCE /chemin/vers/backup.sql;

-- Vérifier tables
CHECK TABLE users;
CHECK TABLE users, articles;

-- Réparer tables
REPAIR TABLE users;

-- Optimiser tables
OPTIMIZE TABLE users;
OPTIMIZE TABLE users, articles, comments;

-- Analyser tables
ANALYZE TABLE users;

-- Voir taille tables
SELECT
    tablename AS 'Table',
    ROUND(((datalength + indexlength) / 1024 / 1024), 2) AS 'Taille (MB)'
FROM informationschema.TABLES
WHERE tableschema = 'mabase'
ORDER BY (datalength + indexlength) DESC;

-- Vider cache de requêtes
RESET QUERY CACHE;

-- Tuer processus
SHOW PROCESSLIST;
KILL processid;

-- Toujours utiliser prepared statements (depuis code application)
-- JAMAIS de concaténation directe de variables utilisateur

-- Créer utilisateur avec permissions limitées
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'motdepassefort';
GRANT SELECT, INSERT, UPDATE, DELETE ON mabase. TO 'appuser'@'localhost';
FLUSH PRIVILEGES;

-- Révoquer permissions
REVOKE ALL PRIVILEGES ON mabase. FROM 'appuser'@'localhost';

-- Supprimer utilisateur
DROP USER 'appuser'@'localhost';

-- Utiliser INDEX appropriés
-- Éviter SELECT 
-- Utiliser LIMIT
-- Optimiser JOIN (index sur colonnes de jointure)
-- Éviter sous-requêtes corrélées coûteuses
-- Utiliser EXPLAIN pour analyser
-- Partitionner grandes tables
-- Configurer cache de requêtes
-- Utiliser types de données appropriés (plus petit possible)

-- Tables: pluriel, snakecase
users, articles, productcategories

-- Colonnes: singulier, snakecase
userid, createdat, isactive

-- Index: préfixe idx
idxuseremail, idxarticlestatus

-- Clés étrangères: préfixe fk
fkarticleauthor, fkcommentpost

-- Primary key: généralement 'id'
-- Foreign keys: tableid (ex: userid, articleid)

—

Version: MySQL 8.0+ / MariaDB 10.6+ | Documentation: dev.mysql.com