本文已参与「新人创作礼」活动，一起开启掘金创作之路。

每日测验(day49)

• 默写pymysql模块操作MySQL的代码及主要方法

• 什么是sql注入，如何避免

  (ps:sql注入有很多种类型，我们只是介绍了其中最具代表性的一种来诠释，感兴趣的可以自己再去百度查看完整的sql注入种类及解决方式)

昨日内容回顾

• Navicat软件的使用

  """
  作为开发 我们使用软件的频率较高 一般不用直接书写sql语句
  快速完成库 表的创建 等等...
  """
  

• 数据库练习题

  """
  1 面对复杂的查询题目的时候 不要捉急 sql语句不要想着一口气写完 分步书写
  2 只要是涉及到多表查询的时候 肯定用到联表操作和子查询
  	联表操作 你可以无限制的联N多张表
  		将两张表的结果起别名 变成了一张表
  		之后再去另外一张表拼接再起别名
  		再去拼接...
  3 一定要把昨天上课将的练习题自己能够理解并敲出来
  """
  

• pymysql模块

  # 直接安装 无需考虑版本
  pip3 install pymysql
  
  import pymysql
  
  conn = pymysql.connect(
  		host = '127.0.0.1',
      	port = 3306
      	user = 'root',
      	password = '123456',  # 还可以简写passwd = '123456'
      	database = 'db666',  # 还可以简写db = 'db666'
      	charset = 'utf8'  # 千万不要加横杆
  )
  # cursor = conn.cursor()  # 括号内不加参数的话 那么查询出来的数据是元组的形式 数据不够明确 容易混乱
  cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 字典形式返回数据 数据有具体的描述信息 更加的合理方便
  
  sql = 'select * from user'
  affect_rows = cursor.execute(sql)  # 返回值是当前sql语句执行的受影响的行数
  cursor.fetchone()  # 只能结果的一条  数据本身
  cursor.fetchall()  # 拿所有		列表套多个数据
  cursor.fetchmany(n)  # 指定获取几条
  """
  上述三个方法在读取数据的时候有一个类似于文件指针的特点
  """
  cursor.scroll(1,'relative')  # 相对于光标所在的当前位置往后移动
  cursor.scroll(1,'absolute')  # 相对于数据开头往后移动
  

• sql注入

  """
  就是利用一些特殊字符 结合软件固定的一些语句句式
  非法侵入并违规操作
  
  利用MySQL注释的语法 造成了sql注入的问题
  	用户不需要属于用户名和密码也能够登录并且获取到整个用户表的数据
  
  日常应用软件在获取用户输入的内容时 都会限制一些特殊符号的输入
  
  如何解决上述问题？
  	所有敏感的信息不要自己去做拼接操作 交互固定的模块帮你去过滤数据防止sql注入
  
  在pymysql中 execute就能够帮你过滤
  """
  # sql注入
  sql = "select * from user where username='%s' and password='%s'"%(username,password)
  # 不要自己拼接
  sql = "select * from user where username=%s and password=%s"
  execute(sql,(username,password))  # 只能识别%s
  

pymysql补充

# 1.针对增删改 pymysql需要二次确认才能真正的操作数据
import pymysql


conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    passwd = '123456',
    db = 'day48',
    charset = 'utf8',
    autocommit = True
)
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 增
sql = 'insert into user(name,password) values(%s,%s)'
# rows = cursor.execute(sql,('jackson',123))
rows = cursor.executemany(sql,[('xxx',123),('ooo',123),('yyy',123)])
print(rows)
# conn.commit()  # 确认
# 修改
# sql = 'update user set name="jasonNB" where id=1'
# rows = cursor.execute(sql)
# print(rows)
# conn.commit()  # 确认
# 删除
sql = 'delete from user where id=7'
rows = cursor.execute(sql)
print(rows)
conn.commit()  # 确认
# 查
# sql = 'select * from user'
# cursor.execute(sql)
# print(cursor.fetchall())

"""
增删改查中
    删改增它们的操作设计到数据的修改 
    需要二次确认
"""


# 还可以一次性插入N多条数据
rows = cursor.executemany(sql,[('xxx',123),('ooo',123)])

今日内容概要

今日内容基本都是了解知识点，作为一名开发人员你在工作中基本用不到

• 视图(了解)
• 触发器(了解)
• 事务(需要掌握)
• 存储过程(了解)
• 内置函数(了解)
• 流程控制(了解)
• 索引理论(了解 日后再去扩展)

今日内容详细

视图(了解)

