Beágyazott és csatolt allekérdezések SQL-ben, EXISTS predikátum. Az EXISTS operátor használata Lekérdezések a létező függvény használatával

AHOL LÉTEZIK

A részlekérdezés egy vagy több sor megléte szempontjából ellenőrzésre kerül. Ha legalább egy sor egyezik a lekérdezéssel, akkor a rendszer a TRUE logikai értéket adja vissza. Az opcionális NOT kulcsszó megadásakor a TRUE logikai értéket adja vissza, ha az allekérdezés nem ad vissza egyező sort.

részlekérdezés

A teljesen kialakított segédlekérdezés alapján az eredményül kapott adatkészlet lekérésre kerül.

Általános szabályok

Az EXISTS operátor teszteli egy vagy több sor meglétét egy szülőlekérdezés egy részlekérdezésében.

SELECT * FROM jobs WHERE NOT EXISTS (SELECT * FROM alkalmazott WHERE jobs.job_id=alkalmazott.job_id);

Ez a példa a rekordok segédlekérdezését a további NOT kulcsszó használatával ellenőrzi. A következő példa adott rekordokat keres egy részlekérdezésben a fő eredménykészlet lekéréséhez.

SELECT au_lname FROM authors WHERE EXISTS (SELECT * FROM publishers WHERE authors.city=publishers.city);

Ez a lekérdezés azoknak a szerzőknek a vezetéknevét adja vissza (au_lname), akik ugyanabban a városban élnek, mint a kiadók. Vegye figyelembe, hogy használhat csillagot az allekérdezésben, mert az allekérdezésnek csak egy rekordot kell visszaadnia TRUE logikai értékkel. Ilyen esetekben az oszlopok nem számítanak. A kulcspont a karakterlánc létezése.

Sok lekérdezésben az EXISTS operátor ugyanazt a funkciót hajtja végre, mint az ANY. Az EXISTS operátor általában akkor a leghatékonyabb, ha korrelált lekérdezésekkel használják.

Az EXISTS operátor szemantikailag egyenértékű az ANY operátorral.

Az EXISTS utasításban szereplő segédlekérdezés általában kétféle keresés egyikét hajtja végre. Az első lehetőség egy helyettesítő karakter – egy csillag (például SELECT * FROM...) használata, ebben az esetben nem kér le egyetlen oszlopot vagy értéket sem. A csillag itt "bármely oszlopot" jelent. A második lehetőség az, hogy csak egy adott oszlopot jelöl ki az allekérdezésben (például SELECT aujd FROM). Egyes egyedi platformok több oszlopban is engedélyezik az allekérdezéseket (pl. SELECT aujd, aujname FROM...). Ez a funkció azonban ritka, és kerülni kell olyan kódban, amelyet más platformokra kell átvinni.

Platformok közötti különbségek

Minden platform támogatja az EXISTS operátort a fent leírt formában.

„Korábban könnyebb volt” – gondoltam, miközben leültem, hogy optimalizáljam a következő lekérdezést az SQL-ben vezetői stúdió. Amikor MySQL alatt írtam, minden sokkal egyszerűbb volt – vagy működik, vagy nem. Vagy lelassul, vagy nem. Az elmagyarázás minden problémámat megoldott, semmi több nem kellett. Most van egy hatékony környezetem a lekérdezések és eljárások/függvények fejlesztésére, hibakeresésére és optimalizálására, és ez a sok zűr csak további problémákat okoz szerintem. És miért mind? Mert a beépített lekérdezésoptimalizáló gonosz. Ha MySQL-ben és PostgreSQL-ben írok

Válassza ki a *-ot a, b, c közül, ahol a.id = b.id, b.id = c.id

és mindegyik táblagépen legalább 5 ezer sor lesz – minden lefagy. És hála Istennek! Mert egyébként a fejlesztőben jó esetben lustaság alakul ki a helyes íráshoz, rosszabb esetben pedig egyáltalán nem érti, mit csinál! Végül is ugyanaz a lekérdezés az MSSQL-ben hasonlóan fog működni

Válassza ki a *-ot a b csatlakozásból a.id = b.id csatlakozásból c a b.id = c.id-n

A beépített optimalizáló átfésüli a redundáns kérést, és minden rendben lesz.

Azt is maga dönti el, hogy mi a jobb: létezni vagy csatlakozni és még sok más. És minden a lehető legoptimálisabban fog működni.

Csak egy DE van. Egy ponton az optimalizáló megbotlik összetett lekérdezésés elmúlik, és akkor kap egy hatalmas problémát. És lehet, hogy nem azonnal kapja meg, hanem amikor az asztalok súlya eléri a kritikus tömeget.

Tehát itt a cikk lényege. létezik, és nagyon nehéz műveleteket végeznek. Ez valójában egy külön segédlekérdezés az egyes eredménysorok. És ha fészkelő is van, akkor általában leoltják a villanyt. Minden rendben lesz, ha 1, 10, 50 sor érkezik vissza. Nem fogja érezni a különbséget, és a csatlakozás talán még lassabb lesz. De amikor az 500-at kihúzzák, akkor kezdődnek a problémák. 500 részlekérdezés egy kérésben komoly.

Emberi megértés szempontjából ugyan jobb az in és a létezik, de az 50+ sort visszaadó lekérdezések időköltsége szempontjából nem elfogadhatóak.

Le kell foglalni, hogy természetesen ha valahol csökken, akkor valahova meg kell érkeznie. Igen, a csatlakozás memóriaigényesebb, mert a teljes értéktáblázat egyidejű megtartása és a vele való művelet költségesebb, mint az egyes sorokhoz tartozó segédlekérdezések futtatása, ami gyorsan felszabadítja a memóriát. Konkrétan meg kell nézni a kérést, és meg kell mérni, hogy a többletmemória felhasználása az idő kedvéért kritikus lesz-e vagy sem.

Példákat hozok a teljes analógiákra. Általánosságban elmondható, hogy még nem találkoztam olyan bonyolultságú lekérdezésekkel, amelyeket ne lehetne csatlakozások kaszkádjává bővíteni. Eltarthat egy napig, de minden kiderülhet.

Válassza ki a *-ot a-ból, ahol a.id in (az azonosító kiválasztása b-ből) válassza ki a *-ot az a-ból, ahol létezik (válasszon felső 1 1-et b-ből, ahol b.id = a.id) válassza ki a *-ot a b csatlakozásból az a.id = b-n. id válassza ki a *-ot a-ból ahol a.id nincs benne (az id-t b-ből válassza ki) válassza ki a *-ot a-ból, ahol nem létezik (a b.-ből válassza ki a felső 1 1-et, ahol b.id = a.id) válassza ki a *-t egy bal oldali csatlakozásból b az a-n. id = b.id ahol a b.id nulla

Ismétlem - az MSSQL optimalizáló optimalizálja ezeket a példákat maximális teljesítményés soha nem lesznek ostoba emberek ilyen egyszerű kérésekkel.

Nézzünk most egy példát egy valós lekérdezésre, amit át kellett írni, mert egyszerűen lefagyott néhány mintán (a szerkezet nagyon leegyszerűsödött és a fogalmak le lettek cserélve, nem kell félni az adatbázis szerkezetének valamiféle nem optimálistól ).

Ki kell húznia az összes ismétlődő „terméket” a különböző fiókokban, a termék, a csoport és a szülőcsoport paramétereire összpontosítva, ha van ilyen.

Válassza ki a következőt: d.PRODUCT_ID a PRODUCT s közül, PRODUCT_GROUP sg balra csatlakozik M_PG_DEPENDENCY sd on (sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID), PRODUCT d, PRODUCT_GROUP dg balra csatlakozik M_PG_DEPENDENCY dd, M_PG_DEPENDENCY dd.DEYM. ILD_ID), ahol s.PRODUCT_GROUP_ID=sg .PRODUCT_GROUP_ID és d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID és sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC és sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME és s.PRODUCT_NAME=d.PRODUCT_NAME és s.PRODUCT_TYPE=d.CUCU_PRODUCT_TYPE és .CUSE_PRODUCT_TYPE és.REISSE és s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT és dg.PRODUCT_GROUP_IS_TMPL=0 és ((az sd.M_PG_DEPENDENCY_CHILD_ID null és a dd.M_PG_DEPENDENCY_CHILD_ID null) vagy létezik (a PRODUCT_GROUP 1-et válasszon a PRODUCT_GROUPsg_GROUPs,ENDPG_GROUPs. PARENT_ID = sg1.PRODUCT_GROUP_ID és dd .M_PG_DEPENDENCY_PARENT_ID = dg1.PRODUCT_GROUP_ID és sg1.PRODUCT_GROUP_PERSPEC=dg1.PRODUCT_GROUP_PERSPEC és sg1.PRODUCT_GROUP_NAME=dg1.PRODUCT_GROUP_NAME és))

Tehát ez az a helyzet, amikor az optimalizáló feladta. És minden sornál végrehajtottak egy súlyos létezést, ami megölte az adatbázist.

Válassza ki a d.PRODUCT_ID elemet a PRODUCT s termékből, csatlakozzon a PRODUCT_ d termékhez s.PRODUCT_TYPE=d.PRODUCT_TYPE és s.PRODUCT_NAME=d.PRODUCT_NAME és s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE és s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT csatlakozás a PRODUCT_IDs_GROUP_GROUP-hoz sg.PRODUCT_GROUP_ID csatlakozik a PRODUCT_GROUP dg csoporthoz a következőn: d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID és sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME és sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC maradt csatlakozni a M_GROUP_GROUP-hoz. Azonosító maradt csatlakozni M_PG_DEPENDENCY dd a dg-n. PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID csatlakozott a PRODUCT_GROUP sgp csoporthoz sgp-n.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_PARENT_ID, csatlakozott a PRODUCT_GROUP dgp-hez a dgp-n.PRODUCT_GROUP_PROPG_DDEPPENTC.PPENDY_ID NÉV = dgp.PRODUCT_GROUP_NAME és isnull(sgp.PRODUCT_GROUP_IS_TMPL, 0) = isnull( dgp. PRODUCT_GROUP_IS_TMPL, 0) ahol (sd.M_PG_DEPENDENCY_CHILD_ID nulla és dd.M_PG_DEPENDENCY_CHILD_ID null) vagy (sgp.PRODUCT_GROUP_NAME nem nulla és dgp.PRODUCT_GROUP_NAME nem null) menjen

Ezen átalakítások után a nézet teljesítménye exponenciálisan nőtt a talált termékek számával. Illetve a keresési idő gyakorlatilag független maradt a találatok számától, és mindig nagyon kicsi volt. Ahogy lennie kell.

Ez egy világos példa arra, hogy az MSSQL-optimalizálóban való bizalom kegyetlen tréfát űzhet. Ne bízzon benne, ne legyen lusta, csatlakozzon manuálisan, gondolja át minden alkalommal, hogy egy adott helyzetben mi a jobb - létezik, benne vagy csatlakozik.

Az SQL nyelvi predikátum EXISTS logikai feladatot hajt végre. BAN BEN SQL lekérdezések ezt az állítmányt a forma kifejezéseiben használják

LÉTEZIK (KIVÁLASZTÁS * FROM TABLE_NAME...).

Ez a kifejezés igazat ad vissza, ha a lekérdezés egy vagy több olyan sort talál, amely megfelel a feltételnek, és hamis értéket ad vissza, ha nem található sor.

A NOT EXISTS esetében fordítva van. Kifejezés

NEM LÉTEZIK (KIVÁLASZTÁS * A TABLE_NAME TÁBLÁZATBÓL...)

igaz értéket ad vissza, ha nem található sor a lekérdezésben, és false értéket, ha legalább egy sor található.

A legegyszerűbb lekérdezések az SQL EXISTS predikátummal

A példákban a könyvtári adatbázissal és annak „Használt könyv” (BOOKINUSE) és „Felhasználó” (USER) táblákkal dolgozunk. Egyelőre csak a „Használt könyv” táblázatra van szükségünk (BOOKINUSE).

Szerző	Cím	Pubyear	Inv_No	Felhasználói azonosító
Tolsztoj	Háború és béke	2005	28	65
Csehov	A Cseresznyéskert	2000	17	31
Csehov	Válogatott történetek	2011	19	120
Csehov	A Cseresznyéskert	1991	5	65
Ilf és Petrov	A tizenkét szék	1985	3	31
Majakovszkij	Versek	1983	2	120
Paszternák	Zhivago doktor	2006	69	120
Tolsztoj	vasárnap	2006	77	47
Tolsztoj	Anna Karenina	1989	7	205
Puskin	A kapitány lánya	2004	25	47
Gogol	Játszik	2007	81	47
Csehov	Válogatott történetek	1987	4	205
Paszternák	Kedvencek	2000	137	18

1. példa Határozza meg azoknak a felhasználóknak az azonosítóit, akik Tolsztoj könyveit és Csehov könyveit is megkapták. A külső lekérdezés azokról a felhasználókról választ adatokat, akik Tolsztoj könyveit kapták, az EXISTS predikátum pedig egy további feltételt ad meg, amelyet a belső lekérdezésben ellenőriznek – azokat a felhasználókat, akik Csehov könyveit kapták. A belső kérés további feltétele, hogy a külső és belső kérések felhasználói azonosítói egyezzenek: User_ID=tols_user.user_id. A kérés a következő lesz:

Ez a lekérdezés a következő eredményt adja vissza:

Az EXISTS és az IN predikátumok közötti különbségek

Az EXISTS predikátumot tartalmazó lekérdezések első pillantásra azt a benyomást kelthetik, hogy ez azonos állítmány IN. Ez rossz. Bár nagyon hasonlóak. Az IN predikátum értékeket keres az argumentumában megadott tartományból, és ha vannak ilyen értékek, akkor az ehhez a tartományhoz tartozó összes sort kijelöli. Az EXISTS predikátum eredménye „igen” vagy „nem” válasz arra a kérdésre, hogy vannak-e egyáltalán olyan értékek, amelyek megfelelnek az argumentumban meghatározottaknak. Ezenkívül az IN predikátumot megelőzi annak az oszlopnak a neve, amely alapján a tartomány értékeinek megfelelő sorokat kell keresni. Nézzünk egy példát, amely bemutatja az EXISTS predikátum és az IN predikátum közötti különbséget, és az IN predikátum segítségével megoldott problémát.

4. példa Határozza meg azoknak a felhasználóknak az azonosítóit, akiknek olyan szerzők adtak ki könyveket, akiknek könyveit a 31-es azonosítójú felhasználónak adták ki. A kérés a következő lesz:

Felhasználói azonosító

120

65

205

Egy belső lekérdezés (az IN után) kiválasztja a szerzőket: Csehov; Ilf és Petrov. A külső lekérdezés kiválasztja az összes olyan felhasználót, akinek ezek a szerzők könyveket adtak ki. Azt látjuk, hogy az EXISTS állítmánytól eltérően az IN predikátumot az oszlop neve előzi meg, jelen esetben - Szerző.

Lekérdezések EXISTS predikátummal és további feltételekkel

Ha a lekérdezésben az EXISTS predikátumon kívül legalább egy további feltételt alkalmaz, például összesített függvények, akkor az ilyen lekérdezések egyszerű adatelemzést szolgálhatnak. Mutassuk meg ezt a következő példával.

5. példa. Határozza meg azoknak a felhasználóknak az azonosítóit, akiknek Pasternak legalább egy könyvet adott ki, és akiknek kettőnél több könyvet adtak ki. A következő lekérdezést írjuk, amelyben az első feltételt az EXISTS predikátum adja meg beágyazott lekérdezéssel, a második feltételnek pedig a HAVING operátorral mindig a beágyazott lekérdezés után kell jönnie:

A kérés eredménye:

Felhasználói azonosító

120

Mint a BOOKINUSE táblázatból látható, Pasternak könyvét is kiadták a 18-as azonosítójú felhasználónak, de csak egy könyvet kapott, és nem szerepel a mintában. Ha ismét alkalmazza a COUNT függvényt egy hasonló lekérdezésre, de ezúttal a kiválasztott sorok megszámlálásához (ezt gyakorolja saját maga), akkor információt kaphat arról, hogy hány felhasználó olvassa el Pasternak könyveit más szerzők könyveit is. Ez már az adatelemzés területéről való.

Az EXISTS predikátummal rendelkező lekérdezések két táblán

Az EXISTS predikátummal rendelkező lekérdezések egynél több táblából is lekérhetnek adatokat. Sok probléma megoldható ugyanazzal az eredménnyel JOIN operátor, de bizonyos esetekben az EXISTS használatával kevésbé körülményes lekérdezést hozhat létre. Célszerű az EXISTS használata olyan esetekben, amikor az eredményül kapott táblázat csak egy tábla oszlopait tartalmazza.

A következő példában ugyanabból az adatbázisból a BOOKINUSE táblán kívül szükség lesz egy USER táblára is.

A lekérdezés eredménye a következő táblázat lesz:

Szerző

Csehov

Majakovszkij

Paszternák

A JOIN operátorhoz hasonlóan olyan esetekben, amikor egynél több tábla van, táblaálneveket kell használnia annak ellenőrzésére, hogy a táblákat összekötő kulcsok értékei megegyeznek-e. Példánkban a táblaálnevek a bk és a us, a táblákat összekötő kulcs pedig a User_ID.

EXISTS predikátum kettőnél több tábla összekapcsolásában

Most részletesebben meglátjuk, miért célszerű az EXISTS használata olyan esetekben, amikor az eredményül kapott táblázat csak egy tábla oszlopait tartalmazza.

Az "Ingatlan" adatbázissal dolgozunk. Az Ügylet táblázat az ügyletekre vonatkozó adatokat tartalmazza. Feladatainknál fontos lesz ebben a táblázatban a Típus oszlop a tranzakció típusára vonatkozó adatokkal - eladás vagy lízing. Az Object tábla az objektumokról tartalmaz adatokat. Ebben a táblázatban szükségünk lesz a Szobák (szobák száma) és a LogBalc oszlopok értékeire, amelyek a loggia vagy erkély jelenlétére vonatkozó adatokat tartalmazzák logikai formátumban: 1 (igen) vagy 0 (nem). Az Ügyfél, Kezelő és Tulajdonos táblák az ügyfelekről, a cégvezetőkről és az ingatlantulajdonosokról tartalmaznak adatokat. Ezekben a táblázatokban az FName és a LNname a vezetéknév, illetve a vezetéknév.

7. példa. Azonosítsa azokat az ügyfeleket, akik olyan ingatlanokat vásároltak vagy béreltek, amelyekhez nem tartozik loggia vagy erkély. A következő lekérdezést írjuk, amelyben az EXISTS predikátum hozzáférést ad meg két tábla összekapcsolásának eredményéhez:

Mivel a Kliens táblából az oszlopok a csillag operátorral vannak kiválasztva, ennek a táblának az összes oszlopa megjelenik, amelyeknek annyi sora lesz, ahány kliens megfelel a EXISTS predikátum által megadott feltételnek. Nem kell oszlopot kiadnunk azokból a táblákból, amelyek csatlakozását az allekérdezés eléri. Ezért a gépidő megtakarítása érdekében csak egy oszlop kerül leolvasásra. Ehhez a SELECT szó után egy mértékegységet kell írni. Ugyanezt a technikát alkalmazzuk a következő példákban szereplő lekérdezésekben.

Írjon saját maga egy SQL-lekérdezést az EXISTS predikátummal, majd nézze meg a megoldást

Az SQL lekérdezéseket továbbra is az EXISTS predikátummal együtt írjuk

9. példa. Határozza meg a lízingelt tárgyak tulajdonosait. Megírjuk a következő lekérdezést, amelyben az EXISTS predikátum is hozzáférést ad két tábla összekapcsolásának eredményéhez:

Az előző példához hasonlóan a tábla összes mezője, amelyhez a külső lekérdezés hozzáfért, visszaadásra kerül.

10. példa. Határozza meg azon tulajdonosok számát, akiknek ingatlanait Saveljev menedzser kezelte. Írunk egy lekérdezést, amelyben a külső lekérdezés három tábla összekapcsolásához fér hozzá, és az EXISTS predikátum csak egy táblához ad hozzáférést:

Minden lekérdezést egy meglévő adatbázishoz hasonlítanak. Sikeres használat!

Relációs adatbázisok és SQL nyelv

Novoszibirszki Állami Gazdasági és Menedzsment Akadémia

LABORATÓRIUMI GYAKORLAT A FEGYELEMRŐL

"ADATBÁZIS"

7. sz. laboratóriumi munka

"Az alapok nyelve SQL adatok: adatkezelési parancsok»

NOVOSIBIRSK 2000

Az SQL a Structured Query Language rövidítése. A nyelv nevéből kitűnik, hogy fő célja lekérdezések generálása az adatbázisból való információszerzés érdekében. Az adatok lekérésére szolgáló parancsok képezik a DML adatkezelési nyelv alapját - az SQL nyelv szerves részét. A DML azonban nem csupán parancsokat tartalmaz az adatok adatbázisból való lekérésére. Vannak még parancsok adatmódosításra, adatkezelésre és egyebekre.

A laboratóriumi munka a DML nyelv alapvető eszközeit vizsgálja. Folyamatban laboratóriumi munka ragaszkodunk az SQL2 szabványhoz.

Tekintettel arra, hogy az SQL egy nagy nyelv, csak az alapvető parancsokat fogjuk figyelembe venni. Különféle speciális SQL-eszközöket a következő laboratóriumok tárgyalnak.

Laboratóriumi munkák elvégzéséhez a relációs adatmodell alapjainak, a relációs algebra és a relációszámítás alapjainak ismerete, valamint az MS SQL Server DBMS-sel való munkavégzés elveinek ismerete szükséges.

A laboratóriumi munka eredményeként elsajátítja az SQL nyelvi parancsokkal történő adatkezelés módszereit, figyelembe véve az MS SQL Server DBMS-ben megvalósított nyelv dialektusát.

BEVEZETÉS

Az SQL az adatkezelési lehetőségek széles skáláját tartalmazza, mind a lekérdezések létrehozásához, mind az adatbázis frissítéséhez. Ezek a képességek csak az adatbázis logikai struktúrájára támaszkodnak, nem a fizikai struktúrájára, ami összhangban van a relációs modell követelményeivel.

Az SQL szintaxis eredeti szerkezete Codd relációs számításán alapult (vagy legalábbis úgy tűnt). A relációs algebra egyetlen támogatott művelete az unió volt.

Az SQL2 az előző szabványban kifejlesztett relációs kalkulus-szerű szintaxis mellett közvetlenül valósítja meg az uniót, a metszéspontot, a különbséget és az összekapcsolást. A kiválasztási, projekt- és termékműveleteket szinte közvetlenül támogatták (és továbbra is támogatják), míg a felosztási és hozzárendelési műveleteket nehézkesebb formában.

Először az SQL lekérdezési nyelvet, majd annak adatbeviteli és módosítási műveleteit írjuk le. Az adatmódosítási műveleteket utoljára írjuk le, mivel szerkezetük bizonyos mértékig a lekérdező nyelv szerkezetétől függ.

Egyszerű lekérdezések

Nekünk egyszerű kérés lesz egy lekérdezés, amely csak egy táblát ér el az adatbázisban. Az egyszerű lekérdezések segítenek az SQL alapvető szerkezetének illusztrálásában.

Egyszerű kérés. Egy lekérdezés, amely csak egy adatbázistáblához fér hozzá.

Kérés: Ki dolgozik vakolóként?

WHERE SKILL_TYPE = "Gipszoló"

Eredmény:

G.Rickover

Ez a lekérdezés a három leggyakoribbat mutatja be kifejezéseket SQL: SELECT, FROM és WHERE. Bár példánkban különböző sorokra helyeztük őket, mindegyik megjelenhet ugyanabban a sorban. Különböző behúzások is lehetnek, és a kifejezéseken belüli szavak tetszőleges számú szóközzel elválaszthatók. Nézzük meg az egyes kifejezések jellemzőit.

Válassza ki. A SELECT záradék felsorolja azokat az oszlopokat, amelyeknek meg kell jelenniük a kapott táblázatban. Ezek mindig valamilyen relációs tábla oszlopai. Példánkban az eredményül kapott táblázat egy oszlopból (NAME) áll, de általában több oszlopot is tartalmazhat; számított értékeket vagy állandókat is tartalmazhat. Példákat adunk ezekre a lehetőségekre. Ha az eredményül kapott táblázatnak egynél több oszlopot kell tartalmaznia, akkor az összes szükséges oszlopot ezután felsoroljuk SELECT parancsok vesszővel elválasztva. Például a SELECT WORKER_ID, NAME kifejezés egy táblázatot eredményez, amely a WORKER_ID és a NAME oszlopokból áll.

SELECT záradék. Meghatározza az eredményül kapott tábla oszlopait.

Tól től. A FROM záradék egy vagy több táblát ad meg, amelyekhez a lekérdezés hozzáfér. A SELECT és WHERE záradékban felsorolt ​​összes oszlopnak szerepelnie kell a FROM parancsban felsorolt ​​táblák egyikében. Az SQL2-ben ezek a táblák közvetlenül definiálhatók a sémában alaptáblaként vagy adatnézetként, vagy maguk is lehetnek névtelen táblák, amelyek SQL-lekérdezésekből származnak. Ez utóbbi esetben a kérés kifejezetten a FROM parancsban van megadva.

A FROM kifejezés. Megadja a meglévő táblákat, amelyekhez a lekérdezés hozzáfér.

Ahol. A WHERE záradék feltételt tartalmaz. amely alapján kiválasztásra kerülnek a táblázat(ok) sorai. Példánkban a feltétel az, hogy a SKILL_TYPE oszlopnak tartalmaznia kell a "Plaster" konstanst aposztrófok közé zárva, ahogy az SQL-ben mindig szöveges állandókkal történik. A WHERE záradék a leginkább illékony SQL-parancs; sokféle feltételt tartalmazhat. Beszélgetésünk nagy részét a WHERE parancsban engedélyezett különféle konstrukciók szemléltetésének szentelik.

WHERE záradék. Megadja azt a feltételt, hogy a megadott táblákból mely sorok legyenek kiválasztva.

A fenti SQL lekérdezést a rendszer a következő sorrendben dolgozza fel: FROM, WHERE, SELECT. Vagyis a FROM parancsban megadott tábla sorai a munkaterületre kerülnek feldolgozás céljából. A WHERE záradék ezután minden sorra egymás után kerül alkalmazásra. Minden olyan sor, amely nem felel meg a WHERE feltételnek, kizárásra kerül. Ezután azokat a sorokat, amelyek teljesítik a WHERE feltételt, a SELECT utasítás dolgozza fel. Példánkban a NÉV ki van választva minden ilyen sorból, és az összes kiválasztott érték lekérdezés eredményeként jelenik meg.

Kérés: Adjon meg minden információt az irodaházakról.

WHERE TYPE = "iroda"

Eredmény:

BLDG IDADDRESSTYPEQLTY LEVELSTATUS

312 Elm St., 123 Office 2 2

210 Berezovaya st. 1011 Office Z 1

Osinovaya utca 111. 1213 Office 4 1

A SELECT parancsban szereplő csillag (*) a „teljes sort” jelenti. Ez egy kényelmes gyorsírás, amelyet gyakran fogunk használni.

Kérés: Mennyi az egyes villanyszerelők heti fizetése?

KIVÁLASZTÁS NÉV, "Heti fizetés = ", 40 * HRLY_RATE

WHERE SKILL_TYPE = "villanyszerelő"

Eredmény:

M. Faraday Heti fizetés = 500,00

H.Columbus Heti fizetés = 620,00

Ez a lekérdezés szemlélteti mind a karakterkonstansok (példánkban "Heti fizetés = "), mind a SELECT parancsban végzett számítások használatát. A SELECT utasításon belül olyan számításokat végezhet, amelyek numerikus oszlopokat és numerikus állandókat, valamint szabványos aritmetikai operátorokat ( +, -, *, /), szükség szerint csoportosítva zárójelben. Beépítettünk egy újat is ORDER parancsot BY, amely a lekérdezés eredményét növekvő alfanumerikus sorrendbe rendezi a megadott oszlop szerint. Ha az eredményeket csökkenő sorrendbe szeretné rendezni, akkor a parancshoz hozzá kell adni a DESC szót. Az ORDER BY záradék több oszlop szerint rendezheti az eredményeket, egyeseket növekvő, másokat pedig csökkenő sorrendben. A rendezés elsődleges kulcs oszlopa jelenik meg először.

Karakterállandó. Betűkből, számokból és „speciális” karakterekből álló konstans.

Kérés: Kinek van 10-12 dollár órabére?

WHERE HRLY_RATE > = 10 ÉS HRLY_RATE< - 12

Eredmény:

MUNKAVÁLLALÁSI ID NAME HRLY_RATE SKILL_TYPE SUPV_ID

Ez a lekérdezés a WHERE utasítás néhány további szolgáltatását szemlélteti: az összehasonlító operátorokat és a logikai AND operátort. Hat összehasonlító operátor (=,<>(nem egyenlő),<, >, <=, >=). Az AND, OR és NOT logikai operátorok összetett feltételek létrehozására vagy egy feltétel tagadására használhatók. A zárójelek a feltételek csoportosítására használhatók, ahogy az a programozási nyelvekben megszokott.

Összehasonlító operátorok =,<>, <, >, <=, >=.

Boole-műveletekÉS (ÉS), VAGY (VAGY) és NEM (Ő) .

A lekérdezés megfogalmazásához használhatja a BETWEEN (between) operátort is:

WHERE HRLY_RATE 10 ÉS 12 KÖZÖTT

A BETWEEN segítségével összehasonlíthatunk egy mennyiséget két másik mennyiséggel, amelyek közül az első kisebb, mint a második, ha az összehasonlított mennyiség e mennyiségek mindegyikével vagy bármely közötti értékkel egyenlő lehet.

Kérés: Vakolókat, tetőfedőket, villanyszerelőket soroljon fel.

WHERE SKILL_TYPE IN ("Gipszos", "Tetőfedő", "Villanyszerelő")

Eredmény:

WORKER_ID NAME HRLY_RATE SKILL_TYPE SUPV_ID

1412 K.Nemo 13.75 Vakolat 1520

2920 R. Garrett 10.00 Tetőfedő 2920

1520 G. Rickover 11.75 Vakolat 1520

Ez a lekérdezés elmagyarázza az IN (B) összehasonlító operátor használatát. A WHERE feltétel akkor tekinthető igaznak, ha a sor szakterülete a zárójelben megadott halmazon belül található, vagyis ha a szakterület vakoló, tetőfedő vagy villanyszerelő. Az IN operátort ismét látni fogjuk az allekérdezésekben.

Tételezzük fel, hogy nem emlékszünk pontosan a szakterületünk írásmódjára: „villanyszerelő” vagy „elektronikai mérnök” vagy valami más. A nem meghatározott karakterláncokat helyettesítő helyettesítő karakterek megkönnyítik a pontatlan írásmódok megtalálását a lekérdezésben.

Minta szimbólumok. A meghatározatlan karakterláncokat helyettesítő karakterek.

Kérés: Sorolja fel azokat az alkalmazottakat, akiknek a szakterülete „Elekkel” kezdődik!

WHERE SKILL_TYPE LIKE ("Elect%")

Eredmény:

MUNKAVÁLLALÓ AZONOSÍTÓ NÉV HRLY_RATE SKILL_TYPE SUPV_ID

1235 M. Faraday 12.50 Villanyszerelő 1311

1311 H. Columbus 15.50 Elektromos 1311

Az SQL-ben két helyettesítő karakter található: % (százalék) és _ (aláhúzás). Az aláhúzás pontosan egy meghatározatlan karaktert helyettesít. A százalék tetszőleges számú, nullával kezdődő karaktert helyettesít. Helyettesítő karakterek használata esetén egy LIKE operátor szükséges a karakterváltozók és az állandók összehasonlításához. Egyéb példák:

NÉV, SZERETNÉL "__Columbus"

A NÉV MINT "__K%"

Az első példa feltétele igaz, ha a NÉV két karakterből áll, amelyet a „Columbus” követ. A WORKER táblában minden név kezdőbetűvel és ponttal kezdődik. Így ezt a feltételt felhasználva mi. Keressük meg az összes "Columbus" vezetéknévvel rendelkező alkalmazottat. A második példa feltétele lehetővé teszi, hogy megtaláljuk az összes alkalmazottat, akinek vezetékneve „K” betűvel kezdődik.

Kérés: Keresse meg az összes olyan munkát, amely a következő két héten belül kezdődik.

HOL START_DATE CURRENT_DATE ÉS KÖZÖTT

Eredmény:(Tegyük fel, hogy az aktuális dátum AKTUÁLIS DÁTUM = 10.10)

WORKER_ID BLDG_ID START_DATE NUM_DAYS

1235 312 10.10 5

1235 515 17.10 22

3231 111 10.10 8

1412 435 15.10 15

3231 312 24.10 20

1311 460 23.10 24

Ez a lekérdezés a BETWEEN operátor használatát szemlélteti dátum és intervallum értékekkel. A CURRENT_DATE egy olyan függvény, amely mindig a mai dátumot adja vissza. Kifejezés

CURRENT_DATE + INTERVAL "14" DAY

kéthetes időszakot ad hozzá az aktuális dátumhoz. Így a ASSIGNMENT akkor van kiválasztva (feltételezve, hogy a mai nap 10/10), ha a START_DATE oszlop értéke 10/10 és 10/24 között van. Ebből láthatjuk, hogy a dátummezőkhöz intervallumértékeket adhatunk. Sőt, az intervallumok értékeit megszorozhatjuk egész értékekkel. Tegyük fel például, hogy meg akarjuk tudni, hogy egy bizonyos számú hét múlva (a NUM_WEEKS változóval jelölve) mennyi lesz a szám. Ezt így tehetjük meg:

CURRENT_DATE + INTERVAL "7" DAY * NUM_WEEKS

2. Többtáblás lekérdezések

Az adatelemek egyetlen tábla határain átnyúló összekapcsolásának képessége minden adatbázisnyelv számára fontos. A relációs algebrában ezt a funkciót az összekapcsolási művelet hajtja végre. Bár az SQL nagy része közvetlenül relációs számításon alapul, az SQL a relációs algebra összekapcsolási műveletéhez hasonló módon kapcsolja össze a különböző táblákból származó adatokat. Most megmutatjuk, hogyan történik ez. Fontolja meg a kérést:

Kérés:

A válaszhoz szükséges adatok két táblázatban találhatók: MUNKÁS és MEGJEGYZÉS. Az SQL-megoldás megköveteli mindkét tábla felsorolását a FROM parancsban, és egy speciális WHERE záradék megadását:

KIVÁLASZTÁSA SKILL_TYPE

MUNKÁSTÓL, MEGJEGYZÉS

WHERE WORKER.WORKER_ID = FELADAT.MUNKÁS_ID

ÉS BLDG_ID = 435

Mi folyik itt? A kérés feldolgozásának két szakaszát kell figyelembe vennünk.

1. Szokás szerint először a FROM záradék kerül feldolgozásra. Ebben az esetben azonban, mivel a parancs két táblát ad meg, a rendszer ezeknek a tábláknak a soraiból létrehoz egy derékszögű szorzatot. Ez azt jelenti, hogy egy nagy tábla jön létre (logikailag), amely mindkét tábla oszlopaiból áll, és az egyik tábla minden sora párosul a másik tábla minden sorával. Példánkban, mivel a MUNKAVÁLLALÁS tábla öt oszlopból, a ASSIGNMENT tábla pedig négy oszlopból áll, a FROM paranccsal előállított derékszögű szorzat kilenc oszlopos lesz. A derékszögű szorzat sorainak teljes száma egyenlő m * n-nel, ahol m a WORKER tábla sorainak száma; és n a HOZZÁADÁS tábla sorainak száma. Mivel a MUNKÁS tábla 7 soros és a ASSIGNMENT tábla 19 soros, a derékszögű szorzat 7x19 vagy 133 sort tartalmaz. Ha a FROM parancs kettőnél több táblát sorol fel, akkor a parancsban megadott összes tábla derékszögű szorzata jön létre.

Descartes termék. Egy táblázat minden sorának egyesítésének eredménye minden egyes egy sor egy másik táblázatból.

2. Az óriás relációs tábla létrehozása után a rendszer a korábbiak szerint a WHERE parancsot használja. A FROM paranccsal létrehozott táblázat minden sora. ellenőrzi, hogy a WHERE feltétel teljesül-e. Azokat a sorokat, amelyek nem felelnek meg a feltételnek, nem veszik figyelembe. A SELECT záradék ezután a fennmaradó sorokra kerül alkalmazásra.

A WHERE záradék a lekérdezésünkben két feltételt tartalmaz:

1. MUNKÁS. WORKER_ID = FELADAT.MUNKÁS_ID

2. BLDG_ID = 435

E feltételek közül az első a csatlakozási feltétel. Vegye figyelembe, hogy mivel a WORKER és ASSIGNMENT tábla is tartalmaz egy MUNKAVÁLLALÓ_ID nevű oszlopot, a derékszögű szorzatuk két ilyen nevű oszlopot fog tartalmazni. Megkülönböztetésük érdekében az oszlopnév elé a forrástábla nevét helyezzük el, ponttal elválasztva.

Az első feltétel azt jelenti, hogy bármely kiválasztott sorban a WORKER_ID oszlop értékének meg kell egyeznie a WORKER_ID oszlop értékével a ASSIGNMENT táblából. Valójában WORKER_ID két táblát egyesítünk. Minden olyan sor, amelyben e két oszlop értéke nem egyenlő, kizárásra kerül a terméktáblázatból. Pontosan ugyanez történik a relációs algebra természetes összekapcsolási műveletének végrehajtásakor. (A természetes csatlakozáshoz képest azonban még mindig van némi különbség: az SQL nem távolítja el automatikusan az extra WORKER_ID oszlopot). E két tábla teljes összekapcsolása a BLDG_ID = 435 további feltétellel az ábrán látható. 1. A SELECT parancs használata végül a következő lekérdezési eredményt adja:

KÉPESSÉG TÍPUSA

Vakoló

Tetőfedő

Villanyszerelő

Rizs. 1. A MUNKAVÁLLALÁS és a MEGJEGYZÉS táblák összekapcsolása

Most megmutatjuk, hogyan lehet egy táblát önmagához kapcsolni SQL-ben.

Kérés: Sorolja fel az alkalmazottakat vezetőik nevének feltüntetésével.

VÁLASSZA A.WORKER_NAME, B.WORKER_NAME

A MUNKÁSTÓL, B MUNKÁSTÓL

WHERE B.WORKER_ID = A.SUPV_ID

A FROM záradék ebben a példában létrehozza a WORKER tábla két "másolatát", és az A és B álnevet adja. Az álnév a táblának adott alternatív név. Ezután a WORKER tábla A és B példányait a WHERE paranccsal egyesítjük a B-ben lévő WORKER_ID és az A SUPV_ID egyenlőségi feltétele alapján. Így az A sorok mindegyike a B sorhoz kapcsolódik, amely az A sor kezelőjével kapcsolatos információkat tartalmaz. (2. ábra).

Rizs. 2. A MUNKÁS tábla két példányának összekapcsolása

Minden sorból két-két alkalmazotti nevet kiválasztva megkapjuk a szükséges listát:

A.NAMEB.NAME

M. Faraday H. Columbus

K.Nemo G.Rickover R.Garrett R.Garrett

P. Mason P. Mason G. Rickover G. Rickover H. Columbus H. Columbus J. Barrister P. Mason

Becenév. A táblázatnak adott alternatív név.

A.WORKER_NAME a dolgozót, B.WORKER_NAME pedig a vezetőt képviseli. Kérjük, vegye figyelembe, hogy egyes dolgozók saját maguk menedzserei, ami a soraikban szereplő WORKER_ID - SUPV_ID egyenlőségből következik.

SQL-ben egyszerre kettőnél több táblát is összekapcsolhat:

Kérés

SELECT WORKER_NAME

MUNKÁSTÓL, MEGJEGYZÉSBŐL, ÉPÍTÉSBŐL

WHERE MUNKAVÁLLALÓ.MUNKAVÁLLALÓ_AZONOSÍTÓ = FELADAT.MUNKAVÁLLALÁS ÉS ASSIGNMENT.BLDG_ID = ÉPÜLET.BLDG_ID ÉS

TYPE = "iroda"

Eredmény:

M. Faraday

G.Rickover

J. Barrister

Vegye figyelembe, hogy ha egy oszlopnév (például WORKER_ID vagy BLDG_ID) egynél több táblában szerepel, akkor a félreértések elkerülése végett az oszlopnév elé kell fűzni az eredeti tábla nevét. De ha az oszlopnév csak egy táblában fordul elő, mint a példánkban a TYPE, akkor nincs kétértelműség, így a tábla nevét nem kell megadni.

A lekérdezésben szereplő SQL-parancsok egy táblát hoznak létre három relációs adatbázistáblából. Az első két táblát a WORKER_ID, majd a harmadik táblát a BLDG_ID csatlakozik a kapott táblához. Feltétel

TYPE = "iroda"

A WHERE záradék az irodaházak kivételével az összes sort kizárja. Ez megfelel a kérés követelményeinek.

3. Allekérdezések

Allekérdezés. Lekérdezés lekérdezésben

Egy részlekérdezés elhelyezhető a lekérdezés WHERE záradékán belül, ezáltal kibővítve a WHERE záradék lehetőségeit. Nézzünk egy példát.

Kérés: Milyen szakterületei vannak a 435-ös épülethez rendelt munkásoknak?

SELECT SKTLL_TYPE

FROM WORKER WHERE WORKER_ID IN

(SELECT WORKER_ID

WHERE BLDG_ID = 435)

Allekérdezés ebben a példában

(SELECT WORKER_ID

WHERE BLDG_ID = 435)

A részlekérdezést tartalmazó lekérdezés meghívásra kerül külső kérés vagy fő kérés. Az allekérdezés eredményeként a következő alkalmazotti azonosítók jönnek létre:

MUNKAVÁLLALÁSI ID

Külső kérés. A fő lekérdezés, amely az összes segédlekérdezést tartalmazza.

Ez az azonosítókészlet ezután egy segédlekérdezés helyét veszi át a külső lekérdezésben. Ettől kezdve a külső lekérdezés az allekérdezés által létrehozott halmaz segítségével kerül végrehajtásra. A külső lekérdezés a WORKER tábla minden sorát a WHERE záradéknak megfelelően dolgozza fel. Ha egy sor WORKER_ID-je az allekérdezés által létrehozott (IN) halmazban található, akkor a sor SKILL_TYPE-ja kerül kiválasztásra és megjelenik a kapott táblázatban:

KÉPESSÉG TÍPUSA

Vakoló

Tetőfedő

Villanyszerelő

Nagyon fontos, hogy az allekérdezés SELECT záradéka tartalmazza a WORKER_ID-t és csak a WORKER_ID-t. Ellenkező esetben a külső lekérdezés WHERE záradékának, ami azt jelenti, hogy a WORKER_ID a dolgozóazonosítók halmazában van, nem lenne értelme.

Vegye figyelembe, hogy egy segédlekérdezés logikailag végrehajtható azelőtt, hogy a fő lekérdezés legalább egy sort figyelembe venne. Bizonyos értelemben egy segédlekérdezés független a fő lekérdezéstől. Teljes lekérdezésként is végrehajtható. Azt mondjuk, hogy egy ilyen segédlekérdezés nincs összefüggésben a fő lekérdezéssel. Amint azt hamarosan látni fogjuk, az allekérdezések korrelálhatók.

Nem korrelált részlekérdezés. Olyan segédlekérdezés, amelynek értéke független minden külső lekérdezéstől.

Íme egy példa egy segédlekérdezésre egy segédlekérdezésben.

Kérés: Sorolja fel az irodaházakhoz rendelt alkalmazottakat.

Ismét megnézzük azt a lekérdezést, amellyel a kapcsolatot vizsgáltuk.

SELECT WORKER_MAME

WORKER_ID IN

(SELECT WORKER_ID

WHERE BLDG_ID IN

WHERE TYPE = "iroda"))

Eredmény:

M. Faraday

G.Rickover

J. Barrister

Ne feledje, hogy az oszlopneveket nem kell sehol táblanevekkel előtagoznunk, mivel minden részlekérdezés egy és csak egy táblát dolgoz fel, így nem merülhet fel kétértelműség.

A lekérdezés végrehajtása belülről kifelé sorrendben történik. Vagyis először a legbelső (vagy "alsó") lekérdezés kerül végrehajtásra, majd az azt tartalmazó részlekérdezés, majd a külső lekérdezés.

Összefüggő allekérdezések. A fent tárgyalt összes allekérdezés független volt azoktól a fő lekérdezésektől, amelyekben használták őket. Független alatt azt értjük, hogy az allekérdezések önállóan is végrehajthatók teljes lekérdezésként. Most áttérünk az allekérdezések egy osztályára, amelyek végrehajtásának eredménye a fő lekérdezés által figyelembe vett sortól függhet. Az ilyen részlekérdezéseket korrelált részlekérdezéseknek nevezzük.

Korrelált részlekérdezés. Olyan segédlekérdezés, amelynek eredménye a fő lekérdezés által figyelembe vett sortól függ.

Kérés: Sorolja fel azokat az alkalmazottakat, akiknek az órabére magasabb, mint a vezetőiké!

SELECT WORKER_NAME

WHERE A.HRLY_RATE >

(SELECT B.HRLY_RATE

WHERE B.WORKER_ID = A.SUPV_ID)

Eredmény:

A kérés végrehajtásának logikai lépései a következők:

1. A MUNKAVÁLLALÁS táblából két példányt készít a rendszer: az A példányt és a B példányt. Az általunk definiált mód szerint A a munkavállalóra, B a vezetőre vonatkozik.

2. A rendszer ezután minden A sort figyelembe vesz. Egy adott sort akkor választ ki, ha az megfelel a WHERE feltételnek. Ez a feltétel azt jelenti, hogy egy sor kerül kiválasztásra, ha a HRLY_RATE értéke nagyobb, mint az allekérdezés által generált HRLY_RATE.

3. Az allekérdezés kiválasztja a HRLY_RATE értéket a B sorból, amelynek WORKER_ID értéke megegyezik az A sor SUPV_ID értékével. Ebben a pillanatban a fő kérés figyelembe veszi. Ez a menedzser HRLY_RATE értéke.

Vegye figyelembe, hogy mivel az A.HRLY_RATE csak egy értékkel hasonlítható össze, az allekérdezésnek csak egy értéket kell visszaadnia. Ez az érték attól függően változik, hogy melyik A sort veszi figyelembe. Így az allekérdezés korrelál a fő lekérdezéssel. További példákat fogunk látni a korrelált részlekérdezésekre később, amikor a beépített függvényeket tanulmányozzuk.

EXISTS és NOT EXISTS operátorok

Tegyük fel, hogy azonosítani akarjuk azokat a munkásokat, akik nem egy adott épületben dolgoznak. A felszínen úgy tűnik, hogy egy ilyen kérés könnyen kielégíthető a kérelem igenlő változatának egyszerű tagadásával. Tegyük fel például, hogy egy BLDG_ID 435-ös épület iránt érdeklődünk. Tekintsük a kérést:

SELECT WORKER_ID

AHOL BLDG_ID NEM 435

Sajnos ez a megoldás helytelen megfogalmazása. A kérés egyszerűen megadja a más épületekben dolgozó munkavállalók azonosítóit. Nyilvánvalóan ezek egy része a 435-ös épülethez is hozzárendelhető.

A helyesen megfogalmazott megoldás a NOT EXISTS operátort használja:

SELECT WORKER_ID

AHOL NEM LÉTEZIK

WHERE ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID ÉS

Eredmény:

WORKER_ID

Az EXISTS és NOT EXISTS operátorok mindig az allekérdezés elé kerülnek. Az EXISTS értéke igaz, ha az allekérdezés által generált halmaz nem üres. Ha az allekérdezés által generált halmaz üres, akkor az EXISTS „false” értéket vesz fel. A NOT EXISTS operátor természetesen pont fordítva működik. Igaz, ha az allekérdezés eredménye üres, egyébként hamis.

EXISTS operátor. Igaz értéket ad vissza, ha az eredményhalmaz nem üres.

NOT EXISTS operátor. Igaz értéket ad vissza, ha az eredményhalmaz üres.

Ebben a példában a NOT EXISTS operátort használtuk. Az allekérdezés a ASSIGNMENT tábla összes olyan sorát kiválasztja, amelyben a WORKER_ID értéke megegyezik a fő lekérdezés által figyelembe vett sorral, és a BLDG_ID értéke 435. Ha ez a halmaz üres, akkor a fő lekérdezés által figyelembe vett dolgozó sor kiválasztott, mivel ez azt jelenti, hogy Ez az alkalmazott nem a 435-ös épületben dolgozik.

Az általunk biztosított megoldásban korrelált részlekérdezést használtunk. Ha a NOT EXISTS helyett az IN operátort használjuk, akkor egy nem korrelált részlekérdezéssel is beérhetünk:

SELECT WORKER_ID

WORKER_ID NINCS BE

(SELECT WORKER_ID

WHERE BLDG_ID = 435)

Ez a megoldás egyszerűbb, mint a NOT EXISTS operátor megoldása. Felmerül egy természetes kérdés: miért van szükségünk LÉTEZEKRE, és miért NEM LÉTEZIK? A válasz az, hogy a NOT EXISTS az egyetlen módja annak, hogy megoldjuk azokat a lekérdezéseket, amelyek a feltételben az "minden" szót tartalmazzák. Az ilyen lekérdezéseket a relációs algebrában az osztási művelettel, a relációs kalkulusban pedig az univerzális kvantorral lehet megoldani. Íme egy példa egy lekérdezésre, amelynek feltétele az „minden” szó:

Kérés: Sorolja fel az egyes épületekhez rendelt alkalmazottakat.

Ez a kérdés megvalósítható SQL-ben kettős tagadások segítségével. Újrafogalmazzuk a lekérdezést, hogy tartalmazzon egy kettős negatívot:

Kérés: Sorolja fel azokat az alkalmazottakat, akiknek Nem van egy épület, amelyhez nincsenek hozzárendelve.

Kiemeltük a kettős negatívumot. Nyilvánvaló, hogy ez a kérés logikailag egyenértékű az előzővel.

Most SQL-ben szeretnénk megfogalmazni a megoldást. Hogy a végső megoldást könnyebben érthetővé tegyük, először egy előzetes problémára adunk megoldást: az összes olyan épület azonosításának problémáját, amelyekhez egy feltételezett munkás, "1234" Nem kijelölt.

(I) SELECT BLDG_ID

AHOL NEM LÉTEZIK

FELADAT.MUNKAVÁLLALÓ_ID = 1234)

Ezt a lekérdezést (I) jelöltük meg, mert később hivatkozunk rá. Ha nincs olyan épület, amely eleget tenne ennek a kérésnek, akkor az 1234-es munkás minden épülethez hozzá van rendelve, és így eleget tesz az eredeti kérelem feltételeinek. Ahhoz, hogy megoldást kapjunk az eredeti lekérdezésre, általánosítanunk kell egy adott 1234 dolgozótól származó (I) lekérdezést a WORKER_ID változóra, és ezt a módosított lekérdezést a nagyobb lekérdezés részlekérdezésévé kell alakítanunk. Íme a megoldás:

(II) KIVÁLASZTJA MUNKAazonosítót

AHOL NEM LÉTEZIK

AHOL NEM LÉTEZIK

WHERE ASSIGNMENT.BLDG_ID = ÉPÜLET.BLDG_ID ÉS

ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID)

Eredmény:

MUNKAVÁLLALÁSI ID

Vegye figyelembe, hogy a (II) lekérdezés negyedik sorától kezdődő részlekérdezés megegyezik az (I) lekérdezéssel, az „1234” helyett a WORKER.WORKER_ID. A (II) lekérdezés a következőképpen olvasható:

Válassza ki a WORKER_ID elemet a WORKER közül, ha nincs olyan épület, amelyhez ne lenne hozzárendelve WORKER_ID.

Ez megfelel az eredeti kérés feltételeinek.

Látjuk, hogy a NOT EXISTS operátor használható olyan lekérdezések megfogalmazására, amelyek relációs algebrában osztási műveletet, relációs számításban univerzális kvantort igényeltek. A könnyű használhatóság szempontjából a NOT EXISTS operátor nem kínál különösebb előnyt, ami azt jelenti, hogy a NOT EXISTS kétszer használó SQL-lekérdezéseket semmivel sem könnyebb megérteni, mint az osztásos relációs algebrai megoldásokat vagy az univerzális kvantorokkal rendelkező relációs számítási megoldásokat. További kutatásokra lesz szükség olyan nyelvi konstrukciók létrehozásához, amelyek lehetővé teszik az ilyen lekérdezések természetesebb megoldását.

Beépített funkciók

Nézzük meg az ilyen típusú kérdéseket:

Melyek a maximális és minimális óradíjak? Átlagosan hány napot töltenek az alkalmazottak a 435-ös épületben? Hány nap van összesen a 312-es épület vakolási munkáira? Hány különböző szakterület létezik?

E kérdések megválaszolásához statisztikai függvényekre van szükség, amelyek egy táblázat sok sorát nézik, és egyetlen értéket adnak vissza. Öt ilyen függvény van az SQL-ben, ezeket beépített függvényeknek vagy beállított függvényeknek nevezzük. Ezek a funkciók a SUM (összeg), AVG (átlag), COUNT (mennyiség), MAX (maximum) és MIN (minimum).

Beépített funkció (beállítás funkció). Több sorban működő statisztikai függvény: SUM (összeg), AVG (átlag), COUNT (mennyiség), MAX (maximum), MIN (minimum).

Kérés: Melyek a maximális és minimális óradíjak?

MAX(HRLY_RATE), MIN(HRLY_RATE) KIVÁLASZTÁS

Eredmény: 17.40, 8.20

MAX funkciókés a MIN egy táblázatoszlopon működik. Ebből az oszlopból kiválasztják a maximális vagy minimális értéket. A lekérdezés megfogalmazásunk nem tartalmaz WHERE záradékot. A legtöbb lekérdezés esetében ez nem biztos, hogy így van, ahogy a következő példánk is mutatja.

Kérés:Átlagosan hány napot töltenek az alkalmazottak a 435-ös épületben?

AVG KIVÁLASZTÁSA (NUM_DAYS)

WHERE BLDG_ID =435

Eredmény: 12.33

Kérés: Hány nap van összesen a 312-es épület vakolási munkáira?

SZUM VÁLASZTÁSA (NUM_DAYS)

MEGJEGYZÉSBŐL, MUNKÁS

WHERE WORKER.WORKER_ID = MEGJEGYZÉS.MUNKÁS_ID ÉS

SKILL_TYPE = "Gipszoló" ÉS

Eredmény: 27

A megoldás az ASSIGNMENT és a WORKER táblák összekapcsolását használja. Erre azért van szükség, mert a SKILL_TYPE a WORKER táblában, a BLDG_ID pedig a ASSIGNMENT táblában található.

Kérés: Hány különböző szakterület létezik?

SZÁM KIVÁLASZTÁSA (DISTINCT SKILL_TYPE)

Eredmény: 4

Mivel ugyanaz a szakterület több különböző sorban is megjelenhet, a DISTINCT kulcsszót kell használnia ebben a lekérdezésben, nehogy a rendszer egynél többször számolja ugyanazt a szakterületet. A DISTINCT operátor bármelyik beépített funkcióval használható, bár természetesen redundáns a MAX és MIN funkciókkal.

KÜLÖNBÖZŐ. Operátor, amely kiküszöböli az ismétlődő sorokat.

A SUM és AVG függvényeket csak numerikus oszlopokkal szabad használni. Más funkciók numerikus és karakteres adatokkal is használhatók. A COUNT kivételével minden függvény használható számított kifejezésekkel. Például:

Kérés: Mennyi az átlag heti fizetés?

AVG KIVÁLASZTÁSA (40 * HRLY_RATE)

Eredmény: 509.14

A COUNT egy teljes sorra utalhat, nem pedig egy oszlopra :

Kérés: Hány épület rendelkezik 3-as minőségi fokozattal?

SZÁM KIVÁLASZTÁSA (*)

ÉPÜLETBŐL, HONNAN

Eredmény: 3

Ahogy ezek a példák is mutatják, ha egy SELECT parancs beépített függvényt tartalmaz, akkor semmi más nem jelenhet meg abban a SELECT parancsban. Az egyetlen kivétel ez alól a szabály alól a GROUP BY záradék, amelyet most megvizsgálunk.

GROUP BY és HAVING záradékok

A menedzsmentben gyakran szükség van statisztikai információkra az egyes csoportokról sok csoportban. Vegyük például a következő lekérdezést:

Kérés: Minden vezetőnél tájékozódjon a beosztottak közül a maximális órabérről.

A probléma megoldásához a dolgozókat vezetőik szerint csoportokra kell osztanunk. Ezután minden csoporton belül meghatározzuk a maximális ajánlatot. SQL-ben ez a következőképpen történik:

CSOPORTOSÍTÁS SUPV_ID SZERINT

Eredmény:

SUPV_IDMAX(HRLY RATE)

A lekérdezés feldolgozása során a rendszer először csoportokra osztja a WORKER tábla sorait a következő szabály segítségével. A sorok akkor és csak akkor kerülnek ugyanabba a csoportba, ha azonos SUPV_ID-vel rendelkeznek. A SELECT záradék ezután minden csoportra vonatkozik. Mivel ebben a csoportban csak egy SUPV_ID érték van, a csoportban nincs SUPV_ID bizonytalanság. A SELECT záradék minden csoporthoz kiadja a SUPV_ID-t, valamint kiszámítja és kiadja a MAX(HRLY_RATE) értéket. Az eredményt fent mutatjuk be.

A beépített függvényekkel rendelkező SELECT parancsban csak azok az oszlopok jelenhetnek meg, amelyek a GROUP BY záradékban szerepelnek. Vegye figyelembe, hogy a SUPV_ID használható a SELECT parancsban, mert a GROUP BY záradékban szerepel.

GROUP BY záradék. Azt jelzi, hogy a sorokat csoportokra kell osztani a megadott oszlop(ok) közös értékeivel.

A GROUP BY záradék lehetővé teszi bizonyos összetett számítások elvégzését. Például érdemes megtudnunk ezeknek a maximális ajánlatoknak az átlagát. A beépített függvényekkel végzett számítás azonban korlátozott abban az értelemben, hogy nem teszi lehetővé a beépített függvények más beépített függvényeken belüli használatát. Szóval olyan kifejezés, mint

AVG(MAX(HRLY_RATE))

tiltott. Egy ilyen kérés végrehajtása két szakaszból áll. Először a maximális ajánlatokat kell egy új táblázatba feltenni, majd második lépésben ki kell számítanunk azok átlagát.

Használhatja a WHERE záradékot a GROUP BY paranccsal:

Kérés: Minden épülettípusnál tájékozódjon átlagos szint minőség az 1. státuszú épületek között.

TÍPUS KIVÁLASZTÁSA, AVG (QLTY_LEVEL)

AHOL ÁLLAPOT = 1

Eredmény:

TYPEAVG (QLTY_SZINT)

1. üzlet

Lakóépület 3

A WHERE záradék a GROUP BY utasítás előtt kerül végrehajtásra. Így egyetlen csoport sem tartalmazhat 1-től eltérő állapotú sort. Az 1. állapot sorai a TYPE érték szerint vannak csoportosítva, majd minden csoportra egy SELECT záradék kerül alkalmazásra.

HAVING kifejezést. Feltételeket helyez el a csoportokon.

A GROUP BY záradék által létrehozott csoportokra is alkalmazhatunk feltételeket. Ez a HAVING kifejezés használatával történik. Tegyük fel például, hogy úgy döntöttünk, hogy az előző lekérdezések egyikét pontosítjuk:

Kérés: Minden olyan vezető esetében, akinek egynél több beosztottja van, tájékozódjon a maximális órabérről a beosztottjai között.

Ezt a feltételt a megfelelő HAVING paranccsal tükrözhetjük:

SUPV_ID, MAX (HRLY_RATE) KIVÁLASZTÁSA

MUNKAVÁLLALÁSI CSOPORTBÓL, SUPV_ID

HAVING COUNT(*) > 1

Eredmény:

SUPV_ID MAX (HRLY_RATE)

A WHERE és a HAVING záradék közötti különbség az, hogy a WHERE a sorokra, míg a HAVING a csoportokra vonatkozik.

A lekérdezés tartalmazhat egy WHERE és egy HAVING záradékot is. Ebben az esetben először a WHERE záradék kerül végrehajtásra, mert a csoportosítás előtt kerül végrehajtásra. Vegyük például a korábbi lekérdezés következő módosítását:

Kérés: Minden egyes épülettípusnál derítse ki az 1-es állapotú épületek átlagos minőségi szintjét. Csak azokat az épülettípusokat vegye figyelembe, amelyek maximális minőségi szintje nem haladja meg a 3-at.

TÍPUS KIVÁLASZTÁSA, AVG (QLTY_JLEVEL)

AHOL ÁLLAPOT = 1

MAXIMÁLIS (QLTY_SZINT)<= 3

Eredmény:

AVG TÍPUS (QLTY_LEVEL)

1. üzlet

Lakóépület 3

Vegye figyelembe, hogy a FROM záradékkal kezdődően a kitételek sorrendben kerülnek végrehajtásra, majd a SELECT záradék kerül alkalmazásra. Így a WHERE záradék kerül alkalmazásra a BUILDING táblára, és minden olyan sor törlődik, amelyben a STATUS eltér 1-től. A fennmaradó sorok TYPE szerint vannak csoportosítva; minden sor azonos TYPE értékkel ugyanabba a csoportba kerül. Így több csoport jön létre, minden TYPE értékhez egy. A HAVING záradék ezután minden csoportra vonatkozik, és azokat a csoportokat, amelyek maximális minőségi szintje meghaladja a 3-at, eltávolítják. Végül a SELECT záradékot alkalmazzuk a többi csoportra.

7. Beépített függvények és segédlekérdezések

A beépített függvények csak SELECT záradékban vagy HAVING parancsban használhatók. A soron belüli függvényt tartalmazó SELECT záradék azonban része lehet egy részlekérdezésnek. Nézzünk egy példát egy ilyen segédlekérdezésre:

Kérés: Mely munkavállalók órabére magasabb az átlagosnál?

SELECT WORKER_NAME

WHERE HRLY_RATE >

(AVG (HRLY_RATE) KIVÁLASZTÁSA

Eredmény:

H. Columbus

Vegye figyelembe, hogy az allekérdezés nincs összefüggésben a fő lekérdezéssel. Az allekérdezés pontosan egy értéket ad vissza – az átlagos óradíjat. A fő lekérdezés csak akkor választ ki egy dolgozót, ha a ráta nagyobb, mint a számított átlag.

A kapcsolódó lekérdezések beépített függvényeket is használhatnak:

Lekérdezés: Ugyanazon vezető beosztottjai közül melyik alkalmazottnál magasabb az átlagos órabér?

Ebben az esetben ahelyett, hogy minden dolgozóra egy átlagos óradíjat számítanánk ki, az átlagos munkabért kell kiszámítanunk az ugyanannak a vezetőnek beosztott munkavállalói csoportokra. Sőt, a számítást újra el kell végezni a fő lekérdezésben szereplő minden egyes dolgozóra:

SELECT A. WORKER_NAME

Az SQL lehetővé teszi a lekérdezések egymásba ágyazását. Egy segédlekérdezés általában egyetlen értéket ad vissza, amelyet a rendszer ellenőrzi, hogy az állítmány igaz-e.

A keresési kifejezések típusai:
. Összehasonlítás egy segédlekérdezés eredményével (=, >=)
. Egy segédlekérdezés eredményeihez való tartozás ellenőrzése (IN)
. Létezés ellenőrzése (EXISTS)
. Többszörös (mennyiségi) összehasonlítás (ANY, ALL)

Megjegyzések a beágyazott lekérdezésekhez:
. Egy részlekérdezésnek csak egy oszlopot kell kijelölnie (kivéve az EXISTS predikátummal rendelkező részlekérdezést), és az eredmény adattípusának meg kell egyeznie a predikátumban megadott érték adattípusával.
. Bizonyos esetekben használhatja a DISTINCT kulcsszót annak biztosítására, hogy egyetlen érték kerüljön visszaadásra.
. Nem tartalmazhat ORDER BY vagy UNION záradékot egy részlekérdezésben.
. Az allekérdezés a keresési feltételtől balra vagy jobbra is elhelyezhető.
. Az allekérdezések GROUP BY záradék nélkül használhatnak összesítő függvényeket, amelyek automatikusan egy speciális értéket adnak vissza tetszőleges számú sorhoz, egy speciális IN predikátumot és oszlopalapú kifejezéseket.
. Amikor csak lehetséges, használjon JOIN táblaillesztéseket az allekérdezések helyett.

Példák beágyazott lekérdezésekre:

SELECT * FROM Rendelések WHERE SNum=(SELECT SNum FROM SalesPeople WHERE SName=’Motika’)
SELECT * FROM Rendelések WHERE SNum IN (SELECT SNum FROM SalesPeople WHERE City=’London’)
SELECT * FROM Rendelések WHERE SNum=(SELECT DISTINCT SNum FROM Orders WHERE CNum=2001)
SELECT * FROM Orders WHERE Amt>(SELECT AVG(Amt) FROM Orders WHERE Odate=10/04/1990)
SELECT * FROM Customer WHERE CNum=(SELECT SNum+1000 FROM SalesPeople WHERE SName=’Serres’)

2) Kapcsolódó segédlekérdezések

Az SQL-ben olyan segédlekérdezéseket hozhat létre, amelyek egy külső lekérdezésből származó táblára hivatkoznak. Ebben az esetben az allekérdezés többször végrehajtásra kerül, egyszer a külső lekérdezés tábla minden sorához. Ezért fontos, hogy az allekérdezés az indexet használja. Egy segédlekérdezés hozzáférhet ugyanahhoz a táblához, mint egy külső. Ha a külső lekérdezés viszonylag kis számú sort ad vissza, akkor a csatolt részlekérdezés gyorsabb lesz, mint a nem csatolt. Ha egy részlekérdezés kis számú sort ad vissza, a kapcsolódó lekérdezés lassabb lesz, mint a nem kapcsolódó lekérdezés.

Példák kapcsolódó részlekérdezésekre:

SELECT * FROM SalesPeople Main WHERE 1(SELECT AVG(Amt) FROM Orders O2 WHERE O2.CNum=O1.CNum) //visszaadja az összes olyan rendelést, amelynek értéke meghaladja az adott ügyfél átlagos rendelési értékét

3) A predikátum LÉTEZIK

Szintaktikai forma: LÉTEZIK ()

Az állítmány egy segédlekérdezést vesz argumentumként, és igazra értékeli, ha a részlekérdezés kimenettel rendelkezik, egyébként pedig false. Az allekérdezés egyszer végrehajtásra kerül, és több oszlopot is tartalmazhat, mivel azok értékeit nem ellenőrzik, hanem egyszerűen rögzítik a sorok jelenlétének eredményét.

Megjegyzések az EXISTS predikátumhoz:
. Az EXISTS egy predikátum, amely IGAZ vagy HAMIS értéket ad vissza, és önmagában vagy más logikai kifejezésekkel együtt használható.
. Az EXISTS nem használhat összesítő függvényeket az allekérdezésében.
. A korrelált részlekérdezésekben az EXISTS predikátum végrehajtásra kerül a külső tábla minden sorában.
. Kombinálhatja az EXISTS predikátumot táblaillesztésekkel.

Példák az EXISTS predikátumra:

SELECT * FROM Customer WHERE EXISTS(SELECT * FROM Customer WHERE City=’San Jose’) – minden ügyfelet visszaküld, ha valamelyikük San Joséban él.
SELECT DISTINCT SNum FROM Customer First WHERE NOT EXISTS (SELECT * FROM Customer Send WHERE Send.SNum=First.SNum AND Send.CNumFirst.CNum) – azon eladók számát adja vissza, akik csak egy ügyfelet szolgáltak ki.
SELECT DISTINCT F.SNum, SNum, F.City FROM SalesPeople F, Customer S WHERE EXISTS (SELECT * FROM Customer T WHERE S.SNum=T.SNum ÉS S.CNumT.CNum ÉS F.SNum=S.SNum) – visszatér a több ügyfelet kiszolgáló összes eladó száma, neve és lakóhelye.
SELECT * FROM SalesPeople Frst WHERE EXISTS (SELECT * FROM Customer Send WHERE Frst.SNum=Send.SNum AND 1

4) A mennyiségi összehasonlítás predikátumai

Szintaktikai forma: (=|>|=|) BÁRMELYIK|MINDEN ()

Ezek a predikátumok egy segédlekérdezést használnak argumentumként, azonban az EXISTS predikátumhoz képest a relációs predikátumokkal (=,>=) együtt használatosak. Ebben az értelemben hasonlóak az IN predikátumhoz, de csak segédlekérdezéseknél használatosak. A szabvány lehetővé teszi a SOME kulcsszó használatát ANY helyett, de nem minden DBMS támogatja ezt.

Megjegyzések az összehasonlító predikátumokhoz:
. Az ALL predikátum kiértékelése IGAZ, ha az allekérdezés végrehajtása során kiválasztott minden egyes érték megfelel a külső lekérdezési predikátumban megadott feltételnek. Leggyakrabban egyenlőtlenségekkel használják.
. Az ANY predikátum kiértékelése IGAZ, ha az allekérdezés végrehajtása során kiválasztott legalább egy érték megfelel a külső lekérdezési predikátumban megadott feltételnek. Leggyakrabban egyenlőtlenségekkel használják.
. Ha az allekérdezés nem ad vissza egyetlen sort sem, akkor az ALL automatikusan IGAZ értéket vesz fel (az összehasonlítási feltétel teljesültnek tekintendő), ANY esetén pedig a FALSE értéket veszi fel.
. Ha az összehasonlítás IGAZ egyetlen sor esetén sem, és van egy vagy több sor NULL értékkel, akkor az ANY az UNKNOWN értéket adja vissza.
. Ha az összehasonlítás HAMIS egyetlen sor esetén sem, és van egy vagy több NULL értékű sor, akkor az ALL értéke UNKNOWN.

Példák a mennyiségi összehasonlítás predikátumára:

SELECT * FROM SalesPeople WHERE City=ANY(Válasszon várost az ügyféltől)
SELECT * FROM Megrendelések WHERE Amt ALL (SELECT Rating FROM Customer WHERE City=’Róma’)

5) Egyediség állítmány

EGYEDI|KÜLÖNBÖZŐ ()

A predikátum az allekérdezés kimeneti adatai egyediségének (másodpéldányok hiányának) ellenőrzésére szolgál. Ezenkívül az UNIQUT predikátumban a NULL értékű karakterláncokat egyedinek tekintjük, a DISTINCT predikátumban pedig két meghatározatlan értéket tekintünk egyenlőnek egymással.

6) Párosítsa az állítmányt

MÉRKŐZÉS ()

A MATCH predikátum azt teszteli, hogy egy lekérdezési karakterlánc értéke megegyezik-e az allekérdezésből származó bármely karakterlánc értékével. Ez az allekérdezés abban különbözik az IN AND ANY predikátumtól, hogy lehetővé teszi a „részleges” (PARTIAL) egyezések feldolgozását, amelyek olyan sorok között fordulhatnak elő, amelyeknek néhány NULL értéke van.

7) Lekérdezések a FROM részben

Valójában legális az allekérdezés használata mindenhol, ahol engedélyezett a táblahivatkozás.

SELECT CName, Tot_Amt FROM Customer, (SELECT CNum, SUM(Amt) AS Tot_Amt FROM Orders GROUP BY CNum) WHERE City=’London’ AND Customer.CNum=Orders.CNum
//subquery az egyes londoni ügyfelek által leadott megrendelések teljes mennyiségét adja vissza.

8) Rekurzív lekérdezések

REKURSÍVVAL
Q1 AS KIVÁLASZTÁS… FROM… HOL…
Q2 AS KIVÁLASZTÁS… FROM… HOL…