Fonctions et opérateurs MySQL (prédéfinis)

Booléens


=	Égal à	`a = b`	`SELECT * FROM Sales WHERE year = 2017`
<=>	NULL safe égal à	`a <=> b`	`SELECT * FROM Sales WHERE year <=> given_year`
<>, !=	Différent de	`a != b`	`SELECT * FROM Sales WHERE year != 2017`
<	Inférieur à	`a < b`	`SELECT * FROM Sales WHERE year < 2017`
<=	Inférieur ou égal à	`a <= b`	`SELECT * FROM Sales WHERE year <= 2017`
>	Supérieur à	`a > b`	`SELECT * FROM Sales WHERE year > 2017`
>=	Supérieur ou égal à	`a >= b`	`SELECT * FROM Sales WHERE year >= 2017`

BETWEEN ... AND	Entre deux valeurs (incluses)	`BETWEEN a AND b`	`SELECT * FROM Sales WHERE year BETWEEN 2017 AND 2020`
IS NULL	A pour valeur `NULL`	`a IS NULL`	`SELECT * FROM Sales WHERE year IS NULL`
LIKE	Correspond au motif ⁽¹⁾	`a LIKE 'pattern'`	`SELECT* FROM Sales WHERE nom NOT REGEXP '^[a-z]$'`
EXISTS	A au moins un résultat	`a EXISTS`	`SELECT * FROM Sales WHERE EXISTS (SELECT ... FROM ... LIMT 1)`

AND, &&	Et	`a && b`	`SELECT* FROM People WHERE nom = "Dupont" AND prenom = "Jeanne"`
OR, \|\|	Ou	`a \|\| b`	`SELECT * FROM People WHERE age >= 18 OR parent_autorise = 1`
XOR	Ou exclusif	`a xor b`	`SELECT * FROM User WHERE salt IS NULL XOR password = ENCODE("1234", salt)`
NOT, !	Inverse ⁽²⁾	`not a`	`SELECT * FROM People WHERE NOT(nom LIKE "Du%" AND LENGTH(nom) < 10)`

⁽¹⁾ Pour le LIKE :
% : 0 à n caractères quelconques
_ : exactement un caractère
Voir exemples

⁽²⁾ Le NOT peut s’appliquer à une expression entre parenthèses mais aussi à un opérateur : NOT NULL, NOT LIKE, NOT BETWEEN, etc

Nombres


+	Addition	`num1 + num2`	`SELECT 1 + 2 # 3`
-	Soustraction	`num1 - num2`	`SELECT 1 - 2 # -1`
/, DIV	Division	`num1 / num2`	`SELECT 1 / 2 # 0.5`
*	Multiplication	`num1 * num2`	`SELECT 1 * 2 # 2`
%, MOD	Modulo	`num1 % num2`	`SELECT 4 % 2 # 0`

ROUND	Arrondi	`ROUND(num,precision)`	`SELECT ROUND(2.98765, 2) # 2.99`
TRUNCATE	Troncature	`TRUNCATE(num,precision)`	`SELECT TRUNCATE(2.98765, 2) # 2.98`
CEIL	Arrondi à l'entier supérieur Synonyme: CEILING	`CEIL(num)`	`SELECT CEIL(5.1) # 6`
FLOOR	Arrondi à l'entier inférieur	`FLOOR(num)`	`SELECT FLOOR(5.9) # 5`
FORMAT	Arrondi + formattage	`FORMAT(num,precision)`	`SELECT FORMAT(1000, 2) # 1,000.00`

BIN	Retourne le code binaire de num	`BIN(code)`	`SELECT BIN(8) # 1010`
HEX	Retourne le code hexadecimal de num	`HEX(code)`	`SELECT HEX(10) # A`
OCT	Retourne le code octal de num	`OCT(code)`	`SELECT OCT(10) # 12`
CONV	Convertit num d'une base à l'autre	`CONV(num,from,to)`	`SELECT CONV(1010,2,16) # A`

ABS	Valeur absolue	`ABS(num)`	`SELECT ABS(-2) # 2`
SIGN	Retourne le signe de num (-1,0 ou 1)	`SIGN(num)`	`SELECT SIGN(-2) # -1`

RAND	Retourne un nombre aléatoire entre 0 et 1	`RAND()`	`SELECT RAND() # 0.2651881633933523`
PI	Retourne pi	`PI()`	`SELECT PI() # 3.141593`

POW	Retourne num pouvoir p Synonyme: POWER	`POW(num,p)`	`SELECT POW(2,8) # 256`
SQRT	Racine carrée	`SQRT(num)`	`SELECT SQRT(256) # 16`
EXP	Exponentielle	`EXP(num)`	`SELECT EXP(2) # 7.38905609893065`
LN	Logarithme népérien	`LN(num)`	`SELECT LN(EXP(2)) # 2`
LOG10	Log base 10	`LOG10(num)`	`SELECT LOG10(10) # 1`
LOG2	Log base 2	`LOG2(num)`	`SELECT LOG2(2) # 1`
LOG	Log base n	`LOG(base,num)`	`SELECT LOG3(3,3) # 1`

SIN	Sinus	`SIN(num)`	`SELECT SIN(0) # 0`
COS	Cosinus	`COS(num)`	`SELECT COS(0) # 1`
TAN	Tangente	`TAN(num)`	`SELECT TAN(0) # 0`
ASIN	Arc Sinus	`ASIN(num)`	`SELECT SIN(0) # 0`
ACOS	Arc Cosinus	`ACOS(num)`	`SELECT ACOS(1) # 0`
ATAN	Arc tangente	`ATAN(num)`	`SELECT ATAN(0) # 0`
ATAN2	Arc tangente de deux points	`ATAN2(num1,num2)`	`SELECT ATAN(-3,2) # -0.982793723247329`
COT	Cotangente	`COT(num)`	`SELECT COT(7) # 1.1475154224051356`

