Rozdział 20

Język SQL

Język SQL jest ogólnie przyjętym standardem języków baz danych. Niezależnie od tego, jaka firma jest producentem SZBD, jak również do współpracy z jakim systemem operacyjnym SZBD został zaprojektowany, możemy być pewni, że zaimplementowana jest w nim obsługa języka SQL.

Polecenia języka SQL dzielą się na dwie grupy:

• polecenia DDL, służące do definiowania danych

• polecenia DML, służące do modyfikowania danych.

Za pomocą języka DDL możemy utworzyć takie obiekty w bazie danych jak np. tabele. Polecenia języka DML służą do wstawiania, modyfikowania i usuwania danych z istniejących obiektów.

W rozdziale wykorzystamy tabele bazy danych Dziennik utworzone w rozdziałach 18 i 19.

Selekcja pionowa (projekcja)

Z reguły, do utworzenia zestawienia nie wykorzystujemy wszystkich informacji opisujących dany obiekt. Ilość atrybutów opisujących obiekt i przechowywanych w tabeli powodowałaby nieczytelność takiego zestawienia. Na przykład, jeżeli chcielibyśmy utworzyć listę wszystkich uczniów danej klasy, na podstawie której wydrukowalibyśmy zaproszenia na nasze urodziny, dane o imionach rodziców byłyby nam zupełnie niepotrzebne. Porównajmy dwie tabele: pierwsza obrazuje wynik "wyboru" wszystkich kolumn tabeli Uczeń, druga — ograniczenia liczby kolumn do interesujących nas w danym przypadku:

rys 20.1 Lista zaproszonych na nasze urodziny

ERU	Ewa	Rusek	K-ce 40-111, ul. Opiłki 2/1	Jan, Barbara	887-22-22
EST	Ewa	Starska	K-ce 42-212, ul. Jana 1a	Jan, Brygida	215-02-21
JBA	Jaś	Badura	Bytom 33-333, ul.Pawła 2/12	Olek, Grażyna	501-50150112
KST	Krzyś	Strużyna	K-ce 41-011, ul. Piotra 11/1	Witold, Hanna	brak
MJA	Małgosia	Janoska	Gliwice 35-212, ul. Paśki 3b	Marcin, Magda	299-717-921

rys. 20.2 Poprawiona lista naszych gości

Ewa	Rusek
Ewa	Starska
Jaś	Badura
Krzyś	Strużyna
Małgosia	Janoska

W drugim przypadku lista wygląda zdecydowanie lepiej. Efekt ten osiągnęliśmy poprzez selekcję pionową, czyli ograniczenie liczby kolumn, na podstawie których powstała nasza kwerenda do dwóch: kolumny Imię i kolumny Nazwisko.

Aby utworzyć opisywane w tym rozdziale zestawienia i raporty należy z dostępnych obiektów bazy danych wybrać obiekt Kwerendy, kliknąć przycisk Nowy (w rezultacie wywołamy kreatora kwerend), następnie z listy dostępnych możliwości wybrać Widok Projekt. Następnie należy przełączyć Widok Projekt na Widok SQL w sposób pokazany na rys. 20.3

rys. 20.3 Kwerenda przedstawiona w Widoku Projekt

Skoro potrafimy już przejść do tworzenia nowych kwerend w Widoku SQL, możemy spróbować wpisać poniższą instrukcję:

SELECT *

FROM Uczen;

Instrukcja SELECT powoduje pobranie przez SZBD danych z tabeli, której nazwa określona jest poprzez klauzulę FROM. Znak * jest specjalnym znakiem zastępującym nazwy wszystkich kolumn tabeli. Po wykonaniu instrukcji (przełączeniu Widoku SQL na Widok Arkusz danych) na ekranie zobaczymy te same dane, co na rys. 20.1

Aby ograniczyć liczbę kolumn do dwóch, wpiszmy:

SELECT Imię, Nazwisko

FROM Uczen;

W efekcie otrzymamy listę przedstawioną na rys. 20.2 Teraz możemy zapisać naszą nową kwerendę w bazie danych.

Kolejność podawania nazw kolumn jako parametrów polecenia SELECT nie jest obojętna. Jako pierwsze będą zwracane wartości przechowywane w tej kolumnie, której nazwę podaliśmy jako pierwszy parametr.

