CDA學(xué)習(xí)-Mysql workbench的查詢結(jié)果導(dǎo)出到Excel

1, 使用workbench的界面功能

2, 使用命令操作

-- 創(chuàng)建數(shù)據(jù)庫school
create database school;
-- 選擇進(jìn)入school數(shù)據(jù)庫
use school;
-- ------------建表導(dǎo)數(shù)-------------
-- 創(chuàng)建stu
create table stu(
s_id varchar(10) primary key,
s_name varchar(10) not null,
s_birth date,
s_sex varchar(10));
-- 導(dǎo)入數(shù)據(jù)
insert into stu values
('01' , '趙雷' , '1990-01-01' , '男'),
('02' , '錢電' , '1990-12-21' , '男'),
('03' , '孫風(fēng)' , '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; -- 檢查數(shù)據(jù)
#01 保存到sql的新表中: create table新表名as select查詢語句;
create table stu001 as select * from stu;
create table stu700 as select * from stu;
#查詢默認(rèn)的文件安全路徑
show variables like 'secure_file_priv'; #一般為C:\ProgramData\MySQL\MySQL Server 8.0\Uploads

#02,把mysql中已有表格導(dǎo)出為CSV外部文件,然后可以在Excel等表格工具中打開
#保存到外部文件: select 查詢語句 into outfile '文件路徑.csv' ,需要預(yù)先在默認(rèn)安全路徑中建好空的"文件路徑.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導(dǎo)出查詢結(jié)果到外部帶列標(biāo)題的CSV文件(然后可以導(dǎo)入到Excel中打開,注意選擇UTF8來識別.)
select  *  from (select '編號','姓名','生日','年齡' union select s_id ,s_name,s_birth ,s_sex from stu700) b  #用union加標(biāo)題
into outfile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/stu666.csv'
fields terminated by ','   #字段用逗號分隔
enclosed by '"'            #用引號括上字符型字段
lines terminated by '\r\n'  ;#行的結(jié)束符為回車符,回車換行

 #3,mysql導(dǎo)出查詢結(jié)果到外部帶列標(biāo)題的txt文件,然后可以在Excel等表格工具中打開
select  *  from (select '編號','姓名','生日','年齡' union select s_id ,s_name,s_birth ,s_sex from stu700) b #用union加標(biāo)題
into outfile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/stu456.txt'
fields terminated by ','   #字段用逗號分隔
enclosed by '"'            #用引號括上字符型字段
lines terminated by '\r\n'  ;#行的結(jié)束符為回車符,回車換行