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.

		SELECT * FROM books;
		SELECT title, date FROM books;

		-- avec WHERE
		SELECT * FROM books WHERE date > 1990;
		SELECT title, date FROM books WHERE date >= 1900 AND date <= 1950;

		-- ORDER BY
		SELECT title, date FROM books ORDER BY date 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;

		-- Nombre total de livres
		SELECT COUNT(*) AS nombre_livres FROM books;

		-- Année du livre le plus ancien
		SELECT MIN(date) AS plus_ancien FROM books;

		-- Année du livre le plus récent
		SELECT MAX(date) AS plus_recent FROM books;

		-- Nombre d’auteurs par pays
		SELECT country, COUNT(*) AS nombre_auteurs FROM authors GROUP BY country;