AdSense Placeholder
Slot: header_reference_page
SQL est le langage qui sert à interroger et à modifier les bases de données relationnelles. Chaque instruction de cette page a été exécutée dans SQLite 3.46 sur le petit schéma customers / orders créé dans la première section ; là où MySQL, PostgreSQL ou SQL Server diffèrent, la dernière section le signale. Mettez en forme les longues requêtes avec le formateur SQL.
Créer des tables
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
country TEXT DEFAULT 'US',
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
total NUMERIC NOT NULL CHECK (total >= 0),
status TEXT NOT NULL DEFAULT 'new'
);
SQLite n'applique REFERENCES (et donc ON DELETE CASCADE) qu'après PRAGMA foreign_keys = ON; sur chaque connexion ; les autres bases de données l'appliquent toujours.
| Instruction |
Rôle |
ALTER TABLE customers ADD COLUMN phone TEXT; |
Ajoute une colonne |
ALTER TABLE customers RENAME COLUMN phone TO mobile; |
Renomme une colonne |
ALTER TABLE customers DROP COLUMN mobile; |
Supprime une colonne |
CREATE INDEX idx_orders_customer ON orders(customer_id); |
Accélère les recherches et les jointures sur cette colonne |
CREATE UNIQUE INDEX idx_email ON customers(email); |
Impose aussi l'unicité |
DROP TABLE IF EXISTS old_orders; |
Supprime une table et ses données |
CREATE VIEW big_orders AS SELECT * FROM orders WHERE total > 100; |
Enregistre une requête sous forme de table virtuelle |
Insérer, modifier et supprimer
| Instruction |
Rôle |
INSERT INTO customers (name, email) VALUES ('Ana', '[email protected]'); |
Ajoute une ligne |
INSERT INTO orders (customer_id, total) VALUES (1, 40), (1, 120), (2, 75); |
Ajoute plusieurs lignes |
INSERT INTO archive SELECT * FROM orders WHERE status = 'done'; |
Copie des lignes issues d'une requête |
UPDATE orders SET status = 'paid' WHERE id = 2; |
Modifie les lignes correspondantes |
DELETE FROM orders WHERE status = 'cancelled'; |
Supprime les lignes correspondantes |
INSERT INTO customers (id, name, email) VALUES (1, 'Ana B.', '[email protected]') ON CONFLICT(id) DO UPDATE SET name = excluded.name; |
Upsert : insère ou, si la clé existe, met à jour |
INSERT INTO customers (name) VALUES ('Di') RETURNING id; |
Renvoie les valeurs de la nouvelle ligne (SQLite 3.35 et suivantes) |
Un UPDATE ou un DELETE sans WHERE modifie toutes les lignes. Exécutez d'abord le même WHERE dans un SELECT. Transformez l'export d'un tableur en instructions INSERT avec le convertisseur CSV en SQL.
Interroger
| Clause |
Exemple |
Remarques |
SELECT |
SELECT name, email FROM customers; |
SELECT * renvoie toutes les colonnes |
WHERE |
WHERE country = 'US' AND total > 50 |
Filtre les lignes avant le regroupement |
IN, BETWEEN |
WHERE status IN ('new','paid'), WHERE total BETWEEN 50 AND 100 |
BETWEEN inclut les deux bornes |
LIKE |
WHERE email LIKE '%@example.com' |
% n'importe quel texte, _ un caractère |
IS NULL |
WHERE email IS NULL |
= NULL ne correspond jamais |
ORDER BY |
ORDER BY total DESC, id |
Croissant par défaut |
LIMIT / OFFSET |
LIMIT 10 OFFSET 20 |
Lignes 21 à 30 |
DISTINCT |
SELECT DISTINCT country FROM customers; |
Supprime les lignes en double |
AS |
SELECT total * 1.2 AS gross FROM orders; |
Alias de colonne ou de table |
CASE |
CASE WHEN total >= 100 THEN 'large' ELSE 'small' END |
Valeur conditionnelle |
COALESCE |
COALESCE(email, 'none') |
Premier argument non NULL |
Les clauses apparaissent toujours dans cet ordre : SELECT ... FROM ... JOIN ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT.
Jointures
| Jointure |
Renvoie |
INNER JOIN |
Uniquement les lignes avec une correspondance des deux côtés |
LEFT JOIN |
Toutes les lignes de gauche, avec des NULL là où la droite n'a pas de correspondance |
RIGHT JOIN |
Toutes les lignes de droite (SQLite 3.39 et suivantes) |
FULL OUTER JOIN |
Toutes les lignes des deux côtés (SQLite 3.39 et suivantes ; absent de MySQL) |
CROSS JOIN |
Toutes les combinaisons de lignes |
-- Customers with no orders
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
Regroupements et agrégats
| Fonction |
Renvoie |
COUNT(*) |
Nombre de lignes |
COUNT(col) |
Nombre de valeurs non NULL |
SUM(col), AVG(col) |
Total et moyenne (les NULL sont ignorés) |
MIN(col), MAX(col) |
Plus petite et plus grande valeur |
-- Revenue per customer, only customers above 100
SELECT customer_id, COUNT(*) AS orders, SUM(total) AS revenue
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 100
ORDER BY revenue DESC;
WHERE filtre les lignes avant le regroupement ; HAVING filtre les groupes après.
Sous-requêtes, CTE et fonctions de fenêtrage
-- Orders above the average
SELECT * FROM orders WHERE total > (SELECT AVG(total) FROM orders);
-- Common table expression
WITH revenue AS (
SELECT customer_id, SUM(total) AS amount FROM orders GROUP BY customer_id
)
SELECT c.name, r.amount FROM revenue r JOIN customers c ON c.id = r.customer_id;
-- Rank each customer's orders and keep a running total
SELECT customer_id, id, total,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rank_in_customer,
SUM(total) OVER (ORDER BY id) AS running_total
FROM orders;
Transactions
| Instruction |
Rôle |
BEGIN; |
Démarre une transaction (START TRANSACTION dans MySQL, BEGIN TRANSACTION dans SQL Server) |
COMMIT; |
Rend les modifications définitives |
ROLLBACK; |
Annule tout depuis le BEGIN |
SAVEPOINT s1; / ROLLBACK TO s1; |
Annulation partielle au sein d'une transaction |
Différences entre dialectes
| Tâche |
SQLite |
PostgreSQL |
MySQL / MariaDB |
SQL Server |
| 10 premières lignes |
LIMIT 10 |
LIMIT 10 |
LIMIT 10 |
SELECT TOP 10 ... ou OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY |
| Clé auto-incrémentée |
INTEGER PRIMARY KEY |
GENERATED ALWAYS AS IDENTITY ou SERIAL |
AUTO_INCREMENT |
IDENTITY(1,1) |
| Concaténer des chaînes |
a || b |
a || b |
CONCAT(a, b) |
a + b ou CONCAT(a, b) |
| Upsert |
ON CONFLICT ... DO UPDATE |
ON CONFLICT ... DO UPDATE |
ON DUPLICATE KEY UPDATE |
MERGE |
| Renvoyer la ligne insérée |
RETURNING |
RETURNING |
non pris en charge (MariaDB : RETURNING) |
OUTPUT inserted.* |
| Heure courante |
CURRENT_TIMESTAMP, datetime('now') |
CURRENT_TIMESTAMP, now() |
CURRENT_TIMESTAMP, NOW() |
CURRENT_TIMESTAMP, GETDATE() |
| Délimiter un identifiant |
"name" |
"name" |
`name` |
[name] |
| Comparaison insensible à la casse |
LIKE (ASCII uniquement) |
ILIKE |
LIKE (avec les collations par défaut) |
LIKE (avec les collations par défaut) |
| Type booléen |
aucun : 0 et 1 |
BOOLEAN |
BOOLEAN (alias de TINYINT(1)) |
BIT |
Sécurité
Ne construisez jamais du SQL en collant des saisies d'utilisateur dans une chaîne : c'est ainsi que naît l'injection SQL. Utilisez des paramètres (?, $1, :name ou %s, selon le pilote) et transmettez les valeurs séparément.