Pracując z backendem aplikacji prędzej czy później nadejdzie potrzeba zgłębienia swojej wiedzy na temat baz danych. W tym poście będę posługiwał się narzędziem psql. Jest to program działający z poziomu terminala, służący do interakcji z bazą danych (w moim przypadku będzie to PostgreSQL).

Pominę instalację Postgresa, ponieważ bardzo dobrze jest wytłumaczona w poradniku instalacji GoRails, z którego zawsze korzystam.

Zacznijmy od stworzenia testowej bazy danych na potrzeby tego artykułu. Uruchamiamy terminal (linux/unix) i lecimy.

$ sudo -i # zaloguj się na roota
$ su postgres # zaloguj się jako user postgres, by móc tworzyć bazy danych
$ psql # uruchom narzędzie psql
=# CREATE DATABASE testdb; # utwórz bazę danych o nazwie 'testdb'
=# \q # wyłącz psql
$ exit # wyjdź z konta postgres
$ exit # wyjdź z konta roota

Teraz uruchom psql na nowo utworzonej bazie.

$ psql testdb
testdb=#

Jeśli to co, widzisz wygląda jak druga linijka powyższego listingu, to wszystko poszło pomyślnie, gratulację. Przejdźmy do operacji na danych.

Tworzenie tabeli

By utworzyć tabelę korzystamy z funkcji CREATE TABLE. Stwórzmy tabelę, o nazwie flights, zawierającą pola:

  • id,
  • origin,
  • destination,
  • duration. 
    $ CREATE TABLE flights(
id SERIAL PRIMARY KEY,
origin VARCHAR NOT NULL,
destination VARCHAR NOT NULL,
duration INT NOT NULL
);

    Typ każdego pola musi zostać określony. I tak, id jest typem SERIAL, dzięki czemu będzie automatycznie wzrastało o jeden, przy każdym nowym elemencie w tabeli. Tj. element dodany jako pierwszy będzie miał id = 1, element drugi id = 2, itd. Ponadto, id zostało oznaczone jako PRIMARY KEY. Posłuży on nam później do łączenia innych tabel i wyszukiwania danych. Pola origin oraz destination są typem VARCHAR, tzn. ciągiem znaków oraz oznaczone jako NOT NULL, co oznacza, że podczas dodawania nowego rekordu do bazy, te pola muszą mieć jakąś wartość. Ostatnim polem jest duration, z typem INT, czyli liczbą całkowitą, również oznaczone jako NOT NULL.

Dodawanie danych do tabeli

Powinniśmy wypełnić naszą nową tabelę jakimiś danymi. Dodajemy rekordy dzięki komendzie INSERT INTO. Składnia prezentuje się następująco

$ INSERT INTO [nazwa_tabeli] ([nazwa_wiersza_1], [nazwa_wiersza_2], ..., [nazwa_wiersza_N]) VALUES ([wartość_wiersza_1], [wartość_wiersza_2], ..., [wartość_wiersza_N]);

Dodajmy kilka lotów.

$ INSERT INTO flights (origin, destination, duration) VALUES ('Gdynia', 'Copenhagen', 120);
$ INSERT INTO flights (origin, destination, duration) VALUES ('Gdynia', 'Alesund', 160);
$ INSERT INTO flights (origin, destination, duration) VALUES ('Krakow', 'Malaga', 240);
$ INSERT INTO flights (origin, destination, duration) VALUES ('Dublin', 'New York', 650);
$ INSERT INTO flights (origin, destination, duration) VALUES ('Moscow', 'Rome', 320);

Jak można zauważyć, nie określamy wartości pól id - jest ono uzupełniane automatycznie podczas tworzenia nowych rekordów.

Wyświetlanie danych

Aby sprawdzić jak wygląda nasza tabela flights użyjemy komendy SELECT do pobrania z niej danych.

SELECT * FROM flights;

* oznacza tutaj wszystkie kolumny jakie są w tabeli flights. Możemy określić, które psql ma wyświetlić, np.

SELECT origin, duration FROM flights;

Co zwróci nam

 origin | duration 
--------+----------
 Gdansk |      120
 Moscow |      320
 Dublin |      650
 Krakow |      240
 Gdynia |      160
(5 rows)

Różne odmiany selecta

Możemy wyświetlać dane nie tylko wybierając porządane kolumny, ale też poprzez konkretne wartości. Wyświetlmy zatem wszystkie loty, których czas trwania jest dłuższy niż 300 minut.

$ SELECT * FROM flights WHERE duration > 300;

Możemy również zliczyć ile jest takich lotów w bazie poprzez COUNT(*)

$ SELECT COUNT(*) FROM flights WHERE duration > 300;

Możemy też wyliczyć średnią długość lotu na podstawie wszystkich dostępnych danych.

$ SELECT AVG(duration) FROM flights;

Ciekawą i przydatną operacją jest łączenie WHERE z LIKE.

$ SELECT * FROM flights WHERE origin LIKE 'G%';

Dzięki ‘G%’ baza danych sprawdza wszystkie rekordy kolumny ‘origin’, w których znajduje się litera g. Znak % po literze ‘G’ oznacza, że coś może ale nie musi znajdować się w miejscu znaku %. Może być to jedna lub wiele liter lub nic. Dzięki takiemu warunkowi otrzymujemy wszystkie loty rozpoczynające się z miast zawierających literę G w nazwie.

W następnej części zaprezentuję operacje na danych - modyfikacja, usuwanie oraz bardziej złożone zapytania korzystające z PRIMARY KEY oraz FOREIGN KEY.