mysql LOAD DATA LOCAL INFILE

jhclb82123 2016-09-06 09:53:24
目前在自学mysql5.5用户手册
遇上一个问题
基础差,无解
问题如下:
建立一个表:
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

要在pet表中查入记录;
用load data local infile命令
文本内容如下:
name owner species sex birth death
Fluffy Harold cat f 1993-02-04
Claws Gwen cat m 1994-03-17
Buffy Harold dog f 1989-05-13
Fang Benny dog m 1990-08-27
Bowser Diane dog m 1979-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11
Whistler Gwen bird 1997-12-09
Slim Benny snake m 1996-04-29

假设该文本存放在d:\下

用记事本如何编辑文字?
在MYSQL命令行怎么写?

谢谢赐教!!!
...全文
297 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
jhclb82123 2016-09-18
  • 打赏
  • 举报
回复
引用 3 楼 ACMAIN_CHM 的回复:
引用
To read a file containing jokes that are separated by lines consisting of %%, you can do this CREATE TABLE jokes (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, joke TEXT NOT NULL); LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes FIELDS TERMINATED BY '' LINES TERMINATED BY '\n%%\n' (joke); FIELDS [OPTIONALLY] ENCLOSED BY controls quoting of fields. For output (SELECT ... INTO OUTFILE), if you omit the word OPTIONALLY, all fields are enclosed by the ENCLOSED BY character. An example of such output (using a comma as the field delimiter) is shown here: "1","a string","100.20" "2","a string containing a , comma","102.20" "3","a string containing a \" quote","102.20" "4","a string containing a \", quote and comma","102.20" If you specify OPTIONALLY, the ENCLOSED BY character is used only to enclose values from columns that have a string data type (such as CHAR, BINARY, TEXT, or ENUM): 1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a \" quote",102.20 4,"a string containing a \", quote and comma",102.20
.
版主,我目前看书阅读到存储过程这里, 觉得很是迷惑, 几乎没法理解存储过程是干什么用的,这个貌似和之前学的命令行有很大区别, 就卡在这个存储过程上几天了, 我想成为一位数据库方面维护方面的人员 看各个招聘资历要求都需要具有“熟悉mysql存储过程,能编制复杂的存储过程的能力。” 所以如果过不了存储过程这个坎,我觉学mysql和Excel没啥区别。 望指教~~~
致命的西瓜 2016-09-18
  • 打赏
  • 举报
回复
引用 5 楼 jhclb82123 的回复:
版主,我目前看书阅读到存储过程这里, 觉得很是迷惑, 几乎没法理解存储过程是干什么用的,这个貌似和之前学的命令行有很大区别, 就卡在这个存储过程上几天了, 我想成为一位数据库方面维护方面的人员 看各个招聘资历要求都需要具有“熟悉mysql存储过程,能编制复杂的存储过程的能力。” 所以如果过不了存储过程这个坎,我觉学mysql和Excel没啥区别。 望指教~~~
没有想象的那么复杂,写几个简单的就能理解了,一般就是做数据流转或者业务处理用的
jhclb82123 2016-09-09
  • 打赏
  • 举报
回复
谢谢版主,已经解决了,
ACMAIN_CHM 2016-09-07
  • 打赏
  • 举报
回复
引用
To read a file containing jokes that are separated by lines consisting of %%, you can do this CREATE TABLE jokes (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, joke TEXT NOT NULL); LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes FIELDS TERMINATED BY '' LINES TERMINATED BY '\n%%\n' (joke); FIELDS [OPTIONALLY] ENCLOSED BY controls quoting of fields. For output (SELECT ... INTO OUTFILE), if you omit the word OPTIONALLY, all fields are enclosed by the ENCLOSED BY character. An example of such output (using a comma as the field delimiter) is shown here: "1","a string","100.20" "2","a string containing a , comma","102.20" "3","a string containing a \" quote","102.20" "4","a string containing a \", quote and comma","102.20" If you specify OPTIONALLY, the ENCLOSED BY character is used only to enclose values from columns that have a string data type (such as CHAR, BINARY, TEXT, or ENUM): 1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a \" quote",102.20 4,"a string containing a \", quote and comma",102.20
.
jhclb82123 2016-09-06
  • 打赏
  • 举报
回复
引用 1 楼 ACMAIN_CHM 的回复:
load data infile
版主,貌似没啥区别,还是把所有的文字挤在第一行第一列,无法像用insert into table名 values 字段;这样条理清晰, 想用TXT文件只是输入方便;
ACMAIN_CHM 2016-09-06
  • 打赏
  • 举报
回复
load data infile

56,675

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