Artikel dari seri tentang membuat proyek Java (tautan ke materi lain ada di akhir). Tujuannya adalah menganalisis teknologi utama, dan hasilnya adalah menulis bot telegram.
Halo semuanya, Senior dan Senioritas perangkat lunak masa depan. Seperti yang saya katakan di bagian sebelumnya ( memeriksa pekerjaan rumah ), hari ini akan ada materi baru. Bagi mereka yang sangat bersemangat, saya telah menggali pekerjaan rumah yang menarik sehingga mereka yang sudah tahu segalanya dan mereka yang belum tahu tapi ingin googling bisa berlatih dan menguji kemampuannya. Hari ini kita akan berbicara tentang jenis koneksi dan gabungan.
Jenis hubungan dalam database
Untuk memahami apa itu hubungan, Anda perlu mengingat apa itu kunci asing. Bagi yang lupa, selamat datang di awal seri .
Satu-ke-banyak
Mari kita ingat contoh kita dengan negara dan kota. Jelas bahwa sebuah kota harus memiliki negara. Bagaimana cara menghubungkan suatu negara ke kota? Setiap kota perlu dilampirkan pengenal (ID) unik dari negara tempatnya berada: kami telah melakukan ini. Ini disebut salah satu jenis koneksi - satu ke banyak (ada baiknya juga mengetahui versi bahasa Inggris - satu-ke-banyak). Singkatnya, kita dapat mengatakan: beberapa kota dapat menjadi milik satu negara. Begitulah cara Anda mengingatnya: hubungan satu-ke-banyak. Sejauh ini sudah jelas, bukan? Jika belum, berikut gambar pertama dari Internet: Terlihat ada pelanggan dan pesanannya. Masuk akal jika satu pelanggan dapat menerima lebih dari satu pesanan. Ada satu-ke-banyak :) Atau contoh lain: Ada tiga tabel: penerbit, penulis, dan buku. Setiap penerbit yang tidak ingin bangkrut dan ingin sukses memiliki lebih dari satu penulis, setujukah Anda? Pada gilirannya, setiap penulis mungkin memiliki lebih dari satu buku - hal itu juga tidak diragukan lagi. Dan ini berarti, sekali lagi, hubungan antara satu penulis dengan banyak buku, satu penerbit dengan banyak penulis . Masih banyak lagi contoh yang bisa diberikan. Kesulitan dalam persepsi pada awalnya mungkin hanya terletak pada belajar berpikir abstrak: melihat dari luar tabel dan interaksinya.
Satu lawan satu (satu lawan satu)
Hal ini dapat dikatakan sebagai kasus khusus komunikasi satu-ke-banyak. Situasi di mana satu record dalam satu tabel hanya terkait dengan satu record di tabel lain. Contoh apa saja yang bisa diambil dari kehidupan? Jika kita mengecualikan poligami, maka kita dapat mengatakan bahwa ada hubungan satu lawan satu antara suami dan istri. Meski poligami dikatakan boleh, namun setiap istri tetap boleh mempunyai satu suami saja. Hal yang sama juga berlaku pada orang tua. Setiap orang hanya boleh mempunyai satu ayah kandung dan hanya satu ibu kandung. Hubungan satu-ke-satu yang eksplisit. Saat saya menulis ini, sebuah pemikiran muncul di benak saya: mengapa membagi hubungan satu-ke-satu menjadi dua catatan di tabel yang berbeda, jika mereka sudah memiliki hubungan satu-ke-satu? Saya sendiri yang menemukan jawabannya. Catatan ini juga dapat ditautkan ke catatan lain dengan cara lain. Apa yang saya bicarakan? Contoh lain dari hubungan satu lawan satu adalah antara negara dan presiden. Apakah mungkin untuk menuliskan semua data tentang presiden di tabel “negara”? Ya, Anda bisa, SQL tidak akan mengucapkan sepatah kata pun. Tetapi jika Anda berpikir bahwa presiden juga seorang manusia... Dan dia mungkin juga memiliki seorang istri (hubungan satu-ke-satu yang lain) dan anak-anak (hubungan satu-ke-banyak yang lain) dan ternyata hal itu akan terjadi diperlukan untuk menghubungkan negara dengan istri dan anak presiden…. Kedengarannya gila, bukan? :D Masih banyak contoh lain untuk hubungan ini. Selain itu, dalam situasi seperti ini, Anda dapat menambahkan kunci asing ke kedua tabel, tidak seperti hubungan satu-ke-banyak.
Banyak ke banyak
Dari namanya saja sudah bisa ditebak apa yang akan kita bicarakan. Seringkali dalam hidup, dan kita memprogram hidup kita, ada situasi ketika jenis koneksi di atas tidak cukup untuk menggambarkan hal-hal yang kita butuhkan. Kami telah berbicara tentang penerbit, buku, dan penulis. Ada begitu banyak koneksi di sini... Setiap publikasi dapat memiliki beberapa penulis - koneksi satu-ke-banyak. Pada saat yang sama, setiap penulis mungkin memiliki beberapa penerbit (kenapa tidak, penulis diterbitkan di satu tempat, berebut uang, pergi ke penerbit lain, misalnya). Dan ini sekali lagi merupakan hubungan satu-ke-banyak. Atau ini: setiap penulis bisa memiliki beberapa buku, namun setiap buku juga bisa memiliki beberapa penulis. Sekali lagi, hubungan satu-ke-banyak antara penulis dan buku, buku dan penulis. Dari contoh ini kita dapat menarik kesimpulan yang lebih formal:
Jika kita mempunyai dua tabel A dan B.
A dapat berhubungan dengan B sebagai satu lawan banyak.
Namun B juga dapat berhubungan dengan A seperti halnya seseorang berhubungan dengan banyak orang.
Artinya mereka mempunyai hubungan banyak ke banyak.
Sudah jelas cara mengatur jenis koneksi sebelumnya di SQL: kita cukup meneruskan ID yang satu itu ke catatan itu, yang jumlahnya banyak, bukan? Satu negara memberikan ID-nya sebagai kunci asing ke banyak kota. Apa yang harus dilakukan dengan hubungan banyak ke banyak ? Metode ini tidak cocok. Kita perlu menambahkan tabel lain yang akan menghubungkan kedua tabel tersebut. Sebagai contoh, mari kita pergi ke MySQL, membuat database baru manytomany, membuat dua tabel, penulis dan buku, yang hanya berisi nama dan ID-nya: CREATE DATABASE manytomany; GUNAKAN banyak ke banyak; BUAT TABEL penulis( id INT AUTO_INCREMENT, nama VARCHAR(100), PRIMARY KEY (id) ); BUAT TABEL buku( id INT AUTO_INCREMENT, nama VARCHAR(100), PRIMARY KEY (id) ); Sekarang mari kita buat tabel ketiga yang akan memiliki dua kunci asing dari tabel penulis dan buku kita, dan tautan ini akan menjadi unik. Artinya, tidak mungkin menambahkan catatan dengan kunci yang sama dua kali: CREATE TABLE author_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 ), UNIK (book_id, author_id) ); Di sini kami menggunakan beberapa fitur baru yang perlu dikomentari secara terpisah:
NOT NULL berarti kolom tersebut harus selalu diisi, dan jika tidak, SQL akan memberitahukannya kepada kita;
UNIQUE mengatakan bahwa suatu bidang atau sekumpulan bidang harus unik dalam tabel. Sering terjadi bahwa selain pengidentifikasi unik, bidang lain harus unik untuk setiap catatan. Dan UNIQUE bertanggung jawab atas masalah ini.
Dari praktik saya: ketika berpindah dari sistem lama ke sistem baru, kita sebagai pengembang harus menyimpan ID sistem lama untuk bekerja dengannya dan membuatnya sendiri. Mengapa membuat sendiri dan tidak menggunakan yang lama? ID tersebut mungkin tidak cukup unik, atau pendekatan pembuatan ID ini mungkin tidak lagi relevan dan terbatas. Untuk tujuan ini, kami membuat nama ID lama juga unik di tabel. Untuk memeriksanya, Anda perlu menambahkan data. Tambahkan buku dan penulis: NSERT INTO book (name) VALUES ("book1"); MASUKKAN KE penulis (nama) NILAI ("penulis1"); Kita sudah mengetahui dari artikel sebelumnya bahwa mereka akan memiliki ID 1 dan 1. Oleh karena itu, kita dapat segera menambahkan record ke tabel ketiga: INSERT INTO author_x_books VALUES (1,1); Dan semuanya akan baik-baik saja sampai kita ingin mengulangi perintah terakhir lagi: yaitu, tuliskan ID yang sama lagi: Hasilnya akan alami - kesalahan. Akan ada duplikat. Entrinya tidak akan direkam. Ini adalah bagaimana koneksi banyak ke banyak akan dibuat... Semua ini sangat keren dan menarik, tetapi muncul pertanyaan logis: bagaimana cara mendapatkan informasi ini? Bagaimana cara menggabungkan data dari tabel yang berbeda dan mendapatkan satu jawaban? Inilah yang akan kita bicarakan di bagian selanjutnya))
Koneksi (Bergabung)
Pada bagian sebelumnya, saya mempersiapkan Anda untuk segera memahami apa itu join dan di mana menggunakannya. Karena saya sangat yakin bahwa begitu pemahaman datang, semuanya akan segera menjadi sangat sederhana, dan semua artikel tentang join akan terlihat jelas di mata bayi :D Secara kasar dan umum, join adalah hasil dari beberapa tabel dengan cara dari GABUNG (gabung dari bahasa Inggris gabung). Dan itu saja...) Dan untuk bergabung, Anda perlu menentukan kolom yang akan digunakan untuk menggabungkan tabel. Iblis itu tidak seseram yang dilukis kan?) Berikutnya kita akan membahas apa saja jenis join yang ada dan bagaimana cara menggunakannya. Ada banyak jenis gabungan, dan kami tidak akan mempertimbangkan semuanya. Hanya yang benar-benar kita butuhkan. Itu sebabnya kami tidak tertarik pada gabungan eksotis seperti Cross dan Natural. Saya benar-benar lupa, kita perlu mengingat satu nuansa lagi: tabel dan bidang dapat memiliki alias - nama samaran. Mereka mudah digunakan untuk bergabung. Misalnya, Anda dapat melakukan ini: SELECT * FROM table1; jika kueri akan sering menggunakan tabel1, maka Anda dapat memberinya alias: SELECT* FROM table1 as t1; atau lebih mudah lagi untuk menulis: SELECT * FROM table1 t1; dan kemudian dalam kueri dimungkinkan untuk menggunakan t1 sebagai alias untuk tabel ini.
GABUNG DALAM
Gabungan yang paling umum dan sederhana. Dikatakan bahwa ketika kita memiliki dua tabel dan sebuah field yang dapat digabungkan, semua record yang hubungannya ada di kedua tabel akan dipilih. Sulit untuk mengatakannya. Mari kita lihat sebuah contoh: Mari tambahkan satu record ke database kota kita. Satu entri untuk kota dan satu untuk negara: $ INSERT INTO country VALUES(5, "Uzbekistan", 34036800); dan $ INSERT INTO city (nama, populasi) VALUES("Tbilisi", 1171100); Kami menambahkan negara yang tidak memiliki kota di tabel kami, dan kota yang tidak terkait dengan negara di tabel kami. Jadi, INNER JOIN terlibat dalam penerbitan semua catatan untuk koneksi yang ada di dua tabel. Seperti inilah sintaks umumnya ketika kita ingin menggabungkan dua tabel table1 dan table2: SELECT * FROM table1 t1 INNER JOIN table2 ON t1.id = t2.t1_id; dan kemudian semua record yang memiliki hubungan pada kedua tabel tersebut akan dikembalikan. Untuk kasus kita, ketika kita ingin menerima informasi negara beserta kotanya, maka hasilnya akan seperti ini: $ SELECT * FROM city ci INNER JOIN country co ON ci.country_id = co.id; Di sini, meskipun namanya sama, Anda dapat melihat dengan jelas bahwa bidang kota didahulukan, baru kemudian bidang negara. Namun dua entri yang kami tambahkan di atas tidak ada. Karena itulah cara kerja INNER JOIN.
KIRI GABUNG
Ada kasus, dan cukup sering, ketika kita tidak puas dengan hilangnya bidang tabel utama karena tidak ada catatannya di tabel yang berdekatan. Inilah gunanya LEFT JOIN. Jika dalam permintaan kami sebelumnya kami menentukan LEFT dan bukan INNER, kami akan menambahkan kota lain sebagai respons - Tbilisi: $ SELECT * FROM city ci LEFT JOIN country co ON ci.country_id = co.id; Ada entri baru tentang Tbilisi dan segala sesuatu yang berhubungan dengan negara ini ada di null . Ini sering kali digunakan.
BENAR BERGABUNG
Di sini akan ada perbedaan dari LEFT JOIN di mana semua field akan dipilih bukan di kiri, tetapi di kanan dalam koneksi. Maksudnya bukan kota, tapi semua negara yang akan diambil: $PILIH*DARI kota ci KANAN GABUNG negara co ON ci.country_id = co.id; Sekarang jelas bahwa dalam hal ini tidak akan ada Tbilisi, tetapi kita akan memiliki Uzbekistan. Sesuatu seperti itu…))
Mengamankan Gabungan
Sekarang saya ingin menunjukkan kepada Anda gambaran khas yang dijejalkan para junior sebelum wawancara untuk meyakinkan mereka bahwa mereka memahami esensi dari gabungan: Di sini semuanya ditampilkan dalam bentuk set, setiap lingkaran adalah tabel. Dan tempat-tempat yang dicat adalah bagian-bagian yang akan ditampilkan di SELECT. Mari lihat:
INNER JOIN hanyalah perpotongan himpunan, yaitu record yang memiliki koneksi ke dua tabel - A dan B;
LEFT JOIN adalah semua record dari tabel A, termasuk semua record dari tabel B yang mempunyai perpotongan (koneksi) dengan A;
RIGHT JOIN adalah kebalikan dari LEFT JOIN - semua record di tabel B dan record dari A yang memiliki hubungan.
Setelah semua ini, gambar ini seharusnya jelas))
Pekerjaan rumah
Kali ini tugasnya akan sangat menarik dan semua orang yang berhasil menyelesaikannya dapat yakin bahwa mereka siap untuk mulai bekerja di sisi SQL! Tugasnya tidak dikunyah dan ditulis untuk siswa menengah, jadi tidak akan mudah dan membosankan bagi Anda :) Saya akan memberi Anda waktu seminggu untuk mengerjakan tugas itu sendiri, dan kemudian saya akan menerbitkan artikel terpisah dengan analisis terperinci dari solusi tugas yang saya berikan kepada Anda.
Tugas sebenarnya:
Tulis skrip SQL untuk membuat tabel 'Siswa' dengan kolom berikut: id (kunci utama), nama, nama belakang, email (unik).
Tulis skrip SQL untuk membuat tabel 'Buku' dengan kolom berikut: id, judul (id + judul = kunci utama). Tautkan 'Siswa' dan 'Buku' dengan hubungan 'Siswa' satu-ke-banyak 'Buku'.
Tulis skrip SQL untuk membuat tabel 'Guru' dengan kolom berikut: id (kunci utama), nama, nama belakang, email (unik), subjek.
Hubungkan 'Siswa' dan 'Guru' dengan hubungan 'Siswa' banyak-ke-banyak Guru'.
Pilih 'Siswa' yang memiliki nama belakang 'oro', misalnya 'Sid oro v', 'V oro novsky'.
Pilih dari tabel 'Siswa' semua nama belakang ('nama_belakang') dan jumlah pengulangannya. Pertimbangkan bahwa ada nama yang sama dalam database. Urutkan berdasarkan kuantitas dalam urutan menurun. Seharusnya terlihat seperti ini:
nama keluarga
kuantitas
Petrov
15
Ivanov
12
Sidorov
3
Pilih 3 nama teratas yang paling sering diulang dari 'Siswa'. Urutkan berdasarkan kuantitas dalam urutan menurun. Seharusnya terlihat seperti ini:
nama
kuantitas
Alexander
27
Sergei
10
Petrus
7
Pilih 'Siswa' yang memiliki jumlah 'Buku' terbanyak dan 'Guru' terkait. Urutkan berdasarkan jumlah dalam urutan menurun. Seharusnya terlihat seperti ini:
Nama belakang guru
Nama belakang siswa
Kuantitas buku
Petrov
Sidorov
7
Ivanov
Smith
5
Petrov
Kankava
2>
Pilih 'Guru' yang memiliki jumlah 'Buku' terbanyak dari seluruh 'Siswa' miliknya. Urutkan berdasarkan kuantitas dalam urutan menurun. Seharusnya terlihat seperti ini:
Nama belakang guru
Kuantitas buku
Petrov
9
Ivanov
5
Pilih 'Guru' yang nomor 'Buku' untuk semua 'Siswa' miliknya antara 7 dan 11. Urutkan berdasarkan kuantitas dalam urutan menurun. Seharusnya terlihat seperti ini:
Nama belakang guru
Kuantitas buku
Petrov
sebelas
Sidorov
9
Ivanov
7
Cetak semua 'nama_belakang' dan 'nama' dari semua 'Guru' dan 'Siswa' dengan kolom 'tipe' (siswa atau guru). Urutkan menurut abjad berdasarkan 'nama_belakang'. Seharusnya terlihat seperti ini:
nama keluarga
jenis
Ivanov
murid
Kankava
guru
Smith
murid
Sidorov
guru
Petrov
guru
Tambahkan kolom 'nilai' ke tabel 'Siswa' yang ada, yang akan menyimpan mata kuliah yang diikuti siswa tersebut (nilai numerik dari 1 hingga 6).
Item ini tidak wajib, tapi akan menjadi nilai tambah. Tulis fungsi yang akan menelusuri semua 'Buku' dan menampilkan semua 'judul' yang dipisahkan dengan koma.
Kesimpulan
Seri tentang database telah berlarut-larut. Setuju. Namun, kami telah menempuh perjalanan panjang dan sebagai hasilnya kami mendapatkan pengetahuan tentang masalah ini! Terima kasih telah membaca, saya ingatkan Anda bahwa setiap orang yang ingin melanjutkan dan mengikuti proyek ini perlu membuat akun di GitHub dan berlangganan akun saya :) Lebih banyak lagi yang akan datang - mari kita bicara tentang Maven dan Docker. Terima kasih semuanya telah membaca. Saya ulangi sekali lagi: siapa yang berjalan akan menguasai jalan ;)
Hari ini kita akan berbicara tentang jenis koneksi dan gabungan.
Untuk memahami apa itu hubungan, Anda perlu mengingat apa itu kunci asing. Bagi yang lupa, selamat datang di awal seri .
gambar pertama dari Internet: Terlihat ada pelanggan dan pesanannya. Masuk akal jika satu pelanggan dapat menerima lebih dari satu pesanan. Ada satu-ke-banyak :) Atau contoh lain: 
Ada tiga tabel: penerbit, penulis, dan buku. Setiap penerbit yang tidak ingin bangkrut dan ingin sukses memiliki lebih dari satu penulis, setujukah Anda? Pada gilirannya, setiap penulis mungkin memiliki lebih dari satu buku - hal itu juga tidak diragukan lagi. Dan ini berarti, sekali lagi, hubungan antara satu penulis dengan banyak buku, satu penerbit dengan banyak penulis . Masih banyak lagi contoh yang bisa diberikan. Kesulitan dalam persepsi pada awalnya mungkin hanya terletak pada belajar berpikir abstrak: melihat dari luar tabel dan interaksinya.
Sekarang mari kita buat tabel ketiga yang akan memiliki dua kunci asing dari tabel penulis dan buku kita, dan tautan ini akan menjadi unik. Artinya, tidak mungkin menambahkan catatan dengan kunci yang sama dua kali: CREATE TABLE author_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 ), UNIK (book_id, author_id) ); 
Di sini kami menggunakan beberapa fitur baru yang perlu dikomentari secara terpisah:
Hasilnya akan alami - kesalahan. Akan ada duplikat. Entrinya tidak akan direkam. Ini adalah bagaimana koneksi banyak ke banyak akan dibuat... Semua ini sangat keren dan menarik, tetapi muncul pertanyaan logis: bagaimana cara mendapatkan informasi ini? Bagaimana cara menggabungkan data dari tabel yang berbeda dan mendapatkan satu jawaban? Inilah yang akan kita bicarakan di bagian selanjutnya))
Di sini, meskipun namanya sama, Anda dapat melihat dengan jelas bahwa bidang kota didahulukan, baru kemudian bidang negara. Namun dua entri yang kami tambahkan di atas tidak ada. Karena itulah cara kerja INNER JOIN.
Ada entri baru tentang Tbilisi dan segala sesuatu yang berhubungan dengan negara ini ada di null . Ini sering kali digunakan.
Sekarang jelas bahwa dalam hal ini tidak akan ada Tbilisi, tetapi kita akan memiliki Uzbekistan. Sesuatu seperti itu…))
Di sini semuanya ditampilkan dalam bentuk set, setiap lingkaran adalah tabel. Dan tempat-tempat yang dicat adalah bagian-bagian yang akan ditampilkan di SELECT. Mari lihat:
GO TO FULL VERSION