Design de consulta

A vista Design de consulta permite-lhe criar e editar uma consulta de uma base de dados.

Para aceder a este comando...

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


Ícone de nota

A maioria das bases de dados utiliza consultas para filtrar ou ordenar as tabelas da base de dados e mostrar os registos no computador. As vistas oferecem a mesma funcionalidade que as consultas, mas ao nível do servidor. Se a base de dados estiver num servidor que suporte vistas, pode utilizar as vistas para filtrar os registos no servidor de modo a acelerar o tempo de exibição.


Ícone de nota

Ao selecionar o comando Criar vista no separador Tabelas de um documento de base de dados, irá visualizar a janela Design de vista que é semelhante à janela Design de consulta aqui descrita.


O esquema da janela Design de consulta é armazenado com uma consulta criada, não sendo possível armazenar o mesmo com uma vista criada.

Vista de design

Para criar uma consulta, clique no ícone Consultas do documento de base de dados e, em seguida, clique em Criar consulta na vista de design.

The lower pane of the Design View is where you define the query. To define a query, specify the database field names to include and the criteria for displaying the fields. To rearrange the columns in the lower pane of the Design View, drag a column header to a new location, or select the column and press +arrow key.

Os ícones da barra Design de consulta e a barra Design são mostrados na parte superior da janela vista de Design de consulta.

Se quiser testar uma consulta, clique duas vezes no nome da consulta no documento de base de dados. O resultado da consulta é mostrado numa tabela semelhante à da Vista de origens de dados. Nota: a tabela mostrada é temporária.

Teclas na vista da design de consulta

Tecla

Função

F4

Pré-visualizar

F5

Executar consulta

F7

Adicionar tabela ou consulta


Procurar

A primeira vez que abrir um design de consulta, surgirá uma caixa de diálogo na qual deve selecionar a tabela ou consulta que será utilizada para a nova consulta.

Clique duas vezes nos campos para os adicionar à consulta. Arraste e largue para definir relações.

Ícone de nota

Ao desenhar uma consulta, não pode modificar as tabelas selecionadas.


Remover tabelas

Para remover a tabela da vista de design, clique na margem superior da janela da tabela para mostrar o menu de contexto. Pode utilizar o comando Eliminar para remover a tabela da vista de design. Em alternativa, poderá premir a tecla Delete.

Mover e modificar o tamanho de uma tabela

Pode redimensionar e dispor as tabelas da forma que pretender. Para mover tabelas, arraste a margem superior para a posição pretendida. Aumente ou reduza a dimensão na qual a tabela é apresentada posicionando o cursor do rato numa margem ou canto e arrastando a tabela até que esta tenha a dimensão pretendida.

Relações entre tabelas

Se existirem relações de dados entre um nome de um campo de uma tabela e o nome de um campo de outra tabela, pode utilizar estas relações para a consulta.

If, for example, you have a spreadsheet for articles identified by an article number, and a spreadsheet for customers in which you record all articles that a customer orders using the corresponding article numbers, then there is a relationship between the two "article number" data fields. If you now want to create a query that returns all articles that a customer has ordered, you must retrieve data from two spreadsheets. To do this, you must inform LibreOfficeDev about the relationship which exists between the data in the two spreadsheets.

To do this, click a field name in a table (for example, the field name "Item-Number" from the Customer table), hold down the mouse button and then drag the field name to the field name of the other table ("Item-Number" from the Item table). When you release the mouse button, a line connecting the two fields between the two table windows appears. The corresponding condition that the content of the two field names must be identical is entered in the resulting SQL query.

A criação de uma consulta baseada em várias folhas só é possível se utilizar o LibreOfficeDev como interface para uma base de dados relacional.

Ícone de nota

Não é possível aceder a tabelas de diferentes bases de dados numa consulta. As consultas que envolvam várias tabelas só podem ser criadas dentro de uma única base de dados.


Specifying the relation type

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.

Em alternativa, prima a tecla Tab até que o vetor de união esteja selecionado e prima as teclas Shift+F10 para abrir o menu de contexto onde poderá escolher o comando Editar. Algumas bases de dados apenas permitem um subconjunto dos tipos de união possíveis.

Eliminar relações

Para eliminar uma relação entre duas tabelas, clique na linha de ligação e prima a tecla Delete.

Em alternativa, elimine as entradas correspondentes nos Campos utilizados na caixa de diálogo Relações. Ou prima a tecla Tab até que o vetor de ligação seja destacado, prima as teclas Shift+F10 para abrir o menu de contexto e selecione o comando Eliminar.

Defining the query

Selecione as condições para definir a consulta. Cada coluna da tabela de design aceita um campo de dados para a consulta. As condições de uma linha são associadas por um E booleano.

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.

Eliminar nomes dos campos

Para remover um nome de um campo da consulta, clique no cabeçalho de coluna do campo e escolha o comando Eliminar no menu de contexto.

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.

Esquema

Introduza o nome do esquema que é atribuído à vista de consulta ou de tabela.

Nome da consulta ou da vista de tabela

Introduza o nome da consulta ou da vista de tabela.

Filtrar dados

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

Campo

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.

Nome alternativo

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 column AS alias FROM table.

Por exemplo:

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

Tabela

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.

Ordenar

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.

Visível

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.

Critério

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

ou

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:

Funções

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:

Opção

SQL

Efeito

Sem função

Não será executada qualquer função.

Média

AVG

Calcula a média aritmética de um campo.

Contar

COUNT

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

a) CONTAR(*): Passar um asterisco como o argumento, faz a contagem de todos os registos na tabela.

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.

Máximo

MAX

Determines the highest value of a record for that field.

Mínimo

MIN

Determines the lowest value of a record for that field.

Soma

SUM

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

Agrupar

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.


Também é possível introduzir chamadas de função diretamente na instrução SQL. A sintaxe é:

SELECT FUNCTION(coluna) FROM tabela.

Por exemplo, a chamada de função em SQL para calcular uma soma é:

SELEC SOMA("Preço") DE "Artigo".

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.

A função correspondente numa instrução SQL é:

SELECT FUNCTION() AS alias FROM tabela

Exemplo:

SELECT COUNT(*) AS count FROM "Item"

Ícone de nota

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


Exemplos

No exemplo que se segue, é executada uma consulta em duas tabelas: uma tabela "Item" com o campo "Item_No" e uma tabela "Suppliers" com o campo "Supplier_Name". Além disso, ambas as tabelas têm um nome de campo comum "Supplier_No."

Tem que executar os seguintes passos para criar uma consulta que contenha todos os fornecedores que entregam mais do que três artigos.

  1. Inserir as tabelas "Item" e "Fornecedores" no design de consulta.

  2. Ligue os campos "Supplier_No" das duas tabelas se ainda não existir uma relação deste tipo.

  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. Introduza >3 como um critério e desative o campo Visível.

  5. Clique duas vezes no campo "Supplier_Name" da tabela "Suppliers" e escolha a função Agrupar.

  6. Execute a consulta.

Se os campos "price" (para o preço individual de um artigo) e "Supplier_No" (para o fornecedor do artigo) existirem na tabela "Item", é possível obter o preço médio do artigo que um fornecedor entrega com a seguinte consulta:

  1. Inserir a tabela "Item" no design de consulta.

  2. Clique duas vezes nos campos "Price" e "Supplier_No".

  3. Ative a linha Função e selecione a função Média do campo "Preço".

  4. Também é possível introduzir "Média" na linha como nome alternativo (sem aspas).

  5. Escolha Grupo para o campo "Supplier_No".

  6. Execute a consulta.

Estão disponíveis os seguintes comandos e símbolos no menu de contexto:

Funções

Shows or hides a row for the selection of functions.

Nome da tabela

Mostra ou oculta a linha para o nome da tabela.

Nome alternativo

Mostra ou oculta a linha para o nome alternativo.

Valores distintos

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

Por exemplo, se o nome "Smith" ocorrer várias vezes na base de dados de endereços, é possível escolher o comando Valores distintos para especificar na consulta que o nome "Smith" irá ocorrer apenas uma vez.

Para uma consulta que envolva vários campos, a combinação de valores de todos os campos terá de ser única, para que o resultado possa ser formado a partir de um registo específico. Por exemplo, "Ferreira em Porto" ocorre uma vez no livro de endereços e "Ferreira em Lisboa" ocorre duas. Com o comando Valores distintos, a consulta utilizará os dois campos "apelido" e "cidade" e o resultado será "Ferreira em Porto" uma vez e "Ferreira em Lisboa" uma vez.

Em SQL, este comando corresponde ao predicado DISTINCT.

Limite

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.

Formular condições de filtro

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:

Operador

Significado

A condição é satisfeita se...

=

é igual a

... o conteúdo do campo é idêntico à expressão indicada.

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

<>

é diferente de

... o conteúdo do campo não corresponde à expressão especificada.

>

maior que

... o conteúdo do campo é maior do que a expressão especificada.

<

menor que

... o conteúdo do campo é menor do que a expressão especificada.

>=

maior ou igual a

... o conteúdo do campo é maior ou igual à expressão especificada.

<=

é menor ou igual a

... o conteúdo do campo é menor ou igual à expressão especificada.


LibreOfficeDev - comando

Comando SQL

Significado

A condição é satisfeita se...

IS EMPTY

IS NULL

vazio

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

não está vazio

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

é um elemento de

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

Não é um elemento de

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

BETWEEN x AND y

BETWEEN x AND y

está dentro do intervalo [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

Não está dentro do intervalo [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...)

contém 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...)

não contém a, b, c...

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

= TRUE

= TRUE

tem o valor True

... o nome do ficheiro tem o valor True.

= FALSE

= FALSE

tem o valor false

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


Exemplos

='Ms.'

devolve os nomes de campos com o conteúdo "Ms."

<'2001-01-10'

