Berikut adalah sintaks kueri SQL umum untuk menggabungkan tiga tabel atau lebih. Permintaan SQL ini harus bekerja di semua database relasional utama seperti MySQL, Oracle, Microsoft SQLServer, Sybase, dan PostgreSQL
Ringkasan. dalam tutorial ini, Anda akan mempelajari cara menggunakan MySQL
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)1 tindakan referensial untuk kunci asing untuk menghapus data dari beberapa tabel terkaitDalam tutorial sebelumnya, Anda belajar cara menghapus data dari beberapa tabel terkait menggunakan satu pernyataan
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)2. Namun, MySQL menyediakan cara yang lebih efektif yang disebutCREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)1 tindakan referensial untuk kunci asing yang memungkinkan Anda menghapus data dari tabel anak secara otomatis saat Anda menghapus data dari tabel indukMySQL
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)_1 contohMari kita lihat contoh penggunaan MySQL
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)1Misalkan kita memiliki dua tabel.
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)6 danCREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)7. Dalam model database ini, setiap gedung memiliki satu atau banyak ruangan. Namun, setiap kamar hanya dimiliki oleh satu bangunan. Sebuah ruangan tidak akan ada tanpa bangunanHubungan antara tabel
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)6 danCREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)7 adalah satu-ke-banyak (1. N) seperti yang diilustrasikan dalam diagram database berikutSaat Anda menghapus baris dari tabel
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)6, Anda juga ingin menghapus semua baris di tabelCREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)7 yang merujuk ke baris di tabelCREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)6 sebagai kueri berikutDELETE FROM buildings WHERE building_no = 2;
Code language: SQL (Structured Query Language) (sql)Anda juga ingin baris di tabel
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)_7 yang mengacu pada bangunan nomor 2 juga akan dihapusBerikut adalah langkah-langkah yang menunjukkan cara kerja
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)1 tindakan referensialLangkah 1. Buat tabel
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)6CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)Langkah 2. Buat tabel
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)_7CREATE TABLE rooms ( room_no INT PRIMARY KEY AUTO_INCREMENT, room_name VARCHAR(255) NOT NULL, building_no INT NOT NULL, FOREIGN KEY (building_no) REFERENCES buildings (building_no) ON DELETE CASCADE );
Code language: SQL (Structured Query Language) (sql)Perhatikan bahwa klausa
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)1 di akhir definisi batasan kunci asingLangkah 3. Sisipkan baris ke dalam tabel
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)6INSERT INTO buildings(building_name,address) VALUES('ACME Headquaters','3950 North 1st Street CA 95134'), ('ACME Sales','5000 North 1st Street CA 95134');
Langkah 4. Data kueri dari tabel
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)6SELECT * FROM buildings;
Code language: SQL (Structured Query Language) (sql)Kami memiliki dua baris di tabel
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)6.Langkah 5. Sisipkan baris ke dalam tabel
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)_7INSERT INTO rooms(room_name,building_no) VALUES('Amazon',1), ('War Room',1), ('Office of CEO',1), ('Marketing',2), ('Showroom',2);
Code language: SQL (Structured Query Language) (sql)Langkah 6. Data kueri dari tabel
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)_7SELECT * FROM rooms;
Code language: SQL (Structured Query Language) (sql)Kami memiliki tiga kamar milik gedung no 1 dan dua kamar milik gedung no 2.
Langkah 7. Hapus gedung dengan no gedung. 2
DELETE FROM buildings WHERE building_no = 2;
Code language: SQL (Structured Query Language) (sql)Langkah 8. Kueri data dari tabel
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)_7SELECT * FROM rooms;
Code language: SQL (Structured Query Language) (sql)Seperti yang Anda lihat, semua baris yang merujuk ke
INSERT INTO buildings(building_name,address) VALUES('ACME Headquaters','3950 North 1st Street CA 95134'), ('ACME Sales','5000 North 1st Street CA 95134');
Perhatikan bahwa
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)1 hanya berfungsi dengan tabel dengan mesin penyimpanan yang mendukung kunci asing e. g. , InnoDBBeberapa jenis tabel tidak mendukung kunci asing seperti MyISAM sehingga Anda harus memilih mesin penyimpanan yang sesuai untuk tabel yang Anda rencanakan untuk menggunakan MySQL
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)1 tindakan referensialKiat untuk menemukan tabel yang terpengaruh oleh tindakan MySQL
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)1Terkadang, mengetahui tabel mana yang terpengaruh oleh
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)1 tindakan referensial saat Anda menghapus data dari tabel merupakan hal yang berguna. Anda dapat meminta data ini dariSELECT * FROM buildings;
Code language: SQL (Structured Query Language) (sql)_0 diSELECT * FROM buildings;
Code language: SQL (Structured Query Language) (sql)1 database sebagai berikutUSE information_schema; SELECT table_name FROM referential_constraints WHERE constraint_schema = 'database_name' AND referenced_table_name = 'parent_table' AND delete_rule = 'CASCADE'
Code language: SQL (Structured Query Language) (sql)Misalnya, untuk menemukan tabel yang terkait dengan tabel ________0______6 dengan aturan penghapusan
SELECT * FROM buildings;
Code language: SQL (Structured Query Language) (sql)3 dalam databaseSELECT * FROM buildings;
Code language: SQL (Structured Query Language) (sql)4, Anda menggunakan kueri berikutCREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)0Dalam tutorial ini, Anda telah mempelajari cara menggunakan MySQL
CREATE TABLE buildings ( building_no INT PRIMARY KEY AUTO_INCREMENT, building_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)1 tindakan referensial untuk kunci asing untuk menghapus data secara otomatis dari tabel anak saat Anda menghapus data dari tabel induk.