Aide-mémoire : les commandes SQL

-- Syntaxe : structure
CREATE DATABASE nom CHARACTER SET utf8mb4;
CREATE TABLE t (
  id INT AUTO_INCREMENT PRIMARY KEY,
  col TYPE [NOT NULL] [DEFAULT v] [UNIQUE],
  CONSTRAINT ck_t CHECK (condition),
  CONSTRAINT fk_t FOREIGN KEY (col) REFERENCES parent (id) ON DELETE {RESTRICT | CASCADE | SET NULL}
);
ALTER TABLE t ADD COLUMN col TYPE;  ALTER TABLE t MODIFY COLUMN col TYPE NOT NULL;
ALTER TABLE t RENAME COLUMN a TO b; ALTER TABLE t DROP COLUMN col;
ALTER TABLE t ADD CONSTRAINT ... ;  ALTER TABLE t DROP FOREIGN KEY fk_t;
DROP TABLE IF EXISTS t;  TRUNCATE TABLE t;
CREATE INDEX idx ON t (col1, col2);  DROP INDEX idx ON t;
CREATE [OR REPLACE] VIEW v AS SELECT ... [WITH CHECK OPTION];  DROP VIEW v;

-- Syntaxe : données
INSERT INTO t (c1, c2) VALUES (v1, v2), (v3, v4);
INSERT INTO t (c1, c2) SELECT ... ;
INSERT INTO t (c1, c2) VALUES (v1, v2) AS n ON DUPLICATE KEY UPDATE c2 = n.c2;
UPDATE t SET c1 = v1, c2 = c2 + 1 WHERE condition;
DELETE FROM t WHERE condition;

-- Syntaxe : lecture
[WITH cte AS (SELECT ...)]
SELECT [DISTINCT] expr [AS alias], ...
FROM t1 a
[INNER | LEFT] JOIN t2 b ON b.cle = a.cle
WHERE condition_sur_les_lignes
GROUP BY colonnes
HAVING condition_sur_les_groupes
ORDER BY expr [ASC | DESC], ...
LIMIT n [OFFSET m];

-- Ensembles : requête1 UNION [ALL] | INTERSECT | EXCEPT requête2
-- Fenêtre :   fonction() OVER (PARTITION BY ... ORDER BY ...)

-- Syntaxe : transactions et droits
START TRANSACTION;  SAVEPOINT s;  ROLLBACK TO SAVEPOINT s;  COMMIT;  ROLLBACK;
SELECT ... FOR UPDATE;
CREATE USER 'u'@'hote' IDENTIFIED BY 'mot de passe';
GRANT SELECT, INSERT ON base.table TO 'u'@'hote';
GRANT SELECT (col1, col2) ON base.table TO 'u'@'hote';
REVOKE INSERT ON base.table FROM 'u'@'hote';
CREATE ROLE 'r';  GRANT 'r' TO 'u'@'hote';  SET DEFAULT ROLE ALL TO 'u'@'hote';
SHOW GRANTS FOR 'u'@'hote';