BRE03

PHP Jour 4 : le SQL

Les contenus du cours BRE03 Web Dev Course © 2024 par Mari Doucet sont sous licence CC BY-NC-SA 4.0

La représentation des données

Tableau basique

Titre	Année	Réalisateur
Alien, le 8ème passager	1979	Ridley Scott
Aliens, le retour	1986	James Cameron
Aliens 3	1992	David Fincher
Alien, la résurrection	1997	Jean-Pierre Jeunet
Titanic	1997	James Cameron
Gladiator	2000	Ridley Scott

Un tableau simple, chaque ligne représente un film, chaque colonne une caractéristique du film.

Tableau avec un identifiant unique

ID	Titre	Année	Réalisateur
1	Alien, le 8ème passager	1979	Ridley Scott
2	Aliens, le retour	1986	James Cameron
3	Aliens 3	1992	David Fincher
4	Aliens, la résurrection	1997	Jean-Pierre Jeunet
5	Titanic	1997	James Cameron
6	Gladiator	2000	Ridley Scott

On ajoute un identifiant unique à chaque film et sa valeur augmente pour chacun des films.

Ajoutons-en un :

ID	Titre	Année	Réalisateur
1	Alien, le 8ème passager	1979	Ridley Scott
2	Aliens, le retour	1986	James Cameron
3	Aliens 3	1992	David Fincher
4	Aliens, la résurrection	1997	Jean-Pierre Jeunet
5	Titanic	1997	James Cameron
6	Gladiator	2000	Ridley Scott
7	Le fabuleux destin d’Amélie Poulain	2001	Jean-Pierre Jeunet

Base de données

Si je veux préciser des choses sur les réalisateurs, plutôt que de me répéter dans le tableau des films, je vais créer un autre tableau, qui lui concerne les réalisateurs. Il aura aussi son propre identifiant unique qui ne concerne que ce tableau.

Films

ID	Titre	Année	Réalisateur
1	Alien, le 8ème passager	1979	Ridley Scott
2	Aliens, le retour	1986	James Cameron
3	Aliens 3	1992	David Fincher
4	Aliens, la résurrection	1997	Jean-Pierre Jeunet
5	Titanic	1997	James Cameron
6	Gladiator	2000	Ridley Scott
7	Le fabuleux destin d’Amélie Poulain	2001	Jean-Pierre Jeunet

Réalisateurs

ID	Nom	Pays
1	Ridley Scott	USA
2	James Cameron	Canada
3	David Fincher	USA
4	Jean-Pierre Jeunet	France

Base de données relationnelle

Maintenant que nous avons deux tableaux avec des identifiants uniques, nous allons pouvoir utiliser ces identifiants pour créer des relations entre nos tableaux :

Films

ID	Titre	Année	Réalisateur
1	Alien, le 8ème passager	1979	1
2	Aliens, le retour	1986	2
3	Aliens 3	1992	3
4	Aliens, la résurrection	1997	4
5	Titanic	1997	2
6	Gladiator	2000	1
7	Le fabuleux destin d’Amélie Poulain	2001	4

Réalisateurs

ID	Nom	Pays
1	Ridley Scott	USA
2	James Cameron	Canada
3	David Fincher	USA
4	Jean-Pierre Jeunet	France

Une base de données c’est donc ça : une série de tableaux, qu’on appelle tables, et les relations qui les lient.

SQL et MySQL

Il existe de nombreux moyens de gérer des bases de données, nous allons utiliser MySQL.

MySQL

MySQL est un SGBD ( Système de Gestion de Base de Données), plus exactement même c’est un SGBDR (Système de Gestion de Bases de Données Relationnelles).

Pourquoi on utilise celui-ci ? C’est le plus connu et le plus courant, tout bêtement.

SQL

Le SQL (Structured Query Language) est un language qui nous permet de discuter avec MySQL.

CRUD

Nous allons utiliser MySQL pour faire 4 grands types d’actions :

Create

Pour créer des entrées dans les tables.

Read

Pour lire des entrées dans les tables

Update

Pour modifier des entrées dans les tables

Delete

Pour supprimer des entrées dans les tables

SELECT : lire des données

Nos tables d’exemple

movies

id	title	year	director
1	Alien, le 8ème passager	1979	1
2	Aliens, le retour	1986	2
3	Aliens 3	1992	3
4	Aliens, la résurrection	1997	4
5	Titanic	1997	2
6	Gladiator	2000	1
7	Le fabuleux destin d’Amélie Poulain	2001	4

directors

id	name	country
1	Ridley Scott	USA
2	James Cameron	Canada
3	David Fincher	USA
4	Jean-Pierre Jeunet	France

SELECT

Sélectionner une colonne

SELECT nom_de_la_colonne FROM nom_de_la_table

C’est la version la plus simple possible d’un SELECT.

SELECT title FROM movies

Nous enverra chacun des titres de la table movies.

Sélectionner plusieurs champs

SELECT nom_de_la_colonne_1, nom_de_la_colonne2 FROM nom_de_la_table

Il suffit de lister les colonnes, séparées par une virgule.

Sélectionner toutes les colonnes

SELECT * FROM nom_de_la_table

On utilise le raccourci *.

WHERE

Si on veut sélectionner des colonnes sous certaines conditions, il faut utiliser une clause WHERE dans notre SELECT.

SELECT * FROM movies WHERE director = 2

Nous renverra toutes les colonnes des films du réalisateur 2.

SELECT * FROM movies WHERE year > 1999

Nous renverra toutes les colonnes des films après 1999 (Gladiator).

Si vous avez besoin de conditions multiples, vous pouvez utiliser AND et OR.

SELECT * FROM movies WHERE year > 1999 AND director = 4

nous renverra Le fabuleux destin d’Amélie Poulain.

ORDER BY

Par défaut les entrées seront retournées du plus petit id au plus grand mais nous pouvons modifier ce comportement avec ORDER BY.

Soit en changeant la colonne qui sert d’ordre :

SELECT * FROM movies ORDER BY year

soit en modifiant l’ordre avec ASC (du plus petit au plus grand) ou DESC (du plus grand au plus petit) :

SELECT * FROM movies ORDER BY year DESC

LIMIT

En cas de besoin nous pouvons limiter le nombre de résultats retournés (par exemple pour économiser les ressources ou mettre en place une pagination) :

SELECT * FROM movies LIMIT 3

retournera :

id	title	year	director
1	Alien, le 8ème passager	1979	1
2	Aliens, le retour	1986	2
3	Aliens 3	1992	3

Et toutes ces commandes peuvent être mélangées :

SELECT * FROM movies WHERE year > 1990 ORDER BY year DESC LIMIT 2

id	title	year	director
7	Le fabuleux destin d’Amélie Poulain	2001	4
6	Gladiator	2000	1

Exercices SELECT

Consignes des exercices

INSERT : Ajouter des données

Notre table d’exemple

directors

id	name	country
1	Ridley Scott	USA
2	James Cameron	Canada
3	David Fincher	USA
4	Jean-Pierre Jeunet	France

Si je veux ajouter une entrée à la table directors, je dois utiliser INSERT

INSERT INTO directors (name, country)
VALUES ("Céline Sciamma", "France")

id	name	country
1	Ridley Scott	USA
2	James Cameron	Canada
3	David Fincher	USA
4	Jean-Pierre Jeunet	France
5	Céline Sciamma	France

Et si je veux ajouter plusieurs entrées :

INSERT INTO directors (name, country)
VALUES 
("Lana Wachowski", "USA"),
("Lilly Wachowsky", "USA")

id	name	country
1	Ridley Scott	USA
2	James Cameron	Canada
3	David Fincher	USA
4	Jean-Pierre Jeunet	France
5	Céline Sciamma	France
6	Lana Wachowski	USA
7	Lilly Wachowsky	USA

Exercices INSERT

Consignes des exercices

UPDATE : Modifier les données

Notre table d’exemple

directors

id	name	country
1	Ridley Scott	USA
2	James Cameron	Canada
3	David Fincher	USA
4	Jean-Pierre Jeunet	France
5	Céline Sciamma	France
6	Lana Wachowski	USA
7	Lilly Wachowsky	USA

Une erreur s’est glissée dans la table. Lilly Wachowski qui est la soeur de Lana prend un i et pas un y à la fin de son nom de famille.

Nous allons corriger ceci avec un UPDATE :

UPDATE directors
SET name = "Lilly Wachowski"
WHERE directors.id = 7

id	name	country
1	Ridley Scott	USA
2	James Cameron	Canada
3	David Fincher	USA
4	Jean-Pierre Jeunet	France
5	Céline Sciamma	France
6	Lana Wachowski	USA
7	Lilly Wachowski	USA

Si vous voulez faire le même update sur plusieurs champs, c’est votre clause WHERE qui va devoir être plus générique. Attention ce pendant, plus elle est générale, plus vous risquez de faire des bêtises.

UPDATE directors
SET country = "États-Unis"
WHERE directors.country = "USA"

id	name	country
1	Ridley Scott	États-Unis
2	James Cameron	Canada
3	David Fincher	États-Unis
4	Jean-Pierre Jeunet	France
5	Céline Sciamma	France
6	Lana Wachowski	États-Unis
7	Lilly Wachowski	États-Unis

Exercice UPDATE

Consignes de l’exercice

DELETE Supprimer des données

Notre table d’exemple

movies

id	title	year	director
1	Alien, le 8ème passager	1979	1
2	Aliens, le retour	1986	2
3	Aliens 3	1992	3
4	Aliens, la résurrection	1997	4
5	Titanic	1997	2
6	Gladiator	2000	1
7	Le fabuleux destin d’Amélie Poulain	2001	4

Si nous voulons supprimer une entrée, nous allons utiliser DELETE :

DELETE FROM movies
WHERE movies.id = 3

id	title	year	director
1	Alien, le 8ème passager	1979	1
2	Aliens, le retour	1986	2
4	Aliens, la résurrection	1997	4
5	Titanic	1997	2
6	Gladiator	2000	1
7	Le fabuleux destin d’Amélie Poulain	2001	4

Encore une fois il faudra se méfier de la clause WHERE si elle est trop générale, vous allez effacer plus d’entrées que ce que vous vouliez, et il n’y a pas de Ctrl+Z sur une base de données.

Exercice DELETE

Consignes de l’exercice

JOIN Utiliser les relations

Nos tables d’exemple

movies

id	title	year	director
1	Alien, le 8ème passager	1979	1
2	Aliens, le retour	1986	2
3	Aliens 3	1992	3
4	Aliens, la résurrection	1997	4
5	Titanic	1997	2
6	Gladiator	2000	1
7	Le fabuleux destin d’Amélie Poulain	2001	4

directors

id	name	country
1	Ridley Scott	USA
2	James Cameron	Canada
3	David Fincher	USA
4	Jean-Pierre Jeunet	France

Nous avons donc deux tables dont la relation est établie entre les directors et les movies.

En effet, dans la table movies, la colonne director contient l’id du director correspondant.

Dans nos requêtes pour utiliser cette relation, nous allons joindre les tables en utilisant JOIN.

SELECT movies.title, directors.name 
FROM movies JOIN directors
ON movies.director = directors.id

title	name
Alien, le 8ème passager	Ridley Scott
Aliens, le retour	James Cameron
Aliens 3	David Fincher
Aliens, la résurrection	Jean-Pierre Jeunet
Titanic	James Cameron
Gladiator	Ridley Scott
Le fabuleux destin d’Amélie Poulain	Jean-Pierre Jeunet

Vous pouvez utiliser ce que vous avec appris pour les SELECT avec des jointures. Par exemple, une clause WHERE :

SELECT movies.title, directors.name 
FROM movies JOIN directors
ON movies.director = directors.id
WHERE directors.id = 4

title	name
Aliens, la résurrection	Jean-Pierre Jeunet
Le fabuleux destin d’Amélie Poulain	Jean-Pierre Jeunet

Exercices JOIN

Consignes des exercices

Mini-Projet : 3WATrade

Consignes du 3WATrade