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.
Mamy już stworzoną bazę i pierwszą podstawową tabelę która
będzie przechowywać informacje o bohaterach naszej wyimaginowanej gry przygodowej
teraz czas nakarmić tą bazę informacjami o bohaterach zanim
jednak dowiesz się jak to zrobić obiecane rozwiązania zadań z poprzedniej
lekcji
zadaniem do wykonania było dodanie nowej kolumny birthdate do tabeli
i wybranie odpowiedniego typu dla danych przechowywanych w
niej kolumny dodajemy do tabeli więc jest to zapytanie modyfikujące
tabelę czyli typu ddl alter table ponieważ
zmianą w tabeli heroes będzie dodanie
kolumny więc add column birth date teraz
jaki typ danych birthdate powinno to być data
tutaj wystarczy tworzymy kolumnę i tylko sprawdźmy czy
ona się utworzyła dodała się na końcu każda nowa
kolumna dodaje się na końcu tej listy kolumn zadaniem
dodatkowym było zdefiniowanie wartości domyślnej
do kolumny health w ilości dwustu tutaj
chcemy zmodyfikować kolumny w tabeli więc jest to modyfikacja samej
tabeli także znowu alter table tym
razem nie będziemy dodawać kolumny tylko chcemy zmodyfikować kolumnę
więc alter column kolumnę health i chcemy
ustawić wartość domyślną więc set default
no wartość 200 teraz
nowe postacie będą dostawały domyślną wartość health na
200 o ile nie podamy innej za chwilę zademonstruje
jak w praktyce to wygląda i jak wykorzystywać mechanizm
domyślności przy dodawaniu nowych rekordów do
wprowadzania danych służy zapytanie insert jest to zapytanie typu
dml w swojej podstawowej postaci składa się ze słów kluczowych insert
into i nazwy tabeli do której mają zostać zapisane dane
następnie opcjonalnie w nawiasach umieszcza się listę kolumn ze
wspomnianej tabeli o tym co to oznacza za chwilę i na końcu słowo
kluczowe values po którym też w nawiasach wprowadza się listę wartości
oddzielonych przecinkami w tej postaci z listą kolumn podane
wartości zostaną przypisane do odpowiednich wyspecyfikowanych kolumn
w tej postaci każda z wartości zostanie przypisana do
odpowiedniej kolumny według kolejności zadeklarowanej
w zapytaniu tak jak na tej ilustracji wartość pierwsza do kolumny drugiej
wartość droga do kolumny pierwszej i wartość trzecia do kolumny trzeciej natomiast
w postaci bez listy kolumn wartości zostaną zapisane do
kolumn zgodnie z kolejnością widoczną w opisie tabeli ta
kolejność odpowiada kolejności dodawania kolumn do tabeli przyjmując
podaną kolejność kolumn przypisanie nastąpi w takiej
kolejności wartość pierwsza do kolumny pierwszej wartość druga do kolumny drugiej
wartość trzecia do kolumny trzeciej zanim
wprowadzimy jakiekolwiek dane do tabeli bohaterów sprawdźmy co
w niej w tej chwili jest do wybierania danych czy wyświetlania
danych z tabeli służy zapytanie select i w
takiej najprostszej postaci którą tutaj będziemy używać gwiazdka oznacza
wszystkie kolumny tabeli znaczy dane ze wszystkich kolumn i
from nazwa tabeli z której będziemy wybierać dane i
widać że nie ma żadnych danych zauważ że lista kolumn
lista wszystkich kolumn która została wybrana jest
w kolejności kolumn które występują w tej liście tak czyli ta
lista kolumn jest stała i odpowiada kolejności
dodawania kolumn do tabeli a teraz wprowadźmy
naszego pierwszego bohatera używamy do tego zaprezentowanego przed chwilą
zapytania insert into w tej prostszej formie tutaj widzimy
jaka jest kolejność kolumn po lewej stronie i na dole więc
możemy po prostu podać same wartości wymyślmy
sobie jakieś imię jakieś nazwisko
będzie to na początek mężczyzna elf jakimiś
parametrami siły mądrości zdrowia
i many plus dodamy identyfikator unikalny ponieważ
jest to nasz pierwszy bohater to będzie miał numer 1 i datę urodzenia
formatowanie daty zależne jest od ustawienia
parametru konfiguracyjnego date style w moim przypadku mogę
sprawdzić show jest
to miesiąc dzień rok dlatego musimy umieścić
miesiąc na pierwszej pozycji dzień na drugiej pozycji w różnych systemach
domyślnie jest to różne ustawienie na systemach linuksowych
jeżeli na przykład ma się ustawienie jeżeli ma się włączoną lokalizację
polską prawdopodobnie to będzie dzień miesiąc rok warto
sprawdzić wtedy wiadomo w jaki sposób formatować tą datę żeby
poprawnie się zapisała dobrze zapisała no dobrze zapisał się nasz pierwszy
bohater sprawdźmy w takim razie co mamy
w naszej tabeli i proszę bardzo pierwszy rekord
wszystkie dane się zgadzają to teraz idziemy krok dalej wpiszmy następnego
tylko tym razem zamiast wartości zdrowia użyjemy
słowa default co oznacza wpisz tutaj wartość
domyślną jaka jest przypisana do tej kolumny to co zrobiłeś
w swoim zadaniu do przepracowania a następnie już kolejne
kolumny czyli mana identyfikator numer dwa i data
urodzin to już nie jest elf więc nie jest tak długowieczny
nasz nowy bohater został wpisany teraz
sprawdźmy jak to wygląda okazuje
się że on był koło odpaliłem zapytanie wpisania
dwa razy i jared wpisał się podwójnie teraz
chciałbym wyrzucić jego klon no i teraz kłopot w
jaki sposób powiedzieć że chce drugi wiersz a nie trzeci
te numerki 123 po lewej stronie to jest tylko oznaczenie
wierszy przez pg admina baza danych
nie ma takich informacji dlatego identyfikator jest
tak ważny w tym wypadku tabela pozwoliła mi na wprowadzenie bohatera
z takim samym identyfikatorem w jaki sposób się zabezpieczyć
przed tym żeby nie można było wprowadzić takiego rekordu o tym w następnym
rozdziale w tej chwili jedyne co mi zostało to usunąć wpisanego
bohatera i wpisać go na nowo tylko raz usuwam
zapytaniem delete from heroes i żeby
usunąć konkretne rekordy a nie wszystko muszę
podać jakiś warunek który wybierze mi tylko te rekordy
które chcę usunąć warunek ten specyfikuje się używając
słowa kluczowego where i podając jakiś predykat czyli
właśnie warunek wybierający zwykle używa się wartości
klucza jako wartości unikalnej do wybierania konkretnych rekordów
w tym momencie mogę się odwołać do tej wartości hero id 2
i zostały usunięte
dwa wiersze ten komunikat bazy mówi nam
właśnie o tym ile wierszy dane polecenie zmodyfikowało
w tym wypadku ile wierszy usunęło i
musimy go wpisać jeszcze raz a teraz pobawimy się troszkę
z tym zapytaniem insert into i spróbujmy dodać
wartości które będą niekompletne i
dodatkowo zobaczmy co się stanie kiedy użyjemy
wartości default słowa kluczowego default na
innej kolumnie niż zdrowie która ma ustawioną
wartość domyślną na przykład na mądrości
czyli w tym momencie ustawmy sobie zdrowie
na zero zobaczmy co się co się stanie i identyfikator
3 natomiast nie podaje daty urodzin proszę
bardzo został zapisany rekord natomiast co
zostało zapisane wartość domyślna dla kolumny wisdom okazuje
się jest wartością null czyli pustą i jest to domyślna
wartość dla kolumn które nie mają określonej wartości default
i takich które potrafią które są w stanie przyjąć
wartość null zdrowie zero jak najbardziej może być zapisane
nie ma tam żadnego ograniczenia natomiast no właśnie nie
podaliśmy wartości many a podaliśmy wartość hero id co
się stało ponieważ nie podaliśmy do jakich kolumn chcemy wpisać te
dane zostały one wpisane w kolumny kolejne w
tym wypadku nasze hero id wpadło w kolumny mana a ostatnie
dwie kolumny nie dostały żadnych danych generalnie ten rekord jest
bardzo zepsuty więc po prostu go usuniemy i wpiszemy
na nowo poprawnie tym razem już jest tylko jeden rekord
więc można go zidentyfikować bardzo precyzyjnie przez
hero id jest null natomiast przez wartość hero id
która w tym wypadku jest wartością null
i w ten sposób usunęliśmy dokładnie ten
wiersz który chcieliśmy i nie
ma naszego hobbita teraz wpiszmy już odpowiednią
wartość default na kolumnie
zdrowia zero many hero id 3 i
jakąś datę urodzenia też warto tu podać zobaczmy
co się zapisało bezbłędnie i
proszę bardzo ma wszystkie dane poprawnie już wpisane no
może ta mana na 0 jest trochę krzywdząca dla naszego hobbita dlaczego
nie dać mu odrobiny możliwości zmodyfikujmy tą
wartość do jakiejś niewielkiej dodatniej do modyfikacji
danych aktualizacji danych w tabeli służy polecenie dml
update tabela a następnie po słowie kluczowym
set podajemy które kolumny chcemy zmodyfikować chcemy
ustawić mana na 10 i oczywiście nie uruchamiamy
tego zapytania w tej postaci dlatego że w ten sposób zmodyfikowałoby
to czy ustawiło wartość many na 10 dla wszystkich
wierszy tej tabeli tylko wybieramy warunkiem już wcześniej poznanym
where które wiersze chcemy zaktualizować w tym wypadku chodzi o
bohatera o id 3 i
pięknie wartość many została zmodyfikowana teraz poprawiłem
ten błąd gdzie chciałem zaktualizować dane dla hero id
równego 4 którego widać nie ma w bazie pytanie
co się stanie jeżeli ja takie zapytanie próbuję uruchomić pomyśl
jeżeli potrzebujesz zapauzuj a ja przetestuję update
0 ponieważ nie zostały znalezione wiersze tym warunkiem więc nie
zostały też żadne wiersze zmodyfikowane warto mieć świadomość tego że podanie
warunku pewnie będzie spełniony nie powoduje błędu tylko niewykonanie
się takiej aktualizacji na jakichkolwiek danych a
propos podstawowych operacji na danych o których mówiliśmy możesz się spotkać
z akronimem crud który odnosi się właśnie do tych operacji
c oznacza create a w sql odnosi się to do polecenia
insert r to read czyli w sql select u
odpowiada słowu update i łączy się z poleceniem o tej samej nazwie tak
jak d które oznacza delete skrót ten najczęściej pojawia się gdy jest
mowa o zapewnieniu czy implementacji podstawowych funkcjonalności
obsługi danych na przykład taki opis zadania zaimplementuj
klucz dla modelu pacjenta oznacza nic więcej jak zdefiniowanie
operacji tworzenie odczytywania aktualizacji i usuwania danych dotyczących poszczególnych
pacjentów w połączeniu z bazą danych będzie to oznaczało zwykle stworzenie
zestawu zapytań wykorzystujących przedstawione 4 polecenia
insert select update i delete jeśli
wprowadziłeś własne modyfikacje do danych albo będziesz
wprowadzał na przykład spoiler alert przy okazji zadania
do pracy własnej i chciałbyś je zapisać na dysku do
pliku masz kilka opcji takie operacje nie są dobrze ustandaryzowane
każdy system bazodanowy robi je po swojemu jeśli
podążasz za przykładami w tym kursie używając postgresa to
za chwilę pokażę ci jak użyć polecenia copy do zapisywania danych z
tabeli do pliku a potem odczytywanie ich nie jest to standardowe polecenie
sql ale w tym przypadku może ci pomóc w przypadku innych systemów
są tam dostępne narzędzia do eksportowania i importowania danych
o których możesz sobie doczytać a linki do pomocnych
ci stron znajdziesz w materiałach do lekcji postgres także ma
programy pomocnicze do eksportowania importowania danych takie jak pg
dump i pg restore natomiast na potrzeby szybkiego zrzutu
danych takich jak teraz potrzebujemy wystarczy nam polecenie
copy najpierw zobaczymy co mamy w naszej
tabeli do tego skorzystam z opcji pg admina który
potrafi wyświetlić zawartość tabeli używając
zapytania select gwiazdka from heroes wyświetlających
wszystko co jest w tabeli heroes public odnosi się tutaj do schematu
public automatycznie on dodaje taki prefix upewniając się że chodzi o
tabelę właśnie z tego schematu mamy trzy wiersze trzech
bohaterów na razie wpisanych i spróbujmy zrzucić
tych trzech bohaterów do pliku używając właśnie polecenia copy kopiujemy
tabelę heroes do pliku gdzieś na
dysku w tym wypadku mam katalog założony
i będzie to plik csv czyli coma
seperated values jest to format dosyć powszechny dość prosty
w obsłudze odczytywaniu są to po prostu wartości w
tym wypadku z bazy przedzielone przecinkami każdy rekord jest na
w osobnej linijce musimy tylko powiedzieć że właśnie taki
format chcemy zachować i uruchamiając takie zapytanie baza
nam powiedziała że skopiowała trzy rekordy i możemy sprawdzić plik się
stworzył w katalogu temp możemy teraz zobaczyć co on będzie miał w
środku w środku ma te trzy rekordy każdy każda kolumna
każdy element kolumny oddzielony jest od następnego przecinkiem jak widać
teraz zobaczmy jak w jaki sposób możemy wczytać
te dane pomysł jest prosty copy
heroes from i ścieżka do pliku natomiast nasza
tabela już ma te dane więc dane z pliku csv
zostaną prawdopodobnie dodane do tabeli
i tak właśnie się stało ponieważ nie chcemy
mieć kopii tylko chcemy mieć nasze trzy rekordy to
teraz wyczyszczę tą tabelę i jeszcze raz wczytam dane z
pliku z tym że zapewne pamiętasz jakie polecenie
dml służy do usuwania danych oczywiście
mowa o delete i można by użyć do tego żeby skasować
wszystko z tabeli heroes w ten sposób chciałem ci opowiedzieć o
innym poleceniu które też usuwa rekordy z bazy
ale robi to troszkę inaczej i troszkę szybciej wyobraź sobie że masz
w tabeli heroes bardzo dużo już danych tych naszych
bohaterów jest są setki a może nawet tysiące usunięcie ich
używając polecenia delete spowoduje że każdy z nich po kolei
będzie usuwany natomiast chcielibyśmy wszystkich usunąć
od razu bezwarunkowo do czyszczenia tabeli
z danych użyjemy polecenia truncate heroes
które nam odetnie dane z tej tabeli truncate
działa troszkę inaczej niż delete usuwa
wszystkie dane na raz tak naprawdę tworząc nową
czystą tabelę ponieważ rzadko chcemy wykasować dane
z całej tabeli więc też nie tak często używa się tego polecenia
ze względu na na swój sposób działania raczej
jest uznawany za polecenie ddl niż dml i też
troszeczkę inaczej się zachowuje dlatego że jeżeli są jakieś
zdarzenia przypisane do kasowania rekordów
poprzez przez tak zwane triggery
po polsku wyzwalacze o których będę wspominał innym razem to
nie są one uruchamiane dlatego że dane nie są kasowane
w wierszu po wierszu tylko za jednym zamachem po prostu jest
tworzona nowa czysta tabela natomiast jest to super szybki
sposób na pozbycie się wszystkich danych z tabeli teraz jak już mamy
naszą tabelę czystą zresztą sprawdźmy sobie tutaj jak
to wygląda proszę bardzo nie ma możemy wczytać sobie do
naszej tabeli heroes z pliku który wcześniej stworzyliśmy oczywiście
straszna nazwa
łatwo jest zrobić literówkę w niej i
w tej chwili wczytaliśmy dane z pliku możemy odświeżyć sobie widok naszej
tabeli i wszyscy trzej bohaterowie spowrotem znaleźli
się w naszej tabeli tym
razem podstawowe zadanie do przepracowania jest podwójne po
pierwsze dodaj kilka dodatkowych postaci do tabeli heroes pamiętaj
o odpowiednich wartościach identyfikatora hero id ważne żeby
te identyfikatory się nie powtarzały natomiast jak już dodasz te nowe
postacie napisz i uruchom zapytanie aktualizujące
które zwiększy wartość wszystkich identyfikatorów o 10 zdarzają
się sytuacje kiedy trzeba połączyć dane z dwóch źródeł
czy z dwóch tabel dane które posiadają już jakieś identyfikatory i
wtedy modyfikacja hurtowa identyfikatorów w jednej
z tabel tak żeby nie pokrywały się identyfikatorami danych z drugiej
tabeli jest sytuacją z którą możesz też się spotkać mam
dla ciebie też bonus czyli zadanie dodatkowe a właściwie
modyfikacje zadania tego pierwszego spróbuj dodać tych
kilku nowych bohaterów ale używając jednego tylko zapytania
insert into jest to też przy okazji pewne
pewne usprawnienie wydajnościowe dlatego że kilka poleceń sql
zawsze kosztuje więcej niż jedno więc jeśli można zmniejszyć
ilość zapytań to jak najbardziej należy z tego skorzystać jeżeli chodzi
o rozwiązania to przykładowe rozwiązania będziesz mógł zobaczyć
na początku następnej lekcji i porównać ze swoimi a więc do zobaczenia
Zapis i odczyt danych z pliku (w PostgreSQL) · 15 min
-- Przed wykonaniem tych zapyta? nale?y stworzy? katalog Temp na dysku C:
-- Zrzucenie do pliku zawarto?ci tabeli 'heroes' w formacie CSV
COPY heroes TO 'C:/Temp/heroes.csv' WITH CSV;
-- Zwyk?e wyczyszczenie danych z tabeli 'heroes'
DELETE FROM heroes;
-- Szybkie wyczyszczenie danych z tabeli 'heroes'
TRUNCATE heroes;
-- Wczytanie danych z pliku do tabeli 'heroes'
COPY heroes FROM 'C:/Temp/heroes.csv' WITH CSV;Wprowadzanie danych do bazy · 3 min
-- Sprawdzenie jakie dane s? w tabeli
SELECT * FROM heroes;
-- Wprowadzenie nowego rekordu dla nowej postaci
INSERT INTO heroes VALUES ('Brass', 'Comtel', 'M', 'elf', 15, 19, 150, 210, 1, '02-25-1948');
-- Polecenie (PostgreSQL) aby sprawdzi? jaki jest bie??cy format daty
SHOW datestyle;
-- Wprowadzenie nowego rekordu z wykorzystaniem warto?ci domy?lnej w kolumnie 'health'
INSERT INTO heroes VALUES ('Jared', 'Brewster', 'M', 'cz?owiek', 15, 10, DEFAULT, 50, 2, '05-20-1966');
-- Wprowadzenie nowego rekordu ze z?? kolejno?ci? danych
INSERT INTO heroes VALUES ('Gal', 'Krzywon�g', 'K', 'hobbit', 12, DEFAULT, 0, 3);
-- Usuni?cie rekordu (b??dnego - tego, kt�ry ma pust? warto?? w kolumnie 'hero_id'
DELETE FROM heroes WHERE hero_id IS NULL;
-- Wprowadzenie poprawionego rekordu
INSERT INTO heroes VALUES ('Gal', 'Krzywon�g', 'K', 'hobbit', 12, 10, DEFAULT, 0, 3, '11-03-1977');
-- Zmiana warto?ci w kolumnie 'mana' dla konkretnego rekordu
UPDATE hereos SET mana = 10 WHERE hero_id = 3;