Technika práce s databázovými soubory
Databázový
soubor
V
teorii i praxi existuje řada databázových systémů (síťové, hierarchické,
relační, apod., odlišující se především organizací svých souborů). V
době málo výkonných počítačů sehrály první dva systémy svou rozhodující
roli. Dnes se prosazují (zejména pro svou pružnost) relační databázové
systémy, které své pojmenování získaly podle relací z množinového počtu.
V matematice jsou relace definovány jako podmnožiny kartézského součinu, což
je abstraktní model záznamů databázových souborů. Jelikož oprávněnost
existence relačních databázových souborů již není nutno prokazovat,
budeme definovat relační databázový soubor ne matematicky, ale pragmaticky:
Reálný
svět je tvořen objekty (včetně objektů abstraktních, zobecněných), které
je možné popsat uvedením jejich vlastností (atributů). Například konkrétní
automobil lze popsat jeho státní poznávací značkou, typem, rokem výroby,
barvou, apod. Pomocí těchto atributů lze popsat nejen konkrétní automobil,
ale všechny automobily. Říkáme celý soubor automobilů (všechny
automobily) nebo obecněji celou třídu objektů. Jedna třída objektů tvoří
jeden soubor. Databázový soubor je tedy popis jedné třídy objektů reálného
světa.
Z
formálního hlediska je databázový soubor (DBF - DataBase File) v podstatě
tabulka (Table), zapsaná ve vnější paměti počítače. Jméno souboru (file
name) odpovídá názvu tabulky. Jednotlivé řádky tabulky (rows) jsou do
souboru ukládány jako záznamy (records). Paměťová místa záznamu, do kterých
je možné zapisovat hodnoty atributů tabulky se nazývají položky (fields) a
odpovídají sloupcům tabulky (columns). Soubory, záznamy a položky jsou
pojmy jazyka FoxPro. Tabulky, řádky a sloupce jsou pojmy jazyka SQL (dotazovací
jazyk, bude probírán).
Databázové
soubory a jejich položky mají svá jména. Jména musí začínat písmenem,
mohou obsahovat číslice a znaky podtržení. Písmena české abecedy se
zapisují bez diakritických znaků. Velikost písmen nerozhoduje. Rozhodujících
je prvních osm znaků jména souboru a deset znaků jména položky. Ostatní
znaky se ignorují. Vyznačení příslušnosti položky k souboru lze provést
tak, že před její jméno se zapíše jméno souboru a oddělí se tečkou.
Pak nemůže dojít k chybě při stejném pojmenování položek. Vzniká tím
jakási předpona jména položky, které se říká kvalifikátor nebo prefix.
Jména databázových souborů mohou být uváděna v programech s příponou
.DBF (DataBase File). V praxi se přípony souborů zapisují pouze ve výjimečných
případech (například při rušeni nebo přejmenování souborů).
Normování
databázových souborů
Zatím
se všechno jeví jednoduché a srozumitelné. Komplikace nastávají v projekční
praxi, kdy bez znalosti hodnot vlastností objektů (obsahu souboru = domén
atributů) je nutné navrhnout strukturu databázových souborů. V podstatě
jde o problém aby v jednom souboru nebylo popsáno více objektů než jeden.
Jinak dojde ke ztrátám informací, potížím při aktualizaci, neodůvodnitelné
redundanci (nadbytečnosti) obsahu souboru, a jiným nežádoucím jevům. Dobře
navrhnout strukturu databázových souborů vyžaduje značné zkušenosti. V
první fázi je možné se řídit pravidlem: když se vám jeví, že jde některé
atributy zapsat do číselníků, třídníků, katalogů (pozor, ne ceníků!),
pak vytvořte nový soubor pro tyto atributy. Proč ne ceníků? Z jednoduchého
důvodu. Ceny se mění a aktualizací ceníku by se mohly zničit informace o
skutečných nákladech.
V
obecné teorii databázových systémů existuje řada pravidel vedoucích k
odstranění nežádoucích redundancí. Odstraňování nežádoucích
redundancí se nazývá normování. Všechny se však opírají o znalost domén
atributů (jinými slovy o naplněné soubory). A tady nastává problém. Jen zřídka
kdy je znám obsah souborů v době jejich navrhování.
V
našem příkladě v evid_voz jsou popsány vozidla konkrétního autoparku.
Kdyby tento soubor obsahoval atributy: normovaná spotřeba, výrobce, nosnost,
ložná plocha apod., pak nejde o vlastnosti konkrétního vozidla (stejné
vlastnosti mohou mít i jiná vozidla), ale o vlastnosti jiné třídy objektů,
kterou lze nazvat typy vozidel => dá se vytvořit katalog vozidel podle typů.
Pracovní
oblasti databázových souborů
FoxPro
umožňuje pracovat s databázovými soubory (vytvořit soubor, naplnit,
doplnit, změnit strukturu či obsah souboru, vyhledávat a číst informace
zapsaní v souboru a pod.), současně ve 25 pracovních oblastech. Pracovní
oblast může být označována písmeny a - j pro prvních deset pracovních
oblastí nebo čísly 1 - 25 nebo přezdívkou (ALIAS), kterou můžeme dát
oblasti při jejím obsazování. Obsazení pracovní oblasti se provádí otevřením
souboru. V poslední době se výše uvedených označování používá stále
méně a oblasti se v programu označují jménem souboru (bez přípony),
který je v ní otevřen. Funguje to totiž tak, že když při otevření
souboru není zadána klauzule alias, pak se okopíruje do příslušného pole
místo alias název souboru.
Interaktivní
vytvoření databázového souboru
Vytvoř
se anglicky řekne create. Příkaz k vytvoření souboru bude mít obecný
tvar:
CREATE
<soubor>
Příkaz
zajistí aktivaci okna pro definování struktury. Okno má v záhlaví
zobrazenou cestu a jméno souboru. Umožňuje zapsat strukturu souboru do čtyř
sloupců. Do sloupce NAME se zapisuje jméno položky, do TYPE typ údaje, do
WIDTH (zobrazovaná) délka a do DEC počet desetinných míst (jen číselných
údajů.)
Zápis
jednotlivých údajů se ukončuje klávesou ENTER. Při zápisu TYPE stačí uvést
první písmeno typu údaje nebo zmáčknout ENTER a výběr provést ze
zobrazeného menu. Posuv kurzoru v okně lze zajistit pomocí kurzorových kláves
- šipek, nebo pomocí myši. Pohyb mezi prvky umístěnými v okně se zajišťuje
klávesou Tab (zpětný pohyb Tab+Shift. Zapsané údaje lze opravovat přepisováním.
Vložení řádku lze zajistit klávesami ^I (Insert = vlož). Zrušení klávesami
^D (Delete = zruš). Lze použít myši. Zápis struktury se ukončí klávesami
^W (Write = zapiš). Uvedené klávesy a jejich kombinace si zapamatujeme, lze
je použít ve stejných funkcích v řadě oken FoxPro.
Konkrétně,
zápis příkazu:
┌──────COMMAND───────
│ CREATE evid_voz
└────────────────────
Programové
vytvoření databázového souboru
Velmi
často bude nutné vytvořit databázový soubor přímo z programu (programová
struktura je součástí programu nebo je vypočtena). Databázový soubor se
vytváří příkazem:
CREATE
DBF <jméno
souboru>(<1.položka> <typ> [(<expN>)],
<2.položka>
[<typ>][(<expN>)], ... )
kde
typ je uváděn písmenem. Rozměr <expN> se neuvádí pro typ D (datový,
je vždy 8B), L (logický, je vždy 1B), M (typ memo je vždy 10B). U numerické
položky se do závorek zapisuje nejdříve celkový rozměr a pak počet
desetinných míst, oddělených čárkou. Pozor. Nezapomínat na správně
uvedený počet závorek. Tento příkaz je vhodné si zapamatovat. V literatuře
se často touto formou popisuje databázové soubory. Místo slova DBF je možné
použít rovnocenného slova TABLE. (Tento příkaz patří mezi příkazy
dotazovacího jazyka SQL.)
Například
vytvoření evidence vozidel se programuje:
┌───────────────────────────
│ CREATE DBF evid_voz ;
│ (OZNACENI C (12),;
│ TYP C (10),;
│ PORIZENI D, ;
│ KM N (10),)
│
│
└───────────────────────────
Vytvoření
databázového souboru kopírováním
Vytvoření
nového (prázdného) databázového souboru, se stejnou strukturou jako má
aktivní soubor lze programovat příkazem:
COPY
STRUCTURE TO <soubor>
[FIELDS <seznam položek>]
[[WITH]
CDX | PRODUCTION]
Zadáním
klauzule [WITH] CDX nebo PRODUCTION se k novému databázovému souboru vytvoří
indexový soubor typu CDX stejných klíčů jako má původní databázový
soubor. Klauzulí FIELDS lze zadat výběr položek.
Vytvoření
databázového souboru podle souboru struktur
Vytvoření
databázového souboru lze programovat též pomocí speciálního databázového
souboru, který sám má předepsanou strukturu (jde tedy o metastrukturu =
strukturu popisující strukturu):
Deklarace
každé popisované položky je v jednom záznamu, který má atributy,
pojmenované:
FIELD_NAME
- jméno položky
FIELD_TYPE
- typ položky
FIELD_LEN
- délka položky
FIELD_DEC
- počet desetinných míst pro numerické položky.
Jak
již bylo řečeno, takový soubor umožňuje vytvářet soubory podle okamžité
potřeby. Soubor struktury se nejčastěji vytváří z aktivního souboru příkazem:
COPY
TO <soubor> STRUCTURE
EXTENDED
který
uloží popis struktury aktuálního databázového souboru do zadaného databázového
souboru. Takto vytvořený databázový soubor se dá upravovat a použít k
vytvoření nového databázového souboru následujícím příkazem:
CREATE
[<soubor1>] FROM
[<soubor2>]
který
vytvoří prázdný databázový soubor podle popisu struktury uložené v
souboru. Soubor <soubor2> s uloženým popisem struktury se zpravidla vytváří
příkazem COPY STRUCTURE EXTENDED.
Zobrazení
struktury databázového souboru
Strukturu
databázového souboru si lze kdykoliv zobrazit nebo vytisknout. Musíme však
systému sdělit, který soubor chceme použít ( použít se anglicky řekne
use). Pak napíšeme příkaz: zobraz strukturu. (Anglicky: display structure).
Zadáme:
┌──────COMMAND──────
│ USE EVID_VOZ
│ DISP STRU
└───────────────────
Zobrazí
se výsledek:
┌─────────────────────────────────────────────────────────
│
│ Structure for database: C:\FOX\PR\EVID_VOZ.DBF
│ Number of data records: 0
│ Date of last update : 10.09.91
│ Field Field Name Type Width Dec
│ 1 OZNACENI Character 12
│ 2 TYP Character 10
│ 3 PORIZENI Date 8
│ 4 KM Numeric 10
│
│
│ ** Total ** 40
└─────────────────────────────────────────────────────────
Bude-li
zapotřebí strukturu vytisknout, nutno zapsat příkaz zapiš strukturu na tiskárně
(anglicky = list structure to printer).
┌──────COMMAND───────
│ USE EVID_VOZ
│ LIST STRU TO PRINT
└────────────────────
Poznámka:
Příkazy DISPLAY a LIST jsou podobné a zaměnitelné. Pro zobrazování používáme
raději příkaz DISPLAY. Při větším rozsahu, než je rozsah obrazovky, umožní
postupné čtení jednotlivých částí zobrazované informace. LIST pracuje
bez přerušení a hodí se pro tisk.
Obecné
definice uvedených příkazů mají tvar:
DISPLAY
STRUCTURE [IN <expN> |
<alias>]
[TO
PRINTER | TO FILE <soubor>]
LIST
STRUCTURE [IN <alias>]
[TO PRINTER | TO FILE <soubor>]
NOCONSOLE
Naplnění
a aktualizace souborů zkušebními daty
Pro
naplnění a aktualizaci databázových souborů budeme zpočátku používat příkaz:
┌──────COMMAND───────
│ USE EVID_VOZ
│ BROWSE
└────────────────────
Zobrazí
se okno - formulář pro zápis, prohlížení a aktualizaci databázového
souboru. (Slovo browse znamená prohlížet. Příkaz browse má velice složitou
strukturu a bohaté možnosti využití. Pro naši potřebu stačí však uvést
pouze název příkazu. Zápis záznamu si lze vynutit klávesami ^N, pohyb ve
formuláři se provádí pomocí šipek, k přechodu na další sloupec se použije
klávesy TAB, na předchozí sloupec - Shift a TAB. Práce v okně browse, se
ukončuje klávesami ^W. Záznam nelze zrušit, pouze označit pro zrušení klávesami
^T. Vlastní zrušení proběhne až po ukončení práce a po zápisu příkazu
PACK.)
Příklad
zápisu hodnot do souboru EVID_VOZ:
OZNACENI TYP PORIZENI KM
------------------------------------------------
BMR 25-36 │ Tatra 111 │ 01.11.60│ 42414 │
BMR 72-58 │ Praga V3S │ 21.03.61│ 51055 │
BMR 52-83 │ Tatra 111 │ 06.01.64│ 87544 │
Výpis
souboru na obrazovce a tiskárně
Výpis
souboru se provádí analogicky jako výpis struktury souboru. Musíme ovšem sdělit
systému, že si přejeme zobrazení všech záznamů (anglicky všechny se řekne
all).
┌──────COMMAND───────
│ USE EVID_VOZ
│ DISPLAY ALL
└────────────────────
Zobrazí
se obsah souboru ve tvaru:
┌────────────────────────────────────────────────────────────
│ RECORD# OZNACENI TYP PORIZENI KM
│ 1 BMR 25-36 Tatra 111 01.11.80 42414
│ 2 BMR 72-58 Praga V3S 21.03.81 51055
│ 3 BMR 52-83 Tatra 111 06.01.84 87544
└────────────────────────────────────────────────────────────
Pro
tisk obsahu souboru je vhodnější příkaz:
┌──────COMMAND───────
│ USE EVID_VOZ
│ LIST ALL TO PRINT
└────────────────────
(K
potlačení tisku čísel záznamů - record# - stačí napsat klauzuli OFF,
tedy: LIST ALL OFF TO PRINT).
Restrukturalizace
databázového souboru
Změna
struktury otevřeného databázového souboru se zajistí příkazem:
MODIFY
STRUCTURE
Příkaz
předpokládá, že příslušný databázový soubor byl předem otevřen příkazem
USE... Příkaz zobrazí strukturu databázového souboru ve tvaru popsaném v
kapitole 6.3.1. V dialogovém okně lze měnit už nadefinované položky (například
jejich typ a rozměr), přidávat nové položky a rušit staré. Po ukončení
aktualizace souboru následuje dotaz na provedení změny (make the structured
changes permanent). Po kladné odpovědi (yes) se provede automatická
aktualizace celého souboru. Při aktualizaci nedojde ke ztrátě informací uložených
v souboru, pokud nebyly přímo zadáním změn vyžádány (zkrácení rozměru,
zrušení položky). Změna typů položek proběhne ve smyslu kapitoly 6.2.
Indexové soubory typu CDX, které se automaticky otevřely příkazem USE, jsou
aktualizovány souběžně s aktualizací databázového souboru. O indexových
souborech bude pojednáno v následující kapitole.
Manipulace
s databázovými soubory
Manipulací
rozumíme:
-
přidání záznamu do souboru (insert),
-
výběr potřebných položek z různých souborů (select),
-
vyhledání záznamů odpovídajících zvoleným kritériím,
-
změnu některých položek vyhledaného záznamu,
-
vytváření nových položek matematickými, řetězcovými a logickými
operacemi,
-
seskupování (group) položek podle zvolených kritérii,
-
uspořádání (order) záznamů podle zvolených kritérii,
-
sjednocení (join, významové propojení) záznamů dvou a více nesourodých
souborů,
-
spojení (union) sourodých souborů
-
zápis obsahu databázových souborů do textových souborů nebo jeho tisk.
Uvedené
manipulace lze zajistit dvěma příkazy FoxPro. Jsou to příkazy univerzální,
vyskytující se v každém významném databázovém systému. Jsou to současně
nesložitější a tudíž nejtěžší příkazy FoxPro.
Vkládaní
informací do databázového souboru
Provádí
se příkazem:
INSERT
INTO <soubor dbf>
[(<položka1> [,<položka2> [,..]])]
VALUES
(<expr1> [, <expr2> [, ...]])
Příkaz
přidá větu na konec existujícího databázového souboru. V příkazu se zadává,
za frází VALUES, seznam hodnot přiřazovaných položkám vkládané věty.
Neuvede-li se před VALUES seznam jmen položek, musí být hodnoty v seznamu v
počtu a pořadí dle struktury záznamu. Když se uvede seznam položek, nemusí
být úplný a na pořadí nezáleží, hodnoty musí odpovídat uvedeným položkám.
Příkaz má též tvar:
INSERT
INTO <soubor dbf> FROM
ARRAY <pole> | FROM MEMVAR
Tento
tvar příkazu umožní naplnit hodnoty položek podle hodnot prvků
<pole> nebo při uvedení fráze MEMVAR naplnit položky hodnotami proměnných
shodných jmen. (Položka má stejné jméno jako paměťová proměnná.)
Nenaplněné položky zůstávají prázdné.
Příklad
naplnění souborů (vytvořených v kapitolách 6.3.1. a 6.5.):
┌─────────────────────────────────────────────────────────────────────────
│ INSERT INTO EVID_VOZ VALU ('BMC 48-45', 'Tatra 417', {12/1/84},
124554)
│ INSERT INTO TYPY_VOZ VALU ('Tatra 417', 4.5, 12.3)
│ INSERT INTO EVID_VOZ VALU ('BMT 52-62', 'PRAGA 441', {10/14/87},
120000)
│ INSERT INTO EVID_VOZ VALU ('BMS 52-78', 'TATRA 417', {11/24/90},
14555)
│ typ = 'Praga 441'
│ nosnost = 5.0
│ spotreba = 14.34
│ INSERT INTO typy_voz from memvar
└─────────────────────────────────────────────────────────────────────────
O
výsledku se přesvědčíme výpisem:
┌─────────────────────────────────────────────────────────────────────
│
│ OZNACENI
TYP PORIZENI
KM
│ BMC
48-45 Tatra 417
12/01/84 124554
│ BMS
52-78 Praga 441
10/14/87 120000
│ BMT
52-62 Tatra 417
11/24/90 14555
│
│ TYP NOSNOST SPOTREBA
│ Tatra
417 4.5
12.30
│ Praga
441 5.0
14.34
└─────────────────────────────────────────────────────────────────────
Zbylé
manipulace
Všechny
zbylé manipulace se soubory zajišťuje jediný příkaz:
SELECT
[ALL
| DISTINCT] [<alias>.]<zvolený prvek> [AS <sloupec>]
[,
[<alias>.]<zvolený prvek> [AS <sloupec>]...]
FROM
<soubor dbf> [<dočasné alias>]
[,<soubor
dbf>[<dočasné alias>]...]
[INTO
<určení>] | [TO FILE <soubor> [ADDITIVE] | TO PRINT]]
[NOCONSOLE]
[PLAIN] [NOWAIT]
[WHERE
<podmínka spojení> [AND <podmínka spojení> ...]
[AND
| OR <filtr výběru> [AND | OR <filtr výběru>...]]]
[GROUP
BY <určení skupiny> [, <určení skupiny> ...]]
[HAVING
<filtr výběru>]
[UNION
[ALL] <příkaz SELECT>]
[ORDER
BY <pořadí> [ASC | DESC][, <pořadí> [ASC | DESC]...]]
Příkaz
umožňuje výběr, uspořádání a výstup informací z jednoho nebo více
databázových souborů (pomocí zadaných klauzulí). Příkaz určuje co
(SELECT), odkud (FROM), kam (TO, INTO), s jakým omezením (WHERE), za jaké
seskupení (GROUP BY), s čím spojeně (UNION) a v jakém uspořádání (ORDER
BY) se má vytvořit a odeslat. Tento příkaz je zřejmě nejsložitějším a
na pochopení nejtěžším příkazem FoxPro. Kdo ho však pochopí, pochopí
princip práce relačních databází. (Příkaz se vyskytuje v každém seriózním
relačním databázovém systému.)
Komu
by příkaz a jeho následující popis připadal nepřekonatelný, ten ať přejde
ihned k příkladům a s příkazem ať se seznamuje "po kapičkách".
Pozdější návrat k definici je nejen možný, ale i žádoucí. Pamatujeme
si: Příkaz se dá zkoušet, naučit a používat po částech. Zpřeházení
klauzulí není na závadu, pokud je zachována logika příkazu.
SELECT
[ALL | DISTINCT]
Fráze
ALL (implicitní) znamená, že se berou v dotaz všechny řádky výstupní
tabulky. DISTINCT odstraní duplicity řádků. Frázi DISTINCT je povoleno v příkazu
použít pouze jednou.
Povinně
se uvádí seznam prvků (minimálně jeden). Všechny položky lze zkráceně
vyjádřit zápisem hvězdičky. Obecně má seznam tvar:
[<alias>.]<zvolený
prvek> [AS <sloupec>] [, [<alias>.]<zvolený prvek> [AS
<sloupec>]...]
<zvolený
prvek> je:
a)jméno
položky databázového souboru
b)konstanta
c)výraz
(může obsahovat uživatelsky definované funkce (UDF).
Dále
může výraz obsahovat i speciální SQL funkce AVG(), COUNT(), MIN(), MAX() a
SUM(), jejichž parametrem je položka nebo výraz obsahující položku.
COUNT
(<výraz >) - četnost výskytu
SUM
(<výrazN>) - suma hodnot
AVG
(<výrazN>) - aritmetický průměr
MIN
(<výrazN>) - nejmenší hodnota
MAX
(<výrazN>) = největší hodnota
<alias>
uvádí
se při nejednoznačnosti jmen položek. Ve frázi AS můžeme zadat námi
zvolené jméno pro záhlaví sloupce ve výstupní sestavě.
FROM
...
uvádí
seznam databázových souborů <soubor dbf> potřebných pro tvorbu výstupu.
Tyto soubory nemusí být předem otevřené, nepovinným parametrem <dočasné
alias> jim lze dočasně přiřadit jméno alias, které je pak nutno použít
v příkazu SELECT vždy, když se na tento databázový soubor odvoláváme.
INTO
<určení>
určuje
směrování výstupu, nikdy nedojde k současnému zobrazení výstupu (případné
uvedení TO se nebere v úvahu). Neuvedeme-li INTO, je výstupním zařízením
obrazovka (viz ale i frázi TO). Jako <určení> můžeme uvést jednu z následujících
frází:
ARRAY
<pole>
výstup
se ukládá do uvedeného pole
CURSOR
<soubor dbf>
výstup
se uloží do dočasně vytvořeného databázového souboru zadaného jména. Případně
již existující databázový soubor téhož jména uzavře a přepíše. Po
ukončení příkazu SELECT zůstane dočasný soubor otevřen pro čtení, jeho
uzavření jej i zruší.
DBF
<soubor dbf> | TABLE <soubor dbf>
výstup
se uloží do databázového souboru, který je tímto přepsán nebo se založí.
TO
...
umožňuje
uložit výstup do textového souboru <soubor> (s frází ADDITIVE doplnit
na konec souboru). Je-li užita fráze PRINTER, výstup je směrován na tiskárnu.
Neuvedeme-li TO ani INTO, výstupním zařízením je obrazovka.
NOCONSOLE
potlačí
výstup na obrazovku.
PLAIN
potlačí
výpis záhlaví sloupců, nemá však účinek při uvedení INTO.
NOWAIT
Výpis
na obrazovce se nezastaví po jejím zaplnění.
WHERE
...
Konstrukci
s <podmínka spojení> je vhodné použít při výběru z více než
jednoho databázového souboru. Implicitně by se každá věta z prvního databázového
souboru spojovala s každou větou z druhého databázového souboru.
<podmínka
spojení>
určuje
položky, které zprostředkují vazbu databázových souborů. Má formát
<položka1><operátor><položka2>, kde položky jsou z různých
souborů a <operátor> může být =, <>, !=, #, ==, >, >=,
<, <=. <podmínka spojení> může být použita násobně prostřednictvím
operátoru AND.
<filtr
výběru>
určuje
podmínky, jaké musí splňovat věta, aby mohla být uvažována pro výstup,
může být použit násobně prostřednictvím operátorů AND a OR. <filtr
výběru> může mít následující tvary:
a)
<položka1> <operátor> <položka2>
evid_voz.typ
= typy_voz.typ
b)
<položka> <operátor> <výraz>
evid_voz.spotreba
>= 25
c)
<položka> <operátor> ALL (<jiný příkaz SELECT
(subquery)>) zde musí <položka> splňovat podmínky pro všechny
hodnoty přijatelné pro subquery.
spotřeba
> ALL (select spotreba from typy_voz where typ='Tatra 417°)
d)
<položka> <operátor> ANY |
SOME
(<jiný příkaz SELECT (subquery)>) zde musí <položka> splňovat
podmínky pro alespoň jednu z hodnot přijatelných pro subquery.
e)
<položka> [NOT] BETWEEN <dolní_mez> AND <horní_mez>
spotreba
between 10 and 50
f)
[NOT] EXISTS (<jiný příkaz SELECT (subquery)>)
podmínka
EXISTS není splněna pouze tehdy, jestliže subquery nic nevybralo.
exist
(select * from typy_voz where spotreba < 20)
g)
<položka> [NOT] IN <seznam_hodnot>
zde
musí být položka v zadaném seznamu
typ
in ("Tatra 417","typ3","typ6","typ14")
h)
<položka> [NOT] IN (<jiný příkaz SELECT (subquery)>)
zde
musí mít <položka> některou z hodnot vybraných v subquery
evid_voz.typ
IN (select typ from typ_voz where spotreba < 20)
i)
<položka> [NOT] LIKE <výrazC>
lze
použít "divoké znaky" % (ve významu *) a podtržítko (ve významu
?).
typ
like "typ"
GROUP
BY
sdružuje
řádky do skupin podle hodnot v jednom nebo několika sloupcích. <určení
skupiny> může být číselný výraz určující sloupec ve výstupu nebo
jméno položky nebo speciální funkce SQL s položkou jako parametrem.
HAVING
dovoluje
seskupovat řádky do skupin pokud je splněna podmínka <filtr výběru>.
UNION
kombinuje
výsledky stávajícího příkazu SELECT s dalším příkazem SELECT a potlačí
výstup duplicitních řádek. Uvedení fráze ALL duplicity umožňuje.
ORDER
BY
zadává
setřídění výstupu vzestupné (ASC - implicitní) nebo sestupné (DESC).
<pořadí> určuje, podle čeho třídit - je to buď číselný výraz určující
sloupec ve výstupu nebo jméno <zvolený prvek>, pokud je toto prosté jméno
položky.
Indexování
Záznamy
souboru jsou ukládány a zobrazovány v pořadí jejich pořízení. Pro
aktualizaci dat i pro jejich výpisy je z důvodů lepší přehlednosti vhodné
data uspořádat podle položek, které zaujímají v souboru klíčová
postavení. V našem příkladě podle označení vozidla. Skutečné třídění
souboru by při velkých počtech záznamů bylo velice zdlouhavé. Místo toho
se používá indexových souborů.
INDEX
ON <klíčová položka>
TAG <jmeno> [DESCENDING]
[ADDITIVE]
[FOR <logický výraz>]
Příkaz
vytvoří (nebo doplní) kompaktní indexový soubor stejného jména jako aktuální
soubor, s příponou .CDX. Soubor může obsahovat více indexů vytvořených tímto
příkazem (proto klauzule ADDITIVE - přidat). Je povoleno postupně přidávat
a měnit, podle potřeby, jednotlivé indexy. (Klíčová položka může být
nahrazena libovolným číselným nebo znakovým výrazem.) Jméno klauzule TAG
(jmenovka) se volí obvykle shodné se jménem klíčové položky. Slouží k
určení řídícího indexu (každý z indexů obsažených v indexovém
souboru může být řídícím). Řídící index určuje logické uspořádání
souboru. Zbylé indexy souboru se pouze automaticky aktualizují v závislosti
na aktualizaci databázového souboru. Logické uspořádání je vzestupné
(ascending). V případě potřeby sestupného uspořádání nutno v příkazu
zadat klauzuli DESCENDING.
Indexové
soubory umožňují provádět výběry záznamů. Výběr se předepisuje
klauzulí FOR (pro), za kterou následuje logický výraz. Soubor se pak navenek
jeví jako by obsahoval pouze záznamy odpovídající tomuto výrazu. (Jde o časově
nenáročnou operaci).
Příklad
vytvoření indexů:
┌─────────────────COMMAND───────────────────
│ USE evid_voz
│ INDEX ON oznaceni TAG oznaceni
│ INDEX ON porizeni TAG porizeni desc addi
└───────────────────────────────────────────
Od
tohoto okamžiku bude mít soubor evid_voz automaticky aktualizované oba
indexy. I když bude otevírán bez požadavku na uspořádaný výstup. Požadavek
na uspořádaný výstup (order) se zadává v příkazu USE zadáním jmenovky
požadovaného indexu (tag).
Například:
┌─────────────COMMAND──────────────
│ USE EVID_VOZ ORDER TAG oznaceni
│ LIST ALL TO PRINT
└──────────────────────────────────
Nástroje
vytváření a doplňování indexových souborů
K
vytváření složitých indexových výrazů a provádění změn v indexových
souborech slouží speciální dialogové okno, které lze otevřít z hlavní
nabídky volbami analogickými k vytvoření databázového souboru, popsanými
v kapitole 6.3.5 (File, New). V dialogu New se zvolí z výběrového pole rádiový
knoflík (o) Index. Dialog indexových souborů má tvar, který odpovídá výše
vedené struktuře příkazu.
Jednoduchá
aktualizace databázových souborů
S
doposud získanými znalostmi jsme schopní sestavit program pro pořízení a
aktualizaci libovolného databázového souboru (podle vzoru pro program
aktualizace evidence vozidel). Program (command) bude mít pouze čtyři příkazy
a nazveme ho jménem souboru, který bude aktualizovat. V našem případě
EVID_VOZ.
Program
pořídíme příkazem:
┌───────────COMMAND─────────────
│ MODIFY COMMAND EVID_VOZ
└───────────────────────────────
Vytvoří
se okno nadepsané EVID_VOZ.PRG, do kterého zapíšeme příkazy podle
obrázku.
┌────────────────EVID_VOZ.PRG──────────────────
│ SET DATE TO GERMAN
│ USE evid_voz ORDER TAG OZNACENI
│ BROWSE PARTITION 12+2 REDIT
│ PACK
└──────────────────────────────────────────────
V
podstatě jsme využili znalostí z předchozích kapitol, zejména kapitoly
6.3.3. Příkaz browse jsme ale rozšířili o klauzuli, která rozděluje okno
browse na dvě části (partition) tak, že dělící čára je za první (klíčovou)
položkou. Dělicí čára okna se počítá od pravého okraje okna v počtu
znaků. Rozměr položky OZNACENI je 12. Musíme ho zvětšit o jeden znak, připadající
na dělicí čáru (mezi položkovou, nejde o dělicí čáru částí oken).
Klauzule REDIT zajistí, že pravá strana (right) bude mít zobrazeny všechny
položky záznamu. Levá strana umožní snadné a rychlé vyhledávání záznamů.
Program se aktivuje příkazem:
┌────COMMAND────
│ DO EVID_VOZ
└───────────────
Zopakujeme
si:
a)
Nastavení data na německý tvar se provede příkazem SET DATE TO GERMAN.
b)
Příkaz PACK zajistí zrušení záznamů označených klávesami ^T.
c)
Index zajistí správné zatříd. nového zázn. až v okamžiku, kdy se nám zázn.
ztratí z dohledu.
d)
Zápis nového záznamu se provádí klávesami ^N.
Aktualizovat
lze v obou částech okna. Přepínání mezi okny se provádí klávesami ^H.
(Pohodlnější je přepínání myší.)
BROWSE
PARTITION fsize(field(1))+1 + fsize(field(2))+2
kde
field() je funkce, která vrací jméno položky a fsize() je funkce, která
vrací rozměr položky. Tyto funkce nejsou ve skriptech probírány, ale jejich
popis najdete v nápovědi systému (klávesa F1) nebo ve vhodném manuálu.
Cvičení
Výpis
všech položek
Výpis
všech položek (symbolicky) vyjádřeno hvězdičkou místo pracného vyjmenování
souborů (FROM evid_voz a typy_voz) do textového souboru, který lze doplňovat
a tisknou pomocí textového editoru T602. Klauzule additive přikazuje přidat
další hodnoty k předchozímu obsahu souboru):
┌──────────────────────────────────────────────────
│ sele * from evid_voz to file pokus5
│ sele * from typy_voz to file pokus5 additive
└──────────────────────────────────────────────────
Sjednocení
Sjednocení
(join) dvou a více
souborů se uvádí pouze výčtem souborů vstupujících do operace:
┌──────────────────────────────────────────────
│ select * ;
│ from evid_voz , typy_voz ;
│ to file pokus
└──────────────────────────────────────────────
Má-li
první soubor M záznamů a druhý N záznamů, pak výsledkem bude soubor o M*N
záznamech. Kombinuje se každý záznam s každým záznamem jiných souborů.
Všimneme si snahy systému o zachování jednoznačných názvů položek =
sloupců. Systém přidává písmena (dočasné označení pracovních oblastí)
oddělena _.
Samostatné
použití příkazu v tomto tvaru se nedoporučuje (zdánlivě je nelogický a může
vyprodukovat nesmírně mnoho nesmyslných řádků. Jiná je ovšem jeho úloha
při použití klauzule WHERE.
┌───────────────────────────────────────────────────────────────────────
│ OZNACENI TYP_A PORIZENI KM TYP_B NOSNOST SPOTREBA
│ BMC 48-45 Tatra 417 12/01/84 124554 Tatra 417 4.5 12.30
│ BMC 48-45 Tatra 417 12/01/84 124554 Praga 441 5.0 14.34
│ BMS 52-78 Praga 441 10/14/87 120000 Tatra 417 4.5 12.30
│ BMS 52-78 Praga 441 10/14/87 120000 Praga 441 5.0 14.34
│ BMT 52-62 Tatra 417 11/24/90 14555 Tatra 417 4.5 12.30
│ BMT 52-62 Tatra 417 11/24/90 14555 Praga 441 5.0 14.34
└───────────────────────────────────────────────────────────────────────
Výběr
záznamů
Restrikce
je zajištěna podmínkou spojení, porovnávající obsah klíčových položek
(v našem příkladě položky TYP, vyskytující se u obou souborů). Tím jsme
získali (normovanou) spotřebu jednotlivých vozidel. Z didaktických důvodů
je ponecháno zobrazení položky typ z obou souborů. Browse není součástí
příkazu select. Demonstruje ověření výsledného souboru.
┌───────────────────────────────────────
│ select * ;
│ from evid_voz a, typy_voz b ;
│ into DBF pokus2 ;
│ where a.typ=b.typ
│ browse
└───────────────────────────────────────
OZNACENI │ TYP_A
│PORIZENI│ KM
│ TYP_B │NOSNOST│SPOTREBA│
----------------------------------------------------------------------------------
BMC 48-45│Tatra
417│12/01/84│124554│Tatra 417│ 4.5│ 12.30
│
BMS 52-78│Praga
441│10/14/87│120000│Praga 441│ 5.0│ 14.34
│
BMT 52-62│Tatra 417│11/24/90│
14555│Tatra 417│ 4.5│ 12.30
│
Výběr
záznamů v uspořádaném tvaru
Použito
klauzule ORDERED A alias. Výstup do textového souboru
┌──────────────────────────────────────────────
│ select oznaceni, a.typ, spotreba ;
│ from evid_voz a, typy_voz b ;
│ where a.typ = b.typ ;
│ to file pokus3 ;
│ order by oznaceni
└──────────────────────────────────────────────
┌─────────────────────────────────────────────
│ OZNACENI
TYP
SPOTREBA
│ BMC
48-45 Tatra 417 12.30
│ BMT
52-62 Tatra 417 12.30
│ BMS
52-78 Praga 441 14.34
│
└─────────────────────────────────────────────
Výpočet
položek
V
příkladu se počítají součty plánovaní spotřeby, podle typů, lze získat
seskupováním (group). Není-li uveden název sloupce za spojkou AS,
zobrazí se začátek výrazu (sumace). Příklad na zjištění spotřeby podle
druhů vozidel.
┌──────────────────────────────────────────────
│ select a.typ, sum(b.spotreba) AS "spotreba";
│ from evid_voz a, typy_voz b ;
│ to file pokus4 where a.typ=b.typ ;
│ group by a.typ ;
│ order by a.typ
└──────────────────────────────────────────────
┌──────────────────────────────────────────────
│ TYP SPOTREBA
│ Tatra 417 24.60
│ Praga 441 14.34
└──────────────────────────────────────────────
Četnosti
záznamů
Potřebujeme
zjistit počet vozidel autoparku podle typů. Jelikož označení jsou jedinečná,
stačí spočítat jejich výskyt. Položky vzniklé výpočtem je vhodné výstižně
pojmenovat.
┌──────────────────────────────────────────────
│ sele typ,count(oznaceni) as pocet ;
│ from evid_voz ;
│ order by typ ;
│ group by typ ;
│ to pokus.txt
└──────────────────────────────────────────────
┌──────────────────────────────────────────────
│ TYP POCET
│ Praga V3S 6
│ Skoda 120 6
│ Skoda F 1
│ Skoda MB 4
│ Skoda R 1
│ Tatra 111 5
│ Tatra 411 2
│ Tatra 417 4
└──────────────────────────────────────────────