Podatkovne baze 1

SQL


Kako do podatkov iz baze


SELECT


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


SQLiteStudio

</span>


SELECT s1, s2 FROM tabPod


SELECT s1, s2 FROM tabPod (2)


SELECT DISTINCT


Poizvedba po določenih podatkih

</span>


Izrazi


Urejanje

</span>


SELECT - primer

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

</span>


Zahtevnejše poizvedbe

S podpoizvedbami


Omejitve


Primer

</span>


WITH

</span>


Združevalne funkcije

Združevalne funkcije (2)


Združevalne funkcije (3)


Zgled


Združevalne funkcije in težave


Združevalne funkcije in težave (2)


Združevalne funkcije in težave (3)


Združevalne funkcije in težave (4)


Še en zgled


Še en zgled (2)


Primerjanje z NULL


In sedaj nekaj zapletenega


Kako dobiti seznam celin


Ustrezna celina


Kombiniranje notranje in zunanje poizvedbe


Kombiniranje notranje in zunanje poizvedbe (2)


Ustrezna celina (2)


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
                )
       );

Združevalne funkcije in skupine podatkov


GROUP BY

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

GROUP BY - primer

SELECT ... FROM donacije;
Ime Prispevek
Anja 25
Janez 62
Anja 60
Špela 390
Špela 25

</span>

SELECT ... FROM donacije
 GROUP BY ime;
Ime Prispevek
Anja 25
Anja 60
   
Janez 62
   
Špela 390
Špela 25

</span>

SELECT ... FROM donacije
 GROUP BY prispevek/100;
Ime Prispevek
Anja 25
Anja 60
Janez 62
Špela 25
   
Špela 390

</span> </span>


Primer


Izbor stolpcev

SELECT ime, prispevek FROM donacije GROUP BY ime;

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

Napake

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

GROUP BY in WHERE

</span>

</span> </span>


GROUP BY in WHERE (2)

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

continent stevilo
Africa 1
Asia 4
North America 1
South America 1

</span>

</span> </span>


Filtriranje skupin


Filtriranje skupin (2)


GROUP BY in HAVING


Več ključev


Zgled


Stikanje tabel

</span>

</span> </span>


Stikanje tabel (2)

Leto Kraj
1896 Atene
1948 London
2004 Atene
2008 Beijing
2012 London
Kraj Država
Atene Grčija
London Velika Britanija
Sydney Avstralija

</span>

</span>

Leto Kraj Država
1896 Atene Grčija
1948 London Velika Britanija
2004 Atene Grčija
2008 Beijing  
2012 London Velika Britanija
  Sydney Avstralija

</span> </span>


SELECT in več tabel


Kartezični produkt


Sklicevanje na stolpce


Stikanje z omejevanjem

</span>


Izbor določenih vrednosti


Notranji stik (INNER JOIN)


Zunanji stik (OUTER JOIN)


Druge možnosti stikanja


Ustvarjanje baz

CREATE DATABASE baza;

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, ...),]
  ...
);

Podatkovni tipi


Omejitve


Omejitve (2)


Privzete vrednosti


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, ...), ...;

Vstavljanje izpeljanih podatkov

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

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;

Brisanje podatkov

DELETE FROM tabela
 WHERE pogoj;

Primeri

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

DELETE FROM narocilo
 WHERE stevilo < 1;

Transakcije


Spremembe in transakcije