###Mysql常用命令

note:粗体字部分是用户需要根据自己的实际情况替换的部分，斜体部分为可选内容，#开头的命令是在终端中执行，>开头的命令是在mysql中执行

#####1. 登陆mysql

• # mysql -u username -p （回车后输入密码）
• # msyql -uroot -p123（root为用户名，123为密码 -u和root中间，-p和123中间没有空格）
• 远程连接mysql
  用法：# mysql -uusername -ppassword -h host -P 端口号 -D databases
  示例：# mysql -uroot -p123456 -h 192.168.1.91 -P 3306 -D test

#####2. 执行sql文件

• > source /path/source.sql;
• # mysql -uroot -p123 < source.sql

#####3.1 数据库备份

假设要备份temp这个数据库：

• # mysqldump -uroot -p123 temp > dump.sql

假设要备份temp这个数据库中的表table1和table2：

• # mysqldump -uroot -p123 temp table1 table2 > dump.sql

利用gzip压缩数据：

• # mysqldump -uroot -p123 temp table1 table2 | gzip > dump.sql.gz

备份多个数据库：

• # mysqldump -uroot -p123 --databases db1 [db2 db3..] > dump.sql

增加where条件:

• # mysqldump -uroot -p123 temp table --where="" > dump.sql

当不能使用mysqldump时

• # mysql -uroot -p123 -Ddatabase -e "select concat('insert into table values (',id,')'"

#####3.2 数据库恢复

• # mysql -uroot -p123 temp < dump.sql
• # 登录到mysql中执行命令 source < dump.sql

从压缩文件直接恢复：

• # gunzip < dump.sql.gz | mysql -uroot -p123 temp

#####4. 表结构修改

• ######4.1.1 增加一个新列
  用法：> alter table tablename add 列名 类型 约束条件;
  示例：> alter table user add name varchar(32) not null default 'unknow';
• ######4.1.2 增加一个新列并指定位置
  用法：> alter table tablename add 列名 类型 约束条件 after 某列;
  示例：> alter table user add name varchar(32) not null default 'unknow' after id;
• ######4.2 删除列
  用法：> alter table tablename drop column 列名;
  示例：> alter table user drop column name;
• ######4.3 重命名列
  用法：> alter table tablename change 原列名 新列名 原类型 约束条件;
  示例：> alter table user change name nickname varchar(32) not null default 'unknow';
• ######4.4 改变列的类型
  用法：> alter table tablename change 列名 列名 新类型 约束条件;
  示例：> alter table user change name name tinyint not null default '0';
  用法：> alter table tablename alter column 列名 新类型;
  示例：> alter table user alter column name tinyint;
• ######4.5 重命名表
  用法：> alter table tablename1 rename tablename2;
  示例：> alter table user rename new_user;
• ######4.6 加索引
  用法：> alter table tablename add index 索引名(字段名1[，字段名2 …]);
  示例：> alter table user add index name_age(name,age);
• ######4.7 加主关键字的索引
  用法：> alter table tablename add primary key(id);
  示例：> alter table user add primary key(id);
• ######4.8 加唯一限制条件的索引
  用法：> alter table tablename add unique 索引名(字段名);
  示例：> alter table user add unique index_name(name);
• ######4.9 删除某个索引
  用法：> alter table tablename drop index 索引名;
  示例：> alter table user drop index_name;

#####5. 查看表的索引信息

• > show index from tablename;

#####6. sql性能分析

• sql使用索引情况，扫描的行数
  > explain select * from user;

#####7. 数据库正在执行的sql

> show processlist;

#####8. 随机数

mysql机函数rand(),会生成大于等于0,小于1(0到1)之间的小数，floor函数都可以把小数变为整数整数

• 0到99的随机数
  > select floor(rand()*100);
• 1到99的随机数
  > select floor(1+rand()*99);

#####9. 创建用户

创建用户前需确保没有用户名为""的用户，如果有需要先删除 > select user,password from mysql.user; > delete from mysql.user where user = ''; > create User 用户命 IDENTIFIED BY '密码'; > GRANT ALL ON 表名.* TO 用户命; > flush privileges; (授权后还要刷新系统权限表) 给用户赋予权限 用法：> GRANT 操作 ON 库名.表名 TO 用户名; （库名表名可以用*匹配） 示例：> GRANT SELECT, INSERT,UPDATE ON temp.user TO newuser;

#####10. 清空表中数据

用法：# mysqldump -u用户名 -p密码 --add-drop-table --no-data 数据库 | mysql -u用户名 -p密码 数据库 示例：# mysqldump -uroot -p123 --add-drop-table --no-data temp | mysql -uroot -p123 temp 示例：# mysqldump -uroot -p123 --add-drop-table --no-data temp | grep user | mysql -uroot -p123 temp 原理说明：根据mysqldump的备份参数，包括drop table和建表create table语句，其实是先把database里的所有表drop后，再重建表结构。

#####11. 批量删除指定表

用法：# mysqldump -u用户名 -p密码 --add-drop-table --no-data 数据库 | grep ^DROP | grep 条件 | mysql -u用户名 -p密码 数据库 示例：# mysqldump -uroot -p123 --add-drop-table --no-data temp | grep ^DROP | grep user | mysql -uroot -p123 temp

#####12. 启动mysql

• # service mysqld start
• # /etc/inint.d/mysqld start

#####13. 关闭mysql

• # service mysqld start
• # /etc/inint.d/mysqld start

#####14. 重启mysql

• # service mysqld restart
• # /etc/inint.d/mysqld restart

#####15. 设置mysql终端编码

• 在终端中使用msyql中文经常会乱码,这个时候可以set names utf8;

#####16. 禁止mysql缓存结果

• select SQL_NO_CACHE count(*) from tablename; 需要注意这个是禁止查询的结果放入缓存中,而不是禁止从缓存中查询

#####17. 查询n次的耗时

• select benchmark(n, (select COUNT(*) FROM tablename)); 测试表明测试的语句不会被缓存
  benchmark会重复计算表达式n次,benchmark函数只能测量数字表达式的性能，虽然说表达式可以是一个子查询，但子查询返回的只能是单个值

#####18. 变量

• 测试表明使用变量,不会做任何缓存,对比使用SQL_NO_CACHE,SQL_NO_CACHE会缓存一些sql分析,执行计划
  > set := 0;
  > select COUNT(*) FROM tablename where id > ;

#####19. 查询行数比较多的表

> select table_name,table_rows from information_schema.tables order by table_rows desc limit 10;

#####20. 不产生binlog的删除数据方式

> truncate tablename;

#####21. 秒数和日期转换

> FROM_UNIXTIME(1466780897) > UNIX_TIMESTAMP('2016-05-25 00:00:00')

#####22. 查出的列拼成一行

> select group_concat(id) from tablename; > 默认group_concat长度限制1024可以通过 SET SESSION group_concat_max_len=102400;设置

#####23. 查看binlog格式

> show variables like 'binlog_format';