5.2. Datubāzes un SQL

Ievads

Mērķi

  • Aprakstīt relāciju datubāzu vispārīgās īpašības
  • Aprakstīt Codd'a 3 likumus relāciju datubāzu projektēšanai
  • Saprast, kas ir konekcija ar datubāzi
  • Veidot SQL pieprasījumus
  • Izvilkt informāciju no datubāzes ar dialogveida rīku
  • Raksturot JDBC
  • Izskaidrot, kā izmantojot JDBC veidoto abstrakcijas slāni var datubāzes piekļuves fasādi (front-end) portēt uz citu platformu
  • Aprakstīt 5 galvenos uzdevumus, kuri saistīti ar JDBC programmētāja interfeisu
  • Aprakstīt prasības JDBC draiverim un tā attiecības ar JDBC draiveru pārvaldnieku

Ievadjautājumi

  • Relāciju datubāzes ir visuresošas IS izstrādē. Programmatūras un datubāzu integrācija ļauj nodrošināt jebkādas informācijas pastāvīgu saglabāšanu
  • Kādus informācijas veidus programmēšanas projekts padara par pastāvīgiem (persistent) - t.i. tie dzīvo arī pēc aplikācijas darba beigām.
  • Kā pieslēgties datubāzei gan ar dialogveida rīku, gan no Javas programmas? Kā Java izmanto konkrētās datubāzes piegādātāja interfeisus (API)?
  • Kādi ir galvenie JDBC abstrakcijas slāņa jēdzieni?
  • Kādas problēmas varētu rasties, pārejot no vienas datubāzu sistēmas uz citu? Kāds tam sakars ar JDBC?

Relāciju datubāzu sistēmas

  • Uzdevums - informācijas pārvaldība (information management)
  • E.F.Codd'u uzskata par relāciju DB modeļa izgudrotāju, kurš minējis 12-daļīgu pārbaudi, vai sistēmu var uzskatīt par relāciju datubāzu sistēmu (RDBMS), sk. "The Practical SQL Handbook", Emerson, et al. 1989.

Relāciju datubāzu sistēmas kritēriji

  • Reprezentē visu datubāzē esošo informāciju tabulu veidā
  • Datu loģiskā reprezentācija ir neatkarīga no to fiziskās glabāšanas parametriem
  • Datu strukturēšanai, pieprasījumu veidošanai un mainīšanai lieto augsta līmeņa valodu
  • Atbalsta relāciju operācijas ("selektus" un "džoinus") un kopu operācijas (piemēram, kopu šķēlumu un starpību)
  • Atbalsta skatus (view), kuri dod alternatīvu datu meklēšanas iespēju tabulās
  • Dod iespēju atšķirt nezināmas vērtības no "null", "0" un tukšām vērtībām, kuras ir zināmas
  • Atbalsta mehānismus integritātei, autorizācijai, transakcijām un atkopšanai (recovery)

Tabulas

Pirmais Codd'a kritērijs - datiem jāglabājas tabulās. Tabulām ir rindiņas un kolonnas. Palielinot ierakstu skaitu, pievienojas jaunas rindas. Piemērs "Adresu tabula"

VārdsAdreseTelefons
PūksAugšiela 107349576
SivēnsParka iela 377234348

Rindas sauc arī par ierakstiem (records) un kolonnas - par laukiem (fields). Vienu no laukiem var pasludināt par primāro atslēgu (primary key), kurš unikāli identificē ierakstu.

Datu neatkarība

Otrais Codd'a kritērijs norāda, ka datu loģiskais un fiziskais glabāšanas veids eksistē neatkarīgi viens no otra.

  • Lietotājs uzskata datubāzu sistēmu kā loģisku datu reprezentāciju
  • Lietotājam nav jāzin, kā dati tiek glabāti

Lietotāju neinteresē tas, kā mašīnas atmiņā glabājas datubāzes dati. Daudzas relāciju datubāzu sistēmas, piemēram, izmanto B-kokus - īpašas kokveida meklēšanai piemērotas datu struktūras, kurās katrai B-koka virsotnei atbilst viena "lappuse uz diska" - informācijas gabals - daži kilobaiti, kuru var sevišķi efektīvi ielādēt operatīvajā atmiņā. Lietotājs par to var nezināt un sekmīgi programmēt šo datubāzi.

Pieprasījumu valoda

Trešais Codd'a kritērijs datubāzu sistēmai - visaptveroša, augsta līmeņa valoda, lai sazinātos ar datubāzi.

  • Valoda SQL (Structured Query Language) - visizplatītākā šāda valoda
  • To veidoja IBM, ap 1981 piedāvājot pirmo produktu: SQL/DS
  • Nav standartizēta; ap 1988 veidojās ISO-ANSI standarta projekts SQLam, bet tas nav ieviesies kā standarts
  • SQLam ir "mazākais kopsaucējs" - vienkāršākās datu manipulācijas komandas
  • Oracle un Sybase dialekti ir pamatīgi papildināti ar citām komandām

SQL komandas

SELECT
Atlasa 0 vai vairāk ierakstus no nosauktās tabulas (vai vairākām tabulām)
INSERT
Pievieno jaunu ierakstu tabulai
DELETE
Izdzēš vienu vai vairākus ierakstus no tabulas
UPDATE
Modificē vienu vai vairākus laukus norādītajos ierakstos
CREATE
Izveido jaunu, tukšu tabulu ar norādītajiem lauku vārdiem un tipiem
DROP
Izdzēš tabulu no datubāzes

Komanda SELECT

Šī komanda atbalsta šādas iespējas:

  • Sastiķējumus (joins)
  • DISTINCT - tikai atšķirīgu rindu atlasi
  • ORDER BY - sakārtošanas kritērijus
  • Regulāras izteiksmes: LIKE 'SIVĒN%'
  • Dažādu kolonnu salīdzināšanu WHERE apakšizteiksmēs

SELECT formālā sintakse:

SELECT [table.]column [ , [table.]column  ... ]
FROM table [ , table ...]
[ WHERE [table.]column OPERATOR VALUE
[ AND | OR [table.]column OPERATOR VALUE] ... ]
[ ORDER BY [table.]column [DESC] [, [table.]column [DESC] ]
  • OPERATOR var būt <, >, =, <=, >=, <>, LIKE
  • VALUE var būt literāļa vērtība vai kolonnas nosaukums

SELECT piemēri

SELECT first_name, last_name FROM emp_details 
    WHERE dept = 'finance'

SELECT first_name, last-name FROM emp_details
    WHERE dept = 'finance'
        ORDER BY last_name, first_name DESC

SELECT DISTINCT first_name, last_name FROM emp_details
    WHERE dept = 'finance'
        ORDER BY last_name, first_name DESC

SELECT first_name, last_name FROM emp_details
    WHERE dept = 'finance' AND last_name LIKE '_ughes'

SELECT emp_details.first_name, epm_details.last_name, 
        project_details.project
    FROM emp_details, project_details
        WHERE emp_details.emp_id = project_details.emp_id
        ORDER BY emp_details.last_name, emp_details.first_name

Komanda INSERT

Norādām kolonnu jeb lauku vārdu sarakstu un atbilstoši tam - jaunā ieraksta vērtības. Dažreiz SQL sintakse ļauj kolonnu vārdus nerakstīt, ja vērtību skaits precīzi atbilst kolonnu skaitam un secībai, bet tas ir ļoti slikts stils.

INSERT INTO table_name ( column [ , column ]... )
VALUES (value [, value]...)

Piemērs:

INSERT INTO emp_details ( first_name, last_name, dept, salary)
    VALUES ('David', "Hughes', 'I.T.S', '12345')

Komanda DELETE

DELETE FROM table_name
    WHERE column OPERATOR VALUE
        [ AND | OR column OPERATOR value ] ...

Piemērs:

DELETE FROM emp_details WHERE emp_id = '12345'

Komanda UPDATE

UPDATE table_name SET column=value [ , column=value ...]
    WHERE column OPERATOR VALUE
        [AND | OR column OPERATOR value ] ...

Piemērs:

UPDATE emp_details SET salary=300 WHERE emp_id = '1234'

Šai komandai ir jānorāda konkrētas literāļu vērtības, kuras ir jāieraksta datubāzē (nevar norādīt kolonnu nosaukumus).