SQLite Query: Selecteren, Waar, LIMIT, OFFSET, Aantal, Groeperen op

De onderstaande secties behandelen het lezen van gegevens met SELECT en FROM, het filteren van rijen met WHERE en de bijbehorende operatoren, het sorteren en beperken van resultaten, aggregatiefuncties, GROUP BY en HAVING, subquery's, setbewerkingen, NULL-afhandeling, CASE-expressies en common table expressions.

Schrijven SQL queries in een SQLite database, moet u weten hoe de clausules SELECT, FROM, WHERE, GROUP BY, ORDER BY en LIMIT werken en hoe u ze moet gebruiken.

Tijdens deze tutorial leert u hoe u deze clausules gebruikt en hoe u schrijft SQLite clausules.

Gegevens lezen met Select

De SELECT-clausule is de belangrijkste instructie die u gebruikt om een ​​zoekopdracht uit te voeren SQLite database. In de SELECT-clausule geeft u aan wat u wilt selecteren. Maar laten we vóór de select-clausule eens kijken waar we gegevens kunnen selecteren met behulp van de FROM-clausule.

De FROM-component wordt gebruikt om aan te geven waar u gegevens wilt selecteren. In de from-component kunt u een of meer tabellen of subquery's opgeven waaruit u de gegevens wilt selecteren, zoals we later in de tutorials zullen zien.

Houd er rekening mee dat u voor alle onderstaande voorbeelden sqlite3.exe moet uitvoeren en een verbinding met de voorbeeld-database moet openen als volgt:

Stap 1) In deze stap,

Open Deze computer en navigeer naar de volgende map: “C:\sqlite”. 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-component kunt u niet alleen een kolomnaam selecteren, maar u hebt ook veel andere opties om aan te geven wat u wilt selecteren. Zoals volgt:

SELECT *

