Funcțiile agregate în sql sunt: Funcții agregate SQL

Să învățăm să rezumam. Nu, acestea nu sunt rezultatele studiului SQL, ci rezultatele valorilor coloanelor tabelelor bazei de date. Funcțiile agregate SQL operează pe valorile unei coloane pentru a produce o singură valoare rezultată. Cele mai frecvent utilizate funcții de agregare SQL sunt SUM, MIN, MAX, AVG și COUNT. Este necesar să se facă distincția între două cazuri de utilizare a funcțiilor agregate. În primul rând, funcțiile agregate sunt utilizate pe cont propriu și returnează o singură valoare rezultată. În al doilea rând, funcțiile de agregare sunt utilizate cu clauza SQL GROUP BY, adică gruparea după câmpuri (coloane) pentru a obține valorile rezultate în fiecare grup. Să luăm în considerare mai întâi cazurile de utilizare a funcțiilor agregate fără grupare.

Funcția SQL SUM

Funcția SQL SUM returnează suma valorilor dintr-o coloană a tabelului bazei de date. Poate fi aplicat numai coloanelor ale căror valori sunt numere. Interogările SQL pentru a obține suma rezultată încep astfel:

SELECTAȚI SUMA(COLUMN_NAME)...

Această expresie este urmată de FROM (TABLE_NAME) și apoi poate fi specificată o condiție folosind clauza WHERE. În plus, numele coloanei poate fi precedat de DISTINCT, ceea ce înseamnă că vor fi numărate numai valorile unice. În mod implicit, toate valorile sunt luate în considerare (pentru aceasta puteți specifica în mod specific nu DISTINCT, ci TOATE, dar cuvântul TOATE nu este necesar).

Exemplul 1. Există o bază de date a companiei cu date despre diviziile și angajații săi. Tabelul Personal are și o coloană cu date despre salariile angajaților. Selecția din tabel arată astfel (pentru a mări imaginea, faceți clic pe ea cu butonul stâng al mouse-ului):

Pentru a obține suma tuturor salariilor, utilizați următoarea interogare:

SELECTAȚI SUMA(Salariul) FROM Personal

Această interogare va returna valoarea 287664.63.

Si acum . În exerciții începem deja să complicăm sarcinile, apropiindu-le de cele întâlnite în practică.

Funcția SQL MIN

Funcția SQL MIN operează și pe coloanele ale căror valori sunt numere și returnează minimul tuturor valorilor din coloană. Această funcție are o sintaxă similară cu cea a funcției SUM.

Exemplul 3. Baza de date și tabelul sunt aceleași ca în exemplul 1.

Trebuie să știi minim salariile angajați ai departamentului cu numărul 42. Pentru aceasta, scrieți următoarea cerere:

Interogarea va returna valoarea 10505.90.

Și din nou exercițiu pentru auto-rezolvare. În acest exercițiu și în alte câteva exerciții, veți avea nevoie nu numai de tabelul Staff, ci și de tabelul Org, care conține date despre diviziile companiei:

Exemplul 4. Tabelul Org este adăugat la tabelul Staff, care conține date despre departamentele companiei. Tipăriți numărul minim de ani lucrați de un angajat într-un departament situat în Boston.

Funcția SQL MAX

Funcția SQL MAX funcționează în mod similar și are o sintaxă similară, care este utilizată atunci când trebuie să determinați valoare maximă dintre toate valorile coloanei.

Exemplul 5.

Trebuie să aflați salariul maxim al angajaților departamentului numărul 42. Pentru a face acest lucru, scrieți următoarea solicitare:

Interogarea va returna valoarea 18352.80

Este timpul exerciții pentru rezolvare independentă.

Exemplul 6. Lucrăm din nou cu două mese - Staff și Org. Afișează numele departamentului și valoarea maximă a comisionului primit de un angajat din departamentul aparținând grupului de departamente (Divizia) Est. Utilizare JOIN (aderarea la mese) .

Funcția SQL AVG

Ceea ce se spune cu privire la sintaxa pentru funcțiile descrise anterior este valabil și pentru funcția SQL AVG. Această funcție returnează media tuturor valorilor dintr-o coloană.

Exemplul 7. Baza de date și tabelul sunt aceleași ca în exemplele anterioare.

Să presupunem că doriți să aflați vechimea medie a angajaților din departamentul numărul 42. Pentru a face acest lucru, scrieți următoarea interogare:

Rezultatul va fi 6,33

Exemplul 8. Lucrăm cu o singură masă - Personal. Retrage salariu mediu angajați cu 4 până la 6 ani de experiență.

Funcția SQL COUNT

Funcția SQL COUNT returnează numărul de înregistrări dintr-un tabel de bază de date. Dacă specificați SELECT COUNT(COLUMN_NAME) ... în interogare, rezultatul va fi numărul de înregistrări fără a lua în considerare acele înregistrări în care valoarea coloanei este NULL (nedefinită). Dacă utilizați un asterisc ca argument și începeți o interogare SELECT COUNT(*) ..., rezultatul va fi numărul tuturor înregistrărilor (rândurilor) din tabel.

Exemplul 9. Baza de date și tabelul sunt aceleași ca în exemplele anterioare.

Vrei să știi numărul tuturor angajaților care primesc comisioane. Numărul de angajați ale căror valori ale coloanei Comm nu sunt NULL va fi returnat de următoarea interogare:

SELECTAȚI COUNT(Comm) FROM Staff

Rezultatul va fi 11.

Exemplul 10. Baza de date și tabelul sunt aceleași ca în exemplele anterioare.

Dacă doriți să aflați numărul total de înregistrări din tabel, atunci utilizați o interogare cu un asterisc ca argument pentru funcția COUNT:

SELECTAȚI COUNT(*) DIN Personal

Rezultatul va fi 17.

În urmatoarele exercițiu pentru soluție independentă va trebui să utilizați o subinterogare.

Exemplul 11. Lucrăm cu o singură masă - Personal. Afișați numărul de angajați din departamentul de planificare (Plains).

Funcții agregate cu SQL GROUP BY

Acum să ne uităm la utilizarea funcțiilor agregate împreună cu instrucțiunea SQL GROUP BY. Instrucțiunea SQL GROUP BY este utilizată pentru a grupa valorile rezultate pe coloane într-un tabel al bazei de date.

Exemplul 12. Există o bază de date a portalului de reclame. Are un tabel de anunțuri care conține date despre anunțurile trimise pentru săptămână. Coloana Categorie conține informații despre categorii mari reclame (de exemplu, Imobiliare), iar coloana Piese este despre părți mai mici incluse în categorii (de exemplu, părțile Apartamente și Cabane fac parte din categoria Imobiliare). Coloana Unități conține date despre numărul de reclame trimise, iar coloana Bani conține date despre suma de bani primită pentru trimiterea reclamelor.

Categorie	Parte	Unități	Bani
Transport	Mașini	110	17600
Imobiliare	Apartamente	89	18690
Imobiliare	Dachas	57	11970
Transport	Motociclete	131	20960
Materiale de construcție	Scânduri	68	7140
Inginerie Electrică	televizoare	127	8255
Inginerie Electrică	Frigidere	137	8905
Materiale de construcție	Regips	112	11760
Timp liber	Cărți	96	6240
Imobiliare	Acasă	47	9870
Timp liber	Muzică	117	7605
Timp liber	Jocuri	41	2665

Folosind instrucțiunea SQL GROUP BY, găsiți suma de bani câștigată prin postarea de anunțuri în fiecare categorie. Scriem următoarea cerere.

Lecția va acoperi subiectul sql redenumirea unei coloane (câmpuri) folosind cuvântul de serviciu AS; Subiectul funcțiilor agregate în sql este, de asemenea, acoperit. Va fi demontat exemple concrete cereri

Numele coloanelor din interogări pot fi redenumite. Acest lucru face rezultatele mai lizibile.

În SQL, redenumirea câmpurilor implică utilizarea cuvânt cheie AS, care este folosit pentru a redenumi numele câmpurilor în seturile de rezultate

Sintaxă:

SELECTAȚI<имя поля>LA FEL DE<псевдоним>DIN...

Să ne uităm la un exemplu de redenumire în SQL:

Exemplu de baza de date „Institut”: Afișați numele profesorilor și salariile acestora, pentru acei profesori al căror salariu este sub 15.000, 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ă ne uităm la un exemplu:

Exemplu de baza de date „Institut”: Din tabelul profesorilor, afișați câmpul de nume și calculați valoarea salariului și bonusului, denumind câmpul "bonus_salariu"

✍ 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

Pentru a obține valori totale și a evalua expresii, se folosesc funcții de agregare în sql:

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 calcul.

Important: Când lucrați cu funcții agregate în SQL, este folosit un cuvânt funcție LA FEL DE

Exemplu de baza de date „Institut”: Obțineți valoarea celui mai mare salariu dintre profesori, afișați rezultatul ca "salariu_max"

✍ Soluție:

SELECT MAX (zarplata) AS max_salary FROM profesori;

SELECT MAX(zarplata) AS max_salary FROM profesori;

Rezultate:

Să luăm în considerare mai multe exemplu complex folosind funcții de agregare în sql.

✍ Soluție:

Clauza GROUP BY în SQL

Gruparea după operator în sql este de obicei folosit împreună cu funcțiile de agregare.

Funcțiile agregate sunt executate 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, iar funcțiile de agregare sunt executate pentru fiecare grup separat.

Să ne uităm la un exemplu cu tabelul de lecții:

Exemplu:

Important: Astfel, prin utilizarea GROUP BY, toate rândurile de ieșire a interogării sunt împărțite în grupuri caracterizate prin aceleași combinații de valori în acele coloane (adică funcțiile de agregare sunt efectuate pe fiecare grup separat).

Merită să luați în considerare faptul că atunci când grupați după un câmp care conține valori NULL, toate astfel de înregistrări vor intra într-un singur grup.

Pentru tipuri variate imprimantele le identifică cost mediuși cantitate (adică separat pentru laser, 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 verifica valorile, 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. Efectuați calcule numai pentru acele grupuri al căror preț mediu este mai mic de 30.000.

Următoarele subsecțiuni descriu alte propuneri instrucțiunea SELECT, care poate fi folosit în interogări, precum și în funcții agregate și seturi de operatori. Lasă-mă să-ți amintesc asta în acest moment 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 în combinație cu funcțiile de agregare care sunt acceptate în Transact-SQL.

Clauza GROUP BY

Oferi A SE GRUPA CU grupează un set selectat de rânduri pentru a produce un set de rânduri rezumative după valorile uneia sau mai multor coloane sau expresii. Un caz simplu de utilizare a clauzei GROUP BY este prezentat în exemplul de mai jos:

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

Acest exemplu selectează și grupează posturile de angajați.

În exemplul de mai sus, se creează clauza GROUP BY grup separat pentru toți valori posibile(inclusiv Valoare NULL) coloana Job.

Utilizarea coloanelor într-o clauză GROUP BY trebuie să îndeplinească anumite condiții. Mai exact, 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 de agregare sunt acoperite în următoarea subsecțiune.) Acest lucru are sens deoarece Doar coloanele din clauza GROUP BY au o singură valoare pentru fiecare grup.

Puteți grupa un tabel 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:

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 din SELECT.

Funcții agregate

Funcțiile agregate sunt utilizate pentru a obține valori 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 comune

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ărui calcul al funcției 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ă instrucțiunea SELECT nu are o clauză GROUP BY și lista coloanelor select conține macar, o funcție de agregare, atunci nu trebuie să conțină coloane simple (cu excepția coloanelor care servesc drept 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 pentru funcția de agregare. Pe de altă parte, lista de selectare a coloanei poate conține nume de coloane care nu sunt argumente pentru funcția de agregare dacă acele coloane sunt argumente pentru clauza GROUP BY.

Un argument pentru o funcție agregată poate fi precedat de una dintre cele două posibilități: Cuvinte cheie:

TOATE

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

DISTINCT

Specifică faptul că numai valorile unice ale coloanei sunt utilizate pentru calcule.

Funcțiile agregate MIN și MAX

Funcțiile agregate MIN și MAX calculează cel mai mic și cea mai mare valoare coloană în consecință. Dacă o interogare conține o clauză WHERE, funcțiile MIN și MAX returnează cele mai mici și cele mai mari valori ale rândurilor care corespund condițiilor specificate. Exemplul de mai jos arată 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 este posibil să obțineți acest nume de familie în mod obișnuit, deoarece, așa cum am menționat mai devreme, nu aveți voie să specificați în mod explicit coloana LastName. Pentru a obține și numele de familie al acestui angajat împreună cu cel mai mic număr de personal al unui angajat, se folosește o subinterogare. Exemplul de mai jos arată utilizarea unei astfel de subinterogări, în care subinterogarea conține instrucțiunea SELECT din exemplul anterior:

Rezultatul cererii:

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

Funcțiile MIN și MAX pot accepta, de asemenea, șiruri și date ca argumente. În cazul unui argument șir, valorile sunt comparate folosind ordinea reală de sortare. Pentru toate argumentele de date de tip „date”, cea mai mică valoare a coloanei va fi cea mai mare data devreme, iar cel mai mare este cel mai recent.

Puteți utiliza cuvântul cheie DISTINCT cu funcțiile MIN și MAX. Înainte de a utiliza funcțiile de agregare MIN și MAX, toate valorile NULL sunt eliminate din coloanele lor de argumente.

Funcția agregată SUM

Agregat Funcția SUM calculează valoare totală valorile coloanei. Argumentul acestei funcții agregate trebuie să fie întotdeauna un tip de date numerice. Utilizarea funcției de agregare SUM este prezentată în exemplul de mai jos:

UTILIZAȚI SampleDb; SELECT SUM (Buget) „Bugetul total” FROM Proiect;

Acest exemplu calculează suma totală a bugetelor pentru toate proiectele. Rezultatul cererii:

Î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 (la fel ca toate interogările similare). Funcția de grupare implicită din exemplul de mai sus poate fi specificată 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ă toate valorile duplicate dintr-o coloană înainte de a aplica funcția SUM. De asemenea, toate valorile NULL sunt eliminate î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 un 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 pentru proiect” FROM Proiect;

Aici se calculează media valoare aritmetică buget 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 duplicate din coloană sunt eliminate înainte de a utiliza funcția COUNT. 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; SELECT ProjectNumber, COUNT(DISTINCT Job) „Funcționează în proiect” FROM Works_on GROUP BY ProjectNumber;

Aici se numără numărul de posturi diferite pentru fiecare proiect. Rezultatul acestei interogări:

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 poziției s-a 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țiunile interogare SELECT 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 versiune 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 calculează numărul de posturi din toate proiectele. Rezultatul cererii:

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 de tip BIGINT, în timp ce funcția COUNT returnează valori de date de tip INTEGER.

Funcții agregate statistice

Următoarele funcții alcătuiesc grupul 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 unei coloane sau expresii.

STDEVP

Calculează abaterea standard a populaț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ă 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 în detaliu 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ă condiția și conține funcții sau constante agregate.

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

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

În acest exemplu, sistemul grupează toate rândurile după valorile coloanei ProjectNumber folosind clauza GROUP BY. După aceasta, 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 tabelului Works_on după titlul postului și elimină acele locuri de muncă 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 într-un singur grup.

clauza ORDER BY

Oferi COMANDA PENTRU determină 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 indicator alternativ de ordine de sortare care identifică coloanele după ordinea în care apar în lista de selecție a instrucțiunii SELECT (1 este prima coloană, 2 este a doua coloană etc.). parametrul ASC specifică sortarea în ordine crescătoare și parametrul DESC- în sens 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 precum SELECT DISTINCT, deoarece în astfel de interogări, numele coloanelor specificate în clauza ORDER BY trebuie, de asemenea, specificate în lista coloanelor selectate. În plus, această clauză nu poate conține nume de coloane din tabele nespecificate î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:

În acest exemplu, numerele departamentelor și numele angajaților sunt selectate pentru angajații al căror număr de personal este mai mic de 20.000 și sortate după nume și prenume. Rezultatul acestei interogări:

Coloanele din clauza ORDER BY pot fi specificate nu după numele lor, ci după ordinea lor în lista de selectare. În consecință, propoziția din exemplul de mai sus poate fi rescrisă după cum urmează:

Astfel de cale alternativă specificarea coloanelor 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 interogați dacă trebuie adăugate sau eliminate coloane din lista de selectare. Specificarea coloanelor în 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, ordonând rezultatul în ordine descrescătoare după numărul de angajați.

Transact-SQL plasează 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 a pagina rezultatele

Afișarea rezultatelor interogării pe pagina curentă poate fi implementată fie în aplicație utilizator, sau instruiți serverul 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, pe partea serverului, doar rândurile necesare pentru pagina curenta. După cum v-ați putea aștepta, crearea de pagini pe partea serverului oferă de obicei performanță mai bună, deoarece Numai liniile necesare pentru afișare sunt trimise clientului.

Pentru a sprijini crearea paginilor pe partea serverului în 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 sursă), 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 linii de rezultat care trebuie sărit în rezultatul afișat. Acest număr este calculat după ce rândurile sunt sortate folosind clauza ORDER BY. Într-o propoziție FETCH NEXT indică numărul de satisfăcători starea UNDEși rânduri sortate pentru a fi returnate. Parametrul acestei clauze poate fi o constantă, o expresie sau rezultatul unei alte interogări. Clauza FETCH NEXT este similară cu FETCH MAINTI.

Scopul principal atunci când se creează pagini pe partea de server este acela de a putea implementa formulare comune de pagină folosind variabile. Această sarcină poate fi îndeplinită folosind Pachetul SQL Server.

Declarația SELECT și proprietatea IDENTITY

proprietatea IDENTITATE vă permite să definiți valori pentru o anumită coloană a tabelului sub forma unui contor care crește 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 creează 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 doar o coloană cu proprietatea IDENTITY. Proprietarul tabelului are opțiunea de a specifica o valoare de pornire și o creștere, 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); -- 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 create automat de sistem, începând de la 10.000 și crescând în trepte de unitate pentru fiecare valoare ulterioară: 10.000, 10.001, 10.002 etc.

Mai multe funcții și variabile de sistem 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 utilizate 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 sistem. Dar utilizatorul își poate specifica în mod explicit valorile pentru anumite rânduri prin alocarea parametrului IDENTITY_INSERT Valoare ON înainte de a introduce o valoare explicită:

SETARE IDENTITATE INSERT numele tabelului

Deoarece puteți utiliza parametrul IDENTITY_INSERT pentru a seta o coloană cu proprietatea IDENTITY la orice valoare, inclusiv o valoare duplicat, proprietatea IDENTITY nu impune, în general, unicitatea valorilor coloanei. Prin urmare, pentru a impune unicitatea valorilor coloanei, ar trebui să utilizați constrângeri UNIQUE sau PRIMARY KEY.

Când inserați valori într-un tabel după ce ați activat IDENTITY_INSERT, sistemul creează următoarea valoare a coloanei IDENTITY prin incrementul celei mai mari valori curente a acelei coloane.

Instrucțiunea CREATE SEQUENCE

Există mai multe dezavantaje semnificative în utilizarea proprietății IDENTITATE, dintre care cele mai semnificative sunt următoarele:

aplicarea proprietății este limitată la tabelul specificat;

noua valoare a coloanei nu poate fi obținută în alt mod decât 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țiuni 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 create automat de sistem, începând cu valoarea 1 și crescând cu 5 pentru fiecare valoare ulterioară. Astfel, în oferta 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 Este specificată valoarea minimă și maximă a obiectului secvență. (Rețineți că valoarea MINVALUE trebuie să fie mai mică sau egală cu valoarea inițială, 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 setată la NO CYCLE, ceea ce înseamnă că depășirea valorii maxime sau minime a secvenței va genera 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, a performanței. Secvența specifică nu trebuie să fie stocată; este stocată doar ultima sa valoare.)

Noile valori ale secvenței sunt create folosind 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 arată cum să utilizați această expresie 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); --...

În exemplul de mai sus, creăm mai întâi un tabel de produse format din patru coloane. Apoi, două instrucțiuni INSERT inserează două rânduri în acest tabel. Primele două celule ale primei coloane vor avea valorile 11 și 16.

Exemplul de mai jos arată utilizarea unei vizualizări de director sys.secvente pentru a vizualiza valoarea curentă a unei secvențe fără a o folosi:

De obicei, instrucțiunea NEXT VALUE FOR este utilizată într-o instrucțiune INSERT pentru a determina 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ă. Exemplul de mai jos arată utilizarea 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;

Ștergerea unei secvențe folosind o instrucțiune 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 uniune. Î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 în ambele tabele.

Forma generală a operatorului UNION arată astfel:

select_1 UNION select_2 ( select_3])...

Parametrii select_1, select_2, ... sunt instrucțiuni SELECT care creează o îmbinare. Dacă se utilizează opțiunea TOATE, sunt afișate toate rândurile, inclusiv duplicatele. Într-un operator UNION, parametrul ALL are aceeași semnificație ca într-o listă de selecție SELECT, dar cu o diferență: pentru o listă de selecție SELECT, acest parametru este utilizat în mod implicit, dar pentru un operator 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 de reședință al angajaților.

Crearea tabelului EmployeeEnh ne oferă o bună 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 tabelului sursă sunt inserate în masa noua. Numele tabelului nou este specificat în clauza INTO, iar numele tabelului sursă este specificat în clauza FROM.

Exemplul de mai jos arată cum să creați tabelul 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 introduse prin mediu Studio de management 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 instrucțiunii 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 selectate 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 unei îmbinări poate fi ordonat numai utilizând clauza ORDER BY în ultima instructie SELECTAȚI așa cum se arată în exemplul de mai jos. Oferte GRUP BY și HAVING pot fi folosite cu instrucțiuni separate SELECT, dar nu în uniunea în sine.

Interogarea din acest exemplu selectează angajații care fie lucrează în departamentul d1, fie au început să lucreze la proiect înainte de 1 ianuarie 2008.

Operatorul UNION acceptă parametrul ALL. Utilizarea acestei opțiuni nu elimină duplicatele 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 operatori SAU.

Operatorii INTERSECT și EXCEPT

Alți doi operatori pentru lucrul cu seturi, INTERSECTȘi CU EXCEPTIA, determinați intersecția și respectiv diferența. Sub intersecție în acest context există 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 operatorului INTERSECT:

Transact-SQL nu acceptă utilizarea parametrului ALL cu operatorul INTERSECT sau EXCEPT. Utilizarea operatorului EXCEPT este prezentată în exemplul de mai jos:

Rețineți că acești trei operatori de set au priorități de execuție diferite: operator INTERSECT Are cea mai mare prioritate, urmat de operatorul EXCEPT, iar operatorul UNION are cea mai mică prioritate. Neatenție la prioritatea de execuție atunci când se utilizează multiple diferiți operatori pentru lucrul cu seturi poate duce la rezultate neașteptate.

Expresii CASE

În domeniul programării aplicațiilor de baze de date, uneori este necesară modificarea reprezentării datelor. De exemplu, oamenii pot fi subdivizați prin codificarea lor î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 unui program. 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 Transact-SQL permite expresii. Expresia CASE are două forme:

expresie CASE simplă;

expresia de căutare CASE.

Sintaxa pentru o expresie CASE simplă este:

Instructiuni cu expresie simplă CASE caută mai întâi în lista tuturor expresiilor din clauza CÂND prima expresie care se potrivește cu expresia_1, apoi execută cea corespunzătoare THEN clauza. Dacă nu există o expresie care să se potrivească în lista WHEN, clauza ELSE.

Sintaxa expresiei de căutare CASE este următoarea:

ÎN în acest caz, caută prima condiție de potrivire și apoi execută clauza THEN corespunzătoare. Dacă niciuna dintre condiții nu îndeplinește cerințele, se execută clauza ELSE. Utilizarea unei expresii de căutare CASE este prezentată în exemplul de mai jos:

UTILIZAȚI SampleDb; SELECTează Numele Proiectului, CAZUL CÂND Buget > 0 ȘI Buget 100000 ȘI Buget 150000 ȘI Buget

Rezultatul acestei interogări:

Acest exemplu cântărește bugetele tuturor proiectelor și apoi afișează ponderile lor 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; SELECTează 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:

Utilizarea funcțiilor agregate

SQL definește multe funcții încorporate de diferite categorii, printre care un loc special este ocupat de funcțiile agregate, care operează pe valorile coloanelor de mai multe rânduri și returnează o singură valoare. Argumentele pentru agregarea funcțiilor pot fi atât coloane de tabel, cât și rezultatele expresiilor peste acestea. Funcțiile agregate în sine pot fi incluse în alte expresii aritmetice. Următorul tabel prezintă cele mai frecvent utilizate funcții agregate unare standard.

Formatul general al unei funcții agregate unare este următorul:

function_name([TOATE | DISTINCT] expresie)

unde DISTINCT indică faptul că funcția ar trebui să ia în considerare numai sensuri diferite argument și ALL - toate valorile, inclusiv duplicatele (această opțiune este utilizată implicit). De exemplu, funcția AVG cu cuvântul cheie DISTINCT pentru rândurile de coloane cu valorile 1, 1, 1 și 3 va returna 2, iar dacă cuvântul cheie ALL este prezent, va returna 1.5.

Funcțiile agregate sunt utilizate în clauzele SELECT și HAVING. Aici ne vom uita la utilizarea lor în clauza SELECT. În acest caz, expresia din argumentul funcției se aplică tuturor rândurilor din tabelul de intrare al clauzei SELECT. În plus, o clauză SELECT nu poate folosi atât funcții agregate, cât și coloane de tabel (sau expresii cu acestea) în absența unei clauze GROUP BY, pe care o vom analiza în secțiunea următoare.

Funcția COUNT are două formate. În primul caz, este returnat numărul de rânduri din tabelul de intrare; în al doilea caz, este returnat numărul de valori ale argumentului din tabelul de intrare:

• NUMARA(*)
• COUNT(expresie)

Cel mai simplu mod de a utiliza această funcție este să numărați numărul de rânduri dintr-un tabel (toate sau cele care îndeplinesc o condiție specificată). Pentru aceasta, se folosește prima opțiune de sintaxă.

Interogare: numărul de tipuri de produse pentru care sunt disponibile informații în baza de date.

SELECTAȚI COUNT(*) AS „Numărul de tipuri de produse”

DE LA Produs

A doua versiune a sintaxei funcției COUNT poate lua numele unei singure coloane ca argument. În acest caz, se numără fie numărul tuturor valorilor din această coloană a tabelului de intrare, fie numai al celor care nu se repetă (folosind cuvântul cheie DISTINCT).

Interogare: Numărul de nume distincte conținute în tabelul Client.

SELECTAȚI NUMĂR (FNAME DISTINCT)

DE LA Client

Utilizarea funcțiilor agregate unare rămase este similară cu COUNT, cu excepția faptului că pentru funcțiile MIN și MAX, utilizarea cuvintelor cheie DISTINCT și ALL nu are sens. Cu funcțiile COUNT, MAX și MIN, pe lângă câmpurile numerice, pot fi folosite și câmpuri de caractere. Dacă argumentul unei funcții agregate nu conține valori, funcția COUNT returnează 0 și toate celelalte returnează NULL.

SELECTARE MAX (Data Comanda)

DIN

WHERE OrdDate"1.09.2010"

Sarcina pentru muncă independentă: Formulați interogări în SQL pentru a prelua următoarele date:

• Costul total al tuturor comenzilor;
• Numărul de orașe diferite conținut în tabelul Client.

Poate produce generalizat procesare de grup valorile câmpului. Acest lucru se face folosind funcții agregate. Funcțiile agregate produc o singură valoare pentru un întreg grup de tabele. SQL oferă următoarele funcții agregate:

• NUMARA– numără numărul de rânduri de tabel cu valori non-NULL ale câmpului specificat ca argument.
• SUMĂ– calculează suma aritmetică a tuturor valorilor selectate pentru un anumit câmp.
• AVG– face media tuturor valorilor selectate din acest câmp.
• MAX– afișează cea mai mare valoare dintre toate valorile selectate pentru acest câmp.
• MIN– afișaje cea mai mică valoare din toate valorile selectate ale acestui câmp.

  Utilizarea funcțiilor agregate

  Funcțiile agregate sunt utilizate în mod similar cu numele câmpurilor din clauza SELECT a unei interogări, cu o singură excepție: ele iau numele câmpurilor ca argument. Numai câmpuri numerice poate fi folosit cu SUMĂȘi AVG. CU NUMARA, MAX, Și MIN Pot fi utilizate atât câmpurile numerice, cât și câmpurile de caractere. Când este utilizat cu câmpuri de caractere MAXȘi MIN le va traduce în echivalentul ASCII. Înseamnă că MINîl va alege pe primul și MAX ultima valoare în ordine alfabetică.

  Pentru a găsi valoarea totală a vânzărilor în tabelul de vânzări, trebuie să scriem următoarea interogare:

  SELECTAȚI SUM(Sum) FROM Sells

  Ca rezultat obținem:

  Această interogare a numărat numărul de valori non-nule din câmpul SNum din tabelul Sells. Dacă rescriem interogarea după cum urmează:

  SELECTAȚI COUNT(SDate) FROM Vânzări

  Ca rezultat obținem:

  COUNT OF SDdate

  4

  Se obțin rezultate diferite de interogare atunci când se calculează ceea ce pare a fi același lucru, deoarece una dintre valorile câmpului SDate este goală ( NUL). Fiți atenți când utilizați astfel de interogări.