Limbajul SQL. Formarea de interogări la baza de date

Caracteristicile funcției COUNT.

Numele parametrului	Sens
Subiect articol:	Caracteristicile funcției COUNT.
Rubrica (categoria tematica)	Comerț

funcții agregate.

Rezumatul solicitărilor de citire

Solicitările totale de citire vă permit să obțineți subtotaluri sau totale finale (informații statistice) pentru valorile conținute în baza de date. Interogările rezumate sunt extrem de importante de utilizat atunci când informațiile necesare nu sunt în mod explicit în baza de date și este extrem de important să le calculați.

SQL oferă funcții agregate (statistice) pentru a rezuma informațiile conținute într-o bază de date.

Grupurile de rânduri sunt numite agregate, prin urmare, funcțiile agregate sunt numite funcții ale căror argumente sunt grupuri de rânduri.

Agregatele pot fi toate rândurile de tabel sau grupurile de rânduri create de clauza GROUP BY (care va fi discutată în 13.7.2).

Funcția agregată primește ca argument o expresie care conține, by macar, o coloană de tabel și returnează o valoare ca rezultat. Τᴀᴋᴎᴍ ᴏϬᴩᴀᴈᴏᴍ, funcțiile agregate vă permit să efectuați operații pe valorile unei coloane întregi a unui tabel sau a mai multor tabele simultan.

SQL are cinci funcții agregate standard: SUM, AVG, MIN, MAX, COUNT.

SUMĂ(<выражение>) calculează suma tuturor valorilor din expresie;

AVG(<выражение>) calculează media tuturor valorilor din expresie;

MIN(<выражение>) găsește cea mai mică dintre toate valorile din expresie;

MAX(<выражение>) găsește cea mai mare dintre toate valorile din expresie;

NUMARA(<выражение>) numără numărul de valori dintr-o expresie.

Cea mai comună expresie este numele unei coloane de tabel.

Funcția COUNT(*) numără numărul de rânduri (ᴛ.ᴇ. numără și NULL).

NUMĂRĂ(DISTE<выражение>) numără numărul de valori distincte dintr-o expresie.

Nota 1.În construcția Microsoft Access NUMĂRĂ(DISTE<выражение>) nu funcționează!!! Această problemă este rezolvată prin utilizarea unei interogări imbricate în clauza FROM.

Restricții pentru a utiliza funcții agregate:

Funcțiile agregate nu pot fi utilizate într-o clauză WHERE (funcționează pentru un singur rând, nu pentru un agregat);

Funcțiile agregate nu pot fi imbricate unele în altele (puteți folosi interogări imbricate dacă este extrem de important);

Funcțiile agregate și numele de coloane obișnuite nu pot fi utilizate într-o clauză SELECT în același timp (cu excepția cazului în care este specificat un GROUP BY pe acele coloane)

Nota 2.Într-o clauză SELECT, se recomandă ca coloanele de returnare care utilizează funcții agregate să primească aliasuri.

Exemplul 29. Calculați costul total al tuturor apelurilor.

SELECTAȚI SUMA(STOIM)

DE LA VYZOVY

Exemplul 30. Calculați vârsta medie pentru contacte.

SELECTAȚI AVG(VOZRAST)

DE LA CONTACT

Exemplul 31. Calculați durata minimă și maximă a apelurilor efectuate.

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

DE LA VYZOVY V, TIPY_VYZ T

WHERE (V.TIP_ID=T.ID) ȘI (T.NAZV="Ieșire")

Exemplul 32. Afișează data și ora primului apel.

SELECTARE MIN (VREMYA)

DE LA VYZOVY

Exemplul 33. Calculați numărul de contacte mai vechi de 30 de ani.

SELECTARE NUMĂR (ID)

DE LA CONTACT

UNDE VOZRAST>30

SELECTARE NUMĂR (*)

DE LA CONTACT

UNDE VOZRAST>30

Exemplul 34. Calculați numărul de tonuri de apel deja atribuite unui contact.

Nu funcționează în ACCESS:

SELECTAȚI COUNT(DISTINCT MEL_ID)

DE LA CONTACT

Lucrează în ACCESS:

SELECTARE NUMĂR (MELID)

DE LA (SELECTARE DISTINCT MEL_ID

DE LA CONTACT)

Caracteristicile funcției COUNT. - concept și tipuri. Clasificarea și caracteristicile categoriei „Funcții ale funcției COUNT”. 2017, 2018.

- Șapte miliarde și numărând foaia de lucru A

Referințe suplimentare Referințe DeGolyer E., Vance H., Bibliography of the Petroleum Industry, Bull. 83, A. și. M. College of Texas, College Station, Texas, 725 p., 1944. Dunbar C. R., Dunbar L., A Selected List of Periodicals, Serials, and Books Dealing with Petroleum and Allied Subjects, Department of Conservation, New Orleans, Louisiana, 218, p., 1939. Economic Geology Publishing Company, Urbana, Illinois,... .

- Sistemul de educație petrolieră în țara noastră

Țara noastră a acordat întotdeauna o mare atenție educației inginerilor petrolieri. La scurt timp după Revoluția din octombrie din septembrie 1918, guvernul sovietic a publicat Decretul despre organizarea Academiei de Mine de la Moscova. Mai târziu, în 1930, un institut petrolier separat a fost separat din această Academie și noi numim acest institut Universitatea de Stat Rusă Gubkin de Petrol și Gaze. De atunci, nivelul de educație petrolieră a crescut de mai multe ori. In prezent sistemul de petrol... .

