MYSQL常用命令大全
本文主要是记录mysql的一些常用命令
一、连接Mysql服务器
格式： mysql -h主机地址 -u用户名 －p用户密码
1. 连接到本地的MYSQL
mysql -u root -p
用户名前的空格可有可无，密码前必须有空格，mysql的提示符是：mysql>
2. 连接远程主机mysql
假设远程主机IP为：110.100.100.100，用户名为root,密码为R123456,端口号为33063(默认端口为3306)
mysql -h 110.100.100.100 -P 33063 -u root -p
输入密码：R123456
3. 退出mysql
exit 敲回车
二、修改密码
格式：mysqladmin -u用户名 -p 旧密码 password 新密码
1. 给root用户加密码a1234
mysqladmin -u root -password a1234
2. 将root用户的密码改为ab123456
mysqladmin -u root -p a1234 password ab123456
三、用户操作
1. 添加用户
格式：grant select on 数据库.* to 用户名@登录主机 identified by "密码";
1.1. 增加一个test用户，密码为Ab12345.,可在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。先用root用户连接mysql,然后敲命令：
grant select, insert, update, delete on *.* to 'test'@'%' identified by 'Ab12345.';
这个用户十分危险，如果密码泄漏了，就可以在internet上的任意电脑上登录数据库，任他为所欲为
1.2. 增加一个用户test1密码为Ab45678.，只可在localhost上登录，并对数据库testdb进行增删改查操作
grant select, insert, update, delete on testdb.* to 'test1'@'localhost' identified by 'Ab45678.';
2. 撤销用户权限
格式：revoke privilege on 数据库名.表名 from '用户名'@'主机名';
revoke select on testdb.* from 'test1'@'localhost';
3. 授予用户权限
grant select on testdb.* to 'test1'@'localhost';
更多关于权限操作，请参考《MYSQL授予用户权限》一文
4. 查看用户权限
show grants for 'test1'@'localhost';
5. 删除用户
drop user 'test'@'%';
注：授予/撤销权限后，使用如下命令刷新权限，使之生效：
flush privileges;
四、数据库操作
1. 显示数据库
show databases;
2. 选择数据库
use testdb;
3. 当前选择的数据库
select database();
4. 显示数据表
show tables;
5. 查看表结构
desc table_name;
6. 创建数据库并指定编码
create database if not exists testdb default charset utf8 collate utf8_general_ci;
7. 删除数据库
drop database testdb;
8. 显示mysql版本
mysql> select version();
9. 显示当前时间
select now();
10. 创建数据表
create table mytable(id int(11) not null primary key auto_increment,name char(20) not null sex int(1) not null default 0 comment '性别 1:男 2:女');
11. 删除数据表
drop table mytable;
12. 表插入数据
insert into mytable values('Tom',1),('Lucy',2);
13. 查询表中的数据
select * from mytable;
更多查询请参考《Mysql查询大全》
14. 删除表中的数据
delete from mytable where id=1;
15. 更新表中的数据
update mytable set sex=2 where id=2;
16. 增加字段
alter table mytable add email varchar(25) default "";
17. 加索引
格式：alter table 表名 add index 索引名(字段名1[,字段名2...]);
alter table mytable add index index_name(name)
加主关键字索引：
alter table mytable add primary key(id);
加唯一索引：
格式：alter table 表名 add unique 索引名(字段名);
alter table mytable add unique unique_index_name(email);
18. 删除索引
格式：alter table 表名 drop index 索引名;
alter table mytable drop index index_name;
19. 修改字段名称及类型
alter table mytable change old_field_name new_field_name field_type;
20. 删除字段
alter table mytable drop field_name;
21. 修改表名
格式：rename table 原表名 to 新表名;
rename table mytable to yourtable;
当你执行 RENAME 时，你不能有任何锁定的表或活动的事务。你同样也必须有对原初表的 ALTER 和 DROP 权限，以及对新表的 CREATE 和 INSERT 权限。
如果在多表更名中，MySQL 遭遇到任何错误，它将对所有被更名的表进行倒退更名，将每件事物退回到最初状态。
22. 查询mysql数据库占存储空间大小
进入information_schema数据库（存放了其他的数据库的信息）
use information_schema;
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables; --查询所有数据库
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home'; -- 查询指定数据库
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home' and table_name='users';--查询某个数据库中某个表的大小
五、备份数据库
使用mysqldump工具，直接在控制台上操作
1. 导出整个数据库
格式：mysqldump -u root -p 数据库名 > 路径/文件名.sql
mysqldump -u root -p testdb > /var/mysql/testdb.sql
2. 导出一个表
格式：mysqldump -u root -p 数据库名 表名 > 路径/文件名.sql
mysqldump -u root -p testdb mytable > /var/mysql/testdb_mytable.sql
3. 导出数据库结构
mysqldump -u root -p -d -add-drop-table testdb > /var/mysql/testdb.sql
-d 没有数据 -add-drop-table 在每个create语句之前增加一个drop table
六、还原数据
常用 source 命令,没有数据库则先创建数据库
进入mysql数据库控制台，如：
mysql -u root -p
use testdb;
source /var/mysql/testdb.sql;