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_;
