For å skrive SQL-spørsmål i en SQLite-database, må du vite hvordan SELECT, FROM, WHERE, GROUP BY, ORDER BY og LIMIT-setningene fungerer og hvordan du bruker dem.
I løpet av denne opplæringen lærer du hvordan du bruker disse leddene og hvordan du skriver SQLite-ledd.
I denne veiledningen vil du lære-
- Lese data med Select
- Navn og alias
- HVOR
- Begrensning og bestilling
- Fjerner duplikater
- Samlet
- Gruppe av
- Spørring og undersøkelse
- Sett operasjoner -UNION, kryss
- NULL håndtering
- Betingede resultater
- Vanlig borduttrykk
- Avanserte spørsmål
Lese data med Select
SELECT-setningen er hoveduttalelsen du bruker for å søke etter en SQLite-database. I SELECT-setningen oppgir du hva du skal velge. Men før velg klausulen, la oss se fra hvor vi kan velge data ved hjelp av FROM-klausulen.
FROM-setningen brukes til å spesifisere hvor du vil velge data. I fra-leddet kan du spesifisere en eller flere tabeller eller underspørringer du vil velge data fra, som vi vil se senere på veiledningene.
Merk at for alle følgende eksempler må du kjøre sqlite3.exe og åpne en forbindelse 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ørsmål i databasen.
I SELECT-setningen kan du ikke bare velge et kolonnenavn, men du har mange andre alternativer for å spesifisere hva du skal velge. Som følger:
Å VELGE *
Denne kommandoen vil velge alle kolonnene fra alle de refererte tabellene (eller underspørringene) i FROM-setningen. For eksempel:
Å VELGE *FRA studenterINNER JOIN Avdelinger PÅ 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:
VELG studenter. *FRA studenterINNER JOIN Avdelinger PÅ Students.DepartmentId = Departments.DepartmentId;
Dette vil bare velge alle kolonnene fra studenttabellen:
En bokstavelig verdi
En bokstavelig verdi er en konstant verdi som kan spesifiseres i seleksjonsuttalelsen. Du kan bruke bokstavelige verdier normalt på samme måte som du bruker kolonnenavn i SELECT-setningen. Disse bokstavverdiene vises for hver rad fra radene som returneres av SQL-spørringen.
Her er noen eksempler på forskjellige bokstavverdier du kan velge:
- Numerisk bokstavelig - tall i hvilket som helst format som 1, 2.55, ... etc.
- Strengbokstaver - Enhver streng 'USA', 'dette er en eksempletekst', ... etc.
- NULL - NULL-verdi.
- Current_TIME - Det vil gi deg gjeldende tid.
- CURRENT_DATE - dette vil gi deg den gjeldende datoen.
Dette kan være nyttig i noen situasjoner der du må velge en konstant verdi for alle de returnerte radene. Hvis du for eksempel vil velge alle studentene fra Students-tabellen, med en ny kolonne kalt et land som inneholder verdien "USA", kan du gjøre dette:
VELG *, 'USA' SOM land FRA studenter;
Dette vil gi deg alle elevenes kolonner, pluss en ny kolonne "Land" som dette:
Merk at denne nye kolonnen Land ikke er en ny kolonne lagt til tabellen. Det er en virtuell kolonne, opprettet i spørringen for å vise resultatene, og den blir ikke opprettet på bordet.
Navn og alias
Aliaset er et nytt navn for kolonnen som lar deg velge kolonnen med et nytt navn. Kolonnealiasene spesifiseres ved hjelp av nøkkelordet "AS".
Hvis du for eksempel vil velge kolonnen Studentnavn som skal returneres med "Studentnavn" i stedet for "Studentnavn", kan du gi det et alias som dette:
VELG Studentnavn SOM 'Studentnavn' FRA studenter;
Dette vil gi deg studentenes navn med navnet "Student Name" i stedet for "StudentName" slik:
Merk deg at kolonnenavnet fremdeles er " Studentnavn "; kolonnen Studentnavn er fortsatt den samme, den endres ikke av aliaset.
Aliaset endrer ikke kolonnenavnet. det vil bare endre visningsnavnet i SELECT-setningen.
Vær også oppmerksom på at nøkkelordet "AS" er valgfritt, du kan sette aliasnavnet uten det, noe som dette:
VELG Studentnavn 'Studentnavn' FRA studenter;
Og det vil gi deg nøyaktig samme utdata som forrige spørring:
Du kan også gi tabeller aliaser, ikke bare kolonner. Med samme nøkkelord "AS". For eksempel kan du gjøre dette:
VELG s. * FRA Students AS s;
Dette vil gi deg alle kolonnene i tabellen Studenter:
Dette kan være veldig nyttig hvis du blir med 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ørsmål:
VELG Students.StudentName, Departments.DepartmentNameFRA studenterINNER JOIN Avdelinger PÅ Students.DepartmentId = Departments.DepartmentId;
Dette spørsmålet velger hvert studentnavn fra "Students" -tabellen med avdelingsnavnet fra "Departements" -tabellen:
Imidlertid kan den samme spørringen skrives slik:
VELG s.Studentnavn, d.avdelingsnavnFRA Students AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
- Vi ga studenttabellen et alias "s" og instituttabellen et alias "d".
- Så i stedet for å bruke navnet på hele tabellen, brukte vi aliasene deres for å referere til dem.
- INNER JOIN føyer sammen to eller flere bord ved å bruke en betingelse. I vårt eksempel ble vi med på Students-tabellen med Institutt-tabellen med DepartmentId-kolonnen. Det er også en grundig forklaring på INNER JOIN i "SQLite Joins" -veiledningen.
Dette vil gi deg den eksakte produksjonen som forrige spørring:
HVOR
Å skrive SQL-spørsmål ved å bruke SELECT-setningen alene med FROM-setningen som vi så i forrige avsnitt, vil gi deg alle radene fra tabellene. Men hvis du vil filtrere de returnerte dataene, må du legge til en "WHERE" -klausul.
WHERE-setningen brukes til å filtrere resultatsettet som returneres av SQL-spørringen. Slik fungerer WHERE-klausulen:
- I WHERE-setningen kan du spesifisere et "uttrykk".
- Dette uttrykket vil bli evaluert for hver rad som returneres fra tabellen (e) spesifisert i FROM-setningen.
- Uttrykket vil bli evaluert som et boolsk uttrykk, med et resultat enten sant, usant eller null.
- Deretter returneres bare rader som uttrykket ble evaluert med en sann verdi for, og de med falske eller null-resultater blir ignorert og ikke inkludert i resultatsettet.
- For å filtrere resultatsettet ved bruk av WHERE-setningen, må du bruke uttrykk og operatorer.
Liste over operatører i SQLite og hvordan du bruker dem
I det følgende 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-setningen og WHERE-setningen.
I de følgende eksemplene vil vi prøve uttrykkene og operatorene i både select-setningen og WHERE-setningen. For å vise deg hvordan de presterer.
Det er forskjellige typer uttrykk og operatorer som du kan spesifisere som følger:
SQLite sammenkoblingsoperatøren "||"
Denne operatøren brukes til å sammenkoble en eller flere bokstavlige verdier eller kolonner med hverandre. Det vil gi en streng med resultater fra alle sammenkoblede bokstavverdier eller kolonner. For eksempel:
VELG 'Id med navn:' || StudentId || Studentnavn AS StudentIdWithNameFRA studenter;
Dette vil sammenføyes til et nytt alias " StudentIdWithName ":
- Den bokstavelige strengverdien " Id med navn: "
- med verdien av " StudentId " -kolonnen og
- med verdien fra kolonnen " Studentnavn "
SQLite CAST-operatør:
CAST-operatøren brukes til å konvertere en verdi fra en datatype til en annen datatype.
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-operatøren til å gjøre dette slik" CAST ('12 .5' AS REAL) ". Eller hvis du har en desimalverdi som 12,5, og du bare trenger å få heltaldelen, 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:
VELG 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 blir konvertert til en REAL verdi.
- CAST (12.5 AS INTEGER) - verdien 12.5 er en desimalverdi, den blir konvertert til å være et heltall. Desimaldelen blir avkortet, og den blir 12.
SQLite Arithmetic Operators:
Ta to eller flere numeriske bokstavverdier eller numeriske kolonner, og returner en numerisk verdi. Regneoperatørene som støttes i SQLite er:
|
Eksempel:
I det følgende eksemplet vil vi prøve de fem aritmetiske operatorene med bokstavelige numeriske verdier i det samme
velg klausul:
VELG 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 i SQLite så lenge vi velger bokstavelige verdier.
SQLite-sammenligningsoperatører
Sammenlign to operander med hverandre og returner en sann eller usann som følger:
|
Merk at SQLite uttrykker den sanne verdien med 1 og den falske verdien med 0.
Eksempel:
Å VELGE10 <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 slikt:
SQLite Pattern Matching operatorer
" LIKE " - brukes til mønstermatching. Ved å bruke " Like " kan du søke etter verdier som samsvarer med et mønster som er spesifisert med et jokertegn.
Operanden til venstre kan enten være en streng bokstavelig verdi eller en strengkolonne. Mønsteret kan spesifiseres som følger:
- Inneholder mønster. For eksempel Studentnavn LIKE '% a%' - dette vil søke etter studentenes 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 etter studentenavnene som begynner med bokstaven "a".
- Ender med mønsteret. For eksempel " Studentnavn LIKE '% a' " - Søk etter studentenavnene som slutter med bokstaven "a".
- Matcher et hvilket som helst enkelt tegn i en streng ved hjelp av understrekingsbokstaven "_". For eksempel " Studentnavn LIKE 'J___' " - Søk etter studentenavn som er fire tegn lange. Det må starte med "J" -bokstaven og kan ha tre andre tegn etter "J" -bokstaven.
Eksempler på mønstermatching:
- Få studentnavn som begynner med 'j' bokstaven:
VELG Elevnavn fra studenter der studentnavn ser ut som 'j%';
Resultat:
- Få studentenes navn med y-bokstaven:
VELG Studentnavn FRA studenter der studentnavn liker '% y';
Resultat:
- Få studentenavn som inneholder 'n' bokstaven:
VELG Elevnavn fra studenter DER Elevnavn som '% n%';
Resultat:
"GLOB" - tilsvarer LIKE-operatøren, men GLOB er mellom store og små bokstaver, i motsetning til LIKE-operatøren. Følgende to kommandoer vil for eksempel gi forskjellige resultater:
VELG 'Jack' GLOB 'j%';VELG 'Jack' LIKE 'j%';
Dette vil gi deg:
- Den første setningen returnerer 0 (false) fordi GLOB-operatøren er mellom store og små bokstaver, så 'j' er ikke lik 'J'. Imidlertid vil den andre setningen returnere 1 (sant) fordi LIKE-operatøren ikke skiller mellom store og små bokstaver, så 'j' er lik 'J'.
Andre operatører:
SQLite OG
En logisk operatør som kombinerer ett eller flere uttrykk. Det vil returnere sant, bare hvis alle uttrykkene gir en "sann" verdi. Imidlertid vil den bare returnere falsk hvis alle uttrykkene gir en "falsk" verdi.
Eksempel:
Følgende spørsmål søker etter studenter som har StudentId> 5 og Studentnavn begynner med bokstaven N, de returnerte studentene må oppfylle de to betingelsene:
Å VELGE *FRA studenterWHERE (StudentId> 5) AND (StudentName LIKE 'N%');
Som utgang, i skjermbildet ovenfor, vil dette bare gi deg "Nancy". Nancy er den eneste studenten som oppfyller begge vilkårene.
SQLite ELLER
En logisk operator som kombinerer ett eller flere uttrykk, slik at hvis en av de kombinerte operatorene gir true, vil den returnere true. Imidlertid, hvis alle uttrykkene gir falske, vil de returnere falske.
Eksempel:
Følgende spørsmål søker etter studenter som har StudentId> 5 eller Studentnavn begynner med bokstaven N, de returnerte studentene må oppfylle minst en av vilkårene:
Å VELGE *FRA studenterHVOR (StudentId> 5) ELLER (Studentnavn LIKE 'N%');
Dette vil gi deg:
Som utgang, i skjermbildet ovenfor, vil dette gi deg navnet på en student med bokstaven "n" i navnet pluss student-IDen som har verdi> 5.
Som du kan se er resultatet annerledes enn spørringen med AND-operatoren.
SQLite MELLOM
MELLOM brukes til å velge de verdiene som ligger innenfor et område på to verdier. For eksempel vil " X MELLOM Y OG Z " returnere true (1) hvis verdien X er mellom de to verdiene Y og Z. Ellers vil den returnere false (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 det følgende eksemplets spørsmål vil vi skrive et spørsmål for å få studenter med Id-verdi mellom 5 og 8:
Å VELGE *FRA studenterHVOR Student hadde mellom 5 og 8;
Dette vil bare gi studentene med ID 5, 6, 7 og 8:
SQLite IN
Tar en operand og en liste over operander. Det vil være sant hvis den første operandverdien er lik en av operandens verdi fra listen. IN-operatøren returnerer sant (1) hvis listen over operander inneholder den første operandverdien innenfor 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ørsmål vil bare velge elever med ID 2, 4, 6, 8:
Å VELGE *FRA studenterDER StudentId IN (2, 4, 6, 8);
Som dette:
Den forrige spørringen vil gi det eksakte resultatet som følgende spørsmål fordi de er likeverdige:
Å VELGE *FRA studenterHVOR (StudentId = 2) ELLER (StudentId = 4) ELLER (StudentId = 6) ELLER (StudentId = 8);
Begge spørsmålene gir den eksakte effekten. Forskjellen mellom de to spørringene er imidlertid den første spørringen vi brukte "IN" -operatøren. I det andre spørsmålet brukte vi flere "ELLER" -operatører.
IN-operatøren tilsvarer bruk av flere OR-operatører. " WHERE StudentId IN (2, 4, 6, 8) " tilsvarer " WHERE (StudentId = 2) ELLER (StudentId = 4) ELLER (StudentId = 6) ELLER (StudentId = 8); "
Som dette:
SQLite IKKE INN
"NOT IN" -operand er motsatt av IN-operatøren. Men med samme syntaks; det tar en operand og en liste over operander. Det vil være sant hvis den første operandverdien ikke er lik en av operandens verdi fra listen. dvs. det vil returnere true (0) hvis listen over operander ikke inneholder den første operanden. Slik: " col NOT IN (x, y, z) ". Dette tilsvarer " (kol <> x) OG (kol <> y) OG (kol <> z) ".
Eksempel:
Følgende spørsmål vil velge elever med id som ikke er lik en av disse Id 2, 4, 6, 8:
Å VELGE *FRA studenterDER Student IKKE ER INN (2, 4, 6, 8);
Som dette
Den forrige spørringen vi gir det nøyaktige resultatet som følgende spørsmål fordi de er ekvivalente:
Å VELGE *FRA studenterHVOR (StudentId <> 2) OG (StudentId <> 4) OG (StudentId <> 6) OG (StudentId <> 8);
Som dette:
I skjermbildet ovenfor,
Vi brukte flere ikke like operatører "<>" for å få en liste over studenter, som ikke er lik hverken av følgende Ids 2, 4, 6 eller 8. Dette spørsmålet vil returnere alle andre studenter enn disse ID-ene.
SQLite EXISTS
EXISTS-operatørene tar ikke noen operander; det tar bare en SELECT-klausul etter den. EXISTS-operatøren vil returnere true (1) hvis det er noen rader som er returnert fra SELECT-setningen, og den vil returnere false (0) hvis det ikke er noen rader i det hele tatt som returneres fra SELECT-setningen.
Eksempel:
I det følgende eksemplet velger vi avdelingens navn, hvis avdelings-ID-en finnes i studenttabellen:
VELG avdelingsnavnFRA avdelinger AS dWHERE 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 " Matematikk " returneres ikke fordi det ikke er noen student i den avdelingen, så avdelings-ID finnes ikke i studenttabellen. Derfor ignorerte EXISTS-operatøren avdelingen " Math ".
SQLite IKKE
Snu resultatet av den forrige operatøren som kommer etter den. For eksempel:
- IKKE MELLOM - Det vil være sant hvis MELLOM returnerer falsk og omvendt.
- IKKE LIKE - Det vil returnere sant hvis LIKE returnerer falsk og omvendt.
- IKKE GLOB - Det vil returnere sant hvis GLOB returnerer falskt og omvendt.
- IKKE EKSISTER - Det vil returnere sant hvis EKSISTER returnerer falsk og omvendt.
Eksempel:
I det følgende eksemplet vil vi bruke IKKE-operatøren sammen med EXISTS-operatøren for å få avdelingens navn som ikke finnes i Students-tabellen, som er det motsatte resultatet av EXISTS-operatøren. Så søket vil bli gjort gjennom DepartmentId som ikke finnes i avdelingstabellen.
VELG avdelingsnavnFRA avdelinger AS dHVOR IKKE FINNES (VELG avdeling IDFRA Students AS sHVOR d.DepartmentId = s.DepartmentId);
Utgang :
Bare avdelingen " Matematikk " vil bli returnert. Fordi " Matematikk " -avdelingen er den eneste avdelingen, finnes det ikke i studenttabellen.
Begrensning og bestilling
SQLite-ordre
SQLite Order er å sortere resultatet ditt etter ett eller flere uttrykk. For å bestille resultatsettet må du bruke ORDER BY-setningen som følger:
- Først må du spesifisere ORDER BY-setningen.
- 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 sorteres stigende.
- Du kan spesifisere flere uttrykk ved å bruke "," mellom hverandre.
Eksempel
I det følgende eksemplet velger vi alle studentene ordnet etter navn, men i fallende rekkefølge, deretter avdelingsnavnet i stigende rekkefølge:
VELG s.Studentnavn, d.avdelingsnavnFRA Students AS sINNER JOIN Avdelinger AS d ON s.DepartmentId = d.DepartmentIdBESTILL AV D. Avdelingsnavn ASC, s.Studentnavn DESC;
Dette vil gi deg:
- SQLite vil først bestille alle studentene etter avdelingsnavnet i stigende rekkefølge
- For hvert avdelingsnavn vil alle studentene under avdelingsnavnet vises i synkende rekkefølge etter navn
SQLite Limit:
Du kan begrense antall rader som returneres av SQL-spørringen, ved å bruke LIMIT-setningen. LIMIT 10 vil for eksempel gi deg bare 10 rader og ignorere alle de andre radene.
I LIMIT-setningen kan du velge et spesifikt antall rader fra en bestemt posisjon ved å bruke OFFSET-setningen. For eksempel vil " LIMIT 4 OFFSET 4 " ignorere de første 4 radene, og returnerte 4 radene fra de femte radene, slik at du får rad 5,6,7 og 8.
Merk at OFFSET-leddet er valgfritt, du kan skrive det som " LIMIT 4, 4 " og det vil gi deg de eksakte resultatene.
Eksempel :
I det følgende eksemplet returnerer vi bare 3 studenter som starter fra student-id 5 ved hjelp av spørringen:
VELG * FRA studenter GRENSE 4,3;
Dette gir deg bare tre studenter 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 duplikatene og returnere på forskjellige verdier. Du kan spesifisere mer enn en kolonne etter DISTINCT-tasten.
Eksempel:
Følgende spørsmål returnerer dupliserte "avdelingsnavnverdier": Her har vi dupliserte verdier med navnene IT, Fysikk og kunst.
VELG d. AvdelingsnavnFRA Students AS sINNER 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 samme spørsmål for å fjerne disse duplikatene og få bare unike verdier. Som dette:
VELG DISTINCT d. AvdelingsnavnFRA Students AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Dette gir deg bare tre unike verdier for kolonnen avdelingsnavn:
Samlet
SQLite Aggregates er innebygde funksjoner definert i SQLite som vil gruppere flere verdier av flere rader i en verdi.
Her er aggregatene som støttes av SQLite:
SQLite AVG ()
Returnerte gjennomsnittet for alle x-verdiene.
Eksempel:
I det følgende eksemplet får vi gjennomsnittlig karakter studentene får fra alle eksamenene:
VELG AVG (merke) FRA merker;
Dette vil gi deg verdien "18.375":
Disse resultatene kommer fra summering av alle merkeverdiene delt på antall.
COUNT () - COUNT (X) eller COUNT (*)
Returnerer det totale antallet antall 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. Det vil ignorere NULL-verdier.
- COUNT (*): Tell alle radene fra alle kolonnene.
- TELLING (DISTINCT x): Du kan spesifisere et DISTINCT-nøkkelord før x som vil telle antallet av de forskjellige verdiene til x.
Eksempel
I det følgende eksemplet får vi det totale antallet avdelinger med COUNT (DepartmentId), COUNT (*) og COUNT (DISTINCT DepartmentId) og hvordan de er forskjellige:
VELG COUNT (DepartmentId), COUNT (DISTINCT DepartmentId), COUNT (*) FRA studenter;
Dette vil gi deg:
Som følger:
- COUNT (DepartmentId) vil gi deg tellingen på hele avdelings-ID-en, og den vil ignorere nullverdiene.
- COUNT (DISTINCT DepartmentId) gir deg forskjellige verdier for DepartmentId, som bare er 3. Hvilke er de tre forskjellige verdiene for avdelingsnavnet. 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 studenttabellen som er 10 rader for 10 studenter.
GROUP_CONCAT () - GROUP_CONCAT (X) eller GROUP_CONCAT (X, Y)
GROUP_CONCAT aggregatfunksjon sammenføyder flere verdier til en verdi med komma for å skille dem. Den har følgende alternativer:
- GROUP_CONCAT (X): Dette vil sammenkoble all verdien av x i en streng, med kommaet "," brukt som skilletegn mellom verdiene. NULL-verdier blir ignorert.
- GROUP_CONCAT (X, Y): Dette vil sammenkoble verdiene til x til en streng, med verdien av y brukt som skilletegn mellom hver verdi i stedet for standard skilletegn ','. NULL-verdier vil også bli ignorert.
- GROUP_CONCAT (DISTINCT X): Dette vil sammenkoble alle de forskjellige verdiene til x i en streng, med kommaet "," brukt som skilletegn mellom verdiene. NULL-verdier blir ignorert.
GROUP_CONCAT (avdelingsnavn) Eksempel
Følgende spørring vil sammenkoble alle avdelingsnavnets verdier fra studentene og avdelingstabellen i en streng komma skilt. Så i stedet for å returnere en liste over verdier, en verdi på hver rad. Den vil bare returnere en verdi på en rad, med alle verdiene komma atskilt:
VELG GROUP_CONCAT (d. Avdelingsnavn)FRA Students AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Dette vil gi deg:
Dette vil gi deg listen over 8 avdelingers navneverdier sammenkoblet i en streng komma atskilt.
GROUP_CONCAT (DISTINCT avdelingsnavn) Eksempel
Følgende spørring vil sammenkoble de forskjellige verdiene til avdelingsnavnet fra tabellen studenter og avdelinger til en streng komma skilt:
VELG GROUP_CONCAT (DISTINCT d. Avdelingsnavn)FRA Students AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Dette vil gi deg:
Legg merke til hvordan resultatet er annerledes enn forrige resultat; bare tre verdier returnert som er de forskjellige avdelingenes navn, og duplikatverdiene ble fjernet.
GROUP_CONCAT (avdelingsnavn, '&') Eksempel
Følgende spørring vil sammenføye alle verdiene i avdelingsnavnskolonnen fra student- og avdelingstabellen til en streng, men med tegnet '&' i stedet for et komma som skilletegn:
VELG GROUP_CONCAT (d. Avdelingsnavn, '&')FRA Students AS sINNER 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 () & MIN ()
MAX (X) gir deg den høyeste verdien fra X-verdiene. MAX returnerer en NULL-verdi hvis alle verdiene til x er null. Mens MIN (X) gir deg den minste verdien fra X-verdiene. MIN returnerer en NULL-verdi hvis alle verdiene til X er null.
Eksempel
I det følgende spørsmålet vil vi bruke MIN- og MAX-funksjonene for å få høyeste og laveste merke fra " Merker " -tabellen:
VELG MAX (merke), MIN (merke) FRA merker;
Dette vil gi deg:
SQLite SUM (x), totalt (x)
Begge deler returnerer summen av alle x-verdiene. Men de er forskjellige i det følgende:
- SUM vil returnere null hvis alle verdiene er null, men Total vil returnere 0.
- TOTAL returnerer alltid flytende punktverdier. SUM returnerer et heltall hvis alle x-verdiene er et helt tall. Imidlertid, hvis verdiene ikke er et helt tall, vil den returnere en flytende verdi.
Eksempel
I det følgende spørsmålet vil vi bruke SUM og total for å få summen av alle merkene i " Merker " -tabellene:
VELG SUM (merke), TOTAL (merke) FRA merker;
Dette vil gi deg:
Som du ser, returnerer TOTAL alltid et flytende punkt. Men SUM returnerer et heltall fordi verdiene i "Merk" -kolonnen kan være i heltall.
Forskjellen mellom SUM og TOTAL eksempel:
I det følgende spørsmålet viser vi forskjellen mellom SUM og TOTAL når de får SUM av NULL-verdier:
VELG SUM (merke), TOTAL (merke) FRA merker der 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 en blank, mens TOTAL returnerer 0.
Gruppe av
GROUP BY-setningen brukes til å spesifisere en eller flere kolonner som skal brukes til å gruppere radene i grupper. Radene med de samme verdiene blir samlet (ordnet) sammen i grupper.
For andre kolonner som ikke er inkludert i gruppen etter kolonner, kan du bruke en samlet funksjon for den.
Eksempel:
Følgende spørsmål vil gi deg det totale antallet studenter som er til stede i hver avdeling.
VELG d.DepartmentName, COUNT (s.StudentId) AS StudentsCountFRA Students AS sINNER JOIN Avdelinger AS d ON s.DepartmentId = d.DepartmentIdGRUPPE AV d. Avdelingsnavn;
Dette vil gi deg:
GROUPBY avdelingsnavn-ledd vil gruppere alle studentene i grupper en for hvert avdelingsnavn. For hver gruppe "avdeling" vil den telle studentene på den.
HAR klausul
Hvis du vil filtrere gruppene som returneres av GROUP BY-setningen, kan du spesifisere en "HAVING" -klausul med uttrykk etter GROUP BY. Uttrykket vil bli brukt til å filtrere disse gruppene.
Eksempel
I det følgende spørsmålet velger vi de avdelingene som bare har to studenter:
VELG d.DepartmentName, COUNT (s.StudentId) AS StudentsCountFRA Students AS sINNER JOIN Avdelinger AS d ON s.DepartmentId = d.DepartmentIdGRUPPE AV d. AvdelingsnavnHAR TELL (s.StudentId) = 2;
Dette vil gi deg:
Paragrafen HAVING COUNT (S.StudentId) = 2 filtrerer gruppene som returneres og returnerer bare de gruppene som inneholder nøyaktig to elever på den. I vårt tilfelle har Arts-avdelingen to studenter, så den vises i utdataene.
SQLite Query & Subquery
Inne i et hvilket som helst spørsmål kan du bruke et annet spørsmål enten i SELECT, INSERT, DELETE, UPDATE eller i et annet subquery.
Denne nestede spørringen kalles en underspørring. Vi vil nå se noen eksempler på bruk av underspørringer i SELECT-setningen. I modifiseringsdataveiledningen vil vi imidlertid se hvordan vi kan bruke underspørsmål med INSERT, DELETE og UPDATE-setningen.
Bruker underspørring i FROM-leddeksempelet
I det følgende spørsmålet vil vi inkludere et underspørring inne i FROM-setningen:
Å VELGEs.Studentnavn, t.MarkFRA Students AS sINNRE MEDLEM(VELG StudentId, MarkFRA TEST AS tINNER JOIN Merker AS m ON t.TestId = m.TestId) PÅ s.StudentId = t.StudentId;
Spørringen:
VELG StudentId, MarkFRA TEST AS tINNER JOIN Merker AS m ON t.TestId = m.TestId
Ovennevnte spørring kalles her en undersøking fordi den er nestet i FROM-setningen. Legg merke til at vi ga det et aliasnavn "t" slik at vi kan referere til kolonnene som er returnert fra det i spørringen.
Dette spørsmålet vil gi deg:
Så i vårt tilfelle,
- s.StudentName er valgt fra hovedforespørselen som gir navnet på studentene og
- t.Mark er valgt fra undersøket; som gir karakterer oppnådd av hver av disse studentene
Bruker underspørring i WHERE-leddeksemplet
I det følgende spørsmålet vil vi inkludere et underspørsmål i WHERE-setningen:
VELG avdelingsnavnFRA avdelinger AS dHVOR IKKE FINNES (VELG avdeling IDFRA Students AS sHVOR d.DepartmentId = s.DepartmentId);
Spørringen:
VELG AvdelingIdFRA Students AS sHVOR d.DepartmentId = s.DepartmentId
Ovennevnte spørring kalles her en undersøking fordi den er nestet i WHERE-setningen. Delspørringen returnerer DepartmentId-verdiene som vil bli brukt av operatøren IKKE EXISTS.
Dette spørsmålet vil gi deg:
I spørringen ovenfor har vi valgt avdelingen som ikke har noen student påmeldt. Som er "Math" -avdelingen her borte.
Sett operasjoner - UNION, kryss
SQLite støtter følgende SET-operasjoner:
UNION & UNION ALL
Den kombinerer ett eller flere resultatsett (en gruppe med rader) returnert fra flere SELECT-setninger til ett resultatsett.
UNION vil returnere forskjellige verdier. UNION ALL vil imidlertid ikke inkludere duplikater.
Merk at kolonnenavnet vil være kolonnenavnet som er spesifisert i den første SELECT-setningen.
UNION Eksempel
I det følgende eksemplet vil vi få listen over DepartmentId fra studenttabellen og listen over DepartmentId fra avdelingstabellen i samme kolonne:
VELG DepartmentId AS DepartmentIdUnioned FROM StudentsUNIONVELG DepartmentId FRA avdelinger;
Dette vil gi deg:
Spørringen returnerer bare 5 rader som er de forskjellige avdelings-ID-verdiene. Legg merke til den første verdien som er nullverdien.
SQLite UNION ALL Eksempel
I det følgende eksemplet vil vi få listen over DepartmentId fra studenttabellen og listen over DepartmentId fra avdelingstabellen i samme kolonne:
VELG DepartmentId AS DepartmentIdUnioned FROM StudentsUNION ALLVELG DepartmentId FRA avdelinger;
Dette vil gi deg:
Søket returnerer 14 rader, 10 rader fra studenttabellen og 4 fra avdelingstabellen. Vær oppmerksom på at det er duplikater i verdiene som returneres. Vær også oppmerksom på at kolonnenavnet var det som ble angitt 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 INTERSECT
Returnerer verdiene som finnes i begge det kombinerte resultatsettet. Verdier som finnes i et av de kombinerte resultatsettene blir ignorert.
Eksempel
I det følgende spørsmålet velger vi DepartmentId-verdiene som finnes i både tabellene Students and Institutions i DepartmentId-kolonnen:
VELG avdeling fra studenterKrysseVELG DepartmentId FRA avdelinger;
Dette vil gi deg:
Spørringen returnerer bare tre verdier 1, 2 og 3. Hvilke verdier finnes i begge tabellene.
Verdiene null og 4 ble imidlertid ikke inkludert fordi nullverdien bare finnes i studenttabellen og ikke i avdelingstabellen. Og verdien 4 eksisterer i avdelingstabellen og ikke i studenttabellen.
Derfor ble begge verdiene NULL og 4 ignorert og ikke inkludert i de returnerte verdiene.
UNNTATT
Anta 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 "UNNTAT" -setningen. EXCEPT-paragrafen sammenligner de to listene og returnerer de radene som finnes i liste1 og ikke finnes i liste2.
Eksempel
I det følgende spørsmålet vil vi velge DepartmentId-verdiene som finnes i avdelingstabellen og ikke finnes i studenttabellen:
VELG DepartmentId FRA avdelingerUNNTATTVELG avdeling fra studenter;
Dette vil gi deg:
Spørringen returnerer bare verdien 4. Hvilken er den eneste verdien som finnes i avdelingstabellen, og som ikke finnes i studenttabellen.
NULL håndtering
" NULL " -verdien er en spesiell verdi i SQLite. Den brukes til å representere en verdi som er ukjent eller mangler verdi. Merk at nullverdien er helt annerledes enn " 0 " eller tom "" verdi. Fordi 0 og den tomme verdien er en kjent verdi, er nullverdien imidlertid ukjent.
NULL-verdier krever en spesiell håndtering i SQLite, vi ser nå hvordan vi skal håndtere NULL-verdiene.
Søk etter NULL-verdier
Du kan ikke bruke den normale likhetsoperatøren (=) til å søke i nullverdiene. Følgende søk søker for eksempel etter studenter som har en null DepartmentId-verdi:
VELG * FRA studenter HVOR DepartmentId = NULL;
Dette spørsmålet gir ikke noe resultat:
Fordi NULL-verdien ikke er lik noen annen verdi som inkluderte en nullverdi i seg selv, ga den ikke noe resultat.
- For å få spørringen til å fungere, må du imidlertid bruke "IS NULL" -operatøren for å søke etter nullverdier som følger:
VELG * FRA studenter der institutt er NULL;
Dette vil gi deg:
Søket vil returnere de studentene som har en null DepartmentId-verdi.
- Hvis du vil få de verdiene som ikke er null, må du bruke " IS NOT NULL " -operatøren slik:
VELG * FRA studenter der institutt ikke er null;
Dette vil gi deg:
Søket 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 betingelser. For det bør betingelsen for den aktuelle verdien være sant for å bli valgt.
CASE expression vil evaluere denne listen over betingelser for alle verdiene. Hvis tilstanden er oppfylt, 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.
- "Veldig bra" hvis karakteren er mellom 70 og 85.
- "Bra" hvis karakteren er mellom 60 og 70.
Deretter kan du bruke CASE-uttrykket til å gjøre det.
Dette kan brukes til å definere noen logikk i SELECT-setningen, slik at du kan velge bestemte resultater, avhengig av visse forhold som for eksempel setning.
CASE-operatøren kan defineres med forskjellige syntakser som følger:
- Du kan bruke forskjellige forhold:
SAKNÅR tilstand1 SÅ resultat1NÅR tilstand2 SÅ resultat2NÅR tilstand3 SÅ resultat3 ... ELSEN resultatSLUTT
- Eller du kan bare bruke ett uttrykk og sette forskjellige mulige verdier å velge mellom:
CASE-uttrykkNÅR verdi1 SÅ resultat1NÅR verdi2 SÅ resultat2NÅR verdi3 SÅ resultat3 ... ELSE restillnSLUTT
Merk at ELSE-klausulen er valgfri.
Eksempel
I det følgende eksemplet vil vi bruke CASE- uttrykket med NULL- verdi i avdeling Id-kolonnen i Students-tabellen for å vise teksten 'No Department' som følger:
Å VELGEStudent navn,SAKNÅR Avdelingen HAR NULL DÅ 'Ingen avdeling'ELSE DepartmentIdEND AS DepartmentIdFRA studenter;
- CASE-operatøren vil sjekke verdien til DepartmentId om den er null eller ikke.
- Hvis det er en NULL-verdi, vil den velge bokstavelig verdi 'Ingen avdeling' i stedet for avdelingId-verdien.
- Hvis ikke er en nullverdi, vil den velge verdien til avdelingen DepartmentId.
Dette vil gi deg resultatet som vist nedenfor:
Vanlig borduttrykk
Vanlige tabelluttrykk (CTE-er) er underspørsler som er definert i SQL-setningen med et gitt navn.
Det har en fordel i forhold til underspørringene fordi det er definert ut av SQL-setningene og vil gjøre spørringene lettere å lese, vedlikeholde og forstå.
Et vanlig tabelluttrykk kan defineres ved å sette WITH-setningen foran en SELECT-setning som følger:
MED CTEnavnSOM(VELG uttalelse)VELG, OPPDATER, INSERT eller oppdater uttalelse her FRA CTE
" CTEnavn " er hvilket som helst navn du kan gi til CTE, du kan bruke det til å referere til det senere. Merk at du kan definere SELECT-, UPDATE-, INSERT- eller DELETE-setning på CTE-er
La oss nå se et eksempel på hvordan du bruker CTE i SELECT-setningen.
Eksempel
I det følgende eksemplet vil vi definere en CTE fra en SELECT-setning, og deretter vil vi bruke den senere på et annet spørsmål:
MED AllDepartmentsSOM(VELG DepartmentId, DepartmentNameFRA avdelinger)Å VELGEs.StudentId,s.Studentnavn,a.avdelingsnavnFRA Students AS sINNER JOIN AllDepartments AS a ON s.DepartmentId = a.DepartmentId;
I dette spørsmålet definerte vi en CTE og ga den navnet " AllDepartments ". Denne CTE ble definert fra et SELECT-spørsmål:
VELG DepartmentId, DepartmentNameFRA avdelinger
Så etter at vi definerte CTE, brukte vi den i SELECT-spørringen som kommer etter den.
Merk at vanlige tabelluttrykk ikke påvirker utdataene fra 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 bruker den på nytt som et undersøk. Bare SELECT-setningen påvirker resultatet av spørringen.
Dette spørsmålet vil gi deg:
Avanserte spørsmål
Avanserte spørsmål er de spørsmålene som inneholder komplekse sammenføyninger, underspørsler og noen aggregater. I det følgende avsnittet vil vi se et eksempel på et avansert spørsmål:
Hvor vi får,
- Instituttets navn med alle studentene for hver avdeling
- Elevenes navn skilles med komma og
- Viser avdelingen som har minst tre studenter i seg
Å VELGEd.avdelingsnavn,COUNT (s.StudentId) StudentsCount,GROUP_CONCAT (StudentName) AS StudenterFRA avdelinger AS dINNER BLI MED Studentene som er PÅ s.DepartmentId = d.DepartmentIdGRUPPER AV avdelingsnavnHAR TELL (s.StudentId)> = 3;
Vi la til en JOIN-klausul for å hente avdelingsnavnet fra avdelingstabellen. Etter det la vi til en GROUP BY-ledd med to samlede funksjoner:
- "TELL" for å telle studentene for hver avdelingsgruppe.
- GROUP_CONCAT for å sammenkoble studenter for hver gruppe med komma atskilt i en streng.
- Etter GROUP BY brukte vi HAVING-klausulen til å filtrere avdelingene og bare velge de avdelingene som har minst 3 studenter.
Resultatet blir som følger:
Sammendrag:
Dette var en introduksjon til å skrive SQLite-spørsmål og grunnleggende spørsmål om databasen og hvordan du kan filtrere de returnerte dataene. Du kan nå, skrive dine egne SQLite-spørsmål.