Podstawy SQL na przykładzie MySQL

Nie tylko MongoDB Atlas istnieje w web developmencie

Ostatnio zauważyłem, że znaczna większość kursów na youtube czy w innych darmowych miejscach, mówiących o konfiguracji bazy danych w webowym projekcie, opisuje zastosowanie MongoDB, lub ogólnie baz danych No-SQL. Bardzo wiele z tych materiałów mówi też, przy okazji, o zastosowaniach tych technologii w wersji chmurowej. Rzadko poruszane są tematy konfiguracji baz danych lokalnie. Z tego też względu chciałbym poświęcić trochę czasu na zaznajomienie się z bazami danych w wersji innej niż np. wersja MongoDB w chmurze: Atlas. Postaram się w tym i w paru kolejnych wpisach przyswoić (i oczywiście omówić tu w prosty sposób) bazy danych typu SQL i No-SQL oraz ich najczęściej używane pakiety ORM (Objecr-Relational Mapping).

Relacyjne bazy danych

Na wstępie wypadałoby w skrócie opisać, czym są i po co nam te relacyjne bazy danych. Są to bazy danych składające się z tabel (relacji), każda tabela składa się z rekordów (krotek) danych, które można przyrównać do wierszy w tabeli. Kolumny w tej tabeli to atrybuty, określające wartości, jakimi charakteryzuje się rekord tabeli. Każda tabela powinna posiadać tzw. primary key, czyli identyfikator, na podstawie którego będziemy budować relacje z innymi tabelami. Zazwyczaj określamy go jako id rekordu. Pozwala on też na wydajniejsze pobieranie konkretnych rekordów z tabeli. Podobny do primary key jest foreign key. Jest to identyfikator rekordu innej tabeli będący w relacji z danym rekordem. Na podstawie primary i foreign keys definiujemy relacje między tabelami. Dla przykładu posiadamy tabele users i tabele posts. Każdy user ma swoje unikalne id. Z tego względu, każdemu postowi możemy przypisać użytkownika, który go dodał, dodając do tabeli posts atrybut np. authorId, zawierający id twórcy.

Implementacją relacyjnej bazy danych, z jakiej będziemy korzystać w tym wpisie, jest MySQL. Instalacja serwera jest dość prosta. Ze względu na to, że powstało wiele opisów jak to zrobić, nie będę się tu nad tym skupiał. Zakładam, że mamy zainstalowany serwer MySQL lokalnie na komputerze oraz nadaliśmy hasło zabezpieczające dla root'a i możemy odpalić serwer z poziomu konsoli.

mysql -u root -p

Zostaniemy zapytani o hasło, a po wpisaniu hasła otworzy nam się interaktywny wiersz poleceń MySQL. Rozpoznamy go po prompcie mysql>.

Co to jest właściwie ten SQL

SQL (Structured Query Language) jest językiem zapytań, służącym do komunikacji z bazą danych. Jako komunikację rozumiemy pobieranie, dodawanie, edycję, czy też usuwanie danych. Połączenie z bacą danych dokonywane jest przy pomocy systemu zarządzania bazą danych, w naszym wypadku będzie to MySQL. To System zarządzania dokonuje zmian albo zwraca elementy bazy, ale my za pomocą języka SQL przekazujemy mu, komendy co ma zrobić.

Nowy użytkownik bazy danych

Dobrym pomysłem na początek jest dodanie nowego użytkownika, aby nie tworzyć każdej kolejnej bazy danych za pomocą root'a.

CREATE USER 'sebastian'@'localhost' IDENTIFIED BY 'pass123';

Query OK, 0 rows affected (0.22 sec)

Możemy teraz sprawdzić jakich użytkowników widzi MySQL.

SELECT User, Host FROM mysql.user;

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| sebstian         | localhost |
+------------------+-----------+
6 rows in set (0.05 sec)

Początek tej tabeli nas nie interesuje, gdyż są to klienci serwera. Dla nas ważne są root i nasz świeżo utworzony użytkownik. Teraz musimy dodać użytkownikowi uprawnienie, gdyż w tym momencie nie może on praktycznie nic jeszcze robić.

