Kami menganalisis database dan bahasa SQL. (Bagian 4 - memeriksa pekerjaan rumah) - "Proyek Java dari A sampai Z"

Artikel dari seri tentang membuat proyek Java (tautan ke materi lain ada di akhir). Tujuannya adalah untuk menganalisis teknologi utama, hasilnya adalah penulisan bot telegram. Artikel sebelumnya dan analisis pekerjaan rumah pada database: 1 , 2 , 3 . Kepada semua orang yang memiliki kesabaran dan daya tahan, kepada semua orang yang telah membaca artikel keempat ini bersama saya, selamat. Seperti kata pepatah, orang yang berjalan bisa menguasai jalan. Minggu ini artikel terakhir tentang Database akan dirilis, di mana kita akan membahas tentang jenis hubungan dan gabungan . Namun sebelum kita membahas informasi baru, mari kita periksa pekerjaan rumah kita... Saya benar-benar merasa seperti seorang guru. Jangan marah kepada saya: Saya tidak memiliki pendidikan pedagogi, itu sudah pasti. Sejak minggu lalu pemeriksaan rinci pada remote control menghabiskan sebagian besar materi, saya memutuskan untuk membagi analisis pekerjaan rumah dan tinjauan materi baru menjadi dua bagian.

Sebenarnya, analisis pekerjaan rumah

Saya sangat senang ada orang yang melakukan PD dan membicarakannya. Ini keren! Saya yakin sekali bahwa sekadar membaca tanpa memantapkan ilmu adalah jalan menuju kemana-mana. Oleh karena itu, setiap orang yang melakukan atau mencoba melakukan - hormat. Izinkan saya mengingatkan Anda ketentuan tugas:

1. Pahami operator HAVING dan tulis contoh kueri untuk tabel dari contoh kita. Jika Anda perlu menambahkan beberapa bidang atau lebih banyak nilai agar lebih jelas, tambahkan saja. Jika ada yang mau, tulis contoh solusi Anda di komentar - agar saya juga bisa memeriksanya jika saya punya waktu.
2. Instal MySQL Workbench untuk bekerja dengan database melalui UI. Saya rasa kami sudah cukup berlatih bekerja dari konsol. Hubungkan ke basis data. Jika Anda menggunakan sesuatu yang lain untuk bekerja dengan database, silakan lewati tugas ini. Di sini dan selanjutnya saya hanya akan menggunakan MySQL Workbench.
3. Tulis permintaan tanda terima menggunakan data kami:
   1. negara terkecil/terpadat;
   2. jumlah rata-rata penduduk di negara tersebut;
   3. jumlah rata-rata penduduk di negara-negara yang namanya diakhiri dengan “a”;
   4. jumlah negara dengan jumlah penduduk lebih dari empat juta;
   5. mengurutkan negara berdasarkan penurunan jumlah penduduk;
   6. mengurutkan negara berdasarkan nama dalam urutan alami.

Mari kita bicara tentang MEMILIKI

Mengetahui operator Memiliki dapat membantu Anda melewati lebih dari satu wawancara di mana akan ada masalah SQL. Oleh karena itu, memahaminya sangatlah penting. Kebetulan Anda tidak dapat menggunakan kondisi untuk menggabungkan fungsi (SUM, MIN, MAX, AVG). Selain itu, HAVING digunakan untuk field yang dikelompokkan. Apa artinya? Misalnya, jika kita ingin mendapatkan negara yang rata-rata jumlah penduduk perkotaannya lebih dari 50.000 jiwa, kita tidak dapat melakukannya tanpa menggunakan HAVING. Sejauh yang saya pahami, hal ini dilakukan karena agregasi terjadi setelah pernyataan WHERE dijalankan dan tidak mungkin untuk menambahkan nilai agregasi ke dalamnya yang akan dihitung nanti. Meskipun sejauh ini pendapat saya tidak banyak menambah pemahaman, Anda cukup menerima hal ini sebagai fakta dan menjalaninya. Dalam pemrograman, sering kali terjadi bahwa jika ada sesuatu yang tidak jelas pada suatu saat, itu mungkin berarti otak belum mencernanya. Tidurlah dengan pemikiran ini, dan keesokan harinya segalanya akan menjadi lebih jelas.

Menginstal Meja Kerja MySQL

