Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to add new records for mysql. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

The method of adding records to mysql: 1. Use "INSERT INTO table name column name [, … column name n]] VALUES (value 1) […, (value n);" statement; 2, use "INSERT INTO table name SET column name 1 = value 1, column name 2 = value 2..." Statement.

A MySQL data table is made up of rows and columns. The "column" of the table is usually called a Field, and the "row" of a table is called a Record.

After the database and table are created successfully, you need to insert data into the table in the database. In MySQL, you can use the INSERT statement to insert one or more rows of records into an existing table in the database.

Basic grammar

There are two grammatical forms of INSERT statements, which are INSERT. VALUES statement and INSERT... SET statement.

1) INSERT... VALUES statement

The syntax format of INSERT VALUES is:

INSERT INTO [,... ]] VALUES (value 1) [… , (value n)]

The syntax is explained as follows.

Specifies the name of the table being manipulated

Specifies the name of the column in which the data needs to be inserted If you insert data into all the columns in the table, all column names can be omitted and directly use INSERTVALUES (...) That's it.

VALUES or VALUE clause: this clause contains the list of data to insert. The order of the data in the data list corresponds to the order of the columns.

2) INSERT... SET statement

The syntax format is:

INSERT INTO SET =, =,...

This statement is used to directly assign the corresponding column value to some columns in the table, that is, the column name of the data to be inserted is specified in the SET clause, col_name is the specified column name, the equal sign is followed by the specified data, and for unspecified columns, the column value is specified as the default value for that column.

You can see from the two forms of the INSERT statement:

Use INSERT... The VALUES statement can insert a row of data into a table or multiple rows of data

Use INSERT... The SET statement can specify the value of each column in the inserted row, or you can specify the value of some columns

INSERT... The SELECT statement inserts data from other tables into the table.

Adopt INSERT … The SET statement can insert values of some columns into the table, which is more flexible.

INSERT... VALUES statements can insert more than one piece of data at a time.

In MySQL, it is faster to handle multiple inserts with a single INSERT statement than with multiple INSERT statements.

When inserting multiple rows of data using a single INSERT statement, you only need to enclose each row in parentheses.

Add values to all fields in the table

Create a course information table tb_courses in the test_db database, including course number course_id, course name course_name, course credits course_grade, and course Notes course_info. The SQL statements entered and the results of the execution are shown below.

Mysql > CREATE TABLE tb_courses-> (- > course_id INT NOT NULL AUTO_INCREMENT,-> course_name CHAR (40) NOT NULL,-> course_grade FLOAT NOT NULL,-> course_info CHAR (100th) NULL,-> PRIMARY KEY (course_id)->); Query OK, 0 rows affected (0.00 sec)

There are two ways to insert values into all fields in a table: one is to specify all field names, and the other is to specify no field names at all.

[example 1] insert a new record in the tb_courses table with a course_id value of 1 and a Network value of "Network" and a course_grade value of 3 and a value of "Computer Network"

Before performing the insert operation, look at the SQL statement of the tb_courses table and the execution results are shown below.

Mysql > SELECT * FROM tb_courses;Empty set (0.00 sec)

The query results show that the content of the current table is empty and there is no data, and then the operation of inserting data is performed. The input SQL statement and execution process are shown below.

Mysql > INSERT INTO tb_courses-> (course_id,course_name,course_grade,course_info)-> VALUES; Query OK, 1 rows affected (0.08 sec) mysql > SELECT * FROM tb_courses +-+ | course_id | course_name | course_grade | course_info | +-+ -+ | 1 | Network | 3 | Computer Network | +-+ 1 row in set (0.00 sec)

You can see that the record was inserted successfully. When you insert data, all fields of the tb_courses table are specified, so a new value is inserted for each field.

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, you do not need to insert data in the order defined by the table, as long as you ensure that the order of the values is the same as the order of the column fields.

[example 2] insert a new record in the tb_courses table with a course_id value of 2, a course_grade value of 3, and an info value of "MySQL". The SQL statement entered and the execution result are shown below.

Mysql > INSERT INTO tb_courses-> (course_name,course_info,course_id,course_grade)-> VALUES ('Database','MySQL',2,3); Query OK, 1 rows affected (0.08 sec) mysql > SELECT * FROM tb_courses +-+ | course_id | course_name | course_grade | course_info | +-+ -+ | 1 | Network | 3 | Computer Network | | 2 | Database | 3 | MySQL | +-+ 2 rows in set (0.00 sec)

When inserting data using INSERT, the column name list column_list is allowed to be empty, where values need to be specified for each field in the table, and the order of values must be the same as when the fields in the data table were defined.

[example 3] insert a new record in the tb_courses table with a course_id value of 3 and a value of "Java" and a course_grade value of 4 and a value of "Jave EE". The SQL statement entered and the execution result are shown below.

Mysql > INSERT INTO tb_courses-> VLAUES; Query OK, 1 rows affected (0.08 sec) mysql > SELECT * FROM tb_courses +-+ | course_id | course_name | course_grade | course_info | +-+ -+ | 1 | Network | 3 | Computer Network | | 2 | Database | 3 | MySQL | | 3 | Java | 4 | Java EE | +- -+-+ 3 rows in set (0.00 sec)

There is no insert list specified in the INSERT statement, only a list of values. In this case, the values list specifies the inserted values for each field column, and the order of these values must be the same as the order defined by the fields in the tb_courses table.

Note: although the column name of the inserted data can be ignored when inserting data using INSERT, if the value does not contain a column name, the values after the VALUES keyword must not only be complete, but must also be in the same order as the columns when the table was defined. If the structure of the table is modified, columns are added, deleted, or relocated, which will change the order in which data is inserted in this way. If you specify a column name, it is not affected by changes in the table structure.

On how to add new records of mysql to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.