python操作mysql、修改表SQL语句补充、视图、触发器、事物、存储过程等

今日学习内容总结

      在上周的学习中，我们已经将mysql的常用查询关键字学习完毕了。并且学会如何使用可视化软件Navicat。而今天的学习内容首先就是通过python操作mysql。

python操作mysql#

前言#

      为什么要使用Python操作mysql，在做自动化的时候，有时候会由于某种原因，使系统上存在很多脏数据，这就需要每次自动化脚本执行结束时，需要人为去数据库中清除数据，每次都手动执行过于麻烦，于是这里就引进Python3 操作mysql数据库的概念。

      python中支持操作MySQL的模块很多，其中最常见的当属'pymysql'。这个模块是第三方的。pymysql是python操纵mysql的一个模块，本质上是一个socket客户端。

安装#

      pip3 install pymysql

基本使用#

      import pymysql

连接数据库#

Copy
  import pymysql

  conn_obj = pymysql.connect(
      host='127.0.0.1',  # MySQL服务端的IP地址
      port=3306,  # MySQL默认PORT地址(端口号)
      user='root',  # 用户名
      password='root',  # 密码  也可以简写 passwd
      database='school_01',  # 库名称  也可以简写 db
      charset='utf8'  # 字符编码 千万不要加杠utf-8
  )

产生获取命令的游标对象#

Copy
  cursor = conn_obj.cursor(
      cursor=pymysql.cursors.DictCursor
  )  # 括号内不写参数 数据是元组要元组 不够精确 添加参数则会将数据处理成字典

数据库查询与使用#

      编写SQL语句

Copy
      sql1 = 'select * from teacher;'  # SQL语句会被高亮显示 不用惊慌

      执行SQL语句

Copy
  affect_rows = cursor.execute(sql1)
  print(affect_rows)  # 执行SQL语句之后受影响的行数

      获取结果

Copy
  res = cursor.fetchall()
  print(res)

  # 运行结果
'''
5
[{'tid': 1, 'tname': '张磊老师'}, {'tid': 2, 'tname': '李平老师'}, {'tid': 3, 'tname': '刘海燕老师'}, {'tid': 4, 'tname': '朱云海老师'}, {'tid': 5, 'tname': '李杰老师'}]

Process finished with exit code 0
'''

补充了解#

      获取SQL语句执行的结果，跟读取文件内容的read方法几乎一致(光标)。

Copy
    cursor.fetchone()   :  获取首行数据

    cursor.fetchmany(n) ： 获取指定数量的数据

    cursor.fetchall()   ： 获取全部数据

    cursor.scroll(1, 'relative')  # 相对于当前位置往后移动一个单位

    cursor.scroll(1, 'absolute')  # 相对于起始位置往后移动一个单位

SQL注入问题#

      实现用户登录，如果用户存在则登录成功（假设该用户已在数据库中）

Copy
    import pymysql

    user = input('请输入用户名：').strip()
    pwd = input('请输入密码：').strip()

    conn_obj = pymysql.connect(
        host='127.0.0.1',  # MySQL服务端的IP地址
        port=3306,  # MySQL默认PORT地址(端口号)
        user='root',  # 用户名
        password='root',  # 密码  也可以简写 passwd
        database='db',  # 库名称  也可以简写 db
        charset='utf8'  # 字符编码 千万不要加杠utf-8
    )

    cursor = conn_obj.cursor(
        cursor=pymysql.cursors.DictCursor
    )  # 括号内不写参数 数据是元组要元组 不够精确 添加参数则会将数据处理成字典

    sql1 = "select * from userinfo where username='%s' and password='%s'" % (user, pwd)
    print('sql语句', sql1)

    affect_rows = cursor.execute(sql1)
    print(affect_rows)  # 执行SQL语句之后受影响的行数

    # 关闭连接，游标和连接都要关闭
    cursor.close()
    conn_obj.close()

    if affect_rows:
        print('登陆成功')
    else:
        print('登录失败')

      演示

Copy
    请输入用户名：jason
    请输入密码：123
    sql语句 select * from userinfo where username='jason' and password='123'
    1
    登陆成功

    Process finished with exit code 0

      sql注入的两种形式

      1.用户名存在绕过密码。也就是说写正确的用户名错误的密码也可以登录。

Copy
    # '--'测试
    请输入用户名：jason' -- jhahsdjasdjasd
    请输入密码：
    sql语句 select * from userinfo where username='jason' -- jhahsdjasdjasd' and password=''
    1
    登陆成功

    Process finished with exit code 0

    # '#'测试
    请输入用户名：jason' # jhahsdjasdjasd
    请输入密码：
    sql语句 select * from userinfo where username='jason' # jhahsdjasdjasd' and password=''
    1
    登陆成功

    Process finished with exit code 0

      2. 用户不存在绕过用户名和密码。也就是说用户名和密码都不需要也可以登录。

Copy
    请输入用户名：xxx' or 1=1 -- asdjasjdkajsd
    请输入密码：
    sql语句 select * from userinfo where username='xxx' or 1=1 -- asdjasjdkajsd' and password=''
    1
    登陆成功

    Process finished with exit code 0
    
  # 注意
    用户名后面有一个单引号
    or前后都有空格
    必须保证等式成立

      上述现象就是典型的SQL注入问题，利用的是MySQL注释语法及逻辑运算符。

解决SQL注入问题#

      解决SQL注入的问题其实也很简单，就是想办法过滤掉特殊符号。

Copy
    #改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了）
    sql="select * from userinfo where name=%s and password=%s" #！！！注意%s需要去掉引号，因为pymysql会自动为我们加上
    affect_rows=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来。

execute方法补充(了解)#

      批量插入数据

Copy
    sql = 'insert into userinfo(name,password) values(%s,%s)'
    cursor.executemany(sql,[('tom',123),('lavin',321),('pony',333)])

二次确认#

Copy
    数据的增删改查四个操作是有轻重之分的
        查                           不会影响真正的数据 重要程度最低
        增、改、删                    都会影响真正的数据 重要程度较高
    pymysql针对增、改、删三个操作 都设置了二次确认 如果不确认则不会真正影响数据库

    方式1:代码直接编写
    	affect_row = cursor.execute(sql)
    	conn_obj.commit()  # 手动二次确认
    方式2:配置固定参数
        conn_obj = pymysql.connect(
        autocommit=True  # 自动二次确认
    )

修改表SQL语句补充#

Copy
    # 1.修改表的名字  rename
    	alter table t1 rename ttt;

    # 2.添加字段		 add
    	alter table ttt add pwd int;  '''默认是尾部追加字段'''
    	alter table ttt add tid int after name;  '''指定追加位置'''
        alter table ttt add nid int first;  '''指定头部添加字段'''

    # 3.修改字段		 change(名字类型都可)/modify(只能改类型不能改名字)
    	alter table ttt change pwd password tinyint;

    # 4.删除字段			 drop
    	alter table ttt drop nid;

视图#

视图的概念#

      MySQL从5.0.1版本开始提供视图功能。

      视图（View）是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成。

视图的作用#

Copy
    1. 简单
        使用视图的用户完全不需要关心后面对应的表的结构、关联条件、和筛选条件。

    2. 安全
        使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，而通过视图可以轻松实现。

    3. 数据独立
        一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，可通过修改视图来解决，不会对访问者造成影响。

视图存在的问题#

Copy
    # 视图虽然看似很好用 但是会造成表的混乱 毕竟视图不是真正的数据源

    # 视图只能用于数据的查询 不能做增、删、改的操作 可能会影响原始数据(视图里面的数据是直接来源于原始表 而不是拷贝一份)

视图的创建#

      创建视图需要有CREATE VIEW的权限，并且对于查询涉及到的列有SELECT权限。如果使用CREATE OR REPLACE，那么哈需要有该视图的DROP权限。

      语法：

Copy
    CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}] VIEW view_name           
    [(column_list)] 
    AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] 

触发器#

触发器的概念#

      触发器是与表有关的数据库对象，可以在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句。触发器的这种特性可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作 。使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器作用#

      专门针对表数据的操作 定制个性化配套功能。

触发器种类#

Copy
	表数据新增之前、新增之后
	表数据修改之前、修改之后
	表数据删除之前、删除之后

触发器创建#

Copy
    DELIMITER $
    CREATE TRIGGER 触发器名称
    BEFORE|AFTER  INSERT|UPDATE|DELETE
    ON 表名
    [FOR EACH ROW]  -- 行级触发器
    BEGIN
      触发器要执行的功能;
    END$
    DELIMITER ;

      触发器的名字一般情况下建议采用下列布局形式:

Copy
	tri_after_insert_t1
	tri_before_update_t2
	tri_before_delete_t3

      DELIMITER $介绍

      临时修改的原因是因为触发器，存储过程等技术点，代码中也需要使用分号。如果不修改，则无法书写出完成的代码。

使用案例#

      先创建两张表

Copy
    CREATE TABLE cmd (
        id INT PRIMARY KEY auto_increment,
        USER CHAR (32),
        priv CHAR (10),
        cmd CHAR (64),
        sub_time datetime, #提交时间
        success enum ('yes', 'no') #0代表执行失败
    );

    CREATE TABLE errlog (
        id INT PRIMARY KEY auto_increment,
        err_cmd CHAR (64),
        err_time datetime
    );

      需求:cmd表插入数据的success如果值为no 则去errlog表中插入一条记录。

Copy
    delimiter $$  # 将mysql默认的结束符由;换成$$
    create trigger tri_after_insert_cmd after insert on cmd for each row
    begin
        if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象
            insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
        end if;
    end $$
    delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了

      只往cmd表中插入数据

Copy
  	INSERT INTO cmd (
          USER,
          priv,
          cmd,
          sub_time,
          success
        )VALUES
            ('kevin','0755','ls -l /etc',NOW(),'yes'),
            ('kevin','0755','cat /etc/passwd',NOW(),'no'),
            ('kevin','0755','useradd xxx',NOW(),'no'),
            ('kevin','0755','ps aux',NOW(),'yes');

      查看触发器

      SHOW TRIGGERS;查看当前库下所有的触发器信息。

      删除触发器

      DROP TRIGGER 触发器名称;删除当前库下指定的触发器信息。

事务#

事务的概念#

      事务是一系列的动作，它们综合在一起才是一个完整的工作单元，这些动作必须全部完成，如果有一个失败的话，那么事务就会回滚到最开始的状态，仿佛什么都没发生过一样。

      数据库事务是保证在并发情况下能够正确执行的重要支撑，MySQL常见的数据库引擎中支持事务的是InnoDB。

      事务就是一系列操作，正确执行并提交，如果中途出现错误就回滚。事务要保证能够正常的执行，就必须要保持ACID特性。

事务的四大特性#

      原子性

      原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

      事务是一个原子操作, 由一系列动作组成。 组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有的操作执行成功，整个事务才提交。

      事务中的任何一个数据库操作失败，已经执行的任何操作都必须被撤销，让数据库返回初始状态。

      一致性

      事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

      一旦所有事务动作完成, 事务就被提交。数据和资源就处于一种满足业务规则的一致性状态，即数据不会被破坏。

      比如a+b=100,一个事务改变了a比如增加了a的值，那么必须同时改变b，保证在事务结束以后a+b=100依然成立，这就是一致性。

      隔离性

      事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

      在并发数据操作时，不同的事务拥有各自的数据空间，它们的操作不会对对方产生干扰。准确地说，并非要求做到完全无干扰。

      数据库规定了多种事务隔离界别，不同的隔离级别对应不用的干扰程度。隔离级别越高，数据一致性越好，但并发行越弱。比如对于A对B进行转账，A没把这个交易完成的时候，B是不知道A要给他转钱。

      持久性

      持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

事务的传播属性#

      即当前的事务方法被另外一个事务方法调用时如何使用事务，默认取值为required，即使用调用方法的事务。

事务传播行为类型	说明
REQUIRED	如果有事务在运行，当前的方法就在这个事务内运行；否则，就启动一个新的事务，并在自己的事务内运行；
REQUIRES_NEW	当前的方法必须启动新事务，并在它自己的事务内运行；如果有事务正在运行，应该将它挂起。
MANDATORY	当前的方法必须运行在事务内部，如果没有正在运行的事务，将抛出异常。
SUPPORTS	如果有事务在运行，当前的方法就在这个事务内运行；否则它可以不运行在事务中。
NOT_SUPPORTED	当前的方法不应该运行在事务中，如果有运行的事务，将它挂起。
NEVER	当前的方法不应该运行在事务中，如果有运行的事务，就抛出异常。
NESTED	如果有事务在运行，当前的方法就应该在这个事务的嵌套事务内运行。否则，就启动一个新的事务，并在它自己的事务内运行。

      其中，最常使用的是 REQUIRED 、REQUIRES_NEW。

具体使用#

      创建数据

Copy
    create table user(
      id int primary key auto_increment,
      name char(32),
      balance int
      );
    insert into user(name,balance)
      values
      ('jason',1000),
      ('kevin',1000),
      ('tank',1000);

      创建事务

Copy
    开启一个事务的操作
    start transaction;
    编写SQL语句(同属于一个事务)
    update user set balance=90 where name=‘gg’;
    update user set balance=10 where name=‘vv’;
    update user set balance=1 where name=‘aa’;
    事务回滚(返回执行事务操作之前的数据库状态)
    rollback; # 执行完回滚之后 事务自动结束
    事务确认(执行完事务的主动操作之后 确认无误之后 需要执行确认命令)
    commit; # 执行完确认提交之后 无法回滚 事务自动结束

存储过程#

      存储过程中可以将多个sql语句组合起来，可以使用in创建接收变量，out创建返回变量。

存储过程的创建#

Copy
    类型1：不带参数
    delimiter $ $
    create procedure p1()
    begin
    sql语句
    end $ $
    delimiter ;

    类型2：带参数
    delimiter $ $
    create procedure p1(in a int, out b int)
    begin
    select num as b from t1 where id = a;
    end $ $
    delimiter ;

存储过程的使用#

      call 存储过程名。如果有in的变量需要在存储过程名后的（）中写入。

Copy
    call p1(1,2)

      有out的需要先定义在用来接收返回

Copy
    set @res;  定义
    call p1(@res)
    select @res  查看

      查看存储过程具体信息

Copy
    show create procedure pro1;

      查看所有存储过程

Copy
    show procedure status;

      删除存储过程

Copy
    drop procedure pro1;

函数#

      使用需要注意与存储过程的区别，mysql内置的函数只能在sql语句中使用！可以通过help 函数名，查看帮助信息!

      1. 移除指定字符

Copy
    Trim、LTrim、RTrim

      2. 大小写转换

Copy
    Lower、Upper

      3. 获取左右起始指定个数字符

Copy
    Left、Right

      4.返回读音相似值(对英文效果)

Copy
    Soundex

      5. 日期格式:date_format

Copy
    1.where Date(sub_time) = ‘2015-03-01’ # 年月日

    2.where Year(sub_time)=2016 AND Month(sub_time)=07; # year 年 month 月

      更多日期处理相关函数

Copy
	adddate    	增加一个日期 
	addtime	        增加一个时间
	datediff	计算两个日期差值

流程控制#

if判断#

Copy
    if 条件 then
            子代码
    elseif 条件 then
            子代码
    else
            子代码
    end if;

while 循环#

Copy
    DECLARE num INT ;
      SET num = 0 ;
    WHILE num < 10 DO
      SELECT num ;
      SET num = num + 1 ;
    END WHILE ;

索引#

      索引就是一种数据结构，创建索引在查找数据时查询速度会变快，相对的增改删速度会变慢，因为每次增改删都会重建索引。

      索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构：

Copy
    1. primary key 主键

    2. unique key 唯一键

    3. index key 索引键

      上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，而index key没有任何约束功能只会帮你加速查询

索引的优劣势#

      优势

Copy
    1. 检索:可以提高数据检索的效率，降低数据库的IO成本

    2. 排序:通过索引列对数据进行排序，降低了CPU的消耗

      劣势

Copy
    1. 占磁盘空间

    2. 降低更新表的效率

索引的基本使用#

      单列索引之普通索引

Copy
    create index index_name on table(column(length));
    alter table table_name add index index_name(column(length));

      单列索引之唯一索引

Copy
    create unique index index_name on table (column(lnegth));
    alter table table_name add unique index index_name(column);

      单列索引之全文索引

Copy
    create fulltext index index_name on table(column(length));
    alter table table_name add fulltext index_name(column);

      组合索引

Copy
    alter table table_name add index index_name_(title(50),time(10),......);

      删除索引

Copy
    drop index index_name on table;

      查看索引

Copy
    show index from table_name

索引的存储结构#

Copy
    1. 索引在存储引擎中实现(不同的引擎会只用不同的索引)

    2. MyISAM和InnoDB存储引擎:只支持B+tree索引

    3. MEMORY/HEAP存储引擎:支持HSAH和BTREE索引

      索引的底层数据结构是b+树。b树，红黑树，二叉树，b*树 b+树等结构是为了更好的基于树查找到相应的数据。

聚集索引(primary key)#

Copy
    1. 主键索引(聚集索引)的叶子结点会存储数据行，也就是说数据和索引在一起

    2. 辅助索引只会存储主键值

辅助索引(unique key,index key)#

      查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引。

覆盖索引#

      在辅助索引的叶子节点中就已经找到了所有我们想要的数据。

Copy
    select name from user where name='jason';

非覆盖索引#

      虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找。

Copy
    select age from user where name='jason';