MySql常用操作命令总结

连接数据库

命令： mysql -h主机地址 -P端口号 -u用户名 -p密码

本地： 打开DOS，进入MySql\bin，键入命令：mysql -uroot -p，回车。输入密码后键入Enter即可。推荐将Mysql\bin目录加入到系统环境变量，这样打开cmd后可直接输入mysql命令。

远程：mysql -h1.1.1.1 -P3306 -uroot -p123

退出数据库： exit

修改用户密码

命令： mysqladmin -u用户名 -p旧密码 -password新密码

给root添加密码： mysqladmin -uroot -password 123

修改root密码： mysqladmin -uroot -p123 -password456

新增用户

命令： grant 权限 on 数据库.表 to 用户名@主机 identified by '密码'

新增用户user1，赋予增删改查权限，可操作所有数据库，可在internet上任何电脑登录数据库进行操作：

grant select,insert,update,delete on *.* to user1@1.1.1.1 indentified by '123'

新增用户user2，只有查询权限，只能操作db数据库的user表，只能在数据库所在服务器登录该数据库进行操作：

grant select on db.user to user2@localhost indentified by '123'

解除user1的密码：

grant select,insert,update,delete on *.* to user1@1.1.1.1 indentified by ''

添加用户访问权限

grant 权限 on 数据库对象 to 用户@'localhost' identified by ''
权限：select,insert,update,delete,create,create view,alter,drop,lock tables,index,execute
数据库对象：
*.* 所有数据库和表
db.* 某个数据库
db.table 数据库中的某个表
grant select(a,b,c) on *.* 具体字段
grant execute on procedure *.* 存储过程
grant execute on function *.* 函数

flush privileges 刷新服务

查看当前用户（自己）权限：

show grants;

查看其他 MySQL 用户权限：

show grants for user@localhost;

操作 MySQL 外键权限：

grant references on db.table to user@'localhost'

操作 MySQL 临时表权限：

grant create temporary tables on db.table to user@'localhost'

操作 MySQL 索引权限：

grant index on db.table to user@'localhost'

操作 MySQL 视图、查看视图源代码 权限：

grant create view on db.table to user@'localhost'
grant show view on db.table to user@'localhost'

操作 MySQL 存储过程、函数 权限：

grant create routine on db.table to user@'localhost'; — now, can show procedure status
grant alter routine on db.table to user@'localhost'; — now, you can drop a procedure
grant execute on db.table to user@'localhost'

撤销已经赋予给 MySQL 用户权限的权限。
revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可：

grant all on *.* to dba@localhost;
revoke all on *.* from dba@localhost;

MySQL grant、revoke 用户权限注意事项：
1、 grant, revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。

2、如果想让授权的用户，也可以将这些权限 grant 给其他用户，需要选项 grant option

grant select on db.table to dba@localhost with grant option;

这个特性一般用不到。实际中，数据库权限最好由 DBA 来统一管理。

mysql授权表共有5个表：user、db、host、tables_priv和columns_priv。

重点内容授权表的内容有如下用途：

1. user表
   user表列出可以连接服务器的用户及其口令，并且它指定他们有哪种全局（超级用户）权限。在user表启用的任何权限均是全局权限，并适用于所有数据库。例如，如果你启用了DELETE权限，在这里列出的用户可以从任何表中删除记录，所以在你这样做之前要认真考虑。

2. db表
   db表列出数据库，而用户有权限访问它们。在这里指定的权限适用于一个数据库中的所有表。

3. host表
   host表与db表结合使用在一个较好层次上控制特定主机对数据库的访问权限，这可能比单独使用db好些。这个表不受GRANT和REVOKE语句的影响，所以，你可能发觉你根本不是用它。
4. tables_priv表
   tables_priv表指定表级权限，在这里指定的一个权限适用于一个表的所有列。
5. columns_priv表
   columns_priv表指定列级权限。这里指定的权限适用于一个表的特定列。

注：对于GRANT USAGE ON，查看手册有如下介绍和实例：

mysql> GRANT USAGE ON *.* TO user@'localhost';

一个账户有用户名user，没有密码。该账户只用于从本机连接。未授予权限。通过GRANT语句中的USAGE权限，你可以创建账户而不授予任何权限。它可以将所有全局权限设为’N’。假定你将在以后将具体权限授予该账户。

创建/显示/使用/删除 数据库

创建： create database 数据库名

显示： show databases;

使用： use 数据库名;

删除： drop database 数据库名;

创建/显示/重命名/删除 表

创建：

create table 表名(

id int unsigned primary key auto_increment,

name varchar(30) not null,

age int(4) not null default 0

)engine = ismyisam default charset = utf8;

显示： desc 表名;

重命名： rename table 原名 to 新名;

删除： drop table 表名;

表的增/删/改/查操作：

增： insert into 表名(字段1, 字段2) values(字段1对应的值，字段2对应的值);

删： delete from 表名 where 表达式;

改： update 表明 set 字段1 = 值, 字段2 = 值 where 表达式;

多表修改： update a,b set a.num = b.num +1 where a.id = b.uid;

查： select 字段1, 字段2 from 表名 where 表达式 order by 字段名 DESC limit a,b;

多表联合查询： select * from a,b where a.id = b.uid;

增加/修改/删除 字段/索引

加索引： alter table 表名 add index 索引名(字段名);

加关键字： alter table 表名 add primary key(字段名);

加唯一限制条件的索引： alter table 表名 add unique 索引名(字段名);

删除索引： alter table 表名 drop index 索引名;

增加字段： alter table 表名 add 字段名 字段类型 [after 字段名];

修改字段名及类型： alter table 表名 change 旧字段名 新字段名 字段类型 [after 字段名];

只改字段类型，不改名： alter table 表名 modify 字段名 字段类型;

删除字段： alter table 表名 drop 字段名;

设置/删除字段默认值： alter table 表名 alter column 字段名 set/drop default 默认值;

备份数据库

注意： mysqldump不是mysql命令，win下到mysql\bin下执行，linux直接运行mysqldump即可。

导出数据库所有表： mysqldump -u用户名 -p密码 数据库名 > 导出文件名.sql

只导库结构，无数据： mysqldump -u用户名 -p密码 -d 数据库名 > 导出文件名.sql

只导库数据，无结构： mysqldump -u用户名 -p密码 -t 数据库名 > 导出文件名.sql

导出特定表： mysqldump -u用户名 -p密码 数据库名 表名 > 导出文件名.sql

只导表结构，无数据： mysqldump -u用户名 -p密码 -d 数据库名 表名 > 导出文件名.sql

只导表数据，无结构： mysqldump -u用户名 -p密码 -t 数据库名 表名 > 导出文件名.sql

导入数据： 因为导出的只是表和数据，所以要先建好数据库，然后进入mysql控制台，用source命令导入sql文件。

use 数据库名
source d:\wamp\bin\back.sql;

优化相关

修复表： repair table 表名

优化表（回收删除数据后数据库占用的闲置磁盘空间）：optimize table 表名

清理日志： reset master;

查看连接数 show processlist;

输出结果到文件

输出到当前机器：

select * from user into outfile “/home/ssyleo/user.txt”

输出到客户端机器：

mysql -h10.1.2.3 -uroot -p123 -P3306 dbname -e “select * from user” > /home/ssyleo/user.txt

使用muysql的Tee命令，将mysql的所有输出保存到指定文件

mysql>tee /home/ssyleo/user.txt
mysql>select * from user;
mysql>exit