J

ę

zyk SQL – ci

ą

g dalszy

DML (Data Manipulation Language)

Wykład

S. Kozielski

Wstawianie wierszy

INSERT INTO <tablica> [(<lista kolumn>)] VALUES

(<lista warto

ś

ci>)

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)

insert into uczniowie values (‘Jaworek’,NULL, NULL, 1)

Wstawianie wierszy - c.d.

INSERT INTO <tablica> [(<lista kolumn>)]

<instrukcja SELECT>

Modyfikowanie (aktualizacja) wierszy

UPDATE <tablica> SET <kolumna> = <wyra

ż

enie>,

... [WHERE <warunek>]

Przyk

ł

ad:

update uczniowie set wzrost = wzrost + 2 where

nazwisko = ‘Malina’

update uczniowie set klasa = 3 where klasa = 2

Usuwanie wierszy

DELETE FROM <tablica> [WHERE <warunek>]

Przyk

ł

ad

:

delete from uczniowie where klasa = 3

Wyszukiwanie danych

SELECT <>

FROM <>

WHERE <>

GROUP BY <>

HAVING <>

ORDER BY <>

UNION ...

Fraza SELECT – opis uproszczony

SELECT [ALL|DISTINCT]{<wyra

ż

enie> [AS <nazwa>], ... | *}

<wyra

ż

enie>::=

<kolumna> |

<alias>.<kolumna> |

<kwalifikator>.<kolumna> |

<wyra

ż

enie okre

ś

lonego typu> |

<funkcja agreguj

ą

ca>

<kwalifikator>::= <tablica> | <perspektywa> | <synonim>

Przyk

ł

ady zapyta

ń

dotycz

ą

cych tablicy:

uczniowie (nazwisko, wzrost, waga, klasa)

select *

from uczniowie

select nazwisko, klasa

from uczniowie

select nazwisko, waga – (wzrost - 100)

from uczniowie

select nazwisko, waga – (wzrost - 100) as ‘nadwaga’

from uczniowie

Przyk

ł

ady zapyta

ń

- c.d.

select max(wzrost)

from uczniowie

select klasa

from uczniowie

select distinct klasa

from uczniowie

Fraza FROM – wariant bez zł

ą

cze

ń

FROM <element> [<alias>], ...

<element>::= <tablica>|<perspektywa> |

<synonim>|<konstruktor tablicy>

Fraza WHERE

WHERE <warunek>

Warunki filtruj

ą

ce (zwykłe)

<wyra

ż

enie> <op> <stała>

<wyra

ż

enie> <op> <wyra

ż

enie>

<op>::= =, >, >=, <, <=, !=, <>

<wyra

ż

enie> [NOT] BETWEEN <dół> AND <góra>

<wyra

ż

enie> [NOT] IN (<lista warto

ś

ci>)

Przykłady

select *

from uczniowie

where klasa = 4

where waga – (wzrost - 100) > 10

where wzrost beetwen 178 and 183

where klasa in (1,2)

<wyra

ż

enie> [NOT] LIKE <wzorzec tekstowy>

znaki zast

ę

pcze we wzorcu tekstowym:

_ : zast

ę

puje 1 znak,

% : zast

ę

puje 0, 1, 2, 3, ... znaków

Przykłady

select *

from uczniowie

where nazwisko like ‘Kowalsk_’

where upper(nazwisko) like ‘KOWALSK_’

where upper(nazwisko) like ‘KOWAL%’

Warunki ł

ą

cz

ą

ce

Tablice bazy danych:

Zespo

ł

y (nrz, nazwa, nrpk)

Pracownicy (nrp, nazwisko, nrz)

Wyp

ł

aty (nrp, nrt, kwota)

Tematy (nrt, nazwa, nrpk)

Zespoły

1

Zespół Wdro

ż

e

ń

2

2

Zespół Analiz

1

nrpk

nazwa

nrz

Pracownicy

1

Gliwice

Jaworek

3

1

Zabrze

Grabski

2

2

Ruda

Ś

l

ą

ska

Lipowski

1

nrz

adres

nazwisko

nrp

Tematy

2

Projekt reaktora

3

1

Projekt przetwornika

2

2

Projekt zasilacza

1

nrpk

nazwa

nrt

Wypłaty

200

3

1

200

2

3

150

1

1

150

3

3

300

2

2

kwota

nrt

nrp

Warunki ł

ą

cz

ą

ce

select nazwisko, nazwa

from pracownicy, zespoły

where pracownicy.nrz = zespoły.nrz

select nazwisko, nazwa

from pracownicy p, zespoły z

where p.nrz = z.nrz

Pracownicy

1

Gliwice

Jaworek

3

1

Zabrze

Grabski

2

2

Ruda

Ś

l

ą

ska

Lipowski

1

nrz

adres

nazwisko

nrp

Zespoły

1

Zespół Wdro

ż

e

ń

2

2

Zespół Analiz

1

nrpk

nazwa

nrz

Pracownicy



Zespo

ł

y

Zespół Analiz

Zespół Analiz

Zespół Wdro

ż

e

ń

nazwa

1

1

2

nrz

2

Gliwice

Jaworek

3

2

Zabrze

Grabski

2

1

Ruda

Ś

l

ą

ska

Lipowski

1

nrpk

adres

nazwisko

nrp

select nazwisko, nazwa, pracownicy.nrz

from pracownicy, zespoły

where pracownicy.nrz = zespoły.nrz

select nazwisko, nazwa, p.nrz

from pracownicy p, zespoły z

where p.nrz = z.nrz

select p.nazwisko, z.nazwa, p.nrz

from pracownicy p, zespoły z

where p.nrz = z.nrz

select nazwisko, nazwa

from pracownicy p, zespoły z

where p.nrp = z.nrpk

Warunki ł

ą

cz

ą

ce

select nazwisko, kwota, nrt

from pracownicy, wypłaty

where pracownicy.nrp = wypłaty.nrp

select p.nazwisko, w.kwota, w.nrt, p.nrp

from pracownicy p, wypłaty w

where p.nrp = w.nrp

Zł

ą

czenie tabel

Pracownicy

i

Wypłaty

Wypłaty

nrp

nrt

kwota

2

2

300

3

3

150

1

1

150

3

2

200

1

3

200

Pracownicy

nrp

nazwisko

adres

nrz

1

Lipowski

Ruda

2

2

Grabski

Zabrze

1

3

Jaworek

Gliwice

1

Pracownicy



Wypłaty

200

3

2

Ruda

Lipowski

1

200

2

1

Gliwice

Jaworek

3

150

1

2

Ruda

Lipowski

1

150

3

1

Gliwice

Jaworek

3

300

2

1

Zabrze

Grabski

2

kwota

nrt

nrz

adres

nazwisko

nrp

Pracownicy



Wypłaty



Tematy

2

Pr. reaktora

200

3

2

Ruda

Lipowski

1

1

Pr. przetwor.

200

2

1

Gliwice

Jaworek

3

2

Pr. zasilacza

150

1

2

Ruda

Lipowski

1

2

Pr. reaktora

150

3

1

Gliwice

Jaworek

3

1

Pr. przetwor.

300

2

1

Zabrze

Grabski

2

nrpk

nazwa

kwota

nrt

nrz

adres

nazwisko

nrp

select nazwisko, kwota, nrt

from pracownicy p, wypłaty w

where p.nrp = w.nrp and nrz = 4 and

kwota > 2000

select distinct z.nazwa

from 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’

select p2.nazwisko

from pracownicy p1, pracownicy p2

where p1.nazwisko = ‘Bukowy’ and p1.nrz =

p2.nrz and p2.nazwisko <> ‘Bukowy’

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

Zł

ą

czenia zewn

ę

trzne

select nazwisko, kwota, nrt

from pracownicy p, wypłaty w

where p.nrp = w.nrp

Zł

ą

czenie naturalne

r (A, B, C)

s (C, D)

q (A, B, C, D)

————
 ———

= —————

a

1

b

1

c

1

c

1

d

1

a

1

b

1

c

1

d

1

a

2

b

2

c

2

c

5

d

5

a

4

b

4

c

1

d

1

a

3

b

3

c

3

a

4

b

4

c

1

Zł

ą

czenie zewn

ę

trzne

r (A, B, C)

s (C, D) (+)

q (A, B, C, D)

————
 ———— = —————

a

1

b

1

c

1

c

1

d

1

a

1

b

1

c

1

d

1

a

2

b

2

c

2

c

5

d

5

a

4

b

4

c

1

d

1

a

3

b

3

c

3

a

2

b

2

c

2

a

4

b

4

c

1

a

3

b

3

c

3

Zł

ą

czenia zewn

ę

trzne

select nazwisko, kwota, nrt

from pracownicy p, wypłaty w

where p.nrp = w.nrp (+)

Zapis z

ł

ą

cze

ń

wed

ł

ug standardu SQL-2

SELECT <>

FROM <tabela A> [INNER] JOIN <tabela B> ON <warunek>

WHERE <warunek filtruj

ą

cy>

Z

łą

czenia zewn

ę

trzne

SELECT <>

FROM <tabela A> {LEFT|RIGHT|FULL}[OUTER] JOIN <tabela B> ON <warunek>

WHERE <warunek filtruj

ą

cy>

Przykład

select nazwisko, kwota, nrt

from pracownicy p join wypłaty w on p.nrp = w.nrp

where nrz=4 and kwota > 2000

Zł

ą

czenie zewn

ę

trzne - przyk

ł

ad

Zapis klasyczny

select nazwisko, kwota, nrt

from pracownicy p, wypłaty w

where p.nrp = w.nrp (+)

Zapis w SQL-2

select nazwisko, kwota, nrt

from pracownicy p left outer join wypłaty w on p.nrp =

w.nrp

Pe

ł

ny zapis z

ł

ą

cze

ń

w standardzie SQL-2 (SQL-92)

SELECT <>

FROM <rodzaj zł

ą

czenia>

<rodzaj zł

ą

czenia>::=

<tab A> [<typ zł

ą

czenia>] JOIN <tab B>

<tab A> [<typ zł

ą

cz.>] JOIN <tab B> USING (<kolumna>, ...)

<tab A> [<typ zł

ą

czenia>] JOIN <tab B> ON <warunek>

<typ zł

ą

czenia>::= INNER|{LEFT|RIGHT|FULL}[OUTER]

select nazwisko, kwota, nrt

from pracownicy join wypłaty

lub

from pracownicy join wypłaty using (nrp)

lub

from pracownicy p join wypłaty w on p.nrp = w.nrp

Warunki filtruj

ą

ce z pytaniami zagnie

ż

d

ż

onymi

SELECT <>

FROM <>

WHERE <pocz

ą

tek warunku>

(SELECT <>

FROM <>

WHERE < . . . >)

<pocz

ą

tek warunku>::=

1) <wyra

ż

enie> <op>

2) <wyra

ż

enie> <op> {ANY|ALL}

3) <wyra

ż

enie> [NOT] IN

4) [NOT] EXISTS

Baza danych:

Zespo

ł

y (nrz, nazwa, nrpk)

Pracownicy (nrp, nazwisko, nrz, premia)

Wyp

ł

aty (nrp, nrt, kwota)

Tematy (nrt, nazwa, nrpk)

ad 1)

<wyra

ż

enie> <op>

select nazwisko

from pracownicy

where nrp =

(select nrpk

from zespoły

where nazwa = ‘Zespół Wdro

ż

e

ń

’)

ad 1)

<wyra

ż

enie> <op>

Pracownicy (nrp, nazwisko, nrz, premia)

select nazwisko

from pracownicy

where nrz = 3 and premia >

(select premia

from pracownicy

where nazwisko = ‘Jaworek’)

ad 2)

<wyra

ż

enie> <op> {ANY|ALL}

Konstrukcja warunku z {ANY | ALL} pozwala

sprawdzi

ć

, czy warto

ść

wybrana w pytaniu

zewn

ę

trznym spełnia warunek dla {jakiejkolwiek |

ka

ż

dej} warto

ś

ci wybranej w pytaniu wewn

ę

trznym

<wyra

ż

enie> <op> {ANY|ALL}

select nazwisko

from pracownicy

where nrp =

(select nrp

from wypłaty

where kwota > 2000)

Problem: pytanie wewn

ę

trzne daje w wyniku zbiór

warto

ś

ci!

<wyra

ż

enie> <op> {ANY|ALL}

select nazwisko

from pracownicy

where nrp = any

(select nrp

from wypłaty

where kwota > 2000)

<wyra

ż

enie> [NOT] IN

select nazwisko

from pracownicy

where nrp in

(select nrp

from wypłaty

where kwota > 2000)

Ograniczenia na posta

ć

zapyta

ń

z warunkami

zawieraj

ą

cymi pytania zagnie

ż

d

ż

one

Wyszukiwane atrybuty (kolumny) (umieszczone na
li

ś

cie frazy

select)

mog

ą

pochodzi

ć

tylko z tablicy

w pytaniu zewn

ę

trznym

select nazwisko

from pracownicy

where nrz = 4 and nrp in

(select nrp

from wypłaty

where kwota > 2000)

ALL

select nazwisko, wzrost

from uczniowie

where klasa = 1 and wzrost > all

(select wzrost

from uczniowie

where klasa = 3)

select nazwisko

from pracownicy p

where 2000 < all

(select kwota

from wypłaty w

where w.nrp = p.nrp)

ad 3) <wyra

ż

enie> [NOT] IN

Operator IN jest równowa

ż

ny operatorowi =ANY

select nazwa

from tematy

where nrt in

(select nrt

from wypłaty)

select nazwa

from tematy

where nrt not in

(select nrt

from wypłaty)

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

ad 4)

[NOT] EXISTS

Predykat

EXISTS

podaje warto

ść

prawdy, je

ś

li zbiór

wierszy wybranych w pytaniu wewn

ę

trznym nie jest pusty

select nazwa

from tematy t

where exists

(select *

from wypłaty w

where w.nrt = t.nrt)

select nazwisko

from pracownicy p

where not exists

(select *

from wypłaty w

where w.nrp = p.nrp)

Równowa

ż

no

ść

znana z logiki matematycznej

(rachunku zda

ń

)

(

∀

t) (

ϕ

(t))

≡

(

¬∃

t) (

¬ϕ

(t))

select nazwisko

from pracownicy p

where not exists

(select *

from tematy t

where not exists

(select *

from wypłaty w

where w.nrt = t.nrt and w.nrp =

p.nrp))

Funkcje agreguj

ą

ce

SUM (<arg>)

AVG (<arg>)

MAX (<arg>)

MIN (<arg>)

COUNT (<arg>)

<arg>::= [ALL|DISTINCT]<wyra

ż

enie>

oraz

COUNT(*)

select count(*)

from pracownicy

select sum(kwota), avg(kwota), max(kwota)

from wypłaty

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

select max(waga - (wzost - 100))

from uczniowie

select count(nrp), count(distinct nrp),

count(distinct nrt)

from wypłaty

select nazwisko, wzrost

from uczniowie

where wzrost =

(select max(wzrost)

from uczniowie)

select klasa, nazwisko, wzrost

from uczniowie u1

where wzrost =

(select max(wzrost)

from uczniowie u2

where u2.klasa = u1.klasa)

select nrp, nrt, kwota

from wypłaty w1

where kwota =

(select max(kwota)

from wypłaty w2

where w2.nrt = w1.nrt)

Grupowanie – fraza GROUP BY

select nrz, count(*)

from pracownicy

group by nrz

select nrt, sum(kwota), max(kwota), count(*)

from wypłaty

group by nrt

select nrz, sum(kwota)

from pracownicy p, wypłaty w

where p.nrp = w.nrp

group by nrz

select nazwisko, sum(kwota)

from pracownicy p, wypłaty w

where p.nrp = w.nrp

group by nazwisko, p.nrp

SELECT <lista kolumn 1>,<lista funkcji agr.>

FROM <>

WHERE <>

GROUP BY <lista kolumn 2>

<lista kolumn 1>

⊆

<lista kolumn 2>

Filtrowanie grup – fraza HAVING

HAVING <warunek filtruj

ą

cy>

select nrz, count(*)

from pracownicy

group by nrz

having count(*) > 20 or count(*) < 5

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

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

Konstruktor tablicy w frazie FROM

SELECT <>

FROM <konstruktor tablicy>

select max(suma)

from (select sum(kwota) as suma

from wypłaty

group by nrp)

Konstruktor tablicy na li

ś

cie SELECT

select nrp, nazwisko, (select sum(kwota)

from wypłaty w where w.nrp = p.nrp)

as suma

from pracownicy p

Porz

ą

dkowanie wyników wyszukiwania –

fraza ORDER BY

ORDER BY {<wyra

ż

enie> |<nr>} [ASC|DESC], ...

select *

from pracownicy

order by nrz, nazwisko

select nazwisko, waga–(wzrost-100), klasa

from uczniowie

order by klasa, waga–(wzrost–100) desc

lub

order by 3,2 desc

Fraza UNION
(i frazy pokrewne: INTERSECT, MINUS, ...)

SELECT <lista kolumn 1>

FROM <>

. . .

UNION [ALL]

SELECT <lista kolumn 2>

FROM <>

. . .

Fraza UNION - przykład

select nrpk

from zespoły

union

select nrpk

from tematy

Fraza UNION - przykład

select nrpk

from zespoły

union all

select nrpk

from tematy

Fraza INTERSECT - przykład

select nrpk

from zespoły

intersect

select nrpk

from tematy

Fraza MINUS - przykład

select nrpk

from zespoły

minus

select nrpk

from tematy