Un langage d’interrogation: SQL¶

SQL (Structured Query Language, langage de requêtes structuré) est un langage déclaratif destiné à la manipulation de bases de données. Il ne s’agit pas a proprement parlé un langage de programmation, mais plutôt d’une interface standard pour accéder aux bases de données.

LDD, LCD, LMD, LCT¶

SQL est à la fois un LDD, LCD, LMD et LCT mais que signifie de pareil acronyme ?

Langage de Définition de Données: Il permet de créer et de supprimer des objets dans la base de données.
Langage de Contrôle de Données: Il permet de gérer les droits sur les objets de la base.
Langage de Manipulation de Données: Il permet la recherche, l’insertion, la mise à jour et la suppression de données.
Langage de Contrôle de Transaction: Il permet la gestion des transactions.

Langage de Manipulation de Données¶

Soit la relation \(Videogame\) tel que:

id	name	price	type
1	Rayman	15	platform
2	Warcraft	25	RTS
3	StarCraft	20	RTS

Sélection¶

\(\sigma_{(E)}R\) se traduit en SQL par

SELECT * FROM R WHERE <E>;

\(\sigma_{(id\geq2)}Videogame\)	SELECT * FROM Videogame WHERE id >= 2;
\(\sigma_{(name='StarCraft')}Videogame\)	SELECT * FROM Videogame WHERE name = 'StarCraft';
\(\sigma_{('S' \in name)}Videogame\)	SELECT * FROM Videogame WHERE name LIKE 'S%';

L’opérateur LIKE¶

L’opérateur LIKE permet de faire des requêtes sur des motifs de recherche.

SELECT column_list
FROM table_name
WHERE
column_1 LIKE pattern;

Il existe deux manières de construire un motif de recherche en utilisant le signe de pourcentage % et l’underscore _ :

% correspond à toute séquence de zéro caractère ou plus,
_ correspond à n’importe quel caractère.

's%' -- correspond à toute chaîne commençant par s,
'%er' -- correspond à toute chaîne se terminant par er,
'%per%' -- correspond à toute chaîne contenant les caractères per.
'_pple' -- correspond à toute chaîne se terminant par pple et commençant par un unique caractère.

Projection¶

\(\Pi_{(A1, A2, .., AN)}R\) se traduit en SQL par

SELECT a1, a2, .., an FROM R;

\(\Pi_{(name, type)}Videogame\)

SELECT name, type FROM Videogame;

Projection et Sélection¶

L’exemple ci-dessous d’algèbre relationnel:

\[ \begin{align}\begin{aligned}R = \sigma_{(id \geq2)}Videogame\\\Pi_{(name, type)}R\end{aligned}\end{align} \]

se traduit en SQL:

SELECT name, type FROM Videogame WHERE id >= 2;

Limitation¶

Parfois une requête peut retourner de (trop) nombreuses lignes, il peut être utile de limiter le retour de ce nombre de lignes. SQL nous offre la possibilité de le faire via la clause LIMIT.

SELECT column_list FROM table LIMIT row_count;

Retourner les 10 premiers jeux vidéo de la base¶

SELECT id, name FROM Videogame LIMIT 10;

Retourner 10 jeux video à partir du 10ème jeu de la base¶

SELECT id, name FROM dinosaurs LIMIT 10 OFFSET 10;

Alias¶

Il peut parfois être utile de renommer au sein d’une requête une table ou une colonne. Il s’agit des Alias, les noms ne sont pas modifiés en base bien sûr.

SELECT column1, column2
FROM table_name AS alias_name
WHERE [condition];

SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

Order by¶

Il peut parfois être utile de trier les résultats d’une requête par une ou plusieurs colonnes, il s’agit de la clause ORDER BY.

SELECT column1, column2
FROM table_name
WHERE [condition]
ORDER BY column2, column1 ASC|DESC;

Produit cartésien¶

\(R_{1} \times R_{2}\) se traduit en SQL par

SELECT * FROM R1, R2, .., Rn;

\(Videogame \times Budget\)

SELECT * FROM Videogame, Budget;

Union¶

\(R_{1} \cup R_{2}\) se traduit en SQL par

SELECT a1 FROM R1
UNION
SELECT a2 FROM R2;

\(Videogame \cup Boardgame\)

SELECT name FROM Videogame
UNION
SELECT name FROM Boardgame;

Intersection¶

\(R_{1} \cap R_{2}\) se traduit en SQL par

SELECT a1 FROM R1
INTERSECT
SELECT a2 FROM R2;

\(VideogameBen \cap VideogameHan\)

SELECT name FROM dinosIngen
INTERSECT
SELECT name FROM dinosMasrani;

Jointure¶

\(R_{1} \Join_{E} R_{2}\)

Via la clause `WHERE`¶

SELECT *
FROM R1, R2, Rn
WHERE <condition>;

Via la clause `ON`¶

SELECT *
FROM R1 INNER JOIN R2
ON <condition>;

Équi-jointure¶

SELECT *
FROM R1, R2
WHERE R2.id = R1.id;

