SQL Gdzie: zastosowania i przykłady. SQL Gdzie: Zastosowania i przykłady Przykładowe zapytanie dotyczące wybierania wartości numerycznych

Wyszukaj obiekty we wszystkich bazach danych serwera

Czy zdarzyło Ci się kiedyś, że musiałeś znaleźć tabelę, aby wyświetlić dane lub strukturę, ale nie pamiętasz dokładnie, w której bazie danych się ona znajduje? Zdarza mi się to okresowo. Przykładem jest znalezienie tabel z projektu sprzed pięciu lat, aby zobaczyć, jak rozwiązano wówczas bieżący problem.

Na szczęście jest procedura sp_MSforeachdb, co pozwala napisać taki skrypt dość zwięźle:

Zdeklaruj @name JAKO NAZWĘ SYS,
@strSQL JAKO VARCHAR (MAX)
SET @name = "Hierarchia"

USTAW @strSQL = "
JEŚLI ISTNIEJE(WYBIERZ * Z ?..sysobjects GDZIE nazwa LIKE ""%" +@nazwa+"%" ")
ZACZYNAĆ
WYBIERAĆ " "?" " db, nazwa OD ?..sysobjects GDZIE nazwa LIKE ""%" +@nazwa+"%" "
KONIEC
"
UTWÓRZ TABELĘ #wynik (
db NAZWA SYSTEMU,
nazwa SYSNAME,
KLUCZ PODSTAWOWY (db, nazwa))

WSTAW #result EXEC sp_MSforeachdb @strSQL
WYBIERZ * Z #wyniku

USUŃ TABEŁĘ #wynik

Tutaj wszystko jest dość proste:

1. Ustalamy część nazwy, po której będziemy szukać (w tym przypadku - Hierarchia).
2. Zbieramy dynamiczny SQL, który wyszukuje według obiektów sysobjects (w przypadku niektórych starszych wersji MS SQL można na przykład użyć VARCHAR(2000) zamiast VARCHAR(MAX).
   W tym przypadku symbol „?” zostanie zastąpiona nazwą bazy danych.
3. Używając konstrukcji INSERT ... EXEC, zapisujemy wszystkie wyniki w tabeli tymczasowej, aby ostatecznie wyświetlić pojedynczy wynik.

Wyszukiwanie podciągów w tekstach procedur składowanych

Jak wiadomo, teksty procedur składowanych, wyzwalaczy i widoków można znaleźć w tabeli komentarze systemowe.

Aby nasze poprzednie zapytanie pozwoliło nam znaleźć wzmiankę o określonym podciągu w komentarzach sys, można je przepisać w następujący sposób:

USTAW @strSQL = "
WYBIERZ WYRÓŻNIONY " "?" " as db, o.name FROM [?].dbo.syscomments s
JOIN [?].dbo.sysobjects o ON o.id = s.id GDZIE s. TAK JAK""%" +@nazwa+"%" ""

Zwróć uwagę na DISTINCT - może się przydać, ponieważ czasami w komentarzach systemowych znajduje się kilka wpisów dla jednego obiektu.

Wyszukaj podciąg we wszystkich polach tekstowych wszystkich tabel

Skrypt ten bardzo pomaga spać spokojnie po „wyczyszczeniu” bazy danych przed wysłaniem jej do klienta. Oczywiście jego zastosowanie nie ogranicza się do tego obszaru.

W tym przypadku nie było pomocnika w postaci procedury z MS, więc musiałem użyć kursorów:

zadeklaruj @pattern jako nvarchar(MAX)
set @pattern = N"%Test%"
ustaw nocount na
zadeklaruj @sql jako nvarchar(MAX)
zadeklaruj @table jako nazwę systemu
zadeklaruj kursor tabel lokalny statyczny tylko do odczytu dla wybierz nazwę z sys.tables t gdzie t.is_ms_shipped = 0
otwarte stoły

utwórz tabelę #results(nazwa nazwa systemu nie jest null, wartość nvarchar(MAX) nie jest null)
podczas gdy @@FETCH_STATUS = 0
zaczynać
ustaw @sql = ""
wybierz @sql = @sql + "
wstaw do #results wybierz „„” + @tabela + „.” + nazwa + "" " jako nazwa, [" + nazwa + "] z [" + @table + "] gdzie [" + nazwa + "] jak " "" + @pattern + "" ""
z sys.columns c gdzie c.object_id = OBJECT_ID(@table ) i c.system_type_id w
(wybierz identyfikator typu_systemu z sys.types, gdzie nazwa_zestawienia nie ma wartości null)
exec (@sql)
pobierz następny z tabel do @table
koniec
wybierz * z #results
zamknąć stoły
upuść tabelę #wyniki

Oprócz kursorów zasady są takie same. Istnieje kilka niuansów:

• Poszukujemy tabel bez flagi is_ms_shipped, aby wyświetlić tylko tabele utworzone w fazie programowania.
• Aby wybrać kolumny łańcuchowe, zamiast listy typów (która jest żmudna w utrzymaniu), używany jest filtr „nazwa_sortowania nie jest pusta”.

Streszczenie

Uważny czytelnik zapewne domyślił się z projektu, że ostatni przykład nie powstał w tym samym czasie, co dwa pierwsze. Dla ścisłości, ostatnią prośbę pisałem miesiąc temu. Mam na myśli to, że w oparciu o to podejście możesz samodzielnie napisać przydatne skrypty, które rozwiążą Twoje konkretne problemy.

Jeśli czytelnikom spodoba się ten format, czasami będę publikować przydatne skrypty z mojej osobistej kolekcji.

Jeśli masz uwagi, sugestie lub nowe tematy, napisz w komentarzach,

Trudno jest wyjaśnić składnię klauzuli WHERE programu SQL Server, więc spójrzmy na kilka przykładów.

Zaczniemy od sprawdzenia, jak używać klauzuli WHERE z tylko jednym warunkiem.

WYBIERZ * OD pracowników GDZIE imię = „Jane”;

W tym przykładzie klauzuli WHERE programu SQL Server użyliśmy klauzuli WHERE do filtrowania naszych wyników z pliku pracownicy tabela. Powyższa instrukcja SELECT zwróci wszystkie wiersze z pliku pracownicy stół, gdzie imię jest „Jane”. Ponieważ * jest używane w SELECT, wszystkie pola z pracownicy tabela pojawi się w zestawie wyników.

Przykład — użycie warunku AND

Przyjrzyjmy się, jak używać klauzuli WHERE z warunkiem AND.

WYBIERZ * OD pracowników GDZIE nazwisko = „Anderson” ORAZ id_pracownika >= 3000;

W tym przykładzie klauzuli WHERE programu SQL Server użyto klauzuli WHERE do zdefiniowania wielu warunków. W tym przypadku instrukcja SELECT używa warunku AND, aby zwrócić wszystko pracownicy które mają nazwisko„Andersona” i dowód pracownika jest większa lub równa 3000.

Przykład — użycie warunku OR

Przyjrzyjmy się, jak używać klauzuli WHERE z warunkiem OR.

WYBIERZ identyfikator_pracownika, nazwisko, imię OD pracowników GDZIE nazwisko = „Johnson” LUB imię = „Danielle”;

W tym przykładzie klauzuli WHERE SQL Server używa klauzuli WHERE do zdefiniowania wielu warunków, ale zamiast używać warunku AND, używa warunku OR. W tym przypadku ta instrukcja SELECT zwróci wszystko dowód pracownika, nazwisko, I imię wartości z pracownicy stół, gdzie nazwisko to „Johnson” lub imię jest „Daniel”.

Przykład — łączenie warunków AND i OR

Przyjrzyjmy się, jak używać klauzuli WHERE, gdy łączymy warunki AND i OR w jednej instrukcji SQL.

WYBIERZ * OD pracowników GDZIE (stan = „Kalifornia” ORAZ nazwisko = „Smith”) LUB (id_pracownika = 82);

W tym przykładzie klauzuli WHERE programu SQL Server użyto klauzuli WHERE do zdefiniowania wielu warunków, ale łączy ona warunek AND i warunek OR. Ten przykład zwróci wszystko pracownicy która mieszka w państwo z „Kalifornii” i którego nazwisko jest „Smith”, a także wszyscy pracownicy, których dowód pracownika jest równa 82.

Rodzice ustalają kolejność I i Oceniane są warunki LUB. Kolejność działań taka, jaką nauczyłeś się na lekcjach matematyki!

Przykład — łączenie tabel

Przyjrzyjmy się, jak używać klauzuli WHERE, gdy łączymy ze sobą wiele tabel.

WYBIERZ pracowników.employee_id, kontakty.last_name Z pracowników INNER DOŁĄCZ kontakty NA pracowników.employee_id = kontakty.contact_id GDZIE pracownicy.first_name = "Sarah";

W tym przykładzie klauzuli WHERE programu SQL Server zastosowano klauzulę WHERE w celu połączenia wielu tabel w jednej instrukcji SELECT. Ta instrukcja SELECT zwróci wszystkie wiersze, w których imię w pracownicy stół to „Sarah”. I pracownik i Łączność tabele są łączone na dowód pracownika z pracownicy stół i identyfikator_kontaktu z Łączność tabela.

W większości przypadków nie trzeba pobierać wszystkich rekordów, a jedynie te, które spełniają określone kryteria. Dlatego też, aby przefiltrować próbkę SQL-a istnieje operator specjalny GDZIE.

1. Proste filtrowanie za pomocą operatora WHERE.

Wybierzmy na przykład z naszej tabeli rekordy, które dotyczą tylko konkretnego produktu. W tym celu określimy dodatkowy parametr selekcji, który będzie filtrował wartość według kolumny Produkt.

Przykładowe zapytanie o wybór wartości tekstowych:

WYBIERAĆ * Z Sumproduct GDZIE Produkt = „Rowery”

Jak widać, warunek wyboru jest ujęty w pojedynczy cudzysłów, co jest obowiązkowe przy filtrowaniu wartości tekstowych. Podczas filtrowania wartości liczbowych cudzysłowy nie są potrzebne.

Przykładowe zapytanie dotyczące wyboru wartości liczbowych:

WYBIERAĆ > 40000 ZAMÓW PRZEZ Kwota

W tym przykładzie wybraliśmy rekordy, w których przychody ze sprzedaży wyniosły ponad 40 tysięcy dolarów dodatkowo wszystkie rekordy zostały posortowane rosnąco według pól Kwota

Poniższa tabela zawiera listę obsługiwanych instrukcji warunkowych SQL-a:

2. Filtrowanie według zakresu wartości ( MIĘDZY).

Aby wybrać dane mieszczące się w określonym przedziale, stosuje się operator MIĘDZY. Poniższe zapytanie wybierze wszystkie wartości z zakresu 1000 $ V 2000 $ włącznie, w terenie Kwota.

WYBIERAĆ * Z Sumproduct GDZIE Kwota MIĘDZY 1000 I 2000

Kolejność sortowania będzie zależała od kolejności pól w żądaniu. Oznacza to, że w naszym przypadku najpierw dane zostaną posortowane według kolumn Kwota, a następnie przez Miasto.

3. Wybór pustych rekordów ( JEST NULL).

W SQL-a istnieje specjalny operator do wybierania pustych rekordów (tzw ZERO). Pusty wpis to dowolna komórka w tabeli, w której nie wprowadzono żadnych znaków. Jeśli wprowadzono do komórki 0 Lub przestrzeń, wówczas pole uważa się za wypełnione.

WYBIERAĆ * Z Sumproduct GDZIE Kwota JEST NULL

W powyższym przykładzie celowo usunęliśmy dwie wartości w polu Kwota aby zademonstrować pracę operatora ZERO.

4. Zaawansowane filtrowanie ( ORAZ, LUB).

Język SQL-a nie ogranicza się do filtrowania według jednego warunku, na własne potrzeby można zastosować dość złożone struktury, aby selekcjonować dane jednocześnie według wielu kryteriów. W tym celu w SQL-a istnieją dodatkowe operatory, które rozszerzają możliwości operatora GDZIE. Takimi operatorami są: ORAZ, LUB, W, NIE. Oto kilka przykładów działania tych operatorów.

WYBIERAĆ * Z Sumproduct GDZIE Kwota > 40000 I Miasto = „Toronto”

WYBIERAĆ * Z Sumproduct GDZIE Miesiąc= „kwiecień”LUB Miesiąc= „Marzec”

Połączmy operatory I I LUB. W tym celu dokonamy selekcji rowerów ( Rowery ) i łyżwy ( Łyżwy ), które zostały sprzedane w marcu (Marsz ).

WYBIERAĆ * Z Sumproduct GDZIE Produkt= „Rowery”LUB Produkt= „łyżwy” I Miesiąc= „Marzec”

Widzimy, że nasza próbka obejmowała wiele wartości (z wyjątkiem marca ( Marsz), także styczeń ( Styczeń), Luty ( Luty) i kwiecień ( Kwiecień)). Jaki jest powód? I faktem jest, że SQL-a ma priorytety wykonania poleceń. Czyli operatora I ma wyższy priorytet niż operator LUB, więc najpierw wybrano płyty z rolkami, które sprzedano w marcu, a następnie wszystkie rekordy związane z rowerami.

Aby więc uzyskać poprawny wybór, musimy zmienić priorytety wykonywania poleceń. Do tego używamy nawiasy, jak w matematyce. Następnie najpierw zostaną przetworzone operatory w nawiasach, a dopiero potem wszystkie pozostałe.

WYBIERAĆ * Z Sumproduct GDZIE (Produkt= „Rowery”LUB Produkt= „łyżwy”) I Miesiąc= „Marzec”

5. Zaawansowane filtrowanie ( operatora IN).

WYBIERAĆ * Z Sumproduct GDZIE IDW (4, 12, 58, 67)

Operator W pełni tę samą funkcję co LUB ma jednak wiele zalet:

• Podczas pracy z długimi listami klauzula with Włatwiejszy do odczytania;
• Używa się mniej operatorów, co przyspiesza przetwarzanie żądań;
• Najważniejsza zaleta W polega na tym, że w jego konstrukcji można zastosować dodatkową konstrukcję WYBIERAĆ, Co
otwiera ogromne możliwości tworzenia złożonych podzapytań.

6. Zaawansowane filtrowanie ( NIE operatora).

WYBIERAĆ * Z Sumproduct GDZIE NIE MiastoW („Toronto”, „Montreal”)

Słowo kluczowe NIE pozwala usunąć niepotrzebne wartości z próbki. Kolejną unikalną cechą jest to, że jest on umieszczany przed nazwą kolumny objętej filtrowaniem, a nie po.

W tym artykule opowiem o operatorze warunkowym CASE, bez którego rozliczanie danych w szeregu zadań zamieniłoby się w stosy fragmentów kodu, a także o tym, jak wykorzystać go w zapytaniach SQL.

Bez względu na to, jak dobrze zaprojektujesz bazę danych, zawsze pojawią się problemy, gdy instrukcje warunkowe będą niezbędne. Na przykład zamiast ogromnych liczb uzyskaj opis słowny lub w zależności od obecności (braku) danych dodaj dodatkowe znaki do linii lub w bardziej złożonym przykładzie, w zależności od różnych niuansów, przypisz rekord do jednego lub inna grupa. Sytuacji jest wiele.

W zasadzie dany operator posiada co najmniej dwa formularze w różnych bazach danych. Pierwsza opcja przypomina zwykłe przejście z dowolnego języka programowania. Drugi zależy od wyrażeń logicznych.

Jednakże na potrzeby tego artykułu rozważę drugą opcję, ponieważ nie stwarza ona problemów w sytuacjach takich jak CASE WHEN NULL (null w bazie danych nie jest konkretną wartością, więc nie można jej użyć w instrukcji podobnej do przełącznika ). Ponadto w życiu codziennym zadania najczęściej występują specjalnie dla drugiej opcji - obliczeń za pomocą wyrażeń logicznych. Dlatego lepiej od razu się tego nauczyć i dalej z niego korzystać.

Zazwyczaj jest to opisane w ten sposób (składnia może się różnić w zależności od bazy danych):

PRZYPADEK KIEDY wyrażenie_bool1 WTEDY wartość1 ..... KIEDY wyrażenie_boolN WTEDY wartośćN W przeciwnym razie wartośćElse KONIEC

bool_expressionX jest warunkiem logicznym

wartośćX to wartość, która zostanie zastąpiona, jeśli spełniony zostanie odpowiedni warunek logiczny

wartośćElse to wartość, która zostanie zastąpiona, jeśli wcześniej nie został spełniony żaden warunek.

Po tej małej informacji przejdźmy do ćwiczeń.

Notatka: Swoją drogą warto wiedzieć, że zazwyczaj tego operatora można używać nie tylko w selekcji, ale także wszędzie tam, gdzie można zastosować pola. Na przykład kiedy łączyć stoły lub nawet filtrowanie (posiadanie) podczas grupowania (grupowanie według).

Instrukcja warunkowa PRZYPADEK...KIEDY...WTEDY

Aby lepiej zrozumieć instrukcję warunkową CASE...KIEDY...THEN, wyobraźmy sobie mały problem. Załóżmy, że masz tabelę z danymi o klientach i łącznej liczbie ich zakupów. A zadaniem jest dynamiczne tworzenie rabatu. Byłoby oczywiście możliwe ręczne ustawienie rabatu. Ale masz próg, który jest ustalony na stałe (coś w rodzaju: kwota większa niż 1000 otrzyma 2% rabatu, a powyżej 5000 otrzyma 5%) i chciałbyś zautomatyzować ten proces, aby nie nie musisz za każdym razem szukać błędów w bazie danych (klient zgromadził wymaganą kwotę - rabat pojawia się automatycznie).

Weźmy warunkową tabelę klientów z trzema klientami. Na przykład będą w zupełności wystarczające.

Teraz ustalmy kilka warunków automatycznego udzielania rabatu na podstawie zadania. Jednocześnie wierzymy, że klient w każdym przypadku otrzymuje maksymalny rabat.

1. Kwota od 1000 - 2% rabatu

2. Kwota od 5000 - rabat 5%.

3. Kwota od 10 000 - 8% rabatu

4. Ilość zamówień od 10 - 7% rabatu

5. Ilość zamówień od 20 - 8% rabatu

Jak widać rabat zależy od dwóch czynników: kwoty i ilości. Spróbujmy teraz stworzyć z nich warunki na podstawie rabatu, czyli reguły są odwrotne, aby dało się je zastosować w zapytaniu sql. Otrzymujemy co następuje:

1. 2% - Kwota od 1000 do 4999 i liczba zleceń mniejsza niż 10.

2. 5% - Kwota od 5000 do 9999 i liczba zleceń mniejsza niż 10.

3. 7% - Liczba zamówień od 10 do 19 i kwota mniejsza niż 10 000

4. 8% - Ilość od 20 lub kwota od 10 000

Teraz pozostało tylko to zapisać. Uzyskajmy następujące zapytanie sql

Wyświetlamy nazwę i inne dane wybierz nazwę, liczbę_zamówień, sumę_sumy, -- A teraz wyświetlamy rabat PRZYPADEK -- Pierwsza zasada 2% KIEDY c.total_sum >= 1000 i c.total_sum<= 4999 and c.order_count < 10 THEN 2 -- Второе правило 5% WHEN c.total_sum >= 5000 i c.total_sum<= 9999 and c.order_count < 10 THEN 5 -- Третье правило 7% WHEN c.total_sum < 10000 and c.order_count >= 10 i c.order_count<= 19 THEN 5 -- Четвертое правило 8% WHEN c.total_sum >= 10000 lub c.order_count >= 20 THEN 5 -- Nie są spełnione żadne reguły, co oznacza, że ​​rabat wynosi 0. ELSE 0 END jako rabat od klienta c

W wyniku wykonania otrzymujemy następującą tabelę:

Jak widać, dwóch klientów otrzymało 8-procentowy rabat, a jeden klient otrzymał 2-procentowy rabat. W takim przypadku przy każdym zamówieniu procent będzie obliczany automatycznie i nie będziesz musiał niczego dostosowywać. Na przykład, jeśli kwota Petyi wzrośnie do 5000, wówczas jego zniżka automatycznie wzrośnie do 5% (przynajmniej, ponieważ nadal jest wiele zamówień).