SQL

Kako do podatkov iz baze

• Vsebina tabel je uporabniku dostopna s pomočjo poizvedovalnih jezikov.
• Uveljavil se je standardni jezik SQL (Structured Query Language, strukturirani poizvedovalni jezik).
• Enostavno berljiv:

  SELECT tecajnica.simbol, vp.opis, tecajnica.eTecaj
    FROM tecajnica LEFT JOIN vp
         ON tecajnica.simbol = vp.simbol
   WHERE tecajnica.simbol LIKE 'Z%' AND 
         tecajnica.datum = '2004-02-26';
  

SELECT

• Najpogosteje uporabljen ukaz
• Povpraševanje po podatkih
• Rezultat je tabela
  • SELECT torej vrne tabelo!
• Če ukaz uporabljamo v konzoli, se ta tabela izpiše.

SELECT - osnovna struktura

SELECT seznam stolpcev, ki jih želimo videti
FROM seznam tabel, kjer bomo podatke našli
WHERE pogoji, ki določajo, katere podatke želimo videti

SELECT * FROM knjige 
 WHERE cena > 100.00
 ORDER BY naslov;

SELECT * FROM tabela

• *
  • Vrni vse stolpce
  • V izhodu naj bodo vsi stolpci izvorne tabele
• tabela
  • Ime tabele, katere stolpce želimo dobiti
• Zgledi z http://ucisesql.fmf.uni-lj.si

  SELECT * FROM drzave;
  

SQLiteStudio

SELECT s1, s2 FROM tabPod

• s1, s2
  • V izhodno tabelo uvrsti stolpca s1 in s2 iz tabele tabPod
• Primer (https://sqlzoo.net/wiki/SELECT_basics):
  • Vrni ime in število prebivalcev za vsako posamezno državo

    SELECT name, population FROM world;
    
    SELECT population, name FROM world;
    

SELECT s1, s2 FROM tabPod (2)

• Kaj, če v izvorni tabeli ni stolpca s tem imenom?

  • Dobimo napako.

• AS: poimenovanje stolpca v izhodu

  SELECT name AS drzava, population AS st_prebivalcev FROM world;
  
  SELECT name AS "Ime države" FROM world;
  

SELECT DISTINCT

• V izhodno tabelo uvrsti le med seboj različne vrstice.
• Primer: vrni tabelo celin, ki nastopajo v tabeli.

  SELECT DISTINCT continent FROM world;
  

• Kaj vrneta sledeči poizvedbi?

  SELECT leto FROM film;
  
  SELECT DISTINCT leto FROM film;
  

Poizvedba po določenih podatkih

Izrazi

• Kakšna je gostota prebivalstva vseh evropskih držav?

  SELECT name, population / area 
    FROM world
   WHERE continent = 'Europe';
  
  SELECT name AS ime_drzave,
         population / area AS gostota_prebivalstva
    FROM world
   WHERE continent = 'Europe';
  

• Zaokroži na dve decimalki in upoštevaj le države z več kot 2M prebivalstva.

  SELECT name AS "ime države", 
         ROUND(population / area, 2) AS "gostota prebivalstva" 
    FROM world
   WHERE continent = 'Europe' AND population > 2000000;
  

Urejanje

SELECT - primer

SELECT name, ROUND(population/1000000) AS prebMilijoni
  FROM world
 WHERE continent IN ('Asia', 'Europe') AND
       name LIKE 'C%';

Zahtevnejše poizvedbe

• Izpiši imena držav, ki imajo manj prebivalcev kot Slovenija.
• Lahko gremo po korakih
  • Najprej ugotovimo, koliko prebivalcev ima Slovenija

    SELECT population FROM world WHERE name = 'Slovenia';
    

    • Dobimo rezultat 2063151
  • Uporabimo v ustrezni poizvedbi

    SELECT name FROM world WHERE population <= 2063151;
    

S podpoizvedbami

• Kot vrednost v izrazih lahko uporabimo tudi rezultate ukaza SELECT

  SELECT name FROM world
   WHERE population <= (
           SELECT population FROM world  
            WHERE name = 'Slovenia'
         );
  

  • Izračunamo tabelo s stolpcem population, kjer upoštevamo le tiste vrstice, kjer je ime države Slovenia.
  • Ker ima ta tabela le eno vrstico, jo lahko uporabimo kot vrednost v zunanji poizvedbi.
• Ali bomo dobili tudi Slovenijo?

Omejitve

• Podpoizvedbe naj bi vračale le en stolpec z eno vrednostjo! Drugače pride do napake.
  • Npr. če bi v našem primeru imeli dve ali več držav z imenom Canada.
• Z nekaterimi operatorji lahko podpoizvedba vrača tudi več vrstic.
  • e [NOT] IN (SELECT ... ) - ali je (ni) vrednost e ena izmed vrednosti v stolpcu
  • e < ALL (SELECT ...) - ali je vrednost e manjša od vseh vrednosti v stolpcu
  • e >= ANY (SELECT ...) - ali je vrednost e večja ali enaka vsaj eni vrednosti v stolpcu
  • [NOT] EXISTS (SELECT ...) - ali podpoizvedba vrne vsaj eno vrstico (oz. nobene)

Primer

WITH

Združevalne funkcije

• SUM(stolpec): vsota vrednosti v stolpcu
  • Koliko "proizvedemo" v Evropi:

    SELECT SUM(gdp) FROM world
     WHERE continent = 'Europe';
    

• AVG(stolpec): povprečna vrednost v stolpcu
  • Povprečno število prebivalcev v Evropi:

    SELECT AVG(population) FROM world
     WHERE continent = 'Europe';
    

Združevalne funkcije (2)

• MIN(stolpec): minimalna vrednost v stolpcu
  • Najmanjša površina, ki jo ima neka država v tabeli:

    SELECT MIN(area) FROM world;
    

• MAX(stolpec): maksimalna vrednost v stolpcu
  • Največje število prebivalcev izmed držav v Afriki:

    SELECT MAX(population) FROM world
     WHERE continent = 'Africa';
    

• Funkcije SUM, AVG, MIN, MAX upoštevajo le vrednosti, ki niso NULL!

Združevalne funkcije (3)

• COUNT(stolpec): število vrstic z neničelno vrednostjo v stolpcu
  • Število filmov z določeno oznako (podatek je v tabeli):

    SELECT COUNT(oznaka) FROM film;
    

• COUNT(DISTINCT stolpec): število različnih vrednosti v stolpcu
  • Število različnih oznak, ki se pojavijo:

    SELECT COUNT(DISTINCT oznaka) FROM film;
    

• COUNT(*): število vrstic (zapisov)
  • Število ameriških držav v tabeli (J. in S. Amerika):

    SELECT COUNT(*) FROM world
     WHERE continent LIKE '%America%';
    

Zgled

• Izpišimo seznam afriških držav, katerih ime se začne na S, je sestavljeno le iz ene besede in ni Senegal.

  SELECT * FROM world
   WHERE name LIKE 'S%' AND
         continent = 'Africa' AND
         name NOT LIKE '% %' AND
         name <> 'Senegal';
  

• Kakšen je povprečni GDP teh držav?

  SELECT AVG(gdp) FROM world
   WHERE name LIKE 'S%' AND
         continent = 'Africa' AND
         name NOT LIKE '% %' AND
         name <> 'Senegal';
  

Združevalne funkcije in težave

• Zanima nas naslov filma z najkrajšim naslovom.

  SELECT naslov, MIN(LENGTH(naslov)) FROM film;
  

  • Dobimo eno vrstico.
• Je to prav? Je to edini tak film?

  SELECT naslov, LENGTH(naslov) AS dolzina FROM film
  ORDER BY dolzina;
  

• Prejšnja poizvedba ni v skladu s standardom SQL!
  • Čeprav jo SQLite dovoli ...

Združevalne funkcije in težave (2)

• Poskusimo podobno na SQLZoo.

  SELECT name, MAX(population) FROM world
   WHERE continent = 'Africa';
  

• Dobimo napako, npr.

  Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no
  GROUP columns is illegal if there is no GROUP BY clause
  

• Kaj pravzaprav zahtevamo od RDBMS?
  • Dobiti želimo vsa imena afriških držav
  • in še največje prebivalstvo med njimi.
  • To ne bo šlo, saj želimo imeti en stolpec z več vrsticami in enega z eno samo!

Združevalne funkcije in težave (3)

• Kaj pa takole?

  SELECT name, population FROM world
   WHERE continent = 'Africa' AND
         population = MAX(population);
  

• Združevalnih funkcij ne moremo uporabiti pri WHERE, saj tukaj preverjamo pogoje na eni vrstici naenkrat!

Združevalne funkcije in težave (4)

• Rešitev?
• S podpoizvedbo:

  SELECT name, population FROM world
   WHERE continent = 'Africa' AND
         population = (
           SELECT MAX(population) FROM world
            WHERE continent = 'Africa'
         );
  

Še en zgled

• Poišči imena tistih držav, ki imajo bruto družbeni proizvod večji od katerekoli evropske države.
• Spet uporabimo podpoizvedbo:

  SELECT name FROM world 
   WHERE gdp > (
           SELECT MAX(gdp) FROM world 
            WHERE continent = 'Europe'
         );
  

• Naša poizvedba se bere tako:
  • Poišči imena tistih držav, ki imajo bruto družbeni proizvod večji od največjega GDP evropskih držav.

Še en zgled (2)

• Ali lahko napišemo poizvedbo, ki se bo brala tako, kot smo vprašali?
• Če za relacijskim operatorjem uporabimo določilo ALL, mora biti pogoj izpolnjen za vse rezultate podpoizvedbe.

  SELECT name FROM world 
   WHERE gdp > ALL (
           SELECT gdp FROM world 
            WHERE continent = 'Europe'
         );
  

  • SQLite tega ne podpira!
• Kaj pa, če je med rezultati podpoizvedbe NULL?

Primerjanje z NULL

• Operacije z NULL vračajo NULL!
• Izjema: IS [NOT] NULL:

  SELECT name FROM world 
   WHERE gdp > ALL (
           SELECT gdp FROM world 
            WHERE continent = 'Europe' AND
                  gdp IS NOT NULL
         );
  

In sedaj nekaj zapletenega

• Izpiši ime, celino in število prebivalcev vseh tistih držav, ki so na celini, kjer ima vsaka država manj kot 30M prebivalcev.
• Gremo po delčkih.
  • Osnovni SELECT bo enostaven:

    SELECT name, continent, population FROM world
     WHERE continent IN (
             -- seznam ustreznih celin
           );
    

Kako dobiti seznam celin

• Kako preveriti, ali imajo vse države na določeni celini manj kot 30M prebivalcev?
• Poskusimo z MAX za Evropo.

  SELECT continent FROM world
   WHERE 30000000 > (
           SELECT MAX(population) FROM world
            WHERE continent = 'Europe'
         );
  

• Dobimo prazno tabelo.
  • Prav, saj ima največja evropska država 80M prebivalcev.
  • Za nobeno vrstico pogoj ni izpolnjen.

Ustrezna celina

• "Ročno" preverimo, da je taka celina Oceania.
• Torej bi moralo biti tole v redu za vse države v Oceaniji.

  SELECT continent FROM world
   WHERE 30000000 > (
           SELECT MAX(population) FROM world
            WHERE continent = 'Oceania'
         );
  

• Dobimo ogromno tabelo!
  • Če uporabimo COUNT, vidimo, da dobimo VSE države (195).
  • Za vsako vrstico je pogoj izpolnjen!
• Hočemo, da sta celini "zunaj" in "znotraj" enaki.

Kombiniranje notranje in zunanje poizvedbe

• Poglejmo si to poizvedbo:

  SELECT continent, name, population FROM world AS t1
   WHERE population >= ALL (
           SELECT population FROM world AS t2
            WHERE t1.continent = t2.continent
         );
  

• Kaj pomeni world AS t1?
  • Tabelo world smo preimenovali v t1.
  • Sedaj lahko torej uporabimo ime t1 za sklicevanje na tabelo world iz zunanje poizvedbe.
  • t1.continent: stolpec continent iz tabele t1.

Kombiniranje notranje in zunanje poizvedbe (2)

• Imamo torej dve kopiji tabele world:
  • t1 v zunanji poizvedbi, in
  • t2 v podpoizvedbi.
• Lahko si predstavljamo, da imamo dvojno zanko čez vrstice tabele world:
  • t1 se nanaša na trenutno vrstico v zunanji zanki,
  • t2 se nanaša na trenutno vrstico v notranji zanki.
• Dobimo torej države z največjim številom prebivalcev na vsaki celini.

Ustrezna celina (2)

• Poskusimo torej:

  SELECT continent FROM world AS t1
   WHERE 30000000 > ALL (
           SELECT population FROM world AS t2
            WHERE t1.continent = t2.continent
         );
  

• Dobimo vrstico za vsako državo na ustrezni celini.
  • Lahko bi uporabili DISTINCT, saj nas zanima samo seznam celin.

Končna rešitev

SELECT name, continent, population FROM world
 WHERE continent IN (
         SELECT continent FROM world AS t1
          WHERE 30000000 > ALL (
                  SELECT population FROM world AS t2
                   WHERE t1.continent = t2.continent
                )
       );

• Bi šlo enostavneje?

  SELECT name, continent, population FROM world AS t1
   WHERE 30000000 > ALL (
           SELECT population FROM world AS t2
            WHERE t1.continent = t2.continent
         );
  

Združevalne funkcije in skupine podatkov

• Denimo, da nas zanima maksimalno število prebivalcev države na vsaki celini.
• To lahko naredimo s podpoizvedbo:

  SELECT continent, population FROM world AS t1
   WHERE population >= ALL (
           SELECT population FROM world AS t2
            WHERE t1.continent = t2.continent
         );
  

• Težava: delamo dvojno zanko.
  • Ali bi znali sprogramirati brez gnezdenja zank?

GROUP BY

SELECT ... FROM tabela
...
GROUP BY stolpci;

• Vrstice v tabeli razdelimo v skupine glede na navedene stolpce oziroma izraze.
• Združevalne funkcije potem delujejo za vsako skupino posebej.
  • Poizvedba vrne toliko vrstic, kolikor je skupin.

GROUP BY - primer

SELECT ... FROM donacije;

SELECT ... FROM donacije
 GROUP BY ime;

SELECT ... FROM donacije
 GROUP BY prispevek/100;

Primer

• Maksimalno število prebivalcev države na vsaki celini z GROUP BY:

  SELECT MAX(population) FROM world
   GROUP BY continent;
  

• Zanimajo nas tudi celine, ki jim pripadajo dobljene številke.

  SELECT continent, MAX(population) FROM world
   GROUP BY continent;
  

Izbor stolpcev

SELECT ime, prispevek FROM donacije GROUP BY ime;

SELECT ime, prispevek FROM donacije GROUP BY prispevek / 100;

• Zgornja stavka nista v skladu s standardom SQL!
• Če uporabimo GROUP BY, bo v izhodu ena vrstica za vsako skupino.
• Standard prepoveduje uporabo stolpcev, ki niso navedeni pri GROUP BY, izven združevalnih funkcij.
• Kaj točno se zgodi ob izvedbi zgornjih stavkov, je odvisno od RDBMS.

Napake

SELECT name, continent, MAX(population) FROM world
 GROUP BY continent;

• Zakaj to ni OK?
• SQLZoo vrne

  Expression #1 of SELECT list is not in GROUP BY clause
  and contains nonaggregated column 'gisq.world.name'
  which is not functionally dependent on columns in GROUP BY clause;
  this is incompatible with sql_mode=only_full_group_by
  

GROUP BY in WHERE

GROUP BY in WHERE (2)

SELECT continent, COUNT(*) AS stevilo FROM world 
 WHERE population >= 160000000
 GROUP BY continent;

Filtriranje skupin

• Zanimajo nas tiste celine, na katerih živi vsaj 500M prebivalcev.
• Šlo bi brez uporabe GROUP BY:

  SELECT DISTINCT continent FROM world AS t1
   WHERE 500000000 <= (
           SELECT SUM(population) FROM world AS t2
            WHERE t1.continent = t2.continent
         );
  

  • Spet delamo dvojno zanko!

Filtriranje skupin (2)

• Poskusimo še z GROUP BY.
• Izpišimo najprej prebivalstvo vsake celine.

  SELECT continent, SUM(population) FROM world
   GROUP BY continent;
  

• Ali lahko uporabimo WHERE?
  • Ne - ta deluje pred združevanjem!

GROUP BY in HAVING

• Z določilom HAVING filtriramo skupine, nastale pri združevanju.

  SELECT continent FROM world
   GROUP BY continent
  HAVING SUM(population) >= 500000000;
  

  • Najprej tabelo razdelimo na skupine po celinah.
  • SUM(population) potem sešteje prebivalstvo na posamezni celini.
  • Pogoj pri HAVING preverja vrstice, ki jo dobimo za vsako skupino.
• Pri HAVING smemo uporabiti tiste stolpce/izraze, ki jih lahko ob uporabi GROUP BY izberemo za stolpce dobljene tabele.

Več ključev

• Pri GROUP BY lahko uporabimo tudi več stolpcev (ključev).

  Ime	Starost	Kraj
  Janez	18	Kranj
  Metka	18	Kranj
  Peter	20	Kranj
  Pavel	21	Ljubljana
  Ivana	18	Ljubljana
  Silva	19	Ljubljana
  Srečko	20	Celje

  SELECT ... FROM t GROUP BY starost;       -- 4 skupine
  SELECT ... FROM t GROUP BY kraj;          -- 3 skupine
  SELECT ... FROM t GROUP BY starost, kraj; -- 6 skupin
  

Zgled

• Prikaži tista leta in področja, kjer so bile v istem letu podeljene 3 nagrade ali več. Upoštevaj le leta po letu 2000.
• Skupino sestavlja par (leto, področje).
  • GROUP BY yr, subject
  • ali GROUP BY subject, yr
• Z WHERE yr > 2000 bomo omejili leta.
• S HAVING COUNT(*) >= 3 bomo upoštevali le skupine z vsaj tremi vrsticami.

  SELECT yr, subject FROM nobel
   WHERE yr > 2000
   GROUP BY yr, subject
  HAVING COUNT(*) >= 3
   ORDER BY yr;
  

Stikanje tabel

Stikanje tabel (2)

• Seveda moramo računati na nepopolne podatke ...

SELECT in več tabel

• "Prave" baze torej običajno sestavlja več tabel.

• Pogosto želimo kot rezultat dobiti podatke, ki se nahajajo v več tabelah.

  ID	ImePriimek	Posta
  MK1	Miha Kranjc	1000
  MH1	Maja Hrust	2000
  LS1	Lidija Svet	1000
  MK2	Mitja Kern	3000

  Posta	Kraj
  1000	Ljubljana
  2000	Maribor
  3000	Celje

  ID	ImePriimek	Posta	Kraj
  MK1	Miha Kranjc	1000	Ljubljana
  MH1	Maja Hrust	2000	Maribor
  LS1	Lidija Svet	1000	Ljubljana
  MK2	Mitja Kern	3000	Celje

Kartezični produkt

• Navedemo lahko več tabel:

  SELECT * FROM zaposleni, posta;
  

• Dobimo kartezični produkt, torej zapisov (kjer sta in števili zapisov v prvi in drugi tabeli).
  • V primeru zaposlenih: zapisov!
  • Kot da bi vsak živel v vsakem kraju!
• Včasih je to čisto smiselno!
  • Seznam deklet
  • Seznam fantov
  • Kartezični produkt: možni pari

Sklicevanje na stolpce

• Kako do določenih stolpcev?

  SELECT ImePriimek, Posta, Kraj FROM zaposleni, posta;
  

• Od kod dobimo vrednost stolpca Posta?
  • Če imamo tak stolpec v več tabelah, dobimo napako!
• Navedemo lahko, iz katere tabele naj bo nek stolpec:

  SELECT ImePriimek, posta.Posta, Kraj FROM zaposleni, posta;
  

  • Torej tabela.stolpec.
  • Če ni možnosti za zmedo (ime stolpca se pojavi le v eni od navedenih tabel), lahko ime tabele izpustimo.

Stikanje z omejevanjem

Izbor določenih vrednosti

• Denimo, da nas zanimajo podatki o točno določenem zaposlenem.

  SELECT * FROM zaposleni, posta
   WHERE zaposleni.Posta = posta.Posta AND
         ID = 'MK1';
  

• Pogoji v WHERE:
  • preverjanje ujemanja vrstic (zaposleni.Posta = posta.Posta)
  • vsebinsko filtriranje rezultatov (ID = 'MK1')
• Želimo ločiti filtriranje zaradi vsebine in zaradi stikanja!

Notranji stik (INNER JOIN)

• Zaradi preglednosti lahko ločimo obe vrsti filtriranja:

  SELECT * FROM zaposleni
   INNER JOIN posta
           ON zaposleni.Posta = posta.Posta
   WHERE ID = 'MK1';
  

  • Določilo INNER lahko izpustimo.
• Če obstajajo vrstice, za katere pogoj za stikanje ni nikoli resničen (npr. neke poštne številke zaposlenega ni v tabeli posta), teh vrstic ne bo v izhodu!

Zunanji stik (OUTER JOIN)

• Želimo dobiti tudi vrstice iz prve tabele, ki nimajo para v drugi tabeli (in/ali obratno).

  SELECT * FROM oi
    LEFT OUTER JOIN kraj
                 ON oi.kraj = kraj.kraj;
  

  • LEFT [OUTER] JOIN - ohrani vse vrstice iz prve tabele
  • RIGHT [OUTER] JOIN - ohrani vse vrstice iz druge tabele
  • FULL [OUTER] JOIN - ohrani vse vrstice iz obeh tabel
• Vrstice, za katere pogoj pri ON ni nikoli izpolnjen, se ohranijo, vrednosti v stolpcih iz druge tabele pa dobijo vrednosti NULL.
• Filtriranje z WHERE se zgodi po stikanju (in lahko izloči prej ohranjene vrstice).

Druge možnosti stikanja

• Pogoj pri ON je lahko poljuben (ne nujno ujemanje po stolpcih)!
• Če stikamo po istoimeskih stolpcih, lahko namesto pogoja pri ON naštejemo stolpce z določilom USING:

  SELECT * FROM zaposleni
   INNER JOIN posta USING (Posta);
  

  • Če stikamo po več stolpcih, jih naštejemo med oklepaji, ločene z vejicami.
  • Našteti stolpci se v izhodu ne podvojijo!
• Če stikamo po vseh istoimeskih stolpcih, lahko uporabimo NATURAL JOIN:

  SELECT * FROM oi
  NATURAL LEFT OUTER JOIN kraj;
  

Več tabel

• Baza filmov na SQLZoo
  • movie(id, title, yr, score, votes, director)
  • actor(id, name)
  • casting(movieid, actorid, ord)
• Izpiši naslove filmov, kjer je igral John Wayne.
• Za naslove potrebujemo tabelo movie.
• Imena igralcev so v tabeli actor.
• Tabela casting povezuje igralce in filme.
  • Odnos tipa več-na-več med tabelama movie in actor (igralec je igral v več filmih, filmi pa imajo več igralcev)

Stikanje

SELECT title FROM movie
  JOIN casting ON movie.id = movieid
  JOIN actor   ON actorid = actor.id
 WHERE actor.name = 'John Wayne';

• Najprej sestavimo tabelo s stikanjem, nato jo filtriramo z WHERE.
  • Vzamemo vrstico tabele movie.
  • Staknemo jo s tistimi vrsticami tabele casting, ki imajo vrednost v stolpcu movieid enako vrednosti stolpca id tabele movie.
  • Dobljene vrstice staknemo s tistimi vrsticami tabele actor, ki imajo vrednost v stolpcu id enako vrednosti stolpca actorid tabele casting.
  • Od dobljenih vrstic vzamemo le tiste, kjer je vrednost stolpca name v tabeli actor enaka John Wayne.

Igralci v filmu Velikan

• Zanima nas, kdo je poleg Jamesa Deana še igral v filmu Velikan (Giant).
• Zasedbo dobimo iz tabele casting.
  • Tu so le ID-ji filmov in igralcev!
• Potrebujemo ID filma, poznamo pa le njegov naslov, ki se nahaja v tabeli movie.
• Imena igralcev bomo dobili iz tabele actor.
• Uredili bomo po vrstnem redu igralcev v filmu (casting.ord).

  SELECT name FROM movie
    JOIN casting ON movie.id = movieid
    JOIN actor   ON actor.id = actorid
  WHERE title = 'Giant' AND
        name <> 'James Dean'
  ORDER BY ord;
  

Največje zvezde

• Kateri igralci so bili glavni igralci v vsaj 10 filmih?
• V tabeli casting se pojavijo vsaj desetkrat z ord = 1.
  • WHERE ord = 1
  • HAVING COUNT(*) >= 10
• Imena igralcev bomo dobili iz tabele actor.
• Podatkov o filmih ne bomo potrebovali!

  SELECT name FROM actor
    JOIN casting ON actorid = id
   WHERE ord = 1
   GROUP BY id, name
  HAVING COUNT(*) >= 10;
  

• Kaj pa, če nas zanima tudi, v koliko filmih so bili glavni igralci?

Ustvarjanje baz

CREATE DATABASE baza;

• Moramo imeti ustrezne pravice na RDBMS!
• V SQLite ta korak ni potreben - baza se ustvari kot datoteka.

Ustvarjanje tabel

CREATE TABLE tabela (
  stolpec1 tip
    [PRIMARY KEY]                       -- glavni ključ
    [NOT NULL]                          -- prepovedana vrednost NULL
    [UNIQUE]                            -- enolične vrednosti v stolpcu
    [CHECK (pogoj)]                     -- pogoj, ki mora veljati za stolpec
    [DEFAULT (vrednost)]                -- privzeta vrednost
    [REFERENCES druga_tabela(stolpec)], -- tuji ključ
  [PRIMARY KEY (st1, st2, ...),]        -- glavni ključ iz več stolpcev
  [UNIQUE (st1, st2, ...),]             -- enolične vrednosti v več stolpcih
  [CHECK (pogoj),]                      -- pogoj, ki vključuje več stolpcev
  [FOREIGN KEY (st1, st2, ...)          -- tuji ključ iz več stolpcev
     REFERENCES druga_tabela(s1, s2, ...),]
  ...
);

• Različni RDBMS poznajo še druga določila, npr. AUTOINCREMENT pri SQLite.

Podatkovni tipi

• Številski tipi:
  • integer ipd. - cela števila
  • numeric(p, s), decimal(p, s), real, ... - decimalna števila
• Besedilni tipi:
  • char(n), varchar(n) - nizi dolžine natanko/največ n znakov
  • text - poljubno besedilo
• Časovni tipi (oblika ISO 8601):
  • date - datum v obliki YYYY-MM-DD
  • time - čas v obliki hh:mm:ss
  • datetime - datum in čas v obliki YYYY-MM-DD hh:mm:ss
• Detajli se razlikujejo med različnimi RDBMS.

Omejitve

• RDBMS lahko ob spreminjanju podatkov v bazi preverja, ali držijo omejitve, predpisane ob ustvarjanju tabele.
  • Če ne držijo, ne dovoli zaključka transakcije.
• PRIMARY KEY (glavni ključ) - stolpec (ali več njih), katerega vrednost enolično določa vrstico.
  • Lahko je samo en!
  • Vrednosti se ne smejo ponavljati in ne smejo biti NULL.
• NOT NULL - vrednosti v stolpcu morajo obstajati (niso NULL).
• UNIQUE (enoličnost) - vrednosti v stolpcu (ali več njih) se ne smejo ponavljati.
  • Vrednost NULL se lahko pojavi tudi večkrat (če ni prepovedana z NOT NULL)!

Omejitve (2)

• CHECK - pogoj, ki mora veljati v vsaki vrstici tabele.
• FOREIGN KEY/REFERENCES - tuji ključ oz. referenca na drugo tabelo.
  • V stolpcu (ali več njih) se smejo pojaviti samo tiste vrednosti, ki se pojavijo kot vrednosti v stolpcu (ali več njih) tabele, na katero se sklicujemo.
  • Lahko se pojavijo tudi vrednosti NULL (če niso prepovedane z NOT NULL).
• Mogoče je nastaviti (odvisno od RDBMS), kaj se zgodi ob kršitvi omejitve.
  • Za tuje ključe je mogoče določiti tudi, kdaj se preverijo - takoj ali ob koncu transakcije.

Privzete vrednosti

• Včasih so določene vrednosti stolpca zelo pogoste.
  • Primer: število kosov artikla ob dodajanju v košarico je 1.
  • Še en primer: ob dodajanju v tabelo naj se zapiše trenutni čas.
• Če pri vstavljanju podatka ne navedemo, se uporabi privzeta vrednost.
• Poseben primer: avtomatsko številčenje.
  • V SQLite uporabimo določilo AUTOINCREMENT takoj za PRIMARY KEY pri stolpcu tipa ìnteger.
  • Nekateri RDBMS v ta namen uporabijo števce.

Primer (SQLite)

CREATE TABLE kupec (
  id_kupec        integer  PRIMARY KEY AUTOINCREMENT,
  uporabnisko_ime text     NOT NULL UNIQUE,
  ime             text     NOT NULL,
  priimek         text     NOT NULL,
  naslov          text,
  datum_rojstva   date     CHECK (datum_rojstva < DATETIME('now', '-18 years')),
                           -- kupec mora biti starejši od 18 let
  CHECK (LENGTH(ime) + LENGTH(priimek) <= 30)
);

CREATE TABLE narocilo (
  id_narocilo     integer  PRIMARY KEY AUTOINCREMENT,
  id_kupec        integer  NOT NULL REFERENCES kupec(id_kupec),
  stevilo         integer  DEFAULT (1),
  datum           datetime DEFAULT (DATETIME('now'))
);

Vstavljanje podatkov

INSERT INTO tabela [(stolpec1, stolpec2, ...)]
VALUES (vrednost1, vrednost2, ...), ...;

• Za vsak naveden nabor vstavi eno vrstico v navedeno tabelo.
  • Obstoječe vrstice ostanejo nespremenjene.
• Vstavljene vrstice bodo imele v naštetih stolpcih ustrezne naštete vrednosti.
  • Če kateri od stolpcev tabele ni naštet, se uporabi njegova privzeta vrednost oziroma NULL.
• Če stolpcev ne naštejemo, se smatra, da smo našteli vse stolpce v vrstnem redu iz definicije tabele.

Vstavljanje izpeljanih podatkov

INSERT INTO tabela [(stolpec1, stolpec2, ...)]
SELECT ...;

• V navedeno tabelo vstavimo vrstice iz rezultata navedenega stavka SELECT.
  • Rezultat mora imeti ustrezno število stolpcev!
• Stavek SELECT je lahko poljuben (WHERE, JOIN, GROUP BY, WITH, ...).

Primeri

INSERT INTO kupec (uporabnisko_ime, ime, priimek, datum_rojstva)
VALUES ('janez', 'Janez', 'Novak', '1978-03-24'); -- dobi id_kupec = 1

INSERT INTO kupec (uporabnisko_ime, ime, priimek, datum_rojstva)
VALUES ('micka', 'Micka', 'Kovač', '2008-07-11'); -- napaka!

INSERT INTO kupec (uporabnisko_ime, ime, priimek, datum_rojstva)
VALUES ('micka', 'Micka', 'Kovač', '1998-07-11'); -- dobi id_kupec = 2

INSERT INTO narocilo (id_kupec)
SELECT id_kupec FROM kupec
 WHERE priimek = 'Novak';  -- ena vrstica, dobi id_narocilo = 1

INSERT INTO narocilo (id_kupec, stevilo)
VALUES (1, 3), (2, 4), (2, 2), (3, 6), (1, 5); -- napaka!

INSERT INTO narocilo (id_kupec, stevilo)
VALUES (1, 3), (2, 4), (2, 2), (1, 5); -- dobijo id_narocilo = 2, 3, 4, 5

Spreminjanje obstoječih podatkov

UPDATE tabela
   SET stolpec1 = vrednost1, stolpec2 = vrednost2, ...
 WHERE pogoj;

• Spreminjamo obstoječe vrstice v navedeni tabeli - njihovo število ostane nespremenjeno.
• Pogoj WHERE določa, katere vrstice bomo spremenili.
  • Lahko ga izpustimo - tedaj spreminjamo vse vrstice.
• S SET povemo, kako spremenimo stolpce v izbranih vrsticah.
  • Vrednosti lahko sestavimo iz obstoječih vrednosti v trenutni vrstici.

Brisanje podatkov

DELETE FROM tabela
 WHERE pogoj;

• Iz navedene tabele pobrišemo vrstice, ki ustrezajo pogoju.
• Če izpustimo WHERE, bomo pobrisali vse vrstice!

Primeri

UPDATE narocilo
   SET stevilo = stevilo - 1
 WHERE id_kupec = 1;

DELETE FROM narocilo
 WHERE stevilo < 1;

Transakcije

• Začetek transakcije:

  BEGIN [TRANSACTION];
  

• Potrditev transakcije:

  END [TRANSACTION];
  COMMIT;
  

• Preklic transakcije:

  ROLLBACK;
  

Spremembe in transakcije

• Spomnimo: ko se transakcija uspešno zaključi, so nastale spremembe stalne.
  • Nimamo možnosti razveljavitve!
• Če ne poskrbimo za kontrolo transakcij, šteje vsak stavek INSERT/UPDATE/DELETE kot ena transakcija.
• Posledica: spremenjenih in pobrisanih vrstic ne bomo mogli vedno povrniti!
  • Če še nismo zaključili transakcije, jo lahko prekličemo in se tako vrnemo na prvotno stanje.
• Če med izvajanjem stavka pride do kakršnekoli napake, ostane stanje nespremenjeno.