SQLite Fråga: Välj, Var, LIMIT, OFFSET, Count, Group By

Avsnitten nedan går igenom hur man läser data med SELECT och FROM, filtrerar rader med WHERE och dess operatorer, ordnar och begränsar resultat, aggregeringsfunktioner, GROUP BY och HAVING, delfrågor, set-operationer, NULL-hantering, CASE-uttryck och vanliga tabelluttryck.

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 är "StudentName"; 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 Student-tabellen alias ”s” och avdelningstabellen alias ”d”. Istä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 hjälp av ett villkor. I vårt exempel sammanfogade vi Student-tabellen med Avdelningstabellen med kolumnen DepartmentId. Det finns också en utförlig förklaring av INNER JOIN i ”SQLite Fogar”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-klausulen används för att filtrera resultatmängden som returneras av SQL-frågan. Så här fungerar WHERE-klausulen:

• 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":

• Literal strängvärdet "Id med namn:"
• med värdet i kolumnen ”StudentId” och
• med värdet från kolumnen ”StudentName”

SQLite CAST-operatör:

CAST-operatorn används för att konvertera ett värde från en data typ till en annan datatyp.

Om du till exempel 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' ​​AS REAL)”. Eller om du har ett decimalvärde som 12.5, och du bara behöver få heltalsdelen, kan du konvertera det till ett heltal som detta ”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:

• Additionen “+” – ger summan av de två operanderna.
• Subtraction “–” – subtracTestar de två operanderna och resulterar i skillnaden.
• Multiplikationen “*” – produkten av de två operanderna.
• Påminnelse (modulo) “%” – ger resten som blir resultatet av att dividera en operand med den andra operand.
• Division “/” – returnerar kvotresultatet från att dividera den vänstra operanden med den högra operanden.

Exempel:

I följande exempel ska vi prova de fem aritmetiska operatorerna med bokstavliga numeriska värden i samma select-sats:

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:

• "<" – returnerar sant om den vänstra operand är mindre än den högra operand.
• “<=” – returnerar sant om den vänstra operand är mindre än eller lika med den högra operand.
• ">" – returnerar sant om den vänstra operanden är större än den högra operanden.
• ">=" – returnerar sant om den vänstra operand är större än eller lika med den högra operand.
• "=" och "="" – returnerar sant om de två operanderna är lika. Observera att båda operatorerna är desamma och att det inte finns någon skillnad mellan dem.
• “!=” och “<>” – returnerar sant om de två operanderna inte är lika. Observera att båda operatorerna är desamma och att det inte finns någon skillnad mellan dem.

Anteckna det, SQLite uttrycker det sanna värdet med 1 och det falska värdet med 0.

Exempel:

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

”LIKE” – används för mönstermatchning. Med hjälp av ”Like” 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 StudentName LIKE '%a%' – detta söker efter studentnamn som innehåller bokstaven "a" på valfri position i StudentName-kolumnen.
• Börjar med mönstret. Till exempel, ”StudentName LIKE 'a%'” – sök efter elevernas namn som börjar med bokstaven ”a”.
• Slutar med mönstret. Till exempel, ”StudentName LIKE '%a'” – Sök efter elevernas namn som slutar med bokstaven ”a”.
• Matcha ett enskilt tecken i en sträng med hjälp av understrecket "_". Till exempel "StudentName LIKE 'J___'" – Sök efter studentnamn som är 4 tecken långa. Det måste börja med bokstaven "J" och kan innehålla 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” – motsvarar LIKE-operatorn, men GLOB är skiftlägeskänsligt, 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.

Exempel:

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.

Exempel:

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

MELLAN används för att välja värden som ligger inom ett intervall av två värden. Till exempel returnerar "X MELLAN Y OCH Z" sant (1) om värdet X ligger 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 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: “col IN(x, y, z)“. Detta motsvarar ”(col=x) eller (col=y) eller (col=z)”.

Exempel:

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.

Operatorn IN motsvarar att använda flera ELLER-operatorer. "WHERE StudentId IN(2, 4, 6, 8)" motsvarar "WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);"

Så här:

SQLite INTE I

Operanden ”NOT IN” är motsatsen till operatorn IN. Men med samma syntax; den tar en operand och en lista med operander. Den returnerar sant om det första operandvärdet inte är lika med en av operandernas värden från listan. dvs. den returnerar sant (0) om listan med operander inte innehåller den första operanden. Så här: ”col NOT IN(x, y, z)”. Detta motsvarar ”(col<>x) AND (col<>y) AND (col<>z)”.

Exempel:

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.

Exempel:

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 institutionerna "IT, fysik och konst" kommer att returneras. Och institutionsnamnet "Matte" 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 operatorn EXISTS ignorerade institutionen "Matte".

SQLite INTE

Reverses resultatet av den föregående operatorn 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.

Exempel:

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 "Matte" kommer att returneras. Eftersom avdelningen "Matte" är den enda avdelningen finns den inte 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-klausulen kan du välja ett specifikt antal rader med början från en specifik position med hjälp av OFFSET-klausulen. Till exempel kommer "LIMIT 4 OFFSET 4" att ignorera de första 4 raderna och returnera 4 rader med början från den femte raden, så du får raderna 5, 6, 7 och 8.

Observera att OFFSET-klausulen är valfri, du kan skriva den som "LIMIT 4, 4" så ger den dig exakta resultat.

Exempel:

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 dubbletter kan du använda nyckelordet "DISTINCT" för att ta bort dessa dubbletter och returnera distinkta värden. Du kan ange mer än en kolumn efter att DISTINCT-nyckeln har fungerat.

Exempel:

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.

Exempel:

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 använder vi funktionerna MIN och MAX för att hämta det högsta och lägsta betyget från tabellen "Poäng":

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 använder vi SUM och total för att få summan av alla poäng i tabellerna "Poäng":

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.

Exempel:

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

Värdet ”NULL” är ett specialvärde i SQLiteDet används för att representera ett värde som är okänt eller saknar värde. Observera att null-värdet är helt annorlunda än "0" eller ett tomt "". 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, 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.

För att frågan ska fungera måste du dock använda operatorn "IS NULL" för att söka efter nullvä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 operatorn "ÄR INTE NULL" 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 använder vi CASE-uttrycket med NULL-värdet 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

”CTE-namn” är vilket namn du än kan ge CTE:n, som du kan använda för att referera till den senare. Observera att du kan definiera SELECT-, UPDATE-, INSERT- eller DELETE-satser 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 "AllDepartments". 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-klausul för att hämta DepartmentName från tabellen Departments. Efter det lade vi till en GROUP BY-klausul med två aggregeringsfunktioner:

• "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:

Vanliga frågor