Kurs języka SQL

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

 Podstawy polecenia SELECT

• Wybór warunkowy

• Operatory relacyjne

• Warunki łączone

• IN oraz BETWEEN

• Używanie LIKE

 Połączenia

• Klucze

• Dokonywanie połączenia

• Eliminacja duplikatów

• Aliasy i podzapytania w In

 Różnorakie opcje SQL

• Funkcje agregacyjne

• Zestawienia

• Tworzenie nowych tabel

• Poprwianie tabel

• Dodawanie danych

• Usuwanie danych

• Odświerzanie danych

 Indeksy

• GROUP BY oraz HAVING

• Więcej podzapytań

• EXISTS oraz ALL

• Unie i połączenia zewnętrzne

• Embedded SQL

• Najczęściej zadawane pytania o SQL

• Niestandardowy SQL

• Podsumowanie składni

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.

Transfer interrupted!

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

______________________________________________________________________

Niestandardowy SQL..."sprawdź lokalną dokumentację"

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

• ABS(X) Moduł z X.

• CEIL(X) Zaokrąglenie w górę dziesiętnego X-a.

• FLOOR(X) Zaokrąglenie w dół dziesiętnego X-a.

• GREATEST(X,Y) Zwraca największą z tych dwóch wartości.

• LEAST(X,Y) Zwraca najmniejszą z tych dwóch wartości.

• MOD(X,Y) Zwraca reszte z dzielenia X/Y.

• POWER(X,Y) Zwraca X do potęgi Y.

• ROUND(X,Y) Zaokrągla X do Y miejsc po przecinku. Gdy Y jest pominięte, zaokrąglenie następuje do najbliższej liczby całkowitej.

• SIGN(X) Zwraca minus gdy X<0, lub plus w innym wypadku.

• SQRT(X) Zwraca pierwiastek kwadratowy z X.

Funkcje znakowe:

• LEFT(<<>tekst>,X) Zwraca pierwsze X znaków z lewej strony tekstu.

• RIGHT(<<>tekst>,X) J.w. tylko z prawej strony.

• UPPER(<<>tekst>) Zmienia tekst na duże litery.

• LOWER(<<>tekst>) Zmienia tekst na małe litery.

• INITCAP(<<>tekst>) Zmienia tekst do oryginalnych caps'ów.

• LENGTH(<<>tekst>) Zwraca liczbę znaków w tekście.

• <<>tekst>||<<>tekst> Łączy dwa teksty w jeden połączony tekst, gdzie po pierwszym tekście natychmiast następuje następny.

• LPAD(<<>tekst>,X,'*') Wypełnia tekst od lewej określonymi znakami (tu znakami '*'), aby tekst nabrał X znaków długości.

• RPAD(<<>tekst>,X,'*') J.w. ale wypełnia z prawej strony.

• SUBSTR(<<>tekst>,X,Y) Wyciąga Y liter z miejsca tekstu wskazywanego przez X.

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

Procedury, funkcje i pakiety w SQL

Procedury funkcje i pakiety są to obiekty zapisywane w bazie danych tak jak inne obiekty. Jest także możliwość definiowania funkcji i procedur w blokach PL/SQL, ich użycie jest wtedy ograniczone do jednego bloku. Raz zapisane w bazie danych procedury i funkcje mogą być używane w każdej aplikacji bazy danych, przez każdego użytkownika (zakładając posiadanie odpowiednich uprawnień tak samo jak w przypadku dowolnych obiektów bazy danych).

Typowe sytuacje ich użycia są następujące:

1. zdefiniowanie podstawowych funkcji używanych przez aplikacje np.

• utworzenie zamówienia

• usunięcie klienta

przechowywanie w bazie danych zadań typu wsadowego np.

• tworzenie tygodniowego zestawienia rachunków

• zapisanie w bazie danych transakcji np.

• zebranie i przetworzenie informacji z odległych baz danych

• utworzenie generycznego, dzielonego kodu dla aplikacji, przechowywanego na serwerze

Definicje procedury ma następującą składnie:

CREATE [OR REPLACE]
PROCEDURE nazwa(lista_parametrów_formalnych){AS|IS}
blok PL/SQL bez słowa kluczowego DECLARE

Słowo REPLACE oznacza, że w sytuacji, gdy procedura o tej samej nazwie już istnieje, to nie należy podnosić z tego powodu błędu, a tylko zastąpić istniejący obiekt przez nowy. Jest to użyteczne przy testowaniu i uruchamianiu procedur, gdy wielokrotnie trzeba zmienić ich treść.

Są trzy typy przekazywania parametrów:

1. IN - wartość przychodzi z wywołującej jednostki programu i nie ulega zmianie w procedurze lub funkcji

2. OUT - żądana wartość nie przychodzi z wywołującej jednostki programu; przy zwykłym zakończeniu (bez błędu) wartość parametru jest przekazywana do wywołującej jednostki programu

3. IN OUT (domyślny) - wartość przychodzi z wywołującej jednostki programu i przy zwykłym zakończeniu (bez błędu) wartość parametru jest przekazywana do wywołującej jednostki programu

Specyfikując typ danych parametru formalnego nie podaje się jego rozmiaru, a więc dla parametru liczbowego używany jest NUMBER, dla typu napisowego zmiennej długości typ VARCHAR2. Można też używać specyfikacji typu danych odwołującego się do typu danych istniejącej kolumny:
tabela.kolumna%type


Przykład
Wpłatę na konto można zrealizować przy pomocy następującej procedury:

CREATE OR REPLACE
PROCEDURE credit (acc_no IN NUMBER, amount IN NUMBER) AS
BEGIN
UPDATE Account
SET Balance = Balance + amount
WHERE account_id = acc_no;
END;

Podobnie wygląda definicja funkcji:

CREATE [OR REPLACE]
FUNCTION nazwa (lista-parametrówJormalnych)
RETURN typ {AS I IS}
blok PL/SQL bez słowa kluczowego DECLARE z instrukcją
RETURN wyrażenie.

Przykład
Oto przykład funkcji zwracającej stan konta.

CREATE OR REPLACE FUNCTION get_balance (acc_no IN NUMBER)
RETURN NUMBER
IS
value NUMBER;
BEGIN
SELECT Balance INTO value FROM Account
WHERE account_id = acc_no;
RETURN value;
END;

Parametrom procedur i funkcji można przyporządkowywać wartości domyślne.. Umieszcza się je wówczas na końcu listy parametrów formalnych.

CREATE OR REPLACE wpisz_studenta(
Imie IN Studenci.Imię%TYPE,
Nazwisko IN Studenci.Nazwisko%TYPE,
Rok IN Studenci.Rok%TYPE DEFAULT 1)
AS
Indeks Studenci.Indeks%TYPE;
BEGIN
SELECT NVL(Max(Indeks)+l,l) INTO Indeks
FROM Studenci;
INSERT INTO Studenci VALUES(Indeks,Imię,Nazwisko,Rok);
END;

Gdy wpisujemy studenta pierwszego roku piszemy:
wpisz_studenta('Jaś', 'Fasola') ;
Gdy student z innej uczelni przenosi się do nas od razu na rok 2, piszemy:
wpisz_studenta('Jaś', 'Fasola '12) ;

Gdy jest więcej parametrów o wartościach domyślnych np. w procedurze wpisz_studenta mógłby być jeszcze parametr
Data DATE DEFAULT Sysdate
wybór parametrów domyślnych, którym w chwili wywołania procedury przypisuje się wartości, dokonuje się przez wskazanie explicite nazwy parametru np.
wpisz_studenta('Jaś', 'Fasola' I Data => '1-WRZ-99') ;

Funkcji zapisanych w bazie danych można używać w instrukcjach SQL w taki sam sposób jak funkcji standardowych pod warunkiem, że nie zmieniają bazy danych ani nie mają parametrów wyjściowych. Nie powinny też korzystać ze zmiennych nielokalnych zadeklarowanych w pakietach. Wszystkie parametry muszą zostać wyspecyfIkowane i nie wolno używać dla nich notacji typu Data => ' l-WRZ-99 '. Każda procedura i funkcja może podnieść swój błąd i przekazać go do aplikacji, która ją wywołała:
Raise_appl ica tion_error(numer _błędu, tekst_komunikatu);
Zakres numerów od -20000 do -20999 jest zarezerwowany dla błędów definiowanych w procedurach i funkcjach.
Informację o parametrach procedury lub funkcji uzyskujemy od systemu za pomocą instrukcji DESCRIBE 1 np.
DESCRIBE aktualizuj_zarobki
DESCRIBE stan_konta

Do oglądania treści procedury zapisanej w bazie danych służy perspektywa słownika danych o nazwie user_source. Najwygodniej jest używać ją w następujący sposób:

