2023-11-28
閱讀量:
5150
CDA學(xué)習(xí)筆記-Mysql的查詢結(jié)果導(dǎo)出到Excel
CDA學(xué)習(xí)-Mysql workbench的查詢結(jié)果導(dǎo)出到Excel
1, 使用workbench的界面功能
2, 使用命令操作
-- 創(chuàng)建數(shù)據(jù)庫(kù)school create database school; -- 選擇進(jìn)入school數(shù)據(jù)庫(kù) 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查詢語(yǔ)句; 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 查詢語(yǔ)句 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來(lái)識(shí)別.) select * from (select '編號(hào)','姓名','生日','年齡' 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 ',' #字段用逗號(hào)分隔 enclosed by '"' #用引號(hào)括上字符型字段 lines terminated by '\r\n' ;#行的結(jié)束符為回車符,回車換行 #3,mysql導(dǎo)出查詢結(jié)果到外部帶列標(biāo)題的txt文件,然后可以在Excel等表格工具中打開 select * from (select '編號(hào)','姓名','生日','年齡' 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 ',' #字段用逗號(hào)分隔 enclosed by '"' #用引號(hào)括上字符型字段 lines terminated by '\r\n' ;#行的結(jié)束符為回車符,回車換行






評(píng)論(0)


暫無(wú)數(shù)據(jù)
CDA考試動(dòng)態(tài)
CDA報(bào)考指南
推薦帖子
0條評(píng)論
1條評(píng)論
0條評(píng)論