导出Mysql数据库中的数据

导入CSV格式的数据
(参见http://dev.mysql.com/doc/refman/5.6/en/load-data.html)
1.数据库表(st_pptn_r)
CREATE TABLE st_pptn_r (
STCD         varchar(8)     DEFAULT NULL,
TM         datetime         DEFAULT NULL,
DRP         decimal(5,1)     DEFAULT NULL,
INTV         decimal(5,2)     DEFAULT NULL,
PDR         decimal(5,2)     DEFAULT NULL,
DYP         decimal(5,1)     DEFAULT NULL,
WTH         varchar(1)     DEFAULT NULL,
DATETIME     datetime         DEFAULT NULL
)

2.数据
从Oracle导出数据的SQL为(注意字段顺序):
1.st_pptn_r表
select 
stcd, to_char(tm,'yyyy-mm-dd hh24:mi:ss') tm, drp, intv, pdr, dyp, wth, to_char(datetime,'yyyy-mm-dd hh24:mi:ss') datetime
from st_pptn_r;

2.st_river_r表
select 
stcd, to_char(tm,'yyyy-mm-dd hh24:mi:ss') tm , z, q, xsa, xsavv, xsmxv, flwchrcd, wptn, msqmt, msamt, msvmt 
from st_river_r;

3.st_rsvr_r表
select 
stcd, to_char(tm,'yyyy-mm-dd hh24:mi:ss') tm, rz, inq, w, blrz, otq, rwchrcd, rwptn, inqdr, msqmt 
from st_rsvr_r;

最终的数据格式:
1.st_pptn_r.csv文件
tm,drp,intv,pdr,dyp,wth,datetime
60948000,2014-05-02 04:00:00,0.5,1,,,7,
60948000,2014-05-01 22:00:00,0,1,,,9,

2.st_river_r.csv文件
STCD,TM,Z,Q,XSA,XSAVV,XSMXV,FLWCHRCD,WPTN,MSQMT,MSAMT,MSVMT
61906190,2014-05-02 00:00:00,336.77,,,,,,6,,,
62208300,2014-05-03 10:00:00,24.25,,,,,,6,,,

3.st_rsvr_r.csv文件
STCD,TM,RZ,INQ,W,BLRZ,OTQ,RWCHRCD,RWPTN,INQDR,MSQMT
61615000,2014-05-02 04:00:00,77.13,,56.966,,,,6,,
61615810,2014-05-02 06:00:00,289.56,,71.289,,,,5,,


3.命令操作
    1.st_pptn_r表
 > Load Data InFile 'D:/st_pptn_r.csv' 
Into Table ST_PPTN_R 
fields terminated by ',' 
lines terminated by '\r\n'
ignore 1 lines 
(stcd, tm, @drp, @intv, @pdr, @dyp, @wth, @datetime)
set 
drp=if(@drp='',NULL,@drp),
intv=if(@intv='',NULL,@intv),
pdr=if(@pdr='',NULL,@pdr),
dyp=if(@dyp='',NULL,@dyp),
wth=if(@wth='',NULL,@wth),
datetime=if(@datetime='',NULL,str_to_date(@datetime, '%Y-%m-%d %H:%i:%s'));

2.st_river_r表
 > Load Data InFile 'D:/st_river_r.csv' 
Into Table ST_RIVER_R 
fields terminated by ',' 
lines terminated by '\r\n'
ignore 1 lines 
(stcd,tm,@z,@q,@xsa,@xsavv,@xsmxv,@flwchrcd,@wptn,@msqmt,@msamt,@msvmt)
set 
z=if(@z='',NULL,@z),
q=if(@q='',NULL,@q),
xsa=if(@xsa='',NULL,@xsa),
xsavv=if(@xsavv='',NULL,@xsavv),
xsmxv=if(@xsmxv='',NULL,@xsmxv),
flwchrcd=if(@flwchrcd='',NULL,@flwchrcd),
wptn=if(@wptn='',NULL,@wptn),
msqmt=if(@msqmt='',NULL,@msqmt),
msamt=if(@msamt='',NULL,@msamt),
msvmt=if(@msvmt='',NULL,@msvmt);

3.st_rsvr_r表
 > Load Data InFile 'D:/st_rsvr_r.csv' 
Into Table ST_RSVR_R 
fields terminated by ',' 
lines terminated by '\r\n'
ignore 1 lines 
(stcd, tm, @rz, @inq, @w, @blrz, @otq, @rwchrcd, @rwptn, @inqdr, @msqmt)
set 
rz=if(@rz='',NULL,@rz),
inq=if(@inq='',NULL,@inq),
w=if(@w='',NULL,@w),
blrz=if(@blrz='',NULL,@blrz),
otq=if(@otq='',NULL,@otq),
rwchrcd=if(@rwchrcd='',NULL,@rwchrcd),
rwptn=if(@rwptn='',NULL,@rwptn),
inqdr=if(@inqdr='',NULL,@inqdr),
msqmt=if(@msqmt='',NULL,@msqmt);

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

select * from test_info
into outfile '/tmp/test.csv'
fields terminated by ',' 
optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n';

load data infile '/tmp/test.csv'
into table test_info
fields terminated by ',' 
optionally enclosed by '"' escaped by '"'  
lines terminated by '\r\n';



------------------------------------------------------------------------------------------------------
UPDATE st_pptn_r SET tm = STR_TO_DATE(CONCAT(DATE_FORMAT(tm,'%Y-%m-%d %H'),':00:00'),'%Y-%m-%d %H:%i:%s')

DELETE FROM st_pptn_r WHERE tm IN
(
SELECT b.tm FROM 
(
SELECT a.tm, COUNT(a.stcd) cou FROM st_pptn_r a GROUP BY a.tm
) b
WHERE b.cou<1000
);

DELETE FROM st_pptn_r WHERE tm IN
(
SELECT b.tm FROM 
(
SELECT a.tm, COUNT(a.stcd) cou FROM st_pptn_r a WHERE a.drp<0.0001 GROUP BY a.tm
) b
WHERE b.cou>2800 
);

# DELETE FROM st_pptn_r WHERE tm < '2014-07-01 08:00:00' OR tm > '2014-08-01 08:00:00'

DELETE FROM st_pptn_r WHERE tm<'2014-07-16 08:00:00' OR tm>'2014-07-26 08:00:00'

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

SELECT COUNT(*) cou, 'pptn' FROM st_pptn_r
UNION 
SELECT COUNT(*) cou, 'river' FROM st_river_r
UNION 
SELECT COUNT(*) cou, 'rsvr' FROM st_rsvr_r


SELECT tm, stcd, MAX(drp) drp FROM st_pptn_r WHERE drp>10 GROUP BY tm, stcd ORDER BY drp DESC

SELECT SUM(cou) cou,  LEFT(tm, 11) tmm FROM
(
SELECT tm, COUNT(stcd) cou FROM st_pptn_r WHERE drp>10 GROUP BY tm
) b
 GROUP BY tmm
 ORDER BY cou DESC

mysqldump只导出数据或者只导出表结构

使用mysqldump 指令:

 

 

D:\>mysqldump -u root -proot bookStore>bookStore.sql

 

  备份数据库

 


 


  Linux 代码

1.数据的导入导出
mysqldump -u root -p –no-create-db –default-character-set=utf8 -e
–max_allowed_packet=102400 –net_buffer_length=16384 smfs >
f:\da\smfs\smfs12.sql 

 

 

#mysqldump 数据库名 >数据库备份名  

Mysqldump参数大全

  

参数说明

#mysqldump -A -u 用户名 -p密码 数据库名>数据库备份名  

–all-databases  , -A

  

导出全部数据库。

#mysqldump -d -A –add-drop-table -uroot -p >xxx.sql  

mysqldump  -uroot -p –all-databases

 

–all-tablespaces  , -Y

#mysqldump 数据库名 >数据库备份名

导出全部表空间。

#mysqldump -A -u用户名 -p密码 数据库名>数据库备份名

mysqldump  -uroot -p –all-databases –all-tablespaces

#mysqldump -d -A –add-drop-table -uroot -p >xxx.sql

–no-tablespaces  , -y

 

不导出任何表空间信息。

  1.导出结构不导出数据

mysqldump  -uroot -p –all-databases –no-tablespaces

 

–add-drop-database

  Ruby 代码

每个数据库创建之前添加drop数据库语句。

 

mysqldump  -uroot -p –all-databases –add-drop-database

mysqldump –opt -d 数据库名 -u root -p > xxx.sql  

–add-drop-table

 

每个数据表创建之前添加drop数据表语句。(默认为打开状态,使用–skip-add-drop-table取消选项)

mysqldump –opt -d 数据库名 -u root -p > xxx.sql

mysqldump  -uroot -p –all-databases  (默认添加drop语句)

 

mysqldump  -uroot -p –all-databases –skip-add-drop-table 
(取消drop语句)

  2.导出数据不导出结构

–add-locks

 

在每个表导出之前增加LOCK TABLES并且之后UNLOCK 
TABLE。(默认为打开状态,使用–skip-add-locks取消选项)

  Linux 代码

mysqldump  -uroot -p –all-databases  (默认添加LOCK语句)

 

mysqldump  -uroot -p –all-databases –skip-add-locks   (取消LOCK语句)

mysqldump -t 数据库名 -uroot -p > xxx.sql  

–allow-keywords

 

允许创建是关键词的列名字。这由表名前缀于每个列名做到。

mysqldump -t 数据库名 -uroot -p > xxx.sql

mysqldump  -uroot -p –all-databases –allow-keywords

 

–apply-slave-statements

  3.导出数据和表结构

在’CHANGE MASTER’前添加’STOP SLAVE’,并且在导出的最后添加’START SLAVE’。

 

mysqldump  -uroot -p –all-databases –apply-slave-statements

  Linux 代码

–character-sets-dir

 

字符集文件的目录

mysqldump 数据库名 -uroot -p > xxx.sql  

mysqldump  -uroot -p –all-databases 
–character-sets-dir=/usr/local/mysql/share/mysql/charsets

 

–comments

mysqldump 数据库名 -uroot -p > xxx.sql

附加注释信息。默认为打开,可以用–skip-comments取消

 

mysqldump  -uroot -p –all-databases  (默认记录注释)

  4.导出特定表的结构

mysqldump  -uroot -p –all-databases –skip-comments   (取消注释)

 

–compatible

  Linux 代码

导出的数据将和其它数据库或旧版本的MySQL
相兼容。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等,

 

要使用几个值,用逗号将它们隔开。它并不保证能完全兼容,而是尽量兼容。

mysqldump -uroot -p -B 数据库名 –table 表名 > xxx.sql  

mysqldump  -uroot -p –all-databases –compatible=ansi

 

–compact

mysqldump -uroot -p -B数据库名 –table 表名 > xxx.sql

导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项:–skip-add-drop-table 
–skip-add-locks –skip-comments –skip-disable-keys

 

mysqldump  -uroot -p –all-databases –compact

  导入数据:

–complete-insert,  -c

 

使用完整的insert语句(包含列名称)。这么做能提高插入效率,但是可能会受到max_allowed_packet参数的影响而导致插入失败。

  由于mysqldump导出的是完整的SQL语句,所以用mysql客户程序很容易就能把数据导入了:

mysqldump  -uroot -p –all-databases –complete-insert

 

–compress, -C

  Linux 代码

在客户端和服务器之间启用压缩传递所有信息

 

mysqldump  -uroot -p –all-databases –compress

#mysql 数据库名 < 文件名  

–create-options,  -a

  

在CREATE TABLE语句中包括所有MySQL特性选项。(默认为打开状态)

#source /tmp/xxx.sql  

mysqldump  -uroot -p –all-databases

备份数据库
Linux 代码 #mysqldump 数据库名 数据库备份名 #mysqldump -A -u 用户名
-p密码 数据库名数据库备份…

–databases,  -B

导出几个数据库。参数后面所有名字参量都被看作数据库名。

mysqldump  -uroot -p –databases test mysql

–debug

输出debug信息,用于调试。默认值为:d:t:o,/tmp/mysqldump.trace

mysqldump  -uroot -p –all-databases –debug

mysqldump  -uroot -p –all-databases –debug=” d:t:o,/tmp/debug.trace”

–debug-check

检查内存和打开文件使用说明并退出。

mysqldump  -uroot -p –all-databases –debug-check

–debug-info

输出调试信息并退出

mysqldump  -uroot -p –all-databases –debug-info

–default-character-set

设置默认字符集,默认值为utf8

mysqldump  -uroot -p –all-databases –default-character-set=latin1

–delayed-insert

采用延时插入方式(INSERT DELAYED)导出数据

mysqldump  -uroot -p –all-databases –delayed-insert

–delete-master-logs

master备份后删除日志. 这个参数将自动激活–master-data。

mysqldump  -uroot -p –all-databases –delete-master-logs

–disable-keys

对于每个表,用/*!40000 ALTER TABLE tbl_name DISABLE KEYS
*/;和/*!40000 ALTER TABLE tbl_name ENABLE KEYS
*/;语句引用INSERT语句。这样可以更快地导入dump出来的文件,因为它是在插入所有行后创建索引的。该选项只适合MyISAM表,默认为打开状态。

mysqldump  -uroot -p –all-databases

–dump-slave