mysql常用命令梳理

最新推荐文章于 2024-11-02 21:45:35 发布

qq632544991p

最新推荐文章于 2024-11-02 21:45:35 发布

阅读量776

点赞数 1

分类专栏：数据库 mysql学习笔记文章标签： mysql

数据库同时被 2 个专栏收录

15 篇文章 0 订阅

订阅专栏

mysql学习笔记

12 篇文章 0 订阅

订阅专栏

mysql常用命令梳理

索引

1、创建索引

索引的创建可以在CREATE TABLE语句中进行，也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。以下命令语句分别展示了如何创建主键索引（PRIMARY KEY），联合索引（UNIQUE）和普通索引（INDEX）的方法。

mysql>ALTER TABLE 表名 ADD INDEX 索引名 列名;
mysql>ALTER TABLE 表名 ADD UNIQUE 索引名 列名;
mysql>ALTER TABLE 表名 ADD PRIMARY KEY 索引名 列名;
mysql>CREATE INDEX 索引名 ON 表名 列名;
mysql>CREATE UNIQUE INDEX 索引名 ON 表名 列名;

例如：

mysql>ALTER TABLE `article` ADD INDEX `id`; //给article表增加id索引

或者：

mysql>ALTER TABLE article ADD INDEX (id,order_id); //给article表增加id索引，order_id索引

2、重建索引

重建索引在常规的数据库维护操作中经常使用。在数据库运行了较长时间后，索引都有损坏的可能，这时就需要重建。对数据重建索引可以起到提高检索效率。

mysql> REPAIR TABLE 表名 QUICK;

3、查询数据表索引

MySQL查询表索引命令的有两种命令形式：

mysql> SHOW INDEX FROM 表名;

或者：

mysql> SHOW keys FROM 表名;

比如：

mysql> SHOW INDEX FROM uc_member;
+-----------+------------+---------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name            | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+---------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| uc_member |          0 | PRIMARY             |            1 | member_id       | A         |        1099 |     NULL | NULL   |      | BTREE      |         |
| uc_member |          1 | idx_nickname_passwd |            1 | member_nickname | A         |         549 |     NULL | NULL   |      | BTREE      |         |
| uc_member |          1 | idx_nickname_passwd |            2 | member_password | A         |        1099 |     NULL | NULL   |      | BTREE      |         |
| uc_member |          1 | member_mobile       |            1 | member_mobile   | A         |        1099 |     NULL | NULL   |      | BTREE      |         |
+-----------+------------+---------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)

解释：
Non_unique 如果索引不能包括重复词，则为0。如果可以，则为1。
Key_name 索引的名称。
Seq_in_index 索引中的列序列号，从1开始。
Column_name 列名称。
Collation 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
Cardinality 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。
Null 如果列含有NULL，则含有YES。如果没有，则该列含有NO。
Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。

4、删除索引

删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。
DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：

mysql>DROP index 索引名 ON 表名 列名;
mysql>ALTER TABLE 表名 DROP INDEX 索引名 列名;
mysql>ALTER TABLE 表名 DROP UNIQUE 索引名 列名;
mysql>ALTER TABLE 表名 DROP PRIMARY KEY 索引名 列名;

在上面前三条语句中，都删除了table_name中的索引index_name。
而在最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此也可不指定索引名。
如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。
如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。
如果删除组成索引的所有列，则整个索引将被删除。

—————————————索引、主键、唯一索引、联合索引梳理——————————————–
说下Mysql索引概念：
索引就好比一本书的目录，它会让你更快的找到内容，显然目录（索引）并不是越多越好，假如这本书1000页，有500也是目录，它当然效率低，目录是要占纸张的,而索引是要占磁盘空间的。

Mysql索引主要有两种结构：hash和B+树：
hash:hsah索引在mysql比较少用,他以把数据的索引以hash形式组织起来,因此当查找某一条记录的时候,速度非常快.当时因为是hash结构,每个键只对应一个值,而且是散列的方式分布.所以他并不支持范围查找和排序等功能.
B+树:b+tree是mysql使用最频繁的一个索引数据结构,数据结构以平衡树的形式来组织,因为是树型结构,所以更适合用来处理排序,范围查找等功能.相对hash索引,B+树在查找单条记录的速度虽然比不上hash索引,但是因为更适合排序等操作,所以他更受用户的欢迎.毕竟不可能只对数据库进行单条记录的操作.

Mysql常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引

PRIMARY KEY(主键索引)：    ALTER TABLE 表名 ADD PRIMARY KEY 列名
UNIQUE(唯一索引)：     ALTER TABLE 表名 ADD UNIQUE 列名
INDEX(普通索引)：    ALTER TABLE 表名 ADD INDEX 索引名 列名
FULLTEXT(全文索引):  ALTER TABLE 表名 ADD FULLTEXT 列名
组合索引：ALTER TABLE 表名 ADD INDEX 索引名 (列名1,列名2, 列名3)

Mysql各种索引区别：
普通索引：最基本的索引，没有任何限制
唯一索引：与”普通索引”类似，不同的就是：==索引列的值必须唯一，但允许有空值==。
主键索引：它是一种特殊的唯一索引，==不允许有空值==。
全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
组合索引：为了更多的提高mysql效率可建立组合索引，遵循”==最左前缀==“原则。

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
普通索引(==由关键字KEY或INDEX定义的索引==)的唯一任务是加快对数据的访问速度。

普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。

主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字 PRIMARY KEY 来创建。
索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引，这就是联合索引。

主键分为复合主键和联合主键
复合主键就是指你表的主键含有一个以上的字段组成。
例如：

create table test
(
name varchar(19),
id number,
value varchar(10),
primary key (id,name)
)

上面的==id和name字段组合起来就是你test表的复合主键== （若其一为单索引字段时，左边的id才会有索引）
它的出现是因为你的name字段可能会出现重名，所以要加上ID字段这样就可以保证你记录的唯一性
一般情况下，主键的字段长度和字段数目要越少越好。

联合主键，顾名思义就是多个主键联合形成一个主键组合，体现在联合。
(主键原则上是唯一的，别被唯一值所困扰。)
索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

举一个简单的例子
主键A跟主键B组成联合主键
主键A跟主键B的数据可以完全相同(困扰吧，没关系)，联合就在于主键A跟主键B形成的联合主键是唯一的。
下例主键A数据是1，主键B数据也是1，联合主键其实是11，这个11是唯一值，绝对不充许再出现11这个唯一值。(这就是多对多关系)
主键A数据主键B数据
1　　　　　　1
2　　　　　　2
3　　　　　　3
主键A与主键B的联合主键值最多也就是
11
12
13
21
22
23
31
32
33

mysql操作命令梳理（2）-alter（update、insert）

在mysql运维操作中会经常使用到alter这个修改表的命令，alter tables允许修改一个现有表的结构，比如增加或删除列、创造或消去索引、改变现有列的类型、或重新命名列或表本身，也能改变表的注释和表的类型。

下面就针对alter修改命令的使用做一梳理：

2）删除列

==删除列alter table 表名 DROP 列名==; //或者 alter table 表名 drop column 列名;

drop database 库名;      //删除库
drop table 表名;           //删除表
delete from 表名;         //清空表中所有数据，但这张表没有删除，保留的是空表。
delete from 表名 where ...;          //删除表中字段

2）增加列

alter table 表名 ADD 列名列的属性(如INT NOT NULL COMMENT ‘注释说明’)

3）修改列的类型信息。alter table 表名 CHANGE 列名新列名新列属性;

alter table 表名 CHANGE 列名新列名(这里可以用和原来列同名即可) BIGINT NOT NULL COMMENT ‘注释说明’

4）重命名列

alter table 表名 CHANGE 列名新列名 BIGINT NOT NULL COMMENT ‘注释说明’

5）重命名表

alter table 表名 RENAME 表新名

6）删除表中主键

alter table 表名 drop primary key

-------------------------------------------------------------------------------------
如若删除带自增属性的主键，那么要先删除自增长，再删除主键
Alter table haha change id id int(10);        //删除自增长
Alter table haha drop primary key;           //删除主建
-------------------------------------------------------------------------------------

7）添加主键

alter table 表名 ADD CONSTRAINT PK_表名 PRIMARY KEY(列名1,列名2)
-------------------------------------------------------------------------------------
Alter table haha add primary key(id);               //将haha表的id列添加主键
Alter table haha change id id int(10) not null auto_increment;          //自增长属性
-------------------------------------------------------------------------------------
sql中constraint前缀的用意（PK、UK、DF、CK、FK）
--主键
constraint PK_字段 primary key(字段),
--唯一约束
constraint UK_字段 unique key(字段),
--默认约束
constrint DF_字段 default('默认值') for 字段,
--检查约束
constraint CK_字段 check(约束。如：len(字段)>1),
--主外键关系
constraint FK_主表_从表 foreign(外键字段) references 主表(主表主键字段)
-------------------------------------------------------------------------------------

8）添加/创建索引

alter table 表名 add index 索引名 列名;         //普通索引
ALTER TABLE 表名 ADD UNIQUE index 索引名 (列名1，列名2);         //联合索引
ALTER TABLE 表名 ADD PRIMARY index KEY 索引名 列名;         //主键索引
-------------------------------------------------------------------------------------
SHOW INDEX FROM 表名字;       //查询索引

删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理
DROP index 索引名 ON 表名字 列名;       //删除普通索引
ALTER TABLE 表名 DROP INDEX 索引名 列名;       //删除普通索引
ALTER TABLE 表名 DROP UNIQUE 索引名 (列名1,列名2);     //删除联合索引
ALTER TABLE 表名 DROP PRIMARY KEY 索引名 列名;      //删除主键索引
-------------------------------------------------------------------------------------

9）添加唯一限制条件索引

alter table 表名 add unique emp_name2(cardnumber); //empname 类型为String 字符串类型

10）创建联合唯一索引

alter table 表名 ADD UNIQUE INDEX 索引名 (列名1，列名2);
alter ignore table 表名 add unique index(user_id,user_name); //它会删除重复的记录（别怕，会保留一条），然后建立唯一索引，高效而且人性化.(慎用)

11）修改字段属性

alter table 表名 modify column 字段名 类型
alter table 表名 modify column 字段1 类型，字段2 类型
例如：将class表的name列属性改成varchar(100)
alter table class modify column name varchar(100);
或者：
alter table 表名 change 列名 列名 varchar(100);
alter table class change name name varchar(100);

上面提到了使用alter命令修改表名，修改列名；那么修改字段值，就需要使用update命令，如下：

mysql> select * from huanqiu.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | wangshibo |
| 11 | ouou      |
| 19 | hahahahha |
+----+-----------+
3 rows in set (0.00 sec)

mysql> update huanqiu.haha set name="wangshikui" where id=11;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from huanqiu.haha;
+----+------------+
| id | name       |
+----+------------+
|  1 | wangshibo  |
| 11 | wangshikui |
| 19 | hahahahha  |
+----+------------+
3 rows in set (0.01 sec)

修改root登陆密码：

mysql> update mysql.user set password=password(“654321”) where host=’192.168.1.101’ and user=”root”;

update 表名 set 列名=”新的字段值” [where 限制条件1 and 限制条件1]；
update 表名 set 列名1=”新的字段值”,列名2=”新的字段值” [where 限制条件1 and 限制条件1] [ORDER BY …] [LIMIT row_count]；

insert into 表名 values(所有字段的插入值);
insert into 表名(指定字段) values(指定字段);
insert into 表名 values(所有字段的插入值),(所有字段的插入值),(所有字段的插入值); //批量插入

mysql> select * from huanqiu.haha;
+----+------------+
| id | name       |
+----+------------+
| 11 | wangshikui |
+----+------------+
1 row in set (0.00 sec)

mysql> insert into huanqiu.haha values(1,"wangshibo"),(2,"wangshikui"),(3,"wangjuan"),(4,"wangman");
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from huanqiu.haha;
+----+------------+
| id | name       |
+----+------------+
|  1 | wangshibo  |
|  2 | wangshikui |
|  3 | wangjuan   |
|  4 | wangman    |
| 11 | wangshikui |
+----+------------+

创建表时指定AUTO_INCREMENT自增值的初始值:

mysql> CREATE TABLE 表名 (ID INT(5) PRIMARY KEY AUTO_INCREMENT)AUTO_INCREMENT=100;

通过ALTER TABLE 修改初始值(但要大于表中的AUTO_INCREMENT自增值，否则设置无效):

mysql>ATLER TABLE 表名 AUTO_INCREMENT=100;

如果自增序列的最大值被删除了，则在插入新记录时，该值被重用。
就是说如果表中原本有AUTO_INCREMENT属性值连续为78、100的值，但100这个数据被删除了，下此再添加数据的时候自增值为101,100被重用了。

即使在你将整个表中的所有数据delete清空后，之前的自增序列最大值还是会被重用。
解决办法是使用下面命令：

ATLER TABLE 表名 AUTO_INCREMENT=0; //重新设置自增初始值

设置AUTO_INCREMENT_INCREMENT以及AUTO_INCREMENT_offset用户变量值(重启MySQL之后，这些修改会恢复为初始值1)：

mysql>SET auto_increment_increment=10;     //自增量每次增加的值改为10，
mysql>SET auto_increment_offset=2;        //第一次加载数值时的偏移值的个位值
mysql>SHOW VARIABLES LIKE 'AUTO_INC%';      //查看修改后变量的值

mysql>SHOW TABLE STATUS FROM NAME_DB;      //显示数据库NAME_DB中所有表的信息

mysql>SHOW CREATE TABLE NAME_TBL;       //显示表NAME_TBL创建时的信息

mysql>SHOW VARIABLES LIKE 'AUTO_INC%';      //显示MySQL的AUTO_INC开头的用户会话变量(SHOW GLOBAL VARIABLES)
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)

在mysql日常操作中，妙用pager设置显示方式，可以大大提高工作效率。比如select出来的结果集超过几个屏幕，那么前面的结果一晃而过无法看到，这时候使用pager可以设置调用os的more或者less等显示查询结果，和在os中使用more或者less查看大文件的效果一样。

pager用法：
实际上等于将它设置以后的所有mysql操作命令的输出通过pager设置命令执行，类似于管道符的作用.

==nopager命令==：取消pager设置，恢复之前的输出状态。（如果不设置nopager，那么只能通过重启mysql服务才能恢复了）

举些例子来说明吧：
1）当处理大量数据时，不想显示查询的结果，而只需知道查询花费的时间。

mysql> select * from huanqiu.haha;
+----+------------+
| id | name       |
+----+------------+
|  1 | wangshibo  |
|  2 | wangshikui |
|  3 | wangjuan   |
|  4 | wangman    |
| 11 | wangshikui |
+----+------------+
5 rows in set (0.00 sec)
mysql> pager cat /dev/null;        //实际上等于后面执行的命令|cat /dev/null，这样显示结果就只是执行时间了
PAGER set to 'cat /dev/null'
mysql> select * from huanqiu.haha; 
5 rows in set (0.00 sec)<br>
mysql> nopager;                   //恢复之前的输出状态
PAGER set to stdout<br>
mysql> select * from huanqiu.haha;
+----+------------+
| id | name       |
+----+------------+
|  1 | wangshibo  |
|  2 | wangshikui |
|  3 | wangjuan   |
|  4 | wangman    |
| 11 | wangshikui |
+----+------------+
5 rows in set (0.00 sec)

2）如果有大量连接，用show processlist看不方便，想看有多少Sleep状态，则可以用pager。

mysql> show processlist;
+------+-------+---------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id   | User  | Host                | db     | Command     | Time | State                                                                 | Info             |
+------+-------+---------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+
|    5 | root  | localhost           | huanpc | Query       |    0 | init                                                                  | show processlist |
| 1801 | slave | 192.168.1.102:37125 | NULL   | Binlog Dump | 9904 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
+------+-------+---------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

mysql> pager grep Sleep |wc -l;    
PAGER set to 'grep Sleep |wc -l'
mysql> show processlist;           //类似于show processlist结果再通过grep Sleep |wc -l显示；下面表示一共有2个连接，其中0个Sleep状态的连接。
0
2 rows in set (0.00 sec)
mysql> nopager;                    //恢复之前的输出状态

3）设置pager，只查看slave状态的几个status值。

mysql> show slave status \G;              //其中的\G表示显示要换行显示
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.101
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 5370489
               Relay_Log_File: mysql-relay-bin.000005
                Relay_Log_Pos: 2476520
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: huanqiu,huanpc
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 5370489
              Relay_Log_Space: 2476693
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 101
                  Master_UUID: b667a58f-d6e0-11e6-8c0a-fa163e2d66ac
             Master_Info_File: /data/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> pager cat | egrep -i 'system user|Exec_Master_Log_Pos|Seconds_Behind_Master|Read_Master_Log_Pos';
PAGER set to 'cat | egrep -i 'system user|Exec_Master_Log_Pos|Seconds_Behind_Master|Read_Master_Log_Pos''

mysql> show slave status \G;
          Read_Master_Log_Pos: 5370489
          Exec_Master_Log_Pos: 5370489
        Seconds_Behind_Master: 0
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> nopager;                 //恢复之前的显示状态
PAGER set to stdout

mysql操作命令梳理（4）-grant授权和revoke回收权限

在mysql维护工作中，做好权限管理是一个很重要的环节。
下面对mysql权限操作进行梳理：

mysql的权限命令是grant，权限撤销的命令时revoke；
grant授权格式：grant 权限列表 on 库.表 to 用户名@’ip’ identified by “密码”;
revoke回收权限格式：revoke 权限列表 on 库.表 from 用户名@’ip’;

下面通过一些例子说明:

1.grant授权

1）grant 普通数据用户，查询、插入、更新、删除数据库中所有表数据的权利。

mysql> grant all on *.* to wang@'192.168.1.150' identified by "password";           //all等同于all privilege，其中的privileges可以省略
mysql> grant all privileges on *.* to wang@'192.168.1.%' identified by "123456";    //192.168.1.%表示一个网段
mysql> grant insert,select,update,delete,drop,create,alter on huanqiu.* to wang@'%' identified by "123456";
mysql> flush privileges      //授权之后，不要忘记更新权限表

2.查看权限

1）查看当前用户下所有的权限

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

2）查看指定用户下的所有权限
USAGE是默认的初始状态，表示无任何权限！！
下面表示wang用户在192.168.1.0/24网段登陆本机mysql后，对huanqiu库下的所有表有insert,update,alter,delete,create,select的操作权限！

mysql> show grants for wang@'192.168.1.%';           //可以在select user,host,password from mysql.user执行结果中找对应的权限用户信息
+---------------------------------------------------------------------------------------------------------------+
| Grants for wang@192.168.1.%                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wang'@'192.168.1.%' IDENTIFIED BY PASSWORD '*678E2A46B8C71291A3915F92736C080819AD76DF' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `huanqiu`.* TO 'wang'@'192.168.1.%'                    |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

3.revoke撤销权限

revoke跟grant语法差不多，只需要把关键字 “to” 换成 “from” 即可，并且revoke语句中不需要跟密码设置。
注意：==revoke可以回收所有权限，也可以回收部分权限==。

mysql> revoke all on *.* from wang@'192.168.1.150';
mysql> revoke all privileges on *.* from wang@'192.168.1.%';
mysql> revoke insert,select,update,delete,drop，create，alter on huanqiu.* from wang@'%';
mysql> flush privileges

注意事项：
1）grant, revoke用户权限后，该用户只有重新连接MySQL数据库，权限才能生效。
2）如果想让授权的用户，也可以将这些权限grant给其他用户，那么授权时需添加选项 “grant option”！

如下设置后，那么这个wang用户连接mysql后也可以将这些权限授予其他用户。

mysql> grant insert,select,update,alter on huanqiu.* to wang@’%’ identified by “123456” with grant option;

-------------------------------------------------------------------------------------------------------------------
mysql授权表一共涉及到5个表，分别是user、db、host、tables_priv和columns_priv。
这5张表的内容和用途如下：
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表指定列级权限。这里指定的权限适用于一个表的特定列。
------------------------------------------------------------------------------------------------------------------

==看下面一个实例：==
给wang用户授权的权限太大了，现在要收回部分权限，只留给wang用户select和alter的权限。

mysql> show grants for wang@'192.168.1.%';
+---------------------------------------------------------------------------------------------------------------+
| Grants for wang@192.168.1.%                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wang'@'192.168.1.%' IDENTIFIED BY PASSWORD '*678E2A46B8C71291A3915F92736C080819AD76DF' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `huanqiu`.* TO 'wang'@'192.168.1.%'                    |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> revoke insert,update,delete,create on huanqiu.* from wang@'192.168.1.%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for wang@'192.168.1.%';
+---------------------------------------------------------------------------------------------------------------+
| Grants for wang@192.168.1.%                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wang'@'192.168.1.%' IDENTIFIED BY PASSWORD '*678E2A46B8C71291A3915F92736C080819AD76DF' |
| GRANT SELECT, ALTER ON `huanqiu`.* TO 'wang'@'192.168.1.%'                                                    |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

发现revoke回收权限操作后，只剩下select和alter权限了。
revoke回收部分权限，剩下的权限的密码保持不变。

