Funkcija SUM u SQL jeziku, unatoč svojoj jednostavnosti, često se koristi pri radu s bazom podataka. Uz njegovu pomoć prikladno je dobiti neke srednje ili konačne rezultate bez pribjegavanja pomoći pomoćnih DBMS alata.

Sintaksa funkcije

U većini SQL jezika sintaksa zbroja je ista - kao argument se koristi samo naziv polja ili neka aritmetička operacija više njih, preko kojih se traži zbroj.

U iznimnim slučajevima moguće je prenijeti određenu vrijednost kao broj ili varijablu, ali se takve "sheme" praktički ne koriste, jer nemaju veliku vrijednost. Ispod je sintaksa funkcije: SQL jezik:

sum(a) - ovdje se kao parametar a koristi neka numerička vrijednost ili izraz

Važno je napomenuti da prije parametra možete postaviti ključne riječi, na primjer, DISTINCT ili ALL, koje će uzeti samo jedinstvene ili sve vrijednosti.

Primjer korištenja SUM-a u SQL-u

Da biste u potpunosti razumjeli kako funkcija radi, vrijedi razmotriti nekoliko primjera. U SQL-u, SUM se može koristiti i kao povratni rezultat i kao međuvrijednost, na primjer, za testiranje uvjeta.

Za prvi slučaj razmotrite opciju kada trebate vratiti iznos prodaje za svaki proizvod, uzimajući u obzir da broj obavljenih kupnji može biti u množini. Da biste dobili rezultat, bit će dovoljno pokrenuti sljedeći upit:

SELECT Product, sum(PurchaseAmount) FROM Sales GroupBy Product;

Kao odgovor na ovu naredbu postojat će jedinstveni popis proizvoda s ukupnim iznosom kupnje za svaki od njih.

Za drugi primjer, trebate dobiti popis proizvoda čiji je iznos prodaje premašio određenu vrijednost, na primjer, 100. Rezultat za ovaj zadatak možete dobiti na nekoliko načina, od kojih je najoptimalniji izvršiti jedan zahtjev:

SELECT Product FROM (SELECT Product, sum (Purchase Amount) as Amount FROM Sales) WHERE Sum > 100.

Kako mogu saznati broj modela računala koje je proizveo određeni dobavljač? Kako odrediti prosječnu cijenu računala koja imaju iste tehnički podaci? Na ova i mnoga druga pitanja vezana uz neke statističke informacije može se odgovoriti pomoću finalne (agregatne) funkcije. Norma pruža sljedeće agregatne funkcije:

Sve te funkcije vraćaju jednu vrijednost. Istodobno, funkcije BROJ, MIN I MAKS primjenjiv na bilo koju vrstu podataka, dok IZNOS I PROSJ koriste se samo za numerička polja. Razlika između funkcije RAČUNATI(*) I RAČUNATI(<имя поля>) je da drugi ne uzima u obzir NULL vrijednosti prilikom izračuna.

Primjer. Pronađite minimalnu i maksimalnu cijenu za osobna računala:

Primjer. Pronađite raspoloživi broj računala proizvođača A:

Primjer. Ako nas zanima količina razni modeli, proizveden od strane proizvođača A, tada se upit može formulirati na sljedeći način (koristeći činjenicu da se u tablici proizvoda svaki model bilježi jednom):

Primjer. Pronađite broj dostupnih različitih modela koje proizvodi proizvođač A. Upit je sličan prethodnom, u kojem je trebalo odrediti ukupan broj modela koje proizvodi proizvođač A. Ovdje također trebate pronaći broj različitih modela u PC stol (tj. oni dostupni za prodaju).

Kako bi se osiguralo da se koriste samo jedinstvene vrijednosti prilikom dobivanja statističkih pokazatelja, kada argument agregatnih funkcija može se koristiti DISTINCT parametar. Još parametar SVE je zadana vrijednost i pretpostavlja da su sve vraćene vrijednosti u stupcu prebrojane. Operater,

Ako trebamo dobiti broj proizvedenih PC modela svatko proizvođača, morat ćete koristiti GROUP BY klauzula, sintaktički slijedeći WHERE odredbe.

GROUP BY klauzula

GROUP BY klauzula koristi se za definiranje grupa izlaznih linija na koje se može primijeniti agregatne funkcije (COUNT, MIN, MAX, AVG i SUM). Ako ova klauzula nedostaje, a koriste se agregatne funkcije, tada će svi stupci s imenima navedenim u IZABERI, moraju biti uključeni u agregatne funkcije, a te će se funkcije primijeniti na cijeli skup redaka koji zadovoljavaju predikat upita. Inače, svi stupci liste SELECT nije uključeno u skupnim funkcijama moraju biti navedene u klauzuli GROUP BY. Kao rezultat toga, svi retci izlaznih upita podijeljeni su u grupe koje karakteriziraju iste kombinacije vrijednosti u tim stupcima. Nakon toga, agregatne funkcije će se primijeniti na svaku grupu. Imajte na umu da se za GROUP BY sve NULL vrijednosti tretiraju kao jednake, tj. kod grupiranja prema polju koje sadrži NULL vrijednosti, svi takvi redovi će pasti u jednu grupu.
Ako ako postoji klauzula GROUP BY, u klauzuli SELECT nema agregatnih funkcija, tada će upit jednostavno vratiti jedan redak iz svake grupe. Ova značajka, zajedno s ključnom riječi DISTINCT, može se koristiti za uklanjanje duplih redaka u skupu rezultata.
Pogledajmo jednostavan primjer:

SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price S PC-ja GROUP BY model;

U ovom zahtjevu za svaki model računala utvrđuje se njihov broj i prosječna cijena. Svi redovi s istom vrijednošću modela čine grupu, a izlaz SELECT-a izračunava broj vrijednosti i prosječne vrijednosti cijene za svaku grupu. Rezultat upita bit će sljedeća tablica:

model	Kol_model	Prosječna_cijena
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

Kad bi SELECT imao stupac datuma, tada bi bilo moguće izračunati ove pokazatelje za svaki određeni datum. Da biste to učinili, trebate dodati datum kao stupac grupiranja, a zatim bi se agregatne funkcije izračunale za svaku kombinaciju vrijednosti (model-datum).

Postoji nekoliko specifičnih pravila za izvođenje agregatnih funkcija:

• Ako kao rezultat zahtjeva nema primljenih redaka(ili više od jednog redaka za danu grupu), tada nema izvornih podataka za izračun bilo koje agregatne funkcije. U tom će slučaju rezultat funkcija COUNT biti nula, a rezultat svih ostalih funkcija bit će NULL.
• Argument funkcija agregata ne može sama sadržavati agregatne funkcije(funkcija iz funkcije). Oni. u jednom upitu nemoguće je, recimo, dobiti maksimalnu prosječnu vrijednost.
• Rezultat izvršavanja funkcije COUNT je cijeli broj(CIJELI BROJ). Druge agregatne funkcije nasljeđuju tipove podataka vrijednosti koje obrađuju.
• Ako funkcija SUM daje rezultat koji je veći od maksimalne vrijednosti korištene vrste podataka, greška.

Dakle, ako zahtjev ne sadrži GROUP BY klauzule, To agregatne funkcije uključen u SELECT klauzula, izvršavaju se na svim rezultirajućim recima upita. Ako zahtjev sadrži GROUP BY klauzula, svaki skup redaka koji ima iste vrijednosti stupca ili grupe stupaca navedenih u GROUP BY klauzula, čini grupu, i agregatne funkcije izvode se za svaku skupinu posebno.

IMAJU ponudu

Ako WHERE klauzula definira predikat za filtriranje redaka, zatim IMAJU ponudu primjenjuje se nakon grupiranja za definiranje sličnog predikata koji filtrira grupe prema vrijednostima agregatne funkcije. Ova klauzula je potrebna za provjeru vrijednosti koje su dobivene korištenjem funkcija agregata ne iz pojedinačnih redaka izvora zapisa definiranog u FROM klauzula, i od grupe takvih linija. Stoga takav ček ne može biti sadržan u WHERE klauzula.

RAČUNALSTVO

Funkcije sažetka

Izrazi SQL upita često zahtijevaju pretprocesiranje podataka. U tu svrhu koriste se posebne funkcije i izrazi.

Vrlo često morate saznati koliko zapisa odgovara određenom upitu,koliki je zbroj vrijednosti određenog numeričkog stupca, njegove maksimalne, minimalne i prosječne vrijednosti. U tu svrhu koriste se tzv. konačne (statističke, agregatne) funkcije. Funkcije sažetka obrađuju skupove zapisa specificiranih, na primjer, klauzulom WHERE. Ako ih uključite u popis stupaca nakon naredbe SELECT, rezultirajuća tablica sadržavat će ne samo stupce tablice baze podataka, već i vrijednosti izračunate ovim funkcijama. Sljedeće jepopis funkcija sažetka.

• BROJ (parametar ) vraća broj zapisa navedenih u parametru. Ako želite dobiti broj svih zapisa, trebate navesti simbol zvjezdice (*) kao parametar. Ako navedete naziv stupca kao parametar, funkcija će vratiti broj zapisa u kojima ovaj stupac ima vrijednosti različite od NULL. Da biste saznali koliko različitih vrijednosti sadrži stupac, ispred naziva stupca stavite ključnu riječ DISTINCT. Na primjer:

SELECT COUNT(*) FROM Clients;

SELECT COUNT(Order_Amount) FROM kupaca;

SELECT COUNT(DISTINCT Order_Amount) FROM Customers;

Pokušaj pokretanja sljedećeg upita rezultirat će porukom pogreške:

SELECT Region , COUNT(*) FROM Clients ;

• SUM (parametar ) vraća zbroj vrijednosti stupca navedenog u parametru. Parametar također može biti izraz koji sadrži naziv stupca. Na primjer:

ODABERI ZBIR (Iznos_narudžbe) OD kupaca;

Ova SQL izjava vraća tablicu s jednim stupcem i jednim zapisom koja sadrži zbroj svih definiranih vrijednosti za stupac Order_Amount iz tablice Customers.

Recimo da su u izvornoj tablici vrijednosti stupca Order_Amount izražene u rubljima, a mi moramo izračunati ukupni iznos u dolarima. Ako je trenutni tečaj, na primjer, 27,8, tada možete dobiti traženi rezultat pomoću izraza:

ODABERI ZBIR (Iznos_narudžbe*27.8) OD klijenata;

• AVG (parametar ) vraća aritmetičku sredinu svih vrijednosti stupca navedenog u parametru. Parametar može biti izraz koji sadrži naziv stupca. Na primjer:

SELECT PROSJ. (Iznos_narudžbe) FROM kupaca;

ODABERITE PROSJEČNI (Iznos_narudžbe*27,8) OD klijenata

GDJE Regija<>"Sjever_3zapad";

• MAX (parametar ) vraća maksimalnu vrijednost u stupcu navedenom u parametru. Parametar također može biti izraz koji sadrži naziv stupca. Na primjer:

SELECT MAX(Order_Amount) FROM Clients;

SELECT MAX(Order_Amount*27.8) FROM Clients

GDJE Regija<>"Sjever_3zapad";

• MIN (parametar ) vraća minimalnu vrijednost u stupcu navedenom u parametru. Parametar može biti izraz koji sadrži naziv stupca. Na primjer:

SELECT MIN(Iznos_narudžbe) FROM kupaca;

ODABERITE MIN (Iznos narudžbe*27,8) OD klijenata

GDJE Regija<>"Sjever_3zapad";

U praksi je često potrebno dobiti konačnu tablicu koja sadrži ukupni, prosječni, maksimalni i minimalne vrijednosti numerički stupci. Da biste to učinili, trebali biste koristiti funkcije grupiranja (GROUP BY) i funkcije sažetka.

SELECT Region, SUM (Iznos_narudžbe) OD kupaca

GRUPIRAJ PO Regiji;

Tablica rezultata za ovaj upit sadrži nazive regija i ukupne (ukupne) iznose narudžbi svih kupaca iz pripadajućih regija (slika 5).

Sada razmotrite zahtjev za dobivanje svih sažetih podataka po regijama:

ODABERITE Regiju, SUM (Iznos_narudžbe), PROSJ (Iznos_narudžbe), MAX(Iznos_narudžbe), MIN (iznos_narudžbe)

OD klijenata

GRUPIRAJ PO Regiji;

Izvorna tablica i tablica rezultata prikazane su na sl. 8. U primjeru je samo sjeverozapadna regija predstavljena u izvornoj tablici s više od jednog zapisa. Stoga, u tablici rezultata za njega, različite funkcije sažetka daju različite vrijednosti.

Riža. 8. Konačna tablica iznosa narudžbi po regijama

Kada koristite funkcije sažetka na popisu stupaca u izjavi SELECT, zaglavlja njihovih odgovarajućih stupaca u tablici rezultata su Expr1001, Expr1002 i tako dalje. (ili nešto slično, ovisno o SQL implementaciji). Međutim, možete postaviti zaglavlja za vrijednosti funkcija sažetka i druge stupce prema vlastitom nahođenju. Da biste to učinili, odmah nakon stupca u izjavi SELECT navedite izraz u obliku:

AS zaglavlje_stupca

Ključna riječ AS (as) znači da u tablici rezultata, odgovarajući stupac mora imati zaglavlje navedeno nakon AS. Dodijeljeni naslov naziva se i alias. Sljedeći primjer (slika 9) postavlja pseudonime za sve izračunate stupce:

ODABIR regije,

IZNOS (Iznos_narudžbe) AS [Ukupni iznos narudžbe],

PROSJ (Iznos_narudžbe) KAO [Prosječni iznos narudžbe],

MAX(iznos_narudžbe) KAO maksimum,

MIN (Iznos_narudžbe) AS Minimum,

OD klijenata

GRUPIRAJ PO Regiji;

Riža. 9. Konačna tablica iznosa narudžbi po regijama pomoću aliasa stupaca

Nadimci koji se sastoje od nekoliko riječi odvojenih razmakom stavljaju se u uglate zagrade.

Funkcije sažetka mogu se koristiti u klauzulama SELECT i HAVING, ali se ne mogu koristiti u klauzulama WHERE. Operator HAVING sličan je operatoru WHERE, ali za razliku od WHERE odabire zapise u grupama.

Recimo da želite odrediti koje regije imaju više od jednog klijenta. U tu svrhu možete koristiti sljedeći upit:

ODABERITE Regiju, Broj(*)

OD klijenata

GROUP BY Region HAVING COUNT(*) > 1;

Funkcije obrade vrijednosti

Kada radite s podacima, često ih morate obraditi (pretvoriti u pravi tip): odaberite podniz u nizu, uklonite razmake na početku i na kraju, zaokružite broj, izračunajte kvadratni korijen, odredite trenutno vrijeme itd. U SQL-u postoje sljedeće tri vrste funkcija:

• funkcije niza;
• numeričke funkcije;
• funkcije datum-vrijeme.

String funkcije

String funkcije uzimaju niz kao parametar i nakon obrade vraćaju niz ili NULL.

• PODNIZ (linija OD početka)vraća podniz koji proizlazi iz niza navedenog kao parametar crta . Podniz počinje znakom čiji je redni broj naveden u početnom parametru i ima duljinu specificiranu u parametru duljine. Znakovi u nizu su numerirani slijeva na desno, počevši od 1. Uglate zagrade ovdje samo označavaju da je izraz u njima opcionalan. Ako izraz ZA duljinu se ne koristi, tada podniz iz Početak i do kraja izvorne linije. Vrijednosti parametara početak i dužina mora biti odabran tako da je traženi podniz zapravo unutar izvornog niza. Inače će funkcija SUBSTRING vratiti NULL.

Na primjer:

PODNIZ ("Draga Maša!" OD 9 ZA 4) vraća "Maša";

PODNIZ ("Draga Maša!" OD 9) vraća "Maša!";

SUBSTRING("Draga Maša!" FROM 15) vraća NULL.

Ovu funkciju možete koristiti u SQL izrazu, na primjer, ovako:

ODABIR * OD klijenata

WHERE SUBSTRING(Region FROM 1 FOR 5) = "Sjever";

• GORNJI(niz ) pretvara sve znakove niza navedenog u parametru u velika slova.
• LOWER(niz ) pretvara sve znakove niza navedenog u parametru u mala slova.
• SKRIVANJE (NA VODU | ZADNJE | OBA ["znak"] OD niza ) uklanja znak na početku (LEADING), kraj (TRAILING) ili oba (BOTH) znaka iz niza. Prema zadanim postavkama, znak koji se uklanja je razmak (" "), tako da se može izostaviti. Najčešće se ova funkcija koristi za uklanjanje razmaka.

Na primjer:

TRIM (VODI " " OD "grad St. Petersburg") rotira "grad St. Petersburg";

TRIM(TRALING " " FROM "grad St. Petersburg") vraća "grad St. Petersburg";

TRIM (BOTH " " FROM " grad St. Petersburg ") vraća "grad St. Petersburg";

TRIM(BOTH FROM " city of St. Petersburg ") vraća "city of St. Petersburg";

TRIM(BOTH "g" FROM "city of St. Petersburg") vraća "city of St. Petersburg".

Među tim funkcijama najčešće korištene su SUBSTRING() I TRIM().

Numeričke funkcije

Numeričke funkcije mogu prihvatiti podatke ne samo numeričkog tipa kao parametar, već uvijek vraćaju broj ili NULL (ne specifična vrijednost).

• POZICIJA ( targetString IN niz) traži pojavljivanje ciljnog niza u navedenom nizu. Ako je pretraživanje uspješno, vraća broj pozicije svog prvog znaka, inače 0. Ako ciljni niz ima nultu duljinu (na primjer, niz " "), tada funkcija vraća 1. Ako je barem jedan od parametara NULL , tada se vraća NULL. Znakovi u retku su numerirani s lijeva na desno, počevši od 1.

Na primjer:

POSITION("e" IN "Pozdrav svima") vraća 5;

POSITION ("svi" U "Pozdrav svima") vraća 8;

POSITION(" " Pozdrav svima") vraća 1;

POSITION("Pozdrav!" IN "Pozdrav svima") vraća 0.

U tablici Klijenti (vidi sliku 1), stupac Adresa sadrži, osim naziva grada, poštanski broj, naziv ulice i druge podatke. Možda ćete morati odabrati zapise za kupce koji žive u određenom gradu. Dakle, ako želite odabrati zapise koji se odnose na klijente koji žive u St. Petersburgu, možete koristiti sljedeći izraz SQL upita:

ODABIR * OD klijenata

WHERE POSITION (" St. Petersburg " IN Adresa ) > 0;

Imajte na umu da se ovaj jednostavan zahtjev za dohvaćanje podataka može drugačije formulirati:

ODABIR * OD klijenata

WHERE Adresa LIKE "%Petersburg%";

• EKSTRAKT (parametar ) izvlači element iz vrijednosti datum-vrijeme ili iz intervala. Na primjer:

IZVOD (MJESEC OD DATUMA "2005-10-25") vraća 10.

• CHARACTER_LENGTH(niz ) vraća broj znakova u nizu.

Na primjer:

CHARACTER_LENGTH("Pozdrav svima") vraća 11.

• OCTET_LENGTH(niz ) vraća broj okteta (bajtova) u nizu. Svaki latinični ili ćirilični znak predstavljen je jednim bajtom, a znak kineske abecede predstavljen je s dva bajta.
• KARDINALNOST (parametar ) uzima kolekciju elemenata kao parametar i vraća broj elemenata u kolekciji (kardinalni broj). Zbirka može biti, na primjer, niz ili višeskup koji sadrži elemente različitih tipova.
• ABS (broj ) vraća apsolutnu vrijednost broja. Na primjer:

ABS (-123) vraća 123;

ABS (2 - 5) vraća 3.

• MO D (broj1, broj2 ) vraća ostatak cjelobrojnog dijeljenja prvog broja s drugim. Na primjer:

MOD(5, h) vraća 2;

MOD(2, h) vraća 0.

• LN (broj ) vraća prirodni logaritam broja.
• EXP (broj) vraća broj (baza prirodnog logaritma na potenciju broja).
• SNAGA (broj1, broj2 ) vraća broj1 broj2 (broj1 na potenciju broja2).
• SQRT (broj ) vraća kvadratni korijen broja.
• KAT (broj ) vraća najveći cijeli broj koji ne prelazi onaj naveden parametrom (zaokruživanje prema dolje). Na primjer:

FLOOR (5.123) vraća 5.0.

• CEIL (broj) ili CEILING (broj ) vraća najmanji cijeli broj koji nije manji od vrijednosti navedene parametrom zaokruživanja). Na primjer:

CEIL(5.123) vraća 6.0.

• WIDTH_BUCKET (broj1, broj2, broj3, broj4) vraća cijeli broj u rasponu između 0 i broj4 + 1. Parametri broj2 i broj3 određuju numerički interval podijeljen na jednake intervale, čiji je broj određen parametrom broj4. Funkcija određuje broj intervala u koji pada vrijednost broj1. Ako je broj1 izvan navedenog raspona, tada funkcija vraća 0 ili broj 4 + 1. Na primjer:

WIDTH_BUCKET(3.14, 0, 9, 5) vraća 2.

Datum-vrijeme funkcije

SQL ima tri funkcije koje vraćaju trenutni datum i vrijeme.

• TRENUTNI DATUM vraća trenutni datum (tip DATE).

Na primjer: 2005-06-18.

• CURRENT_TIME (broj ) vraća trenutno vrijeme (VRIJEME tipa). Integer parametar specificira preciznost predstavljanja sekundi. Na primjer, vrijednost 2 predstavljat će sekunde do najbliže stotinke (dva decimalna mjesta):

12:39:45.27.

• CURRENT_TIMESTAMP (broj ) vraća datum i vrijeme (tip TIMESTAMP). Na primjer, 2005-06-18 12:39:45.27. Integer parametar specificira preciznost predstavljanja sekundi.

Imajte na umu da datum i vrijeme koje ove funkcije vraćaju nisu karakterni tip. Ako ih želite predstaviti kao nizove znakova, tada biste trebali koristiti funkciju pretvorbe tipa CAST().

Funkcije datuma i vremena obično se koriste u upitima za umetanje, ažuriranje i brisanje podataka. Na primjer, kada se bilježe podaci o prodaji, trenutni datum i vrijeme upisuju se u za to predviđeni stupac. Nakon zbrajanja rezultata za mjesec ili tromjesečje, podaci o prodaji za izvještajno razdoblje mogu se izbrisati.

Izračunati izrazi

Izračunati izrazi izgrađeni su od konstanti (numeričkih, nizovnih, logičkih), funkcija, naziva polja i drugih vrsta podataka njihovim povezivanjem s aritmetičkim, nizovnim, logičkim i drugim operatorima. S druge strane, izrazi se mogu kombinirati pomoću operatora u složenije (složene) izraze. Zagrade se koriste za kontrolu redoslijeda kojim se izrazi procjenjuju.

Logički operatori I, ILI i NE i funkcije o kojima se ranije raspravljalo.

Aritmetički operatori:

• + zbrajanje;
• - oduzimanje;
• * množenje;
• / podjela.

String operatorsamo jedan operator ulančavanja ili ulančavanja niza (| |). Neke implementacije SQL-a (kao što je Microsoft Access) koriste znak (+) umjesto (| |). Operator ulančavanja dodaje drugi niz na kraj prvog primjera, izraza:

"Saša" | | "voli" | | "mahanje"

vratit će niz "Sasha voli Mashu" kao rezultat.

Prilikom sastavljanja izraza, morate osigurati da su operandi operatora važećeg tipa. Na primjer, izraz: 123 + "Sasha" nije valjan jer se operator aritmetičkog zbrajanja primjenjuje na operand niza.

Izračunati izrazi mogu se pojaviti nakon SELECT izjava, kao i u izrazima uvjeta WHERE i HAVI iskaza N.G.

Pogledajmo nekoliko primjera.

Neka tablica Sales sadrži stupce ProductType, Quantity i Price, a mi želimo znati prihod za svaku vrstu proizvoda. Da biste to učinili, samo uključite izraz Količina*Cijena u popis stupaca iza izjave SELECT:

ODABERITE Product_type, Quantity, Price, Quantity*Price KAO

Ukupno OD prodaje;

Ovo koristi ključnu riječ AS (as) za određivanje pseudonima za stupac izračunatih podataka.

Na sl. Slika 10 prikazuje originalnu tablicu prodaje i tablicu rezultata upita.

Riža. 10. Rezultat upita s izračunom prihoda za svaku vrstu proizvoda

Ako želite saznati ukupan prihod od prodaje sve robe, onda samo koristite sljedeći upit:

ODABERI ZBIR (Količina*Cijena) OD prodaje;

Sljedeći upit sadrži izračunate izraze u popisu stupaca i uvjetu WHERE klauzule. Iz tablice prodaje odabire one proizvode čiji je prihod od prodaje veći od 1000:

ODABERITE Product_type, Quantity*Price AS Total

OD prodaje

WHERE Količina*Cijena > 1000;

Pretpostavimo da želite dobiti tablicu koja ima dva stupca:

Proizvod koji sadrži vrstu i cijenu proizvoda;

Ukupni prihod koji sadrži.

Budući da se pretpostavlja da je u originalnoj prodajnoj tablici stupac Product_Type znakovni (CHAR tip), a stupac Price numerički, prilikom spajanja (lijepljenja) podataka iz ovih stupaca potrebno je brojčani tip pretvoriti u znakovni tip pomoću funkcija CAST(). Upit koji izvršava ovaj zadatak izgleda ovako (slika 11):

SELECT Product_Type | | " (Cijena: " | | CAST(cijena AS CHAR(5)) | | ")" KAO proizvod, količina*cijena kao ukupno

OD prodaje;

Riža. 11. Rezultat upita koji kombinira različite vrste podataka u jednom stupcu

Bilješka. U Microsoft Accessu bi sličan upit izgledao ovako:

ODABERITE Product_type + " (Cijena: " + C Str (Cijena) + ")" KAO proizvod,

Količina*Cijena KAO Ukupno

OD prodaje;

Uvjetni izrazi s CASE naredbom

Konvencionalni programski jezici imaju operatore uvjetnog skoka koji vam omogućuju kontrolu procesa izračunavanja ovisno o tome je li neki uvjet istinit ili ne. U SQL-u ovaj operator je CASE (slučaj, okolnost, primjer). U SQL:2003 ovaj operator vraća vrijednost i stoga se može koristiti u izrazima. Ima dva glavna oblika, koje ćemo pogledati u ovom odjeljku.

CASE izjava s vrijednostima

Izjava CASE s vrijednostima ima sljedeću sintaksu:

CASE provjerena_vrijednost

WHEN vrijednost1 THEN rezultat1

WHEN vrijednost2 THEN rezultat2

. . .

WHEN vrijednost N THEN rezultat N

INAČE rezultatX

U slučaju provjerena_vrijednost jednako vrijednost1 , naredba CASE vraća vrijednost rezultat1 , naveden nakon ključne riječi THEN. Inače se checked_value uspoređuje s vrijednost2 , a ako su jednaki, tada se vraća vrijednost result2. U suprotnom, vrijednost koja se testira uspoređuje se sa sljedećom vrijednošću navedenom nakon ključne riječi WHEN, itd. Ako tested_value nije jednak nijednoj od ovih vrijednosti, vrijednost se vraća rezultat X , navedeno nakon ključne riječi ELSE (else).

Ključna riječ ELSE nije obavezna. Ako nedostaje i nijedna od vrijednosti koje se uspoređuju nije jednaka vrijednosti koja se testira, tada izjava CASE vraća NULL.

Recimo, na temelju tablice Klijenti (vidi sl. 1) želite dobiti tablicu u kojoj su nazivi regija zamijenjeni njihovim šiframa. Ako u izvornoj tablici nema previše različitih regija, tada je za rješavanje ovog problema prikladno koristiti upit s operatorom CASE:

ODABERITE ime, adresu,

CASE regija

KAD "Moskva" ONDA "77"

KADA "Tver regija" ONDA "69"

. . .

DRUGO Regija

AS kod regije

OD klijenata;

CASE iskaz s uvjetima pretraživanja

Drugi oblik CASE operatora uključuje njegovu upotrebu kada se u tablici traže oni zapisi koji zadovoljavaju određeni uvjet:

SLUČAJ

WHEN uvjet1 THEN rezultat1

WHEN catch2 THEN result2

. . .

WHEN uvjet N THEN rezultat N

INAČE rezultatX

Izjava CASE testira je li uvjet1 istinit za prvi zapis u skupu definiranom klauzulom WHERE ili cijelu tablicu ako WHERE nije prisutan. Ako da, tada CASE vraća rezultat1. U suprotnom, uvjet2 se provjerava za ovaj zapis. Ako je istina, tada se vraća vrijednost rezultat2, itd. Ako nijedan od uvjeta nije istinit, vraća se vrijednost rezultat x , naveden nakon ključne riječi ELSE.

Ključna riječ ELSE nije obavezna. Ako nedostaje i niti jedan od uvjeta nije istinit, naredba CASE rotira NULL. Nakon što se naredba koja sadrži CASE izvrši za prvi zapis, prelazi se na sljedeći zapis. To se nastavlja dok se ne obradi cijeli skup zapisa.

Pretpostavimo da je u tablici knjiga (naslov, cijena) stupac NULL ako odgovarajuće knjige nema na zalihama. Sljedeći upit vraća tablicu koja prikazuje "Nema na zalihama" umjesto NULL:

ODABERI naslov,

SLUČAJ

KADA JE cijena NULL, ONDA "Nema na skladištu"

ELSE CAST(Cijena KAO CHAR(8))

AS Cijena

IZ knjiga;

Sve vrijednosti u istom stupcu moraju biti iste vrste. Stoga u ovaj zahtjev Funkcija pretvorbe tipa CAST koristi se za pretvaranje numeričkih vrijednosti stupca Cijena u vrstu znakova.

Imajte na umu da uvijek možete koristiti drugi oblik naredbe CASE umjesto prvog:

SLUČAJ

WHEN testirana_vrijednost = vrijednost1 THEN rezultat1

WHEN testirana_vrijednost = vrijednost2 ONDA rezultat2

. . .

KADA je provjereno_vrijednost = vrijednost N ONDA rezultatN

ILSE rezultat

Funkcije NULLIF i COALESCE

U nekim slučajevima, posebno u zahtjevima za ažuriranje podataka (operator UPDATE), prikladno je koristiti kompaktnije funkcije NULLIF() (NULL if) i COALESCE() (kombinirati) umjesto glomaznog CASE operatora.

NULLIF funkcija ( vrijednost1, vrijednost2) vraća NULL ako vrijednost prvog parametra odgovara vrijednosti drugog parametra; u slučaju nepodudaranja, vrijednost prvog parametra vraća se nepromijenjena. To jest, ako je jednakost vrijednost1 = vrijednost2 istinita, tada funkcija vraća NULL, inače vrijednost vrijednost1.

Ova funkcija je ekvivalentan naredbi CASE u sljedeća dva oblika:

• CASE vrijednost1

WHEN vrijednost2 THEN NULL

ELSE vrijednost1

• SLUČAJ

WHEN vrijednost1 = vrijednost2 THEN NULL

ELSE vrijednost1

Funkcija COALESCE( vrijednost1, vrijednost2, ... , N vrijednost) prihvaća popis vrijednosti, koje mogu biti NULL ili NULL. Funkcija vraća određenu vrijednost s popisa ili NULL ako su sve vrijednosti nedefinirane.

Ova funkcija je ekvivalentna sljedećoj CASE izjavi:

SLUČAJ

KADA vrijednost 1 NIJE NULL, ONDA vrijednost 1

KADA vrijednost 2 NIJE NULL, ONDA vrijednost 2

. . .

KADA vrijednost N NIJE NULL, ONDA vrijednost N

INAČE NULL

Pretpostavimo da je u tablici Knjige (naslov, cijena) stupac Cijena NULL ako odgovarajuće knjige nema na zalihama. Sljedeći upit vraća tablicu gdje umjesto NULL Prikazuje se tekst "Nema na skladištu":

ODABERI ime, SPOJ (CAST(cijena AS CHAR(8)),

"Nema na skladištu") AS Cijena

IZ knjiga;

Naučimo rezimirati. Ne, ovo nisu rezultati proučavanja SQL-a, već rezultati vrijednosti stupaca tablica baze podataka. Agregat SQL funkcije djelovati na vrijednosti stupca da proizvede jednu rezultirajuću vrijednost. 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 koriste se same i vraćaju jednu rezultirajuću vrijednost. Drugo, agregatne funkcije se koriste s klauzulom SQL GROUP BY, odnosno grupiranjem po poljima (stupcima) kako bi se dobile rezultirajuće vrijednosti u svakoj grupi. Razmotrimo prvo slučajeve korištenja agregatnih funkcija bez grupiranja.

SQL funkcija SUM

SQL funkcija SUM vraća zbroj vrijednosti u stupcu tablice baze podataka. Može se primijeniti samo na stupce čije su vrijednosti brojevi. SQL upiti da biste dobili rezultirajući zbroj počnite ovako:

SELECT SUM (COLUMN_NAME) ...

Nakon ovog izraza slijedi FROM (TABLE_NAME), a zatim se uvjet može navesti korištenjem WHERE klauzule. Osim toga, nazivu stupca može prethoditi DISTINCT, što znači da će se brojati samo jedinstvene vrijednosti. Prema zadanim postavkama, sve vrijednosti se uzimaju u obzir (za ovo možete posebno navesti ne DISTINCT, već ALL, ali riječ ALL nije potrebna).

Ako želite pokrenuti upite baze podataka iz ovog vodiča u MS-u SQL poslužitelj, ali ovaj DBMS nije instaliran na vašem računalu, tada ga možete instalirati koristeći upute na ovoj poveznici .

Prvo ćemo raditi s bazom podataka poduzeća - Company1. Skripta za izradu ove baze podataka, njezinih tablica i popunjavanje tablica podacima nalazi se u datoteci na ovoj poveznici .

Primjer 1. Postoji baza podataka poduzeća s podacima o njegovim odjelima i zaposlenicima. Tablica osoblja također ima stupac s podacima o plaćama zaposlenika. Odabir iz tablice izgleda ovako (za povećanje slike kliknite na nju lijevom tipkom miša):

Za dobivanje zbroja svih plaća koristimo sljedeći upit (na MS SQL Serveru - s prethodnom konstrukcijom USE company1;):

SELECT SUM (Plaća) FROM osoblja

Ovaj će upit vratiti vrijednost 287664,63.

A sada . Na vježbama već počinjemo komplicirati zadatke, približavajući ih onima koji se susreću u praksi.

SQL MIN funkcija

Funkcija SQL MIN također radi na stupcima čije su vrijednosti brojevi i vraća najmanju vrijednost od svih vrijednosti u stupcu. Ova funkcija ima sintaksu sličnu onoj funkcije SUM.

Primjer 3. Baza podataka i tablica su iste kao u primjeru 1.

Moramo saznati minimalnu plaću za zaposlenike odjela broj 42. Da biste to učinili, napišite sljedeći upit (na MS SQL Serveru - s prefiksom USE company1;):

Upit će vratiti vrijednost 10505,90.

I opet vježba za samorješenje. U ovoj i nekim drugim vježbama bit će vam potrebna ne samo tablica Staff, već i tablica Org koja sadrži podatke o odjelima tvrtke:

Primjer 4. Tablica Org dodaje se tablici Staff koja sadrži podatke o odjelima tvrtke. Ispišite minimalni broj godina rada jednog zaposlenika u odjelu koji se nalazi u Bostonu.

SQL MAX funkcija

Funkcija SQL MAX radi slično i ima sličnu sintaksu, koja se koristi kada trebate odrediti najveću vrijednost među svim vrijednostima u stupcu.

Primjer 5.

Moramo saznati maksimalnu plaću zaposlenika u odjelu broj 42. Da biste to učinili, napišite sljedeći upit (na MS SQL Serveru - s prefiksom USE company1;):

Upit će vratiti vrijednost 18352,80

Vrijeme je vježbe za samostalno rješavanje.

Primjer 6. Opet radimo s dvije tablice - Staff i Org. Prikažite naziv odjela i najveću vrijednost provizije koju prima jedan zaposlenik u odjelu koji pripada skupini odjela (Division) Istočni. Koristiti JOIN (spajanje tablica) .

SQL AVG funkcija

Ono što je navedeno u vezi sa sintaksom za prethodno opisane funkcije vrijedi i za funkciju SQL AVG. Ova funkcija vraća prosjek svih vrijednosti u stupcu.

Primjer 7. Baza podataka i tablica su iste kao u prethodnim primjerima.

Recimo da želite saznati prosječni radni staž zaposlenika u odjelu broj 42. Da biste to učinili, napišite sljedeći upit (na MS SQL Serveru - s prethodnom konstrukcijom USE company1;):

Rezultat će biti 6,33

Primjer 8. Radimo za jednim stolom - Staff. Prikaz prosječne plaće zaposlenika s 4 do 6 godina iskustva.

SQL COUNT funkcija

Funkcija SQL COUNT vraća broj zapisa u tablici baze podataka. Ako u upitu navedete SELECT COUNT(COLUMN_NAME) ..., rezultat će biti broj zapisa bez uzimanja u obzir onih zapisa u kojima je vrijednost stupca NULL (nedefinirano). Ako koristite zvjezdicu kao argument i pokrenete SELECT COUNT(*) ... upit, rezultat će biti broj svih zapisa (redaka) tablice.

Primjer 9. Baza podataka i tablica su iste kao u prethodnim primjerima.

Želite znati broj svih zaposlenika koji primaju provizije. Broj zaposlenika čije vrijednosti stupca Comm nisu NULL vratit će sljedeći upit (na MS SQL Serveru - s prefiksom USE company1;):

SELECT COUNT (Comm) FROM Staff

Rezultat će biti 11.

Primjer 10. Baza podataka i tablica su iste kao u prethodnim primjerima.

Ako želite saznati ukupan broj zapisa u tablici, tada upotrijebite upit sa zvjezdicom kao argument funkcije COUNT (na MS SQL Serveru - s prethodnom konstrukcijom USE company1;):

SELECT COUNT (*) FROM Staff

Rezultat će biti 17.

U sljedećem vježba za samostalno rješavanje morat ćete koristiti podupit.

Primjer 11. Radimo za jednim stolom - Staff. Prikaz broja zaposlenih u odjelu planiranja (Plains).

Skupne funkcije sa SQL GROUP BY

Pogledajmo sada korištenje agregatnih funkcija zajedno s SQL naredbom GROUP BY. Naredba SQL GROUP BY koristi se za grupiranje vrijednosti rezultata po stupcima u tablici baze podataka. Web stranica ima lekcija posvećena posebno ovom operatoru .

Radit ćemo s bazom podataka "Ads Portal 1". Skripta za izradu ove baze podataka, njezine tablice i popunjavanje tablice podataka nalazi se u datoteci na ovoj poveznici .

Primjer 12. Dakle, postoji baza podataka portala za oglase. Ima tablicu oglasa koja sadrži podatke o oglasima poslanim za tjedan. Stupac Kategorija sadrži podatke o velikim kategorijama oglasa (npr. Nekretnine), a stupac Dijelovi sadrži podatke o manjim dijelovima koji su uključeni u kategorije (npr. dijelovi Apartmani i Ljetnikovi dijelovi su kategorije Nekretnine). U stupcu Jedinice nalaze se podaci o broju zaprimljenih oglasa, a u stupcu Novac podaci o novčanom iznosu primljenom za podnošenje oglasa.

Kategorija	Dio	Jedinice	Novac
Prijevoz	Automobili	110	17600
Nekretnina	Apartmani	89	18690
Nekretnina	Dače	57	11970
Prijevoz	Motocikli	131	20960
Građevinski materijali	Daske	68	7140
Elektrotehnika	televizori	127	8255
Elektrotehnika	Hladnjaci	137	8905
Građevinski materijali	Regips	112	11760
Slobodno vrijeme	knjige	96	6240
Nekretnina	Kod kuće	47	9870
Slobodno vrijeme	glazba, muzika	117	7605
Slobodno vrijeme	Igre	41	2665

Pomoću SQL naredbe GROUP BY pronađite iznos novca zarađen objavljivanjem oglasa u svakoj kategoriji. Pišemo sljedeći upit (na MS SQL Serveru - s prethodnom konstrukcijom USE adportal1;):

ODABERITE kategoriju, SUM (novac) KAO novac IZ GRUPE OGLASA PO kategoriji

Primjer 13. Baza podataka i tablica su iste kao u prethodnom primjeru.

Pomoću SQL naredbe GROUP BY saznajte u kojem je dijelu svake kategorije podnesak napravljen najveći broj reklame Pišemo sljedeći upit (na MS SQL Serveru - s prethodnom konstrukcijom USE adportal1;):

ODABERITE kategoriju, dio, MAX (jedinice) KAO maksimum IZ GRUPE OGLASA PO kategoriji

Rezultat će biti sljedeća tablica:

Ukupne i pojedinačne vrijednosti mogu se dobiti u jednoj tablici kombiniranje rezultata upita korištenjem operatora UNION .

Relacijske baze podataka i SQL jezik