Dzisiaj ruszamy na blogu z nową serią wpisów gościnnych, które będą ukazywać się w kategorii “Porcja wiedzy”. Na pierwszy ogień idą bazy danych i SQL.
O co chodzi?
Co jakiś czas będą się ukazywać artykuły gościnne z różnych technologii. Znajdziecie w nich podstawowe informacje związane z danym zagadnieniem oraz linki, gdzie uczyć się dalej. Od kilki miesięcy chodził mi głowie pomysł zbudowania na blogu bazy wiedzy z różnych technologii, ale sama oczywiście nie jestem w stanie napisać artykułów na tyle tematów, bo się zwyczajnie nie znam na wszystkich zagadnieniach Jakiś czas temu ukazał się wpis Joanny Hulek o algorytmach i pomyślałam, iż fajnie byłoby zrobić z tego coś cyklicznego. Dlatego postanowiłam zawołać posiłki! I tak oto powstały wpisy gościnne. Od dziś możecie się ich spodziewać regularnie. Ogromnie dziękuję Agnieszce, która jest autorką tekstu poniżej za podzielenie się swoją wiedzą. jeżeli nie wiecie, czym są bazy danych i jak się w ogóle do nich zabrać, przeczytajcie koniecznie. choćby jeżeli zajmujecie się inną technologią, dobrze jest znać podstawy zagadnień związanych z bazami danych. Oddaję głos Agnieszce
Wstęp
Każdy na pewno słyszał już pojęcie “baza danych”. Tak naprawdę bez tego ciężko byłoby zrobić jakąś większą aplikację. Ale co to adekwatnie jest ta baza, jak wygląda? Baza danych (a konkretnie: relacyjna baza danych) to zespół połączonych ze sobą logicznie tabel, przechowujących używane przez nas na codzień dane (imiona, nazwiska, numery telefonów, numery kont bankowych, dane adresowe, ale także obrazy, dźwięki i wiele więcej).
Dane w tych tabelach są przechowywane w pewnym logicznym porządku – na przykład adresy są w jednej tabeli, imię i nazwisko w drugiej, a dane dotyczące płatności w trzeciej. Zasady określające ten podział noszą nazwę normalizacji (więcej poczytacie o tym tutaj), i są to dobre praktyki stosowane przy projektowaniu tabel. Tabele łączą się między sobą dzięki tak zwanych kluczy – są to specjalne pola służące jednoznacznej identyfikacji danego rekordu, czyli wiersza danych. Te połączenia zwane są właśnie relacjami.
Dla przykładu, załóżmy iż w tabeli z danymi osobowymi mamy pana Adama Kowalskiego, który ma przypisane ID o numerze 156. To ID będzie właśnie tym kluczem, który umożliwia nam odnalezienie powiązanych z panem Adamem danych w innych tabelach – odwołując się do tabeli z adresami, podajemy jego ID i otrzymujemy adres (lub adresy) przypisane do danej osoby. A jak adekwatnie wyciągać dane z bazy? Służy do tego specjalny język, którego trzon jest wspólny dla wszystkich relacyjnych baz danych.
Język SQL – jak działa?
Jeśli znacie już jakiś język programowania, to wiecie jak to jest – piszecie kolejne kroki, które komputer musi wykonać, byście uzyskali zamierzony efekt. Można powiedzieć, iż te instrukcje są czymś w rodzaju serii rozkazów.
Język SQL natomiast (Structured Query Language, strukturalny język zapytań) działa na trochę innej zasadzie – mówimy, co chcemy uzyskać, a jak to ma zostać wykonane, to już nie nasz problem – tym się zajmuje silnik bazodanowy. Porównajmy sobie schemat działania “normalnego” języka programowania i języka SQL.
Przykład w pseudokodzie – chcemy wyciągnąć z szuflady z kuchni czerwony nóż:
idź do kuchni
otwórz drugą szufladę po lewej stronie
chwyć czerwony nóż
wyjmij czerwony nóż
zamknij szufladę
I teraz to samo, tylko w logice języka SQL:
Przynieś mi nóż z kuchni, jest w drugiej szufladzie po lewej stronie.
Ogólnie rzecz ujmując, tak to właśnie wygląda Omówmy sobie teraz pokrótce z czego składają się zapytania (zwane też kwerendami, od query).
Elementy języka zapytań
Podstawowym szkieletem zapytania będzie forma:
SELECT [lista pól z tabeli - co chcemy mieć w wyniku]
FROM [nazwa tabeli - skąd chcemy to mieć]
WHERE [dodatkowe warunki, filtry - jakie chcemy to mieć]
Wracając do przykładu z nożem, wyglądałoby to tak:
SELECT nóż
FROM druga_szuflada_po_lewej
WHERE kolor = 'czerwony'
W klauzuli WHERE warunków może być więcej, powiązanych ze sobą operatorami AND i OR – tak samo jak w językach programowania; można też używać nawiasów, porównań większe/mniejsze/równe, tworząc bardziej złożone wyrażenia logiczne.
Przeanalizujmy sobie kilka zapytań, żeby zobaczyć, jak to wszystko działa.
Przykład 1 (SQL Server, baza testowa Northwind):
SELECT CustomerID, OrderID, EmployeeID
FROM dbo.Orders
WHERE CustomerID = 'SAVEA' AND EmployeeID IN(1, 3, 9)
W tym zapytaniu wybieramy trzy kolumny z tabeli Orders, gdzie CustomerID jest równe ‘SAVEA’ oraz EmployeeID mieści się w zbiorze liczb 1, 3 i 9 (czyli ID ma być równe 1 lub 3 lub 9). Taką samą konstrukcję (zmienna IN zbiór) znajdziemy np w Pythonie.
Przykład 2 (SQL Server, baza testowa Northwind):
SELECT OrderID, CustomerID, OrderDate
FROM dbo.Orders
WHERE OrderID BETWEEN 10755 AND 10847
Przykład podobny jak powyżej, ale warunek mamy określony przy pomocy słów kluczowych BETWEEN… AND… – myślę iż łatwo się domyślić o co tu chodzi; to po prostu inny sposób zapisu “większe od 10755 i mniejsze od 10847”.
Przykład 3 (baza Oracle):
SELECT *
FROM Flota
WHERE id_auta not in (SELECT id_auta FROM Pracownicy WHERE id_auta is not null);
Gwiazdka zaraz po SELECT oznacza, iż chcemy z danej tabeli wybrać wszystkie kolumny. W warunku WHERE widzimy… zapytanie w zapytaniu (zwane podzapytaniem, podkwerendą, ang. subquery). Chodzi nam o to, iż potrzebujemy id_auta, które nie należą (not in) do pewnego zbioru. Zbiór ten określamy dzięki drugiego, niejako zagnieżdżonego zapytania. Całość działa w ten sposób, iż silnik najpierw wykonuje zapytanie wewnętrzne, a jego wynik podstawia do zapytania zewnętrznego i wykonuje je bazując na uzyskanych danych.
Łączenie tabel
A co, jeżeli chcielibyśmy wyciągnąć dane z więcej niż jednej tabeli? Tak naprawdę, to rzadko korzysta się z zapytań do jednej tabeli… Zobaczmy więc, jak działają JOINy.
JOIN jest używany do łączenia dwóch lub więcej tabel, w celu uzyskania bardziej komplementarnych danych. Wygląda tak:
SELECT dane.imie, dane.nazwisko, adresy.ulica, adresy.miasto
FROM dane JOIN adresy ON dane.id = adresy.id
W klauzuli FROM mamy wytypowane dwie tabele – dane (z danymi osobowymi) oraz adresy (z adresami). Łączymy je dzięki JOIN, dając warunek dane.id = adresy.id, czyli po prostu parujemy dane na podstawie ich ID. Dla pana Adama z ID = 156 zostanie “dobrany” adres z ID = 156.
Notacja z kropką nie powinna być Wam obca; w SQLu również się z niej korzysta, tak jak w powyższym przykladzie, by określić, o pole z której tabeli nam chodzi. jeżeli tabela jest tylko jedna, to nie ma problemu, ale jeżeli jest ich więcej, to trzeba określać z której tabeli pochodzi. By ułatwić sobie życie, i nie pisać całej nazwy tabeli (przy dużych zapytaniach byłoby to uciążliwe) możemy skorzystać z aliasów, czyli skróconej nazwy, którą sami nadajemy na potrzeby danego zapytania. Aliasy deklarujemy w klauzuli FROM, pisząc nazwa_tabeli as alias. Przeróbmy powyższy przykład tak, aby korzystał z tego dobrodziejstwa:
SELECT d.imie, d.nazwisko, a.ulica, a.miasto
FROM dane as d JOIN adresy as a ON d.id = a.id
Tabeli dane nadaliśmy alias d, a tabeli adresy – a.
UPDATE & INSERT
Jak zaktualizować jakieś pole w tabeli? Do tego służy polecenie UPDATE. Ogólnie wygląda to tak:
UPDATE [nazwa tabeli w której dokonujemy zmiany]
SET [nazwa pola] = [nowa wartość]
WHERE [warunki]
Przykład:
UPDATE dane
SET imie = 'Jan'
WHERE ID = 157
UWAGA: jeżeli przy update nie dodamy klauzuli where, zaktualizowanie zostaną wszystkie rekordy w całej tabeli. A przeważnie nie o to nam będzie chodzić Także warto uważać.
Dwa słowa o transakcjach
Jeśli już przy aktualizowaniu jesteśmy, warto powiedzieć kilka słów o transakcjach, czyli o commit i rollback. Commit jest potwierdzeniem dokonanej transakcji, natomiast rollback ją cofa.
Wyobraźcie sobie, iż macie otwarty przed sobą notatnik. Zaczynacie coś w nim pisać. Czy ten tekst tam na pewno jest? Niby tak. Ale o ile teraz go zamkniecie bez zapisywania, to tekst zniknie. Czyli jakby go nigdy nie było. Gdybyście kliknęli “zapisz”, wtedy zmiany byłyby na stałe.
Z commitem i rollbackiem jest podobnie. Robiąc update, wprowadzacie zmianę, ale dopiero commit ją zapisuje, utrwala. jeżeli jednak uznacie, iż coś jest nie tak, lepiej zrobić rollback i jeszcze raz wszystko sprawdzić (np jeżeli zobaczycie, iż zaktualizowało się kilka tysięcy wierszy zamiast oczekiwanych kilku). Po każdym update MUSI nastąpić commit bądź rollback – dlatego, iż na czas aktualizacji rekordu jest on blokowany dla innych użytkowników, a jeżeli aktualizujemy dużą liczbę rekordów, to bardzo łatwo zablokować pół bazy (piszę z własnego doświadczenia ;)).
Wstawianie nowych wierszy do istniejącej tabeli
Często będziemy musieli wstawić nowe dane do istniejącej już tabeli. To zadanie zrealizujemy dzięki polecenia INSERT, którego ogólna postać wygląda tak:
INSERT INTO [nazwa_tabeli] (lista pól)
VALUES (lista wartości dla pól)
Przykład:
INSERT INTO dane (id, imie, nazwisko)
VALUES (158, 'Janina', 'Nowak')
Możemy również dane do wstawienia wyciągnąć z innej tabeli. Robimy to tak:
INSERT INTO nowa_tabela (id, imie, nazwisko)
SELECT (old_id, old_imie, old_nazwisko)
FROM stara_tabela
WHERE id > 150
Podsumowanie & co dalej?
Mamy za sobą krótki wstęp do baz danych – wiemy mniej więcej, jak wyglądają i jak się z nich korzysta. Temat samych baz, jak i języków do ich obsługi, jest bardzo szeroki. Myślę jednak iż warto wiedzieć – chociaż ogólnie – jak to wszystko działa, bo tak naprawdę korzystamy z nich codziennie… tyle, iż w większości przypadków nieświadomie
Czego adekwatnie potrzebujemy, gdybyśmy chcieli sami spróbować swoich sił przy bazach?
Każdy może sobie taką bazę zainstalować w domu. Jest to całkiem proste i darmowe, czemu więc nie spróbować?
Będziemy potrzebować dwóch elementów: silnika bazodanowego oraz programu do obsługi bazy, w którym będziemy pisać nasze zapytania, przeglądać strukturę tabel i robić inne fajne rzeczy.
Dla baz Microsoftu ściągamy sobie SQL Server w wersji Express, który będzie naszym silnikiem, i do tego Management Studio (bazodanowy odpowiednik Visual Studio).
Dla Oracle, będzie to Oracle Database Enterprise Edition, oraz na przykład SQL Developer.
Dodatkowo, dla każdej z tych baz, możemy sobie dołożyć małe, testowe bazy wypełnione danymi, by było na czym ćwiczyć.
Do niektórych systemów bazodanowych – na przykład Oracle i Microsoft SQL Server – dostajemy rozszerzenie języka SQL, które umożliwia dodawanie logiki do tabel. Te języki mają już więcej wspólnego z tradycyjnymi językami programowania, zawierają bowiem instrukcje warunkowe, pętle czy zmienne.
Dla Oracle mamy PL/SQL, a dla SQL Server jest T-SQL. Dzięki nim możemy na przykład oprogramować sprawdzanie poprawności danych przed zapisaniem ich w bazie – ot chociażby weryfikacja numerów PESEL (bo po co mamy trzymać nieprawidłowe?), albo wyciągnąć z bazy pewien zestaw danych i przetwarzać je rekord po rekordzie. Znając SQL i PL/SQL lub T-SQL możemy zostać programistą baz danych i robić właśnie tego typu rzeczy.
Przydatne linki:
Learn SQL – kurs na codecademy
sqlpedia – kurs MS SQL
O oracle ludzkim głosem – kurs Oracle SQL
sql-exercises – zbiór zadań z SQL wraz z rozwiązaniami
O autorce
Agnieszka Włodarczyk – z wykształcenia inżynier informatyk z zamiłowaniem do programowania, z zawodu… jeszcze-nie-programista W tej chwili zajmuje się utrzymaniem aplikacji w dużej korporacji. Więcej tekstów Agnieszki możecie przeczytać na jej blogu – Granice kodu.
I jak się podobało? Macie jakieś pytania? Piszcie śmiało pod tym postem!
