Wprowadzenie do SQL (Structured Query Language)
Wersja 3.6
Dokument ten jest podręcznikiem SQL i jest pionierskim dokumentem w
tej klasie, jako że jest pierwszym pełnym podręcznikiem SQL dostępnym w
sieci. SQL umożliwia użytkownikom dostęp do danych w relacyjnych DBMS-ach
(database management systems) takich jak Oracle, Sybase, Informix, Microsoft
SQL Server, Access i innych, pozwalając użytkownikom na opis danych, które
chcą obejrzeć. SQL pozwala też użytkownikom definiować dane w bazie i
manipulować tymi danymi. Ten dokument opisze jak używać SQL i zademonstruje
to na przykładach. SQL, którego będziemy używać w tym dokumencie to "ANSI",
czyli standardowy SQL - żadnych rozszerzeń konkretnych implementacji DBMS-ów
nie będziemy omawiać aż do rozdziału "Niestandardowy SQL". Zalecane jest,
żebyś wydrukował sobie ten dokument, aby mieć łatwy dostęp do tekstu i
przykładów.
Spis treści
W relacyjnych bazach danych, dane są przechowywane w tabelach.
Przykładowa tabela poniżej będzie wiązała (przechowywała relacje między)
numerem ubezpieczenia socjalnego, nazwiskiem i adresem:
BazaAdresowPracownikow
| NumerUbezpieczeniaSocjalnego | Imie | Nazwisko | Adres | Miasto | Stan |
|---|
| 512687458 | Joe | Smith | 83 First Street |
Howard | Ohio |
| 758420012 | Mary | Scott | 842 Vine Ave. |
Losantiville | Ohio |
| 102254896 | Sam | Jones | 33 Elm St. |
Paris | New York |
| 876512563 | Sarah | Ackerman | 440 U.S.
110 | Upton | Michigan |
Teraz powiedzmy, że chcesz obejrzeć adres każdego pracownika. Użyj
polecenia SELECT w następujący sposób:
SELECT Imie, Nazwisko, Adres, Miasto, Stan
FROM TabelaAdresowPracownikow;
Zapytanie to wywoła następujący rezultat dla tej tabeli:
| Imie | Nazwisko | Adres | Miasto | Stan |
|---|
| Joe | Smith | 83 First
Street | Howard | Ohio |
| Mary | Scott | 842 Vine
Ave. | Losantiville | Ohio |
| Sam | Jones | 33 Elm St. | Paris | New
York |
| Sarah | Ackerman | 440 U.S. 110 |
Upton | Michigan |
Aby wyjaśnić, co przed chwilą zrobiłeś, powiem, że zapytałeś o
wszystkie dane z tabeli TabelaAdresowPracownikow, a dokładniej, zapytałeś o
kolumny "Imie", "Nazwisko", "Adres", "Miasto", "Stan". Zauważ, że nazwy
kolumn i nazwy tabel (czyli baz danych) nie mają spacji... Muszą być
wprowadzane jako jedno słowo. Zauważ też, że polecenie kończy się średnikiem
(;). Ogólna forma polecenia SELECT, odczytująca wszystkie
wiersze w tabeli to:
SELECT NazwaKolumny, NazwaKolumny, ...
FROM NazwaTabeli;
Aby odczytać wszystkie kolumny z tabeli bez wpisywania ich nazw,
wykorzystaj:
SELECT * FROM NazwaTabeli;
Każdy DBMS czy oprogramowanie bazodanowe ma inne metody łączenia się
z bazą danych i wprowadzania komend SQL; spytaj lokalnego "guru"
komputerowego by pomógł ci użyć SQL na danym systemi.
Dla dalszej dyskusji polecenia SELECT, spójrzmy na nową
przykładową tabelę (tylko dla celów hipotetycznych):
TablicaStatystykPracownikow
| NumerIDPracownika | Pensja | Zyski | Stanowisko |
|---|
| 010 | 75000 | 15000 | Kierownik |
| 105 | 65000 | 15000 | Kierownik |
| 152 | 60000 | 15000 | Kierownik |
| 215 | 60000 | 12500 | Kierownik |
| 244 | 50000 | 12000 | Personel |
| 300 | 45000 | 10000 | Personel |
| 335 | 40000 | 10000 | Personel |
| 400 | 32000 | 7500 | Ekspedient |
| 441 | 28000 | 7500 | Ekspedient |
Istnieje sześć operatorów relacyjnych w SQL. Po ich przedstawieniu,
omówimy jak się z nich korzysta:
Słowo WHERE jest używane do określania, że tylko niektóre wiersze
tabeli mają być wyświetlane. Mają być wyświetlane bazując na kryteriach
przedstawionych w klauzuli WHERE. Najłatwiej zrozumieć to patrząc na kilka
przykładów.
Gdybyś chciał zobaczyć NumerIDPracownika osób zarabiających ponad
50000$, użyj tego:
SELECT NumerIDPracownika
FROM TablicaStatystykPracownikow
WHERE Pensja >= 50000;
Zauważ, że użyliśmy znaku >= (większy lub równy), gdyż chcieliśmy
zobaczyć tych, którzy zarabiają więcej niż 50000$ lub dokładnie 50000$.
Powyższe zapytanie da następujący rezultat:
NumerIDPracownika
| 010 |
| 105 |
| 152 |
| 215 |
| 244 |
Treść polecenia WHERE, tj. Pensja >= 50000, jest znane pod
określeniem "warunku". To samo można robić dla kolumn tekstowych:
SELECT NumerIDPracownika
FROM TablicaStatystykPracownikow
WHERE Stanowisko = 'Kierownik';
To wyświetli numery ID wszystkich kierowników. Ogólnie, przy
kolumnach tekstowych używaj operatorów równości lub nierówności i upewnij
się, że tekst jest otaczany znakami apostrofu (').
Operator AND łączy dwa lub więcej warunków i wyświetla wiersz
tylko wtedy, gdy dane w tym wierszu spełniają wszystkie przedstawione warunki.
Np. Aby wyświetlić cały personel zarabiający ponad 40000$, wykorzystaj:
SELECT NumerIDPracownika
FROM TablicaStatystykPraconikow
WHERE Pensja > 40000 AND Stanowisko = 'Personel';
Operator OR łączy dwa lub więcej warunków, ale zwraca wiersz,
jeżeli dowolny z nich (warunków) jest spełniony. Aby zobaczyć wszystkich,
którzy zarabiają mniej niż 40000$, lub mają mniej niż 10000$ zysków,
wykorzystaj następujące zapytanie:
SELECT NumerIDPracownika
FROM TablicaStatystykPracownikow
WHERE Pensja < 40000 OR Zyski < 10000;
Operatory AND i OR można łączyć ze sobą, np:
SELECT NumerIDPracownika
FROM TablicaStatystykPracownikow
WHERE Stanowisko = 'Kierownik' AND Pensja > 60000 OR Zyski > 12000;
Najpierw SQL wyszukuje wierszy w których pensja jest większa niż
60000$ oraz stanowisko to Kierownik, a potem biorąc tą nową listę wierszy,
sprawdza czy któryś z nich wpełnia poprzedni warunek AND lub warunek że
kolumna Zysku ma być większa niż 12000$. Następnie SQL wyświetla tylko tą
drugą nową listę wierszy, pamiętając że każdy, którego Zyski przekraczają
12000$ będzie włączony, jako że operator OR włącza wiersz zarówno gdy
drugi warunek jest prawdziwy. Zauważ, że operacja AND jest wykonywana
najpierw.
Aby wykonać operacje OR przed operacjami AND, np. w
celu zobaczenia listy pracowników zarabiających ponad 50000$ lub mających
wielkie zyski (>10000$) i jednocześnie będących (w obu wypadkach) kierownikami,
użyj nawiasów:
SELECT NumerIDPracownika
FROM TablicaStatystykPracownikow
WHERE Stanowisko = 'Manager' AND (Pensja > 50000 OR Zyski > 10000);
Łatwiejszym sposobem używania warunków łączonych jest używanie
IN i BETWEEN. Np. Gdy chcesz otrzymać listę kierowników i
personelu:
SELECT NumerIDPracownika
FROM TablicaStatystykPracownikow
WHERE Stanowisko IN ('Kierownik', 'Personel');
albo wylistować tych zarabiających więcej lub dokładnie 30000$, ale
maksymalnie 50000$, użyj:
SELECT NumerIDPracownika
FROM TablicaStatystykPracownikow
WHERE Pensja BETWEEN 30000 AND 50000;
Aby wylistować wszystkich nie znajdujących się w tym zakresie,
spróbuj:
SELECT NumerIDPracownika
FROM TablicaStatystykPracownikow
WHERE Pensja NOT BETWEEN 30000 AND 50000;
Podobnie, NOT IN listuje wszystkie wiersze, w których nie występują
elementy z listy IN.
Spójrz na tabelę TablicaAdresowPracownikow i powiedzmy, że
chciałbyś zobaczyć wszystkie osoby, których nazwiska zaczynają się na "L";
spróbuj:
SELECT NumerIDPracownika
FROM TablicaAdresowPracownikow
WHERE Nazwisko LIKE 'L%';
Znak procenta (%) jest użyty do przedstawienia dowolnego znaku
(cyfry, litery) lub do zestawu znaków, które mogą pojawić się po "L". Aby
znaleźć tych, których nazwiska kończą się na "L", użyj '%L'. Aby znaleźć
osoby, które mają "L" w środku nazwiska, użyj '%L%'. Znak '%'
może być używany zamiast dowolnych innych znaków w określonej pozycji, w
stosunku do podanych znaków (tu np. znaku 'L'). Polecenie NOT LIKE
wyświetli wiersze, które nie odpowiadają temu opisowi. Inne możliwości użycia
LIKE lub pozostałych operatorów warunkowych sa dopszczalne, ale zależą
od tego, jakiego DBMS-a używasz - jak zwykle zajrzyj do podręcznika, lub
spytaj administratora systemu.
W tym rozdziale omówimy tylko wewnętrzne połączenia i equijoins,
jako że w praktyce są one najbardziej użyteczne. Dla dalszych informacji,
przejrzyj linki SQL znajdujące się na końcu tego dokumentu.
Dobry projekt bazy danych sugeruje by każda tabela zawierała dane
dotyczące tylko pojedynczej sprawy, a dodatkowe informacje można było dostać
(w relacyjnej bazie danych) przez używaie dodatkowych tabel i poprzez ich
łączenie (join).
Najpierw spójrz na te przykładowe tabele:
PosiadaczeAntykow
| IDPosiadacza | NazwiskoPosiadacza | ImiePosiadacza |
|---|
| 01 | Jones | Bill |
| 02 | Smith | Bob |
| 15 | Lawson | Patricia |
| 21 | Akins | Jane |
| 50 | Fowler | Sam |
Zamowienia
| IDPosiadacza | ZadanyPrzedmiot |
|---|
| 02 | Stół |
| 02 | Biurko |
| 21 | Krzesło |
| 15 | Lustro |
Antyki
| IDSprzedajacego | IDKupujacego | Rzecz |
|---|
| 01 | 50 | Łóżko |
| 02 | 15 | Stół |
| 15 | 02 | Krzesło |
| 21 | 50 | Lustro |
| 50 | 01 | Biurko |
| 01 | 21 | Gabinet |
| 02 | 21 | Stolik do kawy |
| 15 | 50 | Krzesło |
| 01 | 15 | Szkatułka |
| 02 | 21 | Garnki |
| 21 | 02 | Biblioteka |
| 50 | 01 | Doniczka |
Najpierw omówmy koncepcję kluczy. Podstawowym kluczem jest kolumna,
lub zestaw kolumn, który unikalnie identyfikuje resztę danych w dowolnym
zadanym wierszu. Np. w tabeli PosiadaczeAntykow, IDPosiadacza
unikalnie określa dany wiersz. Oznacza to dwie rzeczy: nie może być dwóch
wierszy, które będą miały taki sam IDPosiadacza, a nawet jeśli dwóch
posiadaczy będzie miało te same imiona, kolumna IDPosiadacza zapewnia,
że tych dwóch posiadaczy nikt nie pomyli ze sobą, ponieważ baza danych będzie
raczej używać pola IDPosiadacza do wyśledzania właścicieli niż ich imion.
Obcy klucz jest kolumną w obcej tabeli, gdzie ta kolumna jest
podstawowym kluczem. Oznacza to, że dowolne dane w kolumnie obcego klucza
muszą mieć odpowiedniki w odpowiadającej tabeli, w której ten klucz jest
kluczem podstawowym. W mowie DBMS-owej, ta odpowiedniość określana jest
jako integracja referencyjna. Np. w tabeli Antyki,
IDKupujacego oraz
IDSprzedajacego są obcymi kluczami do podstawowego klucza tabeli
PosiadaczeAntykow (tj. kolumny IDPosiadacza). Innymi słowy,
wszystkie te dane "ID" są używane w odniesieniu do posiadaczy, kupców, lub
sprzedawców antyków jako takich, nie musząc używać właściwych imion.
Celem tych kluczy jest to, żeby dane mogły być powiązane pomiędzy
tabelami bez potrzeby powtarzania danych w każdej tabeli -- to jest właśnie
potęga relacyjnych baz danych. Np. możesz znaleźć imiona tych, którzy kupili
krzesło bez konieczności listowania tych imion z tabeli Antyki... Możesz
je uzyskać, wiążąc tych, którzy kupili krzesło z imionami w tabeli
PosiadaczeAntykow, korzystając z IDPosiadacza, łączącego dane
tych dwóch tabel. Aby znaleźć imiona osób, które kupiły krzesło, użyj
następującego zapytania:
SELECT NazwiskoPosiadacza, ImiePosiadacza
FROM PosiadaczeAntykow, Antyki
WHERE IDKupujacego = IDPosiadacza AND Rzecz = 'Krzesło';
Zwróć uwage na następującą rzecz w tym zapytaniu...zauważ, że obie
tabele włączone w relację istnieją w klauzuli FROM tego zapytania. W
następnej klauzuli, WHERE, zauważ też, że część Rzecz =
'Krzesło' ogranicza
listę do tych osób, które kupiły (a w tym przykładzie poprzez to posiadają)
krzesło. Dalej, zauważ jak powiązane są kolumny ID jednej tabeli z drugą
poprzez użycie klauzuli IDKupujacego = IDPosiadacza. Tylko tam, gdzie ID
zgadzają się i gdzie rzeczą jest krzesło (przez ten AND), imiona z
tabeli
PosiadaczeAntyków zostaną wylistowane. Z powodu tego, że warunek łączący
używa znaku równości, to powiązanie nazywane jest equijoin (ang. equal
znaczy równy, a join znaczy powiązanie). Wynikiem zapytania są dwa imiona:
Smith, Bob oraz Flower, Sam.
Można używać notacji kropkowej, aby nazwy kolumn poprzedzać nazwami
tabel, aby zapobiec dwuznaczności, np:
SELECT PosiadaczeAntykow.NazwiskoPosiadacza,
PosiadaczeAntykow.ImiePosiadacza
FROM PosiadaczeAntykow, Antyki
WHERE Antyki.IDKupujacego = PosiadaczeAntykow.IDPosiadacza AND
Antyki.Rzecz = 'Krzesło';
Jednak z uwagi na to, że nazwy kolumn były inne w każdej tabeli, nie
było to niezbędne.
Powiedzmy, że chcesz wylistować ID i imiona tylko tych osób, które
sprzedały antyk. Oczywiści chcesz mieć listę, w której każda osoba jest
przedstawiona tylko raz -- nie interesuje cię ile antyków dana osoba
sprzedała, tylko sam fakt sprzedania (jeśli interesuje cię liczenie,
zobacz
niżej rozdział o funkcji agregującej). To oznacza, że będziesz musiał
powiedzieć SQL-owi aby wyeliminował duplikujące się wiersze i po prostu
wylistował każdą osobę tylko raz. Aby to zrobić, użyj słowa kluczowego
DISTINCT.
Najpierw będziemy musieli połączyć się z tabelą
PosiadaczeAntyków
aby uzyskać dane o imieniu i nazwisku danej osoby. Jednakże, pamiętaj, że
skoro kolumna IDSprzedajacego w tabeli Antyki jest obcym
kluczem z tabeli PosiadaczeAntykow, sprzedawca jest wylistowany tylko
jeśli istnieje wiersz w tej (PosiadaczeAntykow) tabeli, opisujący dany
ID. Chcemy też w naszym
listingu wyeliminować wielokrotne pojawienia IDSprzedajacego, więc
używamy DISTINCT na kolumnie, w której moga pojawić się powtórzenia.
Aby jeszcze skomplikować, będziemy też chcieli mieć listę
posortowaną według nazwiska, a następnie według imienia, a na koniec według
IDPosiadacza. Dlatego użyjemy klauzuli ORDER BY:
SELECT DISTINCT IDSprzedajacego, NazwiskoPosiadacza, ImiePosiadacza
FROM Antyki, PosiadaczeAntykow
WHERE IDSprzedajacego = IDPosiadacza
ORDER BY NazwiskoPosiadacza, ImiePosiadacza, IDPosiadacza;
W tym przykładzie, jako że każdy sprzedał jakąś rzecz, uzyskamy
listę wszystkich posiadaczy, w alfabetycznym porządku, posortowanych według
nazwiska. Dla późniejszych odwołań (i w wypadku gdyby ktoś się pytał), ten
typ połączenia jest uważany za kategorię połączeń wewnętrznych (ang. inner
joins).
W tym rozdziale będziemy mówić o aliasach, In oraz o używaniu
podzapytań, oraz o tym jak można z nich korzystać na przykładzie trzech
tabel. Najpierw spójrz na poniższe zaptanie, które drukuje nazwiska tych
posiadaczy, którzy wystosowali jakieś zamówienie, wraz z tym zamówieniem,
ale tylko dla tych zamówień, które moga być dokonane (tzn. gdy istnieje
kupiec, który posiada zamawianą rzecz):
SELECT Posiad.NazwiskoPosiadacza Nazwisko, Zam.ZadanyPrzemiot Zamawiany
przedmiot
FROM Zamowienia Zam, PosiadaczeAntykow Posiad
WHERE Zam.IDPosiadacza = Posiad.IDPosiadacza
AND Zam.ZadanyPrzedmiot IN
(SELECT Rzecz
FROM Antyki);
To nam daje:
| Nazwisko | Zamawiany | przedmiot |
|---|
| Smith | Stół |
| Smith | Biurko |
| Akins | Krzesło |
| Lawson | Lustro |
Są tu pewne rzeczy na które warto zwrócić uwagę:
No! Wystarczy narazie na temat złożonych zapytań SELECT. Przejdźmy
do innych spraw związanych z SQL.
Omówię pięć najważniejszych funkcji: SUM, AVG,
MAX, MIN oraz COUNT.
Są one zwane funkcjami agregacyjnymi, ponieważ zwykle podsumowują one
rezultaty zapytania, a nie wypisują listingu wszyskich wierszy.
Mając w pamięci tabele z początku tego dokumentu, spójrzmy na trzy
przykłady:
SELECT SUM(Pensja), AVG(Pensja)
FROM TablicaStatystykPracownikow;
To zapytanie pokazuje sumę wszystkich pensji w tabeli i średnią
pensję wyliczoną ze wszystkich wpisów w tabeli.
SELECT MIN(Zyski)
FROM TablicaStatystykPracownikow
WHERE Stanowisko = 'Kierownik';
To zapytanie pokazuje najmniejszą wartość kolumny "Zyski" wśród
pracowników, którzy są kierownikami. Wartość ta to 12500.
SELECT COUNT(*)
FROM TablicaStatystykPracownikow
WHERE Stanowisko = 'Personel';
To zapytanie pokazuje, ilu pracownikow ma status personelu (3).
W SQL możesz mieć możliwość tworzenia zestawień (views).
Zestawienie umożliwia połączenie wyników zapytania do nowej, prywatnej
tabeli, którą można wykorzystywać do następnych zapytań, w których może ona
służyć jako źródło wymieniane przy słowie kluczowym FROM. Gdy
korzystasz z zestawienia, dokonywane jest zapytanie zdefiniowane w momencie
tworzenia tego zestawienia, a rezultaty tego zapytania wyglądają jak normalna
tabela.
Przykład utworzenia zestawienia:
CREATE VIEW ANTVIEW AS SELECT ZadanyPrzedmiot FROM Zamowienia;
A teraz napiszmy zapytanie wykorzystujące to zestawienie, gdzie
tabela
którą on reprezentuje jest po prostu listingiem przedmiotów żądanych z
tabeli Zamowienia:
SELECT IDSprzedajacego
FROM Antyki, ANTVIEW
WHERE ZadanyPrzedmiot = Rzecz;
To zapytanie pokaże wszystkie IDSprzedajacego z tabeli
Antyki, gdzie
kolumna Rzecz będzie miała odpowiednika w zestawieniu Antview,
które jest listą
wszystkich porządanych Reczy w tabeli Zamowienia. Listing jest
generowany
dzięki przechodzeniu przez Rzeczy tabeli Antyki jedna za drugą,
dopóki nie
znajdzie się zgodność z jednym z elementów zestawienia Antview.
Zestawienia mogą też
być używane do ograniczania dostępu do baz danych, jak również do
upraszczania skomplikowanych zapytań.
Wszystkie tabele w bazie muszą być w pewnym momencie utworzone...
spójrzmy, jak można utworzyć tabelę Zamówienia:
CREATE TABLE Zamowienia
(IDPosiadacza INTEGER NOT NULL,
ZadanyPrzedmiot CHAR(40) NOT NULL);
To polecenie daje tabeli nazwę i mówi DBMS-owi o każdej kolumnie tej
tabeli. Zauważ, że to polecenie działa na tradycyjnych typach danych oraz,
że te typy mogą być inne, zależnie od DBMS-a, z którego korzystasz. Jak
zwykle, sprawdź w dokumentacji. Najpopularniejsze typy danych to:
Zauważ też, że NOT NULL oznacza, że kolumna musi mieć wartość w
każdym wierszu. Gdy zostanie użyty NULL, kolumna może pozostawać w
niektórych
wierszach pusta.
Dodajmy do tabeli Antyki kolumnę umożliwiającą wpisywanie ceny danej
Rzeczy:
ALTER TABLE Antyki ADD (Cena DECIMAL(8,2) NULL);
Dane dla tej kolumny mogą być uzupełniane lub dodawane później
(zgodnie z definicją).
Aby wstawić wiersz do tabeli, zrób następujące:
INSERT INTO Antyki VALUES (21, 01, 'Ottoman', 200.00);
To polecenie wstawia dane do tabeli jako nowy wiersz, kolumna za
kolumną w zdefiniowanym porządku (tj. w takim porządku w jakim
definiowaliśmy tabelę). Zamiast tego, zmieńmy kolejność i pozostawmy pole z
ceną pustym:
INSERT INTO Antyki (IDKupujacego, IDSprzedajacego, Rzecz)
VALUES (01, 21, 'Ottoman');
Usuńmy wiersz z naszej bazy danych:
DELETE FROM Antyki
WHERE Rzecz = 'Ottoman';
Ale jeśli istnieje inny wiersz, który również zawiera pole
'Ottoman', to ten wiersz zostanie też usunięty. Usuńmy wszystkie wiersze
(w naszym wypadku jeden), które zawierają określone dane, które dodaliśmy
poprzednio:
DELETE FROM Antyki
WHERE Rzecz = 'Ottoman' AND IDKupujacego = 01 AND IDSprzedajacego = 21;
Odświeżmy Cene w wierszu, który nie ma jeszcze ustawionej ceny:
UPDATE Antyki SET Cena = 500.00 WHERE Rzecz = 'Krzesło';
To ustawi Ceny wszystkich krzeseł na 500.00. Jak pokazano, aby
zmniejszyć liczbę poprawianych wierszy do konkretnych, interesujących nas,
trzeba użyć większej ilości warunków w klauzuli WHERE.
Indeksy umożliwiają DBMS-owi szybszy dostęp do danych (pamiętaj, że
ta własność nie jest standardowo dostępna na wszystkich możliwych
systemach). System tworzy wewnętrzną strukturę danych (indeks), który
prowadzi do tego, że wybór wierszy bazujący na indeksowanych kolumnach
może być szybszy. Indeks mówi DBMS-owi, gdzie znajduje się określony wiersz
w tabeli posiadającej indeksowane wartości kolumny, podobnie jak indeks w
książce mówi ci, na której stronie pojawia się dane słowo. Stwórzmy indeks
dla IDPosiadacza kolumny PosiadaczeAntykow:
CREATE INDEX POID_IDX ON PosiadaczeAntykow (IDPosiadacza);
Teraz indeks dla imion:
CREATE INDEX IMIE_IDX ON PosiadaczeAntykow (NazwiskoPosiadacza,
ImiePosiadacza);
Aby pozbyć się indeksu, napisz:
DROP INDEX POID_IDX;
Na marginesie, możesz w ten sposób porzucić także tabelę (ostrożnie!
-- to znaczy, że twoja tabela będzie kasowana). W kolejnym przykładzie,
indeks jest tworzony dla dwóch kolumn -- czasami ta konstrukcja może dziwnie
działać... Sprawdź w dokumentacji zanim dokonasz takiej operacji.
Niektóre DBMS-y nie wymuszają kluczy podstawowych; innymi słowy
unikalność kolumny nie jest wymuszana automatycznie. Oznacza to, że gdy np.
spróbuję wstawić kolejny wiersz do tabeli PosiadaczeAntykow, z numerem
IDPosiadacza ustawionym na 02, to niektóre systemy pozwolą na to, nawet
mimo
to, że kolumna ta miała być unikalna dla tej tabeli (każdy wiersz miał być
inny). Jedna z dróg obejścia tego problemu jest stworzenie unikalnego indeksu
dla kolumny, która chcemy aby została kluczem podstawowym, by tak zmusić system
do zabezpieczenia przed duplikatami:
CREATE UNIQUE INDEX POID_IDX ON PosiadaczeAntykow (IDPosiadacza);
Jednym z zastosowań GROUP BY jest połączenie z funkcjami
agregacyjnymi (szczególnie z COUNT - liczącą wiersze w grupie). Załóżmy,
że
tabela Antyki ma kolumnę Cena oraz że wszystkie wiersze tej kolumny
są
wypełnione. Chcemy zobaczyć najdroższą Rzecz kupioną przez każdego z
posiadaczy. Musimy powiedzieć SQL-owi, by zgrupował nabytki każdego
posiadacza i pokazał nam największą cenę:
SELECT IDPosiadacza, MAX(Cena)
FROM Antyki
GROUP BY IDKupujacego;
A teraz załóżmy, że chcemy zobaczyć tylko największe ceny nabytków
jeśli cena przekraca $1000, więc użyjemy klauzuli HAVING (ang. mający):
SELECT IDKupujacego, MAX(Cena)
FROM Antyki
GROUP BY IDKupujacego
HAVING Cena > 1000;
Kolejnym popularnym wykorzystaniem podzapytań jest ich użycie ich w
połączeniu z poleceniem Select jako składnika klauzuli Where. Np.
wylistowanie kupców, którzy nabyli drogi przedmiot (o $100 droższy od
przeciętnego) odpowiada następującemu zapytaniu:
SELECT IDKupujacego
FROM Antyki
WHERE Cena >
(SELECT AVG(Cena) + 100
FROM Antyki);
Podzapytanie oblicza średnią cenę + $100, a potem używając tej
wartości, wypisuje id każdego posiadacza, który zapłacił więcej niż tyle.
Można tu użyć dodatkowo DISTINCT, by wyeliminować duplikaty.
Lista imion tych z tabeli PosiadaczeAntykow, którzy KUPILI jakąś
rzecz:
SELECT NazwiskoPosiadacza
FROM PosiadaczeAntykow
WHERE IDPosiadacza IN
(SELECT DISTINCT IDKupujacego
FROM Antyki);
Podzapytanie zwraca listę kupców, a Nazwiska drukowane są tylko
wtedy, gdy IDPosiadacza istnieje na liście podzapytania (czasami zwanej
listą kandydatów). Zauważ: w niektórych DBMS-ach zamiast IN można używać
równości, ale dla czytelności IN wydaje się byc lepszym rozwiązaniem.
Jako przykład odświeżenia, wiemy że dżentelmen który kupił
bibliotekę ma złe imię w tabeli... powinno być John:
UPDATE PosiadaczeAntykow
SET ImiePosiadacza = 'John'
WHERE IDPosiadacza =
(SELECT IDKupujacego
FROM Antyki
WHERE Rzecz = 'Biblioteka');
Najpierw podzapytanie wyszukuje IDKupujacego dla osób, które
kupiły
bibliotekę, a potem zewnętrzne zapytanie poprawia Imie tej osoby.
Zapamiętaj następującą rzecz o podzapytaniach: gdy dajesz
podzapytanie jako część warunku WHERE, klauzula Select
podzapytania musi
mieć kolumny, które odpowiadają liczbowo oraz typem tym z klauzuli
Where
zewnętrznego zapytania. Innymi słowy jeśli masz "WHERE ColumnName =
(SELECT...);", to Select musi mieć wewnątrz tylko jedną kolumnę, aby
odpowiaać kolumnie ColumnName zewnętrznej klauzuli Where, i musi
oczywiście
też być zgodnego typu z kolumną zewnętrzną (obydwa mogą być ciągami znaków,
lub obydwa mogą być liczbami, itd.).
EXISTS używa podzapytania jako warunku, gdzie warunek jest
True
(prawdziwy), gdy podzapytanie zwraca jakiekolwiek wiersze, lub False
(fałszywy), gdy tego nie robi. Np. załóżmy, że chcemy zobaczyć listę
Posiadaczy, ale tylko jeśli sklep zajmował się krzesłami:
SELECT ImiePosiadacza, NazwiskoPosiadacza
FROM PosiadaczeAntykow
WHERE EXISTS
(SELECT *
FROM Antyki
WHERE Rzecz = 'Krzesło');
Jeśli znajdą się jakieś krzesła w kolumnie antyków, to podzapytanie
zwróci wiersz, lub wiersze sprawiające, że klauzula EXISTS będzie
prawdziwa,
powodując przez to to, że SQL wylistuje posiadaczy antyków. Jeśli nie było
krzeseł, zapytanie nie zwróci ani jednego wiersza.
ALL jest inną niezwykłą funkcją, jako że zapytania ALL
mogą być
zwykle dokonywane innymi, prawdopodobnie prostszymi metodami; spójrzmy na
przykład:
SELECT IDKupujacego, Rzecz
FROM Antyki
WHERE Cena >= ALL
(SELECT Cena
FROM Antyki);
Zwróci najdroższą rzecz, oraz jej kupca. Podzapytanie zwraca listę
wszystkich Cen w tabeli Antyki, a zewnętrzne zapytanie przechodzi
przez
każdy wiersz tabeli Antyki i jeśli cena (zewnętrzna) jest większa lub
równa od
reszty (ALL) zwróconej przez podzapytanie, to jest ona wyświetlana.
Użyliśmy
tu ">=" z uwagi na to, że najwyższa cena Rzeczy będzie najwyższą ceną z
listy podzapytania, gdyż są to te same ceny.
Zdarzają się przypadki, że chcesz obejrzeć rezultaty serii zapytań
połączone razem; użyj unii (UNION). Aby połączyć wyjście dwóch
następujących
zapytań, wyświetlających ID wszystkich kupców plus wszystkich pozostałych,
którzy wystosowali zamówienie:
SELECT IDKupujacego
FROM Antyki
UNION
SELECT IDPosiadacza
FROM Zamowienia;
Zauważ, że SQL wymaga by lista Select zgadzała się kolumna do
kolumny w typach danych. W tym wypadku IDKupujacego i
IDPosiadacza są danymi
tego samego typu (integer). Zauważ też, że SQL automatycznie dokonuje tu
eliminacji duplikatów (przy użyciu unii), nie potrzeba używać
DISTINCT.
Zewnętrznego połączenia używamy gdy zapytanie łączące jest
"zjednoczone" z wierszami nie włączonymi do połączenia, a są wyjątkowo
użytecznymi gdy dołączone są flagi stałego tekstu. Spójrz na zapytanie:
SELECT IDPosiadacza, 'jest w tabeli Zamowienia i Antyki'
FROM Zamowienia, Antyki
WHERE IDPosiadacza = IDKupujacego
UNION
SELECT IDPosiadacza, 'jest tylko w tabeli Antyki'
FROM Antyki
WHERE IDKupujacego NOT IN
(SELECT IDPosiadacza
FROM Zamowienia);
Pierwsze zapytanie dokonuje połączenia w celu wyświetlenia
wszystkich posiadaczy, znajdujących się w obydwu tabelach, dodając
powtarzającą się, statyczną informację po wyświetleniu ID. Unia łączy ta
listę z następną listą. Następna lista jest generowana przez wyszukanie
tych ID, które nie znajdują się w tabeli Zamówień, tak więc wyłączonych
z
zapytania łączącego. Potem każdy wiersz tabeli Antyków jest skanowany w
poszukiwaniu IDKupujacego, który nie należy do listy posiadaczy z
poprzednej
listy. Jeśli warunek ten jest spełniony, ID takie jest wyświetlane (wraz ze
swoim komentarzem). Być może istnieje łatwiejszy sposób dokonania tego, ale
trudno jest wygenerować dodatkowe teksty informacyjne.
Ta koncepcja jest bardzo pożyteczna w sytuacjach, gdzie klucz
podstawowy jest związany z obcym kluczem, lecz wartość obcego klucza dla
niektórych kluczy podstawowych jest NULL. Np. w jednej tabeli podstawowym
kluczem są sprzedawcy, a w drugiej klienci, którzy mają w swoim wierszu
kolumnę określającą ich ekspedienta. Jednak gdy dany sprzedawca nie ma
klientów, to jego imię nie zaistnieje na liście klientów. Gdy chcemy
wydrukować listing wszystkich sprzedawców, używamy zewnętrznego połączenia,
drukując ich wraz z ich klientami, a w wypadku gdy takiego brak -- tj. pole
ma wartość NULL.
DOSYĆ ZAPYTAŃ!!! mówisz?...przejdźmy do czegoś całkiem innego...
/* Aby wiedzieć o co w tym chodzi, oto przykładowy program, który
używa embedded SQL. Umożliwia on programistom połączenie się z
bazą i włączanie kodu SQL wprost do programu, tak że ich programy
mogą używać, manipulować i przetwarzać dane z bazy
-Ten przykładowy program w C wydrukuje raport.
-Program ten będzie musiał być prekompilowany na instrukcje SQL
przed właściwą kompilacją.
-Części EXEC SQL są te same (standardowe), ale otaczające je
fragmenty kodu C być może będą wymagały zmiany, włączając w to
deklaracje nazwy hosta, jeśli używasz innego języka.
-Osadzony SQL zmienia swoją formę zależnie od systemu, więc sprawdź
dokładnie dokumentację swojego oprogramowania, szczególnie
deklaracje zmiennych i procedury logowania w których sieć, DBMS i
system operacyjny mają znaczenie krytyczne. */
/*****************************************************/
/* Ten program nie jest wykonywalny ani kompilowalny */
/* Ma na celu tylko pokazanie przykładu */
/*****************************************************/
#include
/* Ta sekcja deklaruje zmienne hosta; będą to zmienne których używa
twój program, lecz zmienna SQL również pobiera wartości lub
przekazuje je na zewnątrz. */
EXEC SQL BEGIN DECLARE SECTION;
int IDKupujacego;
char Imie[100], Nazwisko[100], Rzecz[100];
EXEC SQL END DECLARE SECTION;
/* To włącza zmienną SQLCA, aby można było dokonywać analizy błędów. */
EXEC SQL INCLUDE SQLCA;
main() {
/* To prawdopodobny sposób zalogowania się do bazy danych */
EXEC SQL CONNECT UserID/Password;
/* Ten kod mówi, że jesteś podłączony, lub sprawdza czy został
wygenerowany kod błędu, oznaczający nieprawidłowy lub niemożliwy
login */
if(sqlca.sqlcode) {
printf(Printer, "Błąd przy podłączaniu się do serwera bazy danych.\n");
exit();
}
printf("Podłączony do serwera bazy danych.\n");
/* To deklaruje "Cursor". Jest to używane gdy zapytanie zwraca
więcej niż jeden wiersz, a operacja ma być wykonana na każdym
wierszu rezultatu. Mając każdy wiersz odebrany, użyję go w raporcie.
Później użyję "Fetch" aby wybrać każdy wiersz, jeden na raz. By
właściwie wywołać zapytanie, użyta jest instrukcja "Open". "Declare"
po prostu zestawia zapytanie. */
EXEC SQL DECLARE ItemCursor CURSOR FOR
SELECT RZECZ, IDKUPUJACEGO
FROM ANTYKI
ORDER BY RZECZ;
EXEC SQL OPEN ItemCursor;
/* +-- Możesz tu wstawić blok obsługi błędów --+ */
/* Fetch pobiera wartości następnego wiersza zapytania do zmiennych
hosta. Jednakże wpierw trzeba zrobić "priming fetch" (technika
programowania).. Gdy kursor jest poza obszarem danych, zostanie
wygenerowany kod SQL pozwalający opuszczenie pętli. Zauważ, że dla
uproszczenia pętla opuści każdy kod SQL, nawet gdyby był to kod
błędu. W innym wypadku musi być dokonywane sprawdzanie kodów. */
EXEC SQL FETCH ItemCursor INTO :Rzecz, :IDKupujacego;
while(!sqlca.sqlcode) {
/* Z każdym wierszem, będziemy robili parę rzeczy. Najpierw
podbijemy cenę o $5 w górę (łapówka dla dealera) i pobierzemy imię
kupującego by wstawić je do raportu. Aby tego dokonać, użyję Update
i Select przed drukowaniem linii na ekran. Update zakłada jednakże,
że dany kupiec kupił tylko jedną z wymienionych rzeczy, gdyż w
przeciwnym wypadku cena byłaby zwiększona zbyt wiele razy. W
przeciwnym wypadku, musiałaby być użyta logiczna zmienna "RowID"
(zobacz w dokumentacji). Zauważ też dwukropek przed zmiennymi hosta
używanymi wewnątrz instrukcji SQL. */
EXEC SQL UPDATE ANTYKI
SET CENA = CENA + 5
WHERE RZECZ = :Rzecz AND IDKUPUJACEGO = :IDKupujacego;
EXEC SQL SELECT IMIEPOSIADACZA, NAZWISKOPOSIADACZA
INTO :Imie, :Nazwisko
FROM POSIADACZEANTYKOW
WHERE IDKUPUJACEGO = :IDKupujacego;
printf("%25s %25s %25s", Imie, Nazwisko, Rzecz);
/* Brzydki raport -- tylko dla potrzeb przykładu! Weź następny
wiersz */
EXEC SQL FETCH ItemCursor INTO :Rzecz, :IDKupujacego;
}
/* Zamknij kursor, potwierdź zmiany (patrz niżej) i zakończ program.
*/
EXEC SQL CLOSE ItemCursor;
EXEC SQL COMMIT RELEASE;
exit();
}
(zobacz odnośnik do FAQ jeśli chcesz więcej)
______________________________________________________________________
- INTERSECT i MINUS są jak polecenie UNION,
z wyjątkiem tego, że
INTERSECT produkuje wiersze, które występują w obydwu zapytaniach, a
MINUS
rezultaty pierwszego zapytania, ale bez rezultatów drugiego.
- Rozszerzenia generowania raportów: klauzula COMPUTE jest
wstawiana na końcu zapytania, aby umożliwić wydrukowanie rezultatu funkcji
agregacyjnej na końcu listingu, np. COMPUTE SUM (Cena); Inną możliwością
jest użycie logiki przerywania: definiowania przerwań (breaks) dzielących
rezultaty zapytania na grupy w oparciu o kolumny, np. BREAK ON
IDKupujacego.
Potem, aby wydrukować rezultat po listingu grupy, użyj COMPUTE SUM OF Cena
ON IDKupujacego. Jeśli przykładowo użyłeś wszystkie trzy z tych klauzul
(pierwszy BREAK, potem COMPUTE przy przerwaniu, na koniec
COMPUTE całkowitej
sumy), otrzymasz raport, który zgrupował rzeczy według ich
IDKupujacego,
listując sumę cen za każdą grupą rzeczy należących do IDKupujacego. Po
wszystkich listingach, drukowana jest suma wszystkich listowanych cen.
- W uzupełnieniu do wymienionych wcześniej funkcji agregacyjnych,
niektóre DBMS-y umożliwiają użycie większej ilości funkcji w listach
Select.
Funkcje te muszą działać na odpowiednich typach danych. Oto niektóre funkcje
matematyczne:
Funkcje znakowe:
Oto kilka ogólnych form instrukcji omawianych w tym podręczniku oraz
kilka dodatkowych (wyjaśnienia będą dodane). PAMIĘTAJ, że wszystkie te
instrukcje mogą, ale nie muszą być dostępne w twoim systemie, więc sprawdź w
dokumentacji czy dostępne masz:
ALTER TABLE <<>NAZWA TABELI> ADD|DROP|MODIFY (Właściwości
kolumny...zobacz
Create Table); -- Pozwala ci dodawać lub
kasować kolumnę lub kolumny z
tabeli, lub zmieniać właściwości (typy danych, itd.) istniejących kolumn;
ta instrukcja może być też używana do zmieniania fizycznych właściwości
tabeli (jak tabela jest przechowywana, itd.), ale te definicje są zależne od
DBMS-a, więc przeczytaj dokumentację. Te fizyczne właściwości są używane w
instrukcji Create Table kiedy tabela jest tworzona. Dodatkowo, poprzez
instrukcję Alter Table można dokonać tylko jednej opcji -- czy to
ADD, DROP,
czy MODIFY.
COMMIT; -- sprawia, że zmiany jakie dokonaliśmy na systemie bazy
dnaych pozostaną w nim na stałe.
CREATE [UNIQUE] INDEX
ON (); --UNIQUE jest opcjonalne;
w nawiasach kwadratowych.
CREATE TABLE
( [()] ,
...następne kolumny); (również prawidłowe przy ALTER TABLE)
--gdzie ROZMIAR jest używany tylko w określonych typach danych (patrz
wyżej), a ograniczenia mogą być następujące:
ROLLBACK; --Anuluje wszystkie zmiany na bazie danych od czasu
wykonania ostatniego polecenia Commit... uważaj! Niektóre programy
używają
automatycznego potwierdzania (commiting), więc Rollback może nie działać.
SELECT [DISTINCT|ALL] <<>LISTA KOLUMN, FUNKCJE, STAŁE, ITD.>
FROM <<>LISTA TABEL LUB VIEW'ÓW>
[WHERE <<>WARUNKI>]
[GROUP BY <<>KOLUMNY GRUPUJACE>]
[HAVING <<>WARUNEK>]
[ORDER BY <<>KOLUMNY PORZADKUJACE> [ASC|DESC]]; --gdzie ASC|DESC pozwala
na porządkowanie w porządku rosnącym lub malejącym.
UPDATE <<>NAZWA TABELI>
SET <<>NAZWA KOLUMNY> = <<>WARTOŚĆ>
[WHERE <<>WARUNEK>]; --jeśli nie zastosuje się klauzuli Where, wszystkie
wiersze będą poprawione zgodnie z instrukcją Set.
Chwilowo niedostępne
Autor nie podpisuje się pod tymi site'ami.
Mam nadzieję, że nauczyłeś się czegoś z tego wprowadzenia do tego
ważnego języka, który staje się coraz ważniejszy w świecie przetwarzania
w technologii klient-serwer. Napisałem ten dokument aby dać coś
wartościowego sieci i społeczności sieciowej. W rzeczywistości dowiedziałem
się, że ten dokument jest używany np. w wielu collegach w klasach zajmujących
się bazami danych. Wzmiankę o tym dokumencie można znaleźć też w książce
Waite Publishing o Borland C++ Builder. Dodatkowo chciałbym podziękować
wszystkim ludziom ze wszystkich pięciu kontynentów, którzy skontaktowali się
ze mną w sprawie tego dokumentu.
Mam też nadzieję kontynuować i dodawać nowy materiał do tego
podręcznika - taki jak tematy o projektowaniu baz danych, niestandardowych
rozszerzeniach SQL, choć wolałbym unikać materiału o konkretnych DBMS-ach.
Wszystkiego najlepszego w twoich SQL-owych i innych komputerowych
przygodach.
Jim Hoffman
Komentarze lub sugestie? Napisz do mnie pod jhoffman@one.net.
A może chcesz spojrzeć na strony Jima Hoffmana, by dowiedzieć się
więcej o mnie, lub obejrzeć mój nowy podręcznik algorytmów. Możesz też
ściągnąć PDF-ową wersję tego dokumentu (dzięki uprzejmości Matta Kelly'ego z
highcroft.com).
Copyright 1996-1998, James Hoffman. Dokument ten może być używany za
darmo przez każdego użytkownika internetu, ale nie może być włączany do
innych dokumentów, publikowany w żadnej formie, lub reprodukowany masowo w
inny sposób.
1998 tłumaczenie na język polski za zgodą autora - Przemek Borys
<<>pborys@p-soft.silesia.linux.org.pl>
Komentarze odnośnie tłumaczenia mile widziane.