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

Wesley13
• 阅读 772

从这一篇开始,大概会花四五篇的内容篇幅,归纳整理一下之前学过的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)                               -- 设定主键
    );

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

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

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

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

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

做简单的表格信息概览:

desc db1.subway

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

文件导出(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';                -- 换行符

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

文件导入(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)                             
    );

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

导入本地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);

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

DESC db1.president

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

文件导出(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';              -- 换行符

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

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

插入命令:

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

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

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

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

关于删除表:

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

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

本文小结:

文件导入(txt\csv)

数据导出(TXT\csv)

表创建

表删除

记录插入

在线课程请点击文末原文链接:
往期案例数据请移步本人GitHub:
https://github.com/ljtyduyu/DataWarehouse/tree/master/File

欢迎关注数据小魔方qq交流群


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

本文分享自微信公众号 - 数据小魔方(datamofang)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
待兔 待兔
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Stella981 Stella981
3年前
Android蓝牙连接汽车OBD设备
//设备连接public class BluetoothConnect implements Runnable {    private static final UUID CONNECT_UUID  UUID.fromString("0000110100001000800000805F9B34FB");
Wesley13 Wesley13
3年前
thinkphp 基本配置
12returnarray(34//定义数据库连接信息5'DB\_TYPE''mysql',//指定数据库是mysql67'DB\_HOST''localhost',89'DB\_NAME''uchome',//数据库名1011'DB\_USER''root
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这