专栏首页数据小魔方MySQL数据库基础——本地文件交互

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

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

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

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

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

文件导入(csv):

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

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

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)                               -- 设定主键
    );

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

CREATE TABLE 表名 (
  column1 类型(字符位数)  是否允许为空值 自增列(可选) 默认值(可选),
  column2 类型(字符位数)  是否允许为空值 自增列(可选) 默认值(可选),
  PRIMARY KEY (column1)
  );

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

以下是导入语句:

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);-- 最后一行指定要导入的列名(次内列名需与之前新建的空表列名严格匹配)

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

做简单的表格信息概览:

desc db1.subway

文件导出(TXT):

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

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):

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

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文件:

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文件。

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';              -- 换行符

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

插入命令:

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

关于删除表:

truncate db1.president;    -- 删除表内所有记录(保留空表)
drop table db1.president;  -- 彻底删除表(数据库中该表将不存在)
drop table db1.subway;     -- 彻底删除表(数据库中该表将不存在)
select count(*) from db1.president

本文小结:

文件导入(txt\csv)

数据导出(TXT\csv)

表创建

表删除

记录插入 往期案例数据请移步本人GitHub: https://github.com/ljtyduyu/DataWarehouse/tree/master/File

本文分享自微信公众号 - 数据小魔方(datamofang)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2017-12-05

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 必懂的NoSQL理论-Map-Reduce(上)

    本文主要内容:基本的Map-Reduce Map-Reduce 基本原理 面向聚合的数据库能够兴起很大一部分原因是由于集群的增长。数据库运行在集群环境中意...

    ImportSource
  • 数据表水平拆分

    水平拆分就是把一张大表的内容拆分到不同数据表中,来提升数据库的性能 1张表 -> N张表 ? 拆分是根据路由算法来决定 常用的路由算法:哈希值取模 例如 ...

    dys
  • mongodb 性能篇

    一、  索引及其优化 索引的概述 数据库的索引好比是一本书前面的目录,能加快数据查询的速度。 适当的地方增加索引,不合理的地方删除次优索引,能优化性能较差的应用...

    一夕如环
  • ACMUG 2016My SQL全球开源数据库年会

    关于中国MySQL用户组 MySQL 是全球范围内最流行的开源数据库。其稳定,高效,易用使其成为互联网应用之首选数据库平台。被Facebook、Twitter、...

    IT大咖说
  • 分库后如何处理分页?

    在数据量过大以后,通常都会进行分库操作,把一张表拆分到不同数据库中 例如 tb1 表被拆分到3个库中,分库1、分库2、分库3 现在想执行分页操作 SELECT ...

    dys
  • mongodb架构篇

    一、  复制集 Replica set的架构 Replica set的部署 主从操作日志 在local数据库里面:db.oplog.rs.find() 日志的基...

    一夕如环
  • mysql命令之对用户,库,表操作--基础教程(三)

    创建用户: 进入在root用户进行操作: CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456'; 授权...

    前朝楚水
  • mysql基本命令-基础教程(二)

    用得最多的就是对数据的 增、删、改、查; 首先说明: 具体数据是存在表里面(这个东西可以想象一下excel表格); 表又存在数据库; 一个mysql软件里面可...

    前朝楚水
  • snowflake升级版全局id生成

    1. 背景 分布式系统或者微服务架构基本都采用了分库分表的设计,全局唯一id生成的需求变得很迫切。 传统的单体应用,使用单库,数据库中自增id可以很方便实现。分...

    aoho求索
  • 使用Redis统计活跃用户

    统计活跃用户这个案例非常经典,也是我当时学习redis时,接触到的第一个让我眼睛一亮的使用方式 场景 用户登录后需要记录,以便以后进行登录统计 统计需求主...

    dys

扫码关注云+社区

领取腾讯云代金券