La figure ci-dessous rappelle les notations des différentes relations entre les données pour diagramme ERD :
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 :
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.
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 ...
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;
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;
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;
Préparer bien vos requêtes dans vos sites web.