mysql笔记

1、修改表的编码格式：
alter table table_name default character set utf8;
此方法虽然修改了表的编码格式，但是字段的编码格式并没有修改过来。

2、修改字段的编码格式：
alter table table_name change 字段名1 varchar(36) character set utf8 not null;
但是一次只能修改一个字段，还是很麻烦，不方便。

3、修改一张表所有字段编码格式：
alter table table_name convert to character set utf8;
它可以修改一张表的所有字段的编码格式，顿时方便多了。

mysql> show processlist;
根据上面查出的session Id 杀死该session,kill 10644;

mysqldump -h127.0.0.1 -uroot -p test  > test.dump_`date '+%Y%m%d%H%M%S'`.sql 导出test数据库结构和数据

mysqldump -h127.0.0.1 -uroot -p wise_base_channel channel_category channel_category_copy100069 channel_category_itn channel_category_itn_copy channel_store_goods channel_store_goods_copy100069 channel_store_goods_itn ?channel_store_goods_itn_copy ?store_goods_channel_category store_goods_channel_category_copy100069 store_goods_channel_category_itn store_goods_channel_category_itn_copy > wise_base_channel.dump_`date '+%Y%m%d%H%M%S'`.sql 
执行完得到wise_base_channel.dump_20181214165449.sql wise_base_channel为库名后面的为表名 包含结构和数据

mysqldump --no-data -h127.0.0.1 -uroot -p wise_base_channel channel_category channel_category_copy100069 channel_category_itn channel_category_itn_copy channel_store_goods channel_store_goods_copy100069 channel_store_goods_itn ?channel_store_goods_itn_copy ?store_goods_channel_category store_goods_channel_category_copy100069 store_goods_channel_category_itn store_goods_channel_category_itn_copy > wise_base_channel.dump_`date '+%Y%m%d%H%M%S'`.sql
执行完得到wise_base_channel.dump_20181214165952.sql wise_base_channel为库名后面的为表名 只有结构

mysqldump -t -h127.0.0.1 -uroot -p wise_base_channel channel_category channel_category_copy100069 channel_category_itn channel_category_itn_copy channel_store_goods channel_store_goods_copy100069 channel_store_goods_itn  channel_store_goods_itn_copy  store_goods_channel_category store_goods_channel_category_copy100069 store_goods_channel_category_itn store_goods_channel_category_itn_copy > wise_base_channel.dump_`date '+%Y%m%d%H%M%S'`.sql
执行完得到wise_base_channel.dump_20181214171738.sql wise_base_channel为库名后面的为表名 只有数据没有表结构

 mysqldump -t -h127.0.0.1 -uroot -p wise_base_channel  > wise_base_channel.dump_`date '+%Y%m%d%H%M%S'`.sql
执行完得到wise_base_channel.dump_20181214172055.sql wise_base_channel为库名 只有数据没有表结构(所有的表数据)

mysqldump -t -c -h127.0.0.1 -uroot -p'password'  wise_base_acl_new resource --where='merchant_id!="1"' > resource.sql
mysqldump -t -c -h127.0.0.1 -uroot -p'password'  wise_base_acl_new role_resource --where='merchant_id!="1"' > role_resource.sql 

#导出csv格式
mysqldump -h127.0.0.1 -uroot -p'password' db_xs map -t -T /var/lib/mysql-files/   -w'is_usable=1'  --fields-enclosed-by=\" --fields-terminated-by=,
#导出csv格式2
vim test.sql
select store_code,',',name from map where is_usable=1;

mysql -h172.16.120.78 -uroot -p'root@123@CTL' -Ddb_xs --default-character-set=utf8 --skip-column-names --raw < test.sql  > data.csv

[root@BJ-HS-P-Map03 mysql-files]# cat data.csv
leroymerlin.183 ,       leroy merlin_183
leroymerlin.084 ,       leroy merlin_084
leroymerlin.195 ,       leroy merlin_195

mysql -uroot -pmpwd 
create database testmyisam;
use testmyisam;
create table t1(id int) engine=myisam;
insert into t1 values(1),(2);

备份 1,2
exit
rm backup/t* -rf
cp t* backup/ -a

插入3,4
mysql -uroot -pmpwd testmyisam
insert into t1 values(3),(4);
select * from t1;            
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

恢复备份前的数据1,2
exit
rm t* -rf
cp backup/t* .
chown mysql:mysql t*
ls
backup  db.opt  t.frm  t.MYD  t.MYI
repair table t1; //此操作处理用户恢复auto
mysql -uroot -pmpwd testmyisam
select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

删除
insert into t1 values(3);
truncate table t1;
drop table t1;

对已有的表更改
alter table t1 engine=myisam;