Biz ma'lumotlar bazalarini va SQL tilini tahlil qilamiz. (3-qism) - "A dan Zgacha Java loyihasi"

Java loyihasini yaratish haqidagi turkum maqola (boshqa materiallarga havolalar oxirida). Uning maqsadi - asosiy texnologiyalarni tahlil qilish, natijada telegram botini yozish.

• Uy vazifasini tekshirish
  • Asosiy kalitni qo'shish
  • Moldovani qo'shish
  • Shaharlar jadvalini tuzing
  • Shaharlarga chet el kalitini qo'shing
  • Bonus qismi. Sinov
• SELECT bayonoti
  • WHERE parametri
  • ORDER BY parametr
  • GROUP BY parametr
• Uy vazifasi
• Xulosa

Assalomu alaykum, xonimlar va janoblar, keling, ma'lumotlar bazalari, SQL va boshqa narsalar haqida gapirishni davom ettiramiz. Bugungi material qisman nazariya va qisman amaliyotni o'z ichiga oladi. Eslatib o‘tamiz, oxirgi marta biz hamma narsani qanday sozlash, ma’lumotlar bazasi, jadval yaratish va undan ma’lumotlarni olish haqida gaplashgan edik. Masofadan zondlash bilan biror narsa ishlaganligini ko'rish vaqti keldi. Menimcha, uning yarmini faqat oldingi maqoladan kelib chiqib amalga oshirish mumkin edi. Ma'lum bo'lishicha, ilovani to'g'ri yig'ish va hamma narsani kamroq yoki kamroq chiroyli qilish uchun ma'lumotlar bazalari haqida gapirish kerak va ular haqida gapirish uchun ko'p vaqt sarflash kerak bo'ladi.

Uy vazifasini tekshirish

Vazifalarni muvaffaqiyatli bajargan har bir kishiga katta hurmat. Bu shuni anglatadiki, siz bu faqat sizga kerakligini va faqat sizga yordam berishini tushunasiz. Mening vazifamni e'tiborsiz qoldirganlar uchun shartni eslatib o'taman:

1. ID maydonidan mamlakat jadvali sxemasiga asosiy kalitni (PRIMARY KEY) qo'shishingiz kerak.
2. Mamlakat jadvaliga yana bir mamlakatni qo'shing - Moldova.
3. Oldingi maqolaning sxemasiga ko'ra, tavsiflangan barcha maydonlarni o'z ichiga olgan shahar jadvalini yarating. Maydon nomlari quyidagicha bo'ladi: id, nom, mamlakat_id, aholi.
4. Shahar jadvaliga asosiy kalit qo'shing.
5. Shahar jadvaliga chet el kalitini qo'shing.

Boshlash uchun oldingi maqolaning birinchi qismidan foydalanamiz va ma'lumotlar bazasi terminaliga o'tamiz.

Asosiy kalitni qo'shish

Siz birlamchi kalitni (PRIMARY KEY) ikki usulda qo'shishingiz mumkin: darhol jadval yaratishda yoki yaratilgandan so'ng, ALTER TABLE yordamida.

Jadvalni yaratishda asosiy kalit

Biz allaqachon jadval yaratganimiz uchun va uni o'chirmasdan biz ushbu ma'lumotlar bazasida ushbu yondashuvni ko'rsata olmaymiz, biz shunchaki vaqtinchalik test ma'lumotlar bazasini yaratamiz, unda biz hamma narsani qilamiz. Keling, quyidagi buyruqlarni kiritamiz:

• yangi ma'lumotlar bazasini yaratish:

  $CREATE DATABASE testi;

• asosiy kalitni qo'shib jadval yarating:

  $ CREATE TABLE country(id INT, nomi VARCHAR(30), PRIMARY KEY (id));

Umuman olganda, hech qanday murakkab narsa yo'q. O'zgaruvchilar e'lon qilingandan so'ng, quyidagi PRIMARY KEY (id) qismi qo'shiladi , bu erda birlamchi kalit bo'ladigan maydon nomi qavs ichida beriladi. Keling, jadval sxemasi qanday o'zgarganini ko'rib chiqaylik: $ DESC mamlakat; Ko'rib turganingizdek, PRI qiymati identifikator kiritish uchun kalit maydonida paydo bo'ldi .

Jadval yaratilgandan keyin asosiy kalit

Yuqorida aytib o'tganimdek, jadval yaratilgandan keyingi birinchi kalit ALTER TABLE yordamida tayinlanishi mumkin . Biz ushbu misolni shaharlarimiz ma'lumotlar bazasida ishga tushiramiz :

• testdan ma'lumotlar bazamizga o'tamiz:

  $USE shaharlari;

• Keling, bizning ma'lumotlar bazasida ekanligimizni tekshirib ko'raylik (u erda boshqa maydon bo'lishi kerak - aholi). Buning uchun biz yozamiz:

  $ DESC aholisi;

hamma narsa to'g'ri, stol bizniki. Keling, quyidagilarni yozamiz:

$ ALTER TABLE mamlakati ASOSIY KALT QO'SHISH (id);

• va darhol buyruq bilan tekshiring:

  $DESC mamlakati;

Rasmdan ko'rinib turibdiki, hamma narsa to'g'ri, PRI qiymati aynan qaerda bo'lishi kerak. Aytgancha, biz test ma'lumotlar bazasi bilan ishladik. Endi biz uni o'chirishimiz kerak: nima uchun serverni chalkashtirib yuborishimiz kerak, to'g'rimi? Buning uchun biz juda mashhur buyruqdan foydalanamiz: $ DROP DATABASE testi;

Moldovani qo'shish

Avval biz nimani yozib olishimizni hal qilishimiz kerak. Keyingi identifikatorimiz 4 bo'ladi. Nomi Moldova bo'ladi va uning aholisi 3550900. Shuning uchun biz allaqachon bilgan INSERT INTO buyrug'ini bajaramiz: $ INSERT INTO country VALUES (4, 'Moldova', 3550900); Va biz ushbu qiymatning ma'lumotlar bazasida aniqligini tekshiramiz: $ SELECT * FROM country WHERE id = 4; Ma'lumotlar so'rovida men qaysi maydon qidirilishini darhol aniqladim, shuning uchun biz faqat bitta yozuvni oldik, bu bizga kerak bo'lgan narsadir.

Shaharlar jadvalini tuzing

Ma'lumotlar bazasi haqidagi birinchi maqoladagi diagrammadan foydalanib , biz jadval haqida kerakli ma'lumotlarni olamiz. U quyidagi maydonlarni o'z ichiga oladi:

• id - noyob identifikator;
• nomi - shahar nomi;
• country_id — mamlakat tashqi kaliti;
• aholi — shahar aholisi.

Har safar o'ziga xos identifikator yozish biroz stress, shunday emasmi? Buni MySQL rasmiylariga qoldirmoqchiman . Va shunday yo'l bor - AUTO INCREMENT . Biz buni raqamli maydonga qo'shishimiz kerak va agar biz qiymatlarni aniq o'tkazmasak, MySQL ning o'zi avvalgisiga nisbatan identifikatorni bittaga oshiradi. Shuning uchun jadval yaratish quyidagicha bo'ladi: $ CREATE TABLE city ( id INT AUTO_INCREMENT, nomi VARCHAR(30), mamlakat_id INT, aholi INT, PRIMARY KEY (id)); Hamma narsa to'g'ri bajarilganligini aniqlash uchun jadval diagrammasini ko'rib chiqaylik: $ DESC city; Jadval diagrammasidan ko'rinib turibdiki, bizda id maydoni uchun yangi tavsif mavjud - auto_increment. Shunday qilib, biz hamma narsani to'g'ri qildik. Keling, to'liq tuzilgan jadvaldagi ma'lumotlarni tekshiramiz. Buning uchun biz vazifaning oxirgi qismini - tashqi kalitni bajaramiz.

Shaharlarga chet el kalitini qo'shing

Chet el kaliti uchun quyidagi buyruq bo'ladi: $ ALTER TABLE city ADD FOREIGN KEY (country_id) REFERENCES country(id); Va darhol jadval sxemasida nima noto'g'ri ekanligini tekshirib ko'raylik: u bir soat ichida o'zgarganmi? $DESC shahri;

Bonus qismi. Sinov

Men uni vazifaga qo'shishni unutibman - birinchi qismning skrinshotidagi ma'lumotlarni to'ldiring. Men unutdim, shuning uchun endi buni o'zim qilaman. Va qiziqqanlar uchun, buni mensiz o'zingiz qilishingiz mumkin, keyin biz tekshiramiz;) Xarkov, Kiev, Minsk, Odessa, Voronej bor edi va biz Kishinyovni ham qo'shamiz. Lekin bu safar biz identifikatorlarni uzatmaymiz, ularni o'tkazib yuboramiz: $ INSERT INTO shahar (nomi, mamlakat_id, aholi) VALUES ('Xarkov', 1, 1443000), ('Kiyev', 1, 3703100), ('Minsk' , 3, 2545500), (“Odessa”, 1, 1017699), (“Voronej”, 2, 1058261), (“Kishinev”, 4, 695400); Ko'rib turganingizdek, bitta INSERT INTO buyrug'i yordamida bir vaqtning o'zida bir nechta yozuvlarni kiritishingiz mumkin. Qulay narsa, esda tuting) Va darhol jadvalda nima borligini ko'rib chiqaylik: $ SELECT * FROM shahar; AUTO_INCREMENT - biz xohlagandek ishladi. Biz ularni topshirmagan bo'lsak ham, ID fayllari to'ldirilgan. Chet el kaliti - bu qaram narsa. Uning to'g'ri ishlashini tekshirish uchun siz chet el jadvalida mavjud bo'lmagan xorijiy kalitni yozishga harakat qilishingiz mumkin. Aytaylik, id = 5 Qozog'iston deb qaror qildik. Lekin aslida bu mamlakatlar jadvalida emas. Va ma'lumotlar bazasi qasamyod qilishini tekshirish uchun shaharni qo'shing - Ostona: $ INSERT INTO shahar (nomi, mamlakat_id, aholi) VALUES ('Astana', 5, 1136156); Va biz tabiiy ravishda xatoga duch kelamiz: Endi xorijiy kalit bizning ma'lumotlar bazamizda bo'lmagan shaharga mamlakatni belgilashga urinmasligimizga ishonch hosil qiladi. Uy vazifasining bu qismini tugallangan deb hisoblash mumkin - yangisiga o'ting :)

SELECT bayonoti

Xo'sh, hamma narsa endi juda qo'rqinchli ko'rinmaydi, shunday emasmi? Yana bir bor ta'kidlab o'tmoqchimanki, Java dasturchilari uchun ma'lumotlar bazasini bilish shart. Ma'lumotlar bazasisiz siz hech qaerga borolmaysiz. Ha, men allaqachon ariza yozishni boshlamoqchiman, roziman. Lekin bu zarur. Shunday qilib, biz shu yo'lni davom ettiramiz. SELECT bayonotidan foydalanib, biz ma'lumotlar bazasidan ma'lumotlarni olamiz. Ya'ni, bu odatiy DML operatsiyasi (siz nima ekanligini allaqachon unutdingizmi?...))) OLDINI maqolalarni qayta o'qing). Relyatsion ma'lumotlar bazalarining afzalliklari nimada? Ular ma'lumotlarni yig'ish va olish uchun ajoyib funksiyalarga ega. SELECT iborasi aynan shu maqsadda ishlatiladi. Bunda hech qanday murakkab narsa yo'qdek tuyuladi, to'g'rimi? Ammo tushunadigan narsa hali ko'p ekanligi ma'lum bo'ldi) Biz qurishimiz mumkin bo'lgan asoslarni tushunish biz uchun muhim. SELECT iborasi bilan eng oddiy so'rov bitta jadvaldan barcha ma'lumotlarni tanlashdir. Menga wikidagi operatorlar SELECT soʻrovida aynan qanday tartibda borishi kerakligi haqidagi tavsif juda yoqdi, shuning uchun uni bu yerga koʻchirib olaman:

SELECT
  [DISTINCT | DISTINCTROW | ALL]
  select_expression,...
FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula}]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]

Bu yerda siz avval GROUP BY operatorini, keyin esa WHERE operatorini qo'ya olmasligingizni ko'rishingiz mumkin. Buni eslab qolish kerak, shunda keyinchalik ular qaerdan kelib chiqqanligi aniq bo'lmagan xatolar uchun xafa bo'lmaydi. $ SELECT * FROM shahardan; Ammo barcha ma'lumotlarni o'chirib tashlash biz uchun qiziq emas. Agar biz mixlarni mikroskop bilan bolg'alamoqchi bo'lsak, xuddi shunday bo'ladi [1] , [2] . Ma'lumotlar bazasi filtrlash, saralash va jamlash operatsiyalarini Java kodiga qaraganda ancha tezroq bajarganligi sababli, bu masalani ma'lumotlar bazasiga qo'ygan ma'qul. Shunday qilib, vazifalarni murakkablashtirish orqali biz yangi funksiyalarni ochamiz.

WHERE parametri

Tanlovni filtrlash uchun WHERE so'zi ishlatiladi . Buni quyidagicha talqin qilish kerak: SELECT * FROM tablename (jadval jadvali nomidan barcha maydonlarni tanlang) QERDA talbe_row = 1 (bu yerda yozuvlarda jadval_satr maydoni 1 ga teng). Shuni ta'kidlash kerakki, so'rovdagi kalit so'zlarning tartibi muhim ahamiyatga ega. WHERE a =1 FROM table_name SELECT * deb yoza olmaysiz. Rus tili uchun bu yaxshi va ba'zilar uchun bu tartibsizlik kabi ko'rinmasligi mumkin, ammo SQL uchun bu qabul qilinishi mumkin emas. Biz quyidagi so'rovni yozamiz: $ SELECT * FROM city WHERE country_id = 1; Va biz Ukraina shaharlarini tanladik. Yomon emas, to'g'rimi? Agar biz nafaqat ukrainani, balki belarusni ham xohlasak-chi? Shu maqsadda biz maydon olishi mumkin bo'lgan qiymatlar to'plamini sanab o'tishimiz mumkin: $SELECT * FROM city WHERE country_id IN(1, 3); Bunga javoban bizda allaqachon ikki mamlakat shaharlari bor. Filtrlash uchun bir nechta shartlar mavjud bo'lsa-chi? Aytaylik, biz ikki milliondan ortiq aholisi bo'lgan shaharlarni xohlaymizmi? Buning uchun OR va VA so'zlaridan foydalaning : $ SELECT * FROM city WHERE country_id IN (1, 3) VA aholi > 2000000; Ajoyib, lekin yana bitta shartni qo'shishimiz kerak bo'lsa-chi - oddiy ibora orqali ismlarni qidirish (men bu erda oddiy iboralarni tasvirlamayman: mana bu 4 qismda "qisqacha" qilgan odam )? Masalan, biz shaharni qanday yozishni eslaymiz, lekin toʻliq emas... Buning uchun filtrlash ifodasiga LIKE kalit soʻzini qoʻshishingiz mumkin : $ SELECT * FROM city WHERE country_id IN (1, 3) VA aholisi > 2000000 YOKI “%hark%” LIKE nomi; Va shu tarzda biz Xarkovni ham qo'lga kiritdik. Natijada izlanishlarimiz juda yaxshi chiqdi, deyishimiz mumkin. Lekin men ID bo'yicha emas, balki aholi soni bo'yicha saralashni xohlayman, lekin qanday qilib? Ha juda oddiy...

ORDER BY parametr

ORDER BY-dan foydalanib, biz olingan yozuvlarni ma'lum bir maydon bo'yicha saralashimiz mumkin. U raqamlarni ham, satrlarni ham tartiblaydi. Oldingi so‘rovni kengaytirib, aholi soni bo‘yicha tartiblaymiz: TARTIBI BO‘YICHA aholi qo‘shamiz: $ SELECT * FROM FROM WHERE country_id IN (1, 3) VA aholi > 2000000 YOKI “%hark%” YOKI TARTIBI aholi bo‘yicha; Ko'rib turganimizdek, saralash tabiiy tartibda, ya'ni o'sish tartibida sodir bo'ldi. Agar biz buning aksini xohlasak-chi? Buning uchun siz DESC so'zini qo'shishingiz kerak: $ SELECT * FROM city FROM WHERE country_id IN (1, 3) VA aholisi > 2000000 YOKI “%hark%” SIYAH nomi TARTIB BY aholi DESC; Endi saralash aholining qisqarishiga asoslangan. Va ma'lumotlar bazasi buni juda tez bajaradi: u erda hech qanday Collections.sort solishtirish mumkin emas. Endi qatorlar bo'yicha, teskari tartibda nomlar bo'yicha saralaymiz: $ SELECT * FROM FROM country_id IN (1, 3) VA aholisi > 2000000 YOKI “%hark%” YOKI BO'YICHA TARTIBI NOMI BO'YICHA TARTIB BERING DESC;

GROUP BY parametr

Muayyan maydonlar bo'yicha yozuvlarni guruhlash uchun foydalaniladi. Bu odatda agregat funktsiyalardan foydalanish uchun kerak bo'ladi... Agregat funktsiyalar nima?)) Agar ular turli yozuvlar uchun bir xil bo'lsa, ba'zi maydonlar bo'yicha guruhlash mantiqan to'g'ri keladi. Keling, misolimizdan foydalanib, bu nimani anglatishini ko'rib chiqaylik. Aytaylik, shaharlarda xorijiy kalitlar - mamlakat identifikatorlari mavjud. Shunday qilib, ID bir mamlakat shaharlari uchun bir xil. Shuning uchun, siz ular bo'yicha yozuvlarni olishingiz va guruhlashingiz mumkin: $ SELECT country_id, COUNT(*) FROM city GROUP BY BY country_id; Ammo yig'ish funktsiyalarisiz u biroz zaif ko'rinadi, tan olishingiz kerak. Shuning uchun, keling, bir nechta eng keng tarqalgan funktsiyalarni ko'rib chiqaylik:

• COUNT - yozuvlar soni, guruhlashsiz ishlatilishi mumkin, COUNT(*) sifatida ishlatiladi . Ba'zi maydonlar bo'yicha guruhlashda - COUNT(guruhlangan_maydon);
• MAX - ma'lum bir maydon uchun maksimal qiymatni topadi;
• MIN - ma'lum bir maydon uchun minimal qiymatni topadi;
• SUM - ma'lum bir maydon uchun yig'indini topadi;
• AVG - o'rtacha qiymatni topadi.

Umuman olganda, bu funktsiyalarni guruhlashsiz ishlatish mumkin, shundan keyingina faqat bitta maydon ko'rsatiladi. Keling, ularni shahar aholisi uchun sinab ko'raylik: $ COUNT COUNT(*) FROM shahardan; Ular so'ragan narsalari bor edi. Faqat yozuvlar soni. Ba'zan bu foydali. Misol uchun, agar biz ma'lum bir muallifning maqolalari sonini bilishimiz kerak bo'lsa. Ularni ma'lumotlar bazasidan chiqarib tashlash va hisoblashning hojati yo'q. Siz shunchaki COUNT() dan foydalanishingiz mumkin. $ Shahardan AVG(aholi) TANLASH; $ MIN (aholi) shahardan TANLASH; Va bu erda guruhlash kuchga kiradi. Masalan, vazifa mamlakatdagi eng kichik shaharni olishdir. Buni qanday qilishni allaqachon bilasizmi? Oʻzingiz sinab koʻring, keyin koʻring: $ SELECT country_id as Country, MIN(aholi) shahardan WHERE GROUP BY country_id; Hozircha biz faqat davlat identifikatorini ko‘ramiz, lekin bu muhim emas – keyingi safar hammasini qilamiz. Shunday qilib, allaqachon natija bor va biz xohlagan narsaga erishdik - ID = 1 bo'lgan mamlakatdagi eng kichik shahar. Qolgan funktsiyalar bir xil bo'ladi. Shuni ta'kidlash kerakki, guruhlash va agregatsiyadan foydalanganda * orqali barcha maydonlarni yirtib tashlash ishlamaydi! O'ylab ko'r ;)

Uy vazifasi

