Fonctions globales Utilisé de la même manière par des noms de champs dans l'instruction SELECT, mais à une exception près: ils prennent le nom du champ comme argument. Avec des fonctions SOMME. et Avg. Seuls les champs numériques peuvent être utilisés. Avec des fonctions Compte, max et min Pourrait être utilisé à la fois des champs numériques et symboliques. Lorsqu'il est utilisé avec des champs symboliques Max et Min. Ils les diffuseront dans l'équivalent et le processus de code ASCII dans l'ordre alphabétique. Certains SGDM permettent d'utiliser des agrégats imbriqués, mais il s'agit d'une déviation de la norme ANSI avec toutes les conséquences résultant d'ici.

Par exemple, vous pouvez calculer le nombre d'élèves qui ont cédé des examens pour chaque discipline. Pour ce faire, faites une demande avec un groupe par le champ "Discipline" et dérive le nom de la discipline et le nombre de lignes du groupe sous cette discipline. L'application du symbole * comme argument de la fonction de comptage signifie compter toutes les lignes du groupe.

Sélectionnez R1. Discipline, compte (*)

Groupe par r1.discipline;

Résultat:

Sélectionnez R1.Discipline, comptez (*)

Où r1. N'est pas une évaluation nulle

Groupe par r1.discipline;

Résultat:

il ne tombera pas dans l'ensemble des tuples avant le regroupement, de sorte que le nombre de tuples dans le groupe de la discipline "La théorie de l'information" sera de 1 moins.

Un résultat similaire peut être obtenu si vous écrivez une demande de la manière suivante:

Sélectionnez R1. Discipline, compte (R1. Évaluation)

Groupe par R1. La discipline;

