MySQL增加，刪除，更新，匯出資料

標籤：mysql   匯入   增加   修改   刪除   

匯出資料

將檢索到的資料，儲存到伺服器的檔案內。

通過：select * into outfile ‘檔案地址‘ from xxx;

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

注意：
可以自動建立檔案，但是不能重寫已有檔案。
產生的檔案格式：預設的採用行來區分記錄，定位字元區分欄位。

為了滿足某種特別的需求，會採用不同的分割方式。支援在匯出資料時，設定記錄與欄位的分隔字元。
通過如下的選項：
fields：設定欄位選項
lines：設定行選項（記錄選項）

預設值：
欄位：fields terminated by ‘\t‘ enclosed by ‘‘ escaped by ‘\\‘
記錄：lines terminated by ‘\n‘ starting by ‘‘

也可以根據實際情況自己設定。

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

注意：
常規的所有的記錄，應該通過行來顯示。
但是也有例外，例如儲存位元據：Blob binary使用into dumpfile

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

增加資料

insert into tbl_name (欄位列表) values (值列表)

可以不將所有的欄位都插入資料。
如果說需要完成部分欄位的插入，需要必須存在欄位列表。

沒有插入部分欄位，可以使用下面的set語句。

insert into teacher_grade (name) values (‘黃繼光‘);insert into teacher_grade set name=‘黃繼光‘;insert into teacher_grade set t_name=‘張三丰‘,c_name=‘太極拳‘;insert into teacher_grade (t_name,c_name) values (‘黃繼光‘,‘射擊‘),(‘黃飛鴻‘,‘飛毛腿‘);

插入資料時，如果主鍵衝突會如何？
預設有主鍵約束，不會插入成功；

但是可以在insert文法內，可以進行控制。
在主鍵衝突時，改成執行更新操作。

insert into teacher_grade (id,t_name,c_name) values(13,‘張無忌‘,‘太極拳‘) on duplicate key update t_name=‘張無忌‘,c_name=‘太極拳‘;

注意：這裡update後面不跟set。

流程：
先判斷是否插入成功？
如果失敗（主鍵衝突|唯一索引衝突），則進行更新操作。

插入（失敗）更新更新完畢

插入資料來源：
除了使用自訂的資料外，還可以使用select語句查詢到資料，作為插入的資料來源。

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

資料可以來源於其他資料表，要求欄位數量和類型一致即可。

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

通過強制使用default關鍵字或者default()函數使用預設值。

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

replace
主鍵或唯一索引衝突，則替換，否則插入。

insert into teacher values(1,‘老子‘,‘儒家‘,30);
如果插入衝突，先刪除舊記錄，再插入新紀錄。
replace into teacher values(1,‘老子‘,‘儒家‘,30);
主鍵不衝突，直接插入。
replace into teacher values(15,‘老子‘,‘儒家‘,30);

匯入select * into outfile ‘file‘命令匯出的內容

load data infile ‘file‘ into table tbl_name;

注意：
匯入時，涉及到資料增加，需要考慮是否衝突的情況。
通常可以在匯出時，將主鍵匯出成null。在匯入時，利用自動成長的特性，可以形成新的主鍵。

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; 

刪除資料

允許使用條件：刪除合格資料。
允許使用limit：限制刪除的記錄數，limit n;

常見情境：
limit配合order by來使用。（先將結果排序，再刪除固定數量的記錄數。）
delete from teacher order by days limit 10;

如果只有order by是沒有意義的。

允許串連刪除
允許使用類似的join文法，同時刪除多個表內的記錄。

create table one(one_id int,one_data char,public_field int);create table two(two_id int,two_data char,public_field int);insert into one values(1,‘a‘,10);insert into one values(2,‘b‘,20);insert into one values(3,‘c‘,30);insert into two values(2,‘b‘,20);insert into two values(3,‘c‘,30);insert into two values(4,‘d‘,40);select * from one;select * from two;

select * from one join two using(public_field);

先提供表名，再提供串連條件。

delete from one,two using one join two on one.public_field=two.public_field  where one_id=2;

如果不使用串連，則需要分別刪除。
delete one
delete two

刪除表所有資料
delete from test;

清空表
truncate teacher;
重建自定增長的主鍵，不會返回刪除的記錄數。

delete與truncate的區別
delete：逐行刪除。
truncate：刪除表，建立表。

更新資料

replace
insert onduplicate key update
條件更新，排序更新，限制條數
update ... where ... order by ... limit ...

多表更新

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

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。

MySQL增加，刪除，更新，匯出資料