Polski podręcznik SQL
Podstawy polecenia SELECT
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.
Wybór warunkowy
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 |
Operatory relacyjne
Istnieje sześć operatorów relacyjnych w SQL. Po ich przedstawieniu, omówimy jak się z nich korzysta:
· = Równy
· <> lub != (zobacz w instrukcji) Nierówny
· < Mniejszy
· > Większy
· <= Mniejszy lub równy
· >= Większy lub równy
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 (').
Bardziej skomplikowane warunki: warunki łączone
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);
IN oraz BETWEEN
Ł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.
Używanie LIKE
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.
Połączenia
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 |
Klucze
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.
Dokonywanie połączenia
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.
DISTINCT i eliminowanie duplikatów
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).
Aliasy i podzapytania w in
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ę:
· 1. "Nazwisko" i "Zamawiany przedmiot" w linii Select dają nagłówki do raportu.
· 2. Zam i Posiad to aliasy; są to nowe nazwy dla dwóch tabel wymienionych w klauzuli FROM, które sa używane jako przedrostki w notacjach kropkowych nazw kolumn w zapytaniu. To wyklucza zaistnienie niejednoznaczności, szczególnie w equijoin'nie klauzuli WHERE, gdzie obie tabele mają kolumny IDPosiadacza, a notacja kropkowa mówi SQL, że bierzemy dwa różne IDPosiadacza z różnych tabel.
· 3. Zauważ, że tabela Zamowienia jest wymieniona jako pierwsza w polu FROM; to zapewnia, że listing jest wykonywany na jej podstawie, a tabela PosiadaczeAntykow jest wykorzystywana tylko do uzyskiwania dodatkowych informacji (nazwisk).
· 4. Co najważniejsze, AND w poleceniu WHERE wymusza wywołanie podzapytania In. To, co tu się teraz dzieje to wywołanie podzapytania, zwracającego wszystkie Rzeczy istniejące w tabeli Antyki, gdyż nie ma tam polecenia WHERE. Dalej, dla wiersza z tabeli Zamowienia, aby został on wylistowany, musi istnieć odpowiednik w liście rzeczy tabeli Antyki. Możesz myśleć o tym w następujący sposób: podzapytanie zwraca zestaw rzeczy, z którym jest porównywane każde ZadaneZamowienie z tabeli Zamowienia; Warunek In jest prawdziwy, Gdy ZadaneZamowienie jest w (ang. in) zestawie zwróconym z tabeli Antyki.
· 5. Zauważ też, że w tym wypadku istniał antyk dla każdego zamówienia... Oczywiście nie będzie tak zawsze. W dodatku zauważ, że gdy użyte zostanie IN, "= ANY", lub "= SOME", to te słowa kluczowe odnoszą się do dowolnych możliwych trafień wierszy, nie trafień kolumn... to znaczy, nie możesz wstawić wielu kolumn w poleceniu Select podzapytania w próbie porównania kolumny w zewnętrznym poleceniu Where do jednej z wielu możliwych wartości kolumn z podzapytania; tylko jedna kolumna może być wynikiem podzapytania, a porównanie (we właściwym zapytaniu, nie podzapytaniu) może nastąpić tylko z zestawem wierszy zwróconych przez podzapytanie, nie vice-versa.
No! Wystarczy narazie na temat złożonych zapytań SELECT. Przejdźmy do innych spraw związanych z SQL.
Różnorakie opcje SQL
Funkcje agregacyjne
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.
SUM () zwraca sumę wartości z wierszy spełniających zapytanie dla kolumny numerycznej.
AVG () zwraca średnią dla danej kolumny.
MAX () zwraca największą wartość w danej kolumnie.
MIN () zwraca najmniejszą wartość w danej kolumnie.
COUNT(*) zwraca liczbę określającą ilość wierszy spełniających warunki.
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).
Zestawienia
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ń.
Tworzenie nowych tabel
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:
Char(x) - kolumna znakowa, gdzie x oznacza maksymalną ilość znaków dozwolonych w tej kolumnie.
Integer - Kolumna liczb całkowitych, dodatnich lub ujemnych.
Decimal(x, y) - Kolumna liczb dziesiętnych, gdzie x to maksymalna ilość cyfr w tej kolumnie, a y to maksymalna ilość cyfr w części ułamkowej. Maksymalna liczba dla deklaracji Decimal(4,2) to 99.99.
Date - kolumna z datą w formacie zależnym od DBMS-a
Logical - kolumna, która może przechowywać tylko dwie wartości: TRUE lub FALSE (prawda lub fałsz).
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.
Uzupełnianie tabel
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ą).
Dodawanie danych
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');
Usuwanie danych
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żanie danych
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.
Różne Tematy
Indeksy
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);
GROUP BY oraz HAVING
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;
Więcej podzapytań
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 oraz ALL
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.
Unie i połączenia zewnętrzne
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...
Embedded SQL -- brzydki przykład (nie pisz takich programów... chyba że w celach wywołania kłótni)
/* 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();
}
Najczęściej zadawane pytania o SQL
(zobacz odnośnik do FAQ jeśli chcesz więcej)
· 1. Dlaczego nie mogę po prostu zapytać o pierwsze trzy wiersze tabeli?
--Ponieważ w relacyjnych bazach danych wiersze nie są poukładane w konkretnym porządku, tj. system wstawia je wg. swojego uznania, z czego wynika, że możesz żądać wybierania wierszy tylko używając określonych funkcji SQL takich jak ORDER BY, itd.
· 2. Co to DDL i DML o których słychać?
--DDL (Data Definition Language) odnosi się (w SQL) do instrukcji Create Table... DML (Data Manipulation Language) odnosi się do instrukcji Select, Update, Insert i Delete.
· 3. Czyż bazy danych to nie są zwykłe pliki?
--No... DBMS-y składują dane w plikach zdefiniowanych przez administratorów systemu zanim nowe bazy są utworzone (w dużych systemach), ale system składuje dane w specjalnym formacie i może rozdzielać dane z jednej bazy na wiele plików. W świecie baz danych zbiór plików stworzonych dla bazy danych jest nazywany "tablespace" (obszar tabeli). Ogólnie, w małych systemach wszystko o bazie danych (definicje i dane tabel) są trzymane w jednym pliku.
--Nie. Z dwóch powodów. Po pierwsze arkusze mogą mieć dane w komórkach, a komórka to trochę więcej niż kolumna w wierszu. Zależnie od oprogramowania, komórka może mieć formuły i formatowanie, co w tabelach baz danych nie istnieje (jak narazie). Po drugie, komórki arkusza często zależą od danych w innych komórkach. W bazach danych "komórki" są niezależne, nie licząc tego, że kolumny są logicznie powiązane. Oraz tego, że czasem mogą zaistnieć obce lub podstawowe klucze.
· 5. Jak mogę zaimportować plik tekstowy do bazy danych?
--Nie możesz zrobić tego bezpośrednio... Musisz użyć narzędzia takiego jak Oraclowy SQL*Loader, lub napisać program, który załaduje dane do bazy. Program taki musiałby zwyczajnie przejść przez każdy rekord pliku tekstowego, złamać go na kolumny i dokonać operacji Insert na bazie danych.
· 6. Czym jest schemat?
--Schemat jest logicznym zestawem tabel, takich jak tabela antyków powyżej. Zwykle mówi się o tym zwyczajnie jako o "bazie danych", ale baza danych może mieć więcej niż jeden schemat. Np. schemat gwiezdny (star schema) jest zestawem tabel, gdzie najważniejsze informacje przechowuje jedna, wielka, centralna tabela, która jest połączona poprzez obce klucze z tabelami przechowującymi informacje uzupełniające.
· 7. Jakie możesz mi dać ogólne wskazówki, aby moje zapytania SQL i moje bazy były lepsze i szybsze (zoptymizowane)?
Powinieneś spróbować zapobiec wyrażeniom Select takim jak SELECT KolumnaA + KolumnaB, itd. Optymizer zapytań bazy, czyli ta część DBMS-a, która wyszukuje najlepszej drogi do uzyskania żądanych danych, potrzebuje więcej czasu kiedy przetwarza takie wyrażenia, niż gdyby je wybrać normalnie.
Zmniejsz ilość kolumn włącznych w klauzuli Group By.
Gdy używasz połączeń, staraj się używać indeksowanych kolumn na łączeniach.
Gdy nie jesteś pewny, indeksuj.
O ile nie robisz wielokrotnych zliczeń, lub złożonego zapytania, używaj lepiej COUNT(*) niż COUNT(Nazwa_Kolumny).
· 8. Czym jest normalizacja?
--Normalizacja to technika tworzenia baz danych, która sugeruje użycie pewnch kryteriów przy tworzeniu układu tabel (określenie, która tabela będzie miała jakie kolumny oraz stworzenie struktur kluczy), mająca na celu eliminację redundancji w danych pozakluczowych. O normalizacji zwykle mówi się w kategoriach form; przedstawię więc trzy pierwsze formy.
First Normal Form odnosi się do rozdzielania danych do oddzielnych tabel (dane w każdej tabeli są zbliżonego typu) oraz dodawania każej tabeli klucza podstawowego.
Wstawianie danych w Second Normal Form wymusza usuwanie danych z innych tabel, które bazują tylko na części klucza. Np. gdybym zostawił imiona posiadaczy antyków w tabeli z rzeczami, to nie byłoby to w 2NF, gdyż te dane byłyby redundantne (zbędne); imię byłoby powtarzane dla każdej posiadanej rzeczy; jako takie więc, imiona zostały wstawione do swojej własnej tabeli. Imiona same w sobie nie mają nic wspólnego z rzeczami, jak identyfikatory kupców i sprzedawców.
Third Normal Form wymusza pozbywanie się wszystkiego z tabel, co nie zależy od klucza podstawowego. Włączaj tylko informację zależną od danego klucza, a dane niezależne przerzuć do innych tabel, które sa niezależne od klucza podstawowego i utwórz klucze podstawowe dla nowych tabel.
Każda z form nakłada pewną redundancję, a jeśli dane są w 3NF, to są one od razu w 1NF i 2NF. Ogólnie, składuj dane w ten sposób, by żadna kolumna niezwiązana z podstawowym kluczem dana nie zależała tylko od klucza podstawowego. Jeśli rzucisz okiem na przykładową bazę danych, zobaczysz że sposobem nawigowania w takiej bazie danych jest tworzenie połączeń, korzystając z kolumn kluczowych.
Dwa inne ważne punkty tworzenia baz danych, to używanie dobrych, konsekwentnych, logicznych, pełno-słownych nazw dla tabel, kolumn, oraz użycie pełnych słów w samej tabeli. Jeśli chodzi o ostatni punkt, moja baza tu trochę zostaje w tyle, jako że użyam kodów numerycznych do identyfikacji. Zwykle najlepiej jest (o ile jest to możliwe) używanie kluczy, które są zrozumiałe same przez się; np. lepszym kluczem byłyby pierwsze cztery litery nazwiska i pierwszy inicjał posiadacza, np. JONEB dla Billa Jones'a.
· 9. Jaka jest różnica między jednowierszowym zapytaniem, a wielowierszowym zapytaniem i dlaczego ważnym jest znać różnicę?
--Po pierwsze, zapytanie jednowierszowe to takie, które zwraca tylko jeden wiersz w wyniku, a wielowierszowe to takie, które zwraca więcej niż jeden wiersz. To czy zapytanie zwraca jeden lub więcej wierszy, zależy wewnętrznie od projektu (schematu) tabel bazy. Jako twórca zapytań, musisz być świadom schematu, być pewnym włączenia dostatecznej ilości warunków oraz sprecyzować swoją instrukcję SQL we właściwy sposób, tak byś otrzymał oczekiwany wynik (w jednym lub wielu wierszach). Np. jeśli chciałbyś być pewny, że zapytanie do tabeli PosiadaczeAntykow zwróci tylko jeden wiersz, rozważ warunek równości z podstawowym kluczem tej tabeli, IDPosiadacza.
Na myśl przychodzą trzy powody, dla których fakt ten jest ważny. Po pierwsze, otrzymywanie wielu wierszy w miejscu gdzie oczekujesz tylko jednego i vice-versa może oznaczać, że zapytanie jest błędne, że baza jest niekompletna, lub po prostu że dowiedziałeś się czegoś nowego o swoich danych. Po drugie, jeśli korzystasz z instrukcji Update lub Delete, to lepiej byś był pewien, że instrukcja, którą wykonujesz wykonuje działanie na określonym wierszu (wierszach)... w przeciwnym wypadku możesz skasować lub poprawić więcej wierszy niż chciałeś. Po trzecie, zapytania pisane w Embedded SQL musza być dokładnie przemyślane jeśli chodzi o ilość zwracanych wierszy. Gdy piszesz jednowierszowe zapytanie, spodziewasz się tylko jednego wiersza, aby program działał logicznie. Jeśli jednak twoje zapytanie zwróci więcej wierszy, będziesz musiał użyć instrukcji Fetch, a pewnie też pewnego rodzaju pętli w programie, aby przetworzyć każdy zwracany wiersz.
· 10. Czym są relacje?
--Następne pytanie z zakresu projektowania... pojęcie "relacje" odnoszą się do relacji między podstawowymi a obcymi kluczami pomiędzy tabelami. Koncepcja ta jest bardzo ważna, gdyż kiedy projektujemy tabele relacyjnej bazy danych, te relacje muszą zostać określone w celu zdecydowania, które kolumny są, a które nie są podstawowymi lub obcymi kluczami. Możliwe, że słyszałeś o diagramie relacji, który jest graficznym obrazem tabel w schemacie bazy danych, z liniami łączącymi powiązane kolumny między tabelami. Obejrzyj przykładowy diagram na końcu tej sekcji, lub niektóre z site'ów poniżej w celu rozwinięcia tego tematu, jako że istnieje wiele sposobów rysowania diagramów. Ale najpierw spójrzmy na wszystkie rodzaje relacji...
Relacja jeden-do-jednego oznacza, że masz kolumne klucza podstawowego, która jest powiązana z kolumną obcego klucza i że dla każdej wartości klucza podstawowego istnieje jedna wartość klucza obcego. Np. w naszym pierwszym przykładzie TablicaAdresowPracownikow dodajmy kolumnę IDPracownika. Wtedy tabela TablicaAdresowPracownikow będzie powiązana z tabelą TablicaStatystykPracownikow. Każdy pracownik z TablicaAdresowPracownikow ma swoje statystyki (jeden wiersz danych) w TablicaStatystykPracownikow. Będzie to relacja 1-1. Zwróć uwagę na słowo "ma" (ma swoje statystyki)... gdy mówimy o relacjach, ważnym jest opisywanie ich czasownikiem.
Dwa pozostałe rodzaje relacji mogą używać lub nie logicznych kluczy podstawowych oraz kluczy obcych... Wszystko zależy od projektanta. Pierwsza z nich to relacja jeden-do-wielu ("1-M", czyli 1-many). Oznacza to, że dla każdej kolumny w jednej tabeli istnieje jedna lub więcej związanych wartości w drugiej tabeli. Można dodawać klucze do projektu, lub może po prostu użyć jakiegoś identyfikatora w którejś z kolumn i przeznaczyć go do zestawienia relacji. Przykładem może być to, że dla każdego IDPosiadacza w tabeli PosiadaczeAntykow istniała jedna lub więcej (lub zero) Rzeczy zakupionych z tabeli Antyki (czasownik: kupić).
W końcu pozostaje nam relacja wiele-do-wielu ("M-M" many-many). Ta relacja nie wymusza generalnie użycia kluczy, lecz zwykle wymusza identyfikowanie kolumn. Tradycyjna relacja "M-M" oznacza, że jedna kolumna danej tabeli ma relację w pewnej kolumnie drugiej tabeli oraz że jedna wartość z pierwszej tabeli ma wiele odpowiedników w drugiej tabeli i odwrotnie. Częściej zdarza się jednak, że dwie tabele mają między sobą nawzajem relacje 1-M. [Złym] przykładem tej częstszej sytuacji mogłaby być sytuacja, kiedy masz bazę przydziału pracy, gdzie jedna tabela przechowuje jeden wiersz dla każdego zadania i zatrudnionych pracowników, oraz wiele wierszy dla każdej pracy w drugiej tabeli, jedna dla każdego pracownika zatrudnionego w projekcie. Te tabele mają relacje M-M: każdy pracownik w pierwszej tabeli ma wiele zatrudnień w drugiej tabeli, a każda praca ma wielu pracowników do niej przydzielonych, znajdujących się w pierwszej tabeli. To wierzchołek góry lodowej jeśli chodzi o ten temat... obejrzyj linki poniżej dla dalszych informacji.
Sample Simplified Entity-Relationship Diagram
· 11. Jakie istnieją niestandardowe rozszerzenia SQL (bardzo popularne pytanie)?
--Zobacz następny rozdział...
______________________________________________________________________
Podsumowanie składni--dla zaawansowanych użytkowników
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:
· 1. NULL lub NOT NULL (patrz wyżej)
· 2. UNIQUE wymusza unikalność każdego wiersza w kolumnie
· 3. PRIMARY KEY mówi bazie danych, że ta kolumna jest kolumną podstawowego klucza (używane tylko gdy klucz jest jednokolumnowym kluczem, w przeciwnym wypadku instrukcja PRIMARY KEY(kolumna, kolumna, ...) powinna się pojawić na końcu polecenia, za definicją ostatniej kolumny.
· 4. CHECK umożliwia dodanie warunku, który będzie sprawdzany przy wprowadzaniu danych do tej kolumny; np. CHECK (CENA > 0) spowoduje, że system będzie sprawdzał kolumnę Cena czy przypadkiem nie jest większa niż zero, zanim przyjmie wartość...
· 5. DEFAULT wstawia domyślną wartość do bazy danych, jeśli wiersz jest wprowadzony bez uwzględninia danej kolumny. Np. ZYSKI INTEGER DEFAULT = 10000
· 6. FOREIGN KEY działa tak jak PRIMARY KEY, ale następuje po nim:
· REFERENCES <<>NAZWA TABELI> (), które odnoszą się do referencyjnego klucza podstawowego.
· CREATE VIEW <<>NAZWA TABELI> AS <<>ZAPYTANIE>;
· DELETE FROM <<>NAZWA TABELI> WHERE <<>WARUNEK>;
· INSERT INTO <<>NAZWA TABELI> [(<<>LISTA KOLUMN>)] VALUES (<<>LISTA WARTOSCI>);
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.