Divers

SELECT  CONCAT_WS(' ', `champ_1`, `champ_2`) AS `nom_complet`,
  IF(`champ_1`, `champ_2`, `champ_3`) AS `champ_a`,
  IFNULL(`champ_2`, 0) AS `champ_safe`,    
  DATE_FORMAT(`created_at`, '%d/%m %H:%i') AS `day`,    
  TIMESTAMPDIFF(SECOND, DATE(NOW()), `created_at`) * 1000 AS `duration`,
  ST_X(`map_gps`) AS `lat`,ST_Y(`map_gps`) AS `lng`,
  ROUND(123.456, 2),
  FLOOR(RAND() * 100) + 1,
  GROUP_CONCAT(`produit` SEPARATOR ', ')
      CASE 
        WHEN age < 18 THEN 'Mineur'
        WHEN age BETWEEN 18 AND 65 THEN 'Adulte'
        ELSE 'Senior'
    END AS categorie_age
  FROM `table`;
SELECT * FROM `table` WHERE `champ` = 'valeur' AND `date_creation` BETWEEN '2024-01-01' AND '2024-12-31' AND `status` IN ('actif', 'inactif')
,AND NOT EXISTS (SELECT 1 FROM `table` WHERE `table` = `table_externe`.`id`)
DATE_ADD(`table`.`created_at`, INTERVAL 1 MONTH) // YEAR
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR)
SELECT ABS('-456')
SELECT LPAD('TTTT',10,'A')
SELECT     COUNT(*) AS total,    MAX(`id`) AS max_id,    MIN(`prix`) AS min_prix,  AVG(`salaire`) AS salaire_moyen,  SUM(`montant`) AS total_montant FROM `table`
, `table`.`champ` IN (SELECT `champ` FROM `table2` WHERE `champ` LIKE :term)
SELECT TIMEDIFF('12:30:00', '10:00:00'); -- Résultat : 02:30:00

Fenêtrage et Analytique
SELECT id, nom, salaire,RANK() OVER (ORDER BY salaire DESC) AS classement FROM employes;
  SELECT id, client_id, montant,SUM(montant) OVER (PARTITION BY client_id ORDER BY id) AS total_cumulé FROM ventes;

 Forcer l'utilisation d'un index
SELECT * FROM utilisateurs FORCE INDEX (idx_utilisateur_email) WHERE email = 'exemple@email.com';

Utilisation de JSON en SQL
SELECT JSON_EXTRACT('{ "nom": "Michel", "age": 35 }', '$.nom'); -- Retourne "Michel"

Transactions SQL (ACID) pour assurer l'intégrité des données
START TRANSACTION;
UPDATE comptes SET solde = solde - 100 WHERE id = 1;
UPDATE comptes SET solde = solde + 100 WHERE id = 2;
COMMIT;  -- Confirme la transaction
-- ROLLBACK; -- Annule la transaction en cas d'erreur

Triggers pour Automatiser des Actions
CREATE TRIGGER after_insert_client
AFTER INSERT ON clients
FOR EACH ROW
INSERT INTO logs (message) VALUES (CONCAT('Nouveau client ajouté : ', NEW.nom));

Jointure
SELECT * FROM `table_t1` LEFT JOIN `table_t2` ON `table_t2`.`id` = `table_t1`.`clef_id`;

Recherche sur des données de type 'SET' 
$attr["champ"] = ["value" => "aaaa","param" => \PDO::PARAM_STR];
WHERE FIND_IN_SET(:champ, `table`.`champ`)

Supprime les doublons :
DELETE p1 FROM `table` p1 JOIN `table` p2 ON p1.`ref` = p2.`ref` AND p1.id > p2.id;
SELECT COUNT(*),`ref`,`libelle` FROM `table` GROUP BY `ref` HAVING COUNT(*)>1

SELECT * FROM commandes c LEFT JOIN clients cl ON c.client_id = cl.id WHERE cl.id IS NULL;

Fonction récursive
DELIMITER $$

CREATE FUNCTION factorial(n INT) RETURNS INT
DETERMINISTIC
BEGIN
    IF n <= 1 THEN
        RETURN 1;
    ELSE
        RETURN n * factorial(n - 1);
    END IF;
END $$

DELIMITER ;

SELECT factorial(5);

 table partitionnée
CREATE TABLE logs (
    id INT NOT NULL,
    date_log DATE NOT NULL,
    message TEXT,
    PRIMARY KEY (id, date_log)
)
PARTITION BY RANGE (YEAR(date_log)) (
    PARTITION p1 VALUES LESS THAN (2023),
    PARTITION p2 VALUES LESS THAN (2024),
    PARTITION p3 VALUES LESS THAN (2025)
);


WITH RECURSIVE months AS (
    SELECT DATE_FORMAT('2024-01-01', '%Y-%m') AS mois
    UNION ALL
    SELECT DATE_FORMAT(DATE_ADD(mois, INTERVAL 1 MONTH), '%Y-%m')
    FROM months WHERE mois < DATE_FORMAT('2024-12-01', '%Y-%m')
)
SELECT * FROM months;

WITH RECURSIVE sequence AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM sequence WHERE n < 10
)
SELECT * FROM sequence;

SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);

SELECT * FROM table WHERE colonne REGEXP '[^0-9]';

SELECT 
    id_client,
    SUM(CASE WHEN type = 'achat' THEN montant ELSE 0 END) AS total_achats,
    SUM(CASE WHEN type = 'remboursement' THEN montant ELSE 0 END) AS total_remboursements
FROM transactions
GROUP BY id_client;

ALTER TABLE articles ADD FULLTEXT(titre, contenu);
SELECT * FROM articles WHERE MATCH(titre, contenu) AGAINST('intelligence artificielle');

SELECT CONCAT('CMD-', LPAD(id, 6, '0')) AS ref_commande FROM commandes;

CREATE TABLE nouvelle_table AS SELECT * FROM ancienne_table;  // structure et données
CREATE TABLE nouvelle_table LIKE ancienne_table; // structure seul

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

WITH RECURSIVE missing_numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM missing_numbers WHERE n < (SELECT MAX(id) FROM commandes)
)
SELECT n FROM missing_numbers
WHERE n NOT IN (SELECT id FROM commandes);

WITH RECURSIVE dates AS (
    SELECT '2024-01-01' AS date_jour
    UNION ALL
    SELECT DATE_ADD(date_jour, INTERVAL 1 DAY) FROM dates WHERE date_jour < '2024-12-31'
)
SELECT * FROM dates;

SELECT DATE_ADD('2024-01-01', INTERVAL FLOOR(RAND() * 365) DAY) AS random_date;

SELECT TRIM(nom) FROM utilisateurs;
SELECT REPLACE(REGEXP_REPLACE('   Hello   World  ', '\\s+', ' '), ' ', ' '); 

SELECT 
    SUBSTRING(nom, 1, 3) AS debut_nom, -- 3 premières lettres
    LOCATE('@', email) AS position_arobase, -- Position du @ dans un email
    LEFT(nom, 5) AS debut, -- 5 premières lettres
    RIGHT(nom, 4) AS fin -- 4 dernières lettres
FROM utilisateurs;

INSERT INTO clients (nom, email) 
SELECT 'Dupont', 'dupont@email.com' 
WHERE NOT EXISTS (SELECT 1 FROM clients WHERE email = 'dupont@email.com');

SELECT REPLACE(texte, 'ancien_mot', 'nouveau_mot') FROM articles;

SET @debut = NOW();
SELECT * FROM utilisateurs;
SELECT TIMESTAMPDIFF(SECOND, @debut, NOW()) AS duree_execution;