MySQL Data Add Statement

After the database and tables have been created successfully, you need to insert data into the tables in the database. In MySQL, you can use INSERT statements to insert one or more rows of tuple data into tables that already exist in the database.

Basic Grammar

The INSERT statement has two syntax forms, INSERT... VALUES statement and INSERT... SET statement.

1) INSERT...VALUES statement
Grammar:

INSERT INTO <Table Name> [ <Column Name 1> [ , ... <Column Name n>] ]
VALUES (Value 1) [... , (value n) ];

The syntax is described below.

• <Table Name>: Specify the name of the table being operated on.
• <Column Name>: Specifies the column name to insert data into. If you insert data into all columns in a table, all column names can be omitted, using INSERT<Table Name>VALUES(...) directly.
• VALUES or VALUE clause: This clause contains the list of data to be inserted. The order of data in the list corresponds to the order of columns.

Case:

Specify that all fields of the table insert one piece of data

INSERT INTO tb_courses
(course_id,course_name,course_grade,course_info)
VALUES(1,'Network',3,'Computer Network');

Specify that all fields of the table insert multiple pieces of data

INSERT INTO tb_courses(course_id,course_name,course_grade,course_info)
VALUES(1,'Network',3,'Computer Network'),(1,'Network',3,'Computer Network'),(1,'Network',3,'Computer Network'),(1,'Network',3,'Computer Network');
Query OK, 1 rows affected (0.08 sec)

Do not specify all field inserts for the table

mysql> INSERT INTO tb_courses
    -> (course_grade,course_info)
    -> VALUES(3,'Computer Network');
Query OK, 1 rows affected (0.08 sec)

If you specify a primary key self-incrementing column, you need to give a default value, (mysql is automatically supplemented if you don't specify one)

mysql> INSERT INTO tb_courses
    -> (id,course_grade,course_info)
    -> VALUES(default,3,'Computer Network');
Query OK, 1 rows affected (0.08 sec)

If it's a time field and you don't want the field data to be 0000-00-00 00:00:00, we can use now() to get the current time to fill in

mysql> INSERT INTO tb_courses
    -> (course_grade,cdateTime)
    -> VALUES(3,now());
Query OK, 1 rows affected (0.08 sec)

2) INSERT...SET statement
Grammar:

INSERT INTO <Table Name>
SET <Column Name 1> = <Value 1>,
    <Column Name 2> = <Value 2>,
        ..................

This statement is used to directly specify the corresponding column values for some columns in a table, that is, the column names of the data to be inserted are specified in the SET clause, the col_name is the specified column name, the data specified after the equal sign, and the column values are specified as the default values for unspecified columns.

case

insert into t_student 
set name='Ha-ha', age=21;

Use less, let's not say much here. Basic considerations are the same as the above grammar

Matters needing attention

• Use the INSERT...VALUES statement to insert one or more rows of data into the table.
• The INSERT...VALUES statement can insert multiple pieces of data at once.
• Use the INSERT...SET statement to specify the value of each column in the insertion row or the value of the insertion part of the column.
• Inserting a single piece of data with an INSERT... SET... statement is faster than using an INSERT... VALUES... statement. But in actual development, INSERT... VALUES is basically used.
• Specifies that fields added to the display can be automatically populated using default if there is a default value
• The order of column names after the INSERT statement may not be the order in which the tb_courses table is defined, that is, when inserting data, it is not necessary to insert in the order defined by the table, as long as the order of values is the same as that of column fields.

Complex Insert Case

Some fields require data calculated from multiple tables, which requires familiarity with inline and duplicate queries

Case 1

INSERT INTO `t_student_gradeclass_course` 
(`id`, `studentID`, `gradeClassID`, `t_course`) 
VALUES (1, (SELECT id FROM t_student where id=1 ), 11, 2);

Tip Zan - Collection - Attention - Easy to review and receive the latest content in the future There are other questions discussed in the commentary area - or trust me privately - that will be answered at the first time In case of infringement, please contact me privately Thank you, cooperate, I hope my efforts will help you ^^