Вгнездени и поврзани подпрашања во SQL, ПОСТОИ предикат. Користење на операторот EXISTS Queries користејќи ја функцијата exists

КАДЕ ПОСТОИ

Подпрашањето се проверува за присуство на еден или повеќе редови. Ако барем еден ред се совпаѓа со барањето, се враќа буловата вредност TRUE. Кога е наведен опционалниот клучен збор НЕ, Буловата вредност TRUE се враќа ако подпрашањето не врати соодветни редови.

подпрашање

Врз основа на целосно формираното подпрашање, добиениот сет на податоци се презема.

Општи правила

Операторот EXISTS го тестира постоењето на еден или повеќе редови во подпрашање на родителско барање.

SELECT * FROM jobs WHERE NOT EXISTS (SELECT * FROM вработен WHERE jobs.job_id=employye.job_id);

Овој пример проверува во подпрашањето за записи користејќи го дополнителниот клучен збор НЕ. Следниот пример бара одредени записи во подпрашање за да го врати главниот сет на резултати.

ИЗБЕРИ au_lиме ОД автори КАДЕ ПОСТОИ (ИЗБЕРИ * ОД издавачи КАДЕ authors.city=publishers.city);

Ова барање ги враќа презимињата на авторите (au_lname) кои живеат во истиот град со издавачите. Забележете дека можете да користите ѕвездичка во подпрашањето бидејќи подпрашањето мора да врати само еден запис со буловата вредност ТОЧНО. Во такви случаи, колоните не се важни. Клучната точка е постоењето на стрингот.

Во многу прашања, операторот EXISTS ја извршува истата функција како ANY. Операторот EXISTS е обично најефикасен кога се користи со корелирани прашања.

Операторот EXISTS е семантички еквивалентен на операторот ANY.

Подпрашање во изјавата EXISTS обично врши еден од двата типа на пребарувања. Првата опција е да користите џокер - ѕвездичка (на пример, SELECT * FROM...), во тој случај не преземате некоја специфична колона или вредност. Ѕвездичката овде значи „која било колона“. Втората опција е да изберете само една специфична колона во подпрашањето (на пример, SELECT aujd FROM). Некои поединечни платформи дозволуваат подпрашања на повеќе колони (на пр. SELECT aujd, aujname FROM...). Сепак, оваа функција е ретка и треба да се избегнува во кодот што треба да се пренесе на други платформи.

Разлики помеѓу платформи

Сите платформи го поддржуваат операторот EXISTS во формата што ја опишавме погоре.

„Порано беше полесно“ - си помислив додека седнав да го оптимизирам следното барање во SQL менаџмент студио. Кога пишував под MySQL, сè беше навистина поедноставно - или работи или не. Или успорува или не. Објаснете ми ги реши сите проблеми, ништо повеќе не беше потребно. Сега имам моќно опкружување за развој, дебагирање и оптимизирање на прашања и процедури/функции, и сето ова неред само создава повеќе проблеми според мене. И зошто сите? Бидејќи вградениот оптимизатор за пребарување е злобен. Ако во MySQL и PostgreSQL пишувам

Изберете * од a, b, c каде што a.id = b.id, b.id = c.id

и секој од таблетите ќе има најмалку 5k линии - сè ќе замрзне. И фала му на Бога! Затоа што во спротивно програмерот, во најдобар случај, развива мрзеливост да пишува правилно, а во најлош случај, воопшто не разбира што прави! На крајот на краиштата, истото барање во MSSQL ќе работи слично

Изберете * од спојување b на a.id = b.id приклучи се c на b.id = c.id

Вградениот оптимизатор ќе го чешла вишокот барање и се ќе биде во ред.

Тој, исто така, сам ќе одлучи што е подобро да прави - постои или се придружува и многу повеќе. И сè ќе работи што е можно пооптимално.

Има само едно НО. Во еден момент, оптимизаторот ќе се сопне сложено барањеи поминува, а потоа добивате огромен проблем. И можеби нема да добиете веднаш, но кога тежината на масите ќе достигне критична маса.

Значи, тука е поентата на статијата. постои и во се многу тешки операции. Ова е всушност посебно подпрашање за секојрезултат линии. И ако има и гнездење, тогаш генерално ги гасне светлата. Се ќе биде ок кога ќе се вратат 1, 10, 50 линии. Нема да ја почувствувате разликата, а можеби приклучувањето ќе биде уште побавно. Но, кога ќе се извлечат 500, почнуваат проблемите. 500 подпрашања во рамките на едно барање се сериозни.

Иако од гледна точка на човечкото разбирање, во и постои се подобри, но од гледна точка на временските трошоци за прашања кои враќаат 50+ редови, тие не се прифатливи.

Потребно е да се направи резервација која, нормално, ако некаде се намали, мора некаде да стигне. Да, придружувањето е поинтензивно за меморија, бидејќи држењето на целата табела со вредности одеднаш и работењето со неа е поскапо од извршувањето подпрашања за секој ред, со што брзо се ослободува меморијата. Треба да го погледнете конкретно барањето и да измерите дали употребата на дополнителна меморија за доброто на времето ќе биде критична или не.

Ќе дадам примери на целосни аналогии. Општо земено, сè уште не сум сретнал прашања од таков степен на сложеност што не може да се прошири во каскада од спојувања. Можеби ќе потрае еден ден, но се може да се открие.

Изберете * од a каде a.id во (изберете id од b) изберете * од a каде што постои (изберете го врвот 1 1 од b каде b.id = a.id) изберете * од приклучување b на a.id = b. id изберете * од a каде што a.id не е во (изберете id од b) изберете * од a каде што не постои (изберете го врвот 1 1 од b каде b.id = a.id) изберете * од лево приклучете b на a. id = b.id каде b.id е нула

Повторувам - MSSQL оптимизатор ги оптимизира овие примери за максимални перформансии никогаш нема да има глупави луѓе со толку едноставни барања.

Ајде сега да разгледаме пример за вистинско барање што мораше да се препише бидејќи едноставно се замрзна на некои примероци (структурата е многу поедноставена и концептите се заменети, нема потреба да се плашиме од некоја неоптималност на структурата на базата на податоци ).

Треба да ги извадите сите дупликати „производи“ во различни сметки, фокусирајќи се на параметрите на производот, неговата група и матичната група, доколку постои.

Изберете d.PRODUCT_ID од PRODUCT s, PRODUCT_GROUP sg лево придружете се M_PG_DEPENDENCY sd вклучено (sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID), PRODUCT d, PRODUCT_GROUP ддPYGDE лево, PRODUCT_GROUP во M_PG_DEPENDENCY dg. UP_ID = дд .M_PG_DEPENDENCY_CHILD_ID) каде што s.PRODUCT_GROUP_ID=sg .PRODUCT_GROUP_ID и d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID и sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC и sg.PRODUCT_GROUP_NAME=dg.NAME и DUPRODUCT.NAME=Dg.PROUP_DUCT. .PRODUCT_TYPE=d.PRODUCT_TYPE и s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE и s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT и dg.PRODUCT_GROUP_IS_TMPL=0 и ((sd.M_PG_DEPENDENCY_CHILD_ID е нула, а dd.M_PG_DEPENDENCY_CHILD_ID е нула, DUGROUPGCT е (DUGROUPG1 е нула) или PRO dg1 каде што sd.M_PG_DEPENDENCY_PARENT_ID = sg1.PRODUCT_GROUP_ID и dd .M_PG_DEPENDENCY_PARENT_ID = dg1.PRODUCT_GROUP_ID и sg1.PRODUCT_GROUP_PERSPEC=dg1.PRODUCT_GROUP_PERSPEC и sg1.PRODUCT_GROUP_NAME=dg1.PRODUCT_NAME и)GROUP))

Така, ова е случај кога оптимизаторот се откажа. И за секоја линија беше извршена тешка егзистенција, што ја уби базата на податоци.

Изберете d.PRODUCT_ID од PRODUCT s придружете се на PRODUCT d на s.PRODUCT_TYPE=d.PRODUCT_TYPE и s.PRODUCT_NAME=d.PRODUCT_NAME и s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE и s.PRODUCT_IS_SECURE и s.PRODUCT_IS_SECURE и s.PRODUCT_IS_SECURE и s.PRODUCT_IS_SECURE sg на s.PRODUCT_GROUP_ID= sg.PRODUCT_GROUP_ID придружете се на PRODUCT_GROUP dg на d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID и sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME и sg.PRODUCT_GROUP_GROUPPGROUPG лево од dg.PRODUCT_GROUP_GROUP_ID=dg.PRODUCT_GROUP_ID. PEND ENCY sd на sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID лево приклучи се M_PG_DEPENDENCY dd на dg. PRODUCT_GROUP_ID = дд.M_PG_DEPENDENCY_CHILD_ID лево придружете се на PRODUCT_GROUP sgp на sgp.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_PARENT_ID лево придружете се PRODUCT_GROUP dgpGPRODUCT_dgp. Y_PARENT_ID и sgp.PRODUC T_GROUP_NAME = dgp.PRODUCT_GROUP_NAME и isnull(sgp.PRODUCT_GROUP_IS_TMPL, 0) = isnull( dgp. PRODUCT_GROUP_IS_TMPL, 0) каде што (sd.M_PG_DEPENDENCY_CHILD_ID е нула, а dd.M_PG_DEPENDENCY_CHILD_ID е нула) или (sgp.PRODUCT_GROUP_NAME не е нула, а dgp_DUCT не е GROUP).

По овие трансформации, перформансите на приказот се зголемија експоненцијално со бројот на пронајдени производи. Поточно, времето за пребарување остана практично независно од бројот на натпревари и секогаш беше многу мало. Како што треба да биде.

Ова е јасен пример за тоа како довербата во MSSQL оптимизатор може да игра сурова шега. Не му верувајте, немојте да бидете мрзливи, придружете се рачно, размислувајте секој пат што е подобро во дадена ситуација - постои, во или придружете се.

SQL јазичниот предикат EXISTS врши логичка задача. ВО SQL прашањаовој прирок се користи во изразите на формата

ПОСТОИ (ИЗБЕРИ * ОД ТАБЕЛА_NAME ...).

Овој израз се враќа точно кога барањето ќе најде еден или повеќе редови што одговараат на состојбата и враќа неточно кога не се пронајдени редови.

За НЕ ПОСТОИ, обратно е. Изразување

НЕ ПОСТОИ (ИЗБЕРЕТЕ * ОД ТАБЕЛА_NAME ...)

враќа точно кога не се најдени редови во барањето и неточно кога ќе се најде барем еден ред.

Наједноставните прашања со предикатот SQL ПОСТОИ

Во примерите работиме со базата на податоци на библиотеката и нејзините табели „Книга во употреба“ (BOOKINUSE) и „Корисник“ (КОРИСНИК). Засега ни треба само табелата „Книга во употреба“ (BOOKINUSE).

Автор	Наслов	Pubyear	Ин_бр	ID_Корисник
Толстој	Војна и мир	2005	28	65
Чехов	Градината со вишни	2000	17	31
Чехов	Избрани приказни	2011	19	120
Чехов	Градината со вишни	1991	5	65
Илф и Петров	Дванаесетте столици	1985	3	31
Мајаковски	Песни	1983	2	120
Пашканат	Доктор Живаго	2006	69	120
Толстој	недела	2006	77	47
Толстој	Ана Каренина	1989	7	205
Пушкин	Ќерката на капетанот	2004	25	47
Гогољ	Игра	2007	81	47
Чехов	Избрани приказни	1987	4	205
Пашканат	Омилени	2000	137	18

Пример 1.Определете ги личните карти на корисниците на кои им биле дадени книгите на Толстој и на кои им биле дадени и книгите на Чехов. Надворешното барање избира податоци за корисниците на кои им биле дадени книгите на Толстој, а предикатот ПОСТОИ одредува дополнителен услов што се проверува во внатрешното барање - корисници на кои им биле дадени книгите на Чехов. Дополнителен услов во интерното барање е корисничките ИД од надворешните и внатрешните барања да се совпаѓаат: User_ID=tols_user.user_id. Барањето ќе биде како што следува:

Ова барање ќе го врати следниов резултат:

Разлики помеѓу EXISTS и IN предикати

На прв поглед на прашањата со предикатот ПОСТОИ, може да добиете впечаток дека е идентичен прирок ИН. Ова е погрешно. Иако се многу слични. Предикатот IN бара вредности од опсегот наведен во неговиот аргумент, и ако има такви вредности, тогаш се избираат сите редови што одговараат на овој опсег. Резултатот од предикатот EXISTS е одговорот „да“ или „не“ на прашањето дали воопшто има вредности што одговараат на оние наведени во аргументот. Дополнително, на предикатот IN му претходи името на колоната со која се бараат редови што одговараат на вредностите во опсегот. Ајде да погледнеме пример кој ја покажува разликата помеѓу прирокот EXISTS и прирокот IN, а проблемот е решен со употреба на прирокот IN.

Пример 4.Определете ги личните карти на корисници на кои им се издадени книги од автори чии книги се издадени на корисникот со ID 31. Барањето ќе биде како што следува:

ID_Корисник

120

65

205

Внатрешно барање (по IN) избира автори: Чехов; Илф и Петров. Надворешното барање ги избира сите корисници на кои им се издадени книги од овие автори. Гледаме дека, за разлика од прирокот EXISTS, на предикатот IN му претходи името на колоната, во случајов - Автор.

Прашања со EXISTS предикат и дополнителни услови

Ако, покрај предикатот EXISTS во барањето, примените барем еден дополнителен услов, на пример, наведен со користење агрегатните функции, тогаш таквите прашања можат да послужат за едноставна анализа на податоци. Да го покажеме ова со следниот пример.

Пример 5.Определете ги личните карти на корисниците на кои им е издадена најмалку една книга од Пастернак, а на кои им се издадени повеќе од 2 книги. Го пишуваме следното барање, во кое првиот услов е одреден со предикатот EXISTS со вгнездено барање, а вториот услов со операторот HAVING мора секогаш да доаѓа по вгнезденото барање:

Резултат од барањето:

ID_Корисник

120

Како што може да се види од табелата BOOKINUSE, книгата на Пастернак исто така му била издадена на корисникот со ID 18, но му била дадена само една книга и не е вклучена во примерокот. Ако повторно ја примените функцијата COUNT на слично барање, но овој пат за да ги броите избраните редови (вежбајте го ова сами), можете да добиете информации за тоа колку корисници кои ги читаат книгите на Пастернак читаат и книги од други автори. Ова е веќе од областа на анализа на податоци.

Прашања со EXISTS прирок на две табели

Пребарувањата со предикатот EXISTS можат да преземат податоци од повеќе од една табела. Многу проблеми може да се решат со истиот резултат користејќи JOIN оператор, но во некои случаи користењето EXISTS ви овозможува да креирате помалку незгодно барање. Пожелно е да се користи EXISTS во случаи кога добиената табела ќе содржи колони од само една табела.

Во следниот пример, од истата база на податоци, покрај табелата BOOKINUSE, ќе ви треба и табела КОРИСНИК.

Резултатот од барањето ќе биде следнава табела:

Автор

Чехов

Мајаковски

Пашканат

Како и со користење на операторот JOIN, во случаи кога има повеќе од една табела, треба да користите псевдоними на табелите за да проверите дали вредностите на копчињата што ги поврзуваат табелите се совпаѓаат. Во нашиот пример, псевдонимите на табелата се bk и us, а клучот што ги поврзува табелите е User_ID.

ПОСТОИ прирок во спојувања на повеќе од две табели

Сега ќе видиме подетално зошто е подобро да се користи EXISTS во случаи кога добиената табела ќе содржи колони од само една табела.

Работиме со базата на податоци „Недвижен имот“. Табелата за зделки содржи податоци за зделките. За нашите задачи, колоната Тип со податоци за видот на трансакцијата - продажба или закуп - ќе биде важна во оваа табела. Табелата Object содржи податоци за објекти. Во оваа табела, ќе ни требаат вредностите на колоните Соби (број на соби) и LogBalc, кои содржат податоци за присуство на чардак или балкон во Булова форма: 1 (да) или 0 (не). Табелите Клиент, Управник и Сопственик содржат податоци за клиенти, менаџери на компании и сопственици на имот, соодветно. Во овие табели, FName и LName се име и презиме, соодветно.

Пример 7.Идентификувајте клиенти кои купиле или изнајмиле имот што немаат чардак или балкон. Го пишуваме следното барање, во кое предикатот EXISTS одредува пристап до резултатот од спојување на две табели:

Бидејќи колоните се избираат од табелата Клиент со помош на операторот со ѕвездичка, ќе се прикажат сите колони од оваа табела, кои ќе имаат онолку редови колку што има клиенти кои одговараат на условот наведен со предикатот EXISTS. Не треба да даваме никакви колони од табелите до чие спојување пристапува подпрашањето. Затоа, за да се заштеди време на машината, се зема само една колона. За да го направите ова, единица се пишува по зборот SELECT. Истата техника се користи во прашањата во следните примери.

Сами напишете SQL барање со предикатот EXISTS, а потоа погледнете го решението

Продолжуваме да пишуваме SQL барања заедно со предикатот EXISTS

Пример 9.Определете ги сопствениците на предметите што биле дадени под закуп. Го пишуваме следното барање, во кое предикатот EXISTS исто така одредува пристап до резултатот од спојување на две табели:

Како и во претходниот пример, ќе се вратат сите полиња од табелата до кои пристапува надворешното барање.

Пример 10.Определете го бројот на сопственици чии имоти ги управувал менаџерот Савељев. Ние пишуваме барање во кое надворешното барање пристапува до спојување на три табели, а предикатот EXISTS одредува пристап до само една табела:

Сите барања се проверуваат со постоечка база на податоци. Успешна употреба!

Релациони бази на податоци и SQL јазик

Државната академија за економија и менаџмент во Новосибирск

ЛАБОРАТОРИСКИ ПРАКТИКУМ ЗА ДИСЦИПЛИНА

"БАЗА НА ПОДАТОЦИ"

Лабораториска работа бр.7

„Јазик на базите SQL податоци: команди за манипулација со податоци»

НОВОСИБИРСК 2000 г

SQL е кратенка за Структурен јазик за пребарување. Од името на јазикот е јасно дека неговата главна цел е да генерира прашања за да се добијат информации од базата на податоци. Наредбите за преземање податоци ја формираат основата на јазикот за манипулација со податоци DML - составен дел на јазикот SQL. Сепак, DML се состои од повеќе од само команди за преземање податоци од база на податоци. Постојат и команди за модификација на податоци, управување со податоци и други.

Лабораториската работа ги испитува основните алатки на јазикот DML. Во тек лабораториска работаќе се држиме до стандардот SQL2.

Поради фактот што SQL е голем јазик, ќе ги разгледаме само основните команди. Различни специфични SQL алатки се опфатени во следните лаборатории.

За извршување на лабораториска работа, потребно е познавање на основите на моделот на релациони податоци, основите на релациска алгебра и релациона пресметка и принципите на работа со MS SQL Server DBMS.

Како резултат на завршувањето на лабораториската работа, ќе ги совладате методите за манипулирање со податоци користејќи команди на јазикот SQL, разгледајте го дијалектот на јазикот имплементиран во MS SQL Server DBMS.

ВОВЕД

SQL содржи широк опсег на можности за манипулација со податоци, како за креирање прашања, така и за ажурирање на базата на податоци. Овие способности се потпираат само на логичката структура на базата на податоци, а не на нејзината физичка структура, која е во согласност со барањата на релацискиот модел.

Оригиналната структура на SQL синтаксата беше (или барем изгледаше дека е) заснована на релациската пресметка на Код. Единствената поддржана операција во релационата алгебра беше унија.

Покрај синтаксата слична на релациона пресметка развиена во претходниот стандард, SQL2 директно ги имплементира операциите унија, пресек, разлика и приклучување. Операциите за избор, проект и производ беа (и продолжуваат да се) поддржани речиси директно, додека операциите за поделба и доделување се поддржани во понезгодна форма.

Прво ќе го опишеме јазикот на барањето SQL, а потоа неговите операции за внесување и модификација на податоци. Операциите за модификација на податоците ќе бидат опишани последно, бидејќи нивната структура до одреден степен се потпира на структурата на јазикот за пребарување.

Едноставни прашања

За нас едноставно барањеќе има барање кое пристапува само до една табела во базата на податоци. Едноставните прашања ќе ни помогнат да ја илустрираме основната структура на SQL.

Едноставно барање.Прашање што пристапува само до една табела со база на податоци.

Барање:Кој работи како гипсар?

WHERE SKILL_TYPE = „Гипсар“

Резултат:

Г.Риковер

Ова барање ги илустрира трите најчести фрази SQL: SELECT, FROM и WHERE. Иако во нашиот пример ги поставивме на различни линии, сите тие можат да се појават на иста линија. Тие, исто така, можат да бидат поинаку вовлечени, а зборовите во фразите може да се одделат со произволен број празни места. Ајде да ги погледнеме карактеристиките на секоја фраза.

Изберете. Клаузулата SELECT ги наведува колоните што треба да се појават во добиената табела. Ова се секогаш колони од некоја релациона табела. Во нашиот пример, добиената табела се состои од една колона (ИМЕ), но генерално може да содржи неколку колони; може да содржи и пресметани вредности или константи. Ќе дадеме примери за секоја од овие опции. Ако добиената табела мора да содржи повеќе од една колона, тогаш сите потребни колони се наведени после SELECT командиразделени со запирки. На пример, фразата SELECT WORKER_ID, NAME ќе резултира со табела која се состои од колоните WORKER_ID и NAME.

SELECT клаузула.Ги одредува колоните од добиената табела.

Од. Клаузулата FROM одредува една или повеќе табели до кои пристапува барањето. Сите колони наведени во клаузулите SELECT и WHERE мора да постојат во една од табелите наведени во командата FROM. Во SQL2, овие табели може директно да се дефинираат во шемата како базни табели или прикази на податоци, или тие самите можат да бидат неименувани табели кои произлегуваат од SQL-пребарувањата. Во вториот случај, барањето е експлицитно дадено во командата FROM.

Фразата ОД.Ги одредува постоечките табели до кои се пристапува со барањето.

Каде. Клаузулата WHERE содржи услов. врз основа на кои се избираат редовите од табелата(ите). Во нашиот пример, условот е колоната SKILL_TYPE да ја содржи константата „Plasterer“ затворена во апострофи, како што секогаш се прави со текстуалните константи во SQL. Клаузулата WHERE е најнестабилната команда SQL; може да содржи многу различни услови. Голем дел од нашата дискусија ќе биде посветен на илустрација на различните конструкции дозволени во командата WHERE.

КАДЕ клаузула.Го одредува условот врз основа на кои редови се избираат од наведените табели.

Горенаведеното SQL барање системот го обработува по следниот редослед: FROM, WHERE, SELECT. Односно, редовите на табелата наведени во командата FROM се ставаат во работната област за обработка. Клаузулата WHERE потоа се применува на секој ред во низа. Сите редови кои не го задоволуваат условот WHERE се исклучени од разгледување. Потоа оние редови кои го задоволуваат условот WHERE се обработуваат со изјавата SELECT. Во нашиот пример, NAME е избрано од секој таков ред и сите избрани вредности се излезни како резултати од барањето.

Барање:Обезбедете ги сите информации за деловни згради.

WHERE TYPE = „Канцеларија“

Резултат:

BLDG IDADDRESSTYPEQLTY LEVELSTATUS

312 Elm St., 123 Office 2 2

210 Березоваја ул. 1011 Канцеларија Z 1

ул. Осиноваја 111. 1213 Канцеларија 4 1

