Bases de Données Relationnelles et SQL
Objectifs
Qu’est-ce qu’une base de données ?
- Collection organisée de données
- Stockée électroniquement
- Structurée pour faciliter l’accès et la gestion
- Conçue pour minimiser la redondance
- peut être utilisée par des applications
- interrogeables, modifiables par les utilisateurs
Base de données relationnelles
- Organise les données dans des
Tables
- Tables interconnectées
- Tables structurées
- Table = tableau
SGBD
Un Système de Gestion de Base de Données (SGBD) est un logiciel qui permet de :
- stocker
- organiser
- gérer des données de manière structurée
Exemples de SGBD :
- relationnelles : PostgreSQL, MySQL, Oracle Database
- NoSQL : MongoDB, Cassandra
- SGBD : interface principale avec les données
- Pour nous PostgreSQL, gratuit, libre, installé VM
- NoSQL : Not Only SQL
Kesako
- PostgreSQL : SGBD
- DBeaver : outil client qui permet de se connecter à un SGBD
- SQL : langage de programmation conçu pour gérer et manipuler des bases de données relationnelles.
On parle de SQL ensuite
Types de données
Il existe de très nombreux types de données que l’on peut stocker, les principaux :
- Chaîne de caractères
- Numérique
- Booléen
- Date, Temps
- Géométrique
Plus de détails sur la documentation PostgreSQL.
- Date, Temps -> plein de fonctions
- Géométrique -> contours géographiques
Types - exemple
CREATE TABLE personne (
PRIMARY KEY,
id_personne SERIAL VARCHAR(100) NOT NULL,
nom VARCHAR(100) NOT NULL,
prenom DATE,
date_naissance VARCHAR(255) UNIQUE,
email INT CHECK (nb_enfants >= 0),
nb_enfants DECIMAL(3, 2)
taille_m BOOLEAN,
est_actif DEFAULT NOW()
last_updated TIMESTAMPTZ );
- SERIAL : séquence
- NOT NULL : obligatoire
- UNIQUE : distinctes, mais n’empêche pas les NULL
- DECIMAL(3, 2) : de -9.99 à 9.99
- 3 chiffres significatifs dont 2 après la virgule
Code bien aligné, Mots clés en majuscule
Table
Une Table
est composée de lignes et de colonnes :
- une
ligne
représente un enregistrement spécifique - une
colonne
représente un attribut particulier de ces enregistrements Clé primaire
(PK) : colonne ou un ensemble de colonnes qui identifie de manière unique chaque enregistrement dans une table- permet d’identifier une ligne sans ambiguïté
PK :
- obligatoire (non null)
- unique
Table - exemple
Modèle Physique des Données (UML)
id_joueuse (PK) | nom | prenom | date_naissance | pays |
---|---|---|---|---|
1 | Sebag | Marie | 1986-10-15 | France |
2 | Polgar | Judit | 1976-07-23 | Hongrie |
3 | Hou | Yifan | 1994-02-27 | Chine |
4 | Kosteniuk | Alexandra | 1984-04-23 | Suisse |
5 | Ju | Wenjun | 1991-01-31 | Chine |
Associations entre tables
Clé étrangère
Une clé étrangère (FK) :
- permet de faire le lien entre 2 tables
- c’est une colonne d’une table A
- elle correspond à la clé primaire d’une table B
Types d’associations
1..1
: Une Personne a un Passeport et un Passeport appartient à une seule Personne- clé étrangère dans l’une des 2 tables
1..*
: Une Joueuse joue pour une seule équipe Equipe. Une Equipe est composée de plusieurs Joueuses- clé étrangère dans la table Joueuse
*..*
: Un Etudiant suit plusieurs Cours et un Cours et suivi par plusieurs Etudiants- table d’association entre Etudiant et Cours
Lien entre 2 tables via une clé étrangère
id_joueuse | nom | prenom | date_naissance | code_pays |
---|---|---|---|---|
1 | Sebag | Marie | 1986-10-15 | FR |
2 | Polgar | Judit | 1976-07-23 | HU |
3 | Hou | Yifan | 1994-02-27 | CN |
4 | Kosteniuk | Alexandra | 1984-04-23 | CH |
5 | Ju | Wenjun | 1991-01-31 | CN |
code_pays | nom |
---|---|
CH | Suisse |
CN | Chine |
FR | France |
HU | Hongrie |
On supposera ici :
- Une joueuse a un pays
- Un pays a plusieurs joueuses
Lien entre 2 tables via une table d’association
id_joueuse | nom | prenom | date_naissance | code_pays |
---|---|---|---|---|
1 | Sebag | Marie | 1986-10-15 | FR |
2 | Polgar | Judit | 1976-07-23 | HU |
3 | Hou | Yifan | 1994-02-27 | CN |
4 | Kosteniuk | Alexandra | 1984-04-23 | CH |
5 | Ju | Wenjun | 1991-01-31 | CN |
id_joueuse | id_tournoi |
---|---|
1 | 1 |
1 | 2 |
3 | 2 |
4 | 1 |
4 | 2 |
id_tournoi | nom | ville |
---|---|---|
1 | Norway Chess | Oslo |
2 | Tata Steel | Wijk aan Zee |
- Une joueuse peut participer a plusieurs tournois
- Un tournoi accueille plusieurs joueuses
SQL
- SQL : Structured Query Language
- Inventé en 1970 par Edgar F. Codd
- Langage de programmation
- Utilisé pour gérer et manipuler des bases de données relationnelles
- très très très utilisé
- tous les langages qui traitent de la data peuvent encapsuler du SQL
Opérations CRUD
SQL permet d’effectuer les opérations du CRUD :
SELECT
: récupérer des données depuis une tableINSERT
: insérer de nouvelles données dans une tableUPDATE
: mettre à jour des données existantesDELETE
: supprimer des données d’une table
Create, Read, Update, Delete
Actions sur une table
Créer une Table
-- Création de la table personne (cette ligne est un commentaire)
CREATE TABLE personne (
INT PRIMARY KEY,
id_personne VARCHAR(30) NOT NULL,
nom VARCHAR(40),
prenom DATE,
date_naissance
adresse TEXT );
La table est créée mais vide.
id_personne | nom | prenom | date_naissance | adresse |
---|---|---|---|---|
Une bonne pratique est de stocker les tables dans un schéma pour bien organiser votre base de données.
CREATE SCHEMA ran;
CREATE TABLE ran.personne (
INT PRIMARY KEY,
id_personne ...
);
Insérer des données
INSERT INTO personne (id_personne, nom, prenom, date_naissance, adresse)
VALUES
1, 'Gatore' , 'Ali' , '1990-05-15', 'Amiens'),
(2, 'Dure' , 'Laure', '1985-09-22', 'Auxerre'),
(3, 'Erateur', 'Maud' , '1995-03-10', 'Lille'); (
id_personne | nom | prenom | date_naissance | adresse |
---|---|---|---|---|
1 | Gatore | Ali | 1990-05-15 | Amiens |
2 | Dure | Laure | 1985-09-22 | Auxerre |
3 | Erateur | Maud | 1995-03-10 | Lille |
Supprimer une table
DROP TABLE personne;
Si ensuite, vous essayez :
SELECT *
FROM personne;
ERREUR: la relation « personne » n’existe pas
Actions sur les lignes
Sélectionner tout
Pour afficher tout le contenu d’une table.
SELECT *
FROM personne;
- Une requête se termine par un ;
- Possible de limiter le nombre de lignes affichées
- LIMIT 5
Filter les lignes
SELECT *
FROM personne
WHERE adresse LIKE 'A%'
AND prenom = 'Laure'
AND adresse IN ('Amiens', 'Auxerre')
AND adresse IS NOT NULL
AND adresse != 'Lille'
AND id_personne BETWEEN 2 AND 3;
Pour une meilleure lisibilité, alignez votre code !
id_personne | prenom | nom | date_naissance | adresse |
---|---|---|---|---|
2 | Laure | Dure | 1985-09-22 | Auxerre |
La clause LIKE
est utilisée pour rechercher un texte spécifique dans une colonne de texte
%
représente zéro, un ou plusieurs caractères_
représente un seul caractère
Mettre à jour des lignes
UPDATE personne
SET adresse = 'Amiens'
WHERE id_personne = 2;
Si vous refaites un SELECT * FROM personne;
id_personne | prenom | nom | date_naissance | adresse |
---|---|---|---|---|
1 | Ali | Gatore | 1990-05-15 | Amiens |
2 | Laure | Dure | 1985-09-22 | Amiens |
3 | Maud | Erateur | 1995-03-10 | Lille |
- simples quotes en SQL
- pas de double
Supprimer des lignes
DELETE FROM personne
WHERE prenom = 'Ali';
Pour ensuite faire revivre Ali :
INSERT INTO personne (id_personne, nom, prenom, date_naissance, adresse)
VALUES (1, 'Gatore', 'Ali', '1990-05-15', 'Amiens');
id_personne | prenom | nom | date_naissance | adresse |
---|---|---|---|---|
2 | Laure | Dure | 1985-09-22 | Amiens |
3 | Maud | Erateur | 1995-03-10 | Lille |
Actions sur les colonnes
Sélectionner des colonnes
SELECT nom,
prenomFROM personne;
nom | prenom |
---|---|
Gatore | Ali |
Dure | Laure |
Erateur | Maud |
Renommer une colonne
ALTER TABLE personne
RENAME COLUMN date_naissance TO dnais;
id_personne | nom | prenom | dnais | adresse |
---|---|---|---|---|
1 | Gatore | Ali | 1990-05-15 | Amiens |
2 | Dure | Laure | 1985-09-22 | Amiens |
3 | Erateur | Maud | 1995-03-10 | Lille |
Ajouter un attribut
ALTER TABLE personne
ADD joue_echecs BOOLEAN;
Vous pouvez ajouter une valeur par défaut.
ALTER TABLE personne
ADD joue_echecs BOOLEAN DEFAULT true;
id_personne | nom | prenom | dnais | adresse | joue_echecs |
---|---|---|---|---|---|
1 | Gatore | Ali | 1990-05-15 | Amiens | true |
2 | Dure | Laure | 1985-09-22 | Amiens | true |
3 | Erateur | Maud | 1995-03-10 | Lille | true |
Supprimer une colonne
ALTER TABLE personne
DROP COLUMN joue_echecs;
id_personne | nom | prenom | dnais | adresse |
---|---|---|---|---|
1 | Gatore | Ali | 1990-05-15 | Amiens |
2 | Dure | Laure | 1985-09-22 | Amiens |
3 | Erateur | Maud | 1995-03-10 | Lille |
Renommer à l’affichage
Le mot clé AS
permet de renommer une colonne à l’affichage.
⚠️ Il ne change pas le nom de la colonne.
SELECT prenom,
AS Ville
adresse FROM personne;
prenom | Ville |
---|---|
Maud | Lille |
Ali | Amiens |
Laure | Amiens |
Jointures
Alias
Jusqu’à maintenant, nous n’avions qu’une seule table.
Nous savions donc que le champ nom venait de la table personne.
Comment faire si nous faisons une jointure avec une table qui a également une colonne nommée nom ?
SELECT p.nom -- 2. p.nom : attribut nom de la table d'alias p i.e. personne
FROM personne p -- 1. nous déclarons p comme alias de la table personne
Jointure totale
SELECT p.prenom,
c.produit,
c.quantiteFROM personne p
JOIN commande c ON p.id_personne = c.id_personne
WHERE prenom = 'Laure';
SELECT p.prenom,
c.produit,
c.quantiteFROM personne p
JOIN commande c USING(id_personne);
Si et seulement si les 2 colonnes qui permettent de faire la jointure ont le même nom.
↪️ Alors vous pouvez utiliser cette syntaxe avec USING.
id_personne | nom | prenom | dnais | adresse |
---|---|---|---|---|
1 | Gatore | Ali | 1990-05-15 | Amiens |
2 | Dure | Laure | 1985-09-22 | Amiens |
3 | Erateur | Maud | 1995-03-10 | Lille |
CREATE TABLE commande (
INT PRIMARY KEY,
id_commande VARCHAR(50),
produit INT,
quantite DECIMAL(10, 2),
prix_unitaire INT,
id_personne FOREIGN KEY (id_personne) REFERENCES personne(id_personne)
);
INSERT INTO commande (id_commande, produit, quantite, prix_unitaire, id_personne) VALUES
1, 'livre', 1, 10, 2),
(2, 'pain', 3, 2, 3),
(3, 'pomme', 10, 0.5, 2); (
id_commande | produit | quantite | prix_unitaire | id_personne |
---|---|---|---|---|
1 | livre | 1 | 10 | 2 |
2 | pain | 3 | 2 | 3 |
3 | pomme | 10 | 0.5 | 2 |
prenom | produit | quantite |
---|---|---|
Laure | livre | 1 |
Laure | pomme | 10 |
Maud | pain | 3 |
Faire une jointure c’est comme créer un gros tableau fusionné
Types de jointures
Dans la jointure précédente :
- Laure apparait 2 fois car elle a 2 commandes
- Maud apparait une fois
- Ali n’apparait pas
Comment faire pour inclure Ali dans le tableau même sans commande ?
- jointure interne (INNER JOIN)
- la valeur de id_personne doit être présente dans les 2 tables
- sinon id_personne est dans une seule, alors rien n’est affiché la concernant
Jointures externes
SELECT p.prenom,
c.produit,
c.quantiteFROM personne p
LEFT JOIN commande c USING(id_personne);
LEFT JOIN
signifie que l’on garde tout le contenu provenant de la table précédente- et que l’on complète avec le contenu de la table qui suit
RIGHT JOIN
pour faire l’inverse
prenom | produit | quantite |
---|---|---|
Laure | livre | 1 |
Laure | pomme | 10 |
Maud | pain | 3 |
Ali |
La jointure externe effectuée avec le mot clé LEFT JOIN
indique que nous affichons :
- toutes les données provenant de la table personnes
- complétées par les données de la table commandes
- pour les lignes où le lien est établi
Agrégation
GROUP BY
- Utilisé pour regrouper les résultats en fonction d’une ou plusieurs colonnes
- Permet l’utilisation de fonctions d’agrégation telles COUNT, SUM, AVG…
SELECT adresse,
COUNT(1)
FROM personne
GROUP BY adresse;
adresse | count |
---|---|
Amiens | 2 |
Lille | 1 |
HAVING
Pour filter après un GROUP BY
SELECT adresse,
COUNT(1)
FROM personne
GROUP BY adresse
HAVING COUNT(1) > 1;
adresse | count |
---|---|
Amiens | 2 |
- ne pas confondre avec
WHERE
HAVING
sert à filter après un GROUP BY
Ordonner les lignes
SELECT *
FROM personne
ORDER BY dnais DESC
- Pour ordonner les résultats
- Ajouter éventuellement DESC pour décroissant
id_personne | nom | prenom | dnais | adresse |
---|---|---|---|---|
3 | Erateur | Maud | 1995-03-10 | Lille |
1 | Gatore | Ali | 1990-05-15 | Amiens |
2 | Dure | Laure | 1985-09-22 | Amiens |
Possible d’ordonner selon plusieurs colonnes
Formes normales
1ere forme normale
Une relation est de première forme normale (1NF) si
- elle possède au moins une clé
- et si tous ses attributs sont atomiques
- Ne contient qu’une seule valeur pour un tuple donné
- Donc ne regroupe pas un ensemble de plusieurs valeurs
id | nom |
---|---|
1 | Ali Gator |
2 | Laure Dure |
3 | Maud Erateur |
Cette table ne respecte pas la 1NF car son attribut nom n’est pas atomique.
Autres formes normales
- 2NF : 1NF + tout attribut non clé dépend de la clé
- 3NF : 2NF + un attribut non clé ne peut pas dépendre d’un autre attribut non clé
Les schémas
Une bonne pratique (non appliquée ici) est de classer nos tables dans différents schémas.
De la même manière que vous rangez des fichiers dans des dossiers, vous vous y retrouverez plus facilement en rangeant les tables dans des schémas
.
Si vous ne déclarez pas de shéma lors de la création d’une table, elle va dans le schéma public.
Schémas - exemple
Créer un schéma pour le projet info
CREATE schema projet;
À la création, stocker la table dans le schéma
CREATE table projet.joueuse(
...
);
Préciser le schéma lors des requêtes
SELECT *
FROM projet.joueuse;
SQL sans table
SELECT CURRENT_DATE;
SELECT 1 + 2;
SELECT 1 > 2;
SELECT 'Salut';
Autres notions non abordées
- ACID, COMMIT, ROLLBACK
- vues, snapshot
- index
- droits : grant / revoke
- WITH (Common Table Expressions)
- UNION, INTERSECT, EXCEPT
- DISTINCT
- EXISTS
- SEQUENCE