Podstawy
4 godz. 8 min · Bazy Danych · Full-stack i Programowanie
Pierwszy rozdział wprowadza w historię powstania języka. Wspomnimy o standardach, wykorzystywanych wersjach i podzbiorach SQL’a. Zaraz potem wskoczymy w pierwsze poważne operacje, które pozwolą na pełny cykl tworzenia, zasilania bazy danych i wyciągnięcia z niej wybranych informacji. Rozwikłamy też tajemniczy akronim - CRUD.
Skoro potrafimy już tworzyć bazę i tabele, w które możemy wstawić nowe wiersze, dowiemy się więcej o bardzo ważnej funkcji bazy danych: weryfikacji danych. Zostaną zaprezentowane mechanizmy i techniki, które pomogą utrzymać spójność danych.
W kolejnych lekcjach znajduje się opis serca SQL’a, czyli polecenia wybierającego dane i możliwości i opcji z nim związanych. Od przekształcenia bazy danych w prosty kalkulator, aż do warunków filtrowania wybieranych danych (klauzulą WHERE), sortowania ich (klauzulą ORDER BY), czy ograniczania dużych zestawów wynikowych - w tym wsparcia do paginacji.
SQL jest językiem obsługi relacyjnych baz danych. Dlatego najwyższy czas, aby wspomnieć o relacjach i tym w jaki sposób rozszerzają one możliwości wyszukiwania danych oraz pozwalają zobrazować powiązania między danymi w bazie. Dowiesz się o różnych rodzajach połączeń między tabelami i jak te połączenia (zwane JOIN’ami) pozwalają wybrać to, czego potrzebujesz.
Kolejny rozdział dotyczy grupowania danych (wykorzystując klauzulę GROUP BY) i związanych z tym nowymi możliwościami. Dowiesz się tutaj też w jaki sposób agregować pogrupowane dane i jak takie zagregowane dane filtrować.
Przekonasz się, w jaki sposób można zapamiętywać trudne lub często wykonywane zapytania, wykorzystując do tego widoki (VIEWS) i jak można poprawić wydajność pobierania z nich danych. Przykładowo zastosujemy widoki zmaterializowane (MATERIALISED VIEWS). W tym rozdziale przećwiczysz też możliwość zapisywania danych przez widoki.
Z baz danych korzysta zwykle wielu użytkowników. Aby operacje, które wykonują na wspólnej bazie nie spowodowały nieoczekiwanych konsekwencji, wykorzystuje się mechanizm transakcji. W tym rozdziale wytłumaczymy Ci w jaki sposób użytkownicy mogą modyfikować równolegle te same dane. Tutaj też dowiesz się o poziomach izolacji transakcji i jak używać ich w różnych sytuacjach bazodanowych.
Przy pracy z większą ilością danych, bardzo szybko doświadczysz spowolnienia wykonywania zapytań. O tym jak je przyspieszyć dowiesz się właśnie w tym rozdziale. Zwiększanie wydajności zapytań jest tematem rozległym i zaawansowanym. Tutaj będziesz miał szansę poznać podstawy, w tym - po co są i jak używać indeksów, w jaki sposób analizować jak zapytania są wykonywane i jakie jeszcze są metody wydajniejszego odczytywania danych.
Kurs jest dla osób początkujących, rozpoczynających przygodę z bazami danych. Nie wymaga się znajomości SQL'a ani zaawansowanej wiedzy dotyczącej baz danych. Warto wiedzieć co to są relacyjne bazy danych. Dobrze mieć dostęp do ulubionego silnika bazy danych, ale nie jest to wymagane.
Zanim przejdziemy do elementów wydajnościowych związanych z widokami
chcę ci pokazać w jaki sposób wygenerować sobie używając tylko sql
dużo danych testowych takie dane będą nam potrzebne żeby
móc zademonstrować przydatność widoków zmaterializowanych o
których opowiem ci na następnej lekcji będę chciał wygenerować
paręset tysięcy rekordów i to wystarczy na nasze potrzeby ale
można rozszerzyć podaną metodę nawet na setki milionów
wygenerowanych rekordów jeśli będziesz chciał proces generowania
takich danych przez który cię przeprowadzę będzie kapitalnym
poligonem który posłuży do przedstawienia paru dodatkowych mechanizmów
sql które przydadzą ci się przy pracy z bazami danych w
ogóle generowanie danych jest kapitalnym sposobem na
przeprowadzenie testów wydajnościowych tak zapytań do bazy jaki i
aplikacji czy serwisu działających na tejże bazie wtedy
często okazuje się że zapytanie które szybko i sprawnie działa
na zapisanych kilkuset wierszach tabeli zaczyna mulić i
trwa godzinami przy docelowym zapełnieniu bazy danych jeśli
chodzi o rozwiązania do zadania z poprzedniej lekcji omówię je na
początku następnej o zmaterializowanych widokach będzie
to lepsze wprowadzenie do tamtego tematu a teraz do
bazy stworzymy sobie w takim
razie tabelę big
data w której wrzucimy sobie
trzy kolumny jedna niech to będzie identyfikator tak
jak trzeba i to w dodatku niech on się generuje i niech
będzie to nasz klucz główny a co do tego weźmiemy
sobie jakiś duży dużą wartość całkowitą
weżmiemy sobie jakiś tekst zwykle
dużo ważą w sensie takie teksty zwykle zajmują więcej
miejsca i są trudniejsze w obróbce więc
może to nam da trochę więcej czasu przetwarzania i może jakąś wartość
niech to będzie losowa cena o
i niech już czeka na wypełnienie
no teraz żebyśmy mogli ją wypełnić odpowiednimi
danymi to co musimy zrobić jeżeli chodzi o wartość
liczbową to możemy wylosować wygenerować losową
liczbę natomiast tekstem no tutaj już
jest trudniejsza sprawa to może nam świetnie posłużyć żeby
przedstawić parę parę ciekawych troche bardziej zaawansowanych
technik obróbki danych oczywiście wszystko w ramach standardowego
sql żeby móc wygenerować jakiś
tekst a właściwie wiele rekordów z
losowym tekstem musimy w ogóle móc wygenerować jakieś
dane czyli zestaw rekordów z danymi
jeżeli nie mamy żadnej tabeli no to nie jest
to takie oczywiste natomiast sql daje nam tutaj
pewne możliwości po pierwsze typem danych który
można używać w sql są tablice
w różnych silnikach różnie się je
deklaruje akurat tutaj w postgresie można zadeklarować pisząc
słowo kluczowe array i wpisując jakieś
wartości do tablicy i dostajemy
tablice z jakimiś wartościami tablicę konkretnego typu
oczywiście jest to klasyczny array więc musi
być typowany a teraz pojawia się znienacka
niesamowita funkcja pod tytułem unnest funkcja unnest
bieże zestaw danych w postaci tablicy i
tworzy z niej zestaw wierszy proszę
bardzo mamy wygenerowane 5 wierszy z
jakimiś wartościami które akurat zostały wzięte z tej z
tej tablicy skoro unnest daje nam rezultat
takijakby to była tabela czyli po prostu źródło wierszy to
możemy tak to potraktować i użyć tego w zapytaniu select
jako po prostu źródła danych czyli w klauzuli
from zgodnie ze standardem musimy nadać nazwę jakoś
temu źródle danych niech to będą
single values i jeszcze rozszerzymy raz dwa
trzy cztery pięć sześć siedem
osiem dziewięć tak żeby mieć żebyśmy mieli pełną dziesiątkę
i w ten sposób efekt jest ten sam oczywiście
nazwa kolumny zwracanej będzie
nazwą funkcji ale tym się na razie nie martwimy
no dobrze mamy 10 wierszy jak moglibyśmy
zrobić wygenerować więcej nie tworząc
tablicy z setką albo tysiącem pozycji
tu z pomocą przychodzi wspomniany już wcześniej
typ łączenia danych czyli cross join czyli
po prostu iloczyn kartezjański dwóch zbiorów jednym z naszych zbiorów jest
właśnie tablica wartości od 0 do 9
moglibyśmy do tego dołączyć drugą
taką tablicę drugie takie źródło danych tylko nazwijmy jakoś je
inaczej na razie niech to będą extended values i co się
stanie teraz jak złączymy te dwie tabele kartezjański
iloczyn jak w twarz strzelił ale jak się przyjrzymy to
tak mamy po lewej jakąś wartość jedną zero
po prawej mamy liczbę od 1 do 9 a później wartość 1 po
lewej stronie i znowu po prawej od 0 do 9 czy to czegoś
nie przypomina no właśnie może jesteśmy w stanie połączyć te
dwie te dwie kolumny w jedną nazwijmy tą
kolumnę one tą kolumne two i
możemy zrobić one razy
10 plus two i proszę bardzo 100
wartości z dwóch takich tablic tutaj troszkę uporządkuję
żeby było nieco czytelniej co tu się dzieje no dobrze skoro
tak to może nazwijmy troszkę inaczej te nasze
kolumny to nie jest one tylko tens dziesiątki
a to są jedności to nazwiemy ones to są dziesiątki tens
a tutaj są jedności ones skoro tak
to może możemy jeszcze dorzucić tutaj
jeszcze jedną jeszcze jeden rząd wielkości o i
to będą hundreds i możemy sobie to wykorzystać tutaj
dodając razy sto plus ciach
tylko że ewidentnie jest nie pouporządkowany
więc order by po prostu numbers ciach
i mamy wartości tutaj od od
0 aż do do 1000 takie wartości
bardzo nam się przydadzą do wygenerowania naszych ciągów znaków
więc zamienimy to w widok tak
żebyśmy mogli się odnosić do tego zapytania nie musieć go przepisywać
sprawimy że nasze następne zapytania tudzież
widoki będą czytelniejsze nazwijmy go generate
data i ciach
mamy zapisany taki widok to nam generuje po prostu ciąg liczb
od 0 do 1000 no dobrze
a po co nam te liczby czy w ogóle przydadzą nam
się do tego do czegoś w tym momencie dobrze wiedzieć że jest
coś takiego jak kod ascii który każdemu znakowi
przyporządkowuje pewną wartość kody ascii można podejrzeć
w wielu miejscach na przykład tutaj jest strona www
ascii table com gdzie można zobaczyć jakie znaki
przyporządkowane mają jakie wartości jest dużo znaków niewidocznych
typu znak końca linii znak kasowania
i tak dalej ale nas interesują te znaki widoczne
od spacji wartości 32 do powiedzmy do tyldy czyli
126 w takim razie wybierzmy sobie z
naszego widoku generate data
tylko te wartości od 32 do 126
wiesz już o tym że można użyć słowa kluczowego limit
żeby ograniczyć ilość zwracanych wierszy więc
to nam się przyda natomiast chcielibyśmy zacząć ten nasz
zbiór danych nie od pierwszego wiersza tylko od 32
i jak już wspominałem wcześniej do tego słuzy słowo
offset 31 to nam zwróci zestaw danych
który zacznie się od 31 wartości i teraz potrzebujemy tylko
limit na tym limit to jest 126
minus 31 dzięki temu mamy ostatni
rekord na 125 a chcieliśmy 126
uwzględnić więc zrobimy tak no dobrze a teraz co możemy zrobić z tymi
wartościami jest taka funkcja sql nazywa się
chr która bierze wartość i zamienia ją na
znak skoro wiemy jakie wartości graniczne
nas interesują możemy po prostu ograniczyć zbiór
danych do tych wartości i mamy proszę od wykrzyknika aż
do tyldy tak jak żeśmy to sobie założyli no
dobrze ale jakbyśmy chcieli z tego skleić jakieś słowo no to
każdy znak mógłby się tylko raz tam pojawić chcemy to troszkę rozszerzyć
tak żebyśmy mogli większe słowa tworzyć i tu znowu przyda
nam się nasz widok generate data który ma strasznie dużo wierszy
i pozwoli nam zwielokrotnić te nasze znaki
tak żeby był większy zbiór z którego można będzie składać
te losowe słowa jak to zrobimy może masz pomysł
jak można by zwielokrotnić ten nasz zestaw danych no
właśnie podobnie jak w naszym widoku czyli używając cross
join i użyjemy cross join musimy jakoś rozróżnić
te dwie te dwa źródła danych nazwijmy tutaj generate
data 1 generate data 2 to jest ważne też
dlatego żeby silnik bazy wiedział
do kolumn którego źródła danych się odwołujemy tutaj jeżeli spróbuję
uruchomić to zapytanie to baza mi krzyknie że kolumna
numbers jest niejednoznaczna dlatego że występuje
i gd1 i gd2 więc muszę powiedzieć konkretnie
o którą mi chodzi w ten sposób i mamy
zmultiplikowany wielokrotnie proszę w
tysiącach nasz zestaw znaków teraz co możemy z nim zrobić
żeby złożyć jakieś słowa należałoby zagregować
te znaki żeby można było z zagregować
to trzeba pogrupować po jakiejś wartości pomysł
tutaj jest taki żeby dodać jeszcze jedną kolumnę
w których byłyby jakieś wartości powiedzmy od 1
do 1000 po których można by było grupować w ten sposób
część tych znaków zostanie przypisana do jedynki
część do dwójki i w ten sposób stworzą jakieś słowa
dziwne bo dziwnie ale chodzi nam o ciągi znaków może nie słowa a ciągi
znaków w takim razie zróbmy tak to nasze zapytanie które nam generuje
zestaw znaków zapiszmy jako widok
nasz widok się zapisał i teraz możemy go użyć będziemy
chcieli zagregować znaki do tego nadaje się świetnie funkcja
agregująca string agg agregacja znaków
polega na sklejaniu ich trzeba też tutaj podać w argumencie drugim
czym będziemy je sklejać czyli separator my chcemy żeby
to był po prostu pusty string nie będziemy niczym oddzielać i
to będzie nasz losowy ciąg znaków do tego potrzebujemy losowej liczby
zrobimy zaokrąglenie dlatego że funkcja random która
w każdym silniku jest troszkę inna ale w każdym istnieje
funkcja która generuje losowe wartości między zerem
a jednym tu akurat w postgresie nazywa się random a będziemy chcieli wygenerować
tych wierszy no powiedzmy że będziemy potrzebowali
dużo więc 10 tysięcy i będziemy korzystać z
widoku generate chars i będziemy grupować po
kolumnie render zobaczmy co dostaniemy proszę
bardzo piękne bazgrołki ale mamy jakieś ciągi
znaków można oczywiście pobawić się i uwzględnić
tylko alfanumeryczne znaki tak żeby było jakoś
sensowniejsze ale dla nas to wystarczy chodzi o to żeby zapełnić jakimś
tekstem tabele to nasze zapytanie zwraca losowe
ciągi znaków znowu dla uproszczenia stwórzmy sobie widok
nie musimy mieć kolumny rnd
możemy po prostu te wartości po których grupujemy
wrzucić do klauzuli group by i efekt będzie taki
sam tylko że będziemy mieli jedną kolumnę tą którą chcemy wykorzystać
i taki widok sobie stwórzmy w tym momencie chyba
mamy wszystko do tego żeby wypełnić naszą tabelę big data jak
wiadomo polecenie insert into może opierać się
też na kwerendzie nie musi być to jeden
wiersz z danymi i w takiej postaci po prostu wystarczy
napisać insert into tabela i już tutaj z czego
wybieramy dane do big data jedna tylko rzecz ponieważ mamy
kolumnę identyfikatora która sama się będzie wypełniała więc
musimy podać do jakich kolumn będziemy wrzucać dane czyli
big number sam tekst i random price jeżeli
dobrze pamiętam natomiast zapytaniem które będzie z
którego będzie insert czerpał dane będzie następujące po pierwsze
duża liczba którą tutaj chcemy to może
być losowa liczba tak więc niech to będzie random
razy no pojedziemy z zerami
a właściwie lepiej nie z zerami duże
liczby dziesiętne możemy zapisywać w notacji
naukowej to znaczy 10
do potęgi siódmej a ponieważ to jest wartość
całkowita w tej kolumnie musimy nie będziemy rzutować wystarczy że
zaokrąglimy wynik randoma do wartości
całkowitej tekst wyciągniemy z naszego widoku generate
czas a losową cenę też wygenerujemy przez funkcję random
z tym że w tym wypadku już musimy rzutować to
na wartość decimal z dwoma miejscami po przecinku źródłem
danych tutaj jest widok generate chars tu się
pomyliłem dlatego że kolumną tutaj jest oczywiście chr dane
będziemy brać się z naszego widoku random strings i
tutaj będziemy wrzucać też losowy tekst i takie
zapytanie insert powinno nam wprowadzić kilkadziesiąt tysięcy no z 10
tysięcy około 10 000 wierszy a że
chcielibyśmy tych wierszy mieć no trochę więcej bo 10 000 to nie
jest dużo jak na bazę danych i nie zauważylibyśmy dużej różnicy
w odczytywaniu danych przez widok
zmaterializowany i po prostu przez zapytanie więc dorzućmy
tutaj no jeszcze z 90 000
Generowanie dużej ilości danych · 2 min
-- Stworzenie tabeli którą chcemy zapełnić dużą ilością danych
CREATE TABLE big_data (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
big_number int,
some_text varchar(100),
random_price numeric(10,2)
);
-- Wyświetlenie tablicy 5-cio elementowej (jedno-wymiarowej)
SELECT ARRAY[0,1,2,4,5];
-- Wykorzystanie funkcji unnest do przekształcenia tablicy w zestaw rekordów
SELECT UNNEST(ARRAY[0,1,2,4,5]);
-- Zapytanie zwracające 10 cyfr w zakresie 0-9
SELECT * FROM (SELECT UNNEST(ARRAY[0,1,2,3,4,5,6,7,8,9])) AS single_values;
-- Uzyskanie dwóch unikalnych kolumn, pierwsza daje nam dziesiątki, druga jedności
SELECT * FROM (SELECT UNNEST(ARRAY[0,1,2,3,4,5,6,7,8,9])) AS single_values
CROSS JOIN (SELECT UNNEST(ARRAY[0,1,2,3,4,5,6,7,8,9])) AS extended_values;
-- Stworzenie widoku z pełnym zapytaniem generującym liczby aż do 1000
CREATE VIEW generate_data AS
SELECT hundreds*100 + tens*10 + ones AS numbers
FROM (SELECT UNNEST(ARRAY[0,1,2,3,4,5,6,7,8,9]) AS tens) AS ten
CROSS JOIN (SELECT UNNEST(ARRAY[0,1,2,3,4,5,6,7,8,9]) AS ones) AS one
CROSS JOIN (SELECT UNNEST(ARRAY[0,1,2,3,4,5,6,7,8,9]) AS hundreds) AS hundred
ORDER BY numbers;
-- Stworzenie widoku generującego zestaw liter (jak słowow) ak wyżej
CREATE VIEW generate_chars AS
SELECT CHR(gd1.numbers)
FROM generate_data gd1
CROSS JOIN generate_data gd2
WHERE gd1.numbers BETWEEN 33 AND 126;
-- Stworzenie widoku generującego losowe ciągi znaków (dzięki grupowaniu po losowej wartości)
CREATE VIEW random_strings AS
SELECT string_agg(chr, '') AS random_string
FROM generate_chars
GROUP BY ROUND(RANDOM()*10000);
-- Zapytanie wypełniające tablicę 'big_data' ok. 10000 rekordów
INSERT INTO big_data (big_number, some_text, random_price)
SELECT ROUND(RANDOM()*10^7),
random_string,
CAST(RANDOM()*1000 AS DECIMAL(10,2))
FROM random_strings;