MySQL Insert/Update data

MySQL Insert/Update data

INSERT Statement

1. List the values of all fields at once, for example:

INSERT INTO student VALUES ('chenqi','M', $); INSERT INTO Student VALUES ('Bush',' m', ', '), (' Obama'm  ', 45);

Allows multiple rows of data to be inserted simultaneously;

2. Assign values only to partial fields

INSERT into student (name, sex) VALUES ('Abby'F'), (' Joseph ' ' M ');

Fields that do not appear in the INSERT statement are assigned a default value. Allows multiple rows of data to be inserted simultaneously;

3. Using the SET clause

INSERT into student SET name='Stein', sex=' M';

Fields that are not present in the SET clause are given default values. This form of Isnert statement does not allow multiple rows of data to be inserted at one time.

REPLACE Statement

The Replace statement is very similar to the INSERT statement usage, except that when duplicate values are present in a unique index or primary key, replace deletes the old row and then inserts a new row, and the INSERT statement is an error.
If the data table does not use the primary key or the unique index, replace is exactly the same as the INSERT statement.

INSERT ... On DUPLICATE KEY UPDATE Syntax

If an on DUPLICATE KEY UPDATE is specified at the end of the INSERT statement, and the row is inserted to cause duplicate values in a unique index or primary KEY, UPDATE is performed on the row where the duplicate value occurs, and if the unique value column is not duplicated. The new row is inserted.

For example, if column A has a unique index and contains a value of 1,

CREATE TABLE t (ID int auto_increment,a int, b int, c int, PRIMARY key (ID), UNIQUE Key (a)); INSERT in to T (a,b,c) VALUES (1,9,one);

The following two statements have the same effect:

INSERT into T (A, C) VALUES (13) on DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

If the row is inserted as a new record, the value of the affected row (affected-rows) displays 1, and if the original record is updated, the value of the affected row is displayed as 2.

You can also insert multiple rows of records:

INSERT into TABLE (a,c) VALUES (1,3), (1,7) on DUPLICATE KEY UPDATE c=c+1;

After execution, the value of C is changed to +1 (c on the original value).

use the values function :

INSERT into  t (a,c) VALUES (1,3), (1,7) on DUPLICATE KEY UPDATE c=values (c) +  1;

After execution, the value of C becomes 8 (c on the inserted value +1).

LOAD DATA LOCAL INFILE file_name into TABLE table_name

By default, the LOAD data statement assumes that the values of each field are separated by ' \ t ', the data rows are separated by ' \ n ', and the order of the data values is consistent with the order of the fields in the data table.

The local keyword allows the client to read the data file and send it to the server to load, if the local keyword is omitted, it means that the data file is stored on the server host, and you must have the appropriate file server access rights to load the data in the data table.

Another way to load a data file is to use the Mysqlimport tool, for example:

passwd hostname -p Port db_name file_name

This command generates a load data statement that loads the values of the file_name file into the specified data table in Db_name.

Note: The Mysqlimport program determines the corresponding data table based on the name of the data file, for example:

Mysqlimport-l sampdb Member.txt

is to load the Member.txt data file into the Sampdb.member data table.

Options for Mysqlimport:

-r:--replace, for duplicate record on unique key, replace old record with new line;

-i:--ignore, for duplicate records on a unique key, ignore the non-processing;

-l:--local, reads the input file from the client's local main clause;

-c,--columns, specifies the list of fields in the input data file (ordered);
-d,--delete, clear the data table before importing the new data file;

Reference Documentation:

Http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

MySQL Insert/update data