SQLite Spørring: Velg, Hvor, LIMIT, OFFSET, Count, Group by

Avsnittene nedenfor går gjennom lesing av data med SELECT og FROM, filtrering av rader med WHERE og operatorene, sortering og begrensning av resultater, aggregeringsfunksjoner, GROUP BY og HAVING, delspørringer, settoperasjoner, NULL-håndtering, CASE-uttrykk og vanlige tabelluttrykk.

Å skrive SQL-spørringer i en SQLite database, må du vite hvordan SELECT-, FROM-, WHERE-, GROUP BY-, ORDER BY- og LIMIT-klausulene fungerer og hvordan du bruker dem.

I løpet av denne opplæringen vil du lære hvordan du bruker disse klausulene og hvordan du skriver SQLite klausuler.

Lese data med Select

SELECT-klausulen er hovedsetningen du bruker til å spørre en SQLite database. I SELECT-leddet oppgir du hva du skal velge. Men før select-leddet, la oss se hvor vi kan velge data ved å bruke FROM-leddet.

FROM-leddet brukes til å spesifisere hvor du vil velge data. I fra-klausulen kan du spesifisere en eller flere tabeller eller underspørringer å velge data fra, som vi vil se senere i veiledningene.

Merk at for alle de følgende eksemplene må du kjøre sqlite3.exe og åpne en tilkobling til eksempeldatabasen som flytende:

Trinn 1) I dette trinnet

Åpne Min datamaskin og naviger til følgende katalog «C:\sqlite», og åpne deretter «sqlite3.exe»:

Trinn 2) Åpne databasen «TutorialsSampleDB.db» med følgende kommando:

Nå er du klar til å kjøre alle typer spørringer på databasen.

I SELECT-leddet kan du velge ikke bare et kolonnenavn, men du har mange andre alternativer for å spesifisere hva du skal velge. Som følgende:

PLUKKE UT *

Denne kommandoen vil velge alle kolonnene fra alle de refererte tabellene (eller underspørringene) i FROM-leddet. For eksempel:

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

Dette vil velge alle kolonnene fra både tabellstudentene og avdelingstabellene:

VELG tabellnavn.*

Dette vil velge alle kolonnene fra bare tabellen "tabellnavn". For eksempel:

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

Dette vil kun velge alle kolonnene fra elevtabellen:

En bokstavelig verdi

En bokstavelig verdi er en konstant verdi som kan spesifiseres i select-setningen. Du kan bruke bokstavelige verdier normalt på samme måte som du bruker kolonnenavn i SELECT-leddet. Disse bokstavelige verdiene vil vises for hver rad fra radene som returneres av SQL-spørringen.

Her er noen eksempler på forskjellige bokstavelige verdier som du kan velge:

• Numerisk bokstavelig - tall i alle formater som 1, 2.55, ... osv.
• Streng bokstaver – Enhver streng 'USA', 'dette er en eksempeltekst', … osv.
• NULL – NULL verdi.
• Current_TIME – Det vil gi deg gjeldende tid.
• CURRENT_DATE – dette vil gi deg gjeldende dato.

Dette kan være nyttig i enkelte situasjoner der du må velge en konstant verdi for alle de returnerte radene. For eksempel, hvis du vil velge alle studentene fra Studenter-tabellen, med en ny kolonne kalt et land som inneholder verdien "USA", kan du gjøre dette:

SELECT *, 'USA' AS Country FROM Students;

Dette vil gi deg alle elevenes kolonner, pluss en ny kolonne "Land" som dette:

Merk at denne nye kolonnen Land faktisk ikke er en ny kolonne lagt til i tabellen. Det er en virtuell kolonne, opprettet i spørringen for å vise resultatene, og den vil ikke bli opprettet på tabellen.

Navn og alias

Aliaset er et nytt navn for kolonnen som lar deg velge kolonnen med et nytt navn. Kolonnealiasene er spesifisert ved hjelp av nøkkelordet "AS".

For eksempel, hvis du vil velge Studentnavn-kolonnen som skal returneres med "Student Name" i stedet for "StudentName", kan du gi den et alias som dette:

SELECT StudentName AS 'Student Name' FROM Students;

Dette vil gi deg elevenes navn med navnet "Student Name" i stedet for "StudentName" slik:

Merk at kolonnenavnet fortsatt er «StudentName»; kolonnen StudentName er fortsatt den samme, den endres ikke av aliaset.

Aliaset vil ikke endre kolonnenavnet; det vil bare endre visningsnavnet i SELECT-leddet.

Vær også oppmerksom på at nøkkelordet "AS" er valgfritt, du kan sette aliasnavnet uten det, noe som dette:

SELECT StudentName 'Student Name' FROM Students;

Og det vil gi deg nøyaktig samme utdata som forrige spørring:

Du kan også gi tabeller aliaser, ikke bare kolonner. Med samme søkeord "AS". Du kan for eksempel gjøre dette:

SELECT s.* FROM Students AS s;

Dette vil gi deg alle kolonnene i tabellen Studenter:

Dette kan være veldig nyttig hvis du deltar i mer enn ett bord; i stedet for å gjenta hele tabellnavnet i spørringen, kan du gi hver tabell et kort aliasnavn. For eksempel i følgende spørring:

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

Denne spørringen vil velge hvert studentnavn fra "Studenter"-tabellen med avdelingsnavnet fra "Avdelinger"-tabellen:

Imidlertid kan samme spørring skrives slik:

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

Vi ga Student-tabellen et alias «s» og avdelingstabellen et alias «d». I stedet for å bruke hele tabellnavnet, brukte vi aliasene deres til å referere til dem. INNER JOIN kobler sammen to eller flere tabeller ved hjelp av en betingelse. I vårt eksempel koblet vi Student-tabellen med Avdelinger-tabellen med DepartmentId-kolonnen. Det finnes også en grundig forklaring på INNER JOIN i «SQLite tiltrer"opplæring."

Dette vil gi deg nøyaktig utdata som forrige spørring:

HVOR

Å skrive SQL-spørringer ved å bruke SELECT-leddet alene med FROM-leddet som vi så i forrige seksjon, vil gi deg alle radene fra tabellene. Imidlertid, hvis du vil filtrere de returnerte dataene, må du legge til en "WHERE"-klausul.

WHERE-klausulen brukes til å filtrere resultatsettet som returneres av SQL-spørringen. Slik fungerer WHERE-klausulen:

• I WHERE-leddet kan du spesifisere et "uttrykk".
• Dette uttrykket vil bli evaluert for hver rad som returneres fra tabellen(e) spesifisert i FROM-leddet.
• Uttrykket vil bli evaluert som et boolsk uttrykk, med resultatet enten sant, usant eller null.
• Da vil bare rader der uttrykket ble evaluert med en sann verdi returneres, og de med falske eller null-resultater vil bli ignorert og ikke inkludert i resultatsettet.

For å filtrere resultatsettet ved hjelp av WHERE-leddet, må du bruke uttrykk og operatorer.

Liste over operatører i SQLite og hvordan du bruker dem

I den følgende delen vil vi forklare hvordan du kan filtrere ved hjelp av uttrykk og operatorer.

Uttrykk er en eller flere bokstavelige verdier eller kolonner kombinert med hverandre med en operator.

Merk at du kan bruke uttrykk i både SELECT-leddet og i WHERE-leddet.

I de følgende eksemplene vil vi prøve uttrykkene og operatorene i både select-leddet og WHERE-leddet. For å vise deg hvordan de presterer.

Det finnes forskjellige typer uttrykk og operatorer som du kan spesifisere som følger:

SQLite sammenkoblingsoperatoren «||»

Denne operatoren brukes til å sette sammen én eller flere bokstavelige verdier eller kolonner med hverandre. Det vil produsere én streng med resultater fra alle sammenkoblede bokstavelige verdier eller kolonner. For eksempel:

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

Dette vil sammenkobles til et nytt alias «StudentIdWithName»:

• Den litterale strengverdien «Id med navn:»
• med verdien i kolonnen «StudentId» og
• med verdien fra kolonnen «StudentName»

SQLite CAST-operatør:

CAST-operatoren brukes til å konvertere en verdi fra en data-type til en annen datatype.

Hvis du for eksempel har en numerisk verdi lagret som en strengverdi som denne «12.5» og du vil konvertere den til en numerisk verdi, kan du bruke CAST-operatoren til å gjøre dette slik: «CAST( '12.5' AS REAL)». Eller hvis du har en desimalverdi som 12.5, og du bare trenger heltallsdelen, kan du omforme den til et heltall som denne: «CAST(12.5 AS INTEGER)».

Eksempel

I følgende kommando vil vi prøve å konvertere forskjellige verdier til andre datatyper:

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

Dette vil gi deg:

Resultatet er som følger:

• CAST('12.5' ​​AS REAL) – verdien '12.5' ​​er en strengverdi, den vil bli konvertert til en REAL verdi.
• CAST(12.5 SOM HELTAL) – verdien 12.5 er en desimalverdi, den vil bli konvertert til å være en heltallsverdi. Desimaldelen avkortes, og den blir 12.

SQLite Aritmetisk Operators:

Ta to eller flere numeriske bokstavelige verdier eller numeriske kolonner og returner én numerisk verdi. De aritmetiske operatorene som støttes i SQLite er:

• Addisjon «+» – gir summen av de to operandene.
• Subtracsjon «–» – underteksttracTester de to operandene og resulterer i differansen.
• Multiplikasjon «*» – produktet av de to operandene.
• Påminnelse (modulo) «%» – gir resten som kommer av å dele én operand med den andre operand.
• Divisjon «/» – returnerer kvotientresultatet fra å dele den venstre operand med den høyre operand.

Eksempel:

I det følgende eksemplet skal vi prøve de fem aritmetiske operatorene med bokstavelige numeriske verdier i samme select-klausul:

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

Dette vil gi deg:

Legg merke til hvordan vi brukte en SELECT-setning uten en FROM-klausul her. Og dette er tillatt SQLite så lenge vi velger bokstavelige verdier.

SQLite Sammenligningsoperatører

Sammenlign to operander med hverandre og returner en sann eller usann som følger:

• «<» – returnerer sann hvis den venstre operand er mindre enn den høyre operand.
• «<=» – returnerer sann hvis den venstre operand er mindre enn eller lik den høyre operand.
• «>» – returnerer sann hvis den venstre operand er større enn den høyre operand.
• «>=» – returnerer sann hvis den venstre operand er større enn eller lik den høyre operand.
• «=» og «==» – returnerer sann hvis de to operandene er like. Merk at begge operatorene er like, og det er ingen forskjell mellom dem.
• «!=» og «<>» – returnerer sann hvis de to operandene ikke er like. Merk at begge operatorene er like, og det er ingen forskjell mellom dem.

Noter det, SQLite uttrykker den sanne verdien med 1 og den falske verdien 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 gi noe som dette:

SQLite Mønstermatchende operatører

«LIKE» – brukes til mønstergjenkjenning. Ved å bruke «Like» kan du søke etter verdier som samsvarer med et mønster spesifisert med et jokertegn.

Operanden til venstre kan enten være en bokstavlig strengverdi eller en strengkolonne. Mønsteret kan spesifiseres som følger:

• Inneholder mønster. For eksempel StudentName LIKE '%a%' – dette vil søke etter studentnavn som inneholder bokstaven «a» hvor som helst i StudentName-kolonnen.
• Starter med mønsteret. For eksempel «StudentName LIKE 'a%'» – søk etter elevnavn som begynner med bokstaven «a».
• Slutter med mønsteret. For eksempel «StudentName LIKE '%a'» – Søk etter elevnavn som slutter med bokstaven «a».
• Finn et hvilket som helst enkelttegn i en streng ved å bruke understrekingsbokstaven «_». For eksempel «StudentName LIKE 'J___'» – Søk etter studentnavn som er fire tegn lange. Navnet må starte med bokstaven «J» og kan ha tre andre tegn etter bokstaven «J».

Eksempler på mønstersamsvar:

Få elevers navn som begynner med bokstaven 'j':

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

Resultat:

Få elevenes navn til slutt med «y»-bokstaven:

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

Resultat:

Få elevenes navn som inneholder bokstaven 'n':

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

Resultat:

«GLOB» – tilsvarer LIKE-operatoren, men GLOB skiller mellom store og små bokstaver, i motsetning til LIKE-operatoren. For eksempel vil følgende to kommandoer returnere forskjellige resultater:

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

Dette vil gi deg:

Den første setningen returnerer 0(false) fordi GLOB-operatoren skiller mellom store og små bokstaver, så 'j' er ikke lik 'J'. Imidlertid vil den andre setningen returnere 1 (true) fordi LIKE-operatoren ikke skiller mellom store og små bokstaver, så 'j' er lik 'J'.

Andre operatører:

SQLite OG

En logisk operator som kombinerer ett eller flere uttrykk. Det vil returnere sant, bare hvis alle uttrykkene gir en "sann" verdi. Imidlertid vil den returnere usann bare hvis alle uttrykkene gir en "falsk" verdi.

Eksempel:

Følgende spørring vil søke etter studenter som har StudentId > 5 og StudentName begynner med bokstaven N, de returnerte studentene må oppfylle de to betingelsene:

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

Som en utgang, i skjermbildet ovenfor, vil dette bare gi deg "Nancy". Nancy er den eneste studenten som oppfyller begge vilkårene.

SQLite OR

En logisk operator som kombinerer ett eller flere uttrykk, slik at hvis en av de kombinerte operatorene gir sann, vil den returnere sant. Men hvis alle uttrykkene gir usann, vil det returnere usann.

Eksempel:

Følgende spørring vil søke etter studenter som har StudentId > 5 eller StudentName begynner med bokstaven N, de returnerte studentene må oppfylle minst ett av vilkårene:

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

Dette vil gi deg:

Som en utgang, i skjermbildet ovenfor, vil dette gi deg navnet på en student med bokstaven "n" i navnet pluss student-ID-en som har verdi>5.

Som du kan se er resultatet annerledes enn spørringen med AND-operatoren.

SQLite MELLOM

MELLOM brukes til å velge verdier som er innenfor et område på to verdier. For eksempel vil «X MELLOM Y OG Z» returnere sann (1) hvis verdien X er mellom de to verdiene Y og Z. Ellers vil den returnere usann (0). «X MELLOM Y OG Z» tilsvarer «X >= Y OG X <= Z», X må være større enn eller lik Y, og X er mindre enn eller lik Z.

Eksempel:

I følgende eksempelspørring vil vi skrive en spørring for å få elever med Id-verdi mellom 5 og 8:

