Wykonując ćwiczenia przedstawione w tym e‑materiale, nabierzesz wprawy w posługiwaniu się kwerendamikwerendakwerendami w konkretnych sytuacjach problemowych. Tym razem zajmiemy się zapytaniami SQL: wstawiającymi, modyfikującymi, usuwającymi dane, jak również wpływającymi na strukturę tabel.
Pliki potrzebne do ćwiczeń
W e‑materiale Bazy danych – ćwiczenia, etap IPzdnHclWpBazy danych – ćwiczenia, etap I dowiedzieliśmy się, jak zainstalować pakiet XAMPP. Znaleźć tam można również pliki baz danych potrzebne do wykonania ćwiczeń w całej serii oraz opis struktury tabel, czyli przeznaczenie, a także typ wartości każdej kolumny we wszystkich wykorzystywanych bazach danych.
Ważne!
Wykorzystywane bazy danych pochodzą z zadań maturalnych.
Zadania oraz bazy zostały opracowane przez Centralną Komisję Egzaminacyjną i pojawiły się na egzaminach maturalnych z informatyki (poziom rozszerzony, cz. ):
w maju roku (baza vod),
w maju roku (baza rekrutacja),
w czerwcu roku (baza jezyki).
Wszystkie arkusze egzaminacyjne można znaleźć na stronie internetowej CKE.
Polecenia do zrealizowania
Ta część e‑materiału zawiera zestaw poleceń do wykonania, wyniki działania kwerend oraz przykładowe rozwiązania zadań wraz z wnioskami z każdego wykonanego ćwiczenia.
Polecenie 1
Baza danych: vod
Stwórz zapytanie, które wstawi do bazy nowe wypożyczenie. Klient Henryk Polarek w dniu 2020‑12‑22 zamówił film pt. „Szeregowiec Ryan”.
Poprawne wyniki kwerendy – wstawionych rekordów: 1
ID_wyp
Data_wyp
ID_filmu
Pesel
501
2020‑12‑22
AA1998
58073022657
Przykładowa poprawna kwerenda:
Linia 1. INSERT INTO wypozyczenia.
Linia 2. VALUES otwórz nawias okrągły 501 przecinek cudzysłów 2020 minus 12 minus 22 cudzysłów przecinek cudzysłów AA1998 cudzysłów przecinek cudzysłów 58073022657 cudzysłów zamknij nawias okrągły.
INSERT INTO wypozyczenia
VALUES (501, "2020-12-22", "AA1998", "58073022657")
Wersja alternatywna kwerendy:
Linia 1. INSERT INTO wypozyczenia otwórz nawias okrągły ID podkreślnik wyp przecinek Data podkreślnik wyp przecinek ID podkreślnik filmu przecinek Pesel zamknij nawias okrągły.
Linia 2. VALUES otwórz nawias okrągły 501 przecinek cudzysłów 2020 minus 12 minus 22 cudzysłów przecinek cudzysłów AA1998 cudzysłów przecinek cudzysłów 58073022657 cudzysłów zamknij nawias okrągły średnik.
Jeżeli wartości atrybutów wprowadzamy zgodnie z kolejnością (i liczbą) kolumn wynikającą ze struktury tabeli, to nawias okrągły, zawierający wypis atrybutów, umieszczony przed klauzulą VALUES, staje się opcjonalny – to znaczy można go umieścić, ale niekoniecznie trzeba.
Ponieważ w tabeli nie ma włączonego trybu AUTO_INCREMENT dla klucza podstawowego, to wpisano ręcznie pierwszą wolną wartość, czyli 501. Gdyby autoinkrementacja była w tabeli włączona, to zamiast konkretnej liczby można jako wartość klucza podać NULL. System wówczas sam przypisze rekordowi pierwszą, niewykorzystaną wartość liczbową.
Po wykonaniu zapytania otrzymamy jedynie informację o wstawieniu rekordu. Aby rzeczywiście zobaczyć nowy, istniejący rekord w tabeli, należy dodatkowo otworzyć jej podgląd.
Wartości będące łańcuchami koniecznie zamykamy w cudzysłowie lub ewentualnie wewnątrz apostrofów.
Polecenie 2
Baza danych: vod
Stwórz zapytanie, które wstawi do bazy nowy film - „The Social Network” o identyfikatorze SN2010. Gatunku i kraju produkcji tego filmu nie chcemy jeszcze podawać (te kolumny mają tymczasowo pozostać puste w rekordzie). Koszt wypożyczenia to 7 zł.
Poprawne wyniki kwerendy – wstawionych rekordów: 1
ID_filmu
Tytul
Kraj_produkcji
Gatunek
Cena_w_zl
SN2010
The Social Network
7
Przykładowa poprawna kwerenda:
Linia 1. INSERT INTO filmy.
Linia 2. VALUES otwórz nawias okrągły cudzysłów SN2010 cudzysłów przecinek cudzysłów The Social Network cudzysłów przecinek cudzysłów cudzysłów przecinek cudzysłów cudzysłów przecinek 7 zamknij nawias okrągły średnik.
INSERT INTO filmy
VALUES ("SN2010", "The Social Network", "", "", 7);
Wersja alternatywna kwerendy:
Linia 1. INSERT INTO filmy otwórz nawias okrągły ID podkreślnik filmu przecinek Tytul przecinek Cena podkreślnik w podkreślnik zl zamknij nawias okrągły.
Linia 2. VALUES otwórz nawias okrągły cudzysłów SN2010 cudzysłów przecinek cudzysłów The Social Network cudzysłów przecinek 7 zamknij nawias okrągły średnik.
INSERT INTO filmy (ID_filmu, Tytul, Cena_w_zl)
VALUES ("SN2010", "The Social Network", 7);
Wnioski płynące z tego zapytania:
W przypadku pominięcia nawiasów okrągłych z wypisem kolejności i liczby wstawianych atrybutów umieszczonych przed klauzulą VALUES musimy stosować się do porządku kolumn określonego w strukturze tabeli. A zatem nieistniejące wartości wpisujemy w postaci pustych łańcuchów.
Jeżeli z użyciem dodatkowych nawiasów, umieszczonych przed klauzulą VALUES, podamy listę kolumn różną od tej zdefiniowanej w strukturze tabeli, to puste wartości możemy pominąć (wersja alternatywna kwerendy).
Kolumny pominięte w zapytaniu INSERT mogą zawierać zdefiniowane wartości domyślne, które ochronią tabelę przed wstawieniem wartości powodującej na przykład nieprawidłowe działanie systemu. W tej tabeli takich mechanizmów nie zdefiniowano dla atrybutów, choć można je łatwo dodać z użyciem opcji „Ustawienia domyślne” w widoku struktury tabeli.
System zarządzania bazą danych MySQL powinien zareagować ostrzeżeniami (ang. warning) o braku dwóch wartości w przypadku wykonania wersji alternatywnej zapytania. W wersji zachowującej oryginalną kolejność i liczbę atrybutów wartości podano, tyle że są to puste łańcuchy – stąd brak ostrzeżeń systemowych w tej wersji kwerendy.
Polecenie 3
Baza danych: vod
Dodaj dwóch nowych klientów wypożyczalni, ale za pomocą jednego zapytania:
Jan Ruciak, nr PESEL: 88062577999,
Anna Jaruga, nr PESEL: 98100716541.
Poprawne wyniki kwerendy – wstawionych rekordów: 2
Pesel
Imie
Nazwisko
88062577999
Jan
Ruciak
98100716541
Anna
Jaruga
Przykładowa poprawna kwerenda:
Linia 1. INSERT INTO klienci VALUES.
Linia 2. otwórz nawias okrągły cudzysłów 88062577999 cudzysłów przecinek cudzysłów Jan cudzysłów przecinek cudzysłów Ruciak cudzysłów zamknij nawias okrągły przecinek.
Linia 3. otwórz nawias okrągły cudzysłów 98100716541 cudzysłów przecinek cudzysłów Anna cudzysłów przecinek cudzysłów Jaruga cudzysłów zamknij nawias okrągły.
INSERT INTO klienci VALUES
("88062577999", "Jan", "Ruciak"),
("98100716541", "Anna", "Jaruga")
Wnioski płynące z tego zapytania:
To zapytanie uczy nas wstawiania wielu rekordów z użyciem jednej kwerendy. Każdy wiersz zamykamy w pomocniczych nawiasach okrągłych, rozdzielając rekordy przecinkami.
Ponieważ zachowano kolejność i liczbę atrybutów znaną ze struktury tabeli, to opcjonalny nawias przed klauzulą VALUES zawierający wypis wstawianych kolumn można pominąć.
Tego typu zapytania INSERT wstawiające wiele rekordów rozdzielonych przecinkami znajdziemy bardzo często w plikach *.sql będących zrzutami (eksportami) tabel.
Polecenie 4
Baza danych: rekrutacja
Dodaj do bazy danych kolejnego przedszkolaka, lecz koniecznie z użyciem klauzuli SET zamiast VALUES. Dane dziecka: Agata Chomik, lat 5, nr PESEL: 15291093503.
Poprawne wyniki kwerendy – wstawionych rekordów: 1
Pesel
Nazwisko
Imie
Plec
Wiek
p15291093503
Chomik
Agata
dziewczynka
5
Przykładowa poprawna kwerenda:
Linia 1. INSERT INTO dzieci SET Pesel znak równości cudzysłów p15291093503 cudzysłów przecinek.
Linia 2. Nazwisko znak równości cudzysłów Chomik cudzysłów przecinek Imie znak równości cudzysłów Agata cudzysłów przecinek.
Linia 3. Plec znak równości cudzysłów dziewczynka cudzysłów przecinek Wiek znak równości 5.
INSERT INTO dzieci SET Pesel = "p15291093503",
Nazwisko = "Chomik", Imie = "Agata",
Plec = "dziewczynka", Wiek = 5
Wnioski płynące z tego zapytania:
Ta kwerenda przedstawia alternatywną wersję zapytania INSERT, całkowicie opartą na klauzuli SET zamiast VALUES.
Warto zauważyć, że klauzula SET musi pojawić się tylko raz dla danego wstawianego rekordu, a kolejne kolumny wypisujemy po przecinku.
Ta wersja kwerendy jest dłuższa i nieco mniej czytelna od wariantu z klauzulą VALUES, przez co nie jest tak często stosowana przez programistów. Nie oznacza to jednak, że nie warto jej w ogóle stosować.
Polecenie 5
Baza danych: rekrutacja
Stwórz zapytanie, które zmieni błędnie określoną przez rodziców preferencję wyboru przedszkola (zgłoszenie nr 5). Sprawa dotyczy dziecka o identyfikatorze: p07222600090. Jako pierwszą preferencję wybrano aktualnie Przedszkole Niepubliczne Radosny Zakątek (identyfikator w bazie: 57), a dziecko miało zostać zapisane do Przedszkola nr 64 Kubuś Puchatek (identyfikator w bazie: 48).
Poprawne wyniki kwerendy – zmodyfikowanych rekordów: 1
Id
Pesel
Numer_preferencji
Id_przedszkola
5
p07222600090
1
48
Przykładowa poprawna kwerenda:
Linia 1. UPDATE preferencje SET Id podkreślnik przedszkola znak równości 48 WHERE Id znak równości 5 średnik.
UPDATE preferencje SET Id_przedszkola = 48 WHERE Id = 5;
Wnioski płynące z tego zapytania:
Kluczowa w każdym zapytaniu UPDATE jest klauzula WHERE – w przypadku jej braku zmieniamy WSZYSTKIE rekordy tabeli! Warto więc zawsze przed wysłaniem jakiegokolwiek nowo utworzonego zapytania UPDATE wykonać najpierw zrzut (eksport) całej bazy danych.
Klauzula SET zgodnie ze swoją nazwą (ang. set – ustawić) posłużyła nam do zdefiniowania nowej wartości dla wskazanej kolumny.
Po wykonaniu zapytania otrzymamy jedynie informację o zmodyfikowaniu rekordu. Aby rzeczywiście zobaczyć zmieniony wiersz w tabeli, należy dodatkowo otworzyć jej podgląd.
Polecenie 6
Baza danych: rekrutacja
Stwórz zapytanie, które zmieni bieżącą wartość identyfikatora Przedszkola Niepublicznego im. Panienki z Okienka na nową wartość równą 24.
Poprawne wyniki kwerendy – zmodyfikowanych rekordów: 1
Id_przedszkola
Nazwa_przedszkola
Liczba_miejsc
24
Przedszkole Niepubliczne im. Panienki z Okienka
20
Przykładowa, poprawna kwerenda:
Linia 1. UPDATE przedszkola SET Id podkreślnik przedszkola znak równości 24 WHERE.
Linia 2. przedszkola kropka Id podkreślnik przedszkola znak równości 20.
UPDATE przedszkola SET Id_przedszkola = 24 WHERE
przedszkola.Id_przedszkola = 20
Wnioski płynące z tego zapytania:
Zmiana wartości identyfikatora jest możliwa, ponieważ aktualnie w tabeli nie istnieje żaden rekord, w którym atrybut Id_przedszkola miałby wartość 24. Oczywiście gdyby taka wartość istniała w innym rekordzie, to zmiana nie mogłaby zostać przeprowadzona – nie mogą bowiem istnieć dwie identyczne wartości klucza podstawowego (klucz musi być unikalny).
Dokonana tutaj niewielka z pozoru modyfikacja niesie olbrzymie logiczne konsekwencje w tabeli preferencje. Każde dziecko przypisane wcześniej w tej tabeli do naszej placówki pozostaje z wybranym dla siebie przedszkolem o atrybucie Id_przedszkola nadal równym 20 – a to już nie jest Przedszkole Niepubliczne im. Panienki z Okienka! Rozwiązaniem problemu byłaby tylko kaskadowa aktualizacja wszystkich rekordów w tabeli preferencje - wszędzie, gdzie dla dziecka wybrano placówkę numer 20, należy zmienić wartość identyfikatora również na 24.
Po wykonaniu zapytania otrzymamy jedynie informację o zmodyfikowaniu rekordu. Aby rzeczywiście zobaczyć zmieniony rekord w tabeli, należy dodatkowo otworzyć jej podgląd.
Polecenie 7
Baza danych: jezyki
Usuń z bazy danych wszystkie języki należące rodziny indoeuropejskiej.
Poprawne wyniki kwerendy – zmodyfikowanych rekordów: 63
Przykładowa poprawna kwerenda:
Linia 1. DELETE FROM jezyki WHERE Rodzina znak równości cudzysłów indoeuropejska cudzysłów.
DELETE FROM jezyki WHERE Rodzina="indoeuropejska"
Wnioski płynące z tego zapytania:
Klauzula DELETE usuwa wybrane rekordy, natomiast struktura tabeli pozostaje nienaruszona.
Podobnie jak w przypadku użycia zapytania UPDATE, brak wpisania klauzuli WHERE jest równoznaczny z wykonaniem kwerendy na wszystkich rekordach tabeli. Zatem wpisanie tylko wartości DELETE FROM jezyki usunie wszystkie rekordy! Warto zatem zawsze przed wysłaniem jakiegokolwiek nowo utworzonego zapytania DELETE wykonać najpierw zrzut (eksport) całej bazy danych.
Poprawne wyniki kwerendy: brak rekordów w tabeli jezyki
Przykładowa poprawna kwerenda:
Linia 1. TRUNCATE jezyki.
TRUNCATE jezyki
Wnioski płynące z tego zapytania:
W zapytaniu TRUNCATE nie można wskazać za pomocą klauzuli WHERE, które dokładnie rekordy mają zostać usunięte. Kwerenda TRUNCATE wyczyści WSZYSTKIE rekordy tabeli.
Jest to kwerenda dużo szybsza w działaniu od zapytania DELETE, które kasuje rekordy kolejno wiersz po wierszu, dodatkowo tworząc wpisy w dzienniku transakcji.
Wydanie polecenia TRUNCATE zresetuje wartość parametru AUTO_INCREMENT dla klucza podstawowego tabeli (oczywiście o ile ta opcja jest dla tej kolumny aktualnie włączona).
Kwerenda TRUNCATE, podobnie jak DELETE, również nie usuwa struktury tabeli, a tylko zgromadzone w niej rekordy.
Polecenie 9
Baza danych: jezyki
Usuń z bazy tabelę o nazwie: panstwa (wraz z jej strukturą).
Poprawne wyniki kwerendy: brak tabeli panstwa w bazie jezyki
Przykładowa poprawna kwerenda:
Linia 1. DROP TABLE panstwa.
DROP TABLE panstwa
Wnioski płynące z tego zapytania:
W przeciwieństwie do poleceń DELETE oraz TRUNCATE, tego typu zapytanie usuwa nie tylko zgromadzone rekordy, lecz także strukturę samej tabeli.
Polecenie 10
Baza danych: jezyki
W tabeli uzytkownicy zmień nazwę kolumny Urzedowy na wersję angielską: Official.
Poprawne wyniki kwerendy: zmieniona nazwa kolumny
Przykładowa poprawna kwerenda:
Linia 1. ALTER TABLE uzytkownicy CHANGE Urzedowy Official.
Linia 2. TINYTEXT CHARACTER SET utf8 średnik.
ALTER TABLE uzytkownicy CHANGE Urzedowy Official
TINYTEXT CHARACTER SET utf8;
Wnioski płynące z tego zapytania:
Zapytanie ALTER modyfikuje strukturę kolumn tabeli. W tym przypadku za pomocą klauzuli CHANGE zmieniamy nazwę kolumny – najpierw podajemy bieżącą wartość, a po spacji nową, obowiązującą od tej pory nazwę atrybutu.
W przypadku kolumny tekstowej warto ustawić także używany w zapisanych łańcuchach zestaw znaków, czyli CHARACTER SET – tutaj jest to utf8.
Polecenie 11
Baza danych: jezyki
W tabeli panstwa usuń ze struktury kolumnę: Populacja.
Poprawne wyniki kwerendy: brak kolumny Populacja w tabeli panstwa
Przykładowa poprawna kwerenda:
Linia 1. ALTER TABLE panstwa DROP Populacja.
ALTER TABLE panstwa DROP Populacja
Wnioski płynące z tego zapytania:
W przypadku usuwania wybranej kolumny z tabeli stosujemy kombinację poleceń ALTER oraz DROP. Pamiętajmy, że usunięcie kolumny stanowi jednocześnie modyfikację już istniejącej struktury tabeli.
Oczywiście usunięcie kolumny spowoduje także utratę zgromadzonych w niej wartości tego atrybutu we wszystkich rekordach w tabeli.
Polecenie 12
Baza danych: jezyki
Odtwórz zapytaniem strukturę tabeli panstwa według następujących wytycznych dotyczących jej kolumn:
Panstwo – typ danych: VARCHAR(50) – identyfikator tekstowy oznaczający nazwę państwa; jest to klucz podstawowy tej tabeli, bez włączonego AUTO_INCREMENT;
Kontynent – typ danych: TINYTEXT – kontynent, na którym leży stolica państwa;
Populacja – typ danych: FLOAT – całkowita liczba mieszkańców podana w milionach, z dokładnością do jednego miejsca po przecinku.
Poprawne wyniki kwerendy: istniejąca tabela panstwa (struktura, bez rekordów)
Przykładowa poprawna kwerenda:
Linia 1. CREATE TABLE panstwa otwórz nawias okrągły.
Linia 2. Panstwo varchar otwórz nawias okrągły 50 zamknij nawias okrągły NOT NULL przecinek.
Linia 3. Kontynent tinytext CHARACTER SET utf8.
Linia 4. COLLATE utf8 podkreślnik bin przecinek.
Linia 5. Populacja float przecinek.
Linia 6. PRIMARY KEY otwórz nawias okrągły Panstwo zamknij nawias okrągły.
Linia 7. zamknij nawias okrągły DEFAULT CHARSET znak równości utf8 średnik.
CREATE TABLE panstwa (
Panstwo varchar(50) NOT NULL,
Kontynent tinytext CHARACTER SET utf8
COLLATE utf8_bin,
Populacja float,
PRIMARY KEY(Panstwo)
) DEFAULT CHARSET=utf8;
Wnioski płynące z tego zapytania:
Tworzenie tabeli wymaga podania nazw i typów poszczególnych kolumn dla atrybutów tekstowych. Warto też określić używany zestaw znaków – tutaj jest to zestaw: utf8.
Nadanie klucza podstawowego następuje tuż po zdefiniowaniu ostatniej kolumny: PRIMARY KEY(Panstwo).
Zapytania CREATE odnajdziemy bardzo często w plikach *.sql będących zrzutami (eksportami) tabel.
Słownik
kwerenda
kwerenda
(ang. query – zapytanie) - zapisane z użyciem języka SQL polecenie skierowane do systemu bazodanowego, którego celem może być: pobranie, dodanie, modyfikacja lub usunięcie danych albo sposobu ich przechowywania w systemie
pole
pole
(inaczej: kolumna tabeli) - jednostkowa cecha opisująca element (obiekt) tabeli, np. pole „Nazwisko” może być jedną z kolumn tabeli „Uczeń”
rekord
rekord
(inaczej: wiersz tabeli lub krotka) - pojedynczy kompletny zestaw danych dotyczący konkretnego elementu (obiektu) w tabeli
SZBD
SZBD
System Zarządzania Bazą Danych - narzędzie/aplikacja do tworzenia bazy danych (np. MySQL, PostgreSQL, Firebird, Oracle, Microsoft Access)
tabela
tabela
podstawowa struktura logiczna relacyjnej bazy danych definiowana jako zestaw pól (kolumn) opisujących właściwości obiektów, które ma przechowywać