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

For å 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

  1. Åpne Min datamaskin og naviger til følgende katalog "C:\sqlite"Og
  2. Åpne deretter "sqlite3.exe"

Lese data med Select

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

Lese data med Select

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:

Lese data med Select

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:

Lese data med Select

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:

Lese data med Select

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:

Navn og alias

Merk at kolonnenavnet fortsatt "Studentnavn"; 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:

Navn og alias

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:

Navn og alias

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:

Navn og alias

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 Studenttabellen et alias "s" og avdelingstabellen et alias "d".
  • Så i stedet for å bruke hele tabellens navn, brukte vi aliasene deres for å referere til dem.
  • INNER JOIN slår sammen to eller flere tabeller ved å bruke en betingelse. I vårt eksempel slo vi sammen Studenter-tabellen med Institutttabellen med Avdelings-ID-kolonnen. Det er også en utdypende forklaring på INNER JOIN i "SQLite Blir med»-opplæringen.

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

Navn og alias

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-leddet brukes til å filtrere resultatsettet som returneres av SQL-spørring. 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 slå seg sammen til et nytt alias "StudentIdWithName"

  • Den bokstavelige strengverdien "ID med navn: "
  • med verdien av "Student-ID” kolonne og
  • med verdien fra "Studentnavn”-kolonnen

SQLite sammenkoblingsoperatoren '||'

SQLite CAST-operatør:

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

For eksempel, hvis du 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' ​​SOM EKTE)". Eller hvis du har en desimalverdi som 12.5, og du bare trenger å få heltallsdelen, kan du kaste den til et heltall som dette "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:

SQLite CAST Operator

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:

  • Tillegg "+” – gi summen av de to operandene.
  • Subtraksjon "-” – trekker fra de to operandene og resulterer i differansen.
  • Multiplikasjon "*” – produktet av de to operandene.
  • Påminnelse (modulo) "%” – gir resten som er resultatet av å dele en operand med den andre operanden.
  • divisjon "/” – returnerer kvotientresultatene fra å dele den venstre operanden med den høyre operanden.

Eksempel:

I følgende eksempel vil vi prøve de fem aritmetiske operatorene med bokstavelige numeriske verdier i samme

velg klausul:

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

Dette vil gi deg:

SQLite Aritmetisk Operatorer

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 sant hvis venstre operand er mindre enn høyre operande.
  • "<=” – returnerer sant hvis den venstre operanden er mindre enn eller lik den høyre operanden.
  • ">” – returnerer sant hvis venstre operande er større enn høyre operande.
  • ">=” – returnerer sant hvis den venstre operanden er større enn eller lik den høyre operanden.
  • "="Og"==” – returnerer sant hvis de to operandene er like. Merk at begge operatørene er like, og det er ingen forskjell mellom dem.
  • "!="Og"<>” – returnerer sant hvis de to operandene ikke er like. Merk at begge operatørene 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 Sammenligning Operatorer

SQLite Mønstermatchende operatører

"SOM” – brukes til mønstertilpasning. Bruker "I likhet med", kan du søke etter verdier som samsvarer med et mønster spesifisert ved hjelp av 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 Studentnavn LIKE '%a%' – dette vil søke etter elevenes navn som inneholder bokstaven "a" i en hvilken som helst posisjon i kolonnen Studentnavn.
  • Starter med mønsteret. For eksempel, "Studentnavn LIKE 'a%'” – søk på elevenes navn som begynner med bokstaven “a”.
  • Avslutter med mønsteret. For eksempel, "Studentnavn LIKE '%a'” – Søk etter elevenes navn som slutter på bokstaven “a”.
  • Matche et enkelt tegn i en streng ved å bruke understrekingsbokstaven "_". For eksempel, "Studentnavn LIKE 'J___'” – Søk etter elevenes navn som er på 4 tegn. Den må begynne med "J"-bokstaven og kan ha alle tre andre tegn etter "J"-bokstaven.

Eksempler på mønstersamsvar:

  1. Få elevers navn som begynner med bokstaven 'j':
    SELECT StudentName FROM Students WHERE StudentName LIKE 'j%';

    Resultat:

    SQLite Mønster Matching Operatorer

  2. Få elevenes navn til slutt med «y»-bokstaven:
    SELECT StudentName FROM Students WHERE StudentName LIKE '%y';

    Resultat:

    SQLite Mønster Matching Operatorer

  3. Få elevenes navn som inneholder bokstaven 'n':
    SELECT StudentName FROM Students WHERE StudentName LIKE '%n%';

    Resultat:

    SQLite Mønster Matching Operatorer

"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:

SQLite Mønster Matching Operatorer

  • 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%');

SQLite OG Operator

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:

SQLite OR Operator

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

BETWEEN brukes til å velge de verdiene som er innenfor et område på to verdier. For eksempel, "X MELLOM Y OG Z” vil 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 MELLOM Operator

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: "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:

SQLite IN Operator

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 å bruke flere OR-operatorer. «WHERE StudentId IN(2, 4, 6, 8)"tilsvarer" HVOR (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);"

Som dette:

SQLite IN Operator

SQLite IKKE I

"NOT IN"-operand er det motsatte av IN-operatøren. Men med samme syntaks; det tar en operand og en liste over operander. Det vil returnere sant hvis den første operanden verdien ikke er lik en av operandenes verdi fra listen. dvs. den vil returnere sann (0) hvis listen over operander ikke inneholder den første operanden. Slik: "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

SQLite IKKE I Operator

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:

SQLite IKKE I Operator

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:

SQLite Eksisterer Operator

Bare de tre avdelingene "IT, fysikk og kunst" vil bli returnert. Og avdelingsnavnet "Math” vil ikke bli returnert fordi det ikke er noen student i den avdelingen, så avdelings-ID-en finnes ikke i elevtabellen. Det er derfor EXISTS-operatøren ignorerte "Math” avdeling.

SQLite IKKE

Revsletter resultatet av den foregående operatoren som kommer etter den. 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);

Produksjon:

SQLite IKKE Operator

Bare avdelingen "Math " vil bli returnert. Fordi "Math” avdeling er den eneste avdelingen som ikke finnes i elevtabellen.

Begrensning og bestilling

SQLite Kontakt

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:

Begrensning og bestilling

  • 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-leddet kan du velge et spesifikt antall rader fra en bestemt posisjon ved å bruke OFFSET-leddet. For eksempel, "LIMIT 4 OFFSET 4” vil ignorere de første 4 radene, og returnerte 4 rader fra den femte radene, så du vil få radene 5,6,7 og 8.

Merk at OFFSET-klausulen er valgfri, du kan skrive den som "LIMIT 4, 4" og det 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:

Begrensning og bestilling

Fjerner duplikater

Hvis SQL-spørringen din returnerer dupliserte verdier, kan du bruke "DISTINCT” nøkkelord for å fjerne disse duplikatene og returnere distinkte verdier. Du kan spesifisere mer enn én kolonne etter DISTINCT-tasten.

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:

Fjerne duplikater

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:

Fjerne duplikater

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":

Aggregat:SQLite AVG()

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:

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

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:

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

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:

Aggregert:GROUP_CONCAT(DISTINCT Avdelingsnavn) Eksempel

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:

Aggregate:GROUP_CONCAT(DepartmentName ,'&') Eksempel

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 vil vi bruke MIN- og MAX-funksjonene for å få det høyeste merket og det laveste merket fra "Marks" tabell:

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

Dette vil gi deg:

Aggregat:SQLite MAX() og MIN()

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 vil vi bruke SUM og total for å få summen av alle merkene i "Marks" tabeller:

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

Dette vil gi deg:

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

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:

Aggregert: Forskjellen mellom SUM og TOTAL Eksempel

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:

Gruppe BY:HAVING-klausul

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:

Gruppe BY

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:

SQLite Spørring og underspørring: Bruker underspørring i FROM-klausulen

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:

SQLite Spørring og underspørring: Bruke underspørring i WHERE-klausulen

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:

Sett Operasjoner - UNION Eksempel

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:

Sett Operasjoner - UNION Eksempel

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:

Sett Operasjoner - STYRING

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:

Sett Operasjoner - UNNTATT

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

NULL håndtering

Den "NULL"verdi er en spesiell verdi i SQLite. Den brukes til å representere en verdi som er ukjent eller manglende verdi. Merk at nullverdien er helt annerledes enn "0" eller tom "" verdi. Fordi 0 og den tomme verdien er en kjent verdi, er 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:

NULL-håndtering

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.

  • Men for å få spørringen til å fungere, må du bruke "ER NULL" operator for å søke etter nullverdier som følger:
SELECT * FROM Students WHERE DepartmentId IS NULL;

Dette vil gi deg:

NULL-håndtering

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 "ER IKKE NULL" operatør som dette:
SELECT * FROM Students WHERE DepartmentId IS NOT NULL;

Dette vil gi deg:

NULL-håndtering

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:

  1. Du kan bruke forskjellige forhold:
CASE 
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  WHEN condition3 THEN result3
  …
  ELSE resultn
END
  1. 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 det følgende eksempelet vil vi bruke CASE uttrykk med NULL verdi i avdelings-ID-kolonnen i Studenter-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:

Betingede resultater

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

Den "CTEnavn” er et hvilket som helst navn du kan gi for CTE, du kan bruke det til å referere til det 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 "Alle avdelinger«. Denne CTE 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:

Vanlig tabelluttrykk

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 BLI klausul for å hente avdelingsnavnet fra tabellen avdelinger. 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:

Avanserte spørringer

Oppsummering

Dette var en introduksjon til skriving SQLite spørringer og det grunnleggende om spørring i databasen og hvordan du kan filtrere de returnerte dataene. Du kan nå, skrive din egen SQLite spørringer.