Ѕвездичка (*) во командата SELECT значи „цел ред“. Ова е пригоден стенографија што често ќе ја користиме.

Барање:Колкава е неделната плата за секој електричар?

ИЗБЕРЕТЕ ИМЕ, „Неделна плата = ", 40 * HRLY_RATE

WHERE SKILL_TYPE = „Електричар“

Резултат:

M. Faraday Неделна плата = 500,00

H.Columbus Неделна плата = 620,00

Ова барање ја илустрира употребата на двете карактерни константи (во нашиот пример „Неделна плата =“) и пресметките во командата SELECT. Во изјавата SELECT, можете да вршите пресметки кои користат нумерички колони и нумерички константи, како и стандардни аритметички оператори ( +, -, *, /), групирани по потреба со помош на загради. Вклучивме и нов ORDER команда BY, што го подредува резултатот од барањето во растечки алфанумерички редослед по наведената колона. Ако сакате да ги подредите резултатите по опаѓачки редослед, треба да додадете DESC на командата. Клаузулата ORDER BY може да ги подреди резултатите по повеќе колони, некои во растечки редослед, а други во опаѓачки редослед. Прво е наведена колоната за примарен клуч на сортот.

Константа на карактерот.Константа која се состои од букви, бројки и „специјални“ знаци.

Барање:Кој има часовна стапка од 10 до 12 долари?

КАДЕ HRLY_RATE > = 10 И HRLY_RATE< - 12

Резултат:

ИД на РАБОТНИКИМЕ HRLY_RATE SKILL_TYPE SUPV_ID

Ова барање илустрира некои од дополнителните карактеристики на исказот WHERE: споредбени оператори и Буловиот И оператор. Шест споредбени оператори (=,<>(не е еднакво),<, >, <=, >=). Буловите оператори И, ИЛИ и НЕ може да се користат за создавање сложени услови или за негирање на услов. Заградите може да се користат за групирање услови, како што е вообичаено во програмските јазици.

Споредбени оператори =,<>, <, >, <=, >=.

Булова операцииИ (И), ИЛИ (ИЛИ) и НЕ (ТОЈ) .

Може да го користите и операторот BETWEEN (помеѓу) за да го формулирате ова барање:

КАДЕ HRLY_RATE ПОМЕЃУ 10 И 12

BETWEEN може да се користи за споредување количество со две други величини, од кои првата е помала од втората, ако споредената количина може да биде еднаква на секоја од овие количини или која било вредност помеѓу.

Барање: Наведете гипсери, кровџии и електричари.

WHERE SKILL_TYPE IN („Гипсар“, „Кровер“, „Електричар“)

Резултат:

ИМЕ на WORKER_ID HRLY_RATE SKILL_TYPE SUPV_ID

1412 К.Немо 13,75 Гипс 1520 г

2920 R. Garrett 10.00 Roofer 2920

1520 Г. Риковер 11,75 Гипс 1520 г

Ова барање ја објаснува употребата на споредбениот оператор IN (B). Условот WHERE се смета за вистинит ако типот на специјалитет на редот се наоѓа во комплетот наведен во загради, односно ако специјалитетот е малтер, покривар или електричар. Ќе го видиме операторот IN повторно во подпрашања.

Да претпоставиме дека не можеме точно да се сетиме на правописот на нашата специјалност: „електричар“ или „инженер по електроника“ или нешто друго. Знаците за џвакање, кои ги заменуваат низите на недефинирани знаци, го олеснуваат пронаоѓањето на неточни правописи во барањето.

Симболи на шаблони.Карактери кои ги заменуваат низите на недефинирани знаци.

Барање:Наведете ги вработените чија специјалност започнува со „Елек“.

WHERE SKILL_TYPE LIKE („Избори%“)

Резултат:

ИМЕ НА РАБОТНИЧКИ ИД HRLY_RATE SKILL_TYPE SUPV_ID

1235 М. Фарадеј 12.50 Електричар 1311 г

1311 H. Columbus 15.50 Electric 1311

SQL има два знаци: % (процент) и _ (подвлекување). Подвлекувањето заменува точно еден недефиниран знак. Процентот заменува произволен број на знаци, почнувајќи од нула. Кога се користат знаци со џокер, потребен е оператор LIKE за споредба на променливите на знаци со константи. Други примери:

ИМЕ КАКО „__Колумбо“

ИМЕТО КАКО „__K%“

Условот во првиот пример е точен ако NAME се состои од два знака проследени со „Колумбо“. Во табелата WORKER, сите имиња започнуваат со прв иницијал и точка. Така, користејќи ја оваа состојба ние. Да ги најдеме сите вработени со презиме „Колумбо“. Состојбата на вториот пример ни овозможува да ги најдеме сите вработени чии презимиња започнуваат со буквата „К“.

Барање:Најдете ги сите работни места што започнуваат во следните две недели.

КАДЕ START_DATE ПОМЕЃУ CURRENT_DATE И

Резултат:(Да се ​​претпостави дека тековниот датум е ТЕКОВЕН ДАТУМ = 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

Ова барање ја илустрира употребата на операторот BETWEEN со вредности на датум и интервал. CURRENT_DATE е функција која секогаш го враќа денешниот датум. Изразување

CURRENT_DATE + INTERVAL „14“ ДЕН

додава период од две недели на тековниот датум. Така, ASSIGNMENT се избира (под претпоставка дека денес е 10/10) ако вредноста на нејзината колона START_DATE е помеѓу 10/10 и 10/24. Од ова можеме да видиме дека можеме да додадеме интервални вредности на полињата за датум. Покрај тоа, можеме да ги помножиме вредностите на интервалите со цели броеви. На пример, да претпоставиме дека сакаме да дознаеме кој број ќе биде за одреден број недели (означен со променливата NUM_WEEKS). Можеме да го направиме вака:

CURRENT_DATE + INTERVAL „7“ ДЕН * NUM_WEEKS

2. Барања со повеќе табели

Способноста да се поврзат податочните елементи преку границите на една табела е важна за секој јазик на базата на податоци. Во релациска алгебра, оваа функција се изведува со операцијата спојување. Иако голем дел од SQL се заснова директно на релациона пресметка, SQL поврзува податоци од различни табели на сличен начин со операцијата за спојување на релациска алгебра. Сега ќе покажеме како се прави ова. Размислете за барањето:

Барање:

Податоците потребни за одговорот се во две табели: РАБОТНИК и ЗАДАЧА. Решението SQL бара да се наведат двете табели во командата FROM и да се наведе посебен тип на клаузула WHERE:

ИЗБЕРЕТЕ ВЕШТИНА_ТИП

ОД РАБОТНИК, ЗАДАЧА

WHERE WORKER.WORKER_ID = ASIGNMENT.WORKER_ID

И BLDG_ID = 435

Што се случува овде? Мора да разгледаме две фази во тоа како системот го обработува ова барање.

1. Како и обично, прво се обработува клаузулата FROM. Меѓутоа, во овој случај, бидејќи командата одредува две табели, системот создава Декартов производ од редовите на овие табели. Ова значи дека е создадена една голема табела (логично) која се состои од колони од двете табели, при што секој ред од една табела е спарен со секој ред од другата табела. Во нашиот пример, бидејќи табелата РАБОТНИК има пет колони, а табелата ЗАДАВАЊЕ има четири колони, Декартовиот производ произведен со командата FROM ќе има девет колони. Вкупниот број на редови на декартовскиот производ е еднаков на m * n, каде што m е бројот на редови од табелата РАБОТНИК; а n е бројот на редови во табелата ЗАДАВАЊЕ. Бидејќи табелата РАБОТНИК има 7 редови, а табелата ЗАДАВАЊА има 19 редови, декартовскиот производ ќе содржи 7x19 или 133 редови. Ако командата FROM наведува повеќе од две табели, се креира Декартов производ од сите табели наведени во командата.

Декартов производ. Резултатот од спојувањето на секој ред од една табела со секојред од друга табела.

2. По креирањето на џиновската релациона табела, системот ја користи командата WHERE како и претходно. Секој ред од табелата создаден со командата FROM. се проверува дали е задоволен условот WHERE. Редовите кои не го задоволуваат условот се исклучени од разгледување. Клаузулата SELECT потоа се применува на преостанатите редови.

Клаузулата WHERE во нашето барање содржи два услови:

1. РАБОТНИК. WORKER_ID = ASIGNMENT.WORKER_ID

2. BLDG_ID = 435

Првиот од овие услови е условот за спојување. Имајте на ум дека бидејќи и табелите WORKER и ASSIGNMENT содржат колона со име WORKER_ID, нивниот декартов производ ќе содржи две колони со тоа име. За да ги разликуваме, на името на колоната му претходиме името на изворната табела, одделено со точка.

Првиот услов значи дека во кој било избран ред, вредноста на колоната WORKER_ID од табелата WORKER мора да одговара на вредноста на колоната WORKER_ID од табелата ASSIGNMENT. Во реалноста, ние спојуваме две табели по WORKER_ID. Сите редови во кои вредностите на овие две колони не се еднакви се исклучени од табелата со производи. Точно истото се случува и при извршување на операцијата природно спојување на релациска алгебра. (Сепак, сè уште има одредена разлика од природното спојување: SQL автоматски не ја отстранува дополнителната колона WORKER_ID). Целосното спојување на овие две табели со дополнителниот услов BLDG_ID = 435 е прикажано на сл. 1. Користењето на командата SELECT на крајот ќе го даде следниот резултат на барањето:

ТИП ВЕШТИНА

Гипсар

Покривник

Електричар

Ориз. 1. Спојување на табелите РАБОТНИК и ЗАДАЧИ

Сега ќе покажеме како да се приклучи табела за себе во SQL.

Барање:Наведете ги вработените, наведувајќи ги имињата на нивните менаџери.

ИЗБЕРЕТЕ A.WORKER_NAME, B.WORKER_NAME

ОД РАБОТНИК А, РАБОТНИК Б

КАДЕ B.WORKER_ID = A.SUPV_ID

Клаузулата FROM во овој пример создава две „копии“ од табелата WORKER, давајќи им ги псевдонимите A и B. Алијасот е алтернативно име дадено на табелата. Потоа, копиите A и B од табелата WORKER се спојуваат со командата WHERE врз основа на условот за еднаквост на WORKER_ID во B и SUPV_ID во A. Така, секој ред од A е споен со редот B, кој содржи информации за менаџерот на редот А (сл. 2).

Ориз. 2. Спојување на две копии од табелата РАБОТНИК

Со избирање на две имиња на вработени од секоја линија, ја добиваме потребната листа:

A.NAMEB.NAME

М. Фарадеј Х. Колумбо

К.Немо Г.Риковер Р.Гарет Р.Гарет

Мејсон П. Мејсон Г. Риковер Г. Риковер Х. Колумбус Х.

Прекар.Алтернативно име дадено на табелата.

A.WORKER_NAME го претставува работникот, а B.WORKER_NAME го претставува менаџерот. Имајте предвид дека некои работници се нивни менаџери, што произлегува од еднаквоста WORKER_ID - SUPV_ID во нивните линии.

Во SQL, можете да поврзете повеќе од две табели истовремено:

Барање

ИЗБЕРЕТЕ WORKER_NAME

ОД РАБОТНИК, ЗАДАЧА, ЗГРАДА

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID И ASIGNMENT.BLDG_ID = BUILDING.BLDG_ID И

TYPE = „Канцеларија“

Резултат:

М. Фарадеј

Г.Риковер

J. Barrister

Забележете дека ако името на колоната (на пример, WORKER_ID или BLDG_ID) се појавува во повеќе од една табела, тогаш за да се избегне двосмисленост, мора да го поставиме името на колоната со името на оригиналната табела. Но, ако името на колоната се појавува само во една табела, како TYPE во нашиот пример, тогаш нема двосмисленост, така што името на табелата не треба да се одредува.

Наредбите SQL во ова барање создаваат една табела од три табели со релациона база на податоци. Првите две табели се споени со WORKER_ID, по што третата табела се спојува со BLDG_ID кон добиената табела. Состојба

TYPE = „Канцеларија“

Клаузулата WHERE предизвикува да се исклучат сите редови освен оние за деловни згради. Ова ги исполнува барањата на барањето.

3. Подпрашања

Подпрашање.Барање во барање

Подпрашање може да се стави во клаузулата WHERE на барањето, со што се прошируваат можностите на клаузулата WHERE. Ајде да погледнеме на пример.

Барање:Кои се специјалностите на работниците распоредени во зградата 435?

ИЗБЕРЕТЕ SKTLL_TYPE

ОД РАБОТНИК КАДЕ ВЛЕГУВА WORKER_ID

(ИЗБЕРЕТЕ WORKER_ID

КАДЕ BLDG_ID = 435)

Подпрашање во овој пример

(ИЗБЕРЕТЕ WORKER_ID

КАДЕ BLDG_ID = 435)

Барањето што содржи подпрашање се нарекува надворешно барањеили главното барање. Подпрашањето резултира со создавање на следниов сет на лични карти на вработени:

ИД на РАБОТНИК

Надворешно барање.Главното барање, кое ги содржи сите подпрашања.

Овој сет на ИД потоа го зазема местото на подпрашање во надворешното барање. Од оваа точка натаму, надворешното барање се извршува со користење на множеството создадено од подпрашањето. Надворешното барање го обработува секој ред од табелата WORKER според клаузулата WHERE. Ако WORKER_ID на редот лежи во множеството (IN) создадено од подпрашањето, тогаш се избира SKILL_TYPE на редот и се прикажува во добиената табела:

ТИП ВЕШТИНА

Гипсар

Покривник

Електричар

Многу е важно клаузулата SELECT на подпрашањето да го содржи WORKER_ID и само WORKER_ID. Во спротивно, клаузулата WHERE на надворешното барање, што значи дека WORKER_ID е во множеството идентификатори на работници, нема да има значење.

Забележете дека подпрашањето може логично да се изврши пред барем еден ред да биде разгледан од главното барање. Во извесна смисла, подпрашањето е независно од главното барање. Може да се изврши како целосно барање. Велиме дека таквото подпрашање не е во корелација со главното барање. Како што ќе видиме наскоро, подпрашањата можат да бидат во корелација.

Неповрзано подпрашање.Подпрашање чија вредност е независна од кое било надворешно барање.

Еве пример за подпрашање во подпрашање.

Барање: Наведете ги вработените распоредени во деловните згради.

Повторно го гледаме барањето со кое ја испитавме врската.

ИЗБЕРЕТЕ WORKER_MAME

КАДЕ ВЛЕГУВА WORKER_ID

(ИЗБЕРЕТЕ WORKER_ID

КАДЕ ВЛЕЗ BLDG_ID

WHERE TYPE = „Канцеларија“))

Резултат:

М. Фарадеј

Г.Риковер

J. Barrister

Имајте предвид дека не треба никаде да ги префиксираме имињата на колоните со имињата на табелите, бидејќи секое подпрашање обработува една и само една табела, така што не може да се појават нејаснотии.

Извршувањето на барањето се случува по редослед внатре-надвор. Односно, најинтерното барање (или „најдолното“) се извршува прво, потоа се извршува подпрашањето што го содржи, а потоа надворешното барање.

Корелирани подпрашања. Сите подпрашања дискутирани погоре беа независни од главните прашања во кои беа користени. Под независни, мислиме дека подпрашањата можат сами да се извршат како целосни барања. Сега продолжуваме да разгледуваме класа на подпрашања чии резултати од извршување може да зависат од редот што го разгледува главното барање. Таквите подпрашања се нарекуваат корелирани подпрашања.

Корелирано подпрашање. Подпрашање чиј резултат зависи од редот што го разгледува главното барање.

Барање:Наведете ги вработените чии саатни се повисоки од оние на нивните менаџери.

ИЗБЕРЕТЕ WORKER_NAME

WHERE A.HRLY_RATE >

(ИЗБЕРЕТЕ Б.HRLY_RATE

КАДЕ B.WORKER_ID = A.SUPV_ID)

Резултат:

Логичките чекори за извршување на ова барање се:

1. Системот создава две копии од табелата WORKER: копирајте A и копирајте B. Според начинот на кој ги дефиниравме, A се однесува на вработениот, B се однесува на менаџерот.

2. Системот потоа го разгледува секој ред A. Даден ред се избира ако го задоволува условот WHERE. Овој услов значи дека ќе се избере ред ако неговата вредност HRLY_RATE е поголема од HRLY_RATE генерирана од подпрашањето.

3. Подпрашањето ја избира вредноста HRLY_RATE од редот Б, чиј WORKER_ID е еднаков на SUPV_ID од редот А, во овој моментсметано со главното барање. Ова е HRLY_RATE на менаџерот.

Имајте предвид дека бидејќи A.HRLY_RATE може да се спореди само со една вредност, подпрашањето мора да врати само една вредност. Оваа вредност се менува во зависност од тоа кој ред А се разгледува. Така, подпрашањето е во корелација со главното барање. Ќе видиме повеќе примери на поврзани подпрашања подоцна кога ќе ги проучуваме вградените функции.

EXISTS и НЕ ПОСТОИ оператори

Да претпоставиме дека сакаме да идентификуваме работници кои не се назначени да работат во одредена зграда. На површина, се чини дека таквото барање може лесно да се задоволи со едноставно негирање на афирмативната верзија на барањето. Да претпоставиме, на пример, дека сме заинтересирани за зграда со BLDG_ID 435. Размислете за барањето:

ИЗБЕРЕТЕ WORKER_ID

КАДЕ BLDG_ID НЕ 435

За жал, ова е неточна формулација на решението. Барањето едноставно ќе ни ги даде легитимациите на работниците кои работат во други згради. Очигледно, некои од нив може да се доделат и на зградата 435.

Правилно формулирано решение го користи операторот НЕ ПОСТОИ:

ИЗБЕРЕТЕ WORKER_ID

КАДЕ НЕ ПОСТОИ

WHERE ASIGNMENT.WORKER_ID = WORKER.WORKER_ID И

Резултат:

WORKER_ID

Операторите EXISTS и NOT EXISTS се секогаш поставени пред подпрашањето. EXISTS се оценува како точно ако множеството генерирано од подпрашањето не е празно. Ако множеството генерирано од подпрашањето е празно, тогаш EXISTS ја зема вредноста „false“. Операторот НЕ ПОСТОИ, се разбира, работи токму спротивното. Вистина е ако резултатот од подпрашањето е празен, а неточно во спротивно.

ПОСТОИ оператор. Се враќа точно ако комплетот резултати не е празен.

НЕ ПОСТОИ оператор. Се враќа точно ако комплетот резултати е празен.

Во овој пример го користевме операторот НЕ ПОСТОИ. Подпрашањето ги избира сите редови од табелата ASSIGNMENT во кои WORKER_ID ја има истата вредност како и редот земен од главното барање, а BLDG_ID е еднаков на 435. Ако ова множество е празно, тогаш работниот ред што се разгледува со главното барање е избрано, бидејќи тоа значи дека Овој вработен не работи во зградата 435.

Во решението што го дадовме, користевме поврзано подпрашање. Ако го користиме операторот IN наместо NOT EXISTS, можеме да поминеме со неповрзано подпрашање:

ИЗБЕРЕТЕ WORKER_ID

КАДЕ НЕ ВЛЕЗЕ РАБОТНИК_ID

(ИЗБЕРЕТЕ WORKER_ID

КАДЕ BLDG_ID = 435)

Ова решение е поедноставно од решението со операторот НЕ ПОСТОИ. Се поставува природно прашање: зошто ни треба ПОСТОИ, а воопшто НЕ ПОСТОИ? Одговорот е дека НЕ ​​ПОСТОИ е единствениот начин да се решат прашањата што го содржат зборот „секое“ во условот. Ваквите прашања се решаваат во релациска алгебра со помош на операцијата за делење, а во релациона пресметка со помош на универзалниот квантификатор. Еве пример за барање со зборот „секој“ во неговата состојба:

Барање:Наведете ги вработените доделени во секоја зграда.

Ова прашање може да се имплементира во SQL користејќи двојни негации. Ќе го преформулираме барањето за да вклучи двоен негативен:

Барање:Наведете ги таквите вработени за кои Неима зграда на која не се доделени.

Ја истакнавме двојната негатива. Јасно е дека ова барање е логично еквивалентно на претходното.

Сега сакаме да го формулираме решението во SQL. За конечното решение полесно да се разбере, прво даваме решение за прелиминарен проблем: проблемот на идентификување на сите згради за кои хипотетички работник, „1234“ Неназначен.

(I) ИЗБЕРЕТЕ BLDG_ID

КАДЕ НЕ ПОСТОИ

ЗАДАВАЊЕ. ИД на РАБОТНИК = 1234)

Го означивме ова барање (I) затоа што ќе се осврнеме на него подоцна. Ако не постои зграда што го задоволува ова барање, тогаш работникот 1234 е доделен на секоја зграда и затоа ги задоволува условите од првобитното барање. За да добиеме решение за оригиналното барање, мораме да го генерализираме барањето (I) од специфичен работник 1234 на променливата WORKER_ID и да го претвориме ова изменето барање во подпрашање на поголемото барање. Еве го решението:

(II) ИЗБЕРЕТЕ WORKER_ID

КАДЕ НЕ ПОСТОИ

КАДЕ НЕ ПОСТОИ

WHERE ASIGNMENT.BLDG_ID = BUILDING.BLDG_ID И

ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID)

Резултат:

ИД на РАБОТНИК

Забележете дека подпрашањето што започнува на четвртата линија на барањето (II) е идентично со барањето (I), со „1234“ заменето со WORKER.WORKER_ID. Прашањето (II) може да се прочита на следниов начин:

Изберете WORKER_ID од WORKER ако нема зграда на која не е доделен WORKER_ID.

Ова се совпаѓа со условите на оригиналното барање.

Гледаме дека операторот НЕ ПОСТОИ може да се користи за формулирање на оние барања за кои е потребна операција за делење во релациска алгебра и универзален квантификатор во релациона пресметка. Од гледна точка на лесно користење, операторот НЕ ПОСТОИ не нуди посебна придобивка, што значи дека SQL-пребарувањата што користат НЕ ПОСТОИ двапати не се полесни за разбирање од решенијата за релациона алгебра со поделба или решенија за релациона пресметка со универзални квантификатори. Ќе бидат потребни повеќе истражувања за да се создадат јазични конструкции кои ќе овозможат ваквите прашања да се решаваат поприродно.

Вградени функции

Да ги разгледаме прашањата од овој тип:

Кои се максималните и минималните саатни стапки? Колкав е просечниот број на денови на работа на вработените во зградата 435? Колку е вкупниот број на денови наменети за малтерисување на зградата 312? Колку различни специјалитети има?

За да се одговори на овие прашања потребни се статистички функции кои гледаат многу редови во табелата и враќаат една вредност. Постојат пет такви функции во SQL, наречени вградени функции или поставени функции. Овие функции се SUM (збир), AVG (просек), COUNT (количина), MAX (максимум) и MIN (минимум).

Вградена функција (наместена функција). Статистичка функција која работи на повеќе редови: SUM (збир), AVG (просек), COUNT (количина), MAX (максимум), MIN (минимум).

Барање:Кои се максималните и минималните саатни стапки?

ИЗБЕРИ МАКС (HRLY_RATE), MIN (HRLY_RATE)

Резултат: 17.40, 8.20

MAX функциии MIN работат на една колона од табелата. Тие ја избираат максималната или минималната вредност, соодветно, од оваа колона. Нашата формулација за барање не содржи клаузула WHERE. За повеќето прашања ова можеби не е случај, како што покажува нашиот следен пример.

Барање:Колкав е просечниот број на денови на работа на вработените во зградата 435?

ИЗБЕРИ AVG (NUM_DAYS)

КАДЕ BLDG_ID =435

Резултат: 12.33

Барање:Колку е вкупниот број на денови наменети за малтерисување на зградата 312?

ИЗБЕРЕТЕ СУМ (NUM_DAYS)

ОД ЗАДАЧА, РАБОТНИК

WHERE WORKER.WORKER_ID = ASIGNMENT.WORKER_ID И

SKILL_TYPE = „Гипсар“ И

Резултат: 27

Решението користи спојување помеѓу табелите ASSIGNMENT и WORKER. Ова е неопходно бидејќи SKILL_TYPE е во табелата WORKER, а BLDG_ID е во табелата ASSIGNMENT.

Барање:Колку различни специјалитети има?

ИЗБЕРЕТЕ БРОЈ (ИЗЛИЧЕН ВИСОК_ТИП)

Резултат: 4

Бидејќи истата специјалност може да се појави во повеќе различни редови, мора да го користите клучниот збор DISTINCT во ова барање за да спречите системот да брои ист тип на специјалност повеќе од еднаш. Операторот DISTINCT може да се користи со која било од вградените функции, иако секако е вишок со функциите MAX и MIN.

РАЗЛИЧНИ. Оператор кој елиминира дупликат линии.

Функциите SUM и AVG треба да се користат само со нумерички колони. Други функции може да се користат и со нумерички и со податоци со знаци. Сите функции освен COUNT може да се користат со пресметани изрази. На пример:

Барање:Која е просечната неделна плата?

ИЗБЕРИ AVG (40 * HRLY_RATE)

Резултат: 509.14

COUNT може да се однесува на цел ред наместо на поединечна колона :

Барање: Колку згради имаат ниво на квалитет 3?

ИЗБЕРЕТЕ COUNT (*)

ОД ГРАДЕЊЕ КАДЕ

Резултат: 3

Како што покажуваат сите овие примери, ако командата SELECT содржи вградена функција, тогаш ништо друго не може да се појави во таа команда SELECT. Единствен исклучок од ова правило е клаузулата GROUP BY, која ќе ја разгледаме сега.

клаузули ГРУПИ ПО и ИМАЊЕ

Во управувањето, често се потребни статистички информации за секоја група во многу групи. На пример, разгледајте го следново барање:

Барање:За секој менаџер, дознајте ја максималната часовна стапка меѓу неговите подредени.

За да се реши овој проблем, мора да ги поделиме работниците во групи според нивните менаџери. Потоа ќе ја одредиме максималната понуда во секоја група. Во SQL ова се прави на следниов начин:

ГРУПАЦИЈА ПО SUPV_ID

Резултат:

SUPV_IDMAX (СТАПКА НА HRLY)

Кога го обработува ова барање, системот прво ги дели редовите од табелата WORKER во групи користејќи го следново правило. Редовите се ставаат во истата група ако и само ако имаат ист SUPV_ID. Потоа се применува клаузулата SELECT на секоја група. Бидејќи има само една вредност SUPV_ID во оваа група, нема SUPV_ID несигурност во групата. За секоја група, клаузулата SELECT го издава SUPV_ID, а исто така ја пресметува и дава вредноста MAX(HRLY_RATE). Резултатот е претставен погоре.

Во командата SELECT со вградени функции, може да се појават само оние колони кои се вклучени во клаузулата GROUP BY. Забележете дека SUPV_ID може да се користи во командата SELECT бидејќи е вклучена во клаузулата GROUP BY.

ГРУПА ПО клаузула. Укажува дека редовите треба да се поделат во групи со заеднички вредности на наведените колони.

Клаузулата GROUP BY ви овозможува да извршите одредени сложени пресметки. На пример, можеби ќе сакаме да го дознаеме просекот на овие максимални понуди. Сепак, пресметувањето со вградените функции е ограничено во смисла дека не дозволува вградените функции да се користат во други вградени функции. Значи израз како

AVG (MAX(HRLY_RATE))

забрането. Имплементацијата на таквото барање ќе се состои од две фази. Прво треба да ги ставиме максималните понуди во нова табела, а во вториот чекор треба да го пресметаме нивниот просек.

Можете да ја користите клаузулата WHERE со командата GROUP BY:

Барање:За секој тип на градба дознајте просечно нивоквалитет меѓу зградите со статус 1.

ИЗБЕРИ ТИП, AVG (QLTY_LEVEL)

КАДЕ СТАТУС = 1

Резултат:

TYPEAVG (QLTY_LEVEL)

Продавница 1

Станбена зграда 3

Клаузулата WHERE се извршува пред исказот GROUP BY. Така, ниту една група не може да содржи ред кој има статус различен од 1. Редовите за статус 1 се групирани по вредност TYPE, а потоа се применува клаузула SELECT за секоја група.

Имајќи фраза. Поставува услови на групи.

Можеме да примениме услови и за групи создадени од клаузулата GROUP BY. Ова е направено со користење на фразата HAVING. Да претпоставиме, на пример, дека решивме да направиме едно од претходните прашања поконкретно:

Барање: За секој менаџер кој има повеќе од еден подреден, дознајте ја максималната стапка на час меѓу неговите подредени.

Можеме да ја одразиме оваа состојба со соодветната команда HAVING:

ИЗБЕРЕТЕ SUPV_ID, MAX (HRLY_RATE)

ОД ГРУПА НА РАБОТНИЦИ ПО SUPV_ID

ИМАТЕ БРОЈ(*) > 1

Резултат:

SUPV_ID MAX (HRLY_RATE)

Разликата помеѓу клаузулите WHERE и HAVING е во тоа што WHERE се однесува на редови, додека HAVING се однесува на групи.

Барањето може да содржи и WHERE и HAVING клаузула. Во овој случај, прво се извршува клаузулата WHERE бидејќи се извршува пред групирањето. На пример, разгледајте ја следната модификација на претходното барање:

Барање: За секој тип на зграда, дознајте го просечното ниво на квалитет меѓу зградите од статус 1. Размислете само за оние типови на згради чие максимално ниво на квалитет не надминува 3.

ИЗБЕРИ ТИП, AVG (QLTY_JLEVEL)

КАДЕ СТАТУС = 1

ИМА МАКС (QLTY_LEVEL)<= 3

Резултат:

TYPE AVG (QLTY_LEVEL)

Продавница 1

Станбена зграда 3

Забележете дека почнувајќи со клаузулата FROM, клаузулите се извршуваат по редослед, а потоа се применува клаузулата SELECT. Така, клаузулата WHERE се применува на табелата BUILDING и сите редови во кои STATUS е различен од 1 се бришат. Останатите редови се групирани по TYPE; сите редови со иста вредност TYPE завршуваат во иста група. Така, се создаваат неколку групи, по една за секоја вредност TYPE. Клаузулата HAVING потоа се применува на секоја група, а оние групи чија вредност на максималното ниво на квалитет надминува 3 се отстранети. Конечно, клаузулата SELECT се применува на останатите групи.

7. Вградени функции и подпрашања

Вградените функции може да се користат само во клаузула SELECT или команда HAVING. Сепак, клаузула SELECT што содржи вградена функција може да биде дел од подпрашање. Ајде да погледнеме пример за такво подпрашање:

Барање:Кои работници имаат повисока плата од просечната саатница?

ИЗБЕРЕТЕ WORKER_NAME

WHERE HRLY_RATE >

(ИЗБЕРЕТЕ AVG (HRLY_RATE)

Резултат:

Х. Колумбо

Забележете дека подпрашањето не е во корелација со главното барање. Подпрашањето враќа точно една вредност - просечната часовна стапка. Главното барање избира работник само ако неговата стапка е поголема од пресметаниот просек.

Корелираните прашања може да користат и вградени функции:

Прашање: Кој вработен има час повисок од просечниот час меѓу подредените на истиот менаџер?

Во овој случај, наместо да пресметуваме една просечна саатница за сите работници, мораме да ја пресметаме просечната стапка за секоја група работници што се пријавуваат кај истиот менаџер. Покрај тоа, нашата пресметка мора да се направи одново за секој работник што се разгледува со главното барање:

ИЗБЕРЕТЕ А. WORKER_NAME

SQL ви овозможува да вгнездувате прашања едни во други. Вообичаено, подпрашањето враќа една вредност, која се проверува за да се види дали предикатот е вистинит.

Видови термини за пребарување:
. Споредба со резултатот од подпрашање (=, >=)
. Проверка дали припаѓа на резултатите од подпрашање (IN)
. Проверка на постоење (ПОСТОИ)
. Повеќекратна (квантитативна) споредба (БИЛО, СИТЕ)

Забелешки за вгнездените прашања:
. Подпрашањето мора да избере само една колона (освен за подпрашање со предикат EXISTS), а неговиот тип на податоци за резултат мора да одговара на типот на податоци на вредноста наведена во предикатот.
. Во некои случаи, можете да го користите клучниот збор DISTINCT за да се осигурате дека ќе се врати една вредност.
. Не можете да вклучите клаузула ORDER BY или UNION во подпрашање.
. Подпрашањето може да се наоѓа или лево или десно од условот за пребарување.
. Подпрашањата може да користат функции за агрегација без клаузула GROUP BY, која автоматски враќа посебна вредност за кој било број на редови, специјален IN предикат и изрази базирани на колони.
. Секогаш кога е можно, треба да користите JOIN приклучоци на табелата наместо подпрашања.

Примери за вгнездени прашања:

SELECT * FROM Orders WHERE SNum=(SELECT SNum FROM SalesPeople WHERE SName=’Motika’)
SELECT * FROM Orders WHERE SNum IN (ИЗБЕРЕТЕ СНУМ ОД SalesPeople WHERE City='London')
ИЗБЕРИ * ОД нарачки КАДЕ СНум=(ИЗБЕРИ РАЗЛИЧЕН СНУМ ОД нарачки КАДЕ CNum=2001)
SELECT * FROM Orders WHERE Amt>(SELECT AVG(Amt) FROM Orders WHERE Odate=10/04/1990)
ИЗБЕРЕТЕ * ОД Клиент WHERE CNum=(ИЗБЕРЕТЕ СНУМ+1000 ОД SalesPeople WHERE SName=’Serres’)

2) Поврзани подпрашања

Во SQL, можете да креирате подпрашања кои упатуваат на табела од надворешно барање. Во овој случај, подпрашањето се извршува повеќе пати, еднаш за секој ред од табелата од надворешното барање. Затоа, важно е подпрашањето да го користи индексот. Подпрашањето може да пристапи до истата табела како и надворешната. Ако надворешното барање врати релативно мал број редови, тогаш поврзаното подпрашање ќе биде побрзо од неповрзаното. Ако подпрашањето врати мал број редови, поврзаното барање ќе биде побавно од неповрзаното барање.

Примери за поврзани подпрашања:

SELECT * FROM SalesPeople Main WHERE 1(SELECT AVG(Amt) FROM Orders O2 WHERE O2.CNum=O1.CNum) //ги враќа сите нарачки чија вредност ја надминува просечната вредност на нарачката за даден клиент

3) Предикатот ПОСТОИ

Синтаксичка форма: ПОСТОИ ()

Предикатот зема подпрашање како аргумент и оценува како точно ако подпрашањето има излез, а во спротивно неточно. Подпрашањето се извршува еднаш и може да содржи неколку колони, бидејќи нивните вредности не се проверуваат, но резултатот од присуството на редови едноставно се запишува.

Забелешки за предикатот EXISTS:
. EXISTS е предикат кој враќа ТОЧНО или НЕТОЧНО и може да се користи сам или со други Булови изрази.
. EXISTS не може да користи функции за агрегација во неговото подпрашање.
. Во корелираните подпрашања, предикатот EXISTS се извршува за секој ред од надворешната табела.
. Може да го комбинирате предикатот EXISTS со приклучоци на табели.

Примери за предикатот EXISTS:

SELECT * FROM Customer WHERE EXISTS (SELECT * FROM Customer WHERE City=’San Jose’) – ги враќа сите клиенти доколку некој од нив живее во Сан Хозе.
ИЗБЕРИ РАЗЛИЧЕН СНУМ ОД Клиент Прво КАДЕ НЕ ПОСТОИ (ИЗБЕРИ * ОД Клиент Испрати КАДЕ Send.SNum=First.SNum И Send.CNumFirst.CNum) – ги враќа броевите на продавачи кои опслужувале само еден клиент.
ИЗБЕРЕТЕ РАЗЛИЧНИ F.SNum, SName, F.City FROM SalesPeople F, Customer S WHERE EXISTS (ИЗБЕРЕТЕ * ОД Клиент T WHERE S.SNum=T.SNum И S.CNumT.CNum И F.SNum=S.SNum) – враќа броеви, имиња и градови на живеење на сите продавачи кои опслужувале повеќе клиенти.
SELECT * FROM SalesPeople Frst WHERE EXISTS (SELECT * FROM Customer Send WHERE Frst.SNum=Send.SNum AND 1

4) Предикати на квантитативна споредба

Синтаксичка форма: (=|>|=|) БИЛО|СИТЕ ()

Овие предикати користат подпрашање како аргумент, меѓутоа, во споредба со предикатот EXISTS, тие се користат заедно со релациони предикати (=,>=). Во оваа смисла, тие се слични на предикатот IN, но се користат само со подпрашања. Стандардот дозволува да се користи клучниот збор SOME наместо ANY, но не го поддржуваат сите DBMS.

Забелешки за споредбените предикати:
. СИТЕ предикатот се оценува на ТОЧНО ако секоја вредност избрана за време на извршувањето на подпрашањето ја задоволува состојбата наведена во предикатот на надворешното барање. Најчесто се користи со нееднаквости.
. БИЛО предикатот се оценува на ТОЧНО ако барем една вредност избрана за време на извршувањето на подпрашањето го задоволува условот наведен во предикатот на надворешното барање. Најчесто се користи со нееднаквости.
. Ако подпрашањето не врати ниту еден ред, тогаш ALL автоматски ја зема вредноста TRUE (се смета дека условот за споредба е задоволен), а за ANY ја зема вредноста FALSE.
. Ако споредбата е ВИСТИНА за ниеден ред и има еден или повеќе редови со NULL вредност, тогаш ANY враќа UNKNOWN.
. Ако споредбата е FALSE за ниеден ред и има еден или повеќе редови со NULL вредност, тогаш СИТЕ враќа НЕПОЗНАТ.

Примери за предикатот на квантитативна споредба:

ИЗБЕРЕТЕ * ОД SalesPeople WHERE City=BY (ИЗБЕРЕТЕ ГРАД ОД клиент)
SELECT * FROM Orders WHERE Amt ALL (SELECT Rating FROM Customer WHERE City='Rome')

5) Прирок на единственост

ЕДИНСТВЕНИ|ИЗЛИЧНИ ()

Предикатот се користи за проверка на уникатноста (отсуството на дупликати) во излезните податоци на подпрашањето. Покрај тоа, во предикатот UNIQUT, низите со NULL вредности се сметаат за единствени, а во DISTINCT предикатот, две недефинирани вредности се сметаат за еднакви една со друга.

6) Поврзете прирок

НАТПРЕВАР ()

Предикатот MATCH тестира дали вредноста на низата за пребарување ќе одговара на вредноста на која било низа што произлегува од подпрашањето. Ова подпрашање се разликува од IN AND ANY предикатите по тоа што овозможува обработка на „делумни“ (PARCIAL) совпаѓања што може да се појават меѓу редовите што имаат некои NULL вредности.

7) Прашања во делот FROM

Всушност, легално е да се користи подпрашање секаде каде што е дозволена референца на табела.

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 го враќа вкупниот износ на нарачки поставени од секој клиент од Лондон.

8) Рекурзивни прашања

СО РЕКУРЗИВНИ
П1 КАКО ИЗБЕРЕ… ОД… КАДЕ…
Q2 КАКО ИЗБЕРЕ… ОД… КАДЕ…