Il existe plusieurs langages permettant d’interagir avec une base de données relationnelle. Le plus utilisé est le Structured Query Language ou SQL.

Ce langage peut être partagé en trois parties :

• la définition des données : pour créer et modifer des tables et lignes dans la base
• le contrôle de transaction : pour interroger la base par exemple
• le contrôle des données : pour gérer l’accès aux données (selon le statut de l’utilisateur par exemple)

On utilise ci-dessous du SQL classique. On pourra à ce propos consulter avec intérêt les sites 1 et 2.

Remarque : sqlite, que nous utiliserons avec Python, diffère légèrement de SQL. On pourra consulter ce site.

Le SQL s’utilise soit dans l’interface de programmation d’un SGBD soit directement dans du code python avec sqlite3.

Nous allons dans cette partie utiliser le schéma relationnel suivant :

Nous pouvons donc créer une base de données lycee :

CREATE DATABASE IF NOT EXISTS lycee

I. Création des tables

La structure de base pour créer une table est la suivante :

CREATE TABLE nom_de_la_table
(
    colonne1 type_donnees,
    colonne2 type_donnees,
    colonne3 type_donnees,
)

Il est aussi possible d’ajouter des contraintes, pour les clés primaires et étrangères par exemples.

Ci-dessous est donnée le code créant la table eleves :

CREATE TABLE eleves 
(
    id INTEGER NOT NULL AUTO_INCREMENT,
    nom VARCHAR(100),
    prenom VARCHAR(100),
    naissance VARCHAR(100),
    classe INTEGER,
    spe_1 INTEGER,
    spe_2 INTEGER,

    PRIMARY KEY (id)

    FOREIGN KEY (classe) REFERENCES classes(id),
    FOREIGN KEY (spe_1) REFERENCES cours(id),
    FOREIGN KEY (spe_2) REFERENCES cours(id)
)

Le code est assez clair :

• on crée tout d’abord la table en précisant son nom
• puis les variables en indiquant leur types. VARCHAR(100) signifie que l’on utilise du texte comptant au maximum 100 caractères
• vient ensuite la clé primaire…
• … et enfin les clés secondaires

II. Insérer des valeurs

Les tables étant créées il est intéressant de les peupler en insérant des lignes (row en anglais)

La fonction à utiliser est INSERT et sa structure :

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN)

Dans bien des cas, il n’est pas nécessaire de fournir la valeur de la clé primaire si celle-ci est actualisée automatiquement

Par exemple :

INSERT INTO eleves (nom, prenom, naissance, classe, spe_1, spe_2)
VALUES ("Dupont", "Eric", "01/03/03", 1, 5, 2)

III. Rechercher des valeurs

1. Sélection de base

Une fois la base créée et peuplée, l’action la plus courante est de sélectionner des données.

La fonction à utiliser est SELECT et sa structure :

SELECT nom_du_champ FROM nom_du_tableau WHERE condition

• On peut par exemple rechercher les informations de l’élève dont l’id est 8 :

SELECT * FROM eleves WHERE id = 8

Le * (appelé wildcard) signifie que l’on sélectionne toutes les colonnes.

• On peut aussi indiquer le nom des attributs à sélectionner :

SELECT nom, prenom FROM eleves WHERE id = 8

• Et si l’on souhaite obtenir toutes les informations de tous les élèves ?

SELECT * FROM eleves WHERE 1

Explication : le WHERE 1 est toujours vrai car 1 = True.

• Pour les conditions multiples ?

SELECT nom FROM eleves WHERE id <= 16 and classe = 2

On sélectionne ainsi les élèves dont l’id est inférieur ou égal à 16 et la classe est la 2

• Comment sélectionner les chaînes de caractères ?

SELECT nom FROM eleves WHERE prenom LIKE "Jul%"

permettra de sélectionner les élèves dont le prénom commence par Jul (les Julie, Julia, Julio, Julien…)

2. Jointures

Imaginons que nous souhaitions sélectionner les élèves de la “Terminale 1”… Nous pourrions faire une requête du type

SELECT nom, prenom FROM eleves WHERE classe = 1

mais il faudrait être certain que l’identifiant de la “Terminale 1” est bien le numéro 1… Or voici la table classes :

La “Terminale 1” n’a pas l’id numéro 1 !

Si l’on consulte le schéma proposé plus haut, on constate que la classe de chaque élève correspond à un id de la table classe. Il est ainsi possible de préciser la classe en mettant en correspondance les tables eleves et classes. Comme nous souhaitons sélectionner les élèves pour lesquels la classe est exactement la “Terminale 1” et pas plus, nous allons faire une INNER JOIN

La structure est la suivante :

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.fk_id

Dans le cas présent on fait :

SELECT * FROM eleves INNER JOIN classes ON eleves.classe = classes.id WHERE classes.nom = "Term-1"

Expliquons les choses :

• SELECT * FROM eleves : on sélectionne tous les attributs de la table eleves …
• INNER JOIN classes : … en indiquant que l’on souhaite utiliser des critères de la table classes…
• ON eleves.classe = classes.id : … en rappelant la relation entre les tables (la classe de l’élève correspond à l’id de la classe )…
• WHERE classes.nom = "Term-1" :… et on ne prend que les lignes pour lesquelles le nom de la classe est Term-1

3. Présentation des résultats

• Il est possible dans une recherche de compter les résultats :

SELECT count(*) FROM eleves WHERE 1

On obtient avec cette requête le nombre d’élèves du lycée.

• Afin de renommer un résultat, on utilise un alias :

SELECT count(*) as nombre FROM eleves WHERE 1

Là encore on compte le nombre d’élèves mais ce décompte est renvoyé dans la colonne nombre.

• Pour les tri, on utilise les mots clés ORDER BY ... ASC (ordre croissant) ou ORDER BY ... DESC (ordre décroissant) :

SELECT nom FROM eleves WHERE 1 ORDER BY nom ASC

Cette requête renvoie les noms de tous les élèves triés dans l’ordre alphabétique

• Enfin, il est possible de grouper les données par exemple pour compter le nombre d’élèves par classe. On utilise le mot clé GROUP BY ...

SELECT classes.nom, count(eleves.id) as nombre_eleves FROM eleves INNER JOIN classes ON eleves.classe = classes.id GROUP BY classes.nom

On a ainsi :

• sélectionné le nom de chaque classe et compté combien elle comporte d’élèves (en comptant les id)
• en réalisant une jointure avec la table classes
• et en groupant les résultats selon les classes.nom

IV. Modifier des valeurs

1. Mettre à jour une valeur

Comment faire si un élève change de classe ? On met à jour sa donnée avec UPDATE :

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition; 

Cela peut donner (en connaissant bien les id des élèves et des classes)

UPDATE eleves SET eleves.classes = 3 WHERE eleves.id = 8

En étant astucieux, on peut ainsi renommer tous les élèves :

UPDATE eleves SET eleves.nom = "Wayne" WHERE 1

2. Supprimer une entrée

On a parfois besoin de supprimer une entrée/ligne. On utilise :

DELETE FROM table_name WHERE condition; 

Ainsi :

DELETE FROM eleves WHERE eleves.id = 9 

permet d’effacer l’élève d’id numéro 9 s’il quitte le lycée.