SQL Handout · ⁨Feuille de travail SQL⁩

Une base de données 数据库 stocke les données dans des tables 表. Une requête 查询 lit les données avec SELECT. SELECT * retourne toutes les colonnes ; FROM nomme la table.

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
SELECT * FROM student;

• Chaque ligne 行 est un enregistrement 记录. Les mots-clés SQL s'écrivent par habitude en MAJUSCULES.
• Une instruction se termine par un point-virgule ;.

Répertoriez les colonnes 列 que vous souhaitez, séparées par des virgules. Utilisez AS pour renommer une colonne dans le résultat (un alias 别名).

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
SELECT name, score AS mark FROM student;

Une colonne sélectionnée peut aussi être un calcul — associez-la à AS pour nommer la nouvelle colonne :

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
SELECT name, score + 5 AS bonus FROM student;

• SELECT DISTINCT col élimine les valeurs dupliquées du résultat.

WHERE garde uniquement les lignes qui correspondent à une condition 条件. Comparez avec =, <> (différent de), <, >, <=, >=. Mettez du texte entre guillemets simples.

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71), (3, 'Ana', 95);
SELECT name, score FROM student WHERE score >= 80;

• SQL exécute les parties dans cet ordre : FROM → WHERE → SELECT.

Erreurs courantes

• Mettez les valeurs textuelles entre guillemets simples : WHERE name = 'Ann' ; les noms de colonnes n'ont pas de guillemets.
• SELECT * retourne toutes les colonnes — nommez uniquement les colonnes dont vous avez besoin.
• WHERE filtre les lignes ; il vient après FROM.

Combinez les conditions avec AND, OR, NOT. AND nécessite que tous les côtés soient vrais ; OR nécessite qu'au moins un côté soit vrai ; NOT inverse un côté. Utilisez des parenthèses pour définir la priorité 优先级.

CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT name FROM student WHERE form = '11A' AND score >= 90;

LIKE correspond à un motif de texte 模式 : % représente n'importe quel texte et _ représente un seul caractère — ce sont des caractères génériques 通配符.

CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT name FROM student WHERE name LIKE 'M%';        -- starts with M

IN correspond à un ensemble 集合 de valeurs ; BETWEEN correspond à une plage 范围 (extrémités incluses).

CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT name, score FROM student
WHERE form IN ('11A', '11C') AND score BETWEEN 80 AND 100;

NULL marque une valeur manquante 缺失值 — rien n'a été stocké dans cette cellule. NULL n'est pas 0 et n'est pas une chaîne vide. Une comparaison avec = ou <> ne correspond jamais ; testez avec IS NULL ou IS NOT NULL.

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', NULL), (3, 'Ana', 95);
SELECT name FROM student WHERE score IS NULL;

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', NULL), (3, 'Ana', 95);
SELECT name, score FROM student WHERE score IS NOT NULL;

• WHERE score = NULL ne retourne aucune ligne — pas même la ligne de Sam.
• Un NULL en arithmétique donne un NULL : score + 5 reste NULL pour Sam.

Erreurs courantes

• Testez une valeur vide avec IS NULL, jamais = NULL.
• Dans LIKE, % correspond à n'importe quel texte et _ correspond à un seul caractère : 'A%' signifie « commence par A ».
• IN (1, 2, 3) est plus court que beaucoup de ORs ; BETWEEN a AND b inclut les deux extrémités.

ORDER BY trie 排序 les lignes du résultat. Ajoutez DESC pour un tri décroissant 降序 (de haut en bas) ; le tri croissant 升序 (de bas en haut) est par défaut.

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei',88),(2,'Sam',71),(3,'Ana',95);
SELECT name, score FROM student ORDER BY score DESC;

Tri par plusieurs colonnes

Répertoriez plusieurs colonnes. En cas d'égalité 平局 dans la première colonne, la suivante fait office de critère de rupture.

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei',88),(2,'Sam',88),(3,'Ana',95);
SELECT name, score FROM student ORDER BY score DESC, name ASC;

LIMIT (top-N)

LIMIT n garde seulement les premières n lignes — associez-le à ORDER BY pour une liste des N premiers 前 N 名.

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei',88),(2,'Sam',71),(3,'Ana',95);
SELECT name, score FROM student ORDER BY score DESC LIMIT 2;

• ORDER BY peut aussi trier selon un alias ou un calcul : ORDER BY avg_score DESC.
• Le texte se trie alphabétiquement : ORDER BY name exécute A → Z.