• 什么是视图

  """
  视图就是通过查询得到一张虚拟表，然后保存下来，下次可以直接使用
  其实视图也是表
  """
  

• 为什么要用视图

  """
  如果要频繁的操作一张虚拟表(拼表组成的)，你就可以制作成视图 后续直接操作
  """
  

• 如何操作

  # 固定语法
  create view 表名 as 虚拟表的查询sql语句
  
  # 具体操作
  create view teacher2course as
  select * from teacher INNER JOIN course
  on teacher.tid = course.teacher_id
  ;
  

• 注意

  """
  1 创建视图在硬盘上只会有表结构 没有表数据(数据还是来自于之前的表)
  2 视图一般只用来查询 里面的数据不要继续修改 可能会影响真正的表
  """
  

• 视图到底使用频率高不高呢？

  """
  不高
  当你创建了很多视图之后 会造成表的不好维护 
  """
  # 总结
  	视图了解即可 基本不用！！！
  

触发器(了解)

在满足对表数据进行增、删、改的情况下，自动触发的功能

使用触发器可以帮助我们实现监控、日志...

触发器可以在六种情况下自动触发 增前 增后 删前删后 改前改后

基本语法结构

create trigger 触发器的名字  before/after insert/update/delete on 表名
for each row
begin
	sql语句
end

# 具体使用 针对触发器的名字 我们通常需要做到见名知意
# 针对增
create trigger tri_before_insert_t1  before insert on t1
for each row
begin
	sql语句
end
create trigger tri_after_insert_t1  after insert on t1
for each row
begin
	sql语句
end
"""针对删除和修改 书写格式一致"""

ps:修改MySQL默认的语句结束符  只作用于当前窗口
	delimiter $$  将默认的结束符号由;改为$$
    delimiter ;
    
# 案例
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表中的记录succes字段是no那么就触发触发器的执行去errlog表中插入数据
NEW指代的就是一条条数据对象
"""
delimiter $$
create trigger tri_after_insert_cmd after insert on cmd 
for each row
begin
	if NEW.success = 'no' then
    	insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;

# 朝cmd表插入数据
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('jason','0755','ls -l /etc',NOW(),'yes'),
    ('jason','0755','cat /etc/passwd',NOW(),'no'),
    ('jason','0755','useradd xxx',NOW(),'no'),
    ('jason','0755','ps aux',NOW(),'yes');

# 删除触发器
drop trigger tri_after_insert_cmd;

事务

• 什么是事务

  """
  开启一个事务可以包含多条sql语句 这些sql语句要么同时成功
  要么一个都别想成功 称之为事务的原子性
  """
  

• 事务的作用

  """
  保证了对数据操作的安全性
  """
  eg:还钱的例子
      egon用银行卡给我的支付宝转账1000
      	1 将egon银行卡账户的数据减1000块
          2 将jason支付宝账户的数据加1000块
      
     	你在操作多条数据的时候可能会出现某几条操作不成功的情况 
  

• 事务的四大特性

  """
  ACID
  A:原子性
  	一个事务是一个不可分割的单位，事务中包含的诸多操作
  	要么同时成功要么同时失败
  C:一致性
  	事务必须是使数据库从一个一致性的状态变到另外一个一致性的状态
  	一致性跟原子性是密切相关的
  I:隔离性
  	一个事务的执行不能被其他事务干扰
  	（即一个事务内部的操作及使用到的数据对并发的其他事务是隔离的，并发执行的事务之间也是互相不干扰的）
  D:持久性
  	也叫"永久性"
  	一个事务一旦提交成功执行成功 那么它对数据库中数据的修改应该是永久的
  	接下来的其他操作或者故障不应该对其有任何的影响
  """
  

• 如何使用事务

  # 事务相关的关键字
  # 1 开启事务
  start transaction;
  # 2 回滚(回到事务执行之前的状态)
  rollback;
  # 3 确认(确认之后就无法回滚了)
  commit;
  
  """模拟转账功能"""
  create table user(
  	id int primary key auto_increment,
      name char(16),
      balance int
  );
  insert into user(name,balance) values
  ('jason',1000),
  ('egon',1000),
  ('tank',1000);
  
  
  # 1 先开启事务
  start transaction;
  # 2 多条sql语句
  update user set balance=900 where name='jason';
  update user set balance=1010 where name='egon';
  update user set balance=1090 where name='tank';
  
  """
  总结
  	当你想让多条sql语句保持一致性 要么同时成功要么同时失败 
  	你就应该考虑使用事务
  """