Création de champs calculés
En général, pour créer champ calculé (dérivé) la liste SELECT doit contenir une expression SQL. Ces expressions utilisent les opérations arithmétiques d'addition, de soustraction, de multiplication et de division, ainsi que des fonctions SQL intégrées. Vous pouvez spécifier le nom de n'importe quelle colonne (champ) d'une table ou d'une requête, mais utilisez uniquement le nom de colonne de la table ou de la requête répertorié dans la liste des clauses FROM de l'instruction correspondante. Lors de la construction d’expressions complexes, des parenthèses peuvent être nécessaires.
Les normes SQL vous permettent de spécifier explicitement les noms des colonnes de la table résultante, pour lesquelles la clause AS est utilisée.
SELECT Product.Name, Product.Price, Deal.Quantity, Product.Price*Deal.Quantity AS Cost FROM Product INNER JOIN Deal ON Product.ProductCode=Deal.ProductCode Exemple 6.1.
Calcul du coût total de chaque transaction. Exemple 6.2.
Obtenez une liste d’entreprises indiquant les noms et initiales des clients. SELECT Entreprise, Nom+""+ Gauche(Prénom,1)+"."+Gauche(Deuxième prénom,1)+"."AS Nom complet DU Client
Exemple 6.2.
Obtention d'une liste d'entreprises indiquant les noms et initiales des clients. La requête utilise la fonction intégrée Left, qui vous permet dans ce cas de couper un caractère à gauche dans une variable de texte.
Exemple 6.3. Obtenez une liste de produits indiquant l'année et le mois de vente.
SELECT Product.Name, Year(Transaction.Date) AS Year, Month(Transaction.Date) AS Month FROM Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode
Exemple 6.3.
Réception d'une liste de produits indiquant l'année et le mois de vente. La requête utilise les fonctions intégrées Année et Mois pour extraire l'année et le mois d'une date. Utilisation des fonctions récapitulatives
En utilisant fonctions finales (agrégatives):
- Count (Expression) - détermine le nombre d'enregistrements dans l'ensemble de sortie de la requête SQL ;
- Min/Max (Expression) - détermine la plus petite et la plus grande de l'ensemble de valeurs dans un certain champ de requête ;
- Avg (Expression) - cette fonction vous permet de calculer la moyenne d'un ensemble de valeurs stockées dans un champ spécifique d'enregistrements sélectionné par une requête. Il s'agit d'une moyenne arithmétique, c'est-à-dire la somme des valeurs divisée par leur nombre.
- Somme (Expression) - Calcule la somme de l'ensemble des valeurs contenues dans un champ spécifique des enregistrements sélectionnés par la requête.
Le plus souvent, les noms de colonnes sont utilisés comme expressions. L'expression peut également être calculée à partir des valeurs de plusieurs tables.
Toutes ces fonctions opèrent sur les valeurs d'une seule colonne d'un tableau ou sur une expression arithmétique et renvoient une seule valeur. Les fonctions COUNT , MIN et MAX s'appliquent aux champs numériques et non numériques, tandis que les fonctions SUM et AVG ne peuvent être utilisées que pour les champs numériques, à l'exception de COUNT(*) . Lors du calcul des résultats d'une fonction, toutes les valeurs nulles sont d'abord éliminées, puis l'opération requise est appliquée uniquement aux valeurs de colonne spécifiques restantes. L'option COUNT(*) est un cas d'utilisation particulier de la fonction COUNT ; son objectif est de compter toutes les lignes de la table résultante, qu'elle contienne des valeurs NULL, des doublons ou toute autre valeur.
Si vous devez éliminer les valeurs en double avant d'utiliser une fonction récapitulative, vous devez faire précéder le nom de la colonne dans la définition de la fonction du mot-clé DISTINCT. Cela n'a aucune signification pour les fonctions MIN et MAX, mais son utilisation peut affecter les résultats des fonctions SUM et AVG, vous devez donc vous demander s'il doit être présent dans chaque cas. De plus, le mot clé DISTINCT ne peut être spécifié qu'une seule fois dans une requête.
Il est très important de noter que fonctions finales (agrégatives) ne peut être utilisé que dans une liste d'une clause SELECT et dans le cadre d'une clause HAVING. Dans tous les autres cas, cela est inacceptable. Si la liste de la clause SELECT contient fonctions finales (agrégatives), et que le texte de la requête ne contient pas la clause GROUP BY, qui permet de combiner les données en groupes, alors aucun des éléments de liste de la clause SELECT ne peut inclure de références à des champs, sauf dans le cas où les champs agissent comme des arguments fonctions finales.
Exemple 6.4. Déterminez le premier nom alphabétique du produit.
SELECT Min(Product.Name) AS Min_Name FROM Produit Exemple 6.4.
Détermination du premier nom alphabétique du produit. Exemple 6.5.
Déterminez le nombre de transactions. SELECT Count(*) AS Number_of_deals FROM Deal
Exemple 6.5. Déterminez le nombre de transactions.
Exemple 6.6. Déterminez la quantité totale de marchandises vendues.
SELECT Sum(Deal.Quantity) AS Item_Quantity FROM Deal Exemple 6.6.
Détermination de la quantité totale de marchandises vendues. Exemple 6.7. Déterminer le prix moyen des biens vendus. SELECT Avg(Product.Price) AS Avg_Price FROM Product INNER JOIN Deal ON Product.ProductCode=Deal.ProductCode;
Exemple 6.7. Définition
prix moyen
marchandises vendues. fonctions finales (agrégatives) SELECT Sum(Product.Price*Transaction.Quantity) AS Coût FROM Produit INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode
Exemple 6.8. Calculer le coût total des marchandises vendues. Clause GROUPE PAR
Les requêtes nécessitent souvent la génération de sous-totaux, ce qui est généralement indiqué par l'apparition de l'expression « pour chaque… » dans la requête. Une clause GROUP BY est utilisée dans l'instruction SELECT à cet effet. Une requête qui contient GROUP BY est appelée requête de regroupement car elle regroupe les données renvoyées par l'opération SELECT, puis crée une seule ligne récapitulative pour chaque groupe individuel. Le standard SQL exige que la clause SELECT et la clause GROUP BY soient étroitement liées. Lorsqu'une instruction SELECT contient une clause GROUP BY, chaque élément de liste de la clause SELECT doit avoir une valeur unique pour l'ensemble du groupe. De plus, la clause SELECT ne peut inclure que les types d'éléments suivants : noms de champs,
, constantes et expressions qui incluent des combinaisons des éléments répertoriés ci-dessus.
Tous les noms de champs répertoriés dans la clause SELECT doivent également apparaître dans la clause GROUP BY - sauf si le nom de colonne est utilisé dans Calculez le volume moyen des achats effectués par chaque client.
SELECT Client.LastName, Avg(Transaction.Quantity) AS Average_Quantity FROM Client INNER JOIN Trade ON Client.ClientCode=Transaction.ClientCode GROUP BY Client.LastName Exemple 6.9.
Calculez le volume moyen des achats effectués par chaque client. L'expression « chaque client » est reflétée dans la requête SQL sous la forme d'une phrase.
GROUPER PAR Client.Nom Exemple 6.10.
Déterminez à quel prix chaque produit a été vendu. SELECT Product.Name, Sum(Product.Price*Transaction.Quantity) AS Cost FROM Product INNER JOIN Deal ON Product.ProductCode=Transaction.ProductCode GROUP BY Product.Name
Exemple 6.10. Détermination du montant pour lequel chaque produit a été vendu.
SELECT Client.Company, Count(Transaction.TransactionCode) AS Number_of_transactions FROM Client INNER JOIN Transaction ON Client.ClientCode=Transaction.ClientCode GROUP BY Client.Company Exemple 6.11.
Compter le nombre de transactions effectuées par chaque entreprise. SELECT Customer.Company, Sum(Transaction.Quantity) AS Total_Quantity, Sum(Product.Price*Transaction.Quantity) AS Cost FROM Product INNER JOIN (Client INNER JOIN Transaction ON Customer.ClientCode=Transaction.CustomerCode) ON Product.ProductCode=Transaction .Code produit GROUPE PAR Client.Entreprise
Exemple 6.12. Calcul de la quantité totale de biens achetés pour chaque entreprise et de son coût.
Exemple 6.13. Déterminez le coût total de chaque produit pour chaque mois.
SELECT Product.Name, Month(Transaction.Date) AS Month, Sum(Product.Price*Transaction.Quantity) AS Cost FROM Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode GROUP BY Product.Name, Month(Transaction.Date ) Exemple 6.13.
Détermination du coût total de chaque produit pour chaque mois.
Exemple 6.14. Déterminez le coût total de chaque produit de première classe pour chaque mois. SELECT Product.Name, Month(Transaction.Date) AS Month, Sum(Product.Price*Transaction.Quantity) AS Cost FROM Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode WHERE Product.Grade="First" GROUP BY Product .Nom, Mois (Transaction.Date)
Exemple 6.14.
- Détermination du coût total de chaque produit de première classe pour chaque mois.
- OÙ exclut les enregistrements qui ne satisfont pas à la condition du calcul des valeurs agrégées par regroupement ;
- Les fonctions d'agrégation ne peuvent pas être spécifiées dans la condition de recherche WHERE.
Exemple 6.15. Identifiez les entreprises dont le nombre total de transactions dépasse trois.
SELECT Client.Company, Count(Trade.Quantity) AS Number_of_deals FROM Client INNER JOIN Trade ON Client.ClientCode=Transaction.ClientCode GROUP BY Client.Company HAVING Count(Transaction.Quantity)>3 Exemple 6.15.
Identification des entreprises dont le nombre total de transactions dépassait trois. Exemple 6.16.
Affichez une liste des produits vendus pour plus de 10 000 roubles. SELECT Product.Name, Sum(Product.Price*Deal.Quantity) AS Cost FROM Product INNER JOIN Deal ON Product.ProductCode=Transaction.ProductCode GROUP BY Product.Name HAVING Sum(Product.Price*Deal.Quantity)>10000
Exemple 6.16. Affichage d'une liste de produits vendus pour plus de 10 000 roubles.
Exemple 6.17. Afficher une liste de produits vendus à plus de 10 000 sans préciser le montant.
SELECT Product.Name FROM Product INNER JOIN Deal ON Product.ProductCode=Deal.ProductCode GROUP BY Product.Name HAVING Sum(Product.Price*Deal.Quantity)>10000 Exemple 6.17. Afficher une liste de produits vendus à plus de 10 000 sans préciser le montant. Dans ce manuel vous apprendrez à utiliser Fonction COMPTE
V
Serveur SQL (Transact-SQL) avec syntaxe et exemples. Description
Dans SQL Server (Transact-SQL)
Fonction COMPTE
renvoie le nombre de lignes d'un champ ou d'une expression dans le jeu de résultats.
Syntaxe
La syntaxe de la fonction COUNT dans SQL Server (Transact-SQL) est la suivante :
OU la syntaxe de la fonction COUNT lors du regroupement des résultats d'une ou plusieurs colonnes est :
Paramètres ou arguments
expression1 , expression2 , … expression_n
Expressions qui ne sont pas incluses dans une fonction COUNT et doivent être incluses dans une clause GROUP BY à la fin de l'instruction SQL.
gregate_expression est la colonne ou l'expression dont les valeurs non NULL seront comptées.
tables - tables à partir desquelles vous souhaitez obtenir des enregistrements. Il doit y avoir au moins une table répertoriée dans la clause FROM.
Conditions OÙ - facultatives. Ce sont les conditions qui doivent être remplies pour les enregistrements sélectionnés. Inclure des valeurs non NULL Tout le monde ne comprend pas cela, mais la fonction COUNT ne comptera que les enregistrements pour lesquels la valeur de l'expression dans COUNT (aggregate_expression) n'est pas NULL. Lorsqu'une expression contient une valeur NULL, elle n'est pas incluse dans le compteur COUNT.
Par exemple, si vous disposez du tableau suivant appelé marchés :
Ce exemple COUNT renverra 3 car toutes les valeurs market_id dans l'ensemble de résultats de la requête ne sont PAS NULL.
Toutefois, si vous avez exécuté l'instruction SELECT suivante, qui utilise la fonction COUNT :
Transact-SQL
SELECT COUNT(filials) FROM marchés ; --Résultat : 1
Cet exemple COUNT ne renverra que 1, car une seule valeur filiale dans le jeu de résultats de la requête n'est PAS NULL. Ce sera la première ligne qui dira filials = "oui". Il s'agit de la seule ligne incluse dans le calcul de la fonction COUNT.
Application
La fonction COUNT peut être utilisée dans les cas suivants Versions SQL Serveur (Transact-SQL) :
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005
Exemple avec un champ
Regardons quelques-uns Exemples SQL Fonctions COUNT du serveur pour comprendre comment utiliser la fonction COUNT dans SQL Server (Transact-SQL).
Par exemple, vous pouvez savoir combien de contacts possède un utilisateur avec last_name = "Rasputin".
Dans cet exemple de fonction COUNT, nous avons spécifié l'alias « Nombre de contacts » à l'expression COUNT (*). Par conséquent, le jeu de résultats affichera « Nombre de contacts » comme nom de champ.
Exemple utilisant DISTINCT
Vous pouvez utiliser l'opérateur DISTINCT dans la fonction COUNT. Par exemple, l'instruction SQL ci-dessous renvoie le nombre de services uniques dans lesquels au moins un employé a prénom = « Samvel ».
Pour déterminer le nombre d'enregistrements dans une table MySQL, vous devez utiliser la fonction spéciale COUNT().
La fonction COUNT() renvoie le nombre d'enregistrements dans une table qui correspondent à un critère donné.
La fonction COUNT(expr) compte toujours uniquement les lignes pour lesquelles le résultat de expr est NOT NULL .
L'exception à cette règle concerne l'utilisation de la fonction COUNT() avec un astérisque comme argument - COUNT(*) . Dans ce cas, toutes les lignes sont comptées, qu'elles soient NULL ou NOT NULL.
Par exemple, la fonction COUNT(*) renvoie le nombre total d'enregistrements dans la table :
SELECT COUNT(*) FROM nom_table
Comment compter le nombre d'enregistrements et les afficher à l'écran
Exemple de code PHP+MySQL pour compter et afficher le nombre total de lignes :
$res = mysql_query("SELECT COUNT(*) FROM table_name") $row = mysql_fetch_row($res); $total = $ligne ; // total des enregistrements echo $total; ?>
Cet exemple illustre l'utilisation la plus simple de la fonction COUNT(). Mais vous pouvez également effectuer d'autres tâches en utilisant cette fonction.
En spécifiant une colonne de table spécifique comme paramètre, la fonction COUNT(column_name) renvoie le nombre d'enregistrements dans cette colonne qui ne contiennent pas de valeur NULL. Les enregistrements avec des valeurs NULL sont ignorés.
SELECT COUNT (nom_colonne) FROM nom_table
La fonction mysql_num_rows() ne peut pas être utilisée car pour connaître le nombre total d'enregistrements, vous devez exécuter Requête SELECT* FROM db , c'est-à-dire obtenir tous les enregistrements, mais cela n'est pas souhaitable, il est donc préférable d'utiliser la fonction count.
$result = mysql_query("SELECT COUNT (*) as rec FROM db");
Utilisation de la fonction COUNT() comme exemple
Voici un autre exemple d'utilisation de la fonction COUNT(). Disons qu'il existe une table ice_cream avec un catalogue de glaces, qui contient des identifiants de catégorie et des noms de glaces.
INFORMATIQUE
Fonctions récapitulatives
Les expressions de requête SQL nécessitent souvent un prétraitement des données. À cette fin, des fonctions et expressions spéciales sont utilisées.
Très souvent, vous avez besoin de 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. A cet effet, les fonctions dites finales (statistiques, agrégées) sont utilisées. Les fonctions récapitulatives traitent les ensembles d'enregistrements spécifiés, par exemple, par une clause WHERE. Si vous les incluez dans la liste des colonnes après l'instruction SELECT, la table résultante contiendra non seulement les colonnes de la table de base de données, mais également les valeurs calculées par 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 le symbole astérisque (*) comme 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 différentes contient une colonne, faites précéder le nom de la colonne du mot-clé DISTINCT. Par exemple:
SELECT COUNT(*) FROM Clients ;
SELECT COUNT(Order_Amount) FROM Clients ;
SELECT COUNT(DISTINCT Order_Amount) FROM Clients ;
Essayer d'exécuter la requête suivante entraînera un message d'erreur :
SELECT 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_Amount) DES clients ;
Cette instruction SQL renvoie une table à une colonne et à un enregistrement contenant la somme de toutes les valeurs définies pour la colonne Order_Amount 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 devions calculer le montant total en dollars. Si le taux de change actuel est, par exemple, de 27,8, alors vous pouvez obtenir le résultat souhaité en utilisant l'expression :
SÉLECTIONNER LA SOMME (Order_amount*27.8) DES 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_Amount) FROM Clients ;
SELECT AVG (Order_Amount*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:
SELECT MAX(Order_Amount) FROM Clients ;
SELECT MAX(Order_Amount*27.8) FROM 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:
SELECT MIN(Order_Amount) FROM Clients ;
SELECT MIN (Montant de la commande*27,8) DES 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é.
SELECT Région, SOMME (Order_amount) DES 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 correspondantes (Fig. 5).
Considérons maintenant une demande pour obtenir toutes les données récapitulatives par région :
SELECT Région, SUM (Order_Amount), AVG (Montant_commande), MAX(Montant_commande), MIN (Commande_montant)
DES Clients
GROUPER PAR Région ;
Les tableaux d'origine et de résultats sont présentés dans la Fig. 8. Dans l'exemple, seule la région Nord-Ouest est représentée dans la table source par plusieurs enregistrements. Par conséquent, dans le tableau des résultats, différentes fonctions récapitulatives donnent des valeurs différentes.
Riz. 8. Tableau final des montants des commandes par région
Lorsque vous utilisez des fonctions récapitulatives sur une liste de colonnes dans une instruction SELECT, les en-têtes de leurs colonnes correspondantes dans la table de résultats sont Expr1001, Expr1002, etc. (ou quelque chose de similaire, selon l'implémentation SQL). Cependant, vous pouvez définir des en-têtes pour les valeurs des fonctions récapitulatives et d'autres colonnes à votre discrétion. Pour cela, juste après la colonne dans l'instruction SELECT, spécifiez une expression de la forme :
AS en-tête_colonne
Le mot-clé AS (as) signifie que dans le tableau résultat, la colonne correspondante doit avoir un en-tête précisé 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 :
SÉLECTIONNER la région,
SOMME (Commande_montant) AS [Commande_total],
MOYENNE (Order_Amount) AS [Montant moyen de la commande],
MAX(Commande_Amount) AS Maximum,
MINIMUM (Montant_de la commande) AS Minimum,
DES Clients
GROUPER PAR Région ;
Riz. 9. Tableau final des montants des commandes par région à l'aide des alias de colonnes
Les surnoms composés de plusieurs mots séparés par des espaces sont mis 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. L'opérateur HAVING est similaire à l'opérateur WHERE, mais contrairement à WHERE, il sélectionne les enregistrements en groupes.
Supposons que vous souhaitiez déterminer quelles régions comptent plusieurs clients. Pour cela, vous pouvez utiliser la requête suivante :
SELECT Région, Nombre (*)
DES Clients
GROUPER PAR Région AYANT COUNT(*) > 1 ;
Fonctions de traitement de la valeur
Lorsque vous travaillez avec des données, vous devez souvent les traiter (les convertir à la forme souhaitée) : sélectionner une sous-chaîne dans une chaîne, supprimer les espaces de début et de fin, arrondir un nombre, calculer la racine carrée, déterminer l'heure actuelle, etc. SQL a les trois types de fonctions suivants :
- fonctions de chaîne ;
- fonctions numériques ;
- fonctions date-heure.
Fonctions de chaîne
Les fonctions de chaîne prennent une chaîne comme paramètre et renvoient une chaîne ou NULL après l'avoir traitée.
- SOUS-CHAÎNE (ligne DU début)renvoie une sous-chaîne résultant de la chaîne spécifiée en paramètre doubler . Sous-chaîne commence par le caractère dont le numéro de série est spécifié dans le paramètre start et a la longueur spécifiée dans le paramètre length. Les caractères d'une ligne sont numérotés de gauche à droite, en commençant par 1. Les crochets indiquent ici uniquement que l'expression qu'ils contiennent est facultative. Si l'expression POUR longueur n'est pas utilisé, alors une sous-chaîne de commencer et jusqu'à la fin de la ligne originale. Valeurs des paramètres début et durée doit être choisi de manière à ce que la sous-chaîne recherchée se trouve réellement à l'intérieur de 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 !" FROM 9) renvoie "Masha !" ;
SUBSTRING("Chère Masha!" FROM 15) renvoie NULL.
Vous pouvez utiliser cette fonction dans une expression SQL, par exemple, comme ceci :
SELECT * FROM Clients
OÙ SUBSTRING(Région DE 1 POUR 5) = "Nord" ;
- SUPÉRIEUR(chaîne ) convertit tous les caractères de la chaîne spécifiée dans le paramètre en majuscules.
- INFÉRIEUR(chaîne ) convertit tous les caractères de la chaîne spécifiée dans le paramètre 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) d'une chaîne. Par défaut, le caractère à supprimer est un espace (" "), il peut donc être omis. Le plus souvent, cette fonction est utilisée pour supprimer des espaces.
Par exemple:
TRIM (MENANT " " DE "ville de Saint-Pétersbourg") fait tourner "ville de Saint-Pétersbourg" ;
TRIM(TRALING " " FROM "ville de Saint-Pétersbourg") renvoie "ville de Saint-Pétersbourg" ;
TRIM (BOTH " " FROM " ville Saint-Pétersbourg ") renvoie " ville Saint-Pétersbourg " ;
TRIM(BOTH FROM " ville de Saint-Pétersbourg ") renvoie "ville de Saint-Pétersbourg" ;
TRIM(BOTH "g" FROM "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 peuvent accepter non seulement des données de type numérique comme paramètre, mais renvoient toujours un nombre ou NULL (valeur non définie).
- POSITION ( cibleString DANS chaîne) recherche une occurrence de la chaîne cible dans la chaîne spécifiée. Si la recherche réussit, 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, en commençant par 1.
Par exemple:
POSITION("e" IN "Bonjour tout le monde") renvoie 5 ;
POSITION ("tout le monde" DANS "Bonjour tout le monde") renvoie 8 ;
POSITION(" " Bonjour à tous") renvoie 1 ;
POSITION("Bonjour !" IN "Bonjour à tous") renvoie 0.
Dans le tableau 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 :
SELECT * FROM Clients
OÙ POSITION (" Saint-Pétersbourg " DANS Adresse ) > 0 ;
Notez que cette simple requête de récupération de données peut être formulée différemment :
SELECT * FROM Clients
OÙ Adresse LIKE "%Petersburg%" ;
- EXTRAIT (paramètre ) extrait un élément d'une valeur date-heure ou d'un intervalle. Par exemple:
EXTRAIT (MOIS DE LA DATE "2005-10-25") renvoie 10.
- CHARACTER_LENGTH(chaîne ) renvoie le nombre de caractères dans la chaîne.
Par exemple:
CHARACTER_LENGTH("Bonjour à tous") renvoie 11.
- OCTET_LENGTH(chaîne ) renvoie le nombre d'octets (octets) dans la chaîne. Chaque caractère latin ou cyrillique est représenté par un octet et le caractère de l'alphabet chinois est représenté par deux octets.
- CARDINALITÉ (paramètre ) prend une collection d'éléments comme paramètre et renvoie le nombre d'éléments dans la collection (numéro cardinal). Une collection peut être, par exemple, un tableau ou un multiset contenant des éléments de différents types.
- ABS (numéro ) renvoie la valeur absolue d'un nombre. Par exemple:
ABS (-123) renvoie 123 ;
ABS (2 - 5) renvoie 3.
- MO D (numéro1, numéro2 ) renvoie le reste d'une 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 (la base du logarithme népérien à la puissance du nombre).
- PUISSANCE (numéro1, numéro2 ) renvoie le numéro1 numéro2 (numéro1 à la puissance nombre2).
- SQRT (numéro ) renvoie la racine carrée d'un nombre.
- ÉTAGE (numéro ) renvoie le plus grand entier ne dépassant pas celui spécifié par le paramètre (arrondi à l'inférieur). Par exemple:
ÉTAGE (5,123) renvoie 5,0.
- CEIL (numéro) ou PLAFOND (numéro ) renvoie le plus petit entier qui n'est pas inférieur à la valeur spécifiée par le paramètre d'arrondi). Par exemple:
CEIL(5.123) renvoie 6,0.
- LARGEUR_BUCKET (numéro1, numéro2, numéro3, numéro4) renvoie un entier compris entre 0 et numéro4 + 1. Les paramètres numéro2 et numéro3 spécifient un intervalle numérique divisé en intervalles égaux, dont le nombre est spécifié par le paramètre numéro4. La fonction détermine. le numéro de l'intervalle dans lequel la valeur tombe numéro1. Si le 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
SQL dispose de trois fonctions qui renvoient la date et l'heure actuelles.
- CURRENT_DATE renvoie la date actuelle (tapez DATE).
Par exemple : 2005-06-18.
- CURRENT_TIME (numéro ) renvoie l'heure actuelle (type TIME). Le paramètre entier spécifie la précision de la représentation des secondes. Par exemple, une valeur de 2 représentera les secondes au centième le plus proche (deux décimales) :
12:39:45.27.
- CURRENT_TIMESTAMP (numéro ) renvoie la date et l'heure (type TIMESTAMP). Par exemple, 18/06/2005 12:39:45.27. Le paramètre entier spécifie la précision de la représentation des secondes.
Notez que la date et l'heure renvoyées par ces fonctions ne sont pas un type de 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() pour cela.
Les fonctions date-heure sont couramment utilisées dans les requêtes pour insérer, mettre à jour et supprimer des données. Par exemple, lors de l'enregistrement des informations sur les ventes, vous saisissez date actuelle et le temps. Après avoir résumé les résultats d'un mois ou d'un trimestre, les données de ventes pour la période de référence peuvent être supprimées.
Expressions calculées
Les expressions calculées sont construites à partir de constantes (numériques, chaînes, logiques), de fonctions, de noms de champs et d'autres types de données en les connectant avec des opérateurs arithmétiques, chaînes, logiques et autres. À leur tour, les expressions peuvent être combinées à l’aide d’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ées précédemment.
Opérateurs arithmétiques :
- + ajout ;
- - soustraction ;
- * multiplications ;
- / division.
Opérateur de chaîneun seul opérateur de concaténation ou de concaténation de chaînes (| |). Dans certaines implémentations SQL (par exemple, Microsoft Accès) au lieu de (| |), le symbole (+) est utilisé. L'opérateur de concaténation ajoute la deuxième chaîne à la fin du premier exemple, l'expression :
"Sacha" | | "aime" | | « agitant »
en conséquence, renverra la chaîne "Sasha aime Masha".
Lors de la composition d'expressions, vous devez vous assurer que les opérandes des opérateurs sont de types valides. Par exemple, l'expression : 123 + "Sasha" n'est pas valide car l'opérateur d'addition arithmétique est appliqué à un opérande de chaîne.
Les expressions calculées peuvent apparaître après Instruction SELECT, ainsi que les expressions de condition des instructions WHERE et HAVI N.G.
Regardons quelques exemples.
Laissez la table Sales contenir les colonnes ProductType, Quantity et Price, et nous souhaitons connaître les revenus de 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 Type_produit, Quantité, Prix, Quantité*Prix COMME
Total des ventes ;
Cela utilise le mot-clé AS (as) pour spécifier un alias pour la colonne de données calculée.
Sur la fig. La figure 10 montre la table Sales d'origine et la table des résultats de la requête.
Riz. 10. Résultat de la requête avec calcul du chiffre d'affaires pour chaque type de produit
Si vous souhaitez connaître le revenu total de la vente de tous les biens, utilisez simplement la requête suivante :
SÉLECTIONNER LA SOMME (Quantité*Prix) DES Ventes ;
La requête suivante contient des expressions calculées à la fois dans la liste de colonnes et dans la condition de la clause WHERE. Il sélectionne dans le tableau des ventes les produits dont le chiffre d'affaires est supérieur à 1000 :
SELECT Type_produit, Quantité*Prix AS Total
DE Ventes
OÙ Quantité*Prix > 1000 ;
Supposons que vous souhaitiez obtenir un tableau comportant deux colonnes :
Produit contenant le type de produit et son prix ;
Total contenant les revenus.
Étant donné que dans la table des ventes d'origine, il est supposé que la colonne Product_Type est un caractère (type CHAR) et que la colonne Prix est numérique, lors de la fusion (collage) des données de ces colonnes, il est nécessaire de convertir le type numérique en type de caractère à l'aide de l'option Fonction CAST(). La requête qui effectue cette tâche ressemble à ceci (Fig. 11) :
SELECT Type_Produit | | " (Prix : " | | CAST(Price AS CHAR(5)) | | ")" AS Produit, Quantité*Prix AS Total
DE Ventes ;
Riz. 11. Résultat d'une requête combinant différents types de données dans une seule colonne
Note. Dans Microsoft Access, une requête similaire ressemblerait à ceci :
SELECT Product_Type + " (Prix : " + C Str (Prix) + ")" COMME Article,
Quantité*Prix AS Total
DE Ventes ;
Expressions conditionnelles avec instruction CASE
Les langages de programmation conventionnels ont des opérateurs de saut conditionnel qui vous permettent de contrôler le processus de calcul selon qu'une condition est vraie ou non. En SQL, cet opérateur est CASE (cas, circonstance, instance). Dans 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 des valeurs
L'instruction CASE avec des valeurs a la syntaxe suivante :
CAS valeur_vérifiée
QUAND valeur1 ALORS résultat1
QUAND valeur2 ALORS résultat2
. . .
QUAND la valeur de N ALORS le résultat de N
AUTRE résultatX
Au cas où valeur_vérifiée est égal à la valeur1 , l'instruction CASE renvoie la valeur résultat1 , spécifié après le mot clé THEN. Sinon, la valeur_vérifiée est comparée à valeur2 , et s'ils sont égaux, alors la valeur result2 est renvoyée. Sinon, la valeur testée est comparée à la valeur suivante spécifiée après le mot clé WHEN, etc. Si valeur_testée n'est égale à aucune de ces valeurs, alors la valeur est renvoyée. résultat X , spécifié après le mot clé ELSE (else).
Le mot clé ELSE est facultatif. S'il est manquant et qu'aucune des valeurs comparées n'est égale à la valeur testée, alors l'instruction CASE renvoie NULL.
Disons, sur la base de la table Clients (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 source, alors pour résoudre ce problème, il est pratique d'utiliser une requête avec l'opérateur CASE :
SELECT Nom, Adresse,
CAS Région
QUAND "Moscou" PUIS "77"
QUAND "Région de Tver" ALORS "69"
. . .
Région Ailleurs
AS Code de région
DES Clients ;
Instruction CASE avec conditions de recherche
La deuxième forme de l'opérateur CASE implique son utilisation lors de la recherche dans une table des enregistrements qui satisfont à une certaine condition :
CAS
QUAND condition1 ALORS résultat1
QUAND catch2 ALORS résultat2
. . .
QUAND condition N ALORS résultat N
AUTRE résultatX
L'instruction CASE teste si la condition1 est vraie pour le premier enregistrement de l'ensemble spécifié par la clause WHERE, ou pour la table entière si WHERE n'est pas présent. Si oui, alors CASE renvoie result1. Sinon, la condition2 est vérifiée pour cet enregistrement. Si c'est vrai, alors la valeur result2 est renvoyée, etc. Si aucune des conditions n'est vraie, alors la valeur result est renvoyée X , spécifié après le mot clé ELSE.
Le mot clé ELSE est facultatif. S'il est manquant et qu'aucune des conditions n'est vraie, l'instruction CASE effectue une rotation NULL. Une fois l'instruction contenant CASE exécutée pour le premier enregistrement, elle passe à l'enregistrement suivant. Cela continue jusqu'à ce que l'ensemble des enregistrements ait été traité.
Supposons que dans une table de livre (Titre, Prix), une colonne soit NULL si le livre correspondant est en rupture de stock. La requête suivante renvoie une table qui affiche « En rupture de stock » au lieu de NULL :
SÉLECTIONNER le titre,
CAS
QUAND LE PRIX EST NULL ALORS "En rupture de stock"
AUTRE CAST(Prix COMME CHAR(8))
Prix AS
DE Livres ;
Toutes les valeurs d'une même colonne doivent être du même type. Par conséquent, dans cette demande La fonction de conversion de type CAST permet de convertir les valeurs numériques de la colonne Prix en un type caractère.
Notez que vous pouvez toujours utiliser la deuxième forme de l'instruction CASE au lieu de la première :
CAS
QUAND valeur_testée = valeur1 ALORS résultat1
QUAND valeur_testée = valeur2 ALORS résultat2
. . .
QUAND valeur_vérifiée = valeur N ALORS résultatN
AUTRE résultat
Fonctions NULLIF et COALESCE
Dans certains cas, notamment dans les demandes de mise à jour de données (opérateur UPDATE), il est pratique d'utiliser les fonctions plus compactes NULLIF() (NULL if) et COALESCE() (combine) au lieu de l'opérateur CASE encombrant.
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 vraie, alors la fonction renvoie NULL, sinon la valeur valeur1.
Cette fonction est équivalente à l'instruction CASE sous les deux formes suivantes :
- Valeur CAS1
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 peuvent être définies ou nulles. La fonction renvoie une valeur spécifiée à partir d'une 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 NULL ALORS valeur 1
QUAND la valeur 2 N'EST PAS NULL ALORS valeur 2
. . .
QUAND la valeur N N'EST PAS NULL ALORS la valeur N
AUTRE NUL
Supposons que dans la table Livres (Titre, Prix), la colonne Prix soit NULL si le livre correspondant est en rupture de stock. La requête suivante renvoie une table où, au lieu de NUL Le texte « En rupture de stock » s'affiche :
SELECT Nom, COALESCE (CAST(Price AS CHAR(8)),
"En rupture de stock") AS Prix
DE Livres ;
Comment puis-je connaître le nombre de modèles de PC produits par un fournisseur particulier ? Comment déterminer le prix moyen des ordinateurs dotés du même spécifications techniques? Il est possible de répondre à ces questions et à bien d’autres liées à certaines informations statistiques en utilisant La requête utilise les fonctions intégrées Année et Mois pour extraire l'année et le mois d'une date.. La norme fournit les fonctions d'agrégation suivantes :
Toutes ces fonctions renvoient une valeur unique. Parallèlement, les fonctions COMPTE, MINIMUM Et MAXIMUM applicable à tout type de données, tandis que SOMME Et MOYENNE sont utilisés uniquement pour les champs numériques. Différence entre la fonction COMPTER(*) Et COMPTER(<имя поля>) est que le second ne prend pas en compte les valeurs NULL lors du calcul.
Exemple. Trouvez le prix minimum et maximum pour les ordinateurs personnels :
Exemple. Recherchez le nombre d'ordinateurs disponibles produits par le fabricant A :
Exemple. Si nous sommes intéressés par la quantité divers modèles, produit par le fabricant A, alors la requête peut être formulée comme suit (en utilisant le fait que dans la table Product chaque modèle est enregistré une fois) :
Exemple. Trouver le nombre de modèles différents disponibles produits par le fabricant A. La requête est similaire à la précédente, dans laquelle il fallait déterminer nombre total modèles produits par le fabricant A. Ici, vous devez également trouver le nombre de modèles différents dans le tableau PC (c'est-à-dire disponibles dans le commerce).
Pour garantir que seules des valeurs uniques sont utilisées lors de l'obtention d'indicateurs statistiques, lorsque argument des fonctions d'agrégation peut être utilisé Paramètre DISTINCT. Un autre paramètre TOUS est la valeur par défaut et suppose que toutes les valeurs renvoyées dans la colonne sont comptées. Opérateur,
Si nous avons besoin d’obtenir le nombre de modèles de PC produits tout le monde fabricant, vous devrez utiliser Clause GROUPE PAR, suivant syntaxiquement OÙ clauses.
prix moyen
Clause GROUPE PAR utilisé pour définir des groupes de chaînes de sortie pouvant être appliquées à fonctions d'agrégation (COUNT, MIN, MAX, AVG et SUM). Si cette clause est manquante et que des fonctions d'agrégation sont utilisées, alors toutes les colonnes dont les noms sont mentionnés dans SÉLECTIONNER, devrait être inclus dans fonctions d'agrégation, et ces fonctions seront appliquées à l'ensemble des lignes qui satisfont au prédicat de requête. Sinon, toutes les colonnes de la liste SELECT non inclus dans les fonctions d'agrégation, il faut préciser dans la clause GROUP BY. En conséquence, toutes les lignes de requête de sortie sont divisées en groupes caractérisés par les mêmes combinaisons de valeurs dans ces colonnes.Après cela, des fonctions d'agrégation seront appliquées à chaque groupe. Veuillez noter que pour GROUP BY, toutes les valeurs NULL sont traitées comme égales, c'est-à-dire lors du regroupement par un champ contenant des valeurs NULL, toutes ces lignes tomberont dans un seul groupe. Si s'il y a une clause GROUP BY , dans la clause SELECT pas de fonctions d'agrégation
, la requête renverra simplement une ligne de chaque groupe. Cette fonctionnalité, ainsi que le mot-clé DISTINCT, peuvent être utilisés pour éliminer les lignes en double dans un jeu de résultats.
| Regardons un exemple simple : SELECT modèle, COUNT (modèle) AS Qty_model, AVG (prix) AS Avg_price À PARTIR DU PC |
Modèle GROUPE PAR ; Dans cette demande, pour chaque modèle de PC, leur nombre et leur coût moyen sont déterminés. Toutes les lignes avec les mêmes valeurs
| Le modèle (numéro de modèle) forme un groupe et la sortie SELECT calcule le nombre de valeurs et les valeurs de prix moyennes pour chaque groupe. Le résultat de la requête sera le tableau suivant : | modèle | Qté_modèle |
| 1121 | 3 | 850.0 |
| 1232 | 4 | 425.0 |
| 1233 | 3 | 843.33333333333337 |
| 1260 | 1 | 350.0 |
Prix_moy.
Si le SELECT avait une colonne de date, il serait alors possible de calculer ces indicateurs pour chaque date spécifique. Pour ce faire, vous devez ajouter la date en tant que colonne de regroupement, puis les fonctions d'agrégation seront calculées pour chaque combinaison de valeurs (modèle-date). Il existe plusieurs spécifiques:
- règles pour exécuter des fonctions d'agrégation Si à la suite de la demande aucune ligne reçue
- (ou plus d'une ligne pour un groupe donné), alors il n'y a aucune donnée source pour calculer l'une des fonctions d'agrégation. Dans ce cas, le résultat des fonctions COUNT sera nul et le résultat de toutes les autres fonctions sera NULL. Argument fonction d'agrégation ne peut pas lui-même contenir des fonctions d'agrégation
- (fonction à partir de la fonction). Ceux. dans une requête, il est impossible, par exemple, d'obtenir le maximum de valeurs moyennes. Le résultat de l'exécution de la fonction COUNT est entier
- (ENTIER). D'autres fonctions d'agrégation héritent des types de données des valeurs qu'elles traitent. Si la fonction SOMME produit un résultat supérieur à la valeur maximale du type de données utilisé,.
erreur Ainsi, si la demande ne contient pas Clause GROUPE PAR fonctions d'agrégation, Que inclus dans Clause SELECT Clause GROUPE PAR, sont exécutés sur toutes les lignes de requête résultantes. Si la demande contient , chaque ensemble de lignes qui a les mêmes valeurs d'une colonne ou d'un groupe de colonnes spécifié dans Clause GROUPE PAR fonctions d'agrégation sont effectués pour chaque groupe séparément.
Détermination du coût total de chaque produit pour chaque mois.
Après cela, des fonctions d'agrégation seront appliquées à chaque groupe. Veuillez noter que pour GROUP BY, toutes les valeurs NULL sont traitées comme égales, c'est-à-dire lors du regroupement par un champ contenant des valeurs NULL, toutes ces lignes tomberont dans un seul groupe. Clause OÙ définit un prédicat pour filtrer les lignes, puis AVOIR une offre s'applique après le regroupement pour définir un prédicat similaire qui filtre les groupes par valeurs fonctions d'agrégation. Cette clause est nécessaire pour valider les valeurs obtenues en utilisant fonction d'agrégation pas à partir de lignes individuelles de la source d'enregistrement définie dans Clause DE, et de groupes de telles lignes. Un tel contrôle ne peut donc pas être contenu dans Clause OÙ.