260 lines
6.6 KiB
Markdown
260 lines
6.6 KiB
Markdown
---
|
|
title: "Base de données avancées : Le langage SQL"
|
|
date: 2022-02-11
|
|
tags: ["schema", "relation", "SQL"]
|
|
categories: ["Base de données avancées", "Cours"]
|
|
---
|
|
|
|
SQL, pour *Structured Query Langage* est un langage de programmation servant à
|
|
interagir avec des bases de données relationelles. Il regroupe plusieurs
|
|
fonctionnalités :
|
|
|
|
* Définition des données : créations des relations appelées ici les *tables*
|
|
* Manipulation des données : interrogation, insertion, supression et mise à
|
|
jour des données contenues dans les tables
|
|
* Contrôle des données : permissions, contrôle d'intégrités.
|
|
|
|
L'usage veut que les mots clés du langage soit écrits en majuscule, mais ce
|
|
n'est qu'un usage.
|
|
|
|
Une requête écrite en *SQL* se termine par un point-virgule. Il est aussi
|
|
possible d'ajouter des commentaires en commençant la ligne avec `--`.
|
|
|
|
## La projection
|
|
|
|
Elle s'exprime avec la command `SELECT`, Il est possible de supprimer les
|
|
doublons avec le `DISTINCT`. il est assi possible d'afficher tous les attributs
|
|
d'une tables avec `*`.
|
|
|
|
```sql
|
|
-- afficher les noms prénom des cyclistes
|
|
SELECT nom, prenom
|
|
FROM cyclistes;
|
|
```
|
|
|
|
```sql
|
|
-- afficher tous les attributs de la table cyclistes
|
|
SELECT *
|
|
FROM cyclistes
|
|
```
|
|
|
|
```sql
|
|
-- Afficher les couleurs de vélo disponibles
|
|
SELECT DISTINCT couleur
|
|
FROM velos;
|
|
```
|
|
|
|
## La selection
|
|
|
|
Elle s'exprime avec la clause `WHERE`. Il est possible d'utiliser des opérateurs
|
|
de comparaison (<, >, !=, <=, >=) ou des connecteurs (`not`, `and`, `or`). Il
|
|
est aussi possible de founir un intervale comme prédicat avec `BETWEEN`.
|
|
|
|
Il est possible d'utiliser `%` pour remplacer un nombre indéfini de caractères
|
|
et `_` pour en remplacer un seul.
|
|
|
|
```sql
|
|
-- Afficher le modèle et la taille des vélos bleus Gitanes
|
|
SELECT modele, taille
|
|
FROM velos
|
|
WHERE marque = 'Gitane'
|
|
AND couleur = 'bleu`;
|
|
```
|
|
|
|
```sql
|
|
-- Afficher les marques, modèles et couleurs des vélos de plus de 26 pouces
|
|
SELECT marque, couleur
|
|
FROM velos
|
|
WHERE taille > 26;
|
|
```
|
|
|
|
## Les operation binaires
|
|
|
|
Il est bon de rapeller que les opération binaires se font que sur la base de
|
|
relations compatibles. On en dénombre 3 en SQL :
|
|
|
|
* l'union avec `UNION`
|
|
* l'intersection avec `INTERSECT`
|
|
* la différence avec `MINUS` (Oracle) ou `EXCEPT` (PostgreSQL). Il n'y pas
|
|
d'opérateur de différence dans MySQL.
|
|
|
|
```sql
|
|
-- afficher le modèle des vélo de marque Gitane et Motobécane
|
|
SELECT marque, modele
|
|
FROM velos
|
|
WHERE marque = 'Gitane'
|
|
UNION
|
|
SELECT marque, modele
|
|
FROM velos
|
|
WHERE marque = 'Motobécane';
|
|
```
|
|
|
|
```sql
|
|
-- afficher les vélo rouge à 6 vitesses
|
|
SELECT marque, modele
|
|
FROM velos
|
|
WHERE vitesses = 6
|
|
INTERSECT
|
|
SELECT marque, modele
|
|
FROM velos
|
|
WHERE couleur = 'rouge';
|
|
```
|
|
|
|
## Les jointures
|
|
|
|
Pour acceder aux attributs commun de deux relation, il nous faut utiliser la
|
|
notation pointée.
|
|
|
|
Il existe deux syntaxe possible pour exprimer des jointures naturelles : une
|
|
ancienne via une clause `WHERE` et une autre pus moderne via le mot clé `JOIN`.
|
|
|
|
```sql
|
|
-- nouvelle sytaxe
|
|
SELECT date, description , marque, modele
|
|
FROM velos
|
|
JOIN repatation ON reparations.code_velo = velos.code_velo
|
|
WHERE marque = 'Gitane';
|
|
|
|
-- ancienne syntaxe
|
|
SELECT date, description , marque, modele
|
|
FROM velos, reparations
|
|
WHERE reparations.code_velo = velos.code_velo
|
|
AND marque = 'Gitane';
|
|
```
|
|
|
|
Pour simplifier l'écriture des clause `WHERE` et `AND`, il est possible
|
|
d'assigner des alias pour nos tables.
|
|
|
|
```sql
|
|
|
|
SELECT date, description , marque, modele
|
|
FROM velos AS v
|
|
JOIN repatation AS r ON r.code_velo = v.code_velo
|
|
WHERE marque = 'Gitane';
|
|
```
|
|
|
|
Cette notation est très utile dans le cadre des auto-jointures.
|
|
|
|
## opérateur d'appartenance
|
|
|
|
Permet de tester si un élément appartient (ou non) à un ensemble.
|
|
|
|
```sql
|
|
-- afficher les cycliste ayant un velo 6 vitesses Motobécane
|
|
SELECT nom, prenom
|
|
FROM cyclistes
|
|
WHERE velo_id IN (
|
|
SELECT velo_id
|
|
FROM velos
|
|
WHERE vitesses = 6
|
|
AND marque = 'Motobécane'
|
|
);
|
|
```
|
|
|
|
Il est posible d'exprimer la négation avec le mot clé `NOT`
|
|
|
|
## opérateur d'existance
|
|
|
|
La requête s'exécute si celle imbriqué dans le `EXIST` retourne une valeur.
|
|
comme pour `IN`, il est possible d'exprimer la négation avec `NOT`.
|
|
|
|
```sql
|
|
-- trouver les marques vélos qui on servi lors des course de Trouville
|
|
SELECT DISTINCT marques
|
|
FROM velos
|
|
WHERE velos_id IN (
|
|
SELECT velo_id
|
|
FROM course
|
|
WHERE ville = 'Trouville'
|
|
);
|
|
|
|
```
|
|
|
|
## les quantificateurs
|
|
|
|
Ils permettent de réaliser une comparaison entre ne attribut et un ensemble de
|
|
valeurs. Em plus de l'opérateur de compataison, il faut indiquer si l;on compare
|
|
sur n'importe quelle valeur (`any`) ou toutes les valeurs (`all`).
|
|
|
|
```sql
|
|
-- trouver les vélos qui ont plus de vitesse que tous les VTT
|
|
SELECT marque, modele
|
|
FROM velos,
|
|
WHERE vitesse >all(
|
|
SELECT vitesses
|
|
FROM velos
|
|
WHERE type = 'VTT'
|
|
);
|
|
```
|
|
|
|
## Les opérateurs arithmétiques et ensemblistes
|
|
|
|
Les opérateurs arithmétiques et ensemblistes se positionnent dans les clauses
|
|
`SELECT` ou `WHERE`. Les opérateurs arithmétiques comprennnent l'addition, la
|
|
soustraction, la multiplication et la division. Les fonctions arithmériques
|
|
comprennent (entre autres) la valeur absolue `ABS`, l'exponentielle `EXP`, la
|
|
racine carré `SQRT` etc.
|
|
|
|
Les fonctions ensemblistes comprennent (entre autres) le comptage `COUNT`, les
|
|
la valeur minimale `MIN` et maximale `MAX`, la somme `SUM`, la moyenne `AVG`
|
|
etc.
|
|
|
|
```sql
|
|
-- compter le nombre de vélos au total
|
|
SELECT COUNT(*)
|
|
FROM velos;
|
|
|
|
-- prix moyen calculé sur l'ensemble vélos
|
|
SELECT AVR(prix)
|
|
FROM velos;
|
|
```
|
|
|
|
## Opérateurs de groupes
|
|
|
|
Afin de partitionner les résutats d'une fonction ensembliste (aussi appelée
|
|
fonction d'aggrégation). il est possible d'utiliser l'opérateur `GROUP BY`.
|
|
|
|
```sql
|
|
-- coumpter le nombre de modèle disponible pour chaque marque
|
|
SELECT marque COUNT(modele)
|
|
FROM velo
|
|
GROUP BY marque;
|
|
```
|
|
|
|
Il est possible d'appliquer une selection sur une fonction d'aggrégation avec la
|
|
clause `HAVING` :
|
|
|
|
```sql
|
|
-- afficher les marque ayant plus de 3 modèles disponibles
|
|
SELECT marque
|
|
FROM velos
|
|
HAVING COUNT(modele) > 3;
|
|
```
|
|
## classement des résultats
|
|
|
|
Avec la clause `ORDER BY`, il est possible de classer les résultats d'une
|
|
requête en fonction d'une colonne. Il est possible de définir un tri croissant
|
|
avec `ASC` ou décroissant avec `DESC`:
|
|
|
|
```sql
|
|
SELECT modele, prix
|
|
FROM velo
|
|
WHERE marque = 'Gitane'
|
|
ORDER BY prix ASC;
|
|
```
|
|
|
|
## La division en SQL
|
|
|
|
Pour rappel, la division permet, par exemple, de trouver les cyclistes qui ont
|
|
participé à toutes les courses.
|
|
|
|
Il n'existe pas d'opérateur de division en SQL, mais il est poussible d'utiliser
|
|
`HAVING` :
|
|
|
|
```sql
|
|
SELECT id_cycliste, nom, prenom
|
|
FROM cyclistes
|
|
JOIN courses USING(id_cycliste)
|
|
GROUP BY id_cycliste, nom, prenom
|
|
HAVING COUNT(id_course) = (SELECT COUNT(*) FROM courses);
|
|
```
|