Mysql数据库用户管理，授权，索引

一、Mysql用户管理

1、新建用户

CREATE USER ‘用户名’@‘来源地址’ [IDENTIFIED BY [PASSWORD] '密码‘];

'用户名'：指定将创建的用户名.

'来源地址'：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录

可用通配符%

'密码'：若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密；

若使用加密密码，需要先使用SELECT PASSWORD('密码')；获取密文，再在语句中添PASSWORD '密文'；

若省略“IDENTIFIED BY"部分，则用户的密码将为空(不建议使用)

mysql> CREATE USER 'jj'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)


mysql> select password ('123456');       ###先获取加密的密码
+-------------------------------------------+
| password ('123456')                       |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> create user 'mm'@'192.168.246.%' identified by '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
Query OK, 0 rows affected (0.00 sec)

注：jj只能使用本机登录，mm可以使用192.168.246网段的任意主机登录

2、查看用户信息

创建后的用户保存在mysql数据库的user表里

USE mysql;

SELECT User,authentication_string,Host from user;

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user,authentication_string,Host from user;
+-----------+-------------------------------------------+---------------+
| user      | authentication_string                     | Host          |
+-----------+-------------------------------------------+---------------+
| root      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost     |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost     |
| jj        | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost     |
| mm        | *8E33A6D2D844B026DB599F1BC6F1A2B8665A1CAE | 192.168.246.% |
+-----------+-------------------------------------------+---------------+
4 rows in set (0.00 sec)

3、重命名指定

RENAME USER '用户1'@'地址' TO '用户2'@'地址';

mysql> rename user 'jj'@'localhost' to 'mjj'@'192.168.246.%';
Query OK, 0 rows affected (0.00 sec)

4、删除用户

DROP USER '用户'@'地址' ;

mysql> drop user 'mjj'@'192.168.246.%';
Query OK, 0 rows affected (0.00 sec)

5、修改密码

1.修改当前密码

SET PASSWORD = PASSWORD('密码');

mysql> set password = password('654321');
Query OK, 0 rows affected, 1 warning (0.00 sec)

2.修改其它用户密码

SET PASSWORD FOR '用户'@'地址' = PASSWORD('密码');

mysql> set password for 'mm'@'192.168.246.%' = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

6、忘记root密码的解决方法

1.修改/etc/my.cnf配置文件，免密登录mysql

2.使用SQL语句修改密码

3.最后把/etc/my.cnf里免密登录文件删除

vim /etc/my.cnf
[mysqld]
skip-grant-tables    #添加，使登录mysql不使用授权表
systemctl restart mysqld
mysql                #直接登录
UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD('密码') where user='root';
                     #使用SQL语句修改密码
FLUSH PRIVILEGES;    #刷新
quit
vim /etc/my.cnf      #进入配置文件删除免密登录mysql的配置
mysql -u root -p密码 #测试

二、数据库用户授权

1、授予权限

grant 提权

GRANT  权限列表  ON  数据库名.表名  TO  '用户名'@'来源地址'  [IDENTIFIED BY '密码'];

#权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select, insert,  

update”。使用"all"表示所有权限，可授权执行任何操作。

#数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符"*"。

#'用户名@来源地址'：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址。

#IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。

在新建用户时，若省略“IDENTIFIED BY"部分，则用户的密码将为空。

例1：允许用户mm在本地查询xjjdq数据库中所有表的数据记录，

但禁止查询其他数据库中的表的记录。

GRANT select ON xjjdq.* TO 'mm'@'localhost' IDENTIFIED BY '123456';

例2：#允许用户mm在所有终端远程连接mysql，并拥有所有权限。

GRANT ALL [PRIVILEGES] ON *.* TO 'mm'@'%' IDENTIFIED BY '123456';

授权用户权限all privilege。all privilege权限如下:

2、刷新权限

flush privileges；

3、查看权限

SHOW GRANTS FOR 用户名@来源地址；

4、撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址；

三、Mysql索引

1、索引概述

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于C语言的链表通过指针指向数据记录的内存地址）。

使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。

索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容。

索引是表中一列或者若干列值排序的方法。

建立索引的目的是加快对表中记录的查找或排序

2、索引的优缺点

优点：

设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因。

当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。

可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本。

通过创建唯一(键)性索引，可以保证数据表中每一行数据的唯一性。

可以加快表与表之间的连接。

在使用分组和排序时，可大大减少分组和排序的时间。

缺点：

索引需要占用额外的磁盘空间。

对于 MyISAM 引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址。

而 InnoDB 引擎的表数据文件本身就是索引文件。

在插入和修改数据时要花费更多的时间，因为索引也要随之变动。

3、创建索引的原则依据

索引虽可以提升数据库查询的速度，但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源，在有索引的情况下，数据库会先进行索引查询，然后定位到具体的数据行，如果索引使用不当，反而会增加数据库的负担。

1.表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是子表的主键，查询时可以快速定位

2.记录数超过300行的表应该有索引。如果没有索引，需要把表遍历一遍，会严重影响数据库的性能。

3.经常与其他表进行连接的表，在连接字段上应该建立索引。

4.唯一性太差的字段不适合建立索引。

5.更新太频繁地字段不适合创建索引。

6.经常出现在 where 子句中的字段，特别是大表的字段，应该建立索引。

select name,score from xjj where id=1

7.索引应该建在选择性高的字段上。

8.索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。

id type score mm(txt)  page  blog

blob clob  txt

MySQL的优化哪些字段/场景适合创建索引

①小字段

②唯一性强的字段

③更新不频繁，但查询率很高的字段

④表记录超过300+行

⑤主键、外键、唯一键

四、索引的分类和创建

模板：

mysql> create database max;
mysql> use max
Database changed
mysql> create table mvjj (id int(10),name varchar(10),tall decimal(5,2),tel varchar(11),address varchar(50),remark text);
mysql> insert into mvjj values(1,'a','160','111','rujia','this is jp');
mysql> insert into mvjj values(2,'b','158','222','gelinhaotai','this is jp');
mysql> insert into mvjj values(3,'c','168','333','gelinhaotai','this is jp');
mysql> insert into mvjj values(4,'d','168','444','hanting','this is jp');
mysql> insert into mvjj values(5,'e','165','555','rujia','this is jp');
mysql> insert into mvjj values(6,'f','157','666','rujia','this is loli');
mysql> select * from mvjj;
+------+------+--------+------+-------------+--------------+
| id   | name | tall   | tel  | address     | remark       |
+------+------+--------+------+-------------+--------------+
|    1 | a    | 160.00 | 111  | rujia       | this is jp   |
|    2 | b    | 158.00 | 222  | gelinhaotai | this is jp   |
|    3 | c    | 168.00 | 333  | gelinhaotai | this is jp   |
|    4 | d    | 168.00 | 444  | hanting     | this is jp   |
|    5 | e    | 165.00 | 555  | rujia       | this is jp   |
|    6 | f    | 157.00 | 666  | rujia       | this is loli |
+------+------+--------+------+-------------+--------------+

1、普通索引

最基本的索引类型，没有唯一性之类的限制。

1.直接创建索引

CREATE INDEX 索引名 ON 表名 (列名[(length)]);

#(列名(length))：length是可选项。如果忽略 length 的值，则使用整个列的值作为索引。如果指定使用列前的 length 个字符来创建索引，这样有利于减小索引文件的大小。

#索引名建议以“_index”结尾。

mysql> create index tall_index on mvjj (tall);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select tall from mvjj;
+--------+
| tall   |
+--------+
| 157.00 |
| 158.00 |
| 160.00 |
| 165.00 |
| 168.00 |
| 168.00 |
+--------+
6 rows in set (0.00 sec)
mysql> show create table mvjj;
| mvjj  | CREATE TABLE "mvjj" (
  "id" int(10) DEFAULT NULL,
  "name" varchar(10) DEFAULT NULL,
  "tall" decimal(5,2) DEFAULT NULL,
  "tel" varchar(11) DEFAULT NULL,
  "address" varchar(50) DEFAULT NULL,
  "remark" text,
  KEY "tall_index" ("tall")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

2.修改表方式创建

ALTER TABLE 表名 ADD INDEX 索引名 (列名);

mysql> alter table mvjj add index id_index (id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table mvjj;
| mvjj  | CREATE TABLE "mvjj" (
  "id" int(10) DEFAULT NULL,
  "name" varchar(10) DEFAULT NULL,
  "tall" decimal(5,2) DEFAULT NULL,
  "tel" varchar(11) DEFAULT NULL,
  "address" varchar(50) DEFAULT NULL,
  "remark" text,
  KEY "tall_index" ("tall"),
  KEY "id_index" ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

3.创建表的时候指定索引

CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,...],INDEX 索引名 (列名));

create table test(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index id_index (id));

2、唯一索引

与普通索引类似，但区别是唯一索引列的每个值都唯一。

唯一索引允许有空值（注意和主键不同）。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引。

1.直接创建唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

mysql> create unique index tel_index on mvjj(tel);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create unique index tall_index on mvjj(tall);
ERROR 1061 (42000): Duplicate key name 'tall_index'

只有数据唯一没有重复的才可以直接创建唯一索引

2.修改表方式创建

ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);

3.创建表的时候指定

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));

3、主键索引

是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。

一个表只能有一个主键，不允许有空值。 添加主键将自动创建主键索引。

1.创建表的时候指定

CREATE TABLE 表名 ([...],PRIMARY KEY (列名));

2.修改表方式创建

ALTER TABLE 表名 ADD PRIMARY KEY (列名);  

4、组合索引（单列索引与多列索引)

可以是单列上创建的索引，也可以是在多列上创建的索引。需要满足最左原则，因为select语句的 where条件是依次从左往右执行的，所以在使用select 语句查询时where条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。

CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));

select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';

5、全文索引（FULLTEXT）

适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。

在 MySQL5.6 版本以前FULLTEXT 索引仅可用于 MyISAM 引擎，在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引。全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。每个表只允许有一个全文索引。

1.直接创建索引

CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);

2.修改表方式创建

ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

3.创建表的时候指定索引

CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));  

#数据类型可以为 CHAR、VARCHAR 或者 TEXT

4.使用全文索引查询

SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');

6、查看索引

show index from 表名;

show index from 表名\G； 竖向显示表索引信息

show keys from 表名;

show keys from 表名\G;

各字段的含义如下：

Table 表的名称

Non_unique 如果索引内容唯一，则为 0；如果可以不唯一，则为 1。

Key_name 索引的名称。

Seq_in_index 索引中的列序号，从 1 开始。 limit 2,3

Column_name 列名称。

Collation 列以什么方式存储在索引中。在 MySQL 中，有值‘A’（升序）或 NULL（无分类）。

Cardinality 索引中唯一值数目的估计值。

Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目(zhangsan)。如果整列被编入索引，则为 NULL。

Packed 指示关键字如何被压缩。如果没有被压缩，则为 NULL。

Null 如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO。

Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。

Comment 备注。

7、删除索引

1.直接删除索引

DROP INDEX 索引名 ON 表名;

例：drop index name_index on member;

2.修改表方式删除索引

ALTER TABLE 表名 DROP INDEX 索引名;

例：alter table member drop index id_index;

show index from member;

3.删除主键索引

ALTER TABLE 表名 DROP PRIMARY KEY;

小结：

索引分为：

① 普通索引 ：针对所有字段，没有特殊的需求/规则

② 唯一索引 : 针对唯一性的字段，仅允许出现一次空值

③ 组合索引 (多列/多字段组合形式的索引)

④ 全文索引（varchar char text）

⑤ 主键索引 ：针对唯一性字段、且不可为空，同时一张表只允许包含一个主键索引

创建索引：

① 在创建表的时候，直接指定index

② alter修改表结构的时候，进行add 添加index

③ 直接创建索引index

PS:主键索引---->直接创建主键即可