Mysql can insert data to a specified table based on the query results.

Mysql can insert data to a specified table based on the query results.

MySQL can insert the queried record set to the specified table. The example is as follows:

First, describe the structure of the three tables:

S (sno, sname. sex, age, dept) // student information table

C (cno, cname, tname) // course information table

SC (sno, cno, grade) // optional course information table

Now the problem is:

Insert the student ID, name, and system name of a course score greater than or equal to 80 into an existing (to be created) basic table stu (sno, sname, dept ).

The existing database Schema of the stu table is:

 

<span style="font-size:18px;color:#FF0000;">drop table if EXISTS stu ;create table stu (  sno int(4) unique ,  sname varchar(20),  dept varchar(10));</span>

 

Now write the SQL statement of the result:

 

<span style="font-size:18px;color:#FF0000;">insert into stu (sno,sname,dept)select sno ,sname , dept from s wheres.sno in (select sc.sno from sc where sc.grade >= 80 );</span>

 

The following are the syntax rules of the preceding SQL statement:

 

Insert into table name 1 (Field List 1) SELECT Field List 2 FROM table name 2 WHERE condition expression;

Table Name 1: indicates the table to which the record is inserted. Field List 1: indicates which fields are assigned values. Field List 2: The data of the fields queried from the table. Table name 2: indicates the table from which the record is queried. Conditional expression: Specifies the query conditions for SELECT statements.

 

After the records queried in "table name 2" are inserted into "table name 1", the original records are still saved in "table name 2.

Tip:

When using this method, you must ensure that the number of fields in "Field List 1" and "Field List 2" is the same, make sure that the data type of each corresponding field is the same. If the data types are different, the database system reports an error. Then, the INSERT statement is blocked from being executed downward.