MySQL Training Preparation (1): MySQL data insertion

These should be required by the company, need to give the development department to train MySQL, so has been thinking, from what to start, to make you interested in prawns! Start with the syntax, which will give you the fastest knowledge and interest in SQL Server!

In MySQL, in addition to the INSERT INTO INSERT statement that we are familiar with, there is a special statement, REPLACE into statement.

1. INSERT into statement

Syntax one: INSERT [low_priority | DELAYED] [IGNORE]  [into] tbl_name [(col_name,...)]  VALUES (expression,...), (...),... Syntax two: INSERT [low_priority | DELAYED] [IGNORE]  [into] tbl_name [(col_name,...)]  SELECT ... Syntax three: INSERT [low_priority | DELAYED] [IGNORE]  [into] tbl_name  SET col_name=expression, Col_name=expression, ...

INSERT into ... A statement in values form inserts a row based on an explicitly specified value. An expression can reference any column that was previously set in a value table. INSERT into Tbl_name (col1,col2) VALUES (15,col1*2); But this cannot be: INSERT into Tbl_name (col1,col2) VALUES (col2*2,15);.

INSERT INTO Select Inserts the row selected from the other table. It must meet the following conditions: 1) The query cannot contain an ORDER BY clause. 2) The destination table of the INSERT statement cannot appear in the FROM clause in the Select query section;

If you are an insert ... Values or inserts ... Select does not specify a list, the values of all columns must be in the values () table or provided by SELECT. If you don't know the order of the columns in the table, use describe tbl_name to find out. If you specify a list and do not name all the columns in the table, the unnamed columns are set to their default values.

If you specify the keyword ignore in an insert with many value rows, any row in the table that replicates an existing primary or unique key is ignored and is not inserted. If you do not specify ignore, insert the row if there is any copy of the existing key value is discarded.

It is important to note that:

1) Inserts a string into a char, VARCHAR, text, or BLOB column that exceeds the maximum length of the column, and the value is truncated to the maximum length of the column.

2) Insert a value that is not valid for the column type into a date or time column, and the column is set to the appropriate "0" value for that column type.

3) Insert NULL to a column that is declared not NULL, and the column is set to its default value.

4) Set the value beyond the column range to a number column, and the value is clipped to the appropriate endpoint value in the range.

5) Set the number column to a value such as ' 10.34 a ', the trailing garbage is stripped and the number portion is still inserted. If the value is not a number at all, the column is set to 0.

2. REPLACE into statement

Syntax One:REPLACE [low_priority | DELAYED] [IGNORE]  [ into]Tbl_name[(col_name,...)]  VALUES(expression,...), (...),... Syntax Two:REPLACE [low_priority | DELAYED] [IGNORE]  [ into]Tbl_name[(col_name,...)]  SELECT ... Syntax Three:REPLACE [low_priority | DELAYED] [IGNORE]  [ into]Tbl_nameSET col_name=Expressioncol_name=Expression, ...

Replace into is similar to insert into, except that replace into is the first attempt to insert data into a table, and if you find that this row of data is already in the table (judging by a primary key or a unique index), then the row data is first deleted and then the new data is inserted. Otherwise, insert the new data directly. Resembles the following statement in SQL Server:

if  not exists(Select 1  fromTwhereId= 1)? Insert  intoT (ID, Update_time)Values(1,getdate())  Else  UpdateTSetUpdate_time= getdate()whereId= 1 

It is important to note that:

1) The table in which the data is inserted must have a primary key or a unique index! Otherwise, REPLACE into will insert the data directly, which will result in duplicate data appearing in the table.

2) If a table has both a primary key and a unique index, or if there are multiple unique indexes, you must replace replace into with INSERT into and update.

We create a table t_s_alarm, create a primary key i_id, create a unique index I_code, with the following statement:

Create TableT_s_alarm (i_idINT(Ten) unsigned not NULLAuto_increment,i_codeINT(Ten) unsigned not NULL, V_alarmvarchar( -)default NULL, V_memoVARCHAR( -)default NULL,Primary Key(i_id),Unique Key(I_code))

Insert three record:

INSERT  intoT_s_alarm (I_code, V_alarm, V_memo)VALUES(1,'Alarm1','test1'), (2,'Alarm2','test1'), (3,'ALARM3','test3');

Execute Query Result:

Select *  from T_S_alarm

Then execute the Replace statement, and the result is 2 records affected:

REPLACE  into  VALUES (1'alarm4'test4')
Execution Result:
Query OK, 2 rows affected (MS)

Execute Query Result:

From T_s_alarm

Judging from the results of the query, the first record is deleted and a new record is inserted.

MySQL Training Preparation (1): MySQL data insertion