当前位置：Gxlcms > 数据库问题 > MySQL 04

MySQL 04

时间：2021-07-01 10:21:17 帮助过：15人阅读

目录

  • python操作mysql
    • mysql
    • sql注入问题
      • 问题描述
      • 解决办法
    • 增/删/改
    • 查询
  • 索引
    • 基本概念
    • 索引的原理
    • mysql索引种类
      • 普通索引
      • 唯一索引
      • 联合索引
    • 创建索引
    • 正确使用索引
      • 索引未命中
      • 最左前缀
      • 索引覆盖
      • explain
  • 慢查询日志
    • 基本概念
    • 配置

python操作mysql

mysql

  1. <code>'''
  2. mysql> select * from user_info;
  3. +----+------+----------+
  4. | id | name | password |
  5. +----+------+----------+
  6. |  1 | bigb | 123456   |
  7. +----+------+----------+
  8. 1 row in set (0.00 sec)
  9. '''
  10. import pymysql
  11. user_name = input('请输入用户名: ').strip()
  12. password = input('请输入密码: ').strip()
  13. # 连接数据库
  14. conn = pymysql.connect(
  15.     host='localhost',
  16.     user='root',
  17.     password='123',
  18.     database='db3',
  19.     charset='utf8'
  20. )
  21. # 游标
  22. cursor = conn.cursor()  # 默认以元祖返回
  23. # cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 以字典返回
  24. # 拼接sql语句
  25. sql = "select * from user_info where name = '%s' and password = '%s'" % (user_name, password)
  26. print(sql)
  27. # 执行sql语句
  28. res = cursor.execute(sql)  # 返回执行sql语句影响成功的记录条数
  29. print(res)
  30. cursor.close()
  31. conn.close()
  32. if res:
  33.     print('登录成功!')
  34. else:
  35.     print('登录失败!')
  36.     
  37. '''
  38. 请输入用户名: bigb
  39. 请输入密码: 123456
  40. select * from user_info where name = 'bigb' and password = '123456'
  41. 1
  42. 登录成功!
  43. '''</code>

sql注入问题

问题描述

  • 对于上面的登录脚本, 如果我们在输入用户名时, 在用户名在后面加上‘ #, 这样即使密码错误也能登录成功 (绕过密码)
  1. <code>请输入用户名: bigb' #
  2. 请输入密码: 234235
  3. select * from user_info where name = 'bigb' #' and password = '234235'
  4. 1
  5. 登录成功!</code>
  • 甚至用户名输错也能登录成功 (绕过用户名和密码)
  1. <code>请输入用户名: blake' or 1=1 #
  2. 请输入密码: 4647723
  3. select * from user_info where name = 'blake' or 1=1 #' and password = '4647723'
  4. 1
  5. 登录成功!
  6. </code>
  • 上面描述的就是sql注入问题, 原因是我们可以通过输入特殊符号改变where后的筛选条件 (#可以让后面的内容变成注释)

解决办法

  • pymysql对sql针对注入问题进行了优化, 我们将sql语句的拼接工作交给cursor.execute(sql, (user_name, password))即可
  1. <code>import pymysql
  2. user_name = input('请输入用户名: ').strip()
  3. password = input('请输入密码: ').strip()
  4. # 连接数据库
  5. conn = pymysql.connect(
  6.     host='localhost',
  7.     user='root',
  8.     password='123',
  9.     database='db3',
  10.     charset='utf8'
  11. )
  12. # 游标
  13. cursor = conn.cursor()  # 默认以元祖返回
  14. # cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 以字典返回
  15. # sql语句
  16. sql = "select * from user_info where name = %s and password = %s"
  17. print(sql)
  18. # 拼接并执行sql语句
  19. res = cursor.execute(sql, (user_name, password))  
  20. print(res)
  21. cursor.close()
  22. conn.close()
  23. if res:
  24.     print('登录成功!')
  25. else:
  26.     print('登录失败!')
  27. </code>

增/删/改

  • 在通过mysql进行增删改的时候, 要在最后加上conn.commit()提交
  1. <code>'''
  2. mysql> select * from user_info;
  3. +----+-------+----------+
  4. | id | name  | password |
  5. +----+-------+----------+
  6. |  1 | bigb  | 123456   |
  7. |  3 | alpha | 111111   |
  8. +----+-------+----------+
  9. 2 rows in set (0.00 sec)
  10. '''
  11. import pymysql
  12. # 连接数据库
  13. conn = pymysql.connect(
  14.     host='localhost',
  15.     user='root',
  16.     password='123',
  17.     database='db3',
  18.     charset='utf8'
  19. )
  20. # 游标
  21. cursor = conn.cursor()
  22. # 增加
  23. sql = "insert into user_info (name, password) values ('%s', '%s')" % ('blake', '654321')
  24. print(sql)
  25. res = cursor.execute(sql)
  26. print(res)
  27. # 修改
  28. sql = "update user_info set password='%s' where name='%s'" % ('222222', 'bigb')
  29. print(sql)
  30. res = cursor.execute(sql)
  31. print(res)
  32. # 删除
  33. sql = "delete from user_info where name='%s' " % ('alpha')
  34. print(sql)
  35. res = cursor.execute(sql)
  36. print(res)
  37. conn.commit()
  38. cursor.close()
  39. conn.close()
  40. '''
  41. mysql> select * from user_info;
  42. +----+-------+----------+
  43. | id | name  | password |
  44. +----+-------+----------+
  45. |  1 | bigb  | 222222   |
  46. |  5 | blake | 654321   |
  47. +----+-------+----------+
  48. 2 rows in set (0.00 sec)
  49. '''</code>

查询

  • cursor.fetchone() 返回一条记录
  • cursor.fetchmany(n) 返回n条记录
  • cursor.fetchall() 返回所有记录
  • 注意 fetch会记录光标位置, fetchone()和fetchmany()会使光标向后移动相应条数, fetchall()直接将光标移动到末尾
  1. <code>'''
  2. +----+---------+----------+
  3. | id | name    | password |
  4. +----+---------+----------+
  5. |  1 | bigb    | 111111   |
  6. |  2 | blake   | 222222   |
  7. |  3 | black   | 333333   |
  8. |  4 | alpha   | 111111   |
  9. |  5 | bravo   | 222222   |
  10. |  6 | charlie | 333333   |
  11. |  7 | delta   | 111111   |
  12. |  8 | echo    | 222222   |
  13. |  9 | foxtrot | 333333   |
  14. +----+---------+----------+
  15. '''
  16. import pymysql
  17. # 连接
  18. conn = pymysql.connect(
  19.     host='localhost',
  20.     user='root',
  21.     password='123',
  22.     database='db3',
  23.     charset='utf8'
  24. )
  25. # 游标
  26. cursor = conn.cursor()
  27. sql = "select * from user_info"
  28. # 执行sql语句
  29. rows = cursor.execute(sql)
  30. print(rows)
  31. res1 = cursor.fetchone()
  32. res2 = cursor.fetchone()
  33. res3 = cursor.fetchone()
  34. res4 = cursor.fetchmany(3)
  35. res5 = cursor.fetchall()
  36. print(res1)
  37. print(res2)
  38. print(res3)
  39. print(res4)
  40. print(res5)
  41. '''
  42. 9
  43. (1, 'bigb', '111111')
  44. (2, 'blake', '222222')
  45. (3, 'black', '333333')
  46. ((4, 'alpha', '111111'), (5, 'bravo', '222222'), (6, 'charlie', '333333'))
  47. ((7, 'delta', '111111'), (8, 'echo', '222222'), (9, 'foxtrot', '333333'))
  48. '''</code>

索引

基本概念

  • 索引相当于字典的音序表, 如果查某个字, 可以根据音序表快速定位这个字在字典中的位置, 如果不使用音序表则要一页一页查找
  • 优点: 索引可以提升查询数据的速度
  • 缺点: 增加索引会消耗很多内存资源

索引的原理

  • 原理: 不断缩小搜索范围, 把随机事件变顺序事件

  • 底层数据结构: B+树

mysql索引种类

普通索引

  • index 加速查找

唯一索引

  • primary key 主键唯一索引, 加速查找, 不为空, 不重复
  • unique 普通唯一索引, 加速查找, 不重复

联合索引

  • primary key (id, name) 联合主键索引
  • unique (id, name) 联合唯一索引
  • index (id, name) 联合普通索引

创建索引

  • 在创建表时添加
  1. <code>creat table 表名 ( 
  2. 列名1 列类型 [列约束],
  3. 列名2 列类型 [列约束],
  4. index/primary key/unique [索引名] (列名1)
  5. );
  6. mysql> create table user (
  7.     -> id int auto_increment,
  8.     -> name char(10) not null default '',
  9.     -> email char(20) not null default '',
  10.     -> primary key pk_id (id)
  11.     -> );
  12. Query OK, 0 rows affected (0.05 sec)</code>
  • 在已创建的表上添加
  1. <code>alter table 表名 add index/unique index [索引名] (列名)
  2. create index/unique index [索引名] on 表名(列名)  </code>
  • 删除索引
  1. <code>alter table 表名 drop 索引名
  2. drop index 索引名 on 表名</code>

正确使用索引

索引未命中

  • 进行四则运算
  • 使用函数
  • 条件不明确 :> >= < <= != between...and... like

最左前缀

  • 基于联合索引的情况下
  • 假设我们创建index（clo1, clo2, clo3）这样的一个联合索引, 相当于 (clo1), (clo1, clo2), (clo1, clo2, clo3) 三非索引

索引覆盖

  • 在查询的时候，利用到的索引已经完全包含需要查询数据，在这种情况下，查询结果直接就是索引的值，并不需要再利用索引回表查询了
  • 如 select id from user where id = ‘88888‘

explain

  • 可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的

  • explain sql语句

技术图片

慢查询日志

基本概念

  • MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句

  • show varibles like %slow%; 查看当前日志状态 (是否开启, 日志文件保存位置)

  1. <code>mysql> show variables like '%slow%';
  2. +---------------------------+-----------------------------------------------------+
  3. | Variable_name             | Value                                               |
  4. +---------------------------+-----------------------------------------------------+
  5. | log_slow_admin_statements | OFF                                                 |
  6. | log_slow_slave_statements | OFF                                                 |
  7. | slow_launch_time          | 2                                                   |
  8. | slow_query_log            | OFF                                                 |
  9. | slow_query_log_file       | D:\MySQL\mysql-5.6.46-winx64\data\Black-PC-slow.log |
  10. +---------------------------+-----------------------------------------------------+</code>
  • show varibles like %long%; 查看慢查询设定的时间(10秒)
  1. <code>mysql> show variables like '%long%';
  2. +--------------------------------------------------------+-----------+
  3. | Variable_name                                          | Value     |
  4. +--------------------------------------------------------+-----------+
  5. | long_query_time                                        | 10.000000 |
  6. | performance_schema_events_stages_history_long_size     | 10000     |
  7. | performance_schema_events_statements_history_long_size | 10000     |
  8. | performance_schema_events_waits_history_long_size      | 10000     |
  9. +--------------------------------------------------------+-----------+
  10. 4 rows in set (0.00 sec)</code>

配置

  • set global 变量名 = 值
  1. <code>mysql> set global slow_query_log = on;
  2. mysql> set global slow_query_log_file = "D:/MySQL/myslow.log";
  3. mysql> set global long_query_time = 1;</code>

MySQL 04

标签：wait   lte   char   upd   alt   范围   前缀   查看   statement   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！