Di sini dan selanjutnya saya akan menggunakan Workbench untuk pertanyaan. Saya akan menunjukkan kepada Anda apa yang Anda perlukan untuk menginstal dan membuat koneksi ke database. Ini adalah produk dari Oracle, jadi Anda hanya perlu mengunjungi situs web mereka dan memilih versi dan sistem operasi yang Anda inginkan. Untuk melakukannya, ikuti tautan ini : Di sini Anda dapat memilih sistem operasi yang Anda perlukan. Klik Unduh , tetapi alih-alih mengunduh, kita akan melihat jendela ini: Jangan tersesat, cari saja tombol bernama Tidak, terima kasih, mulai saja unduhan saya , dan unduhan akan dimulai. Mengapa mereka melakukan itu? Mungkin, agar lebih banyak orang yang mendaftar, ini tidak penting bagi kami. Setelah berhasil diunduh, jalankan file instalasinya. Di MacOS tampilannya seperti ini: Cukup seret ikon dan selesai, instalasi selesai. Tidak sesulit instalasi MySQL itu sendiri bukan? Atau kita baru saja terbiasa dan menjadi lebih berpengalaman ;) Bagian kedua dari tugas ini adalah membuat koneksi ke database kita. Apa yang dibutuhkan untuk ini? Klik tanda plus di sebelah Koneksi MySQL: Di jendela yang muncul, masukkan data yang diperlukan:

• Nama Koneksi - nama koneksi kita. Tuliskan nama sejelas mungkin agar tidak ada masalah identifikasi di kemudian hari. Saya memberi nama koneksi ini JRTB_DB ;
• Nama Host - sudah ditetapkan sebagai lokal 127.0.0.1 (alias localhost). Dalam kasus kami, tidak ada yang perlu diubah, karena database diinstal di komputer, tetapi jika database berada di tempat lain, maka host (ip mesin tempat database berjalan) perlu diubah;
• Nama Pengguna - Anda juga dapat menentukan pengguna Anda jika perlu. Jika Anda belum menambahkan apa pun ke kunci ini, biarkan tidak berubah;
• Kata Sandi - klik Simpan di Rantai Kunci dan atur persis kata sandi yang Anda tetapkan sendiri. Saya membiarkan semuanya sederhana - root .

Untuk memeriksa apakah koneksi akan berfungsi, klik Uji Koneksi : Nah, jika semuanya dilakukan dengan benar, hasilnya tidak akan lama lagi: Sekarang kita memiliki koneksi yang tersimpan di database, dan kita tidak perlu membuat koneksi dan mengisi dalam nama dan kata sandi setiap saat. Dan kebahagiaan ini akan terlihat seperti ini: Kita masuk ke koneksi yang baru dibuat dan melihat jendela permintaan. Untuk memastikan bahwa semuanya benar, mari kita periksa daftar database, buka database kami dan dapatkan semua data tentang kota-kota tersebut: Dan di sini kita mendapatkan kekuasaan penuh untuk apa yang kita butuhkan. Bagian pertama bertanggung jawab atas script yang kita masukkan. Selanjutnya, di Result Grid , kita melihat hasil operasi terakhir di skrip. Dan Output Tindakan menunjukkan daftar operasi dan hasilnya. Suatu hal yang sangat berguna, saya ingin memberitahu Anda: dengan bantuannya Anda dapat memantau kecepatan eksekusi skrip tertentu. Mengapa ini penting? Salah satu masalah paling umum dalam kecepatan eksekusi tugas dalam suatu aplikasi adalah kecepatan eksekusi query dalam database. Di sini Anda dapat dengan cepat dan mudah memeriksanya dengan tangan.

Kami menulis permintaan yang diperlukan

Kita hanya punya 7 permintaan yang harus diselesaikan, ayo!

1. Dapatkan negara yang paling padat penduduknya. Di sini Anda dapat berjalan dengan cerdik dan mengambil beberapa rute:

• Menurut tabel negara

Kemudian kita hanya perlu mengurutkan kueri berdasarkan populasi dan hanya mengambil satu catatan. Untuk melakukan ini, Anda perlu menambahkan operator LIMIT di akhir skrip dan menentukan jumlah yang diperlukan: $ SELECT * FROM negara ORDER BY populasi DESC LIMIT 1;

• Menurut tabel kota

Semuanya lebih menarik di sini, karena permintaannya akan lebih kompleks, tetapi juga lebih menarik. Karena kita masih belum tahu tentang gabungan, kita hanya bisa mendapatkan ID negara: $ SELECT country_id, SUM(population) FROM city GROUP BY country_id ORDER BY SUM(population) DESC LIMIT 1; Di sini kami melakukan hal yang keren - kami mengumpulkan jumlah populasi semua kota yang diketahui di setiap negara, mengurutkannya berdasarkan jumlah ini, dan mengambil elemen pertama. Nah, apakah itu bagus? Saya senang :D Setelah ini Anda langsung merasa seperti ahli kueri... (tidak lama tentunya))