Une fonction Compte (nom d'attribut) considère le nombre de certaines valeurs du groupe, contrairement à la fonction Compter (*), Qui considère le nombre de lignes du groupe. En effet, dans le groupe avec la discipline "La théorie des informations" sera de 4 lignes, mais seulement 3 valeurs de l'attribut "évaluation".

NULL valeurs règles dans les fonctions globales

Si des valeurs dans la colonne sont égales NUL Lors du calcul du résultat de la fonction, ils sont exclus.

Si toutes les valeurs de la colonne sont égales NUL T. Max min Sum avg \u003d Null, comptez \u003d 0 (zéro).

Si la table est vide, count (*) \u003d 0 .

Vous pouvez appliquer des fonctions agrégées et sans une opération pré-groupe, dans ce cas, toute la relation est considérée comme un groupe et pour ce groupe, vous pouvez calculer une valeur au groupe.

Règles pour l'interprétation des fonctions agrégées

Les fonctions d'agrégat peuvent être incluses dans la liste de sortie, puis s'appliquent à la table entière.

Sélectionnez Max (évaluation) de R1 donnera l'évaluation maximale à la session;

Sélectionnez Somme (évaluation) de R1 donnera le montant de toutes les estimations sur CEXSI;

Sélectionnez AVG (évaluation) de R1 Poussière note moyenne tout au long de la session.

2; Résultat: "largeur \u003d" 640 "

En contactant la base de données «Session» (Tables R1), nous trouverons le nombre d'examens réussi:

Sélectionnez Count (*) comme Remis _ examens

Où ration 2;

Résultat:

L'argument des fonctions globales peut être des colonnes de tables séparées. Afin de calculer, par exemple, le nombre valeurs différentes une colonne du groupe, vous devez appliquer mot-clé Distinct avec le nom de la colonne. Nous calculons le nombre d'estimations différentes obtenues pour chaque discipline:

Sélectionnez R1. Discipline, Count (distinct R1.Cécry)

Où r1. N'est pas une évaluation nulle

Groupe par r1.discipline;

Résultat:

Le même résultat est obtenu si vous excluez une condition explicite dans la partie où la requête sera la forme suivante:

Sélectionnez R1. Discipline, compte (distinct R1. Évaluation)

Groupe par R1. La discipline;

Une fonction Count (distinct R1.Cécry) Il considère que certains certains divers valeurs.

Pour et dans ce cas, il a été reçu le résultat souhaitéIl est nécessaire de pré-convertir le type de données de la colonne "Evaluation", en l'apportant au type réel, le résultat du calcul de la moyenne ne sera pas un entier. Dans ce cas, la demande ressemblera à ceci:

2 groupe par R2. Groupe, R1. La discipline; Ici, la fonction Cast () effectue la conversion de la colonne "Évaluation" sur le type de données réel. "Largeur \u003d" 640 "

Sélectionnez R2.Group, R1. Discipline, Count (*) Comme total, AVG (Cast (comme évaluation décimale (3.1))) comme Middle_Ball

De r1, r2

où r1. Pho \u003d r2. Nom complet et r1. N'est pas une évaluation nulle

et r1. Évaluation 2.

Groupe par r2. Groupe, R1. La discipline;

Voici une fonction Jeter () Effectue la conversion de la colonne "Évaluation" sur le type de données réel.

Vous ne pouvez pas utiliser les fonctions agrégées de la clause WHERE, car les conditions de cette section sont estimées en termes d'une seule ligne et de fonctions globales sont en termes de groupes de lignes.

L'offre de groupe BA vous permet de déterminer le sous-ensemble de valeurs dans un champ spécial en termes d'un autre champ et d'appliquer la fonction de l'unité à un sous-ensemble. Cela permet de combiner des champs et des fonctions agrégées dans une seule phrase de sélection. Les fonctions globales peuvent être utilisées à la fois pour exprimer la sortie de la chaîne de sélection et dans l'expression des conditions de traitement des groupes formés. Dans ce cas, chaque fonction d'agrégat est calculée pour chaque groupe sélectionné. Les valeurs obtenues lors du calcul des fonctions d'agrégat peuvent être utilisées pour produire les résultats pertinents ou pour la condition de sélection des groupes.

Nous allons construire une demande qui affiche des groupes dans lesquels plus d'une discipline obtenue lors des examens:

une; Résultat: "largeur \u003d" 640 "

Sélectionnez R2. Grouper

De r1, r2

Où r1. Pho \u003d r2. Nom complet et.

R1.anceton \u003d 2.

Groupe de r2.group, R1. Discipline

Ayant compté (*) 1;

Résultat:

Nous avons la "banque" de la base de données, composée d'un tableau F, qui stocke le ratio f contenant des informations sur les comptes dans les branches de certaines banques:

Trouvez un solde total dans les branches. Vous pouvez faire une requête séparée pour chacune d'elles en sélectionnant la somme (résidu) à partir de la table pour chaque branche, mais le groupe en regroupant l'opération vous permettra de les mettre dans une seule commande:

SÉLECTIONNER Branche , SOMME ( Résidu )

Groupe par branche;

Par groupe. Applique les fonctions globales de manière indépendante pour chaque groupe, déterminé par le champ du champ de la succursale. Le groupe est constitué de lignes avec la même valeur de champ de champ et une fonction SOMME. Il est appliqué séparément pour chaque groupe de ce type, c'est-à-dire que la balance totale des comptes est calculée séparément pour chaque branche. La valeur du champ auxquelles s'applique Par groupe. Il est possible de définir une seule valeur au groupe de sortie, ainsi que du résultat du fonctionnement de la fonction d'agrégat.

5 000; Les arguments de la présence d'offre sont soumis aux mêmes règles que dans la clause Select, où groupe par est utilisé. Ils doivent avoir une valeur au groupe de sortie. "Largeur \u003d" 640 "

Supposons que certaines branches ne choisissent que les valeurs totales des soldes sur les comptes dépassant 5 000 $, ainsi que les soldes totaux pour les branches sélectionnées. Pour apporter des succursales, le total des résidus dans lesquels plus de 5 000 dollars doivent utiliser l'offre d'utilisation. La présence d'offre détermine les critères utilisés pour supprimer certains groupes de la sortie, tout comme l'offre où l'offre est-elle pour les lignes individuelles.

La commande correcte sera la suivante:

Sélectionnez une branche, somme (résidus)

Par groupe. Branche

Avoir une somme ( Résidu ) 5 000;

Arguments dans la phrase Ayant obéir aux mêmes règles que dans la proposition SÉLECTIONNER a été utilisé Par groupe. . Ils doivent avoir une valeur au groupe de sortie.

La commande suivante sera interdite:

Sélectionnez une branche, somme (résidus)

Groupe par branche

Avoir une crack de données \u003d 27/12/2004 ;

Domaine Date d'ouverture ne peut pas être utilisé dans la proposition Ayant Parce qu'il peut avoir plus d'une valeur au groupe de sortie. Pour éviter une telle situation Ayant devrait se référer uniquement aux agrégats et aux champs sélectionnés Par groupe. . Disponible le droit chemin Faire la requête ci-dessus:

Sélectionnez une branche, somme (résidus)

Où datotoberie \u003d '27 / 12/2004 '

Groupe par branche;

Le sens de cette demande est la suivante: trouver le montant des résidus de chaque branche des comptes ouverts le 27 décembre 2004.

Comme mentionné précédemment, il ne peut utiliser que des arguments ayant une valeur au groupe de sortie. Les références pratiquement aux fonctions agrégées sont les plus courantes, mais également les champs sélectionnés à l'aide de groupe par le groupe sont également autorisés. Par exemple, nous voulons voir le total des soldes sur les comptes de succursales à Saint-Pétersbourg, Pskov et Uryupinsk:

Sélectionnez une branche, somme (résidus)

De f, q

Où F. branche \u003d Q. Branche

Groupe par branche

Avoir une branche ('Saint-Pétersbourg', Pskov, «Uryupinsk»);

100 000; Si le résidu total est supérieur à 100 000 $, nous le verrons dans une attitude résultante, sinon nous aurons une attitude vide. "Largeur \u003d" 640 "

Par conséquent, dans les expressions arithmétiques des prédicats inclus dans la condition d'échantillon d'échantillon, seules les spécifications de colonne spécifiées sous forme de colonnes de regroupement peuvent être utilisées directement dans le groupe par section. Les colonnes restantes ne peuvent être spécifiées que dans la spécification des unités de comptage, de SUM, d'AVG, min et max, qui calculent une certaine valeur d'agrégation pour l'ensemble du groupe de lignes dans ce cas. Le résultat de la saisie d'une partition est la table regroupée ne contenant que les groupes de chaînes pour lesquels le résultat du calcul de la condition de sélection de la partie de l'ayant est vrai. En particulier, si la section présentée dans une requête qui ne contient pas de groupe par, le résultat de son exécution sera soit une table vierge, soit le résultat des partitions précédentes de l'expression de table, considérée comme un groupe sans regroupement Colonnes. Considérer un exemple. Supposons que nous voulions retirer le montant total des soldes sur toutes les branches, mais seulement s'il s'agit de plus de 100 000 $. Dans ce cas, notre demande ne contiendra pas les opérations de regroupement, mais contiendra la présentation de la section et sera la suivante:

Sélectionnez Somme ( Résidu )

Avoir une somme ( Résidu ) 100 000;

Si le résidu total est supérieur à 100 000 $, nous le verrons dans une attitude résultante, sinon nous aurons une attitude vide.

PAR GROUPE (Sélectionnez les instructions) Vous permet de regrouper des données (chaînes) sur n'importe quelle colonne ou plusieurs colonnes ou expressions. Le résultat sera un ensemble de lignes de synthèse.

Chaque colonne de la liste des échantillons doit être présente dans le groupe par suggestion, l'exception n'est que des constantes et des colonnes - des opérandes de fonctions agrégées.

La table peut être regroupée par une combinaison de ses colonnes.

Fonctions globales Utilisé pour obtenir une seule valeur totale d'un groupe de chaînes. Toutes les fonctions globales effectuent des calculs au-dessus d'un argument, qui peut être une colonne ou une expression. Le résultat du calcul de toute fonction d'agrégat est la valeur constante affichée dans une colonne séparée du résultat.

Les fonctions globales sont indiquées dans la liste Sélectionner des colonnes d'instructions, qui peuvent également contenir le groupe par offre. Si l'instruction SELECT ne contient pas le groupe par proposition et que la liste des colonnes d'échantillonnage contient au moins une fonction agrégée, elle ne doit pas contenir de colonnes simples. D'autre part, la liste d'échantillonnage de la colonne peut contenir des noms de colonne qui ne sont pas des arguments d'une fonction agrégée si ces colonnes servent de groupe par des arguments d'offre.

Si la requête contient la proposition où la proposition, les fonctions globales calculent la valeur pour les résultats des échantillons.

Fonctions d'agrégats min et max Calculez respectivement la valeur la plus petite et la plus grande valeur de la colonne. Les arguments peuvent être des chiffres, des lignes et des dates. Tout valeurs null Supprimer avant le calcul (c'est-à-dire que le calcul n'est pas pris).

Sum Fonction agrégée Calcule la quantité totale de valeurs de colonne. Les arguments ne peuvent être que des chiffres. L'utilisation du paramètre distinct élimine toutes les valeurs répétitives de la colonne avant d'appliquer la fonction Somme. De même, toutes les valeurs NULL sont supprimées avant d'appliquer cette fonction agrégée.

Fonction d'agrégation AVG. Renvoie la valeur moyenne de toutes les valeurs de colonne. Les arguments ne peuvent également être que des chiffres et toutes les valeurs nuls sont supprimées avant de calculer.

Fonction de comptage globale Il a deux formes différentes:

Count (Name) - compte le nombre de valeurs dans la colonne COL_NAME, les valeurs NULL ne sont pas prises en compte.
Count (*) - compte le nombre de lignes de la table, les valeurs null sont également prises en compte.

Si le mot clé distinct est utilisé dans la requête, toutes les valeurs de colonne répétitives sont supprimées avant d'appliquer la fonction de comptage.

Count_big fonction Semblable à la fonction de comptage. La seule différence entre eux est le type de résultat renvoyé: la fonction Count_big renvoie toujours la valeur de type Bigint, tandis que la fonction de comptage renvoie les données des données de type entier.

DANS offrant avoir. La condition qui s'applique au groupe de cordes est déterminée. Il a la même signification pour les groupes de lignes que la proposition de proposition de contenu de la table correspondante (où s'applique au regroupement, après).

Comment découvrir le nombre de modèles PC fabriqués par un ou plusieurs fournisseurs? Comment déterminer le prix moyen des ordinateurs qui ont le même caractéristiques? Celles-ci et de nombreux autres problèmes liés à certaines informations statistiques peuvent être obtenus en répondant à fonctions totales (agrégates). Les fonctions globales suivantes sont fournies:

Toutes ces fonctions renvoient la seule signification. Dans ce cas Compte, min. et Max applicable à tous types de données, tandis que SOMME. et Avg. Utilisé uniquement pour les champs numériques. La différence entre la fonction COMPTER (*) et Compter (<имя поля>) C'est que la seconde lorsque le comptage ne prend pas en compte les valeurs nulles.

Exemple. Trouvez le prix minimum et maximum des ordinateurs personnels:

Exemple. Trouvez Disponible Nombre disponible d'ordinateurs émis par le fabricant A:

Exemple. Si nous sommes intéressés par le nombre de modèles différents fabriqués par le fabricant A, la demande peut alors être formulée comme suit (en utilisant le fait que dans la table du produit, chaque modèle est enregistré une fois):

Exemple. Trouvez le nombre de modèles différents fabriqués par le fabricant A. La demande est similaire à celle du précédent, dans laquelle il était nécessaire de déterminer le nombre total de modèles fabriqués par le fabricant A. Il doit également trouver le nombre de modèles différents. dans la table PC (c'est-à-dire disponible dans le commerce).

Pour obtenir des indicateurs statistiques, seules des valeurs uniques, argument des fonctions globales peut être utilisé paramètre distinct. Autre paramètre tout. Utilisé par défaut et suppose compter toutes les valeurs retournées dans la colonne. Opérateur,

Si nous avons besoin d'obtenir le nombre de modèles PC produisés À chaque fois que fabricant, alors il sera nécessaire d'utiliser pAR GROUPESyntaxiquement ensuite après offres où..

PAR GROUPE

PAR GROUPE utilisé pour déterminer les groupes de chaînes de sortie auxquels peut être utilisé fonctions globales (comptage, min, max, avg et somme). Si cette proposition est manquante et que des fonctions agrégées sont utilisées, alors toutes les colonnes avec les noms mentionnés dans SÉLECTIONNERdoit être inclus dans fonctions globalesEt ces fonctions s'appliqueront à l'ensemble de la ligne qui satisfasse au prédicat de la requête. Sinon, toutes sélectionnent des colonnes de liste, non inclus Dans les fonctions globales, doit être indiquée dans le groupe par. 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, les fonctions globales seront appliquées à chaque groupe. Il convient de garder à l'esprit que pour le groupe par toutes les valeurs null sont traités comme étant égaux, c'est-à-dire. Lors du regroupement sur le champ contenant des valeurs nulles, toutes ces lignes tomberont dans un groupe.
Si un avec l'offre de groupe par, dans certains choix il n'y a pas de fonctions agrégéesLa demande renvoie simplement une ligne de chaque groupe. Cette fonctionnalité, avec le mot-clé distinct, peut être utilisé pour éliminer les doublons de lignes dans l'ensemble résultant.
Considérons un exemple simple:

Sélectionnez le modèle, le nombre (modèle) comme Qty_Model, avg (prix) en tant qu'AVG_PRICE
De PC.
Groupe par modèle;

Dans cette requête, leur nombre et leur coût moyen sont déterminés pour chaque modèle PC. Toutes les chaînes avec les mêmes valeurs de modèle (numéro de modèle) forment un groupe et le nombre de valeurs et de prix moyens pour chaque groupe sont calculés à la sortie SELECT. Le résultat de la requête sera le tableau suivant:

modèle	Qty_Model	Avg_price.
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

Si la sélection avait une colonne avec la date, il serait possible de calculer ces indicateurs pour chaque date précise. Pour ce faire, ajoutez une date sous forme de colonne de regroupement, puis les fonctions globales auraient été calculées pour chaque combinaison de valeurs (date de modèle).

Il y a plusieurs spécifiques règles pour effectuer des fonctions agrégées:

Si à la suite de l'exécution de la requête pas reçu de chaîne (Ou pas une seule chaîne pour ce groupe), les données initiales pour calculer l'une des fonctions d'agrégat sont manquantes. Dans ce cas, le résultat des fonctions de comptage sera de zéro et le résultat de toutes les autres fonctions est NULL.
Argument fonction globale ne peut pas contenir des fonctions d'agrégat (fonction de fonction). Ceux. Dans une demande, il est impossible, disons, obtenez un maximum de valeurs moyennes.
Le résultat de la fonction de comptage est entier (Entier). Autres fonctions agrégées hériter des types de données de valeurs traitées.
Si, lors de l'exécution de la fonction Somme, le résultat a été obtenu supérieur à la valeur maximale du type de données utilisé, survient. erreur.

Donc, si la requête ne contient pas groupe de suggestions par.T. fonctions globalesinclus dans B. sélectionner une offresont exécutés au-dessus de toutes les lignes de requête résultantes. Si la requête contient pAR GROUPE, chaque ligne de ligne qui a la même valeur de colonne ou des mêmes groupes de colonnes spécifiées dans groupe de propositions par.constitue un groupe et fonctions globales Effectué pour chaque groupe séparément.

Offre avoir

Si un offre où détermine le prédicat à filtrer les chaînes, puis offre avoir Appliqué après regroupement Déterminer un groupe de filtrage par prédicat similaire par des valeurs fonctions globales. Cette offre est nécessaire pour vérifier les valeurs obtenues par fonction globale pas de lignes individuelles de la source d'enregistrements définis dans offre de., et de groupes de cette chaîne. Par conséquent, une telle vérification ne peut pas être contenue dans offrir où..

Bonjour! Aujourd'hui, nous vous familiariserons avec des fonctions globales dans SQL, nous analyserons en détail les données des tables créées dans les cours précédents.

Concept général

Dans la dernière leçon, nous avons rencontré comment les demandes de données sont construites. Les fonctions globales existent pour avoir la possibilité de généraliser les données obtenues de quelque manière que ce soit, c'est-à-dire de les manipuler comme il le souhaite.

Ces fonctions sont effectuées à l'aide des mots-clés inclus dans la demande SELECT et comment ils sont prescrits seront décrits plus loin. Pour préciser, voici quelques possibilités de fonctions globales dans SQL:

Résumez les valeurs sélectionnées
Trouver des valeurs arithmétiques moyennes
Trouver minimal et maximum de valeurs

Exemples de fonctions agrégat SQL

Nous analyserons les fonctionnalités les plus couramment utilisées et donnerons quelques exemples.

Fonction de somme

Cette fonctionnalité vous permet de résumer les valeurs de n'importe quel champ lors de la demande de sélection. Suffisant fonctionnalité utiledont la syntaxe est assez simple comme toutes les autres fonctions d'agrégat dans SQL. Pour comprendre immédiatement, commençons avec l'exemple:

Obtenez le montant de toutes les commandes de la table des commandes, qui ont été commises en 2016.

Il serait possible de simplement retirer le montant des ordres, mais il me semble que c'est assez simple. Rappelez-vous la structure de notre table:

démon	aMT.	odate.	cnum.	snum.
1001	128	2016-01-01	9	4
1002	1800	2016-04-10	10	7
1003	348	2017-04-08	2	1
1004	500	2016-06-07	3	3
1005	499	2017-12-04	5	4
1006	320	2016-03-03	5	4
1007	80	2017-09-02	7	1
1008	780	2016-03-07	1	3
1009	560	2017-10-07	3	7
1010	900	2016-01-08	6	8

Le code suivant établira l'échantillon souhaité:

Sélectionnez Sum (AMT) des commandes où ODATE entre "2016-01-01" et "2016-12-31";

En conséquence, nous obtenons:

Somme (AMT)
4428

Dans cette requête que nous avons utilisée fonction de sommeAprès quoi entre parenthèses, vous devez spécifier le champ pour la somme. Ensuite, nous avons indiqué la condition dans laquelle, quelles lignes sélectionnées que depuis 2016. En fait, cette condition peut être enregistrée différemment, mais la fonction de sommation globale de SQL est plus importante.

Fonction avgérée

La fonction suivante calcule le champ de données arithmétiques moyen, que nous indiquons comme paramètre. La syntaxe d'une telle fonction est identique à la fonction de sommation. Par conséquent, nous allons immédiatement passer à la tâche la plus simple:

Affiche le coût moyen de la commande de la table des commandes.

Et demande immédiatement:

Sélectionnez AVG (AMT) à partir des commandes;

En conséquence, nous obtenons:

Il vaut également la peine de dire que, contrairement aux fonctions précédentes, ces 2 peuvent travailler avec des paramètres symboliques, c'est-à-dire que vous pouvez écrire un type de demande. Min (odate) (Dans ce cas, la date de notre part est symbolique), puis nous reviendrons à 2016-01-01.

Le fait est que, dans ces fonctions, il existe un mécanisme de conversion de symboles dans le code ASCII, qui se comparent ensuite.

Un autre un point important C'est que nous pouvons produire des opérations mathématiques simples dans la demande sélectionnée, par exemple une telle demande:

Sélectionnez (Max (Amt) - Min (AMT)) comme "différence" des commandes;

Retournera cette réponse:

De toute évidence, le nombre de commandes est 10, mais si vous avez soudainement une grande table, cette fonction sera très confortable. En ce qui concerne les vendeurs uniques, il est nécessaire d'utiliser distinct ici, car un vendeur peut servir plusieurs commandes.

Groupe de l'opérateur par.

Maintenant considérer 2. opérateur importantCela aide à élargir les fonctionnalités de nos questions dans SQL. Le premier de ceux-ci est le groupe par opérateur, qui effectue un regroupement sur n'importe quel domaine parfois nécessaire. Et déjà pour ce groupe produit une action spécifiée. Par example:

Supprimez le montant de toutes les commandes pour chaque vendeur séparément.

C'est-à-dire que nous avons maintenant besoin pour chaque vendeur de la table des commandes pour mettre en évidence les champs avec le prix de commande et résumer. Tout cela rendra le groupe par déclaration dans SQL assez facile:

Seelect snum, somme (AMT) comme "La somme de toutes les commandes" Du groupe de commandes par snum;

Et à la fin nous obtenons:

snum.	La somme de toutes les commandes
1	428
3	1280
4	947
7	2360
8	900

Comme vous pouvez le constater, SQL a alloué un groupe pour chaque vendeur et a examiné la somme de toutes leurs commandes.

Opérateur ayant

Cet opérateur est utilisé comme ajout au précédent. Il est nécessaire de mettre les conditions d'échantillonnage lors du regroupement. Si la condition est effectuée, le groupe est attribué, sinon, rien ne se passe. Considérez le code suivant:

Seelect snum, somme (AMT) comme "La somme de toutes les commandes" Du groupe de commandes par snum ayant max (AMT)\u003e 1000;

Qui créera un groupe pour le vendeur et considère le nombre d'ordres de ce groupe uniquement si quantité maximale Commandez plus de 1000. Il est évident qu'un tel vendeur n'est qu'un, un groupe sera distingué pour lui et la somme de toutes les commandes sera considérée comme suit:

snum.	La somme de toutes les commandes
7	2360

Il semblerait pourquoi ici ne pas utiliser la situation où la condition, mais SQL est tellement construite que dans ce cas, cela donnera une erreur, et c'est pourquoi il y a un opérateur en SQL.

Exemples pour fonctions agrégées dans SQL

1. Écrivez une demande qui affirmerait toutes les montants des commandes effectuées le 1er janvier 2016.

Sélectionnez Sum (AMT) des commandes où Odate \u003d "2016-01-01";

2. Écrivez une demande qui calculerait le nombre de différents différents des champs NULL du champ de la ville de la table client.

Sélectionnez Compte (ville distincte) des clients;

3. Écrivez une demande qui aurait choisi le montant le plus petit pour chaque client.

Sélectionnez CNUM, MIN (AMT) du groupe de commandes par CNUM;

4. Écrivez une demande que je choisirais des clients dont les noms commencent par les lettres G.

Sélectionnez CNAME à partir de clients où CName comme "g%";

5. Écrivez une demande qui aurait choisi la note la plus élevée dans chaque ville.

Sélectionnez la ville, Max (note) du groupe des clients par ville;

Conclusion

Sur ce que nous allons finir. Dans cet article, nous avons connu des fonctions agrégées dans SQL. Démonté les concepts principaux et des exemples de base qui peuvent être utiles plus avant.

Si vous avez des questions, demandez-leur dans les commentaires.

Les sous-sections suivantes décrivent d'autres instructions SELECT, qui peuvent être utilisées dans des requêtes, ainsi que des fonctions agrégées et des ensembles d'opérateurs. Laissez-moi vous rappeler, K. ce moment Nous avons examiné l'utilisation de la proposition de l'endroit et, dans cet article, nous examinerons les propositions de groupe, commander et avoir des exemples d'utilisation de ces propositions en association avec des fonctions globales prises en charge dans Transact-SQL.

PAR GROUPE

Phrase Par groupe. Groupes La ligne sélectionnée définie pour obtenir une numérotation de lignes de synthèse par les valeurs d'une ou plusieurs colonnes ou expressions. Le simple cas d'application du groupe par phrase est indiqué dans l'exemple ci-dessous:

Utiliser échantillondb; Sélectionnez le travail de Works_on Group par emploi;

Dans cet exemple, un échantillon et un regroupement de postes d'employés se produisent.

Dans l'exemple ci-dessus, le groupe par offre crée un groupe distinct pour toutes les valeurs possibles (y compris la valeur NULL) de la colonne Job.

L'utilisation de colonnes dans le groupe par clause doit répondre à certaines conditions. En particulier, chaque colonne de la liste d'échantillonnage de la requête doit également être présente dans le groupe par suggestion. Cette exigence ne s'applique pas aux constantes et aux colonnes qui font partie de la fonction d'agrégat. (Les fonctions globales sont discutées dans la sous-section suivante.) C'est logique, car Seulement pour les colonnes du groupe par clause est garantie une valeur pour chaque groupe.

La table peut être regroupée par une combinaison de ses colonnes. Dans l'exemple ci-dessous, le regroupement des lignes de table de Works_On sur deux colonnes est démontré:

Utiliser échantillondb; Sélectionnez ProjectorNumber, emploi de Works_on Group par ProjectNumber, Job;

Le résultat de cette demande:

Selon les résultats de la requête, vous pouvez voir qu'il y a neuf groupes avec différentes combinaisons du numéro de projet et de la position. La séquence de colonnes du groupe par clause ne doit pas nécessairement être identique à celle de la liste de colonnes de sélection.

Fonctions globales

Les fonctions globales sont utilisées pour obtenir les valeurs totales. Toutes les fonctions globales peuvent être divisées en catégories suivantes:

fonctions globales ordinaires;

fonctions d'agrégats statistiques;

fonctions agrégates définies par l'utilisateur;

fonctions d'agrégats analytiques.

Nous examinerons ici les trois premiers types de fonctions agrégées.

Fonctions globales normales

Le langage Transact-SQL prend en charge les six fonctions globales suivantes: Min., Max, SOMME., Avg., Compter, Comte_big..

Toutes les fonctions globales effectuent des calculs au-dessus d'un argument, qui peut être une colonne ou une expression. (La seule exception est la deuxième forme de deux fonctions: comptage et comte_big, nommément comptage (*) et comte_big (*), respectivement.) Le résultat du calcul de toute fonction globale est la valeur constante affichée dans une colonne séparée du résultat.

Les fonctions globales sont spécifiées dans la liste Sélectionner des colonnes d'instructions, qui peuvent également contenir le groupe par offre. Si l'instruction SELECT ne contient pas le groupe par propositions et que la liste des colonnes d'échantillonnage contient au moins une fonction d'agrégat, elle ne doit pas contenir de colonnes simples (à l'exception des colonnes qui servent d'arguments de fonction agrégées). Par conséquent, le code dans l'exemple est incorrect:

Utiliser échantillondb; Sélectionnez Nom, min (ID) de l'employé;

Ici, la colonne de la dernière nom de la table des employés ne doit pas figurer dans la liste d'échantillonnage de la colonne, car elle n'est pas un argument de la fonction globale. D'autre part, la liste d'échantillonnage de la colonne peut contenir des noms de colonne qui ne sont pas des arguments d'une fonction agrégée si ces colonnes servent de groupe par des arguments d'offre.

L'argument de la fonction globale peut précéder l'un des deux mots clés possibles:

Tout.

Indique que les calculs sont effectués au-dessus de toutes les valeurs de la colonne. Ceci est la valeur par défault.

Distinct.

Indique que seules les valeurs uniques de la colonne s'appliquent aux calculs.

Fonctions d'agrégats min et max

Les fonctions agrégées MIN et Max calculent respectivement la plus petite et la plus grande valeur de colonne. Si la requête contient la proposition où la proposition, les fonctions min et max renvoient la plus petite et la plus grande valeurs des lignes correspondant aux conditions spécifiées. L'exemple ci-dessous montre l'utilisation de la fonction d'agrégat MIN:

Utiliser échantillondb; - retournera le 2581 Sélectionnez Min (ID) comme "ID minimum" de l'employé;

Retourné dans l'exemple ci-dessus Le résultat n'est pas très informatif. Par exemple, le nom de l'employé appartenant à ce numéro est inconnu. Mais obtenir ce nom de famille dans la méthode conventionnelle Il est impossible car, comme mentionné précédemment, spécifiez explicitement que la colonne de dernière nom n'est pas autorisée. Afin de recevoir également le nom de cet employé avec le nom de famille le plus petit, la sous-requête est utilisée. L'exemple ci-dessous montre l'utilisation d'une telle sous-compréhension, où la requête investie contient l'instruction SELECT dans l'exemple précédent:

Demande résultat:

L'utilisation de la fonction d'agrégation maximale est indiquée dans l'exemple ci-dessous:

En tant qu'argument, la fonction MIN et Max peut également recevoir des lignes et des dates. Dans le cas d'un argument de chaîne, les valeurs sont comparées à l'aide de l'ordre de tri actuel. Pour tous les arguments de données temporaires de type "date", les plus petites valeurs de la colonne seront la date la plus ancienne et le plus grand est le dernier.

Avec les fonctionnalités min et max, vous pouvez appliquer le mot-clé distinct. Avant d'appliquer les fonctions d'agrégat MIN et Max, toutes les valeurs NULL sont exclues des colonnes de leurs arguments.

Sum Fonction agrégée

Agrégat fonction de somme Calcule la quantité totale de valeurs de colonne. L'argument de cette fonction d'agrégat doit toujours avoir un type de données numérique. L'utilisation de la fonction de somme globale est indiquée dans l'exemple ci-dessous:

Utiliser échantillondb; Sélectionnez Somme (budget) «budget d'été» du projet;

Dans cet exemple, la quantité totale de budgets de tous les projets est calculée. Demande résultat:

Dans cet exemple, la fonction globale augmente toutes les valeurs des budgets de projet et détermine leur montant total. Pour cette raison, la demande contient une fonction de regroupement implicite (ainsi que toutes les demandes similaires). La fonction de regroupement implicite de l'exemple ci-dessus peut être spécifiée explicitement, comme indiqué dans l'exemple ci-dessous:

Utiliser échantillondb; Sélectionnez la somme (budget) «budget total» du groupe de projet par ();

L'utilisation du paramètre distinct élimine toutes les valeurs répétitives de la colonne avant d'appliquer la fonction Somme. De même, toutes les valeurs NULL sont supprimées avant d'appliquer cette fonction agrégée.

Fonction d'agrégation AVG.

Agrégat fonction avgérée Renvoie la valeur arithmétique moyenne pour toutes les valeurs de colonne. L'argument de cette fonction d'agrégat doit toujours avoir un type de données numérique. Avant d'appliquer la fonction AVG, toutes les valeurs NULL sont supprimées de son argument.

L'utilisation de la fonction d'agrégation AVG est affichée dans l'exemple ci-dessous:

Utiliser échantillondb; - renvoie 133833 Select AVG (budget) "Budget intermédiaire pour le projet" du projet;

Voici le calcul de la valeur budgétaire arithmétique moyenne de tous les budgets.

Compte et comte_big

Agrégat fonction de comptage Il a deux formes différentes:

Count (Col_Name) Nombre (*)

La première forme de fonction compte le nombre de valeurs dans la colonne de colonne. Si le mot clé distinct est utilisé dans la requête, toutes les valeurs de colonne répétitives sont supprimées avant d'appliquer la fonction de comptage. Lors du comptage du nombre de valeurs de colonne, cette forme de la fonction de comptage ne prend pas en compte les valeurs de NULL.

L'utilisation de la première forme de la fonction d'agrégat de comptage est indiquée dans l'exemple ci-dessous:

Utiliser échantillondb; Sélectionnez ProjectNumber, compter (travail distinct) «Fonctionne dans le projet» du groupe Works_on par ProjectNumber;

Voici les comptes de différentes positions pour chaque projet. Le résultat de cette demande:

Comme on peut le constater à la suite de l'exécution de la demande présentée dans l'exemple, les valeurs nuls de la fonction de comptage n'ont pas été prises en compte. (La somme de toutes les valeurs des colonnes postales s'est avérée 7, pas 11, comme cela devrait être.)

La deuxième forme de la fonction de comptage, c'est-à-dire La fonction Count (*) compte le nombre de lignes dans la table. Et si l'instruction Sélectionnez Demande Avec la fonction Count (*) contient la proposition de la condition avec la condition, la fonction renvoie le nombre de lignes satisfaisant à la condition spécifiée. Contrairement à la première version de la fonction de comptage, le deuxième formulaire n'ignore pas les valeurs NULL, car cette fonction exploite des chaînes, pas des colonnes. Dans l'exemple ci-dessous, l'utilisation de la fonction de comptage (*) est démontrée:

Utiliser échantillondb; Sélectionnez Job comme "Type de travail", Comptez (*) "Besoin de travailleurs" de Works_on Group par emploi;

Voici le nombre de postes dans tous les projets. Demande résultat:

Fonctions d'agrégats statistiques

Les fonctions suivantes comprennent un groupe de fonctions d'agrégats statistiques:

Var.

Calcule la dispersion statistique de toutes les valeurs présentées dans la colonne ou l'expression.

Varp.

Calcule la dispersion statistique de l'ensemble de toutes les valeurs présentées dans la colonne ou l'expression.

Stdev

Calcule l'écart type (qui est calculé comme une racine carrée de la dispersion correspondante) de toutes les valeurs de la colonne ou de l'expression.

