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:

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;

 

 



List do administratora Serwisu Edukacyjnego Nauczycieli I LO

Twój email: (jeśli chcesz otrzymać odpowiedź)
Temat:
Uwaga: ← tutaj wpisz wyraz  ilo , inaczej list zostanie zignorowany

Poniżej wpisz swoje uwagi lub pytania dotyczące tego rozdziału (max. 2048 znaków).

Liczba znaków do wykorzystania: 2048

 

W związku z dużą liczbą listów do naszego serwisu edukacyjnego nie będziemy udzielać odpowiedzi na prośby rozwiązywania zadań, pisania programów zaliczeniowych, przesyłania materiałów czy też tłumaczenia zagadnień szeroko opisywanych w podręcznikach.



   I Liceum Ogólnokształcące   
im. Kazimierza Brodzińskiego
w Tarnowie

©2017 mgr Jerzy Wałaszek

Dokument ten rozpowszechniany jest zgodnie z zasadami licencji
GNU Free Documentation License.