Clauza GROUP BY(instrucțiunea SELECT) vă permite să grupați datele (rândurile) după valoarea unei coloane sau a mai multor coloane sau expresii. Rezultatul va fi un set de rânduri rezumative.
Fiecare coloană din lista de selectare trebuie să fie prezentă în clauza GROUP BY, singurele excepții sunt constantele și coloanele - operanzi ai funcțiilor agregate.
Un tabel poate fi grupat după orice combinație a coloanelor sale.
Funcții agregate sunt folosite pentru a obține o singură valoare totală dintr-un grup de rânduri. Toate funcțiile agregate efectuează calcule pe un singur argument, care poate fi fie o coloană, fie o expresie. Orice funcție agregată evaluează la o valoare constantă care este afișată într-o coloană separată în rezultat.
Funcțiile agregate sunt specificate în lista de coloane a unei instrucțiuni SELECT, care poate conține și o clauză GROUP BY. Dacă instrucțiunea SELECT nu conține o clauză GROUP BY, iar lista de coloane select conține cel puțin o funcție de agregare, atunci nu trebuie să conțină coloane simple. Pe de altă parte, o listă selectată de coloane poate conține nume de coloane care nu sunt argumente pentru funcția de agregare dacă acele coloane sunt folosite ca argumente pentru clauza GROUP BY.
Dacă interogarea conține o clauză WHERE, atunci funcțiile agregate calculează o valoare pentru rezultatele selecției.
Funcțiile agregate MIN și MAX se calculează cele mai mici și, respectiv, cele mai mari valori ale coloanei. Argumentele pot fi numere, șiruri și date. Toate valorile NULL sunt eliminate înainte de calcul (adică, nu sunt luate în considerare).
Funcția agregată SUM calculează suma totală a valorilor coloanei. Numai numerele pot fi argumente. Utilizarea parametrului DISTINCT elimină orice valori duplicate din coloană înainte de a aplica funcția SUM. De asemenea, eliminați toate valorile NULL înainte de a aplica această funcție de agregare.
Funcția agregată AVG returnează media tuturor valorilor dintr-o coloană. Argumentele pot fi, de asemenea, doar numere, iar toate valorile NULL sunt eliminate înainte de evaluare.
Funcția agregată COUNT are două forme diferite:
- COUNT (col_name) - numără numărul de valori din coloana col_name, valorile NULL nu sunt numărate
- COUNT (*) - numără numărul de rânduri din tabel, se iau în considerare și valorile NULL
Dacă interogarea folosește cuvântul cheie DISTINCT, toate valorile coloanelor duplicat sunt eliminate înainte ca funcția COUNT să fie utilizată.
COUNT_BIG funcție aceeași funcție ca COUNT. Singura diferență dintre cele două este tipul de rezultat pe care îl returnează: COUNT_BIG returnează întotdeauna valori BIGINT, în timp ce COUNT returnează valori de date INTEGER.
V AVÂND oferta definește o condiție care se aplică unui grup de rânduri. Are aceeași semnificație pentru grupurile de rânduri ca și clauza WHERE pentru conținutul tabelului corespunzător (UNDE se aplică înainte de grupare, HAVING după).
Următoarele subsecțiuni descriu alte clauze SELECT care pot fi utilizate în interogări, precum și funcții agregate și seturi de instrucțiuni. Permiteți-mi să vă reamintesc că până acum am acoperit utilizarea clauzei WHERE, iar în acest articol ne vom uita la clauzele GROUP BY, ORDER BY și HAVING și vom oferi câteva exemple de utilizare a acestor clauze în combinație cu funcțiile agregate. care sunt acceptate în Transact-SQL.
Clauza GROUP BY
Oferi A SE GRUPA CU grupează setul de rânduri selectat pentru a obține un set de rânduri rezumative pe baza valorilor uneia sau mai multor coloane sau expresii. Un caz de utilizare simplu pentru clauza GROUP BY este prezentat în exemplul de mai jos:
UTILIZAȚI SampleDb; SELECTARE Job FROM Works_On GROUP BY Job;
În acest exemplu, posturile de angajați sunt eșantionate și grupate.
În exemplul de mai sus, clauza GROUP BY creează un grup separat pentru toate valorile posibile (inclusiv NULL) pentru coloana Job.
Utilizarea coloanelor în clauza GROUP BY trebuie să îndeplinească anumite condiții. În special, fiecare coloană din lista de selecție a interogării trebuie să apară și în clauza GROUP BY. Această cerință nu se aplică constantelor și coloanelor care fac parte dintr-o funcție de agregare. (Funcțiile agregate sunt discutate în subsecțiunea următoare.) Acest lucru are sens deoarece numai coloanele din clauza GROUP BY au o valoare garantată per grup.
Un tabel poate fi grupat după orice combinație a coloanelor sale. Exemplul de mai jos demonstrează gruparea rândurilor din tabelul Works_on pe două coloane:
UTILIZAȚI SampleDb; SELECTARE ProjectNumber, Job FROM Works_On GROUP BY ProjectNumber, Job;
Rezultatul acestei interogări:
Pe baza rezultatelor interogării, puteți vedea că există nouă grupuri cu diferite combinații de număr și titlu de proiect. Secvența de nume de coloane din clauza GROUP BY nu trebuie să fie aceeași cu cea din lista de coloane SELECT.
Funcții agregate
Funcțiile agregate sunt folosite pentru a obține valorile sumei. Toate funcțiile agregate pot fi împărțite în următoarele categorii:
funcții agregate obișnuite;
funcții agregate statistice;
funcții agregate definite de utilizator;
funcţii agregate analitice.
Aici ne vom uita la primele trei tipuri de funcții agregate.
Funcții agregate obișnuite
Transact-SQL acceptă următoarele șase funcții agregate: MIN, MAX, SUMĂ, AVG, NUMARA, COUNT_BIG.
Toate funcțiile agregate efectuează calcule pe un singur argument, care poate fi fie o coloană, fie o expresie. (Singura excepție este a doua formă a două funcții, COUNT și COUNT_BIG, și anume COUNT (*) și, respectiv, COUNT_BIG (*).) Orice funcție agregată evaluează la o valoare constantă care apare într-o coloană separată de rezultat.
Funcțiile agregate sunt specificate în lista de coloane a unei instrucțiuni SELECT, care poate conține și o clauză GROUP BY. Dacă instrucțiunea SELECT nu include o clauză GROUP BY, iar lista de coloane de selecție conține cel puțin o funcție de agregare, atunci nu trebuie să conțină coloane simple (altele decât coloanele utilizate ca argumente pentru funcția de agregare). Prin urmare, codul din exemplul de mai jos este incorect:
UTILIZAȚI SampleDb; SELECTAȚI Nume, MIN (Id) FROM Angajat;
Aici, coloana LastName din tabelul Employee nu ar trebui să fie în lista de coloane select, deoarece nu este un argument pentru funcția de agregare. Pe de altă parte, o listă selectată de coloane poate conține nume de coloane care nu sunt argumente pentru funcția de agregare dacă acele coloane sunt folosite ca argumente pentru clauza GROUP BY.
Un argument de funcție agregată poate fi precedat de unul dintre cele două cuvinte cheie posibile:
TOATEIndică faptul că calculele sunt efectuate pentru toate valorile din coloană. Aceasta este valoarea implicită.
DISTINCTSpecifică faptul că numai valorile unice ale coloanei sunt utilizate pentru calcule.
Funcțiile agregate MIN și MAX
Funcțiile agregate MIN și MAX calculează cele mai mici și, respectiv, cele mai mari valori dintr-o coloană. Dacă interogarea conține o clauză WHERE, funcțiile MIN și MAX returnează cele mai mici și cele mai mari valori ale rândului care corespund condițiilor specificate. Exemplul de mai jos demonstrează utilizarea funcției de agregare MIN:
UTILIZAȚI SampleDb; - Returnează 2581 SELECT MIN (Id) AS „Valoarea minimă a ID” FROM Employee;
Rezultatul returnat în exemplul de mai sus nu este foarte informativ. De exemplu, numele de familie al angajatului care deține acest număr este necunoscut. Dar nu puteți obține acest nume de familie în mod obișnuit, deoarece, așa cum am menționat mai devreme, nu puteți specifica în mod explicit coloana LastName. Pentru a obține numele de familie al acestui angajat împreună cu cel mai mic număr de personal al angajatului, se folosește o subinterogare. Exemplul de mai jos demonstrează utilizarea unei astfel de subinterogări, în care subinterogarea conține instrucțiunea SELECT din exemplul anterior:
Rezultatul executării interogării:
Utilizarea funcției de agregare MAX este prezentată în exemplul de mai jos:
Funcțiile MIN și MAX pot accepta șiruri și date ca argumente. În cazul unui argument șir, valorile sunt comparate folosind ordinea reală de sortare. Pentru toate argumentele de date temporare de tip dată, cea mai mică valoare a coloanei este data cea mai veche, iar valoarea cea mai mare este cea mai recentă.
Cuvântul cheie DISTINCT poate fi folosit cu funcțiile MIN și MAX. Toate valorile NULL sunt eliminate din coloanele lor de argument înainte de a utiliza funcțiile agregate MIN și MAX.
Funcția agregată SUM
Agregat Funcția SUM calculează suma totală a valorilor coloanei. Argumentul acestei funcții agregate trebuie să fie întotdeauna de tip de date numerice. Utilizarea funcției de agregare SUM este prezentată în exemplul de mai jos:
UTILIZAȚI SampleDb; SELECT SUM (Buget) „Buget total” FROM Proiect;
Acest exemplu calculează bugetele totale pentru toate proiectele. Rezultatul executării interogării:
În acest exemplu, funcția agregată grupează toate valorile bugetului proiectului și determină totalul acestora. Din acest motiv, interogarea conține o funcție implicită de grupare (ca toate interogările similare). Funcția de grupare implicită din exemplul de mai sus poate fi specificată în mod explicit, așa cum se arată în exemplul de mai jos:
UTILIZAȚI SampleDb; SELECT SUM (Buget) „Bugetul total” FROM Project GROUP BY ();
Utilizarea parametrului DISTINCT elimină orice valori duplicate din coloană înainte de a aplica funcția SUM. De asemenea, eliminați toate valorile NULL înainte de a aplica această funcție de agregare.
Funcția agregată AVG
Agregat Funcția AVG returnează media aritmetică a tuturor valorilor dintr-o coloană. Argumentul acestei funcții agregate trebuie să fie întotdeauna de tip de date numerice. Toate valorile NULL sunt eliminate din argument înainte ca funcția AVG să fie aplicată.
Utilizarea funcției de agregare AVG este prezentată în exemplul de mai jos:
UTILIZAȚI SampleDb; - Returnează 133833 SELECT AVG (Buget) „Buget mediu pentru un proiect” FROM Proiect;
Aici se calculează media aritmetică a bugetului pentru toate bugetele.
Agregați funcțiile COUNT și COUNT_BIG
Agregat Funcția COUNT are două forme diferite:
COUNT (nume_coloană) COUNT (*)
Prima formă a funcției numără numărul de valori din coloana col_name. Dacă interogarea folosește cuvântul cheie DISTINCT, toate valorile coloanelor duplicat sunt eliminate înainte ca funcția COUNT să fie utilizată. Această formă a funcției COUNT nu ia în considerare valorile NULL atunci când se numără numărul de valori ale coloanei.
Utilizarea primei forme a funcției de agregare COUNT este prezentată în exemplul de mai jos:
UTILIZAȚI SampleDb; SELECTARE ProjectNumber, COUNT (DISTINCT Job) "Jobs in Project" FROM Works_on GROUP BY ProjectNumber;
Aici este numărat numărul de poziții diferite pentru fiecare proiect. Rezultatul acestei interogări:
După cum puteți vedea din interogarea din exemplu, valorile NULL nu au fost luate în considerare de funcția COUNT. (Suma tuturor valorilor din coloana jobului este 7, nu 11 așa cum ar trebui să fie.)
A doua formă a funcției COUNT, adică. funcția COUNT (*) numără numărul de rânduri dintr-un tabel. Și dacă instrucțiunea SELECT a interogării cu funcția COUNT (*) conține o clauză WHERE cu o condiție, funcția returnează numărul de rânduri care satisfac condiția specificată. Spre deosebire de prima versiune a funcției COUNT, a doua formă nu ignoră valorile NULL, deoarece această funcție operează pe șiruri, nu pe coloane. Exemplul de mai jos demonstrează utilizarea funcției COUNT (*):
UTILIZAȚI SampleDb; SELECTARE Job CA „Tipul locului de muncă”, COUNT (*) „Este nevoie de muncitori” FROM Works_on GROUP BY Job;
Aici este numărat numărul de postări din toate proiectele. Rezultatul executării interogării:
COUNT_BIG funcție aceeași funcție ca COUNT. Singura diferență dintre cele două este tipul de rezultat pe care îl returnează: COUNT_BIG returnează întotdeauna valori BIGINT, în timp ce COUNT returnează valori de date INTEGER.
Funcții agregate statistice
Următoarele funcții alcătuiesc grupul de funcții agregate statistice:
VARCalculează varianța statistică a tuturor valorilor dintr-o coloană sau expresie.
VARPCalculează varianța statistică a populației tuturor valorilor dintr-o coloană sau expresie.
STDEVCalculează abaterea standard (care este calculată ca rădăcină pătrată a varianței corespunzătoare) a tuturor valorilor dintr-o coloană sau expresie.
STDEVPCalculează abaterea standard a colecției tuturor valorilor dintr-o coloană sau expresie.
Funcții agregate definite de utilizator
Motorul de baze de date suportă, de asemenea, implementarea funcțiilor definite de utilizator. Această capacitate permite utilizatorilor să suplimenteze funcțiile agregate ale sistemului cu funcții pe care le pot implementa și instala ei înșiși. Aceste funcții reprezintă o clasă specială de funcții definite de utilizator și sunt discutate în detaliu mai târziu.
clauza HAVING
Într-o propoziție AVÂND definește o condiție care se aplică unui grup de rânduri. Astfel, această clauză are aceeași semnificație pentru grupurile de rânduri ca și clauza WHERE pentru conținutul tabelului corespunzător. Sintaxa pentru clauza HAVING este următoarea:
AVÂND stare
Aici parametrul condiție reprezintă o condiție și conține funcții sau constante agregate.
Utilizarea clauzei HAVING împreună cu funcția de agregare COUNT (*) este prezentată în exemplul de mai jos:
UTILIZAȚI SampleDb; - Returnează „p3” SELECT ProjectNumber FROM Works_on GROUP BY ProjectNumber HAVING COUNT (*)
În acest exemplu, sistemul folosește clauza GROUP BY pentru a grupa toate rândurile după valorile din coloana ProjectNumber. Numărul de rânduri din fiecare grup este apoi numărat și sunt selectate grupuri care conțin mai puțin de patru rânduri (trei sau mai puține).
Clauza HAVING poate fi folosită și fără funcții agregate, așa cum se arată în exemplul de mai jos:
UTILIZAȚI SampleDb; - Va returna „Consultant” SELECTARE Job FROM Works_on GROUP BY Job HAVING Job LIKE „К%”;
Acest exemplu grupează rândurile tabelului Works_on după titlul postului și elimină joburile care nu încep cu litera „K”.
Clauza HAVING poate fi folosită și fără clauza GROUP BY, deși aceasta nu este o practică obișnuită. În acest caz, toate rândurile tabelului sunt returnate în același grup.
clauza ORDER BY
Oferi COMANDA PENTRU determină ordinea de sortare a rândurilor din setul de rezultate returnat de interogare. Această clauză are următoarea sintaxă:
Ordinea de sortare este specificată în parametrul col_name. Col_number este un indicator alternativ al ordinii de sortare care identifică coloanele în ordinea în care apar în lista de selectare a instrucțiunii SELECT (1 este prima coloană, 2 este a doua coloană și așa mai departe). Parametrul ASC definește o ordine crescătoare și parametrul DESC- în aval. Valoarea implicită este ASC.
Numele coloanelor din clauza ORDER BY nu trebuie să fie neapărat în lista de coloane selectate. Dar acest lucru nu se aplică interogărilor precum SELECT DISTINCT, deoarece în astfel de interogări, numele coloanelor specificate în clauza ORDER BY trebuie să apară și în lista coloanelor selectate. În plus, această clauză nu poate conține nume de coloane din tabele care nu sunt specificate în clauza FROM.
După cum puteți vedea din sintaxa clauzei ORDER BY, sortarea setului de rezultate se poate face pe mai multe coloane. Această sortare este prezentată în exemplul de mai jos:
Acest exemplu selectează numere de departamente și nume de familie și prenume ale angajaților pentru angajații ale căror numere de personal sunt mai mici de 20.000, precum și sortate după nume și prenume. Rezultatul acestei interogări:
Coloanele din clauza ORDER BY pot fi specificate nu după numele lor, ci în ordine în lista de selecție. În consecință, propoziția din exemplul de mai sus poate fi rescrisă după cum urmează:
Această modalitate alternativă de a specifica coloanele după poziția lor în loc de nume este utilizată dacă criteriul de ordonare conține o funcție de agregare. (O altă modalitate este să folosiți numele coloanelor, care apar apoi în clauza ORDER BY.) Cu toate acestea, în clauza ORDER BY, se recomandă să specificați coloanele după numele lor, mai degrabă decât după numere, pentru a facilita actualizarea interogării. dacă trebuie să adăugați sau să eliminați coloane din lista de selectare. Specificarea coloanelor din clauza ORDER BY după numerele lor este prezentată în exemplul de mai jos:
UTILIZAȚI SampleDb; SELECTARE ProjectNumber, COUNT (*) „Număr de angajați” FROM Works_on GROUP BY ProjectNumber ORDER BY 2 DESC;
Aici, pentru fiecare proiect, se selectează numărul proiectului și numărul de angajați care participă la acesta, ordonând rezultatul în ordine descrescătoare după numărul de angajați.
Transact-SQL plasează valorile NULL în partea de sus a listei când sunt sortate în ordine crescătoare, iar valorile NULL la sfârșitul listei când sunt sortate în ordine descrescătoare.
Utilizarea clauzei ORDER BY pentru paginarea rezultatelor
Afișarea rezultatelor interogării pe pagina curentă poate fi fie implementată într-o aplicație personalizată, fie puteți solicita serverului de baze de date să facă acest lucru. În primul caz, toate rândurile bazei de date sunt trimise către aplicație, a cărei sarcină este să selecteze rândurile necesare și să le afișeze. În al doilea caz, numai rândurile necesare pentru pagina curentă sunt preluate și afișate din partea serverului. După cum v-ați putea aștepta, crearea de pagini pe partea serverului oferă de obicei cea mai bună performanță, deoarece doar liniile necesare pentru afișare sunt trimise clientului.
Pentru a sprijini crearea paginilor pe partea serverului, SQL Server 2012 introduce două noi clauze SELECT: OFFSET și FETCH. Aplicarea acestor două propoziții este demonstrată în exemplul de mai jos. Aici, din baza de date AdventureWorks2012 (pe care o găsiți în sursă), extrage ID-ul companiei, titlul postului și ziua de naștere a tuturor angajatelor, sortate după titlul postului în ordine crescătoare. Setul de rânduri rezultat este împărțit în pagini de 10 rânduri și este afișată o a treia pagină:
Într-o propoziție DECALAJ specifică numărul de linii de rezultat care trebuie sărit în rezultatul afișat. Această sumă este calculată după ce rândurile sunt sortate cu clauza ORDER BY. Într-o propoziție FETCH NEXT specifică numărul de WHERE și rândurile sortate de returnat. Parametrul acestei clauze poate fi o constantă, o expresie sau rezultatul unei alte interogări. Clauza FETCH NEXT este similară cu clauza FETCH MAINTI.
Scopul principal atunci când se creează pagini pe partea serverului este acela de a putea implementa formulare comune de pagină folosind variabile. Această sarcină poate fi realizată prin pachetul SQL Server.
Declarația SELECT și proprietatea IDENTITY
proprietatea IDENTITATE vă permite să determinați valorile pentru o anumită coloană a tabelului sub forma unui contor incremental automat. Coloanele cu un tip de date numerice, cum ar fi TINYINT, SMALLINT, INT și BIGINT pot avea această proprietate. Pentru o astfel de coloană de tabel, Motorul de bază de date generează automat valori secvențiale începând cu valoarea de pornire specificată. Prin urmare, proprietatea IDENTITATE poate fi utilizată pentru a genera valori numerice cu o singură cifră pentru coloana selectată.
Un tabel poate conține o singură coloană cu proprietatea IDENTITY. Proprietarul tabelului are capacitatea de a specifica valoarea inițială și creșterea, așa cum se arată în exemplul de mai jos:
UTILIZAȚI SampleDb; CREATE TABLE Product (Id INT IDENTITY (10000, 1) NOT NULL, Name NVARCHAR (30) NOT NULL, Price MONEY) INSERT INTO Product (Nume, Price) VALUES ("Produs1", 10), ("Product2", 15) , ("Produs3", 8), ("Produs4", 15), ("Produs5", 40); - Va returna 10004 SELECT IDENTITYCOL FROM Product WHERE Nume = "Product5"; - Analog cu declarația anterioară SELECT $ identity FROM Product WHERE Nume = "Product5";
Acest exemplu creează mai întâi un tabel Product care conține o coloană Id cu o proprietate IDENTITY. Valorile din coloana Id sunt create automat de sistem, începând de la 10.000 și crescând într-un pas pentru fiecare valoare ulterioară: 10.000, 10.001, 10.002 etc.
Mai multe funcții de sistem și variabile sunt asociate cu proprietatea IDENTITY. De exemplu, codul de exemplu folosește variabila de sistem $ identitate... După cum puteți vedea din rezultatele rulării acestui cod, această variabilă este referită automat la proprietatea IDENTITY. În schimb, puteți utiliza și funcția de sistem IDENTITYCOL.
Valoarea inițială și incrementul coloanei cu proprietatea IDENTITATE pot fi găsite folosind funcțiile IDENT_SEEDși IDENT_INCR respectiv. Aceste funcții se aplică după cum urmează:
UTILIZAȚI SampleDb; SELECT IDENT_SEED ("Produs"), IDENT_INCR ("Produs")
După cum sa menționat, valorile IDENTITATE sunt setate automat de către sistem. Dar utilizatorul își poate specifica în mod explicit valorile pentru anumite șiruri de caractere prin alocarea parametrului IDENTITY_INSERT valoarea ON înainte de a introduce valoarea explicită:
SETARE IDENTITATE INSERT numele tabelului
Deoarece parametrul IDENTITY_INSERT poate fi utilizat pentru a seta orice valoare, inclusiv valori duplicate, pe o coloană cu proprietatea IDENTITY, proprietatea IDENTITY de obicei nu impune unicitatea valorilor coloanei. Prin urmare, constrângerile UNIQUE sau PRIMARY KEY ar trebui aplicate pentru a impune unicitatea valorilor coloanei.
Când inserați valori într-un tabel după ce ați activat IDENTITY_INSERT, sistemul creează următoarea valoare în coloana IDENTITY, incrementând cea mai mare valoare curentă din acea coloană.
Instrucțiunea CREATE SEQUENCE
Utilizarea proprietății IDENTITY are câteva dezavantaje semnificative, dintre care cele mai semnificative sunt:
proprietatea este limitată la tabelul specificat;
noua valoare a coloanei nu poate fi obtinuta in nici un alt mod, decat prin aplicarea acesteia;
proprietatea IDENTITY poate fi specificată numai la crearea unei coloane.
Din aceste motive, SQL Server 2012 introduce secvențe care au aceeași semantică ca proprietatea IDENTITY, dar fără dezavantajele enumerate anterior. În acest context, o secvență se referă la funcționalitatea unei baze de date care vă permite să specificați valori de contor pentru diferite obiecte de bază de date, cum ar fi coloane și variabile.
Secvențele sunt create folosind instrucțiunea CREAȚI SECVENȚA... Instrucțiunea CREATE SEQUENCE este definită în standardul SQL și este suportată de alte sisteme de baze de date relaționale, cum ar fi IBM DB2 și Oracle.
Exemplul de mai jos arată cum să creați o secvență în SQL Server:
UTILIZAȚI SampleDb; CREATE SECVENCE dbo.Sequence1 AS INT START CU 1 INCREMENTARE CU 5 MINVALUE 1 MAXVALUE 256 CYCLE;
În exemplul de mai sus, valorile Sequence1 sunt generate automat de sistem, începând de la o valoare de 1 și în trepte de 5 pentru fiecare valoare succesivă. Astfel, în clauza START este indicată valoarea inițială, iar în Oferta INCREMENTARE- Etapa. (Pasul poate fi fie pozitiv, fie negativ.)
În următoarele două propoziții opționale MINVALUEși MAXVALUE specifică valoarea minimă și maximă a obiectului secvență. (Rețineți că MINVALUE trebuie să fie mai mic sau egal cu valoarea inițială, iar MAXVALUE nu poate fi mai mare decât limita superioară a tipului de date specificat pentru secvență.) Într-o propoziție CICLU indică faptul că secvența se repetă de la început atunci când valoarea maximă (sau minimă pentru o secvență cu pas negativ) este depășită. În mod implicit, această clauză este setată la NO CYCLE, ceea ce înseamnă că depășirea valorii maxime sau minime a secvenței ridică o excepție.
Caracteristica principală a secvențelor este independența lor față de tabele, adică. ele pot fi utilizate cu orice obiect de bază de date, cum ar fi coloanele de tabel sau variabile. (Această proprietate are un efect pozitiv asupra stocării și, prin urmare, asupra performanței. O anumită secvență nu trebuie să fie stocată; doar ultima ei valoare este stocată.)
Sunt create noi valori ale secvenței cu NEXT VALUE FOR expresii, a cărui aplicare este prezentată în exemplul de mai jos:
UTILIZAȚI SampleDb; - Returnează 1 SELECT NEXT VALUE FOR dbo.sequence1; - Returnează 6 (pasul următor) SELECT NEXT VALUE FOR dbo.sequence1;
Puteți folosi expresia NEXT VALUE FOR pentru a atribui rezultatul unei secvențe unei variabile sau unei celule de coloană. Exemplul de mai jos ilustrează utilizarea acestei expresii pentru a atribui rezultate unei coloane:
UTILIZAȚI SampleDb; CREATE TABLE Product (Id INT NOT NULL, Name NVARCHAR (30) NOT NULL, Price MONEY) INSERT INTO Product VALUES (NEXT VALUE FOR dbo.sequence1, "Product1", 10); INSERT INTO Product VALUES (URMĂTORUL VALOARE PENTRU dbo.sequence1, „Product2”, 15); -...
Exemplul de mai sus creează mai întâi un tabel numit Produs cu patru coloane. Apoi, două instrucțiuni INSERT inserează două rânduri în acest tabel. Primele două celule ale primei coloane vor fi 11 și 16.
Exemplul de mai jos demonstrează utilizarea vizualizării catalogului sys.secvente pentru a vizualiza valoarea curentă a unei secvențe fără a o folosi:
De obicei, expresia NEXT VALUE FOR este utilizată într-o instrucțiune INSERT pentru a instrui sistemul să insereze valorile generate. Această expresie poate fi folosită și ca parte a unei interogări pe mai multe linii folosind clauza OVER.
Pentru a modifica o proprietate a unei secvențe existente, aplicați Instrucțiunea ALTER SEQUENCE... Una dintre cele mai importante utilizări pentru această declarație este opțiunea RESTART WITH, care resetează secvența specificată. Exemplul de mai jos demonstrează utilizarea instrucțiunii ALTER SEQUENCE pentru a reseta aproape toate proprietățile Sequence1:
UTILIZAȚI SampleDb; ALTER SEQUENCE dbo.sequence1 RESTART CU 100 INCREMENTARE CU 50 MINVALUE 50 MAXVALUE 200 FĂRĂ CICLU;
Secvența este ștearsă folosind instrucțiunea SECVENȚA DE CĂDERARE.
Setați operatori
Pe lângă operatorii discutați mai devreme, Transact-SQL acceptă alți trei operatori de set: UNION, INTERSECT și EXCEPT.
operator UNION
operator UNION combină rezultatele a două sau mai multe interogări într-un singur set de rezultate care include toate rândurile care aparțin tuturor interogărilor din uniune. În consecință, rezultatul unirii celor două tabele este un tabel nou care conține toate rândurile din unul sau ambele tabele originale.
Forma generală a operatorului UNION arată astfel:
select_1 UNION select_2 (select_3])...
Opțiunile select_1, select_2, ... sunt instrucțiuni SELECT care creează unirea. Dacă se utilizează parametrul ALL, sunt afișate toate rândurile, inclusiv duplicatele. În instrucțiunea UNION, parametrul ALL are aceeași semnificație ca și în SELECT, dar cu o diferență: acesta este implicit pentru un SELECT, dar trebuie specificat explicit pentru UNION.
În forma sa originală, SampleDb nu este potrivit pentru demonstrarea utilizării operatorului UNION. Prin urmare, această secțiune creează un nou tabel EmployeeEnh care este identic cu tabelul Employee existent, dar are o coloană suplimentară Oraș. Această coloană indică locul de reședință al angajaților.
Crearea tabelului EmployeeEnh ne oferă o oportunitate de a demonstra utilizarea clauzei. ÎNîn instrucțiunea SELECT. Instrucțiunea SELECT INTO efectuează două operații. Mai întâi, este creat un nou tabel cu coloanele listate în lista SELECT. Apoi, rândurile din tabelul original sunt inserate în noul tabel. Numele noului tabel este specificat în clauza INTO, iar numele tabelului sursă este specificat în clauza FROM.
Exemplul de mai jos arată crearea tabelului EmployeeEnh din tabelul Employee:
UTILIZAȚI SampleDb; SELECT * INTO EmployeeEnh FROM Employee; ALTER TABLE EmployeeEnh ADD City NCHAR (40) NULL;
În acest exemplu, instrucțiunea SELECT INTO creează tabelul EmployeeEnh, inserează toate rândurile din tabelul sursă Employee în acesta, iar apoi instrucțiunea ALTER TABLE adaugă coloana City la noul tabel. Dar coloana Oraș adăugată nu conține nicio valoare. Valorile pot fi inserate în această coloană prin Management Studio sau folosind următorul cod:
UTILIZAȚI SampleDb; UPDATE EmployeeEnh SET City = "Kazan" WHERE Id = 2581; UPDATE EmployeeEnh SET City = „Moscova” WHERE Id = 9031; UPDATE EmployeeEnh SET City = "Ekaterinburg" WHERE Id = 10102; UPDATE EmployeeEnh SET City = "Sankt Petersburg" WHERE Id = 18316; UPDATE EmployeeEnh SET City = "Krasnodar" WHERE Id = 25348; UPDATE EmployeeEnh SET City = "Kazan" WHERE Id = 28559; UPDATE EmployeeEnh SET City = "Perm" WHERE Id = 29346;
Acum suntem gata să demonstrăm utilizarea declarației UNION. Exemplul de mai jos arată o interogare pentru a crea o îmbinare între tabelele EmployeeEnh și Department folosind această declarație:
UTILIZAȚI SampleDb; SELECTAȚI Orașul AS „ORAS” FROM EmployeeEnh UNION SELECTAȚI Locația FROM Departament;
Rezultatul acestei interogări:
Numai tabelele compatibile pot fi alăturate folosind declarația UNION. Prin tabele compatibile, înțelegem că ambele liste de coloane din selecție trebuie să conțină același număr de coloane, iar coloanele corespunzătoare trebuie să aibă tipuri de date compatibile. (În ceea ce privește compatibilitatea, tipurile de date INT și SMALLINT nu sunt compatibile.)
Rezultatul concatenării poate fi ordonat numai folosind clauza ORDER BY din ultima instrucțiune SELECT, așa cum se arată în exemplul de mai jos. Clauzele GROUP BY și HAVING pot fi utilizate cu instrucțiuni individuale SELECT, dar nu în cadrul uniunii în sine.
Interogarea din acest exemplu selectează angajați care fie lucrează în departamentul d1, fie care au început să lucreze la un proiect înainte de 1 ianuarie 2008.
Operatorul UNION acceptă opțiunea ALL. Această opțiune nu elimină duplicatele din setul de rezultate. Un operator OR poate fi utilizat în locul operatorului UNION dacă toate instrucțiunile SELECT unite de unul sau mai mulți operatori UNION se referă la același tabel. În acest caz, setul de instrucțiuni SELECT este înlocuit cu o singură instrucțiune SELECT cu un set de instrucțiuni OR.
Operatorii INTERSECT și EXCEPT
Alți doi operatori pentru lucrul cu seturi, INTERSECTși CU EXCEPTIA, definiți intersecția și respectiv diferența. Sub intersecție, în acest context, există un set de rânduri care aparțin ambelor tabele. Și diferența dintre două tabele este definită ca toate valorile care aparțin primului tabel și nu sunt prezente în al doilea. Exemplul de mai jos demonstrează utilizarea operatorului INTERSECT:
Transact-SQL nu acceptă utilizarea parametrului ALL cu operatorii INTERSECT sau EXCEPT. Utilizarea operatorului EXCEPT este prezentată în exemplul de mai jos:
Amintiți-vă că acești trei operatori de set au o prioritate de execuție diferită: INTERSECT are cea mai mare prioritate, urmată de EXCEPT și UNION are cea mai mică prioritate. Neacordarea atenției priorității execuției atunci când se utilizează mai mulți operatori de set diferiți poate duce la rezultate neașteptate.
expresii CASE
În domeniul programării aplicațiilor de baze de date, uneori este necesară modificarea prezentării datelor. De exemplu, oamenii pot fi subdivizați în funcție de clasa lor socială, folosind valorile 1, 2 și 3, desemnând bărbați, femei și, respectiv, copii. Această tehnică de programare poate reduce timpul necesar implementării programului. expresie CASE Transact-SQL vă permite să implementați cu ușurință acest tip de codificare.
Spre deosebire de majoritatea limbajelor de programare, CASE nu este o declarație, ci o expresie. Prin urmare, o expresie CASE poate fi folosită aproape oriunde Transact-SQL permite utilizarea expresiilor. Expresia CASE are două forme:
expresie CASE simplă;
expresia de căutare CASE.
Sintaxa pentru o expresie CASE simplă este următoarea:
O instrucțiune cu o expresie CASE simplă caută mai întâi în lista tuturor expresiilor din clauza CÂND prima expresie care se potrivește cu expresia_1 și apoi execută cea corespunzătoare THEN clauza... Dacă nu există o expresie potrivită în lista CÂND, atunci clauza ELSE.
Sintaxa pentru o expresie de căutare CASE este următoarea:
În acest caz, caută prima condiție de calificare și apoi execută clauza THEN corespunzătoare. Dacă niciuna dintre condiții nu îndeplinește cerințele, se execută clauza ELSE. Utilizarea expresiei de căutare CASE este prezentată în exemplul de mai jos:
UTILIZAȚI SampleDb; SELECTează NumeProiect, CAZ CAND Buget> 0 ȘI Buget 100000 ȘI Buget 150000 ȘI Buget
Rezultatul acestei interogări:
Acest exemplu ponderează bugetele tuturor proiectelor și afișează ponderile calculate împreună cu numele proiectelor corespunzătoare.
Exemplul de mai jos arată o altă modalitate de a utiliza o expresie CASE în care clauza WHEN conține subinterogări care fac parte din expresie:
UTILIZAȚI SampleDb; SELECTAȚI Numele Proiectului, CAZUL CÂND p1.Buget (SELECTARE AVG (p2.Buget) FROM Proiectul p2) THEN „peste medie” END „Categoria bugetară” FROM Proiectul p1;
Rezultatul acestei interogări este următorul:
Lecția va acoperi subiectul sql redenumirea unei coloane (câmpuri) folosind cuvântul funcției AS; a acoperit, de asemenea, subiectul funcțiilor agregate în sql. Vor fi analizate exemple specifice de cereri
Puteți redenumi numele coloanelor în interogări. Acest lucru face rezultatele mai lizibile.
În SQL, redenumirea câmpurilor este asociată cu utilizarea lui cuvânt cheie AS care este folosit pentru a redenumi numele câmpurilor din seturile de rezultate
Sintaxă:
SELECTAȚI<имя поля>LA FEL DE<псевдоним>DIN...
Să ne uităm la un exemplu de redenumire în SQL:
Exemplu DB „Institut”: Afișați numele profesorilor și salariile acestora, pentru acei profesori ale căror salarii sunt sub 15000, redenumiți câmpul zarplata în "Salariu mic"
✍ Soluție:
Redenumirea coloanelor în SQL este adesea necesară la calcularea valorilor asociate cu mai multe câmpuri Mese. Să luăm în considerare un exemplu:
Exemplu DB „Institut”: Din tabelul profesorilor, scoateți câmpul de nume și calculați suma salariului și a bonusului, denumind câmpul „Salariu_ premiu”
✍ Soluție:
| 1 2 | SELECT numele, (zarplata + premia) AS zarplata_premia FROM profesori; |
SELECT numele, (zarplata + premia) AS zarplata_premia FROM profesori;
Rezultat:
Funcții agregate în SQL
Funcțiile agregate în sql sunt folosite pentru a obține totaluri și a calcula expresii:
Toate funcțiile agregate returnează o singură valoare.
Funcțiile COUNT, MIN și MAX se aplică oricărui tip de date.
Funcțiile SUM și AVG sunt utilizate numai pentru câmpurile numerice.
Există o diferență între funcțiile COUNT (*) și COUNT (): a doua nu ia în considerare valorile NULL la numărare.
Important: atunci când lucrați cu funcții agregate în SQL, se folosește un cuvânt special LA FEL DE
Exemplu DB „Institut”: Obțineți valoarea celui mai mare salariu dintre profesori, afișați rezultatul ca „Max_zp”
✍ Soluție:
| SELECT MAX (zarplata) AS max_zp FROM profesori; |
SELECT MAX (zarplata) AS max_zp FROM profesori;
Rezultate:
Să luăm în considerare un exemplu mai complex de utilizare a funcțiilor agregate în sql.
✍ Soluție:
Clauza GROUP BY în SQL
Clauza group by din sql este de obicei folosită împreună cu funcțiile de agregare.
Funcțiile agregate sunt efectuate pe toate rândurile de interogare rezultate. Dacă interogarea conține o clauză GROUP BY, fiecare set de rânduri specificat în clauza GROUP BY constituie un grup și funcțiile de agregare sunt executate pentru fiecare grup separat.
Luați în considerare un exemplu cu tabelul de lecții: 
Exemplu:
Important: Astfel, ca urmare a utilizării GROUP BY, toate rândurile de ieșire ale interogării sunt împărțite în grupuri caracterizate de aceleași combinații de valori în aceste coloane (adică, funcțiile de agregare sunt efectuate pentru fiecare grup separat).
Trebuie avut în vedere că atunci când sunt grupate după un câmp care conține valori NULL, toate astfel de înregistrări se vor încadra într-un singur grup.
Pentru diferite tipuri de imprimante, determinați costul și cantitatea lor medie (adică separat pentru laser, cu jet de cerneală și matrice). Utilizați funcții agregate. Rezultatul ar trebui să arate astfel:
Având instrucțiune SQL
Clauza HAVING din SQL este necesară pentru a valida valori, care se obţin folosind funcţia de agregare după grupare(după folosirea GROUP BY). O astfel de verificare nu poate fi inclusă într-o clauză WHERE.
Exemplu: Magazin de calculatoare DB... Calculați prețul mediu al computerelor cu aceeași viteză a procesorului. Calculați numai pentru acele grupuri al căror preț mediu este mai mic de 30.000.
prin valoarea coloanei Disciplina. Vom obține 4 grupuri pentru care putem calcula niște valori de grup, de exemplu, numărul de tupluri dintr-un grup, valoarea maximă sau minimă a coloanei Scor.| Funcţie | Rezultat |
|---|---|
| NUMARA | Numărul de rânduri sau valori de câmp nevide pe care le-a selectat interogarea |
| SUMĂ | Suma tuturor valorilor selectate pentru acest câmp |
| AVG | Media aritmetică a tuturor valorilor selectate în acest câmp |
| MIN | Cea mai mică dintre toate valorile selectate pentru acest câmp |
| MAX | Cea mai mare dintre toate valorile selectate pentru acest câmp |
| R1 | |||
|---|---|---|---|
| Numele complet | Disciplina | Nota | |
| Grupa 1 | Petrov F.I. | Bază de date | 5 |
| K. A. Sidorov | Bază de date | 4 | |
| Mironov A.V. | Bază de date | 2 | |
| Stepanova K.E. | Bază de date | 2 | |
| Krylova T.S. | Bază de date | 5 | |
| Vladimirov V.A. | Bază de date | 5 | |
| Grupa 2 | K. A. Sidorov | Teoria informației | 4 |
| Stepanova K.E. | Teoria informației | 2 | |
| Krylova T.S. | Teoria informației | 5 | |
| Mironov A.V. | Teoria informației | Nul | |
| Grupa 3 | Trofimov P.A. | Rețele și telecomunicații | 4 |
| Ivanova E.A. | Rețele și telecomunicații | 5 | |
| N. V. Utkina | Rețele și telecomunicații | 5 | |
| Grupa 4 | Vladimirov V.A. | Engleză | 4 |
| Trofimov P.A. | Engleză | 5 | |
| Ivanova E.A. | Engleză | 3 | |
| Petrov F.I. | Engleză | 5 | |
Funcții agregate sunt folosite ca numele câmpurilor din instrucțiunea SELECT, cu o singură excepție: iau numele câmpului ca argument. Numai câmpurile numerice pot fi utilizate cu funcțiile SUM și AVG. Atât câmpurile numerice, cât și cele de caractere pot fi utilizate cu funcțiile COUNT, MAX și MIN. Când sunt utilizate cu câmpuri de caractere, MAX și MIN le vor traduce în echivalentul ASCII și le vor procesa alfabetic. Unele SGBD-uri permit utilizarea agregatelor imbricate, dar aceasta este o abatere de la standardul ANSI cu toate consecințele care decurg.
De exemplu, puteți calcula numărul de studenți care au promovat examene la fiecare disciplină. Pentru a face acest lucru, trebuie să executați o interogare grupată după câmpul „Disciplina” și să afișați ca rezultat numele disciplinei și numărul de rânduri din grup pentru această disciplină. Utilizarea caracterului * ca argument pentru funcția COUNT înseamnă că toate liniile din grup sunt numărate.
SELECTAȚI Disciplina R1, NUMĂRARE (*) DIN GRUPA R1 PENTRU Disciplina R1
Rezultat:
Dacă dorim să numărăm numărul celor care au promovat examenul la orice disciplină, atunci trebuie să excludem valorile nedefinite din raportul inițial înainte de grupare. În acest caz, cererea va arăta astfel:
Obtinem rezultatul:
În acest caz, linia cu studentul
| Mironov A.V. | Teoria informației | Nul |
|---|
nu va intra în setul de tupluri înainte de grupare, deci numărul de tupluri din grup pentru disciplina " Teoria informației„va fi cu 1 mai puțin.
Poate fi aplicat funcții agregate tot fara operatia de grupare preliminara, in acest caz intreaga relatie este considerata ca un grup si pentru acest grup se poate calcula o valoare pe grup.
Referindu-ne din nou la baza de date Session (tabelele R1, R2, R3), vom afla numarul de examene promovate cu succes:
Acest lucru este, desigur, diferit de selectarea unui câmp, deoarece o singură valoare este întotdeauna returnată, indiferent de câte rânduri sunt în tabel. Argumentul funcții agregate pot exista coloane separate de tabele. Dar pentru a calcula, de exemplu, numărul de valori distincte ale unei anumite coloane dintr-un grup, este necesar să folosiți cuvântul cheie DISTINCT împreună cu numele coloanei. Să calculăm numărul de note diferite primite pentru fiecare disciplină:
Rezultat:
Rezultatul poate include valoarea câmpului de grupare și mai multe funcții agregate, iar câmpurile multiple pot fi utilizate în condiții de grupare. În acest caz, grupurile sunt formate dintr-un set de câmpuri de grupare specificate. Operațiile de agregare pot fi aplicate la concatenarea mai multor tabele sursă. De exemplu, să punem întrebarea: stabiliți pentru fiecare grupă și fiecare disciplină numărul celor care au promovat cu succes examenul și media punctajului la disciplină.
Rezultat:
Nu putem folosi funcții agregateîn clauza WHERE deoarece predicatele sunt evaluate în termenii unui singur rând și funcții agregate- din punct de vedere al grupurilor de linii.
Clauza GROUP BY vă permite să definiți un subset de valori dintr-un anumit câmp în termenii unui alt câmp și să aplicați o funcție de agregare subsetului. Acest lucru face posibilă combinarea câmpurilor și funcții agregateîntr-o singură clauză SELECT. Funcții agregate poate fi folosit atât în expresia pentru ieșirea rezultatelor rândului SELECT, cât și în exprimarea condiției de procesare a grupurilor HAVING formate. În acest caz, fiecare funcție agregată este calculată pentru fiecare grup selectat. Valori calculate funcții agregate, poate fi folosit pentru a afișa rezultate relevante sau pentru condiții de selecție de grup.
Să construim o interogare care să afișeze grupurile în care s-au obținut mai mult de un deuce la o disciplină la examene:
Pe viitor, de exemplu, vom lucra nu cu baza de date „Sesiune”, ci cu baza de date „Banca”, care constă dintr-un tabel F, care stochează relația F care conține informații despre conturile din sucursalele unei anumite bănci. :
F = (N, nume complet, Sucursală, Data deschiderii, Data închiderii, Sold); Q = (Sucursală, Oraș);
deoarece pe această bază puteți ilustra mai bine munca cu funcții agregate și grupare.
De exemplu, să presupunem că vrem să găsim soldul total al contului în sucursale. Puteți face o interogare separată pentru fiecare dintre ele selectând SUM (Rămas) din tabelul pentru fiecare ramură. GROUP BY, totuși, vă va permite să le puneți pe toate într-o singură comandă:
SELECTARE Sucursala, SUMA (Sold) FROM F GROUP BY Branch;
Se aplică GROUP BY funcții agregate independent pentru fiecare grup, identificat prin valoarea câmpului Filiala. Grupul este format din linii cu aceeași valoare a câmpului Branch și
Standardul ISO definește următoarele cinci functii de agregare:
NUMARA- returnează numărul de valori din coloana specificată;
SUMĂ- returnează suma valorilor din coloana specificată;
AVG- returnează valoarea medie în coloana specificată;
MIN- returnează valoarea minimă în coloana specificată;
MAX- returnează valoarea maximă în coloana specificată.
Toate aceste funcții operează pe valori dintr-o singură coloană a tabelului și returnează o singură valoare. Funcțiile COUNT, MIN și MAX se aplică atât câmpurilor numerice, cât și nenumerice, în timp ce funcțiile SUM și AVG pot fi utilizate numai în cazul câmpurilor numerice. Cu excepția COUNT (*), atunci când se evaluează rezultatele oricăror funcții, toate valorile nule sunt mai întâi excluse, după care operația necesară este aplicată numai valorilor rămase ale coloanei non-nule. Opțiunea COUNT (*) este o utilizare specială a funcției COUNT - scopul ei este de a număra toate rândurile dintr-un tabel, indiferent dacă acestea conțin valori goale, valori duplicate sau orice altă valoare. Dacă doriți să eliminați valorile duplicate înainte de a utiliza funcția de agregare, plasați cuvântul cheie DISTINCT în fața numelui coloanei în definiția funcției. Standardul ISO permite utilizarea cuvântului cheie ALL pentru a indica în mod explicit că eliminarea valorilor duplicate nu este necesară, deși acest cuvânt cheie este asumat în mod implicit dacă nu este specificat niciun alt calificator. Cuvântul cheie DISTINCT este lipsit de sens pentru funcțiile MIN și MAX. Cu toate acestea, utilizarea sa poate afecta rezultatele execuției funcțiilor SUM și AVG, așa că ar trebui să luați în considerare în prealabil dacă ar trebui să fie prezent în fiecare caz specific. În plus, cuvântul cheie DISTINCT poate fi specificat cel mult o dată în fiecare solicitare.
Trebuie remarcat faptul că funcțiile agregate pot fi utilizate doar într-o listă SELECT și într-o clauză HAVING (vezi secțiunea 5.3.4). În toate celelalte cazuri, utilizarea acestor funcții este invalidă. Dacă lista SELECT conține o funcție de agregare, iar textul de interogare nu conține o clauză GROUP BY care furnizează agregarea datelor în grupuri, atunci niciunul dintre elementele listei SELECT nu poate include referințe de coloană, cu excepția cazului în care această coloană este utilizată ca parametru al functia de agregare. De exemplu, următoarea solicitare este nevalidă:
SELECTAȚIpersonal nu,NUMARA (salariu)
DINPersonal;
Eroarea este că această solicitare nu conține construcția A SE GRUPA CU, iar accesul la coloana staffNo din lista SELECT se realizează fără a utiliza funcția de agregare.
Exemplul 13. Utilizarea funcției COUNT (*).Determinați câte proprietăți de închiriere au o rată de închiriere de peste 350 GBP pe lună,
SELECTARE NUMĂR(*) CA număr
DINPropertyForRent
UNDEchirie> 350;
Clauza WHERE se limitează la numărarea numai a articolelor de închiriat care depășesc 350 GBP pe lună. Numărul total de proprietăți închiriate care îndeplinesc condiția specificată poate fi determinat folosind funcția de agregare COUNT. Rezultatele executării interogării sunt prezentate în tabel. 23.
Tabelul 23
| numara |
Exemplul 14. Folosind funcția COUNT (DISTINCT).Determinați câte proprietăți de închiriere diferite au fost vizualizate de clienți în mai 2001.
SELECTAȚI NUMĂR (DISTINCTpropertyNo) AS count
DINVizionare
Din nou, limitarea rezultatelor interogării la o analiză numai a acelor contracte de închiriere care au fost vizualizate în mai 2001 se realizează prin utilizarea unei clauze WHERE. Numărul total de obiecte examinate care îndeplinesc condiția specificată poate fi determinat folosind funcția de agregare COUNT. Cu toate acestea, deoarece același obiect poate fi vizualizat de clienți diferiți de mai multe ori, trebuie să specificați cuvântul cheie DISTINCT în definiția funcției pentru a exclude valorile duplicate din calcul. Rezultatele executării interogării sunt prezentate în tabel. 24.
Tabelul 24
Exemplul 16. Utilizarea funcțiilor MIN, MAXnAVG.Calculați valoarea salariului minim, maxim și mediu.
SELECTARE MIN(salariu) LA FEL DE min, MAX(salariu) LA FEL DE max, AVG(salariu) LA FEL DE medie
DINPersonal;
În acest exemplu, trebuie să procesați informații despre tot personalul companiei, deci nu trebuie să utilizați o clauză WHERE. Valorile necesare pot fi calculate folosind funcțiile MIN, MAX și AVG aplicate coloanei de salarii din tabelul Staff. Rezultatele executării interogării sunt prezentate în tabel. 26.
Tabelul 26.
Rezultatul interogării
| min | max | medie |
| 9000.00 | 30000.00 | 17000.00 |
Rezultate de grupare (clauza GROUP BY). Exemplele de date rezumative de mai sus sunt similare cu rândurile rezumate care se găsesc de obicei la sfârșitul rapoartelor. În totaluri, toate datele detaliate ale raportului sunt comprimate într-o singură linie de rezumat. Cu toate acestea, de foarte multe ori este necesar să se formeze subtotaluri în rapoarte. În acest scop, o clauză GROUP BY poate fi specificată în instrucțiunea SELECT. Este apelată o interogare care conține o clauză GROUP BY interogare de grupare deoarece grupează datele rezultate din operația SELECT și apoi creează un singur rând rezumat pentru fiecare grup individual. Coloanele listate în clauza GROUP BY sunt denumite coloane grupate. Standardul ISO cere ca clauzele SELECT și GROUP BY să fie strâns legate. Când utilizați clauza GROUP BY într-o instrucțiune SELECT, fiecare articol din lista SELECT trebuie să aibă singurul sens pentru întregul grup.În plus, o clauză SELECT poate include doar următoarele tipuri de elemente:
Numele coloanelor;
funcții de agregare;
constante;
Expresii care includ combinații ale celor de mai sus.
Toate numele coloanelor enumerate în lista SELECT trebuie să apară și în clauza GROUP BY, cu excepția cazului în care numele coloanei este utilizat numai în funcția de agregare. Opusul nu este întotdeauna adevărat - clauza GROUP BY poate conține nume de coloane care nu sunt în lista SELECT. Dacă clauza WHERE este folosită împreună cu clauza GROUP BY, atunci este procesată mai întâi și sunt grupate numai acele rânduri care îndeplinesc condiția de căutare. Standardul ISO specifică că atunci când se realizează gruparea, toate valorile lipsă sunt tratate ca fiind egale. Dacă două rânduri de tabel din aceeași coloană de grupare conțin valori nule și valori identice în toate celelalte coloane grupate nevide, acestea sunt plasate în același grup.
Exemplul 17. Utilizarea clauzei GROUP BY.Determinați numărul de personal care lucrează în fiecare dintre departamentele companiei, precum și salariile totale ale acestora.
SELECTAȚIramurăNu, NUMARA(personalNu) LA FEL DE numara, SUMĂ(salariu) LA FEL DE sumă
DINPersonal
A SE GRUPA CUramuraNr
COMANDA PENTRUramurăNu;
Nu este necesar să includeți numele de coloană staffNo și salariu în clauza GROUP BY, deoarece acestea apar doar în lista SELECT cu funcții agregate. În același timp, coloana branchNo din lista clauzei SELECT nu este asociată cu nicio funcție de agregare și, din acest motiv, trebuie specificată în clauza GROUP BY. Rezultatele executării interogării sunt prezentate în tabel. 27.
Tabelul 27
Rezultatul interogării
| ramuraNr | Numara | Sumă |
| B003 | 54000.00 | |
| B005 | 39000.00 | |
| B007 | 9000.00 |
Conceptual, la procesarea acestei cereri, se parcurg următorii pași.
1. Rândurile din tabelul de personal sunt împărțite în grupuri în conformitate cu valorile din numărul coloanei departamentului companiei. În cadrul fiecăreia dintre grupuri, există date despre întreg personalul unuia dintre departamentele companiei. În exemplul nostru, vor fi create trei grupuri, așa cum se arată în Fig. 1.
2. Pentru fiecare dintre grupuri se calculează numărul total de rânduri, egal cu numărul de angajați din departament, precum și suma valorilor din coloana de salarii, care este suma salariilor tuturor. angajații departamentului care ne interesează. Un singur rând rezumat este apoi generat pentru întregul grup de rânduri sursă.
3. Rândurile rezultate din tabelul rezultat sunt sortate în ordinea crescătoare a numărului de ramură indicat în coloana branchNo.
| ramuraNr | personalNr | Salariu |
| V00Z | SG37 | 12000.00 |
| V00Z | SG14 | 18000.00 |
| V00Z | SG5 | 24000.00 |
| B005 | SL21 | 30000.00 |
| B005 | SL41 | 9000.00 |
| B007 | SA9 | 9000.00 |
| COUNT (nr personal) | SUMA (salariu) |
| 54000.00 | |
| 39000.00 | |
| 9000.00 |
Orez. 1. Trei grupuri de înregistrări create la executarea unei interogări
Standardul SQL permite plasarea subinterogărilor în lista SELECT. Prin urmare, interogarea de mai sus poate fi reprezentată și după cum urmează:
SELECTAȚIramurăNu, (SELECTARE COUNT (nr personal)LA FEL DE numara
DINPersonalul s
UNDEs.branchNo = b.branchNo),
(SELECT SUM (salariu) AS sumă
DINPersonalul s
UNDEs.branchNo = b.branchNo)
DINFiliala b
COMANDA PENTRUramurăNu;
Dar în această versiune a interogării, pentru fiecare dintre departamentele companiei descrise în tabelul Filiale, sunt generate două rezultate ale calculării funcțiilor de agregare, prin urmare, în unele cazuri, pot apărea rânduri care conțin valori zero.
Constrângeri de grupare (clauza HAVING). Clauza HAVING este destinată să fie utilizată împreună cu clauza GROUP BY pentru a specifica constrângerile specificate pentru a le selecta grupuri, care va fi plasat în tabelul de interogări rezultat. Deși clauzele HAVING și WHERE au sintaxă similară, scopul lor este diferit. Clauza WHERE este concepută pentru a selecta rânduri individuale pentru a umple tabelul de interogări rezultat, în timp ce clauza HAVING este utilizată pentru a selecta grupuri, plasat în tabelul de interogări rezultat. Standardul ISO impune ca numele coloanelor utilizate în clauza HAVING să apară în lista articolelor GROUP BY sau să fie utilizate în funcțiile agregate. În practică, termenii de căutare dintr-o clauză HAVING includ întotdeauna cel puțin o funcție agregată; în caz contrar, aceste condiții de căutare trebuie plasate într-o clauză WHERE și aplicate pentru selectarea rândurilor individuale. (Rețineți că funcțiile agregate nu pot fi folosite într-o clauză WHERE.) Clauza HAVING nu este o parte necesară a limbajului SQL - orice interogare scrisă folosind clauza HAVING poate fi reprezentată diferit fără a o folosi.
Exemplul 18. Folosind construcția HAVING.Pentru fiecare departament al companiei cu mai mult de un personal, determinați numărul de angajați și cuantumul salariilor acestora.
SELECTAȚIramurăNu, COUN T (nr personal) LA FEL DE numara, SUMĂ(salariu) LA FEL DE sumă
DINPersonal
A SE GRUPA CUramuraNr
AVÂND NUMĂRARE(nr personal)> 1
COMANDA PENTRUramurăNu;
Acest exemplu este similar cu cel precedent, dar aici sunt folosite restricții suplimentare, ceea ce indică faptul că ne interesează doar informații despre acele departamente ale companiei în care lucrează mai mult de o persoană. O cerință similară este impusă grupurilor, astfel încât clauza HAVING ar trebui utilizată în interogare. Rezultatele executării interogării sunt prezentate în tabel. 28.
Tabelul 28
| ramurăNumăr sumă |
| V00Z 3 54000,00 |
| B005 2 39000,00 |
Subinterogări.În această secțiune, vom discuta despre utilizarea instrucțiunilor SELECT complete încorporate în corpul altei instrucțiuni SELECT. Extern(a doua) instrucțiune SELECT folosește rezultatul execuției intern(primul) operator care să determine conținutul rezultatului final al întregii operațiuni. Interogările interioare pot fi găsite în clauzele WHERE și HAVING ale instrucțiunii exterioare SELECT - în acest caz, ele sunt denumite subinterogări, sau interogări imbricate.În plus, instrucțiunile interne SELECT pot fi utilizate în instrucțiunile INSERT, UPDATE și DELETE . Există trei tipuri de subinterogări.
Subinterogare scalară returnează valoarea selectată din intersecția unei coloane cu un rând, adică singurul sens. În principiu, o subinterogare scalară poate fi utilizată oriunde este necesară o singură valoare. Exemple de subinterogări scalare sunt prezentate în exemplele 13 și 14.
Subinterogare șir returnează valorile mai multor coloane ale unui tabel, dar ca un singur rând. O subinterogare de șir poate fi utilizată oriunde este folosit un constructor de valoare șir - de obicei predicate. O variantă a subinterogării șir este prezentată în exemplul 15.
Subinterogare de tabel returnează valorile uneia sau mai multor coloane ale unui tabel repartizate pe mai mult de un rând. O subinterogare de tabel poate fi utilizată oriunde poate fi specificat un tabel, de exemplu, ca operand al unui predicat IN.
Exemplul 19. Utilizarea unei subinterogări cu verificarea egalității. Machiaj o listă a personalului care lucrează în filiala companiei situată la adresa Main St1 463.
SELECTAȚI
DINPersonal
UNDEbranchNo = (SELECTARE branchNo
DINRamura
UNDEstrada = "163 Main S t");
O declarație internă SELECT (SELECT branchNo FROM Branch ...) este utilizată pentru a determina numărul sucursalei companiei situate la 163 Main St. (Există un singur astfel de departament într-o companie, deci acest exemplu este un exemplu de subinterogare scalară.) Odată obținut numărul de departament dorit, se execută o subinterogare externă pentru a prelua detaliile lucrătorilor din acel departament. Cu alte cuvinte, instrucțiunea interioară SELECT returnează un tabel format dintr-o singură valoare „BOOV. Acesta este numărul sucursalei companiei situată la” 163 Main St1. Ca rezultat, instrucțiunea SELECT exterioară devine după cum urmează:
SELECTAȚIpersonalNu, fNume, INume, poziție
DINPersonal
UNDEbranchNu = "B0031;
Rezultatele acestei interogări sunt prezentate în tabel. 29.
Tabelul 29
Rezultatul interogării
| personalNr | fNume | INume | poziţie |
| SG37 | Ann | Fag | Asistent |
| SG14 | David | Vad | supraveghetor |
| SG5 | Susan | Marca | Administrator |
O subinterogare este un instrument pentru crearea unui tabel temporar, al cărui conținut este preluat și procesat de un operator extern. O subinterogare poate fi specificată imediat după operatorii de comparare (adică operatorii =,<, >, <=, >=, <>) într-o clauză WHERE sau HAVING. Textul subinterogării trebuie să fie cuprins între paranteze.
Exemplul 20. Utilizarea subinterogărilor cu funcții agregate. Listați toți angajații cu salarii peste medie, indicând cât de mult salariul lor este mai mare decât salariul mediu pentru întreprindere.
SELECTAȚIpersonalNu, fNume, INume, funcție, salariu - ( SELECTARE AVG(salariu) DIN Personal) LA FEL DE salDiff
DINPersonal
UNDEsalariu> ( SELECTARE AVG(salariu) DIN S t a f f);
Trebuie menționat că nu se poate în mod direct include în expresie de interogare„UNDE salariu> AVG (salariu)”, din moment ce folosirea agregatului funcțiile din clauza WHERE sunt interzise. Pentru a obține rezultatul dorit, creați o subinterogare care calculează salariul mediu anual și apoi utilizați-o într-o declarație SELECT externă pentru a prelua informații despre acei angajați din companie ale căror salarii sunt mai mari decât această medie. Cu alte cuvinte, subinterogarea returnează salariul mediu anual pentru companie, care este de 17.000 GBP.
Rezultatul acestei subinterogări scalare este utilizat într-o instrucțiune exterioară SELECT atât pentru a calcula variația salariilor față de medie, cât și pentru a selecta informațiile despre angajați. Prin urmare, instrucțiunea SELECT exterioară devine:
SELECTAȚIpersonalNu, fNume, INume, funcție, salariu - 17000 La fel de salDiff
DINPersonal
UNDEsalariu> 17000;
Rezultatele executării interogării sunt prezentate în tabel. treizeci.
Tabelul 30.
Rezultatul interogării
| personalNr | fNume | INume | poziţie | salDiff |
| SL21 | Ioan | alb | Administrator | 13000.00 |
| SG14 | David | Vad | supraveghetor | 1000.00 |
| SG5 | Susan | Marca | Administrator | 7000.00 |
Aplicați la subinterogări urmând reguli si restrictii.
1. Clauza ORDER BY nu trebuie folosită în subinterogări, deși poate apărea în instrucțiunea exterioară SELECT.
2. Lista SELECT a unei subinterogări trebuie să constea din numele coloanelor individuale sau expresii compuse din acestea, cu excepția cazului în care cuvântul cheie EXISTS este utilizat în subinterogare.
3. În mod implicit, numele coloanelor dintr-o subinterogare se referă la tabelul numit în clauza FROM a subinterogării. Cu toate acestea, vă puteți referi și la coloanele din tabel specificate în clauza FROM a interogării externe utilizând nume calificate de coloane (așa cum este descris mai jos).
4. Dacă subinterogarea este unul dintre cei doi operanzi implicați în operația de comparare, atunci subinterogarea trebuie specificată în partea dreaptă a acestei operațiuni. De exemplu, următorul exemplu de scriere a unei interogări din exemplul anterior este incorect deoarece subinterogarea este plasată în partea stângă a operației de comparare cu valoarea coloanei de salariu.
SELECTAȚI
DINPersonal
UNDE(SELECTARE MEDIA (salariu) FROM Personal)< salary;
Exemplul 21... Subinterogări imbricate și utilizarea predicatului IN. Faceți o listă cu proprietățile de închiriere de care sunt responsabili angajații în sucursala situată la 163 Main st1.
SELECTAȚIproprietateNr, strada, oras, cod postal, tip, camere, inchiriere
DINPropertyForRent
capitolul 5... Limbajul SQL: manipularea datelor 189
UNDEstaffNo IN (SELECTARE personalNo
DINPersonal
UNDEbrancliNo = (SELECTARE branchNr
DINRamura
UNDEstrada = "163 Main S t"));
Prima interogare, cea mai internă, are scopul de a determina numărul sucursalei companiei situate la 463 Main St ". A doua interogare intermediară preia informații despre personalul care lucrează în acest departament. În acest caz, sunt mai multe rânduri de date. selectat și deci în interogarea externă nu poți folosi operatorul de comparație =.Trebuie să folosești în schimb cuvântul cheie IN.Interogarea exterioară preia informații despre contractele de închiriere de care sunt responsabili angajații companiei, datele despre care au fost obținute ca urmare a interogării intermediare.Rezultatele interogării sunt prezentate în tabel.31.
Tabelul 31
Rezultatul interogării
| proprietateNr | stradă | oraș | cod poștal | tip | camere | chirie |
| PG16 | 5 Novar Dr | Glasgow | G129AX | Apartament | ||
| PG36 | 2 Manor Rd | Glasgow | G324QX | Apartament | ||
| PG21 | 18 Dale Rd | Glasgow | G12 | Casa |
Cuvinte cheie ORICE și TOATE. Cuvintele cheie ANY și ALL pot fi utilizate cu subinterogări care returnează o singură coloană de numere. Dacă subinterogarea este precedată de cuvântul cheie ALL, condiția de comparare este îndeplinită numai dacă este îndeplinită pentru toate valorile din coloana rezultat a subinterogării. Dacă textul subinterogării este precedat de cuvântul cheie ORICE, atunci condiția de comparare va fi considerată îndeplinită dacă este îndeplinită pentru cel puțin una (una sau mai multe) valori din coloana rezultată a subinterogării. Dacă subinterogarea are ca rezultat o valoare nulă, atunci condiția de comparare pentru cuvântul cheie ALL va fi considerată îndeplinită, iar pentru cuvântul cheie ORICE, va fi considerată neîndeplinită. Conform standardului ISO, puteți utiliza suplimentar cuvântul cheie SOME, care este un sinonim pentru cuvântul cheie ANY.
Exemplul 22. Folosind cuvintele cheie ANY și SOME. Găsiți toți angajații al căror salariu depășește cel puțin salariul unu un angajat al unei sucursale a firmei cu numărul „booz”.
SELECTAȚIpersonalNu, fNume, INume, funcție, salariu
DINPersonal
UNDEsalariu> UNELE (SELECTARE salariu
DINPersonal
UNDEbranchNu = "B003");
Deși această interogare poate fi scrisă folosind o subinterogare care determină salariul minim al personalului din departament cu numărul „BOOZ”, după care subinterogarea externă poate selecta informații despre tot personalul companiei al cărui salariu este mai mare decât această valoare (vezi exemplul 20), este posibilă și o altă abordare.care constă în folosirea cuvintelor cheie SOME / ANY. În acest caz, interogarea interioară creează un set de valori (12000, 18000, 24000), iar interogarea exterioară selectează informații despre acei angajați al căror salariu este mai mare decât oricare dintre valorile din acest
set (de fapt, mai mult decât valoarea minimă - 12000). Această metodă alternativă poate fi considerată mai naturală decât definirea salariului minim în subinterogare. Dar, în ambele cazuri, sunt generate aceleași rezultate ale interogării, care sunt prezentate în tabel. 32 .
Tabelul 32
Rezultatul interogării
| personalNr | fNume | INume | poziţie | salariu |
| SL21 | Ioan | alb | Administrator | 30000.00 |
| SG14 | David | Vad | supraveghetor | 18000.00 |
| SG5 | Susan | Marca | Administrator | 24000.00 |
Exemplul 23. Folosind cuvântul cheie ALL. Găsiți toți angajații ale căror salarii sunt mai mari decât salariile oricărui angajat din filiala de băuturi a companiei.
SELECTAȚIpersonalNu, fNume, INarae, functie, salariu
DINPersonal
UNDEsalariu> TOATE(SELECTARE salariul
DINPersonal
UNDEbranchNu = "BOG3");
În general, această solicitare este similară cu cea anterioară. Și în acest caz, s-ar putea folosi o subinterogare care determină valoarea maximă a salariului personalului departamentului sub numărul "WOOZ", iar apoi, folosind o interogare externă, selectați informații despre toți angajații companiei ale căror salarii depășesc această valoare. Cu toate acestea, în acest exemplu, este aleasă abordarea cu cuvinte cheie ALL. Rezultatele executării interogării sunt prezentate în tabel. 33 .
Tabelul 33
Rezultatul interogării
| personalNr | INume | fNume | poziţie | salariu |
| SL21 | alb | Ioan | Administrator | 30000,00 |
Interogări cu mai multe tabele. Toate exemplele de mai sus au aceeași limitare importantă: coloanele plasate în tabelul rezultat sunt întotdeauna selectate dintr-un singur tabel. Cu toate acestea, în multe cazuri acest lucru nu este suficient. Pentru a combina coloane din mai multe tabele sursă în tabelul rezultat, trebuie să efectuați operația conexiuni.În SQL, o operație de îmbinare este utilizată pentru a combina informațiile din două tabele formând perechi de rânduri legate selectate din fiecare tabel. Perechile de rânduri plasate în tabelul combinat sunt compilate în funcție de egalitatea valorilor coloanelor specificate incluse în ele.
Dacă aveți nevoie să obțineți informații din mai multe tabele, puteți fie să aplicați o subinterogare, fie să efectuați o îmbinare între tabele. Dacă tabelul de interogări rezultat trebuie să conțină coloane din tabele sursă diferite, atunci este recomandabil să utilizați mecanismul de îmbinare a tabelului. Pentru a efectua o îmbinare, este suficient să specificați numele a două sau mai multe tabele în clauza FROM, separându-le cu virgule, și apoi să includeți în interogare clauza WHERE care definește coloanele folosite pentru unirea tabelelor specificate. În plus, în loc de nume de tabel, puteți utiliza pseudonime, atribuite acestora în clauza FROM. În acest caz, numele tabelelor și aliasurile atribuite acestora trebuie separate prin spații. Aliasurile pot fi folosite pentru a califica numele coloanelor ori de câte ori există ambiguitate cu privire la tabelul căruia îi aparține o anumită coloană. În plus, alias-urile pot fi folosite pentru a abreviera numele tabelelor. Dacă este definit un alias pentru un tabel, acesta poate fi folosit oriunde care necesită numele acelui tabel.
Exemplul 24. Conexiune simplă. Enumerați numele tuturor clienților care au vizualizat cel puțin o proprietate închiriată și au oferit feedback cu privire la aceasta.
SELECTAȚIc.clientNo, fName, INname, propertyNo, comentariu
DINClient c, Vizualizare v
UNDEc.clientNu = v.clientNu;
Acest raport necesită informații atât din tabelul Client, cât și din tabelul de vizualizare, așa că vom folosi mecanismul de alăturare a tabelului atunci când construim interogarea. Clauza SELECT listează toate coloanele care ar trebui plasate în tabelul de interogări rezultat. Rețineți că coloana cu numărul clientului (clientNo) necesită o rafinare, deoarece o astfel de coloană poate fi prezentă și într-un alt tabel care participă la unire. Prin urmare, este necesar să indicăm în mod explicit ce valori de tabel ne interesează. (În acest exemplu, ați putea la fel de bine să selectați valorile pentru coloana clientNo din tabelul de vizualizare). Numele este specificat prin prefixarea numelui coloanei cu numele tabelului corespunzător (sau aliasul acestuia). În exemplul nostru, este folosită valoarea „c”, specificată ca alias pentru tabelul Client. Pentru a forma rândurile rezultate, se folosesc acele rânduri ale tabelelor sursă care au o valoare identică în coloana clientNo. Această condiție este determinată prin setarea condiției de căutare cu.clientNo = v.clientNo. Sunt numite coloane similare din tabelele originale coloane compatibile. Operația descrisă este echivalentă cu operația egalitatea se unește algebră relațională. Rezultatele executării interogării sunt prezentate în tabel. 34.
Tabelul 34
Rezultatul interogării
| clientNr | fNume | INume | proprietateNr | cometariu |
| CR56 | Aline | Stewart | PG36 | |
| CR56 | Aline | Stewart | PA14 | prea mic |
| CR56 | Aline | Stewart | PG4 | |
| CR62 | Maria | Tregear | PA14 | nicio sufragerie |
| CR76 | Ioan | Kay | PG4 | prea îndepărtat |
Cel mai adesea, interogările cu mai multe tabele sunt efectuate pe două tabele conectate printr-o relație unu-la-mai multe (1: *) sau părinte-copil. În exemplul de mai sus, care implică accesarea tabelelor Client și Vizualizare, acestea din urmă sunt conectate doar printr-o astfel de relație. Fiecare rând din tabelul Vizualizare (copil) este legat de un singur rând din tabelul Client (părinte), în timp ce același rând din tabelul Client (părinte) poate fi legat.
cu multe rânduri din tabelul de vizualizare (copil). Perechile de rânduri care sunt generate atunci când interogarea este executată sunt rezultatul tuturor combinațiilor valide de rânduri din tabelele copil și părinte. Secțiunea 3.2.5 a descris în detaliu modul în care într-o bază de date relațională cheile primare și externe ale tabelelor creează o relație „părinte-copil”. Tabelul care conține cheia străină este de obicei un copil, în timp ce tabelul care conține cheia primară va fi întotdeauna părintele. Pentru a utiliza o relație părinte-copil într-o interogare SQL, trebuie să specificați o condiție de căutare în care vor fi comparate cheile străine și primare. Exemplul 24 compară cheia primară a tabelului Client (cu. ClientNo) cu cheia externă a tabelului de vizualizare (v. ClientNo).
Standardul SQL oferă în plus următoarele moduri de a defini o anumită conexiune:
DINClient cu A TE ALATURA Vizualizare v PE c.clientNo = v.clientNu
DINClientul J OIN Vizionare UTILIZAREA clientNr
DINClient UNIUNEA NATURALĂ Vizionare
În fiecare caz, clauza FROM înlocuiește clauzele originale FROM și WHERE. Cu toate acestea, prima opțiune creează un tabel cu două coloane clientNo identice, în timp ce în celelalte două cazuri tabelul rezultat va conține doar o coloană clientNo.
Exemplul 25. Sortarea rezultatelor unirii tabelelor. Pentru fiecare ramură a companiei, enumerați numerele de personal și numele angajaților care se ocupă de orice facilități închiriate și, de asemenea, indicați facilitățile pentru
la care ei raspund.
SELECTAȚIs.branchNo, s.staffNo, fName, INname, propertyNo
DINPersonal, PropertyForRent p
UNDEs.staffNo = p.staffNo
COMANDA PENTRUs.branchNo, s.staffNo, propertyNo;
Pentru a face rezultatele mai ușor de citit, rezultatul este sortat folosind numărul departamentului ca cheie principală de sortare și numărul personalului și numărul proprietății ca chei minore. Rezultatele executării interogării sunt prezentate în tabel. 35.
Tabelul 35
Rezultatul interogării
| ramuraNr | PersonalNr | fNume | INume | proprietateNr |
| CARE | SG14 | David | Vad | PG16 |
| CARE | SG37 | Ann | Fag | PG21 |
| CARE | SG37 | Ann | Fag | PG36 |
| BOO5 | SL41 | Maria | Lee | PL94 |
| SBI7 | SA9 | Julie | Howe | PA14 |
Exemplul 26. O îmbinare a trei tabele. Pentru fiecare sucursală a companiei, enumerați numerele de personal și numele angajaților care se ocupă de orice unități închiriate, indicând orașul în care se află sucursala și numerele unităților de care răspunde fiecare angajat.
SELECTAȚI b.filialaNr, b.oraș, s.staffNr, fNume, INume, proprietateNr
DIN Filiala b, Staff s, PropertyForRent p
UNDE b.branchNo = s.branchNo ȘI s.staffNo = p.staffNo
COMANDA PENTRU b.branchNo, s.staffNo, propertyNo;
Coloanele din cele trei tabele sursă - Branch, Staff și PropertyForRent - trebuie să fie plasate în tabelul rezultat, așa că ar trebui să alăturați aceste tabele în interogarea dvs. Tabelele Sucursale și Personal pot fi legate folosind clauza b.branchNo = * s .branchNo, care va lega sucursalele companiei de personalul care lucrează în acestea. Tabelele Staff și PropertyForRent pot fi legate folosind condiția s.staffNo = p.staffNo. Ca urmare, fiecare angajat va fi asociat cu proprietățile de închiriere pentru care este responsabil. Rezultatele executării interogării sunt prezentate în tabel. 36.
Tabelul 36
Interogarea rezultatelor
| ramuraNr | oraș | personal Mo | fNume | INume | proprietateNr |
| B003 | Glasgow | SG14 | David | Vad | PG16 |
| B003 | Glasgow | SG37 | Ann | Fag | PG21 |
| B003 | Glasgow | SG37 | Ann | Fag | PG36 |
| B005 | Londra | SL41 | Julie | Lee | PL94 |
| B007 | Aberdeen | SA9 | Maria | Howe | PA14 |
Rețineți că standardul SQL vă permite să utilizați o formulare alternativă a clauzelor FROM și WHERE:
DIN(Sucursala b ÎNSCRIEȚI-vă personalului UTILIZAND filialaNu) LA FEL DE bs
A TE ALATURAProprietateForRent p UTILIZAREA personalNr
Exemplul 27. Gruparea după mai multe coloane. Determinați numărul de proprietăți de închiriere pentru care este responsabil fiecare angajat.
SELECTAȚIs.branchNu, S.staffNu, NUMARA(*) LA FEL DE numara
DIN Staff s, PropertyForRent p
UNDE S.staffNo = p.staffNo
A SE GRUPA CUs.branchNu, s.staffNr
COMANDA PENTRUs.branchNu, s.staffNu;
Pentru a întocmi raportul solicitat, în primul rând, trebuie să aflați care dintre angajații companiei este responsabil pentru obiectele închiriate. Această sarcină poate fi rezolvată prin alăturarea tabelelor Staff și PropertyForRent de către coloana staffNo din clauzele FROM / WHERE. Apoi este necesar să se formeze grupuri formate din numărul departamentului și numerele de personal ale angajaților săi, pentru care să se aplice clauza GROUP BY. În cele din urmă, tabelul rezultat trebuie sortat prin specificarea unei clauze ORDER BY. Rezultatele executării interogării sunt prezentate în tabel. 37.
Tabelul 37
Rezultatul interogării
| ramuraNr | personalNr | numara |
| V00Z | SG14 | |
| V00Z | SG37 | |
| B005 | SL41 | |
| B007 | SA9 |
A face legături. O îmbinare este un subset al unei combinații mai generale de date din două tabele, numite carteziană... Produsul cartezian a două tabele este un alt tabel, format din toate perechile posibile de rânduri care alcătuiesc ambele tabele. Setul de coloane din tabelul rezultat este toate coloanele din primul tabel urmate de toate coloanele din al doilea tabel. Dacă introduceți o interogare pe două tabele fără a specifica o clauză WHERE, rezultatul executării interogării în mediul SQL va fi produsul cartezian al acestor tabele. În plus, standardul ISO oferă un format special pentru instrucțiunea SELECT care vă permite să calculați produsul cartezian a două tabele:
SELECTAȚI (* j columnList]
FROM tableNamel CROSS JOINCaNeulte2
Luați în considerare din nou un exemplu în care tabelele client și Vizualizare sunt conectate folosind coloana comună clientNr. Când lucrați cu tabele, al căror conținut este dat în Tabel. 3.6 și 3.8, produsul cartezian al acestor tabele va fi de 20 de rânduri (4 rânduri în tabelul Client x 5 rânduri în tabelul de vizualizare = 20 de rânduri). Acest lucru este echivalent cu emiterea interogării utilizate în Exemplul 5.24, dar fără a aplica clauza WHERE. Procedura de generare a unui tabel care conține rezultatele unirii a două tabele folosind instrucțiunea SELECT este următoarea.
1. Se formează un produs cartezian al tabelelor specificate în clauza FROM.
2. Dacă interogarea conține o clauză WHERE, aplicați condițiile de căutare fiecărui rând din tabelul de produse carteziene și stocați numai acele rânduri din tabel care îndeplinesc condițiile specificate. În ceea ce privește algebrei relaționale, această operație se numește prescripţie Produs cartezian.
3. Pentru fiecare rând rămas se determină valoarea fiecărui element specificat în lista SELECT, în urma căreia se formează un rând separat al tabelului rezultat.
4. Dacă clauza SELECT DISTINCT este prezentă în interogarea originală, toate rândurile duplicat sunt eliminate din tabelul rezultat.
5. Dacă interogarea în curs de executare conține o clauză ORDER BY,
© 2015-2019 site
Toate drepturile aparțin autorilor lor. Acest site nu pretinde autor, dar oferă o utilizare gratuită.
Data creării paginii: 2016-08-07