Stdevp

Calcule la déviation RMS de la totalité de toutes les valeurs de colonne ou d'expression.

Fonctions d'agrégats d'utilisateurs

Le composant moteur de base de données prend également en charge la mise en œuvre des fonctions définies par l'utilisateur. Cette fonctionnalité permet aux utilisateurs de compléter les fonctionnalités d'agrégat du système qu'ils peuvent mettre en œuvre et s'installer eux-mêmes. Ces fonctions représentent une classe spéciale de fonctions définies par l'utilisateur et sont discutées en détail plus tard.

Offre avoir

Dans une phrase Ayant La condition qui s'applique au groupe de cordes est déterminée. Ainsi, cette proposition a la même signification pour les groupes de lignes que la proposition de proposition de contenu de la table correspondante. Offre de syntaxe ayant suivant:

Avoir une condition

Ici, le paramètre de condition représente la condition et contient des fonctions d'agrégats ou des constantes.

L'utilisation de la fonction d'agrégat de comptage (*) est affichée dans l'exemple ci-dessous:

Utiliser échantillondb; - Retourner "P3" Sélectionnez ProjectNumber de Works_on Group par ProjectNumber Compte (*)

Dans cet exemple, à travers la proposition du groupe par le système, toutes les lignes des valeurs de la colonne ProjectorNumber sont regroupées. Après cela, le nombre de lignes de chaque groupe est calculé et que des groupes contenant moins de quatre lignes (trois ou moins) sont sélectionnés.

L'offre peut également être utilisée sans fonctions globales, comme indiqué dans l'exemple ci-dessous:

Utiliser échantillondb; - renvoyer le "consultant" Sélectionnez un emploi auprès de Works_on Group par travail ayant un travail comme "à%";

Dans cet exemple, il existe un regroupement des lignes de table de Works_on par position et éliminant ces positions qui ne commencent pas par la lettre "K".

L'offre peut également être utilisée sans la suggestion de groupe par, bien que ce ne soit pas une pratique courante. Dans ce cas, toutes les lignes de la table reviennent dans un groupe.

Ordonner l'ordre par.

Phrase Commandé par. Détermine l'ordre de tri des lignes de l'ensemble résultant renvoyé par la requête. Cette offre a la syntaxe suivante:

L'ordre de tri est spécifié dans le paramètre Col_Name. Le paramètre Col_Number est un alternatif pointeur de l'ordre de tri, qui définit les colonnes afin de saisir la liste de sélection de l'instruction SELECT (1 - première colonne, une colonne de 2 secondes, etc.). Paramètre ASC détermine le tri dans l'ordre ascendant, et paramètre Desc - dans le bas. Par défaut, le paramètre ASC est appliqué.

Les noms des colonnes dans la commande par offre ne doivent pas être répertoriés dans la liste des colonnes exemple. Mais cela ne s'applique pas à sélectionner des demandes distinctes, car Dans ces demandes, les noms des colonnes spécifiés dans l'ordre par suggestion doivent également être répertoriés dans la liste des échantillons de colonnes. De plus, cette proposition ne peut pas contenir de noms de colonnes à partir des tables non spécifiées dans la phrase de la phrase.

Comme vous pouvez voir la commande par la syntaxe de la proposition, le tri de l'ensemble résultant peut être effectué sur plusieurs colonnes. Ce tri est montré dans l'exemple ci-dessous:

Dans cet exemple, un échantillon de départements et de noms de famille et de noms pour les employés des employés, dont le numéro de table est inférieur à 20 000, ainsi que le tri par nom et nom. Le résultat de cette demande:

Les colonnes de l'ordre par clause ne peuvent pas être spécifiées par leurs noms, mais dans la liste d'échantillonnage. En conséquence, la proposition dans l'exemple ci-dessus peut réécrire de la sorte:

Tel voie alternative Spécification des colonnes par leur position au lieu de noms, il est utilisé si le critère de commande contient une fonction d'agrégat. (Une autre façon consiste à utiliser les noms des colonnes, qui sont ensuite affichés dans l'ordre par.) Toutefois, dans l'ordre par phrase, il est recommandé de spécifier des colonnes par leurs noms et non par des chiffres pour simplifier la mise à jour de la demande si vous doivent ajouter ou supprimer des colonnes dans la liste des échantillons. Spécification des colonnes dans la commande par ordre par leurs numéros est affichée dans l'exemple ci-dessous:

Utiliser échantillondb; Sélectionnez ProjectNumber, Count (*) «Nombre d'employés» du groupe de Works_on par ordre de projecteur Order par 2 Desc;

Ici, le numéro de projet est sélectionné pour chaque projet et le nombre d'employés y participant, rationalisant le résultat dans une ordonnance descendante par le nombre d'employés.

Transact-SQL Langue Lors du tri dans une commande croissante place des valeurs null au début de la liste et à la fin de la liste - à une diminution.

Utilisation de la commande par proposition de fractionner les résultats sur les pages

Affichage des résultats de la requête sur la page en cours, vous pouvez ou implémenter dans une application utilisateur ou pour spécifier ceci au serveur de base de données. Dans le premier cas, toutes les lignes de base de données sont envoyées par l'application dont la tâche consiste à sélectionner les lignes requises et à les afficher. Dans le second cas, seules les lignes requises pour la page en cours sont sélectionnées dans le serveur et s'affiche. Comme vous pouvez le supposer, la création de pages du côté serveur fournit généralement meilleure performancecar Seules les lignes nécessaires à l'affichage sont envoyées au client.

Pour soutenir la création de pages du côté serveur dans serveur SQL 2012 entre deux nouvelles instructions de propositions Sélectionnez: Décalage et récupération. L'utilisation de ces deux propositions est démontrée dans l'exemple ci-dessous. Ici de la base de données Adventureworks2012 (que vous pouvez trouver dans la source) est extraite par l'identifiant de l'entreprise, le nom du poste et l'anniversaire de toutes les femmes employées avec le tri du résultat par le titre de position dans l'ordre croissant. L'ensemble de lignes résultant est divisé en pages à 10 lignes et la troisième page est affichée:

Dans une phrase Décalage. Le nombre de chaînes du résultat qui doit être ignoré dans le résultat affiché est indiqué. Cette quantité est calculée après avoir tri des lignes par la proposition par la proposition. Dans une phrase Chercher ensuite ensuite. Le nombre de satisfaisants de la condition et des chaînes triées à renvoyer est indiquée. Le paramètre de cette proposition peut être une constante, une expression ou un résultat d'une autre requête. Chercher la prochaine proposition similaire à l'offre Chercher d'abord.

L'objectif principal lors de la création de pages du côté serveur est la possibilité de mettre en œuvre des formulaires de page communs à l'aide de variables. Cette tâche peut être effectuée par le package SQL Server.

Sélectionnez la propriété d'instruction et d'identité

Identité de propriété. Vous permet de déterminer les valeurs d'une colonne spécifique de la table en tant que compteur croissant automatiquement. Cette propriété peut avoir une colonne de type de données numérique, telle que Tinyint, Smallint, Int et Bigint. Pour une telle colonne de table, le composant moteur de base de données crée automatiquement des valeurs de série à partir de la valeur de départ spécifiée. Ainsi, la propriété d'identité peut être utilisée pour créer des valeurs numériques sans ambiguilles pour la colonne sélectionnée.

Le tableau ne peut contenir qu'une seule colonne avec la propriété Identity. Le propriétaire de la table a la possibilité de spécifier la valeur initiale et l'étape d'augmentation, comme indiqué dans l'exemple ci-dessous:

Utiliser échantillondb; Créer un produit de table (ID int Intentialité (10000, 1) Non NULL, NOM NVARCHAR (30) NON NULL, ARGENT DE PRIX) Insertion dans le produit (nom, prix) Valeurs ("Produit1", 10), ("Produit2", 15) , ("Produit3", 8), ("produit4", 15), ("produit5", 40); - renvoyer 10004 selectycol du produit où nom \u003d "production5"; - analogue de la précédente Select $ identité du produit où nom \u003d "produit5";

Dans cet exemple, la table de produit est créée pour la première fois contenant la colonne ID avec la propriété Identity. Les valeurs dans la colonne ID sont créées automatiquement par le système, à partir de 10 000 et augmentant avec une seule étape pour chaque valeur ultérieure: 10 000, 10 001, 10 002, etc.

Certaines fonctions et variables système sont associées à la propriété d'identité. Par exemple, dans le code exemple utilisé variable système $ Identité. Comme vous pouvez le constater à partir des résultats de ce code, cette variable fait automatiquement référence à la propriété d'identité. Au lieu de cela, vous pouvez également utiliser une fonction système ItalyCol.

La valeur initiale et l'étape d'incrémentation de la colonne avec la propriété d'identité peuvent être trouvées à l'aide de fonctions. Identité et Identif_incr respectivement. Ces fonctionnalités s'appliquent comme suit:

Utiliser échantillondb; Sélectionnez Identif_Seed ("Produit"), IDEN_INCR ("Produit")

Comme déjà mentionné, les valeurs de l'identité sont automatiquement installées par le système. Mais l'utilisateur peut spécifier ses valeurs pour certaines lignes, attribuant le paramètre Identity_insert. La valeur sur avant d'insertion de valeur explicite:

Définir l'identité Insérer le nom de la table sur

Depuis l'utilisation du paramètre Identity_Insert pour une colonne avec la propriété Identity, vous pouvez définir n'importe quelle valeur, y compris la répétition, la propriété d'identité ne garantit généralement pas l'unicité forcée des valeurs de colonne. Par conséquent, une clé unique ou principale doit être appliquée pour appliquer le caractère unique des valeurs de colonne.

Lors de l'insertion de valeurs dans le tableau après l'attribution du paramètre Identity_Insert, la valeur du système crée la valeur suivante de la colonne d'identité, augmentant ainsi la plus grande valeur actuelle de cette colonne.

Créer un opérateur de séquence

L'application de la propriété d'identité présente plusieurs inconvénients importants, dont les plus importants sont les suivants:

l'application de la propriété est limitée à la table spécifiée;

la nouvelle valeur de colonne ne peut pas être obtenue d'une manière différente, sauf lors de l'application;

la propriété Identity ne peut être spécifiée que lors de la création d'une colonne.

Pour ces raisons, SQL Server 2012 a saisi des séquences possédant la même sémantique que la propriété d'identité, mais ne dispose pas de défauts précédemment répertoriés. Dans ce contexte, la séquence est une fonctionnalité de base de données qui vous permet de spécifier les valeurs de compteur pour différents objets de base de données, telles que des colonnes et des variables.

Les séquences sont créées à l'aide de l'instruction Créer une séquence. L'instruction Create Séquence est définie dans SQL Standard et est prise en charge par d'autres systèmes de base de données relationnels, tels que IBM DB2 et Oracle.

L'exemple ci-dessous montre la création d'une séquence dans SQL Server:

Utiliser échantillondb; Créer une séquence Dbo.Suce1 comme INT Commencez avec 1 incrément de 5 Minvalue 1 Maxvalue 256 cycle;

Dans l'exemple ci-dessus, la valeur de la séquence SEQUENCE1 est créée automatiquement par le système commençant par la valeur 1 à l'étape 5 pour chaque valeur ultérieure. Dedans offrant début. La valeur initiale est indiquée et dans incrément d'offre. - Marcher. (L'étape peut être à la fois positive et négative.)

Dans les deux suivantes, facultatives, phrases Minvalue. et Valeur max. Spécifiez la valeur minimale et maximale de l'objet de séquence. (Notez que la valeur MinValue doit être inférieure ou égale à la valeur initiale et la valeur maxvalue ne peut pas être supérieure à la limite supérieure du type de données spécifié pour la séquence.) Dans la proposition Cycle Il est indiqué que la séquence est répétée du début pour dépasser le maximum (ou le minimum d'une séquence avec une étape négative). Par défaut, cette proposition n'est pas du cycle, ce qui signifie que le dépassement de la valeur de séquence maximale ou minimale provoque une exception.

La principale caractéristique des séquences est leur indépendance sur les tables, c'est-à-dire Ils peuvent être utilisés avec des objets de base de données, tels que des colonnes de table ou des variables. (Cette propriété a un effet positif sur le stockage et, en conséquence, par performance. Séquence Il n'est pas tenu de stocker; Seule sa dernière signification est sauvée.)

Les nouvelles valeurs de séquence sont créées en utilisant formulaire de valeur suivanteL'utilisation est indiquée dans l'exemple ci-dessous:

Utiliser échantillondb; - Retour 1 Sélectionnez la valeur suivante pour Dbo.Suce1; - Retour 6 (étape suivante) Sélectionnez la valeur suivante pour Dbo.Suce1;

En utilisant la valeur suivante pour l'expression, vous pouvez attribuer le résultat de la séquence d'une variable ou d'une cellule de la colonne. L'exemple ci-dessous montre l'utilisation de cette expression pour attribuer des résultats à la colonne:

Utiliser échantillondb; Créer un produit de table (ID INT NON NULL, NOM NVARCHAR (30) NON NULL, ARGENT DE PRIX) Insertion dans les valeurs de produit (valeur suivante pour Dbo.Sonfrence1, Production1, 10); Insérer dans les valeurs de produit (valeur suivante pour Dbo.Sonfrence1, "item2", 15); - ...

Dans l'exemple ci-dessus, la table du produit est créée pour la première fois, composée de quatre colonnes. En outre, deux instructions d'insertion insérer deux lignes dans cette table. Les deux premières cellules de la première colonne auront 11 et 16.

Exemple ci-dessous montre l'utilisation de la vue Répertoire syswards. Pour afficher la valeur de séquence actuelle sans l'utiliser:

Habituellement, la valeur suivante de l'expression est appliquée dans des instructions d'insertion afin que le système insère les valeurs créées. Cette expression peut également être utilisée dans le cadre d'une requête multiligne à l'aide de la plus grande offre.

Pour modifier les propriétés de la séquence existante appliquée modifier l'instruction de séquence. Un des plus applications importantes Ce manuel est associé au redémarrage avec le paramètre, qui réinstalle la séquence spécifiée. Dans l'exemple ci-dessous, l'instruction Séquence Alter est montrée pour réinstaller presque toutes les propriétés de séquence Séquence1:

Utiliser échantillondb; Alter Séquence Dbo.Squence1 Redémarrance avec 100 incréments de 50 Minvalue 50 Maxvalue 200 Pas de cycle;

La séquence est supprimée à l'aide de l'instruction Séquence de goutte.

Opérateurs travaillant avec des ensembles

En plus des opérateurs examinés plus tôt, la langue transactive-SQL prend en charge trois autres opérateurs avec des ensembles: Union, intersect et sauf.

Syndicat de l'opérateur

Syndicat de l'opérateur Combine les résultats de deux demandes ou plus d'un ensemble de résultats, qui inclut toutes les lignes appartenant à toutes les demandes de l'Union. En conséquence, le résultat de la combinaison des deux tables est une nouvelle table contenant toutes les lignes incluses dans l'une des tables source ou de ces deux tables.

La forme commune de l'opérateur syndical ressemble à ceci:

sELECT_1 Union SELECT_2 (SELECT_3]) ...

SELECT_1, SELECT_2 Les paramètres, ... sont des instructions qui créent une association. Si vous utilisez tous les paramètres, toutes les lignes sont affichées, y compris des doublons. Dans la déclaration Union, tout paramètre a la même valeur que dans la liste Sélectionner la sélection, mais avec une différence: pour la liste Sélectionner une sélection, ce paramètre est utilisé par défaut et il est clairement spécifié pour l'opérateur de l'Union.

Dans sa forme originale, la base de données SAMPLEDB ne convient pas à la démonstration de l'application de l'opérateur de l'Union. Par conséquent, cette section crée une nouvelle table des employés, identique à la table existante de l'employé, mais possède une colonne de ville supplémentaire. Cette colonne indique le lieu de résidence des employés.

La création de la table Employedenh nous fournit un cas pratique pour démontrer l'utilisation de l'offre. Dans Dans la déclaration SELECT. Sélectionner dans l'instruction effectue deux opérations. Tout d'abord crée une nouvelle table avec des colonnes répertoriées dans la liste Sélectionner une sélection. Ensuite, les rangées de table de départ sont insérées dans la nouvelle table. Le nom de la nouvelle table est spécifié dans la phrase dans la phrase et le nom de la table source est indiqué dans la clause depuis.

L'exemple ci-dessous montre la création de la table des employés de l'employé:

Utiliser échantillondb; Sélectionnez * dans Employéenh de l'employé; Alter table employéenh ajoutez la ville nchar (40) null;

Dans cet exemple, la sélection de l'instruction SELECT crée la table EmployedENH, insère toutes les lignes de la table source de l'employé, après quoi l'instruction ALTER TABLE ajoute une colonne de ville à une nouvelle table. Mais la colonne de la ville ajoutée ne contient aucune valeur. Les valeurs de cette colonne peuvent être collées via l'environnement de gestion de gestion ou à l'aide du code suivant:

Utiliser échantillondb; Update employéenh set city \u003d "kazan" où id \u003d 2581; Mettre à jour Employéenh Set City \u003d "Moscou" où ID \u003d 9031; Update employéenh set city \u003d "Ekaterinburg" où ID \u003d 10102; Mettre à jour Employéenh Set City \u003d "Saint-Pétersbourg" où ID \u003d 18316; Mettre à jour Employéenh Set City \u003d "Krasnodar" où ID \u003d 25348; Update employéenh set city \u003d "kazan" où id \u003d 28559; Mettre à jour Employéenh Set City \u003d "Perm" où ID \u003d 29346;

Nous sommes maintenant prêts à démontrer l'utilisation des instructions de l'Union. L'exemple ci-dessous montre une requête pour créer une connexion d'employéenh et de département en utilisant cette instruction:

Utiliser échantillondb; Sélectionnez la ville comme "Ville" de Employéenh Union Select Emplacement du département;

Le résultat de cette demande:

Vous pouvez combiner utiliser uniquement les tableaux compatibles de l'instruction Union. Sous les tableaux compatibles, il est entendu que la liste des deux échantillons de colonne doit contenir le même nombre de colonnes, et les colonnes correspondantes doivent avoir des types de données compatibles. (En ce qui concerne la compatibilité, les types de données Int et Smallint ne sont pas compatibles.)

Le résultat de la combinaison ne peut être arrangé que l'utilisation de la commande par fourniture à dernières instructions Sélectionnez, comme indiqué dans l'exemple ci-dessous. Le groupe et les offres peuvent être appliqués avec des instructions de sélection séparées, mais pas dans la combinaison elle-même.

La demande de cet exemple constitue un échantillon d'employés qui travaillent dans le département D1 ou ont commencé à travailler sur le projet avant le 1er janvier 2008

L'opérateur du syndicat prend en charge tous les paramètres. Lorsque vous utilisez ce paramètre, les doublons ne sont pas supprimés de l'ensemble résultant. Au lieu de l'opérateur de l'Union, vous pouvez appliquer le ou la déclaration si toutes les instructions de sélectionnées connectées par un ou plusieurs opérateurs syndicaux font référence à la même table. Dans ce cas, le kit d'instruction SELECT est remplacé par une instruction SELECT avec un ensemble ou des opérateurs.

Intersect et sauf opérateurs

Deux autres opérateurs pour travailler avec des ensembles, Couper. et Sauf, Déterminez respectivement l'intersection et la différence. Sous l'intersection dans ce contexte, il existe un ensemble de chaînes qui appartiennent aux deux tables. Et la différence entre les deux tables est définie comme toutes les valeurs appartenant à la première table et ne sont pas présentes dans la seconde. Dans l'exemple ci-dessous, l'opérateur d'intersect est affiché:

La langue transactive-SQL ne prend pas en charge l'utilisation du paramètre Tous avec la déclaration d'intersect ou l'opérateur à l'exception. L'utilisation de l'opérateur sauf est affichée dans l'exemple ci-dessous:

Il convient de rappeler que ces trois opérateurs pour les ensembles ont une priorité d'exécution différente: la déclaration d'intersect a la priorité la plus élevée, suivie de la déclaration sauf, et l'opérateur de l'Union a la priorité la plus basse. Inattention à la priorité de l'exécution Lors de l'utilisation de plusieurs opérateurs différents pour travailler avec des ensembles peut entraîner des résultats inattendus.

Cas d'expressions.

Dans la portée de la demande de base de données, il est parfois nécessaire de modifier la présentation des données. Par exemple, les personnes peuvent être subdivisées en les codant sur leur appartenance sociale, en utilisant des valeurs de 1, 2 et 3, dénotant des hommes, des femmes et des enfants, respectivement. Une telle programmation peut réduire le temps nécessaire pour mettre en œuvre le programme. Expression de cas Le langage Transact-SQL vous permet d'implémenter facilement un tel type de codage.

Contrairement à la plupart des langages de programmation, l'affaire n'est pas une instruction, mais une expression. Par conséquent, l'expression de cas peut être utilisée presque partout où la langue transact-sql vous permet d'appliquer des expressions. L'expression de cas a deux formes:

expression de cas simple;

coffre d'expression de recherche.

La syntaxe de l'expression de cas simple est la suivante:

Instructions avec une expression simple de cas d'abord recherchant dans la liste de toutes les expressions proposition quand La première expression coïncidant avec l'expression expression_1, après quoi elle effectue le l'offre d'alors.. En l'absence d'une expression coïncidante dans la liste, elle est effectuée offre d'autre..

La syntaxe d'expression de la recherche de cas est la suivante:

Dans ce cas, une recherche est effectuée les premières conditions répondant aux exigences des exigences, après quoi l'apport correspondant est exécuté. Si aucune des conditions ne remplit les conditions requises, l'offre d'entreprise est effectuée. L'application de l'expression de la recherche de cas est affichée dans l'exemple ci-dessous:

Utiliser échantillondb; Sélectionnez ProjectName, cas lorsque Budget\u003e 0 et budget 100000 et budget 150000 et budget

Le résultat de cette demande:

Dans cet exemple, les budgets de tous les projets sont pesés, après quoi ils sont affichés pour leurs coefficients de poids avec les noms correspondants de projets.

Dans l'exemple ci-dessous, une autre méthode s'applique à une autre méthode d'application de cas, lorsque la proposition de la proposition contient des demandes investies une partie de l'expression:

Utiliser échantillondb; Sélectionnez ProjectName, cas lorsque P1.Budget (Sélectionnez AVG (P2.Budget) à partir du projet P2), puis «Catégorie de budget au-dessus de la moyenne» du projet P1;

Le résultat de cette demande est la suivante: