SQL - 11-dars

• AVG() funktsiyasi o'rta ustunni aylantiradi.


• COUNT() funksiya ustundagi qatorlar sonini aylantiradi.


• MAX() funksiya ustundagi eng katta qiymatni aylantiradi.


• MIN() funksiya ustundagi eng kichik qiymatni aylantiradi.


• SUM() funksiyasi shpalning yig'indisi qiymatini aylantiradi.

Narxlardan MIN(narx), MAX(narx), AVG(narx) ni tanlang;

Endi biz "Budynok do'stim uchun" pochta egasi tomonidan bizga qancha tovarlar olib kelganini bilmoqchimiz (id=2). Bunday savolni jamlash unchalik oson emas. Keling, tinchlanaylik, iltimos:

1. Ta'minotlar (kiruvchi) jadvalidan siz avval "Budinok Druku" pochta menejeri (id=2) tomonidan amalga oshirilgan etkazib berish identifikatorini (id_incoming) tanlashingiz kerak:

2. Endi Supply Journal (magazine_incoming) jadvalidan siz 1-bandda topilgan zahiralarda topilgan tovarlarni (id_mahsulot) va ularning miqdorini (miqdorini) tanlashingiz kerak. 1-banddan so'rash uchun biz quyidagilarni qo'shamiz:

3. Endi hosil bo'lgan jadvalga topilgan mahsulotlarning narxlar jadvalida saqlanadigan narxlarini qo'shishimiz kerak. Keyin bizga ta'minot jurnali (magazine_incoming) va id_mahsulot bo'yicha narxlarning jamlangan jadvali kerak bo'ladi:

4. Aniq chiqqan jadvalda Sum ustuni ko'rinmaydi, shuning uchun hisoblangan pechlar. Bunday tizimlarni yaratish imkoniyati MySQL-ga o'tkazildi. Buning uchun siz hisoblanayotgan elementni va hisoblash uchun mas'ul ekanligingizni kiritishda ko'rsatishingiz kerak. Bizning arizamizda bunday standart summa deb ataladi va hisob-kitob miqdori va narxiga asoslanadi. Yangi maqola nomi AS so‘zi bilan to‘ldirilsin:

Magazin_incoming.id_mahsuloti, jurnalning_incoming.miqdori, narxlar.narxi, jurnalning_kirish.miqdori*narxlar.narxini TANLANGAN JURNALI_Kiruvchi_jurnal, narxlar QAYERDA jurnali_incoming.id_mahsulot= narxlar.id_mahsulot VA id_com

5. Yaxshi, biz summani yo'qotdik va biz "Budynok druku" pochta egasi tomonidan bizga qancha tovar olib kelganini bilib oldik. SUM() funksiyasi sintaksisi:

jadval_nomidan SUM(jadval_nomi) ni tanlang;

Biz jadval nomini bilamiz - summa, lekin bizda jadval nomining o'qi yo'q, shuning uchun bu so'rov natijasidir. Nega ovora? Bunday muammolar uchun MySQL-da kontseptsiya mavjud. Bu noyob nomlarni taqdim etadigan va keyingi qidiruv uchun ma'lumotlar bazasida saqlanishi mumkin bo'lgan tanlovni so'raydi.

Haqoratli ko'rinishlarni yaratish sintaksisi:

CREATE VIEW ViewName AS to'ldirilgan;

Keling, so'rovimizni report_vendor bayonoti sifatida saqlaymiz:

TANLANGAN magazin_incoming.id_mahsuloti, jurnali_incoming.miqdori, narxlar.narxi, jurnalning_kirish.miqdori*narxlar.narx AS KO'RISHNI YARATING jurnali_kirish, narxlar WHERE jurnali_incoming.id_product=com );

6. Endi o'qni SUM() funksiyasi yordamida o'zgartirish mumkin:

report_vendor FROM SUM(summa) ni tanlang;

Baltalar va natijaga erishildi, garchi buning uchun biz hissalar, ma'lumotlar, hisoblangan narsalar va namoyonlarni vikorystvuvat qilishimiz kerak edi. Shunday qilib, ba'zida natijaga erishish uchun hech qanday sababsiz o'ylash kerak. Keyin biz ikkita juda muhim mavzuni - hisob-kitoblar va namoyishlar bilan chiqdik. Keling, ular haqida keyinroq gaplashamiz.

Hisoblangan maydonlar (stovpts)

element_nomi_1, element_nomi_2, element_nomi_1*element_nomi_2 AS element_nomini jadval_nomidan tanlang;

Yana bir nuance AS kalit so'zi bo'lib, u hisoblanayotgan shaxsning ismini ko'rsatish uchun ishlatilgan. Aslida, ushbu kalit so'zdan foydalanib, siz har qanday turdagi odamlar uchun taxalluslardan foydalanishingiz mumkin. Nima kerak? Kodni tez o'qish uchun. Masalan, bizning bayonotimiz quyidagicha ko'rinishi mumkin:

A.id_mahsuloti, A.miqdori, B.narxi, A.miqdori*B.narx ASJA jurnali_kirish A AS, narxlar B QAYERDA A.id_mahsulot= B.id_mahsulot VA id_incoming= (ID_vendor identifikatorini QAYERDA TANGLASH) SIFATIDA hisobot_vendorni KOʻRISH YARATING = 2);

Kutib turing, u juda qisqaroq va aqlliroq.

Tashqi ko'rinish

Jadvalda ko'rsatilgan narsalarni eslab qolsangiz, ular ma'lumotlarni saqlaydilar va uni boshqa jadvaldan tortib olmaydilar. Birinchidan, jadvallardagi ma'lumotlarni o'zgartirganda, taqdim etilgan natijalar ham o'zgaradi. Aks holda, ularni topshirishdan oldin kerakli ma'lumotlarni qidirgandan so'ng, ma'lumotlar bazasining samaradorligi pasayadi. Shuning uchun ular bilan yomonlik qilish yaxshi emas.

Bir yoki bir nechta kompyuter modellari ishlab chiqarilganligini qanday aniqlash mumkin? Kompyuterning texnik xususiyatlaridan kelib chiqib, uning o'rtacha narxini qanday aniqlash mumkin? Bu va ba'zi statistik ma'lumotlar bilan bog'liq boshqa oziqlanish uchun qo'shimcha ma'lumot uchun quyidagilarni tanlashingiz mumkin. sumka (agregat) funktsiyalari. O'tkazish standarti quyidagi jamlangan funktsiyalarni o'z ichiga oladi:

Barcha funktsiyalar bir xil qiymatni aylantiradi. Qaysi funktsiya ostida COUNT, MINі MAX har qanday turdagi ma'lumotlarga zastosovny, o'sha paytda SUMі AVG Vikorist faqat raqamli maydonlar uchun ishlatiladi. Funktsiyalar orasidagi farq COUNT(*)і COUNT(<имя поля>) NULL qiymatlari aybdor emas deb ishoniladi.

dumba. Shaxsiy kompyuterlarning minimal va maksimal narxini toping:

dumba. Ishlab chiqaruvchi A tomonidan chiqarilgan kompyuterlar sonini aniq bilib oling:

dumba. Agar biz A generatori tomonidan ishlab chiqarilgan turli xil modellar sonini hisobga oladigan bo'lsak, biz buni quyidagi tartibda shakllantirishimiz mumkin (Mahsulot jadvalida har bir model bir marta qayd etilganligini hisobga olgan holda):

dumba. Ishlab chiqaruvchi A tomonidan ishlab chiqarilgan turli xil modellar sonini toping. Oldidagiga o'xshash so'rov, unda siz ishlab chiqaruvchi A tomonidan ishlab chiqarilgan modellar sonini aniqlashingiz kerak. Shuningdek, siz turli xil modellar sonini bilishingiz kerak. stol kompyuteri (sotish uchun mavjud).

Statistik ko'rsatkichlarni tanlashda faqat noyob qiymatlar hisobga olinishini ta'minlash uchun, funktsiyalarni birlashtirish uchun argumentlar vikoristuvati mumkin DISTINCT parametri. Junior ALL parametri Vikorist hisob-kitoblarni kuzatib boradi va ustunda aylanadigan barcha ma'nolarning qisqacha mazmunini etkazadi. Operator,

Chunki biz o'chirilgan kompyuter modellari sonini yo'q qilishimiz kerak teriga xos virobnik, keyin vikorist bo'lish kerak taklif GROUP BY, keyin sintaktik tajovuzkor taklif QAYERDA.

Taklif GROUP BY

Agar SELECT sana bilan bog'langan bo'lsa, ma'lum bir sananing ko'rsatkichlarini hisoblash mumkin edi. Buning uchun sanani guruh operatori sifatida qo'shish kerak, so'ngra agregat funktsiyalar teri kombinatsiyasi qiymati uchun hisoblab chiqiladi (model-sana).

Bir guruh qo'shiqlar uxlayapti agregat funktsiyalarining qovushqoqligi qoidalari:

• Natijada, men yozaman suv qatori olib tashlanmadi(yoki ushbu guruh uchun bitta qator), keyin har qanday agregat funktsiyalarni hisoblash uchun chiqish ma'lumotlari kunlik. Bunday holda, COUNT funktsiyasining natijasi nolga teng bo'ladi, qolgan barcha funktsiyalarning natijasi esa NULL bo'ladi.
• Dalil agregat funktsiyalari Men agregat funktsiyalarni o'zim nazorat qila olmayman(funktsiya ichidagi funktsiya). Tobto. Bitta yozuvda, aytaylik, maksimal o'rtacha qiymatlarni saqlab qolish mumkin emas.
• COUNT funktsiyasining natijasi butun son(INTEGER) Boshqa agregat funktsiyalar to'plangan ma'lumotlar qiymatlari turlarini kamaytiradi.
• SUM funksiyasidan foydalanilganda, tahlil qilinayotgan ma'lumotlar turining maksimal qiymatidan oshgan natija ko'rsatiladi. kechir.

Ey azizim, nega qasos olmaysiz? taklif GROUP BY, Bu agregat funktsiyalari, tarkibiga kiradi taklif SELECT, barcha hosil bo'lgan qatorlarni chizish kerak. Men qasos olishni so'rayapman taklif GROUP BY, har bir qatorlar toʻplami, biroq ular birlashmasi yoki tayinlangan elementlar guruhi uchun muhim qiymatga ega boʻlishi mumkin. taklif GROUP BY, guruh hosil qilish, bu agregat funktsiyalari Teri guruhini diqqat bilan tekshirish kerak.

HAVING taklifi

Yakshcho taklif QAYERDA satrlarni filtrlash uchun predikatni bildiradi, keyin HAVING taklifi tiqilish guruhlashdan keyin qiymatlar ortidagi guruhlarni filtrlaydigan predikatga o'xshash qiymat uchun agregat funktsiyalari. Ushbu taklif ma'noni tekshirish uchun talab qilinadi, go'yo uni olib tashlash kerak agregat funktsiyalari tayinlangan bir necha qator yozuvlardan emas daryolar FROM, a z bunday qatorlar guruhlari. Shuning uchun bunday teskari o'zgarishlarga yo'l qo'yib bo'lmaydi daryo QAYERDA.

Biz sumkalarimizni to'ldirishimiz kerak. Biroq, bu SQL modifikatsiyasining natijalari, ammo ma'lumotlar bazasi jadvali qiymatlarining xulosalari. SQL agregat funktsiyalari bitta natija qiymatini ayirish orqali qiymatga nisbatan ishlaydi. Eng ko'p ishlatiladigan funktsiyalar SQL agregat funktsiyalari SUM, MIN, MAX, AVG va COUNT. Agregat funktsiyalarning turg'unligining ikki turi mavjud. Birinchisi: agregat funktsiyalar quvvatga bo'ysunadi va bitta natija qiymatini aylantiradi. Boshqalar: Agregat funktsiyalari har bir guruhdan olingan qiymatlarni ajratib olish uchun maydonlar bo'yicha guruhlash uchun SQL GROUP BY operatoridan foydalanadi. Guruhlashtirmasdan agregat funktsiyalarni tanlashni ko'rib chiqamiz.

SQL SUM funksiyasi

SQL SUM funktsiyasi ma'lumotlar bazasi jadvalining yig'indisi qiymatini aylantiradi. Siz faqat nuqtaga, qaysi raqamlarning ma'nosiga yopishib olishingiz mumkin. Natijani olish uchun SQL so'rovi quyidagicha boshlanadi:

SUM (COLUMN_NAME) ni tanlang ...

Bu iboradan keyin FROM (TABLE_NAME) va keyingi WHERE bandidan keyin aql kelishi mumkin. Bundan tashqari, element nomidan oldin DISTINCT ko'rsatilishi mumkin, ya'ni faqat noyob qiymatlar kiritilgan. Barcha qiymatlar sug'urta uchun qoplanadi (buning uchun siz ayniqsa DISTINCT emas, balki ALL ni kiritishingiz mumkin, lekin HAMMA so'zi majburiy emas).

Agar siz MS SQL Serverda ushbu dars uchun ma'lumotlar bazasiga kirishni istasangiz, agar ma'lumotlar bazasi kompyuteringizda o'rnatilmagan bo'lsa, uni o'rnatishingiz mumkin, ushbu xabardagi ko'rsatmalarga rioya qiling .

Boshidan kompaniyaning ma'lumotlar bazasidan foydalanish mumkin - Company1. Ma'lumotlar bazasi, jadval va yonayotgan ma'lumotlar jadvalini yaratish uchun skript - ushbu post uchun fayllar .

dumba 1. Ularning sho'ba korxonalari va josuslari haqidagi ma'lumotlarga ega kompaniyalarning ma'lumotlar bazasi mavjud. Xodimlar jadvali, shuningdek, xodimlarning ish haqi to'g'risidagi ma'lumotlarga mos keladi. Jadvaldagi tanlov quyidagicha ko'rinadi (rasmni kattalashtirish uchun sichqonchaning chap tugmasi bilan bosing):

Barcha ish haqi to'lovlari miqdorini chiqarish uchun quyidagi buyruqdan foydalaning (MS SQL Serverda - USE company1 forvard dizaynidan;):

Xodimlardan SUM (ish haqi) TANLASH

Bu 287664.63 raqamiga yoziladi.

Va hozir . Biz allaqachon narsalarni tartibga solishni boshlaymiz, ularni amaliy narsalarga yaqinlashtiramiz.

SQL MIN funksiyasi

SQL MIN funksiyasi raqamlar kabi qiymatlar bilan ham ishlaydi va raqamning minimal qiymatini aylantiradi. Bu funksiya SUM funksiyasiga o'xshash sintaksisga ega.

Butun 3. Ma'lumotlar bazasi va jadval 1-ilovadagi kabi.

42-bo'limda ishchi kuchining eng kam ish haqi to'g'risida ma'lumot olish kerak. Kimlar uchun quyidagi so'rovni yozmoqdamiz (MS SQL Serverda - forvard konstruktsiyasi USE company1 bilan;):

To'g'ri qiymat 10,505,90.

Men qo'ng'iroq qilaman mustaqil rahbarlik huquqi. Kimning va boshqa huquqlar nafaqat xodimlar jadvalini, balki kompaniyalarning bo'linmalari to'g'risidagi ma'lumotlarni o'z ichiga olgan Tashkilotlar jadvalini ham talab qiladi:

Butun 4. Xodimlar jadvalidan oldin kompaniyalarning bo'linmalari to'g'risidagi ma'lumotlarni o'z ichiga olgan tashkilot jadvali qo'shiladi. Bostonda yetishtirilgan Veddilydan bitta spivorotenik tomonidan qayta ishlangan jinslarning minimal sonini kiriting.

SQL MAX funktsiyasi

SQL MAX funktsiyasi shunga o'xshash tarzda ishlaydi va shunga o'xshash sintaksisdan foydalanadi, u barcha qiymatlar orasidagi maksimal qiymatni hisoblash kerak bo'lganda muzlaydi.

Butun 5.

42-bo'limda talaba ishchilarining maksimal ish haqini aniqlash kerak. Ular uchun quyidagi yozuvni yozamiz (MS SQL Serverda - USE company1 forvard konstruktsiyasi bilan);

Qaytish qiymati 18352.80

Prishaning soati mustaqil o'sish huquqi.

Butun 6. Men yana ikkita jadval bilan ishlayapman - Xodimlar va Org. Bo'linma nomini va komissiyaning maksimal qiymatini kiriting, bu sizga bo'linmalardan (bo'linma) Sharqiy guruhga tegishli bo'lgan bo'linishdan bir sarmoyani olib tashlash imkonini beradi. Vikoristati JOIN (birlashtirilgan jadval).

SQL AVG funksiyasi

Oldingi funksiya tavsiflari sintaksisi toʻgʻri va SQL AVG funksiyasiga oʻxshash. Bu funktsiya shpalning o'rta qiymatining o'rta qiymatini aylantiradi.

Butt 7. Ma'lumotlar bazasi va jadval oldingi dumba bilan bir xil.

Iltimos, 42-bo'limda harbiy xizmatchilarning o'rtacha ish staji haqida bizga xabar bering. Biz kimlar uchun quyidagi so'rovni yozmoqdamiz (MS SQL Serverda - oldinga inshoot USE company1 bilan;):

Natijada 6,33 bo'ladi

Button 8. Biz bitta stol bilan ishlaymiz - Xodimlar. 4 yildan 6 yilgacha ish tajribasiga ega bo'lgan ishchilarning o'rtacha ish haqini kiriting.

SQL COUNT funktsiyasi

SQL COUNT funktsiyasi ma'lumotlar bazasi jadvalidagi bir qator yozuvlarni aylantiradi. Agar siz so'rovga SELECT COUNT(COLUMN_NAME) ... kiritsangiz, natijada ustun qiymatlari NULL (qiymatlanmagan) bo'lgan yozuvlar hisobga olinmagan holda ko'p sonli yozuvlar bo'ladi. Agar yulduzchadan foydalansangiz va argumentni SELECT COUNT(*) ... deb boshlasangiz, natija jadvaldagi barcha yozuvlar (qatorlar) soni bo'ladi.

Button 9. Ma'lumotlar bazasi va jadval oldingi dumba bilan bir xil.

Komissiyalarni olib tashlash uchun barcha homiylar sonini bilish kerak. Comm ustunining qiymati NULL bo'lmagan serverlar soni, to'g'rirog'i, boshlang'ich yozuv (MS SQL Serverda - USE company1 ning oldingi konstruktsiyasi bilan;):

Xodimlardan COUNT (Comm) TANLANING

Natija 11 bo'ladi.

Button 10 Ma'lumotlar bazasi va jadval oldingi dumba bilan bir xil.

Agar siz jadvalda qancha yozuv borligini bilishingiz kerak bo'lsa, COUNT funktsiyasiga argument sifatida yulduzchadan foydalanishingiz mumkin (MS SQL Serverda - USE kompaniyasining oldinga konstruktsiyasi bilan;):

Xodimlardan COUNT (*) TANLANG

Natija 17 bo'ladi.

Kelgusida o'zini o'zi boshqarish huquqi Siz vikoristingizni to'ldirishingiz kerak.

Button 11. Biz bitta stol bilan ishlaymiz - Xodimlar. Rejalashtirish bo'limidagi ishchilar sonini kiriting (tekisliklar).

SQL GROUP BY (guruhlangan) dan funksiyalarni bir vaqtning o'zida jamlash

Endi SQL GROUP BY operatori yordamida agregat funktsiyalarning ta'rifini ko'rib chiqamiz. SQL GROUP BY operatori ma'lumotlar bazasi jadvalidagi ustunlarning natijaviy qiymatlarini guruhlash uchun ishlatiladi. Veb-saytda dars, ushbu operatorga bag'ishlanish.

"Portal Stunning 1" ma'lumotlar bazasini tekshiring. Ma'lumotlar bazasi, jadvallar va ma'lumotlar jadvallarini yaratish uchun skript - ushbu post uchun fayllar .

Button 12 Bundan tashqari, portalning ma'lumotlar bazasi ajoyib. Bir kun davomida berilgan ovozlar haqidagi ma'lumotlarni ko'rsatish uchun E'lonlar jadvalini o'rnatishingiz kerak bo'ladi. Turkum bo'limi katta toifalar haqidagi ma'lumotlarni o'z ichiga oladi (masalan, Buzilmaslik), Ehtiyot qismlar bo'limida toifaga kiritilgan boshqa qismlar haqida ma'lumot mavjud (masalan, Kvartiralar va Kottejlar qismlari Buzilmaslik toifasining qismlari). Birliklar bo'limida qabul qilingan arizalar soni to'g'risidagi ma'lumotlar, "Pul" bo'limida esa arizalar uchun yig'ilgan pul miqdori haqida ma'lumotlar mavjud.

SQL GROUP BY operatoridan foydalanib, siz har bir toifadagi hujjatlarni topshirish uchun olingan tiyinlar summasini bilib olishingiz mumkin. Biz quyidagi so'rovni yozamiz (MS SQL Serverda - USE adportal1; oldingi konstruktsiyasidan foydalangan holda):

Turkum boʻyicha Eʼlonlar guruxidan pul sifatida SOUM (pul) ni tanlang

Button 13 Ma'lumotlar bazasi bir xil jadval - o'tmishda ishlatilgan.

Ko'rib turganingizdek, SQL GROUP BY operatori teri toifasining qaysi qismida eng ko'p chalkashlikni oldi. Biz quyidagi so'rovni yozamiz (MS SQL Serverda - USE adportal1; oldingi konstruktsiyasidan foydalangan holda):

Turkum, qism, MAX (birliklar) ni E’lonlar guruxidan Turkum bo‘yicha maksimal qilib TANLASH

Natijada jadval bo'ladi:

Moddalar va individual qiymatlar bitta jadvalda ko'rsatilishi mumkin qo'shimcha operator UNION uchun so'rovlar natijalarini birlashtirish.

Relyatsion ma'lumotlar bazalari va SQL tili