Erreurs courantes

• ORDER BY trie par défaut par ordre croissant ; ajoutez DESC pour un ordre décroissant.
• ORDER BY intervient vers la fin, après WHERE et GROUP BY.
• LIMIT limite le nombre de lignes retournées, mais seulement après le tri.

Une fonction agrégée 聚合函数 convertit de nombreuses lignes en une seule valeur de résumé 汇总. Entourez une moyenne de ROUND(x, 2) pour l'arrondir.

CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT COUNT(*), ROUND(AVG(score), 2), MAX(score) FROM student;

GROUP BY produit une ligne de résumé par groupe 组. HAVING filtre ces groupes — c'est comme WHERE, mais il s'exécute après le groupement.

CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT form, COUNT(*) AS n, ROUND(AVG(score), 2) AS avg_score
FROM student
GROUP BY form
HAVING COUNT(*) > 1;

Peu importe l'ordre dans lequel vous les écrivez, SQL exécute toujours les clauses d'une requête dans le même ordre fixe :

Erreurs courantes

• Vous ne pouvez pas sélectionner une colonne simple à côté d'une agrégation sauf si elle est dans GROUP BY.
• Filtrez les lignes avec WHERE (avant le groupement) et filtrez les groupes avec HAVING (après).
• COUNT(*) compte les lignes ; COUNT(col) ignore les NULLs dans cette colonne.

Une clé primaire 主键 identifie de manière unique chaque ligne dans une table. Une clé étrangère 外键 dans une table pointe vers la clé primaire d'une autre — cela établit une relation 关系 entre elles.

CREATE TABLE class (id INTEGER, name TEXT);
CREATE TABLE student (id INTEGER, name TEXT, class_id INTEGER);
INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', 2);
SELECT * FROM student;

Un join 连接 combine des lignes de deux tables. INNER JOIN ... ON ... garde les lignes où les clés correspondent. Un alias court 别名 (s, c) garde la requête lisible.

CREATE TABLE class (id INTEGER, name TEXT);
CREATE TABLE student (id INTEGER, name TEXT, class_id INTEGER);
INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', 2);
SELECT s.name, c.name AS class
FROM student s INNER JOIN class c ON s.class_id = c.id;

Joignez d'abord, puis GROUP BY pour résumer sur les lignes jointes.

CREATE TABLE class (id INTEGER, name TEXT);
CREATE TABLE student (id INTEGER, name TEXT, class_id INTEGER);
INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', 2), (3, 'Ana', 1);
SELECT c.name AS class, COUNT(*) AS n
FROM student s INNER JOIN class c ON s.class_id = c.id
GROUP BY c.name;

Un INNER JOIN ne garde que les lignes correspondantes. Un LEFT JOIN 左连接 garde toutes les lignes de la table de gauche (première) ; là où il n'y a pas de correspondance, les colonnes de la table de droite reviennent en tant que NULL.

CREATE TABLE class (id INTEGER, name TEXT);
CREATE TABLE student (id INTEGER, name TEXT, class_id INTEGER);
INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', NULL);
SELECT s.name, c.name AS class
FROM student s LEFT JOIN class c ON s.class_id = c.id;

• Sam n'a pas de classe, mais la ligne apparaît quand même — avec class valant NULL.
• Pour trouver uniquement les lignes sans correspondance, ajoutez WHERE c.id IS NULL.

Erreurs courantes

• Un join sans condition ON apparie chaque ligne avec chaque ligne (un joint cartésien).
• Associez la clé étrangère à la clé primaire : ON orders.customer_id = customers.id.
• Un INNER JOIN rejette les lignes qui n'ont pas de correspondance de l'autre côté ; utilisez un LEFT JOIN pour les garder.

INSERT INTO ... VALUES ... ajoute de nouvelles lignes 行. Nommez les colonnes, puis donnez les valeurs dans le même ordre. (Chaque bloc ci-dessous se termine par un SELECT pour que vous puissiez voir le résultat.)

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student (id, name, score) VALUES (1, 'Mei', 88);
INSERT INTO student VALUES (2, 'Sam', 71);
SELECT * FROM student;

• INSERT INTO t VALUES (...), (...); ajoute plusieurs lignes en une seule instruction.

UPDATE ... SET ... WHERE ... modifie des lignes existantes. Toujours ajouter WHERE, sinon toutes les lignes changent.

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
UPDATE student SET score = 75 WHERE name = 'Sam';
SELECT * FROM student;

