Sinusuri namin ang mga database at ang wikang SQL. (Bahagi 6 - Pagsusuri sa huling takdang-aralin) - "Java project from A to 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. Ang bahaging ito ay naglalaman ng pagsusuri ng huling gawain sa database. Pagbati, mahal na mga mambabasa. Ngayon ay susuriin natin ang gawain mula sa huling artikulo sa database. Ito ay kawili-wili dahil ito ay inilaan para sa mga panayam sa Middle level. Iyon ay, pagkatapos ng gawaing ito maaari ka nang pumunta para sa isang pakikipanayam, at matagumpay mong maipapasa ang hindi bababa sa bahagi ng kung ano ang nauugnay sa mga relational database. Alam ko kung gaano kinakailangan ang artikulong ito, at samakatuwid ay ilalagay ko ang lahat ng aking karanasan upang gawin itong kapaki-pakinabang at kawili-wili. At kung hindi ka makatulog sa gitna ng artikulo, nangangahulugan ito na nakamit ko ang aking layunin. Hindi ko na uulitin ang buong gawain: Sipiin ko ito bago kumpletuhin ang bawat gawain, na salungguhitan ito sa mga italics. Inaasahan ko na lahat ng nagbabasa ng artikulong ito ay tatakbo sa lahat ng mga query sa kanilang database at makakakuha ng parehong bagay. Ito ay magdadala ng pinakamataas na benepisyo sa negosyo. At mas magiging masaya ako mula sa pag-iisip na nakatulong ako sa isang tao sa aming mahirap na gawain)

Ehersisyo 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). Nagawa na namin ito, kaya hindi dapat magkaroon ng anumang mga problema. Sa script kailangan mong tukuyin ang pangunahing susi at isang natatanging field na naiiba sa pangunahin. Una, gumawa tayo ng bagong database para sa gawaing ito: $ GUMAWA NG DATABASE final_task; At gamitin natin ang database na ito: $ USE final_task; Kapag na-set up na ang kapaligiran at handa nang patakbuhin ang trabaho, maaari nating isulat ang sumusunod na script: $ CREATE TABLE student ( id INT AUTO_INCREMENT, name VARCHAR(40), last_name VARCHAR(50), email VARCHAR(100), PRIMARY KEY ( id), NATATANGING (email ) ); Sa ngayon, wala pang bago kumpara sa mga pinagdaanan na natin. Anumang mga komento ay hindi kailangan, magpatuloy tayo.

Gawain 2-3

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'. Pagsamahin natin ang dalawang gawain sa isa upang gawin itong mas mabilis at mas maginhawa. Napag-usapan ko na kung paano magdagdag ng isang hiwalay na dayuhang key sa mga nakaraang artikulo. Upang magdagdag, kailangan nating tandaan kung paano tayo gumagawa ng mga koneksyon at sa pamamagitan ng kung ano. Tutulungan ka ng nakaraang artikulo, at narito ang script: $ CREATE TABLE book ( id INT, title VARCHAR(100), student_id INT DEFAULT NULL, PRIMARY KEY (id, title), FOREIGN KEY (student_id) REFERENCES student (id ) ); Sa simpleng paraan na ito, nagdagdag kami ng composite key para sa aming table PRIMARY KEY (id, title) , ngayon ang key ay magiging eksaktong pares. Nangangahulugan ito na maaaring mayroong higit sa isang magkaparehong halaga ng field ng id sa talahanayan. At eksaktong pareho para sa pamagat.

Gawain 4

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. Patuloy naming inihahanda ang aming database para sa mga query, lumikha ng talahanayan ng guro: $ CREATE TABLE teacher( id INT AUTO_INCREMENT, pangalan VARCHAR(30), last_name VARCHAR(30), email VARCHAR(100), subject VARCHAR(40), PRIMARY KEY ( id), NATATANGING (email) ); So far hindi naman mahirap diba? Tatlong gawain na ang tapos na!

Gawain 5

Iugnay ang 'Mag-aaral' at 'Guro' sa isang 'Mag-aaral' na maraming-sa-maraming Guro' na relasyon. Ngayon ito ay mas kawili-wili! Napag-usapan lang namin ito last time. Hayaan mong ipaalala ko sa iyo kung ano ang kailangan para makamit ito: kailangan mong lumikha ng isang intermediate table na mag-iimbak ng mga pares ng mag-aaral-guro. Sa tulong nito, magiging posible na lumikha ng isang many-to-many na relasyon. Samakatuwid, gumawa tayo ng talahanayan students_x_techers . Ang diskarte sa pagbibigay ng pangalan ay bukas at maaari ding: student_teacher . $ GUMAWA NG TABLE students_x_teachers ( student_id INT NOT NULL, teacher_id INT NOT NULL, PRIMARY KEY (student_id, teacher_id), FOREIGN KEY (student_id) REFERENCES student(id), FOREIGN KEY (teacher_id) REFERENCES teacher(id) ); Tulad ng nakikita mo, ang lahat ay ginagawa nang malinaw at magkakaugnay. Mayroon kaming composite key para sa dalawang foreign key: student_id at teacher_id. Bakit isa ring foreign key? Upang makatiyak tayo na mayroong mga tala sa talahanayan ng mag-aaral at guro para sa mga pares na itinatala.

Gawain 6

Piliin ang 'Mag-aaral' na may 'oro' sa kanilang apelyido, halimbawa 'Sidorov', 'Voronovsky'. Upang gawin itong kawili-wili at visual para sa amin, iminumungkahi kong magdagdag muna ng ilang mga mag-aaral, upang ang ilan sa kanila ay angkop para sa kahilingang ito, at ang ilan ay hindi. Samakatuwid, isulat natin ang mga dapat isama bilang resulta ng kahilingan: $ INSERT INTO student (name, last_name, email) VALUES ('Ivan', 'Sidorov', 'ivan.sidorov@gmail.com'); $ INSERT IN TO student (name, last_name, email) VALUES ('Nikolay', 'Voronovsky', 'nikolay.voronovsky@gmail.com'); At ang mga hindi dapat makapasok: $ INSERT INTO student (name, last_name, email) VALUES ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO student (name, last_name, email) VALUES('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); Tingnan natin ang resulta, tingnan ang listahan ng data sa talahanayan ng mga mag-aaral: $ SELECT * FROM student; at nakukuha natin: May apat na record sa kabuuan, dalawa sa kanila ang dapat magkasya, at dalawa ang hindi dapat. Kapag naihanda na ang lahat ng data para sa kahilingan, maaari tayong gumawa ng kahilingan para sa mismong gawain: $ SELECT * FROM student WHERE last_name LIKE '%oro%'; Bilang resulta, sina Ivan at Nikolai ay dumaan sa listahan.

Gawain 7

Ang susunod na gawain, mababasa natin: 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

Para sa kalinawan, kailangan mong magdagdag ng higit pang data. Without further ado, let's add the Petrovs, Ivanovs and Sidorovs, who does not know their kinship ;) Hindi ako mag-iimbento ng email address, ibubukod ko lang ito sa mga bagong entry. Patakbuhin natin ang sumusunod na command nang 12 beses: $ INSERT INTO student (name, last_name) VALUES ('Ivan', 'Ivanov'); Magdagdag tayo ng 15 Petrov: $ INSERT INTO student (name, last_name) VALUES ('Petr', 'Petrov'); At dalawang Sidorov (mayroon na kaming isa))): $ INSERT INTO student (name, last_name) VALUES ('Sidor', 'Sidorov'); Ngayon ang data ay handa na. Upang makakuha ng ganoong data, kailangan mong gumawa ng pagpapangkat; upang makagawa ng pagpapangkat, kailangan mong gamitin ang operator ng Group By, at kailangan mong gawin ito sa pamamagitan ng field ng last_name. Mapapansin mo rin na ang bilang ng mga pag-uulit ay itinalaga bilang quantity , at dito kailangan mo ring tandaan kung paano gumawa ng mga alias sa SQL: $ SELECT last_name, COUNT(*) bilang quantity MULA sa student GROUP BY last_name ORDER BY COUNT(*) DESC ; Kaya't nasobrahan ko ito sa mga Petrov - naging 16))

Gawain 8

Kundisyon: 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

Oh, para sa layuning ito mayroon na tayong mga Ivan, Peters at Sidors. Samakatuwid hindi na kailangang idagdag ang mga ito. Alam na natin kung paano mag-sort. Ang tanging bagay na hindi natin napag-usapan ngayon ay kung paano pumili ng isang tiyak na bilang ng mga talaan. Ito ay lumitaw na sa mga nakaraang solusyon sa mga problema sa database. Sa mga hindi pa nakakabasa nito, basahin nyo na. Para sa natitira, dumiretso tayo sa punto: $ SELECT name, COUNT(*) as quantity MULA sa mag-aaral GROUP BY name ORDER BY COUNT(*) DESC LIMIT 3; Tulad ng makikita mula sa query, kung alam mo ang pagkakasunud-sunod ng mga operator sa isang SELECT query, walang magiging problema sa pagsasagawa ng naturang query. At ang gawaing ito ay nasa atin pa rin. At ang kaalaman na ipinakita kanina ay sapat na upang malutas ang problemang ito.

Gawain 9

Kondisyon ng gawain: Piliin ang 'Mag-aaral' na may pinakamalaking bilang ng 'Aklat' at nauugnay na 'Guro'. Pagbukud-bukurin ayon sa dami sa pababang pagkakasunud-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

Kaya, ang gawaing ito ay malinaw na mas mahirap kaysa sa nauna, tama? Hindi kataka-taka: amoy sumali... at higit sa isa) Una, kailangan nating maunawaan kung ano ang gagawin... Malinaw na ang dami ng Aklat ay nangangailangan ng pagpapangkat. Pero ano? At bakit natin sila igrupo? Ang query ay nagsasangkot ng tatlong talahanayan, pagpapangkat, at pag-uuri. Sa paghusga sa katotohanan na ang mga talaan kung saan walang mga libro ay hindi ipinapakita, nangangahulugan ito na kailangan mong kumuha ng INNER JOIN. Magre-request din kami ng LEFT JOIN para walang problema dito. At mayroong ilang mga pagpipilian. Ang unang bagay na ginagawa namin ay pagsamahin ang tatlong talahanayan sa isang talaan. Susunod, pangkat namin ayon sa mag-aaral at idagdag ang pangalan ng guro dito. Ano ang pipiliin natin? Ang pangalan ng guro, mag-aaral at bilang ng mga libro. Magdagdag tayo ng data para sa kahilingan:

• tatlong guro;
• sampung aklat;
• ikonekta ang dalawang mag-aaral sa tatlong guro.

Tatlong guro

$ INSERT INTO teacher(last_name) VALUES ('Matvienko'); $ INSERT INTO teacher(last_name) VALUES ('Shevchenko'); $ INSERT INTO teacher(last_name) VALUES ('Vasilenko');

10 libro

Kukunin ko ang mga ID ng 1st at 2nd students. Magkadikit ako ng mga libro sa kanila. Dahil hindi nakatakda ang AUTO_INCREMENT, upang hindi magsulat ng bagong ID sa bawat pagkakataon, kailangan mong gawin ang sumusunod: $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; Susunod, magdagdag ng mga aklat para sa unang mag-aaral: $ INSERT INTO book (title, student_id) VALUES('book1', 1); $ INSERT INTO book (title, student_id) VALUES('book2', 1); $ INSERT INTO book (title, student_id) VALUES('book3', 1); $ INSERT INTO book (title, student_id) VALUES('book4', 1); $ INSERT INTO book (title, student_id) VALUES('book5', 1); $ INSERT INTO book (title, student_id) VALUES('book6', 1); At mga aklat para sa pangalawang mag-aaral: $ INSERT INTO book (title, student_id) VALUES('book7', 2); $ INSERT INTO book (title, student_id) VALUES('book8', 2); $ INSERT INTO book (title, student_id) VALUES('book9', 2); $ INSERT INTO book (title, student_id) VALUES('book10', 2);

Koneksyon ng guro-mag-aaral

Upang gawin ito, magdagdag ng mga students_x_teachers sa talahanayan: $ INSERT INTO students_x_teachers VALUES (1,1); $INSERT INTO students_x_teachers VALUES(1,2); $INSERT INTO students_x_teachers VALUES(2,3);

Ipatupad natin ang kahilingan

Ginagawa namin ang unang yugto - ini-link namin ang tatlong talahanayan sa isang tala: $ SELECT * MULA sa guro tch INNER JOIN students_x_teachers st_x_tch ON tch.id = st_x_tch.teacher_id INNER JOIN student st ON st_x_tch.student_id = st.id INNER JOIN book b ON st .id = b.student_id; Siyempre, wala pa kaming anumang mga tala, gayunpaman, makikita namin na matagumpay naming naikonekta ang tatlong talahanayan. Ngayon ay nagdaragdag kami ng pagpapangkat ng aklat, pag-uuri at ang mga patlang na kailangan namin: $ SELECT tch.last_name, st.last_name, st.id, COUNT(*) bilang mga aklat MULA sa student st INNER JOIN book b ON st.id = b.student_id INNER SUMALI sa mga estudyante_x_teachers st_x_tch SA st.id = st_x_tch.student_id INNER SUMALI sa teacher tch SA tch.id = st_x_tch.teacher_id GROUP BY st.id ORDER BY books DESC; Ngunit nakakakuha kami ng error sa SQL at ang sumusunod na sagot: Error Code: 1055. Ang Expression #1 ng SELECT list ay wala sa GROUP BY clause at naglalaman ng hindi pinagsama-samang column na 'final_task.tch.last_name' na hindi umaasa sa mga column sa GROUP BY sugnay Hindi gumagana ang mga elementong ito dahil mayroong maraming-sa-maraming ugnayan sa pagitan ng guro at mag-aaral. At totoo ito: hindi tayo makakakuha ng isang guro lang bawat estudyante. Kaya pumunta tayo sa ibang paraan. Gumamit tayo ng tinatawag na View SQL. Ano ang ideya: lumikha kami ng isang hiwalay na view, na isang bagong talahanayan, kasama na ang pagpapangkat na kailangan namin. At sa talahanayang ito ay idaragdag namin ang mga kinakailangang pangalan ng mga guro. Ngunit isinasaalang-alang namin ang katotohanan na maaaring mayroong higit sa isang guro, kaya ang mga entry ay uulitin. Lumikha ng view: $ GUMAWA NG TINGNAN ang mga aklat ng mag-aaral bilang PUMILI st.last_name,st.id,COUNT(*) bilang mga aklat MULA SA mag-aaral st INNER SUMALI aklat b SA st.id=b.student_id GROUP NG st.id ORDER NG mga aklat DESC; Susunod, ginagawa namin ang view na ito bilang isang simpleng talahanayan na may tatlong field: apelyido ng mag-aaral, student_id at bilang ng mga aklat. Ayon sa ID ng estudyante, maaari din tayong magdagdag ng guro sa pamamagitan ng dalawang pagsali: $ SELECT tch.last_name bilang 'Teacher', sbw.last_name 'Student', sbw.books bilang 'Books' mula sa studentbook sbw INNER JOIN students_x_teachers stch ON sbw. id = stch.student_id INNER JOIN teacher tch ON tch.id = stch.teacher_id; At ngayon ang magiging resulta ay: Huh! Ito ay isang kahilingan, tama?) Ito ay naging tulad ng inaasahan: ang isang mag-aaral na may id=1 ay may anim na aklat at dalawang guro, at isang mag-aaral na may id=2 ay may apat na aklat at isang guro.

Gawain 10

Kundisyon: Piliin ang 'Guro' na may pinakamaraming bilang ng 'Mga Aklat' 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

Dito maaari nating gamitin ang isang handa na kahilingan mula sa nakaraang gawain. Ano ang kailangan nating baguhin tungkol dito? Mayroon na kaming data na ito, kailangan lang naming magdagdag ng isa pang pagpapangkat at alisin ang pangalan ng mag-aaral mula sa data ng output. Ngunit una, magdagdag tayo ng isa pang mag-aaral sa guro upang gawing mas kawili-wili ang resulta. Para magawa ito, isusulat namin ang: $ INSERT INTO students_x_teachers VALUES (2, 1); At ang mismong query: $ SELECT tch.last_name bilang 'Teacher', SUM(sbw.books) bilang 'Books' mula sa studentbook sbw INNER JOIN students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN teacher tch ON tch.id = stch teacher_id GROUP NG tch.id; Bilang resulta, nakuha natin: ang gurong si Vasilenko ay mayroong 10 aklat, at si Shevchenko ay mayroong 6...)

Gawain 11

Kundisyon: Pumili ng isang 'Guro' na ang bilang ng 'Mga 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

Dito natin gagamitin ang HAVING. Napag-usapan namin siya. Ang kahilingan ay magiging eksaktong kapareho ng dati, kailangan mo lang idagdag ang kundisyon na ang bilang ng mga aklat ay nasa isang partikular na hanay. At gaya ng sinabi ko sa mga nakaraang artikulo, kapag kailangan nating mag-filter sa panahon ng pagpapangkat at/o sa mga function ng aggregation, kailangan nating gamitin ang HAVING : $ SELECT tch.last_name bilang 'Teacher', SUM(sbw.books) bilang 'Books' mula sa studentbook sbw INNER JOIN students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN teacher tch ON tch.id = stch.teacher_id GROUP BY tch.id MAY SUM(sbw.books) > 6 AT SUM(sbw.books) < 12 Na-highlight ko ang bahaging idinagdag ko. At, sa katunayan, ang inaasahang resulta: Tanging si Vasilenko ang pumasa sa pag-ikot na ito))

Gawain 12

Kundisyon: 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

Iyon ay, kailangan nating pagsamahin ang dalawang output, at ito mismo ang para sa UNION. Sa madaling salita, kukuha kami ng mga rekord mula sa mga mag-aaral at mula sa mga guro at magkakasamang magpi-print: $ PUMILI ng apelyido, 'guro' bilang uri mula sa guro UNION LAHAT piliin ang apelyido, 'mag-aaral' bilang uri mula sa mag-aaral ORDER BY apelyido; At magkakaroon ng parehong mga guro at mag-aaral. Tila ang lahat ay simple, ngunit ito ay kapag tinitingnan na natin ang resulta. At kaya kailangan mong hulaan ang dalawang bagay.

Gawain 13

Kundisyon: 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). ALTER TABLE student ADD CONSTRAINT check_rate CHECK (rate > 0 AT rate < 7); Dito kami ay nagdaragdag ng field sa pamamagitan ng ALTER TABLE at CHECK para itakda ang limitasyon sa field na ito mula 1 hanggang 6.

Gawain 14

Kundisyon: Ang item na ito ay hindi kinakailangan, ngunit magiging isang plus. Sumulat ng isang function na dadaan sa lahat ng 'Mga Aklat' at maglalabas ng lahat ng 'mga pamagat' na pinaghihiwalay ng mga kuwit. Dito kailangan mo lang magbalik ng linya bilang resulta ng kahilingan, na maglalaman ng lahat ng pamagat ng mga aklat. Dito na naman ako nag-google. Mayroong ganoong function - GROUP_CONCAT , kung saan ito ay ginagawa nang napakasimple: $ SELECT GROUP_CONCAT(title) mula sa libro; At iyon na...)) Lahat ng 14 na gawain ay handa na.

mga konklusyon

Uuhhh... Hindi naging madali. Ito ay kawili-wili. Sulit ang mga gawain, mas sigurado ako. Habang ginagawa namin ang mga gawaing ito, napag-usapan namin ang maraming bagay na hindi pa alam noon:

• SQL VIEW
• GROUP_CONCAT
• UNYON

at iba pa. Salamat sa lahat ng may lakas na basahin at ulitin ang ginawa ko. Sino ang nakakaalam kung paano gumawa ng mga kahilingan nang mas mahusay - sumulat sa mga komento, tiyak na babasahin ko ang mga ito)

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