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