SQL fondamental et schema design

Section 13.3.1 : SQL fondamental et schema design

🎯 Objectif pédagogique

Maîtriser les fondamentaux de SQL pour créer, interroger et manipuler des bases de données relationnelles. Comprendre la conception de schémas avant de passer à Active Record.

Pourquoi SQL est incontournable

SQL (Structured Query Language) est le langage universel des bases de données relationnelles. Créé en 1970 par IBM, il a survécu à toutes les modes technologiques. Que vous utilisiez PostgreSQL, MySQL, SQLite ou BigQuery, SQL reste la compétence la plus demandée pour tout développeur.

Les bases : CREATE, INSERT, SELECT

-- Créer une table
CREATE TABLE utilisateurs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  nom VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  age INTEGER,
  ville VARCHAR(100) DEFAULT 'Paris',
  date_inscription DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Insérer des données
INSERT INTO utilisateurs (nom, email, age, ville)
VALUES ('Alice Martin', 'alice@example.com', 28, 'Paris');

INSERT INTO utilisateurs (nom, email, age, ville)
VALUES ('Bob Dupont', 'bob@example.com', 32, 'Lyon');

INSERT INTO utilisateurs (nom, email, age, ville)
VALUES ('Charlie Petit', 'charlie@example.com', 25, 'Paris');

-- Lire des données
SELECT * FROM utilisateurs;
SELECT nom, email FROM utilisateurs;
SELECT nom, age FROM utilisateurs WHERE ville = 'Paris';
SELECT * FROM utilisateurs WHERE age >= 28 ORDER BY age DESC;
SELECT * FROM utilisateurs LIMIT 10 OFFSET 20;  -- Pagination

WHERE : filtrer les données

-- Comparaisons
SELECT * FROM utilisateurs WHERE age > 25;
SELECT * FROM utilisateurs WHERE ville = 'Paris';
SELECT * FROM utilisateurs WHERE nom LIKE 'A%';     -- Commence par A
SELECT * FROM utilisateurs WHERE email LIKE '%@gmail.com';

-- Opérateurs logiques
SELECT * FROM utilisateurs WHERE age > 25 AND ville = 'Paris';
SELECT * FROM utilisateurs WHERE ville = 'Paris' OR ville = 'Lyon';
SELECT * FROM utilisateurs WHERE ville IN ('Paris', 'Lyon', 'Marseille');
SELECT * FROM utilisateurs WHERE age BETWEEN 25 AND 35;
SELECT * FROM utilisateurs WHERE email IS NOT NULL;

Agrégation : GROUP BY et fonctions

-- Fonctions d agrégation
SELECT COUNT(*) FROM utilisateurs;                    -- Nombre total
SELECT AVG(age) FROM utilisateurs;                    -- Âge moyen
SELECT MIN(age), MAX(age) FROM utilisateurs;          -- Min et Max
SELECT SUM(age) FROM utilisateurs WHERE ville = 'Paris';

-- GROUP BY
SELECT ville, COUNT(*) as nb_utilisateurs
FROM utilisateurs
GROUP BY ville
ORDER BY nb_utilisateurs DESC;

-- HAVING (filtre sur les groupes)
SELECT ville, COUNT(*) as nb
FROM utilisateurs
GROUP BY ville
HAVING nb >= 2;

Schema design : relations entre tables

-- One-to-Many : un utilisateur a plusieurs articles
CREATE TABLE articles (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  titre VARCHAR(255) NOT NULL,
  contenu TEXT,
  utilisateur_id INTEGER NOT NULL,
  date_publication DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id)
);

-- Many-to-Many : articles et tags (via table de jonction)
CREATE TABLE tags (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  nom VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE articles_tags (
  article_id INTEGER NOT NULL,
  tag_id INTEGER NOT NULL,
  PRIMARY KEY (article_id, tag_id),
  FOREIGN KEY (article_id) REFERENCES articles(id),
  FOREIGN KEY (tag_id) REFERENCES tags(id)
);

JOIN : combiner les tables

-- INNER JOIN : données qui matchent dans les deux tables
SELECT utilisateurs.nom, articles.titre
FROM articles
INNER JOIN utilisateurs ON articles.utilisateur_id = utilisateurs.id;

-- LEFT JOIN : tous les utilisateurs, même sans articles
SELECT utilisateurs.nom, COUNT(articles.id) as nb_articles
FROM utilisateurs
LEFT JOIN articles ON articles.utilisateur_id = utilisateurs.id
GROUP BY utilisateurs.id;

-- Requête complexe
SELECT
  u.nom,
  COUNT(a.id) as nb_articles,
  AVG(LENGTH(a.contenu)) as longueur_moyenne
FROM utilisateurs u
LEFT JOIN articles a ON a.utilisateur_id = u.id
WHERE u.ville = 'Paris'
GROUP BY u.id
HAVING nb_articles > 0
ORDER BY nb_articles DESC
LIMIT 5;