Mamy już listę naszych przyszłych gości, ale kolejność nazwisk jest dość przypadkowa. Zmodyfikujmy naszą listę tak, aby lista uczniów była uszeregowana alfabetycznie wg. nazwisk uczniów. W tym celu (w Widoku SQL naszej kwerendy) dodajmy do instrukcji SELECT dodatkowy element:

SELECT Imię, Nazwisko

FROM Uczen

order by Nazwisko;

Sprawdzenie efektu tej modyfikacji pozostawiamy Czytelnikowi.

Selekcja pozioma (selekcja)

Załóżmy taką sytuację: jako nauczyciel matematyki w klasie 2a chcemy skontaktować się z rodzicami Jasia Badury. Niestety Jasia od kilku tygodni nie ma w szkole i nie możemy poprosić go o numer telefonu rodziców. Na szczęście informacja o numerze telefonu kontaktowego przechowywana jest w naszej bazie danych. Pozostaje jedynie znaleźć w tabeli Uczeń wiersz w którym zapisane są dane Jasia Badury. Załóżmy dodatkowo, że klasa 2a jest klasą liczną, zapisanych do niej jest 49 uczniów. Nawet po ograniczeniu kolumn do imienia, nazwiska i numeru telefonu otrzymalibyśmy długą listą, w 97% zawierającą nie interesujące nas w danej chwili dane. Zamiast samemu wyszukiwać w liście numeru telefonu do rodziców Jasia możemy wykorzystać do tego SZBD. Utwórzmy nową kwerendę i w Widoku SQL wpiszmy następujące polecenie:

SELECT *

FROM Uczen

WHERE [ID ucznia]='JBA';

Nazwę kolumny ID ucznia umieściliśmy w nawiasie kwadratowym, ponieważ zawiera ona spację. Pominięcie nawiasów zakończyłoby się błędem — SZBD nie potrafiłby jednoznacznie ocenić, którą kolumnę chcemy wykorzystać jako parametr w klauzuli WHERE.

Wartość testu logicznego umieściliśmy w apostrofach. W przeciwnym razie SZBD uznałby, że JBA jest nazwą parametru, a nie jego wartością.

W rezultacie kwerenda zwróci wszystkie przechowywane w kolumnie Uczeń dane opisujące Jasia Badurę. Nas jednak interesuje wyłącznie numer telefonu. Połączmy selekcję pionową z poziomą w następującym poleceniu:

SELECT Imię, Nazwisko, [telefon kontaktowy]

FROM Uczen

WHERE [ID ucznia]='JBA';

W rezultacie SZBD zwróci następujące informację:

Jaś

Badura

501-50150112

Rozważmy kolejny problem: Dyrektor chciałby wiedzieć, ile ocen niedostatecznych wystawiliśmy na koniec semestru w klasie 2a. Zadanie sprowadza się do wybrania z tabeli Ocena tych wierszy, które w kolumnie Ocena przechowują wartość 2, w kolumnie ID przedmiotu wartość "MATMA", a w kolumnie zdobyta za wartość semestr. Rozwiązaniem zadania jest instrukcje:

SELECT Ocena

FROM Ocena

WHERE Ocena=2 AND [ID przedmiotu]='MATMA' AND [Zdobyta za]='semestr';

Na koniec jeszcze jeden przykład. Tym razem chcielibyśmy sprawdzić, które szafki były okradzione w ciągu ostatnich 3 miesięcy. Zadanie sprowadza się do wybrania z tabeli Szatnia tych wierszy dla których data ostatniej kradzieży, przechowywana w kolumnie Data kradzieży należy do zakresu <data dzisiejsza, data dzisiejsza - 91>. Rozwiązaniem zadania jest poniższe zapytanie skierowane do SZBD:

SELECT [Data kradzieży], [Nr szafki], Uwaga

FROM Szatnia

WHERE (Date()-[Data kradzieży])<91;

W efekcie uzyskamy wynik podobny do przedstawionego na rys 20.4

Rys. 20.4 Lista szafek okradzionych w ciągu ostatnich trzech miesięcy

Data kradzieży	Nr szafki	Uwaga
2001-02-23	1	Wyjątkowy bałagan
2001-02-26	10	!!!
2001-02-02	12	coś tam zdechło

Funkcja Date()zwraca aktualną datę systemową.

Zadanie można rozwiązać korzystając z innych, wbudowanych w SZBD funkcji daty i czasu. Takie zmodyfikowanie zapytania, aby SZBD zwracał szafki okradzione w ciągu ostatnich trzech miesięcy, bez względu na to, ile dni mają poszczególne miesiące, pozostawiamy Czytelnikowi.

Dodawanie, modyfikowanie i usuwanie wierszy

Informacje przechowywane w bazie danych nie są niezmienne. Aby dane odpowiadały zmieniającej się rzeczywistości musimy, przy okazji każdej zmiany, uaktualnić bazę. Przyjmijmy, że dyrektor zatrudnił nową nauczycielkę. Fakt ten powinien zostać uwzględniony w naszej bazie. Do dopisywania danych do istniejącej tabeli służy instrukcja INSERT. W bazie Dziennik informacje opisujące każdego nauczyciela przechowywane są w tabeli Nauczyciel. Polecenie dopisania nowej nauczycielki wygląda następująco:

INSERT INTO Nauczyciel ( [ID nauczyciela], Imię, Nazwisko, Adres, [Wychowawca klasy] )

VALUES ('SUSI', 'Samanta', 'Lepsza', 'K-ce, 44-112 ul. Portowa 13c', '4d');

W rezultacie utworzony zostanie nowy wiersz w tabeli Nauczyciel, zawierający w poszczególnych kolumnach podane przez nas wartości.

Rys. 20.5 Tabela Nauczyciel po wykonaniu kwerendy dodającej nową nauczycielkę

ID nauczyciela	Imię	Nazwisko	Adres	Wychowawca klasy	telefon kontaktowy
HISZP	Maurycy	Polak	K-ce, 21-212 ul. Ułanów 2/2	2a	0800-212-121
MIRO	Zdybek	Sęk	K-ce, 26-215 ul. Paski 4a/12	3a	0700-222-221
SUSI	Samanta	Lepsza	K-ce, 44-112 ul. Portowa 13c	4d
SZEL	Michał	Pasek	K-ce, 44-012 ul. Miła 1/1	4c	brak

W tym przypadku nie znamy numeru telefonu Samanty, więc pozostawiliśmy kolumnę telefon kontaktowy pustą. Innym wyjściem z sytuacji byłoby wpisanie słowa 'brak' zamiast numeru telefonu.

W każdym SZDB brak wartości w jakimś polu powoduje wstawienie w to pole specjalnej wartości NULL. Wartość NULL symbolizuje brak informacji o danym atrybucie i nie równa się wartości 0.

Zarządzanie bazą danych nie tylko wymagają dopisywania nowych informacji, ale również zmieniania (aktualizowania) danych już istniejących. Sytuację wymagającą zamiany istniejących danych prześledzimy na przykładzie Ewy. Ewa Starska, pomimo przykrych doświadczeń z przeszłości, nadal zostawia w szatni swoje drugie śniadanie. Jak można się było spodziewać, dzisiaj po zakończeniu lekcji zastała drzwi do szafki wyłamane, a śniadanie zniknęło. Dla nas, jako administratora szkolnej bazy danych Dziennik, oznacza to konieczność uaktualnia wpisu dotyczącego ostatniej kradzieży z szafki Ewy. Najprostsze polecenie języka SQL zmieniające tą datę na dzisiejszą ma postać:

UPDATE Szatnia

SET [Data kradzieży] = date ()

WHERE [ID ucznia]='EST';

Pominięcie klauzuli WHERE w poleceniu UPDATE spowoduje uaktualnienie wszystkich wierszy kolumny Data kradzieży. W praktyce nie używa się tego polecenia bez wskazania, jakich wierszy zmiana ma dotyczyć.

Prześledźmy jeszcze jedną sytuacje wymagającą modyfikacji danych. Jako nauczyciel matematyki zostaliśmy poproszeni o zweryfikowanie semestralnych ocen z tego przedmiotu. Ponieważ sprawdzian mógłby nie przynieść oczekiwanych rezultatów, zdecydowaliśmy się po prostu podnieść semestralną ocenę z tego przedmiotu wszystkim uczniom o 1. Poniżej przedstawiamy polecenie realizujące to zadanie:

UPDATE Ocena

SET Ocena=Ocena+1

WHERE [Zdobyta za]='Semestr' AND [ID przedmiotu]='MATMA';

Efekt zmian przedstawiamy na rys. 20.6

Rys.20.6. Tabela Ocena przed i po wywołaniu kwerendy modyfikującej oceny

ID ucznia	Ocena	Zdobyta za	Przedmiot
ERU	4	semestr	Język angielski
EST	1,5	odpowiedź	Matematyka
EST	3	semestr	Język angielski
JBA	2	sprawdzian	Matematyka
JBA	2	semestr	Matematyka
KST	3,5	semestr	Matematyka
KST	2	semestr	Język angielski
MJA	4	odpowiedź	Matematyka
MJA	4	semestr	Matematyka

ID ucznia	Ocena	Zdobyta za	Przedmiot
ERU	4	semestr	Język angielski
EST	1,5	odpowiedź	Matematyka
EST	3	semestr	Język angielski
JBA	2	sprawdzian	Matematyka
JBA	3	semestr	Matematyka
KST	4,5	semestr	Matematyka
KST	2	semestr	Język angielski
MJA	4	odpowiedź	Matematyka
MJA	5	semestr	Matematyka

W niektórych sytuacjach dalsze przechowywanie danych w bazie jest niepotrzebne. O ile warto przechowywać dane wykorzystywane przy zestawieniach (takie jak oceny uczniów, którzy ukończyli już naszą szkołę), o tyle przedmioty, których nie będzie już w planie lekcji powinny zostać z bazy usunięte. Do usunięcia przedmiotu Filozofia systematyczna wykorzystamy poniższą instrukcję:

DELETE *

FROM [Lista przedmiotów]

WHERE [Id przedmiotu]='FILO';

W rezultacie z tabeli zostaną usunięte wszystkie wiersze spełniające podany warunek.

Nie podanie warunku w klauzuli WHERE spowoduje usunięcie wszystkich danych z tabeli.

Łączenie tabel

Cechą charakterystyczną relacyjnych baz danych jest przechowywanie informacji podzielonych między wiele tabel. Wiąże się to z koniecznością łączenia cząstkowych danych o obiektach. Najczęstszym sposobem powiązania kilku tabel jest połączenie naturalne, realizowane poprzez wskazanie odpowiadających sobie pól w łączonych tabelach. Na przykład, chcąc poznać numery szafek należących do poszczególnych uczniów, musimy wykorzystać informację z tabeli Uczeń (takie jak imię i nazwisko), oraz informację z tabeli Szatnia. Zadanie te realizuje poniższa komenda:

SELECT Uczen.Imię, Uczen.Nazwisko, Szatnia.[Nr szafki]

FROM Uczen INNER JOIN Szatnia ON Uczen.[ID ucznia] = Szatnia.[ID ucznia];

W przypadku pobierania danych z większej liczby kolumn, powinno się poprzedzić nazwę kolumny nazwą tabeli, w której znajduje się dana kolumna. Pozwoli to uniknąć wieloznaczności i w efekcie błędu, który pojawiłby się, gdyby w obu kolumnach znajdowały się tabele o tej samej nazwie.

Rezultatem uruchomienia naszej nowej kwerendy będzie lista podobna do poniższej:

Rys 20.7 Efekt złączenia naturalnego dwóch tabel

Imię	Nazwisko	Nr szafki
Ewa	Rusek	2
Ewa	Starska	10
Jaœ	Badura	12
Krzyś	Strużyna	1
Małgosia	Janoska	8

Kolejnym zadaniem będzie wyświetlenie informacji o ocenach z matematyki wraz z imieniem i nazwiskiem ucznia. Rozwiązanie tego zadania wymaga wykorzystania selekcji, projekcji i złączenia naturalnego w jednym zapytaniu. Pierwszym etapem będzie ograniczenie kolumn w obu łączonych tabelach. Z tabeli Uczeń wykorzystamy dane przechowywane w kolumnach Imię, Nazwisko i ID ucznia, z tabeli Ocena — kolumny: ID ucznia, Ocena, Przedmiot. W drugim etapie wyselekcjonujemy te oceny, które zostały wystawione z matematyki. Wreszcie połączymy obie tabele wykorzystując do tego identyfikator ucznia, jednoznacznie identyfikującą nam ucznia w obu tabelach. Ostatnim krokiem będzie posegregowanie listy ocen według nazwiska ucznia. Rozwiązaniem zadania jest zapytanie:

SELECT Uczen.Imię, Uczen.Nazwisko, Ocena.Ocena

FROM Uczen INNER JOIN Ocena ON Uczen.[ID ucznia] = Ocena.[ID ucznia]

WHERE Ocena.[ID przedmiotu]='MATMA'

ORDER BY Uczen.Nazwisko;

Kolumny wykorzystane do połączenia tabel nie muszą być jawnie wyszczególnione. Natomiast wymienienie ich po słowie kluczowym SELECT spowoduje umieszczenie ich w zastawieniu.

Rys.20.7 Efekt wywołania kwerendy pozbawionej i kwerendy zawierającej kolumnę ID ucznia.

Imię	Nazwisko	Ocena	Przedmiot
Jaś	Badura	2	Matematyka
Małgosia	Janoska	5	Matematyka
Małgosia	Janoska	4	Matematyka
Ewa	Rusek	5,5	Matematyka
Ewa	Rusek	5	Matematyka
Ewa	Starska	1,5	Matematyka
Krzyś	Strużyna	3,5	Matematyka
Krzyś	Strużyna	3,5	Matematyka

Imię	Nazwisko	Ocena	Przedmiot	Uczen.ID ucznia	Ocena.ID ucznia
Jaś	Badura	2	Matematyka	JBA	JBA
Małgosia	Janoska	5	Matematyka	MJA	MJA
Małgosia	Janoska	4	Matematyka	MJA	MJA
Ewa	Rusek	5,5	Matematyka	ERU	ERU
Ewa	Rusek	5	Matematyka	ERU	ERU
Ewa	Starska	1,5	Matematyka	EST	EST
Krzyś	Strużyna	3,5	Matematyka	KST	KST
Krzyś	Strużyna	3,5	Matematyka	KST	KST

Operatory teoriomnogościowe

Z traktowania relacji jako pewnego typu zbiorów wynika, że relacje, tak jak zbiory, można dodawać, odejmować i wyznaczać ich część wspólną.

Do zsumowania tabel służy operator UNION. Proszę zauważyć, że w przeciwieństwie do łączenia tabel, tabele sumowane nie muszą zawierać żadnych wspólnych kolumn. Przypuśćmy, że w szkole doszło do wypadku. Przyszedł policjant i zażądał listy wszystkich osób przebywających w ciągu ostatniego tygodnia w szkole. Tłumacząc prośbę policjanta na język baz danych, musimy sporządzić listę imion i nazwisk i uczniów i nauczycieli. Pierwszym krokiem będzie sporządzenie listy uczniów, drugim — listy nauczycieli, a końcowym etapem — połączenie obu list w jedną.

SELECT Imię,Nazwisko

FROM Nauczyciel

UNION SELECT Imię, Nazwisko

FROM Uczen;

Proszę zauważyć, że efekt zsumowania dwóch kolumn za pomocą operatora UNION jest wyraźnie inny od próby łączenia obu tabel bez podania warunku połączenia. Powyższe polecenie zwraca listę imion i nazwisk uczniów i nauczycieli, a poniższa próba skończy się obliczeniem iloczynu kartezjańskiego obu tabel:

SELECT Uczen.Imię, Uczen.Nazwisko, Nauczyciel.Imię, Nauczyciel.Nazwisko

FROM Uczen, Nauczyciel;

Ponieważ operatory INTERSEC i MINUS nie zostały zaimplementowane przez firmę Microsoft w Accessie 97, czytelnik musi we własnym zakresie sprawdzić ich działanie korzystając z innego SZBD.

Proste funkcje

