MySQL Add, delete, update, export data

Exporting Data

Saves the retrieved data to a file in the server.

Pass:select * into outfile ‘文件地址‘ from xxx;

select * from teacher_grade;select * into outfile  ‘d:/testmysql/result‘ from teacher_grade;

Note:
You can create files automatically, but you cannot overwrite existing files.
Generated file format: The default is to use rows to differentiate between records, tab-delimited fields.

In order to meet a particular requirement, different segmentation methods are used. Supports setting the delimiter for records and fields when exporting data.
With the following options:
Fields: Setting field options
Lines: Set line options (logging options)

Default value:
Fields: fields terminated by ‘\t‘ enclosed by ‘‘ escaped by ‘\\‘
Recording:lines terminated by ‘\n‘ starting by ‘‘

can also be set according to the actual situation.

select * into outfile  ‘d:/testmysql/result‘ fields terminated by ‘\t‘ enclosed by ‘*‘lines terminated by ‘\n‘ starting by ‘start:‘from teacher_grade;

Note:
All records that are regular should be displayed by line.
However, there are exceptions, such as saving binary data: Blob binary usinginto dumpfile

select * into dumpfile ‘d:/testmysql/resultbin‘ from teacher_grade limit 1;select * into outfile  ‘d:/testmysql/result‘ from teacher_grade limit 1;

Add Data

insert into tbl_name (字段列表) values (值列表)

You can not insert all of the fields into your data.
If you need to complete the insertion of some fields, you need to have a list of fields.

You can use the following set statement without inserting a partial field.

 Insert  into Teacher_grade (name) values (' Huang Jiguang '); Insert  into Teacher_grade set name=' Huang Jiguang '; Insert  into Teacher_grade set t_name= 'Zhangsanfeng ', c_name=' Taijiquan ' ; Insert  into Teacher_grade (t_name,c_name) values (' Huang Jiguang ',' shot '), ( ' Yellow Feihong ',' scud');

What happens if a primary key conflict occurs when inserting data?
The default has a PRIMARY KEY constraint and will not be inserted successfully;

However, you can control it within the insert syntax.
In the case of a primary key conflict, change to perform the update operation.

insert into teacher_grade (id,t_name,c_name) values(13,‘张无忌‘,‘太极拳‘) on duplicate key update t_name=‘张无忌‘,c_name=‘太极拳‘;

Note: Here the update is not followed by set.

Process:
First to determine if the insert is successful?
If the failure (primary key violation | Unique index conflict), the update operation is performed.

插入（失败）更新更新完毕

To insert a data source:
In addition to using custom data, you can use the SELECT statement to query the data as an inserted data source.

insert into teacher_grade (t_name,c_name) select t_name,c_name from teacher_grade;

Data can be derived from other data tables, requiring a consistent number of fields and types.

insert into teacher_grade (t_name,c_name) select t_name,class_name from teacher;

The default value is used by forcing the defaults keyword or by using the

alter table teacher modify days tinyint(3) unsigned default 10;insert into teacher values (10,‘xxx‘,‘yyy‘,default),(11,‘xxx‘,‘yyy‘,default(days));

Replace
Primary key or unique index conflict is replaced, otherwise inserted.

insert into teacher values(1,‘老子‘,‘儒家‘,30);
If you insert a conflict, delete the old record and insert a new record.
replace into teacher values(1,‘老子‘,‘儒家‘,30);
The primary key does not conflict and is inserted directly.
replace into teacher values(15,‘老子‘,‘儒家‘,30);

Import select * into outfile ‘file‘命令导出的内容

load data infile ‘file‘ into table tbl_name;

Note:
When importing, it involves data increase, which needs to be considered in case of conflict .
You can typically export a primary key to NULL when you export it. When importing, a new primary key can be formed using the auto-grow feature.

select  null , t_name,class_name,days from  teacher;  

desc teacher; alter  table  teacher Modify t_id int  auto_increment;  alter  table  Teacher drop  primary  key ;  alter  table  Teacher Modify t_id int  primary  key  auto_increment;   

load data infile ‘d:/testmysql/result‘ into table teacher;

Delete Data

Allow conditions: Delete data that meets the criteria.
Allow limit: Limits the number of records to delete, and limit n;

Common scenarios:
Limit is used in conjunction with order by. (Sort the results first, and then delete the fixed number of records.) ）
delete from teacher order by days limit 10;

If only order by there was no point in it.

Allow connection Removal
Allows the use of similar join syntax while deleting records within multiple tables.

 Create table One (one_id int, one_data char, public_field int); Create table (two_id int, two_data char, public_field int); Insert  into one values(1,' A ',ten); Insert  into one values(2,' B ',+); Insert  into one values(3,' C ',+); Insert  into the values(2,' B ',+); Insert  into the values(3,' C ',+); Insert  into the values(4,' d ',+); Select *   from one; Select *  from;

select * from one join two using(public_field);

Provide the table name first, and then provide the join condition.

deletefromone,twousingonetwoon one.public_field=two.public_field  where one_id=2;

If you do not use a connection, you need to delete it separately.
Delete One
Delete a

Delete all data from a table
delete from test;

Clear Table
truncate teacher;
Rebuilds the self-growing primary key and does not return the number of deleted records.

the difference between delete and truncate
Delete: Remove line by row.
Truncate: Deletes a table and creates a new table.

Update Data

replace
insert onduplicate key update
Conditional update, sort update, limit number of bars
update ... where ... order by ... limit ...

Multiple table Updates

update one join two on one.public_field = two.public_field set one_data=‘x‘,two_data= ‘y‘ where one_id=3;

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL Add, delete, update, export data