MySQL常用命令

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

从压缩文件直接恢复：

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

4. 表结构修改

4.1 增加一个新列
用法：> alter table tablename add 列名类型 约束条件;
示例：> alter table user add name varchar(32) not null default 'unknow';
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. 批量删除指定表

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 @id := 0;
> select COUNT(*) FROM tablename where id > @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;