Informatyka dla klas III

Bazy danych

Baza danych (ang. data base) jest strukturą przechowującą różnego rodzaju dane. Język SQL (ang. Structured Query Language – Strukturalny Język Zapytań) jest językiem, który służy do obsługi bazy danych. W bazie danych informacje przechowywane są w tabelach. Tabele składają się z wierszy i kolumn. Wiersze będziemy nazywali rekordami, a kolumny polami. Przykładowa tabela wygląda następująco:

Nazwisko	Imię	Wiek
Musiał	Józef	46
Olgiewicz	Karol	58
Andrusiewicz	Magdalena	35

Tabela składa się z trzech wierszy i trzech kolumn. Poszczególne kolumny posiadają nazwy: Nazwisko, Imię 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:

Nazwisko	Imię	Wiek
Musiał	Józef	46
Olgiewicz	Karol	58
Andrusiewicz	Magdalena	35
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:

OsobaId	Nazwisko	Imię	Wiek
1	Musiał	Józef	46
2	Olgiewicz	Karol	58
3	Andrusiewicz	Magdalena	35
4	Kowalski	Jan	27
5	Kowalski	Jan	27

Teraz naszych Kowalskich już nie pomylimy, ponieważ jeden posiada OsobaId = 4, a drugi OsobaId = 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. 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:

WykonanieId	Nazwisko	Imię	Zabieg	Data
1	Kowalski	Jan	Rentgen klatki piersiowej	2015-01-18
2	Kowalski	Jan	Morfologia	2015-01-20
3	Zyznar	Karol	Rentgen szczęki	2015-02-02
4	Magdoń	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 rekordzie 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:

PacjentId	Nazwisko	Imię
1	Kowalski	Jan
2	Zyznar	Karol
3	Magdoń	Zofia
4	Hebda	Magdalena

ZabiegId	Zabieg
1	Rentgen klatki piersiowej
2	Morfologia
3	Rentgen szczęki
4	Mamografia

WykonanieId	PacjentId	ZabiegId	Data
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 PacjentId z pierwszej tabeli oraz ZabiegId 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). Dokładniej o relacjach pomówimy na dalszych lekcjach.

Typy danych w SQL

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

SMALLINT	–	liczba całkowita 16 bitowa ze znakiem, zakres od `-32768` do `32767.`
INTEGER lub INT	–	liczba całkowita 32 bitowa ze znakiem, zakres od `-2147483648` do `2147483647.`
BIGINT	–	liczba całkowita 64 bitowa ze znakiem, zakres od `-9223372036854775808` do `9223372036854775807.`
DOUBLE lub REAL	–	liczba zmiennoprzecinkowa podwójnej precyzji, zajmuje 8 bajtów

Typy daty i czasu

DATE	–	data, format rrrr-mm-dd, np. 2015-01-12
TIME	–	czas, format gg:mm:ss, np. 10:51:00
DATETIME	–	data i czas, format rrrr-mm-dd gg:mm:ss, np. 2015-01-12 10:51:00

Typy łańcuchowe

CHAR(n)	–	rezerwuje miejsce na n znaków. Jeśli przechowywany tekst ma mniejszą długość, to zostanie uzupełniony spacjami do na znaków.
VARCHAR(n)	–	rezerwuje miejsce do n znaków. Jeśli tekst ma mniejszą długość, to nie zostanie uzupełniony spacjami, lecz zapamiętany w takiej postaci, w jakiej występuje.

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

Praca z MySQL

Aby pracować z bazą MySQL, należy się do niej zalogować. Uruchom terminal (Ctrl+Alt+T). Od nauczyciela otrzymasz nazwę konta w systemie MySQL, hasło dostępu do tego konta i nazwę swojej bazy danych. Na przykład:

Grupa 3k1		Grupa 3k2
`konto: uczen_3k1 hasło: pass_3k1 baza: baza_3k1`		`konto: uczen_3k2 hasło: pass_3k2 baza: baza_3k2`

W terminalu wpisz polecenie:

Grupa 3k1		Grupa 3k2
`mysql -u uczen_3k1 -ppass_3k1 baza_3k1`		`mysql -u uczen_3k2 -ppass_3k2 baza_3k2`

Jeśli wszystko wykonasz poprawnie, otrzymasz zgłoszenie systemu MySQL:

Welcome to the MySQL monitor. Commands end with 
		; or \g.

		Your MySQL connection id is 46

		Server version: 5.5.40-0ubuntu1 (Ubuntu)

	

		Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights 
		reserved.

	

		Oracle is a registered trademark of Oracle Corporation and/or its

		affiliates. Other names may be trademarks of their respective owners.

	

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

	

		mysql>

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. Na początek utworzymy tabelę do przechowywania danych. 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 SQL proponuję pisać dużymi literami, aby odróżniały się od parametrów i danych.

W nazwach tabel i kolumn stosuj tylko małe litery łacińskie (bez polskich znaków) oraz cyfry. Atrybuty kolumn udostępniają różne opcje dla kolumny. Poznamy je za chwilę.

W wierszu poleceń wpisz:

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

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

CREATE TABLE osoba

		(

    nazwisko VARCHAR(20),

    imie VARCHAR(2),

    wiek INT

		);

