MySQL收尾

python操作MySQL

# 创建链接对象
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='1',
    database='db4_3',
    charset='utf8',
    autocommit=True  # 自动二次确认
)
# 生成游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 括号内不写参数是元祖组织不够明确
# 定义sql语句
sql = "select * from 表名 where 字段名=%s"
# 执行sql语句
"""
这里说一个sql注入的问题， 
	在查询的时候，可以利用一些特殊符号的组合绕过相应的机制
	所以把数据交给execute自动帮我们拼接，过滤
"""
cursor.execute(sql, 值)  # 返回值是执行sql语句影响的行数
res = cursor.fetchall()  # 拿到所有返回的结果
res = cursor.fetchone()  # 只拿到一个

视图

• 什么是视图

  视图就是通过查询拼接起来一张虚拟表，然后保存下来，下次继续使用
  

• 如何用视图

  create view student_class as select * from student inner join class on student.class_id = class.cid;
  
  """
  视图只有表结构，没有表数据文件
  视图只用来查询
  """
  drop view 视图名
  
  在开发过程中，通常不会使用视图
  

触发器

• 什么是触发器

  在满足对某张表数据的增删改的情况下，自动触发的的功能称之为触发器
  
  即，我们对某一张表做增删改的时候，就会触发触发器的执行，即自动运行另外一段sql代码
  

• 怎么用触发器

  """
  需要注意 在书写sql代码的时候结束符是; 而整个触发器的结束也需要分号;
  这就会出现语法冲突 需要我们临时修改结束符号
  delimiter $$
  delimiter ; 
  该语法只在当前窗口有效  
  """
  
  触发器的语法结构
  
  """
  delimiter $$
  create trigger 触发器名 before/after insert/update/delete on 表名 for each row
  begin
  	sql语句
  end
  """
  
  
  eg:
              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
          );
  
          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中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
          INSERT INTO cmd (
              USER,
              priv,
              cmd,
              sub_time,
              success
          )
          VALUES
              ('egon','0755','ls -l /etc',NOW(),'yes'),
              ('egon','0755','cat /etc/passwd',NOW(),'no'),
              ('egon','0755','useradd xxx',NOW(),'no'),
              ('egon','0755','ps aux',NOW(),'yes');
  
          # 查询errlog表记录
          select * from errlog;
          # 删除触发器
          drop trigger tri_after_insert_cmd;
  

存储过程

• 什么是存储过程

  存储过程包含了一系列可执行的sql语句， 通过调用它的名字可以执行其内部的一堆sql类似于python中的自定义函数
  

• 怎么用

  delimiter $$
  create procedure p1()
  begin
  	select * from user;
  end $$
  delimiter ;
  
  # 调用
  call p1()
  
  # 创建带有参数的存储过程
  delimiter $$
  create procedure p2(
      in m int,  # in表示这个参数必须只能是传入不能被返回出去
      in n int,  
      out res int  # out表示这个参数可以被返回出去
  )
  begin
      select tname from teacher where tid > m and tid < n;
      set res=0;  # 用来标志存储过程是否执行
  end $$
  delimiter ;
  
  # 在MySQL中调用存储过程
  set @res=10  # res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储10
  call p1(2,4,10);  # 报错
  call p1(2,4,@res);  
  # 查看结果
  select @res;  # 执行成功，@res变量值发生了变化
  
  # 在python中调用存储过程
  pymysql链接mysql
  产生的游表cursor.callproc('p1',(2,4,10))  # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;
  cursor.excute('select @_p1_2;')
  

• 定义变量

set @res=10;  定义
select @res;  查看
select @res  查看

事务

• 什么是事务

  开启一个事务可以包含一些sql语句，这些sql语句要么同时成功
  要么一个都别想成功
  

• 事务的作用

  保证了对数据操作的安全性
  

• 事务的四个属性(ACID)

  原子性
  	"""
  	一个事务是一个不可分割的单位，事务中包括的操作要么都做，要么都不做
  	"""
  一致性
  	"""
  	事务必须是使数据库从一个一致性状态变到另外一个一致性状态
  	"""
  隔离性
  	"""
  	一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
  	"""
  持久性
  	"""
  	一个事务一旦提交，对数据库中数据的改变就应该是永久性的
  	"""
  

• 怎么用

  eg:
      create table user(
      id int primary key auto_increment,
      name char(32),
      balance int
      );
  
      insert into user(name,balance)
      values
      ('jason',1000),
      ('egon',1000),
      ('tank',1000);
  
      # 修改数据之前先开启事务操作
      start transaction;
  
      # 修改操作
      update user set balance=900 where name='jason'; #买支付100元
      update user set balance=1010 where name='egon'; #中介拿走10元
      update user set balance=1090 where name='tank'; #卖家拿到90元
  
      # 回滚到上一个状态
      rollback;
  
      # 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
      commit;
      """开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""
  
      # 站在python代码的角度，应该实现的伪代码逻辑，
      try:
          update user set balance=900 where name='jason'; #买支付100元
          update user set balance=1010 where name='egon'; #中介拿走10元
          update user set balance=1090 where name='tank'; #卖家拿到90元
      except 异常:
          rollback;
      else:
          commit;
  

内置函数

参考博客
	http://www.cnblogs.com/linhaifeng/articles/7495918.html#_label2
        
 eg：
	        CREATE TABLE blog (
            id INT PRIMARY KEY auto_increment,
            NAME CHAR (32),
            sub_time datetime
        );

    		INSERT INTO blog (NAME, sub_time)
            VALUES
                ('第1篇','2015-03-01 11:31:21'),
                ('第2篇','2015-03-11 16:31:21'),
                ('第3篇','2016-07-01 10:21:31'),
                ('第4篇','2016-07-22 09:23:21'),
                ('第5篇','2016-07-23 10:11:11'),
                ('第6篇','2016-07-25 11:21:31'),
                ('第7篇','2017-03-01 15:33:21'),
                ('第8篇','2017-03-01 17:32:21'),
                ('第9篇','2017-03-01 18:31:21');

mysql> select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
+-------------------------------+-----------+
| date_format(sub_time,'%Y-%m') | count(id) |
+-------------------------------+-----------+
| 2015-03                       |         2 |
| 2016-07                       |         4 |
| 2017-03                       |         3 |
+-------------------------------+-----------+
3 rows in set (0.01 sec)

流程控制

• if

  delimiter //
  CREATE PROCEDURE proc_if ()
  BEGIN
      
      declare i int default 0;
      if i = 1 THEN
          SELECT 1;
      ELSEIF i = 2 THEN
          SELECT 2;
      ELSE
          SELECT 7;
      END IF;
  
  END //
  delimiter ;
  

• while

  delimiter //
  CREATE PROCEDURE proc_while ()
  BEGIN
  
      DECLARE num INT ;
      SET num = 0 ;
      WHILE num < 10 DO
          SELECT
              num ;
          SET num = num + 1 ;
      END WHILE ;
  
  END //
  delimiter ;
  

索引

• 什么是索引

  数据都是存在硬盘上的，查询数据不可避免的需要进行IO操作
  索引就是一种数据结构，类似于书的目录，意味着以后查数据先去查目录再去查数据
  索引在Mysql中也叫键，是存储引擎用于快速查找记录的一种数据结构
  

• 索引有哪些

  primary key  主键索引    非空且唯一
  unique key   唯一索引    唯一
  index key    普通索引    没有任何约束，只会帮你加快查询
  
  
  本质：
  	"""
  	通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
  	"""
      
  影响：
  	"""
  	在表中有大量数据的前提下，创建索引速度会很慢
  	在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低
  	"""
  

• b+树

  <https://www.www.zyiz.net/i/i/?n=17&i=blog/1036857/201709/1036857-20170912011123500-158121126.png>
  
  只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据
  
  查询次数由树的层级决定，层级越低次数越少
  
  一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？一个磁盘块儿存放占用空间比较小的数据项
  
  思考我们应该给我们一张表里面的什么字段字段建立索引能够降低树的层级高度>>> 主键id字段
  

• 聚集索引

  聚集索引其实指的就是表的主键，innodb引擎规定一张表中必须要有主键。先来回顾一下存储引擎。
  
  myisam在建表的时候对应到硬盘有几个文件(三个)？
  
  innodb在建表的时候对应到硬盘有几个文件(两个)？frm文件只存放表结构，不可能放索引，也就意味着innodb的索引跟数据都放在idb表数据文件中。
  
  # 特点:叶子结点放的一条条完整的记录
  

• 辅助索引

  辅助索引:查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引
  
  # 特点：叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})