程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> Mysql 記錄復制導入和導出

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

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved