CHAPITRE VIII

FONCTIONS AVANCEES

I. INTRODUCTION
Cette unité est une extension de l'unité précédente, où différents opérateurs et fonctions ont été
discutés en détail. Cet appareil traite exclusivement des fonctions de recherche textuelle. Il décrit
également les fonctions Distribution, Bit, Cryptage et Information. Il décrit également en détail
l'ensemble de fonctions et de modificateurs pouvant être utilisés avec le modificateur GROUP BY
avec des exemples appropriés.

OBJECTIFS
Après avoir étudié cette unité, vous devriez être capable de:

 Expliquer et appliquer les fonctions de recherche Full text

 Décrire diverses fonctions de casting

 Discuter de l'importance des fonctions Bit, Cryptage et Information

 Discuter des fonctions et des modificateurs à utiliser avec les clauses Group BY

II. FONCTIONS DE RECHERCHE DE TEXTE INTEGRAL

MySQL prend en charge l'indexation de texte intégral et la recherche:

Un index de texte intégral dans MySQL est un index de type FULLTEXT.

Les index de texte intégral ne peuvent être utilisés qu'avec des tables MyISAM et ne peuvent être
créés que pour des colonnes CHAR, VARCHAR ou TEXT.

Une définition d'index FULLTEXT peut être donnée dans l'instruction CREATE TABLE
lorsqu'une table est créée ou ajoutée ultérieurement à l'aide d’ALTER TABLE ou CREATE
INDEX.
Pour les grands ensembles de données, il est beaucoup plus rapide de charger vos données dans
une table sans index FULLTEXT, puis de créer l'index par la suite, plutôt que de charger des
données dans une table comportant un index FULLTEXT existant.

La recherche en texte intégral est effectuée à l'aide de la syntaxe MATCH () ... AGAINST.
MATCH () prend une liste séparée par des virgules qui nomme les colonnes à rechercher.
AGAINST prend une chaîne à rechercher et un modificateur facultatif indiquant le type de
recherche à effectuer. La chaîne de recherche doit être une chaîne littérale et non une variable ou
un nom de colonne. Il existe trois types de recherches en texte intégral:

 Une recherche booléenne interprète la chaîne de recherche en utilisant les règles d'un
langage de requête spécial. La chaîne contient les mots à rechercher. Elle peut également
contenir des opérateurs qui spécifient des exigences telles qu'un mot doit être présent ou
absent dans les lignes correspondantes, ou qu'il doit être pondéré plus haut ou plus bas
que la normale. Les mots courants tels que "certains" ou "alors" sont des mots vides et ne
correspondent pas s'ils sont présents dans la chaîne de recherche. Le modificateur IN
BOOLEAN MODE spécifie une recherche booléenne.

 Une recherche en langage naturel interprète la chaîne de recherche comme une phrase en
langage humain naturel (une phrase en texte libre). Il n'y a pas d'opérateurs spéciaux. La
liste de mots vides s'applique. En outre, les mots présents dans 50% ou plus des lignes sont
considérés comme communs et ne correspondent pas. Les recherches en texte intégral sont
des recherches en langage naturel si le modificateur IN NATURAL LANGUAGE MODE
est fourni ou si aucun modificateur n'est fourni.

 Une recherche d'extension de requête est une modification d'une recherche en langage
naturel. La chaîne de recherche est utilisée pour effectuer une recherche en langage naturel.
Ensuite, les mots des lignes les plus pertinentes renvoyées par la recherche sont ajoutés à
la chaîne de recherche et la recherche est répétée. La requête renvoie les lignes de la
deuxième recherche. Le modificateur IN NATURAL LANGUAGE WITH QUERY
EXPANSION ou WITH QUERY EXPANSION spécifie une recherche d'extension de
requête.

Les modificateurs IN NATURAL LANGUAGE MODE et IN NATURAL LANGUAGE WITH

QUERY EXPANSION ont été ajoutés dans MySQL 5.1.7.

II.1. RECHERCHES BOOLEENNES EN TEXTE INTEGRAL

MySQL peut effectuer des recherches booléennes en texte intégral à l'aide du modificateur IN
BOOLEAN MODE:

APPLICATION
1ere étape :
Création de la table (nous créerons la table sans l’index FULLTEXT car il est très difficile de le
faire pendant la création de la table, il est donc alors conseillé de le faire après la création de celle-
ci à travers la commande ALTER TABLE comme indiquer ci-dessous)
D’où

Apres la description, nous aurons :

2eme étape : insertion des données dans la table.

D’où on aura:
3eme étape : la recherche
Pour le faire, c’est très simple. Il suffit juste de bien tenir compte des syntaxe ci-dessous :
Application :

4eme étape : testons la recherche sans le mot MySQL mais avec le mot langage dépendant juste
de la colonne titre :
5eme étape : cette fois-ci, essayons la recherche avec la dépendance des colonnes titre et
description

6eme étape : essayons maintenant sans langage et sans MySQL

7eme étape : avec le mot langage sans MySQL

Puis les exemples qui suivent :

NB : Il est possible d’entamer la recherche avec juste le mot qui existe (ex : + MySQL), le contraire
n’est pas possible.
En un mot, la recherche s’est effectuée à travers toutes les lignes dans les deux colonnes (titre
et description) c’est adire toute ligne contenant le mot MySQL, a été affiché en dehors du mot
YourSQL.
Les opérateurs + et - indiquent qu'un mot doit être présent ou absent, respectivement, pour qu'une
correspondance se produise. Ainsi, cette requête récupère toutes les lignes contenant le mot
"MySQL" mais ne contenant pas le mot "YourSQL".

