Wstęp
Cześć, czas na lekcję dziesiątą!
Ostatnio stworzyliśmy wspólnie pierwsze tabele, wypełniliśmy je danymi oraz poprawialiśmy lub kasowaliśmy informacje.
W realnym świecie, zanim baza przyjmie ostateczną postać, bywa często zmieniana. Wyobraź sobie, że tworzymy sklep naszej księgarni, Wpadamy na pomysł dodania jak ostatnio tabeli z promocjami, mamy książki, postanawiamy dodać oceny, albo przypominamy obie, że do zamówienia jeszcze coś trzeba dołączyć. Dane się zmieniają, ale i ich struktura przechodzi ewolucję.
Zmienialiśmy dane, teraz poprawimy same tabele!
Modyfikacja istniejącej tabeli – ALTER
Baza relacyjna, jak sama nazwa wskazuje, opiera się na relacjach pomiędzy tabelami. Silnik bazodanowy, nie tylko posiada informacje o kluczach obcych, czyli identyfikatorach w tabeli A z tabeli B (ale to już wiesz) ale i pilnuje by ta relacja naprawdę zachodziła. Na przykład, dodaję zamówienie:
INSERT INTO zamowienia (data_zlozenia, klient_id) VALUES ('2025-01-01', 999);W sytuacji naszego sklepu, nie mamy klienta o id 999. Sam silnik bazy danych, nie powinien pozwolić na dodanie takiego rekordu. W większych bazach, tak by się stało, jednak w SQLite, musimy uruchomić ten mechanizm. W tej chwili, powyższe by zadziałało a to błąd!
w edytorze SQL wpisz
PRAGMA foreign_keys = ON;i uruchom ten fragment
i teraz, gdy spróbuję dodać taki rekord jak wyżej:
Jak widzisz wywołało to błąd, strażnik zadziałał. Nie możemy dodać rekordu, gdzie jako klienta wybieramy nieistniejący rekord.
Wróćmy teraz do naszej bazy zamówień, i ostatnio dodanej tabeli z promocjami. Zakładamy, że promocje się nie łączą, i jedno zamówienie, może, ale nie musi być powiązane z jakaś promocją.
Mamy więc tabelę zamówienia, które w tej chwili powiązane są z tabelami pozycje_zamowienia oraz klienci:
Witać tu relacje jeden do wielu, czyli jeden klient może mieć wiele zamówień, oraz jedno zamówienie może mieć wiele pozycji.
mamy świeżą tabelę z promocjami
Musimy powiązać relacją jeden do jednego promocje z zamowienia.
Do modyfikacji struktur w SQL służy komenda ALTER.
ALTER TABLE zamowienia
ADD COLUMN promocja_id INTEGER DEFAULT NULL REFERENCES promocje(id);Powyższy kod, zmienia tabelę zamowienia
ADD COLUMN promocja_id – dodaję kolumnę o typie INTEGER, gdzie wartościa domyślna jest null, czyli nie musi być tu wartości, a także za pomocą REFERENCES ustawia to jako klucz obcy, tworzy tak zwany constraint, który będzie pilnował danych.
odśwież widok tabeli i widzimy:
Zmienił się również diagram
Doskonale, o to chodziło, możemy teraz dodać zamówienie, gdzie użyto promocji.
Transakcje oraz pobieranie id ostatnio dodanego rekordu + skomplikowany insert
W realnym świecie, odbędzie się to w programie, np. zrobimy coś takiego na kursie programowania (jeśli jeszcze nie zacząłeś to zapraszam: kurs programowania ), ale nawet robiąc to od strony programistycznej, użyjemy dwóch rzeczy, których jeszcze nie znasz. Pierwsza to transakcje a druga, pobieranie ostatnio dodanego id w danej sesji.
Po kolei
Rozpoczynamy TRANSAKCJĘ (wszystko albo nic) – to oznacza, że system notuje moment, będziemy mogli wprowadzać zmiany w bazie, i potem je sprawdzić, jeśli będą ok, to zrobimy CIMMIT czy zatwierdzenie tych zamian, a mozemy zrobić rollback i powrócić do niepopsutych przez nas np .danych.
BEGIN TRANSACTION;KROK 1: Wstawiamy nagłówek zamówienia (Tabela Matka)
Używamy INSERT … SELECT, aby dynamicznie obliczyć cenę z rabatem
INSERT INTO zamowienia (data_zlozenia, klient_id, status, promocja_id, wartosc_calkowita)
SELECT
DATE('now'), -- Dzisiejsza data
1, -- Zakładamy klienta ID 1
'Nowe',
p.id, -- ID promocji (czyli 1 - Black Friday)
-- TU JEST OBLICZENIE: Cena książki * (1.0 - mnożnik rabatu)
-- 39.90 * (1.0 - 0.2) = 39.90 * 0.8 = 31.92
39.90 * (1.0 - (p.rabat_procent / 100.0))
FROM promocje p
WHERE p.id = 1;W TYM MOMENCIE baza wstawiła zamówienie i wie, jakie ma ID.
Użyjemy funkcji last_insert_rowid(), żeby je pobrać w kolejnym kroku.
KROK 2: Wstawiamy pozycję zamówienia (Tabela Córka)
INSERT INTO pozycje_zamowienia (zamowienie_id, ksiazka_id, ilosc, cena_za_sztuke)
VALUES (
last_insert_rowid(), -- <-- MAGIA! Tu wskoczy ID zamówienia z Kroku 1
3, -- ID książki Solaris
1, -- Ilość
39.90 -- Oryginalna cena za sztukę (bez rabatu)
);Zatwierdzamy TRANSAKCJĘ (zapisujemy zmiany na stałe)
COMMIT;Poniżej całosć
BEGIN TRANSACTION;
INSERT INTO zamowienia (data_zlozenia, klient_id, status, promocja_id, wartosc_calkowita)
SELECT
DATE('now'), -- Dzisiejsza data
1, -- Zakładamy klienta ID 1
'Nowe',
p.id, -- ID promocji (czyli 1 - Black Friday)
-- TU JEST OBLICZENIE: Cena książki * (1.0 - mnożnik rabatu)
-- 39.90 * (1.0 - 0.2) = 39.90 * 0.8 = 31.92
39.90 * (1.0 - (p.rabat_procent / 100.0))
FROM promocje p
WHERE p.id = 1;
INSERT INTO pozycje_zamowienia (zamowienie_id, ksiazka_id, ilosc, cena_za_sztuke)
VALUES (
last_insert_rowid(), -- <-- MAGIA! Tu wskoczy ID zamówienia z Kroku 1
3, -- ID książki Solaris
1, -- Ilość
39.90 -- Oryginalna cena za sztukę (bez rabatu)
);
COMMIT;Sprawdźmy więc, co się dodało, wyświetlimy w query wszystkie dane, z trzech tabel, czyli zamówienia, pozycje zamówień oraz promocje.
select z.zamowienie_id , z.data_zlozenia , k.tytul , p.nazwa , z.wartosc_calkowita
from zamowienia z
JOIN pozycje_zamowienia pz on z.zamowienie_id = pz.zamowienie_id
join ksiazki k on pz.ksiazka_id = k.ksiazka_id
join promocje p on z.promocja_id = p.id
order by z.data_zlozenia desc ;Wybrałem kolumny, z zamówienia, książki aby było widać tytuł, i z join na promocje.
Widoki (Views) czyli query zapisane na dłużej
W czasie pracy z bazami danych, często zachodzi potrzeba wywoływania takich samych zapytań. Na przykład, zamiast co jakiś czas generować złączenia, aby podejrzeć komplet danych, np statystycznych o klientach, możemy sobie zapisać takie zapytanie na stałe. Nazywa się to view.
Od strony pobierania danych wygląda wtedy jak baza, ale jest to przez silnik bazodanowy optymalizowane, i działa lepiej, niż po prostu query wprowadzone w edytor.
Powiedzmy, że chcemy informację, o Klientach, ich ilości zamówień, kwocie sumarycznej, oraz dacie ostatniego zamówienia.
SELECT
k.imie,
k.nazwisko,
k.email,
COUNT(z.zamowienie_id) AS ilosc_zamowien,
COALESCE(SUM(z.wartosc_calkowita), 0) AS laczna_kwota_zamowien,
MAX(z.data_zlozenia) AS data_ostatniego_zamowienia
FROM
klienci k
LEFT JOIN
zamowienia z ON k.klient_id = z.klient_id
GROUP BY
k.klient_id,
k.imie,
k.nazwisko,
k.email
ORDER BY
laczna_kwota_zamowien DESC; 
Jeśli coś takiego często sprawdzamy to warto mieć takie dane na stałe w bazie.
Oto jak to zrobić – przed sam select dodaj CREATE VIEW klienci_raport AS
Teraz zobacz, odśwież Views w nawigatorze (prawy myszy i refresh) i zobaczyć po rozwinięciu nasz widok.
Chwila oddechu – Magia Funkcji Tekstowych
Uff, ALTER TABLE i Klucze Obce to ciężki kaliber. Na koniec zróbmy coś lżejszego i bardzo przydatnego. Zobaczmy, jak SQL potrafi manipulować tekstem, żeby posprzątać bałagan w danych.
Wróćmy do naszej głównej tabeli klienci.
1. Sklejanie tekstów (Konkatenacja ||)
Często chcemy wyświetlić pełne dane w jednej kolumnie. Używamy do tego dwóch pionowych kresek ||.
SELECT imie || ' ' || nazwisko AS pelne_imie_i_nazwisko
FROM klienci;
2. Sprzątanie wielkości liter (UPPER, LOWER)
Masz w bazie bałagan w mailach (raz duże, raz małe litery)? Ujednolickmy to.
SELECT
email AS oryginalny_email,
LOWER(email) AS email_malymi,
UPPER(email) AS email_duzymi
FROM klienci;
3. Podmiana tekstu (REPLACE)
Idealne do poprawiania literówek w całej bazie naraz. REPLACE(tekst, co_zamienic, na_co_zamienic)
-- Załóżmy, że w tabeli wydawnictwa mamy błąd i chcemy go poprawić w locie
SELECT nazwa, REPLACE(nazwa, 'Znak', 'Wydawnictwo Znak') AS nowa_nazwa
FROM wydawnictwa
WHERE nazwa LIKE '%Znak%';Rozwiązanie Zadania Domowego (z Lekcji 9)
Tym razem dla odmiany na końcu.
Zadanie: Stwórz tabelę recenzje, wstaw dane, zaktualizuj je i usuń.
Rozwiązanie Krok po Kroku:
-- 1. CREATE (Tworzenie tabeli)
CREATE TABLE recenzje (
id INTEGER PRIMARY KEY AUTOINCREMENT,
autor TEXT,
ocena INTEGER,
tresc TEXT
);
-- 2. INSERT (Wstawianie danych)
INSERT INTO recenzje (autor, ocena, tresc) VALUES
('Jan Kowalski', 5, 'Super książka, nie mogłem się oderwać!'),
('Anna Nowak', 2, 'Nuda, strasznie się dłuży.'),
('Piotr Wiśniewski', 4, 'Solidna pozycja, ale końcówka słaba.');
-- 3. UPDATE (Aktualizacja z WHERE!)
-- Zmieniamy recenzję Anny (zakładamy, że ma ocenę 2)
UPDATE recenzje
SET ocena = 4, tresc = 'Jednak po drugim czytaniu jest OK'
WHERE ocena = 2;
-- 4. DELETE (Usuwanie)
-- Usuwamy recenzję z najwyższą oceną (zakładamy, że to ta Jana z oceną 5)
DELETE FROM recenzje
WHERE ocena = 5;
-- Sprawdzenie końcowego stanu:
SELECT * FROM recenzje;
Jeśli Twoja tabela recenzje istnieje i zawiera teraz dwie recenzje z ocenami 4 – gratulacje! Jesteś pełnoprawnym administratorem bazy danych.
Zadanie domowe
- stwórz sobie kilka raportów, używając wiedzy z poprzednich lekcji, jak obliczanie średniej i inne i stwórz z tego widok.
- Dodaną w zadaniu domowym z poprzedniej lekcji tabelę, uzbrój w klucz obcy do tabeli książki, i potem za pomocą update, powiąż dane z recenzjami z konkretną książką.
0 komentarzy