Design da consulta

A Exibição de design de consulta permite criar e editar uma consulta de banco de dados.

Para acessar este comando...

Em uma janela de arquivo de banco de dados, clique no ícone Consultas e escolha Editar - Editar.


Ícone Nota

A maioria dos bancos de dados utiliza consultas para filtrar ou ordenar tabelas de bancos de dados ao exibir registros no computador. As exibições oferecem a mesma funcionalidade que as consultas, mas no lado do servidor. Se o banco de dados estiver em um servidor que ofereça suporte a exibições, você poderá utilizá-las para filtrar os registros no servidor e, assim, acelerar a tempo de exibição.


Ícone Nota

Ao selecionar o comando Criar exibição na página da guia Tabelas de um documento de banco de dados, você verá a janela Design de exibição, que lembra a janela Design de consulta descrita aqui.


O leiaute da janela de Design de consulta é armazenada junto com a consulta criada, mas não pode ser armazenada com uma exibição criada.

A exibição de design

Para criar uma consulta, clique no ícone Consultas em um documento de banco de dados e, em seguida, clique em Criar consulta na exibição de design.

O painel inferior da exibição de design é onde você define a consulta. Para definir uma consulta, especifique os nomes de campos do banco de dados a serem incluídos e os critérios para exibir os campos. Para reorganizar as colunas no painel inferior da Exibição de design, arraste o cabeçalho da coluna para um novo local ou selecione a coluna e pressione +Tecla de seta.

Na parte superior da janela Exibição de design, são exibidos os ícones da barra Design de consulta e da barra Design.

Caso deseje testar uma consulta, clique duas vezes no nome da consulta no documento de banco de dados. O resultado da consulta será exibido em uma tabela semelhante à da Exibição de fonte de dados. Nota: a tabela exibida é apenas temporária.

Chaves na consulta na exibição de design

Chave

Função

F4

Visualização

F5

Executar consulta

F7

Adicionar tabela ou consulta


Procurar

Quando se abre o design da consulta pela primeira vez, vê-se uma caixa de diálogo para primeiro selecionar a tabela ou consulta que será a base para sua nova consulta.

Clique duas vezes nos campos para adicioná-los à consulta. Arraste e solte para definir as relações.

Ícone Nota

Ao projetar uma consulta, você não poderá modificar as tabelas selecionadas.


Remover tabelas

Para remover a tabela da exibição de design, clique na borda superior da janela da tabela e exiba o menu de contexto. Use o comando Excluir para remover a tabela da exibição de design. Outra opção é pressionar a tecla Delete.

Mover tabela e modificar tamanho de tabela

Você pode redimensionar e dispor as tabelas de acordo com as suas preferências. Para mover as tabelas, arraste a borda superior até a posição desejada. Amplie ou reduza o tamanho no qual a tabela será exibida, posicionando o cursor do mouse sobre uma borda ou sobre um canto e arrastando a tabela até o tamanho desejado.

Relações de tabela

Se houver relações de dados entre um nome de campo em uma tabela e um nome de campo em outra, você poderá usar essas relações para a sua consulta.

Se, por exemplo, tiver uma planilha para artigos identificados por um número de artigo e uma planilha para clientes em que registre todos os artigos que um cliente encomende usando os números de artigo correspondentes, é porque há uma relação entre os dois campos de dados de "número do artigo". Se agora quiser criar uma consulta que retorne todos os artigos que um cliente tenha encomendado, deverá recuperar os dados das duas planilhas. Para fazer isso, é preciso informar o LibreOfficeDev sobre a relação que existe entre os dados das duas planilhas.

Para fazer isso, clique em um nome de campo em uma tabela (por exemplo, o nome de campo "Item-Number" (Número do item) da tabela Customer (Cliente), mantenha pressionado o botão do mouse e, em seguida, arraste o nome do campo para o nome do campo da outra tabela ("Item-Number" - Número do item) da tabela Item). Quando soltar o botão do mouse, aparecerá uma linha conectando os dois campos nas duas janelas. A condição correspondente de o conteúdo dos dois nomes de campos ser idêntico é inserida na consulta SQL resultante.

A criação de uma consulta baseada em várias planilhas relacionadas só é possível quando se usa o LibreOfficeDev como interface de um banco de dados relacional.

Ícone Nota

Não é possível acessar tabelas de diferentes bancos de dados em uma consulta. As consultas que envolvem várias tabelas somente poderão ser criadas dentro de um banco de dados.


Especificar o tipo de relação

Se clicar duas vezes na linha que conecta dois campos vinculados ou chamar o comando de menu Inserir - Nova relação, poderá especificar o tipo de relação na caixa de diálogo Relações.

Como alternativa, pressione Tab até que linha seja selecionada, e pressione Shift+F10 para exibir o menu de contexto e escolher o comando Editar. Alguns bancos de dados suportam somente um subconjunto de tipos de JOIN possíveis.

Exclusão de relações

Para excluir uma relação entre duas tabelas, clique na linha de conexão e, em seguida, pressione a tecla Delete.