Analyse de données avec les agrégats¶

Note

Partitionnement horizontal d’une table en sous-tables, en fonction des valeurs d’un ou plusieurs attributs de partitionnement, suivi éventuellement de l’application d’une fonction de calcul à chaque attribut des sous-tables obtenues. On parle aussi de regroupement.

SELECT <A1, An, fonctions de calcul>
FROM <R1, R2, Rn> WHERE <condition>
GROUP BY <A2, A1, A6, An>;

La table est divisée en sous-ensembles de lignes, avec un sous-ensemble pour chaque valeur différente des attributs de partitionnement projetés dans le SELECT. Les fonctions d’agrégation sont appliquées sur les attributs concernés.

SELECT dept.name, AVG(employee.age)
FROM employee, dept
WHERE employee.dept_id = dept.id
GROUP BY dept.id;

Fonctions d’agrégation¶

Note

Une fonction d’agrégation (ou fonction de regroupement) s’applique aux valeurs du sous-ensemble d’un agrégat d’une relation avec pour résultat la production d’une valeur atomique unique (entier, chaîne, date, etc).

Il existe cinq fonctions d’agrégation prédéfinies:

COUNT: Renvoie le nombre de valeurs non nulles d’une propriété pour tous les tuples d’une relation.
SUM: Renvoie la somme des valeurs d’une propriété des tuples (numériques) d’une relation.
AVG: Renvoie la moyenne des valeurs d’une propriété des tuples (numériques) d’une relation.
MIN: Renvoie la plus petite valeur d’une propriété parmi les tuples d’une relation.
MAX: Renvoie la plus grande valeur d’une propriété parmi les tuples d’une relation.

Fonctions de calcul sans partitionnement

Si une ou plusieurs fonctions de calcul sont appliquées sans partitionnement, le résultat de la requête est un tuple unique.

Comptage d’une relation

Pour effectuer un comptage sur tous les tuples d’une relation, appliquer la fonction count à un attribut de la clé primaire. En effet cet attribut étant non nul par définition, il assure que tous les tuples seront comptés.

Single-Value Rule¶

Le principe de la « Single-Value Rule » établie par le standard SQL énonce que toute colonne de la clause SELECT doit soit :

être un attribut d’agrégation (et donc désigner une colonne présente dans la clause GROUP BY) ;
être attribut de calcul (présent dans une fonction d’agrégation.)

SELECT countrycode, citycode, COUNT(citycode)
FROM city
GROUP BY countrycode;

ERROR:  column "city.citycode" must appear in the GROUP
BY clause or be used in an aggregate function;

La requête est non standard et non logique car on cherche à mettre plusieurs données dans la case citycode après le GROUP BY (il y a plusieurs citycode par countrycode). Elle sera refusée par tous les SGBD.

Restriction après agrégation¶

La clause HAVING permet d’effectuer une second restriction après l’opération d’agrégation.

SELECT <attributs, fonctions>
FROM <relations>
WHERE <condition>
GROUP BY <attributs>
HAVING <condition sur les fonctions de calcul>;

SELECT dept.name, AVG(employee.age)
FROM employee, dept
WHERE employee.dept_id = dept.id
GROUP BY dept.id
HAVING COUNT(employee.id) > 2;

Cette requête calcul l’âge moyen du personnel pour chaque société comportant plus de 2 salariés.

Ordre de résolution des requête SQL¶

L’ordre de résolution standard d’une requête SQL est :

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

Création d’une base de données¶

Avec SQLite, il suffit d’indiquer le nom du fichier de base de données en argument de la commande sqlite3.

$ sqlite3 boardgames.sqlite3

Important

Si le fichier existe alors la commande ouvre la base de données sinon celle-ci est créée avant ouverture.

Création d’une table¶

Exemple de code de création des relations \(Category\) et \(Boardgame\)

CREATE TABLE IF NOT EXISTS "Category" (
`id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`name`  TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS "Boardgame" (
`id`          INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`name`        NUMERIC NOT NULL,
`price`       REAL NOT NULL,
`category_id` INTEGER NOT NULL,

FOREIGN KEY(`category_id`) REFERENCES `Category`(`id`)
);

Pour en savoir plus rendez-vous sur la documentationcreatetabledesqlitehttps://www.sqlite.org/lang_createtable.html.

Attention

Il ne faut pas oublier d’activer le support des clés étrangéres via le pragma suivant:

PRAGMA foreign_keys;

Indication

Il est recommandé d’écrire les requêtes de création de tables dans un fichier sql et de le jouer ensuite via la commande sqlite3

$ sqlite3 boardgames.sqlite3
sqlite> .read creation_schema.sql

Renommer une table¶

ALTER TABLE boardgame
RENAME TO boardgames;

Ajouter une colonne à une table¶

ALTER TABLE boardgames
ADD COLUMN description TEXT;

Supprimer une table¶

DROP TABLE boardgames;

Insertion de données¶

INSERT INTO table1 (column1, column2 ,..)
VALUES(value1, value2 ,...);

Pour en savoir plus rendez-vous sur la documentation sqlite d’insert.

Insertion d’une ligne¶

INSERT INTO boardgame (name, price)
VALUES ('HeroQuest', 60.3);

Insertion de plusieurs lignes¶

INSERT INTO boardgame (name, birthday)
VALUES
('Star Wars Rebellion', 99.90),
('Conan', 103.90);

Mise à jour de données¶

UPDATE table
SET column_1 = new_value_1,
column_2 = new_value_2
WHERE
search_condition
ORDER column_or_expression
LIMIT row_count OFFSET offset;

Pour en savoir plus rendez-vous sur la documentation sqlite de l’update.

Mise à jour d’une colonne¶

UPDATE boardgames
SET name = 'HeroQuest a very old game'
WHERE id = 1;

Mise à jour de plusieurs colonne¶

UPDATE boardgames
SET name = 'HeroQuest a very old game',
price = 200
WHERE id = 1;

Suppression de données¶

DELETE
FROM table
WHERE search_condition;

Pour en savoir plus rendez-vous sur la documentation sqlite du delete.

Suppression d’une ou plusieurs lignes¶

DELETE FROM boardgames WHERE id = 1;
DELETE FROM boardgames WHERE name LIKE 'Star Wars%';

Suppression de toutes les lignes¶

DELETE FROM boardgames;

Les vues¶

C’est un ensemble des résultats d’une requête stockée dans une entité. Elle offre la possibilité d’accéder aux données des tables sous-jacentes via la vue. Les tables auxquelles fait référence la requête dans la définition de la vue s’appellent des tables de base.

Une vue est utile dans certains cas:

Fournir une couche d’abstraction sur les tables. Ajout et suppression de colonnes dans la vue sans toucher au schéma des tables sous-jacentes.
Encapsulation des requêtes complexes afin de simplifier l’accès aux données.

Attention

Les vues sont en lecture seule. Cela signifie que vous ne pouvez pas utiliser les instructions INSERT, DELETE et UPDATE pour mettre à jour les données des tables de base via la vue.

Création d’une vue¶

CREATE VIEW IF NOT EXISTS view_name
AS
select-statement;

CREATE VIEW v_boardgames
AS
SELECT id, boardgame.name, category.name AS category,
FROM boardgame, category
WHERE boardgame.category_id = category.id;

Suppression d’une vue¶

DROP VIEW IF EXISTS view_name;

Les transactions¶

Par défaut, SQLite fonctionne en mode « auto-commit ». Cela signifie que pour chaque commande, SQLite démarre, traite et valide automatiquement la transaction.

Ouvrir une transaction¶

Pour démarrer une transaction de manière explicite il faut exécuter la commande BEGIN TRANSACTION. Dès lors la transaction est ouverte jusqu’à ce qu’elle soit explicitement validée ou annulée.

BEGIN TRANSACTION;

Exécuter des requêtes de mises à jour¶

On exécute des instructions SQL pour sélectionner ou mettre à jour les données de la base de données. Notez que la modification est uniquement visible pour la session en cours (ou le client).

Valider ou annuler les modifications¶

Enfin on valide les modifications dans la base de données à l’aide de l’instruction COMMIT ou COMMIT TRANSACTION.

COMMIT;

Si vous ne souhaitez pas enregistrer les modifications, vous pouvez annuler en utilisant l’instruction ROLLBACK ou ROLLBACK TRANSACTION:

ROLLBACK;

Un exemple de transaction¶

On commence par créer deux tables:

la table accounts stocke des données sur les numéros de compte et leurs soldes.
la table account_changes stocke les modifications des comptes.

Création des tables¶

CREATE TABLE accounts (
    account_no INTEGER NOT NULL,
    balance DECIMAL NOT NULL DEFAULT 0,
    PRIMARY KEY(account_no),
    CHECK(balance >= 0)
);

CREATE TABLE account_changes (
    change_no INT NOT NULL PRIMARY KEY,
    account_no INTEGER NOT NULL,
    flag TEXT NOT NULL,
    amount DECIMAL NOT NULL,
    changed_at TEXT NOT NULL
);

Insertion des données d’init¶

INSERT INTO accounts (account_no,balance)
VALUES (100,20100);

INSERT INTO accounts (account_no,balance)
VALUES (200,10100);

Premier transfert¶

Transfert de 1000 crédits du compte 100 vers le 200 et enregistrement des modifications dans la table account_changes en une seule transaction

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE account_no = 100;

UPDATE accounts
SET balance = balance + 1000
WHERE account_no = 200;

INSERT INTO account_changes(account_no,flag,amount,changed_at)
VALUES(100,'-',1000,datetime('now'));

INSERT INTO account_changes(account_no,flag,amount,changed_at)
VALUES(200,'+',1000,datetime('now'));

COMMIT;

Échec de transfet¶

Exemple de tentative de déduire 20000 crédit du compte 100:

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 20000
WHERE account_no = 100;

INSERT INTO account_changes(account_no,flag,amount,changed_at)
VALUES(100,'-',20000,datetime('now'));