Deze opdracht selecteert alle kolommen uit alle tabellen (of subquery's) waarnaar wordt verwezen in de FROM-clausule. Bijvoorbeeld:

SELECT *
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Hiermee worden alle kolommen geselecteerd uit zowel de tabellen Studenten als de Afdelingstabellen:

SELECT tabelnaam.*

Hierdoor worden alle kolommen uit alleen de tabel “tabelnaam” geselecteerd. Bijvoorbeeld:

SELECT Students.*
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Hierdoor worden alleen alle kolommen uit de studententabel geselecteerd:

Een letterlijke waarde

Een letterlijke waarde is een constante waarde die kan worden opgegeven in de select-instructie. U kunt letterlijke waarden normaal gesproken op dezelfde manier gebruiken als kolomnamen in de SELECT-component. Deze letterlijke waarden worden weergegeven voor elke rij uit de rijen die door de SQL-query worden geretourneerd.

Hier volgen enkele voorbeelden van verschillende letterlijke waarden die u kunt selecteren:

• Numeriek letterlijk – getallen in elk formaat zoals 1, 2.55, … enz.
• Letterlijke tekenreeksen – 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 tabel Studenten wilt selecteren, met een nieuwe kolom met de naam land die de waarde 'VS' bevat, kunt u dit doen:

SELECT *, 'USA' AS Country FROM Students;

Dit geeft je alle kolommen van de studenten, plus een nieuwe kolom “Land” zoals deze:

Houd er rekening mee dat deze nieuwe kolom Land niet daadwerkelijk een nieuwe kolom is die aan de tabel is toegevoegd. Het is een virtuele kolom die in de query is gemaakt voor het weergeven van de resultaten en die niet in de tabel wordt 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 opgegeven met het trefwoord “AS”.

Als je bijvoorbeeld de kolom StudentName wilt selecteren die moet worden geretourneerd met 'Student Name' in plaats van 'StudentName', kun je deze een alias als deze geven:

SELECT StudentName AS 'Student Name' FROM Students;

Hierdoor krijg je de namen van de studenten met de naam “Student Name” in plaats van “StudentName” zoals dit:

Merk op dat de kolomnaam nog steeds "StudentName" is; de kolom StudentName blijft hetzelfde, deze verandert niet door de alias.

De alias verandert de kolomnaam niet; het verandert alleen de weergavenaam in de SELECT-clausule.

Houd er ook rekening mee dat het trefwoord “AS” optioneel is, u kunt de aliasnaam zonder deze naam plaatsen, zoiets als dit:

SELECT StudentName 'Student Name' FROM Students;

En het geeft je exact dezelfde uitvoer als de vorige vraag:

U kunt tabellen ook aliassen geven, niet alleen kolommen. Met hetzelfde trefwoord “AS”. U kunt dit bijvoorbeeld doen:

SELECT s.* FROM Students AS s;

Hierdoor krijg je alle kolommen in de tabel Studenten:

Dit kan erg handig zijn als u meer dan één tabel koppelt; in plaats van de volledige tabelnaam in de query te herhalen, kunt u elke tabel een korte aliasnaam geven. Bijvoorbeeld in de volgende query:

SELECT Students.StudentName, Departments.DepartmentName
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Deze zoekopdracht selecteert elke studentnaam uit de tabel “Studenten” met de afdelingsnaam uit de tabel “Afdelingen”:

Dezelfde query kan echter als volgt worden geschreven:

SELECT s.StudentName, d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

We gaven de tabel 'Studenten' de alias 's' en de tabel 'Afdelingen' de alias 'd'. In plaats van de volledige tabelnaam te gebruiken, gebruikten we de aliassen om ernaar te verwijzen. Een INNER JOIN voegt twee of meer tabellen samen met behulp van een voorwaarde. In ons voorbeeld hebben we de tabel 'Studenten' samengevoegd met de tabel 'Afdelingen' via de kolom 'Afdelings-ID'. Er is ook een uitgebreide uitleg over de INNER JOIN te vinden in de documentatie.SQLite Sluit zich aan bij”Zelfstudie.

Dit geeft u de exacte uitvoer als de vorige query:

WAAR

Als u SQL-query's schrijft met alleen de SELECT-clausule en de FROM-clausule, 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 resultaten van de SQL-query te filteren. Zo werkt de WHERE-clausule:

• In de WHERE-clausule kunt u een “expressie” opgeven.
• Die expressie wordt geëvalueerd voor elke rij die wordt geretourneerd uit de tabel(len) die is opgegeven in de FROM-clausule.
• De expressie wordt geëvalueerd als een Booleaanse expressie, met als resultaat waar, onwaar of null.
• Vervolgens worden alleen de rijen waarvoor de expressie is geëvalueerd met een waarde True geretourneerd, en de rijen met false- of null-resultaten worden genegeerd en niet opgenomen in de resultatenset.

Om de resultatenset te filteren met behulp van de WHERE-component, moet u expressies en operatoren gebruiken.

Lijst met operatoren in SQLite en hoe ze te gebruiken

In het volgende gedeelte leggen we uit hoe u kunt filteren met behulp van expressies en operatoren.

Een expressie bestaat uit een of meer letterlijke waarden of kolommen die met elkaar worden gecombineerd via een operator.

Houd er rekening mee dat u expressies zowel in de SELECT-clausule als in de WHERE-clausule kunt gebruiken.

In de volgende voorbeelden zullen we de expressies en operatoren in zowel de select-clausule als de WHERE-clausule uitproberen. Om u te laten zien hoe ze presteren.

Er zijn verschillende soorten expressies en operatoren die u als volgt kunt opgeven:

SQLite de concatenatie-operator “||”

Deze operator wordt gebruikt om een ​​of meer letterlijke waarden of kolommen aan elkaar te koppelen. Het zal één string van resultaten produceren van alle aaneengeschakelde letterlijke waarden of kolommen. Bijvoorbeeld:

SELECT 'Id with Name: '|| StudentId || StudentName AS StudentIdWithName
FROM Students;

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 data type naar een ander gegevenstype.

Als je bijvoorbeeld een numerieke waarde hebt opgeslagen als een tekenreeks, zoals '12.5', en je wilt deze omzetten naar een numerieke waarde, dan kun je de CAST-operator gebruiken, bijvoorbeeld zo: 'CAST('12.5' ​​AS REAL)'. Of als je een decimale waarde hebt, zoals 12.5, en je wilt alleen het gehele deel, dan kun je deze converteren naar een geheel getal, bijvoorbeeld zo: 'CAST(12.5 AS INTEGER)'.

Voorbeeld

In de volgende opdracht proberen we verschillende waarden naar andere gegevenstypen te converteren:

SELECT CAST('12.5' AS REAL) ToReal, CAST(12.5 AS INTEGER) AS ToInteger;

Dit geeft je:

Het resultaat is als volgt:

• CAST('12.5' ​​AS REAL) – de waarde '12.5' ​​is een stringwaarde, deze wordt geconverteerd naar een REAL-waarde.
• CAST(12.5 AS INTEGER) – de waarde 12.5 is een decimale waarde, deze wordt geconverteerd naar een geheel getal. Het decimale deel wordt afgekapt en wordt 12.

SQLite Rekenkunde OperaTorens:

Neem twee of meer numerieke letterlijke waarden of numerieke kolommen en retourneer één numerieke waarde. De rekenkundige operatoren die worden ondersteund in SQLite zijn:

• Optellen “+” – geeft de som van de twee operanden.
• Subtractie “–” – subtracts de twee operanden en resulteert in het verschil.
• Vermenigvuldiging “*” – het product van de twee operanden.
• Rest (modulo) “%” – geeft de rest die overblijft na deling van de ene operand door de tweede operand.
• Deling “/” – geeft het quotiënt terug dat ontstaat door de linkeroperand te delen door de rechteroperand.

Voorbeeld:

In het volgende voorbeeld zullen we de vijf rekenkundige operatoren met letterlijke numerieke waarden in dezelfde SELECT-clausule uitproberen:

SELECT 25+6, 25-6, 25*6, 25%6, 25/6;

Dit geeft je:

Merk op hoe we hier een SELECT-instructie zonder FROM-clausule hebben gebruikt. En dit mag naar binnen SQLite zolang we letterlijke waarden selecteren.

SQLite Vergelijkingsoperatoren

Vergelijk twee operanden met elkaar en retourneer true of false als volgt:

• “<” – geeft true terug als de linkeroperand kleiner is dan de rechteroperand.
• “<=” – retourneert true als de linkeroperand kleiner dan of gelijk is aan de rechteroperand.
• “>” – geeft true terug als de linkeroperand groter is dan de rechteroperand.
• “>=” – retourneert true als de linkeroperand groter is dan of gelijk aan de rechteroperand.
• “=” en “==” – retourneert true als de twee operanden gelijk zijn. Merk op dat beide operatoren hetzelfde zijn en er geen verschil tussen is.
• “!=” en “<>” – retourneert true als de twee operanden niet gelijk zijn. Merk op dat beide operatoren hetzelfde zijn en er geen verschil tussen is.

Let daar op, SQLite drukt de ware waarde uit met 1 en de valse waarde met 0.

Voorbeeld:

SELECT
  10<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:

SQLite Operatoren voor patroonherkenning

“LIKE” wordt gebruikt voor patroonherkenning. Met “LIKE” 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 opgegeven:

• Bevat een patroon. Bijvoorbeeld: StudentName LIKE '%a%' – hiermee wordt gezocht naar studentennamen die de letter 'a' bevatten, ongeacht de positie in de kolom StudentName.
• Begint met het patroon. Bijvoorbeeld: “StudentName LIKE 'a%'” – zoek naar de namen van studenten die beginnen met de letter “a”.
• Het resultaat volgt het patroon. Bijvoorbeeld: “StudentName LIKE '%a'” – Zoek naar de namen van studenten die eindigen op de letter “a”.
• Zoek naar een willekeurig teken in een tekenreeks met behulp van de underscore-letter "_". Bijvoorbeeld: "StudentName LIKE 'J___'" – Zoek naar studentennamen van 4 tekens lang. De naam moet beginnen met de letter "J" en mag na de letter "J" maximaal drie andere tekens bevatten.

Voorbeelden van patroonmatching:

Krijg studentennamen die beginnen met de letter 'j':

SELECT StudentName FROM Students WHERE StudentName LIKE 'j%';

Resultaat:

Haal de namen van studenten op die eindigen met de letter 'y':

SELECT StudentName FROM Students WHERE StudentName LIKE '%y';

Resultaat:

Haal de namen van studenten op die de letter 'n' bevatten:

SELECT StudentName FROM Students WHERE StudentName LIKE '%n%';

Resultaat:

“GLOB” is gelijk aan de LIKE-operator, maar GLOB is hoofdlettergevoelig, in tegenstelling tot de LIKE-operator. De volgende twee commando's leveren bijvoorbeeld verschillende resultaten op:

SELECT 'Jack' GLOB 'j%';
SELECT 'Jack' LIKE '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 hoofdletterongevoelig is, dus 'j' is gelijk aan 'J'.

Andere operatoren:

SQLite EN

Een logische operator die een of meer expressies combineert. Het retourneert true, alleen als alle expressies een "true"-waarde opleveren. Het retourneert echter false alleen als alle expressies een "false"-waarde opleveren.

Voorbeeld:

Met de volgende query wordt gezocht naar studenten met een StudentId > 5 en StudentName begint met de letter N. De geretourneerde studenten moeten aan de twee voorwaarden voldoen:

SELECT *
FROM Students
WHERE (StudentId > 5) AND (StudentName LIKE 'N%');

Als uitvoer geeft dit in de bovenstaande schermafbeelding alleen “Nancy” weer. Nancy is de enige studente die aan beide voorwaarden voldoet.

SQLite OR

Een logische operator die een of meer expressies combineert, zodat als een van de gecombineerde operatoren true oplevert, deze true retourneert. Als echter alle expressies false opleveren, retourneert deze false.

Voorbeeld:

Met de volgende query wordt gezocht naar studenten met een StudentId > 5 of een StudentName die begint met de letter N. De geretourneerde studenten moeten aan ten minste één van de volgende voorwaarden voldoen:

SELECT *
FROM Students
WHERE (StudentId > 5) OR (StudentName LIKE 'N%');

Dit geeft je:

Als uitvoer krijgt u in de bovenstaande schermafbeelding de naam van een student met de letter “n” in zijn of haar naam plus de student-ID met waarde>5.

Zoals u kunt zien, is het resultaat anders dan de query met de AND-operator.

SQLite TUSSEN

BETWEEN wordt gebruikt om waarden te selecteren die binnen een bereik van twee waarden liggen. Bijvoorbeeld: "X BETWEEN Y AND Z" geeft true (1) terug als de waarde X tussen de twee waarden Y en Z ligt. Anders geeft het false (0) terug. "X BETWEEN Y AND Z" is equivalent aan "X >= Y AND X <= Z", waarbij X groter dan of gelijk aan Y en kleiner dan of gelijk aan Z moet zijn.

Voorbeeld:

In de volgende voorbeeldquery schrijven we een query om studenten met een Id-waarde tussen 5 en 8 op te halen:

SELECT *
FROM Students
WHERE StudentId BETWEEN 5 AND 8;

Dit geeft alleen de leerlingen met ID 5, 6, 7 en 8:

SQLite IN

Neemt één operand en een lijst met operanden. Het retourneert true als de eerste operandwaarde gelijk is aan een van de waarden van de operanden uit 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:

Met de volgende query worden alleen studenten met id's 2, 4, 6 en 8 geselecteerd:

SELECT *
FROM Students
WHERE StudentId IN(2, 4, 6, 8);

Soortgelijk:

De vorige query zal exact hetzelfde resultaat opleveren als de volgende query, omdat ze gelijkwaardig zijn:

SELECT *
FROM Students
WHERE (StudentId = 2) OR (StudentId =  4) OR (StudentId =  6) OR (StudentId = 8);

Beide query's geven de exacte output. Het verschil tussen de twee query's is echter dat we in de eerste query de "IN"-operator gebruikten. In de tweede query gebruikten we meerdere "OR"-operatoren.

De IN-operator is equivalent aan het gebruik van meerdere OR-operatoren. De uitdrukking "WHERE StudentId IN(2, 4, 6, 8)" is equivalent aan "WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);"

Soortgelijk:

SQLite NIET IN

De operator "NOT IN" is het tegenovergestelde van de operator "IN". De syntaxis is echter hetzelfde: hij neemt één operand en een lijst met operanden. Hij retourneert true als de waarde van de eerste operand niet gelijk is aan een van de waarden in de lijst. Dus, hij retourneert true (0) als de lijst met operanden de eerste operand niet bevat. Bijvoorbeeld: "col NOT IN(x, y, z)". Dit is equivalent aan "(col<>x) AND (col<>y) AND (col<>z)".

Voorbeeld:

Met de volgende query worden studenten geselecteerd met ID's die niet gelijk zijn aan een van de ID's 2, 4, 6, 8:

SELECT *
FROM Students
WHERE StudentId NOT IN(2, 4, 6, 8);

Net als deze

Bij de vorige query geven we exact hetzelfde resultaat als bij de volgende query, omdat ze gelijkwaardig zijn:

SELECT *
FROM Students
WHERE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);

Soortgelijk:

In de bovenstaande schermafbeelding,

We hebben meerdere niet-gelijke operatoren "<>" gebruikt om een ​​lijst met studenten te krijgen die niet gelijk zijn aan een van de volgende Id's 2, 4, 6 of 8. Deze query retourneert alle andere studenten dan deze lijst met Id's.

SQLite BESTAAT

De EXISTS-operatoren nemen geen operanden; er volgt alleen een SELECT-component. De EXISTS-operator retourneert true (1) als er rijen worden geretourneerd vanuit de SELECT-component, en retourneert false (0) als er helemaal geen rijen worden geretourneerd vanuit de SELECT-component.

Voorbeeld:

In het volgende voorbeeld selecteren we de naam van de afdeling, als de afdelings-id bestaat in de tabel met studenten:

SELECT DepartmentName
FROM Departments AS d
WHERE EXISTS (SELECT DepartmentId FROM Students AS s WHERE d.DepartmentId = s.DepartmentId);

Dit geeft je:

Alleen de drie afdelingen "IT, Natuurkunde en Kunst" worden geretourneerd. De afdeling "Wiskunde" wordt niet geretourneerd omdat er geen studenten in die afdeling zijn ingeschreven, waardoor het afdelings-ID niet in de studententabel voorkomt. Daarom heeft de EXISTS-operator de afdeling "Wiskunde" genegeerd.

SQLite NIET

Reverses is het resultaat van de voorafgaande operator die erna komt. Bijvoorbeeld:

• NOT BETWEEN – Het retourneert waar als BETWEEN false retourneert en vice versa.
• NOT LIKE – Het retourneert true als LIKE false retourneert en vice versa.
• NOT GLOB – Het retourneert true als GLOB false retourneert en omgekeerd.
• NOT EXISTS – Het retourneert waar als EXISTS false retourneert en omgekeerd.

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 in de tabel Students voorkomen, wat het omgekeerde resultaat is van de EXISTS-operator. De zoekopdracht wordt dus uitgevoerd via DepartmentId die niet in de tabel Department voorkomen.

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
                  FROM Students AS s
                  WHERE d.DepartmentId = s.DepartmentId);

Output:

Alleen de afdeling "Wiskunde" wordt weergegeven. Omdat "Wiskunde" de enige afdeling is, bestaat deze niet in de studententabel.

Beperken en ordenen

SQLite Bestel

SQLite Volgorde is het sorteren van uw resultaat op een of meer uitdrukkingen. Om de resultatenset te ordenen, moet u de ORDER BY-clausule als volgt gebruiken:

• Eerst moet u de ORDER BY-clausule opgeven.
• De ORDER BY-clausule moet aan het einde van de query worden opgegeven; alleen de LIMIT-clausule kan erna worden opgegeven.
• Geef de expressie op waarmee de gegevens moeten worden geordend. Deze expressie kan een kolomnaam of een expressie zijn.
• Na de expressie kunt u een optionele sorteerrichting opgeven. Ofwel DESC, om de gegevens aflopend te ordenen, of ASC om de gegevens oplopend te ordenen. Als u geen van deze opgeeft, worden de gegevens oplopend gesorteerd.
• U kunt meer expressies opgeven door de “,” tussen elkaar te gebruiken.

Voorbeeld

In het volgende voorbeeld selecteren we alle studenten op naam, maar dan in aflopende volgorde. Vervolgens selecteren we de studenten op afdelingsnaam, maar dan in oplopende volgorde:

SELECT s.StudentName, d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
ORDER BY d.DepartmentName ASC , s.StudentName DESC;

Dit geeft je:

• SQLite rangschikt eerst alle studenten op hun afdelingsnaam, in oplopende volgorde
• Vervolgens worden voor elke afdelingsnaam alle studenten onder die afdelingsnaam weergegeven in aflopende volgorde van hun naam.

SQLite Begrenzing:

U kunt het aantal rijen dat door uw SQL-query wordt geretourneerd, beperken 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 met behulp van de OFFSET-clausule een specifiek aantal rijen selecteren vanaf een bepaalde positie. Bijvoorbeeld: "LIMIT 4 OFFSET 4" negeert de eerste 4 rijen en retourneert 4 rijen vanaf de vijfde rij, dus u krijgt de rijen 5, 6, 7 en 8.

Houd er rekening mee dat de OFFSET-clausule optioneel is; u kunt deze ook schrijven als "LIMIT 4, 4" en u krijgt dan exact hetzelfde resultaat.

Voorbeeld:

In het volgende voorbeeld retourneren we alleen 3 studenten, beginnend met student-id 5, met behulp van de query:

SELECT * FROM Students LIMIT 4,3;

Dit geeft je slechts drie studenten vanaf rij 5. Je krijgt dus de rijen met StudentId 5, 6 en 7:

Duplicaten verwijderen

Als uw SQL-query dubbele waarden retourneert, kunt u het trefwoord "DISTINCT" gebruiken om deze duplicaten te verwijderen en alleen unieke waarden te retourneren. U kunt na het trefwoord DISTINCT meerdere kolommen specificeren.

Voorbeeld:

De volgende query retourneert dubbele “afdelingsnaamwaarden”: Hier hebben we dubbele waarden met de namen IT, Physics en Arts.

SELECT d.DepartmentName
FROM Students AS s
INNER JOIN Departments 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 zullen we het trefwoord DISTINCT gebruiken met dezelfde zoekopdracht om die duplicaten te verwijderen en alleen unieke waarden te verkrijgen. Soortgelijk:

SELECT DISTINCT d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Dit geeft u slechts drie unieke waarden voor de kolom Afdelingsnaam:

Aggregaat

SQLite Aggregaten zijn ingebouwde functies die zijn gedefinieerd in SQLite waarmee meerdere waarden van meerdere rijen in één waarde worden gegroepeerd.

Dit zijn de aggregaten die worden ondersteund door SQLite:

SQLite AVG()

Retourneert het gemiddelde voor alle x-waarden.

Voorbeeld:

In het volgende voorbeeld berekenen we het gemiddelde cijfer dat studenten voor alle examens moeten halen:

SELECT AVG(Mark) FROM Marks;

Dit geeft je de waarde “18.375”:

Deze resultaten komen uit de optelling van alle merkwaarden 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:

• COUNT(x): Telt alleen x-waarden, waarbij x een kolomnaam is. Het negeert NULL-waarden.
• COUNT(*): Tel alle rijen van alle kolommen.
• COUNT (DISTINCT x): U kunt een DISTINCT-trefwoord opgeven vóór de x, waarmee het aantal verschillende waarden van x wordt berekend.

Voorbeeld

In het volgende voorbeeld krijgen we het totale aantal afdelingen met COUNT(DepartmentId), COUNT(*) en COUNT(DISTINCT DepartmentId) en hoe ze verschillen:

SELECT COUNT(DepartmentId), COUNT(DISTINCT DepartmentId), COUNT(*) FROM Students;

Dit geeft je:

Als volgt:

• COUNT(DepartmentId) geeft u het aantal van alle afdelings-ID's en negeert de nulwaarden.
• COUNT(DISTINCT DepartmentId) geeft u verschillende waarden van DepartmentId, die slechts 3 zijn. Dit zijn de drie verschillende waarden van de afdelingsnaam. Merk op dat er 8 waarden voor de afdelingsnaam in de studentnaam voorkomen. Maar alleen de drie verschillende waarden: Wiskunde, IT en Natuurkunde.
• COUNT(*) telt het aantal rijen in de studententabel, dit zijn 10 rijen voor 10 studenten.

GROUP_CONCAT() – GROUP_CONCAT(X) of GROUP_CONCAT(X,Y)

De GROUP_CONCAT aggregate functie voegt meerdere waarden samen tot één waarde met een komma om ze te scheiden. Het heeft de volgende opties:

• GROUP_CONCAT(X): Hiermee worden alle waarden van x samengevoegd tot één tekenreeks, waarbij de komma “,” wordt gebruikt als scheidingsteken tussen de waarden. NULL-waarden worden genegeerd.
• GROUP_CONCAT(X, Y): Hiermee worden de waarden van x samengevoegd 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): Hiermee worden alle afzonderlijke waarden van x samengevoegd tot één tekenreeks, waarbij de komma “,” wordt gebruikt als scheidingsteken tussen de waarden. NULL-waarden worden genegeerd.

GROUP_CONCAT(Afdelingsnaam) Voorbeeld

De volgende query zal alle waarden van de afdelingsnaam van de studenten en de afdelingen tabel samenvoegen in één string, gescheiden door komma's. Dus in plaats van een lijst met waarden te retourneren, één waarde op elke rij. Het zal slechts één waarde op één rij retourneren, met alle waarden gescheiden door komma's:

SELECT GROUP_CONCAT(d.DepartmentName)
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Dit geeft je:

Dit geeft u de lijst met de waarden van de namen van 8 afdelingen, samengevoegd in één tekenreeks, gescheiden door komma's.

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:

SELECT GROUP_CONCAT(DISTINCT d.DepartmentName)
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Dit geeft je:

Merk op hoe het resultaat anders is dan het vorige resultaat; er werden slechts drie waarden geretourneerd, namelijk de namen van de verschillende afdelingen, en de dubbele waarden zijn verwijderd.

GROUP_CONCAT(Afdelingsnaam,'&') Voorbeeld

Met de volgende query worden alle waarden van de kolom afdelingsnaam uit de tabellen studenten en afdelingen samengevoegd tot één tekenreeks, maar met het teken '&' in plaats van een komma als scheidingsteken:

SELECT GROUP_CONCAT(d.DepartmentName, '&')
FROM Students AS s
INNER JOIN Departments 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 van elkaar te scheiden.

SQLite MAXIMUM MINIMUM()

MAX(X) retourneert u de hoogste waarde uit de X-waarden. MAX retourneert een NULL-waarde als alle waarden van x nul zijn. Terwijl MIN(X) u de kleinste waarde uit de X-waarden retourneert. MIN retourneert een NULL-waarde als alle waarden van X nul zijn.

Voorbeeld

In de volgende query gebruiken we de functies MIN en MAX om het hoogste en het laagste cijfer uit de tabel "Cijfers" te halen:

SELECT MAX(Mark), MIN(Mark) FROM Marks;

Dit geeft je:

SQLite SOM(x), Totaal(x)

Beide zullen de som van alle x-waarden retourneren. Maar ze verschillen in het volgende:

• SUM retourneert null als alle waarden nul zijn, maar Total retourneert 0.
• TOTAAL retourneert altijd waarden met drijvende komma. SOM retourneert een geheel getal als alle x-waarden een geheel getal zijn. Als de waarden echter geen geheel getal zijn, wordt er een drijvende-kommawaarde geretourneerd.

Voorbeeld

In de volgende query gebruiken we SUM en total om de som van alle cijfers in de tabel "Marks" te berekenen:

SELECT SUM(Mark), TOTAL(Mark) FROM Marks;

Dit geeft je:

Zoals u kunt zien, retourneert TOTAL altijd een drijvende komma. Maar SOM retourneert een geheel getal omdat de waarden in de kolom “Mark” mogelijk gehele getallen zijn.

Verschil tussen SOM en TOTAAL voorbeeld:

In de volgende query laten we het verschil zien tussen SUM en TOTAL wanneer ze de SUM van NULL-waarden krijgen:

SELECT SUM(Mark), TOTAL(Mark) FROM Marks WHERE TestId = 4;

Dit geeft je:

Houd er rekening mee dat er geen cijfers zijn voor TestId = 4, dus er zijn null-waarden voor die test. SOM retourneert een nulwaarde als blanco, terwijl TOTAAL 0 retourneert.

Groep DOOR

De GROUP BY-clausule 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 is opgenomen in de groep op kolommen, kunt u daarvoor een aggregatiefunctie gebruiken.

Voorbeeld:

Met de volgende query krijgt u het totale aantal studenten per afdeling te zien.

SELECT d.DepartmentName, COUNT(s.StudentId) AS StudentsCount
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
GROUP BY d. DepartmentName;

Dit geeft je:

De GROUPBY DepartmentName-clausule groepeert alle studenten in groepen, één voor elke afdelingsnaam. Voor elke groep van “afdelingen” telt het de studenten erop.

HAVING-clausule

Als u de groepen wilt filteren die door de GROUP BY-clausule worden geretourneerd, kunt u een “HAVING”-clausule opgeven met een expressie na de GROUP BY. De expressie wordt gebruikt om deze groepen te filteren.

Voorbeeld

In de volgende query selecteren we de afdelingen waar slechts twee studenten studeren:

SELECT d.DepartmentName, COUNT(s.StudentId) AS StudentsCount
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
GROUP BY d. DepartmentName
HAVING COUNT(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 Kunst 2 studenten, dus wordt dit weergegeven in de uitvoer.

SQLite Query en 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 voorbeeld van de FROM-clausule

In de volgende query zullen we een subquery opnemen in de FROM-component:

SELECT
  s.StudentName, t.Mark
FROM Students AS s
INNER JOIN
(
   SELECT StudentId, Mark
   FROM Tests AS t
   INNER JOIN Marks AS m ON t.TestId = m.TestId
)  ON s.StudentId = t.StudentId;

De vraag:

   SELECT StudentId, Mark
   FROM Tests AS t
   INNER JOIN Marks AS m ON t.TestId = m.TestId

De bovenstaande query wordt hier een subquery genoemd omdat deze is genest in de FROM-clausule. Merk op dat we het een aliasnaam “t” hebben gegeven, zodat we in de query kunnen verwijzen naar de kolommen die ervan worden geretourneerd.

Deze vraag levert je het volgende op:

Dus in ons geval,

• s.StudentName wordt geselecteerd uit de hoofdquery die de naam van studenten en
• t.Mark wordt geselecteerd uit de subquery; dat de cijfers oplevert die door elk van deze studenten zijn behaald

Subquery gebruiken in het WHERE-clausulevoorbeeld

In de volgende query zullen we een subquery opnemen in de WHERE-clausule:

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
                  FROM Students AS s
                  WHERE d.DepartmentId = s.DepartmentId);

