Sinusuri namin ang mga database at ang wikang SQL. (Bahagi 3) - "Proyekto ng Java mula A hanggang Z"

Isang artikulo mula sa isang serye tungkol sa paglikha ng isang proyekto ng Java (nasa dulo ang mga link sa iba pang mga materyales). Ang layunin nito ay pag-aralan ang mga pangunahing teknolohiya, ang resulta ay pagsulat ng isang telegram bot.

• Sinusuri ang takdang-aralin
  • Pagdaragdag ng pangunahing susi
  • Pagdaragdag ng Moldova
  • Lumikha ng talahanayan ng mga lungsod
  • Magdagdag ng foreign key sa mga lungsod
  • Bahagi ng bonus. Pagsubok
• PUMILI ng pahayag
  • SAAN parameter
  • ORDER BY parameter
  • GROUP BY parameter
• Takdang aralin
• Konklusyon

Kumusta, mga binibini at mga ginoo, magpatuloy tayo sa pag-uusap tungkol sa mga database, SQL at iba pang mga bagay. Ang materyal ngayon ay maglalaman ng bahaging teorya at bahaging pagsasanay. Hayaan mong ipaalala ko sa iyo na noong huling beses na napag-usapan natin kung paano i-set up ang lahat, kung paano lumikha ng isang database, isang talahanayan at kumuha ng data mula dito. Oras na para makita kung may nangyari sa remote sensing. Sa aking palagay, kalahati nito ay maaaring gawin lamang batay sa naunang artikulo. Ito ay lumabas na upang maayos na mag-ipon ng isang application at gawing mas maganda o mas maganda ang lahat, kailangan mong pag-usapan ang tungkol sa mga database, at upang pag-usapan ang mga ito kailangan mong gumastos ng maraming oras.

Sinusuri ang takdang-aralin

Malaking paggalang sa lahat ng matagumpay na natapos ang mga gawain. Nangangahulugan ito na nauunawaan mo na ikaw lang ang nangangailangan nito at nakakatulong lamang ito sa iyo. Para sa mga nagpabaya sa aking gawain, hayaan ninyong ipaalala ko sa inyo ang kondisyon:

1. Kailangan mong magdagdag ng primary key (PRIMARY KEY) mula sa ID field sa country table schema.
2. Magdagdag ng isa pang bansa sa talahanayan ng bansa - Moldova.
3. Ayon sa pamamaraan ng nakaraang artikulo, lumikha ng isang talahanayan ng lungsod, na maglalaman ng lahat ng mga patlang na inilarawan. Ang mga pangalan ng field ay ang mga sumusunod: id, name, country_id, population.
4. Magdagdag ng pangunahing susi sa talahanayan ng lungsod.
5. Magdagdag ng foreign key sa talahanayan ng lungsod.

Upang makapagsimula, gamitin natin ang unang bahagi ng nakaraang artikulo at pumunta sa terminal ng database.

Pagdaragdag ng pangunahing susi

Maaari kang magdagdag ng pangunahing key (PRIMARY KEY) sa dalawang paraan: kaagad kapag gumagawa ng talahanayan, o pagkatapos ng paggawa, gamit ang ALTER TABLE.

Pangunahing susi sa paggawa ng talahanayan

Dahil nakagawa na kami ng table, at nang hindi tinatanggal ito ay hindi namin maipapakita ang diskarteng ito sa loob ng database na ito, gagawa lang kami ng pansamantalang database ng pagsubok kung saan gagawin namin ang lahat. Ipasok natin ang sumusunod na mga utos:

• lumikha ng bagong database:

  pagsubok ng $CREATE DATABASE;

• lumikha ng isang talahanayan na may pagdaragdag ng pangunahing susi:

  $ GUMAWA NG TABLE bansa(id INT, pangalan VARCHAR(30), PRIMARY KEY (id));

Sa pangkalahatan, walang kumplikado. Pagkatapos ideklara ang mga variable, idaragdag ang sumusunod na bahagi PRIMARY KEY (id) , kung saan ang pangalan ng field na magiging pangunahing key ay ipinapasa sa panaklong. At tingnan natin kung paano nagbago ang schema ng talahanayan: $ DESC country; Gaya ng nakikita mo, ang halagang PRI ay lumitaw sa Key field para sa id entry .

Pangunahing susi pagkatapos ng paggawa ng talahanayan

Tulad ng sinabi ko kanina, ang unang susi pagkatapos lumikha ng isang talahanayan ay maaaring italaga gamit ang ALTER TABLE . Tatakbuhin namin ang halimbawang ito sa database ng aming mga lungsod :

• pumunta tayo sa aming database mula sa pagsubok:

  $USE lungsod;

• Suriin natin na siguradong nasa database tayo (dapat may ibang larangan doon - populasyon). Upang gawin ito sumulat kami:

  $ DESC populasyon;

lahat ay tama, ang mesa ay sa amin. Isulat natin ang sumusunod:

$ ALTER TABLE bansa ADD PRIMARY KEY (id);

• at suriin ito kaagad gamit ang utos:

  $DESC bansa;

Tulad ng nakikita mo mula sa larawan, lahat ay tama, ang halaga ng PRI ay eksakto kung saan ito dapat. Sa pamamagitan ng paraan, nagtrabaho kami sa isang database ng pagsubok. Ngayon kailangan nating tanggalin ito: bakit kailangan nating kalat ang server, di ba? Upang gawin ito, gumagamit kami ng medyo kilalang utos: $ DROP DATABASE test;

Pagdaragdag ng Moldova

Una kailangan naming magpasya kung ano ang aming itatala. Ang aming susunod na ID ay magiging 4. Ang pangalan ay Moldova, at ang populasyon nito ay 3550900. Samakatuwid, isinasagawa namin ang INSERT INTO command na alam na namin: $ INSERT INTO country VALUES (4, 'Moldova', 3550900); At sinusuri namin kung ang halagang ito ay eksaktong nasa database: $ SELECT * FROM country WHERE id = 4; Sa data request, agad kong natukoy kung aling field ang hahanapin, kaya isang record lang ang nakuha namin, iyon ang kailangan namin.

Lumikha ng talahanayan ng mga lungsod

Gamit ang diagram mula sa unang artikulo tungkol sa database, nakukuha namin ang kinakailangang impormasyon tungkol sa talahanayan. Maglalaman ito ng mga sumusunod na field:

• id — natatanging identifier;
• pangalan - pangalan ng lungsod;
• country_id — country foreign key;
• populasyon - populasyon ng lungsod.

Medyo nakaka-stress ang pagsulat ng kakaibang ID sa bawat pagkakataon, hindi ba? Gusto kong ipaubaya ito sa mga awtoridad ng MySQL . At may ganoong paraan - AUTO INCREMENT . Kailangan nating idagdag ito sa digital field, at kung hindi natin tahasang ipasa ang mga value, ang MySQL mismo ay tataas ang ID ng isa kumpara sa nauna. Samakatuwid, ang paggawa ng talahanayan ay magiging ganito: $ CREATE TABLE city ( id INT AUTO_INCREMENT, pangalan VARCHAR(30), country_id INT, population INT, PRIMARY KEY (id)); Tingnan natin ang table diagram upang makita kung ang lahat ay ginawa nang tama: $ DESC city; Gaya ng nakikita mo mula sa diagram ng talahanayan, mayroon kaming bagong paglalarawan para sa field ng id - auto_increment. Kaya ginawa namin ang lahat ng tama. Suriin natin ang data sa isang ganap na naka-configure na talahanayan. Upang gawin ito, gagawin namin ang huling bahagi ng gawain - ang foreign key.

Magdagdag ng foreign key sa mga lungsod

Para sa foreign key magkakaroon ng ganitong command: $ ALTER TABLE city ADD FOREIGN KEY (country_id) REFERENCES country(id); At agad nating suriin kung ano ang mali sa schema ng talahanayan: nagbago ba ito sa loob ng isang oras? $DESC lungsod;

Bahagi ng bonus. Pagsubok

Nakalimutan kong idagdag ito sa gawain - punan ang data na nasa screenshot ng unang bahagi. Nakalimutan ko, kaya ako na mismo ang gagawa. At sa mga interesado, you can do it yourself without me and then we'll check ;) There were Kharkov, Kyiv, Minsk, Odessa, Voronezh, and we'll also add Chisinau. Ngunit sa pagkakataong ito ay hindi na kami magpapadala ng mga ID, laktawan namin ang mga ito: $ INSERT INTO city (name, country_id, population) VALUES ('Kharkov', 1, 1443000), ('Kyiv', 1, 3703100), ('Minsk' , 3, 2545500), ('Odessa', 1, 1017699), ('Voronezh', 2, 1058261), ('Kishinev', 4, 695400); Tulad ng nakikita mo, maaari kang gumawa ng ilang mga entry nang sabay-sabay gamit ang isang INSERT INTO command. Isang madaling gamiting bagay, tandaan) At agad nating tingnan kung ano ang nasa talahanayan: $ SELECT * FROM city; AUTO_INCREMENT - gumana nang eksakto tulad ng gusto namin. Ang mga file ng ID ay napunan lahat, kahit na hindi namin naisumite ang mga ito. Ang isang dayuhang susi ay isang bagay na umaasa. Upang suriin kung ito ay gumagana nang tama, maaari mong subukang magsulat ng isang dayuhang key na hindi umiiral sa dayuhang talahanayan. Sabihin nating napagpasyahan namin na ang id = 5 ay Kazakhstan. Ngunit sa katotohanan ay wala ito sa talahanayan ng mga bansa. At para matiyak na susumpa ang database, idagdag ang lungsod - Astana: $ INSERT INTO city (name, country_id, population) VALUES ('Astana', 5, 1136156); At natural na nakukuha namin ang error: Ngayon tinitiyak ng foreign key na hindi namin susubukan na magtalaga ng isang bansa sa lungsod na wala sa aming database. Ang bahaging ito ng takdang-aralin ay maaaring ituring na natapos - ipasa sa bago :)

PUMILI ng pahayag

Well, parang hindi na nakakatakot ang lahat, di ba? Nais kong tandaan muli na para sa mga developer ng Java, ang kaalaman sa database ay dapat magkaroon. Kung walang database hindi ka makakapunta kahit saan. Oo, gusto ko nang magsimulang magsulat ng aplikasyon, sumasang-ayon ako. Pero kailangan. Kaya magpapatuloy tayo sa ganitong paraan. Gamit ang SELECT statement, kinukuha namin ang data mula sa database. Iyon ay, ito ay isang tipikal na operasyon ng DML (nakalimutan mo na ba kung ano ito?...))) Basahin muli ang mga artikulo NOON). Ano ang mga benepisyo ng relational database? Mayroon silang mahusay na pag-andar para sa pagsasama-sama at pagkuha ng data. Ito ang ginagamit ng SELECT statement. Mukhang walang magiging kumplikado tungkol dito, tama ba? Ngunit lumalabas na marami pa rin ang dapat maunawaan) Mahalaga para sa atin na maunawaan ang mga pangunahing kaalaman kung saan tayo mabubuo. Ang pinakasimpleng query na may SELECT statement ay ang piliin ang lahat ng data mula sa isang table. Talagang nagustuhan ko ang paglalarawan mula sa wiki tungkol sa eksaktong pagkakasunud-sunod ng mga operator na dapat pumunta sa isang SELECT query, kaya't buong tapang kong kokopyahin ito dito:

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

Dito mo makikita na hindi mo pwedeng unahin ang GROUP BY operator at pagkatapos ay ang WHERE operator. Ito ay kailangang tandaan upang sa bandang huli ay walang sama ng loob sa mga pagkakamaling hindi malinaw kung saan nanggaling. $SELECT * MULA sa lungsod; Ngunit ang pag-scrap ng lahat ng data ay malinaw na hindi masaya para sa amin. Ito ay eksaktong pareho kung gusto nating martilyo ang mga kuko gamit ang mikroskopyo [1] , [2] . Dahil ang database ay gumaganap ng pag-filter, pag-uuri at pagsasama-sama ng mga operasyon nang mas mabilis kaysa sa Java code, mas mahusay na iwanan ang bagay na ito sa database. Samakatuwid, sa pamamagitan ng pagpapakumplikado sa mga gawain magbubukas kami ng bagong pag-andar.

SAAN parameter

Upang i-filter ang isang seleksyon, ginagamit ang salitang WHERE . Dapat itong bigyang-kahulugan tulad ng sumusunod: SELECT * FROM tablename (piliin ang lahat ng field mula sa table tablename) WHERE talbe_row = 1 (kung saan sa mga record ang table_row field ay katumbas ng 1). Mahalagang tandaan na ang pagkakasunud-sunod ng mga keyword sa query ay mahalaga. Hindi mo maaaring isulat kung WHERE a =1 FROM table_name SELECT *. Para sa wikang Ruso ito ay ok, at sa ilan ay maaaring hindi ito mukhang tulad ng isang gulo, ngunit para sa SQL ito ay hindi katanggap-tanggap. Sinusulat namin ang sumusunod na query: $ SELECT * FROM city WHERE country_id = 1; At pinili namin ang mga lungsod ng Ukrainian. Hindi masama, tama? Paano kung gusto natin hindi lamang ang Ukrainian, kundi pati na rin ang Belarusian? Para sa layuning ito, maaari naming ilista ang koleksyon ng mga halaga na maaaring kunin ng field: $SELECT * FROM city WHERE country_id IN(1, 3); At mayroon na tayong mga lungsod mula sa dalawang bansa bilang tugon. Paano kung maraming kundisyon para salain? Sabihin nating gusto natin ang mga lungsod na may populasyon na higit sa dalawang milyon? Upang gawin ito, gamitin ang mga salitang O at AT : $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000; Mahusay, ngunit paano kung kailangan nating magdagdag ng isa pang kundisyon - upang maghanap ng mga pangalan sa pamamagitan ng isang regular na expression (hindi ko ilalarawan ang mga regular na expression dito: narito ang isang tao na "maikli" ang gumawa nito sa 4 na bahagi )? Halimbawa, natatandaan namin kung paano baybayin ang isang lungsod, ngunit hindi ganap... Upang gawin ito, maaari mong idagdag ang LIKE na keyword sa pag-filter na expression : $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR pangalan TULAD "%hark%"; At sa ganitong paraan nakuha rin namin si Kharkov. Bilang resulta, masasabi nating napakahusay ng aming paghahanap. Ngunit nais kong ayusin hindi ayon sa ID, ngunit ayon sa populasyon, ngunit paano? Oo napakasimple...

ORDER BY parameter

Gamit ang ORDER BY, maaari naming pag-uri-uriin ang mga talaan na aming natanggap ayon sa isang partikular na field. Pinag-uuri nito ang parehong mga numero at mga string. Palawakin natin ang nakaraang query, pagbukud-bukurin ayon sa populasyon, pagdaragdag ng ORDER BY population: $ SELECT * FROM city WHERE country_id IN (1, 3) AT population > 2000000 OR name LIKE “%hark%” ORDER BY population; Tulad ng nakikita natin, ang pag-uuri ay naganap sa natural na pagkakasunud-sunod, iyon ay, sa pataas na pagkakasunud-sunod. Paano kung kabaligtaran ang gusto natin? Para magawa ito, kailangan mong idagdag ang salitang DESC: $ SELECT * FROM city WHERE country_id IN (1, 3) AT population > 2000000 OR name LIKE “%hark%” ORDER BY population DESC; Ngayon ang pag-uuri ay batay sa pagbawas ng populasyon. At ginagawa ito ng database nang napakabilis: walang Collections.sort doon na maihahambing. Ngayon, pag-uri-uriin natin ayon sa hilera, ayon sa pangalan sa reverse order: $ SELECT * MULA sa lungsod KUNG SAAN NAKAsadsad ang country_id (1, 3) AT populasyon > 2000000 O pangalan TULAD ng “%hark%” ORDER BY name DESC;

GROUP BY parameter

Ginagamit sa pagpangkat ng mga tala ayon sa mga partikular na field. Karaniwang kailangan ito para gumamit ng mga pinagsama-samang pag-andar... Ano ang mga pinagsama-samang pag-andar?)) Makatuwirang ipangkat ayon sa ilang mga patlang kung pareho ang mga ito para sa magkakaibang mga talaan. Tingnan natin kung ano ang ibig sabihin nito gamit ang ating halimbawa. Sabihin nating may mga dayuhang key ang mga lungsod - mga country ID. Kaya, ang ID ay pareho para sa mga lungsod mula sa parehong bansa. Samakatuwid, maaari kang kumuha at magpangkat ng mga tala ayon sa kanila: $ SELECT country_id, COUNT(*) FROM city GROUP BY country_id; Ngunit kung walang mga pag-andar ng pagsasama-sama, mukhang medyo walang kinang, dapat mong aminin. Samakatuwid, tingnan natin ang ilan sa mga pinakakaraniwang function:

• COUNT - bilang ng mga tala, maaaring gamitin nang walang pagpapangkat, ginamit bilang COUNT(*) . Sa kaso ng pagpapangkat ayon sa ilang field - COUNT(groupped_field);
• MAX - hinahanap ang pinakamataas na halaga para sa isang partikular na field;
• MIN - hinahanap ang pinakamababang halaga para sa isang partikular na field;
• SUM - hinahanap ang kabuuan para sa isang partikular na field;
• AVG - hinahanap ang average na halaga.

Sa pangkalahatan, maaaring gamitin ang mga function na ito nang walang pagpapangkat, pagkatapos lamang ng isang field ang ipapakita. Subukan natin ang mga ito para sa populasyon ng ating lungsod: $ SELECT COUNT(*) FROM city; Ang hiniling nila ay kung ano ang nakuha nila. Ang dami lang ng record. Minsan ito ay kapaki-pakinabang. Halimbawa, kung kailangan nating malaman ang bilang ng mga artikulo ng isang partikular na may-akda. Hindi na kailangang i-rake ang mga ito sa database at bilangin ang mga ito. Maaari mo lamang gamitin ang COUNT(). $ PUMILI NG AVG(populasyon) MULA sa lungsod; $ PUMILI NG MIN(populasyon) MULA sa lungsod; At dito nagkakaroon ng puwersa ang pagpapangkat. Halimbawa, ang gawain ay makuha ang pinakamaliit na lungsod sa bansa. Alam na kung paano gawin ito? Subukan ito sa iyong sarili, pagkatapos ay panoorin ang: $ SELECT country_id bilang Bansa, MIN(populasyon) MULA sa lungsod WHERE GROUP BY country_id; Sa ngayon nakikita lang namin ang numero ng ID ng bansa, ngunit hindi mahalaga - sa susunod na gagawin namin ang lahat. At kaya mayroon nang resulta, at nakuha namin ang gusto namin - ang pinakamaliit na lungsod sa bansa na may ID = 1. Magiging pareho ang iba sa mga function. Mahalagang tandaan na ang pag-rake ng lahat ng field sa pamamagitan ng * kapag gumagamit ng pagpapangkat at pagsasama-sama ay hindi gagana! Pag-isipan mo ;)

Takdang aralin

Batay sa mga resulta ng mga nakaraang artikulo, malinaw na ang araling-bahay ay ginagawa, kaya magpatuloy tayo)) Oo, lahat ng gumagawa ng araling-bahay ay patuloy na maglalagay ng "+" sa mga komento. Mahalaga para sa akin na ang paksa ng takdang-aralin ay kawili-wili sa iyo, upang patuloy kong gawin ito sa hinaharap. Oo, regular kong binabasa ang iyong mga komento. Syempre, mas madalas akong sumagot. Nakita ko na hiniling nila na magbigay ng mas mahirap na mga problema sa SQL. Hanggang sa natutunan namin ang pagsali, walang mga kagiliw-giliw na problema, kaya magkakaroon ng mga kailangan ko para sa karagdagang materyal.

Mga gawain:

Unawain ang HAVING operator at magsulat ng isang halimbawang query para sa mga talahanayan mula sa aming halimbawa. Kung kailangan mong magdagdag ng ilang mga patlang o higit pang mga halaga upang gawin itong mas malinaw, idagdag ang mga ito. Kung nais ng sinuman, isulat ang iyong halimbawang solusyon sa mga komento: sa ganitong paraan masusuri ko rin ito kung may oras ako.
1. I-install ang MySQL Workbench upang gumana sa database sa pamamagitan ng UI. Sa tingin ko, mayroon na tayong sapat na pagsasanay sa pagtatrabaho mula sa console. Kumonekta sa database. Kung gumamit ka ng ibang bagay upang magtrabaho kasama ang database, huwag mag-atubiling laktawan ang gawaing ito. Dito at higit pa ay gagamitin ko lamang ang MySQL Workbench.
2. Sumulat ng mga kahilingan na matanggap gamit ang aming data:
   1. ang pinakamaliit/pinaka-mataong bansa;
   2. average na bilang ng mga naninirahan sa bansa;
   3. average na bilang ng mga naninirahan sa mga bansa na ang mga pangalan ay nagtatapos sa "a";
   4. ang bilang ng mga bansang may populasyon na higit sa apat na milyon;
   5. pag-uri-uriin ang mga bansa sa pamamagitan ng pagbaba ng bilang ng mga naninirahan;
   6. pagbukud-bukurin ang mga bansa ayon sa pangalan sa natural na pagkakasunud-sunod.

Konklusyon

Ngayon ay tinalakay namin nang detalyado ang araling-bahay mula sa huling aralin. Bukod dito, itinuturing kong mahalaga ito kapwa para sa mga hindi gumawa nito at para sa mga gumawa nito. Para sa una, ito ay isang pagkakataon upang malaman ang sagot, at para sa huli, upang ihambing ito sa iyong resulta. Mag-subscribe sa aking GitHub account upang manatiling updated sa mga pagbabago sa proyekto. Papanatilihin ko ang buong code base doon. Lahat ay magaganap sa organisasyong ito . Susunod, tinalakay namin ang SELECT statement. Siya ang pinakamahalaga sa atin. Sa pamamagitan nito madadaanan ang lahat ng kahilingan para sa data, at dapat nating maunawaan ito. Ang pinakamahalagang bagay ay tandaan ang pagkakasunud-sunod kung saan ang mga parameter ay idinagdag (SAAN, ORDER NG, GROUP BY, at iba pa). Oo, hindi ko sinabi ang lahat ng posible, ngunit hindi ako nagtakda ng ganoong layunin para sa aking sarili. Oo, alam ko na sabik ka nang magsulat ng aplikasyon. Maging mapagpasensya, ito lang ang kailangan mo. Parehong para sa proyekto at para sa iyong propesyonal na paglago. Habang naghihintay ka, tiyaking pamilyar na sa iyo ang Git. Gagamitin ko ito bilang default, bilang isang kilalang tool. Salamat sa lahat ng nagbabasa. Sa susunod na artikulo ay pag-uusapan natin ang tungkol sa mga koneksyon sa database at pagsali. Iyan ay kung saan ang mga cool na gawain ay magiging))

Ang isang listahan ng lahat ng mga materyales sa serye ay nasa simula ng artikulong ito.