username | userpwd | age |
---|---|---|
admin | admin | 18 |
root | root | 18 |
cat | cat | 17 |
dog | dog | 15 |
dog1 | dog1 | 15 |
zhu | zhu | 21 |
create database youku; --创建数据库
use youku; -- 选择数据库
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`username` varchar(11) DEFAULT NULL,
`userpwd` varchar(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
//插入数据
INSERT INTO `user` VALUES ('admin','admin',18),('root','root',18),('cat','cat',17),('dog','dog',15),('dog1','dog1',15),('zhu','zhu',21);
-- 表数据导出
SELECT * FROM user
INTO OUTFILE 'F:/user.txt';
-- 根据需要设置输出的格式,每一行数据','分隔,同时字符串型的数据用双引号""包含
SELECT * FROM user INTO OUTFILE 'F:/user1.txt'
Fields TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
-- 表数据导入 (注意,导入的数据的列数要对应表的各列)
-- 先删除数据
use youku;
delete from user where age>10;
select * from user;
LOAD DATA INFILE 'F:/user.txt' INTO TABLE user;
select * from user;
-- 导入有特殊格式的表user1.txt(去除分隔号','和字串的引号“”)
use youku;
delete from user where age>10;
select * from user;
LOAD DATA INFILE 'F:/user1.txt' INTO TABLE user
Fields TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
select * from user;
=================
-- 表数据导出
SELECT * FROM user
INTO OUTFILE 'F:/user.txt';
admin admin 18
root root 18
cat cat 17
dog dog 15
dog1 dog1 15
zhu zhu 21
-- 根据需要设置输出的格式,每一行数据','分隔,同时字符串型的数据用双引号""包含
SELECT * FROM user INTO OUTFILE 'F:/user1.txt'
Fields TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
"admin","admin",18
"root","root",18
"cat","cat",17
"dog","dog",15
"dog1","dog1",15
"zhu","zhu",21
结语:数据的具体导入导出格式大家可以自己尝试一下。但个人建议涉及格式转换的话尽量先用其他工具,为防止出错还是尽量只转换纯文本列数据(无特殊格式)。