Kita nganalisa database lan basa SQL. (Bagian 6 - Priksa tugas pungkasan) - "Proyek Jawa saka A nganti Z"

Artikel saka seri babagan nggawe proyek Jawa (pranala menyang materi liyane ana ing pungkasan). Tujuane kanggo nganalisa teknologi utama, asile nulis bot telegram. Bagian iki ngemot analisis tugas pungkasan ing basis data. Sugeng rawuh, para maos ingkang minulya. Dina iki kita bakal nganalisa tugas saka artikel pungkasan ing database. Iku menarik amarga iki dimaksudaké kanggo wawancara ing tingkat Menengah. Sing, sawise tugas iki sampeyan wis bisa pindhah kanggo Interview, lan sampeyan bakal kasil pass ing paling bagean apa hubungane karo database relasional. Aku ngerti carane perlu artikel iki bisa, lan mulane aku bakal sijine kabeh pengalaman kanggo nggawe migunani lan menarik. Lan yen sampeyan ora turu ing tengah artikel, tegese aku wis entuk target. Aku ora bakal mbaleni kabeh tugas: Aku bakal ngutip sadurunge ngrampungake saben tugas, underlining ing miring. Aku ngarepake saben wong sing maca artikel iki bakal mbukak kabeh pitakon ing database lan entuk perkara sing padha. Iki bakal nggawa keuntungan maksimal kanggo bisnis. Lan aku bakal luwih seneng amarga mikir yen aku nulungi wong ing tugas sing angel)

Latihan 1

Tulis skrip SQL kanggo nggawe tabel 'Siswa' kanthi kolom ing ngisor iki: id (kunci utama), jeneng, jeneng_paling, e_mail (unik). Kita wis nindakake iki, mula ora ana masalah. Ing skrip sampeyan kudu nemtokake kunci utama lan kolom unik sing beda karo sing utama. Kaping pisanan, ayo gawe database anyar kanggo tugas iki: $ GIPUN BASEDATA final_task; Lan ayo nggunakake database iki: $ USE final_task; Sawise lingkungan wis disetel lan siap kanggo mbukak proyek, kita bisa nulis skrip ing ngisor iki: $ CREATE TABLE siswa ( id INT AUTO_INCREMENT, jeneng VARCHAR (40), jeneng mburi VARCHAR (50), email VARCHAR (100), PRIMARY KEY ( id), UNIK (email) ); Nganti saiki, ora ana sing anyar dibandhingake karo sing wis kita lakoni. Sembarang komentar ora perlu, ayo nerusake.

Tugas 2-3

Tulis skrip SQL kanggo nggawe tabel 'Buku' kanthi kolom ing ngisor iki: id, judhul (id + judhul = kunci utama). Link 'Siswa' lan 'Buku' karo 'Siswa' siji-kanggo-akeh sesambetan 'Buku'. Ayo gabungke rong tugas dadi siji supaya luwih cepet lan luwih trep. Aku wis ngrembug babagan carane nambah kunci asing sing kapisah ing artikel sadurunge. Kanggo nambah, kita kudu ngelingi carane kita nggawe sambungan lan liwat apa. Artikel sakdurunge bakal mbantu sampeyan, banjur iki skrip: $ CREATE TABLE book ( id INT, judhul VARCHAR(100), student_id INT DEFAULT NULL, PRIMARY KEY (id, title), FOREIGN KEY (student_id) REFERENCES student (id ) ); Kanthi cara sing prasaja iki, kita nambahake kunci komposit kanggo tabel PRIMARY KEY (id, judhul) , saiki kuncine bakal persis pasangan. Iki tegese bisa ana luwih saka siji nilai kolom id sing padha ing tabel. Lan persis padha kanggo judhul.

Tugas 4

Tulis skrip SQL kanggo nggawe tabel 'Guru' kanthi kolom ing ngisor iki: id (kunci utama), jeneng, jeneng_paling, e_mail (unik), subyek. Kita terus nyiapake basis data kanggo pitakon, nggawe tabel guru: $ Nggawe TABLE guru ( id INT AUTO_INCREMENT, jeneng VARCHAR (30), jeneng mburi VARCHAR (30), email VARCHAR (100), subyek VARCHAR (40), KUNCI UTAMA ( id), UNIK (email) ); Nganti saiki ora angel, ta? Telung tugas wis rampung!

Tugas 5

