Introduction

Le SQL (Structured Query Language ou langage de requête structurée) est un langage informatique permettant d’exploiter des bases de données relationnelles. Il permet ainsi de réaliser les quatre opérations de base pour la persistance de données (CRUD : create, read update, delete) mais également de gérer l’organisation des données.

Le langage a été créé en 1974 et a été normalisé en 1986. Le langage est reconnu par la plupart des systèmes de gestion de base de données (SGBDR), comme par exemple MySQL ou encore PostgreSQL.

Cet article n’a pas pour but de présenter le langage SQL, pour ça, je vous laisse suivre des tutoriels plus intéressants, ici c’est plutôt une fiche récapitulative des outils que propose le langage car je ne sais pas pour vous mais il m’arrive souvent d’avoir un trou sur telle ou telle fonctionnalité ! Donc si vous voulez juste vous rafraichir les idées, cet article est fait pour vous 😀 !

Create

CREATE TABLE user (
    id INT PRIMARY KEY NOT NULL,
    pseudo VARCHAR(100) NOT NULL CHECK (pseudo <> ''),
    email VARCHAR(100),
    register_date DATE
);

Création d’une table user avec 4 champs :

• L’id comme clé primaire
• Le pseudo qui doit être non nul et différent d’une chaîne vide
• Une adresse email
• La date d’inscription de l’utilisateur

Insert

INSERT INTO user VALUES (
    'lelouch', 'lelouch@gmail.com', NOW()
);

Simple exemple d’insertion pour notre table user du début. On n’a pas besoin de préciser l’id, le SGBD va se charger d’insérer un nouvel ID par lui-même.

Update

UPDATE user SET pseudo = 'lelouch' WHERE id = 1;

On met à jour le pseudo d’un utilisateur dont l’id vaudrait zéro (non, non aucune référence n’est présente ici :3).

Delete

DELETE * FROM user WHERE email = '';

On supprime tous les utilisateurs de notre base qui n’ont pas d’adresse email de renseigné.

Select

SELECT * FROM user;

Simple récupération de toutes les données de la table user.

Thétajointure

SELECT * FROM animal WHERE weight > 50;

Jointure où l’opérateur de pivot peut-être : <, <=, >, >=, != ou <>. Ici, par exemple, on récupère tous les animaux dont le poids va être supérieur à 50 Kg.

Autojointure

SELECT * FROM table t, table t2 ON t2.id = t.id;

C’est la jointure d’une table à elle-même, grâce à l’utilisation d’alias.

Equijointure

SELECT * FROM user t, table2 t2 WHERE t.colonne = t2.colonne;

L’équijointure, c’est tout simplement tester l’égalité entre la clé primaire d’une première table et la clé étrangère d’une seconde.

Jointure externe

SELECT id, nom FROM authors u
LEFT JOIN books b ON b.userid = u.id;

Une jointure entre deux tables authors et books. L’une étant dominante sur sa table subordonnée (dans le cas d’un LEFT JOIN, cela va correspondre à la table où il peut manquer des éléments).

Opérateurs ensemblistes

Les différentes requêtes qu’on a vues précédemment sont appelées des opérations unaires. C’est-à-dire qu’elles consistent à éliminer des lignes ou des colonnes de la table désirée afin de récupérer uniquement l’information qui nous intéresse. A contrario les opérations ensemblistes consistent à effectuer un recoupement entre plusieurs tables. Toutefois, attention pour pouvoir utiliser ce genre de fonction il faut avoir le même type de schéma entre les tables utilisé.

UNION

Ensemble des lignes de 2 tables. Imaginons une entreprise qui possède plusieurs magasins et dans chacun de ces magasins il y a une table qui liste les clients. Sachant que certains clients sont potentiellement présents dans les 2 tables, pour éviter de retourner plusieurs fois les mêmes enregistrements, on peut utiliser la requête UNION. Comme par exemple :

SELECT * FROM magasin1_client
UNION
SELECT * FROM magasin2_client

INTERSECT

Ensemble des lignes appartenant simultanément aux 2 tables.

SELECT nom
FROM   nageurs
INTERSECT
SELECT prenom
FROM   nageurs
ORDER  BY 1

Dans cet exemple, on va récupérer par ordre alphabétique les noms des nageurs qui ont un même prénom qu’un autre nageur.

EXCEPT

Ensemble des lignes appartenant à la 1ère table mais pas à la seconde.

SELECT prenom, nom 
FROM user
EXCEPT
SELECT prenom, nom 
FROM user
WHERE banned = 1;

Permet de récupérer l’ensemble des utilisateurs non bannis de la base.

Sous requête

Une sous-requête est une requête à l’intérieur d’une autre requête. Avec le SQL, vous pouvez construire des requêtes imbriquées sur autant de niveaux que vous voulez. Une sous-requête peut être effectuée dans une requête de type SELECT, INSERT, UPDATE ou DELETE.

• IN: Condition vraie si attribut ∈ {valeurs renvoyées par sous requête }.
• ANY: Comparaison vraie si vraie pour au moins 1 des valeurs ∈ {valeurs renvoyées par sous requête }.
• ALL: Comparaison vraie si vraie pour chacune des valeurs ∈ {valeurs renvoyées par sous requête }.

Pour illustrer ceci nous allons imaginer une base de données d’animaux pour un cabinet de vétérinaires.

SELECT id, nom FROM animal
WHERE id IN (
    SELECT DISTINCT id FROM animal_mort
    WHERE name LIKE 'L%';
);

On récupère ici tous les animaux morts dont le nom commence par un L.

SELECT id, nom FROM animal
WHERE id NOT IN (
    SELECT DISTINCT id FROM animal_mort
);

On récupère ici tous les animaux encore en vie.

SELECT id, nom FROM animal
WHERE poids > ANY (
    SELECT poids FROM animal WHERE couleur = 'grise'
);

On récupère tous les animaux, dont le poids est supérieur aux poids des animaux dont la couleur est grise.

SELECT id, nom FROM animal
WHERE poids > ALL (
    SELECT poids FROM animal WHERE couleur = 'grise'
)
ORDER BY poids;

On récupère tous les animaux, dont le poids est supérieur aux poids de tous les animaux dont la couleur est grise.

Fonctions d’agrégats

Les fonctions d’agrégation en SQL permettent d’effectuer des opérations statistiques sur un ensemble de données.

Nom	Effet
AVG()	permet de calculer la moyenne sur un ensemble d’enregistrements
COUNT()	permet de compter le nombre d’enregistrements sur une table
MAX()	pour récupérer la valeur maximum d’une colonne
MIN()	pour récupérer la valeur minimale d’une colonne
SUM()	pour calculer la somme sur un ensemble d’enregistrements

Ces fonctions sont assez simples à utiliser, comme ce sont des fonctions statistiques sur un ensemble de lignes d’une table ou d’une colonne, voici quelques exemples :

// Permet d'obtenir le prix minimum et maximum d'une table appelée article
SELECT MIN(prix), MAX(prix) FROM article;

// Permet de compter le nombre d'utilisateurs de notre table user
SELECT COUNT(*) FROM user

GROUP BY

La notion de fonction d’agrégation prend tout son sens avec l’instruction GROUP BY. En effet, il est possible de regrouper les enregistrements selon un ou plusieurs critères et connaître, en une seule requête, quelques-unes de ces données statistiques en fonction de ces critères. Comme, je dis toujours un exemple vaut mieux que 1000 mots.

Nous possédons un magasin avec une table article contenant les produits suivants :

Id de l’article	Nom de l’article	Id de la catégorie de l’article
1	Pomme	1
2	Orange	1
3	Banane	1
4	Tome 1: radiant	2
5	Tome 1: black lagoon	2
6	Kleenex	NULL

SELECT category_id, COUNT(*) AS 'Nombre de produits par catégorie'
FROM article
WHERE category_id IS NOT NULL
GROUP BY category_id
ORDER BY category_id;

Dans cet exemple, on utilise la fonction COUNT afin de calculer le nombre d’articles disponibles pour chaque catégorie de notre magasin. On exclut les catégories qui sont nulles grâce au WHERE. Comme le champ catégorie id n’est pas encapsulé par la fonction COUNT, nous devons le rajouter dans la clause du GROUP BY. On obtient alors le résultat suivant :

Id de la catégorie	Nombre de produits
1	3
2	2

HAVING COUNT

La clause HAVING COUNT a été rajouté au SQL car le mot-clé WHERE ne pouvait pas être utilisé avec les fonctions d’agrégations. Par exemple, si on ajoutait un champ pays à notre liste d’utilisateurs, pour connaître les pays qui ont au moins 5 utilisateurs dans notre BDD, on ferait quelque chose du genre :

SELECT COUNT(id), name, email, country
FROM user
GROUP BY country
HAVING COUNT(id) > 5;

Les séquences

Les séquences permettent de définir une suite de nombres entiers. L’évolution de cette suite est régie par un certain nombre de paramètres. Ceci peut permettre de :

• Générer des valeurs de clé primaire
• Coordonner les valeurs de clé dans plusieurs lignes ou tables
• Se crée un compteur, que l’on incrémente quand on veut

CREATE SEQUENCE seq_user
START WITH 1
INCREMENT BY 1
MAXVALUE 9000;

On crée une séquence appelée seq_user, dont la valeur commence à 1 et qui s’incrémente de 1 après l’appel de la fonction NEXTVAL(), avec comme valeur maximum 9000.

SELECT NEXTVAL('seq_user'); 

Cette requête retournera donc la prochaine valeur de la séquence. On pourrait ainsi utiliser cette séquence pour nos insertions en base de données et connaître à tout moment l’id courant de la table user.

Views

On peut considérer les vues comme des tables virtuelles définies par une requête. Sachant que les données ne sont pas stockées physiquement.

Intérêts :

• Réponse aux besoins de confidentialité
• Facilité pour les utilisateurs dans la manipulation de données (requêtes complexes)
• Sauvegarde des requêtes dans le dictionnaire de données

Prenons un exemple :

CREATE VIEW clients_calvados AS SELECT prenom, nom, lieu_dit, cp, ville FROM Client WHERE cp IN (
    SELECT cp FROM Client WHERE cp = 14
);

On construit notre vue, comme étant l’ensemble des clients venant du Calvados. On peut ensuite effectuer des requêtes classiques sur notre vue, comme par exemple trier par nom.

SELECT * FROM clients_calvados WHERE nom LIKE 'A%';

Index

Un index est objet optionnel associé à une table, vue ou cluster qui a pour objectif d’accélérer certaines recherches.

Partons d’une requête assez simple que l’on réalise tous assez souvent :

SELECT * FROM user WHERE nom ='durand';

On balaye toute la table afin de retrouver la ligne dont le pseudo vaut ‘durand’, le temps de réponse peut vite devenir prohibitifs pour une grosse table (généralement à partir de plus de 100 lignes). Une solution pour accélérer ce genre de requête, la création d’un index.

CREATE INDEX idx_user ON user(nom);

Attention, les index sont à utiliser avec parcimonie, en effet les index occupent de l’espace supplémentaire en BDD. De plus l’insertion et la mise à jour sont plus longues (les index sont mis également à jour). Bien utiliser les index permet de faire une vraie différence ne les négligeait pas ! Pour en savoir plus, voici un tutoriel complet.

Utiliser un logiciel pour créer sa base de données

Enfin, voici le logiciel MySQL Workbench proposé par Oracle. Il permet de gérer vos bases de données grâce à une interface graphique assez intuitive. Il permet de lier directement sa modélisation à sa base de données et ainsi créer automatiquement vos tables. Vous pouvez même également modifier les données ou encore les utilisateurs. Voici un petit tutoriel venant tout droit de la documentation qui vous présentera très bien l’outil ! Petit bonus, le logiciel est disponible sous Windows, Mac et surtout Linux <3