Query-ontwerp

In Query-ontwerp kunt u een database-query maken of bewerken.

De lay-out van de query-ontwerpweergave wordt opgeslagen met de gemaakte query, maar kan als een gemaakte weergave worden opgeslagen.

De ontwerpweergave

Als u een query wilt maken, klikt u op het pictogram Query's in een databasedocument. Klik vervolgens op Query maken in ontwerpweergave.

In het onderste gedeelte van het ontwerpvenster kunt u de query definiëren. Als u een query wilt definiëren, geeft u aan welke veldnamen van de database moeten worden opgenomen en welke criteria voor de weergave van velden u wilt gebruiken. Als u de kolommen in het onderste gedeelte van het ontwerpvenster opnieuw wilt schikken, sleept u de gewenste kolomkop naar de nieuwe locatie of selecteert u de gewenste kolom en drukt u op +pijltoets.

Boven in het query-ontwerpvenster worden de pictogrammen van de werkbalk Query-ontwerp en de werkbalk Ontwerp weergegeven.

Als u een query wilt testen, dubbelklikt u op de querynaam in het databasedocument. Het queryresultaat wordt weergegeven in een tabel die vergelijkbaar is met de gegevensbronverkenner. N.B.: de tabel die wordt weergegeven, is een tijdelijke tabel.

Toetsen in query-ontwerpweergave

Sleutel	Functie
F4	Voorbeeld
F5	Query uitvoeren
F7	Tabel of query toevoegen

Bladeren

Wanneer u Queryontwerp voor het eerst opent, ziet u een dialoogvenster waarin u eerst de tabel of query moet selecteren die de basis zal vormen voor uw nieuwe zoekopdracht.

Dubbelklik op de velden om deze aan de query toe te voegen. Met slepen en neerzetten kunt u relaties definiëren.

Tabellen verwijderen

Wilt u een tabel uit het ontwerpvenster verwijderen, dan klikt u op de bovenste rand van de tabel en opent u het contextmenu. Kies de opdracht Verwijderen om de tabel uit het ontwerpvenster te verwijderen. U kunt de tabel ook verwijderen met de toets Delete.

Tabel verplaatsen en tabelformaat wijzigen

U kunt het formaat van tabellen wijzigen of tabellen verplaatsen. Wilt u tabellen verplaatsen, dan sleept u de bovenste rand naar de gewenste plek. Het formaat van de tabellen kunt u wijzigen door de randen of hoeken van de tabel naar binnen of naar buiten te slepen.

Tabelrelaties

Als er gegevensrelaties tussen een veldnaam in de ene tabel en een veldnaam in een andere tabel bestaan, kunt u deze relaties voor uw query gebruiken.

Als u bijvoorbeeld een werkblad hebt met artikelen waarbij elk artikel is voorzien van een artikelnummer en u een werkblad hebt met klanten waarin u bijhoudt welke klant welke artikelen heeft besteld, bestaat er een relatie tussen beide "artikelnummers". Wanneer u nu een query wilt maken die alle artikelen voor een bepaalde klant laat zien, dan moet u gegevens uit twee werkbladen halen. Stel in LibreOffice in wat de relatie tussen de twee werkbladen is om dit te doen.

Wanneer u dit wilt doen, klikt u in een tabel op een veldnaam (bijvoorbeeld de veldnaam 'Itemnummer' in de tabel Klant), houdt u de muisknop ingedrukt en sleept u de veldnaam naar de veldnaam van de andere tabel (bijvoorbeeld 'Itemnummer' in de tabel Item). Wanneer u de muisknop loslaat, verschijnt er een lijn die de twee velden in de twee vensters met elkaar verbindt. De overeenkomstige voorwaarde dat de inhoud van de twee velden gelijk moet zijn, wordt in de resulterende SQL-query opgenomen.

Het maken van een query die op verschillende verwante bladen is gebaseerd, is alleen mogelijk als u LibreOffice als de interface voor een relationele database gebruikt.

Stel het verbindingstype in

Als u dubbelklikt op de lijn tussen twee gekoppelde velden of de menuopdracht Invoegen - Nieuwe relatie aanroept, kunt u het type relatie opgeven in het dialoogvenster Relaties.

