博客整理day36 python操作数据库

目录

• python day36 python操作mysql
  • 一 数据库的连接
    • sql注入问题
  • 二 对数据库操作
    • 创建表操作
    • 操作数据
      • 增加数据
      • 修改数据
      • 删除数据
      • 查找数据
  • 三 索引
    • 索引原理
    • 索引的种类
    • 索引创建
      • 主键索引
      • 唯一索引
      • 普通索引
      • 联合索引
    • 索引的优缺
    • explain查询优化
    • 覆盖索引
  • 四 慢日志管理

python day36 python操作mysql

一 数据库的连接

import pymysql

conn = pymysql.connect('数据库ip','用户','密码','数据库名') #打开数据库连接

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) #返回的值是字典类型
cursor.execute('sql语句')

res = cursor.fetchall() #取出所有的数据,返回列表套字典
res = cursor.fetchone() #取出一条数据,返回字典
res = cursor.fetchmany(5) #自定义获取多少数据,返回列表套字典

print(res)

cursor.close()
conn.close()

sql注入问题

​ 是指通过客户输入到后台的那些能到数据库得到数据的位置上，恶性的输入一些对数据有害的操作。

#解决方法
username = input('请输入用户名:')
password = input('请输入密码:')

sql = 'select * from user where name = %s and password = %s'

cursor.execute(sql,(username,password))

conn.connect = pymysql.connect(host='loaclhost',user='root',password='123',database='test',charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursor.DictCursor) #返回字典

二 对数据库操作

创建表操作

import pymysql

#打开数据库连接
conn = pymysql.connect(
    host= 'localhost',user='root',password='123',database='test',charset='utf8'
)
#使用cursor()创建一个游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

#使用execute()方法执行sql,如果表存在则删除
cursor.execute('drop table if exists user')

sql = 'create table user(
	id int auto_increment primary key,
	name varchar(100),
    password int
)'

cursor.execute(sql)

cursor.close()
conn.close()

操作数据

增加数据

import pymysql

#打开数据库连接
conn = pymysql.connect(
    host= 'localhost',user='root',password='123',database='test',charset='utf8'
)
#使用cursor()创建一个游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#-------------------------------------------------------------
#增加一条数据
sql = 'insert into user(name,password) values (%, %s)'

cursor.execute(sql,('momo','123'))

#添加多条数据
data = [
    ('momo1','123'),
    ('momo2','123'),
    ('momo3','123'),
]

cursor.execute(sql,data)
#-----------------------------------------------------------------
conn.commit()
cursor.close()
conn.close()

修改数据

import pymysql

#打开数据库连接
conn = pymysql.connect(
    host= 'localhost',user='root',password='123',database='test',charset='utf8'
)
#使用cursor()创建一个游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#-------------------------------------------------------------
#修改数据
sql = 'update user name=%s where id=%s'

cursor.execute(sql,('simple',1))
#-----------------------------------------------------------------
conn.commit()
cursor.close()
conn.close()

删除数据

import pymysql

#打开数据库连接
conn = pymysql.connect(
    host= 'localhost',user='root',password='123',database='test',charset='utf8'
)
#使用cursor()创建一个游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#-------------------------------------------------------------
#删除数据
sql = 'delete from user where id=%s'

cursor.execute(sql,1)
#-----------------------------------------------------------------
conn.commit()
cursor.close()
conn.close()

查找数据

fetchall() #取出所有的数据,返回列表套字典
cursor.fetchone() #取出一条数据,返回字典
cursor.fetchmany(5) #自定义获取多少数据,返回列表套字典
rowcount()#只读属性,并返回执行execute()方法后影响的行数

import pymysql

#打开数据库连接
conn = pymysql.connect(
    host= 'localhost',user='root',password='123',database='test',charset='utf8'
)
#使用cursor()创建一个游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#-------------------------------------------------------------
username = input('请输入用户名:')
password = input('请输入密码:')

sql = 'select * from user where username = %s and password = %s'

cursor.execute(sql,(username,password))

res = cursor.fetchall() #取出所有的数据,返回列表套字典

if res:
    print('登录成功!')
