Õpime kokkuvõtet tegema. Ei, need ei ole SQL-i uurimise tulemused, vaid andmebaasi tabelite veergude väärtuste tulemused. SQL-i koondfunktsioonid töötavad veeru väärtustel, et saada üks tulemus. Kõige sagedamini kasutatavad SQL-i koondfunktsioonid on SUM, MIN, MAX, AVG ja COUNT. Tuleb eristada kahte koondfunktsioonide kasutamise juhtumit. Esiteks kasutatakse koondfunktsioone eraldi ja need tagastavad ühe saadud väärtuse. Teiseks kasutatakse koondfunktsioone koos klausliga SQL GROUP BY, st rühmitatakse väljade (veergude) järgi, et saada igas rühmas saadud väärtused. Vaatleme esmalt koondfunktsioonide kasutamise juhtumeid ilma rühmitamiseta.
SQL SUM funktsioon
Funktsioon SQL SUM tagastab andmebaasi tabeli veerus olevate väärtuste summa. Seda saab rakendada ainult veergudele, mille väärtused on numbrid. SQL-päringud saadud summa saamiseks algavad järgmiselt:
VALI SUMMA(VEERG_NAME) ...
Sellele avaldisele järgneb FROM (TABLE_NAME) ja seejärel saab tingimuse määrata WHERE-klausli abil. Lisaks võib veeru nimele eelneda DISTINCT, mis tähendab, et arvesse lähevad ainult kordumatud väärtused. Vaikimisi võetakse arvesse kõiki väärtusi (selleks saate konkreetselt määrata mitte DISTINCT, vaid KÕIK, kuid sõna KÕIK pole nõutav).
Näide 1. Seal on ettevõtte andmebaas, mis sisaldab andmeid selle allüksuste ja töötajate kohta. Tabelis Personal on ka veerg töötajate palkade andmetega. Valik tabelist näeb välja selline (pildi suurendamiseks klõpsa sellel hiire vasaku nupuga):
Kõigi palkade summa saamiseks kasutage järgmist päringut:
VALI SUMMA(PALK) Töötajatelt
See päring tagastab väärtuse 287664.63.
Ja nüüd . Harjutustes hakkame juba ülesandeid keerulisemaks muutma, lähendades neid praktikas ettetulevatele.
SQL MIN funktsioon
Funktsioon SQL MIN töötab ka veergudel, mille väärtused on numbrid, ja tagastab veeru kõigi väärtuste miinimumi. Selle funktsiooni süntaks on sarnane funktsiooni SUM omaga.
Näide 3. Andmebaas ja tabel on samad, mis näites 1.
Peame välja selgitama osakonna nr 42 töötajate töötasu alammäära. Selleks kirjutage järgmine päring:
Päring tagastab väärtuse 10505.90.
Ja jälle harjutus eneselahenduseks. Selles ja mõnes teises harjutuses pole vaja mitte ainult personali tabelit, vaid ka Org tabelit, mis sisaldab andmeid ettevõtte divisjonide kohta:
Näide 4. Tabel Org lisatakse tabelisse Personal, mis sisaldab andmeid ettevõtte osakondade kohta. Printige Bostonis asuvas osakonnas ühe töötaja minimaalne töötatud aastate arv.
SQL MAX funktsioon
Funktsioon SQL MAX töötab sarnaselt ja sellel on sarnane süntaks, mida kasutatakse siis, kui peate määrama veeru kõigi väärtuste maksimaalse väärtuse.
Näide 5.
Peate välja selgitama osakonna number 42 töötajate maksimaalse palga. Selleks kirjutage järgmine päring:
Päring tagastab väärtuse 18352.80
On aeg harjutused iseseisvaks lahenduseks.
Näide 6. Töötame taas kahe lauaga – Staff ja Org. Kuvatakse osakonna nimi ja osakonna gruppi (Division) Ida kuuluvas osakonnas ühe töötaja poolt saadud vahendustasu maksimaalne väärtus. Kasuta LIITUMINE (laudade ühendamine) .
SQL AVG funktsioon
Eelnevate funktsioonide süntaksi kohta öeldu kehtib ka SQL AVG funktsiooni kohta. See funktsioon tagastab veeru kõigi väärtuste keskmise.
Näide 7. Andmebaas ja tabel on samad, mis eelmistes näidetes.
Oletame, et soovite teada saada osakonna number 42 töötajate keskmist tööstaaži. Selleks kirjutage järgmine päring:
Tulemuseks 6.33
Näide 8. Töötame ühe lauaga – Staff. Kuva 4–6-aastase töökogemusega töötajate keskmine palk.
SQL COUNT funktsioon
Funktsioon SQL COUNT tagastab andmebaasi tabelis olevate kirjete arvu. Kui määrate päringus SELECT COUNT(COLUMN_NAME) ..., on tulemuseks kirjete arv, võtmata arvesse neid kirjeid, mille veeru väärtus on NULL (määratlemata). Kui kasutate argumendina tärni ja käivitate päringu SELECT COUNT(*) ..., on tulemuseks tabeli kõigi kirjete (ridade) arv.
Näide 9. Andmebaas ja tabel on samad, mis eelmistes näidetes.
Soovite teada kõigi vahendustasu saavate töötajate arvu. Töötajate arv, kelle Comm veeru väärtused ei ole NULL, tagastatakse järgmise päringuga:
VALI COUNT(Comm) FROM Personal
Tulemuseks on 11.
Näide 10. Andmebaas ja tabel on samad, mis eelmistes näidetes.
Kui soovite teada saada tabelis olevate kirjete koguarvu, kasutage funktsiooni COUNT argumendina tärniga päringut:
VALI COUNT(*) Töötajatelt
Tulemuseks on 17.
Järgmises iseseisva lahenduse harjutus peate kasutama alampäringut.
Näide 11. Töötame ühe lauaga – Staff. Kuvage planeerimisosakonna töötajate arv (Plains).
Koondfunktsioonid SQL GROUP BY abil
Vaatame nüüd koondfunktsioonide kasutamist koos lausega SQL GROUP BY. SQL GROUP BY lauset kasutatakse tulemuste väärtuste rühmitamiseks andmebaasi tabelis veergude kaupa.
Näide 12. Olemas on kuulutusteportaali andmebaas. Sellel on reklaamide tabel, mis sisaldab andmeid nädala kohta esitatud reklaamide kohta. Veerg Kategooria sisaldab andmeid suurte kuulutuste kategooriate kohta (näiteks Kinnisvara) ja veerg Osad sisaldab andmeid kategooriatesse kuuluvate väiksemate osade kohta (näiteks korterite ja suvemajade osad kuuluvad kategooriasse Kinnisvara). Veerus Ühikud on andmed esitatud kuulutuste arvu kohta ja veerus Raha on andmed kuulutuste esitamise eest saadud rahasumma kohta.
| Kategooria | osa | Ühikud | Raha |
| Transport | Autod | 110 | 17600 |
| Kinnisvara | Korterid | 89 | 18690 |
| Kinnisvara | Dachas | 57 | 11970 |
| Transport | Mootorrattad | 131 | 20960 |
| Ehitusmaterjalid | Lauad | 68 | 7140 |
| Elektrotehnika | telerid | 127 | 8255 |
| Elektrotehnika | Külmikud | 137 | 8905 |
| Ehitusmaterjalid | Regips | 112 | 11760 |
| Vaba aeg | Raamatud | 96 | 6240 |
| Kinnisvara | Kodus | 47 | 9870 |
| Vaba aeg | Muusika | 117 | 7605 |
| Vaba aeg | Mängud | 41 | 2665 |
SQL GROUP BY lause abil leidke igas kategoorias reklaamide postitamisega teenitud rahasumma. Kirjutame järgmise palve.
- Koondfunktsioonid kasutatakse sarnaselt väljanimedega SELECT-lauses, ühe erandiga: nad võtavad välja nime argumendina. Funktsioonidega SUMMA Ja AVG Kasutada saab ainult numbrivälju. Funktsioonidega COUNT, MAX ja MIN Kasutada saab nii numbri- kui ka märgivälju. Kui kasutatakse koos märgiväljadega MAX Ja MIN tõlgib need ASCII-koodi ekvivalendiks ja töötleb neid tähestikulises järjekorras. Mõned DBMS-id lubavad kasutada pesastatud agregaate, kuid see on kõrvalekalle ANSI standardist koos kõigi sellest tulenevate tagajärgedega.
Näiteks saate arvutada õpilaste arvu, kes sooritasid eksamid igal erialal. Selleks peate käivitama päringu, mis on rühmitatud väljaga "Displiin" ja kuvama tulemuseks selle distsipliini nimetuse ja ridade arvu rühmas. Märgi * kasutamine funktsiooni COUNT argumendina tähendab kõigi rühma ridade loendamist.
VALI R1. Distsipliin, COUNT(*)
GROUP BY R1.Distsipliin;
Tulemus:
SELECT R1. Distsipliin, COUNT (*)
KUS R1. Hindamine EI OLE NULL
GROUP BY R1.Distsipliin;
Tulemus:
ei kaasata enne rühmitamist korduste komplekti, seega on "Infoteooria" distsipliini jaoks grupis olevate korterite arv 1 võrra väiksem.
Sarnase tulemuse saate, kui kirjutate päringu järgmiselt:
VALI R1. Distsipliin, COUNT (R1. Reiting)
RÜHMITEE R1. Distsipliin;
Funktsioon COUNT (ATTRIBUUDI NIMI) loendab konkreetsete väärtuste arvu rühmas, mitte funktsiooni COUNT(*), mis loeb ridade arvu rühmas. Tõepoolest, distsipliiniga "Infoteooria" rühmas on 4 rida, kuid atribuudi "Hindamine" jaoks on ainult 3 konkreetset väärtust.
Reeglid NULL-väärtuste käsitlemiseks koondfunktsioonides
Kui mis tahes väärtused veerus on võrdsed NULL Funktsiooni tulemuse arvutamisel jäetakse need välja.
Kui kõik veerus olevad väärtused on võrdsed NULL, See Maksimaalne minimaalne summa keskmine = NULL arv = 0 (null).
Kui laud on tühi, arv (*) = 0 .
Koondfunktsioone saate kasutada ka ilma eelrühmitamiseta, sel juhul käsitletakse kogu seost ühe rühmana ja selle rühma jaoks saate arvutada ühe väärtuse rühma kohta.
Koondfunktsioonide tõlgendamise reeglid
Koondfunktsioonid saab lisada väljundnimekirja ja seejärel rakendatakse neid kogu tabelile.
SELECT MAX (hinnang) alates R1 annab seansi jooksul maksimaalse hinnangu;
VALI SUMMA (Skoor) R1-st annab sessiooni kõigi hinnete summa;
VALI AVG (hinnang) R1-st annab kogu seansi keskmise hinde.
2; Tulemus: " width="640" Pöördudes uuesti andmebaasi "Session" (tabel R1), leiame edukalt sooritatud eksamite arvu:
SELECT COUNT(*) Nagu Kohale toimetatud _ eksamid
KUS skoor 2;
Tulemus:
Funktsioonide koondamise argumendid võivad olla üksikud tabeli veerud. Näiteks rühma teatud veeru erinevate väärtuste arvu arvutamiseks peate koos veeru nimega kasutama märksõna DISTINCT. Arvutame igas distsipliinis saadud erinevate hinnete arvu:
SELECT R1. Distsipliin, COUNT (DISTINCT R1.Evaluation)
KUS R1. Hindamine EI OLE NULL
GROUP BY R1.Distsipliin;
Tulemus:
Sama tulemuse saame, kui välistame osa WHERE selgesõnalise tingimuse, sel juhul näeb päring välja järgmine:
VALI R1. Distsipliin, COUNT (DISTINCT R1. Hindamine)
RÜHMITEE R1. Distsipliin;
Funktsioon COUNT (DISTINCT R1.Evaluation) loeb ainult teatud mitmesugused tähendusi.
Soovitud tulemuse saamiseks on vaja veeru „Reiting” andmetüübi eelteisendus teha, viies see reaalsesse tüüpi, siis ei ole keskmise arvutamise tulemus täisarv. Sel juhul näeb taotlus välja järgmine:
2 Rühmitage R2 järgi. Rühm, R1. Distsipliin; Siin teisendab funktsioon CAST() veeru Score kehtivaks andmetüübiks. "laius = 640" Valige R2.Group, R1.Discipline,Count(*) väärtuseks Total, AVG(cast(Score as decimal(3,1))) kui Average_score
Alates R1, R2
kus R1. Täisnimi = R2. Täisnimi ja R1. skoor ei ole null
ja R1. 2. skoor
R2 rühma järgi. Rühm, R1. Distsipliin;
Siin on funktsioon CAST() Teisendab veeru Hinnang kehtivaks andmetüübiks.
Te ei saa WHERE-klauslis kasutada koondfunktsioone, kuna selle jaotise tingimusi hinnatakse ühe rea alusel ja koondfunktsioone ridade rühmadena.
Klausel GROUP BY võimaldab teil määratleda konkreetse välja väärtuste alamhulga teise välja osas ja rakendada alamhulgale koondfunktsiooni. See võimaldab kombineerida välju ja koondfunktsioone ühes SELECT-klauslis. Koondfunktsioone saab kasutada nii SELECT-rea tulemuste väljastamise avaldises kui ka moodustatud HAVING-rühmade töötlemise tingimuse avaldises. Sel juhul arvutatakse iga koondfunktsioon iga valitud rühma jaoks. Koondfunktsioonide arvutamisel saadud väärtusi saab kasutada vastavate tulemuste kuvamiseks või rühmade valiku tingimiseks.
Koostame päringu, mis kuvab rühmad, kus eksamitel saadi ühes distsipliinis rohkem kui üks halb hind:
1; Tulemus: " width="640" VALI R2. Grupp
ALATES R1, R2
KUS R1. Täisnimi = R2. Täisnimi JA
R1.Skoor = 2
RÜHM R2.Rühm, R1.Distsipliin
HAVING count (*) 1;
Tulemus:
Meil on andmebaas “Pank”, mis koosneb ühest tabelist F, mis salvestab seose F, mis sisaldab infot teatud panga filiaalides olevate kontode kohta:
Leidke filiaalide kontode kogusaldo. Saate teha igaühe jaoks eraldi päringu, valides iga haru tabelist SUM (Jälejäänud), kuid operatsioon GROUP BY võimaldab need kõik ühte käsku panna:
VALI Filiaal ,SUM( Ülejäänud )
GROUP BY haru;
GROUP BY rakendab koondfunktsioone sõltumatult igale rühmale, mis on tuvastatud välja haru väärtusega. Rühm koosneb ridadest, millel on sama haruvälja väärtus, ja funktsioonist SUMMA rakendatakse iga sellise grupi kohta eraldi, st kogu kontojääk arvutatakse iga filiaali kohta eraldi. Selle välja väärtus, millele see kehtib GROUP BY, on definitsiooni järgi ainult üks väärtus väljundrühma kohta, täpselt nagu koondfunktsiooni tulemus.
5000; HAVING-klauslis olevad argumendid järgivad samu reegleid, mis SELECT-klauslis, mis kasutab funktsiooni GROUP BY. Neil peab olema üks väärtus väljundrühma kohta. "laius = 640" Oletame, et valime ainult need filiaalid, mille kontode kogusaldo ületab 5000 dollarit, samuti valitud filiaalide kogusaldo. Tulemuste kuvamiseks filiaalide kohta, mille kogusaldo on üle 5000 $, peate kasutama klauslit HAVING. HAVING-klausel määrab kriteeriumid, mida kasutatakse teatud rühmade väljundist eemaldamiseks, täpselt nagu WHERE-klausel üksikute ridade puhul.
Õige käsk oleks järgmine:
VALI haru, SUM (jäänud)
GROUP BY Filiaal
HAVING SUM ( Ülejäänud ) 5 000;
Argumendid lauses OMAMINE järgima samu reegleid, mis lauses VALI kus kasutatakse GROUP BY. Neil peab olema üks väärtus väljundrühma kohta.
Järgmine käsk on keelatud:
VALI haru, SUM(jäänud)
RÜHM filiaalide kaupa
Avamiskuupäev = 27.12.2004 ;
Väli Avamise kuupäev ei saa lauses kasutada OMAMINE, kuna sellel võib väljundrühma kohta olla rohkem kui üks väärtus. Sellise olukorra vältimiseks on soovitus OMAMINE peaks viitama ainult valitud koondandmetele ja väljadele GROUP BY. Ülaltoodud päringu tegemiseks on õige viis:
VALI haru, SUM(jäänud)
WHEREOpenDate = '27/12/2004'
GROUP BY haru;
Selle päringu tähendus on järgmine: leidke saldode summa iga 27. detsembril 2004 avatud kontoharu kohta.
Nagu varem öeldud, saab HAVING võtta ainult argumente, millel on üks väärtus väljundrühma kohta. Praktikas on kõige levinumad viited koondfunktsioonidele, kuid kehtivad ka GROUP BY abil valitud väljad. Näiteks tahame näha Peterburi, Pihkva ja Urjupinski filiaalide kontode kogujääke:
VALI haru, SUM (jäänud)
F, Q
KUS F. haru = Q. haru
RÜHM filiaalide kaupa
HAVING Branch SISSE ('Peterburi', 'Pihkva', 'Urjupinsk');
100 000; Kui kogujääk on üle 100 000 $, siis näeme seda saadud seoses, vastasel juhul saame tühja seose. "laius = 640"
Seetõttu saab lause HAVING valikuklauslis sisalduvates predikaataritmeetilistes avaldistes otse kasutada ainult klauslis GROUP BY rühmitavate veergudena määratud veergude spetsifikatsioone. Ülejäänud veerge saab määrata ainult koondfunktsioonide COUNT, SUM, AVG, MIN ja MAX spetsifikatsioonides, mis sel juhul arvutavad kogu ridade rühma jaoks mingi koondväärtuse. Sektsiooni HAVING täitmise tulemuseks on rühmitatud tabel, mis sisaldab ainult neid ridade rühmi, mille jaoks jaotises HAVING valikutingimuse arvutamise tulemus on TÕENE. Täpsemalt, kui HAVING-klausel esineb päringus, mis ei sisalda GROUP BY-d, on selle täitmise tulemuseks kas tühi tabel või tabeliavaldise eelmiste osade täitmise tulemus, mida käsitletakse ühe rühmana. ilma veergude rühmitamiseta. Vaatame näidet. Oletame, et tahame kuvada kõigi filiaalide saldod, kuid ainult siis, kui see on suurem kui 100 000 dollarit. Sel juhul ei sisalda meie päring rühmitamist, vaid sisaldab jaotist HAVING ja näeb välja järgmine:
SELECT SUM( Ülejäänud )
HAVING SUM( Ülejäänud ) 100 000;
Kui kogujääk on üle 100 000 $, siis näeme seda saadud seoses, vastasel juhul saame tühja seose.
veeru Distsipliin väärtuse järgi. Saame 4 rühma, mille jaoks saame arvutada mõned grupiväärtused, näiteks grupis olevate korteežide arv, veeru Score maksimaalne või minimaalne väärtus. | Funktsioon | Tulemus |
|---|---|
| COUNT | Päringuga valitud ridade või mittetühjade väljade väärtuste arv |
| SUMMA | Selle välja kõigi valitud väärtuste summa |
| AVG | Kõigi selle välja jaoks valitud väärtuste aritmeetiline keskmine |
| MIN | Selle välja jaoks valitud väärtustest väikseim |
| MAX | Selle välja jaoks valitud väärtustest suurim |
| R1 | |||
|---|---|---|---|
| Täisnimi | Distsipliin | Hinne | |
| 1. rühm | Petrov F.I. | Andmebaas | 5 |
| Sidorov K. A. | Andmebaas | 4 | |
| Mironov A.V. | Andmebaas | 2 | |
| Stepanova K.E. | Andmebaas | 2 | |
| Krylova T.S. | Andmebaas | 5 | |
| Vladimirov V. A. | Andmebaas | 5 | |
| 2. rühm | Sidorov K. A. | Infoteooria | 4 |
| Stepanova K.E. | Infoteooria | 2 | |
| Krylova T.S. | Infoteooria | 5 | |
| Mironov A.V. | Infoteooria | Null | |
| 3. rühm | Trofimov P. A. | Võrgud ja telekommunikatsioon | 4 |
| Ivanova E. A. | Võrgud ja telekommunikatsioon | 5 | |
| Utkina N.V. | Võrgud ja telekommunikatsioon | 5 | |
| 4. rühm | Vladimirov V. A. | inglise keel | 4 |
| Trofimov P. A. | inglise keel | 5 | |
| Ivanova E. A. | inglise keel | 3 | |
| Petrov F.I. | inglise keel | 5 | |
Koondfunktsioonid kasutatakse sarnaselt väljanimedega SELECT-lauses, kuid ühe erandiga: nad võtavad välja nime argumendina. Funktsioonidega SUM ja AVG saab kasutada ainult numbrivälju. Funktsioonidega COUNT, MAX ja MIN saab kasutada nii numbri- kui ka märgivälju. Märgiväljadega kasutamisel tõlgivad MAX ja MIN need samaväärseks ASCII-koodiks ja töötlevad neid tähestikulises järjekorras. Mõned DBMS-id lubavad kasutada pesastatud agregaate, kuid see on kõrvalekalle ANSI standardist koos kõigi sellest tulenevate tagajärgedega.
Näiteks saate arvutada õpilaste arvu, kes sooritasid eksamid igal erialal. Selleks peate käivitama päringu, mis on rühmitatud välja "Displiin" järgi ja tulemuseks kuvama eriala nimetuse ja selle distsipliini rühma ridade arvu. Märgi * kasutamine funktsiooni COUNT argumendina tähendab kõigi rühma ridade loendamist.
SELECT R1. Distsipliin, COUNT(*) FROM R1 GROUP BY BY BY. Distsipliin
Tulemus:
Kui tahame arvestada mis tahes erialal eksami sooritanud inimeste arvu, peame enne rühmitamist esialgsest suhtarvust välja jätma ebakindlad väärtused. Sel juhul näeb taotlus välja järgmine:
Saame tulemuse:
Sel juhul joon õpilasega
| Mironov A.V. | Infoteooria | Null |
|---|
ei kuulu enne rühmitamist korteežide hulka, seega tuleb distsiplineerida grupis olevate korteežide arvu " Infoteooria" on 1 vähem.
Võib kasutada koondfunktsioonid ka ilma eelgrupeerimisoperatsioonita, mille puhul loetakse kogu seos üheks rühmaks ja selle rühma jaoks saab arvutada ühe väärtuse rühma kohta.
Pöördudes uuesti andmebaasi "Session" (tabelid R1, R2, R3), leiame edukalt sooritatud eksamite arvu:
See erineb loomulikult välja valimisest, kuna alati tagastatakse üks väärtus, olenemata sellest, mitu rida tabelis on. Argument koondfunktsioonid võivad olla eraldi tabeli veerud. Kuid selleks, et arvutada näiteks rühma teatud veeru erinevate väärtuste arvu, peate koos veeru nimega kasutama märksõna DISTINCT. Arvutame igas distsipliinis saadud erinevate hinnete arvu:
Tulemus:
Tulemus võib sisaldada rühmitamisvälja väärtust ja mitut koondfunktsioonid ja rühmitamise tingimustes saate kasutada mitut välja. Sel juhul moodustatakse rühmad kindlaksmääratud rühmitusväljade komplekti alusel. Koondfunktsiooni toiminguid saab rakendada mitme lähtetabeli ühendamiseks. Näiteks esitame küsimuse: määrake iga rühma ja iga eriala kohta eksami edukalt sooritanud õpilaste arv ja distsipliini keskmine punktisumma.
Tulemus:
Me ei saa kasutada koondfunktsioonid WHERE-lauses, kuna predikaate hinnatakse ühe rea järgi ja koondfunktsioonid- ridade rühmade osas.
Klausel GROUP BY võimaldab teil määratleda konkreetse välja väärtuste alamhulga teise välja osas ja rakendada alamhulgale koondfunktsiooni. See võimaldab kombineerida põlde ja koondfunktsioonidühes SELECT-klauslis. Koondfunktsioonid saab kasutada nii SELECT-rea tulemuste väljastamiseks kui ka genereeritud HAVING-rühmade töötlemistingimuste avaldises. Sel juhul arvutatakse iga koondfunktsioon iga valitud rühma jaoks. Arvutusest saadud väärtused koondfunktsioonid, saab kasutada vastavate tulemuste kuvamiseks või rühmade valiku tingimiseks.
Koostame päringu, mis kuvab rühmad, kus eksamitel saadi ühes distsipliinis rohkem kui üks halb hind:
Edaspidi töötame näitena mitte andmebaasiga "Session", vaid andmebaasiga "Pank", mis koosneb ühest tabelist F, mis salvestab seose F, mis sisaldab teavet teatud panga filiaalide kontode kohta:
F = (N, täisnimi, filiaal, avamiskuupäev, sulgemiskuupäev, saldo); Q = (filiaal, linn);
kuna selle põhjal on võimalik tööd selgemalt illustreerida koondfunktsioonide ja rühmitamisega.
Oletame näiteks, et tahame leida pangakontode kogusaldo. Kõigi nende kohta saate teha eraldi päringu, valides iga haru tabelist SUM(Balance). GROUP BY võimaldab teil aga need kõik ühte käsku panna:
SELECT haru, SUM(ülejäänud) FROM GROUP BY haru;
GROUP BY kehtib koondfunktsioonid sõltumatult iga rühma jaoks, mis on määratletud välja haru väärtuse abil. Rühm koosneb ridadest, millel on sama haruvälja väärtus, ja
ISO standard määratleb järgmised viis liitmisfunktsioonid:
COUNT– tagastab väärtuste arvu määratud veerus;
SUMMA– tagastab määratud veerus olevate väärtuste summa;
AVG– tagastab keskmise väärtuse määratud veerus;
MIN– tagastab määratud veerus minimaalse väärtuse;
MAX– tagastab määratud veerus maksimaalse väärtuse.
Kõik need funktsioonid töötavad väärtustel ühes tabeli veerus ja tagastavad ühe väärtuse. Funktsioonid COUNT, MIN ja MAX kehtivad nii numbriliste kui ka mittenumbriliste väljade puhul, samas kui funktsioone SUM ja AVG saab kasutada ainult numbriväljade jaoks. Välja arvatud COUNT(*), eemaldatakse mis tahes funktsiooni tulemuste hindamisel esmalt kõik nullväärtused ja seejärel rakendatakse vajalik toiming ainult veeru ülejäänud mittetühjadele väärtustele. Valik COUNT(*) on funktsiooni COUNT eriline kasutusjuht – selle eesmärk on loendada tabeli kõiki ridu, olenemata sellest, kas see sisaldab nullväärtusi, duplikaate või muid väärtusi. Kui soovite enne liitmisfunktsiooni kasutamist dubleerivad väärtused välistada, peate funktsiooni definitsioonis veeru nime ette eelnema märksõna DISTINCT. ISO standard lubab kasutada märksõna KÕIK, et näidata selgesõnaliselt, et duplikaatväärtuste välistamine ei ole vajalik, kuigi see märksõna on vaikimisi ette nähtud, kui muud määrajat pole määratud. Märksõnal DISTINCT pole funktsioonide MIN ja MAX jaoks tähendust. Kuid selle kasutamine võib mõjutada funktsioonide SUM ja AVG tulemusi, seega peaksite eelnevalt kaaluma, kas see peaks igal konkreetsel juhul olemas olema. Lisaks saab märksõna DISTINCT igas päringus määrata ainult üks kord.
Tuleb märkida, et liitmisfunktsioone saab kasutada ainult loendis SELECT ja HAVING-klauslis (vt jaotis 5.3.4). Kõigil muudel juhtudel on nende funktsioonide kasutamine vastuvõetamatu. Kui loendis SELECT on koondamisfunktsioon ja päringu kehas ei ole andmete rühmitamist võimaldavat klauslit GROUP BY, siis ei saa loendi SELECT ükski element sisaldada veergude viiteid, välja arvatud juhul, kui veergu kasutatakse koondamisfunktsiooni parameetrina. Näiteks järgmine päring on vale:
VALIpersonal Ei,COUNT (palk)
FROMPersonal;
Viga seisneb selles, et selles päringus pole konstruktsiooni GROUP BY ja loendi SELECT veergu staffNo pääsete juurde ilma liitmisfunktsiooni kasutamata.
Näide 13: funktsiooni COUNT(*) kasutamine.Tehke kindlaks, kui paljudel üüriobjektidel on üür üle 350 naela kuus,
VALI COUNT(*) AS-i arv
FROMPropertyForRent
KUSüür > 350;
Ainult nende üüripindade arvestamise piirang, mille üür on üle 350 naela kuus, rakendatakse WHERE-klausli abil. Kindlaksmääratud tingimusele vastavate üüripindade koguarvu saab määrata koondamisfunktsiooni COUNT abil. Päringu tulemused on toodud tabelis. 23.
Tabel 23
| loendama |
Näide 14. Funktsiooni COUNT(DISTINCT) kasutamine.Tehke kindlaks, mitut erinevat üüripinda vaatasid kliendid 2001. aasta mais.
SELECT COUNT(DISTINCTvaraNo) AS-i arv
FROMVaatamine
Jällegi, päringutulemuste piiramine ainult nende üüripindadega, mida kontrolliti mais 2001, saavutatakse WHERE-klausli abil. Määratud tingimusele vastavate uuritud objektide koguarvu saab määrata liitmisfunktsiooni COUNT abil. Kuna aga erinevad kliendid saavad sama objekti mitu korda vaadata, on vaja funktsiooni definitsioonis määrata märksõna DISTINCT – see võimaldab dubleerivad väärtused arvutusest välja jätta. Päringu tulemused on toodud tabelis. 24.
Tabel 24
Näide 16. Funktsioonide MIN, MAXnAVG kasutamine.Arvutage miinimum-, maksimum- ja keskmine palk.
VALI MIN(palk) AS min, MAX(palk) AS max, AVG(palk) AS keskm
FROMPersonal;
Selles näites peate töötlema teavet kogu ettevõtte personali kohta, seega ei pea te kasutama klauslit WHERE. Vajalikud väärtused saab arvutada MIN, MAX ja AVG funktsioonide abil, mis on rakendatud personali tabeli palga veerule. Päringu tulemused on toodud tabelis. 26.
Tabel 26.
Taotluse tulemus
| min | max | keskm |
| 9000.00 | 30000.00 | 17000.00 |
Tulemuste rühmitamine (GROUP BY konstruktsioon).Ülaltoodud kokkuvõtlike andmete näited on sarnased kokkuvõtlike ridadega, mida tavaliselt leidub aruannete lõpus. Selle tulemusena tihendatakse kõik üksikasjalikud aruandeandmed ühte kokkuvõtlikku rida. Väga sageli on aga vaja aruannetes genereerida vahesummasid. Selleks saab SELECT-lauses määrata klausli GROUP BY. Kutsutakse päring, mis sisaldab klauslit GROUP BY rühmitamistaotlus, kuna see rühmitab SELECT-toimingust saadud andmed ja loob seejärel iga üksiku rühma jaoks ühe kogurea. Kutsutakse välja veerud, mis on loetletud klauslis GROUP BY rühmitatud veerud. ISO standard nõuab, et SELECT ja GROUP BY klauslid oleksid omavahel tihedalt seotud. Kui kasutate SELECT-lauses klauslit GROUP BY, peab loendi SELECT igal loendielemendil olema ainus tähendus kogu rühma jaoks. Lisaks võib SELECT-klausel sisaldada ainult järgmist tüüpi elemente:
Veergude nimed;
liitmisfunktsioonid;
Konstandid;
Avaldised, mis sisaldavad ülaltoodud elementide kombinatsioone.
Kõik veergude nimed, mis ilmuvad loendis SELECT, peavad ilmuma ka klauslis GROUP BY, välja arvatud juhul, kui veeru nime kasutatakse ainult koondamisfunktsioonis. Vastupidine pole alati tõsi – klausel GROUP BY võib sisaldada veergude nimesid, mida loendis SELECT pole. Kui WHERE-klauslit kasutatakse koos klausliga GROUP BY, töödeldakse seda kõigepealt ja rühmitatakse ainult need read, mis vastavad otsingutingimusele. ISO standard täpsustab, et rühmitamisel käsitletakse kõiki puuduvaid väärtusi võrdsetena. Kui kaks tabelirida samas rühmitusverus sisaldavad NULL-väärtusi ja identseid väärtusi kõigis teistes mitte-null-rühma veergudes, paigutatakse need samasse rühma.
Näide 17: GROUP BY konstruktsiooni kasutamine.Määrake ettevõtte igas osakonnas töötavate töötajate arv ja nende kogupalk.
VALIharu nr, COUNT(personali nr) AS loendama, SUMMA(palk) AS summa
FROMPersonal
GROUP BYfiliaal nr
TELLIharu nr;
Elementide loendisse GROUP BY ei ole vaja personalinumbri ja palga veergude nimesid lisada, kuna need kuvatakse ainult koondamisfunktsioonidega loendis SELECT. Samal ajal ei ole SELECT-klausli loendi veerg branchNo seotud ühegi liitmisfunktsiooniga ja seetõttu tuleb see määrata klauslis GROUP BY. Päringu tulemused on toodud tabelis. 27.
Tabel 27
Taotluse tulemus
| filiaal nr | Count | Summa |
| B003 | 54000.00 | |
| B005 | 39000.00 | |
| B007 | 9000.00 |
Põhimõtteliselt tehakse selle päringu töötlemisel järgmised toimingud:
1. Personali tabeli read jaotatakse rühmadesse vastavalt ettevõtte osakonna numbrite veerus olevatele väärtustele. Igas rühmas on andmed ühe ettevõtte osakonna kogu personali kohta. Meie näites luuakse kolm rühma, nagu on näidatud joonisel fig. 1.
2. Iga rühma jaoks arvutatakse ridade koguarv, mis on võrdne osakonna töötajate arvuga, samuti palga veerus olevate väärtuste summa, mis on kõigi osakonna töötajate palkade summa. osakond, mis meid huvitab. Seejärel luuakse kogu lähteridade rühma jaoks üks kokkuvõtterida.
3. Saadud tabeli read järjestatakse veerus branchNo määratud haru numbri kasvavas järjekorras.
| filiaal nr | personal nr | Palk |
| В00З | SG37 | 12000.00 |
| В00З | SG14 | 18000.00 |
| В00З | SG5 | 24000.00 |
| B005 | SL21 | 30000.00 |
| B005 | SL41 | 9000.00 |
| B007 | SA9 | 9000.00 |
| COUNT(personali nr) | SUMMA(palk) |
| 54000.00 | |
| 39000.00 | |
| 9000.00 |
Riis. 1. Päringu täitmisel loodud kolm kirjete rühma
SQL-standard võimaldab pesastatud päringuid paigutada SELECT-loendisse. Seega võib ülaltoodud päringut esitada ka järgmiselt:
VALIharu nr, (VALI COUNT(personali nr)AS loendama
FROMPersonal s
KUSs.branchNo = b.branchNo),
(SELECT SUM(palk) AS summa
FROMPersonal s
KUSs.branchNo = b.branchNo)
FROMFiliaal b
TELLIharu nr;
See päringu versioon loob aga kaks koondfunktsiooni tulemust iga tabelis Branch kirjeldatud ettevõtte filiaali kohta, mistõttu on mõnel juhul võimalik näha nullväärtusi sisaldavaid ridu.
Rühmitamise teostamise piirangud (HAVING konstruktsioon). Klausel HAVING on mõeldud kasutamiseks koos klausliga GROUP BY, et seada piiranguid, mis on määratud nende valimiseks. rühmad, mis paigutatakse saadud päringutabelisse. Kuigi klauslitel HAVING ja WHERE on sarnane süntaks, on nende eesmärgid erinevad. WHERE-klauslit kasutatakse üksikute ridade valimiseks, et täita päringutabel, ja HAVING-klauslit kasutatakse rühmad, paigutatakse saadud päringutabelisse. ISO standard nõuab, et HAVING-klauslis kasutatavad veerunimed peavad olema GROUP BY elementide loendis või neid tuleks kasutada liitmisfunktsioonides. Praktikas sisaldavad HAVING-klausli otsinguterminid alati vähemalt ühte liitmisfunktsiooni; vastasel juhul tuleb need otsinguterminid paigutada WHERE-klauslisse ja kasutada üksikute ridade valimiseks. (Pidage meeles, et koondfunktsioone ei saa WHERE-klauslis kasutada.) HAVING-klausel ei ole SQL-keele vajalik osa – iga HAVING-klausli abil kirjutatud päringut saab kirjutada muul kujul ilma seda kasutamata.
Näide 18. HAVING konstrukti kasutamine.Rohkem kui ühe töötajaga ettevõtte iga filiaali kohta määrake töötajate arv ja nende töötasu suurus.
VALIharu nr, COUNT T(personali nr) AS loendama, SUMMA(palk) AS summa
FROMPersonal
GROUP BYfiliaal nr
LOENDAB(personali arv) > 1
TELLIharu nr;
See näide on sarnane eelmisele, kuid see kasutab täiendavaid piiranguid, mis näitavad, et meid huvitab teave ainult ettevõtte nende osakondade kohta, kus töötab rohkem kui üks inimene. Sarnane nõue kehtib ka rühmade kohta, seega peaks päring kasutama konstruktsiooni HAVING. Päringu tulemused on toodud tabelis. 28.
Tabel 28
| filiaalNo loendussumma |
| В00З 3 54000,00 |
| B005 2 39000,00 |
Alampäringud. Selles jaotises käsitleme täielike SELECT-lausete kasutamist, mis on manustatud teise SELECT-lause kehasse. Väline(Teine) SELECT-lause kasutab täitmise tulemust sisemine(esimene) operaator, kes määrab kogu toimingu lõpptulemuse sisu. Sisemised päringud leiate välimise SELECT-lause klauslitest WHERE ja HAVING - sel juhul nimetatakse neid alampäringud, või pesastatud päringud. Lisaks saab sisemisi SELECT-lauseid kasutada INSERT-, UPDATE- ja DELETE-lausetes . Alampäringuid on kolme tüüpi.
Skalaarne alampäring tagastab ühe veeru ja ühe rea lõikepunktist valitud väärtuse, s.t. ainus tähendus. Põhimõtteliselt saab skalaarset alampäringut kasutada kõikjal, kus on vaja ühte väärtust. Skalaarsete alampäringute variandid on toodud näidetes 13 ja 14.
Stringi alampäring tagastab mitme tabeli veeru väärtused, kuid ühe reana. Stringi alampäringut saab kasutada kõikjal, kus kasutatakse stringiväärtuse konstruktorit, tavaliselt predikaate. Stringi alampäringu varianti on näidatud näites 15.
Tabeli alampäring tagastab ühe või mitme tabeli veeru väärtused rohkem kui ühes reas. Tabeli alampäringut saab kasutada kõikjal, kus tabelit saab määrata, näiteks IN-predikaadi operandina.
Näide 19: alampäringu kasutamine võrdsuse testiga. Koosta ettevõtte filiaalis aadressil Main St1 463 töötavate töötajate nimekiri.
VALI
FROMPersonal
KUSharu nr = (VALI haru nr
FROMFiliaal
KUStänav = "Põhitänav 163" );
Sisemine SELECT-lause (SELECT branchNo FROM Branch ...) on mõeldud aadressil "163 Main St" asuva ettevõtte filiaali numbri määramiseks. (Ettevõttel on ainult üks selline filiaal, seega on see näide skalaarse alampäringu näide.) Pärast vajaliku filiaali numbri saamist teostatakse välimine alampäring, et hankida selle filiaali töötajate kohta üksikasjalik teave. Teisisõnu, sisemine SELECT-lause tagastab tabeli, mis koosneb ühest väärtusest "BOOV". See tähistab aadressil "163 Main St1" asuva ettevõtte filiaali numbrit. Selle tulemusena on välimine SELECT-lause järgmine vorm:
VALIpersonalNo, fName, INname, ametikoht
FROMPersonal
KUSfiliaalNo = "B0031;
Selle päringu tulemused on esitatud tabelis. 29.
Tabel 29
Taotluse tulemus
| personal nr | fNimi | INnimi | positsiooni |
| SG37 | Ann | Pöök | Assistent |
| SG14 | David | Ford | Juhendaja |
| SG5 | Susan | Bränd | Juht |
Alampäring on tööriist ajutise tabeli loomiseks, mille sisu hangib ja töötleb väline operaator. Alampäringu saab määrata vahetult pärast võrdlustehtereid (st operaatorid =,<, >, <=, >=, <>) klauslis WHERE või HAVING. Alampäringu tekst peab olema sulgudes.
Näide 20. Alampäringute kasutamine koondamisfunktsioonidega. Koostage nimekiri kõigist töötajatest, kelle palk on üle keskmise, näidates ära, kui palju nende palk ületab ettevõtte keskmist töötasu.
VALIpersonalNr, fNimi, INnimi, ametikoht, palk - ( VALI AVG(palk) FROM personal) AS salDiff
FROMPersonal
KUSpalk > ( VALI AVG(palk) FROM S t a f f) ;
Tuleb märkida, et otse ei saa kaasata päringu avaldis"KUS palk > AVG (palk)", kuna kasutada liitmist funktsioonid WHERE-klauslis on keelatud. Soovitud tulemuse saavutamiseks tuleks luua alampäring, mis arvutab välja keskmise aastapalga ning seejärel kasutada seda välises SELECT-lauses, mis otsib infot nende ettevõtte töötajate kohta, kelle palk ületab selle keskmise. Teisisõnu, alampäring tagastab ettevõtte keskmise aastapalga 17 000 naela.
Selle skalaarse alampäringu tulemust kasutatakse välimises SELECT-lauses nii töötasu keskmisest tasemest kõrvalekaldumise arvutamiseks kui ka töötajate info valimiseks. Seetõttu on välimine SELECT-lause järgmine vorm:
VALIpersonalNr, fNimi, INnimi, ametikoht, palk - 17000 Nagu salDiff
FROMPersonal
KUSpalk > 17000;
Päringu tulemused on toodud tabelis. kolmkümmend.
Tabel 30.
Taotluse tulemus
| personal nr | fNimi | INnimi | positsiooni | salDiff |
| SL21 | John | Valge | Juht | 13000.00 |
| SG14 | David | Ford | Juhendaja | 1000.00 |
| SG5 | Susan | Bränd | Juht | 7000.00 |
Rakenda alampäringutele järgides reegleid ja piirangud.
1. Alampäringutes ei tohiks kasutada klauslit ORDER BY, kuigi see võib esineda välises SELECT-lauses.
2. Alampäringu SELECT loend peab koosnema üksikute veergude nimedest või neist koosnevatest avaldistest, välja arvatud juhul, kui alampäringus kasutatakse märksõna EXISTS.
3. Vaikimisi viitavad alampäringu veerunimed tabelile, mille nimi on määratud alampäringu klauslis FROM. Siiski on võimalik viidata ka välise päringu FROM-klauslis määratud tabeli veergudele, kasutades kvalifitseeritud veergude nimesid (nagu allpool kirjeldatud).
4. Kui alampäring on üks kahest võrdlustoiminguga seotud operandist, tuleb alampäring määrata selle toimingu paremal küljel. Näiteks eelmises näites toodud päringu tähis on vale, kuna alampäring on paigutatud võrdlustoimingu vasakule küljele palga veeru väärtuse suhtes.
VALI
FROMPersonal
KUS(VALI AVG(palk) töötajatelt)< salary;
Näide 21. Pesastatud alampäringud ja IN-predikaadi kasutamine. Koostage nimekiri üüripindadest, mille eest vastutavad peatnn 163 asuva ettevõtte filiaali töötajad.
VALIkinnisvara nr, tänav, linn, sihtnumber, tüüp, ruumid, rent
FROMPropertyForRent
5. peatükk. SQL-keel: andmete töötlemine 189
KUSpersonalNo IN (VALI personali nr
FROMPersonal
KUSbrancliNo = (VALI haru nr
FROMFiliaal
KUStänav = "163 Main S t "));
Esimene, kõige sisemine päring, on mõeldud ettevõtte filiaali numbri määramiseks, mis asub aadressil Main St. 463. Teine, vahepealne päring, otsib teavet selles filiaalis töötavate töötajate kohta. Sel juhul on rohkem kui üks rida andmeid valitud ja seetõttu välispäringus kasutada võrdlusoperaatorit =. Selle asemel tuleb kasutada märksõna IN Väline päring otsib infot liisinguobjektide kohta, mille eest need ettevõtte töötajad vastutavad, mille kohta saadi andmed. vahepäringu täitmise tulemus Päringu tulemused on toodud tabelis 31.
Tabel 31
Taotluse tulemus
| vara nr | tänav | linn | postiindeks | tüüp | ruumid | rentida |
| PG16 | 5 Novar Dr | Glasgow | G129AX | Korter | ||
| PG36 | 2 Mõisa tee | Glasgow | G324QX | Korter | ||
| PG21 | 18 Dale Rd | Glasgow | G12 | Maja |
Märksõnad ANY ja KÕIK. Märksõnu ANY ja ALL saab kasutada alampäringutega, mis tagastavad ühe numbriveeru. Kui alampäringule eelneb märksõna KÕIK, on võrdlustingimus täidetud ainult siis, kui see kehtib kõigi alampäringu tulemuste veerus olevate väärtuste puhul. Kui alampäringu tekstile eelneb märksõna ANY, loetakse võrdlustingimus täidetuks, kui see on täidetud alampäringu tulemuseks olevas veerus vähemalt ühe (või mitme) väärtuse puhul. Kui alampäringu täitmise tulemuseks on tühi väärtus, siis KÕIK märksõna puhul loetakse võrdlustingimus täidetuks ja ANY märksõna puhul loetakse see täitmata. Vastavalt ISO standardile saab lisaks kasutada MÕNE märksõna, mis on MÕIME märksõna sünonüüm.
Näide 22. Kasutades märksõnu ANY ja MÕNE. Otsige üles kõik töötajad, kelle palk ületab vähemalt palgaüks ettevõtte filiaali töötaja numbri "booz" all.
VALIpersonalNr, fNimi, INnimi, ametikoht, palk
FROMPersonal
KUSpalk > SOME(VALI palk
FROMPersonal
KUSharu nr = "B003");
Kuigi selle päringu saab kirjutada alampäringuga, mis määrab osakonna töötajate numbri "BOHO" miinimumpalga, mille järel saab välimises alampäringus valida teabe kõigi ettevõtte töötajate kohta, kelle palk ületab seda väärtust (vt näide 20), on võimalik ka teine lähenemine, mis seisneb MÕNE/ANY märksõnade kasutamises. Sel juhul loob sisemine alampäring väärtuste komplekti (12000, 18000, 24000) ja välimine päring valib teabe nende töötajate kohta, kelle palk on suurem kui ükski selle väärtus.
komplekt (tegelikult rohkem kui minimaalne väärtus - 12000). Seda alternatiivset meetodit võib pidada loomulikumaks kui miinimumpalga määratlemist alampäringus. Kuid mõlemal juhul saadakse samad päringutulemused, mis on esitatud tabelis. 32 .
Tabel 32
Taotluse tulemus
| personal nr | fNimi | INnimi | positsiooni | palk |
| SL21 | John | Valge | Juht | 30000.00 |
| SG14 | David | Ford | Juhendaja | 18000.00 |
| SG5 | Susan | Bränd | Juht | 24000.00 |
Näide 23. Kasutades märksõna KÕIK. Leidke ettevõtte filiaali numbrist "booz" kõik töötajad, kelle palk on suurem kui mõne töötaja palk.
VALIpersonalNr, fNimi, INarae, ametikoht, palk
FROMPersonal
KUSpalk > KÕIK(VALI palk
FROMPersonal
KUSfiliaalNo = "BOG3");
Üldiselt on see taotlus sarnane eelmisele. Ja sel juhul oleks võimalik kasutada alampäringut, mis määrab osakonna personali palga maksimumväärtuse numbri "BOZ" all ning seejärel välispäringu abil valida info kõigi ettevõtte töötajate kohta, kelle palk ületab see väärtus. Kuid selles näites on valitud KÕIK märksõna lähenemisviis. Päringu tulemused on toodud tabelis. 33 .
Tabel 33
Taotluse tulemus
| personal nr | INnimi | fNimi | positsiooni | palk |
| SL21 | Valge | John | Juht | 30000,00 |
Mitme tabeli päringud. Kõikidel ülalkirjeldatud näidetel on sama oluline piirang: saadud tabelisse paigutatud veerud valitakse alati ühest tabelist. Paljudel juhtudel sellest aga ei piisa. Mitme lähtetabeli veergude kombineerimiseks saadud tabelis peate toimingu sooritama ühendused. SQL-is kasutatakse liitmistoimingut kahest tabelist pärineva teabe kombineerimiseks, moodustades igast tabelist valitud seotud ridade paarid. Kombineeritud tabelisse paigutatud ridade paarid koostatakse nendes sisalduvate määratud veergude väärtuste võrdsuse alusel.
Kui teil on vaja teavet hankida mitmest tabelist, võite kasutada alampäringut või ühendada tabelid. Kui saadud päringute tabel peab sisaldama veerge erinevatest lähtetabelitest, siis on soovitatav kasutada tabelite ühendamise mehhanismi. Ühenduse teostamiseks piisab, kui määrata FROM-klauslis kahe või enama tabeli nimed, eraldades need komadega, ja seejärel lisada päringusse WHERE-klausel, mis määratleb määratud tabelite ühendamiseks kasutatavad veerud. Lisaks saate tabelinimede asemel kasutada pseudonüümid, neile FROM-klauslis määratud. Sel juhul tuleb tabelinimed ja neile määratud varjunimed eraldada tühikutega. Varjunimesid saab kasutada veergude nimede selgitamiseks alati, kui võib tekkida ebaselgus, millisesse tabelisse veerg kuulub. Lisaks saab varjunimesid kasutada tabelinimede lühendamiseks. Kui tabeli jaoks on määratletud pseudonüüm, saab seda kasutada kõikjal, kus on vaja määrata selle tabeli nimi.
Näide 24. Lihtne ühendus. Tee nimekiri kõigist klientidest, kes on juba vähemalt ühe üüripinna üle vaadanud ja asja kohta oma arvamuse avaldanud.
VALIc.clientNo, fName, INname, propertyNo, comment
FROMKlient c, vaatamine v
KUSc.clientNo = v.clientNo;
See aruanne nõuab teavet nii kliendi tabelist kui ka kuvamise tabelist, seega kasutame päringu koostamiseks tabelite ühendamise mehhanismi. SELECT-klausel loetleb kõik veerud, mis tuleks saadud päringutabelisse paigutada. Pange tähele, et veerg clientNo nõuab kvalifikatsiooni, kuna veerg võib esineda ka mõnes teises ühendamises osalevas tabelis. Seetõttu on vaja selgesõnaliselt märkida, millised tabeliväärtused meid huvitavad. (Selles näites oleks võinud sama lihtsalt valida vaatetabelist veeru clientNo väärtused.) Nime täpsustamiseks määratakse vastava tabeli nimi (või selle pseudonüüm) eesliitena veeru nime ette. Meie näide kasutab väärtust "c", mis on määratud kliendi tabeli varjunimena. Saadud ridade genereerimiseks kasutatakse lähtetabelite ridu, millel on veerus clientNo identne väärtus. See tingimus määratakse otsingutingimuse määramisega:.clientNo=v.clientNo. Sarnaseid lähtetabelite veerge nimetatakse sobivad veerud. Kirjeldatud toiming on samaväärne operatsiooniga seosed võrdsuse kaudu relatsioonialgebra. Päringu tulemused on toodud tabelis. 34.
Tabel 34
Taotluse tulemus
| klient nr | fNimi | INnimi | vara nr | kommenteerida |
| CR56 | Aline | Stewart | PG36 | |
| CR56 | Aline | Stewart | PA14 | liiga väike |
| CR56 | Aline | Stewart | PG4 | |
| CR62 | Maarja | Tregear | PA14 | söögituba pole |
| CR76 | John | Kay | PG4 | liiga kauge |
Kõige sagedamini tehakse mitme tabeliga päringuid kahes tabelis, mis on ühendatud üks-mitmele (1:*) või vanem-laps-suhtega. Ülaltoodud näites, mis hõlmab juurdepääsu tabelitele Klient ja vaatamine, on viimased ühendatud just sellise seosega. Tabeli Vaade (alam) iga rida on seotud ainult ühe tabeli Klient (ülem) reaga, samas kui tabeli Klient (ülem) sama rida saab seostada
paljude tabeli Vaade (laps) ridadega. Päringu täitmisel genereeritavad reapaarid on alam- ja ülemtabelite kõigi kehtivate ridade kombinatsioonide tulemus. Jaotis 3.2.5 kirjeldab üksikasjalikult, kuidas relatsiooniandmebaasis loovad tabelite primaar- ja võõrvõtmed vanem-alasuhte. Võõrvõtit sisaldav tabel on tavaliselt alam, samas kui primaarvõtit sisaldav tabel on alati ülem. SQL-päringus vanem-alasuhte kasutamiseks peate määrama otsingutingimuse, mis võrdleb võõrvõtit ja primaarvõtit. Näide 24 võrdleb kliendi tabeli primaarvõtit (v. clientNo) vaatamistabeli välisvõtmega (v. clientNo).
Lisaks pakub SQL-standard selle ühenduse määratlemiseks järgmisi viise:
FROMKlient koos LIITU Vaatamine v PEAL c.clientNo = v.clientNo
FROMKlient J OIN Vaatamine KASUTAMINE klient nr
FROMKlient LOODUSLIK LIITUMINE Vaatamine
Igal juhul asendab klausel FROM algsed FROM- ja WHERE-klauslid. Kuid esimene valik loob kahe identse clientNo veeruga tabeli, samal ajal kui kahel teisel juhul sisaldab tulemuseks olev tabel ainult ühte clientNo veergu.
Näide 25. Liitmistabelite tulemuste sorteerimine. Märkige iga ettevõtte filiaali kohta töötajate arv ja rendipindade eest vastutavate töötajate nimed ning märkige ka ruumid, mille jaoks
millele nad vastavad.
VALIs.branchNo, s.staffNo, fName, INname, propertyNo
FROMStaff s, PropertyForRent p
KUSs.staffNo = p.staffNo
TELLIs.branchNo, s.staffNo, propertyNo;
Tulemuste lugemise hõlbustamiseks sorteeritakse saadud väljund, kasutades peamise sortimise võtmena osakonna numbrit ning kõrvalklahvidena personalinumbrit ja vara numbrit. Päringu tulemused on toodud tabelis. 35.
Tabel 35
Taotluse tulemus
| filiaal nr | Töötaja nr | fNimi | INnimi | vara nr |
| WHO | SG14 | David | Ford | PG16 |
| WHO | SG37 | Ann | Pöök | PG21 |
| WHO | SG37 | Ann | Pöök | PG36 |
| BOO5 | SL41 | Maarja | Lee | PL94 |
| SBI7 | SA9 | Julie | Kuidas | PA14 |
Näide 26. Kolme laua ühendamine. Loetlege iga ettevõtte filiaali kohta rendipindade eest vastutavate töötajate numbrid ja nimed, näidates ära ettevõtte filiaali asukoha linn ja nende rajatiste numbrid, mille eest iga töötaja vastutab.
VALI b.branchNo, b.city, s.staffNo, fName, INname, propertyNo
FROM Filiaal b, Personal, PropertyForRent p
KUS b.branchNo = s.branchNo JA s.staffNo = p.staffNo
TELLI b.filiaali nr, personali nr, kinnisvara nr;
Saadud tabel peab sisaldama veerge kolmest lähtetabelist – haru, personal ja PropertyForRent –, nii et päring peab nende tabelitega liituma. Filiaali ja personali tabeleid saab ühendada tingimusega b.branchNo=*s .branchNo, mille tulemusena seostatakse ettevõtte filiaalid neis töötavate töötajatega. Staff ja PropertyForRent tabeleid saab ühendada tingimusega s.staffNo=p.staffNo. Selle tulemusena seostatakse iga töötaja üüripindadega, mille eest ta vastutab. Päringu tulemused on toodud tabelis. 36.
Tabel 36
Päringu tulemused
| filiaal nr | linn | personalMo | fNimi | INnimi | vara nr |
| B003 | Glasgow | SG14 | David | Ford | PG16 |
| B003 | Glasgow | SG37 | Ann | Pöök | PG21 |
| B003 | Glasgow | SG37 | Ann | Pöök | PG36 |
| B005 | London | SL41 | Julie | Lee | PL94 |
| B007 | Aberdeen | SA9 | Maarja | Kuidas | PA14 |
Pange tähele, et SQL-standard lubab kasutada FROM- ja WHERE-konstruktsioonide alternatiivset sõnastust:
FROM(B filiaal b LIITUDA personali KASUTAMISE filiaali nr) AS bs
LIITUPropertyForRent p KASUTAMINE personal nr
Näide 27. Rühmitamine mitme veeru järgi. Määrake üüripindade arv, mille eest ettevõtte iga töötaja vastutab.
VALIs.branchNo, S.staffNo, COUNT(*) AS loendama
FROM Staff s, PropertyForRent p
KUS S.staffNo = p.staffNo
GROUP BYs.branchNo, s.staffNo
TELLIs.branchNo, s.staffNo;
Nõutava akti koostamiseks tuleb esmalt välja selgitada, kes ettevõtte töötajatest üüripindade eest vastutab. Selle probleemi saab lahendada, ühendades tabelid Staff ja PropertyForRent, kasutades klausli FROM/WHERE veergu staffNo. Seejärel on vaja moodustada osakonnanumbritest ja selle töötajate personalinumbritest koosnevad rühmad, mille jaoks tuleks kasutada GROUP BY konstruktsiooni. Lõpuks tuleb saadud tabel sorteerida, kasutades ORDER BY klauslit. Päringu tulemused on toodud tabelis. 37.
Tabel 37
Taotluse tulemus
| filiaal nr | personal nr | loendama |
| В00З | SG14 | |
| В00З | SG37 | |
| B005 | SL41 | |
| B007 | SA9 |
Ühenduste loomine.Ühendus on alamhulk kahest kutsutavast tabelist saadud andmete üldisemast kombinatsioonist Descartes. Kahe tabeli Descartes'i korrutis on veel üks tabel, mis koosneb kõigist võimalikest ridade paaridest, mis on mõlema tabeli osa. Saadud tabeli veergude komplekt on kõik esimese tabeli veerud, millele järgnevad kõik teise tabeli veerud. Kui sisestate päringu kahe tabeli vastu ilma WHERE-klauslit määramata, on päringu tulemuseks SQL-keskkonnas nende tabelite Descartes'i korrutis. Lisaks pakub ISO standard SELECT-lause jaoks spetsiaalset vormingut, mis võimaldab arvutada kahe tabeli Descartes'i korrutist:
SELECT(*j veeruloend]
TAbelistNamel RISTLIITUMINECaYeUlte2
Vaatame uuesti näidet, kus kliendi ja Tabelite vaatamise ühendamine toimub ühise veeru clientNo abil. Töötades tabelitega, mille sisu on toodud tabelis. 3.6 ja 3.8 sisaldab nende tabelite Descartes'i korrutis 20 rida (4 klienditabeli rida x 5 vaatamistabeli rida = 20 rida). See on samaväärne näites 5.24 kasutatud päringu esitamisega, kuid ilma WHERE-klauslit kasutamata. Kahe tabeli SELECT-lause abil ühendamise tulemusi sisaldava tabeli genereerimise protseduur on järgmine.
1. Moodustatakse FROM konstruktsioonis määratud tabelite Descartes'i korrutis.
2. Kui päring sisaldab WHERE-klauslit, rakendades Descartes'i korrutitabeli igale reale otsingutingimusi ja salvestades tabelisse ainult need read, mis vastavad määratud tingimustele. Seoses relatsioonialgebraga nimetatakse seda operatsiooni piirang Descartes'i toode.
3. Iga järelejäänud rea jaoks määratakse iga SELECT loendis määratud elemendi väärtus, mille tulemuseks on saadud tabeli eraldi rida.
4. Kui algne päring sisaldab konstruktsiooni SELECT DISTINCT, eemaldatakse saadud tabelist kõik topeltread.
5. Kui teie käivitatav päring sisaldab klauslit ORDER BY,
©2015-2019 sait
Kõik õigused kuuluvad nende autoritele. See sait ei pretendeeri autorlusele, kuid pakub tasuta kasutamist.
Lehe loomise kuupäev: 2016-08-07
Kuidas ma saan teada konkreetse tarnija toodetud arvutimudelite arvu? Kuidas määrata samade tehniliste omadustega arvutite keskmist hinda? Nendele ja paljudele teistele statistilise teabega seotud küsimustele saab vastata kasutades lõplikud (koond)funktsioonid. Standard pakub järgmisi koondfunktsioone:
Kõik need funktsioonid tagastavad ühe väärtuse. Samal ajal funktsioonid COUNT, MIN Ja MAX kohaldatav mis tahes andmetüübile, samas SUMMA Ja AVG kasutatakse ainult numbriväljade jaoks. Funktsioonide erinevus COUNT(*) Ja COUNT(<имя поля>) on see, et teine ei võta arvutamisel arvesse NULL väärtusi.
Näide. Leia personaalarvutite minimaalne ja maksimaalne hind:
Näide. Leidke saadaolev tootja A toodetud arvutite arv:
Näide. Kui meid huvitab tootja A poolt toodetud erinevate mudelite arv, siis saab päringu vormistada järgmiselt (kasutades seda, et tabelis Toode on iga mudel kirjas üks kord):
Näide. Leidke saadaolevate erinevate tootja A toodetud mudelite arv. Päring sarnaneb eelmisele päringule, kus tuli määrata tootja A toodetud mudelite koguarv. Siit tuleb leida ka erinevate mudelite arv arvutilaud (st need, mis on müügil).
Tagamaks, et statistiliste näitajate hankimisel kasutatakse ainult kordumatuid väärtusi, kui koondfunktsioonide argument saab kasutada DISTINCT parameeter. Teine parameeter KÕIK on vaikeväärtus ja eeldab, et loendatakse kõik veerus tagastatud väärtused. operaator,
Kui meil on vaja saada toodetud arvutimudelite arv kõik tootja, peate kasutama GROUP BY klausel, järgneb süntaktiliselt pärast KUS klauslid.
GROUP BY klausel
GROUP BY klausel kasutatakse väljundstringide rühmade määratlemiseks, millele saab rakendada koondfunktsioonid (COUNT, MIN, MAX, AVG ja SUM). Kui see klausel puudub ja kasutatakse koondfunktsioone, siis kõik veerud, mille nimed on mainitud VALI, tuleks lisada koondfunktsioonid, ja neid funktsioone rakendatakse kogu ridade komplektile, mis vastavad päringu predikaadile. Vastasel juhul kõik loendi SELECT veerud ei sisalda koondfunktsioonides tuleb täpsustada klauslis GROUP BY. Selle tulemusena jagatakse kõik väljundpäringu read rühmadesse, mida iseloomustavad nendes veergudes samad väärtuste kombinatsioonid. Pärast seda rakendatakse igale rühmale koondfunktsioone. Pange tähele, et GROUP BY puhul käsitletakse kõiki NULL väärtusi võrdsetena, st. NULL-väärtusi sisaldava välja järgi rühmitamisel langevad kõik sellised read ühte rühma.Kui kui on olemas klausel GROUP BY, SELECT-klauslis puuduvad koondfunktsioonid, tagastab päring igast rühmast lihtsalt ühe rea. Seda funktsiooni koos märksõnaga DISTINCT saab kasutada tulemusekomplektist dubleerivate ridade kõrvaldamiseks.
Vaatame lihtsat näidet:
| VALI mudel, COUNT(mudel) AS Kogus_mudel, AVG(hind) AS Avg_price ARVUTIST GROUP BY mudeli järgi; |
Selles taotluses määratakse iga arvutimudeli jaoks nende arv ja keskmine maksumus. Kõik sama mudeli väärtusega read moodustavad rühma ja SELECT väljund arvutab iga rühma väärtuste arvu ja keskmise hinna väärtused. Päringu tulemuseks on järgmine tabel:
| mudel | Kogus_mudel | Keskmine_hind |
| 1121 | 3 | 850.0 |
| 1232 | 4 | 425.0 |
| 1233 | 3 | 843.33333333333337 |
| 1260 | 1 | 350.0 |
Kui SELECT oleks kuupäeva veerg, siis oleks võimalik need näitajad välja arvutada iga konkreetse kuupäeva kohta. Selleks tuleb rühmitusveeruna lisada kuupäev ja seejärel arvutatakse koondfunktsioonid iga väärtuste kombinatsiooni jaoks (mudel-kuupäev).
On mitmeid spetsiifilisi koondfunktsioonide täitmise reeglid:
- Kui taotluse tulemusena ühtegi rida ei saadud(või rohkem kui üks rida antud rühma jaoks), siis puuduvad lähteandmed ühegi koondfunktsiooni arvutamiseks. Sel juhul on funktsioonide COUNT tulemus null ja kõigi teiste funktsioonide tulemus NULL.
- Argument koondfunktsioon ise ei saa sisaldada koondfunktsioone(funktsioon funktsioonist). Need. ühes päringus on võimatu näiteks saada keskmiste väärtuste maksimumi.
- Funktsiooni COUNT täitmise tulemus on täisarv(TÄISARV). Teised koondfunktsioonid pärivad nende töödeldavate väärtuste andmetüübid.
- Kui funktsioon SUM annab tulemuse, mis on suurem kui kasutatud andmetüübi maksimaalne väärtus, viga.
Seega, kui taotlus ei sisalda GROUP BY klauslid, See koondfunktsioonid hulka arvatud SELECT klausel, käivitatakse kõigil saadud päringuridadel. Kui taotlus sisaldab GROUP BY klausel, iga ridade komplekt, millel on punktis määratletud veeru või veergude rühma samad väärtused GROUP BY klausel, moodustab rühma ja koondfunktsioonid tehakse igale rühmale eraldi.
ON pakkumine
Kui KUS klausel defineerib ridade filtreerimiseks predikaadi, siis ON pakkumine kehtib pärast rühmitamist defineerida sarnane predikaat, mis filtreerib rühmi väärtuste järgi koondfunktsioonid. See klausel on vajalik väärtuste kinnitamiseks, mis on saadud kasutades koondfunktsioon mitte punktis määratletud kirjeallika üksikutelt ridadelt FROM klausli, ja alates selliste joonte rühmad. Seetõttu ei saa sellist tšekki sisaldada KUS klausel.
