自我总结31
python操作MySQL
pymysql模块
安装 pip install pymysql
sql注入问题
输入用户名：xx(名字) ' or 1=1 #
输入密码：xxxxxx(密码)
select * from user where name='xx ' or 1=1 #' and password='xxx'
[{'id': 1, 'name': 'zhangsan', 'password': ''},
{'id': 2, 'name': 'zekai', 'password': '123'},
{'id': 3, 'name': 'kkk', 'password': ''}]
产生的原因: 因为过于相信用户输入的内容， 根本没有做任何的检验
解决的方法:
sql = "select * from user where name=%s and password=%s"
cursor.execute(sql, (user, pwd))
用cursor.execute来检验输入的用户名和密码
连接
连接数据库的参数
conn = pymysql.connect(host='localhost',
user='root',password='123qwe',database='test',charset='utf8')
cursor=conn.cursor() 默认返回的值是元祖类型
cursor =conn.cursor(cursor=pymysql.cursors.DictCursor) 返回的是字典类型
查
fetchall() ： 取出所有的数据 返回的是列表套字典
fetchone() ： 取出一条数据 返回的是字典
fetchmany(size) ： 取出size条数据 返回的是列表套字典
增
sql = "insert into user (name, password) values (%s, %s)"
cursor.execute(sql, ('aaa', 'qqq')) ### 新增一条数据
data = [
('aaa','qqq'),
('aaa1','qqq1'),
('aaa2','qqq2'),
]
cursor.executemany(sql, data) ### 新增多条数据
conn.commit() # 除了查 ，必须要加该代码
print(cursor.lastrowid) ### 获取最后一行的ID值
修
sql = "update user set name=%s where id=%s"
cursor.execute(sql, ('dgsahdsa', 2))
conn.commit()
cursor.close()
conn.close()
删
sql = "delete from user where id=%s"
cursor.execute(sql, ('dgsahdsa', 2))
conn.commit()
cursor.close()
conn.close()
索引
索引的作用
为了提高查询效率
类比 字典中的目录
索引的本质
一个特殊的文件
索引的底层原理
B+树
索引的种类
主键索引：加速查找+不能重复+不能为空 primary key
唯一索引：加速查找+不能重复 unique（name）
联合唯一索引： unique（name，email）
普通索引： 加速查找 index（name）
联合(组合)索引：index （ name，email）
索引的创建
主键索引
- 增加
create table xxx(
id int auto_increment,
primary key(id)
)
alter table xxx change id id int auto_increment primary key
alter table xxx add primary key(id);
- 删除
alter table xxx drop primary key;
唯一索引
- 增加
1. create table x1(
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 x1(name);
3. alter table x1 add unique index ix_name(name)
- 删除
alter table x2 drop index u_name;
普通索引
- 增加
1. create table x2(
id int auto_increment primary key,
name varchar(32) not null default '',
index u_name(name)
)charset utf8
2. reate index 索引名 on 表名（字段名）；
create index ix_name on x2(name);
3. alter table x2 add index ix)name(name)
- 删除
alter table x2 drop index u_name;
索引的优缺点
通过观察：*.ibd文件可知：
- 索引加快了查询速度
- 但加了索引之后，会占用大量的磁盘资源
索引加的越多越好？不是
不会命中索引的情况
-
不能再SQL语句中，进行四则运算，会降低SQL的查询效率
-
使用函数
select * from tb1 where reverse(email) ='aaa';
-
类型不一致
如果列是字符串类型，传入的条件必须用引号引起来
排序条件为索引，则select字段必须也是索引字段，否则无法命中
-
order by
select name from s1 order by email desc;
当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢
select email from s1 order by email desc;
特别的：如果对主键排序，速度还是很快
select * from tb1 order by nid desc;
-
count(1)或count(列)代替count(*)在mysql中没有差别
-
组合索引最左前缀
什么时候回创建联合索引？
根据公司的业务场景，在嘴常用的几列上添加索引
select * from user where name='mqb' and email ='mqb@qq.com';
如果遇到上述业务情况，错误的做法是：
index ix_name(name),
index ix_email(email)
正确的做法：
index ix_name_email(name,email)
如果组合索引为：ix_name_email (name,email)
where name='zekai' and email='xxxx' -- 命中索引
where name='zekai' -- 命中索引
where email='zekai@qq.com' -- 未命中索引 -
explain
explain select * from user where name ='mqb' and email ='mqb @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 使用到了索引索引覆盖：
select id from user where id =2000;
慢查询日志
查看慢SQL的相关变量
show variables like '%slow%'
配置慢SQL的变量
set global 变量名=值
set global slow_query_log=on;
set global slow_query_log_file='地址'；
set global long_query_time=1;
