Informatyka dla klasy IIIK

Bazy danych

Praca z serwerem SQL

Rozpoczynamy naukę programowania baz danych w języku SQL (ang. Structured Query Language), który zawiera wszystkie niezbędne narzędzia do ich obsługi. Programy będziemy tworzyć interaktywnie na serwerze MYSQL. Wybrałem środowisko udostępniane przez Wampserver. Jest to darmowy program, który instaluje na komputerze użytkownika Serwer Apachi do obsługi stron WWW wraz z interpreterem języka PHP oraz serwer MYSQL do programowania relacyjnych baz danych.

Przed przystąpieniem do pracy sprawdź w zasobniku systemowym, czy ikona serwera Wamp jest zielona:

obrazek

Inny kolor (a w szczególności czerwony), świadczy, że któryś ze składników serwera nie mógł zostać uruchomiony z powodu konfliktu z innym programem, który w danej chwili działa na komputerze. Typowym przypadkiem jest Skype, którego należy wyłączyć w czasie pracy serwera Wamp.

Przed przystąpieniem do pracy z językiem SQL musisz się zalogować na serwer mysql. Pomijam tutaj proces konfiguracji tego serwera, który nauczyciel przeprowadził wcześniej w pracowni na każdym komputerze. Uczeń w każdej grupie posiada konto, które poda nauczyciel na lekcji. Aby przyspieszyć proces logowania się na serwer na każdym komputerze uczniowskim są zainstalowane skrypty startowe.

Aby pracować z bazą MySQL, należy się do niej zalogować. Naciśnij klawisze Windows+R i wpisz:

Grupa 3k1		Grupa 3k2
`3k1`		`3k2`

Jeśli wszystko wykonasz poprawnie, otrzymasz zgłoszenie serwera MySQL o następującej postaci:

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>

Co to jest relacyjna baza 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 zupełnie nam wystarczy (większość zadań maturalnych nie wykracza poza te typy podstawowe).

Tworzenie tabel w SQL

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

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;

Kończenie pracy z serwerem MySQL:

QUIT;