Como alternativa, exclua as entradas correspondentes em Campos envolvidos na caixa de diálogo Relações. Ou, pressione Tab até que o vetor de conexão seja realçado e, em seguida, pressione Shift+F10 para abrir o menu de contexto e selecione o comando Excluir.

Definir a consulta

Selecione condições para definir a consulta. Cada coluna da tabela de design aceita um campo de dados para a consulta. As condições em uma linha são vinculadas com um E lógico.

Especificar o nome dos campos

Primeiro, selecione todos os nomes de campo a partir das tabelas que deseja adicionar à consulta. É possível fazer isso com o recurso de arrastar e soltar ou por meio de um clique duplo em um nome de campo na janela da tabela. Com o método de arrastar e soltar, use o mouse para arrastar um nome de campo da janela da tabela até a área inferior da janela de edição da consulta. Ao fazer isso, você pode decidir qual coluna na janela de edição de consulta vai receber o campo selecionado. Um nome de campo pode ser selecionado com um clique duplo. Ele será adicionado à próxima coluna livre na janela de edição de consulta.

Exclusão de nomes de campos

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

Salvar a consulta

Use o ícone Salvar na barra Padrão para salvar a consulta. Você verá uma caixa de diálogo que solicita um nome para a consulta. Se o banco de dados suportar esquemas, você também poderá inserir um nome para o esquema.

Esquema

Insira o nome do esquema que é atribuído à consulta ou à exibição de tabelas.

Nome da consulta ou nome de exibição da tabela

Insira o nome da consulta ou da exibição de tabelas.

Filtragem de dados

Para filtrar dados para a consulta, defina os critérios desejados na área inferior da janela de edição de consulta. As seguintes opções estão disponíveis:

Campo

Digite o nome do campo de dados referido na consulta. Todas as configurações feitas nas linhas das opções do filtro referem-se a este campo. Se ativar uma célula com um clique do mouse, verá um botão de seta, o que permitirá a seleção de um campo. A opção "Nome da tabela.*" seleciona todos os campos de dados onde o critério especificado será aplicado para todos os campos da tabela.

Alias

Especifica um alias. Esse alias será listado numa consulta, no lugar do nome de campo. Isso possibilita o uso de rótulos de coluna definidos pelo usuário. Por exemplo, se o nome do campo de dados for PtNo e, em vez desse nome, você quiser que apareça PartNum na consulta, insira PartNum como alias.

Numa instrução SQL, os alias são definidos da seguinte maneira:

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érios

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

Nenhuma função será executada.

Média

AVG

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

Contagem

COUNT

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

a) COUNT(*): Para contar todos os registros da tabela, passe um asterisco como argumento.

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.


Você também pode inserir 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 é:

SELECT SUM("Price") FROM "Article".

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 em uma instrução SQL é:

SELECT FUNCTION() AS alias FROM table

Exemplo:

SELECT COUNT(*) AS count FROM "Item"

Ícone 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 a seguir, é executada uma consulta entre duas tabelas: uma tabela "Item" com o campo "Item_No" (No. do item), e uma tabela "Suppliers" (Fornecedores) com o campo "Supplier_Name" (Nome do fornecedor). Além disso, ambas as tabelas possuem um nome de campo comum, "Supplier_No."

As etapas a seguir são necessárias para a criação de uma consulta que contenha todos os fornecedores que entreguem mais de três itens.

  1. Insira as tabelas "Item" e "Suppliers" (Fornecedores) no design de consulta.

  2. Vincule os campos "Supplier_No" das duas tabelas se ainda não houver uma relação desse 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. Insira >3 como 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) estiverem presentes na tabela "Item", você poderá obter o preço médio do item oferecido por um fornecedor com a seguinte consulta:

  1. Insira 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 no campo de preço.

  4. Você também pode inserir "Média" na linha do nome do alias (sem aspas).

  5. Para o campo "Supplier_No" (No. do fornecedor), escolha Agrupar.

  6. Execute a consulta.

Os seguintes símbolos e comandos de menu de contexto estão disponíveis:

Funções

Shows or hides a row for the selection of functions.

Nome da tabela

Mostra ou oculta a linha do nome da tabela.

Nome do alias

Mostra ou oculta a linha do nome do alias.

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 houver várias ocorrências do nome "Silva" no banco de dados de endereços, você poderá escolher o comando Valores distintos para especificar na consulta que o nome "Silva" ocorrerá apenas uma vez.

Para uma consulta que envolva vários campos, a combinação de valores de todos os campos deve ser exclusiva, de modo que o resultado possa ser formado a partir de um registro específico. Por exemplo, você tem "Smith em Chicago" uma vez no catálogo de endereços e "Smith em Londres" duas vezes. Com o comando Valores distintos, a consulta usará os dois campos, "last name" (sobrenome) e "city" (cidade), e retornará o resultado "Smith em Chicago" uma vez e "Smith em Londres" uma vez.

No SQL, esse 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.

Formulação de 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

Condição será satisfeita se...

=

igual a

