Ringkasan. dalam tutorial ini, Anda akan belajar bagaimana menggunakan pernyataan MySQL
INSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)0 untuk memasukkan data ke dalam tabel, di mana data berasal dari hasil pernyataanINSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)1MySQL
INSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)_0 IkhtisarDalam tutorial sebelumnya, Anda telah mempelajari cara menyisipkan satu atau lebih baris ke dalam tabel menggunakan pernyataan
INSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)3 dengan daftar nilai kolom yang ditentukan dalam klausaINSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)4INSERT INTO table_name(c1,c2,...) VALUES(v1,v2,..);
Code language: SQL (Structured Query Language) (sql)Selain menggunakan nilai baris dalam klausa
INSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)4, Anda dapat menggunakan hasil dari pernyataanINSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)6 sebagai sumber data untuk pernyataanINSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)7Berikut ini mengilustrasikan sintaks pernyataan
INSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)0INSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Dalam sintaks ini, alih-alih menggunakan klausa
INSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)4, Anda dapat menggunakan pernyataanINSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)1. PernyataanINSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)1 dapat mengambil data dari satu atau lebih tabelPernyataan
INSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)0 sangat berguna saat Anda ingin menyalin data dari tabel lain ke tabel atau meringkas data dari beberapa tabel ke dalam tabelMySQL
INSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)_0 contohPertama, buat tabel baru bernama
CREATE TABLE suppliers ( supplierNumber INT AUTO_INCREMENT, supplierName VARCHAR(50) NOT NULL, phone VARCHAR(50), addressLine1 VARCHAR(50), addressLine2 VARCHAR(50), city VARCHAR(50), state VARCHAR(50), postalCode VARCHAR(50), country VARCHAR(50), customerNumber INT, PRIMARY KEY (supplierNumber) );
Code language: SQL (Structured Query Language) (sql)4CREATE TABLE suppliers ( supplierNumber INT AUTO_INCREMENT, supplierName VARCHAR(50) NOT NULL, phone VARCHAR(50), addressLine1 VARCHAR(50), addressLine2 VARCHAR(50), city VARCHAR(50), state VARCHAR(50), postalCode VARCHAR(50), country VARCHAR(50), customerNumber INT, PRIMARY KEY (supplierNumber) );
Code language: SQL (Structured Query Language) (sql)Perhatikan bahwa Anda akan belajar cara membuat tabel baru di tutorial berikutnya. Untuk saat ini, Anda hanya perlu menjalankan pernyataan ini untuk membuat tabel
CREATE TABLE suppliers ( supplierNumber INT AUTO_INCREMENT, supplierName VARCHAR(50) NOT NULL, phone VARCHAR(50), addressLine1 VARCHAR(50), addressLine2 VARCHAR(50), city VARCHAR(50), state VARCHAR(50), postalCode VARCHAR(50), country VARCHAR(50), customerNumber INT, PRIMARY KEY (supplierNumber) );
Code language: SQL (Structured Query Language) (sql)4Misalkan semua pelanggan dari
CREATE TABLE suppliers ( supplierNumber INT AUTO_INCREMENT, supplierName VARCHAR(50) NOT NULL, phone VARCHAR(50), addressLine1 VARCHAR(50), addressLine2 VARCHAR(50), city VARCHAR(50), state VARCHAR(50), postalCode VARCHAR(50), country VARCHAR(50), customerNumber INT, PRIMARY KEY (supplierNumber) );
Code language: SQL (Structured Query Language) (sql)_6 menjadi pemasok perusahaan. Kueri berikut menemukan semua pelanggan yang berlokasi di California, ASSELECT customerNumber, customerName, phone, addressLine1, addressLine2, city, state, postalCode, country FROM customers WHERE country = 'USA' AND state = 'CA';
Kedua, gunakan pernyataan
CREATE TABLE suppliers ( supplierNumber INT AUTO_INCREMENT, supplierName VARCHAR(50) NOT NULL, phone VARCHAR(50), addressLine1 VARCHAR(50), addressLine2 VARCHAR(50), city VARCHAR(50), state VARCHAR(50), postalCode VARCHAR(50), country VARCHAR(50), customerNumber INT, PRIMARY KEY (supplierNumber) );
Code language: SQL (Structured Query Language) (sql)7 untuk menyisipkan pelanggan yang berlokasi diCREATE TABLE suppliers ( supplierNumber INT AUTO_INCREMENT, supplierName VARCHAR(50) NOT NULL, phone VARCHAR(50), addressLine1 VARCHAR(50), addressLine2 VARCHAR(50), city VARCHAR(50), state VARCHAR(50), postalCode VARCHAR(50), country VARCHAR(50), customerNumber INT, PRIMARY KEY (supplierNumber) );
Code language: SQL (Structured Query Language) (sql)8 dari tabelCREATE TABLE suppliers ( supplierNumber INT AUTO_INCREMENT, supplierName VARCHAR(50) NOT NULL, phone VARCHAR(50), addressLine1 VARCHAR(50), addressLine2 VARCHAR(50), city VARCHAR(50), state VARCHAR(50), postalCode VARCHAR(50), country VARCHAR(50), customerNumber INT, PRIMARY KEY (supplierNumber) );
Code language: SQL (Structured Query Language) (sql)9 ke dalam tabelCREATE TABLE suppliers ( supplierNumber INT AUTO_INCREMENT, supplierName VARCHAR(50) NOT NULL, phone VARCHAR(50), addressLine1 VARCHAR(50), addressLine2 VARCHAR(50), city VARCHAR(50), state VARCHAR(50), postalCode VARCHAR(50), country VARCHAR(50), customerNumber INT, PRIMARY KEY (supplierNumber) );
Code language: SQL (Structured Query Language) (sql)4INSERT INTO suppliers ( supplierName, phone, addressLine1, addressLine2, city, state, postalCode, country, customerNumber ) SELECT customerName, phone, addressLine1, addressLine2, city, state , postalCode, country, customerNumber FROM customers WHERE country = 'USA' AND state = 'CA';
Code language: SQL (Structured Query Language) (sql)Itu mengembalikan pesan berikut yang menunjukkan bahwa 11 baris telah berhasil dimasukkan
11 row(s) affected Records: 11 Duplicates: 0 Warnings: 0
Code language: SQL (Structured Query Language) (sql)Ketiga, verifikasi penyisipan dengan meminta data dari tabel
CREATE TABLE suppliers ( supplierNumber INT AUTO_INCREMENT, supplierName VARCHAR(50) NOT NULL, phone VARCHAR(50), addressLine1 VARCHAR(50), addressLine2 VARCHAR(50), city VARCHAR(50), state VARCHAR(50), postalCode VARCHAR(50), country VARCHAR(50), customerNumber INT, PRIMARY KEY (supplierNumber) );
Code language: SQL (Structured Query Language) (sql)4SELECT * FROM suppliers;
Code language: SQL (Structured Query Language) (sql)Inilah hasilnya
Menggunakan
INSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)1 pernyataan dalam daftarINSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)4Pertama, buat tabel baru bernama ________20______4
CREATE TABLE stats ( totalProduct INT, totalCustomer INT, totalOrder INT );
Code language: SQL (Structured Query Language) (sql)Kedua, gunakan pernyataan
INSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)_3 untuk menyisipkan nilai yang berasal dari pernyataanINSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)1INSERT INTO stats(totalProduct, totalCustomer, totalOrder) VALUES( (SELECT COUNT(*) FROM products), (SELECT COUNT(*) FROM customers), (SELECT COUNT(*) FROM orders) );
Code language: SQL (Structured Query Language) (sql)Dalam contoh ini
- Pertama, gunakan pernyataan
INSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)_1 dengan fungsiSELECT customerNumber, customerName, phone, addressLine1, addressLine2, city, state, postalCode, country FROM customers WHERE country = 'USA' AND state = 'CA';
Code language: SQL (Structured Query Language) (sql)8 untuk mendapatkan total produk, karyawan, dan pesanan - Kedua, gunakan nilai yang dikembalikan dari pernyataan ________0______6 sebagai ganti nilai dalam klausa
INSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)4 dari pernyataanINSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)3
Ketiga, kueri data dari tabel
SELECT customerNumber, customerName, phone, addressLine1, addressLine2, city, state, postalCode, country FROM customers WHERE country = 'USA' AND state = 'CA';
Code language: SQL (Structured Query Language) (sql)4SELECT * FROM stats;
Code language: SQL (Structured Query Language) (sql)_Dalam tutorial ini, Anda telah mempelajari cara menggunakan pernyataan MySQL
INSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
Code language: SQL (Structured Query Language) (sql)0 untuk memasukkan data ke dalam tabel dari kumpulan hasil.