COLUMN Line FORMAT 99999
COLUMN Text FORMAT A8O
SET PAGESIZE 23
SET PAUSE ON
SELECT Line, Text
FROM User_Source
WHERE Nam e = 'Nazwa-procedury';


Nazwę procedury (ewentualnie funkcji lub pakietu) należy napisać dużymi literami! System wypisze tekst procedury podając numery linii, przy użyciu których łatwiej jest wyznaczyć miejsce wystąpienia błędu przy kompilacji procedury (to jest przywykonywaniu instrukcji CREATE PROCEDURE). Na etapie testowania i usuwania błędów po każdej instrukcji CREATE PROCEDURE należy wykonywać (ewentualnie umieszczać w skrypcie, jeśli kompilowane procedury znajdują się w skrypcie) instrukcję SQL*Plus,
showerrors
która, gdy wystąpią błędy, wypisze je na ekran.
Przy testowaniu i wykrywaniu błędów semantycznych można używać procedury
DBMS_OUTPUT. Put_line(tekst)
Wyświetlany na ekranie tekst może zawierać w sobie aktualne wartości zmiennych np,
DBMS_OUTPUT.Put_line('Pracownik: ' || Nazwisko || ' Zarobki: ' ||TO_CHAR(Zarobki))
Gdy zmieniają się obiekty, do których odwołuje się procedura lub funkcja, Oracle automatycznie dokonuje ponownej kompilacji, gdy ta procedura lub funkcja zostanie wywołana. Konieczność ponownej kompilacji można odczytać z perspektywy słownika danych User_objects:

SELECT Status
FROM User_objects
WHERE Object_name = 'OBLICZ_ZAROBKI' ;

Konieczność ponownej kompilacji wskazuje wartość Status = 'INVALID'.
Aby samemu spowodować wykonanie ponownej kompilacji, używamy instrukcji:
ALTER PROCEDURE Oblicz_zarobki COMPILE; Uprawnienia do użycia procedury (uprawnienie EXECUTE) nadaje się W standardowy sposób np.:
GRANT EXECUTE
ON Oblicz_zarobki
TO Moja księgowa;

Jest możliwość tzw. przeładowania nazw procedur i funkcji tzn. użycia tej samej nazwy wielokrotnie. Jest to wygodne w sytuacji, gdy różne, specjalne przypadki z logicznego punktu widzenia tej samej procedury lub funkcji, możemy zapisać za pomocą zbioru procedur i funkcji używając tej samej nazwy np. dodawanie z różną liczbą argumentów i różnymi typami danych. Wersje z tą samą nazwą muszą się różnić albo liczbą parametrów albo nazwą i typem parametru (aby system był wstanie w chwili wykonywania wybrać właściwą wersję).
Ze względu na dużą liczbę procedur i funkcji, jakie zwykle powstają podczas tworzenia aplikacji, konieczne jest grupowanie ich w większe jednostki nazywane pakietami. W ramach pakietu możemy globalnie dla niego zdefiniować:
- kursory,
- zmienne i stałe (mają trwały charakter),
- wyjątki.

Każda sesja ma swoją własną wersję pakietu. Zmienne i stałe zachowują swoje wartości na czas trwania sesji. Pakiet składa się z dwóch części:
- części publicznej (dostępnej z zewnątrz przy posiadaniu odpowiednich uprawnień), czyli specyfikacji i - części prywatnej (dostępnej tylko z wewnątrz), czyli części implementacyjnej.

Składnia tworzenia specyfikacji pakietu jest następująca:

CREATE (OR REPLACE) PACKAGE nazwa--Pakietu AS
Deklaracje obiektów publicznych (w przypadku procedur i funkcji specyfikacja nagłówków)
END nazwa-pakietu;

Składnia tworzenia części implementacyjnej pakietu jest podobna:

CREATE (OR REPLACE) PACKAGE BODY nazwa-pakietu AS
Definicje obiektów publicznych i prywatnych
END nazwa-pakietu;

Można też na koniec części implementacyjnej dołączyć kod inicjalizacyjny pakietu.

CREATE (OR REPLACE) PACKAGE BODY nazwa-pakietu AS
Definicje obiektów publicznych i prywatnych
BEGIN
Instrukcje inicjalizujące
END nazwa-pakietu;

Przykład

Najpierw specyfikacja pakietu przyjmowania i zwalniania pracowników:

CREATE PACKAGE Obsruga-prac AS
PROCEDURE Zatrudnij(
Numer-prac NUMBER,
Nazwisko VARCHAR2,
Zarobki NUMBER,
Numer_dziaru NUMBER);

