Darmowy fragment publikacji:
IDZ DO
IDZ DO
PRZYK£ADOWY ROZDZIA£
PRZYK£ADOWY ROZDZIA£
SPIS TREŒCI
SPIS TREŒCI
KATALOG KSI¥¯EK
KATALOG KSI¥¯EK
KATALOG ONLINE
KATALOG ONLINE
ZAMÓW DRUKOWANY KATALOG
ZAMÓW DRUKOWANY KATALOG
TWÓJ KOSZYK
TWÓJ KOSZYK
DODAJ DO KOSZYKA
DODAJ DO KOSZYKA
CENNIK I INFORMACJE
CENNIK I INFORMACJE
ZAMÓW INFORMACJE
ZAMÓW INFORMACJE
O NOWOŒCIACH
O NOWOŒCIACH
ZAMÓW CENNIK
ZAMÓW CENNIK
CZYTELNIA
CZYTELNIA
FRAGMENTY KSI¥¯EK ONLINE
FRAGMENTY KSI¥¯EK ONLINE
Wydawnictwo Helion
ul. Chopina 6
44-100 Gliwice
tel. (32)230-98-63
e-mail: helion@helion.pl
MySQL. Almanach
Autor: Russell J. T. Dyer
T³umaczenie: Rafa³ Joñca
ISBN: 83-246-0130-9
Tytu³ orygina³u: MySQL in a Nutshell
Format: B5, stron: 294
Przewodnik po najpopularniejszej dostêpnej nieodp³atnie bazie danych
(cid:129) Przegl¹d instrukcji jêzyka SQL
(cid:129) Polecenia klienta i serwera MySQL
(cid:129) Funkcje interfejsów programistycznych
MySQL to stabilny, wydajny i szybki system zarz¹dzania bazami danych dostêpny
nieodp³atnie, na licencji open source. Najczêœciej stosowany jest jako zaplecze
bazodanowe witryn WWW, ale coraz czêœciej siêgaj¹ po niego twórcy rozbudowanych
aplikacji, którzy do niedawna wykorzystywali drogie, komercyjne bazy danych. MySQL
posiada spore mo¿liwoœci, a administracja nim nie nastrêcza wiêkszych problemów,
dziêki wielu narzêdziom tekstowym i graficznym u³atwiaj¹cym pracê z tym systemem.
Dostêpnoœæ wielu interfejsów programistycznych (API) bardzo u³atwia tworzenie
aplikacji opartych na MySQL.
Ksi¹¿ka „MySQL. Almanach” to podrêcznik dla u¿ytkowników, administratorów
i programistów korzystaj¹cych z bazy danych MySQL. Zawiera opisy instrukcji
i funkcji MySQL, narzêdzi administracyjnych i najpopularniejszych interfejsów
programistycznych. Przedstawia proces instalacji bazy i tworzenia nowych tabel,
sposoby konstruowania efektywnych zapytañ oraz sk³adniê i parametry poleceñ
stosowanych w pracy z tekstowymi narzêdziami klienckimi i administracyjnymi.
(cid:129) Instalacja MySQL w ró¿nych systemach operacyjnych
(cid:129) Tworzenie baz i tabel
(cid:129) Wprowadzanie danych i import z plików tekstowych
(cid:129) Wybieranie danych
(cid:129) Instrukcje i funkcje jêzyka SQL
(cid:129) Operacje na liczbach, tekstach i datach
(cid:129) Narzêdzia dostêpne z wiersza poleceñ
(cid:129) Funkcje API dla Perla, PHP i C
Dziêki wiadomoœciom zawartym w tej ksi¹¿ce praca z MySQL stanie siê
bardziej wydajna.
Spis treści
Przedmowa ...................................................................................................................19
1. Wprowadzenie do MySQL .......................................................................................... 23
23
24
25
25
26
Wartość MySQL
Pakiet MySQL
Licencje
Listy mailingowe
Książki i inne publikacje
2.
Instalacja MySQL ......................................................................................................... 27
27
Wybór dystrybucji
Dystrybucje źródłowe dla systemów uniksowych
28
30
Dystrybucje binarne dla systemów uniksowych
31
Dystrybucje RPM systemu Linux
32
Dystrybucje dla systemu Mac OS X
Dystrybucje dla systemu Novell NetWare
32
33
Dystrybucje dla systemu Windows
Zadania po instalacji
35
3. Podstawy MySQL ........................................................................................................ 37
37
38
40
41
41
43
44
45
47
48
48
50
51
Klient mysql
Tworzenie bazy danych i tabel
Pokaż mi
Wstawianie danych
Pobieranie danych
Kolejność, limitowanie wyników i grupowanie
Analiza i manipulacja danymi
Modyfikacja danych
Usuwanie danych
Wyszukiwanie danych
Hurtowy import danych
Interfejs wiersza poleceń
Podsumowanie
5
4.
Instrukcje SQL ..............................................................................................................53
Polecenia pogrupowane według typu
53
54
Polecenia i klauzule w kolejności alfabetycznej
55
ALTER DATABASE
55
ALTER TABLE
ALTER VIEW
59
59
ANALYZE TABLE
60
BACKUP TABLE
60
CACHE INDEX
CHANGE MASTER TO
61
62
CHECK TABLE
63
CHECKSUM TABLE
63
COMMIT
CREATE DATABASE
64
64
CREATE INDEX
65
CREATE TABLE
70
CREATE VIEW
DELETE
71
72
DESCRIBE
73
DO
73
DROP DATABASE
DROP INDEX
73
74
DROP TABLE
74
DROP USER
75
DROP VIEW
EXPLAIN
75
75
FLUSH
76
GRANT
78
HANDLER
INSERT
80
83
JOIN
85
KILL
85
LOAD DATA FROM MASTER
LOAD DATA INFILE
86
87
LOAD INDEX INTO CACHE
87
LOAD TABLE … FROM MASTER
LOCK TABLES
88
89
OPTIMIZE TABLE
89
PURGE MASTER LOGS
89
RENAME TABLE
REPAIR TABLE
90
91
REPLACE
92
RESET
RESET MASTER
92
6
|
Spis treści
RESET SLAVE
RESTORE TABLE
REVOKE
ROLLBACK
ROLLBACK TO SAVEPOINT
SAVEPOINT
SELECT
SET
SET PASSWORD
SET SQL_LOG_BIN
SET TRANSACTION
SHOW BINLOG EVENTS
SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW CREATE DATABASE
SHOW CREATE TABLE
SHOW CREATE VIEW
SHOW DATABASES
SHOW ENGINES
SHOW ERRORS
SHOW GRANTS
SHOW INDEX
SHOW INNODB STATUS
SHOW LOGS
SHOW MASTER LOGS
SHOW MASTER STATUS
SHOW PRIVILEGES
SHOW PROCESSLIST
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW VARIABLES
SHOW WARNINGS
START SLAVE
START TRANSACTION
STOP SLAVE
TRUNCATE TABLE
UNION
UNLOCK TABLES
USE
92
93
93
94
94
94
95
101
102
102
102
103
104
104
105
105
106
106
106
107
107
107
108
108
109
109
109
109
109
110
110
111
111
112
112
113
113
114
114
114
115
115
115
Spis treści
|
7
5. Funkcje tekstów .......................................................................................................... 117
117
118
118
118
118
119
119
120
120
120
121
121
121
121
122
122
123
123
123
124
124
124
125
125
125
126
126
126
127
127
127
127
128
128
129
129
129
129
130
130
131
131
131
Funkcje tekstów pogrupowane według typu
Funkcje tekstów w kolejności alfabetycznej
AES_DECRYPT()
AES_ENCRYPT()
ASCII()
BIN()
BINARY
BIT_LENGTH()
CHAR()
CHAR_LENGTH()
CHARACTER_LENGTH()
COMPRESS()
CONCAT()
CONCAT_WS()
CONV()
DECODE()
DES_DECRYPT()
DES_ENCRYPT()
ELT()
ENCODE()
ENCRYPT()
EXPORT_SET()
FIELD()
FIND_IN_SET()
HEX()
INET_ATON()
INET_NTOA()
INSERT()
INSTR()
LCASE()
LEFT()
LENGTH()
LOAD_FILE()
LOCATE()
LOWER()
LPAD()
LTRIM()
MAKE_SET()
MATCH() AGAINST()
MD5()
MID()
OCT()
OCTET_LENGTH()
8
|
Spis treści
OLD_PASSWORD()
ORD()
PASSWORD()
POSITION()
QUOTE()
REPEAT()
REPLACE()
REVERSE()
RIGHT()
RPAD()
RTRIM()
SHA()
SHA1()
SOUNDEX()
SPACE()
STRCMP()
SUBSTRING()
SUBSTRING_INDEX()
TRIM()
UCASE()
UNCOMPRESS()
UNCOMPRESSED_LENGTH()
UNHEX()
UPPER()
131
132
132
132
133
133
133
133
134
134
134
135
135
135
135
136
136
137
137
137
138
138
138
138
6. Funkcje daty i czasu ....................................................................................................139
140
140
140
141
141
142
142
142
142
143
143
144
144
146
146
146
147
147
Funkcje daty i czasu pogrupowane według typu
Funkcje daty i czasu w porządku alfabetycznym
ADDDATE()
ADDTIME()
CONVERT_TZ()
CURDATE()
CURRENT_DATE()
CURRENT_TIME()
CURRENT_TIMESTAMP()
CURTIME()
DATE()
DATE_ADD()
DATE_FORMAT()
DATE_SUB()
DATEDIFF()
DAY()
DAYNAME()
DAYOFMONTH()
Spis treści
|
9
DAYOFWEEK()
DAYOFYEAR()
EXTRACT()
FROM_DAYS()
FROM_UNIXTIME()
GET_FORMAT()
HOUR()
LAST_DAY()
LOCALTIME()
LOCALTIMESTAMP()
MAKEDATE()
MAKETIME()
MICROSECOND()
MINUTE()
MONTH()
MONTHNAME()
NOW()
PERIOD_ADD()
PERIOD_DIFF()
QUARTER()
SEC_TO_TIME()
SECOND()
STR_TO_DATE()
SUBDATE()
SUBTIME()
SYSDATE()
TIME()
TIMEDIFF()
TIMESTAMP()
TIMESTAMPDIFF()
TIMESTAMPADD()
TIME_FORMAT()
TIME_TO_SEC()
TO_DAYS()
UNIX_TIMESTAMP()
UTC_DATE()
UTC_TIME()
UTC_TIMESTAMP()
WEEK()
WEEKDAY()
WEEKOFYEAR()
YEAR()
YEARWEEK()
10
|
Spis treści
147
148
148
148
149
149
150
151
151
151
152
152
152
153
153
153
154
154
154
155
156
156
157
157
157
158
158
159
159
159
160
160
160
161
161
161
162
162
163
163
163
164
164
7. Funkcje matematyczne i agregujące .........................................................................165
165
165
165
166
166
166
167
167
167
167
167
168
168
168
168
169
169
169
169
170
170
170
171
171
172
172
172
172
172
173
173
173
174
174
174
174
175
175
175
175
176
176
176
Funkcje w kolejności alfabetycznej
ABS()
ACOS()
ASIN()
ATAN()
ATAN2()
AVG()
BIT_AND()
BIT_OR()
BIT_XOR()
CEIL()
CEILING()
COS()
COT()
COUNT()
CRC32()
DEGREES()
EXP()
FLOOR()
FORMAT()
GREATEST()
GROUP_CONCAT()
LEAST()
LN()
LOG()
LOG2()
LOG10()
MAX()
MIN()
MOD()
PI()
POW()
POWER()
RADIANS()
RAND()
ROUND()
SIGN()
SIN()
SQRT()
STD()
STDDEV()
STD()
TAN()
Spis treści
|
11
TRUNCATE()
VARIANCE()
176
177
8. Funkcje sterowania przepływem ..............................................................................179
179
179
180
181
181
Funkcje w kolejności alfabetycznej
CASE()
IF()
IFNULL()
NULLIF()
9. Pozostałe funkcje ...................................................................................................... 183
183
183
184
184
185
185
185
185
186
186
186
186
187
187
187
188
188
188
189
189
189
190
190
190
190
191
191
Funkcje w kolejności alfabetycznej
ANALYSE()
BENCHMARK()
BIT_COUNT()
CAST()
CHARSET()
COALESCE()
COERCIBILITY()
COLLATION()
CONNECTION_ID()
CONVERT()
CURRENT_USER()
DATABASE()
FOUND_ROWS()
GET_LOCK()
INTERVAL()
IS_FREE_LOCK()
IS_USED_LOCK()
ISNULL()
LAST_INSERT_ID()
MASTER_POS_WAIT()
RELEASE_LOCK()
SESSION_USER()
SYSTEM_USER()
USER()
UUID()
VERSION()
10. Serwer i klient MySQL ................................................................................................193
193
195
201
202
mysql
mysqld
mysqld_multi
mysqld_safe
12
|
Spis treści
11. Narzędzia wiersza poleceń .......................................................................................205
205
206
206
206
206
207
210
211
212
214
217
218
218
220
224
225
226
229
230
comp_err
isamchk
make_binary_distribution
msql2mysql
my_print_defaults
myisamchk
myisamlog
myisampack
mysqlaccess
mysqladmin
mysqlbinlog
mysqlbug
mysqlcheck
mysqldump
mysqldumpslow
mysqlhotcopy
mysqlimport
mysqlshow
perror
12.
Interfejs programistyczny dla języka Perl .................................................................231
231
Korzystanie z Perl DBI w celu łączenia z MySQL
235
Omówienie metod i funkcji Perl DBI
235
available_drivers()
begin_work()
236
236
bind_col()
236
bind_columns()
236
bind_param()
bind_param_array()
237
237
bind_param_inout()
237
can()
237
clone()
column_info()
238
238
commit()
238
connect()
238
connect_cached()
data_sources()
238
239
disconnect()
239
do()
dump_results()
239
240
err()
240
errstr()
execute()
240
Spis treści
|
13
execute_array()
execute_for_fetch()
fetch()
fetchall_arrayref()
fetchall_hashref()
fetchrow_array()
fetchrow_arrayref()
fetchrow_hashref()
finish()
foreign_key_info()
func()
get_info()
installed_versions()
last_insert_id()
looks_like_number()
neat()
neat_list()
parse_dsn()
parse_trace_flag()
parse_trace_flags()
ping()
prepare()
prepare_cached()
primary_key()
primary_key_info()
quote()
quote_identifier()
rollback()
rows()
selectall_arrayref()
selectall_hashref()
selectcol_arrayref()
selectrow_array()
selectrow_arrayref()
selectrow_hashref()
set_err()
state()
table_info()
table_info_all()
tables()
trace()
trace_msg()
type_info()
type_info_all()
Atrybuty uchwytów
14
|
Spis treści
241
241
241
241
241
242
242
242
243
243
243
243
243
244
244
244
244
245
245
245
245
245
246
246
246
246
247
247
247
247
248
248
249
249
249
250
250
250
250
250
250
251
251
251
251
13.
Atrybuty dotyczące wszystkich uchwytów
Atrybuty dotyczące jedynie uchwytów baz danych
Atrybuty dotyczące jedynie uchwytów poleceń
Dynamiczne atrybuty DBI
251
253
254
254
Interfejs programistyczny dla języka PHP ................................................................ 255
255
Korzystanie z MySQL w PHP
257
Funkcje PHP związane z MySQL w kolejności alfabetycznej
257
mysql_affected_rows()
mysql_change_user()
258
258
mysql_client_encoding()
258
mysql_close()
259
mysql_connect()
mysql_create_db()
260
260
mysql_data_seek()
261
mysql_db_name()
261
mysql_db_query()
mysql_drop_db()
262
262
mysql_errno()
262
mysql_error()
262
mysql_escape_string()
mysql_fetch_array()
263
263
mysql_fetch_assoc()
264
mysql_fetch_field()
265
mysql_fetch_lengths()
mysql_fetch_object()
265
266
mysql_fetch_row()
266
mysql_field_flags()
267
mysql_field_len()
mysql_field_name()
267
268
mysql_field_seek()
268
mysql_field_table()
269
mysql_field_type()
mysql_free_result()
269
270
mysql_get_client_info()
270
mysql_get_host_info()
270
mysql_get_proto_info()
mysql_get_server_info()
270
271
mysql_info()
271
mysql_insert_id()
271
mysql_list_dbs()
mysql_list_fields()
272
272
mysql_list_processes()
272
mysql_list_tables()
mysql_num_fields()
273
Spis treści
|
15
mysql_num_rows()
mysql_pconnect()
mysql_ping()
mysql_query()
mysql_real_escape_string()
mysql_result()
mysql_select_db()
mysql_stat()
mysql_tablename()
mysql_thread_id()
mysql_unbuffered_query()
273
274
274
274
275
275
275
276
276
276
276
14.
Interfejs programistyczny dla języka C .................................................................... 279
Korzystanie z MySQL z poziomu języka C
279
282
Funkcje w kolejności alfabetycznej
282
mysql_affected_rows()
282
mysql_autocommit()
mysql_change_user()
282
283
mysql_character_set_name()
283
mysql_close()
284
mysql_commit()
mysql_connect()
284
284
mysql_create_db()
285
mysql_data_seek()
285
mysql_debug()
mysql_drop_db()
285
286
mysql_dump_debug_info()
286
mysql_eof()
286
mysql_errno()
mysql_error()
287
287
mysql_escape_string()
288
mysql_fetch_field()
288
mysql_fetch_field_direct()
mysql_fetch_fields()
288
289
mysql_fetch_lengths()
289
mysql_fetch_row()
290
mysql_field_count()
mysql_field_seek()
290
291
mysql_field_tell()
291
mysql_free_result()
291
mysql_get_client_info()
mysql_get_client_version()
292
292
mysql_get_host_info()
292
mysql_get_proto_info()
mysql_get_server_info()
293
16
|
Spis treści
mysql_get_server_version()
mysql_info()
mysql_init()
mysql_insert_id()
mysql_kill()
mysql_list_dbs()
mysql_list_fields()
mysql_list_processes()
mysql_list_tables()
mysql_more_results()
mysql_next_result()
mysql_num_fields()
mysql_num_rows()
mysql_options()
mysql_ping()
mysql_query()
mysql_real_connect()
mysql_real_escape_string()
mysql_real_query()
mysql_reload()
mysql_rollback()
mysql_row_seek()
mysql_row_tell()
mysql_select_db()
mysql_set_server_option()
mysql_shutdown()
mysql_sqlstate()
mysql_stat()
mysql_store_result()
mysql_thread_id()
mysql_thread_safe()
mysql_use_result()
mysql_warning_count()
Typy danych interfejsu programistycznego MySQL dla języka C
293
293
294
294
294
295
295
296
296
296
297
297
297
297
298
299
299
300
301
302
302
302
303
303
303
303
304
304
304
305
305
305
306
306
A Typy danych ...............................................................................................................309
B Operatory ...................................................................................................................313
C Zmienne środowiskowe .............................................................................................317
Skorowidz ...................................................................................................................319
Spis treści
|
17
ROZDZIAŁ 3.
Podstawy MySQL
Choć niniejsza książka zawiera materiał podręcznikowy, który można czytać małymi frag-
mentami w razie potrzeby, w niniejszym rozdziale znajduje się proste ćwiczenie uczące podstaw
MySQL. Informuje, w jaki sposób zalogować się do serwera, utworzyć bazę danych, a także
wpisać i dokonać edycji znajdujących się w niej danych. Trzeba jednak zdawać sobie sprawę
z tego, iż ćwiczenie nie obejmuje wszystkich zagadnień. Stanowi raczej wskazówkę, w jaki
sposób należy wykonywać pewne rodzaje zadań w MySQL.
Klient mysql
Istnieje wiele sposobów interakcji z serwerem MySQL, a tym samym tworzenia i używania
bazy danych. Najprostszym interfejsem jest klient mysql. Dzięki niemu możliwa jest interakcja
przy użyciu wiersza poleceń. Program często nazywany jest monitorem MySQL.
Jeśli serwer MySQL został poprawnie zainstalowany i uruchomiony, mysql powinien włączyć
się bez przeszkód. Jeżeli tak się nie stanie, należy przeprowadzić instalację zgodnie z kroka-
mi opisanymi w rozdziale 2. Jeżeli instalacja została przeprowadzona w sposób domyślny,
program mysql znajduje się w katalogu /usr/local/mysql/bin/. Aby upewnić się, iż znajdzie się
on w ścieżce wyszukiwania, wystarczy wykonać poniższe wiersze:
PATH=$PATH:/usr/local/mysql/bin
export PATH
Zakładając, iż wszystko działa poprawnie, potrzebna jest jeszcze nazwa użytkownika i hasło.
Jeśli nie jest się administratorem, należy uzyskać obie informacje od osoby zarządzającej ser-
werem. Jeżeli serwer MySQL został zainstalowany dopiero przed chwilą, użytkownik root
posiada puste hasło. Sposób ustawiania haseł i tworzenia użytkowników z różnymi prawami
został opisany w rozdziale 2.
Logowanie się do serwera MySQL z poziomu powłoki wygląda następująco.
mysql -h host -u użytkownik -p
Jeśli dokonuje się logowania do lokalnego serwera (mieszczącego się na tym samym kompu-
terze fizycznie lub logicznie, na przykład dzięki połączeniu Telnet lub SSH), można pominąć
argument -h host. Klient domyślnie przyjmuje, iż logowanie dotyczy hosta localhost, który
odnosi się do aktualnego systemu. W przypadku chęci dołączenia do serwera istniejącego na
innym komputerze, trzeba podać jego nazwę, którą można przełożyć na adres IP, lub bezpo-
średnio wpisać adres IP.
37
Argument użytkownik należy zastąpić właściwą nazwą użytkownika. Opcja -p instruuje mysql,
aby poprosił o podanie hasła. Możliwe jest przekazanie hasła na końcu opcji -p (wpisz
-prower, jeśli hasłem jest rower); między opcją a hasłem nie występuje znak spacji. Wpisy-
wanie hasła w wierszu poleceń nie jest dobrym rozwiązaniem ze względów bezpieczeństwa,
ponieważ jest wówczas przesyłane przez sieć jako niezakodowany tekst, a w dodatku ktoś może
podejrzeć listę procesów uruchomionych na danym komputerze.
Aby zakończyć pracę z mysql, wpisz quit lub exit i naciśnij klawisz Enter.
Tworzenie bazy danych i tabel
Zakładając, iż ma się wszystkie prawa wymagane do tworzenia i modyfikowania bazy danych
na serwerze, można przystąpić do tworzenia nowej bazy danych i tabel. W niniejszym rozdziale
wykonamy bazę danych dla fikcyjnej księgarni.
CREATE DATABASE ksiegarnia;
To krótkie polecenie tworzy bazę danych o nazwie ksiegarnia. W niniejszej książce polecenia
i zarezerwowane słowa będą pisane wielkimi literami. Nie jest to jednak wymagane — MySQL
nie rozróżnia wielkości liter w słowach kluczowych i klauzulach. Nazwy baz danych i tabel
są czułe na wielkość liter w systemach operacyjnych czułych na wielkość liter, na przykład
systemach uniksowych, ale nie są czułe w systemach, które nie zwracają uwagi na wielkość
liter, na przykład systemach Windows. Przyjęło się jednak, by słowa kluczowe w dokumen-
tacjach SQL pisać wielkimi literami, a nazwy tabel, baz danych i kolumn małymi literami.
Polecenia SQL są zakończone znakiem średnika. Polecenie SQL może rozciągać się na więcej niż
jeden wiersz. Jego wysłanie do serwera i przetworzenie rozpoczyna się dopiero po wykryciu
znaku średnika. Aby zatrzymać uruchomione polecenie SQL, zamiast średnika należy wpisać c.
Skoro baza danych jest już założona, warto w aktualnej sesji określić domyślną bazę danych,
stosując poniższe polecenie:
USE ksiegarnia;
Kolejny krok to utworzenie pierwszej tabeli, w której później znajdą się dane. Pierwsza tabela
będzie przechowywała podstawowe informacje o książce, ponieważ jest to najważniejszy
element książkowego biznesu.
CREATE TABLE ksiazki (
id_rek INT,
tytul VARCHAR(50),
autor VARCHAR(50)
);
Polecenie tworzy tabelę o nazwie ksiazki z trzema kolumnami. Pierwsza kolumna to po pro-
stu numer identyfikacyjny każdego z rekordów. Jest typu całkowitoliczbowego. Warto pa-
miętać, iż w MySQL pola nazywane są kolumnami, a rekordy wierszami. Typem danych dla
drugiej i trzeciej kolumny są pola tekstowe o zmiennej długości — mogą pomieścić maksy-
malnie 50 znaków. Lista kolumn znajduje się w nawiasach.
Aby poznać opis właśnie utworzonej tabeli, wpisz instrukcję DESCRIBE, która wyświetli po-
niższą tabelę:
38
|
Rozdział 3. Podstawy MySQL
DESCRIBE ksiazki;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id_rek | int(11) | YES | | NULL | |
| tytul | varchar(50) | YES | | NULL | |
| autor | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
Przyglądając się tabeli, nietrudno się domyślić, iż potrzebne byłyby dodatkowe kolumny
z wydawcą, rokiem publikacji, numerem ISBN, gatunkiem, opisem książki itp. Co więcej,
przydałoby się, by MySQL automatycznie przypisał wartość kolumnie id_rek, aby nie trzeba
było samemu martwić się wymyślaniem wartości i sprawdzaniem, czy się nie powielają. Do-
datkowo, decydujemy się zamienić kolumnę autor z wersji zawierającej nazwisko autora na
numer identyfikacyjny autora, który będzie pochodził z osobnej tabeli zawierającej listę autorów.
Zredukuje to ilość tekstu do wpisywania, ułatwi sortowanie i wyszukiwanie, gdyż dane będą
jednorodne. Aby dokonać zmian w już istniejącej tabeli, zastosuj następującą instrukcję SQL:
ALTER TABLE ksiazki
CHANGE COLUMN id_rek id_rek INT AUTO_INCREMENT PRIMARY KEY,
CHANGE COLUMN autor id_autora INT,
ADD COLUMN opis BLOB,
ADD COLUMN gatunek ENUM( powieść , poezja , dramat ),
ADD COLUMN id_wydawcy INT,
ADD COLUMN rok_pub VARCHAR(4),
ADD COLUMN isbn VARCHAR(20);
Każdy wiersz, w którym znajdują się informacje o zmienianych lub dodawanych kolumnach,
poza pierwszym, musi być oddzielony przecinkiem. W drugim wierszu następuje zmiana ko-
lumny id_rek. Choć nazwa kolumny i jej typ pozostaje bez zmian, trzeba podać je raz jeszcze.
Znacznik AUTO_INCREMENT służy do zapewnienia działania opisanego we wcześniejszym akapi-
cie, czyli przypisania każdej nowej książce unikatowej wartości. Dodatkowo wiersz zostaje
oznaczony jako PRIMARY KEY, aby przyspieszyć pobieranie danych.
Trzeci wiersz dokonuje zmian w kolumnie autor w taki sposób, aby dostosować jej tytuł i typ
do tabeli autorzy, która wkrótce powstanie. Tabela autorzy będzie zawierała kolumnę klucza
głównego, po której dokona się złączenia tabel. Ponieważ kolumna ta będzie typu całkowito-
liczbowego, taki typ trzeba zastosować w zmienianej kolumnie autora.
Czwarty wiersz dodaje kolumnę z opisem książki. Zastosowany typ danych to BLOB, co jest
skrótem od binary large object (duży obiekt binarny). Ten typ danych ma zmienną długość
i potrafi przechowywać do 64 kilobajtów danych. Istnieją typy danych potrafiące przecho-
wywać jeszcze więcej informacji. Ich pełna lista wraz z limitami znajduje się w dodatku A.
W kolumnie gatunek wymienia się możliwe wartości, aby zapewnić jednorodność. Dostępne
są również wartości: pusta i NULL, choć nie zostały jawnie wymienione w poleceniu.
Przed rozpoczęciem dodawania danych do tabeli ksiazki, warto jeszcze utworzyć tabelę autorzy.
Tabela autorzy będzie tak zwaną tabelą referencyjną (nazywaną też czasem słownikiem). Trzeba
ją ustawić jako pierwszą, ponieważ w trakcie wpisywania danych do tabeli ksiazki potrzebny
będzie numer identyfikacyjny autora.
Tworzenie bazy danych i tabel
|
39
CREATE TABLE autorzy
(id_rek INT AUTO_INCREMENT PRIMARY KEY,
nazwisko VARCHAR(50),
imie VARCHAR(50),
kraj VARCHAR(50));
Ta tabela nie wymaga znaczącej liczby kolumn, choć w rzeczywistej księgarni zapewne zo-
stałyby zastosowane dodatkowe kolumny opisowe. W przyszłości dojdzie do złączenia tabel
ksiazki i autorzy za pomocą wartości z kolumny id_autora tabeli ksiazki i kolumny id_rek po-
wyższej tabeli. Osobiście zawsze w ten sam sposób nazywam kolumnę klucza głównego
każdej tabeli (id_rek), aby przy wpisywaniu zapytań nie zastanawiać się nad nazwą i nie mu-
sieć sprawdzać jej poleceniem DESCRIBE.
W powyższej tabeli imię i nazwisko autora zostało rozbite na dwie kolumny, aby ułatwić
sortowanie i wyszukiwanie po nazwisku. Dodatkowo pojawiła się kolumna kraju pochodzenia
autora, co umożliwi wyszukiwanie książek napisanych przez autorów pochodzących z kraju
podanego przez użytkownika.
Pokaż mi
Warto zatrzymać się na chwilę i popodziwiać wykonaną do tej pory pracę. Aby uzyskać listę
baz danych, należy wykonać polecenie SHOW DATABASES.
SHOW DATABASES;
+------------+
| Database |
+------------+
| ksiegarnia |
| mysql |
| test |
+------------+
Wynik wykonania polecenia ujawnia istnienie dwóch dodatkowych baz danych poza właśnie
wykonaną bazą. Jedną z tych dodatkowych baz jest mysql, która zawiera dane dotyczące
przywilejów użytkowników. Została ona pokrótce omówiona w poprzednim rozdziale. Trze-
cia wymieniona baza danych (test) jest tworzona automatycznie w trakcie instalacji MySQL.
Tradycyjnie używa się jej do dodawania tabel, dla których chce się przeprowadzić testowanie
poleceń SQL.
Aby wyświetlić listę tabel bazy danych ksiegarnia (po wcześniejszym wybraniu tej bazy danych
poleceniem USE), wpisz poniższe polecenie:
SHOW TABLES;
+----------------------+
| Tables_in_ksiegarnia |
+----------------------+
| autorzy |
| ksiazki |
+----------------------+
40
|
Rozdział 3. Podstawy MySQL
Wynik działania polecenia SHOW TABLES przedstawia listę zawierającą, zgodnie z oczekiwa-
niami, dwie tabele. Jeśli chce się wyświetlić listę tabel innej bazy danych, korzystając nadal
z aktywnej bazy danych ksiegarnia, należy do wcześniejszego polecenia dodać klauzulę FROM.
USE ksiegarnia;
SHOW TABLES FROM mysql;
Spowoduje to wyświetlenie tabel bazy danych mysql, choć klient nadal będzie ściśle związany
z bazą danych ksiegarnia.
Wstawianie danych
Po wykonaniu dwóch pierwszych tabel warto wstawić do nich dane. Najprostszy sposób
wykonania tego zadania to użycie polecenia INSERT. Dzięki temu poleceniu można za jednym
podejściem dodać jeden lub więcej rekordów. Przed dodaniem informacji o książce do tabeli
ksiazki trzeba wypełnić pole tabeli autorzy, aby móc odnieść się od identyfikatora autora. Wy-
konaj poniższe polecenie, korzystając z klienta mysql:
INSERT INTO autorzy
(nazwisko, imie, kraj)
VALUES( Grochola , Katarzyna , Polska );
Po dodaniu informacji o autorze, można wstawić napisaną przez niego książkę.
INSERT INTO ksiazki
(tytul, id_autora, isbn, gatunek, rok_pub)
VALUES( Nigdy w życiu , LAST_INSERT_ID(), 83-88221-55-8 , powieść , 2001 );
Pierwsze polecenie spowodowało dodanie rekordu dla Katarzyny Grocholi, autorki książki
Nigdy w życiu. Standardowa składnia polecenia INSERT najpierw określa kolumny, w których
mają zostać umieszczone dane. Jeśli chce się wstawiać dane do wszystkich kolumn w kolej-
ności określonej w bazie danych, nie trzeba podawać nazw kolumn. W drugim poleceniu SQL
lista kolumn ma inną kolejność niż oryginalna lista kolumn w tabeli. Takie rozwiązanie jest
w pełni akceptowane przez MySQL — trzeba jednak zapewnić taką samą kolejność przekazy-
wanych danych. Identyfikator autora dla kolumny id_autora pobieramy z poprzedniego pole-
cenia, korzystając z funkcji LAST_INSERT_ID().
Pobieranie danych
Skoro w obu tabelach znajdują się dane, warto wykonać pewne zapytania. Do pobrania da-
nych służy polecenie SELECT. Aby pobrać wszystkie kolumny i wiersze tabeli ksiazki, wpisz
poniższe polecenie:
SELECT * FROM ksiazki;
Znak gwiazdki jest w tym przypadku znakiem wieloznaczności powodującym pobranie
wszystkich kolumn. Ponieważ nie zostały określone żadne kryteria, dzięki którym miałyby
zostać pobrane jedynie niektóre wiersze, polecenie spowoduje pobranie wszystkich wierszy
tabeli ksiazki. Aby pobrać konkretne kolumny i wiersze, należy podać nazwy kolumn i zasto-
sować klauzulę WHERE z warunkami stawianymi poszczególnym wierszom.
Pobieranie danych
|
41
SELECT id_rek, tytul, opis
FROM ksiazki
WHERE gatunek = nowela ;
Polecenie SQL wyświetla jedynie numer identyfikacyjny, tytuł i opis wszystkich książek z ta-
beli ksiazki, które należą do gatunku nowela. Oczywiście wyniki byłyby bardziej imponujące,
gdyby w tabeli znajdowało się więcej książek. Załóżmy, iż wpisaliśmy do bazy danych kil-
kadziesiąt książek i kontynuujmy pracę.
Aby z bazy danych pobrać wszystkie książki napisane przez wybranego autora, trzeba do-
konać połączenia tabeli ksiazki z tabelą autorzy. Poniżej został przedstawiony przykład takiego
połączenia.
SELECT ksiazki.id_rek, tytul, rok_pub,
CONCAT(imie, , nazwisko) AS autor
FROM ksiazki, autorzy
WHERE nazwisko = Grochola AND id_autora = autorzy.id_rek;
Obie tabele posiadają kolumnę o nazwie id_rek, więc poza nazwą kolumny trzeba określić
nazwę tabeli, by poprawnie dokonać złączenia. W tym celu przed nazwą kolumny umieszcza
się nazwę tabeli oraz znak kropki jako separator. Przykład takiego rozwiązania znajduje się
w pierwszym wierszu, w którym dochodzi do pobrania numeru identyfikacyjnego rekordu.
Drugi wiersz korzysta z funkcji tekstów o nazwie CONCAT(). Dzięki tej funkcji można połą-
czyć ze sobą kilka fragmentów danych w jeden tekst, aby uzyskać lepiej wyglądający wynik.
W tym przypadku funkcja łączy imię, znak spacji (w apostrofach) oraz nazwisko autora.
Wynik połączenia będzie widoczny jako jedna kolumna o nazwie autor, ponieważ taka na-
zwa została podana jako alias dzięki słowu kluczowemu AS. Klauzula FROM wymienia obie
tabele oddzielone przecinkiem. Jeśli istniałoby więcej tabel, wystarczyłoby je podać w tej
klauzuli w dowolnej kolejności, oddzielając je przecinkami. W klauzuli WHERE informujemy
serwer, iż jesteśmy zainteresowani książkami napisanymi przez autora o nazwisku „Gro-
chola”. Dodatkowo klauzula ta zawiera warunek złączenia obu tabel (ostatni wiersz). Złącze-
nie dotyczy kolumny id_autora tabeli ksiazki i kolumny id_rek tabeli autorzy. Jeśli w tabeli nie
znajduje się żadna książka napisana przez takiego autora, nie zostaną zwrócone żadne wiersze.
Jeżeli książka takiego autora została wpisana, ale jego dane nie znajdują się w tabeli autorzy,
również nie zostaną wyświetlone żadne wiersze. Oto przykładowy wynik wykonania poprzed-
niego polecenia SQL.
+--------+-------------------+---------+--------------------+
| id_rek | tytul | rok_pub | autor |
+--------+-------------------+---------+--------------------+
| 1 | Nigdy w życiu | 2001 | Katarzyna Grochola |
| 2 | Podanie o miłość | 2002 | Katarzyna Grochola |
+--------+-------------------+---------+--------------------+
Nietrudno zauważyć, iż zostały odnalezione dwie książki Katarzyny Grocholi. Tytuł ostatniej
kolumny został określony w zapytaniu za pomocą słowa kluczowego AS. Podobne zmiany
można wymusić dla innych kolumn, korzystając z tego samego słowa kluczowego. Alias autor
może zostać użyty w innym miejscu polecenia SELECT, ale niestety nie w klauzuli WHERE.
Więcej informacji na temat AS znajduje się w rozdziale 4.
42
|
Rozdział 3. Podstawy MySQL
Kolejność, limitowanie wyników i grupowanie
Gdy pobiera się duży zbiór danych, warto posortować znajdujące się w nim informacje we-
dług konkretnego klucza. W tym celu stosuje się klauzulę ORDER BY. Przypuśćmy, iż potrze-
bujemy pobrać z bazy danych wszystkie sztuki napisane przez Williama Shakespeare’a. Po-
niższe zapytanie SQL pobierze odpowiednią listę i posortuje ją według tytułu sztuki:
SELECT ksiazki.id_rek, tytul, wydawca
FROM ksiazki, autorzy, wydawcy
WHERE nazwisko = Shakespeare
AND gatunek = sztuka
AND id_autora = autorzy.id_rek
AND id_wydawcy = wydawcy.id_rek
ORDER BY tytul, rok_pub;
Klauzula ORDER BY znajduje się na końcu, po klauzuli WHERE. Najpierw sortowanie odbywa
się po tytule (kolumnie tytul), a w ramach tego samego tytułu po roku publikacji (kolumnie
rok_pub). Domyślnie dane porządkowane są w porządku alfabetycznym. Jeśli chce się posor-
tować tytuły w odwrotnym porządku alfabetycznym, zaraz po nazwie kolumny title w klauzuli
ORDER BY, ale przed przecinkiem rozpoczynającym rok_pub, trzeba zastosować opcję DESC.
Duża księgarnia może posiada wiele wydań sztuk Shakespeare’a, być może nawet kilka wydań
tej samej sztuki. Aby ograniczyć liczbę wyświetlanych rekordów, stosuje się klauzulę LIMIT na
końcu polecenia SQL.
SELECT ksiazki.id_rek, tytul
FROM ksiazki, autorzy, wydawcy
WHERE nazwisko = Shakespeare
AND gatunek = sztuka
AND id_autora = autorzy.id_rek
AND id_wydawcy = wydawcy.id_rek
ORDER BY tytul, rok_pub
LIMIT 20;
Dodatkowa klauzula ograniczy liczbę zwróconych wierszy do pierwszych 20. Liczenie roz-
poczyna się od pierwszego wiersza zbioru wyników zaraz po posortowaniu danych zgodnie
z wymaganiami zawartymi w klauzuli ORDER BY. Jeśli chce się pobrać kolejnych 10 pozycji,
trzeba w klauzuli LIMIT najpierw podać liczbę wierszy do pominięcia, a następnie po prze-
cinku liczbę wierszy do pobrania. Jeżeli zechcemy pominąć pierwszych 20 wyników i wy-
świetlić jedynie kolejnych 10, trzeba zastąpić wcześniejszą klauzulę LIMIT następującą wersją:
...
LIMIT 20, 10;
W dwuargumentowej wersji klauzuli pierwsza wartość określa liczbę wierszy do pominięcia
(w przykładzie 20), a druga maksymalną liczbę wierszy do pobrania (w przykładzie 10).
Jeżeli chcemy pobrać jedynie listę sztuk Shakespeare’a i nie jesteśmy zainteresowani datą pu-
blikacji ani wydawcą — innymi słowy, jesteśmy zainteresowani jedynie pierwszym znalezio-
nym wierszem dla każdego tytułu — możemy zastosować klauzulę GROUP BY.
SELECT ksiazki.id_rek, tytul
FROM ksiazki, autorzy
WHERE nazwisko = Shakespeare
AND id_autora = autorzy.id_rek
GROUP BY tytul;
Kolejność, limitowanie wyników i grupowanie
|
43
Wynikiem działania powyższego polecenia SQL jest lista wszystkich tytułów sztuk Shake-
speare’a istniejących w bazie danych. Numer identyfikacyjny będzie dotyczył pierwszego
znalezionego wiersza dla każdego tytułu. Co ciekawe, GROUP BY zwróci te same dane co ORDER BY
zastosowane dla tej samej kolumny.
Analiza i manipulacja danymi
MySQL umożliwia nie tylko pobieranie surowych danych, ale również ich analizę i formato-
wanie. Przypuśćmy, iż chcemy się dowiedzieć, ile powieści Tołstoja posiadamy. W tym celu
trzeba w poleceniu SQL zastosować funkcję COUNT().
SELECT COUNT(*)
FROM ksiazki, autorzy
WHERE nazwisko = Tołstoj
AND id_autora = autorzy.id_rek;
+----------+
| COUNT(*) |
+----------+
| 12 |
+----------+
Załóżmy, że po ustawieniu i uruchomieniu bazy danych zawiera ona tabelę zamowienia z in-
formacjami na temat zamówień użytkowników. Możemy wykorzystać tę tabelę, aby spraw-
dzić sprzedaż wybranej książki. Aby sprawdzić kwotę uzyskaną ze sprzedaży książki Armadillo
autorstwa Williama Boyda, wystarczy wpisać poniższe polecenie SQL w kliencie mysql:
SELECT SUM(kwota_sprzedazy) AS Sprzedaż Armadillo
FROM zamowienia, ksiazki, autorzy
WHERE tytul = Armadillo
AND nazwisko = Boyd
AND id_ksiazki = ksiazki.id_rek;
AND id_autora = autorzy.id_rek;
+--------------------+
| Sprzedaż Armadillo |
+--------------------+
| 250.25 |
+--------------------+
Aby uzyskać odpowiednie informacje, łączymy trzy tabele. MySQL pobiera wartość kolumny
kwota_sprzedazy dla każdego wiersza tabeli zamowienia spełniającego kryteria zawarte w klau-
zuli WHERE. Następnie sumuje znajdujące się tam wartości i wyświetla je w kolumnie o poda-
nej nazwie. Większość nazw kolumn występuje tylko w jednej tabeli, więc MySQL nie ma
problemów ze stwierdzeniem, czego dotyczą. Niemniej dla kilku kolumn trzeba zastosować
wersję tabela.kolumna.
Korzystając z wielu różnorodnych funkcji, można sterować sposobem formatowania kolumn
zawierających datę lub czas. Załóżmy, iż chcemy wydobyć z tabeli zamowienia datę złożenia
zamówienia na podstawie posiadanego numeru rachunku (na przykład 1250), który tak na-
prawdę jest numerem identyfikującym rekord (id_rek). Zastosowanie poniższego polecenia
SQL spowoduje zwrócenie daty w domyślnym formacie.
44
|
Rozdział 3. Podstawy MySQL
SELECT data_zakupu AS Data zakupu
FROM zamowienia
WHERE id_rek = 1250 ;
+---------------+
| Data zakupu |
+---------------+
| 2004-03-01 |
+---------------+
Zastosowany format (rok-miesiąc-dzień) jest w pełni zrozumiały. Jeżeli jednak chce się wy-
świetlić nazwę miesiąca jako tekst, a nie liczbę, można skorzystać z odpowiednich funkcji daty.
SELECT CONCAT(DAYOFMONTH(data_zakupu), ,
MONTHNAME(data_zakupu), ,
YEAR(data_zakupu)) AS Data zakupu
FROM orders
WHERE rec_id = 1250 ;
+---------------+
| Data zakupu |
+---------------+
| 1 March 2004 |
+---------------+
Aby przedstawić datę w formacie często stosowanym w Polsce, korzystamy z funkcji CONCAT()
i kilku funkcji daty. Początkowo zastosowany kod może wydawać się niezrozumiały z po-
wodu wstawiania dodatkowych znaków spacji między poszczególne elementy daty. Pierwsza
z funkcji pobiera z daty liczbę reprezentującą dzień i po prostu ją wyświetla. Kolejna funkcja
wydobywa z kolumny data_zakupu miesiąc i zwraca go jako nazwę w języku angielskim.
Trzecia funkcja, znajdująca się w trzecim wierszu, wydobywa rok. Przyglądając się wyni-
kowi, łatwo stwierdzić, iż ten złożony kod działa prawidłowo. Nie jest to jednak najbardziej
wygodny sposób formatowania daty. Lepsze rozwiązanie polega na zastosowaniu funkcji
DATE_FORMAT().
SELECT DATE_FORMAT(data_zakupu, d M Y )
AS Data zakupu
FROM orders
WHERE rec_id = 1250 ;
To rozwiązanie jest znacznie prostsze i krótsze, a co najważniejsze, daje identyczne wyniki.
Aby poprawnie skorzystać z powyższej funkcji, trzeba znać kody formatujące. Zostały one
wymienione w rozdziale 6.
Modyfikacja danych
Do modyfikacji danych w bazie danych służy kilka różnych poleceń. Najbardziej podstawo-
wą i chyba najpopularniejszą instrukcją jest UPDATE. Dzięki niej można zmienić dane we
wskazanych kolumnach wszystkich wierszy spełniających klauzulę WHERE. Przyglądając się
wynikom jednego z wcześniejszych zapytań można zauważyć, iż data wydania dla książki
Katarzyny Grocholi Podanie o miłość to rok 2002. Nie jest to poprawna wartość, gdyż książka
została wydana w roku 2001. Aby uaktualnić tę informację, wpisz poniższe polecenie SQL:
Modyfikacja danych
|
45
UPDATE ksiazki
SET rok_pub = 2001
WHERE id_rek = 2 ;
Query OK, 1 rows affected (0.22 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Najpierw podaje się nazwę aktualizowanej tabeli. Następnie po słowie kluczowym SET poja-
wiają się nazwy kolumn i przypisywane im nowe wartości. Jeżeli zmienia się wartość więcej
niż jednej kolumny, poszczególne przypisania należy rozdzielić przecinkami. Słowo kluczowe
SET stosuje się tylko raz.
Powyższe polecenie posiada klauzulę WHERE, co powoduje ograniczenie liczby modyfikowa-
nych wierszy wyłącznie do wierszy spełniających podane warunki. W przedstawionym przy-
kładzie warunek dotyczy kolumny z unikatowymi wartościami, więc zmieniony zostanie tylko
jeden wiersz. Wynik wykonania polecenia wskazuje na wpłynięcie na jeden z wierszy, dopa-
sowanie się do jednego wiersza, zmienienie jednego z wierszy. W trakcie wykonywania nie
pojawiły się żadne błędy powodujące zgłoszenie ostrzeżeń.
Czasem wstawienie danych do tabeli spowoduje powstanie duplikatu wiersza, gdy dane
wiersza istniały już wcześniej. Przypuśćmy, iż wykonaliśmy polecenie SQL wstawiające do
tabeli kilka książek, ale jedna z tych książek już była zawarta w bazie danych. Jeżeli zastosuje
się polecenie INSERT, powstanie duplikat. Aby temu zapobiec, można użyć polecenia REPLACE,
które wstawia nowy wiersz lub zastępuje istniejący wiersz nowymi danymi. Z perspektywy
MySQL duplikacja występuje tylko wtedy, gdy unikatowe kolumny miałyby zawierać tę sa-
mą wartość. Ponieważ wartość kolumny id_rek jest przypisywana automatycznie, jej duplikacja
jest mało prawdopodobna na etapie dodawania nowych rekordów. Unikatowym elementem
każdej książki jest numer ISBN — dzięki niemu można jednoznacznie określić książkę. Aby
zapewnić, iż nie pojawią się wiersze z tym samym numerem ISBN, warto ponownie zmody-
fikować tabelę ksiazki i wymusić unikatowość wartości znajdujących się w kolumnie isbn. W ten
sposób zapobiegnie się dwukrotnemu wpisaniu danych związanych z tą samą książką.
ALTER TABLE ksiazki
CHANGE COLUMN isbn isbn VARCHAR(20) UNIQUE;
Od teraz można wstawiać dane kolejnych książek nie martwiąc się o zduplikowane wiersze
o tym samym numerze ISBN. Poniżej znajduje się przykładowy kod próbujący dodać dwie
książki autorstwa Katarzyny Grocholi, z których jedna już znajduje się w tabeli:
REPLACE INTO ksiazki
(tytul, id_autora, isbn, gatunek, rok_pub)
VALUES( Nigdy w życiu , 1000 , 83-88221-55-8 , powieść , 2001 ),
( Ja wam pokażę , 1000 , 83-89291-84-3 , powieść , 2004 ),
Składnia polecenie REPLACE jest taka sama jak polecenia INSERT. Warto zauważyć dodanie
dwóch wierszy w jednym poleceniu. Dokładnie tę samą składnię stosuje się, aby dodać wiele
wierszy poleceniem INSERT. Dane każdego wiersza umieszcza się w nawiasach, a poszcze-
gólne grupy nawiasów oddziela przecinkami. Ponieważ w przedstawionym przykładzie w ba-
zie danych istnieje wiersz dla książki o numerze ISBN 83-88221-55-8 (Nigdy w życiu), zostanie
on zastąpiony, a nie dodany. Ponieważ drugiej książki nie ma jeszcze w tabeli, zostanie do
niej dodana.
46
|
Rozdział 3. Podstawy MySQL
Usuwanie danych
Do usuwania konkretnych wierszy danych służy polecenie DELETE. Jeśli na przykład chce się
usunąć z tabeli ksiazki wszystkie wiersze dotyczące książek autorki J. K. Rowling, ponieważ
podjęło się decyzję o niesprzedawaniu książek Harry Potter (nie chce się prowadzić tego ro-
dzaju biznesu), można użyć poniższego polecenia:
DELETE FROM ksiazki
WHERE id_autora =
(SELECT autorzy.id_rek FROM autorzy
WHERE nazwisko = Rowling
AND imie = J.K. );
DELETE FROM autorzy
WHERE nazwisko = Rowling
AND imie = J.K. ;
Powyższy kod usuwa z tabeli ksiazki wszystkie książki, których identyfikator autora zawiera
wartość zwróconą przez podzapytanie. Zapytanie zwraca identyfikator z tabeli autorzy doty-
czący autora o wskazanym imieniu i nazwisku. Innymi słowy, kolumna id_autora musi za-
wierać wartość zwróconą przez polecenie SELECT (podzapytanie w nawiasie). Ponieważ kod
korzysta z podzapytań, do poprawnego działania wymaga MySQL w wersji 4.1 lub nowszej.
Aby wykonać to samo zadanie w jednej z wcześniejszych wersji MySQL, trzeba by wykonać
polecenie SELECT, zapamiętać zwrócony identyfikator autora, a następnie wykonać polecenie
DELETE, ręcznie wpisując numer identyfikacyjny.
Alternatywne rozwiązanie dla poprzedniej konstrukcji polega na zastosowaniu zmiennych
zdefiniowanych przez użytkownika. Oto przykład korzystający ze zmiennych:
SET @potter =
(SELECT id_rek FROM autorzy
WHERE nazwisko = Rowling
AND imie = J.K. );
DELETE FROM ksiazki
WHERE id_autora = @potter;
DELETE FROM autorzy
WHERE id_rek = @potter;
W pierwszym fragmencie polecenie SET służy do utworzenia zmiennej o nazwie @potter,
która będzie zawierać wynik działania polecenia SELECT umieszczonego w nawiasach (podza-
pytanie). Choć MySQL w wersjach wcześniejszych niż 4.1 nie obsługuje podzapytań, przed-
stawiony kod zadziała, gdyż dotyczy zmiennych definiowanych przez użytkownika. Drugie
polecenie SQL usuwa z tabeli ksiazki wszystkie książki, które jako identyfikator autora posia-
dają wartość znajdującą się w zmiennej tymczasowej. Ostatnie polecenie usuwa dane z tabeli
autorzy, ponownie korzystając ze zmiennej. Zmienne zdefiniowane przez użytkownika ist-
nieją do momentu ich zresetowania lub zamknięcia sesji z serwerem MySQL.
Usuwanie danych
|
47
Wyszukiwanie danych
Gdy baza danych zawiera ogromną liczbę informacji, znajdowanie danych przy użyciu ręcz-
nego przeszukiwania wyników polecenia SELECT jest nie do zaakceptowania. Co więcej, cza-
sem nie zna się dokładnego lub pełnego tekstu, który miałby istnieć w wybranej kolumnie.
W takich sytuacjach korzysta się z operatora LIKE. Załóżmy, iż tabela ksiazki zawiera tysiące
wpisów. Klient chce odnaleźć książkę, ale nie pamięta jej autora. Wie jedynie, iż w tytule
książki występowały wyrazy zimową i podróżny. Można użyć tego strzępka informacji do
przeszukania zawartości bazy danych, używając poniższego polecenia:
SELECT ksiazki.id_rek, tytul,
CONCAT(imie, , nazwisko) AS autor
FROM ksiazki, autorzy
WHERE tytul LIKE podróżny
AND tytul LIKE zimową
AND id_autora = autorzy.id_rek;
+--------+-------------------------------+---------------+
| id_rek | tytul | autor |
+--------+-------------------------------+---------------+
| 1400 | Jeśli zimową nocą podróżny | Italo Calvino |
+--------+-------------------------------+---------------+
Poza operatorem LIKE dwukrotnie został użyty znak wieloznaczny procenta, aby wskazać, iż
poszukuje się wszystkich wierszy, w których kolumna tytul zaczyna się od zera lub więcej
znaków przed wzorcem podróżny, a po wzorcu może się pojawić 0 lub więcej innych zna-
ków. Innymi słowy, wyraz podróżny musi się znaleźć w dowolnym miejscu w danych wy-
branej kolumny. Podobna sytuacja dotyczy wyrazu zimową. Warto pamiętać, iż słowo LIKE
to operator. Więcej informacji na temat operatorów znajduje się w dodatku B.
Jeżeli inny klient poprosi o wyszukanie w bazie danych tytułu książki zawierającej wyraz
Ford lub Chevrolet, należy zastosować w klauzuli WHERE operator OR.
SELECT ksiazki.id_rek, tytul,
CONCAT(imie, , nazwisko) AS autor
FROM ksiazki, autorzy
WHERE tytul LIKE Ford AND id_autora = autorzy.id_rek
OR tytul LIKE Chevrolet AND id_autora = autorzy.id_rek;
Więcej przykładów i możliwości wyszukiwania danych znajduje się w rozdziale 4.
Hurtowy import danych
Choć polecenia INSERT i REPLACE są bardzo użyteczne, potrafią być czasochłonne w przy-
padku wprowadzania dużej ilości danych, gdyż wymuszają ich ręczne wpisywanie. Często
w momencie tworzenia nowej bazy danych trzeba dokonać przeniesienia danych ze starej
bazy danych. Załóżmy, iż wydawca wysłał nam dysk z listą wszystkich swoich książek znaj-
dującą się w zwykłym pliku tekstowym. Każdy rekord dotyczący poszczególnej książki znaj-
duje się w osobnym wierszu, a każde pole zostało oddzielone znakiem pionowej kreski. Oto
w jaki sposób mogą wyglądać fikcyjne dane od wydawcy.
ISBN|TYTUL|NAZWISKO|IMIE|DATA WYDANIA
0-907587-68-2|Notatki z podziemia|Dostojewski|Fiodor|kwiecień 1992|
...
48
|
Rozdział 3. Podstawy MySQL
Oczywiście rzeczywisty plik od wydawcy zawierałby znacznie więcej pól i rekordów niż zo-
stało tutaj przedstawione, ale tutaj ograniczymy się jedynie do przykładu. Pierwszy wiersz
zawiera opis pól rekordów. Nie należy pobierać pierwszego wiersza. Zawiera on po prostu
instrukcje dla osoby edytującej plik. Poinformujemy MySQL, by zignorował pierwszy wiersz.
Jeśli chodzi o dane, trzeba zająć się kilkoma problemami. Pola nie znajdują się w takiej samej
kolejności, w jakiej są zapisane w bazie danych. Trzeba poinformować MySQL o zmianie ko-
lejności przy zapisie. Inny problem polega na tym, iż plik tekstowy zawiera dane dla tabel
ksiazki i autorzy. Obejście tego problemu nie jest łatwe, ale wykonalne. W pierwszym podej-
ściu wydobędziemy jedynie informacje o autorze. Osobne polecenie SQL posłuży do wydo-
bycia informacji na temat książki. Na początek należy przenieść plik od wydawcy (ksiazki.txt)
do katalogu /tmp, a następnie wykonać polecenie LOAD DATA INFILE w kliencie mysql.
LOAD DATA INFILE /tmp/ksiazki.txt REPLACE INTO TABLE autorzy
FIELDS TERMINATED BY | LINES TERMINATED BY
TEXT_FIELDS(kol1, kol2, kol3, kol4, kol5)
SET nazwisko = kol3, imie = kol4
IGNORE kol1, kol2, kol5, 1 LINES;
Klauzule TEXT_FIELDS i IGNORE dla kolumn nie są dostępne w wersjach MySQL starszych niż
4.1. Klauzula IGNORE n LINES jest dostępna w MySQL już od dłuższego czasu. Treść IGNORE
1 LINES spowoduje pominięcie pierwszego wiersza. Wracając do pierwszego wiersza pole-
cenia, występuje w nim nazwa wczytywanego pliku i tabela, w której mają zostać umiesz-
czone dane. Znacznik REPLACE daje taki sam efekt jak opisywane wcześniej polecenie REPLACE.
Drugi wiersz informuje o tym, iż pola oddzielane są znakiem pionowej kreski, a wiersze od-
dzielane znakami powrotu karetki (
) i przejścia do nowego wiersza (
). Jest to format ty-
powego pliku MS-DOS. Pliki systemu Unix korzystają jedynie ze znaku przejścia do nowego
wiersza. Trzeci wiersz tworzy aliasy dla poszczególnych kolumn. Czwarty wiersz zawiera
nazwy kolumn tabeli i przypisuje im odpowiednie dane, korzystając z aliasów określonych
w poprzednim wierszu. Ostatni wiersz informuje MySQL, aby zignorował niechciane kolumny,
a także pominął pierwszy wiersz pliku tekstowego, gdyż nie zawiera on danych.
Jeśli korzysta się ze starszej wersji serwera MySQL, który nie zawiera nowej funkcji pozwa-
lającej zignorować niechciane kolumny, trzeba wykonać kilka dodatkowych kroków. Istnieje
kilka możliwych sposobów na wykonanie tego zadania. Jednym z prostszych sposobów (jeśli
wczytywanych danych nie jest zbyt dużo) jest dodanie tymczasowych kolumn do tabeli auto-
rzy. Kolumny te pomieszczą nadmiarowe dane z pliku tekstowego. Później będzie można je
usunąć. Oto skrypt wykonujący całe zadanie:
ALTER TABLE autorzy
ADD COLUMN kol1 VARCHAR(50),
ADD COLUMN kol2 VARCHAR(50),
ADD COLUMN kol5 VARCHAR(50);
LOAD DATA INFILE /tmp/ksiazki.txt REPLACE INTO TABLE autorzy
FIELDS TERMINATED BY | LINES TERMINATED BY
IGNORE 1 LINES
(kol1, kol2, nazwisko, imie, kol5);
ALTER TABLE autorzy
DROP COLUMN kol1,
DROP COLUMN kol2,
DROP COLUMN kol5;
Hurtowy import danych
|
49
Przedstawione rozwiązanie działa poprawnie, choć nie jest tak przyjemne i proste jak wcze-
śniejsze polecenie. Zauważ, iż w drugim poleceniu SQL klauzula IGNORE wymusza pominię-
cie jednego wiersza. Ostatni wiersz tego polecenia wymienia kolumny tabeli autorzy, w których
mają zostać umieszczone importowane dane. Trzecie polecenie usuwa kolumny tymczasowe
po zakończeniu wczytywania danych z pliku tekstowego. W tym celu stosuje instrukcję DROP.
Na ogół nie można cofnąć wyników działania tej instrukcji, więc warto uważać.
Po umieszczeniu danych autorów z pliku tekstowego w tabeli autorzy, można przystąpić do
wczytania danych książek i odszukania poprawnych wartości kolumny id_autora dla każdej
książki. Zadanie to wykona poniższe polecenie SQL:
LOAD DATA INFILE /tmp/ksiazki.txt IGNORE INTO TABLE ksiazki
FIELDS TERMINATED BY | LINES TERMINATED BY
TEXT_FIELDS(kol1, kol2, kol3, kol4, kol5)
SET isbn = kol1, tytul = kol2,
rok_pub = RIGHT(kol5, 4),
id_autora =
(SELECT autorzy.id_rek
WHERE nazwisko = kol3
AND imie = kol4)
IGNORE kol3, kol4, 1 LINES;
Polecenie zawiera kilka sztuczek, umożliwiających poprawne wykonanie całego zadania.
Piąty wiersz polecenia wydobywa rok wydania z pola daty wydania (oryginalnie data wy-
dania zawiera miesiąc i rok wydania), stosując funkcję RIGHT(), która pobiera cztery ostatnie
znaki kol5. Od 6. wiersza występuje podzapytanie, które określa wartość kolumny id_autora
na podstawie imienia i nazwiska autora. Wynik uzyskany w nawiasach zostanie przypisany
do kolumny id_autora. Na końcu dochodzi do zignorowania kolumn kol3, kol4 i pierwszego
wiersza pliku tekstowego. Wykonanie tego samego zadania we wcześniejszych wersjach MySQL
wymagałoby użycia kolumn tymczasowych lub dodatkowej tabeli. Znacznik IGNORE z pierw-
szego wiersza instruuje MySQL, aby ignorował wszystkie komunikaty błędów, nie zastępował
żadnych duplikatów i kontynuował wstawianie kolejnych rekordów.
Interfejs wiersza poleceń
Aby wysłać zapytanie SQL do serwera MySQL, nie trzeba otwierać monitora MySQL. Cza-
sem zachodzi potrzeba szybkiego wykonania polecenia SQL z samej powłoki lub wiersza
poleceń. Przypuśćmy, iż mamy tabelę o nazwie dostawcy i chcemy szybko uzyskać listę do-
stawców z województwa śląskiego wraz z numerami telefonów. Aby ją uzyskać, wystarczy
wpisać poniższe polecenie w powłoce systemu Linux (lub innego równoważnego systemu).
mysql --user= tina --password= muller
-e SELECT dostawca, telefon FROM dostawca
WHERE woj= śląskie ksiegarnia
Narzędzie mysql zostaje wywołane, ale nie wchodzi w tryb monitora. Pierwsze argumenty
przekazują nazwę użytkownika i hasło. Pierwszy wiersz kończy się znakiem lewego ukośnika,
aby poinformować powłokę, iż będą jeszcze następne argumenty. W przeciwnym przypadku
trzeba by wszystko umieścić w jednym wierszu. Drugi wiersz zawiera argument -e wskazu-
jący, iż tekst umieszczony za nim w cudzysłowach powinien zostać wykonany przez klienta
mysql. Zawarte w cudzysłowach polecenie SQL ma dokładnie taką samą postać, jaka byłaby
użyta w trybie monitora. Na końcu pojawia się nazwa bazy danych.
50
|
Rozdział 3. Podstawy MySQL
Istnieją inne opcje i narzędzia wiersza poleceń. Niektóre z nich służą da tworzenia kopii
bezpieczeństwa lub wykonywania konserwacji serwera. Ich pełne omówienie znajduje się
w rozdziale 11.
Podsumowanie
Oczywiście to nie wszystkie zadania, które można wykonywać przy użyciu serwera MySQL.
Niniejsze ćwiczenie miało jedynie na celu przybliżenie podstaw tworzenia i zarządzania bazą
danych. Kolejne rozdziały książki zawierają szczegółowe omówienia wszystkich instrukcji,
klauzul, argumentów, opcji i funkcji serwera MySQL. Jeśli dopiero rozpoczyna się swoją przy-
godę z MySQL, warto zacząć od instrukcji i klauzul wymienionych w niniejszym rozdziale,
a następnie skorzystać z kolejnych rozdziałów, by rozszerzyć swą wiedzę o dostępnych opcjach
i funkcjach.
Podsumowanie
|
51
Pobierz darmowy fragment (pdf)
