Pengantar Koneksi MySQL

Database


Saat membuat database dengan tabel terkait, pada akhirnya Anda mungkin perlu mengambil data yang terkait antar tabel.

Anda dapat melakukan ini menggunakan koneksi MySQL. Gabung adalah cara untuk mengubah kolom dari lebih dari satu tabel menjadi satu set hasil. Ini biasanya jauh lebih efisien daripada mencoba menjalankan beberapa kueri dan menggabungkannya nanti.

Artikel ini membahas berbagai jenis gabungan yang dapat dilakukan di MySQL dan menunjukkan cara kerja yang berbeda untuk menggabungkan data dari beberapa tabel.

Untuk lebih memahami gabungan, kami membuat database sederhana dari item makanan, setiap item memiliki kategori. Kategori disimpan di categories Meja dan barang-barang disimpan di tempat terpisah items Meja.

CREATE TABLE categories (
  id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(250) NOT NULL
);

Contoh categories Tabel penuh data:

CREATE TABLE items (
  id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(512),
  category_id int NULL
);

CREATE TABLE produk ( id int NOT NULL AUTO_INCREMENT PRIMARY KEY, nama varchar(255) NOT NULL, image_url varchar(255), category_id INT, KEY category_id_idx (category_id) );

Contohitems Tabel penuh data:

PENGENAL nama kategori_id
1 apel 1
2 Keju 2

Berdasarkan contoh ini, kita akan memeriksa berbagai jenis koneksi dan cara menggunakannya selama tutorial.

Sekarang setelah kita menyimpan item dan kategori, kita mungkin ingin menampilkan item, bukan hanya nama kategori. category_idkarena “Deli” lebih berarti bagi manusia daripada “2”.

Untuk ini kita dapat menggunakan INNER JOIN, yang memilih catatan yang cocok di antara tabel. Ini adalah perilaku default untuk GABUNG, jadi INNER JOIN Itu seperti itu JOIN.

Kesalahan umum dalam koneksi internal

Peringatan: Jika keanggotaan tanpa ON Klausa, Anda akan melakukan apa yang kadang-kadang disebut A CROSS JOIN, yang menampilkan setiap baris di tabel kiri satu kali untuk setiap baris di tabel kanan. Ini biasanya bukan yang kita inginkan – dan menciptakan a Banyak Hasil lebih.

Mari kita lihat contoh koneksi silang:

-- Don’t do this unless you know what you are doing:
SELECT * FROM items
JOIN categories; -- No ON columns specified!

Kami akhirnya mendapatkan lebih banyak hasil daripada yang kami harapkan! Dengan banyak data, itu akan menjadi kekacauan besar.

PENGENAL nama kategori_id PENGENAL nama
1 apel 1 1 menghasilkan
1 apel 1 2 restoran
2 Keju 2 1 menghasilkan
2 Keju 2 2 restoran

Tentukan kolom untuk kategori GABUNG AKTIF

Untuk mendapatkan hasil yang kita inginkan, kita perlu memberi tahu kolom mana yang terkait.

Dengan kata lain, kita harus mengatakan bahwa sakelar utama (id) Dari categories Tentang kunci asing (category_id) Dari items.

SELECT * FROM items
-- JOIN is the same as INNER JOIN
JOIN categories ON items.category_id = categories.id;
PENGENAL nama kategori_id PENGENAL nama
1 apel 1 1 menghasilkan
2 Keju 2 2 restoran

Sekarang kita dengan mudah mengembalikan nama kategori dengan setiap item!

Memberi nama unik pada kolom

Anda mungkin memperhatikan pada tabel di atas bahwa sekarang ada dua name Bidang khusus untuk kedua tabel name kolom untuk membuat kueri lebih bermanfaat, kita dapat menggunakan Alias Untuk kolom keluaran seperti sesuatu yang lain.

Untuk contoh ini, kita akan menggunakan c Ke categories Dan i Ke items.

SELECT * FROM items AS i -- we now refer to items as i
JOIN categories AS c -- we now refer to categories as c
    ON i.category_id = c.id;

Catatan: Menggunakan AS Ini opsional, jadi kita sering melihatnya dihilangkan.

Sebaiknya tentukan juga kolom yang ingin kita kembalikan daripada meminta semuanya *, terutama saat menggunakan tabel dengan banyak kolom, karena ini dapat membuat kueri berjalan lebih cepat. Dalam contoh ini, kami menghapus kategori id kolom

Karena nama kolom yang sama dipilih lebih dari sekali, kita perlu menentukan dari tabel mana kolom ini berasal. Kita bisa menggunakan i Dan c Sebuah nama panggilan untuk itu

SELECT
    i.id,
    i.name,
    i.category_id,
    c.name AS category_name -- now refer to categories.name AS category_name
FROM items i
JOIN categories c ON i.category_id = c.id;
PENGENAL nama kategori_id Nama Kategori
1 apel 1 menghasilkan
2 Keju 2 restoran

Sekarang hasil yang berguna dikembalikan yang dapat kami tampilkan kepada pengguna!

Untuk meringkas, kami menggunakan gabungan dalam untuk menggabungkan data dari dua tabel dengan sebuah relasi. Ada meja di sebelah kiri – meja pertama yang ditentukan setelah itu FROM (Pada kasus ini, itemsdan tabel sebelah kanan, ditentukan setelah JOIN (categories) dalam contoh kita.

Tautan internal dapat diilustrasikan dengan diagram Venn ini, yang menunjukkan bahwa satu-satunya data yang dikembalikan adalah data di mana item dan kategori terkait.

Misalkan kita menambahkan lebih banyak data ke tabel kita:

  • Item yang tidak ditangani
  • Kategori baru (tetapi tidak ada yang belum menggunakannya).

categories

PENGENAL nama
1 menghasilkan
2 restoran
3 susu

items

PENGENAL nama kategori_id
1 apel 1
2 Keju 2
3 Roti kosong

Jika kita melakukannya INNER JOIN Dalam data ini, kami mendapatkan yang berikut:

SELECT
    i.id,
    i.name,
    i.category_id,
    c.name AS category_name
FROM items i
JOIN categories c ON i.category_id = c.id;
PENGENAL nama kategori_id Nama Kategori
1 apel 1 menghasilkan
2 Keju 2 restoran

Perhatikan sesuatu yang hilang?

Tidak ada roti! kenapa tidak?

Saat kita melakukan inner join i.category_id = c.id, kami memberi tahu MySQL untuk mengembalikan hanya catatan dengan pegangan. Karena “roti” memiliki category_id Dalam arti bahwa NULLtidak cocok dengan apa pun dan karenanya tidak dikembalikan.

Demikian pula, karena tidak ada item yang memiliki kategori “Susu” baru kami, item ini juga tidak akan ada dalam hasil.

Seringkali, kamu masih ingin kembali Semua Item, bahkan item yang tidak memiliki kunci asing yang cocok di tabel yang ditautkan. Untuk mencapai tujuan ini, kita dapat menggunakan LEFT JOIN Untuk memastikan bahwa semua catatan item dikembalikan di tabel pertama (kiri). RIGHT JOIN Ia bekerja dengan cara yang hampir sama, kecuali bahwa ia mengembalikan semua catatan di tabel sebelah kanan—dalam hal ini, categories.

Jika kita melakukannya LEFT JOIN Dalam data ini, kita akan mendapatkan yang berikut:

SELECT
    i.id,
    i.name,
    i.category_id,
    c.name AS category_name
FROM items i
LEFT JOIN categories c ON i.category_id = c.id;
PENGENAL nama kategori_id Nama Kategori
1 apel 1 menghasilkan
2 Keju 2 restoran
3 Roti kosong kosong

Sekarang kami memiliki semua kasing berkat penggunaan a LEFT JOIN dari pada INNER JOIN!

Demikian pula, kita dapat menggunakan RIGHT JOIN Untuk mengembalikan semua kategori (tetapi tidak harus semua item).

SELECT
    i.id,
    i.name,
    i.category_id,
    c.name AS category_name
FROM items i
RIGHT JOIN categories c ON i.category_id = c.id;
PENGENAL nama kategori_id Nama Kategori
1 apel 1 menghasilkan
2 Keju 2 restoran
kosong kosong kosong susu

Koneksi kiri dan kanan dapat ditunjukkan dengan diagram Venn ini.


Jika kita ingin menunjukkan Semua item Dan Semua kategori, kita perlu melakukan pengikatan khusus, kadang-kadang disebut a FULL OUTER JOIN, meskipun jenis gabungan ini tidak didukung di MySQL. Namun, kita dapat mensimulasikan ini dengan melakukan keduanya SEBUAH LEFT JOIN Dan RIGHT JOINdan kombinasinya dengan UNION.

Untuk melakukan ini, kita perlu menambahkan WHERE Paragraf yang hanya menyertakan catatan dengan A NULL barang id Dari bagian kedua kueri, jika tidak, item dengan kategori semuanya akan ditampilkan dua kali.

SELECT
    i.id,
    i.name,
    i.category_id,
    c.name AS category_name
FROM items i
LEFT JOIN categories c ON i.category_id = c.id
UNION ALL
SELECT
    i.id,
    i.name,
    i.category_id,
    c.name AS category_name
FROM items i
RIGHT JOIN categories c ON i.category_id = c.id
-- This prevents duplicate items from showing
-- as we only want categories with no items.
WHERE i.id IS NULL; 
PENGENAL nama kategori_id Nama Kategori
1 apel 1 menghasilkan
2 Keju 2 restoran
3 Roti kosong kosong
kosong kosong kosong susu

Jenis “gabungan luar” ini diwakili oleh diagram ini.

Terkadang berguna untuk mencari catatan yang tidak terkait. Kami mungkin hanya ingin menemukan yang tidak diurutkan – mungkin kami dapat menemukannya untuk membersihkannya.

Untuk melakukan ini, kita dapat menambahkan item tambahan WHERE Klausa untuk A LEFT Dan RIGHT JOIN.

SELECT
    i.id,
    i.name,
    i.category_id,
    c.name AS category_name
FROM items i
LEFT JOIN categories c ON i.category_id = c.id
WHERE c.id IS NULL; 
PENGENAL nama kategori_id Nama Kategori
3 Roti kosong kosong

GABUNG ini ditampilkan di sini.

Untuk menampilkan hanya kategori tanpa item, kita dapat menggunakan yang serupa RIGHT JOIN dengan satu WHERE Ekspresi yang hanya menampilkan catatan dengan NULL barang id.

SELECT
    i.id,
    i.name,
    i.category_id,
    c.name AS category_name
FROM items i
RIGHT JOIN categories c ON i.category_id = c.id
WHERE i.id IS NULL; 
PENGENAL nama kategori_id Nama Kategori
kosong kosong kosong susu

GABUNG ini ditampilkan di sini.

Akhirnya, kita dapat menggunakannya jika kita ingin menampilkan item dan kategori yang tidak terkait OUTER JOIN Jenis kueri, tetapi cari masing-masing items Atau categories Kehadiran kunci NULL.

Agar kueri ini berfungsi, ada baiknya untuk menyertakan badan kueri dalam tanda kurung dan menerapkan ekspresi WHERE terikat pada kueri luar

SELECT * FROM (
SELECT i.id,
          i.name,
          i.category_id,
          c.name AS category_name
   FROM items i
         LEFT JOIN categories c ON i.category_id = c.id
   UNION ALL
   SELECT i.id,
          i.name,
          i.category_id,
          c.name AS category_name
   FROM items i
         RIGHT JOIN categories c ON i.category_id = c.id
   WHERE i.id IS NULL
) AS all_items_all_categories
WHERE id IS NULL OR category_id IS NULL; 
PENGENAL nama kategori_id Nama Kategori
3 Roti kosong kosong
kosong kosong kosong susu

GABUNG dari item yang tidak terkait ini ditampilkan di sini.

Anda sekarang harus tahu cara menggunakan gabungan untuk menggabungkan data dari beberapa tabel dan bagaimana setiap jenis gabungan berbeda. Kesimpulan:

  • INNER JOIN Atau JOIN Mengembalikan hanya rekaman dengan kunci yang cocok di kedua tabel.
  • LEFT JOIN Mengembalikan rekaman dari tabel pertama hanya jika mereka juga direferensikan oleh tabel kedua.
  • RIGHT JOIN Mengembalikan rekaman dari tabel kedua hanya jika mereka juga direferensikan oleh tabel pertama.
  • FULL OUTER JOIN Mengembalikan semua rekaman dari kedua tabel, meskipun tidak cocok di tabel lain.
  • WHERE Dapat memfilter hasil gabungan untuk hanya menampilkan catatan dengan NULL kunci
  • UNION Itu dapat menggabungkan hasil dari dua kueri menjadi satu set hasil.

Dengan pemahaman yang baik tentang gabungan, Anda sudah siap untuk melakukan kueri yang kuat dan efisien di MySQL.

Butuh database yang kompatibel dengan MySQL yang kuat?



Source link

Tinggalkan Balasan

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *