Kurs – podstawy SQL na bazie SQLite

W poprzednim wpisie udało nam się zaznaczyć, że zastosowania relacyjnych baz danych są tak szerokie, że nadają się do większości typowych zadań związanych z katalogowaniem. Dziś postaramy się dowiedzieć czegoś o SQL, języku będącym swoistym rodzajem interfejsu do naszej bazy. SQL na początku może wydawać się nieco skomplikowany, ale później okaże się, że dla możliwości oferowanych przez bazę jest on rozwiązaniem optymalnym.

Nigdy nie lubiłem uczyć się czegoś na sucho – jest to nużące i daje mało efektu. Dlatego skorzystamy z SQLite. Nie wymaga on dużej ilości konfiguracji, jak inne bazy danych, wystarczy jedynie ściągnąć odpowiedni program i uruchomić go. Z tych darmowych udało mi się znaleźć jeden, bardzo przyjemny w użytku. Nazywa się SQLite Administrator, jednak ma jeden mankament – na stronie projektu jest napisane, że działa on tylko na systemach Windows 2000, XP i 2003, dzięki czemu Windowsy 9x odpadają… Dla osób, które korzystają z nich istnieje alternatywa – SQLite Database Browser, który już znacznie mniej mi się podoba, głównie dzięki temu, że jego graficzny interfejs użytkownika nie uczy dobrych nawyków. Do korzystania z konsoli jednak się nada. A więc do boju! Zainstalujmy jeden z tych programów i przejdźmy wreszcie do konkretów.

 

Aby nie było niejednoznaczności, wszelkie opisy działań zamieszczone dalej dotyczyć będą sqliteadmin. Osoby korzystające z innych programów będą musiały wykazać się inicjatywą i same poszukać odpowiednich opcji.

Uruchamiamy program i wybieramy opcję `Utwórz bazę danych’. W oknie dialogowym wybieramy typ pliku – baza sqlite3, wpisujemy nazwę i tyle! Baza danych została utworzona.

Teraz wybieramy kartę `Zapytanie SQL’. Większość naszej pracy będzie się z tym wiązać, choć program ma także tryb graficzny dla każdej ważniejszej opcji. Jednak dla nauki składni znacznie bardziej przyda nam się konsola, do której zapytania będziemy wpisywać ręcznie. Gdy już zrozumiemy istotę języka, wtedy nic nie będzie stało na przeszkodzie, aby wykorzystać tryb graficzny.

Po wpisaniu zapytania wciskamy F8 (wyjątkiem są zapytania z rodziny SELECT, które wykonujemy za pomocą klawisza F9). Czasami, podczas tworzenia i zmieniania tabel przydatne okaże się odświeżenie okienka po lewej, możemy kliknąć na nim prawym przyciskiem i wybrać opcję Odśwież.

CREATE TABLE – utwórz tabelkę.

A oto nasze pierwsze polecenie w SQL, które za chwilę dogłębniej zanalizujemy. Na tym etapie wystarczy wiedzieć, że celem jest stworzenie relacji – tabeli, w której będziemy zapisywać nasze dane. 🙂

CREATE TABLE person(name TEXT);

Wydaje się proste, nieprawdaż? Pierwsze dwa słowa odpowiadają za stworzenie nowej, pustej relacji. Następnie podajemy nazwę tej tabeli, a w nawiasie zdefiniujmy pola, które będzie zawierać. Pole `name’ jest typu tekstowego. O typach danych w SQLite już wkrótce.

Wszystko w porządku? Nie, przecież relacja `person’ powinna zawierać więcej atrybutów, niż tylko `name’! A tak się cieszyliśmy sukcesem… Coż, usuńmy naszą relację i stwórzmy nową.

DROP TABLE person;

CREATE TABLE person(name TEXT, surname TEXT);

Lepiej? Oho, wpadł mi do głowy pomysł. Nasza relacja może jeszcze zawierać wiek danej osoby. Kasowanie i pisanie od nowa raczej nie ma sensu, za dużo literek… Skorzystajmy jednak z dobrodziejstw języka SQL i zmodyfikujmy tabelkę.

ALTER TABLE person ADD age INT NOT NULL DEFAULT 0;

Teraz nasza tabela powiększyła się o nowe pole. I o ile ALTER TABLE person ADD age INT wydaje się zrozumiałe, to już NOT NULL oraz DEFAULT 0 jest czymś nowym. Te dwa dodatki określają, że nowe pole nie może być puste (NOT NULL), a domyślną wartością będzie 0, nawet, jeśli zapomnimy wpisać tam jakiejkolwiek wartości.

OK. Teraz możemy zacząć dodawać nowe krotki do naszej relacji.

INSERT, SELECT, UPDATE, DELETE

Na początku dodamy nową osobę.

INSERT INTO person VALUES('John', 'Doe', 26);

Kolejność wpisywania wartości zależy w tym przypadku od kolejności pól w tabeli. Ale nie ma się co martwić, możemy wybrać tylko te pola, które chcemy i w takiej kolejności, w jakiej nam się podoba.

INSERT INTO person(age, surname, name) VALUES(33, 'Doe', 'Alex');
INSERT INTO person(name) VALUES('Adam');

Spójrzmy na drugi przypadek. Nie wypełniliśmy wszystkich pól. Jaki to da nam efekt? Sprawdźmy:

SELECT * FROM person;

Okazuje się, że trzecia krotka posiada wartości (’Adam’, NULL, 0). NULL jest pustą wartością, dodawaną do rekordu w momencie, kiedy brakuje pewnych danych podczas wpisywania. Jednak w przypadku pola `age’ zdołaliśmy temu przeciwdziałać poprzez ustawienie atrybutu NOT NULL oraz zmiany wartości domyślnej na 0.

No dobrze, Adam NULL to raczej nie jest zbyt fortunny rekord. Zmodyfikujmy go.

UPDATE person SET surname = 'Lake', age = 5 WHERE rowid = 3;

Mamy tu kilka nowych rzeczy naraz. Po pierwsze UPDATE, które zmienia nam konkretne wartości w polach surname i age. Po drugie WHERE, dzięki któremu możemy wybrać tylko tą krotkę, która nas interesuje i pozostawić pozostałe w spokoju (tu wypada zaznaczyć, że UPDATE wywołane bez WHERE zmieni nam wszystkie wpisy w tabeli, co nie jest zbyt praktyczne). Po trzecie, rowid, który jest licznikiem generowanym automatycznie przez SQLite podczas tworzenia bazy danych. Taki numeryczny identyfikator jest bardzo przydatny, gdyż jest przyporządkowany tylko do jednej istniejącej krotki (a Johnów Doe mogą być setki). Najlepiej zilustruje to poniższy przykład.

SELECT name,surname,rowid FROM person;

Podobna metoda, jak podczas wpisywania danych, pozwala nam na wybieranie tylko tych pól, które nas interesują. Okazuje się, że przy każdym kolejnym wpisie licznik zwiększa się nam o jeden.

Tak naprawdę to Adam nie istniał. Tylko go sobie wymyśliłem. Usuńmy go.

DELETE FROM person WHERE rowid = 3;

Za to istnieje Ian Grisham, który ma 40 lat, a jego dane już uda Ci się samodzielnie wpisać.

Bardziej zaawansowane wybieranie danych.

WHERE już jest nam znane. Z powodzeniem możemy je zastosować również z poleceniem SELECT.

SELECT * FROM person WHERE surname = 'Doe';

Podczas przeszukiwania tekstu możemy skorzystać także z możliwości, jakie oferuje nam słowo kluczowe LIKE. Możemy wybrać tekst, w którym zawiera się dany ciąg znaków.

SELECT * FROM person WHERE surname LIKE 'Do%';

To zapytanie pozwoli nam znaleźć wszystkie ciągi znaków zaczynające się od Do. Przykładami są: Do, Doe, Dodododod, Do blah blah blah.

SELECT DISTINCT surname FROM person;

Dzięki DISTINCT mamy tylko niepowtarzające się dane. Dzięki temu, niezależnie od tego, ilu było Johnów Doe, otrzymamy tylko jeden wynik z tym imieniem i nazwiskiem.

SELECT COUNT(*) FROM person;

To zapytanie zwróci nam ilość wpisów w relacji.

SELECT * FROM person ORDER BY age;

Jeśli chcemy posortować wyniki według danego pola, w tym przypadku wieku, używamy ORDER BY.

SELECT * FROM person ORDER BY age LIMIT 1;

To zapytanie ograniczy nam liczbę wyników do jednego, dzięki czemu będziemy mogli wybrać najmłodszą osobę z naszej tabeli.

SELECT * FROM person ORDER BY age DESC LIMIT 1;

DESC i ASC (descending, ascending), dają nam możliwość sortowania wyniku od największego do najmniejszego lub od najmniejszego do największego. ASC jest zachowaniem domyślnym, dlatego nie musiałem go pisać w poprzednim przykładzie, choć użycie go tam jest całkowicie poprawne.

SELECT * FROM person LIMIT 1 OFFSET 2;

Zwraca jedną krotkę zaczynając po drugiej, tak więc realnie zwraca krotkę trzecią. Domyślnym zachowaniem jest OFFSET 0, którego nie musimy pisać przy zapytaniu.

SELECT surname, COUNT(*) FROM person GROUP BY surname;

Dzięki temu zapytaniu możemy sprawdzić, ile osób ma dane nazwisko…

SELECT surname,AVG(age) AS t1 FROM person GROUP BY surname ORDER BY t1;

… oraz wyliczyć średnią wieku dla danego nazwiska. Tutaj ciekawym dodatkiem jest przypisanie nowej nazwy t1 funkcji AVG(age), która odpowiada za zliczanie średniej poszczególnych wyników. Dzięki temu zamiast wpisywać nazwę funkcji po raz drugi (jako argument ORDER BY), możemy skorzystać z przypisanej nazwy. Jest to bardzo przydatna rzecz przy bardziej skomplikowanych zapytaniach, bo nie dość, że krócej się zapisuje, to potem nie będziemy musieli poprawiać kilka razy tego samego fragmentu zapytania powielonego w różnych miejscach.

Zakończenie.

Uff, udało nam się dobrnąć aż tutaj. Powyższy tekst to tak naprawdę podstawy SQLa w pigułce. Większość bardziej skomplikowanych zapytań będzie zbudowana na podstawie tych prostych form i będziemy w stanie sobie z nimi poradzić w ten, czy w inny sposób.

Powyższe przykłady z pewnymi zmianami można zastosować w każdym systemie zarządzania bazami danych. Niestety, za każdym razem będzie trzeba odkrywać na nowo te zmiany.

Do zobaczenia następnym razem. Zachowajcie tą bazę danych, przyda się nam jeszcze. 🙂