General INSERT usage in MySQL

INSERT statements are one of the most common SQL statements, but the use of INSERT statements in MySQL is different from the standard usage. The following describes the general usage of INSERT statements in MySQL for your reference.

General INSERT usage in MySQL:

The INSERT statement in MySQL is not the same as the standard INSERT statement. In the standard SQL statement, the INSERT statement that inserts a record at a time has only one form.

Insert into tablename (column name ...) VALUES (column value );

There is another form in MySQL.

Insert into tablename SET column_name1 = value1, column_name2 = value2 ,...;

The first method separates the column name from the column value. during use, the column name must be consistent with the number of column values. The following statement inserts a record into the users table:

Insert into users (id, name, age) VALUES (123, 'Yao Ming ', 25 );

The second method allows the column names and column values to appear and use in pairs. The following statement will produce the same effect.

Insert into users SET id = 123, name = 'Yao Ming ', age = 25;

If the SET method is used, a value must be assigned to at least one column. If a field uses a default value, for example, a default value or an auto-increment value, either of these fields can be omitted. If auto-increment is used in the id field, the preceding two statements can be written as follows:

Insert into users (name, age) VALUES ('Yao ming', 25 );

Insert into uses SET name = 'Yao Ming ', age = 25;

MySQL has also made some changes in VALUES. If nothing is written in VALUES, MySQL inserts a new record using the default value of each column in the table.

Insert into users () VALUES ();

If nothing is written after the table name, it indicates that all fields in the table are assigned a value. In this way, not only must the value in VALUES be consistent with the number of columns, but the order cannot be reversed. Insert into users VALUES (123, 'Yao ming', 25 );

If the INSERT statement is written as follows, MySQL will report an error.

Insert into users VALUES ('Yao Ming ', 25 );

MySQL index type

How to modify the order of mysql Fields

How to add or delete a primary key in mysql

How to Set MySql access restrictions

Six Methods for changing the password of MySQL