MySQL数据库基础——本地文件交互

发表于

从这一篇开始，大概会花四五篇的内容篇幅，归纳整理一下之前学过的SQL数据库，一来可以为接下来数据分析工作提前巩固基础，二来把以前学的SQL内容系统化、结构化。

今天这一篇仅涉及MySQL与本地文本文件的导入导出操作，暂不涉及主要查询语言以及MySQL与R语言和Python的交互。

平台使用Navicat Premium（当然你也可以使用MySQL自带的workbench或者MySQL Conmand line）。

以下仅涉及MySQL中使用命令行语句导入/导出本地磁盘的文本文件（csv\txt文件）。

文件导入（csv）：

在导入本地文件之前，请确保你的MySQL设置有本地文件导入导出权限。

在导入MySQL之前，需要在指定数据库中先建立空表，以备之后导入。

1
2
3
4
5
6
7
8
9
复制代码USE db1;
CREATE TABLE subway (
    ID      INTEGER(5)  NOT NULL AUTO_INCREMENT,   -- ID是主键，格式为整数，非空值、自增列
    address VARCHAR(10) NOT NULL,
    lon     FLOAT(10,6) NOT NULL,                  -- 浮点
    lat     FLOAT(10,6) NOT NULL,                  -- 浮点
    Type    VARCHAR(10) NOT NULL,                  -- 字符型（10位）
    PRIMARY KEY (ID)                            -- 设定主键
    );

创建空表的语句格式如上： 

1
2
3
4
5
复制代码CREATE TABLE 表名 (
  column1 类型(字符位数)  是否允许为空值 自增列（可选） 默认值（可选）,
  column2 类型(字符位数)  是否允许为空值 自增列（可选） 默认值（可选）,
  PRIMARY KEY (column1)
  );

此时在MySQL中生成一个空表（仅有字段名称）。

以下是导入语句：

1
2
3
4
5
6
7
8
9
复制代码load data local infile 'D:/subway.txt' 
into table db1.subway          -- 导入本地文件语句
character set gbk                -- 设置导入文件编码     
fields terminated by '\t'   -- 指定txt文件内的字段分隔符
optionally enclosed by '"'  -- 指定字符闭合符(可选参数，有些格式txt会设置字符使用双引号/单引号包括等格式)
escaped by '"'                 -- 指定转义符（字符内含符号与闭合符冲突，使用何种符号进行包括并转义，使其保留原意）
lines terminated by '\r\n'  -- 指定换行符
ignore 1 lines                 -- 指定从文件第几行开始导入（如果本地文件有行名，需要略过一行）
(address,lon,lat,Type);             -- 最后一行指定要导入的列名(次内列名需与之前新建的空表列名严格匹配)

主键可以设定为导入列中的某一列(保证无缺失值无重复值即可)，并不是必须设置的。


做简单的表格信息概览：

1
复制代码desc db1.subway


文件导出（TXT）：

将刚才导入的subway文件导出到本地。

1
2
3
4
5
6
7
8
复制代码select "ID","address","lon","lat","Type"   -- 为要导出的字典命名
union select * from db1.subway       -- 指定要从目标表中导出的字段（与第一句指定的字段严格对应）
into outfile 'D:/SUBWAY.txt'            -- 导出目录及文件名
character set gbk                         -- 设置输出编码
FIELDS TERMINATED BY '\t'            -- 字段分隔符
-- OPTIONALLY ENCLOSED BY '"'                  -- 文本包括符号(可选，这里注释掉了)            
-- escaped by '"'                              -- 冲突转义符（可选，这里注释掉了）
LINES TERMINATED BY '\r\n';          -- 换行符


文件导入（csv）:

仍然是导入之前先新建空表：

1
2
3
4
5
6
7
8
9
10
复制代码CREATE TABLE President (

    ID         INTEGER(5)  NOT NULL AUTO_INCREMENT,   -- ID是主键，格式为整数，非空值、自增列
    STATE_NAME VARCHAR(15) NOT NULL,                  -- 字符型（15位）
    STATE_ABBR VARCHAR(5)  NOT NULL,                  -- 浮点
    Count      INTEGER(5)  NOT NULL,                  -- 整数型（5位）
    Clinton    FLOAT(8,4)  NOT NULL,                  -- 浮点型（8位，保留四位小数）
    Trump      FLOAT(8,4)  NOT NULL,
    Results    VARCHAR(5)  NOT NULL,    PRIMARY KEY (ID)                             
    );


导入本地CSV文件：

1
2
3
4
5
6
7
8
9
复制代码load data local infile 'D:/President.csv' 
into table db1.president       -- 导入本地文件语句
-- character set gbk                  -- 设置导入文件编码 (因为原始文件就是utf-8编码的，这里无需指定，如果不是需要单独指定) 
fields terminated by ','    -- 指定txt文件内的字段分隔符
optionally enclosed by '"'  -- 指定字符闭合符(可选参数，有些格式txt会设置字符使用双引号/单引号包括等格式)
escaped by '"'                 -- 指定转义符（字符内含符号与闭合符冲突，使用何种符号进行包括并转义，使其保留原意）
lines terminated by '\r\n'  -- 指定换行符
ignore 1 lines                 -- 指定从文件第几行开始导入（如果本地文件有行名，需要略过一行）
(STATE_NAME,STATE_ABBR,Count,Clinton,Trump,Results);


DESC db1.president


文件导出（csv）:

将刚才导入的President文件导出到本地csv文件。

1
2
3
4
5
6
7
8
复制代码select "ID","STATE_NAME","STATE_ABBR","Count","Clinton","Trump","Results"  -- 为要导出的字典命名
union select * from db1.president        -- 指定要从目标表中导出的字段（与第一句指定的字段严格对应）
into outfile 'D:/President1.csv'            -- 导出目录及文件名
character set gbk                             -- 设置输出编码
FIELDS TERMINATED BY ','                 -- 字段分隔符
OPTIONALLY ENCLOSED BY '"'               -- 文本包括符号(可选，这里注释掉了)
escaped by '"'                              -- 冲突转义符（可选，这里注释掉了）
LINES TERMINATED BY '\r\n';              -- 换行符


除此之外，还有几个基础的增删命令需要掌握：

插入命令：

1
2
3
4
5
6
7
8
9
复制代码INSERT INTO president
   (LastName, Address)          -- 插入列名称
VALUES 
  ('Wilson', 'Champs-Elysees'); -- 具体值
select count(*) from db1.president
INSERT INTO db1.president (STATE_NAME,STATE_ABBR,`Count`,Clinton,Trump,Results) 
VALUES ('Wilson', 'WL',10,0.4555,0.2344,'T');

select count(*) from db1.president




关于删除表：

1
2
3
4
复制代码truncate db1.president;       -- 删除表内所有记录（保留空表）
drop table db1.president;  -- 彻底删除表（数据库中该表将不存在）
drop table db1.subway;     -- 彻底删除表（数据库中该表将不存在）
select count(*) from db1.president


本文小结：

文件导入（txt\csv）

数据导出（TXT\csv）

表创建

表删除

记录插入

在线课程请点击文末原文链接：

Hellobi Live | R语言可视化在商务场景中的应用
往期案例数据请移步本人GitHub：
github.com/ljtyduyu/Da…

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%