Link 'Siswa' lan 'Guru' karo 'Siswa' akeh-kanggo-akeh Guru sesambetan. Saiki iki luwih menarik! We mung ngomong bab iki pungkasan. Ayo kula ngelingake sampeyan apa sing dibutuhake kanggo entuk iki: sampeyan kudu nggawe tabel penengah sing bakal nyimpen pasangan siswa-guru. Kanthi bantuan, bakal bisa nggawe hubungan akeh-kanggo-akeh. Mulane, ayo nggawe tabel students_x_techers . Pendekatan jeneng mbukak lan bisa uga: student_teacher . $ BUAT TABEL siswa_x_guru ( id_siswa INT NOT NULL, id_guru INT NOT NULL, KUNCI UTAMA (ID_siswa, id_guru), KUNCI ASING (ID_siswa) REFERENSI murid(id), KUNCI ASING (id_guru) REFERENSI guru(id) ); Kaya sing sampeyan ngerteni, kabeh wis rampung kanthi jelas lan koheren. Kita duwe kunci gabungan kanggo rong kunci asing: student_id lan teacher_id. Kenapa uga kunci manca? Supaya kita bisa yakin manawa cathetan ing tabel siswa lan guru ana kanggo pasangan sing dicathet.

Tugas 6

Pilih 'Siswa' sing duwe 'oro' ing jeneng mburi, contone 'Sidorov', 'Voronovsky'. Kanggo nggawe menarik lan visual kanggo kita, Aku suggest nambah sawetara siswa pisanan, supaya sawetara wong cocok kanggo panjalukan iki, lan sawetara ora. Mulane, ayo nulis sing kudu dilebokake minangka asil panyuwunan: $ INSERT INTO siswa (jeneng, jeneng_pungkasan, email) NILAI ('Ivan', 'Sidorov', 'ivan.sidorov@gmail.com'); $ INSERT INTO siswa (jeneng, last_name, email) NILAI ('Nikolay', 'Voronovsky', 'nikolay.voronovsky@gmail.com'); Lan sing ora kudu mlebu: $ INSERT INTO mahasiswa (jeneng, last_name, email) NILAI ('Romawi', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO student (name, last_name, email) VALUES('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); Priksa asile, deleng dhaptar data ing tabel siswa: $ SELECT * FROM siswa; lan kita njaluk: Ana papat cathetan ing total, loro mau kudu pas, lan loro ora. Sawise nyiapake kabeh data kanggo panyuwunan, kita bisa nggawe panjalukan kanggo tugas kasebut dhewe: $ SELECT * FROM student WHERE last_name LIKE '%oro%'; Akibaté, Ivan lan Nikolai tindak liwat dhaftar.

Tugas 7

Tugas sabanjure, kita maca: Pilih saka tabel 'Siswa' kabeh jeneng mburi ('last_name') lan nomer repetitions sing. Coba sing ana namesakes ing database. Urut miturut jumlah ing urutan mudhun. Iku kudu katon kaya iki:

jeneng_pungkasan	jumlah
Petrov	15
Ivanov	12
Sidorov	3

Kanggo gamblang, sampeyan kudu nambah data liyane. Tanpa basa-basi, ayo nambahake Petrovs, Ivanovs lan Sidorovs, sing ora ngerti kekerabatane;) Aku ora bakal nggawe alamat email, aku mung bakal ngilangi saka entri anyar. Ayo mbukak printah ing ngisor iki kaping 12: $ INSERT INTO student (jeneng, last_name) VALUES ('Ivan', 'Ivanov'); Ayo nambah 15 Petrovs: $ INSERT INTO mahasiswa (jeneng, last_name) NILAI ('Petr', 'Petrov'); Lan loro Sidorovs (kita wis duwe siji)): $ INSERT INTO mahasiswa (jeneng, last_name) NILAI ('Sidor', 'Sidorov'); Data saiki wis siyap. Kanggo entuk data kasebut, sampeyan kudu nggawe pengelompokan; kanggo nindakake klompok, sampeyan kudu nggunakake operator Grup Miturut, lan sampeyan kudu nindakake iki kanthi kolom last_name. Sampeyan uga bisa sok dong mirsani sing nomer repetitions ditetepake minangka quantity , lan kene sampeyan uga kudu ngelingi carane nggawe alias ing SQL: $ SELECT last_name, COUNT(*) minangka jumlah saka siswa GROUP BY last_name ORDER BY COUNT(*) DESC ; Dadi aku overdid karo Petrovs - ternyata 16))

Tugas 8

Kahanan: Pilih 3 jeneng sing paling sering diulang saka 'Mahasiswa'. Urut miturut jumlah ing urutan mudhun. Iku kudu katon kaya iki:

jeneng	jumlah
Alexander	27
Sergey	10
Petrus	7

Oh, kanggo maksud iki kita wis duwe Ivans, Peters lan Sidors. Mulane ora perlu ditambahake. Kita wis ngerti carane ngurutake. Siji-sijine perkara sing ora dirembug dina iki yaiku carane milih sawetara rekaman. Iki wis katon ing solusi sadurunge kanggo masalah database. Kanggo sing durung maca, maca. Kanggo liyane, ayo langsung menyang titik: $ PILIH jeneng, COUNT (*) minangka jumlah saka siswa GROUP Miturut jeneng ORDER BY COUNT (*) DESC LIMIT 3; Kaya sing bisa dideleng saka pitakon, yen sampeyan ngerti urutan operator ing pitakon SELECT, ora bakal ana masalah karo nglakokake pitakon kasebut. Lan tugas iki isih ana kanggo kita. Lan kawruh sing diwenehi sadurunge cukup kanggo ngatasi masalah iki.

Tugas 9

Kahanan tugas: Pilih 'Siswa' sing paling akeh 'Buku' lan 'Guru' sing gegandhengan. Urut miturut jumlah ing urutan mudhun. Iku kudu katon kaya iki:

Jeneng mburi guru	Jeneng_lase siswa	Jumlah buku
Petrov	Sidorov	7
Ivanov	Smith	5
Petrov	Kankava	2

Dadi, tugas iki jelas luwih angel tinimbang sing sadurunge, ta? Ora nggumunake: ambune kaya gabung ... lan luwih saka siji) Kaping pisanan, kita kudu ngerti apa sing kudu ditindakake ... Cetha yen jumlah Buku mbutuhake pengelompokan. Nanging apa? Lan kenapa kita kudu klompok? Pitakonan kalebu telung tabel, pangelompokan, lan pangurutan. Dideleng saka kasunyatan manawa cathetan sing ora ana buku ora ditampilake, tegese sampeyan kudu njupuk INNER JOIN. Kita uga bakal njaluk panjaluk LEFT JOIN supaya ora ana masalah. Lan ana sawetara opsi. Wangsulan: Bab ingkang pisanan kita tindakake iku nggabungake telung tabel dadi siji rekaman. Sabanjure, kita klompok miturut siswa lan nambahake jeneng guru kasebut. Apa sing bakal kita pilih? Jeneng guru, murid lan nomer buku. Ayo nambah data kanggo panyuwunan:

• guru telu;
• sepuluh buku;
• nyambungake loro siswa karo telung guru.

Guru telu

$ INSERT INTO guru(last_name) NILAI ('Matvienko'); $ INSERT INTO guru(last_name) NILAI ('Shevchenko'); $ INSERT INTO guru(last_name) NILAI ('Vasilenko');

10 buku

Aku bakal njupuk ID siswa 1 lan 2. Aku bakal masang buku kanggo wong-wong mau. Wiwit AUTO_INCREMENT ora disetel, supaya ora nulis ID anyar saben wektu, sampeyan kudu nindakake ing ngisor iki: $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; Sabanjure, tambahake buku kanggo siswa pisanan: $ INSERT INTO book (judhul, student_id) VALUES('book1', 1); $ INSERT INTO book (judul, id_siswa) VALUES('buku2', 1); $ INSERT INTO book (judul, id_siswa) VALUES('book3', 1); $ INSERT INTO book (judhul, id_siswa) VALUES('book4', 1); $ INSERT INTO book (judhul, id_siswa) VALUES('buku5', 1); $ INSERT INTO book (judul, id_siswa) VALUES('buku6', 1); Lan buku kanggo siswa kapindho: $ INSERT INTO buku (judhul, siswa_id) VALUES('book7', 2); $ INSERT INTO book (judul, id_siswa) VALUES('buku8', 2); $ INSERT INTO book (judul, id_siswa) VALUES('buku9', 2); $ INSERT INTO book (judul, id_siswa) VALUES('buku10', 2);

Hubungan guru-murid

Kanggo nindakake iki, nambah students_x_teachers menyang meja: $ INSERT INTO students_x_teachers NILAI (1,1); $INSERT INTO students_x_teachers VALUES(1,2); $INSERT INTO students_x_teachers VALUES(2,3);

Ayo ngleksanakake panjaluk kasebut

Kita nindakake tahap pisanan - kita nyambungake telung tabel dadi siji rekaman: $ PILIH * DARI guru tch BATIN GABUNG students_x_teachers st_x_tch ON tch.id = st_x_tch.teacher_id BATIN GABUNG murid st ON st_x_tch.student_id = st.id INNER GABUNG buku b ON st .id = b.id_siswa; Mesthi, kita durung duwe cathetan, nanging kita bisa ndeleng manawa kita wis kasil nyambungake telung tabel. Saiki kita nambahake klompok buku, ngurutake lan kolom sing kita butuhake: $ PILIH tch.last_name, st.last_name, st.id, COUNT(*) minangka buku FROM student st INNER JOIN book b ON st.id = b.student_id INNER GABUNG siswa_x_guru st_x_tch ON st.id = st_x_tch.id_siswa BATIN GABUNG guru tch ON tch.id = st_x_tch.id_guru GROUP BY st.id ORDER BY buku DESC; Nanging kita entuk kesalahan ing SQL lan jawaban ing ngisor iki: Kode Kesalahan: 1055. Ekspresi # 1 saka dhaptar SELECT ora ana ing klausa GROUP BY lan ngemot kolom 'final_task.tch.last_name' sing ora anaggregasi sing ora gumantung ing kolom ing GROUP BY. klausa Ora bisa njupuk unsur-unsur kasebut amarga ana hubungan akeh-kanggo-akeh antarane guru lan siswa. Lan iku bener: kita ora bisa njaluk mung siji guru saben murid. Dadi ayo pindhah dalan liyane. Ayo nggunakake soko disebut View SQL. Apa idea: kita nggawe tampilan kapisah, kang Tabel anyar, wis karo klompok kita kudu. Lan ing tabel iki kita bakal nambah jeneng guru sing dibutuhake. Nanging kita nganggep kasunyatan manawa ana luwih saka siji guru, mula entri kasebut bakal diulang maneh. Nggawe tampilan: $ Nggawe VIEW studentBooks minangka PILIH st.last_name,st.id,COUNT(*) minangka buku saka siswa st INNER JOIN buku b ON st.id=b.student_id GROUP BY st.id ORDER BY buku DESC; Sabanjure, kita nggarap tampilan iki minangka tabel prasaja sing nduweni telung kolom: jeneng_paling siswa, id_murid lan jumlah buku. Adhedhasar ID siswa, kita uga bisa nambah guru liwat rong gabung: $ PILIH tch.last_name minangka 'Guru', sbw.last_name 'Siswa', sbw.books minangka 'Buku' saka studentbook sbw INNER JOIN students_x_teachers stch ON sbw. id = stch.id_siswa BATIN GABUNG guru tch ON tch.id = stch.id_guru; Lan saiki asil bakal: Huh! Iki panyuwunan, ya?) Pranyata kaya sing dikarepake: siswa sing duwe id=1 duwe buku enem lan guru loro, lan siswa sing duwe id=2 duwe buku papat lan guru siji.

Tugas 10

Kahanan: Pilih 'Guru' sing duwe 'Buku' paling akeh ing antarane kabeh 'Siswa'. Urut miturut jumlah ing urutan mudhun. Iku kudu katon kaya iki:

Jeneng mburi guru	Jumlah buku
Petrov	9
Ivanov	5

Ing kene kita bisa nggunakake panjalukan sing wis siap saka tugas sadurunge. Apa sing kudu kita ganti babagan iki? Kita wis duwe data iki, kita mung kudu nambah klompok liyane lan mbusak jeneng mahasiswa saka data output. Nanging luwih dhisik, ayo nambah siji siswa maneh kanggo guru supaya asile luwih menarik. Kanggo nindakake iki, kita nulis: $ INSERT INTO students_x_teachers VALUES (2, 1); Lan pitakon dhewe: $ SELECT tch.last_name minangka 'Guru', SUM(sbw.books) minangka 'Buku' saka buku siswa sbw INNER GABUNG students_x_teachers stch ON sbw.id = stch.student_id INNER GUBUNGI guru tch ON tch.id = stch .id_guru GROUP BY tch.id; Akibaté, kita entuk: guru Vasilenko duwe 10 buku, lan Shevchenko duwe 6 ...)

Tugas 11

Kahanan: Pilih 'Guru' sing jumlah 'Buku' kanggo kabeh 'Siswa' ana antarane 7 nganti 11. Urut miturut jumlah ing urutan mudhun. Iku kudu katon kaya iki:

Jeneng mburi guru	Jumlah buku
Petrov	sewelas
Sidorov	9
Ivanov	7

Iki ngendi kita bakal nggunakake HAVING. Kita ngomong babagan dheweke. Panyuwunan bakal persis padha karo sadurunge, mung sampeyan kudu nambah syarat yen jumlah buku bakal ana ing sawetara tartamtu. Lan kaya sing dakkandhakake ing artikel sadurunge, nalika kita kudu nyaring sajrone nglompokake lan / utawa ing fungsi agregasi, kita kudu nggunakake HAVING : $ SELECT tch.last_name minangka 'Guru', SUM(sbw.books) minangka 'Buku' saka buku siswa sbw BATIN GABUNG siswa_x_guru stch ON sbw.id = stch.id_siswa BATIN GUBUNG guru tch ON tch.id = stch.id_guru GROUP DEMI tch.id NDUWE SUM(sbw.books) > 6 LAN SUM(sbw.books;) < 12 Aku wis nyorot bagean sing ditambahake. Lan, nyatane, asil sing dikarepake: Mung Vasilenko sing ngliwati babak iki))

Tugas 12

Kahanan: Cetak kabeh 'jeneng_last' lan 'jeneng' kabeh 'Guru' lan 'Siswa' kanthi lapangan 'jinis' (murid utawa guru). Urut miturut abjad miturut 'last_name'. Iku kudu katon kaya iki:

jeneng_pungkasan	jinis
Ivanov	murid
Kankava	guru
Smith	murid
Sidorov	guru
Petrov	guru

Yaiku, kita kudu nggabungake rong output, lan iki pancen UNION. Ing tembung liyane, kita bakal njupuk cathetan saka siswa lan saka guru lan print bebarengan: $ SELECT last_name, 'guru' minangka jinis saka guru UNION ALL pilih last_name, 'murid' minangka jinis saka mahasiswa ORDER BY last_name; Lan bakal ana guru lan murid. Iku bakal katon yen kabeh iku prasaja, nanging iki nalika kita wis katon ing asil. Dadi sampeyan kudu ngira rong perkara.

Tugas 13

Kahanan: Tambah kolom 'tingkat' menyang tabel 'Siswa' sing wis ana, sing bakal nyimpen kursus sing saiki ditindakake siswa (nilai angka saka 1 nganti 6). ALTER TABLE siswa ADD CONSTRAINT check_rate CHECK (rate > 0 AND rate < 7); Ing kene kita nambahake lapangan liwat ALTER TABLE lan Mriksa kanggo nyetel watesan ing lapangan iki saka 1 nganti 6.

Tugas 14

Kondisi: Item iki ora dibutuhake, nanging bakal dadi plus. Tulis fungsi sing bakal mbukak kabeh 'Buku' lan output kabeh 'judhul' sing dipisahake karo koma. Kene sampeyan mung kudu bali baris minangka asil saka panjalukan, kang bakal ngemot kabeh judhul buku. Kene maneh aku kudu google. Ana fungsi kuwi - GROUP_CONCAT , karo kang iki rampung banget prasaja: $ SELECT GROUP_CONCAT(judhul) saka buku; Lan iku ...)) Kabeh 14 tugas wis siyap.

kesimpulan

Uuhhh... Ora gampang. Iku menarik. Tugas-tugas iku worth iku, aku luwih yakin. Nalika nindakake tugas-tugas kasebut, akeh perkara sing durung dingerteni sadurunge:

• SQL VIEW
• GROUP_CONCAT
• UNION

lan liya-liyane. Matur nuwun kanggo kabeh sing duwe kekuwatan maca lan mbaleni apa sing daklakoni. Sapa sing ngerti carane nggawe panjalukan luwih apik - tulis ing komentar, aku mesthi bakal maca)

Dhaptar kabeh materi ing seri kasebut ana ing wiwitan artikel iki.