dbvs II paskaita 2015 09 18 dr R

dbvs II paskaita 2015 09 18 dr. R. Pranaitis 1 VU KF IKI

SQL 2 © Pearson Education Limited, 2004

Turinys SQL paskirtis ir svarba. Duomenų gavimas - SELECT Duomenų įterpimas - INSERT Duomenų atnaujinimas - UPDATE Duomenų ištrynimas - DELETE Naujos lentelės sukūrimas - CREATE TABLE 3

SQL - pagrindinė reliacinių DBVS kalba SQL ypatumai: Lengva išmokti Neprocedūrinė kalba – joje apibūdinama kokios informacijos reikia, o ne kaip ją gauti SQL yra palyginti laisvos formos kalba SQL naudojami įprasti anglų kalbos žodžiai, pvz. , SELECT, INSERT, UPDATE Tinkama daugeliui vartotojų 4

SQL svarba SQL yra pirmoji ir vienintelė iki šiol standartinė duomenų bazių kalba, sugebėjusi įgyti platų pripažinimą SQL – tai milžiniškos gamintojų ir vartotojų investicijos FIPS standartas SQL - kitų standartų pagrindas 5

SQL paskirtis Padėti vartotojui Sukurti Duomenų bazę ir lentelių struktūras, Atlikti pagrindines užduotis, pvz. , insert, update, delete, Įvykdyti paprastas ir sudėtingas užklausas. SQL užklausos turėtų būti atliekamos mini- maliomis vartotojo pastangomis SQL turėtų būti lengvai išmokstama 6

SQL apibūdinimas SQL sudaro 2 pagrindiniai komponentai DDL – duomenų bazės struktūrai apibrėžti, DML – duomenims išgauti ir atnaujinti. Iki SQL 3 SQL nebuvo srauto valdymo komandų SQL – gali būti naudojama interaktyviai arba integruotai aukšto lygio kalboje (pvz. , C, C++). 7

SQL komandų rašymas SQL sakinį sudaro rezervuoti žodžiai ir varto- tojo apibrėžti žodžiai. – Rezervuoti žodžiai: pastovi SQL dalis. Jie turi būti parašyti tiksliai. Perkėlimai į kitą eilutę negalimi. – Vartotojo sukurti žodžiai: tai įvairių DB objektų vardai, pvz. , lentelių, stulpelių, parodymų [views]. 8

SQL komandų rašymas Daugelis SQL sakinio komponentų nepriklauso nuo raidžių registro (case insensitive), išskyrus for pažodinio pobūdžio duomenis Naudojant išplėtotą BNF žymenų formą: - rezerviniai žodžiai - didžiosios raidės 9 vartotojo sukurti žodžiai – mažosios raidės | ženklu atskiriamos alternatyvos {} skliaustai skirti būtiniems elementams [] skliaustai skirti nebūtiniems elementams … nurodo nebūtinus pasikartojimus

Literalai - tai konstantos SQL sakiniuose Ne skaičių literalai pateikiami kabutėse (pvz. , ‘Londonas’). Skaičių literalai pateikiami be kabučių (pvz. , 650. 00). 10

SELECT sakinys SELECT [DISTINCT | ALL] {* | [stulpelio_išraiška [AS naujas_vardas]] [, . . . ] } FROM lentelės_pavadinimas [alias] [, . . . ] [WHERE sąlyga] [GROUP BY stulpelių_sąrašas] [HAVING sąlyga] [ORDER BY 11 stulpelių_sąrašas]

SELECT sakinys FROM WHERE 12 nurodo naudojamas lenteles eilučių filtras

SELECT sakinys Punktų tvarkos keisti negalima SELECT ir FROM yra privalomi 13

Paprasčiausia užklausa [visi stulpeliai, visos eilutės] Tarkime, reikia visų video duomenų SELECT katalogo_nr, pavadinimas, kategorija, dienos_renta, kaina, režisieriaus_nr FROM video naudojant * , t. y. ‘visos eilutės’, galima parašyti paprasčiau: SELECT * FROM Video w 3 schools 14

Paprasčiausios užklausos rezultatas 15

Kai reikia tik kai kurių stulpelių Tarkime, reikia visų video katalogo. Nr, pavadinimų ir nuomos įkainio SELECT katalogo. Nr, pavadinimas, dienos_nuoma FROM Video w 3 schools 16

Kai kurie stulpeliai, visos eilutės 17

DISTINCT naudojimas Tarkime, reikia visų video kategorijų sąrašo SELECT kategorija FROM Video w 3 schools 18

DISTINCT naudojimas DISTINCT padeda išvengti pasikartojimų: SELECT DISTINCT kategorija FROM Video; 19

Skaičiuojamieji laukai Reikia video nuomos trims dienoms sąrašo SELECT katalogo. Nr, pavadinimas, dienos_nuoma*3 FROM Video 20

Skaičiuojamieji laukai Pavadinimas stulpeliui suteikiamas naudojant AS SELECT katalogo. Nr, pavadinimas, dienos_nuoma*3 AS 3 d_nuoma FROM video w 3 schools 21

Ieškos sąlyga – palyginimas Tarkime, reikia darbuotojų, kurių atlyginimas > 10, 000 lt, sąrašo SELECT darb. Nr, vardas, etatas, alga FROM personalas WHERE alga > 10000; w 3 schools 22

Ieška - nurodant sritį Tarkime, reikia sąrašo darbuotojų, kurių alga yra tarp 45, 000 ir 50, 000 lt SELECT darb. Nr, vardas, etatas, alga FROM personalas WHERE alga BETWEEN 45000 AND 50000 Tarp – tai reiškia >= ir <= w 3 schools 23

Ieška nurodant sritį darb. Nr 24 vardas etatas alga

Ieška – norodant sritį Galima naudoti ir NOT BETWEEN Tas BETWEEN daug galios SQL nesuteikia – galima tai atlikti palyginant SELECT darb. Nr, vardas, etatas, alga FROM personalas WHERE salary >= 45000 AND salary <= 50000 Reikšmių sričiai BETWEEN naudingas 25

Priklausymo nustatymas Tarkime, reikia veiksmo ir vaikiškų video sąrašo. SELECT katalogo. Nr, pavadinimas, kategorija FROM video WHERE kategorija IN (‘veiksmas’, ‘vaikams’); 26

Priklausymo nustatymas Galimas negatyvas (NOT IN) Daug galios IN irgi SQL nesuteikia, nes galima: SELECT katalogo. Nr, pavadinimas, kategorija FROM Video WHERE kategorija =‘veiksmas’ OR kategorija =‘vaikams’ IN naudingesnis tada, kai reikšmių yra daug w 3 schools 27

Ieška pagal ruošinį Tarkime, ieškoma darbuotojų vardu Sally. SELECT darb. Nr, vardas, etatas, alga FROM personalas WHERE vardas LIKE ‘Sally%’; 28

Ieška pagal ruošinį SQL naudojami simboliai: du specialūs ruošinio % : bet koks simbolių skaičius, _ (pabraukimas): betkoks vienas simbolis. LIKE ‘Sally%’ reiškia, kad po penkių pirmų simbolių [Sally] gali būti parašyta bet kas. w 3 schools 29

Ieška pagal NULL Tarkime, reikia dar negrąžintų video sąrašo. Tuomet reikia ieškoti pagal specialų raktinį žodį IS NULL: SELECT nuomos_data, nario. Nr, video. Nr FROM nuomos_sutartis WHERE grazinimo_data IS NULL; w 3 schools 30

Ieška pagal NULL Galima naudotis ir negatyvia versija (IS NOT NULL) 31

Rikiavimas pagal stulpelį Tarkime, reikia visų video sąrašo kainų mažėjimo tvarka SELECT * FROM Video ORDER BY kaina DESC; w 3 schools 32

Rikiavimas pagal stulpelį 33

SELECT sakinys – agregatinės funkcijos ISO SQL apibrėžiamos penkios agregatinės funkcijos: COUNT - randa nurodyto stulpelio reikšmių skaičių SUM - randa nurodyto stulpelio reikšmių sumą AVG - randa nurodyto stulpelio reikšmių vidurkį MIN - randa nurodyto stulpelio mažiausią reikšmę MAX - randa nurodyto stulpelio didžiausią 34 w 3 schools

SELECT sakinys – agregatinės funkcijos Funkcijos skaičiuoja su vienu lentelės stulpeliu ir grąžina vieną reikšmę COUNT, MIN, ir MAX taikomos skaitiniams ir neskaitiniams laukams, o SUM ir AVG tinka tik skaičiams išskyrus COUNT(*), funkcijos pirmiausia eli- minuoja ‘null’ reikšmes ir po to skaičiavimus atlieka su likusiomis reikšmėmis 35

SELECT sakinys – agreg. funkcijos COUNT(*) suskaičiuoja visas lentelės eilutes (ir tuščias, ir dubliuojančias) Dublikatų galima išvengti prieš stulpelio pavadinimą panaudojus DISTINCT neveikia MIN/MAX, bet gali būti naudojamas su SUM/AVG 36

COUNT ir SUM naudojimas Tarkime, reikia viso personalo, kurio alga didesnė už 40, 000 lt, skaičiaus ir jų algų bendros sumos. SELECT COUNT(darb. Nr) AS visi_darbuotojai, SUM(alga) as bendras_atlyginimas FROM personalas WHERE alga> 40000; w 3 schools 37

COUNT ir SUM naudojimas 38

MIN, MAX ir AVG naudojimas Tarkime, reikia rasti minimalų, maksimalų ir vidutinį personalo atlyginimą SELECT MIN(salary) AS min_alga, MAX(salary) AS max_alga, AVG(salary) AS vidutinė_alga FROM personalas w 3 schools 39

GROUP BY naudojimas Tarkime, reikia rasti kiekvienos šakos personalo skaičių ir bendras jų algas SELECT padal. Nr, COUNT(darb. Nr) AS visi_darbuotojai, SUM(alga) AS bendra_alga FROM personalas GROUP BY padal. Nr ORDER BY padal. Nr; w 3 schools 40

GROUP BY naudojimas 41

Užklausa užklausoje (subužklausa) Tarkime, reikia rasti darbuotojus, dirbančius padalinyje, kurio adresas ‘ 8 Jefferson Way’ SELECT darb. No, vardas, etatas FROM personalas WHERE padal. Nr = (SELECT padal. Nr FROM padaliniai WHERE street=‘ 8 Jefferson Way’); 42

Subužklausa Vidiniu SELECT nustatomas padalinio, kurio adresas ‘ 8 Jefferson Way’, numeris (‘B 001’). Po to išoriniu SELECT randami tame padalinyje dirbančių darbuotojų duomenys Išorinis SELECT tada tampa SELECT darb. Nr, vardas, etatas FROM personalas WHERE padal. Nr = ‘B 001’; 43

Subužklausa 44

Sub užklausa su agreg. funkcija Tarkime, reikia rasti personalo darbuotojus, kurių atlyginimas yra didesnis už vidutinį SELECT darb. Nr, vardas, etatas FROM personalas WHERE alga > (SELECT AVG(alga) FROM personalas); 45

Sub užklausa su agreg. funkcija ‘WHERE alga > AVG(alga)’ rašyti negalima, nes užklausos pradžioje vidutinė alga nežinoma Panaudojus subužklausą, pirmiausia randama vidutinė alga (41166. 67), o po to išorinis SELECT panaudojamas darbuotojams, kurių alga viršija vidurkį, atlyginimams rasti 46 SELECT staff. No, name, position FROM Staff WHERE salary > 41166. 67;

Subužklausa su agreg. funkcija 47

Kelių lentelių užklausos !!! Anksčiau pateiktuose pavyzdžiuose buvo naudojamos tos pačios lentelės subužklausos Kai užklausos stulpeliai yra iš kelių lentelių, naudojama jungtis (join) Jungtis formuojama po FROM įrašant keletą lentelių (lentelių pavadinimai atskiriami kableliais) 48

Kelių lentelių užklausos - alias Kelių lentelių užklausose skirtingų lentelių stulpelių pavadinimai gali sutapti (galimi neapibrėžtumai formuojant užklausą) Tokiais atvejais sukuriami ir naudojami lentelių vardų sinonimai (pvz. , FROM video v) 49

Paprasta jungtis [Join] Tarkime, reikia visų video sąrašo su režisierių vardais SELECT katalogo. Nr, pavadinimas, kategorija, v. rezisierius. Nr, rezisierius_vardas FROM video v, rezisierius d WHERE v. rezisierius. Nr = d. rezisierius. Nr; 50

Paprasta jungtis [Join] Kaip ieškos rezultatas pateikiamos tik tos eilutės, kurioms abiejose lentelėse stulpelių rezisierius. Nr reikšmės yra identiškos (v. director. No = d. director. No) 51

JOIN alternatyvos Join alternatyvos FROM Video v JOIN Director d ON v. director. No = d. director. No FROM Video JOIN Director USING director. No FROM Video NATURAL JOIN Director w 3 schools 52

4 lentelių jungtis Tarkime, reikia sąrašo visų video su režisieriais, artistais ir jų vaidmenimis SELECT v. catalog. No, title, category, director. Name, actor. Name, character FROM Video v, Director d, Actor a, Role r WHERE d. director. No = v. director. No AND v. catalog. No = r. catalog. No AND r. actor. No = a. actor. No; 53

4 lentelių jungtis [Join] 54

INSERT naudojimas INSERT INTO lentelės_pavad [ (stulpeliu_sarasas) ] VALUES (duomenu_reiksmiu_sarasas) Stulpeliu_sarasas nebūtinas. Pagal nutylėjimą SQL naudoja visus stulpelius ta tvarka, kuria jie buvo išvardinti kuriant lentelę - CREATE TABLE Duomenu_reiksmiu_sarasas turi atitikti stulpelių sąrašą (elementų skaičius, pozicijos, duomenų tipai) w 3 schools 55

INSERT naudojimas Tarkime, reikia į video lentelė reikia įterpti eilutę INSERT INTO Video VALUES (‘ 207132’, ‘Die Another Day’, ‘Action’ 5. 00, 21. 99, ‘D 1001’ ); 56

UPDATE naudojimas UPDATE Table. Name SET column. Name 1 = data. Value 1 [, column. Name 2 = data. Value 2. . . ] [WHERE search. Condition] Table. Name – bazės lentelės pavadinimas SET – atnaujinamų stulpelių sąrašas WHERE – nebūtinas datavalue - duomenų reikšmių tipai turi derėti 57 w 3 schools

Lentelės eilučių atnaujinimas – UPDATE Tarkime, reikia 10% padidinti trilerių kategorijos filmų nuomos kainą UPDATE Video SET dienos_nuoma = dienos nuoma*1. 1 WHERE kategorija = ‘trileris’; 58

DELETE naudojmas DELETE FROM lentelės_pavadinimas [WHERE ieškos_sąlyga] Ieškos_sąlyga neprivaloma. Ją praleidus, bus ištrintos visos lentelės eilutės. Pati lentelė nebus ištrinta. Esant ieškos_sąlygai, bus ištrintos tik ieškos_sąlygą tenkinančios eilutės w 3 schools 59

Eilutės ištrynimas Tarkime, reikia ištrinti įrašą apie video, kurio numeris kataloge: 634817 DELETE FROM Video_nuomai WHERE katalogo. Nr = ‘ 634817’; 60

Duomenų apibrėžimas Du pagrindiniai SQL DDL sakiniai: CREATE TABLE – naujos lentelės sukūrimas CREATE VIEW – naujo parodymo sukūrimas w 3 schools 61

CREATE TABLE sakinys CREATE TABLE Table. Name {(column. Name data. Type [NOT NULL] [UNIQUE] [DEFAULT default. Option][, . . . ]} [PRIMARY KEY (list. Of. Columns), ] {[UNIQUE (list. Of. Columns), ] […, ]} {[FOREIGN KEY (list. Of. FKColumns) REFERENCES Parent. Table. Name [(list. Of. CKColumns)], [ON UPDATE referential. Action] [ON DELETE referential. Action ]] [, …]} 62

Stulpelio apibrėžimas column. Name data. Type [NOT NULL] [UNIQUE] [DEFAULT default. Option] SQL palaikomi šie duomenų tipai: 63

Klausimai? 64