Matura - Internet

Uwaga: aby uniknąć kłopotów z polskimi literami w Windows (konsola i system stosują różne kodowanie), umówmy się, że nie będziemy ich stosować.

W bazie danych informacje przechowywane są w tabelach (ang. tables). Tabele składają się z wierszy i kolumn. Wiersze będziemy nazywali rekordami (ang. records), a kolumny polami (ang. fields) Przykładowa tabela może wyglądać następująco:

Abramowicz

Karol

24

Olgiewicz

Mariusz

36

Nitecka

Zofia

18

Tabela składa się z trzech wierszy i trzech kolumn. Poszczególne kolumny posiadają nazwy: Nazwisko, Imie oraz Wiek. Każdy rekord (wiersz) tej tabeli przechowuje trzy informacje w kolejnych kolumnach. Zwróć jednak uwagę, że taki sposób przechowywania informacji nie jest najlepszy. Wyobraźmy sobie, że w tabeli mamy dwie osoby o tym samym nazwisku, imieniu i wieku:

Abramowicz

Karol

24

Olgiewicz

Mariusz

36

Nitecka

Zofia

18

Kowalski

Jan

27

Kowalski

Jan

27

Oczywiście są to dwie różne osoby. Jak je rozróżnić? Rozwiązaniem jest dodanie do tabeli dodatkowej kolumny, zawierającej dane, które dla każdego rekordu są inne (unikalne). Mogą być to np. kolejne numery:

1

Abramowicz

Karol

24

2

Olgiewicz

Mariusz

36

3

Nitecka

Zofia

18

4

Kowalski

Jan

27

5

Kowalski

Jan

27

Teraz naszych Kowalskich już nie pomylimy, ponieważ jeden posiada Osoba_Id = 4, a drugi Osoba_Id = 5. Taką dodatkową kolumnę służącą do jednoznacznej identyfikacji rekordu tabeli nazywamy kluczem głównym lub podstawowym (ang. primary key).

Tabele w bazie danych są zwykle powiązane ze sobą różnymi zależnościami. Zależności te nazywamy relacjami (ang. relations). Wyobraź sobie, że pewna baza danych wiąże ze sobą pacjentów szpitala z zabiegami, które mają być u nich wykonane. Wszystko można by umieścić w jednej tabeli:

1

Kowalski

Jan

Rentgen klatki piersiowej

2015-01-18

2

Kowalski

Jan

Morfologia

2015-01-20

3

Zyznar

Karol

Rentgen stopy

2015-02-02

4

Magdon

Zofia

Mamografia

2015-02-11

5

Hebda

Magdalena

Mamgrafia

2015-03-03

Na pierwszy rzut oka wygląda dobrze. Ale... który Kowalski Jan ma rentgena i morfologię? Czy jest to ta sama osoba? A jeśli ktoś popełni pomyłkę przy wprowadzaniu nazwy zabiegu, jak w rekordach 4 i 5 w kolumnie Zabieg? Podstawową przesłanką tworzenia relacji w bazie danych są powtarzające się dane. W tym prostym przypadku takimi powtarzającymi się danymi są pacjenci oraz zabiegi. Aby rozwiązać ten problem, utworzymy trzy tabele: jedną dla pacjentów, drugą dla zabiegów, a trzecią, która łączy ze sobą pacjentów i zabiegi. Całość wygląda tak:

1

Kowalski

Jan

2

Zyznar

Karol

3

Magdon

Zofia

4

Hebda

Magdalena

1

Rentgen klatki piersiowej

2

Morfologia

3

Rentgen stopy

4

Mammografia

1

1

1

2015-01-18

2

1

2

2015-01-20

3

2

3

2015-02-02

4

3

4

2015-02-11

5

4

4

2015-03-03

W trzeciej tabeli umieszczamy odwołania do kolumn Pacjent_Id z pierwszej tabeli oraz Zabieg_Id z drugiej tabeli. Zwróć uwagę, że teraz jednoznacznie wskazaliśmy na pacjenta Kowalski Jan. Również nie ma możliwości pomylenia nazwy zabiegu, ponieważ w trzeciej tabeli wpisujemy tylko jego numer z tabeli drugiej. Utworzyliśmy tzw. relację wiele do wielu (wielu pacjentów może mieć ten sam zabieg oraz wiele zabiegów może dotyczyć tego samego pacjenta).

Do zapamiętania:

