可以使用select……into outfile导出txt、excel等格式的文件
语法格式: select [ 列名 ] from table [ where 语句 ] into outfile ‘目标文件’ [ option ];
语句可以分为两个部分,前面是一个普通的select查询语句,通过这个语句查询处所需要的数据;
后部分是导出数据导到哪里,以及导出数据的格式;
------------------------------------------------------------------------------------------------------------------------------------
‘目标文件’:指将查出的记录导出到哪个文件;
option:有常用的5个选项,分别如下:
fields terminated by '字符串':设置字符串为字段的分隔符,默认值是‘ \t ’;
fields enclosed by '字符':设置字符来括上字段的值。默认情况下不使用任何符号;
fields optionally enclosed by '字符':设置字符来括上char,varchar,text等字符型字段,默认情况下不使用任何符号;
fields escaped by '字符':设置转义字符,默认值为“\”;
lines starting by '字符串':设置每行开头的字符,默认情况下无任何字符;
lines terminated by '字符串':设置每行的结束符,默认值是‘\n’;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select sx,mz,bz from cr01 into OUTFILE 'C:/Users/del/Desktop/a.txt'fields terminatedby '、'optionally enclosed by '\"' lines starting by '\>' terminated by '\r\n';
字段的分隔符 字符上加括号 开头 结尾换行
PS:Windows下: \r\n 是换行
------------------------------------------------------------------------------------------------------------------------------
前期数据准备:
create table cr01 ( sx int(50),
mzvarchar(50),
bzvarchar(50)
);insert into cr01 ( sx,mz,bz ) values (1,'sww','sww01');insert into cr01 values (2,'aww','aww02');insert into cr01 values (3,'qww','qww03'),
(4,'eww','eww04'),
(5,'rww','rww05');insert into cr01 ( sx,mz,bz ) values (6,'yww','yww06'),
(7,'uww','uww07');select * from cr01;
单表导出为xls文件,导出到桌面:
select sx,mz,bz from cr01 into OUTFILE 'C:/Users/del/Desktop/a.xls'
示例:select * from cr01 where sx in (1,3,5,7) into OUTFILE 'C:/Users/del/Desktop/a.xls'
===========================================================================================
create table employee ( num int(50),
d_idint(50),
namevarchar(50),
ageint(50),
sexvarchar(50),
homeaddvarchar(50)
);insert into employee values(1,1001,'zhangsan',26,'nan','beijing');insert into employee values(2,1001,'lisi',24,'nv','hunan');insert into employee values(3,1002,'wangwu',25,'nan','jiangsu');insert into employee values(4,1004,'aric',15,'nan','yingguo');select * fromemployee;create table department ( d_id int(50),
d_namevarchar(50),
functionevarchar(50),
addressvarchar(50)
);insert into department values(1001,'keyanbu','yanfachanpin','3lou5hao');insert into department values(1002,'shengchanbu','shengchanchanp','5louyiceng');insert into department values(1003,'xiaoshoubu','cehuaxiaoshou','1louxiaoshoudating');select * fromemployee;select * from department;
================================================================================================
多表导出为xls文件:(左连接)
select num,name,employee.d_id,age,sex,d_name,functione from employee left join department on employee.d_id =department.d_idinto OUTFILE 'C:/Users/del/Desktop/a.xls';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
右连接:
select num,name,employee.d_id,age,sex,d_name,functione from employee right join department on employee.d_id = department.d_id;
select num,name,employee.d_id,age,sex,d_name,functione from employee right join department on employee.d_id =department.d_idinto OUTFILE 'C:/Users/del/Desktop/a.xls';
========================================================================
导出文本文件:
select sx,mz,bz from cr01 into OUTFILE 'C:/Users/del/Desktop/a.txt'fields terminatedby '\、' optionally enclosed by '\“' lines starting by '\>' terminated by '\r\n';
>1、“sww“、“sww01“>2、“aww“、“aww02“>3、“qww“、“qww03“>4、“eww“、“eww04“>5、“rww“、“rww05“>6、“yww“、“yww06“>7、“uww“、“uww07“
=========================================================================================
修改参数:
select sx,mz,bz from cr01 into OUTFILE 'C:/Users/del/Desktop/a.txt'fields terminatedby '\t'optionally enclosed by ' ' lines starting by ' ' terminated by '\r\n';
1sww sww012aww aww023qww qww034eww eww045rww rww056yww yww067 uww uww07
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select num,name,employee.d_id,age,sex,d_name,functione from employee left join department on employee.d_id =department.d_idinto OUTFILE 'C:/Users/del/Desktop/a.txt'fields terminatedby '\t'optionally enclosed by ' ' lines starting by ' ' terminated by '\r\n';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select num,name,employee.d_id,age,sex,d_name,functione from employee left join department on employee.d_id =department.d_idinto OUTFILE 'C:/Users/del/Desktop/b.txt'fields terminatedby '\t'optionally enclosed by ' ' lines starting by ' ' terminated by '\r\n';
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++