Język SQL cz.2 konspekt wykładu 1. 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 zródła informacji o jednostce podrzędnej, a pózniej 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) 1 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 2. 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 2 3. 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 3 4. 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: Znajdz 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 5. 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 4 INTERSECT SELECT Nazwa, Powierzchnia FROM Miasta; 6. Wstawianie danych INSERT Into Odcinki_jezdni (ID, Szerokosc, Stan, Kategoria_ruchu, Data_wprowadzenia_info) VALUES (1, 5.5 , null, 1 , sysdate() ); 7. 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; 8. Usuwanie danych DELETE FROM Odc_drogi WHERE Dlugosc IS NULL; 9. 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: 5 SQL> ALTER TABLE Powiaty MODIFY nr_stat VARCHAR2(4); Dodanie nowej kolumny: SQL> ALTER TABLE Powiaty ADD powierzchnia number(10); 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; 11. Definiowanie indeksów SQL> Create Unique Index On Wlasciciele (nazwisko,imie, data_ur,imie_ojca); SQL> Create Index On Wlasciciele (nazwisko,imie); 12. Definiowanie synonimów SQL> Create Synonym DK for DKowalski.DaneOsobiste; SQL> SELECT * FROM DKowalski.DaneOsobiste; SQL> SELECT * FROM DK; 13. 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; 6 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ń 14. 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