Biz verilənlər bazası və SQL dilini təhlil edirik. (3-cü hissə) - "A-dan Z-yə Java layihəsi"

Java layihəsinin yaradılması haqqında silsilədən məqalə (digər materiallara keçidlər sonundadır). Onun məqsədi əsas texnologiyaları təhlil etməkdir, nəticə teleqram botunu yazmaqdır.

• Ev tapşırığını yoxlamaq
  • Əsas açarın əlavə edilməsi
  • Moldovanı əlavə edir
  • Şəhər cədvəlini yaradın
  • Şəhərlərə xarici açar əlavə edin
  • Bonus hissəsi. Test
• SELECT bəyanatı
  • WHERE parametri
  • SİFARİŞ BY parametr
  • GROUP BY parametr
• Ev tapşırığı
• Nəticə

Salam, xanımlar və cənablar, verilənlər bazası, SQL və digər mövzular haqqında danışmağa davam edək. Bugünkü materialda hissə nəzəriyyəsi və qismən təcrübə olacaq. Nəzərinizə çatdırım ki, keçən dəfə biz hər şeyi necə qurmaq, verilənlər bazası, cədvəl yaratmaq və ondan məlumat almaq barədə danışmışdıq. Uzaqdan zondlama ilə bir şeyin olub olmadığını görmək vaxtıdır. Fikrimcə, bunun yarısını ancaq əvvəlki yazıya əsaslanaraq etmək olardı. Məlum oldu ki, tətbiqi düzgün yığmaq və hər şeyi az-çox gözəl etmək üçün verilənlər bazası haqqında danışmaq, onlar haqqında danışmaq üçün isə çox vaxt sərf etmək lazımdır.

Ev tapşırığını yoxlamaq

Tapşırıqları uğurla yerinə yetirən hər kəsə böyük hörmət. Bu o deməkdir ki, siz başa düşürsünüz ki, buna yalnız sizin ehtiyacınız var və o, yalnız sizə kömək edir. Tapşırığıma etinasız yanaşanlar üçün şərti xatırlatmaq istəyirəm:

1. ID sahəsindən ölkə cədvəlinin sxeminə əsas açarı (PRIMARY KEY) əlavə etməlisiniz.
2. Ölkə cədvəlinə daha bir ölkə əlavə edin - Moldova.
3. Əvvəlki məqalənin sxeminə uyğun olaraq, təsvir olunan bütün sahələri ehtiva edən bir cədvəl şəhəri yaradın. Sahə adları aşağıdakı kimi olacaq: id, ad, ölkə_id, əhali.
4. Şəhər cədvəlinə əsas açar əlavə edin.
5. Şəhər cədvəlinə xarici açar əlavə edin.

Başlamaq üçün əvvəlki məqalənin birinci hissəsindən istifadə edək və verilənlər bazası terminalına keçək.

Əsas açarın əlavə edilməsi

Siz əsas açarı (PRIMARY KEY) iki yolla əlavə edə bilərsiniz: dərhal cədvəl yaratarkən və ya yaradıldıqdan sonra ALTER TABLE-dən istifadə etməklə.

Cədvəl yaratarkən əsas açar

Artıq cədvəl yaratdığımıza və onu silmədən bu yanaşmanı bu verilənlər bazası daxilində göstərə bilməyəcəyimizə görə, sadəcə olaraq hər şeyi edəcəyimiz müvəqqəti test bazası yaradacağıq. Aşağıdakı əmrləri daxil edək:

• yeni verilənlər bazası yaradın:

  $CREATE DATABASE testi;

• əsas açar əlavə edərək cədvəl yaradın:

  $ CƏDVƏL YARADIN ölkə(id INT, adı VARCHAR(30), PRIMARY KEY (id));

Ümumiyyətlə, mürəkkəb bir şey yoxdur. Dəyişənlər elan edildikdən sonra aşağıdakı PRIMARY KEY (id) hissəsi əlavə olunur , burada əsas açar olacaq sahənin adı mötərizədə verilir. Və cədvəlin sxeminin necə dəyişdiyini görək: $ DESC ölkəsi; Gördüyünüz kimi, PRI dəyəri id girişi üçün Açar sahəsində göründü .

Cədvəl yaratdıqdan sonra əsas açar

Daha əvvəl dediyim kimi, cədvəl yaratdıqdan sonra ilk açarı ALTER TABLE istifadə edərək təyin etmək olar . Bu nümunəni şəhərlərimizin verilənlər bazasında işlədəcəyik :

• testdən verilənlər bazamıza keçək:

  $USE şəhərləri;

• Mütləq verilənlər bazamızda olduğumuzu yoxlayaq (orada başqa sahə olmalıdır - əhali). Bunu etmək üçün yazırıq:

  $ DESC əhali;

hər şey düzdür, masa bizimdir. Aşağıdakıları yazaq:

$ ALTER TABLE ölkəsi İLKİN AÇAR ƏLAVƏ EDİN (id);

• və dərhal əmrlə yoxlayın:

  $DESC ölkəsi;

Şəkildən göründüyü kimi, hər şey düzgündür, PRI dəyəri tam olaraq olması lazım olan yerdədir. Yeri gəlmişkən, biz test bazası ilə işləmişik. İndi onu silmək lazımdır: niyə serveri qarışdırmaq lazımdır, elə deyilmi? Bunun üçün kifayət qədər tanınmış komandadan istifadə edirik: $ DROP DATABASE testi;

Moldovanı əlavə edir

Əvvəlcə nə yazacağımıza qərar verməliyik. Növbəti ID-miz 4 olacaq. Adı Moldova, əhalisi isə 3550900 olacaq. Buna görə də biz artıq bildiyimiz INSERT INTO əmrini yerinə yetiririk: $ INSERT INTO country VALUES (4, 'Moldova', 3550900); Və biz bu dəyərin tam olaraq verilənlər bazasında olub-olmadığını yoxlayırıq: $ SELECT * FROM country WHERE id = 4; Məlumat sorğusunda hansı sahənin axtarılacağını dərhal müəyyən etdim, buna görə də biz yalnız bir qeyd aldıq, bu da bizə lazım olan şeydir.

Şəhər cədvəlini yaradın

Verilənlər bazası haqqında ilk məqalədəki diaqramdan istifadə edərək , cədvəl haqqında lazımi məlumatları əldə edirik. O, aşağıdakı sahələri ehtiva edəcək:

• id - unikal identifikator;
• ad — şəhər adı;
• ölkə_id — ölkə xarici açarı;
• əhali — şəhər əhalisi.

Hər dəfə unikal şəxsiyyət vəsiqəsi yazmaq bir az streslidir, elə deyilmi? Bunu MySQL səlahiyyətlilərinə həvalə etmək istərdim . Və belə bir yol var - AVTO ARTIRMA . Bunu rəqəmsal sahəyə əlavə etməliyik və əgər biz dəyərləri açıq şəkildə ötürməsək, MySQL özü əvvəlki ilə müqayisədə ID-ni bir artıracaq. Buna görə də cədvəl yaratmaq belə görünəcək: $ CREATE TABLE city ( id INT AUTO_INCREMENT, adı VARCHAR(30), ölkə_id INT, əhali INT, PRIMARY KEY (id)); Hər şeyin düzgün yerinə yetirildiyini görmək üçün cədvəl diaqramına baxaq: $ DESC city; Cədvəl diaqramından göründüyü kimi, id sahəsi üçün yeni təsvirimiz var - auto_increment. Beləliklə, biz hər şeyi düzgün etdik. Tam konfiqurasiya edilmiş cədvəldə məlumatları yoxlayaq. Bunun üçün tapşırığın son hissəsini - xarici açarı edəcəyik.

Şəhərlərə xarici açar əlavə edin

Xarici açar üçün bu əmr olacaq: $ ALTER TABLE city ADD FOREIGN KEY (ölkə_id) REFERANSLAR ölkə(id); Və masanın sxemində nəyin səhv olduğunu dərhal yoxlayaq: bir saat ərzində dəyişdi? $DESC şəhəri;

Bonus hissəsi. Test

Onu tapşırığa əlavə etməyi unutdum - birinci hissənin ekran görüntüsündə olan məlumatları doldurun. Unutdum, indi özüm edəcəm. Maraqlananlar üçün, mənsiz özünüz edə bilərsiniz, sonra yoxlayacağıq;) Xarkov, Kiyev, Minsk, Odessa, Voronej var idi və Kişinyovu da əlavə edəcəyik. Lakin bu dəfə biz şəxsiyyət vəsiqələrini ötürməyəcəyik, onları ötürəcəyik: $ INSERT INTO şəhər (ad, ölkə_id, əhali) DƏYƏRLƏR ('Xarkov', 1, 1443000), ('Kiyev', 1, 3703100), ('Minsk' , 3, 2545500), ('Odessa', 1, 1017699), ('Voronej', 2, 1058261), ('Kişinev', 4, 695400); Gördüyünüz kimi, bir INSERT INTO əmrindən istifadə etməklə eyni vaxtda bir neçə giriş edə bilərsiniz. Faydalı bir şey, yadda saxla) Və dərhal cədvəldə nə olduğuna baxaq: $ SEÇİN * Şəhərdən; AUTO_INCREMENT - tam istədiyimiz kimi işlədi. Təqdim etməməyimizə baxmayaraq, şəxsiyyət sənədləri hamısı doldurulur. Xarici açar asılı bir şeydir. Düzgün işlədiyini yoxlamaq üçün xarici cədvəldə olmayan xarici açarı yazmağa cəhd edə bilərsiniz. Tutaq ki, id = 5-in Qazaxıstan olduğuna qərar verdik. Amma reallıqda ölkələr cədvəlində yoxdur. Və məlumat bazasının and içəcəyini yoxlamaq üçün şəhəri əlavə edin - Astana: $ INSERT INTO city (ad, ölkə_id, əhali) DƏYƏRLƏR ('Astana', 5, 1136156); Və təbii olaraq səhv alırıq: İndi xarici açar məlumat bazamızda olmayan bir ölkəni şəhərə təyin etməyə çalışmamağımızı təmin edir. Ev tapşırığının bu hissəsini tamamlanmış hesab etmək olar - irəli yenisinə :)

SELECT bəyanatı

Yaxşı, artıq hər şey o qədər də qorxulu görünmür, elə deyilmi? Bir daha qeyd etmək istərdim ki, Java proqramçıları üçün verilənlər bazası bilikləri mütləq olmalıdır. Verilənlər bazası olmadan heç yerə gedə bilməzsiniz. Bəli, mən artıq ərizə yazmağa başlamaq istəyirəm, razıyam. Amma lazımdır. Beləliklə, biz bu yolu davam etdirəcəyik. SELECT ifadəsindən istifadə edərək verilənlər bazasından məlumatları əldə edirik. Yəni bu tipik bir DML əməliyyatıdır (siz artıq onun nə olduğunu unutmusunuz?...))) ƏVVƏL məqalələri yenidən oxuyun). Əlaqəli verilənlər bazalarının üstünlükləri nələrdir? Onlar məlumat toplamaq və əldə etmək üçün əla funksionallığa malikdirlər. SELECT ifadəsi bunun üçün istifadə olunur. Deyəsən, bunda mürəkkəb bir şey ola bilməz, elə deyilmi? Ancaq məlum oldu ki, hələ başa düşüləcək çox şey var) Üzərində qura biləcəyimiz əsasları başa düşmək bizim üçün vacibdir. SELECT ifadəsi ilə ən sadə sorğu bütün məlumatları bir cədvəldən seçməkdir. Vikidən operatorların SELECT sorğusunda dəqiq hansı qaydada getməli olduğu haqqında təsviri çox bəyəndim, ona görə də həyasızcasına onu buraya köçürəcəyəm:

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], ...]

Burada siz əvvəlcə GROUP BY operatorunu, sonra isə WHERE operatorunu qoya bilməyəcəyinizi görə bilərsiniz. Bunu yadda saxlamaq lazımdır ki, sonradan haradan qaynaqlandığı bəlli olmayan səhvlərə görə inciklik olmasın. $SEÇ * şəhərdən; Ancaq bütün məlumatları silmək bizim üçün açıq şəkildə əyləncəli deyil. Əgər biz mikroskopla mismar vurmaq istəsək [1] , [2] . Verilənlər bazası süzgəcdən keçirmə, çeşidləmə və toplama əməliyyatlarını Java kodundan çox daha sürətli yerinə yetirdiyi üçün bu məsələni verilənlər bazasına buraxmaq daha yaxşıdır. Buna görə də, vəzifələri çətinləşdirərək, yeni funksionallıq açacağıq.

WHERE parametri

Seçimi süzgəcdən keçirmək üçün WHERE sözündən istifadə olunur . Bunu aşağıdakı kimi şərh etmək lazımdır: SELECT * FROM cədvəlin adı (cədvəl cədvəlinin adından bütün sahələri seçin) HARADA talbe_row = 1 (burada qeydlərdə cədvəl_sətir sahəsi 1-ə bərabərdir). Qeyd etmək lazımdır ki, sorğuda açar sözlərin ardıcıllığı vacibdir. Siz WHERE a =1 FROM table_name SELECT * yaza bilməzsiniz. Rus dili üçün bu yaxşıdır və bəziləri üçün bu, belə bir qarışıqlıq kimi görünməyə bilər, lakin SQL üçün bu qəbuledilməzdir. Aşağıdakı sorğunu yazırıq: $ SELECT * FROM city WHERE country_id = 1; Biz isə Ukrayna şəhərlərini seçdik. Pis deyil, hə? Nəinki ukraynalı, həm də belarus dilini istəsək? Bu məqsədlə sahənin götürə biləcəyi dəyərlər toplusunu sadalaya bilərik: $SELECT * FROM city WHERE country_id IN(1, 3); Artıq cavab olaraq iki ölkənin şəhərləri var. Süzgəcdən keçirmək üçün bir neçə şərt varsa nə etməli? Tutaq ki, əhalisi iki milyondan çox olan şəhərləri istəyirik? Bunu etmək üçün OR və AND sözlərindən istifadə edin : $ SEÇİN * FROM city WHERE country_id IN IN (1, 3) VƏ əhalisi > 2000000; Əla, amma daha bir şərt əlavə etməliyiksə - adları müntəzəm ifadə vasitəsilə axtarmaq (burada müntəzəm ifadələri təsvir etməyəcəyəm: bunu 4 hissədə "qısaca" edən bir adam var )? Məsələn, biz bir şəhəri necə yazmağı xatırlayırıq, amma tam yox... Bunun üçün filtrləmə ifadəsinə LİKE açar sözünü əlavə edə bilərsiniz : $ SEÇİN * FROM city WHERE country_id IN (1, 3) VƏ əhalisi > 2000000 OR “%hark%” kimi ad; Və bu yolla Xarkovu da aldıq. Nəticədə deyə bilərik ki, axtarışlarımız çox yaxşı alındı. Amma şəxsiyyətə görə yox, əhaliyə görə çeşidləmək istərdim, bəs necə? Bəli çox sadə...

SİFARİŞ BY parametr

ORDER BY-dən istifadə edərək, aldığımız qeydləri müəyyən bir sahə üzrə çeşidləyə bilərik. O, həm rəqəmləri, həm də sətirləri sıralayır. Gəlin əvvəlki sorğunu genişləndirək, əhali sayına görə çeşidləyək, ORDER BY BY əlavə edək: $ SEÇİN * ŞƏHƏRDƏN HARADA ölkə_id (1, 3) VƏ Əhali > 2000000 VEYA “%hark%” KİMİ ad SİPARİŞ BY əhali; Gördüyümüz kimi çeşidləmə təbii qaydada, yəni artan qaydada baş vermişdir. Bəs əksini istəsək? Bunun üçün DESC sözünü əlavə etməlisiniz: $ SEÇİN * ŞƏHƏRDƏN OLDUĞU ŞƏHƏRDƏN ölkə_id (1, 3) VƏ əhalisi > 2000000 VEYA “%hark%” KİMİ ad SİPARİŞ BY əhali DESC; İndi çeşidləmə əhalinin azalmasına əsaslanır. Və verilənlər bazası bunu çox tez edir: orada heç bir Collections.sort müqayisə edilə bilməz. İndi sətirlərə görə, ada görə tərs ardıcıllıqla sıralayaq: $ SEÇİN * ŞƏHƏRDƏN OLDUĞU ölkə_id (1, 3) VƏ əhalisi > 2000000 VEYA ad KİMİ “%hark%” Ada görə SİPARİŞ EDİN DESC;

GROUP BY parametr

Qeydləri xüsusi sahələr üzrə qruplaşdırmaq üçün istifadə olunur. Bu adətən məcmu funksiyalardan istifadə etmək üçün lazımdır... Ümumi funksiyalar nələrdir?)) Əgər onlar müxtəlif qeydlər üçün eynidirsə, bəzi sahələr üzrə qruplaşdırmağın mənası var. Nümunəmizdən istifadə edərək bunun nə demək olduğuna baxaq. Tutaq ki, şəhərlərin xarici açarları var - ölkə identifikatorları. Beləliklə, şəxsiyyət eyni ölkənin şəhərləri üçün eynidir. Beləliklə, siz qeydləri onlar tərəfindən götürüb qruplaşdıra bilərsiniz: $ SELECT country_id, COUNT(*) FROM city GROUP BY BY country_id; Amma aqreqasiya funksiyaları olmadan bir az sönük görünür, etiraf etməlisiniz. Buna görə də, ən ümumi funksiyalardan bir neçəsinə baxaq:

• COUNT - qeydlərin sayı, qruplaşdırmadan istifadə edilə bilər, COUNT(*) kimi istifadə olunur . Bəzi sahələr üzrə qruplaşdırma halında - COUNT(qruplaşdırılmış_sahə);
• MAX - konkret sahə üçün maksimum dəyəri tapır;
• MIN - konkret sahə üçün minimum dəyəri tapır;
• SUM - müəyyən bir sahə üçün cəmi tapır;
• AVG - orta dəyəri tapır.

Ümumiyyətlə, bu funksiyalar qruplaşdırmadan istifadə edilə bilər, yalnız bundan sonra yalnız bir sahə göstərilir. Gəlin onları şəhər əhalisimiz üçün sınayaq: $ SEÇİN COUNT(*) FROM şəhər; Onların istədikləri, əldə etdikləri idi. Sadəcə qeydlərin sayı. Bəzən bu faydalıdır. Məsələn, müəyyən bir müəllifin məqalələrinin sayını öyrənmək lazımdırsa. Onları verilənlər bazasından çıxarıb saymağa ehtiyac yoxdur. Siz sadəcə COUNT() funksiyasından istifadə edə bilərsiniz. $ ŞƏHƏRDƏN AVG(əhali) SEÇİN; $ ŞƏHƏRDƏN MIN (əhali) SEÇİN; Və burada qruplaşma qüvvəyə minir. Məsələn, vəzifə ölkənin ən kiçik şəhərini almaqdır. Bunu necə edəcəyinizi artıq bilirsiniz? Özünüz cəhd edin, sonra baxın: $ SELECT country_id-i Ölkə kimi, MIN(əhali) ŞƏHƏRDƏN QRUP GROUP BY country_id; Hələlik biz yalnız ölkənin şəxsiyyət nömrəsini görürük, amma fərqi yoxdur - gələn dəfə hər şeyi edəcəyik. Beləliklə, artıq bir nəticə var və biz istədiyimizi əldə etdik - ID = 1 olan ölkənin ən kiçik şəhəri. Qalan funksiyalar eyni olacaq. Qeyd etmək vacibdir ki, qruplaşdırma və aqreqasiyadan istifadə edərkən bütün sahələri * vasitəsilə çıxarmaq işləməyəcək! Fikir verin ;)

Ev tapşırığı

Əvvəlki yazıların nəticələrinə əsasən aydın olur ki, ev tapşırığı edilir, ona görə də davam edək)) Bəli, ev tapşırığını edən hər kəs şərhlərdə “+” qoymağa davam edəcək. Ev tapşırığı mövzusunun sizin üçün maraqlı olması mənim üçün vacibdir ki, gələcəkdə də bunu davam etdirim. Bəli, mütəmadi olaraq şərhlərinizi oxuyuram. Təbii ki, daha az cavab verirəm. Gördüm ki, daha çətin SQL məsələlərini vermək istədilər. Qoşulmağı öyrənənə qədər heç bir maraqlı problem olmayacaq, ona görə də əlavə material üçün lazım olanlar olacaq.

Tapşırıqlar:

HAVING operatorunu anlayın və nümunəmizdən cədvəllər üçün nümunə sorğu yazın. Daha aydın etmək üçün bəzi sahələr və ya daha çox dəyər əlavə etmək lazımdırsa, onları əlavə edin. Kim istəsə, öz nümunə həllinizi şərhlərdə yazın: bu yolla mən də vaxtım olsa yoxlaya bilərəm.
1. UI vasitəsilə verilənlər bazası ilə işləmək üçün MySQL Workbench quraşdırın. Düşünürəm ki, biz artıq konsoldan işləmək üçün kifayət qədər təcrübə əldə etmişik. Verilənlər bazasına qoşulun. Əgər verilənlər bazası ilə işləmək üçün başqa bir şey istifadə edirsinizsə, bu tapşırığı atlayın. Burada və bundan sonra yalnız MySQL Workbench istifadə edəcəyəm.
2. Məlumatlarımızdan istifadə edərək qəbul etmək üçün sorğular yazın:
   1. ən kiçik/ən çox əhalisi olan ölkə;
   2. ölkədə əhalinin orta sayı;
   3. adları “a” ilə bitən ölkələrdə əhalinin orta sayı;
   4. əhalisi dörd milyondan çox olan ölkələrin sayı;
   5. ölkələri əhalinin sayının azalmasına görə çeşidləyin;
   6. ölkələri təbii qaydada adlarına görə sıralayın.

Nəticə

Bu gün biz keçən dərsdən ev tapşırığını ətraflı müzakirə etdik. Üstəlik, bunu həm etməyənlər, həm də edənlər üçün vacib hesab edirəm. Birincilər üçün bu, cavabı tapmaq, ikincilər üçün isə nəticə ilə müqayisə etmək imkanıdır. Layihədəki dəyişikliklərdən xəbərdar olmaq üçün GitHub hesabıma abunə olun . Mən bütün kod bazasını orada saxlayacağam. Bu təşkilatda hər şey olacaq . Sonra SELECT bəyanatını müzakirə etdik. O, bizim üçün ən önəmlidir. Məlumat üçün bütün sorğular onun vasitəsilə keçəcək və biz bunu başa düşməliyik. Ən əsası, parametrlərin əlavə olunma qaydasını xatırlamaqdır (HARADA, SİPARİŞ BY, QRUP BY və s.). Bəli, mümkün olan hər şeyi demədim, amma özümə belə bir məqsəd qoymadım. Bəli, bilirəm ki, siz artıq ərizə yazmağa həvəslisiniz. Səbirli olun, sizə lazım olan tək şey budur. Həm layihə, həm də peşəkar inkişafınız üçün. Gözləyərkən Git-in sizə artıq tanış olduğundan əmin olun. Mən onu tanınmış alət kimi standart olaraq istifadə edəcəyəm. Oxuduğunuz üçün hər kəsə təşəkkür edirəm. Növbəti məqalədə verilənlər bazası bağlantıları və birləşmələri haqqında danışacağıq. Gözəl tapşırıqlar burada olacaq))

Serialdakı bütün materialların siyahısı bu məqalənin əvvəlindədir.