Kami menganalisis pangkalan data dan bahasa SQL. (Bahagian 6 - Menyemak tugasan akhir) - "Projek Java dari A hingga Z"

Artikel daripada siri tentang mencipta projek Java (pautan ke bahan lain ada di penghujung). Matlamatnya adalah untuk menganalisis teknologi utama, hasilnya menulis bot telegram. Bahagian ini mengandungi analisis tugas akhir pada pangkalan data. Salam sejahtera, pembaca budiman. Hari ini kita akan menganalisis tugas dari artikel terakhir pada pangkalan data. Ia menarik kerana ia bertujuan untuk temuduga di peringkat Pertengahan. Iaitu, selepas tugas ini anda sudah boleh pergi untuk temu duga, dan anda akan berjaya lulus sekurang-kurangnya sebahagian daripada perkara yang berkaitan dengan pangkalan data hubungan. Saya tahu betapa perlunya artikel ini, dan oleh itu saya akan meletakkan semua pengalaman saya untuk menjadikannya berguna dan menarik. Dan jika anda tidak tertidur di tengah-tengah artikel, ini bermakna saya mencapai matlamat saya. Saya tidak akan mengulangi keseluruhan tugasan: Saya akan memetiknya sebelum menyelesaikan setiap tugasan, menggariskannya dalam huruf condong. Saya menjangkakan bahawa semua orang yang membaca artikel ini akan menjalankan semua pertanyaan dalam pangkalan data mereka dan mendapat perkara yang sama. Ini akan membawa faedah maksimum kepada perniagaan. Dan saya akan menjadi sedikit lebih gembira daripada pemikiran bahawa saya telah membantu seseorang dalam tugas sukar kami)

Latihan 1

Tulis skrip SQL untuk mencipta jadual 'Pelajar' dengan medan berikut: id (kunci utama), nama, nama belakang, e-mel (unik). Kami telah melakukan ini, jadi tidak sepatutnya ada sebarang masalah. Dalam skrip anda perlu menentukan kunci utama dan medan unik yang berbeza daripada yang utama. Mula-mula, mari kita cipta pangkalan data baharu untuk tugasan ini: $ CREATE DATABASE final_task; Dan mari kita gunakan pangkalan data ini: $ USE final_task; Setelah persekitaran disediakan dan bersedia untuk menjalankan kerja, kita boleh menulis skrip berikut: $ CREATE TABLE pelajar ( id INT AUTO_INCREMENT, name VARCHAR(40), last_name VARCHAR(50), e-mel VARCHAR(100), PRIMARY KEY ( id), UNIK (e-mel ) ); Setakat ini, tiada apa yang baru berbanding apa yang telah kita lalui. Sebarang komen yang tidak perlu, mari kita teruskan.

Tugasan 2-3

Tulis skrip SQL untuk mencipta jadual 'Buku' dengan medan berikut: id, tajuk (id + tajuk = kunci utama). Pautkan 'Pelajar' dan 'Buku' dengan perhubungan 'Buku' 'Pelajar' satu-ke-banyak. Mari gabungkan dua tugasan menjadi satu untuk menjadikannya lebih cepat dan lebih mudah. Saya telah membincangkan cara menambah kunci asing yang berasingan dalam artikel sebelumnya. Untuk menambah, kita perlu ingat bagaimana kita membuat sambungan dan melalui apa. Artikel sebelumnya akan membantu anda, dan kemudian berikut ialah skrip: $ CREATE TABLE book ( id INT, tajuk VARCHAR(100), student_id INT DEFAULT NULL, PRIMARY KEY (id, title), FOREIGN KEY (student_id) RUJUKAN pelajar (id ) ); Dengan cara mudah ini, kami menambah kunci komposit untuk KUNCI UTAMA jadual kami (id, tajuk) , kini kuncinya ialah tepat pada pasangan. Ini bermakna terdapat lebih daripada satu nilai medan id yang sama dalam jadual. Dan betul-betul sama untuk tajuk.

Tugasan 4

Tulis skrip SQL untuk mencipta jadual 'Guru' dengan medan berikut: id (kunci utama), nama, nama belakang, e_mel (unik), subjek. Kami terus menyediakan pangkalan data kami untuk pertanyaan, buat jadual guru: $ CREATE TABLE cikgu( id INT AUTO_INCREMENT, name VARCHAR(30), last_name VARCHAR(30), email VARCHAR(100), subject VARCHAR(40), PRIMARY KEY ( id), UNIK (e-mel) ); Setakat ni tak susah kan? Tiga tugas sudah selesai!

