MySQL-数据导入与导出

数据导入

把文件系统的内容导入到数据库中

load data infile "文件名"
into table 表名
fields terminated by "分隔符"
lines terminated by "\n";

导入步骤

• 在数据库中创建对应的表

create table scoretab(
id int,
name varchar(15),
score float(5,2),
number bigint,
class char(7)
);

• 把文件拷贝到数据库的默认搜索路径中

# 查看文件的默认搜索目录
show variables like "secure_file_priv";

• 执行数据导入语句

load data infile "/var/lib/mysql-files/scoretable.csv"
into table scoretab
fields terminated by ","
lines terminated by "\n";

数据导出

将数据库中表的记录导出到系统文件里

select ... from 表名
into outfile "/var/lib/mysql-files/文件名"
fields terminated by "分隔符"
lines terminated by "\n";

表的复制

create table 表名 select .. from 表名 where 条件;

示例

# 复制 MOSHOU.sanguo表的全部记录和字段,sanguo2
create table sanguo2 select * from MOSHOU.sanguo;
# 复制MOSHOU.sanguo表的前3条记录,sanguo3
create table sanguo3 select * from MOSHOU.sanguo limit 3;
# 复制MOSHOU.sanguo表的id,name,country三个字段的前5条记录,sanguo4
create table sanguo4 select id,name,country from MOSHOU.sanguo limit 5;

复制表结构

create table 表名 select * from 表名 where false;
# 复制表的时候不会把原表的键(key)属性复制过来