SIECI KOMPUTEROWE I BAZY DANYCH

ĆWICZENIE NR 7

Temat:TWORZENIE I ZARZĄDZANIE INTERNETOWĄ BAZĄ DANYCH

Akademia Górniczo-HutniczaWydzia In ynierii Mechanicznej i Robotykił ż

Katedra Robotyki i Dynamiki Maszyn

Język SQL

Opis języka SQL

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.

Kurs dostępny w niniejszym opracowaniu dotyczy implementacji języka SQL w systemie bazy MySQL.

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 potrzebujesz informacji z bazy danych, musisz "zapytać" system bazy danych w zrozumiałym dla niego języku. Tym językiem jest SQL. 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.

Aby połączyć się z serwerem baz danych potrzebujesz specjalnego programu tzw. klienta lub języka skryptowego (umieszczanego na serwerach WWW), który posiada wbudowaną obsługę baz danych. Przykładem programu klienckiego może być np. program MySQL-Front lub oprogramowanie dostarczane łącznie z pakietem MySQL. Najczęstszym jednak sposobem korzystania z bazy danych jest połączenie wywoływane z wnętrza skryptu.

Uwaga: Bardzo dobrym narzędziem, które może okazać się przydatne podczas nauki SQL, jest panel administracyjny do baz danych - phpMyAdmin. Jego obsługa została opisana w dalszej części.

Aby połączyć się z serwerem MySQL w trybie tekstowym należy połączyć się z serwerem (149.156.115.209, port22, SSH) i wpisać

mysql --user=sNrlegitymacji --password=Nazwisko [enter]

a następnie:

use sNrlegitymacji [enter].

Wszystkie polecenia należy kończyć ‘;’!

Wszystkie polecenia SQL zawarte poniżej można wykonywać z lini poleceń.

Tabele zawierają pola, które określają, jakie dane będzie zawierał pojedynczy rekord (inaczej: wiersz). Jeśli chcesz utworzyć tabelę przechowującą dane o pracownikach trafią do niej pola typu imię, nazwisko, data_urodzenia, adres, płaca. Jeden z rekordów będzie wyglądał następująco: 'Jan', 'Kowalski', '2002-07-20', 'Kwiatowa 8, Poznań', '1200.00'. Już na pierwszy rzut oka widać, że podane dane są różnego typu. imię, nazwisko i adres to dane tekstowe, data_urodzenia to pole zawierające w sobie datę, płaca reprezentuje dane liczbowe.

Uwaga: W ramach konta otrzymujesz jedną bazę MySQL. Nazwa bazy to „sNrlegitymacji” W jej obrębie możesz natomiast utworzyć nieskończoną ilość tabel.

Do utworzenia tabeli służy polecenie SQL CREATE TABLE:

CREATE TABLE nazwa_tabeli struktura_tabeli;

Każda tabela powinna posiadać swoją nazwę. Definiujesz ją w miejscu nazwa_tabeli. W miejsce struktura_tabeli wstawiasz 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:

CREATE TABLE pracownicy ( imie VARCHAR(30),

nazwisko VARCHAR(30), data_urodzenia DATE, adres VARCHAR(255), placa DECIMAL(10,2));

Powyższe polecenie utworzy tabelę pracownicy o następującej strukturze:

+----------------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------------+---------------+------+-----+---------+-------+| imie | varchar(30) | YES | | NULL | || nazwisko | varchar(30) | YES | | NULL | || data_urodzenia | date | YES | | NULL | || adres | varchar(255) | YES | | NULL | || placa | decimal(10,2) | YES | | NULL | |+----------------+---------------+------+-----+---------+-------+

Po każdej nazwie pola następuje definicja typu danych, jakie będzie ono zawierało. Spis wszystkich możliwych do użycia w MySQL typów danych znajduje się w dalszej części. Istnieje możliwość określenia wartości standardowej, jakie przyjmie każde nowe pole w momencie, kiedy nie zostanie wypełnione innymi danymi. Wartości standardowe podaje się dla każdego pola po słowie kluczowym DEFAULT zaraz po definicji typu. Polecenie tworzące tę samą tabelę jednak z definicją wartości standardowych dla niektórych pól (data_urodzenia => '1950-01-01' i placa => '1000.00') będzie wyglądać następująco:

CREATE TABLE pracownicy ( imie VARCHAR(30), nazwisko VARCHAR(30), data_urodzenia DATE DEFAULT '1950-01-01', adres VARCHAR(255), placa DECIMAL(10,2) DEFAULT '1000.00');

Tabela pracownicy będzie wówczas wyglądać:

+----------------+---------------+------+-----+------------+-------+| Field | Type | Null | Key | Default | Extra |+----------------+---------------+------+-----+------------+-------+| imie | varchar(30) | YES | | NULL | || nazwisko | varchar(30) | YES | | NULL | || data_urodzenia | date | YES | | 1950-01-01 | || adres | varchar(255) | YES | | NULL | || placa | decimal(10,2) | YES | | 1000.00 | |+----------------+---------------+------+-----+------------+-------+

Typ danych Opis

TINYINT1-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

SMALLINT2-bajtowa wartość całkowita. Zakres wartości bez znaku od 0 do 65 535 lub ze znakiem od -32 768 do 32 768

MEDIUMINT3-bajtowa wartość całkowita. Zakres wartości od 0 do 16 777 215 lub ze znakiem od -8 388 608 do 8 388 608

INT4-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

FLOAT4-bajtowa liczba zmiennoprzecinkowa z zakresu od -1.402823466E+38 do -1.175494351E-38, 0 i 1.175494351E-38 do 3.402823466E+38

DOUBLE8-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

DECIMALPole zmiennoprzecinkowe o takiej samej pojemności jak DOUBLE. Liczba, zamiast zostać zredukowana do postaci szesnastkowej, pamiętana jest w formacie znakowym

