Structured Query Language (SQL)

Dotazovací jazyk pro relační SŘBD. Tento tutoriál ukazuje příkazy a syntaxi jazyka platnou pro standard z roku 1992. 

SQL = DDL + DML + TCL + DCL

Oracle SQL Developer - dotazování


Sdílená data


Výběr záznamů - příkaz SELECT

Zjednodušená syntaktická konstrukce příkazu : 

SELECT  [DISTINCT] { * | [sloupec_vyraz [[AS] alias] [,...]}
   FROM  tabulka [alias] [,...]
[ WHERE  podmínka]
[ GROUP  BY seznam_neagregovaných_sloupců_výrazů]
[HAVING  podmínka]
[ ORDER  BY seznam_řazených_sloupců_výrazů]
;

Dotazy nad jednou tabulkou - projekce

-- toto je komentář platný do konce řádky
-- vybrat celý obsah tabulky
SELECT * FROM db2.cd;

-- hodnoty vybraných sloupců tabulky
SELECT pisen, d_min, d_sec FROM db2.cd;

-- hodnoty vybraného sloupce – mohou se opakovat
SELECT interpret FROM db2.cd;

-- hodnoty vybraného sloupce – každá jen jednou
SELECT DISTINCT interpret FROM db2.cd;

-- výpočet nových hodnot ze sloupců tabulky
SELECT pisen, d_min * 60 + d_sec FROM db2.cd;

-- to samé, přidělení aliasu vypočtenému výrazu
SELECT pisen, d_min * 60 + d_sec AS stopaz FROM db2.cd;

Dotazy nad jednou tabulkou - selekce (a projekce)

-- vybrat písně hrající déle než 5 minut
SELECT pisen, d_min, d_sec FROM db2.cd WHERE d_min >= 5;

V podmínce WHERE je možné použít následující relační operátory a logické spojky:

Relační operátory

Logické spojky 

-- vybrat písně, hrající 5 až 7 minut
SELECT pisen, d_min, d_sec
  FROM db2.cd
 WHERE d_min >= 5 AND d_min <= 7; 

-- to samé, použit operátor BETWEEN
SELECT pisen, d_min, d_sec
  FROM db2.cd
 WHERE d_min BETWEEN 5 AND 7;

-- negace podmínky s operátorem BETWEEN je legrace
SELECT pisen, d_min, d_sec
  FROM db2.cd
 WHERE d_min NOT BETWEEN 5 AND 7; 

-- vybrat písně oblíbených interpretů – množinový operátor IN
SELECT interpret, pisen
  FROM db2.cd
 WHERE interpret IN ('The Beatles', 'The Cranberies'); 

-- vybrat písně neoblíbených interpretů (operátor NOT IN)
SELECT interpret, pisen
  FROM db2.cd
 WHERE interpret NOT IN ('The Beatles', 'The Cranberies'); 

-- vybrat písně interpretů začínající The – operátor LIKE
SELECT interpret, pisen
  FROM db2.cd
 WHERE interpret LIKE 'The %'; 

-- negace operátoru LIKE
SELECT interpret, pisen
  FROM db2.cd
 WHERE interpret NOT LIKE 'The %';

Ve vyhledávacím řetězci lze použít tyto dva speciální znaky: 

-- použití tzv. escape znaku např. pro hledání 50%
SELECT pisen, poznamka
  FROM db2.cd
 WHERE poznamka LIKE '%50#%%' ESCAPE '#';

Hodnota NULL a tříhodnotová logika

Prázdná položka v záznamu databáze je v jazyce SQL reprezentována hodnotou NULL. Použití hodnoty NULL v podmínce selekce vyžaduje změnu sémantiky vyhodnocení logických podmínek. Kromě hodnot TRUE a FALSE musíme navíc zavést hodnotu UNKNOWN a přejít na tzv. tříhodnotovou logiku. 

-- vybrat písně, které nemají vyplněnu poznámku – rovnost na NULL
SELECT pisen, poznamka
  FROM db2.cd
 WHERE poznamka = NULL; 

-- vybrat písně, které mají vyplněnu poznámku – nerovnost na NULL
SELECT pisen, poznamka
  FROM db2.cd
 WHERE poznamka <> NULL;

Oba dotazy poskytnou prázdné odpovědi. Podmínka testující rovnost či nerovnost vůči hodnotě NULL je vždy vyhodnocena jako UNKNOWN. Co se týče hodnot UNKNOWN, aplikuje se předpoklad tzv. uzavřeného světa - tj. co není známo, není pravda. A UNKONWN není TRUE. Proto oba dotazy chybně nabízí prázdnou odpověď. Podobná situace platí i u operátorů IN a NOT IN. Tohle je častá chyba začátečníků. Pro test rovnosti či nerovnosti vůči hodnotě NULL je správné použít k tomu určené operátory IS NULL a IS NOT NULL. 

-- vybrat písně, které nemají vyplněnu poznámku – IS NULL
SELECT pisen, poznamka
  FROM db2.cd
 WHERE poznamka IS NULL; 

-- vybrat písně, které mají poznámku vyplněnu – IS NOT NULL
SELECT pisen, poznamka
  FROM db2.cd
 WHERE poznamka IS NOT NULL;

Podobná situace platí i u operátoru NOT IN: 

-- vybrat písně, které nemají v poznámce text Prvni pisnicka nebo Druha pisnicka
-- CHYBNĚ - vybere jen písně s vyplněnou poznámkou
SELECT pisen, poznamka
  FROM db2.cd
 WHERE poznamka NOT IN ('Prvni pisnicka', 'Druha pisnicka'); 

-- vybrat písně, které nemají v poznámce text Prvni pisnicka nebo Druha pisnicka
-- SPRÁVNĚ - vybere i písně bez vyplněné poznámky
SELECT pisen, poznamka
  FROM db2.cd
 WHERE poznamka NOT IN ('Prvni pisnicka', 'Druha pisnicka')
    OR poznamka IS NULL;

Tříhodnotovou logiku můžeme tedy shrnout v následující tabulce. Znovu však připomínám, že dotaz vybere pouze ty záznamy, pro které je podmínka v klauzuli WHERE vyhodnocena jako TRUE.

A B A and B A or B not A
TRUE TRUE TRUE TRUE FALSE
TRUE FALSE FALSE TRUE FALSE
TRUE UNKNOWN UNKNOWN TRUE FALSE
FALSE TRUE FALSE TRUE TRUE
FALSE FALSE FALSE FALSE TRUE
FALSE UNKNOWN FALSE UNKNOWN TRUE
UNKNOWN TRUE UNKNOWN TRUE UNKNOWN
UNKNOWN FALSE FALSE UNKNOWN UNKNOWN
UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN

Potřebujeme-li namísto NULL dosadit definovanou hodnotu, použijeme funkci NVL(sloupec, hodnota) : 

SELECT pisen, NVL(poznamka, 'Bez poznamky')
  FROM db2.cd;

Dotazy nad jednou tabulkou - řazení

Výsledná data, která získáme zadaným dotazem, nejsou nijak uspořádána. Ve skutečnosti SŘBD tato data vypisuje chronologicky, tj. data jsou seřazena podle času, kdy byla do databáze vložena. Toto kritérium nám rozhodně nestačí a občas požadujeme vypisovaná data seřadit podle našeho kritéria. K tomuto účelu slouží v příkazu SELECT klauzule ORDER BY. 

SELECT interpret, pisen 
  FROM db2.cd
 ORDER BY interpret ASC; 

SELECT interpret, pisen 
  FROM db2.cd
 ORDER BY interpret DESC;

Směr řazení hodnot udává klíčové slovo : 

-- seřazení interpretů proti abecedě,
-- abecedně seřazeny písně každému interpretovi
SELECT interpret, pisen
  FROM db2.cd
 ORDER BY interpret DESC, pisen ASC; 

-- řazení podle výrazu využívá jeho alias
SELECT pisen, d_min * 60 + d_sec AS stopaz
  FROM db2.cd
 ORDER BY stopaz DESC; 

-- to samé, použito číselné označení sloupce
SELECT pisen, d_min * 60 + d_sec
  FROM db2.cd
 ORDER BY 2 DESC;

Dotazy nad jednou tabulkou - agregační funkce

-- funkce COUNT spočítá všechny (i neúplné) záznamy tabulky
SELECT COUNT(*) FROM cd;

-- počet záznamů, kde je vyplněna položka pisen
SELECT COUNT(pisen) FROM db2.cd;

-- počet záznamů, kde je vyplněna položka interpret
SELECT COUNT(interpret) FROM db2.cd;

-- počet záznamů, kde je vyplněna položka poznamka – bude jiný
SELECT COUNT(poznamka) FROM db2.cd;

-- počet různých interpretů v tabulce
SELECT COUNT(DISTINCT interpret) FROM db2.cd;

-- ostatní agregační funkce
SELECT MIN(d_min), MAX(d_min), SUM(d_min), AVG(d_min) FROM db2.cd;

Poznámka: 

-- každému interpretovi (tvoří skupinu atributů) spočítám,
-- kolik písní mám a jak dlouho budou všechny hrát,
-- celé to abecedně seřadím
SELECT interpret,
       COUNT(stopa) AS pocet_stop,
       SUM(d_min * 60 + d_sec) AS delka
  FROM db2.cd
 GROUP BY interpret
 ORDER BY interpret;

Poznámka:

Aby dotaz byl korektní, musí klauzule GROUP BY obsahovat všechny neagregované sloupce, jejichž hodnotu chceme z dotazu znát. 

-- ten samý dotaz s podmínkou (HAVING), že chci jen ty
-- interprety, kteří nabízí max. 12 písní a budou hrát
-- alespoň 5 minut (300 sekund)
-- v podmínce HAVING nelze používat aliasy a výrazy zde musí
-- být jen agregované
SELECT interpret,
       COUNT(stopa) AS pocet_stop,
       SUM(d_min * 60 + d_sec) AS delka
  FROM db2.cd
 GROUP BY interpret
HAVING SUM(d_min * 60 + d_sec) > 300 AND COUNT(stopa) < 12
 ORDER BY interpret;

Poznámka:


Dotazy nad jednou tabulkou - vnořený dotaz

-- hledám nejdéle hrající písně
-- vnořený dotaz vrací právě jednu hodnotu
SELECT pisen, d_min, d_sec
  FROM db2.cd
 WHERE d_min * 60 + d_sec = (SELECT MAX(d_min * 60 + d_sec) FROM cd); 

-- to samé, použit množinový operátor ALL
-- vnořený dotaz vrací množinu hodnot
SELECT pisen, d_min, d_sec
  FROM db2.cd
 WHERE d_min * 60 + d_sec >= ALL(SELECT d_min * 60 + d_sec FROM cd); 

-- hledám všechny písně, které nejsou nejdéle hrající
-- použit množinový operátor ANY/SOME
-- vnořený dotaz vrací množinu hodnot
SELECT pisen, d_min, d_sec
  FROM db2.cd
 WHERE d_min * 60 + d_sec < ANY(SELECT d_min * 60 + d_sec FROM cd);

Poznámka:


Dotazy nad více tabulkami - přirozené spojení (natural join, inner join, equijoin)

-- bez aliasů, plně kvalifikované názvy atributů
SELECT db2.tituly.id, db2.tituly.interpret, db2.tituly.titul, 
       db2.pisne.stopa, db2.pisne.pisen, db2.pisne.d_min, db2.pisne.d_sec, db2.pisne.poznamka
  FROM db2.tituly, db2.pisne
 WHERE db2.tituly.id = db2.pisne.id; 

-- podmínka přirozeného spojení
SELECT t.id, t.interpret, t.titul, 
       p.stopa, p.pisen, p.d_min, p.d_sec, p.poznamka
  FROM db2.tituly t, db2.pisne p
 WHERE t.id = p.id; 

-- operátor INNER JOIN s podmínkou ON
SELECT t.id, t.interpret, t.titul,
       p.stopa, p.pisen, p.d_min, p.d_sec, p.poznamka
  FROM db2.tituly t INNER JOIN db2.pisne p ON t.id = p.id;  

-- operátor JOIN s podmínkou ON
SELECT t.id, t.interpret, t.titul,
       p.stopa, p.pisen, p.d_min, p.d_sec, p.poznamka
  FROM db2.tituly t JOIN db2.pisne p ON t.id = p.id;  

-- operátor INNER JOIN s konstrukcí USING
SELECT id, t.interpret, t.titul,
       p.stopa, p.pisen, p.d_min, p.d_sec, p.poznamka
  FROM db2.tituly t INNER JOIN db2.pisne p USING (id);  

-- operátor JOIN s konstrukcí USING
SELECT id, t.interpret, t.titul,
       p.stopa, p.pisen, p.d_min, p.d_sec, p.poznamka
  FROM db2.tituly t JOIN db2.pisne p USING (id);  

-- operátor NATURAL JOIN
SELECT id, t.interpret, t.titul,
       p.stopa, p.pisen, p.d_min, p.d_sec, p.poznamka
  FROM db2.tituly t NATURAL JOIN db2.pisne p;  

-- operátor NATURAL INNER JOIN
SELECT id, t.interpret, t.titul,
       p.stopa, p.pisen, p.d_min, p.d_sec, p.poznamka
  FROM db2.tituly t NATURAL INNER JOIN db2.pisne p;

Dotazy nad více tabulkami - levé spojení (left outer join)

-- operátor LEFT OUTER JOIN
SELECT p.katedra, p.zkratka,
       r.semestr, r.den, r.h_od, r.h_do, r.poznamka
  FROM db2.predmety p LEFT OUTER JOIN db2.rozvrh r
    ON p.id = r.id_predm;	

-- specifická syntaxe Oracle
SELECT p.katedra, p.zkratka,
       r.semestr, r.den, r.h_od, r.h_do, r.poznamka
  FROM db2.predmety p, db2.rozvrh r
 WHERE p.id = r.id_predm(+);

Dotazy nad více tabulkami - pravé spojení (right outer join)

-- operátor RIGHT OUTER JOIN
SELECT p.katedra, p.zkratka,
       r.semestr, r.den, r.h_od, r.h_do, r.poznamka
  FROM db2.predmety p RIGHT OUTER JOIN db2.rozvrh r
    ON p.id = r.id_predm;	

-- specifická syntaxe Oracle
SELECT p.katedra, p.zkratka,
       r.semestr, r.den, r.h_od, r.h_do, r.poznamka
  FROM db2.predmety p, db2.rozvrh r
 WHERE r.id_predm = p.id(+);

Dotazy nad více tabulkami - plné spojení (full outer join)

-- levé a pravé spojení provedeno současně
-- operátor FULL OUTER JOIN
SELECT p.katedra, p.zkratka,
       r.semestr, r.den, r.h_od, r.h_do, r.poznamka
  FROM db2.predmety p FULL OUTER JOIN db2.rozvrh r 
    ON p.id = r.id_predm;

Dotazy nad více tabulkami - polospojení (semijoin)

-- operátor EXISTS s poddotazem
SELECT p.zkratka, p.nazev
  FROM db2.predmety p
 WHERE EXISTS ( SELECT *
                  FROM db2.rozvrh r
                 WHERE p.id = r.id_predm);				 

-- operátor IN s poddotazem
SELECT p.zkratka, p.nazev
  FROM db2.predmety p
 WHERE p.id IN ( SELECT r.id_predm
                   FROM db2.rozvrh r
                  WHERE r.id_predm IS NOT NULL);				  

-- lze realizovat přirozeným spojením s DISTINCT
SELECT DISTINCT p.zkratka, p.nazev
  FROM db2.predmety p, db2.rozvrh r
 WHERE p.id = r.id_predm;

Dotazy nad více tabulkami - antijoin

-- operátor NOT EXISTS s poddotazem
SELECT p.zkratka, p.nazev
  FROM db2.predmety p
 WHERE NOT EXISTS ( SELECT *
                      FROM db2.rozvrh r
                     WHERE p.id = r.id_predm);					 

-- operátor NOT IN s poddotazem
SELECT p.zkratka, p.nazev
  FROM db2.predmety p
 WHERE p.id NOT IN ( SELECT r.id_predm
                       FROM db2.rozvrh r
                      WHERE r.id_predm IS NOT NULL);

Dotazy nad více tabulkami - selfjoin

-- podmínka přirozeného spojení
SELECT zamestnanec.prijmeni, zamestnanec.jmeno,
       vedouci.prijmeni, vedouci.jmeno
  FROM db2.osoby zamestnanec, db2.osoby vedouci
 WHERE zamestnanec.id_nad = vedouci.id_osoby; 

-- operátor INNER JOIN s podmínkou ON
SELECT zamestnanec.prijmeni, zamestnanec.jmeno,
       vedouci.prijmeni, vedouci.jmeno
  FROM db2.osoby zamestnanec INNER JOIN db2.osoby vedouci
    ON zamestnanec.id_nad = vedouci.id_osoby;

Dotazy nad více tabulkami - kartézský součin

-- operátor CROSS JOIN – vyžaduje disjunktní množiny atributù
SELECT p.katedra, p.zkratka, p.nazev,
       r.semestr, r.den, r.h_od, r.h_do, r.poznamka
  FROM db2.predmety p CROSS JOIN db2.rozvrh r;  

-- bez operátoru či podmínky
SELECT p.katedra, p.zkratka, p.nazev,
       r.semestr, r.den, r.h_od, r.h_do, r.poznamka
  FROM db2.predmety p, db2.rozvrh r;

Dotazy z více dotazů - množinové operace

-- nad kompatibilními dotazy lze použít množinové operátory
-- operátor UNION – sjednocení, odstraňuje duplicitní data
-- komutativní operace, lze použít nad větším počtem dotazů
-- výsledkem je FULL OUTER JOIN
SELECT p.katedra, p.zkratka,
       r.semestr, r.den, r.h_od, r.h_do, r.poznamka
  FROM db2.predmety p LEFT OUTER JOIN db2.rozvrh r
    ON p.id = r.id_predm
UNION
SELECT p.katedra, p.zkratka,
       r.semestr, r.den, r.h_od, r.h_do, r.poznamka
  FROM db2.predmety p RIGHT OUTER JOIN db2.rozvrh r
    ON p.id = r.id_predm;	

-- operátor UNION ALL – sjednocení, ponechá duplicitní data
-- komutativní operace, lze použít nad větším počtem dotazů
SELECT p.katedra, p.zkratka,
       r.semestr, r.den, r.h_od, r.h_do, r.poznamka
  FROM db2.predmety p LEFT OUTER JOIN db2.rozvrh r
    ON p.id = r.id_predm
UNION ALL
SELECT p.katedra, p.zkratka,
       r.semestr, r.den, r.h_od, r.h_do, r.poznamka
  FROM db2.predmety p RIGHT OUTER JOIN db2.rozvrh r
    ON p.id = r.id_predm;	

-- operátor INTERSECT – průnik, odstraňuje duplicitní data
-- komutativní operace, lze použít nad větším počtem dotazů
-- výsledkem je INNER JOIN, tj. přirozené spojení
SELECT p.katedra, p.zkratka,
       r.semestr, r.den, r.h_od, r.h_do, r.poznamka
  FROM db2.predmety p LEFT OUTER JOIN db2.rozvrh r
    ON p.id = r.id_predm
INTERSECT
SELECT p.katedra, p.zkratka,
       r.semestr, r.den, r.h_od, r.h_do, r.poznamka
  FROM db2.predmety p RIGHT OUTER JOIN db2.rozvrh r
    ON p.id = r.id_predm;	

-- operátor MINUS – rozdíl
-- záleží na pořadí dotazů, lze aplikovat jen nad dvěma dotazy
-- výsledek je srovnatelný s ANTIJOINem
SELECT p.katedra, p.zkratka,
       r.semestr, r.den, r.h_od, r.h_do, r.poznamka
  FROM db2.predmety p LEFT OUTER JOIN db2.rozvrh r
    ON p.id = r.id_predm
MINUS
SELECT p.katedra, p.zkratka,
       r.semestr, r.den, r.h_od, r.h_do, r.poznamka
  FROM db2.predmety p RIGHT OUTER JOIN db2.rozvrh r
    ON p.id = r.id_predm;

Data definition language

Vytvoření tabulky


Vybrané datové typy SŘBD Oracle

NUMBER libovolné číslo
NUMBER(p) celé číslo, p - počet cifer čísla v rozsahu 1 až 38
NUMBER(p, s) číslo, s - přesnost čísla v rozsahu -84 až 127 (záporná na desítky, kladná na desetiny)
VARCHAR2(n) textový řetězec dlouhý maximálně n bytů, n - počet bytů v rozsahu 1 až 4000
VARCHAR2(n CHAR) textový řetězec dlouhý maximálně n znaků, délka řetězce nesmí překročit velikost 4000 bytů
CHAR(n) textový řetězec dlouhý právě n bytů, n - počet bytů v rozsahu 1 až 2000
DATE datum a čas v rozsahu 1.1.4712 př.n.l. až 31.12.9999 n.l.
BLOB velký binární objekt velikosti až 4 GB 
CREATE TABLE lide (
  os_cislo   NUMBER(5)    NOT NULL,
  rc         VARCHAR2(30) NOT NULL, 
  jmeno      VARCHAR2(30) NOT NULL,
  prijmeni   VARCHAR2(30) NOT NULL,
  dat_naroz  DATE,
  pohlavi    CHAR(1)      NOT NULL,
  telefon    VARCHAR2(50),
  plat       NUMBER(5),
  cislo_prac NUMBER(5)    NOT NULL
); 

CREATE TABLE pracoviste (
  cislo_prac NUMBER(5)    NOT NULL,
  nazev      VARCHAR2(30) NOT NULL
); 

CREATE TABLE predmety (
  zkratka   VARCHAR2(5),
  katedra   VARCHAR2(3),
  nazev     VARCHAR2(30) NOT NULL,
  kredity   NUMBER(2)    NOT NULL
);

Změna tabulky: přidání a odebrání sloupce

ALTER TABLE pracoviste ADD ( adresa VARCHAR2(80) );

ALTER TABLE pracoviste DROP ( adresa );

Změna tabulky: přidání a odebrání integritního omezení


Entitní integritní omezení

-- primární klíč pro tabulku LIDE
ALTER TABLE lide ADD CONSTRAINT pk_lide PRIMARY KEY (os_cislo);

-- primární klíč pro tabulku PRACOVISTE
ALTER TABLE pracoviste ADD CONSTRAINT pk_pracoviste PRIMARY KEY (cislo_prac);

-- primární klíč pro tabulku PREDMETY
ALTER TABLE predmety ADD CONSTRAINT pk_predmety PRIMARY KEY (zkratka, katedra);

-- nastavení unikátnosti hodnot atributu RC v tabulce LIDE
ALTER TABLE lide ADD CONSTRAINT unikatni_rc UNIQUE (rc);

-- nastaveni unikatnosti hodnot atributu NAZEV v tabulce PRACOVISTE
ALTER TABLE pracoviste ADD CONSTRAINT unikatni_nazev UNIQUE (nazev);

Doménové integritní omezení

-- hlídání velikosti platu člověka
ALTER TABLE lide ADD CONSTRAINT kontrola_platu CHECK ( plat > 5000 AND plat < 20000 );

Referenční integritní omezení

-- restriktivní omezení, platí jako výchozí
ALTER TABLE lide 
ADD CONSTRAINT fk_prac FOREIGN KEY ( cislo_prac ) 
REFERENCES pracoviste ( cislo_prac );

-- pracovníci rušeného pracoviště budou mít číslo pracoviště NULL
ALTER TABLE lide 
ADD CONSTRAINT fk_prac FOREIGN KEY ( cislo_prac ) 
REFERENCES pracoviste ( cislo_prac ) ON DELETE SET NULL;

-- kaskádní mazání
-- kromě rušeného pracoviště budou smazáni i všichni jeho pracovníci
ALTER TABLE lide 
ADD CONSTRAINT fk_prac FOREIGN KEY ( cislo_prac ) 
REFERENCES pracoviste ( cislo_prac ) ON DELETE CASCADE;

Zrušení integritního omezení

-- zrušení primárního klíče
ALTER TABLE lide DROP CONSTRAINT pk_lide;

-- zrušení doménového integritního omezení
ALTER TABLE lide DROP CONSTRAINT kontrola_platu;

-- zrušení cizího klíče
ALTER TABLE lide DROP CONSTRAINT fk_prac;

Vytvoření indexu

Logicky seřadí záznamy tabulky podle daného atributu (množiny atributů). 

-- unikátní index pro primární klíč
-- obvykle má shodný název s integritním omezením
CREATE UNIQUE INDEX pk_lide ON lide (os_cislo);

-- běžný index (pro cizí klíč)
-- název se může shodovat s integritním omezením
CREATE INDEX fk_prac ON lide (cislo_prac);

Zrušení indexu

-- zrušení unikátního indexu
DROP INDEX pk_lide;

-- zrušení běžného indexu
DROP INDEX fk_prac;

Vyprázdnění tabulky

TRUNCATE TABLE lide;

Zrušení tabulky

-- smazání obsahu tabulky i její definice
DROP TABLE predmety;

-- ke smazání nedojde, brání tomu referenční integrita (krome CASCADE)
DROP TABLE pracoviste;

-- smazání tabulky včetně všech referenčních integritních omezení, které by tomu bránily
DROP TABLE pracoviste CASCADE CONSTRAINTS;

-- platí pouze v SŘBD Oracle
-- tabulka včetně obsahu bude skutečně smazána, jinak je přesunuta do odpadkového koše
DROP TABLE lide PURGE;

Vytvoření databázové pohledu

-- pojmenování sloupců pohledu využívá názvy tabulky
CREATE VIEW cd_beatles AS
SELECT *
  FROM db2.cd
 WHERE interpret LIKE '%Beatles%'; 

-- vlastní pojmenování sloupců pohledu
CREATE VIEW cd_prehled ( skladatel, dilo ) AS
SELECT DISTINCT interpret, titul
  FROM db2.cd;  

-- pohledy bývají zpravidla spojovány se složitějšímy dotazy
CREATE VIEW cd_stat ( skladatel, dilo, pocet_stop, delka ) AS
SELECT a.interpret, 
       a.titul, 
       count(b.stopa), 
       sum(b.d_min)
  FROM db2.tituly a, db2.pisne b
 WHERE a.id = b.id
 GROUP BY  a.interpret, a.titul;

Zrušení databázového pohledu

Zrušení databázového pohledu nemá žádný vliv na uložená data v databázi. 

DROP VIEW cd_beatles;

DROP VIEW cd_prehled;

DROP VIEW cd_stat;

Data manipulation language

Před manipulací s daty v databázi se vytvoříme kopie těchto tabulek: 

DROP TABLE rozvrh;
DROP TABLE predmety;
DROP TABLE mistnosti;

CREATE TABLE predmety  AS SELECT * FROM db2.predmety;
CREATE TABLE rozvrh    AS SELECT * FROM db2.rozvrh;
CREATE TABLE mistnosti AS SELECT * FROM db2.mistnosti; 

ALTER TABLE predmety ADD CONSTRAINT pk_predmety PRIMARY KEY (id);

Vkládání dat do databáze

Jedním příkazem INSERT lze vložit jeden záznam do jedné tabulky: 

-- vkladani celeho zaznamu vcetne nevyplnenych polozek (NULL)
-- vyzaduje znalost poradi atributu v definici tabulky
INSERT  INTO predmety 
VALUES ( 99, 'DS', 'KIV', 'Distribuovane systemy', NULL, NULL, NULL );

-- skonci chybou - porusena integrita primarniho klice
INSERT  INTO predmety 
VALUES ( 99, 'PP', 'KIV', 'Pocitace a programovani', NULL, NULL, NULL );

-- vkladani jen vybranych polozek noveho zaznamu
-- jejich poradi lze definovat dle uvazeni
INSERT  INTO predmety ( nazev, katedra, zkratka, kredity, id )
VALUES ( 'Pocitace a programovani', 'KIV', 'PP', 5, 98 );

Výsledek dotazu (obvykle více záznamů) lze uložit do tabulky jedním příkazem INSERT: 

INSERT INTO rozvrh (id_predm, semestr, poznamka)
SELECT id, 'LS', 'Garant: '||garant
  FROM predmety
 WHERE katedra = 'KIV';

Aktualizace dat v databázi

Aktualizací dat se rozumí změna údajů v existujících záznamech v tabulce. Vždy dochází k přepsání původní hodnoty, případně nastavení hodnoty místo prázdné položky. 

-- aktualizace vsech zaznamu v tabulce
UPDATE predmety
   SET kredity = 20;   

-- aktualizace jen vybranych zaznamu
UPDATE predmety
   SET kredity = 20
 WHERE katedra = 'KIV'
   AND zkratka = 'KP';   

-- vymazani hodnoty polozky ve vybranem zaznamu
UPDATE predmety
   SET pocet_studentu = NULL
 WHERE katedra = 'KIV'
   AND zkratka = 'PC';   

-- aktualizace nekolika polozek v zaznamu najednou
-- tak, kde neni nastaven pocet studentu, zustava hodnota NULL
UPDATE predmety
   SET kredity = 3, 
       pocet_studentu = pocet_studentu + 10;

Smazání/odstranění dat z databáze

-- vymazani celeho obsahu tabulky
-- to same vykona (jinak) prikaz TRUNCATE
DELETE 
  FROM rozvrh;  

-- smazani jen vybranych zaznamu
DELETE
  FROM rozvrh
 WHERE poznamka LIKE 'Seminar%';

Transaction control language


Spáchání transakce

COMMIT;

Poznámka: Transakci také spáchá u většiny SŘBD jakýkoliv příkaz jazyků DDL a DCL!


Odrolování transakce

ROLLBACK;

Bod uložení v transakci

-- zahajeni transakce
DELETE FROM predmety WHERE zkratka = 'HB';
SELECT zkratka FROM predmety;

-- nastaveni bodu lozeni
SAVEPOINT prvni;

-- dalsi prikaz DELETE v transakci
DELETE FROM predmety WHERE zkratka LIKE 'P%';
SELECT zkratka FROM predmety;

-- odvolani vsech operaci k danemu bodu ulozeni
ROLLBACK TO SAVEPOINT prvni;
SELECT zkratka FROM predmety;

Nastavení režimu transakce

-- transakce dovoluje pouze cist data, ne zapis
SET TRANSACTION READ ONLY;

-- vychozi nastaveni
-- transakce dovoluje cist i zapisovat data
SET TRANSACTION READ WRITE;

Explicitní zamykání tabulek a záznamů

I když transakce implicitně zamyká použité tabulky, můžeme si je zamknout (před jinou transakcí) sami.

Každou tabulku lze zamknout 

-- uzamceni cele tabulky vylucnim zamkem
-- zadna jina transakce nema do teto tabulky pristup
LOCK TABLE rozvrh IN EXCUSIVE MODE;

-- uzamceni cele tabulky sdilenym zamkem
-- ostatni transakce mohou z dane tabulky pouze cist
LOCK TABLE rozvrh IN SHARE MODE;

Některé SŘBD (zpravidla v nejvyšší edici) umožní zamykání jen vybraných záznamů 

-- uzamknuti jen rozvrhovych akci konanych v ZS
SELECT *
  FROM rozvrh
 WHERE semestr = 'ZS'
   FOR UPDATE;

Odemknutí zamčených tabulek a záznamů

-- spachani transakce a take odemceni vsech tabulek uzamcenych v transakci 
COMMIT;

-- zruseni transakce a take odemceni vsech tabulek uzamcenych v transakci 
ROLLBACK;

Data control language


Přidělení oprávnění

-- prideleni prava SELECT nad tabulkou PREDMETY uzivateli soused
GRANT SELECT ON predmety TO soused;

-- prideleni vice prav najednou
GRANT INSERT, UPDATE, DELETE ON predmety TO soused;

-- prideleni opravneni vsem uzivatelum (PUBLIC)
GRANT SELECT ON predmety TO PUBLIC;

-- prideleni prava SELECT nad tabulkou PREDMETY uzivateli soused
-- navic uzivatel soused ziskal moznost toto opravnini sirit dale
GRANT SELECT ON predmety TO soused WITH GRANT OPTION;

Přístup k datům, ke kterým jsme získali přístup

SELECT * FROM zima.predmety;

Odebrání oprávnění

-- odebrani prava SELECT nad tabulkou PREDMETY uzivateli soused
-- odebira tez moznost pridelene pravo sirit dale, pokud bylo prideleno
REVOKE SELECT ON rozvrh FROM soused;

-- odebrani vice pridelenych prav najednou
REVOKE INSERT, UPDATE, DELETE ON rozvrh FROM soused;

-- odebrani opravneni vsem uzivatelum
REVOKE SELECT ON predmety FROM PUBLIC;

Uživatelské role

Uživatelská práva/oprávnění můžeme sdružovat do rolí a uživatelům přidělovat celé role. Abychom tak mohli učinit, potřebujeme mít přidělené systémové oprávnění CREATE ROLE.


Definice role

-- role pro cteni dat z tabulek
CREATE ROLE role_cteni;

-- prideleni dilcich opravneni roli
GRANT SELECT ON predmety TO role_cteni;
GRANT SELECT ON rozvrh TO role_cteni;
GRANT SELECT ON mistnosti TO role_cteni;

-- role pr zapis dat do tabulek
CREATE ROLE role_zapis;

-- prideleni dilcich opravneni roli
GRANT INSERT, UPDATE, DELETE ON predmety TO role_zapis;
GRANT INSERT, UPDATE, DELETE ON rozvrh TO role_zapis;
GRANT INSERT, UPDATE, DELETE ON mistnosti TO role_zapis;

Přidělení role

-- prideleni role uzivateli
GRANT role_zapis TO soused;

-- prideleni role vsem uzivatelum
-- uzivatele mohou tuto roli sirit dal i ji modifikovat (pripadne smazat)
GRANT role_cteni TO PUBLIC WITH GRANT OPTION;

Odebrání role

-- odebrani role uzivateli
REVOKE role_zapis FROM soused;

-- odebrani role vsem uzivatelum
-- odebrani moznosti sirit, menit pridelenou roli, pokud to bylo prideleno
REVOKE role_cteni FROM PUBLIC;

Copyright © 2023 Martin Zíma