Ce cours sur le SQL a pour but de donner des bases pour quiconque voudrait avoir une vision globale sur ce sujet. Il permet d’avoir une vision globale sur comment lire une base de données et faire des requêtes. Cet article est le dernier d’une série de 5 articles.
Ce cours est la suite de la partie 4, il va présenter les fonctions avancées. Si vous débutez totalement en SQL, commencez par la partie 1.
Opérateurs mathématiques
La plupart des opérations classiques fonctionnent. On va donc avoir + pour l’addition, – pour la soustraction, x pour la multiplication, / pour la division. Pour la division, attention à la division d’entier, il est préférable de d’abord convertir en FLOAT pour être sûr du comportement.
- On peut ajouter entre elles 2 colonnes : colonne_1 + colonne_2
- On peut ajouter/diviser/soustraite/multiplier une colonne à un nombre : colonne_1 + 12
Liste non exhaustive de fonctions mathématiques
Voici une liste (non exhaustive) de fonctions mathématiques :
- FLOOR permet de prendre la partie entière d’un nombre. Exemple : FLOOR(41,33) = 41
- POWER (ou POW selon le SGBDR) permet de calculer la puissance d’un nombre : POW(a,b) = a^b. Exemple : POW(3,2) = 9
- PI() donne la valeur de PI.
- SQRT permet de calculer la racine carrée d’un nombre. Exemple : SQRT(4) = 2
Pour avoir une liste plus complète de fonctions mathématiques : https://sql.sh/fonctions/mathematiques
Entrainement
Je l’avais promis à la fin de la partie 4. Voici le dernier entrainement :
https://sqlbolt.com/lesson/select_queries_with_expressions
Fonctions mathématiques avancées
oici deux fonctions mathématiques plus compliquées :
- ROUND permet d’arrondir un nombre.
La fonction prend en entrée 2 arguments, le nombre et le nombre de chiffres après la virgule que l’on souhaite garder. Ce nombre peut être négatif.
Exemple :- ROUND(41.654,2) = 41.65
- ROUND(41.654,0) = 42
- ROUND(41.654,-1) = 40
- RAND() permet d’obtenir un nombre aléatoire entre 0 et 1 selon la loi uniforme.
Exemple :- RAND() : 0.6011546427394773
- RAND() : 0.2825456742755674
L’opérateur CASE
Cette fonction est bien pratique et permet de faire des transformations. Par exemple, on veut regrouper en trois catégories les personnes. On dispose de leur taille en centimètres. On fait 3 catégories : Petit, moyen et grand. La fonction CASE permet cela.
SELECT
CASE
WHEN size < 160 THEN ‘Petit’
WHEN size BETWEEN 160 and 180 THEN ‘Moyen’
ELSE ‘Grand’
END AS groupe_taille
FROM
table
Chiffrement
On peut même encoder des morceaux de texte pour les rendre indéchiffrables. La fonction MD5 permet cela :
- MD5(‘valentin’) = dee484ff7366319331b0d36e9d0958c1
- MD5(‘Valentin’) = 76bf1508c054395f67a605468d76c22f
Vous allez donc pouvoir par exemple encrypter un mot de passe. Si quelqu’un tape un mot de passe, on calcule son MD5 et comme il est unique, on vérifie que le mot de passe est bon.
Opérateurs de date
Le SQL présente diverses fonctions de date. On peut faire ces opérations :
- Changer de format de date
- Calculer la différence entre deux dates
- Calculer de manière automatique la date du jour
- Changer des heures en seconde
- Donner le jour correspondant à une date
Voici la liste de toutes les fonctions de date répertoriées sur SQL.sh : https://sql.sh/fonctions/date-heure
Changer le format d’une date
Supposons qu’on ait un champ date. Son format de base est “YYYY-MM-JJ”. Si on veut changer le format de cette date, on utilise la fonction DATE_FORMAT(date,format). On a divers formats possibles :
- %M permet par exemple de récupérer le nom du mois (January, …, December)
- %m permet de récupérer le mois en chiffre (01 .. 12)
- %T permet de récupérer l’heure au format 24 heures (hh:mm:ss)
- %a permet de récupérer le nom du jour de la semaine abrégé (Sun, Mon, … Sat)
- %Y permet d’obtenir l’année en 4 chiffres (2021)
Voici quelques exemples :
- FORMAT_DATE(“2021-05-17”, “%D %b %Y”) = “17th May 2021”
- FORMAT_DATE(“2021-05-17 10:21:20”, “%H:%i:%s”) = “10:21:20”
- FORMAT_DATE(“2021-05-17”, “%W %M %e %Y”) = “Monday May 17 2021”
Calculer la différence entre deux dates
Supposons qu’on veuille calculer la différence entre le 3 mars et le 25 février. Si on devait créer soi-même la fonction ce serait compliqué, notamment à cause de la gestion des longueurs différentes des mois, des années bissextiles..
Pour cela, on utilise la fonction DATEDIFF. Son utilisation va dépendre du SGBDR.
- En MySQL : DATEDIFF(‘2021-05-18’, ‘2021-05-17’) va retourner 1
- En SQL Server : DATEDIFF(day,’2021-05-18’,2021-05-17’) va retourner -1
Aujourd’hui nous sommes quel jour ?
Souvent, on a besoin de la différence entre la date du jour et une date en base. La fonction NOW() permet de récupérer la date du jour. Exemple de sortie possible :
SELECT NOW()
On obtient alors par exemple : ‘2021-09-26 15:40:41’
Voici aussi une autre fonction potentiellement utile : SEC-TO-TIME() qui permet de transformer un nombre de secondes en heures, minutes, secondes : https://sql.sh/fonctions/sec_to_time
Opérateurs de chaînes de caractère
On peut faire plusieurs manipulations sur les chaînes de caractère :
- Concaténer (ajouter à la suite) plusieurs colonnes
- Enlever ou remplacer certaines lettres
- Tout mettre en majuscules / minuscules
- Trouver des sous-chaînes de caractères
Cette liste est bien sûr non exhaustive.
Concaténer
La fonction CONCAT() permet de concaténer 2 (ou plus) chaînes de caractères, c’est à dire les accoler à la suite. CONCAT(‘Jean’,’DUPONT’) donne pour résultat ‘JeanDUPONT’. Donc on va naturellement rajouter un espace. CONCAT(“Jean”,“ ”,“DUPONT”) pour avoir cette fois “Jean DUPONT”
MAJUSCULE to minuscule
La fonction LCASE() pour LOWERCASE() permet de tout mettre en minuscule. LCASE(‘DUPONT’)= ‘dupont’.
A l’inverse, UCASE permet de transformer tout en majuscule. UCASE(‘Dupont’) = ‘DUPONT’
LENGTH / LEFT / RIGHT
La fonction LENGTH() (ou LEN) permet de connaître la longueur d’une chaîne de caractères. LEN(‘Dupont’) = 6
LEFT(chaine,longueur) permet d’extraire une chaîne de caractères d’une longueur donnée, en démarrant de la gauche. RIGHT c’est la même chose mais en démarrant de la droite.
- LEFT(‘Dupont’,2) = ‘Du’
- RIGHT(‘Dupont’,2) = ‘nt’
L’opérateur CAST : Changer de format
Vous avez une chaîne de caractères mais qui en fait représente une date ? La fonction CAST permet de mettre au bon format :
CAST(colonne as format)
Exemple : CAST(‘2021-05-17’ as DATE)
Cet opérateur renvoie null si la transformation n’est pas possible
Exercice : Quelles sont les changements de format possibles ?
L’opérateur HAVING
Cet opérateur aAgit comme un WHERE mais après des fonctions d’agrégation.
Exemple : On veut les villes des gens ayant un salaire moyen supérieur à 30000 euros annuel.
SELECT
ville,
AVG(salaire) as salaire_moyen
FROM
table
GROUP BY
ville
HAVING
salaire_moyen > 30000
Les sous-requêtes
Voici une notion bien utile pour conclure : Les sous-requêtes. Elles permettent de faire des premiers calculs avant de par exemple faire une jointure.
Exemple 1
SELECT
*
FROM
`table`
WHERE
`nom_colonne` IN (
SELECT `colonne`
FROM `table2`
WHERE `cle_etrangere` = 36
)
Exemple 2
SELECT
*
FROM
(SELECT
fonction1(colonne1),
fonction2(colonne2),
colonne3)
FROM
`table`)
LEFT JOIN
(SELECT
fonction(colonne3),
colonne4
FROM
table2)
ON colonne3 = colonne4
Ordre d’exécution des requêtes
Pour terminer, voici un peu de connaissance sur l’ordre dans lequel les requêtes s’exécutent en SQL. L’ordre de résolution standard d’une requête SQL est :
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
- LIMIT
Et voilà, ce cours de SQL en 5 parties est terminé. N’hésitez pas à faire vos retours dans les commentaires et à signaler d’éventuelles erreurs.