Acasă › DE › Subinterogări imbricate și legate în SQL, predicat EXISTS. Utilizarea operatorului EXISTS Interogări folosind funcția exists

Subinterogări imbricate și legate în SQL, predicat EXISTS. Utilizarea operatorului EXISTS Interogări folosind funcția exists

UNDE EXISTA

Subinterogarea este verificată pentru prezența unuia sau mai multor rânduri. Dacă cel puțin un rând se potrivește cu interogarea, este returnată valoarea booleană TRUE. Când este specificat cuvântul cheie opțional NOT, valoarea booleană TRUE este returnată dacă subinterogarea nu returnează niciun rând care se potrivește.

subinterogare

Pe baza subinterogării complet formate, setul de date rezultat este preluat.

Reguli generale

Operatorul EXISTS testează existența unuia sau mai multor rânduri într-o subinterogare a unei interogări părinte.

SELECT * FROM jobs WHERE NU EXISTIS (SELECT * FROM angajat WHERE jobs.job_id=employye.job_id);

Acest exemplu verifică subinterogarea înregistrări folosind cuvântul cheie suplimentar NOT. Următorul exemplu caută înregistrări specifice într-o subinterogare pentru a prelua setul principal de rezultate.

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

Această interogare returnează numele de familie ale autorilor (au_lname) care locuiesc în același oraș cu editorii. Rețineți că puteți utiliza un asterisc în subinterogare, deoarece subinterogarea trebuie să returneze doar o înregistrare cu valoarea booleană TRUE. În astfel de cazuri, coloanele nu contează. Punctul cheie este existența șirului.

În multe interogări, operatorul EXISTS îndeplinește aceeași funcție ca ANY. Operatorul EXISTS este de obicei cel mai eficient atunci când este utilizat cu interogări corelate.

Operatorul EXISTS este echivalent semantic cu operatorul ANY.

O subinterogare într-o instrucțiune EXISTS efectuează de obicei unul dintre cele două tipuri de căutări. Prima opțiune este să utilizați un wildcard - un asterisc (de exemplu, SELECT * FROM...), caz în care nu recuperați nicio coloană sau valoare specifică. Asteriscul aici înseamnă „orice coloană”. A doua opțiune este să selectați o singură coloană specifică în subinterogare (de exemplu, SELECT aujd FROM). Unele platforme individuale permit subinterogări pe mai multe coloane (de exemplu, SELECT aujd, aujname FROM...). Cu toate acestea, această caracteristică este rară și ar trebui evitată în codul care trebuie portat pe alte platforme.

Diferențele între platforme

Toate platformele acceptă operatorul EXISTS în forma descrisă mai sus.

„A fost mai ușor înainte” - m-am gândit în timp ce mă așezam pentru a optimiza următoarea interogare în SQL studio de management. Când am scris în MySQL, totul era cu adevărat mai simplu - fie funcționează, fie nu. Ori încetinește, ori nu. Explică mi-a rezolvat toate problemele, nu mai era nevoie de nimic. Acum am un mediu puternic pentru dezvoltarea, depanarea și optimizarea interogărilor și procedurilor/funcțiilor, iar toată această aglomerație creează doar mai multe probleme în opinia mea. Și de ce toate? Deoarece optimizatorul de interogări încorporat este rău. Dacă în MySQL și PostgreSQL scriu

Selectați * din a, b, c unde a.id = b.id, b.id = c.id

și fiecare dintre tablete va avea cel puțin 5k linii - totul va îngheța. Și slavă Domnului! Pentru că altfel dezvoltatorul, în cel mai bun caz, dezvoltă lenea de a scrie corect și, în cel mai rău caz, nu înțelege deloc ce face! La urma urmei, aceeași interogare în MSSQL va funcționa similar

Selectați * dintr-o îmbinare b pe a.id = b.id unire c pe b.id = c.id

Optimizatorul încorporat va pieptăna cererea redundantă și totul va fi în regulă.

De asemenea, va decide singur ce este mai bine să facă - să existe sau să se alăture și multe altele. Și totul va funcționa cât mai optim posibil.

Există un singur DAR. La un moment dat, optimizatorul se va împiedica interogare complexăși trece, și atunci ai o problemă uriașă. Și s-ar putea să nu o obțineți imediat, ci când greutatea meselor atinge masa critică.

Așadar, aici este punctul articolului. există și sunt operațiuni foarte grele. Aceasta este de fapt o subinterogare separată pentru fiecare linii de rezultat. Și dacă există și cuibărit, atunci în general stinge lumina. Totul va fi ok când vor fi returnate 1, 10, 50 de linii. Nu vei simți diferența și, poate, alăturarea va fi și mai lentă. Dar când 500 este scos, încep problemele. 500 de subinterogări într-o singură solicitare este serios.

Deși din punctul de vedere al înțelegerii umane, în și există sunt mai bune, dar din punct de vedere al costurilor de timp pentru interogările care returnează 50+ rânduri, acestea nu sunt acceptabile.

Este necesar sa faci o rezervare ca, firesc, daca scade undeva, trebuie sa ajunga undeva. Da, join-ul necesită mai multă memorie, deoarece ținerea întregului tabel de valori simultan și operarea cu acesta este mai costisitoare decât rularea subinterogărilor pentru fiecare rând, eliberând rapid memorie. Trebuie să analizați în mod specific cererea și să măsurați dacă utilizarea memoriei suplimentare de dragul timpului va fi critică sau nu.

Voi da exemple de analogii complete. În general, nu am întâlnit încă interogări cu un asemenea grad de complexitate care să nu poată fi extinse într-o cascadă de îmbinări. Poate dura o zi, dar totul poate fi dezvăluit.

Selectați * dintr-un unde a.id in (selectați id-ul din b) selectați * dintr-un unde există (selectați top 1 1 din b unde b.id = a.id) selectați * dintr-un join b pe a.id = b. id selectează * dintr-un unde a.id not in (selectează id-ul din b) selectează * dintr-un unde nu există (selectează top 1 1 din b unde b.id = a.id) selectează * dintr-o alăturare stângă b pe a. id = b.id unde b.id este nul

Repet - optimizatorul MSSQL optimizează aceste exemple pentru performanță maximăși nu vor exista niciodată oameni proști cu cereri atât de simple.

Să luăm acum în considerare un exemplu de interogare reală care a trebuit rescrisă pentru că pur și simplu s-a înghețat pe unele mostre (structura este foarte simplificată și conceptele au fost înlocuite, nu trebuie să vă temeți de o structură neoptimă a bazei de date ).

Trebuie să scoateți toate „produsele” duplicate în conturi diferite, concentrându-vă pe parametrii produsului, grupului său și grupului părinte, dacă există unul.

