SQL-lekérdezések SQLite-adatbázisba történő megírásához ismernie kell a SELECT, FROM, WHERE, GROUP BY, ORDER BY és LIMIT záradék működését és használatát.
A bemutató során megtanulja, hogyan kell használni ezeket a záradékokat, és hogyan kell írni az SQLite záradékokat.
Ebben az oktatóanyagban megtanulja-
- Adatok olvasása a Select gombbal
- Nevek és álnevek
- HOL
- Korlátozás és rendelés
- Ismétlődések eltávolítása
- Összesítve
- Csoportosít
- Lekérdezés és allekérdezés
- Műveletek beállítása -UNION, Metszés
- NULL kezelés
- Feltételes eredmények
- Közös tábla kifejezés
- Speciális lekérdezések
Adatok olvasása a Select gombbal
A SELECT záradék az a fő utasítás, amelyet az SQLite adatbázis lekérdezéséhez használ. A SELECT záradékban megadja, hogy mit kell kiválasztani. De a select záradék előtt nézzük meg, honnan választhatjuk ki az adatokat a FROM záradék segítségével.
A FROM záradék arra szolgál, hogy meghatározza, hová kívánja kiválasztani az adatokat. A from záradékban megadhat egy vagy több táblázatot vagy allekérdezést, amelyből kiválaszthatja az adatokat, amint azt később az oktatóanyagokon láthatjuk.
Vegye figyelembe, hogy a következő példák mindegyikéhez futtatnia kell az sqlite3.exe fájlt, és áramlásként meg kell nyitnia a kapcsolatot a minta adatbázissal:
1. lépés) Ebben a lépésben
- Nyissa meg a Sajátgép elemet, és keresse meg a következő könyvtárat: " C: \ sqlite " és
- Ezután nyissa meg az " sqlite3.exe " fájlt :
2. lépés: Nyissa meg a " TutorialsSampleDB.db " adatbázist 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 nemcsak egy oszlop nevét választhatja ki, hanem még sok más lehetőséget is megadhat, hogy mit válasszon. Mint a következő:
SELECT *
Ez a parancs kiválasztja az összes oszlopot a FROM záradék összes hivatkozott táblája (vagy allekérdezése) közül. Például:
SELECT *A hallgatóktólBELSŐ CSATLAKOZÁS Osztályok ON Students.DepartmentId = Departments.DepartmentId;
Ez kiválasztja az összes oszlopot mind a hallgatók, mind a tanszékek táblázataiból:
SELECT táblanév. *
Ez az összes oszlopot csak a "tablename" táblázatból választja ki. Például:
SELECT hallgatók. *A hallgatóktólBELSŐ CSATLAKOZÁS Osztályok ON Students.DepartmentId = Departments.DepartmentId;
Ez csak az összes oszlopot kiválasztja a diákok táblázatából:
Szó szerinti érték
A szó szerinti érték egy állandó érték, amelyet a select utasításban lehet megadni. A szó szerinti értékeket általában ugyanúgy használhatja, mint az oszlopneveket a SELECT záradékban. Ezek a szó szerinti értékek minden sorban megjelennek az SQL lekérdezés által visszaadott sorokból.
Íme néhány példa a választható szó szerinti értékekre:
- Numerikus literál - számok bármilyen formátumban, például 1, 2,55,… stb.
- String literálok - Bármely karakterlánc „USA”, „ez egy minta szöveg”, stb.
- NULL - NULL érték.
- Current_TIME - Megadja az aktuális időt.
- CURRENT_DATE - ez megadja az aktuális dátumot.
Ez hasznos lehet bizonyos esetekben, amikor állandó értéket kell választania az összes visszaadott sor számára. Például, ha az összes hallgatót ki szeretné választani a Diákok táblából, egy új oszlopnak, amelynek neve egy ország, amely az "USA" értéket tartalmazza, megteheti:
SELECT *, 'USA' AS Country FROM hallgatóktól;
Ez megadja a hallgatók összes oszlopát, valamint egy új "Ország" oszlopot:
Vegye figyelembe, hogy ez az új Ország oszlop valójában nem egy új oszlop, amelyet a táblához adtak. Ez egy virtuális oszlop, amelyet a lekérdezésben hoztak létre az eredmények megjelenítésére, és nem jön létre a táblán.
Nevek és álnevek
Az álnév az oszlop új neve, amely lehetővé teszi az új névvel ellátott oszlop kiválasztását. Az oszlopneveket az "AS" kulcsszóval adjuk meg.
Például, ha a "StudentName" helyett a "StudentName" mezővel kívánja visszaadni a StudentName oszlopot, akkor adhat neki egy ilyen álnevet:
SELECT StudentName AS 'Student Name' a hallgatóktól;
Ez megadja a hallgatók nevét a „Student Name” névvel a „StudentName” helyett, így:
Vegye figyelembe, hogy az oszlop neve továbbra is " StudentName "; a StudentName oszlop továbbra is ugyanaz, az alias nem változik.
Az álnév nem változtatja meg az oszlop nevét; csak megváltoztatja a megjelenítési nevet a SELECT záradékban.
Ne feledje, hogy az "AS" kulcsszó opcionális, az alias nevet nélküle is beírhatja:
SELECT StudentName 'Student Name' a hallgatóktól;
És pontosan ugyanazt a kimenetet adja meg, mint az előző lekérdezés:
A tábláknak álneveket is adhat, nem csak oszlopokat. Ugyanazzal a "AS" kulcsszóval. Megteheti például:
SELECT s. * A hallgatók közül;
Ez megadja a diákok táblázat összes oszlopát:
Ez nagyon hasznos lehet, ha több táblához csatlakozik; Ahelyett, hogy a lekérdezésben megismételné a táblázat teljes nevét, minden táblának rövid álnevet adhat. Például a következő lekérdezésben:
Válassza ki a Students.StudentName, Departments.DepartmentName elemetA hallgatóktólBELSŐ CSATLAKOZÁS Osztályok ON Students.DepartmentId = Departments.DepartmentId;
Ez a lekérdezés kiválasztja az egyes hallgatók nevét a "Diákok" táblázatból, tanszékeik nevét pedig a "Tanszékek" táblából:
Ugyanaz a lekérdezés így írható:
SELECT s.StudentName, d.DepartmentNameA hallgatóktól ASBelső Csatlakozás Osztályok AS d ON s.DepartmentId = d.DepartmentId;
- Megadtuk a hallgatói táblának egy álnevet "s", a tanszékeknek pedig egy álnevet "d".
- Aztán ahelyett, hogy a teljes tábla nevét használtuk volna, az álneveikkel hivatkoztunk rájuk.
- Az INNER JOIN két vagy több táblát köt össze feltétel használatával. Példánkban a Student táblához csatlakoztunk a DepartmentId oszlopos Departments táblához. Az "SQLite Joins" oktatóanyagban a Belső csatlakozás részletes ismertetése is található.
Ez megadja a pontos kimenetet, mint az előző lekérdezés:
HOL
Az SQL lekérdezések kizárólag SELECT záradékkal történő írásával, a FROM záradékkal együtt, amint azt az előző szakaszban láttuk, megkapja a táblák összes sorát. Ha azonban vissza akarja szűrni a visszaküldött adatokat, hozzá kell adnia egy "WHERE" záradékot.
A WHERE záradék az SQL lekérdezés által visszaadott eredménykészlet szűrésére szolgál. Így működik a WHERE záradék:
- A WHERE záradékban megadhat egy "kifejezést".
- Ezt a kifejezést kiértékelik a FROM záradékban megadott táblázat (ok) ból visszaadott minden egyes sor esetében.
- A kifejezést logikai kifejezésként értékelik, amelynek eredménye igaz, hamis vagy null.
- Ezután csak azok a sorok kerülnek visszaadásra, amelyeknél a kifejezést valós értékkel értékelték, és azokat, amelyek hamis vagy null eredményt kapnak, figyelmen kívül hagyják, és nem tartalmazzák az eredményhalmazban.
- A WHERE záradék használatával történő eredményszűréshez kifejezéseket és operátorokat kell használnia.
Az SQLite operátorainak listája és használatuk
A következő részben elmagyarázzuk, hogyan szűrhet a kifejezés és az operátorok segítségével.
A kifejezés egy vagy több szó szerinti érték vagy oszlop, kombinálva egy operátorral.
Ne feledje, hogy a SELECT és a WHERE záradékban egyaránt használhat kifejezéseket.
A következő példákban kipróbáljuk a kifejezéseket és az operátorokat mind a select, mind a WHERE záradékban. Annak érdekében, hogy megmutassam nekik, hogyan teljesítenek.
Különböző típusú kifejezések és operátorok adhatók meg, amelyeket az alábbiak szerint adhat meg:
SQLite a "||" összefűző operátor
Ezt az operátort egy vagy több szó szerinti érték vagy oszlop összefűzésére használják. Az összes összefűzött szó szerinti értékből vagy oszlopból egy eredménysor kerül előállításra. Például:
SELECT 'Id with Name:' || StudentId || StudentName AS StudentIdWithNameA hallgatóktól;
Ez összefűz egy új álnevet " StudentIdWithName ":
- Az " Id with Name: " szó szerinti karakterlánc értéke
- a " StudentId " oszlop értékével és
- a " StudentName " oszlop értékével
SQLite CAST operátor:
A CAST operátor arra szolgál, hogy egy adattípust egy másik adattípussá konvertáljon.
Például, ha van egy numerikus értéket tárolja, a string értéket, mint ez " '12 0,5' » és szeretné átalakítani, hogy egy numerikus érték akkor a CAST, hogy ezt megtehesse, mint ez« CAST ('12 0,5' AS IGAZ) ". Vagy ha van egy decimális értéke, például 12,5, és csak az egész számra van szüksége, akkor egész számra vetítheti, mint ez 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 megadja:
Az eredmény a következő:
- CAST ('12 .5 'AS REAL) - a '12 .5' érték egy string érték, amelyet REAL értékre konvertálunk.
- CAST (12.5 AS INTEGER) - a 12.5 érték tizedesérték, egész számra konvertálódik. A tizedes rész csonka lesz, és 12 lesz.
SQLite számtani operátorok:
Vegyen két vagy több numerikus szó szerinti értéket vagy numerikus oszlopot, és adjon meg egy numerikus értéket. Az SQLite által támogatott számtani operátorok a következők:
|
Példa:
A következő példában megpróbáljuk kipróbálni az öt számtani operátort, ugyanabban a szó szerinti számértékekkel
válasszon záradékot:
SELECT 25 + 6, 25-6, 25 * 6, 25% 6, 25/6;
Ez megadja:
Figyelje meg, hogyan használtunk itt egy SELECT utasítást FROM záradék nélkül. Ez pedig megengedett az SQLite-ben, amíg szó szerinti értékeket választunk.
SQLite Comparison operátorok
Hasonlítson össze két operandusot egymással, és adjon vissza igaz vagy hamis értéket az alábbiak szerint:
|
Vegye figyelembe, hogy az SQLite az igaz értéket 1-vel, a hamis értéket 0-val fejezi ki.
Példa:
SELECT10 <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 ad:
SQLite Pattern Matching operátorok
" LIKE " - a minták illesztésére szolgál. A " Tetszik " használatával olyan értékeket kereshet, amelyek megfelelnek a helyettesítő karakterrel megadott mintának.
A bal oldali operandus lehet karakterlánc szó szerinti értéke vagy karakterlánc oszlop. A minta a következőképpen határozható meg:
- Mintát tartalmaz. Például a StudentName LIKE „% a%” - ez a diákok nevét keresi, amelyek az „a” betűt tartalmazzák a StudentName oszlop tetszőleges helyén.
- A mintával kezdődik. Például: " StudentName LIKE 'a%" "- keresse meg a diákok nevét, amelyek" a "betűvel kezdődnek.
- Vége a mintával. Például: " StudentName LIKE '% a' " - Keresse meg az "a" betűvel végződő diákok nevét.
- A karakterláncban szereplő egyetlen karakter egyeztetése az aláhúzás "_" betűjével. Például: " StudentName LIKE 'J___' " - 4 karakter hosszú diáknevek keresése. A "J" betűvel kell kezdődnie, és a "J" betű után további három karakter is szerepelhet.
Mintaillesztési példák:
- A „j” betűvel kezdődő diákok neve:
Válassza ki a hallgatónevet a hallgatók közül, ahol a diáknév LIKE 'j%';
Eredmény:
- A diákok neve az y betűvel végződik:
Válassza ki a diáknevet a hallgatók közül, ahol a diáknév LIKE '% y';
Eredmény:
- Szerezzen be olyan diákok neveket, amelyek tartalmazzák az 'n' betűt:
Válassza ki a diáknevet a hallgatók közül, ahol a diáknév LIKE '% n%';
Eredmény:
A "GLOB" - egyenértékű a LIKE operátorral, de a GLOB kis- és nagybetűk között különbözik, 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 megadja:
- Az első utasítás 0-t (hamis) ad vissza, mert a GLOB operátor megkülönbözteti a kis- és nagybetűket, így a 'j' nem egyenlő a 'J' -vel. A második állítás azonban 1-t ad vissza (igaz), mert a LIKE operátor nem érzékeny a kis- és nagybetűkre, így a 'j' egyenlő a 'J'-vel.
Egyéb üzemeltetők:
SQLite ÉS
Egy vagy több kifejezést ötvöző logikai operátor. Csak akkor tér vissza igazra, ha az összes kifejezés "igaz" értéket ad. Csak akkor ad vissza hamis értéket, ha az összes kifejezés "hamis" értéket ad.
Példa:
A következő lekérdezés azon hallgatók után kutat, akiknek StudentId értéke> 5, a StudentName pedig N betűvel kezdődik, a visszaküldött hallgatóknak meg kell felelniük a két feltételnek:
SELECT *A hallgatóktólWHERE (StudentId> 5) ÉS (StudentName LIKE 'N%');
Kimenetként a fenti képernyőképen ez csak "Nancy" -t ad. Nancy az egyetlen hallgató, aki mindkét feltételnek megfelel.
SQLite VAGY
Egy logikai operátor, amely egy vagy több kifejezést kombinál, így ha az összesített operátorok közül az egyik igaz, akkor igaz lesz. Ha azonban az összes kifejezés hamis eredményt ad, akkor hamis értéket ad vissza.
Példa:
A következő lekérdezés olyan hallgatókra keres, akiknek StudentId> 5 vagy a StudentName N betűvel kezdődik, a visszaküldött hallgatóknak meg kell felelniük legalább az egyik feltételnek:
SELECT *A hallgatóktólWHERE (StudentId> 5) VAGY (StudentName LIKE 'N%');
Ez megadja:
Kimenetként a fenti képernyőképen megkapja annak a hallgatónak a nevét, amelynek nevében "n" betű van, plusz a> 5 értékű diák azonosítója.
Mint látható, az eredmény eltér az AND operátorral lekérdezettől.
SQLite KÖZÖTT
A KÖZÖTT azoknak az értékeknek a kiválasztására szolgál, amelyek két érték tartományán belül vannak. Például az " X Y és Z KÖZÖTT " értéke true (1) lesz, 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ű " X> = Y ÉS X <= Z " -vel, X-nek nagyobbnak vagy egyenlőnek kell lennie Y-vel és X-nek kisebb vagy egyenlő Z-vel.
Példa:
A következő példakérdezésben írunk egy lekérdezést, hogy megkapjuk az 5 és 8 közötti Id értékű diákokat:
SELECT *A hallgatóktólHOL 5. és 8. között hallgató volt;
Ez csak az 5., 6., 7. és 8. azonosítójú diákokat fogja megadni:
SQLite IN
Egy operandumot és egy operanduslistát vesz fel. Ez akkor tér vissza igazra, ha az első operandus értéke megegyezik a listán szereplő operandusok egyikének értékével. Az IN operátor visszaadja az true (1) értéket, ha az operandusok listája az első operandusértéket tartalmazza az értékein belül. Ellenkező esetben hamis (0) értéket ad vissza.
Így: " col IN (x, y, z) ". Ez egyenértékű a ((col = x) vagy (col = y) vagy (col = z) " értékkel .
Példa:
A következő lekérdezés csak a 2., 4., 6., 8. azonosítójú diákokat választja ki:
SELECT *A hallgatóktólWHERE StudentId IN (2, 4, 6, 8);
Mint ez:
Az előző lekérdezés a pontos eredményt adja meg a következő lekérdezésként, mivel ezek egyenértékűek:
SELECT *A hallgatóktólHOL (StudentId = 2) VAGY (StudentId = 4) VAGY (StudentId = 6) VAGY (StudentId = 8);
Mindkét lekérdezés megadja a pontos kimenetet. A két lekérdezés közötti különbség azonban az, hogy az első lekérdezést az "IN" operátornak használtuk. A második lekérdezésben több "OR" operátort használtunk.
Az IN operátor egyenértékű több OR operátor használatával. A " WHERE StudentId IN (2, 4, 6, 8) " egyenértékű a " WHERE (StudentId = 2) VAGY (StudentId = 4) VAGY (StudentId = 6) VAGY (StudentId = 8); "
Mint ez:
SQLite NEM IN
A "NOT IN" operandus az IN operátor ellentéte. De ugyanazzal a szintaxissal; egy operandus és egy operandus lista szükséges. Vissza fog állni, ha az első operandus értéke nem egyenlő a listán szereplő operandusok egyikének értékével. azaz igaz (0) értéket ad vissza, ha az operandusok listája nem tartalmazza az első operandust. Így: " col NOT IN (x, y, z) ". Ez egyenértékű az " (col <> x) ÉS (col <> y) AND (col <> z) " kifejezéssel.
Példa:
A következő lekérdezés azokat a tanulókat választja ki, akik azonos azonosítóval nem azonosak a 2., 4., 6., 8. azonosítóval:
SELECT *A hallgatóktólAHova a diák nem került be (2, 4, 6, 8);
Mint ez
Az előző lekérdezés a következő eredményként adja meg a pontos eredményt, mert ekvivalensek:
SELECT *A hallgatóktólHOL (StudentId <> 2) ÉS (StudentId <> 4) ÉS (StudentId <> 6) ÉS (StudentId <> 8);
Mint ez:
A fenti képernyőképen
Több, nem egyenlő operátort "<>" használtunk olyan hallgatók listájának összeállításához, amelyek nem egyenlőek a következő 2., 4., 6. és 8. azonosítóval. Ez a lekérdezés az összes többi hallgatót adja vissza, kivéve az Id-ek listáját.
SQLite LÉTEZIK
Az EXISTS operátorok nem vesznek operandusokat; csak egy SELECT záradékra van szükség utána. Az EXISTS operátor visszaadja az true (1) értéket, ha a SELECT záradékból visszatérnek sorok, és hamis (0) értéket ad vissza, ha egyáltalán nincsenek sorok a SELECT záradékból.
Példa:
A következő példában kiválasztjuk a tanszék nevét, ha a tanulói azonosító szerepel a hallgatók táblázatában:
SELECT DepartmentNameRészlegektől AS dHOL LÉTEZIK (KIVÁLASZTJA a DepartmentId-et a hallgatók közül, honnan d.DepartmentId = s.DepartmentId);
Ez megadja:
Csak a három részleget " IT, fizika és művészet " adják vissza. És a tanszék neve " Math " nem kerül visszaadásra, mert nincs tanuló abban a tanszéken, ezért a tanszék azonosítója nem szerepel a hallgatók táblázatában. Ezért az EXISTS operátor figyelmen kívül hagyta a " Math " osztályt.
SQLite NEM
Megfordítja az utána következő operátor eredményét. Például:
- NEM KÖZÖTT - Igaz lesz, ha a KÖZÖTT hamis értéket ad vissza, és fordítva.
- NEM LIKE - Igaz lesz, ha a LIKE hamis értéket ad vissza, és fordítva.
- NOT GLOB - Igaz lesz, ha a GLOB hamis értéket ad vissza, és fordítva.
- NEM LÉTEZIK - Igaz lesz, ha a LÉTEZIK hamisat ad vissza, és fordítva.
Példa:
A következő példában a NOT operátort az EXISTS operátorral fogjuk használni, hogy megkapjuk az osztályok nevét, amelyek nem szerepelnek a Student táblában, ami az EXISTS operátor fordított eredménye. Tehát a keresés a DepartmentId-n keresztül történik, amely nem szerepel az osztálytáblázatban.
SELECT DepartmentNameRészlegektől AS dAhol nem létezik (SELECT DepartmentIdA hallgatóktól ASHOL d.DepartmentId = s.DepartmentId);
Kimenet :
Csak a " Math " osztály kerül visszaadásra. Mivel a " Math " részleg az egyetlen tanszék, amely nem szerepel a hallgatók táblázatában.
Korlátozás és rendelés
SQLite rendelés
Az SQLite Order 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álnia az alábbiak szerint:
- Először meg kell adnia az ORDER BY záradékot.
- Az ORDER BY záradékot meg kell adni a lekérdezés végén; csak a LIMIT záradék adható meg utána.
- Adja meg a kifejezést az adatok rendezéséhez, ez lehet oszlopnév vagy kifejezés.
- A kifejezés után megadhat egy opcionális rendezési irányt. Vagy DESC, az adatok csökkenő sorrendjéhez, vagy ASC, hogy az adatok emelkedő sorrendjéhez rendeljenek. Ha egyiket sem adta meg, az adatok növekvő sorrendbe kerülnek.
- További kifejezéseket megadhat az egymás közötti "," használatával.
Példa
A következő példában az összes hallgatót a nevük szerint, de csökkenő sorrendben, majd a tanszék neve szerint növekvő sorrendben választjuk ki:
SELECT s.StudentName, d.DepartmentNameA hallgatóktól ASBelső Csatlakozás Osztályok AS d ON s.DepartmentId = d.DepartmentIdRENDELÉS d.DepartmentName ASC, s.StudentName DESC;
Ez megadja:
- Az SQLite először az összes hallgatót növekvő sorrendben rendezi a tanszék nevén
- Ezután minden tanszéknévnél az adott tanszék alatt az összes hallgató név szerint csökkenő sorrendben jelenik meg
SQLite Limit:
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 kiválaszthat egy meghatározott számú sort egy adott pozíciótól kezdve. Például a " LIMIT 4 OFFSET 4 " figyelmen kívül hagyja az első 4 sort, és az ötödik sortól kezdve 4 sort adott vissza, így az 5,6,7 és a 8 sort kapja.
Ne feledje, hogy az OFFSET záradék opcionális, beírhatja úgy, mint a " LIMIT 4, 4 ", és ez adja meg a pontos eredményt.
Példa :
A következő példában csak 3 diákot adunk vissza az 5. diák azonosítótól kezdve a lekérdezéssel:
KIVÁLASZT * A hallgatók közül 4,3;
Ez csak három diákot kap az 5. sortól kezdve. Tehát megkapja a StudentId 5, 6 és 7 sorokat:
Ismétlődések eltávolítása
Ha az SQL lekérdezése megismétli az értékeket, akkor a " DISTINCT " kulcsszóval eltávolíthatja ezeket az ismétlődéseket és visszatérhet a különálló értékekre. Több oszlopot is megadhat a DISTINCT billentyű működése után.
Példa:
A következő lekérdezés megismétli az "osztálynévértékeket": Itt duplikált értékek vannak az IT, a fizika és a művészetek nevekkel.
SELECT d.DepartmentNameA hallgatóktól ASBelső Csatlakozás Osztályok AS d ON s.DepartmentId = d.DepartmentId;
Ez megadja az osztály neve duplikált értékeit:
Figyelje meg, hogyan vannak duplikált értékek az osztály nevéhez. Most a DISTINCT kulcsszót használjuk ugyanazzal a lekérdezéssel, hogy eltávolítsuk ezeket a duplikátumokat, és csak egyedi értékeket kapjunk. Mint ez:
SELECT DISTINCT d.DepartmentNameA hallgatóktól ASBelső Csatlakozás Osztályok AS d ON s.DepartmentId = d.DepartmentId;
Ez csak három egyedi értéket ad meg a részleg neve oszlopnak:
Összesítve
Az SQLite aggregátumok az SQLite-ben definiált beépített függvények, amelyek több sor több értékét egy értékbe csoportosítják.
Itt vannak az SQLite által támogatott összesítések:
SQLite AVG ()
Visszaadta az összes x érték átlagát.
Példa:
A következő példában megkapjuk a hallgatók átlagértékét az összes vizsgán:
AVG (Mark) kiválasztása a jelölésekből;
Ezzel megkapja a "18,375" értéket:
Ezek az eredmények az összes jelérték összegezéséből származnak, elosztva a számukkal.
COUNT () - COUNT (X) vagy COUNT (*)
Visszaadja az x érték megjelenésének számát. És itt van néhány lehetőség, amelyet a COUNT alkalmazással használhat:
- COUNT (x): Csak x értéket számol, ahol x oszlopnév. A NULL értékeket figyelmen kívül hagyja.
- COUNT (*): Számolja meg 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önálló értékeinek számát.
Példa
A következő példában megkapjuk a COUNT (DepartmentId), a COUNT (*) és a COUNT (DISTINCT DepartmentId) osztályokkal rendelkező részlegek teljes számát, és azok különbségeit:
SELECT COUNT (DepartmentId), COUNT (DISTINCT DepartmentId), COUNT (*) FROM hallgatóktól;
Ez megadja:
Mint a következő:
- A COUNT (DepartmentId) megadja az összes osztályazonosító számát, és figyelmen kívül hagyja a null értékeket.
- A COUNT (DISTINCT DepartmentId) megadja a DepartmentId különálló értékeit, amelyek csak 3. A részlegnév három különböző értéke. Figyelje meg, hogy a tanuló nevében 8 osztály osztálynév szerepel. De csak a különböző három érték, amelyek a matematika, az informatika és a fizika.
- A COUNT (*) megszámolja a diákok táblázatának azon sorait, amelyek 10 diák 10 sora.
GROUP_CONCAT () - GROUP_CONCAT (X) vagy GROUP_CONCAT (X, Y)
A GROUP_CONCAT összesített függvény a többszörös értékeket vesszővel összefűzi egy értékre vesszővel. A következő lehetőségek állnak rendelkezésre:
- GROUP_CONCAT (X): Ez összefűzi az x értékét egy karaktersorozatba, a "" vesszővel, amelyet elválasztóként használnak az értékek között. A NULL értékeket a rendszer figyelmen kívül hagyja.
- GROUP_CONCAT (X, Y): Ez összefűzi az x értékeit egy karaktersorozatba, az y értékét elválasztóként használva az egyes értékek között az alapértelmezett elválasztó helyett ','. A NULL értékeket szintén figyelmen kívül hagyjuk.
- GROUP_CONCAT (DISTINCT X): Ez összefűzi az x összes különálló értékét egy karaktersorozatba, a "" vesszővel, amelyet elválasztóként használnak az értékek között. A NULL értékeket a rendszer figyelmen kívül hagyja.
GROUP_CONCAT (DepartmentName) példa
A következő lekérdezés összefűzi a tanszék nevének összes értékét a hallgatóktól és a tanszéktáblázattól, egy vesszővel elválasztva. Tehát az értékek listájának visszaadása helyett minden sorban egy értéket kell megadni. Csak egy értéket ad vissza egy sorban, az összes vesszővel elválasztva:
SELECT GROUP_CONCAT (d.DepartmentName)A hallgatóktól ASBelső Csatlakozás Osztályok AS d ON s.DepartmentId = d.DepartmentId;
Ez megadja:
Ezzel megkapja a 8 részleg nevének értékét, amelyek egy vesszővel elválasztva vannak összefűzve.
GROUP_CONCAT (DISTINCT osztálynév) példa
A következő lekérdezés összefűzi a tanszék nevének különálló értékeit a hallgatók és az osztályok táblázatából, egy vesszővel elválasztva:
SELECT GROUP_CONCAT (DISTINCT d.DepartmentName)A hallgatóktól ASBelső Csatlakozás Osztályok AS d ON s.DepartmentId = d.DepartmentId;
Ez megadja:
Figyelje meg, hogy az eredmény eltér az előző eredménytől; csak három értéket adott vissza, amelyek a külön osztályok nevei, és az ismétlődő értékeket eltávolítottuk.
GROUP_CONCAT (DepartmentName, '&') Példa
A következő lekérdezés összefűzi a tanszéknév oszlop összes értékét a hallgatók és a tanszékek táblázatából egy karaktersorozatba, de vessző helyett elválasztóként a & & karaktert írja be:
SELECT GROUP_CONCAT (d.DepartmentName, '&')A hallgatóktól ASBelső Csatlakozás Osztályok AS d ON s.DepartmentId = d.DepartmentId;
Ez megadja:
Figyelje meg, hogyan használják az "&" karaktert az alapértelmezett "," karakter helyett az értékek elválasztására.
SQLite MAX () és MIN ()
A MAX (X) az X értékek közül a legmagasabb értéket adja vissza. A MAX NULL értéket ad vissza, ha az x összes értéke nulla. Míg a MIN (X) a legkisebb értéket adja vissza az X értékekből. A MIN NULL értéket ad vissza, ha az X összes értéke nulla.
Példa
A következő lekérdezésben a MIN és a MAX funkciókat használjuk a legmagasabb és a legalacsonyabb pontszám megszerzéséhez a " Jelölések " táblából:
SELECT MAX (Mark), MIN (Mark) FROM Marks;
Ez megadja:
SQLite SUM (x), összesen (x)
Mindkettő visszaadja az összes x érték összegét. De a következőkben különböznek egymástól:
- A SUM null értéket ad vissza, ha az összes érték null, de a Total értéke 0.
- A TOTAL mindig lebegőpontos értékeket ad vissza. A SUM egész számot ad vissza, ha az összes x érték egész szám. Ha azonban az értékek nem egészek, akkor lebegőpontos értéket ad vissza.
Példa
A következő lekérdezésben a SUM és a total függvényt fogjuk használni a " Jelek " táblák összes jegyének összegéhez :
SELECT SUM (Mark), TOTAL (Mark) FROM Marks;
Ez megadja:
Amint láthatja, a TOTAL mindig lebegőpontot ad vissza. De a SUM egész számot ad vissza, mert a "Mark" oszlopban szereplő értékek egész számokban lehetnek.
Különbség SUM és TOTAL között:
A következő lekérdezésben megmutatjuk a különbséget SUM és TOTAL között, amikor megkapják a NULL értékek összegét:
SELECT SUM (jelölés), TOTAL (megjelölés) a jelölések közül WHERE TestId = 4;
Ez megadja:
Ne feledje, hogy a TestId = 4 esetében nincsenek jelölések, ezért a teszt nullértékekkel rendelkezik. 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űjtjük (rendezzük).
Bármely más oszlop esetében, amely nem szerepel oszloponként a csoportban, használhat hozzá összesítő függvényt.
Példa:
A következő lekérdezés megadja az egyes tanszékeken jelen lévő hallgatók teljes számát.
SELECT d.DepartmentName, COUNT (s.StudentId) AS StudentsCountA hallgatóktól ASBelső Csatlakozás Osztályok AS d ON s.DepartmentId = d.DepartmentIdCSOPORT d. DepartmentName;
Ez megadja:
A GROUPBY DepartmentName záradék az összes hallgatót minden tanszék nevéhez egy-egy csoportba csoportosítja. Minden "tanszék" csoportnál a hallgatók számítanak rá.
Rendelkezik záradékkal
Ha a GROUP BY záradék által visszaadott csoportokat szeretné szűrni, akkor a GROUP BY után megadhat egy "HAVING" záradékot kifejezéssel. A kifejezést e csoportok szűrésére használják.
Példa
A következő lekérdezésben azokat az osztályokat választjuk ki, amelyeken csak két hallgató van:
SELECT d.DepartmentName, COUNT (s.StudentId) AS StudentsCountA hallgatóktól ASBelső Csatlakozás Osztályok AS d ON s.DepartmentId = d.DepartmentIdCSOPORT d. DepartmentNameSZÁMA (s.StudentId) = 2;
Ez megadja:
A HAVING COUNT (S.StudentId) = 2 záradék kiszűri a visszaküldött csoportokat, és csak azokat a csoportokat adja vissza, amelyek pontosan két diákot tartalmaznak. Esetünkben a Művészeti Tanszéknek 2 hallgatója van, így ez megjelenik a kimenetben.
SQLite Query & Subquery
Bármely lekérdezésen belül használhat másik lekérdezést a SELECT, INSERT, DELETE, UPDATE vagy egy másik lekérdezés belsejében.
Ezt a beágyazott lekérdezést allekérdezésnek hívják. Látunk néhány példát az alkérdezések használatára a SELECT záradékban. Az Adatok módosítása oktatóanyagban azonban megtudhatjuk, hogyan használhatjuk az alkérdezéseket INSERT, DELETE és UPDATE utasítással.
Allekérdezés használata a FROM záradék példában
A következő lekérdezésben egy allekérdezést fogunk beilleszteni a FROM záradékba:
SELECTs.StudentName, t.MarkA hallgatóktól ASBELSŐ ÖSSZEKAPCSOLÁS(SELECT StudentId, MarkA tesztektől AS tBELSŐ CSATLAKOZÁS Jelölések m ON t.TestId = m.TestId) ON s.StudentId = t.StudentId;
A lekérdezés:
SELECT StudentId, MarkA tesztektől AS tBELSŐ CSATLAKOZÁS Jelölések m ON t.TestId = m.TestId
A fenti lekérdezést itt subquery-nek hívjuk, mert a FROM záradékba van ágyazva. Figyelje meg, hogy "t" álnevet adtunk neki, hogy a lekérdezésben tőle visszaküldött oszlopokra hivatkozhassunk.
Ez a lekérdezés megadja:
Tehát a mi esetünkben
- 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; ami e diákok által megszerzett jegyeket ad
Allekérdezés használata a WHERE záradék példában
A következő lekérdezésben a WHERE záradékba belefoglalunk egy allekérdezést:
SELECT DepartmentNameRészlegektől AS dAhol nem létezik (SELECT DepartmentIdA hallgatóktól ASHOL d.DepartmentId = s.DepartmentId);
A lekérdezés:
SELECT DepartmentIdA hallgatóktól ASHOL d.DepartmentId = s.DepartmentId
A fenti lekérdezést itt subquery-nek hívják, mert be van ágyazva a WHERE záradékba. Az allekérdezés visszaadja a DepartmentId értékeket, amelyeket a NEM LÉTEZETT operátor fog használni.
Ez a lekérdezés megadja:
A fenti lekérdezésben kiválasztottuk azt az osztályt, amelybe egyetlen hallgató sem iratkozott be. Ami itt a "matematika" részleg.
Műveletek beállítása - UNION, Metszéspont
Az SQLite a következő SET műveleteket támogatja:
UNION & UNION ALL
A több SELECT utasításból visszaküldött egy vagy több eredményhalmazt (sorok csoportját) egy eredményhalmazba egyesíti.
Az UNION különböző értékeket ad vissza. Az UNION ALL azonban nem fog, és tartalmazni fog duplikátumokat.
Vegye figyelembe, hogy az oszlop neve az első SELECT utasításban megadott oszlop neve lesz.
UNIÓ példa
A következő példában megkapjuk a DepartmentId listáját a hallgatók táblázatából, és a DepartmentId listáját az ugyanabban az oszlopban található tanszékek táblázatból:
SELECT DepartmentId AS DepartmentIdUnioned FROM hallgatóktólUNIÓSELECT DepartmentId FROM Departments;
Ez megadja:
A lekérdezés csak 5 sort ad vissza, amelyek a különálló 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 megkapjuk a DepartmentId listáját a hallgatók táblázatából, és a DepartmentId listáját az ugyanabban az oszlopban található tanszékek táblázatból:
SELECT DepartmentId AS DepartmentIdUnioned FROM hallgatóktólUNIÓ MINDENSELECT DepartmentId FROM Departments;
Ez megadja:
A lekérdezés 14 sort, 10 sort ad vissza a hallgatók táblájából és 4 sort a tanszékek táblájából. Vegye figyelembe, hogy a visszaadott értékekben vannak duplikátumok. Vegye figyelembe azt is, hogy az oszlop neve megegyezett az első SELECT utasításban.
Most nézzük meg, hogy az UNION hogyan fog különböző eredményeket elérni, ha az UNION ALL-et az UNION-ra cseréljük:
SQLite INTERSECT
Visszaadja az értékeket mindkét kombinált eredményhalmazban. A kombinált eredménykészletek egyikében szereplő értékeket a rendszer figyelmen kívül hagyja.
Példa
A következő lekérdezésben kiválasztjuk a DepartmentId értékeket, amelyek a DepartmentId oszlop diákjai és tanszékei táblázatokban egyaránt megtalálhatók:
KIVÁLASZTÁSI OSZTÁLY A hallgatók közülMetsziSELECT DepartmentId FROM Departments;
Ez megadja:
A lekérdezés csak három értéket ad vissza: 1, 2 és 3. Ezek az értékek léteznek mindkét táblázatban.
Azonban a null és a 4 értékeket nem vették fel, mert a null érték csak a diákok táblájában létezik, a tanszékeken nem. A 4. érték pedig a tanszékek táblázatában található, és nem a diákok táblázatában.
Ezért a NULL és a 4 értékeket egyaránt figyelmen kívül hagyták, és nem vették figyelembe a visszaadott értékekben.
KIVÉVE
Tegyük fel, hogy ha két sorlistád van, list1 és list2, és csak a list1-ből akarod a sorokat, amelyek nem léteznek a list2-ben, használhatod az "EXCEPT" záradékot. Az EXCEPT záradék összehasonlítja a két listát, és visszaadja azokat a sorokat, amelyek léteznek a list1-ben, és amelyek nem léteznek a list2-ben.
Példa
A következő lekérdezésben kiválasztjuk a DepartmentId értékeket, amelyek léteznek a részlegek táblázatban, és amelyek nem szerepelnek a hallgatók táblázatában:
Válassza ki a DepartmentId-t az osztályokbólKIVÉVEKIVÁLASZTÁSI OSZTÁLY A hallgatók közül
Ez megadja:
A lekérdezés csak a 4. értéket adja vissza. Ez az egyetlen érték, amely a részlegek táblázatban létezik, és a diákok táblázatban nem létezik.
NULL kezelés
A " NULL " érték az SQLite speciális értéke. Ismeretlen vagy hiányzó érték képviseletére szolgál. Vegye figyelembe, hogy a null érték teljesen eltér a " 0 " vagy az üres "" értéktől. 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 az SQLite-ben, most meglátjuk, hogyan kell kezelni a NULL értékeket.
NULL értékek keresése
Nem használhatja a normál egyenlőség operátort (=) a null értékek keresésére. Például a következő lekérdezés azon hallgatók után kutat, akiknek null a DepartmentId értéke:
KIVÁLASZTÁS * FROM hallgatók közül, ahol 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 foglal egy null értéket, ezért nem adott eredményt.
- Ahhoz azonban, hogy a lekérdezés működőképes legyen, az "IS NULL" operátorral kell null értékeket keresnie a következők szerint:
KIVÁLASZT * A hallgatók közül, ahol a tanszék NULL;
Ez megadja:
A lekérdezés azokat a hallgatókat adja vissza, akiknek null a DepartmentId értéke.
- Ha meg szeretné kapni azokat az értékeket, amelyek nem nullák, akkor a " NEM NULL " operátort kell használnia, mint ez:
KIVÁLASZT * A hallgatók közül, ahol az osztály nem teljes;
Ez megadja:
A lekérdezés azokat a tanulókat adja vissza, akiknek nincs NULL DepartmentId értéke.
Feltételes eredmények
Ha van értéklistája, és valamely feltétel alapján valamelyiket ki szeretné választani. Ehhez az adott érték feltételének igaznak kell lennie a kiválasztáshoz.
A CASE kifejezés kiértékeli a feltételek ezen listáját az összes értékre vonatkozóan. Ha a feltétel igaz, akkor ezt az értéket adja vissza.
Például, ha van egy "Osztályozás" oszlop, és az alábbiak szerint szeretne egy szöveges értéket kiválasztani az osztályzat értéke alapján:
- "Kiváló", ha az osztályzat magasabb, mint 85.
- "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 a CASE kifejezést használhatja erre.
Ez felhasználható bizonyos logika definiálá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ás.
A CASE operátor a következő szintaxisokkal határozható meg:
- Különböző feltételeket használhat:
ÜGYMIKOR feltétel1 AKKOR eredmény1MIKOR feltétel2, akkor eredmény2MIKOR 3. feltétel, AKKOR eredmény3… MÁS eredményVÉGE
- Vagy csak egy kifejezést használhat, és különböző lehetséges értékeket választhat:
CASE kifejezésMIKOR érték1 AKKOR eredmény1MIKOR érték2, akkor eredmény2MIKOR érték3 AKKOR eredmény3 ... MÁS újra megfogalmazvaVÉGE
Vegye figyelembe, hogy az ELSE záradék opcionális.
Példa
A következő példában a NULL értékű CASE kifejezést használjuk a Diákok tábla osztályazonosító oszlopában a „Nincs tanszék” szöveg megjelenítéséhez:
SELECTTanuló név,ÜGYAMIKOR A tanszék NULL, akkor "Nincs részleg"ELSE OsztályEND AS DepartmentIdA hallgatóktól;
- A CASE operátor ellenőrzi a DepartmentId értékét, hogy az null-e vagy sem.
- Ha NULL érték, akkor a DepartmentId érték helyett a „Nincs osztály” szó szerinti értéket választja.
- Ha nem nullérték, akkor kiválasztja a DepartmentId oszlop értékét.
Ez megadja az alábbi kimenetet:
Közös tábla kifejezés
A közös tábla kifejezések (CTE-k) olyan lekérdezések, amelyeket az SQL utasításban adott névvel definiálnak.
Előnye van az allekérdezésekkel szemben, mivel az SQL utasításokból van meghatározva, és megkönnyíti a lekérdezések olvashatóságát, karbantartását és megértését.
Közös tábla kifejezés meghatározható úgy, hogy a WITH záradékot a SELECT utasítások elé helyezzük a következőképpen:
CTEname-nelMINT(SELECT utasítás)Itt válassza ki, frissítse, illessze be vagy frissítse a nyilatkozatot a CTE-től
A " CTEname " bármilyen név, amelyet megadhat a CTE-hez, használhatja arra, hogy később hivatkozhasson rá. Ne feledje, hogy meghatározhatja a SELECT, az UPDATE, az INSERT vagy a DELETE utasítást a CTE-ken
Most nézzünk meg egy példát a CTE használatára a SELECT záradékban.
Példa
A következő példában meghatározunk egy CTE-t egy SELECT utasításból, majd később felhasználjuk egy másik lekérdezésnél:
Minden részleggelMINT(SELECT DepartmentId, DepartmentNameOsztályoktól)SELECTs.StudentId,s.StudentName,a.DepartmentNameA hallgatóktól ASINNER JOIN AllDepartments AS a ON s.DepartmentId = a.DepartmentId;
Ebben a lekérdezésben meghatároztunk egy CTE-t és " AllDepartments " nevet adtunk neki . Ezt a CTE-t egy SELECT lekérdezés definiálta:
SELECT DepartmentId, DepartmentNameOsztályoktól
Aztán miután meghatároztuk a CTE-t, az utána következő SELECT lekérdezésben használtuk.
Vegye figyelembe, hogy a Közös tábla kifejezések nem befolyásolják a lekérdezés kimenetét. Ez egy módja annak, hogy meghatározzon egy logikai nézetet vagy allekérdezést annak érdekében, hogy ugyanazon lekérdezésben újra felhasználhassa őket. A közös tábla kifejezések olyanok, mint egy változó, amelyet deklarál, és újra felhasználja allekérdezésként. Csak a SELECT utasítás befolyásolja a lekérdezés kimenetét.
Ez a lekérdezés megadja:
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 néhány összesítést tartalmaznak. A következő részben egy speciális lekérdezés példáját láthatjuk:
Hol kapjuk,
- Tanszék neve az összes tanuló hallgatóival
- A tanulók neve vesszővel elválasztva
- A tanszéken legalább három hallgató jelenléte
SELECTd.DepartmentName,COUNT (s.StudentId) StudentsCount,GROUP_CONCAT (StudentName) AS hallgatókRészlegektől AS dBELSŐ CSATLAKOZÁS A hallgatók, ha s ONDepartmentId = d.DepartmentIdCSOPORT d.DepartmentNameSZÁMA (s.StudentId)> = 3;
Hozzáadtunk egy JOIN záradékot a DepartmentName lekéréséhez a Departments táblából. Ezután hozzáadtunk egy GROUP BY záradékot két összesített függvénnyel:
- "COUNT" az egyes tanszékcsoportok hallgatóinak számlálásához.
- GROUP_CONCAT az egyes csoportok tanulóinak összefűzéséhez vesszővel, egy sztringben elválasztva.
- A GROUP BY után a HAVING záradékot használtuk az osztályok szűrésére, és csak azokat a tanszékeket választottuk ki, amelyek legalább 3 hallgatóval rendelkeznek.
Az eredmény a következő lesz:
Összegzés:
Ez bevezető volt az SQLite lekérdezések megírásához és az adatbázis lekérdezésének alapjaihoz, valamint a visszaküldött adatok szűrésének módjához. Most megírhatja saját SQLite lekérdezéseit.