else:
    print('登陆失败!')
#-----------------------------------------------------------------
conn.commit()
cursor.close()
conn.close()

三 索引

作用 : 索引的本质是一个特殊的文件,它可以提高数据的查询效率

​ 类似于字典中的目录

索引原理

​ 通过不断地缩小想要获取数据的范围来筛选出最终想要的结果,同事把随机的事件变成顺序的事件,也就是说,有了这个索引机制,可以用同一种查找方式来锁定数据

​ 索引的索引的数据结构:B+树

索引的种类

普通索引 : index :加速查找

唯一索引:

​ 主键索引 : 加速查找 + 不能重复 + 不能为空 primary key

​ 唯一索引 : 加速查找 + 不能重复 unique(name)

联合索引:

​ 联合唯一索引 : unique(name,class)

​ 联合主键索引 : primary key (id,name)

​ 联合普通索引 : index(id,name)

索引创建

主键索引

新增主键索引

create table user( id int auto_increment primary key)charset utf8;

alter table user modify id int auto_increment primary key;

alter table user add primaryky(id);

删除主键索引

alter table user drop primary key;

唯一索引

新增唯一索引

create table user(
	id int auto_increment primary key,
    name varchar(32) not null default '',
    unique u_name(name)
)charset utf8;

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

alter table 表名 add unique index 索引名 (字段名);

删除唯一索引

alter table 表名 drop index 索引名;

普通索引

新增普通索引

create table user(
	id int auto_increment primary key,
    name varchar(32) not null default '',
    index u_name(name)
)charset utf8;

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

alter table 表名 add index 索引名(字段名);

删除索引

alter table 表名 drop index 索引名;

联合索引

新增联合索引

索引名(字段名1,字段名2);

索引的优缺

索引加快了查询速度,但加了索引之后,会占用大量的磁盘空间

索引未命中

​ 并不是创建了索引就一定会加快查询速度,要想利用索引达到提高查询速度的效果,需遵循以下问题

1. 范围问题,或者说条件不明确,条件中如果出现符号或者关键字 : >,>=,<,<=,!=,between...and..,like,大于号,小于号

2. 不能在sql语句中,进行四则运算,不然会降低sql的查询效率

3. 使用函数

   select * from 表名 where 函数(字段名) = '值';

4. 类型不一致

   如果列类型是字符串,传入条件需要加引号,不然索引也不能命中,查询效率低

5. order by #排序条件是索引,则查询字段也必须是索引字段

   当根据索引排序时,select查询的字段如果不是索引,则速度依然很慢

   ​ select 非索引字段名 from 表名 order by 索引字段名 desc;

   attention : 但对主键进行排序,不管查询字段是什么,都可以命中

6. 最左前缀匹配原则

   ​ 对于组合索引mysql会一直向右匹配直到遇到范围查询(>,<,between,like)就停止匹配(指的是范围大了,有索引速度也会变慢)

   ​ 比如 a=1 and b=2 and c=3 and d=4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,abd的顺序可以任意调整

   #如果组合索引为 ix_name_email(name,email)
   where name = 'momo' and email = 'momo@qq.com';  --> 命中索引
   
   where name = 'momo';   --> 命中索引
   
   where email = 'momo@qq.com';  --> 未命中索引
   

   index (a,b,c,d)
   					
   where a=2 and b=3 and c=4 and d=5   --->命中索引
   					
   where a=2 and c=3 and d=4   ----> 未命中索引
   

explain查询优化

explain select * from user where name='momo' and email='momo@qq.com'G
*************************** 1. row ***************************
						   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      #使用到了索引
               
-----------------------------------------------------------------
type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。

结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

possible_keys：列指出MySQL能使用哪个索引在该表中找到行

key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL

覆盖索引

innoDB存储引擎支持覆盖索引(covering index,或称索引覆盖),即从辅助索引中就可以得到查询记录,而不需要查询聚集索引中的记录

优点: 辅助索引不包含整行记录的所有信息,因此其大小要远小于聚集索引,可以减少大量的IO操作

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="D:/mysql-5.7.28/data/myslow.log";
			
set global long_query_time=1;