Návrh dotazu

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

Pro přístup k tomuto příkazu...

In a database file window, click the Queries icon, then choose Edit - Edit.


Ikona poznámky

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


Ikona poznámky

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 vytvář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 +kurzorové klávesy.

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

Pokud chcete dotaz ověřit, poklepejte na název dotazu 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

Klávesa

Funkce

F4

Náhled

F5

Spustit dotaz

F7

Přidat tabulku nebo dotaz


Procházet

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

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

Ikona poznámky

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í tabulky a změna velikosti tabulky

Tabulky je možné podle potřeby uspořádat a změ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.

Pří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 LibreOfficeDev sdělit, jaká existuje relace mezi daty v těchto dvou sešitech.

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 pokud používáte LibreOfficeDev jako rozhraní k relační databázi .

Ikona poznámky

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.


Specifikování typu relace

If you double-click on the line connecting two linked fields or call the menu command Insert - New Relation, you can specify the type of relation in the Relations dialog.

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 odstranit relaci mezi dvěma tabulkami, 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.

Defining the query

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

Specifying field names

First, select all field names from the tables that you want to add to the query. You can do this either by drag-and-drop or by double-clicking a field name in the table window. With the drag-and-drop method, use the mouse to drag a field name from the table window into the lower area of the query design window. As you do this, you can decide which column in the query design window will receive the selected field. A field name can also be selected by double-clicking. It will then be added to the next free column in the query design window.

Odstranění názvů polí

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

Saving the query

Use the Save icon on the Standard toolbar to save the query. You will see a dialog that asks you to enter a name for the query. If the database supports schemas, you can also enter a schema name.

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

To filter data for the query, set the desired criteria in the lower area of the query design window. The following options are available:

Pole

Enter the name of the data field that is referred to in the Query. All settings made in the filter option rows refer to this field. If you activate a cell here with a mouse click you'll see an arrow button, which enables you to select a field. The "Table name.*" option selects all data fields with the effect that the specified criteria will be applied to all table fields.

Alias

Specifies an alias. This alias will be listed in the query instead of the field name. This makes it possible to use user-defined column labels. For example, if the data field is named PtNo and, instead of that name, you would like to have PartNum appear in the query, enter PartNum as the alias.

In a SQL statement, aliases are defined as follows:

SELECT sloupec AS alias FROM tabulka.

Například:

SELECT "DČ" AS "ČísloDílu" FROM "Díly"

Tabulka

The corresponding database table of the selected data field is listed here. If you activate this cell with a mouse click, an arrow will appear which enables you to select a different table for the current query.

Řadit

If you click on this cell, you can choose a sort option: ascending, descending and unsorted. Text fields will be sorted alphabetically and numerical fields numerically. For most databases, administrators can set the sorting options at the database level.

Viditelné

If you mark the Visible property for a data field, that field will be visibly displayed in the resulting query. If you are only using a data field to formulate a condition or make a calculation, you do not necessarily need to display it.

Kritérium

Specifies a first criteria by which the content of the data field is to be filtered.

Nebo

Here you can enter one additional filter criterion for each line. Multiple criteria in a single column will be interpreted as boolean OR.

You can also use the context menu of the line headers in the lower area of the query design window to insert a filter based on a function:

Funkce

The functions which are available here depend on those provided by the database engine.

If you are working with the embedded HSQL database, the list box in the Function row offers you the following options:

Možnost

SQL

Význam

Bez funkce

Nebude spuštěna žádná funkce.

Průměr

AVG

Vypočítá aritmetický průměr pole.

Počet

COUNT

Determines the number of records in the table. Empty fields can either be counted (a) or excluded (b).

a) COUNT(*): Zadáte-li jako argument funkce hvězdičku, spočítá funkce všechny záznamy v tabulce.

b) COUNT(column): Passing a field name as an argument counts only those records for fields in which the field name in question contains a value. Records with fields having Null values (i.e. fields containing no characters) will not be counted.

Maximum

MAX

Determines the highest value of a record for that field.

Minimum

MIN

Determines the lowest value of a record for that field.

Součet

SUM

Calculates the sum of the values of records for the associated fields.

Seskupit

GROUP BY

Groups query data according to the selected field name. Functions are executed according to the specified groups. In SQL, this option corresponds to the GROUP BY clause. If a criterion is added, this entry appears in the SQL HAVING sub-clause.


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

Except for the Group function, the above functions are called Aggregate functions. These are functions that calculate data to create summaries from the results. Additional functions that are not listed in the list box might be also possible. These depend on the specific database engine in use and on the current functionality provided by the Base driver used to connect to that database engine.

To use other functions not listed in the list box, you must enter them manually under Field.

You can also assign aliases to function calls. If you not wish the query string to be displayed in the column header, enter a desired substitute name under 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".

Ikona poznámky

If you run such a function, you cannot insert any additional columns for the query other than as an argument in a "Group" function.


Příklady

V následujícím příkladu je dotaz spuštěn nad dvěma tabulkami: tabulkou Položka s polem Položka_č 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 kroky uvedené dále.

  1. Do návrhu dotazu vložte tabulky Položka a Dodavatelé.

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

  3. Double-click on the "Item_No" field from the "Item" table. Display the Function line using the context menu and select the Count function.

  4. Zadejte hodnotu >3 jako kritérium a zrušte zaškrtnutí políčka v poli Viditelné.

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

  6. Spusťte dotaz.

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

  1. Do návrhu dotazu vložte tabulku Položka.

  2. Poklepejte na pole Cena a Dodavatel_č.

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

  4. Možné je také zadat text Average do řádku pro zástupný název (alias).

  5. Pro pole Dodavatel_č vyberte funkci Group.

  6. Spusťte dotaz.

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

Funkce

Shows or hides a row for the selection of functions.

Název tabulky

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

Alias

Zobrazí nebo skryje řádek s aliasem.

Jedinečné hodnoty

Retrieves only distinct values from the query. This applies to multiple records that might contain several repeating occurrences of data in the selected fields. If the Distinct Values command is active, you should only see one record in the query (DISTINCT). Otherwise, you will see all records corresponding to the query criteria (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í, musí být jedinečná 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

Allows you to limit the maximum number of records returned by a query.

If a Limit construction is added, you will get at most as many rows as the number you specify. Otherwise, you will see all records corresponding to the query criteria.

Formulování podmínek filtru

When formulating filter conditions, various operators and commands are available to you. Apart from the relational operators, there are SQL-specific commands that query the content of database fields. If you use these commands in the LibreOfficeDev syntax, LibreOfficeDev automatically converts these into the corresponding SQL syntax via an internal parser. You can also enter the SQL command directly and bypass the internal parser. The following tables give an overview of the operators and commands:

Operátor

Význam

Případy, kdy je podmínka splněna

=

se rovná

Obsah pole se shoduje se zadaným výrazem.

The operator = will not be displayed in the query fields. If you enter a value without any operator, the = operator is automatically assumed.

<>

není rovno

Obsah pole se neshoduje se zadaným výrazem.

>

je větší než

Obsah pole je větší než zadaný výraz.

<

je menší než

Obsah pole je menší než zadaný výraz.

>=

je větší nebo se rovná

Obsah pole je větší nebo roven zadanému výrazu.

<=

je menší nebo se rovná

Obsah pole je menší nebo roven zadanému výrazu.


Příkaz LibreOfficeDev

SQL příkaz

Význam

Případy, kdy je podmínka splněna

IS EMPTY

IS NULL

nezadáno

... the field contains no data. For Yes/No fields with three possible states, this command automatically queries the undetermined state (neither Yes nor No).

IS NOT EMPTY

IS NOT NULL

není prázdné

... the field is not empty, i.e it contains data.

LIKE

placeholder (*) for any number of characters

placeholder (?) for exactly one character

LIKE

placeholder (%) for any number of characters

Placeholder (_) for exactly one character

je prvkem

... the data field must contain the indicated search string in the order given. The position of the (*) placeholder indicates whether the searched for string (x) occurs before the placeholder (x*), after the placeholder (*x) or between the placeholders (*x*). Instead of the familiar file system (*) placeholder, you can also use the SQL (%) character as a placeholder in SQL queries in the LibreOfficeDev interface.

The (*) or (%) placeholder stands for any number of characters. The question mark (?) in the LibreOfficeDev interface or the underscore (_) in SQL queries is used to represent exactly one character.

NOT LIKE

NOT LIKE

není prvkem

... the field does not contain data having the specified expression.

BETWEEN x AND y

BETWEEN x AND y

patří do intervalu [x,y]

... the field contains a data value that lies between the two values x and y.

NOT BETWEEN x AND y

NOT BETWEEN x AND y

nepatří do intervalu [x,y]

... the field contains a data value that does not lie between the two values x and y.

IN (a; b; c...)

Note that semicolons are used as separators in all value lists!

IN (a, b, c...)

obsahuje a, b, c…

... the field name contains one of the specified expressions a, b, c,... Any number of expressions can be specified, and the result of the query is determined by a boolean OR operator. The expressions a, b, c... can be either numbers or characters

NOT IN (a; b; c...)

NOT IN (a, b, c...)

neobsahuje a, b, c…

... the field does not contain one of the specified expressions a, b, c,...

= TRUE

= TRUE

má hodnotu Pravda

Pole má hodnotu Pravda.

= FALSE

= FALSE

má hodnotu Nepravda

... the field data value is set to false.


Příklady

='Ms.'

Vrátí pole, která obsahují výraz "Polsko".

<'2001-01-10'

Vrátí data, která jsou starší než 10. ledna 2001.

LIKE 'g?ve'

returns records with field content such as "give" and "gave".

LIKE 'S*'

returns records with field contents such as "Sun".

BETWEEN 10 AND 20

returns records with field content between the values 10 and 20. (The fields can be either text fields or number fields).

IN (1; 3; 5; 7)

returns records with the values 1, 3, 5, 7. If the field name contains an item number, for example, you can create a query that returns the item having the specified number.

NOT IN ('Smith')

returns records that do not contain "Smith".


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

Příklad: select * from Zboží where NázevZboží like 'Tužka *%' {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}

Příklad: select Article.* from {oj item LEFT OUTER JOIN orders ON item.no=orders.ANR}

Dotazy na textová pole

To query the content of a text field, you must put the expression between single quotes. The distinction between uppercase and lowercase letters depends on the database in use. LIKE, by definition, is case-sensitive (though some databases don't interpret this strictly).

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.

Prvek datového typu

Escape syntaxe SQL č. 1 - může být zastaralá

Escape syntaxe SQL č. 2

Syntaxe SQL2

Datum

{D'YYYY-MM-DD'}

{d 'YYYY-MM-DD'}

'YYYY-MM-DD'

Čas

{D'HH:MM:SS'}

{t 'HH:MI:SS[.SS]'}

'HH:MI:SS[.SS]'

Datum a čas

{D'YYYY-MM-DD HH:MM:SS'}

{ts 'YYYY-MM-DD HH:MI:SS[.SS]'}

'YYYY-MM-DD HH:MI:SS[.SS]'


Příklad: select {d '1999-12-31'} from svět.roky

Příklad: select * from mojetabulka where roky='1999-12-31'

All date expressions (date literals) must be enclosed with single quotation marks. (Consult the reference for the particular database and connector you are using for more details.)

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:

Stav

Kritérium dotazu

Příklad

Ano

pro tabulky ve formátu dBASE: není rovno žádné zadané hodnotě

=1 vrátí všechny záznamy, kde má pole Ano/Ne stav Ano nebo Zapnuto (zobrazeno černě),

Ne

.

=0 vrátí všechny záznamy, kde má pole Ano/Ne stav Ne nebo Vypnuto (neoznačeno).

Null

IS NULL

IS NULL vrátí všechny záznamy, kde pole Ano/Ne není ve stavu ani Ano ani Ne (označeno šedě).


Ikona poznámky

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

Ikona upozornění

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.


Ikona tipu

Při výběru záznamů podle části obsahu textového pole je užitečné přidat skrytý sloupec s kritériem "LIKE '%' || :Cast_pole || '%'". Tím vybereme záznamy s přesnou shodou. Požadujeme-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.

Ikona tipu

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

In LibreOfficeDev you do not need any knowledge of SQL for most queries, since you do not have to enter the SQL code. If you create a query in the query designer, LibreOfficeDev automatically converts your instructions into the corresponding SQL syntax. If, with the help of the Switch Design View On/Off button, you change to the SQL view, you can see the SQL commands for a query that has already been created.

You can formulate your query directly in SQL code. Note, however, that the special syntax is dependent upon the database system that you use.

If you enter the SQL code manually, you can create SQL-specific queries that are not supported by the graphical interface in the Query designer. These queries must be executed in native SQL mode.

By clicking the Run SQL command directly icon in the SQL view, you can formulate a query that is not processed by LibreOfficeDevand sent directly to the database engine.