Remarque: pour implémenter cette fonctionnalité, MySQL utilise ce que l’on appelle parfois la
logique booléenne implicite, dans laquelle:
+ signifie ET, - signifie NOT.

[Pas d'opérateur] impliquant OU

Les recherches booléennes en texte intégral présentent les caractéristiques suivantes:

 Ils n'utilisent pas le seuil de 50%. (pas de recherche à travers le mot contenant une partie
du mot en recherche)

 Ils ne trient pas automatiquement les lignes par ordre de pertinence décroissante. Vous
pouvez voir ceci dans le résultat de la requête précédente: La ligne avec la pertinence la plus
 élevée est celle qui contient deux fois «MySQL», mais elle est répertoriée en dernier, pas
en premier.

 Ils peuvent fonctionner même sans index FULLTEXT, bien qu'une recherche exécutée de
cette manière serait assez lente.

 Les paramètres de texte intégral de longueur de mot minimale et maximale s'appliquent.

 La liste de mots vides s'applique.

 La fonction de recherche en texte intégral booléen prend en charge les opérateurs suivants:

o +: un signe plus indique que ce mot doit être présent dans chaque ligne renvoyée.

o -: Un premier signe moins indique que ce mot ne doit figurer dans aucune des
lignes renvoyées.

REMARQUE:
 L'opérateur - agit uniquement pour exclure les lignes qui sont par ailleurs mises en
correspondance avec d'autres termes de recherche. Ainsi, une recherche en mode booléen
qui ne contient que les termes précédés de - renvoie un résultat vide. Il ne renvoie pas
«toutes les lignes, sauf celles contenant l'un des termes exclus».

 (Pas d'opérateur): Par défaut (lorsque ni + ni - n'est spécifié), le mot est facultatif, mais les
lignes qui le contiennent sont mieux notées. Cela imite le comportement de MATCH () ...
AGAINST () sans le modificateur IN BOOLEAN MODE.

 ><: Ces deux opérateurs permettent de modifier la contribution d'un mot à la valeur de
pertinence attribuée à une ligne. L'opérateur> augmente la contribution et l'opérateur
<la diminue.

 (): Les parenthèses regroupent les mots en sous-expressions. Les groupes entre parenthèses
peuvent être imbriqués.
 ~: Un tilde de premier plan joue le rôle d'opérateur de négation, ce qui a pour effet de rendre
négatif la contribution du mot à la pertinence de la ligne. Ceci est utile pour marquer des
mots «bruits». Une ligne contenant un tel mot a une note inférieure à celle des autres, mais
n'est pas totalement exclue, contrairement à l'opérateur -.

 *: l'astérisque sert d'opérateur de troncature (ou de caractère générique). Contrairement aux

autres opérateurs, il convient de l'ajouter au mot à affecter. Les mots correspondent s'ils
commencent par le mot précédant l'opérateur *.
Cela affichera la recherche concernant tous les mots commençant par la lettre ou le mot
précèdent l’astérisque c’est-à-dire t dans notre cas.

Si un mot vide ou un mot trop court est spécifié avec l'opérateur de troncature, il ne sera pas extrait
d'une requête booléenne. Par exemple, une recherche sur '+ mot + mot d'arrêt *' 'renverra
probablement moins de lignes qu'une recherche sur' + mot + mot d'arrêt ', car la requête précédente
reste telle quelle et nécessite que le mot d'arrêt * soit présent dans un document. La dernière requête
est transformée en + mot.

 ": Une phrase placée entre guillemets (") ne correspond qu'aux lignes qui contiennent la
phrase littéralement, telle qu'elle a été saisie. Le moteur de texte intégral divise la phrase
en mots et effectue une recherche dans l'index FULLTEXT pour les mots. Les caractères
qui ne sont pas des mots ne doivent pas nécessairement correspondre exactement: la
recherche d'une phrase requiert uniquement que les correspondances contiennent
exactement les mêmes mots que la phrase et dans le même ordre. Par exemple, "phrase
test" correspond à "test, phrase".

Si la phrase ne contient aucun mot figurant dans l'index, le résultat est vide. Par exemple, si
tous les mots sont soit des mots vides, soit plus courts que la longueur minimale des mots indexés,
le résultat est vide.

II.2. RECHERCHES EN TEXTE INTEGRAL AVEC EXTENSION DE

REQUETE
La recherche en texte intégral prend en charge le développement de requêtes (et en particulier sa
variante «Développement de requêtes en aveugle»). Cela est généralement utile lorsqu'une
expression de recherche est trop courte, ce qui signifie souvent que l'utilisateur se fie sur la
connaissance implicite que le moteur de recherche de texte intégral manque. Par exemple, un
utilisateur recherchant «base de données» peut en réalité signifier que «MySQL», «Oracle»,
«DB2» et «RDBMS» sont des expressions qui doivent correspondre à «bases de données» et
doivent également être renvoyées. Ceci est une connaissance implicite.

Le développement de requête aveugle (également appelé retour automatique de pertinence) est

activé en ajoutant WITH QUERY EXPANSION ou IN NATURAL LANGUAGE MODE
WITH QUERY EXPANSION à la suite de la phrase de recherche. Cela fonctionne en effectuant
la recherche deux fois, où la phrase de recherche pour la deuxième recherche est la phrase de
recherche originale concaténée avec les quelques documents les plus pertinents de la première
recherche. Ainsi, si l'un de ces documents contient le mot «bases de données» et le mot «MySQL»,
la deuxième recherche trouve les documents contenant le mot «MySQL» même s'ils ne contiennent
pas le mot «base de données». L'exemple suivant montre cette différence:

REMARQUE:
Etant donné que l'expansion des requêtes à l'aveugle tend à augmenter considérablement le bruit
en renvoyant des documents non pertinents, il est judicieux de l'utiliser uniquement lorsqu'une
expression de recherche est plutôt courte.

II.3. RESTRICTIONS DE TEXTE INTEGRAL

Les recherches en texte intégral sont prises en charge uniquement pour les tables MyISAM.

Les recherches en texte intégral peuvent être utilisées avec la plupart des jeux de caractères multi-
octets. L'exception est que pour Unicode, le jeu de caractères utf8 peut être utilisé, mais pas le jeu
de caractères ucs2. Cependant, bien que les index FULLTEXT sur les colonnes ucs2 ne puissent
pas être utilisés, vous pouvez effectuer des recherches en mode BOOLEAN sur une colonne ucs2
ne disposant pas d'un tel index.

Les langues idéographiques telles que le chinois et le japonais n'ont pas de séparateurs de mots.
Par conséquent, l'analyseur FULLTEXT ne peut pas déterminer où les mots commencent et
finissent dans ces langages et d'autres.

Bien que l'utilisation de plusieurs jeux de caractères dans une même table soit prise en charge,
toutes les colonnes d'un index FULLTEXT doivent utiliser le même jeu de caractères et le même
classement.

La liste de colonnes MATCH () doit correspondre exactement à la liste de colonnes de la définition

d'index FULLTEXT de la table, sauf si MATCH () est IN MODE BOOLEAN. Les recherches en
mode booléen peuvent être effectuées sur des colonnes non indexées, même si elles sont
susceptibles d'être lentes.

L'argument de AGAINST () doit être une chaîne constante.

II.4. REGLAGE PRECIS DE LA RECHERCHE DE TEXTE INTEGRAL

MYSQL
La capacité de recherche en texte intégral de MySQL comporte peu de paramètres réglables par
l'utilisateur. Vous pouvez exercer plus de contrôle sur le comportement de recherche en texte
intégral si vous disposez d'une distribution source MySQL car certaines modifications nécessitent
des modifications du code source.

Notez que la recherche en texte intégral est soigneusement réglée pour une efficacité maximale. La
modification du comportement par défaut dans la plupart des cas peut en réalité diminuer
l'efficacité. Ne modifiez pas les sources MySQL sauf si vous savez ce que vous faites.

La plupart des variables de texte intégral décrites dans cette section doivent être définies au moment
du démarrage du serveur. Un redémarrage du serveur est nécessaire pour les changer. Ils ne peuvent
pas être modifiés pendant l'exécution du serveur.

Certaines modifications de variables nécessitent la reconstruction des index FULLTEXT dans vos
tables. Les instructions pour le faire sont données à la fin de cette section.

Les longueurs minimale et maximale des mots à indexer sont définies par les variables système
ft_min_word_len et ft_max_word_len. La valeur minimale par défaut est de quatre caractères.
Le maximum par défaut dépend de la version. Si vous modifiez l'une ou l'autre valeur, vous devez
reconstruire vos index FULLTEXT. Par exemple, si vous souhaitez que les mots de trois caractères
puissent faire l'objet d'une recherche, vous pouvez définir la variable ft_min_word_len en mettant
les lignes suivantes dans un fichier d'options:
Ensuite, vous devez redémarrer le serveur et reconstruire vos index FULLTEXT. Notez en
particulier les remarques concernant myisamchk dans les instructions qui suivent cette liste.

Pour remplacer la liste de mots vides par défaut, définissez la variable système ft_stopword_file.
La valeur de la variable doit être le chemin du fichier contenant la liste des mots vides ou la chaîne
vide pour désactiver le filtrage des mots vides. Après avoir modifié la valeur de cette variable ou
le contenu du fichier de mots vides, redémarrez le serveur et reconstruisez vos index FULLTEXT.

La liste de mots vides est de forme libre. En d'autres termes, vous pouvez utiliser n'importe quel
caractère non alphanumérique, tel que nouvelle ligne, espace ou virgule, pour séparer les mots
vides. Les exceptions sont le caractère de soulignement ( _) et une seule apostrophe (') qui sont
traités comme faisant partie d'un mot. Le jeu de caractères de la liste de mots vides est le jeu de
caractères par défaut du serveur.

Le seuil de 50% pour les recherches en langage naturel est déterminé par le système de pondération
choisi. Pour le désactiver, recherchez la ligne suivante dans storage / myisam / ftdefs.h:

Changer cette ligne en ce qui suit :

Recompilez ensuite MySQL. Il n'est pas nécessaire de reconstruire les index dans ce cas.

Remarque: en apportant cette modification, vous réduisez considérablement la capacité de

MySQL à fournir des valeurs de pertinence adéquates pour la fonction MATCH (). Si vous avez
réellement besoin de rechercher de tels mots courants, il serait préférable d’utiliser plutôt le mode
IN BOOLEAN, qui ne respecte pas le seuil de 50%.

II.5. RECHERCHE DE TEXTE INTEGRAL TODO

Amélioration des performances pour toutes les opérations FULLTEXT.

 Opérateurs de proximité
  Prise en charge des mots «toujours indexés». Celles-ci peuvent être toutes les chaînes que
l'utilisateur souhaite traiter comme des mots, tels que «C ++», «AS / 400» ou «TCP / IP».

 Prise en charge de la recherche en texte intégral dans les tables MERGE.

 Support pour UCS-2.

 Faites en sorte que la liste de mots vides dépende de la langue du jeu de données.

 Racine (dépend de la langue du jeu de données).

 Préparateur d'UDF générique, utilisable par l'utilisateur.

 Rendre le modèle plus flexible (en ajoutant des paramètres ajustables à FULLTEXT dans
les instructions CREATE TABLE et ALTER TABLE).

QUESTIONS D'AUTO-EVALUATION
1. Une définition d'index ______ peut être donnée dans l'instruction CREATE TABLE
lorsqu'une table est créée ou ajoutée ultérieurement à l'aide d’ALTER TABLE ou CREATE
INDEX.

III. FONCTIONS DE CASTING

 CAST (type expr AS)

 CONVERT (type, expr)

 CONVERT (expr USING transcodingingname)

Les fonctions CAST () et CONVERT () peuvent être utilisées pour prendre une valeur d'un type
et produire une valeur d'un autre type.

La valeur de type peut être l'une des suivantes:

 BINARY
 CHAR
 DATE
 DATETIME
 SIGNED [INTEGER]
 TIME
 UNSIGNED [INTEGER]
CAST () et CONVERT () sont disponibles à partir de MySQL 4.0.2. Le type de conversion CHAR
est disponible à partir de 4.0.6. Le formulaire USING de CONVERT () est disponible à partir de
la version 4.1.0.
CAST () et CONVERT (... USING ...) sont une syntaxe SQL standard. La forme non USING de
CONVERT () est la syntaxe ODBC.
CONVERT () avec USING est utilisé pour convertir les données entre différents jeux de
caractères. Dans MySQL, les noms de transcodage sont les mêmes que les noms de jeux de
caractères correspondants. Par exemple, cette instruction convertit la chaîne 'abc' dans le jeu de
caractères par défaut du serveur en chaîne correspondante dans le jeu de caractères utf8:

 Les fonctions de conversion sont utiles lorsque vous souhaitez créer une colonne avec
un type spécifique dans un fichier.
« Instruction CREATE ... SELECT » :

APPLICATION :

 Les fonctions peuvent également être utiles pour trier les colonnes ENUM par ordre lexical.
Normalement, le tri des colonnes ENUM a lieu à l'aide des valeurs numériques internes.
En convertissant les valeurs en CHAR, vous obtenez un tri lexical:

APPLICATION :
Créons d’abord une nouvelle table que nous nommerons « nouvel_1 » et qui comportera une
colonne de type ENUM.
Etape 2 :

CAST (str AS BINARY) est la même chose que BINARY str. CAST (expr AS CHAR) traite
l'expression en tant que chaîne avec le jeu de caractères par défaut.

REMARQUE:
 Vous ne devez pas utiliser CAST () pour extraire des données dans différents formats, mais
plutôt utiliser des fonctions de chaîne telles que LEFT () ou EXTRACT ().
  Pour convertir une chaîne en valeur numérique, vous n'avez normalement rien à faire.
Utilisez simplement la valeur de chaîne pour ainsi dire un nombre:
Application

 Si vous utilisez un nombre dans un contexte de chaîne, le nombre sera automatiquement

converti en chaîne BINARY.

Application :

QUESTIONS D'AUTO-EVALUATION

2. Les fonctions ____ et ____ peuvent être utilisées pour prendre une valeur d'un type et
produire une valeur d'un autre type.

IV. AUTRES FONCTIONS

IV.1 FONCTIONS DE BITS

MySQL utilise l'arithmétique BIGINT (64 bits) pour les opérations sur les bits, de sorte que ces
opérateurs ont une plage maximale de 64 bits.

 | BITWISE OU:
Application :

 Le résultat est un entier non signé de 64 bits. & BITWISE ET:

Application :

Le résultat est un entier non signé de 64 bits.

 ^ XOR bit à bit:

Application :
Le résultat est un entier non signé de 64 bits.

 <<SHIFTL ou décalé le numéro à gauche :

Application :

Le résultat est un entier non signé de 64 bits.

 >>: Décale le numéro long (BIGINT) vers la droite.

Application :

Le résultat est un entier non signé de 64 bits.

 ~: Inverser tous les bits.

Application :
Le résultat est un entier non signé de 64 bits.

 BIT_COUNT (N): Retourne le nombre de bits définis dans l'argument N.

Application :
IV.2. FONCTIONS DE CHIFFREMENT

 AES_ENCRYPT (str, key_str)

 AES_DECRYPT (str, key_str)

Ces fonctions permettent le cryptage / décryptage des données à l'aide de l'algorithme officiel
AES (Advanced Encryption Standard), précédemment appelé RIJNDAEL. Un codage avec une
longueur de clé de 128 bits est utilisé, mais vous pouvez l'étendre jusqu'à 256 bits en modifiant la
source. Nous avons choisi 128 bits parce que c'est beaucoup plus rapide et généralement assez
sécurisé.

Les arguments d'entrée peuvent avoir n'importe quelle longueur. Si l'un des arguments est
NULL, le résultat de cette fonction est également NULL.

Comme AES est un algorithme de niveau bloc, le remplissage est utilisé pour coder des chaînes
de longueur inégale. La longueur de chaîne de résultat peut donc être calculée sous la forme
16 * (trunc (chaîne_longue / 16) +1).

Si AES_DECRYPT () détecte des données non valides ou un remplissage incorrect, il renvoie

NULL.

Toutefois, il est possible pour AES_DECRYPT () de renvoyer une valeur non NULL
(éventuellement une erreur) si les données d'entrée ou la clé sont non valides.

Vous pouvez utiliser les fonctions AES pour stocker des données sous une forme cryptée en
modifiant vos requêtes:

APPLICATION :
1. LA LONGUEUR DE LA CHAINE DE RESULTAT :
Lorsque nous cryptons une expression, celle-ci devient illisible c’est-à-dire qu’elle se transforme
en une expression incompréhensive par l’humain. Cette expression cryptée, a une longueur
déterminable à travers la formule ci-dessous :
16 * (trunc (longueur_du_mot / 16) + 1)
Comment cela se calcule vraiment ?
Supposons que nous avons eu à créer une table t comportant deux (2) colonnes text et password.
Créons la table t :
Retenons que nos deux colonnes sont en varchar, et colonne qui doit subir les transformations de
texte ou les cryptages est bien sûr la colonne password et elle est en varchar (50).
Commençons alors les calculs :
Soit la formule : 16 * (trunc (longueur_du_mot / 16) + 1)
 Avec la longueur du mot étant 50, nous aurons donc 16 * (trunc (50/16) + 1)
 16 * (3,125 + 1)
 16 * 4,125
 66 en minimum, environ varbinary(100).
Mais pourquoi cela donnerait-il un varbinary au lieu du varchar ?
Comme on a su le dire ci-dessus, après cryptage d’une expression celle-ci se transforme en une
expression incompréhensible qui est en réalité une expression en varbinary. Nous retenons donc
que pour crypter une expression, celle-ci doit être en varchar afin d’être transformé en varbinary.

2. COMMENT CRYPTER ?
Crypter une expression est une chose très facile à faire, pourquoi ?
Parce qu’il est juste question de prendre en référence ou en considération trois (3) éléments :
 AES_ENCRYPT (utilisé pour le cryptage)
 Le mot à crypter et
 La clé de cryptage (qui ne doit être oubliée).
Soit la table ci-dessous :

Insérons maintenant :

Retenons qu’ici nous avons eu à utiliser la clé qui s’appelle la_cle. Pour les raisons de sécurité, il
est demandé de jamais oublier la clé sinon il nous sera impossible de décrypter les expressions
cryptées.
Vous pouvez obtenir encore plus de sécurité en ne transférant pas la clé sur la connexion pour
chaque requête. Pour ce faire, vous pouvez la stocker dans une variable côté serveur au moment
de la connexion.
Exemple:

Insérons pour voir le résultat :

NB : Pour la 3eme ligne, nous l’avons encrypté avec la clé incorporée dans la variable @password.
DECRYPTAGE

CRYPTAGE AVEC DES :

AES_ENCRYPT () et AES_DECRYPT () ont été ajoutés à MySQL 4.0.2 et peuvent être
considérés comme les fonctions de cryptage les plus sécurisées sur le plan cryptographique
actuellement disponibles dans MySQL.

DECODE (crypt_str, pass_str): décrypte la chaîne cryptée crypt_str en utilisant pass_str

cozqse2mme mot de passe. crypt_str doit être une chaîne renvoyée par ENCODE ().
ENCODE (str, pass_str): Cryptez str en utilisant pass_str comme mot de passe. Pour déchiffrer le
résultat, utilisez DECODE (). Le résultat est une chaîne binaire de la même longueur que la chaîne.
Si vous souhaitez l'enregistrer dans une colonne, utilisez un type de colonne BLOB.

DES_DECRYPT (str_to_decrypt [, key_str]): déchiffre une chaîne chiffrée avec DES_ENCRYPT

(). En cas d'erreur, cette fonction renvoie NULL. Notez que cette fonction ne fonctionne que si
MySQL a été configuré avec le support SSL.

 Si aucun argument key_str n’est donné, DES_DECRYPT () examine le premier octet de

la chaîne chiffrée pour déterminer le numéro de clé DES utilisé pour chiffrer la chaîne
d'origine, puis lit la clé à partir du fichier de clé DES pour décrypter le message. Pour que
cela fonctionne, l'utilisateur doit disposer du privilège SUPER. Le fichier de clé peut
être spécifié avec l'option de serveur --des-key-file.

 Si vous transmettez à cette fonction un argument key_str, cette chaîne est utilisée comme
clé de déchiffrement du message.
 Si l'argument str_to_decrypt ne ressemble pas à une chaîne chiffrée, MySQL renverra le
str_to_decrypt donné.

Crypte la chaîne avec la clé donnée en utilisant l'algorithme Triple-DES. En cas d'erreur, cette
fonction renvoie NULL.

APPLICATION :

a. Sans clé

b. Avec clé

DECRYPTAGE DE DES :
Soit :
 a. Sans clé :

b. Avec clé :

NB :

 Notez que cette fonction ne fonctionne que si MySQL a été configuré avec le support
SSL.
 Le Cryptage sans ne se fait qu’avec le système DES_ENCRYPT mais pas avec
AES_ENCRYPT.

La clé de chiffrement à utiliser est choisie en fonction du deuxième argument de DES_ENCRYPT

(), s'il en existe un:

CRYPTAGE AVEC ENCRYPT (str [, salt])

Cryptez str en utilisant l'appel système Unix crypt (). L'argument salt devrait être une chaîne avec
deux caractères. (Depuis MySQL 3.22.16, salt peut contenir plus de deux caractères.)
ENCRYPT () ignore tous les caractères sauf les 8 premiers caractères de str, du moins sur certains
systèmes. Ce comportement est déterminé par la mise en œuvre de l'appel système sous-jacent
crypt (). Si crypt () n'est pas disponible sur votre système, ENCRYPT () renvoie toujours
NULL. C'est pourquoi nous vous recommandons d'utiliser plutôt MD5 () ou SHA1 (), car ces deux
fonctions existent sur toutes les plateformes.

MD5 (str)
Calcule une somme de contrôle MD5 128 bits pour la chaîne. La valeur est renvoyée sous forme
de chaîne de 32 chiffres hexadécimaux, ou NULL si l'argument était NULL. La valeur de retour
peut, par exemple, être utilisée comme clé de hachage.

Il s'agit de "l'algorithme MD5 Message-Digest de RSA Data Security, Inc.".

Application :

a. Sélection ordinaire :

b. Sélection depuis la table :

LE CRYPTAGE PASSWORD

PASSWORD (str), OLD_PASSWORD (str)

Calcule et retourne une chaîne de mot de passe à partir du mot de passe en texte brut str, ou NULL
si l'argument était NULL. Cette fonction est utilisée pour chiffrer les mots de passe MySQL pour
le stockage dans la colonne Mot de passe de la table des droits des utilisateurs.

Le cryptage PASSWORD () est unidirectionnel (non réversible).

APPLICATION :

a. Sélection ordinaire
 b. Sélection depuis de la table :

PASSWORD () n'effectue pas le cryptage de mot de passe de la même manière que les mots de
passe Unix sont cryptés.

REMARQUE:

 La fonction PASSWORD () est utilisée par le système d'authentification de MySQL

Server. Vous ne devez pas l'utiliser dans vos propres applications.

 Pour cela, utilisez MD5 () ou SHA1 () à la place. Consultez également la RFC 2195 pour
plus d'informations sur la gestion sécurisée des mots de passe et de l'authentification dans
votre application.

CRYPTAGE AVEC SHA1 (str)

SHA (str) Calcule une somme de contrôle SHA1 de 160 bits pour la chaîne, comme décrit dans la
RFC 3174 (algorithme de hachage sécurisé). La valeur est renvoyée sous forme de chaîne de 40
chiffres hexadécimaux, ou NULL si l'argument était NULL. L’une des utilisations possibles de
cette fonction est la clé de hachage. Vous pouvez également l'utiliser en tant que fonction
sécurisée par cryptographie pour stocker les mots de passe.

a. Sélection ordinaire :
 b. Sélection depuis la table :

SHA1 () a été ajouté à MySQL 4.0.2 et peut être considéré comme un équivalent plus sûr du point
de vue cryptographique de MD5 (). SHA () est synonyme de SHA1 ().

IV.3. FONCTIONS D'INFORMATION

FONCTION BENCHMARK (compter, expr)

La fonction BENCHMARK () exécute l'expression expr plusieurs fois. Il peut être utilisé pour
indiquer la rapidité avec laquelle MySQL traite l'expression. La valeur de résultat est toujours
zéro 0. L'utilisation prévue provient du client MySQL, qui indique les temps d'exécution des
requêtes:
Le temps indiqué est le temps écoulé côté client, et non pas le temps CPU du côté serveur. Il peut
être judicieux d’exécuter BENCHMARK () plusieurs fois et d’interpréter le résultat en fonction de
l’importance de la charge de la machine serveur.

FONCTION CHARSET (str)

Renvoie le jeu de caractères de l'argument de chaîne.

CHARSET () a été ajouté à MySQL 4.1.0.

FONCTION COERCIBILITÉ (str)

Renvoie la valeur de coercibilité du classement de l'argument de chaîne.

Les valeurs de retour ont les significations suivantes:

Les valeurs inférieures ont une priorité plus élevée. COERCIBILITY () a été ajouté dans MySQL
4.1.1.

FONCTION COLLATION (str)

Renvoie le classement du jeu de caractères de l'argument de chaîne.

COLLATION () a été ajouté à MySQL 4.1.0.

FONCTION CONNECTION_ID ()

Renvoie l'ID de connexion (ID de thread) de la connexion. Chaque connexion a son propre
identifiant unique.

FONCTION CURRENT USER ()

Renvoie le nom d'utilisateur et le nom d'hôte avec lesquels la session en cours a été authentifiée.
Cette valeur correspond au compte utilisé pour évaluer vos privilèges d'accès. Il peut être différent
de la valeur de USER ().
FONCTION DATABASE () ;
Renvoie le nom de la base de données (actuel) par défaut.

S'il n'y a pas de base de données par défaut, DATABASE () renvoie NULL à partir de MySQL
4.1.1 et la chaîne vide avant celle-ci.

FONCTION SQL_CALC_FOUND_ROWS ()
Le second SELECT retournera un nombre indiquant le nombre de lignes que le premier SELECT
aurait renvoyé s'il avait été écrit sans la clause LIMIT. (Si l'instruction SELECT précédente
n'inclut pas l'option SQL_CALC_FOUND_ROWS, FOUND_ROWS () peut renvoyer un résultat
différent lorsque LIMIT est utilisé ou non.).

REMARQUE: Si vous utilisez SELECT SQL_CALC_FOUND_ROWS, MySQL doit calculer

le nombre de lignes dans le jeu de résultats complet. Cependant, cela est plus rapide que de ré
exécuter la requête sans LIMIT, car le jeu de résultats n'a pas besoin d'être envoyé au client.
SQL_CALC_FOUND_ROWS et FOUND_ROWS () peuvent être utiles dans les situations où
vous souhaitez limiter le nombre de lignes renvoyées par une requête, mais également déterminer
le nombre de lignes du jeu de résultats complet sans l'exécuter à nouveau. Un exemple est un script
Web qui présente un affichage paginé contenant des liens vers les pages montrant d'autres sections
d'un résultat de recherche. L'utilisation de FOUND_ROWS () vous permet de déterminer combien
d'autres pages sont nécessaires pour le reste du résultat.

FONCTION LAST_INSERT_ID ()
LAST_INSERT_ID (expr)
Retourne la dernière valeur générée automatiquement qui a été insérée dans une colonne
AUTO_INCREMENT.

Le dernier ID généré est conservé sur le serveur connexion par connexion. Cela signifie que la
valeur renvoyée par la fonction à un client donné est la valeur AUTO_INCREMENT la plus récente
générée par ce client. La valeur ne peut pas être affectée par d'autres clients, même s'ils génèrent
leurs propres valeurs AUTO_INCREMENT. Ce comportement garantit que vous pouvez récupérer
votre propre ID sans vous préoccuper de l'activité des autres clients et sans avoir besoin de verrous
ou de transactions.

 La valeur de LAST_INSERT_ID () n'est pas modifiée si vous mettez à jour la colonne

