SQLite Lekérdezés: Select, Hol, LIMIT, OFFSET, Count, Group By

SQL lekérdezések írásához egy SQLite adatbázis, ismernie kell a SELECT, FROM, WHERE, GROUP BY, ORDER BY és LIMIT záradékok működését és felhasználását.

Ebben az oktatóanyagban megtudhatja, hogyan kell használni ezeket a záradékokat és hogyan kell írni SQLite záradékok.

Adatok olvasása a Select segítségével

A SELECT záradék a fő utasítás, amelyet egy lekérdezéshez használ SQLite adatbázis. A SELECT záradékban megadja, hogy mit válasszon. De a select záradék előtt nézzük meg, honnan tudunk adatokat kiválasztani a FROM záradék segítségével.

A FROM záradék annak meghatározására szolgál, hogy hol kívánja kiválasztani az adatokat. A from záradékban megadhat egy vagy több táblát vagy részlekérdezést az adatok kiválasztásához, amint azt később látni fogjuk az oktatóanyagokban.

Vegye figyelembe, hogy a következő példák mindegyikéhez le kell futtatnia az sqlite3.exe fájlt, és meg kell nyitnia a kapcsolatot a mintaadatbázishoz folyamként:

Step 1) Ebben a lépésben a

1. Nyissa meg a Sajátgépet, és keresse meg a következő könyvtárat:C:\sqlite"És
2. Ezután nyissa megsqlite3.exe"

Step 2) Nyissa meg az adatbázist "TutorialsSampleDB.db” a következő paranccsal:

Most már készen áll bármilyen típusú lekérdezés futtatására az adatbázisban.

A SELECT záradékban nem csak egy oszlopnevet jelölhet ki, hanem sok más lehetőség is van a kiválasztandó elemek meghatározására. Az alábbiak szerint:

SELECT *

Ez a parancs kijelöli az összes oszlopot az összes hivatkozott táblából (vagy allekérdezésből) a FROM záradékban. Például:

SELECT * 
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Ezzel kijelöli az összes oszlopot mind a tanulók, mind a tanszéki táblázatokból:

SELECT táblanév.*

Ezzel az összes oszlopot csak a „táblanév” táblából választja ki. Például:

SELECT Students.*
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Ezzel csak a tanulói táblázat összes oszlopát választja ki:

Szó szerinti érték

A literális érték egy állandó érték, amely a select utasításban adható meg. A literális értékeket ugyanúgy használhatja, mint az oszlopneveket a SELECT záradékban. Ezek a literális értékek az SQL-lekérdezés által visszaadott sorok soraiban jelennek meg.

Íme néhány példa a választható literális értékekre:

• Numerikus Literális – számok bármilyen formátumban, például 1, 2.55, … stb.
• Karakterlánc-literálok – bármely „USA”, „ez mintaszöveg” stb. karakterlánc.
• NULL – NULL érték.
• Current_TIME – Megadja az aktuális időt.
• CURRENT_DATE – ez az aktuális dátumot jeleníti meg.

Ez hasznos lehet bizonyos helyzetekben, amikor állandó értéket kell kiválasztania az összes visszaadott sorhoz. Például, ha ki szeretné jelölni az összes diákot a Diákok táblából egy ország nevű új oszloppal, amely az „USA” értéket tartalmazza, ezt megteheti:

SELECT *, 'USA' AS Country FROM Students;

Ekkor megjelenik az összes diák oszlopa, valamint egy új „Ország” oszlop, például:

Vegye figyelembe, hogy ez az új Ország oszlop valójában nem a táblázathoz hozzáadott új oszlop. Ez egy virtuális oszlop, amely a lekérdezésben jön létre az eredmények megjelenítésére, és nem jön létre a táblázatban.

Nevek és Alias

Az álnév az oszlop új neve, amely lehetővé teszi az oszlop új névvel történő kiválasztását. Az oszlopálnevek az „AS” kulcsszóval vannak megadva.

Például, ha azt szeretné, hogy a StudentName oszlopban a „Student Name” helyett „Student Name” legyen visszaadva, a következő álnevet adhatja neki:

SELECT StudentName AS 'Student Name' FROM Students;

Ezzel a tanulók neveit „Student Name” helyett „Student Name” néven kapja meg, így:

Vegye figyelembe, hogy az oszlop neve továbbra is "Tanuló név“; a StudentName oszlop továbbra is ugyanaz, az alias nem változik.

Az álnév nem változtatja meg az oszlop nevét; csak a SELECT záradékban lévő megjelenített nevet fogja megváltoztatni.

Azt is vegye figyelembe, hogy az „AS” kulcsszó nem kötelező, az alias nevét megadhatja anélkül, ilyesmi:

SELECT StudentName 'Student Name' FROM Students;

És pontosan ugyanazt a kimenetet adja, mint az előző lekérdezés:

A táblázatoknak álneveket is megadhat, nem csak oszlopokat. Ugyanazzal az „AS” kulcsszóval. Például ezt teheti:

SELECT s.* FROM Students AS s;

Ekkor megjelenik a Diákok táblázat összes oszlopa:

Ez nagyon hasznos lehet, ha egynél több asztalhoz csatlakozik; ahelyett, hogy megismételné a teljes tábla nevét a lekérdezésben, minden táblának megadhat egy rövid álnevet. Például a következő lekérdezésben:

SELECT Students.StudentName, Departments.DepartmentName
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Ez a lekérdezés kiválasztja az egyes hallgatók nevét a „Hallgatók” táblázatból, a tanszékneveket pedig a „Részlegek” táblázatból:

Azonban ugyanaz a lekérdezés felírható így:

SELECT s.StudentName, d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

• A Hallgatói táblázatnak „s”, a tanszéki táblázatnak pedig „d” álnevet adtunk.
• Ezután a teljes tábla nevének használata helyett az álneveiket használtuk a hivatkozásra.
• Az INNER JOIN két vagy több táblát egyesít egy feltétel segítségével. Példánkban a Hallgatók táblát összekapcsoltuk a Tanszékek táblával az Osztályazonosító oszloppal. A BELSŐ CSATLAKOZÁSRA is van egy mélyreható magyarázat a „SQLite Csatlakozás” oktatóanyag.

Ez a pontos kimenetet adja meg, mint az előző lekérdezésnél:

AHOL

Ha SQL-lekérdezéseket ír a SELECT záradék használatával, a FROM záradékkal együtt, amint azt az előző részben láttuk, akkor a táblák összes sora megjelenik. Ha azonban szűrni szeretné a visszaadott adatokat, akkor hozzá kell adnia egy „WHERE” záradékot.

A WHERE záradékkal szűrheti a visszaadott eredménykészletet SQL lekérdezés. Így működik a WHERE záradék:

• A WHERE záradékban megadhat egy „kifejezést”.
• Ezt a kifejezést a rendszer a FROM záradékban megadott táblá(k)ból visszaadott minden sorra kiértékeli.
• A kifejezés logikai kifejezésként kerül kiértékelésre, amelynek eredménye igaz, hamis vagy nulla.
• Ekkor csak azok a sorok jelennek meg, amelyeknél a kifejezés igaz értékkel lett kiértékelve, a hamis vagy null eredménnyel rendelkezők pedig figyelmen kívül maradnak, és nem szerepelnek az eredményhalmazban.
• Az eredménykészlet WHERE záradékkal történő szűréséhez kifejezéseket és operátorokat kell használnia.

Az üzemeltetők listája itt SQLite és hogyan kell használni őket

A következő részben elmagyarázzuk, hogyan szűrhet kifejezések és operátorok használatával.

A kifejezés egy vagy több literális érték vagy oszlop egy operátorral kombinálva egymással.

Vegye figyelembe, hogy a SELECT és a WHERE záradékban is használhat kifejezéseket.

A következő példákban megpróbáljuk a kifejezéseket és az operátorokat mind a select, mind a WHERE záradékban. Annak érdekében, hogy megmutassa, hogyan teljesítenek.

Különféle típusú kifejezések és operátorok léteznek, amelyeket az alábbiak szerint adhat meg:

SQLite a „||” összefűzési operátor

Ez az operátor egy vagy több literális érték vagy oszlop összefűzésére szolgál. Az összes összefűzött literális értékből vagy oszlopból egyetlen eredménysort fog készíteni. Például:

SELECT 'Id with Name: '|| StudentId || StudentName AS StudentIdWithName
FROM Students;

Ez egy új álnévvé fog összefűzni "StudentIdWithName"

• A szó szerinti karakterlánc értéke "Azonosító névvel: "
• a "Diákigazolvány” oszlop és
• a következő értékkel: "Tanuló név” oszlopban

SQLite CAST operátor:

A CAST operátor egy érték konvertálására szolgál egy adattípusból a másikba adattípus.

Például, ha van egy numerikus érték karakterláncként tárolva, mint ez " „12.5” ", és számértékké szeretné alakítani, a CAST operátor segítségével ezt így teheti meg "CAST ('12.5' VALÓDI)“. Vagy ha van egy decimális értéke, például 12.5, és csak az egész részt kell megkapnia, akkor egész számra öntheti, mint például a „CAST(12.5 AS INTEGER)”.

Példa

A következő parancsban megpróbáljuk a különböző értékeket más adattípusokká konvertálni:

SELECT CAST('12.5' AS REAL) ToReal, CAST(12.5 AS INTEGER) AS ToInteger;

Ez a következőket adja:

Az eredmény a következő:

• CAST('12.5' AS REAL) – a '12.5' érték egy karakterlánc érték, amely VALÓDI értékké lesz konvertálva.
• CAST(12.5 AS INTEGER) – a 12.5 érték egy decimális érték, a rendszer egész értékké konvertálja. A tizedes rész csonkolva lesz, és 12 lesz.

SQLite Számtan Operators:

Vegyünk két vagy több numerikus literális értéket vagy numerikus oszlopot, és adjunk vissza egy numerikus értéket. A támogatott aritmetikai operátorok SQLite vannak:

Kiegészítés "+” – adja meg a két operandus összegét. kivonás "-” – kivonja a két operandust, és a különbséget eredményezi. Szorzás"*” – a két operandus szorzata. Emlékeztető (modulo) "%” – megadja az egyik operandus és a második operandus elosztásából származó maradékot. Osztály "/” – a bal oldali operandus jobb oldali operandusszal való osztásának hányadosát adja vissza.

Példa:

A következő példában megpróbáljuk az öt aritmetikai operátort literális numerikus értékekkel ugyanabban

kitétel kiválasztása:

SELECT 25+6, 25-6, 25*6, 25%6, 25/6;

Ez a következőket adja:

Figyeljük meg, hogyan használtunk itt egy SELECT utasítást FROM záradék nélkül. És ez be van engedve SQLite mindaddig, amíg literális értékeket választunk.

SQLite Összehasonlító operátorok

Hasonlítson össze két operandust egymással, és adjon vissza igaz vagy hamis értéket a következőképpen:

"<” – igazat ad vissza, ha a bal oldali operandus kisebb, mint a jobb oldali operandus. "<=” – igazat ad vissza, ha a bal oldali operandus kisebb vagy egyenlő, mint a jobb oldali operandus. ">” – igazat ad vissza, ha a bal oldali operandus nagyobb, mint a jobb oldali operandus. ">=” – igazat ad vissza, ha a bal oldali operandus nagyobb vagy egyenlő, mint a jobb oldali operandus. "="És"==” – igaz értéket ad vissza, ha a két operandus egyenlő. Vegye figyelembe, hogy mindkét operátor ugyanaz, és nincs köztük különbség. "!="És"<>” – igaz értéket ad vissza, ha a két operandus nem egyenlő. Vegye figyelembe, hogy mindkét operátor ugyanaz, és nincs köztük különbség.