Utworzona 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 
		('Musiał', 'Józef', 46);

		INSERT INTO osoba (nazwisko, imie, wiek) VALUES ('Olgiewicz', 'Karol', 
		58);

		INSERT INTO osoba (nazwisko, imie, wiek) VALUES ('Andrusiewicz', 
		'Magdalena', 35);

Zawartość tabeli wyświetlamy za pomocą polecenia SELECT. Aby zobaczyć całą tabelę, wpisz:

SELECT * FROM osoba;

W wyniku otrzymasz zawartość całej tabeli osoba:

+--------------+-----------+------+
| nazwisko     | imie      | wiek |
+--------------+-----------+------+
| Musiał       | Józef     |   46 |
| Olgiewicz    | Karol     |   58 |
| Andrusiewicz | Magdalena |   35 |
+--------------+-----------+------+
3 rows in set (0.00 sec)

Jeśli nie interesują nas wszystkie kolumny tabeli, to stosujemy następujące polecenie 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):

mysql> SELECT wiek, nazwisko FROM osoba;
+------+--------------+
| wiek | nazwisko     |
+------+--------------+
|   46 | Musiał       |
|   58 | Olgiewicz    |
|   35 | Andrusiewicz |
+------+--------------+
3 rows in set (0.00 sec)

Dodamy teraz do naszej tabeli dwóch Kowalskich:

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

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

Wyświetl zawartość tabeli:

mysql> SELECT * FROM osoba;
+--------------+-----------+------+
| nazwisko     | imie      | wiek |
+--------------+-----------+------+
| Musiał       | Józef     |   46 |
| Olgiewicz    | Karol     |   58 |
| Andrusiewicz | Magdalena |   35 |
| Kowalski     | Jan       |   27 |
| Kowalski     | Jan       |   27 |
+--------------+-----------+------+
5 rows in set (0.00 sec)

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

DROP TABLE osoba;

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

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

Następnie wprowadź do tabeli dane:

INSERT INTO osoba VALUES (1, 'Żak', 'Anna', 
		43);

		INSERT INTO osoba VALUES (2, 'Kowalski', 'Jan', 29);

		INSERT INTO osoba VALUES (3, 'Kowalski', 'Jan', 29);

Wyświetl zawartość tabeli:

mysql> SELECT * FROM osoba;
+----------+----------+------+------+
| osoba_id | nazwisko | imie | wiek |
+----------+----------+------+------+
|        1 | Żak      | Anna |   43 |
|        2 | Kowalski | Jan  |   29 |
|        3 | Kowalski | Jan  |   29 |
+----------+----------+------+------+
3 rows in set (0.00 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:

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

Powstanie błąd, ponieważ w tabeli już jest rekord o kluczu podstawowym 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 oboba_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 tabeli. Wpisz:

ALTER TABLE osoba MODIFY COLUMN osoba_id INT 
		AUTO_INCREMENT;

Ponieważ modyfikujemy kolumnę, za jej nazwą należy umieścić typ oraz atrybut. Teraz kolejne rekordy dodajemy następująco:

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

Wyświetl zawartość tabeli.

mysql> SELECT * FROM osoba;
+----------+----------+------------+------+
| osoba_id | nazwisko | imie       | wiek |
+----------+----------+------------+------+
|        1 | Żak      | Anna       |   43 |
|        2 | Kowalski | Jan        |   29 |
|        3 | Kowalski | Jan        |   29 |
|        4 | Magdoń   | Rozalia    |   45 |
|        5 | Kawa     | Aleksander |   33 |
+----------+----------+------------+------+
5 rows in set (0.00 sec)

Zwróć uwagę, że dwa ostatnie rekordy otrzymały swoje numery identyfikacyjne, chociaż w danych wejściowych podaliśmy w tym miejscu 0.

Usuń tabelę osoba.

DROP TABLE osoba;

Ćwiczenie

Wprowadź do bazy danych następującą tabelę (pole p_id ma być polem klucza podstawowego z automatycznym zwiększaniem zawartości):

pracownicy

p_id nazwisko imie pensja pracuje_od

1 Kruk Roman 1850 1998-10-01

2 Zakała Dorota 2100 2001-12-07

3 Jurczak Dagmara 2000 2005-05-22

4 Łątka Łukasz 2650 2009-03-19

5 Karaś Jarosław 3250 2011-11-06

Uwaga: daty wprowadzamy w apostrofach w postaci: rrrr-mm-dd, np: '1998-10-01'. Inaczej do bazy zostanie wstawiona data 0000-00-00.

Wyświetl następujące dane:

wszystko
nazwisko, imię i pensję pracownika
nazwisko i datę rozpoczęcia pracy
imiona pracowników

Podsumowanie poznanych poleceń SQL

Tworzenie 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,

		);

Wprowadzanie danych:

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

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

Pobieranie danych z tabeli:

SELECT * FROM tabela;

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

Usuwanie tabeli:

DROP TABLE tabela;

Zmiana atrybutu AUTO_INCREMENT dla kolumny:

ALTER TABLE tabela MODIFY COLUMN kolumna typ 
		AUTO_INCREMENT;