Baza danych

W pliku firma.sql.zip zapisano schemat oraz dane bazy danych firma. Pobieramy plik i zapisujemy w wybranym katalogu.

Uruchamiamy środowisko XAMPP, otwieramy przeglądarkę i w polu adresu wpisujemy localhost. Po załadowaniu się strony powitalnej klikamy link phpMyAdmin w prawym górnym rogu.

Wykonujemy import bazy firma. W tym celu klikamy zakładkę Import, a następnie przycisk Przeglądaj i wskazujemy zapisany plik firma.sql. Następnie klikamy przycisk Import na dole strony. Po wykonaniu operacji zobaczymy komunikat „Import zakończony sukcesem, wykonano 43 zapytań. (firma.sql)”. Ewentualne komunikaty o błędach pomijamy.

Schemat bazy

Utworzoną bazę danych wybieramy w lewym panelu aplikacji phpMyAdmin. Po wybraniu nazwy tabeli zobaczymy zawarte w niej pola i dane, w zakładce Struktura znajdziemy informacje na temat typów danych i ewentualnych ograniczeń.

R1A2LYkk4fAa0

Podgląd struktury tabeli w aplikacji phpMyAdmin. Po lewej stronie jest lista, tam między innymi: Nowa, firma, Nowy, klienci, produkty, transakcje, uskarbowe, wojewodztwa. Po prawej stronie u góry jest pasek z napisami: Serwer: localhost, Baza danych: firma, Tabela: transakcje. Poniżej są zakładki: Struktura tabeli i Widok relacyjny. Zakładka Widok relacyjny jest szara. Poniżej jest tabela o nagłówkach: hasz, Nazwa, Typ, Metoda porównywania napisów, Atrybuty, Null, Ustawienia domyślne, Komentarze. W tabeli są dane.

Relacje między tabelami możemy podejrzeć po wybraniu opcji Widok relacyjny w zakładce Struktura.

R1FcLxisN7WQS

Ilustracja przedstawia podgląd relacji między tabelami w aplikacji phpMyAdmin. U góry jest pasek z napisami: Serwer: localhost, Baza danych: firma, Tabela: transakcje. Poniżej są zakładki: Przeglądaj, Struktura, SQL, Szukaj, Wstaw, Eksport, Import, Uprawnienia, Więcej. Wybrano zakładkę: Struktura. Wybrano Widok relacyjny. Następnie jest otwarta tabela o nazwie: Ograniczenia klucza obcego. W tabeli są kolumny: Działania, Właściwości ograniczenia, Kolumna, Ograniczenia klucza obcego (INNODB).

Podane zapytania wprowadzamy do pola tekstowego po wybraniu bazy firma w lewym panelu i otworzeniu zakładki SQL – powinniśmy widzieć w niej komunikat „Wykonanie zapytania/zapytań SQL do bazy danych firma:”. Wpisane lub wklejone zapytanie zostanie wykonane po kliknięciu przycisku Wykonaj na dole strony. Po wyświetleniu wyników zapytania należy kliknąć przycisk Pokaż okno zapytań, aby powrócić do okna Wykonanie zapytania.

Analiza danych

Skonstruujemy kwerendę krok po kroku.

SELECT * FROM produkty;

Prosta kwerenda wybierająca pokaże informacje o towarach. Spróbujmy policzyć, który sprzedawał się najczęściej.

SELECT COUNT(*)
FROM transakcje
WHERE id_p = 'EG';

Zapytanie zwróci wartość 1333.

Powyższe zapytanie używa funkcji agregującejfunkcje agregującefunkcji agregującej COUNT() do zliczenia rekordów z tabeli transakcje, w których identyfikator produktu ma wartość EG. Ale czy musimy wykonywać taką kwerendę dla każdego produktu? Nie.

Język SQL definiuje klauzulę GROUP BY kolumna, służącą do zgrupowaniagrupowaniezgrupowania rekordów według powtarzających się wartości w podanej kolumnie. Spróbujemy wykorzystać ją do pogrupowania rekordów z tabeli transakcje według identyfikatorów produktów, a następnie zliczymy każdą grupę. Słowo kluczowe AS pozwala na nadanie nazwy kolumnie, w której znajdą się wyniki zliczania.

SELECT id_p, COUNT(*) AS liczba_sprzedazy
FROM transakcje
GROUP BY id_p;

Wyniki możemy dodatkowo posortować malejąco, umieszczając na końcu zapytania klauzulę ORDER BY liczba_sprzedazy DESC. Zwróćmy uwagę, że nazwa pola liczba_sprzedazy jest aliasem funkcji agregującej.

Nadal nie mamy nazw produktów, dlatego warto rozszerzyć kwerendę o dane z tabeli produkty.