这里特别注意下：
==如果给一个用户设置的权限过大，除了上面使用revoke回收部分权限外，还可以使用grant进行权限修改！
也就是说，grant不仅可以添加权限，也可以修改权限（实际上就是对同一’用户名’@’ip’设置权限，以覆盖之前的权限）；
grant修改后的权限将覆盖之前的权限！==
那么问题来了：授权后的密码是密文形式保存的，如果记不住之前授权时的密码，那么怎样保证覆盖后的权限跟之前的权限一致？

莫慌！
grant授权操作中其实不仅可以设置明文密码，也可以设置密文密码，如下：
1）grant 权限列表 on 库.表.* to 用户名@’ip’ identified by “明文密码”
2）grant 权限列表 on 库.表.* to 用户名@’ip’ identified by password “密文密码”.

也就是说：
在grant重置权限的时候可以用查看的密文密码当做新的密码，然后去覆盖之前的权限，这就保证了修改前后的密码一致！
如上的例子，采用grant的操作如下：

mysql> show grants for wang@'192.168.1.%';
+---------------------------------------------------------------------------------------------------------------+
| Grants for wang@192.168.1.%                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wang'@'192.168.1.%' IDENTIFIED BY PASSWORD '*678E2A46B8C71291A3915F92736C080819AD76DF' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `huanqiu`.* TO 'wang'@'192.168.1.%'                    |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> grant alter,select on huanqiu.* to wang@'192.168.1.%' identified by password '*678E2A46B8C71291A3915F92736C080819AD76DF';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for wang@'192.168.1.%';
+---------------------------------------------------------------------------------------------------------------+
| Grants for wang@192.168.1.%                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wang'@'192.168.1.%' IDENTIFIED BY PASSWORD '*678E2A46B8C71291A3915F92736C080819AD76DF' |
| GRANT SELECT, ALTER ON `huanqiu`.* TO 'wang'@'192.168.1.%'                                                    |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

通常开发同事在让运维同事开通mysql权限时，他们会在自己本地mysql里生成一个密文密码，然后把这个密文密码给运维同事，运维同事在用这个密文密码进行授权，
那么授权的密码就只有开发同事自己知道了，其他人都不知道！比较安全的一种做法~

mysql操作命令梳理（5）-执行sql语句查询即mysql状态说明

在日常mysql运维中，经常要查询当前mysql下正在执行的sql语句及其他在跑的mysql相关线程，这就用到==mysql processlist==这个命令了。
mysql> show processlist; //查询正在执行的sql语句
mysql> show full processlist; //查询正在执行的完整sql语句
mysql> kill connection id //停掉processlist查询出的某个线程，id是对应的id号

mysql> show processlist;
+------+-------+---------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id   | User  | Host                | db     | Command     | Time | State                                                                 | Info             |
+------+-------+---------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+
| 1574 | root  | localhost           | huanpc | Sleep       |  702 |                                                                       | NULL             |
| 1955 | root  | localhost           | NULL   | Query       |    0 | init                                                                  | show processlist |
| 1958 | slave | 192.168.1.102:37399 | NULL   | Binlog Dump |   10 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
+------+-------+---------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

mysql> kill connection 1574;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+------+-------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id   | User  | Host                | db   | Command     | Time | State                                                                 | Info             |
+------+-------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| 1955 | root  | localhost           | NULL | Query       |    0 | init                                                                  | show processlist |
| 1958 | slave | 192.168.1.102:37399 | NULL | Binlog Dump |   18 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
+------+-------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

除此之外，show processlist还能查看当前mysql连接数。
如果是root帐号，能看到所有用户的当前连接。
如果是其它普通帐号，只能看到自己占用的连接。
注意：
show processlist;只列出前100条
如果想全列出要使用show full processlist;

使用show status;可以比较全面地查看到mysql状态

mysql> show status;

参数解释：
Aborted_clients              由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。 
Aborted_connects          尝试已经失败的MySQL服务器的连接的次数。 
Connections                   试图连接MySQL服务器的次数。 
Created_tmp_tables       当执行语句时，已经被创造了的隐含临时表的数量。 
Delayed_insert_threads  正在使用的延迟插入处理器线程的数量。 
Delayed_writes               用INSERT DELAYED写入的行数。 
Delayed_errors               用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。 
Flush_commands            执行FLUSH命令的次数。 
Handler_delete                请求从一张表中删除行的次数。 
Handler_read_first           请求读入表中第一行的次数。 
Handler_read_key            请求数字基于键读行。 
Handler_read_next           请求读入基于一个键的一行的次数。 
Handler_read_rnd             请求读入基于一个固定位置的一行的次数。 
Handler_update                请求更新表中一行的次数。 
Handler_write                   请求向表中插入一行的次数。 
Key_blocks_used              用于关键字缓存的块的数量。 
Key_read_requests           请求从缓存读入一个键值的次数。 
Key_reads                        从磁盘物理读入一个键值的次数。 
Key_write_requests          请求将一个关键字块写入缓存次数。 
Key_writes                        将一个键值块物理写入磁盘的次数。 
Max_used_connections      同时使用的连接的最大数目。 
Not_flushed_key_blocks     在键缓存中已经改变但是还没被清空到磁盘上的键块。 
Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。 
Open_tables                       打开表的数量。 
Open_files                          打开文件的数量。 
Open_streams                    打开流的数量(主要用于日志记载） 
Opened_tables                    已经打开的表的数量。 
Questions                           发往服务器的查询的数量。 
Slow_queries                       要花超过long_query_time时间的查询数量。 
Threads_connected              当前打开的连接的数量。 
Threads_running                  不在睡眠的线程数量。 
Uptime                                服务器工作了多少秒。

mysql操作命令梳理（6）-中文乱码问题

在平时的mysql运维操作中，经常会碰到插入中文字段后出现乱码的情况，产生中文乱码的原因一般有：
1）mysql的编码格式不对，是latin1编码。强烈推荐将mysql下的编码格式都改为utf8，因为它兼容世界上所有字符！
2）mysql的表的语系设定问题(包含character与collation)
3）客户端程式(例如php)的连线语系设定问题

下面就对Mysql下处理数据表中中文字段乱码问题的操作做一记录：
为了防止后续操作出现乱码现象，最好在创建库或数据表的时候就设置正确的编码。

创建数据库的时候,设置编码格式
mysql> CREATE DATABASE hqsb
    -> CHARACTER SET utf8
    -> COLLATE utf8_general_ci; 
Query OK, 1 row affected (0.01 sec)

创建表的时候，设置编码格式
mysql> use hqsb;
Database changed
mysql>   CREATE TABLE haha (                                                                                                                  
    ->   id int(10) PRIMARY KEY AUTO_INCREMENT,
    ->   name varchar(64) NOT NULL
    ->   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

这3个设置好了，基本就不会出问题了,即建库和建表时都使用相同的编码格式。

如果在建库建表的时候没有指明编码格式导致中文乱码，可以通过以下方式进行查询。
1）查看mysql系统默认的编码格式（保证下面查询结果中的所有编码格式都是utf8，有不是的就手动修改！）:

mysql> show variables like "%char%"; 
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | gbk                             |
| character_set_connection | gbk                              |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | gbk                              |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

手动进行修改：

mysql> SET character_set_filesystem='utf8'; 
Query OK, 0 rows affected (0.00 sec)

再次查看是否已修改：

mysql> show variables like "%char%"; 
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | utf8                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

2）查看数据库（比如hqsb）的编码格式:

mysql> show create database hqsb;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| hqsb     | CREATE DATABASE `hqsb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

如果数据库的编码格式不正确，可以手动修改：

mysql> ALTER DATABASE hqsb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 
Query OK, 1 row affected (0.01 sec)

3）查看数据表（比如haha）的编码格式:

mysql> show create table haha;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| haha  | CREATE TABLE `haha` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

如果数据表的编码格式不正确，可以手动修改：

mysql> ALTER TABLE haha DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.01 sec)

注意：
在客户端（比如xshell）里连接mysql插入数据，==最好事先确保编码格式为Utf-8==。

解决网页中乱码的问题
1）将网站编码设为utf-8。
2）如果网站已运作了好久,并有很多旧数据,不能再更改简体中文的设定,那么建议将页面的编码设为GBK。
GBK与GB2312的区别就在于:GBK能比GB2312显示更多的字符,要显示简体码的繁体字,就只能用GBK。
3）编辑/etc/my.cnf　,在[mysql]段加入default_character_set=utf8;
4）在编写Connection URL时，加上?useUnicode=true&characterEncoding=utf-8参数;
5）在网页代码中加上一个”set names utf8”或者”set names gbk”的指令，告诉MySQL连线内容都要使用utf8或者gbk;