Kami memiliki tabel Geeks2 yang memiliki dua baris di mana Col 1 adalah 21 & 31 dan kami ingin memperbarui nilai dari tabel Geeks2 ke tabel Geeks1 untuk baris di mana Col 1 adalah 21 dan 31. Juga, kami ingin memperbarui nilai Col 2 dan Col 3 saja
Di versi terbaru MySQL, Anda dapat melakukan pembaruan tabel silang, juga dikenal sebagai pembaruan tabel korelasi di mana Anda dapat menggabungkan dua tabel atau lebih. Menggunakan query MySQL JOIN (INNER dan LEFT) dan UPDATE, kita dapat melakukan pembaruan lintas tabel dalam langkah yang sangat sederhana
Tutorial ini akan memandu Anda tentang cara melakukan pembaruan tabel silang MySQL menggunakan perintah asli MySQL
Sintaks Dasar
Sintaks umum untuk kueri gabungan pembaruan MySQL dasar adalah
UPDATE db. tabel1, db. Meja 2,
[GABUNG KIRI. INNER JOIN] db. table1 ON db. Tabel 1. kolom1 = db. Meja 2. kolom2
SET db. Tabel 1. kolom2 = db. Meja 2. kolom2, db. Meja 2. kolom3 = Ekspresi
DIMANA [kondisi]
Mari kita pecahkan kueri di atas menjadi bagian-bagian yang lebih kecil sehingga kita dapat memahaminya dengan lebih baik
Kita mulai dengan mendefinisikan tabel utama. Dalam hal ini, (db. table1) diikuti dengan tabel yang ingin kita gabungkan menggunakan pernyataan update, dalam hal ini, db. Meja 2
CATATAN. Penting untuk menentukan setidaknya satu tabel setelah pernyataan pembaruan;
Di bagian kedua kueri, kami menentukan jenis GABUNG tertentu yang ingin kami lakukan, mis. e. , INNER atau LEFT JOIN dan predikat gabungan. Selalu setel kueri GABUNG segera setelah kueri PEMBARUAN
Predikat gabungan adalah kondisi GABUNG khusus yang mengevaluasi ke nilai Boolean
Pada bagian berikut, kami menetapkan nilai baru ke kolom db. table1 dan db. Meja 2
Terakhir, kami menetapkan kondisi menggunakan klausa WHERE, yang membantu membatasi baris yang mengalami pembaruan
Contoh kasus penggunaan
Misalkan Anda memiliki dua tabel, disebut tb1 dan tb2;
BUAT DATABASE JIKA TIDAK ADA sample_db;
GUNAKAN sample_db;
BUAT TABEL tb1 (
col1 INT(11) BUKAN NULL,
col2 INT BUKAN NULL,
KUNCI UTAMA (col1)
);
BUAT TABEL tb2 (
col1 INT NOT NULL AUTO_INCREMENT,
col2 VARCHAR(100) BUKAN NULL,
col3 INT DEFAULT NULL,
col4 INT DEFAULT NULL,
KUNCI UTAMA (col1)
);
INSERT INTO tb1(col1, col2) NILAI(1,0), (2,1),(3,3),(4,4),(5,5);
INSERT INTO tb2(col2,col3,col4) NILAI ("Pertama", 1, 500),("Kedua", 2, 1000),("Ketiga", 3, 1500),("Keempat", 4, 2000)
Pada tabel yang dibuat menggunakan kueri di atas, kita dapat melakukan kueri pembaruan menggunakan pernyataan INNER JOIN karena tabel ditautkan pada col2
Pertimbangkan kueri di bawah ini
GUNAKAN sample_db;
UPDATE tb2 INNER JOIN tb1 ON tb2. kol3 = tb1. col1 SET col3 = col4 + col4 * 5;
Karena kita ingin mengupdate data pada tabel kedua (col3) dimana kita mendapatkan nilai kolom dan mengalikannya dengan 5, pada contoh di atas kita menghilangkan kondisi WHERE karena kita ingin mengupdate semua record pada tabel yang ditentukan.
Anda dapat mengonfirmasi ini menggunakan kueri pemilihan seperti yang ditunjukkan di bawah ini
PILIH * DARI sample_db. tb2;
Outputnya seperti yang ditunjukkan di bawah ini
mysql> PILIH * DARI sample_db. tb2;
+------+--------+-------+------+
col1. col2. col3. col4
+------+--------+-------+------+
1. Pertama. 3000. 500
2. Kedua. 6000. 1000
3. Ketiga. 9000. 1500
4. Keempat. 12000. 2000
5. Kelima. 15000. 2500
+------+--------+-------+------+
5 baris dalam set (0. 00 detik)
Kesimpulan
Dalam tutorial singkat ini, kita membahas cara menggunakan kueri gabungan pembaruan MySQL yang memungkinkan Anda menggabungkan tabel dan memperbarui nilai yang sesuai
Kami biasanya menggunakan joins untuk melewati baris dalam tabel tertentu yang memiliki atau mungkin tidak memiliki baris serupa di beberapa tabel lain. Kita dapat menggunakan klausa UPDATE Table_1, Table_2 SET Table_1.column_2 = Table_2.column_2, Table_2.column_3 = expr WHERE Table_1.column_1 = Table_2.column_1 AND condition _0 di samping pernyataan UPDATE Table_1, Table_2 SET Table_1.column_2 = Table_2.column_2, Table_2.column_3 = expr WHERE Table_1.column_1 = Table_2.column_1 AND condition 1 untuk mengimplementasikan beberapa pembaruan tabel
Sintaks dasar MySQL UPDATE JOIN dapat digambarkan sebagai berikut
UPDATE Table_1, Table_2, [INNER JOIN] Table_1 ON Table_1.Column_1 = Table_2. Column_1 SET Table_1.Column_2 = Table_2.Column_2, Table_2.Column_3 = expression WHERE condition
Sintaks di atas berfungsi sebagai berikut untuk tabel tertentu di MySQL
- Kami memulai pekerjaan kami dengan menentukan tabel utama yang disebut UPDATE Table_1, Table_2 SET Table_1.column_2 = Table_2.column_2, Table_2.column_3 = expr WHERE Table_1.column_1 = Table_2.column_1 AND condition 3 dan tabel yang ingin kami gabungkan dengan tabel utama sebagai UPDATE Table_1, Table_2 SET Table_1.column_2 = Table_2.column_2, Table_2.column_3 = expr WHERE Table_1.column_1 = Table_2.column_1 AND condition 4. Tabel yang disebutkan dalam klausa UPDATE Table_1, Table_2 SET Table_1.column_2 = Table_2.column_2, Table_2.column_3 = expr WHERE Table_1.column_1 = Table_2.column_1 AND condition _1 diperbarui, dan data dalam tabel yang tidak disebutkan setelah klausa UPDATE Table_1, Table_2 SET Table_1.column_2 = Table_2.column_2, Table_2.column_3 = expr WHERE Table_1.column_1 = Table_2.column_1 AND condition 1 tidak akan diubah
- Setelah kami melakukannya, kami harus menyebutkan jenis gabungan yang ingin kami gunakan. Dalam sintaks di atas, kami telah menggunakan gabungan UPDATE Table_1, Table_2 SET Table_1.column_2 = Table_2.column_2, Table_2.column_3 = expr WHERE Table_1.column_1 = Table_2.column_1 AND condition 7. Penggabungan ini harus dilakukan segera setelah klausa UPDATE Table_1, Table_2 SET Table_1.column_2 = Table_2.column_2, Table_2.column_3 = expr WHERE Table_1.column_1 = Table_2.column_1 AND condition 1
- Setelah memberikan nilai ke kolom yang ingin kami perbarui, kami menyebutkan klausa UPDATE Table_1, Table_2 SET Table_1.column_2 = Table_2.column_2, Table_2.column_3 = expr WHERE Table_1.column_1 = Table_2.column_1 AND condition 9 untuk menentukan kondisi tertentu untuk pembaruan
Ada satu lagi cara menggunakan metode ini yang dapat diilustrasikan sebagai berikut
UPDATE Table_1, Table_2 SET Table_1.column_2 = Table_2.column_2, Table_2.column_3 = expr WHERE Table_1.column_1 = Table_2.column_1 AND condition
Sebelum kita mulai, mari kita buat dua tabel untuk dikerjakan. Kami akan memanggil tabel ini CREATE TABLE marks ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) ); CREATE TABLE student_details ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, total FLOAT DEFAULT NULL, PRIMARY KEY (emp_id), CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES marks (performance) ); INSERT INTO marks(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); INSERT INTO student_details(stu_name,performance,total) VALUES('Preet Sanghavi', 1, 50000), ('Joe Sand', 3, 65000), ('Su Greens', 4, 75000), ('Gray Dellop', 5, 125000), ('Neon Jonty', 3, 85000), ('Peter Foe', 2, 45000), ('Little Wayne', 3, 55000); _0 dan CREATE TABLE marks ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) ); CREATE TABLE student_details ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, total FLOAT DEFAULT NULL, PRIMARY KEY (emp_id), CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES marks (performance) ); INSERT INTO marks(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); INSERT INTO student_details(stu_name,performance,total) VALUES('Preet Sanghavi', 1, 50000), ('Joe Sand', 3, 65000), ('Su Greens', 4, 75000), ('Gray Dellop', 5, 125000), ('Neon Jonty', 3, 85000), ('Peter Foe', 2, 45000), ('Little Wayne', 3, 55000); 1. Tabel ini dapat dibuat dengan kode berikut
CREATE TABLE marks ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) ); CREATE TABLE student_details ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, total FLOAT DEFAULT NULL, PRIMARY KEY (emp_id), CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES marks (performance) ); INSERT INTO marks(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); INSERT INTO student_details(stu_name,performance,total) VALUES('Preet Sanghavi', 1, 50000), ('Joe Sand', 3, 65000), ('Su Greens', 4, 75000), ('Gray Dellop', 5, 125000), ('Neon Jonty', 3, 85000), ('Peter Foe', 2, 45000), ('Little Wayne', 3, 55000);
Untuk memvisualisasikan tabel CREATE TABLE marks ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) ); CREATE TABLE student_details ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, total FLOAT DEFAULT NULL, PRIMARY KEY (emp_id), CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES marks (performance) ); INSERT INTO marks(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); INSERT INTO student_details(stu_name,performance,total) VALUES('Preet Sanghavi', 1, 50000), ('Joe Sand', 3, 65000), ('Su Greens', 4, 75000), ('Gray Dellop', 5, 125000), ('Neon Jonty', 3, 85000), ('Peter Foe', 2, 45000), ('Little Wayne', 3, 55000); 0, kami menggunakan kode berikut
SELECT * FROM student_details;
Kode di atas akan memberikan output berikut
stu_id stu_name stu_performance total 1 Preet Sanghavi 1 50000 2 Joe Sand 3 65000 3 Su Greens 4 75000 4 Gray Dellop 5 125000 5 Neon Jonty 3 85000 6 Peter Foe 2 45000 7 Little Wayne 3 55000
Demikian pula, kita dapat memvisualisasikan tabel CREATE TABLE marks ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) ); CREATE TABLE student_details ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, total FLOAT DEFAULT NULL, PRIMARY KEY (emp_id), CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES marks (performance) ); INSERT INTO marks(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); INSERT INTO student_details(stu_name,performance,total) VALUES('Preet Sanghavi', 1, 50000), ('Joe Sand', 3, 65000), ('Su Greens', 4, 75000), ('Gray Dellop', 5, 125000), ('Neon Jonty', 3, 85000), ('Peter Foe', 2, 45000), ('Little Wayne', 3, 55000); 1
Kode di atas akan memberikan output berikut
performance percentage 1 0 2 0.01 3 0.03 4 0.05 5 0.08
Seperti yang dapat kita lihat dari blok kode di atas, nilai persentase ada di tabel CREATE TABLE marks ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) ); CREATE TABLE student_details ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, total FLOAT DEFAULT NULL, PRIMARY KEY (emp_id), CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES marks (performance) ); INSERT INTO marks(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); INSERT INTO student_details(stu_name,performance,total) VALUES('Preet Sanghavi', 1, 50000), ('Joe Sand', 3, 65000), ('Su Greens', 4, 75000), ('Gray Dellop', 5, 125000), ('Neon Jonty', 3, 85000), ('Peter Foe', 2, 45000), ('Little Wayne', 3, 55000); 1, dan kita harus menggunakan UPDATE JOIN dengan CREATE TABLE marks ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) ); CREATE TABLE student_details ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, total FLOAT DEFAULT NULL, PRIMARY KEY (emp_id), CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES marks (performance) ); INSERT INTO marks(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); INSERT INTO student_details(stu_name,performance,total) VALUES('Preet Sanghavi', 1, 50000), ('Joe Sand', 3, 65000), ('Su Greens', 4, 75000), ('Gray Dellop', 5, 125000), ('Neon Jonty', 3, 85000), ('Peter Foe', 2, 45000), ('Little Wayne', 3, 55000); 6 sebagai gabungan utama kita untuk menyesuaikan CREATE TABLE marks ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) ); CREATE TABLE student_details ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, total FLOAT DEFAULT NULL, PRIMARY KEY (emp_id), CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES marks (performance) ); INSERT INTO marks(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); INSERT INTO student_details(stu_name,performance,total) VALUES('Preet Sanghavi', 1, 50000), ('Joe Sand', 3, 65000), ('Su Greens', 4, 75000), ('Gray Dellop', 5, 125000), ('Neon Jonty', 3, 85000), ('Peter Foe', 2, 45000), ('Little Wayne', 3, 55000); 7 dari setiap siswa di tabel CREATE TABLE marks ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) ); CREATE TABLE student_details ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, total FLOAT DEFAULT NULL, PRIMARY KEY (emp_id), CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES marks (performance) ); INSERT INTO marks(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); INSERT INTO student_details(stu_name,performance,total) VALUES('Preet Sanghavi', 1, 50000), ('Joe Sand', 3, 65000), ('Su Greens', 4, 75000), ('Gray Dellop', 5, 125000), ('Neon Jonty', 3, 85000), ('Peter Foe', 2, 45000), ('Little Wayne', 3, 55000); 0 berdasarkan nilai CREATE TABLE marks ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) ); CREATE TABLE student_details ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, total FLOAT DEFAULT NULL, PRIMARY KEY (emp_id), CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES marks (performance) ); INSERT INTO marks(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); INSERT INTO student_details(stu_name,performance,total) VALUES('Preet Sanghavi', 1, 50000), ('Joe Sand', 3, 65000), ('Su Greens', 4, 75000), ('Gray Dellop', 5, 125000), ('Neon Jonty', 3, 85000), ('Peter Foe', 2, 45000), ('Little Wayne', 3, 55000); 9 dan
Penting untuk dicatat bahwa penghubung antara dua tabel, CREATE TABLE marks ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) ); CREATE TABLE student_details ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, total FLOAT DEFAULT NULL, PRIMARY KEY (emp_id), CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES marks (performance) ); INSERT INTO marks(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); INSERT INTO student_details(stu_name,performance,total) VALUES('Preet Sanghavi', 1, 50000), ('Joe Sand', 3, 65000), ('Su Greens', 4, 75000), ('Gray Dellop', 5, 125000), ('Neon Jonty', 3, 85000), ('Peter Foe', 2, 45000), ('Little Wayne', 3, 55000); 0 dan CREATE TABLE marks ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) ); CREATE TABLE student_details ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, total FLOAT DEFAULT NULL, PRIMARY KEY (emp_id), CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES marks (performance) ); INSERT INTO marks(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); INSERT INTO student_details(stu_name,performance,total) VALUES('Preet Sanghavi', 1, 50000), ('Joe Sand', 3, 65000), ('Su Greens', 4, 75000), ('Gray Dellop', 5, 125000), ('Neon Jonty', 3, 85000), ('Peter Foe', 2, 45000), ('Little Wayne', 3, 55000); 1, adalah kolom SELECT * FROM student_details; 0
Sekarang mari kita lihat pernyataan UPDATE JOIN_ beraksi
Kami dapat melakukan operasi pembaruan seperti yang dijelaskan di atas menggunakan kueri berikut
UPDATE student_details INNER JOIN marks ON student_details.performance = marks.performance SET total = total + total * percentage;
Di sini, tabel CREATE TABLE marks ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) ); CREATE TABLE student_details ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, total FLOAT DEFAULT NULL, PRIMARY KEY (emp_id), CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES marks (performance) ); INSERT INTO marks(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); INSERT INTO student_details(stu_name,performance,total) VALUES('Preet Sanghavi', 1, 50000), ('Joe Sand', 3, 65000), ('Su Greens', 4, 75000), ('Gray Dellop', 5, 125000), ('Neon Jonty', 3, 85000), ('Peter Foe', 2, 45000), ('Little Wayne', 3, 55000); _0 adalah tabel utama di mana nilai CREATE TABLE marks ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) ); CREATE TABLE student_details ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, total FLOAT DEFAULT NULL, PRIMARY KEY (emp_id), CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES marks (performance) ); INSERT INTO marks(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); INSERT INTO student_details(stu_name,performance,total) VALUES('Preet Sanghavi', 1, 50000), ('Joe Sand', 3, 65000), ('Su Greens', 4, 75000), ('Gray Dellop', 5, 125000), ('Neon Jonty', 3, 85000), ('Peter Foe', 2, 45000), ('Little Wayne', 3, 55000); 7 perlu diperbarui
Karena kita menghilangkan klausa UPDATE Table_1, Table_2 SET Table_1.column_2 = Table_2.column_2, Table_2.column_3 = expr WHERE Table_1.column_1 = Table_2.column_1 AND condition _9 dalam pernyataan UPDATE Table_1, Table_2 SET Table_1.column_2 = Table_2.column_2, Table_2.column_3 = expr WHERE Table_1.column_1 = Table_2.column_1 AND condition 1 dalam kueri yang disebutkan di atas, semua catatan dalam tabel CREATE TABLE marks ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) ); CREATE TABLE student_details ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, total FLOAT DEFAULT NULL, PRIMARY KEY (emp_id), CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES marks (performance) ); INSERT INTO marks(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); INSERT INTO student_details(stu_name,performance,total) VALUES('Preet Sanghavi', 1, 50000), ('Joe Sand', 3, 65000), ('Su Greens', 4, 75000), ('Gray Dellop', 5, 125000), ('Neon Jonty', 3, 85000), ('Peter Foe', 2, 45000), ('Little Wayne', 3, 55000); 0 dapat diubah berdasarkan kondisi stu_id stu_name stu_performance total 1 Preet Sanghavi 1 50000 2 Joe Sand 3 65000 3 Su Greens 4 75000 4 Gray Dellop 5 125000 5 Neon Jonty 3 85000 6 Peter Foe 2 45000 7 Little Wayne 3 55000 1
Keluaran dari kueri yang disebutkan di atas akan memberikan hasil sebagai berikut
stu_id stu_name stu_performance total 1 Preet Sanghavi 1 50000 2 Joe Sand 3 66950 3 Su Greens 4 78750 4 Gray Dellop 5 135000 5 Neon Jonty 3 87550 6 Peter Foe 2 45450 7 Little Wayne 3 56650
Seperti yang dapat kita lihat pada blok kode di atas, CREATE TABLE marks ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) ); CREATE TABLE student_details ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, total FLOAT DEFAULT NULL, PRIMARY KEY (emp_id), CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES marks (performance) ); INSERT INTO marks(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); INSERT INTO student_details(stu_name,performance,total) VALUES('Preet Sanghavi', 1, 50000), ('Joe Sand', 3, 65000), ('Su Greens', 4, 75000), ('Gray Dellop', 5, 125000), ('Neon Jonty', 3, 85000), ('Peter Foe', 2, 45000), ('Little Wayne', 3, 55000); 7 dari setiap siswa diperbarui berdasarkan SELECT * FROM student_details; 0 di tabel CREATE TABLE marks ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) ); CREATE TABLE student_details ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, total FLOAT DEFAULT NULL, PRIMARY KEY (emp_id), CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES marks (performance) ); INSERT INTO marks(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); INSERT INTO student_details(stu_name,performance,total) VALUES('Preet Sanghavi', 1, 50000), ('Joe Sand', 3, 65000), ('Su Greens', 4, 75000), ('Gray Dellop', 5, 125000), ('Neon Jonty', 3, 85000), ('Peter Foe', 2, 45000), ('Little Wayne', 3, 55000); 1