1 Język SQL – ciąg dalszy DML (Data Manipulation Language)Wykład S. Kozielski

2 Wstawianie wierszy INSERT INTO [()] VALUES () Przykład: wstawianie wierszy do tablicy: uczniowie (nazwisko, wzrost, waga, klasa) insert into uczniowie values (‘Malina’,183,79.5,3) insert into uczniowie (nazwisko,klasa) values (‘Jaworek’,1)

3 Wstawianie wierszy - c.d.INSERT INTO [()]

4 Modyfikowanie (aktualizacja) wierszyUPDATE SET = , [WHERE ] Przykład: update uczniowie set wzrost = wzrost + 2 where nazwisko = ‘Malina’ update uczniowie set klasa = 3 where klasa = 2

5 Usuwanie wierszy DELETE FROM [WHERE ] Przykład: delete from uczniowie where klasa = 3

6 Wyszukiwanie danych SELECT <> FROM <> WHERE <>GROUP BY <> HAVING <> ORDER BY <> UNION ...

7 Fraza SELECT – opis uproszczonySELECT [ALL|DISTINCT]{ [AS ], ... | *} ::= | . | . | | ::= | |

8 Przykłady zapytań dotyczących tablicy:Przykłady zapytań dotyczących tablicy: uczniowie (nazwisko, wzrost, waga, klasa) select * from uczniowie select nazwisko, klasa select nazwisko, waga – (wzrost - 100) select nazwisko, waga – (wzrost - 100) as ‘nadwaga’

9 select max(wzrost) from uczniowie select klasa select distinct klasaPrzykłady zapytań - c.d. select max(wzrost) from uczniowie select klasa select distinct klasa

10 Fraza FROM – wariant bez złączeńFROM [], ... ::= | | |

11 WHERE Fraza WHERE WHERE

12 Warunki filtrujące (zwykłe) ::= =, >, >=, <, <=, !=, <> [NOT] BETWEEN AND [NOT] IN ()

13 Przykłady select * from uczniowie where klasa = 4 where waga – (wzrost - 100) > 10 where wzrost beetwen 178 and 183 where klasa in (1,2)

14 [NOT] LIKE znaki zastępcze we wzorcu tekstowym: _ : zastępuje 1 znak, % : zastępuje 0, 1, 2, 3, ... znaków

15 Przykłady select * from uczniowie where nazwisko like ‘Kowalsk_’ where upper(nazwisko) like ‘KOWALSK_’ where upper(nazwisko) like ‘KOWAL%’

16 Warunki łączące Tablice bazy danych: Zespoły (nrz, nazwa, nrpk)Pracownicy (nrp, nazwisko, nrz, ) Wypłaty (nrp, nrt, kwota) Tematy (nrt, nazwa, nrpk)

17 Warunki łączące select nazwisko, kwota, nrt from pracownicy, wypłatywhere pracownicy.nrp = wypłaty.nr select p.nazwisko, w.kwota, w.nrt, p.nrp from pracownicy p, wypłaty w where p.nrp = w.nrp

18 Złączenie tabel Pracownicy Wypłaty nrp nrt kwota 2 300 3 150 1 200 nrpnazwisko adres nrz 1 Lipowski Ruda 2 Grabski Zabrze 3 Jaworek Gliwice

19 Pracownicy  Wypłaty nrp nazwisko adres nrz nrt kwota 2 GrabskiZabrze 1 300 3 Jaworek Gliwice 150 Lipowski Ruda 200

20 Pracownicy  Wypłaty  Tematynrp nazwisko adres nrz nrt kwota nazwa kier. 2 Grabski Zabrze 1 300 Pr. przetwor. 3 Jaworek Gliwice 150 Pr. reaktora Lipowski Ruda Pr. zasilacza 200

21 select nazwisko, kwota, nrtfrom pracownicy p, wypłaty w where p.nrp = w.nrp and nrz = 4 and kwota > 2000

22 select distinct z.nazwafrom zespoły z, pracownicy p, wypłaty w, tematy t where z.nrz = p.nrz and p.nrp = w.nrp and w.nrt = t.nrt and t.nazwa = ‘Projekt sterownika’

23 select p2.nazwisko from pracownicy p1, pracownicy p2 where p1.nazwisko = ‘Bukowy’ and p1.nrz = p2.nrz and p2.nazwisko <> ‘Bukowy’

24 select p2.nazwisko from pracownicy p1, wypłaty w, tematy t, pracownicy p2 where p1.nazwisko = ‘Grabski’ and p1.nrp = t.nrpk and t.nrt = w.nrt and w.nrp = p2.nrp

25 Złączenia zewnętrzne select nazwisko, kwota, nrtfrom pracownicy p, wypłaty w where p.nrp = w.nrp

26 Złączenie naturalne r (A, B, C) s (C, D) q (A, B, C, D)————  ——— = —————   a1 b1 c c1 d a1 b1 c1 d1 a2 b2 c c5 d a4 b4 c1 d1 a3 b3 c3 a4 b4 c1

27 Złączenie zewnętrzne r (A, B, C) s (C, D) (+) q (A, B, C, D)————  ———— = —————   a1 b1 c c1 d a1 b1 c1 d1 a2 b2 c c5 d a4 b4 c1 d1 a3 b3 c a2 b2 c2 a4 b4 c a3 b3 c3

28 Złączenia zewnętrzne select nazwisko, kwota, nrtfrom pracownicy p, wypłaty w where p.nrp = w.nrp (+)

29 Zapis złączeń w standardzie SQL-2 (SQL-92)SELECT <> FROM ::= [] JOIN [] JOIN USING (, ...) [] JOIN ON ::= INNER|{LEFT|RIGHT|FULL}[OUTER]

30 select nazwisko, kwota, nrtfrom pracownicy join wypłaty lub from pracownicy join wypłaty using (nrp) from pracownicy p join wypłaty w on p.nrp = w.nrp

31 Złączenie zewnętrzne Zapis klasyczny select nazwisko, kwota, nrtfrom pracownicy p, wypłaty w where p.nrp = w.nrp (+) Zapis w SQL-2 from pracownicy p left outer join wypłaty w on p.nrp = w.nrp

32 Warunki filtrujące z pytaniami zagnieżdżonymiSELECT <> FROM <> WHERE ( SELECT <> WHERE < >)

33 ::=1) 2) {ANY|ALL} 3) [NOT] IN 4) [NOT] EXISTS

34 Baza danych: Zespoły (nrz, nazwa, nrpk)Pracownicy (nrp, nazwisko, nrz, premia) Wypłaty (nrp, nrt, kwota) Tematy (nrt, nazwa, nrpk)

35 ad 1) select nazwisko from pracownicy where nrp = (select nrpk from zespoły where nazwa = ‘Zespół Wdrożeń’)

36 ad 1) select nazwisko from pracownicy where nrz = 3 and premia > (select premia where nazwisko = ‘Jaworek’)

37 ad 2) {ANY|ALL}select nazwa from tematy where nrt = any (select nrt from wypłaty)

38 ANY select nazwisko from pracownicy where nrp = any (select nrpfrom wypłaty where kwota > 2000)

39 ALL select nazwisko, wzrost from uczniowiewhere klasa = 1 and wzrost > all (select wzrost where klasa = 3)

40 select nazwisko from pracownicy p where 2000 < all (select kwota from wypłaty w where w.nrp = p.nrp)

41 ad 3) [NOT] INselect nazwa from tematy where nrt in (select nrt from wypłaty)

42 select nazwa from zespoły where nrz in (select nrz from pracownicy where nrp in (select nrp from wypłaty where nrt in (select nrt from tematy where nazwa = ‘Projekt sterownika’)))

43 ad 4) [NOT] EXISTS select nazwa from tematy t where exists (select *from wypłaty w where w.nrt = t.nrt)

44 select nazwisko from pracownicy p where not exists (select * from wypłaty w where w.nrp = p.nrp)

45 select nazwisko from pracownicy p where not exists (select * from tematy t from wypłaty w where w.nrt = t.nrt and w.nrp = p.nrp))

46 Funkcje agregujące SUM () AVG ()MAX () MIN () COUNT () ::= [ALL|DISTINCT] oraz COUNT(*)

47 select count(*) from pracownicy select sum(kwota), avg(kwota), max(kwota) from wypłaty

48 select sum(kwota), avg(kwota), max(kwota)from wypłaty where nrt = 3 select sum(kwota), max(kwota), count(p.nrp) from pracownicy p, wypłaty w where p.nrp = w.nrp and nrz = 4

49 select max(waga - (wzost - 100))from uczniowie select count(nrp), count(distinct nrp), count(distinct nrt) from wypłaty

50 select nazwisko, wzrostfrom uczniowie where wzrost = (select max(wzrost) from uczniowie)

51 select klasa, nazwisko, wzrostfrom uczniowie u1 where wzrost = (select max(wzrost) from uczniowie u2 where u2.klasa = u1.klasa)

52 select nrp, nrt, kwota from wypłaty w1 where kwota = (select max(kwota) from wypłaty w2 where w2.nrt = w1.nrt)

53 Grupowanie – fraza GROUP BYselect nrz, count(*) from pracownicy group by nrz

54 select nrt, sum(kwota), max(kwota), count(*) from wypłaty group by nrt

55 select nrz, sum(kwota) from pracownicy p, wypłaty w where p.nrp = w.nrp group by nrz

56 select nazwisko, sum(kwota)from pracownicy p, wypłaty w where p.nrp = w.nrp group by nazwisko, p.nrp

57 SELECT ,FROM <> WHERE <> GROUP BY 

58 Filtrowanie grup – fraza HAVINGHAVING select nrz, count(*) from pracownicy group by nrz having count(*) > 20 or count(*) < 5

59 select nazwisko, p.nrp, sum(kwota)from pracownicy p, wypłaty w where p.nrp = w.nrp group by nazwisko, p.nrp having sum(kwota) > 10000

60 select nazwisko, p.nrp, sum(kwota)from pracownicy p, wypłaty w where p.nrp = w.nrp group by nazwisko, p.nrp having sum(kwota) > (select sum(kwota) from pracownicy pp, wypłaty ww where pp.nrp = ww.nrp and nazwisko = ‘Jaworek’)

61 Konstruktor tablicy w frazie FROMSELECT <> FROM select max(suma) from (select sum(kwota) as suma from wypłaty group by nrp)

62 Konstruktor tablicy na liście SELECTselect nrp, nazwisko, (select sum(kwota) from wypłaty w where w.nrp = p.nrp) as suma from pracownicy p

63 Porządkowanie wyników wyszukiwania – fraza ORDER BYORDER BY { |} [ASC|DESC], ... select * from pracownicy order by nrz, nazwisko

64 select nazwisko, waga–(wzrost-100), klasafrom uczniowie order by klasa, waga–(wzrost–100) desc lub order by 3,2 desc

65 Fraza UNION (i frazy pokrewne: INTERSECT, MINUS, ...)SELECT FROM <> . . . UNION [ALL] SELECT

66 select nrpk from zespoły union from tematyFraza UNION - przykład select nrpk from zespoły union from tematy

67 select nrpk from zespoły union all from tematyFraza UNION - przykład select nrpk from zespoły union all from tematy

68 Fraza INTERSECT - przykładselect nrpk from zespoły intersect from tematy

69 select nrpk from zespoły minus from tematyFraza MINUS - przykład select nrpk from zespoły minus from tematy