mysql数据库用户管理与索引-优快云博客

本文链接：https://blog.youkuaiyun.com/2401_85041083/article/details/144586198

一. 数据库用户管理

主键：都是在创建表的时候一起创建好的，表结构在工作是严禁随意修改的。

1.1 创建用户

mysql> create user 'xy104'@'192.168.246.7'identified by '123456';
xy104：是用户名
localhost：这个是权限最高的root用户

#登录用户
mysql> mysql -h 192.168.246.6 -u xy104 -p123456

#查看当前登录用户
mysql> select user();

#创建后的用户保存在 mysql 数据库的 user 表里
mysql> select host,user,authentication_string,plugin from user;

1.2 赋权和解除权限

增删改查所有命令

select
update
alter
create
drop
delete

查看权限

show grants for 'xy104'@'192.168.233.22';

在这里插入图片描述

#查看库
show databases;

在这里插入图片描述

赋权

grant select,insert on jx1.* to 'jx404'@'192.168.246.7';
jx1.*：左边是库名，右边是表名

flush privileges;

解除权限

nevoke delete on jx1.* to 'jx404'@'192.168.246.7';

flush privileges;

1.3 删除用户

drop user 'jx404'@'192.168.246.7';

1.4 免密登录

vim /etc/my.cnfsy
#在配置文件中添加
skip-grant-tables

stemctl restart mysqld

#使用 update 修改 root 密码，刷新数据库
update user set authentication_string = '密码' where user = 'root';

FLUSH PRIVILEGES;

1.5 删除表内的数据，但是保留表名和表的结构

#创建自动递增的表
create table if not exists jx404 (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50)
);

zerofill：不满足4位的前面会用0来自动填充

方法1
delete from 表名;
如果该表有自增长的字段，delete删除之后，会保留之前的自增长记录
方法2
truncate table 表名;
全部清空，自增长记录也会从1开始

二. mysql的索引

索引在所有的关系型数据库当中的都有，索引是一个排序的列表，在这个列表当中存储了索引的值和这个值对应数据所在的物理地址。
使用了索引之后，查询数据表时，可以不用全表扫描来定义数据所在行。
可以通过所以直接找到该行数据对应的物理地址，访问数据，提高查询的速度。
合理的设置索引可以提高数据库查询速度，也是必备的一项操作。

2.1 索引的作用

索引就相当于书本的目录，当表很大(数据量多)或者查询涉及到多个表时(不超过3个)使用索引可以上千上万倍的提高查询速度。
可以降低数据库的I/O成本，索引也会降低数据库的排序成本
也可以创建唯一性索引。
可以加快表于表之间的连接速度
使用索引可以减少分组和排序的时间
通过索引可以提高数据库的恢复速度

2.2 索引的副作用

1、索引也需要占用磁盘的空间
2、更新一个有索引的表比更新没有索引的表时间更长，因为索引也需要更新。

2.3 索引的创建原则

虽然索引可以提高访问速度，但是也不是任何情况下都需要索引。
在有索引的情况下，数据库会先查索引然后再定位到数据行，索引的设置如果不合理，反而会增加数据库的负担。

表的主键和外键必须有索引，但是主键和外键默认就是索引
查过300行的表数据，就需要添加索引了。
和其他表连接的字段，要建立索引。
唯一性差的字段不适合设置索引。
字段更新频繁，不适合索引
经常出现在where条件的字段当中，尤其是大表，应该建立索引
在经常进行 group by 和 order by，要创建索引

2.4 索引的类型

mysgl的索引默认就是B-TREE索引
叶子节点上包含的条目直接指向到数据行，叶子节点之间也互相连接，形成了一个向下延伸的指针，根据索引值一步一步定位到数据行。
哈希索引：他是把索引值转换成值，通过对应索引的hash值定位数据所在的行

2.5 索引的分类和创建

主键索引

#建表时创建索引
create table member (id int(10)PRIMARY KEY,
name varchar(10),
cardid int(18),
phone int(11),
address varchar(50),
remark text,
);

#主键索引
explain select * from member where id= 1;

一般索引（普通索引）

create index name_index on member (name(5));
name(5)：例名当中匹配的长度，在不损失精确性的情况下，长度越小越好。减少索引文件的大小。

#建表时创建索引
create table member1 (id int(10)PRIMARY KEY,
name varchar(10),
cardid int(18),
phone int(11),
address varchar(50),
remark text,
index name index(name)
);

#建表后创建
create index 索引名 on 表名 (字段); 
#索引名建议以“_index”结尾

唯一索引

该字段的值必须是唯一的，但是可以为空。添加唯一约束，自动就是唯一索引。

#建表时创建索引
create table member2 (id int(10)PRIMARY KEY,
name varchar(10) unique key,
cardid int(18),
phone int(11),
address varchar(50),
remark text
);

explain select * from member2 where id= 1;

#建表后创建
create unique index cardid_index on member2(cardid);

组合索引

可以是单字段的索引，也可以是多字段的组合

create table member5 (id int(10)PRIMARY KEY,
name varchar(10) unique key,
cardid int(18),
phone int(11),
address varchar(50),
remark text,
unique index cardid_phone_address (cardid,phone,address)
);
 
explain select * from member5 where iphone="" and cardid=" ";

#建表后创建
alter table member5 add constraint uc_cardid_phone_address uniqhe (cardid,phone,address)

全文索引

适合模糊查询进行匹配时使用，用来检索文章当中的信息。

#建表后创建
create fulltext index remark_index on member6(remark);

explain select * from member6 where match(remark) against(' ');