Concepção de consultas

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

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.

Ao selecionar o comando Criar vista no separador Tabelas em documento de uma 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 com uma vista criada.

Vista de design

Para criar uma consulta, clique no ícone Consultas no documento e, em seguida, clique em Criar consulta no modo de design.

O painel inferior da Vista de Design é onde se define a consulta. Para definir uma consulta, especifique os nomes dos campos da base de dados a incluir e os critérios para a exibição dos campos. Para reorganizar as colunas no painel inferior da Vista de Design, arraste um cabeçalho de coluna para uma nova localização ou selecione a coluna e pressione +tecla de seta.

Na parte superior da janela da vista de design, são mostrados os ícones da barra Design de consulta e da barra Design.

Se quiser testar uma consulta. clique duas vezes no nome da consulta. O resultado é mostrado em formato de tabela semelhante à Vista de origem de dados. Nota: a tabela apenas é mostrada temporariamente.

Chaves na vista de design de uma consulta

Explorar

Ao abrir o design de consulta pela primeira vez, surgirá uma caixa de diálogo na qual deverá escolher a tabela ou consulta que servirá de base à sua consulta.

Clique duas vezes nos campos para os adicionr à consulta. Arraste e solte para definir as relações.

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

Remover tabelas

Para remover uma 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. Outra possibilidade é premir a tecla Delete.

Mover tabelas e alterar o tamanho de tabelas

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 de tabela

Se as relações de dados entre um nome de campo de uma tabela e um nome de campo de outra tabela, pode utilizar as relações na sua consulta.

Se, por exemplo, tiver uma folha de cálculo para artigos identificados por um número de artigo e uma folha de cálculo para clientes, na qual regista de todos os artigos encomendados por um cliente utilizando os números de artigo correspondentes, existe uma relação entre os dois campos de dados "número de artigo". Se pretender agora criar uma consulta que tenha como resultado todos os artigos que um cliente tenha encomendado, deverá obter os dados a partir de duas folhas de cálculo. Para tal, deverá comunicar ao LibreOfficeDev qual a relação existente entre os dados nas duas folhas de cálculo.

Para executar este procedimento, clique num nome de campo numa tabela (por exemplo, o nome do campo "Artigo-Número" da tabela Cliente), continue a premir o botão do rato e, em seguida, arraste o nome de campo para o nome de campo da outra tabela ("Artigo-Número" da tabela Artigo). Assim que soltar o botão do rato, surge uma linha que estabelece uma ligação entre os dois campos nas duas janelas. A condição correspondente de que o conteúdo dos dois nomes dos campos sejam idênticos é introduzida na consulta SQL resultante.

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.

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.

Especificar o tipo de relação

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

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.

Definir uma consulta

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.

Especificar nome de campos

Em primeiro lugar, selecione todos os nomes de campos das tabelas que pretender adicionar à consulta. Pode fazê-lo pelo processo arrastar e largar, ou clicando duas vezes num nome de um campo na janela da tabela. Com o método arrastar e largar, utilize o rato para arrastar um nome de um campo a partir da janela da tabela até à área inferior do design de consulta. Enquanto faz isto, pode decidir a qual coluna pretende adicionar o campo. Selecione um nome de um campo clicando duas vezes sobre o mesmo. Este será adicionado à próxima coluna que esteja livre.

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.

Guardar uma consulta

Utilize o ícone Guardar na barra Padrão para guardar a consulta. Verá uma caixa de diálogo a solicitar a introdução de um nome para a consulta. Se a base de dados suportar esquemas, também é possível introduzir um nome de esquema.

Esquema

Introduza o nome do esquema que foi 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

Para filtrar dados para a consulta, defina as preferências na área inferior da janela do design de consulta. Estão disponíveis as seguintes opções:

Campo

Introduza o nome do campo de dados ao qual se refere na Consulta. Todas as definições estabelecidas nas linhas inferiores referem-se a este campo. Se ativar uma célula através de um clique do rato, irá visualizar um botão de seta que permite selecionar um campo. A opção "Table name.*" seleciona todos os campos de dados e os critérios são válidos para todos os campos das tabelas.

Nome alternativo

Especifica um nome alternativo. Este nome alternativo será listado numa consulta em vez do nome do campo. Este procedimento faz com que seja possível utilizar etiquetas de coluna definidas pelo utilizador. Por exemplo, se o campo de dados tiver o nome PtNo e, em vez desse nome, preferir que apareça PartNum na consulta, introduza PartNum como nome alternativo.

Numa instrução SQL, os nomes alternativos são definidos da seguinte forma:

SELECT coluna AS alias FROM tabela.

Por exemplo:

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

Tabela

A tabela da base de dados correspondente do campo de dados selecionado é aqui listada. Se ativar a célula com um clique do rato, irá surgir uma seta que lhe permite selecionar outra tabela da consulta atual.

Ordenação

Ao clicar nesta célula, pode selecionar as opções de ordenação: ascendente, descendente e sem ordenação. Os campos de textos serão ordenados alfabeticamente, e os campos numéricos serão ordenados numericamente. Na maior parte das bases de dados, os administradores podem definir as opções de ordenação.

Visível

Se assinalar a propriedade Visível para um campo de dados, esse campo estará visível na consulta. Se utilizar apenas um campo de dados para formular uma condição, não é obrigatório que este seja visualizado.

Critérios

Especifica um primeiro critério pelo qual o conteúdo do campo de dados deve ser filtrado.

ou

Pode inserir aqui um critério adicional para filtragem em cada linha. Critérios múltiplos numa única coluna serão interpretados como um OU booleano.

Também pode utilizar o menu de contexto dos cabeçalhos de linha na área inferior do design de consulta para inserir um filtro para as funções:

Funções

As funções disponíveis aqui dependem daquelas fornecidas pelo motor do banco de dados.

Se estiver a trabalhar com a base de dados HSQL incorporada, a caixa de lista na linha Funções disponibiliza-lhe as seguintes opções:

Também é possível invocar uma 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("Preço") FROM "Artigo".

Com exceção da função Agrupar, as funções acima são denominadas Funções agregadas. Estas funções calculam dados para criar resumos a partir dos resultados. Também podem estar disponíveis funções adicionais que não são apresentadas na caixa de lista. Estas dependem do sistema de base de dados específico em utilização e do estado atual do controlador do Base.

Para utilizar uma função que não esteja listada, tem de as introduzir manualmente em Campo.

Também é possível atribuir nomes alternativos a chamadas de função. Se não pretender que a consulta seja visualizada no cabeçalho da coluna, introduza o nome pretendido em Nome alternativo.

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

SELECT FUNCTION() AS alias FROM tabela

Exemplo:

SELECT COUNT(*) AS contagem FROM "Item"

Se executar esta função, não poderá inserir colunas adicionais para a consulta, mas apenas recebê-las como uma função de "Agrupar".

Exemplos

No exemplo seguinte, é executada uma consulta em duas tabelas: uma tabela «Item» com o campo «Item_No» e uma tabela «Fornecedores» com o campo «Nome_Fornecedor». Além disso, ambas as tabelas têm um campo comum denominado «N.º_Fornecedor».

Para criar uma consulta que inclua todos os fornecedores que entregam mais de três artigos, é necessário seguir os passos a seguir.

1. Insira as tabelas «Artigo» e «Fornecedores» no desenho da consulta.

2. Ligue os campos «Supplier_No» das duas tabelas, caso ainda não exista uma relação deste tipo.

3. Clicar duas vezes no campo "Item_No" da tabela "Item". Para mostrar a linha Função, utilize o menu de contexto e selecione a função Contar.

4. Introduza >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) existirem na tabela «Item», é possível obter o preço médio do artigo fornecido por um determinado fornecedor através da seguinte consulta:

1. Insira a tabela «Item» no desenho da consulta.

2. Clique duas vezes nos campos «Preço» e «N.º do fornecedor».

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

4. Também pode introduzir «Average» na linha do nome do alias (sem aspas).

5. Selecione «Grupo» para o campo «Supplier_No».

6. Execute a consulta.

Estão disponíveis os seguintes comandos e ícones do menu de contexto:

Funções

Mostra ou oculta uma linha para a seleção de funções.

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

Recupera apenas valores distintos da consulta. Isto aplica-se a vários registos que possam conter várias ocorrências repetidas de dados nos campos selecionados. Se o comando Valores Distintos estiver ativo, deverá ver apenas um registo na consulta (DISTINCT). Caso contrário, verá todos os registos correspondentes aos critérios da consulta (ALL).

Por exemplo, se o nome «Smith» aparecer várias vezes na sua base de dados de endereços, pode selecionar o comando Valores distintos para especificar na consulta que o nome «Smith» só aparecerá uma vez.

Numa consulta que envolva vários campos, a combinação de valores de todos os campos deve ser única, para que o resultado possa ser obtido a partir de um registo específico. Por exemplo, tem «Smith em Chicago» uma vez na sua agenda de contactos e «Smith em Londres» duas vezes. Com o comando Valores Distintos, a consulta utilizará os dois campos «apelido» e «cidade» e apresentará o resultado da consulta «Smith em Chicago» uma vez e «Smith em Londres» uma vez.

Em SQL, este comando corresponde ao predicado DISTINCT.

Limite

Permite-lhe limitar o número máximo de registos devolvido por uma consulta.

Se for adicionada uma construção Limit, serão apresentadas, no máximo, tantas linhas quantas as especificadas. Caso contrário, serão apresentados todos os registos que correspondam aos critérios da consulta.

Definição de condições de filtragem

Ao definir condições de filtragem, tem à sua disposição vários operadores e comandos. Para além dos operadores relacionais, existem comandos específicos de SQL que consultam o conteúdo dos campos da base de dados. Se utilizar estes comandos na sintaxe LibreOfficeDev, o LibreOfficeDev converte-os automaticamente para a sintaxe SQL correspondente através de um analisador interno. Também pode introduzir o comando SQL diretamente e ignorar o analisador interno. As tabelas seguintes apresentam uma visão geral dos operadores e comandos:

Exemplos

Como Sequência de escape: {escape 'caractere-de-escape'}

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

Este exemplo irá apresentar-lhe todas as entradas cujo nome do item comece por «The *». Isto significa que também pode pesquisar caracteres que, de outra forma, seriam interpretados como caracteres de substituição, tais como *, ?, _, % ou o ponto.

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

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

Consultar campos de texto

Para pesquisar o conteúdo de um campo de texto, deve colocar a expressão entre aspas simples. A distinção entre maiúsculas e minúsculas depende da base de dados utilizada. Por definição, o operador LIKE distingue maiúsculas de minúsculas (embora algumas bases de dados não interpretem isto de forma estrita).

Consultar campos de data

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

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

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

Todas as expressões de data (literais de data) devem ser colocadas entre aspas simples. (Consulte a documentação de referência do banco de dados e do conector específicos que está a utilizar para obter mais detalhes.)

Consultar campos de resposta sim/não

Para consultar campos do tipo Sim/Não, utilize a seguinte sintaxe nas tabelas dBASE:

A sintaxe depende do sistema de base de dados utilizado. Deve também ter em conta 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 com parâmetros permitem ao utilizador introduzir valores durante a execução. Esses valores são utilizados nos critérios de seleção dos registos a apresentar. Cada um desses valores tem um nome de parâmetro associado, que é utilizado para solicitar a introdução de dados ao utilizador quando a consulta é executada.

Os nomes dos parâmetros são precedidos por dois pontos tanto na vista de Design como na vista SQL de uma consulta. Isto pode ser utilizado sempre que for necessário introduzir um valor. Se for necessário que o mesmo valor apareça mais do que uma vez na consulta, utiliza-se o mesmo nome de parâmetro.

No caso mais simples, em que o utilizador introduz um valor que é comparado por igualdade, o nome do parâmetro, precedido de dois pontos, é simplesmente introduzido na linha «Critério». No modo SQL, isto deve ser escrito da seguinte forma: WHERE "Field" = :Parameter_name

Os nomes dos parâmetros não podem conter nenhum dos seguintes caracteres: <espaço>`!"$%^*()+={}[]@'~#<>?/,. Não podem ser iguais aos nomes dos campos nem a palavras reservadas do SQL. Podem ser iguais aos aliases.

Uma forma útil de selecionar registos com base em partes do conteúdo de um campo de texto consiste em adicionar uma coluna oculta com "LIKE '%' || :Part_of_field || '%'" como critério. Isto irá selecionar registos com uma correspondência exata. Se for desejado um teste que não distinga maiúsculas de minúsculas, uma solução é utilizar LOWER (Field_Name) como campo e LIKE LOWER ( '%' || :Part_of_field || '%' ) como critério. Note que os espaços no critério são importantes; se forem omitidos, o analisador SQL interpreta todo o critério como uma cadeia de caracteres a ser comparada. No modo SQL, isto deve ser escrito como LOWER ( "Field_Name" ) LIKE LOWER ( '%' || :Part_of_field || '%' ).

As consultas de parâmetros podem ser utilizadas como fonte de dados para subformulários, para permitir que o utilizador restrinja os registos apresentados.

Introdução de parâmetros

A caixa de diálogo Introdução de parâmetros solicita ao utilizador que introduza os valores dos parâmetros. Introduza um valor para cada parâmetro da consulta e confirme clicando em OK ou premindo a tecla Enter.

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

O utilizador pode utilizar os caracteres curinga SQL «%» (cadeia de caracteres arbitrária) ou «_» (caractere único arbitrário) como parte do valor para recuperar registos com critérios mais complexos.

Modo SQL

SQL significa «Structured Query Language» (Linguagem de Consulta Estruturada) e descreve instruções para atualizar e administrar bases de dados relacionais.

No LibreOfficeDev, não é necessário ter conhecimentos de SQL para a maioria das consultas, uma vez que não é preciso introduzir o código SQL. Se criar uma consulta no designer de consultas, o LibreOfficeDev converte automaticamente as suas instruções na sintaxe SQL correspondente. Se, com a ajuda do botão Ativar/Desativar Vista de Design, mudar para a vista SQL, poderá ver os comandos SQL de uma consulta que já tenha sido criada.

Pode formular a sua consulta diretamente em código SQL. Note, no entanto, que a sintaxe específica depende do sistema de base de dados que utilizar.

Se introduzir o código SQL manualmente, pode criar consultas específicas de SQL que não são suportadas pela interface gráfica no Query Designer. Estas consultas devem ser executadas no modo SQL nativo.

Ao clicar no ícone Executar comando SQL diretamente na vista SQL, pode formular uma consulta que não é processada pelo LibreOfficeDev e é enviada diretamente para o motor de base de dados.