DELETE FROM ... WHERE ... supprime des lignes. Sans WHERE, il vide entièrement la table 表.

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
DELETE FROM student WHERE score < 80;
SELECT * FROM student;

Erreurs courantes

• UPDATE et DELETE sans WHERE modifient toutes les lignes — ajoutez toujours le WHERE.
• Dans INSERT, les valeurs doivent correspondre à la liste des colonnes par ordre et type.
• Testez d'abord un DELETE risqué comme un SELECT avec le même WHERE.

CREATE TABLE définit le schéma d'une table 表结构: les noms de colonnes et leurs types de données 数据类型. Les principaux types SQLite sont INTEGER, TEXT et REAL (un nombre décimal).

CREATE TABLE student (id INTEGER, name TEXT, score REAL);
INSERT INTO student VALUES (1, 'Mei', 88.5);
SELECT * FROM student;

Une contrainte 约束 est une règle sur une colonne : PRIMARY KEY (une ID unique), NOT NULL (doit avoir une valeur), UNIQUE et DEFAULT (une valeur par défaut).

CREATE TABLE student (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    score INTEGER DEFAULT 0
);
INSERT INTO student (id, name) VALUES (1, 'Mei');
SELECT * FROM student;

Déclarez une clé étrangère avec REFERENCES — elle indique que la colonne pointe vers une clé primaire d'une autre table :

CREATE TABLE class (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE student (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    class_id INTEGER REFERENCES class(id)
);
INSERT INTO class VALUES (1, 'Maths');
INSERT INTO student VALUES (1, 'Mei', 1);
SELECT s.name, c.name AS class
FROM student s INNER JOIN class c ON s.class_id = c.id;

• La forme longue est FOREIGN KEY (class_id) REFERENCES class(id) sur sa propre ligne.

ALTER TABLE ... ADD COLUMN ... modifie le schéma d'une table existante. Un DEFAULT remplit la nouvelle colonne dans les anciennes lignes.

CREATE TABLE student (id INTEGER, name TEXT);
INSERT INTO student VALUES (1, 'Mei');
ALTER TABLE student ADD COLUMN score INTEGER DEFAULT 0;
SELECT * FROM student;

• ALTER TABLE student RENAME TO pupil; renomme toute la table.
• DROP TABLE student; supprime complètement la table — structure et données.

Erreurs courantes

• Chaque colonne nécessite un type de données (ex. INTEGER, TEXT).
• Une PRIMARY KEY doit être unique et ne peut pas être NULL.
• Une valeur de clé étrangère doit exister dans la table qu'elle pointe.

Les tables se connectent via des relations 关系. Le un-vers-beaucoup 一对多 est le plus courant : une classe a beaucoup d'étudiants. Le beaucoup-vers-beaucoup 多对多 nécessite une table de jonction 连接表 intermédiaire. Un diagramme entité-relationship 实体关系图 (diagramme ER) dessine chaque entité 实体 comme une boîte et chaque relation comme une ligne.

La table de jonction contient une ligne par lien. Ici, Mei est dans deux clubs, et Chess a deux membres :

CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE club (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE membership (student_id INTEGER, club_id INTEGER);
INSERT INTO student VALUES (1, 'Mei'), (2, 'Sam');
INSERT INTO club VALUES (1, 'Chess'), (2, 'Art');
INSERT INTO membership VALUES (1, 1), (1, 2), (2, 1);
SELECT s.name, c.name AS club
FROM membership m
INNER JOIN student s ON m.student_id = s.id
INNER JOIN club c ON m.club_id = c.id;

La normalisation 范式化 organise les tables pour éviter la redondance 冗余 (les mêmes données répétées) et les erreurs de mise à jour qu'elle provoque. Les trois premières formes normales 范式 :

• 1NF : chaque cellule contient une valeur atomique 原子 unique — pas de listes dans une cellule.
• 2NF : aucune colonne ne dépend uniquement d'une partie d'une clé composite 复合主键.
• 3NF : aucune colonne ne dépend d'une autre colonne non-clé.

Erreurs courantes

• Séparez les groupes répétés dans leur propre table (normalisation) au lieu de nombreuses colonnes similaires.
• Chaque table doit décrire UN SEUL type de chose.
• Liez les tables avec une clé étrangère pointant vers la clé primaire d'une autre table.