CALCULS

Fonctions récapitulatives

Les expressions SQL nécessitent souvent un prétraitement des données. À cette fin, des fonctions et des expressions spéciales sont utilisées.

Très souvent, vous devez savoir combien d'enregistrements correspondent à une requête particulière,quelle est la somme des valeurs d'une certaine colonne numérique, ses valeurs maximale, minimale et moyenne. Pour cela, des fonctions dites de synthèse (statistiques, agrégées) sont utilisées. Les fonctions récapitulatives traitent les jeux d'enregistrements spécifiés par une clause WHERE, par exemple. Si elles sont incluses dans la liste des colonnes suivant l'instruction SELECT, la table résultante contiendra non seulement les colonnes de la table de la base de données, mais également les valeurs calculées à l'aide de ces fonctions. Ce qui suit estliste des fonctions récapitulatives.

• COUNT (paramètre ) - renvoie le nombre d'enregistrements spécifié dans le paramètre. Si vous souhaitez obtenir le nombre de tous les enregistrements, vous devez spécifier un astérisque (*) en tant que paramètre. Si vous spécifiez un nom de colonne en paramètre, la fonction renverra le nombre d'enregistrements dans lesquels cette colonne a des valeurs autres que NULL. Pour savoir combien de valeurs distinctes une colonne contient, faites précéder son nom du mot-clé DISTINCT. Par exemple:

SELECTIONNER LE COMPTE (*) DE Clients ;

SELECTIONNER LE COUNT (Order_amount) FROM Clients ;

SELECTIONNER LE COMPTE (DISTINCT Order_amount) FROM Clients ;

Tenter d'exécuter la requête suivante entraînera un message d'erreur :

SELECTIONNER Région, COUNT (*) FROM Clients ;

• SOMME (paramètre ) - renvoie la somme des valeurs de la colonne spécifiée dans le paramètre. Le paramètre peut également être une expression contenant le nom de la colonne. Par exemple:

SÉLECTIONNER LA SOMME (Order_Sum) DE Clients ;

Cette expression SQL renvoie une table à une colonne et à un enregistrement contenant la somme de toutes les valeurs définies dans la colonne Order_Sum de la table Customers.

Supposons que dans la table source, les valeurs de la colonne Order_Amount soient exprimées en roubles et que nous souhaitions calculer le montant total en dollars. Si le taux de change actuel est, par exemple, 27,8, alors vous pouvez obtenir le résultat requis en utilisant l'expression :

SÉLECTIONNER LA SOMME (Order_Sum * 27,8) DE Clients ;

• AVG (paramètre ) - renvoie la moyenne arithmétique de toutes les valeurs de la colonne spécifiée dans le paramètre. Le paramètre peut être une expression contenant le nom de la colonne. Par exemple:

SELECT AVG (Order_Sum) FROM Clients ;

SELECT AVG (Order_Sum * 27.8) FROM Clients

O Région<>"Nord_3ouest" ;

• MAX (paramètre ) - renvoie la valeur maximale dans la colonne spécifiée dans le paramètre. Le paramètre peut également être une expression contenant le nom de la colonne. Par exemple:

SELECTIONNER MAX (Amount_order) FROM Clients ;

SELECTIONNER MAX (Somme_Commande * 27,8) DES Clients

OÙ Région<>"Nord_3ouest" ;

• MIN (paramètre ) - renvoie la valeur minimale dans la colonne spécifiée dans le paramètre. Le paramètre peut être une expression contenant le nom de la colonne. Par exemple:

SELECTIONNER MIN (Somme_Commande) DE Clients ;

SELECTIONNER MIN (Amount_order * 27,8) FROM Clients

OÙ Région<>"Nord_3ouest" ;

En pratique, il est souvent nécessaire d'obtenir un tableau récapitulatif contenant les valeurs totales, moyennes, maximales et minimales des colonnes numériques. Pour ce faire, vous devez utiliser les fonctions de regroupement (GROUP BY) et de résumé.

SELECTIONNER Région, SOMME (Order_Sum) DE Clients

GROUPER PAR Région;

Le tableau des résultats de cette requête contient les noms des régions et le montant total (total) des commandes de tous les clients des régions respectives (Fig. 5).

Examinons maintenant une requête pour obtenir tous les totaux par région :

SELECTIONNER Région, SOMME (Order_Sum), AVG (Amount_order), MAX (Amount_order), MIN (Somme_Commande)

DES Clients

GROUPER PAR Région;

Les tables initiales et finales sont présentées dans la Fig. 8. Dans l'exemple, seule la région Nord-Ouest est représentée dans la table d'origine par plusieurs enregistrements. Par conséquent, dans le tableau de résultats correspondant, différentes fonctions récapitulatives donnent des valeurs différentes.

