MySQL数据表格导入导出

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';

-- 根据需要设置输出的格式,每一行数据','分隔,同时字符串型的数据用双引号""包含

SELECT * FROM user INTO OUTFILE 'F:/user1.txt'

    Fields TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';