The UPDATE statement of the MySQL database perspective

The SQL used to manipulate the database is generally divided into two types, one is the query statement, which we call the SELECT statement, the other is the UPDATE statement, also known as the data manipulation statement. The implication is that the data is modified. There are 3 statements in standard SQL, which are insert, update, and delete. There is another replace statement in MySQL, so this article takes MySQL as the background to discuss how to make an UPDATE statement in SQL.

First, insert and replace

The INSERT and replace statements have the ability to insert new data into a table. The syntax for these two statements is similar. The main difference between them is how to handle duplicate data.

1. General usage of Inserts

Insert statements in MySQL are not the same as standard inserts, and in standard SQL statements there is only one form of INSERT statement that inserts one record at a time.

INSERT into tablename (column name ...) Values (column value);

And there's another form in MySQL.

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

The first method separates the column names from the column values, and when used, the column names must be the same as the number of column values. As the following statement inserts a record into the users table:

INSERT into the users (ID, name, age) VALUES (123, ' Yao ', 25);

The second method allows column names and column values to appear and be used in pairs, such as the following statement, which produces a medium-like effect.

INSERT into users SET ID = 123, name = ' Yao Ming ', age = 25;

If you use the Set method, you must assign at least one column. If a field uses a saved value (such as Default or self-increment), these fields can be omitted from either of these methods. The above two statements can be written in the following form, such as using the self-increment on the ID field:

INSERT into the 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 the new record using the default values for each column in the table.

INSERT into Users () VALUES ();

If you write nothing after the table name, you assign a value to all the fields in the table. In this way, not only values are consistent with the number of columns, but the order cannot be reversed. INSERT into Users VALUES (123, ' Yao ', 25);

If you write the INSERT statement in the following form, MySQL will make an error.

INSERT into Users VALUES (' Yao Ming ', 25);

2. Insert multiple records using insert

See this title maybe people will ask, what is there to say, invoke multiple INSERT statements can not insert more than one record? However, this method is used to increase the load on the server, because every time the SQL Server executes, the SQL is analyzed, optimized, and so on. Fortunately, MySQL offers another solution, which is to insert multiple records using an INSERT statement. This is not a standard SQL syntax, so it can only be used in MySQL.

INSERT into the users (name, age)

VALUES (' Yao Ming ', 25), (' Bill Gates ', 50), (' Martians ', 600);

The INSERT statement above inserts 3 consecutive records into the users table. It is worth noting that the values in the INSERT statement above must have the value of each record put on a pair of (...) In, the middle uses "," split. Suppose there is a table table1

CREATE TABLE table1 (n INT);

If you want to insert 5 records into the Table1, the following is the wrong way to write:

INSERT into table1 (i) VALUES (1,2,3,4,5);

MySQL will throw the following error

ERROR 1136:column Count doesn ' t match value count at row 1

And the right way to do that is:

INSERT into T Able1 (i) VALUES (1), (2), (3), (4), (5);

Of course, this type of writing can also omit the column name so that the number of values in each pair of parentheses must be the same, and that number must be consistent with the number of columns. Such as:

INSERT into T able1 VALUES (1), (2), (3), (4), (5);