MYSQL常用命令汇总

1、为了回显以输出被执行的命令

mysql -vvv -h$DB_IP -P$DB_PORT -D$DB_NAME -u$DB_USER –p

2、命令提示符操作，从linux导出到windows
表结构

mysqldump --no-data -h$DB_IP -P$DB_PORT -u$DB_USER -p $DB_NAME > D:\db.sql

表结构和数据

mysqldump -h$DB_IP -P$DB_PORT -u$DB_USER -p $DB_NAME > D:\db.sql

3、LINUX环境中导出表数据，不包括表结构

mysqldump --single-transaction --set-gtid-purged=off -t -h$DB_IP -P$DB_PORT -u$DB_USER -p $DB_NAME $TABLE1_NAME $TABLE2_NAME > db.sql

导出数据库

mysqldump -h$DB_IP -P$DB_PORT -u$DB_USER -p $DB_NAME > db.sql

导出一个表

mysqldump -h$DB_IP -P$DB_PORT -u$DB_USER -p $DB_NAME $TABLE_NAME > db.sql

导出一个数据库结构

mysqldump -h$DB_IP -P$DB_PORT -u$DB_USER -p -d –add-drop-table $DB_NAME > db.sql

#-d 没有数据 
#–add-drop-table 在每个create语句之前增加一个drop table

4、

load data local infile "D:/tbl_app_trans_log_index.txt" into table tbl_app_trans_log_index(transIndexCode);
SELECT * FROM 表名 INTO OUTFILE 'c:/txt1.txt';
select * from tbl_app_trans_log where retrieval_num<>'' and retrieval_num in (select retrieval_num from tbl_app_trans_log where retrieval_num<>'' group by retrieval_num having count(*)>1) order by retrieval_num;
select * from tbl_interface_trans_log_index group by transIndexCode having count(*)>1 into outfile "D:/interface.txt"
select * from tbl_channel_msg_log_1 where trans_submit_time > '20161202131543' into outfile './1205.txt'  数据库服务器data/用户名目录下
load data infile '/home/cp_mgmdb/data/cp_mgmdb/output2.txt' into table tbl_tmp_chnl_trans_log_2;

5、查看正在运行的线程

show processlist;

6、查看看建表语句

SHOW CREATE TABLE tbl_name;

修改字段类型

ALTER TABLE tbl_name MODIFY COLUMN fld_nm CHAR(10) DEFAULT ' ' NOT NULL (CHARACTER SET utf8 COLLATE utf8_general_ci);

增加字段

ALTER TABLE tbl_name ADD fld_nm_1 CHAR(10) DEFAULT ' ' NOT NULL AFTER fld_nm_2;

修改字段顺序

ALTER TABLE tbl_name MODIFY fld_nm_1 CHAR(10) AFTER fld_nm_2;

修改字段名 

ALTER TABLE tbl_name CHANGE old_fld_nm new_fld_nm CHAR(10) DEFAULT ' ' NOT NULL;

删除表

DROP TABLE IF EXISTS tbl_name;

清空表数据

DELETE FROM tbl_name;

删除字段

ALTER TABLE tbl_name DROP COLUMN fld_nm;

创建索引

create index index_name on table_name(fld_nm1,fld_nm2,...);
CREATE [UNIQUE] INDEX index_name ON table_name(fld_nm1,fld_nm2,...);

删除索引

drop index index_name on table_name;

7、时间类型字段，自动更新时间

rec_upd_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL

时间类型字段，非自动更新时间

rec_crt_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL

8、设置编码

set names utf8;
show variables like 'character_set_%';

9、MYSQL存储过程批量造数据

(1)创建
--/
CREATE PROCEDURE genData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i < 10 DO
        INSERT INTO tbl_cupa_trans_log_1 (pri_key, alt_key, mchnt_no, term_no, trans_index_cd, trans_type, batch_no, trace_no, submit_time, iss_resp_cd, acq_resp_cd, trans_st, trans_mode, msg_type, req_trans_mti, trans_mti, pri_acct_no, trans_proc_cd, trans_amount, local_time, local_date, card_expire_dt, settle_date, pos_entry_md_cd, card_seq, pos_cond_mode, pos_pin_capture_cd, acpt_ins_id_cd, retri_ref_no, auth_id_resp_cd, pos_mchnt_no, pos_term_no, app_mchnt_no, app_term_no, app_id, app_name, addn_resp_dt, addn_pt_dt, trans_curr_cd, pin_cd, sec_ctrl_inf, addn_at, msg_fld55_req, msg_fld55_rsp, pboc_elec_dt, detail_inq, field60, orig_msg, field62, field63, req_trans_resv, rsp_trans_resv, up_load, rvsl_rsn_cd, rvsl_times, script_result, app_crypt_type, app_type, aid, operator_id, tbl_no, rec_src_no, trade_attri, internal_err_cd, rec_upd_ts, rec_crt_ts) VALUES (CONCAT('12345678900010212345601CPA007',i), '', '123456789000102', '12345601', CONCAT('CPA007',i), 'CPA007', '000235', '009537', '20170930100852', '00', '', '', '', '22', '0200', '0210', '6212141000000000013', '000000', '000000000100', '100744', '0930', '', '0929', '022', '', '00', '', '00092900', '100744315107', '', '123456789000102', '12345601', '', '', '', '', '92010000   00092900   ', '', '156', '', '0600000000000000', '', '', '', '', '', '22000235', '', '', 'CUP', '', '', '1', '', '', '', '', '', '', '01', '1', '0', '03', '', '2017-09-30 09:53:32', '2017-09-30 09:53:31');
        SET i = i + 1;
    END WHILE;
END;
/
(2)调用
CALL genData();
(3)删除
DROP PROCEDURE genData;
(4)查看
SHOW PROCEDURE status;