Langage SQL - Notions de base

Langage SQL - Notions de base#

Les bases de données relationnelles peuvent être créées et manipulées grâce au langage SQL1Structured Query Language (langage de requête structuré). Il ne s'agit pas d'un langage de programmation, mais d'un langage de requête permettant d'influer directement sur la base de données en créant des tables, insérant des données, et en y recherchant des informations.

Création de tables#

Le langage SQL permet de créer des tables en spécifiant leur nom et le nom des différentes colonnes.

Pour créer une table, il faut utiliser l'instruction CREATE TABLE suivi du nom de la table et d'une paire de parenthèses. Entre ces parenthèses, nous indiquons la liste des nom des colonnes, ainsi que leur type de données. Les données peuvent être de type TEXT, INTEGER (nombre entier) ou REAL (nombre réel).
Chaque instruction doit se terminer par un point-virgule.

CREATE TABLE Livre (
    titre TEXT,
    auteur TEXT,
    date_pub TEXT,
    isbn INTEGER,
    prix REAL,
    PRIMARY KEY(ISBN)
);

Identifiants artificiels numériques#

Lorsque la clef primaire d'une table est un identifiant artificiel créé uniquement pour ce rôle, on peut utiliser le mot-clef AUTOINCREMENT dans la définition de la PRIMARY KEY afin que SQL se charge lui-même d'attribuer ce numéro unique aux futures lignes de la table. La valeur de cet identifiant doit obligatoirement être INTEGER

CREATE TABLE Utilisateur (
    nom TEXT,
    prenom TEXT,
    role TEXT,
    uid INTEGER,
    PRIMARY KEY(uid AUTOINCREMENT)
);

Clefs étrangères#

Lors de la création d'une table contenant des clefs étrangères, on doit également les spécifier avec FOREIGN KEY ... REFERENCES .... Après le FOREIGN KEY, on spécifie entre parenthèses quelle colonne est la clef étrangère. Puis, après le REFERENCES, on donne le nom de la table et de sa colonne référencée. Dans l'exemple ci-dessous, utilisateur est une clef étrangère référençant la colonne uid de la table Utilisateur. De plus, livre est une clef étrangère référançant la colonne isbn de la table livre.

CREATE TABLE Emprunt (
    livre INTEGER,
    utilisateur INTEGER,
    date TEXT,
    eid INTEGER,
    PRIMARY KEY(eid AUTOINCREMENT),
    FOREIGN KEY(utilisateur) REFERENCES Utilisateur(uid),
    FOREIGN KEY(livre) REFERENCES Livre(isbn)
);

Insertion de données#

Pour insérer une ligne dans une table, il faut utiliser l'instruction INSERT INTO ... VALUES .... Après INSERT INTO, il faut préciser le nom de la table dans laquelle nous souhaitons ajoutons une ligne, ainsi que les noms de colonnes à remplir. Nous ajoutons ensuite le mot-clef VALUES et une paire de parenthèses entre lesquelles nous indiquons les valeurs à insérer dans chaque colonne. L'ordre des valeurs doit être le même que celui établit plus tôt dans la requête.

Les valeurs de type TEXT doivent être entre guillemets, et la séparation entre les unités et les décimales d'une valeur REAL se fait avec un point.

INSERT INTO Livre(titre, auteur, date_pub, isbn, prix) VALUES
('La Vérité sur l`Affaire Harry Québert', 'Joël Dicker', 2012, 9782877068161, 23.95);

Lorsqu'on insère des données dans une table contenant une clef primaire qui a été définie avec AUTOINCREMENT, on peut omettre sa valeur et SQL se charge de l'attribuer automatiquement.

INSERT INTO Utilisateur(nom, prenom, role) VALUES
('Jan', 'Maxime', 'enseignant');
INSERT INTO Utilisateur(nom, prenom, role) VALUES
('Queloz', 'Aurélien', 'élève');

Sélectionner des données#

L'instruction SELECT ... WHERE ... permet de rechercher des données dans une table. On fait suivre le mot-clef SELECT du nom de(s) colonne(s) que l'on souhaite afficher, et le WHERE de la table contenant ces données. Ainsi, la requête suivante nous permet d'afficher tous les titres et auteurs dans notre table Livre.

Si on souhaite ne pas avoir de lignes "doublons" dans les résultats, on peut faire suivre SELECT du mot-clef DISTINCT afin de les retirer du résultat de la recherche et n'avoir ainsi que des lignes uniques.

SELECT DISTINCT Livre.auteur FROM Livre

On peut également utiliser SELECT * pour sélectionner toutes les colonnes d'un seul coup.

SELECT * FROM Livre

Filtrer les données#

Les résultats obtenus à l'aide d'une requête SELECT ... FROM ... peuvent être filtrés en faisant suivre cette requête d'un WHERE. Ce mot-clef est suivi d'une condition qui s'écrit de manière similaire à Python en utilisant les opérateurs de comparaisons =, !=, >, >=, <, <=. La requête suivante permet par exemple de sélectionner toutes les lignes où le prix est inférieur ou égal à 10CHF.

SELECT * FROM Livre WHERE Livre.prix <= 10

Celle ci-dessous permet de sélectionner les titres de livre écrit par J.K. Rowling.

SELECT Livre.Titre FROM Livre WHERE Livre.auteur = 'J.K. Rowling'

Opérateurs logiques#

Comme en Python, il est possible de chaîner plusieurs conditions avec les opérateurs logiques AND et OR. La requête suivante permet d'afficher tous les livres écrits par Alexandre Dumas ou par Gustave Flaubert.

SELECT Livre.Titre FROM Livre WHERE Livre.auteur = 'Alexandre Dumas' OR Livre.Auteur = 'Gustave Flaubert'

La requête suivante permet d'afficher toutes les infos des livres écrits par J.K. Rowling après 2003.

SELECT Livre.Titre FROM Livre WHERE Livre.auteur = 'J.K. Rowling' AND Livre.date_pub > 2003

Opérateur LIKE#

Le mot-clef LIKE peut s'utiliser comme un opérateur de comparaison sur du texte, de manière similaire à un =. Il permet de vérifier qu'une colonne soit semblable à une valeur que l'on définit. Ces similitudes peuvent se décliner de trois manières

  • La valeur commence par un certain texte. Par exemple, pour trouver tous les prénoms d'utilisateur qui commencent par "M" ou pour trouver tous les livres dont le titre commence par "Harry". Pour cela, il faut ajouter le signe % (qui peut être compris par "n'importe quel texte") après la valeur commençant le mot.

    SELECT * FROM Livre WHERE Livre.titre LIKE 'Harry%'

  • La valeur se termine par un certain texte. Par exemple pour trouver tous les livres dont la date de publication se termine par "2". Cette fois, le signe % doit précéder la valeur terminant le mot.

    SELECT * FROM Livre WHERE Livre.date_pub LIKE '%2'

  • La valeur contient un certain texte. Par exemple pour trouver tous les livres dont le titre contient "le". Le signe % doit ici entourer la valeur à contenir.

    SELECT * FROM Livre WHERE Livre.titre LIKE '%le%'

Joindre plusieurs tables#

Les requêtes SELECT précédentes ont permis de rechercher des informations dans une seule table à la fois. Si toutefois on souhaite rechercher tous les titres de livres qu'un certain utilisateur a emprunté, les trois tables devront être mises à contribution dans la même requête.

Pour joindre deux tables entre elles, on utilise JOIN ... ON ... dans une requête SELECT. On fait suivre le JOIN de la table à rajouter à la requête, et le ON des champs qui permettent de lier ces deux tables, avec un signe d'égalité. Ces deux champs sont simplement la clef étrangère et la clef primaire référencée. Par exemple, la requête suivante me permet de lier la table des Utilisateurs avec la table des Emprunts.

SELECT *
FROM Utilisateur
JOIN Emprunt ON Emprunt.utilisateur = Utilisateur.UID

Comme vous pouvez le constater avec le résultat de cette requête, tous les emprunts de livre ont été collés à leur utilisateur.

On peut utiliser autant de JOIN que souhaiter pour coller plusieurs tables ensemble. La requête suivante nous permet de coller les 3 tables ensemble :

SELECT *
FROM Utilisateur
JOIN Emprunt ON Emprunt.utilisateur = Utilisateur.UID
JOIN Livre ON Emprunt.livre = Livre.ISBN

Cette requête peut être simplement complétée par un WHERE et affinée après le SELECT pour trouver tous les noms de livres empruntés par l'utilisateur dont le prénom est Catherine

SELECT Livre.titre
FROM Utilisateur
JOIN Emprunt ON Emprunt.utilisateur = Utilisateur.UID
JOIN Livre ON Emprunt.livre = Livre.ISBN
WHERE Utilisateur.prenom = 'Catherine'
Contenu