Duomenų bazių projektavimas

Duomenų tipai

Kuriant lentelę reikia išvardyti jos stulpelius, kiekvienam kurių būtina nurodyti galimų reikšmių
aibę– stulpelio tipą. Visų pirma stulpelio duomenų tipui priskiriama duomenų rūšis:

• Simboliniai duomenys
• Dvejetainiai duomenys
• Skaičiai
• Datos
• Laikas

Simboliniai duomenys (angl. character datatypes).

Simbolių eilutė turi simbolių skaičiaus atributą – ilgį. 

Fiksuoto ilgio simbolių eilutės:

• CHAR(n) – iki 254 bait,

Kintamo ilgio:

• VARCHAR(n) – iki 32672 (4000) baitų ilgio,
• CLOB(n[K|M|G]) – iki 2 GB (4 GB).

Kai n > 254 užklausose negalima naudoti:

• DISTINCT,GROUP BY,ORDER BY

Skaitiniai duomenys (number datatypes).

SMALLINT – sveikieji skaičiai [-32 768; 32 767].

INTEGER – „dideli“ sveikieji skaičiai (4 baitai) [-2 147 483 648; 2 147 483 647].

BIGINT – „ypač dideli“ sveikieji skaičiai (8 b)

REAL – slankiojo kablelio skaičiai, 32 bitai, pvz., -2E5, 5.555E-18, -.655645e8.

FLOAT (DOUBLE) – dvigubo tikslumo skaičiai (64 b)

DECIMAL (n, m) (NUMERIC) – dešimtainiai supakuoti skaičiai – iki 1000 (31) dešimtainių

skaitmenų.

Dvejetainiai duomenų tipai (binary datatypes).

BIT(n)- Fiksuoto ilgio:

Kintamojo ilgio:

BIT VARYING(n)

BLOB(n[K|M|G])

Datos ir laiko duomenys.
DATE – (atmintyje – 4 baitai)
TIME – (3 baitai)
TIMESTAMP –(10 bait)
DATE ir TIME vaizdavimas priklauso nuo terpės
(locale),
2005.01.01, 12:00:00,
2005-01-01-12.15.55.330000
DBVS visada „supranta“ ISO datą ir laiką:
2005-01-01, 12:00:00

Visi SQL duomenų tipai turi ypatingą reikšmę - NULL.

Parinkus stulpeliui duomenų tipą, reikia parinkti ilgio charakteristiką ir tikslumą.

Lentelės kūrimo sintaksė

CREATE TABLE

Kuriant (apibrėžiant) lentelę būtinai nurodoma:

• lentelės vardas.
• lentelės stulpelių vardai ir jų tipai.

NOT NULL - stulpelis negali įgyti NULL reikšmės.

DEFAULT - numatytoji reikšmė

Indeksų kūrimas

Indeksas yra objektas kuris gali pagreitinti priėjimą prie duomenų, pateikiant eilutes iš lentelės vartotojui. Jie gali būti sukuriami automatiškai ar išoriškai. Jei nėra indekso priskirto stulpeliui tai pilna lentelė bus peržiūrima.

Indeksas pasirūpina tiesioginį ir greitą priėjimą prie eilučių lentelėse. Jų tikslas yra pašalinti diskų I/O operacijas, tam panaudojant indeksų dalį . Indeksus automatiškai naudoja ir palaiko ORCLE Serveris. Kai jau kartą indeksai yra sukurti, jokių vartotojo tiesioginių veiksmų nereikia. Indeksai yra logiškai ir fiziškai nepriklausomi nuo lentelės kuriai jie sukurti. Tai reiškia kad jie gali būti sukurti ar panaikinti bet kuriuo laiku ir neturi įtakos bazinei lentelei ar kitiems indeksams. Kai lentelė panaikinama, surišti su ja indeksai taip pat panaikinami.Taigi indeksai kuriami automatiškai ar rankiniu būdu. Automatiškai jie kuriami kai jūs nustatote pirminį raktą arba stulpelio reikšmes apibūdinate kaip unikalias. Rankiniu būdu galima suskurti indeksą dėl neunikalių stulpelio reikšmių kad pagreitinti priėjimą prie eilučių.

Indeksas gali būti kuriamas vienai ar keliems stulpeliams.

CREATE INDEX Indeksas ON Lentelė (Stulpelis1[,stulpelis2]...);

Padidinti užklausos greitį galima per Ename lentelei EMP.

CREATE INDEX Emp_Ename_idx ON EMP(Ename);

DROP INDEX Emp_Ename_Index;

Daug indeksų lentelei nereiškia kad tai pagreitins darbą. Kiekviena DML operacija kuri

baigiama per COMMIT lentelei su indeksais reiškia kad ir indeksai turi būti atnaujinami.

Daugiau indeksų kuriuos surišate su lentele, daugiau pastangų Serveris turi kad atnaujinti

indeksus po DML.

Indeksai kuriami kai:

• Dažnai stulpeliai naudojami paragrafe WHERE ar apjungimo sąlygoje
• Stulpelis talpina plačią reikšmių eilę.
• Stulpelis talpina didelį kiekį NULL reikšmių
• Du ar daugiau stulpelių dažnai naudojamos kartu WHERE paragrafe ar apjungimo sąlygoje
• Lentelė yra didelė ir daugumoje užklausų yra pateikiami 2-4% visų lentelės eilučių.

Negalima kurti indeksų:

• Kai lentelė yra maža.
• Stulpeliai nėra dažnai vartojami kaip sąlyga užklausoje.
• Dauguma užklausų tikisi ištraukti daugiau nei 2-4% visų lentelės eilučių.
• Lentelė dažnai yra atnaujinama.

DB projektavimas

• Atliekamas prieš pradedant DB formavimo procesą.
• Svarbus norint sukurti efektyvią, tikslią, poreikius atitinkančią sistemą.
• Patartina planavimą pradėti raštu ant balto popieriaus lapo.
• Nors atrodo, kad toks projektavimas yra laiko gaišimas pradžioje, tačiau išties daugiau laiko sugaištama vėliau, jei prieš tai neplanuojama.

DB projektavimo procesas

1. Numatyti duomenų bazės paskirtį.
2. Apibrėžti, kokios lentelės bus reikalingos.
3. Apibrėžti, kokie laukai turės būti DB lentelėse.
4. Nurodyti laukus su unikaliomis reikšmėmis.
5. Nustatyti, kaip lentelės siesis tarpusavyje.
6. Tobulinti pradinį projektą.

DB paskirtis

Pradedant kurti duomenų bazę reikia atsakyti į tris esminius klausimus:

1. Kokius duomenis turėtų kaupti duomenų bazė?
2. Kokią informaciją apie šiuos duomenis norės žinoti DB vartotojas?
3. Kokius veiksmus vartotojas norės atlikti su duomenimis?

Išoriniai raktai:

Pirminiai raktai taip pat atlieka vaidmenį kuriant ryšius tarp lentelių. Vienos lentelės

pirminis laukas įterpiamas į kitą lentelę ir tampa ten išoriniu raktu.

DB normalizavimas 

• Patikrinimas, kad:
• Lentelėje yra tik su pirminiu raktu susiję duomenys,
• Kad kiekviename lauke yra tik vienas duomenų blokas.
• Kad pasikartojantys duomenys yra eliminuoti
• Normalizavimo tikslai:
• Duomenų surikiavimas į tokias logines grupes, kad kiekviena grupė apibūdintų
• dalį visumos;
• Pasikartojančių duomenų kiekio duomenų bazėje minimizavimas;
• Efektyviai ir greitai prieinamos ir valdomos duomenų bazės sukūrimas nepakenkiant duomenų vientisumui;
• Sukūrimas tokios struktūros, kad keičiant duomenis, juos reikėtų taisyti tik vienoje vietoje.

Ryšiai tarp lentelių

Ryšys – sąsaja tarp bendrų laukų dvejose duomenų bazės lentelėse.

Trys tipai:

• 1:1
• Naudojamas retai, paprastai dėl saugumo, arba kai dalis lentelės laukų dažniausiai būna tušti
• ◦1:∞
• Dažniausiai naudojamas ryšys. Pirminis raktas su išorinio rakto lauku kitoje lentelėje
• ◦∞: ∞
• Reikia skaidyti į du 1:∞ ryšius per tarpinę lentelę

Tobulinimas

Sudėliojus pirminį DB projektą (lentelės, laukai ir ryšiai) reikia atidžiai jį išnagrinėti, ieškant galimų trūkumų.

Patikrinkite, ar tokia duomenų bazės struktūra leis jums gauti tuos atsakymus, kurių norite.

Paskutinė galimybė lengvai ištaisyti DB struktūros klaidas!

Dažniausios klaidos

• Per mažai dėmesio planavimui
• Normalizavimo ignoravimas
• Prastai parinkti laukų/lentelių pavadinimai
• Dokumentacijos trūkumas

Duomenų išrinkimas (SQL JOIN sakinys)

Kelių lentelių jungimas

Viena iš svarbiausių SELECT sakinio galimybių yra dviejų ar daugiau lentelių jungimas (angl. join).

Užklausa, jungianti 2 lenteles:

SELECT <stulpeliai> FROM <lentel1>, <lentel2>
WHERE <jungimo slyga> [AND <paieškos slyga>]

Daugelyje DBVS yra užtikrinamas lentelių jungimas SELECT sakinio fraze JOIN:

SELECT <stulpeliai>
FROM <lentelė1> JOIN <lentelė 2>
ON <jungimo sąlyga>
[WHERE <paieškos sąlyga>]



Pavardės vykdytojų, vykdančių projektą Nr. 1:

SELECT Pavardė
FROM Vykdytojai, Vykdymas
WHERE Vykdytojas = Nr AND Projektas = 1
Sąlyga Vykdytojas = Nr - loginis ryšys tarp dviejų lentelių.


Bendruoju atveju, jei užklausoje dviems lentelms nėra jokios sąlygos ir vienoje iš lentelių yra n eilučių, o kitoje - m eilučių, tai rezultatą sudarys m × n eilučių

Informacija apie tai, kokie vykdytojai kokius projektus vykdo ir kiek kiekvienam projektui skiria
valandų:

SELECT Pavardė, Pavadinimas, Valandos
FROM Vykdytojai, Projektai, Vykdymas
WHERE Projektas = Projektai.Nr AND
Vykdytojas = Vykdytojai.Nr

Stulpelio Nr patikslinimas lentelės vardu yra būtinas, nes dvi lentelės turi stulpelį vardu Nr.


Užklausa, jungianti 2 lenteles:

SELECT <stulpeliai> FROM <lentel1>, <lentel2>
WHERE <jungimo slyga> [AND <paieškos slyga>]

Daugelyje DBVS yra užtikrinamas lentelių jungimas SELECT sakinio fraze JOIN:

SELECT <stulpeliai>
FROM <lentelė1> JOIN <lentelė 2>
ON <jungimo sąlyga>
[WHERE <paieškos sąlyga>]

Struktrinės užklausos



Vienoje užklausoje gali būti ir kita užklausa, t.y. galimos struktūrinės užklausos – „Structured QL“.

Keli SELECT sakiniai yra griežtoje hierarchinėje priklausomybėje.
Pavardės vykdytojų, dalyvaujančių projekte Nr. 1:

SELECT Pavardė FROM Vykdytojai
WHERE Nr IN (SELECT Vykdytojas
FROM Vykdymas
WHERE Projektas = 1)

Duomenų išrinkimas (SQL SELECT sakinys)

Duomenų išrinkimas panaudojant

WHERE sakinyje nurodoma sąlyga, kurią turi tenkinti grąžinamos eilutės.
ORDER BY sakiniu nurodoma viena ar daugiau rikiavimo sąlygų.
GROUP BY sąlygoje nurodoma, kad reikia grupuoti tam tikras eilutes. Grupuojant eilutes, dažniausiai naudojamos agregatinės funkcijos maksimalioms, vidutinėms ir panašioms reikšmėms išrinkti iš grupuotų eilučių.
HAVING sakinyje nurodomas kriterijus, taikomas grupuojamoms eilutėms; šis
raktinis žodis gali būti naudojamas tik tais atvejais, jeigu užklausoje yra GROUP
BY sakinys.

1.Platesnis WHERE paragrafo panaudojimas

Duomenų išrinkimui pagal užduotą sąlygą formuojama užklausa, naudojant
paragrafą WHERE. Sąlyga gali būti pateikiama kaip aritmetinė išraiška ar kaip
simbolių eilutė, surišta per lyginimo operatorius =,>,<,>=,<=,<>.

SELECT * FROM Emp WHERE Sal>=1500;

SELECT Ename,Sal,Comm FROM Emp WHERE Sal<=Comm;
sąlygoje dalyvauja du stulpeliai, bet nenurodyta konkreti reikšmė.

*Simbolinės eilutės arba data sąlygoje turi būti pateikta tarp viengubų kabučių.

Paragrafe WHERE gali būti formuojama sąlyga, naudojant sąlygos
pateikimo būdus:

BETWEEN ... AND - kurie nurodo žemiausios ir aukščiausios reikšmių intervalą,
išrenkant duomenis iš lentelės, t.y. rezultate pateikia duomenis tam tikram
reikšmių intervalui.

SELECT Ename,Sal FROM EMP WHERE Sal BETWEEN 1000 AND 1500;
Pateikia darbuotojus, kurių alga yra intervale tarp 1000 ir 1500.

BETWEEN ..AND operatorius apima ir kraštutines reikšmes. Mažesnioji reikšmė
turi būti nurodyta pirma, didesnioji - antra.

IN (sąrašas reikšmių, kurios turi būti pateiktos užklausos rezultate,
pateikiamos per kablelį)

SELECT Empno,Ename,Sal,Mgr FROM EMP WHERE Mgr IN(7902,7566);
pateiks sąraše tik du darbuotojus.

Šią sąlygą galima būtų pateikti ir kitaip.

SELECT * FROM EMP WHERE Deptno IN(10,20);

Išves darbuotojus, kurie dirba 10 ir 20 departamentuose.


LIKE ( šablonas duomenų išrinkimui iš lentelės )

LIKE galima naudoti šabloną pagal kurį atrenkami duomenys iš lentelės.
Sąlygoje gali būti pateikiami tik simboliniai ar skaitmeniniai duomenys. Mažiausiai
turi būti du simboliai formuojant šabloną.

% - reiškia nulį arba keletą simbolių
_ reiškia vieną simbolį

SELECT Ename FROM EMP WHERE Ename LIKE ‘S%’;

parodo visus darbuotojų vardus, kurių pavardė prasideda “S” raide, bet
neparodys, kurių vardai prasidės “s” raide(mažąja). LIKE operatorius gali būti
kaip trumpinys.

SELECT Ename,Hiredate FROM EMP WHERE Hiredate LIKE ‘% 81‘;
- Visi kurie pradėjo dirbti 81 metais.

SELECT Ename FROM EMP WHERE Ename LIKE ‘_A%’;
Parodys visus darbuotojus, kurių varde bus antra raidė “A”


IS NULL - (nurodo, jog vartotoją domina tik eilutės lentelėje su neapibrėžta lauko reikšme)
NULL paragrafas gali būti naudojamas norint pateikti duomenis su NULL reikšme.

SELECT Ename,Mgr FROM EMP WHERE Mgr IS NULL;
pateiks duomenis, kur Mgr neužpildytas.

IS NULL tikrina nurodyto stulpelio reikšmę.

SELECT Ename,Job,Comm FROM EMP WHERE Comm IS NULL;
parodo visus darbuotojus, kurie negauna komisinio atlyginimo.

Sudėtinga sąlyga gali būti pateikta naudojant AND arba OR.
Tokiu būdu galima pateikti kelias sąlygas duomenų išrinkimui.

SELECT Empno,Ename,Job,Sal FROM EMP WHERE Sal>=1100 AND Job=’CLERK’;

Rezultate pasirodys visi darbuotojai, kurie užima nurodytas pareigas ir jų alga
viršija 1100.

SELECT Empno,Ename,Job,Sal FROM EMP WHERE Sal>1100 OR Job=’CLERK’;

Rezultate pasirodys visi darbuotojai, kurie užima nurodytas pareigas arba jų alga viršija 1100.

Su kiekvienu sąlygos paragrafu gali būti naudojamas ir paragrafas
NOT, kuris reiškia sąlygos neigimą.

SELECT * FROM EMP WHERE Comm IS NOT NULL;
Rezultate parodo visus darbuotojus, kurie gauna komisinį atlyginimą.

... WHERE NOT Job IN (“CLERK’,’MANAGER’)
... WHERE Sal NOT BETWEEN 1000 AND 1500

SELECT Ename,Job,Sal FROM EMP WHERE job=’SALESMAN’

OR job=’PRESIDENT’ AND sal>1500;

Dvi sąlygos nurodant pareigas su atlyginimu, kai pareigos pateikiamos taip pat per sąlygą OR (arba) t.y. kai pareigos gali turėti skirtingas reikšmes, bet abiem atvejais atlyginimui keliami tie patys reikalavimai.

SELECT Ename,Job,Sal FROM EMP WHERE (job=’SALESMAN’

OR job=’PRESIDENT’) AND sal>1500;

Irgi dvi sąlygos, bet tikrinama pirma pareigybes ir jei yra viena iš nurodytų,

toliau tikrinamas atlyginimas.

Rūšiavimas ORDER BY

Normaliai išvedant duomenis iš lentelės į ekraną, duomenų tvarka yra tokia, kaip jie buvo suvesti į lentelę. Norint pakeisti duomenų atvaizdavimo tvarką operatoriuje SELECT galima naudoti paragrafą ORDER BY, kuriame nustatome, kaip turi būti pateikti duomenys užklausos rezultate. Standartiniu būdu, naudojant ORDER BY, duomenų vaizdavimas vyksta pagal alfabetą nuo A iki Z arba skaičių

didėjimo tvarka. 

Norint pakeisti duomenų atvaizdavimo tvarką atvirkščiai, prie paragrafo ORDER BY naudojame papildomą žodį DESC. Jei naudojame paragrafą ORDER BY, jis turi būti nurodomas operatoriaus SELECT gale.

SELECT * FROM EMP ORDER BY Hiredate; 

rūšiuoja pagal priėmimo į darbą datą

SELECT * FROM EMP ORDER BY Hiredate DESC;

rūšiuoja taip pat pagal datą, bet atvirkštine tvarka, T.y. vėliausia priimti į darbą pasirodys užklausos

rezultate.

LOWER(stulpelis|reikšmė) 

 Bet kokiam formate parašytus simbolius rezultate pateikia mažosiomis raidėmis. Labai naudinga nurodant sąlygą užklausoje kai reikia suvienodinti informacijos formatą duomenų atrinkimo metu. Be to ji naudinga aplikacijose pateikiant informaciją vienodam pavidale, jei ji yra suvesta skirtingam formate.

SELECT LOWER(VARDAS) FROM DARBUOTOJAS

UPPER(stulpelisėlė|reikšmė)

Bet kokiam formate parašytus simbolius rezultate pateikia didžiosiomis raidėmis. Labai naudinga nurodant sąlygą užklausoje kai reikia suvienodinti informacijos formatą duomenų atrinkimo metu. Be to ji naudinga aplikacijose pateikiant informaciją vienodam pavidale, jei ji yra suvesta skirtingam formate.

SELECT UPPER(VARDAS) FROM DARBUOTOJAS

Funkcijos Visai Grupei (Grupinės)

Šios funkcijos veikia per visą lentelę arba per lentelės sritį ir tik tada pateikia bendrą rezultatą kiekvienai grupei atskirai arba visai lentelei. Pagal nutylėjimą visos lentelės eilutės skaitomos kaip viena grupė, jei nenurodyta kitaip. Paragrafas GROUP BY komandoje SELECT gali būti naudojamas sudalinti lentelę į grupes. Grupinėse funkcijose NULL reikšmė ignoruojama. Grupinėms funkcijoms galima priskirti

funkcijas:

AVG() - nustato vidurkį grupei

COUNT() - nustato eilučių, kurių išraiška yra apibrėžta, kiekį lentelėje

MAX() -nustato didžiausią reikšmę

MIN() - nustato mažiausią reikšmę

SUM() - nustato bendrą sumą, ignoruojant neapibrėžtas reikšmes

Bet kuriuo atveju funkcijos taikomos visai grupei, bet jei nurodyta DISTINCT (MIN(DISTINCT SAL)), tai funkcija taikoma tik skirtingoms reikšmėms, t.y. pasikartojančios reikšmės yra ignoruojamos. Visais atvejais, jei yra NULL reikšmė, tai įrašas ignoruojamas, išskyrus funkciją COUNT, kai naudojama ‘*’.

• SELECT MAX(SAL),MIN(SAL),AVG(SAL) FROM Emp;
• SELECT COUNT(*) from EMP where DEPTNO=20;

Norint sugrupuoti įrašus lentelėje, naudojam paragrafą GROUP BY

SELECT AVG(SAL) FROM Emp;

SELECT AVG(SAL) FROM Emp GROUP BY JOB;

Kiekvienai pareigybei išvedamas darbo užmokesčio vidurkis, tačiau toks atvaizdavimo būdas nėra akivaizdus, todėl rekomenduojama šalia spausdinti ir pareigybę:

SELECT JOB,AVG(SAL) FROM Emp GROUP BY JOB;
SELECT JOB, AVG(SAL) FROM Emp WHERE JOB=‘MANAGER’ GROUP BY JOB ;

Duomenys lentelėje sugrupuojami pagal darbą, ir dar po to galima išrinkti konkrečiai nurodytą darbą ir suskaičiuoti atlyginimo vidurkį.

Viduje vienos grupės galima dar grupuoti smulkiau. Tarkime skirtinguose departamentuose, norime

grupuoti informaciją pagal pareigybes :

SELECT JOB, AVG(SAL) FROM Emp WHERE JOB=‘MANAGER’ GROUP BY DEPTN,JOB ;

SELECT Deptno,Job,Sum(sal) FROM EMP GROUP BY Deptno,Job;

Pateikiant rezultatą pirmiausia duomenys grupuojami pagal departamento numerį, o po to

jo viduje dar grupuojama pagal darbo pobūdį. Taip sutvarkius, sumuojamas bendras

atlyginimas suformuotose grupėse.

Grupinėse funkcijose yra svarbu išvedant duomenis, užklausą formuoti taip, kad nebūtų individualių laukų, kurie nėra įtraukti į paragrafą GROUP BY

SELECT DEPTN,MIN(SAL) FROM EMP; - klaidinga užklausa, nes funkcija MIN yra grupinė, o laukas DEPTNO yra kiekvienam įraše ir tai nesuderinama.

Teisingai atrodytų: 

SELECT DEPTN,MIN(SAL) FROM EMP GROUP BY DEPTNO;

Dabar DEPTNO jau yra grupės pavadinimas ir nebėra individualus laukas.

Gali būti grupuojami duomenys, pateikiant juos kaip grupę grupėje. Naudojant grupines funkcijas yra galimybė išjungti arba įjungti iš rezultato atskiras duomenų grupes. Tam galime naudoti paragrafą HAVING, kuris panaudojamas po GROUP BY.

Turi tokią pačią prasmę kaip ir operatorius WHERE, tačiau kuris yra negalimas grupinėse funkcijose.

Bendru atveju sintaksė būtų tokia

SELECT stulpelis, grupinė_funkcija

FROM lentelė

[WHERE Sąlyga]

[GROUP BY sąrašas_grupavimui]

[HAVING grupės sąlyga]

[ORDER BY rūšiavimo sąlyga]

SELECT Job,Sum(Sal) Benbra_Suma FROM EMP

WHERE Job NOT LIKE ‘SALES%’

GROUP BY Job

HAVING Sum(Sal)>5000

ORDER BY Sum(Sal)

Rūšiuoja pagal gautą sumą grupėse. Bendra_Suma formuoja naują stulpelio antraštę

Skaičiuoja visiems darbuotojams, išskyrus “SALES”

Norint parodyti vidutinį atlyginimą darbuotojams, pagal sąlygą, jog mus domina tik departamentai, kuriuose dirba daugiau nei trys žmonės, formuosime užklausą:

SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING COUNT(*)>3;

Norint parodyti tik darbus, kurių didžiausias atlyginimas yra >=$3000, formuosim užklausą:

SELECT JOB,MAX(SAL) FROM EMP HAVING GROUP BY JOB MAX(SAL)>=3000;

Nesant grupavimo, geriau atrankos sąlygai užduoti naudoti paragrafą WHERE.

Žemiau pateikiamas pavyzdys yra neteisingas, nes grupinei funkcija naudojama poroje su

paragrafu WHERE

SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO WHERE AVG(SAL)>2000;

Teisingai būtų taip:

SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)>2000;

SELECT Max(Avg(Sal)) FROM EMP GROUP BY Deptno;

Pirma suskaičiuoja atlyginimo vidurkį departamente, o po to išrenka didžiausią

vidurkį tarp departamentų.

Įvadas į DBVS ir SQL kalbą

Duomenų bazės, DBVS

Duomenų bazė (DB) - tarpusavyje susijusių duomenų rinkinys, kuriems apdoroti pasitelkiamas kompiuteris. Tokia DB yra panaši į elektroninę kartoteką.

Panašios DB vartotojai atlieka operacijas:

• naujo įrašo (naujos kortelės) įtraukimas;
• esamo įrašo (kortelės duomenų) keitimas;
• esamo įrašo (kortelės) šalinimas;
• duomenų paieška duomenų bazėje (kartotekoje).

Kompiuterinė sistema, kurioje atliekama panašios operacijos, vadinama duomenų bazės sistema (DBS).

PĮ, suteikianti DB vartotojui galimybę dirbti su DB neatsižvelgiant į technines detales, vadinama duomenų bazių valdymo sistema (DBVS).

DBVS leidžia vartotojui žiūrėti į DB kaip į žymiai aukštesnio lygio objektą, negu į įrašų bylą – tai pagrindinė paskirtis.

DBVS atlieka vartotojo sąsajos (interfeiso) su DB vaidmenį.

Vartotojai operuoja DB naudojant formalią kalbą, pvz. SQL.

 Reliacinės (sąryšinės) ir kitokios DB

DBVS suteikia vartotojui galimybę vartoti logines sąvokas. Sąvokų rinkinys ir jų vartojimo taisyklės - modelis. Dauguma DBVS naudoja reliacinį duomenų modelį – 1970 m. E.F. Kodas (E.F. Codd), IBM. DB, kurios apibrėžiamos naudojant reliacinį modelį, vadinamos reliacinėmis duomenų bazėmis (RDB). Atitinkamos jų valdymo sistemos - reliacinėmis duomenų bazių valdymo sistemomis (RDBVS).

Pagrindinės reliacinio modelio sąvokos:

• lentelė(table)
• eilutė (row)
• stulpelis (column) 

Reliacinė sistema remiasi šiais pagrindiniais principais: 

1. duomenys vartotojui pateikiami lentelėmis ir niekaip kitaip; 
2. vartotojui pateikiami operatoriai, leidžiantys iš esamų lentelių gauti naujas.

RDB - tai DB, kurią vartotojai priima kaip lentelių rinkinį.

• Lentelės eilutė aprašo vieną objektą.
• Stulpelis - objekto charakteristiką.
• Eilutės ir stulpelio sankirta – reikšmė (laukas) (value).

Šiuo metu vienos labiausiai paplitusių ir išvystytų RDBVS:

• IBM DBVS DB2
• Oracle DBVS ORACLE
• Microsoft DBVS SQL Server
• Sybase Inc. DBVS – SYBASE
• PostgreSQL – atvirojo kodo
• MySQL – atvirojo kodo

SQL

Vartotojo sąsajai su DB (užklausoms formuluoti) yra naudojama formalizuota kalba. 

Reliacinėse sistemose - SQL (Structured Query Language) SQL sukurta IBM 1970-jų m. pr. 1-oji realizacija - RDBVS IBM System R. 1986 – SQL-86 (1-as ANSI/ISO standartas) 1992 – SQL-92 (SQL2) 1999 – SQL:1999 (SQL3) 2008 – SQL:2008

SQL yra neprocedūrinė (deklaratyvi) programavimo kalba. Kalboje išskiriama trys sakinių grupės:

• duomenų apibrėžimo sakiniai (kalba) (DDL, Data Definition Language),
• manipuliavimo duomenimis sakiniai (DML, Data Manipulation Language),
• duomenų valdymo sakiniai (DCL, Data Control Language). SQL = DDL + DML + DCL

DDL sakiniais kuriamos DB ir lentelės. Kuriant DB yra nurodoma jos vardas (pavadinimas), fizinė vieta ir kt. Kuriant lentelę - lentelės vardas, stulpelių vardai ir jų tipai.

SQL duomenų rūšys: tekstiniai duomenys, skaičiai, dvejetainiai duomenys, datos ir laikai. 

Kiekviena rūšis su fizinėmis realizacijos savybėmis yra duomenų tipas.

Kiekviename duomenų tipe yra spec. reikšmė NULL, žyminti, kad duomenys yra nežinomi, dar neįvesti, jau pašalinti...

DML sakiniais formuojamos užklausos (query) duomenims surasti, šalinti ir įterpti.

DCL sakiniais koordinuojamas vartotojų darbas su DB.

Duomenų išrinkimas

SELECT sakinys naudojamas įrašams iš vienos ar daugiau lentelų atrinkti. FROM sakinyje nurodomos lentelės, iš kurių reikia išrinkti eilutes (sąryšiai gali būti nurodomi skirtingais JOIN variantais). WHERE sakinyje nurodoma sąlyga, kurią turi tenkinti grąžinamos eilutės. GROUP BY sąlygoje nurodoma, kad reikia grupuoti tam tikras eilutes. Grupuojant eilutes, dažniausiai naudojamos agregatinės funkcijos maksimalioms, vidutinėms ir panašioms reikšmėms išrinkti iš grupuotų eilučių. ORDER BY sakiniu nurodoma viena ar daugiau rikiavimo sąlygų. HAVING sakinyje nurodomas kriterijus, taikomas grupuojamoms eilutėms; šis raktinis žodis gali būti naudojamas tik tais atvejais, jeigu užklausoje yra GROUP BY sakinys.

Duomenų valdymas

INSERT vartojamas naujų įrašų įterpimui į lentelę. 

DELETE leidžia ištrinti įrašus iš lentelės. 

UPDATE naudojamas pakeisti vieno ar daugiau įrašų reikšmes.

SQL Select sakinys

Duomenų atrinkimui iš duomenų bazės naudojamas SELECT sakinys. 

Rezultatai grąžinami lentelės pavidalu, kuri vadinama “Result-Set”.

SELECT sakinio sintaksė:

SELECT laukai FROM lentelės_pavadinimas; 

SELECT * FROM lentelės_pavadinimas;

Duomenų išrinkimas panaudojant DISTINCT.

DISTINCT - skirtingų reikšmių pasirinkimas Jei žinote, kad jūsų sakinys pasirinks perteklinius duomenis, ir norite matyti tik skirtingas reikšmes, savo sąlygoje SELECT galite naudoti raktinį žodį DISTINCT. 

Pavyzdžiui, tarkime, kad kiekvienas iš jūsų klientų atstovauja keliems skirtingiems interesams, ir kai kurie iš jų naudoja tą patį telefono numerį. Norėdami užtikrinti, kad kiekvieną telefono numerį matytumėte tik kartą, jūsų sąlyga SELECT atrodys taip:

SELECT DISTINCT telefonoNumeris FROM klientai

Duomenų išrinkimas panaudojant WHERE.

Duomenų išrinkimui pagal užduotą sąlygą formuojama užklausa, naudojant paragrafą WHERE. Sąlygoje gali būti nurodyti stulpelių vardai, kuriems yra taikoma sąlyga ir ši sąlyga yra tikrinama kiekvienai lentelės eilutei, formuojant užklausos rezultatą.

WHERE - stulpelio vardas; sąlyga; kito stulpelio vardas arba konstanta ar reikšmių sąrašas taip formuojama sąlyga duomenų atrinkimui.

SELECT Ename,Job,Deptno FROM EMP WHERE Job=’CLERK’;

Pateiks rezultate tik darbuotojus su nurodyta specialybe. Sąlygoje svarbu pateikti lyginimui skirtus duomenis reikiamam formate (dešinėj pusėj raidžių dydis, datos formatas ir kt.)

DML-DARBAS SU DUOMENIMIS.

Duomenų manipuliavimo komandos leidžia įterpti duomenis į duomenų bazę arba pašalinti juos iš ten. Be to galima duomenis pataisyti ir ten pat juos palikti. Yra trys pagrindinės operacijos su duomenimis, kuriuos gali atlikti vartotojas, jei tam jam suteikti įgaliojimai.

• INSERT
• UPDATE
• DELETE

UPDATE leidžia pakeisti duomenis lentelėje.

UPDATE Lentelės vardas SET Stulpelis1=reikšmė1[,Stulpelis2=reikšmė2 ...] [WHERE sąlyga];

Galima iš karto atnaujinti kelias eilutes pagal nurodytą sąlygą. Stulpelis1, Stulpelis2... Stulpelių vardai, kurių reikšmes reikia pakeisti naujomis Reikšmė1, Reikšmė2 atitinkamos naujos stulpelių reikšmės. WHERE užduoda sąlygą, kuriai esant patenkintai tos eilutės yra atnaujinamos. 

Sąlyga yra nebūtina, bet jei ji nenurodyta, pakeitimas atliekamas visoms eilutėms lentelėje.

UPDATE Emp SET DEPTNO=20 WHERE EMPNO=7782; 

Bus pakeista stulpelio DEPTNO reikšmė eilutei, kur EMPNO=7782, t.y. darbuotojas pervedamas į kitą departamentą.

UPDATE Emp SET DEPTNO=20; 

Visoms eilutėms lentelėje Emp pakeičiamas departamento numeris. Visi darbuotojai taps to paties departamento pareigūnais.

INSERT leidžia įterpti vieną eilutę į lentelę.
INSERT INTO Lentelės_vardas[(stulpelis1[,stulpelis2, ...])] VALUES(reikšmė1[, reikšmė2, ...]);

Komanda įterps reikšmę1 į stulpelį1, reikšmę2 į stulpelį2 ir t.t. Stulpelių sąrašas yra nebūtinas, bet jei sąrašas nenurodytas, tai komanda nebus užbaigta tol, kol nebus įvesta tiek duomenų, kiek numatyta lentelės struktūroje. Simboliniai duomenys ir datos tipo duomenys visada nurodomi tarp viengubų kabučių.

INSERT INTO Dept(Deptno,Dname,Loc) VALUES(50,’DEVELOPMENT’,’DETROIT’); 

Galima įvedimo metu taip pat suformuoti ir tuščias laukų reikšmes, Dėl to reikia norimą palikti neužpildytą stulpelį neįtraukti į sąrašą.

INSERT INTO Dept(Deptno,Dname) VALUES(60,’MIS’); 

Tą patį galima padaryti ir kitaip

INSERT INTO Dept VALUES(70,’FINANCE’,NUL);

DELETE galima pašalinti įrašus iš lentelės.

DELETE FROM Lentelės_vardas [WHERE sąlyga]; Iš lentelės nurodytu vardu bus pašalintas įrašas, kuris patenkina sąlygą.

DELETE FROM Dept WHERE Dname=’DEVELOPMENT’; 

Jei sąlyga nenurodyta, panaikinamos visos eilutės.

DELETE FROM Dept;

Kaip paprastai įrašų panaikinimui galima pasinaudoti užklausa pateikiančia duomenis iš kitos lentelės

DELETE FROM EMPLOE WHERE Deptno=(SELECT Deptno FROM Dept WHERE Dname=’SALES’);
Iš lentelės EMPLYE pašalins visus darbuotojus, kurių pareigos nurodytos sąlygoje.
Pašalinti visus įrašus galima taip pat ir su komanda

• TRUNCATE TABLE lentelės_vardas; Ši komanda įrašus šalina fiziškai ir atstatyti jų neįmanoma.
• DROP pašalina viską: duomenis kartu su struktūra.
• DROP TABLE lentelės_vardas;