Baza danych

Do odtworzenia bazy i wykonywania zapytań użyjemy lokalnego środowiska XAMPP, zawierającego serwer WWW Apache, bazę danych MariaDB oraz aplikację webową phpMyAdmin działającą w przeglądarce WWW i umożliwiającą obsługę baz danych za pomocą interfejsu graficznego.

Załączony plik szkola2.sql pobieramy i zapisujemy w wybranym katalogu.

Podane dalej polecenia wykonamy za pomocą aplikacji phpMyAdmin, którą uruchamiamy, wpisując w polu adresu przeglądarki internetowej localhost/phpmyadmin. Jeżeli środowisko XAMPP jest uruchomione, widzimy stronę główną, na której klikamy zakładkę SQL. W oknie Uruchom zapytanie/zapytania SQL na serwerze "localhost": wpisujemy lub wklejamy pojedyncze zapytania lub kilka zapytań kończących się znakiem średnika. Zapytania uruchamiamy kliknięciem przycisku Wykonaj (lub Go w niektórych wersjach aplikacji).

Zaczynamy od utworzenia bazy szkola2. Wykonujemy polecenia:

DROP DATABASE IF EXISTS szkola2;
CREATE DATABASE szkola2;

Pierwsze polecenie usuwa bazę szkola2, jeżeli wcześniej istniała. Drugie tworzy pustą bazę o takiej samej nazwie.

Kolejną czynnością jest utworzenie tabel i dodanie do nich danych. Odpowiednie polecenia zapisane zostały w pliku szkola2.sql. Wybieramy nazwę utworzonej bazy szkola2 w panelu po lewej stronie, następnie klikamy zakładkę Import. W oknie Importowanie do bazy danych "szkola2" w sekcji Plik do importu naciskamy Przeglądaj i wskazujemy wymieniony plik. Na dole klikamy przycisk Import. Po udanym imporcie zobaczymy komunikat Import zakończony sukcesem, wykonano 49 zapytań. (szkola2.sql).

/opt/lampp/bin/mysql -u root -p -e "DROP DATABASE szkola2"
/opt/lampp/bin/mysql -u root -p -e "CREATE DATABASE szkola2"
/opt/lampp/bin/mysql -u root -p szkola2 < szkola2.sql

Żeby zapoznać się z bazą danych, wykonujemy polecenia:

USE szkola2;
SHOW TABLES;

Polecenie USE wskazuje bazę danych, z której chcemy korzystać. W phpMyAdmin można je pominąć, jeżeli wcześniej klikniemy nazwę bazy w lewym panelu, a następnie zakładkę SQL. Polecenie SHOW TABLES wyświetla listę tabel.

Zmiany w bazie

W tabeli przedmioty mamy dwie kolumny zawierające imiona i nazwiska nauczycieli. Nie jest to najlepsze rozwiązanie, np. gdybyśmy chcieli z nauczycielami łączyć inne informacje w bazie, np. telefony czy adresy e‑mail, lub też gdyby jeden nauczyciel uczył wielu przedmiotów.

W tabeli uczniowie mamy kolumnę klasa przechowującą powtarzające się nazwy klas. Dane nie powinny się powtarzać, ponadto zazwyczaj z klasami kojarzymy więcej informacji, np. wychowawcę lub funkcje klasowe.

Z powyższych obserwacji możemy wysnuć wniosek, że dobrze byłoby utworzyć dwie dodatkowe tabele – jedną z danymi dotyczącymi nauczycieli, drugą z danymi na temat klas.

Tabela nauczyciele

Tabelę nauczyciele z nazwiskami i imionami nauczycieli tworzymy za pomocą podzapytania wybierającego posortowane dane z tabeli przedmioty.

CREATE TABLE nauczyciele AS
SELECT naucz_nazw, naucz_imie FROM przedmioty ORDER BY naucz_nazw;

Zbadajmy strukturę utworzonej tabeli, aby sprawdzić wykorzystane typy danych:

DESCRIBE nauczyciele;

Teraz możemy zmienić nazwy pól:

ALTER TABLE nauczyciele CHANGE naucz_nazw nazwisko VARCHAR(30);
ALTER TABLE nauczyciele CHANGE naucz_imie imie VARCHAR(30);

Do zmiany nazw wykorzystujemy klauzulę ALTER TABLE ... CHANGE ..., w której oprócz nowej nazwy pola podajemy jego typ danych.

Tabela z samymi nazwiskami i imionami jest bezużyteczna, nie będziemy w stanie powiązać jej z żadną inną tabelą. Aby utworzyć relacje,relacjarelacje, dodajemy do tabeli klucz głównyklucz głównyklucz główny:

ALTER TABLE nauczyciele
ADD COLUMN id_naucz INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT;

Powyższa klauzula pozwala dodawać do tabel nowe pola. Zwróćmy uwagę, że po członie ADD COLUMN podajemy nazwę pola, następnie typ danych i ewentualne ograniczenia.

RbbpcMr6LCzJx

Zrzut ekranu przedstawiający tabelę nauczyciele. Tabela składa się z sześciu kolumn opisanych kolejno: Field, Type, Null, Key, Default, Extra. Dane wpisano w trzech wierszach. W kolumnie Filed podano nazwy pół. W kolumnie Type podano typy pół. Kolumna Null zawiera wartości YES albo NO. W kolumnie Key określono klucz, którym jest id_naucz. W kolumnie Default we wszystkie komórki wpisano NULL. W kolumnie Extra wpisano wartość tylko w ostatniej komórce, jest to auto_increment.

Kolejną czynnością jest dodanie pola klucza obcegoklucz obcyklucza obcego w tabeli przedmioty do przechowywania identyfikatorów nauczycieli.

ALTER TABLE przedmioty ADD COLUMN id_naucz INT(10) UNSIGNED;
ALTER TABLE przedmioty
ADD FOREIGN KEY (id_naucz) REFERENCES nauczyciele(id_naucz);

Na początku należy utworzyć pole, którego typ musi odpowiadać typowi danych klucza głównego. W drugiej kolejności tworzymy klucz obcy i tym samym definiujemy relację między tabelami.

Wprowadzamy identyfikatory nauczycieli do odpowiednich rekordów z tabeli przedmioty:

UPDATE przedmioty
SET id_naucz = (SELECT id_naucz FROM nauczyciele WHERE nazwisko = naucz_nazw AND imie = naucz_imie);

Klauzula aktualizacyjna UPDATE korzysta z podzapytania SELECT wykonywanego dla każdego rekordu z tabeli przedmioty, które wybiera identyfikator nauczyciela poprzez dopasowanie nazwiska i imienia. W tym przypadku w tabeli przedmioty nie ma dwóch nauczycieli, którzy nazywaliby się tak samo.

Na koniec możemy usunąć pola naucz_nazw i naucz_imie z tabeli przedmioty za pomocą klauzuli ALTER TABLE nazwa_tabeli DROP COLUMN nazwa_pola:

ALTER TABLE przedmioty DROP COLUMN naucz_nazw;
ALTER TABLE przedmioty DROP COLUMN naucz_imie;

R10KoHRkXS96Y

Zrzut ekranu przedstawiający tabelę przedmioty. Tabela składa się z sześciu kolumn opisanych kolejno: Field, Type, Null, Key, Default, Extra. Dane wpisano w trzech wierszach. W kolumnie Filed podano nazwy pół. W kolumnie Type podano typy pół. Kolumna Null zawiera wartości YES albo NO. W kolumnie Key określono klucze. W kolumnie Default we wszystkie komórki wpisano NULL. W kolumnie Extra wpisano wartość tylko w pierwszej komórce, jest to auto_increment.

Wszystkie omówione powyżej zapytania tworzące i modyfikujące tabelę nauczyciele możemy wykonać za jednym razem:

R1Am2JmVXpu2B

Ilustracja przedstawia zapytania tworzące i modyfikujące tabelę nauczyciele w aplikacji phpMyAdmin. Tu: Wykonanie zapytania/zapytań SQL do bazy danych szkola2. W czternastu rzędach zapis: CREATE TABLE nauczyciele AS SELECT naucz_nazw, naucz_imie FROM przedmioty ORDER BY naucz_nazw; ALTER TABLE nauczyciele CHANGE naucz_nazw nazwisko VARCHAR(30) ; ALTER TABLE nauczyciele CHANGE naucz_imie imie VARCHAR(30); ALTER TABLE nauczyciele ADD COLUMN id naucz INT(10) UNSIGNED PRIMARY KEY AUTO INCREMENT; ALTER TABLE przedmioty ADD COLUMN id naucz INT(10) UNSIGNED; ALTER TABLE przedmioty ADD FOREIGN KEY (id naucz) REFERENCES nauczyciele(id naucz) ; UPDATE przedmioty SET id naucz = (SELECT id naucz FROM nauczyciele WHERE nazwisko = naucz_nazw AND imie = naucz_imie); ALTER TABLE przedmioty DROP COLUMN naucz_nazw; ALTER TABLE przedmioty DROP COLUMN naucz_imie.

Tabela klasy

W tabeli przechowującej informacje o klasach zapiszemy nazwy klas i identyfikatory wychowawców. Kolejność czynności będzie podobna, jak podczas tworzenia poprzedniej tabeli. Zaczynamy od utworzenia tabeli z niepowtarzającymi się nazwami klas. Wykorzystujemy podzapytanie SELECT DISTINCT:

CREATE TABLE klasy AS SELECT DISTINCT klasa FROM uczniowie ORDER BY klasa;

Następnie sprawdzamy typy danych i zmieniamy nazwę pola:

DESCRIBE klasy;
ALTER TABLE klasy CHANGE klasa nazwa CHAR(2);

Klasom trzeba przypisać unikalne identyfikatory, czyli należy utworzyć klucz główny.

ALTER TABLE klasy
ADD COLUMN id_klasy INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT;

Kolejną czynnością jest utworzenie pól na dodatkowe dane. Przyjmijmy, że są to identyfikator wychowawcy i rocznik klasy. Odpowiednie instrukcje prezentują się następująco:

ALTER TABLE klasy
ADD COLUMN id_naucz INT(10) UNSIGNED,
ADD COLUMN rok CHAR(9);

Pozostaje utworzenie relacji między tabelami klasy i nauczyciele.

ALTER TABLE klasy
ADD FOREIGN KEY (id_naucz) REFERENCES nauczyciele(id_naucz);

R19FsdBNIRCTK

Zrzut ekranu przedstawiający tabelę klasy. Tabela składa się z sześciu kolumn opisanych kolejno: Field, Type, Null, Key, Default, Extra. Dane wpisano w czterech wierszach. W kolumnie Filed podano nazwy pół. W kolumnie Type podano typy pół. Kolumna Null zawiera wartości YES albo NO. W kolumnie Key określono klucze. W kolumnie Default we wszystkie komórki wpisano NULL. W kolumnie Extra wpisano wartość tylko w drugiej komórce, jest to auto_increment.

Skoro mamy tabelę klasy, możemy zaktualizować dane w tabeli uczniowie.

ALTER TABLE uczniowie
ADD COLUMN id_klasy INT(10) UNSIGNED;
ALTER TABLE uczniowie ADD FOREIGN KEY (id_klasy) REFERENCES klasy(id_klasy);
UPDATE uczniowie SET id_klasy = (SELECT id_klasy FROM klasy WHERE nazwa = klasa);
ALTER TABLE uczniowie DROP COLUMN klasa;

R1SvDOQdzOqzK

Zrzut ekranu przedstawiający tabelę uczniowie. Tabela składa się z sześciu kolumn opisanych kolejno: Field, Type, Null, Key, Default, Extra. Dane wpisano w sześciu wierszach. W kolumnie Filed podano nazwy pół. W kolumnie Type podano typy pół. Kolumna Null zawiera wartości YES albo NO. W kolumnie Key określono klucze. W kolumnie Default we wszystkie komórki wpisano NULL. W kolumnie Extra wpisano wartość tylko w pierwszej komórce, jest to auto_increment.

Wszystkie omówione zapytania tworzące i modyfikujące tabelę klasy:

RWyq2twLSCCBr

Ilustracja przedstawia zapytania tworzące i modyfikujące tabelę klasy i uczniowie w aplikacji phpMyAdmin. Tu zapis: Wykonanie zapytania/zapytań SQL do bazy danych szkola2. W piętnastu kolejnych rzędach zapis: CREATE TABLE klasy AS SELECT DISTINCT klasa FROM uczniowie ORDER BY klasa; ALTER TABLE klasy CHANGE klasa nazwa CHAR(2); ALTER TABLE klasy ADD COLUMN id klasy INT(10) UNSIGNED PRIMARY KEY AUTO INCREMENT; ALTER TABLE klasy ADD COLUMN id naucz INT(10) UNSIGNED, ADD COLUMN rok CHAR(9); ALTER TABLE klasy ADD FOREIGN KEY (id naucz) REFERENCES nauczyciele(id naucz); ALTER TABLE uczniowie ADD COLUMN id klasy INT(10) UNSIGNED; ALTER TABLE uczniowie ADD FOREIGN KEY (id klasy) REFERENCES klasy(id klasy); UPDATE uczniowie SET id klasy = (SELECT id klasy FROM klasy WHERE nazwa = klasa); ALTER TABLE uczniowie DROP COLUMN klasa.

Słownik