De vraag:

SELECT DepartmentId
FROM Students AS s
WHERE 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 levert je het volgende op:

In de bovenstaande zoekopdracht hebben we de afdeling geselecteerd waarvoor geen student is ingeschreven. Dat is de afdeling 'Wiskunde' hier.

Set Operaties – UNION,Intersect

SQLite ondersteunt de volgende SET-bewerkingen:

UNIE & UNIE ALLEN

Het combineert een of meer resultatensets (een groep rijen) die worden geretourneerd door meerdere SELECT-instructies in één resultatenset.

UNION retourneert verschillende waarden. UNION ALL zal echter geen duplicaten bevatten.

Houd er rekening mee dat de kolomnaam de kolomnaam is die is opgegeven in de eerste SELECT-instructie.

UNIE Voorbeeld

In het volgende voorbeeld halen we de lijst met DepartmentId's op uit de tabel 'studenten' en de lijst met DepartmentId's uit de tabel 'afdelingen' in dezelfde kolom:

SELECT DepartmentId AS DepartmentIdUnioned FROM Students
UNION
SELECT DepartmentId FROM Departments;

Dit geeft je:

De query retourneert slechts vijf rijen die de verschillende afdelings-ID-waarden zijn. Let op de eerste waarde, die de nulwaarde is.

SQLite UNIE ALLE Voorbeeld

In het volgende voorbeeld halen we de lijst met DepartmentId's op uit de tabel 'studenten' en de lijst met DepartmentId's uit de tabel 'afdelingen' in dezelfde kolom:

SELECT DepartmentId AS DepartmentIdUnioned FROM Students
UNION ALL
SELECT DepartmentId FROM Departments;

Dit geeft je:

De query retourneert 14 rijen, 10 rijen uit de studententabel en 4 uit de afdelingentabel. Houd er rekening mee dat er duplicaten voorkomen in de geretourneerde waarden. Houd er ook rekening mee dat de kolomnaam de naam is die is opgegeven in de eerste SELECT-instructie.

Laten we nu eens kijken hoe UNION all verschillende resultaten zal opleveren als we UNION ALL vervangen door UNION:

SQLite SNIJDEN

Retourneert dat de waarden voorkomen in beide gecombineerde resultatensets. Waarden die in een van de gecombineerde resultatensets voorkomen, worden genegeerd.

Voorbeeld

In de volgende query selecteren we de DepartmentId-waarden die in de tabellen Students en Departments voorkomen in de kolom DepartmentId:

SELECT DepartmentId FROM Students
Intersect
SELECT DepartmentId FROM Departments;

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 opgenomen omdat de nulwaarde alleen in de studententabel voorkomt en niet in de afdelingentabel. En de waarde 4 komt voor in de afdelingentabel 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 hebt, list1 en list2, en u wilt alleen de rijen uit list1 die niet in list2 voorkomen, dan kunt u de clausule "EXCEPT" gebruiken. De clausule EXCEPT vergelijkt de twee lijsten en retourneert de rijen die in list1 voorkomen en niet in list2.

Voorbeeld

In de volgende query selecteren we de DepartmentId-waarden die in de tabel departments voorkomen en die niet in de tabel students voorkomen:

SELECT DepartmentId FROM Departments
EXCEPT
SELECT DepartmentId FROM Students;

Dit geeft je:

De query retourneert alleen de waarde 4. Dit is de enige waarde die voorkomt in de afdelingentabel, en niet in de studententabel.

NULL-afhandeling

De waarde "NULL" is een speciale waarde in SQLiteHet wordt gebruikt om een ​​waarde weer te geven die onbekend of ontbrekend is. Merk op dat een null-waarde totaal anders is dan een "0" of lege waarde. Omdat 0 en de lege waarde bekende waarden zijn, is een null-waarde onbekend.

NULL-waarden vereisen een speciale afhandeling in SQLite, we zullen nu zien hoe we met de NULL-waarden moeten omgaan.

Zoek naar NULL-waarden

U kunt de normale gelijkheidsoperator (=) niet gebruiken om de null-waarden te doorzoeken. De volgende query zoekt bijvoorbeeld naar de studenten met een null DepartmentId-waarde:

SELECT * FROM Students WHERE DepartmentId = NULL;

Deze zoekopdracht levert geen resultaat op:

Omdat de NULL-waarde niet gelijk is aan een andere waarde, inclusief een null-waarde, heeft deze geen enkel resultaat opgeleverd.

Om de query te laten werken, moet u echter de operator "IS NULL" gebruiken om naar null-waarden te zoeken, zoals hieronder weergegeven:

SELECT * FROM Students WHERE DepartmentId IS NULL;

Dit geeft je:

De query retourneert de studenten die een null DepartmentId-waarde hebben.

Als je de waarden wilt ophalen die niet null zijn, moet je de operator "IS NOT NULL" gebruiken, zoals dit:

SELECT * FROM Students WHERE DepartmentId IS NOT NULL;

Dit geeft je:

De query retourneert de studenten die geen NULL DepartmentId-waarde hebben.

Voorwaardelijke resultaten

Als u een lijst met waarden heeft en u een van deze waarden wilt selecteren op basis van bepaalde voorwaarden. Daarom moet de voorwaarde voor die specifieke waarde waar zijn om geselecteerd te kunnen worden.

De CASE-expressie evalueert deze lijst met voorwaarden voor alle waarden. Als de voorwaarde waar is, wordt die waarde geretourneerd.

Als u bijvoorbeeld een kolom 'Cijfer' hebt en u wilt een tekstwaarde selecteren op basis van de cijferwaarde, gaat u als volgt te werk:

– “Excellent” als het cijfer hoger is dan 85.

– “Zeer goed” als het cijfer tussen 70 en 85 ligt.

– “Goed” als het cijfer tussen 60 en 70 ligt.

Vervolgens kunt u daarvoor de CASE-expressie gebruiken.

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 de if-instructie.

De CASE-operator kan met verschillende syntaxisen worden gedefinieerd, zoals hieronder:

Je kunt verschillende voorwaarden gebruiken:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  WHEN condition3 THEN result3
  …
  ELSE resultn
END

Of u kunt slechts één uitdrukking gebruiken en verschillende mogelijke waarden opgeven waaruit u kunt kiezen:

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  WHEN value3 THEN result3
  …
  ELSE restuln
END

Houd er rekening mee dat de ELSE-clausule optioneel is.

Voorbeeld

In het volgende voorbeeld gebruiken we de CASE-expressie met de NULL-waarde in de kolom 'Department Id' in de tabel 'Students' om de tekst 'No Department' weer te geven, zoals hieronder:

SELECT
  StudentName,
  CASE
    WHEN DepartmentId IS NULL THEN 'No Department'
    ELSE DepartmentId
  END AS DepartmentId
FROM Students;

• De CASE-operator controleert of de waarde van de DepartmentId null is of niet.
• Als het een NULL-waarde is, wordt de letterlijke waarde 'No Department' geselecteerd in plaats van de DepartmentId-waarde.
• Als dit geen nulwaarde is, wordt de waarde van de kolom DepartmentId geselecteerd.

Dit geeft je de output zoals hieronder getoond:

Algemene tabeluitdrukking

Algemene tabelexpressies (CTE's) zijn subquery's die binnen de SQL-instructie met een bepaalde naam worden gedefinieerd.

Het heeft een voordeel ten opzichte van de subquery's omdat het wordt gedefinieerd op basis van de SQL-instructies en het de query's gemakkelijker maakt om te lezen, te onderhouden en te begrijpen.

Een algemene tabelexpressie kan worden gedefinieerd door de WITH-component vóór een SELECT-instructie te plaatsen, als volgt:

WITH CTEname
AS
(
   SELECT statement
)
SELECT, UPDATE, INSERT, or update statement here FROM CTE

De "CTEname" is een naam die u aan de CTE kunt geven; u kunt deze later gebruiken om ernaar te verwijzen. Houd er rekening mee dat u SELECT-, UPDATE-, INSERT- of DELETE-instructies kunt definiëren voor CTE's.

Laten we nu een voorbeeld bekijken van het gebruik van CTE in de SELECT-clausule.

Voorbeeld

In het volgende voorbeeld definiëren we een CTE vanuit een SELECT-instructie, die we later in een andere query gebruiken:

WITH AllDepartments
AS
(
  SELECT DepartmentId, DepartmentName
  FROM Departments
)
SELECT
  s.StudentId,
  s.StudentName,
  a.DepartmentName
FROM Students AS s
INNER JOIN AllDepartments AS a 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:

SELECT DepartmentId, DepartmentName
  FROM Departments

Nadat we de CTE hadden gedefinieerd, gebruikten we deze in de SELECT-query die erna kwam.

Houd er rekening mee dat algemene tabelexpressies geen invloed hebben op de uitvoer van de query. Het is een manier om een ​​logische weergave of subquery te definiëren, zodat u deze in dezelfde query kunt hergebruiken. Algemene tabelexpressies zijn als een variabele die u declareert en deze opnieuw gebruikt als subquery. Alleen de SELECT-instructie heeft invloed op de uitvoer van de query.

Deze vraag levert je het volgende op:

Geavanceerde vragen

Geavanceerde query's zijn query's die complexe joins, subquery's en sommige aggregaten bevatten. In de volgende sectie zien we een voorbeeld van een geavanceerde query:

Waar we de,

• Afdelingsnamen met alle studenten voor elke afdeling
• De naam van de leerling wordt gescheiden door een komma en
• Het tonen van de afdeling waar minimaal drie studenten aanwezig zijn

SELECT
  d.DepartmentName,
  COUNT(s.StudentId) StudentsCount,
  GROUP_CONCAT(StudentName) AS Students
FROM Departments AS d
INNER JOIN Students AS s ON s.DepartmentId = d.DepartmentId
GROUP BY d.DepartmentName
HAVING COUNT(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 aggregatiefuncties:

• “COUNT” om de studenten voor elke afdelingsgroep te tellen.
• GROUP_CONCAT om studenten voor elke groep samen te voegen, met komma's gescheiden in één tekenreeks.

Na de GROUP BY hebben we de HAVING-clausule gebruikt om de afdelingen te filteren en alleen die afdelingen te selecteren die ten minste drie studenten hebben.

Het resultaat is als volgt:

Veelgestelde vragen