11. Filtracja danych

Streszczenie

Artykuł ten chciałbym poświęcić klauzuli WHERE. Ponieważ do tej pory traktowaliśmy ją bardzo ogólnikowo, myślę, że przyszedł czas na dokładne przeanalizowanie możliwości, których może nam dostarczyć skuteczne jej wykorzystanie.

Treść

Klauzula WHERE służy do ograniczenia zbioru danych, na których przeprowadzana będzie operacja. Dotyczy to zarówno operacji SELECT, jak i UPDATE i DELETE. W warunkach wyszukiwania można uwzględnić szereg operatorów porównania, dopasowywania łańcuchów znaków i wartości niezdefiniowanej NULL. Sama składnia ograniczająca warunki wyszukiwania jest dość prosta:

Podstawowy sposób filtracji danych polega na pobieraniu wartości większy, mniejszy lub równy  i porównaniu z pewną wartością, którą podajemy. Przykładem może być wybranie kontaktu z osobami o imieniu Marcin lub ludzi urodzonych po roku 1980. Wszystkie te operacje można bez problemu wykonać za pomocą operatorów porównania. Poniższa tabela przedstawia operatory porównania dostępne w standardzie Transact-SQL.

Przejdźmy teraz do przeanalizowania prostego zapytania, które już wielokrotnie przetoczyło się przez ten kurs. Wygenerujmy sobie w Query Designer skrypt wylistowujący kontakty do osób mających nazwisko Diaz. Poprawnie wygenerowane zapytanie powinno wyglądać w następująco:

SELECT     Person.Contact.*
FROM       Person.Contact
WHERE      (LastName = N'Diaz')

Skupmy się jednak na klauzuli WHERE. Kolumna LastName jest typu znakowego, z tego względu tekst wpisany do designera jest standardowo konwertowany do łańcuch znakowy. W tym celu SQL Server zastosował funkcję konwersji:

 N' wartość_do_konwersji ’

Sprawdźmy, co się stanie, jeśli kolumna, po której będziemy wyszukiwać, będzie miała wartość integer (całkowitoliczbową).

SELECT   Person.Contact.*
FROM     Person.Contact
WHERE    (ContactID = 257)

Ponieważ kolumna ContactID jest typu całkowitego designer nie musi, a nawet nie może konwertować wartości. Próba wpisania w kolumnie Filter wyrażenia innego niż cyfra zakończy się niepowodzeniem i wypisaniem komunikatu o błędzie.
Wiemy już jak wybierać informacje o pojedynczym wierszu. Teraz przejdźmy do wybrania wszystkich rekordów z bazy, gdzie data modyfikacji wiersza jest większa niż 2004-07-26. W tym celu przy kolumnie związanej z nakładaniem warunków wpisujemy datę 2004-07-26.

SELECT    Person.Contact.*
FROM      Person.Contact
WHERE     (ModifiedDate > CONVERT(DATETIME, '2004-07-26 00:00:00', 102))

Zapytanie, które dostaliśmy jako wynik naszego ograniczenia, jest dość skomplikowane. Spowodowane jest to tym, iż kolumna, na którą nakładamy warunki jest typu datetime. Podobnie jak w przypadku łańcucha znakowego, designer konwertuje tekst wpisany przez użytkownika do formatu takiego, jaki ma kolumna. Konwersja ta jest jednak znacznie bardziej skomplikowana i używam do niej systemowej funkcji CONVERT. Funkcja ta przyjmuje jako pierwszy argument typ na jaki konwertujemy. Drugim argumentem jest obiekt, którego typ chcemy zmienić, zaś ostatni parametr określa styl podawania daty. W zależności, czy rok oddzielimy od miesiąca myślnikami czy kropkami musimy podać stosowny do tego sposób kodowania. Tabelę określającą odpowiednie formaty zamieściłem poniżej.

Wiemy już, jak przeszukiwać tabelę po typie całkowitym, znakowym oraz po dacie. Spróbujmy teraz złożyć wszystko to razem i zapisać zapytanie wybierające wszystkie kontakty, które zostały zmodyfikowane po 21 lipca 2004 dla osób z nazwiskiem Diaz lub tą, która ma identyfikator równy 13365. Takiego zapytania jeszcze nie pisaliśmy. Mamy tu do czynienia z dwoma różnymi operatorami logicznymi pomiędzy poszczególnymi warunkami AND, OR. Pierwszą operacją jest wybranie z tabeli pól, jakie będą nam potrzebne, czyli LastName, ModificationDate i ContactID. Jeśli mamy już wybrane pola przechodzimy do określania warunków. Zacznijmy od wyznaczenia zbioru rekordów, które zostały zmodyfikowane po 2004-07-21. Czyli w kolumnie Filter wpisujemy naszą datę poprzedzoną znakiem >. Teraz dokładamy warunek ograniczający zbiór rekordów do osób z nazwiskiem Diaz. Na tą chwilę zapytanie wygląda następująco:

SELECT    Person.Contact.*
FROM      Person.Contact
WHERE     (ModifiedDate > CONVERT(DATETIME, '2004-07- 21 00:00:00', 102)) AND (LastName = N'Diaz')

Teraz musimy dodać warunek, który dołoży do naszego zbioru rozwiązań kontakt z identyfikatorem 13365. W tym celu do klauzuli WHERE dodajemy:

OR (ContactID = 13365)

Całe zapytanie wygląda tak:

SELECT    LastName, ModifiedDate, ContactID
FROM      Person.Contact
WHERE     (ModifiedDate > CONVERT(DATETIME, '2004-07-21 00:00:00', 102)) AND (LastName = N'Diaz') OR (ContactID = 13365)

Jednak przeważnie podczas wyszukiwania nie znamy całego nazwiska lub imienia (albo po prostu nie chce nam się całego wpisywać) wtedy musimy nieco zmodyfikować nasze zapytanie. Jeśli chcielibyśmy wyświetlić listę osób, których imię zaczyna się na literę A zaś nazwisko na S powinniśmy użyć słowo kluczowe LIKE. Składnia wyrażenia jest następująca:

WHERE nazwa_kolumny [NOT] LIKE ‘szukane_tekst’

a zapytanie wygląda w następujący sposób:

SELECT    FirstName, LastName
FROM      Person.Contact
WHERE     (FirstName LIKE N'A%') AND (LastName LIKE N'S%')

Znak % jest znakiem autouzupełnienia. Mówi o tym, że po naszej literze może występować dowolny ciąg różnych znaków. Gdybyśmy przed literą A dodali znak % oznaczałoby to, że szukamy takich imion, które posiadają literę A nie zważając na pozycję na której ona występuje. Lista innych znaków autouzupełnienia przedstawia poniższa tabela.

Jeśli chcielibyśmy znaleźć osobę, której nazwisko zaczyna się na literę D, po niej występują dwie inne litery, następnie litera Z i ciąg innych liter musielibyśmy uruchomić następujący skrypt.

SELECT    FirstName, LastName
FROM      Person.Contact
WHERE     (LastName LIKE N'D__Z%')

Podsumowanie

W powyższym artykule dowiedzieliśmy się, jak w sposób efektywny ograniczać zbiór danych wynikowych ze względu na nasze potrzeby. Wszystkie skrypty, które utworzyliśmy były przedstawione na podstawie operacji SELECT jednak można bez żadnych przeszkód użyć ich zarówno w operacjach UPDATE jak i DELETE. Składnia klauzuli WHERE jest we wszystkich przypadkach taka sama.