- EDUCAȚIA PETROLIARĂ ÎN ȚARA NOASTRA

UNIVERSITATEA TEHNICĂ Departamentul limbi straine MANUAL DE ENGLEZA PE UN PROFIL LARG AL UNIVERSITĂȚII PETROLEUM PETROLEUM Ufa 2012 Manualul este format din 16 lecții. Fiecare lectie include vocabular activ, lexical, gramatica,... .

- In tara noastra

Istoria învățământului de inginerie petrolieră din țara noastră este inseparabilă de istoria întemeierii și dezvoltării industriilor sale de petrol și gaze. În Rusia țaristă, industria petrolului era concentrată aproape exclusiv în Caucaz. La 2 iulie 1918, șeful guvernului a semnat decretul privind naționalizarea industriei petroliere (nu produceau materii prime gazoase în Rusia și nu exista nici o industrie a gazelor acolo înainte de revoluție). Dar în vremurile grele ale Civilei... .

- Contabilitate de gestiune

1. Model. 2. Cerințe de informare. 3. Colectarea de informații. 4. Activarea modelului și interpretarea rezultatelor. 5. Prezentarea rezultatelor modelului. G \u003d D-Z Gpr \u003d D (V) pr - Zpr - profit produs MD - venit marginal. MD \u003d B - Z variabile, (B - venit, C-costuri) Model ... .

- Exercițiul 2. Imaginează-ți că lucrezi ca contabil într-o întreprindere mare. Răspunde la următoarele întrebări.

1. Totalizați conturile la sfârșitul anului fiscal? 2. Care sunt activitățile dumneavoastră obișnuite la sfârșitul anului fiscal? 3. Când întocmiți o situație financiară? 4. De ce ar trebui să aveți mai multe active decât datorii? 5. Departamentul de contabilitate lucrează la impozite? 6. Ce este diferență între salariul tau brut si salariul net? 7. Diferența dintre salariul brut și salariul net este mare în această țară? De ce depinde această diferență? Exercițiul 3. Alcătuiește... .

- Funcția COUNT

Rezumarea datelor de interogare În această lecție, ne vom uita la acele funcții SQL care sunt concepute pentru a rezuma. Cu ajutorul lor, este posibil să se realizeze gamă largă operatii matematice cu date. Principalele lucruri din această lecție vor fi următoarele ....

- Contor Geiger

Cost: 300 Este mic dispozitiv util nu numai că afișează numărul de radiații dintr-o persoană, dar măsoară și fondul de radiații din mediu. Poate fi util atunci când trebuie să aflați dacă un crater uriaș strălucitor în față este periculos. Greutate: 5 lb....

- Numărătoare inversă

De îndată ce setul de instalare este format, începe procesul de numărare. Citirea afișează raportul dintre prețul de închidere și cel mai mare sau pret minim Două zile de tranzacționareîn urmă, în funcție de ce set de instalare este activ - setat la... .

Următoarele subsecțiuni descriu alte clauze de instrucțiuni SELECT care pot fi utilizate în interogări, precum și funcții agregate și seturi de instrucțiuni. Lasă-mă să-ți reamintesc momentul prezent ne-am uitat la 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 împreună cu funcțiile agregate care sunt acceptate în Transact- SQL.

GROUP BY oferta

Propoziție A SE GRUPA CU grupează setul selectat de rânduri pentru a produce un set de rânduri rezumative pe baza valorilor uneia sau mai multor coloane sau expresii. O utilizare simplă a clauzei 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 angajat sunt selectate și grupate.

În exemplul de mai sus, se creează clauza GROUP BY grup separat pentru toate valorile posibile (inclusiv NULL) ale coloanei 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 în două coloane:

UTILIZAȚI SampleDb; SELECTARE ProjectNumber, Job FROM Works_On GROUP BY ProjectNumber, Job;

Rezultatul acestei interogări este:

Pe baza rezultatelor interogării, puteți vedea că există nouă grupuri cu diferite combinații de număr de proiect și poziție. 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 utilizate pentru a obține valorile totale. 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

Limbajul 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 cea de-a doua formă a celor două funcții COUNT și COUNT_BIG, și anume COUNT(*) și, respectiv, COUNT_BIG(*).) Rezultatul oricărei funcții agregate este o valoare constantă afișată într-o coloană separată de rezultat.

Funcțiile agregate sunt specificate în lista de coloane a instrucțiunii SELECT, care poate conține și o clauză GROUP BY. Dacă nu există o clauză GROUP BY în instrucțiunea SELECT și lista de coloane select conține cel puțin o funcție de agregare, atunci aceasta nu trebuie să conțină coloane simple (altele decât coloanele care servesc ca argumente pentru funcția de agregare). Prin urmare, codul din exemplul de mai jos este incorect:

UTILIZAȚI SampleDb; SELECT Nume, MIN(Id) FROM Angajat;

Aici, coloana LastName din tabelul Employee nu ar trebui să fie în lista de selectare a coloanei, deoarece nu este un argument de funcție agregată. Pe de altă parte, o listă de selectare a coloanei poate conține nume de coloane care nu sunt argumente pentru o funcție de agregare dacă acele coloane sunt argumente pentru clauza GROUP BY.

Un argument de funcție agregată poate fi precedat de unul dintre cele două cuvinte cheie posibile:

TOATE

Specifică faptul că calculele sunt efectuate pentru toate valorile din coloană. Aceasta este valoarea implicită.

DISTINCT

Specifică că numai valori unice coloană.

Funcțiile agregate MIN și MAX

Funcțiile agregate MIN și MAX calculează cea mai mică și, respectiv, cea mai mare valoare a unei coloane. Dacă interogarea conține o clauză WHERE, funcțiile MIN și MAX returnează cea mai mică și cea mai mare valoare a rândurilor care îndeplinesc condițiile specificate. Exemplul de mai jos arată utilizarea funcției de agregare MIN:

UTILIZAȚI SampleDb; -- Returnează 2581 SELECT MIN(Id) AS „Min Id” FROM Employee;

Rezultatul returnat în exemplul de mai sus nu este foarte informativ. De exemplu, numele angajatului care deține acest număr este necunoscut. Cu toate acestea, nu este posibil să obțineți acest nume de familie în mod obișnuit, deoarece, după cum am menționat mai devreme, nu este permisă specificarea explicită a coloanei LastName. Pentru a obține numele de familie al acestui angajat împreună cu cel mai mic număr de personal al unui angajat, este utilizată o subinterogare. Următorul exemplu arată utilizarea unei astfel de subinterogări, în care subinterogarea conține instrucțiunea SELECT din exemplul anterior:

Rezultatul execuției interogării:

Utilizarea funcției de agregare MAX este prezentată în exemplul de mai jos:

Funcțiile MIN și MAX pot lua, de asemenea, șiruri și date ca argumente. În cazul unui argument șir, valorile sunt comparate folosind ordinea reală de sortare. Pentru toate argumentele date de timp de tipul date, cea mai mică valoare a coloanei va fi data cea mai veche, iar cea mai mare valoare a coloanei va fi cea mai recentă dată.

Cu funcțiile MIN și MAX, puteți aplica cuvânt cheie DISTINCT. Înainte ca funcțiile de agregare MIN și MAX să fie utilizate, toate valorile NULL sunt excluse din coloanele argumentelor lor.

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 sumar” FROM Proiect;

Acest exemplu calculează suma totală a bugetelor tuturor proiectelor. Rezultatul execuției interogării:

În acest exemplu, funcția agregată grupează toate valorile bugetului proiectului și determină suma lor totală. 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) „Buget total” FROM Project GROUP BY();

Utilizarea opțiunii DISTINCT elimină toate valorile duplicate din coloană înainte de a aplica funcția SUM. În mod similar, toate valorile NULL sunt eliminate înainte de a aplica această funcție de agregare.

Funcția de agregare 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. Înainte ca funcția AVG să fie utilizată, toate valorile NULL sunt eliminate din argumentul său.

Utilizarea funcției de agregare AVG este prezentată în exemplul de mai jos:

UTILIZAȚI SampleDb; -- Returnează 133833 SELECT AVG (Buget) „Buget mediu per proiect” FROM Proiect;

Aici se calculează media aritmetică a bugetului pentru toate bugetele.

Funcțiile agregate 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ă cuvântul cheie DISTINCT este utilizat într-o interogare, orice valoare a coloanei duplicat este eliminată înainte ca funcția COUNT să fie aplicată. Această formă a funcției COUNT nu ia în considerare valorile NULL atunci când se numără numărul de valori dintr-o coloană.

Utilizarea primei forme a funcției de agregare COUNT este prezentată în exemplul de mai jos:

UTILIZAȚI SampleDb; SELECTARE ProjectNumber, COUNT(DISTINCT Job) "Works in project" FROM Works_on GROUP BY ProjectNumber;

Aici se numără numărul de poziții diferite pentru fiecare proiect. Rezultatul acestei interogări este:

După cum puteți vedea din exemplul de interogare, valorile NULL nu au fost luate în considerare de funcția COUNT. (Suma tuturor valorilor din coloana jobului sa dovedit a fi 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 unei interogări cu funcția COUNT(*) conține o clauză WHERE cu o condiție, funcția returnează numărul de rânduri care satisface condiție specificată. Spre deosebire de prima formă a funcției COUNT, a doua formă nu ignoră valorile NULL, deoarece această funcție operează pe rânduri, nu pe coloane. Exemplul de mai jos demonstrează utilizarea funcției COUNT (*):

UTILIZAȚI SampleDb; SELECTARE Job AS „Tipul de muncă”, COUNT(*) „Necesită muncitori” FROM Works_on GROUP BY Job;

Aici se numără numărul de posturi din toate proiectele. Rezultatul execuției interogării:

COUNT_BIG funcție similar cu funcția COUNT. Singura diferență dintre ele este tipul de rezultat pe care îl returnează: funcția COUNT_BIG returnează întotdeauna valori BIGINT, în timp ce funcția COUNT returnează valori de date INTEGER.

Funcții agregate statistice

Următoarele funcții alcătuiesc un grup de funcții agregate statistice:

VAR

Calculează varianța statistică a tuturor valorilor reprezentate într-o coloană sau expresie.

VARP

Calculează varianța statistică a populației tuturor valorilor reprezentate într-o coloană sau expresie.

STDEV

Calculează abaterea standard (care este calculată ca Rădăcină pătrată din varianța corespunzătoare) a tuturor valorilor coloanei sau expresiei.

STDEVP

Calculează abaterea standard a totalității 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ă mărească 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 mai detaliat mai târziu.

AVÂND oferta

Î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 grupuri de rânduri ca și clauza WHERE pentru conținutul tabelului corespunzător. Sintaxă AVÂND oferte Următorul:

AVÂND stare

Aici, parametrul condiție reprezintă o condiție și conține funcții sau constante agregate.

Utilizarea clauzei HAVING cu funcția de agregare COUNT(*) este prezentată în exemplul de mai jos:

UTILIZAȚI SampleDb; -- Returnează „p3” SELECTează Numărul Proiectului FROM Works_on GROUP BY ProjectNumber HAVING COUNT(*)

În acest exemplu, folosind clauza GROUP BY, sistemul grupează toate rândurile pe baza valorilor din coloana ProjectNumber. După aceea, numărul de rânduri din fiecare grup este numărat și sunt selectate grupurile care conțin mai puțin de patru rânduri (trei sau mai puțin).

Clauza HAVING poate fi folosită și fără funcții agregate, așa cum se arată în exemplul de mai jos:

UTILIZAȚI SampleDb; -- Returnează „Consultant” SELECTARE Job FROM Works_on GROUP BY Job HAVING Job LIKE „K%”;

Acest exemplu grupează rândurile din tabelul Works_on după poziție și elimină acele poziții 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 de tabel sunt returnate în același grup.

COMANDA PRIN oferta

Propoziție COMANDA PENTRU definește ordinea de sortare a rândurilor din setul de rezultate returnat de interogare. Această propoziție are următoarea sintaxă:

Ordinea de sortare este specificată în parametrul col_name. Parametrul col_number este un specificator alternativ de ordine de sortare care specifică coloanele în ordinea în care apar în lista select a instrucțiunii SELECT (1 este prima coloană, 2 este a doua coloană și așa mai departe). Parametrul ASC definește sortarea în ordine crescătoare și parametrul DESC- Descendentă. Valoarea implicită este ASC.

Numele coloanelor din clauza ORDER BY nu trebuie să se afle în lista de coloane selectate. Dar acest lucru nu se aplică interogărilor SELECT DISTINCT, deoarece în astfel de interogări, numele coloanelor specificate în clauza ORDER BY trebuie, de asemenea, specificate în lista de coloane select. În plus, această clauză nu poate conține nume de coloane din tabele care nu sunt listate în clauza FROM.

După cum puteți vedea din sintaxa clauzei ORDER BY, setul de rezultate poate fi sortat pe mai multe coloane. Această sortare este prezentată în exemplul de mai jos:

Acest exemplu selectează numerele de departament și numele și prenumele angajaților pentru angajații al căror număr de personal este mai mic de 20.000 și sortează după nume și prenume. Rezultatul acestei interogări este:

Coloanele din clauza ORDER BY pot fi specificate nu după numele lor, ci prin ordine în lista de selectare. Î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 numele lor se aplică 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ă ca coloanele să fie specificate după numele lor, mai degrabă decât după numere, pentru a facilita actualizarea interogați dacă coloanele trebuie adăugate sau eliminate din lista de selectare. Specificarea coloanelor din clauza ORDER BY după numerele lor este prezentată în exemplul de mai jos:

UTILIZAȚI SampleDb; SELECT 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 de proiect și numărul de angajați care participă la acesta, sortând rezultatul în ordine descrescătoare după numărul de angajați.

Transact-SQL pune valori NULL la începutul listei atunci când sortați în ordine crescătoare și la sfârșitul listei când sortați în ordine descrescătoare.

Utilizarea clauzei ORDER BY pentru paginarea rezultatelor

Afișarea rezultatelor interogării pe pagina curentă poate fi fie implementată în aplicația utilizator, fie serverul de baze de date poate fi instruit 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, pe partea serverului, doar rândurile necesare pentru pagina curenta. După cum v-ați putea aștepta, generarea paginilor pe server oferă de obicei performanțe mai bune, deoarece numai rândurile necesare pentru afișare sunt trimise clientului.

Pentru a sprijini crearea paginilor pe partea serverului, SQL Server 2012 introduce două noi clauze de instrucțiune 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 surse), sunt preluate ID-ul companiei, titlul postului și ziua de naștere a tuturor angajatelor, sortând rezultatul 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ă a treia pagină:

Într-o propoziție DECALAJ specifică numărul de rânduri de rezultat care trebuie sărit în rezultatul afișat. Acest număr este calculat după ce rândurile sunt sortate după clauza ORDER BY. Într-o propoziție FETCH NEXT specifică numărul de WHERE care se potrivesc ș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 îndeplinită prin Pachetul SQL Server.

Instrucțiunea SELECT și proprietatea IDENTITY

proprietatea IDENTITATE vă permite să definiți valori pentru o anumită coloană a tabelului ca un contor cu creștere automată. Coloanele de 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 pornind de la valoarea de pornire specificată. Astfel, proprietatea IDENTITY poate fi folosită pentru a crea unice valori numerice 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 ("Articol1", 10), ("Articol2", 15) , ("Item3", 8), ("Item4", 15), ("Item5", 40); -- Returnează 10004 SELECT IDENTITYCOL FROM Product WHERE Nume = "Product5"; -- Similar 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 generate automat de sistem, începând de la 10.000 și crescând cu una pentru fiecare valoare ulterioară: 10.000, 10.001, 10.002 și așa mai departe.

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 rezultatul acestui cod, această variabilă face referire automată la proprietatea IDENTITY. În schimb, puteți utiliza și funcția de sistem IDENTITYCOL.

Valoarea inițială și incrementul unei coloane cu proprietatea IDENTITATE pot fi găsite folosind funcțiile IDENT_SEEDși IDENT_INCR respectiv. Aceste funcții sunt aplicate după cum urmează:

UTILIZAȚI SampleDb; SELECT IDENT_SEED(„Produs”), IDENT_INCR(„Produs”)

După cum sa menționat deja, valorile IDENTITATE sunt setate automat de către sistem. Dar utilizatorul își poate specifica în mod explicit propriile valori pentru anumite rânduri prin setarea parametrului IDENTITY_INSERT ON înainte de a introduce o valoare explicită:

SETARE IDENTITATE INSERT numele tabelului

Deoarece opțiunea IDENTITY_INSERT poate fi setată la orice valoare pentru o coloană de proprietate IDENTITY, inclusiv o valoare duplicat, proprietatea IDENTITY nu impune de obicei unicitatea valorilor coloanei. Prin urmare, constrângerile UNIQUE sau PRIMARY KEY ar trebui utilizate pentru a impune unicitatea valorilor coloanei.

Când inserați valori într-un tabel după ce IDENTITY_INSERT este activat, sistemul creează următoarea valoare a coloanei IDENTITY, incrementând cea mai mare valoare curentă a acelei coloane.

Instrucțiunea CREATE SEQUENCE

Utilizarea proprietății IDENTITY are mai multe dezavantaje semnificative, dintre care cele mai semnificative sunt următoarele:

cererea de proprietate este limitată la tabelul specificat;

noua valoare a coloanei nu poate fi obtinuta in 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ță este o funcționalitate a bazei 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 cu valoarea 1 cu incremente de 5 pentru fiecare valoare ulterioară. Astfel, în oferta START este specificată valoarea inițială și Oferta INCREMENTARE- Etapa. (Pasul poate fi fie pozitiv, fie negativ.)

În următoarele două propoziții opționale MINVALUEși MAXVALUE sunt specificate valorile minime și maxime ale obiectului secvenței. (Rețineți că valoarea MINVALUE trebuie să fie mai mică sau egală cu valoarea de început, iar valoarea MAXVALUE nu poate fi mai mare decât limita superioară a tipului de date specificat pentru secvență.) În clauză 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 NO CYCLE, ceea ce înseamnă că depășirea valorii maxime sau minime a secvenței cauzează 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 utilizare este prezentată în exemplul de mai jos:

UTILIZAȚI SampleDb; -- Returnează 1 SELECT NEXT VALUE FOR dbo.sequence1; -- Returnează 6 (pasul următor) SELECTEAZĂ URMĂTOAREA VALORĂ PENTRU 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 arată 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 de produse cu patru coloane. Apoi, două instrucțiuni INSERT inserează două rânduri în acest tabel. Primele două celule din prima coloană vor avea valorile 11 și 16.

Exemplul de mai jos arată 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 forța sistemul să insereze valorile generate. Această expresie poate fi folosită și ca parte a unei interogări cu mai multe rânduri folosind clauza OVER.

Pentru a modifica o proprietate a unei secvențe existente, utilizați Instrucțiunea ALTER SEQUENCE. Una dintre cele mai importante utilizări ale acestei instrucțiuni este opțiunea RESTART WITH, care resetează secvența specificată. Următorul exemplu arată 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;

Ștergeți o secvență folosind instrucțiunea SECVENȚA DE CĂDERARE.

Setați operatori

Pe lângă operatorii discutați mai devreme, Transact-SQL acceptă încă trei seturi de operatori: 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 unire. În consecință, rezultatul unării a două tabele este un tabel nou care conține toate rândurile incluse într-unul dintre tabelele originale sau ambele tabele.

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ă o uniune. Dacă se folosește opțiunea ALL, sunt afișate toate rândurile, inclusiv duplicatele. În operatorul UNION, parametrul ALL are aceeași semnificație ca în lista de selecție SELECT, cu o diferență: pentru lista de selectare SELECT, acest parametru este aplicat implicit, dar pentru operatorul UNION trebuie specificat explicit.

În forma sa originală, baza de date 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 existent Angajat, dar are o coloană suplimentară Oraș. Această coloană indică locul unde locuiesc angajații.

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 de selectare SELECT. Apoi, rândurile tabelului 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 din această coloană pot fi inserate folosind Managementul Mediului Studio sau cu 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 este:

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 și că 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 unei îmbinări poate fi ordonat numai folosind clauza ORDER BY în ultima instructie SELECTAȚI 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 preia angajații care fie lucrează în departamentul d1, fie au început să lucreze la proiect înainte de 1 ianuarie 2008.

Operatorul UNION acceptă opțiunea ALL. Când se utilizează această opțiune, duplicatele nu sunt eliminate din setul de rezultate. Puteți utiliza operatorul OR în loc de operatorul 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.

Instrucțiunile 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ția în acest context se află un set de rânduri care aparțin ambelor tabele. Iar diferența dintre două tabele este definită ca fiind toate valorile care aparțin primului tabel și nu sunt prezente în al doilea. Exemplul de mai jos arată utilizarea instrucțiunii INTERSECT:

Transact-SQL nu acceptă utilizarea opțiunii ALL fie cu instrucțiunea INTERSECT, fie cu instrucțiunea EXCEPT. Utilizarea instrucțiunii EXCEPT este prezentată în exemplul de mai jos:

Rețineți că acești trei operatori de set au o prioritate de execuție diferită: operatorul INTERSECT are cea mai mare prioritate, urmat de EXCEPȚIA declarației, iar operatorul UNION are cea mai mică prioritate. Neatenția la prioritatea 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 prin codificarea lor în funcție de apartenența lor socială, folosind valorile 1, 2 și 3, indicând bărbați, femei și, respectiv, copii. Această tehnică de programare poate reduce timpul necesar implementării programului. expresie CASE Limbajul Transact-SQL facilitează implementarea acestui 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 în care limbajul Transact-SQL permite utilizarea expresiilor. Expresia CASE are două forme:

o 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:

V acest caz se caută prima condiție de potrivire și apoi se execută clauza THEN corespunzătoare. Dacă niciuna dintre condiții nu corespunde cerințelor, se execută clauza ELSE. Utilizarea expresiei de căutare CASE este prezentată în exemplul de mai jos:

UTILIZAȚI SampleDb; SELECTAȚI Nume Proiect, CAZUL CÂND Buget > 0 ȘI Buget 100000 ȘI Buget 150000 ȘI Buget

Rezultatul acestei interogări este:

Acest exemplu ponderează bugetele tuturor proiectelor și afișează ponderile lor calculate împreună cu numele proiectelor respective.

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 Nume Proiect, CAZUL CÂND p1.Buget (SELECTARE AVG(p2.Buget) DIN Proiectul p2) ATUNCI „Peste medie” TERMINați „Categoria bugetară” DIN Proiectul p1;

Rezultatul acestei interogări este următorul:

Scopul lucrării: să învețe cum să formezi cele mai simple interogări de baze de date în SQL, să folosești expresii în interogări, inclusiv operații aritmetice, funcții pentru lucrul cu șiruri și date și funcții de agregare.

Interogare pentru a selecta întregul tabel. În munca de laborator nr. 10, o astfel de interogare a fost deja generată prin intermediul Access. Puteți simplifica tipul de cerere dacă în locul unei cereri

SELECTAȚI Comenzi.* FROM Comenzi;

scrie o cerere

SELECTAȚI * FROM Comenzi

SQL vă permite să omiteți numele tabelului înaintea numelui câmpului în cazurile în care interogarea folosește un singur tabel sau numele câmpului nu apare în mai multe tabele într-o interogare cu mai multe tabele.

Crearea de interogări SQL în Access începe cu un apel către designerul de interogări. Pentru a face acest lucru, în fereastra bazei de date, selectați obiectul „Interogări”, elementul de meniu „Creare”, iar în fereastra „Interogare nouă”, elementul „Designer”. Apoi, selectați tabelul „Comenzi” și treceți la modul SQL. Treceți în modul SQL: Accesați meniul VizualizareMod SQL.

Finalizați generarea cererii și executați-o.

Afișarea câmpurilor preferate, înlocuirea numelor câmpurilor cu aliasuri, sortarea înregistrărilor. Câmpurile tabelului sunt afișate pe ecranul de afișare în ordinea în care sunt listate în interogare. Numele câmpurilor când se afișează rezultatele interogărilor sunt adesea dificil de citit. Ele pot fi înlocuite în interogare cu aliasuri, așa cum se arată în exemplu:

SELECT OrderCode AS Order, ReceiverName AS Receiver, ReceiverAddress AS Address, ExecutionDate AS Data FROM Orders ORDER BY ReceiverName ASC;

În exemplu Cod comandă, Nume destinatar, Adresă destinatarși Data executiei– numele câmpurilor din tabelul Comenzi. Când rezultatele interogării sunt afișate pe ecranul de afișare, numele câmpurilor vor fi înlocuite cu aliasurile corespunzătoare specificate după cuvânt LA FEL DE.

Propoziție

ORDER BY ReceiverName ASC

servește la sortarea înregistrărilor selectate în valori de câmp crescător (adică în ordine alfabetică). Nume Destinatar. Dacă doriți să sortați în ordine descrescătoare , apoi în loc de ASC trebuie să folosească DESC (prescurtare pentru descendență).

Creați și executați această solicitare.

Ieșirea înregistrărilor fără duplicare. Formulați și executați următoarea interogare

SELECTAȚI ReceiverName AS Receiver FROM Orders ORDER BY ReceiverName DESC.

Numele destinatarilor se repetă de mai multe ori, deoarece toate înregistrările din tabel sunt selectate. Pentru a evita înregistrările duplicate, adăugați cuvântul DISTINCT la interogare după cuvântul SELECT. Uneori, modul DISTINCT este setat implicit în DBMS. Pentru ieșire toateînregistrează în acest caz, cuvântul ALL este inserat după cuvântul SELECT.

Utilizarea expresiilor într-o interogare. În lista de ieșiri, puteți specifica nu numai numele câmpurilor și aliasurile acestora, ci și expresii care includ operații și funcții aritmetice.

Multiplicare. Creați o solicitare pentru a afișa codul produsului, prețul, cantitatea și costul total al produsului comandat din tabelul „Comandat”. Cererea arată astfel:

SELECTează codul produsului, prețul, cantitatea, Pret*Cantitate AS Cost FROM comandat;

Pe cont propriu completati cererea cu un pret redus.

Utilizarea funcțiilor. Funcţie STR() este destinat să fie convertit într-un tip de text. Pentru a afișa costul mărfurilor în mii de ruble, indicând unitatea de măsură, se utilizează următoarea interogare:

SELECTează codul articolului, str(Preț*Cantitate/1000)+"mii. RUB" CA Prețul DE LA Comandat;

Pentru ca numărul și textul să fie tipărite în coloana „Cost”, trebuie să convertiți numărul în tipul de text și să îl combinați cu textul „mii de ruble”. Funcția este folosită pentru a converti str(<выражение числового типа>) și operația de îmbinare „+” (concatenare).

Creați o interogare în care sunt selectate 5 câmpuri din tabelul „Comenzi” și rezultatul este afișat în două coloane. Câmpul „Cod de comandă” este afișat în prima coloană, iar următoarele câmpuri sunt combinate în coloana cu aliasul „Adresa clientului”: Indexul Destinatarului, Țara Destinatarului, Orașul Destinatarului, Numele Destinatarului.

Nu uitați să puneți o virgulă urmată de un spațiu între câmpurile de adresă pentru a îmbina. Rezultatul interogării (sunt afișate primele două linii) ar trebui să arate astfel:

Funcția de selecție a părții date DATEPART(). Citiți descrierea acestei funcții în ajutorul Access (Cuprins, secțiunea „Visual Basic Language Reference”, articolul „Funcții”, litera D).

Utilizați o interogare în tabelul „Comenzi” pentru a determina anii în care au fost livrate mărfurile.

funcții agregate.(În Access se numesc statistici). Să numărăm numărul total de înregistrări din tabelul „Comenzi” și numărul de înregistrări care conțin date în câmpul „Zona destinatar”, adică numărul de înregistrări cu un câmp „Zona destinatar” nevid. Pentru a face acest lucru, rulați următoarea interogare:

SELECT count(*),count(RecipientRegion) FROM Comenzi;

Interogarea folosește o funcție de agregare NUMARA(). Folosind funcțiile de agregare MAX(), MIN() și AVG(), scrieți o interogare pentru a calcula prețul minim și mediu maxim al unui produs în tabelul Produse.

Folosind funcția de agregare SUM(), scrieți o interogare pentru a calcula costul total de expediere pentru toate articolele comandate din tabelul Comenzi.

Salvați toate solicitările pe care le-ați creat și afișați-le profesorului.

Astăzi vom vorbi pe tema „Operațiuni de grup în interogări Access”. Operațiunile de grup în interogările Access vă permit să selectați grupuri de înregistrări cu aceleași valori în câmpurile specificate și să calculați datele totale pentru fiecare grup pentru alte câmpuri folosind una dintre funcțiile statistice. Funcțiile statistice sunt aplicabile în primul rând câmpurilor cu tipul de date Numeric, Monedă, Data/Ora.
V Acces sunt furnizate nouă funcții statistice:

• Suma - suma valorilor unui câmp pentru grup;
• Media - media tuturor valorilor câmpurilor din grup;
• Max, Min - valoarea maximă, minimă a câmpului din grup;
• Count - numărul de valori de câmp din grup, excluzând valorile goale;
• StDev - abaterea standard de la valoarea medie a câmpului din grup;
• Var - dispersia valorilor câmpului în grup;
• First and Last - valoarea câmpului din prima sau ultima înregistrare din grup.

Rezultatul folosind operațiuni de grup conține o intrare pentru fiecare grup. Interogarea, în primul rând, include prin care se realizează gruparea și câmpurile pentru care functii statistice. Pe lângă aceste câmpuri, cererea poate include câmpuri care specifică condițiile de selecție.
Să luăm în considerare construcția unei interogări cu un singur tabel cu o operație de grup pe exemplul tabelului SUPPLY_PLAN.

Interogați cu funcția Sum

Sarcină. Determinați cantitatea totală din fiecare dintre bunurile care urmează să fie livrate clienților în temeiul contractelor. Toate datele privind cantitatea de mărfuri planificată pentru tarif sunt specificate în tabelul DELIVERY_PLAN.

1. Creați o interogare de selectare în vizualizarea Design din tabelul SUPPLY_PLAN.
2. Din lista tabelului, trageți câmpul CODE_TOV, codul produsului, în formularul de solicitare. Acest câmp va grupa înregistrările în tabel.
3. Trageți câmpul QUANTITY_POST în formularul de cerere, în funcție de care se va calcula cantitatea totală a fiecăruia dintre bunurile comandate în toate contractele.
4. Executați comanda Totaluri din grupul Afișare/Ascunde. Se va afișa formularul de cerere linie nouă Operațiune de grupare (Total) cu valoarea Grupare (Group By) în ambele câmpuri ale cererii.
5. În coloana NUM_POST, înlocuiți cuvântul Group By cu funcția Sum. Pentru a face acest lucru, apelați lista și selectați această funcție. Formularul de cerere va lua forma prezentată în Fig. 4.11.
6. Pentru a afișa rezultatul interogării (Figura 4.12), faceți clic pe butonul Run din grupul Rezultate.
7. Schimbați denumirea câmpului Sum-QTY_POST la Bunuri comandate. Pentru a face acest lucru, treceți la modul proiectare, plasați cursorul mouse-ului pe câmpul NUM_POST din formularul de solicitare și apăsați butonul din dreapta. V meniul contextual selectați Proprietăți. În fereastra Proprietăți câmp, introduceți în rândul Legendă - Mărfuri comandate. Pentru a deschide fereastra de proprietăți, poate fi executată comanda Foaie de proprietăți din grupul Afișare/Ascunde.

1. Salvați ca articole comandate.
2. Pentru a calcula numărul de mărfuri comandate în fiecare lună, grupați după două câmpuri: CODE_TOV și DATE_POST, care stochează luna de livrare (Fig. 4.13).
3. Pentru a calcula numărul de mărfuri comandate într-o anumită lună, completați interogarea anterioară prin introducerea unui parametru de interogare în condiția de selecție (Fig. 4.14).

Interogare cu funcția Count

Sarcină. Determinați de câte ori au fost expediate bunurile în cadrul fiecărui contract. Faptul de expediere este consemnat in tabelul FACTURA.

1. Creați o interogare de selectare pe baza tabelului FACTURĂ.
2. Din lista de câmpuri din tabelul INVOICE, trageți câmpul NOM_DOG în formularul de interogare. Acest câmp ar trebui utilizat pentru grupare.
3. De fapt, sensul sarcinii se rezumă la numărarea numărului de rânduri din tabel cu același număr de contract, deci nu contează pe ce câmp va fi calculată funcția Count. Trageți orice câmp în formularul de solicitare, de exemplu, NOM_DOG din nou.
4. Executați comanda Totaluri din grupul Afișare/Ascunde. Înlocuiți cuvântul Group By într-una dintre coloanele numite HOM_DOG cu funcția Count. Formularul de cerere va lua forma prezentată în Fig. 4.15.

prin valoarea coloanei Disciplina . Vom obține 4 grupuri, pentru care putem calcula niște valori de grup, precum numărul de tupluri din grup, valoarea maximă sau minimă a coloanei Scor. Tabelul 5.7. Funcții agregate

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 dintr-un anumit câmp
AVG	Media aritmetică a tuturor valorilor selectate dintr-un anumit 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
	Sidorov K. A.	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	Sidorov K. A.	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
	Utkina N.V.	Rețele și telecomunicații	5
Grupa 4	Vladimirov V. A.	limba engleză	4
	Trofimov P. A.	limba engleză	5
	Ivanova E. A.	limba engleză	3
	Petrov F.I.	limba engleză	5

Funcții agregate sunt folosite ca nume de câmpuri în instrucțiunea SELECT, dar cu o singură excepție: iau numele câmpului ca argument. Doar cu funcțiile SUM și AVG câmpuri numerice. Ambele câmpuri numerice și 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 lor ASCII și le vor procesa în ordine alfabetică. Unele SGBD-uri permit agregate imbricate, dar aceasta este o abatere de la standardul ANSI, cu toate implicațiile sale.

De exemplu, puteți calcula numărul de studenți care au susținut examene la fiecare disciplină. Pentru a face acest lucru, trebuie să executați o interogare grupată după câmpul „Subiect” și să afișați ca rezultat numele disciplinei și numărul de rânduri din grup pentru această disciplină. Folosirea caracterului * ca argument pentru funcția COUNT înseamnă a număra toate rândurile din grup.

SELECTAȚI R1.Discipline, COUNT(*) FROM R1 GROUP BY R1.Discipline

Rezultat:

Dacă vrem să numărăm numărul de studenți care au promovat examenul la orice disciplină, atunci trebuie să excludem valorile nule 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 disciplină " Teoria informației„va fi cu 1 mai puțin.

Poate fi aplicat funcții agregate de asemenea, fără operația de pregrupare, caz în care întreaga relație este considerată ca un grup și pentru acest grup se poate calcula o valoare per grup.

Referindu-ne din nou la baza de date „Sesiune” (tabelele R1, R2, R3), aflăm numărul de examene promovate cu succes:

Aceasta este, desigur, diferită de selecția câmpului, deoarece o singură valoare este întotdeauna returnată, indiferent de câte rânduri există în tabel. Argument funcții agregate pot exista coloane separate de tabele. Dar pentru a calcula, de exemplu, numărul de valori diferite ale unei anumite coloane dintr-un grup, trebuie să utilizați cuvântul cheie DISTINCT împreună cu numele coloanei. Să calculăm numărul de note diferite primite la fiecare disciplină:

Rezultat:

Rezultatul poate include o valoare de câmp 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 conform unui set de câmpuri de grupare specificate. Operațiile de agregare pot fi aplicate pentru a uni mai multe 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- în ceea ce privește grupurile de rânduri.

Clauza GROUP BY vă permite să definiți un subset de valori într-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 ca într-o expresie de rezultat SELECTează rândurile, iar în exprimarea condiţiei de prelucrare pentru grupele formate AVÂND . În acest caz, fiecare funcție agregată este calculată pentru fiecare grup selectat. Valori rezultate din calcul funcții agregate, poate fi folosit pentru a afișa rezultatele corespunzătoare sau pentru a condiționa selecția grupurilor.

Să construim o interogare care să afișeze grupuri în care au fost primite mai mult de un deuce la o disciplină la examene:

Pe viitor, de exemplu, nu vom lucra cu baza de date „Sesiune”, ci cu baza de date „Bancă”, formată 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ă se poate ilustra mai clar lucrarea cu funcţii agregate şi grupare.

De exemplu, să presupunem că vrem să găsim soldul total al conturilor la sucursale. Puteți face o interogare separată pentru fiecare dintre ele selectând SUM(Sold) din tabelul pentru fiecare ramură. GROUP BY, totuși, le va pune pe toate într-o singură comandă:

SELECTARE Sucursala, SUM(Sold) FROM F GROUP BY Branch;

Se aplică GROUP BY funcții agregate independent pentru fiecare grup definit de valoarea câmpului Branch. Grupul este format din linii cu aceeași valoare câmpuri Filiala, și