Ć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.

Do przeprowadzenia niniejszego ćwiczenia niezbędne jest wcześniejsze przeprowadzenie ćwiczenia z lekcji 3.10 Import i eksport danych (musisz zaimportować dane do bazy danych Biblioteka).


Krok 1 - Używając polecenia SELECT dokonaj agregacji danych

1. 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).


-- (1) ustawmy Biblioteke jako baze robocza 
USE Biblioteka
GO

-- (2) dokonajmy zestawienia (agregacji)
-- ilosci ksiazek wg roku wydania
SELECT rok_wydania AS [Rok], 
       COUNT(*) AS [Ilosc wydanych ksiazek]
FROM Ksiazki
GROUP BY rok_wydania
GO


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.


-- (2) dokonajmy zestawienia (agregacji)
-- ilosci ksiazek wg roku wydania
SELECT rok_wydania AS [Rok], 
       COUNT(*) AS [Ilosc wydanych ksiazek]
FROM Ksiazki
GROUP BY rok_wydania
HAVING rok_wydania >= '2002'
GO


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żone

1. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (3).


-- (3) wykorzystajmy zagniezdzone zapytanie
DECLARE @wydawnictwo varchar(100)
SET @wydawnictwo = 'MS Press'

SELECT tytul
FROM Ksiazki
WHERE ID_wydawnictwa = (
	SELECT ID_wydawnictwa
	FROM Wydawnictwa
	WHERE wydawnictwo = @wydawnictwo)
GO


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.

Gdy używasz zagnieżdżonych zapytań, musisz zwrócić uwagę, że zapytanie takie na ogół powinno zwracać pojedynczą wartość.

Zagnieżdżaj zapytania wtedy, gdy nie widzisz innej alternatywy. Dla powyższego przypadku lepszym rozwiązaniem byłoby wykorzystanie złączenia (lub utworzenie perspektywy - dowiesz się więcej na ten temat w dalszej części ćwiczeń).


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).


-- (1) ustawmy Biblioteke jako baze robocza 
USE Biblioteka
GO

-- (2) zlaczenie wewnetrzne
SELECT Ksiazki.tytul, Ksiazki.rok_wydania, Wydawnictwa.wydawnictwo
FROM Ksiazki
INNER JOIN Wydawnictwa
ON Ksiazki.ID_wydawnictwa = Wydawnictwa.ID_Wydawnictwa
GO


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).

Złączenia wewnętrzne są najczęściej wykorzystywane do tworzenia perspektyw.


4. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (3).


-- (3) zlaczenie zewnetrzne prawe
SELECT Ksiazki.tytul, Ksiazki.rok_wydania, Wydawnictwa.wydawnictwo
FROM Ksiazki
RIGHT OUTER JOIN Wydawnictwa
ON Ksiazki.ID_wydawnictwa = Wydawnictwa.ID_Wydawnictwa
GO


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).


-- (4) zlaczenie zewnetrzne lewe
SELECT Wydawnictwa.wydawnictwo, Ksiazki.tytul, Ksiazki.rok_wydania
FROM Wydawnictwa
LEFT OUTER JOIN Ksiazki
ON Wydawnictwa.ID_wydawnictwa = Ksiazki.ID_Wydawnictwa
GO


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).


-- (5) zlaczenie krzyzowe - iloczyn kartezjanski
SELECT tytul, rok_wydania, wydawnictwo
FROM Ksiazki
CROSS JOIN Wydawnictwa
GO


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ów

1. 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.


-- ustawmy Biblioteke jako baze robocza 
USE Biblioteka
GO

-- zadeklarujmy zmienne
DECLARE 
@id_wydawnictwa int,
@wydawnictwo varchar(100),
@komunikat varchar(150),
@tytul varchar(100)

-- wyswietlmy naglowek raportu
PRINT '----- Ksiazki wg wydawnictw -----'

-- zadeklarujmy kursor do przegladania wydawnictw
DECLARE kursor_wydawnictwa CURSOR FOR 
SELECT ID_wydawnictwa, wydawnictwo
FROM Wydawnictwa
ORDER BY wydawnictwo

-- otworzmy zadeklarowany kursor
OPEN kursor_wydawnictwa

-- pobierzmy pierwszy rekord z kursora
-- i wstawmy wartosci z pol do zmiennych
FETCH NEXT FROM kursor_wydawnictwa 
INTO @id_wydawnictwa, @wydawnictwo

-- w petli wyswietlajmy ksiazki wg wydawnictw
-- przy uzyciu drugiego kursora 
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT ' ' 
	SELECT @komunikat = '*** Wydawnicwto: ' + @wydawnictwo + ' ***'
	PRINT @komunikat
	
	DECLARE kursor_ksiazki CURSOR FOR
	SELECT tytul
	FROM Ksiazki
	WHERE ID_wydawnictwa = @id_wydawnictwa
	
	OPEN kursor_ksiazki
	
	FETCH NEXT FROM kursor_ksiazki INTO @tytul

	IF @@FETCH_STATUS <> 0 
      		PRINT '    <>'    

	WHILE @@FETCH_STATUS = 0
   	BEGIN      
      		SELECT @komunikat = '    ' + @tytul
      		PRINT @komunikat
      		FETCH NEXT FROM kursor_ksiazki INTO @tytul
      	END

   	CLOSE kursor_ksiazki
   	DEALLOCATE kursor_ksiazki
   
   	FETCH NEXT FROM kursor_wydawnictwa 
	INTO @id_wydawnictwa, @wydawnictwo
END

-- zamknijmy kursor i zwolnijmy pamiec
CLOSE kursor_wydawnictwa
DEALLOCATE kursor_wydawnictwa
GO


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ów
W 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.

Z uwagi na fakt, że kursory rezydują w pamięci komputera, przy dużej ilości rekordów w bazie danych w zapytaniach przy deklaracji kursorów najlepiej jest użyć klauzuli WHERE. Ograniczy to ilość pamięci potrzebnej do funkcjonowania kursorów.


Do projektu Prace dyplomowe dołącz skrypt zaaw_tsql.sql, w którym zaprezentuj przykładowe zapytania z użyciem agregacji i kursorów (np. zrób zestawienie ilości prac obronionych w danym roku - do wyciągnięcia roku z pełnej daty możesz użyć funkcji DATEPART, o której więcej znajdziesz w Books Online; zaś za pomocą kursorów wyświetl raport prezentujący tytuły prac według promotorów).


Przejdź dalej



Ćwiczenia > Język T-SQL > Zaawansowane metody dostępu do danych