Baza danych

W tym e‑materiale poznamy sposoby modyfikacji zawartości bazy danych na przykładzie bazy danych Pracownicy, którą zamieszczamy w wersjach dla programu LibreOffice Base oraz Microsoft Access. Odpowiedni plik należy pobrać i zapisać na dysku.

Żeby wykonywać omówione dalej zapytania SQL w programie LibreOffice Base, wybieramy polecenie Narzędzia | SQL, natomiast w programie Microsoft Access wybieramy wstążkę Tworzenie, następnie Projekt kwerendy, zamykamy okno Pokazywanie tabeli i klikamy ikonę SQL.

Modyfikacja danych

Modyfikowanie danych to jedna z podstawowych operacji CRUDoperacje CRUDCRUD, które możliwe są do wykonania w bazie danych.

Dzięki temu, że nazwy stanowisk są w osobnej tabeli stanowiska, a nie np. w tabeli place lub pracownicy, zmiany wymaga tylko jedna wartość. W języku SQL mamy instrukcję aktualizującą dane, której ogólna postać jest następująca:

UPDATE nazwa_tabeli
SET pole1 = wartość, pole2 = wartość, ...
WHERE warunek;

Wykorzystamy tę klauzulę do zmiany nazwy stanowiska. Na początku wyświetlimy listę stanowisk.

SELECT * FROM stanowiska;

Na liście wyników powinniśmy zobaczyć rekord o identyfikatorze 7 i tekście „goniec” w polu stanowisko. Do wskazania rekordu, który chcemy zaktualizować, możemy użyć dotychczasowej nazwy stanowiska:

• w LibreOffice Base:

UPDATE "stanowiska"
SET "stanowisko" = 'kurier'
WHERE "stanowisko" = 'goniec';

• w Microsoft Access:

UPDATE stanowiska
SET stanowisko = 'kurier'
WHERE stanowisko = 'goniec';

W bazach danych obsługiwanych przez strony internetowe o wiele częściej rekordy wskazuje się przez klucze główne, które są osadzane w formularzach i przesyłane później do serwera. Nasze zapytanie może więc wyglądać również następująco:

• w LibreOffice Base:

UPDATE "stanowiska"
SET "stanowisko" = 'kurier'
WHERE "id" = 7;

• w Microsoft Access:

UPDATE stanowiska
SET stanowisko = 'kurier'
WHERE id = 7;

Korzystanie z kluczy głównych konieczne jest wtedy, gdy aktualizujemy dane w tabelach zależnych.

W tabeli kontakty rekordy przyporządkowane są do pracowników za pomocą kluczy obcych, czyli unikalnych wartości kluczy głównych z tabeli pracownicy. Musimy więc odczytać identyfikator pracownika. Użyjemy zapytania:

• w LibreOffice Base:

SELECT "id" FROM "pracownicy"
WHERE "imie" = 'Igor' AND "nazwisko" = 'Majchrzak';

• w Microsoft Access:

SELECT id FROM pracownicy
WHERE imie = 'Igor' AND nazwisko = 'Majchrzak');

Przedstawiona kwerenda zwraca identyfikator, który pozwala wskazać rekord odpowiadający danemu pracownikowi w tabeli zależnej. Użyjemy tej kwerendy w podzapytaniu, aby zmienić dane:

• w LibreOffice Base:

UPDATE "kontakty"
SET "telefon" = '768123098'
WHERE "id_pracownika" = (
    SELECT "id" FROM "pracownicy"
    WHERE "imie" = 'Igor' AND "nazwisko" = 'Majchrzak');

• w Microsoft Access:

UPDATE kontakty
SET telefon = '768123098'
WHERE id_pracownika = (
    SELECT id FROM pracownicy
    WHERE imie = 'Igor' AND nazwisko = 'Majchrzak');

Po wykonaniu kodu możemy użyć zapytania wybierającego wszystkie rekordy z tabeli kontakty do sprawdzenia, czy dane zostały zmienione.

Usuwanie danych

Usuwanie, podobnie jak zmienianie danych, jest również jedną z podstawowych operacji CRUDoperacje CRUDCRUD implementowanych w każdej bazie.

Przed usuwaniem danych możemy wykonać kopię przynajmniej najważniejszych informacji. Poniższe zapytania wybierają wszystkie rekordy z tabeli pracownicy i zapisują w tabeli pracownicy_kopia.

• W LibreOffice Base:

SELECT * INTO "pracownicy_kopia" FROM "pracownicy";

• W Microsoft Access:

SELECT * INTO pracownicy_kopia FROM pracownicy;

Polecenie DELETE usuwa całe rekordy, dlatego poza wskazaniem tabeli podajemy warunek wybierający rekordy do usunięcia. Spróbujemy więc usunąć dane pracownika o identyfikatorze 4 (Uwaga! Mogą pojawić się problemy, które opisane zostały poniżej).

• w LibreOffice Base:

DELETE FROM "pracownicy" WHERE "id" = 4;

• w Microsoft Access:

DELETE FROM pracownicy WHERE id = 4;

Podczas próby wykonania zapytań w obydwu programach zobaczymy ostrzeżenie o naruszeniu integralności danych. Ostrzeżenia są wynikiem relacji, która łączy tabelę pracownicy i place: identyfikator pracownika o wartości 4 występuje w tabeli place jako klucz obcy.

W takim wypadku usunięcie danych o pracowniku z identyfikatorem 4 wymaga wcześniejszego usunięcia związanych z nim danych w tabelach powiązanych: place i kontakty. W sumie wykonujemy trzy zapytania:

• w LibreOffice Base:

DELETE FROM "place" WHERE "id_pracownika" = 4;
DELETE FROM "kontakty" WHERE "id_pracownika" = 4;
DELETE FROM "pracownicy" WHERE "id" = 4;

• w Microsoft Access wykonujemy zapytania pojedynczo:

DELETE FROM place WHERE id_pracownika = 4;
DELETE FROM kontakty WHERE id_pracownika = 4;
DELETE FROM pracownicy WHERE id = 4;

Zastanówmy się, czy zawsze konieczne jest usuwanie danych z tabel zależnych zanim usuniemy je z tabeli głównej. Wszystko zależy od sposobu zdefiniowania ograniczeń nałożonych na relacje łączące tabele.

W LibreOffice Base podczas definiowania relacji oprócz połączenia pól możemy zaznaczyć w narzędziu Opcje usuwania opcję Usuń kaskadowo:

RTlfA4wP1MnFJ

Zrzut ekranu przedstawia okno dialogowe zatytułowane Relacje. W opcjach Złączone tabele wpisano place oraz pracownicy. W opcjach Złączone pola, w kolumnie place wpisano id_pracownika, w kolumnie pracownicy wpisano id. Poniżej znajdują się opcje aktualizacji, wybrano Brak akcji. W opcjach usuwania wybrano usuń kaskadowo. Poniżej przyciski Pomoc, Ok, Anuluj.

W Microsoft Access oprócz wymuszania więzów integralności w oknie definiowania relacji możemy zaznaczyć Kaskadowo usuń rekordy pokrewne:

Rq3Yrehlwyofh

Zrzut ekranu przedstawia okno dialogowe zatytułowane Edytowanie relacji. Pokrewna tabela/kwerenda wybrano opcje pracownicy (id) i place (id_pracownika). Poniżej zaznaczono opcje Wymuszaj więzy integralności oraz Kaskadowo usuń rekordy pokrewne. Typ relacji ustawiony jest jako Jeden - do - wielu. Obok przyciski OK, Anuluj, Typ sprzężenia, Utwórz nowe.

W języku SQL ograniczenia te zdefiniowane musiałyby być w poleceniach tworzących tabele zależne przy użyciu klauzuli ON CASCADE, np.:

CREATE TABLE "place" (
    ...
    FOREIGN KEY ("id_pracownika") REFERENCES "pracownicy"("id")
    ON DELETE CASCADE
);

Podsumowując, jeżeli podczas definiowania relacji łączącej tabelę zależne z główną nałożono ograniczenie kaskadowego usuwania rekordów, do usunięcia danych – w tym wypadku pracownika – można użyć jednego polecenia, które próbowaliśmy wydać na początku.

Słownik