mysql 数据导出

 

use movie;
 
#CMD命令 查看MySql的导入与导出的目录【其他目录无权限】
# 使用mysql -u root -p  连接mysql
# show variables like '%secure%'
#+--------------------------+------------------------------------------------+
#| Variable_name            | Value                                          |
#+--------------------------+------------------------------------------------+
#| require_secure_transport | OFF                                            |
#| secure_auth              | ON                                             |
#| secure_file_priv         | C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\ |genregenre
#+--------------------------+------------------------------------------------+
#3 rows in set, 1 warning (0.00 sec)
 
#MySql导出csv数据，带表头
 
 
#导出电影的类型
SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/genre.csv'
FIELDS TERMINATED BY ',' 
FROM (select 'gid','gname' union select*from genre) genre_;
 
 
#导出电影的信息  == 如果太多可以只导出前500个，加限制
SELECT *  INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/movie.csv'
FIELDS TERMINATED BY ','   
OPTIONALLY ENCLOSED BY '"'   
LINES TERMINATED BY '\r'  #电影描述中出现\r换行字符，
FROM (select 'mid','title','introduction','rating','releasedate' union select*from movie) movie_;
 
 
#导出演员person的信息 == 如果有中文名要中文名，如果没有取英文名
SELECT *  INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/person.csv'
FIELDS TERMINATED BY ','   
OPTIONALLY ENCLOSED BY '"'   
FROM (select 'pid','birth','death','name','biography','birthplace' union 
select person_id,person_birth_day,person_death_day,case  when person_name is null then person_english_name else person_name  end 
  as name,person_biography,person_birth_place from person) person_;
 

#导出电影ID和电影类别之间的对应 【1对1】
SELECT *  INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/movie_to_genre.csv'
FIELDS TERMINATED BY ','   
OPTIONALLY ENCLOSED BY '"'   
FROM (select 'mid','gid' union select*from movie_to_genre) movie_to_genre_;
 
 
#导出演员ID和电影ID之间的对应 【1对多】
SELECT *   INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/person_to_movie.csv'
FIELDS TERMINATED BY ','   
OPTIONALLY ENCLOSED BY '"'   
FROM (select 'pid','mid' union select*from person_to_movie) person_to_movie_;