Ćwiczenia
> Język T-SQL > Zaawansowane metody dostępu do danych |
![]() ![]() |
||||||||||||||||||||||||||
4.5 Zaawansowane metody dostępu do danych![]() Wyszukiwanie i wyświetlanie danych może być wykonywane na wiele sposobów. Sama składnia polecenia SELECT może być bardzo złożona (zobacz Books Online). W tej lekcji pokażemy Ci kilka przykładów nieco innych metod wyszukiwania i wyświetlania danych przy użyciu języka T-SQL.
Krok 1 - Używając polecenia SELECT dokonaj agregacji danych1. Uruchom program Query Analyzer.2. Zaloguj się używając uwierzytelnienia systemu Windows. 3. W menu głównym programu wybierz File - Open. 4. W oknie Open Query File wybierz plik demo_4_5_1.sql. 5. Zaznacz i uruchom (klawisz F5) fragmenty kodu oznaczone w komentarzach jako (1) oraz (2).
Wynikiem wykonania powyższego kodu jest ustawienie bazy danych Biblioteka jako bazy roboczej oraz wyświetlenie ilości książek z tabeli Ksiazki wydanych każdego roku. Rok Ilosc wydanych ksiazek ---- ---------------------- 2001 1 2002 2 2003 2 (3 row(s) affected)Klauzula GROUP BY powoduje, że dane są grupowane według wybranej kolumny lub wybranego fragmentu danych (nie zawsze to jest kolumna tabeli, ale zawsze jest to kolumna w zestawie wyników). Funkcję COUNT już znasz. W tym przypadku służy ona do zliczenia rekordów, w których powtarza się taki sam rok wydania. 2. Usuń komentarz w linijce ze słowem HAVING w kodzie oznaczonym w komentarzu jako (2) i ponownie zaznacz i uruchom ten fragment kodu.
Wynikiem wykonania powyższego kodu jest wyświetlenie ilości książek z tabeli Ksiazki wydanych każdego roku począwszy od roku 2002. Rok Ilosc wydanych ksiazek ---- ---------------------- 2002 2 2003 2 (2 row(s) affected)Klauzula HAVING jest odpowiednikiem WHERE dla agregacji (ogranicza liczbę wierszy w agregacji). Krok 2 - Zobacz, jak działają zapytania zagnieżdżone1. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (3).
Wynikiem wykonania powyższego kodu jest wyświetlenie książek z tabeli Ksiazki wydanych przez wydawnictwo MS Press. tytul --------------------------------------------- SQL Server 2000. Vademecum Administratora Windows Server 2003. Vademecum Administratora MS Access wersja 2002 dla ekspertów (3 row(s) affected)W powyższym kodzie użyliśmy zmiennej do przekazania do zapytania nazwy wydawnictwa. Następnie przy pomocy zagnieżdżonego zapytania SELECT uzyskaliśmy identyfikator wydawnictwa, który posłużył jako parametr w klazuli WHERE zapytania nadrzędnego SELECT wyszukującego w tabeli Ksiazki książki wydane przez wybrane wydawnictwo.
Krok 3 - Zapoznaj się z różnymi rodzajami złączeń1. W menu głównym programu Query Analyzer wybierz File - Open.2. W oknie Open Query File wybierz plik demo_4_5_2.sql. 3. Zaznacz i uruchom (F5) fragmenty kodu oznaczone w komentarzach jako (1) oraz (2).
Wynikiem wykonania powyższego kodu jest ustawienie bazy danych Biblioteka jako bazy roboczej oraz wyświetlenie wszystkich książek z tabeli Ksiazki wraz z nazwą wydawnictwa z tabeli Wydawnictwa. tytul rok_wydania wydawnictwo ---------------------------------------------- ----------- ------------ SQL Server 2000. Vademecum Administratora 2001 MS Press Windows Server 2003. Vademecum Administratora 2003 MS Press UML w kropelce 2002 LTP MS Access wersja 2002 dla ekspertów 2003 MS Press XML na poważnie 2002 Helion (5 row(s) affected)Powyższy kod to przykład złączenia wewnętrznego (ang. inner join), które polega na wyświetleniu wybranych danych pochodzących z wielu tabel powiązanych związkami. Złączenie w tym wypadku następuje przez przyrównanie wartości klucza obcego tabeli Ksiazki (kolumna Ksiazki.ID_wydawnictwa) do wartości klucza głównego tabeli Wydawnictwa (kolumna Wydawnictwa.ID_wydawnictwa). Zwróć uwagę na notację "kropkową". Dzięki niej serwer wie, z której tabeli ma pobrać odpowiednie kolumny. Do zapytań zawierających złączenia można także dołączyć klauzulę WHERE (jak do zwykłych zapytań SELECT).
4. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (3).
Wynikiem wykonania powyższego kodu jest wyświetlenie listy książek wraz z nazwami wydawnictw - lista jest uzupełniona o puste pozycje książek dla wydawnictw, które nie mają na koncie żadnej wydanej książki. tytul rok_wydania wydawnictwo ---------------------------------------------- ----------- ------------ SQL Server 2000. Vademecum Administratora 2001 MS Press Windows Server 2003. Vademecum Administratora 2003 MS Press MS Access wersja 2002 dla ekspertów 2003 MS Press UML w kropelce 2002 LTP XML na poważnie 2002 Helion NULL NULL Mikom (6 row(s) affected)Powyższy kod jest przykładem zastosowania złączenia zewnętrznego prawego (ang. right outer join), które polega na wyświetleniu wyników takich jak złączenie wewnętrzne uzupełnionych o wiersze zawierające te wartości w kolumnach prawej tabeli (tu tabeli Wydawnictwa), którym nie przyporządkowano wierszy w tabeli lewej (tu tabeli Ksiazki). Stąd ostatni wiersz zawierający nazwę wydawnictwa Mikom (którego książek nie ma w bazie danych) oraz wartości puste (NULL) w kolumnach pochodzących z lewej tabeli (tabeli Ksiazki). 5. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (4).
Wynikiem wykonania powyższego kodu jest wyświetlenie listy jak w poprzednim przykładzie, tym razem jednak jako pierwsza wyświetlana jest kolumna z tabeli Wydawnictwa, a jako następne kolumny z tabeli Ksiazki. wydawnictwo tytul rok_wydania ------------ ---------------------------------------------- ----------- MS Press SQL Server 2000. Vademecum Administratora 2001 MS Press Windows Server 2003. Vademecum Administratora 2003 MS Press MS Access wersja 2002 dla ekspertów 2003 LTP UML w kropelce 2002 Helion XML na poważnie 2002 Mikom NULL NULL (6 row(s) affected)Powyższy kod jest przykładem złączenia zewnętrznego lewego (ang. left outer join), które polega na wyświetleniu wyników podobnych do wyników złączenia prawego, z tym że w złączeniu lewym nieprzyporządkowane wartości w prawej tabeli są uzupełniane wartościami pustymi (w przykładzie zamieniliśmy jedynie strony tabel - tabela Ksiazki jest teraz po prawej stronie, zaś Wydawnictwa po lewej). 6. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (5).
Wynikiem działania powyższego kodu jest wyświetlenie iloczynu kartezjańskiego dwóch relacji (tabel) - czyli wszystkich możliwych kombinacji kolumn z lewej tabeli z kolumnami z prawej tabeli. tytul rok_wydania wydawnictwo ---------------------------------------------- ----------- ------------ SQL Server 2000. Vademecum Administratora 2001 MS Press Windows Server 2003. Vademecum Administratora 2003 MS Press UML w kropelce 2002 MS Press MS Access wersja 2002 dla ekspertów 2003 MS Press XML na poważnie 2002 MS Press SQL Server 2000. Vademecum Administratora 2001 LTP Windows Server 2003. Vademecum Administratora 2003 LTP UML w kropelce 2002 LTP MS Access wersja 2002 dla ekspertów 2003 LTP XML na poważnie 2002 LTP SQL Server 2000. Vademecum Administratora 2001 Helion Windows Server 2003. Vademecum Administratora 2003 Helion UML w kropelce 2002 Helion MS Access wersja 2002 dla ekspertów 2003 Helion XML na poważnie 2002 Helion SQL Server 2000. Vademecum Administratora 2001 Mikom Windows Server 2003. Vademecum Administratora 2003 Mikom UML w kropelce 2002 Mikom MS Access wersja 2002 dla ekspertów 2003 Mikom XML na poważnie 2002 Mikom (20 row(s) affected)Powyższy kod jest przykładem użycia złączenia krzyżowego (ang. cross join), które polega na wyświetleniu wszystkich kombinacji wierszy z lewej tabeli z wierszami z prawej tabeli. W tabeli Ksiazki jest w tym przypadku 5 wierszy, zaś w tabeli Wydawnictwa - 4 wiersze. Stąd w wyniku złączenia krzyżowego powstało 20 wierszy (4 razy 5). Krok 4 - Wyświetl mini-raport korzystając z kursorów1. W menu głównym programu Query Analyzer wybierz File - Open.2. W oknie Open Query File wybierz plik demo_4_5_3.sql. 3. Uruchom (klawisz F5) skrypt.
Wynikiem wykonania powyższego kodu jest wyświetlenie mini-raportu zestawiającego tytuły książek według wydawnictw, przez które książki zostały wydane. ----- Ksiazki wg wydawnictw ----- *** Wydawnicwto: Helion *** XML na poważnie *** Wydawnicwto: LTP *** UML w kropelce *** Wydawnicwto: Mikom *** <<brak ksiazek>> *** Wydawnicwto: MS Press *** SQL Server 2000. Vademecum Administratora Windows Server 2003. Vademecum Administratora MS Access wersja 2002 dla ekspertówW powyższym kodzie użyliśmy tak zwanych kursorów (ang. cursor). Kursory są dynamicznymi zestawami rekordów przechowywanymi tymczasowo w pamięci komputera-klienta (akurat w naszym przypadku komputer-serwer i komputer-klient to ta sama maszyna). Kursory nie są obiektami bazy danych, a jedynie mechanizmem dostępu do danych. Zaletą kursorów jest to, że dzięki nim można przetwarzać w dowolny sposób zestaw rekordów rekord po rekordzie (aplikacje na ogół nie potrafią przetwarzać zbioru rekordów, a tylko pojedyncze rekordy, potrzebują mechanizmu umożliwiającego im poruszanie się po zbiorze rekordów). Omówmy szczegółowo powyższy kod: 1) ustawiamy bazę danych Biblioteka jako bazę roboczą, 2) deklarujemy zmienne, które będą potrzebne w dalszej części kodu, 3) wyświetlamy nagłówek raportu (funkcja PRINT), 4) za pomocą polecenia DECLARE CURSOR deklarujemy kursor kursor_wydawnictwa, który będzie służył do poruszania się po zbiorze rekordów zawierających identyfikatory i nazwy wszystkich wydawnictw z tabeli Wydawnictwa (patrz polecenie SELECT w deklaracji kursora), 5) używając polecenia OPEN CURSOR otwieramy świeżo utworzony kursor kursor_wydawnictwa (czyli umieszczamy zbiór rekordów w pamięci i umieszczamy wskaźnik przed pierwszym rekordem), 6) przy pomocy polecenia FETCH NEXT wartości z pierwszego rekordu umieszczamy w zmiennych @id_wydawnictwa oraz @wydawnictwo, 7) w pętli WHILE (pętli wykonującej się dopóki nie nastąpi warunek zakończenia pętli - w tym wypadku do wyzerowania wartości zwracanej przez funkcję systemową @@FETCH_STATUS - czyli dopóki kursor napotyka rekord w zestawie pobranym z tabeli Wydawnictwa) wykonujemy następujące operacje: wyświetlamy nazwę wydawnictwa (korzystając ze zmiennej @wydawnictwo), deklarujemy i otwieramy kursor kursor_ksiazki pobierający pojedyncze rekordy z zestawu książek danego wydawnictwa (kursor korzysta z polecenia SELECT z klauzulą WHERE, w której jako argument przekazywany jest pobrany identyfikator wydawnictwa), wyświetlamy książki danego wydawnictwa (jeśli książek nie ma, to funkcja @@FETCH_STATUS od razu zwraca zero i wyświetlamy informację o braku książek danego wydawnictwa), zamykamy i usuwamy z pamięci kursor kursor_ksiazki (odpowiednio polecenia CLOSE oraz DEALLOCATE), pobieramy dane następnego wydawnictwa używając kursora kursor_wydawnictwa (polecenie FETCH NEXT) aż do końca zbioru z tabeli Wydawnictwa (patrz warunek nadrzędnej pętli WHILE), wreszcie na koniec zamykamy i usuwamy z pamięci kursor kursor_wydawnictwa.
Przejdź dalej ![]() |
|||||||||||||||||||||||||||
![]() Ćwiczenia > Język T-SQL > Zaawansowane metody dostępu do danych |