Poprzez konstruktor wyrażeń użytkownik ma dostęp do wielu predefionowanych w Accessie funkcji. Funkcje te podzielone są na kategorie. Poprzez kliknięcia klawisza Pomoc do każdej z nich wywołamy dokładną, zawierająca przykład użycia instrukcję.

Rys. 20.8 Konstruktor wyrażeń wykorzystany do wyświetlenia listy funkcji

Ponieważ celem tej książki nie jest nauka obsługi programu Access, po dalsze informacje dotyczące wbudowanych funkcji odsyłamy do instrukcji programu.

Funkcje agregujące

Na osobne omówienie zasługuje grupa funkcji agregujących (obliczających wartości zbiorcze), takich jak: SUM, COUNT, AVG, MIN, MAX. W wyniku ich zastosowania otrzymamy jedną wartość wyliczoną poprzez SZBD na podstawie wielu pól. Wróćmy do pytania dyrektora o liczbę ocen niedostatecznych wystawionych na koniec semestru z matematyki. Poprzednio odpowiedzieliśmy na nie drukując listę wszystkich tych ocen. Metoda ta, skuteczna w przypadku listy kilku, czy kilkunastu ocen, okaże się niewystarczająca przy zapytaniu zwracającym kilkaset, czy kilka tysięcy danych. Zamiast samemu zliczać wiersze, wykorzystajmy do tego SZBD. Po modyfikacji instrukcja będzie wyglądała następująco:

SELECT COUNT(Ocena)

FROM Ocena

WHERE Ocena=2 AND [ID przedmiotu]='MATMA' AND [Zdobyta za]='semestr';

W rezultacie otrzymamy jedną liczbę, będącą odpowiedzią na pytanie dyrektora.

Rozważmy nieco bardziej skomplikowane zadanie. Mamy przygotować zestawienie zawierające średnią ocen klasy z każdego przedmiotu. Ponieważ oceny przechowywane są w tabeli Ocena, a nazwy przedmiotów w tabeli Lista przedmiotów, zapytanie nasze będzie musiało pobierać dane z obu tych tabel. Kolejnym krokiem będzie określenie, które dane interesują nas w danym przypadku. O ile z nazwą przedmiotu nie będziemy mieli żadnych problemów, o tyle średnią ocen musimy wyliczyć na potrzeby zapytania. Służy do tego funkcja AVG . Parametrem dla funkcji będzie lista wszystkich ocen pogrupowana według nazw przedmiotów. Rozwiązanie zadania przedstawiamy poniżej:

SELECT Avg(Ocena) AS Średnia, [Nazwa przedmiotu]

FROM [Lista przedmiotów] INNER JOIN Ocena ON [Lista przedmiotów].[ID przedmiotu] = Ocena.[ID przedmiotu]

GROUP BY [Nazwa przedmiotu];

Rys 20.9 Wynik zwracany przez kwerendę

Średnia	Nazwa przedmiotu
4	Elementy informatyki
5	Fizyka
3	Język angielski
3,34375	Matematyka

Zamiast nazw kolumn można stosować ich aliasy. Alias dla kolumny definiuje się poprzez wpisanie słowa kluczowego AS po nazwie kolumny.

Podział ten jest podziałem uproszczonym, nie uwzględniającym zagadnień związanych z ochroną i bezpieczeństwem danych.

w rzeczywistości odpowiada fizycznemu rozmieszczeniu wierszy w tabeli.

Jako testu logicznego moglibyśmy użyć wartości dowolnego innego klucza kandydującego.

Do przykładu tego wrócimy jeszcze przy okazji omawiania funkcji agregujących. Okaże się wtedy, że SZBD może wykonać za nas całą pracę związaną z prośbą dyrektora.

Średnia liczba dni przypadających na trzy miesiące.

Ponieważ więzy integralności powiązania tabeli Lista przedmiotów z tabelą Ocena nie pozwoliły by na usunięcie wiersza bez kaskadowego usuwania pól z tabeli Ocena, na potrzeby przykładu uprościmy sytuację wyłączając je w oknie Relacje SZBD Access.

Termin Połączenie naturalne opisany został w rozdziale 19.

7 Część I ♦ Podstawy obsługi systemu WhizBang (Nagłówek strony)

7 C:\My Documents\MATURA\R-20.DOC

---