Новосибирска държавна академия по икономика и управление

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

"БАЗА ДАННИ"

Лабораторна работа №7

„Език на SQL база данни: команди за манипулиране на данни»

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

SQL е съкращение от Structured Query Language. От името на езика става ясно, че основната му цел е да генерира заявки за получаване на информация от база данни. Командите за извличане на данни са в основата на езика за манипулиране на данни DML - неразделна част от езика SQL. DML обаче се състои от повече от команди за извличане на данни от база данни. Има и команди за промяна на данни, управление на данни и други.

Лабораторната работа разглежда основните средства на езика DML. По време на тази лабораторна работа ще се придържаме към стандарта SQL2.

Поради факта, че SQL е голям език, ще разгледаме само основните команди. Различни специфични SQL инструменти са обхванати в следващите лабораторни упражнения.

За извършване на лабораторна работа са необходими познания по основи на релационния модел на данни, основите на релационната алгебра и релационното смятане и принципите на работа с СУБД MS SQL Server.

В резултат на завършване на лабораторната работа ще овладеете методите за манипулиране на данни с помощта на езикови команди на SQL, ще разгледате диалекта на езика, внедрен в СУБД на MS SQL Server.

ВЪВЕДЕНИЕ

SQL съдържа широк набор от възможности за манипулиране на данни, както за създаване на заявки, така и за актуализиране на базата данни. Тези възможности разчитат само на логическата структура на базата данни, а не на нейната физическа структура, която е в съответствие с изискванията на релационния модел.

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

В допълнение към синтаксиса, подобен на релационното смятане, разработен в предишния стандарт, SQL2 директно имплементира операциите обединение, пресичане, разлика и съединение. Операциите за избор, проект и продукт бяха (и продължават да бъдат) поддържани почти директно, докато операциите за разделяне и присвояване се поддържат в по-тромава форма.

Първо ще опишем езика за SQL заявки и след това неговите операции за въвеждане и модифициране на данни. Операциите за модифициране на данни ще бъдат описани последни, тъй като тяхната структура зависи до известна степен от структурата на езика за заявки.

Прости запитвания

За нас проста молбаще има заявка, която има достъп само до една таблица в базата данни. Простите заявки ще ни помогнат да илюстрираме основната структура на SQL.

Проста молба.Заявка, която има достъп само до една таблица на базата данни.

Заявка:Кой работи като мазач?

WHERE SKILL_TYPE = "Шпакловчик"

Резултат:

G.Rickover

Тази заявка илюстрира трите най-често срещани фрази SQL: SELECT, FROM и WHERE. Въпреки че в нашия пример ги поставихме на различни редове, всички те могат да се появят на един ред. Те също могат да бъдат с различни отстъпи и думите във фрази могат да бъдат разделени с произволен брой интервали. Нека да разгледаме характеристиките на всяка фраза.

Изберете. Клаузата SELECT изброява колоните, които трябва да се появят в получената таблица. Това винаги са колони от някаква релационна таблица. В нашия пример получената таблица се състои от една колона (NAME), но по принцип може да съдържа няколко колони; може също да съдържа изчислени стойности или константи. Ще дадем примери за всяка от тези опции. Ако получената таблица трябва да съдържа повече от една колона, тогава всички необходими колони са изброени след командата SELECT, разделени със запетаи. Например фразата SELECT WORKER_ID, NAME ще доведе до таблица, състояща се от колоните WORKER_ID и NAME.

Клауза SELECT.Указва колоните на получената таблица.

от. Клаузата FROM указва една или повече таблици, достъпни от заявката. Всички колони, изброени в клаузите SELECT и WHERE, трябва да съществуват в една от таблиците, изброени в командата FROM. В SQL2 тези таблици могат да бъдат директно дефинирани в схемата като базови таблици или изгледи на данни, или самите те могат да бъдат неименувани таблици в резултат на SQL заявки. В последния случай заявката е изрично дадена в командата FROM.

Фразата FROM.Указва съществуващите таблици, до които има достъп чрез заявката.

Където. Клаузата WHERE съдържа условие. въз основа на които се избират редовете на таблицата(ите). В нашия пример условието е, че колоната SKILL_TYPE трябва да съдържа константата "Plasterer", оградена с апостроф, както винаги се прави с текстови константи в SQL. Клаузата WHERE е най-променливата SQL команда; може да съдържа много различни условия. Голяма част от нашата дискусия ще бъде посветена на илюстрирането на различните конструкции, разрешени в командата WHERE.

WHERE клауза.Указва условието, въз основа на което се избират редове от посочените таблици.

Горната SQL заявка се обработва от системата в следния ред: FROM, WHERE, SELECT. Тоест редовете на таблицата, посочени в командата FROM, се поставят в работната област за обработка. След това клаузата WHERE се прилага към всеки ред последователно. Всички редове, които не отговарят на условието WHERE, се изключват от разглеждане. След това тези редове, които отговарят на условието WHERE, се обработват от оператора SELECT. В нашия пример NAME се избира от всеки такъв ред и всички избрани стойности се извеждат като резултати от заявката.

Заявка:Предоставяне на цялата информация за офис сгради.

WHERE TYPE = "Офис"

Резултат:

BLDG IDADDRESSTYPEQLTY LEVELSTATUS

ул. Елм 312, офис 2 2 2

ул. Березовая 210 1011 Офис Z 1

ул. Осиновая 111 1213 Office 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?

WHERE HRLY_RATE > = 10 И HRLY_RATE< - 12

Резултат:

РАБОТНИЧЕСКА ИДЕНТИФИКАЦИЯИМЕ HRLY_RATE SKILL_TYPE SUPV_ID

Тази заявка илюстрира някои от допълнителните функции на оператора WHERE: оператори за сравнение и булев оператор AND. Шест оператора за сравнение (=,<>(не е равно),<, >, <=, >=). Булевите оператори И, ИЛИ и НЕ могат да се използват за създаване на съставни условия или за отхвърляне на условие. Скобите могат да се използват за групиране на условия, както е обичайно в езиците за програмиране.

Оператори за сравнение =,<>, <, >, <=, >=.

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

Можете също да използвате оператора BETWEEN (между), за да формулирате тази заявка:

WHERE HRLY_RATE МЕЖДУ 10 И 12

BETWEEN може да се използва за сравняване на количество с две други количества, първото от които е по-малко от второто, ако сравняваното количество може да бъде равно на всяко от тези количества или всяка стойност между тях.

Заявка: Избройте мазачи, покриви и електротехници.

WHERE SKILL_TYPE IN ("Шпакловчик", "Покривач", "Електротехник")

Резултат:

WORKER_ID ИМЕ HRLY_RATE SKILL_TYPE SUPV_ID

1412 К.Немо 13.75 Шпакловчик 1520г

2920 Р. Гарет 10.00 Покривач 2920

1520 G. Риковер 11.75 Мазилка 1520

Тази заявка обяснява използването на оператора за сравнение IN (B). Условието WHERE се счита за вярно, ако типът специалност на реда се намира вътре в набора, посочен в скоби, т.е. ако типът специалност е мазач, покривач или електротехник. Ще видим оператора IN отново в подзаявките.

Да предположим, че не можем да си спомним точно изписването на нашата специалност: „електротехник“ или „инженер по електроника“ или нещо друго. Заместващите символи, които заместват недефинирани символни низове, улесняват намирането на неточни изписвания в заявка.

Шаблонни символи.Знаци, които заместват недефинирани символни низове.

Заявка:Избройте служителите, чийто вид специалност започва с „Елек“.

WHERE SKILL_TYPE LIKE ("Elect%")

Резултат:

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

1235 М. Фарадей 12.50 Електротехник 1311

1311 H. Columbus 15.50 Electric 1311

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

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

ИМЕ КАТО „__K%“

Условието в първия пример е вярно, ако NAME се състои от два знака, последвани от "Columbus". В таблицата WORKER всички имена започват с първи инициал и точка. По този начин, използвайки това условие ние. Нека намерим всички служители с фамилното име "Колумб". Условието от втория пример ни позволява да намерим всички служители, чиито фамилни имена започват с буквата „К“.

Заявка:Намерете всички работни места, които започват през следващите две седмици.

WHERE START_DATE BETWEEN 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 + ИНТЕРВАЛ „14“ ДЕН

добавя двуседмичен период към текущата дата. По този начин ASSIGNMENT е избрано (ако приемем, че днес е 10/10), ако стойността му в колона START_DATE е между 10/10 и 10/24. От това можем да видим, че можем да добавяме интервални стойности към полетата за дата. Освен това можем да умножим стойностите на интервалите по цели числа. Да предположим например, че искаме да разберем какво число ще бъде след определен брой седмици (обозначено с променливата NUM_WEEKS). Можем да го направим така:

CURRENT_DATE + ИНТЕРВАЛ „7“ ДЕН * NUM_СЕДИЦИ

2. Многотаблични заявки

Способността да се свързват елементи от данни през границите на една таблица е важна за всеки език на базата данни. В релационната алгебра тази функция се изпълнява от операцията за присъединяване. Въпреки че голяма част от SQL се базира директно на релационно смятане, SQL свързва данни от различни таблици по начин, подобен на операцията за свързване на релационната алгебра. Сега ще покажем как се прави това. Обмислете искането:

Заявка:

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