SELECT *
FROM Students
WHERE StudentId BETWEEN 5 AND 8;

Dette vil kun gi elevene med ID 5, 6, 7 og 8:

SQLite IN

Tar en operand og en liste over operander. Det vil returnere sant hvis den første operanden verdien lik en av operandenes verdi fra listen. IN-operatoren returnerer sann (1) hvis listen over operander inneholder den første operandverdien i verdiene. Ellers vil den returnere falsk (0).

Slik som dette: «col IN(x, y, z)». Dette tilsvarer «(col=x) eller (col=y) eller (col=z)».

Eksempel:

Følgende spørring vil kun velge elever med ID 2, 4, 6, 8:

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

Som dette:

Den forrige spørringen vil gi det nøyaktige resultatet som følgende spørring fordi de er likeverdige:

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

Begge spørringene gir det nøyaktige resultatet. Forskjellen mellom de to spørringene er imidlertid at det første søket vi brukte "IN"-operatoren. I den andre spørringen brukte vi flere "ELLER"-operatorer.

IN-operatoren tilsvarer bruk av flere ELLER-operatorer. «WHERE StudentId IN(2, 4, 6, 8)» tilsvarer «WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);»

Som dette:

SQLite IKKE I

Operanden «NOT IN» er det motsatte av IN-operatoren. Men med samme syntaks; den tar én operand og en liste med operander. Den returnerer sann hvis den første operandverdien ikke er lik verdien til en av operandene fra listen. dvs. den returnerer sann (0) hvis listen over operander ikke inneholder den første operanden. Slik som dette: «col NOT IN(x, y, z)». Dette tilsvarer «(col<>x) AND (col<>y) AND (col<>z)».

Eksempel:

Følgende spørring vil velge elever med ID-er som ikke er lik en av disse ID-ene 2, 4, 6, 8:

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

Som dette

Den forrige spørringen gir vi det nøyaktige resultatet som følgende spørring fordi de er likeverdige:

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

Som dette:

I skjermbildet ovenfor,

Vi brukte flere ikke like-operatorer «<>» for å få en liste over studenter som ikke er lik verken av følgende Id-er 2, 4, 6 eller 8. Denne spørringen vil returnere alle andre studenter enn disse listene med Id-er.

SQLite Eksisterer

EXISTS-operatorene tar ingen operander; det tar bare en SELECT-klausul etter seg. EXISTS-operatoren vil returnere true (1) hvis det er noen rader returnert fra SELECT-leddet, og den vil returnere usant (0) hvis det ikke er noen rader returnert fra SELECT-leddet.

Eksempel:

I følgende eksempel vil vi velge avdelingens navn, hvis avdelings-ID finnes i elevtabellen:

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

Dette vil gi deg:

Bare de tre avdelingene «IT, fysikk og kunst» vil bli returnert. Og avdelingsnavnet «Matte» vil ikke bli returnert fordi det ikke er noen student i den avdelingen, så avdelings-ID-en finnes ikke i studenttabellen. Det er derfor EXISTS-operatoren ignorerte avdelingen «Matte».

SQLite IKKE

Reverses resultatet av den foregående operatoren som kommer etter det. For eksempel:

• IKKE MELLOM – Det vil returnere sant hvis BETWEEN returnerer falskt og omvendt.
• IKKE LIKE – Det vil returnere sant hvis LIKE returnerer falskt og omvendt.
• IKKE GLOB – Det vil returnere sant hvis GLOB returnerer usann og omvendt.
• IKKE FINNES – Det vil returnere sant hvis EXISTS returnerer usant og omvendt.

Eksempel:

I det følgende eksempelet vil vi bruke NOT-operatoren med EXISTS-operatoren for å få avdelingenes navn som ikke finnes i Students-tabellen, som er det motsatte resultatet av EXISTS-operatoren. Så, søket vil bli gjort gjennom DepartmentId som ikke finnes i avdelingstabellen.

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

Utgang:

Bare avdelingen «Matte» vil bli returnert. Fordi avdelingen «Matte» er den eneste avdelingen, finnes ikke den i elevtabellen.

Begrensning og bestilling

SQLite Orden

SQLite Rekkefølgen er å sortere resultatet etter ett eller flere uttrykk. For å bestille resultatsettet, må du bruke ORDER BY-klausulen som følger:

• Først må du spesifisere ORDER BY-klausulen.
• ORDER BY-klausulen må spesifiseres på slutten av spørringen; bare LIMIT-klausulen kan spesifiseres etter den.
• Spesifiser uttrykket du vil bestille dataene med, dette uttrykket kan være et kolonnenavn eller et uttrykk.
• Etter uttrykket kan du angi en valgfri sorteringsretning. Enten DESC, for å bestille dataene synkende eller ASC for å bestille dataene stigende. Hvis du ikke spesifiserte noen av dem, vil dataene bli sortert stigende.
• Du kan spesifisere flere uttrykk ved å bruke "," mellom hverandre.

Eksempel

I følgende eksempel vil vi velge alle studentene sortert etter navn, men i synkende rekkefølge, deretter etter avdelingsnavn i stigende rekkefø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 gi deg:

• SQLite vil først bestille alle studentene etter avdelingsnavn i stigende rekkefølge
• For hvert avdelingsnavn vil alle studentene under det avdelingsnavnet vises i synkende rekkefølge etter navnene deres

SQLite Grense:

Du kan begrense antall rader som returneres av SQL-spørringen din, ved å bruke LIMIT-klausulen. For eksempel vil LIMIT 10 gi deg bare 10 rader og ignorere alle de andre radene.

I LIMIT-klausulen kan du velge et bestemt antall rader som starter fra en bestemt posisjon ved hjelp av OFFSET-klausulen. For eksempel vil «LIMIT 4 OFFSET 4» ignorere de første 4 radene og returnere 4 rader som starter fra den femte raden, slik at du får rad 5, 6, 7 og 8.

Merk at OFFSET-klausulen er valgfri. Du kan skrive den som «LIMIT 4, 4», og den vil gi deg de nøyaktige resultatene.

Eksempel:

I det følgende eksempelet returnerer vi bare 3 elever fra student-ID 5 ved å bruke spørringen:

SELECT * FROM Students LIMIT 4,3;

Dette vil gi deg bare tre elever fra rad 5. Så det vil gi deg radene med StudentId 5, 6 og 7:

Fjerner duplikater

Hvis SQL-spørringen din returnerer dupliserte verdier, kan du bruke nøkkelordet «DISTINCT» for å fjerne disse duplikatene og returnere distinkte verdier. Du kan angi mer enn én kolonne etter at DISTINCT-nøkkelen har virket.

Eksempel:

Følgende spørring vil returnere dupliserte "avdelingsnavnverdier": Her har vi dupliserte verdier med navn IT, Fysikk og Kunst.

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

Dette vil gi deg dupliserte verdier for avdelingsnavnet:

Legg merke til hvordan det er dupliserte verdier for avdelingsnavnet. Nå vil vi bruke DISTINCT-nøkkelordet med det samme søket for å fjerne disse duplikatene og få bare unike verdier. Slik:

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

Dette vil gi deg bare tre unike verdier for avdelingsnavnkolonnen:

Aggregate

SQLite Aggregater er innebygde funksjoner definert i SQLite som vil gruppere flere verdier av flere rader til én verdi.

Her er aggregatene som støttes av SQLite:

SQLite AVG()

Returnerte gjennomsnittet for alle x-verdiene.

Eksempel:

I følgende eksempel vil vi få gjennomsnittskarakteren studentene får fra alle eksamenene:

SELECT AVG(Mark) FROM Marks;

Dette vil gi deg verdien "18.375":

Disse resultatene kommer fra summeringen av alle merkeverdiene delt på antallet.

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

Returnerer det totale antallet ganger x-verdien dukket opp. Og her er noen alternativer du kan bruke med COUNT:

• COUNT(x): Teller bare x verdier, der x er et kolonnenavn. Den vil ignorere NULL-verdier.
• COUNT(*): Tell alle radene fra alle kolonnene.
• COUNT (DISTINCT x): Du kan spesifisere et DISTINCT nøkkelord før x, som vil få tellingen av de distinkte verdiene til x.

Eksempel

I følgende eksempel vil vi få det totale antallet avdelinger med COUNT(DepartmentId), COUNT(*) og COUNT(DISTINCT DepartmentId) og hvordan de er forskjellige:

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

Dette vil gi deg:

Som følgende:

• COUNT(DepartmentId) vil gi deg tellingen av alle avdelings-IDene, og den vil ignorere nullverdiene.
• COUNT(DISTINCT DepartmentId) gir deg distinkte verdier for DepartmentId, som bare er 3. Som er de tre forskjellige verdiene for avdelingsnavn. Legg merke til at det er 8 verdier av avdelingsnavn i studentnavnet. Men bare de forskjellige tre verdiene som er matematikk, IT og fysikk.
• COUNT(*) teller antall rader i elevtabellen som er 10 rader for 10 elever.

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

GROUP_CONCAT aggregatfunksjon setter sammen flere verdier til én verdi med et komma for å skille dem. Den har følgende alternativer:

• GROUP_CONCAT(X): Dette vil sette sammen all verdien av x i én streng, med kommaet "," brukt som skilletegn mellom verdiene. NULL-verdier vil bli ignorert.
• GROUP_CONCAT(X, Y): Dette vil sette sammen verdiene til x til én streng, med verdien av y brukt som skilletegn mellom hver verdi i stedet for standardskilletegn ','. NULL-verdier vil også bli ignorert.
• GROUP_CONCAT(DISTINCT X): Dette vil sette sammen alle de distinkte verdiene til x i én streng, med kommaet "," brukt som skilletegn mellom verdiene. NULL-verdier vil bli ignorert.

