Internetowa baza danych MySQL

Zarządzanie systemami
informatycznymi
Zarządzanie internetową
bazą danych MySQL
Bazy danych
Koncepcja bazy danych
 Relacyjna baza danych
 Projektowanie internetowej bazy danych

Koncepcja bazy danych
Bazą danych nazywamy zbiór danych w postaci tabel oraz narzędzi
stosowanych do gromadzenia, przekształcania oraz wyszukiwania danych.
Tabelą w bazie danych nazywamy zbiór rekordów opisujących obiekty np.
pracownicy zawierających informacje o tych obiektach w sposób
ujednolicony.
Rekord zwany także krotką lub wierszem, to pozioma struktura danych
opisująca jeden obiekt. Rekord składa się z pól opisujących dokładnie
cechy obiektu np. pojedynczego pracownika.
Pole zwane także atrybutem lub kolumną, to struktura danych opisująca
pojedynczą daną w rekordzie np. nazwisko pracownika.
Klucz podstawowy (ang. primary key) zwany też kluczem głównym to jedno
lub więcej pól, których wartość jednoznacznie identyfikuje każdy rekord w
tabeli. Taka cecha klucza nazywana jest unikatowością. Klucz podstawowy
służy do powiązania rekordów w jednej tabeli z rekordami z innej tabeli.
Klucz podstawowy jest nazywany kluczem obcym, jeśli odwołuje się do innej
tabeli. Na przykład, w bazie pracowników kluczem podstawowym może być
numer ewidencyjny pracownika.
Relacyjna baza danych
Relacja
Po podzieleniu danych na tabele i zdefiniowaniu pól kluczy podstawowych trzeba
wprowadzić do systemu bazy danych informacje na temat sposobu poprawnego
łączenia powiązanych danych w logiczną całość. W tym celu definiuje się relacje
między tabelami.
Typy relacji
1. relacja jeden-do-jednego
W relacji jeden-do-jednego każdy rekord w tabeli A może mieć tylko jeden dopasowany
rekord z tabeli B, i tak samo każdy rekord w tabeli B może mieć tylko jeden
dopasowany rekord z tabeli A. Ten typ relacji spotyka się rzadko, ponieważ
większość informacji powiązanych w ten sposób byłoby zawartych w jednej tabeli.
Relacji jeden-do-jednego można używać do podziału tabeli z wieloma polami, do
odizolowania części tabeli ze względów bezpieczeństwa, albo do przechowania
informacji odnoszącej się tylko do podzbioru tabeli głównej.
2. Relacja jeden-do-wielu
Relacja jeden-do-wielu jest najbardziej powszechnym typem relacji.
W relacji jeden-do-wielu rekord w tabeli A może mieć wiele dopasowanych do niego
rekordów z tabeli B, ale rekord w tabeli B ma tylko jeden dopasowany rekord w
tabeli A.
Relacje
3. Relacja wiele-do-wielu
W relacji wiele-do-wielu, rekord w tabeli A może mieć wiele dopasowanych do niego
rekordów z tabeli B i tak samo rekord w tabeli B może mieć wiele dopasowanych do
niego rekordów z tabeli A. Jest to możliwe tylko przez zdefiniowanie trzeciej tabeli
(nazywanej tabelą łącza), której klucz podstawowy składa się z dwóch pól kluczy
obcych z tabel A i B. Relacja wiele-do-wielu jest w istocie dwiema relacjami jedendo-wielu z trzecią tabelą. Na przykład, tabele "Zamówienia" i "Produkty" są
powiązane relacją wiele-do-wielu zdefiniowaną przez utworzenie dwóch relacji jedendo-wielu z tabelą "Pozycje zamówione".
Przykład relacji
Język SQL (ang. Structured Query Language)
klasyfikacja zapytań
●
●
●
●
Data Definition Language (DDL) – polecenia tworzenia i zarządzania
strukturą bazy danych (CREATE, DROP, ALTER)
Data Manipulation Language (DML) – polecenia zarządzania danymi
(INSERT, UPDATE, DELETE)
Data Control Language (DCL) – polecenia kontroli danych (GRANT,
REVOKE, DENY)
Data Querying Language (DQL) – polecenia kwerend danych
(SELECT)
Projektowanie internetowej bazy danych

Baza danych powinna określać model obiektów świata
realnego (np. klienci, zamówienia, książki)

Unikać przechowywania redundantnych danych
–
Marnotrawstwo pamięci
–
Możliwość powstania anaomalii uaktualniania

Zapisywać atomowe wartości kolumn

Dobrać właściwe klucze, które zagwarantują unikalność
rekordów

Unikać tworzenia tabel z wieloma pustymi polami
Architektura internetowej bazy danych
1
Przeglądarka
2
Serwer WWW
6
3
Interpreter PHP
5
Serwer MySQL
4
Stosowanie MySQL

Uruchamianie bazy danych MySQL

Definiowanie użytkowników i nadawanie przywilejów

Usuwanie przywilejów

Tworzenie tabel

Wstawianie danych

Wyszukiwanie danych

Zmiany struktury i zawartości tabel
Uruchomienie bazy MySQL
Połącznie z bazą danych MySQL
mysql -h nazwa_komputera -u identyfikator_uzytkonika – p
np:
> mysql -h localhost -u root -p
tworzenie bazy danych:
> create database nazwa_bazy;
pokazanie utworzonych baz danych:
> show databases;
przejscie do bazy:
>use nazwa_bazy;
Poziomy przywilejów
Polecenia GRANT i REVOKE służą do nadawania i
odbierania użytkownikom MySQL praw na czterech
poziomach uprzywilejowania:
• globalny
• baza danych
• tabela
• kolumna
Definiowanie użytkowników
GRANT przywileje [kolumny]
ON obiekt
TO identyfikator_uzytkownika [ IDENTYFIED BY 'haslo']
[REQUIRE opcje_ssl]
[WITH [GRANT OPTION | ograniczenia]
Utworzenie użytkownika mającego status administratora:
mysql> grant all
-> on *
-> to robert identified by 'haslo'
-> with grant option;
Utworzenie użytkownika bez przywilejów
mysql>grant usage
-> on moja_baza. *
-> to student identified by 'mnb123‘;
Modyfikowanie przywilejów
mysql> grant select, insert, update, delete, alter, drop
->on moja_baza.*
->to student
Usunięcie części przywilejów:
mysql> revoke alter, drop
->on moja_baza.*
->from student
Typy przywilejów
Typy przywilejów:
•
Nadawane zwykłym użytkownikom
– SELECT, INSERT, UPDATE, DELETE, INDEX,
ALTER, CREATE, DROP
•
Przywileje dla administratorów
– FILE, LOCK TABLES, PROCESS, RELOAD, SHOW
DATABASES, SHUTDOWN, SUPER
•
Przywileje specjalne
– ALL PRIVILEGES, USAGE
Usuwanie przywilejów
REVOKE przywileje
ON obiekt
FROM identyfikator_uzytkownika
usuwanie użytkownika - with grant option
mysql>revoke all privileges, grant
->from student;
usuwanie części przywilejów:
mysql> revoke alter, create, drop
->on my_db.*
->from student;
Tworzenie tabel bazy danych
CREATE TABLE nazwa_tabeli(kolumny)
np:
create table klienci
( klientid int unsigned not null auto_increment primary
key,
nazwisko char(50) not null,
adres char(100) not null,
miejscowosc char(30) not null
);
Aby pokazać utworzone tabele:
mysql> show tables;
Aby pokazać zawartość tabeli:
mysql> describe klienci;
Typy danych w MySQL

Liczby całkowite:
TINYINIT, SMALLINIT, MEDIUMINIT, INTEGER, BIGINIT

Liczby zminnoprzecinkowe:
FLOAT, DOUBLE (REAL), DECIMAL

Data i czas:
DATE, TIME, DATETIME, TIMESTAMP, YEAR

Łańcuchy znaków:
CHAR(M), VARCHAR(M)

Dane binarne:
TEXT, BLOB
Wstawianie danych
INSERT [INTO] nazwa_tabeli [ (kolumna1, kolumna2,..)]
VALUES(wartosc1, wartosc2,...)
np:
insert into klienci values
(3, 'Julia Kowalska', 'Wierzbowa 25', 'Warszawa'),
(4, 'Adam Pawlak', 'Szeroka 1/47', 'Szczecin'),
(5, 'Michalina Nowak', 'Zachodnia 357', 'Gliwice');
Wyszukiwanie danych
SELECT [opcje] kolumna
[INTO plik]
FROM nazwy_tabel
[WHERE warunek]
[GROUP BY rodzaj_grupowania]
[HAVING wartosc_funkcji]
[ORDER BY porzadek_sortownia]
[LIMIT limit]
[PROCEDURE nazwa_procedury(argumenty)];
Wyszukiwanie danych
Typy złączeń tabel
•
Kartezjański - wszystkie możliwe kombinacje połączeń
wierszy łączonych tabel przez użycie przecinków oddzielających
nazwy tabel, bez klauzuli WHERE
•
Full join – jak wyżej
•
Cross join – jak wyżej z klauzulą CROSS JOIN między
nazwami tabel
•
Inner join – równoważne przecinkowi między nazwami tabel
stosowane z WHERE (bez klauzuli WHERE staje się Full join)
•
Equi-join – stosowane z warunkiem zawierającym znak
równości w celu wyszukania pasujących do siebie wierszy z
różnych tabel, w SQL odpowiada mu klauzula WHERE
•
Left join/Right join – używane w celu wyszukiwania
brakujących wartości
Typy złączeń tabel
•
Inner join
•
Left join
•
Right join
•
Full join
Wyszukiwanie w wielu tabelach
Łączenie dwóch tabel
Łączenie trzech tabel
Szeregowanie danych w określonym porządku
ORDER BY
Agregowanie i grupowanie danych
Funkcje agregujące:

avg(kolumna)- wartość średnia

count(kolumna) – ilość wartości niezerowych

min(kolumna) - najmniejsza w kolumnie

max(kolumna) - największa w kolumnie

std(kolumna) - odchylenie standardowe

sum(kolumna) – suma wartości w kolumnie
Zmiany rekordów
UPDATE nazwa_tabeli
SET kolumna1=wyrażenie1, kolumna2=wyrażenie2
[WHERE warunek]
[ORDER BY kryteria_porzadkowania]
[LIMIT ilosc]
np.:
update klienci
set adres='Olkuska 250'
where klientid=4;
Zmiany struktury tabel
ALTER TABLE nazwa_tabeli zmiana
[ , zmiana, ...]
np.
alter table zamowienia
add podatek float(6.2) after wartosc; <-dodanie kolumny
alter table zamowienia
drop podatek; <-usuwanie kolumny
alter table klienci
modify nazwisko char(70) not null; <-zmiana typu kolumny
Usuwanie elementów bazy danych
Usuwanie rekordów:
DELETE FROM tabela
[WHERE warunek]
[ORDER BY kolumny_sortowania]
[LIMIT ilosc]
np.
delete from klienci
where klientid=5;
Usuwanie tabel:
drop table nazwa_tabeli;
Usuwanie całej bazy danych:
drop database baza_danych;
Administracja i funkcje zaawansowane MySQL

System przywilejów

Ochrona bazy danych

Tworzenie kopii zapasowych bazy danych

Ładowanie danych do tabeli z pliku

Maszyny zapisu

Transakcje

Klucze obce

Procedury składowane
System przywilejów
Systemowa baza danych mysql
przechowuje w tabelach informacje o
przywilejach wszystkich użytkowników
zarejestrowanych do bazy i zmiany
wywołane poleceniem GRANT
Tabela user - globalne przywileje
użytkowników
Tabele db i host - przywileje dostępu do
poszczególnych baz danych
Tabele tables_priv i columns_priv –
przywileje nadane na wskazane tabele
i kolumny
Aby odświeżyć zmienione przywileje
należy przeładować bazę danych:
flush privleges;
z poziomu systemu:
mysqladmin flush-privileges
mysqladmin reload
Ochrona bazy danych

Nadawanie wszystkim użytkownikom haseł i odpowiednie ich ukrywanie

Ustawianie przywilejów użytkowników na jak najbardziej ograniczone,
tylko na ich potrzeby.

Wszystkie dane pochodzące od użytkownika powinny podlegać weryfikacji.

W przypadku przesyłania danych poufnych należy zastosować protokół
SSL.
Tworzenie kopii zapasowej bazy danych MySQL
W katalogu bin serwera
mysqldump -u root -p -–opt -–all-databases >
/sciezka/do/kopii/wszystkie.sql
zapisanie w pliku zrzutu wszystkie.sql wszystkich poleceń SQL
niezbędnych do zrekonstruowania bazy danych.
mysqlhotcopy database /sciezka/do/kopii
LOAD DATA INFILE
LOAD DATA INFILE "noweksiazki.txt" INTO TABLE ksiazki;
Instrukcja pozwala na odczytanie danych z pliku noweksiazki.txt i
zapisanie ich w tabeli ksiazki.
Pola danych w pliku muszą być rozdzielone znakiem tabulacji, dane
umieszczone w apostrofach, a każdy wiersz musi być zakończony
znakiem nowego wiersza(\n).
MySQL
Maszyny zapisu
Maszyna zapisu określa typ i sposób przechowywania tabeli:

MyISAM – typ domyślny o indeksowo-sekwencyjnej metodzie dostępu, który jest
standardową metodą zapisu plików i rekordów. Nie obsługuje transakcji i kluczy
obcych.

ISAM – typ jak powyżej, obecnie uznany za przestarzały

MEMORY – zapisywanie tymczasowych tabel w pamięci (do pracy na danych
tymczasowych)

MERGE – cechy MyISAM, omija systemowe ograniczenie wielkości pliku

BDB – tabele obsługujące transakcje (Berkeley DB)

InnoDB – tabele obsługujące transakcje i klucze obce, działające szybciej od tabel
BDB, w przypadku stosowania maszyny zapisu obsługującej transakcje jest to
zalecany typ tabel.
Zmiana typu tabeli:
alter table zamowienia type=innodb;
Transakcje

Transakcje to mechanizm pomagający w utrzymaniu spójności danych,
szczególnie w przypadku wystąpienia błędu bądź załamania serwera.

Transakcją jest zapytanie lub seria zapytań, dla których
zagwarantowane jest, że zostaną wykonane w całości, lub też w całości
niewykonane.

Transakcje muszą spełniać normy ACID:
atomowość – transakcja musi być wykonana w całości lub nie
wykonana wogóle
spójność – transakcja powinna pozostawić spójną bazę danych
izolacja – operacja do zakończenia transakcji powinna być odizolowana
od bazy
trwałość – po zapisaniu w bazie danych transakcja powinna być trwała
Uruchamianie transakcji w InnoDB

Wyłączenie domyślnego, automatycznego zatwierdzania dla sesji:
set autocommit=0;

Jeżeli automatyczne zatwierdzanie jest włączone, każdą transakcję
należy zaczynać instrukcją:
start transaction;

Aby zatwierdzić instrukcje składające się na transakcję nalaży
wpisać: commit;

Czynność można cofnąć przed wydaniem commit poleceniem :
rollback;
Klucze obce

Mechanizm kluczy obcych dostępny jest w tabelach typu MyISAM i
InnoDB

Klucze obce mogą być traktowane tak samo jak klucze główne i
będą wymagały podawania właściwych wartości zgodnych z
numeracją tego klucza

Tabele typu InnoDB wprzeciwieństwie do MyISAM kontrolują
integralność kluczy obcych.
np.
>alter table pozycje_zamowione
add foreign key (zamowienieid) references
zamowienia(zamowienieid);
Kolumna zamowienieid staje się kluczem obcym na podstawie klucza
głownego tabeli zamowienia w kolumnie o tej samej nazwie.
Klucze obce
Przykład nadania klucza obcego dla pola zamowieniaid i próba
wpisania wartości do tego pola. W przypadku tabeli InnoDB takie
działanie jest niedozwolone, w przeciwieństwie do MyISAM.
Procedury składowane
Procedura składowana jest podprogramem utworzonym i zapisanym w MySQL.
#Przykład prostej procedury skladowanej
delimiter//
create procedure suma_zamowien(out suma float)
BEGIN
select sum(wartosc) into suma from zamowienia;
END
//
delimiter;
#Uruchomienie procedury
call suma_zamowienia(@t);
Funkcje składowane
#Przykładowa funkcja
delimiter //
create function dodaj_podatek(cena float) returns float
return cena*1.22;
//
delimiter ;
#uruchomienie funkcji
select dodaj_podatek(100);
Łączenie z bazą danych
mysqli_connect(servername,username,password,dbname);
new mysqli (servername,username,password,dbname);
$con = mysqli_connect("localhost","peter","abc123”, ”mydb”);
lub
$con = new mysqli ("localhost","peter","abc123”, ”mydb”);
if (!$con)
{
die(”Nie mogę nawiązać połączenia.” );
}
Połączenie z PostgreSQL:
$dbconn = pg_connect ("host=localhost port=5432 dbname=testdb user=lamb
password=foo");
Pobieranie danych z bazy danych
Wybór bazy danych:
mysqli_selecet_db($db, nazwa_bazy);
$db ->select_db(nazwa_bazy);
Wysłanie zapytania:
$wynik = mysqli_query($db, $zapytanie);
$wynik=$db->query($zapytanie);
Wersja PostgreSQL:
$wynik = pg_query($dbconn, $zapytanie);
Odczytanie rezultatu zapytania
Wersja proceduralna:
$wiersz = mysqli_fetch_assoc($wynik); //tablica asocjacyjna
$wiersz = mysqli_fetch_row($wynik); //tablica numeryczna
$wiersz = mysqli_fetch_array($wynik); //obie tablice
Wersja obiektowa:
$wiersz = $wynik->fetch_assoc();
$wiersz = $wynik->fetch_row();
$wiersz = $wynik->fetch_array();
Wersja PostgreSQL:
$wiersz = pg_fetch_assoc($wynik);
$wiersz = pg_fetch_row($wynik);
$wiersz = pg_fetch_array($wynik);
Zamykanie połączenia z bazą danych
mysqli_free_result($wynik);
$wynik -> free();
mysqli_close($db);
$db->close();
pg_close($dbconn);
Przykład- Pobieranie danych z bazy
<?php
$db = mysqli_connect("localhost", "student", "student", "test");
if(!$db)
{
die('<p><strong>Nie można odczytać danych w tej
chwili</strong></p></body></html>');
}
$zapytanie = "select * from posty";
$wynik = mysqli_query($db, $zapytanie);
//obliczenie ilosci pobranych rekordów
$ilosc_pozycji= mysqli_num_rows($wynik);
if($ilosc_pozycji==0){
echo '<p>Brak postów</p>';
}else
{
for($i=0; $i<$ilosc_pozycji && $i<3; $i++){
$wiersz=mysqli_fetch_assoc($wynik);
echo '<div id="posty"><p >'.stripslashes($wiersz['data']).
'</p><p> '.stripslashes($wiersz['temat']). '</p>
<p> '.stripslashes($wiersz['tresc']). '</p>
<p>'.stripslashes($wiersz['autor']). '</p>
</div>';
}
}
mysqli_free_result($wynik);
mysqli_close($db);
?>
Wpisywanie danych do bazy
@ $db = mysqli_connect('localhost', 'student', 'student', 'test');
if(!$db)
{
die('<p><strong>Nie można zarejestrować w tej
chwili</strong></p></body></html>');
}
//wysyłanie zapytania
$zapytanie = "insert into posty(data, autor, temat, tresc)
values('".$data."','".$autor."','".$temat."','".$tresc."')";
$wynik = mysqli_query($db, $zapytanie);
if($wynik)
echo '<div id="one">Post został dodany:
'.mysqli_affected_rows($db).'</div>';
mysqli_close($db);
Włączenie obsługi PHP w serwerze Apache
W pliku konfiguracyjnym Apache:
LoadModule php5_module "C:\php\php5apache2_4.dll"
AddHandler application/x-httpd-php .php
# configure the path to php.ini
PHPIniDir "C:\php"
; http://php.net/extension-dir
; extension_dir = "./"
; On windows:
extension_dir = "C:\php\ext"
Włączenie obsługi baz danych w
interpreterze PHP
Usunąć komnetarz w postci ';'
dla MySQL:
extension=php_mysql.dll
extension=php_mysqli.dll
Dla PostgreSQL:
extension=php_pgsql.dll