4-02. Introduction au langage SQL

• Identifier les composants d’une requête.

Au départ, une base de données est vide. Il faut commencer par la peupler avec des tables (ou relations) – qui seront elles-mêmes vides au départ.

On va reprendre le même thème que l’activité du chapitre précédent. Ça m’évitera de vous présenter une nouvelle situation ! 🤓 Notre base de données servira à gérer une ensemble de livres destinés à être emprunter.

Table des auteurs

			CREATE TABLE authors (
				id INT AUTO_INCREMENT PRIMARY KEY,
				lastname VARCHAR(100) NOT NULL, -- string max 100 caractères, valeur obligatoire
				firstname VARCHAR(100), -- string max 100 caractères, valeur non obligatoire
				country VARCHAR(50) NOT NULL
			) ENGINE=InnoDB;
		

Table des livres

Allez hop ! Voici le code :

		CREATE TABLE books (
			id INT AUTO_INCREMENT PRIMARY KEY,
			title VARCHAR(255) NOT NULL,
			date INT CHECK (date IS NULL OR ( date >= 1453 AND  date <= 2050 )),
			id_author INT NOT NULL,
			FOREIGN KEY (id_author) REFERENCES authors(id)
		) ENGINE=InnoDB;
	

Deux nouveautés dans cette table : d’une part la fonction CHECK qui va vérifier la cohérence de la date (l’absence de date est autorisée), et d’autre part la déclaration d’une clé étrangère (FOREIGN KEY).

Table des utilisateurs

		CREATE TABLE users (
			id INT AUTO_INCREMENT PRIMARY KEY,
			name VARCHAR(100) NOT NULL,
			class VARCHAR(50),
			card VARCHAR(20) UNIQUE NOT NULL
		) ENGINE=InnoDB;
	

Ici, encore une (petite nouveauté) : on impose que l’attribut card soit unique, c’est-à-dire que deux entrées dans la table ne peuvent pas avoir le même numéro de carte. Et ce numéro de carte est obligatoire (NOT NULL). Du coup on aurait peut s’en servir comme clé primaire (unique et obligatoire). Mais bon, on reste sur l’attribut "id", qui joue ce rôle – ça correspond plus à la pratique réelle.

Table des emprunts

		CREATE TABLE loans (
			id_book INT NOT NULL,
			id_user INT NOT NULL,
			date DATE NOT NULL,
			PRIMARY KEY (id_book, id_user),
			FOREIGN KEY (id_book) REFERENCES books(id),
			FOREIGN KEY (id_user) REFERENCES users(id)
		) ENGINE=InnoDB;
	

Dernière table, dernière nouveauté. La clé primaire est ici le tuple ("id_book", "id_user"). En effet, une même personne ne peut emprunter le même livre simultanément qu’une seule fois. Ça suppose d’ailleurs qu’une fois le livre rendu, on efface l’entrée correspondante dans la table. Ce n’est pas forcément la meilleure solution, car du coup on ne peut pas garder d’historique des emprunts. Mais là encore, c’est pour l’exemple et pour respecter l’activité précédente.

Maintenant que vous avez créé ces quatre tables, vous avez la structure de votre base de données… Mais aucune donnée. 😏 C’est donc le moment de…

• Construire des requêtes avec INSERT.

Pour créer une entrée, la commande est INSERT INTO .... C’est assez simple, il vous suffit de regarder la structure des différentes commandes ci-dessous.

		INSERT INTO authors (lastname, firstname, country) VALUES("Orwell", "George", "Grande Bretagne");
		INSERT INTO books (title, date, id_author) VALUES ("Animal Farm", 1945, 1);
		
		-- Insertion devant causer des erreurs
		INSERT INTO books (title, date, id_author) VALUES ("Animal Farm 2", 1945, 3);
		INSERT INTO books (title, date, id_author) VALUES ("Animal Farm 3", 19450, 1);

		-- Insertion de plusieurs entrées
		INSERT INTO `authors` (`lastname`, `firstname`, `country`) VALUES
		('Salinger', 'J.D.', 'États-Unis'),
		('Lee', 'Harper', 'États-Unis'),
		('Golding', 'William', 'Grande Bretagne'),
		('Green', 'John', 'États-Unis'),
		('Austen', 'Jane', 'Grande Bretagne'),
		('Huxley', 'Aldous', 'Grande Bretagne'),
		('Fitzgerald', 'F. Scott', 'États-Unis'),
		('Wilde', 'Oscar', 'Irlande'),
		('Haddon', 'Mark', 'Grande Bretagne'),
		('Steinbeck', 'John', 'États-Unis'),
		('Simmons', 'Dan', 'États-Unis'),
		('Tolkien', 'J.R.R.', 'Grande Bretagne');
	

À ce stade, vous pouvez télécharger le fichier nsi-books-v01.sql et l’importer dans votre base (repartez de zéro en rafraîchissant la page).

• Construire des requêtes SQL avec SELECT, FROM, WHERE, JOIN.

Lire des données dans une (ou plusieurs) tables est l’opération la plus courante. La commande se construit avec SELECT ... FROM ....

La quantité de données pouvant être très importante, on y ajoute souvent les clauses WHERE et ORDER BY. Regardez les quelques exemples ci-dessous, ils parlent d’eux-même.

Provenant d’une table unique

		SELECT * FROM books; -- sélectionner tous les champs
		SELECT title, date FROM books; -- sélectionner certains champs

		-- avec WHERE → ajout d’une condition
		SELECT * FROM books WHERE date > 1990;
		SELECT title, date FROM books WHERE date >= 1900 AND date <= 1950;

		-- avec ORDER BY (ordre ASC ou DESC)
		SELECT title, date FROM books ORDER BY date; -- par défaut ordre croissant (ASC)
		SELECT lastname, firstname FROM authors ORDER BY lastname;
		SELECT title, date FROM books ORDER BY date DESC;
		SELECT title, date FROM books WHERE date > 1900 ORDER BY date DESC;
	

Fonctions

Le langage MySQL comporte quelques fonctions natives que l’on peut utiliser dans une requête. En voici quelques exemples :

		-- compter des entrées
		SELECT COUNT(*) AS nb_livres FROM books;
		SELECT COUNT(firstname) FROM authors; -- auteurs avec prénom
		SELECT COUNT(*) FROM authors WHERE firstname IS NULL; -- auteur sans prénom

		-- minimum et maximum
		SELECT MIN(date) AS plus_ancien FROM books;
		SELECT MAX(date) AS plus_recent FROM books;
	

GROUP BY

Un peu plus subtil, le mot-clé GROUP BY. Ce mot-clé regroupe les entrées qui ont les mêmes valeurs en une unique ligne.

Utilisez la commande ci-dessous et essayez de comprendre le résultat affiché.

		SELECT * FROM authors GROUP BY country
	

Le résultat obtenu comporte seulement trois auteurs, des trois pays différents présents dans le champ country de la table authors. Ces trois auteurs sont le premier auteur dont le champ country est "Grande Bretagne", puis le premier auteur dont le champ country est "États-Unis", etc.

En l’état, cette commande n’a bien sûr aucun intérêt, car les noms des auteurs sont tous différents. Mais regardez son utilisation ci-dessous :

		SELECT country, COUNT(*) AS nb_auteurs FROM authors GROUP BY country;
	

Cette commande permet d’obtenir le nombre d’auteurs des différents pays.

Autres mots-clés : LIMIT, LIKE, DISTINCT

Le mot-clé LIMIT limite le nombre de résultats affichés. Il peut être utile dans le cas où la recherche est susceptible de renvoyer un grand nombre d’entrée.

SELECT * FROM books WHERE date >= 1950 ORDER BY date LIMIT 4;

Le mot-clé LIKE permet de recherche des valeurs d’attributs de type texte avec une correspondance partielle.

SELECT * FROM books WHERE date >= 1950 AND title LIKE "%ring%";

Le mot-clé DISTINCT, utilisé juste après SELECT, permet d’obtenir uniquement les valeurs distinctes.

SELECT DISTINCT country FROM authors;

Provenant de plusieurs tables

JOIN

Il est souvent nécessaire de chercher des données provenant de plusieurs tables différentes. Par exemple, on peut vouloir avoir la liste des livres avec le nom et le prénom de l’auteur, plutôt que son id. Pour cela, on va joindre deux tables en précisant les attributs de chaque table qui permettent de faire cette jointure.

		SELECT books.title, books.date, authors.lastname, authors.firstname
		FROM books
		JOIN authors ON books.id_author = authors.id;
	

Cette requête permet de chercher des valeurs sur deux tables différentes : books et authors. Remarquez que, du coup, lorsqu’on précise les attributs à sélectionner, on doit également préciser sur quelle table on doit les chercher.

La commande JOIN permet de préciser quelle table on joint à la table principale (books), mais aussi et surtout comment trouver la correspondance entre les entrées des différentes tables.

Il est possible de joindre plus de deux tables.

		SELECT books.title, users.name, loans.date
		FROM loans
		JOIN books ON loans.id_book = books.id
		JOIN users ON loans.id_user = users.id
		ORDER BY loans.date;
	

HAVING

WHERE et HAVING servent tous deux à filtrer les éléments d’une requête. La principale différence entre ces deux mots-clés est que WHERE filtre les lignes individuelles avant tout regroupement, alors que HAVING filtre les groupes après l'application de la clause GROUP BY.

		-- Cette requête ne fonctionnera pas comme souhaité
		SELECT authors.country, COUNT(*) AS nb_auteurs
		FROM authors
		WHERE COUNT(*) > 2
		GROUP BY authors.country;

		-- Version correcte avec HAVING
		SELECT authors.country, COUNT(*) AS nb_auteurs
		FROM authors
		GROUP BY authors.country
		HAVING COUNT(*) > 2;
	

La première requête génèrerait une erreur car les fonctions d’agrégation comme COUNT() ne peuvent pas être utilisées dans la clause WHERE. En effet, WHERE s’applique aux lignes individuelles avant le regroupement, alors que COUNT() opère sur des groupes.

			-- Requête n°1
			SELECT users.name, COUNT(*) AS nb_emprunts
			FROM loans
			JOIN users ON loans.id_user = users.id
			GROUP BY users.id
			ORDER BY nombre_emprunts DESC;

			-- Requête n°2
			SELECT authors.lastname, authors.firstname, COUNT(*) AS nb_livres
			FROM books
			JOIN authors ON books.id_author = authors.id
			GROUP BY authors.id
			ORDER BY nombre_livres DESC;

			-- Requête n°3
			SELECT books.id, books.title
			FROM books
			WHERE books.id NOT IN (SELECT id_book FROM loans);
		

				SELECT books.id, books.title
				FROM books
				JOIN authors ON books.id_author = authors.id
				WHERE authors.country = "États-Unis"
			

				SELECT COUNT(DISTINCT users.id) AS nb_eleves
				FROM users
				JOIN loans ON users.id = loans.id_user
				WHERE users.class = "1e 2"
			

				SELECT id, title
				FROM books
				WHERE title LIKE "%the%"
			

• Construire des requêtes avec UPDATE.

La commande pour modifier une (ou plusieurs) entrée(s) est UPDATE. Attention, cette commande modifie le contenu de votre base de données. Une mauvaise manip peut potentiellement être catastrophique en situation réelle ! 😅 Il n’y a pas de Ctrl+Z dans ce domaine.

		UPDATE users SET class="1e 1", card="E074" WHERE id = 1;
	

Je pense que cette commande est suffisamment parlante pour se passer de commentaires.

• Construire des requêtes avec DELETE.

Voilà une commande très simple, car elle efface l’entrée complète. Il s’agit de DELETE.

		DELETE FROM users WHERE class="1e 1"
	

Là encore, la commande est explicite. Et là encore, si elle est mal formulée, les effets peuvent être dévastateurs… 😅