Laboratorium Bazy Danych 1 Język SQL
Politechnika Śląska
Instytut Informatyki
Język SQL
instrukcja laboratoryjna
przygotowali:
mgr inż. Paweł Kasprowski
(Kasprowski@zti.iinf.polsl.gliwice.pl)
mgr inż. Bożena Małysiak
(bozena@ivp.iinf.polsl.gliwice.pl)
Język SQL (Structured Query Language) jest najbardziej znanym językiem zapytań, zaimplementowanym w praktycznie wszystkich istniejących na rynku systemach relacyjnych baz danych.
SQL jest używany jako samodzielny język służący do interakcyjnych zapytań, tworzenia i aktualizacji relacyjnej bazy danych (i w ten sposób będzie wykorzystywany podczas ćwiczeń laboratoryjnych). Może być również zanurzany (ang. embedded) w klasyczne języki programowania. W językach 4GL (np. INFORMIX 4GL) jest zintegrowany ze środowiskiem 4GL.
Instrukcje języka SQL podzielić można na kilka typów:
- instrukcje wyszukiwania danych - SELECT
- instrukcje tworzenia bazy (Data Description Language) – np.: CREATE TABLE
- instrukcje modyfikacji danych (Data Modification Language) – np.: UPDATE
- instrukcje do określania praw dostępu i więzów integralności (Data Control Language) – np.: GRANT
Podczas laboratorium SQL1 i SQL2 wykorzystywać się będzie głównie instrukcję wyszukiwania SELECT.
Instrukcja wyszukiwania SELECT służy do generowania zapytań do bazy danych. Po jej wykonaniu powstaje tablica wynikowa zawierająca żądane atrybuty pobrane z wierszy spełniających podane warunki. Jej najprostsza forma to:
SELECT <atrybuty>
FROM <tabela>
WHERE <warunek>
Przykładowo zapytanie:
SELECT Nazwisko FROM Pracownicy
powoduje pobranie wartości atrybutu Nazwisko z wszystkich rekordów tablicy Pracownicy. Aby uzyskać wszystkie atrybuty tablicy w miejsce listy atrybutów użyć należy znaku ‘*’:
SELECT * FROM Pracownicy
Powyższe zapytanie zwraca jako wynik całą tablicę Pracownicy.
Aby pobrać tylko określone rekordy użyć należy frazy WHERE:
SELECT * FROM Pracownicy WHERE NumerPrac>10
Powyższe zapytanie pobiera z tablicy tylko rekordy spełniające podany warunek.
Przy porównywaniu wartości tekstowych z podanym wzorcem można we wzorcu używać znaków specjalnych ‘_’ – odpowiednik jednego dowolnego znaku oraz ‘%’ odpowiednik dowolnego ciągu znaków. W niektórych systemach przyjmuje się symbolikę pochodzącą z systemu DOS, odpowiednio ‘?’ i ‘*’. Do porównań wartości tekstowych służy fraza LIKE.
SELECT * FROM Pracownicy WHERE Nazwisko LIKE „Kowal%”
Inne operatory używane po frazie WHERE to na przykład operator IN (zbiór wartości)
SELECT * FROM Pracownicy WHERE NumerPrac IN (100,101,200)
lub operator BETWEEN x AND y
SELECT * FROM Pracownicy WHERE NumerPrac BETWEEN 100 AND 200
Do sortowania danych używa się frazy ORDER BY. Występuje ona zawsze na końcu zapytania:
SELECT * FROM Pracownicy WHERE NumerPrac > 10
ORDER BY Nazwisko
Zapytanie powyższe zwróci tablicę wartości posortowaną alfabetycznie według nazwisk. Można sortować tablicę wartości według więcej niż jednego atrybutu
ORDER BY NumerZesp, Nazwisko
W tym przypadku tablica posortowana zostanie według numerów zespołów a w ramach jednego zespołu według nazwisk. Do sortowania w odwrotnej kolejności służy znacznik DESC.
ORDER BY NumerPrac DESC
W zapytaniu dane pobierać można z więcej niż jednej tablicy jednocześnie. Należy wtedy zdefiniować warunek łączący te tabele.
SELECT Nazwisko, NazwaZesp FROM Pracownicy P, Zespoły Z
WHERE P.NumerZesp=Z.NumerZesp
Powyższe zapytanie zwróci listę nazwisk pracowników i nazw zespołów w których pracują. Zapytanie tworzy najpierw iloczyn kartezjański dwóch tablic według podanego warunku a następnie dokonuje odpowiednich selekcji i projekcji. Ponieważ pole NumerZesp występuje w obydwu tablicach, dla każdego wystąpienia w zapytaniu należy określić z której tablicy ma być pobrane. W przykładzie skorzystano z możliwości tworzenia tzw. aliasów – identyfikatorów tabel. Zapis ‘Pracownik P’ powoduje, że tablica Pracownik jest w dalszej części zapytania widziana pod identyfikatorem ‘P’.
Użycie aliasów pozwala na otwarcie więcej niż jednej instancji tej samej tablicy. Przykładowo, aby znaleźć pracowników należących do zespołów o numerze zespołu większym niż pracownik o numerze pracowniczym 5 można zastosować zapytanie:
SELECT P.NumerPrac, P.Nazwisko
FROM Pracownicy P, Pracownicy P1
WHERE P1.NumerPrac=5 AND P.NumerZesp>P1.NumerZesp
Po frazie SELECT można w miejsce atrybutów użyć tzw. funkcji agregujących. Są to funkcje SUM(), AVG(), COUNT(), MAX() i MIN().
SELECT SUM(Pensja) FROM Pracownicy
Powyższe zapytanie zwróci jeden rekord z jednym polem zawierającym sumę zawartości pola Pensja we wszystkich rekordach tabeli Pracownicy.
SELECT COUNT(*) FROM Pracownicy
Powyższe zapytanie zwróci ilość rekordów w tabeli Pracownicy.
Aby w zapytaniu uzyskać jedynie wartości unikalne danego atrybutu lub grupy atrybutów należy użyć frazy DISTINCT. Frazę tę użyć można na dwa sposoby. Pierwszy sposób to umieszczenie jej bezpośrednio za słowem SELECT:
SELECT DISTINCT Nazwisko FROM Pracownicy
Zapytanie powyższe zwróci zbiór nazwisk pracowników bez powtórzeń dla tych samych nazwisk.
SELECT DISTINCT Imię, Nazwisko FROM Pracownicy
Zapytanie powyższe zwróci zbiór unikalnych par imię - nazwisko. Dla tego samego nazwiska i różnych imion pojawią się dwa rekordy, np.: Jan Kowalski i Piotr Kowalski.
Inne zastosowanie frazy DISTINCT to użycie jej wewnątrz funkcji agregującej:
SELECT SUM(DISTINCT Pensja) FROM Pracownicy
Zapytanie powyższe zwróci sumę różnych pensji. Na przykład dla zbioru 100,200,300,200,300 zapytanie to zwróci wartość 600 podczas, gdy zapytanie bez frazy DISTINCT zwróciłoby wartość 1100.
Za pomocą instrukcji SELECT można także grupować dane. Aby uzyskać informacje na temat sum dochodów pracowników w poszczególnych zespołach można zmodyfikować poprzednie zapytanie dodając frazę GROUP BY:
SELECT SUM(Pensja) FROM Pracownicy GROUP BY NumerZesp
Powyższe zapytanie zwróci jeden rekord dla każdej wartości pola NumerZesp. W rekordzie tym będzie suma pola Pensja dla wszystkich rekordów tablicy o danej zawartości NumerZesp.
SELECT P.NumerZesp, NazwaZesp, Sum(Pensja)
FROM Pracownicy P, Zespoły Z
WHERE P.NumerZesp=Z.NumerZesp AND NazwaZesp<>”Piece”
GROUP BY P.NumerZesp, NazwaZesp
Powyższe zapytanie zwróci tablicę z trzema kolumnami: numer zespołu, nazwa zespołu, suma zarobków w zespole. Zauważyć należy, że wszystkie atrybuty występujące bezpośrednio po frazie SELECT na których nie dokonuje się agregacji muszą znaleźć się we frazie GROUP BY. W tym przykładzie zasada ta dotyczy pola NazwaZesp.
W języku SQL istnieje możliwość nałożenia warunku na całą grupę za pomocą frazy HAVING. Przykładowo zapytanie:
SELECT P.NumerZesp, Sum(Pensja)
FROM Pracownicy P
GROUP BY P.NumerZesp
HAVING P.NumerZesp>10
zwróci sumy zarobków tylko w zespołach, których numer jest większy niż 10.
Oprócz zwykłych warunków w warunkach frazy HAVING można stosować także funkcje agregujące.
HAVING Sum(P.Pensja)>1000
Zapytanie zwróci tylko te zespoły, w których suma pensji jest większa od 1000.
HAVING count(*)>5
Zapytanie zwróci tylko te zespoły, dla których liczba rekordów (liczba pracowników) jest większa od 5.
Nazwy kolumn zapytania nie muszą być identyczne z nazwami atrybutów bazy. Można zdefiniować własne nazwy za pomocą frazy AS.
SELECT P.NumerZesp AS Zespół, Sum(Pensja) as Zarobki
Instrukcja SELECT może być zagnieżdżona w obrębie innej instrukcji SELECT np.
SELECT Nazwisko
FROM Pracownicy
WHERE NumerZesp IN
(SELECT NumerZesp
FROM Zespoły
WHERE Kierownik= „MISIURA”);
Zapytanie zagnieżdżone ujęte w nawiasy może być traktowane jako zbiór wartości. W takim przypadku powinno zawracać tylko jedną kolumnę danych. Odwoływać się do niego można przez porównanie z którymś z atrybutów zapytania zewnętrznego:
atrybut IN (zapytanie) – wartość znajduje się w tablicy wyników zapytania.
atrybut > ALL (zapytanie) – wartość jest większa od wszystkich elementów tablicy wyników zapytania.
atrybut > SOME|ANY (zapytanie) – wartość jest większa od przynajmniej jednego elementu tablicy wyników zapytania.
Zamiast operatora ‘>’ można użyć innych operatorów porównania.
Innym sposobem użycia zagnieżdżonej instrukcji SELECT jest zastosowanie jej we frazie EXISTS (zapytanie) lub NOT EXISTS (zapytanie). Frazy takie użyte bezpośrednio jako warunki są prawdziwe, jeśli odpowiednio istnieje lub nie istnieje chociaż jeden rekord w tabeli wynikowej zapytania zagnieżdżonego.
WHERE EXISTS
FROM Zespoły Z
WHERE Z.NumerZesp>P.NumerZesp)
Zapytanie to zwraca nazwiska pracowników, dla których istnieją zespoły o numerach większych niż numer zespołu, do którego należą.
Zwykłe złączenie tablic przez podanie warunku łączącego jest nie zawsze wystarczające. Dla przykładu zapytanie
SELECT P.NumerPrac, Nazwisko, Imię, NazwaZwiązku
FROM Pracownicy P, Związki Z
WHERE P.NumerPrac=Z.NumerPrac
da w wyniku numery pracownicze tylko pracowników należących do związku. Aby uzyskać listę wszystkich pracowników z polem NazwaZwiązku wypełnionym w zależności od tego, czy pracownik do jakiegoś należy, należy zastosować złączenie zewnętrzne tablic. Jest kilka „standardów” tworzenia złączeń zewnętrznych. Na laboratorium używa się serwera SQL Base firmy Centura i w tym przypadku (podobnie jest dla Oracle’a) złączenie zewnętrzne definiuje się bezpośrednio przy podaniu warunku złączenia:
WHERE P.NumerPrac=Z.NumerPrac(+)
Zapis t1.pole=t2.pole(+) oznacza, że do tablicy wynikowej dołączane będą tylko rekordy z tablicy t2 spełniające podany warunek. W pozostałych miejscach zamiast pól z t2 znajdą się wartości NULL.
Inną metodą definiowania złączeń zewnętrznych jest na przykład stosowany w systemie Informix zapis z użyciem frazy OUTER:
FROM Pracownicy P, OUTER Związki Z
Zapytanie przekształcić możemy w tablicę wirtualną – tak zwaną perspektywę lub widok (ang. VIEW). Perspektywę tworzy się za pomocą frazy:
CREATE VIEW Nazwa [(atrybuty)] AS (zapytanie)
Od tego momentu identyfikator Nazwa będzie traktowany w kolejnych instrukcjach języka SQL jak zwykła tablica. Przykładowo instrukcja:
CREATE VIEW DanePers (Nazwisko, Imię, Zespół) AS
(SELECT Nazwisko, Imię, NazwaZesp
...
jacek_040