Maglumatlar bazalaryny we SQL dilini seljerýäris. (6-njy bölüm - Iň soňky tabşyrygy barlamak) - "A-dan Z-a çenli Java taslamasy"

Java taslamasyny döretmek baradaky seriýadan bir makala (beýleki materiallara baglanyşyk ahyrynda). Maksady esasy tehnologiýalary seljermek, netijesi telegramma bot ýazmak. Bu bölümde maglumatlar bazasyndaky iň soňky meseläniň derňewi bar. Salam, gadyrly okyjylar. Bu gün meseläni maglumatlar bazasyndaky soňky makaladan seljereris. Gyzykly, sebäbi orta derejedäki söhbetdeşlikler üçin niýetlenendir. Thisagny, bu ýumuşdan soň eýýäm söhbetdeşlik üçin gidip bilersiňiz we baglanyşyk bazalaryna degişli zatlaryň azyndan bir bölegini üstünlikli geçirersiňiz. Bu makalanyň näderejede zerur bolup biljekdigini bilýärin, şonuň üçin ähli tejribämi peýdaly we gyzykly etmek üçin ulanaryn. Makalanyň ortasynda uklamasaňyz, bu meniň maksadymy ýerine ýetirendigimi aňladýar. Taskhli meseläni gaýtalamaryn: Her meseläni ýerine ýetirmezden ozal sitata bilen aýdaryn. Bu makalany okanlaryň hemmesiniň maglumatlar bazasyndaky ähli soraglary işledip, şol bir zady almagyna garaşýaryn. Bu iş üçin iň ýokary girdeji getirer. We kyn işimizde birine kömek etdim diýen pikirden birneme bagtly bolaryn)

1-nji maşk

Aşakdaky meýdanlar bilen 'Talyp' tablisasyny döretmek üçin SQL skriptini ýazyň: id (esasy açar), ady, familiýasy, e_mail (üýtgeşik). Muny eýýäm etdik, şonuň üçin hiç hili problema bolmaz. Scriptazgyda esasy açary we esasy açaryndan tapawutlanýan özboluşly meýdany görkezmeli. Ilki bilen, bu mesele üçin täze maglumat bazasyny döredeliň: $ DATABASE CREATE final_task; Geliň, bu maglumat bazasyny ulanalyň: $ USE final_task; Daşky gurşaw gurlup, işe başlamaga taýyn bolansoň, aşakdaky skripti ýazyp bileris: $ TABLE okuwçysyny dörediň (id INT AUTO_INCREMENT, ady VARCHAR (40), familiýa VARCHAR (50), VARCHAR (100) e-poçta, PRIMARY Açar ( id), UNIQUE (e-poçta)); Şu wagta çenli başdan geçirenlerimiz bilen deňeşdireniňde täze zat ýok. Islendik teswir gerek däl, dowam edeliň.

2-3-nji wezipe

Aşakdaky meýdanlar bilen 'Kitap' tablisasyny döretmek üçin SQL skriptini ýazyň: id, title (id + title = esasy açar). 'Talyp' we 'Kitap' bilen 'Talyp' bir-birden 'Kitap' gatnaşyklary bilen baglanyşdyryň. Has çalt we amatly etmek üçin iki meseläni bir ýere jemläliň. Öňki makalalarda nädip daşary ýurt açaryny goşmalydygyny ara alyp maslahatlaşdym. Goşmak üçin nädip baglanyşyk gurýandygymyzy we nämäniň üsti bilen ýadymyzdan çykarmaly däldiris. Öňki makala size kömek eder, soň bolsa ssenariýa: $ CREATE TABLE kitaby (id INT, ady VARCHAR (100), talyp_id INT DEFAULT NULL, PRIMARY Açar (id, ady), Daşary ýurt açary (talyp_id) REFERENCES okuwçysy (id) )); Bu ýönekeý usul bilen, PRIMARY KEY (id, ady) stolumyz üçin birleşdirilen açar goşduk , indi açar jübüt bolar. Bu, tablisada birden köp meňzeş ID meýdan bahasynyň bolup biljekdigini aňladýar. Ady üçin edil şol bir zat.

4-nji wezipe

Aşakdaky meýdanlar bilen 'Mugallym' tablisasyny döretmek üçin SQL skriptini ýazyň: id (esasy açar), ady, familiýasy, e_mail (üýtgeşik), mowzuk. Maglumatlar bazamyzy soraglar üçin taýýarlamagy dowam etdirýäris, mugallymlar tablisasyny döredýäris: $ TABLE mugallymy dörediň (ID INT AUTO_INCREMENT, ady VARCHAR (30), familiýa VARCHAR (30), VARCHAR (100) e-poçta, VARCHAR (40), PRIMARY KEY ( id), UNIQUE (e-poçta)); Şu wagta çenli kyn däl, şeýlemi? Üç mesele eýýäm gutardy!

5-nji wezipe

'Talyp' we 'Mugallym' bilen 'Talyp' köp mugallym gatnaşyklaryny baglanyşdyryň. Indi bu has gyzykly! Bu barada soňky gezek gürleşdik. Muňa ýetmek üçin nämäniň zerurdygyny ýatladýaryn: okuwçy-mugallym jübütlerini saklaýan aralyk tablisa döretmeli. Onuň kömegi bilen köp adam gatnaşyklaryny döredip bolar. Şonuň üçin geliň, talyplar_x_techers tablisasyny döredeliň . At dakmak çemeleşmesi açyk we şeýle hem bolup biler: talyp_ mugallym . $ Jetwel dörediň talyplar_x_ mugallymlar (talyp_id INT DÜZGÜN däl, mugallym_id INT NULL däl, başlangyç açar (talyp_id, mugallym_id), daşary ýurt açary (talyp_id) salgylanma okuwçy (id), daşary ýurt açary (mugallym_id) REFERENCES mugallymy (id); Görşüňiz ýaly, hemme zat aýdyň we yzygiderli ýerine ýetirilýär. Iki sany daşary ýurt açary üçin birleşdirilen açarymyz bar: talyp_id we mugallym_id. Näme üçin daşary ýurt açary? Jübütleriň ýazylmagy üçin okuwçy we mugallym tablisasyndaky ýazgylaryň bardygyna göz ýetirmek üçin.

6-njy wezipe

Familiýasynda 'oro' bolan 'Talyp' saýlaň, mysal üçin 'Sidorow', 'Woronowskiý'. Biziň üçin gyzykly we görnükli etmek üçin ilki bilen birnäçe okuwçyny goşmagy maslahat berýärin, olaryň käbiri bu haýyşa laýyk, käbiri bolsa laýyk däl. Şonuň üçin, haýyşyň netijesinde goşulmalylary ýazalyň: $ INSERT INTO talyp (ady, familiýasy, e-poçta) GYMMATLAR ('Iwan', 'Sidorow', 'ivan.sidorov@gmail.com'); Talyplara INSERT (ady, familiýasy, e-poçta) GYMMATLAR ('Nikolaý', 'Woronowskiý', 'nikolay.voronovsky@gmail.com'); Girmeli däller: $ INSERT INTO talyp (ady, familiýasy, e-poçta) GYMMATLAR ('Roman', 'Kyrk', 'roman.fortny@gmail.com'); Talybyň içine giriň (ady, familiýasy, e-poçta) GYMMATLAR ('Kostýa', 'Petrow', 'kostya.petrov@gmail.com'); Netijäni barlap göreliň, talyplar tablisasyndaky maglumatlaryň sanawyna seredeliň: $ SELECT * Talypdan; we alýarys: Jemi dört ýazgy bar, ikisi gabat gelmeli, ikisi laýyk däl. Talap üçin ähli maglumatlary taýýarlap, meseläniň özi üçin haýyş edip bileris: $ SELECT * Talypdan WHERE last_name '% oro%' ýaly; Netijede, Iwan we Nikolaý sanawdan geçdiler.

7-nji wezipe

Indiki mesele, biz okaýarys: 'Talyp' tablisasyndan ähli familiýalary ('familiýa') we gaýtalanýan sanyny saýlaň. Maglumat bazasynda atlaryň bardygyny göz öňünde tutuň. Aşakdaky tertipde mukdar boýunça tertipläň. Bu şeýle bolmaly:

familiýa	mukdary
Petrow	15
Iwanow	12
Sidorow	3

Has düşnükli bolmak üçin has köp maglumat goşmaly. Goşmaça sözlemiz, garyndaşlygyny bilmeýän Petrowlary, Iwanowlary we Sidorowlary goşalyň;) E-poçta salgysyny oýlap tapmaýaryn, diňe täze ýazgylardan aýyraryn. Aşakdaky buýrugy 12 gezek işledeliň: $ INSERT INTO talyp (ady, familiýasy) GYMMATLAR ('Iwan', 'Iwanow'); 15 sany Petrow goşalyň: $ INSERT INTO talyp (ady, familiýasy) GYMMATLAR ('Petr', 'Petrow'); Iki sany Sidorow (bizde eýýäm bar)): $ INSERT INTO talyp (ady, familiýasy) GYMMATLAR ('Sidor', 'Sidorow'); Maglumatlar taýýar. Şeýle maglumatlary almak üçin toparlaşdyrmaly, toparlaşdyrmak üçin “Group By” operatory ulanmaly we muny “familiýa” meýdançasynda etmeli. Gaýtalamalaryň sanynyň mukdar hökmünde kesgitlenendigini hem görüp bilersiňiz , bu ýerde SQL-de lakamlary nädip ýasamalydygyny hem ýatdan çykarmaly dälsiňiz: $ SELECT last_name, COUNT (*) talyplar toparyndan FROM BY COUNT (*) DESC ; Şonuň üçin men ony Petrowlar bilen artykmaç etdim - 16 boldy))

8-nji wezipe

: Agdaýy: 'Talyp' -dan iň köp gaýtalanýan 3 aty saýlaň. Aşakdaky tertipde mukdar boýunça tertipläň. Bu şeýle bolmaly:

ady	mukdary
Aleksandr	27
Sergeý	10
Peter	7

Aý, bu maksat bilen eýýäm Iwanlar, Piters we Sidors bar. Şonuň üçin olary goşmagyň zerurlygy ýok. Nädip sortlamalydygyny eýýäm bilýäris. Bu gün gürleşmedik ýekeje zadymyz, belli bir mukdarda ýazgyny nädip saýlamaly. Maglumat bazasynyň meseleleriniň öňki çözgütlerinde eýýäm peýda boldy. Okamadyklar üçin okaň. Galan zatlar üçin gönüden-göni meselä geleliň: $ SELECT ady, COUNT (*) mukdar hökmünde talyplar toparyndan COUNT (*) DESC LIMIT 3; Talapdan görnüşi ýaly, SELECT talapynda operatorlaryň tertibini bilýän bolsaňyz, şeýle soragy ýerine ýetirmekde kynçylyk bolmaz. Bu wezipe henizem bize bagly. Öň berlen bilimler bu meseläni çözmek üçin ýeterlikdir.

9-njy wezipe

Wezipe şerti: Iň köp 'Kitap' we baglanyşykly 'Mugallym' bolan 'Talyp' saýlaň. Aşakdaky tertipde mukdar boýunça tertipläň. Bu şeýle bolmaly:

Mugallymyň familiýasy	Talybyň familiýasy	Kitabyň mukdary
Petrow	Sidorow	7
Iwanow	Smith	5
Petrow	Kankava	2

Şeýlelik bilen, bu wezipe öňküsinden has kyn, şeýlemi? Geň däl: goşulmak ýaly ys gelýär ... we birden köp) Ilki bilen näme etmelidigimize düşünmeli ... Kitabyň mukdarynyň toparlanmagy talap edýändigi düşnüklidir. Emma näme? Näme üçin olary toparlamaly? Talap üç tablisany, toparlaşdyrmagy we tertipleşdirmegi öz içine alýar. Kitap ýok ýerlerde ýazgylaryň görkezilmeýändigine seretseň, INNER JOIN-i almaly diýmekdir. Şeýle hem bu meselede hiç hili problema bolmazlygy üçin Çep goşulmak üçin haýyş ederis. Birnäçe wariant bar. Ilki bilen edýän işimiz, üç tablisany bir ýazga goşmak. Soň bolsa, talyplar boýunça toparlaýarys we oňa mugallymyň adyny goşýarys. Biz näme saýlarys? Mugallymyň ady, okuwçy we kitap sany. Haýyş üçin maglumatlary goşalyň:

• üç mugallym;
• on kitap;
• iki okuwçyny üç mugallym bilen baglanyşdyryň.

Üç mugallym

Mugallymyň içine giriň (familiýa) GYMMATLAR ('Matwienko'); Mugallymyň içine $ INSERT (familiýa) GYMMATLAR ('Şewçenko'); Mugallymyň içine giriň (familiýa) GYMMATLAR ('Vasilenko');

10 kitap

1-nji we 2-nji okuwçylaryň şahsyýetnamalaryny alaryn. Olara kitap dakaryn. AUTO_INCREMENT gurulmandygy sebäpli, her gezek täze şahsyýet ýazmazlyk üçin aşakdakylary etmeli: $ ALTER TABLE kitaby MODIFY id INT NULL AUTO_INCREMENT; Ondan soň, birinji okuwçy üçin kitap goşuň: $ INSERT INTO kitaby (ady, talyp_id) GYMMATLAR ('book1', 1); $ Kitaba giriň (ady, okuwçy_id) GYMMATLAR ('kitap2', 1); $ Kitaba giriň (ady, okuwçy_id) GYMMATLAR ('kitap3', 1); $ Kitaba giriň (ady, okuwçy_id) GYMMATLAR ('kitap4', 1); $ Kitaba giriň (ady, okuwçy_id) GYMMATLAR ('kitap5', 1); $ INSERT INTO kitaba (ady, talyp_id) GYMMATLAR ('kitap6', 1); Ikinji okuwçy üçin kitaplar: $ INSERT INTO kitaby (ady, talyp_id) GYMMATLAR ('book7', 2); $ Kitaba giriň (ady, okuwçy_id) GYMMATLAR ('kitap8', 2); $ Kitaba giriň (ady, okuwçy_id) GYMMATLAR ('book9', 2); $ Kitaba giriň (ady, okuwçy_id) GYMMATLAR ('kitap10', 2);

Mugallym-okuwçy baglanyşyklary

Munuň üçin okuwçylara_x_ mugallymlary goşuň: $ INSERT INTO talyplar_x_ mugallymlaryň bahalary (1,1); Talyplara_x_ mugallymlaryň GYMMATLARYNA $ INSERT (1,2); Talyplara_x_ mugallymlaryň GYMMATLARYNY $ INSERT (2,3);

Geliň, haýyşy durmuşa geçireliň

Birinji etap edýäris - üç tablisany bir ýazga baglanyşdyrýarys: $ SELECT * Mugallymdan INNER JOIN okuwçylaryndan_x_ mugallymlar st_x_tch ON tch.id = st_x_tch.teacher_id INNER JOIN talyp st st_x_tch.student_id = st.id INNER JOIN kitaby b ON st. .id = b.student_id; Elbetde, entek ýazgylarymyz ýok, ýöne üç tablisany üstünlikli birleşdirendigimizi görüp bileris. Indi kitap toparlara bölmek, tertiplemek we zerur meýdanlary goşýarys: $ SELECT tch.last_name, st.last_name, st.id, COUNT (*) JOIN okuwçylary_x_ mugallymlar st_x_tch ON st.id = st_x_tch.student_id INNER JOIN mugallym tch ON tch.id = st_x_tch.teacher_id GROUP tarapyndan st.id buýrugy DESC kitaplary; Söne SQL-de ýalňyşlyk alýarys we aşakdaky jogap: roralňyş kody: 1055. SELECT sanawynyň 1-nji beýany GROUP BY bölüminde ýok we GROUP BY sütünlerine işlemeýän sütünlere jemlenmedik 'final_task.tch.last_name' sütünini öz içine alýar. madda Bu elementleri almaýar, sebäbi mugallym bilen okuwçynyň arasynda köp-köp gatnaşyk bar. Bu hakykat: her okuwça diňe bir mugallym alyp bilmeris. Geliň, başga tarapa gideliň. View SQL atly bir zady ulanalyň. Pikir näme: täze bir tablisa bolan aýratyn görnüşi döredýäris, eýýäm zerur toparlara bölünýäris. Bu tablisa mugallymlaryň zerur atlaryny goşarys. Oneöne birden köp mugallymyň bolup biljekdigini göz öňünde tutýarys, şonuň üçin ýazgylar gaýtalanar. Görnüşi dörediň: $ DÖRTMEK Talyplar kitaplaryny SELECT st.last_name, st.id, COUNT (*) kitaplary hökmünde talyplaryň INNER JOIN kitabyndan b ON st.id = b.student_id GROUP DESC kitaplary tarapyndan sargyt; Ondan soň, bu görnüşi bilen üç ugry bolan ýönekeý tablisa hökmünde işleýäris: okuwçy familiýasy, talyp_id we kitap sanamak. Talybyň şahsyýetnamasyna görä, iki goşmaçanyň üsti bilen mugallym goşup bileris: $ SELECT tch.last_name 'Mugallym', sbw.last_name 'Talyp', sbw.books 'sbw INNER JOIN talyplar_x_ mugallymlar stb ON ON sbw. id = stch.student_id INNER JOIN mugallymy tch ON tch.id = stch.teacher_id; Indi bolsa netije bolar: Huh! Bu haýyş, şeýlemi?) Garaşylyşy ýaly ýüze çykdy: id = 1 okuwçynyň alty kitaby we iki mugallymy, id = 2 okuwçynyň dört kitaby we bir mugallymy bar.

10-njy wezipe

: Agdaýy: 'hli' Talyplarynyň 'arasynda iň köp' Kitap 'bolan' Mugallymy 'saýlaň. Aşakdaky tertipde mukdar boýunça tertipläň. Bu şeýle bolmaly:

Mugallymyň familiýasy	Kitabyň mukdary
Petrow	9
Iwanow	5

Bu ýerde öňki ýumuşdan taýýar haýyşy ulanyp bileris. Bu hakda näme üýtgetmeli? Bizde eýýäm bu maglumatlar bar, başga bir topar goşmaly we çykyş maglumatlaryndan okuwçynyň adyny aýyrmaly. Firstöne ilki bilen netijäni has gyzykly etmek üçin mugallyma ýene bir okuwçy goşalyň. Munuň üçin ýazýarys: $ INSERT INTO talyplara_x_ mugallymlaryň GYMMATLARY (2, 1); Gözlegiň özi: $ SELECT tch.last_name 'Mugallym', SUM (sbw.books) talyplar kitabyndan sbw INNER JOIN talyplar_x_ mugallymlar stb ON sbw.id = stch.student_id INNER JOIN mugallymy tch.id = stch mugallym_id GROUP BY tch.id; Netijede, alýarys: mugallym Wasilenkonyň 10 kitaby, Şewçenkonyň 6 kitaby bar ...)

11-nji wezipe

: Agdaýy: “Talyplary” üçin 7-den 11-e çenli bolan “Mugallym” saýlaň. Aşakdaky tertipde mukdar boýunça tertipläň. Bu şeýle bolmaly:

Mugallymyň familiýasy	Kitabyň mukdary
Petrow	on bir
Sidorow	9
Iwanow	7

Bu ýerde HAVING ulanarys. Biz ol hakda gürleşdik. Isleg öňküsi ýaly bolar, diňe kitaplaryň sanynyň belli bir aralykda bolmagy şertini goşmaly. Öňki makalalarda aýdyşym ýaly, toparlara bölmek we / ýa-da jemlemek funksiýalarynda süzgüç etmeli bolanymyzda, HAVING : $ SELECT tch.last_name 'Mugallym', SUM (sbw.books) -dan 'Kitaplar' hökmünde ulanmalydyrys. talyplar kitaby sbw INNER JOIN talyplar_x_ mugallymlar stch ON sbw.id = stch.student_id INNER JOIN mugallym tch ON tch.id = stch.teacher_id GROUP BY tch.id HAVING SUM (sbw.books)> 6 we SUM (sbw.books) <12; Goşan bölümimi belledim. Aslynda garaşylýan netije: Diňe Wasilenko bu tapgyrdan geçdi))

12-nji wezipe

: Agdaýy: 'hli' Mugallym 'we' Talyp 'meýdan' görnüşi (okuwçy ýa-da mugallym) bilen 'familiýa' we 'adyny' çap ediň. 'Last_name' boýunça elipbiý boýunça tertipläň. Bu şeýle bolmaly:

familiýa	görnüşi
Iwanow	okuwçy
Kankava	mugallym
Smith	okuwçy
Sidorow	mugallym
Petrow	mugallym

Twoagny, iki netijäni birleşdirmeli, bu bolsa UNION üçin niýetlenen zat. Başga sözler bilen aýdanymyzda, okuwçylardan we mugallymlardan ýazgy alarys we bilelikde çap ederis: $ SELECT familiýa, mugallym UNION-dan görnüşi hökmünde 'mugallym' HEMMESI familiýa, 'talyp' okuwçynyň görnüşi ýaly familiýa; Mugallymlaram, okuwçylaram bolar. Hemme zat ýönekeý ýaly bolup görünýär, ýöne bu eýýäm netijä seredenimizde. Şonuň üçin iki zady çaklamaly.

13-nji wezipe

: Agdaýy: Bar bolan 'Talyp' tablisasyna 'baha' sütünini goşuň, bu okuwçynyň häzirki kursyny saklar (san bahasy 1-den 6-a çenli). HEMMESI JEMLE studentJI TERJIME EDIP BOLANOK Bu ýerde ALTER TABLE we 1-den 6-a çenli çäk bellemek üçin bir meýdan goşýarys.

14-nji wezipe

Itionagdaý: Bu element hökman däl, ýöne goşmaça bolar. 'Hli' Kitaplardan 'geçjek we dykgat bilen bölünen ähli' atlary 'çykarjak bir funksiýa ýazyň. Bu ýerde diňe kitaplaryň atlaryny öz içine alýan haýyşyň netijesinde bir setir gaýtarmaly. Ynha, ýene google-a girmeli boldum. Şeýle bir funksiýa bar - GROUP_CONCAT , bu gaty ýönekeý ýerine ýetirilýär: $ SELECT GROUP_CONCAT (ady) kitapdan; Ine ...)) 14 meseläniň hemmesi taýýar.

Netijeler

Uuhhh ... aňsat däldi. Gyzykly boldy. Wezipeler oňa mynasyp boldy, men has ynamly. Bu meseleleri ýerine ýetirip otyrkam, öň belli bolmadyk köp zatlary geçdik:

• SQL VIEW
• GROUP_CONCAT
• Bileleşik

we ş.m. Eden zatlarymy okamaga we gaýtalamaga güýji bolan hemmelere sag bolsun aýdýaryn. Islegleri has gowulaşdyrmagy kim bilýär - teswirlerde ýazyň, hökman okaryn)

Tapgyryň ähli materiallarynyň sanawy bu makalanyň başynda.