Vegye figyelembe, hogy, SQLite az igaz értéket 1-gyel, a hamis értéket pedig 0-val fejezi ki.

Példa:

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 '<>';

Ez valami ilyesmit fog adni:

SQLite Pattern Matching operátorok

"HASONLÓ” – a mintaillesztésre szolgál. A „Mint“, akkor olyan értékeket kereshet, amelyek megfelelnek a helyettesítő karakterrel megadott mintának.

A bal oldali operandus lehet karakterlánc-literális érték vagy karakterlánc-oszlop. A minta a következőképpen határozható meg:

• Mintát tartalmaz. Például, Tanulónév LIKE „%a%” – ez megkeresi azokat a tanulók neveit, amelyek a StudentName oszlop bármely pozíciójában az „a” betűt tartalmazzák.
• A mintával kezdődik. Például, "Diáknév LIKE 'a%'” – keresse meg a tanulók „a” betűvel kezdődő nevét.
• A mintával végződik. Például, "Diáknév LIKE „%a”” – Keresse meg a tanulók „a” betűvel végződő nevét.
• Egy karakterlánc egyetlen karakterének egyeztetése a „_” aláhúzás betűvel. Például, "Diáknév LIKE 'J___'” – 4 karakter hosszúságú tanulónevek keresése. A „J” betűvel kell kezdődnie, és a „J” betű után további három karaktert tartalmazhat.

Mintaillesztési példák:

1. Szerezzen „j” betűvel kezdődő tanulóneveket:

   SELECT StudentName FROM Students WHERE StudentName LIKE 'j%';

   Eredmény:

   

2. A tanulók neve y betűvel végződjön:

   SELECT StudentName FROM Students WHERE StudentName LIKE '%y';

   Eredmény:

   

3. Az „n” betűt tartalmazó tanulónevek lekérése:

   SELECT StudentName FROM Students WHERE StudentName LIKE '%n%';

   Eredmény:

   

"GLOB" – egyenértékű a LIKE operátorral, de a GLOB megkülönbözteti a kis- és nagybetűket, ellentétben a LIKE operátorral. Például a következő két parancs különböző eredményeket ad vissza:

SELECT 'Jack' GLOB 'j%';
SELECT 'Jack' LIKE 'j%';

Ez a következőket adja:

• Az első utasítás 0 (hamis) értéket ad vissza, mivel a GLOB operátor megkülönbözteti a kis- és nagybetűket, így a „j” nem egyenlő a „J”-vel. A második utasítás azonban 1-et (igaz) ad vissza, mivel a LIKE operátor nem érzékeny a kis- és nagybetűkre, így a „j” egyenlő a „J”-vel.

Egyéb operátorok:

SQLite ÉS

Logikai operátor, amely egy vagy több kifejezést kombinál. Igazat ad vissza, csak akkor, ha minden kifejezés „igaz” értéket ad. Azonban csak akkor ad vissza hamis értéket, ha az összes kifejezés „hamis” értéket ad.

Példa:

A következő lekérdezés azokat a hallgatókat keresi, akiknek StudentId > 5, és a StudentName N betűvel kezdődik, a visszaküldött hallgatóknak meg kell felelniük a két feltételnek:

SELECT * 
FROM Students 
WHERE (StudentId > 5) AND (StudentName LIKE 'N%');

Kimenetként a fenti képernyőképen ez csak „Nancy”-t fog adni. Nancy az egyetlen diák, aki mindkét feltételnek megfelel.

SQLite OR

Logikai operátor, amely egy vagy több kifejezést kombinál úgy, hogy ha a kombinált operátorok egyike igazat ad, akkor igazat ad vissza. Ha azonban az összes kifejezés hamis értéket ad, akkor hamis értéket ad vissza.

Példa:

A következő lekérdezés azokat a tanulókat keresi, akiknek StudentId > 5 vagy a StudentName N betűvel kezdődik, a visszaküldött hallgatóknak teljesíteniük kell legalább egy feltételt:

SELECT * 
FROM Students 
WHERE (StudentId > 5) OR (StudentName LIKE 'N%');

Ez a következőket adja:

Kimenetként a fenti képernyőképen ez egy olyan diák nevét adja meg, akinek a nevében „n” betű van, valamint a diákazonosító értéke >5.

Amint láthatja, az eredmény eltér az ÉS operátorral végzett lekérdezéstől.

SQLite KÖZÖTT

A BETWEEN segítségével kiválaszthatja azokat az értékeket, amelyek két érték tartományán belül vannak. Például, "X Y ÉS Z KÖZÖTT” igaz (1) értéket ad vissza, ha az X érték a két Y és Z érték között van. Ellenkező esetben hamis (0) értéket ad vissza. "X Y ÉS Z KÖZÖTT" egyenértékű a "X >= Y ÉS X <= Z“, X-nek nagyobbnak vagy egyenlőnek kell lennie Y-nál, és X kisebbnek vagy egyenlőnek kell lennie Z-vel.

Példa:

A következő példalekérdezésben egy lekérdezést írunk, hogy 5 és 8 közötti azonosítóval rendelkező tanulókat kapjunk:

SELECT *
FROM Students
WHERE StudentId BETWEEN 5 AND 8;

Ez csak az 5-ös, 6-os, 7-es és 8-as azonosítójú tanulókat fogja megadni:

SQLite IN

Egy operandust és egy operanduslistát vesz igénybe. Igaz értéket ad vissza, ha az első operandus értéke megegyezik az egyik operandus értékével a listából. Az IN operátor true (1) értéket ad vissza, ha az operandusok listája az első operandusértéket tartalmazza az értékein belül. Ellenkező esetben false (0) értéket ad vissza.

Mint ez: "oszlop IN(x, y, z)“. Ez egyenértékű a " (col=x) vagy (col=y) vagy (col=z) ".

Példa:

A következő lekérdezés csak a 2-es, 4-es, 6-os, 8-as azonosítójú tanulókat választja ki:

SELECT * 
FROM Students
WHERE StudentId IN(2, 4, 6, 8);

Mint ez:

Az előző lekérdezés pontos eredményt ad a következő lekérdezésként, mert egyenértékűek:

SELECT * 
FROM Students
WHERE (StudentId = 2) OR (StudentId =  4) OR (StudentId =  6) OR (StudentId = 8);

Mindkét lekérdezés pontos kimenetet ad. A két lekérdezés közötti különbség azonban az, hogy az első lekérdezésnél az „IN” operátort használtuk. A második lekérdezésben több „OR” operátort használtunk.

Az IN operátor egyenértékű több VAGY operátor használatával. A „WHERE StudentId IN(2, 4, 6, 8)"egyenértékű" WHERE (StudentId = 2) VAGY (StudentId = 4) OR (StudentId = 6) VAGY (StudentId = 8);"

Mint ez:

SQLite NEM BENT

A „NOT IN” operandus az IN operátor ellentéte. De ugyanazzal a szintaxissal; egy operandusra és egy operanduslistára van szükség. Igazat ad vissza, ha az első operandus értéke nem egyenlő az egyik operandus értékével a listából. azaz igaz (0) értéket ad vissza, ha az operandusok listája nem tartalmazza az első operandust. így: "oszlop NEM IN(x, y, z)“. Ez egyenértékű a "(col<>x) ÉS (col<>y) ÉS (col<>z)".

Példa:

A következő lekérdezés azokat a tanulókat választja ki, akiknek az azonosítója nem egyezik meg a következő azonosítókkal: 2, 4, 6, 8:

SELECT * 
FROM Students
WHERE StudentId NOT IN(2, 4, 6, 8);

Mint ez

Az előző lekérdezésnél a pontos eredményt a következő lekérdezésként adjuk meg, mert egyenértékűek:

SELECT * 
FROM Students
WHERE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);

Mint ez:

A fenti képernyőképen

Több „<>” nem egyenlő operátort használtunk, hogy egy listát kapjunk azokról a tanulókról, amelyek nem egyenlők a következő 2-es, 4-es, 6-os vagy 8-as azonosítókkal. Ez a lekérdezés az összes többi tanulót visszaadja, kivéve az azonosítók listáját.

SQLite LÉTEZIK

Az EXISTS operátorok nem vesznek fel semmilyen operandust; csak egy SELECT záradék kell utána. Az EXISTS operátor true (1) értéket ad vissza, ha vannak olyan sorok, amelyeket a SELECT záradék ad vissza, és hamis (0) értéket ad vissza, ha a SELECT záradékból egyáltalán nincsenek visszaadva sorok.

Példa:

A következő példában a tanszék nevét választjuk ki, ha a tanszéki azonosító létezik a hallgatói táblázatban:

SELECT DepartmentName
FROM Departments AS d
WHERE EXISTS (SELECT DepartmentId FROM Students AS s WHERE d.DepartmentId = s.DepartmentId);

Ez a következőket adja:

Csak a három osztály"IT, fizika és művészetek” visszaküldik. És az osztály neve "Math” nem kerül visszaadásra, mert nincs hallgató azon a tanszéken, így a tanszéki azonosító nem létezik a hallgatói táblázatban. Ezért az EXISTS operátor figyelmen kívül hagyta a „Math” osztály.

SQLite NEM

Revkiértékeli az utána következő előző operátor eredményét. Például:

• NOT BETWEEN – Igaz értéket ad vissza, ha a BETWEEN hamis értéket ad vissza, és fordítva.
• NOT LIKE – Igazat ad vissza, ha a LIKE hamis értéket ad vissza, és fordítva.
• NOT GLOB – Igaz értéket ad vissza, ha a GLOB hamis értéket ad vissza, és fordítva.
• NOT EXISTS – Igaz értéket ad vissza, ha az EXISTS értéke false, és fordítva.

Példa:

A következő példában a NOT operátort az EXISTS operátorral fogjuk használni, hogy megkapjuk a Diákok táblában nem létező részlegek nevét, ami az EXISTS operátor fordított eredménye. Tehát a keresés a részlegtáblázatban nem szereplő DepartmentId-n keresztül történik.

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId 
                  FROM Students AS s 
                  WHERE d.DepartmentId = s.DepartmentId);

teljesítmény:

Csak az osztály"Math ” visszaküldik. Mert a "Math” tanszék az egyetlen tanszék, amely nem létezik a hallgatói táblázatban.

Korlátozás és rendelés

SQLite Megrendelés

SQLite A sorrend az eredmény egy vagy több kifejezés szerinti rendezése. Az eredménykészlet megrendeléséhez az ORDER BY záradékot kell használni az alábbiak szerint:

• Először is meg kell adnia az ORDER BY záradékot.
• Az ORDER BY záradékot a lekérdezés végén kell megadni; csak utána a LIMIT záradék adható meg.
• Adja meg a kifejezést, amellyel az adatokat rendezni szeretné, ez a kifejezés lehet oszlopnév vagy kifejezés.
• A kifejezés után megadhat egy opcionális rendezési irányt. Vagy a DESC az adatok csökkenő sorrendbe állításához, vagy az ASC az adatok növekvő sorrendje. Ha egyiket sem adja meg, akkor az adatok növekvő sorrendben lesznek rendezve.
• Több kifejezést is megadhat egymás között a „,” használatával.

Példa

A következő példában az összes hallgatót nevük szerint, de csökkenő sorrendben, majd a tanszék neve szerint jelöljük ki növekvő sorrendben:

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;

Ez a következőket adja:

• SQLite először az összes hallgatót osztálynév szerint, növekvő sorrendben rendezi
• Ezután minden tanszéknévnél az adott tanszéknév alatt lévő összes hallgató nevük szerinti csökkenő sorrendben megjelenik

SQLite Határ:

Korlátozhatja az SQL-lekérdezés által visszaadott sorok számát a LIMIT záradék használatával. Például a LIMIT 10 csak 10 sort ad, és figyelmen kívül hagyja az összes többi sort.

A LIMIT záradékban az OFFSET záradék segítségével meghatározott számú sort választhat ki egy adott pozíciótól kezdve. Például, "4. KORLÁTOZÁS 4” figyelmen kívül hagyja az első 4 sort, és 4 sort ad vissza az ötödik sortól kezdve, így az 5,6,7., 8., XNUMX. és XNUMX. sorokat kapja.

Vegye figyelembe, hogy az OFFSET záradék nem kötelező, így írhatja be:4., 4. KORLÁTOZÁS” és a pontos eredményeket fogja megadni.

Példa:

A következő példában csak 3 diákot adunk vissza az 5-ös diákazonosítótól kezdve a lekérdezéssel:

SELECT * FROM Students LIMIT 4,3;

Ezzel csak három tanulót kapsz az 5. sortól kezdve. Tehát az 5., 6. és 7. StudentId sorokat kapod:

Ismétlődések eltávolítása

Ha az SQL-lekérdezés ismétlődő értékeket ad vissza, használhatja a „KÜLÖNBÖZŐ” kulcsszót az ismétlődések eltávolításához és a különböző értékek visszaadásához. A DISTINCT kulcsmunka után több oszlopot is megadhat.

Példa:

A következő lekérdezés ismétlődő „részlegnév értékeket” ad vissza: Itt duplikált értékeket találunk IT, Physics és Arts néven.

SELECT d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Ezzel duplikált értékeket kap az osztálynévhez:

Figyelje meg, hogy a részlegnévnél hogyan vannak ismétlődő értékek. Most a DISTINCT kulcsszót fogjuk használni ugyanazzal a lekérdezéssel, hogy eltávolítsuk az ismétlődéseket, és csak egyedi értékeket kapjunk. Mint ez:

SELECT DISTINCT d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Ez csak három egyedi értéket ad a részlegnév oszlophoz:

adalékanyag

SQLite Az aggregátumok olyan beépített függvények, amelyeket definiálunk SQLite amely több sor több értékét egyetlen értékbe csoportosítja.

Íme a által támogatott aggregátumok SQLite:

SQLite AVG()

Az összes x érték átlagát adta vissza.

Példa:

A következő példában megkapjuk a tanulók átlagos pontszámát az összes vizsgáról:

SELECT AVG(Mark) FROM Marks;

Ez a „18.375” értéket adja:

Ezek az eredmények az összes pontérték összegéből származnak, osztva azok számával.

COUNT() – COUNT(X) vagy COUNT(*)

Az x érték megjelenésének teljes számát adja vissza. Íme néhány lehetőség, amelyet a COUNT funkcióval használhat:

• COUNT(x): Csak x értéket számol, ahol x egy oszlopnév. Figyelmen kívül hagyja a NULL értékeket.
• COUNT(*): Megszámolja az összes sort az összes oszlopból.
• COUNT (DISTINCT x): Megadhat egy DISTINCT kulcsszót az x előtt, amely megkapja az x különböző értékeinek számát.

Példa

A következő példában megkapjuk a COUNT(Részlegazonosító), COUNT(*) és COUNT(DISTINCT DepartmentId) osztályok teljes számát, és miben különböznek egymástól:

SELECT COUNT(DepartmentId), COUNT(DISTINCT DepartmentId), COUNT(*) FROM Students;

Ez a következőket adja:

Az alábbiak szerint:

• A COUNT(Részlegazonosító) megadja az összes osztályazonosító számát, és figyelmen kívül hagyja a null értékeket.
• A COUNT(DISTINCT DepartmentId) az osztályazonosító különálló értékeit adja meg, amelyek csak 3. Melyik az osztálynév három különböző értéke. Figyeljük meg, hogy a tanszéknév 8 értéke van a hallgató nevében. De csak a három különböző érték, a matematika, az informatika és a fizika.
• A COUNT(*) megszámolja a tanulói táblázat azon sorait, amelyek 10 tanulóra vonatkoztatva 10 sor.

GROUP_CONCAT() – GROUP_CONCAT(X) vagy GROUP_CONCAT(X,Y)

A GROUP_CONCAT összesítő függvény a többszörös értékeket egyetlen értékké fűzi össze, és elválasztja őket egymástól. A következő lehetőségek állnak rendelkezésre:

• GROUP_CONCAT(X): Ez az összes x értéket egy karakterláncba fogja összefűzni, az értékek között elválasztóként használt „” vesszővel. A NULL értékek figyelmen kívül maradnak.
• GROUP_CONCAT(X, Y): Ez összefűzi az x értékeit egy karakterláncba úgy, hogy az y értéket használjuk elválasztóként az egyes értékek között az alapértelmezett ',' elválasztó helyett. A NULL értékek szintén figyelmen kívül maradnak.
• GROUP_CONCAT(DISTINCT X): Ez az x összes különálló értékét egy karakterláncba fogja összefűzni, a „” vesszővel az értékek elválasztójaként. A NULL értékek figyelmen kívül maradnak.

GROUP_CONCAT(Osztálynév) Példa

A következő lekérdezés a tanszéknév összes értékét a hallgatókból és a tanszékek táblájából egyetlen karakterláncba fogja összefűzni, vesszővel elválasztva. Tehát ahelyett, hogy egy értéklistát adna vissza, minden sorban egy értéket. Egy sorban csak egy értéket ad vissza, az összes értéket vesszővel elválasztva:

SELECT GROUP_CONCAT(d.DepartmentName)
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Ez a következőket adja:

Ez megadja a 8 részlegnév-értékek listáját egy karakterláncba fűzve, vesszővel elválasztva.

GROUP_CONCAT(DISTINCT Osztálynév) Példa

A következő lekérdezés összefűzi a tanszék nevének különböző értékeit a hallgatók és a tanszékek táblájából egy karakterláncba, vesszővel elválasztva:

SELECT GROUP_CONCAT(DISTINCT d.DepartmentName)
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Ez a következőket adja:

Figyelje meg, hogy az eredmény mennyiben különbözik az előző eredménytől; csak három értéket adott vissza, amelyek a különböző részlegek nevei, és az ismétlődő értékeket eltávolítottuk.

GROUP_CONCAT(Osztálynév ,'&') Példa

A következő lekérdezés a tanszéknév oszlop összes értékét a hallgatók és tanszékek táblázatából egyetlen karakterláncba fogja összefűzni, de elválasztóként vessző helyett „&” karaktert használ:

SELECT GROUP_CONCAT(d.DepartmentName, '&')
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Ez a következőket adja:

Figyelje meg, hogyan használja az „&” karaktert az alapértelmezett „,” karakter helyett az értékek elválasztására.

SQLite MAX() és MIN()

A MAX(X) a legmagasabb értéket adja vissza az X értékek közül. A MAX NULL értéket ad vissza, ha x minden értéke nulla. Míg a MIN(X) a legkisebb értéket adja vissza az X értékek közül. A MIN NULL értéket ad vissza, ha X minden értéke nulla.

Példa

A következő lekérdezésben a MIN és MAX függvényeket használjuk, hogy megkapjuk a legmagasabb és a legalacsonyabb pontokat a „Marks" asztal:

SELECT MAX(Mark), MIN(Mark) FROM Marks;

Ez a következőket adja:

SQLite SZUM(x), összesen(x)

Mindkettő az összes x érték összegét adja vissza. De a következőkben különböznek:

• A SUM nullát ad vissza, ha az összes érték nulla, de a Total értéke 0.
• A TOTAL mindig lebegőpontos értékeket ad vissza. A SUM egész értéket ad vissza, ha az összes x érték egész szám. Ha azonban az értékek nem egész számok, akkor lebegőpontos értéket ad vissza.

Példa

A következő lekérdezésben a SUM és total értékeket fogjuk használni, hogy megkapjuk a „Marks” táblázatok:

SELECT SUM(Mark), TOTAL(Mark) FROM Marks;

Ez a következőket adja:

Mint látható, a TOTAL mindig lebegőpontos értéket ad vissza. A SUM azonban egész számot ad vissza, mert a „Jelölés” oszlopban lévő értékek egész számban lehetnek.

A SUM és a TOTAL közötti különbség példa:

A következő lekérdezésben megmutatjuk a SUM és a TOTAL közötti különbséget, ha a NULL értékek SUM összegét kapják:

SELECT SUM(Mark), TOTAL(Mark) FROM Marks WHERE TestId = 4;

Ez a következőket adja:

Ne feledje, hogy a TestId = 4-hez nincsenek jelölések, ezért a teszthez null értékek tartoznak. A SUM üres értéket ad vissza, míg a TOTAL értéke 0.

Csoportosít

A GROUP BY záradék egy vagy több oszlop megadására szolgál, amelyek a sorok csoportokba csoportosítására szolgálnak. Az azonos értékű sorokat csoportokba gyűjti (rendezi).

Minden más oszlophoz, amely nem szerepel az oszlopok szerinti csoportban, használhat összesítő függvényt.

Példa:

A következő lekérdezés megmutatja az egyes tanszékeken jelen lévő hallgatók teljes számát.

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;

Ez a következőket adja:

A GROUPBY DepartmentName záradék az összes hallgatót csoportokba csoportosítja minden osztálynévhez. A „tanszék” minden csoportjára számítani fogja a tanulókat.

HAVING záradék

Ha szűrni szeretné a GROUP BY záradék által visszaadott csoportokat, akkor megadhat egy „HAVING” záradékot a GROUP BY kifejezés után. A kifejezés a csoportok szűrésére szolgál.

Példa

A következő lekérdezésben azokat a tanszakokat választjuk ki, amelyeken csak két hallgató van:

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;

Ez a következőket adja:

A HAVING COUNT(S.StudentId) = 2 záradék kiszűri a visszaadott csoportokat, és csak azokat a csoportokat adja vissza, amelyekben pontosan két diák szerepel. Nálunk a Művészeti tagozaton 2 hallgató van, így a kimenetben megjelenik.

SQLite Lekérdezés és segédlekérdezés

Bármely lekérdezésben használhat másik lekérdezést a SELECT, INSERT, DELETE, UPDATE vagy egy másik részlekérdezésben.

Ezt a beágyazott lekérdezést allekérdezésnek nevezik. Most látni fogunk néhány példát az allekérdezések használatára a SELECT záradékban. Az Adatok módosítása oktatóanyagban azonban látni fogjuk, hogyan használhatunk segédlekérdezéseket INSERT, DELETE és UPDATE utasításokkal.

Allekérdezés használata a FROM záradék példájában

A következő lekérdezésben egy segédlekérdezést fogunk beilleszteni a FROM záradékba:

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;

A lekérdezés:

   SELECT StudentId, Mark
   FROM Tests AS t
   INNER JOIN Marks AS m ON t.TestId = m.TestId

A fenti lekérdezést itt segédlekérdezésnek nevezzük, mert a FROM záradékba van beágyazva. Figyeljük meg, hogy „t” álnevet adtunk neki, hogy a lekérdezésben hivatkozhassunk az ebből visszaadott oszlopokra.

Ez a lekérdezés a következőket adja:

Tehát a mi esetünkben

• Az s.StudentName a fő lekérdezésből van kiválasztva, amely megadja a hallgatók nevét és
• t.Mark az allekérdezésből van kiválasztva; ez adja az egyes tanulók által szerzett pontszámokat

Allekérdezés használata a WHERE záradék példájában

A következő lekérdezésben a WHERE záradékban egy segédlekérdezést fogunk felvenni:

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId 
                  FROM Students AS s 
                  WHERE d.DepartmentId = s.DepartmentId);

A lekérdezés:

SELECT DepartmentId 
FROM Students AS s 
WHERE d.DepartmentId = s.DepartmentId

A fenti lekérdezést itt segédlekérdezésnek nevezzük, mert a WHERE záradékba van beágyazva. Az allekérdezés azokat a DepartmentId értékeket adja vissza, amelyeket a NOT EXISTS operátor fog használni.

Ez a lekérdezés a következőket adja:

A fenti lekérdezésben azt a tanszéket választottuk ki, amelyre nincs beiratkozott hallgató. Melyik itt a „Matek” tanszék.

Készlet Operaciók – UNION,Intersect

SQLite a következő SET műveleteket támogatja:

UNION & UNION ALL

A több SELECT utasításból visszaadott egy vagy több eredményhalmazt (sorok egy csoportját) egyesíti egy eredményhalmazba.

Az UNION különböző értékeket ad vissza. Az UNION ALL azonban nem fog és nem is fog tartalmazni ismétlődéseket.

Vegye figyelembe, hogy az oszlopnév az első SELECT utasításban megadott oszlopnév lesz.

UNION Példa

A következő példában ugyanabban az oszlopban kapjuk meg a DepartmentId listát a hallgatói táblából és a DepartmentId listáját a tanszéki táblából:

SELECT DepartmentId AS DepartmentIdUnioned FROM Students
UNION
SELECT DepartmentId FROM Departments;

Ez a következőket adja:

A lekérdezés csak 5 sort ad vissza, amelyek a különböző osztályazonosító értékek. Figyelje meg az első értéket, amely a null érték.

SQLite UNION ALL Példa

A következő példában ugyanabban az oszlopban kapjuk meg a DepartmentId listát a hallgatói táblából és a DepartmentId listáját a tanszéki táblából:

SELECT DepartmentId AS DepartmentIdUnioned FROM Students
UNION ALL
SELECT DepartmentId FROM Departments;

Ez a következőket adja:

A lekérdezés 14 sort ad vissza, 10 sort a hallgatói táblázatból és 4 sort a tanszéki táblázatból. Ne feledje, hogy a visszaadott értékek ismétlődnek. Azt is vegye figyelembe, hogy az oszlopnév megegyezett az első SELECT utasításban megadott névvel.

Most pedig nézzük meg, hogy az UNION all hogyan fog különböző eredményeket adni, ha az UNION ALL szót UNION-ra cseréljük:

SQLite METSZÉS

A mindkét kombinált eredménykészletben található értékeket adja vissza. A rendszer figyelmen kívül hagyja azokat az értékeket, amelyek a kombinált eredménykészletek valamelyikében léteznek.

Példa

A következő lekérdezésben kiválasztjuk azokat a DepartmentId értékeket, amelyek mind a Hallgatók, mind a Tanszékek táblában megtalálhatók a Tanszékazonosító oszlopban:

SELECT DepartmentId FROM Students
Intersect
SELECT DepartmentId FROM Departments;

Ez a következőket adja:

A lekérdezés csak három 1, 2 és 3 értéket ad vissza. Melyek azok az értékek, amelyek mindkét táblában megtalálhatók.

A null és 4 értékek azonban nem kerültek bele, mert a null érték csak a hallgatói táblában létezik, a tanszéki táblázatban nem. És a 4-es érték a tanszékek táblájában van, és nem a hallgatói táblázatban.

Ez az oka annak, hogy mind a NULL, mind a 4 értéket figyelmen kívül hagyták, és nem szerepelnek a visszaadott értékekben.

KIVÉVE

Tegyük fel, hogy ha két sorlistája van, a lista1 és a lista2, és csak az 1. listából szeretné azokat a sorokat, amelyek nem szerepelnek a list2-ben, használhatja az „EXCEPT” záradékot. Az EXCEPT záradék összehasonlítja a két listát, és azokat a sorokat adja vissza, amelyek léteznek a list1-ben és nem léteznek a list2-ban.

Példa

A következő lekérdezésben kiválasztjuk azokat a DepartmentId értékeket, amelyek a tanszéki táblában vannak, és nem léteznek a hallgatói táblában:

SELECT DepartmentId FROM Departments
EXCEPT
SELECT DepartmentId FROM Students;

Ez a következőket adja:

A lekérdezés csak a 4-es értéket adja vissza. Ez az egyetlen érték, amely létezik a részlegek táblájában, és nem létezik a tanulók táblájában.

NULL kezelés

AzNULL” érték egy speciális érték SQLite. Ismeretlen vagy hiányzó érték ábrázolására szolgál. Vegye figyelembe, hogy a null érték teljesen más, mint a "0” vagy üres „” értéket. Mivel a 0 és az üres érték ismert érték, a null érték azonban ismeretlen.

A NULL értékek speciális kezelést igényelnek SQLite, most meglátjuk, hogyan kell kezelni a NULL értékeket.

NULL értékek keresése

A normál egyenlőség operátor (=) nem használható a null értékek keresésére. Például a következő lekérdezés azokat a hallgatókat keresi, akiknél nulla DepartmentId érték van:

SELECT * FROM Students WHERE DepartmentId = NULL;

Ez a lekérdezés nem ad eredményt:

Mivel a NULL érték nem egyenlő semmilyen más értékkel, amely magában foglalja a null értéket, ezért nem adott vissza semmilyen eredményt.

• Ahhoz azonban, hogy a lekérdezés működjön, a "NULLA" operátort a null értékek kereséséhez a következők szerint:

SELECT * FROM Students WHERE DepartmentId IS NULL;

Ez a következőket adja:

A lekérdezés azokat a hallgatókat adja vissza, akiknek nulla DepartmentId értéke van.

• Ha azokat az értékeket szeretné megkapni, amelyek nem nullák, akkor a „NEM NULL” operátor így:

SELECT * FROM Students WHERE DepartmentId IS NOT NULL;

Ez a következőket adja:

A lekérdezés azokat a hallgatókat adja vissza, akiknek nincs NULL DepartmentId értéke.

Feltételes eredmények

Ha rendelkezik értéklistával, és bizonyos feltételek alapján bármelyiket ki szeretné választani közülük. Ehhez az adott érték feltételének igaznak kell lennie ahhoz, hogy kiválasztható legyen.

A CASE kifejezés kiértékeli ezeket a feltételeket az összes értékhez. Ha a feltétel igaz, akkor ezt az értéket adja vissza.

Például, ha van egy „Osztályzat” oszlopa, és az érdemjegy értéke alapján szeretne szöveges értéket kiválasztani a következők szerint:

– „Kiváló”, ha az osztályzat 85-nél magasabb.

– „Nagyon jó”, ha az osztályzat 70 és 85 között van.

– „Jó”, ha az osztályzat 60 és 70 között van.

Ezután használhatja a CASE kifejezést ehhez.

Ez felhasználható bizonyos logikák meghatározására a SELECT záradékban, így bizonyos feltételektől függően kiválaszthat bizonyos eredményeket, például az if utasítástól függően.

A CASE operátor különböző szintaxisokkal definiálható az alábbiak szerint:

1. Különféle feltételeket használhat:

CASE 
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  WHEN condition3 THEN result3
  …
  ELSE resultn
END

2. Vagy csak egy kifejezést használhat, és különböző lehetséges értékek közül választhat:

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  WHEN value3 THEN result3
  …
  ELSE restuln 
END

Vegye figyelembe, hogy az ELSE záradék nem kötelező.

Példa

A következő példában a CASE kifejezéssel NULL értéket a Tanulók táblázat Tanszékazonosító oszlopában, hogy a „Nincs részleg” szöveg jelenjen meg a következőképpen:

SELECT 
  StudentName,
  CASE 
    WHEN DepartmentId IS NULL THEN 'No Department'
    ELSE DepartmentId 
  END AS DepartmentId
FROM Students;

• A CASE operátor ellenőrzi a DepartmentId értékét, hogy az nulla-e vagy sem.
• Ha ez egy NULL érték, akkor a DepartmentId érték helyett a „No Department” szó szerinti értéket fogja kiválasztani.
• Ha nem null érték, akkor a DepartmentId oszlop értékét fogja kiválasztani.

Ez az alábbi kimenetet adja meg:

Általános táblázatos kifejezés

A közös táblakifejezések (CTE-k) olyan részlekérdezések, amelyek az SQL utasításon belül vannak megadva adott néven.

Előnye az allekérdezésekkel szemben, mert az SQL-utasításokból van meghatározva, és könnyebben olvashatóvá, karbantarthatóvá és megérthetővé teszi a lekérdezéseket.

Egy általános táblakifejezés úgy határozható meg, hogy a WITH záradékot a SELECT utasítások elé helyezzük a következőképpen:

WITH CTEname
AS
(
   SELECT statement
)
SELECT, UPDATE, INSERT, or update statement here FROM CTE

AzCTEname” bármilyen név, amit megadhat a CTE-nek, később hivatkozhat rá. Vegye figyelembe, hogy a CTE-ken megadhat SELECT, UPDATE, INSERT vagy DELETE utasítást.

Most lássunk egy példát a CTE használatára a SELECT záradékban.

Példa

A következő példában egy CTE-t fogunk definiálni egy SELECT utasításból, majd később egy másik lekérdezéshez használjuk:

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;

Ebben a lekérdezésben definiáltunk egy CTE-t, és a következő nevet adtuk nekiMinden osztály“. Ez a CTE egy SELECT lekérdezésből lett meghatározva:

SELECT DepartmentId, DepartmentName
  FROM Departments

Majd miután definiáltuk a CTE-t, felhasználtuk az utána következő SELECT lekérdezésben.

Vegye figyelembe, hogy a gyakori táblakifejezések nem befolyásolják a lekérdezés kimenetét. Ez egy logikai nézet vagy részlekérdezés meghatározásának módja annak érdekében, hogy újra felhasználhassák őket ugyanabban a lekérdezésben. A gyakori táblakifejezések olyanok, mint egy változó, amelyet Ön deklarál, és újra felhasználja segédlekérdezésként. Csak a SELECT utasítás befolyásolja a lekérdezés kimenetét.

Ez a lekérdezés a következőket adja:

Speciális lekérdezések

A speciális lekérdezések azok a lekérdezések, amelyek összetett összekapcsolásokat, részlekérdezéseket és bizonyos összesítéseket tartalmaznak. A következő részben láthatunk egy példát egy speciális lekérdezésre:

Ahol megkapjuk a

• A tanszékek nevei az egyes tanszékek összes hallgatójával
• A tanulók nevét vesszővel és vesszővel elválasztva
• Megmutatja, hogy a tanszék legalább három hallgatója van

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;

Hozzáadtuk a JOIN záradékot, hogy megkapja a DepartmentName értéket a Departments táblából. Ezt követően hozzáadtunk egy GROUP BY záradékot két összesítő funkcióval:

• „COUNT” az egyes osztálycsoportok tanulóinak megszámlálásához.
• GROUP_CONCAT az egyes csoportok tanulóinak összefűzéséhez egy karakterláncban elválasztott vesszővel.
• A GROUP BY után a HAVING záradékkal szűrtük a tanszékeket, és csak azokat a tanszékeket jelöljük ki, ahol legalább 3 hallgató van.

Az eredmény a következő lesz:

Összegzésként

Ez egy bevezetés volt az írásba SQLite lekérdezések és az adatbázis lekérdezésének alapjai, valamint a visszaadott adatok szűrésének módja. Most már írhatod a sajátodat SQLite lekérdezéseket.