Praktyczny SQL

Dane oryginału

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