Lab_5.pdf

Przedmiot: Bazy Danych I

II informatyka, studia dzienne

Laboratorium 5 – Struktury danych w MySQL

typy danych

relacje

klucze i indeksy

Troch teorii:

MySQL jest najpopularniejszym darmowym systemem obs ugi baz danych

rozpowszechnianym na zasadach licencji GPL. Jego nowatorska budowa pozwoli a na

stworzenie

niezwykle

szybkiego

i niezawodnego serwera obs uguj cego bazy danych.

ł ą

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

ść ł ż

przeprowadzania oblicze . Stosuj c pola tego typu we w asnej bazie danych

ń ą ł

ż żć ś ł ż żż

obliczeniach mog doprowadzi do b du przepe nienia

ą ć łę ł

FLOAT (dok adno )

ł ść Liczba zmiennoprzecinkowa z precyzj wyra an liczb bajtów. Dwie

ą ż ą ą

ś ż ę

zmiennoprzecinkow o pojedynczej precyzji, natomiast 8 - liczą ę

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

+ 3 8

do - 1.175494351E - 38, 0 i 1.175494351E - 38 do 3.402823466E +38

DOUBLE

ą ś

od - 1.7976931348623157E +308 do - 2.2250738585072014E - 308, 0, i

2.2250738585072014E - 308 do 1.7976931348623157E +308

8 - bajtowa warto ca kowita. Typ BIGINT jest u ywany podczas

nale y uwa a , aby ich warto ci nie by y zbyt du e, poniewa u yte w

warto ci akceptowane jako precyzja to 4 i 8. U ycie 4 tworzy liczb

8 - bajtowa liczba zmiennoprzecinkowa przechowuj ca warto ci z zakresu

DECIMAL

Pole zmiennoprzecinkowe o takiej samej pojemno ci jak DOUBLE. Liczba,

zamiast zosta zredukowana do postaci szesnastkowej, pami tana jest w

formacie znakowym

DATE

Data (bez czasu), wy wietlana w formacie RRRR-MM - DD. Akceptuje

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,

ą ę

up yn a od tej daty ulegnie przepe nieniu w roku 2037. Je li polu temu nie

ł ęł

ł ś

ł ść ż ś

otrzyma ono warto daty bie cej

ść żą

TIME

Czas mieszcz cy si w przedziale od - 838:59:59 do 838:59:59

ą ę

YEAR

ś ł ść

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

ł ś

jest jedynie taki obszar pami ci, jakiego wymaga warto wstawiona w to pole

ę ść

TINYBLOB, TINYTEXT Kolumna binarna lub tekstowa o rozmiarze nie przekraczaj cym 25ą

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

LONGBLOB, LONGTEXT Kolumna binarna lub tekstowa o rozmiarze nie przekraczaj cym

4 294 967 295 bajtów

ł ń ż ć ą ś

wymienionych na li cie warto ci w definicji tej kolumny

ś ś

ł ń ż ć ą ę ś

spo róś warto ci wyst puj cych na li cie w definicji tej kolumny

ś ę ą ś

Tworzenie tabel

Do utworzenia tabeli s u y polecenie

ł ż

CREATE TABLE :

CREATE TABLE nazwa_tabeli struktura_tabeli;

Po ka dej nazwie pola nast puje definicja typu danych, jakie b dzie ono zawiera o. Istnieje

ż ę ę ł

mo liwo okre lenia warto ci domy lnej, jakie przyjmie ka de nowe pole w mo mencie,

ł ś ś ę ż

pola po s owie kluczowym DEFAULT zaraz po definicji typu. Polecenie tworz ce tabel z

ą ę

daty w ró nych formatach z zakresu od 1000 - 01 - 01 do 9999 - 12 - 31

do momentu kiedy 32 - bitowe pole przechowuj ce liczb sekund, jaka

zosta a nadana warto przez wyra enie INSERT lub UPDATE, domy lnie

Rok. Dozwolone warto ci to przedzia od 1901 do 2155 oraz warto 0000.

Pole znakowe o zmiennej d ugo ci z zakresu od 1 do 255 bajtów. Zajmowany

16 777 215 bajtów

ENUM ('elem1', 'elem2', ...) Kolumna a cuchowa, która mo e zawiera jedn z warto ci

SET ('elem1', 'elem2', ...) Kolumna a cuchowa, która mo e zawiera dowoln liczb warto ci

ż ść ś ś ś ż

kiedy nie zostanie wype nione innymi danymi. Warto ci domy lne podaje si dla ka dego

definicj warto ci domy lnych dla niektórych pól (data_urodzenia = > '1950 - 01 - 01' i placa

ą ś ś

= > '1000.00') mo e wygl da nast puj co:

ż ą ć ę ą

CREATE TABLE pracownicy (

imie VARCHAR(30),

nazwisko VARCHAR(30),

data_urodzenia DATE DEFAULT '1950-01-01',

placa DECIMAL(10,2) DEFAULT '1000.00'

);

Tabela pracownicy b dzie wówczas wygl da :

ę ą ć

+----------------+---------------+------+-----+------------+-------+

+----------------+---------------+------+-----+------------+-------+

| data_urodzenia | date | YES | | 1950-01-01 | |

| placa | decimal(10,2) | YES | | 1000.00 | |

+----------------+---------------+------+-----+------------+-------+

Inne atrybuty pól tabeli:

AUTO_INCREMENT – automatycznie zwi kszana warto w polu

ę ść

ż ć

UNIQUE – pole unikatowe

Przy u yciu opcji AUTO_INCREMENT nie wstawiamy do tego pola danych, s one

wstawiane automatycznie.

Dodawanie wierszy do tabeli:

INSERT INTO [nazwa_tabeli] values ([warto ci])

ś - wszystkie warto ci

pól

INSERT INTO [nazwa_tabeli] ([pola]) values ([warto ci])

ś – wybrane

pola

Indeksy w MySQL

Indeksy , w du ym uproszczeniu, powoduj przy pieszenie operacji wyszukiwania

ż ą ś

wykonywanych na bardzo du ych tabelach. W indeksach system bazy danych zapisuje

wewn trzne przyporz dkowania bloków danych do odpowiednich rekordów, dzi ki czemu

ę ą

operacja wyszukiwania sprowadza si do przeszukania indeksu, a nie ca ej tabeli.

ę ł

ż ł ć ł ęś

Na o enie indeksów przebiega w podobny sposób do modyfikacji struktury tabeli. Dodanie

ł ż

indeksu na polu nazwisko b dzie wygl da o nast puj co:

ę ą ł ę ą

ALTER TABLE pracownicy ADD INDEX nazwisko_idx (nazwisko);

Spowoduje to utworzenie indeksu o nazwie nazwisko_idx opartego na kolumnie nazwisko w

tabeli pracownicy .

Usuni cie indeksu odbywa si za pomoc polecenia:

ę ę ą

ALTER TABLE DROP INDEX

nazwisko_idx;

NOT NULL – pole nie mo e by puste

Indeks nale y zak ada dla pól, wed ug których najcz ciej jest wykonywane wyszukiwanie.

Relacje w MySQL

W MySQL brak jest przede mo liwo ci definiowania relacji pomi dzy tabelami. Jest to jedno z

ż ś ę

powa niejszych niedoci gni w systemie, które jednak nie zniech ca u ytkowników MySQLa

do osadzania systemów opartych o to rozwi zanie. Na wybrane kolumny mo na jednak

ą ż

na o y indeksy PRIMARY KEY, KEY FOREIGN_FIELD, które mog zadba o to, by dane

ą ć

wstawiane do tabel by y w a ciwe.

ł ś

CREATE TABLE `TEST` (

`master_db` varchar(64) NOT NULL default '',

`master_table` varchar(64) NOT NULL default '',

`master_field` varchar(64) NOT NULL default '',

`foreign_db` varchar(64) NOT NULL default '',

`foreign_table` varchar(64) NOT NULL default '',

`foreign_field` varchar(64) NOT NULL default '',

PRIMARY KEY (`master_db`, `master_table`, `master_field`),

KEY foreign_field (foreign_db, foreign_table)

) TYPE=MyISAM COMMENT='Tabela testowa';

Zadania do samodzielnego wykonania:

1. Uruchom interfejs PHPMyAdmin i utwórz baz danych o nazwie TEST.

2. W bazie danych utwórz tabele wg schematu:

3. Dodaj do ka dej tabeli po 5 wierszy, pami taj c o zachowaniu ostro no ci przy relacjach

ż ę ą ż ś

mi dzy tabelami.

4. Zapisz wszystkie operacje w pliku create_table.sql. Usu z bazy wszystkie tabele

poleceniem

DROP TABLE [nazwa_tabeli] i wywo aj skrypt create_table.sql

5. Napisz polecenia SQL, wy wietlaj ce

ś ą

ę ą ę

b) dane osób o nazwisku zawieraj cym fraz „ski”

ą ę

c) nazwy zespo ów, których pracownicy maj nazwisko zawieraj ce fraz „ski”

ł ą ą ę

d) minimalne, rednie i maksymalne zarobki dla wszystkich pracowników

e) minimalne, rednie i maksymalne zarobki tylko dla pracownika o podanym przez nas

nazwisku

ż ą ęć ę ż

ł żć

a) imi i nazwisko osoby zarabiaj cej najwi cej z danego etatu

f) liczb osób pracuj cych

w zespole o danej nazwie

6. Usu baz danych [po ocenionej pracy].

ń ę

Laboratorium 6 – zapytania wybieraj ce, cz. III:

wykonywanie obliczeń

funkcje agreguj ce

klauzule GROUP BY, HAVING

ę ą

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: