Algorytmy, struktury danych i techniki programowania

Podyplomowe Studium Programowania i Baz Danych
Wybrany system baz danych – MySQL
15 godz. wykładu / 30 godz. laboratorium
dr inż. Paweł Syty, 413GB, [email protected], 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
1
Zakres materiału
Podstawowe definicje i terminologia. Rodzaje systemów baz danych.
Podstawowe bazy danych. Instalacja bazy MySQL. Administracja bazą danych.
Projektowanie baz danych. Podstawowa praca z bazami. Operatory. Relacje
między tablicami. Transakcje i blokowanie. Backup baz. Dostęp do baz z
różnych języków programowania (C++, Java, PHP, Python). 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
2
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)
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
3
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
4
• 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.
5
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
6
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.
7
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.
8
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/
9
Logowanie do bazy danych
mysql -u login -h nazwa_hosta –p
Na przykład, gdy nie ma jeszcze innych użytkowników i na hoście lokalnym:
mysql -u root
(logowanie bez hasła)
Ustawienie 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:
SET PASSWORD FOR root = PASSWORD('mojetajnehaslo');
ALTER USER root IDENTIFIED BY 'mojetajnehaslo';
10
Plik konfiguracyjny
/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
mysqladmin variables – ustawienia systemu
mysqladmin -u new_user password new_password – nowy
użytkownik z ustawieniem hasła
mysqladmin -u username [-p] password new_password
– ustawienie hasła dla istniejącego użytkownika
11
Inne
mysql nazwa_bazy -u login –p
– logowanie z wyborem bazy
- 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)
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
12
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/
13
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.
14
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;
15
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;
16
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
17
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
18
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
19
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;
20
Opis struktury tabeli
DESCRIBE <nazwa-tabeli>;
• Przykład
DESCRIBE Users;
21
Usuwanie tabel i baz danych
• Składnia
DROP TABLE <nazwa-tabeli>;
DROP <nazwa-bazy-danych>;
• Przykład
DROP TABLE Users;
DROP Forum;
22
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');
23
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';
24
Łą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 autorid=1 AND thread=5;
25
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();
26
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;
27
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;
28
Modyfikacja danych
• Składnia
UPDATE <tabela>
SET <kolumna> = <wartość> [, <kol>=<wart>]
[ WHERE <warunki> ];
• Przykład
UPDATE Users
SET passwd = 'mojetajnehaslo'
WHERE login = 'root';
29
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!)
30