mysql outfile xls_mysql——表的导出——用select……into outfile导出 xls文件和文本文件...

可以使用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';

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++