Fonctions et opérateurs SQL
2024 - 2025
Positionnement dans BDW
Ces diapositives utilisent le genre féminin (e.g., chercheuse, développeuses) plutôt
que l’écriture inclusive (moins accessible, moins concise, et pas totalement inclusive)
2 / 23
Pourquoi des fonctions et opérateurs ?
I Manipuler les chaînes de caractères (e.g., recherche de
sous-chaine, concaténation)
I Réaliser des calculs mathématiques (e.g., somme, division,
moyenne)
I Manipuler des dates (e.g., différence entre dates, formatage)
I Convertir entre types de données (e.g., caractère vers entier)
Se référer à la documentation spécifique au SGBD utilisé !
[Link]
3 / 23
Plan
Opérateurs
Fonctions chaînes
Fonctions numériques
Fonctions date
Fonctions diverses
Opérateurs Fonctions chaînes Fonctions numériques Fonctions date Fonctions diverses
Opérateurs arithmétiques
I + (addition)
I - (soustraction)
I * (multiplication)
I / division (4 chiffres après la virgule par défaut)
I div (division entière)
I mod ou % (modulo, i.e., reste de la division entière)
I …
[Link]
5 / 23
Opérateurs Fonctions chaînes Fonctions numériques Fonctions date Fonctions diverses
Exemple d’opérateur arithmétique
idE nomE moyenneLycée effectifLycée idE nomU département décision
123 Ana 19.5 1000 123 INSA informatique O
234 Bob 18 1500 123 UCB électronique N
345 Chloé 17.5 500 123 UCB informatique O
456 Damien 19.5 1000 123 UJM électronique O
543 Chloé 17 2000 234 INSA biologie N
567 Éléonore 14.5 2000 345 UJF bioinformatique O
654 Ana 19.5 1000 345 UJM bioinformatique N
678 Farid 19 200 345 UJM électronique N
765 Joana 14.5 1500 345 UJM informatique O
789 Gisèle 17 800 543 UJF informatique N
876 Irène 19.5 400 678 UCB histoire O
898 Hector 18.5 800 765 UCB histoire O
765 UJM histoire N
Table Élève 765 UJM psychologie O
876 UCB informatique N
nomU ville effectif 876 UJF biologie O
INSA Lyon 36000 876 UJF biologie marine N
UCB Lyon 15000 898 INSA informatique O
UJF Grenoble 10000 898 UCB informatique O
UJM Saint-Étienne 21000
Table Candidature
Table Université
La moyenne des élèves sur 100
idE moyenne100
123 97.5
SELECT idE, moyenneLycee * 5 AS moyenne100 234 90
FROM Eleve; 345 87.5
456 97.5
… …
6 / 23
Opérateurs Fonctions chaînes Fonctions numériques Fonctions date Fonctions diverses
Opérateurs de comparaison
Comparaison de deux expressions selon leur ordre naturel :
I e1 Θ e2 avec Θ un opérateur parmi {=, !=, <, >, ≥, ≤}
I [not] between … and teste une valeur dans un intervalle
I is [not] null teste une valeur nulle
I [not] in teste si une valeur se trouve dans une liste
I is [not] true, is [not] false, et is [not] unknown
I …
[Link]
Fonctions et opérateurs SQL 7 / 23
Opérateurs Fonctions chaînes Fonctions numériques Fonctions date Fonctions diverses
Exemple de comparaison
idE nomE moyenneLycée effectifLycée idE nomU département décision
123 Ana 19.5 1000 123 INSA informatique O
234 Bob 18 1500 123 UCB électronique N
345 Chloé 17.5 500 123 UCB informatique O
456 Damien 19.5 1000 123 UJM électronique O
543 Chloé 17 2000 234 INSA biologie N
567 Éléonore 14.5 2000 345 UJF bioinformatique O
654 Ana 19.5 1000 345 UJM bioinformatique N
678 Farid 19 200 345 UJM électronique N
765 Joana 14.5 1500 345 UJM informatique O
789 Gisèle 17 800 543 UJF informatique N
876 Irène 19.5 400 678 UCB histoire O
898 Hector 18.5 800 765 UCB histoire O
765 UJM histoire N
Table Élève 765 UJM psychologie O
876 UCB informatique N
nomU ville effectif 876 UJF biologie O
INSA Lyon 36000 876 UJF biologie marine N
UCB Lyon 15000 898 INSA informatique O
UJF Grenoble 10000 898 UCB informatique O
UJM Saint-Étienne 21000
Table Candidature
Table Université
Les universités dont le nom diffère de 'UCB'
nomU
SELECT nomU
INSA
FROM Universite
UJF
WHERE nomU != 'UCB'; UJM
8 / 23
Plan
Opérateurs
Fonctions chaînes
Fonctions numériques
Fonctions date
Fonctions diverses
Opérateurs Fonctions chaînes Fonctions numériques Fonctions date Fonctions diverses
Fonctions chaînes de caractères
I concat(s1 , s2 , …) concatène les chaînes s1 , s2 , …
I length(s) retourne la longueur de la chaîne s
I substr(s, pos, len) extrait de s la sous-chaîne qui début à
l’index pos et de longueur len
I replace(s, old, new) remplace dans s toutes les occurrences
de la chaîne old par la chaîne new
I …
[Link]
Opérateurs Fonctions chaînes Fonctions numériques Fonctions date Fonctions diverses
Exemple de fonctions chaînes de caractères
idE nomE moyenneLycée effectifLycée idE nomU département décision
123 Ana 19.5 1000 123 INSA informatique O
234 Bob 18 1500 123 UCB électronique N
345 Chloé 17.5 500 123 UCB informatique O
456 Damien 19.5 1000 123 UJM électronique O
543 Chloé 17 2000 234 INSA biologie N
567 Éléonore 14.5 2000 345 UJF bioinformatique O
654 Ana 19.5 1000 345 UJM bioinformatique N
678 Farid 19 200 345 UJM électronique N
765 Joana 14.5 1500 345 UJM informatique O
789 Gisèle 17 800 543 UJF informatique N
876 Irène 19.5 400 678 UCB histoire O
898 Hector 18.5 800 765 UCB histoire O
765 UJM histoire N
Table Élève 765 UJM psychologie O
876 UCB informatique N
nomU ville effectif 876 UJF biologie O
INSA Lyon 36000 876 UJF biologie marine N
UCB Lyon 15000 898 INSA informatique O
UJF Grenoble 10000 898 UCB informatique O
UJM Saint-Étienne 21000
Table Candidature
Table Université
Le nom et la ville des universités, séparés par un tiret
nomville
SELECT CONCAT(nomU, '-', ville) AS nomville INSA-Lyon
UCB-Lyon
FROM Universite;
UJF-Grenoble
UJM-Saint-Étienne
11 / 23
Opérateurs Fonctions chaînes Fonctions numériques Fonctions date Fonctions diverses
Comparaison approximative
I s like pat compare approximativement la chaîne s selon le
pattern pat (ilike pour la version insensible à la casse)
I Deux caractères joker pour le pattern pat :
I '_' pour un caractère alphanumérique quelconque
I '%' pour une chaîne alphanumérique quelconque
I Fonctions plus complexes avec des expression régulières
Exemples de patterns :
'a_ _' ≡ toutes les chaînes de 3 caractères commençant par un 'a'
'a%' ≡ toutes les chaînes commençant par un 'a'
'_n_' ≡ toutes les chaînes de 3 caractères avec un 'n' en deuxième
position
[Link]
Opérateurs Fonctions chaînes Fonctions numériques Fonctions date Fonctions diverses
Exemple de comparaison approximative
idE nomE moyenneLycée effectifLycée idE nomU département décision
123 Ana 19.5 1000 123 INSA informatique O
234 Bob 18 1500 123 UCB électronique N
345 Chloé 17.5 500 123 UCB informatique O
456 Damien 19.5 1000 123 UJM électronique O
543 Chloé 17 2000 234 INSA biologie N
567 Éléonore 14.5 2000 345 UJF bioinformatique O
654 Ana 19.5 1000 345 UJM bioinformatique N
678 Farid 19 200 345 UJM électronique N
765 Joana 14.5 1500 345 UJM informatique O
789 Gisèle 17 800 543 UJF informatique N
876 Irène 19.5 400 678 UCB histoire O
898 Hector 18.5 800 765 UCB histoire O
765 UJM histoire N
Table Élève 765 UJM psychologie O
876 UCB informatique N
nomU ville effectif 876 UJF biologie O
INSA Lyon 36000 876 UJF biologie marine N
UCB Lyon 15000 898 INSA informatique O
UJF Grenoble 10000 898 UCB informatique O
UJM Saint-Étienne 21000
Table Candidature
Table Université
Le nom des élèves qui contient un 'e'
nomE
Chloé
SELECT nomE Hector
Damien
FROM Eleve Éleonore
WHERE nomE LIKE '%e%'; Gisèle
Chloé
Irène
Opérateurs Fonctions chaînes Fonctions numériques Fonctions date Fonctions diverses
Exemple de comparaison approximative (2)
idE nomE moyenneLycée effectifLycée idE nomU département décision
123 Ana 19.5 1000 123 INSA informatique O
234 Bob 18 1500 123 UCB électronique N
345 Chloé 17.5 500 123 UCB informatique O
456 Damien 19.5 1000 123 UJM électronique O
543 Chloé 17 2000 234 INSA biologie N
567 Éléonore 14.5 2000 345 UJF bioinformatique O
654 Ana 19.5 1000 345 UJM bioinformatique N
678 Farid 19 200 345 UJM électronique N
765 Joana 14.5 1500 345 UJM informatique O
789 Gisèle 17 800 543 UJF informatique N
876 Irène 19.5 400 678 UCB histoire O
898 Hector 18.5 800 765 UCB histoire O
765 UJM histoire N
Table Élève 765 UJM psychologie O
876 UCB informatique N
nomU ville effectif 876 UJF biologie O
INSA Lyon 36000 876 UJF biologie marine N
UCB Lyon 15000 898 INSA informatique O
UJF Grenoble 10000 898 UCB informatique O
UJM Saint-Étienne 21000
Table Candidature
Table Université
Le nom et identifiant des élèves qui ont candidaté dans une université
avec un nom de trois lettres
[Link] nomE
SELECT DISTINCT [Link], nomE 123 Ana
FROM Eleve e NATURAL JOIN Candidature 345 Chloé
543 Chloé
WHERE nomU IN (SELECT nomU 678 Farid
FROM Universite 765 Joana
876 Irène
WHERE nomU LIKE '___'); 898 Hector
Plan
Opérateurs
Fonctions chaînes
Fonctions numériques
Fonctions date
Fonctions diverses
Opérateurs Fonctions chaînes Fonctions numériques Fonctions date Fonctions diverses
Fonctions numériques
I abs(e), cos(e), sin(e), log2(e), sqrt(e), …
I rand() retourne une nombre aléatoire dans [0, 1]
I round(n, dec) arrondit le nombre n à dec décimales (dec
peut être négatif pour arrondir la partie entière)
I trunc(n, dec) tronque le nombre n à dec décimales
I …
[Link]
Opérateurs Fonctions chaînes Fonctions numériques Fonctions date Fonctions diverses
Exemple de fonction numérique
idE nomE moyenneLycée effectifLycée idE nomU département décision
123 Ana 19.5 1000 123 INSA informatique O
234 Bob 18 1500 123 UCB électronique N
345 Chloé 17.5 500 123 UCB informatique O
456 Damien 19.5 1000 123 UJM électronique O
543 Chloé 17 2000 234 INSA biologie N
567 Éléonore 14.5 2000 345 UJF bioinformatique O
654 Ana 19.5 1000 345 UJM bioinformatique N
678 Farid 19 200 345 UJM électronique N
765 Joana 14.5 1500 345 UJM informatique O
789 Gisèle 17 800 543 UJF informatique N
876 Irène 19.5 400 678 UCB histoire O
898 Hector 18.5 800 765 UCB histoire O
765 UJM histoire N
Table Élève 765 UJM psychologie O
876 UCB informatique N
nomU ville effectif 876 UJF biologie O
INSA Lyon 36000 876 UJF biologie marine N
UCB Lyon 15000 898 INSA informatique O
UJF Grenoble 10000 898 UCB informatique O
UJM Saint-Étienne 21000
Table Candidature
Table Université
La moyenne des élèves sur 10, arrondie à l’entier
idE moyenne10
SELECT idE, round(CAST(moyenneLycee / 2 AS 123 10
234 9
,→ NUMERIC), 0) AS moyenne10 345 9
FROM Eleve; 456 10
… …
Plan
Opérateurs
Fonctions chaînes
Fonctions numériques
Fonctions date
Fonctions diverses
Opérateurs Fonctions chaînes Fonctions numériques Fonctions date Fonctions diverses
Fonctions sur les dates/temps
I currenttimestamp et now() retournent la date et temps
courants (format 'YYYY-MM-DD HH:MM:[Link]')
I currentdate retourne la date courante (format
'YYYY-MM-DD')
I extract(champ from d) extrait le champ donné (year,
day, second, …) d’une date d
I to_date(d, f) retourne la date d au format f
I d1 – d2 retourne le nombre de jours entre les dates d1 et d2
I …
[Link]
[Link]
Opérateurs Fonctions chaînes Fonctions numériques Fonctions date Fonctions diverses
Exemple de fonction date
idE nomE moyenneLycée effectifLycée idE nomU département décision
123 Ana 19.5 1000 123 INSA informatique O
234 Bob 18 1500 123 UCB électronique N
345 Chloé 17.5 500 123 UCB informatique O
456 Damien 19.5 1000 123 UJM électronique O
543 Chloé 17 2000 234 INSA biologie N
567 Éléonore 14.5 2000 345 UJF bioinformatique O
654 Ana 19.5 1000 345 UJM bioinformatique N
678 Farid 19 200 345 UJM électronique N
765 Joana 14.5 1500 345 UJM informatique O
789 Gisèle 17 800 543 UJF informatique N
876 Irène 19.5 400 678 UCB histoire O
898 Hector 18.5 800 765 UCB histoire O
765 UJM histoire N
Table Élève 765 UJM psychologie O
876 UCB informatique N
nomU ville effectif 876 UJF biologie O
INSA Lyon 36000 876 UJF biologie marine N
UCB Lyon 15000 898 INSA informatique O
UJF Grenoble 10000 898 UCB informatique O
UJM Saint-Étienne 21000
Table Candidature
Table Université
La date courante formatée
SELECT TO_CHAR(NOW(),'DD Mon YYYY') dateFormatee
15 septembre 2024
AS dateFormatee;
Plan
Opérateurs
Fonctions chaînes
Fonctions numériques
Fonctions date
Fonctions diverses
Opérateurs Fonctions chaînes Fonctions numériques Fonctions date Fonctions diverses
Fonctions diverses
I current_database() pour la base de données courante
I current_schema() pour le répertoire schéma courant
I current_user() pour l’utilisateur de la session
I coalesce(s1 , s2 , …) retourne le premier argument non nul
parmi s1 , s2 , …
I …
[Link]
En résumé
I Opérateurs et fonctions pour les chaînes, numériques, dates,
etc.
I Autres fonctions (géographiques, hachage, RI, etc.)
I Fonctions associées aux regroupements (clause group by)
[Link]