Oldingi maqolalar natijalariga ko'ra, uy vazifasi bajarilayotgani aniq, shuning uchun davom etaylik)) Ha, uy vazifasini bajaradigan har bir kishi izohlarda "+" qo'yishda davom etadi. Kelajakda buni davom ettirishim uchun uy vazifasi mavzusi siz uchun qiziqarli bo'lishi men uchun muhim. Ha, sharhlaringizni muntazam o'qib chiqaman. Albatta, men kamroq javob beraman. Ular qiyinroq SQL muammolarini berishni so'rashganini ko'rdim. Biz qo'shilishni o'rganmagunimizcha, qiziqarli muammolar bo'lmaydi, shuning uchun menga keyingi materiallar uchun kerak bo'lganlar bo'ladi.

Vazifalar:

HAVING operatorini tushuning va bizning misolimizdagi jadvallar uchun namuna so'rovini yozing. Agar siz aniqroq qilish uchun ba'zi maydonlar yoki ko'proq qiymatlarni qo'shishingiz kerak bo'lsa, ularni qo'shing. Agar kimdir xohlasa, sharhlaringizda o'z misolingizni yozing: agar vaqtim bo'lsa, shu tarzda men ham tekshirib ko'raman.
1. UI orqali ma'lumotlar bazasi bilan ishlash uchun MySQL Workbench dasturini o'rnating. O'ylaymanki, biz allaqachon konsoldan ishlash bo'yicha yetarlicha amaliyotga ega bo'ldik. Ma'lumotlar bazasiga ulaning. Agar siz ma'lumotlar bazasi bilan ishlash uchun boshqa narsadan foydalansangiz, bu vazifani o'tkazib yuboring. Bu erda va bundan keyin men faqat MySQL Workbench-dan foydalanaman.
2. Bizning ma'lumotlarimizdan foydalangan holda qabul qilish uchun so'rovlarni yozing:
   1. eng kichik/eng aholi yashaydigan mamlakat;
   2. mamlakat aholisining o'rtacha soni;
   3. ismlari "a" bilan tugaydigan mamlakatlar aholisining o'rtacha soni;
   4. aholisi to'rt milliondan ortiq bo'lgan mamlakatlar soni;
   5. aholi sonining kamayishi bo'yicha mamlakatlarni saralash;
   6. mamlakatlarni tabiiy tartibda nomlari bo'yicha tartiblang.

Xulosa

Bugun biz oxirgi darsdagi uy vazifasini batafsil muhokama qildik. Bundan tashqari, men buni qilmaganlar uchun ham, qilganlar uchun ham muhim deb bilaman. Birinchisi uchun bu javobni topish, ikkinchisi uchun esa uni natijangiz bilan solishtirish imkoniyatidir. Loyihadagi o'zgarishlardan xabardor bo'lish uchun mening GitHub hisobimga obuna bo'ling . Men u erda butun kod bazasini saqlab qolaman. Bu tashkilotda hamma narsa sodir bo'ladi . Keyinchalik, biz SELECT bayonotini muhokama qildik. U biz uchun eng muhimi. Aynan shu orqali ma'lumotlar uchun barcha so'rovlar amalga oshiriladi va biz buni tushunishimiz kerak. Eng muhimi, parametrlarni qo'shish tartibini eslab qolishdir (QAYERDA, ORDER BY, GROUP BY va boshqalar). Ha, men mumkin bo'lgan hamma narsani aytmadim, lekin men o'zim uchun bunday maqsadni qo'ymadim. Ha, siz allaqachon ariza yozishga ishtiyoqmand ekanligingizni bilaman. Sabr qiling, bu sizga kerak bo'lgan narsadir. Ham loyiha uchun, ham kasbiy o'sishingiz uchun. Kutganingizda, Git sizga allaqachon tanish ekanligiga ishonch hosil qiling. Men uni sukut bo'yicha, taniqli vosita sifatida ishlataman. O'qiganingiz uchun barchaga rahmat. Keyingi maqolada biz ma'lumotlar bazasi ulanishlari va ulanishlar haqida gapiramiz. Bu erda ajoyib vazifalar bo'ladi))

Seriyadagi barcha materiallar ro'yxati ushbu maqolaning boshida.