PostgreSQL

-- Créer une table
CREATE TABLE utilisateurs (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Modifier une table
ALTER TABLE utilisateurs ADD COLUMN age INTEGER;
ALTER TABLE utilisateurs ALTER COLUMN nom TYPE VARCHAR(200);

-- Index et vues
CREATE INDEX idx_email ON utilisateurs(email);
CREATE VIEW utilisateurs_actifs AS SELECT * FROM utilisateurs WHERE actif = true;

-- Insertion
INSERT INTO utilisateurs (nom, email) VALUES ('Jean Dupont', 'jean@example.com');
INSERT INTO utilisateurs (nom, email) VALUES
    ('Alice Martin', 'alice@example.com'),
    ('Bob Leroy', 'bob@example.com')
RETURNING id, nom;

-- Mise à jour
UPDATE utilisateurs SET email = 'nouveau@example.com' WHERE id = 1;

-- UPSERT
INSERT INTO utilisateurs (email, nom)
VALUES ('test@example.com', 'Test User')
ON CONFLICT (email) DO UPDATE SET nom = EXCLUDED.nom;

-- Suppression
DELETE FROM utilisateurs WHERE id = 5;

-- CTE (Common Table Expression)
WITH ventes_mensuelles AS (
    SELECT DATE_TRUNC('month', date_vente) AS mois, SUM(montant) AS total
    FROM ventes GROUP BY DATE_TRUNC('month', date_vente)
)
SELECT * FROM ventes_mensuelles WHERE total > 10000;

-- Window Functions
SELECT nom, departement, salaire,
    AVG(salaire) OVER (PARTITION BY departement) AS salaire_moyen_dept,
    RANK() OVER (PARTITION BY departement ORDER BY salaire DESC) AS rang
FROM employes;

-- Requête récursive
WITH RECURSIVE hierarchie AS (
    SELECT id, nom, manager_id, 1 AS niveau FROM employes WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.nom, e.manager_id, h.niveau + 1
    FROM employes e JOIN hierarchie h ON e.manager_id = h.id
)
SELECT * FROM hierarchie;

-- Table avec JSONB
CREATE TABLE evenements (id SERIAL PRIMARY KEY, donnees JSONB NOT NULL);

-- Insertion JSON
INSERT INTO evenements (donnees) VALUES
    ('{"type": "click", "user": {"id": 1, "name": "Alice"}}');

-- Requêtes JSON
SELECT donnees->>'type' AS type_evenement FROM evenements;
SELECT donnees->'user'->>'name' AS nom_utilisateur FROM evenements;
SELECT * FROM evenements WHERE donnees @> '{"type": "click"}';

-- Mise à jour JSON
UPDATE evenements SET donnees = jsonb_set(donnees, '{user,status}', '"active"') WHERE id = 1;

-- Créer utilisateur/rôle
CREATE USER nom_utilisateur WITH PASSWORD 'mot_de_passe';
CREATE ROLE nom_role;

-- Gestion des privilèges
GRANT ALL PRIVILEGES ON DATABASE nom_db TO nom_utilisateur;
GRANT SELECT, INSERT ON TABLE nom_table TO nom_utilisateur;
REVOKE INSERT ON TABLE nom_table FROM nom_utilisateur;

-- Modifier utilisateur
ALTER USER nom_utilisateur WITH SUPERUSER;
GRANT nom_role TO nom_utilisateur;

-- Maintenance de base
ANALYZE nom_table;
VACUUM ANALYZE nom_table;
REINDEX TABLE nom_table;

-- Vérifier les tailles
SELECT pg_size_pretty(pg_database_size('nom_db')) AS taille_db;
SELECT schemaname, tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS taille
FROM pg_tables ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC LIMIT 10;

# Backup essentiels
pg_dump nom_db > backup.sql
pg_dump -Fc nom_db > backup.dump  # Format compressé
pg_dump -t nom_table nom_db > table_backup.sql

# Restauration
psql nom_db < backup.sql
pg_restore -d nom_db backup.dump

-- Connexions actives
SELECT pid, usename, state, query FROM pg_stat_activity WHERE state != 'idle';

-- Performance
SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10;

-- Cache hit ratio
SELECT sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio FROM pg_statio_user_tables;

-- Analyser les performances
EXPLAIN ANALYZE SELECT * FROM table WHERE condition;

-- Éviter SELECT * - être spécifique
SELECT col1, col2 FROM table;

-- Créer des indexes sur les colonnes de recherche
CREATE INDEX idx_email ON utilisateurs(email);

-- Principe du moindre privilège
REVOKE ALL ON SCHEMA public FROM PUBLIC;
CREATE ROLE lecture_seule;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO lecture_seule;

BEGIN;
SAVEPOINT mon_savepoint;
-- Opération risquée
ROLLBACK TO mon_savepoint;  -- Si erreur
COMMIT;

-- CHECK constraint
ALTER TABLE produits ADD CONSTRAINT prix_positif CHECK (prix > 0);

-- Type ENUM
CREATE TYPE statut_commande AS ENUM ('en_attente', 'expédié', 'livré');