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

末蓝、 2023-10-02 08:46 26阅读 0赞

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

\+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

![3d2debf8548e3e618359c83f00dc1493.png][]

select sx,mz,bz from cr01 into OUTFILE 'C:/Users/del/Desktop/a.txt'
    fields terminated by '、'optionally enclosed by '\"' lines starting by '\>' terminated by '\r\n';
        字段的分隔符              字符上加括号                   开头                    结尾换行
    
    
    
    PS：Windows下：  \r\n   是换行

![2769e34526b3efb7227e284b2e8c38d2.png][]

\------------------------------------------------------------------------------------------------------------------------------

前期数据准备：

create table cr01 ( sx int(50),
                        mz varchar(50),
                        bz varchar(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;

![ca3df996407649c461227cedb8630f66.png][]

单表导出为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'

![428b3061bd3e1dcd5d65279725a38e03.png][]

![65845e05147ee411dbf2a387cff9ca1b.png][]

===========================================================================================

create table employee ( num int(50),
                            d_id int(50),
                            name varchar(50),
                            age int(50),
                            sex varchar(50),
                            homeadd varchar(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 * from employee;
    
    create table department ( d_id int(50),
                              d_name varchar(50),
                              functione varchar(50),
                              address varchar(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 * from employee;
    
    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_id
    into OUTFILE 'C:/Users/del/Desktop/a.xls';

![f5745b2228c302295d92156563d3ace3.png][]

![c80dd32f339f44a463505f81c156f1be.png][]

\++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

右连接：

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_id
    into OUTFILE 'C:/Users/del/Desktop/a.xls';

![560dd7efbe3f37ca43ab28a712ea3f66.png][]

![d519d315ac44089267796cfa1fe71e99.png][]

![2b8705acd54031e97267ff49e7996ef6.png][]

========================================================================

导出文本文件：

select sx,mz,bz from cr01 into OUTFILE 'C:/Users/del/Desktop/a.txt'
    fields terminated by '\、' optionally enclosed by '\“' lines starting by '\>' terminated by '\r\n';

![d8a6c2199395bfa2394c4350a68a2cff.png][]

![242e64f456bcc9ad3067b96bfe7939b0.png][]

>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 terminated by '\t'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 num,name,employee.d_id,age,sex,d_name,functione from employee left join department on employee.d_id = department.d_id
    into OUTFILE 'C:/Users/del/Desktop/a.txt'
    fields terminated by '\t'optionally enclosed by ' ' lines starting by ' ' terminated by '\r\n';

![16b39b44f0d5c51368281dba8e618990.png][]

\++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select num,name,employee.d_id,age,sex,d_name,functione from employee left join department on employee.d_id = department.d_id
    into OUTFILE 'C:/Users/del/Desktop/b.txt'
    fields terminated by '\t'optionally enclosed by ' ' lines starting by ' ' terminated by '\r\n';

![8a1c46fae46de5745aa55830c78a0e93.png][]

\+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

[3d2debf8548e3e618359c83f00dc1493.png]: https://img-blog.csdnimg.cn/img_convert/3d2debf8548e3e618359c83f00dc1493.png
[2769e34526b3efb7227e284b2e8c38d2.png]: https://img-blog.csdnimg.cn/img_convert/2769e34526b3efb7227e284b2e8c38d2.png
[ca3df996407649c461227cedb8630f66.png]: https://img-blog.csdnimg.cn/img_convert/ca3df996407649c461227cedb8630f66.png
[428b3061bd3e1dcd5d65279725a38e03.png]: https://img-blog.csdnimg.cn/img_convert/428b3061bd3e1dcd5d65279725a38e03.png
[65845e05147ee411dbf2a387cff9ca1b.png]: https://img-blog.csdnimg.cn/img_convert/65845e05147ee411dbf2a387cff9ca1b.png
[f5745b2228c302295d92156563d3ace3.png]: https://img-blog.csdnimg.cn/img_convert/f5745b2228c302295d92156563d3ace3.png
[c80dd32f339f44a463505f81c156f1be.png]: https://img-blog.csdnimg.cn/img_convert/c80dd32f339f44a463505f81c156f1be.png
[560dd7efbe3f37ca43ab28a712ea3f66.png]: https://img-blog.csdnimg.cn/img_convert/560dd7efbe3f37ca43ab28a712ea3f66.png
[d519d315ac44089267796cfa1fe71e99.png]: https://img-blog.csdnimg.cn/img_convert/d519d315ac44089267796cfa1fe71e99.png
[2b8705acd54031e97267ff49e7996ef6.png]: https://img-blog.csdnimg.cn/img_convert/2b8705acd54031e97267ff49e7996ef6.png
[d8a6c2199395bfa2394c4350a68a2cff.png]: https://img-blog.csdnimg.cn/img_convert/d8a6c2199395bfa2394c4350a68a2cff.png
[242e64f456bcc9ad3067b96bfe7939b0.png]: https://img-blog.csdnimg.cn/img_convert/242e64f456bcc9ad3067b96bfe7939b0.png
[16b39b44f0d5c51368281dba8e618990.png]: https://img-blog.csdnimg.cn/img_convert/16b39b44f0d5c51368281dba8e618990.png
[8a1c46fae46de5745aa55830c78a0e93.png]: https://img-blog.csdnimg.cn/img_convert/8a1c46fae46de5745aa55830c78a0e93.png