BASES DE DONNEES :
Principes et fonctionnements,
exemples.
Barbo Bertrand – IGH - Avril 2002
Plan
• Introduction aux bases de données
• Conception d’une base de données
relationnelle
• Utilisation de MySQL et SQL
• Exercice : Conception d’une base de
données relationnelle
• Exercice : Requêtes SQL
Les différents types de base de
données
Modèle objet :
ACEDB (http://www.acedb.org/)
Bases de données relationnelles :
ORACLE
SYBASE (IMGT)
MySQL (EnsEMBL)
PostgreSQL
ACCESS
MySQL
• SGBDR (Système de Gestion de Bases de
Données Relationnelles) client-serveur
• Compatible avec le langage de requête SQL
• Permet 50 millions d’enregistrements (lignes)
• Fonctionne avec différents systèmes d’exploitation
(Windows , Unix, Linux)
• Gratuit (sauf sous certaines conditions)
• Fiable
• Compatible avec plusieurs langages de
programmation (C, C++, Eiffel, JAVA, Perl, PHP,
Python et TCL)
• Disponible sur Internet http://www.mysql.com
SQL
• Structured Query Language
Langage de Requêtes Structurées
• Pas réellement un langage, outil standardisé
de dialogue avec diverses bases de données
• Permet d’extraire des données d’une base
de données
• Peut être associé à des langages de
programmation (C, C++, PHP …)
Les Bases de données
relationnelles
Définition :
Stockage organisé de données ayant des relations
entre elles.
Intérêt :
Eviter la redondance des informations
Extraire les données de façon pertinente (requête)
Faciliter les modifications
Structure :
Constituée de tables (tableaux de données) reliées
entre elles par des relations.
Qu’est-ce qu’une table ?
Une table correspond à un thème et possède
un nom unique dans la base de données.
Une table est un tableau de données composée
d'une ou plusieurs colonnes et d’une ou
plusieurs lignes appelées tuples.
Chaque colonne possède un nom unique à
l'intérieur de la table.
Exemple de table
Animaux
Nom Espèces DateNaissance Propriétaire
Kiki Chien 1990-10-25 David
Milou Chien 1995-05-25 Tintin
Titi Oiseau 2000-01-28 Vladimir
Tom Chat 2001-03-02 Sophie
Jerry Souris 1999-04-18 Farida
Rominet Chat 2000-08-06 Vladimir
Notion de Clé
• Clé primaire :
Identifiant correspondant à une ligne unique d’une
table.
• Clé externe :
Rappel de la clé primaire dans une autre table.
Elle a la même valeur que la clé primaire, mais on
peut la retrouver plusieurs fois dans la même
table.
Table des Propriétaires
Propriétaires
ID Nom Code Postal Ville
1 David 34090 Montpellier
2 Tintin 34090 Montpellier
3 Vladimir 34730 Prades le lez
4 Sophie 34170 Castelnau le lez
5 Farida 34770 Gigean
Table des Animaux
Animaux
Nom Espèces DateNaissance Propriétaire
Kiki Chien 1990-10-25 1
Milou Chien 1995-05-25 2
Titi Oiseau 2000-01-28 3
Tom Chat 2001-03-02 4
Jerry Souris 1999-04-18 5
Rominet Chat 2000-08-06 3
Conception d’une base de
données
• Faire l’inventaire de tous les types de
données (colonnes)
• Regrouper ces types par thèmes (tables)
• Attribuer une clé primaire par table
• Définir les relations entre les tables par la
mise en place de clefs externes
Exemple de conception d’une
base de données (1)
Liste des types :
Nom de l’animal Espèce
Sexe Date de naissance
Nom du propriétaire Téléphone
Adresse …
Exemple de conception d’une
base de données (2)
ANIMAUX PROPRIO
IDAnimaux IDProprio
Nom Nom
Espèce Rue
Sexe CP
DateNaiss Ville
NomProprio Téléphone
Exemple de conception d’une
base de données (3)
ANIMAUX PROPRIO VILLE
IDAnimaux IDProprio IDVille
Nom Nom Ville
Espèce Rue CP
Sexe IDVille
DateNaiss Téléphone
IDProprio
Création d’une table (1)
CREATE TABLE < nom de la table >
( < nom de colonne 1 > < type de la colonne 1
> <contraintes 1> ,
< nom de colonne 2 > < type de la colonne 2
> <contraintes 2> ,
...
< nom de colonne N > < type de la colonne N
> <contraintes N> ) ;
Création d’une table (2)
CREATE TABLE Animaux
(
Nom CHAR(30) NOT NULL,
Espèce CHAR(30) NOT NULL,
DateNaissance DATE NOT NULL,
Propriétaire CHAR(30) NOT NULL
);
Création d’une table (3)
Nom Espèce DateNaissance Propriétaire
Destruction d’une table
DROP TABLE < nom de la table >;
destruction des informations contenues dans
la table.
destruction du schéma de la relation. (Aucune
requête ne pourra être effectuée sur cette
table dans le futur)
Insertion de tuples (1)
Ajout d'une ou plusieurs lignes dans une table.
INSERT INTO < nom d'une table > ( < liste
de colonnes > ) VALUES ( valeur1, ...,
valeurN ) ;
INSERT INTO < nom d'une table > ( < liste
de colonnes > ) < requête de sélection >;
Insertion de tuples (2)
INSERT INTO Animaux (Nom,Espèce,Date
Naissance, Propriétaire) VALUES (‘Kiki’,
‘Chien', ‘1990-10-25', ‘David’);
Insertion de tuples (3)
Animaux
Nom Espèces DateNaissance Propriétaire
Kiki Chien 1990-10-25 David
Insertion de tuples (4)
LOAD DATA LOCAL INFILE < nom du
fichier text > INTO TABLE < nom d’une
table >;
Permet d’insérer toutes les lignes d’un seul
coup.
Dans le fichier text, les champs de chaque
ligne sont séparés par des tabulations.
Insertion de tuples (5)
Animaux
Nom Espèces DateNaissance Propriétaire
Kiki Chien 1990-10-25 David
Milou Chien 1995-05-25 Tintin
Titi Oiseau 2000-01-28 Vladimir
Tom Chat 2001-03-02 Sophie
Jerry Souris 1999-04-18 Farida
Rominet Chat 2000-08-06 Vladimir
Sélection de tuples (1)
SELECT < (optionnel) clause d’unicité ALL ou
DISTINCT > < liste de colonnes ou * >
FROM < liste de tables >
(optionnel) WHERE < critère de sélection >
(optionnel) GROUP BY < liste de colonnes >
(optionnel) HAVING < critère de sélection de
group by >
(optionnel) ORDER BY < critère d’ordre >;
Sélection de tuples (2)
SELECT * FROM Animaux;
Sélection de tuples (3)
Nom Espèces DateNaissance Propriétaire
Kiki Chien 1990-10-25 David
Milou Chien 1995-05-25 Tintin
Titi Oiseau 2000-01-28 Vladimir
Tom Chat 2001-03-02 Sophie
Jerry Souris 1999-04-18 Farida
Rominet Chat 2000-08-06 Vladimir
Sélection de tuples (4)
SELECT *
FROM Animaux
WHERE Propriétaire=‘Vladimir’
;
Sélection de tuples (5)
Nom Espèces DateNaissance Propriétaire
Titi Oiseau 2000-01-28 Vladimir
Rominet Chat 2000-08-06 Vladimir
Sélection de tuples (6)
SELECT DISTINCT Espèce
FROM Animaux
;
Sélection de tuples (7)
Espèce
Chat
Chien
Oiseau
Souris
Sélection de tuples (8)
SELECT * FROM Animaux
WHERE Propriétaire LIKE ‘%d%’
;
Sélection de tuples (9)
Nom Espèces DateNaissance Propriétaire
Kiki Chien 1990-10-25 David
Titi Oiseau 2000-01-28 Vladimir
Jerry Souris 1999-04-18 Farida
Rominet Chat 2000-08-06 Vladimir
Sélection de tuples (10)
SELECT * FROM Animaux
ORDER BY Nom DESC
;
Sélection de tuples (11)
Nom Espèces DateNaissance Propriétaire
Tom Chat 2001-03-02 Sophie
Titi Oiseau 2000-01-28 Vladimir
Rominet Chat 2000-08-06 Vladimir
Milou Chien 1995-05-25 Tintin
Kiki Chien 1990-25-10 David
Jerry Souris 1999-04-18 Farida
Sélection de tuples (12)
SELECT COUNT(*) FROM Animaux ;
SELECT Propiétaire, COUNT(*) AS
‘Nombre Animaux’
FROM Animaux
GROUP BY Propriétaire;
Sélection de tuples (13)
COUNT(*)
6
Propriétaire Nombre Animaux
David 1
Farida 1
Sophie 1
Tintin 1
Vladimir 2
Suppression de tuples (1)
DELETE FROM < nom d’une table >
(optionnel) WHERE < critère de sélection >;
Remarque : Effectuer une suppression de
tuples sans clause WHERE, efface
l’ensemble des tuples.
DELETE FROM Animaux
WHERE Propriétaire=‘Vladimir’;
Suppression de tuples (2)
Animaux
Nom Espèces DateNaissance Propriétaire
Kiki Chien 1990-10-25 David
Milou Chien 1995-05-25 Tintin
Tom Chat 2001-03-02 Sophie
Jerry Souris 1999-04-18 Farida
Suppression de tuples (3)
DELETE FROM Animaux
WHERE Espèce IN (‘Chien’, ‘Oiseau’);
Suppression de tuples (4)
Animaux
Nom Espèces DateNaissance Propriétaire
Tom Chat 2001-03-02 Sophie
Jerry Souris 1999-04-18 Farida
Rominet Chat 2000-08-06 Vladimir
Modification de tuples (1)
UPDATE < nom d’une table >
SET < attribut 1 > = < expression 1 >,
< attribut 2 > = < expression 2 >,
…,
< attribut N > = < expression N >,
(optionnel) WHERE < critère de sélection>;
Remarque : Effectuer une modificaton sans clause
WHERE, modifie l’ensemble des tuples de la
table.
Modification de tuples (2)
UPDATE Animaux
SET Espèce=‘Canari’, Propriétaire=‘David’
WHERE Nom = ‘Titi’;
Modification de tuples (3)
Animaux
Nom Espèces DateNaissance Propriétaire
Kiki Chien 1990-10-25 David
Milou Chien 1995-05-25 Tintin
Titi Canari 2000-01-28 David
Tom Chat 2001-03-02 Sophie
Jerry Souris 1999-04-18 Farida
Rominet Chat 2000-08-06 Vladimir
Création de la table Propriétaires
CREATE TABLE Propriétaires
(
ID INT NOT NULL AUTO_INCREMENT,
Nom VARCHAR(30) NOT NULL,
CP NUMERIC(5,0) NOT NULL,
Ville CHAR(30) NOT NULL,
PRIMARY KEY (ID)
);
LOAD DATA LOCAL INFILE Propriétaires.txt
INTO TABLE Propriétaires;
Table des Propriétaires
Propriétaires
ID Nom Code Postal Ville
1 David 34090 Montpellier
2 Tintin 34090 Montpellier
3 Vladimir 34730 Prades le lez
4 Sophie 34170 Castelnau le lez
5 Farida 34770 Gigean
Table des Animaux
Animaux
Nom Espèces DateNaissance Propriétaire
Kiki Chien 1990-10-25 1
Milou Chien 1995-05-25 2
Titi Oiseau 2000-01-28 3
Tom Chat 2001-03-02 4
Jerry Souris 1999-04-18 5
Rominet Chat 2000-08-06 3
Requête sur deux tables (1)
SELECT Animaux.Nom, Ville
FROM Animaux, Propriétaires
WHERE Propriétaire = ID
;
Requête sur deux tables (2)
Nom Ville
Kiki Montpellier
Milou Montpellier
Titi Prades le lez
Tom Castelnau le lez
Jerry Gigean
Rominet Prades le lez
Requête double sur une même
table (1)
SELECT < liste de colonnes ou * >
FROM < liste de tables > < alias >
(optionnel) WHERE < critère de sélection >
(optionnel) GROUP BY < liste de colonnes >
(optionnel) HAVING < critère de sélection de
group by >
(optionnel) ORDER BY < critère d’ordre >
;
Requête double sur une même
table (2)
Quel sont les Nom et Date de Naissance des animaux
plus âgés que Titi ?
SELECT a2.Nom, a2.DateNaissance
FROM Animaux a1, Animaux a2
WHERE
a2.DateNaissance < a1.DateNaissance
AND a1.Nom=‘Titi’
;
Requête double sur une même
table (3)
Nom DateNaissance
Kiki 1990-10-25
Milou 1995-05-25
Jerry 1999-04-18
Exercice : conception d’une db
Réaliser le schéma organisationnel de la base de
données d’une bibliothèque municipale à partir de
la liste de données suivantes :
Titre du livre / Nom du lecteur / Adresse du
lecteur / Année de parution du livre / Localisation
du livre / Nom, date de naissance et de décès,
adresse et téléphone de l’auteur / Nom, adresse et
téléphone de l’éditeur / Date d’emprunt et de
restitution du livre / Date de naissance du lecteur
Exercice - Schéma des tables
LIVRES LECTEURS AUTEURS
IDLivre IDLecteur IDAuteurs
Titre Nom Nom
IDAuteurs Rue DateNaissance
Année Téléphone DateDécès
IDEditeurs IDVille Rue
Localisation DateNaissance IDVille
Téléphone
EMPRUNTS EDITEURS
IDEmprunt IDEditeurs VILLES
IDLivre Nom IDVille
IDLecteur Rue Ville
DateEmprunt IDVille CP
DateRestitution Téléphone
Exercice - Schéma des relations
EMPRUNTS LIVRES
LECTEURS AUTEURS EDITEURS
VILLES
Exercice SQL
Schéma des tables
Client (numcli, nom, prenom, datenaiss, cp,
rue, ville)
Fournisseur (numfour, raisonsoc)
Produit (numprod, desi, prixuni, numfour)
Commande (numcli, numprod, quantite,
datec)
Exercice SQL - Fonctions utiles
>, =, <, >= ,<=
BETWEEN … AND …
IS NULL
WHERE … LIKE …
WHERE …IN …
AVG(nom de la colonne)
COUNT(nom de la colonne)
WHERE … AND …
DISTINCT
GROUP BY … HAVING …
Exercice SQL - Réponses (1)
1) SELECT * FROM client;
2) SELECT * FROM client ORDER BY nom DESC;
3) SELECT desi, prixuni/6 FROM produit ;
4) SELECT nom,prenom FROM client;
5) SELECT nom,prenom FROM client WHERE ville='Lyon';
6) SELECT * FROM commande
WHERE quantite>=3;
7) SELECT desi FROM produit
WHERE prixuni BETWEEN 50 AND 100;
8) SELECT * FROM commande
WHERE quantite IS NULL;
9) SELECT nom,ville FROM client
WHERE ville LIKE '%ll%';
Exercice SQL - Réponses (2)
10) SELECT prenom FROM client WHERE nom IN
('Dupont','Durand','Martin');
SELECT prenom FROM client WHERE numcli IN (1,4,3);
11) SELECT avg(prixuni) FROM produit;
12) SELECT count(*) FROM commande;
13) SELECT nom,datec,quantite FROM client,commande
WHERE client.numcli=commande.numcli;
14) SELECT client.numcli, nom, datec, quantite FROM client,
commande WHERE client.numcli = commande.numcli ;
SELECT c1.numcli,nom,datec,quantite
FROM client c1,commande c2
WHERE c1.numcli=c2.numcli;
Exercice SQL - Réponses (3)
15) SELECT DISTINCT nom FROM client, commande
WHERE client.numcli = commande.numcli and quantite=1 ;
SELECT distinct nom FROM client c1,commande c2
WHERE c1.numcli=c2.numcli AND quantite=1;
16) SELECT numcli,sum(quantite) FROM commande
GROUP BY numcli;
SELECT DISTINCT nom, sum(quantite) FROM client, commande
WHERE commande.numcli=client.numcli GROUP BY
commande.numcli ;
17) SELECT numprod, avg(quantite) FROM commande GROUP BY
numprod HAVING count(numprod)>1;
Bonnes adresses
Exercices SQL en ligne sur une base de données MySQL
http://eric.univ-lyon2.fr/~jdarmont/enseignement/tutoriel-sql/
Cours d’introduction à MySQL
http://magali.contensin.free.fr/html/MySQL/Cours_MySQL.html
Cours MySQL
http://dev.nexen.net/docs/mysql/chargement.html
Cours SQL
http://www.allprog.com/SQL/
