_OPTIDIEN-TECH

SQL_STRUCTURED_QUERY_LANGUAGE

ANSI_SQL_92_STANDARD

01_Definition_DDL

  • CREATE DATABASE [db]; - New DB
  • CREATE TABLE [t] (...); - New Table
  • ALTER TABLE [t] ADD [c]; - Add col
  • DROP TABLE [t]; - Suppr table
  • TRUNCATE TABLE [t]; - Vider table
  • CREATE INDEX [i] ON [t]([c]); - Index

02_Query_DML

  • SELECT * FROM [t]; - Tout lire
  • SELECT DISTINCT [c] - Valeurs uniques
  • WHERE [c] = 'v' - Filtre
  • WHERE [c] LIKE '%v%' - Pattern matching
  • ORDER BY [c] DESC - Tri décroissant
  • LIMIT 10 OFFSET 5 - Pagination
  • IN ('a', 'b', 'c') - Liste de choix

03_Joins

  • INNER JOIN [t2] ON [c1]=[c2] - Intersection
  • LEFT JOIN [t2] ... - Tout t1 + match t2
  • RIGHT JOIN [t2] ... - Tout t2 + match t1
  • FULL JOIN [t2] ... - Union complète
  • UNION - Fusionner résultats

04_Aggregation

  • COUNT(*) - Compter lignes
  • SUM([c]) / AVG([c]) - Somme / Moyenne
  • MIN([c]) / MAX([c]) - Bornes
  • GROUP BY [c] - Groupement
  • HAVING COUNT(*) > 1 - Filtre post-group

05_Modification

  • INSERT INTO [t] ([c]) VALUES ([v]); - Insérer
  • UPDATE [t] SET [c]=[v] WHERE ...; - Modifier
  • DELETE FROM [t] WHERE ...; - Supprimer
  • ON CONFLICT ([c]) DO UPDATE - Upsert (PG)

06_Functions

  • COALESCE([c], 'def') - Si NULL alors def
  • CAST([c] AS INT) - Type convert
  • NOW() / CURRENT_DATE - Temps
  • SUBSTR([c], 1, 3) - Tronquer string
  • CASE WHEN ... THEN ... END - Conditionnel

07_Transactions

  • BEGIN; - Début transaction
  • COMMIT; - Valider
  • ROLLBACK; - Annuler
  • SAVEPOINT [n]; - Point de sauvegarde

08_Admin

  • EXPLAIN ANALYZE [q]; - Profiler requête
  • GRANT ALL ON [t] TO [u]; - Droits
  • SHOW TABLES; / \dt - Lister tables
  • mysqldump -u [u] [db] - Backup MySQL
  • pg_dump [db] > [file] - Backup PG