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

Om SQL-query's te schrijven 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,

1. Open Deze computer en navigeer naar de volgende map:C:\sqlite"En
2. Open vervolgens “sqlite3.exe"

Stap 2) Open de databank “ZelfstudiesSampleDB.db" door 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 “Studenten naam“; de kolom StudentName is nog steeds 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 hebben de studententabel een alias “s” gegeven en de afdelingentabel een alias “d”.
• Vervolgens gebruikten we in plaats van de volledige naam van de tabel hun aliassen om ernaar te verwijzen.
• 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 met de kolom AfdelingId. Er is ook een diepgaande uitleg voor de INNER JOIN in de “SQLite Joins” tutorial.

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 resultatenset te filteren die wordt geretourneerd door de SQL-query. 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 “StudentIdMetNaam"

• De letterlijke tekenreekswaarde “Id met naam: "
• met de waarde van de “StudentID'kolom en
• met de waarde van “Studenten naamkolom

SQLite CAST-operator:

De CAST-operator wordt gebruikt om een ​​waarde van het ene gegevenstype naar het andere te converteren data type.

Als u bijvoorbeeld een numerieke waarde hebt opgeslagen als een tekenreekswaarde zoals deze " '12.5' "en u wilt het omzetten naar een numerieke waarde, dan kunt u de CAST-operator gebruiken om dit op deze manier te doen"CAST( '12.5' ​​ALS ECHT)“. Of als u een decimale waarde heeft zoals 12.5, en u alleen het gehele deel nodig heeft, kunt u dit naar een geheel getal zoals dit “CAST(12.5 AS INTEGER)” casten.

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:

Toevoeging "+” – geef de som van de twee operanden. Aftrekken “-” – trekt de twee operanden van elkaar af en resulteert in het verschil. Vermenigvuldiging “*” – het product van de twee operanden. Herinnering (modulo) “%” – geeft de rest die ontstaat door de ene operand door de tweede operand te delen. Divisie "/” – retourneert de quotiëntresultaten van het delen van de linker operand door de rechter operand.

Voorbeeld:

In het volgende voorbeeld zullen we de vijf rekenkundige operatoren met letterlijke numerieke waarden in dezelfde volgorde proberen.

selecteer clausule:

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:

"<” – retourneert true als de linker operand kleiner is dan de rechter operand. "<=” – retourneert true als de linkeroperand kleiner is dan of gelijk is aan de rechteroperand. ">” – retourneert true als de linker operand groter is dan de rechter operand. ">=” – retourneert true als de linkeroperand groter is dan of gelijk is aan de rechteroperand. "="En"==” – retourneert true als de twee operanden gelijk zijn. Merk op dat beide operatoren hetzelfde zijn en dat er geen verschil tussen hen is. "!="En"<>” – retourneert true als de twee operanden niet gelijk zijn. Merk op dat beide operatoren hetzelfde zijn en dat er geen verschil tussen hen 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 patroonafstemming. Gebruik makend van "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 patroon. Bijvoorbeeld, Studentnaam LIKE '%a%' – hierdoor wordt gezocht naar de namen van de studenten die de letter “a” bevatten op een willekeurige positie in de kolom StudentName.
• Begint met het patroon. Bijvoorbeeld, "Studentnaam LIKE 'a%'” – zoek naar de namen van de leerlingen die beginnen met de letter “a”.
• Eindigt met het patroon. Bijvoorbeeld, "Studentnaam LIKE '%a'” – Zoek naar de namen van de leerlingen die eindigen op de letter “a”.
• Het matchen van elk afzonderlijk teken in een tekenreeks met behulp van de onderstrepingsletter “_”. Bijvoorbeeld, "Studentnaam LIKE 'J___'” – Zoek naar namen van leerlingen met een lengte van 4 tekens. Het moet beginnen met de letter “J” en mag na de letter “J” nog drie extra tekens bevatten.

Voorbeelden van patroonmatching:

1. Krijg studentennamen die beginnen met de letter 'j':

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

   Resultaat:

   

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

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

   Resultaat:

   

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

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

   Resultaat:

   

“GLOB” – is equivalent aan de LIKE operator, maar GLOB is hoofdlettergevoelig, in tegenstelling tot de LIKE operator. De volgende twee opdrachten geven bijvoorbeeld verschillende resultaten:

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 TUSSEN Y EN Z" zal true (1) retourneren als de waarde X tussen de twee waarden Y en Z ligt. Anders zal het false (0) retourneren. "X TUSSEN Y EN Z" is gelijk aan "X >= Y EN X <= Z“, X moet groter zijn dan of gelijk zijn aan Y en X is kleiner dan of gelijk aan Z.

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

Soortgelijk: "kolom IN(x, y, z)“. Dit komt overeen met “ (kol=x) of (kol=y) of (kol=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 OF-operatoren. De “WHERE StudentId IN(2, 4, 6, 8)" is gelijk aan " WHERE (StudentId = 2) OF (StudentId = 4) OF (StudentId = 6) OF (StudentId = 8);"

Soortgelijk:

SQLite NIET IN

De operand "NOT IN" is het tegenovergestelde van de IN-operator. Maar met dezelfde syntaxis; het neemt één operand en een lijst met operanden. Het retourneert true als de eerste operandwaarde niet gelijk is aan de waarde van een van de operanden uit de lijst. d.w.z. het retourneert true (0) als de lijst met operanden de eerste operand niet bevat. Zoals dit: "col NIET IN(x, y, z)“. Dit komt overeen met “(kol<>x) EN (kol<>y) EN (kol<>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” zal worden geretourneerd. En de afdelingsnaam “Math” wordt niet geretourneerd omdat er geen student in die afdeling zit, dus de afdelings-ID bestaat niet in de tabel met studenten. Daarom negeerde de operator EXISTS de “Math" afdeling.

SQLite NIET

Reverses 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);

uitgang:

Alleen de afdeling “Math ” zal worden geretourneerd. Omdat de “Math” Afdeling is de enige afdeling die niet voorkomt 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, beginnend vanaf een specifieke positie. Bijvoorbeeld, "LIMIET 4 OFFSET 4” negeert de eerste 4 rijen en retourneert 4 rijen vanaf de vijfde rij, dus je krijgt rijen 5,6,7 en 8.

Merk op dat de OFFSET-clausule optioneel is, je kunt deze schrijven als “LIMIT 4, 4' en het geeft u de exacte resultaten.

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 de “DISTINCT”-zoekwoord om deze duplicaten te verwijderen en afzonderlijke waarden terug te geven. U kunt meer dan één kolom opgeven na het DISTINCT-sleutelwerk.

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 cijfer en het laagste cijfer te verkrijgen van de "Marks" tafel:

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 punten in de "Marks” tafels:

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.

Zet de 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 "NULL'waarde 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 nulwaarde totaal anders is dan “0' of een blanco ''-waarde. Omdat 0 en de blanco waarde een bekende waarde zijn, is de nulwaarde echter 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 echter te laten werken, moet u de "IS NIETS" operator om naar null-waarden te zoeken als volgt:

SELECT * FROM Students WHERE DepartmentId IS NULL;

Dit geeft je:

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

• Als je die waarden wilt krijgen die niet nul zijn, dan moet je de “IS NIET NUL” operator zoals deze:

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:

1. Je kunt verschillende voorwaarden gebruiken:

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

2. 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 uitdrukking met NULL waarde in de kolom afdelings-ID in de tabel Studenten om de tekst 'Geen afdeling' als volgt weer te geven:

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 "CTEnaam" is elke naam die u kunt geven voor de CTE, u kunt het 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 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 zoekopdracht hebben we een CTE gedefinieerd en deze de naam gegeven “Alle afdelingen“. 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;

Wij hebben een toegevoegd AANMELDEN -clausule om de Afdelingsnaam uit de tabel Afdelingen op 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:

Samenvatting

Dit was een introductie tot het schrijven SQLite query's en de basisbeginselen van het uitvoeren van query's op de database en hoe u de geretourneerde gegevens kunt filteren. U kunt nu uw eigen schrijven SQLite queries.