Selectați d.PRODUCT_ID din PRODUCT s, PRODUCT_GROUP sg left join M_PG_DEPENDENCY sd on (sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID), PRODUCT d, PRODUCT_GROUP dg left join M_PG_DEPENDENCY dd on (dg.PRODUCT_GROUP_DEPENDENCY dd unde PRODUCT_GROUP_ID DEPENDENCY dd. . PRODUCT_GROUP_ID și d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID și sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC și sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME și s.PRODUCT_NAME=d.PRODUCT_N AME și s.PRODUCT_TYPE=d.PRODUCT_GROUP_PERSPEC și sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME și s.PRODUCT_NAME=d.PRODUCT_N AME și s.PRODUCT_TYPE=d. și s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT și dg.PRODUCT_GROUP_IS_TMPL=0 și ((sd.M_PG_DEPENDENCY_CHILD_ID este null și dd.M_PG_DEPENDENCY_CHILD_ID este nul) sau există (selectați 1 din PRODUCT_GROUP sg g1.PRODUCT_GROUP_ID și dd .M_PG_DEPENDENCY_PARENT_ID = dg1.PRODUCT_GROUP_ID și sg1.PRODUCT_GROUP_PERSPEC=dg1.PRODUCT_GROUP_PERSPEC și sg1.PRODUCT_GROUP_NAME=dg1.PRODUCT_GROUP_NAME și))

Deci acesta este cazul când optimizatorul a renunțat. Și pentru fiecare linie a fost executat un grea exist, care a ucis baza de date.

Selectați d.PRODUCT_ID din PRODUCT s alăturați-vă la PRODUCT d pe s.PRODUCT_TYPE=d.PRODUCT_TYPE și s.PRODUCT_NAME=d.PRODUCT_NAME și s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE și s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT alăturați-vă la PRODUCT_GRO UP sg PRODUCT_GROUP on_IDs. = sg.PRODUCT_GROUP_ID alăturați-vă PRODUCT_GROUP dg pe d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID și sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME și sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC stânga se alătură M_PG_DEP ENDENCY sd pe stânga PRODUCT_ID_DEN_MPG sg _DEPENDENȚĂ dd pe dg .PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID stânga se alătură PRODUCT_GROUP sgp pe sgp.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_PARENT_ID stânga se alătură PRODUCT_GROUP dgp pe dgp.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_NAME și PRODUCT_NAME_PROD_NAME.UPSCTg și isnull(sgp.PRODUCT_GROUP_IS_TMPL, 0) = isnull (dgp. PRODUCT_GROUP_IS_TMPL, 0) unde (sd.M_PG_DEPENDENCY_CHILD_ID este nul și dd.M_PG_DEPENDENCY_CHILD_ID este nul) sau (sgp.PRODUCT_GROUP_NAME nu este nul și dgp.PRODUCT_GROUP_NAME nu este nul) mergeți

După aceste transformări, performanța vizualizării a crescut exponențial odată cu numărul de produse găsite. Sau mai bine zis, timpul de căutare a rămas practic independent de numărul de potriviri și a fost întotdeauna foarte mic. Cum ar trebui să fie.

Acesta este un exemplu clar al modului în care încrederea în optimizatorul MSSQL poate juca o glumă crudă. Nu aveți încredere în el, nu fi leneș, alăturați-vă manual, gândiți-vă de fiecare dată ce este mai bun într-o anumită situație - există, sau alăturați-vă.

Predicatul limbajului SQL EXISTS realizează o sarcină logică. ÎN interogări SQL acest predicat este folosit în expresiile formei

EXISTĂ (SELECT * FROM TABLE_NAME ...).

Această expresie returnează true când interogarea găsește unul sau mai multe rânduri care se potrivesc cu condiția și false atunci când nu sunt găsite rânduri.

Pentru NU EXISTĂ e invers. Expresie

NU EXISTĂ (SELECT * FROM TABLE_NAME ...)

returnează true când nu sunt găsite rânduri în interogare și false când este găsit cel puțin un rând.

Cele mai simple interogări cu predicatul SQL EXISTĂ

În exemple, lucrăm cu baza de date a bibliotecii și cu tabelele sale „Carte în uz” (BOOKINUSE) și „Utilizator” (USER). Pentru moment, avem nevoie doar de tabelul „Carte în uz” (BOOKINUSE).

Autor	Titlu	Pubyear	Nr_inv	ID-ul de utilizator
Tolstoi	Razboi si pace	2005	28	65
Cehov	Livada de cireși	2000	17	31
Cehov	Povești alese	2011	19	120
Cehov	Livada de cireși	1991	5	65
Ilf și Petrov	Cele douăsprezece Scaune	1985	3	31
Maiakovski	Poezii	1983	2	120
Păstârnac	Doctor Jivago	2006	69	120
Tolstoi	duminică	2006	77	47
Tolstoi	Anna Karenina	1989	7	205
Pușkin	fiica căpitanului	2004	25	47
Gogol	Joacă	2007	81	47
Cehov	Povești alese	1987	4	205
Păstârnac	Favorite	2000	137	18

Exemplul 1. Determinați ID-urile utilizatorilor cărora li s-au dat cărțile lui Tolstoi și cărora li s-au dat și cărțile lui Cehov. Interogarea exterioară selectează date despre utilizatorii cărora li s-au dat cărțile lui Tolstoi, iar predicatul EXISTĂ specifică o condiție suplimentară care este verificată în interogarea interioară - utilizatorii cărora li s-au dat cărțile lui Cehov. O condiție suplimentară în cererea internă este ca ID-urile utilizatorului din cererile externe și interne să se potrivească: User_ID=tols_user.user_id. Solicitarea va fi după cum urmează:

Această interogare va returna următorul rezultat:

Diferențele dintre predicatele EXISTS și IN

La prima vedere la interogările cu predicatul EXISTS, puteți avea impresia că este identic predicat IN. Este gresit. Deși sunt foarte asemănătoare. Predicatul IN caută valori din intervalul specificat în argumentul său, iar dacă există astfel de valori, atunci toate rândurile corespunzătoare acestui interval sunt selectate. Rezultatul predicatului EXISTĂ este un răspuns „da” sau „nu” la întrebarea dacă există valori care corespund cu cele specificate în argument. În plus, predicatul IN este precedat de numele coloanei prin care se caută rânduri care se potrivesc cu valorile din interval. Să ne uităm la un exemplu care arată diferența dintre predicatul EXISTS și predicatul IN și problema rezolvată folosind predicatul IN.

Exemplul 4. Determinați ID-urile utilizatorilor cărora li s-au eliberat cărți de către autori ale căror cărți au fost eliberate utilizatorului cu ID 31. Solicitarea va fi după cum urmează:

ID-ul de utilizator

120

205

O interogare internă (după IN) selectează autori: Cehov; Ilf și Petrov. Interogarea externă selectează toți utilizatorii cărora li s-au eliberat cărți de către acești autori. Vedem că, spre deosebire de predicatul EXISTĂ, predicatul IN este precedat de numele coloanei, în acest caz - Autor.

Interogări cu predicat EXISTS și condiții suplimentare

Dacă, pe lângă predicatul EXISTS din interogare, aplicați cel puțin o condiție suplimentară, de exemplu, specificată folosind funcții agregate, atunci astfel de interogări pot servi pentru o analiză simplă a datelor. Să demonstrăm acest lucru cu următorul exemplu.

Exemplul 5. Determinați ID-urile utilizatorilor cărora li s-a eliberat cel puțin o carte de către Pasternak și cărora le-au primit mai mult de 2 cărți. Scriem următoarea interogare, în care prima condiție este specificată de predicatul EXISTS cu o interogare imbricată, iar a doua condiție cu operatorul HAVING trebuie să urmeze întotdeauna interogarea imbricată:

Rezultatul cererii:

ID-ul de utilizator

120

După cum se poate observa din tabelul BOOKINUSE, cartea lui Pasternak a fost eliberată și utilizatorului cu ID 18, dar i s-a dat o singură carte și nu este inclusă în eșantion. Dacă aplicați din nou funcția COUNT la o interogare similară, dar de data aceasta pentru a număra rândurile selectate (exersați-l singur), puteți obține informații despre câți utilizatori care citesc cărțile lui Pasternak citesc și cărți ale altor autori. Aceasta este deja din domeniul analizei datelor.

Interogări cu predicat EXISTS pe două tabele

Interogările cu predicatul EXISTS pot prelua date din mai multe tabele. Multe probleme pot fi rezolvate cu același rezultat folosind operator JOIN, dar în unele cazuri utilizarea EXISTS vă permite să creați o interogare mai puțin greoaie. Este de preferat să folosiți EXISTS în cazurile în care tabelul rezultat va conține coloane dintr-un singur tabel.

În exemplul următor, din aceeași bază de date, pe lângă tabelul BOOKINUSE, veți avea nevoie și de un tabel USER.

Rezultatul interogării va fi următorul tabel:

Autor

Cehov

Maiakovski

Păstârnac

Ca și în cazul utilizării operatorului JOIN, în cazurile în care există mai mult de un tabel, ar trebui să utilizați aliasuri de tabel pentru a verifica dacă valorile cheilor care conectează tabelele se potrivesc. În exemplul nostru, aliasurile de tabel sunt bk și us, iar cheia care conectează tabelele este User_ID.

EXISTĂ predicat în îmbinări a mai mult de două tabele

Acum vom vedea mai detaliat de ce este de preferat să folosiți EXISTS în cazurile în care tabelul rezultat va conține coloane dintr-un singur tabel.

Lucrăm cu baza de date „Real Estate”. Tabelul Deal conține date despre oferte. Pentru sarcinile noastre, coloana Tip cu date despre tipul tranzacției - vânzare sau închiriere - va fi importantă în acest tabel. Tabelul Object conține date despre obiecte. În acest tabel, vom avea nevoie de valorile coloanelor Camere (număr de camere) și LogBalc, care conține date despre prezența unei logie sau balcon în format boolean: 1 (da) sau 0 (nu). Tabelele Client, Manager și Proprietar conțin date despre clienți, managerii companiei și, respectiv, proprietarii de proprietăți. În aceste tabele, FName și LName sunt numele și, respectiv, de familie.

Exemplul 7. Identificați clienții care au achiziționat sau închiriat proprietăți care nu au logie sau balcon. Scriem următoarea interogare, în care predicatul EXISTS specifică un acces la rezultatul unirii a două tabele:

Deoarece coloanele sunt selectate din tabelul Client folosind operatorul asterisc, vor fi afișate toate coloanele din acest tabel, care vor avea atâtea rânduri câte clienți se potrivesc cu condiția specificată de predicatul EXISTS. Nu trebuie să scoatem nicio coloană din tabelele a căror unire este accesată de subinterogare. Prin urmare, pentru a economisi timpul mașinii, este preluată doar o coloană. Pentru a face acest lucru, se scrie o unitate după cuvântul SELECT. Aceeași tehnică este utilizată în interogările din exemplele următoare.

Scrieți singur o interogare SQL cu predicatul EXISTS și apoi uitați-vă la soluție

Continuăm să scriem interogări SQL împreună cu predicatul EXISTS

Exemplul 9. Determinați proprietarii obiectelor care au fost închiriate. Scriem următoarea interogare, în care predicatul EXISTS specifică și un acces la rezultatul unirii a două tabele:

Ca și în exemplul anterior, toate câmpurile din tabelul accesate de interogarea externă vor fi returnate.

Exemplul 10. Determinați numărul de proprietari ale căror proprietăți au fost gestionate de managerul Savelyev. Scriem o interogare în care interogarea exterioară accesează o îmbinare a trei tabele, iar predicatul EXISTS specifică accesul la un singur tabel:

Toate interogările sunt verificate cu o bază de date existentă. Utilizare cu succes!

Baze de date relaţionale şi Limbajul SQL

Academia de Stat de Economie și Management din Novosibirsk

PRACTICUM DE LABORATOR PE DISCIPLINA

"BAZĂ DE DATE"

Lucrare de laborator nr 7

„Limbajul bazei de date SQL: comenzi de manipulare a datelor»

NOVOSIBIRSK 2000

SQL este o abreviere pentru Structured Query Language. Din denumirea limbii reiese clar că scopul său principal este generarea de interogări pentru a obține informații dintr-o bază de date. Comenzile pentru preluarea datelor formează baza limbajului de manipulare a datelor DML - o parte integrantă a limbajului SQL. Cu toate acestea, DML constă în mai mult decât simple comenzi pentru preluarea datelor dintr-o bază de date. Există, de asemenea, comenzi pentru modificarea datelor, gestionarea datelor și altele.

Lucrarea de laborator examinează instrumentele de bază ale limbajului DML. În curs munca de laborator ne vom menține la standardul SQL2.

Datorită faptului că SQL este un limbaj mare, vom lua în considerare doar comenzile de bază. Diverse instrumente SQL specifice sunt acoperite în laboratoarele ulterioare.

Pentru a efectua lucrări de laborator, sunt necesare cunoștințe despre elementele de bază ale modelului de date relaționale, elementele de bază ale algebrei relaționale și calculului relațional și principiile de lucru cu MS SQL Server SGBD.

Ca urmare a finalizării lucrărilor de laborator, veți stăpâni metodele de manipulare a datelor folosind comenzile limbajului SQL, luați în considerare dialectul limbajului implementat în SGBD-ul MS SQL Server.

INTRODUCERE

SQL conține o gamă largă de capabilități de manipulare a datelor, atât pentru crearea de interogări, cât și pentru actualizarea bazei de date. Aceste capabilități se bazează doar pe structura logică a bazei de date, nu pe structura sa fizică, care este în concordanță cu cerințele modelului relațional.

Structura originală a sintaxei SQL a fost (sau cel puțin părea a fi) bazată pe calculul relațional al lui Codd. Singura operație acceptată în algebra relațională a fost uniunea.

În plus față de sintaxa de calcul relațional dezvoltată în standardul anterior, SQL2 implementează direct operațiunile unire, intersecție, diferență și unire. Operațiunile de selectare, proiect și produs au fost (și continuă să fie) susținute aproape direct, în timp ce operațiunile de divizare și de atribuire sunt susținute într-o formă mai greoaie.

Vom descrie mai întâi limbajul de interogare SQL și apoi operațiunile sale de introducere și modificare a datelor. Operațiunile de modificare a datelor vor fi descrise în ultimul rând, deoarece structura lor se bazează într-o anumită măsură pe structura limbajului de interogare.

Interogări simple

Pentru noi simpla cerere va exista o interogare care accesează un singur tabel din baza de date. Interogările simple ne vor ajuta să ilustrăm structura de bază a SQL.

Cerere simplă. O interogare care accesează doar un tabel al bazei de date.

Cerere: Cine lucrează ca tencuitori?

WHERE SKILL_TYPE = „Tepcuitor”

Rezultat:

G. Rickover

Această interogare ilustrează cele trei cele mai comune fraze SQL: SELECT, FROM și WHERE. Deși în exemplul nostru le-am plasat pe linii diferite, toate pot apărea pe aceeași linie. Ele pot fi, de asemenea, indentate diferit, iar cuvintele din fraze pot fi separate printr-un număr arbitrar de spații. Să ne uităm la caracteristicile fiecărei fraze.

Selectați. Clauza SELECT listează coloanele care ar trebui să apară în tabelul rezultat. Acestea sunt întotdeauna coloane ale unui tabel relațional. În exemplul nostru, tabelul rezultat este format dintr-o coloană (NUME), dar în general poate conține mai multe coloane; poate conține și valori calculate sau constante. Vom da exemple pentru fiecare dintre aceste opțiuni. Dacă tabelul rezultat trebuie să conțină mai multe coloane, atunci toate coloanele necesare sunt listate după comenzi SELECT separate prin virgule. De exemplu, expresia SELECT WORKER_ID, NAME va avea ca rezultat un tabel format din coloanele WORKER_ID și NAME.

clauza SELECT. Specifică coloanele tabelului rezultat.

Din. Clauza FROM specifică unul sau mai multe tabele care sunt accesate de interogare. Toate coloanele listate în clauzele SELECT și WHERE trebuie să existe într-unul dintre tabelele listate în comanda FROM. În SQL2, aceste tabele pot fi definite direct în schemă ca tabele de bază sau vizualizări de date sau pot fi ele însele tabele fără nume rezultate din interogări SQL. În acest din urmă caz, interogarea este dată în mod explicit în comanda FROM.

Expresia FROM. Specifică tabelele existente care sunt accesate de interogare.

Unde. Clauza WHERE conține o condiție. pe baza cărora sunt selectate rândurile tabelului (tabelelor). În exemplul nostru, condiția este ca coloana SKILL_TYPE să conțină constanta „Plasterer” închisă în apostrofe, așa cum se face întotdeauna cu constantele text în SQL. Clauza WHERE este cea mai volatilă comandă SQL; poate conține multe condiții diferite. O mare parte din discuția noastră va fi dedicată ilustrării diferitelor constructe permise în comanda WHERE.

clauza WHERE. Specifică condiția pe baza cărora sunt selectate rândurile din tabelele specificate.

Interogarea SQL de mai sus este procesată de sistem în următoarea ordine: FROM, WHERE, SELECT. Adică, rândurile tabelului specificate în comanda FROM sunt plasate în zona de lucru pentru procesare. Clauza WHERE este apoi aplicată fiecărui rând în secvență. Toate rândurile care nu îndeplinesc condiția WHERE sunt excluse din considerare. Apoi acele rânduri care îndeplinesc condiția WHERE sunt procesate de instrucțiunea SELECT. În exemplul nostru, NAME este selectat din fiecare astfel de rând și toate valorile selectate sunt afișate ca rezultate ale interogării.

Cerere: Furnizați toate informațiile despre clădirile de birouri.

WHERE TYPE = „Birou”

Rezultat:

BLDG IDADDRESSTYPEQLTY LEVELSTATUS

312 Elm St., 123 Office 2 2

210 Berezovaya st. 1011 Office Z 1

111 Osinovaya st. 1213 Office 4 1

Un asterisc (*) într-o comandă SELECT înseamnă „întregul rând”. Aceasta este o scurtătură convenabilă pe care o vom folosi des.

Cerere: Care este salariul săptămânal pentru fiecare electrician?

SELECTAȚI NUMELE, „Salariu săptămânal = ", 40 * HRLY_RATE

WHERE SKILL_TYPE = „Electrician”

Rezultat:

M. Faraday Salariu saptamanal = 500,00

H.Columbus Salariu saptamanal = 620,00

Această interogare ilustrează utilizarea atât a constantelor de caractere (în exemplul nostru "Salariul săptămânal = ") cât și a calculelor în comanda SELECT În cadrul instrucțiunii SELECT, puteți efectua calcule care folosesc coloane numerice și constante numerice, precum și operatori aritmetici standard (. +, -, *, /), grupate după cum este necesar folosind paranteze. Am inclus și un nou comanda COMANDA BY, care sortează rezultatul interogării în ordine alfanumerică crescătoare după coloana specificată. Dacă doriți să ordonați rezultatele în ordine descrescătoare, trebuie să adăugați DESC la comandă. Clauza ORDER BY poate sorta rezultatele după mai multe coloane, unele în ordine crescătoare, iar altele în ordine descrescătoare. Coloana cheii primare a sortării este listată mai întâi.

Caracter constant. O constantă formată din litere, cifre și caractere „speciale”.

Cerere: Cine are un tarif orar de la 10 la 12 USD?

UNDE HRLY_RATE > = 10 ȘI HRLY_RATE< - 12

Rezultat:

ID LUCRĂTOR NAME HRLY_RATE SKILL_TYPE SUPV_ID

Această interogare ilustrează unele dintre caracteristicile suplimentare ale instrucțiunii WHERE: operatori de comparație și operatorul boolean AND. Șase operatori de comparare (=,<>(nu este egal),<, >, <=, >=). Operatorii booleeni AND, OR și NOT pot fi utilizați pentru a crea condiții compuse sau pentru a anula o condiție. Parantezele pot fi folosite pentru a grupa condiții, așa cum este obișnuit în limbajele de programare.

Operatori de comparație =,<>, <, >, <=, >=.

Operații booleeneȘI (ȘI), SAU (SAU) și NU (EL) .

De asemenea, puteți utiliza operatorul BETWEEN (între) pentru a formula această interogare:

UNDE HRLY_RATE ÎNTRE 10 ȘI 12

BETWEEN poate fi folosit pentru a compara o cantitate cu alte două mărimi, dintre care prima este mai mică decât a doua, dacă cantitatea comparată poate fi egală cu fiecare dintre aceste mărimi sau cu orice valoare intermediară.

Cerere: Listați tencuitori, acoperișori și electricieni.

WHERE SKILL_TYPE IN ("Tepcuitor", "Acoperiș", "Electrician")

Rezultat:

WORKER_ID NUME HRLY_RATE SKILL_TYPE SUPV_ID

1412 K.Nemo 13,75 Tencuitor 1520

2920 R. Garrett 10.00 Roofer 2920

1520 G. Rickover 11,75 Tencuitor 1520

Această interogare explică utilizarea operatorului de comparație IN (B). Clauza WHERE este considerată adevărată dacă tipul de specialitate al rândului este situat în interiorul setului specificat în paranteze, adică dacă tipul de specialitate este tencuitor, acoperiș sau electrician. Vom vedea din nou operatorul IN în subinterogări.

Să presupunem că nu ne putem aminti exact ortografia specialității noastre: „electrician” sau „inginer electronic” sau altceva. Caracterele wildcard, care înlocuiesc șirurile de caractere nedefinite, facilitează găsirea de ortografii inexacte într-o interogare.

Simboluri de tipar. Caractere care înlocuiesc șiruri de caractere nedefinite.

Cerere: Listați angajații al căror tip de specialitate începe cu „Elek”.

WHERE SKILL_TYPE LIKE ("Elect%)

Rezultat:

ID-UL LUCRĂTORULUI NUME HRLY_RATE SKILL_TYPE SUPV_ID

1235 M. Faraday 12.50 Electrician 1311

1311 H. Columb 15.50 Electric 1311

SQL are două caractere wildcard: % (procent) și _ (subliniere). Literul de subliniere înlocuiește exact un caracter nedefinit. Procentul înlocuiește un număr arbitrar de caractere, începând cu zero. Când sunt utilizate caractere metalice, este necesar un operator LIKE pentru a compara variabilele caracter cu constantele. Alte exemple:

NUME LIKE „__Columbus”

NUME LIKE „__K%”

Condiția din primul exemplu este adevărată dacă NUME este format din două caractere urmate de „Columbus”. În tabelul WORKER, toate numele încep cu prima inițială și punct. Astfel, folosind această condiție noi. Să găsim toți angajații cu numele de familie „Columbus”. Condiția celui de-al doilea exemplu ne permite să găsim toți angajații ale căror nume de familie încep cu litera „K”.

Cerere: Găsiți toate locurile de muncă care încep în următoarele două săptămâni.

UNDE START_DATE ÎNTRE CURRENT_DATE ȘI

Rezultat:(Să presupunem că data curentă este DATA CURENT = 10.10)

WORKER_ID BLDG_ID START_DATE NUM_ZILE

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

Această interogare ilustrează utilizarea operatorului BETWEEN cu valori de dată și interval. CURRENT_DATE este o funcție care returnează întotdeauna data de astăzi. Expresie

CURRENT_DATE + INTERVAL „14” ZI

adaugă o perioadă de două săptămâni la data curentă. Astfel, ASSIGNMENT este selectat (presupunând că astăzi este 10/10) dacă valoarea sa din coloana START_DATE este între 10/10 și 10/24. Din aceasta putem vedea că putem adăuga valori de interval la câmpurile de date. Mai mult, putem înmulți valorile intervalelor cu valori întregi. De exemplu, să presupunem că vrem să aflăm ce număr va fi într-un anumit număr de săptămâni (notat cu variabila NUM_WEEKS). O putem face astfel:

CURRENT_DATE + INTERVAL „7” ZI * NUM_WEEKS

2. Interogări cu mai multe tabele

Capacitatea de a lega elementele de date peste granițele unui singur tabel este importantă pentru orice limbaj de bază de date. În algebra relațională, această funcție este realizată prin operația de îmbinare. Deși o mare parte din SQL se bazează direct pe calculul relațional, SQL conectează datele din tabele diferite într-un mod similar cu operația de unire a algebrei relaționale. Acum vom arăta cum se face acest lucru. Luați în considerare cererea:

Cerere:

Datele necesare pentru răspuns sunt în două tabele: LUCRĂTOR și MISSION. Soluția SQL necesită listarea ambelor tabele în comanda FROM și specificarea unui tip special de clauză WHERE:

SELECTAȚI SKILL_TYPE

DE LA MUNCITOR, MISSION

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID

ȘI BLDG_ID = 435

Ce se petrece aici? Trebuie să luăm în considerare două etape în modul în care sistemul procesează această solicitare.

1. Ca de obicei, clauza FROM este procesată mai întâi. Totuși, în acest caz, deoarece comanda specifică două tabele, sistemul creează un produs cartezian al rândurilor acestor tabele. Aceasta înseamnă că este creat (logic) un tabel mare format din coloane din ambele tabele, fiecare rând al unui tabel asociat cu fiecare rând al celuilalt tabel. În exemplul nostru, deoarece tabelul WORKER are cinci coloane și tabelul ASSIGNMENT are patru coloane, produsul cartezian produs de comanda FROM va avea nouă coloane. Numărul total de rânduri al produsului cartezian este egal cu m * n, unde m este numărul de rânduri din tabelul WORKER; iar n este numărul de rânduri din tabelul ASSIGNMENT. Deoarece tabelul WORKER are 7 rânduri și tabelul ASSIGNMENT are 19 rânduri, produsul cartezian va conține 7x19 sau 133 de rânduri. Dacă comanda FROM listează mai mult de două tabele, este creat un produs cartezian al tuturor tabelelor specificate în comandă.

produs cartezian. Rezultatul unirii fiecărui rând dintr-un tabel cu fiecare rândul altui tabel.

2. După crearea tabelului relațional gigant, sistemul folosește comanda WHERE ca înainte. Fiecare rând al tabelului creat de comanda FROM. este verificată pentru a vedea dacă este îndeplinită condiția WHERE. Rândurile care nu îndeplinesc condiția sunt excluse din considerare. Clauza SELECT este apoi aplicată la rândurile rămase.

Clauza WHERE din interogarea noastră conține două condiții:

1. MUNCITOR. WORKER_ID = ASSIGNMENT.WORKER_ID

2. BLDG_ID = 435

Prima dintre aceste condiții este condiția de alăturare. Rețineți că, deoarece ambele tabele WORKER și ASSIGNMENT conțin o coloană numită WORKER_ID, produsul lor cartezian va conține două coloane cu acest nume. Pentru a le diferenția, precedăm numele coloanei cu numele tabelului sursă, despărțit de un punct.

Prima condiție înseamnă că în orice rând selectat, valoarea coloanei WORKER_ID din tabelul WORKER trebuie să se potrivească cu valoarea coloanei WORKER_ID din tabelul ASSIGNMENT. În realitate, unim două tabele prin WORKER_ID. Toate rândurile în care valorile acestor două coloane nu sunt egale sunt excluse din tabelul cu produse. Exact același lucru se întâmplă atunci când se efectuează operația de unire naturală a algebrei relaționale. (Cu toate acestea, există încă o diferență față de o îmbinare naturală: SQL nu elimină automat coloana WORKER_ID suplimentară). Îmbinarea completă a acestor două tabele cu condiția suplimentară BLDG_ID = 435 este prezentată în Fig. 1. Utilizarea comenzii SELECT va da în cele din urmă următorul rezultat al interogării:

TIP DE ABILITATE

Tencuitor

Acoperișor

Electrician

Orez. 1. Alăturarea tabelelor LUCRĂTOR și ATRIBUIRE

Acum vom arăta cum să asociați un tabel cu el însuși în SQL.

Cerere: Listați angajații, indicând numele managerilor lor.

SELECTAȚI A.WORKER_NAME, B.WORKER_NAME

DE LA LUCRĂTORUL A, MUNCITORUL B

UNDE B.WORKER_ID = A.SUPV_ID

Clauza FROM din acest exemplu creează două „copii” ale tabelului WORKER, dându-le aliasurile A și B. Un alias este un nume alternativ dat tabelului. Apoi, copiile A și B ale tabelului WORKER sunt alăturate de comanda WHERE bazată pe condiția de egalitate a WORKER_ID în B și SUPV_ID în A. Astfel, fiecare rând din A este alăturat rândului B, care conține informații despre managerul rândului A. (Fig. 2).

Orez. 2. Unirea a două exemplare ale tabelului LUCRĂTOR

Selectând două nume de angajați din fiecare linie, obținem lista necesară:

A.NAMEB.NAME

M. Faraday H. Columb

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

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

Poreclă. Un nume alternativ dat tabelului.

A.WORKER_NAME reprezintă lucrătorul, iar B.WORKER_NAME reprezintă managerul. Vă rugăm să rețineți că unii lucrători sunt proprii manageri, ceea ce decurge din egalitatea WORKER_ID - SUPV_ID din rândurile lor.

În SQL, puteți lega mai mult de două tabele simultan:

Cerere

SELECTAȚI WORKER_NAME

DE LA MUNCITOR, MESARE, CONSTRUIRE

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID ȘI ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID ȘI

TYPE = „Birou”

Rezultat:

M. Faraday

G. Rickover

J.Barrister

Rețineți că dacă un nume de coloană (de exemplu, WORKER_ID sau BLDG_ID) apare în mai mult de un tabel, atunci pentru a evita ambiguitatea trebuie să punem înainte numele coloanei cu numele tabelului original. Dar dacă numele coloanei apare într-un singur tabel, cum ar fi TYPE în exemplul nostru, atunci nu există nicio ambiguitate, astfel încât numele tabelului nu trebuie specificat.

Comenzile SQL din această interogare creează un tabel din trei tabele de baze de date relaționale. Primele două tabele sunt unite prin WORKER_ID, după care al treilea tabel este alăturat prin BLDG_ID tabelului rezultat. Condiție

TYPE = „Birou”

Clauza WHERE face ca toate rândurile să fie excluse, cu excepția celor pentru clădirile de birouri. Aceasta îndeplinește cerințele cererii.

3. Subinterogări

Subinterogare. Interogare în cadrul unei interogări

O subinterogare poate fi plasată în clauza WHERE a unei interogări, extinzând astfel capacitățile clauzei WHERE. Să ne uităm la un exemplu.

Cerere: Care sunt specialitățile muncitorilor încadrați în clădirea 435?

SELECTAȚI SKTLL_TYPE

FROM WORKER WHERE WORKER_ID IN

(SELECTARE WORKER_ID

UNDE BLDG_ID = 435)

Subinterogare în acest exemplu

(SELECTARE WORKER_ID

UNDE BLDG_ID = 435)

O interogare care conține o subinterogare este apelată cerere externă sau cererea principală. Subinterogarea are ca rezultat crearea următorului set de ID-uri de angajat:

ID LUCRĂTOR

Cerere externă. Interogarea principală, care conține toate subinterogările.

Acest set de ID-uri ia apoi locul unei subinterogări în interogarea exterioară. Din acest moment, interogarea exterioară este executată folosind setul creat de subinterogare. Interogarea exterioară procesează fiecare rând din tabelul WORKER conform clauzei WHERE. Dacă WORKER_ID al unui rând se află în setul (IN) creat de subinterogare, atunci SKILL_TYPE al rândului este selectat și afișat în tabelul rezultat:

TIP DE ABILITATE

Tencuitor

Acoperișor

Electrician

Este foarte important ca clauza SELECT a subinterogării să conţină WORKER_ID şi numai WORKER_ID. În caz contrar, clauza WHERE a interogării externe, ceea ce înseamnă că WORKER_ID se află în setul de ID-uri de lucrător, nu ar avea nicio semnificație.

Rețineți că o subinterogare poate fi executată în mod logic înainte ca cel puțin un rând să fie luat în considerare de interogarea principală. Într-un sens, o subinterogare este independentă de interogarea principală. Poate fi executat ca o interogare completă. Spunem că o astfel de subinterogare nu este corelată cu interogarea principală. După cum vom vedea în curând, subinterogările pot fi corelate.

Subinterogare necorelată. O subinterogare a cărei valoare este independentă de orice interogare exterioară.

Iată un exemplu de subinterogare într-o subinterogare.

Cerere: Listați angajații alocați clădirilor de birouri.

Din nou ne uităm la interogarea cu care am examinat conexiunea.

SELECTAȚI WORKER_MAME

UNDE ÎN WORKER_ID

(SELECTARE WORKER_ID

UNDE ÎN BLDG_ID

WHERE TYPE = „Birou”))

Rezultat:

M. Faraday

G. Rickover

J.Barrister

Rețineți că nu este nevoie să prefixăm numele coloanelor cu nume de tabel oriunde, deoarece fiecare subinterogare procesează unul și doar un tabel, astfel încât nu pot apărea ambiguități.

Execuția interogării are loc într-o ordine din interior spre exterior. Adică, interogarea cea mai interioară (sau „cea mai de jos”) este executată mai întâi, apoi este executată subinterogarea care o conține și apoi interogarea exterioară.

Subinterogări corelate. Toate subinterogările discutate mai sus au fost independente de interogările principale în care au fost utilizate. Prin independent, înțelegem că subinterogările pot fi executate pe cont propriu ca interogări complete. Acum trecem la considerarea unei clase de subinterogări ale căror rezultate de execuție pot depinde de rândul luat în considerare de interogarea principală. Astfel de subinterogări sunt numite subinterogări corelate.

Subinterogare corelată. O subinterogare al cărei rezultat depinde de rândul luat în considerare de interogarea principală.

Cerere: Enumerați angajații ale căror tarife orare sunt mai mari decât cele ale managerilor lor.

SELECTAȚI WORKER_NAME

UNDE A.HRLY_RATE >

(SELECTARE B.HRLY_RATE

WHERE B.WORKER_ID = A.SUPV_ID)

Rezultat:

Pașii logici pentru executarea acestei solicitări sunt:

1. Sistemul creează două copii ale tabelului LUCRĂTOR: copia A și copia B. După modul în care le-am definit, A se referă la angajat, B se referă la manager.

2. Sistemul ia în considerare apoi fiecare rând A. Un rând dat este selectat dacă îndeplinește condiția WHERE. Această condiție înseamnă că un rând va fi selectat dacă valoarea sa HRLY_RATE este mai mare decât HRLY_RATE generată de subinterogare.

3. Subinterogarea selectează valoarea HRLY_RATE din rândul B, al cărei WORKER_ID este egal cu SUPV_ID al rândului A, în acest moment luate în considerare de cererea principală. Acesta este HRLY_RATE al managerului.

Rețineți că, deoarece A.HRLY_RATE poate fi comparat doar cu o singură valoare, subinterogarea trebuie să returneze o singură valoare. Această valoare se modifică în funcție de rândul A luat în considerare. Astfel, subinterogarea este corelată cu interogarea principală. Vom vedea alte exemple de subinterogări corelate mai târziu când studiem funcțiile încorporate.

operatori EXISTS și NOT EXISTS

Să presupunem că vrem să identificăm lucrătorii care nu sunt desemnați să lucreze într-o anumită clădire. La suprafață, se pare că o astfel de cerere poate fi satisfăcută cu ușurință prin simpla negare a versiunii afirmative a cererii. Să presupunem, de exemplu, că suntem interesați de o clădire cu BLDG_ID 435. Luați în considerare cererea:

SELECTAȚI WORKER_ID

UNDE BLDG_ID NU 435

Din păcate, aceasta este o formulare incorectă a soluției. Solicitarea ne va oferi pur și simplu actele de identitate ale lucrătorilor care lucrează în alte clădiri. Evident, unele dintre ele pot fi atribuite și clădirii 435.

O soluție corect formulată folosește operatorul NOT EXISTS:

SELECTAȚI WORKER_ID

UNDE NU EXISTA

WHERE ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID AND

Rezultat:

WORKER_ID

Operatorii EXISTS și NOT EXISTS sunt întotdeauna plasați înaintea subinterogării. EXISTS evaluează la adevărat dacă setul generat de subinterogare nu este gol. Dacă setul generat de subinterogare este gol, atunci EXISTS ia valoarea „fals”. Operatorul NOT EXISTS, desigur, funcționează exact invers. Este adevărat dacă rezultatul subinterogării este gol, iar fals în caz contrar.

EXISTĂ operator. Returnează adevărat dacă setul de rezultate nu este gol.

operator NU EXISTĂ. Returnează adevărat dacă setul de rezultate este gol.

În acest exemplu am folosit operatorul NOT EXISTS. Subinterogarea selectează toate rândurile din tabelul ASSIGNMENT în care WORKER_ID are aceeași valoare ca și rândul considerat de interogarea principală, iar BLDG_ID este egal cu 435. Dacă acest set este gol, atunci rândul worker considerat de interogarea principală este selectat, deoarece aceasta înseamnă că Acest angajat nu lucrează în clădirea 435.

În soluția oferită de noi, am folosit o subinterogare corelată. Dacă folosim operatorul IN în loc de NOT EXISTS, ne putem descurca cu o subinterogare necorelată:

SELECTAȚI WORKER_ID

UNDE NU INTRODE WORKER_ID

(SELECTARE WORKER_ID

WHERE BLDG_ID = 435)

Această soluție este mai simplă decât soluția cu operatorul NU EXISTĂ. Apare o întrebare firească: de ce avem nevoie de EXISTĂ și NU EXISTĂ deloc? Răspunsul este că NU EXISTĂ este singura modalitate de a rezolva interogările care conțin cuvântul „fiecare” în condiție. Astfel de interogări sunt rezolvate în algebra relațională folosind operația de împărțire și în calculul relațional folosind cuantificatorul universal. Iată un exemplu de interogare cu cuvântul „fiecare” în starea sa:

Cerere: Listați angajații alocați fiecărei clădiri.

Această întrebare poate fi implementată în SQL folosind negații duble. Vom reformula interogarea pentru a include un dublu negativ:

Cerere: Listați astfel de angajați pentru care Nu există o clădire căreia nu sunt alocate.

Am evidențiat dublu negativ. Este clar că această cerere este echivalentă logic cu cea anterioară.

Acum vrem să formulăm soluția în SQL. Pentru a face soluția finală mai ușor de înțeles, oferim mai întâi o soluție unei probleme preliminare: problema identificării tuturor clădirilor pentru care un ipotetic muncitor, „1234” Nu numit.

(I) SELECTAȚI BLDG_ID

UNDE NU EXISTA

ASSIGNMENT.WORKER_ID = 1234)

