扫码加入数据分析学习群

CDA学习-Mysql workbench的查询结果导出到Excel

1, 使用workbench的界面功能

2, 使用命令操作

-- 创建数据库school
create database school;
-- 选择进入school数据库
use school;
-- ------------建表导数-------------
-- 创建stu
create table stu(
s_id varchar(10) primary key,
s_name varchar(10) not null,
s_birth date,
s_sex varchar(10));
-- 导入数据
insert into stu values
('01' , '赵雷' , '1990-01-01' , '男'),
('02' , '钱电' , '1990-12-21' , '男'),
('03' , '孙风' , '1990-05-20' , '男'),
('04' , '李云' , '1990-08-06' , '男'),
('05' , '周梅' , '1991-12-01' , '女'),
('06' , '吴兰' , '1992-03-01' , '女'),
('07' , '郑竹' , '1992-04-21' , '女'),
('08' , '王菊' , '1990-01-20' , '女');

select * from stu; -- 检查数据
#01 保存到sql的新表中: create table新表名as select查询语句;
create table stu001 as select * from stu;
create table stu700 as select * from stu;
#查询默认的文件安全路径
show variables like 'secure_file_priv'; #一般为C:\ProgramData\MySQL\MySQL Server 8.0\Uploads

#02,把mysql中已有表格导出为CSV外部文件,然后可以在Excel等表格工具中打开
#保存到外部文件: select 查询语句 into outfile '文件路径.csv' ,需要预先在默认安全路径中建好空的"文件路径.csv"做文件接收
#不带分隔符
select * from stu700 into outfile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/stu700.csv';
select * from stu700 into outfile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/stu799.xlsx' fields terminated by ',' ;
select * from stu700 into outfile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/stu799.txt'  fields terminated by ',' ;

#带分隔符
select  * from stu700 into outfile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/stu811.csv' fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

#2,mysql导出查询结果到外部带列标题的CSV文件(然后可以导入到Excel中打开,注意选择UTF8来识别.)
select  *  from (select '编号','姓名','生日','年龄' union select s_id ,s_name,s_birth ,s_sex from stu700) b  #用union加标题
into outfile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/stu666.csv'
fields terminated by ','   #字段用逗号分隔
enclosed by '"'            #用引号括上字符型字段
lines terminated by '\r\n'  ;#行的结束符为回车符,回车换行

 #3,mysql导出查询结果到外部带列标题的txt文件,然后可以在Excel等表格工具中打开
select  *  from (select '编号','姓名','生日','年龄' union select s_id ,s_name,s_birth ,s_sex from stu700) b #用union加标题
into outfile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/stu456.txt'
fields terminated by ','   #字段用逗号分隔
enclosed by '"'            #用引号括上字符型字段
lines terminated by '\r\n'  ;#行的结束符为回车符,回车换行