Tugasan 5

Pautkan 'Pelajar' dan 'Guru' dengan hubungan 'Pelajar' ramai-ke-banyak Guru. Sekarang ini lebih menarik! Kami baru sahaja bercakap tentang ini kali terakhir. Biar saya ingatkan anda perkara yang diperlukan untuk mencapai ini: anda perlu mencipta jadual perantaraan yang akan menyimpan pasangan pelajar-guru. Dengan bantuannya, anda boleh mewujudkan perhubungan ramai-ke-banyak. Oleh itu, mari kita buat jadual students_x_techers . Pendekatan penamaan adalah terbuka dan boleh juga: student_teacher . $ CIPTA JADUAL pelajar_x_guru ( id_pelajar INT BUKAN NULL, id_guru INT BUKAN NULL, KUNCI UTAMA (id_pelajar, id_guru), KUNCI ASING (id_murid) RUJUKAN murid(id), KUNCI ASING (id_guru) RUJUKAN guru(id) ); Seperti yang anda lihat, semuanya dilakukan dengan jelas dan koheren. Kami mempunyai kunci komposit untuk dua kunci asing: student_id dan teacher_id. Mengapa juga kunci asing? Bagi memastikan rekod dalam jadual pelajar dan guru wujud untuk pasangan yang direkodkan.

Tugasan 6

Pilih 'Pelajar' yang mempunyai 'oro' dalam nama keluarga mereka, contohnya 'Sidorov', 'Voronovsky'. Untuk menjadikannya menarik dan visual untuk kami, saya cadangkan untuk menambah beberapa pelajar terlebih dahulu, supaya sesetengah daripada mereka sesuai untuk permintaan ini, dan ada yang tidak. Oleh itu, mari kita catatkan mereka yang harus disertakan sebagai hasil permintaan: $ INSERT INTO student (name, last_name, email) NILAI ('Ivan', 'Sidorov', 'ivan.sidorov@gmail.com'); $ INSERT INTO student (name, last_name, e-mel) NILAI ('Nikolay', 'Voronovsky', 'nikolay.voronovsky@gmail.com'); Dan mereka yang tidak sepatutnya masuk: $ INSERT INTO student (nama, last_name, email) NILAI ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO student (name, last_name, email) VALUES('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); Mari semak keputusan, lihat senarai data dalam jadual pelajar: $ PILIH * DARI pelajar; dan kami dapat: Terdapat empat rekod secara keseluruhan, dua daripadanya patut muat, dan dua tidak patut. Setelah menyediakan semua data untuk permintaan itu, kami boleh membuat permintaan untuk tugas itu sendiri: $ SELECT * FROM student WHERE last_name LIKE '%oro%'; Akibatnya, Ivan dan Nikolai melalui senarai itu.

Tugasan 7

Tugas seterusnya, kita baca: Pilih daripada jadual 'Pelajar' semua nama keluarga ('last_name') dan bilangan ulangan mereka. Pertimbangkan bahawa terdapat nama dalam pangkalan data. Isih mengikut kuantiti dalam tertib menurun. Ia sepatutnya kelihatan seperti ini:

nama terakhir	kuantiti
Petrov	15
Ivanov	12
Sidorov	3

Untuk kejelasan, anda perlu menambah lebih banyak data. Tanpa berlengah lagi, mari kita tambah Petrov, Ivanov dan Sidorov, yang tidak tahu persaudaraan mereka ;) Saya tidak akan mencipta alamat e-mel, saya hanya akan mengecualikan ia daripada entri baru. Mari jalankan arahan berikut 12 kali: $ INSERT INTO student (name, last_name) VALUES ('Ivan', 'Ivanov'); Mari tambah 15 Petrov: $ INSERT INTO student (name, last_name) NILAI ('Petr', 'Petrov'); Dan dua Sidorov (kami sudah ada satu))): $ INSERT INTO student (name, last_name) NILAI ('Sidor', 'Sidorov'); Sekarang data sudah siap. Untuk mendapatkan data sedemikian, anda perlu melakukan pengelompokan; untuk melakukan pengelompokan, anda perlu menggunakan operator Kumpulan Mengikut, dan anda perlu melakukannya dengan medan nama akhir. Anda juga boleh melihat bahawa bilangan ulangan ditetapkan sebagai kuantiti , dan di sini anda juga perlu ingat cara membuat alias dalam SQL: $ SELECT last_name, COUNT(*) sebagai kuantiti DARI KUMPULAN pelajar OLEH nama akhir ORDER BY COUNT(*) DESC ; Jadi saya berlebihan dengan Petrovs - ternyata 16))

Tugasan 8

Syarat: Pilih 3 nama teratas yang paling kerap berulang daripada 'Pelajar'. Isih mengikut kuantiti dalam tertib menurun. Ia sepatutnya kelihatan seperti ini:

nama	kuantiti
Alexander	27
Sergey	10
Peter	7

Oh, untuk tujuan ini kita sudah mempunyai Ivans, Peters dan Sidors. Oleh itu, tidak perlu menambahnya. Kami sudah tahu cara menyusun. Satu-satunya perkara yang kita tidak bercakap tentang hari ini ialah cara memilih bilangan rekod tertentu. Ini telah pun muncul dalam penyelesaian sebelumnya untuk masalah pangkalan data. Bagi yang belum membacanya, bacalah. Untuk selebihnya, mari kita terus ke intinya: $ PILIH nama, COUNT(*) sebagai kuantiti DARI KUMPULAN pelajar MENGIKUT nama ORDER BY COUNT(*) DESC LIMIT 3; Seperti yang dapat dilihat daripada pertanyaan, jika anda mengetahui susunan pengendali dalam pertanyaan SELECT, tidak akan ada masalah dengan melaksanakan pertanyaan sedemikian. Dan tugas ini masih terpulang kepada kita. Dan ilmu yang disampaikan tadi cukup memadai untuk menyelesaikan masalah ini.

Tugasan 9

Syarat tugas: Pilih 'Pelajar' yang mempunyai bilangan terbesar 'Buku' dan 'Guru' yang berkaitan. Isih mengikut kuantiti dalam tertib menurun. Ia sepatutnya kelihatan seperti ini:

Nama keluarga_guru	Nama belakang pelajar	Kuantiti buku
Petrov	Sidorov	7
Ivanov	Smith	5
Petrov	Kankava	2

Jadi, tugas ini jelas lebih sukar daripada yang sebelumnya, bukan? Tidak hairanlah: ia berbau seperti bergabung... dan lebih daripada satu) Pertama, kita perlu memahami apa yang perlu dilakukan... Jelas sekali bahawa kuantiti Buku memerlukan pengelompokan. Tapi apa? Dan mengapa kita harus mengumpulkan mereka? Pertanyaan melibatkan tiga jadual, pengumpulan dan pengisihan. Berdasarkan fakta bahawa rekod di mana tiada buku tidak ditunjukkan, bermakna anda perlu mengambil INNER JOIN. Kami juga akan membuat permintaan untuk LEFT JOIN supaya tiada masalah dengan ini. Dan terdapat beberapa pilihan. Perkara pertama yang kita lakukan ialah menggabungkan tiga jadual menjadi satu rekod. Seterusnya, kami mengumpulkan mengikut pelajar dan menambah nama guru padanya. Apa yang akan kita pilih? Nama guru, murid dan bilangan buku. Mari tambah data untuk permintaan:

• tiga orang guru;
• sepuluh buku;
• menghubungkan dua orang murid dengan tiga orang guru.

Tiga orang guru

$ INSERT IN TO teacher(last_name) NILAI ('Matvienko'); $ INSERT IN TO teacher(last_name) NILAI ('Shevchenko'); $ INSERT IN TO teacher(last_name) NILAI ('Vasilenko');

10 buah buku

Saya akan mengambil ID pelajar pertama dan kedua. Saya akan melampirkan buku kepada mereka. Memandangkan AUTO_INCREMENT tidak ditetapkan, untuk tidak menulis ID baharu setiap kali, anda perlu melakukan perkara berikut: $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; Seterusnya, tambahkan buku untuk pelajar pertama: $ INSERT INTO book (tajuk, student_id) VALUES('book1', 1); $ INSERT INTO book (tajuk, student_id) VALUES('book2', 1); $ INSERT INTO book (tajuk, student_id) NILAI('book3', 1); $ INSERT INTO book (tajuk, student_id) NILAI('book4', 1); $ INSERT INTO book (tajuk, student_id) NILAI('book5', 1); $ INSERT INTO book (tajuk, student_id) NILAI('book6', 1); Dan buku untuk pelajar kedua: $ INSERT INTO book (tajuk, student_id) VALUES('book7', 2); $ INSERT INTO book (tajuk, student_id) NILAI('book8', 2); $ INSERT INTO book (tajuk, student_id) NILAI('book9', 2); $ INSERT INTO book (tajuk, student_id) NILAI('book10', 2);

Hubungan guru-murid

Untuk melakukan ini, tambahkan students_x_teachers pada jadual: $ INSERT INTO students_x_teachers NILAI (1,1); $INSERT INTO students_x_teachers VALUES(1,2); $INSERT INTO students_x_teachers VALUES(2,3);

Mari kita laksanakan permintaan itu

Kami melakukan peringkat pertama - kami memautkan tiga jadual ke dalam satu rekod: $ PILIH * DARI cikgu 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 buku b ON st .id = b.id_pelajar; Sudah tentu, kami tidak mempunyai sebarang rekod lagi, namun, kami dapat melihat bahawa kami telah berjaya menyambungkan tiga jadual. Sekarang kami menambah kumpulan buku, pengisihan dan medan yang kami perlukan: $ PILIH tch.last_name, st.last_name, st.id, COUNT(*) sebagai buku DARI student st INNER JOIN book b ON st.id = b.student_id INNER SERTAI students_x_teachers st_x_tch ON st.id = st_x_tch.student_id DALAM SERTAI cikgu tch ON tch.id = st_x_tch.teacher_id KUMPULAN OLEH st.id ORDER BY books DESC; Tetapi kami mendapat ralat dalam SQL dan jawapan berikut: Kod Ralat: 1055. Ungkapan #1 senarai SELECT tiada dalam klausa GROUP BY dan mengandungi lajur 'final_task.tch.last_name' tanpa teragregat yang tidak bergantung secara fungsi pada lajur dalam GROUP BY klausa Ia tidak berfungsi mengambil elemen ini kerana terdapat hubungan banyak-ke-banyak antara guru dan pelajar. Dan memang benar: kita tidak boleh mendapatkan seorang guru sahaja bagi setiap pelajar. Jadi mari kita pergi ke arah lain. Mari gunakan sesuatu yang dipanggil View SQL. Apakah ideanya: kami mencipta pandangan yang berasingan, iaitu jadual baharu, sudah dengan kumpulan yang kami perlukan. Dan ke jadual ini kami akan menambah nama guru yang diperlukan. Tetapi kami mengambil kira hakikat bahawa mungkin terdapat lebih daripada seorang guru, jadi penyertaan akan diulang. Cipta paparan: $ CREATE VIEW studentBooks as SELECT st.last_name,st.id,COUNT(*) as books DARI student st INNER JOIN buku b DI st.id=b.student_id KUMPULAN OLEH st.id ORDER BY books DESC; Seterusnya, kami menggunakan paparan ini sebagai jadual ringkas yang mempunyai tiga medan: nama akhir pelajar, id_pelajar dan kiraan buku. Mengikut ID pelajar, kami juga boleh menambah guru melalui dua gabungan: $ PILIH tch.last_name sebagai 'Guru', sbw.last_name 'Pelajar', sbw.books sebagai 'Buku' daripada studentbook sbw DALAM JOIN students_x_teachers stch ON sbw. id = stch.id_pelajar DALAM SERTAI guru tch DI PADA tch.id = stch.teacher_id; Dan sekarang hasilnya akan menjadi: Huh! Ini adalah permintaan, bukan?) Ternyata seperti yang dijangkakan: pelajar dengan id=1 mempunyai enam buku dan dua guru, dan pelajar dengan id=2 mempunyai empat buku dan seorang guru.

Tugasan 10

Syarat: Pilih 'Guru' yang mempunyai bilangan 'Buku' terbanyak di antara semua 'Pelajar'nya. Isih mengikut kuantiti dalam tertib menurun. Ia sepatutnya kelihatan seperti ini:

Nama keluarga_guru	Kuantiti buku
Petrov	9
Ivanov	5

Di sini kita boleh menggunakan permintaan sedia dari tugas sebelumnya. Apa yang perlu kita ubah mengenainya? Kami sudah mempunyai data ini, kami hanya perlu menambah kumpulan lain dan mengalih keluar nama pelajar daripada data output. Tetapi pertama, mari tambah seorang lagi pelajar kepada guru untuk menjadikan keputusan lebih menarik. Untuk melakukan ini, kami menulis: $ INSERT INTO students_x_teachers NILAI (2, 1); Dan pertanyaan itu sendiri: $ SELECT tch.last_name sebagai 'Guru', SUM(sbw.books) sebagai 'Books' daripada studentbook sbw INNER JOIN students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN teacher tch ON tch.id = stch id_guru KUMPULAN OLEH tch.id; Hasilnya, kita dapat: guru Vasilenko mempunyai 10 buku, dan Shevchenko mempunyai 6...)

Tugasan 11

Syarat: Pilih 'Guru' yang bilangan 'Buku' untuk semua 'Pelajar'nya adalah antara 7 dan 11. Isih mengikut kuantiti dalam tertib menurun. Ia sepatutnya kelihatan seperti ini:

Nama keluarga_guru	Kuantiti buku
Petrov	sebelas
Sidorov	9
Ivanov	7

Di sinilah kita akan menggunakan HAVING. Kami bercakap tentang dia. Permintaan akan sama seperti sebelumnya, cuma anda perlu menambah syarat bilangan buku berada dalam julat tertentu. Dan seperti yang saya katakan dalam artikel sebelum ini, apabila kita perlu melakukan penapisan semasa pengumpulan dan/atau pada fungsi pengagregatan, kita perlu menggunakan HAVING : $ SELECT tch.last_name sebagai 'Guru', SUM(sbw.books) sebagai 'Buku' daripada buku pelajar sbw SERTAI BATIN pelajar_x_guru stch HIDUP sbw.id = stch.id_pelajar DALAM SERTAI guru tch PADA tch.id = stch.teacher_id KUMPULAN OLEH tch.id MEMPUNYAI JUMLAH(sbw.buku) > 6 DAN JUMLAH(sbw.buku) < 12 Saya telah menyerlahkan bahagian yang saya tambah. Dan, sebenarnya, hasil yang diharapkan: Hanya Vasilenko yang lulus pusingan ini))

Tugasan 12

Syarat: Cetak semua 'nama_akhir' dan 'nama' semua 'Guru' dan 'Pelajar' dengan 'jenis' medan (pelajar atau guru). Isih mengikut abjad mengikut 'last_name'. Ia sepatutnya kelihatan seperti ini:

nama terakhir	menaip
Ivanov	pelajar
Kankava	cikgu
Smith	pelajar
Sidorov	cikgu
Petrov	cikgu

Iaitu, kita perlu menggabungkan dua output, dan ini adalah tujuan UNION. Dalam erti kata lain, kami akan mengambil rekod daripada pelajar dan daripada guru dan mencetak bersama-sama: $ SELECT last_name, 'teacher' as type from teacher UNION ALL pilih last_name, 'student' as type from student ORDER BY last_name; Dan akan ada kedua-dua guru dan pelajar. Nampaknya semuanya mudah, tetapi ini adalah apabila kita sudah melihat hasilnya. Jadi anda perlu meneka dua perkara.

Tugasan 13

Syarat: Tambahkan lajur 'kadar' pada jadual 'Pelajar' sedia ada, yang akan menyimpan kursus yang pelajar sedang pelajari (nilai angka dari 1 hingga 6). ALTER JADUAL pelajar TAMBAH KEKANGAN check_rate SEMAK (kadar > 0 DAN kadar < 7); Di sini kami menambah medan melalui ALTER TABLE dan CHECK untuk menetapkan had pada medan ini dari 1 hingga 6.

Tugasan 14

Keadaan: Item ini tidak diperlukan, tetapi akan menjadi tambahan. Tulis fungsi yang akan melalui semua 'Buku' dan mencetak semua 'tajuk' yang dipisahkan dengan koma. Di sini anda hanya perlu mengembalikan baris sebagai hasil permintaan, yang akan mengandungi semua tajuk buku. Di sini sekali lagi saya terpaksa google. Terdapat fungsi sedemikian - GROUP_CONCAT , yang dengannya ini dilakukan dengan mudah: $ SELECT GROUP_CONCAT(title) daripada buku; Dan itu sahaja...)) Kesemua 14 tugasan sudah sedia.

kesimpulan

Uuhhh... Ia tidak mudah. Ia adalah menarik. Tugasan itu berbaloi, saya lebih pasti. Semasa kami melakukan tugasan ini, kami membincangkan banyak perkara yang tidak diketahui sebelum ini:

• PANDANGAN SQL
• GROUP_CONCAT
• KESATUAN

dan sebagainya. Terima kasih kepada semua orang yang mempunyai kekuatan untuk membaca dan mengulangi apa yang saya lakukan. Siapa yang tahu cara membuat permintaan dengan lebih baik - tulis dalam komen, saya pasti akan membacanya)

Senarai semua bahan dalam siri ini adalah pada permulaan artikel ini.