mysql使用技巧

删除表中数据

删除整张表的数据!!!

delete from 表名;

导入数据基本语法
原文

一、loaddata 导入数据的语法 load data infile

基本语法
复制代码

load data [low_priority] [local] infile ‘file_name.txt’ [replace|ignore]
into table table_name
fields
[terminated by ‘\t’]
[[OPTIONALLY] enclosed by ‘’]
[escaped by ‘’ ]
[lines terminated by ‘\n’]
[ignore number lines]
[(col_name, )]

复制代码

参数说明
参 数 说 明 备 注
low_priority MySQL将会等到没有其他人读这个表的时候，才把数据插入 load data low_priority infile “/tmp/harara.sql” into table orders;
local 表明从客户主机读文件。如果local没指定，文件必须位于服务器上 load data low_priority local infile “/tmp/harara.sql” into table orders;
replace 如果你指定replace，新行将代替有相同的唯一健值的现有行 load data low_priority local infile “/tmp/harara.sql” replace into table orders;
ignore 如果你指定ignore，跳过有唯一键的现有行的重复行输入 load data low_priority local infile “/tmp/harara.sql” ignore into table orders;
fields terminated by 描述字段的分隔符，默认情况下是tab字符（\t） load data infile “/tmp/harara.sql” replace into table orders fields terminated by ‘,’ enclosed by ‘"’ escaped by ‘’;
fields enclosed by 描述的是字段的括起字符。
fields escaped by escaped by 描述的转义字符。默认的是反斜杠: \
lines terminated by’\n’ 指定每条记录的分隔符默认为"\n" 即为换行符 load data infile “/tmp/harara.sql” replace into table test fields terminated by ‘,’ lines terminated by ‘\n’;
(col_name, ) 可以按指定的列把文件导入到数据库中

当我们要把数据的一部分内容导入，需要加入一些栏目（列/字段/field）到MySQL数据库中，以适应一些额外的需要。

比如，我们要从Access数据库升级到MySQL数据库的时候，下面的例子显示了如何向指定的栏目(field)中导入数据：

load data infile “/home/harara.sql” into table orders(order_number,order_date,customer_id)

导入数据完整示例

load data local infile ‘/var/lib/mysql/emp8/customers.txt’ replace into table lf_lanx_tmplp1 character set gbk
fields terminated by ‘,’ enclosed by ‘"’
lines terminated by ‘\n’
(TMPl_NAME);

二、loaddata 导出数据的语法 select into outfile

基本语法
复制代码

select … into outfile ‘file_name’
[character set charset_name]
[export_options]

export_options:
[{fields | columns}
[terminated by ‘string’]
[[optionally] enclosed by ‘char’]
[escaped by ‘char’]
]
[lines
[starting by ‘string’]
[terminated by ‘string’]
]

复制代码

参数说明
参 数 说 明
fields terminated by ‘str’ 设置字段之间的分隔符，默认是"\t"
fields enclosed by ‘char’ 设置包括住字段的值的符号，如单引号、双引号等，默认情况下不使用任何符号
fields optionally enclosed by ‘char’ 设置括住CHAR、VARCHAR和TEXT等字符型字段的分隔符，默认情况下不使用任何符号
fields escaped by ‘char’ 设置转义字符，默认值为""
lines starting by ‘str’ 设置每行数据开头的字符，可以为单个或多个字符。默认情况下不使用任何字符
lines terminated by ‘char’ 设置每行数据结尾的字符，可以为单个或多个字符。默认值是"\n"

完整实例

SELECT TMPl_NAME INTO OUTFILE ‘u/customers.txt’
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’
LINES TERMINATED BY ‘\n’
FROM LF_LANX_TMPLP1;