Figure. 8. Tableau récapitulatif des montants des commandes par région

Lors de l'utilisation de fonctions récapitulatives dans la liste des colonnes de l'instruction SELECT, les en-têtes des colonnes correspondantes dans la table de résultats ressemblent à Expr1001, Expr1002, etc. (ou quelque chose de similaire, selon l'implémentation SQL). Cependant, vous pouvez définir les en-têtes des valeurs des fonctions récapitulatives et des autres colonnes à votre guise. Pour ce faire, il suffit de spécifier une expression de la forme après la colonne dans l'instruction SELECT :

AS column_header

Le mot clé AS (as) signifie que dans la table de résultats, la colonne correspondante doit avoir l'en-tête spécifié après AS. Le titre attribué est également appelé alias. L'exemple suivant (Figure 9) définit des alias pour toutes les colonnes calculées :

Choisissez une région,

SOMME (Order_Sum) AS [Montant total de la commande],

MOYENNE (Order_Sum) AS [Montant moyen de la commande],

MAX (Order_Sum) AS Maximum,

MIN (Order_Sum) AS Minimum,

DES Clients

GROUPER PAR Région;

Figure. 9. Tableau récapitulatif des montants des commandes par région à l'aide des alias de colonne

Les alias contenant plusieurs mots séparés par des espaces sont placés entre crochets.

Les fonctions récapitulatives peuvent être utilisées dans les clauses SELECT et HAVING, mais elles ne peuvent pas être utilisées dans les clauses WHERE. Une clause HAVING est similaire à la clause WHERE, mais contrairement à la clause WHERE, elle sélectionne les enregistrements dans des groupes.

Supposons que vous souhaitiez déterminer quelles régions ont plusieurs clients. A cet effet, vous pouvez utiliser la requête suivante :

SELECTIONNER Région, Nombre (*)

DES Clients

GROUP BY Region HAVING COUNT (*)> 1 ;

Fonctions de gestion des valeurs

Lorsque vous travaillez avec des données, vous devez souvent les traiter (les convertir sous la forme souhaitée) : sélectionnez une sous-chaîne dans une chaîne, supprimez les espaces de début et de fin, arrondissez un nombre, calculez la racine carrée, déterminez l'heure actuelle, etc. Il existe trois types de fonctions en SQL :

• fonctions de chaîne ;
• fonctions numériques;
• fonctions date-heure.

Fonctions de chaîne

Les fonctions de chaîne prennent une chaîne en paramètre et renvoient une chaîne ou NULL après l'avoir traitée.

• SOUS-CHAINE (À PARTIR DU début de la ligne)- renvoie la sous-chaîne résultant de la chaîne spécifiée en paramètre ligne . Sous-chaîne commence par un caractère dont le numéro ordinal est spécifié dans le paramètre start et a une longueur spécifiée dans le paramètre length. Les caractères de ligne sont numérotés de gauche à droite, en commençant à 1. Les crochets indiquent ici uniquement que l'expression qu'ils contiennent est facultative. Si l'expression POUR la longueur n'est pas utilisé, alors la sous-chaîne de Début jusqu'à la fin de la ligne d'origine. Valeurs des paramètres début et durée doit être choisi de manière à ce que la sous-chaîne souhaitée se trouve réellement dans la chaîne d'origine. Sinon, la fonction SUBSTRING renverra NULL.

Par exemple:

SUBSTRING ("Chère Masha!" DE 9 POUR 4) - renvoie "Masha" ;

SUBSTRING ("Chère Masha!" À PARTIR DE 9) — renvoie "Masha!";

SUBSTRING ("Chère Masha!" À PARTIR DE 15) — renvoie NULL.

Vous pouvez utiliser cette fonction dans une instruction SQL comme celle-ci :

SÉLECTIONNER * DE Clients

WHERE SUBSTRING (Région DE 1 POUR 5) = "Nord" ;

• UPPER (chaîne ) - convertit tous les caractères de la chaîne spécifiée en majuscules.
• INFÉRIEUR (ligne ) - convertit tous les caractères de la chaîne spécifiée en minuscules.
• TRIM (LEADING | TRAILING | BOTH ["caractère"] FROM chaîne ) - supprime les caractères de début (LEADING), de fin (TRAILING) ou les deux (BOTH) de la chaîne. Le caractère par défaut à supprimer est un espace (""), vous pouvez donc le laisser vide. Le plus souvent, cette fonction est utilisée spécifiquement pour supprimer des espaces.

Par exemple:

TRIM (LEADING "" FROM "la ville de Saint-Pétersbourg") fait tourner la "ville de Saint-Pétersbourg" ;

TRIM (TRALING "" FROM "ville de Saint-Pétersbourg") renvoie "ville de Saint-Pétersbourg" ;

TRIM (LES DEUX "" FROM "ville de Saint-Pétersbourg") - renvoie "ville de Saint-Pétersbourg" ;

TRIM (LES DEUX DE "ville de Saint-Pétersbourg") - renvoie "ville de Saint-Pétersbourg" ;

TRIM (LES DEUX "r" DE "ville de Saint-Pétersbourg") - renvoie "ville de Saint-Pétersbourg".

Parmi ces fonctions, les plus couramment utilisées sont SUBSTRING() et TRIM().

Fonctions numériques

Les fonctions numériques en tant que paramètre peuvent accepter des données non seulement de type numérique, mais toujours renvoyer un nombre ou NULL (valeur non définie).

• POSITION ( chaîne cible IN chaîne) - recherche une occurrence de la chaîne cible dans la chaîne spécifiée. Si la recherche est réussie, elle renvoie le numéro de position de son premier caractère, sinon - 0. Si la chaîne cible a une longueur nulle (par exemple, la chaîne ""), alors la fonction renvoie 1. Si au moins un des paramètres est NULL, alors NULL est renvoyé. Les caractères de ligne sont numérotés de gauche à droite, à partir de 1.

Par exemple:

POSITION ("e" DANS "Bonjour à tous") - renvoie 5 ;

POSITION ("tout le monde" DANS "Bonjour tout le monde") - renvoie 8 ;

POSITION ("" Bonjour à tous ") - renvoie 1 ;

POSITION ("Bonjour!" DANS "Bonjour tout le monde") - Renvoie 0.

Dans la table Clients (voir Fig. 1), la colonne Adresse contient, en plus du nom de la ville, le code postal, le nom de la rue et d'autres données. Vous devrez peut-être sélectionner des enregistrements pour les clients qui vivent dans une ville spécifique. Ainsi, si vous souhaitez sélectionner des enregistrements liés à des clients vivant à Saint-Pétersbourg, vous pouvez utiliser l'expression de requête SQL suivante :

SÉLECTIONNER * DE Clients

O POSITION ("Saint-Pétersbourg" dans l'adresse)> 0 ;

Notez que cette requête simple pour récupérer des données peut être formulée différemment :

SÉLECTIONNER * DE Clients

O Adresse COMME "% Petersburg%" ;

• EXTRAIT (paramètre ) - extrait un élément d'une valeur date-heure ou d'un intervalle. Par exemple:

EXTRAIT (MOIS À PARTIR DE LA DATE "2005-10-25") - renvoie 10.

• CHARACTER_LENGTH (chaîne ) - renvoie le nombre de caractères d'une chaîne.

Par exemple:

CHARACTER_LENGTH ("Bonjour à tous") - Renvoie 11.

• OCTET_LENGTH (chaîne ) - renvoie le nombre d'octets (octets) dans une chaîne. Chaque caractère latin ou cyrillique est représenté par un octet et un caractère chinois est représenté par deux octets.
• CARDINALITE (paramètre ) - prend une collection d'éléments en paramètre et renvoie le nombre d'éléments de la collection (cardinal). Une collection peut être, par exemple, un tableau ou un multi-ensemble contenant des éléments de différents types.
• ABS (nombre ) - renvoie la valeur absolue d'un nombre. Par exemple:

ABS (-123) - renvoie 123 ;

ABS (2 - 5) - Retours 3.

• MO D (numéro1, numéro2 ) - renvoie le reste de la division entière du premier nombre par le second. Par exemple:

MOD (5, h) - renvoie 2 ;

MOD (2, h) - renvoie 0.

• LN (numéro ) - Renvoie le logarithme népérien d'un nombre.
• EXP (nombre) - renvoie le nombre e (base du logarithme népérien à la puissance du nombre).
• PUISSANCE (numéro1, numéro2 ) - renvoie nombre1 numéro 2 (nombre1 à la puissance nombre2).
• SQRT (nombre ) - Renvoie la racine carrée d'un nombre.
• Numéro d'étage ) - renvoie le plus grand entier ne dépassant pas le paramètre spécifié (arrondi à l'inférieur). Par exemple:

PLANCHER (5.123) - Renvoie 5.0.

• PLAFOND (numéro) ou PLAFOND (numéro ) - renvoie le plus petit entier qui n'est pas inférieur à l'arrondi spécifié par le paramètre). Par exemple:

CEIL (5.123) - Renvoie 6.0.

• WIDTH_BUCKET (nombre1, nombre2, nombre3, nombre4) renvoie un entier compris entre 0 et nombre4 + 1. Les paramètres nombre2 et nombre3 spécifient un segment numérique divisé en intervalles égaux, dont le nombre est spécifié par le paramètre nombre 4. La fonction détermine le numéro de l'intervalle dans lequel la valeur tombe nombre1. Si nombre1 est en dehors de la plage spécifiée, la fonction renvoie 0 ou le nombre 4 + 1. Par exemple :

WIDTH_BUCKET (3.14, 0, 9, 5) - Renvoie 2.

Fonctions date-heure

Il existe trois fonctions dans SQL qui renvoient la date et l'heure actuelles.

• DATE ACTUELLE - renvoie la date courante (tapez DATE).

Par exemple : 18-06-2005.

• CURRENT_TIME (nombre ) - renvoie l'heure courante (type HEURE). Un paramètre entier spécifie la précision des secondes. Par exemple, si la valeur est 2, les secondes seront présentées avec une précision au centième (deux chiffres dans la partie fractionnaire) :

12:39:45.27.

• CURRENT_TIMESTAMP (nombre ) - renvoie la date et l'heure (type TIMESTAMP). Par exemple, 2005-06-18 12 : 39 : 45,27. Un paramètre entier spécifie la précision des secondes.

Notez que la date et l'heure renvoyées par ces fonctions sont de type non-caractère. Si vous devez les représenter sous forme de chaînes de caractères, vous devez alors utiliser la fonction de conversion de type CAST().

Les fonctions date-heure sont couramment utilisées dans les requêtes pour insérer, mettre à jour et supprimer des données. Par exemple, lorsque vous enregistrez des informations sur les ventes, la date et l'heure actuelles sont saisies dans une colonne dédiée. Après avoir additionné les totaux pour le mois ou le trimestre, les données de ventes pour la période de rapport peuvent être supprimées.

Expressions calculées

Les expressions calculées sont construites à partir de constantes (numériques, chaîne, booléennes), de fonctions, de noms de champs et d'autres types de données en les connectant avec des opérateurs arithmétiques, de chaîne, booléens et autres. À leur tour, les expressions peuvent être combinées par des opérateurs en expressions (composées) plus complexes. Les parenthèses sont utilisées pour contrôler l'ordre dans lequel les expressions sont évaluées.

Opérateurs logiques ET, OU et NON et fonctions ont été discutés plus tôt.

Opérateurs arithmétiques :

• + - addition;
• - - soustraction;
• * - multiplication ;
• / - division.

Opérateur de chaîneun seul est l'opérateur de concaténation de chaînes ou de concaténation (| |). Certaines implémentations SQL (telles que Microsoft Access) utilisent le caractère (+) au lieu de (| |). L'opérateur de concaténation ajoute la deuxième ligne à la fin du premier exemple, expression :

"Sacha" | | "aime" | | "Macha"

renverra la chaîne "Sasha loves Masha" en conséquence.

Lors de la composition d'expressions, il est nécessaire de s'assurer que les opérandes des opérateurs ont des types valides. Par exemple, l'expression : 123 + "Sasha" n'est pas valide car l'opérateur d'addition arithmétique est appliqué à l'opérande de chaîne.

Les expressions évaluées peuvent apparaître après l'instruction SELECT, ainsi que dans les expressions de condition des clauses WHERE et HAVI NG.

Regardons quelques exemples.

Supposons que la table Sales contienne les colonnes Item_type, Quantity et Price, et que nous ayons besoin de connaître les revenus pour chaque type de produit. Pour ce faire, il suffit d'inclure l'expression Quantité * Prix dans la liste des colonnes après l'instruction SELECT :

SELECT Item_type, Quantity, Price, Quantity * Price COMME

Ventes totales DE;

Il utilise le mot clé AS (as) pour spécifier un alias pour la colonne de données calculée.

En figue. 10 montre la table Sales d'origine et la table de requête résultante.

Figure. 10. Résultat d'une requête avec le calcul du chiffre d'affaires pour chaque type de produit

Si vous avez besoin de connaître le revenu total de la vente de tous les biens, il suffit d'appliquer la requête suivante :

SÉLECTIONNER LA SOMME (Quantité * Prix) DES Ventes ;

La requête suivante contient des expressions évaluées à la fois dans la liste de colonnes et dans la clause WHERE. Il sélectionne dans la table des ventes les marchandises dont le chiffre d'affaires est supérieur à 1000 :

SELECT Item_type, Quantité * Prix AS Total

DE Ventes

O Quantité * Prix> 1000 ;

Supposons que vous vouliez obtenir un tableau avec deux colonnes :

Produit contenant le type et le prix du produit ;

Total contenant les revenus.

Comme il est supposé que dans la table des ventes d'origine, la colonne Item_type est un caractère (de type CHAR) et la colonne Prix est numérique, alors lors de la fusion (collage) des données de ces colonnes, vous devez convertir le type numérique en caractère à l'aide de () une fonction. La requête effectuant cette tâche ressemble à ceci (Fig. 11) :

SELECT Item_type | | "(Prix :" | | CAST (Prix AS CHAR (5)) | | ")" AS Produit, Quantité * Prix AS Total

DES Ventes ;

Figure. 11. Le résultat d'une requête combinant différents types de données dans une colonne

Noter. Dans Microsoft Access, une requête similaire ressemblera à ceci :

SELECT Item_type + "(Prix :" + C Str (Prix) + ")" AS Produit,

Quantité * Prix AS Total

DES Ventes ;

Expressions conditionnelles avec instruction CASE

Dans les langages de programmation ordinaires, il existe des opérateurs de saut conditionnel qui vous permettent de contrôler le processus de calcul selon qu'une certaine condition est remplie ou non. En SQL, une telle instruction est CASE (cas, circonstance, instance). En SQL : 2003, cet opérateur renvoie une valeur et peut donc être utilisé dans des expressions. Il se présente sous deux formes principales, que nous examinerons dans cette section.

Instruction CASE avec valeurs

L'instruction CASE avec des valeurs a la syntaxe suivante :

CASE check_value

QUAND valeur1 ALORS résultat1

QUAND valeur2 ALORS résultat2

. . .

QUAND valeur N ALORS résultat N

AUTRE résultat X

Dans le cas où valeur_cochéeégal à valeur1 , l'instruction CASE renvoie la valeur résultat1 spécifié après le mot-clé THEN (alors). Sinon, la valeur check_value est comparée à valeur2 , et s'ils sont égaux, alors result2 est renvoyé. Sinon, la valeur vérifiée est comparée à la valeur suivante spécifiée après le mot-clé WHEN (quand), et ainsi de suite. Si la valeur_vérifiée n'est égale à aucune de ces valeurs, la valeur est renvoyée résultat X spécifié après le mot-clé ELSE (sinon).

Le mot clé ELSE est facultatif. S'il est absent et qu'aucune des valeurs à comparer n'est égale à la valeur testée, alors l'instruction CASE renvoie NULL.

Supposons, sur la base de la table Customers (voir Fig. 1), que vous souhaitiez obtenir une table dans laquelle les noms des régions sont remplacés par leurs numéros de code. S'il n'y a pas trop de régions différentes dans la table initiale, alors pour résoudre ce problème, il est pratique d'utiliser une requête avec l'opérateur CASE :

SELECTIONNER Nom, Adresse,

CAS Région

QUAND "Moscou" ALORS "77"

QUAND "Région de Tver" ALORS "69"

. . .

Région ELSE

AS Code de région

DES Clients ;

Instruction CASE avec termes de recherche

La deuxième forme de l'opérateur CASE suppose son utilisation lors de la recherche dans une table des enregistrements qui satisfont à une certaine condition :

CAS

QUAND condition1 ALORS résultat1

QUAND résultat2 ALORS résultat2

. . .

QUAND condition N ALORS résultat N

AUTRE résultat X

La clause CASE vérifie si condition1 est vraie pour le premier enregistrement de l'ensemble spécifié par la clause WHERE, ou dans la table entière s'il n'y a pas de WHERE. Si c'est le cas, CASE renvoie la valeur result1. Sinon, la condition 2 est vérifiée pour cet enregistrement. Si c'est vrai, la valeur result2 est renvoyée, et ainsi de suite. Si aucune des conditions n'est remplie, la valeur est renvoyée. X spécifié après le mot-clé ELSE.

Le mot clé ELSE est facultatif. S'il est absent et qu'aucune des conditions n'est remplie, l'instruction CASE tourne NULL. Une fois que l'instruction contenant le CASE est exécutée pour le premier enregistrement, elle passe à l'enregistrement suivant. Cela continue jusqu'à ce que l'ensemble du jeu d'enregistrements ait été traité.

Par exemple, dans la table livre (Titre, Prix), la colonne est NULL si le livre correspondant n'est pas disponible. La requête suivante renvoie une table qui affiche le texte « Rupture de stock » au lieu de NULL :

SÉLECTIONNER le nom,

CAS

QUAND le prix EST NULL ALORS "Épuisé"

ELSE CAST (Prix AS CHAR (8))

AS Prix

À PARTIR de livres ;

Toutes les valeurs d'une même colonne doivent être du même type. Par conséquent, cette requête utilise la fonction CAST pour convertir les valeurs numériques de la colonne Prix en un type de caractère.

Notez qu'au lieu de la première forme de l'instruction CASE, vous pouvez toujours utiliser la seconde :

CAS

QUAND valeur_test = valeur1 ALORS résultat1

QUAND valeur_test = valeur2 ALORS résultat2

. . .

QUAND valeur_cochée = valeur N ALORS résultatN

Résultat AUTRE

Fonctions NULLIF et COALESCE

Dans certains cas, notamment dans les requêtes de mise à jour des données (instruction UPDATE), il est pratique d'utiliser les fonctions plus compactes NULLIF () (NULL if) et COALESCE () (combine) au lieu de la lourde instruction CASE.

Fonction NULLIF ( valeur1, valeur2) renvoie NULL si la valeur du premier paramètre correspond à la valeur du deuxième paramètre, en cas de non-concordance, la valeur du premier paramètre est renvoyée inchangée. Autrement dit, si l'égalité valeur1 = valeur2 est satisfaite, la fonction renvoie NULL, sinon - valeur valeur1.

Cette fonction est équivalente à l'instruction CASE sous les deux formes suivantes :

• CAS valeur1

QUAND valeur2 ALORS NULL

AUTRE valeur1

• CAS

QUAND valeur1 = valeur2 ALORS NULL

AUTRE valeur1

Fonction COALESCE ( valeur1, valeur2, ..., valeur N) accepte une liste de valeurs, qui peut être définie ou nulle. La fonction renvoie une valeur spécifique de la liste, ou NULL si toutes les valeurs ne sont pas définies.

Cette fonction est équivalente à l'instruction CASE suivante :

CAS

QUAND la valeur 1 N'EST PAS NULLE ALORS la valeur 1

QUAND la valeur 2 N'EST PAS NULLE ALORS la valeur 2

. . .

QUAND la valeur N N'EST PAS NULLE ALORS la valeur N

AUTREMENT NULL

Par exemple, dans la table Livres (Titre, Prix), la colonne Prix est NULL si le livre correspondant n'est pas disponible. La requête suivante renvoie une table dans laquelle, au lieu de NUL le texte « Rupture de stock » s'affiche :

SELECT Nom, COALESCE (CAST (Price AS CHAR (8)),

"En rupture de stock") AS Prix

À PARTIR de livres ;

SQL Leçon 11. Fonctions récapitulatives, colonnes calculées et vues

Les fonctions récapitulatives sont également appelées fonctions statistiques, agrégées ou récapitulatives. Ces fonctions traitent un ensemble de lignes pour compter et renvoyer une seule valeur. Il n'y a que cinq de ces fonctions :

• La fonction AVG () renvoie la valeur moyenne d'une colonne.


• COUNT () La fonction renvoie le nombre de lignes dans une colonne.


• La fonction MAX () renvoie la plus grande valeur de la colonne.


• MIN () La fonction renvoie la plus petite valeur de la colonne.


• La fonction SUM () renvoie la somme des valeurs de la colonne.

Nous en avons déjà rencontré un - COUNT () dans la leçon 8... Faisons maintenant connaissance avec le reste. Supposons que nous voulions connaître les prix minimum, maximum et moyen des livres dans notre magasin. Ensuite, à partir du tableau Prix (prix), il faut prendre les valeurs minimales, maximales et moyennes pour la colonne des prix. La demande est simple :

SELECTIONNER MIN (prix), MAX (prix), AVG (prix) FROM prix ;

Maintenant, nous voulons savoir combien le fournisseur "Imprimerie" nous a apporté la marchandise (id = 2). Ce n'est pas si facile de composer une telle demande. Réfléchissons à la façon de le composer :

1. Dans un premier temps, dans le tableau Livraisons (entrantes), sélectionnez les identifiants (id_incoming) des livraisons effectuées par le fournisseur "Imprimerie" (id = 2) :

2. Maintenant, dans le tableau Magazine des livraisons (magazine_incoming), vous devez sélectionner les marchandises (id_product) et leurs quantités (quantity), qui ont été effectuées dans les livraisons trouvées au paragraphe 1. C'est-à-dire que la demande du paragraphe 1 devient imbriquée :

3. Maintenant, nous devons ajouter au tableau résultant les prix des produits trouvés, qui sont stockés dans le tableau des prix. C'est-à-dire que nous devons concaténer les tables Magazine_incoming et Prices par la colonne id_product :

4. Le tableau résultant manque clairement de la colonne Somme, c'est-à-dire colonne calculée... La possibilité de créer de telles colonnes est fournie dans MySQL. Pour ce faire, il vous suffit de préciser dans la requête le nom de la colonne calculée et ce qu'elle doit calculer. Dans notre exemple, une telle colonne s'appellera summa et calculera le produit des colonnes quantité et prix. Le nouveau nom de colonne est séparé par le mot AS :

SELECT magazine_incoming.id_product, magazine_incoming.quantity, price.price, magazine_incoming.quantity * price.price AS somme FROM magazine_incoming, price WHERE magazine_incoming.id_product = price.id_product AND id_incoming = (SELECT id_incoming FROM entrant WHERE 2 id_vendor

5. Super, tout ce que nous avons à faire est de résumer la colonne summa et enfin de découvrir combien le fournisseur "Printing House" nous a apporté la marchandise. La syntaxe d'utilisation de la fonction SUM() est la suivante :

SELECT SUM (nom_colonne) FROM nom_table ;

On connaît le nom de la colonne - summa, mais on n'a pas le nom de la table, puisqu'il est le résultat d'une requête. Ce qu'il faut faire? Pour de tels cas, MySQL fournit des vues. Une vue est une requête de sélection qui reçoit un nom unique et peut être stockée dans une base de données pour une utilisation ultérieure.

La syntaxe pour créer une vue est la suivante :

CREATE VIEW nom_vue AS requête ;

Enregistrons notre demande en tant que vue nommée report_vendor :

CREATE VIEW report_vendor AS SELECT magazine_incoming.id_product, magazine_incoming.quantity, price.price, magazine_incoming.quantity * price.price AS summa FROM magazine_incoming, price WHERE magazine_incoming.id_product = price.id_product AND id_incoming = (FROMECT id_incoming = (FROMECT id_incoming );

6. Vous pouvez maintenant utiliser la fonction SUM () finale :

SELECT SUM (somme) FROM report_vendor;

Nous avons donc atteint le résultat, même si pour cela nous avons dû utiliser des requêtes imbriquées, des jointures, des colonnes calculées et des vues. Oui, il faut parfois réfléchir pour obtenir le résultat, sans qu'il soit nulle part. Mais nous avons abordé deux sujets très importants - les colonnes calculées et les vues. Parlons-en plus en détail.

Champs calculés (colonnes)

À l'aide d'un exemple, nous avons examiné un champ mathématique calculé aujourd'hui. Ici, je voudrais ajouter que vous pouvez utiliser non seulement l'opération de multiplication (*), mais aussi la soustraction (-), et l'addition (+) et la division (/). La syntaxe est la suivante :

SELECT nom_colonne_1, nom_colonne 2, nom_colonne * nom_colonne 2 AS nom_colonne_calculée FROM nom_table ;

La deuxième nuance est le mot-clé AS, nous l'avons utilisé pour définir le nom de la colonne calculée. En fait, ce mot-clé est utilisé pour définir des alias pour toutes les colonnes. Pourquoi est-ce nécessaire ? Pour un code plus court et plus lisible. Par exemple, notre vue pourrait ressembler à ceci :

CREATE VIEW report_vendor AS SELECT A.id_product, A.quantity, B.price, A.quantity * B.price AS summa FROM magazine_incoming AS A, price AS B WHERE A.id_product = B.id_product AND id_incoming = (SELECT id_incoming FROM entrant O id_vendeur = 2);

Convenez que c'est beaucoup plus court et plus clair.

Représentation

Nous avons déjà couvert la syntaxe pour créer des vues. Une fois les vues créées, elles peuvent être utilisées de la même manière que les tables. C'est-à-dire exécuter des requêtes sur eux, filtrer et trier les données, combiner certaines vues avec d'autres. D'une part, c'est un moyen très pratique de stocker des requêtes complexes fréquemment utilisées (comme dans notre exemple).

Gardez à l'esprit, cependant, que les vues ne sont pas des tables, c'est-à-dire qu'elles ne stockent pas de données, elles les récupèrent uniquement à partir d'autres tables. Par conséquent, tout d'abord, lorsque les données des tableaux changent, les résultats de la présentation changent également. Et deuxièmement, lorsqu'une requête est adressée à la vue, les données requises sont recherchées, c'est-à-dire que les performances du SGBD diminuent. Par conséquent, ils ne doivent pas être abusés.

La fonction SUM en langage SQL, malgré sa simplicité, est assez souvent utilisée lorsque l'on travaille avec une base de données. Avec son aide, il est pratique d'obtenir des résultats intermédiaires ou finaux sans recourir à l'aide d'outils de SGBD auxiliaires.

Syntaxe de la fonction

Dans la plupart des langages SQL, la syntaxe de somme est la même — seul le nom du champ ou une opération arithmétique de plusieurs d'entre eux est utilisé comme argument, sur lequel la somme est requise.

Dans des cas exceptionnels, vous pouvez transférer une valeur spécifique sous la forme d'un nombre ou d'une variable, cependant, de tels "schémas" ne sont pratiquement pas utilisés, car ils n'ont pas une grande valeur. Voici la syntaxe de la fonction en langage SQL :

somme (a) - ici une valeur numérique ou une expression est utilisée comme paramètre

Il convient de noter qu'avant le paramètre, vous pouvez définir des mots-clés, par exemple, DISTINCT ou ALL, qui ne prendront respectivement que des valeurs uniques ou toutes.

Un exemple d'utilisation de SUM en SQL

Pour une compréhension finale du fonctionnement de la fonction, il convient de considérer quelques exemples. En SQL, SUM peut être utilisé à la fois comme résultat de retour et comme valeur intermédiaire, par exemple, pour tester une condition.

Pour le premier cas, envisagez l'option lorsque vous souhaitez retourner le montant des ventes pour chaque produit, étant donné que le nombre d'achats effectués peut être au pluriel. Pour obtenir le résultat, il suffira d'exécuter la requête suivante :

SELECT Produit, somme (Montant de l'achat) FROM Groupe de ventesPar produit ;

La réponse à cette commande sera une liste unique de produits avec le montant total des achats pour chacun d'eux.

Pour le deuxième exemple, vous devez obtenir une liste de produits dont le montant des ventes a dépassé une certaine valeur, par exemple 100. Il existe plusieurs façons d'obtenir le résultat de cette tâche, la plus optimale étant la exécution d'une demande :

SELECTIONNER L'article FROM (SELECTIONNER L'article, somme (Montant des achats) comme FROM Montant des ventes) WHERE Montant> 100.

Ce tutoriel va vous montrer comment utiliser MySQL la fonction SOMME avec syntaxe et exemples.

La description

Fonction MySQL SOMME renvoie la valeur totale de l'expression.

Syntaxe

La syntaxe de la fonction SUM dans MySQL est :

OU La syntaxe de la fonction MySQL SUM lors du regroupement des résultats par une ou plusieurs colonnes est :

Paramètres ou arguments

expression1, expression2, ... expression_n sont des expressions qui ne sont pas encapsulées dans la fonction SUM et doivent être incluses dans la clause GROUP BY à la fin de l'instruction SQL.

expression_agrégat est la colonne ou l'expression à résumer.

tables - les tables à partir desquelles vous souhaitez obtenir des enregistrements. Il doit y avoir au moins une table spécifiée dans la clause FROM.

O les conditions sont facultatives. Ce sont les conditions qui doivent être remplies pour les enregistrements sélectionnés.

Application

La fonction SUM peut être utilisée dans les versions suivantes de MySQL :

• MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23

Exemple d'expression unique

Regardons quelques exemples de fonction SUM MySQL pour comprendre comment utiliser la fonction SUM dans MySQL.

Par exemple, vous devez savoir quel est le salaire total cumulé de tous les employés qui gagnent plus de 20 000 $ par an.

Dans cet exemple de la fonction SUM, l'expression SUM (salaire) a été aliasée en "Salaire total". Par conséquent, lorsque l'ensemble de résultats est renvoyé, « Salaire total » sera affiché comme nom de champ.

Exemple utilisant DISTINCT

Vous pouvez utiliser la clause DISTINCT dans la fonction SUM. Par exemple, l'instruction SQL suivante renvoie le salaire total combiné des valeurs de salaire uniques où le salaire est supérieur à 20 000 $ par an.

Si les deux salaires étaient de 10 000 $ par an, une seule de ces valeurs serait utilisée dans la fonction SUM.

Exemple utilisant la formule

L'expression contenue dans la fonction SUM ne doit pas nécessairement être un seul champ. Vous pouvez également utiliser une formule. Par exemple, vous pouvez calculer la commission totale.

MySQL

SELECTIONNER LA SOMME (ventes * 0,05) COMME "Commission totale" DES commandes ;

SELECTIONNER SOMME (ventes * 0,05) COMME "Commission totale" DES commandes;

Exemple utilisant GROUP BY

Dans certains cas, vous devrez utiliser la clause GROUP BY avec la fonction SUM.

Par exemple, vous pouvez utiliser la fonction SOMME pour renvoyer le rayon et les ventes totales du rayon correspondant.