Disseny de la consulta

La Vista del disseny de la consulta us permet crear i editar una consulta de la base de dades.

Per a accedir a aquesta ordre...

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


Icona de nota

La majoria de les bases de dades utilitzen consultes per a filtrar o ordenar les taules per a la visualització de registres en un ordinador. Les vistes ofereixen les mateixes funcionalitats que les consultes, però des d'un servidor. Si la vostra base de dades és en un servidor que permet l'ús de vistes, les podreu utilitzar per a filtrar els registres en el servidor i, d'aquesta manera, accelerar-ne el temps de visualització.


Icona de nota

Si seleccioneu l'ordre Crea una vista de la pestanya Taules en un document de base de dades, visualitzareu la finestra Disseny de la vista, que és molt semblant a la finestra Disseny de la consulta que es descriu aquí.


La disposició de la finestra Disseny de la consulta s'emmagatzema amb una consulta creada, però en canvi no es pot emmagatzemar amb una vista creada.

La vista de disseny

Per a crear una consulta, feu clic a la icona Consultes d'un document de base de dades, i a continuació a Crea una consulta en vista de disseny.

La subfinestra que hi ha a la part inferior de la Vista de disseny és l'espai on heu de definir la consulta. Per a definir una consulta, indiqueu els noms dels camps de la base de dades que vulgueu incloure i els criteris per a la visualització d'aquests camps. Si voleu reorganitzar les columnes d'aquesta subfinestra, arrossegueu una capçalera de columna fins a una nova posició o seleccioneu la columna i premeu +tecla de cursor.

Les icones de les barres Disseny de la consulta i Disseny es mostren a la part superior de la finestra Vista de disseny de la consulta.

Si voleu provar el funcionament d'una consulta, feu doble clic al nom d'aquesta consulta en el document de base de dades. El resultat de la consulta es mostrarà en una taula semblant a la Vista de fonts de dades. Nota: la taula que es mostrarà serà temporal.

Tecles per a la vista del disseny de la consulta

Tecla

Funció

F4

Previsualització

F5

Execució de la consulta

F7

Addició d'una taula o una consulta


Navega

En obrir el disseny de consultes per primera vegada, veureu un diàleg al qual haureu de seleccionar la taula o la consulta que s'utilitzarà per a la consulta nova.

Feu doble clic als camps que vulgueu per a afegir-los a la consulta. Arrossegueu i deixeu anar per a definir relacions.

Icona de nota

Quan estigueu dissenyant una consulta no podreu modificar les taules seleccionades.


Supressió de taules

Per suprimir una taula de la Vista de disseny, feu clic a la vora superior de la finestra de la taula i obriu el menú contextual, on podreu triar l'ordre Suprimeix. De manera alternativa, podeu prémer el botó Supr.

Moure taules i modificar-ne la mida

Podeu redimensionar i organitzar les taules segons les vostres preferències. Per moure una taula, arrossegueu-ne la vora superior fins a la posició que vulgueu. Podeu augmentar o reduir la mida d'una taula col·locant la busca del ratolí en una vora o una cantonada de la taula i arrossegant-lo fins aconseguir la mida desitjada.

Relacions entre taules

En cas que hi hagi dades relacionades entre el nom de camp d'una taula i el nom de camp d'una altra taula, podreu utilitzar aquestes relacions per a la vostra consulta.

Així, si per exemple disposeu, d'una banda, d'un full de càlcul per a articles identificats amb un número d'article i, de l'altra, d'un altre full de càlcul per a clients en el qual registreu tots els articles que aquests clients sol·liciten mitjançant els números d'article corresponents, hi haurà una relació entre els dos camps de dades «número d'article». D'aquesta manera, si voleu crear una consulta que torni tots els articles sol·licitats per un client, haureu de recuperar les dades corresponents dels dos fulls de dades. Per a fer-ho, haureu d'informar el LibreOffice sobre la relació que existeix entre les dades dels dos fulls de càlcul.

Per a indicar aquesta relació, feu clic al nom d'un camp d'una taula (per exemple, al nom del camp «Número-article» de la taula Clients), manteniu premut el botó del ratolí i arrossegueu el nom del camp fins a l'altra taula (fins a «Número-article» de la taula Articles). Quan deixeu anar el botó del ratolí, apareixerà una línia que connectarà els dos camps de les dues finestres. La condició corresponent, segons la qual els dos noms de camp hauran de ser idèntics, s'introduirà a la consulta SQL resultant.

Només podeu crear una consulta basada en diversos fulls relacionats si utilitzeu el LibreOffice com a interfície per a una base de dades relacional.

Icona de nota

No podeu accedir a taules de bases de dades diferents en una consulta. Només podeu crear consultes en què intervinguin diverses taules en una sola base de dades.


Especificació del tipus de relació

Si feu doble clic en la línia que connecta els dos camps enllaçats o crideu l'ordre de menú Insereix ▸ Relació nova s'obrirà el diàleg Relacions, que us permet especificar el tipus d'enllaç.

També podeu prémer la tecla Tab fins que la línia estigui seleccionada, i a continuació Maj+F10 per mostrar el menú contextual i triar-hi l'ordre Edita. Hi ha bases de dades que només admeten un subconjunt de tots els tipus d'unió possibles.

Supressió de relacions

Per suprimir una relació entre dues taules, feu clic a la línia de connexió i premeu la tecla Supr.

També podeu suprimir les entrades respectives a Camps implicats del diàleg Relacions, o bé podeu prémer la tecla Tab fins que el vector de connexió quedi realçat i, a continuació, Maj+F10 per a obrir el menú contextual i seleccionar-hi l'ordre Suprimeix.

Definició de la consulta

Seleccioneu les condicions per a definir la consulta. Cada columna de la taula de disseny accepta un camp de dades per a la consulta. Les condicions d'una fila s'enllacen amb el booleà AND.

Especificació dels noms dels camps

En primer lloc, seleccioneu tots els noms dels camps de les taules que vulgueu afegir a la consulta. Per a fer-ho, podeu arrossegar i deixar anar o bé fer doble clic a un nom de camp a la finestra de la taula. Si opteu pel primer mètode, utilitzeu el ratolí per a arrossegar un nom de camp des de la finestra de la taula fins a l'àrea inferior de la finestra de disseny de la consulta. D'aquesta manera, podreu decidir a quina columna voleu afegir el camp. Podeu seleccionar un nom de camp fent-hi doble clic. En aquest cas, s'afegirà a la columna lliure següent en la finestra de disseny de la consulta.

Supressió de noms de camp

Per suprimir un nom de camp de la consulta, feu clic a la capçalera de columna del camp i trieu l'ordre Suprimeix en el menú contextual que es mostrarà per a la columna.

Desament de la consulta

Utilitzeu la icona Desa de la barra Estàndard per a desar la consulta. Visualitzareu un diàleg que us sol·licitarà un nom per a la consulta. Si la base de dades admet esquemes, també en podreu indicar un.

Esquema

Introduïu el nom de l'esquema assignat a la consulta o a la vista de taula.

Nom de la consulta o de la vista de taula

Introduïu el nom de la consulta o de la vista de taula.

Filtratge de dades

Per a filtrar les dades per a la consulta, definiu els criteris desitjats a la finestra de disseny de la consulta. Disposareu de les opcions següents:

Camps

Introduïu el nom del camp de dades al qual es fa referència en la consulta. Totes les configuracions fetes en l'opció del filtre fan referència a aquest camp. Si activeu aquí una cel·la amb un clic del ratolí, veureu un botó de fletxa que us permet seleccionar un camp. L'opció «Nom de la taula.*» selecciona tots els camps de dades amb l'efecte què els criteris especificats s'aplicaran a tots els camps de la taula.

Àlies

Permet especificar un àlies. Aquest àlies es mostrarà a la consulta en comptes del nom del camp. Això fa possible utilitzar etiquetes de columna definides per l'usuari. Per exemple, si el camp de dades s'anomena «NmPt» i voleu que el nom «NúmPart» aparegui a la consulta, introduïu «NúmPart» com a àlies.

En una expressió SQL, els àlies es defineixen de la manera següent:

SELECT column AS alias FROM table.

Per exemple:

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

Taula

Aquí es llista la taula de la base de dades corresponent al camp de dades seleccionat. Si activeu aquesta cel·la de la taula fent-hi clic amb el ratolí, apareixerà una fletxa que us permetrà seleccionar una altra taula de la consulta actual.

Ordena

Si feu clic a la cel·la, podreu seleccionar una de les opcions d'ordenació: ascendent, descendent o sense ordenar. Els camps de text s'ordenaran de manera alfabètica, i els numèrics de manera numèrica. Els administradors poden definir les opcions d'ordenació per a la majoria de bases de dades.

Visible

Si activeu la propietat Visible per a un camp de dades, aquest camp serà visible a la consulta resultant. Si feu servir un camp de dades només per a formular una condició o fer un càlcul, no cal mostrar-lo.

Criteris

Permet especificar un primer criteri mitjançant el qual el contingut del camp de dades s'haurà de filtrar.

o

Aquí podeu introduïr un criteri de filtratge addicional per línia. Diversos criteris a una única columna s'interpretaran com a O booleà.

També podeu utilitzar el menú contextual de les capçaleres de línia a la part inferior de la finestra de disseny de la consulta per a inserir un filtre basat en una funció:

Funcions

Les funcions disponibles aquí dependran d'aquelles que forneixi el motor de la base de dades.

Si treballeu amb la base de dades HSQL incrustada, el quadre de llista de la línia Funció us oferirà les opcions següents:

Opció

SQL

Efecte

Cap funció

No s'executarà cap funció.

Mitjana

AVG

Calcula la mitjana aritmètica d'un camp.

Compta

COUNT

Determina el nombre de registres de la taula. Els camps buits es poden comptar (a) o excloure (b).

a) COUNT(*): si s'indica un asterisc com a argument es comptaran tots els registres de la taula.

b) COUNT(column): Passing a field name as an argument counts only the records in which the specified field contains a value. Records in which the field has a Null value (i.e. contains no textual or numeric value) will not be counted.

Màxim

MAX

Determina el valor més alt d'un registre per al camp.

Mínim

MIN

Determina el valor més baix d'un registre per al camp.

Suma

SUM

Calcula la suma de valors dels registres dels camps associats.

Agrupa

GROUP BY

Agrupa les dades de la consulta d'acord amb el nom de camp seleccionat. Les funcions s'executen d'acord amb els grups indicats. A l'SQL, aquesta opció correspon a la clàusula GROUP BY. Si s'afegeix un criteri, aquesta entrada apareix a la subclàusula SQL HAVING.


També podeu introduir crides de funció directament a l'expressió SQL. La sintaxi és:

SELECT FUNCTION(columna) FROM taula.

Per exemple, a l'SQL la crida de funció per a calcular una suma és:

SELECT SUM("Preu") 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.

Si voleu fer servir altres funcions que no són al quadre de llista, heu d'introduir-les manualment a Camp.

You can also assign aliases to function calls. If you do not want to display the query string in the column header, enter a desired substitute name under Alias.

La funció corresponent en una expressió SQL és:

SELECT FUNCTION() AS alias FROM table

Exemple:

SELECT COUNT(*) AS count FROM "Item"

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


Exemples

En l'exemple següent, hi ha una consulta que s'executa en dues taules: d'una banda, la taula "Element" amb el camp "Núm_Element" i, de l'altra, la taula "Proveïdors" amb el camp "Nom_Proveïdor". A més, aquestes dues taules tenen un nom de camp comú, "Núm_Proveïdor".

Per a crear una consulta que contingui tots els proveïdors que entreguen més de tres elements, realitzeu els passos següents:

  1. Inseriu les taules «Element» i «Proveïdors» al disseny de la consulta.

  2. Enllaceu els camps «Núm_Proveïdor» de les dues taules si encara no s'hi ha establert una relació d'aquest tipus.

  3. Feu doble clic en el camp «Núm_Element» de la taula «Element». Feu que es mostri la línia Funció mitjançant el menú contextual i seleccioneu la funció Compta.

  4. Introduïu >3 com a criteri i desactiveu el camp Visible.

  5. Feu doble clic al camp "Nom_Proveïdor" de la taula "Proveïdors" i trieu la funció Agrupa.

  6. Executeu la consulta.

Si els camps "Preu" (per al preu individual d'un article) i "Núm_Proveïdor" (per al proveïdor de l'article) existeixen a la taula "Element", podreu obtenir el preu mitjà per a l'element proporcionat per un proveïdor mitjançant la consulta següent:

  1. Inseriu la taula "Element" al disseny de la consulta.

  2. Feu doble clic als camps "Preu" i "Núm_Proveïdor".

  3. Habiliteu la línia Funció i seleccioneu la funció Mitjana en el camp "Preu".

  4. També podeu introduir "Mitjana" a la línia per al nom de l'àlies (sense cometes).

  5. Trieu Agrupa per al camp "Núm_Proveïdor".

  6. Executeu la consulta.

Disposareu de les ordres de menú contextual i els símbols següents:

Funcions

Mostra o amaga una fila per a la selecció de funcions.

Nom de la taula

Mostra o amaga la fila per al nom de la taula.

Nom de l'àlies

Mostra o amaga la fila per al nom de l'àlies.

Valors diferents

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

Per exemple, si el nom «Martí» apareix més d'una vegada a la base de dades d'adreces, podreu triar l'ordre Valors diferents per a especificar a la consulta que el nom «Martí» només tindrà una ocurrència.

Per a una consulta en què intervinguin diversos camps, la combinació dels valors de tots els camps ha de ser única per tal que el resultat es pugui formar a partir d'un registre específic. Per exemple, pot ser que tingueu «Martí a Barcelona» una vegada a la llibreta d'adreces i «Martí a Girona» dues vegades. En un cas així, si trieu l'ordre Valors diferents, la consulta utilitzarà els dos camps «Cognoms» i «Ciutat» i tornarà el resultat «Martí a Barcelona» una vegada i el resultat «Martí a Girona» una sola vegada, també.

En SQL, aquesta ordre correspon al predicat DISTINCT.

Límit

Us permet limitar el nombre màxim de registres que retorna una consulta.

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.

Formulació de condicions de filtratge

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 LibreOffice syntax, LibreOffice 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

Definició

La condició es compleix si...

=

igual a

... el contingut del camp és idèntic a l'expressió indicada.

L'operador = no es mostrarà en els camps de la consulta. Si introduïu un valor sense cap operador, s'interpretarà l'operador = de manera automàtica.

<>

diferent de

... el contingut del camp no es correspon amb l'expressió indicada.

>

major que

... el contingut del camp és major que l'expressió indicada.

<

menor que

... el contingut del camp és menor que l'expressió indicada.

>=

major que o igual a

... el contingut del camp és major que l'expressió indicada o igual a aquesta expressió.

<=

menor que o igual a

... el contingut del camp és menor que l'expressió indicada o igual a aquesta expressió.


Ordre del LibreOffice

Ordre SQL

Definició

La condició es compleix si...

IS EMPTY

IS NULL

és nul

... el camp no conté dades. Per als camps Sí/No amb tres estats possibles, aquesta ordre consulta automàticament l'estat indeterminat (ni Sí ni No).

IS NOT EMPTY

IS NOT NULL

no és buit

... el camp no és buit. Això és, conté dades.

LIKE

espai reservat (*) per a qualsevol nombre de caràcters

espai reservat (?) per a un sol caràcter

LIKE

espai reservat (%) per a qualsevol nombre de caràcters

espai reservat (_) per a un sol caràcter

és un element de

... el camp de dades conté l'expressió indicada. L'espai reservat (*) indica si l'expressió x apareix al principi del contingut del camp (x*), al final (*x) o dins d'aquest (*x*). Quan treballeu amb consultes SQL a la interfície del LibreOffice podeu utilitzar com a espais reservats tant el caràcter SQL % com l'espai reservat habitual del sistema de fitxers, (*).

Els espais reservats (*) i (%) fan referència a qualsevol nombre de caràcters. Si voleu representar un sol caràcter, podeu utilitzar el signe d'interrogació (?) a la interfície del LibreOffice o la ratlla baixa (_) a les consultes SQL.

NOT LIKE

NOT LIKE

no és un element de

... el camp no conté dades amb l'expressió especificada.

BETWEEN x AND y

BETWEEN x AND y

es troba dins l'interval [x,y]

... el camp conté un valor que es troba entre els valors x i y.

NOT BETWEEN x AND y

NOT BETWEEN x AND y

No es troba dins l'interval [x,y]

... el camp conté un valor que no es troba entre els valors x i y.

IN (a; b; c...)

Noteu que s'utilitzen signes de punt i coma com a separadors en totes les llistes de valors!

IN (a, b, c...)

conté a, b, c...

... el nom del camp conté una de les expressions a, b, c... indicades. Podeu expressar un nombre d'expressions il·limitat, i el resultat de la consulta queda determinat per un operador booleà O. Les expressions a, b, c... poden ser tant nombres com caràcters.

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

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

no conté a, b, c...

... el camp no conté cap de les expressions a, b, c... indicades.

= TRUE

= TRUE

té el valor True

... el nom del camp té el valor True.

= FALSE

= FALSE

té el valor False

... el valor del camp és FALS.


Exemples

='Ms.'

retorna els noms de camp que continguin "Sra."

<'2001-01-10'

retorna les dates anteriors al 10 de gener de 2001

LIKE 'g?ve'

retorna els registres amb continguts com ara "cola" i "cala".

LIKE 'S*'

retorna els registres amb continguts com ara «Sun».

BETWEEN 10 AND 20

retorna els registres amb continguts de camp que es trobin entre els valors 10 i 20 (els camps poden ser tant numèrics com de text).

IN (1; 3; 5; 7)

retorna registres amb els valors 1, 3, 5, 7. Si, per exemple, el nom de camp conté un número d'element, podeu crear una consulta que torni l'element amb el número especificat.

NOT IN ('Smith')

retorna registres que no continguin «Martí».


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

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

L'exemple us proporcionarà totes les entrades en què el nom de l'element comenci per 'The *'. Això significa que també podeu cercar caràcters que altrament s'interpretarien com a espais reservats, com ara *, ?, _, % o un punt.

Outer Join Escape Sequence: {oj outer-join}

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

Consulta de camps de text

Per a consultar el contingut d'un camp de text, heu d'escriure l'expressió entre cometes simples. La distinció entre majúscules i minúscules dependrà de la base de dades que s'estigui utilitzant. Per definició, LIKE distingeix entre majúscules i minúscules (malgrat que algunes bases de dades no segueixen aquest criteri estrictament).

Consulta de camps de data

Date fields are represented as #Date# to clearly identify them as dates. Date, time and date/time constants (literals) used in conditions can be of either the SQL Escape Syntax type, or default SQL2 syntax.

Element de tipus data

Sintaxi d'escapament de l'SQL núm. 1 - pot ser obsoleta

Sintaxi d'escapament de l'SQL núm. 2

Sintaxi 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 i hora

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

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

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


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

Exemple: select * from mytable where years='1999-12-31'

Totes les expressions (literals de data) han de col·locar-se entre cometes simples. (Per a més informació, consulteu la documentació de referència de la base de dades i del connector que utilitzeu.)

Consulta de camps Sí/No

Per consultar camps Sí/No, utilitzeu la sintaxi següent per a taules dBASE:

Estat

Criteri de la consulta

Exemple

per a taules dBASE: diferent de qualsevol valor donat

=1 torna tots els registres en què el camp Sí/No té l'estat "Sí" o "Activat" (seleccionat en color negre).

No

.

=0 torna tots els registres per als quals el camp Sí/No té l'estat "No" o "Desactivat" (sense selecció).

Nul

IS NULL

IS NULL torna tots els registres per als quals el camp Sí/No no té l'estat Sí ni l'estat No (seleccionat en color gris).


Icona de nota

La sintaxi depèn del sistema de bases de dades utilitzat. Noteu també que els camps Sí/No es poden definir de manera diferenciada (amb només 2 estats en comptes de 3).


Consultes amb paràmetres

Parameter queries allow the user to input values at run-time. These values are used within the criteria for selecting the records to be displayed. Each such value has a parameter name associated with it, which is used to prompt the user when the query is run.

Parameter names are preceded by a colon in both the Design and SQL views of a query. This can be used wherever a value can appear. If the same value is to appear more than once in the query, the same parameter name is used.

In the simplest case, where the user enters a value which is matched for equality, the parameter name with its preceding colon is simply entered in the Criterion row. In SQL mode this should be typed as WHERE "Field" = :Parameter_name

Icona d'avís

Els noms dels paràmetres no poden contenir cap d'aquests caràcters: <espai>`!"$%^*()+={}[]@'~#<>?/,. No poden ser iguals que els noms dels camps o les paraules reservades de l'SQL, però poden ser iguals que els àlies.


Icona de consell

A useful construction for selecting records based on parts of a text field's content is to add a hidden column with "LIKE '%' || :Part_of_field || '%'" as the criterion. This will select records with an exact match. If a case-insensitive test is wanted, one solution is to use LOWER (Field_Name) as the field and LIKE LOWER ( '%' || :Part_of_field || '%' ) as the criterion. Note that the spaces in the criterion are important; if they are left out the SQL parser interprets the entire criterion as a string to be matched. In SQL mode this should be typed as LOWER ( "Field_Name" ) LIKE LOWER ( '%' || :Part_of_field || '%' ).


Parameter queries may be used as the data source for subforms, to allow the user to restrict the displayed records.

Entrada de paràmetres

The Parameter Input dialog asks the user to enter the parameter values. Enter a value for each query parameter and confirm by clicking OK or typing Enter.

The values entered by the user may consist of any characters which are allowable for the SQL for the relevant criterion; this may depend on the underlying database system.

Icona de consell

És possible fer servir els caràcters comodí «%» (cadena arbitrària) i «_» (caràcter individual arbitrari) com a part del valor per a recuperar els registres amb criteris més complexos.


Mode SQL

La sigla SQL correspon a l'anglès "Structured Query Language" (llenguatge d'interrogació estructurat). Es tracta d'un llenguatge que dóna instruccions per a l'actualització i la gestió de bases de dades relacionals.

In LibreOffice 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, LibreOffice 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.

També podeu formular una consulta directament en codi SQL. No obstant això, observeu que la sintaxi especial dependrà del sistema de bases de dades que utilitzeu.

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 LibreOffice and sent directly to the database engine.

Ens cal la vostra ajuda!