DEGREES	Convertion d'un degrès en radian	`DEGREES(num)`	`SELECT DEGREES(90) # 5156.620156177409`
RADIANS	Convertion d'un radian en degrès	`RADIANS(num)`	`SELECT RADIANS(DEGREES(90)) # 90`

Bits


BIT_COUNT	Retourne le nombre de bits à 1 dans arg	`BIT_COUNT(num)`	`SELECT BIT_COUNT(16) # 1`
&	Bitwise and	`a & b`	`SELECT 5 & 3 # 1 (101 & 011 = 001)`
\|	Bitwise or	`a \| b`	`SELECT 5 \| 3 # 7 (101 & 011 = 111)`
^	Bitwise xor	`a ^ b`	`SELECT 5 ^ 3 # 6 (101 ^ 011 = 110)`
<<	Left shift	`a << n`	`SELECT 8 << 1 # 16`
>>	Right shift	`a >> n`	`SELECT 8 >> 1 # 4`
~	Bitwise not	`~num`	`SELECT ~2 # 18446744073709552000`

Tests


CASE	Multi-condition	CASE arg WHEN val1 THEN rval1 WHEN val2 THEN rval2 ELSE rval3 END CASE WHEN arg1 = val1 THEN rval1 WHEN arg2 = val2 THEN rval2 ELSE rval3 END	`SELECT (CASE att WHEN 1 THEN "un" WHEN 2 THEN "deux" ELSE "plusieurs" END) # plusieurs`
IF	Condition ternaire	`IF(exp, valtrue, valfalse)`	`SELECT IF(1 = 0, "ok", "nok") # nok`
IFNULL	Retourne arg si != `NULL`, sinon default	`IFNULL(arg, default)`	`SELECT IFNULL(NULL, "-") # -`
NULLIF	Retoure `NULL` si arg = default	`NULLIF(arg, default)`	`SELECT NULLIF("-", "-") # (null)`

DEFAULT	Retourne la valeur par défaut d'une colonne. Lève une erreur si la colonne n'a pas de valeur par défaut	`DEFAULT(attr)`	`SELECT default(col1) from table1`

Convertions


CAST	Convertion d'un type de donnée à un autre	`CAST(value AS type)`	`SELECT CAST('2012-12-12' AS DATETIME) # 2012-12-12T00:00:00Z`
CONVERT	Convertion d'un encodage à un autre	`CONVERT(value USING transcoding_name)`	`SELECT CONVERT('bird' USING utf8) # bird`

CHARSET	Retourne l'encodage utilisé	`CHARSET(arg)`	`SELECT CHARSET("abc") # utf8`
COLLATION	Retourne la collation utilisée	`COLLATION(arg)`	`SELECT COLLATION("abc") # utf8_general_ci`

Chaînes de caractère


CONCAT	Concaténation	`CONCAT(args)`	`SELECT CONCAT("aa", " ", "bb") # aa bb`
CONCAT_WS	Concaténation avec séparateur	`CONCAT_WS(sep, args)`	`SELECT CONCAT_WS(",", aa", " ", "bb") # aa, ,bb`
TRIM	Supprime les espaces au début et à la fin. Voir aussi: LTRIM et RTRIM	`TRIM(arg)`	`SELECT TRIM(" aa ") # aa`
SPACE	Retourne num espaces	`SPACE(num)`	`SELECT CONCAT("a", SPACE(5), "b") # a b`
REPEAT	Répète arg num fois	`REPEAT(arg,num)`	`SELECT REPEAT("ab", 3) # ababab`
LPAD	Préfixe arg avec char pour obtenir num caractères. Voir aussi: RPAD	`LPAD(arg,num,char)`	`SELECT LPAD("1", 3, "0") # 001`

CHAR_LENGTH	Retourne le nombre de caractère de arg Synonyme: CHARACTER_LENGTH	`CHAR_LENGTH(arg)`	`SELECT CHAR_LENGTH("éléphant") # 8`
LENGTH	Retourne le nombre d'octets de arg. Synonyme: OCTET_LENGTH	`LENGTH(arg)`	`SELECT LENGTH("éléphant") # 10`
BIT_LENGTH	Retourne le nombre de bits de arg	`BIT_LENGTH(arg)`	`SELECT BIT_LENGTH("éléphant") # 80`

ASCII	Retourne le code decimal de char. Valeur > 127 = pas un caractère ASCII	`ASCII(char)`	`SELECT ASCII("à") # 195`
ORD	Retourne la valeur numérique de char (= (code octet 1) + (code octet 2 * 256) + (code octet 3 * 2562)) = ASCII si le caractère est sur 1 octet	`ORD(arg)`	`SELECT ORD("à") # 50080`
CHAR	Retourne le caractère du code decimal num	`CHAR(arg)`	`SELECT CHAR(ORD("à") USING "utf8") # à`
HEX	Retourne arg en hexadécimal	`HEX(arg)`	`SELECT HEX("Hello World") # 48656C6C6F20576F726C64`
UNHEX	Decode le code hexadécimal arg	`HEX(arg)`	`SELECT CAST(UNHEX(HEX('Hello World')) AS CHAR) # Hello World`

REVERSE	Inverse les lettres	`REVERSE(arg)`	`SELECT REVERSE("abc") # cba`
QUOTE	Ajoute un backslash devant chaque caractère de quote, backslash, NUL et control-Z.	`QUOTE(arg)`	`SELECT QUOTE('text\'quote') # 'text\'quote'`
REPLACE	Remplace a par b	`REPLACE(arg,a,b)`	`SELECT REPLACE("a-b-c", "-", " ") # a b c`
INSERT	Remplace la sous-chaine par une autre selon sa position	`INSERT(arg,pos,len,new)`	`SELECT INSERT('Hello World!', 7, 5, 'Bob') # Hello Bob!`
LOWER	Retourne arg en minuscules. Synonyme: LCASE	`LOWER(arg)`	`SELECT LOWER("ABC") # abc`
UPPER	Retourne arg en majuscules. Synonyme: UCASE	`UPPER(arg)`	`SELECT UPPER("abc") # ABC`
SOUNDEX	Retourne le résultat de Soundex sur arg⁽¹⁾	`SOUNDEX(arg)`	`SELECT SOUNDEX("racket"), SOUNDEX("raquet") # R230 R230`

SUBSTRING	Retourne une sous-chaîne (à partir de 1) Synonyme: MID, SUBSTR	`SUBSTRING(arg,pos,num)`	`SELECT SUBSTRING("Hello World",1,5) # Hello`
LEFT	Retourne num caractères à partir de la gauche	`LEFT(arg,num)`	`SELECT LEFT("Hello World",5) # Hello`
RIGHT	Retourne num caractères à partir de la droite	`RIGHT(arg,num)`	`SELECT RIGHT("Hello World",5) # World`
SUBSTRING_INDEX	Retourne le texte situé devant le délimiteur	`SUBSTRING_INDEX(arg, delim, occurrence)`	`SELECT SUBSTRING_INDEX("/home/bob/file", "/", 3) # /home/bob`
POSITION	Retourne la position d'une sous-chaîne	`POSITION(char IN arg)`	`SELECT POSITION("o" in "Hello World") # 5`
INSTR	Retourne la position du délimiteur	`INSTR(arg,delim)`	`SELECT INSTR('un,deux,trois', 'trois') # 9`
LOCATE	Retourne la position du délimiteur à partir de la position donnée (incluse).	`LOCATE(delim,arg,pos)`	`SELECT LOCATE(',','un,deux,trois',4) # 8`

STRCMP	Compare deux chaînes de caractère. 1:(arg>str), -1:(arg<str), 0:(arg=str)	`STRCMP(arg,str)`	`SELECT STRCMP("img10","img2") # -1`
CRC32	Calcule la check-sum de arg	`CRC32(arg)`	`SELECT CRC32("Hello") # 4157704578`

⁽¹⁾ Soundex est un algorithme d’indexation de noms par leur prononciation (en anglais britannique), l’objectif étant que Soundex retourne la même valeur pour deux mots qui se prononcent de la même manière, indifféremment des différences d’écriture.

Groupes


ELT	Retourne l'élément num dans la liste d'arguments	`ELT(num,args)`	`SELECT ELT(3, 'un', 'deux', 'trois') # trois`
FIELD	Retourne la position de arg dans liste d'arguments	`FIELD(arg,args)`	`SELECT FIELD('trois', 'un', 'deux', 'trois') # 3`
MAKE_SET	Crée un set des arguments situés à la position donnée en binaire ⁽¹⁾	`MAKE_SET(binpos,args)`	`SELECT MAKE_SET(1\|8, 'un','deux','trois','quatre') # un,quatre`
FIND_IN_SET	Retourne la position de arg dans le set ⁽²⁾	`FIND_IN_SET(arg,set)`	`SELECT FIND_IN_SET('trois', 'un,deux,trois') # 3`
GREATEST	Retourne la plus grande valeur parmis une liste	`GREATEST(args)`	`SELECT GREATEST(1,2,3) # 3`
LEAST	Retourne la plus petite valeur parmis une liste	`LEAST(args)`	`SELECT LEAST(1,2,3) # 1`
INTERVAL	Retourne l'index de la valeur plus petite que arg parmis une liste	`INTERVAL(arg, args)`	`SELECT INTERVAL(5.5,1,3,5,7) # 3`
COALESCE	Retourne la première valeur non nulle parmis une liste	`COALESCE(args)`	`SELECT COALESCE(NULL,3,2) # 3`
IN	Vérifie si arg est dans une liste de valeurs donnée	`arg IN(args)`	`SELECT 3 IN(1,2,3) # 1`

⁽¹⁾ Position en binaire = puissances de 2. Pos 1→1 (2^0), pos 2→2 (2^1), pos 3→4 (2^2), pos 4→8 (2^3), etc.
⁽²⁾ On peut aussi utiliser l’opérateur sur les bits & pour vérifier la présence d’un index ou non

Tester la présence d’une valeur dans un groupe :

Soit en testant la présence de l’index (position binaire)

-- interests: set('reading','sports','swimming','drawing','writing','acting')
-- Les personnes ayant les intérêts "reading" (2^0) et "drawing" (2^3)
SELECT name
FROM people_interests
WHERE interests & 1 AND interests & 8
ORDER BY name;

Soit avec FIND_IN_SET (revient au même que la requête précédente)

SELECT name
FROM people_interests
WHERE FIND_IN_SET('reading',interests)>0
AND FIND_IN_SET('drawing',interests)>0
ORDER BY name;

