Mysql record copy import and export _ MySQL

Mysql record copy import and export bitsCN.com

Mysql record copy import and export

Copy record

Lower case-insensitive function

Password encryption function

Create table 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 |

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

Create table user

> Create table user (uid int primary key auto_increment, uname char (10), upass char (10 ));

Copy record

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

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

You can add the where condition when copying a record.

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

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

Certificate -----------------------------------------------------------------------------------------------------------------------------------------------------

Import data

Load data infile...

Syntax:

Load data infile 'file absolute path' into table name fields terminated by 'field delimiter 'lines terminated by' record delimiter ';

Example:

> Load data infile '/tmp/a.txt' into table t20 fields terminated by ': 'Lines terminated by'/N ';

Export data

Select... into outfile...

Syntax:

Select field from table name into outfile' absolute path of the file 'Fields terminated by' field segmentation server 'lines terminated by' record delimiter ';

Example:

> Select * from t20 into outfile '/tmp/B .txt' fields terminated by ': 'Lines terminated by'/N ';

BitsCN.com