11. /usr/local/mysql/bin/mysql wang
12. /usr/local/mysql/bin/mysql -uMAIN -preport4jp -A MAIN --default-character-set=cp932 < JHF_CUSTOMER.sql
13. create index wang_tb_primary1 on wang_tb3(id);
14. show index from wang_tb3;
15. drop view JHF_ALL_CASHFLOW;
16. select * from wang_tb5 where id in(1,3);
17. /usr/local/mysql/bin/mysqldump --opt --where "id in(1,3)" wang wang_tb5 >wang_tb5.sql
18. for x in `cat bb`;do echo --$x-- && /usr/local/mysql/bin/mysqldump --opt -- where "id in (2,3)" wang $bb>mm.sql;done
--wang_tb5--
--wang_tb6--
19.mysql> kill query 235994;
连接不断掉，但返回值为空
kill 把连接断掉了
20. /usr/local/mysql/bin/mysql INFO -e "delete from JHF_SPOT_RATE where FEEDTIME < date_sub(NOW(),INTERVAL 60 MINUTE);"
表优化
21. for tb in `mysql -A INFO -e "show tables" | sed '1d'`;do echo "OPTIMIZE TABLE $tb..." && mysql -A INFO -e "OPTIMIZE TABLE $tb";done
22. /usr/local/mysql/bin/mysql -A wang -e "source aa.sql"
EXPLAIN语法（获取SEEXPLAIN语法（获取SELECT相关信息）LECT相关信息）
23. mysql> explain select * from wang_tb6;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | wang_tb6 | ALL | NULL | NULL | NULL | NULL | 4 | |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
24. 快速重建索引
通常情况下，如果你想改变服务器的全文搜索变量，你需要在表格中重新建立全文索引，以确保你的更新得到映射。这一操作将会花费大量的时间，特别是如果你需要处理很多数据的时候。一种快速的解决方法是使用REPAIR TABLE命令，以下为演示过程：
表B
mysql> REPAIR TABLE content QUICK;
+-----------+--------+----------+----------+
| Table| Op| Msg_type | Msg_text |
+-----------+--------+----------+----------+
| content| repair | status| OK|
+-----------+--------+----------+----------+
1 row in set (0.05 sec) 压缩一定的表格类型
25. /usr/local/mysql/bin/mysqldump --default-character-set=cp932 --opt MAIN JHF_CP_TRADING_BALANCE > JHF_CP_TRADING_BALANCE.sql
26. mysql加锁：
mysql> FLUSH TABLES WITH READ LOCK;
mysql解琐：
mysql> UNLOCK TABLES;
27. DROP TABLE IF EXISTS tb1_name
如果不能肯定一个表是否存在，但希望如果它存在就删除它。那么可在此语句中增加IF EXISTS。这样，如果DROP TABLE 语句中给出的表不存在，MySQL不会发出错误信息
======================
分割符用法
DELIMITER /
/
======================
BEGIN
END
======================
28. 创建表为innodb的存储引擎
CREATE TABLE `JHF_AFFILIATE_BIND_REG` (
`AFFILIATE_ID` VARCHAR(20) NOT NULL ,
`CUSTOMER_ID` VARCHAR(20) NOT NULL ,
`BATCHED_FLAG` DECIMAL(1,0) ,
`ACTIVE_FLAG` DECIMAL(1,0) ,
`FRONT_DATE` VARCHAR(8) ,
`INPUT_DATE` TIMESTAMP NULL DEFAULT NULL ,
`UPDATE_DATE` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY (`AFFILIATE_ID`,`CUSTOMER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=cp932;
29. 更改表的存储引擎
alter table wang_tb engine=myisam;
30. 查看表的状态(可以看到该表创建时所用的存储引擎)
show table status\G;
Name: JHF_AFFILIATE_BIND_REG
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2007-08-01 15:27:30
Update_time: NULL
Check_time: NULL
Collation: cp932_japanese_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 4096 kB
31. 查看创建语句
mysql> mysql> show create table wang_tb\G;
*************************** 1. row ***************************
Table: wang_tb
Create Table: CREATE TABLE `wang_tb` (
`id` int(11) default NULL,
`name` varchar(30) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=cp932
1 row in set (0.00 sec)
ERROR:
No query specified
32.mysql手动同步复制
[prmapp_ld4:root/616]#/usr/local/mysql/bin/mysql -A
mysql>slave stop;
mysql>CHANGE MASTER TO
>MASTER_HOST='192.168.33.100',
>MASTER_USER=' slave ',
>MASTER_PASSWORD=' slave ',
>MASTER_LOG_FILE=' mysql-bin.000137 ',
>MASTER_LOG_POS=1711;
mysql>slave start;
mysql>show slave status;
mysql 存储过程备份
/usr/local/mysql/bin/mysqldump -R -t -d MAIN >MAIN_store.sql
备份 trigger:
/usr/local/mysql/bin/mysqldump --triggers -d -t MAIN > /tmp/master.trigger
删除trigger
/usr/local/mysql/bin/mysql -A MAIN -e "DROP TRIGGER $x;"
指令:"show triggers"
导入trigger
/usr/local/mysql/bin/mysql -A MAIN < /tmp/master.trigger
mysqldump 文件中一行只有一个insert用法
mysqldump wangzm www --skip-extended-inser>www1.sql
