Język SQL - cz.2
konspekt wykładu
Złączenia tabel
Złączenie „klasyczne”:
SELECT Nr_bud, L_kondygnacji, Nr_lokalu, Powierzchnia_lokalu
FROM Budynki, Lokale
WHERE Budynki.ID = Lokale.ID_budynku AND L_kondygnacji > 4;
Wykorzystanie kwalifikatorów i aliasów:
SELECT B.Nr_bud, B.L_kondygnacji, L.Nr, L.Powierzchnia_lokalu
FROM Budynki B, Lokale L
WHERE B.ID = L.ID_budynku AND L_kondygnacji > 4;
Złączenie w SQL-92:
SELECT Nr_bud, L_kondygnacji, Nr_lokalu, Powierzchnia_lokalu
FROM Budynki JOIN Lokale
WHERE L_kondygnacji > 4;
SELECT Nr_bud, L_kondygnacji, Nr_lokalu, Powierzchnia_lokalu
FROM Budynki JOIN Lokale USING ID_bud
WHERE L_kondygnacji > 4;
SELECT Nr_bud, L_kondygnacji, Nr_lokalu, Powierzchnia_lokalu
FROM Budynki JOIN Lokale ON Budynki.ID = Lokale.ID_bud
WHERE L_kondygnacji > 4;
SELECT Nr, Powierzchnia, Nazwisko, Imie
FROM Dzialki JOIN Dzial_Wlasc_int JOIN Wlasciciele
WHERE nazwisko='Kowalski';
Złączenie tabeli samej ze sobą.
W operacji złączenia języka SQL ta sama tabela może występować wielokrotnie w klauzuli FROM.
Tabela może więc być łączona sama ze sobą. Konieczne jest użycie aliasów, które pozwalają na rozróżnienie pomiędzy odwołaniem do tabeli jako źródła informacji o jednostce podrzędnej, a później nadrzędnej. Najczęściej wykorzystuje się tego typu złączenie w przypadku tabeli w której zapisana jest zależność hierarchiczna np. w przypadku tabeli z danymi o jednostkach administracyjnych
Przykładowa struktura tabeli Jedn_adm (jednostki administracyjne)
Jedn_adm
ID |
Rodzaj |
Nazwa |
ID_j_nadrzednej |
1 |
g |
Dąbrowa |
41 |
2 |
g |
Stara Wieś |
41 |
3 |
g |
Nowa Wieś |
47 |
41 |
p |
Wąchocki |
112 |
112 |
w |
Mazowieckie |
0 |
SELECT A.ID, A.Rodzaj, A.Nazwa, B.Nazwa
FROM jedn_adm A, jedn_adm B
WHERE A.ID = B.Id_j_nadrzednej;
Wynik zapytania:
A.ID |
A.Rodzaj |
A.Nazwa |
A.ID_j_nadrzednej |
B.ID |
B.Rodzaj |
B.Nazwa |
.ID_j_nadrzednej |
1 |
g |
Dąbrowa |
41 |
41 |
p |
Wąchocki |
112 |
Zapytania zewnętrzne:
W prostym złączeniu tabel otrzymujemy na wyjściu tylko odpowiadające sobie wiersze z łączonych tabel.
Jeżeli chcemy w odpowiedzi uwzględnić również te wiersze, które nie mają odpowiadających wierszy w pozostałych tabelach złączenia, należy użyć operatora złączenia zewnętrznego „(+)”
Symbol „(+)” stawia się po nazwie kolumny z tej strony operatora relacyjnego, z której może wystąpić brak informacji
Operator „(+)” może stać tylko przy jednej tabeli w ramach jednego zapytania
SELECT dzialki.nr, baseny.powierzchnia FROM dzialki, baseny
WHERE dzialki.nr = baseny.id_dzialki (+);
Składnia SQL-92:
Budynki LEFT OUTER JOIN Lokale
Budynki RIGHT OUTER JOIN Lokale
Budynki FULL OUTER JOIN Lokale
Wybrane funkcje systemowe (z systemu Oracle)
funkcje agregujące: SUM, MAX, MIN, AVG, COUNT, STDDEV, VARIANCE
funkcje znakowe: CHR, ASCII, LOWER, UPPER, INITCAP, LPAD, RPAD, LTRIM, RTRIM, REPLACE, SUBSTR, INSTR, LENGHT
funkcje związane z datą i czasem: SYSDATE, ROUND, TRUNC
funkcje matematyczne: ABS, EXP, LN, LOG, POWER, ROUND, TRUNC, MOD, SIN, COS, TAN, SQRT
funkcje do konwersji typów danych: TO_CHAR, TO_DATE, TO_NUMBER, NVL
Funkcje grupowe
Umożliwiają obliczenie liczności oraz parametrów statystycznych (minimum, maksimum, wartość średnia, odchylenie standardowe, wariancja) grup danych złożonych z wielu wierszy
Do określenia liczby wierszy służy funkcja grupowa COUNT.
Jeżeli w zapytaniu nie ma klauzuli GROUP BY zliczanie dotyczy całej tabeli jako jednej grupy
Zliczenie wszystkich wierszy w tabeli:
SELECT Count (*) FROM Drogi;
Obliczenie liczby budynków posiadających nazwę własną (np. „Pałac Kultury i Nauki”, „Spodek”)
SELECT Count(nazwa) FROM Budynki
wyszukiwanie wszystkich wierszy, w których w kolumnie "nazwa" występuje wartość różna od NULL
Aby ignorować duplikaty wierszy podczas zliczania należy dodać klauzulę DISTINCT
SELECT Count(Distinct Nazwa) FROM Ulice;
Zliczenie liczby odcinków dróg o tym samym rodzaju nawierzchni
SELECT Nawierzchnia, Count(*) FROM Odcinki_jezdni GROUP BY Nawierzchnia;
Mb, 234
Kk, 23
Bt, 45
Gr, 567
Obliczenie parametrów statystycznych odcinków jezdni dla poszczególnych rodzajów nawierzchni:
SELECT nawierzchnia, MIN (długosc) minimum,
MAX (dlugosc) maksimum,
AVG (dlugosc) średnia
FROM Odcinki_jezdni GROUP BY nawierzchnia;
Nawierzchnia MIN (długosc) MAX (dlugosc) AVG (dlugosc)
Mb 3 5 3.8
Kk 0.5 2 1.2
SELECT nawierzchnia, MIN (długosc),
MAX (dlugosc),
AVG (dlugosc)
FROM Odcinki_jezdni GROUP BY nawierzchnia;
Nawierzchnia minimum maksimum średnia
Mb 3 5 3.8
Kk 0.5 2 1.2
Podzapytania
W języku SQL można oprócz prostych zapytań formułować zapytania zagnieżdżone. Podzapytania nie przekazują wyniku na ekran, ale wynik przekazywany jest do zapytania wyższego poziomu.
Tabele: DZIALKI, DZIAL_WLAS, WLASICIELE
Pytanie: Znajdź numery działek należących do Kowalskiego
SELECT nr FROM Dzialki WHERE Id = ANY (SELECT ID_dzialki FROM Dzial_Wlas WHERE Id_wlasciciela = ANY (SELECT Id FROM Wlasciciele WHERE nazwisko = `Kowalski'));
Zapytania zagnieżdżone w warunkach zawierających operatory porównania są jednowierszowe.
Jeżeli podzapytanie może wyszukać więcej niż jeden wiersz, należy użyć innych operatorów:
= ALL (dla każdego wiersza)
= ANY (dla któregokolwiek wiersza) - to samo co IN
.....WHERE id_teryt IN (SELECT nr FROM gminy WHERE nazwa IN (`Koluszki', `Wąchock'))
Wyszukanie gmin leżących w powiatach o powierzchni większej od 1000 km2
Select * from gminy where id_powiatu=ANY (SELECT id FROM powiaty WHERE powierzchnia > 1000);
WADY:
Przy wielu poziomach zagnieżdżenia mała czytelność
Możliwość uzyskania w odpowiedzi tylko kolumn z tabeli występującej w zapytaniu najbardziej zewnętrznym
Stosowanie operatorów teoriomnogościowych
W SQL oprócz operatorów projekcji, selekcji i złączenia mogą być używane klasyczne operatory teoriomnogościowe - suma (UNION) , przecięcia (INTERSECT) i różnica zbiorów (MINUS)
wyniki zapytań mogą być traktowane jako zbiory i mogą być argumentami operacji teoriomnogościowych
aby 2 zapytania mogły być połączone muszą mieć identyczną strukturę (istotna jest liczba, kolejność i typy kolumn, nieistotne są nazwy)
klauzula ORDER BY może wystąpić tylko raz, po ostatnim zapytaniu; kryteria sortowania mogą być wyłącznie numerami kolumn (zgodnie z pozycją kolumny)
Przykład:
Wypisanie danych o gminach i powiatach na jednej liście posortowanej po powierzchni:
SELECT Nazwa, Powierzchnia FROM Gminy
UNION
SELECT Nazwa_pow, Area FROM Powiaty
ORDER BY 2;
Wypisanie nazw miast o statusie gminy:
SELECT Nazwa, Powierzchnia FROM Gminy
INTERSECT
SELECT Nazwa, Powierzchnia FROM Miasta;
Wstawianie danych
INSERT Into Odcinki_jezdni (ID, Szerokosc, Stan, Kategoria_ruchu, Data_wprowadzenia_info)
VALUES (1, 5.5 , null, '1' , sysdate() );
Aktualizacja danych
UPDATE Szlaki_drogowe
SET KolejnoscOdsniezania = 1
WHERE Lenght(Nr)<3;
UPDATE Budynki
SET Uwagi = 'Brak danych o wysokości'
WHERE Wysokosc IS NULL;
UPDATE Odcinki_rzek
SET Klasa_czystosci = Klasa_czystosci+1
WHERE id=245;
UPDATE Sprzet
SET GwarancjaStart = Sysdate()
GwarancjaStop=Sysdate()+364;
Usuwanie danych
DELETE FROM Odc_drogi
WHERE Dlugosc IS NULL;
Modyfikacja struktury danych
Struktury danych mogą być łatwo powiększane. Zmniejszanie (np. usunięcie kolumny) jest zwykle niedopuszczalne lub ograniczone pewnymi warunkami
Przykład z systemu Oracle:
SQL> ALTER TABLE powiaty MODIFY nr_stat number(4);
SQL> ALTER TABLE powiaty MODIFY nr_stat number(2);
ERROR at line 1
ORA-01440: column to be modified must be empty to decrease precision or scale
Zmiana typu kolumny możliwa jest tylko jeżeli w tabeli nie ma danych:
SQL> ALTER TABLE Powiaty MODIFY nr_stat VARCHAR2(4);
Dodanie nowej kolumny:
SQL> ALTER TABLE Powiaty ADD powierzchnia number(10);
Definiowanie perspektyw
SQL> Create View DzialkiBudyn (Nr_działki, Rodzaj_budynku, Liczba_pięter) As
SELECT dzialki.nr, budynki.rodzaj, budynki.liczba_kondygnacji
FROM dzialki, dzialki_bud_int, budynki
WHERE dzialki.ID = dzialki_bud_int.ID_dzialki AND
dzialki_bud_int.ID_bud= budynki.ID;
Select * From DzialkiBudyn;
12/1, m, 2
134, m, 1
12/2, g, 1
12/2, m, 2
231, p, 3
Typy kolumn w perspektywie dziedziczone są po elementach listy SELECT
Należy unikać używania w tworzeniu perspektywy po klauzuli SELECT znaku (*)
Dodanie klauzuli WITH CHECK OPTION powoduje nałożenie ograniczenia na operacje modyfikacji danych; nie pozwoli na wprowadzenie danych które nie będą widoczne w perspektywie
SQL> Create View WysokieBudynki As
SELECT ID, Nr_ew, Funkcja, Liczba_kondyg, Wysokosc FROM Budynki
WHERE Liczba_kondyg>4 WITH CHECK OPTION;
Definiowanie indeksów
SQL> Create Unique Index On Wlasciciele (nazwisko,imie, data_ur,imie_ojca);
SQL> Create Index On Wlasciciele (nazwisko,imie);
Definiowanie synonimów
SQL> Create Synonym DK for DKowalski.DaneOsobiste;
SQL> SELECT * FROM DKowalski.DaneOsobiste;
SQL> SELECT * FROM DK;
Definiowanie sekwencji
Sekwencje umożliwiają pobieranie unikalnych liczb całkowitych, przez różnych użytkowników w sposób współbieżny
SQL> Create Sequence AutoNr1;
SQL> Create Sequence AutoNr2
increment By 10
minvalue 100
maxvalue 10000
cycle;
Wprowadzanie danych z użyciem sekwencji:
SQL> INSERT Into Pracownicy (Identyfikator, Imie, Nazwisko)
Values (AutoNr1.nextval, `Piotr', `Krawczyk');
SQL> Alter Sequence AutoNr2 Increment By 1
Operatory dotyczące sekwencji (nextval, currval) nie mogą być użyte w następujących miejscach:
we frazie WHERE
w zapytaniach z ORDER BY, CONNECT BY, GROUP BY, DISTINCT
w perspektywach
definiowanie, modyfikacja więzów integralności i ograniczeń
Ustanawianie więzów integralności
Integralność kolumny i wiersza
SQL> ALTER TABLE Szlaki_drogow MODIFY Nr NOT NULL;
SQL> ALTER TABLE Jezdnie MODIFY Kategoria_ruchu DEFAULT `A';
SQL> ALTER TABLE Jezdnie ADD CONSTRAINT jezdnie_chk CHECK (klasa_techniczna BETWEEN 1 AND 6);
SQL> ALTER TABLE Jezdnie ADD CONSTRAINT jezdnie_chk CHECK (klasa_techniczna>=1 AND klasa_techniczna<=6);
Klucz główny:
SQL>ALTER TABLE Gminy ADD CONSTRAINT gminy_pk PRIMARY KEY (Nr_TERYT);
Klucz unikalny:
SQL> ALTER TABLE Własciciele ADD CONSTRAINT wlas_uk UNIQUE (imie, nazwisko);
Klucz obcy:
SQL> ALTER TABLE Gminy ADD CONSTRAINT gminy_pow_fk FOREIGN KEY (Nr_TERYT_pow) REFERENCES Powiaty (Nr_TERYT) ON DELETE CASCADE;
7