Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00478 010179 11041410 na godz. na dobę w sumie
100 sposobów na SQL - książka
100 sposobów na SQL - książka
Autor: , Liczba stron: 400
Wydawca: Helion Język publikacji: polski
ISBN: 978-83-246-0985-7 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> bazy danych >> sql - programowanie
Porównaj ceny (książka, ebook, audiobook).

Zbiór praktycznych porad dla programistów i projektantów baz danych

Jesteś programistą, któremu przypadło w udziale opracowanie aplikacji bazodanowej? Szukasz sposobu na zoptymalizowanie działania zapytań SQL? A może zastanawiasz się, w jaki sposób sprawnie zarządzać kontami użytkowników bazy danych? Język SQL to potężne narzędzie, którego opanowanie pozwoli Ci na sprawne poruszanie się w gąszczu tabel każdej bazy danych. Za pomocą odpowiednio sformułowanych instrukcji można manipulować danymi, zarządzać kontami użytkowników i generować raporty. Jednak, pomimo stosunkowo niewielkiej ilości poleceń, język SQL kryje w sobie wiele zawiłości.

Dzięki książce '100 sposobów na SQL' nie będziesz musiał odkrywać każdej z nich samodzielnie. W każdym z rozdziałów znajdziesz praktyczne porady i sposoby rozwiązywania typowych zadań programistycznych związanych z bazami danych i językiem SQL. Poznasz podstawy języka, dowiesz się, w jaki sposób przetwarzać różne typy danych i korzystać z symboli zastępczych. Nauczysz się tworzyć aplikacje sieciowe, optymalizować wydajność tabel i zapytań SQL oraz tworzyć raporty. Przeczytasz ponadto o administrowaniu serwerem bazy danych i udostępnianiu tabel użytkownikom.

Zostań ekspertem w programowaniu baz danych!

Znajdź podobne książki Ostatnio czytane w tej kategorii

Darmowy fragment publikacji:

100 sposob(cid:243)w na SQL Autor: Andrew Cumming, Gordon Russell T‡umaczenie: Marcin Karbowski ISBN: 978-83-246-0985-7 Tytu‡ orygina‡u: SQL Hacks Format: B5, stron: 400 Przyk‡ady na ftp: 135 kB Zbi(cid:243)r praktycznych porad dla programist(cid:243)w i projektant(cid:243)w baz danych (cid:149) Jak obs‡ugiwa(cid:230) r(cid:243)¿ne typy danych? (cid:149) W jaki spos(cid:243)b optymalizowa(cid:230) wydajno(cid:156)(cid:230) baz danych? (cid:149) Jak budowa(cid:230) raporty za pomoc„ jŒzyka SQL? Jeste(cid:156) programist„, kt(cid:243)remu przypad‡o w udziale opracowanie aplikacji bazodanowej? Szukasz sposobu na zoptymalizowanie dzia‡ania zapytaæ SQL? A mo¿e zastanawiasz siŒ, w jaki spos(cid:243)b sprawnie zarz„dza(cid:230) kontami u¿ytkownik(cid:243)w bazy danych? JŒzyk SQL to potŒ¿ne narzŒdzie, kt(cid:243)rego opanowanie pozwoli Ci na sprawne poruszanie siŒ w g„szczu tabel ka¿dej bazy danych. Za pomoc„ odpowiednio sformu‡owanych instrukcji mo¿na manipulowa(cid:230) danymi, zarz„dza(cid:230) kontami u¿ytkownik(cid:243)w i generowa(cid:230) raporty. Jednak, pomimo stosunkowo niewielkiej ilo(cid:156)ci poleceæ, jŒzyk SQL kryje w sobie wiele zawi‡o(cid:156)ci. DziŒki ksi„¿ce (cid:132)100 sposob(cid:243)w na SQL(cid:148) nie bŒdziesz musia‡ odkrywa(cid:230) ka¿dej z nich samodzielnie. W ka¿dym z rozdzia‡(cid:243)w znajdziesz praktyczne porady i sposoby rozwi„zywania typowych zadaæ programistycznych zwi„zanych z bazami danych i jŒzykiem SQL. Poznasz podstawy jŒzyka, dowiesz siŒ, w jaki spos(cid:243)b przetwarza(cid:230) r(cid:243)¿ne typy danych i korzysta(cid:230) z symboli zastŒpczych. Nauczysz siŒ tworzy(cid:230) aplikacje sieciowe, optymalizowa(cid:230) wydajno(cid:156)(cid:230) tabel i zapytaæ SQL oraz tworzy(cid:230) raporty. Przeczytasz ponadto o administrowaniu serwerem bazy danych i udostŒpnianiu tabel u¿ytkownikom. (cid:149) Podstawowe elementy jŒzyka SQL (cid:149) £„czenie tabel (cid:149) Przetwarzanie danych tekstowych (cid:149) Operacje na liczbach i datach (cid:149) Bazy danych w aplikacjach sieciowych (cid:149) Zapytania niezale¿ne od tabel (cid:149) Maksymalizowanie wydajno(cid:156)ci zapytaæ (cid:149) Tworzenie raport(cid:243)w (cid:149) Administrowanie kontami u¿ytkownik(cid:243)w (cid:149) UdostŒpnianie informacji Zostaæ ekspertem w programowaniu baz danych! Wydawnictwo Helion ul. Ko(cid:156)ciuszki 1c 44-100 Gliwice tel. 032 230 98 63 e-mail: helion@helion.pl Spis treści Twórcy książki ................................................................................................................................9 Wstęp .............................................................................................................................................11 Rozdział 1. Podstawy SQL ...........................................................................................................19 1. Uruchamianie SQL za pomocą wiersza poleceń ........................................................ 19 2. Nawiązywanie połączenia między aplikacją a bazą danych SQL .................................. 27 3. Warunkowe polecenia INSERT ..................................................................................... 32 4. Polecenie UPDATE .......................................................................................................... 36 5. Rozwiązywanie krzyżówek w SQL .............................................................................. 39 6. Nie powtarzajcie bez przerwy tych samych obliczeń ................................................ 41 Rozdział 2. Polecenia JOIN, UNION oraz VIEW ..........................................................................45 7. Modyfikowanie schematu bez naruszania kwerend ................................................. 45 8. Filtrowanie wierszy i kolumn ........................................................................................ 47 9. Filtrowanie kolumn indeksowanych ............................................................................ 50 10. Konwertowanie podzapytań na wyrażenia JOIN ...................................................... 52 11. Konwertowanie zagregowanych podzapytań na wyrażenia JOIN ......................... 55 12. Upraszczanie skomplikowanych poleceń UPDATE .................................................. 56 13. Dopasowywanie złączeń do relacji ............................................................................... 58 14. Tworzenie kombinacji ..................................................................................................... 61 Rozdział 3. Obsługa tekstu ..........................................................................................................65 15. Wyszukiwanie słów kluczowych bez użycia operatora LIKE ................................. 65 16. Wyszukiwanie ciągów tekstowych w kolumnach ..................................................... 69 17. Rozwiązywanie anagramów .......................................................................................... 71 18. Sortowanie poczty elektronicznej ................................................................................. 76 Rozdział 4. Daty ............................................................................................................................81 19. Konwertowanie ciągów tekstowych na daty .............................................................. 81 20. Wyszukiwanie trendów .................................................................................................. 85 21. Tworzenie raportów w oparciu o dowolne przedziały czasowe ................................. 89 22. Raporty kwartalne ........................................................................................................... 94 23. Drugi wtorek miesiąca .................................................................................................... 97 Spis treści | 5 Rozdział 5. Dane liczbowe ......................................................................................................... 103 24. Mnożenie wartości ......................................................................................................... 103 25. Sumy pośrednie ............................................................................................................. 105 26. Dołączanie wierszy pominiętych przez wyrażenie JOIN ....................................... 109 27. Identyfikowanie nakładających się zakresów ........................................................... 111 28. Unikanie dzielenia przez zero ..................................................................................... 114 29. Wyrażenie COUNT ....................................................................................................... 115 30. Wyznaczanie wartości maksymalnej z dwóch pól .................................................. 118 31. Dzielenie rezultatów funkcji COUNT ........................................................................ 120 32. Błędy podczas zaokrąglania ........................................................................................ 121 33. Jednoczesne pobieranie wartości i sum częściowych .............................................. 123 34. Obliczanie mediany ....................................................................................................... 126 35. Przedstawianie wyników w formie wykresu ........................................................... 130 36. Obliczanie odległości między lokalizacjami wskazywanymi przez system GPS .... 132 37. Porównywanie faktur i wpłat ...................................................................................... 136 38. Wyszukiwanie błędów transpozycji ........................................................................... 139 39. Naliczanie podatku progresywnego .......................................................................... 143 40. Rangi ................................................................................................................................ 146 Rozdział 6. Aplikacje sieciowe .................................................................................................. 149 41. Kopiowanie stron internetowych do tabeli ............................................................... 149 42. Prezentowanie danych z wykorzystaniem skalowalnej grafiki wektorowej ...... 157 43. Wzbogacanie aplikacji sieciowych o narzędzia do nawigacji ................................ 164 44. Definiowanie połączenia między systemem MySQL a programem Access ........ 170 45. Przetwarzanie dzienników serwerów sieciowych ................................................... 174 46. Przechowywanie obrazów w bazie danych .............................................................. 181 47. Atak SQL injection ......................................................................................................... 185 48. Zapobieganie atakowi SQL injection .......................................................................... 191 Rozdział 7. Porządkowanie danych .......................................................................................... 197 49. Śledzenie rzadko zmieniających się wartości ........................................................... 197 50. Łączenie tabel zawierających różne dane .................................................................. 200 51. Wyświetlanie wierszy w charakterze kolumn .......................................................... 202 52. Wyświetlanie kolumn w charakterze wierszy .......................................................... 205 53. Usuwanie niespójnych rekordów ............................................................................... 207 54. Denormalizowanie tabel ............................................................................................... 209 55. Importowanie danych innych użytkowników ......................................................... 211 56. Zabawa w swatanie ....................................................................................................... 213 57. Generowanie unikalnych numerów sekwencyjnych ............................................... 215 6 | Spis treści Rozdział 8. Przechowywanie małych ilości danych ................................................................221 58. Przechowywanie parametrów w bazie danych ........................................................ 221 59. Definiowanie osobnych parametrów dla poszczególnych użytkowników ......... 227 60. Lista parametrów ........................................................................................................... 231 61. Bezpieczeństwo oparte na wierszach ......................................................................... 232 62. Wykonywanie kwerend bez wykorzystywania tabel .............................................. 235 63. Tworzenie wierszy bez udziału tabel ......................................................................... 237 Rozdział 9. Blokowanie i wydajność .........................................................................................241 64. Określanie poziomu izolacji ......................................................................................... 241 65. Blokowanie pesymistyczne .......................................................................................... 246 66. Blokowanie optymistyczne .......................................................................................... 248 67. Niejawne blokowanie wewnątrz transakcji ............................................................... 251 68. Obsługa powtarzanych operacji .................................................................................. 252 69. Wykonywanie funkcji w bazie danych ...................................................................... 257 70. Łączenie kwerend .......................................................................................................... 259 71. Pobieranie dużej liczby wierszy .................................................................................. 261 72. Pobieranie podzbioru uzyskanych rezultatów ......................................................... 264 73. Przechowywanie plików w bazie danych ................................................................. 266 74. Porównywanie i synchronizowanie tabel .................................................................. 270 75. Minimalizowanie obciążenia łącz dla zbyt wielu złączeń ...................................... 274 76. Kompresowanie w celu uniknięcia typu danych LOB ............................................ 278 Rozdział 10. Raporty ...................................................................................................................281 77. Uzupełnianie brakujących wartości w tabeli przestawnej ...................................... 281 78. Podział na zakresy ......................................................................................................... 286 79. Jednoznaczne identyfikowanie aktualizacji .............................................................. 290 80. Sześć stopni od Kevina Bacona .................................................................................... 295 81. Tabele decyzyjne ............................................................................................................ 298 82. Generowanie sekwencyjnych lub brakujących danych ........................................... 302 83. Wyszukiwanie n pierwszych wierszy w grupach .................................................... 309 84. Przechowywanie list wartości oddzielonych przecinkami w kolumnach ........... 312 85. Analizowanie prostych drzewek ................................................................................. 314 86. Definiowanie kolejek w bazie danych ........................................................................ 318 87. Tworzenie kalendarza ................................................................................................... 319 88. Testowanie dwóch wartości za pomocą podzapytania ........................................... 322 89. Wybieranie trzech możliwości spośród pięciu ......................................................... 324 Spis treści | 7 Rozdział 11. Użytkownicy i administracja ................................................................................ 329 90. Implementowanie kont na poziomie aplikacji ......................................................... 329 91. Eksportowanie i importowanie definicji tabel .......................................................... 336 92. Wdrażanie aplikacji ....................................................................................................... 345 93. Automatyczne tworzenie kont użytkowników ........................................................ 350 94. Tworzenie kont użytkowników i administratorów ................................................. 352 95. Automatyczne aktualizacje .......................................................................................... 355 96. Tworzenie dziennika zdarzeń ..................................................................................... 358 Rozdział 12. Szerszy dostęp ...................................................................................................... 363 97. Anonimowe konta ......................................................................................................... 364 98. Wyszukiwanie i przerywanie długo wykonywanych kwerend ............................... 366 99. Zarządzanie przestrzenią dyskową ............................................................................ 370 100. Uruchamianie kwerend za pośrednictwem stron internetowych ......................... 374 Skorowidz .................................................................................................................................. 381 8 | Spis treści Modyfikowanie schematu bez naruszania kwerend SPOSÓB 7. R O Z D Z I A Ł D R U G I Polecenia JOIN, UNION oraz VIEW Sposoby 7. – 14. W celu powiązania ze sobą dwóch tabel wykorzystać możemy złączenie. Często wynika ono z zastosowania odwołania do klucza zewnętrznego. Załóżmy na przykład, iż dysponujemy tabelą pracownik, zawierającą kolumnę z numerami id poszczególnych wydziałów dla każdego z pracowników. Aby wyświetlić nazwy wydziałów przypisanych pracow- nikom, możemy posłużyć się poleceniem JOIN: SELECT pracownik.nazwa, wydzial.nazwa FROM pracownik JOIN wydzial ON (pracownik.wydzial=wydzial.id) Domyślnie wybierane jest złączenie INNER JOIN. Istnieją również inne rodzaje złączeń: LEFT OUTER JOIN, FULL OUTER JOIN oraz CROSS JOIN. Wszystkie opisano w niniej- szym rozdziale. Dwie tabele można połączyć również za pomocą polecenia UNION. W przeciwieństwie do polecenia JOIN powoduje ono połączenie wierszy w obu tabelach — otrzymujemy jeden wynik. Tabele muszą mieć taką samą liczbę kolumn, a odpowiadające sobie ko- lumny muszą być tego samego typu. Za pomocą polecenia VIEW możemy nadawać kwerendom nazwy. Wyrażenie SELECT (wykorzystujące polecenie JOIN lub UNION) da się zapisać jako widok danych. W miarę możliwości system będzie traktował widok jak tabelę podstawową, umożliwiając prze- prowadzanie na niej operacji SELECT, JOIN, UPDATE, DELETE oraz INSERT (z pewnymi ograniczeniami). S P O S Ó B 7. Modyfikowanie schematu bez naruszania kwerend Jeśli zmieniają się wymagania dotyczące oprogramowania i konieczne jest zaprojektowanie nowej bazy danych, nie trzeba od razu pozbywać się całego wcześniej napisanego kodu. Dzięki zastąpieniu nieistniejących tabel widokami danych istniejące kwerendy nadal będą działać. Prędzej czy później będziecie musieli wprowadzić gruntowne zmiany w strukturze bazy danych. Dzięki zastosowaniu odpowiednich rozwiązań można to osiągnąć, nie pozby- wając się oryginalnego kodu. Polecenia JOIN, UNION oraz VIEW | 45 SPOSÓB 7. Modyfikowanie schematu bez naruszania kwerend Przykładowo powiedzmy, że firma prowadzi rejestr wyposażenia w formie przedsta- wionej w tabeli 2.1. Tabela 2.1. Tabela „sprzet” Numer 50430 50431 Opis Komputer PC Monitor 19 cali DataZakupu 2004-07-02 2004-07-02 Załóżmy teraz, że firma otwiera nowe biuro i konieczne jest prowadzenie rejestru dla obu filii z osobna. Czy należy zrobić kopię aplikacji i bazy danych, czy też zmienić jej strukturę? Kopiowanie bazy danych Skopiowanie bazy danych oraz aplikacji wydaje się kuszącym rozwiązaniem. Niestety, mimo iż pomaga to uporać się z doraźnymi problemami, na dłuższą metę przyniesie więcej szkody niż pożytku. Konieczne będzie obsługiwanie dwóch aplikacji, zakupienie dodatkowego sprzętu i tworzenie dwóch baz danych, których ewentualne połączenie będzie trudnym zadaniem. Sytuacja pogorszy się, jeśli otwarta zostanie kolejna filia. Zmiana tabeli SQL daje nam do dyspozycji polecenie pozwalające na dodanie kolumny przy jednocze- snym zachowaniu istniejących danych (możemy również zmieniać nazwy pól i usuwać zbędne elementy tabel): ALTER TABLE sprzet ADD COLUMN biuro VARCHAR(20); UPDATE sprzet SET biuro = SiedzibaGłówna ; W ten sposób dodajemy nową kolumnę i przypisujemy wszystkie wiersze do istniejącego biura (w praktyce sytuacja wyglądałaby nieco inaczej — część wyposażenia na ogół prze- wożona jest do nowej placówki). Nazwa pierwszego biura zmieniona zostaje na „Siedzi- bę Główną”. Możemy teraz rozpocząć tworzenie listy wyposażenia dla nowego biura. Wcześniej jednak należy sprawdzić poprawność kwerend opartych na zmienionej tabeli. Wyrażenia INSERT, które nie wskazują określonych kolumn, nie zadziałają poprawnie. Jeśli zatem polecenie INSERT miało postać: INSERT INTO sprzet VALUES (50322, Drukarka Laserowa ,DATE 2004-07-02 ); pojawi się komunikat o błędzie. Jeśli jednak kwerenda była następująca: INSERT INTO sprzet (Numer,Opis,DataZakupu) VALUES (50322, Drukarka Laserowa ,DATE 2004-07-02 ); zostanie ona przeprowadzona poprawnie, a w kolumnie biuro znajdzie się wartość NULL. 46 | Polecenia JOIN, UNION oraz VIEW Filtrowanie wierszy i kolumn SPOSÓB 8. Istnieje spora szansa, iż kwerendy będą działać pomimo zmian wprowadzonych w tabeli, ale zwrócone wyniki odnosić się będą do obu filii, nawet jeśli przetwarzane dane doty- czyć mają tylko jednej z nich. Zastępowanie tabeli widokiem danych Alternatywnym rozwiązaniem jest skopiowanie danych do nowej tabeli i zastąpienie ist- niejącej widokiem danych: CREATE TABLE SprzetOgolem (Numer INTEGER PRIMARY KEY , Biuro VARCHAR(20) DEFAULT SiedzibaGłówna , Opis VARCHAR(100) , DataZakupu DATE ); INSERT INTO SprzetOgolem SELECT Numer, SiedzibaGłówna , Opis, DataZakupu FROM sprzet; Nowa tabela zawiera takie same dane — cały sprzęt po raz kolejny przypisaliśmy do starego biura. W razie potrzeby (przeniesienia części wyposażenia do nowej placówki) należy zmienić odpowiednie wiersze. Możemy teraz usunąć starą tabelę i zastąpić ją widokiem danych: DROP TABLE sprzet; CREATE VIEW sprzet AS SELECT Numer, Opis, DataZakupu FROM SprzetOgolem WHERE biuro= SiedzibaGłówna ; Dzięki temu wszystkie wcześniej utworzone kwerendy będą działać poprawnie — po- nieważ odwołują się one do nazwy sprzet i nie ma znaczenia, czy określa ona tabelę, czy widok danych. Kierownik pierwszego biura będzie mógł nadal korzystać z tej samej aplikacji i danych dotyczących sprzętu pozostawionego w jego placówce, z możliwością wykonywania na nich operacji INSERT oraz UPDATE. Możemy nawet przyznać mu upraw- nienia ograniczające wykonywane operacje do danych związanych z jego filią. Czeka nas jednak jeszcze nieco dodatkowej pracy. Widok danych sprzet przechowywać możemy lokalnie dla każdego konta z osobna. Dzięki temu jego zawartość będzie inna dla każdego z użytkowników. Więcej informacji na ten temat znaleźć można w sposobie 59. S P O S Ó B 8. Filtrowanie wierszy i kolumn Nie warto pobierać od razu całej tabeli. Można korzystać z filtrowania wierszy i kolumn w celu zmniejszenia przepływu danych w systemie, Niektórzy programiści starają się za wszelką cenę unikać baz danych. Opanowują poje- dyncze wyrażenie SQL i wykorzystują je na okrągło, bez względu na okoliczności. Do szczęścia potrzeba im jedynie polecenia SELECT * FROM t. Po prostu wczytują całą tabelę i traktują ją jak gigantyczną tablicę. Nie ma potrzeby opanowywania całego języka SQL prawda? Problem w tym, że takie podejście jest nieefektywne. Polecenia JOIN, UNION oraz VIEW | 47 SPOSÓB 8. Filtrowanie wierszy i kolumn Załóżmy, że obsługujemy stronę internetową, której poszczególne fragmenty przecho- wywane są w bazie danych. Ułatwia to zarządzanie jej zawartością i kontrolę wersji, ale obejrzenie każdej ze stron wymaga pobrania danych z bazy. Sama tabela ma dwa pola: nazwaStrony i zawartosc. Jak najefektywniej rozwiązać ten problem w języku Perl? Nazwa poszukiwanej strony przechowywana jest w zmiennej $p: my $sql = SELECT nazwaStrony, zawartosc FROM strona ; my $sth = $dbh- prepare($sql); my $rsh = $sth- execute(); while (my $row = $sth- fetchrow_hashref() ) { print $row- {zawartosc} if ($row- {nazwaStrony} eq $p); } Przedstawiony kod cechuje liniowy spadek wydajności. W miarę dodawania kolejnych stron zwiększa się przesył danych między umieszczoną na serwerze bazą danych a pro- gramem. Konieczne jest przeprowadzenie filtrowania. Podczas pracy w SQL należy filtrować dane w celu uzyskania wymaganych informacji. Poniższy kod jest znacznie lepszy, choć nadal ma pewne wady: my $sql = SELECT nazwaStrony, zawartosc FROM strona WHERE nazwaStrony = .$p. ; my $sth = $dbh- prepare($sql); my $rsh = $sth- execute(); my $row =$sth- fetchrow_hashref(); print $row- {zawartosc} if $row; Możliwe, iż zmienna $p przybierze nieoczekiwaną wartość. Przykładowo zamiast index. html wpisana zostanie nazwa index html. W takim przypadku kwerenda nie zadzia- łałaby z powodu błędu składni. Zignorowanie tego problemu grozi nie tylko błędami składni. Umożliwia również wła- manie do bazy danych za pomocą ataku SQL injection [Sposób 48.]. Przed wspomnianym atakiem można się zabezpieczyć, stosując symbole zastępcze, określane również jako zmienne wiązane lub parametry kwerendy. Zwykle wprowadza się je, umieszczając znak ? w miejscu, w którym pojawić się ma zawartość zmiennej, a na- stępnie przesyłając zmienną jako parametr wywołania execute API. Przedstawiony kod przybiera zatem postać: my $sql = SELECT nazwaStrony, zawartosc FROM strona WHERE nazwaStrony = ? ; my $sth = $dbh- prepare($sql); my $rsh = $sth- execute($p); my $row =$sth- fetchrow_hashref(); print $row- {zawartosc} if $row; W ten sposób możemy obsługiwać większą liczbę parametrów; wystarczy wprowadzać je w kolejności, w jakiej ? pojawia się w ciągu znaków $sql. Dodatkową zaletą tego rozwiązania jest buforowanie kwerendy. Za każdym razem, kiedy uruchamiamy nasz kod, w bazie danych wykonywane jest to samo polecenie — bez względu na wyszukiwaną stronę. Strona ta przekazywana jest oddzielnie jako zmienna odpowiadająca symbolowi zastępczemu. 48 | Polecenia JOIN, UNION oraz VIEW Filtrowanie wierszy i kolumn SPOSÓB 8. Filtrowanie danych na serwerze zapewnia szybszy czas odpowiedzi i zmniejsza wyma- gania dotyczące przepustowości pasma łączącego bazę danych z programem. Jest to efektywniejsze rozwiązanie również z innych powodów. Jednym z nich jest indeksowanie [Sposób 9.]. Wiele serwerów baz danych przechowuje ostatnio wykonywane kwerendy w analizato- rach składni. W buforze może nawet zostać umieszczony plan kwerendy. Jeśli kwerenda jest zawsze taka sama, mechanizm SQL nie musi za każdym razem przygotowywać jej do uruchomienia. Symbole zastępcze nie są wyłącznie domeną języka Perl. Języki opisane w podrozdziale „Nawiązywanie połączenia między aplikacją a bazą danych SQL” [Sposób 2.] posiadają podobne rozwiązania. Oto przykłady umieszczania zmiennej mojParametr w ramach symbolu zastępczego. Perl my $sql = SELECT kolumna FROM tabela WHERE kolumna = ? ; my $sth = $dbh- prepare($sql,$mojParametr); Java PreparedStatement sql = con.prepareStatement( SELECT kolumna FROM tabela WHERE kolumna = ? ); sql.setString(1, mojParametr); ResultSet cursor = sql.executeQuery(); Ruby sql = db.prepare( SELECT kolumna FROM tabela WHERE kolumna = ? ); sql.bind_param(1,mojParametr); C# W C# w charakterze symbolu zastępczego nie stosujemy znaku ?. W zamian nadajemy mu nazwę i umieszczamy przed nim znak @: SqlCommand cmd = new SqlCommand( SELECT kolumna FROM tabela WHERE kolumna = @param1 ); cmd.Parameters.Add( @param1 , mojParametr); PHP W PHP stosowane symbole zastępcze zależą od bibliotek wykorzystywanych przy na- wiązywaniu połączenia z bazą danych. Do dyspozycji mamy biblioteki owijające, w tym ADOdb (http://adodb.sourceforge.net), które mogą nam znacznie ułatwić życie. Wyrażenie wykorzystujące ADOdb ma następującą postać: $DB- Execute( SELECT kolumna FROM tabela WHERE kolumna = ? , array(mojParametr)); Polecenia JOIN, UNION oraz VIEW | 49 SPOSÓB 9. Filtrowanie kolumn indeksowanych Ten sam efekt bez posługiwania się ADOdb można osiągnąć za pomocą specjalistycz- nych funkcji, takich jak mysql_stmt_bind_param lub oci_bind_by_name. S P O S Ó B 9. Filtrowanie kolumn indeksowanych Filtrowanie kwerend zwiększa wydajność. Dodatkową poprawę w tym zakresie zapewnia wykorzystanie kolumn indeksowanych. Kwerendy mogą zwracać wszystkie wiersze i wszystkie kolumny zawarte w tabelach. Ale co zrobić, jeśli chcemy uzyskać dostęp jedynie do kilku kolumn lub wierszy? Przesyłanie zbędnych danych jest marnotrawstwem zasobów systemowych. Tworząc zapytania uwzględniające jedynie potrzebne nam informacje, odfiltrowujemy zbędne kolumny i wier- sze. Aby przeprowadzić filtrowanie kolumn, należy upewnić się, czy zapytanie nie obejmuje niepotrzebnych fragmentów tabeli (na przykład nie zawiera znaku * w wyrażeniu SELECT). Wiersze filtrować można za pomocą warunku WHERE, jak również innych wyrażeń (na przykład HAVING). Zdefiniowanie klucza głównego tworzy indeks powiązanych z nim kolumn. Dzięki temu wyszukiwanie danych jest znacznie szybsze niż w przypadku braku indeksu. Indeks jest wykorzystywany do zapewnienia unikalności klucza, a unikalność ta jest warunkiem jego ważności. Indeks daje również dodatkowe korzyści przy stosowaniu poleceń JOIN. Filtrowanie danych nieposiadających indeksu może powodować znaczne problemy zwią- zane z wydajnością. Indeks skraca czas wyszukiwania i może być wykorzystany przez optymalizatory zapytań podczas wstępnego filtrowania danych (w przypadku braku in- deksu jest ono oparte na przeszukiwaniu całej tabeli danych). W zależności od przetwarza- nej kwerendy program optymalizujący może nawet wykorzystać indeks do całej operacji. Jeśli do uzyskania wyników potrzebny jest jedynie indeks (nie ma konieczności analizowania bazy danych), indeks taki nazywamy indeksem pokrywającym — „pokrywa” on całość kwerendy. Rozważmy teraz bazę danych zawierającą strony. Załóżmy, że w tabeli przechowywane są nazwy i zawartość stron, jak również ich poprzednie wersje, zapisywane w celu kon- troli wersji (patrz tabela 2.2). Tabela 2.2. Tabela „strona” zawartosc nazwaStrony nazwaUzytkownika ostatniaModyfikacja numerWersji b hello /b index.html h1 Hia /h1 index.html p page2 /p p2.html gordon gordon andrew h1 Indeks /h1 contents.html gordon 2006-03-01 2006-10-10 2006-02-05 2006-02-05 1 2 1 1 Możemy teraz obsługiwać zmiany wprowadzane na stronie przez różnych użytkowników i prowadzić ich rejestr. Poniższa kwerenda umożliwia pobranie z bazy bieżącej wersji strony index.html: 50 | Polecenia JOIN, UNION oraz VIEW Filtrowanie kolumn indeksowanych SPOSÓB 9. SELECT nazwaStrony, zawartosc FROM strona x WHERE nazwaStrony = index.html AND numerWersji = ( SELECT MAX (y.numerWersji) FROM strona y WHERE y.nazwaStrony = index.html ); Zaprezentowana kwerenda jest dość efektywna. Indeks przypisany kolumnie nazwa- Strony umożliwia szybkie wyszukanie nazwy index.html — bez konieczności analizowa- nia wszystkich wierszy. Tabela posiada klucz główny(nazwaStrony,numerWersji), który — mimo iż nie opiera się wyłącznie na kolumnie nazwaStrony — powinien działać jeszcze lepiej, ponieważ zawiera wszystkie dane objęte kwerendą. System może wykorzystywać tego typu złożone indeksy, o ile jest w stanie wyszukać indeksowane dane przez odczytywanie zawartość tabeli od lewej do prawej, bez korzystania z niepo- trzebnych kolumn. Proces ten określany jest nazwą częściowego dopasowywania indeksów. Jeśli tabela posiada indeks (numerWersji,nazwaStrony,ostatniaModyfikacja), uzyskanie indeksu dla kolumny numerWersji nie stanowi problemu. Jeśli jednak po- trzebny jest indeks kolumny nazwaStrony, rozwiązanie to okaże się nieefektywne, po- nieważ na początku listy wymieniona jest kolumna numerWersji. Warto zatem do- kładnie przemyśleć kolejność elementów tworzonego indeksu złożonego lub złożonego klucza podstawowego. Każda z często wykorzystywanych kolumn powinna pojawić się jako pierwsza przynajmniej raz. Załóżmy, że do wyboru mamy indeksy (nazwaStrony,numerWersji) i (numerWer- sji,nazwaStrony). Wiemy, że w kolumnie nazwaStrony znajdują się tysiące róż- nych rekordów, a w kolumnie numerWersji — jedynie kilka wersji strony. W takiej sytuacji zdecydowanie należy wybrać pierwszą możliwość. Umieszczanie dyskryminato- ra niskiej rangi na pierwszym miejscu w indeksie nie jest najlepszym pomysłem. Jeszcze gorszym rozwiązaniem jest tworzenie osobnego indeksu dla kolumny numerWersji. Zastosowanie indeksów przyspiesza wykonywanie kwerend wykorzystujących warunki złączeń i wyrażenia WHERE zawierające znaki = oraz . Warto zatem rozważyć dodanie indeksów do kolumn uwzględnionych w tego typu zapytaniach. Przeanalizujmy poniż- szą kwerendę dotyczącą tabeli t: SELECT z FROM t WHERE x = 6 AND y 7; Przedstawiona kwerenda przeprowadza filtrowanie w oparciu o kolumny x i y. Jeśli z uwa- gi na jej częste wykorzystywanie chcielibyśmy poprawić jej wydajność, najlepszym roz- wiązaniem byłoby opracowanie odpowiednich indeksów. Indeksy x i y stworzyć można w następujący sposób: CREATE INDEX ind_1 ON t (x); CREATE INDEX ind_2 ON t (y); Polecenia JOIN, UNION oraz VIEW | 51 SPOSÓB 10. Konwertowanie podzapytań na wyrażenia JOIN Utworzenie dwóch indeksów nie jest optymalne, jeśli w całej aplikacji wykorzystywać będziemy jedynie powyższe zapytanie wykorzystujące jednocześnie i kolumnę x i ko- lumnę y. W takim przypadku idealnym rozwiązaniem jest przypisanie x roli pierwszego dyskryminatora i umieszczenie y po nim: CREATE INDEX ind_1 ON t (x,y); Oczywiście optymalizator może zignorować utworzone przez nas indeksy, jeśli uzna, że takie rozwiązanie przyspieszy wykonywanie kwerendy. Na ogół jednak indeksy zwięk- szają znacznie efektywność tworzonego kodu. S P O S Ó B 10. Konwertowanie podzapytań na wyrażenia JOIN Czasami chcemy pobrać dane z jednej tabeli, wykorzystać je do przetworzenia danych w innej, a uzyskane rezultaty wykorzystać w jeszcze innej tabeli. Kuszącym rozwiązaniem wydaje się utworzenie trzech osobnych zapytań, jednak najlepiej wykonać wszystkie wymienione operacje w ramach jednego wyrażenia SQL. Rozważmy bazę danych zawierającą stanowiska pracowników. Do każdego stanowiska przypisana jest ranga, od której zależy miesięczna płaca. Całość przedstawiają tabele 2.3, 2.4 i 2.5. Tabela 2.3. Tabela „etaty” Pracownik Stanowisko Grzegorz Nowak Wykładowca Andrzej Kowalski Nauczyciel Marcin Maliniak Technik Tabela 2.4. Tabela „rangi” Stanowisko Wykładowca Nauczyciel Technik Tabela 2.5. Tabela „pensje” Ranga WYK1 WYK2 TECH1 TECH2 Ranga WYK1 WYK2 TECH1 Placa 2000,00 3000,00 5000,00 6000,00 Wyznaczenie wysokości pensji Andrzeja Kowalskiego wymaga wykonania trzech czynno- ści. Najpierw należy określić jego stanowisko: 52 | Polecenia JOIN, UNION oraz VIEW Konwertowanie podzapytań na wyrażenia JOIN SPOSÓB 10. mysql SELECT stanowisko FROM etaty WHERE pracownik = Andrzej Kowalski ; +------------+ | stanowisko | +------------+ | Nauczyciel | +------------+ Następnie musimy wyznaczyć rangę przypisaną do danego stanowiska: mysql SELECT ranga FROM rangi WHERE stanowisko = Nauczyciel ; +-------+ | ranga | +-------+ | WYK2 | +-------+ Na koniec należy odszukać wysokość pensji odpowiadającą randze WYK2: mysql SELECT placa FROM pensje WHERE ranga = WYK2 ; +---------+ | placa | +---------+ | 3000,00 | +---------+ Nie jest to zbyt efektywne rozwiązanie, ponieważ wymaga przesłania do bazy trzech osobnych zapytań i przetwarzania uzyskanych w międzyczasie wyników. Jeśli w trakcie całego procesu zawartość bazy ulegnie zmianie, moglibyśmy otrzymać niepoprawną odpowiedź lub nawet komunikat o błędzie. Łączenie kwerend bywa stresujące. Zestre- sowani programiści często używają podzapytań: mysql SELECT placa FROM pensje WHERE ranga = - (SELECT ranga FROM rangi WHERE stanowisko = - (SELECT stanowisko FROM etaty WHERE pracownik = Andrzej Kowalski )); +---------+ | placa | +---------+ | 3000,00 | +---------+ Przedstawiony kod eliminuje pewne problemy dzięki sprowadzeniu całej operacji do jednej kwerendy, podzapytania zmniejszają jednak szybkość wykonania polecenia. Jeśli wyrażenia zawarte w podzapytaniach nie zawierają funkcji zagregowanych (takich jak MAX()), najprawdopodobniej w ogóle nie ma konieczności używania podzapytań. W za- mian wystarczy zastosować polecenie JOIN. Aby przekształcić kwerendę opartą na podza- pytaniach w wyrażenie JOIN, należy wykonać następujące czynności: 1. Oznaczamy wszystkie kolumny nazwą tabeli, w której są one zawarte. 2. Jeśli odwołanie do tej samej tabeli znajduje się w dwóch różnych wyrażeniach FROM, należy zastosować nazwy zastępcze (w tym przykładzie nie jest to konieczne). 3. Wszystkie warunki FROM łączymy razem, tworząc pojedyncze wyrażenie FROM. 4. Usuwamy wszystkie wystąpienia (SELECT. 5. Zamieniamy drugie słowo kluczowe WHERE na AND. Polecenia JOIN, UNION oraz VIEW | 53 SPOSÓB 10. Konwertowanie podzapytań na wyrażenia JOIN Oto etap pośredni: SELECT pensje.placa FROM pensje, rangi, etaty WHERE pensje.ranga= (SELECT rangi.ranga from rangi AND rangi.stanowisko= (SELECT etaty.stanowisko from etaty AND etaty.pracownik = Andrzej Kowalski )) Końcowa postać przedstawia się następująco: SELECT placa FROM pensje, rangi, etaty WHERE pensje.ranga=rangi.ranga AND rangi.stanowisko=etaty.stanowisko AND etaty.pracownik = Andrzej Kowalski ; Innym rozwiązaniem jest zamiana warunków zdefiniowanych wewnątrz podzapytań na warunki JOIN ON: SELECT placa FROM pensje JOIN rangi ON (pensje.ranga=rangi.ranga) JOIN etaty ON (rangi.stanowisko=etaty.stanowisko) WHERE etaty.pracownik = Andrzej Kowalski ; Wyszukiwanie danych spoza bazy Wielu programistów potrafi zastępować podzapytania wyrażeniami JOIN, prawdziwym wyzwaniem są natomiast operacje polegające na wyszukiwaniu nieistniejących danych. W jaki sposób można na przykład stwierdzić, czy w bazie znajdują się rangi nieprzypi- sane do żadnego stanowiska? Kosztowne obliczeniowo rozwiązanie zakłada wyszukanie wszystkich rang w tabeli pensje, a następnie sprawdzenie każdej z nich w tabeli rangi. Nie trzeba chyba rozwodzić się nad wydajnością tej techniki. Alternatywną metodą jest wykorzystanie podzapytania zawierającego warunek NOT IN, to jednak również nie oszczędza zasobów systemowych: mysql SELECT pensje.ranga FROM pensje - WHERE ranga NOT IN (SELECT ranga FROM rangi); +-------+ | ranga | +-------+ | TECH2 | +-------+ Spadki wydajności wynikać mogą z konieczności utworzenia tymczasowej tabeli po- średniej, koniecznej do wykonania podzapytania. Wspomniana tabela wykorzystywana jest następnie do przeprowadzenia kwerendy zewnętrznej. Podczas tworzenia tabeli tym- czasowej nie zostaną wykorzystane indeksy przypisane do tabeli pensje. W rezultacie podczas wykonywania operacji przeszukana będzie cała tabela tymczasowa. Jest to odwrotność zaprezentowanego wcześniej problemu osadzonych podzapytań. W tym przypadku poszukujemy niepasujących wierszy w tabelach. Zastosowanie wcześniejszej techniki z użyciem operatora != zamiast = spowoduje jedynie wielki bałagan i nie przy- bliży nas do rozwiązania. W zamian należy wykorzystać wyrażenie OUTER JOIN. Za 54 | Polecenia JOIN, UNION oraz VIEW Konwertowanie zagregowanych podzapytań na wyrażenia JOIN SPOSÓB 11. jego pomocą łączymy wszystkie tabele zawarte we frazie FROM. Poszukujemy elemen- tów tabeli pensje niewymienionych w tabeli rangi. Dzięki zastosowaniu wyrażenia OUTER JOIN niepasujące wiersze będą miały wartość NULL w polu rangi.ranga: mysql SELECT pensje.ranga - FROM pensje LEFT OUTER JOIN rangi ON (pensje.ranga = rangi.ranga) - WHERE rangi.ranga IS NULL; +-------+ | ranga | +-------+ | TECH2 | +-------+ Technikę tę wykorzystać można do eliminowania wyrażeń EXISTS i NOT EXISTS. Wy- eliminowanie podzapytań ułatwia optymalizatorowi wykorzystywanie indeksów. S P O S Ó B 11. Konwertowanie zagregowanych podzapytań na wyrażenia JOIN Podzapytania niezawierające funkcji zagregowanych można zastępować wyrażeniami JOIN oraz OUTER JOIN. A co, jeśli podzapytania zawierają wspomniane funkcje? Niektóre podzapytania łatwo wyeliminować [Sposób 10.], inne przysparzają pod tym względem nieco trudności. Przeanalizujmy tabelę 2.6 zawierającą dane dotyczące za- mówień. Tabela 2.6. Tabela „zamowienia” Klient Krzysiek Krzysiek Krzysiek Wojtek Kiedy 2006-10-10 2006-10-11 2006-10-12 2006-10-10 Ilosc_towaru 5 3 1 7 Załóżmy, że musimy znaleźć dni, w których poszczególni klienci zakupili najwięcej towaru: SELECT klient,kiedy,ilosc_towaru FROM zamowienia o1 WHERE o1.kiedy = ( SELECT MAX(kiedy) FROM zamowienia o2 WHERE o1.klient = o2.klient ); Wykonanie zaprezentowanego powyżej kodu będzie dość powolne, ponieważ wymaga przeszukania wszystkich wierszy tabeli zamowienia. Ponadto stare wersje MySQL nie obsługują podzapytań. W celu ich wyeliminowania możemy posłużyć się warunkiem HAVING oraz złączeniem tabeli z nią samą: SELECT o1.klient,o1.kiedy,o1.ilosc_towaru FROM zamowienia o1 JOIN zamowienia o2 on (o1.klient = o2.klient) GROUP BY o1.klient,o1.kiedy,o1.ilosc_towaru HAVING o1.kiedy = MAX(o2.kiedy) Polecenia JOIN, UNION oraz VIEW | 55 SPOSÓB 12. Upraszczanie skomplikowanych poleceń UPDATE Oto otrzymany wynik: +----------+------------+--------------+ | klient | kiedy | ilosc_towaru | +----------+------------+--------------+ | Wojtek | 2006-10-10 | 7 | | Krzysiek | 2006-10-12 | 1 | +----------+------------+--------------+ 2 rows in set (0.00 sec) Technika ta sprawdza się dla wszystkich funkcji zagregowanych. S P O S Ó B 12. Upraszczanie skomplikowanych poleceń UPDATE Polecenie UPDATE daje możliwość przeprowadzania skomplikowanych obliczeń. Dzięki temu można uniknąć konieczności stosowania kursora lub wykonywania wspomnianych obliczeń poza bazą danych. Przykład polecenia UPDATE przytaczany w wielu książkach opisujących podstawy SQL pre- zentuje prostą operację, polegającą na podniesieniu pensji wszystkich pracowników o 100 zł. UPDATE pracownik SET placa = placa +100; Jest to z pewnością proste wyrażenie — jednak może się ono okazać zbyt proste, by miało jakąkolwiek wartość praktyczną. Załóżmy, iż negocjacje w sprawie płac zakończyły się bardziej złożonymi warunkami, których wprowadzenie wymaga dostępu do innych tabel w bazie. Pracownicy posiadający czyste konto wykroczeń dyscyplinarnych otrzymają 100 zł pod- wyżki, natomiast ci, którzy dopuścili się tylko jednego wykroczenia, będą otrzymywać wypłatę taką samą jak dotąd. Pensja pracowników z dwoma i więcej wykroczeniami na koncie zostanie obniżona o 100 zł. Dane dotyczące pracowników oraz zachowywanej przez nich dyscypliny przechowywane są w tabelach pracownik oraz dyscyplina: mysql SELECT * FROM pracownik; +----+--------+---------+ | id | imie | placa | +----+--------+---------+ | 1 | Janusz | 5000.00 | | 2 | Marcin | 5000.00 | | 3 | Marian | 5000.00 | +----+--------+---------+ mysql SELECT * FROM dyscyplina; +------------+------+ | kiedy | prac | +------------+------+ | 2006-05-20 | 1 | | 2006-05-21 | 1 | | 2006-05-22 | 3 | +------------+------+ Moglibyśmy napisać skomplikowane polecenie UPDATE aktualizujące tabelę pracownik na podstawie odwołań do tabeli dyscyplina, łatwiej jednak będzie podzielić cały proces na dwa etapy. Najpierw przygotujemy widok danych obliczający nowe wartości płac, a na- stępnie wprowadzimy je do bazy za pomocą polecenia UPDATE. 56 | Polecenia JOIN, UNION oraz VIEW Upraszczanie skomplikowanych poleceń UPDATE SPOSÓB 12. Widok danych nowePlace posiada dwie kolumny: klucz podstawowy aktualizowanej tabeli (pracownik) oraz nowe wartości płac. Ich zawartość możemy obejrzeć przed wy- konaniem kwerendy UPDATE. Widok zawierający nowe płace dla każdego z pracowników definiujemy w następujący sposób: mysql CREATE VIEW nowePlace AS - SELECT id, CASE WHEN COUNT (prac) = 0 THEN placa +100 - WHEN COUNT (prac) 1 THEN placa -100 - ELSE placa - END AS v - FROM pracownik LEFT JOIN dyscyplina ON (id=prac) - GROUP BY id,placa; Query OK, 0 rows affected (0.00 sec) mysql SELECT * FROM nowePlace; +----+---------+ | id | v | +----+---------+ | 1 | 4900.00 | | 2 | 5100.00 | | 3 | 5000.00 | +----+---------+ Widok danych można obejrzeć w celu przeanalizowania nowych płac, jednak tabela pracownik nie została jeszcze zaktualizowana. Dobrym pomysłem byłoby zlecenie in- nemu pracownikowi sprawdzenia nowych wartości przed wprowadzeniem ostatecznych zmian. Nowe wartości płac umieszczamy w bazie za pomocą pojedynczego wyrażenia UPDATE. Jest to ważne, ponieważ musimy mieć pewność, że sprawdzone dane zawarte w widoku zgadzają się z danymi wprowadzanymi do bazy. mysql UPDATE pracownik - SET placa = (SELECT v FROM nowePlace - WHERE nowePlace.id=pracownik.id) - WHERE id IN (SELECT id FROM nowePlace); Query OK, 2 rows affected (0.01 sec) Rows matched: 3 Changed: 2 Warnings: 0 mysql SELECT * FROM pracownik; +----+--------+---------+ | id | imie | placa | +----+--------+---------+ | 1 | Janusz | 4900.00 | | 2 | Marcin | 5100.00 | | 3 | Marian | 5000.00 | +----+--------+---------+ 3 rows in set (0.00 sec) Wykorzystywanie kursora Podczas wykonywania kilku operacji podobnych do przedstawionej powyżej kuszące wydaje się wykorzystanie kursora opisanego w sposobie 2. Zaletą polecenia UPDATE jest Polecenia JOIN, UNION oraz VIEW | 57 SPOSÓB 13. Dopasowywanie złączeń do relacji jego zwięzłość, szybkość i niepodzielność. Przejrzystość kodu zależy od stylu progra- mowania, do którego jesteście przyzwyczajeni. Język SQL stosowany bez kursorów zna- komicie pasuje do stylu deklaratywnego. Korzystanie z widoków danych Operację aktualizacji można przeprowadzić bez wcześniejszego tworzenia widoku danych, utrudnia to jednak wyświetlenie podglądu końcowych wyników. Inną wadą skompliko- wanych wyrażeń UPDATE jest niewygodny proces debugowania: każdy test polecenia powoduje zmianę zawartości bazy danych. Przed przeprowadzeniem kolejnej próby ko- nieczne jest przywrócenie jej do poprzedniego stanu. Dzięki zastosowaniu widoku da- nych możemy sprawdzić rezultaty kwerendy bez wprowadzania zmian do bazy. S P O S Ó B 13. Dopasowywanie złączeń do relacji Kiedy relacja między tabelami jest opcjonalna, najlepiej zastosować polecenie OUTER JOIN. Jeśli musimy je zastosować w trakcie wprowadzania wielu zmian, czasem konieczna jest zamiana wszystkich wyrażeń INNER JOIN na wyrażenia OUTER JOIN. Wyróżniamy dwa wzorce złączeń: łańcuch i gwiazdę (patrz rysunki 2.1 i 2.2). Ich opis przedstawiony jest poniżej. Rysunek 2.1. Wzorzec łańcucha Rysunek 2.2. Wzorzec gwiazdy 58 | Polecenia JOIN, UNION oraz VIEW Dopasowywanie złączeń do relacji SPOSÓB 13. Łańcuch W przedstawionym przykładzie łańcucha znajdują się dwa odwołania. Odwołanie z ta- beli podroz (patrz tabela 2.7) do tabeli budzet (patrz tabela 2.8) jest opcjonalne — użytkownicy mogą wstawiać w polu budzet w tabeli podroz wartość NULL. Niezbędne jest połączenie między tabelami budzet i personel (patrz tabela 2.9) — każdy wiersz w pierwszej z nich musi posiadać odpowiadającą mu wartość w polu dysponent. Kweren- dy oparte na tabelach podroz oraz budzet wykorzystują zatem wyrażenie OUTER JOIN, natomiast kwerendy związane z tabelami budzet i personel — wyrażenie INNER JOIN. Tabela 2.7. Tabela „podroz” podrozID POD01 POD02 Tabela 2.8. Tabela „budzet” opis Sycylia Egipt budzet NULL CT22 budzetID CT22 opis dysponent (NOT NULL) Mesa oficerska ST02 Tabela 2.9. Tabela „personel” personelID ST01 ST02 imie Grzegorz Janusz ranga Kapitan Porucznik Aby wyświetlić listę wszystkich podróży wraz ze szczegółami dotyczącymi ich budżetu, musimy zastosować wyrażenie LEFT OUTER JOIN. W ten sposób uwzględnione zostaną również podróże nieposiadające przypisanego budżetu: mysql SELECT podrozID, podroz.opis, budzet.opis - FROM podroz LEFT OUTER JOIN budzet ON (podroz.budzet=budzet.budzetID); +----------+---------+----------------+ | podrozID | opis | opis | +----------+---------+----------------+ | POD01 | Sycylia | NULL | | POD02 | Egipt | Mesa oficerska | +----------+---------+----------------+ Możemy również zmienić frazę FROM, na przykład: FROM budzet RIGHT OUTER JOIN podroz ON podroz.budzet=budzet.budzetId. Aby uwzględnić imię dysponenta budżetu, musimy dołączyć również tabelę personel. Wartość NULL dla pola dysponent jest niedozwolona, co może prowadzić do wniosku, iż wyrażenie INNER JOIN da poprawne rezultaty. Niestety, jest to błędny wniosek: Polecenia JOIN, UNION oraz VIEW | 59 SPOSÓB 13. Dopasowywanie złączeń do relacji mysql SELECT podrozID, podroz.opis,budzet.opis, imie - FROM podroz LEFT OUTER JOIN budzet ON(podroz.budzet=budzet.budzetID) - INNER JOIN personel ON (dysponent=personelID); +----------+---------+----------------+--------+ | podrozID | opis | opis | imie | +----------+---------+----------------+--------+ | POD02 | Egipt | Mesa oficerska | Janusz | +----------+---------+----------------+--------+ Łańcuch złączeń obliczany jest od lewej do prawej, przez co rezultat polecenia LEFT JOIN z pierwszej kwerendy jest dołączany do tabeli budzet za pomocą wyrażenia INNER JOIN. Wiersz zawierający dane podróży POD01 nie jest wyświetlany, ponieważ w polu budzet posiada wartość NULL. Można rzecz jasna posłużyć się nawiasami lub zmienić kolejność wyrażeń JOIN tak, aby INNER JOIN było przetwarzane jako pierwsze, jednak z punktu widzenia optymalizatora najlepszym rozwiązaniem jest dalsze stosowanie po- lecenia LEFT OUTER JOIN: mysql SELECT podrozID, podroz.opis,budzet.opis, imie - FROM podroz LEFT OUTER JOIN budzet ON(podroz.budzet=budzet.budzetID) - LEFT OUTER JOIN personel ON (dysponent=personelID); +----------+---------+----------------+--------+ | podrozID | opis | opis | imie | +----------+---------+----------------+--------+ | POD01 | Sycylia | NULL | NULL | | POD02 | Egipt | Mesa oficerska | Janusz | +----------+---------+----------------+--------+ Gwiazda Cechą charakterystyczną dla wzorca gwiazdy jest jedna, centralna tabela. Wszystkie po- zostałe tabele są z nią połączone za pomocą odpowiednich relacji. Relacje te mogą być opcjonalne lub obowiązkowe. W poniższym przykładzie rolę tabeli centralnej spełnia bilet. Wszystkie bilety przypi- sane są do jednego lotniska, ale tylko niektóre sprzedane zostały przez pośrednika i je- dynie część z nich zakupiona została przez osoby posiadające konto stałego pasażera (stPasazer): CREATE TABLE bilet (biletid CHAR(4) PRIMARY KEY ,posrednik CHAR(4) NULL ,odlotZ CHAR(3)NOT NULL ,stPasazer CHAR (4) NULL ,FOREIGN KEY (posrednik) REFERENCES posrednik(id) ,FOREIGN KEY (odlotZ) REFERENCES lotnisko(id) ,FOREIGN KEY (stPasazer) REFERENCES stPasazer(id) ); W przypadku schematu gwiazdy wyrażeń LEFT OUTER JOIN należy używać jedynie w odniesieniu do tabel, które tego wymagają. Kolejność złączeń nie ma znaczenia. mysql SELECT lotnisko.nazwa AS lotnisko, - posrednik.nazwa AS posrednik, - stPasazer.imieNazwisko stPasazer - FROM bilet LEFT OUTER JOIN posrednik ON (posrednik = posrednik.id) 60 | Polecenia JOIN, UNION oraz VIEW Tworzenie kombinacji SPOSÓB 14. - INNER JOIN lotnisko ON (odlotZ = lotnisko.id) - LEFT OUTER JOIN stPasazer ON (stPasazer = stPasazer.id); +----------+--------------+------------------+ | lotnisko | posrednik | stPasazer | +----------+--------------+------------------+ | Warszawa | NULL | NULL | | Warszawa | Sigma Travel | NULL | | Katowice | Sigma Travel | Marcin Karbowski | | Katowice | NULL | Wojciech Pająk | +----------+--------------+------------------+ Ponieważ wszystkie tabele łączą się ze sobą poprzez tabelę centralną, to ona dyktuje wymagania dotyczące zastosowanych wyrażeń JOIN. Wartość NULL w polu posrednik nie wpływa na relacje między tabelami stPasazer i lotnisko a tabelą bilet. Podob- nie wartość NULL w polu stPasazer nie ma wpływu na relację łączącą tabele lotnisko i posrednik z tabelą bilet. S P O S Ó B 14. Tworzenie kombinacji Zastosowanie polecenia JOIN bez dodatkowych warunków powoduje połączenie każdego wiersza jednej tabeli z każdym wierszem innej. Utworzone zatem zostają wszystkie możliwe kombinacje wierszy. Bardzo często jest to skutek pomyłki, ale bywa również użyteczne. Kwerendy wykorzystujące polecenie CROSS JOIN pojawiają się rzadko, warto jednak wiedzieć, jak się nimi posługiwać — na wypadek, gdyby ich zastosowanie okazało się niezbędne. Jeśli tabela wykorzystywana jest więcej niż raz, mówimy o złączeniu tabeli z nią samą (ang. self-join). Jeśli złączenie dwóch wystąpień tej samej tabeli nie jest obwarowane żadnymi warunkami, otrzymamy w rezultacie wszystkie możliwe kombinacje jej wierszy. Przeprowadzając złączenie na tabeli zawierającej dane A w pierwszym wierszu i dane B w wierszu drugim, otrzymamy: ( A , A ), ( A , B ), ( B , A ) oraz ( B , B ). Lista uwzględnia zatem każdą kombinację wierszy. Załóżmy, że w lokalnej lidze grają cztery drużyny piłkarskie. Każda z nich rozegrać ma dwa mecze z wszystkimi pozostałymi — raz u siebie i raz na wyjeździe. Całość przed- stawiona została w tabelach 2.10 i 2.11. Tabela 2.10. Tabela „druzyny” NazwaDruzyny Lwy Tygrysy Pumy Ropuchy Tabela 2.11. Tabela „wyniki” Gospodarze Lwy Ropuchy Pumy Goscie Pumy Tygrysy Tygrysy bramkiGospodarzy bramkiGosci 1 3 0 4 5 0 Polecenia JOIN, UNION oraz VIEW | 61 SPOSÓB 14. Tworzenie kombinacji Musimy napisać kwerendę prezentującą wyniki wszystkich meczy. W celu uzyskania wszystkich możliwych kombinacji posługujemy się poleceniem CROSS JOIN: mysql SELECT gospodarze.nazwaDruzyny Gospodarze, goscie.nazwaDruzyny Goscie - FROM druzyny gospodarze CROSS JOIN druzyny goscie - ; +------------+---------+ | Gospodarze | Goscie | +------------+---------+ | Lwy | Lwy | | Tygrysy | Lwy | | Pumy | Lwy | | Ropuchy | Lwy | | Lwy | Tygrysy | | Tygrysy | Tygrysy | | Pumy | Tygrysy | | Ropuchy | Tygrysy | | Lwy | Pumy | | Tygrysy | Pumy | | Pumy | Pumy | | Ropuchy | Pumy | | Lwy | Ropuchy | | Tygrysy | Ropuchy | | Pumy | Ropuchy | | Ropuchy | Ropuchy | +------------+---------+ 16 rows in set (0.00 sec) Teraz trzeba wprowadzić warunek uniemożliwiający łączenie drużyny z nią samą: mysql SELECT gospodarze.nazwaDruzyny Gospodarze, goscie.nazwaDruzyny Goscie - FROM druzyny gospodarze CROSS JOIN druzyny goscie - WHERE gospodarze.nazwaDruzyny != goscie.nazwaDruzyny - ; +------------+---------+ | Gospodarze | Goscie | +------------+---------+ | Tygrysy | Lwy | | Pumy | Lwy | | Ropuchy | Lwy | | Lwy | Tygrysy | | Pumy | Tygrysy | | Ropuchy | Tygrysy | | Lwy | Pumy | | Tygrysy | Pumy | | Ropuchy | Pumy | | Lwy | Ropuchy | | Tygrysy | Ropuchy | | Pumy | Ropuchy | +------------+---------+ 12 rows in set (0.00 sec) Aby wyświetlić wyniki rozegranych meczy, pozostawiając puste miejsca w przypadku meczy jeszcze nierozegranych, należy posłużyć się poleceniem LEFT OUTER JOIN ([Spo- sób 13.] i [Sposób 26.]) w celu połączenia iloczynu zbiorów z tabelą wyniki. mysql SELECT gospodarze.nazwaDruzyny Gospodarze, goscie.nazwaDruzyny Goscie, - wyniki.bramkiGospodarzy, wyniki.bramkiGosci - FROM druzyny gospodarze CROSS JOIN druzyny goscie LEFT JOIN wyniki on - (gospodarze.nazwaDruzyny = wyniki.gospodarze) - AND wyniki.goscie = goscie.nazwaDruzyny) 62 | Polecenia JOIN, UNION oraz VIEW Tworzenie kombinacji SPOSÓB 14. - WHERE gospodarze.nazwaDruzyny != goscie.nazwaDruzyny; +------------+---------+------------------+--------------------+ | Gospodarze | Goscie | bramkiGospodarzy | BramkiGosci | +------------+---------+------------------+--------------------+ | Tygrysy | Lwy | NULL | NULL | | Pumy | Lwy | NULL | NULL | | Ropuchy | Lwy | NULL | NULL | | Lwy | Tygrysy | NULL | NULL | | Pumy | Tygrysy | 0 | 0 | | Ropuchy | Tygrysy | 3 | 5 | | Lwy | Pumy | 1 | 4 | | Tygrysy | Pumy | NULL | NULL | | Ropuchy | Pumy | NULL | NULL | | Lwy | Ropuchy | NULL | NULL | | Tygrysy | Ropuchy | NULL | NULL | | Pumy | Ropuchy | NULL | NULL | +------------+---------+------------------+--------------------+ 12 rows in set (0.00 sec) Polecenia JOIN, UNION oraz VIEW | 63
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

100 sposobów na SQL
Autor:
,

Opinie na temat publikacji:


Inne popularne pozycje z tej kategorii:


Czytaj również:


Prowadzisz stronę lub blog? Wstaw link do fragmentu tej książki i współpracuj z Cyfroteką: