BDD exercices

Lire un diagramme ERD

La figure ci-dessous rappelle les notations des différentes relations entre les données pour diagramme ERD :

Notation des relations dans un diagramme ERD (entity relation diagram)

Un exemple complet

Dans ces exercices nous allons utiliser la base de données fournie par mysqltutorial.org.

Elle est fournie avec son diagramme ERD indiquant ce qui la compose. Prenez le temps d'importer la base dans votre PHPMyAdmin et d'analyser ce diagramme.

Continuez le tutoriel sur mlysqltutorial.org au-delà de ce qui est traduit et proposé dans cette page.

Le site est très complet et présente également les notions plus avancées des bases de données comme :

  • les procédures stockées qui sont des fonctions pour les bases de données et rassemblent les requêtes nécessaires pour faire une opération complète sur la base
  • Les triggers permettent de déclencher des actions et tâches planifiées, de vérifier l’intégrité des données
  • Les views (vue) : ce sont des alias entre des requêtes qui deviennent accessibles comme des tables

Trouvez un mémo qui vous conviennent

On peut trouver sur internet des dizaines de mémo en ligne ou imprimable sur tous les domaines de l’informatique. Le mot magique pour en trouver un est “cheat sheet”, ajoutez pdf si vous souhaitez une version imprimable. Cherchez-en un qui vous convienne, par exemple celui de Mémo mysql sur devhints.io.

Rechercher / afficher des informations

Rappel des syntaxes de la commande SELECT

SELECT * FROM table1, table2;
SELECT field1, field2 FROM table1, table2;
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUP BY field;
SELECT ... FROM ... WHERE condition GROUP BY field HAVING condition2;
SELECT ... FROM ... WHERE condition ORDER BY field1, field2;
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC;
SELECT ... FROM ... WHERE condition LIMIT 10;
SELECT DISTINCT field1 FROM ...
SELECT DISTINCT field1, field2 FROM ...

Requêtes simples

Requêtes sur une table

Tests sur la table employees. Voici quelques requêtes à tester dans phpMyAdmin :

SELECT lastname, firstname, jobtitle FROM employees;
SELECT lastname, firstname, jobtitle FROM employees ORDER BY lastname DESC;
SELECT lastname, firstname, jobtitle FROM employees ORDER BY lastname DESC, firstname DESC;
SELECT lastname, firstname, jobtitle FROM employees GROUP BY jobtitle;
SELECT lastname, firstname, jobtitle FROM employees WHERE jobtitle = 'Sales Rep';
SELECT lastname, firstname, jobtitle FROM employees WHERE jobtitle LIKE 'Sales%';
SELECT lastname, firstname, jobtitle FROM employees WHERE lastname LIKE 'K%';

Quelle est la différence entre ces 2 requêtes :

SELECT DISTINCT state, city FROM customers WHERE state IS NOT NULL ORDER BY state, city;
Requêtes sur plusieurs tables

Pour regrouper des infos de 2 tables (ou plus), la méthode habituelle est d’utiliser un attribut commun entre les 2 tables pour lier les résultats. Pour cela une opération de jointure est nécessaire c’est le plus souvent INNER JOIN.

Les autres jointures permettent d’explorer toutes les combinaisons des lignes de plusieurs tables, ce qui présentent un intérêt restreint quand les données sont justement liées par des clefs d’association.

Avec MySQL, il existe 2 principales syntaxes pour réaliser les jointures internes : avec la clause INNER JOIN ou avec la clause WHERE

SELECT productCode, productName, textDescription FROM products t1 INNER JOIN productlines t2 ON t1.productline = t2.productline;

Si la clef porte le même nom entre les deux tables on peut utiliser la syntaxe USING :

SELECT productCode, productName, textDescription FROM products INNER JOIN productlines USING (productline);

La syntaxe équivalente avec la clause WHERE serait :

SELECT productCode, productName, textDescription FROM products, productlines
WHERE products.productline = productlines.productLine;

L’exemple ci-dessous montre comment afficher les managers des employés. Pour cela, il faut faire une jointure interne sur la même table. On utilise des alias pour plus de clarté dans l’écriture de la requête :

SELECT employees.lastname, employees.firstname, managers.lastname as 'nom manager', managers.firstname as 'prenom manager' FROM employees, employees as managers WHERE employees.reportsTo = managers.employeeNumber;

On a souvent intérêt à concaténer / renommer les colonnes pour rendre plus lisible le résultat et l’exploiter plus facilement dans la page Web.

SELECT CONCAT (employees.lastname, " ", employees.firstname) as "Employé", CONCAT (managers.lastname, " ", managers.firstname) as "manager" FROM employees, employees as managers WHERE employees.reportsTo = managers.employeeNumber;

Même requête avec la syntaxe INNER JOIN

SELECT CONCAT (employees.lastname, " ", employees.firstname) as "Employé", CONCAT (managers.lastname, " ", managers.firstname) as "manager" FROM employees
INNER JOIN employees as managers  ON employees.reportsTo = managers.employeeNumber;

Sélection d’informations sur 3 tables

SELECT orderNumber, orderDate, orderLineNumber,
       productName, quantityOrdered, priceEach
FROM orders
INNER JOIN orderdetails USING (orderNumber)
INNER JOIN products USING (productCode)
ORDER BY orderNumber, orderLineNumber;
Effectuer des calculs

Afficher le nom des managers et le nombre de personnes qu’ils managent :

SELECT CONCAT (managers.lastname, " ", managers.firstname) as "manager", COUNT(employees.lastname)  FROM employees
INNER JOIN employees as managers  ON employees.reportsTo = managers.employeeNumber
GROUP BY manager;

Afficher le montant et le status des factures

SELECT orderNumber, status, SUM(quantityOrdered * priceEach) as total
FROM orders
INNER JOIN orderdetails USING (orderNumber)
GROUP BY orderNumber;

Quelques conseils

Préparer bien vos requêtes dans vos sites web.