Operatorji so odvisni od shem relacij, nad katerimi jih izvajamo.
JOIN
Vir: Wikipedia.
INSERT
, UPDATE
, DELETE
, SELECT
.INSERT
- vstavljanje vrsticINSERT
- stavek za vstavljanje vrstic.INSERT INTO ime_tabele
VALUES (vrednost1, vrednost2, ..., vrednostn);
NULL
ali avtomatično generirane.INSERT INTO ime_tabele (stolpec1, st2, ..., stn)
VALUES (vrednost1, vrednost2, ... , vrednostn);
NULL
, če ni določena).INSERT
VALUES
lahko naštejemo več vektorjev vrednosti (vrstic) in jih ločimo z vejico.SELECT
, če se ujema s shemo tabele.INSERT INTO ime_tabele
SELECT ...;
UPDATE
- spreminjanje vrsticUPDATE ime_tabele SET st1 = v1, st2 = v2, ...
WHERE pogoj;
WHERE
, bomo spreminjali vse vrstice v tabeli!DELETE
- brisanje vrsticDELETE FROM ime_tabele
WHERE pogoj;
WHERE
, bomo izbrisali vse vrstice v tabeli!SELECT
- projekcijapopulation
in name
iz tabele world
:SELECT population, name FROM world;
name
in population
ter ju preimenuj v drzava
in st_prebivalcev
.SELECT name AS drzava, population AS st_prebivalcev
FROM world;
name
in ga preimenuj v Ime države
.SELECT name AS "Ime države" FROM world;
SELECT
SELECT ... WHERE
- selekcijaworld
, ki pripadajo državam v Evropi.SELECT * FROM world WHERE continent = 'Europe';
=
, <>
, !=
, <
, <=
, >
, >=
IS NULL
, IS NOT NULL
e BETWEEN a AND b
e NOT BETWEEN a AND b
e IN (v1, v2, ...)
e NOT IN (v1, v2, ...)
NOT
, AND
, OR
, XOR
SELECT
world
izračunaj razmerje med prebivalstvom in površino.SELECT name, population / area
FROM world
WHERE continent = 'Europe'
AND area > 0; -- da se izognemo deljenju z 0
SELECT name AS ime_drzave,
population / area AS gostota_prebivalstva
FROM world
WHERE continent = 'Europe'
AND area > 0;
SELECT
SELECT name AS "ime države",
ROUND(population / area, 2)
AS "gostota prebivalstva"
FROM world
WHERE continent = 'Europe' AND population > 2000000;
SELECT name, ROUND(population/1000000, 2) AS milijoni
FROM world
WHERE continent IN ('Asia', 'Europe')
AND name LIKE 'C%';
ORDER BY
- urejanje podatkovpopulation
v tabeli world
:SELECT name, population
FROM world
ORDER BY population;
SELECT name, population
FROM world
ORDER BY 2;
SELECT name, population
FROM world
ORDER BY population DESC;
ORDER BY
- urejanje podatkovSELECT name
FROM world
WHERE continent = 'Europe'
AND area > 0
ORDER BY population/area;
continent
in potem še po name
:SELECT continent, name
FROM world
ORDER BY continent, name;
world
.SELECT name FROM world
WHERE population <= (
SELECT population FROM world
WHERE name = 'Slovenia');
SELECT name FROM world
WHERE population BETWEEN (
SELECT population FROM world
WHERE name = 'Canada'
) AND (
SELECT population FROM world
WHERE name = 'Algeria');
nobel
.SELECT DISTINCT yr
FROM nobel
WHERE subject = 'physics'
AND yr NOT IN (
SELECT yr FROM nobel
WHERE subject = 'chemistry'
);
world
.SELECT AVG(population)
FROM world
WHERE continent = 'Europe';
SELECT MAX(population)
FROM world
WHERE continent = 'Africa';
SELECT MIN(area) FROM world;
SELECT SUM(gdp) FROM world
WHERE continent = 'Europe';
NULL
!COUNT
world
?SELECT COUNT(*) FROM world;
world
, ki imajo gdp
različen od NULL
?SELECT COUNT(gdp) FROM world;
SELECT COUNT(DISTINCT continent) FROM world;
Asia
v celini?SELECT COUNT(*) FROM world
WHERE continent LIKE '%Asia%';
world
.SELECT name, population
FROM world
WHERE continent = 'Africa'
AND population = MAX(population);
WHERE
.SELECT name, population
FROM world
WHERE continent = 'Africa'
AND population = (
SELECT MAX(population) FROM world
WHERE continent = 'Africa'
);
world
.SELECT name FROM world
WHERE gdp > (
SELECT MAX(gdp) FROM world
WHERE continent = 'Europe'
);
SELECT name FROM world
WHERE gdp > ALL (
SELECT gdp FROM world
WHERE continent = 'Europe'
AND gdp IS NOT NULL
);
NULL
moramo vedno uporabiti IS NULL
ali IS NOT NULL
, nikoli =
ali <>
.world
poiščimo države z maksimalnim številom prebivalstva v svoji celini.SELECT continent, name, population FROM world t1
WHERE population >= ALL (
SELECT population FROM world t2
WHERE t1.continent = t2.continent
AND population > 0
);
t1.continent
.GROUP BY
GROUP BY
razdeli tabelo na skupine, definirane z istimi vrednostmi navedenih stolpcev.SELECT
, agregirani s kako od združevalnih funkcij.SELECT continent, MAX(population) FROM world
GROUP BY continent;
SELECT
zunaj njih uporabljati samo stolpce, ki so navedeni pri GROUP BY
!HAVING
world
.SELECT continent, COUNT(*) AS kolikoDrzav FROM world
WHERE population > 200000000
GROUP BY continent;
WHERE
nam tu ne more pomagati!SELECT continent, SUM(population) FROM world
GROUP BY continent
HAVING SUM(population) >= 500000000;
HAVING
HAVING
je dejansko WHERE
nad vrsticami, ki predstavljajo skupine, dobljene z združevanjem.SELECT continent FROM world
GROUP BY continent
HAVING SUM(population) >= 500000000;
nobel
.SELECT yr FROM nobel
WHERE subject = 'Physics' AND yr > 1970
GROUP BY yr
HAVING COUNT(yr) = 1;
SELECT winner FROM nobel
GROUP BY winner
HAVING COUNT(DISTINCT subject) > 1;
nobel
.SELECT yr, subject FROM nobel
WHERE yr > 2000
GROUP BY yr, subject
HAVING COUNT(*) >= 3
ORDER BY yr;
JOIN
movie
, actor
in casting
.John Wayne
?SELECT title FROM movie
JOIN casting ON movie.id = movieid
JOIN actor ON actorid = actor.id
WHERE actor.name = 'John Wayne';
JOIN
združimo potrebne tabele in na ta način posredno izvajamo sklicevanje med tabelami.SELECT name FROM movie
JOIN casting ON movie.id = movieid
JOIN actor ON actor.id = actorid
WHERE title = 'Giant' AND name <> 'James Dean';
ord = 1
) v vsaj 10 filmih.SELECT actor.name FROM actor
JOIN casting ON actorid = id
WHERE ord = 1
GROUP BY actorid
HAVING COUNT(id) >= 10;
SELECT
izberemo stolpec, ki ne nastopa v GROUP BY
?actorid
pojavi lahko samo eno ime actor.name
, lahko naredimo takole:SELECT actor.name FROM actor
JOIN casting ON actorid = id
WHERE ord = 1
GROUP BY actorid, actor.name
HAVING COUNT(id) >= 10;
SELECT actor.name, COUNT(actor.name) AS filmi
FROM actor JOIN casting ON actorid = id
WHERE ord = 1
GROUP BY actorid, actor.name
HAVING COUNT(id) >= 10
ORDER BY filmi DESC;
SELECT name, COUNT(*) FROM movie
JOIN casting ON movie.id = movieid
JOIN actor ON actor.id = actorid
WHERE title LIKE '%love%'
GROUP BY name
HAVING COUNT(*) > 1;
SELECT movie.title, actor.name FROM movie
JOIN casting ON movie.id = movieid
JOIN actor ON actor.id = actorid
WHERE casting.ord = 1
AND movie.id IN (
SELECT movieid FROM casting
JOIN actor ON actor.id = actorid
WHERE actor.name = 'Al Pacino'
)
AND actor.name <> 'Al Pacino';
LEFT JOIN
SELECT id, title FROM movie
LEFT JOIN casting ON movieid = movie.id
WHERE movieid IS NULL;
SELECT title, name FROM movie
LEFT JOIN casting ON movieid = movie.id AND ord = 2
LEFT JOIN actor ON actor.id = actorid
WHERE title LIKE 'Wallace & Gromit: %';