Praktyczny SQL
Autorzy: Anthony Debarros
Wydawnictwo: DW PWN
Kategorie: Informatyka
Typ: e-book
Formaty: MOBI EPUB
Ilość stron: 402
cena od: 53.40 zł
Praktyczny SQL jest szybkim i prostym wprowadzeniem do programowania za pomocą SQL. Po zapoznaniu się z podstawami języka SQL i baz danych dowiesz się, jak korzystać z interfejsu pgAdmin i bazy danych PostgreSQL do definiowania, organizowania i analizowania zestawów danych ze świata rzeczywistego, takich jak statystyki kryminalne i dane demograficzne w USA. Następnie nauczysz się, jak tworzyć bazy danych przy użyciu własnych danych, jak pisać zapytania do wykonywania obliczeń i jak radzić sobie z typowymi problemami podczas przetwarzania publicznych danych. W każdym rozdziale, za pomocą łatwych do wykonania ćwiczeń dowiesz się, jak zbudować potężne bazy danych i znaleźć sens w ich zestawach.
Copyright © 2018 by Anthony DeBarros. Title of English-language original: Practical SQL: A Beginner’s Guide to Storytelling with Data, ISBN 978-1-59327-827-4, published by No Starch Press. Polish-language edition copyright © 2018 by Polish Scientific Publishers PWN Wydawnictwo Naukowe PWN Spółka Akcyjna. All rights reserved.
Przekład Mariusz Rogulski, Magdalena Rogulska i Witold Sikorski na zlecenie WITKOM Witold Sikorski
Projekt okładki polskiego wydania Hubert Zacharski, na podstawie oryginału
Wydawca Edyta Kawala
Redaktor prowadzący Jolanta Kowalczuk
Redaktor Anna Marecka
Koordynator produkcji Anna Bączkowska
Skład wersji elektronicznej na zlecenie Wydawnictwa Naukowego PWN Karol Ossowski/Woblink
Zastrzeżonych nazw firm i produktów użyto w książce wyłącznie w celu identyfikacji.
Copyright © for the Polish edition by Wydawnictwo Naukowe PWN SA
Warszawa 2018
ISBN 978-83-01-20327-6
eBook został przygotowany na podstawie wydania papierowego z 2018 r., (wyd. I)
Warszawa 2018
Wydawnictwo Naukowe PWN SA
02-460 Warszawa, ul. Gottlieba Daimlera 2
tel. 22 69 54 321, faks 22 69 54 288
infolinia 801 33 33 88
e-mail: pwn@pwn.com.pl; reklama@pwn.pl
www.pwn.pl
O autorze
Anthony DeBarros jest wielokrotnie nagradzanym dziennikarzem, który przez większość swojej kariery łączył zainteresowania związane z analizą danych, kodowaniem i opowiadaniem historii przez dane. Spędził ponad 25 lat w firmie Gannett, w tym Poughkeepsie Journal, USA TODAY i Gannett Digital. Jest pierwszym wiceprezesem ds. rozwoju treści i produktów dla firmy wydawniczej i eventowej, mieszka i pracuje w Washingtonie, D.C.
O recenzencie merytorycznym
Josh Berkus jest „emerytowanym hakerem” projektu PostgreSQL, w który był zaangażowany przez trzynaście lat jako członek podstawowego zespołu. Przez piętnaście lat pełnił funkcję konsultanta, pracując z PostgreSQL, MySQL, CitusDB, Redis, CouchDB, Hadoop i Microsoft SQL Server. Josh jest obecnie menedżerem społeczności Kubernetes w Red Hat, Inc.
Spis treści
PRZEDMOWA
PODZIĘKOWANIA
WPROWADZENIE Czym jest SQL?
Po co używać SQL?
O książce
Korzystanie z przykładowych skryptów
Korzystanie z PostgreSQL Instalacja PostgreSQL
Praca z pgAdmin
Alternatywa dla pgAdmin
Podsumowanie
1. TWORZENIE PIERWSZEJ BAZY DANYCH I TABELI Tworzenie bazy danych Wykonywanie SQL w pgAdmin
Połączenie z bazą danych analysis
Tworzenie tabeli Polecenie CREATE TABLE
Tworzymy tabelę teachers
Wpisywanie wierszy do tabeli Instrukcja INSERT
Przeglądanie danych
Gdy kod nie zadziała
Formatowanie SQL w celu zwiększenia czytelności
Podsumowanie
2. EKSPLORACJA DANYCH ZA POMOCĄ INSTRUKCJI SELECT Podstawowa składnia SELECT Zapytania dla podzbioru kolumn
Użycie DISTINCT do znalezienia unikatowych wartości
Sortowanie danych przez ORDER BY
Filtrowanie wierszy za pomocą WHERE Użycie LIKE i ILIKE z WHERE
Łączenie operatorów z użyciem AND i OR
Zastosowanie poznanych pojęć
Podsumowanie
3. ZROZUMIEĆ TYPY DANYCH Znaki
Liczby Liczby całkowite
Autoinkrementacja liczb
Liczby niecałkowite
Wybór numerycznego typu danych
Daty i godziny
Użycie typu interval w obliczeniach
Typy dodatkowe
Konwersja wartości z jednego typu do innego z użyciem CAST
Skrócona notacja CAST
Podsumowanie
4. IMPORT I EKSPORT DANYCH Praca z rozdzielanymi plikami tekstowymi Postępowanie z kolumnami zawierającymi separatory
Obsługa nagłówków
Użycie COPY do importu danych
Importowanie danych ze spisu ludności opisujących hrabstwa Tworzenie tabeli us_counties_2010
Kolumny i typy danych w spisie ludności
Wykonanie importu danych ze spisu ludności za pomocą COPY
Import podzbioru kolumn za pomocą COPY
Wstawianie domyślnej wartości do kolumny podczas importu
Użycie COPY do eksportu danych Eksport wszystkich danych
Eksport wybranych kolumn
Eksport wyników zapytania
Import i eksport przez pgAdmin
Podsumowanie
5. PODSTAWY MATEMATYKI I STATYSTYKI W SQL Operatory arytmetyczne Matematyka i typy danych
Dodawanie, odejmowanie i mnożenie
Dzielenie i modulo
Potęgowanie, pierwiastkowanie i silnie
Kolejność działań
Matematyka na tabelach z danymi ze spisu ludności Dodawanie i odejmowanie kolumn
Znajdowanie wartości procentowych z całości
Śledzenie zmian w wartości procentowej
Funkcje agregujące do liczenia średnich i sum
Wyznaczanie mediany Znajdowanie mediany z funkcjami percentylowymi
Mediana i percentyle z danymi ze spisu ludności
Znajdowanie innych kwantyli za pomocą funkcji percentylowych
Tworzenie funkcji median()
Znajdowanie najczęściej występujących wartości
Podsumowanie
6. ŁĄCZENIE TABEL W RELACYJNEJ BAZIE DANYCH Łączenie tabel z użyciem JOIN
Łączenie tabel z użyciem kolumn kluczy
Zapytania do wielu tabel z użyciem JOIN
Typy JOIN JOIN
LEFT JOIN i RIGHT JOIN
FULL OUTER JOIN
CROSS JOIN
Użycie NULL do znalezienia wierszy z brakującymi wartościami
Trzy typy relacji pomiędzy tabelami Relacja jeden-do-jednego
Relacja jeden-do-wielu
Relacja wiele-do-wielu
Wybór określonych kolumn w złączeniu
Upraszczanie składni JOIN z aliasami tabel
Złączanie wielu tabel
Obliczenia matematyczne na kolumnach łączonych tabel
Podsumowanie
7. DOBRE PRAKTYKI W PROJEKTOWANIU TABEL Nazywanie tabel, kolumn i innych identyfikatorów Używanie cudzysłowów z identyfikatorami
Pułapki z identyfikatorami
Wytyczne do nazewnictwa identyfikatorów
Sprawdzanie wartości kolumn za pomocą ograniczeń Klucze główne: naturalny kontra sztuczny
Klucze obce
Automatyczne usuwanie rekordów z użyciem CASCADE
Ograniczenie CHECK
Ograniczenie UNIQUE
Ograniczenie NOT NULL
Usuwanie ograniczeń lub dodawanie ich później
Przyspieszanie zapytań za pomocą indeksów B-drzewo: domyślny indeks PostgreSQL
Uwagi dotyczące korzystania z indeksów
Podsumowanie
8. POZYSKIWANIE INFORMACJI PRZEZ GRUPOWANIE I SUMOWANIE Tworzenie tabel ankiet bibliotecznych Tworzenie tabeli dla danych z 2014 roku
Tworzenie tabeli dla danych z 2009 roku
Eksploracja danych o bibliotekach z użyciem funkcji agregujących Zliczanie wierszy i wartości za pomocą count()
Wyszukiwanie wartości maksymalnych i minimalnych przy użyciu max() i min()
Agregowanie danych z użyciem GROUP BY
Podsumowanie
9. WERYFIKOWANIE I MODYFIKOWANIE DANYCH Importowanie danych o producentach mięsa, drobiu i jaj
Wywiad z zestawem danych Sprawdzanie brakujących wartości
Sprawdzanie niespójności w wartościach danych
Sprawdzanie zniekształconych wartości za pomocą length()
Modyfikacja tabel, kolumn i danych Modyfikowanie tabel za pomocą ALTER TABLE
Modyfikowanie wartości za pomocą UPDATE
Tworzenie kopii zapasowych tabel
Przywracanie brakujących wartości danych w kolumnach
Aktualizowanie wartości w celu zachowania spójności
Naprawianie kodów pocztowych przy użyciu konkatenacji
Aktualizacja danych pomiędzy tabelami
Usuwanie niepotrzebnych danych Usuwanie wierszy z tabeli
Usuwanie kolumny z tabeli
Usuwanie tabeli z bazy danych
Użycie bloków transakcji do zapisywania lub cofania zmian
Poprawianie wydajności podczas aktualizowania dużych tabel
Podsumowanie
10. FUNKCJE STATYSTYCZNE W SQL Tworzenie tabeli z danymi statystycznymi ze spisu ludności Wyznaczanie korelacji za pomocą corr(Y, X)
Sprawdzenie dodatkowych korelacji
Przewidywanie wartości za pomocą analizy regresji
Określanie wpływu zmiennej niezależnej za pomocą r-kwadrat
Tworzenie rankingów w SQL Rankingi z użyciem rank() oraz dense_rank()
Ranking w podgrupach z użyciem PARTITION BY
Obliczanie współczynników dla porównań
Podsumowanie
11. PRACA Z DATAMI I GODZINAMI Typy danych i funkcje dla dat i godzin
Manipulowanie datą i godziną Wyodrębnianie komponentów z wartości timestamp
Tworzenie wartości datetime ze składników timestamp
Pobieranie aktualnej daty i godziny
Praca ze strefami czasowymi Znajdowanie ustawień Twojej strefy czasowej
Ustawianie strefy czasowej
Obliczenia z datami i godzinami Znajdowanie wzorców w danych o nowojorskich taksówkach
Znajdowanie wzorców w danych Amtrak
Podsumowanie
12. ZAAWANSOWANE TECHNIKI ZAPYTAŃ Użycie podzapytań Filtrowanie w klauzuli WHERE za pomocą podzapytań
Tworzenie tabel pochodnych za pomocą podzapytań
Złączanie tabel pochodnych
Generowanie kolumn z podzapytań
Wyrażenia z podzapytaniami
Wspólne wyrażenia tablicowe
Tabele krzyżowe Instalowanie funkcji crosstab()
Tablicowanie wyników ankiety
Tablicowanie odczytów temperatury w mieście
Przeklasyfikowanie wartości za pomocą CASE
Użycie CASE we wspólnych wyrażeniach tablicowych
Podsumowanie
13. EKSPLORACJA TEKSTÓW W CELU WYSZUKIWANIA ISTOTNYCH DANYCH Formatowanie za pomocą funkcji tekstowych Formatowanie wielkości znaków
Informacje o łańcuchach znaków
Usuwanie znaków
Pobieranie i zmiana znaków
Dopasowywanie wzorców tekstowych za pomocą wyrażeń regularnych Notacja wyrażeń regularnych
Przekształcanie tekstu w dane za pomocą funkcji z wyrażeniami regularnymi
Używanie wyrażeń regularnych z WHERE
Dodatkowe funkcje dotyczące wyrażeń regularnych
Wyszukiwanie pełnotekstowe w PostgreSQL Typy danych wyszukiwania tekstowego
Tworzenie tabeli do wyszukiwania pełnotekstowego
Przeszukiwanie tekstów przemówień
Klasyfikowanie dopasowań zapytań według trafności
Podsumowanie
14. ANALIZA DANYCH PRZESTRZENNYCH ZA POMOCĄ POSTGIS Instalowanie PostGIS i tworzenie przestrzennej bazy danych
Budowanie bloków danych przestrzennych
Geometrie dwuwymiarowe Format Well-Known Text
Uwaga dotycząca układów współrzędnych
Spatial Referencing System Identifier
Typy danych PostGIS
Tworzenie obiektów przestrzennych za pomocą funkcji PostGIS Tworzenie typu geometry z użyciem Well-Known Text
Tworzenie typu geography z Well-Known Text
Funkcje dla Point
Funkcje dla LineString
Funkcje dla Polygon
Analizowanie danych rynku rolniczego Tworzenie i wypełnianie kolumny geography
Dodanie indeksu GiST
Wyszukiwanie obiektów geograficznych w obrębie podanej odległości
Wyznaczanie odległości pomiędzy obiektami geograficznymi
Praca z plikami shape ze spisu ludności Zawartość pliku shape
Ładowanie plików shape przez GUI
Eksplorowanie pliku shape ze spisu ludności 2010
Wykonywanie złączeń przestrzennych Odkrywanie danych o drogach i szlakach wodnych
Złączanie tabel dróg i szlaków wodnych ze spisu ludności
Znajdowanie lokalizacji, w której przecinają się obiekty
Podsumowanie
15. OSZCZĘDZANIE CZASU DZIĘKI WIDOKOM, FUNKCJOM I WYZWALACZOM Użycie widoków do uproszczenia zapytań Tworzenie i odpytywanie widoków
Wstawianie, aktualizowanie i usuwanie danych przy użyciu widoku
Programowanie własnych funkcji Tworzenie funkcji percent_change()
Użycie funkcji percent_change()
Aktualizowanie danych za pomocą funkcji
Używanie języka Python w funkcji
Automatyzowanie działań w bazie danych za pomocą wyzwalaczy Rejestrowanie zmian ocen w tabeli
Automatyczna klasyfikacja temperatury
Podsumowanie
16. UŻYWANIE POSTGRESQL Z WIERSZA POLECEŃ Konfigurowanie wiersza poleceń dla psql Konfiguracja psql w Windows
Konfiguracja psql w macOS
Ustawienia psql w systemie Linux
Praca z psql Uruchamianie psql i łączenie z bazą danych
Uzyskiwanie pomocy
Zmiana użytkownika i połączenia z bazą danych
Uruchamianie zapytań SQL w psql
Nawigacja i formatowanie wyników
Metapolecenia do uzyskiwania informacji o bazie danych
Importowanie, eksportowanie i używanie plików
Dodatkowe narzędzia wiersza poleceń do przyspieszania pracy Dodawanie bazy danych za pomocą createdb
Ładowanie plików shape z użyciem shp2pgsql
Podsumowanie
17. UTRZYMANIE NASZEJ BAZY DANYCH Odzyskiwanie nieużywanego miejsca za pomocą VACUUM Śledzenie wielkości tabeli
Monitorowanie procesu autovacuum
Ręczne wykonywanie VACUUM
Zmniejszanie rozmiaru tabeli za pomocą VACUUM FULL
Zmiana ustawień serwera Lokalizacja i edycja postgresql.conf
Ponowne załadowanie ustawień za pomocą pg_ctl
Kopia zapasowa bazy danych i jej przywracanie Wykorzystanie pg_dump do tworzenia kopii zapasowej bazy danych lub tabeli
Przywracanie bazy danych z kopii zapasowej za pomocą pg_restore
Dodatkowe opcje kopii zapasowej i przywracania
Podsumowanie
18. IDENTYFIKACJA I OPOWIEŚĆ KRYJĄCA SIĘ ZA NASZYMI DANYMI Zaczynamy od pytania
Dokumentacja naszych działań
Zbieranie danych
Brak danych? Utwórz własną bazę danych
Dostęp do miejsca pochodzenia danych
Wywiad na temat danych za pomocą zapytań
Skonsultuj się z właścicielem danych
Identyfikacja kluczowych wskaźników i trendów czasowych
Zapytaj dlaczego
Przekaż swoje ustalenia
Podsumowanie
ANEKS DODATKOWE ZASOBY POSTGRESQL Środowiska programistyczne PostgreSQL
Narzędzia, dodatki i rozszerzenia PostgreSQL
Aktualności PostgreSQL
Dokumentacja
PRZYPISY
PRZEDMOWA
Gdy ludzie pytają, którego języka programowania nauczyłam się najpierw, często z roztargnieniem odpowiadam – „Pythona”, zapominając, że nauczyłam się pisać kod właśnie w SQL. To prawdopodobnie dlatego, że po spędzeniu wielu lat na wprowadzaniu formuł w arkuszach kalkulacyjnych Excela uczenie się języka SQL było tak intuicyjne. Nie miałam technicznego przygotowania, ale odkryłam, że składnia SQL, w przeciwieństwie do wielu innych języków programowania, jest prosta i łatwa do zastosowania. Na przykład wykonując SELECT * na tabeli SQL, wyświetlamy każdy wiersz i kolumnę. Wystarczy użyć słowa kluczowego JOIN, aby zwrócić wiersze danych z różnych powiązanych tabel, które następnie można dalej grupować, sortować i analizować.
Jestem redaktorem graficznym i pracowałam jako programistka oraz dziennikarz u wielu wydawców, w tym w POLITICO, Vox i USA TODAY. Moje codzienne obowiązki obejmują analizowanie danych i tworzenie wizualizacji z tego, co wyszukuję. Po raz pierwszy użyłam SQL, gdy pracowałam w The Chronicle of Higher Education i jej siostrzanej gazecie The Chronicle of Philanthropy. Nasz zespół przeanalizował dane, począwszy od finansów organizacji non profit, a skończywszy na wynagrodzeniach na wydziałach college’ów i uniwersytetów. Wiele z naszych projektów obejmowało dane nawet z 20 lat, a do moich głównych zadań należały zaimportowanie wszystkich tych danych do bazy danych SQL i ich analiza. Musiałam obliczyć procentową zmianę w kwotach pochodzących ze zbiórek pieniędzy organizacji non profit lub znaleźć medianę wielkości dofinansowania na uniwersytecie, w celu określenia wyników instytucji.
Odkryłam, że SQL to potężny język, który zasadniczo ukształtował moje rozumienie tego, co można zrobić z danymi, a czego nie. SQL doskonale radzi sobie z uporządkowaniem dużych zestawów danych i pomaga odkryć, w jaki sposób różne zestawy danych są powiązane. Ponadto jego zapytania i funkcje można łatwo wykorzystać w ramach tego samego projektu lub nawet w innej bazie danych.
To doprowadziło do powstania „Praktycznego SQL”. Patrząc wstecz, chciałabym przeczytać rozdział 4. na temat importu i eksportu danych, aby móc zrozumieć siłę zbiorczych importów, zamiast pisać długie, uciążliwe instrukcje INSERT przy wypełnianiu tabeli. Możliwości statystyczne PostgreSQL, o których mowa w rozdziałach 5. i 10. tej książki, są również czymś, co chciałabym wcześniej poznać, ponieważ moja analiza danych często wymaga obliczenia procentowej zmiany, znalezienia wartości średniej lub mediany. Wstydzę się powiedzieć, że nie wiedziałam, w jaki sposób funkcja percentile_cont(), o której mowa w rozdziale 5., może być użyta do łatwego obliczenia mediany w PostgresSQL – z dodatkową zaletą, że znajduje także podziały naturalne lub kwantyle.
Jednak na tym etapie mojej kariery niewiele wiedziałam o możliwościach SQL. Dopiero w 2014 roku, kiedy zostałam programistką w Gannett Digital w zespole kierowanym przez Anthony’ego DeBarrosa, nauczyłam się korzystać z PostgreSQL. Zaczęłam rozumieć, jak potężny jest SQL w przypadku tworzenia powtarzalnego i wydajnego przepływu danych.
Kiedy poznałam Anthony’ego, już od ponad 20 lat pracował w USA TODAY i innych podmiotach należących do Gannett, kierując zespołami, które budowały bazy danych oraz publikowały wyniki śledztw, za co otrzymywały nagrody. Anthony pokazał mi tajniki baz danych naszego zespołu, a także nauczył mnie, jak właściwie budować i utrzymywać własne. Właśnie dzięki współpracy z Anthonym naprawdę nauczyłam się kodować.
Jednym z pierwszych projektów, przy którym ja i Anthony współpracowaliśmy, były wybory w USA w 2014 r. Pomogliśmy stworzyć wizualizację danych prognoz wyborczych, aby pokazać czytelnikom USA Today najnowsze dane z sondaży, dane finansowe kampanii oraz informacje biograficzne dla ponad 1300 kandydatów startujących w ponad 500 wyścigach o miejsca w Kongresie i fotel gubernatora. Budowanie naszej infrastruktury danych było skomplikowanym, wieloetapowym procesem opartym na bazie danych PostgreSQL.
Anthony nauczył mnie pisać kod, który pobrał dane ze wszystkich naszych źródeł do pół tuzina tabel w PostgreSQL. Stamtąd już mogliśmy przenieść dane do formatu, który zasilał mapy, wykresy i prezentację naszej prognozy wyborczej na najbliższą przyszłość.
Mniej więcej w tym czasie poznałam jedną z moich ulubionych części PostgreSQL – jego potężny zestaw funkcji geograficznych (rozdział 14. w tej książce). Dodając rozszerzenie PostGIS do bazy danych, można tworzyć dane przestrzenne, które następnie da się wyeksportować jako GeoJSON lub jako plik shape, czyli format do zastosowania z aplikacjami mapowymi. Można także wykonywać złożone analizy przestrzenne, takie jak obliczanie odległości między dwoma punktami, znajdowanie gęstości szkół lub, jak pokazuje Anthony w jednym z rozdziałów, wszystkie rynki rolnicze w danym promieniu.
To umiejętność, która wielokrotnie przydała się w mojej karierze. Na przykład podczas pracy w Vox użyłam jej do zbudowania zestawu danych dotyczących ryzyka narażenia na ołów na poziomie poszczególnych okręgów spisowych*, co uważam za jedno z moich największych osiągnięć w zakresie korzystania z PostGIS. Korzystając z tej bazy danych, udało mi się stworzyć zestaw danych przestrzennych dotyczących ryzyka narażenia na ołów dla każdego amerykańskiego okręgu spisowego, który może być łatwo zmapowany na poziom krajowy.
Przy tak wielu różnych językach programowania – obecnie ponad 200 – pytanie: od czego zacząć, może być przytłaczające. Jedna z najlepszych porad, którą otrzymałam, gdy zaczęłam programować, dotyczyła tego, by znaleźć nieefektywności w moim systemie pracy, które można byłoby poprawić za pomocą programu. W moim przypadku tworzyłam bazę danych w celu łatwego wyszukiwania danych o projekcie. Może jesteś w podobnej sytuacji lub może po prostu chcesz wiedzieć, jak analizować duże zestawy danych.
Bez względu na to, prawdopodobnie szukasz sensownego przewodnika, który pomija żargon programistyczny i zagłębia się w SQL w sposób łatwy do zrozumienia, zarówno w sensie praktycznym, jak i, co ważniejsze, w zakresie jego zastosowania. I tym właśnie jest Praktyczny SQL. Nie zajmuje się teorią programowania, ale skupia na nauczaniu SQL na przykładach, używając prawdziwych zestawów danych. Nie boi się także pokazywać, jak radzić sobie z irytującymi pułapkami związanymi z danymi: błędnie napisanymi nazwami, brakującymi wartościami i kolumnami z nieodpowiednimi typami danych. Jest to ważne, ponieważ – jak szybko się przekonasz – nie ma czegoś takiego jak dane pozbawione błędów.
Z biegiem lat moja rola związana z dziennikarstwem danych ewoluowała. Obecnie buduję mniej baz danych, za to więcej map. Tworzę również więcej raportów. Jednak podstawowe wymagania dotyczące mojej pracy i to, czego nauczyłam się podczas poznawania SQL, pozostają takie same: znać swoje dane i dbać, by były prawdziwe. Innymi słowy, najważniejszym aspektem pracy z danymi jest rozumienie, co się w nich znajduje.
Nie możesz oczekiwać, że zadasz odpowiednie pytania dotyczące swoich danych, jeśli nie wiesz, jak je przeanalizować najlepiej. Na szczęście w tym momencie pojawia się Praktyczny SQL. Nauczy Cię podstaw pracy z danymi, dzięki czemu będziesz mieć możliwość stworzenia własnej historii oraz zebrania spostrzeżeń.
Sarah Frostenson
grafik w POLITICO
PODZIĘKOWANIA
Praktyczny SQL jest efektem pracy wielu osób. Podziękowania kieruję, po pierwsze, do zespołu No Starch Press. Dziękuję Billowi Pollockowi i Tyler Ortman za sprecyzowanie wizji i określenie wstępnej koncepcji; redaktorom Annie Choi i Liz Chadwick za dopracowanie każdego rozdziału; redaktor Anne Marie Walker za wygładzenie, dzięki jej bystremu oku, wersji końcowej oraz redaktor technicznej za uporządkowanie książki i za dobrą organizację procesu wydawniczego.
Josh Berkus, menedżer społeczności Kubernetes w Red Hat, Inc., był naszym recenzentem technicznym. Praca z Joshem była jak uzyskanie klasy mistrzowskiej w SQL i PostgreSQL. Dziękuję, Josh, za Twoją cierpliwość i wysokie standardy.
Dziękuję Investigative Reporters and Editors (IRE) oraz jego byłym i obecnym członkom oraz pracownikom za szkolenie dziennikarzy w poszukiwaniu wspaniałych historii kryjących się za danymi. IRE było moim początkiem z SQL i dziennikarstwem danych.
Podczas lat spędzonych w USA TODAY wielu kolegów albo uczyło mnie języka SQL, albo udzielało niezapomnianych lekcji na temat analizy danych. Specjalne podziękowania należą się Paulowi Overbergowi za podzielenie się rozległą wiedzą na temat danych demograficznych i spisu ludności w USA, Lou Schilling za wiele technicznych lekcji, Christopherowi Schnaarsowi za jego kompetencje w SQL oraz Sarze Frostenson za to, że zgodziła się napisać przedmowę do tej książki.
Moje najgorętsze podziękowania kieruję do mojej ukochanej żony Elizabeth oraz do naszych synów. Dziękuję, że sprawiacie, iż każdy dzień jest jasny i ciepły, za Waszą miłość i za cierpliwość podczas mojej pracy nad książką.
WPROWADZENIE
Tuż po dołączeniu do zespołu USA TODAY otrzymałem zestaw danych, które miałem analizować prawie co tydzień przez następną dekadę. Była to tygodniowa lista bestsellerów prezentująca ranking najlepiej sprzedających się książek w Stanach Zjednoczonych na podstawie poufnych danych dotyczących sprzedaży. To zestawienie było nie tylko bogatym źródłem pomysłów na historię, ale również w szczególny sposób uchwycało amerykański Zeitgeist.
Dla przykładu, czy wiesz, że sprzedaż książek kucharskich nieco wzrasta w tygodniu, w którym obchodzimy Dzień Matki, lub że Oprah Winfrey zmieniła skromnych pisarzy w autorów bestsellerów, goszcząc ich w swoim show? Tydzień po tygodniu wraz z redaktorem listy książek przeglądaliśmy dane dotyczące sprzedaży i gatunków książek, porównując dane, w poszukiwaniu następnego nagłówka. Rzadko kiedy niczego nie znajdowaliśmy: odnotowaliśmy wszystko, począwszy od szaleńczego wzrostu przebojowej serii o Harrym Potterze, aż po fakt, że Oh, the Places You’ll Go! autorstwa dr. Seussa stało się ponadczasowym prezentem dla nowych absolwentów. Moim technicznym towarzyszem w tym czasie był język programowania baz danych SQL (Structured Query Language). Dość wcześnie przekonałem dział IT USA TODAY, by udzielił mi dostępu do systemu baz danych opartego na języku SQL, który zasilał naszą aplikację listy książek. Używając SQL, udało mi się odblokować historie ukryte w bazie danych, zawierające tytuły, autorów, gatunki i różne kody, które są charakterystyczne dla świata wydawniczego. Analiza danych za pomocą SQL w celu odkrywania interesujących historii jest dokładnie tym, czego nauczysz się, korzystając z tej książki.
Czym jest SQL?
SQL jest powszechnie stosowanym językiem programowania, który pozwala tworzyć bazy danych oraz zapytania do nich. Niezależnie od tego, czy jesteś analitykiem marketingowym, dziennikarzem czy badaczem mapującym neurony w mózgu muszki owocowej, odkryjesz korzyści z używania SQL do zarządzania obiektami bazy danych, jak również do tworzenia, modyfikowania, eksplorowania i podsumowywania danych.
Ponieważ SQL to dojrzały język, który istnieje od dziesięcioleci, jest głęboko zakorzeniony w wielu nowoczesnych systemach. Para badaczy IBM po raz pierwszy opisała składnię SQL (nazwaną wtedy SEQUEL) w artykule z 1974 r., bazując na teoretycznej pracy brytyjskiego informatyka Edgara F. Codda. W 1979 roku prekursor firmy bazodanowej Oracle (zwanej wówczas Relational Software) jako pierwszy użył języka w produkcie komercyjnym. Dziś SQL jest nadal jednym z najczęściej używanych języków komputerowych na świecie i mało prawdopodobne, że wkrótce się to zmieni.
SQL występuje w wielu wariantach, które są zazwyczaj powiązane z konkretnymi systemami baz danych. Amerykański Narodowy Instytut Normalizacyjny (ANSI) i Międzynarodowa Organizacja Normalizacyjna (ISO), które określają standardy dla produktów i technologii, definiują standardy dotyczące tego języka i wprowadzanych w nim zmian. Dobrą wiadomością jest to, że istniejące warianty nie odbiegają daleko od standardu, więc gdy poznasz konwencję SQL dla jednej bazy danych, możesz przenieść tę wiedzę do innych systemów.
Po co używać SQL?
Dlaczego więc powinno się używać SQL? W końcu SQL zwykle nie jest pierwszym narzędziem, które wybierają ludzie, ucząc się analizować dane. W rzeczywistości wiele osób zaczyna od arkuszy kalkulacyjnych Microsoft Excel i ich zestawu funkcji analitycznych. Po pracy z Excelem można przejść do Accessa, systemu baz danych wbudowanego w Microsoft Office, który ma graficzny interfejs zapytań ułatwiający wykonywanie pracy, przez co znajomość SQL nie jest konieczna.
Ale jak wiesz, Excel i Access mają swoje ograniczenia. Excel pozwala na wstawienie maksymalnie 1 048 576 wierszy w arkuszu roboczym, natomiast Access ogranicza rozmiar bazy danych do dwóch gigabajtów i 255 kolumn na pojedynczą tabelę. Często zdarza się, że zbiory danych przekraczają te ograniczenia, szczególnie gdy pracuje się z danymi pochodzącymi z systemów obejmujących cały kraj. Ostatnią przeszkodą, na którą chcielibyśmy się natknąć, próbując wykonać pracę w wyznaczonym terminie, byłoby to, że nasz system bazodanowy nie byłby w stanie sprostać oczekiwaniom.
Korzystanie z rozbudowanego systemu baz danych SQL umożliwia pracę z terabajtami danych, wieloma powiązanymi tabelami i tysiącami kolumn. Zapewnia to lepszą programową kontrolę nad strukturami danych, co prowadzi do wydajności, szybkości i – co najważniejsze – dokładności.
SQL jest również doskonałym dodatkiem do języków programowania, takich jak R czy Python używanych w naukach związanych z danymi. Jeśli używasz jednego z tych języków, możesz połączyć się z bazami danych SQL, a w niektórych przypadkach nawet włączyć składnię SQL bezpośrednio do języka. Osobom bez znajomości języków programowania SQL często służy jako łatwe do zrozumienia wprowadzenie do pojęć związanych ze strukturami danych i logiką programowania.
Ponadto znajomość języka SQL może pomóc Ci w dalszej analizie danych. Jeśli będziesz się zajmować tworzeniem aplikacji online, przekonasz się, że bazy danych są istotnym elementem wielu popularnych frameworków webowych, interaktywnych map i systemów zarządzania treścią. Gdy będziesz zmuszony zagłębić się nieco wnikliwiej w te systemy, zdolność do manipulowania danymi i bazami danych za pomocą SQL okaże się bardzo przydatna.
O książce
Praktyczny SQL jest przeznaczony dla osób, które w swoim codziennym życiu mają styczność z danymi i chcą się nauczyć je analizować oraz nimi manipulować. W tym celu omawiam rzeczywiste dane i scenariusze takie jak dane demograficzne z USA, statystyki kryminalne i dane o podróżach taksówkami w Nowym Jorku. Wraz z wiedzą o bazach danych i programowaniu poznasz także wskazówki, jak analizować i pozyskiwać dane, a także uzyskasz wiele wartościowych informacji zebranych przeze mnie podczas całej mojej kariery. Nie będę skupiał się na konfigurowaniu serwerów lub innych zadaniach wykonywanych typowo przez administratora bazy danych, lecz na nauce podstaw SQL i PostgreSQL, tak aby były Ci pomocne w dalszej drodze.
Przygotowane ćwiczenia są przeznaczone dla początkujących programistów SQL, chociaż zakładam, że wiesz, jak pracować z komputerem, w tym jak instalować programy, nawigować po dysku twardym i pobierać pliki z Internetu. Chociaż wiele rozdziałów w tej książce może być traktowanych niezależnie, warto zapoznać się z nimi w podanej kolejności, tak aby mieć odpowiednie podstawy. Niektóre zestawy danych używane we wcześniejszych rozdziałach pojawiają się ponownie w dalszej części książki, zatem zapoznanie się treścią w podanej kolejności ułatwi Ci nieutracenie wątku.
Praktyczny SQL rozpoczyna się od podstaw dotyczących baz danych, zapytań, tabel i danych, które są wspólne dla SQL niezależnie od systemu bazodanowego. Rozdziały od 13. do 17. zawierają tematy bardziej specyficzne dla PostgreSQL, takie jak wyszukiwanie pełnotekstowe i GIS. Poniższy spis treści zawiera więcej szczegółów na temat zagadnień omówionych w poszczególnych rozdziałach:
Rozdział 1: Tworzenie pierwszej bazy danych i tabeli stanowi wprowadzenie do PostgreSQL, interfejsu użytkownika pgAdmin i skryptu ładującego prosty zestaw danych o nauczycielach do nowej bazy danych.
Rozdział 2: Eksploracja danych za pomocą instrukcji SELECT opisuje podstawową składnię zapytań SQL, w tym sposoby sortowania i filtrowania danych.
Rozdział 3: Zrozumieć typy danych wyjaśnia sposoby definiowania kolumn w tabelach w celu przechowywania określonych typów danych, począwszy od tekstów, przez daty, a skończywszy na różnych postaciach liczb.
Rozdział 4: Import i eksport danych opisuje, w jaki sposób należy korzystać z poleceń SQL, aby załadować dane z plików zewnętrznych, a następnie je wyeksportować. Tu załadujesz tabelę z danymi pochodzącymi ze spisu ludności, której będziesz używać w dalszej części książki.
Rozdział 5: Podstawy matematyki i statystyki w SQL obejmuje operacje arytmetyczne i wprowadza zagregowane funkcje do wyszukiwania sum, średnich i median.
Rozdział 6: Łączenie tabel w relacyjnej bazie danych wyjaśnia, jak tworzyć zapytania do wielu powiązanych tabel, łącząc je za pomocą kluczy. Dowiesz się, jak i kiedy używać różnych typów złączeń.
Rozdział 7: Dobre praktyki w projektowaniu tabel obejmuje sposób konfigurowania tabel w celu poprawy organizacji i integralności danych oraz przyspieszenia zapytań przy użyciu indeksów.
Rozdział 8: Pozyskiwanie informacji przez grupowanie i sumowanie wyjaśnia, jak korzystać z funkcji agregujących, aby odkryć trendy w korzystaniu z bibliotek w USA na podstawie danych z corocznych ankiet.
Rozdział 9: Weryfikowanie i modyfikowanie danych pokaże, jak znaleźć i naprawić niekompletne lub niedokładne dane, wykorzystując jako przykład zbiór informacji dotyczących producentów mięsa, jaj i drobiu.
Rozdział 10: Funkcje statystyczne w SQL wprowadza funkcje korelacji, regresji i rankingu dostępne w SQL, które pomagają pozyskać więcej informacji z posiadanych zbiorów danych.
Rozdział 11: Praca z datami i godzinami wyjaśnia, w jaki sposób tworzyć, manipulować i wyszukiwać daty oraz godziny w bazie danych z uwzględnieniem stref czasowych na przykładzie danych dotyczących taksówek w Nowym Jorku i rozkładów jazdy pociągów Amtrak.
Rozdział 12: Zaawansowane techniki zapytań wyjaśnia, w jaki sposób używać bardziej złożonych operacji SQL takich jak podzapytania i tabele krzyżowe oraz instrukcji CASE w celu zmiany wartości w zbiorze danych zawierającym odczyty temperatury.
Rozdział 13: Eksploracja tekstów w celu wyszukiwania istotnych danych opisuje, jak korzystać z mechanizmu wyszukiwania pełnotekstowego PostgreSQL i wyrażeń regularnych, aby wyodrębnić dane z nieustrukturyzowanego tekstu na przykładzie zestawu przemówień prezydentów USA.
Rozdział 14: Analiza danych przestrzennych za pomocą PostGIS wprowadza typy danych i zapytania związane z obiektami przestrzennymi, które pozwolą Ci analizować obiekty geograficzne, takie jak jednostki administracyjne, drogi i rzeki.
Rozdział 15: Oszczędzanie czasu dzięki widokom, funkcjom i wyzwalaczom wyjaśnia, jak zautomatyzować zadania bazy danych, aby uniknąć powtarzania rutynowych czynności.
Rozdział 16: Używanie PostgreSQL z wiersza poleceń opisuje, w jaki sposób używać poleceń tekstowych w wierszu poleceń, aby połączyć się z bazą danych i wykonywać zapytania.
Rozdział 17: Utrzymanie naszej bazy danych zawiera wskazówki i procedury dotyczące monitorowania rozmiaru bazy danych, dostosowywania ustawień i tworzenia kopii zapasowych.
Rozdział 18: Identyfikacja i opowieść kryjąca się za naszymi danymi zawiera wskazówki i pomysły na analizy oraz weryfikacje danych, a także wyciąganie istotnych wniosków i ich jasne przedstawianie.
Załącznik: Dodatkowe zasoby PostgreSQL zawiera listę oprogramowania i dokumentacji, które pomogą Ci rozwinąć umiejętności.
Każdy rozdział kończy się częścią „Sprawdź się” zawierającą ćwiczenia pomagające utrwalić tematy, których się nauczyliśmy.
Korzystanie z przykładowych skryptów
Każdy rozdział zawiera przykłady skryptów, z których większość bazuje na przygotowanych przeze mnie danych. Wszystkie skrypty i przykładowe dane używane w książce są dostępne do pobrania pod adresem https://www.nostarch.com/practicalSQL/. Kliknij Download the code from GitHub, aby przejść do repozytorium GitHub zawierającego te materiały. Na GitHub zobaczysz przycisk „Clone or Download”, który daje Ci możliwość pobrania pliku ZIP ze wszystkimi materiałami. Zapisz plik na komputerze w lokalizacji, w której możesz go łatwo znaleźć, na przykład na pulpicie.
Wewnątrz pliku ZIP dla każdego rozdziału znajduje się osobny folder. Każdy folder zawiera plik o nazwie Chapter_XX (gdzie XX jest numerem rozdziału), zakończony rozszerzeniem .sql. Możesz otworzyć te pliki za pomocą edytora tekstu lub narzędzia administracyjnego PostgreSQL, które zainstalujesz. Gdy w książce znajduje się propozycja uruchomienia skryptu, możesz wtedy skopiować i wkleić przykładowy kod. Zwróć uwagę, że w książce w celu zaoszczędzenia miejsca część przykładowych skryptów jest skróconych, ale aby ukończyć ćwiczenie, musisz mieć kompletną postać skryptu w pliku .sql. Gdy w przykładowym kodzie w książce zobaczysz --snip--, będzie to oznaczać, że przykład jest skrócony.
Także w plikach .sql zobaczysz linie zaczynające się od dwóch myślników (--) i spacji. Są to komentarze, które podają numer listingu i dodatkowe informacje, ale nie są częścią właściwego kodu. Te komentarze wskazują także sytuacje, gdy plik zawiera dodatkowe przykłady, których nie ma w książce.
UWAGA
Po pobraniu plików użytkownicy systemu Windows mogą potrzebować zezwolenia na odczyt plików przez bazę danych. Aby to zrobić, kliknij prawym przyciskiem myszy folder zawierający listingi i dane, wybierz Właściwości i kliknij kartę Zabezpieczenia. Kliknij Edytuj…, a następnie Dodaj…. Wpisz nazwę Wszyscy w polu nazwy obiektu i kliknij OK. Zaznacz Wszyscy na liście użytkowników, zaznacz wszystkie pola w kolumnie Zezwalaj, a następnie kliknij Zastosuj i OK.
Korzystanie z PostgreSQL
W tej książce nauczę Cię SQL z wykorzystaniem bazodanowego systemu PostgreSQL, który jest produktem typu open source. PostgreSQL (lub po prostu Postgres) jest rozbudowanym systemem baz danych, który może obsłużyć bardzo duże ilości danych. Oto kilka powodów, dla których PostgreSQL jest doskonałym wyborem do wykorzystania przy pracy z tą książką:
Jest darmowy.
Istnieją wersje dla systemów operacyjnych Windows, macOS oraz Linux.
Jego implementacja SQL ściśle przestrzega norm ANSI.
Jest powszechnie stosowany w analityce i eksploracji danych, więc znalezienie pomocy online od innych użytkowników jest łatwe.
Rozszerzenie geoprzestrzenne, PostGIS, pozwala analizować dane geometryczne i wykonywać funkcje związane z mapami.
Jest dostępny w wielu wariantach, takich jak Amazon Redshift i Greenplum, które koncentrują się na przetwarzaniu ogromnych zbiorów danych.
Jest popularny w zastosowaniach dla aplikacji internetowych, w tym opartych na popularnych frameworkach internetowych Django i Ruby on Rails.
Oczywiście można również użyć innego systemu bazodanowego, np. Microsoft SQL Server lub MySQL; wiele przykładów kodu w tej książce łatwo przekłada się na implementację SQL tych systemów. Jednak niektórych z nich, szczególnie w dalszej części książki, nie da się prosto przenieść na inne systemy, w związku z czym konieczne byłoby wyszukanie w Internecie odpowiednich rozwiązań. W niektórych przykładach będę zaznaczał, czy przykładowy kod jest zgodny ze standardem ANSI SQL i czy da się przenieść do innych systemów, czy jest specyficzny dla PostgreSQL.
Instalacja PostgreSQL
Zaczniemy od zainstalowania bazy danych PostgreSQL i graficznego narzędzia administracyjnego pgAdmin, czyli oprogramowania, które ułatwia zarządzanie bazą danych, importowanie i eksportowanie danych oraz pisanie zapytań.
Jedną z dużych zalet pracy z PostgreSQL jest to, że niezależnie od tego, czy pracujesz w systemach: Windows, MacOS czy Linux, narzędzia open source ułatwiają pobranie i uruchomienie PostgreSQL. Poniższe części opisują instalację dla wszystkich trzech systemów operacyjnych, choć pewne opcje mogą ulec zmianie wraz z pojawieniem się nowych wersji. Sprawdź dokumentację wskazaną w każdej części, a także repozytorium GitHub z zasobami dla tej książki. Będę tam dodawał pliki z aktualizacjami i odpowiedziami na często zadawane pytania.
UWAGA
Zawsze instaluj najnowszą dostępną wersję PostgreSQL dla swojego systemu operacyjnego, aby mieć pewność, że zawiera ona najświeższe łatki oraz nowe funkcje. W przypadku tej książki zakładam, że używasz wersji 10.0 lub nowszej.
Instalacja w Windows
W przypadku Windows zalecam użycie instalatora dostarczonego przez firmę EnterpriseDB, która oferuje wsparcie dla użytkowników PostgreSQL. Oprogramowanie EnterpriseDB zawiera pakiety PostgreSQL z pgAdmin oraz własny Stack Builder, który daje nam m.in. możliwość zainstalowania rozszerzenia PostGIS dla danych przestrzennych i wsparcie dla języków programowania. Aby pobrać oprogramowanie, wejdź na https://www.enterprisedb.com/ i utwórz darmowe konto. Następnie przejdź do strony pobierania: https://www.enterprisedb.com/software-downloads-postgres/.Wybierz najnowszą dostępną 64-bitową wersję EDB Postgres Standard dla systemu Windows, chyba że używasz starszego komputera z 32-bitowym systemem operacyjnym. Po pobraniu instalatora wykonaj następujące kroki:
Kliknij instalator prawym klawiszem myszy i wybierz opcję Uruchom jako administrator. Odpowiedz Tak na pytanie o zezwolenie programowi na wprowadzenie zmian w komputerze. Program zainicjuje proces, a następnie wyświetli początkowy ekran powitalny. Przejdź przez niego.
Wybierz folder do instalacji systemu, akceptując domyślny.
W oknie Select Components zaznacz pola instalacji serwera PostgreSQL, narzędzi pgAdmin, Stack Builder oraz Command Line Tools.
Wybierz lokalizację do przechowywania danych. Możesz wskazać wartość domyślną, która znajduje się w podkatalogu „data” w folderze PostgreSQL.
Ustaw hasło. PostgreSQL ma rozbudowane mechanizmy zabezpieczeń i uprawnień. To jest hasło dla superużytkownika (nazywanego postgres) tworzonej bazy danych.
Wybierz numer portu, na którym serwer będzie nasłuchiwał. Jeśli nie masz innej bazy danych lub aplikacji, która go używa, domyślna wartość 5432 powinna być odpowiednia. Jeśli masz już zainstalowaną inną wersję PostgreSQL lub inna aplikacja używa tej wartości, możesz wpisać 5433 lub inną wartość. Będzie to również poprawne.
Wybierz swoje ustawienia regionalne. Możesz też użyć wartości domyślnych. Następnie kliknij na ekranie podsumowania, aby rozpocząć instalację, która potrwa kilka minut.
Po zakończeniu instalacji pojawi się pytanie, czy chcesz uruchomić Stack Builder, aby zainstalować dodatkowe rozszerzenia. Zaznacz to pole i kliknij przycisk Finish.
Po uruchomieniu Stack Builder wybierz z menu rozwijanego zainstalowaną wersję PostgreSQL i kliknij przycisk Next. Zostanie pobrana lista dodatkowych aplikacji.
Rozwiń pozycję Spatial Extensions i wybierz 32-bitową lub 64-bitową wersję PostGIS Bundle dla zainstalowanej wersji Postgres. Ponadto rozwiń pozycję Add-ons, tools and utilities i wybierz EDB Language Pack, który doinstaluje obsługę języków programowania, w tym Pythona. Klikaj kilka razy; trzeba będzie poczekać, aż instalator pobierze dodatkowe komponenty.
Po pobraniu plików instalacyjnych kliknij przycisk Next, aby zainstalować oba składniki. W przypadku PostGIS musisz zaakceptować warunki licencji; klikaj Next, dopóki nie pojawi się okienko Choose Components. Upewnij się, że są zaznaczone pozycje PostGIS oraz Create spatial database. Kliknij przycisk Next, zaakceptuj domyślną lokalizację bazy danych i ponownie kliknij przycisk Next.
Gdy pojawi się okienko Database Connection, wpisz hasło do bazy danych i kontynuuj przechodzenie przez okienka, aż do ukończenia instalacji PostGIS.
Gdy ukaże się pytanie o rejestrację GDAL, odpowiedz Yes. Odpowiedz Yes również na pytania dotyczące ustawienia POSTGIS_ENABLED_DRIVERS i włączenia zmiennej środowiskowej POSTGIS_ENABLE_OUTDB_RASTERS.
Po zakończeniu w menu Start systemu Windows powinien pojawić się folder PostgreSQL zawierający skróty i linki do dokumentacji.
Jeśli wystąpią jakiekolwiek problemy z instalacją PostgreSQL, zapoznaj się z rozdziałem „Troubleshooting” w dokumentacji EDB na stronie https://www.enterprisedb.com/resources/product-documentation/. Jeśli nie możesz zainstalować PostGIS za pomocą Stack Builder, spróbuj pobrać instalator ze strony PostGIS http://postgis.net/windows_downloads/ i zapoznaj się z dokumentacją na stronie http://postgis.net/documentation/.
Instalacja w macOS
Użytkownikom macOS polecam pobranie aplikacji open source Postgres.app, która zawiera PostgreSQL, rozszerzenie PostGIS oraz kilka innych gadżetów:
Wejdź na http://postgresapp.com/ i pobierz plik z obrazem aplikacji, który ma rozszerzenie .dmg.
Dwukrotnie kliknij plik .dmg w celu jego otwarcia, a następnie przeciągnij i upuść ikonę aplikacji do folderu Applications.
Dwukrotnie kliknij ikonę aplikacji. Gdy aplikacja Postgres.app zostanie uruchomiona, kliknij Initialize w celu utworzenia i uruchomienia bazy PostgreSQL.
Ikona małego słonia na pasku menu oznacza, że masz uruchomioną bazę danych. Aby skorzystać z dołączonego do PostgreSQL narzędzia udostępniającego wiersz poleceń, musisz uruchomić aplikację Terminal i wykonać następujący kod w wierszu poleceń (możesz skopiować go jako pojedynczą linię ze strony aplikacji Postgres.app https://postgresapp.com/documentation/install.html):
Ponieważ Postgres.app nie zawiera pgAdmin, musisz teraz wykonać następujące kroki, aby go pobrać i uruchomić:
Wejdź na stronę pgAdmin z plikami dla macOS: https://www.pgadmin.org/download/pgadmin-4-macos/.
Wybierz najnowszą wersję i pobierz instalator (szukaj pliku obrazu z rozszerzeniem .dmg).
Dwukrotnie kliknij plik .dmg, zaakceptuj warunki korzystania, a następnie przeciągnij ikonę słonia aplikacji pgAdmin do folderu Applications.
Dwukrotnie kliknij ikonę aplikacji w celu uruchomienia pgAdmin.
UWAGA
W systemie macOS przy pierwszym uruchomieniu pgAdmin może pojawić się okno dialogowe z komunikatem „pgAdmin4.app can’t be opened because it is from an unidentified developer”. Wtedy kliknij prawym przyciskiem myszy ikonę i wybierz Open. Następne okno dialogowe powinno dać możliwość otwarcia aplikacji; w przyszłości Twój Mac będzie już pamiętał o wprowadzonym ustawieniu.
Instalacja w macOS jest stosunkowo prosta, ale jeśli napotkasz jakiś problem, zajrzyj do dokumentacji Postgres.app na stronie https://postgresapp.com/documentation/ lub dla pgAdmin na stronie https://www.pgadmin.org/docs/.
Instalacja w Linuksie
Jeśli jesteś użytkownikiem Linuksa, instalacja PostgreSQL jest jednocześnie i łatwa, i trudna, co według mojej opinii wynika z reguł obowiązujących w świecie tego systemu. Najpopularniejsze dystrybucje Linuksa – w tym Ubuntu i CentOS – zawierają PostgreSQL w standardowej wersji dystrybucji. Niektóre z tych dystrybucji są aktualizowane częściej niż inne. Najlepszym sposobem sprawdzenia, w jaki sposób dodać PostgreSQL lub zainstalować jego najnowszą wersję, jest sprawdzenie dokumentacji wybranej dystrybucji.
Jednak projekt PostgreSQL zawiera w repozytorium aktualne wersje pakietów dla różnych wersji Red Hat, Debian i Ubuntu. Wejdź na strony: https://yum.postgresql.org/ oraz https://wiki.postgresql.org/wiki/Apt, aby uzyskać więcej informacji. Pakiety, które są dostępne, obejmują klienta i serwer PostgreSQL, pgAdmin (jeśli jest dostępny), PostGIS i PL/Python. Dokładne nazwy tych pakietów będą się różnić w zależności od wybranej dystrybucji Linuksa. Konieczne może być również ręczne uruchomienie serwera bazy danych PostgreSQL.
pgAdmin rzadko jest częścią dystrybucji Linuksa. Aby go zainstalować, zajrzyj na stronę pgAdmin pod adresem https://www.pgadmin.org/download/ w celu uzyskania dodatkowych informacji oraz sprawdzenia, czy Twoja wersja systemu jest wspierana. Jeśli czujesz się na siłach, możesz skorzystać z instrukcji budowy aplikacji z kodu źródłowego na stronie dostępnej na stronie https://www.pgadmin.org/download/pgadmin-4-source-code/.
Praca z pgAdmin
Zanim zaczniesz pisać kod, zapoznaj się z pgAdmin, czyli narzędziem administracyjnym i zarządzającym dla PostgreSQL. Jest ono bezpłatne, ale nie bagatelizuj jego wydajności. W rzeczywistości pgAdmin jest w pełni funkcjonalnym narzędziem podobnym do narzędzi płatnych, jak Microsoft SQL Server Management Studio, pozwalającym na kontrolę wielu aspektów działania serwera. Posiada graficzny interfejs do konfigurowania i administrowania serwerem PostgreSQL i bazami danych, a także – co jest najbardziej przydatne w przypadku tej książki – oferuje narzędzie służące do ich pisania, testowania i zapisywania zapytań SQL.
Jeśli korzystasz z systemu Windows, pgAdmin powinien pochodzić z pakietu PostgreSQL pobranego z EnterpriseDB. W menu Start wybierz PostgreSQL ► pgAdmin 4 (numer wersji Postgres powinien też pojawić się w menu). Jeśli używasz macOS i pgAdmin został zainstalowany odrębnie, kliknij ikonę pgAdmin w folderze Applications, upewniając się, że Postgres.app także został uruchomiony.
Po uruchomieniu programu pgAdmin okno powinno wyglądać podobnie jak na rysunku 1.
Rysunek 1. Ekran powitalny pgAdmin w wersji macOS
Pionowe okienko z lewej strony wyświetla przeglądarkę obiektów, w której można zobaczyć dostępne serwery, bazy danych, użytkowników i inne obiekty. W górnej części ekranu znajduje się zbiór pozycji menu, a poniżej zakładki do wyświetlania różnych właściwości obiektów bazy danych i jej wydajności.
Wykonaj teraz następujące kroki, aby połączyć się z domyślną bazą danych:
W przeglądarce obiektów rozwiń znak plusa (+) po lewej stronie węzła Servers, aby wyświetlić domyślny serwer. W zależności od systemu operacyjnego domyślną nazwą serwera może być localhost lub PostgreSQL x, gdzie x to numer wersji PostgreSQL.
Kliknij dwukrotnie nazwę serwera. Na żądanie wprowadź hasło wybrane podczas instalacji. Podczas nawiązywania połączenia przez pgAdmin pojawi się krótki komunikat. Po podłączeniu pod nazwą serwera powinno pojawić się kilka nowych elementów.
Najpierw rozwiń Databases, a następnie domyślną bazę danych postgres.
Dla postgres rozwiń obiekt Schemas, a potem public.
Twój panel przeglądarki obiektów powinien wyglądać jak na rysunku 2.
UWAGA
Jeśli pgAdmin nie pokazuje serwera domyślnego w węźle Servers, musisz go dodać. Kliknij prawym przyciskiem myszy Servers i wybierz opcję Create Server. W oknie dialogowym na karcie General wpisz nazwę swojego serwera. W zakładce Connection, w polu Host name/address box wpisz localhost. Kliknij Save, a na liście powinien pojawić się Twój serwer.
Ta kolekcja obiektów opisuje każdą właściwość serwera bazy danych. Jest ich wiele, ale na razie skupimy się na lokalizacji tabel. Aby wyświetlić strukturę tabeli lub wykonać działanie za pomocą polecenia pgAdmin, to w tym miejscu możesz uzyskać dostęp do tabeli. W rozdziale 1. użyjesz tej przeglądarki do utworzenia nowej bazy danych oraz pozostawienia bez zmian domyślnej bazy postgres.
Dodatkowo pgAdmin zawiera narzędzie Query Tool, w którym tworzysz i uruchamiasz kod. W celu otworzenia narzędzia Query Tool w przeglądarce obiektów pgAdmin kliknij raz na dowolną bazę danych, aby ją podświetlić. Na przykład kliknij bazę danych postgres, a następnie wybierz Tools ► Query Tool. Narzędzie Query Tool ma dwa panele: jeden do tworzenia zapytań, a drugi do przeglądania ich rezultatów.
Można otworzyć wiele zakładek w celu połączenia się i pisania zapytań dla różnych baz danych lub po prostu w celu uporządkowania kodu w sposób, w jaki chcesz. Aby otworzyć kolejną zakładkę, kliknij na inną bazę danych w przeglądarce obiektów i ponownie otwórz Query Tool przez menu.
Rysunek 2. Przeglądarka obiektów pgAdmin
Alternatywa dla pgAdmin
Wprawdzie pgAdmin jest dobry dla początkujących, nie musisz go używać. Jeśli wolisz inne narzędzie administracyjne, które działa z PostgreSQL, możesz je wykorzystać. Jeżeli we wszystkich ćwiczeniach chcesz korzystać z wiersza poleceń systemu z tej książki, w rozdziale 16. znajdziesz instrukcje na temat używania PostgreSQL-owego narzędzia dla wiersza poleceń – psql. (Suplement zawiera listę dodatków PostgreSQL, wśród których możesz poszukiwać uzupełniających narzędzi administracyjnych).
Podsumowanie
Teraz, gdy masz zainstalowany PostgreSQL i pgAdmin, możesz zacząć uczyć się języka SQL i wykorzystywać go do odkrywania cennych informacji w swoich danych!
W rozdziale 1. dowiesz się, jak utworzyć bazę danych i tabelę, a następnie załadujesz dane, aby przeglądać ich zawartość. Zaczynamy!
1
TWORZENIE PIERWSZEJ BAZY DANYCH I TABELI
SQL to coś więcej niż narzędzie do pozyskiwania wiedzy z danych. Jest to również język do definiowania struktur przechowujących dane, dzięki czemu możemy tworzyć relacje między danymi. Najważniejszą ze struktur jest tabela.
Tabela to siatka wierszy i kolumn przechowujących dane. Każdy wiersz zawiera kolekcję kolumn, a każda kolumna zawiera dane określonego typu: najczęściej są to cyfry, znaki i daty. Za pomocą SQL możemy określić strukturę tabeli oraz ich wzajemne relacje w bazie danych. SQL może również zostać użyty do pobierania lub odpytywania danych z tabel.
Zrozumienie tabel jest podstawą do zrozumienia danych w bazie danych. Za każdym razem, gdy zaczynam pracę z nową bazą danych, najpierw zajmuję się tabelami wewnątrz. Szukam wskazówek w nazwach tabel i strukturze kolumn. Czy tabele zawierają tekst, liczby, czy jedno i drugie? Ile wierszy znajduje się w każdej tabeli?
Następnie sprawdzam, ile tabel jest w bazie danych. Najprostsza baza danych może mieć pojedynczą tabelę. Pełna aplikacja obsługująca dane klientów lub śledząca połączenia lotnicze może mieć ich dziesiątki lub setki. Liczba tabel informuje nas nie tylko o tym, ile danych będzie trzeba przeanalizować, ale także o potrzebie zbadania powiązań między danymi z poszczególnych tabel.
Zanim zaczniesz zgłębiać SQL, spójrz na przykład, jak może wyglądać zawartość tabel. Będziemy używać hipotetycznej bazy danych do zarządzania szkolną rejestracją; w tej bazie danych znajduje się kilka tabel, które opisują uczniów i ich klasy. Pierwsza tabela, o nazwie student_enrollment, przechowuje dane uczniów, którzy są przypisani do poszczególnych zajęć lekcyjnych:
Ta tabela pokazuje, że dwóch uczniów zapisało się na COMPSCI101, a trzech na ENG101. Ale gdzie są szczegóły dotyczące każdego ucznia i lekcji? W tym przykładzie są one przechowywane w oddzielnych tabelach zwanych students i classes, a każda z tych tabel odnosi się właśnie do student_enrollment. Tu zaczyna się pojawiać siła relacyjnej bazy danych.
Kilka początkowych wierszy tabeli students zawiera następujące dane:
Tabela students zawiera szczegółowe informacje o każdym uczniu, natomiast w celu ich identyfikacji używa wartości w kolumnie student_id. Ta wartość działa jak unikalny klucz, który łączy obie tabele, co daje możliwość tworzenia wierszy takich jak poniższe z kolumną class_id ze student_enrollment oraz first_name i last_name ze students:
Tabela classes działa w ten sam sposób, zawierając kolumnę class_id i kilka kolumn ze szczegółami dotyczącymi klasy. Twórcy baz danych wolą organizować dane w postaci osobnych tabel dla każdego głównego bytu przechowywanego przez bazę danych w celu ograniczenia redundancji. W przykładzie zapisujemy nazwisko każdego ucznia i datę urodzenia tylko raz. Nawet jeśli uczeń zarejestruje się na wielu zajęciach – tak jak zrobił to Davis Hernandez – nie marnujemy przestrzeni bazy danych, każdorazowo wpisując jego imię obok każdej klasy w tabeli student_enrollment. Wprowadzamy jedynie numer identyfikacyjny ucznia.
Biorąc pod uwagę, że tabele stanowią podstawę wszystkich baz danych, w tym rozdziale rozpoczniesz przygodę z korzystaniem z SQL, tworząc tabelę wewnątrz nowej bazy danych. Następnie załadujesz do niej dane i wyświetlisz wypełnioną tabelę.
Tworzenie bazy danych
Program PostgreSQL, który został przez Ciebie pobrany we „Wprowadzeniu”, to system zarządzania bazą danych, czyli pakiet oprogramowania umożliwiający tworzenie, zarządzanie i wysyłanie zapytań do baz danych. Po zainstalowaniu PostgreSQL został utworzony serwer bazy danych – instancja aplikacji działająca na Twoim komputerze – która zawiera domyślną bazę danych o nazwie postgres. Baza danych to zbiór obiektów, wśród których znajdują się tabele, funkcje, role użytkowników i wiele innych. Zgodnie z dokumentacją PostgreSQL domyślna baza danych jest „przeznaczona do użytku przez użytkowników, narzędzia i aplikacje innych firm” (zobacz https://www.postgresql.org/docs/current/static/app-initdb.html). W ćwiczeniach w tym rozdziale, zamiast utworzenia nowej, zostawimy domyślną bazę danych. Zrobimy tak w celu pozostawienia razem obiektów związanych z konkretnym tematem lub aplikacją.
Aby utworzyć bazę danych, wystarczy użyć tylko jednej instrukcji SQL, pokazanej na listingu 1.1. Ten kod, wraz ze wszystkimi przykładami z tej książki, jest możliwy do pobrania z zasobów dostępnych na https://www.nostarch.com/practicalSQL/.
Listing 1.1. Tworzenie bazy danych o nazwie analysis
Ta instrukcja tworzy na serwerze bazę danych o nazwie analysis przy użyciu domyślnych ustawień PostgreSQL. Zauważ, że kod składa się z dwóch słów kluczowych – CREATE i DATABASE – po których następuje nazwa nowej bazy danych. Instrukcja jest zakończona średnikiem sygnalizującym koniec polecenia. Średnik kończy wszystkie instrukcje PostgreSQL i jest częścią standardu ANSI SQL. Czasami możesz go pominąć, ale nie zawsze, a w szczególności nie podczas uruchamiania wielu instrukcji jako administrator. Używanie średnika jest zatem dobrym nawykiem.
Wykonywanie SQL w pgAdmin
W ramach „Wprowadzenia” do tej książki została omówiona instalacja graficznego narzędzia administracyjnego pgAdmin (jeśli wtedy nie zostało ono przez Ciebie zainstalowane, zrób to teraz). W przypadku większości naszych przykładów użyjesz pgAdmin do uruchomienia (lub wykonania) zapisanych przez nas instrukcji SQL. W dalszej części książki, w rozdziale 16., pokażę Ci, jak uruchamiać instrukcje SQL w oknie terminala za pomocą PostgreSQL-owego programu do obsługi wiersza poleceń – psql. Rozpoczęcie pracy jest jednak nieco łatwiejsze dzięki interfejsowi graficznemu.
Użyjemy pgAdmin do uruchomienia instrukcji SQL z listingu 1.1, która tworzy bazę danych. Następnie połączymy się z nową bazą danych i utworzymy tabelę. W tym celu wykonaj następujące kroki:
Uruchom PostgreSQL. Jeśli używasz systemu Windows, instalator ustawił PostgreSQL, aby uruchamiał się przy każdym starcie systemu. W systemie MacOS należy dwukrotnie kliknąć Postgres.app w folderze Applications.
Uruchom pgAdmin. Zgodnie z postępowaniem we „Wprowadzeniu”, aby wyświetlić domyślny serwer w okienku z lewej strony (w przeglądarce obiektów), rozwiń znak plusa po lewej stronie węzła Servers. W zależności od sposobu instalacji PostgreSQL domyślny serwer może mieć nazwę localhost lub PostgreSQL x, gdzie x jest wersją aplikacji.
Kliknij dwukrotnie nazwę serwera. Jeśli zostało podane hasło podczas instalacji, wpisz je w wierszu poleceń. Zobaczysz krótki komunikat informujący, że pgAdmin nawiązuje połączenie.
W przeglądarce obiektów pgAdmin rozwiń Databases i kliknij raz na bazę postgres, aby ją podświetlić, jak pokazano na rysunku 1.1.
Otwórz Query Tool, wybierając Tools ► Query Tool.
W okienku SQL Editor (górne poziome okno) wpisz lub skopiuj kod z listingu 1.1.
Aby wykonać instrukcję, kliknij podświetloną ikonę błyskawicy. PostgreSQL utworzy bazę danych, a w okienku Output w Query Tool w zakładce Messages wyświetli się informacja o tym, że zapytanie zostało pomyślnie wykonane, jak pokazano na rysunku 1.2.
Aby zobaczyć nową bazę danych, w przeglądarce obiektów kliknij prawym przyciskiem myszy Databases. Z podręcznego menu wybierz Refresh, a na liście pojawi się baza danych analysis, jak pokazano na rysunku 1.3.
Rysunek 1.1. Łączenie z domyślną bazą danych postgres
Rysunek 1.2. Tworzenie bazy danych analysis
Rysunek 1.3. Widok na bazę danych analysis w przeglądarce obiektów
Dobra robota! Masz teraz bazę danych nazwaną analysis, której możesz użyć do większości ćwiczeń z tej książki. W trakcie normalnej pracy najlepiej jest utworzyć nową bazę danych dla każdego projektu, aby przechowywać razem tabele z danymi odpowiadające danemu problemowi.
Połączenie z bazą danych analysis
Przed utworzeniem tabeli należy się upewnić, że pgAdmin jest podłączony do bazy danych analysis, a nie do domyślnej bazy danych postgres. Aby to zrobić, wykonaj następujące kroki:
Zamknij Query Tool przez kliknięcie X w prawym górnym rogu narzędzia. Nie musisz zapisywać pracy, kiedy zostaniesz o to zapytany.
W przeglądarce obiektów kliknij na bazę danych analysis.
Ponownie otwórz Query Tool, wybierając Tools ► Query Tool.
Zobaczysz nazwę analysis on postgres@localhost w górnej części okienka Query Tool. (Ponownie Twoja wersja zamiast localhost może pokazać PostgreSQL.)
Od tego momentu dowolny kod, który wykonasz, zostanie zastosowany do bazy danych analysis.
Tworzenie tabeli
Jak już wspomniałem wcześniej, tabele zawierają dane i ich wzajemne powiązania. Podczas tworzenia tabeli przypisujesz nazwę do każdej kolumny (czasami widniejącej jako pole lub atrybut) i przypisujesz jej typ danych. Są to wartości akceptowane przez kolumnę – takie jak tekst, liczby całkowite, liczby dziesiętne i daty – definicja typu danych zaś to jeden ze sposobów, w jaki SQL wymusza integralność danych. Na przykład kolumna zdefiniowana jako data pobierze dane w jednym z kilku standardowych formatów, takich jak YYYY-MM-DD. Jeśli spróbujesz wpisać znaki niepasujące do formatu daty, na przykład słowo brzoskwinia, otrzymasz błąd.
Dane przechowywane w tabeli można pobierać, analizować lub wysyłać do nich zapytania za pomocą instrukcji SQL. Możesz sortować, edytować i przeglądać dane, a w przyszłości także zmieniać definicję tabeli, jeśli będzie taka potrzeba.
Utwórzmy tabelę w bazie danych analysis.
Polecenie CREATE TABLE
W tym ćwiczeniu wykorzystamy często omawiane dane: wynagrodzenia nauczycieli. Listing 1.2 pokazuje instrukcję SQL do utworzenia tabeli o nazwie teachers:
Listing 1.2. Tworzenie tabeli o nazwie teachers z sześcioma kolumnami
Ta definicja tabeli nie jest zbyt obszerna. Na przykład brakuje kilku ograniczeń będących zapewnieniem, że kolumny, które muszą zostać wypełnione, rzeczywiście zawierają dane lub że nie wprowadzamy przypadkowo podwójnych wartości. Szczegółowo ograniczenia omówię w rozdziale 7., w tych wczesnych rozdziałach pomijam je, by skupić się na tym, aby można było zacząć eksplorować dane.
Kod zaczyna się od dwóch słów kluczowych SQL 1 CREATE i TABLE, które wraz ze słowem teachers sygnalizują PostgreSQL, że następny fragment kodu opisuje tabelę, która ma zostać dodana do bazy danych. Wewnątrz okrągłych nawiasów znajduje się rozdzielona przecinkami lista nazw kolumn wraz z ich typami danych. Dla przejrzystości każda nowa linia kodu znajduje się w osobnej linii i jest wcięta czterema spacjami, co nie jest wymagane, ale czyni kod bardziej czytelnym.
Każda nazwa kolumny reprezentuje jeden konkretny element danych opisany przez typ danych. Kolumna id 2 jest typu bigserial, specjalny typ całkowitoliczbowy, który automatycznie zwiększa się za każdym razem, gdy dodajesz wiersz do tabeli. Pierwszy wiersz w kolumnie id otrzymuje wartość l, drugi wiersz 2 itd. Typ danych bigserial i inne tego rodzaju są specyficzne dla PostgreSQL, ale większość systemów baz danych ma podobne typy.
Następnie tworzymy kolumny dotyczące imienia i nazwiska nauczyciela oraz szkoły, w której uczą 3 . Każda jest typu varchar, czyli typem znakowym o maksymalnej długości określonej przez liczbę podaną w nawiasach. Zakładamy, że nikt w bazie danych nie będzie miał nazwiska składającego się z więcej niż 50 znaków. Chociaż jest to bezpieczne założenie, z czasem odkryjesz, że wyjątki zawsze Cię zaskoczą.
Dla kolumny hire_date 4 został określony typ date, a kolumna salary 5 jest typu numerycznego. Omówię dokładniej typy danych w rozdziale 3., natomiast ta tabela pokazuje niektóre najczęściej stosowane przykłady typów danych. Blok kodu jest zakończony 6 okrągłym nawiasem zamykającym i średnikiem.
Teraz, gdy już wiesz, jak wygląda SQL, uruchomimy ten kod w pgAdmin.
Tworzymy tabelę teachers
Jeśli masz swój kod i połączenie z bazą danych, możesz utworzyć tabelę przy użyciu tych samych kroków, które zrobiliśmy podczas tworzenia bazy danych:
Otwórz narzędzie pgAdmin QueryTool (jeśli nie jest otwarte, kliknij w przeglądarce obiektów pgAdmin w bazę analysis, a następnie wybierz Tools ► Query Tool).
Skopiuj skrypt CREATE TABLE z listingu 1.2 do edytora SQL.
Wykonaj skrypt przez kliknięcie podświetlonej ikony błyskawicy.
PRZYPISY
* Okręg spisowy to region geograficzny w USA stworzony w celu przeprowadzenia spisu ludności (ten i pozostałe przypisy dolne pochodzą od tłumacza).