SGBD et SQL
I. Système de gestion de base de données⚓︎
Pour manipuler les données présentes dans une base de données (écrire, lire ou encore modifier), il est nécessaire d'utiliser un type de logiciel appelé "système de gestion de base de données" très souvent abrégé en SGBD. Il existe une multitude de SGBD : gratuits/payants, libres/propriétaires...
Les fonctions du SGBD⚓︎
Les SGBD permettent de grandement simplifier la gestion des bases de données :
-
Lecture, écriture, modification des informations : les SGBD permettent de gérer les contenus dans une base de données
-
Autorisations d'accès : Il est en effet souvent nécessaire de contrôler les accès par exemple en permettant à l'utilisateur A de lire et d'écrire dans la base de données alors que l'utilisateur B aura uniquement la possibilité de lire les informations contenues dans cette même base de données.
-
Sécuriser les bases de données : les fichiers des bases de données sont stockés sur des disques durs, et peuvent subir des pannes. Il est souvent nécessaire que l'accès aux informations contenues dans une base de données soit maintenu, même en cas de panne matérielle. Les bases de données sont donc dupliquées sur plusieurs ordinateurs afin qu'en cas de panne d'un ordinateur A, un ordinateur B contenant une copie de la base de données présente dans A, puisse prendre le relais. Tout cela est très complexe à gérer, en effet toute modification de la base de données présente sur l'ordinateur A doit entrainer la même modification de la base de données présente sur l'ordinateur B. Cette synchronisation entre A et B doit se faire le plus rapidement possible, il est fondamental d'avoir des copies parfaitement identiques en permanence. C'est aussi les SGBD qui assurent la maintenance des différentes copies de la base de données.
-
Accès concurrent : plusieurs personnes peuvent avoir besoin d'accéder aux informations contenues dans une base de données en même temps. Cela peut parfois poser problème, notamment si les 2 personnes désirent modifier la même donnée au même moment (on parle d'accès concurrent). Ces problèmes d'accès concurrent sont aussi gérés par les SGBD.
Utilisation des SGBD
L'utilisation des SGBD explique en partie la supériorité de l'utilisation des BDD sur des solutions plus simples, mais aussi beaucoup plus limitées, comme les fichiers au format CSV.
II. Le langage SQL⚓︎
III. Premiers pas en SQL⚓︎
SQL
Nous avons vu en cours ce qu'est le modèle relationnel et ce qu'est le schéma relationnel.
Nous allons maintenant aborder la pratique : créer, gérer, consulter une base de données.
Pour cela nous utiliserons le langage le plus utilisé : Structured Query Langage, plus communément nommé SQL.
Dans ce tout premier TP nous allons créer une base de données ne contenant qu'une seule table.
1. création et gestion d'une table⚓︎
La table : eleves
Attributs :
- id (un identifiant unique servant de clef primaire)
- nom
- prénom
- date_naiss
- classe
- nb_heures (nombre d'heures dans son EDT)
Syntaxe pour créer une table
CREATE TABLE (
attribut type ,
attribut type ,
attribut type
);
On peut parfois préceder de :
DROP TABLE IF EXISTS table ;ceci afin de supprimer la table si elle existe déjà.
🖐 attention ceci est à manier avec précaution, vous allez détruire une table existante ! Mais c'est pratique dans nos TP : quand vous aurez fait une erreur, vous devrez souvent reconstruire la table, et cette ligne DROP permettra de le faire facilement. Vous pourrez aussi utiliser le menu Cellule -> exécuter tout pour refaire tout le TP jusqu'au point ou vous en étiez.
On peut ajouter des contraintes, par exemple, au moins l'un des attributs sera clef primaire.
✏️ La contrainte NOT NULL est utilisée pour indiquer que cet attribut doit avoir une valeur non nulle (champ obligatoire).
✏️ Le type, pour nous, sera : INTEGER, TEXT, REAL ou DATE. Mais il en existe d'autres.
✏️ pour les dates, on précise le format (voir ci-dessous).
✏️Des virgules séparent les attributs, le dernier n'est pas ponctué par la virgule.
Les schéma de notre BDD sera limité à une seule table :
eleves (id , nom , prenom, date_naiss, classe, nb_heures)
Allez, on se lance :
Nous avons créé une table, ajoutons des éléments.
Insérer des éléments
✏️ Pour cela on utilise
INSERT INTO table (liste des attributs) VALUES (... , ... , ...) , (... , ... , ...) , (... , ... , ...) ;
La liste des attributs n'est pas obligatoire mais recommandée.
Ajoutons 2 élèves :
Les contraintes d'intégrité :⚓︎
Le SGBD vérifie toujours que tout est correct, et refuse des insertions qui briseraient l'intégrité de la base.
Essayons d'ajouter un 3ème élève avec un id déjà utilisé, rompant la contrainte d'unicité de la clef primaire :
Le SGBD a fait son travail et nous a empêché de mettre la table en erreur.
Bon, ajoutons quand même Alfred mais essayons d'omettre sa date de naissance, qu'on va mettre à NULL :
Rien ne lui échappe !!!!
Bon cette fois, on le remet correctement :
À vous de jouer 1
Ajoutez les élèves suivants :
Solution
INSERT INTO eleves
(id, nom, prenom, date_naiss, classe, nb_heures)
VALUES
(4, 'Torvalds', 'Linus', '28.12.1969', 'TG1', 22),
(5, 'Babbage', 'Charles', '26.12.1791', 'TG1', 39),
(6, 'Turing', 'Alan', '23.06.1912', 'TG1', 29);
Vérifions notre table :
Nous reviendrons plus tard sur l'instruction SELECT. Pour l'instant, il est pratique de connaître la syntaxe suivante, qui permet d'afficher tout le contenu de la table eleves.
Modifier un enregistrement
Il y a une erreur dans la table : Georges Clooney est bien né le 6 mai, mais en 1961, pas en 1960.
Modifier un enregistrement
✏️ Rectifions. Pour cela on utilise :
UPDATE table SET attribut = nouvelle_valeur WHERE condition;
Il peut y avoir différentes conditions. Nous allons voir deux exemples différents
Exemple 1
Exemple 2
Mais une nouvelle règle s'applique : tous les élèves ayant moins de 30h de cours se voient imposer 2h supplémentaires d'accompagnement :
À vous de jouer 2
Il y a une erreur : tous les élèves de TG1 sont en réalité dans la classe InfoMaster
Faire la modification (vous devez modifier tout en une seule fois)
Solution
UPDATE eleves
SET classe = 'infoMaster'
WHERE classe = 'TG1';
Comment vérifier ?
Solution
SELECT *
FROM eleves;
Supprimer un (ou plusieurs) enregistrement
✏️ La syntaxe est :
DELETE
FROM table
WHERE condition;
Supprimer Belmondo
Bon, allez, on remet Bebel !
À vous de jouer 3
1. Supprimer l'élève Turing, puis vérifier la base de données,
Solution
DELETE
FROM eleves
WHERE nom = 'Turing';
SELECT *
FROM eleves;
2. Réinscrire Turing et vérifier à nouveau la base de données.
Solution
INSERT INTO eleves
(id, nom, prenom, date_naiss, classe, nb_heures)
VALUES
(6, 'Turing', 'Alan', '23.06.1912', 'infoMaster', 31);
SELECT *
FROM eleves;
2. Consultation d'une table⚓︎
Sélection
SELECT * FROM <nom_table>
Cette requête va nous donner toutes les lignes et toutes les colonnes de la table.
Projection
SELECT <nom_attribut>, <nom_attribut> FROM <nom_table>
sélectionne une ou plusieurs colonnes :
Les élèves
les conditions courantes
Le caractère % représente n'importe quelle chaîne de caractères. On peut l'insérer n'importe où dans la chaîne de caractères de comparaison.
Par exemple :
SELECT *
FROM eleve
WHERE nom LIKE '%a%'
sélectionne toutes les lignes où la valeur de l'attribut nom est une chaîne de caractères qui contient un a.
Restriction : extraire une ou des lignes
Exemple : quels sont les élèves de la classe TG8 ?
SELECT *
FROM eleves
WHERE classe = 'TG8';
Restriction avec projection
On va combiner les deux pour obtenir seulement les cases rouges :
Exemple
Quels sont les prénoms des élèves qui ne sont pas en TG8 ?
Autre syntaxe
Autre syntaxe équivalente :
SELECT nom, prenom
FROM eleves
WHERE classe != 'TG8';
Les conditions courantes
- = ou != (noté aussi <>)
- < > <= >=
- LIKE
- is (nous verrons plus tard le is)
Exemples
À vous de jouer 4
Écrire la requête qui permet d'afficher le nom, la classe et le nombre d'heures des élèves qui ont moins de 25 heures dans leur emploi du temps.
Solution
SELECT nom, classe, nb_heures
FROM eleves
WHERE nb_heures < 25;
3. Faire des calculs, afficher de nouvelles colonnes⚓︎
Tester
1. Tester
Solution
Cela nous donne le nombre d'élèves de la table eleves
2. Tester
3. Tester
Solution
AVG donne la moyenne.
4. Renommer des colonnes⚓︎
AS
À vous de jouer 5
Ecrire la requête qui affiche le nombre moyen d'heures des élèves en infoMaster
Solution
SELECT AVG(nb_heures)
FROM eleves
WHERE classe = 'infoMaster';
5. Trier⚓︎
Tester
1. Tester
2. Tester
Le tri de chaînes de caractères
Le tri de chaînes de caractères se fait par ordre alphabétique.
À vous de jouer 6
Ecrire la requête qui permet d'afficher les noms, et dates de naissances, par ordre alphabétique, des élèves nés au 20ème siècle.
Aide 1
utiliser la date de naissance et LIKE
Aide 2
On peut utiliser plusieurs fois %
Solution
SELECT nom, date_naiss
FROM eleves
WHERE date_naiss LIKE '%.%.19%'
ORDER BY nom ;
IV. Un peu plus loin avec une seule table⚓︎
En bref
😊 Nous avons eu l'occasion d'étudier la structure d'une base de données relationnelle, nous allons maintenant apprendre à utilise le langage SQL (Structured Query Language) pour :
- créer une base des données
- créer des attributs,
- ajouter de données
- modifier des données
- interroger une base de données afin d'extraire des informations.
SQL est propre aux bases de données relationnelles (les autres types de bases de données utilisent d'autres langages).
Différents langages
👉 SQLite est un système de gestion de base de données relationnelles très répandu.
Noter qu'il existe d'autres systèmes de gestion de base de données relationnelle comme MySQL ou PostgreSQL.
Dans tous les cas, le langage de requête utilisé est le SQL (même si parfois on peut noter quelques petites différences). Ce qui sera vu ici avec SQLite pourra, à quelques petites modifications près, être utilisé avec, par exemple, MySQL.
1. Création d'une base de données⚓︎
Exécuter :
Clé primaire
😊 Vous venez de créer votre première table.
👉 Revenons sur cette première requête : CREATE TABLE : permet de créer la table nommée "livre".
(id INT PRIMARY KEY, titre TEXT, auteur TEXT, ann_publi INT, note INT)
👉 Cette ligne permet de créer les attributs et leur domaine :
id : entier
titre : texte
auteur : texte
ann_pulbi : entier
note : entier
👉 Le terme de PRIMARY KEY associé à id permet de définir la clé primaire et le SGBD nous avertira si l'on tente d'attribuer 2 fois la même valeur à l'attribut.
2. Ajout de données⚓︎
INSERT INTO
INSERT INTO
Exécuter :
À vous de jouer 7
Écrivez et testez une requête permettant d'ajouter ces deux livres :
- Le grand secret, de René Barjavel, paru en 1973, note 7
- Voyage au centre de la Terre, et celui de Jules Verne, paru en 1864, note 8.
Solution
INSERT INTO livre
(id, titre, auteur, ann_publi, note)
VALUES
(17, 'Le grand secret', 'Barjavel', 1973, 7 ),
(18, 'Voyage au centre de la Terre', 'Verne', 1864, 8);
3. Suppression des données⚓︎
DELETE FROM
DELETE FROM
👉 DELETE FROM est utilisée pour supprimer les enregistrements existants dans une table.
Exécuter :
À vous de jouer 8
Ecrire la requête qui permet de visualiser les données et vérifier que les suppressions ont bien été effectuées.
Solution
SELECT *
FROM livre;
À vous de jouer 9
Nous allons remettre ces données dans la table. A vous de le faire !
Solution
INSERT INTO livre
(id, titre, auteur, ann_publi, note)
VALUES
(3, 'Fondation', 'Asimov', 1951, 9),
(10, 'Les Robots', 'Asimov', 1950, 9),
(15, 'La Fin de l’éternité', 'Asimov', 1955, 8);
4. Mise à jour des données⚓︎
UPDATE
UPDATE
👉 Vous pouvez utiliser la requête UPDATE avec une clause WHERE pour mettre à jour la ligne sélectionnée, sinon, toutes les lignes seront mises à jour.
Tester
Solution
👉 Permet de remplacer Barjavel par R. Barjavel dans tous les enregistrements.
5. Rechercher des données⚓︎
SELECT
SELECT
Exécuter :
À vous de jouer 10
Écrivez et testez une requête permettant d'obtenir uniquement les titres des livres.
Solution
SELECT titre
FROM livre;
6. Rechercher avec un critère de sélection⚓︎
WHERE
Pour imposer une (ou des) condition(s) permettant de sélectionner uniquement certains attributs ou valeurs
👉 utiliser la clause WHERE
Exemple 1
Pour rechercher tous les livres d'Isaac Asimov :
Exemple 2
Cette commande sélectionne tous les t-uplet ou auteur se termine par Asimov (par exemple Isaac Asimov ou I. Asimov).
À vous de jouer 11
Écrivez et testez une requête permettant d'obtenir uniquement les titres des livres de K.Dick.
Solution
SELECT titre
FROM livre
WHERE auteur = 'K.Dick'
AND, OR
👉 En compléments de WHERE il est possible de préciser les conditions avec OR ou AND.
Tester
Exemple : Pour rechercher tous les livres d'Isaac Asimov écrit avant 1954
À vous de jouer 12
D'après vous (sans taper la requête !) que donne la requête suivante :
SELECT titre, ann_publi
FROM livre
WHERE auteur = 'Asimov' AND note >= 9;
Solution
Cette requête affiche le titre et l'année de publication de tous les livres de Asimov ayant une note supérieure ou égale à 9.
À vous de jouer 13
Écrivez et testez une requête permettant d'obtenir uniquement les livres de K.Dick. et d'Asimov publié après 1950 et dont la note est supérieure ou égale à 8.
Votre requête doit renvoyer l'auteur, le titre du livre et l'année de publication.
Solution
SELECT auteur, titre, ann_publi
FROM livre
WHERE (auteur = 'Asimov' OR auteur = 'K.Dick') AND (ann_publi > 1950 AND note >= 8);
Attention
Attention, il y a des régles de priorités pour les connecteurs logiques (OR, AND etc). Un connecteur logique doit se trouver entre deux expressions booléennes. Dans le cas d'enchaînements de plus de deux connecteurs, des règles de priorités interviennent. Il est extrêmement recommandé d'utiliser des parenthèses, pour clairement voir la structure.
Dans l'exemple précédant, une absence de parenthèses conduit à l'affichage du livre "Les Robots" (si vous l'avez bien remis dans la table). Or l'année de publication de ce roman est égale à 1950. Il ne doit pas apparaître dans une requète où l'on cherche ann_publi > 1950
7. Recherche avec un résultat classé selon un ordre précis⚓︎
ORDER BY
Il est aussi possible de rajouter la clause ORDER BY après la condition WHERE afin d'obtenir les résultats classés selon un ordre précis.
La clause ORDER BY attributs peut être suivie des clause ASC ou DESC pour un classement selon l'ordre croissant ou décroissant.
Par défaut, sans précision, le classement est par ordre croissant.
À vous de jouer 14
Écrivez et testez une requête permettant d'obtenir tous les livres écrits depuis 1960 classés par date de publication décroissante.
Votre requête doit renvoyer l'auteur, le titre du livre et l'année de publication.
Solution
SELECT auteur,titre,ann_publi
FROM livre
WHERE ann_publi >= 1960
ORDER BY ann_publi DESC;
Avec du texte
👉 Si la clause ORDER BY porte sur un attribut de type TEXT, Le classement est fait ordre alphabétique ou alphabétique inverse.
Exécuter :
8. Recherche en évitant des doublons⚓︎
DISTINCT
On peut éviter des doublons grâce à la clause DISTINCT
Exécuter :
9. Valeurs SQL NULL⚓︎
NULL
Un champ (un attribut) avec une valeur NULL est un champ sans valeur.
Si un champ d'une table est facultatif, il est possible d'insérer un nouvel enregistrement ou de mettre à jour un enregistrement sans ajouter de valeur à ce champ. Ensuite, le champ sera enregistré avec une valeur NULL.
Remarque
👉 Remarque : Une valeur NULL est différente d'une valeur zéro ou d'un champ contenant des espaces.
🌵 Un champ avec une valeur NULL est un champ qui a été laissé vide lors de la création de l'enregistrement.
Comment tester les valeurs NULL ?
Nous devrons utiliser les opérateurs IS NULLet IS NOT NULL.
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
ou :
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
Exemple
Ajoutons un titre dont l'auteur, l'année et la note nous sont inconnus :
À vous de jouer 15
Extraire les livres où l'auteur est non renseigné.
Votre requête doit renvoyer l'auteur, le titre du livre et l'année de publication.
Solution
SELECT auteur, titre, ann_publi
FROM livre
WHERE auteur IS NULL;
Bilan⚓︎
CREATE TABLE
CREATE TABLE LIVRES
(id INT PRIMARY KEY, titre TEXT, auteur TEXT, ann_publi INT, note INT);
INSERT INTO
INSERT INTO LIVRES
(id, titre, auteur, ann_publi, note)
VALUES
(1, '1984', 'Orwell', 1949, 10);
UPDATE... SET... WHERE...
UPDATE LIVRES
SET auteur = 'R. Barjavel'
WHERE auteur = 'Barjavel';
DELETE FROM ... WHERE...
DELETE FROM LIVRES
WHERE auteur = 'Asimov';
SELECT ... FROM ... WHERE...
SELECT titre, ann_publi
FROM LIVRES
WHERE auteur = 'Asimov';
SELECT titre, ann_publi
FROM LIVRES
WHERE 'Asimov' and ann_publi < 1954
WHERE auteur LIKE '%Asimov';
ORDER BY
SELECT auteur, titre, ann_publi
FROM LIVRES
WHERE ann_publi >= 1060
ORDER BY ann_publi DESC;
DISTINCT
SELECT DISTINCT auteur
FROM LIVRES;
SELECT ... FROM ... JOIN ON ...
SELECT *
FROM LIVRES
JOIN AUTEURS ON LIVRES.id_auteur = AUTEURS.id;
SELECT ... FROM ... AS ... JOIN ON... AS ...
SELECT *
FROM LIVRES AS l
JOIN AUTEURS AS a ON l.id_auteur = a.id ;
Les agrégats⚓︎
COUNT(...)
SELECT COUNT(*)
FROM LIVRES
WHERE auteur LIKE "%Asimov";
SUM(...)
SELECT SUM(note)
FROM LIVRES
WHERE auteur LIKE "%Asimov";
AVG(...)
SELECT AVG(note)
FROM LIVRES
WHERE auteur LIKE "%Asimov";
MIN(...) et MAX(...)
Les syntaxes pour MIN(...) et MAX(...) sont analogues à celles pour SUM et AVG
Crédits⚓︎
Jean-Louis Thirot et Mireille Coilhac