首页 > 代码库 > MySQL常用命令

MySQL常用命令

2024-10-17 14:39:39   215人阅读

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. 批量删除指定表

用法：# 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;

MySQL常用命令


联系
我们