1000 Line MySQL study notes (v)

/ * Import and Export */-------------

 Select *  into outfile file address [control format]  from table name; --Export table data 
 Load data [local] infile file address [Replace|ignore]  into table table name [Control format]
     ; --Import data 
The default delimiter for the generated data is the tab local is not specified, then the data file must be on the server
 replace and ignore keyword controls duplicate processing of existing unique key records
 
--Control format
 
Fields control field format default: terminated by ' \ t 'Enclosed by "'Escaped by ' \ \ 'Terminated by ' String '  --TerminationEnclosed by ' char '      --ParcelEscaped by ' char '      --Escape  --Example:   SELECTA,b,a+b intoOUTFILE'/tmp/result.text 'Fields TERMINATED by ', 'Optionally enclosed by ' "'LINES TERMINATED by ' \ n '    fromtest_table;
lines Control row format default: Lines terminated  by ' \ n ' terminated by  ' string '  --Terminate    

/ * Insert */--------------

the data obtained by the SELECT statement can be inserted with insert.  You can omit the designation of a column  , requiring values () in parentheses, to give the value of all fields that appear in the order of the columns.    or use the SET syntax. 
 insert into tbl_name set field=value,...  ; You can use multiple values at once, in the form of (), (), (); 
 insert into tbl_name values (), (), (); You can use an expression when the column value is specified. 
 insert into tbl_name values (Field_value, 10+10, now ());
You can use a special value of default, which indicates that the column uses the defaults. 
 insert into tbl_name  values (field_value, default); You can use the result of a query as the value you want to insert. 
 insert into tbl_name select ...;  You can specify that information for other non-primary key columns be updated when a primary key (or unique index) conflict occurs in the inserted value. 
 insert into tbl_name values/set/Select on duplicate key update field = value, ... ;

/ * Delete */-------------

 DELETE from Tbl_name [WHERE where_definition] [ORDER by ...] [LIMIT Row_count] 

deletes the maximum number of records that are specified for deletion by condition. Limit can be removed by sorting criteria. order by + limit supports multiple table deletions, using similar connection syntax. 
 
Delete from   needs to delete data in multiple table 1, table 2 using table join operation conditions. 

/ * truncate */-------------

 TRUNCATE [table] tbl_name Erase data Delete rebuild table differences:
 
1,truncate is delete table re-create , delete is deleted
2,truncate resets the value of the auto_increment. and delete does not
3,truncate does not know how many deleted, and delete knows. 
4, when used with partitioned tables,truncate preserves the partition

(not to be continued)
(Shocker Source: http://www.cnblogs.com/shockerli/p/1000-plus-line-mysql-notes.html)

1000 Line MySQL study notes (v)