Requête

Objectifs

Comment manipuler les données ?

Cours

Requête

Base de données

Structured Query Language (SQL)

• Prononcé SQL /ɛs kjuː ɛl/ ou sequel /ˈsiːkwəl/.

• Langage pour manipuler des DB relationnelles depuis 1974.

• Norme ISO depuis 1987 pour DBMS relationnels.

• Proche d'un langage naturel pour faciliter l'apprentissage.

• Paradigmes : déclaratif (≠ impératif), orienté objet, procédural.

https://commons.wikimedia.org/wiki/File:SQL_ANATOMY_wiki.svg

ISO 8601

https://xkcd.com/1179/

Standards

https://xkcd.arnaud.at/927

Tables SQL

https://www.blog.datawithbaraa.com/p/what-is-sql

Commandes SQL

https://www.blog.datawithbaraa.com/p/what-is-sql

Commandes SQL

• DDL (Data Definition Language)

  • CREATE, ALTER, DROP, TRUNCATE

• DML (Data Manipulation Language)

  • INSERT, UPDATE, DELETE

• DQL (Data Query Language)

  • SELECT

• DCL (Data Control Language)

  • GRANT, REVOKE

• TCL (Transaction Control Language)

  • COMMIT, ROLLBACK, SAVEPOINT

DDL (Data Definition Language)

Définition de la structure des données (tables, vues, index, …).

CREATE TABLE table1 (
  colonne1 type1,
  colonne2 type2
);
ALTER TABLE table1 ADD colonne type;
DROP TABLE table1;
TRUNCATE TABLE table1;

• CREATE : créer une table (et ses colonnes).

• ALTER : modifier une table (ajouter, modifier ou supprimer une colonne).

• DROP : supprimer une table (et ses données).

• TRUNCATE : vider une table (sans la supprimer).

DML (Data Manipulation Language)

Manipulation des données dans les tables.

INSERT INTO table1 (colonne1, colonne2) VALUES (value1, value2);
UPDATE table1 SET colonne = value WHERE condition1;
DELETE FROM table1 WHERE condition1;

• INSERT : ajouter des lignes dans une table.

• UPDATE : modifier des lignes existantes.

• DELETE : supprimer des lignes existantes.

DQL (Data Query Language)

Interrogation des données dans les tables.

SELECT colonne1, colonne2 FROM table1 WHERE condition1;
SELECT * FROM table1;

SELECT * FROM table1 JOIN table2 ON table1.colonne = table2.colonne;
SELECT * FROM table1 JOIN table2 ON table1.colonne = table2.colonne WHERE condition1;

• SELECT : sélectionner des colonnes dans une table.

  • * : toutes les colonnes.

  • WHERE : filtrer les lignes sélectionnées.

  • JOIN : joindre plusieurs tables pour une requête (clés primaire/étrangère).

• Résultat sous forme de table.

DCL (Data Control Language)

Gestion des droits d'accès aux données.

GRANT SELECT ON table1 TO user1;
REVOKE SELECT ON table1 FROM user1;

• GRANT : accorder des droits d'accès.

• REVOKE : retirer des droits accordés.

TCL (Transaction Control Language)

Gestion des transactions pour garantir l'intégrité des données.

BEGIN;
UPDATE table1 SET colonne = value WHERE condition1;
SAVEPOINT savepoint1;
UPDATE table1 SET colonne = value WHERE condition2;
ROLLBACK TO savepoint1; -- annule la deuxième mise à jour
COMMIT; -- valide la première mise à jour

• COMMIT : valider une transaction.

• ROLLBACK : annuler une transaction.

• SAVEPOINT : définir un point de sauvegarde dans une transaction.

Conditions

SELECT * FROM table1 WHERE colonne = value;
SELECT * FROM table1 WHERE colonne > value;
SELECT * FROM table1 WHERE colonne <= value;
SELECT * FROM table1 WHERE colonne <> value; -- ou !=
SELECT * FROM table1 WHERE colonne IS NULL;
SELECT * FROM table1 WHERE colonne IS NOT NULL;
SELECT * FROM table1 WHERE colonne BETWEEN value1 AND value2;
SELECT * FROM table1 WHERE colonne IN (value1, value2);
SELECT * FROM table1 WHERE colonne LIKE 'value%';
SELECT * FROM table1 WHERE condition1 AND condition2;
SELECT * FROM table1 WHERE condition1 OR condition2;
SELECT * FROM table1 WHERE NOT condition1;

Démonstration

Fonctions

Pour appliquer des opérations sur les données dans les requêtes :

SELECT COUNT(colonne) FROM table1;
SELECT SUM(colonne) FROM table1;
SELECT AVG(colonne) FROM table1;
SELECT MIN(colonne) FROM table1;
SELECT MAX(colonne) FROM table1;
SELECT DISTINCT colonne FROM table1;
SELECT colonne1, colonne2 FROM table1 ORDER BY colonne1 ASC, colonne2 DESC;
SELECT colonne1, colonne2 FROM table1 LIMIT 10 OFFSET 5;

Démonstration

Jointure

https://www.programiz.com/sql/join

Query

https://xkcd.com/1409/

F pour passer en plein écran ou O pour afficher la vue d'ensemble.
Versions sans animation, plein écran, imprimable.

Exercices

Beekeeper Studio

• Ouvrir Beekeeper Studio et se connecter à la base de données Demo Database déjà présente dans l'application.
  • La barre latérale gauche permet de naviguer entre les différentes bases de données.
  • Sélectionner la connexion Demo Database. Si elle n'est pas présente, la télécharger.
  • Se connecter à la base de données.
• Explorer les tables disponibles.
  • Double-cliquer sur une table pour afficher son contenu.
  • Combien de types de fromages sont présents dans la table cheeses ?
  • De quel pays provient le fromage Gouda ?
  • Quels fromages ont les meilleurs avis ?
• Exécuter la requête Demo Query.
  • Aller dans Saved Queries > Demo Query.
  • Cliquer sur Run All pour exécuter la requête.
  • Observer le résultat.
  • Que fait la requête ?

Personne

• Se déconnecter de la base de données.
  • En bas à gauche, cliquer sur le nom de la connexion > Disconnect.
• Créer une nouvelle base de données person.
  • Cliquer sur New Connection > SQLite > Create New Database.
  • Nommer la base de données person.db.
  • Cliquer sur Connect.
• Créer une nouvelle table person avec les colonnes suivantes :
  • id : INTEGER PRIMARY KEY AUTOINCREMENT (clé primaire entier auto-incrémentée)
  • name : VARCHAR(50) (chaîne de caractères de longueur variable de 50 caractères maximum)
  • email : VARCHAR(100)
  • age : SMALLINT (entier signé sur 2 octets)
  • Exécuter la commande suivante :

    • CREATE TABLE person (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name VARCHAR(50) NOT NULL,
        email VARCHAR(100),
        age SMALLINT NOT NULL
      );

    • Cliquer sur Run All pour exécuter la commande.
    • Cliquer une seconde fois devrait indiquer une erreur car la table existe déjà.
  • Voir la table créée.
• Insérer des données dans la table person.

  • INSERT INTO person (name, email, age)
    VALUES
      ('Alice', 'alice@example.com', 28),
      ('Bob', 'bob@example.com', 20),
      ('Charlie', NULL, 25),
      ('Dave', NULL, 30);

  • Voir les données dans la table person en double-cliquant sur elle.
• Sélectionner toutes les données de la table person.

Solution

SELECT * FROM person;

• Sélectionner uniquement les noms et les âges des personnes.

Solution

SELECT name, age FROM person;

• Sélectionner dans l'ordre décroissant des âges.

Solution

SELECT * FROM person ORDER BY age DESC;

• Sélectionner uniquement les personnes de plus de 25 ans.

Solution

SELECT * FROM person
WHERE age > 25;

• Sélectionner uniquement les noms des personnes de plus de 25 ans.

Solution

SELECT name FROM person
WHERE age > 25;

• Calculer la moyenne d'âge des personnes.

Solution

SELECT AVG(age) FROM person;

Avis de films

Reprendre ta table person et l'étendre pour gérer des avis de films.

• Créer une nouvelle table movie avec les colonnes suivantes :
  • id : clé primaire auto-incrémentée en entier.
  • title : chaine de caractères de longueur variable de 100 caractères maximum.
  • year : SMALLINT (petit entier signé sur 2 octets).

Solution

CREATE TABLE movie (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title VARCHAR(100) NOT NULL,
  year SMALLINT NOT NULL
);

• Insérer des données dans la table movie.

  • INSERT INTO movie (title, year)
    VALUES
      ('Memento', 2000),
      ('The Prestige', 2006),
      ('The Dark Knight', 2008),
      ('Inception', 2010),
      ('Interstellar', 2014),
      ('Dunkirk', 2017),
      ('Tenet', 2020),
      ('Oppenheimer', 2023);

• Créer une nouvelle table review avec les colonnes suivantes :
  • id : clé primaire auto-incrémentée en entier.
  • rating : SMALLINT (note de 1 à 6)
  • comment : TEXT (chaîne de caractères sans limite de taille)
  • person_id : INTEGER (clé étrangère vers la table person)
  • movie_id : INTEGER (clé étrangère vers la table movie)

  • CREATE TABLE review (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      rating SMALLINT NOT NULL,
      comment TEXT,
      person_id INTEGER NOT NULL,
      movie_id INTEGER NOT NULL,
      FOREIGN KEY (person_id) REFERENCES person (id),
      FOREIGN KEY (movie_id) REFERENCES movie (id)
    );

• Insérer les données dans la table review avec les avis suivants :
  • Alice a noté Memento 5/6.
  • Alice a noté Dinkirk 3/6 avec le commentaire Pas mal.
  • Bob a noté Memento 1/6 avec le commentaire Je n'ai rien compris (il faudra échapper l'apostrophe).
  • Dave a noté Tenet 4/6.
  • Charlie a noté Memento 6/6 avec le commentaire Chef-d'œuvre.
  • Bob a noté Dinkirk 2/6 avec le commentaire Bof.
  • Charlie a noté Oppenheimer 4/6 avec le commentaire Sympa.

Solution

INSERT INTO review (rating, comment, person_id, movie_id)
VALUES
  (5, NULL, 1, 1),
  (3, 'Pas mal', 1, 6),
  (1, 'Je n''ai rien compris', 2, 1),
  (4, NULL, 4, 7),
  (6, 'Chef-d''œuvre', 3, 1),
  (2, 'Bof', 2, 6),
  (4, 'Sympa', 3, 8);

• Sélectionner les avis de tout le monde sur tous les films.

Solution

SELECT * FROM review
JOIN movie ON review.movie_id = movie.id
JOIN person ON review.person_id = person.id;

• Ne garder que les titres, les notes et les commentaires.

Solution

SELECT title, rating, comment FROM review
JOIN movie ON review.movie_id = movie.id
JOIN person ON review.person_id = person.id;

• Ne garder que les avis de Bob.

Solution

SELECT title, rating, comment FROM review
JOIN movie ON review.movie_id = movie.id
JOIN person ON review.person_id = person.id
WHERE person.name = 'Bob';

• Ne garder que les avis sur Memento avec le nom de la personne au lieu du titre du film.

Solution

SELECT name, rating, comment FROM review
JOIN movie ON review.movie_id = movie.id
JOIN person ON review.person_id = person.id
WHERE movie.title = 'Memento';

• Ne garder que les avis de plus de 3/6 sur Memento.

Solution

SELECT name, rating, comment FROM review
JOIN movie ON review.movie_id = movie.id
JOIN person ON review.person_id = person.id
WHERE movie.title = 'Memento' AND rating > 3;

• Calculer la moyenne des notes de Memento.

Solution

SELECT AVG(rating) FROM review
JOIN movie ON review.movie_id = movie.id
WHERE movie.title = 'Memento';

Modélisation d'une base de données

Individuellement ou par groupe de deux, continuer la modélisation d'une base de données.

• Reprendre le modèle ER de l'exercice de la modélisation.
• Écrire les commandes SQL pour créer les tables correspondantes.
• Mettre en place les relations entre les tables (clés étrangères).
• Insérer quelques données d'exemple dans les tables.
• Écrire des requêtes SQL pour interroger la base de données.
• Indiquer les difficultés rencontrées et les solutions trouvées.

Autre cours

• https://janm.t-doc.org/DOI2/Databases/index.html
• https://sql.sh/cours/select

Jeux

• SQL Murder Mystery

Références

• https://www.guru99.com/fr/sql-commands-dbms-query.html
• https://learnsql.fr/blog/que-sont-ddl-dml-dql-et-dcl-en-sql/
• https://sql.sh/
• https://vladmihalcea.com/tutorials/sql/