Mysql常用命令

原创
2015/03/18 19:32
阅读数 193

###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 @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;
> 默认group_concat长度限制1024可以通过 SET SESSION group_concat_max_len=102400;设置

#####23. 查看binlog格式
> show variables like 'binlog_format';

#####24. 一行转多行
> select tablename.id, SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name from (select 1 n union all select 2 union all select 3 union all select 4 union all select 5) numbers INNER JOIN tablename on CHAR_LENGTH(tablename.name) -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1 order by id, n

展开阅读全文
打赏
1
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
1
分享
返回顶部
顶部