Use of insert into select syntax

In the database, the INSERT INTO SELECT statement is used to insert the results of a query into another table. The INSERT INTO SELECT statement can easily copy the contents of one table to another table, or create a new table based on query results.

The syntax of INSERT INTO SELECT is as follows:

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

This statement means to add column1 and column2 of rows in table1 that meet a certain condition. , column3 and other columns are inserted into the corresponding columns in table2.

The following is a practical example to illustrate the use of INSERT INTO SELECT.

Suppose we have two tables: students and new_students. The students table contains the following columns: id, name, age, score. We want to insert students who are 18 years or older in the students table into the new_students table.

First, we create the students table and insert some sample data:

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  score INT
);
INSERT INTO students (name, age, score)
VALUES ('John', 16, 90),
       ('Alice', 20, 95),
       ('Tom', 18, 85),
       ('Emily', 17, 88);

Next, we create the new_students table:

CREATE TABLE new_students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  score INT
);

Finally, we use the INSERT INTO SELECT statement to insert students Students who are 18 years or older in the table are inserted into the new_students table:

INSERT INTO new_students (name, age, score)
SELECT name, age, score
FROM students
WHERE age >= 18;

In this way, the new_students table contains the information of students who are 18 years or older in the students table.

In addition to directly inserting query results into another table, the INSERT INTO SELECT statement can also be used to create a new table containing only the query results:

CREATE TABLE new_table AS
SELECT column1, column2, ...
FROM table
WHERE condition;

In this way, new_table will be Create and include query results.

Summary

The INSERT INTO SELECT statement selects certain rows in one table (or query result) and inserts them into another table (or creates a new table). This function is very powerful and can easily copy, filter and create new tables.

The above is the detailed content of Use of insert into select syntax. For more information, please follow other related articles on the PHP Chinese website!