复制记录 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'),(
复制记录
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';