用户管理
- 创建mysql账户
- 权限管理
  - 涉及到的表
    - 语法
    - 案例
pymysql
索引
- 语法
- 结论

用户管理

主要是为了控制权限，让不同的人只能操作只属于只记得那部分数据

创建mysql账户

账户中涉及三个数据

账户名
密码
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 用户名@主机地址 identified by "密码";

# 创建某个库下某个表的所有权限

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

# 创建某个库下某个表的某些字段权限

grant 事件(字段名),事件(字段名) on 库名.表名 to 用户名@主机地址 identified by "密码";

# 注意:事件包括update/select/delete....

# 收回权限

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

# 刷新权限

flush privileges;

# 将自己账户的权限给其他账户,自己的账户就得有授予权限的权限,可以通过with grant option进行

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

# 注意:这种操作只能把自己拥有的权限给其他人

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

grant all  on *.*  to 用户名@"%"  identified by "密码";

grant all  on *.*  to 用户名@localhost  identified by "密码";

案例

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

pymysql

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

基本使用

"""

1. 导入模块

2. 连接服务器

3. 用户认证

4. 发送指令

5. 提取结果

"""

import pymysql

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

conn = pymysql.connect(

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

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

    user = "root",  # 必填

    password = "123",  # 必填

    database = "test"  # 必填

)

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

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

# 执行sql语句

sql = "select * from table1"

res = c.execute(sql)  # 返回的是查询结果的数量

# 提取结果,这边获取的结果是一个迭代器,不能往回找结果

c.fetchall()  # 获取所有结果

# c.fetchmany(2) # 获取两条结果

# c.fetchone()  # 获取一条结果

# 关闭连接

c.close()

conn.close()

# 如果想获取之前的结果,可以通过移动光标 scroll进行,mode指定为相对路径或者绝对路径

c.scroll(1,mode="absolute/relative")

sql注入攻击

指的是用户在输入数据时,按照sql语句来编写带有攻击目的的sql语句,并插入到原原始语句中执行

例如:登录功能,需要用户输入用户名和密码

正常的一个登录功能代码如下:

try:

    conn = pymysql.connect(host="127.0.0.1",port=3306,user="root",password="",db="day46",)

    print("连接服务器成功!")

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

    user = input("username:")

    password = input("password:")

    count = cursor.execute("select *from user where name = '%s' and password = '%s'" % (user,password))

    if count:

        print("登录成功!")

    else:

        print("登录失败!")

except Exception as e:

    print(type(e),e)

finally:

    if cursor:cursor.close()

    if conn: conn.close()

上述代码有被注入攻击的危险

尝试在用户名中输入以下内容,密码随意

 23' — ass

或者连用户名都不用写

' or 1 = 1 -- asaa

解决方案:

客户端发送sql给服务器前进行re判断

这样的问题在于一些程序可以模拟客户端直接发送请求给服务器
在服务器端将sql交给mysql是作进一步处理,相关的代码其实pymysql已经做了封装

我们只要保证不要自己来拼接sql语句即可,将拼接参数操作交给pymysql.

案例

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()

注意:pymysql会自动开启事务,所以需要在合适的位置进行提交(commit)

索引

索引就是一个特殊的数据结构,存储的是数据的关键信息与详细信息的位置对应关系

为什么需要索引

索引就相当于书本的目录,可以简便查询,降查询范围进行了缩小,这样就可以加快查询速度

否则的话,就需要对数据进行遍历,非常耗时,所以添加索引是非常有必要的

索引的本质就是尽可能的减小搜索范围

注意:在数据库插入数据会引发索引的重建

索引的影响

不是说有了索引就能加速,还需要你的查询语句正确使用索引
索引也是需要占用额外的数据空间
添加索引后,将导致增删改速度变慢

所以并不是所有数据都需要添加索引

一般来说, 只有查询较多,写入较少的且数据量较大的数据才需要添加索引

磁盘IO

按照正常的机械硬盘7200r/min, 那么查找一次数据至少花费9ms, 具体计算过程可参考计算机基础,而处理器以每秒计算5亿次, 那么9ms大概可以计算450万次运算,显然读取数据对于CPU来说,太慢了

所以每次找数据的时候CPU都会切换到其他的程序去运行, 下次查找再找CPU,这样CPU切来切去的也会降低CPU的利用效率,所以要增加查询速度就是要减少IO操作的次数

索引数据结构

索引的数据结构就是B+树数据结构

B+数据结构是根据二分法进行查找的, 这样就不需要进行遍历数据进行查找了

最左匹配原则

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

聚集索引

聚集索引中包含了所有字段的值,如果指定了主键,主键就是聚集索引,如果没有指定主键,那么搜索引擎就会自动找一个非空且唯一的字段作为聚集索引, 如果还是没有, 则会自动生成一个字段作为聚集索引

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

辅助索引

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

辅助索引中存放的是当前的索引字段以及主键值

覆盖查询

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

回表查询

回表查询就是当前索引中找不到需要的数据, 那么就需要通过主键去聚集索引中进行查找

速度慢于聚集索引

语法

# 创建索引

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

# 联合索引

create index 索引的名字 on 表名(字段1,字段2);

# 删除索引

drop index 索引名称 on 表名

结论

尽量使用占用空间小的字段作为索引
不要在一行中存储太多数据, 如果字段太多可以进行分表操作
尽量使用覆盖查询
如果字段区分度太低,建立的索引没有什么意义, 也就是说应该将区分度高的字段作为索引
模糊匹配中,百分号尽量不要写在前面
不要在等号的左边进行运算
and语句中,会自动找一个具备索引的字段优先执行, 所以and语句中至少要包含一个具备索引的字段
or语句要尽量避免使用,因为or语句会遍历所有条件,如果一定要用,最好保证所有字段都有索引,这样才能加速
联合索引中, 需要将区分度高的放在左边,最低的放到右边

在使用查询语句的时候, 也要保证最左边的索引出现在语句中

注意:如果查询内容过于庞大也是无法通过索引进行加速的

总结: 不是添加索引就能加速, 还需要考虑索引建立是否合理, 查询语句是否合理使用索引

巴特西

MySQL--用户管理 pymysql 索引