01 - Obtenir de l'aide sur les commandes SQL

• Documentation officielle
• Commande help ou help commande

# Exemple avec psql (PostgreSQL)
mydatabase=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

mydatabase=#

02 - Consulter les clés primaires et étrangères des tables

1. Afficher la structure d'une table \d nom_table; ou SELECT * FROM information_schema.table_constraints WHERE table_name = 'nom_table';
2. Visualiser la définition complète de la table SHOW CREATE TABLE nom_table; ou \d+ nom_table; sous PostgreSQL
3. Utiliser des outils graphiques spécialisés

03 - Différencier tables et vues dans la base

Il est difficile de distinguer visuellement une table d'une vue. On peut vérifier le type d'objet en consultant sa définition. \d nom_objet; indiquera s'il s'agit d'une vue avec View.

04 - Comparaison des commandes DROP, DELETE et TRUNCATE

DELETE

• Supprime des lignes spécifiques d'une table, avec possibilité d'utiliser une clause WHERE
• Chaque suppression est enregistrée comme une transaction individuelle, permettant l'annulation
• Ne libère pas l'espace physique occupé par la table ou ses index

TRUNCATE TABLE

• Supprime toutes les lignes de la table rapidement, la structure et les index restant inchangés
• Les séquences auto-incrémentées sont réinitialisées
• Opération non journalisée et irréversible, sans déclenchement de triggers
• L'espace de stockage de la table est réinitialisé à sa taille initiale

DROP TABLE

• Supprime complètement la table (structure et données)
• Libère intégralement l'espace de stockage associé
• Les procédures stockées référençant cette table deviennent invalides

En général, DROP est plus destructif que TRUNCATE, lui-même plus que DELETE. Seul DELETE fonctionne sur les vues. Sans sauvegarde, utiliser DROP et TRUNCATE avec prudence. Pour supprimer des lignes spécifiques, préférer DELETE avec une clause WHERE.

05 - Rôle, avantages et inconvénients des index

Un index est une structure de données triée utilisée par le SGBD pour accélérer l'accès aux lignes d'une table. Les implémentations courantes reposent sur les arbres B ou B+.

Les avantages des index :

• Garantir l'unicité des données via des index uniques
• Accélérer considérablement les opérations de lecture (SELECT)
• Optimiser les jointures entre tables
• Réduire le temps des opérations GROUP BY et ORDER BY
• Permettre au moteur d'optimisation de choisir de meilleurs plans d'exécution

Pourquoi ne pas indexer toutes les colonnes ? Les inconvénients sont :

• La création et la maintenance des index consomment du temps et des ressources
• Les index occupent de l'espace de stockage supplémentaire
• Les opérations d'écriture (INSERT, UPDATE, DELETE) sont ralenties par la mise à jour des index

Situations favorables à la création d'index :

1. Colonnes fréquemment utilisées dans les clauses WHERE
2. Colonnes servant de clé primaire (unicité et performance)
3. Colonnes de clé étrangère pour optimiser les jointures
4. Colonnes sur lesquelles des recherches par plage sont courantes
5. Colonnes fréquemment utilisées dans les clauses ORDER BY
6. Colonnes avec une forte sélectivité (beaucoup de valeurs distinctes)

Situations défavorables :

• Colonnes rarement consultées dans les requêtes
• Colonnes avec très peu de valeurs distinctes (faible sélectivité)
• Colonnes de type TEXT, BLOB ou données binaires volumineuses
• Environnements où les opérations d'écriture sont plus critiques que les lectures

En résumé, les index sont des outils puissants d'optimisation mais qui ont un coût en espace et en temps de modification.

06 - Utilité et cas d'usage des vues

Une vue est une table virtuelle définie par une requête SELECT. Elle permet de présenter des données issues d'une ou plusieurs tables sous un format spécifique, sans stocker de données physiquement.

Avantages des vues :

• Simplifier les requêtes complexes pour les utilisateurs
• Offrir différents angles de vue sur les mêmes données
• Assurer un niveau d'indépendance logique dans l'architecture
• Contrôler l'accès aux données sensibles

Scénarios typiques d'utilisation :

• Restreindre l'accès à certaines colonnes d'une table pour des utilisateurs spécifiques
• Unifier l'accès à des données provenant de sources multiples via une interface commune
• Masquer la complexité des jointures et des calculs aux applications clientes

07 - Intégrité des données : contraintes, triggers ou logique applicative ?

• Privilégier les contraintes déclaratives (PRIMARY KEY, FOREIGN KEY, CHECK, NOT NULL) pour leur performance et simplicité
• Utiliser des triggers pour garantir l'intégrité indépendamment de l'application accédant aux données
• Réserver la logique applicative pour les cas complexes non couverts par les mécanismes du SGBD

08 - Langages SQL : DDL, DML et DCL

Classification fonctionnelle des langages SQL :

DDL (Data Definition Language)

• Définit et modifie la structure de la base : CREATE, ALTER, DROP, TRUNCATE
• Les modifications sont immédiates et non annulables (pas de journalisation transactionnelle)

DML (Data Mnaipulation Language)

• Manipule les données : SELECT, INSERT, UPDATE, DELETE
• Les opérations sont transactionnelles et journalisées

DCL (Data Control Language)

• Gère les permissions d'accès : GRANT, REVOKE

09 - Concepts fondamentaux

Schémas de base de données

L'architecture à trois niveaux (externe, conceptuel, interne) permet l'indépendance logique et physique des données. Les différents schémas offrent des vues adaptées à chaque catégorie d'utilisateurs.

Modèles de données

• Modèle conceptuel : vision utilisateur (ex: diagrammes E-R)
• Modèle logique : implémentation pour le SGBD (relationnel, réseau, hiérarchique)

Modèle Entité-Association

Représentation graphique des entités et de leurs relations. Les entités sont en rectangle, les relations en losange, les propriétés en ovale.

Clés relationnelles

• Super-clé : ensemble d'attributs identifiant de façon unique les tuples
• Clé candidate : super-clé minimale sans redondance
• Clé primaire : clé candidate choisie comme référence principale
• Clé étrangère : référence à une clé primaire d'une autre table

Transactions

Séquence d'opérations logiques possédant les propriétés ACID :

• Atomicité : tout ou rien
• Cohérence : passage d'un état valide à un autre
• Isolation : indépendance des transactions concurrentes
• Durabilité : persistance des modifications après validation

Verrous et curseurs

Les verrous garantissent la cohérence lors d'accès concurrents. Les curseurs permettent un traitement ligne par ligne des résultats de requêtes.

Procédures stockées et triggers

Les procédures stockées sont des programmes SQL précompilés stockés dans la base. Les triggers sont des procédures spéciales exécutées automatiquement en réponse à des événements spécifiques (INSERT, UPDATE, DELETE).

10 - Installation de PostgreSQL sur Debian/Ubuntu

Installation des paquets

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresql
sudo -u postgres psql

Configuration initiale

1. Modifier le fichier /etc/postgresql/[version]/main/postgresql.confAjuster l'écoute réseau : listen_addresses = 'localhost,192.168.1.100'Configurer l'encodage par défaut dans la section [locale]
2. Redémarrer le service : sudo systemctl restart postgresql
3. Vérifier la configuraton Consulter les paramètres d'encodage : SHOW server_encoding;Vérifier les connexions actives : SELECT * FROM pg_stat_activity;

11 - Configuraton des accès distants pour PostgreSQL

Exemple d'octroi d'accès distant à un utilisateur :

-- Dans pg_hba.conf
host    all             all             192.168.1.0/24            scram-sha-256

-- Ensuite en SQL
CREATE USER monutilisateur WITH PASSWORD 'motdepasse';
GRANT ALL PRIVILEGES ON DATABASE mabase TO monutilisateur;

12 - Référence rapide des commandes SQL

Consultez la documentation officielle de votre SGBD pour les commandes spécifiques. Les standards SQL sont documentés dans les spécifications ISO/IEC.