Sinusuri namin ang mga database at ang wikang SQL. (Bahagi 5 - mga koneksyon at pagsasama) - "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.

• Mga uri ng relasyon sa database
  • Isa-sa-marami
  • Isa sa isa (isa-sa-isa)
  • Marami-sa-marami
• Mga Koneksyon (Sumali)
  • INNER JOIN
  • INIWANG SUMALI
  • RIGHT JOIN
  • Securing Joins
• Takdang aralin

Kumusta sa lahat, mga magiging Senior at Senioritas ng software. Tulad ng sinabi ko sa nakaraang bahagi ( pagsuri sa takdang-aralin ), ngayon ay magkakaroon ng bagong materyal. Para sa mga taong masigasig, naghukay ako ng isang kawili-wiling takdang aralin upang ang mga nakakaalam na ng lahat at ang mga hindi alam ngunit gustong mag-google ay makapagpraktis at masubok ang kanilang mga kakayahan. Ngayon ay pag-uusapan natin ang mga uri ng koneksyon at pagsali.

Mga uri ng relasyon sa database

Upang maunawaan kung ano ang mga relasyon, kailangan mong tandaan kung ano ang isang dayuhang susi. Para sa mga nakalimutan, maligayang pagdating sa simula ng serye .

Isa-sa-marami

Tandaan natin ang ating halimbawa sa mga bansa at lungsod. Malinaw na ang isang lungsod ay dapat magkaroon ng isang bansa. Paano i-link ang isang bansa sa isang lungsod? Kinakailangang mag-attach sa bawat lungsod ng natatanging identifier (ID) ng bansa kung saan ito nabibilang: nagawa na namin ito. Ito ay tinatawag na isa sa mga uri ng koneksyon - isa sa marami (makabubuti ring malaman ang Ingles na bersyon - isa-sa-marami). Upang i-paraphrase, masasabi natin: maraming lungsod ang maaaring kabilang sa isang bansa. Ganyan mo dapat tandaan ito: isang one-to-many na relasyon. Sa ngayon ay malinaw, tama ba? Kung hindi, narito ang unang larawan mula sa Internet: Ipinapakita nito na may mga customer at ang kanilang mga order. Makatuwiran na ang isang customer ay maaaring magkaroon ng higit sa isang order. Mayroong isa-sa-marami :) O isa pang halimbawa: May tatlong talahanayan: publisher, may-akda at libro. Bawat publisher na ayaw malugi at gustong maging matagumpay ay may higit sa isang awtor, hindi ka ba sang-ayon? Sa turn, ang bawat may-akda ay maaaring magkaroon ng higit sa isang libro - maaaring walang duda tungkol doon. At nangangahulugan ito, muli, ang koneksyon ng isang may-akda sa maraming mga libro, isang publisher sa maraming mga may-akda . Marami pang mga halimbawa na maaaring ibigay. Ang kahirapan sa pang-unawa sa una ay maaari lamang magsinungaling sa pag-aaral na mag-isip nang abstract: upang tumingin mula sa labas sa mga talahanayan at ang kanilang pakikipag-ugnayan.

Isa sa isa (isa-sa-isa)

Ito ay masasabing isang espesyal na kaso ng one-to-many na komunikasyon. Isang sitwasyon kung saan ang isang tala sa isang talahanayan ay nauugnay sa isang tala lamang sa isa pang talahanayan. Anong mga halimbawa ang maaaring magkaroon mula sa buhay? Kung ibubukod natin ang poligamya, maaari nating sabihin na mayroong isa-sa-isang relasyon sa pagitan ng mag-asawa. Bagaman kahit na sabihin natin na ang poligamya ay pinapayagan, kung gayon ang bawat asawa ay maaari pa ring magkaroon ng isang asawa lamang. Ang parehong ay maaaring sinabi tungkol sa mga magulang. Ang bawat tao ay maaaring magkaroon lamang ng isang biyolohikal na ama at isang biyolohikal na ina lamang. Tahasang isa-sa-isang relasyon. Habang isinusulat ko ito, may naisip ako: bakit hahatiin ang isa-sa-isang relasyon sa dalawang talaan sa magkaibang talahanayan, kung mayroon na silang isa-sa-isang relasyon? Ako mismo ang nakaisip ng sagot. Ang mga rekord na ito ay maaari ding maiugnay sa ibang mga talaan sa ibang mga paraan. Ano bang pinagsasabi ko? Ang isa pang halimbawa ng one-to-one na koneksyon ay sa pagitan ng bansa at ng pangulo. Posible bang isulat ang lahat ng data tungkol sa pangulo sa talahanayan ng "bansa"? Oo, magagawa mo, ang SQL ay hindi magsasabi ng isang salita. Pero kung iisipin mo na tao rin ang presidente... At maaring may asawa (another one-to-one relationship) at mga anak (another one-to-many relationship) tapos ito pala. kinakailangan upang maiugnay ang bansa sa asawa at mga anak ng pangulo…. Parang baliw, tama? :D Maaaring maraming iba pang mga halimbawa para sa koneksyon na ito. Bukod dito, sa ganoong sitwasyon, maaari kang magdagdag ng dayuhang susi sa parehong mga talahanayan, hindi tulad ng isa-sa-maraming relasyon.

Marami-sa-marami

Base na sa pangalan, hulaan mo kung ano ang pag-uusapan natin. Kadalasan sa buhay, at pinoprograma natin ang ating buhay, may mga sitwasyon kung saan ang mga uri ng koneksyon sa itaas ay hindi sapat upang ilarawan ang mga bagay na kailangan natin. Napag-usapan na natin ang tungkol sa mga publisher, libro at may-akda. Napakaraming koneksyon dito... Ang bawat publikasyon ay maaaring magkaroon ng ilang mga may-akda - isang one-to-many na koneksyon. Kasabay nito, ang bawat may-akda ay maaaring magkaroon ng ilang mga publisher (bakit hindi, ang manunulat ay nai-publish sa isang lugar, nagkaroon ng away sa pera, pumunta sa isa pang publishing house, halimbawa). At ito na naman ang one-to-many na relasyon. O ito: ang bawat may-akda ay maaaring magkaroon ng ilang mga libro, ngunit ang bawat aklat ay maaari ding magkaroon ng ilang mga may-akda. Muli, isang isa-sa-maraming relasyon sa pagitan ng may-akda at aklat, aklat at may-akda. Mula sa halimbawang ito maaari tayong gumuhit ng isang mas pormal na konklusyon: Malinaw kung paano itakda ang mga nakaraang uri ng koneksyon sa SQL: ipinapasa lang namin ang ID ng isang iyon sa mga talaan, kung saan marami, tama? Isang bansa ang nagbibigay ng ID nito bilang foreign key sa maraming lungsod. Ano ang gagawin sa maraming-sa-maraming relasyon ? Ang pamamaraang ito ay hindi angkop. Kailangan nating magdagdag ng isa pang talahanayan na magkokonekta sa dalawang talahanayan. Halimbawa, pumunta tayo sa MySQL, lumikha ng isang bagong database na manytomany, lumikha ng dalawang talahanayan, may-akda at libro, na maglalaman lamang ng mga pangalan at ang kanilang mga ID: GUMAWA NG DATABASE ng marami; GAMITIN manytomany; CREATE TABLE author( id INT AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY (id) ); GUMAWA NG TABLE book( id INT AUTO_INCREMENT, pangalan VARCHAR(100), PRIMARY KEY (id) ); Ngayon, gumawa tayo ng pangatlong talahanayan na magkakaroon ng dalawang dayuhang key mula sa aming mga talahanayan ng may-akda at aklat, at ang link na ito ay magiging kakaiba. Ibig sabihin, hindi posibleng magdagdag ng record na may parehong mga key nang dalawang beses: GUMAWA NG TABLE authors_x_books ( book_id INT NOT NULL, author_id INT NOT NULL, FOREIGN KEY (book_id) REFERENCES book(id), FOREIGN KEY (author_id) REFERENCES author (id ), NATATANGI (book_id, author_id) ); Dito ay gumamit kami ng ilang bagong feature na kailangang hiwalay na magkomento:

• NOT NULL ay nangangahulugan na ang patlang ay dapat palaging punan, at kung hindi, sasabihin sa amin ito ng SQL;
• Sinasabi ng UNIQUE na ang isang patlang o isang bungkos ng mga patlang ay dapat na natatangi sa talahanayan. Madalas na nangyayari na bilang karagdagan sa natatanging identifier, dapat na natatangi ang isa pang field para sa bawat tala. At ang UNIQUE ang may pananagutan sa eksaktong bagay na ito.

Mula sa aking pagsasanay: kapag lumipat mula sa isang lumang system patungo sa isang bago, tayo, bilang mga developer, ay dapat mag-imbak ng mga ID ng lumang system upang gumana dito at lumikha ng sarili natin. Bakit lumikha ng iyong sarili at hindi gumamit ng mga luma? Maaaring hindi sapat na kakaiba ang mga ito, o maaaring hindi na nauugnay at limitado ang diskarteng ito sa paggawa ng mga ID. Para sa layuning ito, ginawa naming natatangi din ang lumang ID-name sa talahanayan. Upang suriin ito, kailangan mong magdagdag ng data. Magdagdag ng aklat at may-akda: NSERT INTO book (name) VALUES ("book1"); INSERT INTO author (name) VALUES ("author1"); Alam na natin mula sa mga nakaraang artikulo na magkakaroon sila ng mga ID 1 at 1. Samakatuwid, maaari tayong agad na magdagdag ng tala sa ikatlong talahanayan: INSERT INTO authors_x_books VALUES (1,1); At magiging maayos ang lahat hanggang sa gusto nating ulitin muli ang huling utos: ibig sabihin, isulat muli ang parehong mga ID: Ang resulta ay magiging natural - isang error. Magkakaroon ng duplicate. Ang entry ay hindi itatala. Ito ay kung paano malilikha ang isang marami-sa-maraming koneksyon... Ang lahat ng ito ay napaka-cool at kawili-wili, ngunit isang lohikal na tanong ang lumitaw: kung paano makuha ang impormasyong ito? Paano pagsamahin ang data mula sa iba't ibang mga talahanayan nang magkasama at makakuha ng isang sagot? Ito ang pag-uusapan natin sa susunod na bahagi))

Mga Koneksyon (Sumali)

Sa nakaraang bahagi, inihanda ko kayo upang maunawaan kaagad kung ano ang mga pagsali at kung saan ito gagamitin. Dahil lubos akong kumbinsido na sa sandaling dumating ang pag-unawa, ang lahat ay agad na magiging napakasimple, at lahat ng mga artikulo tungkol sa pagsali ay magiging malinaw tulad ng mga mata ng isang sanggol :D Sa pangkalahatan at sa pangkalahatan, ang mga pagsali ay nakakakuha ng resulta mula sa ilang mga talahanayan sa pamamagitan ng paraan ng isang JOIN (sumali mula sa English join). At iyon lang...) At para makasali, kailangan mong tukuyin ang field kung saan isasama ang mga talahanayan. Ang diyablo ay hindi nakakatakot tulad ng siya ay pininturahan, tama?) Susunod, pag-uusapan lang natin kung anong mga uri ng pagsali ang mayroon at kung paano gamitin ang mga ito. Maraming uri ng pagsali, at hindi namin isasaalang-alang ang lahat. Tanging ang mga talagang kailangan natin. Kaya naman hindi kami interesado sa mga kakaibang pagsali gaya ng Cross at Natural. Nakalimutan ko na, kailangan nating tandaan ang isa pang nuance: ang mga talahanayan at mga patlang ay maaaring magkaroon ng mga alias - pseudonyms. Maginhawang ginagamit ang mga ito para sa pagsali. Halimbawa, magagawa mo ito: SELECT * FROM table1; kung ang query ay madalas na gagamit ng table1, maaari mo itong bigyan ng alias: SELECT* FROM table1 as t1; o mas madaling isulat: SELECT * FROM table1 t1; at pagkatapos sa query ay posible na gamitin ang t1 bilang isang alias para sa talahanayang ito.

INNER JOIN

Ang pinakakaraniwan at simpleng pagsali. Sinasabi nito na kapag mayroon kaming dalawang talahanayan at isang field kung saan ito maaaring pagsamahin, ang lahat ng mga talaan na ang mga relasyon ay umiiral sa dalawang talahanayan ay pipiliin. Mahirap sabihin kahit papaano. Tingnan natin ang isang halimbawa: Magdagdag tayo ng isang tala sa database ng ating mga lungsod. Isang entry para sa mga lungsod at isa para sa mga bansa: $ INSERT INTO country VALUES(5, "Uzbekistan", 34036800); at $ INSERT INTO city (pangalan, populasyon) VALUES("Tbilisi", 1171100); Nagdagdag kami ng isang bansa na walang lungsod sa aming talahanayan, at isang lungsod na hindi nauugnay sa isang bansa sa aming talahanayan. Kaya, ang INNER JOIN ay nakikibahagi sa pagbibigay ng lahat ng mga talaan para sa mga koneksyong iyon na nasa dalawang talahanayan. Ganito ang hitsura ng pangkalahatang syntax kapag gusto nating sumali sa dalawang table table1 at table2: SELECT * FROM table1 t1 INNER JOIN table2 ON t1.id = t2.t1_id; at pagkatapos ay ibabalik ang lahat ng mga talaan na may kaugnayan sa dalawang talahanayan. Para sa aming kaso, kapag gusto naming makatanggap ng impormasyon para sa mga bansa kasama ang mga lungsod, ito ay magiging ganito: $ SELECT * FROM city ci INNER JOIN country co ON ci.country_id = co.id; Dito, bagama't magkapareho ang mga pangalan, makikita mo na ang mga larangan ng mga lungsod ay nauuna, pagkatapos ay ang mga larangan ng mga bansa. Pero yung dalawang entry na idinagdag namin sa itaas ay wala. Dahil ganyan talaga ang INNER JOIN.

INIWANG SUMALI

May mga kaso, at medyo madalas, kapag hindi kami nasiyahan sa pagkawala ng mga patlang ng pangunahing talahanayan dahil sa ang katunayan na walang rekord para dito sa katabing talahanayan. Ito ay para sa isang LEFT JOIN. Kung sa aming nakaraang kahilingan ay tinukoy namin ang LEFT sa halip na INNER, magdaragdag kami ng isa pang lungsod sa tugon - Tbilisi: $ SELECT * FROM city ci LEFT JOIN country co ON ci.country_id = co.id; May bagong entry tungkol sa Tbilisi at lahat ng bagay na nauugnay sa bansa ay walang bisa . Ito ay madalas kung paano ito ginagamit.

RIGHT JOIN

Dito magkakaroon ng pagkakaiba mula sa LEFT JOIN na ang lahat ng mga field ay pipiliin hindi sa kaliwa, ngunit sa kanan sa koneksyon. Ibig sabihin, hindi mga lungsod, ngunit lahat ng bansa ay kukunin: $ SELECT * FROM city ci RIGHT JOIN country co ON ci.country_id = co.id; Ngayon ay malinaw na sa kasong ito ay walang Tbilisi, ngunit magkakaroon tayo ng Uzbekistan. May ganyan...))

Securing Joins

Ngayon gusto kong ipakita sa iyo ang isang tipikal na larawan na pinipilit ng mga junior bago ang isang pakikipanayam upang kumbinsihin sila na naiintindihan nila ang kakanyahan ng mga pagsali: Narito ang lahat ay ipinapakita sa anyo ng mga set, ang bawat bilog ay isang talahanayan. At ang mga lugar kung saan ito pininturahan ay ang mga bahaging ipapakita sa SELECT. Tingnan natin:

• Ang INNER JOIN ay ang intersection lamang ng mga set, iyon ay, ang mga record na may koneksyon sa dalawang table - A at B;
• Ang LEFT JOIN ay lahat ng record mula sa table A, kasama ang lahat ng record mula sa table B na may intersection (koneksyon) sa A;
• Ang RIGHT JOIN ay eksaktong kabaligtaran ng LEFT JOIN - lahat ng mga tala sa talahanayan B at mga tala mula sa A na may kaugnayan.

Pagkatapos ng lahat ng ito, ang larawang ito ay dapat na malinaw))

Takdang aralin

Sa pagkakataong ito ang mga gawain ay magiging lubhang kawili-wili at lahat ng mga matagumpay na malutas ang mga ito ay makatitiyak na sila ay handa nang magsimulang magtrabaho sa bahagi ng SQL! Ang mga gawain ay hindi ngumunguya at isinulat para sa mga nasa gitnang mag-aaral, kaya hindi ito magiging madali at nakakainip para sa iyo :) Bibigyan kita ng isang linggo upang gawin ang mga gawain sa iyong sarili, at pagkatapos ay maglalathala ako ng isang hiwalay na artikulo na may detalyadong pagsusuri ng solusyon sa mga gawaing ibinigay ko sa iyo.

Ang aktwal na gawain:

1. Sumulat ng isang SQL script upang lumikha ng talahanayan ng 'Mag-aaral' na may mga sumusunod na field: id (pangunahing key), pangalan, apelyido, e_mail (natatangi).
2. Sumulat ng isang script ng SQL upang lumikha ng talahanayan ng 'Aklat' na may mga sumusunod na field: id, pamagat (id + pamagat = pangunahing key). I-link ang 'Mag-aaral' at 'Aklat' sa isang 'Mag-aaral' na isa-sa-maraming relasyong 'Aklat'.
3. Sumulat ng SQL script upang lumikha ng talahanayan ng 'Guro' na may mga sumusunod na field: id (pangunahing key), pangalan, apelyido, e_mail (natatangi), paksa.
4. Iugnay ang 'Mag-aaral' at 'Guro' sa isang 'Mag-aaral' na maraming-sa-maraming Guro' na relasyon.
5. Piliin ang 'Mag-aaral' na may 'oro' sa kanilang apelyido, halimbawa 'Sid oro v', 'V oro novsky'.
6. Piliin mula sa talahanayan ng 'Mag-aaral' ang lahat ng apelyido ('last_name') at ang bilang ng kanilang mga pag-uulit. Isaalang-alang na may mga namesakes sa database. Pagbukud-bukurin ayon sa dami sa pababang pagkakasunud-sunod. Dapat itong magmukhang ganito:

   huling pangalan	dami
   Petrov	15
   Ivanov	12
   Sidorov	3

7. Piliin ang nangungunang 3 pinakaulit na pangalan mula sa 'Mag-aaral'. Pagbukud-bukurin ayon sa dami sa pababang pagkakasunud-sunod. Dapat itong magmukhang ganito:

   pangalan	dami
   Alexander	27
   Sergey	10
   Peter	7

8. Piliin ang 'Mga Mag-aaral' na may pinakamalaking bilang ng 'Aklat' at nauugnay na 'Guro.' Pagbukud-bukurin ayon sa dami sa pababang pagkakasunod-sunod. Dapat itong magmukhang ganito:

   Apelyido_ng guro	Apelyido ng mag-aaral	Ang dami ng libro
   Petrov	Sidorov	7
   Ivanov	Smith	5
   Petrov	Kankava	2>

9. Piliin ang 'Guro' na may pinakamalaking bilang ng 'Aklat' mula sa lahat ng kanyang 'Mag-aaral'. Pagbukud-bukurin ayon sa dami sa pababang pagkakasunud-sunod. Dapat itong magmukhang ganito:

   Apelyido_ng guro	Ang dami ng libro
   Petrov	9
   Ivanov	5

10. Piliin ang 'Guro' na ang bilang ng 'Aklat' para sa lahat ng kanyang 'Mag-aaral' ay nasa pagitan ng 7 at 11. Pagbukud-bukurin ayon sa dami sa pababang pagkakasunud-sunod. Dapat itong magmukhang ganito:

    Apelyido_ng guro	Ang dami ng libro
    Petrov	labing-isa
    Sidorov	9
    Ivanov	7

11. I-print ang lahat ng 'apelyido' at 'pangalan' ng lahat ng 'Guro' at 'Mag-aaral' na may field na 'uri' (mag-aaral o guro). Pagbukud-bukurin ayon sa alpabeto ayon sa 'last_name'. Dapat itong magmukhang ganito:

    huling pangalan	uri
    Ivanov	mag-aaral
    Kankava	guro
    Smith	mag-aaral
    Sidorov	guro
    Petrov	guro

12. Magdagdag ng column na 'rate' sa umiiral na talahanayan ng 'Mag-aaral', na mag-iimbak ng kursong kasalukuyang kinaroroonan ng mag-aaral (numeric na halaga mula 1 hanggang 6).
13. Ang item na ito ay hindi kinakailangan, ngunit magiging isang plus. Sumulat ng isang function na dadaan sa lahat ng 'Mga Aklat' at ilalabas ang lahat ng 'mga pamagat' na pinaghihiwalay ng mga kuwit.

Konklusyon

Medyo nag-drag ang serye tungkol sa database. Sumang-ayon. Gayunpaman, malayo na ang narating namin at bilang resulta ay lumabas kami na may kaalaman sa bagay na ito! Salamat sa lahat ng nagbabasa, pinapaalalahanan ko kayo na lahat ng gustong magpatuloy at sumunod sa proyekto ay kailangang gumawa ng account sa GitHub at mag-subscribe sa aking account :) Marami pang darating - pag-usapan natin ang tungkol kay Maven at Docker. Salamat sa lahat ng nagbabasa. Uulitin ko ulit: ang maglalakad ay makakabisado ng daan ;)

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