PROCEDURE Zwolnij(
Numer-prac NUMBER) ;
Ile-przyjetych NUMBER;
Ile_zwolnionych NUMBER;
END Obsruga-prac;

A następnie część implementacyjna tego pakietu:

CREATE PACKAGE BODY Obsruga-prac AS
/* Procedura zatrudniania pracownika */
PROCEDURE Zatrudnij(
Numer-prac NUMBER, Nazwisko VARCHAR2, Zarobki NUMBER, Numer_dziaru NUMBER)
IS
BEGIN
INSERT INTO Pracownicy VALUES
(Numer-prac, Nazwisko, Zarobki, Numer_dziaru) ;
COMMIT;
Ile-przyjętych : = Ile_przyjętych +1 ;
END;
/* Procedura zwalniania pracownika */
PROCEDURE Zwolnij(
Numer-prac NUMBER)
IS
BEGIN
DELETE FROM Pracownicy
WHERE Id-prac = Numer-prac ;
COMMIT;
Ile_zwolnionych := Ile_zwolnionych +1;
END;
BEGIN
Ile-przyjętych := O;
Ile_zwolnionych := 0;
END Obsruga-prac;

Z zewnątrz dostęp do obiektów publicznych pakietu odbywa się przez podanie nazwy pakietu, kropki i nazwy obiektu np.

Obsługa-prac.Zatrudnij (1000, 'Kowalski', 2000, 12) ;

Jak wspomnieliśmy powyżej, nazwy funkcji i procedur mogą być przeładowane to znaczy tej samej nazwy można użyć dla różnych wersji tej samej procedury lub funkcji. Wersje z tą samą nazwą muszą się różnić albo liczbą parametrów albo nazwą i typem parametru. W przypadku powyższego pakietu, można byłoby określić dwie wersje procedury Zwolnij:

PROCEDURE Zwolnij (Numer-prac NUMBER);
PROCEDURE Zwolnij (Nazwisko VARCHAR2);

Informacje o pakietach można odczytać, w podobny sposób jak w przypadku funkcji i procedur posługując się perspektywą słownika danych user_source.
Oracle dostarcza pewnej liczby standardowych pakietów jak DBMS_OUTPUT . Warto tutaj wspomnieć o pakiecie służącym do wysyłania listów elektronicznych (korzystający z programu ORACLE*Mail), w którym najważniejszą procedurą jest wysyłanie listu elektronicznego, którego treściąjest tekst.

DBMS_MAIL.Send(Od, Do, Cc, Bcc, temat, tekst) ;

Procedury, funkcje i pakiety można usunąć z bazy danych używając odpowiedniej instrukcji DROP PROCEDURE procedura, DROP FUNCTION funkcja bądź DROP PACKAGE pakiet.

Typy danych w MySQL

Typy danych w MySQL

(część II tutoriala)


W poprzednim odcinku mojego kursu opisałem sposób tworzenia prostej bazy danych, składającej się z jednej tabeli. Dzisiaj chciałbym dokładniej opisać podstawowe operacje wykonywane podczas modelowania relacyjnych baz danych. Będę nadal opierać się na bazie MySQL. W przypadku innych produktów składnia języka SQL jest bardzo podobna (jednakże nie jest identyczna) ale radzę zajrzeć do dokumentacji.

Do utworzenia tabeli użyliśmy polecenia CREATE TABLE dokładna składnia (w bazie MySQL) tego polecenia wygląda tak:


CREATE [TEMPORARY] TABLE [IF NOT EXISTS] nazwa_tabeli [(definicja_kolumny,...)] [opcje_tabeli] [wyrażenie_SELECT]


TEMPORARY - w ten sposób tworzymy tabele tymczasowe, które będą później automatycznie tworzone na czas połączenia użytkowników z naszą bazą. W momencie przerwania połączenia użytkownik-baza tabela taka zostaje skasowana.

IF NOT EXISTS - ta klauzula spowoduje utworzenie nowej tabeli ale tylko wtedy gdy tabela (o podanej w polu nazwa_tabeli) nazwie nie będzie już wcześniej istniała w bazie

wyrażenie_SELECT - pozwala na automatyczne załadowanie danych do nowo utworzonej tabeli


definicja_kolumny wygląda tak:

Nazwa_kolumny TYP_DANYCH [NOT NULL | NULL] [DEFAULT wartość_domyślna] [AUTO_INCREMENT] [PRIMARY KEY] [definicja_powiązania]

TYPY DANYCH
Podczas tworzenia tabel bardzo ważną sprawą jest dobranie odpowiednich opisów dla danych, które będą w nich później przechowywane i temu zagadnieniu chciałbym tym razem poświęcić więcej czasu.

DANE NUMERYCZNE

W kolumnach tego typu będziemy umieszczali wartości liczbowe. W zależności od potrzeb mogą to być wartości dokładne (TINYINT, SMALLINT, MEDIUMINT, INT, INTEGER, BIGINT,DECIMAL, NUMERIC) lub przybliżone (FLOAT, DOUBLE, DOUBLE PRECISION, REAL). Musimy się zastanowić jakie wartości będą przyjmować dane w określonej kolumnie i wybrać najbardziej odpowiedni typ dla niej.

Liczby całkowite (INTEGER):

• INT, INTEGER - w takich kolumnach przechowujemy liczby całkowite z zakresu -2147483648 do 2147483647 (4 bajty)

• TINYINT - liczba całkowita z zakresu -128 do 127 (1 bajt)

• SMALLINT - liczba całkowita z zakresu -32768 do 32767 (2 bajty)

• MEDIUMINT - liczba całkowita z zakresu -8388608 do 8388607 (3 bajty)

• BIGINT - liczba całkowita z zakresu -9223372036854775808 do 9223372036854775807 (8 bajtów)

Liczby rzeczywiste (REAL):
Każdy kto miał kiedykolwiek kontakt z programowaniem wie że takie liczby przechowywane są w pamięci komputera w postaci przybliżonej (zapis przy pomocy cechy i mantysy czyli wykładnika potęgi). Tak też jest w przypadku MySLQ'a. Nie będę się w tym momencie rozwodził nad teorią takiego zapisu, gdyż można ją znaleźć w dowolnej książce dotyczącej programowania (np.: Turbo Pascal 7.0 cz. I Andrzeja Marciniaka - tą książkę zna chyba każdy kto kiedykolwiek miał kontakt z techniczną uczelnią w Polsce).

Oto zakresy dostępnych typów rzeczywistych:

• FLOAT (M,D)- liczba rzeczywista z zakresu -3.402823466E+38 do -1.175494351E-38 i 0 i 1.175494351E-38 do 3.402823466E+38 (4 bajty),

• REAL, DOUBLE - liczba rzeczywista z zakresu 1.7976931348623157E+308 do -2.2250738585072014E-308 i 0 i 2.2250738585072014E-308 to 1.7976931348623157E+308 (8 bajtów)

Ostatnim bardzo przydatnym i wygodnym typem numerycznym jest: DECIMAL (M,D) gdzie M jest ilością znaczących pozycji w liczbie a D skalą liczby np.: DECIMAL(9,2) będzie miał zakres od -9999999.99 do 9999999.99 jest to liczba składająca się z dziewięciu pozycji i przesunięta o dwa miejsca w prawo (liczba zajmuje M bajtów, jeśli M<D to D+2 bajty). DECIMAL jest doskonałym typem do przechowywania wartości "pieniężnych" np.: cen, gdyż przechowuje informacje w sposób dokładny, oczywiście w taki sposób w jaki zdefiniujemy.

Czasowe typy danych w MySQL'u

Jak pewnie każdy się domyśla służą do przechowywania wartości związanych z czasem czyli roku, daty, godziny lub wszystkiego równocześnie. Możemy skorzystać z następujących typów:

• DATETIME - w takiej kolumnie umieszczamy datę wraz z godziną w formacie : YYYY:MM:DD:HH:mm:SS (odpowiednio rok, miesiąc, dzień, godzina, minuta i sekunda), typ ten akceptuje zakres od '1000-01-01 00:00:00' do '9999-12-31 23:59:59'

• DATE - czyli data w formacie YYYY:MM:DD (rok, miesiąc, dzień), zakres od '1000-01-01' do '9999-12-31'

• TIME - godzina w formacie HH:mm:SS (godzina, minuta, sekunda)

• YEAR - rok YYYY (np.:2001) zakres od 1901 do 2155 (1 bajt)

• TIMESTAMP(n) - jest to data z godziną, precyzję tego zapisu ustalamy sami poprzez wartość n (dla przykładu n=14 oznacza datę i godzinę w formacie YYYY:MM:DD:HH:mm:SS a n=2 YY) zakres od 1970 do 2037

Łańcuchowe (znakowe) typy danych
Przy ich pomocy umieszczamy w bazie danych, informacje typowo tekstowe (łańcuchy znaków). Do naszej dyspozycji są następujące typy:

• CHAR(n) - jest to łańcuch znaków o długości n, gdzie n może przyjmować wartości od 0-255, bez względu na to jaki łańcuch zapiszemy do takiej komórki tabeli, zawsze będzie zajmował n bajtów.

• VARCHAR(n) - ten typ danych pamięta łańcuch znaków oraz jego długość, wartość n określa maksymalną długość łańcucha, np.: jeśli do kolumny VARCHAR(10) zapiszemy łańcuch o długości 5 znaków to będzie on zajmował w bazie 6 bajtów

• BLOB - "pamięta" dane w formie binarnej, ze względu na maksymalną długość takiego ciągu znaków dzieli się na kilka podtypów: TINYBLOB (2^8 znaków), BLOB (2^16 znaków), MEDIUMBLOB (2^24 znaków), LONGBLOB (2^32 znaków)

• TEXT - dane tekstowe, podobnie jak BLOB ma odmiany ze względu na długość tekstu: TINYTEXT (2^8 - 256 znaków ), TEXT (2^16 - 65535 znaków), MEDIUMTEXT (2^24 -16777216 znaków), LONGTEXT (2^32 - 4294967296 znaków)

Typ wyliczeniowy ENUM
dane przyjmują wartości spośród wcześniej przygotowanej przez nas listy. Lista taka może mieć maksymalnie 65535 elementów.

Typ SET
typ zbiorowy - pozycja taka może przyjmować 0 lub więcej wartości spośród listy przygotowanej podczas tworzenia tabeli. Przykład : dla definicji SET ("jeden" , "dwa") do komórki tabeli możemy zapisać następujące wartości:
"" lub "jeden" lub "dwa" lub "jeden, dwa". Definicja SET może mieć maksymalnie 64 elementy.

Mniej doświadczonym użytkownikom radzę przed przystąpieniem do tworzenia tabel wydrukować sobie ten dokument i mieć go pod ręką, gdyż nie widzę sensu uczenia się tych wszystkich wartości na pamięć. W tym momencie pozwolę sobie przerwać opis typów danych, aby zgodnie z obietnicą pokazać jak generować statystyki naszej bazy. Przy tej okazji przybliżę działanie funkcji agregujących : COUNT, SUM, AVG, MIN, MAX.

Funkcja COUNT zlicza rekordy w tabeli korzystamy z niej w następujący sposób:

SELECT COUNT(*) tyle_mamy_klientów from klienci;

Wynikiem takiego zapytania będzie ilość rekordów w tabeli klienci.

Funkcja SUM zwraca sumę elementów danej kolumny:

SELECT SUM(wiek) from klienci;

Dzięki takiemu zapytaniu dowiemy się ile w sumie maja lat nasi klienci - przykład nie jest najlepszy ale pokazuje jak działa ta funkcja.

Aby dowiedzieć się jaki jest średni wiek zarejestrowanych klientów piszemy:

SELECT AVG(wiek) from klienci;

Za wyliczenie średniej arytmetycznej oczywiście odpowiada finkcja AVG

W analogiczny sposób szukamy minimalnego i maksymalnego wieku w bazie:

SELECT MAX(wiek)

from klienci;

SELECT MIN(wiek)

from klienci;

Jesli chcemy zobaczyć kto w naszej bazie jest najstarszy piszemy:

SELECT imie,

nazwisko, wiek FROM klienci ORDER BY wiek DESC

LIMIT 1;

Klauzula ORDER BY określa kolumnę według której mają być posortowane wyniki a DESC powoduje że jest to porządek malejący, przeeciwne działąnie do DESC ma ASC - sortuje w porządku malejącym. Wstawienie ASC w miejsce DESC spowoduje wyświetlenie najmłodszego zarejestrowanego klienta. Dzięki dopisaniu "LIMIT 1" kwerenda zwróci tylko jeden rekord.

Niestety MySQL nie obsługuje jeszcze zapytań typu:

SELECT imie, nazwisko, wiek

FROM klienci

WHERE wiek=(SELECT max(wiek) from klienci);

Zapytania takie działają w np.: MS DATA ENGINE albo SYBASE ale za te serwery trzeba słono zapłacić.

W tym odcinku mojego kursu to już wszystko. Dociekliwych czytelników tradycyjnie odsyłam do dokumentacji MySQL'a. Wszelkie uwagi oraz pytania proszę kierować na [e-mail].

---