Podstawowe polecenia języka SQL

•

SELECT

Składnia polecenia

SELECT

jest mniej więcej następująca:

SELECT [PARAMETRY] wyr [FROM tabela [OPCJE]]

a ściślej następująca:

SELECT

[ALL | DISTINCT | DISTINCTROW ]

[HIGH_PRIORITY]

[STRAIGHT_JOIN]

[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

wyr, ...

[INTO OUTFILE 'plik' parametry_zapisu_do_pliku

| INTO DUMPFILE 'plik']

[FROM tabele

[WHERE definicje_skąd]

[GROUP BY {kolumna | wyr | pozycja}

[ASC | DESC], ... [WITH ROLLUP]]

[HAVING definicje_skąd]

[ORDER BY {koluna | wyr | pozycja}

[ASC | DESC] , ...]

[LIMIT {[offset,] ile_wierszy | ile_wierszy OFFSET
offset}]

[PROCEDURE nazwa_procedury(lista_argumentów)]

[FOR UPDATE | LOCK IN SHARE MODE]]

Za pomocą polecenia

SELECT

możemy uzyskać interesujące nas dane z bazy

danych.

ƒ

każde wyrażenie

wyr

oznacza nazwę kolumny, z której chcemy otrzymać

dane. Kolumn może być więcej i w takim wypadku rozdziela się je
przecinkami.

ƒ

tabela

oznacza tabelę, z której chcemy uzyskać wiersze. Tabel może być

więcej i w takim przypadku rozdziela się je przecinkami. Jeśli nazwy tablic nie
są unikalne to należy stosować notację kropkową.

ƒ

za pomocą opcji i parametrów możemy znacząco skomplikować polecenie

SELECT

, i w ten sposób bardzo dokładnie określić, o które dane nam chodzi.

ƒ

wyniki zapytania

SELECT

możemy wyświetlić zarówno na terminalu, jak

również przekierować do pliku, dzięki czemu możemy wykonać backup
wybranych danych, chociaż są do tego odrębne i lepsze narzędzia.

ƒ

istnieją PARAMETRY, dzięki którym możemy wydobyć jedynie dane
niepowtarzające się; dzięki odpowiednim opcjom możemy dane sortować i
ograniczać ilość wyświetlanych danych,

ƒ

Dzięki słowu

WHERE

można znacząco poprawić precyzję naszego zapytania

do bazy danych. Po słowie

WHERE

możemy użyć dowolnych funkcji MySQL-a,

poza funkcjami agregującymi. Na przykład:

mysql> SELECT col1 FROM tab WHERE col1 > 0;

ƒ

Oprócz słowa

WHERE

możemy użyć podobnego w znaczeniu słowa

HAVING

.

Słowo

HAVING

jest interpretowane w zapytaniu dopiero wówczas, gdy dane

są wysyłane do klienta, który wysłal zapytanie do bazy, a więc już po
wykonaniu wyszukania danych przez serwer. Inaczej mówiąc używanie słowa

HAVING

powinno być raczej ograniczone gdyż nie poddaje się optymalizacji

zapytań do bazy danych. Słowo

HAVING

ma jednak tą zaletę, że można go

używać w przypadku funkcji agregujących, czego nie można zrobić przy
pomocy słowa

WHERE

mysql> SELECT nazwisko, MAX(wynagrodzenie) FROM pracownicy

GROUP BY nazwisko HAVING MAX(wynagrodzenie) > 10;

ƒ

Wyrażeniu

wyr

można nadać skrót (ang. alias) przy pomocy partykuły

AS

.

Taki skrót można dalej używać jako złożone wyrażenie po opcjach

GROUP

BY, ORDER BY, HAVING

.

mysql> SELECT CONCAT(nazwisko,', ',imie) AS imie_nazw FROM
tab ORDER BY imie_nazw;

ƒ

Słowo

AS

jest opcjonalne. Powyższy przykład można równie dobrze zapisać

jako:

mysql> SELECT CONCAT(nazwisko,', ',imie) imie_nazw FROM tab
ORDER BY imie_nazw;

ƒ

jednak może to rodzić pewne problemy, jeśli za pomocą polecenia

SELECT

wydobywa się dane z dwóch kolumn i zapomni się wstawić przecinka między
nazwami kolumn. Wtedy nazwa kolumny, która powinna wystąpić po
przecinku ale przecinka nie było, jest interpretowana jak skrót poprzedzającej
ją nazwy kolumny. Na przykład:

mysql> SELECT columna columnb FROM mytable;

ƒ

W tym przykładzie

columnb

jest traktowane jak skrót

columna

.

ƒ

Nazwa tablicy też może mieć skrót, co czasem poprawia czytelność złożonych
zapytań, i oczywiście skraca je. Skrót nazw tablic tworzy się używając składni

tablica AS skrot

, na przykład

mysql> SELECT t1.nazwa, t2.zarobki FROM pracownicy AS t1,
info AS t2 WHERE t1.nazwa = t2.nazwa;

mysql> SELECT t1.nazwa, t2.zarobki FROM pracownicy t1, info

t2 WHERE t1.nazwa = t2.nazwa;

ƒ

Wydobyte wiersze danych możemy sortować względem nazw kolumn,
skrótów nazw kolumn lub pozycji kolumn. Pozycje kolumn są liczbami
całkowitymi i zaczynają się od wartości 1. Sortowanie zględem pozycji kolumn
jest już przestarzałą właściwością i nie należy jej używać.

mysql> SELECT szkola, miasto, wynik FROM zawody ORDER BY

miasto, wynik;

mysql> SELECT szkola, miasto AS m, wynik AS w FROM zawody
ORDER BY m, w;

mysql> SELECT szkola, miasto, wynik FROM zawody ORDER BY 2,

3;

ƒ

Aby sortować w odwrotnej kolejności należy dodać słowo

DESC

(ang.

descending

) do nazwy kolumny po opcji

ORDER BY

. Domyślnie sortuje się

rosnąco, co zresztą można napisać explicte używając słowa

ASC

.

ƒ

Istnieją też inne sposoby modyfikacji sposobu sortowania i uzyskiwania przez
to odpowiednich danych, ale wybiegają one poza ten wykład. Na przykład
takim modyfikatorem jest

WITH ROLLUP

umieszczany po opcji

GROUP BY

kolumna

.

ƒ

Aby ograniczyć ilość wypisywanych danych można posłużyć się słowem

LIMIT

.

LIMIT

przyjmuj jeden lub dwa argumenty całkowite. Jeśli podamy

dwa rgumenty, to pierwszy oznacza od którego wiersza zaczniemy wyświetlać
dane, natomiast drugi oznacza ile wierszy wyświetlimy, przy czym w tym
wypadku wiersze nujmeruje ię od 0, a nie 1, zatem:

mysql> SELECT * FROM tab LIMIT 5,10; # zwraca wiersze 6-15

ƒ

Dla kompatybilności z baza PostgreSQL, MySQL ozumie też następującą
składnię

LIMIT ile_wierszy OFFSET od_ktorego

. Aby uzyskać

wszystkie wiersze począwszy od jakiegoś numeru, można posłużyć się
bardzo duża liczbą użytą jako drugi parametr słowa

LIMIT

. Na przykłąd aby

uzyskać wiersze od 96-ego do otatniego można napisać tak:

mysql> SELECT * FROM tab LIMIT 95,18446744073709551615;

ƒ

Kiedy podamy tylko jeden argument numeryczny dla słowa

LIMIT

, wtedy jest

on rozumiany jako liczba wierszy zaczynając liczenie od początku, innymi
słowy

LIMIT n

jest równoważne

LIMIT 0,n

. Na przykład:

mysql> SELECT * FROM tab LIMIT 5; # zwraca 5 pierwszych
wierszy

ƒ

Od wersji 4.1 MySQL-a można używać imitacji tablicy -

DUAL

, która w

rzeczywistości nie istnieje. Nie jest konieczne używanie nazwy

DUAL

kiedy

nie używamy żadnej tablicy, ale zostało to wprowadzone dla kompatybilności
z innymi bazami danych.

mysql> SELECT 1 + 1 FROM DUAL;

ƒ

Polecenie

SELECT ... INTO OUTFILE 'nazwa_pliku'

zapisuje

zwracane przez

SELECT

wiersze do pliku o nazwie

nazwa_pliku

. Plik jest

tworzony na serwerze z bazą danych, więc dany użytkownik musi mieć
odpowiednie prawa do zapisywania plików na serwerze. Plik o nazwie

nazwa_pliku

nie może wcześniej istnieć, co zabezpiecza chociażby takie

pliki jak /etc/passwd przed zniszczeniem ich zawartości. Wyrażenie

SELECT

... INTO OUTFILE

jest przeznaczone głównie do szybkiego zapisania

zawartości tablicy w pliku na serwerze bazodanowym. Jeśli zamierzamy
wyniki wykonania polecenia

SELECT

zapisać w pliku na serwerze klienta, to

należy posłużyć się raczej komendą

mysql -e "SELECT ..." > file_name

ƒ

Możemy użyć odpowiednich opcji w poleceniu

SELECT ... INTO

OUTFILE

, mianowicie:

o

FIELDS TERMINATED BY

o

ENCLOSED BY

o

ESCAPED BY

o

LINES TERMINATED BY

Na przykład:

mysql> SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt' FIELDS

TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED
BY '\n' FROM test_tab;

Uwaga! Jakikolwiek plik utworzony w wyniku wydania polecenia

INTO

OUTFILE

lub

INTO DUMPFILE

jest tworzony z prawami pisania w nim

przez wszystkich użytkowników systemu na serwerze bazodanowym.

ƒ

Jeśli zamiast

SELECT ... INTO OUTFILE

użyjemy polecenia

SELECT

... INTO DUMPFILE

MySQL zapisze w pliku tylko jedną długą linię, a

więc bez jakichkolwiek znaków końca linii czy kolumny. Jest to użyteczne w
przypadku używania danych typu

BLOB

.

ƒ

Za pomocą opcji

ALL, DISTINCT, DISTINCTROW

można określić czy

takie same wiersze, które zostaną odnalezione za pomocą polecenia

SELECT

mają być wypisywane czy też nie. Jeśli chcemy mieć tylko dane unikalne
możemy posłużyć się opcjami

DISTINCT

lub

DISTINCTROW

, jeśli zaś

chcemy uzyskać wszystkie dane, które zostały dopasowane za pomocą
polecenia

SELECT

możemy po prostu opuścić te opcje, lub explicite podać

opcję

ALL

.

ƒ

HIGH_PRIORITY

. Najczęściej z bazą danych pracuje jednocześnie kilka

(lub więcej) osób. Często zdaża się, że pracują na tych samych danych. Jeśli
jedna z nich dokonuje zmiany zawartości tablicy a druga chce w tym samym
czasie uzyskać dane z tej tablicy, to najpierw jest wykonywane polecenie
zmieniające dane w tablicy, a dopiero później za pomocą polecenia

SELECT

są wybierane (zaktualizaowane już w tym momencie) dane. Za pomocą
parametru

HIGH_PRIORITY

można nadać poleceniu

SELECT

wysoki

priorytet i w ten sposób uzyskać dane nawet wtedy kiedy tablica jest jakby
"zamknięta" (ang. locked) do czytania z powodu wykonywania zmian danych
w tej tablicy.

ƒ

STRAIGHT_JOIN

wymusza optymalizator zapytania do bazy (uruchamiany

jest zawsze w momencie wywoływania polecenia) do łączenia tablic w
kolejności takiej, w jakiej są wymieniane po słowie

FROM

. Można też użyć

tego parametru do przyspieszenia zapytania jeśli optymalizator łączy tabele w
kolejności nieoptymalnej.

ƒ

SQL_BIG_RESULT

można użyć w przypadku, kiedy używamy też

GROUP

BY

lub

DISTINCT

, aby poinformować optymalizator, że wynik zapytania

będzie się składał z dużej ilości danych. W takim wypadku MySQL użyje tzw.
tablic tymczasowych jeśli to będzie konieczne, co powinno przyspieszyć
uzyskanie danych i odciążyć serwer bazodanowy z konieczności użycia tzw.
pamięci swap.

ƒ

SQL_BUFFER_RESULT

wymusza użycia tablic do umieszczania w nich

wyników zapytania. Jest to szczególnie pomocne w przypadku, kiedy z dużą
bazą danych pracuje wielu użytkowników i kiedy często następuje
"zamykanie" dostępu do tablic z powodu zmiany danych w tabliach. W takich
wypadkach tablice tymczasowe pomagają szybciej "otwierać" "zamknięte"
tablice i dawać do nich dostęp.

ƒ

SQL_SMALL_RESULT

może być użyte razem z

GROUP BY

lub

DISTINCT

w celu użycia szybszych tablic tymczasowych zamiast sortowania.

ƒ

SQL_CALC_FOUND_ROWS

(parametr dostępny od wersji 4.0.0 MySQL-a)

wymusza policzenie ilości wszystkich wierszy zapytania (lekceważąc
ewentualną opcję

LIMIT

. Liczba znalezionych wierszy może być później

użyta do w funkcji

SELECT FOUND_ROWS()

i wykorzystana do yzyskania

tych wierszy.

ƒ

SQL_CACHE

pozwala zachować wyniki polecenia

SELECT

w specjalnym

buforze. To z kolei pomaga szybciej używać tych danych bez potrzeby
każdorazowego wykonywania wyszukiwania tych danych; jeśli jeszcze raz
użyjemy identycznego zpaytania, to serwer wysyła dane z bufora a nie
parsuje i wykonuje jeszcze raz polecenie wyszukania danych. Jest to
szczególnie przydatne kiedy mamy tablice, które nie zmieniają się zbyt często.
Jeśli w buforze znajdują się dane z ostatnio zbuforowanego zapytania i
wykonamy zmiany w tablicach, z których uzyskaliśmy dane, to serwer
wyczyści bufor i następne zapytanie, które tutaj korzystałoby z bufora, będzie
już normalnie traktowane.

ƒ

SQL_NO_CACHE

, odwrotnie niż

SQL_CACHE

powoduje nieumieszczanie

danych w buforze.

•

DELETE

Składnia polecenia

DELETE

jest mniej więcej następująca:

DELETE [PARAMETRY] FROM tabela [OPCJE]]

Składnia polecenia

DELETE

różni się nieznacznie w zależności od tego, czy chcemy

usunąć dane z jednej tablicy czy też z kilku tabel jednocześnie.

W przypadku usuwania danych z jednej tablicy:

DELETE

[LOW_PRIORITY] [QUICK] [IGNORE] FROM tabela

[WHERE definicje_skąd]

[ORDER BY ...]

[LIMIT liczba_wierszy]

W przypadku usuwania danych z kilku tablic:

DELETE

[LOW_PRIORITY] [QUICK] [IGNORE]

nazwa_tablicy[.*] [, nazwa_tablicy[.*] ...]

FROM nazwy_tablic

[WHERE definicje_skąd]

lub też

DELETE

[LOW_PRIORITY] [QUICK] [IGNORE]

FROM nazwa_tablicy[.*] [, nazwa_tablicy[.*] ...]

USING nazwy_tablic

[WHERE definicje_skąd]

Za pomocą polecenia

DELETE

możemy usunąć wiersze danych z tablicy, które

spełniają odpowiednie warunki wymienione w klauzuli

WHERE

. Polecenie

DELETE

zwraca liczbę usuniętych wierszy danych.

ƒ

Uwaga! Jeśli wykonamy polecenie

DELETE

bez klauzuli

WHERE

, wtedy

usunięte zostają wszystkie dane ze wskazanej tablicy. Znacznie szybszą
metodą usunięcia wszystkich danych z tablicy jest użycie polecenia

TRUNCATE TABLE nazwa_tablicy

, przy czym nie zostanie wtedy

zwrócona liczba usuniętych rekordów (wierszy).

ƒ

Jeśli w poleceniu

DELETE

podamy parametr

LOW_PRIORITY

wtedy

wykonanie

DELETE

jest opóźniane dopóty, dopóki nie wykonają się wszystkie

inne polecenia na danej tablicy.

ƒ

Jeśli w poleceniu

DELETE

podamy parametr

QUICK

w przypadku usuwania

danych z tablicy typu

MyISAM

, wtedy istnieje szansa przyspieszenia w

niektórych przypadkach usuwania danych.

ƒ

Parametr

IGNORE

powoduje ignorowanie wszytkich błędów pojawiających się

podczas usuwania rekordów. Błędy, które są ignorowane podczas
wykonywania

DELETE

z parametrem

IGNORE

są zgłaszane jako

ostrzerzenia.

ƒ

W przypadku tablic typu

MyISAM

, usuwane rekordy są zachowywane na

specjalnej liście, i bezpośrednio później wykonane polecenie

SELECT

zwraca

usunięte rekordy. Oznacza to, że usuwane dane zajmują cały czas zasoby

komputera. Można to zmienić używając polecenia

OPTIMIZE TABLE

lub

myisamchk

i zmieniając tablicę.

ƒ

Opcja

LIMIT liczba_wierszy

ogranicza liczbę usuwanych rekordów do

liczba_wierszy

.\ Przydatne jest to w przypadku, gdy chcemy się

upewnić, że nasze polecenie

DELETE

nie zajmie zbyt dużo czasu pracy bazy.

Można powtarzać to polecenie dopóty, dopóki nie zostanie przekroczona
liczba wierszy do usunięcia.

ƒ

Jeśli użyjemy polecenia

DELETE

razem z klauzulą

ORDER BY

wtedy

możemy usuwać rekordy w kolejności takiej, jaka zostanie obliczona po
wykonaniu sortowania. Jest to bardzo użyteczne w połączeniu z opcją

LIMIT

. Na przykład w poniższym przykładzie znajdowane są rekordy

pasujące do klauzuli

WHERE

, następnie sortowane względem danych typu

timestamp i usuwany jest tylko jeden z nich (najstarszy):

DELETE FROM jakas_tablica

WHERE user = 'kowalski'

ORDER BY timestamp

LIMIT 1;

ƒ

Można usuwać dane z kilku tabel jednocześnie. Pierwsza, podana wyżej,
składnia umożliwia usuwanie danych tylko z tablic wymienionych przed
klauzulą

FROM

. Druga składnia umożliwia usuwanie danych tylko z tych tablic,

które zostały wymienione przed klauzulą

USING

. Efekt jest taki, że można

usuwać dane z kilku tablic jednocześnie i również mieć dodatkowe tablice,
które są używane podczas szukania, ale z których nie usuwamy danych

mysql> DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND
t2.id=t3.id;

mysql> DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND
t2.id=t3.id;

ƒ

Powyższe polecenie używają trzech tablic do wyszukania odpowiednich
danych ale powdują usunięcie danych tylko z tablic

t1,t2

.

ƒ

Jeśli chcemy usuwać dane z kilku tabel relacyjnych typu

InnoDB

, w których

zdefiniowane są klucze obce, wtedy usuwanie rekordów poleceniem

DELETE

może odbywać się w innej kolejności niż powinno to wynikać z relacji
pomiędzy tablicami. W takim wypadku wykonanie polecenia

DELETE

nie

powiedzie się a ropoczęte usuwanie danych jest odwracane i dane sa
bezpieczne. W takim przypadku, można usuwać dane tylko z jednej tablicy i
polegać na możliwościach składni

ON DELETE

używanego przy konstrukcji

tablicy, która sprawi, że odpowiednie tablice relacyjne zostaną zmodyfikowane
jak należałoby się tego spodziewać (pod warunkiem że dobrze zdefiniujemy
relacje pomiędzy danymi w różnych tablicach!).

•

INSERT

Składnia polecenia

INSERT

jest mniej więcej następująca:

INSERT [PARAMETRY] tabela VALUES [OPCJE]]