GROUP_CONCAT(avdelingsnavn) Eksempel

Følgende spørring vil sette sammen alle avdelingsnavnets verdier fra studentene og avdelingstabellen i én streng, separert komma. Så i stedet for å returnere en liste med verdier, én verdi på hver rad. Det vil bare returnere én verdi på én rad, med alle verdiene kommadelt:

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

Dette vil gi deg:

Dette vil gi deg listen over 8 avdelingers navneverdier sammenkoblet i én streng kommadelt.

GROUP_CONCAT(DISTINCT Avdelingsnavn) Eksempel

Følgende spørring vil sette sammen de distinkte verdiene til avdelingsnavnet fra studentene og avdelingstabellen i én streng, separert komma:

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

Dette vil gi deg:

Legg merke til hvordan resultatet er annerledes enn det forrige resultatet; bare tre verdier returnerte som er de distinkte avdelingenes navn, og duplikatverdiene ble fjernet.

GROUP_CONCAT(DepartmentName ,'&') Eksempel

Følgende spørring vil sette sammen alle verdiene i avdelingsnavnkolonnen fra student- og avdelingstabellen til én streng, men med tegnet '&' i stedet for et komma som skilletegn:

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

Dette vil gi deg:

Legg merke til hvordan tegnet "&" brukes i stedet for standardtegnet "," for å skille mellom verdiene.

SQLite MAX() og MIN()

MAX(X) gir deg den høyeste verdien fra X-verdiene. MAX vil returnere en NULL-verdi hvis alle verdiene til x er null. Mens MIN(X) gir deg den minste verdien fra X-verdiene. MIN vil returnere en NULL-verdi hvis alle verdiene til X er null.

Eksempel

I den følgende spørringen bruker vi MIN- og MAX-funksjonene for å få den høyeste og laveste karakteren fra «Poeng»-tabellen:

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

Dette vil gi deg:

SQLite SUM(x), Total(x)

Begge vil returnere summen av alle x-verdiene. Men de er forskjellige i følgende:

• SUM vil returnere null hvis alle verdiene er null, men Total vil returnere 0.
• TOTAL returnerer alltid flyttallverdier. SUM returnerer en heltallsverdi hvis alle x-verdiene er et heltall. Men hvis verdiene ikke er et heltall, vil det returnere en flyttallsverdi.

Eksempel

I den følgende spørringen bruker vi SUM og total for å få summen av alle poengene i «Poeng»-tabellene:

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

Dette vil gi deg:

Som du kan se, returnerer TOTAL alltid et flytende komma. Men SUM returnerer en heltallsverdi fordi verdiene i "Mark"-kolonnen kan være i heltall.

Forskjellen mellom SUM og TOTAL eksempel:

I den følgende spørringen vil vi vise forskjellen mellom SUM og TOTAL når de får SUM av NULL-verdier:

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

Dette vil gi deg:

Merk at det ikke er noen merker for TestId = 4, så det er nullverdier for den testen. SUM returnerer en nullverdi som blank, mens TOTAL returnerer 0.

Gruppe BY

GROUP BY-leddet brukes til å spesifisere en eller flere kolonner som skal brukes til å gruppere radene i grupper. Radene med samme verdier vil bli samlet (ordnet) i grupper.

For enhver annen kolonne som ikke er inkludert i gruppen etter kolonner, kan du bruke en aggregert funksjon for den.

Eksempel:

Følgende spørring vil gi deg det totale antallet studenter som er tilstede i hver avdeling.

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 gi deg:

GROUPBY Avdelingsnavn-klausulen vil gruppere alle studentene i grupper én for hvert avdelingsnavn. For hver gruppe av "avdeling" vil den telle studentene på den.

Å ha klausul

Hvis du vil filtrere gruppene som returneres av GROUP BY-leddet, kan du spesifisere en "HAVING"-ledd med uttrykk etter GROUP BY. Uttrykket vil bli brukt til å filtrere disse gruppene.

Eksempel

I følgende spørring vil vi velge de avdelingene som bare har to studenter 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 gi deg:

Klausulen HAVING COUNT(S.StudentId) = 2 vil filtrere gruppene som returneres og returnere bare de gruppene som inneholder nøyaktig to studenter. I vårt tilfelle har kunstavdelingen 2 studenter, så det vises i output.

SQLite Spørring og underspørring

Inne i en hvilken som helst spørring kan du bruke en annen spørring enten i en SELECT, INSERT, DELETE, UPDATE eller i en annen underspørring.

Denne nestede spørringen kalles en underspørring. Vi vil nå se noen eksempler på bruk av underspørringer i SELECT-leddet. I opplæringen for å endre data vil vi imidlertid se hvordan vi kan bruke underspørringer med INSERT, DELETE og UPDATE-setning.

Bruke underspørring i eksempelet fra FROM-klausulen

I den følgende spørringen vil vi inkludere en underspørring i FROM-klausulen:

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;

Spørsmålet:

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

Spørringen ovenfor kalles en underspørring her fordi den er nestet inne i FROM-klausulen. Legg merke til at vi ga den et aliasnavn "t" slik at vi kan referere til kolonnene som returneres fra den i spørringen.

Denne spørringen vil gi deg:

Så i vårt tilfelle,

• s.StudentName velges fra hovedspørringen som gir navn på studenter og
• t.Mark er valgt fra underspørringen; som gir karakterer oppnådd av hver av disse elevene

Bruke underspørring i eksempelet WHERE-klausulen

I den følgende spørringen vil vi inkludere en underspørring i WHERE-klausulen:

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

Spørsmålet:

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

Spørringen ovenfor kalles en underspørring her fordi den er nestet i WHERE-leddet. Underspørringen vil returnere DepartmentId-verdiene som vil bli brukt av operatøren FINNES IKKE.

Denne spørringen vil gi deg:

I spørringen ovenfor har vi valgt avdelingen som ikke har noen studenter påmeldt. Som er "matematikk"-avdelingen her.

Sett Operasjoner – UNION, Intersect

SQLite støtter følgende SET-operasjoner:

UNION & UNION ALLE

Den kombinerer ett eller flere resultatsett (en gruppe rader) returnert fra flere SELECT-setninger til ett resultatsett.

UNION vil returnere distinkte verdier. UNION ALL vil imidlertid ikke inkludere duplikater.

Merk at kolonnenavnet vil være kolonnenavnet spesifisert i den første SELECT-setningen.

UNION Eksempel

I det følgende eksempelet vil vi få listen over DepartmentId fra studenttabellen og listen over DepartmentId fra avdelingstabellen i samme kolonne:

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

Dette vil gi deg:

Spørringen returnerer bare 5 rader som er de distinkte avdelings-ID-verdiene. Legg merke til den første verdien som er nullverdien.

SQLite UNION ALL Eksempel

I det følgende eksempelet vil vi få listen over DepartmentId fra studenttabellen og listen over DepartmentId fra avdelingstabellen i samme kolonne:

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

Dette vil gi deg:

Spørringen vil returnere 14 rader, 10 rader fra elevtabellen og 4 fra avdelingstabellen. Merk at det er duplikater i verdiene som returneres. Vær også oppmerksom på at kolonnenavnet var det som var spesifisert i den første SELECT-setningen.

La oss nå se hvordan UNION alle vil gi forskjellige resultater hvis vi erstatter UNION ALL med UNION:

SQLite KRYSSE

Returnerer verdiene som finnes i både det kombinerte resultatsettet. Verdier som finnes i et av de kombinerte resultatsettene vil bli ignorert.

Eksempel

I den følgende spørringen vil vi velge avdelings-ID-verdiene som finnes i både tabellene Studenter og avdelinger i kolonnen avdelings-ID:

SELECT DepartmentId FROM Students
Intersect
SELECT DepartmentId FROM Departments;

Dette vil gi deg:

Spørringen returnerer bare tre verdier 1, 2 og 3. Dette er verdiene som finnes i begge tabellene.

Verdiene null og 4 ble imidlertid ikke inkludert fordi nullverdien kun finnes i elevtabellen og ikke i avdelingstabellen. Og verdien 4 finnes i avdelingstabellen og ikke i elevtabellen.

Det er derfor både verdiene NULL og 4 ble ignorert og ikke inkludert i de returnerte verdiene.

UNNTATT

Tenk deg at hvis du har to lister med rader, liste1 og liste2, og du bare vil ha radene fra liste1 som ikke finnes i liste2, kan du bruke "EXCEPT"-klausulen. EXCEPT-leddet sammenligner de to listene og returnerer de radene som finnes i liste1 og ikke finnes i liste2.

Eksempel

I den følgende spørringen vil vi velge avdelings-ID-verdiene som finnes i avdelingstabellen og som ikke finnes i elevtabellen:

SELECT DepartmentId FROM Departments
EXCEPT
SELECT DepartmentId FROM Students;

Dette vil gi deg:

Spørringen returnerer bare verdien 4. Som er den eneste verdien som finnes i avdelingstabellen, og som ikke finnes i elevtabellen.

NULL håndtering

«NULL»-verdien er en spesialverdi i SQLiteDen brukes til å representere en verdi som er ukjent eller mangler verdi. Merk at nullverdien er helt forskjellig fra en "0" eller en blank "". Fordi 0 og den blanke verdien er en kjent verdi, er imidlertid nullverdien ukjent.

NULL-verdier krever en spesiell håndtering i SQLite, vil vi se nå hvordan vi håndterer NULL-verdiene.

Søk etter NULL-verdier

Du kan ikke bruke den vanlige likhetsoperatoren (=) for å søke i nullverdiene. Følgende spørring søker for eksempel etter studentene som har en null DepartmentId-verdi:

SELECT * FROM Students WHERE DepartmentId = NULL;

