从这一篇开始,大概会花四五篇的内容篇幅,归纳整理一下之前学过的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
欢迎关注数据小魔方qq交流群
本文分享自微信公众号 - 数据小魔方(datamofang)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。