Om SQL-query's in een SQLite-database te schrijven, moet u weten hoe de SELECT-, FROM-, WHERE-, GROUP BY-, ORDER BY- en LIMIT-clausules werken en hoe u ze kunt gebruiken.
Tijdens deze tutorial leer je hoe je deze clausules gebruikt en hoe je SQLite-clausules schrijft.
In deze tutorial leer je-
- Gegevens lezen met Select
- Namen en alias
- WAAR
- Beperken en bestellen
- Duplicaten verwijderen
- Aggregaat
- Groep OP
- Query en subquery
- Stel Operations-UNION, Intersect in
- NULL-afhandeling
- Voorwaardelijke resultaten
- Gemeenschappelijke tabelexpressie
- Geavanceerde vragen
Gegevens lezen met Select
De SELECT-clausule is de belangrijkste instructie die u gebruikt om een SQLite-database te doorzoeken. In de SELECT-clausule geeft u aan wat u wilt selecteren. Maar laten we voor de select-clausule kijken waar we gegevens kunnen selecteren met behulp van de FROM-component.
De FROM-component wordt gebruikt om aan te geven waar u gegevens wilt selecteren. In de from-clausule kunt u een of meer tabellen of subquery's specificeren om de gegevens uit te selecteren, zoals we later in de tutorials zullen zien.
Merk op dat u voor alle volgende voorbeelden de sqlite3.exe moet uitvoeren en een verbinding met de voorbeelddatabase moet openen als stromend:
Stap 1) In deze stap,
- Open Deze computer en ga naar de volgende map " C: \ sqlite " en
- Open vervolgens " sqlite3.exe ":
Stap 2) Open de database " TutorialsSampleDB.db " met het volgende commando:
Nu bent u klaar om elk type query op de database uit te voeren.
In de SELECT-clausule kunt u niet alleen een kolomnaam selecteren, maar heeft u ook tal van andere opties om aan te geven wat u wilt selecteren. Als volgt:
SELECTEER *
Met deze opdracht worden alle kolommen geselecteerd uit alle tabellen (of subquery's) waarnaar wordt verwezen in de FROM-component. Bijvoorbeeld:
SELECTEER *VAN StudentenINNER JOIN Afdelingen ON Students.DepartmentId = Departments.DepartmentId;
Hiermee worden alle kolommen geselecteerd uit zowel de tabellen studenten als de afdelings tabellen:
SELECTEER tabelnaam. *
Hiermee worden alle kolommen geselecteerd uit alleen de tabel "tabelnaam". Bijvoorbeeld:
SELECTEER studenten. *VAN StudentenINNER JOIN Afdelingen ON Students.DepartmentId = Departments.DepartmentId;
Hiermee worden alleen alle kolommen uit de studententabel geselecteerd:
Een letterlijke waarde
Een letterlijke waarde is een constante waarde die kan worden opgegeven in de instructie select. U kunt letterlijke waarden normaal gesproken op dezelfde manier gebruiken als kolomnamen in de SELECT-clausule. Deze letterlijke waarden worden weergegeven voor elke rij uit de rijen die worden geretourneerd door de SQL-query.
Hier zijn enkele voorbeelden van verschillende letterlijke waarden die u kunt selecteren:
- Numeriek Letterlijk - getallen in elk formaat zoals 1, 2,55, ... enz.
- Letterlijke tekenreeks - Elke tekenreeks 'VS', 'dit is een voorbeeldtekst', ... enz.
- NULL - NULL-waarde.
- Current_TIME - Het geeft u de huidige tijd.
- CURRENT_DATE - dit geeft u de huidige datum.
Dit kan handig zijn in sommige situaties waarin u een constante waarde moet selecteren voor alle geretourneerde rijen. Als u bijvoorbeeld alle studenten uit de studententabel wilt selecteren, met een nieuwe kolom genaamd een land die de waarde "VS" bevat, kunt u dit doen:
SELECTEER *, 'VS' ALS land VAN studenten;
Dit geeft je alle kolommen van de leerlingen, plus een nieuwe kolom "Land", zoals deze:
Merk op dat deze nieuwe kolom Land eigenlijk geen nieuwe kolom is die aan de tabel is toegevoegd. Het is een virtuele kolom, gemaakt in de query voor het weergeven van de resultaten, en wordt niet op de tafel gemaakt.
Namen en alias
De alias is een nieuwe naam voor de kolom waarmee u de kolom met een nieuwe naam kunt selecteren. De kolomaliassen worden gespecificeerd met het sleutelwoord "AS".
Als u bijvoorbeeld de kolom StudentName wilt selecteren die moet worden geretourneerd met "Student Name" in plaats van "StudentName", kunt u deze een alias geven zoals deze:
SELECTEER StudentName ALS 'Studentnaam' VAN Studenten;
Dit geeft je de namen van de studenten met de naam "Student Name" in plaats van "StudentName" als volgt:
Merk op dat de kolomnaam nog steeds " StudentName " is; de kolom StudentName is nog steeds hetzelfde, maar verandert niet door de alias.
De alias verandert de kolomnaam niet; het zal alleen de weergavenaam in de SELECT-clausule veranderen.
Houd er ook rekening mee dat het trefwoord "AS" optioneel is, u kunt de aliasnaam er ook zonder plaatsen, zoiets als dit:
SELECTEER StudentName 'Student Name' VAN Studenten;
En het geeft je exact dezelfde output als de vorige query:
U kunt tabellen ook aliassen geven, niet alleen kolommen. Met hetzelfde trefwoord "AS". U kunt dit bijvoorbeeld doen:
SELECTEER s. * FROM Students AS s;
Dit geeft je alle kolommen in de tabel Studenten:
Dit kan erg handig zijn als u aan meer dan één tafel meedoet; in plaats van de volledige tabelnaam in de query te herhalen, kunt u elke tabel een korte aliasnaam geven. Bijvoorbeeld in de volgende query:
SELECTEER Students.StudentName, Departments.DepartmentNameVAN StudentenINNER JOIN Afdelingen ON Students.DepartmentId = Departments.DepartmentId;
Deze zoekopdracht selecteert de naam van elke student uit de tabel "Studenten" en de naam van de afdeling uit de tabel "Afdelingen":
Dezelfde vraag kan echter als volgt worden geschreven:
SELECTEER s.StudentName, d.DepartmentNameVAN Studenten AS sINNER JOIN Afdelingen AS d ON s.DepartmentId = d.DepartmentId;
- We hebben de studententafel een alias "s" gegeven en de afdelingslijst een alias "d".
- In plaats van de volledige naam van de tabel te gebruiken, gebruikten we hun aliassen om ernaar te verwijzen.
- INNER JOIN voegt twee of meer tabellen samen met een voorwaarde. In ons voorbeeld zijn we lid geworden van de studententabel met de departemententabel met de kolom DepartmentId. Er is ook een uitgebreide uitleg voor de INNER JOIN in de tutorial "SQLite Joins".
Dit geeft u de exacte uitvoer als de vorige zoekopdracht:
WAAR
Als u SQL-query's schrijft met de SELECT-clausule alleen met de FROM-component, zoals we in de vorige sectie hebben gezien, krijgt u alle rijen uit de tabellen. Als u echter de geretourneerde gegevens wilt filteren, moet u een "WHERE" -clausule toevoegen.
De WHERE-clausule wordt gebruikt om de resultaatset te filteren die door de SQL-query wordt geretourneerd. Dit is hoe de WHERE-clausule werkt:
- In de WHERE-component kunt u een "uitdrukking" specificeren.
- Die uitdrukking wordt geëvalueerd voor elke rij die wordt geretourneerd door de tabel (len) die zijn opgegeven in de FROM-component.
- De uitdrukking wordt geëvalueerd als een Booleaanse uitdrukking, met als resultaat true, false of null.
- Alleen rijen waarvoor de expressie is geëvalueerd met een true-waarde, worden geretourneerd en die met false of null-resultaten worden genegeerd en niet opgenomen in de resultatenset.
- Om de resultaten die zijn ingesteld met de WHERE-component te filteren, moet u uitdrukkingen en operatoren gebruiken.
Lijst met operators in SQLite en hoe ze te gebruiken
In de volgende sectie zullen we uitleggen hoe u kunt filteren met behulp van uitdrukkingen en operatoren.
Expressie is een of meer letterlijke waarden of kolommen die met elkaar worden gecombineerd met een operator.
Merk op dat u expressies kunt gebruiken in zowel de SELECT-component als in de WHERE-component.
In de volgende voorbeelden zullen we de uitdrukkingen en operatoren in zowel de select-clausule als de WHERE-clausule proberen. Om u te laten zien hoe ze presteren.
Er zijn verschillende soorten expressies en operatoren die u als volgt kunt specificeren:
SQLite de aaneenschakelingsoperator "||"
Deze operator wordt gebruikt om een of meer letterlijke waarden of kolommen met elkaar samen te voegen. Het zal één reeks resultaten opleveren van alle aaneengeschakelde letterlijke waarden of kolommen. Bijvoorbeeld:
SELECTEER 'Id met naam:' || StudentId || StudentName AS StudentIdWithNameVAN studenten;
Dit wordt samengevoegd tot een nieuwe alias " StudentIdWithName ":
- De letterlijke tekenreekswaarde " Id met naam: "
- met de waarde van de kolom " StudentId " en
- met de waarde uit de kolom " StudentName "
SQLite CAST-operator:
De CAST-operator wordt gebruikt om een waarde van een gegevenstype naar een ander gegevenstype te converteren.
Als u bijvoorbeeld een numerieke waarde hebt opgeslagen als een tekenreekswaarde zoals deze " '12 .5 ' " en u deze wilt converteren naar een numerieke waarde, kunt u de CAST-operator gebruiken om dit als volgt te doen " CAST ('12 .5' AS ECHT) ". Of als je een decimale waarde hebt zoals 12.5, en je hebt alleen het integer gedeelte nodig, dan kun je het casten naar een integer zoals deze "CAST (12.5 AS INTEGER)".
Voorbeeld
In de volgende opdracht zullen we proberen verschillende waarden om te zetten in andere gegevenstypen:
SELECTEER CAST ('12 .5 'ALS ECHT) ToReal, CAST (12.5 ALS INTEGER) ALS ToInteger;
Dit geeft je:
Het resultaat is als volgt:
- CAST ('12 .5 'AS REAL) - de waarde '12 .5' is een tekenreekswaarde, deze wordt geconverteerd naar een REAL-waarde.
- CAST (12.5 ALS INTEGER) - de waarde 12.5 is een decimale waarde, deze wordt geconverteerd naar een geheel getal. Het decimale gedeelte wordt afgekapt en wordt 12.
Rekenkundige operators voor SQLite:
Neem twee of meer numerieke letterlijke waarden of numerieke kolommen en retourneer één numerieke waarde. De rekenkundige operators die worden ondersteund in SQLite zijn:
|
Voorbeeld:
In het volgende voorbeeld zullen we de vijf rekenkundige operatoren proberen met letterlijke numerieke waarden in dezelfde
selecteer clausule:
SELECTEER 25 + 6, 25-6, 25 * 6, 25% 6, 25/6;
Dit geeft je:
Merk op hoe we hier een SELECT-instructie zonder een FROM-component hebben gebruikt. En dit is toegestaan in SQLite zolang we letterlijke waarden selecteren.
SQLite-vergelijkingsoperatoren
Vergelijk twee operanden met elkaar en retourneer een true of false als volgt:
|
Merk op dat SQLite de ware waarde uitdrukt met 1 en de valse waarde met 0.
Voorbeeld:
SELECTEER10 <6 AS '<', 10 <= 6 AS '<=',10> 6 AS '>', 10> = 6 AS '> =',10 = 6 AS '=', 10 == 6 AS '==',10! = 6 AS '! =', 10 <> 6 AS '<>';
Dit geeft zoiets als dit:
Operatoren voor SQLite Pattern Matching
Met " Vind ik leuk " kunt u zoeken naar waarden die overeenkomen met een patroon dat is opgegeven met een jokerteken.
De operand aan de linkerkant kan een letterlijke tekenreekswaarde of een tekenreekskolom zijn. Het patroon kan als volgt worden gespecificeerd:
- Bevat patroon. Bijvoorbeeld, StudentName ALS '% a%' - hiermee wordt gezocht naar de namen van de studenten die de letter "a" bevatten in elke positie in de kolom StudentName.
- Begint met het patroon. Bijvoorbeeld: " StudentNaam ALS 'a%' " - zoek naar de namen van de studenten die beginnen met de letter "a".
- Eindigt met het patroon. Bijvoorbeeld: " StudentName LIKE '% a' " - Zoek naar de namen van de studenten die eindigen op de letter "a".
- Overeenkomen met elk willekeurig teken in een string met behulp van het onderstrepingsteken "_". Bijvoorbeeld: " StudentName LIKE 'J___' " - Zoek naar namen van studenten die 4 tekens lang zijn. Het moet beginnen met de "J" -letter en mag na de "J" -letter nog drie andere tekens bevatten.
Voorbeelden van patroonovereenkomsten:
- Haal de namen van studenten op die beginnen met de 'j'-letter:
SELECTEER StudentNaam VAN Studenten WAAR StudentNaam ZOALS 'j%';
Resultaat:
- Laat de namen van studenten eindigen met de 'y'-letter:
SELECTEER StudentNaam VAN Studenten WAAR StudentNaam ZOALS '% y';
Resultaat:
- Haal de namen van studenten op die de 'n'-letter bevatten:
SELECTEER StudentNaam VAN Studenten WAAR StudentNaam ZOALS '% n%';
Resultaat:
"GLOB" - is gelijk aan de LIKE-operator, maar GLOB is hoofdlettergevoelig, in tegenstelling tot de LIKE-operator. De volgende twee opdrachten zullen bijvoorbeeld verschillende resultaten opleveren:
SELECTEER 'Jack' GLOB 'j%';SELECTEER 'Jack' ALS 'j%';
Dit geeft je:
- De eerste instructie retourneert 0 (false) omdat de GLOB-operator hoofdlettergevoelig is, dus 'j' is niet gelijk aan 'J'. De tweede instructie retourneert echter 1 (true) omdat de LIKE-operator niet hoofdlettergevoelig is, dus 'j' is gelijk aan 'J'.
Andere operators:
SQLite EN
Een logische operator die een of meer uitdrukkingen combineert. Het zal true retourneren, alleen als alle uitdrukkingen een "true" -waarde opleveren. Het zal echter alleen false retourneren als alle expressies een "false" -waarde opleveren.
Voorbeeld:
De volgende zoekopdracht zoekt naar studenten met StudentId> 5 en StudentName begint met de letter N, de geretourneerde studenten moeten aan de twee voorwaarden voldoen:
SELECTEER *VAN StudentenWAAR (StudentId> 5) EN (StudentName ALS 'N%');
Als uitvoer, in de bovenstaande schermafbeelding, krijgt u alleen "Nancy". Nancy is de enige student die aan beide voorwaarden voldoet.
SQLite OF
Een logische operator die een of meer expressies combineert, zodat als een van de gecombineerde operatoren true oplevert, deze true retourneert. Als alle expressies echter false opleveren, wordt false geretourneerd.
Voorbeeld:
De volgende zoekopdracht zoekt naar studenten met StudentId> 5 of StudentName die begint met de letter N, de teruggestuurde studenten moeten aan ten minste een van de voorwaarden voldoen:
SELECTEER *VAN StudentenWAAR (StudentId> 5) OF (StudentName ALS 'N%');
Dit geeft je:
Als uitvoer, in de bovenstaande schermafbeelding, geeft dit je de naam van een student met de letter "n" in hun naam plus de student-ID met waarde> 5.
Zoals u kunt zien, is het resultaat anders dan de query met de operator AND.
SQLite TUSSEN
BETWEEN wordt gebruikt om die waarden te selecteren die binnen een bereik van twee waarden vallen. Bijvoorbeeld: " X TUSSEN Y EN Z " retourneert true (1) als de waarde X tussen de twee waarden Y en Z ligt. Anders wordt false (0) geretourneerd.
Voorbeeld:
In de volgende voorbeeldquery zullen we een query schrijven om studenten met een Id-waarde tussen 5 en 8 te krijgen:
SELECTEER *VAN StudentenWAAR StudentId TUSSEN 5 EN 8;
Dit geeft alleen de studenten met id 5, 6, 7 en 8:
SQLite BINNEN
Heeft één operand en een lijst met operanden nodig. Het zal true retourneren als de eerste operandwaarde gelijk is aan een van de operanden in de lijst. De IN-operator retourneert true (1) als de lijst met operanden de eerste operandwaarde binnen zijn waarden bevat. Anders retourneert het false (0).
Zoals dit: " col IN (x, y, z) ". Dit is gelijk aan " (col = x) of (col = y) of (col = z) ".
Voorbeeld:
De volgende zoekopdracht selecteert alleen leerlingen met ID's 2, 4, 6, 8:
SELECTEER *VAN StudentenWAAR StudentId BINNEN (2, 4, 6, 8);
Zoals dit:
De vorige zoekopdracht geeft het exacte resultaat als de volgende zoekopdracht, omdat ze equivalent zijn:
SELECTEER *VAN StudentenWAAR (StudentId = 2) OF (StudentId = 4) OF (StudentId = 6) OF (StudentId = 8);
Beide queries geven de exacte output. Het verschil tussen de twee query's is echter dat de eerste query we de "IN" -operator gebruikten. In de tweede zoekopdracht hebben we meerdere "OF" -operatoren gebruikt.
De IN-operator is gelijk aan het gebruik van meerdere OR-operators. De " WHERE StudentId IN (2, 4, 6, 8) " is gelijk aan " WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8); "
Zoals dit:
SQLite NIET BINNEN
"NOT IN" -operand is het tegenovergestelde van de IN-operator. Maar met dezelfde syntaxis; er is één operand en een lijst met operanden voor nodig. Het zal true retourneren als de eerste operandwaarde niet gelijk is aan de waarde van een van de operanden in de lijst. dwz het retourneert true (0) als de lijst met operanden niet de eerste operand bevat. Zoals dit: " col NOT IN (x, y, z) ". Dit is gelijk aan " (col <> x) AND (col <> y) AND (col <> z) ".
Voorbeeld:
De volgende zoekopdracht selecteert leerlingen met ID's die niet gelijk zijn aan een van deze ID's 2, 4, 6, 8:
SELECTEER *VAN StudentenWAAR StudentId NIET BINNEN (2, 4, 6, 8);
Zoals dit
De vorige zoekopdracht geven we het exacte resultaat als de volgende zoekopdracht omdat ze equivalent zijn:
SELECTEER *VAN StudentenWAAR (StudentId <> 2) EN (StudentId <> 4) EN (StudentId <> 6) EN (StudentId <> 8);
Zoals dit:
In de bovenstaande schermafbeelding,
We hebben meerdere niet-gelijke operatoren "<>" gebruikt om een lijst met leerlingen te krijgen, die niet gelijk zijn aan geen van de volgende Id's 2, 4, 6 of 8. Deze query zal alle andere studenten retourneren behalve deze lijst met Id's.
SQLite BESTAAT
De EXISTS-operatoren accepteren geen operanden; het duurt slechts een SELECT-clausule erna. De EXISTS-operator retourneert true (1) als er rijen worden geretourneerd door de SELECT-clausule, en het zal false (0) retourneren als er helemaal geen rijen worden geretourneerd door de SELECT-clausule.
Voorbeeld:
In het volgende voorbeeld zullen we de naam van de afdeling selecteren, als de afdelings-ID in de studententabel voorkomt:
SELECTEER AfdelingsnaamVANAF Afdelingen AS dWAAR BESTAAT (SELECTEER Afdelings-ID VAN Studenten AS s WAAR d.DepartmentId = s.DepartmentId);
Dit geeft je:
Alleen de drie afdelingen " IT, natuurkunde en kunst " worden geretourneerd. En de afdelingsnaam " Math " wordt niet geretourneerd omdat er geen student in die afdeling is, dus de afdelings-ID bestaat niet in de studententabel. Daarom negeerde de EXISTS-operator de " Math " -afdeling.
SQLite NIET
Keert het resultaat om van de voorgaande operator die erna komt. Bijvoorbeeld:
- NOT BETWEEN - Het zal true retourneren als BETWEEN false retourneert en vice versa.
- NOT LIKE - Het zal true retourneren als LIKE false retourneert en vice versa.
- NOT GLOB - Het zal true retourneren als GLOB false retourneert en vice versa.
- NOT EXISTS - Het zal true retourneren als EXISTS false retourneert en vice versa.
Voorbeeld:
In het volgende voorbeeld gebruiken we de NOT-operator met de EXISTS-operator om de namen van de afdelingen op te halen die niet voorkomen in de Students-tabel, wat het omgekeerde resultaat is van de EXISTS-operator. De zoekopdracht wordt dus uitgevoerd via DepartmentId die niet in de afdelingstabel staan.
SELECTEER AfdelingsnaamVANAF Afdelingen AS dWAAR NIET BESTAAT (SELECTEER Afdelings-IDVAN Studenten AS sWAAR d.DepartmentId = s.DepartmentId);
Uitgang :
Alleen de afdeling " Wiskunde " wordt geretourneerd. Omdat de afdeling " Wiskunde " de enige afdeling is, bestaat die niet in de studententabel.
Beperken en bestellen
SQLite-volgorde
SQLite Order is om uw resultaat te sorteren op een of meer uitdrukkingen. Om de resultatenset te ordenen, moet u de ORDER BY-component als volgt gebruiken:
- Eerst moet u de ORDER BY-clausule specificeren.
- De ORDER BY-clausule moet aan het einde van de query worden opgegeven; alleen de LIMIT-clausule kan erna worden gespecificeerd.
- Geef de uitdrukking op om de gegevens mee te ordenen. Deze uitdrukking kan een kolomnaam of een uitdrukking zijn.
- Na de uitdrukking kunt u een optionele sorteerrichting specificeren. Ofwel DESC, om de gegevens aflopend te ordenen, of ASC om de gegevens oplopend te ordenen. Als u er geen van heeft gespecificeerd, worden de gegevens oplopend gesorteerd.
- U kunt meer uitdrukkingen specificeren door de "," tussen elkaar te gebruiken.
Voorbeeld
In het volgende voorbeeld selecteren we alle studenten gesorteerd op naam, maar in aflopende volgorde, en vervolgens op afdelingsnaam in oplopende volgorde:
SELECTEER s.StudentName, d.DepartmentNameVAN Studenten AS sINNER JOIN Afdelingen AS d ON s.DepartmentId = d.DepartmentIdORDER BY d.DepartmentName ASC, s.StudentName DESC;
Dit geeft je:
- SQLite rangschikt eerst alle studenten op afdelingsnaam in oplopende volgorde
- Vervolgens worden voor elke afdelingsnaam alle studenten onder die afdelingsnaam in aflopende volgorde met hun naam weergegeven
SQLite-limiet:
U kunt het aantal rijen beperken dat door uw SQL-query wordt geretourneerd door de LIMIT-clausule te gebruiken. LIMIT 10 geeft u bijvoorbeeld slechts 10 rijen en negeert alle andere rijen.
In de LIMIT-clausule kunt u een specifiek aantal rijen selecteren, beginnend vanaf een specifieke positie met behulp van de OFFSET-clausule. Bijvoorbeeld: " LIMIT 4 OFFSET 4 " negeert de eerste 4 rijen en geeft 4 rijen terug vanaf de vijfde rij, dus je krijgt rijen 5,6,7 en 8.
Merk op dat de OFFSET-clausule optioneel is, je kunt het schrijven als " LIMIT 4, 4 " en het geeft je de exacte resultaten.
Voorbeeld :
In het volgende voorbeeld retourneren we slechts 3 studenten, beginnend met de student-ID 5 met behulp van de vraag:
SELECTEER * UIT STUDENTEN LIMIET 4,3;
Dit geeft je slechts drie studenten vanaf rij 5. Dus het geeft je de rijen met StudentId 5, 6 en 7:
Duplicaten verwijderen
Als uw SQL-query dubbele waarden retourneert, kunt u het trefwoord " DISTINCT " gebruiken om die duplicaten te verwijderen en afzonderlijke waarden te retourneren. U kunt meer dan één kolom specificeren na het werk van de DISTINCT-toets.
Voorbeeld:
De volgende zoekopdracht levert dubbele "afdelingsnaamwaarden" op: Hier hebben we dubbele waarden met de namen IT, Natuurkunde en Kunst.
SELECTEER d.DepartmentNameVAN Studenten AS sINNER JOIN Afdelingen AS d ON s.DepartmentId = d.DepartmentId;
Dit geeft u dubbele waarden voor de afdelingsnaam:
Merk op dat er dubbele waarden zijn voor de afdelingsnaam. Nu gebruiken we het trefwoord DISTINCT met dezelfde zoekopdracht om die duplicaten te verwijderen en alleen unieke waarden te krijgen. Zoals dit:
SELECTEER DISTINCT d.DepartmentNameVAN Studenten AS sINNER JOIN Afdelingen AS d ON s.DepartmentId = d.DepartmentId;
Dit geeft u slechts drie unieke waarden voor de kolom met de afdelingsnaam:
Aggregaat
SQLite Aggregates zijn ingebouwde functies die in SQLite zijn gedefinieerd en die meerdere waarden van meerdere rijen in één waarde groeperen.
Dit zijn de aggregaten die worden ondersteund door SQLite:
SQLite AVG ()
Retourneerde het gemiddelde voor alle x-waarden.
Voorbeeld:
In het volgende voorbeeld krijgen we het gemiddelde cijfer dat studenten van alle examens halen:
SELECTEER AVG (Mark) UIT Marks;
Dit geeft je de waarde "18.375":
Deze resultaten zijn afkomstig van de optelling van alle markeringswaarden gedeeld door hun aantal.
COUNT () - COUNT (X) of COUNT (*)
Retourneert het totale aantal keren dat de x-waarde is verschenen. En hier zijn enkele opties die u kunt gebruiken met COUNT:
- AANTAL (x): telt alleen x-waarden, waarbij x een kolomnaam is. NULL-waarden worden genegeerd.
- AANTAL (*): tel alle rijen van alle kolommen.
- COUNT (DISTINCT x): U kunt een DISTINCT-sleutelwoord specificeren vóór de x, dat de telling van de verschillende waarden van x krijgt.
Voorbeeld
In het volgende voorbeeld krijgen we het totale aantal afdelingen met COUNT (DepartmentId), COUNT (*) en COUNT (DISTINCT DepartmentId) en hoe ze verschillen:
SELECTEER AANTAL (Afdeling-ID), AANTAL (DISTINCT Afdelings-ID), AANTAL (*) VAN Studenten;
Dit geeft je:
Als volgt:
- COUNT (DepartmentId) geeft u de telling van alle afdelings-ID's en negeert de null-waarden.
- COUNT (DISTINCT DepartmentId) geeft u verschillende waarden van DepartmentId, die er slechts 3 zijn. Dit zijn de drie verschillende waarden van de afdelingsnaam. Merk op dat de naam van de student 8 waarden bevat voor de naam van de afdeling. Maar alleen de drie verschillende waarden die wiskunde, IT en natuurkunde zijn.
- COUNT (*) telt het aantal rijen in de leerlingentabel dat 10 rijen is voor 10 leerlingen.
GROUP_CONCAT () - GROUP_CONCAT (X) of GROUP_CONCAT (X, Y)
De aggregatiefunctie GROUP_CONCAT voegt veelvouden waarden samen tot één waarde met een komma om ze te scheiden. Het heeft de volgende mogelijkheden:
- GROUP_CONCAT (X): Dit zal alle waarde van x samenvoegen tot één string, met de komma "," gebruikt als scheidingsteken tussen de waarden. NULL-waarden worden genegeerd.
- GROUP_CONCAT (X, Y): Dit zal de waarden van x samenvoegen tot één tekenreeks, waarbij de waarde van y wordt gebruikt als scheidingsteken tussen elke waarde in plaats van het standaardscheidingsteken ','. NULL-waarden worden ook genegeerd.
- GROUP_CONCAT (DISTINCT X): Dit zal alle verschillende waarden van x samenvoegen tot één string, met de komma "," gebruikt als scheidingsteken tussen de waarden. NULL-waarden worden genegeerd.
GROUP_CONCAT (DepartmentName) Voorbeeld
De volgende query zal alle waarden van de afdelingsnaam van de studenten en de afdelings-tabel samenvoegen tot één door komma's gescheiden tekenreeks. Dus in plaats van een lijst met waarden te retourneren, één waarde op elke rij. Het retourneert slechts één waarde op één rij, met alle waarden door komma's gescheiden:
SELECTEER GROUP_CONCAT (d.DepartmentName)VAN Studenten AS sINNER JOIN Afdelingen AS d ON s.DepartmentId = d.DepartmentId;
Dit geeft je:
Dit geeft u de lijst met 8 waarden van de namen van afdelingen, samengevoegd tot één door komma's gescheiden tekenreeks.
GROUP_CONCAT (DISTINCT Afdelingsnaam) Voorbeeld
Met de volgende query worden de verschillende waarden van de afdelingsnaam uit de tabel met studenten en afdelingen samengevoegd tot één door komma's gescheiden tekenreeks:
SELECTEER GROUP_CONCAT (DISTINCT d.DepartmentName)VAN Studenten AS sINNER JOIN Afdelingen AS d ON s.DepartmentId = d.DepartmentId;
Dit geeft je:
Merk op hoe het resultaat verschilt van het vorige resultaat; er werden slechts drie waarden geretourneerd die de namen van de verschillende afdelingen zijn, en de dubbele waarden zijn verwijderd.
GROUP_CONCAT (Afdelingsnaam, '&') Voorbeeld
Met de volgende query worden alle waarden van de kolom met de afdelingsnaam uit de tabel met studenten en afdelingen samengevoegd tot één tekenreeks, maar met het teken '&' in plaats van een komma als scheidingsteken:
SELECTEER GROUP_CONCAT (d.DepartmentName, '&')VAN Studenten AS sINNER JOIN Afdelingen AS d ON s.DepartmentId = d.DepartmentId;
Dit geeft je:
Merk op hoe het teken "&" wordt gebruikt in plaats van het standaardteken "," om de waarden te scheiden.
SQLite MAX () & MIN ()
MAX (X) geeft u de hoogste waarde uit de X-waarden. MAX retourneert een NULL-waarde als alle waarden van x null zijn. Terwijl MIN (X) u de kleinste waarde uit de X-waarden retourneert. MIN retourneert een NULL-waarde als alle waarden van X null zijn.
Voorbeeld
In de volgende zoekopdracht zullen we de MIN- en MAX-functies gebruiken om het hoogste cijfer en het laagste cijfer uit de tabel " Punten " te krijgen:
SELECT MAX (Mark), MIN (Mark) FROM Marks;
Dit geeft je:
SQLite SUM (x), Totaal (x)
Beiden retourneren de som van alle x-waarden. Maar ze verschillen in het volgende:
- SUM retourneert null als alle waarden null zijn, maar Total retourneert 0.
- TOTAL retourneert altijd drijvende-kommawaarden. SOM retourneert een geheel getal als alle x-waarden een geheel getal zijn. Als de waarden echter geen geheel getal zijn, wordt een drijvende-kommawaarde geretourneerd.
Voorbeeld
In de volgende zoekopdracht zullen we SUM en total gebruiken om de som van alle punten in de " Marks " -tabellen te krijgen:
SELECTEER SUM (Mark), TOTAL (Mark) VAN Marks;
Dit geeft je:
Zoals u kunt zien, geeft TOTAL altijd een drijvende komma terug. Maar SOM retourneert een geheel getal omdat de waarden in de kolom "Mark" mogelijk in gehele getallen zijn.
Verschil tussen SUM en TOTAL voorbeeld:
In de volgende query laten we het verschil zien tussen SUM en TOTAL wanneer ze de SUM of NULL-waarden krijgen:
SELECTEER SUM (Mark), TOTAL (Mark) FROM Marks WHERE TestId = 4;
Dit geeft je:
Merk op dat er geen markeringen zijn voor TestId = 4, dus er zijn null-waarden voor die test. SUM retourneert een null-waarde als een blanco, terwijl TOTAL 0 retourneert.
Groep OP
De GROUP BY-component wordt gebruikt om een of meer kolommen op te geven die worden gebruikt om de rijen in groepen te groeperen. De rijen met dezelfde waarden worden in groepen verzameld (gerangschikt).
Voor elke andere kolom die niet in de groep op kolommen is opgenomen, kunt u hiervoor een aggregatiefunctie gebruiken.
Voorbeeld:
De volgende vraag geeft u het totale aantal studenten dat in elke afdeling aanwezig is.
SELECTEER d.DepartmentName, COUNT (s.StudentId) AS StudentsCountVAN Studenten AS sINNER JOIN Afdelingen AS d ON s.DepartmentId = d.DepartmentIdGROEP OP d. Afdelingsnaam;
Dit geeft je:
De GROUPBY DepartmentName-clausule groepeert alle studenten in groepen, één voor elke afdelingsnaam. Voor elke groep "afdeling" telt het de studenten erop.
HAVING-clausule
Als u de groepen wilt filteren die worden geretourneerd door de GROUP BY-component, dan kunt u een "HAVING" -component specificeren met expressie na de GROUP BY. De uitdrukking wordt gebruikt om deze groepen te filteren.
Voorbeeld
In de volgende zoekopdracht zullen we die afdelingen selecteren waar slechts twee studenten op staan:
SELECTEER d.DepartmentName, COUNT (s.StudentId) AS StudentsCountVAN Studenten AS sINNER JOIN Afdelingen AS d ON s.DepartmentId = d.DepartmentIdGROEP OP d. AfdelingsnaamMET AANTAL (s.StudentId) = 2;
Dit geeft je:
De clausule HAVING COUNT (S.StudentId) = 2 filtert de geretourneerde groepen en retourneert alleen die groepen die precies twee studenten bevatten. In ons geval heeft de afdeling Letteren 2 studenten, dus het wordt weergegeven in de output.
SQLite Query & Subquery
Binnen elke query kunt u een andere query gebruiken in een SELECT, INSERT, DELETE, UPDATE of in een andere subquery.
Deze geneste query wordt een subquery genoemd. We zullen nu enkele voorbeelden zien van het gebruik van subquery's in de SELECT-clausule. In de zelfstudie Gegevens wijzigen zullen we echter zien hoe we subquery's kunnen gebruiken met de instructies INSERT, DELETE en UPDATE.
Subquery gebruiken in het FROM-componentvoorbeeld
In de volgende query zullen we een subquery opnemen in de FROM-component:
SELECTEERs.StudentName, t.MarkVAN Studenten AS sBINNENKOMSELECTEER StudentId, MarkVANAF Tests AS tINNER JOIN Markeert ALS m AAN t.TestId = m.TestId) OP s.StudentId = t.StudentId;
De vraag:
SELECTEER StudentId, MarkVANAF Tests AS tINNER JOIN Markeert ALS m AAN t.TestId = m.TestId
De bovenstaande query wordt hier een subquery genoemd omdat deze is genest in de FROM-component. Merk op dat we het een aliasnaam "t" hebben gegeven, zodat we kunnen verwijzen naar de kolommen die eruit worden geretourneerd in de query.
Deze vraag geeft u:
Dus in ons geval
- s.StudentName wordt geselecteerd uit de hoofdquery die de naam van studenten en
- t.Mark is geselecteerd uit de subquery; dat geeft cijfers die door elk van deze studenten zijn behaald
Subquery gebruiken in het WHERE-componentvoorbeeld
In de volgende query zullen we een subquery opnemen in de WHERE-clausule:
SELECTEER AfdelingsnaamVANAF Afdelingen AS dWAAR NIET BESTAAT (SELECTEER Afdelings-IDVAN Studenten AS sWAAR d.DepartmentId = s.DepartmentId);
De vraag:
SELECTEER Afdelings-IDVAN Studenten AS sWAAR d.DepartmentId = s.DepartmentId
De bovenstaande query wordt hier een subquery genoemd omdat deze is genest in de WHERE-component. De subquery retourneert de DepartmentId-waarden die worden gebruikt door de operator NOT EXISTS.
Deze vraag geeft u:
In de bovenstaande vraag hebben we de afdeling geselecteerd waar geen student voor is ingeschreven. Dat is de afdeling "Wiskunde" hier.
Set Operations - UNION, Intersect
SQLite ondersteunt de volgende SET-bewerkingen:
UNIE & UNIE ALLEN
Het combineert een of meer resultatensets (een groep rijen) die zijn geretourneerd door meerdere SELECT-instructies in één resultatenset.
UNION retourneert verschillende waarden. UNION ALL zal en zal echter geen duplicaten bevatten.
Merk op dat de kolomnaam de kolomnaam is die is opgegeven in de eerste SELECT-instructie.
UNION-voorbeeld
In het volgende voorbeeld krijgen we de lijst met DepartmentId uit de studententabel en de lijst met DepartmentId uit de departemententabel in dezelfde kolom:
SELECTEER DepartmentId AS DepartmentIdUnioned FROM StudentsUNIESELECTEER Afdelings-ID VAN Afdelingen;
Dit geeft je:
De query retourneert slechts 5 rijen die de verschillende afdelings-ID-waarden zijn. Let op de eerste waarde die de nulwaarde is.
SQLite UNION ALL Voorbeeld
In het volgende voorbeeld krijgen we de lijst met DepartmentId uit de studententabel en de lijst met DepartmentId uit de departemententabel in dezelfde kolom:
SELECTEER DepartmentId AS DepartmentIdUnioned FROM StudentsUNIE ALLENSELECTEER Afdelings-ID VAN Afdelingen;
Dit geeft je:
De query retourneert 14 rijen, 10 rijen uit de studententabel en 4 uit de departemententabel. Merk op dat er duplicaten zijn in de geretourneerde waarden. Merk ook op dat de kolomnaam degene was die was opgegeven in de eerste SELECT-instructie.
Laten we nu eens kijken hoe UNION all verschillende resultaten zal geven als we UNION ALL vervangen door UNION:
SQLite INTERSECT
Retourneert de waarden die in beide gecombineerde resultatensets voorkomen. Waarden die bestaan in een van de gecombineerde resultatensets, worden genegeerd.
Voorbeeld
In de volgende query zullen we de DepartmentId-waarden selecteren die voorkomen in zowel de tabellen Students als Departments in de DepartmentId-kolom:
SELECTEER Afdelings-ID VAN StudentenSnijdenSELECTEER Afdelings-ID VAN Afdelingen;
Dit geeft je:
De query retourneert slechts drie waarden 1, 2 en 3. Dit zijn de waarden die in beide tabellen voorkomen.
De waarden null en 4 zijn echter niet meegenomen omdat de null-waarde alleen in de studententabel voorkomt en niet in de afdelingstabel. En de waarde 4 bestaat in de afdelingslijst en niet in de studententabel.
Daarom werden zowel de waarden NULL als 4 genegeerd en niet opgenomen in de geretourneerde waarden.
BEHALVE
Stel dat u twee lijsten met rijen heeft, lijst1 en lijst2, en u wilt alleen de rijen van lijst1 die niet in lijst2 bestaat, u kunt de "EXCEPT" -clausule gebruiken. De clausule EXCEPT vergelijkt de twee lijsten en retourneert de rijen die in lijst1 bestaan en niet in lijst2.
Voorbeeld
In de volgende query zullen we de DepartmentId-waarden selecteren die in de departemententabel voorkomen en niet in de studententabel:
SELECTEER Afdelings-ID VAN AfdelingenBEHALVESELECTEER Afdeling-ID VAN Studenten;
Dit geeft je:
De query retourneert alleen de waarde 4. Dit is de enige waarde die voorkomt in de departemententabel en niet in de studententabel.
NULL-afhandeling
De waarde " NULL " is een speciale waarde in SQLite. Het wordt gebruikt om een waarde weer te geven die onbekend is of een ontbrekende waarde heeft. Merk op dat de null-waarde totaal anders is dan de " 0 " of de blanco "" -waarde. Omdat 0 en de blanco waarde een bekende waarde is, is de null-waarde echter onbekend.
NULL-waarden vereisen een speciale behandeling in SQLite, we zullen nu zien hoe we met de NULL-waarden moeten omgaan.
Zoek naar NULL-waarden
U kunt de operator voor normale gelijkheid (=) niet gebruiken om de null-waarden te doorzoeken. De volgende query zoekt bijvoorbeeld naar de studenten die een null DepartmentId-waarde hebben:
SELECTEER * UIT studenten WAAR DepartmentId = NULL;
Deze zoekopdracht levert geen resultaat op:
Omdat de NULL-waarde niet gelijk is aan een andere waarde die zelf een null-waarde bevat, heeft deze daarom geen resultaat geretourneerd.
- Om de query te laten werken, moet u de operator "IS NULL" gebruiken om als volgt naar null-waarden te zoeken:
SELECTEER * UIT studenten WAAR DepartmentId NULL IS;
Dit geeft je:
De query retourneert die studenten die een null DepartmentId-waarde hebben.
- Als je die waarden wilt krijgen die niet null zijn, dan moet je de " IS NOT NULL " operator als volgt gebruiken:
SELECTEER * UIT studenten WAAR DepartmentId NIET ONGELDIG IS;
Dit geeft je:
De query retourneert die studenten die geen NULL DepartmentId-waarde hebben.
Voorwaardelijke resultaten
Als u een lijst met waarden heeft en u wilt er een selecteren op basis van bepaalde voorwaarden. Daarvoor moet de voorwaarde voor die specifieke waarde waar zijn om te worden geselecteerd.
CASE-expressie evalueert deze lijst met voorwaarden voor alle waarden. Als de voorwaarde waar is, wordt die waarde geretourneerd.
Als je bijvoorbeeld een kolom 'Cijfer' hebt en je wilt als volgt een tekstwaarde selecteren op basis van de cijferwaarde:
- "Uitstekend" als het cijfer hoger is dan 85.
- "Zeer goed" als het cijfer tussen de 70 en 85 ligt.
- "Goed" als het cijfer tussen de 60 en 70 ligt.
Vervolgens kunt u de CASE-expressie gebruiken om dat te doen.
Dit kan worden gebruikt om enige logica in de SELECT-clausule te definiëren, zodat u bepaalde resultaten kunt selecteren, afhankelijk van bepaalde voorwaarden, zoals bijvoorbeeld een if-instructie.
De CASE-operator kan als volgt worden gedefinieerd met verschillende syntaxis:
- U kunt verschillende voorwaarden gebruiken:
GEVALWANNEER voorwaarde1 DAN resultaat1WANNEER voorwaarde2 DAN resultaat2WHEN condition3 THEN result3… ELSE resultnEINDE
- Of u kunt slechts één uitdrukking gebruiken en verschillende mogelijke waarden plaatsen om uit te kiezen:
CASE-uitdrukkingWANNEER waarde1 DAN resultaat1WANNEER waarde2 DAN resultaat2WANNEER waarde3 DAN resultaat3… ANDERS restulnEINDE
Merk op dat de ELSE-clausule optioneel is.
Voorbeeld
In het volgende voorbeeld gebruiken we de CASE- expressie met de waarde NULL in de kolom Afdelings-ID in de tabel Studenten om de tekst 'Geen afdeling' als volgt weer te geven:
SELECTEERStudenten naam,GEVALALS DepartmentId NULL IS DAN 'No Department'ELSE Afdeling IdEINDE ALS AfdelingsIdVAN studenten;
- De CASE-operator controleert de waarde van de DepartmentId of deze null is of niet.
- Als het een NULL-waarde is, wordt de letterlijke waarde 'No Department' geselecteerd in plaats van de DepartmentId-waarde.
- Als het geen null-waarde is, wordt de waarde van de kolom DepartmentId geselecteerd.
Dit geeft je de output zoals hieronder getoond:
Gemeenschappelijke tabelexpressie
Common Table Expressions (CTE's) zijn subquery's die met een bepaalde naam in de SQL-instructie zijn gedefinieerd.
Het heeft een voordeel ten opzichte van de subquery's omdat het wordt gedefinieerd op basis van de SQL-instructies, waardoor de query's gemakkelijker te lezen, te onderhouden en te begrijpen zijn.
Een algemene tabelexpressie kan worden gedefinieerd door de WITH-component als volgt voor een SELECT-instructie te plaatsen:
MET CTEnaamZOALSSELECT-instructieSELECTEER, UPDATE, INSERT, of update de instructie hier VANUIT CTE
De " CTE- naam" is elke naam die u aan de CTE kunt geven, u kunt deze gebruiken om er later naar te verwijzen. Merk op dat u de SELECT-, UPDATE-, INSERT- of DELETE-instructie op CTE's kunt definiëren
Laten we nu een voorbeeld bekijken van het gebruik van CTE in de SELECT-clausule.
Voorbeeld
In het volgende voorbeeld zullen we een CTE definiëren op basis van een SELECT-instructie en deze later gebruiken voor een andere query:
MET AllDepartmentsZOALSSELECTEER Afdelings-ID, AfdelingsnaamVAN AfdelingenSELECTEERs.StudentId,s.StudentName,a.DepartmentNameVAN Studenten AS sINNER JOIN AllDepartments ALS een ON s.DepartmentId = a.DepartmentId;
In deze query hebben we een CTE gedefinieerd en deze de naam " AllDepartments " gegeven. Deze CTE is gedefinieerd op basis van een SELECT-query:
SELECTEER Afdelings-ID, AfdelingsnaamVAN Afdelingen
Nadat we de CTE hadden gedefinieerd, hebben we deze gebruikt in de SELECT-query die erna komt.
Merk op dat gemeenschappelijke tabelexpressies geen invloed hebben op de uitvoer van de query. Het is een manier om een logische weergave of subquery te definiëren om ze in dezelfde query opnieuw te gebruiken. Veelgebruikte tabelexpressies zijn als een variabele die u declareert en hergebruikt deze als een subquery. Alleen de SELECT-instructie heeft invloed op de uitvoer van de query.
Deze vraag geeft u:
Geavanceerde vragen
Geavanceerde query's zijn query's die complexe joins, subquery's en enkele aggregaten bevatten. In de volgende sectie zien we een voorbeeld van een geavanceerde zoekopdracht:
Waar we de,
- Afdelingsnamen met alle studenten voor elke afdeling
- Leerlingnaam gescheiden door komma en
- Toont de afdeling met minimaal drie studenten
SELECTEERd. afdelingnaam,COUNT (s.StudentId) StudentsCount,GROUP_CONCAT (StudentName) AS StudentsVANAF Afdelingen AS dINNER LID WORDEN Studenten AS s OP s.DepartmentId = d.DepartmentIdGROEP OP d.DepartmentNameMET AANTAL (s.StudentId)> = 3;
We hebben een JOIN-clausule toegevoegd om de DepartmentName uit de Departments-tabel te halen. Daarna hebben we een GROUP BY-clausule toegevoegd met twee geaggregeerde functies:
- "COUNT" om de studenten voor elke afdelingsgroep te tellen.
- GROUP_CONCAT om studenten voor elke groep samen te voegen met een komma gescheiden in een string.
- Na de GROUP BY hebben we de HAVING-clausule gebruikt om de afdelingen te filteren en alleen die afdelingen te selecteren met minimaal 3 studenten.
Het resultaat is als volgt:
Overzicht:
Dit was een inleiding tot het schrijven van SQLite-query's en de basisprincipes van het opvragen van de database en hoe u de geretourneerde gegevens kunt filteren. U kunt nu uw eigen SQLite-query's schrijven.