Agrégats


COUNT	Nombre de résultats	`COUNT({*\|DISTINCT att})`	`SELECT count(*) FROM NOTE # 6`
MAX	Valeur maximale	`MAX(att)`	`SELECT count(note) FROM NOTE # 16`
MIN	Valeur minimale	`MIN(att)`	`SELECT min(note) FROM NOTE # 8`
AVG	Moyenne	`AVG(att)`	`SELECT avg(note) FROM NOTE # 12.5`
SUM	Somme	`SUM([DISTINCT] att)`	`SELECT sum(note) FROM NOTE # 75`
VARIANCE	Variance standard (population) Synonyme: VAR_POP	`VARIANCE(att)`	`SELECT VARIANCE(Note) FROM NOTE # 6.25`
VAR_SAMP	Variance échantillon. Le dénominateur est le nombre de ligne - 1	`VAR_SAMP(att)`	`SELECT VAR_SAMP(Note) FROM NOTE # 7.5`
STD	Déviation standard (population) Synonyme: STDDEV, STDDEV_POP	`STD(att)`	`SELECT STD(Note) FROM NOTE # 2.5`
STDDEV_SAMP	Déviation échantillon	`STD(att)`	`SELECT STDDEV_SAMP(Note) FROM NOTE # 2.7386127875258306`
GROUP_CONCAT	Concaténe toutes les valeurs du groupe. SQLFiddle	`GROUP_CONCAT(att [ORDER BY att] [SEPARATOR sep])`	`SELECT GROUP_CONCAT(nom SEPARATOR "-") FROM clubs # Musique-Littérature-Sport`

Dates


NOW	Retourne la date et heure actuelle Synonyme: CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP	`NOW()`	`SELECT NOW() # 2017-11-03T07:18:21Z`
SYSDATE	Retourne la date et heure au moment de l'execution⁽¹⁾	`SYSDATE()`	`SELECT SYSDATE() # 2017-11-03T07:18:21Z`
UTC_TIMESTAMP	Retourne la date et heure UTC	`UTC_TIMESTAMP()`	`SELECT UTC_TIMESTAMP() # 2017-11-03T07:18:21Z`
CURRENT_DATE	Retourne la date du jour Synonyme: CURDATE	`CURRENT_DATE()`	`SELECT CURRENT_DATE() # 2017-11-03`
UTC_DATE	Retourne la date UTC	`UTC_DATE()`	`SELECT UTC_DATE() # 2017-11-03`
CURRENT_TIME	Retourne l'heure actuelle Synonyme: CURTIME	`CURRENT_TIME()`	`SELECT CURRENT_TIME() # 07:18:21`
UTC_TIME	Retourne ll'heure UTC	`UTC_TIME()`	`SELECT UTC_TIME() # 07:18:21`
FROM_DAYS	Crée une date à partir d'un nombre de jours	`FROM_DAYS(num)`	`SELECT FROM_DAYS(737001) # 2017-11-03`
FROM_UNIXTIME	Crée une date à partir d'un timestamp Unix	`FROM_UNIXTIME(timestamp)`	`SELECT FROM_UNIXTIME(1509689901) # 2017-11-03T06:18:21Z`
MAKEDATE	Crée une date à partir d'un numéro de jour + année	`MAKEDATE(year,dayofyear)`	`SELECT MAKEDATE(2017,307) # 2017-11-03`
SEC_TO_TIME	Crée un time à partir d'un nombre de secondes	`SEC_TO_TIME(num)`	`SELECT SEC_TO_TIME(3610) # 01:00:10`
MAKETIME	Crée un time à partir d'une heure + minute + seconde	`MAKETIME(hour,minute,second)`	`SELECT MAKETIME(7,18,21) # 07:18:21`
TIMESTAMP	Crée une date à partir d'un timestamp donné	`TIMESTAMP(timestamp)`	`SELECT TIMESTAMP('2017-11-03 07:18:21') # 2017-11-03T07:18:21Z`
STR_TO_DATE	Crée une date à partir d'une chaîne de caractères et du format donné ⁽²⁾	`STR_TO_DATE(str, format)`	`SELECT STR_TO_DATE("03/11/2017", "%d/%m/%Y") # 2017-11-03`

LAST_DAY	Retourne le dernier jour du mois de arg	`LAST_DAY(arg)`	`SELECT LAST_DAY(NOW()) # 2017-11-30`
MICROSECOND	Retourne le nombre de microsecondes de arg	`MICROSECOND(arg)`	`SELECT MICROSECOND(NOW()) # 0`
SECOND	Retourne le nombre de secondes de arg	`SECOND(arg)`	`SELECT SECOND(NOW()) # 21`
MINUTE	Retourne le nombre de minutes de arg	`MINUTE(arg)`	`SELECT MINUTE(NOW()) # 18`
HOUR	Retourne l'heure de arg	`HOUR(arg)`	`SELECT HOUR(NOW()) # 7`
TIME	Retourne la partie temps de arg	`TIME(arg)`	`SELECT TIME(NOW()) # 07:18:21`
DATE	Retourne la partie date de arg	`DATE(arg)`	`SELECT DATE('2017-11-03T07:18:21') # 2017-11-03`
DAY	Retourne le jour du mois de arg (entre 1 et 31) Synonyme: DAYOFMONTH	`DAY(arg)`	`SELECT DAY('2017-11-03T07:18:21') # 3`
DAYNAME	Retourne le jour de la semaine de arg	`DAYNAME(arg)`	`SELECT DAYNAME('2017-11-03T07:18:21') # Friday`
DAYOFWEEK	Retourne le numéro de jour de la semaine de arg (de Dimanche 1 à Samedi 7)	`DAYOFWEEK(arg)`	`SELECT DAYOFWEEK('2017-11-03T07:18:21') # 6`
WEEKDAY	Retourne l'index du jour de la semaine (de Lundi 0 à Dimanche 6)	`WEEKDAY(arg)`	`SELECT WEEKDAY('2017-11-03T07:18:21') # 4`
WEEK	Retourne le numéro de la semaine dans l'année (de 0 à 53). Premier jour: Dimanche.	`WEEK(arg)`	`SELECT WEEK('2017-11-03T07:18:21') # 44`
WEEKOFYEAR	Retourne le numéro de la semaine dans l'année (de 1 à 53). Premier jour: Lundi.	`WEEKOFYEAR(arg)`	`SELECT WEEKOFYEAR('2017-11-03T07:18:21') # 44`
MONTH	Retourne le numéro de mois de arg (entre 1 et 12)	`MONTH(arg)`	`SELECT MONTH(NOW()) # 11`
MONTHNAME	Retourne le nom du mois de arg	`MONTHNAME(arg)`	`SELECT MONTHNAME(NOW()) # November`
QUARTER	Retourne le numéro de quart d'année de arg (entre 1 et 4)	`QUARTER(arg)`	`SELECT QUARTER(NOW()) # 4`
DAYOFYEAR	Retourne le jour de l'année de arg (de 1 à 366)	`DAYOFWEEK(arg)`	`SELECT DAYOFYEAR('2017-11-03T07:18:21') # 307`
YEAR	Retourne l'année de arg (de 1000 à 9999)	`YEAR(arg)`	`SELECT YEAR('2017-11-03T07:18:21') # 2017`
YEARWEEK	Retourne l'année et la semaine de l'année de arg	`YEARWEEK(arg)`	`SELECT YEARWEEK('2017-11-03T07:18:21') # 201744`
UNIX_TIMESTAMP	Retourne arg en timestamp Unix	`UNIX_TIMESTAMP(arg)`	`SELECT UNIX_TIMESTAMP("2017-11-03") # 1509667200`
TO_DAYS	Retourne le nombre de jours depuis l'an 0	`TO_DAYS(arg)`	`SELECT TO_DAYS("2017-11-03") # 737001`

DATE_FORMAT	Formatte une date en chaîne de caractère ⁽²⁾	`DATE_FORMAT(date, format)`	`SELECT DATE_FORMAT(NOW(), "%d/%m/%Y %H:%i:%s") # 03/11/2017 07:18:21`
TIME_FORMAT	Formatte un time en chaîne de caractère ⁽²⁾	`TIME_FORMAT(time, format)`	`SELECT TIME_FORMAT(NOW(), "%H:%i:%s") # 07:18:21`
GET_FORMAT	Retourne le format d'une date selon la localisation donnée (ex: USA, EUR) ⁽³⁾	`GET_FORMAT({DATE\|TIME\|DATETIME},"{EUR\|USA\|JIS\|ISO\|INTERNATIONAL}")`	`SELECT GET_FORMAT(DATETIME, 'EUR') # %Y-%m-%d %H.%i.%s`

+, DATE_ADD	Ajoute l'intervalle donné à une date ⁽⁴⁾ Synonyme: ADDDATE	`date + INTERVAL n unit`, `ADDDATE(date,INTERVAL n unit)`, `ADDDATE(expr,days)`	`SELECT DATE_ADD(NOW(), INTERVAL '10 10:10:10' DAY_SECOND) # 2027-11-03T07:18:21Z`
-, DATE_SUB	Soutrait l'intervalle donné à une date ⁽⁴⁾ Synonyme: SUBDATE	`date - INTERVAL n unit`, `DATE_SUB(date,INTERVAL n unit)`	`SELECT DATE_SUB(NOW(), INTERVAL '10 10:10:10' DAY_SECOND) # 2017-11-13T17:28:31Z`
DATEDIFF	Calcule la différence en jours entre deux dates	`DATEDIFF(date1,date2)`	`SELECT DATEDIFF('2017-11-03T07:18:21', '2017-11-13T17:28:31Z') # -10`
TIMESTAMPADD	Ajoute l'intervalle donné à une date ⁽⁴⁾ (uniquement les unités entières)	`TIMESTAMPADD(unit, num, date)`	`SELECT TIMESTAMPADD(MONTH, 2, "2017-11-03") # 2018-01-03`
TIMESTAMPDIFF	Calcule la différence entre deux dates avec l'unité donné⁽⁴⁾ (uniquement les unités entières)	`TIMESTAMPDIFF(unit,date1,date2)`	`SELECT TIMESTAMPDIFF(MONTH, "2017-11-03", "2017-10-02") # -1`
TIMEDIFF	Calcule la différence de temps entre deux temps. La date supérieure doit nécessairement être en premier pour avoir un résultat valide	`TIMEDIFF(date1,date2)`	`SELECT CAST(TIMEDIFF('07:18:21', '17:28:31') as CHAR) # -10:10:10`
ADDTIME	Ajoute le temps donné à une date	`ADDTIME(arg,time)`	`SELECT ADDTIME(NOW(), '10 10:10:10') # 2017-11-13T17:28:31Z`
SUBTIME	Soustrait le temps donné à une date	`SUBTIME(arg,time)`	`SELECT SUBTIME(NOW(), '10 10:10:10') # 2017-10-23T21:08:11Z`
CONVERT_TZ	Convertit d'une timezone à une autre	`CONVERT_TZ(date, from, to)`	`SELECT CONVERT_TZ(NOW(),'+00:00','+10:00') # 2017-11-03T17:18:21Z`
PERIOD_ADD	Ajoute num mois à une période année/mois donnée	`PERIOD_ADD(period, num)`	`SELECT PERIOD_ADD(EXTRACT(YEAR_MONTH FROM NOW()), 2) # 201801`
PERIOD_DIFF	Retourne la différence entre deux périodes	`PERIOD_DIFF(period1, period2)`	`SELECT PERIOD_DIFF(NOW(), NOW() + INTERVAL 1 HOUR) # -1200`

