• 数据库4


    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_keyG
      '''
               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
      
  • 相关阅读:
    为BlueLake主题增加自定义icon图标
    Kettle自定义jar包供javascript使用
    【实战】使用 Kettle 工具将 mysql 数据增量导入到 MongoDB 中
    用心整理 | Spring AOP 干货文章,图文并茂,附带 AOP 示例 ~
    iOS开发——Web基础URLConnection请求
    iOS开篇——UI之UITableView 自定义Cell
    iOS开篇——UI之UITableView的搜索
    iOS开篇——UI之UITableView
    iOS开篇——UI之UIScrollView
    iOS开篇——UI之UIWebView
  • 原文地址:https://www.cnblogs.com/-406454833/p/11789935.html
Copyright © 2020-2023  润新知