For at skrive SQL-forespørgsler i en SQLite-database skal du vide, hvordan sætningerne SELECT, FROM, WHERE, GROUP BY, ORDER BY og LIMIT fungerer, og hvordan de bruges.
I løbet af denne tutorial lærer du, hvordan du bruger disse klausuler, og hvordan du skriver SQLite-klausuler.
I denne vejledning lærer du-
- Læsning af data med Select
- Navne og alias
- HVOR
- Begrænsning og bestilling
- Fjernelse af dubletter
- Samlet
- Gruppe BY
- Forespørgsel og forespørgsel
- Indstil operationer -UNION, skær
- NULL-håndtering
- Betingede resultater
- Fælles tabeludtryk
- Avancerede forespørgsler
Læsning af data med Select
SELECT-klausulen er den vigtigste sætning, du bruger til at forespørge om en SQLite-database. I SELECT-klausulen angiver du, hvad du skal vælge. Men inden selektionsklausulen skal vi se, hvorfra vi kan vælge data ved hjælp af FROM-klausulen.
FROM-klausulen bruges til at specificere, hvor vil du vælge data. I fra-klausulen kan du angive en eller flere tabeller eller underforespørgsler, du vil vælge data fra, som vi vil se senere på vejledningerne.
Bemærk, at for alle de følgende eksempler skal du køre sqlite3.exe og åbne en forbindelse til eksempeldatabasen som flydende:
Trin 1) I dette trin,
- Åbn Denne computer, og naviger til følgende bibliotek " C: \ sqlite " og
- Åbn derefter " sqlite3.exe ":
Trin 2) Åbn databasen " TutorialsSampleDB.db " med følgende kommando:
Nu er du klar til at køre alle typer forespørgsler i databasen.
I SELECT-klausulen kan du ikke kun vælge et kolonnenavn, men du har mange andre muligheder for at specificere, hvad du skal vælge. Som følger:
VÆLG *
Denne kommando vælger alle kolonnerne fra alle de refererede tabeller (eller underforespørgsler) i FROM-klausulen. For eksempel:
VÆLG *FRA studerendeINNER JOIN Afdelinger PÅ Students.DepartmentId = Departments.DepartmentId;
Dette vælger alle kolonnerne fra både tabellerne studerende og instituttabellerne:
VÆLG tabelnavn. *
Dette vælger kun alle kolonnerne fra tabellen "tablenavn". For eksempel:
VÆLG studerende. *FRA studerendeINNER JOIN Afdelinger PÅ Students.DepartmentId = Departments.DepartmentId;
Dette markerer kun alle kolonnerne fra studerende-tabellen:
En bogstavelig værdi
En bogstavelig værdi er en konstant værdi, der kan specificeres i selektsætningen. Du kan bruge bogstavelige værdier normalt på samme måde som du bruger kolonnenavne i SELECT-klausulen. Disse bogstavelige værdier vises for hver række fra de rækker, der returneres af SQL-forespørgslen.
Her er nogle eksempler på forskellige bogstavelige værdier, som du kan vælge:
- Numerisk bogstavelig - tal i ethvert format som 1, 2.55,… osv.
- String literals - Enhver streng 'USA', 'dette er en prøvetekst', ... osv.
- NULL - NULL-værdi.
- Current_TIME - Det giver dig det aktuelle tidspunkt.
- CURRENT_DATE - dette giver dig den aktuelle dato.
Dette kan være praktisk i nogle situationer, hvor du skal vælge en konstant værdi for alle de returnerede rækker. For eksempel, hvis du vil vælge alle eleverne fra tabellen Students med en ny kolonne kaldet et land, der indeholder værdien "USA", kan du gøre dette:
VÆLG *, 'USA' SOM land FRA studerende;
Dette giver dig alle elevernes kolonner plus en ny kolonne "Land" som denne:
Bemærk, at denne nye kolonne Land faktisk ikke er en ny kolonne tilføjet til tabellen. Det er en virtuel kolonne, der er oprettet i forespørgslen til visning af resultaterne, og den oprettes ikke på bordet.
Navne og alias
Aliaset er et nyt navn til kolonnen, der giver dig mulighed for at vælge kolonnen med et nyt navn. Kolonnealiaserne specificeres ved hjælp af nøgleordet "AS".
For eksempel, hvis du vil vælge kolonnen Studentnavn, der skal returneres med "Student Name" i stedet for "StudentName", kan du give det et alias som dette:
VÆLG Elevnavn SOM 'Elevnavn' FRA studerende;
Dette giver dig de studerendes navne med navnet "Student Name" i stedet for "StudentName" på denne måde:
Bemærk, at kolonnenavnet stadig er " StudentName "; kolonnen Elevnavn er stadig den samme, den ændres ikke ved aliaset.
Aliaset ændrer ikke kolonnens navn. det ændrer bare displaynavnet i SELECT-klausulen.
Bemærk også, at nøgleordet "AS" er valgfrit, du kan sætte aliasnavnet uden det, noget som dette:
VÆLG Elevnavn 'Elevnavn' FRA studerende;
Og det giver dig nøjagtigt samme output som den forrige forespørgsel:
Du kan også give tabeller aliaser, ikke kun kolonner. Med det samme nøgleord "AS". For eksempel kan du gøre dette:
VÆLG s. * FRA studerende AS s;
Dette giver dig alle kolonnerne i tabellen Studerende:
Dette kan være meget nyttigt, hvis du deltager i mere end en tabel; i stedet for at gentage det fulde tabelnavn i forespørgslen, kan du give hver tabel et kort aliasnavn. For eksempel i følgende forespørgsel:
VÆLG Students.StudentName, Departments.DepartmentNameFRA studerendeINNER JOIN Afdelinger PÅ Students.DepartmentId = Departments.DepartmentId;
Denne forespørgsel vælger hvert studerendes navn fra "Studerende" -tabellen med dets afdelingens navn fra tabellen "Afdelinger":
Den samme forespørgsel kan imidlertid skrives således:
VÆLG s.Studentnavn, d.afdelingsnavnFRA Students AS sINNER JOIN Departements AS d ON s.DepartmentId = d.DepartmentId;
- Vi gav elevtabellen et alias "s" og afdelingernes tabel et alias "d".
- Derefter brugte vi deres aliasser til at henvise til dem i stedet for at bruge hele tabelens navn.
- INNER JOIN forbinder to eller flere tabeller sammen ved hjælp af en betingelse. I vores eksempel sluttede vi os til studerende-tabellen med afdelingstabellen med kolonnen DepartmentId. Der er også en grundig forklaring på INNER JOIN i "SQLite Joins" -studiet.
Dette giver dig den nøjagtige output som den forrige forespørgsel:
HVOR
At skrive SQL-forespørgsler ved hjælp af SELECT-klausul alene med FROM-klausulen, som vi så i det foregående afsnit, giver dig alle rækkerne fra tabellerne. Men hvis du vil filtrere de returnerede data, skal du tilføje en "WHERE" -klausul.
WHERE-klausulen bruges til at filtrere resultatsættet, der returneres af SQL-forespørgslen. Sådan fungerer WHERE-klausulen:
- I WHERE-klausulen kan du angive et "udtryk".
- Dette udtryk evalueres for hver række, der returneres fra tabellen / tabellerne specificeret i FROM-klausulen.
- Udtrykket evalueres som et boolsk udtryk med et resultat enten sandt, falsk eller null.
- Derefter returneres kun rækker, for hvilke udtrykket blev evalueret med en sand værdi, og dem med falske eller nul-resultater ignoreres og inkluderes ikke i resultatsættet.
- For at filtrere resultatsættet ved hjælp af WHERE-klausulen skal du bruge udtryk og operatorer.
Liste over operatører i SQLite, og hvordan man bruger dem
I det følgende afsnit forklarer vi, hvordan du kan filtrere ved hjælp af udtryk og operatorer.
Udtryk er en eller flere bogstavelige værdier eller kolonner kombineret med hinanden med en operator.
Bemærk, at du kan bruge udtryk i både SELECT-klausulen og i WHERE-klausulen.
I de følgende eksempler vil vi prøve udtryk og operatorer i både select-klausulen og WHERE-klausulen. For at vise dig, hvordan de klarer sig.
Der er forskellige typer udtryk og operatorer, som du kan angive som følger:
SQLite sammenkædningsoperatoren "||"
Denne operator bruges til at sammenkæde en eller flere bogstavelige værdier eller kolonner med hinanden. Det vil producere en streng med resultater fra alle sammenkædede bogstavelige værdier eller kolonner. For eksempel:
VÆLG 'Id med navn:' || StudentId || StudentName AS StudentIdWithNameFRA studerende;
Dette sammenføjes til et nyt alias " StudentIdWithName ":
- Den bogstavelige strengværdi " Id med navn: "
- med værdien af kolonnen " StudentId " og
- med værdien fra kolonnen " Studentnavn "
SQLite CAST-operatør:
CAST-operatøren bruges til at konvertere en værdi fra en datatype til en anden datatype.
For eksempel, hvis du har en numerisk værdi gemt som en strengværdi som denne " '12 .5 ' ", og du vil konvertere den til en numerisk værdi, kan du bruge CAST-operatøren til at gøre dette som denne " CAST ('12 .5' AS REAL) ". Eller hvis du har en decimalværdi som 12,5, og du kun skal hente heltalets del, kan du kaste den til et heltal som denne "CAST (12.5 AS INTEGER)".
Eksempel
I den følgende kommando vil vi forsøge at konvertere forskellige værdier til andre datatyper:
VÆLG CAST ('12 .5 'AS REAL) ToReal, CAST (12.5 AS INTEGER) AS ToInteger;
Dette giver dig:
Resultatet er som følger:
- CAST ('12 .5 'AS REAL) - værdien '12 .5' er en strengværdi, den konverteres til en REAL værdi.
- CAST (12.5 AS INTEGER) - værdien 12.5 er en decimalværdi, den konverteres til at være et heltal. Den decimale del afkortes, og den bliver 12.
SQLite aritmetiske operatører:
Tag to eller flere numeriske bogstavværdier eller numeriske kolonner, og returner en numerisk værdi. De aritmetiske operatorer, der understøttes i SQLite, er:
|
Eksempel:
I det følgende eksempel vil vi prøve de fem aritmetiske operatorer med bogstavelige numeriske værdier i det samme
vælg klausul:
VÆLG 25 + 6, 25-6, 25 * 6, 25% 6, 25/6;
Dette giver dig:
Bemærk hvordan vi brugte en SELECT-sætning uden en FROM-klausul her. Og dette er tilladt i SQLite, så længe vi vælger bogstavelige værdier.
SQLite-sammenligningsoperatører
Sammenlign to operander med hinanden, og returner en sand eller falsk som følger:
|
Bemærk, at SQLite udtrykker den sande værdi med 1 og den falske værdi med 0.
Eksempel:
VÆLG10 <6 AS '<', 10 <= 6 AS '<=',10> 6 AS '>', 10> = 6 AS '> =',10 = 6 AS '=', 10 == 6 AS '==',10! = 6 AS '! =', 10 <> 6 AS '<>';
Dette vil give noget som dette:
SQLite-mønster matchende operatører
" LIKE " - bruges til mønstermatchning. Ved hjælp af " Like " kan du søge efter værdier, der matcher et mønster, der er angivet ved hjælp af et jokertegn.
Operanden til venstre kan enten være en streng bogstavelig værdi eller en strengkolonne. Mønsteret kan specificeres som følger:
- Indeholder mønster. For eksempel StudentName LIKE '% a%' - dette søger efter de studerendes navne, der indeholder bogstavet "a" i en hvilken som helst position i kolonnen StudentName.
- Starter med mønsteret. For eksempel " StudentName LIKE 'a%' " - søg efter de studerendes navne, der starter med bogstavet "a".
- Ender med mønsteret. For eksempel " StudentName LIKE '% a' " - Søg efter de studerendes navne, der slutter med bogstavet "a".
- Matcher ethvert enkelt tegn i en streng ved hjælp af understregningsbogstavet "_". For eksempel " StudentName LIKE 'J___' " - Søg efter studerendes navne, der er 4 tegn lange. Det skal starte med "J" bogstavet og kan have yderligere tre tegn efter "J" bogstavet.
Eksempler på mønstermatchning:
- Få studerende navne, der starter med 'j' bogstav:
VÆLG Elevnavn FRA studerende, hvor Elevnavn LIGER 'j%';
Resultat:
- Få studerendes navne ender med 'y' bogstav:
VÆLG Elevnavn FRA Studerende, HVOR Elevnavn ligner '% y';
Resultat:
- Få studerendes navne, der indeholder 'n' bogstav:
VÆLG elevnavn FRA studerende, HVOR studentnavn som '% n%';
Resultat:
"GLOB" - svarer til LIKE-operatøren, men GLOB er store og små bogstaver i modsætning til LIKE-operatøren. Følgende to kommandoer returnerer for eksempel forskellige resultater:
VÆLG 'Jack' GLOB 'j%';VÆLG 'Jack' Ligesom 'j%';
Dette giver dig:
- Den første sætning returnerer 0 (falsk), fordi GLOB-operatoren er store og små bogstaver, så 'j' er ikke lig med 'J'. Den anden sætning returnerer dog 1 (sand), fordi LIKE-operatøren er store og små bogstaver, så 'j' er lig med 'J'.
Andre operatører:
SQLite OG
En logisk operator, der kombinerer et eller flere udtryk. Det vil returnere sandt, kun hvis alle udtryk giver en "sand" værdi. Det returnerer dog kun falsk, hvis alle udtryk giver en "falsk" værdi.
Eksempel:
Den følgende forespørgsel søger efter studerende, der har StudentId> 5, og StudentName begynder med bogstavet N, de returnerede studerende skal opfylde de to betingelser:
VÆLG *FRA studerendeWHERE (StudentId> 5) AND (StudentName LIKE 'N%');
Som output i ovenstående skærmbillede vil dette kun give dig "Nancy". Nancy er den eneste studerende, der opfylder begge betingelser.
SQLite ELLER
En logisk operator, der kombinerer et eller flere udtryk, så hvis en af de kombinerede operatorer giver true, vil den returnere true. Men hvis alle udtrykkene giver falske, returnerer de falske.
Eksempel:
Den følgende forespørgsel søger efter studerende, der har StudentId> 5 eller StudentName begynder med bogstavet N, de returnerede studerende skal opfylde mindst en af betingelserne:
VÆLG *FRA studerendeWHERE (StudentId> 5) ELLER (StudentName LIKE 'N%');
Dette giver dig:
Som output i ovenstående skærmbillede vil dette give dig navnet på en studerende med bogstavet "n" i deres navn plus studerende-id'et med værdi> 5.
Som du kan se, er resultatet anderledes end forespørgslen med AND-operatoren.
SQLite MELLEM
MELLEM bruges til at vælge de værdier, der ligger inden for et interval på to værdier. For eksempel vil " X MELLEM Y OG Z " returnere true (1), hvis værdien X er mellem de to værdier Y og Z. Ellers returnerer den false (0). " X MELLEM Y OG Z " svarer til " X> = Y OG X <= Z ", X skal være større end eller lig med Y, og X er mindre end eller lig med Z.
Eksempel:
I følgende eksempelforespørgsel skriver vi en forespørgsel for at få studerende med Id-værdi mellem 5 og 8:
VÆLG *FRA studerendeHVOR StudentId MELLEM 5 OG 8;
Dette giver kun eleverne med id 5, 6, 7 og 8:
SQLite IN
Tager en operand og en liste over operander. Det vil returnere sandt, hvis den første operandværdi svarer til en af operandernes værdi fra listen. IN-operatøren returnerer sand (1), hvis listen over operander indeholder den første operandværdi inden for dens værdier. Ellers returnerer den falsk (0).
Som dette: " col IN (x, y, z) ". Dette svarer til " (col = x) eller (col = y) eller (col = z) ".
Eksempel:
Følgende forespørgsel vælger kun studerende med id 2, 4, 6, 8:
VÆLG *FRA studerendeHVOR StudentId IN (2, 4, 6, 8);
Sådan her:
Den forrige forespørgsel giver det nøjagtige resultat som følgende forespørgsel, fordi de er ækvivalente:
VÆLG *FRA studerendeHVOR (StudentId = 2) ELLER (StudentId = 4) ELLER (StudentId = 6) ELLER (StudentId = 8);
Begge forespørgsler giver den nøjagtige output. Forskellen mellem de to forespørgsler er imidlertid, den første forespørgsel, vi brugte "IN" -operatoren. I den anden forespørgsel brugte vi flere "ELLER" -operatorer.
IN-operatøren svarer til at bruge flere OR-operatører. " WHERE StudentId IN (2, 4, 6, 8) " svarer til " WHERE (StudentId = 2) ELLER (StudentId = 4) ELLER (StudentId = 6) ELLER (StudentId = 8); "
Sådan her:
SQLite IKKE IN
"NOT IN" -operand er det modsatte af IN-operatøren. Men med den samme syntaks; det tager en operand og en liste med operander. Det vil returnere sandt, hvis den første operandværdi ikke er lig med en af operandernes værdi fra listen. det vil returnere sandt (0), hvis listen over operander ikke indeholder den første operand. Som dette: " col NOT IN (x, y, z) ". Dette svarer til " (col <> x) AND (col <> y) AND (col <> z) ".
Eksempel:
Følgende forespørgsel vælger studerende med id'er, der ikke er lig med en af disse Id 2, 4, 6, 8:
VÆLG *FRA studerendeHVOR studerende IKKE var i (2, 4, 6, 8);
Sådan her
Den forrige forespørgsel giver vi det nøjagtige resultat som følgende forespørgsel, fordi de er ækvivalente:
VÆLG *FRA studerendeHVOR (StudentId <> 2) OG (StudentId <> 4) OG (StudentId <> 6) OG (StudentId <> 8);
Sådan her:
I ovenstående skærmbillede,
Vi brugte flere ikke lige operatører "<>" til at få en liste over studerende, der ikke er lig med hverken af de følgende Id'er 2, 4, 6 eller 8. Denne forespørgsel returnerer alle andre studerende bortset fra denne liste over Id'er.
SQLite EXISTS
EXISTS-operatørerne tager ikke nogen operander; det tager kun en SELECT-klausul efter den. EXISTS-operatøren returnerer true (1), hvis der er nogen rækker, der returneres fra SELECT-klausulen, og den returnerer false (0), hvis der slet ikke er nogen rækker, der returneres fra SELECT-klausulen.
Eksempel:
I det følgende eksempel vælger vi afdelingens navn, hvis afdelings-id findes i studerende-tabellen:
VÆLG AfdelingsnavnFRA afdelinger AS dWHERE EXISTS (SELECT DepartmentId FROM Students AS s WHERE d.DepartmentId = s.DepartmentId);
Dette giver dig:
Kun de tre afdelinger " IT, fysik og kunst " returneres. Og instituttets navn " Matematik " returneres ikke, fordi der ikke er nogen studerende i denne afdeling, så afdelingens Id findes ikke i tabellen med studerende. Derfor ignorerede EXISTS-operatøren " Math " -afdelingen.
SQLite IKKE
Vender resultatet af den foregående operatør, der følger efter det. For eksempel:
- IKKE MELLEM - Det vil returnere sandt, hvis MELLEM returnerer falsk og omvendt.
- NOT LIKE - Det vil returnere sandt, hvis LIKE returnerer falsk og omvendt.
- IKKE GLOB - Det returnerer sandt, hvis GLOB returnerer falsk og omvendt.
- IKKE EKSISTER - Det returnerer sandt, hvis EKSISTER returnerer falsk og omvendt.
Eksempel:
I det følgende eksempel bruger vi IKKE-operatoren med EXISTS-operatøren til at få de afdelingers navne, der ikke findes i tabellen Students, hvilket er det omvendte resultat af EXISTS-operatøren. Så søgningen foretages gennem DepartmentId, der ikke findes i afdelingstabellen.
VÆLG AfdelingsnavnFRA afdelinger AS dHVOR IKKE FINDER (VÆLG DepartmentIdFRA Students AS sHVOR d.DepartmentId = s.DepartmentId);
Output :
Kun afdelingen " Matematik " returneres. Fordi " Math " -afdelingen er den eneste afdeling, findes der ikke i tabellen med studerende.
Begrænsning og bestilling
SQLite-rækkefølge
SQLite Order er at sortere dit resultat efter et eller flere udtryk. For at bestille resultatsættet skal du bruge ORDER BY-klausulen som følger:
- Først skal du specificere ORDER BY-klausulen.
- ORDER BY-klausulen skal specificeres i slutningen af forespørgslen; kun LIMIT-klausulen kan specificeres efter den.
- Angiv det udtryk, som dataene skal bestilles med, dette udtryk kan være et kolonnenavn eller et udtryk.
- Efter udtrykket kan du angive en valgfri sorteringsretning. Enten DESC for at bestille dataene faldende eller ASC for at ordne dataene stigende. Hvis du ikke specificerede nogen af dem, sorteres dataene stigende.
- Du kan angive flere udtryk ved hjælp af "," mellem hinanden.
Eksempel
I det følgende eksempel vælger vi alle de studerende, der er ordnet efter deres navne, men i faldende rækkefølge og derefter efter afdelingens navn i stigende rækkefølge:
VÆLG s.Studentnavn, d.afdelingsnavnFRA Students AS sINDRE MEDLEM Afdelinger AS d ON s.DepartmentId = d.DepartmentIdORDRE AF d.afdelingsnavn ASC, s.Studentnavn DESC;
Dette giver dig:
- SQLite vil først ordne alle studerende efter deres instituttnavn i stigende rækkefølge
- Derefter vises for hvert instituttnavn alle studerende under dette instituttnavn i faldende rækkefølge efter deres navne
SQLite-grænse:
Du kan begrænse antallet af rækker, der returneres af din SQL-forespørgsel ved hjælp af LIMIT-klausulen. For eksempel giver LIMIT 10 dig kun 10 rækker og ignorerer alle de andre rækker.
I LIMIT-klausulen kan du vælge et bestemt antal rækker startende fra en bestemt position ved hjælp af OFFSET-klausulen. For eksempel ignorerer " LIMIT 4 OFFSET 4 " de første 4 rækker og returnerer 4 rækker startende fra de femte rækker, så du får rækker 5,6,7 og 8.
Bemærk, at OFFSET-klausulen er valgfri, du kan skrive den som " LIMIT 4, 4 ", og den giver dig de nøjagtige resultater.
Eksempel :
I det følgende eksempel returnerer vi kun 3 studerende startende fra studerende id 5 ved hjælp af forespørgslen:
VÆLG * FRA studerende GRÆNSE 4,3;
Dette giver dig kun tre studerende startende fra række 5. Så det giver dig rækkerne med StudentId 5, 6 og 7:
Fjernelse af dubletter
Hvis din SQL-forespørgsel returnerer duplikerede værdier, kan du bruge nøgleordet " DISTINCT " til at fjerne disse duplikater og returnere på forskellige værdier. Du kan angive mere end en kolonne efter DISTINCT-nøglens arbejde.
Eksempel:
Følgende forespørgsel returnerer duplikat "afdelingens navnværdier": Her har vi duplikatværdier med navne IT, fysik og kunst.
VÆLG d.afdelingsnavnFRA Students AS sINNER JOIN Departements AS d ON s.DepartmentId = d.DepartmentId;
Dette giver dig duplikatværdier for afdelingens navn:
Bemærk, hvordan der er duplikatværdier for afdelingens navn. Nu bruger vi DISTINCT-nøgleordet med den samme forespørgsel til at fjerne disse dubletter og kun få unikke værdier. Sådan her:
VÆLG DISTINCT d.afdelingsnavnFRA Students AS sINNER JOIN Departements AS d ON s.DepartmentId = d.DepartmentId;
Dette giver dig kun tre unikke værdier for afdelingens navnekolonne:
Samlet
SQLite Aggregates er indbyggede funktioner defineret i SQLite, der grupperer flere værdier i flere rækker i en værdi.
Her er aggregaterne understøttet af SQLite:
SQLite AVG ()
Returnerede gennemsnittet for alle x-værdier.
Eksempel:
I det følgende eksempel får vi de gennemsnitlige karakterer, som studerende får fra alle eksamener:
VÆLG AVG (Mark) FRA mærker;
Dette giver dig værdien "18.375":
Disse resultater kommer fra opsummeringen af alle mærkeværdier divideret med deres antal.
COUNT () - COUNT (X) eller COUNT (*)
Returnerer det samlede antal af antallet af gange x-værdien optrådte. Og her er nogle muligheder, du kan bruge med COUNT:
- COUNT (x): Tæller kun x-værdier, hvor x er et kolonnenavn. Det ignorerer NULL-værdier.
- COUNT (*): Tæl alle rækkerne fra alle kolonnerne.
- TÆLLING (DISTINCT x): Du kan angive et DISTINCT-nøgleord før x, som får optællingen af de forskellige værdier på x.
Eksempel
I det følgende eksempel får vi det samlede antal afdelinger med COUNT (DepartmentId), COUNT (*) og COUNT (DISTINCT DepartmentId) og hvordan de er forskellige:
VÆLG COUNT (DepartmentId), COUNT (DISTINCT DepartmentId), COUNT (*) FRA studerende;
Dette giver dig:
Som følger:
- COUNT (DepartmentId) giver dig optællingen af hele afdelings-id'et, og det ignorerer nulværdierne.
- COUNT (DISTINCT DepartmentId) giver dig forskellige værdier for DepartmentId, som kun er 3. Hvilke er de tre forskellige værdier for afdelingens navn. Bemærk, at der er 8 værdier for instituttets navn i studerendes navn. Men kun de forskellige tre værdier, som er matematik, it og fysik.
- COUNT (*) tæller antallet af rækker i eleverstabellen, som er 10 rækker for 10 elever.
GROUP_CONCAT () - GROUP_CONCAT (X) eller GROUP_CONCAT (X, Y)
GROUP_CONCAT aggregatfunktion sammenkobler flere værdier til en værdi med et komma for at adskille dem. Det har følgende muligheder:
- GROUP_CONCAT (X): Dette sammenkæder al værdien af x i en streng med kommaet "," brugt som en separator mellem værdierne. NULL-værdier ignoreres.
- GROUP_CONCAT (X, Y): Dette sammenkæder værdierne af x i en streng, hvor værdien af y bruges som en separator mellem hver værdi i stedet for standardseparatoren ','. NULL-værdier ignoreres også.
- GROUP_CONCAT (DISTINCT X): Dette sammenkæder alle de forskellige værdier af x i en streng med kommaet ",", der bruges som en separator mellem værdierne. NULL-værdier ignoreres.
GROUP_CONCAT (Afdelingsnavn) Eksempel
Den følgende forespørgsel sammenkæder alle instituttets navnværdier fra studerende og afdelingstabellen i en streng kommasepareret. Så i stedet for at returnere en liste med værdier, en værdi på hver række. Det returnerer kun en værdi på en række, hvor alle værdierne er adskilt med komma:
VÆLG GROUP_CONCAT (d.afdelingsnavn)FRA Students AS sINNER JOIN Departements AS d ON s.DepartmentId = d.DepartmentId;
Dette giver dig:
Dette giver dig listen over 8 afdelingers navne værdier sammenkædet i en streng kommasepareret.
GROUP_CONCAT (DISTINCT DepartmentName) Eksempel
Den følgende forespørgsel sammenkæder de forskellige værdier for afdelingens navn fra tabellen studerende og afdelinger i en streng kommasepareret:
VÆLG GROUP_CONCAT (DISTINCT d.afdelingsnavn)FRA Students AS sINNER JOIN Departements AS d ON s.DepartmentId = d.DepartmentId;
Dette giver dig:
Bemærk, hvordan resultatet er anderledes end det foregående resultat; kun tre værdier returneres, som er de forskellige afdelingers navne, og duplikatværdierne blev fjernet.
GROUP_CONCAT (Afdelingsnavn, '&') Eksempel
Den følgende forespørgsel sammenkæder alle værdierne i afdelingens navnekolonne fra tabellen studerende og afdelinger i en streng, men med tegnet '&' i stedet for et komma som en separator:
VÆLG GROUP_CONCAT (d.afdelingsnavn, '&')FRA Students AS sINNER JOIN Departements AS d ON s.DepartmentId = d.DepartmentId;
Dette giver dig:
Bemærk hvordan tegnet "&" bruges i stedet for standardtegnet "," for at adskille mellem værdierne.
SQLite MAX () & MIN ()
MAX (X) returnerer dig den højeste værdi fra X-værdierne. MAX returnerer en NULL-værdi, hvis alle værdierne for x er nul. Mens MIN (X) returnerer dig den mindste værdi fra X-værdierne. MIN returnerer en NULL-værdi, hvis alle X-værdier er nul.
Eksempel
I den følgende forespørgsel bruger vi MIN og MAX-funktionerne til at få det højeste og det laveste mærke fra tabellen " Marks ":
VÆLG MAX (Mark), MIN (Mark) FRA mærker;
Dette giver dig:
SQLite SUM (x), i alt (x)
Begge returnerer summen af alle x-værdier. Men de er forskellige i det følgende:
- SUM returnerer null, hvis alle værdier er null, men Total returnerer 0.
- TOTAL returnerer altid flydende punktværdier. SUM returnerer en heltalværdi, hvis alle x-værdierne er et heltal. Men hvis værdierne ikke er et heltal, returnerer det en flydende punktværdi.
Eksempel
I den følgende forespørgsel bruger vi SUM og total til at få summen af alle mærkerne i " Marks " -tabellerne:
VÆLG SUM (mærke), TOTAL (mærke) FRA mærker;
Dette giver dig:
Som du kan se, returnerer TOTAL altid et flydende punkt. Men SUM returnerer en heltalværdi, fordi værdierne i kolonnen "Marker" muligvis er i heltal.
Forskel mellem SUM og TOTAL eksempel:
I den følgende forespørgsel viser vi forskellen mellem SUM og TOTAL, når de får SUM af NULL-værdier:
SELECT SUM (Mark), TOTAL (Mark) FRA Marks WHERE TestId = 4;
Dette giver dig:
Bemærk, at der ikke er nogen markeringer for TestId = 4, så der er nullværdier for den test. SUM returnerer en nulværdi som en blank, mens TOTAL returnerer 0.
Gruppe BY
GROUP BY-klausulen bruges til at angive en eller flere kolonner, der skal bruges til at gruppere rækkerne i grupper. Rækkerne med de samme værdier samles (arrangeres) sammen i grupper.
For enhver anden kolonne, der ikke er inkluderet i gruppen efter kolonner, kan du bruge en samlet funktion til den.
Eksempel:
Følgende forespørgsel giver dig det samlede antal studerende til stede i hver afdeling.
VÆLG d.DepartmentName, COUNT (s.StudentId) AS StudentsCountFRA Students AS sINDRE MEDLEM Afdelinger AS d ON s.DepartmentId = d.DepartmentIdGRUPPE AF d. Afdelingsnavn;
Dette giver dig:
GROUPBY DepartmentName-klausulen grupperer alle studerende i grupper en for hvert instituttnavn. For hver gruppe "afdeling" tæller den studerende på den.
HAR klausul
Hvis du vil filtrere de grupper, der returneres af GROUP BY-klausulen, kan du angive en "HAVING" -klausul med udtryk efter GROUP BY. Udtrykket bruges til at filtrere disse grupper.
Eksempel
I den følgende forespørgsel vælger vi de afdelinger, der kun har to studerende på det:
VÆLG d.DepartmentName, COUNT (s.StudentId) AS StudentsCountFRA Students AS sINDRE MEDLEM Afdelinger AS d ON s.DepartmentId = d.DepartmentIdGRUPPE AF d. AfdelingsnavnHAR TÆLLING (s.StudentId) = 2;
Dette giver dig:
Klausulen HAVER TÆLL (S.StudentId) = 2 filtrerer de returnerede grupper og returnerer kun de grupper, der indeholder nøjagtigt to elever på den. I vores tilfælde har afdelingen for kunst to studerende, så den vises i output.
SQLite-forespørgsel og underforespørgsel
Inde i enhver forespørgsel kan du bruge en anden forespørgsel enten i en VÆLG, INDSÆT, SLET, OPDATER eller inde i en anden underforespørgsel.
Denne indlejrede forespørgsel kaldes en underforespørgsel. Vi vil nu se nogle eksempler på brug af underforespørgsler i SELECT-klausulen. I vejledningen til ændring af data vil vi dog se, hvordan vi kan bruge underforespørgsler med INSERT, DELETE og UPDATE-sætningen.
Brug af underforespørgsel i FROM-klausuleksemplet
I den følgende forespørgsel inkluderer vi en underforespørgsel inde i FROM-klausulen:
VÆLGs.Studentnavn, t.MarkFRA Students AS sINDRE MEDLEM(VÆLG StudentId, MarkFRA test AS tINNER JOIN Mærker AS m ON t.TestId = m.TestId) ON s.StudentId = t.StudentId;
Forespørgslen:
VÆLG StudentId, MarkFRA test AS tINNER JOIN Mærker AS m ON t.TestId = m.TestId
Ovenstående forespørgsel kaldes en underforespørgsel her, fordi den er indlejret i FROM-klausulen. Bemærk, at vi gav det et aliasnavn "t", så vi kan henvise til de kolonner, der er returneret fra det i forespørgslen.
Denne forespørgsel giver dig:
Så i vores tilfælde
- s.Studentnavn er valgt fra hovedforespørgslen, der giver navnet på studerende og
- t.Mark er valgt fra underforespørgslen; der giver karakter opnået af hver af disse studerende
Brug af underforespørgsel i WHERE-klausuleksemplet
I den følgende forespørgsel inkluderer vi en underforespørgsel i WHERE-klausulen:
VÆLG AfdelingsnavnFRA afdelinger AS dHVOR IKKE FINDER (VÆLG DepartmentIdFRA Students AS sHVOR d.DepartmentId = s.DepartmentId);
Forespørgslen:
VÆLG DepartmentIdFRA Students AS sHVOR d.DepartmentId = s.DepartmentId
Ovenstående forespørgsel kaldes her en underforespørgsel, fordi den er indlejret i WHERE-klausulen. Underforespørgslen returnerer de DepartmentId-værdier, der vil blive brugt af operatøren, IKKE FINDER.
Denne forespørgsel giver dig:
I ovenstående forespørgsel har vi valgt den afdeling, hvor ingen studerende er tilmeldt sig. Hvilket er "Math" -afdelingen herovre.
Sæt operationer - UNION, kryds
SQLite understøtter følgende SET-operationer:
UNION & UNION ALLE
Den kombinerer et eller flere resultatsæt (en gruppe af rækker), der returneres fra flere SELECT-sætninger, til et resultatsæt.
UNION returnerer forskellige værdier. UNION ALL vil dog ikke og inkluderer duplikater.
Bemærk, at kolonnenavnet er det kolonnenavn, der er angivet i den første SELECT-sætning.
UNION Eksempel
I det følgende eksempel får vi listen over DepartmentId fra studenttabellen og listen over DepartmentId fra afdelingstabellen i samme kolonne:
VÆLG DepartmentId AS DepartmentIdUnioned FROM StudentsUNIONVÆLG DepartmentId FRA afdelinger;
Dette giver dig:
Forespørgslen returnerer kun 5 rækker, som er de forskellige afdeling-id-værdier. Bemærk den første værdi, som er nulværdien.
SQLite UNION ALLE Eksempel
I det følgende eksempel får vi listen over DepartmentId fra studenttabellen og listen over DepartmentId fra afdelingstabellen i samme kolonne:
VÆLG DepartmentId AS DepartmentIdUnioned FROM StudentsUNION ALLEVÆLG DepartmentId FRA afdelinger;
Dette giver dig:
Forespørgslen returnerer 14 rækker, 10 rækker fra studerende-tabellen og 4 fra afdelingstabellen. Bemærk, at der er duplikater i de returnerede værdier. Bemærk også, at kolonnenavnet var det, der blev angivet i den første SELECT-sætning.
Lad os nu se, hvordan UNION alle vil give forskellige resultater, hvis vi erstatter UNION ALL med UNION:
SQLite INTERSECT
Returnerer værdierne findes i begge de kombinerede resultatsæt. Værdier, der findes i et af de kombinerede resultatsæt, ignoreres.
Eksempel
I den følgende forespørgsel vælger vi de DepartmentId-værdier, der findes i både tabellerne Students og Departements i kolonnen DepartmentId:
VÆLG DepartmentId FRA studerendeKrydseVÆLG DepartmentId FRA afdelinger;
Dette giver dig:
Forespørgslen returnerer kun tre værdier 1, 2 og 3. Hvilke værdier findes i begge tabeller.
Værdierne null og 4 blev dog ikke medtaget, fordi nulværdien kun findes i studenttabellen og ikke i afdelingstabellen. Og værdien 4 findes i afdelingstabellen og ikke i tabellen studerende.
Derfor blev begge værdier NULL og 4 ignoreret og ikke inkluderet i de returnerede værdier.
UNDTAGEN
Antag, at hvis du har to lister med rækker, liste1 og liste2, og du kun vil have rækkerne fra liste1, der ikke findes i liste2, kan du bruge "undtagen" klausul. EXCEPT-klausulen sammenligner de to lister og returnerer de rækker, der findes i liste1 og ikke findes i liste2.
Eksempel
I den følgende forespørgsel vælger vi de DepartmentId-værdier, der findes i afdelingstabellen og ikke findes i studenttabellen:
VÆLG DepartmentId FRA afdelingerUNDTAGENVÆLG DepartmentId FRA studerende;
Dette giver dig:
Forespørgslen returnerer kun værdien 4. Hvilken er den eneste værdi, der findes i afdelingstabellen, og ikke findes i tabellen studerende.
NULL-håndtering
" NULL " -værdien er en speciel værdi i SQLite. Det bruges til at repræsentere en værdi, der er ukendt eller mangler værdi. Bemærk, at nulværdien er helt forskellig fra " 0 " eller tom "" værdi. Da 0 og den tomme værdi er en kendt værdi, er nulværdien imidlertid ukendt.
NULL-værdier kræver en speciel håndtering i SQLite, vi ser nu, hvordan man håndterer NULL-værdierne.
Søg efter NULL-værdier
Du kan ikke bruge den normale lighedsoperator (=) til at søge i nulværdierne. For eksempel søger følgende forespørgsel efter de studerende, der har en null DepartmentId-værdi:
VÆLG * FRA studerende, HVOR DepartmentId = NULL;
Denne forespørgsel giver ikke noget resultat:
Fordi NULL-værdien ikke svarer til nogen anden værdi inkluderet en nulværdi i sig selv, returnerede den ikke noget resultat.
- For at få forespørgslen til at fungere skal du dog bruge operatoren "IS NULL" til at søge efter nulværdier som følger:
VÆLG * FRA studerende, HVOR instituttet er NULL;
Dette giver dig:
Forespørgslen returnerer de studerende, der har en null DepartmentId-værdi.
- Hvis du vil hente de værdier, der ikke er nul, skal du bruge operatoren " ER IKKE NULL " som denne:
VÆLG * FRA studerende, HVOR instituttet ikke er NULL;
Dette giver dig:
Forespørgslen returnerer de studerende, der ikke har en NULL DepartmentId-værdi.
Betingede resultater
Hvis du har en liste over værdier, og du vil vælge en af dem baseret på nogle betingelser. Til det skal betingelsen for den pågældende værdi være sand for at blive valgt.
CASE-udtryk vil evaluere denne liste over betingelser for alle værdier. Hvis betingelsen er sand, returnerer den denne værdi.
For eksempel, hvis du har en kolonne "Karakter", og du vil vælge en tekstværdi baseret på karakterværdien som følger:
- "Fremragende", hvis karakteren er højere end 85.
- "Meget god", hvis karakteren er mellem 70 og 85.
- "Godt", hvis karakteren er mellem 60 og 70.
Derefter kan du bruge CASE-udtrykket til at gøre det.
Dette kan bruges til at definere en vis logik i SELECT-klausulen, så du kan vælge bestemte resultater afhængigt af visse betingelser som f.eks. If-sætning.
CASE-operatøren kan defineres med forskellige syntakser som følger:
- Du kan bruge forskellige betingelser:
SAGNÅR tilstand1 DAN resultat1NÅR tilstand2 SÅ resultat2NÅR betingelse3 DANN resultat3… ELSE resultatnENDE
- Eller du kan kun bruge et udtryk og sætte forskellige mulige værdier at vælge imellem:
CASE-udtrykNÅR værdi1 DAN resultat1NÅR værdi2 SÅ resultat2NÅR værdi3 DANNES resultat3… ELSE restillENDE
Bemærk, at ELSE-klausulen er valgfri.
Eksempel
I det følgende eksempel bruger vi CASE- udtrykket med NULL- værdi i kolonnen afdelings-id i tabellen Students til at vise teksten 'Ingen afdeling' som følger:
VÆLGElevnavn,SAGNÅR afdelingen var nul, så 'ingen afdeling'ELSE-afdelingIdEND AS DepartmentIdFRA studerende;
- CASE-operatøren kontrollerer værdien af DepartmentId, om den er nul eller ej.
- Hvis det er en NULL-værdi, vælges den bogstavelige værdi 'Ingen afdeling' i stedet for DepartmentId-værdien.
- Hvis ikke er en nulværdi, vælger den værdien i kolonnen DepartmentId.
Dette giver dig output som vist nedenfor:
Fælles tabeludtryk
Almindelige tabeludtryk (CTE'er) er underforespørgsler, der er defineret inde i SQL-sætningen med et givet navn.
Det har en fordel i forhold til underforespørgslerne, fordi det er defineret ud af SQL-sætningerne og gør forespørgslerne lettere at læse, vedligeholde og forstå.
Et fælles tabeludtryk kan defineres ved at placere WITH-klausulen foran en SELECT-sætning som følger:
MED CTEnavnSOM(VÆLG sætning)VÆLG, OPDATER, INDSÆT eller opdater udsagn her FRA CTE
" CTEnavn " er ethvert navn, du kan give til CTE, du kan bruge det til at henvise til det senere. Bemærk, at du kan definere SELECT-, UPDATE-, INSERT- eller SLET-sætning på CTE'er
Lad os nu se et eksempel på, hvordan du bruger CTE i SELECT-klausulen.
Eksempel
I det følgende eksempel definerer vi en CTE fra en SELECT-sætning, og derefter bruger vi den senere på en anden forespørgsel:
MED alle afdelingerSOM(VÆLG DepartmentId, DepartmentNameFRA afdelinger)VÆLGs.StudentId,s.Studentnavn,a.afdelingsnavnFRA Students AS sINNER JOIN AllDepartments AS a ON s.DepartmentId = a.DepartmentId;
I denne forespørgsel definerede vi en CTE og gav den navnet " AllDepartments ". Denne CTE blev defineret ud fra en SELECT-forespørgsel:
VÆLG DepartmentId, DepartmentNameFRA afdelinger
Derefter brugte vi det i SELECT-forespørgslen, der fulgte efter det, efter at vi definerede CTE.
Bemærk, at almindelige tabeludtryk ikke påvirker forespørgslens output. Det er en måde at definere en logisk visning eller underforespørgsel for at genbruge dem i samme forespørgsel. Almindelige tabeludtryk er som en variabel, som du erklærer, og genbruger den som en underforespørgsel. Kun SELECT-sætningen påvirker forespørgslens output.
Denne forespørgsel giver dig:
Avancerede forespørgsler
Avancerede forespørgsler er de forespørgsler, der indeholder komplekse sammenføjninger, underforespørgsler og nogle aggregater. I det følgende afsnit vil vi se et eksempel på en avanceret forespørgsel:
Hvor vi får den,
- Instituttets navne med alle studerende for hver afdeling
- Elevernes navn adskilt med komma og
- Viser afdelingen med mindst tre studerende i den
VÆLGd.afdelingsnavn,COUNT (s.StudentId) StudentsCount,GROUP_CONCAT (StudentName) AS StuderendeFRA afdelinger AS dINDRE MEDLEM Studerende AS s ON s.DepartmentId = d.DepartmentIdGRUPP AF D. AfdelingnavnHAR TÆLLER (s.StudentId)> = 3;
Vi tilføjede en JOIN-klausul for at hente DepartmentName fra afdelingstabellen. Derefter tilføjede vi en GROUP BY-klausul med to samlede funktioner:
- "TÆLL" for at tælle eleverne for hver afdeling.
- GROUP_CONCAT til sammenkædning af studerende for hver gruppe med komma adskilt i en streng.
- Efter GROUP BY brugte vi HAVING-klausulen til at filtrere afdelingerne og kun vælge de afdelinger, der har mindst 3 studerende.
Resultatet bliver som følger:
Resumé:
Dette var en introduktion til at skrive SQLite-forespørgsler og det grundlæggende i forespørgsel til databasen, og hvordan du kan filtrere de returnerede data. Du kan nu skrive dine egne SQLite-forespørgsler.