a dokładniej

INSERT

[LOW_PRIORITY | DELAYED] [IGNORE]

[INTO] tabela [(kol1,kol2,...)]

VALUES ({wyr | DEFAULT},...),(...),...

[ ON DUPLICATE KEY UPDATE kol=wyr, ... ]

lub:

INSERT

[LOW_PRIORITY | DELAYED] [IGNORE]

[INTO] tablica

SET kol={wyr | DEFAULT}, ...

[ ON DUPLICATE KEY UPDATE kol=wyr, ... ]

lub też:

INSERT

[LOW_PRIORITY | DELAYED] [IGNORE]

[INTO] tablica [(kol1,kol2,...)]

SELECT ...

Za pomocą polecenia

INSERT

możemy wstawić nowe rekordy danych do istniejącej

tablicy.

Składnia

INSERT ... VALUES

oraz

INSERT ... SET

wstawiają nowe rekordy

jeśli dokładnie podamy wszystkie wartości (do wszystkich pól tablic). Składnia

INSERT ... SELECT

wstawia rekordy wybrane z innej tablicy lub z innych tablic.

Nazwy kolumn, do których będziemy definiować dane można zadać dokładnie albo
podając w poleceniu

INSERT

listę nazw kolumn

(kol1,kol2,...)

albo stosując

drugą z wymienionych składnię , a więc używając słowa

SET

. Jeśli nie chcemy

podawać nazw kolumn (bo na przykład może być ich dużo) to nie musimy tego robić;
wystarczy jeśli podamy poprawnie listę wartości, ale bezwględnie ważna jest tutaj
kolejność wartości na liście wartości

VALUES()

. Jeśli nie znamy struktury tablicy

albo kolejności pól (kolumn) to możemy posłużyć się poleceniem

DESCRIBE

nazwa_tablicy

.

Wartości kolumn możemy podawać na kilka różnych sposobów:

ƒ

jeśli nie podamy wartości dla kolumny to przyjmowana jest wartość domyślna,
zdefiniowana podczas definicji struktury tablicy. Możemy na przykład nie
podać wartości wszystkich pól (kolumn) tablicy. W takim przypadku MySQL
wypełni wartościami domyślnymi wszystkie pola wstawianego rekordu.
MySQL ma zawsze zdefiniowane wartości dla wszystkich kolumn.
Nienadawanie wartości polom rekordu nie jest jednak dobrym nawykiem, gdyż

z bazy danych korzystają aplikacje, które najczęściej oczekują konkretnych
danych, a nie wartości domyślnych.

Przykład:

mysql> INSERT INTO nazwa_tab () VALUES();

ƒ

Począwszy od wersji 4.0.3 MyQL-a można użyć słowa

DEFAULT

aby

dosłownie nadać wartość domyślną polu rekordu.

Przykład:

mysql> INSERT INTO nazwa_tab VALUES
(DEFAULT,DEFAULT,'Kowalski','18','1','0',DEFAULT);

ƒ

Jeśli typ danych, które chcemy wstawić do tablicy nie odpowiada typowi
kolumny zadeklarowanemu podczas tworzenia tablicy, wtedy może nastąpić
domyślna konwersja do odpowiedniego typu danych. Na przykład próba
wstawienia stringu

'1999.0e-2'

do kolumn typu

INT, FLOAT,

DECIMAL(10,6)

lub

YEAR

spowoduje wstawienie odpowiednio

1999,

19.9921, 19.992100

, i

1999

. Powodem, dla którego w kolumnach typu

INT

i

YEAR

znajdzie się 1999 jest to, że funkcja dokonująca konwersji typu

string-to-integer "patrzy" tylko na początek stringu

'1999.0e-2'

i "widzi",

że pasuje do formatu liczby całkowutej albo roku. W przypadku kolumn typu
zmiennoprzecnikowego lub stałoprzecinkowego funkcja dokonująca konwersji
typu string-to-floating-point i string-to-fixed-point "patrzy" na cały string jak na
potencjalną liczbę o właściwej konsrukcji.

ƒ

Wyrażenie

wyr

może się odnosić do dowolnej kolumny, dla której wartość

zdefiniowano wcześniej (w tej samej instrukcji

INSERT

).

Na przykład:

mysql>INSERT INTO tab (col1,col2) VALUES(15,col1*2);

Tutaj można się było odwołać do

col1

, gdyż

col1

było zdeiniowane

wcześniej niż

col2

.

Przykład 2:

mysql> INSERT INTO tab (col1,col2) VALUES(col2*2,15);

To wyrażenie jest niepoprawne, gdyż wartość

col1

odnosi się do

col2

,

która nie została jeszcze zdefiniowana.

Można stosować następujące parametry wywołania polecenia

INSERT

:

ƒ

Parametr

DELAYED

powoduje wstawienie wiersza lub wierszy do buforu.

Dane z fubora są wstawiane do tablicy w momencie, kiedy jest ona "wolna",
tzn. nie ma do niej w danej chwili żadnych zapytań. Sprawdzanie jest
dokonywane cyklicznie. Jeśli w wyniku któregoś sprawdzenia okaże się, że są
zapytania do danej tablicy, wtedy polecenie

INSERT

jest wstrzymywane do

momentu aż nie nastąpi "zwolnienie" tablicy.

ƒ

Parametr

LOW_PRIORITY

powoduje opóźninie wykonania polecenia

INSERT

doputy, dopóki klienci czytają dane z danej tablicy. Może to

spowodować długie czekanie na wykonanie

INSERT LOW_PRIORITY

, w

przypadku kiedy baza jest mocno obciążona zapytaniami do tablicy. W
przeciwieństwie do opcji

LOW_PRIORITY

opcja

DELAYED

pozwala klientowi

kontynuować natychmiast, kiedy tylko zwolni się dostęp do tablicy.

ƒ

Parametr

HIGH_PRIORITY

powoduje zaniechanie konsekwencji jakie

pociąga ze sobą uruchomienie bazy danych z opcją

--low-priority-

updates

. Baza danych uruchomiona z taką opcją domyślnie pozwala

wykonywać polecenia zmiany danych w tablicach (polecenia

INSERT,

UPDATE

tak, jakby były one wykonywane z opcją

LOW_PRIORITY

.

ƒ

Parametr

IGNORE

powoduje ignorowanie wstawianych rekordów jeśli

zawierają istniejący już w wierszu/ach tablicy indeks

UNIQUE

lub

PRIMARY

KEY

. Jeśli uruchomimy polecenie

INSERT

bez tego parametru i znajdzie się

wiersz, który duplikuje istniejący klucz

UNIQUE

lub

PRIMARY KEY

wtedy

całe polecenie wstawiające kilka wierszy jest przerywane. Jest to dość
kłopotliwa sytuacja gdyż wtedy najczęściej trzeba zliczyć w jakiś sposób ile
rekordów zostało do bazy wstawionych, i które rekordy zostały wstawione.

Jeśli do polecenia

INSERT

dopiszemy

ON DUPLICATE KEY UPDATE

wtedy

zamiast przerywania wykonania całego polecenia

INSERT

lub omijania takich

rekordów, które duplikują wartość indeksu

UNIQUE

lub

PRIMARY KEY

, następuje

UPDATE

rekordu.

Na przykład jeśli kolumna

a

jest zadeklarowana jako

UNIQUE

i już zawiera wartość

1, to poniższe dwa wyrażenia dają ten sam efekt:

mysql> INSERT INTO tab (a,b,c) VALUES (1,2,3) ON DUPLICATE
KEY UPDATE c=c+1;

mysql> UPDATE tab SET c=c+1 WHERE a=1;

Uwaga. Jeśli kolumna

b

również jest zadeklarowana jako

UNIQUE

wtedy polecenie

INSERT

może być równoważne następującemu poleceniu

UPDATE

:

mysql> UPDATE tab SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

Jeśli

a=1 OR b=2

pasuje do kilku (kilkunastu) wierszy wtedy trzeba pamiętać, że

tylko jeden wiersz jest zmieniany instrukcją

UPDATE

. Lepiej więc zabezpieczać się

przed takimi wypadkami definiując odpowiednio tablice używając klauzuli

ON

DUPLICATE KEY

niż wierzyć że jeśli się coś stanie to

ON DUPLICATE KEY

UPDATE

uratuje nas przecież.

Od wersji 4.1.1 MySQLa można używać funkcji

VALUES(col)

w klauzuli

ON

DUPLICATE KEY UPDATE

odnosząc się do kolumn z części

INSERT ...

UPDATE

. Innymi słowy

VALUES(col)

w klauzuli

ON DUPLICATE KEY UPDATE

odnosi się do wartości kolumny

col

, która mogłaby zostać wstawiona jeśli nie

zdarzyłby się konflikt duplikacji klucza. Jest to szczególnie użyteczne, kiedy
wstawiamy dane do wielu wierszy za pomoca jednego polecenia

INSERT

.

Na przykład:

mysql> INSERT INTO tab (a,b,c) VALUES (1,2,3),(4,5,6) ON

DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

Takie wyrażenie jest równoważne następującym dwom wyrażeniom:

mysql> INSERT INTO tab (a,b,c) VALUES (1,2,3) ON DUPLICATE

KEY UPDATE c=3;

mysql> INSERT INTO tab (a,b,c) VALUES (4,5,6) ON DUPLICATE
KEY UPDATE c=9;

Jeśli używamy opcji

ON DUPLICATE KEY UPDATE

wtedy automatycznie

parametr

DELAYED

jest ignorowany.

W przypadku kolumn zadeklarowanych jako

AUTO_INCREMENT

można dowiedzieć

się, jaka była ostatnia wartość takiej kolumny posługując się funkcją

LAST_INSERT_ID()

.

Jeśli posługujemy się wrażeniem

INSERT ... VALUES

z wieloma listami

wartości, albo kiedy posługujemy się wyrażeniem postaci

INSERT ... SELECT

wtedy baza zwróci nam informację mniej więcej następującej postaci:

Records: 100 Duplicates: 0 Warnings: 0

Records

wskazuje na liczbę analizowanych rekordół (to niekoniecznie musi być

liczba wstawionych rekordów).

Duplicates

wskazuje na liczbę rekordów, które nie

mogły być wstawione ze względu na duplikację wartości unikalnych.

Warnings

wskazuje na liczbę prób wstawienia wartości do kolumn, które z jakichś powodów
były problematyczne.

Warnings

mogą się zdarzyć w następujących przypadkach:

o

Kiedy chcemy wstawić wartość

NULL

do kolumny, która była zadeklarowana

jako

NOT NULL

. W przypadku polecenia

INSERT

wielowierszowego lub

polecenia

INSERT ... SELECT

wartość dla tej kolumny jest ustawiana na

podstawie wartości domyślnej dla tej kolumny, a zatem 0 dla typów
numerycznych, pusty string '' dla typów znakowych, oraz ``zero'' dla kolumn
typu data i czas.

o

Kiedy chcemy wstawić wartość, która przekracza dopusczalny zakres wartości
dla danej kolumny. Następuje wtedy obcięcie do najbliższej z możliwych
wartości. Dotyczy to zarówno wartości liczbowych, jak i znakowych.

o

Kiedy chcemy wstawić wartość zupełnie innego typu, niż wynika to z definicji
typu kolumny, np. kiedy chcemy wstawić

'10.34 a'

do kolumny

numerycznej. Ogn tego strinu (nienumeryczny) jest odciety i dostaje się do

bazy tylko część numeryczna. Jeśli string nie miałby w sobie nic z cyfry, wtedy
wstawiona by była wartość 0.

o

Kiedy chcemy wstawić wartość do kolumny typu

date

lub

time

, która jest

nieodpowiednia dla tego typu kolumny. Wtenczas wartość kolumny jest
ustawiana domyślnie jako odpowiednie ``zero''.

•

UPDATE

Składnia polecenia

UPDATE

zależy od tego, w ilu tablicach dokonujemy zmiany

danych. I tak:

w przypadku jednej tablicy:

UPDATE

[LOW_PRIORITY] [IGNORE] nazwa_tab

SET nazwa_kol1=wyr1 [, nazwa_kol2=wyr2 ...]

[WHERE definicja_where]

[ORDER BY ...]

[LIMIT liczba_wierszy]

a w przypadku wielu tablic:

UPDATE

[LOW_PRIORITY] [IGNORE] nazwa_tab1 [, nazwa_tab2 ...]

SET nazwa_kol1=wyr1 [, nazwa_kol2=wyr2 ...]

[WHERE definicja_where]

Za pomocą polecenia

UPDATE

możemy zmienić zawartość kolumn w istniejących

wierszach tablicy. Słowo

SET

wskazuje, które kolumny będziemy zmieniać oraz co

będzie ich nową zawartością. Jeśli w poleceniu

UPDATE

wpiszemy słowo

WHERE

to

możemy określić, w których wierszach dokonamy zmian, przy czym obowiązują tutaj
zwyczajne zasady wyboru odpowiednich wierszy, tak jak w innych poleceniach SQL,
a w szczególności w przypadku polecenia

SELECT

. Jeśli nie podamy opcji

WHERE

,

wtedy zostaną zmienione wszystkie wiersze (uwaga! niebezpieczne). Podobnie działa
opcja

LIMIT

; ogranicza liczbę wierszy, które ulegną zmianie podczas wykonywania

polecenia

UPDATE

.

ƒ

W przypadku słowa

UPDATE

możemy używać następujących dwóch

parametrów:

1. parametr

LOW_PRIORITY

powoduje opóźnienie wykonania polecenia

UPDATE

do momentu zakończenia wykonywania przez bazę

wszystkich innych poleceń pobierania danych z danej tablicy.

2. parametr

IGNORE

powoduje nieprzerywanie polecenia

UPDATE

w

przypadku, kiedy wystąpią błędy podczas zmiany wartości kolumn
(tzw. błędy duplikatu klucza). W takim wypadku wiersze, dla których
występują konflikty, nie są zmieniane.

ƒ

Polecenie

UPDATE

zapamiętuje bierzącą (przed zmianą) wartość danej

kolumny. Na przykład, jeśli chcemy zwiększyć wartość kolumny o 2 to
możemy wykonać następujące polecenie:

mysql> UPDATE dane_personalne SET wiek=wiek+1;

ƒ

Przypisania (po słowie

SET

są wykonywane w poleceniu

UPDATE

od lewej do

prawej. W przykłaszie poniżej najpierw następuje pomnożenie wartości
kolumny przez 2, a dopiero później zwiększenie wartości kolumny o 1:

mysql> UPDATE dane_personalne SET wiek=wiek*2, wiek=wiek+1;

ƒ

Jeśli próbujemy nadać kolumnie nową wartość, która jest taka sama jak
poprzednia, to MySQL zasygnalizuje ten fakt.

ƒ

Jeśli zmieniamy kolumny, które przy definicji tablicy były zadeklarowane jako

NOT NULL

wstawiając wartość

NULL

, wtedy MySQL wstawia do takiej

kolumny wartość domyślną i wypisuje ostrzeżenie. Wartościami domyślnymi
są (chyba, że przy definicji struktury tablicy zaznaczono inaczej): 0 dla kolumn
typu numerycznego, pusty string '' dla kolumn typu znakowego, oraz wartość
``zero'' dla kolumn typu data i czas.

ƒ

Polecenie

UPDATE

zwraca liczbę wmienionych rekordów (wierszy).

ƒ

Zaczynając od wersji 3.23 MySQLa możemy używać opcji

LIMIT

liczba_wierszy

w celu ograniczenia liczby wierszy, które zmienimy

poleceniem

UPDATE

. Opcja

LIMIT liczba_wierszy

działa

następująco:

0. Przed wersją 4.0.13 MySQLa polecenie

UPDATE

zatrzymuje się w

momencie kiedy zostanie zmienionych

liczba_wierszy

wierszy,

które spełniały warunki wymienione po opcji

WHERE

.

1. Od wersji 4.0.13 MySQLa polecenie

UPDATE

zatrzymuje się w

momencie kiedy zostanie znalezionych

liczba_wierszy

wierszy,

które spełniały warunki wymienione po opcji

WHERE

, bez względu na

to, czy zostały one zmienione czy też nie zostały zmienione.

ƒ

Jeśli użyjemy opcji

ORDER BY

wtedy wiersze będą zmieniane w kolejności

takiej jak podano w to w tej opcji.

ƒ

Od wersji 4.04 MySQLa za pomocą jednego polecenia

UPDATE

możemy

zmieniać dane w kilku tablicach:

mysql> UPDATE jednostki, miesiace SET
jednostki.cena=miesiace.cena WHERE jednostki.id=miesiace.id;

ƒ

Uwaga. Przy poleceniu

UPDATE

działającym na kilku tablicach, nie możemy

używać opcji

ORDER BY

i

LIMIT

.

ƒ

Aby wykonać zmiany w tablicy musimy mieć odpowiednie przywileje do
dokonywania takich zmian.

ƒ

Kiedy chcemy zmienić dane w kilku tablicach typu

InnoDB

, dla których

zdefiniowane są klucze klucze obce, wtedy najprawdopodobniej MySQL
wykona

UPDATE

w innej kolejności niż wynikałoby to z relacji rodzic/dziecko.

W takim wypadku najprawdopodobniej polecenie

UPDATE

się nie powiedzie i

dane nie zostaną zmienione. W takich wypadkach lepiej jest zmienić dane w
jednej tablicy i polegać na możliwościach tablic typu

InnoDB

, dzięki którym

tablice z kluczami obcymi zostaną zmienione w sposób odpowiedni.

•

REPLACE

Składnia polecenia

REPLACE

jest następująca

REPLACE

[LOW_PRIORITY | DELAYED]

[INTO] nazwa_tab [(nazwa_kol1,nazwa_kol2...)]

VALUES ({wyr | DEFAULT},...),(...),...

lub

REPLACE

[LOW_PRIORITY | DELAYED]

[INTO] nazwa_tab

SET nazwa_kol={wyr | DEFAULT}, ...

lub

REPLACE

[LOW_PRIORITY | DELAYED]

[INTO] nazwa_tab [(nazwa_kol,...)]

SELECT ...

ƒ

Polecenie

REPLACE

działa dokładnie tak samo jak

INSERT

, z tym, że jeśli w

tablicy

nazwa_tab

istnieje już rekord o takiej samej wartości

PRIMARY

KEY

lub indeksu

UNIQUE

, to poprzedni rekord jest najpierw usuwany a

dopiero wtedy wstawiany nowy. Jest to więc polecenie, które powoduje
bardziej bezpośrednie skutki niż polecenie

UPDATE

. Z tego powodu jest więc

i bardziej niebezpieczne i wygodniejsze, bowiem jeśli nie ma danego rekordu
w tablicy a chcielibyśmy wykonać

UPDATE

tego rekordu, to polecenie

UPDATE

się nie wykona. natomiast polecenie

REPLACE

wykona się. Czasem

jest to pożądane działanie a czasem nie jest. Należy umieć rozróżnić te dwa
przypadki i wybrać odpowiednie rozwiązanie (albo

UPDATE

albo

REPLACE

).

ƒ

Należy zauważyć, że polecenie

REPLACE

nie ma sensu, jeśli tablica nie

zawiera klucza podstawowego (

PRIMARY KEY

) lub unikalnego (

UNIQUE

).

Wtedy po prostu polecenie

REPLACE

jest równoważne poleceniu

INSERT

,

gdyż nie ma indeksu, na podstawie którego można określić czy nowy rekord
zastąpi poprzedni.

ƒ

Wartości wszystkich kolumn sa tak jak w przypadku polecenia

INSERT

podawane w wyrażeniu. Jeśli pominiemy jakieś kolumny, to ich wartości
zostaną nadane na podstawie wartości domyślnych zdefiniowanych podczas
definiowania struktury tablicy.

ƒ

Aby można było używać polecenia

REPLACE

musimy mieć prawo do

wykonywania poleceń

INSERT

oraz

DELETE

dla danej tabeli.

ƒ

Polecenie

REPLACE

zwraca liczbę zmienionych rekordów (usuniętych starych

i wstawionych nowych). Jeśli ta liczba wynosi 1 (dla pojedynczego polecenia

REPLACE

działającego na jednym rekordzie), to oznacza to, że został

wstyawiony jeden nowy rekord do tablicy, ale nie został usunięty żaden stary
rekord. Jeśli natomiast ta liczba jest większa niż 1, to oznacza to, ze zostało

usuniętych 1 lub więcej nowych rekordów zostało usuniętych zanim zostały
wstawione nowe rekordy. Inaczej mówiąc, jeśli

REPLACE

zwraca 1 to znaczy,

że rekord został dodany, natomiast jeśli

REPLACE

zwróci więcej niż 1, to

zostyał zamieniony.

ƒ

Należy zauważyć, że może się zdażyć sytuacja, w której za pomocą jednego
polecenia

REPLACE

, przeznaczonego teoretycznie dla jednego rekordu,

zostanie zastąpionych więcej niż jeden starych rekordów. Stanie się tak
wówczas, gdy tablica zawiera wielokrotne, unikalne indeksy i nowy rekord
duplikuje wartości dla różnych, starych rekordów z różnymi unikalnymi
indeksami.

ƒ

Algorytm stosowany przez MySQL podczas wykonywania polecenia

REPLACE

jest następujący:

0. Spróbuj wstawić nowy rekord do tablicy.
1. Jeśli próba wstawienia nowego rekordu nie powiodła się z powodu

duplikacji klucza podstawowego lub unikalnego, to:

1. usuń z tablicy rekord, który powoduje konflikt z powodu takiego

samego klucza podstawowego lub unikalnego;

2. spróbuj ponownie wstawić nowy rekord do tablicy.

•

DO

Składnia polecenia

DO

jest następująca

DO wyr [, wyr] ...

Polecenie

DO

wykonuje po prostu wyrażenie

wyr

, jednak nie zwraca żadnych

wartości. Jest to właściwie swoisty skrót od

SELECT wyr ...

jednak ma tą

zaletę, że jest wyraźnie szybsze. Stosowane jest wówczas gdy chcemy znacznie
przyspieszyć wykonywanie polecenia

SELECT

i nie zależy nam na tym, żeby

zobaczyć wynik. Jest to więc dość rzadki przypadek.

Polecenie

DO

wykonuje się zatem wtedy, gdy posługujemy się jakimiś funkcjami,

które posiadają tzw. efekty uboczne, tzn. wykonują pewne zadanie, i zwracają wynik,
ale sam wynik nas nie interesuje. Na przykład funkcja

RELEASE_LOCK()

, która

zwalnia dostęp do bazy, kiedy wcześniej został on zamknięty funkcją

GET_LOCK()

.

Przykład:

DO RELEASE_LOCK('string');

•

TRUNCATE

Składnia polecenia

TRUNCATE

jest następująca

TRUNCATE TABLE nazwa_tablicy;

Polecenie

TRUNCATE TABLE

usuwa zupełnie wszystkie dane z tablicy o nazwie

nazwa_tablicy

. Logicznie, jest to ekwiwalent polecenia

DELETE

usuwającego

wszystkie rekordy, jednak są praktyczne różnice. W przypadku tablic typu

InnoDB

polecenie

TRUNCATE TABLE

jest zamieniane na odpowiednie polecenie

DELETE

,

więc w tym przypadku nie ma zupełnie różnic, jednak w innych przypadkach są
różnice:

ƒ

Operacja

TRUNCATE

usuwa zupełnie i tworzy od nowa tablicę co jest

znacznie szybsze niż usuwanie rekordu po rekordzie.

ƒ

Operacje

TRUNCATE

nie są bezpieczne w przypadku transakcji; baza na

pewno zgłosi błąd jeśli mamy aktywną transakcję lub zamknięty dostęp do
tablicy.

ƒ

Nie jest zwracana liczba usuniętych rekordów.

ƒ

Tak długo jak plik z definicją tablicy (plik '

nazwa_tablicy.frm

') jest

nieuszkodzony, tak długo można wykonać polecenie

TRUNCATE TABLE

i w

ten sposób odtworzyć pustą tabelę nawet w przypadku kiedy dane z tabeli lub
indeksy są uszkodzone.

ƒ

Uchwyt talicy, którym posługuje się wewnętrznie MyQL nie pamięta ostatnio
użytej wartości

AUTO_INCREMENT

ale zaczynać odliczać od początku.

Polecenie

TRUNCATE TABLE

pochodzi od rozszerzenia języka SQL, które

wprowadziła firma Oracle.

Podzapytania

Podzapytanie jest wyrażeniem

SELECT

wewnątrz jakiegoś innego polecenia.

Przykład:

mysql> SELECT * FROM tab1 WHERE col1 = (SELECT col1 FROM
tab2);

W tym przykładzie

SELECT * FROM tab1

jest zapytaniem zewnętrznym, natomiast

SELECT col1 FROM tab2

jest podzapytaniem. Mówi się, że podzaptanie jest

zagnieżdżone w zapytaniu zewnętrznym, przym możliwe jest zagnieżdżanie wielokrotne
(głębokie). Podzapytanie musi być zawsze otoczone nawiasami okrągłymi.

Główne zalety stosowania podzapytań:

•

Pozwalają na izolację części zapytań, co czyni je klarowniejszymi, i umożliwia
oddzielenie uzyskiwania jednych informacji od drugich w jednym zapytaniu.

•

Dostarczają mechanizmów, dzieki którym można stosować często czytelniejsze
metody uzyskiwania danych, które inaczej musiałyby być uzyskiwane przy pomocy
znacznie bardziej skomplikowanych i mało czytelnych zapytań.

•

W opini wielu użytkowników podzapytań, podzapytania są bardziej czytelne niż
odpowiednie polecenia zbudowane bez używania podzapytań. W rzeczy samej, to z
powodu podzapytań nazwano język służący do operacji na bazach danych językiem
SQL - STRUKTURALNYM JĘZYKIEM ZAPYTAŃ (ang. Structured Query Language).

Oto przykład ilustrujący sposób używania podzapytań:

mysql> DELETE FROM t1

WHERE s11 > ANY

(SELECT COUNT(*) /* komentarz */ FROM t2

WHERE NOT EXISTS

(SELECT * FROM t3

WHERE ROW(5*t2.s1,77)=

(SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM

(SELECT * FROM t5) AS t5)));

•

Podzapytanie jako argument skalarny

W najprostszym przypadku podzaptanie jest prostym argumentem. Możemy więc go
używać wszędzie tam, gdzie tylko można użyć wartości z kolumny lub po prostu
jakiejś wartości dosłownej. Podczas używania więc podzapytań możemy się
spodziewać, że podzapytanie będzie miało wszystkie te właściwości, które mają
zwykłe argumenty: typ danych, długośc, wskazanie, czy może być wartością

NULL

itd.

Na przykład:

mysql> CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);

SELECT (SELECT s2 FROM t1);

Podzapytanie w tym poleceniu

SELECT

jest typu

CHAR

, ma długość 5 znaków, i

może mieć wartość

NULL

. W zasadzie prawie wszystkie podzapytania mogą mieć

wartość

NULL

, gdyż jeśli tablica jest pusta, tak jak w przykładzie powyżej, wtedy

wartość podzapytania będzie równa

NULL

.

Jest kilka ograniczeń związanych z używaniem podzapytań:

o

Zapytanie zewnętrzne (wyrażenie zewnętrzne) może być jednym z:

SELECT,

INSERT, UPDATE, DELETE, SET, DO

o

Podzapytanie może zawierac jakiekolwiek ze słów kluczowych, które może
zwierac zwykłe polecenie

SELECT

:

DISTINCT, GROUP BY, ORDER

i inne formy wyrażeń języka SQL.

BY, LIMIT

•

Porównywanie przy pomocy podzapytań

Podzapytania używa się najczęściej w celu porównywania, a więc:

jakis_argument operator (podzapytanie)

gdzie operator może być jedenm z:

= > < >= <= <>

Na przykład:

mysql> ... 'a' = (SELECT column1 FROM t1);

Następny przykład:

mysql> SELECT col1 FROM tab1 WHERE col1 = (SELECT MAX(col2)

FROM t2);

Następny przykład:

mysql> SELECT * FROM tab1 AS t WHERE 2 = (SELECT COUNT(*)

FROM tab1 WHERE t1.id = t.id);

Takich zapytań (jak z dwóch ostatnich przykładów) nie da sie zapisać bezpośrednio
inaczej niż przy pomocy podzapytań. Innym rozwiązaniem jest użycie dwóch
osobnych zapytan.

•

Podzapytania z ANY, IN, oraz SOME

Składnia:

argument operator_porównania ANY (podzapytanie)

argument IN (podzapytanie)

argument operator_porównania SOME (podzapytanie)

Słowo kluczowe

ANY

, które musi wystąpić po operatorze porównującym, oznacza

zwróć

TRUE

jeśli w wyniku porównania otrzymamy

TRUE

dla któregokolwiek z

wierszy zwróconych przez podzapytanie,

Na przykład:

mysql> SELECT s1 FROM tab1 WHERE s1 > ANY (SELECT s1 FROM

tab2);

Przypuśćmy, że jest wiersz w tabeli

t2

, kóry zawiera

(10)

. Wyrażenie powyższe

jest wtedy równe

TRUE

, gdy tablica

t2

zawiera

(21,14,7)

, gdyż w

t2

jest

wartość

7

, która jest mniejsza od

10

. Powyższe wyrażenie jest równe

FALSE

, gdy

tablica

t2

zawiera

(20,10)

, lub gdy tablica

t2

jest pusta. Wyrażenie ma wartość

UNKNOWN

jeśli tablica

t2

zawiera

(NULL,NULL,NULL)

.

Słowo

IN

jest skrótem od

= ANY

. Zatem dwa poniższe wyrażenia są równoważne:

mysql> SELECT s1 FROM tab1 WHERE s1 = ANY (SELECT s1 FROM

tab2);

mysql> SELECT s1 FROM tab1 WHERE s1 IN (SELECT s1 FROM tab2);

Słowo

SOME

jest skrótem od słowa

ANY

. Zatem dwa poniższe wyrażenia są

równoważne:

mysql> SELECT s1 FROM tab1 WHERE s1 <> ANY (SELECT s1 FROM

tab2);

mysql> SELECT s1 FROM tab1 WHERE s1 <> SOME (SELECT s1 FROM

tab2);

•

Podzapytania z ALL

Składnia:

argument operator_porównania ALL (podzapytanie)

Słowo

ALL

które musi wystąpić po operatorze porównania, znaczy: zwróć prawdę

(TRUE) jeśli porównanie jest prawdziwe dla wszystkich wierszy, które zwraca
podzapytanie.

Na przykład:

mysql> SELECT col1 FROM tab1 WHERE col1 > ALL (SELECT col1
FROM tab2);

Przypuśćmy, że w tablicy

tab1

jest wiersz zawierający wartość

(10)

w kolumnie

col1

. Powyższe wyrażenie jest prawdziwe (

TRUE

) jeśli tablica

tab2

zawiera

(-

5,0,+5)

, gdyż oczywiście 10 jest większe od każdej z tych trzech wartości tablicy

tab2

. Wyrażenie to będzie nieprawdziwe (

FALSE

jeśli tablica

tab2

zawiera

(12,6,NULL,-100)

, gdyż wartość 12 z tablicy

tab2

jest większe od 10.

Powyższe wyrażenie ma wartość

UNKNOWN

jeśli tablica

tab2

zawiera

(0,NULL,1)

Ponadto, jeśli tablica

tab2

jest pusta, to wynikiem powyższego zapytania będzie

TRUE

. Można by się spodziewać, że wynik powinien być

UNKNOWN

, jednak w tym

wypadku wynik jest zawsze

TRUE

. Tak więc w poniższym przykładzie wynik jest

równy

TRUE

mysql> SELECT * FROM tab1 WHERE 1 > (SELECT col1 FROM tab2);

Natomiast poniższe wyrażenie ma wartość

UNKNOWN

jeśli

tab2

jest pusta:

mysql> SELECT * FROM tab1 WHERE 1 > (SELECT col1 FROM tab2);

Ponadto, następujące wyrażenie ma wartość

UNKNOWN

jeśli

tab2

jest pusta:

mysql> SELECT * FROM tab1 WHERE 1 > ALL (SELECT MAX(col1)
FROM tab2);

•

Podzapytania skorelowane

Podzapytanie skorelowane jest podzapytaniem, które odwołuje się do tablicy, która
występuje też w zapytaniu zewnętrznym.

Na przykład:

mysql> SELECT * FROM tab1 WHERE col1 = ANY

(SELECT col1 FROM tab2 WHERE tab2.col2 = tab1.col2);

Należy zauważyć, że zapytanie zawiera odwołanie do kolumn tablicy

tab1

, pomimo

tego, że podzapytanie po słowie

FROM

nie wspomina ani słowem o tablicy

tab1

.

MySQL patrzy więc na zapytanie zewnętrzne i znajduje tam

tab1

.

Przypuśćmy, że tablica

tab1

zawiera wiersz gdzie

col1

= 5, a

col2

= 6, podczas

gdy tablica

tab2

zawiera wiersz, w którym

col1

= 5 a

col2

= 7. Proste wyrażenie

... WHERE col1 = ANY (SELECT col1 FROM tab2)

mogłoby zwrócić

TRUE

, jednak w tym przykładzie wartość wyrażenia po słowie

WHERE

w

podzapytaniu jest równe

FALSE

(gdyż

(5,6)

nie jest równe

(5,7)

), tak więc w

powyższym przykładzie cyrażenie jako całość jest równe

FALSE

.

Ważna uwaga:

MySQL analizuje wyrażenie zaczynając od wewnątrz (podzapytanie) i kończy analizę
na zewnątrz (zapytanie zewnętrzne).

Na przykład:

mysql>SELECT col1 FROM tab1 AS x WHERE x.col1 = (SELECT col1
FROM tab2 AS x WHERE x.col1 = (SELECT col1 FROM tab3 WHERE
x.col2 = tab3.col1));

W tym przykładzie wyrażenie

x.col2

musi być kolumną w tablicy

tab2

ponieważ

SELECT col1 FROM tab2 AS x ...

"zmienia nazwę" tablicy

tab2

. Nie jest

jednak kolumną w tablicy

tab1

gdyż

SELECT col1 FROM tab1 ...

jest

zapytaniem zewnętrznym, które "znajduje się znacznie dalej".

W przypadku zapytań ze słowami

HAVING

lub

ORDER BY

, MySQL szuka też nazw

kolumn w liście kolumn zapytania zewnętrznego.

•

EXISTS i NOT EXISTS

Jeśli podzapytanie zwraca jakąkolwiek wartość, wtedy wyrażenie

EXISTS

podzapytanie

zwraca wartość

TRUE

, natomiast

NOT EXISTS

podzapytanie

zwraca

FALSE

.

Na przykład:

mysql> SELECT col1 FROM tab1 WHERE EXISTS (SELECT * FROM
tab2);

W przykładzie tym, jeśli

tab2

zawiera jakiekolwiek wiersze, nawet wiersze, kóre nie

zwierają nic innego poza wartością

NULL

wtedy warunek

EXISTS

jest zawsze równy

TRUE

.

Następne przykłady:

ƒ

Jaki rodzaj sklepu występuje w jednym lub więcej miejscowościach?

mysql> SELECT DISTINCT rodzaj_sklepu FROM sklepy

WHERE EXISTS (SELECT * FROM miasta_sklepy

WHERE miasta_sklepy.rodzaj_sklepu =
sklepy.rodzaj_sklepu);

ƒ

Jakiego rodzaju sklepu nie ma w żadnym mieście?

mysql> SELECT DISTINCT rodzaj_sklepu FROM sklepy

WHERE NOT EXISTS (SELECT * FROM miasta_sklepy

WHERE miasta_sklepy.rodzaj_sklepu =
sklepy.rodzaj_sklepu);

ƒ

Jaki rodzaj sklepu występuje we wszystkich miastach?

mysql> SELECT DISTINCT rodzaj_sklepu FROM Stores S1

WHERE NOT EXISTS (

SELECT * FROM miasta WHERE NOT EXISTS (

SELECT * FROM miasta_sklepy

WHERE miasta_sklepy.miasto = miasta.miasto

AND miasta_sklepy.rodzaj_sklepu =
sklepy.rodzaj_sklepu));

W ostatnim przykładzie mamy podwójnie zagnieżdżone zapytanie z

NOT EXISTS

.

Formalnie, to zapytanie to brzmi: "czy istnieje miasto ze sklepem, którego nie ma
wśród sklepów"? Ale można też prościej zapytać po prostu: "czy x jest prawdziwe dla
wszystkich y"?

•

Podzapytania wierszowe

Do tej pory rozważaliśmy podzapytania odnoszące się do kolumn (podzapytania
skalarne), tzn. podzapytania, które zwracają pojedynczą wartość kolumny.
Podzapytanie wierszowe jest takim podzapytaniem, które zwraca jeden wiersz, i tym
samym może zwrócić więcej niż jedną wartość kolumny.

Na przykład:

mysql> SELECT * FROM tab1 WHERE (1,2) = (SELECT col1, col2
FROM tab2);

albo:

mysql> SELECT * FROM tab1 WHERE ROW(1,2) = (SELECT col1, col2

FROM tab2);

W obydwu przypadkach wartośc zapytania jest równa

TRUE

jeśli tylko tablica

tab2

zawiera wiersz, w którym

col1

= 1, i

col2

= 2.

Wyrażenie

(1,2)

i

ROW(1,2)

są czasem nazywane konstruktorami wierszowymi.

Są też poprawne w troche innym kontekście. Na przykład, następne dwa zapytania
są składniowo równoważne:

mysql> SELECT * FROM tab1 WHERE (col1,col2) = (1,1);

mysql> SELECT * FROM tab1 WHERE col1 = 1 AND col2 = 1;

Normalnie, konstruktorów wieszowych używa się w celu porównania z
podzapytaniami, które zwracają wartości dówch lub więcej kolumn. Na przykład,
wykonanie nastepującego zapytania:

mysql> SELECT col1,col2,col3

FROM tab1

WHERE (col1,col2,col3) IN

(SELECT col1,col2,col3 FROM tab2);

da nam wynik: "wszystkie wiersze, w tablicy

tab1

które istnieją też w tablicy

tab2

".

•

Podzapytania po słowie FROM

Składnia:

mysql> SELECT ... FROM (podzapytanie) AS nazwa ...

Klauzula

AS

jest obowiązkowa, gdyż każda tablica występująca zwykle po słowie

FROM

musi mieć nazwę. Wszystkie kolumny z listy kolumn podzapytania muszą mieć

nazwy unikalne.

Na przykład, załóżmy, że mamy następującą tablicę:

mysql> CREATE TABLE tab1 (s1 INT, s2 CHAR(5), s3 FLOAT);

mysql> INSERT INTO tab1 VALUES (1,'1',1.0);

mysql> INSERT INTO tab1 VALUES (2,'2',2.0);

WHERE sb1 > 1;

wtedy możemy skonstruować następujące zapytanie z podzapytaniem po słowie

FROM

:

mysql> SELECT sb1,sb2,sb3

FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM
tab1) AS sb

WHERE sb1 > 1;

Wynik jest następujący:

Result: 2, '2', 4.0.

Następny przykład:

Załóżmy, że chcemy znać średnią zestawu sum z kolumny zgrupowanej tablicy.
Następujące zapytanie nie zadziała:

mysql> SELECT AVG(SUM(col)) FROM tab1 GROUP BY col1;

Natomiast można uzyskac odpowiedni wynik, posługując się podzapytaniem:

mysql> SELECT AVG(sum_col1)

FROM (SELECT SUM(col) AS sum_col1

FROM tab1 GROUP BY col1) AS tab1;

Zauważmy, że nazwa kolumny użyta w podzapytaniu (

sum_column1

) jest użyta w

zapytaniu zewnętrznym.

•

Optymalizacja podzapytań

Trochę praktycznych porad w celu pisania szybszych zapytań z podzapytaniami.

ƒ

Zamiast połączeń (ang. join) lepiej używać podzapytań:

mysql> SELECT DISTINCT col1 FROM tab1 WHERE tab1.col1 IN (

SELECT col1 FROM tab2);

ƒ

zamiast:

mysql> SELECT DISTINCT tab1.col1 FROM tab1, tab2

WHERE tab1.col1 = tab2.col1;

ƒ

Należy w miarę możliwości używać klauzul, specyficznych dla podzapytań, w
zapytaniach zewnętrznych niż wewnętrznych. Na przykład należy użyć:

mysql> SELECT * FROM tab1 WHERE s1 IN

(SELECT s1 FROM tab1 UNION ALL SELECT s1 FROM tab2);

ƒ

zamiast:

mysql> SELECT * FROM tab1 WHERE s1 IN

(SELECT s1 FROM tab1) OR s1 IN (SELECT s1 FROM tab2);

ƒ

Albo, należy napisać raczej:

mysql> SELECT (SELECT col1 + 5 FROM tab1) FROM tab2;

ƒ

niż

mysql> SELECT (SELECT col1 FROM tab1) + 5 FROM tab2;

ƒ

Należy raczej używać podzapytań wierszowych niż skorelowanych. Na
przykład należy raczej użyć:

mysql> SELECT * FROM tab1

WHERE (col1,col2) IN (SELECT col1,col2 FROM tab2);

ƒ

niż:

mysql> SELECT * FROM tab1

WHERE EXISTS (SELECT * FROM tab2 WHERE

tab2.col1=tab1.col1

AND tab2.col2=tab1.col2);

ƒ

Należy używać raczej

NOT (a = ANY (...))

niż

<> ALL (...)

.

ƒ

Należy używać raczej

x = ANY (tablica zawierająca (1,2))

niż

x=1 OR x=2

.

ƒ

Należy używać raczej

ANY

niż

EXISTS

.

Ponadto, MySQL posiada swoje wewnętrzne mechanizmy, które powodują znaczną
optymalizację podzaptań, na przykład wykonuje tylko jeden raz podzapytania
nieskorelowane, lub zastępuje złożone operacje porównawcze odpowiednimi
funkcjami.