Manipulace se soubory

Manipulace se soubory

Základní práce s databázovým souborem

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 klausulí). 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í klausulí není na závadu, pokud je zachována logika příkazu.

SELECT [ALL | DISTINCT]. Fráze ALL (implicitní) znamená, že se berou v potaz 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.

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.

CREATE QUERY

Příkaz select se dá velice efektivně programovat pomocí speciálního dialogu, který se zahajuje příkazem:

CREATE QUERY [<soubor> | ?]

Veškeré zadané informace potřebné pro konstrukci dotazu se uloží do souboru s příponou QPR. Ten lze pak provést příkazem DO.

Místo zápisu příkazu se lze do dialogového okna dostat z hlavního menu volbami FILE, NEW, QUERRY. Jméno se zadává až při ukončení práce. Práce s oknem by po zvládnutí struktury příkazu select neměla činit potíže. V každém okamžiku konstrukce příkazu select lze ověřit postup tlačítkem «Do Querry». (Pozor, při dvou a více vstupních souborech, pokud nebyly zadány podmínky sjednocení nebo výběru, může dojít k přeplnění disku!) Generované příkazy lze zobrazit tlačítkem « See SQL » .(Příkaz select je příkazem dotazovacího jazyka SQL). Všimneme si, že v hlavním menu přibyla další nabídka: RQBE. Nenabízí však nic co by nenabídl dialog, kromě upozornění pro uživatel pracující bez myši, že aktivaci <See SQL> lze dosáhnout klávesami ^S a «Do Querry» klávesami ^Q.

Databases

Otevřené databázové soubory jsou zobrazeny v seznamu "Database". Přidávat lze tlačítkem < Add >, rušit tlačítkem <Clear>. Soubory seznamu se uplatní v klausuli FROM ... příkazu select.

Output Fields

Výstupní položky jsou v seznamu nadepsaném "Output Fields". Počáteční stav je vyjádřen všemi položkami všech souborů. Jinak lze měnit spínačem Select Fields.

[x] Select Fields...(výběr položek):

a) sepněte spínač Select Field, tím vyvoláte dialog výběr položek,

b) sepněte spínač Remove All, tím se vymaže okno Select Output,

c) postupně vyberte položky: označte položku a pak ji přesuňte spínačem Move,

d) zvolte OK.

[x] Order by...(zadání údajů pro tříděni):

a) sepněte spínač Order by...zobrazí se dialog třídění,

b) označte položku, podle které se má provést uspořádání,

c) označte způsob setřídění (Ascending - vzestupně, Descending - sestupně),

d) sepněte spínač Move,

e) opakujte dle potřeby bod b) až d), pak zvolte OK.

[x] Group by...(seskupování položek):

a) sepněte spínač Group by...zobrazí se dialog seskupování,

b) označte položku, podle které se má provést seskupení,

c) sepněte spínač Move,

d) opakujte dle potřeby bod b) a c), pak zvolte OK.

[x] Having by...(podmínky seskupení položek):

a) sepněte spínač Having... zobrazí se okno s dialogem pro vytvoření logického výrazu,

b) roztáhnete překrývané okno položek (field) a zvolte položku nebo potřebnou funkci a pak položku nebo rovnou zapište požadovaný výraz,

c) relační znak v případě potřeby změňte na NOT,

d) z překrývaného okna relací vyberte požadovaný operátor,

e) do okna Example zapište výraz výběru (po operátoru in nebo between se podmínky zapisují oddělené čárkou a znakové konstanty se nemusí zapisovat do uvozovek),

f) u řetězcových položek, obsahujících proměnnou velikost písmen volte Up=Lo (bez ohledu na velikost písma položek, ne zadaných konstant),

g) u složitých logických výrazů si zápis spojky OR vynutíte stejnojmeným tlačítkem, jinak se dílčí výrazy spojí spojkou AND,

h) po zadání zvolte OK.

Output To

Je překrývané okno, které nabízí možnosti výstupu:

- Browse = do nám známé tabulky (standard),

- Report/Label = tisk sestavy, nezapomenout na "Options"

- Table/DBF = vytvoření nového databázového souboru,

nezapomeňte zadat jeho jméno !

- Graph = zobrazení a tisk výsledků ve tvaru dvoj - nebo trojrozměrného grafu,

musí být instalován FoxGraph.

Options

Je rozsáhlý dialog, velice důležitý po volbě Report. (Pro tento požadavek jsou v dalším popisu nastaveny doporučené volby) Má dvě okna.

a) Okno volby formátů (Formatting Option) začínající výběrovým polem:

( ) Screen Display - zobrazení výsledků (standard)

(o) Report - tisková sestava

( ) Label - tisk jmenovek (vizitek)

dále následuje řídící pole:

[x] Report/Label Form Name - jméno tiskového souboru

[x] Quick Report - rychlé pořízení tvaru sestavy

[x] Preview Report/Label - možnost kontroly sestavy před vlastním tiskem

[ ] Show Sumary Info Only - ve výstupní sestavě budou pouze součty a mezisoučty

(potlačeny detaily)

[ ] Eject Page Before Report - před tiskem odstránkovat

[x] Report Heading - tisk hlavičky sestavy

[ ] Suppress Culomn Heading - potlačení záhlaví na dalších stranách

(o) Console On ( ) Console Off - současné zobrazení

[x] Pause Between Screens - při výstupu velkých souborů na obrazovku je

pozastaveno automatické rolování, nová obrazovka po zmáčknutí libovolné klávesy.

b) Okno směrování výstupů (Output Destination)

[x] To printer - na tiskárnu

[x] To File - do souboru, nutno zadat jméno

[x] Owerwrite File - přepsat (starý) soubor

[x] Use Printer Setup - použít nastavení tiskového souboru

Postup při stanovení podmínek spojení a výběru záznamů:

Podmínky spojení a výběru záznamů se zadávají ve spodní polovině dialogu RQBE. Způsob zadávání je analogický zadávání logických podmínek Having...

Podmínky spojení:

a) z překrývaného okna Field Name zvolte položku prvního souboru (zkontrolujte, zda relace je nastavena na Like nebo na jinou požadovanou relaci),

b) z překrývaného okna Example zvolte odpovídající položku druhého souboru.

Podmínky výběru:

a) do okna Field Name zapište položku nebo výraz,

b) relační znak v případě potřeby změňte na NOT,

c) z překrývaného okna relací vyberte požadovaný operátor,

d) do okna Example zapište výraz výběru (po operátoru in nebo between se podmínky zapisují oddělené čárkou a znakové konstanty se nemusí zapisovat do uvozovek),

e) u řetězových výrazů volte Up=Lo (volba bez ohledu na velikost písma se týká obsahu položek a ne zadaných konstant),

f) u složitých logických výrazů si zápis spojky OR vynutíte stejnojmeným tlačítkem, jinak se dílčí výrazy spojí spojkou AND.

< See SQL >

Můžete kontrolovat sestavený příkaz, ale nelze ho modifikovat ani kopírovat. Lze ho přenést do programu generováním. Proto na závěr nezapomeňte dat querry souboru jméno.

« Do Querry »

Výkonné tlačítko pro (ověřovací) provedení příkazu select.

Cvičení

1. Výpis všech položek (symbolicky vyjádřeno hvězdičkou místo pracného vyjmenování) souborů EVID_VOZ a TYPY_VOZ do textového souboru, který lze doplňovat a tisknou pomocí textového editoru T602 (klausule 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

└──────────────────────────────────────────────────

(Výpis souborů, uvedený v kapitole 6.9.1., byl proveden právě pomocí těchto dvou příkazů přímo do textu skript).

2. Spojení (join) dvou a více souborů

┌─────────────────────────────────────

│   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ý s každým). Všimneme si snahy systému o zachování jednoznačných názvů položek = sloupců. Systém přidává písmena oddělena _. Obecné 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í klausule 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

3. Výběr záznamů (restrikce) je zajištěn 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

4. Totéž v uspořádaném tvaru s použitím alias 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

5. 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

6. 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