复习：

上节课复习：
    1、单表查询
    select distinct 字段1,字段2,字段3,... from 库.表
        where 过滤条件
        group by 分组字段
        having 过滤条件
        order by 排序字段 asc,排序字段 desc
        limit 条数;

    2、多表查询
    select 表1.*,表2.id,表2.name from 表1 inner join 表2 on 表1.字段 = 表2.字段;（常用的）
    select * from 表1 left join 表2 on 表1.字段 = 表2.字段;
    select * from 表1 right join 表2 on 表1.字段 = 表2.字段;

    3、子查询
    select * from emp where dep_id =
    (select id from dep where name="IT")

视图 view

视图特点

视图：只有frm表结构的虚拟表（之前我们学过select 的查询结果是一张虚拟表）

• 在操作系统上（innodb），只持久化frm表结构文件，没有idb数据文件
• 每次用view，都会触发sql语句从原表获取数据，实时更新。

视图带来的好处：连接多张表，只需对视图进行查询，简化sql复杂度。
视图的缺点：极度依赖原表，需要通过原表修改视图。

视图管理

创建视图

语法：create view .. as sql

create view school as select * from teacher left join course on course.teacher_id = teacher.tid;

修改视图

特点：视图通常只支持查询（也并不是不支持更新）
可更新情况：

• 没有聚合函数
• 没有分组
• 没有去重
• 没有联合查询
• 必须是单表查询（实际上都是单表查询了，更新视图干啥呢）

更新操作：怎么更新表就怎么更新视图

• 修改视图，原表一起变
• 还是那句话，不该修改视图，而且只支持单表且为原始数据格式

重定义视图

语法：alter view 视图名 as select...
也就是复用名称，重新定义一遍。

删除视图

语法：drop view 视图名

查看视图

语法：show create view 视图名

触发器 trigger

触发器：定义一些动作，被某个条件（增删改，没有查）触发
特点：我们调用不了，是被事件自动触发的。
执行时间：插入前/后，删除前/后、更新前/后

delimiter // #声明sql语句的结束符号为两个斜杠，因为内部要写入sql，避免;冲突。
create trigger name before/after insert/update/delete on for each row
begin
  if NEW.字段 then # NEW代表一个小盒子，装着插入的新纪录
    sql语句; #可以写多条，分号结尾
  end if; #分号结尾
end//
delimiter ; # 将sql结束符换回分号 

drop trigger name; #删除

函数 function

mysql的函数分为内置函数和自定义函数。

内置函数

用法：在sql中，函数只能在sql语句中使用，不支持独立调用。
分类（了解）：数学函数、聚合函数、字符函数、时间函数、流程控制函数、很多很多。
补充：date_format函数可以用来设置时间字段的显示格式。如：date_format(mytime,"%Y-%m")

自定义函数(了解)

函数中不要写sql语句（会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能，若要想在begin...end...中写sql，请用存储过程

定义函数：

delimiter // #声明sql语句的结束符号为两个斜杠，因为内部要写入sql，避免;冲突。
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END //
delimiter ;# 将sql结束符换回分号 

drop function func_name; #删除

执行函数：只能在sql中调用，不能独立使用。

变量：set @xxx=1; select @xxx;
函数：select f1(xx,yy) from t1;

流程控制 procedure

条件语句

if 条件判断

过程：先定义，后写逻辑
定义变量：declare / set

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 ;

调用：call proc_if()

case 多分支

在前面找过来，在concat那里

select () from employee; ()里面写什么呢？
select (
  case
    when name = "cuc" then
      concat(name,"_vip")
    when name = "cuckoo" then
      concat(name,"_svip")
    else
      ...
  end
) as vip_name 

循环语句

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 ;

call proc_while;

调用：call proc_while()

repeat 和 loop (了解)

也是循环，但了解就行了，不要以为只有while一种，有这个意识就行。

存储过程 procedure

存储过程：按业务层面的需要组织多条sql语句，通过存储过程名调用该功能。
哈哈哈：通常存储过程是理论功能，实际很难找到这么优秀的人专门搞这个技术。
存储过程作用：

• 代码精简：用于替代程序里面的sql语句，实现程序和sql解耦
• 网络传输量少：程序调用时，只需要发送存储过程名，避免大条大条的sql语句在程序和数据库之间传输。

优缺点：

• 优点：程序 数据库解耦；网络传输请求效率高
• 缺点：不方便扩展存储过程的功能

存储过程 VS 函数

• 函数只能在sql中用，函数不能写sql
• 存储过程可以独立调用，存储过程能写sql

程序怎么使用数据库（了解）

• 1、程序调用存储过程（很少用）
• 2、程序直接用纯sql语句（很少用）
• 3、程序通过ORM调用数据库，本质也是sql语句（常用）

无参存储过程

delimiter //
create procedure p1()
BEGIN
    select * from blog;
    INSERT into blog(name,sub_time) values("xxx",now());
END //
delimiter ;

#在mysql中调用
call p1() 

#在python中基于pymysql调用(了解)
cursor.callproc('p1') 
print(cursor.fetchall())

有参存储过程

存储过程不是函数；它内部的参数，在sql终端中都是可用的。

• in：传入参数用
• out：用于返回值用
• inout：既可以传入又可以当作返回值

delimiter //
create procedure p3(
    in n1 int,
    out res int,
    inout n3 int
)
BEGIN
    select * from employee where id > n1 and id < n3;
    set res = 1;
    set n3 = 100;

END //
delimiter ;

#在mysql中调用
set @res=0; #0代表假（执行失败），1代表真（执行成功）
set @xxx=10;
call p3(3,@res,@xxx);
select @res,@xxx;

#在python中基于pymysql调用
cursor.callproc('p3',(3,0)) #0相当于set @res=0
print(cursor.fetchall()) #查询select的查询结果

cursor.execute('select @_p3_0,@_p3_1;') #@p3_0代表第一个参数，@p3_1代表第二个参数，即返回值
print(cursor.fetchall())

sql注入

sql注入攻击：在参数中输入sql中可以解析的字符，改变sql语句的执行逻辑，比如通过单引号、--注释等影响sql的原流程。
解决方案：

• 不让用户输入特殊字符：跟用户交互的信息，如密码等，限制使用单引号，--等特殊字符。
• 将用户输入的字符转换成无意义：如果无法限制的情况下，可以将用户输入的所有内容，全部转换为纯字符串等。

# -*- coding: utf-8 -*-
# SQL注入实验代码（模拟版）
# pymysql
import pymysql
#import cryptography

conn = pymysql.connect(host='192.168.20.200', user='root', password='Cuckooyang@123', database='db1', charset='utf8')
cursor = conn.cursor()

inp_user = input("请输入您的账号名>>>: ")  # inp_user = ""
inp_pwd = input("请输入您的密码>>>: ")     # inp_pwd = ""

# sql = "select * from user where name='%s' and password = '%s';" % (inp_user,inp_pwd )
sql = "select * from user where name='%s' and password = '%s';" % (inp_user,inp_pwd )

print(sql)
rows = cursor.execute(sql)
# print(rows)

if rows != 0:
    print("登录成功")
else:
    print("登录失败")

cursor.close()
conn.close()

总结

视图是一张虚拟表，它绑定的一条查询语句，每次调用都会触发查询来获取数据，实际的数据保存在原表中。视图可以帮助程序简化sql复杂度，但极度依赖原表，视图通常也不支持修改，只能进行查询，视图的权限控制也比普通表麻烦。
一条sql的功能比较单一，mysql中提供了很多组织sql的方式给我们组装多条sql形成复杂的业务逻辑。按调度的方式，可分为自动调度和手动调度两种。
触发器trigger 是自动调度的典型，它定义了一些行为，通过探测表中记录的变化（增删改）来触发执行，通常用来保证字段之间的强绑定关系。
在手动调度中，有函数，流程控制，存储过程3种，实际上流程控制和存储过程都是procedure。函数function分为内置函数和自定义函数，内置函数非常多，如聚合函数、时间函数、字符拼接函数等，提供了很多丰富功能，自定义函数不支持写入sql,而且只能在sql中调用，它的目的，就是为了丰富sql查询的功能。procedure不一样，procedure是多条sql组成的逻辑整体，向外提供的是业务处理逻辑，procedure支持if，while等流程控制流，支持向内传递参数，输出返回值等，它的作用更像是一个shell脚本，在程序中可以直接调用。看起来存储过程和函数非常像，但函数的作用范围非常小，面向的是sql功能，存储对象面向的是业务逻辑。
程序中调用sql查询进行鉴权时，如果不对客户输入内容做限制，很容易导致sql注入攻击，代码逻辑层面需要对客户输入内容专门做处理，如不解析或者输入限制。