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