GRANT ALL PRIVILEGES ON * . * TO 'sebastian'@'localhost';

FLUSH PRIVILEGES;

Te dwie komendy nadadzą użytkownikowi sebastian wszystkie możliwe uprawnienia. Na etapie produkcji rozdawanie tak uprawnień będzie niedopuszczalne, ale podczas nauki jest całkiem ok.

Aby wyjść z interaktywnej konsoli MySQL wystarczy wpisać komendę exit.

exit

Pierwsza baza danych i tabela

Zacznijmy od zalogowanie się do MySQL na nowo utworzone konto.

mysql -u sebsatian -p

Gdy mamy już zalogowanego użytkownika i możemy przy jego pomocy komunikować się z bazą danych (ma nadane prawa), dodajmy pierwszą bazę danych, w której będą przechowywane wszystkie nasze dane w tabelach.

CREATE DATABASE sqlbasics;

Możemy teraz sprawdzić, jakie posiada bazy danych nasz zalogowany użytkownik.

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nodemysql          |
| performance_schema |
| sqlbasics          |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

Teraz, aby móc dodawać tabele i rekordy, musimy wybrać, z którą bazą danych chcemy pracować. W naszym wypadku będzie to oczywiście świeżo utworzona baza sqlbasics. Pozostałe bazy danych są to systemowe twory, zostawmy je więc w spokoju.

USE sqlbasics;

Możemy teraz dodać pierwszą tabelę, w której będą znajdować się rekordy naszych użytkowników. Tworząc tabelę, od razu na sztywno deklarujemy, jakie będą argumenty tabeli i jakie wartości te argumenty będą mogły przyjmować. Tak nieelastyczny model jest jedna z podstawowych cech relacyjnych baz danych. Wszystkie rekordy muszą wpisywać się w ten ściśle scharakteryzowany model, aby zostać dodane do tabeli.

Argumenty rekordu mogą przyjmować bardzo wiele różnych typów, oto podstawowe:

• INT - wartości liczbowe całkowite;
• TINYINT - jednobitowy INT, zazwyczaj do określania wartości boolean, jako ze w MySQL nie ma typu BOOL;
• FLOAT - liczba zmiennoprzecinkowa, defaultowo 4-bitowa;
• DATE - data (bez czasu), wyświetlana w formacie RRRR-MM-DD;
• DATETIME - data z czasem dnia wyświetlane według formatu RRRR-MM-DD GG:MM:SS;
• TIMESTAMP - data i czas liczony od początku epoki systemu UNIX, pokazywana jako liczba sekund;
• CHAR - pole z wartością znakową o stałej długości z zakresu od 1 do 255 bajtów;
• VARCHAR - pole z wartością znakową o zmiennej długości z zakresu od 1 do 255 bajtów;
• TEXT - pole tekstowe o rozmiarze nieprzekraczającym 65 535 bajtów, do przechowywania długich wartości tekstowych.

Dla każdego definiowanego argumentu tabeli oprócz definiowania typu musimy zdefiniować również maksymalną długość, jaka może zostać wprowadzona (określamy ją w nawiasie, za typem argumentu). Tak więc nasza pierwsza tabela będzie wyglądała w następujący sposób.

CREATE TABLE users(
    -> id INT AUTO_INCREMENT,
    -> name VARCHAR(50),
    -> email VARCHAR(50),
    -> password VARCHAR(100),
    -> job VARCHAR(50),
    -> register_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    -> PRIMARY KEY(id)
    -> );

Ten zapis mówi, że nowa tabela będzie miała nazwę users i będzie zawierała argumenty: id, name, email, password i register_date. Parametr AUTO_INCREMENT dla argumentu id oznacza, że każdy kolejny rekord będzie dostawał kolejne całkowite id. Za to PRIMART_KEY(id) przypisuje id rekordu jako identyfikator, na podstawie którego będziemy mogli szybko wyszukiwać rekordy lub tworzyć relacje z innymi tabelami. DEFAULT CURENT_TIMESTAMP oznacza, że polu register_date dajemy defaultową wartość.

Możemy teraz sprawdzić, czy nasz tabela znajduje się w bazie danych.

SHOW TABLES;

+---------------------+
| Tables_in_sqlbasics |
+---------------------+
| users               |
+---------------------+
1 row in set (0.02 sec)

Dodawanie rekordów do bazy danych

Gdy posiadamy już bazę danych a w niej tabelę pora na dodanie pierwszego rekordu.

INSERT INTO users (name, email, password, job) VALUES ('Sebastian', 'sebastian@mail.com', 'pass123', 'front-end developer');

Rekord dodajemy przy pomocy metody INSERT INTO, definiując, do jakiej tabeli chcemy dodać nasz rekord, następnie w nawiasie definiujemy, do jakich argumentów będziemy przypisywać dane. Na końcu musimy w kolejnym nawiasie po parametrze VELUES wpisać wartości argumentów. Kolejność nazw argumentów i ich wartości musi się zgadzać. Jako że register_date ustawiamy z defaultu, nie musimy go podawać.

Możemy też dodawać po kilka rekordów jednocześnie.

INSERT INTO users (name, email, password, job) VALUES ('John', 'john@gmail.com', 'pass123', 'back-end developer'), ('Sam', 'sam@yahoo.com', 'pass123', 'designer');

Odczytywanie rekordów z bazy danych

Podstawowym sposobem czytania z bazy danych jest metoda SELECT bez definiowania argumentów (*), podajemy tylko z jakiej tabeli pobieramy dane.

SELECT * FROM users;

+----+-----------+--------------------+----------+---------------------+---------------------+
| id | name      | email              | password | job                 | register_date       |
+----+-----------+--------------------+----------+---------------------+---------------------+
| 1  | Sebastian | sebastian@mail.com | pass123  | front-end developer | 2019-10-27 13:55:19 |
| 2  | John      | john@gmail.com     | pass123  | back-end developer  | 2019-10-27 14:17:34 |
| 3  | Sam       | sam@yahoo.com      | pass123  | designer            | 2019-10-27 14:17:34 |
+----+-----------+--------------------+----------+---------------------+---------------------+
3 rows in set (0.00 sec)

Możemy też odczytać tylko interesujące nas argumenty, np.:

SELECT name, job FROM users;

+-----------+---------------------+
| name      | job                 |
+-----------+---------------------+
| Sebastian | front-end developer |
| John      | back-end developer  |
| Sam       | designer            |
+-----------+---------------------+
3 rows in set (0.00 sec)

Gdy chcemy zawęzić pobierane rekordy, możemy dodać parametr WHERE do query stringu, a po nim zdefiniować do chcemy otrzymać. Poza operatorem przyrównania możemy również używać np. znaków mniejszości i większości dla wartości numerycznych.

SELECT * FROM users WHERE job='designer';

+----+------+---------------+----------+----------+---------------------+
| id | name | email         | password | job      | register_date       |
+----+------+---------------+----------+----------+---------------------+
| 3  | Sam  | sam@yahoo.com | pass123  | designer | 2019-10-27 14:17:34 |
+----+------+---------------+----------+----------+---------------------+
1 row in set (0.01 sec)

Przydatne może być też sortowanie danych. Posortujmy np. po imionach w kolejności rosnącej.

SELECT * FROM users ORDER BY name ASC;

+----+-----------+----------------+----------+---------------------+---------------------+
| id | name      | email          | password | job                 | register_date       |
+----+-----------+----------------+----------+---------------------+---------------------+
| 2  | John      | john@gmail.com | pass123  | back-end developer  | 2019-10-27 14:17:34 |
| 5  | Sam       | sam@yahoo.com  | pass123  | designer            | 2019-10-27 14:52:52 |
| 1  | Sebastian | seba@gmail.com | pass123  | front-end developer | 2019-10-27 13:55:19 |
+----+-----------+----------------+----------+---------------------+---------------------+
3 rows in set (0.01 sec)

Możemy również przeszukiwać rekordy tabeli na podstawie wartości w argumentach. Służy do tego metoda LIKE, po niej mówimy, czego szukamy (składnia zbliżona do regex).

SELECT * FROM users WHERE job LIKE '%end%';

+----+-----------+----------------+----------+---------------------+---------------------+
| id | name      | email          | password | job                 | register_date       |
+----+-----------+----------------+----------+---------------------+---------------------+
| 1  | Sebastian | seba@gmail.com | pass123  | front-end developer | 2019-10-27 13:55:19 |
| 2  | John      | john@gmail.com | pass123  | back-end developer  | 2019-10-27 14:17:34 |
+----+-----------+----------------+----------+---------------------+---------------------+
2 rows in set (0.00 sec)

Taki zapis zapytania mówi: zwróć wszystkie rekordy tabeli users, gdzie w argumencie job występuje wartość 'end' przed którą i za którą może występować dowolna ilość znaków.

Ostatnim z podstawowych parametrów związanym z pokazywaniem danych jest IN

SELECT * FROM users WHERE id IN (1, 5)

+----+-----------+----------------+----------+---------------------+---------------------+
| id | name      | email          | password | job                 | register_date       |
+----+-----------+----------------+----------+---------------------+---------------------+
| 1  | Sebastian | seba@gmail.com | pass123  | front-end developer | 2019-10-27 13:55:19 |
| 5  | Sam       | sam@yahoo.com  | pass123  | designer            | 2019-10-27 14:52:52 |
+----+-----------+----------------+----------+---------------------+---------------------+

Deklarujemy w ten sposób, że SQL ma nam zwrócić rekordy, gdzie id ma wartość 1 lub 5.

Zmiany w rekordach tabeli

W tabeli możemy również dokonywać zmian już istniejących rekordów. Robimy to za pomocą metody UPDATE, definiujemy po niej, w której tabeli chcemy robić zmiany i jaki argument zmieniamy. Aby nie zmienić wszystkich rekordów w tabeli, musimy pamiętać, aby zawęzić query metodą WHERE.

UPDATE users SET email = 'seba@gmail.com' WHERE id = 1;

Rows matched: 1 Changed: 1 Warnings: 0

Rekordy usuwamy za pomocą metody DELETE, tu również musimy określić, który rekord chcemy usunąć, aby przez przypadek nie usunąć wszystkich.

DELETE FROM users WHERE id = 3;

Po naszych zmianach tabele _users_ wygląda następująco. Zauważmy, że obie nasze wcześniejsze metody, _UPDATE_ i _DELETE_ zadziałały. Rekord o _id=3_ został usunięty, a _mail_ dla pierwszego rekordu się zmienił.

```mysql:terminal
SELECT * FROM users;

+----+-----------+----------------+----------+---------------------+---------------------+
| id | name      | email          | password | job                 | register_date       |
+----+-----------+----------------+----------+---------------------+---------------------+
| 1  | Sebastian | seba@gmail.com | pass123  | front-end developer | 2019-10-27 13:55:19 |
| 2  | John      | john@gmail.com | pass123  | back-end developer  | 2019-10-27 14:17:34 |
+----+-----------+----------------+----------+---------------------+---------------------+
2 rows in set (0.00 sec)

Relacje między bazami danych

Główną zaletą baz relacyjnych baz danych, poza z góry zdefiniowanym modelem danych, są właśnie relacje. Za pomocą primary key i foreign key możemy połączyć dwie bazy danych, a dokładnie rekordy z dwóch baz danych.

W pierwszej kolejności zdefiniujmy nową tabelę posts, która będzie zawierała user_id powiązane z rekordem tabeli users.

CREATE TABLE posts(
    -> id INT AUTO_INCREMENT,
    -> user_id INT,
    -> title VARCHAR(100),
    -> body TEXT,
    -> publish_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    -> PRIMARY KEY(id),
    -> FOREIGN KEY (user_id) REFERENCES users(id)
    -> );

posts również posiada PRIMARY KEY, którym jest jego id. FOREIGN KEY (user_id) REFERENCES user(id) mówi o tym, że argument user_id odpowiada argumentowi id tabeli users.

Dodajmy więc kilka postów do tabeli posts.

INSERT INTO posts(user_id, title, body) VALUES (1, 'Post One', 'This is post one'),(5, 'Post Two', 'This is post two'),(5, 'Post Three', 'This is post three'),(5, 'Post Four', 'This is post four'),(2, 'Post Five', 'This is post five'),(1, 'Post Six', 'This is post six'),(2, 'Post Seven', 'This is post seven'),(1, 'Post Eight', 'This is post eight'),(5, 'Post Nine', 'This is post none');

Records: 9 Duplicates: 0 Warnings: 0

SELECT * FROM posts;

+----+---------+------------+--------------------+---------------------+
| id | user_id | title      | body               | publish_date        |
+----+---------+------------+--------------------+---------------------+
| 1  | 1       | Post One   | This is post one   | 2019-10-27 15:32:34 |
| 2  | 5       | Post Two   | This is post two   | 2019-10-27 15:32:34 |
| 3  | 5       | Post Three | This is post three | 2019-10-27 15:32:34 |
| 4  | 5       | Post Four  | This is post four  | 2019-10-27 15:32:34 |
| 5  | 2       | Post Five  | This is post five  | 2019-10-27 15:32:34 |
| 6  | 1       | Post Six   | This is post six   | 2019-10-27 15:32:34 |
| 7  | 2       | Post Seven | This is post seven | 2019-10-27 15:32:34 |
| 8  | 1       | Post Eight | This is post eight | 2019-10-27 15:32:34 |
| 9  | 5       | Post Nine  | This is post none  | 2019-10-27 15:32:34 |
+----+---------+------------+--------------------+---------------------+

Gdy posiadamy już dwie tabele, z czego w tabeli posts mamy foreign key odwołujący się do id z tabeli users, możemy powiązać wyszukiwania z dwóch tabel w jedno za pomocą JOIN. Skupimy się na INNER JOIN, po pozostałe warianty odsyłam do dokumentacji.

SELECT users.name, posts.title, posts.body, posts.publish_date
    -> FROM users
    -> INNER JOIN posts
    -> ON users.id = posts.user_id
    -> ORDER BY posts.publish_date;

+-----------+------------+--------------------+---------------------+
| name      | title      | body               | publish_date        |
+-----------+------------+--------------------+---------------------+
| Sam       | Post Two   | This is post two   | 2019-10-27 15:32:34 |
| Sam       | Post Three | This is post three | 2019-10-27 15:32:34 |
| Sam       | Post Four  | This is post four  | 2019-10-27 15:32:34 |
| Sebastian | Post One   | This is post one   | 2019-10-27 15:32:34 |
| Sam       | Post Nine  | This is post none  | 2019-10-27 15:32:34 |
| Sebastian | Post Six   | This is post six   | 2019-10-27 15:32:34 |
| Sebastian | Post Eight | This is post eight | 2019-10-27 15:32:34 |
| John      | Post Five  | This is post five  | 2019-10-27 15:32:34 |
| John      | Post Seven | This is post seven | 2019-10-27 15:32:34 |
+-----------+------------+--------------------+---------------------+

W zapytaniu deklarujemy, co chcemy otrzymać. users.name definiuje, że oczekujemy argumentu name z tabeli users. Następne dwa wiersze mówią, którą tabelę połączyć z którą. Parametr ON warunkuje, który argument z pierwszej tabeli odwołuje się do foreign key w drugiej. Ostatnia linia ORDER BY, jak sama nazwa wskazuje, sortuje wyniki.

Podsumowanie

Opis ten tylko zarysowuje wszystkie możliwości języka zapytań SQL, w tym wypadku w wariancie silnika MySQL. Po więcej przykładów i bardziej rozbudowane przykłady odsyłam do SQL cheet sheet from Brad Traversy. Nie ukrywam, że było to dla mnie inspiracją do stworzenia tego wpisu.

W następnym wpisie będę starał się wykorzystać MySQL w aplikacji CRUD napisanej przy pomocy Node.js i Express.