1. administracja baz danych mysql

WPROWADZENIE DO ADMINISTRACJI BAZAMI DANYCH MYSQL

opracowanie © grzegorz ciągło

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 potrzebujesz informacji z bazy danych, musisz "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. Instalacja MySQL Pobieramy program instalacyjny ze strony: http://dev.mysql.com/downloads/mysql/ Do ćwiczeń warto wykorzystać wersję lżejszą wersję essential. Po uruchomieniu instalatora wybieramy instalację typu custom i zmieniamy location na c:\mysql co ułatwi nam dostęp do bazy z linii poleceń. Korzystając z kreatora instalacji serwera (Server Instance Configuration Wizard) wybieramy opcje Detailed Configuration >Developer Machine > Multifunctional Database i kończymy pracę z kreatorem korzystając z ustawień domyślnych.

1

WPROWADZENIE DO ADMINISTRACJI BAZAMI DANYCH MYSQL

opracowanie © grzegorz ciągło

Połączenie z bazą danych MySQL Aby połączyć się z systemem baz należy uruchomić „Command line client” lub z linii poleceń wywołać zainstalowany w podfolderze c:\mysql\bin plik mysql.exe z odpowiednimi parametrami: mysql –u root –p Gdzie root to nazwa użytkownika bazy, kolejno program poprosi nas o podanie hasła do bazy. Możemy je zmienić poleceniem: use mysql; UPDATE user SET password=PASSWORD('nowe_haslo') WHERE user='root'; Gdzie: nowe_haslo - to nasze nowe hasło root - nazwa usera któremu zmieniamy hasło Po poprawnym połączeniu się z bazą danych możesz przystąpić do wydawania poleceń języka SQL np.: show databases; pokazuje istniejące bazy create database firma; tworzy bazę danych o nazwie „Firma” use firma; otwiera bazę o nazwie „Firma” Po zakończonej pracy z bazą danych należy użyć komendy: exit Tworzenie tabel 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. 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) );

2

WPROWADZENIE DO ADMINISTRACJI BAZAMI DANYCH MYSQL

opracowanie © grzegorz ciągło

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 | | +----------------+---------------+------+-----+---------+-------+ Możemy to sprawdzić wydając polecenie: DESCRIBE pracownicy; Lub show full columns from pracownicy; 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ę poniżej. 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 | | +----------------+---------------+------+-----+------------+-------+ Sprawdź to! Typy danych w MySQL

Typ danych

Opis

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

3

WPROWADZENIE DO ADMINISTRACJI BAZAMI DANYCH MYSQL

opracowanie © grzegorz ciągło

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

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 daty w różnych formatach z zakresu od 1000-01-01 do 9999-12-31

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

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

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

4

WPROWADZENIE DO ADMINISTRACJI BAZAMI DANYCH MYSQL

opracowanie © grzegorz ciągło

Wstawianie danych do tabeli 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. select * from pracownicy To wyświetli wszystkie rekordy wprowadzone do tabeli 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. 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 polcenie 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 | +-------+--------------+----------------+-------------------------------+---------+

5

WPROWADZENIE DO ADMINISTRACJI BAZAMI DANYCH MYSQL

opracowanie © grzegorz ciągło

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'; 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ć 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.

6

WPROWADZENIE DO ADMINISTRACJI BAZAMI DANYCH MYSQL

opracowanie © grzegorz ciągło

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

7

WPROWADZENIE DO ADMINISTRACJI BAZAMI DANYCH MYSQL

opracowanie © grzegorz ciągło

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

8

WPROWADZENIE DO ADMINISTRACJI BAZAMI DANYCH MYSQL

opracowanie © grzegorz ciągło

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

9

WPROWADZENIE DO ADMINISTRACJI BAZAMI DANYCH MYSQL

opracowanie © grzegorz ciągło

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

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.

10

WPROWADZENIE DO ADMINISTRACJI BAZAMI DANYCH MYSQL

opracowanie © grzegorz ciągło

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; ZADANIA ADMINISTRACYJNE

Teraz samodzielnie opracuj tabelę „klienci” zawierającą dane osobowe klientów oraz pole wydatki i wprowadź do niej podstawowe dane. Wyświetl nazwiska pracowników zarabiających mniej niż 1500 zł i klientów, którzy wydali więcej niż 1000 zł. Zgłoś nauczycielowi wykonanie zadania!

Tworzenie nowego użytkownika Powyższą czynność wykonamy przy użyciu polecenia GRANT, którego składnia jest następująca: GRANT lista_praw_dostępu ON nazwa_tabeli TO nazwa_uzytkownika Pole „lista_praw_dostępu” może zawierać jedno, lub kilka z poniższych uprawnień: • • • • • • • •

SELECT– odczytanie danych z tabeli INSERT – wstawianie danych do tabeli UPDATE – modyfikowanie danych w tabeli DELETE – usunięcie danych z tabeli REFERENCE – odwoływanie się do innych tabel CREATE – tworzenie nowych tabel i baz danych DROP – usuwanie tabel oraz baz danych ALL PRIVILEGES – wszystkie dostępne uprawnienia

Pole „nazwa tabeli” określa tabelę do której przyznajemy uprawnienia użytkownikowi wskazanemu w „nazwa użytkownika”. Utworzymy użytkownika o nazwie „pracownik” któremu nadamy prawa tylko do odczytu, wstawiania oraz modyfikowania danych, a więc SELECT, INSERT, UPDATE dla tabeli o nazwie „klienci”. CREATE USER 'nazwa_uzytkownika’@'localhost' IDENTIFIED BY 'jakies_haslo'; GRANT SELECT, INSERT, UPDATE ON klienci TO pracownik;

11

WPROWADZENIE DO ADMINISTRACJI BAZAMI DANYCH MYSQL

opracowanie © grzegorz ciągło

Tworzymy użytkownika o nazwie „szef” któremu nadajemy wszelkie możliwe uprawnienia dla tabeli „klienci”. GRANT ALL PRIVILEGES ON klienci TO szef; SQL umożliwia także odbieranie wcześniej nadanych uprawnień, a służy do tego polecenie REVOKE, którego składnia jest następująca: REVOKE lista_praw_dostępu ON nazwa_tabeli FROM nazwa_uzytkownika Stwierdziliśmy, iż użytkownik „pracownik” nie powinien mieć możliwości wstawiania nowych danych do tabeli „klienci”. Należy więc usunąć mu prawo używania polecenia INSERT. REVOKE INSERT ON klienci FROM pracownik Import oraz Export bazy MySQL (uwaga to są polecenia zewnętrzne i wykonujemy je po zakończonej pracy z bazą danych i użyciu komendy: exit ) Jeśli chcemy zrobić kopię jednej bazy danych piszemy polecenie: mysqldump -u user -p --opt nazwa_bazy > nazwa_pliku_backup.sql Jeśli chcemy zrobić kopię jednej tabelki w danej bazie: mysqldump -u user -p --opt nazwa_bazy nazwa_tabeli > nazwa_pliku_backup.sql Jeśli chcemy wykonać backup wszystkich baz danych, do których ma dostęp użytkownik piszemy: mysqldump -u user -p --opt --all-databases > nazwa_pliku_backup.sql Żeby przywrócić dane z backapów jak powyżej należy wykonać polecenie. mysql -u user -p

-h localhost nazwa_bazy < nazwa_pliku_backup.sql

12