Ale nie wolno go kopiować ani drukować, chyba, że uzyska się od autorów odpowiednią, pisemną, zgodę. Taki, my autorzy, mamy kaprys. Prosimy o poszanowanie.
W odpowiednim czasie zostanie opublikowany w formie papierowej.
Tekst nie jest jeszcze gotowy – ale codziennie jest go więcej… Zapisz w ‘Ulubionych’ ;)
Podoba się? Zapraszamy na szkolenia.
Edytory zapytań SQL – przegląd
Rozdział książki WG&KR „SQL w Excelu”
Wojciech Gardziński, Krzysztof Rumiński © 2010
Spis treści rozdziału:
Edytory zapytań SQL – przegląd
SQL Server / MS SQLS Management Studio – przykład lokalnej bazy serwerowej
MySQL (przykład internetowej bazy serwerowej)
SQLite (przykład lokalnej bazy plikowej)
Edytory zapytań SQL – przegląd
Przekonujemy Państwa, że używanie SQL w Excelu się opłaca, że to właściwe podejście do tematu analiz. Ale jak zacząć? Co włączyć, żeby dało się owo „zdanie SQL” wpisać?
I, żeby zadziałało…
Zadajmy więc najprostsze zadanie naszemu systemowi bazodanowemu, czyli „SELECT * FROM [tabela]” w różnych narzędziach i przyjrzyjmy się, które robią to lepiej, na czym polega to „lepiej”, gdzie możemy je wykorzystywać i czego potrzebują, żeby działać.
Edytor zapytań – to specjalny program z (często) graficznym interface’em, który wspomaga budowę poprawnych zdań języka zapytań do bazy danych. Obsługuje również wyniki zapytań, abyśmy mogli sprawdzić, jak konkretnie baza zrozumiała zapytanie.
Wspomaga on również nawiązanie łączności z bazą. Połączenie z bazą danych to coś w rodzaju połączenia telefonicznego. Łączymy się z bazą i po potwierdzeniu połączenia mamy możliwość zadawania jej pytań w języku SQL.
Pierwsza trudność to uzyskanie połączenia. Mówimy o tym w innym miejscu, ale każdy edytor również zapewnia wspomaganie tej czynności, która jest nieco trudniejsza, niż uzyskanie połączenia telefonicznego.
Mimo niewątpliwej elegancji i prawie żelaznej, wewnętrznej logiki, język SQL, zwłaszcza w zdaniu SELECT, dotyczącej zapytań o zawartość bazy, jest skomplikowany, a nawet miejscami nieintuicyjny. Wymaga precyzji, nie toleruje błędów ortograficznych (zwanych w informatyce syntaktycznymi) i komentuje te błędy w sposób albo ogólnikowy, albo mało zrozumiały.
Dlatego mamy do dyspozycji wiele edytorów zapytań. Omówimy najważniejsze z nich.
MS Query
Zalety:
- Jest dostępny w każdej, kolejnej wersji Excela, praktycznie bez zmian prawie od dwudziestu lat. Dlatego robi trochę archaiczne wrażenie, ma od lat te same uciążliwości, ale stanowi, dla początkującego użytkownika, chcącego zapoznać się z językiem SQL, nieocenioną pomoc.
- Połączenie z bazą danych, analogiczne do połączenia telefonicznego z rozmówcą, nawet dla początkującego użytkownika jest łatwe – używa on myszki do wybrania systemu bazy danych, samej bazy. Tekst połączenia, tak zwany „connection string” (CS) „sam się pisze” a nawet jest ukryty i nie powoduje rozproszenia uwagi.
- Po połączeniu z bazą ukazuje się lista tabel, z których możemy wybrać te, które chcemy umieścić w zapytaniu. Właściwie wszystkie czynności, zmierzające do sformułowania zapytania, wykonujemy myszką. To jest właśnie istota „edytora graficznego”. Ruchy i kliknięcia myszki edytor zamienia na tekst w języku SQL. I ten tekst decyduje o wyniku zapytania.
- Zdanie w języku SQL można budować graficznie, ale również wpisać i modyfikować ręcznie, nawet gdy edytor graficzny nie potrafi go wyświetlić.
- Wykonanie komendy możemy natychmiast śledzić w zwracanym wyniku zapytania (Autokwerenda).
- Niektóre bazy danych, np. baza Access, dopuszczają
stosowanie kwerend parametrycznych. Zdanie SQL wiążemy wówczas z
parametrem, podawanym w komórce arkusza Excela, np. parametr filtru SQL,
tj. np. „A%” w zapytaniu:
„SELECT * FROM faktura WHERE nazwa LIKE [Podaj wzorzec nazwy klienta]”
Ma też ograniczenia, związane przede wszystkim z dwudziestoletnim wiekiem i brakiem woli dla rozwoju tego narzędzia:
- Nie zawsze jest w stanie wyświetlić zapytanie graficznie, np. nie wyświetli zestawu trzech tabel z dwoma łączami „jeden-do-wielu” i innych złożonych zdań SQL. Z drugiej strony istnieje wówczas możliwość przesłania zapytania do bazy, niejako „na odpowiedzialność” autora zapytania. Ta właściwość „zrzucenia odpowiedzialności” za wynik zapytania to zaleta. Wiele edytorów nie zaakceptuje żadnego zapytania, którego „nie rozumie”.
- Nie jest możliwa zmiana połączenia z bazą po jego nawiązaniu. Okno kwerendy jest przypisane do połączenia.
- Zapisuje SQL w formie „maksymalistycznej”. To jednocześnie
i zaleta, i wada, bowiem w owym SQL „jest wszystko”, ale nie wiadomo, co
jest „za dużo”.
Przykład:
SELECT FAKTURA.NR_FAKT, FAKTURA.DATA, FAKTURA.NAZWA, FAKTURA.TERMIN, FAKTURA.POZYCJI, FAKTURA.WART_NET, FAKTURA.VAT
FROM `C:\KursExcela\Dane\Sprzedaz`.FAKTURA FAKTURA
(to samo co: SELECT * FROM faktura)
- Gdy piszemy zdanie SQL w wielu liniach, należy pamiętać o spacji, kończącej każdą linię – spacji, jak wiadomo, nie widzimy, a Query nie traktuje przełamania linii jako spacji (W niektórych wersjach działa, w niektórych nie działa).
- Nie można tworzyć komend DDL (Data Definicion Language) w tym samym oknie, co komendy DML (Data Manipulation Language) – do tego służy okno Uruchom w menu Plik.
Ogólna ocena:
- Niewątpliwie najlepszy na początek.
MS Access
Kwerenda w widoku SQL umożliwia wpisanie zdania SQL ręcznie, jednak dopiero gdy stworzymy nową kwerendę i wstępnie wskażemy tabelę
Zalety:
- Kreator kwerend jest, oczywiście, dostępny w każdym Accessie.
- Tworzy czytelnego SQL
- Jest możliwość wykorzystania w programie w VBA, łatwiej to jednak zrobić z VBA Excela
Wady:
- MS JET SQL odbiega od standardu, np. wildcard „dowolne znaki” to w Accesie DOS‑owa „*”, a nie standardowy w SQL-u „%”. Nie ma wielu funkcji, np. standardowej REPLACE(), posiada natomiast szereg własnych, opartych na… VBA, np. Mid(). Wprowadza to zamieszanie i utrudnia kopiowanie zapytań. Standard języka zapytań na „SQL ANSI 92” można zmienić w opcjach.
- Wymaga wielu „kliknięć” przy próbach. Najpierw „!” (Odśwież kwerendę na pasku narzędzi) – pojawia się tabela z wartościami – menu Widok, opcja Widok SQL – razem 3 kliknięcia na każdą próbę. Jest to nieco uciążliwe.)
Ogólna ocena:
- Dobry “drugi krok”. Gdy już wiemy, co to SQL i chcemy sprawdzić jego działanie „na bazie danych” (każdy SQL działa „na jakieś bazie”, ale ludzie lubią „czuć się bezpiecznie” i dążą do jednolitości środowiska) – Access wydaje się niezastąpiony.
- To dobre narzędzie dla analityka – nie-informatyka. Z Accessem łatwo się współpracuje: importuje i eksportuje dane, nawet kopiując przez schowek.
Open Office.org Base
Baza danych, dostępna pakiecie Open Office.org, jest podobna do MS Access – tu nieco łatwiej rozpocząć – widok kwerendy SQL jest jednym z widoków domyślnych.
Podobnie jak w MS Access, można tu również wykonywać polecenia SQL na innej (zewnętrznej) bazie danych, albo poprzez dołączenie tabel z innej BD, albo poprzez wyspecyfikowanie kompletnego połączenia bazodanowego w SQL (w składni ODBC albo DSN), np.
Z tego też względu, użycie Open Office.org Base jest sensowne. Standardowy SQL (W Accessie składnia MS JET SQL jest uciążliwa) na bazie zewnętrznej, łatwo edytowalny i z użytecznym interfejsem – w OOB ustawiamy Widok Dokumentu po prawej stronie okna kwerendy i widzimy efekt działania kwerendy od razu po jej zaznaczeniu, nie musimy jej specjalnie uruchamiać – rozwiązanie unikalne.
ADO4Excel
Aby przybliżyć Czytelnikom oraz innym zainteresowanym, temat SQL w Excelu, stworzyliśmy specjalny skoroszyt (MS Excel 2003) testowy do ćwiczeń. Jest on darmowy, dostępny w Internecie – wystarczy go pobrać z http://afin.net/excel/ADO4Excel.xls.
Zawiera makra – coś wszak musi nasze zapytania do serwerów bazodanowych odpowiednio kierować, a wyniki wyświetlać w arkuszu. Robi to stosunkowo prosty kod w VBA, który dodatkowo jest odkryty
Tutaj edytorem jest zwykły arkusz Excela. W komórce B3 podajemy ciąg połączenia bazodanowego, w B4 zdanie SQL i naciskamy przycisk startowy.
Dane wklejają się w arkusz natychmiast.
To narzędzie różni od innych się przede wszystkim dostępnością edycyjną ciągu połączenia bazodanowego (ang. connection string, „CS”). CS nie dość, że możemy wpisać, czyli również zmienić, to, dodatkowo, wykorzystując fakt, że znajduje się on w normalnej komórce normalnego Excela, możemy go dowolnie parametryzować, składając tekst CS z wielu komórek arkusza, np. w jednej z nich umieszczamy ścieżkę do skoroszytów, w drugiej nazwę skoroszytu, w następnej nazwę arkusza, a w kolejnej adres zakresu arkusza, z którego chcemy pobierać dane. Zmieniając dowolny z tych parametrów, otrzymujemy coraz to różne, inne, dane. To unikalna właściwość edytora SQL – inne są sztywno powiązane z konkretną bazą danych i trudno jest zmienić w trakcie edycji kwerendy.
“Interfejsy SQL” baz danych
Wszystkie systemy RDBMS (Relational Data Base Management System – Zarządzania Bazą Danych) udostępniają tzw. „interfejsy”, czyli specjalne programy do zarządzania bazą danych. Bez tych programów można też pobierać i przetwarzać dane w BD, tu jednak mamy poczucie „pełnej kontroli” i często wybieramy tę formę składania zapytań.
SQL Server / MS SQLS Management Studio – przykład lokalnej bazy serwerowej
MySQL (przykład internetowej bazy serwerowej)
Uwaga!
Zapytania na bazie testowej „afin_open” można poćwiczyć osobiście. Wymagana jest jedynie dowolna przeglądarka internetowa.
- Adres: http://afin.vot.pl/phpMyAdmin
- Użytkownik: „afin_open”, hasło: „afin”
- Karta SQL i… można ćwiczyć.
Serwer ten jest utrzymywany przez firmę AFIN i ma na celu wyłącznie udostępnienie narzędzi do prób, związanych z nauką AFIN.NET oraz SQL.
Prosimy o poszanowanie działań innych osób.
Zalety:
- Pełna baza danych na serwerze bazodanowym w dostępie administracyjnym – tu nasza zabawa staje się już poważna.
Wady:
- Bazę danych można, chcący czy niechcący… zepsuć. Prosimy o branie pod uwagę potrzeb innych osób.
Ogólna ocena:
- Raczej dla zaawansowanych, ale analitycy mogą popróbować, żeby poczuć szacunek dla informatyków.
SQLite (przykład lokalnej bazy plikowej)
Przykład menedżera bazy SQLite podajemy bardziej dla przykładu, niż dla jakiegokolwiek zastosowania – baza ta jest rzadko używana, a jeśli już jest, to zwykle poprzez obiekty VBA, a nie język SQL, co nie znaczy, że nie można przez SQL.
Inne
W zdecydowanej większości, programy te są do siebie bardzo podobne i oferują podobną funkcjonalność – mowa tu o funkcjonalności, „ogarnianej” przez analityka. Wybór takiego programu następuje, praktycznie, wtórnie – po wybraniu bazy danych. Do nauki i ćwiczeń SQL, instalowanie bazy danych i jej interfejsu nie jest jednak konieczne.
Interfejsy SQL w Internecie
Jeżeli chcemy „pouczyć się” SQL, polecamy wykorzystać do tego celu Internet i setki, jeśli nie tysiące, stron poświęconych SQL, np.
W3Schools: http://www.w3schools.com/sql/sql_tryit.asp
Można tu testować zapytania SQL, otrzymując natychmiastową odpowiedź w formie tabeli
Zalety:
- Próby łatwe i szybkie – można się uczyć zdalnie, można się dzielić wiedzą
- Pełny, otwarty dostęp bez jakiegokolwiek logowania
- Nie można nic zepsuć
- Help – pełna pomoc dotycząca języka SQL
Wady:
- Sztywność. Mamy dostępną tylko jedną tabelę
Ogólna ocena:
- Idealne narzędzie do nauki zdalnej. Mnóstwo przykładów, pełny „help”
AFIN.NET
AFIN.NET umożliwia dodatkowe operacje.
Programowalny edytor zapytań, tzw. AFIN.NET.InformationServices pozwala na programowe wykonywanie wielu zapytań SQL jedno po drugim, umożliwiając tym samym pisanie tzw. „procedur SQL” – programów, przetwarzających dane w sposób powtarzalny, sformalizowany, niekiedy bardzo skomplikowany, ale, przede wszystkim – parametryzowany, bo wszystkie operacje deklarowane są w normalnym arkuszu kalkulacyjnym.
Kolejne komendy umieszczane są, jedna linia pod drugą, i wykonywane sekwencyjnie od góry po naciśnięciu przycisku ‘Start’. Można tak tworzyć i testować wiele zapytań SQL jednocześnie.
Innym przykładem użycia może być tzw. funkcja SQL o nazie GETDATAODBC(), użyta w formie tablicowej. Zapytanie SQL zwraca zawsze dwuwymiarową tablicę wartości, w skrajnym przypadku może to być jedna wartość (tablica 1:1). Oznacza to, że, aby ją wyświetlić w tablicy w arkuszu, należy funkcję wprowadzić w formie tablicowej.
AFIN.NET dysponuje odpowiednim szablonem – wymaga on wyłącznie otwarcia.
Każda zmiana dowolnego z parametrów-argumentów funkcji GETDATAODBC() powoduje natychmiastową zmianę tablicy wyników. Gdy zapytanie jest prawidłowe – tablica wypełnia się wartościami, gdy zapytanie jest błędne – tablica wyświetla wyłącznie wartości ‘0’ (zero).
Podsumowanie
Wybór edytora zależy od gustu użytkownika i celu, do jakiego chce go użyć. Wybór jest szeroki – jedynym ograniczeniem jest sam użytkownik, a konkretnie jego ochota do nauki profesjonalnego użycia środowiska bazodanowego.