Duomenų bazės ir SQL: praktiniai klausimai su paaiškinimais

1Kas yra reliacinė duomenų bazė

Reliacinė duomenų bazė saugo duomenis lentelėse. Lentelė atrodo kaip skaičiuoklė: kiekviena eilutė yra vienas įrašas, kiekvienas stulpelis – viena savybė.

Parduotuvė gali turėti lentelę customers su stulpeliais id, name ir city bei lentelę orders su stulpeliais id, customer_id ir total.

Pirminis raktas yra stulpelis ar stulpelių rinkinys, vienareikšmiškai identifikuojantis kiekvieną eilutę. Jis niekada negali kartotis ir niekada negali būti tuščias. Čia tai kiekvienos lentelės id.

Išorinis raktas yra stulpelis, nurodantis kitos lentelės pirminį raktą. Lentelėje orders customer_id nurodo, kuris klientas pateikė užsakymą.

Taip lentelės susiejamos: vienas klientas gali turėti daug užsakymų, bet kiekvienas užsakymas priklauso vienam klientui. Tai vienas su daug ryšys.

SQL (Structured Query Language) yra kalba, kuria kuriate lenteles, keičiate duomenis ir klausiate apie juos.

2Duomenų skaitymas su SELECT

Dauguma klausimų yra apie SELECT sakinį. Jis sudarytas iš sakinių dalių, visada rašomų šia tvarka:

• SELECT išvardija norimus stulpelius. DISTINCT pašalina pasikartojančias eilutes.
• FROM nurodo lentelę arba kartu sujungtas lenteles.
• WHERE palieka tik eilutes, atitinkančias sąlygą, pavyzdžiui, WHERE total > 20.
• GROUP BY sudeda tą pačią reikšmę turinčias eilutes į vieną grupę, kad galėtumėte skaičiuoti ar sumuoti pagal grupę.
• HAVING palieka tik grupes, atitinkančias sąlygą. Jis gali naudoti agregatus, o WHERE – ne.
• ORDER BY rūšiuoja rezultatą, pagal numatymą didėjimo tvarka; pridėkite DESC, kad didžiausi būtų pirmi.

Agregatinės funkcijos apibendrina daug eilučių į vieną reikšmę: COUNT, SUM, AVG, MIN ir MAX.

Duomenų bazė sakinio dalių nevykdo ta tvarka, kuria jas rašote. Ji pirmiausia vykdo FROM, tada WHERE, GROUP BY, HAVING, SELECT ir galiausiai ORDER BY.

Kiti sakiniai keičia duomenis: INSERT prideda eilutes, UPDATE keičia eilutes, o DELETE pašalina eilutes. CREATE, ALTER ir DROP keičia pačias lenteles.

3Sujungimai, normalizavimas ir transakcijos

Sujungimas (join) sujungia dviejų lentelių eilutes pagal sąlygą, paprastai išorinis raktas = pirminis raktas:

• INNER JOIN (arba JOIN) palieka tik eilutes, turinčias atitikmenį abiejose lentelėse.
• LEFT JOIN palieka kiekvieną kairiosios lentelės eilutę. Kur atitikmens nėra, dešiniosios lentelės stulpeliai yra NULL.
• NULL reiškia, kad reikšmės nėra arba ji nežinoma. Tikrinkite su IS NULL, niekada su = NULL.

Normalizavimas suskaido lenteles, kad kiekvienas faktas būtų saugomas vieną kartą. Taip išvengiama to paties fakto atnaujinimo keliose vietose.

• 1NF: kiekvienas langelis turi vieną atominę reikšmę, be pasikartojančių grupių.
• 2NF: 1NF, ir kiekvienas ne rakto stulpelis priklauso nuo viso rakto, o ne nuo sudėtinio rakto dalies.
• 3NF: 2NF, ir joks ne rakto stulpelis nepriklauso nuo kito ne rakto stulpelio.

Transakcija sugrupuoja sakinius į vieną vienetą. COMMIT padaro ją nuolatinę, o ROLLBACK ją atšaukia. Transakcijos laikosi ACID: atomiškumas (viskas arba nieko), nuoseklumas, izoliuotumas ir patvarumas.

4Metodas žingsnis po žingsnio

Norėdami parašyti užklausą arba išsiaiškinti, ką grąžina duota užklausa:

1. Raskite lenteles, kuriose saugomi duomenys, ir kaip jos susietos raktais.
2. Parašykite FROM su sujungimais. Rinkitės LEFT JOIN, kai eilutės be atitikmens turi likti.
3. Pridėkite WHERE sąlygoms atskiroms eilutėms.
4. Jei klausime yra „kiekvienam“ ar „pagal“, pridėkite GROUP BY ir agregatą.
5. Pridėkite HAVING agregato sąlygoms, tada ORDER BY, jei svarbi tvarka.
6. Patikrinkite rezultatą nedideliame pavyzdyje: išvardykite eilutes po kiekvienos sakinio dalies tokia tvarka, kokia jas vykdo duomenų bazė.

Teorijos klausimui pirmiausia įvardykite sąvoką (raktas, sujungimo tipas, normalinė forma, ACID savybė), tada pasirinkite variantą, atitinkantį jos apibrėžimą.

5Išspręstas pavyzdys

Sekite kiekvieną žingsnį. Pabandykite nuspėti kitą, prieš jį atidarydami.

6Dažnos klaidos

• Agregatas naudojamas WHERE. COUNT, SUM ar AVG sąlygos priklauso HAVING.
• Rašoma = NULL. Bet koks palyginimas su NULL yra nežinomas; naudokite IS NULL arba IS NOT NULL.
• Naudojamas INNER JOIN, kai eilutės be atitikmens turi likti. Naudokite LEFT JOIN.
• Pamirštama sujungimo sąlyga. Be ON kiekviena eilutė suporuojama su kiekviena eilute (kryžminis sujungimas).
• Parenkamas stulpelis, kurio nėra nei GROUP BY, nei agregate.
• Painiojami DELETE (pašalina eilutes) ir DROP TABLE (pašalina visą lentelę).
• UPDATE arba DELETE vykdomas be WHERE. Jis pakeičia kiekvieną lentelės eilutę.

7Kaip atrodo egzamino uždaviniai

• Lengvi: pagrindinės sąvokos ir faktai. Ką daro SELECT, WHERE, ORDER BY ar DELETE, pirminiai ir išoriniai raktai, ką reiškia NULL.
• Vidutiniai: sąvokų taikymas. Tinkama užklausa užduočiai, WHERE ir HAVING skirtumas, vidiniai ir išoriniai sujungimai, normalinės formos, ACID.
• Sunkūs: atvejai ir analizė. Vidinės užklausos, izoliacijos lygiai ir jų anomalijos, BCNF, langų funkcijos, indeksai ir užklausų našumas, SQL injekcija.

Kai duota užklausa, atsekite ją keliose eilutėse tokia tvarka: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. Dauguma spąstų paaiškėja per tris eilutes.