SQLite Forespørgsel: Vælg, Hvor, LIMIT, OFFSET, Count, Group By

Af: Marcus Allen Marcus Allen
Hours Opdateret

At skrive SQL-forespørgsler i en SQLite database, skal du vide, hvordan SELECT-, FROM-, WHERE-, GROUP BY-, ORDER BY- og LIMIT-klausulerne fungerer, og hvordan du bruger dem.

I løbet af denne øvelse lærer du, hvordan du bruger disse klausuler, og hvordan du skriver SQLite klausuler.

Læsning af data med Select

SELECT-sætningen er hovedsætningen, du bruger til at forespørge på en SQLite database. I SELECT-sætningen angiver du, hvad du skal vælge. Men før select-klausulen, lad os se, hvorfra vi kan vælge data ved hjælp af FROM-klausulen.

FROM-sætningen bruges til at angive, hvor du vil vælge data. I fra-klausulen kan du angive en eller flere tabeller eller underforespørgsler til at vælge data fra, som vi vil se senere i vejledningerne.

Bemærk, at for alle de følgende eksempler skal du køre sqlite3.exe og åbne en forbindelse til eksempeldatabasen som flydende:

Trin 1) I dette trin,

  1. Åbn Denne computer og naviger til følgende mappe "C:\sqlite"Og
  2. Åbn derefter "sqlite3.exe"

Læsning af data med Select

Trin 2) Åbn databasen "TutorialsSampleDB.db” ved følgende kommando:

Læsning af data med Select

Nu er du klar til at køre enhver form for forespørgsel på databasen.

I SELECT-sætningen kan du ikke kun vælge et kolonnenavn, men du har en masse andre muligheder for at angive, hvad du skal vælge. Som følgende:

VÆLG *

Denne kommando vil vælge alle kolonnerne fra alle de refererede tabeller (eller underforespørgsler) i FROM-sætningen. For eksempel:

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

Dette vil vælge alle kolonnerne fra både tabellerne studerende og afdelingstabellerne:

Læsning af data med Select

VÆLG tabelnavn.*

Dette vil vælge alle kolonnerne fra kun tabellen "tabelnavn". For eksempel:

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

Dette vil kun vælge alle kolonnerne fra elevtabellen:

Læsning af data med Select

En bogstavelig værdi

En literal værdi er en konstant værdi, der kan angives i select-sætningen. Du kan bruge bogstavelige værdier normalt på samme måde, som du bruger kolonnenavne i SELECT-sætningen. Disse bogstavelige værdier vil blive vist for hver række fra de rækker, der returneres af SQL-forespørgslen.

Her er nogle eksempler på forskellige bogstavelige værdier, som du kan vælge:

  • Numerisk bogstav – tal i ethvert format som 1, 2.55, … osv.
  • Streng bogstaver – Enhver streng 'USA', 'dette er en eksempeltekst', … osv.
  • NULL – NULL værdi.
  • Current_TIME – Det vil give dig det aktuelle klokkeslæt.
  • CURRENT_DATE – dette giver dig den aktuelle dato.

Dette kan være praktisk i nogle situationer, hvor du skal vælge en konstant værdi for alle de returnerede rækker. For eksempel, hvis du vil vælge alle eleverne fra Elever-tabellen med en ny kolonne kaldet et land, som indeholder værdien "USA", kan du gøre dette:

SELECT *, 'USA' AS Country FROM Students;

Dette vil give dig alle elevernes kolonner plus en ny kolonne "Land" som denne:

Læsning af data med Select

Bemærk, at denne nye kolonne Land faktisk ikke er en ny kolonne tilføjet til tabellen. Det er en virtuel kolonne, der er oprettet i forespørgslen til at vise resultaterne, og den vil ikke blive oprettet på bordet.

Navne og Alias

Aliaset er et nyt navn til kolonnen, der lader dig vælge kolonnen med et nyt navn. Kolonnealiasserne er specificeret ved hjælp af nøgleordet "AS".

For eksempel, hvis du vil vælge kolonnen StudentName, der skal returneres med "Student Name" i stedet for "StudentName", kan du give den et alias som dette:

SELECT StudentName AS 'Student Name' FROM Students;

Dette vil give dig elevernes navne med navnet "Student Name" i stedet for "StudentName" som dette:

Navne og Alias

Bemærk, at kolonnenavnet stadig "Elevnavn"; kolonnen StudentName er stadig den samme, den ændres ikke af aliaset.

Aliaset ændrer ikke kolonnenavnet; det vil blot ændre visningsnavnet i SELECT-sætningen.

Bemærk også, at nøgleordet "AS" er valgfrit, du kan sætte aliasnavnet uden det, noget som dette:

SELECT StudentName 'Student Name' FROM Students;

Og det vil give dig nøjagtig samme output som den forrige forespørgsel:

Navne og Alias

Du kan også give tabeller aliaser, ikke kun kolonner. Med samme søgeord "AS". For eksempel kan du gøre dette:

SELECT s.* FROM Students AS s;

Dette vil give dig alle kolonnerne i tabellen Elever:

Navne og Alias

Dette kan være meget nyttigt, hvis du deltager i mere end ét bord; i stedet for at gentage det fulde tabelnavn i forespørgslen, kan du give hver tabel et kort aliasnavn. For eksempel i følgende forespørgsel:

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

Denne forespørgsel vil vælge hvert elevnavn fra "Studenter"-tabellen med dets afdelingsnavn fra "Afdelinger"-tabellen:

Navne og Alias

Den samme forespørgsel kan dog skrives sådan:

SELECT s.StudentName, d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
  • Vi gav elevtabellen et alias "s" og afdelingstabellen et alias "d".
  • Så i stedet for at bruge hele tabellens navn, brugte vi deres aliaser til at henvise til dem.
  • INNER JOIN forbinder to eller flere tabeller ved hjælp af en betingelse. I vores eksempel forenede vi Studenter-tabel med Departments-tabel med DepartmentId-kolonnen. Der er også en uddybende forklaring på INNER JOIN i "SQLite Deltager” tutorial.

Dette vil give dig det nøjagtige output som den forrige forespørgsel:

Navne og Alias

RELATEREDE ARTIKLER

HVOR

At skrive SQL-forespørgsler ved at bruge SELECT-sætning alene med FROM-sætningen, som vi så i det foregående afsnit, vil give dig alle rækkerne fra tabellerne. Men hvis du vil filtrere de returnerede data, skal du tilføje en "WHERE"-klausul.

WHERE-sætningen bruges til at filtrere det resultatsæt, der returneres af SQL forespørgsel. Sådan fungerer WHERE-klausulen:

  • I WHERE-sætningen kan du angive et "udtryk".
  • Dette udtryk vil blive evalueret for hver række, der returneres fra den eller de tabeller, der er specificeret i FROM-sætningen.
  • Udtrykket vil blive evalueret som et boolsk udtryk, med resultatet enten sandt, falsk eller null.
  • Derefter returneres kun rækker, for hvilke udtrykket blev evalueret med en sand værdi, og dem med falske eller null-resultater vil blive ignoreret og ikke inkluderet i resultatsættet.
  • For at filtrere resultatsættet ved hjælp af WHERE-sætningen, skal du bruge udtryk og operatorer.

Liste over operatører i SQLite og hvordan man bruger dem

I det følgende afsnit vil vi forklare, hvordan du kan filtrere ved hjælp af udtryk og operatorer.

Udtryk er en eller flere bogstavelige værdier eller kolonner kombineret med hinanden med en operator.

Bemærk, at du kan bruge udtryk i både SELECT-udtrykket og i WHERE-udtrykket.

I de følgende eksempler vil vi prøve udtrykkene og operatorerne i både select-udtrykket og WHERE-udtrykket. For at vise dig, hvordan de klarer sig.

Der er forskellige typer udtryk og operatorer, som du kan angive som følger:

SQLite sammenkædningsoperatoren "||"

Denne operator bruges til at sammenkæde en eller flere bogstavelige værdier eller kolonner med hinanden. Det vil producere en række resultater fra alle sammenkædede bogstavelige værdier eller kolonner. For eksempel:

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

Dette vil sammenkædes i et nyt alias "StudentIdWithName"

  • Den bogstavelige strengværdi "ID med navn: "
  • med værdien af ​​"Studiekort” kolonne og
  • med værdien fra "Elevnavn” kolonne

SQLite sammenkædningsoperatoren '||'

SQLite CAST-operatør:

CAST-operatoren bruges til at konvertere en værdi fra en datatype til en anden datatype.

For eksempel, hvis du har en numerisk værdi gemt som en strengværdi som denne " '12.5' " og du vil konvertere det til en numerisk værdi, kan du bruge CAST-operatoren til at gøre dette på denne måde "CAST( '12.5' SOM RIGTIG)“. Eller hvis du har en decimalværdi som f.eks. 12.5, og du kun skal hente heltalsdelen, kan du caste den til et heltal som dette "CAST(12.5 AS INTEGER)".

Eksempel

I følgende kommando vil vi forsøge at konvertere forskellige værdier til andre datatyper:

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

Dette vil give dig:

SQLite CAST OperaTor

Resultatet er som følger:

  • CAST('12.5' ​​AS REAL) – værdien '12.5' ​​er en strengværdi, den vil blive konverteret til en REAL værdi.
  • CAST(12.5 SOM HELTAL) – værdien 12.5 er en decimalværdi, den vil blive konverteret til at være en heltalsværdi. Decimaldelen afkortes, og den bliver 12.

SQLite Aritmetik Operators:

Tag to eller flere numeriske bogstavelige værdier eller numeriske kolonner og returner en numerisk værdi. De aritmetiske operatorer understøttet i SQLite er:

  • Tilføjelse "+” – giv summen af ​​de to operander.
  • subtraktion"-” – trækker de to operander fra og resulterer i forskellen.
  • Multiplikation "*” – produktet af de to operander.
  • Påmindelse (modulo) "%” – giver resten, der er resultatet af at dividere en operand med den anden operand.
  • Division "/” – returnerer kvotientresultaterne ved at dividere venstre operand med højre operand.

Eksempel:

I det følgende eksempel vil vi prøve de fem aritmetiske operatorer med bogstavelige numeriske værdier i samme

vælg klausul:

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

Dette vil give dig:

SQLite Aritmetik Operatorer

Bemærk, hvordan vi brugte en SELECT-sætning uden en FROM-sætning her. Og det er tilladt ind SQLite så længe vi vælger bogstavelige værdier.

SQLite Sammenligningsoperatører

Sammenlign to operander med hinanden og returner en sand eller falsk som følger:

  • "<” – returnerer sand, hvis venstre operand er mindre end højre operand.
  • "<=” – returnerer sand, hvis venstre operand er mindre end eller lig med højre operand.
  • ">” – returnerer sand, hvis venstre operand er større end højre operand.
  • ">=” – returnerer sand, hvis venstre operand er større end eller lig med højre operand.
  • "="Og"==” – returnerer sand, hvis de to operander er lige store. Bemærk, at begge operatører er de samme, og der er ingen forskel på dem.
  • "!="Og"<>” – returnerer sand, hvis de to operander ikke er ens. Bemærk, at begge operatører er de samme, og der er ingen forskel på dem.