⁽¹⁾ NOW() retourne le temps auquel la requête à été lancée tandis que SYSDATE() retourne le temps auquel la requête à été executée. La différence est notable à l’intérieur d’une procédure.
⁽²⁾ Format date
⁽³⁾ Liste des formats par localisation
⁽⁴⁾ Unités d’intervalle disponibles

Encode & decode


TO_BASE64	Encode str en base64	`TO_BASE64`	`SELECT TO_BASE64("Hello World") # SGVsbG8gV29ybGQ=`
FROM_BASE64	Decode str en base64	`FROM_BASE64`	`SELECT CAST(FROM_BASE64(TO_BASE64("Hello World")) AS CHAR) # Hello World=`
ENCODE	Encode str avec secret	`ENCODE(str, secret)`	`SELECT ENCODE("Hello World", "mypassphrase") # SY1W01kCMCYaYk0=`
DECODE	Decode str avec secret	`DECODE(str, secret)`	`SELECT CAST(DECODE(ENCODE("Hello World", "mypassphrase"), "mypassphrase") AS CHAR) # Helo World`
AES_ENCRYPT	Encrypte str avec secret (algorithme AES)	`AES_ENCRYPT(str, secret)`	`SELECT AES_ENCRYPT("Hello World", "mypassphrase") # hN63i2KKmFIxqC/OWipX9g==`
AES_DECRYPT	Decrypte str avec secret (algorithme AES)	`AES_DECRYPT(str, secret)`	`SELECT CAST(AES_DECRYPT(AES_ENCRYPT("Hello World", "mypassphrase"), "mypassphrase") AS CHAR) # Hello World`
DES_ENCRYPT	Encrypte str avec secret (algorithme Triple-DES)	`DES_ENCRYPT(str, secret)`	`SELECT DES_ENCRYPT("Hello World", "mypassphrase") # /28aS5IVYTJu+qkqATSQaeA=`
DES_DECRYPT	Decrypt str avec secret (algorithme Triple-DES)	`DES_DECRYPT(str, secret)`	`SELECT CAST(DES_DECRYPT(DES_ENCRYPT("Hello World", "mypassphrase"),"mypassphrase") AS CHAR) # Hello World`
COMPRESS	Compresse str	`COMPRESS(str)`	`SELECT COMPRESS("Hello World") # CwAAAHic80jNyclXCM8vykkBABgLBB0=`
UNCOMPRESS	Decompresse str	`UNCOMPRESS(str)`	`SELECT CAST(UNCOMPRESS(COMPRESS("Hello World")) as CHAR) # Hello World`
UNCOMPRESSED_LENGTH	Retourne la longueur decompressée de str	`UNCOMPRESSED_LENGTH(str)`	`SELECT UNCOMPRESSED_LENGTH(COMPRESS("Hello World")) # 11`
ENCRYPT	Hashe str avec secret (fonction Unix crypt()). Retourne `NULL` sous Windows	`ENCRYPT(str, secret)`	`SELECT ENCRYPT("Hello World", "mypassphrase") # bXlWY05ZSFJORi9LWQ==`
MD5	Hashe str (algorithme MD5)	`MD5(str)`	`SELECT MD5("Hello World") # b10a8db164e0754105b7a99be72e3fe5`
SHA1	Hashe str (algorithme SHA1)	`SHA1(str)`	`SELECT SHA1("Hello World") # 0a4d55a8d778e5022fab701977c5d840bbc486d0`
SHA2	Hashe str avec secret (algorithme SHA2)	`SHA2(str)`	`SELECT SHA2("Hello World", "mypassphrase") # a591a6d40bf420404a011733cfb7b190d62c65bf0bcda32b57b277d9ad9f146e`
PASSWORD	Hashe str	`PASSWORD(str)`	`SELECT PASSWORD("Hello World") # *190347D0759F3564BAA3EA737E1BB0480A9E75C9`

Pertinence

MATCH ... AGAINST permet de vérifie pertinence d’une ligne pour une recherche, selon les colonnes ciblées et le texte recherché.
Les termes recherchés ne sont pas forcemment accolés dans le texte.
Les mots-vides de la requête (stopwords en anglais) sont ignorés. Plus d’info sur les stopwords.
Ne peut être utilisé que sur les colonnes ayant un index de type FULLTEXT.

Le format est comme suit :

MATCH (colonnes) AGAINST (recherche [
    IN NATURAL LANGUAGE MODE
  | [IN NATURAL LANGUAGE MODE] WITH QUERY EXPANSION
  | IN BOOLEAN MODE
])

Plusieurs modes sont disponibles :

IN NATURAL LANGUAGE MODE (par défaut) :
Interprète la chaîne comme une phrase en langage naturel (texte libre). SQLFiddle

SELECT id, MATCH(title, text) AGAINST('security attack') as relevance FROM documents;

+----+---------------------+
| id | relevance           |
+----+---------------------+
| 1  | 0                   |
| 2  | 0.22764469683170319 |
| 3  | 0.9105787873268127  |
+----+---------------------+

WITH QUERY EXPANSION :
Élargit le résultat de la recherche naturelle en recherchant les documents apparentés aux résultats trouvés

SELECT productName
FROM products
WHERE MATCH(productName) 
      AGAINST('1992' WITH QUERY EXPANSION);

+-------------------------------------+
| productName                         |
+-------------------------------------+
| 1992 Porsche Cayenne Turbo Silver   |
| 1992 Ferrari 360 Spider red         |
| 2001 Ferrari Enzo                   |
| 1932 Alfa Romeo 8C2300 Spider Sport |
| 1948 Porsche 356-A Roadster         |
| 1948 Porsche Type 356 Roadster      |
| 1956 Porsche 356A Coupe             |
+-------------------------------------+

IN BOOLEAN MODE :
Spécifie les termes à inclure et à exclure

SELECT * FROM articles WHERE MATCH (title,body)
    AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
+----+-----------------------+-------------------------------------+
| id | title                 | body                                |
+----+-----------------------+-------------------------------------+
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...        |
|  2 | How To Use MySQL Well | After you went through a ...        |
|  3 | Optimizing MySQL      | In this tutorial we will show ...   |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ... |
|  6 | MySQL Security        | When configured properly, MySQL ... |
+----+-----------------------+-------------------------------------+

Opérateurs de la recherche booléenne:

Opérateur	Description	Exemple
mot	Les lignes qui contiennent ce mot ont une pertinence plus élevée	`join union` : les lignes qui contiennent un des deux mots
~mot	Les lignes qui contiennent ce mot ont une pertinence moins élevée	`join ~union` : les lignes qui contiennent "join" et "union" sont moins pertinentes que celles qui ne contiennent que "join"
"mot1 mot2"	Les lignes qui contiennent ces deux mots littéralement, accolés	`join union` : les lignes qui contiennent exactement l'expression "join union"
+mot	Le mot doit être présent	`+join +union` : les lignes qui contiennent "join" et "union" `+join union` : les lignes qui contiennent "join" (résultat plus pertinent si elle contient aussi "union")
-mot	Le mot ne doit pas être présent	`+join -union` : contient "join" mais pas "union"
(mot1 mot2)	Sous-expression, permet d'appliquer un opérateur à plusieurs mots	`+(join union)` : les lignes qui contiennent "join" et "union"
>mot1 <mot2	Augmente (>) ou diminue (<) la pertinence	`+join +(>left <right)` : les lignes qui contiennent "join left" ou "join right" ("join left" plus pertinent)
mot*	Les lignes qui contiennent un mot qui commence par "mot"	`join`: matche 'join', 'joins', 'joining' etc.

XML


ExtractValue	Extrait une valeur d'une chaîne XML ⁽¹⁾. SQLFiddle	`ExtractValue(xml, xpath)`	`SELECT doc_id, ExtractValue(xml_doc, '/catalog/book[1]/author') FROM catalogs`
UpdateXML	Remplace un fragment XML ⁽¹⁾. Ne fait rien si plusieurs nodes matchent.	`UpdateXML(xml, xpath, newnode)`	`SELECT xml_doc, UpdateXML(xml_doc, '/catalog/book[1]/author', '<author>Chuck</author>') FROM catalogs WHERE doc_id = 1`

⁽¹⁾ Format XPath

Extraire des valeurs :

ExtractValue combiné à REPLACE peut servir à extraire des valeurs d’une liste séparée par virgule :

SELECT ExtractValue(CONCAT('<items><item>',
    REPLACE('a,b,c,d,e,f', ',', '</item><item>'),
    '</item></items>'), '/items/item[5]') as items;

SELECT
ExtractValue(@items, '/items/item[5]') as item5,
ExtractValue(@items, '/items/item[2]') as item2
FROM
(
  SELECT @items := CONCAT('<items><item>',
      REPLACE('a,b,c,d,e,f', ',', '</item><item>'),
  '</item></items>')
) as list;

IP


IS_IPV4	Vérifie si arg est une adresse IPv4	`IS_IPV4(arg)`	`SELECT IS_IPV4('10.0.5.9') # 1`
INET_ATON	Retourne la représentation numérique d'une adresse IPv4 (NULL si l'adresse n'est pas valide)	`INET_ATON(ip)`	`SELECT INET_ATON('10.0.5.9') # 167773449`
INET_NTOA	Retourne l'adresse IPv4 de la représentation numérique	`INET_NTOA(num)`	`SELECT INET_NTOA(INET_ATON('10.0.5.9')) # 10.0.5.9`
IS_IPV4_COMPAT	Vérifie si la représentation numérique donnée est une adresse IPv4	`IS_IPV4_COMPAT(num)`	`SELECT IS_IPV4_COMPAT(INET6_ATON('::10.0.5.9')) # 1`
IS_IPV4_MAPPED	Vérifie si la représentation numérique donnée est une adresse IPv4 mappée en IPv6	`IS_IPV4_MAPPED()`	`SELECT IS_IPV4_MAPPED(INET6_ATON('::ffff:10.0.5.9')) # 1`
IS_IPV6	Vérifie si arg est une adresse IPv6	`IS_IPV6(arg)`	`SELECT IS_IPV6('fdfe::5a55:caff:fefa:9089') # 1`
INET6_ATON	Retourne la représentation numérique d'une adresse IPv6	`INET6_ATON(ip)`	`SELECT INET6_ATON('fdfe::5a55:caff:fefa:9089') # /f4AAAAAAABaVcr//vqQiQ==`
INET6_NTOA	Retourne l'adresse IPv6 de la représentation numérique	`INET6_NTOA(num)`	`SELECT INET6_NTOA(INET6_ATON('fdfe::5a55:caff:fefa:9089')) # fdfe::5a55:caff:fefa:9089`

Verrous


GET_LOCK	Essaie d'obtenir un verrou nommé. Retourne 1 = ok, 0 = timeout, NULL = erreur (ex kill ou memory overflow)	`GET_LOCK(str, timeout)`	`SELECT GET_LOCK('lock1',10)`
RELEASE_LOCK	Libère le verrou str	`RELEASE_LOCK(str)`	`SELECT RELEASE_LOCK('lock1')`
RELEASE_ALL_LOCKS	Libère tous les verrous. Retourne le nombre de verrous libérés	`RELEASE_ALL_LOCKS()`	`SELECT RELEASE_ALL_LOCKS()`
IS_FREE_LOCK	Vérifie si le verrou str est libre. 1 = libre, 0 = utilisé, NULL = erreur (argument incorrect)	`IS_FREE_LOCK(str)`	`SELECT IS_FREE_LOCK('lock1')`
IS_USED_LOCK	Retourne l'identifiant de la session qui a crée le verrou s'il existe ou NULL	`IS_USED_LOCK(str)`	`SELECT GET_LOCK('str',10), IS_USED_LOCK('str'), RELEASE_LOCK('str') # 1 1916 1`

Divers


UUID	Retourne un UUID unique	`UUID()`	`SELECT UUID() # d23ea6c0-c173-11e7-b40b-0242ac110004`
UUID_SHORT	Retourne un UUID court unique	`UUID_SHORT()`	`SELECT UUID_SHORT() # 25330388322746370`
RANDOM_BYTES	Retourne num octets aléatoires (num entre 1 et 1024)	`RANDOM_BYTES(num)`	`SELECT RANDOM_BYTES(5) # nu0xeXw=`

DATABASE	Retourne le nom de la base de données en cours Synonyme: SCHEMA	`DATABASE()`	`SELECT DATABASE() # db_9_0f6a86`
USER	Retourne le nom de l'utiilisateur + hôte en cours Synonyme: SESSION_USER, SYSTEM_USER	`USER`	`SELECT USER() # user_9_0f6a86@10.1.1.14`
CONNECTION_ID	Retourne l'ID de connection de l'utilisateur	`CONNECTION_ID()`	`SELECT CONNECTION_ID() # 259`
VERSION	Retourne le numéro de version MySQL	`VERSION()`	`SELECT VERSION() # 5.6.35`

BENCHMARK	Répète une expression num fois. Permet de mesurer le temps d'execution	`BENCHMARK(num, expt)`	`SELECT BENCHMARK(1000000,AES_ENCRYPT('Hello World',"mypassphrase")) # 0 (Execution Time: 208ms)`
SLEEP	Attend pendant num secondes	`SLEEP(num)`	`SELECT SLEEP(1000)`

FOUND_ROWS	Retourne le nombre de lignes trouvées par le dernier SELECT	`FOUND_ROWS()`	`SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name LIMIT 10; SELECT FOUND_ROWS() # 50`
ROW_COUNT	Retourne le nombre de lignes affectées par le dernier UPDATE ou DELETE. Nécessite le flag CLIENT_FOUND_ROWS (au moment de la connexion)	`ROW_COUNT()`	`UPDATE donnees SET valeur = "new" WHERE id < 3; SELECT ROW_COUNT(); # 2`
LAST_INSERT_ID()	Retourne le dernier ID inséré	`LAST_INSERT_ID()`	`INSERT INTO donnees (nom, valeur) VALUES ('col1', 'val1'); SELECT LAST_INSERT_ID(); # 3`

JSON

https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

GEOMETRIE

https://dev.mysql.com/doc/refman/5.7/en/spatial-analysis-functions.html

Exemples :

SELECT Area(ConvexHull(GeomFromText(points))) as area
  FROM (
      SELECT CONCAT('MULTIPOINT(', GROUP_CONCAT(CONCAT(x, " ", y) SEPARATOR ","), ')') as points
      FROM `places`
  ) as tmp;

Codefights Calculer la surface d’un ensemble de points

SELECT a.name AS place1, b.name AS place2
FROM sights AS a, sights AS b
WHERE a.name < b.name
AND DISTANCE(POINT(a.x, a.y), POINT(b.x, b.y)) < 5
ORDER BY place1, place2;

Codefights Places of interests

SELECT ROUND(SUM(
        DISTANCE(POINT(a.x,a.y), POINT(b.x,b.y))
),9) as total
FROM cities AS a
INNER JOIN cities AS b ON b.id = (a.id + 1);

Codefights Route length

SELECT id1,
    SUBSTRING_INDEX(GROUP_CONCAT(id2 ORDER BY distance, id2), ',', 1) as id2
FROM (
    SELECT a.id AS id1, b.id AS id2, ROUND(DISTANCE(POINT(a.x,a.y), POINT(b.x,b.y))) as distance
    FROM positions as a, positions as b
    WHERE a.id != b.id
    ORDER BY id1, distance, id2
) as tmp
GROUP BY id1;

Codefights Closest cells

This site is open source. Improve this page.