3 Bazy danych SQL cz 2 wykład

Język SQL – cz.2

konspekt wykładu

1. Złączenia tabel

Złączenie „tradycyjne”:

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) 1

Jedn_adm

Rodzaj

Nazwa

ID_j_nadrzednej

Dąbrowa

Stara Wieś

Nowa Wieś

wąchocki

mazowieckie

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

Dąbrowa

wąchocki

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

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

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 minimum maksimum średnia

Mb 3 5 3.8

Nawierzchnia MIN (długosc) MAX (dlugosc) AVG (dlugosc)

Mb 3 5 3.8

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: STUDENT, STUD_AKAD, AKADEMIK

Pytanie: W jakim akademiku mieszka Kowalski

SELECT Nazwa FROM Akademiki WHERE ID_akademika = ANY (SELECT ID_akad FROM

Stud_akad WHERE Id_studenta = ANY (SELECT Id FROM Student 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

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 = TRUNC(SYSDATE,’DD.MM.YYYY’),

GwarancjaStop=TRUNC (SYSDATE,’DD.MM.YYYY’)+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: 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;

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

11. Definiowanie indeksów

SQL> Create Unique Index On Wlasciciele (nazwisko,imie, data_ur,imie_ojca); 12. Definiowanie synonimów

SQL> Create Synonym DK for 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;

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 Wlasciele 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