2. Dapatkan negara terkecil. Di sini Anda bisa berjalan dengan cerdik dan mengambil beberapa rute

Dalam hal ini, semuanya akan sama persis. Satu-satunya perbedaan adalah penyortirannya akan dibalik - itu saja. Oleh karena itu, saya cukup menulis permintaan:

• Menurut tabel kota

$ PILIH country_id, SUM(populasi) DARI kota KELOMPOK OLEH country_id ORDER BY SUM(populasi) LIMIT 1;

• Menurut tabel negara

$ PILIH * DARI negara ORDER BERDASARKAN populasi LIMIT 1; Dan lihat sendiri hasilnya!

3. Jumlah rata-rata penduduk di negara tersebut

Di sini sekali lagi spesifikasi teknisnya entah bagaimana tidak akurat, seolah-olah manajerlah yang menulisnya... Mengapa saya memutuskan ini? Karena tidak jelas meja mana yang akan dikerjakan. Tapi ini normal: tidak ada tugas yang semuanya akan langsung jelas dan dapat dimengerti. Oleh karena itu, Anda perlu membaca tugas dengan cermat, dan jika Anda memiliki pertanyaan, segera tanyakan! Itu benar, sebuah komentar. Dengan mempertimbangkan data yang kami miliki di database, kami akan mencari menggunakan data dari kota. Untuk melakukannya, tulis kueri berikut: $ SELECT country_id, AVG(population) FROM city GROUP BY country_id; Sederhana saja di sini: kami menggunakan fungsi AVG dan mengelompokkan catatan kota kami berdasarkan negara.

4. Jumlah rata-rata penduduk di negara-negara yang namanya diakhiri dengan “a”

Akan ada sedikit perubahan di sini berdasarkan permintaan. Kita perlu menambahkan pemfilteran berdasarkan nama sebelum melakukan pengelompokan. Saya mengerjakan pekerjaan rumah saya, seperti semua siswa, sebelum menerbitkan artikel ini, dan saya memahami bahwa masalah ini tidak dapat diselesaikan tanpa bergabung. Mengapa? Karena selain ID negaranya, kita juga perlu mengetahui namanya. Dan ini tidak dapat dilakukan tanpa menggabungkan dua tabel menjadi satu record. Oleh karena itu, saya akan melakukan tugas ini, tentu saja, tetapi ini kusen saya...))) Saya ingin membuat tugas menggunakan LIKE dalam permintaan...) $ SELECT ci.country_id, AVG(ci.population ) DARI kota ci INNER JOIN country co ON ci.country_id = co.id WHERE co.name LIKE "%a" GROUP BY country_id; Apa yang terjadi disini? Pertama, kami menggabungkan catatan dari tabel kota dan negara menggunakan kunci asing country_id, memfilter berdasarkan nama negara sehingga diakhiri dengan “a,” dan baru kemudian dikelompokkan berdasarkan country_id.

5. Jumlah negara dengan populasi lebih dari empat juta

Disini kita hanya perlu menggunakan fungsi COUNT dan menambahkan filtering pada populasi: $ SELECT COUNT(*) from country WHERE Population > 4000000; Hasilnya, ternyata ada 3 negara yang seperti itu, benarkah? Ya, hanya Moldova yang tidak melewati tonggak sejarah ini.

6. Urutkan negara berdasarkan penurunan jumlah penduduk

Untuk melakukan ini, kita perlu menggunakan operator ORDER BY yang sudah kita kenal. Namun perlu diingat bahwa secara default pengurutan dilakukan dalam urutan alami. Untuk angka artinya diurutkan menaik, untuk string artinya diurutkan mulai dari karakter pertama. Jika kita memerlukan pengurutan menurun, kita memerlukan kebalikan dari pengurutan alami: $ SELECT * FROM country ORDER BY Population DESC;

7. Urutkan negara berdasarkan nama dalam urutan alami

Di sinilah pengetahuan tentang tatanan alam akan berguna. Karena ini adalah defaultnya, maka mudah bagi kami: $ SELECT * FROM country ORDER BY name;

Alih-alih keluaran

Kebetulan ukuran solusi pekerjaan rumah akan sangat besar, jadi kami akan membuat pengecualian: Saya menerbitkan artikel ini dengan cek, dan pada hari Jumat saya akan menerbitkan materi baru dengan koneksi dan bergabung. Terima kasih semuanya telah membaca. Sampai hari Jumat!

Daftar semua materi dalam seri ini ada di awal artikel ini.