AUTO_INCREMENT d'une ligne avec une valeur non magique (c'est-à-dire, une valeur
autre que NULL et non 0). Si vous insérez plusieurs lignes en même temps avec une
instruction insert, LAST_INSERT_ID () renvoie la valeur de la première ligne insérée. La
raison en est qu'il est possible de reproduire facilement la même instruction INSERT sur un
autre serveur.
 Si expr est donné en argument à LAST_INSERT_ID (), alors la valeur de l'argument est
renvoyée par la fonction et définie comme la prochaine valeur à renvoyer par
LAST_INSERT_ID (). Ceci peut être utilisé pour simuler des séquences:
  L'instruction UPDATE incrémente le compteur de séquence et provoque l'appel suivant à
LAST_INSERT_ID () pour renvoyer la valeur mise à jour. L'instruction SELECT
récupère cette valeur.

FONCTION VERSION ()

Retourne une chaîne qui indique la version du serveur MySQL.

REMARQUE: Si votre chaîne de version se termine par -log, cela signifie que la journalisation
est activée.

IV.4 FONCTIONS DIVERSES

FONCTION FORMAT (X, D)
Formate le nombre X en un format du type '#, ###, ###. ##', Arrondi à D décimales et renvoie le
résultat sous forme de chaîne. Si D est égal à 0, le résultat n'aura pas de point décimal ni de fraction.
Exemple 1:
 QUESTIONS D'AUTO-EVALUATION
