MySQL

MySQL
Aleksander Nowacki
Wojciech Nowak
Wprowadzenie
Język SQL (Structured Query Language) służy do
manipulowania
danymi
umieszczonymi
w
relacyjnych bazach danych. Jest językiem
uniwersalnym, dzięki czemu praca na różnych
systemach baz danych sprowadza się do
wydawania tych samych lub podobnych komend
tzw.
zapytań
SQL.
Język
SQL
został
zaimplementowany w większości relacyjnych
systemów baz danych takich jak: DB2, Oracle,
InterBase, MySQL, dBase, Paradox.
Składnię języka SQL można
podzielić na trzy części:



język definiowania struktur danych - DDL (Data
Definition Language) - jest wykorzystywany do wszelkiego
rodzaju operacji na tabelach, takich jak: tworzenie,
modyfikacja oraz usuwanie
język do wybierania i manipulowania danymi - DML
(Data Manipulation Language) - służy do manipulowania
danymi umieszczonymi w tabelach, pozwala na wstawienie
danych, ich prezentację, modyfikowanie oraz usuwanie
język do zapewnienia bezpieczeństwa dostępu do
danych - DCL (Data Control Language) - jest używany
głównie przez administratorów systemu baz danych do
nadawania odpowiednich uprawnień do korzystania z bazy
danych.
Relacyjny system baz
danych
Relacyjny system baz danych przechowuje
wszystkie dane w tabelach. Każda tabela
zawiera dane na konkretny temat, np. dane o
klientach, pracownikach, towarach itp. System
bazy danych zarządza tymi informacjami,
pozwala m.in. na szybsze ich wyszukiwanie i
zorganizowanie. Za każdym razem, kiedy
potrzebujemy informacji z bazy danych, musimy
"zapytać" system bazy danych w zrozumiałym
dla niego języku. Tym językiem jest SQL.
Co to jest MySQL?
MySQL
jest
najpopularniejszym
darmowym systemem obsługi baz danych
rozpowszechnianym na zasadach licencji
GPL (General Public License). Jego
nowatorska
budowa
pozwoliła
na
stworzenie
niezwykle
szybkiego
i
niezawodnego serwera obsługującego
bazy danych.
Połączenie z bazą danych
MySQL
Aby połączyć się z serwerem baz danych
potrzebujemy specjalnego programu tzw.
klienta
lub
języka
skryptowego
(umieszczanego na serwerach WWW),
który posiada wbudowaną obsługę baz
danych. Bardzo dobrym narzędziem, które
może okazać się przydatne podczas nauki
SQL, jest panel administracyjny do baz
danych - phpMyAdmin.
phpMyAdmin
Typy danych w MySQL
TINYINT
1-bajtowe pole, przechowujące wartości całkowite bez znaku z przedziału od 0 do 255 lub ze
znakiem z przedziału od -127 do 127
SMALLINT
2-bajtowa wartość całkowita. Zakres wartości bez znaku od 0 do 65 535 lub ze znakiem od -32 768
do 32 768
MEDIUMINT
3-bajtowa wartość całkowita. Zakres wartości od 0 do 16 777 215 lub ze znakiem od -8 388 608 do
8 388 608
INT
4-bajtowa wartość całkowita. Zakres wartości ze znakiem od -2 147 483 648 do 2 147 483 647 lub
bez znaku od 0 do 4 294 967 295
BIGINT
8-bajtowa wartość całkowita. Typ BIGINT jest używany podczas przeprowadzania obliczeń. Stosując
pola tego typu we własnej bazie danych należy uważać, aby ich wartości nie były zbyt duże,
ponieważ użyte w obliczeniach mogą doprowadzić do błędu przepełnienia
FLOAT(dokładno
ść)
Liczba zmiennoprzecinkowa z precyzją wyrażaną liczbą bajtów. Dwie wartości akceptowane jako
precyzja to 4 i 8. Użycie 4 tworzy liczbę zmiennoprzecinkową o pojedynczej precyzji, natomiast 8 liczbę zmiennoprzecinkową o podwójnej precyzji. Szczegóły dotyczące tych dwóch typów danych
opisują pola FLOAT i DOUBLE
FLOAT
4-bajtowa liczba zmiennoprzecinkowa z zakresu od -1.402823466E+38 do -1.175494351E-38, 0 i
1.175494351E-38 do 3.402823466E+38
DOUBLE
8-bajtowa liczba zmiennoprzecinkowa przechowująca wartości z zakresu od 1.7976931348623157E+308 do -2.2250738585072014E-308, 0, i 2.2250738585072014E-308 do
1.7976931348623157E+308
DATE
Data (bez czasu), wyświetlana w formacie RRRR-MM-DD. Akceptuje daty w różnych formatach z
zakresu od 1000-01-01 do 9999-12-31
Typy danych w MySQL (2)
DATETIME
Pole daty z czasem dnia wyświetlane według formatu RRRR-MM-DD GG:MM:SS. Może
przechowywać wartości z zakresu od 1000-01-01 00:00:00 do 9999-12-31 23:59:59
TIMESTAMP
Data i czas liczony od początku epoki systemu UNIX, 1970-01-01 00:00:00, do momentu kiedy 32bitowe pole przechowujące liczbę sekund, jaka upłynęła od tej daty ulegnie przepełnieniu w roku
2037. Jeśli polu temu nie została nadana wartość przez wyrażenie INSERT lub UPDATE, domyślnie
otrzyma ono wartość daty bieżącej
TIME
Czas mieszczący się w przedziale od -838:59:59 do 838:59:59
YEAR
Rok. Dozwolone wartości to przedział od 1901 do 2155 oraz wartość 0000. MySQL wyświetla
wartość typu YEAR w formacie RRRR
CHAR
Pole znakowe o stałej długości z zakresu od 1 do 255 bajtów. Po wstawieniu wartości puste miejsca
pola CHAR są uzupełniane z prawej strony spacjami
VARCHAR
Pole znakowe o zmiennej długości z zakresu od 1 do 255 bajtów. Zajmowany jest jedynie taki
obszar pamięci, jakiego wymaga wartość wstawiona w to pole
TINYBLOB,
TINYTEXT
Kolumna binarna lub tekstowa o rozmiarze nie przekraczającym 255 bajtów
BLOB, TEXT
Kolumna binarna lub tekstowa o rozmiarze nie przekraczającym 65 535 bajtów
MEDIUMBLOB,
MEDIUMTEXT
Kolumna binarna lub tekstowa o rozmiarze nie przekraczającym 16 777 215 bajtów
LONGBLOB,
LONGTEXT
Kolumna binarna lub tekstowa o rozmiarze nie przekraczającym 4 294 967 295 bajtów
Tworzenie nowej bazy
danych
Aby dodać nowa bazę danych,
wystarczy posłużyć się następującym
poleceniem:
CREATE DATABASE `nazwa_bazy`;
Struktura MySQL
Każda kolumna tabeli
posiada nazwę i zawiera
inny rodzaj danych. Każdej
kolumnie przypisany jest
typ danych.
Baza danych
Tabele
Kolumny
Każdy wiersz zawiera zbiór
pojedynczych wartości
odpowiadających kolumnom.
Każda z tych wartości musi
być tego samego typu, jaki
przypisano kolumnie, w
której się znajduje.
Wiersze
Wartości
Każdy wiersz tabeli
odpowiada innemu
klientowi. Format
tabelaryczny powoduje, że
każdy wiersz ma te same
atrybuty.
Co to są tabele?
Tabele zawierają pola, które określają, jakie
dane będzie zawierał pojedynczy rekord (inaczej:
wiersz).
Jeśli
chcemy
utworzyć
tabelę
przechowującą dane o pracownikach, trafią do
niej pola typu: imię, nazwisko, data_urodzenia,
płaca. Jeden z rekordów będzie wyglądał
następująco:
'Jan',
'Kowalski',
'2002-0720', '1200.00'. Już na pierwszy rzut oka widać, że
podane dane są różnego typu. Imię i nazwisko to
dane
tekstowe,
data_urodzenia
to
pole
zawierające w sobie datę, płaca reprezentuje
dane liczbowe.
Tworzenie tabel
Do utworzenia tabeli służy polecenie SQL
CREATE TABLE:
CREATE TABLE nazwa_tabeli struktura_tabeli;
Tworzenie tabel
Każda tabela powinna posiadać swoją
nazwę. Definiujemy ją w
miejscu nazwa_tabeli. W miejsce
struktura_tabeli wstawiamy dokładną
specyfikację poszczególnych pól, jakie
powinna zawierać tabela. Polecenie
SQL dla przykładu podanego wyżej
będzie wyglądać następująco:
Tworzenie tabel
CREATE TABLE pracownicy (
imie VARCHAR(30),
nazwisko VARCHAR(30),
data_urodzenia DATE DEFAULT '1950-0101',
placa DECIMAL(10,2) DEFAULT '1000.00'
);
Tworzenie tabel
Struktura utworzonej tabeli:
Wstawianie danych do tabeli
Po utworzeniu tabeli należy wypełnić ją danymi.
Służy do tego polecenie INSERT.
INSERT INTO nazwa_tabeli [(kolumna1, kolumna2, kolumna3, …)] VALUES
(wartosc1,wartosc2, wartosc3,…);
Polecenie SQL dla powyższego przykładu będzie wyglądać następująco:
INSERT INTO pracownicy VALUES ('Jan', 'Kowalski', '2002-07-20', '1200.00');
INSERT INTO pracownicy VALUES ('Aleksander', 'Borowiecki', '1952-08-06', '1500.34');
INSERT INTO pracownicy VALUES ('Aniela', 'Michalkowska', '1970-05-23', '854.29');
INSERT INTO pracownicy VALUES ('Katarzyna', 'Kowalska', '2002-07-02', '1200.00');
INSERT INTO pracownicy (imie, nazwisko) VALUES ('Izabela', 'Kwiatkowska');
Wstawianie danych do tabeli
Modyfikacja danych
Poleceniem INSERT można wstawiać nowe dane do
istniejącej tabeli. Do modyfikacji danych już
wcześniej umieszczonych w tabeli służy polecenie
UPDATE.
UPDATE nazwa_tabeli SET nazwa_pola='nowa_wartość';
UPDATE pracownicy SET imie='Zofia';
Po słowie kluczowym SET podajemy kolejno (po
przecinku) nazwy kolumn wraz z nowymi
wartościami, jakie powinny przyjąć.
Modyfikacja danych
Nie jest to najczęściej pożądana sytuacja.
Zazwyczaj chcemy zmienić dane dotyczące tylko
jednego lub wybranych rekordów. Do określenia,
czego ma dotyczyć zmiana służy klauzula
WHERE podawana na końcu polecenia UPDATE.
W celu zmiany imienia tylko dla Izabeli
Kwiatkowskiej polecenie UPDATE będzie wyglądać
następująco:
UPDATE pracownicy SET imie='Zofia‘
WHERE nazwisko='Kwiatkowska';
Pobieranie danych z tabeli
Aby pobrać dane zapisane w tabeli należy użyć zapytania
SELECT. Jego postać ogólna prezentuje się następująco:
SELECT co_zaprezentować FROM nazwa_tabeli
[WHERE warunki_wyszukiwania]
[ORDER BY sortowanie [ASC | DESC], ...]
[LIMIT [ofset,] ilość_wierszy];
W miejscu co_zaprezentować należy podać (po przecinku) listę kolumn,
które chcemy zawrzeć w zestawieniu. W miejscu nazwa_tabeli podaje się
nazwę tabeli, z której pobiera się dane. W celu wybrania trzech kolumn do
zestawienia z tabeli pracownicy należy wykonać polecenie np.
SELECT imie, nazwisko, placa FROM pracownicy;
w celu wybrania wszystkich kolumn:
SELECT * FROM pracownicy;
Pobieranie danych z tabeli
Klauzula where w
poleceniu select
Dzięki klauzuli WHERE jesteśmy w stanie wpłynąć na zakres prezentowanych
danych. Dzięki niej możemy dokładnie definiować, co chcemy uzyskać swoim
zapytaniem. Specyfikując dokładne warunki wyszukiwania można z tabeli
zawierającej setki tysięcy rekordów wybrać tylko kilka interesujących w danym
momencie informacji.
Stosując operatory przyrównania można dokładnie określić, jakie informacje
chcemy pobrać. Dozwolone w MySQL operatory przyrównania to:







=
równe
>
większe
>= większe równe
<
mniejsze
<= mniejsze równe
<> lub !=
różne
LIKE służy głównie do porównywania danych łańcuchowych
Przykłady zastosowania
klauzuli where
SELECT * FROM pracownicy WHERE placa >= 1000;
Przykłady zastosowania
klauzuli where
SELECT * FROM pracownicy WHERE nazwisko LIKE 'K%';
Dzięki zastosowaniu znaków globalnych (% i _) istnieje możliwość
przyrównania do dowolnego ciągu znaków. Znak '%' (procent) zastępuje
dowolną ilość znaków. Znak '_' (podkreślenie) zastępuje dokładnie jeden znak.
Łączenie warunków w
klauzuli where
Warunki wyboru podawane za WHERE można łączyć ze sobą stosując operatory
AND oraz OR. Dzięki temu istnieje możliwość zbudowania zapytania bardziej
złożonego, a co za tym idzie bardziej dokładnego. W momencie zastosowania
operatora AND wszystkie połączone tak warunki muszą zostać spełnione, aby w
wyniku pojawił się dany rekord. Jeżeli zastosujemy do połączenia warunków
operator OR - wówczas może zostać spełniony tylko jeden z warunków
wchodzących w skład zapytania. Wydając zapytanie:
SELECT * FROM pracownicy WHERE (placa > 500 AND placa < 1000) OR
nazwisko = 'Kowalski';
spowodujemy wyświetlenie w wyniku wszystkich pracowników, których płaca
mieści się w zakresie 500-1000 oraz pracowników o nazwisku 'Kowalski‘.
Sortowanie wyników
zapytania
Dane w tabeli mogą być przechowywane w dowolnej kolejności. Można jednak
spowodować ich pobranie w ściśle określonym porządku. Kolumny, według których
MySQL ma posortować dane podaje się po klauzuli ORDER BY oddzielone
przecinkami. Chcąc więc uszeregować listę pracowników rosnąco według nazwiska i
malejąco według płacy należy wpisać następujące polecenie:
SELECT * FROM pracownicy ORDER BY nazwisko ASC, placa DESC;
ASC oznacza sortowanie rosnąco według podanego pola, DESC natomiast oznacza
sortowanie malejące.
Klauzula limit
Użycie klauzuli LIMIT powoduje wyświetlenie jedynie części rekordów. Aby
pobrać dwa pierwsze rekordy należy napisać:
SELECT * FROM pracownicy LIMIT 2;
Dodatkowe funkcje w
MySQL
Nazwa
Opis
AVG(kolumna)
Oblicza wartość średnią we wskazanej kolumnie.
COUNT(kolumna)
Zwraca liczbę niezerowych wartości w podanej kolumnie.
Jeżeli nazwa kolumny jest poprzedzona słowem
kluczowym DISTINCT, to funkcja zwróci tylko ilość
występujących w niej odrębnych wartości. Jeżeli zamiast
nazwy konkretnej kolumny wpisany zostanie symbol *,
wówczas funkcja zwróci wartość równą licznie wierszy w
tabeli.
MIN(kolumna)
Podaje najmniejszą wartość w kolumnie.
MAX(kolumna)
Podaje największą wartość w kolumnie.
STD(kolumna)
Oblicza odchylenie standardowe wartości w kolumnie.
SUM(kolumna)
Sumuje wszystkie wartości z kolumny
Dodatkowe funkcje w
MySQL
Przykład zastosowania funkcji AVG:
Średnia płaca pracowników wynosi 1150.93 zł.
Usuwanie danych z tabeli
Do usunięcia danych z tabeli służy polecenie DELETE. Aby
usunąć wszystkie dane z tabeli należy wydać polecenie:
DELETE FROM nazwa_tabeli;
Można użyć także opisywanego już wcześniej warunku
wyboru, dzięki któremu specyfikujemy dane przeznaczone
do usunięcia. Aby usunąć z przykładowej tabeli pracownicy
wszystkie rekordy, w których płaca jest wyższa od 1000
należy wydać następujące polecenie:
DELETE FROM pracownicy WHERE placa > 1000;
Usuwanie danych z tabeli
Do modyfikacji struktury tabeli służy polecenie ALTER TABLE.
Zmodyfikowanie struktury tabeli zawierającej już jakieś dane spowoduje
próbę
podporządkowania istniejących danych nowemu formatowi. Dzięki temu
poleceniu można dodawać, modyfikować, usuwać pola oraz
manipulować
indeksami. Ogólna postać polecenia przedstawia się następująco:
ALTER TABLE nazwa_tabeli specyfikacja_struktury;
W miejscu specyfikacja_struktury podajemy dokładną operację, jaką chcemy
wykonać na danej tabeli. Aby dodać kolejne pole do istniejącej tabeli należy
użyć polecenia:
ALTER TABLE pracownicy ADD rozmiar_buta VARCHAR(10);
Spowoduje to dodanie jednego pola o nazwie rozmiar_buta na końcu
struktury tabeli.
Usuwanie tabeli
Do usunięcia tabeli służy proste polecenie:
DROP TABLE nazwa_tabeli;
Spowoduje to usunięcie tabeli wraz ze
wszystkimi umieszczonymi w niej danymi.
Łączenie MySQLa z PHP
Funkcja języka PHP, nawiązująca połączenie z serwerem MySQL,
wygląda następująco:

$db = mysql_connect ("adres", "użytkownik", "hasło");
Po prawidłowym podłączeniu do serwera MySQL należy wybrać bazę,
na której będziesz pracować:

mysql_select_db ("baza");
Po poprawnym połączeniu się z bazą danych można przystąpić do
wydawania poleceń języka SQL. Funkcję PHP wysyłającą zapytanie SQL do
serwera wywołuje się następująco:

$wynik_zapytania = mysql_query ("zapytanie_SQL");
Po zakończonej pracy z bazą danych należy użyć funkcji:

mysql_close ($db);
Zadanie
Wypisz trzy imiona, nazwiska i płace z zarobkami
większymi od 500 i mniejszymi niż 1200,
uszeregowanymi po nazwisku.
HELP!
To już koniec 
Dziękujemy za uwagę.