1

Podyplomowe Studium Programowania i Baz Danych

Wybrany system baz danych – MySQL

15 godz. wykładu / 30 godz. laboratorium

dr inż. Paweł Syty, 413GB, pawel.syty@pg.edu.pl, http://sylas.info

Literatura

P. DuBois, MySQL. Vademecum profesjonalisty. Wydanie V. Helion, 2014

M. Stucky, MySQL. Budowanie interfejsów użytkownika. Vademecum

profesjonalisty. Helion, 2003

R.J. Yarger, G. Reese, T. King, MySQL i mSQL. O'REILLY, 2001

M. Zandstra, PHP. Obiekty, wzorce, narzędzia. Wydanie IV. Helion, 2014

Materiały dydaktyczne

http://students.sylas.info

2

Zakres materiału

Podstawowe definicje i terminologia. Rodzaje systemów baz danych.

Podstawowe systemy bazy danych. Instalacja bazy MySQL. Administracja bazą

danych. Projektowanie baz danych. Podstawowa praca z bazami. Operatory.

Relacje między tablicami. Transakcje i blokowanie. Backup bazy. Dostęp do

bazy z różnych języków programowania (C++, Java, PHP). Specyficzne znaki

narodowe.

Język PHP, podstawy języka, połączenie z bazą danych MySQL (MariaDB) i

SQLite, prosta strona WWW przechowująca informacje w bazie danych,

wyszukiwarka informacji.

Java + Android + SQLite, prosta aplikacja (o ile wystarczy czasu).

3

Bazy danych

Uporządkowany zbiór danych, pozwalający na efektywne przechowywanie,

wyszukiwanie, sortowanie oraz pozyskiwanie informacji.

Relacyjna baza danych

Baza danych oparta na relacjach.

Relacja (tablica, tabela)

Podzbiór iloczynu kartezjańskiego dwóch wybranych zbiorów, reprezentujących

dopuszczalne wartości. W bazach danych relacja przedstawiana jest w postaci tabeli. Relacja jest

zbiorem krotek posiadających taką samą strukturę, lecz różne wartości.

Krotka

Każda krotka odpowiada jednemu wierszowi tablicy i posiada co najmniej jeden

atrybut odpowiadający pojedynczej kolumnie tablicy.

4

Właściwości tabeli

Tabela (relacja) jest tabelarycznym zbiorem danych, podobnie jak w arkuszu

kalkulacyjnym.

Przykład: tabela Customers

• wiersze (krotki, rekordy, obserwacje) są unikalne

• kolejność wierszy nie ma znaczenia, są rozróżnialne przez tzw. klucz główny

• każdy wiersz zawiera cały zestaw indywidualnych wartości, które odpowiadają

poszczególnym kolumnom tabeli

• kolumny (atrybuty, pola, zmienne) są również unikalne

5

• każdej kolumnie przyporządkowana jest niepowtarzalna nazwa oraz pewien

fragment danych

• kolejność kolumn nie ma znaczenia

• kolumny maja atomowe wartości (nie są dalej dzielone)

• typ danych określonej wartości musi być zgodny z typem danych kolumny, w

której znajduje się ta wartość

Klucze

Musimy dysponować sposobem, który umożliwiłby jednoznaczna identyfikacje

każdego rekordu. W przykładzie, dane osobowe niezbyt dobrze się do tego celu

nadają, ponieważ wielokrotnie spotykamy osoby o tych samych nazwiskach i

imionach.

W naszym przypadku problem ten został rozwiązany dzięki przypisaniu każdemu

klientowi niepowtarzalnego identyfikatora CustomerID.

6

Takie numery jednoznacznie identyfikujące każdy wiersz tabeli, upraszczają

proces zarówno przechowywania, jak i wyszukiwania danych w bazie.

Kolumna, która zawiera dane jednoznacznie identyfikujące wiersze tabeli,

określana jest mianem klucza lub klucza głównego (primary key).

Klucz może się składać z kilku kolumn.

Zazwyczaj bazy danych składają się z kilku tabel, które powiązane są ze sobą za

pomocą kluczy. Układ tabel to tzw. schemat (baza danych).

Kolejna tabela (Orders):

CustomerID – klucz obcy (foreign key) w tabeli Orders

7

MySQL

(wymowa: maj-es-kju-el)

https://www.mysql.com/

Jeden z najpopularniejszych systemów bazodanowych.

Bardziej formalnie: szybki i solidny systemem zarzadzania relacyjnymi bazami

danych (Relational Database Management System – RDBMS).

MySQL został oficjalnie opublikowany w roku 1996. Stworzony został przez

szwedzką firmę MySQL AB, później sprzedany firmie Sun, która z kolei została

przejęta przez Oracle.

Jest dostępny na licencji GPL (dostępne źródła systemu), lecz jeśli jest to

konieczne, można zakupić jego wersje komercyjną.

Serwer MySQL steruje dostępem do danych, udostępnia je równocześnie dla

wielu użytkowników oraz zapewnia możliwość jak najszybszego z nich

korzystania.

8

Serwer steruje uwierzytelnianiem użytkowników, które pozwala operować na

danych jedynie osobom upoważnionym.

Korzysta ze standardu – języka SQL, dzięki któremu możliwe jest zadawanie

zapytań do bazy danych. SQL jest najczęściej używanym, ustandaryzowanym

językiem dostępu do bazy danych.

Doskonale nadaje się do współpracy z wieloma popularnymi językami

programowania.

Często wykorzystuje się ją do budowania dynamicznych stron internetowych w

połączeniu z językiem np. PHP.

9

Instalacja

https://dev.mysql.com/downloads/mysql/

MySQL jest dostępny dla:

Windows

Linux

Mac OS X

FreeBSD

Dostępny jest również kod źródłowy.

Uwaga! Ze względu na przejęcie rozwoju MySQL-a przez Oracle i niepewną

przyszłość jego licencji, powstała alternatywa na wolnej licencji, całkowicie

zgodna z MySQL: MariaDB.

https://mariadb.org/

10

Logowanie do bazy danych

shell> mysql -u login -h nazwa_hosta –p

Na przykład, gdy nie ma jeszcze innych użytkowników i na hoście lokalnym:

shell> mysql -u root (logowanie bez hasła)

Po zalogowaniu przechodzimy to tzw. trybu interaktywnego MySQL, w którym

możemy wydawać polecenia SQL.

UWAGA! Wyrażenie „shell> ” oznacza umowny znak zachęty systemu

operacyjnego działającego w trybie linii komend – w Windows w pracowniach

komputerowych może to być np. „C:\users\student” lub „# ”.

Jeżeli przed daną komendą nie ma „shell>”, to oznacza że należy ją

wykonywać w trybie interaktywnym MySQL (znak zachęty będzie wyglądał

„mysql> ” lub „mariadb> ”).

exit – opuszczenie trybu interaktywnego i powrót do linii komend systemu

11

Przykładowe, podstawowe operacje w trybie interaktywnym:

Utworzenie nowego użytkownika:

CREATE USER 'user'@'host' IDENTIFIED BY 'password';

Zmiana hasła użytkownika (można też inaczej – patrz następny slajd):

SET PASSWORD FOR user = PASSWORD('new_password');

lub (drugi wariant od wersji MySQL 5.7.6)

ALTER USER user IDENTIFIED BY 'new_password';

Przykłady:

CREATE USER 'ktos'@'%' IDENTIFIED BY 'haslo';

SET PASSWORD FOR root = PASSWORD('mojetajnehaslo');

12

Plik konfiguracyjny MySQL

/etc/mysql/my.cnf (Linux)

mysql/bin/my.ini (Windows)

Fizyczne położenie plików z danymi

/var/lib/mysql (Linux)

mysql/data (Windows)

Program zarządzający – mysqladmin

shell> mysqladmin variables – wypisanie ustawień systemu

shell> mysqladmin -u user [-p] password new_password

– ustawienie hasła new_password dla istniejącego użytkownika user.

Więcej poleceń administracyjnych – kolejne wykłady

13

Kolejne przykłady

shell> mysql nazwa_bazy -u login –p

– logowanie do trybu interaktywnego wraz z wyborem bazy

W trybie interaktywnym:

SELECT VERSION(); – wersja MySQL SELECT USER(); – bieżący użytkownik

SELECT CURRENT_TIME(); – bieżący czas

SELECT CURRENT_DATE(); – bieżąca data

EXIT – opuszczenie systemu

Polecenia MySQL nie są czułe na wielkość liter, ale nazwy i dane tekstowe

oczywiście tak (w zasadzie nazwy są uzależnione od systemu plików w systemie

operacyjnym)

14

Przydatne narzędzia

MySQL Workbench

Graficzne środowisko do projektowania

https://www.mysql.com/products/workbench/

phpMyAdmin

Środowisko do zarządzania bazami MySQL z poziomu przeglądarki WWW,

przez sieć

https://www.phpmyadmin.net/

15

XAMPP

Pakiet serwera WWW, zawierający Apache (serwer WWW), MariaDB

(wcześniejsze wersje zawierały MySQL), PHP (język programowania) i kilka

innych narzędzi

https://www.apachefriends.org/pl/index.html

Program XAMPP jest przykładem zestawu oprogramowania typu LAMP:

Linux (choć niekoniecznie)

Apache

MySQL (MariaDB)

PHP (Perl, Python)

LAMP jest łatwym w konfiguracji zestawem startowym do tworzenia

dynamicznych stron WWW.

16

Podstawy języka SQL

Lista istniejących baz danych

• Składnia

SHOW DATABASES;

Przełączenie się na daną bazę danych

• Składnia

USE <nazwa-bazy-danych>;

• Przykład

USE MOJA_BAZA;

17

Tworzenie nowej bazy danych

• Składnia

CREATE <nazwa-bazy-danych>

DEFAULT CHARACTER SET <zestaw-znaków>

DEFAULT COLLATE <sortowanie>;

• Przykład

CREATE Forum

DEFAULT CHARACTER SET utf8

DEFAULT COLLATE utf8_polish_ci;

18

Najważniejsze typy danych

• INT – liczba całkowita, odpowiednik long w C++

• BIGINT – liczba całkowita duża, często jest kluczem głównym

• FLOAT – liczba zmiennoprzecinkowa

• DECIMAL(n,p) – liczba zmiennoprzecinkowa o długości n i zawierająca p

cyfr po przecinku

• DATE – data

• TIME – czas

• TIMESTAMP – data i czas

• VARCHAR(n) – ciąg znaków o zmiennej długości, max n znaków

• BLOB – dane binarne

19

Atrybuty danych

• NOT NULL – kolumna nie może być pusta, wymagane jest podanie wartości

(za wyjątkiem kolumny, dla której podano DEFAULT lub AUTO_INCREMENT)

• DEFAULT wartość – domyślna wartość

• AUTO_INCREMENT – automatyczna inkrementacja (zwykle dla kluczy

głównych)

• PRIMARY KEY – klucz główny

• UNIQUE – nie są dopuszczalne duplikaty wartości

20

Dyrektywy

• ENGINE = <nazwa-silnika>

Najczęściej wykorzystuje się silniki:

- InnoDB – zaawansowane możliwości blokowania/transakcji; do niedawna nie

pozwalał jednak na przeszukiwanie pełnotekstowe; jest domyślny silnik w

najnowszych wersjach MySQL

- MyISAM – szybszy, ale nie obsługuje transakcji, do pewnego momentu był to

domyślny silnik w MySQL. W przyszłych wersjach MySQL zostanie

zmarginalizowany.

21

Tworzenie tabel

• Składnia

CREATE TABLE <nazwa-tabeli> (

<nazwa-kolumny> <typ> [atrybuty] [,…]

) [atrybuty dyrektywy];

• Przykład

CREATE TABLE Users (

userid BIGINT AUTO_INCREMENT PRIMARY KEY,

login VARCHAR(20) UNIQUE NOT NULL

) ENGINE = InnoDB;

22

Opis struktury tabeli

DESCRIBE <nazwa-tabeli>;

• Przykład

DESCRIBE Users;

23

Usuwanie tabel i baz danych

• Składnia

DROP TABLE <nazwa-tabeli>;

DROP <nazwa-bazy-danych>;

• Przykład

DROP TABLE Users;

DROP Forum;

24

Wprowadzanie danych

• Składnia

INSERT INTO <nazwa-tabeli>

VALUES (wartość, wartość, …);

INSERT INTO <tabela> (kolumna, kolumna, …)

VALUES (wartość, wartość, …);

• Przykład

INSERT INTO Users (login, passwd)

VALUES ('root', 'jakieshaslo');

25

Pobieranie danych – jedna tabela

• Składnia

SELECT <co?> FROM <tabela> [ WHERE <warunki> ];

Do określania warunków można użyć:

- operatorów relacji (=, <>, >, >=, <, <=)

- operatora IS NULL

- operatora LIKE (w rodzaju: LIKE '<znaki>%')

• Przykład

SELECT * FROM Users WHERE login LIKE 'r%';

SELECT passwd FROM Users WHERE login='root';

26

Łączenie wyrażeń warunkowych

• Składnia

SELECT <co?> FROM <tabela> [ WHERE <warunki> ];

Wyrażenia warunkowe można łączyć używając operatorów

- AND – koniunkcja

- OR – alternatywa

• Przykład

SELECT * FROM Posts

WHERE author='Ktostam' AND thread=5;

27

Określanie zbioru oraz zakresu wartości w warunkach

• Składnia

SELECT <co?> FROM <tabela> [ WHERE <warunki> ];

W wyrażeniach warunkowych można stosować operatory:

- IN <zbiór-wartości>

- BETWEEN <wartość> AND <wartość>

• Przykład

SELECT * FROM Users WHERE userid IN (1, 3, 5);

SELECT * FROM Posts

WHERE date BETWEEN '2009-01-01' AND NOW();

28

Pobieranie danych – więcej tabel

• Składnia

SELECT <co?> FROM <tabela> [, <tabela>, …]

[ WHERE <warunki> ];

Domyślnie tworzony jest iloczyn kartezjański tabel, dlatego warunki trzeba

podać

• Przykład

SELECT login, date, msg FROM Users, Posts

WHERE Users.userid = Posts.userid;

29

Pobieranie danych – użycie dyrektyw

• Składnia

SELECT <co?> FROM <tabela> [ WHERE <warunki> ]

[ dyrektywy ];

- ORDER BY <kolumna> [ASC | DESC] [, <kolumna>, …]

- LIMIT n, m – wiersze od n do m (numeracja od zera!)

• Przykład

SELECT login, date, msg FROM Users, Posts

WHERE Users.userid = Posts.userid

ORDER BY date DESC, login ASC

LIMIT 0, 20;

30

Modyfikacja danych

• Składnia

UPDATE <tabela>

SET <kolumna> = <wartość> [, <kol>=<wart>]

[ WHERE <warunki> ];

• Przykład

UPDATE Users

SET passwd = 'mojetajnehaslo'

WHERE login = 'root';

31

Usuwanie danych

• Składnia

DELETE FROM <tabela> [ WHERE <warunki> ];

Do określania warunków można użyć:

- operatorów relacji (=, <>, >, >=, <, <=)

- operatora LIKE (w rodzaju: LIKE '<znaki>%')

• Przykład

DELETE FROM Users WHERE login = 'ktostam';

DELETE FROM Posts WHERE msg LIKE '%costam%';

DELETE FROM Posts; (Usuwa wszystkie rekordy!)

32

Inne

Logowanie komend do pliku:

TEE nazwapliku;

Wykonanie komend z pliku:

SOURCE nazwapliku;

(albo shell> mysql < nazwapliku)