3. La sortie de l'opération AND au niveau des bits 29 et 15 suivante est _____.

4. Il est possible pour AES_DECRYPT () de renvoyer un _______ (éventuellement un

déchet) si les données d'entrée ou la clé sont invalides.

V. FONCTIONS ET MODIFICATEURS A UTILISER AVEC LES

CLAUSES GROUP BY

V.1. FONCTIONS GROUP BY (AGRÉGAT)

Cette section décrit les fonctions de groupe (agrégat) qui agissent sur des ensembles de valeurs.
Sauf indication contraire, les fonctions de groupe ignorent les valeurs NULL.

Si vous utilisez une fonction group dans une instruction ne contenant aucune clause GROUP BY,
cela équivaut à un regroupement sur toutes les lignes.

REMARQUE :

 Les fonctions d'agrégation SUM () et AVG () ne fonctionnent pas avec les valeurs
temporelles. (Ils convertissent les valeurs en nombres, ce qui perd la partie après le premier
caractère non numérique.).

 Pour contourner ce problème, vous pouvez convertir en unités numériques, effectuer

l'opération d'agrégation et reconvertir en une valeur temporelle.

Exemples:
Soit…
FONCTION AVG ([DISTINCT] expr)
Retourne la valeur moyenne d’expr. L’option DISTINCT peut être utilisée à partir de MySQL
5.0.3 pour renvoyer la moyenne des valeurs distinctes d’expr.
AVG () renvoie NULL s'il n'y a pas de lignes correspondantes.
Soit….
FONCTION BIT_AND (expr)
Renvoie le bit AND et tous les bits d’expr. Le calcul est effectué avec une précision de 64 bits
(BIGINT).