• Co to jest baza danych?
• Co to jest DBMS?
• Jaką funkcję pełni tabela w bazie danych?
• Co to jest rekord i pole?
• Co to jest relacja w bazie danych?
• Co to jest klucz główny/podstawowy?

Praca z bazą danych opiera się na architekturze klient-server. Serwer zarządza danymi przechowywanymi w bazie danych i wykonuje polecenia klienta wydawane w specjalnym języku SQL (ang. Structured Query Language - Język Strukturalnych Zapytań). Pakiet XAMPP zawiera serwer MySQL, który obsługuje polecenia języka SQL.

Po wykonaniu polecenia SQL serwer zwraca do klienta jego wyniki, a klient SQL może te wyniki przetworzyć wg potrzeb.

Nauka programowania baz danych opiera się na nauce języka SQL. Tutaj poznamy jedynie proste polecenia SQL, ponieważ zagadnienie to jest dosyć skomplikowane i wymaga osobnego kursu. Naukę możemy śmiało rozpocząć przy pomocy XAMPP.

Tworzenie konta użytkownika

Dostęp do serwera MySQL wymaga konta (ang. account). Dlatego musimy rozpocząć od utworzenia takiego konta.

Uruchom panel sterujący XAMPP i włącz serwery Apache oraz MySQL:

Teraz uruchomimy aplikację phpMyAdmin, która zarządza bazami danych w środowisku MySQL. Aplikacja ta pracuje w przeglądarce internetowej (została napisana w języku php), dlatego wymagany jest serwer Apache do jej uruchomienia. W drugim wierszu przycisków (MySQL) kliknij w przycisk Admin. Jeśli wszystko działa poprawnie, to w przeglądarce zostanie otwarta strona startowa aplikacji phpMyAdmin:

Przejdź do zakładki Konta użytkowników:

W sekcji New kliknij opcję Add user account (dodaj konto użytkownika). Następnie wpisz nazwę użytkownika, nazwę hosta (ang. Host name) z którego będzie wykonywane połączenie (tutaj wybierz Host lokalny), hasło do konta (należy wpisać dwukrotnie):

W polu Database for user account (baza danych dla konta użytkownika) zaznacz pierwszą opcję Create database with same name and grant all privileges (utwórz bazę danych o takiej samej nazwie  i nadaj wszystkie przywileje).

Spowoduje to, iż wraz z kontem zostanie utworzona pusta baza danych o tej samej nazwie i otrzyma ona standardowe przywileje. Reszty opcji nie zmieniaj, kliknij w przycisk Go. Powinien się pojawić komunikat o poprawnym wykonaniu polecenia SQL i na liście użytkowników powinien być nowy użytkownik:

Terminal MySQL

Jesteśmy gotowi do rozpoczęcia nauki języka SQL. W języku SQL można programować bezpośrednio w środowisku phpMyAdmin, jednakże aplikacja ta posiada bardzo dużo opcji, przez co staje się nieco trudna dla początkujących. Dlatego do nauki programowania wybierzemy dużo prostszą aplikację o nazwie mysql.exe, która pracuje w oknie konsoli.

W panelu sterowania XAMPP kliknij przycisk Shell (Serwer MySQL musi pracować!, ponieważ to z nim będziemy się łączyć). Polecenie uruchamiające terminal MySQL jest następujące:

mysql\bin\mysql -u użytkownik -phasło baza

Jeśli twoje konto to: lekcje, hasło to: mumba#15, baza danych to: lekcje, wpisz:

mysql\bin\mysql -u lekcje -pmumba#15 lekcje

Tutaj baza danych posiada taką samą nazwę jak konto użytkownika. Jeśli nastąpi połączenie z serwerem, to pojawi się komunikat:

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 260
Server version: 10.4.32-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [lekcje]>

Jeśli nie chcesz podawać hasła jawnie, to po prostu pomiń je w poleceniu za parametrem -p (ang. password = hasło):

mysql\bin\mysql -u lekcje -p lekcje
Enter password: *********

Wtedy zostanie wyświetlona prośba o wprowadzenie hasła (ang. Enter password:) i przy jego wprowadzaniu będą pojawiały się gwiazdki zamiast liter. Jeśli nie utworzyłeś hasła dla swojego konta, to pomiń je w poleceniu i przy wprowadzaniu hasła wciśnij Enter.

