SQL Où : applications et exemples. SQL Where Applications et exemples Exemple de requête pour sélectionner des valeurs numériques

Rechercher des objets dans toutes les bases de données du serveur

Avez-vous déjà eu besoin de trouver une table pour rechercher des données ou une structure, mais vous ne vous souvenez pas exactement dans quelle base de données elle se trouve ? Cela m'arrive de temps en temps. Un exemple consiste à trouver des tableaux d'un projet vieux de cinq ans pour voir comment le problème actuel a été résolu à l'époque.

Heureusement, il existe une procédure sp_MSforeachdb, ce qui vous permet d'écrire un tel script de manière assez compacte :

DECLARE @name AS SYSNAME,
@strSQL AS VARCHAR(MAX)
SET @nom = "Hiérarchie"

SET @strSQL = "
IF EXISTS(SELECT * FROM ?..sysobjects WHERE nom LIKE ""%" [courriel protégé]+"%" ")
COMMENCER
SÉLECTIONNER " "?" " db, nom FROM ?..sysobjects WHERE nom LIKE ""%" [courriel protégé]+"%" "
FIN
"
CRÉER TABLE #résultat (
dbSYSNAME,
nom SYSNAME,
CLE PRIMAIRE (db,nom))

INSÉRER #résultat EXEC sp_MSforeachdb @strSQL
SELECT * FROM #résultat

SUPPRIMER LE TABLEAU #résultat

Tout est assez simple ici :

1. Nous définissons la partie du nom par laquelle nous allons rechercher (dans ce cas - Hiérarchie).
2. Nous collectons du SQL dynamique qui recherche des sysobjects (pour certaines anciennes versions de MS SQL, vous pouvez utiliser VARCHAR(2000) au lieu de VARCHAR(MAX), par exemple).
   Dans ce cas, le symbole "?" sera remplacé par le nom de la base de données.
3. A l'aide de la construction INSERT ... EXEC, on stocke tous les résultats dans une table temporaire afin d'afficher, comme résultat, un seul résultat.

Rechercher une sous-chaîne dans les textes des procédures stockées

Comme vous le savez, les textes des procédures stockées, des déclencheurs et des vues se trouvent dans le tableau commentaires système.

Pour que notre requête précédente nous permette de trouver une mention dans syscomments d'une sous-chaîne, elle peut être réécrite comme suit :

SET @strSQL="
SELECT DISTINCTE " "?" "as db, o.name FROM [?].dbo.syscomments s
JOIN [?].dbo.sysobjects o ON o.id = s.id WHERE s. COMME""%" [courriel protégé]+"%" ""

Faites attention à DISTINCT - cela peut être utile, car il y a parfois plusieurs entrées dans syscomments pour un objet.

Rechercher une sous-chaîne dans tous les champs de chaîne de toutes les tables

Un tel script aide à dormir paisiblement après avoir "nettoyé" la base de données avant de l'envoyer au client. Naturellement, son application ne se limite pas à ce domaine.

Pour ce cas, un assistant sous la forme d'une procédure de MS n'a pas été trouvé, j'ai donc dû utiliser des curseurs :

déclarer @pattern comme nvarchar(MAX )
définir @motif = N"%Test%"
ne comptez pas sur
déclarer @sql comme nvarchar(MAX )
déclarer @table comme sysname
déclarer les tables curseur local statique read_only pour sélectionner le nom de sys.tables t où t.is_ms_shipped = 0
tables ouvertes

