Design de consulta
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 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.
O painel inferior da Vista de design é o local onde o utilizador define a consulta. Para definir uma consulta, especifique os nomes de campos da base dados a incluir e os critérios para apresentar os campos. Para reordenar as colunas do painel inferior da Vista de desenho, arraste um cabeçalho de coluna para uma nova localização, ou selecione a coluna e prima ComandoCtrl+tecla de seta.
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.
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.
Se, por exemplo, tiver uma folha de cálculo para artigos identificados por números e uma folha de cálculo para clientes, na qual regista de todos os artigos encomendados por um cliente através dos números de artigo correspondentes, existe uma relação entre os dois campos de dados "número de artigo". Se quiser 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 LibreOffice qual a relação existente entre os dados nas duas folhas de cálculo.
Para executar este procedimento, clique num nome de campo de uma tabela (por exemplo, o nome do campo "Artigo-Número" da tabela Clientes), 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 Artigos). 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 LibreOffice 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 tipo de ligação
Se clicar duas vezes na linha que liga dois campos ou se invocar o comando de menu Inserir - Nova relação, poderá especificar o tipo de ligaçã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 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 do campo
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 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 aceitar esquemas, também é possível introduzir um esquema.
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
Para filtrar dados para a consulta, defina as preferências na área inferior da vista de design. Estão disponíveis as seguintes linhas:
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 column AS alias FROM table.
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 permite selecionar outra tabela da consulta atual.
Ordenar
Ao clicar na 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ério
Especifica os critérios pelos quais deve ser filtrado o conteúdo dos campos de dados.
ou
Aqui é possível introduzir um critério adicional para filtragem em cada linha. A ligação entre os critérios múltiplos numa coluna será estabelecida através de uma ligação OR.
Também pode utilizar o menu de contexto dos cabeçalhos de linha na área inferior do design de consulta, para inserir uma linha para as funções:
Funções
As funções que aqui podem ser executadas dependem da base de dados.
Se estiver a trabalhar com a base de dados HSQL , a caixa de lista na linha Funções põe à disposição as seguintes opções:
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 |
CONTAR |
Determina o número de registos na tabela. Os campos em branco podem ser contados (a) ou não (b). a) CONTAR(*): Passar um asterisco como o argumento, faz a contagem de todos os registos na tabela. b) CONTAR(coluna): Passar um nome de campo como argumento, conta apenas campos cujo nome em questão contenha um valor. Os valores nulos (campos em branco) não serão contados. |
Máximo |
MAX |
Determina o valor máximo de um campo. |
Mínimo |
MIN |
Determina o valor mínimo de um campo. |
Soma |
SOMA |
Calcula a soma dos valores de campos associados. |
Agrupar |
AGRUPAR POR |
Agrupa os dados de consulta de acordo com o nome do campo selecionado. As funções são executadas de acordo com os grupos especificados. Em SQL, esta opção corresponde à expressão GROUP BY. Se for adicionado um critério, esta entrada surge em SQL HAVING. |
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".
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 outras funções que não as apresentadas na caixa de lista, tem de as introduzir 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 count 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 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.
-
Inserir as tabelas "Item" e "Fornecedores" no design de consulta.
-
Ligue os campos "Supplier_No" das duas tabelas se ainda não existir uma relação deste tipo.
-
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.
-
Introduza >3 como um critério e desative o campo Visível.
-
Clique duas vezes no campo "Supplier_Name" da tabela "Suppliers" e escolha a função Agrupar.
-
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:
-
Inserir a tabela "Item" no design de consulta.
-
Clique duas vezes nos campos "Price" e "Supplier_No".
-
Ative a linha Função e selecione a função Média do campo "Preço".
-
Também é possível introduzir "Média" na linha como nome alternativo (sem aspas).
-
Escolha Grupo para o campo "Supplier_No".
-
Execute a consulta.
Estão disponíveis os seguintes comandos e símbolos no 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
Aplica apenas valores unívocos à consulta. Isto aplica-se a registos que contenham dados que surjam várias vezes nos campos selecionados. Se o comando Valores distintos estiver ativo, visualizará apenas um registo na consulta (DISTINCT). De outra forma, visualizará todos os registos que correspondam aos critérios da consulta (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
Permite-lhe maximizar o número de registos devolvidos pela consulta.
Se adicionar um Limite, irá obter, no máximo, o número de linhas que especificar. Se não utilizar o limite, serão obtidos todos os registos que coincidam com o critério da consulta.
Formular condições de filtro
Ao criar condições de filtro, pode encontrar vários operadores e comandos à sua disposição. Para além dos operadores relacionais, existem comandos específicos de SQL que efetuam a consulta do conteúdo dos campos de base de dados. Se utilizar estes comandos na sintaxe do LibreOffice , o LibreOffice converte automaticamente as instruções para a sintaxe SQL correspondente. Também é possível introduzir o comando SQL diretamente. As tabelas que se seguem proporcionam um resumo dos operadores e comandos:
Operador |
Significado |
A condição é satisfeita se... |
= |
é igual a |
... o conteúdo do campo é idêntico à expressão indicada. o operador = não será exibido nos campos da consulta. Se tiver introduzido um valor sem qualquer operador, o operador = será adotado automaticamente. |
<> |
é 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. |
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' |
devolve nomes de campos com o conteúdo do tipo "give" e "gave". |
LIKE 'S*' |
devolve os campos de dados com conteúdo de campo como "Sun". |
ENTRE 10 E 20 |
devolve nomes de campos com conteúdo de campo com valores entre 10 e 20. (Os campos podem ser campos de texto ou campos numéricos). |
IN (1; 3; 5; 7) |
devolve nomes de campos com os valores 1, 3, 5, 7. Se o nome do campo contiver, por exemplo, um número de item, pode criar uma consulta que resulte no número especificado. |
NOT IN ('Smith') |
devolve nomes de campos que não contenham "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
Para consultar o conteúdo de um campo de texto, tem que colocar a expressão entre plicas. A distinção entre letras maiúsculas e minúsculas depende do tipo de base de dados utilizada. LIKE, por definição, é sensível a maiúsculas e minúsculas (apesar de algumas bases de dados não o considerarem de forma tão rigorosa).
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'} |
'AAAA-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 'AAAA-MM-DD HH:MI:SS[.SS]'} |
'AAAA-MM-DD HH:MI:SS[.SS]' |
Exemplo: select {d '1999-12-31'} from world.years
Exemplo: select * from mytable where years='1999-12-31'
Todas as expressões de data (literais) devem ser envolvidas por plicas. (Consulte o manual de referência para uma base de dados ou conector particular, caso os esteja a utilizar)
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). |
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
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.
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.
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.
No LibreOffice, não é necessário ter conhecimentos de SQL para a maioria das consultas, uma vez que não é necessário introduzir o código SQL. Se criar uma consulta no design de consulta, o LibreOffice converte automaticamente as instruções para a sintaxe SQL correspondente. Se, com a ajuda do botão Ativar/desativar vista de design, alterar a vista SQL, poderá visualizar os comandos SQL para uma consulta que tenha sido anteriormente criada.
É possível formular a sua consulta diretamente no código SQL. Contudo, tenha em consideração que a sintaxe especial está dependente do sistema de base de dados que utilizar.
Se introduzir o código SQL manualmente, poderá criar consultas específicas de SQL que não sejam permitidas pela interface gráfica Design de consulta. Estas consultas têm que ser executadas no modo SQL nativo.
Ao clicar no ícone Executar comandos SQL diretamente na vista SQL, poderá formular uma consulta que não seja processada pelo LibreOffice.