System jest gotowy na przyjmowanie rozkazów języka SQL, ale o tym w następnym rozdziale. Wyjście z terminala MySQL następuje po wpisaniu polecenia exit. Wtedy nastąpi powrót do okna konsoli, które również możemy zamknąć takim samym poleceniem exit.

Do zapamiętania:

• Uruchamianie phpMyAdmin.
• Tworzenie konta użytkownika z bazą danych o tej samej nazwie co konto.
• Uruchamianie terminala mySQL dla danego użytkownika i dla określonej bazy danych
• Ukrywanie hasła przy logowaniu się do serwera MySQL.
• Kończenie pracy w terminalu MySQL.

Uruchom panel sterujący XAMPP i włącz serwer MySQL:

Kliknij w przycisk Shell i wpisz polecenie uruchamiające aplikację mysql. U mnie wygląda to tak:

mysql\bin\mysql -u geo -p geo
Enter password: *********

Użytkownik geo (czyli ja, -u = user, użytkownik) i jego pusta baza danych o takiej samej nazwie geo. Ponieważ nie podałem hasła w poleceniu za parametrem -p (-p = password, hasło) to zostałem o nie poproszony (Enter password:) i wprowadzane hasło zostało ukryte za znakami gwiazdki. Dobrym zwyczajem jest niepodawanie swoich haseł do wiadomości publicznej.

Jeśli logowanie przebiegło poprawnie, to terminal MySQL uruchomi się i w oknie konsoli zobaczysz tekst:

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 27
Server version: 10.4.32-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [geo]>

W tłumaczeniu brzmi to następująco:

Witaj w monitorze MariaDB. Rozkazy kończą się znakiem ; lub \g.
Numer identyfikacyjny twojego połączenia z MariaDB to 27
Wersja serwera: 10.4.32-MariaDB dystrybucja binarna z mariadb.org

Copyright (c) 2000, 2018, Oracle, Korporacja MariaDB, Ab i inni.

Wpisz 'help;' lub '\h', aby otrzymać pomoc. Wpisz '\c', aby wymazać bieżąco wprowadzone polecenie

MariaDB [geo]>

Bazę danych rozpoczynamy od utworzenia w niej tabeli, w której będzie można przechowywać informację. Praca w systemie MySQL polega na wpisywaniu tzw. zapytań do bazy danych. W odpowiedzi baza danych wykonuje odpowiednie polecenie i zwraca wyniki. Każde zapytanie kończymy w terminalu znakiem średnika lub \g (od angielskiego go = wykonaj). Przy tworzeniu tabel stosujemy następujące zapytanie SQL:

CREATE TABLE nazwa_tabeli
(
    nazwa_kolumny_1 typ_kolumny_1 atrybuty,
    nazwa_kolumny_2 typ_kolumny_2 atrybuty,
    ...
    nazwa_kolumny_n typ_kolumny_n atrybuty,
);

Słowa kluczowe języka SQL będziemy pisać dużymi literami, aby odróżniały się od parametrów i danych. MySQL nie rozróżnia dużych i małych liter. W nazwach tabel i kolumn stosuj tylko małe litery łacińskie (bez polskich znaków) oraz cyfry.

Aby utworzyć tabele, musisz znać podstawowe typy danych, które obowiązują w języku SQL. Typy te będą definiować, co można przechowywać w kolumnach tabeli. Dzielą się ona na trzy kategorie:

Typy liczbowe

Typy daty i czasu

Typy łańcuchowe

W MySQL występuje więcej typów danych, lecz na początek tyle nam wystarczy.

Atrybuty kolumn udostępniają różne opcje dla kolumny. Poznamy je za chwilę.

Uwaga: ważne jest, abyś sam wpisywał polecenia ręcznie, bez kopiowania ich poprzez schowek. W ten sposób przyswoisz sobie ich składnię i nabierzesz wprawy w posługiwaniu się językiem SQL!

W wierszu poleceń wpisz:

CREATE TABLE osoba (nazwisko VARCHAR(20), imie VARCHAR(20), wiek INT);

Jeśli nie popełniłeś błędu, to system MySQL da odpowiedź:

Query OK, 0 rows affected (0.196 sec)

Wpisywanie dłuższych poleceń możesz rozbić na kilka wierszy, lecz ostatni wiersz musi być zakończony średnikiem lub /g.

CREATE TABLE osoba
(
    nazwisko VARCHAR(20),
    imie VARCHAR(2),
    wiek INT
);

Utworzyliśmy tabelę o nazwie osoba zawierającą trzy kolumny: nazwisko (tekst o długości do 20 znaków), imie (tekst o długości do 20 znaków) i wiek (liczba całkowita). Tabela jest pusta. Musimy zatem wprowadzić do niej dane. Do wprowadzania danych służy polecenie INSERT o ogólnej postaci:

INSERT INTO tabela
(kolumna1, kolumna2, ..., kolumnan)
VALUES (wartość_1, wartość_2, ..., wartość_n);

Jeśli dane będą podawane w kolejności zdefiniowanych w tabeli kolumn, to polecenie przyjmuje prostszą postać:

INSERT INTO tabela
VALUES (wartość_1, wartość_2, ..., wartość_n);

Wpisz w wierszu poleceń (nie musisz za każdym razem wpisywać wszystkiego od początku – naciśnij klawisz strzałka w górę, aby przywołać ostatnio wydane polecenie i zmień w nim dane, po czym wciśnij klawisz Enter):

INSERT INTO osoba (nazwisko, imie, wiek) VALUES ('Muszkat', 'Jan', 46);
INSERT INTO osoba VALUES ('Olgiewicz', 'Karol', 58);
INSERT INTO osoba VALUES ('Andrusiewicz', 'Magdalena', 35);

Jeśli nie popełniłeś błędu, to po każdym wpisanym poleceniu otrzymasz komunikat postaci:

Query OK, 1 row affected (0.023 sec)

Wprowadziliśmy trzy rekordy informacji do tabeli osoba. Zawartość kolumn tabeli wyświetlamy za pomocą polecenia SELECT. Aby zobaczyć wszystkie kolumny, wpisz:

W wyniku otrzymasz:

+--------------+-----------+------+
| nazwisko     | imie      | wiek |
+--------------+-----------+------+
| Muszkat      | Jan       |   46 |
| Olgiewicz    | Karol     |   58 |
| Andrusiewicz | Magdalena |   35 |
+--------------+-----------+------+
3 rows in set (0.000 sec)

Jeśli nie interesują nas wszystkie kolumny tabeli, to stosujemy następującą postać polecenia SELECT:

SELECT kolumna_1, kolumna_2, ..., kolumna_n
FROM tabela;

Dla przykładu wyświetlmy wiek oraz nazwisko (w takiej właśnie kolejności):

SELECT wiek, nazwisko FROM osoba;
+------+--------------+
| wiek | nazwisko     |
+------+--------------+
|   46 | Muszkat      |
|   58 | Olgiewicz    |
|   35 | Andrusiewicz |
+------+--------------+
3 rows in set (0.000 sec)

Dodamy teraz do naszej tabeli dwóch Kowalskich Janów o tym samym wieku 27 lat:

INSERT INTO osoba VALUES ('Kowalski','Jan',27);
INSERT INTO osoba VALUES ('Kowalski','Jan',27);

Wyświetl zawartość tabeli:

SELECT * FROM osoba;
+--------------+-----------+------+
| nazwisko     | imie      | wiek |
+--------------+-----------+------+
| Muszkat      | Jan       |   46 |
| Olgiewicz    | Karol     |   58 |
| Andrusiewicz | Magdalena |   35 |
| Kowalski     | Jan       |   27 |
| Kowalski     | Jan       |   27 |
+--------------+-----------+------+
5 rows in set (0.000 sec)

Dwa ostatnie rekordy są identyczne i nie będzie je można rozróżniać. Usuń tabelę osoba za pomocą polecenia:

Tabelę utworzymy ponownie, dodając kolumnę klucza podstawowego. Kolumna ta będzie zawierała liczby. Wpisz:

CREATE TABLE osoba
(
    osoba_id INT PRIMARY KEY,
    nazwisko VARCHAR(20),
    imie VARCHAR(20),
    wiek INT
)\g

Następnie wprowadź do tabeli dane:

INSERT INTO osoba VALUES (1, 'Zyznar', 'Anna', 43);
INSERT INTO osoba VALUES (2, 'Kowalski', 'Jan', 29);
INSERT INTO osoba VALUES (3, 'Kowalski', 'Jan', 29);

Wyświetl zawartość tabeli:

SELECT * FROM osoba;
+----------+----------+------+------+
| osoba_id | nazwisko | imie | wiek |
+----------+----------+------+------+
|        1 | Zyznar   | Anna |   43 |
|        2 | Kowalski | Jan  |   29 |
|        3 | Kowalski | Jan  |   29 |
+----------+----------+------+------+
3 rows in set (0.000 sec)

Teraz każdy z Kowalskich posiada swój numer identyfikacyjny, zatem go nie pomylimy. Ale co się stanie, jeśli dopiszemy do tabeli osobę o kluczu, który już jest w tabeli? Wpisz:

INSERT INTO osoba VALUES (3, 'Magdon', 'Rozalia', 45);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

Powstanie błąd, ponieważ w tabeli już jest rekord o kluczu podstawowym równym 3. Klucze muszą być unikalne, czyli nie mogą się powtarzać (inaczej po co je stosować?).

Wprowadzanie kolejnych numerów można zautomatyzować, jeśli dodamy do kolumny osoba_id atrybut AUTO_INCREMENT (automatyczne zwiększanie o 1). Powoduje on, że każdy nowy rekord otrzyma numer identyfikacyjny o 1 większy od największego numeru w tej kolumnie. Wpisz:

ALTER TABLE osoba
MODIFY COLUMN osoba_id INT AUTO_INCREMENT;

MySQL zwróci komunikat:

ALTER TABLE osoba MODIFY COLUMN osoba_id INT AUTO_INCREMENT;
Query OK, 3 rows affected (0.680 sec)
Records: 3 Duplicates: 0 Warnings: 0

Polecenie ALTER (zmień) służy do wprowadzania zmian w bazie danych. W tym przypadku zmieniamy tabelę osoba (ALTER TABLE osoba...), a w niej parametry kolumny osoba_id (...MODIFY COLUMN osoba_id...). Ponieważ modyfikujemy kolumnę, za jej nazwą należy umieścić typ oraz atrybut (... osoba_id INT AUTO_INCREMENT;) Teraz kolejne rekordy dodajemy następująco:

INSERT INTO osoba VALUES (0, 'Magdon', 'Rozalia', 45);
INSERT INTO osoba VALUES (0, 'Kawa', 'Aleksander', 33);

Wyświetl zawartość tabeli.

SELECT * FROM osoba;
+----------+----------+------------+------+
| osoba_id | nazwisko | imie       | wiek |
+----------+----------+------------+------+
|        1 | Zyznar   | Anna       |   43 |
|        2 | Kowalski | Jan        |   29 |
|        3 | Kowalski | Jan        |   29 |
|        4 | Magdon   | Rozalia    |   45 |
|        5 | Kawa     | Aleksander |   33 |
+----------+----------+------------+------+
5 rows in set (0.001 sec)

Zwróć uwagę, że dwa ostatnie rekordy otrzymały swoje numery identyfikacyjne, chociaż w danych wejściowych podaliśmy w tym miejscu 0. To jest efekt działania atrybutu AUTO_INCREMENT - automatyczne zwiększanie.

Usuń tabelę osoba.

Ćwiczenie

Do zapamiętania:

Tworzenie nowej tabeli:

CREATE TABLE nazwa_tabeli
(
    nazwa_kolumny_1 typ_kolumny_1 atrybuty,
    nazwa_kolumny_2 typ_kolumny_2 atrybuty,
    ...
    nazwa_kolumny_n typ_kolumny_n atrybuty,
);

Typy danych:

SMALINT
INT, INTEGER
BIGINT
DOUBLE, REAL
TIME, DATE, DATETIME
CHAR(n), VARCHAR(n)

Wprowadzanie danych do tabeli:

INSERT INTO nazwa_tabeli
(
    nazwa_kolumny_1,
    nazwa_kolumna_2,
    ...,
    nazwa_kolumny_n
)
VALUES
(
    wartość_1,
    wartość_2,
    ...,
    wartość_n
);

INSERT INTO nazwa_tabeli
VALUES
(
    wartość_1,
    wartość_2,
    ...,
    wartość_n
);

Pobieranie danych z tabeli:

SELECT * FROM tabela;

SELECT
    nazwa_kolumny_1,
    nazwa_kolumna_2,
    ...,
    nazwa_kolumny_n
FROM nazwa_tabeli;

Usuwanie tabeli:

DROP TABLE nazwa_tabeli;

Modyfikacja tabeli:

ALTER TABLE nazwa_tabeli
MODIFY COLUMN nazwa_kolumny typ AUTO_INCREMENT;

Atrybuty:

PRIMARY KEY
AUTO_INCREMENT