créer une table #results(name sysname not null , value nvarchar(MAX ) not null )
tandis que @@FETCH_STATUS = 0
commencer
définir @sql=""
sélectionnez @sql = @sql + "
insérer dans #results select""" + @table + "." + nom + "" " comme nom, [" + nom + "] de [" + @table + "] où [" + nom + "] comme " "" + @pattern + "" ""
de sys.columns c où c.object_id = OBJECT_ID(@table ) et c.system_type_id dans
(sélectionnez system_type_id dans sys.types où collation_name n'est pas null )
exec (@sql)
récupérer ensuite des tables dans @table
fin
sélectionnez * parmi les #résultats
fermer les tables
déposer le tableau #résultats

Hormis les curseurs, les principes sont les mêmes. Il y a quelques nuances :

• Nous recherchons des tables sans le drapeau is_ms_shipped afin de n'afficher que les tables créées pendant le développement.
• Pour sélectionner des colonnes de chaînes, au lieu d'une liste de types (ce qui est fastidieux à maintenir), le filtre "collation_name n'est pas nul" est utilisé.

Résumé

Le lecteur attentif a dû deviner au dessin que le dernier exemple n'a pas été écrit en même temps que les deux premiers. Pour être plus précis, j'ai écrit la dernière demande il y a un mois. Cela signifie que sur la base de cette approche, vous pouvez écrire vous-même des scripts utiles qui résolvent vos problèmes spécifiques.

Si les lecteurs aiment ce format, je publierai parfois des scripts utiles de ma collection personnelle.

Si vous avez des commentaires, des suggestions ou de nouveaux sujets - écrivez dans les commentaires,

Il est difficile d'expliquer la syntaxe de la clause SQL Server WHERE, alors regardons quelques exemples.

Nous allons commencer par voir comment utiliser la clause WHERE avec une seule condition.

SELECT * FROM employés WHERE first_name = "Jane" ;

Dans cet exemple de clause SQL Server WHERE, nous avons utilisé la clause WHERE pour filtrer nos résultats à partir de la employés tableau. L'instruction SELECT ci-dessus renverrait toutes les lignes de la employés table où le prénom est "Jane". Comme le * est utilisé dans le SELECT, tous les champs du employés table apparaîtra dans le jeu de résultats.

Exemple - Utilisation des conditions ET

Voyons comment utiliser la clause WHERE avec la condition AND.

SELECT * FROM employés WHERE nom_de_famille = "Anderson" AND id_employé >= 3000 ;

Cet exemple de clause SQL Server WHERE utilise la clause WHERE pour définir plusieurs conditions. Dans ce cas, cette instruction SELECT utilise la condition AND pour renvoyer tous employés qui ont un nom de famille de "Anderson" et le id_employé est supérieur ou égal à 3000.

Exemple - Utilisation de la condition OU

Voyons comment utiliser la clause WHERE avec la condition OR.

SELECT id_employé, nom_famille, prénom FROM employés WHERE nom_famille = "Johnson" OR prénom = "Danielle" ;

Cet exemple de clause SQL Server WHERE utilise la clause WHERE pour définir plusieurs conditions, mais au lieu d'utiliser la condition AND , il utilise la condition OR . Dans ce cas, cette instruction SELECT renverrait tous id_employé, nom de famille, et prénom valeurs de la employés table où le nom de famille est "Johnson" ou le prénom est "Danielle".

Exemple - Combinaison des conditions ET et OU

Voyons comment utiliser la clause WHERE lorsque nous combinons les conditions AND & OR dans une seule instruction SQL.

SELECT * FROM employés WHERE (state = "California" AND last_name = "Smith") OR (employee_id = 82);

Cet exemple de clause SQL Server WHERE utilise la clause WHERE pour définir plusieurs conditions, mais il combine la condition AND et la condition OR . Cet exemple renverrait tous employés qui résident dans le État de "Californie" et dont nom de famille est "Smith" ainsi que tous les employés dont id_employé est égal à 82.

Les parenthèses déterminent l'ordre dans lequel les et Les conditions OU sont évaluées. Tout comme vous l'avez appris dans l'ordre des opérations en cours de mathématiques !

Exemple - Joindre des tables

Voyons comment utiliser la clause WHERE lorsque nous joignons plusieurs tables ensemble.

SELECT employés.id_employé, contacts.nom_de_famille FROM employés INNER JOIN contacts ON employés.id_employé = contacts.id_contact WHERE employés.prénom = "Sarah" ;

Cet exemple de clause SQL Server WHERE utilise la clause WHERE pour joindre plusieurs tables dans une seule instruction SELECT. Cette instruction SELECT renvoie toutes les lignes où le prénom dans le employés table est "Sarah". Et le employé sable Contacts les tables sont jointes sur le id_employé du employés tableau et le contact_id du Contacts tableau.

Dans la plupart des cas, vous n'avez pas besoin de récupérer tous les enregistrements, mais uniquement ceux qui répondent à certains critères. Par conséquent, afin de filtrer la sélection dans SQL il y a un opérateur spécial OÙ.

1. Filtrage simple avec l'opérateur WHERE.

Par exemple, sélectionnons des enregistrements de notre table qui se rapportent uniquement à un produit particulier. Pour ce faire, nous allons spécifier un paramètre de sélection supplémentaire qui filtrera la valeur par la colonne produit.

Exemple de requête pour sélectionner des valeurs de texte :

SÉLECTIONNER * FROM Sommeproduit OÙ Produit = "Vélos"

Comme vous pouvez le voir, la condition de sélection est entourée de guillemets simples, ce qui est obligatoire lors du filtrage des valeurs de texte. Les guillemets ne sont pas nécessaires lors du filtrage des valeurs numériques.

Exemple de requête pour sélectionner des valeurs numériques :

SÉLECTIONNER > 40000 COMMANDÉ PAR Montant

Dans cet exemple, nous avons sélectionné des enregistrements dans lesquels le chiffre d'affaires était supérieur à 40 mille $ et, en plus, tous les enregistrements sont triés par ordre croissant par champ Montant.

Le tableau ci-dessous répertorie les instructions conditionnelles prises en charge SQL:

2. Filtrage par plage de valeurs ( ENTRE).

Pour sélectionner des données qui se trouvent dans une certaine plage, utilisez l'opérateur ENTRE. La requête suivante sélectionnera toutes les valeurs entre 1000 $ V 2000 $ inclusif, dans le domaine Montant.

SÉLECTIONNER * FROM Sommeproduit OÙ Montant ENTRE 1000 ET 2000

L'ordre de tri dépendra de l'ordre des champs dans la requête. Autrement dit, dans notre cas, les données seront d'abord triées par colonne Montant, puis par ville.

3. Sélection des enregistrements vides ( EST NULL).

DANS SQL il existe un opérateur spécial pour sélectionner les enregistrements vides (appelé NUL). Une entrée vide est une cellule d'un tableau qui ne contient aucun caractère. Si la cellule est saisie 0 ou espace, alors le champ est considéré comme rempli.

SÉLECTIONNER * FROM Sommeproduit OÙ Montant EST NULL

Dans l'exemple ci-dessus, nous avons délibérément supprimé deux valeurs dans le champ Montant pour démontrer le fonctionnement de l'opérateur NUL.

4. Filtrage avancé ( ET, OU).

Langue SQL n'est pas limité au filtrage par une condition, pour vos propres besoins, vous pouvez utiliser des structures assez complexes pour sélectionner des données simultanément selon de nombreux critères. Pour cela dans SQL il y a des opérateurs supplémentaires qui étendent les capacités de l'opérateur OÙ. Ces opérateurs sont : ET, OU, DANS, NON. Voici quelques exemples du fonctionnement de ces opérateurs.

SÉLECTIONNER * FROM Sommeproduit OÙ Montant > 40000 ET ville = "Toronto"

SÉLECTIONNER * FROM Somme produit OÙ Mois= "Avril"OU mois= "mars"

Combinons les opérateurs ET Et OU. Pour cela, faisons une sélection de vélos ( vélos ) et patins ( patins ), qui ont été vendus en mars (Mars ).

SÉLECTIONNER * FROM Sommeproduit OÙ Produit= "Vélos"OU produit= "patins" ET mois= "mars"

On voit que notre échantillon comportait de nombreuses valeurs (sauf pour mars ( Mars), également janvier ( Janvier), Février ( Février) et avril ( Avril)). Quelle est la raison? Et en cela SQL a des priorités d'exécution des commandes. C'est-à-dire que l'opérateur ET a une priorité plus élevée que l'opérateur OU, donc les disques avec patins qui ont été vendus en mars ont été sélectionnés en premier, puis tous les disques liés aux vélos.

Ainsi, afin d'obtenir la bonne sélection, nous devons modifier les priorités d'exécution des commandes. Pour cela nous utilisons supports comme en mathématiques. Ensuite, les opérateurs entre parenthèses seront traités en premier, puis tous les autres.

SÉLECTIONNER * FROM Sommeproduit OÙ (Produit= "Vélos"OU produit= "patins") ET mois= "mars"

5. Filtrage avancé ( Opérateur IN).

SÉLECTIONNER * FROM Produit somme WHERE IDDANS (4, 12, 58, 67)

Opérateur DANS remplit la même fonction que OU, présente cependant plusieurs avantages :

• Lorsque vous travaillez avec de longues listes, la phrase avec DANS plus facile à lire;
• Moins d'opérateurs sont utilisés, ce qui accélère le traitement des requêtes ;
• L'avantage le plus important DANS en ce que dans sa construction il est possible d'utiliser une construction supplémentaire SÉLECTIONNER, Quoi
ouvre de grandes opportunités pour créer des sous-requêtes complexes.

6. Filtrage avancé ( NON opérateur).

SÉLECTIONNER * FROM Sumproduct WHERE NOT VilleDANS ("Toronto", "Montréal")

Mot-clé PAS vous permet de supprimer les valeurs inutiles de la sélection. De plus, sa particularité est qu'il est placé avant le nom de la colonne impliquée dans le filtrage, et non après.

Dans cet article, je vais vous parler de l'instruction conditionnelle CASE, sans laquelle la comptabilisation des données dans un certain nombre de tâches se transformerait en un tas de morceaux de code, ainsi que de la façon de l'utiliser dans les requêtes SQL.

Quelle que soit la qualité de la conception de votre base de données, il y aura toujours des problèmes où les instructions conditionnelles sont indispensables. Par exemple, au lieu d'énormes nombres, obtenez une description verbale, ou, selon la présence (absence) de données, ajoutez des caractères supplémentaires à la chaîne, ou, comme exemple plus complexe, selon diverses nuances, attribuez l'enregistrement à un ou un autre groupe. Beaucoup de situation.

En principe, cet opérateur possède au moins deux formulaires dans des bases de données différentes. La première option ressemble au commutateur habituel de n'importe quel langage de programmation. La seconde dépend des expressions booléennes.

Cependant, dans cet article, je considérerai la deuxième option, car elle n'a pas de problèmes avec des situations comme CASE WHEN NULL (null n'est pas une valeur spécifique dans la base de données, elle ne peut donc pas être utilisée dans une instruction de type commutateur). De plus, dans la vie de tous les jours, les tâches sont le plus souvent rencontrées spécifiquement pour la deuxième option - le calcul via des expressions logiques. Par conséquent, il est préférable d'apprendre immédiatement et de continuer à l'utiliser.