... o conteúdo do campo for idêntico ao da 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.

<>

não é igual a

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

>

maior que

... o conteúdo do campo for maior que o da expressão especificada.

<

menor que

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

>=

maior que ou igual a

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

<=

menor que ou igual a

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


Comando do LibreOfficeDev

Comando SQL

Significado

Condição será satisfeita se...

IS EMPTY

IS NULL

é nulo

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

cai no 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 se encaixa no 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 o valor a, b, c...

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

= TRUE

= TRUE

tem o valor True (Verdadeiro)

... o nome do campo tiver o valor True (Verdadeiro).

= FALSE

= FALSE

tem o valor falso

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


Exemplos

='Ms.'

retorna os nomes de campo que contêm "Ms."

<'2001-01-10'

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


Sequência Escape Like : {escape 'escape-character'}

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

O exemplo fornecerá todas as entradas onde o nome do item começa com 'The *'. Isso significa que você também pode procurar caracteres que seriam interpretados como espaços reservados, tais como *, ?, _, % ou o ponto.

Sequência Escape: Outer Join: {oj outer-join}

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

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

Consulta a 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 Data

A sintaxe SQL Escape #1 - pode estar obsoleta

Sintaxe SQL Escape #2

Sintaxe de 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]'

Data/Hora

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

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

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


Exemplo: selecione {d '1999-12-31'} a partir de 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.)

Consulta a campos Yes/No (Sim/Não)

Para consultar campos Yes/No (Sim/Não), use a seguinte sintaxe para as tabelas dBASE:

Status

Critério de consulta

Exemplo

Sim

para tabelas do dBASE: diferente de qualquer valor fornecido

=1 retorna todos os registros em que o campo Yes/No (Sim/Não) possui o status "Yes" (Sim) ou "On" (Ativado) (selecionados em preto),

Não

.

=0 retorna todos os registros para os quais o campo Yes/No (Sim/Não) possua o status "No" (Não) ou "Off" (Desativado) (sem seleção).

Nulo

IS NULL

IS NULL retorna todos os registros para os quais o campo Yes/No (Sim/Não) não possua os estados Yes ou No (Sim ou Não) (selecionados em cinza).


Ícone Nota

A sintaxe depende do sistema de banco de dados usado. Você também deve observar que os campos Yes/No (Sim/Não) podem ser definidos de forma diferente (somente dois estados, em vez de três).


Consultas com parâmetros

As consultas parametrizadas permite ao usuário inserir valores durante a execução. Estes valores são utilizados nos critérios para selecionar os registros a serem exibidos. Cada valor tem um parâmetro associado a ele, que solicitado ao usuário quando a consulta é executada.

Os nomes de parâmetros são precedidos por dois pontos tanto nas vistas de Design quanto SQL de uma consulta. Isso pode utilizado sempre quando um valor pode aparecer. Se o mesmo valor deve aparecer mais de uma vez na consulta, o mesmo nome de parâmetros é utilizado.

No caso mais simples, onde o usuário insere um valor que verifica uma igualdade, o nome do parâmetro precedido de dois pontos é inserido na linha do Critério. No modo SQL isto deve ser digitado como WHERE "Campo" = :Nome_do_parametro

Ícone Aviso

Os nomes de parâmetros não devem conter qualquer dos caracteres <space>`!"$%^*()+={}[]@'~#<>?/,. Não podem ter os mesmos nomes de campos ou palavras reservadas do SQL. Eles podem ter os mesmos nomes que os alias.


Ícone Dica

Uma construção útil para selecionar registros com base em partes do conteúdo de campos de texto é adicionar colunas ocultas com LIKE '%/'||:Parte_do_campo || '%'" como critério. Isso vai selecionar registros com correspondência exata. Se desejar um teste insensível à caixa, uma solução é usar LOWER (Nome_do_campo) como campo e LIKE LOWER ( '%' || :Parte _do_campo || '%' ) como critério. Observe que os espaços no critério são importantes; se forem deixados de fora, o analisador SQL interpreta o critério inteiro como cadeia a corresponder. No modo SQL isso deveria ser digitados como LOWER ( "Nome_do_campo" ) LIKE LOWER ( '%' || :Parte_do_campo || '%' ).


Consultas parametrizadas podem ser utilizadas como fonte de dados para subformulários, para permitir ao usuário restringir os registros exibidos.

Entrada de parâmetros

A caixa de diálogo Entrada de parâmetros pede ao usuário para inserir os valores dos parâmetros. Insira um valor para cada parâmetro de consulta e confirme clicando em OK ou pressionando Enter.

Os valores inseridos pelo usuário podem consistir de qualquer caracteres permitidos pelo SQL para o critério relevante; isto pode depender do sistema de banco de dados utilizado.

Ícone Dica

O usuário pode utilizar os caracteres curinga do SQL "%" (cadeia arbitrária) ou "_" (caractere arbitrário singular) como parte do valor para buscar registros com critérios mais complexos.


Modo SQL

SQL significa "Structured Query Language" e descreve instruções para atualização e administração de bancos 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.