SELECT id_p, nazwa_p, COUNT(*) liczba_sprzedazy
FROM transakcje
JOIN produkty USING (id_p)
GROUP BY id_p
ORDER BY liczba_sprzedazy DESC;

Początkowe zwrócone rekordy:

M   Mial weglowy                   1806
EGS Wegiel Eko Groszek SILESIAN    1800
OO  Olej opalowy                   1353
EG  Wegiel Eko Groszek PROMETEUSZ  1333
K   Wegiel – Koks                  907

Wbrew pozorom nie musimy pisać zupełnie nowej kwerendy, wystarczy, że zmodyfikujemy poprzednią. Możemy to zrobić np. w ten sposób:

SELECT id_p, nazwa_p, jedn_cena, (COUNT(*) * jedn_cena) AS wartosc_sprzedazy
FROM transakcje
JOIN produkty USING (id_p)
GROUP BY id_p
ORDER BY wartosc_sprzedazy DESC;

Początkowe zwrócone rekordy:

OO   Olej opalowy                   3520   4762560
EGS  Wegiel Eko Groszek SILESIAN    919   1654200
EG   Wegiel Eko Groszek PROMETEUSZ  859   1145047
K    Wegiel – Koks                  1159   1051213
M    Mial weglowy                   569   1027614

Poprzednie zapytanie pokazało wartość sprzedaży wszystkich produktów. Jeżeli chcemy nałożyć na tę wartość jakiś warunek, nie możemy tego zrobić w klauzuli WHERE, ponieważ jest ona wykonywana przed grupowaniem rekordów, a wartość sprzedaży, czyli funkcja agregująca, jest w tym przypadku wykonywana po grupowaniu. W takich sytuacjach używamy klauzuli HAVING, w której podajemy warunki nakładane na pogrupowane rekordy.

SELECT id_p, nazwa_p, jedn_cena, (COUNT(*) * jedn_cena) AS wartosc_sprzedazy
FROM transakcje
JOIN produkty USING (id_p)
GROUP BY id_p
HAVING wartosc_sprzedazy > 1000000
ORDER BY wartosc_sprzedazy DESC;

Zwrócone rekordy:

OO  Olej opalowy                   3520  4762560
EGS Wegiel Eko Groszek SILESIAN    919  1654200
EG  Wegiel Eko Groszek PROMETEUSZ  859  1145047
K   Wegiel – Koks                  1159  1051213
M   Mial weglowy                   569  1027614

Zaczynamy od zliczenia transakcji poszczególnych klientów:

SELECT id_k, COUNT(*) AS liczba_zakupow
FROM transakcje
GROUP BY id_k
HAVING liczba_zakupow > 10
ORDER BY liczba_zakupow DESC;

Zapytanie wybiera rekordy z tabeli transakcje, grupuje według identyfikatorów klientów i zlicza każdą grupę. Dodamy teraz dane osobowe klientów, dołączając tabelę klienci:

SELECT imie,nazwisko, COUNT(*) AS liczba_zakupow
FROM transakcje
JOIN klienci USING (id_k)
GROUP BY transakcje.id_k
HAVING liczba_zakupow > 10
ORDER BY liczba_zakupow DESC;

Początkowe zwrócone rekordy:

Tworzyslawa  Romanuszko  16
Wrocislawa   Pergulak    14
Sulislawa    Pierzchat   14
Selma        Chalminski  14
Nina         Wedolski    14

Widzimy już imiona i nazwiska, spróbujmy dowiedzieć się czegoś więcej o klientach firmy.

Interesujące nas dane znajdują się w tabelach klienci i wojewodztwa. Żeby policzyć klientów z poszczególnych województw, dane należy pogrupować według identyfikatorów województw lub ich nazw:

SELECT nazwa_w, COUNT(*) AS liczba_klientow
FROM klienci
INNER JOIN wojewodztwa USING (id_w)
GROUP BY id_w
ORDER BY COUNT(*) DESC;

Początkowe zwrócone rekordy:

mazowieckie    224
slaskie        182
lodzkie        119
wielkopolskie  112
dolnoslaskie   105

Najwięcej klientów jest z województwa mazowieckiego. Duża liczba klientów z poszczególnych województw niekoniecznie musi się jednak przekładać na liczbę zamówień. Sprawdźmy.

Uzyskanie potrzebnych informacji wymaga użycia trzech tabel źródłowych: transakcje, klienci i wojewodztwa. Interesuje nas liczba transakcji zawartych przez klientów z poszczególnych województw, dlatego dane pogrupujemy według ich identyfikatorów (lub nazw). Dołączamy tabelę klienci, ponieważ kojarzy klientów z województwami, które z kolei odczytujemy z drugiej dołączonej tabeli wojewodztwa.

SELECT nazwa_w, COUNT(*) AS liczba_zamowien
FROM transakcje
INNER JOIN klienci USING (id_k)
INNER JOIN wojewodztwa USING (id_w)
GROUP BY id_w
ORDER BY COUNT(*) DESC;

Początkowe zwrócone rekordy:

mazowieckie    1533
slaskie        1165
lodzkie        809
dolnoslaskie   729
wielkopolskie  721

Wyniki zapytania pozwalają stwierdzić, że na pierwszych pięciu miejscach liczba klientów idzie w parze z liczbą transakcji. Wniosek ten wymaga jednak sprawdzenia.

Podobnie jak w dwóch poprzednich zapytaniach rekordy z tabeli transakcje pogrupujemy według województw. Liczbę transakcji uzyskamy, zliczając identyfikatory klientów, które mogą się w tabeli powtarzać. Dlatego użyjemy klauzuli DISTINCT, aby uzyskać liczbę unikalnych klientów.

SELECT nazwa_w, COUNT(id_k) liczba_transakcji, COUNT(DISTINCT(id_k)) liczba_klientow
FROM transakcje
INNER JOIN klienci USING (id_k)
INNER JOIN wojewodztwa USING (id_w)
GROUP BY id_w
ORDER BY COUNT(*) DESC;

Początkowe zwrócone rekordy:

mazowieckie   1533   213
slaskie       1165   168
lodzkie       809   110
dolnoslaskie  729   100
wielkopolskie 721   107

Jeżeli przeanalizujemy wyniki poprzedniej kwerendy, która zwracała liczbę klientów z poszczególnych województw, zauważymy, że klientów z województwa mazowieckiego było 224, a ze śląskiego 182. Jednak ostatnia kwerenda w polu liczba_klientow podaje inne wyniki: 213 i 168 odpowiednio. Dlaczego?

Żeby odpowiedzieć na to pytanie, użyjemy innego niż zazwyczaj typu złączenia. Najczęstsze złączenie INNER JOIN dopasowuje rekordy na podstawie takiej samej wartości w polach tworzących relację między tabelami. Są zwracane tylko pasujące rekordy.

Mamy jednak również złączenia lewo- i prawostronne, czyli LEFT lub RIGHT JOIN. Dane pobierane są wtedy z obydwu tabel, rekordy są dopasowywane, ale zapytanie zwraca również rekordy, dla których nie znaleziono danych w drugiej tabeli. Pola z tej tabeli są wypełniane wartościami NULL. Spróbujmy wykonać poniższą kwerendę:

SELECT DISTINCT(nazwisko), klienci.id_k, transakcje.id_k
FROM klienci
LEFT JOIN transakcje USING(id_k)
ORDER BY transakcje.id_k;

Wybieramy rekordy z tabeli klienci i dopasowujemy je do rekordów z tabeli transakcje. Jest to złączenie lewostronne, więc zostają zwrócone wszystkie rekordy z tabeli klienci, również tych klientów, których identyfikatory nie wystąpiły w tabeli transakcje. Pola z tabeli transakcje dla klientów, którzy niczego nie kupili, są wypełniane wartościami NULL.

Początkowe zwrócone rekordy:

Baluszewski AdBa399    NULL
Pihulak     AgPi19586  NULL
Cierbi      AnCi6725   NULL
Jaskowiec   BeJa6450   NULL
Kosmeda     BeKo3804   NULL

Rekordy z wartościami NULL możemy wybrać, korzystając z warunku w klauzuli WHERE, a na koniec wystarczy je zliczyć przy użyciu funkcji agregującej. Zapytanie może wyglądać następująco:

SELECT COUNT(*) klienci_bez_zamowien
FROM klienci
LEFT JOIN transakcje USING(id_k)
WHERE transakcje.id_k IS NULL;

W ten sposób dowiemy się, że mamy w bazie 77 klientów, którzy nie złożyli żadnego zamówienia.

SELECT COUNT(*)
FROM transakcje
LEFT JOIN klienci USING(id_k)
WHERE klienci.id_k IS NULL;

Rozbudowujemy poprzednią kwerendę o złączenie INNER JOIN z tabelą wojewodztwa i dodajemy warunek w klauzuli WHERE:

SELECT nazwa_w, COUNT(*)
FROM klienci
INNER JOIN wojewodztwa USING(id_w)
LEFT JOIN transakcje USING(id_k)
WHERE transakcje.id_k IS NULL AND nazwa_w = 'mazowieckie';

Dostajemy odpowiedź. Dla 11 klientów nie ma żadnego wpisu w tabeli transakcje. Wynik zgadza się z wcześniejszymi obserwacjami. Liczba klientów z mazowieckiego, którzy zawarli transakcje, wyniosła 213. Jeżeli dodamy 11, uzyskamy wynik 224, czyli liczbę wszystkich klientów z tego województwa.

Słownik