Cette fonction renvoie 18446744073709551615 s'il n'y a pas de lignes correspondantes. (Il s'agit
de la valeur d'une valeur BIGINT non signée avec tous les bits définis sur 1.)

FONCTION BIT_OR (expr)

Renvoie le OU au niveau du bit de tous les bits dans expr. Le calcul est effectué avec une
précision de 64 bits (BIGINT).
Cette fonction renvoie 0 s'il n'y a pas de lignes correspondantes.

FONCTION BIT_XOR (expr)

Renvoie le XOR au niveau du bit de tous les bits dans expr. Le calcul est effectué avec une
précision de 64 bits (BIGINT).
Cette fonction renvoie 0 s'il n'y a pas de lignes correspondantes.

FONCTION COUNT (expr)

Retourne le nombre de valeurs non NULL dans les lignes extraites par une instruction SELECT.
Exemple:

COUNT (*) diffère quelque peu en ce sens qu'il renvoie le nombre de lignes extraites, qu'elles
contiennent ou non des valeurs NULL.
FONCTION COUNT (DISTINCT expr, [expr ...])

Retourne le nombre de valeurs différentes non NULL.

Exemple:

FONCTION GROUP_CONCAT (expr)

Cette fonction renvoie un résultat sous forme de chaîne avec les valeurs concaténées d'un groupe.
La syntaxe complète est la suivante:

GROUP_CONCAT () a été ajouté dans MySQL 4.1.

Exemple:

FONCTION MIN (expr), MAX (expr)

Renvoie la valeur minimale ou maximale de expr. MIN () et MAX () peuvent prendre un argument
de chaîne; dans ce cas, ils renvoient la valeur de chaîne minimale ou maximale.
Exemple:
FONCTION STD (expr), STDDEV (expr)
Renvoie l'écart type de expr (la racine carrée de VARIANCE ()). Ceci est une extension du SQL
standard. La forme STDDEV () de cette fonction est fournie pour la compatibilité Oracle.

FONCTION SUM (expr)

Retourne la somme de expr. Notez que si le jeu de retour n'a pas de lignes, il retourne NULL!

FONCTION VARIANCE (expr)

Renvoie la variance standard de expr (en considérant les lignes comme la population entière, et
non comme un échantillon; le nombre de lignes est donc un dénominateur). Il s’agit d’une extension
du langage SQL standard, disponible uniquement dans MySQL 4.1 ou ultérieur.

V.2. MODIFICATEURS GROUP BY

Depuis MySQL 4.1.1, la clause GROUP BY autorise un modificateur WITH ROLLUP qui
entraîne l'ajout de lignes supplémentaires à la sortie du résumé. Ces lignes représentent des
opérations récapitulatives de niveau supérieur (ou super agrégées). ROLLUP vous permet donc
de répondre à des questions à plusieurs niveaux d'analyse en une seule requête. Il peut être utilisé,
par exemple, pour prendre en charge les opérations OLAP (Online Analytical Processing).

À titre d’illustration, supposons qu’un tableau nommé sales comporte des colonnes année, pays,
produit et bénéfice permettant d’enregistrer la rentabilité des ventes:

Le contenu de la table peut être résumé chaque année avec un simple GROUP BY comme ceci:
Cette sortie affiche le bénéfice total pour chaque année, mais si vous souhaitez également
déterminer le total des bénéfices pour toutes les années, vous devez additionner vous-même les
valeurs individuelles ou exécuter une requête supplémentaire.

Vous pouvez également utiliser ROLLUP, qui fournit les deux niveaux d'analyse avec une seule
requête. L'ajout d'un modificateur WITH ROLLUP à la clause GROUP BY entraîne la génération
d'une autre ligne indiquant le total général pour toutes les valeurs de l'année:

La ligne de super-agrégat du total général est identifiée par la valeur NULL dans la colonne
année.
ROLLUP a un effet plus complexe lorsqu'il existe plusieurs colonnes GROUP BY. Dans ce cas,
chaque fois qu'il y a une «rupture» (modification de valeur) dans une colonne de regroupement
sauf la dernière, la requête génère une ligne de résumé super-agrégée supplémentaire.
Par exemple, sans ROLLUP, un résumé du tableau des ventes basé sur l'année, le pays et le
produit pourrait ressembler à ceci:

 La sortie indique des valeurs récapitulatives uniquement au niveau de l'analyse année /

pays / produit.
 Lorsque ROLLUP est ajouté, la requête génère plusieurs lignes supplémentaires:

Pour cette requête, l'ajout de ROLLUP force la sortie à inclure des informations de synthèse à
CINQ niveaux d'analyse, et non à un seul. Voici comment interpréter la sortie ROLLUP:
  Après chaque série de lignes de produits pour une année et un pays donnés, une ligne
récapitulative supplémentaire est générée, indiquant le total pour tous les produits. La
colonne produit de ces lignes est définie sur NULL.

 Après chaque série de lignes d'une année donnée, une ligne récapitulative supplémentaire
est générée. Elle affiche le total pour tous les pays et produits. Les colonnes pays et produits
de ces lignes sont définies sur NULL.

 Enfin, après toutes les autres lignes, une ligne récapitulative supplémentaire est générée.
Elle indique le total général pour toutes les années, tous les pays et tous les produits. Cette
ligne contient les colonnes année, pays et produits définies sur NULL.

QUESTIONS D'AUTO-EVALUATION
5. Les fonctions d'agrégation SUM () et AVG () ne fonctionnent pas avec les valeurs ____.

VI. RESUME
Cette unité couvrait diverses fonctions de recherche textuelles, de casting, de cryptage et
d’information. Il traite ensuite de diverses fonctions et modificateurs pouvant être utilisés avec la
clause GROUP BY.

1. Fonctions de recherche de texte intégral: Ces fonctions sont utilisées pour effectuer une
recherche de texte intégral ou des fonctions basées sur des chaînes. Ces fonctions incluent
les recherches booléennes, les recherches d'extension de requête, etc.

2. Fonctions de conversion: Les fonctions CAST () et CONVERT () peuvent être utilisées

pour prendre une valeur d'un type et produire une valeur d'un autre type. Ces fonctions sont
principalement utilisées pour effectuer des conversions entre différents types de données.

3. Autres fonctions: Les autres fonctions comprennent les fonctions Bit, Cryptage et
Information. Ils sont utilisés pour travailler sur les manipulations de bits, le cryptage des
données et la collecte d'informations à partir des sources de données.

4. Group by Clauses: La clause Group By est essentiellement utilisée avec les fonctions
d'agrégation telles que SUM, AVERAGE, etc. Ils peuvent également être utilisés pour
modifier la sortie d'une requête en fonction des besoins.

QUESTIONS SUR LES TERMINAUX

1. Décrivez les opérateurs prenant en charge les recherches booléennes en texte intégral.

2. Expliquez quelques fonctions de cryptage.