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.
Wiesz już kiedy mogą przydać się widoki i jak je tworzyć i odczytywać
z nich dane skoro traktujemy widoki jak wirtualne tabele to
mógłbyś zapytać mikołaj czy w takim razie możemy
też zapisywać do widoków tak jak do tabel a ja na to jasne choć
z pewnymi wyjątkami jak tylko opowiem o rozwiązaniach do pracy samodzielnej to
zobaczysz jak to zrobić przypomnę że zadanie
polegało na rozszerzeniu widoku tank heroes o informację czy
dany bohater ma jakikolwiek ekwipunek czy nie najważniejszą częścią
stworzenia widoku jest napisanie zapytania które
ten widok będzie w sobie skrywał dlatego najpierw
zajmijmy się samym zapytaniem będziemy chcieli rozszerzyć
nasz widok tank heroes o informację z tabeli
equipment więc na pewno będziemy chcieli coś wyciągnąć
jakieś informacje z tank heroes użyjemy
tutaj aliasu żeby szybciej było pisać to zapytanie
i łączymy nasz widok z tabelą equipment dlatego
że z niej będziemy czerpać dodatkowe informacje musimy określić
warunek łączenia na szczęście nasz widok posiada
kolumnę hero id która jest idealną kolumną łączeniową z kolumną
hero id w ekwipunku możemy na szybko zobaczyć co dostaniemy
połączone informacje o ekwipunku dla wybranych bohaterów
z tym że brakuje tutaj jednego bohatera z naszego
widoku ponieważ chcemy zobaczyć wszystkich bohaterów z naszego widoku
przypomnij sobie w jaki sposób musimy przełączyć tabelę equipment
tak jest musimy użyć left
join to nam zapewni wszystkie rekordy z widoku
tank heroes i te z equipment które pasują proszę
bardzo jeszcze moira się załapała teraz pytanie co chcemy wyświetlać na
pewno chcemy wyświetlać informacje z naszego widoku więc użyjemy
zapisu th kropka gwiazdka który wyświetli nam wszystkie
kolumny z widoku tank heroes i chcemy dodać do tego
informację związaną z ekwipunkiem jeżeli
potrzebujemy po prostu wiedzieć czy ekwipunek jest najlepiej
by było zagregować w jakiś sposób informację o ekwipunku i na
podstawie tej agregacji wyciągnąć wniosek czy
ten ekwipunek jest czy go nie ma do agregacji
danych można tutaj użyć właściwie prawie każdej funkcji
agregującej i dodać odpowiedni warunek ja posłużę
się funkcją count żeby zliczyć ile jest
elementów ekwipunku skoro używamy
funkcji count to musimy zgrupować dane po no
niestety wszystkich kolumnach które chcemy wyświetlać czyli first name last
name i health byłbym zapomniał jeszcze
jest oczywiście kolumna hero id w ten sposób
dostaniemy zagregowane dane dla każdego z bohaterów w przypadku
moiry która nie ma żadnego ekwipunku jest to 0 więc teraz na
podstawie tych liczności elementów ekwipunku
możemy coś z tym zrobić ponieważ chcemy zamienić tą
wartość w jakiś ciąg znaków czyli napis
equipped unequipped najlepszym z tym wypadku rozwiązaniem będzie użycie
poznanej już wcześniej formuły warunkowej case when
i teraz jeżeli count jest większy od zera
then equipped a w przeciwnym przypadku unequipped
and i nazwijmy to odpowiednio w
tym momencie mamy dokładnie to co chcieliśmy listę naszych bohaterów
z widoku i odpowiedni tekst stosownie do tego czy mają
ekwipunek czy nie żeby można było zapisywać
do widoku to znaczy wykonać zapytanie insert into widok
muszą być spełnione pewne warunki po pierwsze tylko jedna tabela może być
zamieszczona w klauzuli from jest to dość oczywiste dlatego że gdyby
było tam więcej tabel nie wiadomo by było do których z nich mamy zapisać dane
które widok zwraca jedna tabela oznacza że wiadomo dokładnie do
której tabeli będziemy zapisywać dane zwracane przez widok nie mogą być w
żaden sposób zagregowane nie może być tam klauzuli goodbye having
distinct nie może być też w klauzuli
select żadnych funkcji agregacyjnych dlatego że dane w
ten sposób uzyskane mogą być niezgodne z ograniczeniami wymaganiami
tabeli do której chcemy zapisywać je tabeli która
jest podstawą widoku dane zwracane przez widok także
nie mogą ograniczone limitem ani offsetem muszą
być pełne może być na nich założony warunek where zaraz opowiem
przy okazji przykładu z czym to się wiąże natomiast nie może być ograniczenia
poprzez limit i ostatni warunek w zapytaniu
widoku nie może się pojawić łączenie danych poprzez słowa
kluczowe union intersect czy except te dane nie mogą być w żaden sposób połączone
bardzo często takie łączenie oznacza że na przykład klucze główne mogą się
powtórzyć w niektórych wierszach dlatego takie połączenie jest
w widoku zapisywalnym zabronione zobaczmy teraz na przykładzie
jak wygląda taki zapisywalny widok weźmy
jako przykład widok tank heroes z pierwszej lekcji tego rozdziału
wprowadzającej do widoków wygląda na to że spełnia wszystkie warunki w
widoku zapisywanego jedna tabela w klauzuli from brak
agregacji ani nie ma też limitów nie ma łączenia danych
na przykład przez union wygląda dobrze spróbujmy w takim
razie coś zapisać przez ten widok mam tu takie zapytanie
gotowe oczywiście w widoku mamy tylko trzy kolumny
first name last name i health więc tylko takie dane możemy
zapisać i zobaczmy co
nam się zapisało okazuje się że nowy
rekrut nam się zapisał wartości które podaliśmy też się
nam zapisały no i oczywiście zapisały się wartości domyślne
na które żeśmy skonfigurowali lub
podali w tym wypadku identyfikator sam się wypełnia tak z kolumny
autogenerowanej natomiast pozostałe wartości są puste dlatego
że nie przekazaliśmy ich zresztą nawet jeżeli chcielibyśmy
je przekazać to powodowałoby to błąd o
właśnie kolumny gender nie ma w relacji widok jest traktowany tak jak
tablica więc jako relacja i w relacji
tank heroes nie ma kolumny gender a teraz zapiszmy postać
która ma mało zdrowia powiedzmy o proszę
bardzo mimo że widok ogranicza dane do tych z
wartością atrybutu health powyżej 200 to można
zapisać dane które nie spełniają tego warunku oczywiście nasz
widok zwróci tylko te rekordy
co trzeba natomiast możemy zmodyfikować widok
tak żeby nie pozwolił na zapisywanie rekordów
które nie mieszczą się w ograniczeniach widoku
czyli jeżeli dodamy with check option i zmienimy tą kwerendę
do tworzenia widoku na taką do tworzenia
lub aktualizacji w ten sposób możemy zaktualizować
widok jak spróbujemy jeszcze raz wpisać taką słabszą
postać to niestety baza
nam nie pozwoli w ten sposób możemy udostępniać
widoki do wyświetlania danych i do wpisywania tylko
tego co chcemy możemy udostępnić tylko te kolumny w których pozwalamy
użytkownikowi coś zapisać natomiast reszta kolumn wypełnia
się pustymi lub domyślnymi wartościami z
poprzedniej lekcji już wiesz że widoki można wykorzystać do ograniczenia
dostępu do danych jak możesz się domyślić zapisywalne
widoki także pozwalają na ograniczenie z tym że możliwości
zapisu danych to znaczy do jakich kolumn pozwalamy zapisywać
dane lub je aktualizować pozwól że ci zademonstruje
z tym że musimy się do tej demonstracji przygotować po pierwsze potrzebujemy
drugie połączenie jak stworzyć sobie takie drugie połączenie z
drugim użytkownikiem możesz zobaczyć w poprzedniej lekcji bo
tam też to wykorzystujemy i będę chciał
zmodyfikować nasz widok tank heroes dodając do niego jedną
kolumnę tak żeby się wyświetlały też birthdate bo chciałem
ci pokazać jak manipuluje się interwałami czyli takim
typem danych w sql który pozwala na opisywanie
pewnych przedziałów czasowych żeby zaktualizować ten
widok wspomogę się tutaj ułatwieniem pg admin
potrafi mi wygenerować skrypt tworzący i aktualizujący
widok proszę bardzo nawet jakbym
chciał to mógłbym go usunąć jak bym potrzebował zrobić
większe zmiany natomiast tutaj tylko chcę dodać kolumnę birthdate
to zapytanie sql wygląda podobnie jak to które widziałeś
w pierwszej lekcji ta opcja którą dodaliśmy później jest
check option jest umieszczona w tym miejscu tak akurat sobie
pay admin wygenerował to wszystko jednoczone jest tutaj czy jest na końcu
w momencie kiedy tą opcję ustawiałem nie
użyłem słowa cascaded jest od domyślna wartość tej opcji
a oznacza że jeżeli nasz widok korzysta z innych widoków
to przy zapisie wszystkie warunki to znaczy warunki wszystkich widoków
połączonych będą sprawdzane druga wartość tej opcji to local
który oznacza że tylko ten warunek bieżącego widoku będzie
sprawdzany czyli heroes health większe od 200 natomiast gdybyśmy mieli
połączone inne widoki nie byłyby sprawdzane warunki
tego widoku dobrze możemy dodać sobie tą kolumnę już
ją mamy sprawdźmy tylko czy się dodała proszę bardzo i
jesteśmy gotowi na przykład użycia zapisywalnych widoków do
właśnie ograniczenia możliwości zapisywania danych przez
niepowołane osoby na razie jesteśmy zabezpieczeni przed
innymi użytkownikami nie mają oni dostępu do naszej tabeli tak
jak na przykład z użytkownik other jeżeli spróbuje cokolwiek
wyciągnąć z tabeli heroes niestety
nie ma takich uprawnień jeżeli chcemy udostępnić możliwość
zapisywania danych do tabeli heroes dla określonych kolumn
możemy udostępnić mu widok do odczytu ma uprawnienia
dlatego że dostał je na poprzedniej lekcji natomiast
jakby chciał zapisać cokolwiek to niestety mu się to nie
uda powiedzmy że chcemy zapisać wpisać nowego bohatera
niestety nie może ale możemy dać mu uprawnienia do zapisu
na widoku tank heroes dla kogo dla użytkownika
other ciach i w tym momencie już
on może zapisać te dane które udostępnia
widok no ale chcieliśmy tak naprawdę zaktualizować dane
udostępnione przez widok a konkretnie chcieliśmy odmłodzić
naszych mocnych bohaterów o rok w takim razie to będzie
aktualizacja używając widoku i ustawiamy birthdate
na wartość birthdate minus rok
do tego w sql mamy udostępniony typ interwał
do tego typu operacji i mogę sobie jakiś
literał rzutować na typ interval a jakie literały mam do
dyspozycji jeżeli chodzi o rok i miesiąc są dwie liczby oddzielone
średnikiem czyli na przykład to jest dwa lata i pięć
miesięcy taki interwał natomiast jeżeli chodzi o czas to
są dni w postaci cyfry oddzielone spacją od
godzin minut i sekund które są przedzielone dwukropkami
tak to było 3 dni 12 godzin 39 minut i 23 sekund nas
w tym momencie interesuje tylko rok więc taki zapis 1 rok 0
miesięcy to spowoduje że od daty birthdate zostanie odjęty dokładnie
rok no dobrze w ten sposób jesteśmy w stanie zaktualizować tych
bohaterów którzy są udostępniani przez widok
ale jeszcze jednej rzeczy brakuje dlatego że jeżeli spróbuję uruchomić to
zapytanie dostanę informację że nie mam uprawnień do
aktualizacji jest osobne uprawnienie które muszę nadać
użytkownikowi other jeżeli je nadamy to teraz będzie mógł
odpalić update proszę 6 rekordów zaktualizowanych
jeżeli pamiętasz jakie były daty urodzin przed update
to można zobaczyć różnicę ale to jest tylko różnica
jednego roku update wartości null nie dotyka tylko pomija je nie zgłaszając
błędu to ułatwia takie zapytania dlatego że nie trzeba specjalnie dodawać
warunków które by filtrowały te rekordy które mają w
kolumnie aktualizowanej wartości null ale widzimy
naszego nowego bohatera doktora maxa dopisanego przed chwilą użytkownik
other może spokojnie tutaj działać jeszcze tylko jedno słowo ta
notacja typu interwał o której którą przedstawiłem jest to
notacja standardowa zwykle silniki baz danych mają
swoje wersje tej dotacji mniej lub bardziej czytelne
na przykład dla postgresa moglibyśmy tutaj wpisać 1 year
i też by to było akceptowalne i by też odjęło 1 rok
od daty urodzin to tyle jeżeli chodzi o przykłady a teraz
czas na zadania przed tobą tworzenie
widoku widoku który wybiera ekwipunek który ma jakiś współczynnik
obrony natomiast następnym krokiem jest zmodyfikowanie
tego widoku tak żeby można było napisać do niego nowy element
ekwipunku oczywiście czegoś tutaj jest za dużo to co czego można by się pozbyć
to na przykład informacje o bohaterach rozwiązanie do tego zadania będzie
na początku następnej lekcji więc do tego czasu możesz działać i modyfikować
ten widok tak żeby spełniał założenia do usłyszenia na następnej lekcji
Zapisywanie do widoków · 1 min
-- Zapytanie zapisujące dane z wykorzystaniem widoku
INSERT INTO tank_heroes (first_name, last_name, health)
VALUES ('Super', 'Tank', 410);
-- Weryfikacja zapisanego rekordu
SELECT * FROM heroes;
-- Próba zapisania danych do kolumny nie zawartej w widoku (powinna wywołać błąd)
INSERT INTO tank_heroes (first_name, last_name, health, gender)
VALUES ('Super', 'Tank', 410, 'M');
-- Próba zapisania danych spoza warunku wprowadzonego przez widok (health > 200)
-- Ta próba powiedzie się, jednak zapisane dane nie będą zwracane przez widok (poza zakresem)
INSERT INTO tank_heroes (first_name, last_name, health)
VALUES ('Mistery', 'Girl', 110);
-- Weryfikacja zapisanego rekordu
SELECT * FROM heroes; -- nowa postać widoczna na końcu zestawu danych
SELECT * FROM tank_heroes; -- nowej postaci nie ma w wynikach
-- Modyfikacja widoku aby weryfikował także swój warunek
CREATE OR REPLACE VIEW tank_heroes AS
SELECT heroes.first_name,
heroes.last_name,
heroes.health
FROM heroes
WHERE (heroes.health > 200)
WITH CHECK OPTION; -- domyślna wartość to: WITH CASCADED CHECK OPTION
-- Próba zapisania danych spoza warunku wprowadzonego przez widok (health > 200)
-- Tym razem próba nie powiedzie się gdyż CHECK OPTION nie pozwoli zapisać danych spoza warunku widoku
INSERT INTO tank_heroes (first_name, last_name, health)
VALUES ('Mistery', 'Girl', 110);