Diferența dintre există și în SQL? (paisprezece)

Care este diferența dintre existente și în aprovizionare în SQL?

Când trebuie să folosim există și când ar trebui să folosim?

Există este mult mai rapid decât atunci când rezultatele subquery sunt foarte mari.
În mai rapid decât există atunci când rezultatele de subvenționare sunt foarte mici.

Creați tabelul T1 (ID Int, titlul Varchar (20), SomeintCol Int) Du-te Creare Tabelul T2 (ID INT, T1ID INT, SOMEDATA VARCHAR (20)) Mergeți în T1 Selectați 1, "Titlul 1", 5 Uniune Toate Selectați 2 Selectați 2 , "Titlul 2", 5 Uniunea All Select 3, "Titlul 3", 5 Uniunea Toate Selectați 4, "Titlul 4", 5 Uniune Toate selectați Null, "Titlul 5", 5 Uniune Toate Selectați Null, "Titlul 6", 5 Introduceți în T2 Selectați 1, 1, "Date 1" Union Toate Selectați 2, 1, "Date 2" Union Toate Selectați 3, 2, "Date 3" Union Toate Selectați 4, 3, "Date 4" Union Toate Selectați 5 , 3, "Date 5" Union Toate selectarea 6, 3, "Date 6" Union Toate Selectați 7, 4, "Date 7" Union Toate Selectați 8, Null, "Date 8" Union Toate selectați 9, 6, "Date 9 "Uniunea Selectează 10, 6," Date 10 "Union Toate selectați 11, 8," Date 11 "

Solicitați 1.

Selectați de la T1 unde nu există (selectați * de la T2 unde T1D \u003d T2.T1ID)

Solicitați 2.

Selectați T1. * De la T1 unde nu este în (selectați T2.T1ID de la T2)

Dacă în T1 Identificatorul are o valoare zero, interogarea 1 le va găsi, dar interogarea 2 nu va putea găsi parametri zero.

Vreau să spun că nu poate compara nimic cu zero, așa că nu are rezultatul pentru nul, dar există poate compara totul cu zero.

Există mai repede decât în. Dacă majoritatea criteriilor de filtrare sunt în subquery, atunci este mai bine să se utilizeze și dacă majoritatea criteriilor de filtrare sunt în cererea principală, este mai bine să utilizați există.

Dacă utilizați declarația în Declarația, mecanismul SQL va scana toate înregistrările extrase din cererea internă. Pe de altă parte, dacă vom folosi există, mecanismul SQL va opri procesul de scanare de îndată ce se găsește coincidența.

Dacă subquery returnează mai mult de o valoare, este posibil să fie necesar să efectuați o interogare externă - dacă valorile din coloana specificate în starea corespund oricărei valori din setul de subquară rezultat. Pentru a realiza această sarcină, trebuie să utilizați un cuvânt cheie.

Puteți utiliza o subquey pentru a verifica dacă există un set de înregistrări. Pentru a face acest lucru, trebuie să utilizați oferta existentă cu o subquery. Cuvântul cheie există întotdeauna returnează adevărat sau fals.

Care dintre ele depinde mai repede de numărul de solicitări primite de solicitarea internă:

• Când cererea dvs. internă alege o mie de linii, atunci există va fi cea mai bună alegere.
• Când interogarea dvs. internă alege mai multe linii, atunci va fi mai rapidă

Există evaluează valoarea lui True sau False, dar în compararea cu valoarea multiplă. Când nu știți că înregistrarea există sau nu, trebuie să alegeți există

Cuvântul cheie există evaluează valoarea adevărată sau falsă, dar cuvântul cheie compară toate valorile din coloana de subquerție corespunzătoare. O altă selecție 1 poate fi utilizată cu comanda existist. Exemplu:

Selectați * din temp1 unde există (selectați 1 din temp2 unde condițiile ...)

Dar în mai puțin eficient, există și mai repede.

Bazat regula Optimizer. :

• Există mult mai repede decât atunci când rezultatele de subvenționare sunt foarte mari.
• În mai repede decât există, când rezultatele sub-solicitare sunt foarte mici.

Bazat costuri de optimizare :

• Nu e nicio diferenta.

Conform informațiilor mele, când subquery returnează valoarea nulă, întregul operator devine nul. În acest caz, folosim ieșirile cuvintelor cheie. Dacă vrem să comparăm anumite valori în subqueries, atunci folosim cuvântul cheie în.

Există atunci când trebuie să comparați rezultatele cererii cu o altă substanță. Rezultatele interogării # 1 trebuie să fie obținute atunci când rezultatele întreținerii sunt coincis. Vizualizați pentru a vă alătura .. De exemplu, selectați Tabelul Clientului nr. 1, care a plasat și tabelul de aplicații # 2

În acest sens este necesar să se extragă dacă este listată valoarea unei coloane specifice (1,2,3,4,5). De exemplu, selectați clienții care se află în următoarele coduri poștale, adică valorile ZIP_CODE sunt în lista (....).

Când să utilizați unul peste celălalt ... când simțiți că citește în consecință (comunică cu cele mai bune intenții).

Am constatat că utilizarea cuvintelor cheie există este adesea foarte lentă (este foarte adevărată în Microsoft Access). În schimb, folosesc operatorul de unificare după cum urmează:

Presupun că știi ce fac ei și, prin urmare, sunt folosite în mod diferit, așa că voi înțelege întrebarea dvs. după cum urmează: Când ar fi o idee bună să rescrieți SQL să folosească în loc de existență sau viceversa.

Este o ipoteză corectă?

Schimbare : Motivul pentru care întrebați este că, în multe cazuri, puteți rescrie SQL în funcție de utilizare există și invers, și pentru unele motoare de baze de date, Query Optimizer va procesa două în mod diferit.

De exemplu:

Selectați * de la clienți unde există (selectați * de la comenzi în care comenzile.Customererid \u003d Clienți.ID)

puteți rescrie în:

Selectați * de la clienți în cazul în care ID-ul (selectați CustomerID de la comenzi)

sau cu o conexiune:

Selectați clienții. * De la clienți Inner Comenzi de conectare la clients.id \u003d comenzi.Customererid

Astfel, întrebarea mea rămâne dacă posterul original este o întrebare despre ceea ce face și există și, prin urmare, cum să o folosească sau va cere să rescrie SQL, folosind în a utiliza există în schimb, sau viceversa, va fi bine idee?

Cred că are un răspuns direct. De ce nu verificați din oameni care au dezvoltat această caracteristică în sistemele lor?

Dacă sunteți dezvoltatorul MS SQL, aici este răspunsul de la Microsoft.

Stabilește dacă valoarea specificată corespunde oricărei valori din subquery sau listă.

Specifică o subquery pentru a verifica existența șirurilor de caractere.

În anumite circumstanțe, este mai bine să se folosească mai degrabă decât să existe. În general, dacă predicatul selectiv este în subquery, apoi utilizați. Dacă predicatul selectiv este în interogarea părinte, atunci utilizați.

ÎN. Sprijină numai relațiile de egalitate (sau inegalitate, atunci când sunt precedente Nu.).
Acesta este sinonim \u003d oricare / \u003d Unele , de exemplu

Selectați * de la T1 unde X în (selectați x de la T2);

Există. Suportă opțiunile pentru tipurile de relații care nu pot fi exprimate utilizând ÎN. , de exemplu -

Selectați * de la T1 unde există (selectați null de la T2 unde t2.x \u003d t1.x și t2.y\u003e t1.y și t2.z cum ar fi "℅" || t1.z || "℅");

Și pe o altă notă -

Performanțele estimate și diferențele tehnice între Există. și ÎN. Acestea pot apărea din cauza unor implementări / limitări specifice / erorilor unui anumit furnizor, dar în multe cazuri nu este altceva decât miturile create din cauza unei înțelegeri insuficiente a bazelor de date interne.

Precizia de definire a tabelelor, precizia statisticilor, configurația bazei de date și versiunea optimizorului afectează planul de execuție și, prin urmare, asupra indicatorilor de performanță.

"A fost mai ușor" - am crezut că așezat pentru optimizarea următoarei cereri în SQL Management Studio. Când am scris sub MySQL, a fost cu adevărat mai ușor - sau nu funcționează sau nu. Sau încetinește sau nu. Explicați-mi rezolvarea tuturor problemelor mele, nimic mai mult nu era necesar. Acum am un mediu puternic de dezvoltare, de depanare și de optimizare / funcții, iar toate aceste amestecuri creează în opinia mea doar mai multe probleme. Și de ce? Deoarece optimizatorul de interogare încorporat este rău. Dacă în MySQL și PostgreSQL voi scrie

Selectați * de la A, B, C unde a.ID \u003d B.ID, B.ID \u003d C.ID

Și în fiecare dintre mese va exista cel puțin 5K linii - totul va atârna. Și mulțumesc lui Dumnezeu! Deoarece altfel în dezvoltator, în cel mai bun caz, este produsă să scrie foarte mult, iar în cel mai rău lucru nu înțelege ce face! La urma urmei, aceeași interogare în MSSQL va fi similară

Selectați * de la o Alăturare B pe A.Id \u003d B.ID Alăturați-vă C pe B.ID \u003d C.ID

Optimizatorul încorporat va avea loc de bovine și totul va fi OK.

El va decide, de asemenea, că este mai bine să faci - să existe sau să se alăture și mai mult. Și totul va funcționa cât de optim optim.

Numai unul, dar. La un moment bun, optimizatorul se va transforma într-o interogare complexă și va salva, iar apoi veți obține o problemă de tip fagure. Și tu o primești, poate că nu imediat, dar când greutatea tabelului ajunge la masa critică.

Deci, prin esența articolului. Există și sunt operațiuni foarte grele. De fapt, acesta este un subquery separat pentru fiecare Rezultatele cusăturilor. Și dacă există și cuiburi, atunci acesta este, în general, o lumină de carcasă. Totul va fi bine când 1, 10, 50 de linii se întoarce. Nu veți simți diferența și poate să vă alăturați chiar încet. Dar când va ieși 500 - vor începe probleme. 500 de subquerie în cadrul unei singure cereri sunt grave.

Permiteți din punctul de vedere al înțelegerii umane și există mai bine, dar din punctul de vedere al costurilor temporare pentru cererile care returnează 50+ șiruri - nu sunt permise.

Este necesar să se facă o rezervare, care, în mod natural, dacă undeva scade - undeva ar trebui să sosească. Da, Alăturați-vă este mai mult memorie de resurse, deoarece este de a păstra întreaga masă de valori și de a funcționa - suprapunerea decât pentru a trage subquerie pentru fiecare linie, eliberarea rapidă a memoriei. Este necesar să se urmărească în mod specific la cerere și să măsoară dacă utilizarea memoriei inutile este critică sau nu.

Voi da exemple de analogii complete. În general, nu am întâlnit mai multe interogări ale unui astfel de grad de dificultate care nu putea fi dezmembrat în cascadă de la Join'ov. Lăsați-o să meargă pentru asta, dar totul poate fi dezvăluit.

Selectați * De la A.ID IN (Selectați ID de la B) Selectați * de la A Unde există (selectați Top 1 1 de la B unde B.ID \u003d A.Id) Selectați * de la A ALING B pe A.ID \u003d B. ID Selectați * de la A.ID Nu In (Selectați ID de la B) Selectați * de la A Unde nu există (Selectați Top 1 1 de la B unde B.ID \u003d A.Id) Selectați * dintr-o stânga Alăturați-o pe A. Id \u003d b.id unde b.id este nul

Repet - aceste exemple de optimizator MSSQL optimizează pentru performanțe maxime și niciodată nu vor fi niciodată pe astfel de interogări simple.

Luați în considerare un exemplu de o cerere reală, care a trebuit să rescrie din cauza faptului că, în unele eșantioane, el a depins pur și simplu (structura este foarte simplificată, iar conceptele sunt înlocuite, nu este necesar să sperie o optimalitatea bazei de date structura).

Trebuie să scoateți toate duplicatele "produselor" în diferite conturi, concentrându-se pe parametrii produsului, grupul său și grupul-mamă, dacă există astfel de.

Selectați D.Product_ID de la produsul S, Product_Group SG stânga Alăturați-vă la m_pg_group_id \u003d sd.m_pg_dependendice_child_id), produs d, produs_group dg stânga Alăturați-vă m_pg_group_id \u003d dd.m_pg_dependendice_child_id) în cazul în care s.product_group_id \u003d SG .PRODUCT_GROUP_ID ȘI D.PRODUCT_GROUP_ID \u003d DG.PRODUCT_GROUP_ID ȘI SG.PRODUCT_GROUP_PERSPEC \u003d DG.PRODUCT_GROUP_PERSPEC ȘI SG.PRODUCT_GROUP_NAME \u003d DG.PRODUCT_GROUP_NAME ȘI S.PRODUCT_NAME \u003d D.PRODUCT_NAME \u003d D.PRODUCT_TYPE \u003d D.PRODUCT_TYPE ȘI S.PRODUCT_IS_SECURE \u003d D.PRODUCT_IS_SECURE și s.product_multiselecte și dg.product_group_is_tmpl \u003d 0 și (sd.m_pg_dependendice_child_id este nul și dd.m_pg_dependendicy_child_id este nul) sau există (selectați 1 de la produs_group sg1, produs_group dg1 unde sd.m_pg_dependendice_parent_id \u003d sg1.product_group_id și DD .M_pg_dependendice_parent_id \u003d dg1.product_group_id și sg1.product_group_perspec \u003d dg1.productiv_group_perspec și sg1.produs_group_name \u003d dg1.product_group_n Ame și))

Deci, acesta este cazul când optimizatorul a salvat. Și pentru fiecare linie, au fost efectuate grele, care au ucis baza.

Selectați D.PRODUCT_ID de la produs S Alăturați produsul d on s.product_type \u003d d.product_type și s.product_Name \u003d d.product_is_secure \u003d d.product_is_secure și s.product_multatelect Alăturați-vă produs_group sg pe s.product_group_id \u003d sg.PRODUCT_GROUP_ID alătura PRODUCT_GROUP dg pe d.PRODUCT_GROUP_ID \u003d dg.PRODUCT_GROUP_ID și sg.PRODUCT_GROUP_NAME \u003d dg.PRODUCT_GROUP_NAME și sg.PRODUCT_GROUP_PERSPEC \u003d dg.PRODUCT_GROUP_PERSPEC lăsat alătura M_PG_DEPENDENCY sd pe sg.PRODUCT_GROUP_ID \u003d sd.M_PG_DEPENDENCY_CHILD_ID stânga join M_PG_DEPENDENCY dd pe dg.PRODUCT_GROUP_ID \u003d dd.M_PG_DEPENDENCY_CHILD_ID lăsat alătura PRODUCT_GROUP sgp pe sgp.PRODUCT_GROUP_ID \u003d sd.M_PG_DEPENDENCY_PARENT_ID lăsat alătura PRODUCT_GROUP dgp pe dgp.PRODUCT_GROUP_ID \u003d dd.M_PG_DEPENDENCY_PARENT_ID și sgp.PRODUCT_GROUP_NAME \u003d dgp.PRODUCT_GROUP_NAME și isnull (sgp.PRODUCT_GROUP_IS_TMPL, 0) \u003d isnull (dgp. Produs_group_is_tmpl, 0) unde (sd.m_pg_dependendice_child_id este null și dd.m_pg_dependendicent_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, productivitatea adânciturilor a crescut exponențial numărul de produse găsite. Mai degrabă, timpul de căutare a rămas aproape independent de numărul de coincidențe și a fost întotdeauna foarte mic. Cum ar trebui să fie.

Acesta este un bun exemplu despre modul în care este un optimizator de încredere poate juca o glumă crudă. Nu aveți încredere în el, nu fiți leneși, mânerele să se alăture, gândiți-vă de fiecare dată când este mai bine în această situație - există, în sau alăptați.

SQL există predicat efectuează o sarcină logică. În cadrul interogărilor SQL, acest predicat este utilizat în expresiile speciei

Există (selectați * de la numele_table ...).

Această expresie returnează adevărul atunci când una sau mai multe linii corespunzătoare condiției se găsesc la cerere și o minciună, când nu se găsește nici un șir.

Căci nu există, invers. Expresie

Nu există (selectați * de la name_table ...)

returnează adevărul când nu se găsește niciun șir la cerere și o minciună când a fost găsită cel puțin o linie.

Cele mai simple interogări cu predicatul SQL există

În exemple, lucrăm cu baza de date bibliotecă și cu cărțile sale în cartea de utilizare (Bookinuse) și "Utilizator" (utilizator). În timp ce avem nevoie doar de cartea "Cartea în Bookinuse).

Autor	Titlu	Pubyear.	Inv_no.	Numele de utilizator
Greu	Razboi si pace	2005	28	65
Cehov.	Chiria de cireșe	2000	17	31
Cehov.	Povestiri selectate	2011	19	120
Cehov.	Chiria de cireșe	1991	5	65
ILF și Petrov.	Cele douăsprezece scaune	1985	3	31
Mayakovsky.	Poiz.	1983	2	120
Păstârnac	Doctor Zhivago.	2006	69	120
Greu	duminică	2006	77	47
Greu	Anna Karenina	1989	7	205
Pushkin.	Fiica căpitanului	2004	25	47
Gogol.	Piese	2007	81	47
Cehov.	Povestiri selectate	1987	4	205
Păstârnac	Favorite	2000	137	18

Exemplul 1.Determinați ID-ul utilizatorilor care sunt eliberați pentru cărțile Tolstoy care sunt emise și de cărțile lui Chekhov. Într-o interogare externă, datele sunt selectate pe utilizatorii care au fost emise de cărțile Tolstoy și există predicate stabilește condiția suplimentară care este verificată într-o solicitare internă - utilizatori eliberați de cărțile lui Chekhov. O condiție suplimentară în cererea internă este coincidența identificatorilor de utilizator din cererile externe și interne: user_id \u003d tols_user.user_id. Cererea va fi după cum urmează:

Această interogare va returna următorul rezultat:

Există și în diferențele predicate

Când vă uitați mai întâi la cererile cu predicat există, este impresionat că este identic predicat în . Nu este adevarat. Deși sunt foarte asemănătoare. În predicat în căutarea valorilor din gama specificată în argumentul său și dacă există astfel de valori, atunci toate rândurile corespunzătoare acestui interval sunt selectate. Rezultatul acțiunii de predicare a existenței este răspunsul "da" sau "nu" la întrebarea dacă există vreo înțeles care sunt potrivite în argument. În plus, în fața predicatului, numele coloanei pe care ar trebui văzute rândurile corespunzătoare valorilor din domeniu. Vom analiza un exemplu care arată diferența dintre predicatul de către un predicat și sarcina rezolvată folosind un predicat.

Exemplul 4. Determinați ID-ul utilizatorilor care sunt eliberați de cărțile autorilor ale căror cărți sunt eliberate utilizatorului cu ID 31. Cererea va fi după cum urmează:

Numele de utilizator

120

65

205

Cererea internă (după) selectează autorii: Chekhov; ILF și Petrov. Cererea externă selectează toți utilizatorii care au fost emise de cărți ale acestor autori. Vedem că, spre deosebire de predicatul de existist, predicatul este precedat de numele coloanei, în acest caz - autor.

Solicitările cu predicat există și condiții suplimentare

Dacă în plus față de predicatul există în interogare, aplicați cel puțin o condiție suplimentară, de exemplu, specificată de funcții agregate Astfel de solicitări pot fi utilizate pentru o analiză simplă a datelor. Vom demonstra acest lucru în exemplul următor.

Exemplul 5.Determinați ID-ul utilizatorilor care au fost emise cel puțin o carte de Pasternak și, în același timp, au fost emise mai mult de 2 cărți. Scriem următoarea solicitare, în care prima condiție este stabilită de un predicat cu o cerere investit, iar cea de-a doua condiție cu operatorul având trebuie să urmeze întotdeauna după solicitarea investit:

Solicitare Rezultat:

Numele de utilizator

120

După cum se poate observa din tabelul de carte, Cartea Pastenk este, de asemenea, eliberată utilizatorului cu ID 18, dar este emisă o singură carte și nu se încadrează în eșantion. Dacă aplicați funcția de numărare la o interogare similară încă o dată, dar deja pentru liniile selectate (practicați-vă), puteți obține, de asemenea, informații despre câți utilizatori care citesc cărțile deasrk, în timp ce citiți cărțile altor autori. Acest lucru este deja din domeniul de aplicare al analizei datelor.

Cererile cu predicat există în două mese

Există solicitările predicate pot prelua date de la mai multe tabel. Multe sarcini pot fi rezolvate cu același rezultat folosind operatorul se alătură. Dar, în unele cazuri, utilizarea există vă permite să faceți o cerere mai puțin greoaie. Utilizarea există preferate în cazurile în care coloanele vor cădea în tabelul rezultat numai de la o masă.

În exemplul următor, din aceeași bază de date, în plus față de tabelul de carte, va fi, de asemenea, necesar tabelul "Utilizator".

Rezultatul interogării va fi următorul tabel:

Autor

Cehov.

Mayakovsky.

Păstârnac

Ca și în cazul utilizării operatorului de îmbinare, în cazurile de mai mult de un tabel, ar trebui să utilizați aliasurile de tabel pentru a verifica conformitatea valorilor cheie care leagă tabelele. În exemplul nostru de pseudonime de masă - BK și SUA și cheia care leagă tabelele - user_id.

Există predicat în conexiuni de mai mult de două mese

Acum vom vedea mai mult, de ce există o utilizare preferată în cazurile în care coloanele vor cădea în tabelul rezultat numai de la o masă.

Lucrăm cu baza de date "imobiliare". Tabelul de tranzacționare conține date privind tranzacțiile. Pentru sarcinile noastre din acest tabel va fi o coloană importantă de tip cu date despre tipul de tranzacție - vânzare sau de închiriat. Tabelul obiect conține date despre obiecte. În acest tabel, vom avea nevoie de valorile coloanelor camerelor (număr de camere) și datele Logbalc conținând date despre prezența unei loggii sau a unui balcon în format boolean: 1 (da) sau 0 (nu). Tabele Clientul, Managerul și proprietarul conțin date în mod corespunzător despre clienți, manageri ai companiei și proprietarii de obiecte imobiliare. În aceste tabele FNAME și LNAME, respectiv, numele și prenumele.

Exemplul 7. Identificați clienții care au cumpărat sau închiriază obiecte care nu au loggie sau balcon. Scriem următoarea solicitare, în care predicatul există este setat la rezultatul conectării a două tabele:

Deoarece coloanele sunt selectate din tabelul client folosind operatorul de stele, atunci vor fi afișate toate coloanele acestui tabel, în care vor exista cât mai multe rânduri, deoarece clienții sunt în concordanță cu starea specificată de predicatul existist. Din mesele la care este desenată cererea atașată, nu avem nevoie să emităm o singură coloană. Prin urmare, se extrage doar o singură coloană pentru a salva timpul mașinii. Pentru a face acest lucru, după selectarea cuvântului, este prescrisă o unitate. Aceeași recepție este, de asemenea, aplicată în interogări în următoarele exemple.

Scrieți o interogare SQL cu exigenți predicați-vă, apoi vedeți decizia

Continuăm să scriem cereri SQL cu predicatul

Exemplul 9. Determinați proprietarii de obiecte care au fost închiriate. Scriem următoarea solicitare, în care predicatul există, de asemenea, să apeleze la rezultatul conectării a două tabele:

Ca și în exemplul anterior, de la tabelul la care se adresează cererea externă, vor fi afișate toate câmpurile.

Exemplul 10. Determinați numărul de proprietari, cu obiectele din care au condus un manager Saveliev. Scriem o cerere în care o solicitare externă se referă la o conexiune a trei mese, iar predicatul există este setat la recurs numai la un tabel:

Toate solicitările sunt verificate pe o bază de date existentă. Utilizare reușită!

Baze de date relaționale și limbaj SQL

Novosibirsk Academia de Stat de Economie și Management

Atelier de laborator pe disciplină

"BAZĂ DE DATE"

Laborator de lucru n 7

"Limba bazei de date SQL: echipe de manipulare a datelor»

Novosibirsk 2000.

SQL este un nume abreviat al limbii de interogare structurată (limbă de interogare structurată). Din titlul limbii este clar că scopul său principal este de a forma cereri de informații din baza de date. Comenzile privind selectarea datelor constituie baza limbii de manipulare a datelor DML - componenta limbajului SQL. Cu toate acestea, DML constă nu numai de comenzi de eșantionare a datelor din baza de date. Există, de asemenea, comenzi de modificare a comenzilor, gestionarea datelor și altele.

Lucrarea de laborator discută mijloacele de bază ale limbajului DML. În procesul de efectuare a lucrărilor de laborator, vom adera la standardul SQL2.

Datorită faptului că SQL este un limbaj voluminos, vom lua în considerare numai comenzile principale. Diferitele instrumente specifice SQL sunt discutate în lucrările de laborator ulterioare.

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

Ca urmare a lucrărilor de laborator, sunteți în mod reproiectat de a manipula datele utilizând comenzi SQL, luați în considerare dialectul lingvistic implementat în doamna SQL Server DBMS.

Introducere

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

Inițial, structura sintaxă SQL a fost bazată (sau cel puțin părea că este fondată) pe calculul relațional al codului. Singura suport de algebră relațională acceptată a fost unită.

În SQL2, în plus față de un calcul relațional similar al sintaxei dezvoltate în standardul anterior, operațiunile sunt implementate direct prin combinarea, intersecția, diferența și compusul. Operațiile de alegere, design și lucrări au fost menținute (și au continuat să fie acceptate) aproape direct, în timp ce operațiunile de fisiune și de atribuire sunt susținute într-o formă mai greoaie.

Mai întâi descriem limba de interogare SQL și apoi modificările sale de intrare și de date. Operațiunile de modificare a datelor vor fi descrise ultima dată, deoarece structura acestora se bazează într-o anumită măsură a structurii limbii de interogare.

Solicitări simple

Pentru noi solicitare simplă Va exista o interogare care se referă numai la o masă de bază de date. Solicitările simple ne vor ajuta să ilustrăm structura de bază a SQL.

Solicitare simplă. Interogarea care se referă numai la o masă de bază de date.

Anchetă: Cine lucrează de tencuieli?

Unde Skill_type \u003d "Plastectiere"

Rezultat:

Ryikover.

Această solicitare ilustrează cele trei cele mai frecvent întâlnite. fraze SQL: Selectați, de la și unde. Deși în exemplul nostru le-am plasat la diferite linii, toți pot sta în aceeași linie. Acestea pot fi, de asemenea, plasate cu mici întârzieri, iar cuvintele din interiorul fraze pot fi separate printr-un număr arbitrar de spații. Luați în considerare caracteristicile fiecărei fraze.

SELECTAȚI. Fraza selectată enumeră coloanele care trebuie să fie conectate la tabelul rezultat. Este întotdeauna coloane de o masă relațională. În exemplul nostru, tabelul rezultat constă dintr-o coloană (nume), dar, în general, poate conține mai multe coloane; De asemenea, poate conține valori sau constante calculate. Vom da exemple de fiecare dintre aceste opțiuni. Dacă tabelul rezultat trebuie să conțină mai mult de o coloană, atunci toate coloanele necesare sunt listate după comanda SELECT prin virgulă. De exemplu, expresia selectată Worker_ID, numele va duce la un tabel constând din coloanele lucrător_id și de nume.

Selectați fraza. Specifică coloanele tabelului rezultat.

Din.. Fundația se fixează una sau mai multe tabele la care este menționată cererea. Toate coloanele enumerate în selecția și în cazul în care frazele trebuie să existe într-una din tabelele enumerate în comanda de comandă. În SQL2, aceste tabele pot fi definite direct în schemă ca tabele de bază sau reprezentări de date sau ele însele nu au numit tabele obținute ca urmare a cererilor SQL. În ultimul caz, cererea este citată în mod explicit în comandă.

Frază de la. Specifică tabelele existente la care este menționată cererea.

Unde. În cazul în care fraza conține o condiție. Pe baza cărora sunt selectate rândurile tabelului (tabelele). În exemplul nostru, condiția este că coloana Skill_Type trebuie să conțină o constantă "tencuială" închisă în apostrofe, ca întotdeauna cu constante de text în SQL. În cazul în care fraza este cea mai schimbabilă comandă SQL; Poate conține o varietate de condiții. Cea mai mare parte a prezentării noastre vor fi dedicate ilustrațiilor diferitelor modele permise în cazul în care comanda.

Fraza unde.Setează starea bazată pe care sunt selectate linii din tabelele specificate.

Interogarea SQL de mai sus este procesată de sistem în următoarea ordine: de la, unde, selectați. Pentru a avea rândurile tabelului specificate în comanda comenzii, sunt plasate în atelier pentru procesare. Apoi, în cazul în care fraza este folosită pentru fiecare rând. Toate liniile care nu satisfac în cazul în care condiția sunt excluse din considerație. Apoi, acele șiruri care satisfac condiția în care sunt procesate de comanda SELECT. În exemplul nostru, numele este selectat dintre fiecare linie, iar toate valorile selectate sunt afișate ca rezultate de interogare.

Anchetă: Anulați toate datele privind clădirile birourilor.

Unde tip \u003d "Office"

Rezultat:

Bldg iddresypeqlty nivelstatus.

312 ul.v., 123 office 2 2

210 mesteacan UL. 1011 Oficiul C 1

111 Osinovaya ul. 1213 Office 4 1

Un asterisc (*) în comanda SELECT înseamnă "întreaga linie". Aceasta este o reducere convenabilă pe care o vom folosi adesea.

Anchetă: Care este salariul săptămânal al fiecărui electrician?

Selectați Nume, "Salariu săptămânal = ", 40 * hrly_rate

Unde Skill_type \u003d "Electric"

Rezultat:

M. Salariu săptămânal de la Faraday \u003d 500.00

H.columb salariu săptămânal \u003d 620.00

Această solicitare ilustrează utilizarea și constantele simbolice (în exemplul nostru "salariu săptămânal \u003d") și calcularea comenzii SELECT, în interiorul comenzii SELECT pot fi calculate în care sunt utilizate coloane numerice și constanți numerici, precum și operatori aritmetici standard ( +, -, *, /), grupate, după cum este necesar, folosind paranteze. De asemenea, am inclus noua comandă prin comandă, care sortează rezultatul interogării în ordinea alfanumerică crescândă prin coloana specificată. Dacă doriți să comandați rezultate în descendent, atunci trebuie să adăugați un desC la comandă. Ordinea prin frază poate sorta rezultatele în mai multe coloane, într-una - în ordinea în creștere, conform altora - în ordine descrescătoare. Primul indică coloana cheii de sortare primară.

Constantă simbolică. Constant constând din litere, numere și caractere "speciale".

Anchetă: Cine are o rată orară de la 10 la 12 dolari?

Unde hrly_rate\u003e \u003d 10 și hrly_rate< - 12

Rezultat:

ID-ul lucrătorului. Nume Hrly_rate Skill_Type Supv_ID

Această solicitare ilustrează câteva opțiuni suplimentare pentru comanda În cazul în care operatorii de comparare și funcționarea booleană și (e). Pentru a compara coloanele cu alte coloane sau cu constante, pot fi utilizate șase operatori de comparație (\u003d,<> (nu este egal),<, >, <=, >\u003d). Pentru a crea condiții compozite sau pentru refuz, operații booleane și (sau (sau (sau) și nu (el) poate fi utilizat. Pentru a grupa condițiile, ca de obicei în limbile de programare, pot fi utilizate paranteze.

Comparație operatori \u003d,<>, <, >, <=, >=.

Operațiuni booleeneȘi (e), sau (sau) și nu (el) .

Pentru a formula această solicitare, puteți utiliza și între (între):

Unde hrly_rate între 10 și 12 ani

Între pot fi utilizate pentru a compara o anumită cantitate cu alte două valori, primul dintre acestea fiind mai mic decât al doilea, dacă valoarea comparativă poate fi egală cu fiecare dintre aceste valori sau orice valoare între ele.

Solicitare: Lista de plastici, acoperișoare și electricieni.

În cazul în care Skill_type în ("Plasteratorii", "acoperiș", "electric")

Rezultat:

Worker_id nume hrly_rate abilitate_type supv_id

1412 K.Noo 13.75 Plastereri 1520

2920 R.GARRET 10.00 ROOFER 2920

1520 Rykovover 11.75 Plastereri 1520

Această solicitare explică utilizarea operatorului de comparare (b). Dacă condiția este considerată adevărat dacă tipul de linie de specialitate este amplasată în interiorul setului specificat în paranteze, adică dacă tipul de specialitate este plasmatică, acoperiș sau electrician. Ne vom întâlni cu operatorul de intrare în subqueries.

Să presupunem că nu putem aminti cu precizie scrisul de specialitate: "Electric" sau "Electrik" sau cumva. Simbolurile șablonului care înlocuiesc șirurile de caractere vagi facilitează căutarea unei scrieri inexacte în interogare.

Simboluri ale șablonului.Simboluri care înlocuiesc șirurile nedefinite de caractere.

Anchetă: Listează angajații a căror tip de specialitate începe cu "ele".

În cazul în care Skill_type ca ("elek%")

Rezultat:

Worker ID nume Hrly_rate Skill_type supv_id

1235 m. Faraday 12.50 Electrician 1311

1311 x.columba 15.50 Electrician 1311

SQL are două simboluri de șabloane:% (procent) și _ (subliniere). Undercorele înlocuiește exact un simbol nedefinit. Procentajul înlocuiește un număr arbitrar de caractere, pornind de la zero. Când se utilizează simbolurile șablonului, un operator similar (AS) este utilizat pentru a compara variabilele simbolice cu constantele. Alte exemple:

Numele ca "__ columb"

Numele ca "__k%"

Condiția din primul exemplu este adevărată dacă numele este format din două caractere urmate de Columbus. În tabelul lucrătorului, toate numele încep cu primul inițial și punct. Astfel, folosind această condiție. Noi găsim toți angajații cu numele "Columbus". Condiția celui de-al doilea exemplu vă permite să găsiți toți angajații ale căror nume încep cu litera "K".

Anchetă:Găsiți toată munca care începe în următoarele două săptămâni.

Unde începeți _date între curent_date și

Rezultat: (Să presupunem că data curentă Data curentă \u003d 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

Această solicitare ilustrează utilizarea între operator (între) cu valorile de tip de dată (data) și intervalul. Current_Date este o funcție care returnează întotdeauna valoarea datei de astăzi. Expresie

Current_Date + Interval "14" zi

adaugă un interval de două săptămâni la data curentă. Astfel, atribuirea este selectată (ca o presupunere că astăzi 10.10) în cazul în care, în ea, valoarea coloanei Start_date se află între 10,10 și 24,10. Se poate observa din aceasta că putem adăuga la câmpurile valorilor tipului de interval. Mai mult, putem multiplica valorile lacunelor pentru numere întregi. De exemplu, să presupunem că dorim să aflăm ce număr va fi printr-un anumit număr de săptămâni (indicat de variabila Num_weeks (număr de săptămâni)). Putem să o facem așa:

Current_Date + Interval "7" zi * Num_weeks

2. Cereri multi -d

Abilitatea de a lega elementele de date în afara aceluiași tabel este importantă pentru orice limbă de baze de date. În algebra relațională, această funcție efectuează operația de conectare. Deși o parte semnificativă a SQL se bazează direct pe calculul relațional, SQL leagă datele din tabele diferite în același mod ca și operarea de a conecta algebra relațională. Acum vom arăta cum se face. Luați în considerare interogarea:

Anchetă:

Datele necesare pentru răspuns sunt în două tabele: lucrător și cesiune. Pentru a rezolva în SQL, trebuie să enumerați ambele tabele din comandă și să setați un tip special de caz:

Selectați Skill_type.

De la lucrător, cesiune

Unde lucrător.worker_id \u003d asignare.worker_id

Și blundg_id \u003d 435

Ce se petrece aici? Trebuie să luăm în considerare două etape de procesare a sistemului acestei cereri.

1. Ca de obicei, fraza de la Free este prelucrată pentru prima dată. Cu toate acestea, în acest caz, deoarece echipa conține două mese, sistemul creează un produs decarțian al rândurilor acestor tabele. Aceasta înseamnă că este creată (logic) o masă mare constând din coloane de ambele tabele în care fiecare linie de masă este asociată cu fiecare rând dintr-o altă masă. În exemplul nostru, pentru că există cinci coloane în tabelul lucrătorului și există patru coloane în tabelul de atribuire, în lucrarea cartezienă creată de Command, vor exista nouă coloane. Numărul total de linii decartuiale ale produsului este M * N, unde M este numărul de șiruri de caractere a tabelului lucrătorului; A n este numărul de rânduri ale tabelului de atribuire. Deoarece în Tabelul Lucrătorului 7 rânduri, iar în tabelul de misiune 19, atunci produsul cartesian va conține 7x19 sau 133 de linii. Dacă comandantul din mai mult de două mese, atunci este creat un produs decarțian al tuturor tabelelor specificate în comandă.

Lucrul cartezian. Rezultatul combinării fiecărei linii a unei mese cu fiecare Rând de altă masă.

2. După crearea unui tabel relațional gigant, sistemul, ca înainte, utilizează comanda în cazul în care comanda. Fiecare rând dintr-o masă creată de comandă de la. Verificate pentru finalizarea situației în care este cazul. Rândurile care nu satisfac starea sunt excluse din considerare. Apoi, fraza selectată este aplicată rândurilor rămase.

În cazul în care fraza în cererea noastră conține două condiții:

1. lucrător. Worker_id \u003d asignare.worker_id.

2. BLDG_ID \u003d 435

Prima dintre aceste condiții este condiția conexiunii. Rețineți că, deoarece atât lucrătorul, cât și tabelele de atribuire conțin o coloană numită lucrător_id, lucrarea lor carteiană va conține două coloane cu același nume. Pentru a le distinge, punem în fața numelui numelui de coloană al tabelului sursă, separarea punctului său.

Prima condiție înseamnă că, în orice bară selectată, valoarea coloanei Worker_ID de la tabelul lucrătorului trebuie să se potrivească cu valoarea coloanei Worker_ID din tabelul de atribuire. De fapt, conectăm două tabele de către lucrător_id. Toate liniile în care valorile acestor două coloane nu sunt egale, sunt excluse din tabelul de lucru. Exact același lucru se întâmplă atunci când efectuați o conexiune naturală a algebrei relaționale. (Cu toate acestea, o anumită diferență față de conexiunea naturală este încă acolo: limba SQL nu șterge automat coloana cuptorului lucrător_id). Conectarea completă a acestor două tabele cu o condiție suplimentară BLDG_ID \u003d 435 este reprezentată în fig. 1. Aplicarea comenzii SELECT va fi în cele din urmă, în cele din urmă, rezultatul următorului interogare:

Tipul de calificare

Tencuitor

Acoperișor

Un electrician

Smochin. 1. Conectarea tabelelor de lucrător și de atribuire

Acum vom arăta cum să atașați tabelul în SQL în sine.

Anchetă: Listează angajații, specificând numele managerilor lor.

Selectați A.worker_name, B.Worker_name

De la muncitor a, muncitor în

Unde b.worker_id \u003d a.supv_id

Fundația din acest exemplu creează două "copii" ale mesei lucrătorului, oferindu-le aliasuri A și V. pseudonim - acesta este un nume alternativ dat la masă. Apoi, copiile A și în masa lucrătorului sunt conectate prin comanda în care se bazează pe condiția egalității lucrătorului_ID în B și Supv_ID în A. Astfel, fiecare linie de la A este atașată la linia B, care conține informații despre managerul de rând A (Fig . 2).

Smochin. 2. Conectarea a două copii ale mesei lucrătorului

Alegerea din fiecare linie a două nume de angajați, vom obține lista dorită:

A.Names.name.

M. Faraday H. Columbus

K. Eleo Ryikover R. Gareret R. Garreret

P. Mayson P.Mayson Ryikover Ryikovener H.Columb X.Columba J. Barrister P. Maison

Pseudonim. Un nume alternativ dat la masă.

A.Worker_name reprezintă un angajat, un B.Worker_name reprezintă managerul. Vă rugăm să rețineți că unii muncitori sunt manageri înșiși, care urmează de la lucrător_id - supv_id executat în liniile lor.

În SQL, puteți aborda mai mult de două mese simultan:

Anchetă

Selectați Worker_name.

De la lucrător, cesiune, clădire

Unde worker.worker_id \u003d asigntion.worker_id și cesiune.bldg_id \u003d clădire.bldg_id și

Tip \u003d "Office"

Rezultat:

M. Faraday

Ryikover.

J. Barristres.

Rețineți că dacă numele coloanei (de exemplu, Worker_id sau BLDG_ID) este întâlnit mai mult decât într-o singură masă, atunci Pentru a evita incertitudinea, trebuie să specificăm numele tabelului sursă înainte de numele coloanei. Dar dacă numele coloanei se găsește numai într-o singură masă, cum ar fi tipul de exemplu, nu există incertitudine, deci nu este nevoie să specificați numele tabelului.

Comenzile SQL ale acestei interogări creează o masă din trei tabele baze de date relaționale. Primele două mese sunt conectate de Worker_ID, după care a treia masă este atașată la masă de pe BLDG_ID. Condiție

Tip \u003d "Office"

unde comenzile duc la excepția tuturor liniilor, cu excepția liniilor legate de clădirile de birouri. Aceasta îndeplinește cerințele cererii.

3. Subqueries

Subquery.Solicitați în interiorul interogării

Subquerosul poate fi plasat în comanda în care interogarea în cazul în care opțiunile din care se extinde comanda în care se extind. Luați în considerare un exemplu.

Anchetă: Care sunt lucrătorii de specialitate alocați cladirii 435?

Selectați sktll_type.

De la lucrător în cazul în care muncitorul_id

(Selectați Worker_id.

Unde bldg_id. = 435)

Subquery în acest exemplu

(Selectați Worker_id.

Unde bldg_id. = 435)

Se numește subquerție care conține cererea cerere externă sau cererea principală. Suborderile conduce la crearea următoarelor ID multiple (identificatori) de angajați:

ID-ul lucrătorului.

Cererea externă. Cererea principală în care sunt conținute toate subqueries.

Apoi este o mulțime de ID are un subqueriment într-o interogare externă. Din acest punct de vedere, o solicitare externă este executată utilizând un set creat de subquery. Cererea externă procesează fiecare șir al tabelului lucrător în conformitate cu situația în care este cazul. Dacă liniile Worker_ID se află în (in) un set creat de subquery, atunci sunt selectate linii de calificare și sunt afișate în tabelul rezultat:

Tipul de calificare

Tencuitor

Acoperișor

Un electrician

Este foarte important ca fraza de subminare selectată să conțină lucrător_id și doar lucrător_id. În caz contrar, fraza unde este o cerere externă, ceea ce înseamnă că lucrătorul_id se află într-o varietate de ID-uri de angajați, nu aș avea sens.

Rețineți că subqueria poate executa logic înainte ca cel puțin o linie să fie luată în considerare de către solicitarea principală. Într-un anumit sens, subqueria este independentă de cererea principală. Acesta poate fi efectuat ca o solicitare cu drepturi depline. Spunem că această subordieră nu este corelată cu cererea principală. După cum vom vedea în curând, subqueriele pot fi corelate.

Subquery non-corozițional.O substanță, a cărei valoare nu depinde de nici o cerere externă.

Să dăm un exemplu de subquary în interiorul subquery.

Anchetă: Listează angajații atribuite birourilor.

Din nou, luăm în considerare cererea prin care am studiat conexiunea.

Selectați Worker_mame.

Unde lucrător_id în.

(Selectați Worker_id.

Unde se află bldg_id.

Unde tip \u003d "Office"))

Rezultat:

M. Faraday

Ryikover.

J. Barristres.

Rețineți că nu este niciodată necesar să specificați numele tabelelor oriunde înaintea numelor coloanelor, deoarece fiecare substrat procesează unul și un singur tabel, astfel încât să nu apară incertitudini.

Execuția cererii are loc în ordinea din interior la exterior. Aceasta este, cea mai mare interogare internă (sau "cea mai mică") este efectuată mai întâi, apoi subquery conținând acest lucru este efectuată și apoi cererea externă.

Corelate subqueries. Toate subqueriele revizuite mai sus au fost independente de principalele interogări în care au fost utilizate. Prin independență, înțelegem că subqueriele pot fi efectuate de la sine ca o cerere completă. Acum ne vom întoarce la luarea în considerare a clasei subqueries, rezultatele executării care pot depinde de linia luată în considerare de cererea principală. Astfel de subquers sunt numite subquers corelate.

Subquerție corelată. Un subquery, rezultatul cărora depinde de linia luată în considerare de cererea principală.

Anchetă: Listează angajații a căror rate de orare sunt mai mari decât ratele managerilor lor.

Selectați Worker_name.

Unde a.hrly_rate\u003e

(Selectați b.hrly_rate.

Unde b.worker_id \u003d A.supv_id)

Rezultat:

Etapele logice ale acestei solicitări sunt după cum urmează:

1. Sistemul creează două copii ale mesei lucrătorului: o copie a și o copie a V. în conformitate cu modul în care le-am determinat, dar se referă la angajat, în - către manager.

2. Sistemul examinează apoi fiecare linie A. Acest șir este selectat dacă satisface situația în care este condiția. Această afecțiune înseamnă că șirul va fi selectat dacă valoarea HRLY_RATE în acesta este mai mare decât Hrly_rate generate de subquery.

3. SUBQUIRY Selectează valoarea Hrly_RAT din rândul în care lucrează, dintre care este egal cu șirul Supv_ID A, în prezent, cererea principală luată în considerare. Acesta este un manager Hrly_rate.

Rețineți că de la A.Hrly_rate poate fi comparată numai cu o valoare, subqueria trebuie să producă o singură valoare. Această valoare variază în funcție de cerul este luat în considerare. Astfel, subqueria se corelează cu cererea principală. Ne vom întâlni cu alte exemple de subqueriele corelate mai târziu, când studiem funcțiile încorporate.

Există și nu există operatori

Să presupunem că vrem să identificăm lucrătorii care nu sunt numiți să lucreze la o clădire. Cu un aspect superficial, se pare că o astfel de solicitare este ușor de realizat cu o simplă negare a versiunii afirmative a interogării. Să presupunem, de exemplu, că suntem interesați de clădirea cu BLDG_ID 435. Luați în considerare cererea:

Selectați Worker_id.

Unde bldg_id nu 435

Din păcate, aceasta este o soluție de formulare incorectă. Cererea ne va da pur și simplu angajații ID care lucrează la alte clădiri. Evident, unele dintre ele pot fi de asemenea numite în clădirea 435.

Într-o soluție formulată corect, se utilizează operatorul care nu există (nu există):

Selectați Worker_id.

Unde nu există.

În cazul în care atribuirea.worker_id \u003d worker.worker_id și

Rezultat:

Lucrător_id.

Există și nu există întotdeauna plasate înainte de subquery. Există ia valoarea "adevăr", dacă setul generat de subquery nu este gol. Dacă un set generat de o subquertie este gol, atunci există ia valoarea "minciună". Nu există operatorul funcționează în mod natural exact dimpotrivă. Este adevărat dacă rezultatul subquering este gol și fals altfel.

Există operator. Ia valoarea "adevăr", dacă setul rezultat nu este gol.

Nu există operator. Ia valoarea "adevărul", dacă setul rezultat este gol.

În acest exemplu, am folosit operatorul care nu există. Subvenția selectează toate astfel de rânduri ale tabelului de asignare în care Worker_ID are același înțeles ca și în linia luată în considerare de cererea principală, iar BLDG_ID este 435. Dacă acest set este gol, apoi șirul lucrătorului considerat de cererea principală Este selectat, deoarece aceasta înseamnă că aceasta înseamnă că înseamnă că înseamnă că înseamnă că înseamnă că acest angajat nu lucrează la clădirea 435.

În soluția dată de noi, folosind submarinul corelat. Dacă folosim în loc să nu existizeze operatorul, putem face subquary non-corozițional:

Selectați Worker_id.

În cazul în care lucrătorul_id nu este în

(Selectați Worker_id.

Unde bldg_id \u003d 435)

Această soluție este mai ușoară decât soluția cu operatorul care nu există. Există o întrebare naturală, de ce ar trebui să avem în general existența și nu există. Răspunsul este că nu există este singurul mijloc de rezolvare a cererilor care conțin cuvântul "fiecare" în această condiție. Astfel de solicitări sunt rezolvate în algebra relațională utilizând operațiunea de divizare și în calculul relațional - cu ajutorul unui cuantificator al universalității. Să dăm un exemplu de cerere, în condiția căreia există un cuvânt "fiecare":

Anchetă: Listează angajații atribuite fiecărei clădiri.

Această întrebare poate fi implementată în SQL utilizând Double Negial. Reformulăm cererea, inclusiv dubla negare în ea:

Anchetă: Listează astfel de lucrători pentru care nu Există o clădire pe care nu sunt numiți.

Am alocat dublă negare. Este clar că această solicitare este echivalentă logic cu cea precedentă.

Acum vrem să formulăm o soluție pe SQL. Pentru a simplifica înțelegerea deciziei finale, vom da mai întâi o decizie a problemei preliminare: sarcinile de identificare a tuturor clădirilor la care lucrătorul ipotetic "1234" nu Alocat.

(I) selectați bldg_id

Unde nu există.

Asignare.worker_id \u003d 1234)

Am marcat această interogare (i), așa cum ne vom referi mai târziu la acesta. Dacă nu există clădiri care să îndeplinească această solicitare, atunci lucrătorul din 1234 este atribuit fiecărei clădiri și, prin urmare, îndeplinește condițiile solicitării sursei. Pentru a obține o soluție de solicitare a sursei, trebuie să rezumăm interogarea (I) dintr-o anumită lucrare 1234 la variabila Worker_ID și să transformăm această solicitare modificată la subqueria unei interogări mai mari. Să dăm decizia:

(Ii) selectați Worker_ID

Unde nu există.

Unde nu există.

În cazul în care atribuirea.bldg_id \u003d clădire.bldg_id și

Atribuire.worker_id \u003d worker.worker_id)

Rezultat:

ID-ul lucrătorului.

Rețineți că subqueria, începând cu a patra șir de interogare (II), este identic cu interogarea (I), în care "1234" este înlocuită de Worker.Worker_ID. Interogarea (II) poate fi citită după cum urmează:

Selectați Worker_ID de la lucrător Dacă nu există nici o clădire la care lucrător_id nu este atribuit.

Aceasta corespunde condițiilor solicitării sursei.

Vedem că operatorul care nu există nu există pentru a formula acele cereri, în soluționarea din algebra relațională necesită funcționarea divizării și în calculul relațional - cuantificatorul universalității. Din punctul de vedere al ușurinței de utilizare, operatorul care nu există nu oferă avantaje speciale, adică în interogările SQL, în care nu depășește de două ori, nu este mai ușor să-i dați seama decât în \u200b\u200bsoluții de algebră relațională cu o operațiune de divizare sau soluții pentru calculul relațional cu un cuantificator al universalității. Pentru a crea structuri lingvistice care vă permit să rezolvați mai natural astfel de solicitări, vor fi necesare cercetări suplimentare.

Caracteristici încorporate

Luați în considerare problemele de acest tip:

Care sunt ratele maxime și minime pe oră? Care este numărul mediu de zile de angajați în clădirea 435? Care este numărul total de zile alocate pentru tencuiala pe clădire 312? Câte specialități diferite?

Pentru a răspunde la aceste întrebări, sunt necesare funcții statistice care să ia în considerare setul de rânduri de tabel și să elibereze o valoare. SQL are cinci astfel de funcții numite caracteristici încorporate sau funcții setate. Acestea sunt funcții sumate (sumă), AVG (media), numărătoare (număr), maxime (maxim) și min (minim).

Funcție încorporată (funcția SET). Funcția statistică care funcționează în mai multe șiruri de caractere: suma (suma), AVG (media), numărul (numărul), maximele (maxim), min (minim).

Anchetă: Care sunt ratele maxime și minime pe oră?

Selectați max (Hrly_rate), min (Hrly_rate)

Rezultat:17.40, 8.20

Funcțiile max și min funcționează cu o coloană de masă. Ele aleg valoarea maximă sau minimă, respectiv din această coloană. Formularea cererii noastre nu conține unde comenzi. Pentru majoritatea solicitărilor, acest lucru nu poate fi așa cum arată exemplul următor.

Anchetă:Care este numărul mediu de zile de angajați în clădirea 435?

Selectați AVG (Num_days)

Unde bldg_id \u003d 435

Rezultat: 12.33

Anchetă:Care este numărul total de zile alocate pentru tencuiala pe clădire 312?

Selectați suma (Num_days)

De la atribuire, lucrător

Unde lucrător.worker_id \u003d atribuire.worker_id și

Skill_type \u003d "Plastectiere" și

Rezultat: 27

Soluția utilizează conexiunea tabelelor de alocare și lucrător. Acest lucru este necesar, deoarece Skill_type este situat în tabelul lucrătorului, un BLDG_ID - în tabelul de atribuire.

Anchetă: Câte specialități diferite?

Selectați Count (Skill Skill_Type)

Rezultat: 4

Deoarece aceeași specialitate poate fi repetată în mai multe linii diferite, în această solicitare este necesar să se utilizeze cuvântul cheie distinct (diferit), astfel încât sistemul să nu contul de același tip de specialitate de mai multe ori. Operatorul distinct poate fi utilizat cu oricare dintre caracteristicile încorporate, deși, desigur, cu funcțiile de max și min este redundant.

Distinct.. Operatorul elimină liniile repetitive.

Funcțiile sumelor și AVG trebuie utilizate numai cu coloane numerice. Alte funcții pot fi utilizate cu date numerice și simbolice. Toate funcțiile, cu excepția numărului pot fi utilizate cu expresii calculate. De exemplu:

Anchetă: Care este salariul mediu săptămânal?

Selectați AVG (40 * Hrly_rate)

Rezultat: 509.14

Numărul se poate referi la întregul șir, și nu la semi-suportare :

Anchetă: Câte clădiri au nivele de calitate?

Selectați numărul (*)

De la clădire unde.

Rezultat: 3

Ca toate aceste exemple arată dacă comanda SELECT este o funcție încorporată, atunci nimic altceva nu poate sta în această comandă selectată. Singura excepție de la această regulă este asociată cu fraza grupului, pe care o considerăm acum.

Frazele Grupului de către și având

Gestionarea necesită adesea informații statistice despre fiecare grup într-o varietate de grupuri. De exemplu, luați în considerare următoarea interogare:

Anchetă:Pentru fiecare administrator, aflați rata maximă orară printre subordonații săi.

Pentru a rezolva această sarcină, trebuie să împărtășim angajații în grupuri în conformitate cu managerii lor. Apoi vom defini pariul maxim în interiorul fiecărui grup. În SQL, acest lucru se face în acest fel:

Grup de supv_id.

Rezultat:

Supv_idmax (rata Hrly)

La procesarea acestei interogări, sistemul sparge mai întâi rândurile de masă lucrător la următoarea regulă. Rândurile sunt plasate într-un singur grup dacă și numai atunci când se potrivesc cu supv_id. Fraza selectată este apoi aplicată fiecărui grup. Deoarece în acest grup este doar o valoare SUPV_ID, atunci nu există o incertitudine supv_id în grup. Pentru fiecare grup, expresia selectată afișează SUPV_ID și, de asemenea, calculează și afișează valoarea maximă (Hry_rate). Rezultatul este prezentat mai sus.

În comanda SELECT cu funcții încorporate, pot apărea numai acele coloane care sunt incluse în grup prin expresie. Rețineți că Supv_ID poate fi utilizat în comanda SELECT, deoarece intră în grupul cu fraza.

Grupul de expresii de către.. Aceasta înseamnă că liniile trebuie să fie rupte în grupuri cu valorile comune ale coloanei specificate (coloane).

Grupul prin fraza vă permite să efectuați anumite calcule complexe. De exemplu, este posibil să avem nevoie să aflăm valoarea medie a acestor rate maxime. Cu toate acestea, calculele cu funcții încorporate sunt limitate în sensul că nu este permisă utilizarea funcțiilor încorporate în alte funcții încorporate. Astfel, expresia tipului

AVG (max (Hrly_rate))

interzis. Implementarea acestei interogări va consta din două etape. În primul rând, trebuie să punem pariurile maxime în noua tabel, iar în a doua etapă, calculează media lor.

Cu comanda grupului prin comandă, puteți utiliza în cazul în care comanda:

Anchetă: Pentru fiecare tip de clădiri, aflați nivelul mediu de calitate între clădirile de stare 1.

Selectați tipul, AVG (QLTY_LEVEL)

Unde statutul \u003d 1

Rezultat:

TIPEVG (QLTY_LEVER)

Magazinul 1.

Clădirea rezidențială 3.

În cazul în care fraza este efectuată înainte de comanda grupului. Astfel, nici un grup nu poate conține un șir în care starea este diferită de 1. Stringurile de stare 1 sunt grupate cu valoarea tipului, iar apoi se utilizează fraza selectată pentru fiecare grup.

Fraza având.. Impune condiții pentru grupuri.

De asemenea, putem aplica condițiile grupurilor create de grupul de fraze. Acest lucru se face cu fraza care are. Să presupunem, de exemplu, că am decis să specificăm una dintre întrebările anterioare:

Anchetă: Pentru fiecare manager, care are mai mult de un subordonat, afla rata maximă orară printre subordonații săi.

Putem reflecta această condiție echipa corespunzătoare cu echipa de având:

Selectați SUPV_ID, MAX (Hrly_rate)

De la grupul de lucrători de către supv_id

Având numărul (*)\u003e 1

Rezultat:

Supv_id max (hrly_rate)

Diferența dintre frazele de unde și având este că locul unde se aplică rândurilor, în timp ce se aplică grupurilor.

Solicitarea poate conține, de asemenea, comanda unde și comanda având. În acest caz, primul lucru este realizat de fraza în cazul în care se efectuează înainte de rupere în grupuri. De exemplu, luați în considerare următoarea modificare a cererii prezentate anterior:

Anchetă: Pentru fiecare tip de clădiri, aflați nivelul mediu de calitate în rândul clădirilor de stare 1. să ia în considerare numai acele tipuri de clădiri al căror nivel de calitate maximă nu depășește 3.

Selectați tipul, AVG (QLTY_JLEVEL)

Unde statutul \u003d 1

Având max (QLty_level)<= 3

Rezultat:

Tip AVG (QLTY_LEVEL)

Magazinul 1.

Clădirea rezidențială 3.

Rețineți că pornind de la fraza din expresia este efectuată în ordine și apoi se utilizează fraza selectată. Deci, în cazul în care fraza este folosită pentru masa de construcție și toate liniile în care starea este diferită de 1 este îndepărtată. Liniile rămase sunt grupate după tip; Toate corzile cu aceeași valoare de tip sunt în același grup. Astfel, sunt create un grup neglijabil, unul pentru fiecare valoare de tip. Apoi, fraza care are o expresie este utilizată pentru fiecare grup, iar grupurile în care valoarea maximă a nivelului de calitate depășește 3 se elimină. În cele din urmă, fraza selectată este aplicată grupurilor rămase.

7. Caracteristici încorporate și subqueriene

Funcțiile încorporate pot fi utilizate numai în fraza selectată sau în echipa de avion. Cu toate acestea, fraza selectată care conține funcția încorporată poate face parte din subquer. Luați în considerare un exemplu de astfel de subquertie:

Anchetă: Cine de la angajați este rata orară peste medie?

Selectați Worker_name.

Unde hrly_rate\u003e

(Selectați AVG (Hrly_rate)

Rezultat:

H. Columbus.

Rețineți că subquery nu se corelează cu cererea principală. Subchertul produce exact o valoare - rata medie orare. Principala solicitare alege un angajat numai dacă rata sa este mai calculată.

Funcțiile încorporate pot fi de asemenea utilizate în cererile corelate:

Solicitare: Cine de la angajați este rata orară deasupra ratei medii pe oră printre subordonații aceluiași manager?

În acest caz, în loc să calculeze o rată medie orare pentru toți lucrătorii, trebuie să calculam rata medie a fiecărui grup de lucrători, depunând la același administrator. Mai mult, calculul nostru ar trebui reînnoit pentru fiecare angajat discutat de cererea principală:

Selectați A. Worker_Name.