Data Manipulation Language(DML)

SQL supports the following statements for manipulating data:

• INSERT

• SELECT... INTO

• LOAD DATA ...

• UPDATE （Planing）

• DELETE （Planing）

• MERGE（Planing）

INSERT

INSERT INFO tbl_name [(column_list)] VALUES (value_list) [,(value_list)]...

column_list:
    col_name [, col_name] ...

value_list:
    value [, value] ...

Description:

INSERT statement inserts new rows into an existing table.

Examples:

-- insert a row into table with all columns
INSERT INTO t1 values(1, 2, 3.0, 4.0, "hello");

-- insert a row into table with given columns's values
INSERT INTO t1(COL1, COL2, COL5) values(1, 2, "hello")

-- insert multiple rows into table with all columns
INSERT INTO t1 values(1, 2, 3.0, 4.0, "hello"), (10, 20, 30.0, 40.0, "world"), ;

-- insert multiple rows into table with given columns's values
INSERT INTO t1(COL1, COL2, COL5) values(1, 2, "hello"), (10, 20, "world")

SELECT ... INTO

SELECT ... [into_option]
into_option: {
    INTO OUTFILE 'file_name' options
}

Options	Type	Default
delimiter	String	,
header	Boolean	true
null_value	String	null
format	String	csv
mode	String	error_if_exists

Description:

The SELECT ... INTO form of SELECT enables a query result to be written to a file:

• SELECT ... INTO OUTFILE writes the selected rows to a file. Column and line terminators can be specified inoptions to produce a specific output format.

Examples:

Write data from table t1 to a file data.csv with fields terminated by ',':

SELECT col1, col2, col3 FROM t1 INTO OUTFILE 'data.csv' OPTIONS ( delimit = ',' );

Write data from table t2 to a file data2.csv with fields terminated by '|' and null value represented by 'NA':

SELECT col1, col2, col3 FROM t2 INTO OUTFILE 'data2.csv' OPTIONS ( delimit = '|', null_value='NA');

LOAD DATA

LOAD DATA INFILE 'file_name' options

Options	Type	Default
delimiter	String	,
header	Boolean	true
null_value	String	null
format	String	csv

Description:

The LOAD DATA statement reads rows from a text file into a table at a very high speed.

LOAD DATA is the complement of SELECT...INTO OUTFILE. (See [SELECT ... INTO](#SELECT ... INTO).)

• To write data from a table to a file, use SELECT...INTO OUTFILE.

• To read the file back into a table, use LOAD DATA

Examples:

Read rows from data.csv back into table demo_table1 with fields terminated by ',':

LOAD DATA INFILE 'data.csv' INTO TABLE demo_table1 ( delimit = ',' );

Read rows from data2.csv back into table demo_table2 with fields terminated by '|' and null_value represented by "NA":

LOAD DATA INFILE 'data.csv' INTO TABLE demo_table2 ( delimit = ',', nullptr_value='NA');