Noter det, SQLite udtrykker den sande værdi med 1 og den falske værdi med 0.

Eksempel:

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 '<>';

Dette vil give noget som dette:

SQLite Sammenligning Operatorer

SQLite Mønstermatchende operatorer

"LIKE” – bruges til mønstertilpasning. Ved brug af "lignende", kan du søge efter værdier, der matcher et mønster angivet ved hjælp af et jokertegn.

Operanden til venstre kan enten være en bogstavlig strengværdi eller en strengkolonne. Mønsteret kan specificeres som følger:

  • Indeholder mønster. For eksempel, Elevnavn LIKE '%a%' – dette vil søge efter elevernes navne, der indeholder bogstavet "a" på en hvilken som helst position i kolonnen Elevnavn.
  • Starter med mønsteret. For eksempel, "Elevnavn SOM "a%"” – søg på elevernes navne, der starter med bogstavet ”a”.
  • Slutter med mønsteret. For eksempel, "Elevnavn LIKE '%a'” – Søg efter elevernes navne, der ender med bogstavet “a”.
  • Matcher ethvert enkelt tegn i en streng ved hjælp af understregningsbogstavet "_". For eksempel, "Elevnavn SOM "J___"” – Søg efter elevernes navne på 4 tegn. Det skal starte med bogstavet "J" og kan have yderligere tre tegn efter bogstavet "J".

Eksempler på mønstermatchning:

  1. Få elevernes navne, der starter med bogstavet 'j': 
    SELECT StudentName FROM Students WHERE StudentName LIKE 'j%';

    Resultat:

    SQLite Mønstertilpasning Operatorer

  2. Få elevernes navne til at slutte med bogstavet 'y': 
    SELECT StudentName FROM Students WHERE StudentName LIKE '%y';

    Resultat:

    SQLite Mønstertilpasning Operatorer

  3. Få elevernes navne, der indeholder bogstavet 'n': 
    SELECT StudentName FROM Students WHERE StudentName LIKE '%n%';

    Resultat:

    SQLite Mønstertilpasning Operatorer

"GLOB" – svarer til LIKE-operatoren, men GLOB skelner mellem store og små bogstaver i modsætning til LIKE-operatoren. For eksempel vil følgende to kommandoer returnere forskellige resultater:

SELECT 'Jack' GLOB 'j%';
SELECT 'Jack' LIKE 'j%';

Dette vil give dig:

SQLite Mønstertilpasning Operatorer

  • Den første sætning returnerer 0(falsk), fordi GLOB-operatoren skelner mellem store og små bogstaver, så 'j' er ikke lig med 'J'. Den anden sætning vil dog returnere 1 (sand), fordi LIKE-operatoren er ufølsom mellem store og små bogstaver, så 'j' er lig med 'J'.

Andre operatører:

SQLite AND

En logisk operator, der kombinerer et eller flere udtryk. Det vil kun returnere sandt, hvis alle udtryk giver en "sand" værdi. Det vil dog kun returnere falsk, hvis alle udtryk giver en "falsk" værdi.

Eksempel:

Følgende forespørgsel vil søge efter elever, der har StudentId > 5 og StudentName begynder med bogstavet N, de returnerede elever skal opfylde de to betingelser:

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

SQLite AND OperaTor

Som output, i ovenstående skærmbillede, vil dette kun give dig "Nancy". Nancy er den eneste studerende, der opfylder begge betingelser.

SQLite OR

En logisk operator, der kombinerer et eller flere udtryk, så hvis en af ​​de kombinerede operatorer giver sand, så vil den returnere sand. Men hvis alle udtryk giver falsk, vil det returnere falsk.

Eksempel:

Følgende forespørgsel vil søge efter elever, der har StudentId > 5 eller StudentName begynder med bogstavet N, de returnerede elever skal opfylde mindst én af betingelserne:

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

Dette vil give dig:

SQLite OR OperaTor

Som et output, i ovenstående skærmbillede, vil dette give dig navnet på en elev med bogstavet "n" i deres navn plus elev-id'et med værdi >5.

Som du kan se, er resultatet anderledes end forespørgslen med AND-operator.

SQLite MELLEM

BETWEEN bruges til at vælge de værdier, der er inden for et interval på to værdier. For eksempel, "X MELLEM Y OG Z” vil returnere sand (1), hvis værdien X er mellem de to værdier Y og Z. Ellers vil den returnere falsk (0). “X MELLEM Y OG Z" svarer til "X >= Y OG X <= Z", X skal være større end eller lig med Y, og X er mindre end eller lig med Z.

Eksempel:

I følgende eksempelforespørgsel vil vi skrive en forespørgsel for at få elever med Id-værdi mellem 5 og 8:

SELECT *
FROM Students
WHERE StudentId BETWEEN 5 AND 8;

Dette vil kun give eleverne med id 5, 6, 7 og 8:

SQLite MELLEM OperaTor

SQLite IN

Tager en operand og en liste over operander. Det vil returnere sandt, hvis den første operandværdi er lig med en af ​​operandernes værdi fra listen. IN-operatoren returnerer sand (1), hvis listen over operander indeholder den første operandværdi inden for dens værdier. Ellers vil det returnere falsk (0).

Sådan her: "col IN(x, y, z)". Dette svarer til ” (col=x) eller (col=y) eller (col=z) ".

Eksempel:

Følgende forespørgsel vil kun vælge elever med id 2, 4, 6, 8:

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

Sådan her:

SQLite IN OperaTor

Den forrige forespørgsel vil give det nøjagtige resultat som følgende forespørgsel, fordi de er ækvivalente:

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

Begge forespørgsler giver det nøjagtige output. Men forskellen mellem de to forespørgsler er, den første forespørgsel, vi brugte "IN"-operatoren. I den anden forespørgsel brugte vi flere "ELLER"-operatorer.

IN-operatoren svarer til at bruge flere OR-operatorer. Den "WHERE StudentId IN(2, 4, 6, 8)" svarer til " HVOR (StudentId = 2) ELLER (StudentId = 4) ELLER (StudentId = 6) ELLER (StudentId = 8);"

Sådan her:

SQLite IN OperaTor

SQLite IKKE I

"NOT IN" operand er det modsatte af IN-operatoren. Men med samme syntaks; det kræver en operand og en liste over operander. Det vil returnere sandt, hvis den første operandværdi ikke er lig med en af ​​operandernes værdi fra listen. dvs. den vil returnere sand (0), hvis listen over operander ikke indeholder den første operand. Sådan: "col NOT IN(x, y, z)“. Dette svarer til "(col<>x) AND (col<>y) AND (col<>z)".

Eksempel:

Følgende forespørgsel vil vælge elever med id'er, der ikke er lig med et af disse id'er 2, 4, 6, 8:

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

Ligesom dette

SQLite IKKE I OperaTor

Den forrige forespørgsel giver vi det nøjagtige resultat som følgende forespørgsel, fordi de er ækvivalente:

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

Sådan her:

SQLite IKKE I OperaTor

I ovenstående skærmbillede,

Vi brugte flere ikke-lige-operatorer "<>" for at få en liste over elever, der ikke er lig med nogen af ​​de følgende Id'er 2, 4, 6 eller 8. Denne forespørgsel vil returnere alle andre elever end disse liste over Id'er.

SQLite EXISTS

EXISTS-operatorerne tager ingen operander; det tager kun en SELECT-sætning efter sig. EXISTS-operatoren returnerer sand (1), hvis der er nogen rækker, der returneres fra SELECT-sætningen, og den vil returnere falsk (0), hvis der slet ikke er nogen rækker returneret fra SELECT-udtrykket.

Eksempel:

I det følgende eksempel vil vi vælge afdelingens navn, hvis afdelings-id'et findes i elevtabellen:

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

Dette vil give dig:

SQLite EXISTS OperaTor

Kun de tre afdelinger "IT, fysik og kunst” vil blive returneret. Og afdelingsnavnet "Math” returneres ikke, fordi der ikke er nogen studerende i den pågældende afdeling, så afdelings-id'et findes ikke i elevtabellen. Det er derfor, EXISTS-operatøren ignorerede "Math” afdeling.

SQLite IKKE

Revsletter resultatet af den foregående operator, der kommer efter det. For eksempel:

  • NOT BETWEEN – Det vil returnere sandt, hvis BETWEEN returnerer falsk og omvendt.
  • IKKE LIKE – Det vil returnere sandt, hvis LIKE returnerer falsk og omvendt.
  • IKKE GLOB – Det vil returnere sandt, hvis GLOB returnerer falsk og omvendt.
  • IKKE FINDER – Det vil returnere sandt, hvis EXISTS returnerer falsk og omvendt.

Eksempel:

I det følgende eksempel vil vi bruge NOT-operatoren med EXISTS-operatoren for at få de afdelingers navne, der ikke findes i Elever-tabellen, som er det omvendte resultat af EXISTS-operatoren. Så søgningen vil blive udført via afdelings-id, der ikke findes i afdelingstabellen.

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

Produktion:

SQLite IKKE OperaTor

Kun afdelingen"Math ” vil blive returneret. Fordi "Math” afdeling er den eneste afdeling, der ikke findes i elevtabellen.

Begrænsning og bestilling

SQLite Bestilt

SQLite Ordren er at sortere dit resultat efter et eller flere udtryk. For at bestille resultatsættet skal du bruge ORDER BY-klausulen som følger:

  • Først skal du angive ORDER BY-klausulen.
  • ORDER BY-sætningen skal angives i slutningen af ​​forespørgslen; kun LIMIT-sætningen kan specificeres efter den.
  • Angiv udtrykket for at bestille dataene med, dette udtryk kan være et kolonnenavn eller et udtryk.
  • Efter udtrykket kan du angive en valgfri sorteringsretning. Enten DESC for at rækkefølge dataene faldende eller ASC for at rækkefølge dataene stigende. Hvis du ikke specificerede nogen af ​​dem, ville dataene blive sorteret stigende.
  • Du kan angive flere udtryk ved at bruge "," mellem hinanden.

Eksempel

I det følgende eksempel vil vi vælge alle eleverne sorteret efter deres navne, men i faldende rækkefølge, derefter efter afdelingsnavnet i stigende rækkefølge:

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;

Dette vil give dig:

Begrænsning og bestilling

  • SQLite vil først bestille alle eleverne efter deres afdelingsnavn i stigende rækkefølge
  • For hvert afdelingsnavn vil alle elever under det pågældende afdelingsnavn blive vist i faldende rækkefølge efter deres navne

SQLite Begrænse:

Du kan begrænse antallet af rækker, der returneres af din SQL-forespørgsel, ved at bruge LIMIT-sætningen. For eksempel vil LIMIT 10 kun give dig 10 rækker og ignorere alle de andre rækker.

I LIMIT-klausulen kan du vælge et bestemt antal rækker startende fra en specifik position ved hjælp af OFFSET-klausulen. For eksempel, "LIMIT 4 OFFSET 4” ignorerer de første 4 rækker og returnerer 4 rækker fra den femte række, så du får rækkerne 5,6,7 og 8.

Bemærk, at OFFSET-klausulen er valgfri, du kan skrive den som "LIMIT 4, 4” og det vil give dig de nøjagtige resultater.

Eksempel:

I det følgende eksempel returnerer vi kun 3 elever fra elev-id 5 ved at bruge forespørgslen:

SELECT * FROM Students LIMIT 4,3;

Dette vil kun give dig tre elever fra række 5. Så det vil give dig rækkerne med StudentId 5, 6 og 7:

Begrænsning og bestilling

Fjerner dubletter

Hvis din SQL-forespørgsel returnerer duplikerede værdier, kan du bruge "DISTINCT” nøgleord for at fjerne disse dubletter og returnere forskellige værdier. Du kan angive mere end én kolonne efter DISTINCT-tastens arbejde.

Eksempel:

Følgende forespørgsel vil returnere duplikerede "afdelingsnavneværdier": Her har vi dubletværdier med navnene IT, Fysik og Kunst.

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

Dette vil give dig dublerede værdier for afdelingsnavnet:

Fjernelse af dubletter

Bemærk, hvordan der er duplikerede værdier for afdelingsnavnet. Nu vil vi bruge DISTINCT-søgeordet med den samme forespørgsel til at fjerne disse dubletter og kun få unikke værdier. Sådan her:

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

Dette giver dig kun tre unikke værdier for kolonnen afdelingsnavn:

Fjernelse af dubletter

Aggregate

SQLite Aggregater er indbyggede funktioner defineret i SQLite der vil gruppere flere værdier af flere rækker i én værdi.

Her er aggregaterne understøttet af SQLite:

SQLite AVG()

Returnerede gennemsnittet for alle x-værdierne.

Eksempel:

I det følgende eksempel får vi den gennemsnitlige karakter, som eleverne får fra alle eksamenerne:

SELECT AVG(Mark) FROM Marks;

Dette vil give dig værdien "18.375":

Samlet:SQLite AVG()

Disse resultater kommer fra summeringen af ​​alle mærkeværdierne divideret med deres antal.

COUNT() – COUNT(X) eller COUNT(*)

Returnerer det samlede antal gange, x-værdien dukkede op. Og her er nogle muligheder, du kan bruge med COUNT:

  • COUNT(x): Tæller kun x værdier, hvor x er et kolonnenavn. Det vil ignorere NULL-værdier.
  • COUNT(*): Tæl alle rækkerne fra alle kolonnerne.
  • COUNT (DISTINCT x): Du kan angive et DISTINCT nøgleord før x, som vil få tællingen af ​​de distinkte værdier af x.

Eksempel

I det følgende eksempel får vi det samlede antal afdelinger med COUNT(DepartmentId), COUNT(*) og COUNT(DISTINCT DepartmentId), og hvordan de er forskellige:

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

Dette vil give dig:

Samlet: COUNT() – COUNT(X) eller COUNT(*)

Som følgende:

  • COUNT(DepartmentId) vil give dig antallet af alle afdelings-id'erne, og det vil ignorere null-værdierne.
  • COUNT(DISTINCT DepartmentId) giver dig distinkte værdier af DepartmentId, som kun er 3. Hvilket er de tre forskellige værdier af afdelingsnavnet. Bemærk, at der er 8 værdier af afdelingsnavn i elevens navn. Men kun de forskellige tre værdier som er matematik, IT og fysik.
  • COUNT(*) tæller antallet af rækker i elevtabellen, som er 10 rækker for 10 elever.

GROUP_CONCAT() – GROUP_CONCAT(X) eller GROUP_CONCAT(X,Y)

GROUP_CONCAT aggregatfunktion sammenkæder flere værdier til én værdi med et komma for at adskille dem. Den har følgende muligheder:

  • GROUP_CONCAT(X): Dette vil sammenkæde al værdien af ​​x i én streng, med kommaet "," brugt som en separator mellem værdierne. NULL-værdier vil blive ignoreret.
  • GROUP_CONCAT(X, Y): Dette vil sammenkæde værdierne af x i én streng, hvor værdien af ​​y bruges som en separator mellem hver værdi i stedet for standardseparatoren ','. NULL-værdier vil også blive ignoreret.
  • GROUP_CONCAT(DISTINCT X): Dette vil sammenkæde alle de distinkte værdier af x i én streng, med kommaet "," brugt som en separator mellem værdierne. NULL-værdier vil blive ignoreret.

GROUP_CONCAT(Afdelingsnavn) Eksempel

Følgende forespørgsel vil sammenkæde alle afdelingsnavnets værdier fra de studerende og afdelingstabellen i en streng, adskilt komma. Så i stedet for at returnere en liste med værdier, én værdi på hver række. Det returnerer kun én værdi på én række, med alle værdierne komma adskilt:

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

Dette vil give dig:

Samlet:GROUP_CONCAT() – GROUP_CONCAT(X) eller GROUP_CONCAT(X,Y)

Dette vil give dig listen over 8 afdelingers navne værdier sammenkædet i en streng, kommasepareret.

GROUP_CONCAT(DISTINCT Afdelingsnavn) Eksempel

Følgende forespørgsel vil sammenkæde de forskellige værdier af afdelingsnavnet fra tabellen over studerende og afdelinger i én streng, adskilt komma:

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

Dette vil give dig:

Aggregeret:GROUP_CONCAT(DISTINCT afdelingsnavn) Eksempel

Læg mærke til, hvordan resultatet er anderledes end det forrige resultat; kun tre værdier returnerede, som er de forskellige afdelingers navne, og duplikatværdierne blev fjernet.

GROUP_CONCAT(Afdelingsnavn ,'&') Eksempel

Følgende forespørgsel vil sammenkæde alle værdierne i afdelingsnavnskolonnen fra elev- og afdelingstabellen i én streng, men med tegnet '&' i stedet for et komma som separator:

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

Dette vil give dig:

Aggregate:GROUP_CONCAT(Afdelingsnavn ,'&') Eksempel

Bemærk, hvordan tegnet "&" bruges i stedet for standardtegnet "," til at adskille mellem værdierne.

SQLite MAX() & MIN()

MAX(X) giver dig den højeste værdi fra X-værdierne. MAX returnerer en NULL-værdi, hvis alle værdierne af x er nul. Mens MIN(X) returnerer den mindste værdi fra X-værdierne. MIN vil returnere en NULL-værdi, hvis alle værdierne af X er nul.

Eksempel

I den følgende forespørgsel vil vi bruge funktionerne MIN og MAX til at få det højeste mærke og det laveste mærke fra "Marks" bord:

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

Dette vil give dig:

Samlet:SQLite MAX() & MIN()

SQLite SUM(x), Total(x)

Begge vil returnere summen af ​​alle x-værdierne. Men de er forskellige i følgende:

  • SUM returnerer null, hvis alle værdierne er nul, men Total returnerer 0.
  • TOTAL returnerer altid flydende kommaværdier. SUM returnerer en heltalsværdi, hvis alle x-værdierne er et heltal. Men hvis værdierne ikke er et heltal, vil det returnere en flydende kommaværdi.

Eksempel

I den følgende forespørgsel vil vi bruge SUM og total for at få summen af ​​alle karaktererne i "Marks” tabeller:

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

Dette vil give dig:

Samlet:SQLite SUM(x), Total(x)

Som du kan se, returnerer TOTAL altid et flydende komma. Men SUM returnerer en heltalsværdi, fordi værdierne i "Mark"-kolonnen kan være i heltal.

Forskel mellem SUM og TOTAL eksempel:

I den følgende forespørgsel vil vi vise forskellen mellem SUM og TOTAL, når de får SUM af NULL-værdier:

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

Dette vil give dig:

Aggregeret: Forskellen mellem SUM og TOTAL Eksempel

Bemærk, at der ikke er nogen markeringer for TestId = 4, så der er null-værdier for den test. SUM returnerer en nulværdi som blank, mens TOTAL returnerer 0.

Gruppe BY

GROUP BY-sætningen bruges til at angive en eller flere kolonner, der skal bruges til at gruppere rækkerne i grupper. Rækkerne med samme værdier vil blive samlet (ordnet) i grupper.

For enhver anden kolonne, der ikke er inkluderet i gruppen efter kolonner, kan du bruge en aggregeret funktion til den.

Eksempel:

Følgende forespørgsel vil give dig det samlede antal studerende til stede i hver afdeling.

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;

Dette vil give dig:

Gruppér BY:HAVING-klausul

GROUPBY DepartmentName-klausulen vil gruppere alle studerende i grupper en for hvert afdelingsnavn. For hver gruppe af "afdeling" vil den tælle eleverne på den.

HAVE klausul

Hvis du vil filtrere de grupper, der returneres af GROUP BY-udtrykket, kan du angive et "HAVING"-udtryk med udtryk efter GROUP BY. Udtrykket vil blive brugt til at filtrere disse grupper.

Eksempel

I den følgende forespørgsel vil vi vælge de afdelinger, der kun har to studerende på:

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;

Dette vil give dig:

Gruppe BY

Klausulen HAVING COUNT(S.StudentId) = 2 vil filtrere de returnerede grupper og kun returnere de grupper, der indeholder præcis to elever på den. I vores tilfælde har Arts-afdelingen 2 studerende, så det vises i outputtet.

SQLite Forespørgsel og underforespørgsel

Inde i enhver forespørgsel kan du bruge en anden forespørgsel enten i en SELECT, INSERT, DELETE, UPDATE eller i en anden underforespørgsel.

Denne indlejrede forespørgsel kaldes en underforespørgsel. Vi vil nu se nogle eksempler på brug af underforespørgsler i SELECT-sætningen. Men i selvstudiet om ændring af data vil vi se, hvordan vi kan bruge underforespørgsler med INSERT, DELETE og UPDATE-sætning.

Brug af underforespørgsel i eksemplet FROM-sætning

I den følgende forespørgsel vil vi inkludere en underforespørgsel i FROM-sætningen:

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;

Forespørgslen:

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

Ovenstående forespørgsel kaldes her en underforespørgsel, fordi den er indlejret inde i FROM-sætningen. Bemærk, at vi gav det et aliasnavn "t", så vi kan henvise til de kolonner, der returneres fra det i forespørgslen.

Denne forespørgsel vil give dig:

SQLite Forespørgsel og underforespørgsel: Brug af underforespørgsel i FROM-sætningen

Så i vores tilfælde,

  • s.StudentName er valgt fra hovedforespørgslen, der giver navn på elever og
  • t.Mark er valgt fra underforespørgslen; der giver karakterer opnået af hver af disse elever

Brug af underforespørgsel i eksemplet WHERE-sætning

I den følgende forespørgsel vil vi inkludere en underforespørgsel i WHERE-sætningen:

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

Forespørgslen:

SELECT DepartmentId 
FROM Students AS s 
WHERE d.DepartmentId = s.DepartmentId

Ovenstående forespørgsel kaldes her en underforespørgsel, fordi den er indlejret i WHERE-sætningen. Underforespørgslen returnerer de DepartmentId-værdier, der vil blive brugt af operatøren FINDER IKKE.

Denne forespørgsel vil give dig:

SQLite Forespørgsel og underforespørgsel: Brug af underforespørgsel i WHERE-klausulen

I ovenstående forespørgsel har vi valgt den afdeling, som ikke har nogen studerende tilmeldt sig. Hvilket er "Matematik"-afdelingen herovre.

sæt Operationer – UNION, Kryds

SQLite understøtter følgende SET-handlinger:

UNION & UNION ALLE

Den kombinerer et eller flere resultatsæt (en gruppe rækker), der returneres fra flere SELECT-sætninger, til et resultatsæt.

UNION returnerer forskellige værdier. UNION ALL vil dog ikke og vil inkludere dubletter.

Bemærk, at kolonnenavnet vil være det kolonnenavn, der er angivet i den første SELECT-sætning.

UNION Eksempel

I det følgende eksempel vil vi få listen over afdelings-id fra elevtabellen og listen over afdelings-id fra afdelingstabellen i samme kolonne:

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

Dette vil give dig:

sæt Operationer - UNION Eksempel

Forespørgslen returnerer kun 5 rækker, som er de forskellige afdelings-id-værdier. Bemærk den første værdi, som er nulværdien.

SQLite UNION ALLE Eksempel

I det følgende eksempel vil vi få listen over afdelings-id fra elevtabellen og listen over afdelings-id fra afdelingstabellen i samme kolonne:

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

Dette vil give dig:

sæt Operationer - UNION Eksempel

Forespørgslen returnerer 14 rækker, 10 rækker fra elevtabellen og 4 fra afdelingstabellen. Bemærk, at der er dubletter i de returnerede værdier. Bemærk også, at kolonnenavnet var det, der var angivet i den første SELECT-sætning.

Lad os nu se, hvordan UNION alle vil give forskellige resultater, hvis vi erstatter UNION ALL med UNION:

SQLite KRYDSE

Returnerer de værdier, der findes i både det kombinerede resultatsæt. Værdier, der findes i et af de kombinerede resultatsæt, vil blive ignoreret.

Eksempel

I den følgende forespørgsel vil vi vælge de afdelings-id-værdier, der findes i både tabellerne Studerende og afdelinger i kolonnen Afdelings-id:

SELECT DepartmentId FROM Students
Intersect
SELECT DepartmentId FROM Departments;

Dette vil give dig:

sæt Operationer - Skær

Forespørgslen returnerer kun tre værdier 1, 2 og 3. Hvilket er de værdier, der findes i begge tabeller.

Værdierne null og 4 blev dog ikke inkluderet, fordi null-værdien kun findes i elevtabellen og ikke i afdelingstabellen. Og værdien 4 findes i afdelingstabellen og ikke i elevtabellen.

Det er grunden til, at både værdierne NULL og 4 blev ignoreret og ikke inkluderet i de returnerede værdier.

UNDTAGEN

Antag, at hvis du har to lister med rækker, liste1 og liste2, og du kun vil have rækkerne fra liste1, der ikke findes i liste2, kan du bruge "EXCEPT"-sætning. EXCEPT-udtrykket sammenligner de to lister og returnerer de rækker, der findes i liste1 og ikke findes i liste2.

Eksempel

I den følgende forespørgsel vil vi vælge de DepartmentId-værdier, der findes i afdelingstabellen og ikke findes i elevtabellen:

SELECT DepartmentId FROM Departments
EXCEPT
SELECT DepartmentId FROM Students;

Dette vil give dig:

sæt Operationer - UNDTAGET

Forespørgslen returnerer kun værdien 4. Hvilket er den eneste værdi, der findes i afdelingstabellen, og som ikke findes i elevtabellen.

NULL håndtering

Den "NULL”værdi er en særlig værdi i SQLite. Det bruges til at repræsentere en værdi, der er ukendt eller manglende værdi. Bemærk, at null-værdien er helt anderledes end "0” eller tom ”” værdi. Fordi 0 og den tomme værdi er en kendt værdi, er null-værdien imidlertid ukendt.

NULL-værdier kræver en særlig håndtering i SQLite, vil vi nu se, hvordan vi håndterer NULL-værdierne.

Søg efter NULL-værdier

Du kan ikke bruge den normale lighedsoperator (=) til at søge i nulværdierne. For eksempel søger følgende forespørgsel efter de studerende, der har en null DepartmentId-værdi:

SELECT * FROM Students WHERE DepartmentId = NULL;

Denne forespørgsel giver ikke noget resultat:

NULL Håndtering

Fordi NULL-værdien ikke er lig med nogen anden værdi inkluderet en null-værdi i sig selv, er det derfor, den ikke returnerede noget resultat.

  • Men for at få forespørgslen til at fungere, skal du bruge "ER NULL" operator for at søge efter null-værdier som følger:
SELECT * FROM Students WHERE DepartmentId IS NULL;

Dette vil give dig:

NULL Håndtering

Forespørgslen returnerer de studerende, der har en null DepartmentId-værdi.

  • Hvis du vil have de værdier, der ikke er null, skal du bruge "ER IKKE NULL" operatør som denne:
SELECT * FROM Students WHERE DepartmentId IS NOT NULL;

Dette vil give dig:

NULL Håndtering

Forespørgslen returnerer de studerende, der ikke har en NULL DepartmentId-værdi.

Betingede resultater

Hvis du har en liste over værdier, og du vil vælge en af ​​dem baseret på nogle betingelser. Til det skal betingelsen for den pågældende værdi være sand for at blive valgt.

CASE-udtryk vil evaluere disse betingelser for alle værdierne. Hvis betingelsen er sand, returnerer den denne værdi.

For eksempel, hvis du har en kolonne "Karakter", og du vil vælge en tekstværdi baseret på karakterværdien som følger:

– "Fremragende", hvis karakteren er højere end 85.

– "Meget god", hvis karakteren er mellem 70 og 85.

– "Godt", hvis karakteren er mellem 60 og 70.

Så kan du bruge CASE-udtrykket til at gøre det.

Dette kan bruges til at definere en vis logik i SELECT-sætningen, så du kan vælge bestemte resultater afhængigt af visse betingelser som f.eks. if-sætning.

CASE-operatoren kan defineres med forskellige syntakser som følger:

  1. Du kan bruge forskellige betingelser:
CASE 
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  WHEN condition3 THEN result3
  …
  ELSE resultn
END
  1. Eller du kan kun bruge ét udtryk og sætte forskellige mulige værdier at vælge imellem:
CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  WHEN value3 THEN result3
  …
  ELSE restuln 
END

Bemærk, at ELSE-sætningen er valgfri.

Eksempel

I det følgende eksempel vil vi bruge CASE udtryk med NULL værdi i kolonnen afdelings-id i tabellen Studerende for at vise teksten 'Ingen afdeling' som følger:

SELECT 
  StudentName,
  CASE 
    WHEN DepartmentId IS NULL THEN 'No Department'
    ELSE DepartmentId 
  END AS DepartmentId
FROM Students;
  • CASE-operatøren vil kontrollere værdien af ​​DepartmentId, uanset om den er null eller ej.
  • Hvis det er en NULL-værdi, vil den vælge den bogstavelige værdi 'No Department' i stedet for DepartmentId-værdien.
  • Hvis det ikke er en nulværdi, vil den vælge værdien for afdelings-id-kolonnen.

Dette vil give dig output som vist nedenfor:

Betingede resultater

Fælles tabeludtryk

Almindelige tabeludtryk (CTE'er) er underforespørgsler, der er defineret inde i SQL-sætningen med et givet navn.

Det har en fordel i forhold til underforespørgslerne, fordi det er defineret ud fra SQL-sætningerne og vil gøre forespørgslerne nemmere at læse, vedligeholde og forstå.

Et almindeligt tabeludtryk kan defineres ved at sætte WITH-sætningen foran en SELECT-sætning som følger:

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

Den "CTEnavn” er et hvilket som helst navn du kan give til CTE, du kan bruge det til at henvise til det senere. Bemærk, at du kan definere SELECT, UPDATE, INSERT eller DELETE-sætning på CTE'er

Lad os nu se et eksempel på, hvordan man bruger CTE i SELECT-sætningen.

Eksempel

I det følgende eksempel vil vi definere en CTE fra en SELECT-sætning, og så bruger vi den senere på en anden forespørgsel:

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;

I denne forespørgsel definerede vi en CTE og gav den navnet "Alle afdelinger“. Denne CTE blev defineret ud fra en SELECT-forespørgsel:

SELECT DepartmentId, DepartmentName
  FROM Departments

Så efter at vi havde defineret CTE, brugte vi den i SELECT-forespørgslen, der kommer efter den.

Bemærk, at almindelige tabeludtryk ikke påvirker outputtet af forespørgslen. Det er en måde at definere en logisk visning eller underforespørgsel for at genbruge dem i den samme forespørgsel. Almindelige tabeludtryk er som en variabel, du erklærer, og genbruger den som en underforespørgsel. Kun SELECT-sætningen påvirker outputtet af forespørgslen.

Denne forespørgsel vil give dig:

Fælles tabeludtryk

Avancerede forespørgsler

Avancerede forespørgsler er de forespørgsler, der indeholder komplekse joinforbindelser, underforespørgsler og nogle aggregater. I det følgende afsnit vil vi se et eksempel på en avanceret forespørgsel:

Hvor vi får,

  • Instituttets navne med alle elever for hver afdeling
  • Elevens navn adskilt med komma og
  • Viser, at afdelingen har mindst tre studerende i den
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;

Vi tilføjede en JOIN klausul for at hente afdelingsnavnet fra tabellen afdelinger. Derefter tilføjede vi en GROUP BY-klausul med to aggregerede funktioner:

  • “COUNT” for at tælle eleverne for hver afdelingsgruppe.
  • GROUP_CONCAT for at sammenkæde elever for hver gruppe med komma adskilt i én streng.
  • Efter GROUP BY brugte vi HAVING-klausulen til at filtrere afdelingerne og kun vælge de afdelinger, der har mindst 3 studerende.

Resultatet bliver som følger:

Avancerede forespørgsler

Resumé

Dette var en introduktion til skrivning SQLite forespørgsler og det grundlæggende i at forespørge databasen, og hvordan du kan filtrere de returnerede data. Du kan nu, skrive din egen SQLite forespørgsler.

Du kan lide: