SQL тілі DML тілінің басқа операторларында ішкі сұратуларды пайдалануға мүмкіндік береді, SELECT операторымен анықталатын ішкі сұратулар болып табылады.
Ішкісұрату- SQL тілінің өте қуатты құралы. Ол нәтижелік жиындарды құру процесінде немесе мәліметтерді өзгерту (DELETE , INSERT , UPDATE ) операторларының бірін орындайтын сұратулардың күрделі иерархиясын құруға мүмкіндік береді.
Шартты түрде ішкі сұратуларды үшке бөледі, олардың әрқайсысы алдынғысының салдары болып табылады:
ішкі сұрату келесі мәселерді шешуге мүмкіндік береді:
Кейбір ДҚБЖ (мысалы Oracle ДҚБЖ) ішкі сұрату негізінде CREATE TABLE операторының көмегімен жаңа кестелер құруға мүмкіндік береді.
Ішкі сұратуы қолданудың қарапайым мысалы ретінде келесі операторды қарастыруға болады:
SELECT * from tbl1
WHERE f2=(SELECT f2 FROM tbl2
WHERE f1=1);
Берілге операторда ішкі сұрату предикатта қайталанатын жалғаз мәнді қайтару керек. Егер ішкі сұрату бір мәннен көп қайтарса, онда ДҚБЖ SQL-оператордың қатесі туралы хабар шығарады.
Егер ішкі сұрату бірді бір жолды таңдамаса, онда предикат UNKNOWN тең болады, ДҚБЖ көбінде FALSE деп көрсетіледі.
Стандарт предикаттың жазылуын «ішкі сұрату операторының мәні» түрінде предикаттың жазылуын анықтайды.
Мысалы:
SELECT * from tbl1 WHERE
(SELECT f2 FROM tbl2 WHERE f1=1) = f2;
Көбінесе ішкі сұратулармен бірге агрегацияланатын функциялар қолданылады, олар шартты «топ бойынша ортадан жоғары» типінде анықтауға мүмкіндік береді.
Мысалы:
SELECT f1,f2,f3 FROM tbl1
WHERE f2> (SELECT AVG(f2) FROM tbl1);
Егер ішкі сұрату нәтижесі жолдар тобы (предикат тексеретін ішкі сұратудың мәнін жалғыздығына шарт кепілдік бермеген жағдайда) болса, онда көрсетілген жиындардан бір мәнді таңдауға мүмкіндік беретін IN операторын қолданған дұрыс.
Мысалы:
SELECT * from tbl1 WHERE
f2 IN (SELECT f2 FROM tbl2 WHERE f1=1);
Бұл жағдайда предикат TRUE мәніне ие болады, егер ішкі сұрату қайтаратын мәндердің біреуін қабылдайтын болса, онда шарт орындалады.
Бірақ IN операторының қолдануының кейбір мағыналық жетіспеушіліктері бар: сұратуда нәтижесінде қанша жол болу керектігі нақты анықталмаған. Шынайы мәліметтер моделін құру кезде бұл қандайда бір бірмәнсәздікке немесе мәліметтердің өзіне тәуелділікке әкелуі мүмкін. Кері жағдайда егер мәліметтер моделі сқраудысқраудың нәтижесі ретінде бір ғана жолды болжайтын болса, сәйкесәнше слыстыру = операторы қолданылатын болса, сұрату нәтижесі бірнеше жол болғанда мәліметтер құрылымы мәндерді енгізуге мүмкіндік берген кезде, онда осындай SQL-операторын қолданған кезде қате көрсетуі мүмкін.
Егер сұратуда екі кестеден көп кезіксе, өрістердің аттары көрнектірек болу үшін нүктеден кейін кестенің аты беріледі. Стандартта өріс атын кесте атымен бекітуді қажет етпейді, егер бірмәнділік тумаса (өріс бірінші ағымдағы сұратудың FROM жазбасы бар кестеден ізделеді де, кейін сыртқы сұратудан ізделеді).
Көбінесе сұратуды пайдалану арқылы SELECT операторының жазбасының орнына біріктіруді қолданады. Бірақ тәжірибеде ДҚБЖ көбі сұратуды тиімді пайдаланады. Жылдам әрекет бойынша критикалық талаптары бар кешендік программаларды жобалауда, өндіруші нақты бір ДҚБЖ үшін SQL-операторды орындау жоспарын сұрыптау қажет.
Неғұрлым алдыңғы қатардағы ДҚБЖ Oracle сияқты, SQL тілінің нақты операторларын орындалу өнімділігін бағалауға мүмкіндік беретін SQL-операторлар қатарын ұсынады.
Ішкі сұрату WHERE жазбасымен анықталатын предикатта көрсетілгендей, HAVING жазбасымен анықталатын топтар бойынша предикатта да көрсетіледі.
Мысалы:
SELECT avg_f1, COUNT (f2) from tbl1
GROUP BY avg_f1
HAVING avg_f1 >(SELECT f1 FROM tbl1
WHERE f3='a1');
Корремирленген ішкі сұратулар
SELECT операторында ішкі сұратудан онда көрсетілген сыртқы бағандарға сілтеме жасауға болады. Мұндай ішкі сұрату кестенің әр жолы үшін құрылатын нәтижелік жиынға оның кіру шартын анықтай отырып орындалады.
Мысалы:
SELECT * from tbl1 t1
WHERE f2 IN (SELECT f2 FROM tbl2 t2
WHERE t1.f3=t2.f3);
Келтірілген жағдайда tbl1 кестесінің әр жолы үшін келесі шарт тексеріледі: f2 өрісінің мәні tbl2 кестесінің жолының мәнімен сәйкес келуі керек, мұндағы f3 өрісінің мәні (tbl1) сыртқы кестесінің f3 өрісінің мәніне тең болу қажет. Бұл коррелирленген ішкіссұратудың қарапайым мысалы.
Көбінесе ішкі сұрату сыртқы кестенің деректерін қолдануы талап етіледі. Бұл жаідайда алиастарды мүндетті түрде қолдану керек.
Мысалы:
SELECT * from tbl1 t_out
WHERE f2< (SELECT AVG(f2) FROM tbl1 t_in
WHERE t_out.f1= t_in.f1);
Коррелирленген ішкі сұрату жағдайында HAVING жазбасында тек қана агрегацияланатын кестелерді ғана пайдалану қажет. Себебі әр ішкі сұратудың огрындалу кезінде тексеруші жол санатында, оның мәндеріне ішкі сұрату қолжетімге ие, негізгі сұратудың агрегацияланатын функциясы негізіндегі жолдарды топтастыру нәтижесі алынады.
Мысалы:
SELECT f1, COUNT(*), SUM(f2) from tbl1 t1
GROUP BY f1
HAVING SUM(f2)> (SELECT MIN(f2)*4
FROM tbl1 t1_in
WHERE t1.f1=t1_in.f1);
Бірнеше жол қайтаратын ішкі сұрату үшін предикатты құру.
Егер преликатта мәнді қандайда бір жиынмен салыстыру үшін жоғарыда көрсетілгендей IN операторын қолдануға болады.
Ішкі сұратудың нақты бір шартын қанағаттандыратын жол барлығын тексеру үшін, EXISTS операторы қолданылады.
Мысалы:
SELECT f1,f2,f3 from tbl1
WHERE EXISTS (SELECT * FROM tbl1
WHERE f4='10/11/2003');
Бұл сұрату бос емес нәтижелік жиын тек қана кестенің f4 бағанының қандайда бір мәніне мерзім енгізілген жағдайда ғана құрады, мысалы: '10/11/2003'.
Ішкі сұрату нәтижелерімен EXISTS операторын қолданудың жетістіктері ішкі сұрату жолдар жиыны сияқты, бағандар жиынында қайтара алады.
Коррелирленген ішкі сұратуда EXISTS операторы сыртқы сұратудың әр жолы үшін әр кез есептеліп отырады.
SQL-92 стандартында EXISTS операторы қолданылатын ішкі сұратуларда агрегациялық функцияларды қолдану қарастырылмаған. Бірақ кейбір ДҚБЖ ішкі сұратулардың мынандай түрлері кездеседі.
Ішкі сұрату нәтижесін предикатт қолдану үшін сонымен қатар алдыңғы дәрістерде тереңінек қарастырылған ANY және ALL операторлары қолданылады.
ANY операторын қолдану мысалын көрсетейік:
SELECT f1,f2,f3 from tbl1
WHERE f3 = ANY (SELECT f3 FROM tbl2);
Аталаған оператор нәтижелік жиынға f3 бағандарының мәндері tbl2 кестесінде кездесетін барлық жолдар қосылады.
Мәліметтерді өзгерту операторларында ішкі сұратуларды қолдану
DML тілінің операторларына, SELECT операторынан басқа, кестелерді деректерді өзгертуге мүмкіндік беретін операторлар жатады. Бұл INSERT операторы, кестеге бір немес бірнеше жол қосады, DELETE операторы, кестеден бір немес бірнеше жолды алып тастайды, UPDATE операторы, кестенің бағандарының мәндерін өзгертеді.
INSERT операторы
INSERT операторы SQL-92 стандартында келесі формальды жазбаға ие:
INSERT INTO table_name
[ (field .,:) ]
{ VALUES (value .,:) }
| subquery | {DEFAULT VALUES};
INSERT операторы кестеге бір немесе бірнеше жол қоса алады. Өрістер тізімі (field .,:) өрістер атауларын көрсетеді немесе VALUES жазбасымен анықталатын мәндер тізімі арасынан оларға мән егізу реті, немесе ішкі сұратудың орындалу нәтижесін дер көрсетеді.
VALUES жазбасымен анықталатын тізім кесте мәндерінің конструкторы деп аталады, үтіри арқылы жақшаларға алынады.
Егер (field .,:) өрістер тізімі түсірілген болса, онда мәндерді енгізу реті берілген кестені құру кезінде CREATE TABLE операторындат көрсетілген бағандар ретіне сәйкес болады.
Егер NOT NULL шектелуі қойылған бағандар үшін қосылатын деректер көрсетілмеген болса, онда ДҚБЖ SQL-операторының орындалуындағы қатені көрсетеді.
Келесі INSERT операторы ішкі сұрату негізінде орындалатын tbl2 кестесінің жолдарын көшірілуін көрсетеді
INSERT INTO tbl1(f1,f2,f3)
(SELECT f1,f2,f3 FROM tbl2);
Өрістер тізімінде көрсетілген өрістер саны және осы өрістердің деректер типі кестелер мәнінің конструкторы неиес ішкі сұрату арқылы қолданылатын нәтижелік жиындағы өрәстер саны мен деректер типіне сәйкес болу қажет.
DELETE операторы
DELETE операторы SQL-92 стандартында келесі формальды сипаттауға ие:
DELETE FROM table_name
[ { WHERE condition }
| { WHERE CURRENT OF cursor_name } ];
DELETE операторы WHERE жазбасында шартпен көрсетілген жолдарды кестелерден жояды (іздеуші жою, searched deletion) немесе WHERE CURRENT OF (позициялық жою, positioned deletion).
WHERE CURRENT OF жазбасымен анықталатын позициялық жою, курсордағы жолдарды жоя отырып, қорында осы курсор құрылған деректер қорының кестесінен жояды.
Егер DELETE операторы қандайда бір ұсынымда қолданылатын болса, онда деректер соңғы кесте негізінде құрылған деректер қорынан мәліметтер жойылады.
Егер WHERE жазбасы болмасы, немесе WHERE жазбасындағы предикат ылғи да TRUE мәніне ие болатын болса, онда DELETE операторы кестеден барлық жолдарды жойп жіберетінін ешқашан ұмытпау қажет.
UPDATE операторы
UPDATE операторы SQL-92 стандартында келесі формальды сипаттауға ие:
UPDATE table_name SET { field =
{ expr | NULL | DEFAULT }}.,
[ { WHERE condition }
| { WHERE CURRENT OF cursor_name } ];
UPDATE операторы кесте деректеріне өзгерістер енгізу үшін қажет.
Expr өрнегі, бағандар мәндерін есептеуге арналған, қарапйым өрнек те және жалғыз мән қайтаратын ішкі сұратуда бола алады. Өрнекте өзгеретін бағанның немесе ағымдағы жазбаның ескі мәндеріне сілтеме жасауға болады.
Бағандар мәнін анықтаған кезде CASE шартты өрнегін және типтерді көрсету үшін CAST өрнектерін пайдалануға болады.
Мысалы:
SELECT f1, CAST (f2 AS CHAR),
CAST (f3 AS CHAR) from tbl1;UPDATE tbl2 SET f2 = (SELECT CAST (f2 AS CHAR)
from tbl1 WHERE f1=1);UPDATE tbl2 SET f5 = (SELECT CAST (f5 AS DATE)
from tbl1 WHERE f1=1),
f6= CAST ('10/12/2003' AS DATE);
CASE шартты өрнегі
CASE шартты өрнегі берілген шартқа негізделіп бірнеше мәндердің біреуін таңдауға мүмкіндік береді.
CASE шартты өрнегі келесі формальды сипаттауға ие:
{ CASE { expr WHEN expr THEN { expr | NULL }}
| { WHEN expr THEN { expr | NULL }}
[ ELSE { expr | NULL } ] END}
| { NULLIF {expr1,expr2) }
| {COALESCE (expr .,:) }
CASE шартты өрнегі төрт нысанда жазылуы мүмкін:
· SELECT f1, CASE f3 ·
WHEN 'abc' THEN '1_abc' END FROM tbl1;
· SELECT f1, CASE ·
WHEN f3= 'abc' THEN '1_abc' ·
ELSE f3 END FROM tbl1;
· UPDATE tbl2 SET f3 = (SELECT NULLIF (f3,'aaa') FROM tbl1 WHERE f1=1);
· INSERT INTO tbl1(f1,f2)·
VALUES (1+ COALESCE(SELECT MAX(f1)·
FROM tbl1, 0 ), 100);
UPDATE операторы дұрыс орындалу үшін келесі шарттар тізімі қажет: