SQLite Fråga: Välj, Var, LIMIT, OFFSET, Count, Group By
Marcus Allen
Att skriva SQL-frågor i en SQLite databas måste du veta hur SELECT-, FROM-, WHERE-, GROUP BY-, ORDER BY- och LIMIT-satserna fungerar och hur man använder dem.
Under denna handledning kommer du att lära dig hur du använder dessa klausuler och hur du skriver SQLite klausuler.
Läsa data med Select
SELECT-satsen är huvudsatsen du använder för att fråga en SQLite databas. I SELECT-satsen anger du vad du ska välja. Men innan 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 from-satsen kan du ange en eller flera tabeller eller underfrågor att välja data från, som vi kommer att se senare i handledningarna.
Observera att för alla följande exempel måste du köra sqlite3.exe och öppna en anslutning till exempeldatabasen som flytande:
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 vilken typ av fråga som helst på databasen.
I SELECT-satsen kan du välja inte bara ett kolumnnamn utan du har många andra alternativ för att specificera vad du ska välja. Som följande:
VÄLJ *
Detta kommando kommer att välja alla kolumner från alla refererade tabeller (eller underfrågor) i FROM-satsen. Till exempel:
SELECT * FROM Students INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Detta kommer att välja alla kolumner från både tabellerna studenter och avdelningstabellerna:
VÄLJ tabellnamn.*
Detta kommer att välja alla kolumner från endast tabellen "tabellnamn". Till exempel:
SELECT Students.* FROM Students INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Detta kommer endast att välja alla kolumner från elevtabellen:
Ett bokstavligt värde
Ett bokstavligt värde är ett konstant värde som kan anges i select-satsen. Du kan använda bokstavliga värden normalt på samma sätt som du använder kolumnnamn i SELECT-satsen. Dessa bokstavliga värden kommer att 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 – tal i valfritt format som 1, 2.55, … etc.
- Strängliterals – Vilken sträng som helst '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 elever från tabellen Studenter, med en ny kolumn som kallas ett land som innehåller värdet "USA", kan du göra så här:
SELECT *, 'USA' AS Country FROM Students;
Detta ger dig alla elevers kolumner, plus en ny kolumn "Land" så här:
Observera att denna nya kolumn Land faktiskt inte är en ny kolumn som lagts till i tabellen. Det är en virtuell kolumn som skapas i frågan för att visa resultaten och den kommer inte att skapas i tabellen.
Namn och Alias
Aliaset är ett nytt namn för kolumnen som låter dig välja kolumnen med ett nytt namn. Kolumnaliasen 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 den ett alias så här:
SELECT StudentName AS 'Student Name' FROM Students;
Detta kommer att ge dig elevernas namn med namnet "Student Name" istället för "StudentName" så här:
Observera att kolumnnamnet fortfarande "Elevs namn"; kolumnen StudentName är fortfarande densamma, den ändras inte av aliaset.
Aliaset kommer inte att ändra kolumnnamnet; det kommer bara att ändra visningsnamnet i SELECT-satsen.
Observera också att nyckelordet "AS" är valfritt, du kan sätta aliasnamnet utan det, ungefär så här:
SELECT StudentName 'Student Name' FROM Students;
Och det ger dig exakt samma utdata som den föregående frågan:
Du kan också ge tabeller alias, inte bara kolumner. Med samma sökord "AS". Du kan till exempel göra detta:
SELECT s.* FROM Students AS s;
Detta ger dig alla kolumner i tabellen Studenter:
Detta kan vara mycket användbart om du går med i mer än ett bord; 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:
SELECT Students.StudentName, Departments.DepartmentName FROM Students INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Denna fråga kommer att välja varje studentnamn från tabellen "Studenter" med dess institutionsnamn från tabellen "Avdelningar":
Men samma fråga kan skrivas så här:
SELECT s.StudentName, d.DepartmentName FROM Students AS s INNER JOIN Departments AS d ON 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 referera till dem.
- INNER JOIN sammanfogar två eller flera tabeller med ett villkor. I vårt exempel sammanfogade vi Studenter-tabellen med Departments-tabellen med DepartmentId-kolumnen. Det finns också en djupgående förklaring till INNER JOIN i "SQLite Går med” handledning.
Detta ger dig exakt utdata som den föregående frågan:
VAR
Att skriva SQL-frågor med enbart SELECT-satsen med FROM-satsen som vi såg i föregående avsnitt, kommer att ge dig alla rader från tabellerna. Men om du vill filtrera den returnerade informationen måste du lägga till en "WHERE"-sats.
WHERE-satsen används för att filtrera resultatuppsättningen som returneras av SQL-fråga. Så här fungerar WHERE-satsen:
- I WHERE-satsen kan du ange ett "uttryck".
- Det uttrycket kommer att 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 resultatet antingen sant, falskt eller null.
- Då kommer bara rader för vilka uttrycket utvärderades med ett sant värde att returneras, och de med falska eller null-resultat kommer att ignoreras och inte inkluderas i resultatuppsättningen.
- För att filtrera resultatuppsättningen med hjälp av 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 hjälp av uttryck och operatorer.
Uttryck är ett eller flera bokstavliga värden eller kolumner kombinerade med varandra med en operator.
Observera att du kan använda uttryck i både SELECT-satsen och i WHERE-satsen.
I följande exempel kommer vi att prova uttrycken och operatorerna i både select-satsen och WHERE-satsen. För att visa hur de presterar.
Det finns olika typer av uttryck och operatorer som du kan ange enligt följande:
SQLite sammanlänkningsoperatorn "||"
Denna operator används för att sammanfoga en eller flera bokstavliga värden eller kolumner med varandra. Det kommer att producera en sträng med resultat från alla sammanlänkade bokstavliga värden eller kolumner. Till exempel:
SELECT 'Id with Name: '|| StudentId || StudentName AS StudentIdWithName FROM Students;
Detta kommer att sammanfogas till ett nytt alias "StudentIdWithName"
- Det bokstavliga strängvärdet "ID med namn: "
- med värdet av "Student-ID” kolumn och
- med värdet från "Elevs namn" kolumn
SQLite CAST-operatör:
CAST-operatorn används för att konvertera ett värde från en datatyp till en annan data typ.
Till exempel, om du har ett numeriskt värde lagrat som ett strängvärde som detta " '12.5' " och du vill konvertera det till ett numeriskt värde kan du använda CAST-operatorn för att göra detta så här "CAST( '12.5' SOM RIKTIGT)". Eller om du har ett decimalvärde som 12.5, och du behöver bara få heltalsdelen, kan du casta det till ett heltal så här "CAST(12.5 AS INTEGER)".
Exempelvis
I följande kommando kommer vi att försöka konvertera olika värden till andra datatyper:
SELECT CAST('12.5' AS REAL) ToReal, CAST(12.5 AS INTEGER) AS ToInteger;
Detta ger dig:
Resultatet är följande:
- 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 HELTAL) – värdet 12.5 är ett decimalvärde, det kommer att konverteras till ett heltalsvärde. Decimaldelen kommer att trunkeras och den blir 12.
SQLite Aritmetik Operators:
Ta två eller flera numeriska bokstavliga värden eller numeriska kolumner och returnera ett numeriskt värde. De aritmetiska operatorerna som stöds i SQLite är:
|
Exempelvis:
I följande exempel kommer vi att prova de fem aritmetiska operatorerna med bokstavliga numeriska värden i samma
välj klausul:
SELECT 25+6, 25-6, 25*6, 25%6, 25/6;
Detta ger dig:
Lägg märke till hur vi använde en SELECT-sats utan en FROM-sats här. Och detta är tillåtet SQLite så länge vi väljer bokstavliga värden.
SQLite Jämförelseoperatörer
Jämför två operander med varandra och returnera ett sant eller falskt enligt följande:
|
Anteckna det, SQLite uttrycker det sanna värdet med 1 och det falska värdet med 0.
Exempelvis:
SELECT 10<6 AS '<', 10<=6 AS '<=', 10>6 AS '>', 10>=6 AS '>=', 10=6 AS '=', 10==6 AS '==', 10!=6 AS '!=', 10<>6 AS '<>';
Detta kommer att ge något sånt här:
SQLite Mönstermatchande operatorer
"SOM” – används för mönstermatchning. Använder sig av "Tycka om", kan du söka efter värden som matchar ett mönster som anges med ett jokertecken.
Operanden till vänster kan antingen vara ett bokstavligt strängvärde eller en strängkolumn. Mönstret kan specificeras enligt följande:
- Innehåller mönster. Till exempel, Studentnamn SOM "%a%" – detta kommer att söka efter elevernas namn som innehåller bokstaven "a" på valfri plats i kolumnen Studentnamn.
- Börjar med mönstret. Till exempel, "Studentnamn SOM "a%"” – sök på elevernas namn som börjar med bokstaven ”a”.
- Avslutar med mönstret. Till exempel, "Studentnamn GILLAR "%a"” – Sök efter elevernas namn som slutar med bokstaven ”a”.
- Matcha ett enskilt tecken i en sträng med understrecket "_". Till exempel, "Studentnamn SOM "J___"” – Sök efter elevers namn som är 4 tecken långa. Den måste börja med bokstaven "J" och kan ha ytterligare tre tecken efter bokstaven "J".
Exempel på mönstermatchning:
- Få elevers namn som börjar med bokstaven 'j':
SELECT StudentName FROM Students WHERE StudentName LIKE 'j%';
Resultat:
- Få elevernas namn som slutar med bokstaven "y":
SELECT StudentName FROM Students WHERE StudentName LIKE '%y';
Resultat:
- Få elevernas namn som innehåller bokstaven 'n':
SELECT StudentName FROM Students WHERE StudentName LIKE '%n%';
Resultat:
"GLOB" – är likvärdig med LIKE-operatorn, men GLOB är skiftlägeskänslig, till skillnad från LIKE-operatorn. Till exempel kommer följande två kommandon att returnera olika resultat:
SELECT 'Jack' GLOB 'j%'; SELECT 'Jack' LIKE 'j%';
Detta ger dig:
- Den första satsen returnerar 0(false) eftersom GLOB-operatorn är skiftlägeskänslig, så 'j' är inte lika med 'J'. Den andra satsen kommer dock att returnera 1 (true) eftersom LIKE-operatorn är skiftlägesokänslig, så 'j' är lika med 'J'.
Andra operatörer:
SQLite AND
En logisk operator som kombinerar ett eller flera uttryck. Det kommer att returnera sant, bara om alla uttryck ger ett "sant" värde. Det kommer dock endast att returnera falskt om alla uttryck ger ett "falskt" värde.
Exempelvis:
Följande fråga kommer att söka efter studenter som har StudentId > 5 och StudentName börjar med bokstaven N, de returnerade eleverna måste uppfylla de två villkoren:
SELECT * FROM Students WHERE (StudentId > 5) AND (StudentName LIKE 'N%');
Som en utgång, i skärmdumpen ovan, ger detta dig bara "Nancy". Nancy är den enda studenten som uppfyller båda villkoren.
SQLite OR
En logisk operator som kombinerar ett eller flera uttryck, så att om en av de kombinerade operatorerna ger sant, kommer den att returnera sant. Men om alla uttryck ger falskt, kommer det att returnera falskt.
Exempelvis:
Följande fråga kommer att söka efter studenter som har StudentId > 5 eller StudentName börjar med bokstaven N, de returnerade eleverna måste uppfylla minst ett av villkoren:
SELECT * FROM Students WHERE (StudentId > 5) OR (StudentName LIKE 'N%');
Detta ger dig:
Som en utgång, i skärmdumpen ovan, kommer detta att ge dig namnet på en elev med bokstaven "n" i namnet plus student-id:t med värde>5.
Som du kan se är resultatet annorlunda än frågan med AND-operatorn.
SQLite MELLAN
BETWEEN används för att välja de värden som ligger inom ett intervall på två värden. Till exempel, "X MELLAN Y OCH Z” kommer att returnera sant (1) om värdet X är mellan de två värdena Y och Z. Annars kommer det att returnera falskt (0). "X MELLAN Y OCH Z" är ekvivalent med "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.
Exempelvis:
I följande exempelfråga kommer vi att skriva en fråga för att få elever med Id-värde mellan 5 och 8:
SELECT * FROM Students WHERE StudentId BETWEEN 5 AND 8;
Detta ger endast elever med ID 5, 6, 7 och 8:
SQLite IN
Tar en operand och en lista med operander. Det kommer att returnera sant om det första operandvärdet är lika med ett av operandernas värde från listan. IN-operatorn returnerar true (1) om listan med operander innehåller det första operandvärdet inom dess värden. Annars kommer den att returnera false (0).
Så här: "kol IN(x, y, z)". Detta motsvarar " (kol=x) eller (kol=y) eller (kol=z) ".
Exempelvis:
Följande fråga väljer endast elever med ID 2, 4, 6, 8:
SELECT * FROM Students WHERE 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 likvärdiga:
SELECT * FROM Students WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);
Båda frågorna ger exakt utdata. Men skillnaden mellan de två frågorna är att den första frågan vi använde "IN"-operatorn. I den andra frågan använde vi flera "ELLER"-operatorer.
IN-operatorn motsvarar att använda flera OR-operatorer. den "WHERE StudentId IN(2, 4, 6, 8)" är ekvivalent med " WHERE (StudentId = 2) ELLER (StudentId = 4) ELLER (StudentId = 6) ELLER (StudentId = 8);"
Så här:
SQLite INTE I
"NOT IN"-operand är motsatsen till IN-operatorn. Men med samma syntax; det krävs en operand och en lista med operander. Det kommer att returnera sant om det första operandens värde inte är lika med ett av operandernas värde från listan. dvs den returnerar true (0) om listan med operander inte innehåller den första operanden. Så här: "kol INTE IN(x, y, z)". Detta motsvarar "(kol<>x) AND (kol<>y) AND (kol<>z)".
Exempelvis:
Följande fråga kommer att välja elever med ID som inte är lika med ett av dessa ID 2, 4, 6, 8:
SELECT * FROM Students WHERE StudentId NOT IN(2, 4, 6, 8);
Gillar du
Den föregående frågan ger vi det exakta resultatet som följande fråga eftersom de är likvärdiga:
SELECT * FROM Students WHERE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);
Så här:
I skärmdumpen ovan,
Vi använde flera icke-lika operatorer "<>" för att få en lista över elever som inte är lika med varken av följande Id:n 2, 4, 6 eller 8. Denna fråga kommer att returnera alla andra studenter än dessa lista med Id:n.
SQLite EXISTS
EXISTS-operatorerna tar inga operander; det tar bara en SELECT-sats efter sig. Operatorn EXISTS returnerar true (1) om det finns några rader som returneras från SELECT-satsen, och den returnerar false (0) om det inte finns några rader alls som returneras från SELECT-satsen.
Exempelvis:
I följande exempel kommer vi att välja institutionens namn, om institutions-id finns i elevtabellen:
SELECT DepartmentName FROM Departments AS d WHERE EXISTS (SELECT DepartmentId FROM Students AS s WHERE d.DepartmentId = s.DepartmentId);
Detta ger dig:
Endast de tre avdelningarna "IT, fysik och konst” kommer att returneras. Och avdelningens namn "Math” kommer inte att returneras eftersom det inte finns någon student på den institutionen, så institutions-ID finns inte i studenttabellen. Det är därför EXISTS-operatören ignorerade "Math” avdelning.
SQLite INTE
Revraderar resultatet av föregående operator som kommer efter det. Till exempel:
- INTE MELLAN – Det kommer att returnera sant om BETWEEN returnerar falskt och vice versa.
- NOT LIKE – Det kommer att returnera sant om LIKE returnerar falskt och vice versa.
- INTE GLOB – Det kommer att returnera sant om GLOB returnerar falskt och vice versa.
- FINNS INTE – Det kommer att returnera sant om EXISTER returnerar falskt och vice versa.
Exempelvis:
I följande exempel kommer vi att använda operatorn NOT med operatorn EXISTS för att få avdelningarnas namn som inte finns i tabellen Studenter, vilket är det omvända resultatet av operatorn EXISTS. Så, sökningen kommer att göras genom DepartmentId som inte finns i avdelningstabellen.
SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
FROM Students AS s
WHERE d.DepartmentId = s.DepartmentId);
Produktion:
Endast avdelningen "Math ” kommer att returneras. Eftersom den "Math” avdelningen är den enda avdelningen som inte finns i elevtabellen.
Begränsning och beställning
SQLite Ordersammanställning
SQLite Ordning är att sortera ditt resultat efter ett eller flera uttryck. För att beställa resultatuppsättningen måste du använda ORDER BY-satsen enligt följande:
- Först måste du ange ORDER BY-satsen.
- ORDER BY-satsen måste anges i slutet av frågan; endast LIMIT-satsen 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 skulle data sorteras stigande.
- Du kan ange fler uttryck genom att använda "," mellan varandra.
Exempelvis
I följande exempel kommer vi att välja alla elever sorterade efter deras namn men i fallande ordning, sedan efter institutionens namn i stigande ordning:
SELECT s.StudentName, d.DepartmentName FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId ORDER BY d.DepartmentName ASC , s.StudentName DESC;
Detta ger dig:
- SQLite kommer först att beställa alla studenter efter deras institutionsnamn i stigande ordning
- Sedan för varje institutionsnamn kommer alla studenter under det institutionsnamnet att visas i fallande ordning efter deras namn
SQLite Begränsa:
Du kan begränsa antalet rader som returneras av din SQL-fråga genom att använda LIMIT-satsen. Till exempel, LIMIT 10 ger dig bara 10 rader och ignorerar alla andra rader.
I LIMIT-satsen kan du välja ett specifikt antal rader med början från en specifik position med hjälp av OFFSET-satsen. Till exempel, "LIMIT 4 OFFSET 4” ignorerar de första 4 raderna och returnerar 4 rader med början från den femte raden, så du får raderna 5,6,7 och 8.
Observera att OFFSET-satsen är valfri, du kan skriva den som "GRÄNS 4, 4” och det kommer att ge dig de exakta resultaten.
Exempelvis:
I följande exempel returnerar vi endast 3 elever från student-id 5 med hjälp av frågan:
SELECT * FROM Students LIMIT 4,3;
Detta ger dig bara tre elever från rad 5. Så det kommer att ge dig raderna med StudentId 5, 6 och 7:
Ta bort dubbletter
Om din SQL-fråga returnerar dubbla värden kan du använda "TYDLIG” nyckelord för att ta bort dessa dubbletter och returnera distinkta värden. Du kan ange mer än en kolumn efter DISTINCT-tangentens arbete.
Exempelvis:
Följande fråga kommer att returnera dubbletter av "avdelningens namnvärden": Här har vi dubbletter av värden med namnen IT, Fysik och Konst.
SELECT d.DepartmentName FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Detta ger dig dubbletter av värden för avdelningsnamnet:
Lägg märke till hur det finns dubbletter av värden för avdelningsnamnet. Nu kommer vi att använda nyckelordet DISTINCT med samma fråga för att ta bort dessa dubbletter och bara få unika värden. Så här:
SELECT DISTINCT d.DepartmentName FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Detta ger dig bara tre unika värden för avdelningsnamnkolumnen:
Aggregate
SQLite Aggregat är inbyggda funktioner definierade i SQLite som kommer att gruppera flera värden av flera rader till ett värde.
Här är de aggregat som stöds av SQLite:
SQLite AVG()
Returnerade medelvärdet för alla x-värden.
Exempelvis:
I följande exempel kommer vi att få det genomsnittliga betyg som eleverna får från alla tentor:
SELECT AVG(Mark) FROM Marks;
Detta ger dig värdet "18.375":
Dessa resultat kommer från summeringen av alla märkesvärden dividerat med deras antal.
COUNT() – COUNT(X) eller COUNT(*)
Returnerar det totala antalet gånger x-värdet visades. 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 kommer att ignorera NULL-värden.
- COUNT(*): Räkna alla rader från alla kolumner.
- COUNT (DISTINCT x): Du kan ange ett DISTINCT nyckelord före x som kommer att få räkningen av de distinkta värdena på x.
Exempelvis
I följande exempel får vi det totala antalet avdelningar med COUNT(DepartmentId), COUNT(*) och COUNT(DISTINCT DepartmentId) och hur de är olika:
SELECT COUNT(DepartmentId), COUNT(DISTINCT DepartmentId), COUNT(*) FROM Students;
Detta ger dig:
Som följande:
- COUNT(DepartmentId) ger dig antalet av alla avdelnings-id, och det ignorerar null-värdena.
- COUNT(DISTINCT DepartmentId) ger dig distinkta värden för DepartmentId, som bara är 3. Vilket är de tre olika värdena för avdelningsnamn. Lägg märke till att det finns 8 värden för institutionens namn i studentnamnet. Men bara de olika tre värdena som är matematik, IT och fysik.
- COUNT(*) räknar antalet rader i elevtabellen som är 10 rader för 10 elever.
GROUP_CONCAT() – GROUP_CONCAT(X) eller GROUP_CONCAT(X,Y)
GROUP_CONCAT aggregatfunktion sammanfogar flera värden till ett värde med ett kommatecken för att separera dem. Den har följande alternativ:
- GROUP_CONCAT(X): Detta kommer att sammanfoga alla värden på x till en sträng, med kommatecken "," som används som avgränsare mellan värdena. NULL-värden kommer att ignoreras.
- GROUP_CONCAT(X, Y): Detta kommer att sammanfoga värdena för x till en sträng, med värdet på y som avgränsare mellan varje värde istället för standardavgränsaren ','. NULL-värden kommer också att ignoreras.
- GROUP_CONCAT(DISTINCT X): Detta kommer att sammanfoga alla distinkta värden för x till en sträng, med kommatecken "," som används som avgränsare mellan värdena. NULL-värden kommer att ignoreras.
GROUP_CONCAT(Avdelningsnamn) Exempel
Följande fråga kommer att sammanfoga alla avdelningsnamnets värden från studenterna och avdelningstabellen till en kommaseparerad sträng. 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:
SELECT GROUP_CONCAT(d.DepartmentName) FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Detta ger dig:
Detta kommer att ge dig en lista med 8 avdelningars namnvärden sammanlänkade i en sträng kommaseparerad.
GROUP_CONCAT(DISTINCT Avdelningsnamn) Exempel
Följande fråga kommer att sammanfoga de distinkta värdena för institutionsnamnet från tabellen för studenter och institutioner till en sträng avgränsad med kommatecken:
SELECT GROUP_CONCAT(DISTINCT d.DepartmentName) FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Detta ger dig:
Lägg märke till hur resultatet skiljer sig från det föregående resultatet; endast tre värden returnerades som är de distinkta avdelningarnas namn, och dubblettvärdena togs bort.
GROUP_CONCAT(Avdelningsnamn ,'&') Exempel
Följande fråga kommer att sammanfoga alla värden i avdelningsnamnkolumnen från elev- och avdelningstabellen till en sträng, men med tecknet '&' istället för ett kommatecken som avgränsare:
SELECT GROUP_CONCAT(d.DepartmentName, '&') FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Detta ger dig:
Lägg märke till hur tecknet "&" används istället för standardtecknet "," för att separera mellan värdena.
SQLite MAX() & MIN()
MAX(X) ger dig 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 värden på X är null.
Exempelvis
I följande fråga kommer vi att använda funktionerna MIN och MAX för att få det högsta märket och det lägsta märket från "Marks" tabell:
SELECT MAX(Mark), MIN(Mark) FROM Marks;
Detta ger dig:
SQLite SUM(x), totalt(x)
Båda kommer att returnera summan av alla x-värden. Men de är olika i följande:
- SUM returnerar null om alla värden är null, men Total returnerar 0.
- TOTAL returnerar alltid flyttalsvärden. SUM returnerar ett heltalsvärde om alla x-värden är ett heltal. Men om värdena inte är ett heltal kommer det att returnera ett flyttalsvärde.
Exempelvis
I följande fråga kommer vi att använda SUM och total för att få summan av alla poäng i "Marks” tabeller:
SELECT SUM(Mark), TOTAL(Mark) FROM Marks;
Detta ger dig:
Som du kan se returnerar TOTAL alltid en flyttal. Men SUM returnerar ett heltalsvärde eftersom värdena i kolumnen "Mark" kan vara i heltal.
Skillnaden mellan SUM och TOTAL exempel:
I följande fråga kommer vi att visa skillnaden mellan SUM och TOTAL när de får SUMMA av NULL-värden:
SELECT SUM(Mark), TOTAL(Mark) FROM Marks WHERE TestId = 4;
Detta ger dig:
Observera att det inte finns några markeringar för TestId = 4, så det finns nollvärden för det testet. SUM returnerar ett nollvärde som blank, medan TOTAL returnerar 0.
Grupp av
GROUP BY-satsen används för att specificera 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 aggregatfunktion för den.
Exempelvis:
Följande fråga kommer att ge dig det totala antalet studenter som finns på varje institution.
SELECT d.DepartmentName, COUNT(s.StudentId) AS StudentsCount FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId GROUP BY d. DepartmentName;
Detta ger dig:
GROUPBY DepartmentName-satsen kommer att gruppera alla studenter i grupper en för varje institutionsnamn. För varje grupp av "avdelning" kommer den att räkna eleverna på den.
HA-klausul
Om du vill filtrera grupperna 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.
Exempelvis
I följande fråga kommer vi att välja de institutioner som bara har två studenter på:
SELECT d.DepartmentName, COUNT(s.StudentId) AS StudentsCount FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId GROUP BY d. DepartmentName HAVING COUNT(s.StudentId) = 2;
Detta ger dig:
Klausulen HAVING COUNT(S.StudentId) = 2 kommer att filtrera de grupper som returneras och endast returnera de grupper som innehåller exakt två elever på den. I vårt fall har konstavdelningen 2 studenter, så det visas i utgången.
SQLite Fråga & underfråga
Inuti vilken fråga som helst kan du använda en annan fråga antingen i en SELECT, INSERT, DELETE, UPDATE eller i en annan underfråga.
Denna kapslade fråga kallas en underfråga. Vi kommer nu att se några exempel på användning av subqueries i SELECT-satsen. Men i självstudien Modifiering av data kommer vi att se hur vi kan använda underfrågor med INSERT, DELETE och UPDATE-satsen.
Använder subquery i FROM-satsexemplet
I följande fråga kommer vi att inkludera en underfråga i FROM-satsen:
SELECT s.StudentName, t.Mark FROM Students AS s INNER JOIN ( SELECT StudentId, Mark FROM Tests AS t INNER JOIN Marks AS m ON t.TestId = m.TestId ) ON s.StudentId = t.StudentId;
Frågan:
SELECT StudentId, Mark FROM Tests AS t INNER JOIN Marks AS m ON t.TestId = m.TestId
Ovanstående fråga kallas här en underfråga eftersom den är kapslad inuti FROM-satsen. Lägg märke till att vi gav det ett alias "t" så att vi kan referera till kolumnerna som returneras från det i frågan.
Denna fråga ger dig:
Så i vårt fall,
- s.StudentName väljs från huvudfrågan som ger namn på elever och
- t.Mark väljs från underfrågan; som ger betyg som erhållits av var och en av dessa elever
Använder subquery i WHERE-satsexemplet
I följande fråga kommer vi att inkludera en underfråga i WHERE-satsen:
SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
FROM Students AS s
WHERE d.DepartmentId = s.DepartmentId);
Frågan:
SELECT DepartmentId FROM Students AS s WHERE d.DepartmentId = s.DepartmentId
Ovanstående fråga kallas här en underfråga eftersom den är kapslad i WHERE-satsen. Underfrågan returnerar DepartmentId-värdena som kommer att användas av operatören FINNS INTE.
Denna fråga ger dig:
I ovanstående fråga har vi valt den institution som inte har någon student inskriven på den. Vilket är "Matematik"-avdelningen här borta.
uppsättning Operationer – UNION, Intersect
SQLite stöder följande SET-operationer:
UNION & UNION ALLA
Den kombinerar en eller flera resultatuppsättningar (en grupp rader) som returneras från flera SELECT-satser till en resultatuppsättning.
UNION returnerar distinkta värden. UNION ALL kommer dock inte att inkludera dubbletter.
Observera att kolumnnamnet kommer att vara kolumnnamnet som anges i den första SELECT-satsen.
UNION Exempel
I följande exempel kommer vi att få listan med DepartmentId från studenttabellen och listan med DepartmentId från institutionstabellen i samma kolumn:
SELECT DepartmentId AS DepartmentIdUnioned FROM Students UNION SELECT DepartmentId FROM Departments;
Detta ger dig:
Frågan returnerar endast 5 rader som är de distinkta avdelnings-id-värdena. Lägg märke till det första värdet som är nollvärdet.
SQLite UNION ALLA Exempel
I följande exempel kommer vi att få listan med DepartmentId från studenttabellen och listan med DepartmentId från institutionstabellen i samma kolumn:
SELECT DepartmentId AS DepartmentIdUnioned FROM Students UNION ALL SELECT DepartmentId FROM Departments;
Detta ger dig:
Frågan returnerar 14 rader, 10 rader från elevtabellen och 4 från avdelningstabellen. Observera att det finns dubbletter i värdena som returneras. Observera också att kolumnnamnet var det som angavs i den första SELECT-satsen.
Låt oss nu se hur UNION alla kommer att ge olika resultat om vi ersätter UNION ALL med UNION:
SQLite KORSAS
Returnerar värdena som finns i både den kombinerade resultatuppsättningen. Värden som finns i en av de kombinerade resultatuppsättningarna kommer att ignoreras.
Exempelvis
I följande fråga kommer vi att välja de DepartmentId-värden som finns i både tabellerna Studenter och Institutioner i kolumnen DepartmentId:
SELECT DepartmentId FROM Students Intersect SELECT DepartmentId FROM Departments;
Detta ger dig:
Frågan returnerar endast tre värden 1, 2 och 3. Vilket är de värden som finns i båda tabellerna.
Värdena null och 4 inkluderades dock inte eftersom null-värdet endast finns i elevtabellen och inte i avdelningstabellen. Och värdet 4 finns i avdelningstabellen och inte i elevtabellen.
Det är därför både värdena NULL och 4 ignorerades och inte inkluderades i de returnerade värdena.
BORTSETT FRÅN
Anta 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-satsen jämför de två listorna och returnerar de rader som finns i lista1 och som inte finns i lista2.
Exempelvis
I följande fråga kommer vi att välja de DepartmentId-värden som finns i avdelningstabellen och som inte finns i elevtabellen:
SELECT DepartmentId FROM Departments EXCEPT SELECT DepartmentId FROM Students;
Detta ger dig:
Frågan returnerar endast värdet 4. Vilket är det enda värdet som finns i avdelningstabellen och som inte finns i elevtabellen.
NULL-hantering
Den "NULL” värde är ett speciellt värde i SQLite. Det används för att representera ett värde som är okänt eller saknas. Observera att nullvärdet är helt annorlunda än "0” eller tomt ”” värde. Eftersom 0 och det tomma värdet är ett känt värde, är nullvärdet dock okänt.
NULL-värden kräver en speciell hantering i SQLite, kommer vi att se nu hur man hanterar NULL-värdena.
Sök efter NULL-värden
Du kan inte använda den normala likhetsoperatorn (=) för att söka efter nollvärden. Till exempel söker följande fråga efter studenter som har ett null DepartmentId-värde:
SELECT * FROM Students WHERE DepartmentId = NULL;
Den här frågan ger inget resultat:
Eftersom NULL-värdet inte är lika med något annat värde som inkluderar ett null-värde i sig, är det därför som det inte returnerade något resultat.
- Men för att få frågan att fungera måste du använda "ÄR INGET" operatorn för att söka efter nollvärden enligt följande:
SELECT * FROM Students WHERE DepartmentId IS 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 "ÄR INTE NULL" operatör så här:
SELECT * FROM Students WHERE DepartmentId IS NOT 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 du vill välja något 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 dessa villkor för alla värden. Om villkoret är sant kommer det att returnera 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.
Sedan kan du använda CASE-uttrycket för att göra det.
Detta kan användas för att definiera lite logik i SELECT-satsen så att du kan välja vissa resultat beroende på vissa villkor som if-sats till exempel.
CASE-operatorn kan definieras med olika syntaxer enligt följande:
- Du kan använda olika villkor:
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN condition3 THEN result3 … ELSE resultn END
- Eller så kan du bara använda ett uttryck och sätta olika möjliga värden att välja mellan:
CASE expression WHEN value1 THEN result1 WHEN value2 THEN result2 WHEN value3 THEN result3 … ELSE restuln END
Observera att ELSE-satsen är valfri.
Exempelvis
I följande exempel kommer vi att använda CASE uttryck med NULL värde i kolumnen avdelnings-ID i tabellen Studenter för att visa texten "Ingen avdelning" enligt följande:
SELECT
StudentName,
CASE
WHEN DepartmentId IS NULL THEN 'No Department'
ELSE DepartmentId
END AS DepartmentId
FROM Students;
- CASE-operatören kommer att kontrollera värdet på DepartmentId oavsett om det är null eller inte.
- Om det är ett NULL-värde kommer det att välja det bokstavliga värdet 'No Department' istället för DepartmentId-värdet.
- Om det inte är ett nullvärde, kommer det att välja värdet i kolumnen DepartmentId.
Detta ger dig utdata som visas nedan:
Vanligt tabelluttryck
Vanliga tabelluttryck (CTE) är underfrågor som definieras i SQL-satsen med ett givet namn.
Det har en fördel jämfört med underfrågorna eftersom det definieras utifrån SQL-satserna och kommer att göra 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 enligt följande:
WITH CTEname AS ( SELECT statement ) SELECT, UPDATE, INSERT, or update statement here FROM CTE
Den "CTE-namn” är vilket namn du kan ge för CTE, du kan använda det för att referera till det senare. Observera att du kan definiera SELECT-, UPDATE-, INSERT- eller DELETE-satsen på CTE:er
Låt oss nu se ett exempel på hur man använder CTE i SELECT-satsen.
Exempelvis
I följande exempel kommer vi att definiera en CTE från en SELECT-sats, och sedan kommer vi att använda den senare på en annan fråga:
WITH AllDepartments AS ( SELECT DepartmentId, DepartmentName FROM Departments ) SELECT s.StudentId, s.StudentName, a.DepartmentName FROM Students AS s INNER JOIN AllDepartments AS a ON s.DepartmentId = a.DepartmentId;
I den här frågan definierade vi en CTE och gav den namnet "Alla avdelningar". Denna CTE definierades från en SELECT-fråga:
SELECT DepartmentId, DepartmentName FROM Departments
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 resultatet av 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-satsen påverkar utmatningen av frågan.
Denna fråga ger dig:
Avancerade frågor
Avancerade frågor är de frågor som innehåller komplexa kopplingar, underfrågor och vissa aggregat. I följande avsnitt kommer vi att se ett exempel på en avancerad fråga:
Var får vi,
- Institutionens namn med alla studenter för varje institution
- Elevernas namn separerade med kommatecken och
- Visar att institutionen har minst tre studenter på den
SELECT d.DepartmentName, COUNT(s.StudentId) StudentsCount, GROUP_CONCAT(StudentName) AS Students FROM Departments AS d INNER JOIN Students AS s ON s.DepartmentId = d.DepartmentId GROUP BY d.DepartmentName HAVING COUNT(s.StudentId) >= 3;
Vi lade till en JOIN sats för att hämta DepartmentName från tabellen Departments. Efter det lade vi till en GROUP BY-sats med två aggregatfunktioner:
- "COUNT" för att räkna eleverna för varje institutionsgrupp.
- GROUP_CONCAT för att sammanfoga elever för varje grupp med kommatecken separerade i en sträng.
- Efter GROUP BY använde vi HAVING-satsen för att filtrera avdelningarna och välja endast de avdelningar som har minst 3 studenter.
Resultatet blir följande:
Sammanfattning
Detta var en introduktion till skrivandet SQLite frågor och grunderna för att söka efter databasen och hur du kan filtrera den returnerade informationen. Du kan nu, skriva din egen SQLite frågor.
