mysql 创建索引权限_MYSQL索引和权限管理

索引

可以理解为: 搜索引导,索引是一个特殊的数据结构,其存储的是数据的关键信息与详细信息的位置对应关系

例如:书本的目录

创建索引的语法:

​create index 索引的名字 on 表名称(字段名)

联合索引

​create index 索引的名字 on 表名称(字段名,字段名)

​create index union_index on usr(id,email,name,gender);

删除索引:

​ drop index 索引名称 on 表名称;

磁盘IO

平均 查找一个数据需要花费至少9ms 这段是CPU 就会切换到其他的程序 ,

我们要加速查询,必须较少io操作的次数

索引数据结构

b+树

在b+树中 叶子节点才是存储真实数据的,叶子数量越多,树的层级越高,导致IO次数增加

要避免这个问题,在叶子节点中尽可能的存储更多的数据, 应该将数据量小的字段作为索引

最左匹配原则

当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候(多字段联合索引)，b+树会按照从左到右的顺序来建立搜索树，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

聚集索引

聚集索引中包含了所有字段的值,如果拟制定了主键,主键就是聚集索引,如果没有则找一个非空且唯一的字段作为聚集索引,如果也找不着,自动生成一个字段作为聚集索引

聚集索引中存储了所有的数据

辅助索引

除了聚集索引以外的都叫做辅助索引

辅助索引中只包含当前的索引字段和主键的值

覆盖查询

指得是在当前索引结构中就能找到所有需要的数据 ,如果使用的是聚集索引来查询那么一定覆盖查询,速度是最快的

回表查询

指得是在当前索引结构中找不到所需的数据,需要通过id 去聚集索引中查询 ,速度慢与聚集索引

用户管理

主要为了控制权限,让不同开发者,仅能操作属于自己的业务范围内的数据

创建myqsl账户

账户中涉及的三个数据

账户名 密码 ip地址

ip是用于限制某个账户只能在那些机器上登录

create user 用户名@主机地址 identified by "密码";

# 注意:操作用户 只能由root账户来进行

# 删除 将同时删除所有权限

drop user 用户名@主机地址;

权限管理

涉及到的表

user 与用户相关信息

db用户的数据库权限信息

tables_priv 用户的表权限

columns_priv 用户的字段权限

语法

grant all on *.* to 用户名@主机地址 identified by "密码";

# 如果用户不存在则自动创建新用户,推荐使用

grant all on *.* to rose@localhost identified by "123";

grant all on day42.* to rose1@localhost identified by "123";

grant all on day42.table1 to rose2@localhost identified by "123";

grant select(name),update(name) on day42.table1 to rose3@localhost identified by "123";

all表示的是对所有字段的增删改查

*.* 所有库的所有表

收回权限

revoke all on *.* from 用户名@主机地址;

revoke all on day42.table1 from rose2@localhost;

# 刷新权限

flush privileges;

#with grant option 表示 可以将他拥有的权限授予其它的用户

grant all on *.* to root1@localhost identified by "123" with grant option;

# 授予某个用户 可以在任何主机上登录

grant all on *.* to jack10@"%" identified by "123";

grant all on *.* to jack10@localhost identified by "123";

pymysql

pymysql 是一个第三方模块,帮我们封装了,建立连接,用户认证,sql'的执行以及,结果的获取

基本使用

import pymysql

"""

1.连接服务器

2.用户认证

3.发送指令

4.提取结果

"""

# 1. 连接服务器 获取连接对象(本质上就是封装号的socket)

conn = pymysql.connect(

host = "127.0.0.1", #如果是本机 可以忽略

port = 3306, # 如果没改过 可以忽略

user = "root", #必填

password = "111", #必填

database = "day42" #必填

)

# 2.通过连接拿到游标对象

# 默认的游标返回的是元组类型 不方便使用,需要更换字典类型的游标

c = conn.cursor(pymysql.cursors.DictCursor)

# 3.执行sql

sql = "select * from table1"

res = c.execute(sql)

# 查询语句将返回查询的结果数量

# 4.提取结果

# print(res)

# print(c.fetchall())

# 5.关闭连接

c.close()

conn.close()

# 移动光标 参数1位移动的位置 mode 指定 相对或绝对

# c.scroll(1,mode="absolute")

# print(c.fetchall())

# print(c.fetchmany(1))

print(c.fetchone())

print(c.fetchone())

修改数据

案例:

import pymysql

conn = pymysql.connect(

host = "127.0.0.1", #如果是本机 可以忽略

port = 3306, # 如果没改过 可以忽略

user = "root", #必填

password = "111", #必填

database = "day42", #必填,

#autocommit=False # 开启自动提交 不常用....

)

c = conn.cursor(pymysql.cursors.DictCursor)

name = input("name:")

pwd = input("pwd:")

sql = "select *from user where name = %s"

if c.execute(sql,(name,)):

print("用户名已存在!")

else:

sql2 = "insert into user values(%s,%s)"

if c.execute(sql2,(name,pwd)):

print("注册成功!")

conn.commit() # 调用连接对象的提交函数

else:

print("注册失败!")

c.close()

conn.close()

调用存储过程

# 创建名为add1的存储过程

delimiter |

create procedure add1(in a int,in b int,out c int)

begin

set c = a + b;

end|

delimiter ;

#pymysql中调用

import pymysql

conn = pymysql.connect(

host = "127.0.0.1", #如果是本机 可以忽略

port = 3306, # 如果没改过 可以忽略

user = "root", #必填

password = "111", #必填

database = "day42", #必填,

autocommit=True # 开启自动提交 不常用....

)

c = conn.cursor(pymysql.cursors.DictCursor)

c.callproc("add1",(1,2,1212)) # @_add1_0 @_add1_1 @_add1_2

c.execute("select @_add1_2")

print(c.fetchone())

# 调用存储过程时,传入参数,会自动定义成变量,

# 命名方式 @_过程的名称_参数的索引 从0开始