目录

python操作mysql

  • pip install pymysql

    import pymysql

# 连接数据库的参数
connect = pymysql.connect(
    host='localhost', user='root', password='Cql123456',
    database='test1', charset='utf8'
)

cursor = connect.cursor(cursor=pymysql.cursors.DictCursor)  # 参数控制查询的返回值是字典类型

sql = 'select * from boy_to_girl'

cursor.execute(sql)  # 执行查询sql执行

# print(cursor.fetchone())  # {'id': 1, 'bid': 1, 'gid': 1}, 返回值是字典, 多次读取时需注意光标位置

# print(cursor.fetchall())  # 获取所有查询结果, 返回值是列表套字典, 多次读取时需注意光标位置

print(cursor.fetchmany(3))  # 参数控制获取多少条数据, 返回值是列表套字典, 多次读取时需注意光标位置
# [{'id': 1, 'bid': 1, 'gid': 1}, {'id': 2, 'bid': 1, 'gid': 3}, {'id': 3, 'bid': 1, 'gid': 7}]

# 关闭句柄释放内存空间
cursor.close()
connect.close()

pymysql中sql指令注入问题

  • 产生原因:

    • 没有对用户输入的指令做检验
    • 后面的代码相当于注释
    import pymysql

connect = pymysql.connect(
    host='localhost', user='root', password='Cql123456',
    database='test1', charset='utf8'
)
cursor = connect.cursor(cursor=pymysql.cursors.DictCursor)

name = input('请输入用户名:')
pwd = input('请输入用户密码:')

sql = "select * from nick where name = '%s' and pwd = '%s'" % (name, pwd)
print(sql)

cursor.execute(sql)

print(cursor.fetchall())

# 关闭句柄释放内存空间
cursor.close()
connect.close()

'''
请输入用户名:tank'#
请输入用户密码:456
select * from nick where name = 'tank'#' and pwd = '456'
[{'id': 1, 'name': 'tank', 'pwd': 'tank123'}]
'''

  • 解决方法: 将用户输入的指令和包含%占位符的sql查询语句一起传入cursor.execute方法中, 该方法会自动对用户输入的指令进行检测及相关的转义处理

    import pymysql

connect = pymysql.connect(
    host='localhost', user='root', password='Cql123456',
    database='test1', charset='utf8'
)
cursor = connect.cursor(cursor=pymysql.cursors.DictCursor)

name = input('请输入用户名:')
pwd = input('请输入用户密码:')

sql = "select * from nick where name = %s and pwd = %s"  # 注意%s不加引号
print(sql)

cursor.execute(sql, (name, pwd))

print(cursor.fetchall())

# 关闭句柄释放内存空间
cursor.close()
connect.close()

'''
请输入用户名:tank
请输入用户密码:tank123
select * from nick where name = %s and pwd = %s
[{'id': 1, 'name': 'tank', 'pwd': 'tank123'}]
'''

其他操作---增, 改, 删

  • 增加记录

    import pymysql

connect = pymysql.connect(
    host='localhost', user='root', password='Cql123456',
    database='test1', charset='utf8'
)
cursor = connect.cursor(cursor=pymysql.cursors.DictCursor)

# 一条记录的信息
# name = input('请输入用户名:')
# pwd = input('请输入用户密码:')

# 多条记录的信息
data = [
    ('jason1', 'pwd1'),
    ('jason2', 'pwd2'),
    ('jason3', 'pwd3')
]

sql = "insert into nick (name, pwd) values (%s, %s)"
print(sql)  # insert into nick (name, pwd) values (%s, %s)

# cursor.execute(sql, (name, pwd))  # 增加一条记录的语句, execute
cursor.executemany(sql, data)  # 增加多条记录的语句, executemany

connect.commit()  # 交付结果至硬盘

# 关闭句柄释放内存空间
cursor.close()
connect.close()

  • 修改一条记录

    import pymysql

connect = pymysql.connect(
    host='localhost', user='root', password='Cql123456',
    database='test1', charset='utf8'
)
cursor = connect.cursor(cursor=pymysql.cursors.DictCursor)

sql = "update nick set name = %s, pwd = %s where id = 4"
print(sql)  # update nick set name = %s, pwd = %s where id = 4

cursor.execute(sql, ('dragon', 'cql123456'))

connect.commit()  # 交付结果至硬盘

# 关闭句柄释放内存空间
cursor.close()
connect.close()

  • 删除一条数据

    import pymysql

connect = pymysql.connect(
    host='localhost', user='root', password='Cql123456',
    database='test1', charset='utf8'
)
cursor = connect.cursor(cursor=pymysql.cursors.DictCursor)

sql = "delete from nick where name = %s"
print(sql)  # delete from nick where name = %s

cursor.execute(sql, ('tank',))

connect.commit()  # 交付结果至硬盘

# 关闭句柄释放内存空间
cursor.close()
connect.close()

索引

  1. key包含两层意义和作用:
    • 约束(规范数据库的结构完整性)---constrant
    • 索引(辅助查询)
  2. 索引处于实现层面, 不会约束被索引的字段的行为---index
  • 作用: 提高查询效率
  • 类比: 字典中的目录
  • 本质: 一个特殊的文件
  • 底层原理: B+树

索引的种类

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

  • 唯一索引: 加速查找 + 不能重复

    联合唯一索引

  • 普通索引: 加速查找

    联合索引

主键索引

# 新增主键索引方式一: 在表创建之后增加
alter table table_name 
add primary key (id);

# 新增主键索引方式二: 在创建表时增加
create table table_name (
    id int auto_increment,
    primary key (id)
    ) charset utf8;
    
# 删除主键索引, 如果主键自增, 需先删除自增约束
alter table table_name 
drop primary key;

唯一索引

select count(real_name) from test_primary_key;
+------------------+
| count(real_name) |
+------------------+
|          4000001 |
+------------------+
1 row in set (2.26 sec)  # 未添加唯一索引前查询时间

# 添加唯一索引的方式一: 在表创建之后添加
alter table test_primary_key
add  constraint uqe_real_name unique index (real_name);

select count(real_name) from test_primary_key;
+------------------+
| count(real_name) |
+------------------+
|          4000001 |
+------------------+
1 row in set (0.77 sec)  # 添加唯一索引后的查询时间

# 添加唯一索引的方式二: 在创建表时添加
create table unique_index (
    id int auto_increment primary key,
    name varchar(32),
    constraint uqe_name unique key (name)
    ) charset utf8;
# 此时unique key, unique index, unique三者同义

# 删除唯一索引, 使用关键字index
alter table test_primary_key
drop index uqe_real_name;

普通索引

# 添加普通索引的方式一: 在表创建之后添加
alter table test_primary_key
add index ix_real_name (real_name);  # 声明索引名及被添加普通索引的字段

# 添加普通索引的方式二: 在表创建时添加
create table ordinary_index (
    id int auto_increment primary key,
    name varchar(32),
    index ix_name (name) 
    ) charset utf8;

# 删除普通索引
alter table test_primary_key
drop index ix_real_name;

索引的优缺点: 优点是加快了查询速度, 但是通过观察 *.ibd 文件可知加索引后会占用大量磁盘空间

索引不会命中的情况

  • 索引并不是越多越好, 有些sql语句会使索引不命中

  1. 不能在where语句的条件中进行四则运算, 会降低sql的查询效率

    • select count(id) from test_primary_key where id*2 = 1000;

  2. 使用函数:

    • select * from t where reverse(email) = "zekai";

  3. 类型不一致: 如果字段类型是字符串类型, sql查询时where语句的条件判断也需要是字符串类型, 此外where语句的条件的范围大或者模糊也会降低搜索效率

    • select * from t where email = 999;

  4. 排序条件为添加了索引的字段, 则select查询的字段也必须是添加了索引的字段, 否则无法命中

    • select name from t order by email desc;

  5. count(列)代替count(*)

  6. 组合(联合)索引最左前缀

    # index (a, b, c)
# where a = 2 ...  # 命中
# where (b...) 逻辑连接词 (c...)  # 未中

  7. explain获取查询报告

    explain select count(real_name) from test_primary_key\G
'''
         id: 1
  select_type: SIMPLE  # 索引的类型, all表示可能全表扫表
        table: test_primary_key
   partitions: NULL  # 分区
         type: index  # 索引类型
possible_keys: NULL  # 可能用到的索引
          key: ix_real_name  # 确实用到的所有
      key_len: 98  # 索引的长度
          ref: NULL
         rows: 3992630  # 扫描长度
     filtered: 100.00
        Extra: Using index  # 使用到了索引
1 row in set, 1 warning (0.00 sec)
'''

  8. 索引覆盖: select from test_primary_key where id = 2000;

慢查询日志

  • 查看慢sql的相关变量: show variables like "%slow%"

    +---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| log_slow_admin_statements | OFF   |
| log_slow_slave_statements | OFF   |
| slow_launch_time          | 2     |
| slow_query_log            | OFF   |  # 默认关闭记录慢sql查询日志, on为开启
| slow_query_log_file       | G:\   |  # 慢sql日志的记录位置
+---------------------------+-------+

  • 查看能接受的最长查询时间变量: show variables like "%long%"

    +----------------------------------------------------------+-----------+
| Variable_name                                            | Value     |
+----------------------------------------------------------+-----------+
| long_query_time                                          | 10.000000 |
| performance_schema_events_stages_history_long_size       | 10000     |
| performance_schema_events_statements_history_long_size   | 10000     |
| performance_schema_events_transactions_history_long_size | 10000     |
| performance_schema_events_waits_history_long_size        | 10000     |
+----------------------------------------------------------+-----------+
# long_query_time  能接受的最长查询时间变量

  • 配置慢sql日志变量: set global 变量名 = 变量值

    set global slow_query_log = on;

set global slow_query_log_file = "G:/软件压缩包/mysql-5.7.28/slow_sql_log";  # window中文件路径使用 / 分隔

set global long_query_time = 1;

select count(real_name) from test_primary_key;  # (3.23 sec), 配置好后需先退出客户端重连再查询, 则会记录慢sql