För att skriva SQL-frågor i en SQLite-databas måste du veta hur klausulerna SELECT, FROM, WHERE, GROUP BY, ORDER BY och LIMIT fungerar och hur du använder dem.
Under denna handledning lär du dig hur du använder dessa klausuler och hur du skriver SQLite-klausuler.
I den här handledningen lär du dig-
- Läsa data med Select
- Namn och alias
- VAR
- Begränsning och beställning
- Ta bort dubbletter
- Sammanlagt
- Grupp av
- Fråga & undersökning
- Ställ in operationer -UNION, skär
- NULL-hantering
- Villkorliga resultat
- Vanligt tabelluttryck
- Avancerade frågor
Läsa data med Select
SELECT-klausulen är det huvudsakliga uttalandet du använder för att fråga en SQLite-databas. I SELECT-satsen anger du vad du ska välja. Men före select-satsen, låt oss se varifrån vi kan välja data med hjälp av FROM-satsen.
FROM-satsen används för att ange var du vill välja data. I från-klausulen kan du ange en eller flera tabeller eller underfrågor att välja data från, som vi kommer att se senare på handledningarna.
Observera att för alla följande exempel måste du köra sqlite3.exe och öppna en anslutning till exempeldatabasen som flödande:
Steg 1) I detta steg,
- Öppna Den här datorn och navigera till följande katalog " C: \ sqlite " och
- Öppna sedan " sqlite3.exe ":
Steg 2) Öppna databasen " TutorialsSampleDB.db " med följande kommando:
Nu är du redo att köra alla typer av frågor i databasen.
I SELECT-satsen kan du inte bara välja ett kolumnnamn utan du har många andra alternativ att ange vad du ska välja. Enligt följande:
VÄLJ *
Detta kommando markerar alla kolumner från alla refererade tabeller (eller underfrågor) i FROM-satsen. Till exempel:
VÄLJ *FRÅN studenterINNER JOIN Institutioner PÅ Students.DepartmentId = Institutioner.DepartmentId;
Detta markerar alla kolumner från både tabellstudenterna och avdelningstabellerna:
VÄLJ tabellnamn. *
Detta markerar alla kolumner från endast tabellen "tabellnamn". Till exempel:
VÄLJ studenter. *FRÅN studenterINNER JOIN Institutioner PÅ Students.DepartmentId = Institutioner.DepartmentId;
Detta markerar bara alla kolumner från studenttabellen:
Ett bokstavligt värde
Ett bokstavligt värde är ett konstant värde som kan specificeras i select-uttalandet. Du kan använda bokstavliga värden normalt på samma sätt som du använder kolumnnamn i SELECT-satsen. Dessa bokstavliga värden visas för varje rad från raderna som returneras av SQL-frågan.
Här är några exempel på olika bokstavliga värden som du kan välja:
- Numerisk bokstav - siffror i vilket format som helst, 1, 2,55, ... etc.
- Strängbokstavar - Alla strängar "USA", "detta är en exempeltext", ... etc.
- NULL - NULL-värde.
- Current_TIME - Det ger dig aktuell tid.
- CURRENT_DATE - detta ger dig det aktuella datumet.
Detta kan vara praktiskt i vissa situationer där du måste välja ett konstant värde för alla returnerade rader. Om du till exempel vill välja alla studenter från tabellen Students med en ny kolumn som heter ett land som innehåller värdet "USA" kan du göra detta:
VÄLJ *, "USA" SOM land FRÅN studenter;
Detta ger dig alla elevers kolumner, plus en ny kolumn "Land" så här:
Observera att denna nya kolumn Land inte är en ny kolumn som läggs till i tabellen. Det är en virtuell kolumn, skapad i frågan för att visa resultaten och den skapas inte i tabellen.
Namn och alias
Aliaset är ett nytt namn för kolumnen som låter dig välja kolumnen med ett nytt namn. Kolumnalias anges med nyckelordet "AS".
Om du till exempel vill välja kolumnen Studentnamn som ska returneras med "Studentnamn" istället för "Studentnamn" kan du ge det ett alias så här:
VÄLJ studentnamn SOM 'studentnamn' FRÅN studenter;
Detta ger dig studenternas namn med namnet "Studentnamn" istället för "Studentnamn" så här:
Observera att kolumnnamnet fortfarande är " Studentnamn "; kolumnen Studentnamn är fortfarande densamma, den ändras inte av aliaset.
Aliaset ändrar inte kolumnnamnet. det ändrar bara visningsnamnet i SELECT-satsen.
Observera också att sökordet "AS" är valfritt, du kan ange aliasnamnet utan det, ungefär så här:
VÄLJ studentnamn 'studentnamn' FRÅN studenter;
Och det ger dig exakt samma utdata som föregående fråga:
Du kan också ge tabeller alias, inte bara kolumner. Med samma nyckelord "AS". Du kan till exempel göra detta:
VÄLJ s. * FRÅN studenter AS s;
Detta ger dig alla kolumner i tabellen Studenter:
Detta kan vara mycket användbart om du går med i mer än en tabell; istället för att upprepa hela tabellnamnet i frågan kan du ge varje tabell ett kort aliasnamn. Till exempel i följande fråga:
VÄLJ Students.StudentName, Departments.DepartmentNameFRÅN studenterINNER JOIN Institutioner PÅ Students.DepartmentId = Institutioner.DepartmentId;
Denna fråga kommer att välja varje studentnamn från tabellen "Studenter" med dess avdelningsnamn från "Institutioner" -tabellen:
Samma fråga kan dock skrivas så här:
VÄLJ s.Studentnamn, d.avdelningsnamnFRÅN Students AS sINNER JOIN Avdelningar SOM PÅ s.DepartmentId = d.DepartmentId;
- Vi gav studenttabellen ett alias "s" och avdelningstabellen ett alias "d".
- I stället för att använda hela tabellens namn använde vi deras alias för att hänvisa till dem.
- INNER JOIN sammanfogar två eller flera tabeller tillsammans med ett villkor. I vårt exempel gick vi med i studenttabellen med avdelningstabellen med kolumnen DepartmentId. Det finns också en fördjupad förklaring för INNER JOIN i "SQLite Joins" -handledningen.
Detta ger dig den exakta produktionen som föregående fråga:
VAR
Att skriva SQL-frågor med SELECT-sats ensam med FROM-satsen som vi såg i föregående avsnitt ger dig alla raderna från tabellerna. Men om du vill filtrera de returnerade uppgifterna måste du lägga till en "WHERE" -sats.
WHERE-satsen används för att filtrera resultatuppsättningen som returneras av SQL-frågan. Så här fungerar WHERE-klausulen:
- I WHERE-paragrafen kan du ange ett "uttryck".
- Det uttrycket utvärderas för varje rad som returneras från tabellen / tabellerna som anges i FROM-satsen.
- Uttrycket kommer att utvärderas som ett booleskt uttryck, med ett resultat antingen true, false eller null.
- Därefter returneras endast rader för vilka uttrycket utvärderades med ett sant värde och de med falska eller nollresultat ignoreras och ingår inte i resultatuppsättningen.
- För att filtrera resultatuppsättningen med WHERE-satsen måste du använda uttryck och operatorer.
Lista över operatörer i SQLite och hur man använder dem
I följande avsnitt kommer vi att förklara hur du kan filtrera med expression och operatorer.
Uttryck är en eller flera bokstavliga värden eller kolumner kombinerade med varandra med en operatör.
Observera att du kan använda uttryck i både SELECT-satsen och WHERE-satsen.
I följande exempel kommer vi att försöka uttrycken och operatörerna i både select-satsen och WHERE-satsen. För att visa dig hur de presterar.
Det finns olika typer av uttryck och operatorer som du kan ange enligt följande:
SQLite sammanlänkningsoperatören "||"
Denna operatör används för att sammanfoga ett eller flera bokstavliga värden eller kolumner med varandra. Det kommer att ge en sträng resultat från alla sammanhängande bokstavliga värden eller kolumner. Till exempel:
VÄLJ 'Id med namn:' || StudentId || Studentnamn SOM StudentIdWithNameFRÅN studenter;
Detta kommer att sammanfogas till ett nytt alias " StudentIdWithName ":
- Det bokstavliga strängvärdet " Id med namn: "
- med värdet i kolumnen " StudentId " och
- med värdet från kolumnen " Studentnamn "
SQLite CAST-operatör:
CAST-operatören används för att konvertera ett värde från en datatyp till en annan datatyp.
Till exempel, om du har ett numeriskt värde lagrat som ett strängvärde som det här " '12 .5" "och du vill konvertera det till ett numeriskt värde kan du använda CAST-operatören för att göra det här" CAST ('12 .5 'AS REAL) ". Eller om du har ett decimalvärde som 12,5, och du bara behöver hämta heltalet, kan du kasta det till ett heltal som detta "CAST (12.5 AS INTEGER)".
Exempel
I följande kommando försöker vi konvertera olika värden till andra datatyper:
VÄLJ CAST ('12 .5 'AS REAL) ToReal, CAST (12.5 AS INTEGER) AS ToInteger;
Detta ger dig:
Resultatet är som följer:
- CAST ('12 .5 'AS REAL) - värdet '12 .5' är ett strängvärde, det kommer att konverteras till ett REAL-värde.
- CAST (12,5 SOM INTEGER) - värdet 12.5 är ett decimalvärde, det kommer att konverteras till ett heltal. Decimaldelen trunkeras och den blir 12.
SQLite Arithmetic Operators:
Ta två eller flera numeriska bokstavsvärden eller numeriska kolumner och returnera ett numeriskt värde. De aritmetiska operatörerna som stöds i SQLite är:
|
Exempel:
I följande exempel kommer vi att försöka de fem aritmetiska operatorerna med bokstavliga numeriska värden i samma
välj klausul:
VÄLJ 25 + 6, 25-6, 25 * 6, 25% 6, 25/6;
Detta ger dig:
Lägg märke till hur vi använde ett SELECT-uttalande utan en FROM-klausul här. Och detta är tillåtet i SQLite så länge vi väljer bokstavliga värden.
SQLite-jämförelseoperatörer
Jämför två operander med varandra och returnera en sann eller falsk enligt följande:
|
Observera att SQLite uttrycker det sanna värdet med 1 och det falska värdet med 0.
Exempel:
VÄLJ10 <6 AS '<', 10 <= 6 AS '<=',10> 6 AS '>', 10> = 6 AS '> =',10 = 6 AS '=', 10 == 6 AS '==',10! = 6 AS '! =', 10 <> 6 AS '<>';
Detta kommer att ge ungefär så här:
SQLite-mönster matchande operatörer
" LIKE " - används för mönstermatchning. Med " Gilla " kan du söka efter värden som matchar ett mönster som anges med ett jokertecken.
Operand till vänster kan vara antingen ett stränglitterärt värde eller en strängkolumn. Mönstret kan specificeras enligt följande:
- Innehåller mönster. Till exempel Studentnamn LIKE '% a%' - det här söker efter studenternas namn som innehåller bokstaven "a" i vilken position som helst i kolumnen Studentnamn.
- Börjar med mönstret. Till exempel " Studentnamn LIKE 'a%' " - sök efter studenternas namn som börjar med bokstaven "a".
- Slutar med mönstret. Till exempel " Studentnamn LIKE '% a' " - Sök efter studenternas namn som slutar med bokstaven "a".
- Matcha varje enskilt tecken i en sträng med hjälp av understrykningsbokstaven "_". Till exempel " Studentnamn LIKE 'J___' " - Sök efter studenternas namn som är fyra tecken långa. Den måste börja med bokstaven "J" och kan ha ytterligare tre tecken efter "J" -bokstaven.
Mönster matchande exempel:
- Skaffa studentnamn som börjar med bokstaven 'j':
VÄLJ studentnamn FRÅN studenter där studentnamn gillar 'j%';
Resultat:
- Få studenternas namn med "y" -bokstaven:
VÄLJ studentnamn FRÅN studenter där studentnamn gillar '% y';
Resultat:
- Få studenternas namn som innehåller bokstaven 'n':
VÄLJ studentnamn FRÅN studenter där studentnamn gillar '% n%';
Resultat:
"GLOB" - motsvarar LIKE-operatören, men GLOB är skiftlägeskänslig, till skillnad från LIKE-operatören. Följande två kommandon ger till exempel olika resultat:
VÄLJ 'Jack' GLOB 'j%';VÄLJ 'Jack' LIKE 'j%';
Detta ger dig:
- Det första uttalandet returnerar 0 (falskt) eftersom GLOB-operatören är skiftlägeskänslig, så 'j' är inte lika med 'J'. Det andra uttalandet returnerar dock 1 (sant) eftersom LIKE-operatören är skiftlägeskänslig, så 'j' är lika med 'J'.
Andra operatörer:
SQLite OCH
En logisk operatör som kombinerar ett eller flera uttryck. Det kommer att returnera sant, bara om alla uttryck ger ett "sant" värde. Det kommer dock att returnera falskt endast om alla uttryck ger ett "falskt" värde.
Exempel:
Följande fråga söker efter studenter som har StudentId> 5 och Studentnamn börjar med bokstaven N, de returnerade studenterna måste uppfylla de två villkoren:
VÄLJ *FRÅN studenterVAR (StudentId> 5) OCH (Studentnamn Gillar 'N%');
Som en utgång, i ovanstående skärmdump, kommer detta bara att ge dig "Nancy". Nancy är den enda studenten som uppfyller båda villkoren.
SQLite ELLER
En logisk operatör som kombinerar ett eller flera uttryck, så att om en av de kombinerade operatörerna ger sant, kommer den att återgå till sant. Men om alla uttryck ger falska kommer de att returnera falska.
Exempel:
Följande fråga söker efter studenter som har StudentId> 5 eller Studentnamn börjar med bokstaven N, de studenter som returneras måste uppfylla minst ett av villkoren:
VÄLJ *FRÅN studenterVAR (StudentId> 5) ELLER (Studentnamn som "N%");
Detta ger dig:
Som en utgång, i ovanstående skärmdump, kommer detta att ge dig namnet på en student med bokstaven "n" i sitt namn plus student-id som har värde> 5.
Som du kan se är resultatet annorlunda än frågan med AND-operatören.
SQLite MELLAN
BETWEEN används för att välja de värden som ligger inom ett intervall på två värden. Till exempel kommer " X MELLAN Y OCH Z " att returnera sant (1) om värdet X är mellan de två värdena Y och Z. Annars returnerar det falskt (0). " X MELLAN Y OCH Z " motsvarar " X> = Y OCH X <= Z ", X måste vara större än eller lika med Y och X är mindre än eller lika med Z.
Exempel:
I följande exempelfråga skriver vi en fråga för att få elever med Id-värde mellan 5 och 8:
VÄLJ *FRÅN studenterVAR Student hade MELLAN 5 OCH 8;
Detta ger bara eleverna med id 5, 6, 7 och 8:
SQLite IN
Tar en operand och en lista med operander. Det kommer att returnera true om det första operandvärdet är lika med ett av operandernas värde från listan. IN-operatören returnerar true (1) om listan med operander innehåller det första operandvärdet inom dess värden. Annars returnerar den falskt (0).
Så här: " col IN (x, y, z) ". Detta motsvarar " (col = x) eller (col = y) eller (col = z) ".
Exempel:
Följande fråga väljer bara elever med id 2, 4, 6, 8:
VÄLJ *FRÅN studenterVAR StudentId IN (2, 4, 6, 8);
Så här:
Den föregående frågan ger det exakta resultatet som följande fråga eftersom de är ekvivalenta:
VÄLJ *FRÅN studenterVAR (StudentId = 2) ELLER (StudentId = 4) ELLER (StudentId = 6) ELLER (StudentId = 8);
Båda frågorna ger den exakta produktionen. Skillnaden mellan de två frågorna är dock den första frågan vi använde "IN" -operatören. I den andra frågan använde vi flera "ELLER" -operatörer.
IN-operatören motsvarar att använda flera OR-operatörer. " WHERE StudentId IN (2, 4, 6, 8) " motsvarar " WHERE (StudentId = 2) ELLER (StudentId = 4) ELLER (StudentId = 6) ELLER (StudentId = 8); "
Så här:
SQLite INTE IN
"INTE IN" -operand är motsatsen till IN-operatören. Men med samma syntax; det krävs en operand och en lista med operander. Det kommer att vara sant om det första operandvärdet inte är lika med ett av operandernas värde från listan. dvs det kommer att returnera true (0) om listan med operander inte innehåller den första operanden. Så här: " col NOT IN (x, y, z) ". Detta motsvarar " (kol <> x) OCH (kol <> y) OCH (kol <> z) ".
Exempel:
Följande fråga väljer elever med id: n som inte är lika med en av dessa Id 2, 4, 6, 8:
VÄLJ *FRÅN studenterVAR Student INTE INTE (2, 4, 6, 8);
Så här
Den föregående frågan ger vi det exakta resultatet som följande fråga eftersom de är ekvivalenta:
VÄLJ *FRÅN studenterVAR (StudentId <> 2) OCH (StudentId <> 4) OCH (StudentId <> 6) OCH (StudentId <> 8);
Så här:
I ovanstående skärmdump,
Vi använde flera inte lika operatörer "<>" för att få en lista över studenter, som inte är lika med varken av följande Id: s 2, 4, 6 eller 8. Den här frågan returnerar alla andra studenter än dessa Id-listor.
SQLite EXISTS
EXISTS-operatörerna tar inga operander; det tar bara en SELECT-sats efter den. EXISTS-operatören kommer att returnera true (1) om det finns några rader som returneras från SELECT-satsen, och det kommer att returnera false (0) om det inte finns några rader som returneras från SELECT-satsen.
Exempel:
I följande exempel väljer vi institutionens namn om avdelnings-id finns i studenttabellen:
VÄLJ avdelningsnamnFRÅN avdelningar AS dWHERE EXISTS (VÄLJ DepartmentId FRA Students AS s WHERE d.DepartmentId = s.DepartmentId);
Detta ger dig:
Endast de tre avdelningarna " IT, fysik och konst " kommer att returneras. Och avdelningsnamnet " Matematik " kommer inte att returneras eftersom det inte finns någon student på den institutionen, så institutionens ID finns inte i studenttabellen. Därför ignorerade EXISTS-operatören avdelningen " Math ".
SQLite INTE
Omvandlar resultatet från föregående operatör som kommer efter det. Till exempel:
- INTE MELLAN - Det kommer att återvända om MELLAN returnerar falskt och tvärtom.
- NOT LIKE - Det kommer att returnera true om LIKE returnerar falskt och tvärtom.
- INTE GLOB - Det kommer att återvända om GLOB returnerar falskt och tvärtom.
- NOT EXISTS - Det kommer att returnera true om EXISTS returnerar falskt och vice versa.
Exempel:
I följande exempel använder vi NOT-operatören med EXISTS-operatören för att få de avdelningar som inte finns i tabellen Students, vilket är det omvända resultatet av EXISTS-operatören. Så sökningen kommer att göras via DepartmentId som inte finns i avdelningstabellen.
VÄLJ avdelningsnamnFRÅN avdelningar AS dVAR FÖR INTE (VÄLJ DepartmentIdFRÅN Students AS sVAR d.DepartmentId = s.DepartmentId);
Utgång :
Endast avdelningen " Matematik " returneras. Eftersom avdelningen " Matematik " är den enda avdelningen finns det inte i studenttabellen.
Begränsning och beställning
SQLite Order
SQLite Order är att sortera ditt resultat efter ett eller flera uttryck. För att beställa resultatuppsättningen måste du använda ORDER BY-klausulen enligt följande:
- Först måste du ange ordningen ORDER BY.
- ORDER BY-klausulen måste anges i slutet av frågan; endast LIMIT-klausulen kan anges efter den.
- Ange uttrycket att beställa data med, detta uttryck kan vara ett kolumnnamn eller ett uttryck.
- Efter uttrycket kan du ange en valfri sorteringsriktning. Antingen DESC, för att beställa data fallande eller ASC för att beställa data stigande. Om du inte angav någon av dem sorteras data stigande.
- Du kan ange fler uttryck med "," mellan varandra.
Exempel
I följande exempel kommer vi att välja alla elever ordnade efter deras namn men i fallande ordning, sedan efter avdelningsnamnet i stigande ordning:
VÄLJ s.Studentnamn, d.avdelningsnamnFRÅN Students AS sINNER JOIN Avdelningar AS d ON s.DepartmentId = d.DepartmentIdBESTÄLLNING AV d.avdelningsnamn ASC, s.Studentnamn DESC;
Detta ger dig:
- SQLite kommer först att beställa alla studenter efter deras avdelningsnamn i stigande ordning
- För varje avdelningsnamn visas sedan alla studenter under avdelningsnamnet i fallande ordning efter deras namn
SQLite-gräns:
Du kan begränsa antalet rader som returneras av din SQL-fråga genom att använda LIMIT-satsen. Till exempel ger LIMIT 10 dig bara 10 rader och ignorerar alla andra rader.
I LIMIT-satsen kan du välja ett specifikt antal rader med start från en viss position med OFFSET-satsen. Till exempel kommer " LIMIT 4 OFFSET 4 " att ignorera de första 4 raderna och returnera 4 rader med början från de femte raderna, så du får rader 5,6,7 och 8.
Observera att OFFSET-klausulen är valfri, du kan skriva den som " LIMIT 4, 4 " och den ger dig exakta resultat.
Exempel :
I följande exempel returnerar vi endast 3 studenter som börjar från student-id 5 med frågan:
VÄLJ * FRÅN studenter GRÄNS 4,3;
Detta ger dig bara tre elever som börjar från rad 5. Så det ger dig raderna med StudentId 5, 6 och 7:
Ta bort dubbletter
Om din SQL-fråga returnerar dubbla värden kan du använda nyckelordet " DISTINCT " för att ta bort dubbletterna och återgå till distinkta värden. Du kan ange mer än en kolumn efter DISTINCT-tangentens arbete.
Exempel:
Följande fråga returnerar dubbla "avdelningsnamnvärden": Här har vi dubbla värden med namnen IT, fysik och konst.
VÄLJ d. AvdelningsnamnFRÅN Students AS sINNER JOIN Avdelningar SOM PÅ s.DepartmentId = d.DepartmentId;
Detta ger dig dubbla värden för avdelningens namn:
Lägg märke till hur det finns dubbla värden för avdelningsnamnet. Nu använder vi nyckelordet DISTINCT med samma fråga för att ta bort dessa dubbletter och få bara unika värden. Så här:
VÄLJ DISTINCT d. AvdelningsnamnFRÅN Students AS sINNER JOIN Avdelningar SOM PÅ s.DepartmentId = d.DepartmentId;
Detta ger dig bara tre unika värden för avdelningens namnkolumn:
Sammanlagt
SQLite-aggregat är inbyggda funktioner definierade i SQLite som grupperar flera värden på flera rader i ett värde.
Här är aggregaten som stöds av SQLite:
SQLite AVG ()
Returnerade genomsnittet för alla x-värden.
Exempel:
I följande exempel får vi de genomsnittliga betyg som eleverna får från alla tentor:
VÄLJ AVG (Markera) FRÅN märken;
Detta ger dig värdet "18.375":
Dessa resultat kommer från summering av alla märkvärden dividerat med deras antal.
COUNT () - COUNT (X) eller COUNT (*)
Returnerar det totala antalet av antalet gånger x-värdet uppträdde. Och här är några alternativ du kan använda med COUNT:
- COUNT (x): Räknar endast x-värden, där x är ett kolumnnamn. Det ignorerar NULL-värden.
- COUNT (*): Räkna alla rader från alla kolumner.
- RÄKNING (DISTINCT x): Du kan ange ett DISTINCT-nyckelord före x som får räkningen av de olika värdena på x.
Exempel
I följande exempel får vi det totala antalet avdelningar med COUNT (DepartmentId), COUNT (*) och COUNT (DISTINCT DepartmentId) och hur de är olika:
VÄLJ COUNT (DepartmentId), COUNT (DISTINCT DepartmentId), COUNT (*) FRA studenter;
Detta ger dig:
Enligt följande:
- COUNT (DepartmentId) ger dig räkningen av alla avdelnings-id och det ignorerar nollvärdena.
- COUNT (DISTINCT DepartmentId) ger dig distinkta värden för DepartmentId, som bara är 3. Vilka är de tre olika värdena för avdelningsnamnet. Observera att det finns åtta värden för institutionens namn i studentnamnet. Men bara de tre olika värdena som är matematik, IT och fysik.
- COUNT (*) räknar antalet rader i studenttabellen som är 10 rader för 10 elever.
GROUP_CONCAT () - GROUP_CONCAT (X) eller GROUP_CONCAT (X, Y)
GROUP_CONCAT aggregerad funktion sammanfogar flera värden till ett värde med ett komma för att separera dem. Den har följande alternativ:
- GROUP_CONCAT (X): Detta sammanfogar hela värdet av x i en sträng, med kommatecken "," som används som separator mellan värdena. NULL-värden ignoreras.
- GROUP_CONCAT (X, Y): Detta sammanfogar värdena för x till en sträng, med värdet y används som en separator mellan varje värde istället för standardavgränsaren ','. NULL-värden ignoreras också.
- GROUP_CONCAT (DISTINCT X): Detta sammanfogar alla distinkta värden för x i en sträng, med kommatecken "," som används som separator mellan värdena. NULL-värden ignoreras.
GROUP_CONCAT (avdelningsnamn) Exempel
Följande fråga sammanfogar alla avdelningsnamnsvärden från studenterna och avdelningstabellen till en strängkomma. Så istället för att returnera en lista med värden, ett värde på varje rad. Det returnerar endast ett värde på en rad, med alla värden kommaseparerade:
VÄLJ GROUP_CONCAT (d. Avdelningsnamn)FRÅN Students AS sINNER JOIN Avdelningar SOM PÅ s.DepartmentId = d.DepartmentId;
Detta ger dig:
Detta ger dig listan över åtta avdelningars namnvärden sammankopplade i en strängkomma åtskild.
GROUP_CONCAT (DISTINCT DepartmentName) Exempel
Följande fråga sammanfogar de olika värdena för avdelningsnamnet från tabellen studenter och avdelningar i en strängkomma som är åtskild:
VÄLJ GROUP_CONCAT (DISTINCT d. Avdelningsnamn)FRÅN Students AS sINNER JOIN Avdelningar SOM PÅ s.DepartmentId = d.DepartmentId;
Detta ger dig:
Lägg märke till hur resultatet är annorlunda än föregående resultat; endast tre värden returnerade som är de olika avdelningernas namn och de dubbla värdena togs bort.
GROUP_CONCAT (avdelningsnamn, '&') Exempel
Följande fråga sammanfogar alla värden i avdelningens namnkolumn från tabellen studenter och avdelningar i en sträng, men med tecknet '&' istället för ett kommatecken som separator:
VÄLJ GROUP_CONCAT (d. Avdelningsnamn, '&')FRÅN Students AS sINNER JOIN Avdelningar SOM PÅ s.DepartmentId = d.DepartmentId;
Detta ger dig:
Lägg märke till hur tecknet "&" används istället för standardtecknet "," för att skilja mellan värdena.
SQLite MAX () & MIN ()
MAX (X) returnerar det högsta värdet från X-värdena. MAX returnerar ett NULL-värde om alla värden på x är null. Medan MIN (X) returnerar det minsta värdet från X-värdena. MIN returnerar ett NULL-värde om alla X-värden är noll.
Exempel
I följande fråga kommer vi att använda funktionerna MIN och MAX för att få högsta och lägsta märke från tabellen " Märken ":
VÄLJ MAX (Markera), MIN (Markera) FRÅN märken;
Detta ger dig:
SQLite SUM (x), totalt (x)
Båda returnerar summan av alla x-värden. Men de skiljer sig åt i följande:
- SUM returnerar null om alla värden är null, men Total returnerar 0.
- TOTAL returnerar alltid flytande värden. SUM returnerar ett heltal om alla x-värden är ett heltal. Men om värdena inte är ett heltal returnerar det ett flytande värde.
Exempel
I följande fråga använder vi SUM och total för att få summan av alla märken i tabellen " Märken ":
VÄLJ SUMMA (Markera), TOTALT (Markera) FRÅN märken;
Detta ger dig:
Som du kan se returnerar TOTAL alltid en flytande punkt. Men SUM returnerar ett heltal eftersom värdena i kolumnen "Markera" kan vara i heltal.
Skillnad mellan SUM och TOTAL exempel:
I följande fråga visar vi skillnaden mellan SUM och TOTAL när de får SUM för NULL-värden:
VÄLJ SUMMA (markera), TOTALT (markera) FRÅN märken VAR TestId = 4;
Detta ger dig:
Observera att det inte finns några märken för TestId = 4, så det finns nollvärden för det testet. SUM returnerar ett nullvärde som tomt, medan TOTAL returnerar 0.
Grupp av
GROUP BY-satsen används för att ange en eller flera kolumner som ska användas för att gruppera raderna i grupper. Raderna med samma värden kommer att samlas (ordnas) i grupper.
För alla andra kolumner som inte ingår i gruppen efter kolumner kan du använda en aggregerad funktion för den.
Exempel:
Följande fråga ger dig det totala antalet studenter närvarande i varje institution.
VÄLJ d.DepartmentName, COUNT (s.StudentId) AS StudentsCountFRÅN Students AS sINNER JOIN Avdelningar AS d ON s.DepartmentId = d.DepartmentIdGRUPP AV d. Avdelningsnamn;
Detta ger dig:
GROUPBY DepartmentName-klausulen grupperar alla studenter i grupper en för varje avdelningsnamn. För varje grupp "avdelning" räknar studenterna med den.
HAR klausul
Om du vill filtrera de grupper som returneras av GROUP BY-satsen kan du ange en "HAVING" -sats med uttryck efter GROUP BY. Uttrycket kommer att användas för att filtrera dessa grupper.
Exempel
I följande fråga väljer vi de avdelningar som bara har två studenter:
VÄLJ d.DepartmentName, COUNT (s.StudentId) AS StudentsCountFRÅN Students AS sINNER JOIN Avdelningar AS d ON s.DepartmentId = d.DepartmentIdGRUPP AV d. AvdelningsnamnHAR RÄKNANDE (s.StudentId) = 2;
Detta ger dig:
Klausulen HAVING COUNT (S.StudentId) = 2 filtrerar de returnerade grupperna och returnerar bara de grupper som innehåller exakt två elever på den. I vårt fall har konstavdelningen två studenter, så det visas i produktionen.
SQLite Query & Subquery
Inuti vilken fråga som helst kan du använda en annan fråga antingen i VÄLJ, INSÄTT, RADERA, UPPDATERA eller i en annan underfråga.
Denna kapslade fråga kallas en underfråga. Vi kommer nu att se några exempel på att använda underfrågor i SELECT-satsen. I modifieringsdata-självstudien kommer vi dock att se hur vi kan använda underfrågor med INSERT, DELETE och UPDATE-uttalandet.
Använda underfråga i exemplet från FROM
I följande fråga kommer vi att inkludera en underfråga inuti FROM-satsen:
VÄLJs.StudentName, t.MarkFRÅN Students AS sINRE KOPPLING(VÄLJ StudentId, MarkFRÅN TEST SOM tINNER JOIN Märken AS m ON t.TestId = m.TestId) ON s.StudentId = t.StudentId;
Frågan:
VÄLJ StudentId, MarkFRÅN TEST SOM tINNER JOIN Märken AS m ON t.TestId = m.TestId
Ovanstående fråga kallas en underfråga här eftersom den är kapslad i FROM-klausulen. Lägg märke till att vi gav det ett aliasnamn "t" så att vi kan hänvisa till kolumnerna som returnerats från den i frågan.
Denna fråga ger dig:
Så i vårt fall,
- s.StudentName väljs från huvudfrågan som ger studenternas namn och
- t.Mark är vald från underfrågan; som ger poäng som erhållits av var och en av dessa studenter
Använda underfråga i WHERE-klausulexemplet
I följande fråga kommer vi att inkludera en underfråga i WHERE-klausulen:
VÄLJ avdelningsnamnFRÅN avdelningar AS dVAR FÖR INTE (VÄLJ DepartmentIdFRÅN Students AS sVAR d.DepartmentId = s.DepartmentId);
Frågan:
VÄLJ avdelningFRÅN Students AS sVAR d.DepartmentId = s.DepartmentId
Ovanstående fråga kallas här för en underfråga eftersom den är kapslad i WHERE-klausulen. Underfrågan returnerar DepartmentId-värden som kommer att användas av operatören INTE FÖR.
Denna fråga ger dig:
I ovanstående fråga har vi valt den avdelning som inte har någon student inskriven. Vilket är "Math" -avdelningen här.
Ställ in operationer - UNION, skär
SQLite stöder följande SET-operationer:
UNION & UNION ALL
Den kombinerar en eller flera resultatuppsättningar (en grupp av rader) som returneras från flera SELECT-satser till en resultatuppsättning.
UNION returnerar distinkta värden. UNION ALL kommer dock inte att innehålla dubbletter.
Observera att kolumnnamnet kommer att vara det kolumnnamn som anges i den första SELECT-satsen.
UNION Exempel
I följande exempel får vi listan över DepartmentId från studenttabellen och listan över DepartmentId från institutionstabellen i samma kolumn:
VÄLJ DepartmentId AS DepartmentIdUnioned FROM StudentsUNIONVÄLJ avdelning från avdelningar;
Detta ger dig:
Frågan returnerar endast fem rader som är de olika avdelnings-id-värdena. Lägg märke till det första värdet som är nollvärdet.
SQLite UNION ALL Exempel
I följande exempel får vi listan över DepartmentId från studenttabellen och listan över DepartmentId från institutionstabellen i samma kolumn:
VÄLJ DepartmentId AS DepartmentIdUnioned FROM StudentsUNION ALLVÄLJ avdelning från avdelningar;
Detta ger dig:
Frågan returnerar 14 rader, 10 rader från studenttabellen och 4 från avdelningstabellen. Observera att det finns dubbletter i de returnerade värdena. Observera också att kolumnnamnet var det som anges i det första SELECT-uttalandet.
Låt oss nu se hur UNION alla kommer att ge olika resultat om vi ersätter UNION ALL med UNION:
SQLite INTERSECT
Returnerar värdena finns i båda de kombinerade resultatuppsättningarna. Värden som finns i en av de kombinerade resultatuppsättningarna ignoreras.
Exempel
I följande fråga väljer vi de DepartmentId-värden som finns i både tabellerna Students and Institutions i kolumnen DepartmentId:
VÄLJ avdelning från studenterKorsasVÄLJ avdelning från avdelningar;
Detta ger dig:
Frågan returnerar endast tre värden 1, 2 och 3. Vilka är de värden som finns i båda tabellerna.
Värdena null och 4 inkluderades emellertid inte eftersom nullvärdet endast finns i studenttabellen och inte i avdelningstabellen. Och värdet 4 finns i institutionstabellen och inte i studenttabellen.
Därför ignorerades både värdena NULL och 4 och ingick inte i de returnerade värdena.
BORTSETT FRÅN
Antag att om du har två listor med rader, list1 och list2, och du bara vill ha raderna från list1 som inte finns i list2, kan du använda "EXCEPT" -satsen. EXCEPT-klausulen jämför de två listorna och returnerar de rader som finns i list1 och inte finns i list2.
Exempel
I följande fråga väljer vi de DepartmentId-värden som finns i avdelningstabellen och inte finns i studenttabellen:
VÄLJ avdelning från avdelningarBORTSETT FRÅNVÄLJ Institutionen från studenter;
Detta ger dig:
Frågan returnerar endast värdet 4. Vilket är det enda värdet som finns i avdelningstabellen och inte finns i studenttabellen.
NULL-hantering
" NULL " -värdet är ett speciellt värde i SQLite. Det används för att representera ett värde som är okänt eller saknas värde. Observera att nollvärdet är helt annorlunda än " 0 " eller tomt "" värde. Eftersom 0 och det tomma värdet är ett känt värde är dock null-värdet okänt.
NULL-värden kräver en speciell hantering i SQLite, vi kommer nu att se hur man hanterar NULL-värdena.
Sök efter NULL-värden
Du kan inte använda den normala jämställdhetsoperatören (=) för att söka i nollvärdena. Följande fråga söker till exempel efter studenter som har ett null DepartmentId-värde:
VÄLJ * FRÅN studenter VAR DepartmentId = NULL;
Den här frågan ger inget resultat:
Eftersom NULL-värdet inte är lika med något annat värde som ingår ett nullvärde i sig, gav det därför inget resultat.
- För att få frågan att fungera måste du dock använda "IS NULL" -operatören för att söka efter nollvärden enligt följande:
VÄLJ * FRÅN studenter där institutionen är NULL;
Detta ger dig:
Frågan returnerar de studenter som har ett null DepartmentId-värde.
- Om du vill få de värden som inte är null måste du använda operatorn " ÄR INTE NULL " så här:
VÄLJ * FRÅN studenter där institutionen inte är NULL;
Detta ger dig:
Frågan returnerar de studenter som inte har ett NULL DepartmentId-värde.
Villkorliga resultat
Om du har en lista med värden och vill välja någon av dem baserat på vissa villkor. För det bör villkoret för det specifika värdet vara sant för att kunna väljas.
CASE expression kommer att utvärdera denna lista över villkor för alla värden. Om villkoret är sant returnerar det värdet.
Till exempel, om du har en kolumn "Betyg" och du vill välja ett textvärde baserat på betygsvärdet enligt följande:
- "Utmärkt" om betyget är högre än 85.
- "Mycket bra" om betyget är mellan 70 och 85.
- "Bra" om betyget är mellan 60 och 70.
Då kan du använda CASE-uttrycket för att göra det.
Detta kan användas för att definiera viss logik i SELECT-klausulen så att du kan välja vissa resultat beroende på vissa förhållanden som till exempel uttalande.
CASE-operatören kan definieras med olika syntaxer enligt följande:
- Du kan använda olika villkor:
FALLNÄR tillstånd1 DAN resultat1NÄR tillstånd2 DAN resultat2NÄR villkor3 DÄR resultat3 ... ELSER resultatSLUTET
- Eller så kan du bara använda ett uttryck och sätta olika möjliga värden att välja mellan:
CASE-uttryckNÄR värde1 DAN resultat1NÄR värde2 DAN resultat2NÄR värde3 DAN resulterar3 ... ELSE återställerSLUTET
Observera att ELSE-klausulen är valfri.
Exempel
I följande exempel kommer vi att använda CASE- uttrycket med NULL- värde i avdelningens Id-kolumn i tabellen Students för att visa texten 'No Department' enligt följande:
VÄLJElevs namn,FALLNÄR avdelningen var NULL DÄR 'Ingen avdelning'ELSE DepartmentIdEND AS DepartmentIdFRÅN studenter;
- CASE-operatören kommer att kontrollera värdet på DepartmentId om det är null eller inte.
- Om det är ett NULL-värde kommer det att välja det bokstavliga värdet 'Ingen avdelning' istället för avdelnings-ID-värdet.
- Om inte är ett nullvärde, kommer det att välja värdet i kolumnen DepartmentId.
Detta ger dig resultatet enligt nedan:
Vanligt tabelluttryck
Vanliga tabelluttryck (CTE) är underfrågor som definieras i SQL-satsen med ett givet namn.
Det har en fördel i förhållande till underfrågorna eftersom det definieras ur SQL-uttalanden och gör frågorna lättare att läsa, underhålla och förstå.
Ett vanligt tabelluttryck kan definieras genom att sätta WITH-satsen framför en SELECT-sats på följande sätt:
MED CTEnamnSOM(VÄLJ uttalande)VÄLJ, UPPDATERA, INSÄTTA eller uppdatera uttalande här FRÅN CTE
" CTEname " är vilket namn du kan ge för CTE, du kan använda det för att hänvisa till det senare. Observera att du kan definiera SELECT-, UPDATE-, INSERT- eller DELETE-sats på CTE
Låt oss nu se ett exempel på hur du använder CTE i SELECT-klausulen.
Exempel
I följande exempel definierar vi en CTE från ett SELECT-uttalande och sedan använder vi det senare i en annan fråga:
MED Alla avdelningarSOM(VÄLJ DepartmentId, DepartmentNameFRÅN avdelningar)VÄLJs.StudentId,s.Studentnamn,a.avdelningsnamnFRÅN Students AS sINNER JOIN AllDepartments AS a ON s.DepartmentId = a.DepartmentId;
I denna fråga definierade vi en CTE och gav den namnet " AllDepartments ". Denna CTE definierades från en SELECT-fråga:
VÄLJ DepartmentId, DepartmentNameFRÅN avdelningar
Sedan efter att vi definierat CTE använde vi den i SELECT-frågan som kommer efter den.
Observera att vanliga tabelluttryck inte påverkar frågan. Det är ett sätt att definiera en logisk vy eller underfråga för att återanvända dem i samma fråga. Vanliga tabelluttryck är som en variabel som du deklarerar och återanvänder den som en underfråga. Endast SELECT-uttalandet påverkar frågan.
Denna fråga ger dig:
Avancerade frågor
Avancerade frågor är de frågor som innehåller komplexa sammanfogningar, underfrågor och vissa aggregat. I följande avsnitt ser vi ett exempel på en avancerad fråga:
Där vi får,
- Institutionens namn med alla studenter för varje institution
- Eleverna namn separerade med komma och
- Visar avdelningen med minst tre studenter i sig
VÄLJd.avdelningsnamn,COUNT (s.StudentId) StudentsCount,GROUP_CONCAT (Studentnamn) AS StudenterFRÅN avdelningar AS dINNRE GÅ MED Studenter AS s PÅ s.DepartmentId = d.DepartmentIdGRUPP AV d. AvdelningsnamnHAR RÄKNANDE (s.StudentId)> = 3;
Vi lade till en JOIN-klausul för att få DepartmentName från Institutionstabellen. Efter det lade vi till en GROUP BY-sats med två aggregerade funktioner:
- "COUNT" för att räkna eleverna för varje avdelningsgrupp.
- GROUP_CONCAT för att sammanfoga eleverna för varje grupp med komma separerade i en sträng.
- Efter GROUP BY använde vi HAVING-klausulen för att filtrera avdelningarna och endast välja de avdelningar som har minst 3 studenter.
Resultatet blir följande:
Sammanfattning:
Detta var en introduktion till att skriva SQLite-frågor och grunderna för att fråga databasen och hur du kan filtrera de returnerade data. Nu kan du skriva dina egna SQLite-frågor.