Denne spørringen vil ikke gi noe resultat:

Fordi NULL-verdien ikke er lik noen annen verdi inkludert en null-verdi i seg selv, er det grunnen til at den ikke returnerte noe resultat.

For at spørringen skal fungere, må du imidlertid bruke operatoren «IS NULL» for å søke etter nullverdier som følger:

SELECT * FROM Students WHERE DepartmentId IS NULL;

Dette vil gi deg:

Spørringen vil returnere de studentene som har en null DepartmentId-verdi.

Hvis du vil få de verdiene som ikke er null, må du bruke operatoren «ER IKKE NULL» slik:

SELECT * FROM Students WHERE DepartmentId IS NOT NULL;

Dette vil gi deg:

Spørringen vil returnere de studentene som ikke har en NULL DepartmentId-verdi.

Betingede resultater

Hvis du har en liste over verdier og du vil velge en av dem basert på noen forhold. For det må betingelsen for den aktuelle verdien være sann for å bli valgt.

CASE-uttrykk vil evaluere disse listen over betingelser for alle verdiene. Hvis betingelsen er sann, vil den returnere den verdien.

For eksempel, hvis du har en kolonne "Karakter" og du vil velge en tekstverdi basert på karakterverdien som følger:

– «Utmerket» hvis karakteren er høyere enn 85.

– «Very Good» hvis karakteren er mellom 70 og 85.

– «Bra» hvis karakteren er mellom 60 og 70.

Deretter kan du bruke CASE-uttrykket for å gjøre det.

Dette kan brukes til å definere en viss logikk i SELECT-leddet slik at du kan velge visse resultater avhengig av visse forhold som if-setning for eksempel.

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

Du kan bruke forskjellige forhold:

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

Eller du kan bare bruke ett uttrykk og angi forskjellige mulige verdier å velge mellom:

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

Merk at ELSE-klausulen er valgfri.

Eksempel

I følgende eksempel bruker vi CASE-uttrykket med NULL-verdi i avdelings-ID-kolonnen i Student-tabellen for å vise teksten «Ingen avdeling» som følger:

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

• CASE-operatøren vil sjekke verdien av DepartmentId om den er null eller ikke.
• Hvis det er en NULL-verdi, vil den velge den bokstavelige verdien 'No Department' i stedet for DepartmentId-verdien.
• Hvis det ikke er en nullverdi, vil den velge verdien for DepartmentId-kolonnen.

Dette vil gi deg utdata som vist nedenfor:

Vanlig tabelluttrykk

Vanlige tabelluttrykk (CTE) er underspørringer som er definert inne i SQL-setningen med et gitt navn.

Den har en fordel fremfor underspørringene fordi den er definert ut fra SQL-setningene og vil gjøre spørringene lettere å lese, vedlikeholde og forstå.

Et vanlig tabelluttrykk kan defineres ved å sette WITH-leddet foran en SELECT-setning som følger:

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

«CTEname» er et hvilket som helst navn du kan gi CTE-en, som du kan bruke til å referere til senere. Merk at du kan definere SELECT-, UPDATE-, INSERT- eller DELETE-setninger på CTE-er.

La oss nå se et eksempel på hvordan du bruker CTE i SELECT-klausulen.

Eksempel

I følgende eksempel vil vi definere en CTE fra en SELECT-setning, og deretter bruke den senere på en annen spørring:

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 spørringen definerte vi en CTE og ga den navnet «AllDepartments». Denne CTE-en ble definert fra en SELECT-spørring:

SELECT DepartmentId, DepartmentName
  FROM Departments

Etter at vi definerte CTE brukte vi den i SELECT-spørringen som kommer etter den.

Merk at vanlige tabelluttrykk ikke påvirker utdataene til spørringen. Det er en måte å definere en logisk visning eller underspørring for å gjenbruke dem i samme spørring. Vanlige tabelluttrykk er som en variabel som du deklarerer, og gjenbruker den som en underspørring. Bare SELECT-setningen påvirker resultatet av spørringen.

Denne spørringen vil gi deg:

Avanserte søk

Avanserte spørringer er de spørringene som inneholder komplekse sammenføyninger, underspørringer og enkelte aggregater. I den følgende delen vil vi se et eksempel på en avansert spørring:

Hvor vi får tak i,

• Avdelingens navn med alle studentene for hver avdeling
• Elevens navn adskilt med komma og
• Viser at avdelingen har minst tre studenter 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 la til en JOIN-klausul for å hente DepartmentName fra Departments-tabellen. Etter det la vi til en GROUP BY-klausul med to aggregerte funksjoner:

• "COUNT" for å telle studentene for hver avdelingsgruppe.
• GROUP_CONCAT for å sette sammen elever for hver gruppe med komma atskilt i én streng.

Etter GROUP BY brukte vi HAVING-klausulen til å filtrere avdelingene og velge bare de avdelingene som har minst 3 studenter.

Resultatet blir som følger:

Spørsmål og svar