Habituellement, il est décrit comme ceci (la syntaxe peut varier selon la base de données) :

CAS QUAND expression_bool1 ALORS valeur1 ..... QUAND expression_boolN ALORS valeurN SINON valeurSinon FIN

bool_expressionX est une condition booléenne

valeurX est la valeur qui sera substituée si la condition booléenne correspondante est remplie

valueElse est la valeur qui sera substituée si aucune condition n'a été remplie auparavant.

Après une si petite référence, passons à la pratique.

Note: Soit dit en passant, il convient de savoir que cette instruction peut généralement être utilisée non seulement dans select, mais également à tout endroit où des champs peuvent être utilisés. Par exemple, lorsque joindre des tables ou même filtrage (avoir) lors du regroupement (group by).

Instruction conditionnelle CASE...WHEN...THEN

Pour mieux comprendre l'instruction conditionnelle CASE...WHEN...THEN, imaginez un petit problème. Supposons que vous disposiez d'un tableau contenant des données sur les clients et leur nombre total d'achats. Et la tâche consiste à former dynamiquement une remise. Il serait bien sûr possible de régler la remise manuellement. Mais, vous avez un seuil un, et les seuils sont câblés (quelque chose comme - le montant est supérieur à 1000 obtenez une remise de 2%, et plus de 5000 - obtenez 5%) et vous souhaitez automatiser ce processus afin que vous fassiez ne pas avoir à rechercher les erreurs à chaque fois que vous plongez dans la base de données (le client a accumulé le montant requis - la remise est apparue automatiquement).

Prenons un client de table conditionnel avec trois clients. Par exemple, ils suffiront.

Maintenant, définissons plusieurs conditions pour accorder automatiquement des remises en fonction de la tâche. Dans le même temps, nous pensons que la remise maximale est accordée au client dans tous les cas.

1. Montant de 1000 - 2% de réduction

2. Montant de 5000 - 5% de réduction

3. Montant de 10000 - 8% de réduction

4. Quantité de commandes de 10 à 7% de réduction

5. Quantité de commandes de 20 à 8% de réduction

Comme vous pouvez le constater, la remise dépend de deux facteurs : le montant et la quantité. Maintenant, essayons de créer des conditions à partir d'eux en fonction de la remise, c'est-à-dire que les règles sont vice versa afin qu'elles puissent être utilisées dans une requête sql. Nous obtenons ce qui suit :

1. 2% - Montant de 1000 à 4999 et le nombre de commandes est inférieur à 10.

2. 5% - Montant de 5000 à 9999 et le nombre de commandes est inférieur à 10.

3. 7% - Nombre de commandes de 10 à 19 et le montant est inférieur à 10000

4. 8% - Quantité à partir de 20 ou montant à partir de 10000

Maintenant, il ne reste plus qu'à l'écrire. Obtenez la requête sql suivante

Affichage du nom et d'autres données, sélectionnez le nom, le nombre de commandes, la somme_totale, -- Et maintenant l'affichage de la remise CAS -- Première règle 2 % QUAND c.total_sum >= 1000 et c.total_sum<= 4999 and c.order_count < 10 THEN 2 -- Второе правило 5% WHEN c.total_sum >= 5000 et c.total_sum<= 9999 and c.order_count < 10 THEN 5 -- Третье правило 7% WHEN c.total_sum < 10000 and c.order_count >= 10 et c.order_count<= 19 THEN 5 -- Четвертое правило 8% WHEN c.total_sum >= 10000 ou c.order_count >= 20 THEN 5 -- Aucune règle ne correspond, donc la remise est de 0. ELSE 0 END comme remise du client c

À la suite de l'exécution, nous obtenons le tableau suivant :

Comme vous pouvez le voir, deux clients ont reçu une remise de 8 % et un client a reçu une remise de 2 %. En même temps, à chaque commande, le pourcentage sera automatiquement calculé et vous n'aurez rien à ajuster. Par exemple, si le montant de Petya passe à 5000, sa remise passera automatiquement à 5% (au moins, car il reste encore un certain nombre de commandes).