如何从 MySQL数据库中导入导出数据

上两篇我们介绍了关系型数据库中内连接、外连接及自连接的用法,数据已经是存储在数据库中,假如现在要把查询结果导出到excel或csv文档,或者是要把存储在Excel、csv的数据导入到数据库,又应该如何操作,今天主要介绍MySQL数据库中数据导入导出的实现方法。

一、导入数据

导入数据有两种方法,

方法一:load data infile语句

针对数据量比较大,或者数据已经是保存在excel、csv文档中,则使用load data infile语句。

语法: load data infile‘文件路径’into table表名 [option]。

其中option参数有五个选项,这里主要介绍我在导入数据时常用到的两个选项:

fields terminated by '字符串':字符串为字段的分隔符,假如要导入的csv文档各字段的分隔符是“,”,则可以表述为“fields terminated by ‘,’”;

ignore 1 lines:忽略首行,即如果导入的数据中包括列名行,则需要加上这一句,否则可以省略。

在执行导入语句前,首先要在数据库中创建一张表,然后才是执行load data infile语句。

下面以Personnel表为例,现在Personnel表数据保存在csv文档中:

根据前面说的步骤,首先在数据库中创建一张Personnel表,建表语句是

“Create table Personnel(EmployeeID intnot null,EmployeeName varchar(20),ManagerID int);”;

然后运行“select * fromPersonnel;”,可以看到现在Personnl表还没有数据;

接下来运行导入语句:

“Load data local infile‘C:/Users/aaa/Desktop/Personnel.csv’into table Personnelfields terminated by ‘,’ignore 1 lines;”

查询导入数据后Personnel表数据,结果如下:

可以看到,导入后的数据与csv文档中的数据是基本一致的,唯一不同的地方时Susan Ford的ManagerID,导入数据库后变为0,而csv文档中是空白,这个问题留待后续解决,现在已经把Personel表数据导入到数据库。

方法二:insert into语句

当需要导入的数据量较小时,可以考虑使用insertinto语句。

语法:insert into 表名(列名1,列名2,...)values(值1,值2,...)

使用该语句时,字段列的数量和值的数量必须相同,且列和值要一一对应。

继续以Person表为例,Personnel表只有10条记录、3列,可以使用insert into语句。

在使用insert into语句时,同样需要先创建Personnel表,然后再执行inert into语句。建表语句见方法一,insert into语句如下:

insert intoPersonnelvalues(1,"Susan Ford",NULL),(2,"Harold Jenkins",1),(3,"Jacqueline Baker",1),(4,"Richard Fielding",1),(5,"Carol Bland",2),(6,"Janet Midling",2),(7,"Andrew Brown",3),(8,"Anne Nichol",4),(9,"Bradley Cash",4),(10,"David Sweet",5);

以上就是MySQL数据库中导入数据的两种方法,针对不同情况采用不同的导入方法,当然第一种方法是更常用的。

二、导出数据

介绍完导入数据后,再看看在MySQL数据库中如何将查询结果导出到txt或csv文档。

导出数据常用的方法有三种。

方法一:复制、粘贴法

以上一篇中自连接查询结果为例,查询结果如下:

现在要把以上结果导出到excel表格,因为返回的数据较少,可以直接在数据库中选中这些数据,按复制键(ctrl+c),然后在excel表格中按粘贴键(ctrl+v),即完成数据导出工作。操作过程如下:

选中要复制的数据,然后按ctrl+c;

在excel表格中,任意选中一个单元格,按ctrl+v。

以上就是方法一的操作过程,较简单,查询结果较少时适用此方法。

方法二:菜单式操作法

在MySQL查询结果界面中,可以看到一个图标Export,如下图红圈处:

把鼠标对准图标,可以看到一个提示:“Export recordset toan external file”

点击这个图标就能把查询结果导出到我们想要的位置,假如现在把数据导出到桌面,并把文件命名为“导出数据”,操作界面如下:

点击保存后,我们就能在桌面看到文件“导出数据”,保存类型中可以选择不同的文件类型,如CSV、JSON、XML、Excel等。

以上是方法二的操作路径,方法也是比较简单,同样适用于数据量较小的情形,如果需要导出的数据量较大,此方法也可以用,但耗费时间就比较长,有什么方法可以快速导出数据量较大时的数据,方法三就是针对数据量较大时的操作方法。

方法三:select into outfile语句

语法:select 列名from 表名 [where]into outfile “目标文件路径”[option]。

“[]”表示可选项,“where”是指筛选条件,option参数有五个可选项,下面主要介绍我在实际运用中常用到的选项。

fields terminated by:设置字符串为字段的分隔符,默认为\t。

继续以导出自连接查询结果为例,用语句表示如下:

“select Employees.EmployeeName as EmployeeName,Managers.EmployeeName as ManagerNamefrom Personnel as Employeesleft join Personnel as Managerson Employees.ManagerID =Managers.EmployeeIDorder by Employees.EmployeeIDinto outfile C:/ProgramData/MySQL/MySQLServer5.7/Uploads/daochu_shuju.csv"fields terminated by ',' ;”。 Employeename、Managername两列是要导出的列,left join表示连接条件,根据EmployeeID排序,outfile后面接的这一段表示导出的数据在电脑中的位置,导出的数据文件名是“daochu_shuju”,csv格式,字段之间用逗号分隔,如下所示:

文件所在位置

字段之间通过“,”分隔

以上就是通过select into outfile语句从数据库导出数据的操作方法,关于数据导入导出的介绍就到此为止,下一篇将介绍我对数据分析、数据挖掘两个岗位的认识,偏概念性介绍,不涉及技术方面的操作。

  • 发表于:
  • 原文链接http://kuaibao.qq.com/s/20180406G1FC8R00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券