1 Úvod Zpracování dat můžeme definovat jako obsažné a účelné sestavení dat provedené strojem ze zadaných údajů. Cílem je nejen ušetřit lidskou práci a...
Zpracování dat můžeme definovat jako obsažné a účelné sestavení dat provedené strojem ze zadaných údajů. Cílem je nejen ušetřit lidskou práci a čas, ale zejména zabránit možným chybám. Výsledkem zpracování dat je potom jejich transformace na relevantní informace. Nejefektivnějším způsobem zpracování dat je zpracování na základě databázového přístupu. Naším úkolem je nejen navrhnout správnou logickou strukturu databáze, ale i zabezpečit různorodé požadavky uživatelů požadujících informace, to znamená vytvořit v konkrétním databázovém stroji určité fyzické objekty pro uchování dat a manipulaci s nimi. K tomu jsou zapotřebí jednak základní znalosti z teorie databází, jednak znalost programování v jazyce pro definici dat a pro manipulaci s daty. Vhodným prostředkem pro zvládnutí obecného programovacího jazyka může být aplikace s grafickým uživatelským rozhraním. V praxi je často velice účelný přístup k databázi z libovolného standardního programovacího prostředí. Stanoveným úkolům je podřízena struktura těchto učebních textů. První část seznamuje s teorií databázového přístupu. Druhá část si klade za cíl aplikovat získané teoretické znalosti v praxi, a to na velice rozšířeném databázovém systému s vysoce intuitivním grafickým uživatelským rozhraním. Zejména pro tyto vlastnosti, ale také pro kompatibilitu s univerzálním databázovým jazykem SQL, byl zvolen Microsoft Access. Třetí kapitola je věnována právě jazyku SQL a kapitola čtvrtá jeho použití v jednom ze zástupců databázových serverů, konkrétně v systému MySQL. Poslední kapitola čtenářům ukáže, jak k databázi přistupovat z programovacího prostředí, například ze skriptů webových stránek. Předpokladem pro zvládnutí látky jsou znalosti ovládání standardních kancelářských aplikací, základy programování a pochopení podstaty programování objektového. Cílem nebylo napsat vědeckou publikaci o teorii databází, ale ukázat čtenářům cestu k praktickému zvládnutí dané problematiky. Na rozdíl od klasických publikací často obsahujících zdrojové kódy relativně složitých aplikací je důraz kladen na jednoduchost a možnost vyzkoušet většinu úloh na počítači se standardní programovou výbavou. .
-2-
2.
Teorie databázových systémů 2.1.
Databázový přístup
Pořádání dat provádí každý, kdo používá diář, šanony či desky s různými dokumenty. Pokročilejší způsob je pořádání dat pomocí kartotéky, kterou ve stále větší míře nahrazuje počítač. Ten má navíc tu výhodu, že dokáže data vyhledávat podle různých kritérií (samozřejmě s pomocí vhodného softwaru). Tento způsob je vlastní databázovému přístupu. Databázi můžeme v prvním přiblížení definovat jako množinu záznamů a souborů, které jsou organizovány za určitým účelem. Tento soubor informací se může skládat z různých znaků, čísel, diagramů a dalších objektů. Systematická struktura umožňuje, aby tyto informace mohly být vyhledávány pomocí počítače Při běžné práci se s potřebou pořádání dat setkáváme na každém kroku, většinou se tak děje prostřednictvím tabulek. Příkladem může být telefonní seznam, slovník, v počítači máme seznam adres svých známých, archivujeme odeslanou a přijatou poštu, nabídku Start nebo Oblíbené položky můžeme pokládat za databázi zástupců. Přehled o účtech můžeme mít uspořádaný ve formě souborů tabulkového procesoru. Databáze jsou základem operativních dat podniků, systémů pro správu letového provozu, databázově se zpracovávají data energetických systémů, údaje o meteorologické situaci nebo výsledky vědeckých experimentů. V jednodušších případech stačí souborový přístup, kdy data ukládáme do jednotlivých souborů a ty pak vhodně uspořádáme do odpovídající adresářové struktury. Komplikace nastávají, jednak když počet spravovaných položek dosáhne určité hranice, jednak když chceme zjistit další informace, například údaje o všech zákaznících a jejich objednávkách, nebo data o všech výsledcích daného fyzikálního experimentu. Problémem může být udržování vztahů mezi soubory, pokud ukládáme nové informace. Musíme navíc zajistit, aby data byla vkládána správně, a to i v případě, že je současně vkládá několik uživatelů. V tomto případě potřebujeme systém pro správu databází (Database Management System – DBMS; česky se někdy označuje též systém řízení bází dat, SŘBD). Souborový i databázový přístup mají společné to, že data jsou organizována do souborů; u klasického přístupu je však popis souborů součástí uživatelských programů a veškerá správa dat vyžaduje samostatně naprogramovanou aplikaci. Velkou nevýhodou jsou zejména omezené prostředky pro vytváření vazeb mezi jednotlivými záznamy souborů. Jádrem databázového přístupu je odtržení definic dat a jejich údržby od uživatelských programů. Data jsou uchovávána v centrálně spravované struktuře. Databáze navíc odstraňují další nedostatky souborového přístupu. Hlavní přínosy můžeme shrnout do těchto bodů: • • • • • • •
Odstranění redundance (nadbytečnosti) a inkonzistence dat (data v databázi se musejí nacházet v konzistentním, tj. bezesporném stavu) Vyřešení problémů s přístupem k datům Neizolovanost dat – data jsou uložena na stejném místě ve stejném formátu Možnost zabezpečit integritu dat (kontrolou vstupujících údajů) Zajištění jedinečnosti aktualizací Umožnění současného přístupu více uživatelů (sdílení) Rozvinutá bezpečnostní politika
Systém správy databáze (DBMS) pracuje s daty uchovávanými v tabulkách. Řádek dat reprezentuje konkrétní subjekt, entitu (např. osobu, knihu, objednávku), sloupec pak konkrétní
-3druh informace o tomto subjektu, atribut (např. jméno, datum narození). Systém správy databáze se skládá ze tří hlavních funkcí: • • •
Definice dat – jaká data budou v databázi uložena, jejich typ (např. znaky nebo čísla) a vztahy mezi nimi. Způsob formátování a ověřování integrity Manipulace s daty – vybírání, filtrování, řazení, další operace s nimi, aktualizace Řízení dat – nastavování oprávnění na operace s daty, definování způsobu sdílení a současné aktualizace několika uživateli
Databáze se mění v závislosti na skutečnosti, kterou reprezentuje; hovoříme o stavu databáze. Databáze existuje nezávisle na aplikačních programech. Spolu se systémem pro správu databází tvoří databázový systém.
2.1.1.
Relační databáze
Téměř všechny současné systémy správy databází pracují s daty za pomoci relačního modelu. Každý záznam v databázi obsahuje informace vztažené jednoznačně k jedinému subjektu. Bylo by zbytečné ukládat informace o jméně a adrese zákazníka s každou objednávkou – v relačním systému obsahuje každá informace o objednávkách pouze odkaz na informace o zákazníkovi (realizovaný např. polem číslo zákazníka). Data o dvou třídách informace (např. zákazníci a objednávky) mohou být zpracovávána jako jediná entita založená na vztažených datových hodnotách.
2.2.
Technologie databáze
Relační databáze nemají v reálném světě žádnou analogii, jejich úkolem však je modelovat určitý aspekt reálného světa, tzv. prostor problému. Tento složitý a komplexní prostor omezíme na konkrétní množinu objektů a jejich vzájemných vztahů; vytváříme datový model. Ten zahrnuje definice typů dat a vztahů mezi nimi. Fyzické rozvržení systému potom definuje databázové schéma. Není to nic jiného než myšlenkový model převedený do fyzické reprezentace, kterou je možné implementovat ve zvoleném systému pro správu databází. Databázový stroj potom vytvoří podle představ programátora určité fyzické objekty, do nichž je možné ukládat data. Databázi chápeme jako sjednocení takto vytvořené struktury a vlastních dat. Mohou ji tedy tvořit tabulky, definované pohledy na data, dotazy, uložené procedury a pravidla, jejichž vynucováním zajišťuje databázový stroj ochranu dat. Pojem databáze nezahrnuje samotný databázový stroj ani aplikaci, která se může skládat z formulářů a sestav.
-4Databázový systém Aplikace Databázový stroj
formuláře a sestavy, s nimiž pracuje uživatel program, který provádí vlastní manipulaci s daty – ukládá je na disk a načítá fyzická implementace schématu a dat
Databáze
Databázové schéma
Datový model
Prostor problému
popisuje datový model vůči databázi
myšlenkový model prostoru problému
část reálného světa
Obr. 1 Terminologie relačních databází
2.2.1.
Jazykové prostředky
Ke správnému fungování databáze je zapotřebí existence dvou typů jazyků: • •
Jazyk pro definici dat (Data definition language; DDL) – vytváření všech definic uživatelských dat potřebných v aplikaci; vytváří logické schéma DB Jazyk pro manipulaci s daty (Data manipulation language; DML) – slouží k aktualizaci dat (přidávání, odstraňování, změny) a k výběru dat podle daných požadavků (dotazování)
Dnešní relační systémy používají jako základní jazyk SQL, který zahrnuje nejen oba typy jazyků, ale i další možnosti (např. udělování oprávnění uživatelům).
2.2.2.
Transakční zpracování
Transakce je posloupnost databázových operací, která představuje logickou jednotku práce. Transakce musí převést databázi z jednoho konzistentního stavu do druhého. Správa transakcí zajišťuje, že databáze zůstává v konzistentním stavu nezávisle na selhání systému a selhání transakcí. Správa souběžnosti (Concurrency-control manager) ovládá interakci mezi současně probíhajícími transakcemi, aby zajistila konzistenci databáze.
2.2.3.
Uživatelé databáze
Uživatelé jsou rozděleni podle toho, do jaké míry pracují se systémem •
Správce databáze – koordinuje všechny aktivity v databázovém systému; má dobré znalosti o informačních prostředcích a potřebách podniku. Definuje schéma databáze, strukturu ukládání a metody přístupu, přiděluje uživatelům práva přístupu k databázi, specifikuje meze integrity, monitoruje výkon a zodpovídá za změny ve vybavení
-5•
Aplikační programátoři – vytvářejí uživatelské aplikace
•
Chytří nebo speciální uživatelé – formulují dotazy v databázovém dotazovacím jazyku
•
Naivní uživatelé – spouští jeden z předem napsaných aplikačních programů
2.3.
Modelování dat
Databázový model je sada nástrojů pro popis dat, vztahů mezi nimi a zajištění integrity. Výsledkem modelování je schéma databáze. Existuje několik modelů, každý z nich má své přednosti i nedostatky. Základními modely jsou model vztahu mezi entitami (Entityrelationship model) a relační model.
2.3.1.
Model vztahu mezi entitami (Entity-relationship model)
•
Entita – objekt reálného světa, který je schopen nezávislé existence a je jednoznačně odlišitelný od ostatních objektů. V tabulce je reprezentován řádkem Entita může být konkrétní (např. určitý zákazník je entitou typu zákazník) nebo abstraktní (většinou modeluje vztah mezi jinými entitami – např. zákazník provádí nákup výrobku).
•
Entitní typ je množina entit stejného typu, které sdílejí stejné vlastnosti. (např.: skupina všech osob, společností, knih). Entita je potom konkrétní instancí entitního typu.
•
Vztah – vazba mezi dvěma (nebo více) entitami
•
Vztahový typ – množina vztahů
•
Atribut – funkce přiřazující entitám či vztahům hodnotu určující nějakou podstatnou vlastnost entity nebo vztahu. V tabulce je reprezentován sloupcem (např. jméno, adresa, datum narození). Např.: zákazník = (jméno, příjmení, ulice, město) účet = (číslo účtu, zůstatek)
Databáze může být modelována jako množina entit a vztahy mezi entitami Oddělení
Zaměstnává
Zaměstnanec
Technické oddělení
Josef Novák Petr Malý
Obchodní oddělení
Jana Novotná Pavel Dvořák Jan Berger
Obr. 2 Diagram výskytů entit a vztahů
-6Diagram na obr. 2 ukazuje příklad vztahového typu Zaměstnává mezi dvěma entitními typy, Oddělení a Zaměstnanec. Dva výskyty (instance) entitního typu Oddělení a pět instancí entitního typu Zaměstnanec je spojeno pěti výskyty vztahového typu Zaměstnává. Stejně jako entitní typ, i vztahový typ může mít své vlastní atributy. Ukažme si to na dalším příkladu. Vztah (přesněji vztahový typ) Má_na_programu mezi entitními typy Kino a Film může mít atribut Datum. Vztahové typy, které zahrnují dva entitní typy, se nazývají binární (nebo stupně 2). Většina vztahů v databázovém systému je binární. Bývá zvykem znázorňovat entitní typy a vztahy mezi nimi v tzv. diagramech typů entit a vztahů, neboli E-R diagramech. Režisér
Datum Název Název
Adresa
Kino
Má_na_programu
Rok_výroby
Film
Obr. 3 Diagram typů entit a vztahů, E-R diagram
• • • • • • •
Komponenty E-R diagramu Obdélníky reprezentují množiny entit. Elipsy reprezentují atributy. Kosočtverce reprezentují vztahové typy. Čáry spojují atributy s množinami entit a množiny entit s množinami vztahů. Dvojité elipsy reprezentují atributy s násobnou hodnotou. Vyšrafované elipsy označují odvozené atributy. Atributy primárního klíče jsou podtržené. Pro stručnost se někdy využívá formy lineárního textového zápisu: E: Kino(Název, Adresa); Film(Název, Režisér, Rok_výroby) R: Má_na_programu(Kino, Film)
Entitní typy u vztahů nemusí být rozdílné (obr. 4), takový typ vztahu nazýváme rekurzivní. Popisky manažer a pracovník na obr. 4 jsou nazývány role; specifikují, jak na sebe entity typu Zaměstnanec vzájemně působí přes vztah (přesněji vztahový typ) Pracuje_pro
-7Adresa Jméno
Telefon
manažer Pracuje_pro
Zaměstnanec
pracovník Obr. 4 Příklad E-R diagramu, role
2.3.1.1.
Kardinalita vztahu (poměr)
Můžeme rozlišit několik typů vztahu mezi dvěma entitami, a to podle tzv. kardinality, neboli počtu instancí jedné entity, které můžeme asociovat s jednou instancí jiné entity. Do ER diagramů kardinalitu vztahu označujeme vepsáním znaků 1 nebo N do čáry spojující entitní typ a vztah. Často bývá zvykem psát místo písmena N symbol pro nekonečno či používat jiné grafické symboly. Poměr 1:1 Jednomu záznamu v první tabulce odpovídá maximálně jeden záznam ve druhé tabulce a naopak jednomu záznamu ve druhé tabulce odpovídá maximálně jeden záznam v tabulce první. Vztah 1:1 můžeme použít při rozdělení rozsáhlé tabulky, při oddělení části tabulky z důvodů zabezpečení nebo při uložení informací, které mají vztah pouze k části hlavní tabulky. Příklad (obr.5): • •
Dané kino dává maximálně jeden film Daný film se hraje maximálně v jednom kině
Název kina jednoznačně určuje jméno filmu (je jeho determinantem) a naopak. Daný typ vztahu může zahrnovat i případy 1:0 a 0:1 – kino nehraje nebo film není dáván.
Kino
1
Má_na_programu
1
Film
Obr. 5 Poměr 1:1
Poměr 1:N Vztah 1:N je nejobvyklejším typem vztahu. Záznamu v první tabulce může odpovídat více záznamů v tabulce druhé, ale záznamu v druhé tabulce odpovídá maximálně jeden záznam v tabulce první
-8Příklad (obr.6): • •
Kino může dávat více filmů Daný film (např. konkrétní kopie) je na programu maximálně v jednom kině
V tomto případě název kina není determinantem jména filmu, jméno filmu ale je determinantem názvu kina.
1
Kino
Má_na_programu
∞
Film
Obr. 6 Poměr 1:N
Můžeme však uvažovat i odlišnou situaci, kdy vztah bude mít stejnou kardinalitu (resp. poměr N:1 při daném pořadí relací): • •
Kino může dávat (v jednom okamžiku) maximálně jeden film Daný film může být na programu ve více kinech Poměr M:N
Ve vztahu typu M:N odpovídá jednomu záznamu v první tabulce více záznamů v druhé tabulce a naopak jednomu záznamu v druhé tabulce více záznamů v tabulce první Příklad (obr.7): • •
Kino může dávat více filmů Daný film může být na programu ve více kinech Název kina není determinantem jména filmu, jméno filmu není determinantem názvu
kina.
Kino
∞
Má_na_programu
∞
Film
Obr. 7 Poměr N:N
2.3.1.2.
Dekompozice vztahu M:N
V praxi není vždy nejvhodnější popisovat vztahy typu M:N. Dá se dokázat, že každý vztah M:N se dá rozložit na dva snadněji popisovatelé vztahy 1:N. Rozložení spočívá v tom, že vztah (např. promítání filmu) budeme chápat jako entitu. Novému entitními typu říkáme průnikový entitní typ (obr. 5, 6)
-9-
Režisér Datum
Název
Název
Adresa
∞
Kino
Rok_výroby
∞
Má_na_programu
Film
Obr. 8 Vztah M:N
Nové vztahy jsou oba 1:N; kino může mít více programů, daný program je dáván pouze v jednom kině. Film může být na více programech, na programu je pouze jeden film: Režisér
Název
Datum Název
Adresa
Číslo
Kino
Film
1 dává
Rok_výroby
1 ∞
Program
∞
je_dáván
Obr. 9 Rozložení vztahu M:N na dva vztahy 1:N
2.3.1.3.
Existenční závislost
Závisí-li existence entity A na existenci entity B, pak entita A se nazývá existenčně závislá na B. • •
B je dominantní entita (např. půjčka) A je podřízená entita (např. platba) Je-li entita půjčka smazána, pak všechny s ní spojené entity platba musí být smazány
také.
- 10 2.3.1.4.
Identifikační klíč
Každá entita musí být jednoznačně identifikovatelná (např. rodné číslo u osob) Identifikační klíč entitního typu je množina jednoho nebo více atributů, jejichž hodnoty jednoznačně určují entitu Kandidátní klíč entitního typu je minimální identifikační klíč (po odebrání dalšího atributu by již nebyl identifikačním klíčem); pokud se skládá pouze z jednoho atributu, hovoříme o jednoduchém klíči (např. rodné číslo nebo jméno účtu). Ostatní klíče jsou složené. Protože může existovat několik kandidátních klíčů, jeden z nich je vybrán jako primární klíč (v E-R diagramech podtrženo). Každý výskyt vztahu může být identifikován klíči entit, které v něm vystupují (např. vztah Má_na_programu mezi entitami typu Kino a Film je určen identifikátory konkrétního kina a filmu) Přiklad: (rodné_číslo, číslo_účtu) je primární klíč vztahového typu má_na_ účtu 2.3.1.5.
Slabé množiny entit
Entitní typ, který nemá primární klíč, se nazývá slabý entitní typ. Je to takový entitní typ, který nemůže existovat samostatně a vždy musí mít účast v nějakého vztahu. Existence slabého entitního typu tedy závisí na existenci silného entitního typu; musí být spojena se silným typem, a to vztahem 1:N. Parciální (částečný) klíč slabého entitního typu je množina atributů, která se liší ve všech entitách slabého typu (v E-R diagramech podtrženo čárkovaně). Primární klíč slabého typu je tvořen primárním klíčem silného typu, na němž je tento typ závislý, a parciálním klíčem tohoto slabého typu. Na obr. 10 je atribut číslo splátky parciálním klíčem entitního typu splátka. Primární klíč pro entitní typ splátka je (číslo půjčky, číslo splátky) datum splátky
číslo půjčky
číslo splátky
částka
půjčka
půjčkasplátka
splátka-částka
splátka
Obr. 10 Slabé entitní typy vyznačujeme dvojitým obdélníkem
2.3.1.6.
Redukce E-R schématu na tabulky
Primární klíče umožňují vyjádřit entitní a vztahové typy jako tabulky reprezentující obsah databáze. Databáze, která odpovídá E-R diagramu, může být reprezentována jako kolekce tabulek. Tabulku chápeme jako souhrn sloupců, které popisují jednotlivé vlastnosti položek (atributy). Z jiného pohledu je to souhrn řádků, které popisují jednotlivé entity. Často o nich hovoříme jako o záznamech. Průsečík řádku a sloupce tabulky nazýváme polem a to je
- 11 určeno pro uložení uchovávaných hodnot (pod pojmem pole se ale často rozumí celý sloupec). Pro každý entitní a vztahový typ je jedinečná tabulka, která je spojená se jménem odpovídajícího entitního nebo vztahového typu. Každá tabulka má počet sloupců odpovídající atributům, které mají jedinečná jména. Převod E-R diagramu na tabulku je základ pro odvození designu relační databáze z E-R diagramu. Dotazem rozumíme vyvolání informací z jedné nebo více tabulek, vizuální zobrazení dotazu připomíná další tabulku. Na rozdíl od ní však není nositelem dat. Reprezentace entitních typů tabulkami Silná množina entit se převede na tabulku, kde každý sloupec představuje odpovídající atribut. jméno Novák Novotná Dvořák
rodné číslo 720508/1508 785214/0509 651129/1239
ulice Alejní Poláčkova Evropská
město Teplice Ústí nad Labem Praha
Tabulka zákazník Slabá množina entit se převede na tabulku, která zahrnuje sloupec pro primární klíč identifikační silné množiny entit. V tabulce splátka je jím číslo půjčky, parciálním klíčem je číslo splátky. číslo půjčky 04125 04284 05120
číslo splátky datum splátky částka splátky 11 18.7.2002 2500 18 25.7.2002 3000 24 30.7.2002 8000
Tabulka splátka Reprezentace vztahových typů tabulkami Existuje-li mezi dvěma entitami vztah s nenulovým počtem atributů, je nutno vytvořit novou vazební entitu. Pro modelování vztahu 1:N zahrneme kandidátní klíč z jedné relace (tzv. primární relace) do relace druhé (tzv. cizí nebo nevlastní). V cizí relaci říkáme tomuto klíči cizí klíč (na obr. 11 např. Divize v tabulce Osoby).
Vztahový typ M:N je reprezentován jako tabulka se sloupci pro primární klíče dvou účastnících se entitních typů a popisné atributy vztahu. rodné číslo 720508/1508
číslo účtu 12841144502/800
datum přístupu 12.11.2002
Tabulka má_vloženo Na obr. 12 je ukázka tabulkové reprezentace dekomponovaného vztahu M:N v aplikaci Access. Pole Vyhledávání Filmy a Vyhledávání Kina jsou nevlastní indexy.
Obr. 12 Tabulková reprezentace vztahu M:N po dekompozici
Tabulka odpovídající vztahovému typu spojujícímu slabý entitní typ s jeho identifikačním silným typem je redundantní.
2.3.2.
Relační model dat
Relační model dat (RMD) je založen na matematickém pojmu relace. Nechť Ai je jméno atributu a ai hodnota tohoto atributu (i = 1, …, n). Potom množinu prvků (a1, a2, …, an) nazveme relací řádu n.
- 13 Doménou Di atributu nazveme specifikovanou množinu hodnot, kterých může atribut nabývat: Di = dom(Ai). Atribut je potom dán dvojicí Ai:Di. Relaci r můžeme z matematického hlediska chápat jako podmnožinu kartézského součinu D1 × D2 × … × Dn. Schéma relace se dá zapsat jako uspořádaná n-tice (vektor) atributů: R = (A1:D1, A2:D2, …, An:Dn,). Příklad: je-li jméno = {Novák, Novotná, Dvořák, Smetana} ulice = {Alejní, Poláčkova, Evropská} město = {Teplice, Ústí nad Labem, Praha}, pak r = {(Novák, Alejní, Teplice), (Novotná, Poláčkova, Ústí nad Labem), (Dvořák, Evropská, Praha), (Smetana, Evropská, Praha)} je relace přes dom(jméno) × dom(ulice) × dom(město). Relační schéma je (jméno, ulice, město). Pro větší názornost můžeme zavedené pojmy porovnat s tzv. tabulkovou terminologií, kterou firmy zavádějí pro popis svých relačních produktů (používanou i v jazyce SQL). Současné hodnoty relace jsou specifikovány tabulkou. Schéma relace je záhlaví tabulky, ntice relace je řádek tabulky (ve fyzické reprezentaci nazývaný záznam). Počet řádků určuje kardinalitu relace. Atribut je reprezentován sloupcem (nazývaný též pole). Počet sloupců určuje řád relace. Tělo relace tvoří neuspořádaná množina žádné, jedné n-tice nebo více n-tic. Relaci se v praxi může říkat např. množina záznamů nebo výsledná množina. Nejdůležitější rozdíly mezi oběma popisy spočívají zejména v těchto skutečnostech: • •
v relaci nezáleží na pořadí řádků, tabulka je vždy nějak uspořádána – číslo řádku ale není důležité relace neobsahují duplicitní n-tice (vyplývá z množinového charakteru).
Charakteristickou vlastností databázového systému je skutečnost, že výsledkem jakékoliv operace nad relací je opět jiná relace – výsledky jedné operace můžeme tedy použít jako vstupní hodnoty jiné operace. 2.3.2.1.
Relační algebra
Protože relace jsou množiny, můžeme pro manipulaci s nimi využívat množinové operace. V následujících definicích označuje t element relace r (n-tici reprezentovanou řádkem v tabulce). Sjednocení r ∪ s = {t | t ∈ r or t ∈ s} Aby bylo sjednocení r ∪ s platné, r a s musejí mít stejnou aritu (stejný počet atributů) Domény atributů musejí být kompatibilní (odpovídající sloupce r a s obsahují hodnoty stejného typu) Relace r jméno Novák Novotná Dvořák
Relace s město Teplice Ústí Praha
jméno Novotná Dvořák
město Ústí Teplice
- 14 r∪s jméno Novák Novotná Dvořák Dvořák
město Teplice Ústí Praha Teplice
Průnik r ∩ s = {t | t ∈ r and t ∈ s} Aby byl průnik r ∩ s platný, r a s musejí mít stejnou aritu (stejný počet atributů). Domény atributů musejí být kompatibilní (odpovídající sloupce r a s obsahují hodnoty stejného typu) Relace r jméno Novák Novotná Dvořák
Relace s město Teplice Ústí Praha
jméno Novotná Dvořák
město Ústí Teplice
r∩s jméno Novotná
město Ústí
Rozdíl množin r – s = {t | t ⊂ r and t ⊄ s} Rozdíly množin musejí být brány mezi kompatibilními relacemi. Relace r a s musejí mít stejnou aritu, domény atributů relací r a s musejí být kompatibilní Relace r Jméno Novák Novotná Dvořák
Relace s město Teplice Ústí Praha
jméno Novotná Dvořák
město Ústí Teplice
r–s Jméno Novák Dvořák
město Teplice Praha
Kartézský součin r × s = {t q | t ∈ r and q ∈ s} Předpokládá se, že atributy relací r a s jsou disjunktní (tj. průnik relačních schémat je prázdná množina). Relace r
Pokud jména atributů obou relací jsou shodná (relační schémata nejsou disjunktní), uplatňujeme tzv. tečkovou notaci, např. r.jméno, r.město, s.zaměstnání, s.firma. Někdy je nutné provést kartézský součin relace samotné se sebou, tedy r × r. V tom případě musíme použít operaci přejmenování atributů. Přejmenování atributů Operaci přejmenování lze zadat seznamem substitucí, např. jméno → název Projekce Projekce relace r je definována jako relace se schématem, které je podmnožinou schématu původního. Relace ΠA1, A2, …, Ak (r), kde A1, A2 jsou jména atributů, je relace k sloupců, kterou dostaneme smazáním sloupců, které nejsou vyjmenovány. Duplicitní řádky jsou z výsledku odstraněny. Relace r jméno Novák Novotná Novotná Dvořák
ulice Alejní Poláčkova Brandtova Evropská
město Teplice Ústí Ústí Praha
Πjméno, město (r) jméno Novák Novotná Novotná Dvořák
město Teplice Ústí Ústí Praha
=
jméno Novák Novotná Dvořák
město Teplice Ústí Praha
Selekce (restrikce, výběr) σP(r) = {t | t ∈ r and P(t)}, kde P je logická podmínka. Logická podmínka P je zadána booleovským výrazem (pomocí logických spojek and, or, not) atomických formulí tvaru nebo , kde může nabývat hodnot =, ≠, >, ≥, <, ≤.
- 16 Relace r jméno Novák Novotná Novotná Dvořák
ulice Alejní Poláčkova Brandtova Evropská
město Teplice Ústí Ústí Praha
σměsto="Teplice" or město="Praha" (r) jméno Novák Dvořák
ulice Alejní Evropská
město Teplice Praha
Spojení relací Nechť r a s jsou relace na schématech R a S. Výsledek spojení r * s je relace na schématu R ∪ S, kterou získáme uvažováním každého páru n-tic tr z r a ts z s. Mají-li tr a ts stejné hodnoty na každém z atributů v R ∩ S, je do výsledku zahrnuta ntice t, kde t má stejnou hodnotu jako tr na r a stejnou hodnotu jako ts na s. Příklad r * s je definováno jako: Πr.jméno,r.ulice,r.město,s.zaměstnání (σr.jméno=s.jméno (r × s)) Relace r jméno Novák Novák Dvořák
Pokud nová relace vzniká spojením na maximální množině společných atributů, hovoříme o přirozeném spojení. 2.3.2.2.
Relační kalkul n-tic
Neprocedurální dotazovací jazyk, kde každý dotaz je ve tvaru {t | P(t)}. Je to množina všech n-tic t takových, že predikát P je pravdivý pro t t je proměnná n-tice; t[A] značí hodnotu n-tice t na atributu A t ∈ r značí, že n-tice t je v relaci r Predikátový výraz P je výraz skládající se z komponent patřících do následujících množin:
- 17 • • • • •
Množina atributů a konstant Množina operátorů porovnání: (např.: <, ≤, =, ≠, >, ≥) Množina spojek: and (∧), or (∨), not (¬) Implikace (⇒): x ⇒ y, jestliže x je pravdivé, pak y také, x ⇒ y ≡ ¬x ∨ y Množina kvantifikátorů: ∃ t ∈ r (Q(t)) ≡ existuje n-tice t v relaci r tak, že platí Q(t) ∀ t ∈ r (Q(t)) ≡ pro všechny n-tice t v relaci r platí Q(t)
2.3.3.
Návrh relací – normální formy
2.3.3.1.
Funkční závislosti
Nechť A je množina všech atributů relace r a X ⊆ A. Jestliže jména atributů jsou X1, …, Xn, potom X-hodnotou je libovolný prvek kartézského součinu dom(X1) × … × dom(Xn). Množina atributů C ⊆ A funkčně závisí na množině atributů B ⊆ A (nebo B funkčně určuje C), jestliže ke každé B-hodnotě existuje nejvýše jedna C-hodnota. Označujeme B → C. Funkční závislost je definována mezi dvěma množinami atributů v rámci jednoho schématu relace. jméno Novák Novotná Dvořák
ulice Alejní Poláčkova Evropská
město Teplice Ústí Praha
PSČ 415 01 400 11 160 00
Na množině atributů A = {jméno, ulice, město, PSČ} platí tyto funkční závislosti: {město, ulice} → PSČ (pokud jedna ulice neleží v několika městských částech) PSČ → město (platí vždy) Platí: Pokud je množina kandidátním klíčem, musí na ní být funkčně závislé veškeré ostatní atributy. • • • •
Armstrongova pravidla Jestliže Y ⊆ X, pak X → Y Jestliže X → Y a Y → Z, pak X → Z Jestliže X → Y a X → Z, pak X → YZ (kompozice množiny atributů) Jestliže X → YZ, pak X → Y a X → Z (dekompozice množiny atributů)
Množina všech funkčních závislostí odvoditelných z množiny atributů F se nazývá uzávěr F (označujeme F+). Závislost, která má na pravé straně jeden atribut, nazýváme elementární funkční závislost. Pokrytí množiny funkčních závislostí F je množina funkčních závislostí G, pro kterou F+ = G+. Pokud je G množina elementárních závislostí, hovoříme o pokrytí kanonickém. Závislost f je redundantní v F, pokud (F – {f})+ = F+. Odstraněním všech redundantních závislostí vznikne tzv. neredundantní pokrytí F (neobsahuje redundantní závislosti).
- 18 2.3.3.2.
Normální formy schémat relací
První normální forma Důležitým omezením relačního modelu dat je skutečnost, že relace jsou v tzv. první normální formě, to znamená, že hodnoty v řádcích tabulky jsou atomické, dále nedělitelné. Pokud by tedy hodnota atributu Děti v tabulce osob byla rovna „Jakub, Tereza“, nebylo by možné vyhledat pomocí této teorie rodiče všech Jakubů. Zpracování řetězců jde mimo rámec relačního modelu dat. Studovaná teorie má však dostatek prostředků pro řešení tohoto problému. Předpokládejme, že sledujeme např. údaje o zaměstnancích firmy a jejich dětech. Nejvhodnější pro popis budou následující relace: Relace Osoby příjmení Novák Novotná Dvořák
rodné číslo 720508/1508 785214/0509 651129/1239
ulice Alejní Poláčkova Evropská
město Teplice Ústí Praha
Relace Děti příjmení Novák Dvořák Dvořák
jméno Jana Jakub Tereza
datum 19.1.01 23.5.97 15.8.99
Každé dítě musí mít rodiče: Hodnota příjmení v závislé relaci Děti musí být obsažena jako hodnota primárního klíče hlavní relace Osoby. Toto integritní omezení nazýváme referenční integritou. Atribut, kterého se omezení týká, je cizím klíčem (je spojen s primárním klíčem druhé tabulky, tzv. tabulky primární). Druhá normální forma Relace je v druhé normální formě, pokud je v první normální formě a navíc všechny její atributy jsou závislé na celém kandidátním klíči. Uvažujme relaci Účty vyjádřenou následující tabulkou: banka Česká spořitelna Česká spořitelna Komerční banka Komerční banka Komerční banka
Předpokládáme, že klíčem schématu je {banka, číslo_účtu}. Pokud budeme předpokládat, že každá banka má právě jednu adresu, stává se nevýhodou schématu redundance; adresa banky se zbytečně opakuje v mnoha n-ticích relace. Další nevýhodou je možná ztráta informace – chceme-li např. evidovat novou, dosud nefungující banku, nelze akci realizovat. Intuitivním řešením těchto problémů by bylo navrhnout dvě schémata: Banky
- 19 banka Česká spořitelna Komerční banka
adresa Mírové nám. 2 Bílinská 2
Účty2 banka Česká spořitelna Česká spořitelna Komerční banka Komerční banka Komerční banka
číslo_účtu 1648 4217 0429 4720 3374
zůstatek 24 500 120 000 41 000 50 000 12 200
Důvodem, proč původní schéma nevyhovovalo, byla závislost neklíčového atributu (adresa) na podmnožině klíče (banka). Jednalo se pouze o částečnou závislost. Třetí normální forma Nechť X a Y jsou podmnožiny množiny atributů dané relace, C je jednotlivý atribut, který se nevyskytuje v X ani Y. Nechť dále platí X → Y → C a neplatí Y → X (Y není dalším klíčem). Pak říkáme, že C je tranzitivně závislý na X. Říkáme, že schéma relace r je ve 3. normální formě, jestliže každý neklíčový atribut schématu r není tranzitivně závislý na žádném klíči schématu. Pokud je relace ve třetí normální formě, musí být i ve druhé normální formě. Definice 3. normální formy zahrnuje i případ částečné závislosti neklíčových atributů na klíči. Pokud by totiž pro podmnožinu K’ klíče K platilo K’ → C, potom by i K → K’ → C, což se vylučuje s definicí 3. normální formy. Dalším příkladem nevhodně navrženého schématu je relace vyjádřená tabulkou Auta: model Octavia Fabia Golf Passat Toledo Scénic
výrobce Škoda Škoda Volkswagen Volkswagen Seat Renault
země_původu Česko Česko Německo Německo Španělsko Francie
Klíčem schématu je model (nepředpokládáme, že by dvě automobilky pojmenovaly své vozy stejně). Každý výrobce jednoznačně určuje zemi původu. Redundanci pomůže odstranit rozložení na dvě schémata Automobilky výrobce Škoda Volkswagen Seat Renault
země_původu Česko Německo Španělsko Francie
Ceny_aut model Octavia
výrobce Škoda
cena 354 900
- 20 Fabia Golf Passat Toledo Scénic
Škoda Ford Ford Seat Renault
279 900 440 000 764 000 567 870 514 900
V tomto případě byla nevhodnost původního schématu způsobena tím, že neklíčový atribut (země_původu) byl tranzitivně závislý na klíči (model): model → výrobce → země_původu. Vyšší normální formy Další normální formy, Boyce-Coddova, čtvrtá a pátá, slouží pouze pro určité případy a nesetkáme se s nimi příliš často. Každá vyšší normální forma zahrnuje všechny normální formy nižší. Boyce-Coddova normální forma je někdy považována za jistou variaci třetí normální formy, používá se pro případy relací s více kandidátními klíči, z nichž nejméně dva musí být složené a v některých atributech se překrývají. Vyjadřuje, že mezi kandidátními klíči nesmí být žádná funkční závislost. Čtvrtá normální forma řeší problém, kdy se v jedné relaci spojují nezávislé opakované skupiny, pátá normální forma si všímá vzácného případu cyklické závislosti. 2.3.3.3.
Kritéria pro návrh relačního schématu databáze
Základním předpokladem správně fungující databáze je odstranění tzv. anomálií při aktualizacích relací. Problém řeší převod do 3. normální formy. Jedná se o nahrazení jednoho schématu několika jinými schématy, přičemž původní množina atributů je rovna sjednocení množin atributů nových schémat. Tomuto procesu se říká dekompozice schématu relace. Dekompozice má smysl, pokud jsou splněna následující kritéria: • •
nová schémata pokrývají závislosti původní relace nové relace obsahují stejná data jako původní relace
Proveďme dekompozici schématu relace na několik dílčích schémat. Pokud množina všech funkčních závislostí (uzávěr) původní množiny atributů je rovna sjednocení uzávěrů schémat dílčích, říkáme, že dekompozice má vlastnost pokrytí závislostí. Pro bezztrátovost dekompozice platí následující věta: Mějme schéma R(A, B), kde A, B jsou disjunktní množiny atributů. Potom dekompozice R1(A, B) a R2(A) je bezztrátová právě tehdy, když platí funkční závislost A → B. Stanovit, zda relace je ve 3. normální formě, nemusí být teoreticky jednoduchá záležitost. V praxi většinou postupujeme tak, že dekompozici vždy začneme od závislosti A → B, kde A, B jsou jednotlivé atributy. Výběr těchto atributů spolu s aplikací této úvahy rekurzivně vede k rozumným výsledkům.
2.4.
Datová integrita
Zajištění datové integrity je velice obtížný problém, v mnoha případech se automaticky zajistit nedá. Zajištění věrohodnosti je dosahováno splňováním integritních omezení, podmínek, kterým data musejí vyhovovat.
- 21 Doménová integrita Pravidlo, které definuje přípustné hodnoty daného atributu. Ty jsou dány jednak datovým typem (např. řetězcová hodnota o délce nejvýše 40 znaků). Zahrnuje v sobě i rozhodnutí, zda doména může obsahovat neznámé nebo neexistující hodnoty. Omezení mohou být kladena na samotná data, např. doména atributu datum prodeje může být omezena pouze pracovními dny, u některých atributů můžeme definovat doménu výčtem možných hodnot. Přechodová integrita Omezení přechodové integrity definují stavy, mezi kterými může daná entita přecházet. Můžeme např. zajistit, že na vypsání zkoušky nejdříve musíme zveřejnit termín, těsně před termínem uzamknout pro přihlašování, po testu zadat výsledky písemné části, potom části ústní a termín uzavřít. Přechodová integrita může v tomto příkladu zahrnout např. i tu skutečnost, že při dobrých výsledcích semestrálních prací není vyžadována písemná část zkoušky. Entitová integrita Omezení na úrovni entity zahrnuje např. nutnost existence primárního klíče, dále omezuje doménovou integritu. Např. nedovoluje, aby datum vystavení faktury bylo starší než odpovídající objednávka. Zadávání dat z budoucnosti řešit nemusí, to je omezeno doménou. Referenční integrita Kontroluje vazbu mezi relacemi. Řádek v tabulce obsahující cizí (nevlastní) klíč nesmí obsahovat takovou hodnotu cizího klíče, která nemá odpovídající záznam v primární tabulce. Řádek nesplňující toto kritérium nazýváme sirotkem (sirotčí entitou). K narušení referenční identity je možné dospět třemi způsoby. Je to: •
Přidáním řádku do závislé tabulky s neodpovídajícím cizím klíčem
•
Změnou kandidátního klíče v primární tabulce
•
Odstraněním záznamu v primární tabulce
Všechny tři případy musí být ošetřeny, první případ je obvykle přímo zakázán, další případy řeší databázové stroje tzv. kaskádovitou aktualizací nebo kaskádovitým odstraněním. Databázová integrita Omezení vztahující se na několik relací, např. „Stav zákazníka není přednostní, pokud během posledního roku neutratil odpovídající sumu“. Transakční integrita Databáze zůstává v konzistentním stavu nezávisle na selhání systému a selhání transakcí. Pokud peníze odečteme z jednoho účtu, ale nepodaří se je přičíst na účet jiný, musí se zrušit i předchozí transakce (odečtení z účtu).
- 22 -
3.
Databázový systém Microsoft Access 3.1.
Úvod
Velké množství i zkušených uživatelů výpočetní techniky odrazuje od využívání databázových programů názor, že k tomu potřebujeme znalosti programování nebo že zvládnutí by zabralo spoustu času. Nejzákladnější, byť velmi omezené operace při hromadném zpracovávání dat se dají vykonat v tabulkovém procesoru. O nevýhodách, které to s sebou přináší, jsme již hovořili. Obecně se hodí pouze pro řešení jednoúčelových a jednouživatelských problémů. Chceme-li postihnout data v celé šíři, nevyhneme se použití relační databáze. Nemusíme začínat rovnou s velkými podnikovými řešeními, pokud se spolehlivě naučíme používat prakticky jakýkoliv databázový systém, zejména pokud porozumíme základům SQL; přeorientování se na jiný systém nebude složitou záležitostí. Asi nejrozšířenějším databázovým systémem, určeným zejména k osobnímu použití nebo práci ve skupinách, je program Microsoft Access. K demonstraci jednoduchého databázového systému si ho vybereme ne pro jeho výkon nebo možnosti, ale zejména pro vysoce intuitivní grafické prostředí, kompatibilitu s dalšími databázovými systémy, spolupráci s nejběžnějšími tabulkovými procesory a možnost snadné tvorby SQL dotazů. Access nepracuje s daty přímo, sám o sobě tedy nemá funkci databázového stroje. Namísto toho využívá služeb od verze 3.0 samostatného databázového stroje Microsoft Jet, přičemž se ale dokáže připojit k libovolným datům, uloženým v jakémkoliv zdroji dat ODBC. Microsoft Access je plně funkční systém pro správu relační databáze (RDBMS). Zajišťuje funkce pro definici dat, manipulaci s daty a řízení dat. Jako každý RDBMS umožňuje definovat druh dat a způsob, jakým by tato data měla být uložena. Access podporuje definování nejrůznějších typů dat (např. text, čísla, časové údaje, měny, hypertextové odkazy, obrázky, zvuky, dokumenty nebo tabulky). Umožňuje definovat pravidla pro zajištění integrity dat, a to jednak pomocí tzv. validačního pravidla (do numerického pole nemůžeme zapsat písmeno, případně další omezení hodnot), jednak pravidla pro zajištění referenční integrity (zajišťuje, aby např. nebyla vytvořena objednávka pro neexistujícího zákazníka). Do aplikací, vytvářených v prostředí MS Access pomocí maker nebo jazyka Visual Basic for Applications (VBA), je možné vkládat uživatelské ovládací prvky AktiveX. Access je možné používat i ve sdíleném režimu klient-server, dá se ho využít i jako jednoduchý databázový server pro webové stránky. Zaručuje, že dva lidé nemohou současně aktualizovat jeden objekt.
3.2.
Architektura
Vše, co může mít nějaké jméno, nazývá Access objektem. Databáze je složena z jednotlivých objektů, které slouží nejen k uchovávání dat, ale např. i objekty pro automatizaci činností nad těmito daty. Výchozím formátem databáze Access je soubor .mdb, kromě samotné databáze může obsahovat i další objekty. Hlavními typy objektů jsou : Tabulka. Objekt, který slouží k uchovávání dat. Každá tabulka shromažďuje informace týkající se určitého entitního typu. Tabulka obsahuje sloupce (pole), odpovídající jednotlivým atributům, a řádky (záznamy), uchovávající informace o konkrétní instanci daného entitního typu. Nad každou tabulkou můžeme definovat primární klíč (jedno nebo
- 23 více polí, které jednoznačně identifikují danou entitu) a indexy, které přispívají k rychlejšímu výběru dat. Dotaz. Objekt, který zajišťuje uživatelský pohled na data z jedné nebo více tabulek. V Accessu je možné vytvářet dotaz v grafickém prostředí nebo přímo pomocí jazyka SQL. Při vytváření dotazu v grafickém prostředí (s možností využití mnoha průvodců) se automaticky vytváří i jeho SQL podoba. Formulář. Objekt, který je vhodný zejména pro vstup nebo zobrazení dat. V Accessu slouží i pro řízení průběhu aplikace, může obsahovat uživatelem definované ovládací prvky, může spouštět makra nebo procedury VBA Sestava. Objekt sloužící zejména ke grafickému výstupu – umožňuje formátování, výpočty, zobrazení souhrnů a tisk. Datové stránky. Podpora přístupu k datům z prohlížeče webových stránek (podporuje MS Internet Explorer 5 a vyšší). Makro. Strukturovaná definice jedné nebo více akcí, které má Access vykonat jako odezvu na nějakou definovanou událost (např. stisknutí tlačítka, změna hodnot v polích). Makra umožňují otevírat a provádět dotazy, zobrazovat tabulky či tisknout. Mohou obsahovat jednoduché podmínky, spouštět další makra nebo procedury VBA. Modul. Objekt obsahující uživatelské procedury v jazyce VBA. Oproti makrům nabízejí více chráněný tok akcí a umožňují zachycovat chyby. Moduly mohou být samostatné objekty (procedury mohou být potom volány z libovolného místa aplikace), nebo mohou být přidruženy k formuláři nebo sestavě (a potom odpovídají pouze na události z nich). Kromě uchovávání dat v samotném souboru databáze Access existuje možnost použít Access pouze jako prostředek pro přístup k Microsoft SQL Serveru a data uchovávat v této výkonné databázi. Odpovídající soubor Accessu se nazývá projekt, má příponu .adp a obsahuje pouze databázové objekty založené na kódu nebo na jazyce HTML, tedy formuláře, sestavy, názvy a umístění datových stránek, makra a moduly. Tyto databázové objekty slouží k tvorbě aplikací. Na rozdíl od databáze neobsahuje projekt data ani definiční objekty, tedy tabulky, zobrazení, databázové diagramy, uložené procedury ani uživatelem definované funkce. Tyto databázové objekty jsou uloženy v databázi SQL Serveru.
3.3. 3.3.1.
Vytváření databáze Vytvoření nové databáze
Access umožňuje kromě vytvoření prázdné databáze možnost vytvoření databáze za použití určité předdefinované a uživatelem přizpůsobitelné šablony. V podstatě se podle zadané situace vygeneruje určité množství tabulek, dotazů, formulářů a sestav včetně jejich provázání pomocí různých vztahů, pro které se v komerčních programech často používá také pojem relace. Generování připraveného schématu může pomoci jak začátečníkům, tak zkušeným vývojářům, kterým ušetří spoustu času při nastavování základních tabulek a tvorbě dalších objektů. Volba Prázdná databáze vytvoří nový soubor .mdb ve specifikované složce a zobrazí okno Databáze (obr. 13). Dalším krokem je definice tabulek. Access rozlišuje dva možné pohledy na tabulky: Návrh a modifikace jejich struktury probíhá v režimu Návrhové zobrazení, data v tabulce jsou přístupná v tzv. Zobrazení datového listu (obdobné režimy fungují pro všechny ostatní typy objektů).
- 24 -
Obr. 13 Okno Databáze s novou databází
Novou tabulku můžeme vytvořit pomocí tlačítka Nový, a to následujícími způsoby: •
• • • •
pomocí Zobrazení datového listu – tabulka je vytvořena přímým vložením dat; v tomto režimu se zobrazí prázdná tabulka s deseti poli, po zavření datového listu se zachovají pouze pole obsahující data a jejich datový typ se určí z vložených hodnot v režimu Návrhové zobrazení – jedná se o nejčastější způsob vytváření tabulky, a to definováním jednotlivých polí pomocí Průvodce tabulkou – v tomto režimu nabízí Access vytvoření tabulky podle šablon odpovídajících nejběžnějším situacím; uživatel vybírá a případně upravuje pole z nabízených možností Importem tabulky z již existující databáze podporovaného formátu pomocí Průvodce propojením tabulky z již existující databáze podporovaného formátu
Zástupce na tři nejčastěji používané postupy vytvoření nové tabulky jsou navíc uvedeny přímo v odpovídající kartě okna Databáze. Standardním způsobem je vytváření v návrhovém zobrazení.
- 25 -
Obr. 14 Návrhové zobrazení tabulky
Do jednotlivých sloupců je nutné zadat názvy polí (sloupců vytvářené tabulky) a jejich datový typ. Datový typ může nabývat následujících hodnot: • • • • • • • • •
Text – alfanumerické údaje, max. 255 bajtů Memo – souvislý text ve větách a odstavcích, až 65 535 bajtů Číslo – číselné údaje, 1, 2, 4 nebo 8 bajtů (16 bajtů v případě replikačního identifikátoru) Datum a čas, 8 bajtů Měna – peněžní hodnoty s přesností na 4 desetinná místa, 8 bajtů Automatické číslo – jedinečná automaticky generovaná hodnota pro každý nový záznam, 4 bajty (16 bajtů v případě replikačního identifikátoru). Maximálně jedno pole tohoto typu v tabulce. Ano/ne – logická hodnota, 1 bit Objekt OLE – obrázky, grafy nebo jiné objekty OLE z dalších aplikací Hypertextový odkaz – odkaz s adresou dokumentu nebo www stránky (UNC nebo URL adresa) V kartě Obecné specifikujeme podrobnosti pro dané pole: •
Velikost pole (pro Číslo jsou možnosti pro velikost popsány níže)
•
Formát – určuje, jak mají být data zobrazena; záleží na datovém typu (viz samostatná kapitola)
- 26 •
Počet desetinných míst (pro Číslo a Měna, maximálně 15)
•
Vstupní maska (pro Text, Číslo, Měna, Datum/čas) – editační maska, kterou uživatel uvidí během vkládání dat (po ukončení editace zmizí)
•
Titulek – text, který se zobrazí v názvech formulářů a hlavičkách sestav
•
Výchozí hodnota – určení implicitní hodnoty (standardně je pro čísla 0, logické hodnoty Ne a pro Text a Memo NULL
•
Ověřovací pravidlo – výraz, jehož pravdivost se při vkládání nebo změně dat ověřuje (např. <100 nebo "pondělí" Or "středa")
•
Ověřovací text – hláška, která se objeví při nedodržení ověřovacího pravidla
•
Je nutno zadat – nepovolení hodnoty NULL
•
Povolit nulovou délku – u Text a Memo povolení prázdného řetězce
•
Indexovat (pro Text, Číslo, Datum/čas, Měna a Automatické číslo) – vytvoření indexu urychlujícího operaci se záznamy. Možno vyžadovat jedinečnost v rámci tabulky.
•
Komprese kódu Unicode (pro Text a Memo) – dvojbajtový znak zapíše pomocí jediného (standardně zapnuto v zemích bez složitějších národních znaků)
Pro Číslo jsou možné velikosti pole následující: Bajt (1 bajt, hodnoty 0 – 255) Celé číslo (2 bajty, hodnoty -32 768 až +32 767) Dlouhé celé číslo (4 bajty, hodnoty -2 147 483 648 až + 2 147 483 647) Jednoduchá přesnost (4 bajty, 7 desetinných míst, čísla od 1,401298E– 45 do 3,402823E38 pro kladné i záporné hodnoty) o Dvojitá přesnost (8 bajtů, 15 desetinných míst, čísla od 4,94065645841247E–324 do 1,79769313486231E308 pro kladné i záporné hodnoty) o Desetinné číslo (Čísla od –10^28–1 do 10^28–1 s až 28 desetinnými čísly)
o o o o
V kartě Vyhledávání specifikujeme možnosti zobrazení daného pole pomocí seznamu nebo pole se seznamem. Zdrojem řádků seznamu mohou být hodnoty v jiné tabulce, názvy polí nebo uživatelem definované hodnoty. Více se o těchto vlastnostech dozvíme v samostatné kapitole. 3.3.1.1.
Definování jednoduchých ověřovacích pravidel
Ověřovací výraz se obvykle skládá z operátoru a porovnávané hodnoty. Při nezadání operátoru se porovnává na rovnost. Můžeme specifikovat více porovnání oddělených logickými operátory Or a And. Hodnoty textových řetězců se uzavírají do uvozovek ("), datum do mříží (#). Kromě klasických operátorů (<, <=, >, >=, =, <>) můžeme používat operátory IN, BETWEEN a LIKE. IN
IN ("pondělí"; "středa")
BETWEEN
BETWEEN 50 AND 70
test na rovnost pro kterýkoliv prvek v seznamu; porovnávaná hodnota musí být seznam uzavřený do závorek test na rozsah hodnot; obě porovnávané
- 27 (odpovídá >=50 AND <= 70) LIKE "B*"
LIKE
hodnoty odděleny operátorem AND test textového nebo memo pole na shodu se vzorovým řetězcem
V porovnávacím řetězci pro operátor LIKE je možné používat následující zástupné znaky: ? * # [] !
jakýkoliv jeden znak žádný nebo více znaků jakékoliv jedno číslo seznam platných znaků negace Příklady:
LIKE "### ##" LIKE "*ová" LIKE "?m*" LIKE "[A-CF]*" LIKE "*A0##*" LIKE "[!0-9G]*#" 3.3.1.2.
PSČ řetězec končící -ová řetězec s druhým písmenem m řetězec začínající A, B, C nebo F řetězec kdekoliv obsahující A0 a následované dvěma čísly řetězec nezačínající číslicí ani G a končící číslicí Definování formátu
U všech datových typů je možno přizpůsobit formát našim potřebám; pomocí formátovacích znaků můžeme vytvořit formát vlastní, navíc pro datové typy Číslo, Měna a Automatické číslo jsou nejpoužívanější formátovací možnosti předdefinovány. Předdefinovanými formáty jsou: • • • • • •
Obecné číslo Měna nebo Euro (symboly měny, dvě desetinná místa) Pevný formát (minimálně jedna číslice a dvě desetinná místa) Standardní formát (dvě desetinná místa, oddělení tisíců) Procenta Vědecký formát (např. 1,54E3)
Formátovací znaky (obecné) "text" \ ! * [barva]
zobrazení jakéhokoliv textu zobrazení následujícího znaku zarovnání doleva následující znak bude použit pro vyplnění volného místa změna barvy (musí být použito s dalšími znaky)
Formátovací znaky pro Číslo a Měna oddělovač desetin oddělovač tisíců 0 # % E+ nebo E-
použít pro zobrazení desetinné čárky použít pro oddělení tisíců číslice; pokud není, zobrazí se 0 číslice; pokud není, zobrazí se mezera hodnota se vynásobí 100 a připojí znak % zapnutí exponenciální notace pro kladné nebo záporné exponenty
Formátovací znaky pro Text
- 28 @ & < > - + $ ( ) mezera
jakýkoliv znak nebo mezera jakýkoliv znak nebo nic všechna písmena se zobrazí jako malá všechna písmena se zobrazí jako malá možno použít kdekoliv v řetězci
Formátovací znaky pro Datum/Čas Pro jednotlivé údaje data (rok, měsíc, den, hodina, minuta, sekunda) používáme zástupné symboly y, m, d, h, n, s. Jediný výskyt zástupného symbolu zobrazí danou hodnotu jako jednu nebo dvě číslice podle potřeby (s výjimkou y, to udává den v roce), dvojí opakování zobrazí vždy dvě číslice (případně s nulou). Trojí opakování d nebo m vypíše měsíc římskými čísly a den zkratkou (So), čtyřnásobné opakování těchto symbolů vypíše celá jména (u roku celý letopočet). Existuje ještě několik symbolů pro zobrazení dne v týdnu, čtvrtletí, nastavení 12hodinového cyklu a další. Pokud chceme, aby se formát lišil podle zadané hodnoty, oddělíme formáty středníky. Pro Číslo a Měna uvádíme v pořadí: kladná čísla; záporná čísla; nula; NULL, pro Text uvádíme v pořadí: text; NULL. Příklad pro pole Číslo: Formátovací řetězec: # ##0,00;(# ##0,00)[červená];"nic";"nevím" Vloženo 4500 -4500 ,5 -,002
Zobrazí se abcde abcd e abc*********************ed Město: Teplice. Město: Teplice. TeplicMěsto: e. ABCDE prázdný
Definování vstupní masky
Znaky pro definici vstupní masky: Symbol 0 9 # L ? A a
Význam číslo číslo, mezera číslo, mezera, + nebo písmeno písmeno písmeno nebo číslo písmeno nebo číslo
Povinnost zadat ano ano ano
- 29 & C "text" \ ! < > , mezera : ; - /
znak nebo mezera znak nebo mezera zobrazení jakéhokoliv textu zobrazení následujícího znaku vyplňování zprava doleva převede následující znaky na malá písmena převede následující znaky na velká písmena oddělovače desetin, tisíců, data (závisí na regionálním nastavení)
ano
Vlastnost Vstupní maska může obsahovat až tři oddíly, které jsou odděleny středníky. První určuje vlastní vstupní masku, druhý určuje, zda zástupné znaky budou uloženy v tabulce (0 ano, 1 nebo prázdné ne), třetí určuje podobu znaku, který se objeví v editovaném místě. 3.3.1.4.
Definování primárního klíče a indexů
V návrhovém zobrazení označíme jedno nebo více polí (pro více polí označíme první klepnutím myši na záhlaví řádku a při stisknuté klávese Ctrl klepneme na záhlaví dalších polí) a stiskneme tlačítko Primární klíč
Obr. 15 Definování složeného primárního klíče
Indexy nad jedním polem vytváříme tak, že v kartě Obecné příslušného pole zvolíme Indexovat: ano a vybereme možnost povolení nebo zamezení duplicity (primární klíč má automaticky duplicitu zakázanou). Definování indexů nad více poli musíme pomocí dialogu Indexy (dostupného pomocí tlačítka Indexy nebo odpovídající položky menu).
- 30 -
Obr. 16 Tabulka indexy
Na příkladu je vytvořen index Celé jméno skládající se z polí Příjmení a Jméno. 3.3.1.5.
Definování ověřovacího pravidla tabulky
Ověřovací pravidla lze definovat jednak pro jednotlivá pole, jednak pro celou tabulku. Toto pravidlo se potom ověřuje při ukládání řádku. Definuje se pomocí příkazu Vlastnosti menu Zobrazit.
Obr. 17 Definování ověřovacího pravidla pole
Pokud ve výrazu chceme použít názvy polí, píšeme je do hranatých závorek. Ověřovací pravidlo realizujeme velmi často pomocí funkce IIF, např. pravidlo IIF([město]= "Praha"; Len([jméno] & [příjmení])>8; TRUE) ověřuje délku celého jména pouze u obyvatel Prahy. 3.3.1.6.
Definování vztahů
Po vytvoření dvou nebo více tabulek musíme nadefinovat vztahy mezi nimi. Umožní nám to později spojovat tabulky v dotazech, formulářích a dalších objektech. Do dialogového okna Relace se dostaneme z menu nebo pomocí tlačítka, které je přístupné, pokud je aktivní okno Databáze. Prvním krokem je zobrazení tabulek, které ve vztazích budou vystupovat.
- 31 Potřebný vztah vytvoříme tak, že myší přetáhneme vhodné pole jedné tabulky a pustíme nad odpovídajícím polem druhé tabulky
Obr. 18 Vytvoření vztahu mezi tabulkami
V okně Upravit relace, které se objeví, můžeme doplnit případná další pole vyskytující se ve vztahu, nebo zaškrtnout políčko Zajistit referenční identitu. Access potom bude hlídat, aby neexistovala osoba s neexistující divizí, případně nedovolí smazat divizi z tabulky Divize, kterou má nějaká osoba v tabulce Osoby uvedenou. Nabízí nám potom ještě dvě další zaškrtávací políčka: •
Aktualizace souvisejících polí v kaskádě (při změně pole ID v tabulce Divize se změna promítne i do pole Divize_ID všech zaměstnanců odpovídající divize)
•
Odstranění souvisejících polí v kaskádě (po odstranění některé z divizí odstraní všechny její zaměstnance v tabulce Osoby)
Obr. 19 Dialogové okno Upravit relace
Po zavření okna je vztah graficky znázorněn, při zaškrtnutí políčka Zajistit referenční identitu se navíc připíší znaky symbolizující typ relace (1:N). Pokud chceme relaci odstranit, označíme ji myší a stiskneme klávesu Del. Pokud smažeme ze zobrazení tabulku, všechny relace zůstávají v platnosti. Při zavírání okna Relace se Access ptá na uložení, jedná se pouze o uložení grafického rozložení.
- 32 -
Obr. 20 Relace 1:N
3.3.2.
Úpravy vzhledu databáze
Tabulky lze mazat, kopírovat nebo přejmenovávat z okna Databáze, platí konvence jako např. v průzkumníku. V návrhovém zobrazení je ovládání podobné jako v běžných tabulkových kalkulátorech. Je možné beze ztráty dat přejmenovávat pole, mazat je, nebo vkládat nová.. Nové pole vložíme mezi dvě existující tak, že vybereme z nabídky Vložit příkaz Řádek. Pořadí polí měníme tak, že myší klepneme na záhlaví řádku (nebo označíme více řádků) a poté dalším klepnutím a tažením umístíme na nové místo Nelze použít kombinaci kláves CtrlX, Ctrl-V – vyjmutím bychom přišli o existující data. Naopak ke kopírování se nám obvyklé klávesy mohou hodit, při kopírování se samozřejmě zachovávají všechny údaje o formátování. Změna typu dat je možná pouhým výběrem nového datového typu (s výjimkou Objektu OLE a Replikačního identifikátoru). Největší riziko ztráty dat nastává v případě převodu textu na netextové pole, např. pro bezztrátový převod na Číslo musí pole obsahovat pouze číselné hodnoty. Při převádění Memo na Text nebo zkrácení délky textového pole je řetězec oříznut. Při zmenšení délky Čísla jsou hodnoty přesahující nový datový typ odstraněny. Při všech možných ztrátách dat Access vždy zobrazí varovnou hlášku.
Obr. 21 Dialogová okna při hrozících ztrátách dat během změn datového typu a při zkracování délky textového řetězce
- 33 Všechny změny provedené v návrhovém zobrazení se akceptují až po uložení tabulky. Pokud zavřeme návrhové zobrazení bez uložení, vrátíme se k původnímu vzhledu tabulky.
3.3.3.
Průvodce analýzou tabulky
Průvodce analýzou tabulky umí projít data v již existujících tabulkách, identifikovat redundantní data a doporučit takové změny návrhu, které rozdělí nadbytečná data do samostatných tabulek (pokusí se převést relaci do co možná nejvyšší normální formy). Je možné, aby Access navrhl možné změny sám, nebo nechat rozhodnout uživatele. Příklad ukazuje data v původní tabulce a změny navržené průvodcem. Čím více hodnot tabulka obsahuje, tím přesnějších výsledků průvodce dosáhne.
Obr. 22 Tabulka před analýzou
Po návrhu máme možnost nové tabulky pojmenovat a v dalším dialogovém okně nastavit primární klíče. V příkladu můžeme aktivovat pole Firma a nastavit jej jako primární index, vygenerovaný klíč zmizí. Tlačítko se symbolem „+“ naopak vygeneruje nový klíč.
- 34 Obr. 23 Generované schéma a možnost nastavení klíčů
Poslední dialogové okno nabízí možnost vytvoření dotazu, který má stejné jméno jako původní tabulka a plně původní tabulku zastoupí v již existujících objektech, které se na ni odkazují. Tento dotaz lze použít k aktualizaci dat z obou tabulek najednou. Dotaz umožňuje stejně jako nové tabulky měnit hodnoty svázaných polí výběrem ze seznamu namísto ručního zadání hodnoty. Vyhledávací seznam vyhledá hodnoty z podkladové tabulky. Tak je i při opakování stejné hodnoty ve více záznamech vždy zajištěna její konzistence.
Obr. 24 Dotaz nahrazující původní tabulku
3.3.4.
Vyhledávací vlastnosti
Po rozdělení tabulky na několik dílčích tabulek byla přebytečná pole v tabulce původní nahrazena kvůli úspoře místa pouze určitým kódem. Abychom i v původní tabulce místo číselného kódu viděli původní data, můžeme použít vlastností vyhledávání. Definují se v návrhovém zobrazení v kartě pole Vyhledávání. Protože definování je pracná záležitost (navíc využívající SQL dotazu), použijeme průvodce vyhledáváním. Pro vysvětlení použijeme již použitého příkladu s osobami a divizemi, v okně Relace však odstraníme vztahy mezi oběma tabulkami. Vytvoření vztahu obstará průvodce sám. V návrhovém zobrazení tabulky Osoby v poli Divize_ID klepneme myší na šipku pro změnu datového typu a vybereme Průvodce vyhledáváním. Vybereme, že chceme hodnoty načíst z tabulky nebo dotazu, vybereme tabulku Divize a poté specifikujeme aspoň jedno pole, které bude součástí seznamu. V další fázi nastavíme šířku sloupců, případně skryjeme klíčový sloupec (obr. 25).
- 35 -
Obr. 25 Úprava vzhledu zobrazených sloupců
Výsledné vlastnosti jsou zobrazeny na obr. 26. Zdrojem řádků je SQL dotaz, který pro seznam vybírá hodnoty ID a Popis z tabulky Divize. Klíčový sloupec byl v průvodci skryt, tudíž jeho šířka je 0.
Obr. 26 Vlastnosti vyhledávání
V kartě Vyhledávání specifikujeme další vlastnosti: •
Zobrazit ovládací prvek – specifikace, jak bude dané pole zobrazováno (standardně je Textové pole; Seznam umožňuje pouze výběr, Pole se seznamem kombinuje obojí)
•
Typ zdroje řádků (pro Seznam nebo Pole se seznamem) – určení, zda seznam hodnot se má získat z tabulky, dotazu, zadaného seznamu hodnot nebo seznamu polí z jiné tabulky
- 36 •
Zdroj řádků – pokud je Typ zdroje řádků nastaven na Tabulka/dotaz, příp. Seznam polí, zadá se SQL dotaz, příp. tabulka nebo dotaz; pokud je nastaveno Seznam hodnot, zapisujeme konkrétní hodnoty oddělené středníky
•
Vázaný sloupec – ve vícesloupcovém seznamu určuje hodnotu, která toto pole nastavuje
•
Počet sloupců – počet zobrazených sloupců čtených ze zdroje řádků; pokud Zdroj řádků obsahuje seznam hodnot, jsou hodnoty vkládány do řádků a sloupců v pořadí, ve kterém jsou uvedeny ve vlastnosti Zdroj řádků
•
Hlavičky sloupců – možnost použít první řádek dat jako hlavičky bez možnosti jeho výběru
•
Šířky sloupců – šířka oddělená středníky, číslo 0 skryje sloupec
•
Počet řádků seznamu – počet aktuálně zobrazených řádků pole se seznamem
•
Šířka rozbalovacího seznamu
•
Omezit na seznam – možnost zakázat hodnoty v seznamu se nevyskytující
3.3.5.
Komprimace databáze
Odstraníte-li data nebo objekty z databáze, může dojít k fragmentaci souboru a neefektivnímu využití diskového prostoru. Při komprimování souboru je vytvořena jeho kopie a změněn způsob jeho uložení na disku. Komprimováním je možné optimalizovat výkon databází. Komprimování nemá vliv na automatické číslování v projektu aplikace Access. Pokud jsou ale v databázi aplikace Access odstraněny záznamy z konce tabulky, která obsahuje pole typu Automatické číslo, je při komprimování upravena hodnota automatického čísla. Hodnota automatického čísla pro další přidaný záznam pak bude o jedničku vyšší než hodnota automatického čísla posledního neodstraněného záznamu v tabulce. Součástí komprimace je oprava možných chyb, které nebylo možné identifikovat ihned po jejich vzniku.
3.4. 3.4.1.
Práce s daty Datové listy
Data v tabulce nebo dotazu jsou přístupná v tzv. zobrazení datového listu, a to jak pro prohlížení, tak i aktualizaci, vkládání nebo mazání. Mezi zobrazením datového listu a návrhovým zobrazením se přepínáme například pomocí tlačítka na panelu nástrojů. Pohyb po datovém listu je velice intuitivní, práce se sloupci a řádky připomíná práci v tabulkovém procesoru. Snadno se přesvědčíme o možnosti formátování sloupců, změny šířky, skrytí, opětného zobrazení či ukotvení. Připomeňme, že výběr celého záznamu provedeme klepnutím myší na hlavičku řádku, výběr lze rozšířit tažením. Kopírování nebo vyjmutí řádků probíhá klasickým způsobem, vložení těchto řádků probíhá pomocí příkazu Přidat. Do tabulky původní je možné přidat kopírované řádky pouze pro tabulky bez primárního klíče nebo s primárním klíčem typu automatické číslo. Je to způsobeno požadavkem pro jedinečnost primárního klíče, u automatického čísla Access generuje hodnoty nové. Vyjmutí, přidání i odstranění řádků vyžaduje potvrzení.
- 37 Výhodou jsou vnořené datové listy; jsou to datové listy, které jsou vnořeny do jiného datového listu a obsahují data související nebo spojená s prvním datovým listem. Na obr. 27 je příklad se zaměstnanci a jejich dětmi.
Obr. 27 Zobrazení vnořeného datového listu
Datový list umožňuje řazení i filtrování dat. Nejjednodušší filtrování je podle výběru, zobrazí se záznamy, které mají stejnou hodnotou vybraného atributu jako aktuální záznam (dá se aplikovat i pro několik sousedních atributů vybraných pomocí klávesy Shift). Pokud je vybrána jen část pole, filtr se omezí jen na tento výběr, přičemž je respektováno umístění výběru na začátku nebo na konci v daném poli. Filtry je možno aplikovat několikrát jeden na druhý. Dalším typem filtru je Filtr podle formuláře. V samostatném okně zapíšeme podmínku pro jednotlivá pole tabulky. Podmínka je tvaru porovnávací operátor a porovnávaná hodnota, je možné používat operátory LIKE, BETWEEN a IN. Na jedné kartě okna mezi jednotlivými podmínkami platí vztah AND, mezi jednotlivými kartami potom OR.
Obr. 28 Filtr podle formuláře
Ještě obecnější je Rozšířený filtr či řazení. Všimněme si, že se jedná o jednotlivé karty z předchozího typu filtru uspořádané do řádků jedné tabulky. Jednotlivé podmínky v řádku Kritéria jsou aplikovány zároveň (platí vztah AND), mezi řádky platí vztah OR. Navíc přibyl řádek Řadit (pořadí řazení je zleva doprava). Z hlediska architektury není vytváření filtru nebo řazení nic jiného než vytváření dotazu nad původní tabulkou. Rozšířený filtr či řazení se dokonce dá uložit jako dotaz.
- 38 -
Obr. 29 Rozšířený filtr či řazení
3.4.2.
Dotazy
Pro pokročilejší práci s daty využíváme dotazů. Nečastěji používaným je výběrový dotaz, který vybírá data z tabulek a dotazů, naopak výkonný (nebo akční) dotaz vkládá, aktualizuje nebo odstraňuje data. Výsledkem dotazu je zase relace, v terminologii komerčních aplikací hovoříme o množině záznamů. Ve většině případů se s touto množinou dá pracovat jako s tabulkou, včetně aktualizace dat. Na rozdíl od skutečné tabulky množina záznamů není nositelem dat; vytváří se až v okamžiku spuštění dotazu. Dotazy podobně jako tabulky vytváříme v návrhovém zobrazení a spouštíme v zobrazení datového listu. Obrovskou výhodou je, že dotazy je možné také editovat pomocí jazyka SQL. Každý dotaz navržený v grafickém prostředí se totiž ihned překládá do jazyka SQL a naopak – dotaz napsaný v editoru SQL se dá zobrazit v grafickém prostředí. Access tedy můžeme použít jako vhodný nástroj pro výuku jazyka SQL. Přepnutí do editoru SQL provedeme pomocí menu Zobrazit nebo pomocí šipky u ikony na panelu nástrojů. Pokročilejšímu návrhu dotazů pomocí jazyka SQL se budeme zabývat v samostatné kapitole. 3.4.2.1.
Výběrový dotaz
Nový dotaz vytvoříme například z karty Dotaz okna Databáze. Máme možnost tvořit z návrhového zobrazení nebo pomocí průvodce. V obou případech je prvním krokem volba tabulky (případně dotazu nebo několika tabulek), na jejímž základě bude nový dotaz vytvořen. V případě návrhového zobrazení se jedná o zobrazení požadovaných objektů v horní části okna. Chceme-li se vyhnout specifikaci tabulky tímto způsobem, můžeme vytvořit nový dotaz po výběru požadované tabulky v kartě Tabulky okna Databáze stisknutím tlačítka Nový objekt na hlavním panelu nástrojů.
- 39 Okno návrhového zobrazení dotazu se skládá z horní části, kde se zobrazují jednotlivá pole tabulek a dotazů, na jejichž základě bude dotaz vytvořen, a části dolní, kde se specifikují jednotlivá pole nového dotazu. Každý sloupec představuje jedno pole. Toto pole může být pole jedné z tabulek, pole vypočítané z několika jiných polí nebo výsledek nějaké funkce. Nejrychlejší způsob nadefinování polí je tažením konkrétních polí z podokna tabulky pomocí myši. Tažení lze provádět pro více polí současně. Hvězdička zastupuje všechna pole.
Obr. 30 Návrhové zobrazení dotazu
Řádky Pole a Tabulky charakterizují dané pole, Řadit umožňuje vzestupné nebo sestupné řazení (interpretuje se zleva doprava). Řádek Zobrazit umožňuje skrýt sloupec, který je zapotřebí např. k filtraci nebo řazení. Ostatní řádky (Kritéria, nebo a bez popisu) slouží k zadání kritérií pro filtrování. Platí stejná pravidla jako při používání klasických filtrů. Mezi podmínkami na jednom řádku platí vztah AND, mezi řádky OR. Operátory AND a OR lze používat i v rámci jedné buňky, stejně jako LIKE, BETWEEN a IN. Při porovnávání vkládáme text do uvozovek, datum a čas do mříží. Vlastnosti polí (formát, popis, počet desetinných míst, vstupní maska a další) jsou děděna z vlastností definovaných v tabulce. Předefinování je možné např. z menu Zobrazit – Vlastnosti. Další pole dotazu mohou obsahovat vypočtené hodnoty. Mezi číselnými poli můžeme používat běžné operátory +, – , *, /, ^, operátor celočíselného dělení \, zbytek MOD. Operátor & spojí dva textové řetězce, při aplikaci na číselné hodnoty je převede na text. Při konstrukci výrazů můžeme využít funkce Lupa pro snadnější zápis (Shift-F2) nebo Tvůrce výrazů.
- 40 -
Obr. 31 Úprava výrazů pomocí funkce Lupa
Při vytváření výrazů píšeme názvy polí do hranatých závorek, před dvojtečku můžeme zapsat název sloupce. Hodnoty, které se v tomto sloupci v zobrazení datového listu objeví, není možné přepisovat. Tvůrce výrazů vylepšuje Lupu tím, že názvy jednotlivých polí, funkcí nebo dalších objektů nemusíme vpisovat ručně, ale pouze vybíráme z nabízených hodnot. Při aplikaci předdefinované funkce program nabídne i správný počet a pořadí argumentů. Všimněme si, že názvy polí jsou v hranatých závorkách a od názvu tabulky jsou oddělena vykřičníkem. Tak jsou jednoznačně určena.
Obr. 32 Tvůrce výrazů
Souhrnné dotazy Někdy nás v tabulce nezajímají všechny jednotlivé řádky, ale např. nějaká veličina pro všechny řádky splňující danou podmínku (např. průměrný plat v konkrétních divizích). Tento
- 41 problém řeší souhrnné dotazy. Přidávání souhrnů do dotazu umožní například stisknutí tlačítka Souhrny (∑) na panelu nástrojů. Tím se v návrhové mřížce dotazu objeví řádek Souhrn. Ve výchozím nastavení mají všechna zobrazovaná pole hodnotu Seskupit. Po spuštění by byly vypsány pouze všechny jedinečné řádky. Souhrny vytvoříme tak, že hodnotu Seskupit nahradíme některou ze souhrnných funkcí. Tato funkce vrátí hodnotu určenou z hodnot v daném poli pro každou skupinu. Dostupné souhrnné funkce jsou následující: Sum Avg Min Max Count Count(*) StDev Var First Last
provede součet hodnot v tomto poli vypočítá aritmetický průměr vrátí nejnižší hodnotu vrátí nejvyšší hodnotu vrátí počet řádků, které ve specifikovaném poli neobsahují hodnotu NULL vrátí počet řádků nezávisle na výskytu NULL vypočítá standardní odchylku vypočítá variaci vrátí první hodnotu v poli vrátí poslední hodnotu v poli
Funkce Count, First a Last, Min, Max můžeme použít i na jiné než číselné pole. Kromě těchto předdefinovaných funkcí umožňuje Access využít položky Výraz a zkonstruovat složitější funkci. Pomocí parametru Kde a zapsáním kritéria můžeme filtrovat.
Obr. 33 Návrh souhrnného dotazu
Při výběru zobrazovaných polí musíme být uvážliví, zobrazení prvních jmen v příkladu na obrázku nemusí být nejvhodnější. Pro identifikaci je vhodná položka Divize, další pole počítá průměrný plat v divizi, další počítá záznamy s neprázdným polem Divize v dané divizi, a posledním polem bude funkce počítající rozmezí platů v dané divizi.
- 42 Dotazy s parametrem Pokud chceme parametry dotazu specifikovat až v okamžiku jeho spuštění, zadáme kdekoliv v návrhovém zobrazení místo konkrétní hodnoty název parametru. Název zapisujeme do hranatých závorek a volíme ho takový, aby uživatel po spuštění dotazu věděl, co má zapsat do dialogového okna vybízejícího k zadání hodnoty parametru. Dotaz na obrázku vypíše pracovníky z divize specifikované až po spuštění dotazu.
Obr. 34 Dotaz s parametrem
Křížové dotazy Křížový dotaz je zvláštní typ souhrnného dotazu, na který nahlížíme v tabulkovém formátu. Hodnoty jednoho z polí vystupují jako hlavičky sloupců. Při vytváření křížového dotazu můžeme použít průvodce a teprve potom dotaz modifikovat. Při vytváření v návrhovém zobrazení musíme specifikovat typ dotazu z menu Dotaz – Křížový dotaz a v novém řádku specifikovat, které pole bude hlavičkou sloupce, řádku a z kterého pole se bude pomocí souhrnné funkce počítat zobrazovaná hodnota. Obrázek ukazuje křížový dotaz v návrhovém zobrazení i v zobrazení datového listu. Ukazuje, kolik obyvatel konkrétního města je v které divizi (divize jsou označeny čísly 1, 2 a 3).
- 43 -
Obr. 35 Křížový dotaz – návrhové zobrazení
Obr. 36 Křížový dotaz – zobrazení datového listu
Dotaz nad více tabulkami Po nadefinování správných vztahů je možné vybírat pole z libovolné tabulky. Pokud vztahy nejsou definovány, Access provede nejlepší odhad a spojí pole primárního klíče jedné tabulky s poli v druhé tabulce, která mají stejný název a odpovídající datový typ.
- 44 -
Obr. 37 Dotaz nad více tabulkami
Ve výchozím nastavení zobrazuje Access data ze dvou tabulek na základě shody svázaných atributů. Vypíší se pouze ty osoby, které jsou zařazeny v divizi a na druhé straně se vypíší pouze ty divize, v kterých někdo pracuje. Toto spojení tabulek nazýváme vnitřní spojení. Pokud chceme vypsat i nezařazené osoby nebo neobsazené divize, poklepáním myší na znázornění vztahu mezi tabulkami nastavíme tzv. vnější spojení, a to levé nebo pravé (obr. 38).
Obr. 38 Vlastnosti spojení
Pokud chceme v dotazu vypisovat pouze jedinečné řádky (v SQL jazyce SELECT DISTINCT), specifikujeme ve Vlastnostech dotazu Jedinečné hodnoty – ano. 3.4.2.2.
Výkonné dotazy
Aktualizační dotazy Aktualizační dotazy slouží k hromadné změně některého pole pro záznamy, které splňují určité kritérium. V praxi postupujeme nejčastěji tak, že vytvoříme dotaz výběrový,
- 45 zkontrolujeme, zda správně funguje, a teprve poté převedeme výběrový dotaz na aktualizační. To lze v návrhovém zobrazení pomocí menu Dotaz – Aktualizační dotaz nebo pomocí ikony s rozbalovacím seznamem na panelu nástrojů. Pomocí nově zobrazeného řádku Aktualizovat do určíme, na jakou hodnotu se mají daná pole aktualizovat.
Obr. 39 Aktualizační dotaz
Dotaz spustíme pomocí menu Dotaz – Spustit nebo pomocí ikony na panelu nástrojů. Přestože se Access po spuštění dotazu vždy zeptá, zda skutečně chceme aktualizovat daný počet řádků, je vhodné před spuštěním vytvořit záložní kopii tabulky. Poznámka: Access neuloží v dotazu pole, která nezahrnují kritéria nebo nemají být aktualizována. Při tvorbě však někdy bývá vhodné je do dotazu zahrnout. Vytvářecí dotazy Pokud chceme výsledek jakéhokoliv výběrového dotazu uložit jako novou tabulku, použijeme vytvářecího dotazu. Klasickým způsobem vytvoříme vhodný výběrový dotaz a z menu zvolíme Dotaz – Vytvářecí dotaz, případně použijeme ikonu na panelu nástrojů. Na vyžádání zadáme jméno nové tabulky. Dotaz spustíme pomocí menu nebo ikony na panelu nástrojů. Před přidáním řádků do nové tabulky Access vždy vyžaduje potvrzení. Do nové tabulky jsou přeneseny názvy polí, typ dat a konkrétní hodnoty. Upřesňující vlastnosti, jako např. Popisek, jsou ignorovány. Přidávací dotazy Přidávací dotaz slouží podobně jako dotaz vytvářecí ke zkopírování určitých dat do jiné tabulky. Na rozdíl od dotazu vytvářecího je však cílová tabulka již definována a do ní přidáváme data z tabulky zdrojové. Datové typy obou tabulek musejí být kompatibilní. Postup při vytváření dotazu je stejný jako u dotazu vytvářecího, opět je nutno zadat jméno cílové tabulky. Pomocí nového řádku v návrhovém zobrazení můžeme specifikovat, do kterých polí
- 46 cílové tabulky budou hodnoty daných polí vloženy (vhodné i pro tabulky s poli jinak pojmenovanými)
Obr. 40 Přidávací dotaz
Odstraňovací dotazy K odstranění několika řádků splňujících určitá kritéria používáme odstraňovací dotaz. Při vytváření opět vytvoříme nový výběrový dotaz, pomocí menu nebo ikony jej převedeme na dotaz odstraňovací, spuštěním se provede trvalé odstranění řádků splňujících podmínku. Před provedením je vhodné vytvořit záložní kopii tabulky.
3.4.3.
Import, propojení a export dat
Důležitou vlastností Accessu je, že umožňuje pracovat s mnoha druhy dat v jiných databázích, tabulkových nebo textových souborech. Data se dají do Accessu buď importovat, tedy v podstatě zkopírovat z původního umístění do accessovské databáze, nebo připojit a pracovat s nimi v původním umístění. Import volíme v následujících případech: • • • •
importovaný soubor je relativně malý a jiní uživatelé jej málo mění vytvořená data není zapotřebí sdílet s uživateli jiných databází stará data nebudou zapotřebí nepotřebujeme extrémní výkonnost.
Naopak pro připojení se rozhodneme v těchto případech: • • • •
soubor je větší než 2 GB (maximální kapacita místní databáze Accessu) soubor je často měněn uživateli jiné databázové aplikace soubor je nutné sdílet aplikace bude distribuována jednotlivým uživatelům a poté aktualizována (vhodné oddělit aplikaci od dat).
- 47 Access je schopen spolupracovat se soubory mnoha formátů, například se soubory dBase, Paradox, prakticky jakýmikoliv tabulkami SQL pomocí ODBC, s tabulkami Excelu, Lotusu, textovými soubory a mnoha dalšími. V případě importu nebo propojení postupujeme tak, že vybereme odpovídající položku v menu Soubor – Načíst externí data. Pokračujeme výběrem souboru nebo zdroje dat ODBC. V případě databáze vybereme požadovanou tabulku, v ostatních případech vybereme oblast dat (např. list v Excelu). V dalších oknech průvodce specifikujeme další vlastnosti.
Obr. 41 Průvodce importem – definování datových typů
Propojené tabulky je možné spravovat pomocí Správce propojených tabulek. Je přístupný z menu Nástroje – Správa databáze.
Obr. 42 Správce propojených tabulek
- 48 Export je možné provést z tabulky, výběrového nebo křížového dotazu pomocí menu Soubor – Export. Rychlý export lze v rámci produktů Microsoft Office realizovat pomocí nástrojů Propojení programů Office.
3.4.4.
Formuláře
Formuláře tvoří rozhraní mezi uživateli a vlastní aplikací v Accessu. Formuláře přitom slouží k několika účelům: •
Zobrazení a úpravy dat, včetně vkládání nebo odstraňování
•
Řízení toku aplikace – pomocí maker nebo VBA (Visual Basic for Applications) se určité činnosti dají zautomatizovat; typickým objektem pro spouštění těchto maker a procedur jsou příkazová tlačítka umístěná ve formulářích
•
Příjem vstupů nových dat
•
Zobrazování hlášení
•
Tisk informací – podobně jako sestavy
Formulář se skládá z těla, záhlaví a zápatí. Tělo se vztahuje k jednotlivým záznamům a může být i vícestránkové. Záhlaví a zápatí zůstávají stejná pro všechny záznamy. Kromě standardního formuláře, kdy se na jedné stránce zobrazuje vždy pouze jeden záznam, existují i formuláře průběžné, kdy se současně zobrazuje záznamů několik, podobně jako v datovém listu. Vhodný způsob pro zobrazení vztažených tabulek 1:N nabízejí podformuláře. Na obr. 43 je znázorněn formulář vypisující jednotlivé zaměstnance spolu s jejich dětmi. Výpis dětí je uskutečněn formou průběžného podformuláře.
Obr. 43 Formulář s podformulářem
Access umožňuje i tvorbu modálních formulářů, tzn. takových, které neumožňují pokračovat v práci s aplikací, dokud uživatel neudělá nějaký krok (např. stisknutí tlačítka). Ve formulářích fungují podobně jako v datových listech filtry, řazení, vyhledávání. Formuláře je možné tisknout, přičemž lze definovat, které prvky se tisknout budou a které ne.
- 49 3.4.4.1.
Vytváření formulářů
Ačkoliv v praxi budeme často navrhovat formuláře pomocí průvodce a teprve potom je upravíme v návrhovém zobrazení, předvedeme si vytvoření formuláře pomocí základních nástrojů ručně, abychom lépe porozuměli problematice. V základním okně Databáze se přesuneme do karty Formuláře a zvolíme vytvoření formuláře v návrhovém zobrazení.
Obr. 44 Formulář v návrhovém zobrazení
Současně s otevřením nového prázdného formuláře se zobrazí několik oken s návrhovými nástroji (jejich skrývání nebo zobrazování můžeme řídit pomocí přepínacích tlačítek na hlavním panelu nástrojů). Těmito nástroji jsou: •
Souprava nástrojů – obsahuje tlačítka se všemi ovládacími prvky, které můžeme při návrhu formuláře použít.
•
Seznam polí – objeví se až po výběru tabulky nebo dotazu, na kterém je formulář založen. Pokud myší přetáhneme nějakou položku do formuláře, zobrazí se na požadovaném místě odpovídající textové pole. Pokud před tažením vybereme ovládací prvek v Soupravě nástrojů, objeví se ve formuláři dané pole ve formě vybraného prvku (pokud je kompatibilní).
•
Okno s vlastnostmi – formulář jako celek, každá jeho část a každý ovládací prvek má k sobě přiřazen seznam s vlastnostmi. V tomto okně se jednotlivé vlastnosti pro jednotlivé prvky dají nastavit. Vlastnosti pro formulář jako celek zahrnují specifikaci zdroje záznamů pro daný formulář.
- 50 Vázaný ovládací prvek je spojen s polem podkladové tabulky nebo dotazu. Vázané ovládací prvky můžeme použít k zobrazení, zadávání a aktualizaci hodnot polí v databázi. Nevázaný ovládací prvek nemá zdroj dat. Nevázané ovládací prvky můžeme používat k zobrazování informací, čar, obdélníků a obrázků. Vypočítaný ovládací prvek používá jako zdroj dat výraz. Výraz může používat data z polí podkladové tabulky nebo dotazu, na kterém je založen daný formulář, nebo z jiného ovládacího prvku ve formuláři. Do formulářů v Accessu umísťujeme nejčastěji tyto ovládací prvky: •
Popisek – pevný text. Implicitně mají všechny ostatní ovládací prvky přidružen prvek Popisek.
•
Textové pole – zobrazuje textová, číselná, datová, časová a memo pole, obvykle z podkladové tabulky nebo dotazu. Pokud je povolena aktualizace, můžeme pomocí textového pole měnit hodnotu pole v tabulce. Textové pole je možné použít k výpočtu hodnot pomocí výrazů.
•
Skupina voleb – odděluje skupinu přepínacích tlačítek, přepínačů nebo zaškrtávacích políček; slouží k nabídnutí alternativ, ze které může uživatel vybrat jednu možnost.
•
Přepínací tlačítko, přepínač a zaškrtávací políčko – uchovává hodnotu pravda/nepravda, po zařazení do skupiny voleb mu lze přiřadit číselnou hodnotu; zvolením jednoho prvku tohoto typu se zruší výběr ostatních prvků ve skupině voleb
•
Pole se seznamem – obsahuje seznam potenciálních možností, možno vpisovat další hodnoty
•
Seznam – obsahuje seznam možností, nejde vpisovat, možnost násobného výběru
•
Příkazové tlačítko – slouží ke spouštění makra nebo procedury VBA
•
Rámeček nevázaného objektu – umožňuje zpřístupnění objektu OLE, který není součástí podřízených dat
•
Rámeček vázaného objektu – umožňuje zpřístupnění objektu OLE, který je součástí podřízených dat
•
Konec stránky
•
Karta
•
Podformulář/sestava – vložení jiného formuláře; vazby jsou zajištěny podle aktuálního nastavení relací
•
Čáry, obdélníky
•
Další ovládací prvky – je možné přidat jakýkoliv prvek ActiveX, který je nainstalován v systému
Ovládací prvky ve formuláři se dají v návrhovém zobrazení přesouvat, lze měnit jejich velikost a nastavovat jejich vlastnosti pomocí panelu Vlastnosti. Výběr více objektů probíhá přes klávesu Shift nebo tažením myši, menu Formát potom umožňuje zarovnávání a další užitečné operace. Připomeňme, že podobně jako v Excelu existuje možnost podmíněného formátování.
- 51 Pořadí prvků je stanoveno podle toho, jak byly prvky do formuláře přidávány. Přerovnání se provádí z menu Zobrazit – Pořadí prvků.
Obr. 45 Nejčastější ovládací prvky
3.4.5.
Sestavy
Sestavy nabízejí nejlepší způsob pro výstup informací, které jsou vybírány nebo počítány z dat v databázi. Mohou provádět srovnání, seskupovat data, vytvářet souhrnné výpočty, ale i například vytvářet hotové podklady pro tisk. Práce v návrhovém zobrazení probíhá podle podobných principů jako u formulářů včetně vkládání ovládacích prvků, těmi však nejčastěji bývají obrázky nebo grafy. Opět je přístupná souprava nástrojů, seznam polí a okno vlastností. Sestava se skládá z těla, může obsahovat záhlaví a zápatí stránky, navíc je možné nastavit záhlaví a zápatí sestavy. Při seskupování se dají definovat další záhlaví a zápatí pro každou skupinu. Základní rozdíl, kterým se sestavy od formulářů liší, je možnost řazení a hlavně seskupování dat. V okně Řazení a seskupování můžeme nadefinovat až deset polí nebo výrazů pro rozdělení dat do skupin.
Obr. 46 Řazení a seskupování
- 52 Sestava definovaná podle obr. 46 rozdělí všechny osoby podle států, potom podle měst a v jednotlivých městech bude záznamy řadit podle příjmení. Pro každé pole definující skupinu se dá definovat, zda se má v sestavě zobrazovat záhlaví a zápatí skupiny. Je možné seskupovat např. i podle počátečních znaků textového pole, měsíců u polí Datum/čas a podobně. Sestava na obr. 47 je seskupena podle nastavení zobrazeného na předchozím obrázku. V dolní části každé stránky tohoto příkladu se ještě zobrazuje aktuální datum a číslo stránky z celkového počtu stránek. V záhlaví každé skupiny je navíc spočítán součet skupiny, celkový součet je na úplném konci sestavy.
Obr. 47 Návrh sestavy
Příklady jednoduchých výpočtových polí jsou na obr. 48, tímto způsobem je vytvoří přímo průvodce. Jeho použití je pro rychlejší vytvoření sestavy a naformátování textu výhodné, úpravy se potom provedou v návrhovém zobrazení.
Obr. 48 Výpočtová pole
Podobně jako u formulářů je možné do těla sestavy vložit podsestavu, a dokonce i podformulář.
- 53 -
3.4.6.
Uložení dat ve formě stránky WWW
Prakticky stejně jako formulář či sestava se dá vytvořit stránka HTML. Rozdílem je, že HTML stránka je externím objektem. Připojovací řetězec odkazuje na .mdb soubor pomocí absolutní cesty, před publikování na webu je možné stránku modifikovat v jakémkoliv HTML editoru.
Obr. 49 Návrhové zobrazení WWW stránky
3.4.7.
Vytváření aplikací
3.4.7.1.
Makra
Pro automatizaci aplikací můžeme použít makra. Makro chápeme jako strukturovanou definici jedné nebo několika akcí, které má Access vykonat jako odezvu na nějakou definovanou událost (např. stisknutí tlačítka, změna hodnot v polích). Makra umožňují otevírat a provádět dotazy, zobrazovat tabulky či tisknout. Mohou obsahovat jednoduché podmínky, spouštět další makra nebo procedury VBA. Makra se dají využívat k těmto účelům: • • • • • • • • •
otevření nebo zavření tabulek, dotazů, formulářů nebo sestav tisk včetně tisku do souborů .rtf, .txt nebo .xls provedení výběrového nebo výkonného dotazu s parametry převzatými z ovládacích prvků formuláře vykonávání činnosti na základě podmínek, spouštění dalších maker nebo funkcí VBA nastavení hodnot ovládacích prvků, emulace klávesových akcí filtrování, navigace a vyhledávání v tabulkách nebo dotazech úprava panelů nástrojů vykonávání všech příkazů a nabídek Accessu práce s okny
- 54 • • •
zobrazování hlášení a přehrávání zvuků práce s objekty Accessu (kopírování, přejmenovávání, import, export…) spouštění externích aplikací
Definování maker je přístupné ze záložky Makra a probíhá v přehledném grafickém prostředí jako specifikace posloupnosti akcí. Pomocí menu Zobrazit se do návrhového zobrazení makra dají přidat sloupce Název makra a Podmínka.
Obr. 50 Tvorba maker
Důležité je, že makra se dají převést do kódu VBA (menu Nástroje – Makra – Převést makra do jazyka Visual Basic). Opačný převod není možný. Makro z obr. 50 bude po převodu vypadat následujícím způsobem: Option Compare Database Function Makro1() On Error GoTo Makro1_Err ' Nastaví kurzor myši DoCmd.Hourglass True ' Otevře formulář Osoby DoCmd.OpenForm "Osoby", acNormal, "", "", , acNormal ' Najde záznam se jménem Novák DoCmd.FindRecord "Novák", acAnywhere, False, acDown, False, acCurrent, True ' Zapípá Beep ' Přejde na pole Plat
- 55 DoCmd.GoToControl "Plat" ' Zvýší plat o 50% Forms!Osoby!Plat = Forms!Osoby!Plat * 1.5 Makro1_Exit: Exit Function Makro1_Err: MsgBox Error$ Resume Makro1_Exit End Function
Podmíněné výrazy Pokud chceme, aby příkaz se uskutečnil pouze za předpokladu, že je splněna nějaká podmínka, zobrazíme odpovídající sloupec a podmínku do něj zapíšeme na stejnou řádku jako daný příkaz. Pokud podmínka pravdivá není, Access pokračuje ve vykonávání příkazů na dalších řádkách. Abychom podmínku nemuseli pro víceřádkové příkazy na dalších řádkách opakovat, zapíšeme do nich tři tečky (…). Potom Access vyhodnotí podmínku pouze jednou a při jejím nesplnění přeskočí všechny označené řádky. Skupiny maker Makra vztahující se k danému problému můžeme začlenit do jedné skupiny maker. Pokud do sloupce Název makra vložíme nějaký text, je považován za název makra. Odpovídající řádka určuje začátek tohoto makra, konec je dán buď názvem dalšího makra nebo koncem skupiny. Volání tohoto makra probíhá prostřednictvím jeho názvu odděleného tečkou od názvu skupiny. Příklad na obr. 51 obsahuje dvě makra, Msg_makro.Zobraz otevře formulář, Msg_makro.Vykonej spustí příkazy v závislosti na tom, co uživatel ve formuláři zvolil, a pak celý formulář zavře.
Obr. 51 Skupina maker s podmínkami
- 56 Spojení maker s formuláři Nejčastější způsob spouštění maker je pomocí ovládacích prvků formulářů. Přiřazení probíhá buď pomocí lokálního submenu Sestavit událost (implicitně nabízí tvorbu nového makra), nebo raději z událostních vlastností daného ovládacího prvku (obr. 52).
Obr. 52 Přiřazení makra ovládacímu prvku
Vytvořme v návrhovém zobrazení jednoduchý formulář podle obr. 53. Skládá se ze dvou přepínačů tvořících jeden celek (zajištěno prostřednictvím skupiny voleb pojmenované Volba) a jednoho příkazového tlačítka. Tlačítku přiřadíme makro z obr. 51. Pořadové číslo vybraného přepínače je v makru přístupné prostřednictvím názvu odpovídající skupiny voleb, např. Forms!Msg_form!Volba vrátí číslo 2, pokud byla zvolena možnost Otevři tabulku. Odkaz na ovládací prvek začíná kategorií objektu (Forms pro formuláře, Reports pro sestavy), následuje jeho název a poté název ovládacího prvku. Vše je odděleno vykřičníky, Access ještě každou část názvu umístí do hranatých závorek (nutno zapsat ručně, pokud jméno obsahuje mezery). Kdybychom se chtěli odkazovat na konkrétní vlastnost objektu, oddělili bychom ji tečkou (např. Forms!Msg_form!Volba.Visible).
- 57 -
Obr. 53 Formulář použitý ke spuštění makra
Kromě spouštění pomocí klávesnice nebo myši mohou být makra aktivována při prakticky jakékoliv operaci v rámci formuláře nebo sestavy; při otevření nebo zavření, změnách dat, změnách aktivních prvků, při tisku, chybě nebo po vypršení určeného času. Nastavení obvykle probíhá z událostních vlastností daného objektu. 3.4.7.2.
Visual Basic for Applications
Ačkoliv je používání maker účinné, existují situace, kdy makro pro ošetření situace nestačí. V takovém případě je potom nutné využít procedur jazyka Visual Basic for Applications (VBA). Jedná se zejména o případy zachycování chyb, definice nových funkcí, předávání parametrů a přijímání návratových hodnot, automatického vytváření nových objektů, manipulace s daty v množině záznamů po jednotlivých záznamech, interakce s jinými aplikacemi, přímého volání funkcí Windows API nebo maximalizace výkonu. Na rozdíl od maker jsou moduly VBA zkompilované, takže běží o něco rychleji. Vývojové prostředí Protože VBA je společným programovacím jazykem pro všechny aplikace Office, dají se jeho prostřednictvím integrovat objekty z více aplikací. Visual Basic Editor je jejich společné vývojové prostředí s celou řadou ladicích nástrojů. Veškeré programy ve VBA se ukládají do modulů. Modul může být buď samostatný objekt nebo součást formuláře nebo sestavy. Procedury samostatného modulu jsou přístupné z jakékoliv části aplikace a načítají se do paměti při jejím otevření; zobrazíme je tak, že v okně Databáze zvolíme záložku Moduly. Naproti tomu moduly přidružené k formulářům nebo sestavám zůstávají v paměti pouze tehdy, když je jejich nadřazený objekt otevřen také. Nevýhodou je zase pomalejší načítání nadřazeného objektu. Používají se zejména pro ošetření událostí z daného formuláře nebo sestavy. Zobrazit se dají z návrhového zobrazení nadřazeného objektu z menu Zobrazit – Kód. Při exportu nadřazeného formuláře nebo sestavy se exportují spolu s nimi.
- 58 Klasické okno Visual Basic Editoru je ukázáno na obr. 54. Na pracovní ploše je stabilně umístěné okno Project Explorer, ukazující všechny moduly v databázi, a okno Project Properties. Aby toto okno zobrazovalo vlastnosti modulů přidružených formulářům a sestavám, je nutné přidružený objekt otevřít v návrhovém zobrazení (např. z okna Project Explorer: View – Object). V horní části hlavního okna s kódem jsou dva seznamy: Objekt a Procedura. Prostřednictvím seznamu Objekt vybereme příslušný formulář či sestavu, oddíl nebo libovolný ovládací prvek, který může generovat události. V seznamu Procedura lze vybrat všechny dostupné událostní procedury, tučně jsou znázorněny procedury s neprázdným kódem. Procedura je základní jednotka kódu jazyka Microsoft Visual Basic. Obsahuje posloupnost příkazů, které provádějí určitou operaci nebo výpočet určité hodnoty. Procedura události je procedura, která je spuštěna jako reakce na událost vyvolanou uživatelem, kódem programu nebo systémem. Podle toho, jestli procedury vracejí hodnotu, existují procedury typu Sub a procedury typu Function. Procedury typu Function (často nazývané pouze funkce) vracejí hodnotu, například výsledek výpočtu. Příkladem může být třeba integrovaná funkce Now, která vrací aktuální datum a čas. Díky možnosti vracet hodnotu bývají funkce často používány v mnoha výrazech aplikace Microsoft Access, při nastavení vlastností nebo ve výrazech kritérií ve filtru nebo v dotazu.
Obr. 54 Visual Basic Editor
Vložení nové procedury nebo funkce je možné ručně vepsáním Sub (resp. Function) a zvoleného názvu, po stisknutí klávesy Enter doplní Access příkazy End Sub nebo End Function. Jiná možnost je přes menu Insert. Procedury a funkce je možné ladit v krokovém režimu nebo nastavováním zarážek. Samozřejmostí je možnost používání okna Watches (Kukátko) pro sledování hodnot proměnných. K rychlému zjištění hodnoty proměnné v přerušené proceduře stačí podržení
- 59 kurzoru myši. V některých případech může pomoci okno Immediate, jakýsi příkazový řádek, kde můžeme zadávat příkazy a ty se bezprostředně vykonají. K vyhodnocení výrazu se používá otazník.
Obr. 55 Okno Immediate
Proměnné a konstanty Základní datové typy jsou představeny v tabulce: Datový typ Byte Boolean Integer Long Single Double Currency Decimal Date Object String (proměnný) String (pevný) Variant Uživatelská (def. Type)
Znak Hodnoty 0 až 255 True nebo False % -32 768 až 32 767 & -2 147 483 648 až 2 147 483 647 ! 1.4E-45 až 3.4E38 pro kladné i záporné hodnoty # 4.94E-324 až 1.79E308 pro kladné i záp. hodnoty @ -922 337 203 685 477.5808 až 922 337 203 685 477.5807 14 bajtů +/-79 228 162 514 264 337 593 543 950 335 bez desetinných míst, jinak 28 platných míst 8 bajtů 1.1.100 až 31.12.9999 4 bajty odkaz na jakýkoliv objekt 10 bajtů + délka $ 0 až přibližně 2 miliardy řetězce Délka řetězce $ 1 až přibližně 65 400 16 až přibližně 2 miliardy bajtů podle elementů
libovolná data včetně NULL jakýkoliv počet proměnných libovolného typu
Konstanty deklarujeme pomocí klíčového slova Const, proměnné pomocí Dim. Znak uvedený v tabulce slouží k rychlému, implicitnímu definování proměnné. Stačí při prvním
- 60 použití proměnné doplnit znak pro definici typu, např. Opakovat% = 500. Proměnné a konstanty mohou být přístupné v jedné proceduře, ve všech procedurách v modulu nebo v celé databázi. Proměnnou deklarovanou uvnitř procedury můžeme použít jen v této proceduře; pokud ji chceme užívat ve všech procedurách modulu, deklarujeme ji v odpovídající sekci modulu. K deklaraci proměnné nebo konstanty veřejné, tedy přístupné z celé databáze, použijeme klíčové slovo Public. Příklady deklarování konstant a proměnných: Public Const PI = 3.14159 Public Dim intMyInt As Integer Dim strJmeno As String * 20 'pevná délka řetězce 20 znaků Dim strJm (6 To 10) As String 'pole 5 řetězců proměnné délky Dim db As Database 'objekt databáze Access deklarované proměnné inicializuje už během kompilace – číselné proměnné jsou inicializovány na nulu, variantní jsou prázdné, řetězce s proměnnou délkou mají nulovou délku a řetězce s pevnou délkou jsou vyplněny znaky s nulovým ANSI kódem (Chr(0)). Pokud je příkaz Dim použit uvnitř procedury, jsou proměnné opětovně inicializovány při každém jejím spuštění. Pro dynamické deklarování pole v rámci procedury nebo změnu rozměrů pole deklarovaného v rámci procedury použijeme příkaz ReDim. Příkaz Static v rámci procedury zase zabezpečí, že proměnné si ponechávají svou hodnotu po celou dobu otevření modulu obsahujícího danou proceduru: ReDim strJmena (20) As String * 30 Static strNazv (11 To 20) As String * 20 Uživatelsky definovanou typovou strukturu definujeme pomocí příkazu Type: Type Osoba lngCislo As Long strJmeno As String strPrijmeni As String End Type Uvnitř procedury pak přistupujeme k jednotlivým proměnným takto: Osoba.strJmeno = "Josef" Odkazování na kolekce, objekty a vlastnosti Na konkrétní formulář nebo sestavu se odkazujeme názvem, je však navíc nutné uvést, která kolekce daný objekt obsahuje. Kolekcí rozumíme objekt, ve kterém jsou obsaženy další objekty. Otevřené formuláře jsou v kolekci Forms a sestavy v kolekci Reports. Název kolekce píšeme před vykřičník. Pokud název objektu obsahuje mezery nebo speciální znaky, musíme ho uzavřít do hranatých závorek. Prvky formulářů a sestav opět oddělujeme vykřičníkem, vlastnosti objektů píšeme za tečku. Forms![Seznam osob].ScrollBars (vlastnost ScrollBars formuláře Seznam osob) Forms![Seznam osob].[Výpis].Visible (vlastnost Visible podformuláře Výpis formuláře Seznam osob) Obecný odkaz na objekt má tvar
- 61 NázevKolekce![Název Objektu] Pokud je výhodnější se odkazovat pomocí řetězce, je tomuto výrazu ekvivalentní zápis NázevKolekce("Název Objektu") nebo zápis využívající relativní číslo objektu (počítáno od nuly) NázevKolekce(0) Na aktuální databázi a její tabulky se např. můžeme odkázat takto: Dim db As Databáze, tdf As TableDef Set db = DBEngine.Workspaces(0).Databases(0) Set tdf = db.TableDefs![Tabulka1] Poslední řádek je možné nahradit jedním z těchto zápisů: Set tdf = db.TableDefs("Tabulka1") Set tdf = db.TableDefs(0) Počet prvků kolekce udává vlastnost Count: Set Pocet = db.TableDefs.Count Pokud chceme zjednodušit odkazy na složité objekty, můžeme pomocí příkazu With stanovit některý objekt jako základní a pro odkazy na jeho objekty a vlastnosti používat zkrácenou notaci: With Tlačítko1 .Visible = True .Enabled = True End With Volání procedury Pomocí příkazu Call můžeme předat řízení podprogramu (jako argumenty můžeme použít proměnnou i výraz): Call MySub (intHodiny, intDny * 24) Alternativní syntax bez klíčového slova Call nepoužívá závorky pro argumenty: MySub intHodiny, intDny * 24 Rozhodování na základě podmínky Jednou cestou se program vydá, pokud podmínka bude splněna, a druhou cestou, pokud nebude. Syntax je opět dvojí; buď jednoduchá; If podmínka Then [příkazy] [Else příkazy] případně bloková: If podmínka Then [příkazy] [ElseIf podmínka2 Then [příkazy] ... [Else [příkazy]] End If
- 62 Cykly Pomocí příkazu Do…Loop definujeme blok příkazů, které chceme vykonat několikrát. Příkaz je univerzální, podmínku je možné vyhodnotit na začátku nebo konci cyklu: Do [{While | Until} podmínka] [příkazy] [Exit Do] [příkazy] Loop nebo pro vyhodnocení podmínky na konci cyklu: Do [příkazy] [Exit Do] [příkazy] Loop [{While | Until} podmínka] Kromě těchto obecných cyklů můžeme využít i klasické konstrukce: While podmínka [příkazy] Wend případně pro známý počet opakování For i = začátek To konec [Step krok] [příkazy] [Exit For] [příkazy] Next [i] nebo pro každou položku v nějaké kolekci nebo poli For Each prvek In skupina [příkazy] [Exit For] [příkazy] Next [prvek] Příkaz Select Case Pomocí příkazu Select Case můžeme podmíněně provést příkazy v závislosti na hodnotě nějakého výrazu porovnaného se seznamem nebo rozsahem hodnot: Select Case TestovacíVýraz [Case seznam 1 [příkazy 1]] ... [Case Else [příkazy n]] End Select Příklad: Select Case i Mod j Case 1 To 4, 7 To 9, 11, 13, Is > 30
- 63 strNadpis = "Situace 1" Case 5, 12 strNadpis = "Situace 2" Case Else strNadpis = "Situace 3" End Select Další příkazy Pomocí objektu DoCmd můžeme vykonat akci makra v rámci procedury. Syntax je DoCmd.Metoda [argument],... kde Metoda může nabývat některé z těchto hodnot: AddMenu, ApplyFilter, Beep, CancelEvent, Close, CopyDatabaseFile, CopyObject, DeleteObject, DoMenuItem, Echo, FindNext, FindRecord, GoToControl, GoToPage, GoToRecord, Hourglass, Maximize, Minimize, MoveSize, OpenDataAccessPage, OpenDiagram, OpenForm, OpenFunction, OpenModule, OpenQuery, OpenReport, OpenStoredProcedure, OpenTable, OpenView, OutputTo, PrintOut, Quit, Rename, RepaintObject, Requery, Restore, RunCommand, RunMacro, RunSQL, Save, SelectObject, SendObject, SetMenuItem, SetWarnings, ShowAllRecords, ShowToolbar, TransferDatabase, TransferSpreadsheet, TransferSQLDatabase, TransferText. Pokud například chceme otevřít formulář Form1 ve formulářovém zobrazení pro vkládání dat, zadáme DoCmd.OpenForm "Form1", acNormal, , , acAdd Velice často využijeme funkce MsgBox pro grafický výstup do dialogového okna a zachytávání uživatelem stisknutého tlačítka: Set intStisknuto = MsgBox (hláška[, tlačítka] [, nadpis] [, soubor-nápovědy, kontext]) Zmínili jsme se pouze o několika málo nejdůležitějších příkazech a objektech. Podrobný popis všech dalších příkazů, objektů, jejich vlastností a metod nalezneme například v nápovědě pro Microsoft Visual Basic. Událostní procedury Chceme-li jako reakci na událost spustit určitý kód, můžeme nastavit vlastnost události formuláře, sestavy nebo ovládacího prvku na hodnotu Procedura události (v návrhovém zobrazení v okně vlastností daného objektu vybereme kartu Událostní, vybereme požadovanou událost a stiskneme tlačítko Sestavit; poté vybereme Tvůrce kódu). Access vytvoří šablonu procedury události, do níž pak můžeme přidat kód, který má být při reakci na událost proveden. Access automaticky deklaruje událostní procedury pro každý objekt a událost v modulu formuláře či sestavy pomocí klíčového slova Private. Tím indikuje, že proceduru lze zpřístupnit pouze pro ostatní procedury v tomto modulu. Zachycování chyb Užitečnou vlastností VBA je schopnost zachycovat chyby, analyzovat je a případně podniknout nápravné činnosti. Slouží k tomu příkaz On Error. On Error [GoTo Návěstí] | Resume [Next] | GoTo 0 Příklad:
- 64 Private Sub Příkaz1_Click() On Error GoTo Err_Příkaz1_Click Screen.PreviousControl.SetFocus DoCmd.FindNext Exit_Příkaz1_Click: Exit Sub Err_Příkaz1_Click: MsgBox Err.Description Resume Exit_Příkaz1_Click End Sub Nejlepší způsob, jak se naučit pracovat se zdrojovým kódem, je prostudovat již nějaký hotový program; můžeme využít například ukázkové databáze Microsoftu, posloužit může i vlastní makro převedené Accessem do VBA. Ke zvládnutí práce ve VBA nám mohou vydatně pomoci i další aplikace sady Microsoft Office.
- 65 -
4.
Úvod do SQL
4.1.
Úvod
Structured Query Language (Strukturovaný dotazovací jazyk) je standard ANSI (American National Standards Institute) pro přístup k databázovým systémům. Základní funkcí SQL je tvorba univerzálních dotazů v databázích. Současně umožňuje zakládání tabulek (definování dat), ošetření přístupu k datům, sdílení dat nebo třeba zabezpečení databází. Jazyk SQL je výhodné používat v kombinaci s některým výkonnějším programovacím jazykem nebo databází. Struktura příkazů SQL je velmi názorná. Prvním standardem bylo SQL/86, masového využití dosáhla modifikace SQL/89, ve verzi SQL/92 byla funkčnost ještě rozšířena. Právě tato verze se stala základem většiny současných databázových systémů (např. Oracle, Sybase SQL Server, MS SQL Server, Informix Online, IBM DB2, Software AG ADABAS, ale i Access, FoxPro a mnoho dalších). Nejnovější verzí je SQL/99 (někdy označovaná jako SQL/3), ale už se pracuje na další verzi. Každá další verze je nadmnožinou verze původní, ne vždy je však zajištěna úplná kompatibilita. Navíc tvůrci každého komerčně dodávaného produktu „zdokonalují“ standard, takže každý interpret může obsahovat odlišná vyhrazená slova a zástupné znaky, může se lišit i syntax.
Pro příklady jsou použity následující tabulky: Struktura tabulky Osoby Field 1 2 3 4 5 6 7
Field Name Prijmeni Jmeno Titul Rodne_C Divize Nastup Plat
Prijmeni Novák Dvořák Novotná Miller Syrový Beran
Type Character Character Character Character Character Date Numeric
Protože se jednotlivé interprety SQL liší (ač existuje norma ANSI/ISO, je možné, že některé příklady v konkrétní aplikaci nebudou fungovat zcela korektně.
4.2.
Základní dotazy
SQL je založen na množinových a relačních operacích s několika modifikacemi a vylepšeními. Typický SQL dotaz má tvar: SELECT A1, A2, …, An FROM r1, r2, …, rm WHERE P, kde Ai reprezentuje atributy, ri reprezentuje relace, P je predikát. Tento dotaz je ekvivalentní následujícímu výrazu relační algebry: ΠA1, A2, …, An (σP(r1 × r2 × … × rm)) Výsledek každého SQL dotazu je relace. Následující pomocné příkazy vykonává příkaz SELECT v uvedeném pořadí (žádný z uvedených nemusí být použit, podrobnější popis a příklady použití jsou uvedeny níže): 1. FROM (výběr zdrojové tabulky) 2. WHERE (filtrovací podmínka) 3. DISTINCT (odstranění duplicitních řádek) 4. ORDER BY (setřídění) Zde je uvedena zjednodušená struktura příkazu SELECT, v jednotlivých interpretech dochází k větším či menším odchylkám: SELECT [ALL | DISTINCT] [Alias.] Atribut [AS Jméno_sloupce] [, [Alias.] Atribut [AS Jméno_sloupce] …] FROM [Jméno_databáze!]Tabulka [Lokální_alias] [[INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] JOIN [Jméno_databáze!]Tabulka [Lokální_alias] [ON Připojovací_podmínka …] [INTO Cílová_tabulka | [OUTFILE Jméno_souboru]] [WHERE Výběrová_podmínka [AND | OR Výběrová_podmínka2 …]] [GROUP BY Skupinový_sloupec [, Skupinový_sloupec …]] [HAVING Filtrovací_podmínka] [UNION [ALL] SELECTCommand] [ORDER BY Třídící_položka [ASC | DESC] [, Třídící_položka [ASC | DESC] …]]
4.2.1.
Vypsání všech sloupců tabulky: SELECT - FROM
SELECT * FROM Osoby Příkaz provede vypsání všech sloupců tabulky.
- 67 Prijmeni Novák Dvořák Novotná Miller Syrový Beran
Symbol * v SQL znamená symbol pro všechny sloupce.
4.2.2.
Zamezení vypisování duplicitních řádek: DISTINCT
SQL umožňuje duplicity v relacích i ve výsledcích dotazů (aby byl výsledek dotazu duplicitní, musí být duplicitní ve všech vypisovaných sloupcích). Pro eliminaci duplikátů vložíme klíčové slovo DISTINCT za SELECT. SELECT DISTINCT * FROM Osoby
4.2.3.
Vypisování duplicitních řádek: ALL
Klíčové slovo ALL specifikuje, že duplikáty odstraněny nebudou. SELECT ALL * FROM Osoby
4.2.4.
Vypsání vyjmenovaných sloupců
SELECT Prijmeni, Jmeno, Titul, RodneC FROM Osoby Příkaz provede vypsání všech vyjmenovaných sloupců. Prijmeni Novák Dvořák Novotná Miller Syrový Beran
SELECT Prijmeni, Jmeno, Titul, Plat, (Plat/30) FROM Osoby Příkaz definuje nový sloupec, který se spočte z ostatních sloupců. Prijmeni Novák Dvořák Novotná Miller Syrový Beran
Jmeno Titul František Pavel Ing. Jitka Jiří RNDr. Petr Vladimír
- 68 V tomto příkladě se přidá plat v eurech, neboli plat dělený 30. Nový sloupec tabulky je pojmenován automaticky dle konvencí použité databáze. K definici vypočteného sloupce lze použít i volání některého složitějšího programu, který daný sloupec vypočte. Podobně se dá pracovat s textovými sloupci. SELECT Prijmeni + ' ' + Jmeno, Plat FROM Osoby Expr1000 Novák František Dvořák Pavel Novotná Jitka Miller Jiří Syrový Petr Beran Vladimír
V některých interpretech se pracuje s řetězci jiným způsobem (operátor || v ANSI, funkce CONCAT() v MySQL)
4.2.6.
Přejmenování: AS
Mechanismus pro přejmenovávání relací je v SQL řešen pomocí klauzule AS: staré-jméno AS nové-jméno Textové řetězce se podle ANSI uzavírají do jednoduchých uvozovek, většina interpretů pracuje i s dvojitými. SELECT Prijmeni, Jmeno, Titul, Plat, (Plat/30) AS 'Euro' FROM Osoby Pokud máme potřebu si nově založený sloupec pojmenovat, učiníme tak pomocí klíčového slova AS. Prijmeni Novák Dvořák Novotná Miller Syrový Beran
Jmeno Titul František Pavel Ing. Jitka Jiří RNDr. Petr Vladimír
Příkaz provede pojmenování nového sloupce na „Euro“. Popis sloupce musí odpovídat konvencím použitého programu.
4.2.7.
Použití konstantního sloupce
SELECT 'Příjmení:' AS 'Prijmeni', Prijmeni AS 'Hodnota', 'Jméno:' AS 'Jmeno', Jmeno AS 'Hodnota2' FROM Osoby Uvedený postup umožňuje založení konstantního sloupce, tj. sloupce, který obsahuje konstantní hodnotu; například nápis. Prijmeni Příjmení: Příjmení:
Hodnota Novák Dvořák
Jmeno Hodnota2 Jméno: František Jméno: Pavel
- 69 Příjmení: Příjmení: Příjmení: Příjmení:
Novotná Miller Syrový Beran
Jméno: Jméno: Jméno: Jméno:
Jitka Jiří Petr Vladimír
Příkazu SELECT můžeme použít i bez odkazu na tabulku. Výsledek však tabulka bude: SELECT 1+1 AS 'Výsledek' Výsledek 2
4.2.8.
Výběr řádků: WHERE
Klauzule WHERE odpovídá operaci výběr v relační algebře. Skládá se z predikátu zahrnujícího atributy relací, které jsou v klauzuli FROM SELECT * FROM Osoby WHERE Jmeno='Jiří' Slouží k výběru řádků, které odpovídají určitým podmínkám (podmínce). Prijmeni Miller
Jmeno Jiří
Titul Rodne_C Divize Nastup RNDr. 6711251122 1 01.09.01
Plat 30 000.00
V některých interpretech (např. firmy Borland) se při výběru specifikovaném pomocí operátoru „=“ vypíší všechny položky, které začínají stejně jako výraz, s kterým porovnáváme (např. Jmeno='Ji' vypíše oba řádky). Pokud v nich chceme porovnávat s přesnou hodnotou, musíme použít znaménko přesně rovno „==“. SQL používá logické spojky AND, OR a NOT. Umožňuje tak použití aritmetických výrazů jako operandů pro operátory porovnání. Operátor NOT se vztahuje na bezprostředně následující výraz. Přehlednější a jistější formy dosáhneme použitím závorek „(“ a „)“. SELECT * FROM Osoby WHERE Jmeno='Jiří' OR Jmeno='Jitka' Slouží k výběru řádků, které odpovídají určitým podmínkám (podmínce). Prijmeni Novotná Miller
Jmeno Jitka Jiří
Titul
Rodne_C Divize Nastup 7761251234 3 01.05.00 RNDr. 6711251122 1 01.09.01
Mezi srovnávací operátory patří: •
= rovno
•
<>, != není rovno
•
< menší než
•
<= menší nebo rovno
•
> větší než
•
>= větší nebo rovno
Plat 16 500.00 30 000.00
- 70 Některé interprety podporují použití vykřičníku pro negaci (!<, !>). Pro výběr podle kalendářního data použijeme příkaz SELECT * FROM Osoby WHERE Nastup <= {31.12.00} Zobrazí všechny pracovníky z tabulky, kteří nastoupili před 1. lednem 2001. Prijmeni Dvořák Novotná Beran
Jmeno Pavel Jitka Vladimír 4.2.8.1.
Titul Ing.
Rodne_C 6908303215 7761251234 6105174221
Divize 2 3 3
Nastup 01.03.99 01.05.00 01.11.97
Plat 25 000.00 16 500.00 21 500.00
Zobrazení hodnot v určitém rozpětí: BETWEEN
• SQL zahrnuje operátor porovnání BETWEEN pro zjednodušení klauzule WHERE, který specifikuje hodnotu z určitého intervalu SELECT * FROM Osoby WHERE Plat BETWEEN 14000 AND 21000 BETWEEN slouží k zobrazení hodnot sloupce, které se nacházejí v určitém rozpětí. Příkaz je možné zapsat bez použití BETWEEN: SELECT * FROM Osoby WHERE Plat>=14000 AND Plat<=21000 Prijmeni Novák Novotná Syrový
Jmeno František Jitka Petr
Titul
Rodne_C 8103120781 7761251234 7202122516
Divize 2 3 2
Nastup 01.09.01 01.05.00 01.09.01
Plat 14 000.00 16 500.00 20 000.00
Výsledkem je seznam pracovníků, kteří mají plat v rozmezí 14000 a 21000. 4.2.8.2.
Otestování některého znaku v řetězci: LIKE
SQL zahrnuje operátor pro porovnávání řetězců znaků. Vzorky jsou popsány použitím dvou speciálních znaků •
znak % (procento) – vyhovuje jakémukoliv podřetězci (odpovídá * při dotazu na jména souborů)
•
znak _ (podtržítko) – vyhovuje jakémukoliv znaku (odpovídá ?)
SELECT * FROM Osoby WHERE Jmeno LIKE '_ _a%' Uvedený postup slouží k otestování existence některého znaku na libovolném místě řetězce. Prijmeni Novák Beran
Jmeno František Vladimír
Titul
Rodne_C Divize 8103120781 2 6105174221 3
Nastup 01.09.01 01.11.97
Plat 14 000.00 21 500.00
- 71 V tomto případě uvedený postup vyfiltruje všechny osoby, které mají na třetím místě ve jméně písmeno „a“. Stejného efektu můžeme dosáhnout pomocí dalších funkcí SQL: SELECT * FROM Osoby WHERE SUBSTRING(Jmeno,3,1)='a' Pro porovnání řetězce „100%“ stačí zadat LIKE '100\%' ESCAPE '\'
4.2.9.
Uspořádání zobrazení n-tic, tříděný výstup: ORDER BY
Pro výpis tabulky, setříděné podle některého sloupce, slouží klauzule ORDER BY. SELECT * FROM Osoby ORDER BY Prijmeni Prijmeni Beran Dvořák Miller Novák Novotná Syrový
Tabulka bude setříděna podle příjmení, a to vzestupně. Pro sestupné třídění se použije klauzule DESC (descending), pro implicitní vzestupné třídění ASC (ascending). SELECT * FROM Osoby ORDER BY Prijmeni DESC Prijmeni Syrový Novotná Novák Miller Dvořák Beran
Výsledkem je tabulka setříděná sestupně podle příjmení. Pokud chceme třídit tabulku např. podle prvního sloupce (jméno nemusíme uvádět), použijeme: SELECT * FROM Osoby ORDER BY 1 DESC 4.2.9.1.
Třídění podle více atributů
SELECT * FROM Osoby ORDER BY Prijmeni, Jmeno, Titul
- 72 Tabulka je současně setříděna nejprve podle příjmení a pak podle jména.
4.2.10.
Výstup do textového souboru: TO FILE
Většina interpretů má zabudováno, syntax bývá rozdílná. Někdy je nutné specifikovat další parametry. Následující příklad provede výpis výsledku do textového souboru „\tmp\soubor.txt“. SELECT * INTO OUTFILE '/tmp/soubor.txt' FROM Osoby
4.3.
Agregační dotazy
Agregační dotazy zpracovávají hodnoty z celých sloupců tabulky; mluvíme o funkcích souhrnných. Jazyk SQL podporuje tyto funkce: •
SUM() – součet numerických hodnot ve sloupci
•
MIN() – minimální hodnota ve sloupci
•
MAX() – maximální hodnota ve sloupci
•
COUNT() – počet hodnot ve sloupci
•
AVG() – aritmetický průměr numerických hodnot ve sloupci
Vnořování uvedených funkcí do sebe, např. MIN(SUM()), není ve většině implementací SQL povoleno. U agregačního dotazu zpracovává SQL klauzule v následujícím pořadí (některé uvedené klauzule nemusí příkaz obsahovat): 1. FROM 2. WHERE 3. GROUP BY 4. HAVING 5. výpočet hodnot řádku 6. DISTINCT 7. ORDER BY
4.3.1.
Příklady souhrnných funkcí
SELECT SUM(Plat) AS 'Celkem' FROM Osoby Uvedený postup použijeme, pokud chceme provést součet sloupce tabulky. Podobně postupujeme i při aplikaci ostatních klauzulí. Celkem 127 000.00 Zobrazen je součet všech platů. Pokud chceme součet platů pro konkrétní divizi, využijeme klauzuli WHERE.
- 73 SELECT SUM(Plat) AS 'Celkem_technici' FROM Osoby WHERE Divize=2 Celkem_technici 59 000.00 Pro výpis počtu všech n-tic použijeme příkazu: SELECT COUNT (*) FROM Osoby
4.3.2.
Seskupení hodnot: GROUP BY
SELECT Divize, SUM(Plat) AS 'Celkem' FROM Osoby GROUP BY Divize Chceme-li provést seskupení součtů k některému sloupci, použijeme klauzuli GROUP BY. Divize 1 2 3
Celkem 30 000.00 59 000.00 38 000.00
Zobrazen je součet platů v jednotlivých divizích. Chceme-li zahrnout např. pouze platy převyšující 15000, doplníme klauzuli WHERE. SELECT Divize, SUM(Plat) AS 'Celkem' FROM Osoby WHERE Plat>15000 GROUP BY Divize Divize 1 2 3
Celkem 30 000.00 45 000.00 38 000.00
4.3.3.
Kritéria pro zařazení: HAVING
SELECT Divize, SUM(Plat) AS 'Celkem' FROM Osoby GROUP BY Divize HAVING SUM(Plat)>35000 Klauzule omezuje rozsah tabulky tím, že z agregačních řádků vyřadí ty, které neodpovídají uvedené podmínce. Divize 2 3
Celkem 59 000.00 38 000.00 Výsledkem je součet platů v jednotlivých divizích, kdy součet platů přesahuje hodnotu
3500.
- 74 -
4.4.
Nulové hodnoty 4.4.1.1.
Výpis řádků s prázdným sloupcem: EMPTY()
SELECT * FROM Osoby WHERE EMPTY(Titul) Slouží pro vypsání všech řádků tabulky, které nemají uvedenou prázdnou položku. Prijmeni Novák Novotná Syrový Beran
Příkaz vypíše všechny pracovníky, kteří mají nějaký titul. Pokud naopak chceme vypsat řádky, v nichž položka uvedená je, zadáme: SELECT * FROM Osoby WHERE NOT EMPTY(Titul): Prijmeni Dvořák Miller
Jmeno Pavel Jiří
Titul Ing. RNDr.
Rodne_C Divize 6908303215 2 6711251122 1
Nastup 01.03.99 01.09.01
Plat 25 000.00 30 000.00
N-tice mohou mít hodnotu NULL, skutečná hodnota je neznámá nebo neexistuje. Výsledek jakékoliv aritmetické operace zahrnující NULL je UNKNOWN. TRUE OR UNKNOWN FALSE OR UNKNOWN UNKNOWN OR UNKNOWN TRUE AND UNKNOWN FALSE AND UNKNOWN UNKNOWN AND UNKNOWN
= = = = = =
TRUE UNKNOWN UNKNOWN UNKNOWN FALSE UNKNOWN
•
Výsledek klauzule WHERE je brán jako FALSE, je-li UNKNOWN
•
„P IS UNKNOWN“ se vyhodnotí jako TRUE, jestliže predikát P je vyhodnocen jako UNKNOWN
SELECT * FROM Osoby WHERE Titul IS NULL Všechny souhrnné operace s výjimkou COUNT(*) ignorují n-tice s nulovými hodnotami na souhrnných atributech. Pokud ve sloupci není žádná nenulová částka, výsledek je nula.
- 75 -
4.5.
Dotazy na více tabulek
Klauzule FROM odpovídá kartézskému součinu z relační algebry. Vypíše relace, které mají být procházeny při vyhodnocování výrazu.
4.5.1.
Výpis svázaných informací z druhé tabulky
SELECT Prijmeni, Jmeno, Titul, Popis FROM Osoby, Divize WHERE Osoby.Divize=Divize.Divize Provede vypsání názvů divizí k jednotlivým pracovníkům podle vazby přes společný atribut 'Divize'. Prijmeni Novák Dvořák Novotná Miller Syrový Beran
Jmeno František Pavel Jitka Jiří Petr Vladimír
Titul Ing. RNDr.
Popis Technici Technici Obchodníci Ředitel Technici Obchodníci
Podmínka WHERE je nutná, jinak bychom získali kartézský součin obou relací, neboli kombinaci všech prvků z obou tabulek. V našem případě by výsledek příkazu bez WHERE neměl praktický význam: Prijmeni Novák Novák Novák Dvořák Dvořák Dvořák Novotná Novotná Novotná Miller Miller Miller Syrový Syrový Syrový Beran Beran Beran
Jmeno František František František Pavel Pavel Pavel Jitka Jitka Jitka Jiří Jiří Jiří Petr Petr Petr Vladimír Vladimír Vladimír
Titul
Ing. Ing. Ing.
RNDr. RNDr. RNDr.
Popis Ředitel Technici Obchodníci Ředitel Technici Obchodníci Ředitel Technici Obchodníci Ředitel Technici Obchodníci Ředitel Technici Obchodníci Ředitel Technici Obchodníci
Pro vypsání všech atributů z tabulky 'Divize' můžeme použít: SELECT Prijmeni, Jmeno, Titul, Divize.* FROM Osoby, Divize WHERE Osoby.Divize=Divize.Divize Prijmeni Novák
Jmeno František
Titul
Divize 2
Popis Technici
- 76 Dvořák Novotná Miller Syrový Beran
Pavel Jitka Jiří Petr Vladimír
4.5.2.
Ing.
2 3 1 2 3
RNDr.
Technici Obchodníci Ředitel Technici Obchodníci
Množinové operace
Množinové operace UNION, INTERSECT a EXCEPT operují na relacích a odpovídají operacím relační algebry ∪, ∩ a –. Každá z těchto operací automaticky eliminuje duplikáty; chceme-li i duplikáty, použijeme vícemnožinové verze UNION ALL, INTERSECT ALL a EXCEPT ALL. Operace INTERSECT a EXCEPT nejsou zahrnuty ve všech interpretech. Předpokládejme, že kromě tabulky „Osoby“ máme další tabulku, např. seznam lidí účastnících se sportovní akce: Struktura tabulky Sportovci Field 1 2 3
Field Name Jmeno Krestni Sport
Jmeno Kolář Petera Novotná Miller
Type Character Character Character
Krestni Petr Jan Jitka Jiří
4.5.2.1.
Width 40 20 20
Dec
Index No No No
Sport basketbal volejbal volejbal tenis
Sjednocení: UNION
(SELECT Prijmeni, Jmeno FROM Osoby) UNION (SELECT Jmeno, Krestni FROM Sportovci) Prijmeni Novák Dvořák Novotná Miller Syrový Beran Kolář Petera
Jmeno František Pavel Jitka Jiří Petr Vladimír Petr Jan
Příkaz SELECT musí z obou tabulek vybrat stejný počet sloupců kompatibilního typu. Jako záhlaví se bere záhlaví první tabulky. Pokud bychom použili UNION ALL, řádky se jmény Novotná, Miller by se opakovaly dvakrát.
- 77 4.5.2.2.
Průnik: INTERSECT
(SELECT Prijmeni, Jmeno FROM Osoby) INTERSECT (SELECT Jmeno, Krestni FROM Sportovci) Prijmeni Novotná Miller
Jmeno Jitka Jiří 4.5.2.3.
Rozdíl: EXCEPT
(SELECT Prijmeni, Jmeno FROM Osoby) INTERSECT (SELECT Jmeno, Krestni FROM Sportovci) Prijmeni Novák Dvořák Syrový Beran
Jmeno František Pavel Petr Vladimír
4.5.3.
Proměnné n-tic
Proměnné n-tic jsou definovány v klauzuli FROM pomocí klauzule AS 4.5.3.1.
Samosloučení
SELECT Osoby.Prijmeni, Tabulka2.Prijmeni FROM Osoby, Osoby AS Tabulka2 WHERE Osoby.Prijmeni
Prijmeni_B Dvořák Syrový Beran Syrový Beran Novák Dvořák Miller Syrový Beran Novák Dvořák Syrový Beran Syrový
- 78 -
4.5.4.
Spojené relace (Joined relations)
Operace spojení zpracovává dvě relace a jako výsledek vrátí jednu relaci. Tyto operace jsou typicky používány jako poddotazy v klauzuli FROM. SELECT * FROM A LEFT JOIN B ON A.id=B.id Typ spojení – definuje, kolik je n-tic v každé relaci, které neodpovídají žádné n-tici v jiné relaci. •
[INNER] JOIN – vnitřní spojení
•
LEFT [OUTER] JOIN – levé vnější spojení
•
RIGHT [OUTER] JOIN – pravé vnější spojení
•
FULL [OUTER] JOIN – plné vnější spojení
Vnitřní spojení: Spojení, při kterém jsou záznamy ze dvou tabulek spojeny ve výsledcích dotazu, pouze pokud hodnoty ve spojených polích splní zadanou podmínku. V dotazu je výchozím spojením. Vnější spojení: Spojení, u něhož jsou shodné záznamy ze dvou tabulek zkombinovány do jednoho záznamu ve výsledné relaci, a z jedné tabulky jsou dodány všechny záznamy, i když hodnoty ve spojeném poli neodpovídají hodnotám v druhé tabulce. Levé spojení přidává všechny záznamy z levé strany operace LEFT JOIN, pravé spojení ze strany pravé. Podmínka spojení – definuje, které n-tice ve dvou relacích si odpovídají a které atributy jsou ve výsledku spojení. •
NATURAL (přirozené) – odpovídají si všechny se stejným názvem
•
USING (A1, A2, …, An) – odpovídají si vyjmenované atributy
•
ON <predikát> – možno přiřadit jinak pojmenované atributy
Výraz A LEFT JOIN B USING (C1,C2,C3,...) je ekvivalentní s výrazem A LEFT JOIN B ON (A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,...) 4.5.4.1.
Příklady spojených relací
Pro názornost přepíšeme divizi u osoby Novák na číslo neexistující divize 8 a do tabulky Divize připíšeme další dva řádky. Příkaz má tvar SELECT * FROM <Specifikace>, kde <Specifikace> je vždy uvedena níže. Pro zkrácení výpisu nebudeme v textu uvádět sloupce Jmeno, Titul, Rodne_C a Nastup. Tabulka Osoby Prijmeni Novák Dvořák Novotná Miller Syrový Beran
Popis Technici Obchodníci Ředitel Technici Obchodníci Marketing Školitelé
Osoby NATURAL FULL JOIN Divize (stejné jako Osoby FULL JOIN Divize USING Divize) Prijmeni Novák Dvořák
Divize 8 2
Plat 14 000.00 25 000.00
Popis NULL Technici
- 80 Novotná Miller Syrový Beran NULL NULL
3 1 2 3 4 5
16 500.00 30 000.00 20 000.00 21 500.00 NULL NULL
Obchodníci Ředitel Technici Obchodníci Marketing Školitelé
4.6.
Vnořené poddotazy
SQL poskytuje mechanismus pro vnořování poddotazů. Poddotaz je výraz SELECTFROM-WHERE, který je vnořen do jiného dotazu. Obvyklé použití poddotazů je provádění testů na členství v množině, porovnávání množin a kardinalitu množin. SELECT Jmeno, Prijmeni, (SELECT MAX(Plat) FROM Osoby) AS 'Maximální plat' FROM Osoby Prijmeni Novák Dvořák Novotná Miller Syrový Beran
Pro další příklad upravíme tabulku „Osoby“ tak, že vyprázdníme „Divizi“ u pracovníka „Novák“. Tabulka tedy vypadá takto: Prijmeni Novák Dvořák Novotná Miller Syrový Beran
Nyní chceme získat seznam pracovníků zařazených do divizí. SELECT Prijmeni, Jmeno, Titul FROM Osoby WHERE Osoby.Divize IN (SELECT Divize FROM Divize) Prijmeni Dvořák Novotná Miller Syrový Beran
Jmeno Pavel Jitka Jiří Petr Vladimír
Titul Ing. RNDr.
Nastup 01.09.01 01.03.99 01.05.00 01.09.01 01.09.01 01.11.97
Dotaz z minulého příkladu můžeme zapsat pomocí EXIST[S]. SELECT Prijmeni, Jmeno, Titul FROM Osoby WHERE EXIST (SELECT Divize FROM Divize WHERE Divize.Divize=Osoby.Divize) Predikát EXIST provádí test existence hodnoty v tabulce.
4.6.3.
Poddotaz s ANY (SOME)
Totéž zapsáno pomocí predikátu ANY: SELECT Prijmeni, Jmeno, Titul FROM Osoby WHERE Divize = ANY (SELECT Divize FROM Divize) Ekvivalence F = ANY r ⇔ ∃ t (t ∈ r ∧ [F = t]) platí pro libovolnou operaci porovnání. Výraz (F = ANY r) je ekvivalentní s (F IN r), (F <> ANY r) je jiné než (F NOT IN r).
4.6.4.
Poddotaz s ALL
Predikát ALL provádí test na shodu všech hodnot. Platí pro libovolnou operaci porovnávání, že F > ALL r ⇔ ∀ t (t ∈ r ∧ [F > t]). Výraz (F <> ALL r) je ekvivalentní s (F NOT IN r), (F = ALL r) je jiné než (F IN r).
4.7.
Příkazy k modifikaci tabulky 4.7.1.
Přidání řádku: INSERT INTO
INSERT INTO Divize VALUES ('4', 'Marketing') Divize 1 2 3 4
Popis Ředitel Technici Obchodníci Marketing
Při přidávání řádku do tabulky nemusí být uvedeny všechny atributy tabulky, navíc jejich pořadí nemusí odpovídat pořadí, v jakém byly založeny: INSERT INTO Divize (Popis, Divize) VALUES ('Marketing', '4')
- 82 -
4.7.2.
Vymazání řádku: DELETE FROM
DELETE FROM Divize WHERE Popis = 'Obchodníci' Provede vymazání záznamu, ve kterém je popis divize 'Obchodníci'. Divize 1 2 3
Popis Ředitel Technici Obchodníci
4.7.3.
Aktualizace dat: UPDATE
UPDATE Divize SET Popis = 'Manažer' WHERE Divize = '1' Provede záměnu popisu divize na 'Manažer' u všech záznamů, kde číslo divize je '1'. Divize 1 2 3
Popis Manažer Technici Obchodníci
UPDATE Osoby SET Plat = Plat * 1.3 Všem osobám zvýší platy o 30 %.
4.8.
Jazyk definice dat (Data definition language; DDL) 4.8.1.
Vytváření tabulky: CREATE TABLE
CREATE TABLE Firmy (Nazev Mesto Obrat
char(15) NOT NULL, char(30), int)
Příklad vytvoří následující tabulku: Struktura tabulky: Firmy Field 1 2 3
Name Nazev Mesto Obrat
Type Character Character Integer
Width 15 30 4
Dec
Index No No 0 No
Příkaz provede založení prázdné tabulky podle uvedených parametrů. Význam parametrů jednotlivých atributů je následující: •
char(n) – řetězec znaků s pevnou délkou n.
•
varchar(n) – řetězec znaků s proměnlivou délkou (maximálně n).
•
int – celé číslo; závislé na implementaci.
•
smallint – krátké celé číslo; závislé na implementaci.
- 83 •
numeric(p,d) – číslo s pevnou desetinnou čárkou s přesností na p míst s d místy vpravo od desetinné tečky.
•
real, double precision – čísla s plovoucí desetinnou čárkou; závislé na implementaci.
•
float(n) – číslo s plovoucí desetinnou čárkou s přesností nejméně na n míst.
•
date – data obsahující (4bitový) rok, měsíc a den.
•
time – čas v hodinách, minutách a sekundách.
Ve všech doménových typech jsou povoleny nulové hodnoty. Deklarování atributu NOT NULL je zakazuje. Podobně atribut UNIQUE nepovoluje duplicity. Při vytváření tabulky můžeme vytvořit primární klíč, případně omezit integritu. CREATE TABLE Pobocka (Jmeno char(15) PRIMARY KEY, Mesto char(30), Obrat integer, CHECK (Obrat >= 0)) Příklad ukazuje vytvoření tabulky s primárním klíčem Jmeno, v SQL/92 vytvoření primárního klíče automaticky zajišťuje i NOT NULL. Zároveň příklad zaručuje, že hodnota atributu Obrat bude nezáporná. Můžeme také vytvořit primární klíč z několika sloupců, pro pojmenování můžeme použít vyhrazeného slova CONSTRAINT. Pojmenovat můžeme i ověřovací pravidlo. CREATE TABLE Pobocka (Jmeno char(15) NOT NULL, Mesto char(30), Obrat integer, [CONSTRAINT jméno_klíče] PRIMARY KEY (Jmeno, Mesto), [CONSTRAINT jméno_pravidla] CHECK (Obrat BETWEEN 0 AND 1000))
4.8.2.
Úprava struktury tabulky: ALTER TABLE
Příkaz ALTER TABLE přidá atributy k existující relaci. Ke všem n-ticím v relaci přiřadí NULL jako hodnotu pro nové atributy. ALTER TABLE Divize ADD Poznamka char(30) Vloží nový sloupec specifikovaného typu Divize 1 2 3
Popis Ředitel Technici Obchodníci
Poznamka
ALTER TABLE Divize RENAME COLUMN Poznamka TO Nazev Divize 1 2 3
Popis Ředitel Technici Obchodníci
Nazev
- 84 Přejmenování atributu 'Poznamka' na 'Nazev'. Další možnosti úprav tabulky jsou zřejmé z možné syntaxe příkazu (DROP maže, některé interprety používají navíc CHANGE pro přejmenování). ALTER TABLE Jméno_tabulky Specifikace [, Specifikace …] Specifikace: ADD [COLUMN] nove_jmeno ADD INDEX [jmeno_indexu] (sloupec_indexu,…) ADD PRIMARY KEY (sloupec_indexu,…) DROP [COLUMN] jmeno_sloupce DROP PRIMARY KEY RENAME [TO] nove_jmeno_tabulky ORDER BY sloupec
4.8.3.
Smazání tabulky: DROP TABLE
DROP TABLE Pobocka Smaže tabulku (bez potvrzení).
4.9.
Vytvoření pohledu
Příkaz CREATE VIEW Dotaz AS , kde je jakýkoliv dovolený výraz, se často používá pro skrývání určitých dat před nepovolanými uživateli. Odpovídá vytvoření nového dotazu s názvem Dotaz. CREATE VIEW Vypis AS SELECT Prijmeni, Jmeno, Titul FROM Osoby Příkaz vytvoří dotaz pro vypsání všech vyjmenovaných sloupců: Prijmeni Novák Dvořák Novotná Miller Syrový Beran
Jmeno František Pavel Jitka Jiří Petr Vladimír
Titul Ing. RNDr.
Přidáme-li novou n-tici do pohledu např. pomocí INSERT INTO Vypis VALUES ('Kolář', 'Petr', NULL), vloží se do původní tabulky n-tice, v níž kromě atributu Titul budou mít hodnotu NULL i všechny atributy nezahrnuté do pohledu.
- 85 -
5.
Databázový server MySQL 5.1.
MySQL
Mezi velice oblíbené databáze patří databázový server MySQL od švédské společnosti MySQL AB. Je možné ho použít pro více platforem operačních systémů, pro nekomerční použití je distribuován zdarma jako open-source produkt, kromě binárních souborů je možné zdarma stáhnout i soubory se zdrojovým kódem (http://www.mysql.com/downloads). MySQL server je často používán zejména jako součást webových aplikací, ale používají jej komerčně i firmy zvučných jmen. Jedná se o rychlý, spolehlivý a výkonný relační databázový systém, který umožňuje zpracování velkého množství dat. Vyniká jednoduchou administrací, přičemž nabízí API pro několik programovacích jazyků včetně C++, jakož i rozhraní pro připojení dalších aplikací prostřednictvím ODBC (Microsoft Open Database Conectivity) a JDBC (Java Database Connectivity). Spuštění MySQL v prostředí Unix a Linux Start serveru MySQL se provede z příkazové řádky pomocí příkazu safe_mysqld (nebo spíše pro spuštění na pozadí safe_mysqld &). K ukončení činnosti MySQL použijeme příkaz mysqladmin s parametrem shutdown. Automatický start a zastavení MySQL zajišťuje skript mysql.server, uložený v adresáři /usr/local/mysql/share/mysql, s parametry start nebo stop. Spuštění MySQL v prostředí Windows Databázový server MySQL může v prostředí Windows NT/2000/XP fungovat jako služba operačního systému. Instalace služby se provede pomocí souboru mysqld.exe a parametru --install. Její spouštění je možné klasickými prostředky GUI Windows, pomocí příkazu net start mysql nebo prostřednictvím utility WinMySQLadmin. Pokud nechceme spustit MySQL jako službu (nebo to systém neumožňuje), použijeme příkaz mysqld -standalone pro spuštění nebo mysqladmin shutdown pro ukončení činnosti.
- 86 -
Standardní kódovou stránkou je ISO-8859-1 (Latin1), systém MySQL však plně podporuje větší množství znakových sad a další je dokonce možné vytvářet. Součástí standardní dodávky jsou i chybová hlášení přeložená do několika jazyků včetně češtiny. Použití klienta Klient serveru MySQL se spouští příkazem mysql ve všech operačních systémech. Pokud se přihlašujeme ke vzdálenému počítači, musíme jeho jméno nebo IP adresu definovat pomocí přepínače -h (např. mysql -h 195.113.136.240). Přihlášení se pod konkrétním uživatelským účtem je přes parametr -u a název účtu. Po přihlášení se zobrazí úvodní informace a výzva příkazové řádky klienta. Každý příkaz je nutné ukončit středníkem. Nápovědu lze spustit pomocí příkazu help; ukončení klienta provede příkaz quit (nebo kombinace kláves Ctrl-D). Kromě interaktivního módu je možné použít klienta k vykonání skriptů – do souboru (pojmenujeme např. mysql.in) zapíšeme požadované příkazy a klienta spustíme z příkazové řádky operačního systému pomocí příkazu mysql.exe < mysql.in. Chceme-li navíc, aby i výstup byl realizován ve formě textového souboru (např. mysql.out), zadáme mysql.exe < mysql.in > mysql.out. Výstup je poněkud stručnější než v interaktivním módu (odpadá např. rámování tabulek), přepnutí do módu úplného je pomocí přepínače -t.
- 87 -
Příklad použití klienta Na jednoduchém příkladu si ukážeme, jak lze pomocí klienta vytvořit tabulku v nové databázi a pracovat s ní. MySQL se snaží držet standardu SQL, některé příkazy se však odlišují, možnosti některých jsou doplněny. Pro přesnou syntaxi příkazu použijeme možností podrobné nápovědy. Nejprve si můžeme vyzkoušet, jak se zjišťují informace o prostředí. Výsledek bude vždy tabulka: mysql> SELECT VERSION(), CURRENT_DATE; +--------------+--------------+ | VERSION() | CURRENT_DATE | +--------------+--------------+ | 3.22.20a-log | 2003-01-24 | +--------------+--------------+ 1 row in set (0.01 sec)
Můžeme napsat několik příkazů do jednoho řádku: mysql> SELECT USER(); SELECT NOW(); +------------------+ | USER() | +------------------+ | pnovak@localhost | +------------------+ +---------------------+ | NOW() | +---------------------+ | 2003-01-24 16:28:52 | +---------------------+
Naopak je možné zapsat příkaz pomocí několika řádků, zrušit lze pomocí \c.
Novou databázi vytvoříme pomocí příkazu CREATE DATABASE mysql> CREATE DATABASE adresar; Query OK, 1 row affected (0.08 sec)
Seznam všech databází, které byly vytvořeny v prostředí MySQL serveru, vypíše příkaz SHOW DATABASES. mysql> SHOW DATABASES; +----------+ | Database | +----------+ | adresar | | mysql | | test | +----------+ 3 rows in set (0.06 sec)
Do existující databáze se přepneme pomocí USE. mysql> USE adresar; Database changed
Odstranění databáze z MySQL serveru provede příkaz DROP DATABASE. mysql> DROP DATABASE adresar; Query OK, 0 rows affected (0.04 sec)
Vytvoření tabulky Tabulku vytvoříme pomocí SQL příkazu CREATE TABLE. mysql> CREATE TABLE osoby -> ( -> id int AUTO_INCREMENT PRIMARY KEY, -> jmeno char(50), -> adresa char(100), -> poznamka text -> ); Query OK, 0 rows affected (0.22 sec)
Vlastnost AUTO_INCREMENT zabezpečí, že MySQL bude do tohoto pole automaticky zapisovat jednoznačnou hodnotu při každém vložení nového záznamu do tabulky. Výpis existujících tabulek v dané databázi provede příkaz SHOW TABLES. mysql> SHOW TABLES; +--------------------+ | Tables_in_adresar | +--------------------+ | osoby | +--------------------+ 1 row in set (0.01 sec)
Seznam polí tabulky a jejich vlastností zobrazí příkaz SHOW COLUMNS.
- 89 mysql> SHOW COLUMNS FROM osoby; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | jmeno | varchar(50) | YES | | NULL | | | adresa | varchar(100) | YES | | NULL | | | poznamka | text | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+ 4 rows in set (0.11 sec)
Pro modifikaci struktury tabulky se použije příkaz alter table. mysql> ALTER TABLE osoby -> MODIFY jmeno char(70); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
Odstranění tabulky z databáze zabezpečí příkaz DROP TABLE. mysql> DROP TABLE osoby; Query OK, 0 rows affected (0.00 sec)
Pro vložení nového záznamu do tabulky se použije příkaz INSERT. mysql> INSERT INTO osoby -> (jmeno, adresa) -> VALUES -> ('Petr Novák', 'Poláčkova 8') -> ; Query OK, 1 row affected (0.00 sec)
Záznamy uložené v tabulce vypíše příkaz SELECT. mysql> SELECT * FROM osoby; +----+-------------+-------------+----------+ | id | jmeno | adresa | poznamka | +----+-------------+-------------+----------+ | 1 | Petr Novák | Poláčkova 8 | NULL | +----+-------------+-------------+----------+ 1 row in set (0.10 sec)
Úpravu záznamů v tabulce provede příkaz UPDATE. mysql> UPDATE osoby -> SET -> poznamka = 'spolužák' -> WHERE id = 1 -> ; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
Odstranění záznamů z tabulky provede příkaz DELETE. mysql> DELETE FROM osoby -> WHERE id = 1; Query OK, 1 row affected (0.02 sec)
MySQL dovoluje definovat uživatelské proměnné pro hodnoty typu integer, real nebo string; jejich počáteční hodnota je vždy NULL, přiřazení probíhá pomocí příkazu SET nebo pomocí operátoru přiřazení: mysql> SET @sedm=7; mysql> SELECT @pocet:=COUNT(jmeno) FROM osoby; +----------------------+ | @pocet:=COUNT(jmeno) |
- 90 +----------------------+ | 4 | +----------------------+ 1 row in set (0.03 sec)
Podobná pravidla platí pro práci se systémovými proměnnými, jejich úplný výpis umožňuje příkaz SHOW VARIABLES.
- 91 -
6.
Přístup k databázi z programovacího prostředí 6.1.
OLE DB
Technologie, která v prostředí Windows vytváří rozhraní mezi programem a zdrojem dat, je rozšíření specifikace OLE na databáze, OLE DB. Zkratka OLE znamená Object Linking and Embedding a je to technologie pro integraci programů, jejíž pomocí lze sdílet informace mezi programy pomocí propojených a vložených objektů. Objekty zůstávají ve formátu původní aplikace. Specifikace OLE DB zajišťuje univerzální integraci dat v rámci celého prostředí, nezávisle na typu dat. Definuje rozhraní pro přístup a manipulaci se všemi typy dat – nejen s relačními databázemi, ale i např. se soubory elektronické pošty, obyčejnými soubory nebo tabulkami. K práci s databázemi je určena podmnožina OLE DB, rozhraní ODBC. Aplikace, která přistupuje k datům, se v architektuře OLE DB nazývá spotřebitel dat. Program, který přístup k datům umožňuje, se nazývá zprostředkovatel databáze (provider). Příkladem může být Microsoft OLE DB Provider for SQL Server, OLE DB Provider for Oracle, Microsoft Jet 4.0 OLE DB Provider nebo OLE DB Provider for ODBC Drivers.
6.1.1. ODBC Teoreticky by měl být každý produkt, který využívá jazyka SQL, kompatibilní se všemi ostatními aplikacemi podporujícími SQL. Ačkoliv pro SQL existují standardy, většina softwarových společností implementovala do jazyka různé variace nebo rozšíření, kterými se od standardu odlišuje. Za účasti předních světových společností bylo vyvinuto rozhraní CLI (Common Language Interface) pro všechny hlavní varianty SQL. Rozhraní pro programování aplikací ODBC (Open Database Connectivity), vyvinuté firmou Microsoft pomocí jazyka C pro splnění standardu CLI, je dnes velice používaná metoda pro přístup k databázím. Jakožto součást OLE DB zajišťuje maximální míru interoperability řešení – zpřístupňuje data z jakékoliv aplikace (podporující ODBC), bez závislosti na tom, jaký systém pro správu databází (DBMS) data zpracovává. Funkce rozhraní ODBC, které mohou být volány z jakékoliv aplikace, jsou obsaženy ve zvláštních modulech, tzv. databázových ovladačích. Databázový ovladač je tedy prostředek mezi aplikací a DBMS, který slouží k tomu, aby přeložil dotazy aplikace do příkazů, kterým DBMS rozumí. Zajišťuje použití jednotné syntaxe SQL a jedné funkční sady pro komunikaci s databází. Architektura ODBC se skládá z těchto součástí: • • •
aplikace posílá SQL příkazy do databáze a přebírá výsledky ovladač vytvoří spojení ke zdroji dat, převede příkaz SQL do dialektu zdroje dat, provede SQL příkazy a předá aplikaci výsledky, chyby a zprávy vlastní zdroj dat (SQL server, soubor databáze, soubor tabulkového kalkulátoru nebo textový soubor)
Informace o připojení zahrnují umístění serveru, název databáze, přihlašovací ID, heslo a různé možnosti ovladače ODBC, které popisují způsob připojení ke zdroji dat. Připojení k databázi přes ODBC je možné buď pomocí správce ovladačů ODBC, ve kterém se specifikuje cesta DSN (Data Source Name), nebo přímo pomocí připojovacího řetězce, který obsahuje informace o připojení. Jeho použitím se lze vyhnout definování zdroje DSN. Tento postup je užitečný zejména pro zjednodušení instalace aplikace.
- 92 Připojovací řetězec se skládá ze středníky oddělených parametrů, popisujících zprostředkovatele zdroje dat, umístění zdroje, případně další informace.
6.1.2. Konfigurace ovladače ODBC Několik ovladačů ODBC je obsaženo přímo v operačním systému, některé se instalují spolu s odpovídající databázovou aplikací. Například pro MySQL je možné stáhnout ovladače pro několik platforem zdarma z webu (včetně jejich zdrojového kódu). Připojení k databázi prostřednictvím systémového DSN (Data Source Name) Nastavení DSN je realizováno prostřednictvím Správce zdrojů dat ODBC (přístupný z menu Nástroje pro správu). Zdroje dat dělíme na strojové a souborové. Strojové zdroje dat ukládají informace o spojení do registru systému Windows na konkrétním počítači, můžeme je používat pouze na tom počítači, na kterém jsou definovány. Naproti tomu souborové zdroje dat (soubory DSN) ukládají informace o připojení do textového souboru a je možné je sdílet. Strojové zdroje dat se konfigurují buď pro aktuálního uživatele (uživatelské DSN), nebo pro všechny uživatele daného počítače (systémové DSN).
Obr. 56 Správce zdrojů dat
Při vytváření nového zdroje dat musíme specifikovat parametry závislé na zdroji dat (většinou jméno nebo IP adresa serveru, jméno databáze, u Accessu cesta k .mdb souboru, dále účet a heslo pro přihlášení a další). Příklad pro přihlášení se k danému zdroji dat si ukážeme na příkladu MySQL v ASP stránkách. V připojující se aplikaci se zdroj dat specifikuje pomocí řetězce "DSN=NázevDSN" pro strojový zdroj dat nebo "FileDSN=NázevSouboruDSN" pro souborový. Část skriptu zabezpečující připojení by vypadala tímto způsobem:
- 93 <% Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open "DSN=mysql_dsn" %>
Připojení pomocí připojovacího řetězce Připojení pomocí připojovacího řetězce obchází nutnost konfigurace DSN ve správci zdrojů dat, probíhá tedy přímo. Vše, co bylo nutné specifikovat při vytváření DSN, musí obsahovat připojovací řetězec. Odpovídající řádek ve vytvářeném skriptu by měl tvar následující: Conn.Open "Driver={mySQL}; Server=localhost; Port=3306; Option=0; Socket=; Stmt=; Database=address; Uid=admin; Pwd=password;"
Připojovací řetězec se skládá z několika parametrů oddělených středníkem. Podrobnosti se liší pro jednotlivé zdroje dat, nejdůležitější jsou parametry Driver a Server (případně Dbq pro soubory). Rovnítka v připojovacím řetězci neoddělujeme čárkou. Parametry pro MySQL a MS SQL Server jsou ukázány v tabulce. Parametry připojovacího řetězce pro MySQL Parametr
Popis
Driver Server Port Option Socket Stmt Database Uid Password
typ databázového ovladače jméno nebo IP adresa databázového serveru TCP/IP port pro komunikaci celočíselný kód pro upřesnění soket pro připojení platný SQL příkaz, který se po připojení provede jméno výchozí databáze uživatelské jméno heslo
Parametry připojovacího řetězce pro MS SQL Server Parametr
Popis
Driver Server Database Uid Pwd App Wsid
typ databázového ovladače jméno nebo IP adresa databázového serveru jméno výchozí databáze uživatelské jméno heslo nepovinné, jméno aplikace nepovinné, ID připojujícího se počítače
Příklady přímých ODBC připojení MySQL oConn.Open "Driver={mySQL};" & _ "Server=JmenoServeru;" & _ "Option=16834;" & _ "Database=JmenoDB"
6.1.3. ADO (ActiveX Data Objects) Nejnovější programovací rozhraní pro datové objekty ActiveX (ADO) je založeno na architektuře OLE DB a dále ji rozvíjí. K navázání kontaktu se zprostředkovatelem dat (nejčastěji ODBC) se stále využívá služeb poskytovaných OLE DB, zatímco ADO poskytuje metody k snadnému přístupu do databáze. V podstatě jde o objektovou knihovnu pro přístup k datům, která nabízí potřebné metody včetně možnosti využít potenciálu jazyka SQL. Nahrazuje původní rozhraní Data Access Objects (DAO – rozsáhlé rozhraní určené ale pouze pro Microsoft Jet v prostředí Visual Basic) a Remote Data Objects (RDO). Na rozdíl od nich může být použito i pro přístup k jiným zdrojům dat, než jsou relační databáze, včetně webových stránek a dalších dokumentů. Výhodou je, že přístup pomocí ADO je velmi snadný a je realizovatelný pomocí skriptovacích jazyků, například VBScript nebo JScript. Spolu s OLE DB (rozhraní na systémové úrovni) a ODBC je jednou z hlavních komponent specifikace UDA (Universal Data Access), která je navržena pro zajištění přístupu k datům nezávisle na jejich struktuře. ADO využívá tři základní typy objektů: • • •
Connection – otevírá spojení se zdrojem dat Command – definice příkazu, po provedení vrací výslednou množinu záznamů RecordSet – množina záznamů, která vznikla na základě původní tabulky nebo jako výsledek provedeného příkazu
Objekt Connection definuje spojení k datovému zdroji, Command slouží k definici příkazu a po jeho vykonání vrací výslednou množinu záznamů. Pro zpracování dat, která byla vrácena z databáze, je určen objekt RecordSet. Zbývající objekty zajišťují jeho přípravu, tzn. připojení a vykonání příkazu. 6.1.3.1.
Připojení ke zdroji dat pomocí objektu Connection
Pomocí objektu Connection lze navázat a spravovat připojení mezi aplikacemi a zdroji dat kompatibilními s OLE DB nebo ODBC. Vlastnosti a metody objektu Connection slouží k otevření nebo uzavření databázových připojení nebo k zadání dotazů pro aktualizaci informací. Pro připojení databáze se vytvoří instance objektu Connection. Pokud objekt nevytvoří programátor přímo, vytvoří ADO spojení automaticky při použití objektů Command nebo RecordSet. Postup při otevírání připojení je realizován tak, že vytvoříme objekt Connection a pomocí připojovacího řetězce nebo DSN připojení otevřeme. Zavření spojení nastává v okamžiku ukončení zpracování skriptu, ale jeho výslovným uzavřením pomocí metody Close snížíme požadavky na server a uvolníme připojení dalším uživatelům. Příklad ukazuje připojení prostřednictvím DSN: <% Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open "DSN=mysql_dsn" %>
Ve vzorovém skriptu ASP stránky navazující spojení přes připojovací řetězec uložíme pro přehlednost připojovací řetězec do proměnné; poslední příkaz uvolňuje paměť po objektu Connection: <% Set strConn = _ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Cesta\Zamestnanci.mdb"
- 96 Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open strConn Conn.Close Set Conn = Nothing %>
Aby se nemusela do ASP stránky zadávat celá cesta k databázi, je možné cestu vygenerovat na základě relativní cesty z kořenového adresáře webu: Server.MapPath("/cesta")
Pomocí vlastnosti ConnectionTimeout objektu Connection je možné nastavit časový limit, po který aplikace čeká, než přeruší pokus o připojení a odešle chybovou zprávu (standardní doba je 30 s): Conn.ConnectionTimeout = 20
Použití objektu Connection k provádění příkazů SQL Pomocí metody Execute objektu Connection můžete na zdroje dat aplikovat příkazy, nejčastěji dotazy v jazyce SQL: <% strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/cesta") & "\Zamestnanci.mdb" Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open strConn strSQL = "INSERT INTO Osoby (Jmeno, Prijmeni) VALUES ('Jana','Novotná')" Conn.Execute strSQL Conn.Close Set Conn = Nothing %>
Metoda Execute je kromě příkazu INSERT vhodná pro příkazy UPDATE nebo DELETE. Odpovídající řetězec může vypadat například takto: "UPDATE Osoby SET Jmeno = 'Lenka' WHERE Prijmeni = 'Novotná'"
nebo "DELETE FROM Osoby WHERE Prijmeni = 'Novotná' AND Jmeno = 'Lenka'"
Úplná syntax metody Execute je následující (libovolný počet parametrů zprava lze vynechat): Connection.Execute CommandText, RecordsAffected, Options
nebo Set RecordSet = Connection.Execute (CommandText, RecordsAffected, Options)
CommandText je řetězec obsahující vlastní příkaz, může však obsahovat např. jméno procedury, tabulky, URL a další hodnoty. Do proměnné RecordsAffected server vrací počet operací ovlivněných záznamů. Pomocí číselného parametru Options specifikujeme způsob interpretace argumentu CommandSet. Pro textovou definici příkazu, což zahrnuje i SQL dotazy, použijeme předdefinované hodnoty adCmdText. Pokud nechceme, aby metoda vytvářela sadu záznamů dat (RecordSet), přidáme hodnotu adExecuteNoRecords. Konstanty parametrů jsou definovány v knihovně typů součásti, takže pokud chceme konstanty používat, musíme knihovnu deklarovat buď přímo v ASP stránce nebo v souboru Global.asa, a to pomocí značky <METADATA>. Stránka bude vypadat takto:
- 97 <% strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/cesta") & "\Zamestnanci.mdb" Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open strConn strSQL = "INSERT INTO Osoby (Jmeno, Prijmeni) VALUES ('Jana','Novotná')" Conn.Execute strSQL,,adCmdText + adExecuteNoRecords Conn.Close Set Conn = Nothing %>
Seznam kolekcí, vlastností a metod objektu Connection: Vlastnost nebo metoda Errors (kolekce) ConnectionString ConnectionTimeout DefaultDatabase IsolationLevel Mode Provider Version BeginTrans CommitTrans RollbackTrans Close Execute Open 6.1.3.2.
Popis Ošetřování chyb Informace pro vytvoření spojení s datovým zdrojem Interval čekání na navázání spojení Specifikace výchozí databáze. Úroveň izolace při provádění transakcí Oprávnění pro aktualizaci spojení Specifikace zprostředkovatele OLE DB Verze ADO implementace Řízení transakcí v otevřeném připojení Uzavření spojení Provádí SQL příkaz; pokud je zdáno pouze jméno tabulky, provádí její výpis Vytvoření fyzického připojení Použití objektu RecordSet pro práci s výsledky
K tomu, abychom data mohli načíst, prohlížet je a provádět v nich změny, potřebujeme objekt RecordSet. Tento objekt si můžeme představit jako virtuální tabulku, která obsahuje záznamy vrácené po provedení dotazu. Zachovává pozici jednotlivých záznamů vrácených dotazem a umožňuje postupné prohlížení jednotlivých výsledků. Otevření objektu RecordSet je možné prostřednictvím metody Execute objektu Connection (nebo Command) nebo pomocí metody Open objektu RecordSet (libovolný počet parametrů zprava lze vynechat): RecordSet.Open Source, ActiveConnection, CursorType, LockType, Options
Source je text SQL příkazu, příp. objekt Command, jméno tabulky nebo dalšího objektu, ActiveConnection příslušný objekt Connection, dále následuje typ ukazatele záznamů, způsob zamykání záznamů při editaci a další volby. Načítání množiny záznamů Správným použitím funkcí objektů Connection a RecordSet je možné vyvíjet databázové aplikace a provádět téměř všechny úkoly týkající se práce s daty. Následující příklad provede výběr pomocí příkazu SELECT. Kromě vytvoření objektu Connection a
- 98 otevření připojení je nutné vytvořit instanci objektu RecordSet, ten otevřít pomocí metody Open a využít připojení pomocí objektu Connection. Vrácenou množinou záznamů lze procházet například pomocí metody MoveNext. <% strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ Server.MapPath("/cesta") & "\Zamestnanci.mdb" strSQL = "SELECT Jmeno, Prijmeni FROM Osoby WHERE Prijmeni = 'Novák'" Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open strConn Set rstOsoby = Server.CreateObject("ADODB.RecordSet") rstOsoby.Open strSQL, Conn Do Until rstOsoby.EOF Response.Write rstOsoby("Jmeno") Response.Write " " Response.Write rstOsoby("Prijmeni") Response.Write " " rstOsoby.MoveNext Loop rstOsoby.Close Set rstOsoby = Nothing Conn.Close Set Conn = Nothing %>
Standardně se tedy nejprve vytvoří objekt Connection pro připojení k databázi a potom objekt RecordSet načte výsledky z databáze pomocí tohoto připojení. Přes vlastnosti objektu Connection je možné nastavit další parametry připojení k databázi. Pokud není nutné další parametry specifikovat, můžeme připojení realizovat pomocí výchozího připojení objektů ADO. Jako parametr metody Open objektu RecordSet použijeme místo objektu Connection přímo připojovací řetězec. Objekt Connection není nutné explicitně vytvářet. <% strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ Server.MapPath("/cesta") & "\Zamestnanci.mdb" strSQL = "SELECT Jmeno, Prijmeni FROM Osoby WHERE Prijmeni = 'Novák'" Set rstOsoby = Server.CreateObject("ADODB.RecordSet") rstOsoby.Open strSQL, strConn Do Until rstOsoby.EOF Response.Write rstOsoby("Jmeno") Response.Write " " Response.Write rstOsoby("Prijmeni") Response.Write " " rstOsoby.MoveNext Loop rstOsoby.Close Set rstOsoby = Nothing %>
Při otevření množiny záznamů můžeme určit způsob, jakým se načítá a prochází množina záznamů. Pokud k příkazu pro její otevření přidáme parametr adOpenKeyset, povolíme klientské aplikaci procházení celou množinou záznamů, a to vpřed i vzad. Aplikace potom může využívat více vlastností, například pomocí vlastnosti RecordCount přesně určit počet záznamů v množině.
- 99 <% strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ Server.MapPath("/cesta") & "\Zamestnanci.mdb" strSQL = "SELECT Jmeno, Prijmeni FROM Osoby WHERE Prijmeni = 'Novák'" Set rstOsoby = Server.CreateObject("ADODB.RecordSet") rstOsoby.Open strSQL, strConn, adOpenKeyset, adLockOptimistic, adCmdText If rstOsoby.RecordCount >= 3 then Response.Write "Počet lidí: " & rstOsoby.RecordCount & " " Do Until rstOsoby.EOF Response.Write rstOsoby("Jmeno") Response.Write " " Response.Write rstOsoby("Prijmeni") Response.Write " " rstOsoby.MoveNext Loop Else Response.Write "Bohužel, mariáš se hrát nedá. Počet osob: " & _ rstOsoby.RecordCount & "." End If rstOsoby.Close Set rstOsoby = Nothing %>
Přehled základních vlastností a metod objektu RecordSet: Vlastnost nebo metoda MoveFirst MoveNext MovePrevious MoveLast Open Close BOF EOF PageCount PageSize RecordCount Provider LockType CursorType State
6.1.3.3.
Popis Pohyb po záznamech
Otevření objektu Uzavření objektu Pozice ukazatele před prvním záznamem Pozice ukazatele za posledním záznamem Počet stránek v objektu Počet záznamů tvořících stránku objektu Počet záznamů v objektu Specifikace zprostředkovatele OLE DB Režim zamykání záznamů Použitý typ ukazatele Stav objektu
Zvýšení výkonu dotazů pomocí objektu Command
Dalším objektem ADO je objekt Command. Ten může provést dotazy podobně jako objekty Connection nebo RecordSet. Navíc umožňuje připravit nebo sestavit dotaz ve zdroji dat a potom ho opakovaně zadávat s různými množinami hodnot. Tyto SQL dotazy se ještě dají částečně modifikovat těsně před spuštěním, a to prostřednictvím kolekce Parameters objektů typu Parameter. Tato kolekce je součástí objektu Command. Nový parametr se vytváří pomocí metody CreateParameter objektu Command a do kolekce Parameters ho zařadíme pomocí metody Append. Správná syntax příkazu CreateParameter je následující:
Objekt Command neposkytuje metodu Open k otevření připojení, je nutné předem vytvořit objekt Connection a poté Command připojit pomocí vlastnosti ActiveConnection. Uložení předkompilované verze dotazu specifikovaného ve vlastnosti CommandText zabezpečuje vlastnost Prepared. Následuje definování parametrů. Posledním krokem je několikanásobné vykonání dotazu s konkrétními parametry. <% strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ Server.MapPath("/cesta") & "\Zamestnanci.mdb" strSQL = "INSERT INTO Osoby (Prijmeni, Jmeno) VALUES (?, ?)" Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open strConn Set cmdOsoby = Server.CreateObject("ADODB.Command") Set cmdOsoby.ActiveConnection = Conn cmdOsoby.CommandText = strSQL cmdOsoby.Prepared = True cmdOsoby.Parameters.Append cmdOsoby.CreateParameter("pjm",adVarChar,,255) cmdOsoby.Parameters.Append cmdOsoby.CreateParameter("jmn",adVarChar,,255) cmdOsoby("pjm")="Hájek" cmdOsoby("jmn")="Josef" cmdOsoby.Execute,,adCmdText + adExecuteNoRecords cmdOsoby("pjm")="Neumann" cmdOsoby("jmn")="Jindřich" cmdOsoby.Execute,,adCmdText + adExecuteNoRecords cmdOsoby("pjm")="Havelka" cmdOsoby("jmn")="Petr" cmdOsoby.Execute,,adCmdText + adExecuteNoRecords Conn.Close Set Conn = Nothing %>
Další výhodou použití objektu Command s parametry je mimo jiné např. možnost jednoduše zadávat hodnoty obsahující apostrofy, které by v jiných případech kolidovaly se syntaxí jazyka SQL. Seznam kolekcí, vlastností a metod objektu Command: Vlastnost Parameters (kolekce) ActiveConnection CommandText CommandTimeout CommandType Prepared
Popis Parametry předané nebo převzaté od zprostředkovatele Přiřazení spojení objektu Command Příkaz (nejčastěji SQL příkaz) Čas v sekundách, po který zprostředkovatel čeká na zpracování příkazu Typ příkazu ve vlastnosti CommandText Specifikace připravené nebo kompilované verze příkazu před jeho provedením
- 101 Specifikace zprostředkovatele OLE DB Verze ADO implementace
Provider Version 6.1.3.4.
Správa databázových připojení
Při návrhu webových aplikací přistupujících vzdáleně do databází musíme počítat se silným zatížením. Zbytečně otevíraná připojení mohou přispět k přetěžování serveru. V každém případě je vhodné co nejrychleji explicitně uzavřít databázová připojení. Prostředí ASP sice umožňuje uložit připojení v objektu Application, a potom toto připojení používat na více stránkách, ale z hlediska zatížení serveru je toto řešení nevhodné. Lepším řešením je umístit připojovací řetězec do objektu Application (nebo Session) prostředí ASP a použít ho znovu na dalších stránkách WWW. Připojovací řetězec můžeme například zadat v proceduře události Application_OnStart souboru Global.asa: Application("ConnectionString") = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Cesta\Databaze.mdb"
Do každé další ASP stránky, která přistupuje do databáze, potom zadáme:
a vytvoříme instanci objektu Connection. K otevření připojení použijeme skript: Conn.Open Application("ConnectionString")
Zavření připojení na konci stránky provedeme standardně pomocí metody Close. 6.1.3.5.
Spolupráce formulářů a databáze
Vkládání nových údajů do databáze i editace údajů stávajících je možná prostřednictvím formulářů webové stránky. Na následujícím příkladě velice jednoduché .asp stránky si ukážeme, jak je možné kromě výpisu jednotlivých osob z tabulky Osoby databáze Zamestnanci vytvořit osobu novou, jejíž údaje se specifikují ve formuláři webové stránky. Pro jednoduchost se ve stránce s vypsaným zdrojovým kódem zadává pouze příjmení nové osoby, rozšíření o další pole je triviální záležitost. Pokud je jako primární klíč zvoleno automatické číslo (v našem případě pole ID), je jeho hodnota generována automaticky. Jinak bychom museli zabezpečit jedinečnost primárního klíče. Pro přehlednost kódu je výpis prováděn bez použití tabulek.
Obr. 57 Jednoduchý příklad stránky kombinující formuláře a databáze
- 102 Seznam osob <% dim strConn, rsOsoby, Conn, strSql strConn = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("/cesta") & "\zamestnanci.mdb;uid=sa;pwd=" set Conn = server.CreateObject("ADODB.Connection") set rsOsoby = server.CreateObject("ADODB.RecordSet") Conn.open strConn 'vykoná se, pokud stránka byla volána s odpovídajícím parametrem if request.form("prjmn") <> "" then strSql = "INSERT INTO Osoby (prijmeni) VALUES ('" & _ request.form("prjmn") & "')" Conn.execute strSql end if strSql = "SELECT id, jmeno, prijmeni FROM osoby ORDER BY prijmeni" rsOsoby.open strSql,Conn if rsOsoby.EOF then response.write "Databáze je prázdná." else rsOsoby.MoveFirst do until rsOsoby.EOF 'může odkazovat na další stránku, která by zobrazila všechny informace 'o dané osobě - příp. by bylo možné údaje o osobě editovat response.write "" & _ rsOsoby("id") & " " & rsOsoby("prijmeni") & " " rsOsoby.MoveNext loop end if 'formulář vyhodnocuje táž stránka (volání s parametrem) response.write"" rsOsoby.close set rsOsoby = Nothing Conn.close set Conn = Nothing %>
Vypisovaná stránka obsahuje odkazy na stránku detaily.asp, která provádí úplný výpis informací o dané osobě. Textová proměnná, která v ní specifikuje odpovídající SQL příkaz, může mít tuto podobu: strSql = "SELECT * FROM Osoby WHERE CStr(id)='" & _ request.QueryString("id") & "'"
- 103 -
7.
Literatura:
Sokolowsky, P. – Pokorný, J. – Peterka, J.: Distribuované databázové systémy. ACADEMIA, Praha 1992. Pokorný, J – Halaška, I.: Databázové systémy. Vydavatelství ČVUT , Praha 1998. Riordan, R. M.: Vytváříme relační databázové aplikace. Computer Press, Praha 2000. Viescas, J.: Mistrovství v Microsoft Access 2000. Computer Press, Praha 2000. Brož, M.: Mistrovství v Microsoft Excel 2000. Computer Press, Praha 2000. Kras, P.: Programování v MS Office (Visual Basic pro Excel a Access). Fragment, Havlíčkův Brod 2000 Kukal, J.: Databáze standardu SQL, vol. 1-17. Publikováno v časopise Chip, Praha 19981999. Nejdůležitější prameny publikované na internetu: Melichar, J.: Datové modelování. 2002. Silberschatz, Korth, Sudarshan: Database Systems Concepts.1997. Sesula: Úvod do databázových systémů. Janda, J.: Jemný úvod do SQL. 1997. ANSI/ISO/IEC International Standard (IS): Database Language SQL. 1999. Butler, J.: ASP Data Access. Nápověda aplikací a online zdroje: Microsoft Access Microsoft Excel Microsoft Visual Basic for Applications Microsoft SQL Server MSDN MySQL MyODBC
- 104 Obsah: 1.
Teorie databázových systémů ................................................................................ 1 1.1.