Am marcat această interogare (I) pentru că ne vom referi la ea mai târziu. Dacă nu există nicio clădire care să satisfacă această solicitare, atunci lucrătorul 1234 este repartizat fiecărei clădiri și, prin urmare, îndeplinește condițiile cererii inițiale. Pentru a obține o soluție la interogarea inițială, trebuie să generalizăm interogarea (I) de la un anumit lucrător 1234 la variabila WORKER_ID și să transformăm această interogare modificată într-o subinterogare a interogării mai mari. Iată soluția:

(II) SELECTARE WORKER_ID

UNDE NU EXISTA

WHERE ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID AND

ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID)

Rezultat:

ID LUCRĂTOR

Rețineți că subinterogarea care începe pe a patra linie a interogării (II) este identică cu interogarea (I), cu „1234” înlocuit cu WORKER.WORKER_ID. Interogarea (II) poate fi citită după cum urmează:

Selectați WORKER_ID din WORKER dacă nu există nicio clădire căreia să nu fie atribuit WORKER_ID.

Aceasta corespunde condițiilor cererii inițiale.

Vedem că operatorul NOT EXISTS poate fi folosit pentru a formula acele interogări care au necesitat o operație de divizare în algebra relațională și un cuantificator universal în calculul relațional. Din punct de vedere al ușurinței de utilizare, operatorul NOT EXISTS nu oferă niciun beneficiu deosebit, ceea ce înseamnă că interogările SQL care folosesc NOT EXISTS de două ori nu sunt mai ușor de înțeles decât soluțiile de algebră relațională cu diviziune sau soluții de calcul relațional cu cuantificatori universali. Va fi nevoie de mai multe cercetări pentru a crea constructe de limbaj care să permită ca astfel de interogări să fie rezolvate mai natural.

Funcții încorporate

Să luăm în considerare întrebările de acest tip:

Care sunt tarifele maxime și minime pe oră? Care este numărul mediu de zile în care angajații lucrează în clădirea 435? Care este numărul total de zile alocate lucrărilor de tencuială la clădirea 312? Câte specialități diferite există?

Răspunsul la aceste întrebări necesită funcții statistice care analizează mai multe rânduri dintr-un tabel și returnează o singură valoare. Există cinci astfel de funcții în SQL, numite funcții încorporate sau funcții set. Aceste funcții sunt SUM (suma), AVG (medie), COUNT (cantitate), MAX (maximum) și MIN (minim).

Funcție încorporată (funcție de setare). O funcție statistică care operează pe mai multe rânduri: SUM (sumă), AVG (medie), COUNT (cantitate), MAX (maxim), MIN (minim).

Cerere: Care sunt tarifele maxime și minime pe oră?

SELECTAȚI MAX(HRLY_RATE), MIN(HRLY_RATE)

Rezultat: 17.40, 8.20

Funcții MAXși MIN operează pe o singură coloană a tabelului. Ei selectează valoarea maximă sau, respectiv, minimă din această coloană. Formularea noastră de interogare nu conține o clauză WHERE. Pentru majoritatea interogărilor, acesta poate să nu fie cazul, așa cum arată următorul exemplu.

Cerere: Care este numărul mediu de zile în care angajații lucrează în clădirea 435?

SELECTAȚI AVG(NUM_DAYS)

WHERE BLDG_ID =435

Rezultat: 12.33

Cerere: Care este numărul total de zile alocate lucrărilor de tencuială la clădirea 312?

SELECTAȚI SUM(NUM_DAYS)

DIN SOCARE, MUNCITOR

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID AND

SKILL_TYPE = „Tepcuitor” ȘI

Rezultat: 27

Soluția folosește o îmbinare între tabelele ASSIGNMENT și WORKER. Acest lucru este necesar deoarece SKILL_TYPE este în tabelul WORKER și BLDG_ID este în tabelul ASSIGNMENT.

Cerere: Câte specialități diferite există?

SELECTAȚI COUNT (DISTINCT SKILL_TYPE)

Rezultat: 4

Deoarece aceeași specialitate poate apărea pe mai multe rânduri diferite, trebuie să utilizați cuvântul cheie DISTINCT în această interogare pentru a împiedica sistemul să contorizeze același tip de specialitate de mai multe ori. Operatorul DISTINCT poate fi folosit cu oricare dintre funcțiile încorporate, deși, desigur, este redundant cu funcțiile MAX și MIN.

DISTINCT. Un operator care elimină liniile duplicate.

Funcțiile SUM și AVG trebuie utilizate numai cu coloane numerice. Alte funcții pot fi utilizate atât cu date numerice, cât și cu caractere. Toate funcțiile, cu excepția COUNT, pot fi utilizate cu expresii calculate. De exemplu:

Cerere: Care este salariul mediu pe săptămână?

SELECTAȚI AVG (40 * HRLY_RATE)

Rezultat: 509.14

COUNT se poate referi la un rând întreg, mai degrabă decât la o coloană individuală :

Cerere: Câte clădiri au nivelul de calitate 3?

SELECTARE NUMĂR (*)

DE LA CONSTRUIREA UNDE

Rezultat: 3

După cum arată toate aceste exemple, dacă o comandă SELECT conține o funcție încorporată, atunci nimic altceva nu poate apărea în acea comandă SELECT. Singura excepție de la această regulă implică clauza GROUP BY, pe care o vom analiza acum.

Clauzele GROUP BY și HAVING

În management, sunt adesea necesare informații statistice despre fiecare grup din multe grupuri. De exemplu, luați în considerare următoarea interogare:

Cerere: Pentru fiecare manager, aflați tariful maxim orar în rândul subordonaților săi.

Pentru a rezolva această problemă, trebuie să împărțim lucrătorii în grupuri în funcție de managerii lor. Vom stabili apoi oferta maximă în cadrul fiecărui grup. În SQL, acest lucru se face astfel:

GROUP BY SUPV_ID

Rezultat:

SUPV_IDMAX(HRLY RATE)

Când procesează această interogare, sistemul împarte mai întâi rândurile tabelului WORKER în grupuri folosind următoarea regulă. Rândurile sunt plasate în același grup dacă și numai dacă au același SUPV_ID. Clauza SELECT este apoi aplicată fiecărui grup. Deoarece există o singură valoare SUPV_ID în acest grup, nu există nicio incertitudine SUPV_ID în grup. Pentru fiecare grup, clauza SELECT emite SUPV_ID și, de asemenea, calculează și emite valoarea MAX(HRLY_RATE). Rezultatul este prezentat mai sus.

Într-o comandă SELECT cu funcții încorporate, pot apărea numai acele coloane care sunt incluse în clauza GROUP BY. Rețineți că SUPV_ID poate fi folosit într-o comandă SELECT deoarece este inclus în clauza GROUP BY.

Clauza GROUP BY. Indică faptul că rândurile trebuie împărțite în grupuri cu valori comune ale coloanelor specificate.

Clauza GROUP BY vă permite să efectuați anumite calcule complexe. De exemplu, am putea dori să aflăm media acestor sume licitate maxime. Cu toate acestea, calculul cu funcții încorporate este limitat în sensul că nu permite ca funcțiile încorporate să fie utilizate în cadrul altor funcții încorporate. Deci o expresie ca

AVG(MAX(HRLY_RATE))

interzis. Implementarea unei astfel de solicitări va consta în două etape. Mai întâi trebuie să punem ofertele maxime într-un nou tabel, iar în al doilea pas trebuie să calculăm media lor.

Puteți utiliza clauza WHERE cu comanda GROUP BY:

Cerere: Pentru fiecare tip de clădire aflați nivel mediu calitatea între clădirile de statut 1.

SELECTARE TIP, AVG(QLTY_LEVEL)

UNDE STAREA = 1

Rezultat:

TYPEAVG(QLTY_LEVEL)

Magazin 1

Clădire de locuințe 3

Clauza WHERE este executată înaintea instrucțiunii GROUP BY. Astfel, niciun grup nu poate conține un rând care are o stare diferită de 1. Rândurile de stare 1 sunt grupate după valoarea TYPE și apoi se aplică o clauză SELECT fiecărui grup.

AVÂND frază. Impune condiții grupurilor.

Putem aplica condiții și grupurilor create prin clauza GROUP BY. Acest lucru se face folosind expresia HAVING. Să presupunem, de exemplu, că am decis să specificăm una dintre interogările anterioare:

Cerere: Pentru fiecare manager care are mai mult de un subordonat, aflați tariful orar maxim dintre subalternii săi.

Putem reflecta această condiție cu comanda HAVING corespunzătoare:

SELECTAȚI SUPV_ID, MAX(HRLY_RATE)

DIN GRUPUL DE LUCRĂTORI DIN SUPV_ID

AVÂND NUMĂRARE(*) > 1

Rezultat:

SUPV_ID MAX(HRLY_RATE)

Diferența dintre clauzele WHERE și HAVING este că WHERE se aplică rândurilor, în timp ce HAVING se aplică grupurilor.

O interogare poate conține atât o clauză WHERE, cât și o clauză HAVING. În acest caz, clauza WHERE este executată prima deoarece este executată înainte de grupare. De exemplu, luați în considerare următoarea modificare a interogării anterioare:

Cerere: Pentru fiecare tip de clădire, aflați nivelul mediu de calitate între clădirile de statut 1. Luați în considerare doar acele tipuri de clădiri al căror nivel de calitate maxim nu depășește 3.

SELECTARE TIP, AVG (QLTY_JLEVEL)

UNDE STAREA = 1

AVÂND MAX(QLTY_LEVEL)<= 3

Rezultat:

TYPE AVG(QLTY_LEVEL)

Magazin 1

Clădire de locuințe 3

Rețineți că, începând cu clauza FROM, clauzele sunt executate în ordine, iar apoi se aplică clauza SELECT. Astfel, clauza WHERE este aplicată tabelului BUILDING și toate rândurile în care STATUS este diferit de 1 sunt șterse. Rândurile rămase sunt grupate după TIP; toate rândurile cu aceeași valoare TYPE ajung în același grup. Astfel, se creează mai multe grupuri, câte unul pentru fiecare valoare TYPE. Clauza HAVING este apoi aplicată fiecărui grup, iar acele grupuri a căror valoare maximă a nivelului de calitate depășește 3 sunt eliminate. În cele din urmă, clauza SELECT este aplicată grupurilor rămase.

7. Funcții și subinterogări încorporate

Funcțiile încorporate pot fi utilizate numai într-o clauză SELECT sau o comandă HAVING. Cu toate acestea, o clauză SELECT care conține o funcție inline poate face parte dintr-o subinterogare. Să ne uităm la un exemplu de astfel de subinterogare:

Cerere: Care lucrători au un tarif orar mai mare decât media?

SELECTAȚI WORKER_NAME

UNDE HRLY_RATE >

(SELECTARE AVG(HRLY_RATE)

Rezultat:

H. Columb

Rețineți că interogarea secundară nu este corelată cu interogarea principală. Subinterogarea returnează exact o valoare - rata medie orară. Interogarea principală selectează un lucrător numai dacă rata lui este mai mare decât media calculată.

Interogările corelate pot folosi și funcții încorporate:

Întrebare: Care angajat are un tarif orar mai mare decât tariful orar mediu în rândul subordonaților aceluiași manager?

În acest caz, în loc să calculăm un tarif mediu orar pentru toți lucrătorii, trebuie să calculăm tariful mediu pentru fiecare grup de lucrători care raportează aceluiași manager. Mai mult, calculul nostru trebuie făcut din nou pentru fiecare lucrător luat în considerare de interogarea principală:

SELECTAȚI A. WORKER_NAME

SQL vă permite să imbricați interogări unul în celălalt. De obicei, o subinterogare returnează o singură valoare, care este verificată pentru a vedea dacă predicatul este adevărat.

Tipuri de termeni de căutare:
. Comparație cu rezultatul unei subinterogări (=, >=)
. Verificarea apartenenței la rezultatele unei subinterogări (IN)
. Verificarea existenței (EXISTĂ)
. Comparație multiplă (cantitativă) (ORICE, TOATE)

Note privind interogările imbricate:
. O subinterogare trebuie să selecteze doar o coloană (cu excepția unei subinterogări cu un predicat EXISTS), iar tipul său de date rezultat trebuie să se potrivească cu tipul de date al valorii specificate în predicat.
. În unele cazuri, puteți utiliza cuvântul cheie DISTINCT pentru a vă asigura că este returnată o singură valoare.
. Nu puteți include o clauză ORDER BY sau UNION într-o subinterogare.
. Subinterogarea poate fi situată fie în stânga, fie în dreapta condiției de căutare.
. Subinterogările pot folosi funcții de agregare fără o clauză GROUP BY, care returnează automat o valoare specială pentru orice număr de rânduri, un predicat IN special și expresii bazate pe coloane.
. Ori de câte ori este posibil, ar trebui să utilizați îmbinări de tabel JOIN în loc de subinterogări.

Exemple de interogări imbricate:

SELECT * FROM Comenzi WHERE SNum=(SELECT SNum FROM SalesPeople WHERE SNum=’Motika’)
SELECT * FROM Comenzi WHERE SNum IN (SELECT SNum FROM SalesPeople WHERE City=’Londra’)
SELECT * FROM Comenzi WHERE SNum=(SELECT DISTINCT SNum FROM Comenzi WHERE CNum=2001)
SELECT * FROM Comenzi WHERE Amt>(SELECT AVG(Amt) FROM Comenzi WHERE Odate=10/04/1990)
SELECT * FROM Client WHERE CNum=(SELECT SNum+1000 FROM SalesPeople WHERE SNum=’Serres’)

2) Subinterogări aferente

În SQL, puteți crea subinterogări care fac referire la un tabel dintr-o interogare exterioară. În acest caz, subinterogarea este executată de mai multe ori, o dată pentru fiecare rând de tabel din interogarea exterioară. Prin urmare, este important ca subinterogarea să folosească indexul. O subinterogare poate accesa același tabel ca unul extern. Dacă interogarea exterioară returnează un număr relativ mic de rânduri, atunci subinterogarea legată va fi mai rapidă decât cea nelegată. Dacă o subinterogare returnează un număr mic de rânduri, interogarea asociată va fi mai lentă decât interogarea fără legătură.

Exemple pentru subinterogări asociate:

SELECT * FROM SalesPeople Main WHERE 1(SELECT AVG(Amt) FROM Comenzi O2 WHERE O2.CNum=O1.CNum) // returnează toate comenzile a căror valoare depășește valoarea medie a comenzii pentru un anumit client

3) Predicatul EXISTĂ

Forma sintactică: EXISTĂ ()

Predicatul ia o subinterogare ca argument și evaluează la adevărat dacă subinterogarea are rezultate și false în caz contrar. Subinterogarea este executată o singură dată și poate conține mai multe coloane, deoarece valorile acestora nu sunt verificate, dar rezultatul prezenței rândurilor este pur și simplu înregistrat.

Note despre predicatul EXISTĂ:
. EXISTS este un predicat care returnează TRUE sau FALSE și poate fi folosit singur sau cu alte expresii booleene.
. EXISTS nu poate folosi funcții de agregare în subinterogarea sa.
. În subinterogările corelate, predicatul EXISTS este executat pentru fiecare rând al tabelului exterior.
. Puteți combina predicatul EXISTS cu îmbinări de tabel.

Exemple pentru predicatul EXISTS:

SELECT * FROM Client WHERE EXISTS(SELECT * FROM Customer WHERE City=’San Jose’) – returnează toți clienții dacă vreunul dintre ei locuiește în San Jose.
SELECT DISTINCT SNum FROM Customer First WHERE NU EXISTE (SELECT * FROM Customer Send WHERE Send.SNum=First.SNum AND Send.CNumFirst.CNum) – returnează numărul de vânzători care au servit un singur client.
SELECTARE DISTINCT F.SNum, SName, F.City FROM SalesPeople F, Client S WHERE EXISTIST (SELECT * FROM Customer T WHERE S.SNum=T.SNum AND S.CNumT.CNum AND F.SNum=S.SNum) – returnează numerele, numele și orașele de reședință ale tuturor vânzătorilor care au servit mai mulți clienți.
SELECT * FROM SalesPeople Frst WHERE EXISTIS (SELECT * FROM Customer Send WHERE Frst.SNum=Send.SNum AND 1

4) Predicate ale comparaţiei cantitative

Forma sintactică: (=|>|=|) ORICE|TOȚI ()

Aceste predicate folosesc o subinterogare ca argument, totuși, în comparație cu predicatul EXISTS, ele sunt folosite împreună cu predicate relaționale (=,>=). În acest sens, ele sunt similare cu predicatul IN, dar sunt folosite numai cu subinterogări. Standardul permite folosirea cuvântului cheie SOME în loc de ANY, dar nu toate SGBD-urile îl acceptă.

Note despre predicate de comparație:
. Predicatul ALL evaluează la TRUE dacă fiecare valoare selectată în timpul execuției subinterogării satisface condiția specificată în predicatul de interogare extern. Cel mai des este folosit cu inegalități.
. Predicatul ANY se evaluează la TRUE dacă cel puțin o valoare selectată în timpul execuției subinterogării satisface condiția specificată în predicatul interogare extern. Cel mai des este folosit cu inegalități.
. Dacă subinterogarea nu returnează niciun rând, atunci ALL ia automat valoarea TRUE (se consideră că condiția de comparare este îndeplinită), iar pentru ORICE ia valoarea FALSE.
. Dacă comparația este TRUE pentru niciun rând și există unul sau mai multe rânduri cu o valoare NULL, atunci ORICE returnează UNKNOWN.
. Dacă comparația este FALSA pentru niciun rând și există unul sau mai multe rânduri cu o valoare NULL, atunci ALL returnează UNKNOWN.

Exemple pentru predicatul comparației cantitative:

SELECT * FROM SalesPeople WHERE City=ANY(SELECT City FROM Client)
SELECTAȚI * FROM Comenzi WHERE Amt ALL(SELECTARE Evaluare FROM Client WHERE Oraș=’Roma’)

5) Predicat de unicitate

UNIC | DISTINCT ()

Predicatul este folosit pentru a verifica unicitatea (absența duplicatelor) în datele de ieșire ale subinterogării. Mai mult, în predicatul UNIQUT, șirurile cu valori NULL sunt considerate unice, iar în predicatul DISTINCT, două valori nedefinite sunt considerate egale între ele.

6) Predica meciuri

MECI ()

Predicatul MATCH testează dacă valoarea unui șir de interogare se va potrivi cu valoarea oricărui șir rezultat din subinterogare. Această subinterogare diferă de predicatele IN AND ANY prin faptul că permite procesarea potrivirilor „parțiale” (PARTIAL) care pot apărea printre rândurile care au niște valori NULL.

7) Interogări în secțiunea FROM

De fapt, este legal să folosiți o subinterogare oriunde este permisă o referință de tabel.

SELECT CName, Tot_Amt FROM Client, (SELECT CNum, SUM(Amt) AS Tot_Amt FROM Comenzi GROUP BY CNum) WHERE City=’Londra’ AND Customer.CNum=Comenzi.CNum
//subinterogare returnează cantitatea totală de comenzi plasate de fiecare client din Londra.