Query-ontwerp

Met de Query-ontwerpweergave kunt u een databasequery maken en bewerken.

De meeste databases gebruiken query's om databasetabellen te filteren of te sorteren, om records op uw computer weer te geven. Weergaven bieden dezelfde functionaliteit als query's, maar dan aan de serverkant. Als uw database zich op een server bevindt die weergaven ondersteunt, kunt u weergaven gebruiken om de records op de server te filteren om de weergavetijd te versnellen.

Als u de opdracht Weergave maken selecteert op het tabblad Tabellen van een databasedocument, ziet u het venster Ontwerp bekijken dat lijkt op het venster Query-ontwerp dat hier wordt beschreven.

De lay-out van het venster Query-ontwerp wordt opgeslagen met een gemaakte query, maar kan niet worden opgeslagen met een gemaakte weergave.

De Ontwerpweergave

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

In het onderste deelvenster van de ontwerpweergave definieert u de query. Om een query te definiëren, specificeert u de namen van de databasevelden die u wilt opnemen en de criteria voor het weergeven van de velden. Om de kolommen in het onderste deelvenster van de ontwerpweergave te herschikken, sleept u een kolomkop naar een nieuwe locatie of selecteert u de kolom en drukt u op +pijltoets.

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

Als u een query wilt testen, dubbelklikt u op de naam van de query in het databasedocument. Het queryresultaat wordt weergegeven in een tabel die lijkt op de gegevensbronweergave. Opmerking: de weergegeven tabel is slechts tijdelijk.

Toetsen in de weergave Query-ontwerp

Doorzoeken

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

Dubbelklik op velden om ze aan de query toe te voegen. Slepen en neerzetten om relaties te definiëren.

Tijdens het ontwerpen van een query kunt u de geselecteerde tabellen niet wijzigen.

Tabellen verwijderen

Om de tabel uit de ontwerpweergave te verwijderen, klikt u op de bovenrand van het tabelvenster en geeft u het contextmenu weer. U kunt de opdracht Verwijderen gebruiken om de tabel uit de ontwerpweergave te verwijderen. Een andere optie is om op de Delete-toets te drukken.

Tabel verplaatsen en tabelgrootte wijzigen

U kunt de grootte van de tabellen wijzigen en rangschikken naar uw voorkeuren. Om tabellen te verplaatsen, sleept u de bovenrand naar de gewenste positie. Vergroot of verklein de grootte waarin de tabel wordt weergegeven door de muisaanwijzer op een rand of op een hoek te plaatsen en de tabel te slepen totdat deze de gewenste grootte heeft.

Tabelrelaties

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

Als u bijvoorbeeld een werkblad heeft voor artikelen die worden geïdentificeerd door een artikelnummer en een werkblad voor klanten waarin u alle artikelen die een klant bestelt vastlegt met de bijbehorende artikelnummers, dan is er een relatie tussen de twee datavelden "artikelnummers". Als u nu een query wilt maken die alle artikelen retourneert die een klant heeft besteld, moet u gegevens ophalen uit twee werkbladen. Om dit te doen, moet u LibreOffice informeren over de relatie die bestaat tussen de gegevens in de twee werkbladen.

Klik hiervoor op een veldnaam in een tabel (bijvoorbeeld de veldnaam "Artikelnummer" uit de tabel Klant), houd de muisknop ingedrukt en sleep de veldnaam naar de veldnaam van de andere tabel (" Artikelnummer" uit de Artikeltabel). Wanneer u de muisknop loslaat, verschijnt een lijn die de twee velden tussen de twee tabelvensters verbindt. De bijbehorende voorwaarde dat de inhoud van de twee veldnamen identiek moet zijn, wordt ingevoerd in de resulterende SQL-query.

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

U kunt in een query geen toegang krijgen tot tabellen uit verschillende databases. Query's met meerdere tabellen kunnen alleen binnen één database worden gemaakt.

Het relatietype specificeren

Als u dubbelklikt op de lijn die twee gekoppelde velden verbindt of de menu-opdracht Invoegen - Nieuwe relatie oproept, kunt u het type relatie specificeren in het dialoogvenster Relaties.

U kunt ook op Tab drukken totdat de regel is geselecteerd, dan op Shift+F10 drukken om het contextmenu weer te geven en daar de opdracht Bewerken te kiezen. Sommige databases ondersteunen slechts een subset van de mogelijke join-typen.

Relaties verwijderen

Om een relatie tussen twee tabellen te verwijderen, klikt u op de verbindingslijn en drukt u vervolgens op de Delete-toets.

U kunt ook de respectievelijke vermeldingen in Betrokken velden in het dialoogvenster Relaties verwijderen. Of druk op Tab totdat de verbindende vector gemarkeerd wordt weergegeven, druk dan op Shift+F10 om het contextmenu te openen en selecteer 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 gekoppeld aan een Booleaanse AND.

Veldnamen definiëren

Selecteer eerst alle veldnamen uit de tabellen die u aan de query wilt toevoegen. U kunt dit doen door te slepen en neer te zetten of door te dubbelklikken op een veldnaam in het tabelvenster. Gebruik bij de methode slepen en neerzetten de muis om een veldnaam van het tabelvenster naar het onderste gedeelte van het query-ontwerpvenster te slepen. Terwijl u dit doet, kunt u beslissen in welke kolom in het query-ontwerpvenster het geselecteerde veld wordt weergegeven. Een veldnaam kan ook worden geselecteerd door te dubbelklikken. Het wordt dan toegevoegd aan de volgende vrije kolom in het query-ontwerpvenster.

Veldnamen verwijderen

Om een veldnaam uit de query te verwijderen, klikt u op de kolomkop van het veld en kiest u de opdracht Verwijderen in het contextmenu voor de kolom.

De query opslaan

Gebruik het pictogram Opslaan op de werkbalk Standaard om de zoekopdracht op te slaan. U ziet een dialoogvenster waarin u wordt gevraagd een naam voor de query in te voeren. Als de database schema's ondersteunt, kunt u ook een schemanaam invoeren.

Schema

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

Naam van query of naam van tabelweergave

Voer de naam van de query of tabelweergave in.

Gegevens filteren

Als u gegevens voor de query wilt filteren, stelt u de gewenste criteria in het onderste gedeelte van het query-ontwerpvenster in. De volgende opties zijn beschikbaar:

Veld

Voer de naam in van het gegevensveld waarnaar in de query wordt verwezen. Alle instellingen in de filteroptierijen verwijzen naar dit veld. Als u hier een cel activeert met een muisklik ziet u een pijlknop waarmee u een veld kunt selecteren. De optie "Tabelnaam.*" selecteert alle gegevensvelden met als gevolg dat de opgegeven criteria worden toegepast op alle tabelvelden.

Alias

Specificeert een alias. Deze alias wordt in de query weergegeven in plaats van de veldnaam. Dit maakt het mogelijk om door de gebruiker gedefinieerde kolomlabels te gebruiken. Als het gegevensveld bijvoorbeeld PtNo heet en u in plaats van die naam wilt dat PartNum in de query verschijnt, voert u PartNum in als alias.

In een SQL-instructie worden aliassen als volgt gedefinieerd:

SELECT kolom AS alias FROM tabel.

Bijvoorbeeld:

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

Tabel

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

Sorteren

Als u op deze cel klikt, kunt u een sorteeroptie kiezen: oplopend, aflopend en ongesorteerd. Tekstvelden worden alfabetisch gesorteerd en numerieke velden numeriek. Voor de meeste databases kunnen beheerders de sorteeropties op databaseniveau instellen.

Zichtbaar

Als u de eigenschap Zichtbaar markeert voor een gegevensveld, wordt dat veld zichtbaar weergegeven in de resulterende query. Als u een gegevensveld alleen gebruikt om een voorwaarde te formuleren of een berekening te maken, hoeft u deze niet per se weer te geven.

Criteria

Specificeert een eerste criterium waarop de inhoud van het gegevensveld moet worden gefilterd.

of

Hier kunt u per regel één extra filtercriterium invoeren. Meerdere criteria in een enkele kolom worden geïnterpreteerd als booleaanse OF.

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

Functies

De functies die hier beschikbaar zijn, zijn afhankelijk van de functies die door de database-engine worden geleverd.

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

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

SELECT FUNCTION(kolom) FROM tabel.

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

SELECT SUM("Priis") FROM "Artikel".

Behalve de functie Groeperen, worden de bovenstaande functies aggregaatfuncties genoemd. Dit zijn functies die gegevens berekenen om samenvattingen van de resultaten te maken. Extra functies die niet in de keuzelijst staan, zijn wellicht ook mogelijk. Deze zijn afhankelijk van de specifieke database-engine die wordt gebruikt en van de huidige functionaliteit die wordt geboden door het basisstuurprogramma dat wordt gebruikt om verbinding te maken met die database-engine.

Om andere functies te gebruiken die niet in de lijst staan, moet u ze handmatig invoeren onder Veld.

U kunt ook aliassen toewijzen aan functie-aanroepen. Als u de queryreeks niet in de kolomkop wilt weergeven, voert u een gewenste vervangende naam in onder Alias.

De corresponderende functie in een SQL-statement is:

SELECT FUNCTION() AS alias FROM tabel

Voorbeeld:

SELECT COUNT(*) AS count FROM "Item"

Als u een dergelijke functie uitvoert, kunt u geen extra kolommen voor de query invoegen, behalve als argument in een functie "Groeperen".

Voorbeelden

In het volgende voorbeeld wordt een query uitgevoerd via twee tabellen: een tabel "Artikel" met het veld "Artikelnr" en een tabel "Leveranciers" met het veld "Naam leverancier". Bovendien hebben beide tabellen een gemeenschappelijke veldnaam "Leverancier_Num."

De volgende stappen zijn vereist om een query te maken met alle leveranciers die meer dan drie artikelen leveren.

1. Voeg de tabellen 'Artikel' en 'Leveranciers' in het query-ontwerp in.

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

3. Dubbelklik op het veld "Artikel_Nee" in de tabel "Artikel". Geef de regel Functie weer met behulp van het contextmenu en selecteer de functie Aantal.

4. Voer >3 in als criterium en schakel het veld Zichtbaar uit.

5. Dubbelklik op het veld "Naam leverancier" in de tabel "Leveranciers" en kies de functie Groeperen.

6. Voer de query uit.

Als de velden "prijs" (voor de individuele prijs van een artikel) en "Leverancier_Num" (voor de leverancier van het artikel) in de tabel "Artikel" bestaan, kunt u de gemiddelde prijs van het artikel verkrijgen dat een leverancier met de volgende vraag:

1. Voeg de tabel "Item" in het query-ontwerp in.

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

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

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

5. Kies Groeperen voor het veld "Leverancier_Num".

6. Voer de query uit.

De volgende opdrachten en symbolen in het contextmenu zijn beschikbaar:

Functies

Toont of verbergt een rij voor de selectie van functies.

Tabelnaam

Toont of verbergt de rij voor de tabelnaam.

Aliasnaam

Toont of verbergt de rij voor de aliasnaam.

Verschillende waarden

Haalt alleen afzonderlijke waarden op uit de query. Dit is van toepassing op meerdere records die meerdere herhaalde voorkomens van gegevens in de geselecteerde velden kunnen bevatten. Als de opdracht Unieke waarden actief is, zou u slechts één record in de query moeten zien (EENDUIDIG). Anders ziet u alle records die overeenkomen met de zoekcriteria (ALL).

Als de naam "Smith" bijvoorbeeld meerdere keren voorkomt in uw adressenbestand, kunt u de opdrachtUnieke waarden kiezen om in de query aan te geven dat de naam "Smith" slechts één keer voorkomt.

Voor een query met meerdere velden moet de combinatie van waarden uit alle velden uniek zijn, zodat het resultaat uit een specifiek record kan worden gevormd. U hebt bijvoorbeeld "Smith in Chicago" één keer in uw adresboek en "Smith in Londen" twee keer. Met de opdrachtUnieke waarden, gebruikt de query de twee velden "achternaam" en "stad" en retourneert het zoekresultaat eenmaal "Smith in Chicago" en eenmaal "Smith in Londen".

In SQL komt deze opdracht overeen met het predicaat DISTINCT.

Limiet

Hiermee kunt u het maximale aantal records beperken dat door een query wordt geretourneerd.

Als er een Limiet-constructie wordt toegevoegd, krijgt u maximaal zoveel rijen als het aantal dat u opgeeft. Anders ziet u alle records die overeenkomen met de zoekcriteria.

Filtervoorwaarden formuleren

Bij het formuleren van filtervoorwaarden staan verschillende operators en commando's tot je beschikking. Naast de relationele operators zijn er SQL-specifieke commando's die de inhoud van databasevelden opvragen. Als u deze commando's gebruikt in de syntaxis LibreOffice, converteert LibreOffice deze automatisch naar de bijbehorende SQL-syntaxis via een interne parser. U kunt de SQL-opdracht ook rechtstreeks invoeren en de interne parser omzeilen. De volgende tabellen geven een overzicht van de operators en commando's:

Voorbeelden

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

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

Het voorbeeld geeft u alle vermeldingen waarbij de itemnaam begint met 'De *'. Dit betekent dat u ook kunt zoeken naar tekens die anders als tijdelijke aanduidingen zouden worden geïnterpreteerd, zoals *, ?, _, % of de punt.

Outer Join Escape Sequence: {oj outer-join}

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

Opvragen van tekstvelden

Als u de inhoud van een tekstveld wilt opvragen, moet u de uitdrukking tussen enkele aanhalingstekens plaatsen. Het onderscheid tussen hoofdletters en kleine letters hangt af van de database die wordt gebruikt. LIKE is per definitie hoofdlettergevoelig (hoewel sommige databases dit niet strikt interpreteren).

Datumvelden opvragen

Datumvelden worden weergegeven als #Date# om ze duidelijk als datums te identificeren. Datum-, tijd- en datum-/tijdconstanten (literalen) die in voorwaarden worden gebruikt, kunnen van het type SQL Escape-syntaxis of de standaard SQL2-syntaxis zijn.

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

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

Alle datum-uitdrukkingen (letterlijke datums) moeten tussen enkele aanhalingstekens staan. (Raadpleeg de referentie voor de specifieke database en connector die u gebruikt voor meer details.)

Ja/Nee-velden opvragen

Gebruik de volgende syntaxis voor dBASE-tabellen om Ja/Nee-velden op te vragen:

De syntaxis is afhankelijk van het gebruikte databasesysteem. Houd er ook rekening mee dat Ja/Nee-velden anders kunnen worden gedefinieerd (slechts 2 toestanden in plaats van 3).

Parameterquery's

Met parameterquery's kan de gebruiker tijdens runtime waarden invoeren. Deze waarden worden gebruikt binnen de criteria voor het selecteren van de weer te geven records. Aan elke dergelijke waarde is een parameternaam gekoppeld, die wordt gebruikt om de gebruiker te vragen wanneer de query wordt uitgevoerd.

Parameternamen worden voorafgegaan door een dubbele punt in zowel de ontwerp- als de SQL-weergave van een query. Dit kan overal worden gebruikt waar een waarde kan verschijnen. Als dezelfde waarde meer dan één keer in de query moet voorkomen, wordt dezelfde parameternaam gebruikt.

In het eenvoudigste geval, waarbij de gebruiker een waarde invoert die overeenkomt voor gelijkheid, wordt de parameternaam met de voorafgaande dubbele punt eenvoudig ingevoerd in de rij Criterium. In SQL-modus moet dit worden getypt als WHERE "Field" = :Parameter_name

Parameternamen mogen geen van de tekens <space>`!"$%^*()+={}[]@'~#<>?/, bevatten. mogen niet hetzelfde zijn als veldnamen of gereserveerde SQL-woorden. Ze kunnen hetzelfde zijn als aliassen.

Een handige manier om records te selecteren op grond van de inhoud is het toevoegen van verborgen kolom met "LIKE '%' || :Part_of_field || '%'" als criterium. Hierdoor worden records geselecteerd die overeenkomen. Als u een hoofdletterongevoelige test wilt doen, kunt u een LOWER (Field_Name) als het veld gebruiken en LIKE LOWER ( '%' || :Part_of_field || '%' ) als criterium. Hier zijn de spaties in het criterium van belang, zonder de spaties ziet de SQL-parser het criterium als onderdeel van de te zoeken tekenreeks die moet overeenkomen. In SQL-mode moet dit worden ingevuld als LOWER ( "Field_Name" ) LIKE LOWER ( '%' || :Part_of_field || '%' ).

Parameterquery's kunnen worden gebruikt als gegevensbron voor subformulieren, zodat de gebruiker de weergegeven records kan beperken.

Parameterinvoer

Het dialoogvenster Parameterinvoer vraagt de gebruiker om de parameterwaarden in te voeren. Voer een waarde in voor elke queryparameter en bevestig door op OK te klikken of Enter te typen.

De door de gebruiker ingevoerde waarden mogen bestaan uit alle karakters die voor de SQL voor het betreffende criterium toelaatbaar zijn; dit kan afhankelijk zijn van het onderliggende databasesysteem.

De gebruiker kan de SQL-jokertekens "%" (willekeurige tekenreeks) of "_" (willekeurig enkel teken) gebruiken als onderdeel van de waarde om records met complexere criteria op te halen.

SQL-modus

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

In LibreOffice heeft u voor de meeste query's geen kennis van SQL nodig, aangezien u de SQL-code niet hoeft in te voeren. Als u een query maakt in de query-ontwerper, converteert LibreOffice uw instructies automatisch naar de bijbehorende SQL-syntaxis. Als u met behulp van de knop Ontwerpweergave Aan/Uit schakelt, naar de SQL-weergave gaat, kunt u de SQL-commando's zien voor een query die al is gemaakt.

U kunt uw vraag direct in SQL-code formuleren. Houd er echter rekening mee dat de speciale syntaxis afhankelijk is van het databasesysteem dat u gebruikt.

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

Door te klikken op het pictogram SQL-opdracht direct uitvoeren in de SQL-weergave, kunt u formuleer een query die niet wordt verwerkt door LibreOffice en rechtstreeks naar de database-engine wordt verzonden.