Informatique au lycée Faustin Fleret

Le langage SQL

Un système de gestion de bases de données (SGBD) est un logiciel qui organise et gère les données de façon transparente pour l’utilisateur. Ce sont des logiciels dont la conception est bien trop complexe pour pouvoir être abordée dans ce cours ; nous nous contenterons d’interagir avec eux par l’intermédiaire de requêtes exprimées dans un langage devenu standard au fil des temps : le langage SQL (pour Structured Query Language).

SQL est un langage déclaratif, il n'est donc pas a proprement parlé un langage de programmation, mais plutôt une interface standard pour accéder aux bases de données.

Une base de données est un ensemble de "tables" appelés relations ayant des liens entre eux. Chaque table est composée de "lignes" appelées enregistrements correspondant à des n-uplets de même structure. On appelle cette structure le schéma relationnel de la table.

Présentation du logiciel DB Browser SQLite

Ce logiciel gratuit et libre permet de créer et manipuler des bases de données sans avoir besoin d’un serveur (contrairement à MySQL par exemple). Son interface est agréable et très intuitive.

Téléchargement et installation

Le logiciel se trouve ici. Sur Windows, on peut choisir sa version et il existe aussi une version portable pour une exécution sans installation.

Utilisation

Au lancement, la fenêtre devrait ressembler à cela :

Nous utiliserons beaucoup l'onglet "Exécuter le sql" pour atteindre les objectifs du programmes.

Pour découvrir le langage SQL, nous allons répondre à l’appel d’offre du collège Poudlard qui a besoin d’une base de données pour la gestion de son établissement.

Nous allons organiser la base de données poudlard avec une relation « élèves » et une relation « maisons ».

Créer une base de données

Pour créer la table eleves, il faut utiliser le mot clé CREATE TABLE.

CREATE TABLE IF NOT EXISTS maisons (
	"nom"	TEXT,
	"referent"	TEXT,
	"dortoir"	TEXT,
	"effectif"	INTEGER,
	PRIMARY KEY("nom")
);

Il faut maintenant ajouter du contenu à notre base. Pour ajouter des entrées, on utilise “INSERT INTO .... VALUES ....”.

INSERT INTO maisons (nom,referent,dortoir,effectif)
VALUES ("Gryffondor","MacGonagall","tour",28),
("Serdaigle","Flitwick","tour",35),
("Poufsouffle","Chourave","logis",31),
("Serpentard","Rogue","donjon",25);

Pour modifier un enregistrement/ligne, il faut utiliser UPDATE

UPDATE eleves SET moyenne=20 WHERE nom="Granger" AND prenom="Hermione" ;

Opérations sur une table

SELECT  dortoir FROM maisons

SELECT  nom , dortoir FROM maisons
SELECT * FROM maisons

Pour éliminer les doublons, on utilise SELECT DISTINCT.

SELECT DISTINCT dortoir FROM maisons ;

Pour renommer un attribut (ou une table), on effectue une projection en indiquant tous les attributs et en précisant avec le mot clef AS le nouveau nom.

SELECT nom , referent AS prof_principal , effectif FROM maison ;

Le mot-clé WHERE filtre les données qui répondent à un critère de sélection. Par exemple, pour connaître le nom des élèves de la maison du Poufsouffle on écrira :

SELECT nom,prenom
FROM eleves
WHERE maison = "Poufsouffle" 

L’opérateur LIKE est utilisé dans la clause WHERE des requêtes SQL. Ce mot-clé permet d’effectuer une recherche sur un modèle particulier.

• LIKE ‘%a’ : le caractère “%” est un caractère joker qui remplace tous les autres caractères. Ainsi, ce modèle permet de rechercher toutes les chaînes de caractère qui se termine par un “a”.
• LIKE ‘a%’ : ce modèle permet de rechercher toutes les lignes de “colonne” qui commence par un “a”.
• LIKE ‘%a%’ : ce modèle est utilisé pour rechercher tous les enregistrement qui utilisent le caractère “a”.

Fonctions d’agrégation

Il est possible de regrouper certains enregistrements d’une table par agrégation pour leur appliquer une fonction. Par exemple, pour connaître le nombre d'élèves on écrira :

SELECT COUNT ( * ) FROM eleves

Pour afficher la moyenne des élèves, nous utiliserons la requète :

SELECT AVG(Moyenne) FROM eleves ;

Toutes ces fonctions prennent tout leur sens lorsqu’elles sont utilisée avec la commande GROUP BY qui permet de filtrer les données sur une ou plusieurs colonnes.

SELECT maison, AVG(moyenne) FROM eleves
GROUP BY maison ;

Jointures

L’intérêt d’une base de données réside en particulier dans la possibilité de croiser des informations présentes dans plusieurs tables par l’intermédiaire d’une jointure. Dans la base de données qui nous occupe, le lien entre les 2 tables est la maison, la clé étrangère pour la relation eleves et clef primaire pour la relation maisons.
Les mots-clés JOIN ... ON créent une table intermédiaire formée du produit cartésien des deux tables et applique ensuite la requête sur la nouvelle relation.
Remarque : L’interrogation de plusieurs tables simultanément rend nécessaire le préfixage de l’attribut par le nom de la table pour le cas où certaines d’entres-elles auraient des noms d’attributs en commun.

SELECT referent FROM maisons
JOIN eleves ON eleves.maison = maisons.nom
where eleves.maison = "Serpentard"

SELECT referent FROM maisons m
JOIN eleves e ON e.maison = m.nom
where e.maison = "Serpentard"