SQLite : requêtes SQL

Requêtes sur les tables clients et commandes de la page Tables et types. Pour un affichage lisible dans la console : .headers on puis .mode table.

Insérer

INSERT INTO clients (nom, email, ville) VALUES
  ('Alice Martin', 'alice@exemple.com', 'Lyon'),
  ('Bruno Durand', 'bruno@exemple.com', 'Paris'),
  ('Chloé Petit',  'chloe@exemple.com', 'Lyon'),
  ('David Moreau', 'david@exemple.com', NULL);

INSERT INTO commandes (client_id, montant, statut) VALUES
  (1, 49.90, 'payee'),
  (1, 120.00, 'nouvelle'),
  (2, 15.50, 'payee'),
  (3, 300.00, 'expediee');

INSERT INTO clients (nom, email) VALUES ('Emma Roux', 'emma@exemple.com') RETURNING id, cree_le;
SELECT last_insert_rowid();

RETURNING (SQLite 3.35 et plus) renvoie les valeurs générées sans second SELECT.

Lire des données

SELECT * FROM clients;
SELECT nom, ville FROM clients WHERE ville = 'Lyon';
SELECT nom FROM clients WHERE ville IS NULL;
SELECT nom FROM clients WHERE nom LIKE 'a%';              -- insensible à la casse pour l'ASCII
SELECT nom FROM clients WHERE ville IN ('Lyon', 'Paris');
SELECT id, montant FROM commandes WHERE montant BETWEEN 20 AND 200;
SELECT DISTINCT ville FROM clients;
SELECT nom FROM clients ORDER BY nom DESC LIMIT 2;
SELECT nom FROM clients ORDER BY nom LIMIT 2 OFFSET 2;    -- pagination
SELECT COUNT(*) AS nb FROM clients;

Modifier et supprimer

UPDATE clients SET ville = 'Marseille' WHERE email = 'david@exemple.com' RETURNING id, ville;
UPDATE commandes SET statut = 'payee' WHERE id = 2;
DELETE FROM commandes WHERE statut = 'annulee';

Jointures

SELECT c.nom, o.montant, o.statut
FROM clients c
JOIN commandes o ON o.client_id = c.id
ORDER BY c.nom;

SELECT c.nom, COUNT(o.id) AS nb_commandes
FROM clients c
LEFT JOIN commandes o ON o.client_id = c.id
GROUP BY c.id, c.nom;

Agrégats

SELECT statut, COUNT(*) AS nb, SUM(montant) AS total, ROUND(AVG(montant), 2) AS moyenne
FROM commandes
GROUP BY statut
ORDER BY total DESC;

SELECT client_id, SUM(montant) AS total
FROM commandes
GROUP BY client_id
HAVING SUM(montant) > 100;

SELECT group_concat(nom, ', ') AS noms FROM clients;     -- concatène les valeurs d'un groupe

Expressions de table et fenêtres

WITH totaux AS (
  SELECT client_id, SUM(montant) AS total FROM commandes GROUP BY client_id
)
SELECT c.nom, t.total
FROM totaux t
JOIN clients c ON c.id = t.client_id
ORDER BY t.total DESC;

SELECT id, client_id, montant,
       SUM(montant) OVER (PARTITION BY client_id ORDER BY id) AS cumul_client,
       RANK() OVER (ORDER BY montant DESC) AS rang
FROM commandes;

Insérer ou mettre à jour (upsert)

INSERT INTO clients (nom, email, ville) VALUES ('Alice Martin', 'alice@exemple.com', 'Nice')
ON CONFLICT (email) DO UPDATE SET ville = excluded.ville;

INSERT INTO clients (nom, email) VALUES ('Alice Martin', 'alice@exemple.com')
ON CONFLICT (email) DO NOTHING;

Dates et chaînes

SELECT datetime('now'), date('now'), strftime('%d/%m/%Y %H:%M', 'now');
SELECT * FROM commandes WHERE passee_le >= datetime('now', '-7 days');
SELECT upper(nom), nom || ' <' || email || '>' FROM clients;
SELECT coalesce(ville, 'inconnue') FROM clients;
SELECT substr(nom, 1, 3), length(nom), replace(email, '@', ' at ') FROM clients;

Transactions

BEGIN;
UPDATE commandes SET statut = 'payee' WHERE id = 2;
INSERT INTO commandes (client_id, montant) VALUES (2, 10.00);
COMMIT;        -- valide
-- ROLLBACK;   -- annule tout depuis BEGIN

Hors transaction, chaque instruction est validée seule, ce qui est beaucoup plus lent pour de nombreuses insertions : regrouper les insertions en masse dans une transaction.

Expliquer une requête

EXPLAIN QUERY PLAN SELECT * FROM commandes WHERE statut = 'payee';

Voir Index et performances.

Pour aller plus loin

Manuel : SELECT

Dans la même rubrique