Tout ce qu’il faut savoir en SQL – Partie 5 : Les fonctions avancées

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  :

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. 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.