U kunt ook op de Tab-toets drukken totdat de verbindingsvector is geselecteerd (wordt groter weergegeven). Druk vervolgens op Shift+F10 om het contextmenu te openen en kies de opdracht Bewerken. Sommige databases ondersteunen alleen een subset van de mogelijke join-typen.

Relaties verwijderen

Wanneer u een relatie tussen twee tabellen wilt verwijderen, klikt u op de verbindingslijn en drukt u vervolgens op de Del-toets.

U kunt de items in Betrokken velden ook verwijderen via het dialoogvenster Relaties. Of druk op de Tab-toets totdat de verbindingvector wordt gemarkeerd en druk op Shift+F10 om het contextmenu te openen. Kies vervolgens de opdracht Verwijderen.

De query definiëren

Selecteer voorwaarden om de query te definiëren. Elke kolom van de ontwerptabel accepteert een gegevensveld voor de query. De voorwaarden in één rij zijn via een Booleaanse EN gekoppeld.

Veldnamen opgeven

Selecteer eerst alle velden die u in de query wilt weergeven. U kunt dit doen door te slepen met de muis of door te dubbelklikken op de veldnaam in het tabeloverzicht. Met slepen en neerzetten gebruikt u de muis om een veldnaam van het tabeloverzicht naar een kolom in het onderste gedeelte van het query-ontwerp te slepen. U kunt hierbij de veldnaam naar elke gewenste kolom slepen. Selecteer een veldnaam door erop te dubbelklikken. Het veld wordt vervolgens toegevoegd aan de volgende lege kolom.

Velden verwijderen

Als u een veld uit de query wilt verwijderen, klikt u op de kolomkop van het veld en kiest u de opdracht Verwijderen in het contextmenu.

De query opslaan

Klik op het pictogram Opslaan op de werkbalk Standaard om de query op te slaan. Er verschijnt een dialoogvenster waarin u de naam voor de query invoert. Als de database schema's ondersteunt, kunt u tevens schema's opgeven.

Schema

Voer de naam in van het schema dat aan de query- of tabelweergave is toegewezen.

Naam query-/tabelweergave

Voer de naam in van de query-/tabelweergave.

Gegevens filteren

Stel de gewenste criteria in het onderste gebied van de ontwerpweergave in om gegevens voor de query te filteren. De volgende opties zijn hier mogelijk:

Veld

Voer hier de naam van het gegevensveld in waarnaar in de query wordt verwezen. Alle instellingen in de onderste rijen verwijzen naar dit veld. Als u een cel activeert door er met de muis in te klikken, ziet u een pijltjesknop. Hiermee kunt u een veld selecteren. De optie 'Tabelnaam.*' selecteert alle gegevensvelden , waardoor de opgegeven criteria op alle velden worden toegepast.

Alias

Een alias opgeven. In plaats van de veldnaam wordt deze alias in de query weergegeven. Hierdoor wordt het mogelijk eigen kolomnamen te gebruiken. Als het gegevensveld in de tabel bijvoorbeeld de naam ArtikelNr heeft en u in plaats van deze naam de naam ArtikelNummer in de query wilt gebruiken, vult u bij alias ArtikelNummer in.

In een SQL-instructie wordt een alias als volgt gedefinieerd:

SELECT kolom AS alias FROM tabel.

Voorbeeld:

SELECT "ArtikelNr" AS "ArtikelNummer" FROM "Item"

Tabel

De corresponderende databasetabel van het geselecteerde gegevensveld wordt hier weergegeven. Als u een cel met een muisklik activeert, verschijnt er een pijl waarmee u een andere tabel van de huidige query kunt selecteren.

Sorteren

Als u op de cel klikt, kunt u de sorteeropties selecteren: Oplopend, Aflopend en Niet gesorteerd. Tekstvelden worden alfabetisch gesorteerd en numerieke velden worden numeriek gesorteerd. Voor de meeste databases kunnen beheerders sorteeropties instellen op databaseniveau.

Zichtbaar

Als u de eigenschap Zichtbaar voor een gegevensveld markeert, wordt dat veld zichtbaar afgebeeld in de resulterende query. Als u een gegevensveld alleen gebruikt voor het maken van een voorwaarde of het malen van een berekening, hoeft dit veld niet per se te worden afgebeeld.

Criteria

De eerste criteria opgeven op basis waarvan de inhoud van het gegevensveld moet worden gefilterd.

of

Hier kunt u in elke regel een aanvullend filtercriterium invullen. Meerdere criteria in een kolom worden als een Booleaanse OF gezien.

U kunt ook het contextmenu van de regelnamen in het onderste gedeelte van het query-ontwerpvenster gebruiken om filter voor een functie in te voegen:

Functies

Welke functies u kunt selecteren, is afhankelijk van de database-engine.

Als u met de ingebedde HSQL-database werkt, bevat de keuzelijst in de rij Functie de volgende opties:

Optie	SQL	Effect
Geen functie		Er wordt geen functie uitgevoerd.
Gemiddelde	AVG	Berekent het rekenkundige gemiddelde van een veld.
Aantal	COUNT	Bepaalt het aantal records in de tabel. Lege velden kunnen wel (a) of niet (b) worden geteld. a) COUNT(*): Door een asterisk als argument op te nemen, worden alle records in de tabel geteld. b) AANTAL(kolom): Bij het doorgeven van een veldnaam als een argument worden alleen de records geteld waarin het opgegeven veld een waarde bevat. Records waarin het veld een nulwaarde heeft (d.w.z. geen tekstuele of numerieke waarde bevat) worden niet geteld.
Maximum	MAX	Bepaalt de hoogste waarde van een record voor dat veld.
Minimum	MIN	Bepaalt de laagste waarde van een record voor dat veld.
Som	SUM	Berekent de som van de waarden van records voor de bijbehorende velden.
Groeperen	GROUP BY	Groepeert querygegevens volgens de geselecteerde veldnaam. Functies worden uitgevoerd volgens de opgegeven groepen. In SQL correspondeert dit met de GROUP BY-clausule. Wanneer een criterium wordt toegevoegd, wordt dit weergegeven in de sub-opdracht SQL HAVING.

U kunt ook direct functie-aanroepen in de SQL-instructie invoeren. De syntaxis hiervoor is:

SELECT FUNCTION(kolom) FROM tabel.

De functie-aanroep voor het berekenen van een som is in SQL bijvoorbeeld:

SOM SELECTEREN ("Prijs") VAN "Artikel".

Behalve de functie Groeperen zijn de bovenstaande functies zogenaamde verzamelfuncties. Dit zijn functies die gegevens berekenen om een overzicht van de resultaten te creëren. Er zijn ook nog functies mogelijk die niet in de lijst voorkomen. Deze zijn afhankelijk van het specifieke databasesysteem dat u gebruikt en de huidige status van het stuurprogramma van Base.

U moet, om andere functies, die niet zijn opgesomd in de keuzelijst, te gebruiken, die handmatig invoeren onder Veld.

U kunt ook een alias aan een functie-oproep toewijzen. Als de query niet in de kolomkop moet worden weergegeven, kunt u de gewenste naam onder Alias invoeren.

De corresponderende functie in een SQL-instructie is:

SELECT FUNCTION() AS alias FROM tabel

Voorbeeld:

AANTAL SELECTEREN(*) ALS aantal VAN "Item"

Voorbeelden

In het volgende voorbeeld zal een query worden uitgevoerd op twee tabellen: de tabel met producten met het veld "Product_Nr" en de tabel met leveranciers met het veld "Leverancier_Naam". Tevens bevatten beide tabellen het veld "Leveranciers_Nr."

De volgende stappen moeten worden uitgevoerd om een query met alle leveranciers te maken die meer dan drie items leveren.

1. Voeg de tabellen Item en Leveranciers aan het query-ontwerp toe.

2. Koppel de velden "Leverancier_Nr" van de twee tabellen, als er nog geen relatie van dit type is.

3. Dubbelklik op het veld "Item_Nr" van de "item"-tabel. Geef de regel Functie weer met behulp van het contextmenu en selecteer de functie Aantal.

4. Voer >3 in als een criterium en verwijder het vinkje bij Zichtbaar.

5. Dubbelklik op het veld "Naam_Leverancier" in de tabel met leveranciers en kies de functie Groeperen.

6. Voer de query uit.

Als "Prijs" (van een artikel) en "Leveranciersnummer" (de leverancier van het artikel) velden zijn in de tabel Product, kunt u de gemiddelde prijs van een artikel weergeven met de volgende query:

1. Voeg de tabel Product toe in het query-ontwerp.

2. Dubbelklik op de velden "Prijs" en "Leveranciersnummer".

3. Schakel de regel Functie in en selecteer de functie Gemiddelde via het veld "Prijs".

4. U kunt ook "Gemiddelde" op de regel voor de aliasnaam (zonder aanhalingstekens) invoeren.

5. Kies Groeperen voor het veld "Leveranciers_Nr".

6. Voer de query uit.

De volgende contextmenu's en symbolen zijn beschikbaar:

Functies

Geeft een rij weer voor functies of verbergt deze.

Tabelnaam

Geeft de rij voor de tabelnaam weer of verbergt deze.

Aliasnaam

Toont of verbergt de rij voor de aliasnaam.

Duidelijke waarden

Haalt alleen unieke waarden op in de query. Deze opdracht is van toepassing op records waarin meerdere keren dezelfde waarden staan. Wanneer de functie Unieke waarden actief is, wordt maar één van deze dubbele records weergegeven (DISTINCT). Anders ziet u alle records, overeenkomend met het query-criterium (ALL).

Als de naam "Jansen" bijvoorbeeld meerdere keren voorkomt in de adresdatabase, gebruikt u de opdracht Duidelijke waarden om aan te geven dat de naam Jansen maar één keer in de query mag worden weergegeven.

Wanneer u een query maakt met meerdere velden, moet de combinatie van waarden uit alle velden uniek zijn, zodat het resultaat kan worden gegenereerd op basis van een specifieke record. Stel u hebt een "Jansen in Alkmaar" en een "Jansen in Middelburg". Met de functie Duidelijke waarden zullen zowel "Jansen in Alkmaar" als "Jansen in Middelburg" worden weergegeven. Wanneer er bijvoorbeeld twee keer een "Jansen in Alkmaar" is, wordt hier maar één van weergegeven.

In SQL komt deze opdracht overeen met de opdracht DISTINCT.

Limiet

Hiermee kunt u het aantal records die de query oplevert maximaliseren.

Als er een Limiet wordt toegevoegd, krijgt u hooguit net zoveel rijen als het aantal dat u opgeeft. Anders zult u alle records zien, die met de query-criteria overeenkomen.

Filtervoorwaarden formuleren

Voor het maken van filtervoorwaarden zijn verschillende opdrachten en operators beschikbaar. Naast de relationele operators zijn er SQL-specifieke operators beschikbaar waarmee de inhoud van databasevelden kan worden opgevraagd. Als u deze opdrachten in de LibreOffice-syntaxis gebruikt, worden deze in LibreOffice automatisch geconverteerd naar de bijbehorende SQL-syntaxis. U kunt ook direct een SQL-opdracht invoeren. In de volgende tabellen vindt u een overzicht van de operators en opdrachten:

Operator	Betekenis	Aan de voorwaarde is voldaan als...
=	gelijk aan	... de inhoud van het veld identiek is aan de opgegeven expressie. De operator = wordt niet weergegeven in het queryveld. Als u een waarde zonder operator invoert, wordt automatisch het teken = gebruikt.
<>	niet gelijk aan	... de inhoud van het veld niet overeenkomt met de opgegeven expressie.
>	groter dan	... de inhoud van het veld groter is dan de opgegeven waarde.
<	kleiner dan	... de inhoud van het veld kleiner is dan de opgegeven waarde.
>=	groter dan of gelijk aan	... de inhoud van het veld groter is dan of gelijk is aan de opgegeven expressie.
<=	kleiner dan of gelijk aan	... de inhoud van het veld kleiner is dan of gelijk is aan de opgegeven expressie.

LibreOffice-opdracht	SQL-opdracht	Betekenis	Aan de voorwaarde is voldaan als...
IS EMPTY	IS NULL	is null	... het veld bevat geen gegevens. Deze opdracht kijkt automatisch of er voor Ja/Nee-velden met drie statussen geen waarde is ingevuld (Geen ja en geen nee).
IS NOT EMPTY	IS NOT NULL	is niet leeg	... het veld is niet leeg, dat wil zeggen, het bevat gegevens.
LIKE tijdelijke aanduiding (*) voor een willekeurig aantal tekens tijdelijke aanduiding (?) voor precies één teken	LIKE tijdelijke aanduiding (%) voor een willekeurig aantal tekens tijdelijke aanduiding (_) voor precies één teken	is een element van	... het veld de opgegeven expressie bevat. De tijdelijke aanduiding (*) geeft aan of de expressie x vooraan (x*), in het midden (*x*) of aan het einde (*x) van de veldinhoud staat. U kunt in SQL-query's zowel het SQL-teken % als de meer vertrouwde tijdelijke aanduiding (*) van het bestandssysteem in de LibreOffice-interface invoeren. De symbolen (*) en (%) staan voor een willekeurig aantal tekens. Het vraagteken (?) in de LibreOffice-interface of het onderstrepingsteken (_) in SQL-query's staat voor één teken.
NOT LIKE	NOT LIKE	Is geen element van	... het veld bevat geen gegevens met de opgegeven expressie.
BETWEEN x AND y	BETWEEN x AND y	valt binnen de interval [x,y]	... het veld bevat een waarde bevat die tussen de twee waarden x en y ligt.
NOT BETWEEN x AND y	NOT BETWEEN x AND y	Valt niet binnen de interval [x,y]	... het veld bevat een waarde dat niet tussen de waarden x en y ligt.
IN (a; b; c...) In alle waardenlijsten worden puntkomma's als scheidingstekens gebruikt!	IN (a, b, c...)	bevat a, b, c...	... het veld een van de waarde a, b, c,..... bevat. Er kan een willekeurige hoeveelheid expressies worden opgegeven en het resultaat van de query wordt bepaald door een booleaanse OF. De expressies a, b, c, ... kunnen zowel cijfers als letters zijn
NOT IN (a; b; c...)	NOT IN (a, b, c...)	bevat niet a, b, c...	... het veld bevat geen van de opgegeven expressies a, b, c,…
= TRUE	= TRUE	heeft de waarde WAAR	... het veld heeft de waarde WAAR.
= FALSE	= FALSE	heeft de waarde ONWAAR	... het gegevensveld bevat de waarde FOUT.

Voorbeelden

='Ms.'	geeft de velden terug die "Mw." bevatten.
<'2001-01-10'	geeft data terug die vóór 10 januari 2001 voorkomen
LIKE 'g?ve'	geeft de velden die inhoud hebben als "Hallo" en "Hello"
LIKE 'S*'	geeft de velden met veldinhoud zoals 'Zon'.
BETWEEN 10 AND 20	geeft velden met veldinhoud die tussen de waarden 10 en 20 liggen. (De velden kunnen tekstvelden of getalvelden zijn).
IN (1; 3; 5; 7)	geeft alle records die 1, 3, 5 of 7 als waarde hebben. Als het veld bijvoorbeeld een artikelnummer bevat, kunt u een query maken waarmee het artikel met het opgegeven nummer als resultaat wordt gegeven.
NOT IN ('Smith')	geeft alle velden die niet de waarde "Jansen" bevatten.

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

Bijvoorbeeld: select * from Item where Item Name like 'The *%' {escape '*'}

Dit voorbeeld geeft u alle records waarvan de waarden beginnen met 'The *'. Dit betekent dat u ook kunt zoeken op tekens die normaal als jokerteken worden beschouwd, zoals *, ?, _, % of de punt.

Outer Join Escape-reeks: {oj outer-join}

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

Tekstvelden opvragen

U kunt de inhoud van een tekstveld opvragen door de expressie tussen enkele aanhalingstekens te plaatsen. Of er onderscheid wordt gemaakt tussen hoofdletters en kleine letters, is afhankelijk van de gebruikte database. LIKE is per definitie hoofdlettergevoelig (hoewel deze regel in bepaalde databases niet zo strikt wordt genomen).

Datumvelden opvragen

Datum velden worden weergegeven als #Datum# om ze duidelijk als data te identificeren. Datum, tijd en datum/tijd constanten (letterlijke) die in condities worden gebruikt kunnen van de SQL Escape Syntax type zijn of standaard SQL2 syntax.

Datumtype-element	SQL Escape syntax #1 - kan verouderd zijn	SQL Escape syntax #2	SQL2 syntax
Datum	{D'YYYY-MM-DD'}	{d 'YYYY-MM-DD'}	'YYYY-MM-DD'
Tijd	{D'HH:MM:SS'}	{t 'HH:MI:SS[.SS]'}	'HH:MI:SS[.SS]'
DatumTijd	{D'YYYY-MM-DD HH:MM:SS'}	{ts 'YYYY-MM-DD HH:MI:SS[.SS]'}	'YYYY-MM-DD HH:MI:SS[.SS]'

Bijvoorbeeld: selecteer {d '1999-12-31'} van wereld.jaren

Voorbeeld: selecteer * van mijntabel waar jaren='1999-12-31'

Alle (datum-letterlijke) datum uitdrukkingen moet worden ingesloten door enkele aanhalingstekens. (Raadpleeg de referentie voor de specifieke database en connector die u gebruikt, voor meer informatie.)

Ja/Nee-velden opvragen

Als u Ja/Nee-velden wilt opvragen, moet u de volgende syntaxis gebruiken voor dBASE-tabellen:

Status	Querycriterium	Voorbeeld
Ja	voor dBASE-tabellen: niet gelijk aan een opgegeven waarde	=1 geeft alle records waarin het Ja/Nee-veld de status "Ja" of "Aan" heeft (geselecteerd in zwart),
Nee	.	=0 retourneert alle records waarvoor het Ja/Nee-veld de status 'Nee' of 'Uit' heeft (geen selectie).
Null	IS NULL	IS NULL geeft alle records waarin het Ja/Nee-veld geen van de waarden Ja of Nee heeft (geselecteerd in grijs).

Parameterquery's

Parameter query's maken het voor de gebruiker mogelijk om tijdens de uitvoering waarden in te voeren. Deze waarden worden gebruikt in de criteria voor het selecteren van getoonde records. Ieder van die waarden is geassocieerd met de naam van een parameter, die wordt gebruikt om de gebruiker een invoermelding te geven tijdens het uitvoeren van de query.

Parameter namen worden zowel in de Ontwerpview als in de SQL-view voorafgegaan door een dubbele punt. Als dezelfde waarde meer dan eens in een query voorkomt, wordt dezelfde parameter gebruikt.

In het eenvoudigste geval, als de gebruiker een waarde invoert ter vergelijking, wordt de parameter-naam met de voorafgaande dubbele punt eenvoudig ingevoerd in het criterium Rij. In SQL modus moet dit worden getypt als WHERE "Field" = :Parameter_name

Parameter query's mogen gebruikt worden als de gegevensbron voor subformulieren, om de gebruiker de mogelijkheid te geven om het aantal weergegeven records te beperken.

Parameter opgeven

Het venster Parameter Imput vraagt de gebruiker om de waarden van de parameter in te voeren. Voer voor iedere query parameter een waarde in en bevestig met OK of het typen van Enter.

De waarden die door de gebruiker worden ingevoerd mogelijk bestaan uit ieder karakter dat is toegestaan voor de SQL van het relevante criterium; dit kan afhankelijk zijn van het onderliggende database-systeem.

SQL-modus

SQL staat voor "Structured Query Language" en beschrijft de instructies voor het bijwerken en beheren van relationele databases.

In LibreOffice hebt u voor de meeste query's geen kennis van SQL nodig. Als u een query maakt in het query-ontwerpvenster, worden de instructies in LibreOffice automatisch geconverteerd naar de overeenkomstige SQL-syntaxis. Met behulp van de knop Ontwerpweergave aan/uit kunt u wisselen tussen de ontwerpweergave en de SQL-weergave, waarin de overeenkomstige SQL-instructie voor een reeds gemaakte query wordt weergegeven.

U kunt de query ook direct in SQL opstellen. Let hierbij echter op dat de syntaxis per gebruikt databasesysteem kan verschillen.

Als u handmatig de SQL-code invoert, kunt u in het Query-ontwerp SQL-specifieke query's maken die niet worden ondersteund door de grafische interface. Deze query's moet worden uitgevoerd in de native SQL-modus.

Als u direct op het pictogram SQL-opdracht onmiddellijk uitvoeren in de SQL-weergave klikt, kunt u een query formuleren die niet wordt verwerkt in LibreOffice en rechtstreeks naar de database-engine worden gezonden.