Masukkan data dari tabel lain php

Question

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;
 Table of Contents Show
 MySQL INSERT INTO table_name(column_list)
SELECT 
   select_list 
FROM 
   another_table
WHERE
   condition;Code language: SQL (Structured Query Language) (sql)_0 Ikhtisar
MySQL INSERT INTO table_name(column_list)
SELECT 
   select_list 
FROM 
   another_table
WHERE
   condition;Code language: SQL (Structured Query Language) (sql)_0 contoh
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 daftar INSERT INTO table_name(column_list)
SELECT 
   select_list 
FROM 
   another_table
WHERE
   condition;Code language: SQL (Structured Query Language) (sql)4
Bagaimana cara memasukkan data dari satu tabel ke tabel lainnya di MySQL?
Bagaimana cara memasukkan id dari satu tabel ke tabel lainnya di PHP?
Bagaimana cara mendapatkan data dari tabel lain menggunakan kunci asing di PHP?
Bagaimana cara mengambil data dari database di PHP dan ditampilkan di halaman lain?
 
Code language: SQL (Structured Query Language) (sql)

0 untuk memasukkan data ke dalam tabel, di mana data berasal dari hasil pernyataan

INSERT INTO table_name(column_list)
SELECT 
   select_list 
FROM 
   another_table
WHERE
   condition;
Code language: SQL (Structured Query Language) (sql)

1

MySQL INSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;Code language: SQL (Structured Query Language) (sql)_0 Ikhtisar

Dalam 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 klausa

INSERT INTO table_name(column_list)
SELECT 
   select_list 
FROM 
   another_table
WHERE
   condition;
Code language: SQL (Structured Query Language) (sql)

4

INSERT 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 pernyataan

INSERT 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 pernyataan

INSERT INTO table_name(column_list)
SELECT 
   select_list 
FROM 
   another_table
WHERE
   condition;
Code language: SQL (Structured Query Language) (sql)

7

Berikut 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)

0

INSERT INTO table_name(column_list)
SELECT 
   select_list 
FROM 
   another_table
WHERE
   condition;
Code language: SQL (Structured Query Language) (sql)

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 pernyataan

INSERT INTO table_name(column_list)
SELECT 
   select_list 
FROM 
   another_table
WHERE
   condition;
Code language: SQL (Structured Query Language) (sql)

1. Pernyataan

INSERT 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 tabel

Pernyataan

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 tabel

MySQL INSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;Code language: SQL (Structured Query Language) (sql)_0 contoh

Pertama, 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)

4

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)

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)

4

Misalkan 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, AS

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)

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 di

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)

8 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)

9 ke dalam 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)

4

INSERT 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)

4

SELECT * 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 daftar INSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;Code language: SQL (Structured Query Language) (sql)4

Pertama, 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 pernyataan

INSERT INTO table_name(column_list)
SELECT 
   select_list 
FROM 
   another_table
WHERE
   condition;



Code language: SQL (Structured Query Language) (sql)

1

INSERT 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 fungsi

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)

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 pernyataan

INSERT 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)

4

SELECT * 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.

Bagaimana cara memasukkan data dari satu tabel ke tabel lainnya di MySQL?

Pernyataan INSERT INTO SELECT menyalin data dari satu tabel dan menyisipkannya ke tabel lain. Pernyataan INSERT INTO SELECT mensyaratkan bahwa tipe data dalam tabel sumber dan target cocok. Catatan. Rekaman yang ada di tabel target tidak terpengaruh