devolve as datas ocorridas antes de 10 de janeiro de 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".


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

Exemplo: select * from Item where ItemName like 'The *%' {escape '*'}

O exemplo mostra todas as entradas em que o nome do item começa por 'The *'. Isto significa que também poderá procurar caracteres que de outra forma seriam interpretados como marcadores de posição, tais como *, ?, _, % ou o ponto final.

Junção externa Sequência de escape: {oj outer-join}

Exemplo: selecione Article.* from {oj item LEFT OUTER JOIN orders ON item.no=orders.ANR}

Consultar campos de texto

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

Consultar campos de data

Os campos de data são representados como #Date# para os identificar como tal. As datas, horas e constantes de data e hora (literais) utilizadas nas condições podem ser do tipo SQL Escape Syntax ou SQL2.

Elemento do tipo de data

Sintaxe SQL Escape #1 - pode estar obsoleta

Sintaxe SQL Escape #2

Sintaxe SQL2

Data

{D'YYYY-MM-DD'}

{d 'YYYY-MM-DD'}

'YYYY-MM-DD'

Hora

{D'HH:MM:SS'}

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

'HH:MI:SS[.SS]'

DataHora

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

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

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


Exemplo: select {d '1999-12-31'} from world.years

Exemplo: select * from mytable where years='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.)

Campos Consultar Sim/Não

Para consultar campos Sim/Não, utilize a seguinte sintaxe para tabelas dBase:

Estado

Critério de consulta

Exemplo

Sim

para tabelas dBASE: não igual a qualquer valor indicado

=1 devolve todos os registos em que o campo Sim/Não tenha o estado "Sim" ou "Ativo" (selecionado a preto),

Não

.

=0 devolve todos os registos para os quais o campo Sim/Não tiver o estado "Não" ou "Desativar" (sem seleção).

Nulo

É NULO

IS NULL devolve todos os registos para os quais o campo Sim/Não não tenha o estado Sim nem Não (selecionado a cinzento).


Ícone de nota

A sintaxe depende do sistema de base de dados utilizado. Também deverá ter em consideração que os campos Sim/Não podem ser definidos de forma diferente (apenas 2 estados em vez de 3).


Consultas de parâmetros

As consultas parametrizadas permite ao utilizador inserir valores durante a execução. Estes valores são utilizados nos critérios para selecionar os registos a serem mostrados. Cada valor tem um parâmetro associado e que é solicitado ao utilizador quando a consulta é executada.

Os nomes dos parâmetros são precedidos pelo carácter dois pontos ':', na vista SQL e na vista de design de uma consulta. Estes nomes podem ser utilizados sempre que um valor aparecer. Se o mesmo valor aparecer mais do que uma vez na consulta, será utilizado o mesmo nome de parâmetro.

No caso mais simples, em que o utilizador insere um valor para uma relação de igualdade, o nome do parâmetro precedido de dois pontos é inserido na linha Critério. No modo SQL, deve ser digitado como WHERE "Campo" = :Nome_do_parâmetro

Ícone de aviso

Os nomes de parâmetros não podem conter os caracteres <space>`!"$%^*()+={}[]@'~#<>?/,. Para além disso, não podem ser iguais aos nomes de campos nem a palavras reservadas do SQL. No entanto, podem ter os mesmos nomes que os alias.


Ícone da dica

Uma construção útil para selecionar registos com base em partes do conteúdo de campos de texto é adicionar uma coluna oculta com LIKE '%/'||:Parte_do_campo || '%'" como critério. Esta ação permite selecionar registos com correspondência exata. Se desejar um teste que não diferencie maiúsculas de minúsculas, uma solução é usar LOWER (nome_do_campo) como campo e LIKE LOWER ( '%' || :Parte _do_campo || '%' ) como critério. Tenha em conta de que os espaços do critério são importantes. Se forem deixados de fora, o analisador SQL interpreta todo o critério como cadeia de correspondência. No modo SQL, deve sempre digitar como LOWER ( "nome_do_campo" ) LIKE LOWER ( '%' || :Parte_do_campo || '%' ).


Pode utilizar consultas com parâmetros como origem de dados para sub-formulários, de modo a permitir restringir os registos a mostrar.

Entrada de parâmetros

A caixa de diálogo Entrada de parâmetros solicita ao utilizador a introdução dos valores dos parâmetros. Introduza um valor para cada parâmetro da consulta e confirme clicando em Aceitar ou premindo Enter.

Os valores introduzidos pelo utilizador podem consistir de quaisquer caracteres permitidos pelo SQL para o critério relevante; isto pode depender do sistema de base de dados subjacente.

Ícone da dica

O utilizador pode usar os caracteres genéricos de SQL "%" (expressão arbitrária) ou "_" (carácter único arbitrário) como parte do valor para obter registos com critérios mais complexos.


Modo SQL

SQL significa "Structured Query Language" (Linguagem de Consulta Estruturada) e descreve as instruções para atualização e administração de bases de dados relacionais.

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.