Naučićemo da sumiramo. Ne, ovo još nisu rezultati proučavanja SQL-a, već rezultati vrijednosti stupaca tablica baze podataka. SQL agregatne funkcije djeluju na vrijednosti stupaca kako bi proizvele jednu vrijednost rezultata. Najčešće korištene SQL agregatne funkcije su SUM, MIN, MAX, AVG i COUNT. Potrebno je razlikovati dva slučaja korištenja agregatnih funkcija. Prvo, agregatne funkcije se koriste same i vraćaju jednu rezultujuću vrijednost. Drugo, agregatne funkcije se koriste sa SQL GROUP BY klauzulom, odnosno sa grupiranjem po poljima (kolonama) kako bi se dobile vrijednosti rezultata u svakoj grupi. Razmotrimo prvo slučajeve korištenja agregatnih funkcija bez grupiranja.
SQL SUM funkcija
SQL SUM funkcija vraća zbroj vrijednosti stupca u tablici baze podataka. Može se primijeniti samo na stupce čije su vrijednosti brojevi. SQL upiti za dobivanje rezultujuće sume počinju ovako:
ODABIR SUM (COLUMN_NAME) ...
Nakon ovog izraza slijedi FROM (TABLE_NAME), a zatim se uvjet može specificirati korištenjem klauzule WHERE. Osim toga, DISTINCT se može navesti ispred naziva kolone, što znači da će se računati samo jedinstvene vrijednosti. Prema zadanim postavkama, sve vrijednosti se uzimaju u obzir (za to možete posebno navesti ne DISTINCT, već SVE, ali riječ SVE je opcionalna).
Primjer 1. Postoji baza podataka kompanije sa podacima o njenim sektorima i zaposlenima. Tabela osoblja, pored svega, ima i kolonu sa podacima o platama zaposlenih. Izbor iz tabele je sledeći (za uvećanje slike kliknite na nju levim tasterom miša):
Da bismo dobili zbir svih plata, koristimo sljedeći upit:
ODABERITE ZUM (Platu) OD osoblja
Ovaj upit će vratiti 287664.63.
I sada . U vježbama već počinjemo da komplikujemo zadatke, približavajući ih onima koji se susreću u praksi.
SQL MIN funkcija
SQL MIN funkcija također radi na stupcima čije su vrijednosti brojevi i vraća minimum svih vrijednosti u stupcu. Ova funkcija ima istu sintaksu kao i funkcija SUM.
Primjer 3. Baza podataka i tabela su iste kao u primjeru 1.
Potrebno je saznati minimalnu plaću zaposlenih u odjeljenju sa brojem 42. Da biste to učinili, napišite sljedeći zahtjev:
Zahtjev će vratiti vrijednost 10505.90.
I opet vježbe samopomoći... U ovoj i nekim drugim vežbama biće vam potrebna ne samo tabela osoblja, već i tabela organizacije koja sadrži podatke o divizijama kompanije:
Primjer 4. Tabela Org se dodaje tabeli osoblja, koja sadrži podatke o divizijama firme. Prikažite minimalni broj godina koje je jedan zaposlenik radio u odjelu koji se nalazi u Bostonu.
SQL MAX funkcija
SQL MAX funkcija radi slično i ima sličnu sintaksu, koja se koristi kada trebate odrediti maksimalnu vrijednost među svim vrijednostima u stupcu.
Primjer 5.
Potrebno je saznati maksimalnu platu zaposlenih u odjeljenju broj 42. Da biste to učinili, napišite sljedeći zahtjev:
Zahtjev će vratiti vrijednost 18352.80
Vrijeme je vježbe za samostalno rješavanje.
Primjer 6. Ponovo radimo sa dva stola - Staff i Org. Odštampajte naziv odjela i maksimalne provizije koje je zaradio jedan zaposlenik u odjelu koji pripada Istočnom odjelu. Koristi JOIN (spojiti tabele) .
SQL AVG funkcija
Gornja sintaksa za prethodno opisane funkcije vrijedi i za SQL AVG funkciju. Ova funkcija vraća prosjek svih vrijednosti u koloni.
Primjer 7. Baza podataka i tabela su iste kao u prethodnim primjerima.
Pretpostavimo da želite saznati prosječno radno iskustvo zaposlenih u odjeljenju broj 42. Da biste to učinili, napišite sljedeći upit:
Rezultat će biti vrijednost od 6,33
Primjer 8. Radimo sa jednim stolom - Osoblje. Povući prosječnu platu zaposlenih sa iskustvom od 4 do 6 godina.
SQL funkcija COUNT
SQL COUNT funkcija vraća broj zapisa u tablici baze podataka. Ako navedete SELECT COUNT (COLUMN_NAME) ... u upitu, rezultat će biti broj zapisa isključujući one zapise u kojima je vrijednost stupca NULL (nedefinirana). Ako koristite zvjezdicu kao argument i započnete upit SELECT COUNT (*) ..., rezultat će biti broj svih zapisa (redova) u tabeli.
Primjer 9. Baza podataka i tabela su iste kao u prethodnim primjerima.
Potrebno je saznati broj svih zaposlenih koji primaju provizije. Broj zaposlenih čije vrijednosti Comm stupca nisu NULL vratit će sljedeći upit:
SELECT COUNT (Comm) OD osoblja
Rezultat je 11.
Primjer 10. Baza podataka i tabela su iste kao u prethodnim primjerima.
Ako trebate saznati ukupan broj zapisa u tablici, tada koristimo upit sa zvjezdicom kao argument funkciji COUNT:
ODABIR BROJ (*) IZ osoblja
Rezultat je 17.
U sljedećem vježbe samopomoći morat ćete koristiti potupit.
Primjer 11. Radimo sa jednim stolom - Osoblje. Prikažite broj zaposlenih u odjelu za planiranje (Plains).
Agregatne funkcije sa SQL GROUP BY (Grupiranje)
Pogledajmo sada korištenje agregatnih funkcija u sprezi sa SQL GROUP BY klauzulom. SQL GROUP BY klauzula se koristi za grupiranje vrijednosti rezultata po stupcima tablice baze podataka.
Primjer 12. Postoji baza podataka portala oglasa. Sadrži tabelu Oglasi koja sadrži podatke o oglasima poslanim za sedmicu. Kolona Kategorija sadrži podatke o velikim kategorijama oglasa (npr. Nekretnine), dok kolona Dijelovi sadrži podatke o manjim dijelovima koji su uključeni u kategoriju (npr. dijelovi Stanovi i Vile su dijelovi kategorije Nekretnine). Kolona Jedinice sadrži podatke o broju dostavljenih oglasa, a kolona Novac iznos primljenog novca za slanje oglasa.
| Kategorija | dio | Jedinice | Novac |
| Transport | Motorna vozila | 110 | 17600 |
| Nekretnina | Apartmani | 89 | 18690 |
| Nekretnina | Vikendice | 57 | 11970 |
| Transport | Motocikli | 131 | 20960 |
| Građevinski materijal | Daske | 68 | 7140 |
| Elektrotehnika | TV prijemnici | 127 | 8255 |
| Elektrotehnika | Frižideri | 137 | 8905 |
| Građevinski materijal | Regips | 112 | 11760 |
| Slobodno vrijeme | Knjige | 96 | 6240 |
| Nekretnina | Kuće | 47 | 9870 |
| Slobodno vrijeme | Muzika | 117 | 7605 |
| Slobodno vrijeme | Igre | 41 | 2665 |
Koristeći SQL GROUP BY izraz, pronađite količinu novca zarađenu prikazivanjem oglasa u svakoj kategoriji. Pišemo sljedeći zahtjev.
Lekcija će pokriti temu sql preimenovanja kolone (polja) koristeći AS funkciju riječi; također je pokrio temu agregatnih funkcija u sql. Analizirat će se konkretni primjeri zahtjeva
Možete preimenovati imena kolona u upitima. Ovo čini rezultate čitljivijim.
U SQL-u, preimenovanje polja je povezano s upotrebom ključna riječ AS koji se koristi za preimenovanje imena polja u skupovima rezultata
sintaksa:
SELECT<имя поля>AS<псевдоним>OD ...
Pogledajmo primjer preimenovanja u SQL-u:
Primjer DB "Institut": Prikažite imena nastavnika i njihove plate, za one nastavnike čije su plate ispod 15000 preimenujte polje zarplata u "niska_plata"
✍ Rješenje:
Preimenovanje stupaca u SQL-u je često potrebno prilikom izračunavanja vrijednosti povezanih s više polja stolovi. Razmotrimo primjer:
Primjer DB "Institut": Iz tabele nastavnika ispišite ime polja i izračunajte iznos plate i bonusa, imenujući polje "Plata_ nagrada"
✍ Rješenje:
| 1 2 | SELECT ime, (zarplata + premia) AS zarplata_premia OD nastavnika; |
SELECT ime, (zarplata + premia) AS zarplata_premia OD nastavnika;
rezultat:
Agregatne funkcije u SQL-u
Agregatne funkcije u sql-u se koriste za dobivanje zbroja i izračunavanje izraza:
Sve agregatne funkcije vraćaju jednu vrijednost.
Funkcije COUNT, MIN i MAX primjenjuju se na bilo koji tip podataka.
Funkcije SUM i AVG koriste se samo za numerička polja.
Postoji razlika između funkcija COUNT (*) i COUNT (): druga ne uzima u obzir NULL vrijednosti prilikom brojanja.
Bitan: kada se radi sa agregatnim funkcijama u SQL-u, koristi se posebna riječ AS
Primjer DB "Institut": Dobijte vrijednost najveće plate među nastavnicima, prikažite rezultat kao "Max_zp"
✍ Rješenje:
| SELECT MAX (zarplata) AS max_zp FROM nastavnika; |
SELECT MAX (zarplata) AS max_zp FROM nastavnika;
Rezultati:
Razmotrimo složeniji primjer korištenja agregatnih funkcija u sql-u.
✍ Rješenje:
GROUP BY klauzula u SQL-u
Grupa po klauzuli u sql-u se obično koristi u sprezi sa agregatnim funkcijama.
Agregatne funkcije se izvode na svim rezultujućim redovima upita. Ako upit sadrži GROUP BY klauzulu, svaki skup redova specificiranih u GROUP BY klauzuli čini grupu i agregatne funkcije se izvršavaju za svaku grupu posebno.
Razmotrite primjer s tabelom lekcija: 
primjer:
Bitan: Dakle, kao rezultat korištenja GROUP BY, svi izlazni redovi upita su podijeljeni u grupe koje karakteriziraju iste kombinacije vrijednosti u ovim stupcima (tj. agregatne funkcije se izvode za svaku grupu posebno).
Treba imati na umu da kada se grupišu po polju koje sadrži NULL vrijednosti, svi takvi zapisi će pasti u jednu grupu.
Za različite tipove štampača odredite njihovu prosječnu cijenu i količinu (tj. zasebno za laserske, inkjet i matrične). Koristite agregatne funkcije. Rezultat bi trebao izgledati ovako:
Imati SQL izjavu
Klauzula HAVING u SQL-u je potrebna za provjeru vrijednosti, koji se dobijaju pomoću agregatne funkcije nakon grupisanja(nakon upotrebe GROUP BY). Takav ček ne može biti sadržan u klauzuli WHERE.
primjer: DB prodavnica računara... Izračunajte prosječnu cijenu računara sa istom brzinom procesora. Računajte samo za one grupe čija je prosječna cijena manja od 30.000.
Sljedeći pododjeljci opisuju druge SELECT klauzule koje se mogu koristiti u upitima, kao i agregatne funkcije i skupove izraza. Da vas podsjetim da smo do sada pokrili upotrebu klauzule WHERE, au ovom članku ćemo pogledati klauzule GROUP BY, ORDER BY i HAVING i dati neke primjere korištenja ovih klauzula u kombinaciji sa agregatnim funkcijama koji su podržani u Transact-SQL-u.
GROUP BY klauzula
Rečenica GROUP BY grupiše odabrani skup redova da dobije skup sažetih redova na osnovu vrijednosti jedne ili više kolona ili izraza. Jednostavan slučaj upotrebe klauzule GROUP BY prikazan je u primjeru ispod:
USE SampleDb; SELECT Job FROM Works_On GROUP BY Job;
U ovom primjeru, pozicije zaposlenika su uzorkovane i grupisane.
U gornjem primjeru, klauzula GROUP BY stvara zasebnu grupu za sve moguće vrijednosti (uključujući NULL) za stupac Zadatak.
Upotreba stupaca u klauzuli GROUP BY mora ispunjavati određene uvjete. Konkretno, svaka kolona u odabranoj listi upita također se mora pojaviti u klauzuli GROUP BY. Ovaj zahtjev se ne odnosi na konstante i stupce koji su dio agregatne funkcije. (O agregatnim funkcijama se govori u sljedećem pododjeljku.) Ovo ima smisla jer samo kolone u klauzuli GROUP BY imaju zagarantovanu jednu vrijednost po grupi.
Tabela se može grupirati prema bilo kojoj kombinaciji svojih kolona. Primjer ispod pokazuje grupiranje redova u tabeli Works_on po dvije kolone:
USE SampleDb; SELECT ProjectNumber, Job FROM Works_On GROUP BY ProjectNumber, Job;
Rezultat ovog upita:
Na osnovu rezultata upita možete vidjeti da postoji devet grupa s različitim kombinacijama broja projekta i naziva. Redoslijed naziva stupaca u klauzuli GROUP BY ne mora biti isti kao u SELECT listi stupaca.
Agregatne funkcije
Agregatne funkcije se koriste za dobivanje vrijednosti sume. Sve agregatne funkcije mogu se podijeliti u sljedeće kategorije:
obične agregatne funkcije;
statističke agregatne funkcije;
korisnički definirane agregatne funkcije;
analitičke agregatne funkcije.
Ovdje ćemo pogledati prve tri vrste agregatnih funkcija.
Redovne agregatne funkcije
Transact-SQL podržava sljedećih šest agregatnih funkcija: MIN, MAX, SUMA, AVG, COUNT, COUNT_BIG.
Sve agregatne funkcije izvode izračune na jednom argumentu, koji može biti ili stupac ili izraz. (Jedini izuzetak je drugi oblik od dvije funkcije, COUNT i COUNT_BIG, odnosno COUNT (*) i COUNT_BIG (*).) Svaka agregatna funkcija procjenjuje se na konstantnu vrijednost koja se pojavljuje u zasebnoj koloni rezultata.
Agregatne funkcije su specificirane u listi stupaca izraza SELECT, koji također može sadržavati klauzulu GROUP BY. Ako izraz SELECT ne uključuje klauzulu GROUP BY, a lista stupaca za odabir sadrži barem jednu agregatnu funkciju, tada ne smije sadržavati jednostavne stupce (osim stupaca koji se koriste kao argumenti agregatnoj funkciji). Stoga je kod u primjeru ispod netačan:
USE SampleDb; SELECT Prezime, MIN (Id) FROM Employee;
Ovdje kolona Prezime u tablici Employee ne bi trebala biti na popisu stupaca odabira jer nije argument agregatnoj funkciji. S druge strane, odabrana lista stupaca može sadržavati nazive stupaca koji nisu argumenti agregatnoj funkciji ako se ti stupci koriste kao argumenti klauzule GROUP BY.
Argumentu agregatne funkcije može prethoditi jedna od dvije moguće ključne riječi:
SVEOznačava da se proračuni izvode za sve vrijednosti u koloni. Ovo je zadana postavka.
DISTINCTOdređuje da se za proračune koriste samo jedinstvene vrijednosti stupaca.
Agregatne funkcije MIN i MAX
Funkcije agregata MIN i MAX izračunavaju najmanju i najveću vrijednost u stupcu, respektivno. Ako upit sadrži klauzulu WHERE, funkcije MIN i MAX vraćaju najmanju i najveću vrijednost reda koje odgovaraju navedenim uvjetima. Primjer u nastavku pokazuje upotrebu agregatne funkcije MIN:
USE SampleDb; - Vraća 2581 SELECT MIN (Id) AS "Minimalna vrijednost ID-a" IZ Employee;
Rezultat vraćen u gornjem primjeru nije baš informativan. Na primjer, prezime zaposlenika koji posjeduje ovaj broj nije poznato. Ali ovo prezime ne možete dobiti na uobičajen način, jer, kao što je ranije spomenuto, ne možete eksplicitno navesti kolonu Prezime. Da bi se dobilo prezime ovog radnika zajedno sa najmanjim kadrovskim brojem zaposlenog, koristi se potupit. Primjer ispod pokazuje upotrebu takvog potupita, gdje potupit sadrži naredbu SELECT iz prethodnog primjera:
Rezultat izvršavanja upita:
Upotreba MAX agregatne funkcije prikazana je u primjeru ispod:
Funkcije MIN i MAX također mogu prihvatiti nizove i datume kao argumente. U slučaju argumenta string, vrijednosti se uspoređuju koristeći stvarni redoslijed sortiranja. Za sve argumente privremenih podataka tipa datuma, najniža vrijednost stupca je najraniji datum, a najviša vrijednost je najnoviji.
Ključna riječ DISTINCT može se koristiti s funkcijama MIN i MAX. Sve NULL vrijednosti se uklanjaju iz kolona argumenata prije upotrebe agregatnih funkcija MIN i MAX.
Zbirna funkcija SUM
Agregat Funkcija SUM izračunava ukupni zbir vrijednosti stupca. Argument ove agregatne funkcije uvijek mora biti numeričkog tipa podataka. Upotreba agregatne funkcije SUM prikazana je u primjeru ispod:
USE SampleDb; SELECT SUM (Budžet) "Ukupni budžet" IZ projekta;
Ovaj primjer izračunava ukupne budžete za sve projekte. Rezultat izvršavanja upita:
U ovom primjeru, agregatna funkcija grupiše sve vrijednosti budžeta projekta i određuje njihov ukupni iznos. Iz tog razloga, upit sadrži implicitnu funkciju grupiranja (kao i svi slični upiti). Implicitna funkcija grupisanja iz gornjeg primjera može se eksplicitno specificirati, kao što je prikazano u primjeru ispod:
USE SampleDb; SELECT SUM (Budžet) "Ukupni budžet" IZ Project GROUP BY ();
Korištenje parametra DISTINCT uklanja sve duple vrijednosti u stupcu prije primjene funkcije SUM. Isto tako, uklonite sve NULL vrijednosti prije primjene ove agregatne funkcije.
Agregatna funkcija AVG
Agregat AVG funkcija vraća aritmetičku sredinu svih vrijednosti u koloni. Argument ove agregatne funkcije uvijek mora biti numeričkog tipa podataka. Sve NULL vrijednosti se uklanjaju iz argumenta prije nego što se primijeni funkcija AVG.
Upotreba AVG agregatne funkcije prikazana je u primjeru ispod:
USE SampleDb; - Vraća 133833 SELECT AVG (budžet) "Prosečan budžet za projekat" IZ projekta;
Ovdje se izračunava aritmetički prosjek budžeta za sve budžete.
Agregatne funkcije COUNT i COUNT_BIG
Agregat COUNT funkcija ima dva različita oblika:
COUNT (naziv_kolca) COUNT (*)
Prvi oblik funkcije broji broj vrijednosti u stupcu col_name. Ako upit koristi ključnu riječ DISTINCT, sve duplicirane vrijednosti stupaca se uklanjaju prije nego što se koristi funkcija COUNT. Ovaj oblik funkcije COUNT ne uzima u obzir NULL vrijednosti prilikom brojanja vrijednosti stupaca.
Upotreba prvog oblika agregatne funkcije COUNT prikazana je u primjeru ispod:
USE SampleDb; SELECT ProjectNumber, COUNT (DISTINCT Job) "Poslovi u projektu" FROM Works_on GROUP BY ProjectNumber;
Ovdje se računa broj različitih pozicija za svaki projekat. Rezultat ovog upita:
Kao što možete vidjeti iz upita u primjeru, NULL vrijednosti nisu uzete u obzir od strane funkcije COUNT. (Zbroj svih vrijednosti u koloni posla je 7, a ne 11 kako bi trebao biti.)
Drugi oblik funkcije COUNT, tj. funkcija COUNT (*) broji broj redova u tabeli. A ako izraz SELECT upita s funkcijom COUNT (*) sadrži klauzulu WHERE s uvjetom, funkcija vraća broj redova koji zadovoljavaju navedeni uvjet. Za razliku od prve verzije funkcije COUNT, drugi oblik ne zanemaruje NULL vrijednosti jer ova funkcija radi na nizovima, a ne na stupcima. Primjer ispod pokazuje korištenje funkcije COUNT (*):
USE SampleDb; SELECT Job AS "Tip posla", COUNT (*) "Potrebni radnici" FROM Works_on GRUPA PO Zadatku;
Ovdje se računa broj postova u svim projektima. Rezultat izvršavanja upita:
COUNT_BIG funkcija ista funkcija kao COUNT. Jedina razlika između njih je u tipu rezultata koji vraćaju: COUNT_BIG uvijek vraća BIGINT vrijednosti, dok COUNT vraća INTEGER vrijednosti podataka.
Statističke agregatne funkcije
Sljedeće funkcije čine grupu statističkih agregatnih funkcija:
VARIzračunava statističku varijansu svih vrijednosti u stupcu ili izrazu.
VARPIzračunava statističku varijansu populacije svih vrijednosti u stupcu ili izrazu.
STDEVIzračunava standardnu devijaciju (koja se izračunava kao kvadratni korijen odgovarajuće varijanse) svih vrijednosti u stupcu ili izrazu.
STDEVPIzračunava standardnu devijaciju kolekcije svih vrijednosti u stupcu ili izrazu.
Korisnički definirane agregatne funkcije
Database Engine također podržava implementaciju korisnički definiranih funkcija. Ova mogućnost omogućava korisnicima da dopune sistemske agregatne funkcije funkcijama koje mogu sami implementirati i instalirati. Ove funkcije predstavljaju posebnu klasu korisnički definiranih funkcija i o njima će se kasnije raspravljati.
HAVING klauzula
U rečenici HAVING definira uvjet koji se primjenjuje na grupu redova. Dakle, ova klauzula ima isto značenje za grupe redova kao klauzula WHERE za sadržaj odgovarajuće tabele. Sintaksa za klauzulu HAVING je sljedeća:
HAVING stanje
Ovdje parametar uvjeta predstavlja uvjet i sadrži agregatne funkcije ili konstante.
Upotreba klauzule HAVING u sprezi sa agregatnom funkcijom COUNT (*) prikazana je u primjeru ispod:
USE SampleDb; - Vraća "p3" SELECT ProjectNumber FROM Works_on GRUPA PO ProjectNumber HAVING COUNT (*)
U ovom primjeru, sistem koristi klauzulu GROUP BY da grupiše sve redove prema vrijednostima u stupcu ProjectNumber. Zatim se broj redova u svakoj grupi i odabiru grupe koje sadrže manje od četiri reda (tri ili manje).
Klauzula HAVING se također može koristiti bez agregatnih funkcija, kao što je prikazano u primjeru ispod:
USE SampleDb; - Vratiće "Konsultant" SELECT Job FROM Works_on GRUPA PO POSAO IMA POSAO KAO "K%";
Ovaj primjer grupiše redove tabele Works_on prema naslovu posla i eliminiše poslove koji ne počinju slovom "K".
Klauzula HAVING se također može koristiti bez klauzule GROUP BY, iako to nije uobičajena praksa. U ovom slučaju, svi redovi tabele se vraćaju u istoj grupi.
ORDER BY klauzula
Rečenica POREDAK PO određuje redoslijed sortiranja redova u skupu rezultata koji vraća upit. Ova klauzula ima sljedeću sintaksu:
Redoslijed sortiranja je specificiran u parametru col_name. Col_number je alternativni indikator redoslijeda sortiranja koji identificira stupce redoslijedom kojim se pojavljuju na listi odabira SELECT izraza (1 je prva kolona, 2 druga kolona, itd.). ASC parametar definira uzlazni red, i DESC parametar- nizvodno. Podrazumevano je ASC.
Imena kolona u klauzuli ORDER BY ne moraju biti na listi odabranih kolona. Ali ovo se ne odnosi na upite poput SELECT DISTINCT, jer u takvim upitima, nazivi stupaca specificirani u ORDER BY klauzuli također se moraju pojaviti na listi odabranih stupaca. Osim toga, ova klauzula ne može sadržavati nazive stupaca iz tablica koje nisu specificirane u klauzuli FROM.
Kao što možete vidjeti iz sintakse ORDER BY klauzule, sortiranje skupa rezultata može se obaviti na više kolona. Ovo sortiranje je prikazano u primjeru ispod:
U ovom primjeru se biraju brojevi odjeljenja i prezimena i imena zaposlenih za zaposlenike čiji je broj osoblja manji od 20.000, kao i sortiranje po prezimenu i imenu. Rezultat ovog upita:
Kolone u ORDER BY klauzuli mogu se specificirati ne njihovim imenima, već redoslijedom na listi za odabir. Shodno tome, rečenica u gornjem primjeru može se prepisati na sljedeći način:
Ovaj alternativni način specificiranja stupaca po njihovoj poziciji umjesto po nazivima koristi se ako kriterij naručivanja sadrži agregatnu funkciju. (Drugi način je da koristite nazive stupaca, koji se zatim pojavljuju u ORDER BY klauzuli.) Međutim, u ORDER BY klauzuli, preporučuje se da navedete stupce njihovim imenima, a ne brojevima, kako biste olakšali ažuriranje upita ako trebate dodati ili ukloniti stupce na popisu odabira. Određivanje stupaca u ORDER BY klauzuli njihovim brojevima prikazano je u primjeru ispod:
USE SampleDb; SELECT ProjectNumber, COUNT (*) "Broj zaposlenih" IZ Works_on GROUP BY ProjectNumber ORDER BY 2 DESC;
Ovdje se za svaki projekat odabire broj projekta i broj zaposlenih koji u njemu učestvuju, slijeđujući rezultat u opadajućem redoslijedu prema broju zaposlenih.
Transact-SQL postavlja NULL vrijednosti na vrh liste kada se sortiraju rastućim redoslijedom, a NULL vrijednosti na kraju liste kada se sortiraju u opadajućem redoslijedu.
Korištenje ORDER BY klauzule za paginaciju rezultata
Prikazivanje rezultata upita na trenutnoj stranici može se ili implementirati u prilagođenu aplikaciju, ili možete naložiti poslužitelju baze podataka da to učini. U prvom slučaju, svi redovi baze podataka se šalju aplikaciji, čiji je zadatak da odabere tražene redove i prikaže ih. U drugom slučaju, samo redovi potrebni za trenutnu stranicu se preuzimaju i prikazuju sa strane servera. Kao što možete očekivati, kreiranje stranica na strani servera obično pruža najbolje performanse jer klijentu se šalju samo linije koje su potrebne za prikaz.
Kako bi podržao kreiranje stranice na strani servera, SQL Server 2012 uvodi dvije nove klauzule SELECT: OFFSET i FETCH. Primjena ove dvije rečenice prikazana je u primjeru ispod. Ovdje, iz baze podataka AdventureWorks2012 (koju možete pronaći u izvoru), izvlači poslovni ID, naziv radnog mjesta i rođendan svih zaposlenih žena, sortiran po nazivu radnog mjesta u rastućem redoslijedu. Rezultirajući skup redova se dijeli na stranice od 10 redova i prikazuje se treća stranica:
U rečenici OFFSET specificira broj redova rezultata koje treba preskočiti u prikazanom rezultatu. Ovaj iznos se izračunava nakon što se redovi sortiraju pomoću ORDER BY klauzule. U rečenici DETCH NEXT specificira broj WHERE i sortiranih redova koje treba vratiti. Parametar ove klauzule može biti konstanta, izraz ili rezultat drugog upita. FETCH NEXT klauzula je slična klauzuli PETCH FETCH.
Glavni cilj pri kreiranju stranica na strani servera je biti u mogućnosti implementirati uobičajene forme stranica koristeći varijable. Ovaj zadatak se može postići putem SQL Server paketa.
SELECT izjava i IDENTITY svojstvo
IDENTITY svojstvo omogućava vam da odredite vrijednosti za određeni stupac tablice u obliku automatski inkrementalnog brojača. Stupci numeričkog tipa podataka kao što su TINYINT, SMALLINT, INT i BIGINT mogu imati ovo svojstvo. Za takvu kolonu tabele, Database Engine automatski generiše sekvencijalne vrednosti počevši od navedene početne vrednosti. Stoga se svojstvo IDENTITY može koristiti za generiranje jednocifrenih numeričkih vrijednosti za odabranu kolonu.
Tablica može sadržavati samo jedan stupac sa svojstvom IDENTITY. Vlasnik tabele ima mogućnost da odredi početnu vrednost i inkrement, kao što je prikazano u donjem primeru:
USE SampleDb; KREIRAJ TABELU Proizvod (Id INT IDENTITETA (10000, 1) NIJE NULL, Ime NVARCHAR (30) NOT NULL, Cijena NOVAC) UMETNI U PROIZVOD (IME, Cijena) VRIJEDNOSTI ("Proizvod1", 10), ("Proizvod2", 15) , ("Proizvod3", 8), ("Proizvod4", 15), ("Proizvod5", 40); - Vratiće 10004 SELECT IDENTITYCOL FROM Product WHERE Name = "Proizvod5"; - Analogno prethodnom iskazu SELECT $ identity FROM Product WHERE Naziv = "Proizvod5";
Ovaj primjer prvo kreira tablicu proizvoda koja sadrži stupac Id sa svojstvom IDENTITY. Vrijednosti u koloni Id sistem kreira automatski, počevši od 10.000 i povećavajući se u jednom koraku za svaku narednu vrijednost: 10.000, 10.001, 10.002, itd.
Nekoliko sistemskih funkcija i varijabli pridruženo je svojstvu IDENTITY. Na primjer, primjer koda koristi sistemska varijabla $ identitet... Kao što možete vidjeti iz rezultata pokretanja ovog koda, ova varijabla se automatski poziva na svojstvo IDENTITY. Umjesto toga možete koristiti i sistemsku funkciju IDENTITYCOL.
Početna vrijednost i prirast stupca sa svojstvom IDENTITY mogu se pronaći pomoću funkcija IDENT_SEED i IDENT_INCR respektivno. Ove funkcije se primjenjuju na sljedeći način:
USE SampleDb; SELECT IDENT_SEED ("Proizvod"), IDENT_INCR ("Proizvod")
Kao što je spomenuto, IDENTITY vrijednosti postavlja sistem automatski. Ali korisnik može eksplicitno odrediti njihove vrijednosti za određene nizove dodjeljivanjem parametru IDENTITY_INSERT vrijednost ON prije umetanja eksplicitne vrijednosti:
SET IDENTITY INSERT ime tablice ON
Budući da se parametar IDENTITY_INSERT može koristiti za postavljanje bilo koje vrijednosti, uključujući duplicirane vrijednosti, na stupcu sa svojstvom IDENTITY, svojstvo IDENTITY obično ne provodi jedinstvenost vrijednosti stupca. Stoga, ograničenja UNIQUE ili PRIMARY KEY treba primijeniti da bi se nametnula jedinstvenost vrijednosti stupca.
Kada umetnete vrijednosti u tabelu nakon postavljanja IDENTITY_INSERT na uključeno, sistem kreira sljedeću vrijednost u koloni IDENTITY, povećavajući najvišu trenutnu vrijednost u toj koloni.
Izjava CREATE SEQUENCE
Upotreba svojstva IDENTITY ima nekoliko značajnih nedostataka, od kojih su najznačajniji:
svojstvo je ograničeno na navedenu tabelu;
nova vrijednost kolone se ne može dobiti na bilo koji drugi način, osim njenom primjenom;
svojstvo IDENTITY se može specificirati samo prilikom kreiranja stupca.
Iz ovih razloga, SQL Server 2012 uvodi sekvence koje imaju istu semantiku kao svojstvo IDENTITY, ali bez prethodno navedenih nedostataka. U ovom kontekstu, sekvenca se odnosi na funkcionalnost baze podataka koja vam omogućava da odredite vrijednosti brojača za različite objekte baze podataka, kao što su stupci i varijable.
Sekvence se kreiraju pomoću instrukcije CREATE SEQUEENCE... Izraz CREATE SEQUENCE definiran je u SQL standardu i podržan je od drugih sistema relacijskih baza podataka kao što su IBM DB2 i Oracle.
Primjer ispod pokazuje kako kreirati sekvencu u SQL Serveru:
USE SampleDb; KREIRAJ SEQUENCE dbo.Sequence1 KAO INT POČINJE SA 1 POVEĆANJEM ZA 5 MINVRIJEDNOSTI 1 MAKSIMALNA VRIJEDNOST 256 CIKLUS;
U gornjem primjeru, vrijednosti Sequence1 se generišu automatski od strane sistema, počevši od vrijednosti od 1 i u koracima od 5 za svaku uzastopnu vrijednost. Dakle, u klauzulu START naznačena je početna vrijednost i in INCREMENT ponuda- korak. (Korak može biti pozitivan ili negativan.)
U sljedeće dvije fakultativne rečenice MINVRIJEDNOST i MAXVALUE specificira minimalnu i maksimalnu vrijednost objekta sekvence. (Imajte na umu da MINVALUE mora biti manji ili jednak početnoj vrijednosti, a MAXVALUE ne može biti veći od gornje granice tipa podataka specificiranog za niz.) U rečenici CIKLUS označava da se sekvenca ponavlja od početka kada se prekorači maksimalna (ili minimalna za sekvencu sa negativnim korakom) vrijednost. Po defaultu, ova klauzula je postavljena na NO CYCLE, što znači da prekoračenje maksimalne ili minimalne vrijednosti sekvence izaziva izuzetak.
Glavna karakteristika sekvenci je njihova nezavisnost od tabela, tj. mogu se koristiti sa bilo kojim objektom baze podataka kao što su stupci tablice ili varijable. (Ovo svojstvo ima pozitivan učinak na pohranu, a samim tim i na performanse. Određeni niz ne mora biti pohranjen; pohranjuje se samo njegova posljednja vrijednost.)
Nove vrijednosti sekvence se kreiraju sa SLJEDEĆA VRIJEDNOST ZA izraze, čija je primjena prikazana u primjeru ispod:
USE SampleDb; - Vraća 1 SELECT SLJEDEĆA VRIJEDNOST ZA dbo.sequence1; - Vraća 6 (sljedeći korak) SELECT SLJEDEĆA VRIJEDNOST ZA dbo.sequence1;
Možete koristiti izraz SLJEDEĆA VRIJEDNOST FOR da dodijelite rezultat niza varijabli ili ćeliji stupca. Donji primjer ilustruje upotrebu ovog izraza za dodjelu rezultata koloni:
USE SampleDb; CREATE TABLE Proizvod (Id INT NOT NULL, Naziv NVARCHAR (30) NOT NULL, Cijena NOVAC) UMETNI U VRIJEDNOSTI proizvoda (SLJEDEĆA VRIJEDNOST ZA dbo.sequence1, "Proizvod1", 10); UMETNI U VRIJEDNOSTI proizvoda (SLJEDEĆA VRIJEDNOST ZA dbo.sequence1, "Proizvod2", 15); - ...
Gornji primjer prvo kreira tablicu pod nazivom Proizvod sa četiri stupca. Zatim, dva INSERT izraza ubacuju dva reda u ovu tabelu. Prve dvije ćelije prve kolone bit će 11 i 16.
Primjer ispod pokazuje korištenje kataloškog pogleda sys.sequences da vidite trenutnu vrijednost sekvence bez korištenja:
Tipično, izraz NEXT VALUE FOR se koristi u INSERT izrazu za instrukciju sistemu da ubaci generirane vrijednosti. Ovaj izraz se također može koristiti kao dio višelinijskog upita koji koristi klauzulu OVER.
Da biste promijenili svojstvo postojeće sekvence, primijenite ALTER SEQUENCE izraz... Jedna od najvažnijih upotreba ove naredbe je opcija RESTART WITH, koja resetuje navedeni niz. Primjer u nastavku pokazuje upotrebu naredbe ALTER SEQUENCE za resetiranje gotovo svih svojstava Sequence1:
USE SampleDb; ALTER SEQUENCE dbo.sequence1 RESTART SA 100 POVEĆANJA ZA 50 MIN VRIJEDNOSTI 50 MAKSIMALNE VRIJEDNOSTI 200 BEZ CIKLUSA;
Slijed se briše pomoću instrukcije DROP SEQUENCE.
Postavite operatore
Pored operatora o kojima smo ranije govorili, Transact-SQL podržava tri druga skupa operatora: UNION, INTERSECT i EXCEPT.
UNION operater
UNION operater kombinuje rezultate dva ili više upita u jedan skup rezultata koji uključuje sve redove koji pripadaju svim upitima u uniji. Posljedično, rezultat spajanja dvije tablice je nova tablica koja sadrži sve redove u jednoj ili obje originalne tablice.
Opšti oblik UNION operatora izgleda ovako:
select_1 UNION select_2 (select_3]) ...
Opcije select_1, select_2, ... su SELECT izjave koje kreiraju spajanje. Ako se koristi parametar ALL, prikazuju se svi redovi, uključujući duplikate. U naredbi UNION, parametar ALL ima isto značenje kao u SELECT, ali s jednom razlikom: ovo je zadana vrijednost za SELECT, ali mora biti eksplicitno specificirana za UNION.
U svom originalnom obliku, SampleDb nije prikladan za demonstraciju upotrebe UNION operatora. Stoga, ovaj odjeljak kreira novu tablicu EmployeeEnh koja je identična postojećoj tablici Employee, ali ima dodatni stupac Grad. Ova kolona označava mjesto prebivališta zaposlenih.
Kreiranje tabele EmployeeEnh pruža nam priliku da demonstriramo upotrebu klauzule. INTO u SELECT izrazu. Naredba SELECT INTO izvodi dvije operacije. Prvo se kreira nova tabela sa stupcima navedenim na listi SELECT. Zatim se redovi iz originalne tabele ubacuju u novu tabelu. Ime nove tablice je navedeno u INTO klauzuli, a ime izvorne tablice navedeno je u klauzuli FROM.
Primjer ispod prikazuje kreiranje tabele EmployeeEnh iz tabele Employee:
USE SampleDb; SELECT * INTO EmployeeEnh FROM Employee; ALTER TABLE EmployeeEnh DODAJ Grad NCHAR (40) NULL;
U ovom primjeru, naredba SELECT INTO kreira tablicu EmployeeEnh, u nju umeće sve redove iz izvorne tablice Employee, a zatim naredba ALTER TABLE dodaje stupac City u novu tablicu. Ali dodana kolona Grad ne sadrži nikakve vrijednosti. Vrijednosti se mogu umetnuti u ovu kolonu kroz Management Studio ili korištenjem sljedećeg koda:
USE SampleDb; UPDATE EmployeeEnh SET Grad = "Kazan" GDJE Id = 2581; UPDATE EmployeeEnh SET City = "Moskva" GDJE Id = 9031; UPDATE EmployeeEnh SET Grad = "Jekaterinburg" GDJE Id = 10102; UPDATE EmployeeEnh SET Grad = "Sankt Peterburg" GDJE Id = 18316; UPDATE EmployeeEnh SET Grad = "Krasnodar" GDJE Id = 25348; UPDATE EmployeeEnh SET Grad = "Kazan" GDJE Id = 28559; UPDATE EmployeeEnh SET Grad = "Perm" GDJE Id = 29346;
Sada smo spremni da demonstriramo upotrebu izjave UNIJE. Primjer u nastavku prikazuje upit za kreiranje spajanja između EmployeeEnh i Department tablica koristeći ovu naredbu:
USE SampleDb; ODABERITE Grad KAO "Grad" IZ UNIONA EmployeeEnh ODABIRITE lokaciju IZ Odjela;
Rezultat ovog upita:
Samo kompatibilne tablice mogu se spojiti pomoću UNION izraza. Pod kompatibilnim tabelama podrazumijevamo da obje liste kolona u selekciji moraju sadržavati isti broj kolona, a odgovarajuće kolone moraju imati kompatibilne tipove podataka. (U smislu kompatibilnosti, tipovi podataka INT i SMALLINT nisu kompatibilni.)
Rezultat konkatenacije se može poredati samo pomoću ORDER BY klauzule u posljednjoj SELECT naredbi, kao što je prikazano u primjeru ispod. Klauzule GROUP BY i HAVING mogu se koristiti sa pojedinačnim SELECT naredbama, ali ne unutar samog spoja.
Upit u ovom primjeru odabire zaposlenike koji ili rade u odjelu d1 ili koji su počeli raditi na projektu prije 1. januara 2008.
UNION operator podržava opciju SVE. Ova opcija ne uklanja duplikate iz skupa rezultata. OR operator se može koristiti umjesto UNION operatora ako se svi SELECT izrazi spojeni s jednim ili više UNION operatora odnose na istu tablicu. U ovom slučaju, skup SELECT izraza je zamijenjen jednim SELECT izrazom sa skupom OR naredbi.
INTERSECT i EXCEPT operatori
Dva druga operatera za rad sa setovima, INTERSECT i OSIM, definiraju sjecište i razliku, respektivno. Ispod presjeka, u ovom kontekstu, nalazi se skup redova koji pripadaju objema tablicama. A razlika između dvije tablice je definirana kao sve vrijednosti koje pripadaju prvoj tablici, a nisu prisutne u drugoj. Primjer ispod pokazuje upotrebu INTERSECT operatora:
Transact-SQL ne podržava upotrebu parametra ALL sa INTERSECT ili EXCEPT operatorima. Upotreba operatora EXCEPT prikazana je u primjeru ispod:
Zapamtite da ova tri operatora skupa imaju različit prioritet izvršenja: INTERSECT ima najveći prioritet, praćen EXCEPT, a UNION ima najmanji prioritet. Ne obraćanje pažnje na prioritet izvršenja kada se koristi nekoliko različitih operatora skupa može dovesti do neočekivanih rezultata.
CASE izrazi
U oblasti aplikativnog programiranja baze podataka, ponekad je potrebno modificirati prezentaciju podataka. Na primjer, ljudi se mogu podijeliti prema njihovoj društvenoj klasi, koristeći vrijednosti 1, 2 i 3, koje označavaju muškarce, žene i djecu. Ova tehnika programiranja može smanjiti vrijeme potrebno za implementaciju programa. CASE izraz Transact-SQL vam omogućava da lako implementirate ovu vrstu kodiranja.
Za razliku od većine programskih jezika, CASE nije izjava, već izraz. Stoga se izraz CASE može koristiti gotovo bilo gdje gdje Transact-SQL dozvoljava korištenje izraza. Izraz CASE ima dva oblika:
jednostavan CASE izraz;
izraz za pretragu CASE.
Sintaksa za jednostavan CASE izraz je sljedeća:
Naredba s jednostavnim CASE izrazom prvo pretražuje listu svih izraza u WHEN klauzula prvi izraz koji odgovara izrazu_1, a zatim izvršava odgovarajući THEN klauzula... Ako na listi WHEN ne postoji odgovarajući izraz, onda ELSE klauzula.
Sintaksa za CASE izraz za pretraživanje je sljedeća:
U ovom slučaju, traži prvi kvalifikujući uslov, a zatim izvršava odgovarajuću klauzulu THEN. Ako nijedan od uslova ne ispunjava uslove, ELSE klauzula se izvršava. Upotreba izraza za pretragu CASE prikazana je u primjeru ispod:
USE SampleDb; SELECT ProjectName, CASE WHEN Budget> 0 I Budžet 100000 I Budžet 150000 I Budžet
Rezultat ovog upita:
Ovaj primjer ponderira budžete svih projekata i prikazuje izračunate težine zajedno s odgovarajućim nazivima projekata.
Primjer ispod pokazuje drugi način korištenja izraza CASE gdje klauzula WHEN sadrži potupite koji su dio izraza:
USE SampleDb; SELECT ProjectName, CASE WHEN p1.Budget (IZABRAJTE AVG (p2.Budget) IZ Projekta p2) ONDA "iznad proseka" KRAJ "Kategorija budžeta" IZ Projekta p1;
Rezultat ovog upita je sljedeći:
Korištenje agregatnih funkcija
SQL definira mnoge ugrađene funkcije različitih kategorija, među kojima posebno mjesto zauzimaju agregatne funkcije koje rade na vrijednostima stupaca u skupu redova i vraćaju jednu vrijednost. Argumenti agregatnih funkcija mogu biti i kolone tabele i rezultati izraza nad njima. Agregatne funkcije mogu same biti uključene u druge aritmetičke izraze. Sljedeća tabela navodi najčešće korištene standardne unarne agregatne funkcije.
Opći format unarne agregatne funkcije je sljedeći:
function_name ([SVE | DISTINCT] izraz)
gdje DISTINCT označava da funkcija treba uzeti u obzir samo različite vrijednosti argumenata, a SVE - sve vrijednosti, uključujući duplirane vrijednosti (ovo je zadana postavka). Na primjer, funkcija AVG sa ključnom riječi DISTINCT za redove stupaca sa vrijednostima 1, 1, 1 i 3 će vratiti 2, a ako je prisutna ključna riječ ALL, vratit će 1,5.
Agregatne funkcije se koriste u klauzulama SELECT i HAVING. Ovdje ćemo pogledati njihovu upotrebu u klauzuli SELECT. U ovom slučaju, izraz u argumentu funkcije primjenjuje se na sve redove u ulaznoj tablici klauzule SELECT. Osim toga, agregatne funkcije i stupci tablice (ili izrazi s njima) ne mogu se koristiti u SELECT klauzuli osim ako klauzula GROUP BY, o kojoj ćemo raspravljati u sljedećem odjeljku, nije odsutna.
Funkcija COUNT ima dva formata. U prvom slučaju vraća se broj redova u ulaznoj tablici, u drugom slučaju broj vrijednosti argumenata u ulaznoj tablici:
- COUNT (*)
- COUNT (izraz)
Najjednostavniji način korištenja ove funkcije je prebrojavanje broja redova u tabeli (svih ili zadovoljavajući specificirani uslov). Za to se koristi prva sintaksa.
Upit: Broj vrsta proizvoda, informacije o kojima su dostupne u bazi podataka.
SELECT COUNT (*) AS "Broj vrsta proizvoda"
IZ proizvoda
U drugoj varijanti sintakse za funkciju COUNT, ime jedne kolone može se koristiti kao argument. U ovom slučaju broji se ili sve vrijednosti u ovoj koloni ulazne tablice ili samo vrijednosti koje se ne ponavljaju (kada se koristi ključna riječ DISTINCT).
Upit: Broj različitih imena sadržanih u tabeli Kupci.
ODABIR BROJ (DISTINCT FNAME)
OD kupca
Upotreba ostalih unarnih agregatnih funkcija je ista kao i za COUNT, osim što je za funkcije MIN i MAX upotreba ključnih riječi DISTINCT i ALL besmislena. Pored numeričkih polja, znakovna polja se mogu koristiti sa funkcijama COUNT, MAX i MIN. Ako argument agregatne funkcije ne sadrži vrijednosti, funkcija COUNT vraća 0, a sve ostale vraćaju NULL.
ODABIR MAX (Datum narudžbe)
OD
GDJE RedDate "1.09.2010"
Zadatak za samostalno učenje: Formulirajte upite u SQL-u kako biste odabrali sljedeće podatke:
- Ukupni trošak svih narudžbi;
- Broj različitih gradova sadržanih u tabeli kupaca.
Može izvoditi generaliziranu grupnu obradu vrijednosti polja. Ovo se radi pomoću agregatnih funkcija. Agregatne funkcije proizvode jednu vrijednost za cijelu grupu tablice. SQL pruža sljedeće agregatne funkcije:
- COUNT- broji broj redova tablice s vrijednostima koje nisu NULL u polju navedenom kao argument.
- SUMA- izračunava aritmetički zbir svih odabranih vrijednosti datog polja.
- AVG- vrši usrednjavanje svih izabranih vrednosti ovog polja.
- MAX- prikazuje najveću vrijednost od svih odabranih vrijednosti za ovo polje.
- MIN- Izlazi najmanju vrijednost od svih odabranih vrijednosti za ovo polje.
Korištenje agregatnih funkcija
Agregatne funkcije se koriste slično imenima polja u SELECT klauzuli upita, s jednim izuzetkom: uzimaju imena polja kao argumente. Mogu se koristiti samo numerička polja SUMA i AVG... WITH COUNT, MAX, i MIN mogu se koristiti i numerička i karakterna polja. Kada se koristi sa znakovnim poljima MAX i MINće ih prevesti u ASCII ekvivalent. To znači da MINće izabrati prvi i MAX zadnja vrijednost po abecednom redu.
Da bismo pronašli ukupnu prodaju u tabeli prodaje, moramo napisati sljedeći upit:
ODABERITE SUM (SSum) IZ prodaje
Kao rezultat, dobijamo:
Ovaj upit je izbrojao broj nepraznih vrijednosti u polju SNum tabele Sells. Ako prepišemo zahtjev na sljedeći način:
SELECT COUNT (SDate) FROM Sells
Tada kao rezultat dobijamo:
COUNT OF SDate 4 Različiti rezultati upita pri izračunavanju naizgled isti se dobijaju jer jedna od vrijednosti polja SDate ima praznu vrijednost ( NULL). Budite oprezni kada koristite takve zahtjeve.
