MySQL can be inserted into the specified table based on the results of the query

MySQL can be inserted into the specified table based on the set of records it queries to:

First, the structure of three tables is elaborated:

S (sno,sname.sex,age,dept)//Student Information Sheet

C (cno,cname,tname)//Course Information Sheet

SC (Sno,cno,grade)//Course Elective Information sheet

So now the question is:

To insert a student's number, name, and name into an existing (to be established) basic table Stu (Sno,sname,dept) in a class with a score of 80 or higher.

Stu table already exists the database schema is:

<span style= "Font-size:18px;color: #FF0000;" >drop table if EXISTS stu; CREATE table Stu (  Sno Int (4) Unique,  sname varchar (),  dept varchar (TEN));< /SPAN>

Now write the SQL statement for 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 &G t;=);</span>

Next is the syntax rule for the SQL statement above:

INSERT into Table name 1 (field List 1)       SELECT field List 2 from table name 2 WHERE conditional expression;

• Table Name 1: Indicates which table the record is inserted into.
• Field List 1: Indicates which fields are assigned values.
• Field List 2: Data that represents which fields are queried from the table.
• Table Name 2: Indicates which table the record was queried from.
• Conditional expression: Represents the query condition that sets the SELECT statement.

When you insert a record that is queried in table name 2 into table name 1, the original record is still saved in table name 2.

Tips:

With this approach, you must ensure that the number of fields in field List 1 and field List 2 is the same, and that each corresponding field must have the same data type. If the data type is different, the database system will error. Then, prevent the INSERT statement from executing down.

MySQL can be inserted into the specified table based on the results of the query