一、pymysql模块

一、pymysql模块

（一）如何使用

fetchall()：获取所有数据，返回的是列表套字典
fetchone()：获取一条数据，返回的是字典
fetchmany(size)：获取size条数据，返回的是列表套字典
commit：当sql中有参数时，必须要使用conn.commit()
executemany：新增多条数据

import pymysql

conn = pymysql.connect(host='localhost',user='root',password='wickyo',database 'test',charset='utf8')  # 连接数据库

# 创建一个游标对象cursor
# cursor = conn.cursor() # 默认返回的值是元组类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 返回的是字典对象

# 1. 查
sql = 'select * from userinfo'
cursor.execute(sql)

res = cursor.fetchall()  # 获取所有的数据，返回的是列表套字典
res = cursor.fetchone()  # 获取一条数据，返回的是字典类型
res = cursor.fetchmany(12)  # 获取12条数据，返回的是列表套字典
print(res)

# 2. 增
## （1）新增一条数据
sql = "insert into user (name, password) values (%s,  %s)"
cursor.execute(sql, ('xxx', 'qwe'))  ### 

## （2）新增多条数据 executemany
data = [
    ('zekai1', 'qwe'),
    ('zekai2', 'qwe1'),
    ('zekai3', 'qwe2'),
    ('zekai4', 'qwe3'),
]
cursor.executemany(sql, data)
conn.commit() # 必须要加这一句
print(cursor.lastrowid)  # 打印最后一行id

# 3. 修
sql = "update user set name=%s where id=%s"
cursor.execute(sql, ('dgsahdsa', 2))
conn.commit()

# 4. 删
sql = "delete from user where id=%s"
cursor.execute(sql, ('dgsahdsa', 2))
conn.commit()

cursor.close()  # 关闭cursor对象
conn.close()  #关闭数据库里对象

（二）sql注入问题

利用mysql的#会注释的漏洞，避开密码验证

当输入的用户名为xxx' or 1=1 #时，下面的例子中sql会变成'select * from user where name = xxx' or 1=1 # and password=%s',#后面的内容会被注释掉，会导致sql注入问题
原因

没有做任何检验限制
解决办法

将参数传给execute，利用模块本身进行一个校验

import pymysql

user = input('请输入用户名:')
password = input('请输入密码:')

conn = pymysql.connect(host='localhost',user='root',password='wickyo',databse=  'test',charst='utf8')

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

# sql1 = "select * from user where name='%s' and password='%s'" % (user, pwd)
# cursor.execute(sql1)
sql2 = 'select * from user where name = %s and password=%s'

cursor.execute(sql2,(user,pwd))

res = cursor.fetchall()
print(res)

cursor.close()
conn.close()

if res:
    print('登陆成功')
else:
    print('登录失败')

二、索引

优缺点：

优点：加快查询速度

缺点：占用大量磁盘空间
索引的本质：是一个特殊的文件
索引的底层原理：B+树

（一）主键索引

加速查找
不能重复
不能为空

（1）增

# 1. 创建时
create table xxx(
id int auto_increment,
primary(id)
)

# 2. 通过modify修改字段
alter table xxx modify id int auto_increment primary key;

# 3. 通过add修改字段
alter table xxx add primary key(id);

（2）删

alter table t1 drop primary key

（二）唯一索引

unique(字段名)

加速查找
不能重复

（1）增

# 1. 创建表时
craete table t2(
id int auto_increment primary key,
name varchar(32) not null default'',
unique u_name(name)
)charset utf8;

# 2.创建表结构
create unique index 索引名 on 表名 （字段名;）
# 例子
create unique index ix_name on t2(name);

# 3. 修改表结构
alter table 表名 add unique index 唯一索引名(字段名)

#例子
alter table t2 add unique index ix_name (name)

（2）删

alter table 表名 drop index 唯一索引名;

# 例子
alter table t2 drop index u_name;

（三）普通索引

index(字段名)，加速查找

（1）增

# 1. 创建表时
create table t3 (
id int auto_incremnt primary key,
name varchar (32) not null default '',
index u_name (name))

# 2. 创建表结构
create index ix_name on t3(name);

# 3. 修改表结构
alter table t3 add index ix_name (name)

（2）删

alter table t3 drop index ix_name;

（四）联合索引

联合主键索引

primary key (id,name)
联合唯一索引

unique(id,name)
联合普通索引

index(id,name)

（五）不会命中索引的情况

四则运算

不能在SQL语句中，进行四则运算，会降低SQLd的查询效率
使用函数

select * from t1 where reverse(email)= 'wick';
类型不一致

如果列是字符串类型，传入条件必须用引号括起来

select * from t1 where email = 999;
order by

select name from s1 order by email;

当根据索引排序时，如果select查询的字段不是索引，效率也较低
- 如果对主键排序，速度还是很快select * from t1 order by id ;
count

count(*)相较于count(列名)或count(1),结果一昂，前者效率低
组合索引最左前缀

select * from user where name = 'wick'and email = 'wick@qq.com';

上述场景想要添加索引加快速度，错误中做法：

index ix_name(name)

index ix_email(email)

正确的做法：

index ix_name_email(name,email)
1. where name = 'wick' and email= 'wick@11.com'此种情况索引命中
2. where name='wick'此种情况也命中
3. where email = 'wick@qq.com'此种情况未命中
注意：
```
index (a,b,c,d)
where a=2 and b=3 and c=1 and d=1;  #命中
where a=2 and c=3 and d=4  # 命中
where b =2 and c= 3 and d=3  # 未命中
```

（六）explain

explain select * from user where name = 'wick' and email = 'wick@qq.com'\G

# id: 1
# select_type: SIMPLE
# table: user
# partitions: NULL
# type: ref       索引指向 all
# possible_keys: ix_name_email     可能用到的索引
# key: ix_name_email     确实用到的索引
# key_len: 214            索引长度
# ref: const,const
# rows: 1            扫描的长度
# filtered: 100.00
# Extra: Using index   使用到了索引

（七）索引覆盖

被查询的列，数据能从索引中取得，而不用通过行定位符 row-locator 再到 row 上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。

select id from t1 where id = 2000;

三、慢查询日志

# 1. 查看慢SQL的相关变量
show variables like '%slow%';

# 2. 查看long相关的变量
show variables like '%long%';

# 3. 配置慢相关的的变量
# set global 变量名=值

# 设置慢查询日志开关
set global slow_query_log = on;

# 设置慢查询日志存储地址
set global slow_query_log_file= 'D:/mysql/data/myslow.log';

# 设置慢查询匹配时间
set global long_query_time = 1

巴特西

四、pymysql模块、索引和慢查询

一、pymysql模块

（一）如何使用

（二）sql注入问题

二、索引

（一）主键索引

（1）增

（2）删

（二）唯一索引

（1）增

（2）删

（三）普通索引

（1）增

（2）删

（四）联合索引

（五）不会命中索引的情况

（六）explain

（七）索引覆盖

三、慢查询日志

最新文章

热门文章