ИЗБЕРЕТЕ SKILL_TYPE

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

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID

И BLDG_ID = 435

Какво става тук? Трябва да разгледаме два етапа в начина, по който системата обработва тази заявка.

1. Както обикновено, първо се обработва клаузата FROM. В този случай обаче, тъй като командата указва две таблици, системата създава декартово произведение на редовете на тези таблици. Това означава, че се създава една голяма таблица (логично), състояща се от колони от двете таблици, като всеки ред от една таблица е свързан с всеки ред от другата таблица. В нашия пример, тъй като таблицата WORKER има пет колони, а таблицата ASSIGNMENT има четири колони, декартовият продукт, получен от командата FROM, ще има девет колони. Общият брой редове на декартовото произведение е равен на m * n, където m е броят на редовете на таблицата WORKER; и n е броят на редовете в таблицата ASSIGNMENT. Тъй като таблицата WORKER има 7 реда, а таблицата ASSIGNMENT има 19 реда, декартовият продукт ще съдържа 7x19 или 133 реда. Ако командата FROM изброява повече от две таблици, се създава декартово произведение на всички таблици, посочени в командата.

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

2. След създаването на гигантската релационна таблица системата използва командата WHERE както преди. Всеки ред от таблицата, създаден от командата FROM. се проверява дали условието WHERE е изпълнено. Редове, които не отговарят на условието, се изключват от разглеждане. След това клаузата SELECT се прилага към останалите редове.

Клаузата WHERE в нашата заявка съдържа две условия:

1. РАБОТНИК. WORKER_ID = ASSIGNMENT.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. Обединяване на таблиците WORKER и ASGNMENT

Сега ще покажем как да присъедините таблица към себе си в SQL.

Заявка:Избройте служителите, като посочите имената на техните ръководители.

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

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

WHERE B.WORKER_ID = A.SUPV_ID

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

Ориз. 2. Обединяване на две копия на таблицата WORKER

Избирайки две имена на служители от всеки ред, получаваме необходимия списък:

A.NAMEB.NAME

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

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

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

Псевдоним.Алтернативно име, дадено на таблицата.

A.WORKER_NAME представлява работника, а B.WORKER_NAME представлява мениджъра. Моля, обърнете внимание, че някои работници са собствени мениджъри, което следва от равенството WORKER_ID - SUPV_ID в техните редове.

В SQL можете да свържете повече от две таблици наведнъж:

Заявка

ИЗБЕРЕТЕ WORKER_NAME

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

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

TYPE = "Офис"

Резултат:

М. Фарадей

G.Rickover

J.Barrister

Обърнете внимание, че ако име на колона (например WORKER_ID или BLDG_ID) се появи в повече от една таблица, тогава, за да избегнем двусмислие, трябва да поставим пред името на колоната името на оригиналната таблица. Но ако името на колоната се среща само в една таблица, като TYPE в нашия пример, тогава няма двусмислие, така че името на таблицата не трябва да се посочва.

SQL командите в тази заявка създават една таблица от три таблици на релационна база данни. Първите две таблици се обединяват от WORKER_ID, след което третата таблица се присъединява от BLDG_ID към получената таблица. Състояние

TYPE = "Офис"

Клаузата WHERE кара всички редове да бъдат изключени, с изключение на тези за офис сгради. Това отговаря на изискванията на заявката.

3. Подзаявки

Подзаявка.Заявка в заявка

Подзаявка може да бъде поставена в клаузата WHERE на заявка, като по този начин се разширяват възможностите на клаузата WHERE. Нека разгледаме един пример.

Заявка:Какви са специалностите на работниците, назначени в сграда 435?

ИЗБЕРЕТЕ SKTLL_TYPE

FROM WORKER WHERE WORKER_ID IN

(ИЗБЕРЕТЕ WORKER_ID

WHERE BLDG_ID = 435)

Подзаявка в този пример

(ИЗБЕРЕТЕ WORKER_ID

WHERE BLDG_ID = 435)

Извиква се заявка, която съдържа подзаявка външна заявкаили основна молба. Подзаявката води до създаването на следния набор от идентификатори на служители:

РАБОТНИЧЕСКА ИДЕНТИФИКАЦИЯ

Външна заявка.Основната заявка, която съдържа всички подзаявки.

След това този набор от идентификатори заема мястото на подзаявка във външната заявка. От този момент нататък външната заявка се изпълнява с помощта на набора, създаден от подзаявката. Външната заявка обработва всеки ред от таблицата WORKER според клаузата WHERE. Ако WORKER_ID на ред се намира в (IN) набора, създаден от подзаявката, тогава SKILL_TYPE на реда се избира и показва в получената таблица:

ТИП УМЕНИЕ

Шпакловчик

Покривач

Електротехник

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

Имайте предвид, че подзаявката може логически да бъде изпълнена, преди поне един ред да бъде разгледан от основната заявка. В известен смисъл подзаявката е независима от основната заявка. Може да се изпълни като пълна заявка. Казваме, че такава подзаявка не е свързана с основната заявка. Както ще видим скоро, подзаявките могат да бъдат корелирани.

Некорелирана подзаявка.Подзаявка, чиято стойност не зависи от всяка външна заявка.

Ето пример за подзаявка в рамките на подзаявка.

Заявка: Избройте служителите, назначени в офис сградите.

Отново разглеждаме заявката, с която проверихме връзката.

ИЗБЕРЕТЕ WORKER_MAME

WHERE WORKER_ID IN

(ИЗБЕРЕТЕ WORKER_ID

WHERE BLDG_ID IN

WHERE TYPE = "Офис"))

Резултат:

М. Фарадей

G.Rickover

J.Barrister

Обърнете внимание, че не е необходимо да добавяме префикс към имената на колоните с имена на таблици навсякъде, тъй като всяка подзаявка обработва една и само една таблица, така че не могат да възникнат неясноти.

Изпълнението на заявката се извършва в ред отвътре навън. Тоест първо се изпълнява най-вътрешната заявка (или "най-долната"), след това се изпълнява подзаявката, която я съдържа, и след това външната заявка.

Корелирани подзаявки. Всички подзаявки, обсъдени по-горе, бяха независими от основните заявки, в които бяха използвани. Под независими имаме предвид, че подзаявките могат да се изпълняват сами като пълни заявки. Сега преминаваме към разглеждане на клас подзаявки, чиито резултати от изпълнението могат да зависят от реда, разглеждан от основната заявка. Такива подзаявки се наричат ​​корелирани подзаявки.

Корелирана подзаявка. Подзаявка, чийто резултат зависи от реда, разглеждан от основната заявка.

Заявка:Избройте служителите, чиито почасови ставки са по-високи от тези на техните мениджъри.

ИЗБЕРЕТЕ WORKER_NAME

WHERE A.HRLY_RATE >

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

WHERE B.WORKER_ID = A.SUPV_ID)

Резултат:

Логичните стъпки за изпълнение на тази заявка са:

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

2. След това системата разглежда всеки ред A. Даден ред се избира, ако отговаря на условието WHERE. Това условие означава, че даден ред ще бъде избран, ако неговата стойност HRLY_RATE е по-голяма от HRLY_RATE, генерирана от подзаявката.

3. Подзаявката избира стойността HRLY_RATE от ред B, чийто WORKER_ID е равен на SUPV_ID на ред A, който в момента се разглежда от основната заявка. Това е HRLY_RATE на мениджъра.

Имайте предвид, че тъй като A.HRLY_RATE може да се сравнява само с една стойност, подзаявката трябва да върне само една стойност. Тази стойност се променя в зависимост от това кой ред A се разглежда. По този начин подзаявката е свързана с основната заявка. Ще видим още примери за корелирани подзаявки по-късно, когато изучаваме вградените функции.

Оператори EXISTS и NOT EXISTS

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

ИЗБЕРЕТЕ WORKER_ID

WHERE BLDG_ID NOT 435

За съжаление, това е неправилна формулировка на решението. Искането просто ще ни даде идентификационните номера на работници, работещи в други сгради. Очевидно някои от тях могат да бъдат причислени и към сграда 435.

Правилно формулираното решение използва оператора NOT EXISTS:

ИЗБЕРЕТЕ WORKER_ID

КЪДЕТО НЕ СЪЩЕСТВУВА

WHERE ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID И

Резултат:

WORKER_ID

Операторите EXISTS и NOT EXISTS винаги се поставят преди подзаявката. EXISTS се оценява на true, ако наборът, генериран от подзаявката, не е празен. Ако наборът, генериран от подзаявката, е празен, тогава EXISTS приема стойността „false“. Операторът NOT EXISTS, разбира се, работи точно обратното. Вярно е, ако резултатът от подзаявката е празен, и невярно в противен случай.

Оператор EXISTS. Връща true, ако наборът от резултати не е празен.

НЕ СЪЩЕСТВУВА оператор. Връща true, ако наборът от резултати е празен.

В този пример използвахме оператора NOT EXISTS. Подзаявката избира всички редове от таблицата ASSIGNMENT, в които WORKER_ID има същата стойност като реда, разглеждан от основната заявка, а BLDG_ID е равен на 435. Ако този набор е празен, тогава работният ред, разглеждан от основната заявка, е избран, тъй като това означава, че Този служител не работи в сграда 435.

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

ИЗБЕРЕТЕ WORKER_ID

КЪДЕТО WORKER_ID НЕ Е ВЪВ

(ИЗБЕРЕТЕ WORKER_ID

WHERE BLDG_ID = 435)

Това решение е по-просто от решението с оператора NOT EXISTS. Възниква естествен въпрос: защо имаме нужда от СЪЩЕСТВУВАНЕ и изобщо НЕ СЪЩЕСТВУВА? Отговорът е, че NOT EXISTS е единственият начин за решаване на заявки, които съдържат думата "всеки" в условието. Такива заявки се решават в релационната алгебра с помощта на операцията за деление и в релационното смятане с помощта на универсалния квантор. Ето пример за заявка с думата „всеки“ в нейното условие:

Заявка:Избройте служителите, назначени за всяка сграда.

Този въпрос може да бъде реализиран в SQL с помощта на двойни отрицания. Ще преформулираме заявката, за да включим двойно отрицание:

Заявка:Избройте такива служители, за които Неима сграда, към която не са зачислени.

Подчертахме двойното отрицание. Ясно е, че това искане е логически еквивалентно на предишното.

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

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

КЪДЕТО НЕ СЪЩЕСТВУВА

ASSIGNMENT.WORKER_ID = 1234)

Отбелязахме тази заявка (I), защото ще я разгледаме по-късно. Ако няма сграда, която да отговаря на тази заявка, тогава работник 1234 се присвоява на всяка сграда и следователно удовлетворява условията на първоначалната заявка. За да получим решение на оригиналната заявка, трябва да обобщим заявката (I) от конкретен работник 1234 към променливата WORKER_ID и да превърнем тази модифицирана заявка в подзаявка на по-голямата заявка. Ето решението:

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

КЪДЕТО НЕ СЪЩЕСТВУВА

КЪДЕТО НЕ СЪЩЕСТВУВА

WHERE ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID И

ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID)

Резултат:

РАБОТНИЧЕСКА ИДЕНТИФИКАЦИЯ

Имайте предвид, че подзаявката, започваща на четвъртия ред на заявка (II), е идентична на заявка (I), като "1234" е заменено с WORKER.WORKER_ID. Запитване (II) може да се прочете, както следва:

Изберете WORKER_ID от WORKER, ако няма сграда, към която да не е присвоен WORKER_ID.

Това отговаря на условията на първоначалната заявка.

Виждаме, че операторът NOT EXISTS може да се използва за формулиране на онези заявки, които изискват операция за деление в релационната алгебра и универсален квантор в релационното смятане. От гледна точка на лесна употреба, операторът NOT EXISTS не предлага особена полза, което означава, че SQL заявките, които използват NOT EXISTS два пъти, не са по-лесни за разбиране от решенията на релационната алгебра с деление или решенията на релационното смятане с универсални квантори. Ще са необходими повече изследвания за създаване на езикови конструкции, които позволяват подобни заявки да бъдат решавани по-естествено.

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

Нека разгледаме въпроси от този тип:

Какви са максималната и минималната почасова ставка? Какъв е средният брой дни, в които служителите работят в сграда 435? Какъв е общият брой дни, предвидени за мазилка на сграда 312? Колко различни специалности има?

Отговорът на тези въпроси изисква статистически функции, които разглеждат много редове в таблица и връщат една стойност. В SQL има пет такива функции, наречени вградени функции или зададени функции. Тези функции са SUM (сума), AVG (средно), COUNT (количество), MAX (максимум) и MIN (минимум).

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

Заявка:Какви са максималната и минималната почасова ставка?

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

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

Функциите MAX и MIN работят върху една колона на таблицата. Те избират съответно максималната или минималната стойност от тази колона. Нашата формулировка на заявка не съдържа клауза WHERE. За повечето заявки това може да не е така, както показва следващият ни пример.

Заявка:Какъв е средният брой дни, в които служителите работят в сграда 435?

ИЗБЕРЕТЕ СР.(NUM_DAYS)

WHERE BLDG_ID =435

Резултат: 12.33

Заявка:Какъв е общият брой дни, предвидени за мазилка на сграда 312?

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

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

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID И

SKILL_TYPE = "Шпакловчик" И

Резултат: 27

Решението използва свързване между таблиците ASSIGNMENT и WORKER. Това е необходимо, защото SKILL_TYPE е в таблицата WORKER, а BLDG_ID е в таблицата ASSIGNMENT.

Заявка:Колко различни специалности има?

ИЗБЕРЕТЕ БРОЯ (DISTINCT SKILL_TYPE)

Резултат: 4

Тъй като една и съща специалност може да се появи в множество различни редове, трябва да използвате ключовата дума DISTINCT в тази заявка, за да попречите на системата да преброи един и същ тип специалност повече от веднъж. Операторът DISTINCT може да се използва с всяка от вградените функции, въпреки че, разбира се, е излишен с функциите MAX и MIN.

РАЗЛИЧЕН. Оператор, който елиминира дублиращи се редове.

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

Заявка:Каква е средната седмична заплата?

ИЗБЕРЕТЕ СРЕДНО (40 * HRLY_RATE)

Резултат: 509.14

COUNT може да се отнася за цял ред, а не за отделна колона :

Заявка: Колко сгради имат ниво на качество 3?

ИЗБЕРЕТЕ БРОЯ (*)

ОТ СГРАДА КЪДЕ

Резултат: 3

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

клаузи GROUP BY и HAVING

При управлението често се изисква статистическа информация за всяка група в много групи. Например, разгледайте следната заявка:

Заявка:За всеки мениджър разберете максималната часова ставка сред неговите подчинени.

За да разрешим този проблем, трябва да разделим работниците на групи според техните ръководители. След това ще определим максималната оферта във всяка група. В SQL това се прави по следния начин:

ГРУПИРАНЕ ПО SUPV_ID

Резултат:

SUPV_IDMAX(HRLY RATE)

Когато обработва тази заявка, системата първо разделя редовете на таблицата WORKER на групи, като използва следното правило. Редовете се поставят в една и съща група, ако и само ако имат един и същ SUPV_ID. След това клаузата SELECT се прилага към всяка група. Тъй като в тази група има само една стойност на SUPV_ID, в групата няма несигурност на SUPV_ID. За всяка група клаузата SELECT извежда SUPV_ID и също така изчислява и извежда стойността MAX(HRLY_RATE). Резултатът е представен по-горе.

В команда SELECT с вградени функции могат да се появят само онези колони, които са включени в клаузата GROUP BY. Имайте предвид, че SUPV_ID може да се използва в команда SELECT, тъй като е включена в клаузата GROUP BY.

Клауза GROUP BY. Показва, че редовете трябва да бъдат разделени на групи с общи стойности на определените колони.

Клаузата GROUP BY ви позволява да извършвате определени сложни изчисления. Например, може да искаме да разберем средната стойност на тези максимални оферти. Изчисленията с вградени функции обаче са ограничени в смисъл, че не позволяват вградените функции да се използват в други вградени функции. Така че израз като

СР.(МАКС.(HRLY_RATE))

забранен. Изпълнението на такова искане ще се състои от два етапа. Първо трябва да поставим максималните оферти в нова таблица, а във втората стъпка трябва да изчислим тяхната средна стойност.

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

Заявка:За всеки тип сграда разберете средното ниво на качество сред сградите със статус 1.

ИЗБЕРЕТЕ ТИП, СРЕДНО(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.

ИЗБЕРЕТЕ ТИП, СРЕДНО (QLTY_JLEVEL)

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

ПРИТЕЖАВАНЕ НА МАКС.(QLTY_LEVEL)<= 3

Резултат:

СРЕДЕН ТИП (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 >

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

Резултат:

Х. Колумб

Имайте предвид, че подзаявката не е свързана с основната заявка. Подзаявката връща точно една стойност - средната часова ставка. Основната заявка избира работник само ако неговата ставка е по-голяма от изчислената средна стойност.

Корелираните заявки могат също да използват вградени функции:

Въпрос: Кой служител има часова ставка, по-висока от средната часова ставка сред подчинените на същия мениджър?

В този случай, вместо да изчисляваме една средна часова ставка за всички работници, трябва да изчислим средната ставка за всяка група работници, докладващи на един и същ ръководител. Освен това нашето изчисление трябва да се направи наново за всеки работник, разглеждан от основната заявка:

ИЗБЕРЕТЕ A. WORKER_NAME

SQL ви позволява да влагате заявки една в друга. Обикновено подзаявката връща една стойност, която се проверява, за да се види дали предикатът е верен.

Типове думи за търсене:
. Сравнение с резултата от подзаявка (=, >=)
. Проверка за принадлежност към резултатите от подзаявка (IN)
. Проверка за съществуване (EXISTS)
. Многократно (количествено) сравнение (ВСЯКО, ВСИЧКО)

Бележки за вложени заявки:
. Подзаявката трябва да избере само една колона (с изключение на подзаявка с предикат EXISTS), а нейният тип данни за резултата трябва да съответства на типа данни на стойността, посочена в предиката.
. В някои случаи можете да използвате ключовата дума DISTINCT, за да сте сигурни, че се връща една стойност.
. Не можете да включите клауза ORDER BY или UNION в подзаявка.
. Подзаявката може да бъде разположена отляво или отдясно на условието за търсене.
. Подзаявките могат да използват функции за агрегиране без клауза GROUP BY, които автоматично връщат специална стойност за произволен брой редове, специален IN предикат и изрази, базирани на колони.
. Когато е възможно, трябва да използвате JOIN обединяване на таблици вместо подзаявки.

Примери за вложени заявки:

SELECT * FROM Orders WHERE SNum=(SELECT SNum FROM SalesPeople WHERE SName=’Motika’)
ИЗБЕРЕТЕ * ОТ Поръчки WHERE SNum IN (ИЗБЕРЕТЕ SNum FROM SalesPeople WHERE City=’London’)
SELECT * FROM Orders 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) Свързани подзаявки

В SQL можете да създавате подзаявки, които препращат към таблица от външна заявка. В този случай подзаявката се изпълнява многократно, веднъж за всеки ред на таблица от външната заявка. Следователно е важно подзаявката да използва индекса. Подзаявка може да има достъп до същата таблица като външна. Ако външната заявка върне относително малък брой редове, тогава свързаната подзаявка ще бъде по-бърза от несвързаната. Ако подзаявка върне малък брой редове, свързаната заявка ще бъде по-бавна от несвързаната заявка.

Примери за свързани подзаявки:

SELECT * FROM SalesPeople Main WHERE 1(SELECT AVG(Amt) FROM Orders O2 WHERE O2.CNum=O1.CNum) //връща всички поръчки, чиято стойност надвишава средната стойност на поръчката за даден клиент

3) Предикат СЪЩЕСТВУВА

Синтактична форма: СЪЩЕСТВУВА ()

Предикатът приема подзаявка като аргумент и се оценява като true, ако подзаявката има изход, и false в противен случай. Подзаявката се изпълнява веднъж и може да съдържа няколко колони, тъй като техните стойности не се проверяват, но резултатът от наличието на редове просто се записва.

Бележки относно предиката EXISTS:
. EXISTS е предикат, който връща TRUE или FALSE и може да се използва самостоятелно или с други булеви изрази.
. EXISTS не може да използва функции за агрегиране в своята подзаявка.
. В корелирани подзаявки предикатът EXISTS се изпълнява за всеки ред от външната таблица.
. Можете да комбинирате предиката EXISTS с обединения на таблици.

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

SELECT * FROM Customer WHERE EXISTS(SELECT * FROM Customer WHERE City=’San Jose’) – връща всички клиенти, ако някой от тях живее в Сан Хосе.
SELECT DISTINCT SNum FROM Customer First WHERE NOT EXISTS (SELECT * FROM Customer Send WHERE Send.SNum=First.SNum AND Send.CNumFirst.CNum) – връща номерата на продавачите, обслужили само един клиент.
SELECT DISTINCT F.SNum, SName, F.City FROM SalesPeople F, Customer S WHERE EXISTS (SELECT * FROM Customer T WHERE S.SNum=T.SNum AND S.CNumT.CNum AND F.SNum=S.SNum) – връща номера, имена и населени места на всички продавачи, обслужили няколко клиента.
ИЗБЕРЕТЕ * ОТ SalesPeople Frst WHERE EXISTS (ИЗБЕРЕТЕ * ОТ Customer Send WHERE Frst.SNum=Send.SNum AND 1

4) Предикати на количествено сравнение

Синтактична форма: (=|>|=|) ВСЯКАКВИ|ВСИЧКИ ()

Тези предикати използват подзаявка като аргумент, но в сравнение с предиката EXISTS, те се използват заедно с релационни предикати (=,>=). В този смисъл те са подобни на предиката IN, но се използват само с подзаявки. Стандартът позволява използването на ключовата дума SOME вместо ANY, но не всички СУБД я поддържат.

Бележки относно предикатите за сравнение:
. Предикатът ALL се оценява на TRUE, ако всяка стойност, избрана по време на изпълнението на подзаявката, удовлетворява условието, посочено във външния предикат на заявката. Най-често се използва при неравности.
. Предикатът ANY се оценява на TRUE, ако поне една стойност, избрана по време на изпълнението на подзаявката, удовлетворява условието, посочено във външния предикат на заявката. Най-често се използва при неравности.
. Ако подзаявката не върне нито един ред, тогава ALL автоматично приема стойността TRUE (счита се, че условието за сравнение е изпълнено), а за ANY приема стойността FALSE.
. Ако сравнението е TRUE за нито един ред и има един или повече редове с NULL стойност, тогава ANY връща UNKNOWN.
. Ако сравнението е FALSE за нито един ред и има един или повече редове с NULL стойност, тогава ALL връща UNKNOWN.

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

SELECT * FROM SalesPeople WHERE City=ANY(SELECT City FROM Customer)
SELECT * FROM Orders WHERE Amt ALL(SELECT Rating FROM Customer WHERE City=’Rome’)

5) Предикат за уникалност

УНИКАЛЕН|ОТЛИЧЕН ()

Предикатът се използва за проверка на уникалността (отсъствието на дубликати) в изходните данни на подзаявката. Освен това в предиката UNIQUT низовете с NULL стойности се считат за уникални, а в предиката DISTINCT две недефинирани стойности се считат за равни една на друга.

6) Предикат за съвпадение

СЪВПАДА ()

Предикатът MATCH тества дали стойността на низ на заявка ще съвпадне със стойността на всеки низ, получен от подзаявката. Тази подзаявка се различава от предикатите IN AND ANY по това, че позволява обработка на „частични“ (PARTIAL) съвпадения, които могат да възникнат сред редове, които имат някои NULL стойности.

7) Заявки в секцията ОТ

Всъщност е законно да се използва подзаявка навсякъде, където е разрешена препратка към таблица.

ИЗБЕРЕТЕ 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
//подзаявка връща общото количество поръчки, направени от всеки клиент от Лондон.

8) Рекурсивни заявки

С РЕКУРСИВ
Q1 AS SELECT … FROM … WHERE …
Q2 КАТО ИЗБОР ... ОТ ... КЪДЕ ...

КЪДЕТО СЪЩЕСТВУВА

Подзаявката се проверява за наличие на един или повече редове. Ако поне един ред съответства на заявката, се връща булевата стойност TRUE. Когато е посочена незадължителната ключова дума NOT, булевата стойност TRUE се връща, ако подзаявката не върне съвпадащи редове.

подзаявка

Въз основа на напълно оформената подзаявка се извлича полученият набор от данни.

Общи правила

Операторът EXISTS тества съществуването на един или повече редове в подзаявка на родителска заявка.

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

Този пример проверява подзаявката за записи с помощта на допълнителната ключова дума NOT. Следващият пример търси конкретни записи в подзаявка, за да извлече основния набор от резултати.

SELECT au_lname FROM автори WHERE EXISTS (SELECT * FROM издатели WHERE authors.city=publishers.city);

Тази заявка връща фамилните имена на авторите (au_lname), които живеят в същия град като издателите. Имайте предвид, че можете да използвате звездичка в подзаявката, защото подзаявката трябва да върне само един запис с булева стойност TRUE. В такива случаи колоните нямат значение. Ключовият момент е съществуването на низа.

В много заявки операторът EXISTS изпълнява същата функция като ANY. Операторът EXISTS обикновено е най-ефективен, когато се използва с корелирани заявки.

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

Подзаявка в оператор EXISTS обикновено изпълнява един от двата типа търсения. Първата опция е да използвате заместващ знак - звездичка (например ИЗБЕРЕТЕ * ОТ...), в който случай не извличате конкретна колона или стойност. Звездицата тук означава "всяка колона". Втората опция е да изберете само една конкретна колона в подзаявката (например SELECT aujd FROM). Някои отделни платформи позволяват подзаявки за множество колони (напр. SELECT aujd, aujname FROM...). Тази функция обаче е рядка и трябва да се избягва в код, който трябва да бъде пренесен към други платформи.

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

Всички платформи поддържат оператора EXISTS във формата, която описахме по-горе.

Освен това можете да използвате Оператор EXISTS. Този оператор често се използва с корелирани подзаявки, за да се провери дали стойността, извлечена от външната заявка, съществува в набора от резултати, извлечен от вътрешната заявка. Ако подзаявката върне поне един ред, операторът връща TRUE. Ако стойността не съществува, операторът връща FALSE. Съответно операторът NOT EXISTS проверява дали стойността, извлечена от външната заявка, не съществува в набора от резултати, извлечен от вътрешната заявка.

Операторът EXISTS тества съществуването на редове в резултатния набор от подзаявка.

Ако стойността на низа на подзаявката бъде намерена:

• търсенето не продължава във вътрешната заявка;

  условието е отбелязано ВЯРНО.

Ако стойността на низа на подзаявката не е намерена:

• условието е маркирано като FALSE ;

  търсенето продължава във вътрешната заявка.

Оператор EXISTS

Всички логически оператори са приложими с вложени оператори SELECT. Освен това можете да използвате оператора EXISTS. Този оператор често се използва с корелирани подзаявки, за да се провери дали стойността, извлечена от външната заявка, съществува в набора от резултати, извлечен от вътрешната заявка. Ако подзаявката върне поне един ред, операторът връща TRUE. Ако стойността не съществува, операторът връща FALSE. Съответно операторът NOT EXISTS проверява дали стойността, извлечена от външната заявка, не съществува в набора от резултати, извлечен от вътрешната заявка.