Návrh dotazu

Režim návrhu dotazu umožňuje vytvořit a upravit databázový dotaz.

Většina databází používá dotazy k filtrování či řazení tabulek a následnému zobrazení záznamů. Pohledy nabízejí stejné funkce jako dotazy, ale na straně serveru. Pokud databázový server pohledy podporuje, je možné je použít k filtrování záznamů na serveru a zrychlení zobrazení.

Zvolte příkaz Vytvořit pohled na kartě Tabulky v databázovém dokumentu. Zobrazí se okno Návrh pohledu, které je podobné zde popsanému oknu Návrh dotazu.

Rozvržení okna Návrh dotazu se ukládá s vytvořeným dotazem, ale nelze je uložit s vytvořeným pohledem.

Režim návrhu

Chcete-li vytvořit dotaz, klepněte v databázovém dokumentu na ikonu Dotazy a poté klepněte na Vytvořit dotaz v režimu návrhu.

V dolní části režimu návrhu definujete dotaz. Pro definici dotazu vyberte názvy databázových polí, které má dotaz obsahovat, a kritéria pro zobrazení polí. Chcete-li přeuspořádat sloupce v dolní části režimu návrhu, přetáhněte záhlaví sloupce na nové místo nebo vyberte sloupec a stiskněte +klávesy šipek.

V horní části návrhu jsou zobrazeny ikony lišty Návrh dotazu a lišty Návrh.

Pokud chcete dotaz vyzkoušet, poklepejte na jeho název v databázovém dokumentu. Výsledek dotazu se zobrazí v tabulce podobné zobrazení zdroje dat. Poznámka: zobrazená tabulka je pouze dočasná.

Klávesy v režimu návrhu dotazu

Procházet

Když otevřete návrh dotazu poprvé, zobrazí se dialogové okno, ve kterém musíte nejprve vybrat tabulku nebo dotaz, který bude základem pro nový dotaz.

Poklepáním na pole je přidáte do dotazu. Přetáhnutím určíte relace.

Při vytváření dotazu není možné vybrané tabulky měnit.

Odstranění tabulek

Chcete-li odstranit tabulku z návrhu, klepněte pravým tlačítkem na horní okraj tabulky. Z místní nabídky zvolte příkaz Odstranit. Další možností je klepnout na tabulku a stisknout klávesu Delete.

Posunutí a změna velikosti tabulky

Tabulky je možné podle potřeby uspořádávat a měnit jejich velikost. Jestliže chcete tabulku posunout, přetáhněte její horní okraj na požadovanou pozici. Okno tabulky zvětšíte nebo zmenšíte tak, že umístíte kurzor myši na jeho ohraničení nebo roh a tažením myši jej upravíte na požadovanou velikost.

Relace mezi tabulkami

Pokud existují mezi názvem pole v jedné tabulce a názvem pole v druhé tabulce datové relace, je možné v dotazu tyto relace použít.

Například máte sešit pro zboží (identifikované číslem) a sešit pro zákazníky, ve kterém pomocí odpovídajících čísel zboží zaznamenáváte všechno zboží, které si zákazník objedná. Mezi těmito dvěma datovými poli „číslo zboží“ tedy existuje relace. Pokud chcete vytvořit dotaz, který vrací všechno zboží, které si zákazník objednal, musíte získat data z obou sešitů. Musíte tedy LibreOffice sdělit, jaká existuje mezi daty v těchto dvou sešitech relace.

To zajistíte tak, že klepnete na název pole v tabulce (např. pole „Zboží-Číslo“ v tabulce Zákazníci), podržíte tlačítko myši a přetáhnete název pole na název pole v druhé tabulce („Zboží-Číslo“ v tabulce Zboží). Když uvolníte tlačítko myši, zobrazí se čára spojující obě pole. Do výsledného SQL dotazu se vloží odpovídající podmínka, že obsah polí musí být roven.

Vytvoření dotazu, který je založen na několika sešitech, je možné jen tehdy, pokud používáte LibreOffice jako rozhraní k relační databázi.

V dotazu není možné přistupovat k tabulkám z jiných databází. Dotazy nad několika tabulkami lze vytvořit jen v jedné databázi.

Určení typu relace

Pokud poklepete na čáru spojující dvě propojená pole nebo zvolíte příkaz Vložit - Nová relace, můžete určit typ relace v dialogovém okně Relace.

Případně je možné pomocí klávesy Tab vybrat relaci, poté stisknutím Shift+F10 zobrazit místní nabídku a zvolit příkaz Upravit. Některé databáze podporují jen některé druhy relací.

Odstranění relací

Chcete-li relaci mezi dvěma tabulkami odstranit, klepněte na spojnici a poté stiskněte klávesu Delete.

Případně je možné odstranit odpovídající záznamy v poli Použitá pole v dialogovém okně Relace nebo pomocí klávesy Tab vybrat relaci (zobrazí se zvětšeně), poté stisknutím Shift+F10 zobrazit místní nabídku a zvolit příkaz Odstranit.

Definování dotazu

Vyberte podmínky pro definici dotazu. Každý sloupec představuje jedno datové pole. Podmínky v jednom řádku se spojí booleovským AND.

Určení názvů polí

Nejprve vyberte všechny názvy polí, která chcete do dotazu přidat. To je možné udělat buď přetažením, nebo poklepáním na název pole v okně tabulky. Pokud chcete použít přetažení, přetáhněte název pole do spodní části okna návrhu. Můžete tak určit, do kterého sloupce chcete pole přidat. Pokud na název pole poklepete, přidá se do následujícího volného sloupce.

Odstranění názvů polí

Chcete-li název pole z dotazu odstranit, klepněte na záhlaví pole a v místní nabídce zvolte Odstranit.

Uložení dotazu

Dotaz uložíte pomocí ikony Uložit na liště Standardní. Zobrazí se dialogové okno, ve kterém zadáte název dotazu. Pokud databáze podporuje schémata, je možné zadat také název schématu.

Schéma

Zadejte název schématu, které se přiřadí dotazu nebo pohledu.

Název dotazu nebo název pohledu

Zadejte název dotazu nebo pohledu.

Filtrování dat

Chcete-li filtrovat data pro dotaz, nastavte požadovaná kritéria v dolní části okna návrhu dotazu. K dispozici jsou následující možnosti:

Pole

Zadejte název datového pole, na které se odkazujete v dotazu. Všechna nastavení v řádcích nastavení filtru se vztahují k tomuto poli. Pokud aktivujete buňku klepnutím myši, uvidíte tlačítko šipky, které vám umožňuje vybrat pole. Volba "Název tabulky.*" vybere všechna datová pole dané tabulky s tím, že zadaná kritéria budou použita pro všechna pole tabulky.

Alias

Zadejte alias datového pole. V dotazu se místo skutečného názvu pole použije tento alias. To umožňuje používat uživatelem definované popisky sloupců. Např. pokud se datové pole jmenuje DČ a místo tohoto názvu chcete v dotazu zobrazit ČísloDílu, zadejte jako alias ČísloDílu.

V SQL příkazu jsou aliasy definovány následovně:

SELECT sloupec AS alias FROM tabulka.

Například:

SELECT "PtNo" AS "PartNum" FROM "Parts"

Tabulka

Databázová tabulka, ve které se nachází vybrané datové pole. Pokud aktivujete buňku klepnutím myši, zobrazí se šipka, která vám umožní vybrat jinou tabulku z aktuálního dotazu.

Řadit

V této buňce je možné zvolit řazení: vzestupně, sestupně a neseřazeno. Textová pole se řadí abecedně (A až Z) a číselná pole číselně (0 až 9). U většiny databází může administrátor nastavit možnosti řazení.

Viditelné

Pokud zaškrtnete vlastnost datového pole Viditelné, zobrazí se dané pole v dotazu. Pokud pole používáte jen pro zadání podmínky nebo pro výpočet, nemusíte jej zobrazovat.

Kritéria

Určuje první kritérium, podle kterého se filtruje obsah datového pole.

Nebo

Do každého řádku s tímto názvem je možné zadat další kritérium pro filtrování. Více kritérií v jednom sloupci bude interpretováno jako booleovské OR.

Pro vložení filtru založeného na funkci můžete také použít místní nabídku záhlaví řádků v dolní části okna návrhu dotazu:

Funkce

Výběr dostupných funkcí závisí na databázi.

Pokud pracujete s vestavěnou databází HSQL, v seznamu Funkce najdete následující možnosti:

Funkce je možné zadat také přímo do SQL dotazu. Syntaxe je:

SELECT FUNKCE(sloupec) FROM tabulka.

Funkci pro výpočet součtu je například možné volat ve výrazu SQL následujícím způsobem:

SELECT SUM("Cena") FROM "Výrobek".

Kromě funkce Seskupit jsou výše zmíněné funkce tzv. agregační funkce. Jsou to funkce, které počítají data pro vytváření souhrnů z výsledků. Také mohou být k dispozici další funkce, které nejsou vypsány v seznamu. To závisí na použitém databázovém systému a na aktuální funkci ovladače, který se používá pro připojení k databázovému enginu.

Chcete-li použít funkce, které nejsou v seznamu, musíte je ručně zadat do Pole.

Také volání funkce je možné přiřadit alias. Pokud nechcete v záhlaví sloupce zobrazit řetězec dotazu, zadejte požadovaný název do Alias.

Odpovídající funkce v příkazu SQL je:

SELECT FUNKCE() AS alias FROM tabulka

Příklad:

SELECT COUNT(*) AS počet FROM "Položka".

Spustíte-li tuto funkci, není již možné vložit do dotazu další sloupce jinak než jako argument ve funkci "Group".

Příklady

V následujícím příkladu je dotaz spuštěn nad dvěma tabulkami: tabulkou „Zboží“ s polem „Zboží_č“ a tabulkou „Dodavatelé“ s polem „Název_dodavatele“. Obě tabulky mají navíc společný název pole „Dodavatel_č“.

K vytvoření dotazu obsahujícího všechny dodavatele, kteří dodávají více než tři položky, je nutné provést následující kroky.

1. Do návrhu dotazu vložte tabulky „Zboží“ a „Dodavatelé“.

2. Spojte pole „Dodavatel_č“ obou tabulek, pokud dosud relace tohoto typu neexistuje.

3. Poklepejte na pole „Zboží_č“ v tabulce „Zboží“. Zobrazte si pomocí místní nabídky řádek Funkce a zvolte funkci Počet.

4. Zadejte jako kritérium hodnotu >3 a zrušte zaškrtnutí pole Viditelné.

5. V tabulce „Dodavatelé“ poklepejte na pole „Název_dodavatele“ a vyberte funkci Seskupit.

6. Spusťte dotaz.

Existuje-li v tabulce „Zboží“ pole „Cena“ (jako jednotlivá cena výrobku) a pole „Dodavatel_č“ (jako dodavatel daného výrobku), potom je možné obdržet průměrnou cenu za položku dodávanou dodavatelem zadáním následujícího dotazu:

1. Do návrhu dotazu vložte tabulku „Zboží“.

2. Poklepejte na pole „Cena“ a „Dodavatel_č“.

3. Pro pole „Cena“ povolte řádek Funkce a zvolte funkci Průměr.

4. Můžete také zadat do řádku pro zástupný název (alias) text „Average“ (bez uvozovek).

5. Pro pole „Dodavatel_č“ vyberte funkci Seskupit.

6. Spusťte dotaz.

K dispozici jsou následují příkazy a symboly místní nabídky:

Funkce

Zobrazí nebo skryje řádek pro výběr funkcí.

Název tabulky

Zobrazí nebo skryje řádek s názvem tabulky.

Alias

Zobrazí nebo skryje řádek s aliasem.

Jedinečné hodnoty

Zobrazí pouze jedinečné hodnoty z dotazu. To se týká vícenásobných záznamů, které v daných polích obsahují opakovaně stejná data. Je-li příkaz Jedinečné hodnoty aktivní, měl by být z těchto záznamů zobrazen pouze jediný (DISTINCT). V opačném případě se zobrazí všechny záznamy splňující kritéria dotazu (ALL).

Příklad: pokud se v databázi adres několikrát objevuje „Novák“, po zvolení příkazu Jedinečné hodnoty se v dotazu „Novák“ zobrazí jen jednou.

Pokud dotaz obsahuje několik polí, jedinečná musí být kombinace hodnot ze všech polí. Příklad: v adresáři máte jednou „Nováka v Brně“ a dvakrát „Nováka v Praze“. S příkazem Jedinečné hodnoty použije dotaz pole „příjmení“ a „město“ a ve výsledku dotazu zobrazí jednou „Nováka v Brně“ a jednou „Nováka v Praze“.

V SQL odpovídá tento příkaz predikátu DISTINCT.

Limit

Umožní omezit maximální počet záznamů, které dotaz vrátí.

Pokud je zadán Limit, získáte nejvýše tolik řádků, kolik udává stanovený počet. Jinak se zobrazí všechny záznamy odpovídající kritériím dotazu.

Formulování podmínek filtru

Při vytváření podmínek filtru máte k dispozici různé operátory a příkazy. Kromě relačních operátorů jsou k dispozici také příkazy specifické pro SQL, které kontrolují obsah databázových polí. Pokud použijete příkazy v syntaxi LibreOffice, LibreOffice je pomocí interního syntaktického analyzátoru automaticky převede převede na odpovídají syntaxi SQL. Možné je také zadat SQL příkaz přímo a tím se použití interního analyzátoru vyhnout. Následující tabulka uvádí přehled operátorů a příkazů:

Příklady

Escape sekvence Like: {escape 'escape-character'}

SELECT * FROM Item WHERE ItemName LIKE 'The *%' {escape '*'}

Tento příklad zobrazí všechny záznamy, kde název zboží začíná 'Tužka *'. To znamená, že je možné hledat také znaky, které se jinak identifikují jako zástupné znaky, např. *, ?, _, % nebo tečku.

Escape sekvence Outer Join: {oj outer-join}

SELECT Article.* FROM {oj item LEFT OUTER JOIN orders ON item.no=orders.ANR}

Dotazy na textová pole

Jestliže chcete filtrovat obsah textových polí, musíte uzavřít výraz do jednoduchých uvozovek. Rozdíl mezi velkými a malými znaky závisí na použité databázi. U LIKE podle definice záleží na velikosti znaků (ale některé databáze nejsou tak striktní).

Dotazy na datová pole

Datová pole se kvůli zřetelné identifikaci označují jako #Date#. V podmínkách použité konstanty (literály) data, času nebo data/času mohou mít escape syntaxi SQL nebo výchozí syntaxi SQL2.

SELECT {d '1999-12-31'} FROM world.years

SELECT * FROM mytable WHERE years='1999-12-31'

Všechny výrazy (literály) data musí být uzavřeny v jednoduchých uvozovkách. (Podrobnosti naleznete v dokumentaci k databázi a konektoru, které používáte.)

Dotazy na pole s hodnotami Yes/No (Ano/Ne)

Chcete-li filtrovat pole typu Ano/Ne, použijte pro tabulky dBASE následující syntaxi:

Tato syntaxe závisí na použitém databázovém systému. Poznámka: pole Ano/Ne se mohou chovat různě (mohou nabývat dvou stavů místo tří).

Parametrické dotazy

Parametrické dotazy umožňují uživateli při vykonávání zadat vstupní hodnoty. Tyto hodnoty jsou použity v rámci kritérií pro výběr záznamů, které se mají zobrazit. Každé hodnotě odpovídá název parametru, který se zobrazí s výzvou pro zadání hodnoty při spuštění dotazu.

Názvy parametrů jsou uvozeny dvojtečkou, a to v režimu návrhu i SQL. Tento zápis se použije, kdekoliv se má hodnota vyskytnout. Pokud se stejná hodnota objevuje v dotazu víckrát, použije se tentýž název parametru.

V nejjednodušším případě, kdy uživatel vkládá hodnotu, které se mají záznamy rovnat, je název parametru s uvozující dvojtečkou zadán do řádku Kritérium. V režimu SQLto lze zapsat jako WHERE "Pole" = :Nazev_parametru

Názvy parametrů nesmí obsahovat některý ze znaků <mezera>`!"$%^*()+={}[]@'~#<>?/,. Rovněž nemohou být stejné jako názvy polí nebo vyhrazená slova jazyka SQL. Mohou být stejné jako aliasy.

Při výběru záznamů podle části obsahu textového pole bývá užitečné přidat skrytý sloupec s kritériem "LIKE '%' || :Cast_pole || '%'". Tím vyberete záznamy s přesnou shodou. Požadujete-li, aby při testování nezáleželo na velikosti písmen, řešením může být použití pole LOWER (Nazev_pole) a kritéria LIKE LOWER ( '%' || :Cast_pole || '%' ). Důležité jsou mezery v kritériu: jsou-li vynechány, syntaktický analyzátor SQL celé kritérium interpretuje jako řetězec, s nímž se má obsah shodovat. V režimu SQL lze toto zapsat jako LOWER ( "Nazev_pole" ) LIKE LOWER ( '%' || :Cast_pole || '%' ).

Parametrické dotazy lze použít jako zdroj dat pro podformuláře, u nichž může uživatel omezit zobrazené záznamy.

Vstup parametru

V dialogovém okně Vstup parametru je uživatel požádán o zadání hodnot parametrů. Zadejte hodnotu pro každý parametr dotazu a potvrďte klepnutím na OK nebo stisknutím klávesy Enter.

Hodnoty zadané uživatelem mohou obsahovat jakékoliv znaky, které jsou povolené pro SQL příslušného kritéria; to může záviset na použitém databázovém systému.

Je-li potřeba získat záznamy pomocí komplexnějších kritérií, je možné jako součást hodnoty použít zástupné znaky SQL "%" (libovolný řetězec) nebo "_" (libovolný jediný znak).

Režim SQL

SQL neboli „Structured Query Language“ je jazyk, který popisuje příkazy sloužící k aktualizaci a správě relačních databází.

V LibreOffice nemusíte pro většinu dotazů SQL znát, jelikož není nutné zadávat příkazy SQL přímo. Když vytvoříte dotaz v režimu návrhu, LibreOffice automaticky převede zadané instrukce na odpovídající syntaxi SQL. Pokud se tlačítkem Režim návrhu zap/vyp přepnete do režimu SQL, zobrazí se příkazy SQL pro vytvořený dotaz.

Dotaz je však možné také zadat přímo v SQL. Speciální syntaxe ovšem závisí na použitém databázovém systému.

Pokud zadáváte SQL dotaz přímo, je možné použít specifické možnosti SQL, které nelze zadat v grafickém rozhraní v Návrhu dotazu. Tyto dotazy je nutno spouštět v nativním režimu SQL.

Po klepnutí na ikonu Spustit SQL příkaz přímo v režimu SQL je možné zadat dotaz, který nezpracovává LibreOffice, ale posílá se přímo databázovému enginu.