Mysql 记录复制导入和导出

Mysql 记录复制导入和导出



复制记录
lower  不区分大小写函数
password 加密函数

创建表employee
> create table employee (id int,name char(10),agi int,sex enum('M','F'),department char(10));
> insert into employee values (23,'john',27,'M','Engi'),(31,'Sue',31,'F','Fiance'),(113,'David',26,'M','Admin');
> select * from employee;
+------+-------+------+------+------------+
| id   | name  | agi  | sex  | department |
+------+-------+------+------+------------+
|   23 | john  |   27 | M    | Engi       |
|   31 | Sue   |   31 | F    | Fiance     |
|  113 | David |   26 | M    | Admin      |
+------+-------+------+------+------------+

创建表user
> create table user (uid int primary key auto_increment,uname char(10),upass char(10));

复制记录
> insert into user (uname,upass) select lower(name),password(lower(name)) from employee;
> select * from user;
+-----+-------+------------+
| uid | uname | upass      |
+-----+-------+------------+
|   1 | john  | *DACDE7F57 |
|   2 | sue   | *934B89788 |
|   3 | david | *8201E0C1B |
+-----+-------+------------+


复制记录时，可以添加where条件
> insert into user (uname,upass) select (name),password(name) from employee where department='Fiance';
> select * from user;
+-----+-------+------------+
| uid | uname | upass      |
+-----+-------+------------+
|   1 | john  | *DACDE7F57 |
|   2 | sue   | *934B89788 |
|   3 | david | *8201E0C1B |
|   4 | Sue   | *287E48EAA |
+-----+-------+------------+



--------------------------------------------------------------------------------------------------------------------------------------------------------


导入数据

load data infile...

语法：

load data infile '文件绝对路径' into table 表名 fields terminated by '字段分割符' lines terminated by '记录分割符';

样例：

> load data infile '/tmp/a.txt' into table t20 fields terminated by ':' lines terminated by '\n';


导出数据

select...into outfile...

语法：

select 字段 from 表名 into outfile '文件绝对路径' fields terminated by '字段分割服' lines terminated by '记录分割符';

样例：

> select * from t20 into outfile '/tmp/b.txt' fields terminated by ':' lines terminated by '\n';