Aide-mémoire du langage SQL

🔍 Les bases de SELECT

SELECT customer_id, name, email
FROM customers;

-- With alias and DISTINCT
SELECT DISTINCT country AS customer_country
FROM customers;

⚡ Clause WHERE

SELECT name, price
FROM products
WHERE price > 100;

-- Multiple conditions
SELECT name, category
FROM products
WHERE category = 'Electronics'
  AND price <= 500;

⚙️ Opérateurs logiques

-- AND operator
SELECT * FROM orders
WHERE status = 'shipped' AND total > 100;

-- OR operator
SELECT * FROM customers
WHERE country = 'USA' OR country = 'Canada';

-- NOT operator
SELECT * FROM products
WHERE NOT category = 'Discontinued';

↕️ ORDER BY

-- Single column sort
SELECT name, price
FROM products
ORDER BY price DESC;

-- Multiple columns
SELECT name, category, price
FROM products
ORDER BY category ASC, price DESC;

✂️ LIMIT / OFFSET

-- First 10 rows
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 10;

-- Pagination (skip first 20, get next 10)
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 10 OFFSET 20;

*️⃣ Recherche de motifs (LIKE)

-- Starts with 'John'
SELECT name, email
FROM customers
WHERE name LIKE 'John%';

-- Contains 'smith'
SELECT name FROM customers
WHERE name LIKE '%smith%';

-- Exact pattern (5 characters starting with 'A')
SELECT code FROM products
WHERE code LIKE 'A____';

↔️ Plages et ensembles (BETWEEN, IN)

-- BETWEEN (inclusive)
SELECT name, price
FROM products
WHERE price BETWEEN 50 AND 100;

-- IN operator
SELECT name, country
FROM customers
WHERE country IN ('USA', 'Canada', 'Mexico');

-- NOT IN
SELECT name FROM products
WHERE category NOT IN ('Discontinued', 'Draft');

❓ Gestion des NULL

-- Find NULL values
SELECT name, phone
FROM customers
WHERE phone IS NULL;

-- Exclude NULL values
SELECT name, email
FROM customers
WHERE email IS NOT NULL;

-- Provide default value
SELECT name, COALESCE(phone, 'No phone') AS contact
FROM customers;

Σ Fonctions d'agrégation

-- Basic aggregates
SELECT
  COUNT(*) AS total_orders,
  SUM(amount) AS total_revenue,
  AVG(amount) AS avg_order_value,
  MIN(amount) AS smallest_order,
  MAX(amount) AS largest_order
FROM orders;

-- COUNT variations
SELECT COUNT(*) AS all_rows,
       COUNT(email) AS rows_with_email,
       COUNT(DISTINCT country) AS unique_countries
FROM customers;

▦ GROUP BY / HAVING

-- Group by single column
SELECT country, COUNT(*) AS customer_count
FROM customers
GROUP BY country
ORDER BY customer_count DESC;

-- Group by multiple columns with HAVING
SELECT category, status, AVG(price) AS avg_price
FROM products
GROUP BY category, status
HAVING AVG(price) > 50;

🔗 Jointures (référence rapide) VOIR LE GUIDE DÉTAILLÉ

-- INNER JOIN (most common)
SELECT orders.id, customers.name, orders.amount
FROM orders
INNER JOIN customers
  ON orders.customer_id = customers.id;

-- LEFT JOIN (all orders, even without customer)
SELECT orders.id, customers.name
FROM orders
LEFT JOIN customers
  ON orders.customer_id = customers.id;

📚 Guide complet : Aide-mémoire SQL JOIN avec 7 types de JOIN et des schémas visuels

⬚ Sous-requêtes

-- Subquery with IN
SELECT name, price
FROM products
WHERE category_id IN (
  SELECT id FROM categories
  WHERE name = 'Electronics'
);

-- Subquery with EXISTS
SELECT name FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
);

-- Scalar subquery
SELECT name, price,
  price - (SELECT AVG(price) FROM products) AS price_diff
FROM products;

✂️ Fonctions de chaînes

-- Extract characters by position
SELECT SUBSTRING(name, 1, 3) AS prefix FROM products;

-- First / last N characters
SELECT LEFT(email, 5), RIGHT(phone, 4) FROM users;

-- Dynamic position (find @ then extract domain)
SELECT SUBSTRING(email, INSTR(email, '@') + 1) AS domain
FROM users;

-- Common string helpers
SELECT
  UPPER(name), LOWER(name),
  TRIM(name),
  LENGTH(name)
FROM users;

📚 Guide complet : Guide interactif SQL SUBSTRING avec la syntaxe pour chaque base et des exemples pratiques