Dzień dobry! Dawno nie było nowego wpisu i miło mi tu wrócić :) Zakup nieruchomości zajął mi mnóstwo czasu, ale biuro już się urządza i przygotowałam dla was powitalny wpis. Będzie lekko i prosto, bo zaczniemy od podstaw SQL. Miło mi też widzieć, że czasami tu wracacie, a starsze wpisy są nadal czytane! Dziękuję!

Tak więc, początkujący programiści, ruszajcie do czytania!

Co to jest ten SQL?

SQL to język zapytań w którym porozumiewamy się z relacyjnymi bazami danych. Istnieją różne metody pośrednie takie jak przyjazne użytkownikowi programy z graficznym UI (gdzie wyklikujemy zapytanie SQL) czy ORMy, gdzie zapytania są generowane w czasie działania aplikacji. Niestety nie zawsze są optymalne. Dlatego warto poświęcić czas na naukę SQL :)

Baza danych

Żeby wykonać zapytanie musimy mieć bazę danych. Dzisiaj będzie wirtualna, nie potrzebujemy znać typów.

Definicja bazy: posiada trzy tabele: user, address i łączącą je tabelę relacją wiele do wielu: user_adress.

Tabela user ma pola: uuid, username, email, created_at, updated_at.

Tableta address ma pola: uuid, nip, street.

Tabela user_address ma pola user_uuid oraz address_uuid.

Zadania

Osobiście najlepiej uczę się przez zadania oraz wyzwania, więc prezentuję Wam zadania (dziękuję Aniołowi za pomoc w ich tworzeniu) na rozgrzewkę. Dzisiaj będziemy pobierać dane, użyjemy do tego operatora SELECT, który do tego służy.

Zadanie 1: sortowanie i wybieranie kolumn

Zacznijmy powoli :) Z naszej tabeli chcemy pobrać username i email wszystkich użytkowników i posortować od najdłużej istniejącego. Przejdziemy przez pierwsze zapytanie krokami :)

Najpierw pobierzmy wszystkich użytkowników:

SELECT * FROM user;

Jednak tabela user może mieć bardzo dużo kolumn i części tych danych nie potrzebujemy, więc wybierzmy konkretne:

SELECT username, email FROM user;

Na koniec możemy posortować użytkowników po dacie ich utworzenia, żeby w pierwszej kolejności mieć najdłużej istniejących użytkowników.

SELECT username, email FROM user ORDER BY created_at;

Proste, prawda?

Zadanie 2:  Filtrowanie danych poprzez WHERE

Teraz pobierzemy tylko tych użytkowników, którzy posiadają numer telefonu.

Do stawiania warunków w zapytaniu służy operator WHERE , wykorzystamy również IS NOT oraz NULL (zwykle nieobowiązkowe wartości są NULLami, jeśli ich nie wypełnimy).

SELECT username, phone FROM user WHERE phone IS NOT NULL;

W ramach ćwiczenia można również spróbować wylistować użytkowników, którzy nie podali swojego numeru telefonu żeby wysłać im przypomnienie:

SELECT username, phone FROM user WHERE phone IS NULL;

Zadanie 3: LIKE

Bardzo przydatny jest operator LIKE które w przeciwieństwie do porównania pozwala szukać wyrażeń na początku, w środku i na końcu stringa, w różnych kombinacjach. Najczęściej używa do tego znaków _ oraz %, gdzie _ odpowiada za jeden znak a % za dowolny ciąg znaków (włączając pusty).

Teraz wybierzmy sobie użytkowników z mailami w domenie gmail.com:

SELECT username, email FROM user WHERE email like '%@gmail.com';

Można je dowolnie mieszać. Na przykład możemy szukać maili z dowolną końcówką. Google akurat nie oferuje takiej opcji, ale żeby kontynuować przykład:

SELECT username, email FROM user WHERE email like '%@gmail.%';

To wyrażenie niestety złapie także imie.nazwisko@gmail.kotek oraz imie.nazwisko@gmail. i w teorii możemy coś na to poradzić.

Jeśli mówimy o rozwiązaniach produkcyjnych to nie szłabym w tę stronę, ale istnieje w SQL narzędzie do budowania czegoś w stylu wyrażeń regularnych i wygląda to mniej więcej tak:

1. [] wyszukuje znaki znajdujące się w kwadratowych nawiasach np.: przez wyrażenie k[o]t przejdzie 'kot', ale już nie 'kat'. Przez wyrażenie k[ao]t przejdzie zarówno 'kot' jak i 'kat', ale nie 'kit'
2. [^] wyszukuje znaki nie znajdujące się w kwadratowych nawiasach. Na przykład przez wyrażenie k[^i]t przejdzie 'kot', 'kat', ale ponownie nie 'kit'.
3. [a-c] wyszukuje przedział znaków, np.: tutaj znaki od a do c włącznie. Przykładowo przez wyrażenie k[a-i]t przejdzie zarówno 'kat' jak i 'kit', ale już nie 'kot', bo literka 'o' jest poza przedziałem od 'a' do 'i'.

SELECT username, email from user where email like '%@gmail.__%';

Nie wiem czy chcę dalej brnąć w koty, katów i kit, więc przejdźmy dalej :)

Zadanie 4: LEFT JOIN

Teraz pobierzmy dane z dwóch tabel na raz, wyszukując użytkownika wraz z nipem jeśli istnieje w bazie.

Tego typu operacje robi się poprzez JOIN. Istnieje kilka rodzajów, troszkę się od siebie różnią, ale zaczniemy powolutku.

LEFT JOIN wraca wszystkie wyniki z lewego zapytania i dopasowuje istniejące z prawego. Dlatego gdy wywołamy poniższe zapytanie dostaniemy wszystkich użytkowników i nipy ich firm jeśli istnieją. Jeśli nie istnieją to zapytanie zamiast nipu zwróci pustą wartość.

SELECT user.username, address.nip FROM user LEFT JOIN user_address on user.uuid = user_address.user_uuid LEFT JOIN address ON user_address.address_uuid = address.uuid;

Zadanie 5: (INNER) JOIN

Teraz pobierzmy użytkownika o NIPie 3398554396.

Tutaj potrzebujemy tylko użytkowników, którzy mają zdefiniowane adresy, więc zastosujemy JOIN oraz dodamy operator WHERE który pozwoli wyfiltrować wartości.

SELECT username, address.nip FROM user JOIN user_address ON user.uuid = user_address.user_uuid JOIN address ON user_address.address_uuid = address.uuid WHERE address.nip = '3398554396'

Zadanie 6: LIMIT

Rzadko chcemy zwracać wszystkie rekordy z bazy, czasem potrzebujemy tylko kilku najważniejszych. Przykładowy przypadek: trzech najdłużej istniejących użytkowników dostanie rabat. Oto jak ich znaleźć:

SELECT username, email FROM user ORDER BY created_at LIMIT 3;

Podsumowanie

Cieszę się, że wytrwaliście do końca :) Potrzebowałam czegoś na rozruszanie, mam nadzieje, że wpis się przyda. Jak zwykle zapraszam do komentowania i poprawiania jeśli zauważycie błąd.

Trzymajcie się i nie przestawajcie uczyć!