Mysql记录复制导入和导出

Mysql记录复制导入和导出

 

复制记录

lower  不区分大小写函数

password 加密函数

  www.2cto.com  

创建表employee

> create table employee (id int,name char(10),agi int,sex enum(&＃39;M&＃39;,&＃39;F&＃39;),department char(10));

> insert into employee values (23,&＃39;john&＃39;,27,&＃39;M&＃39;,&＃39;Engi&＃39;),(31,&＃39;Sue&＃39;,31,&＃39;F&＃39;,&＃39;Fiance&＃39;),(113,&＃39;David&＃39;,26,&＃39;M&＃39;,&＃39;Admin&＃39;);

> 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=&＃39;Fiance&＃39;;

> select * from user;

+-----+-------+------------+

| uid | uname | upass      |

+-----+-------+------------+

|   1 | john  | *DACDE7F57 | 

|   2 | sue   | *934B89788 | 

|   3 | david | *8201E0C1B | 

|   4 | Sue   | *287E48EAA | 

+-----+-------+------------+

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

  www.2cto.com  

导入数据

load data infile...

 

语法：

 

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

 

样例：

 

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

 

导出数据

select...into outfile...

 

语法：

 

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

 

样例：

 

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