Databáze je souhrn všech uložených dat, potřebných k fungování dané organizace
Databázový řídící systém – (DBS) by měl poskytnout:
Když do světa zpracování dat vstoupily počítače, nepřišly s revolučními změnami, které by převrátily naruby všechno, co se používalo dosud. Bylo tomu přesně naopak. Počítačové zpracování dat převzalo staleté zkušenosti úředníků, administrativních pracovníků, účetních, skladníků a všech těch, kteří s hromadami údajů museli zápasit mnohem dříve, než byl sestrojen první počítač. Současné zpracování dat v IS, ale i v jiných aplikacích vyžadujících přístup k většímu množství dat, se řeší databázovou technologií. Databázové programy mají za úkol uchovávat, analyzovat a spravovat data ve strukturovaném tvaru. Cílem takto organizovaných dat je rychlé vyhledání uložených údajů, jednoduchý způsob aktualizace, libovolné vyhodnocování a flexibilní spojování.
Souhrn prostředků, které dovolují relativně pohodlným a bezpečným způsobem manipulovat s uloženými daty (bází dat) - vytvářet, modifikovat nebo aktualizovat je a především v těchto datech vyhledávat požadované informace. Program (lépe řečeno systém programů), kterým se řídí a spravuje báze dat, se obecně označuje jako systém řízení báze dat (SŘBD). Anglicky jsou tyto prostředky označovány jako Database Management System. Termín relační databázový systém se váže ke způsobu organizace dat. Pokud v rámci prostředků poskytovaných databázovým systémem navrhneme určitou konkrétní strukturu zpracovávaných dat a doplníme databázový systém prostředky realizujícími specifické akce nad těmito daty, získáme teprve přímo použitelný počítačový prostředek k uchovávání informací z určité oblasti dění. Takový konkrétní systém se nazývá informační systém nebo někdy aplikace. Pro vlastní použití je ještě potřeba naplnit IS konkrétními daty. Databázový systém umožňuje s daty pracovat dvojím způsobem. Umožňuje člověku, který je alespoň částečně seznámen se základními principy systému a se strukturou uložených dat, přímo zasahovat do těchto dat a poskytuje mu poměrně silné prostředky pro vyhledávání potřebných informací v těchto datech. Poskytuje prostředky, kterými lze připravit ony výše zmíněné specifické akce a specifické povely pro práci s daty - připravit program, který tyto akce či povely realizuje. Častý případ je ten, kdy je uživatel systému téměř úplně izolován od vlastních prostředků databázového systému. Toto uživatelské rozhraní potom může být snáze přizpůsobeno konkrétním potřebám a zvyklostem potencionálních uživatelů IS.
Součástí databázového systému je jazyk, kterým lze tento databázový systém ovládat. Příkazy jazyka lze provádět ve dvou různých režimech. V základním přímém režimu je vydaný příkaz (pokud je syntakticky správný i logicky proveditelný) bezprostředně proveden a systém očekává další příkaz. Strukturovanou posloupnost těchto příkazů jazyka je možno uložit do textového souboru (běžně se pro něj používá extenze .PRG a termín program) a zadat provedení této posloupnosti příkazů v nepřímém režimu. Provedení programu je možné vyvolat jak z přímého režimu, tak i z jiného programu. Základem každého jazyka je souhrn objektů, se kterými je možné manipulovat nebo které je možné vytvářet. Databázový soubor je složený objekt, který je tvořen posloupností záznamů. Každý záznam obsahuje údaje o jistém objektu reálného světa a je tvořen posloupností položek (polí), v nichž jsou uloženy atributy tohoto objektu. Položka má pevné jméno, pevný typ a nabývá vždy nějaké hodnoty tohoto typu. Seznam položek v jednom záznamu, jejich jména a typy jsou dány při definici databázového souboru a jsou shodné pro všechny záznamy daného databázového souboru. Počet záznamů je proměnný podle uložených dat, databázový soubor může být i prázdný, tj. neobsahuje žádný záznam. Jméno databázového souboru je jménem ve smyslu operačního systému. S položkami souborů se pracuje v zásadě stejně, jako se pracuje s proměnnými v jiných programových jazycích. Nejpodstatnější rozdíl je v tom, že hodnoty položek jsou uložené trvalé údaje na paměťovém médiu. Pro práci s dočasnými pracovními hodnotami je možno používat paměťové proměnné a paměťová pole. Proměnná vzniká nejčastěji přiřazením hodnoty, méně často deklarací, zaniká buď explicitním příkazem nebo vymazáním celé oblasti pro paměťové proměnné. Pole je třeba deklarovat a uvést jeho rozměry.
Mnoho databázových systémů o sobě tvrdí, že jsou nejrychlejší, nejkomplexněji pojaté, dají se nejsnadněji adaptovat na konkrétní podmínky, existuje pro ně největší počet již vyvinutých aplikací, mají nejjednodušší údržbu a jsou tudíž ve výsledném součtu i nejlevnější. Existuje samozřejmě velké množství hledisek, podle kterých je lze posuzovat. Skutečností je, že souhrn dostupných informací nezřídka představuje změť nesourodých polopravd, ve které se nezkušený uživatel těžko orientuje. Z inzerce lze obvykle získat informaci o tom, že ten či onen program je nejlepší, případně že je nabízen téměř zdarma nebo o 5 % levněji. Z propagačních materiálů lze obvykle zjistit cenu a výčet funkcí, které program obsahuje. Na výstavách ho lze letem světem spatřit v chodu, takže lze posoudit barevné provedení a úroveň prezentace firmy. Situaci se pokouší zachraňovat různé recenze programů v odborných počítačových časopisech. Zde autoři v první části obvykle popíší vlastní dojmy a na závěr přiloží tabulku, obsahující výčet voleb menu, funkcí a možností systémem má/nemá. Recenze většinou obsahují popis balení produktu při dodávce, pečlivé hodnocení způsobu instalace, hardwarové požadavky, odlišnosti od minulé verze, řešení nedostatků minulé verze, novinky. Přináší též mnoho obrázků s použitím základních funkcí. Odlišnosti v základním pojetí i funkčních možnostech jednotlivých programů mají za následek, že u jednoho programu je vyzdvihováno to a u druhého kritizováno něco úplně jiného. Kritika nedostatků z pohledu recenzenta se navíc pravidelně míjí s připomínkami uživatelů, odvozenými z praxe. Za víceméně objektivní jsou považovány srovnávací testy, ve kterých jsou testované programy použity k řešení stejného úkolu, porovnávají se stejné funkce. Výstupem je i srovnávací tabulka časové odezvy při zatížení programů většími objemy dat, tabulka možností a funkcí, vybavenosti a v neposlední řadě i cena.
Logická struktura dat se řídí třemi databázovými modely - hierarchickým, síťovým a relačním.
Relační algebra. Relační model je založen na přesných výrocích teorie množin a proto umožňuje obzvlášť precizní popis dat. Relační algebra má pro práci s relacemi velký význam. Základními operacemi nad relačními tabulkami jsou projekce, selekce a spojení.
Projekce (project) tabulky definovaná podmnožinou atributů představuje vypuštění některých sloupců neobsažených v požadované množině atributů. Součástí operace projekce je i případné vypuštění duplicitních řádek ve výsledné tabulce. Tabulku lze naopak také rozšířit o sloupec vzniklý nějakou operací nad hodnotami uložených atributů. Projekce relace R na atributy B: pB(R), B je podmnožina atributů R Selekce (select) tabulky definovaná podmnožinou definičního oboru relace (tj. logickou podmínkou charakterizující tuto podmnožinu) představuje průnik relace s touto novou podmnožinou. Selekce relace R podle logické podmínky j: sj(R) Spojení (join) tabulek je vytvoření nové spojené tabulky na základě shodných hodnot atributů v obou tabulkách (spojení záznamů týkajících se téhož objektu do jednoho záznamu za sebe). Podle způsobu porovnávání hodnot ve spojovaných sloupcích se rozlišují 3 druhy spojení - na rovnost (Equijoin), na nerovnost (Theta join) - nemá praktický význam, vnější - inkluse (Outer join). Pokud výsledná relace obsahuje všechny sloupce z první i druhé tabulky, vyjma sloupce, který byl využit pro spojení, pak toto spojení je označováno jako přirozené (Natural join). ? - spojení relací R a S přes sloupce i a j R (i ? j) S ? je jeden z operátorů: <, <=, >, >=, =
Přirozené spojení (NATURAL JOIN) R*S Pro každý atribut Ai který se nachází v R i v S, vyber z R x S ty n-tice, pro které platí R.Ai = S.Ai Vypusť pro každý atribut Ai sloupec S.Ai
Vnější spojení (outer join) dvou relací umožňuje zahrnout do výsledné relace i ty řádky, pro které neexistuje v druhé relaci stejná hodnota ve společném sloupci; chybějícím hodnotám druhé relace se přiřadí hodnota NULL. Zůstanou tak ve výsledné relaci zachovány i ty řádky, které by se při přirozeném spojení ztratily. Vnějším spojením lze tedy do výsledku získat i ty n-tice, které s ničím spojit nelze, přičemž zbývající komponenty n-tic se doplní prázdnými hodnotami. Příklady
Najdi všechny zaměstnance s platem vyšším než 10 000 Kč.
p plat > 10000 (Zaměstnanec)
Vytvoř seznam obsahující číslo zaměstnance, příjmení, jméno a plat
sc_zam, prijmeni, jmeno, plat(Zaměstnanec)
SQL (Structured Query Language) strukturovaný dotazovací jazyk, který byl určen americkým ústavem pro normalizaci (ANSI) jako standard pro komunikaci s relačními databázemi. SQL je nezávislý na datech, protože se uživatel nemusí starat o fyzické uložení dat. Jazyk SQL se používá pro získání informací z databázových souborů, přičemž požadavky na výběr se specifikují popisně. 1970 - E.F.Codd - definice relačního modelu dat, 1974 - první popis dotazovacího jazyka SEQUEL, 1979 - na trh uveden první relační databázový systém ORACLE, 1981 - INGRES, 1982 - SQL/DS, 1982 - zahájení práce na standardu ANSI SQL, 1983 - DB2, 1986 - schválení normy ANSI SQL, 1987 - schválení normy ISO SQL. SQL je neprocedurální jazyk (popíšeme jaká data chceme najít, ne jak to má počítač provést - poradí si sám).
SELECT [DISTINCT | ALL] {* | [vyraz [AS nove_jmeno]] [,...]}
FROM jmeno_tabulky [alias][,...]
[WHERE podminka]
[GROUP BY seznam_sloupcu]
[HAVING podminka]
[ORDER BY seznam_sloupcu]
Příkaz SELECT –základní funkce
Příklad 6.1 Všechny sloupce, všechny řádky Zobraz všechny informace o všech zaměstnancích Znak * lze použít jako zkratku pro ‘všechny sloupce’:
SELECT *
FROM zaměstnanec
Příklad 6.2 -výběr sloupců –projekce Zobraz seznam pracovníků a jejich platy
SELECT jméno, příjmení, plat
FROM zaměstnanec
Příklad 6.3 - Použití DISTINCT Zobrazte čísla nemovitostí, které si některý klient prohlédl
SELECT cNem
FROM prohlídka
DISTINCT eliminuje duplicity:
SELECT DISTINCT cNem
FROM prohlídka
Příklad 6.4 -Vypočítané hodnoty Vypište seznam pracovníků a pro každého uveďte výši ročního platu.
SELECT jméno, příjmení, plat*12 AS roční_plat
FROM zaměstnanec
Příklad 6.5 - Výběr řádků - selekce Zobrazte všechny informace o zaměstnancích s platem větším než 10000 Kč
SELECT * FROM zaměstnanec
WHERE plat >10000
Příklad 6.6 -Výběr řádků Zobraz údaje o pobočkách se sídlem v Praze nebo v Brně
SELECT * FROM Pobočka
WHERE město='Praha' OR město='Brno'
Příklad 6.7 -interval Zobrazte údaje o zaměstnancích s platem v rozmezí 9 000 -12000 Kč.
SELECT *FROM zaměstnanec
WHERE plat BETWEEN 9000 AND 12000
nebo SELECT *
FROM zaměstnanec
WHERE plat>= 9000 AND plat <=1000
lze použít i negaci NOT BETWEEN
Příklad 6.8 -Příslušnost k množině Zobraz údaje o všech managerech a účetních
SELECT *
FROM Zaměstnanec
WHERE PracZar IN ('manager','účetní')
nebo
SELECT *
FROM zaměstnanec
WHERE PracZar='manager' OR PracZar='účetní'
IN je efektivnější, obsahuje-li množina více hodnot. Je možno použít i negaci NOT IN
Příklad 6.9 -Hledání řetězce SQL má dva speciální symboly % posloupnost nula nebo více znak ů _ libovolný jeden znak Najdi zaměstnance, kteří mají v adrese Ostravu.
SELECT *
FROM zaměstnanec
WHERE adresa LIKE '%Ostrava%'
Příklad 6.10 -podmínka s použitím NULL Zobraz detaily všech prohlídek, kde není zaznamenána žádná poznámka
SELECT *
FROM prohlídka
WHERE poznámka IS NULL
Příklad 6.11 - řazení dle jednoho sloupce Zobraz seznam zaměstnanců dle výše platu, od nejvyššího
ISO standard definuje 5 agregačních funkcí: * COUNT() vrátí počet hodnot ve specifikovaném sloupci * SUM() vrátí součet hodnot ve specifikovaném sloupci * AVG() vrátí průměr hodnot ve specifikovaném sloupci * MIN() vrátí nejmenší hodnotu ve specifikovaném sloupci * MAX() vrátí největší hodnotu ve specifikovaném sloupci
Každá z funkcí pracuje s jedním sloupcem a vrací jednu hodnotu COUNT, MIN a MAX se aplikuje na numerické i nenumerické hodnoty, ale SUM a AVG se mohou použít pouze na numerické hodnoty. Kromě COUNT(*), každá funkce nejdříve eliminuje hodnoty NULL a pracuje pouze se zbylými hodnotami COUNT(*) spočítá všechny řádky tabulky bez ohledu na výskyt NULL nebo duplicitních hodnot. Je možno použít DISTINCT před jménem sloupce za účelem eliminace duplicit. DISTINCT nemá vliv na MIN/MAX, ale může mít vliv na SUM/AVG. Agregační funkce mohou být použity pouze v seznamu příkazu SELECT a v klauzuli HAVING.
Příklad 6.12 Použití COUNT(*) U kolika nemovitostí je požadované nájemné vyšší než 8000 Kč?
SELECT COUNT(*) AS počet
FROM nemovitost
WHERE nájem > 8000
Příklad 6.13 Kolik různých nemovitostí si klienti prohlédli v březnu?
SELECT COUNT(DISTINCT cNem) AS počet
FROM prohlídka
WHERE datum >={1.3.99} AND datum <={31.3.99}
(formát datumu není ve všech SQL serverech stejný, např u MySQl funguje >=‘1999-03-31’ ale i jiné formáty)
Příklad 6.14 -Použití COUNT a SUM Najdi počet managerů a součet jejich platů.
SELECT COUNT(cZam) AS počet, SUM(plat) AS PlatCelkem
FROM zaměstnanec
WHERE PracZar ='manager'
Příklad 6.15 - Použití MIN, MAX, AVG Najdi min, maximum a průměrný plat zaměstnanců
SELECT MIN(plat) AS MIN, MAX(plat) AS MAX, AVG(plat) AS průměr
FROM zaměstnanec
Který zaměstnanec má minimální plat?
SELECT prijmeni,MIN(plat)
FROM Zamestnanec
Předchozí příkaz nevrátí správnou hodnotu, nelze míchat ne agregované sloupce s agregovanými. POZOR správně:
SELECT prijmeni, plat FROM Zamestnanec
WHERE plat = (SELECT MIN(plat) FROM Zamestnanec)
Agregační funkce můžeme aplikovat na podmnožiny tabulky vytvořené podle výběrového kritéria. Tabulka se konceptuálně rozdělí na skupiny, pro které je hodnota zvoleného sloupce konstantní. Každá položka v seznamu příkazu SELECT musí mít pouze jednu hodnotu v dané skupině a SELECT může obsahovat pouze: * názvy sloupců * agregační funkce * konstanty * výrazy obsahující kombinaci výše zmíněných
Příklad 6.16 Použití GROUP BY Najdi počet zaměstnancův každé pobočce a jejich celkový plat
SELECT cPob, COUNT(cPob) AS počet, SUM(plat) AS PlatCelkem
FROM zaměstnanec
GROUP BY cPob
ORDER BY cPob
HAVING se používá ve spojení s GROUP BY na filtrování skupin, které budou vybrány do výsledné tabulky. Je podobný jako WHERE, ale WHERE filtruje jednotlivé řádky, zatímco HAVING filtruje skupiny. Názvy sloupců v HAVING se musí objevit také v seznamu GROUP BY, případně musí být obsaženy v agregačních funkcích.
Příklad 6.17 Použití HAVING Pro každou pobočku, která má více než jednoho zaměstnance, najdi počet zaměstnancův každé pobočce a součet jejich platů.
SELECT cPob,COUNT(cZam)AS PočetZam,SUM(plat) AS PlatCelkem
FROM zaměstnanec
GROUP BY cPob
HAVING COUNT(cZam)>1
ORDER BY cPob
Poddotazy (=subqueries) Některé SQL dotazy mohou v sobě obsahovat další SELECT - poddotaz Poddotaz lze použít v části WHERE a HAVING vnějšího SELECTu Poddotazy mohou být požity i v příkazech INSERT, UPDATE, a DELETE. Poddotazy lze využít i v části FROM , na vytvoření dočasné tabulky (např. ...FROM Zamestnanec, (SELECT p.nazev, p.id, a.mesto FROM pobocka p, adresa a WHERE pobocka.psc=mesto.psc) AS pm WHERE .... )
Příklad 7.1 Poddotaz Najdi zaměstnance, kteří pracují v pražských pobočkách
SELECT cZam,Jmeno,Prijmeni,PracZar
FROM Zamestnanec
WHERE cPob IN
(SELECT cPob
FROM Pobocka
WHERE mesto ='Praha')
Příklad 7.2 Poddotaz s agregační funkcí Zobraz jméno,příjmení a plat zaměstnanců,kteří mají plat větší jako průměr a zobraz o kolik
SELECT cZam,Jmeno,Prijmeni,PracZar,
Plat -(SELECT avg(plat) FROM Zamestnanec) AS Rozdil
FROM Zamestnanec
WHERE Plat >(SELECT AVG(Plat) FROM Zamestnanec)
Pravidla pro použití poddotazů
V poddotazu nelze použít ORDER BY Seznam sloupců v poddotazu musí sestávat pouze z jednoho sloupce nebo výrazu kromě poddotazů, obsahujících EXISTS Názvy sloupců se implicitně týkají tabulky která je uvedena za FROM v poddotazu
Příklad 7.3 Vhnízděný poddotaz Zobraz nemovitosti, o které se starají zaměstnanci z Pražské pobočky v Brněnské ulici
SELECT cNem, Ulice, Mesto, PSC, Typ, PocetMistn, Najem
FROM Nemovitost
WHERE cZam IN
(SELECT cZam
FROM Zamestnanec
WHERE cPob =
(SELECT cPob
FROM Pobocka Mesto
WHERE Mesto ='Praha' AND Ulice LIKE '% Brněnská%'))
ANY,SOME a ALL ANY a ALL lze použít s poddotazy, které produkují jeden sloupec čísel. ALL – vrátí TRUE tehdy, když danou podmínku spl ň ují všechny hodnoty vyprodukované poddotazem ANY – vrátí TRUE, když alespoň jedna z hodnot vyprodukovaná poddotazem splňuje podmínku Je-li výsledek poddotazu prázdná množina, tak ALL vrátí hodnotu TRUE, ANY vrátí FALSE SOME lze použít místo ANY
Příklad 7.4 Použití ANY/SOME Najdi zaměstnance, kteří mají plat větší než některý ze zaměstnanců pobočky P01
SELECT cZam,Jmeno,Prijmeni,PracZar,Plat
FROM Zamestnanec
WHERE Plat >
SOME (SELECT Plat
FROM Zamestnanec
WHERE cPob = 'P01')
Příklad 7.5 Použití ALL Najdi zaměstnance, kteří mají plat větší než všichni zaměstnanci pobočky P01
SELECT cZam,Jmeno,Prijmeni,PracZar,Plat
FROM Zamestnanec
WHERE Plat >
ALL (SELECT Plat
FROM Zamestnanec
WHERE cPob = 'P01')
Dotazy na hodnoty z více tabulek Poddotaz se dá použít v případech, že sloupce ve výsledku pocházejí z jedné tabulky Pokud sloupce ve výsledku pocházejí z více tabulek, je nutné použít JOIN (a tedy více tabulek v části FROM) Propojovací sloupce se obvykle specifikují v rámci WHERE
Příklad 7.6 Jednoduchý JOIN Zobrazte jména klientů a jejich komentář při prohlídce nemovitosti.
SELECT K.cKl,Jmeno,Prijmeni,cNem,Poznamka
FROM Klient K,Prohlidka P
WHERE K.cKl =P.cKl
alternativně
SELECT K.cKl,Jmeno,Prijmeni,cNem,Poznamka
FROM Klient K JOIN Prohlidka P ON K.cKl =P.cKl
Příklad 7.7 JOIN a ORDER BY Pro každou pobočku zobrazte číslo a jméno zaměstnance kteří se starají o nějakou nemovitost a číslo příslušné nemovitosti.
SELECT Z.cPob,Z.cZam,Jmeno,Prijmeni,cNem
FROM Zamestnanec Z, Nemovitost N
WHERE Z.cZam =N.cZam
ORDER BY Z.cPob,Z.cZam,N.cNem
Příklad 7.8 Spojení 3 tabulek Pro každou pobočku zobraz zaměstnance, kteří se starají o nějakou nemovitost spolu s městem, v kterém se nachází pobočka a s nemovitostmi o které se starají
SELECT P.cPob,P.Mesto,Z.cZam,Jmeno,Prijmeni,cNem
FROM Pobocka P,Zamestnanec Z,Nemovitost N
WHERE P.cPob=Z.cPob AND Z.cZam=N.cZam
ORDER BY P.cPob,Z.cZam,cNem
Příklad 7.9 Seskupení podle více sloupců Pro každou pobočku najdi počet nemovitostí, o které se starají zaměstnanci
SELECT Z.cPob,Z.cZam,COUNT(*)AS pocet
FROM Zamestnanec Z,Nemovitost N
WHERE Z.cZam =N.cZam
GROUP BY Z.cPob,Z.cZam
ORDER BY Z.cPob,Z.cZam
Příklad 7.10 Levé vnější spojení Zobraz všechny pobočky a k nim nemovitosti které jsou v témže městě jako příslušná pobočka.
SELECT P.*,N.*
FROM Pobocka P LEFT OUTER JOIN Nemovitost N ON P. Mesto =N.Mesto FULL OUTER JOIN
Příklad 7.11 Pravé vnější spojení Zobraz všechny nemovitosti a ke každé nemovitosti také pobočku, ležící v témž městě .. code-block:: sql
SELECT P.*,N.* FROM Pobocka P RIGHT OUTER JOIN Nemovitost N ON P.Mesto =N.Mesto
Příklad 7.12 Pro každou pobočku zobraz všechny zaměstnance a ke každému zaměstnanci také číslo nemovitostí o které se stará
SELECT Z.cPob,Z.cZam,Jmeno,Prijmeni,cNem
FROM Zamestnanec Z LEFT OUTER JOIN Nemovitost N ON Z.cZam =N.cZam
ORDER BY Z.cPob,Z.cZam,N.cNem
Příklad 7.13 Pro každou pobočku zobraz všechny zaměstnance a ke každému zaměstnanci také počet nemovitostí o které se stará
EXISTS a NOT EXISTS lze použít pouze s poddotazy Jako výsledek produkují pouze true/false True právě tehdy, když existuje alespoň jeden řádek v tabulce, kterou vrátí poddotaz. False právě tehdy,když poddotaz vrátí prázdnou tabulku. Protože EXISTS testuje pouze existenci řádků ve výsledné tabulce poddotazu, výsledek poddotazu může obsahovat libovolný počet sloupců
Příklad 7.14 Použití EXISTS Najdi zaměstnance,kteří pracují v pražskýchpobočkách
SELECT cZam,Jmeno,Prijmeni,PracZar
FROM Zamestnanec Z
WHERE EXISTS (SELECT * FROM Pobocka P WHERE Z.cPob=P.cPob AND Mesto='Praha')
Podmínka Z.cPob =P.cPob je nutná aby se uvažovali správné záznamy v pobočce pro každého zaměstnance Kdybychom tuto podmínku vypustili, tak bychom v poddotazu dostali všechny záznamy Zamestnance:
SELECT *
FROM Pobocka WHERE Mesto='Praha'
Podmínka by byla vždy TRUE a dotaz by byl:
SELECT cZam,Jmeno,Prijmeni,PracZar
FROM Zamestnanec
WHERE TRUE
Příklad 7.14b Jiná formulace dotazu z Příkladu 7.14
SELECT cZam,Jmeno,Prijmeni,PracZar
FROM Zamestnanec Z,Pobocka P
WHERE Z.cPob=P.cPob AND Mesto='Praha'
Příklad 7.15 Použití UNION Zobrazte města, kde je pobočka nebo nemovitost.
SELECT Mesto
FROM Pobocka
WHERE Mesto IS NOT NULL)
UNION
(SELECT Mesto
FROM Nemovitost
WHERE Mesto IS NOT NULL)
Modifikace dat - UPDATE
UPDATE JménoTabulky
SET JménoSloupce1=hodnota1 [,JménoSloupce2=hodnota2...]
[WHERE Podmínka]
JménoTabulky může být jméno tabulky nebo upravitelného pohledu SET specifikuje názvy sloupců, jejichž hodnoty mají být modifikovány
UPDATE WHERE je volitelné: není-li uvedeno, tak se modifikují všechny hodnoty vyjmenovaných sloupců je-li uvedeno,tak se modifikují hodnoty pouze těch řádků, které vyhovují zadané podmínce
Příklad 8.1 Modifikace hodnot 1 sloupce Máme zvýšit o 3% plat všem zaměstnancům.
UPDATE Zamestnanec
SET Plat =Plat*1.03
Máme zvýšit o 5% plat všem managerům.
UPDATE Zamestnanec
SET Plat =Plat*1.05
WHERE PracZaR ='Manager'
Příklad 8.2 Modifikace hodnot více sloupců Uprav pracovní zařazení pracovníka Oskara Pilného (cZam=’Z0102’) na managera a uprav jeho plat na 30000.
Odstranění dat - DELETE
DELETE FROM JménoTabulky
[WHERE podmínka]
JménoTabulky může být jméno základní tabulky nebo pohledu Podmínka je volitelná Není-li podmínka uvedena, tak se odstraní všechny řádky tabulky Je-li podmínka uvedená,tak se odstraní pouze ty řádky, pro které je podmínka splněná.
Příklad 8.3 - DELETE Vymažte všechnyřádky tabulky Prohlidka, týkající se nemovitosti N0001.
DELETE FROM Prohlidka
WHERE cNem ='N0001'
POZOR: DELETE FROM Prohlidka Vymaže všechny řádky tabulky Prohlidka
Pohled je dynamický výsledek jedné nebo více relačních operací realizovaných nad základními relacemi. Virtuální relace která vlastně neexistuje v databázi, ale vytvoří se na vyžádání v okamihu vyžádání. Obsah pohledu je definován jako dotaz na jednu nebo více základních relací. Všechny operace nad pohledem jsou automaticky transformovány do operací nad relacemi z kterých byl pohled odvozen. Pohled je dotaz, uložený v databázi jako trvalý objekt. Jako pojmenovaný pohled můžeme uložit prakticky libovolný příkaz SELECT.
Obvyklé Příklady pohledů: * podmnožina řádků nebo sloupců zákl. tabulky * spojení tabulek * statistický souhrn vytvořený ze zákl.tabulky * kombinace pohledů
Výhody pohledů * Soustřeďují potřebná data pro uživatele * Skrývají složitost podkladových dat * Zjednodušují správu uživatelských oprávnění * Definují uspořádání dat pro export do jiných aplikací
Vytvoření pohledu - CREATE VIEW
CREATE VIEW jméno_pohledu [(jméno_sloupce[,...])] AS DefinujícíDotaz
[WITH [CASCADED |LOCAL]CHECK OPTION]
Může přiřadit jméno každému sloupci pro každý sloupec v pohledu. Je-li seznam sloupců specifikován,musí mít stejný počet položek jako je počet sloupců produkovaný v rámci DefinujícíhoDotazu Chybí-li,každý sloupec má jméno příslušného sloupce z DefinujícíhoDotazu. Seznam musí být specifikován, může-li vzniknout nejasnost týkající se jména sloupce. WITH CHECK OPTION zajišťuje, že když řádek nesplňuje WHERE klauzuli definujícího dotazu, tak se nepřidá do příslušné základní tabulky
Příklad 8.4 - Create View Vytvořte pohled tak, aby manager pobočky P01 viděl detaily pouze pracovníků své pobočky.
CREATE VIEW Zam01 AS SELECT * FROM Zamestnanec
WHERE cPob ='P01'
WITH CHECK OPTION
Příklad 8.5 - Create View Vytvoř pohled na údaje zaměstnanců pobočky P01 s vyloučením platu.
CREATE VIEW Zam01X
AS SELECT cZam,Jmeno,Prijmeni,PracZar
FROM Zamestnanec
WHERE cPob ='P01'
Příklad 8.6 Vytvoř pohled,kde bude přehled zaměstnanců, kteří se starají o nějakou nemovitost,číslo příslušné pobočky ve které pracují, cZam a počet nemovitostí, o které se každý stará.
CREATE VIEW Přehled (cPob,cZam,pocet)
AS SELECT Z.cPob,Z.cZam,COUNT(*)
FROM Zamestnanec Z,Nemovitost N
WHERE Z.cZam=N.cZam
GROUP BY Z.cPob,Z.cZam
Odstranění pohledu -DROP VIEW
DROP VIEW JménoPohledu
[RESTRICT |CASCADE]
Odstraní z databáze definici pohledu Příklad:[[code format=”sql”]] DROP VIEW Zam3code
Postup při použití pohledů O kolik nemovitostí se stará každý ze zaměstnanců pobočky P01 (pohled Prehled z Příkladu 8.6)?
SELECT cZam,pocet
FROM Prehled
WHERE cPob ='P01'
ORDER BY cZam
Jak to DB systém zpracuje: (a) Jména sloupců v seznamu příkazu SELECT se nahradí jmény sloupců v příslušném definujícím dotazu:
SELECT Z.cZam AS cZam,COUNT(*) AS počet
FROM Zamestnanec Z, Nemovitost N
WHERE Z.cZam=N.cZam AND cPob=’P01’
GROUP BY Z.cPob,Z.cZam
ORDER BY Z.cZam
SELECT Z.cZam AS cZam,COUNT(*)AS Počet FROM Zamestnanec Z,Nemovitost N WHERE Z.cZam=N.cZam AND cPob=’P01’ GROUP BY Z.cPob,Z.cZam ORDER BY Z.cZam
Omezení pohledů Definice a použití pohledů má určitá omezení: (a) je-li sloupec v pohledu založen na agregační funkci:
Například následující dotazy jsou chybné:
SELECT COUNT(pocet)
FROM Prehled
SELECT *
FROM Prehled
WHERE pocet >2
(b) Pohled používající seskupení nelze spojit se základní tabulkou nebo s jiným pohledem Například pohled Přehled
CREATE VIEW Přehled (cPob,cZam,pocet)
AS SELECT Z.cPob,Z.cZam,COUNT(*)
FROM Zamestnanec Z,Nemovitost NWHERE Z.cZam =N.cZam
GROUP BY Z.cPob,Z.cZam
Všechny úpravy základní tabulky se promítnou do všech pohledůvytvořených nad touto tabulkou Analogicky bychom očekávali,že je-li upraven pohled, tak se příslušná změna objeví v základní tabulce.
Uvažujme pohled Prehled. Pokusíme-li se přidat záznam,že v pobočce P01 zaměstnanec Z0006 se stará o 2 nemovitosti:
INSERT INTO Prehled
VALUES ('P01','SG5',2)
To by ale znamenalo,že máme přidat 2 záznamy do tabulky Nemovitost, kde bude také uvedeno o které nemovitosti se stará (jejich identifikace) –to ale neznáme
ISO specifikuje pohledy, které musí být upravitelné Pohled je upravitelný právě tehdy, když:
Aby pohled byl upravitelný,musí DBMS být schopen identifikovat příslušný řádek nebo sloupec v základní tabulce.
Transakce je logická jednotka práce sestávající z jednoho nebo více SQL příkazů které jsou atomické z hlediska zotavení se z chyb. Změny,které realizuje jedna transakce,nejsou viditelné pro ostatní konkurenčně probíhající transakce, pokud daná transakce neskončí Transakce je i jednotka zotavení se z chyb Každá změna v DB se zapisuje do transakčního protokolu –„logu“ Při výpadku systému DBS automaticky obnoví data pomocí dat v transakčním logu.
Vytvoření transakce
BEGIN TRANSACTION
Příkaz1
Příkaz2
COMMIT resp.
ROLLBACK
COMMIT končí transakci úspěšně a změny jsou trvale zaznamenány ROLLBACK přeruší transakci a všechny změny se anulují, DB se vrátí do stavu před transakcí
Privilegia (oprávnění) uživatelů Privilegia jsou akce, které uživatel může realizovat nad základní tabulkou nebo pohledem: SELECT INSERT UPDATE DELETE REFERENCES
INSERT/UPDATE/REFERENCES může být omezeno na vyjmenované sloupce. Vlastník tabulky musí udělit ostatním uživatelům potřebná privilegia použitím Na vytvoření pohledu musí uživatel mít SELECT privilegium na všechny tabulky nad kterými vytváří pohled a REFERENCES privilegium na jmenované sloupce.
Udělení práv v SQL - GRANT
GRANT {seznam_privilegií |ALL PRIVILEGES}
ON jméno_objektu
TO {seznam_autorizační_id|PUBLIC}
[WITH GRANT OPTION]
ALL PRIVILEGES udělí všechna privilegia PUBLIC umožňuje přístup všem jméno_objektu může být základní tabulka,pohled,doména WITH GRANT OPTION umožňuje odevzdat privilegia
Příklad 8.7 - GRANT
GRANT ALL PRIVILEGES
ON zamestnanec
TO manager WITH
GRANT OPTION GRANT SELECT, UPDATE (salary)
ON zamestnanec
TO admin
GRANT SELECT
ON pobocka
TO PUBLIC
Odebrání práv v SQL - REVOKE REVOKE odebere privilegia udělená příkazem GRANT
REVOKE [GRANT OPTION FOR] {seznam_privilegií |ALL PRIVILEGES}
ON jméno_objektu
FROM {seznam_autoriz_id |PUBLIC}
[RESTRICT |CASCADE]
Příklad 8.8 -REVOKE
REVOKE SELECT
ON pobočka
FROM PUBLIC REVOKE ALL PRIVILEGES
ON zamestnanec FROM zástupce