DATEData (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

DATETIMEPole 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 32-bitowe 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

VARCHARPole 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 nieprzekraczającym 255 bajtów

BLOB, TEXT Kolumna binarna lub tekstowa o rozmiarze nieprzekraczającym 65 535 bajtów

MEDIUMBLOB, MEDIUMTEXT

Kolumna binarna lub tekstowa o rozmiarze nieprzekraczającym 16 777 215 bajtów

LONGBLOB, LONGTEXT

Kolumna binarna lub tekstowa o rozmiarze nieprzekraczającym 4 294 967 295 bajtów

ENUM('elem1', 'elem2', ...)

Kolumna łańcuchowa, która może zawierać jedną z wartości wymienionych na liście wartości w definicji tej kolumny

SET('elem1', 'elem2', ...)

Kolumna łańcuchowa, która może zawierać dowolną liczbę wartości spośród wartości występujących na liście w definicji tej kolumny

Po utworzeniu tabeli należy wypełnić ją danymi. Służy do tego polecenie INSERT. Polecenie SQL dla przykładu będzie wyglądać następująco:

INSERT INTO pracownicy VALUES ('Jan', 'Kowalski', '2002-07-20', 'Kwiatowa 8, Poznań', '1200.00');

Powyższe polecenie wstawi pojedynczy rekord do tabeli pracownicy. Aby wstawić kolejne rekordy należy ponowić powyższe zapytanie używając nowych danych:

INSERT INTO pracownicy VALUES ('Aleksander', 'Borowiecki', '1952-08-06', 'ul. Wycieczkowa 8/12, Wrocław', '1500.34');INSERT INTO pracownicy VALUES ('Aniela', 'Michałkowska', '1970-05-23', 'al. Kwiatowa 15, Kraków', '854.29');INSERT INTO pracownicy VALUES ('Katarzyna', 'Kowalska', '2002-07-02', 'Kwiatowa 8, Poznań', '1200.00');

Polecenia INSERT można użyć także w innej formie. Za nazwą tabeli można wyspecyfikować listę pól, które będziesz wypełniać danymi. Pozostałe pola przyjmą puste lub standardowe wartości. Zapytanie z wyspecyfikowaną listą pól wygląda następująco:

INSERT INTO pracownicy (imie, nazwisko) VALUES ('Izabela', 'Kwiatkowska');

Powyższa składnia polecenia jest szczególnie przydatna, gdy tabela posiada wiele kolumn, a nie chcesz w danym momencie podawać wszystkich danych dla pojedynczego rekordu lub satysfakcjonują Cię ustawione wcześniej wartości standardowe.

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. Jego składnia jest następująca:

UPDATE nazwa_tabeli SET nazwa_pola='nowa_wartość';

Po słowie kluczowym SET podaj kolejno (po przecinku) nazwy kolumn wraz z nowymi wartościami, jakie powinny przyjąć. Po wydaniu poniższego polecenia:

UPDATE pracownicy SET imie='Zofia';

Wszystkie rekordy w polu imie będą miały wartość 'Zofia':

+-------+--------------+----------------+-------------------------------+---------+| imie | nazwisko | data_urodzenia | adres | placa |+-------+--------------+----------------+-------------------------------+---------+| Zofia | Kowalski | 2002-07-20 | Kwiatowa 8, Poznań | 1200.00 || Zofia | Kwiatkowska | NULL | NULL | NULL || Zofia | Borowiecki | 1952-08-06 | ul. Wycieczkowa 8/12, Wrocław | 1500.34 || Zofia | Michałkowska | 1970-05-23 | al. Kwiatowa 15, Kraków | 854.29 || Zofia | Kowalska | 2002-07-02 | Kwiatowa 8, Poznań | 1200.00 |+-------+--------------+----------------+-------------------------------+---------+

Nie jest to najczęściej pożądana sytuacja. Zazwyczaj chcesz 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';

Dokładny opis klauzuli WHERE znajduje się w dalszej części laboratorium. 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ć podaj (po przecinku) listę kolumn, które chcesz zawrzeć w zestawieniu. W miejscu nazwa_tabeli podaj nazwę tabeli, z której pobierzesz dane. Wybierając trzy kolumny do zestawienia z tabeli pracownicy napiszesz następująco:

SELECT imie, nazwisko, placa FROM pracownicy;

Spowoduje to wyświetlenie wszystkich rekordów, jednak w zestawieniu zostaną zaprezentowane jedynie wartości trzech pól: imie, nazwisko i placa:

+------------+--------------+---------+| imie | nazwisko | placa |+------------+--------------+---------+| Jan | Kowalski | 1200.00 || Izabela | Kwiatkowska | NULL || Aleksander | Borowiecki | 1500.34 || Aniela | Michałkowska | 854.29 || Katarzyna | Kowalska | 1200.00 |+------------+--------------+---------+

Aby w zestawieniu umieścić wszystkie pola można użyć w miejscu co_zaprezentować znaku '*' (gwiazdki):

SELECT * FROM pracownicy;

Zostaną wówczas wyświetlone wszystkie rekordy znajdujące się w tabeli:

+------------+--------------+----------------+-------------------------------+---------+| imie | nazwisko | data_urodzenia | adres | placa |+------------+--------------+----------------+-------------------------------+---------+| Jan | Kowalski | 2002-07-20 | Kwiatowa 8, Poznań | 1200.00 || Izabela | Kwiatkowska | NULL | NULL | NULL || Aleksander | Borowiecki | 1952-08-06 | ul. Wycieczkowa 8/12, Wrocław | 1500.34 || Aniela | Michałkowska | 1970-05-23 | al. Kwiatowa 15, Kraków | 854.29 || Katarzyna | Kowalska | 2002-07-02 | Kwiatowa 8, Poznań | 1200.00 |+------------+--------------+----------------+-------------------------------+---------+

Dzięki klauzuli WHERE jesteś w stanie wpłynąć na zakres prezentowanych danych. Dzięki niej możesz dokładnie definiować, co chcesz 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.

Uwaga: Klauzulę WHERE stosuje się najczęściej w poleceniu SELECT. Ma ona jednak zastosowanie także w innych poleceniach operujących na danych takich jak UPDATE, DELETE itp.

Stosując operatory przyrównania możesz dokładnie określić, jakie informacje chcesz 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:

SELECT * FROM pracownicy WHERE placa >= 1000;

Spowoduje wyświetlenie listy pracowników, których płaca jest większa lub równa 1000:

+------------+------------+----------------+-------------------------------+---------+| imie | nazwisko | data_urodzenia | adres | placa |+------------+------------+----------------+-------------------------------+---------+| Jan | Kowalski | 2002-07-20 | Kwiatowa 8, Poznań | 1200.00 || Aleksander | Borowiecki | 1952-08-06 | ul. Wycieczkowa 8/12, Wrocław | 1500.34 |+------------+------------+----------------+-------------------------------+---------+

SELECT imie, nazwisko, adres FROM pracownicy WHERE nazwisko = 'Kowalski';

Spowoduje wyświetlenie danych (tylko imię, nazwisko i adres) wszystkich pracowników, których nazwisko brzmi dokładnie Kowalski:

+------+----------+--------------------+| imie | nazwisko | adres |+------+----------+--------------------+| Jan | Kowalski | Kwiatowa 8, Poznań |+------+----------+--------------------+

SELECT * FROM pracownicy WHERE nazwisko LIKE 'K%';

Spowoduje wyświetlenie wszystkich pracowników, których nazwisko rozpoczyna się na literę 'K':

+---------+-------------+----------------+--------------------+---------+| imie | nazwisko | data_urodzenia | adres | placa |+---------+-------------+----------------+--------------------+---------+| Jan | Kowalski | 2002-07-20 | Kwiatowa 8, Poznań | 1200.00 || Izabela | Kwiatkowska | NULL | NULL | NULL |+---------+-------------+----------------+--------------------+---------+

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. Zapytanie:

SELECT imie, nazwisko FROM pracownicy WHERE nazwisko LIKE 'Kowalsk_';

spowoduje wyświetlenie wszystkich pracowników, których nazwisko zaczyna się ciągiem znaków, ‘Kowalsk' i zaraz po nim występuje jeden dowolny znak:

+-----------+----------+| imie | nazwisko |+-----------+----------+| Jan | Kowalski || Katarzyna | Kowalska |+-----------+----------+

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śli zastosujesz 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';

spowodujesz wyświetlenie w wyniku wszystkich pracowników, których płaca mieści się w zakresie 500-1000 oraz pracowników o nazwisku 'Kowalski':

+--------+--------------+----------------+-------------------------+---------+| imie | nazwisko | data_urodzenia | adres | placa |+--------+--------------+----------------+-------------------------+---------+| Jan | Kowalski | 2002-07-20 | Kwiatowa 8, Poznań | 1200.00 || Aniela | Michałkowska | 1970-05-23 | al. Kwiatowa 15, Kraków | 854.29 |+--------+--------------+----------------+-------------------------+---------+

Dane w tabeli mogą być przechowywane w dowolnej kolejności. Możesz 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 wpiszesz 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:

+------------+--------------+----------------+-------------------------------+---------+| imie | nazwisko | data_urodzenia | adres | placa |+------------+--------------+----------------+-------------------------------+---------+| Aleksander | Borowiecki | 1952-08-06 | ul. Wycieczkowa 8/12, Wrocław | 1500.34 || Katarzyna | Kowalska | 2002-07-02 | Kwiatowa 8, Poznań | 1200.00 || Jan | Kowalski | 2002-07-20 | Kwiatowa 8, Poznań | 1200.00 || Izabela | Kwiatkowska | NULL | NULL | NULL || Aniela | Michałkowska | 1970-05-23 | al. Kwiatowa 15, Kraków | 854.29 |+------------+--------------+----------------+-------------------------------+---------+

Używając klauzuli LIMIT spowodujesz wyświetlenie jedynie części rekordów. Aby pobrać dwa pierwsze rekordy napisz:

SELECT * FROM pracownicy LIMIT 2;

W wyniku otrzymasz:

+---------+-------------+----------------+--------------------+---------+| imie | nazwisko | data_urodzenia | adres | placa |+---------+-------------+----------------+--------------------+---------+| Jan | Kowalski | 2002-07-20 | Kwiatowa 8, Poznań | 1200.00 || Izabela | Kwiatkowska | NULL | NULL | NULL |+---------+-------------+----------------+--------------------+---------+

Wypełniając pole ofset wyświetlisz podaną ilość rekordów od pewnego miejsca w tabeli. Chcąc pobrać rekordy od 3 do 7 napisz następująco:

SELECT * FROM pracownicy LIMIT 2, 5;

Spowoduje to wyświetlenie pięciu rekordów (o ile tyle istnieje w bazie) poczynając od rekordu trzeciego:

+------------+--------------+----------------+-------------------------------+---------+| imie | nazwisko | data_urodzenia | adres | placa |+------------+--------------+----------------+-------------------------------+---------+| Aleksander | Borowiecki | 1952-08-06 | ul. Wycieczkowa 8/12, Wrocław | 1500.34 || Aniela | Michałkowska | 1970-05-23 | al. Kwiatowa 15, Kraków | 854.29 || Katarzyna | Kowalska | 2002-07-02 | Kwiatowa 8, Poznań | 1200.00 |+------------+--------------+----------------+-------------------------------+---------+

MySQL pozwala na połączenie ze sobą wielu opcji, dzięki którym można bardzo dokładnie zawęzić poszukiwaną ilość informacji. Przykład bardziej złożonego zapytania można przedstawić następująco:

SELECT imie, nazwisko, placa FROM pracownicy WHERE placa >= 500 AND placa < 1200 ORDER BY nazwisko LIMIT 5;

Powyższe zapytanie spowoduje wygenerowanie następującego zestawienia:

+--------+--------------+--------+| imie | nazwisko | placa |+--------+--------------+--------+| Aniela | Michałkowska | 854.29 |+--------+--------------+--------+

Jak widać połączono w tym momencie warunek wyboru według płacy pomiędzy 500 a 1200 z sortowaniem danych według nazwiska i ograniczeniem wyniku tylko do pięciu pierwszych rekordów.

W przypadku wielu tabel zapytanie budowane jest w sposób analogiczny jednakże w większości wypadków konieczne jest zbudowanie relacji pomiędzy danymi w tabelach. Jako przykład rozszerzmy wykonaną już specyfikację pracownika dodając informacje i jego zatrudnieniu. W tym celu zmodyfikujmy w pierwszej kolejności tabelę „pracownicy” dodając do niej klucz główny tabeli. W tym celu dodajmy najpierw dodatkową kolumnę na której położony zostanie klucz główny tabeli,

ALTER TABLE pracownicy ADD COLUMN id INT FIRST ;

a następnie zmodyfikujmy tę kolunmę przypisując jej wartość klucza głównego oraz możliwość autoinkrementacji.

ALTER TABLE pracownicy MODIFY id INT AUTO_INCREMENT PRIMARY KEY;

Sprawdzenie dokonanych zmian uzyskamy poleceniem:

DESC pracownicy;

jego wynik pokazany jest poniżej:+----------------+---------------+------+-----+------------+----------------+| Field | Type | Null | Key | Default | Extra |+----------------+---------------+------+-----+------------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || imie | varchar(30) | YES | | NULL | || nazwisko | varchar(30) | YES | | NULL | || data_urodzenia | date | YES | | 1950-01-01 | || adres | varchar(255) | YES | | NULL | || placa | decimal(10,2) | YES | | 1000.00 | |+----------------+---------------+------+-----+------------+----------------+natomiast sprawdzenia zawartości poprzez polecenie select:

SELECT * FROM pracownicy;

+----+------------+--------------+----------------+-------------------------------+---------+| id | imie | nazwisko | data_urodzenia | adres | placa |+----+------------+--------------+----------------+-------------------------------+---------+| 1 | Jan | Kowalski | 2002-07-20 | Kwiatowa 8, Pozna˝ | 1200.00 || 2 | Aleksander | Borowiecki | 1952-08-06 | ul. Wycieczkowa 8/12, Wrocław | 1500.34 || 3 | Aniela | Michalkowska | 1970-05-23 | al. Kwiatowa 15, Kraków | 854.29 || 4 | Katarzyna | Kowalska | 2002-07-02 | Kwiatowa 8, Pozna˝ | 1200.00 || 5 | Maria | Wroclawska | 2002-07-12 | Rycerska 15, Koło | 1100.00 |+----+------------+--------------+----------------+-------------------------------+---------+Należy zwrócić uwagę na to iż baza automatycznie przypisała wartości w kolumnie klucza głównego.

W kolejnym kroku dodajmy do naszego przykładu dwie tabele związane z możliwością przechowywania informacji o zatrudnieniu. Po pierwsze tabela „zaklady” opisana nastęująco:

CREATE TABLE zaklady ( id INT, nazwa VARCHAR(30), PRIMARY KEY (id));

Przykładowe dane można do niej dodać poprzez:

INSERT INTO zaklady VALUES (1,'piekarnia');INSERT INTO zaklady VALUES (2,'sklep miesny');INSERT INTO zaklady VALUES (3,'kiosk Ruchu');INSERT INTO zaklady VALUES (4,'zakład pogrzebowy');INSERT INTO zaklady VALUES (5,'bar Pokusa');

Kolejna tabela pozwoli nam na wykonanie powiązania pomiędzy pracownikami oraz zakładami pracy:

CREATE TABLE pracownicy_zaklady ( id_pracownicy INT, id_zaklady INT

);

Jak widać zawiera ona jedynie klucze podstawowe tabel pracownicy oraz zakłady. Modeluje ona relację w której jedna osobo może być zatrudniona w kilku zakładach pracy oraz jeden zakład może zatrudniać wiele osób czyli tzw. relacja wielo-wieloznaczna. Przykładowe powiązania mogą mieć postać:

INSERT INTO pracownicy_zaklady VALUES (1,2);INSERT INTO pracownicy_zaklady VALUES (2,1);INSERT INTO pracownicy_zaklady VALUES (3,3);INSERT INTO pracownicy_zaklady VALUES (4,5);INSERT INTO pracownicy_zaklady VALUES (4,1);INSERT INTO pracownicy_zaklady VALUES (5,3);

Dla tak przechowywanych danych postawmy pytanie o to gdzie pracują poszczególne osoby. Zapytanie takie może mieć następującą postać:

SELECT imie, nazwisko, nazwaFROM pracownicy AS p, zaklady AS z, pracownicy_zaklady AS pzWHERE p.id=pz.id_pracownicy AND z.id=pz.id_zaklady;

Podobnie jeśli interesuje nas pytanie o osoby pracujące w kiosku wtedy zapytanie zostanie zmodyfikowane następująco:

SELECT p.id, imie, nazwisko, nazwaFROM pracownicy AS p, zaklady AS z, pracownicy_zaklady AS pzWHERE p.id=pz.id_pracownicy AND z.id=pz.id_zaklady AND nazwa = "kiosk";

+----+--------+--------------+-------+| id | imie | nazwisko | nazwa |+----+--------+--------------+-------+| 3 | Aniela | Michalkowska | kiosk || 5 | Maria | Wroclawska | kiosk |+----+--------+--------------+-------+W przedstawionym powyżej zapytaniu wypisany został dodatkowo identyfikator id z tabeli pracownicy. Ponieważ w tabeli zaklady istnieje pole o identycznej nazwie konieczne było uzycie dodatkowo nazwy tabeli z której pole powinno zostać pobrane. Ponieważ w klauzuli FROM podano aliasy dla poszczególnych tabel wystarczające jest odwołanie się do konkretnego pola poprzez alias.

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 wyspecyfikujesz 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;

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 miejsce specyfikacja_struktury podajesz dokładną operację, jaką chcesz 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. Aby zmienić typ jednego konkretnego pola użyj polecenia:

ALTER TABLE pracownicy MODIFY rozmiar_buta INT;

Aby usunąć konkretne pole ze struktury tabeli należy użyć polecenia:

ALTER TABLE pracownicy DROP rozmiar_buta;

Zmiana nazwy tabeli przedstawia się następująco:

ALTER TABLE pracownicy RENAME zatrudnieni;

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.Indeksy, w dużym uproszczeniu, powodują przyśpieszenie operacji wyszukiwania wykonywanych na bardzo dużych tabelach.

Uwaga: Jeśli Twoje tabele mają więcej niż 100 rekordów zastanów się nad założeniem odpowiednich indeksów. 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.

Indeks należy zakładać dla pól, według których najczęściej jest wykonywane wyszukiwanie. Modyfikacja 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

Usunięcie indeksu odbywa się za pomocą polecenia:

ALTER TABLE DROP INDEX nazwisko_idx;

Opis narzędzia phpMyAdmin

phpMyAdmin jest narzędziem, pozwalającym zdalnie zarządzać bazą SQL. Program ten zapewnia realizację wszystkich podstawowych funkcji: tworzenie i usuwanie tabel, umieszczanie rekordów (także z przygotowanego wcześniej pliku) oraz administracja bazą. phpMyAdmin jest jedną z kilku metod zarządzania bazą - dostęp do niej uzyskasz także za pomocą dowolnego innego skryptu lub też ODBC. phpMyAdmin dostępny jest po wpisaniu w przeglądarce WWW adresu http://149.156.115.209/phpmyadmin. Po wpisaniu adresu zostaniesz poproszony o autoryzację tzn. podanie nazwy oraz hasła dostępu do bazy. Użytkownik: sNrlegitymacji Hasło: Nazwisko

Kliknięcie na nazwie bazy w lewej kolumnie spowoduje przejście do trybu edycji.

Tabela to zbiór rekordów, czyli danych. Każda tabela jest definiowalna w dowolny sposób tzn. może zawierać pola o różnej długości, typie (formacie umieszczanych danych). Aby utworzyć tabelę za pomocą phpMyAdmin: 1. Na ekranie głównym wybierz opcję Utworzenie nowej tabeli dla bazy danych, wpisz nazwę tabeli oraz określ ilość pól, a następnie kliknij na Wykonaj.

2. Zdefiniuj poszczególne pola (kolumny tabeli) wpisując ich nazwy, ustalając typy oraz

atrybuty.

3. Kliknij na Zapamiętaj. Stworzona w ten sposób tabela pojawi się w lewej kolumnie, po kliknięciu na jej nazwie uzyskasz możliwość wykonania dalszych operacji (np. wprowadzenia danych).

Za pomocą phpMyAdmin możesz wykonywać wszystkie popularne operacje na tabelach: umieszczać dane, przeglądać je, kopiować, eksportować i zarządzać. Poniżej opiszemy krótko sposób realizacji najważniejszych funkcji. Każda operacja na tabeli jest wykonywana po wybraniu z lewego okna nazwy tabeli, którą

chcesz edytować.

W górnej części prawego okna znajdują się następujące opcje:

• Przeglądaj - umożliwia przejrzenie rekordów tabeli, ekran ze spisem dzieli rekordy na określone porcje,

• Wybór - konstruuje zapytanie, które zaprezentuje rekordy spełniające warunek (np. pole x zawiera ciąg Warszawa),

• Dodaj - powoduje dodanie do tabeli rekordu, • Wyczyść - kasuje wszystkie dane z tabeli, • Usuń - usuwa tabelę i dane.

W prezentowanej powyżej części możesz przeprowadzić edycję podstawowych atrybutów poszczególnych pól tabelki lub też skasować je.

Wykonanie zapytania/zapytań SQL do bazy umożliwia wprowadzenie tekstowej komendy wykonującej stosowne operacje na bazie. Wybranie opcji Lokalizacja pliku tekstowego spowoduje, że zostaną wykonane polecenia zapisane we wskazanym pliku. Wydanie polecenia jest możliwe tylko w sytuacji, gdy znasz język SQL.

Dodanie nowego pola umożliwia poszerzenie tabeli o kolejne kolumny - wystarczy, że

ustalisz ilość nowych pól oraz ich umiejscowienie w strukturze tabeli. Sortowanie tabeli powoduje uporządkowanie jej według zadanego kryterium/kolumny.

Kolejne opcje: • Zmiana nazwy tabeli, • Przenieś tabelę do... - spowoduje skopiowanie struktury i danych umieszczonych w

tabeli do wskazanej innej tabeli, źródło zostanie usunięte, • Kopiuj tabelę do... - jw., bez usunięcia źródła, • Zarządzanie tabelą - zestaw opcji umożliwiających optymalizację, analizę i naprawę

tabeli, • Komentarze tabeli - umożliwia dodanie komentarza/opisu do aktualnej tabeli, • Usunięcie tabeli.

W przypadku większych baz danych może czasami okazać się, że korzystniej i szybciej jest przygotować dane w pliku tekstowym (np. wygenerowanym z zewnętrznego programu), a następnie zaimportować go do bazy. Aby zaimportować dane z zewnętrznego pliku w phpMyAdmin: 1. W lewym oknie kliknij na nazwę tabeli, do której dane chcesz zaimportować. 2. W prawym oknie użyj opcji Dodanie pliku tekstowego do tabeli. 3. Zostaniesz poproszony o podanie kilku informacji o importowanym pliku, w szczególności o jego lokalizacji i formacie.

4. Kliknij na Wysłanie. Jeżeli w tabeli znajdują się już dane, możesz wyeksportować je np. do użytku na innym serwerze lub lokalnym komputerze. Aby wyeksportować dane z tabeli w phpMyAdmin: 1. W lewym oknie kliknij na nazwę tabeli, z której dane chcesz wyeksportować. 2. W prawym oknie przejdź do sekcji Zrzut tabeli.

3. Wybierz interesującą Cię formę zapisu pliku z danymi: • Tylko struktura - spowoduje wygenerowanie zapytania SQL, które tworzy tylko tabelę, • Struktura i dane - jw. oraz zapytania umieszczające w tabelach dane, • Tylko dane - w zapytaniu SQL znajdą się tylko komendy umieszczające dane, bez

struktury bazy, • CSV dla Excel - zrzut danych w formacie rozpoznawanym przez popularny arkusz

kalkulacyjny firmy Microsoft, • dane CSV - zrzut danych w formacie ustalonym parametrami.

Opcje dodatkowe: Dodanie 'drop table' - na początku zrzutu dodaje komendę kasującą tabelę. Jest to przydatne w przypadku nadpisywania tabeli całkowicie nowymi danymi, Pełne dodania - w zapytaniach SQL dodaje także nazwy pól, które dla danego rekordu pozostają puste, Rozszerzone dodania - jw. do zaawansowanych zastosowań, Użycie cudzysłowów z nazwami pól i tabel - w zapytaniach SQL umieszcza nazwy pól i tabel w cudzysłowie. W ten sposób użycie "niebezpiecznych" nazw i znaków nie spowoduje kłopotów, Wysłanie jako plik - umożliwia zapis wyeksportowanych danych w postaci pliku, Rozpoczęcie rekordu - umożliwia ustalenie, jaki zakres danych ma zostać wyeksportowany. Po zakończeniu pracy, z phpMyAdmin niezbędne jest wylogowanie się, aby żadna postronna osoba nie miała dostępu do bazy. Aby zakończyć pracę z phpMyAdmin: 1. Kliknij na Wejście w lewym oknie.

2. W prawym oknie w sekcji MySQL kliknij na Wylogowanie.

Połączenie z baza danych z poziomu PHP

Połączenie z bazą MySQL można uzyskać z poziomu skryptów PHP lub z konsoli serwera.. W celu połączenia się z serwerem baz danych ze skryptu PHP należy podać jego nazwę (adres domenowy lub adres IP), nazwę użytkownika oraz hasło. 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żesz przystąpić do wydawania poleceń języka SQL. Funkcję PHP wysyłającą zapytanie SQL do serwera wywołuje się następująco:

mysql_query("zapytanie_SQL");

Po zakończonej pracy z bazą danych należy użyć funkcji:

mysql_close($db);

Zadania do wykonania

a) Uruchomić narzędzie phpMyAdmin (http://149.156.115.209/phpmyadmin/) a następnie przy jego pomocy utworzyć dwie tabele pozwalające na definiowanie systemu kontaków do wybranych osób. Pierwsza tabela może mieć nazwę „osoby” oraz zawierać:

- pole przechowujące liczbę ID rekordu ( pole wymagane, liczba całkowita nadawana automatycznie)

- pole przechowujące imię dowolnej osoby ( maksymalnie 30 znaków)

- pole zawierające nazwisko osoby ( maksymalnie 100 znaków)

- pole na datę urodzenia osoby

- pole zawierające informację o wieku osoby

Kluczem podstawowym tabeli ma być pole ID.

Utworzyć drugą tabelę „kontakty” zawierającą:

- pole przechowujące liczbę ID rekordu ( pole wymagane, liczba całkowita nadawana automatycznie) - pole ID_osoby – identyfikujące osobę w tabeli osoby- pole odnoszące się do rodzaju kontaktu (np.: email, telefon stacjonarny, tel.

komórkowy, nr komunikatora itp....)

- pole definujące kontakt ( maksymalnie 100 znaków)

Tabela powinna mieć klucz złożony z pól ID oraz ID_osoby

b) Korzystając z phpMyAdmin’a lub skryptów dodać co najmniej 5 rekordów zawierających dane (np. kolegów z grupy) do tabeli „osoby” oraz co najmniej po dwa wpisy dla każdej osoby w tabeli „kontakty”.

c) Wyszukać w przygotowanym zestawie danych:

– rekordy osób, których imię zaczyna się na wybraną literę alfabetu

– policzyć dostępne w bazie kontakty jednego wybranego rodzaju

– wybrać z danych wszystkie kontakty dla wybranej osoby

– wyszukać w zestawieniu kontaktów zadany ciąg znaków (np. „12617”) oraz określić osobę lub osoby związane z danym kontaktem.

d) Wyeksportować całą bazę danych do pliku w formacie *.sql, *.zip, *.xml.

e) Otworzyć plik *.xml za pomocą dostępnych narzędzi.

f) Wyczyścić bazę danych z wpisów, a następnie dokonać importu danych z pliku *.sql.

g) Utworzyć projekt własnej bazy danych dla wybranego tematu.

Wyniki wykonania wszystkich zadań należy umieścić w sprawozdaniu w postaci zrzutów ekranowych (podobnie jak w tym dokumencie).