MySQL进阶2

一 视图

1 问题

对于复杂的查询，往往是有多个数据表进行关联查询而得到，如果数据库因为需求等原因发生了改变，为了保证查询出来的数据与之前相同，则需要在多个地方进行修改，维护起来非常麻烦
解决办法：定义视图

2 视图是什么

通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
视图是对若干张基本表的引用，是一张虚拟表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；
作用：方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；

3 视图的优点和缺点

优点

• 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结构

• 安全：使用视图的用户只能访问他们被允许查询的结构集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现

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

缺点

• 性能：从数据库视图查询数据可能会很慢，特别是如果视图是基于其他视图创建的

• 表依赖关系：将根据数据库的基础表创建一个视图。每当更改与其相关联的表的结构时，都必须更改视图。

  4 视图操作

  (1)定义视图

  建议以v_开头

  create view 视图名称 as select语句;
  -- 创建一个查询员工基本信息和部门信息的视图
  CREATE VIEW v_empinfo AS
  SELECT emp.empname, emp.hirdate, dept.deptname, dept.location
  FROM employee as emp, department as dept
  WHERE emp.deptno = dept.deptno;
  

  (2)查看视图

  查看表会将所有的视图也列出来

  -- 查看视图
  show tables;
  -- 查询视图创建语句
  SHOW CREATE VIEW view_name;
  

  (3)使用视图

视图的用途就是查询

select * from 视图名称;

-- 查看视图
select * from empinfo;

(4) 删除视图

drop view 视图名称;

(5) 修改视图

alter view 视图名称 as select语句;

-- 修改视图，添加员工岗位、员工工资
ALTER VIEW v_empinfo AS
SELECT emp.empname, emp.job, emp.salary,
emp.hirdate, dept.deptname, dept.location
FROM employee as emp, department as dept
WHERE emp.deptno = dept.deptno;

(6)原表修改数据,影响视图

-- 删除employee“五虎上将”的所有成员
DELETE FROM employee WHERE job = "五虎上将";
-- 查询视图数据
SELECT * FROM v_empinfo WHERE job = "五虎上将";

(7)修改视图数据:一般不能修改

-- 从视图中删除数据
DELETE FROM empinfo WHERE deptname = "军委1部";

出现错误，代码： 1395
因为视图默认情况下都是只读的，不允许通过视图直接进行数据的改动，这也是数据安全隔离的一种操作手段。

如果需求确认创建可更新(update、 delete、insert)的视图，需要确认下面的条件之一满足的话，就不允许通过视图更新表中的数据
（1）聚合函数；
（2）DISTINCT关键字；
（3）GROUP BY子句；
（4）ORDER BY子句；
（5）HAVING子句；
（6）UNION运算符；
（7）位于选择列表中的子查询；
（8）FROM子句中包含多个表；
（9）SELECT语句中引用了不可更新视图；
（10）WHERE子句中的子查询，引用FROM子句中的表；
（11）ALGORITHM选项指定为TEMPTABLE（使用临时表总会使视图成为不可更新的）。

二 触发器

触发器（trigger）也称为触发程序，是 MySQL 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete， update）时就会激活它执行。

理解：在指定表上，(insert(插入)、update(更新)、delete(删除))事件动作,触发，执行指定的一群或一个sql语句。类比python中的魔法方法。

（一）创建触发器

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

• TRIGGER ：触发器是与表有关的数据库对象，当表上出现特定事件时，将激活该对象的操作。

• trigger_name : 触发器名称，用户自行指定

• CREATE TRIGGER trigger_name trigger_time trigger_event

• ON tbl_name FOR EACH ROW trigger_stmt

• trigger_time ：是触发程序的动作时间。它可以是BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发。

• trigger_event : 指明了激活触发程序的语句的类型。

  trigger_event：可以是下述值之一：

  • INSERT：将新行插入表时激活触发程序。
  • UPDATE：更改某一行时激活触发程序。
  • DELETE：从表中删除某一行时激活触发程序。

• tbl_name ：触发程序与命名为tbl_name的表相关。tbl_name必须引用永久性表。不能将触发程序与视图关联起来

• trigger_stmt : 触发程序体，可以是一条SQL语句或是BEGIN和END包含的数条语句

注意：并且一张表上不能创建两个相同类型的触发器。

（二）查看触发器

SHOW TRIGGERS; 查看触发器信息

（三）删除触发器

DROP TRIGGER 触发器名;

（四）触发器的执行顺序

• 如果BEFORE触发器执行失败，SQL无法正确执行。
• SQL执行失败时，AFTER型触发器不会触发。
• AFTER类型的触发器执行失败，SQL会回滚。

（五）案例学习

商品购买生成订单，由于订单表的数据比较敏感，屏蔽其SQL操作，通过触发器将订单生成过程封装起来。
创建商品表、订单表：

-- 商品表
CREATE TABLE goods_item(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '商品编号',
gname VARCHAR(100) NOT NULL COMMENT '商品名称',
gprice DOUBLE NOT NULL COMMENT '商品单价',
stock INTEGER NOT NULL COMMENT '商品库存'
);
-- 订单表
CREATE TABLE goods_order(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单编号',
gname VARCHAR(100) NOT NULL COMMENT '商品名称',
gprice DOUBLE NOT NULL COMMENT '商品单价',
goprice DOUBLE NOT NULL COMMENT '成交单价',
gocount INTEGER DEFAULT 1 COMMENT '成交数量',
subtotal DOUBLE NOT NULL COMMENT '小计金额'
);

初始化商品数据：

-- 增加商品
INSERT INTO goods_item (gname, gprice, stock)
VALUES('加湿器', 199.00, 100),
('空气净化器', 329.00, 20);

触发器需求：在购买商品时，修改商品库存数量，此时一旦库存数量发生更新，就自动生成订单：
定义触发器逻辑

DELIMITER $$  			#变更结束符号
CREATE TRIGGER goods_sale_trigger AFTER
UPDATE ON goods_item FOR EACH ROW
BEGIN
-- 声明变量 购买数量、小计金额
DECLARE buycount INT;
DECLARE subtotal DOUBLE;
IF new.stock < old.stock THEN
-- 计算购买数量和小计金额
SET buycount = old.stock - new.stock;
SET subtotal = new.gprice * buycount;
-- 生成订单
INSERT INTO goods_order
VALUES(0,new.gname, new.gprice, new.gprice, buycount, subtotal);
END IF;
END;$$
-- 修改默认结束符号
delimiter ;  			#修改回来了

备注：由于MySQL一旦在语句中遇到 ; 表示语句即刻执行，使用关键字DELIMITER$$重新指定执行符号为$$，创建触发器的过程中直到遇到符号$$才会完整执行整段SQL语句，完成触发器的创建。

• old: 表示事件发生之前的数据, 旧的数据
• new: 表示事件发生之后的数据, 新的数据

执行SQL语句更新商品表中加湿器的库存数量

-- 更新库存，商品1从原来100更新为80
UPDATE goods_item SET stock=80 WHERE id=1;

查看结果，发现出现了数据

三 存储过程

存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

(一) 存储过程和函数的创建

1 创建存储过程

CREATE PROCEDURE proc_name ([proc_parameters]) [characteristics] routing_body

• CRAETE PROCEDURE 是创建存储过程的基本语法
• proc_name 是创建的存储过程的名称
• proc_parameters 是执行存储过程时的参数
  • IN：输入参数
  • OUT：输出参数
  • INOUT：输入或者输出参数
• routing_body 是存储过程的执行体，将多个SQL语句包含在BEGIN…END之间，描述存储过程的功能主体
• characteristics ：是一项功能参数，不同的参数描述了存储过程不同的特性
  • LANGUAGE SQL ：描述了存储过程中的语句由SQL语句组成，也是目前唯一的特性
  • DETEMINISTIC (确定性)：描述存储过程的执行结果在输入参数一致的情况是否返回相同结果.
  • SQL SECURITY {DEFINER | INVOKER} ：描述有权限执行存储过程的用户，DEFINER表示只有定义存储过程的用户才有权限执行，INVOKER表示拥有权限的用户都可以执行，默认情况下为DEFINER
  • COMMENT ‘string’ ：存储过程的注释信息，描述存储过程或者函数

练习

-- 创建无参数存储过程			# 可以理解定义函数
delimiter $$
create procedure avg_employee()
begin
select avg(e.comm) from python0831.employee as e;
end $$
delimiter ;
-- 调用
call avg_employee

--创建有参数存储过程
delimiter $$
create procedure avg_employee2(out result float)
begin
select avg(e.comm) into result from python0831.employee as e;
end $$
delimiter ;
--调用:输出结果放到@result
call avg_employee2(@result);
--查看输出结果
select @result;

2 自定义存储函数(了解)

创建

CREATE FUNCTION func_name([func_parameters])
RETURNS type
[characteristic] routing_body

• CREATE FUNCTION创建自定义函数的基本法
• func_name自定义函数的名称
• [func_parameters]函数的参数列表
  • 只能是in
• RETURNS type函数的返回值的类型
• characteristic参数指定函数的特性，存储函数只支持DETERMINISTIC（没有NOT DETERMINISTIC）、NO SQL 和 READS SQL DATA ：
  • DETERMINISTIC：指明存储函数的执行结果是确定的.
  • NO SQL：表示子程序中不包含SQL语句
  • READS SQL DATA：表示子程序中包含读数据的语句；

练习

-- 有参数
delimiter $$
create function my_func(id int) returns varchar(20)
READS SQL DATA
return (select empname from python0831.employee where empno=id);
delimiter ;
delimiter $$
create function my_func3(id int) returns double
READS SQL DATA
return (select salary from python0831.employee where empno=id);
delimiter ;
--无参数
delimiter $$
create function my_func2() returns varchar(20)
NO SQL
return "返回值"
$$
delimiter ;
--调用
select my_func(1);
select my_func2();

如果上述过程出错，需要打开允许创建自定义函数

mysql> show variables like '%func%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
mysql> set global log_bin_trust_function_creators=1;
mysql> show variables like '%func%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON |
+---------------------------------+-------+

3 删除存储过程或者函数

DROP {PROCEDURE | FUNCTION} 数据库.存储过程名;
drop procedure python0831.test2;

4 查看存储过程或者函数

SHOW CREATE PROCEDURE 数据库.存储过程名;
show create procedure my_db.avg_employee2;

( 二 ) 存储过程基础语法

1 声明变量

DECLARE var_name [, var_name2]... date_type [default value];
只能在begin...end 块中使用

2 变量赋值

SET var_name = expr [, var_name2 = expr2]
# 通过查询赋值变量
SELECT col_name[,...] INTO var_name[,...] table_expr

示例：
DELIMITER $$
create procedure test1(out num2 int)
begin
declare num int default 100;
set num2=num;
end $$
DELIMITER ;
call test1(@n);
select @n;

3 流程处理

• if语句

  IF search_condition THEN statement_list
  [ELSEIF search_condition THEN statement_list] ...
  [ELSE statement_list]
  END IF
  

• case语句

  CASE case_value
  WHEN when_value THEN statement_list
  [WHEN when_value THEN statement_list] ...
  [ELSE statement_list]
  END CASE
  

• 循环结构

  • loop循环

    CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
    END CASE
    

  • while循环

    [begin_label:] WHILE search_condition DO
    statement_listEND WHILE [end_label]
    

4 练习

-- 案例操作：获取大于指定工资的所有员工数据，如果没有指定就获取大于平均工资的所有员工数据
DELIMITER $$
CREATE PROCEDURE get_salary2(IN sa DOUBLE)
BEGIN
IF ISNULL(sa) = 1
THEN
SELECT AVG(salary) INTO sa FROM employee;
END IF;
INSERT INTO emp
SELECT * FROM employee WHERE salary >= sa;
END $$
DELIMITER ;
-- 调用存储过程
call get_salary(10000);
-- 查看数据
select * from emp;

（三）总结：存储过程和函数区别

1）一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。存储过程，功能强大，可以执行包括修改表等一系列数据库操作；用户定义函数不能用于执行一组修改全局数据库状态的操作。
2）对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。函数只能返回一个变量；而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型，而函数只能有IN类~~存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。
3）存储过程，可以使用非确定函数，不允许在用户定义函数主体中内置非确定函数。
4）存储过程一般是作为一个独立的部分来执行（ EXECUTE 语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程，而可以使用函数。

四 函数

函数是实现了特定的行为的功能组件，通过函数操作能很方便的完成普通 SQL 语句实现较为困难的功能

1 case函数

流程操作语法中，区分值匹配模式和表达式匹配模式，CASE语句后添加具体表达式数据，和WHEN后面的表示数据进行匹配，匹配成功则执行对应的THEN子句表达式result。

-- 值匹配
CASE value
WHEN [compare-value] THEN result
[WHEN [compare-value] THEN result ...]
[ELSE result]
END
-- 表达式匹配
CASE WHEN [condition] THEN result
[WHEN [condition] THEN result ...]
[ELSE result]
END

基本操作案例：

-- 值匹配模式
SELECT
CASE "A"
WHEN "A" THEN "优秀"
WHEN "B" THEN "良好"
ELSE "three" END;
-- 表达式匹配模式
SELECT CASE
WHEN 100 > 80 THEN "大于关系成立"
WHEN 100 < 80 THEN "小于关系成立"
ELSE "其他关系" END;

2 if 流程函数

-- 基本IF流程函数：条件为expr1，条件为True则返回expr2，否则返回expr3
IF(条件,成立返回值,不成立返回值)
-- 简化NULL判断操作：如果expr1表达式为NULL则返回expr2，否则返回expr1；返回值时候数字或者字
符串
IFNULL(expr1,expr2)
-- 检查等价判断操作：如果expr1和expr2相等则返回NULL，否则返回expr1
NULLIF(expr1,expr2)
案例操作：
-- IF判断
SELECT IF(1, "条件成立", "条件不成立"); -- 条件成立
-- IFNULL判断
SELECT IFNULL(NULL, "hello");-- hello
-- NULLIF判断
SELECT NULLIF(12, 11); -- 12
SELECT NULLIF(12, 12); -- NULL

3 内聚函数

常用聚合函数见前篇

4 内置函数

（1）字符串常用函数

concat(s1,s2,…Sn) 连接s1,s2..Sn为一个字符串
length(str) 返回值为字符串str 的长度
select concat('hello','world');
select length('helloworld');

（2）数值函数

abs(x) 返回x的绝对值
round(x,y)返回参数x的四舍五入的有y位小数的值
select abs(-10);
select round(2.23423,3);

(3) 日期和时间函数

now() 返回当前日期和时间,
unix_timestamp(date) --返回date时间的unix时间戳
date_fomat(date,fmt) --返回按字符串fmt格式化日期date值
datediff(expr,expr2) --返回起始时间和结束时间的间隔天数
--统计时间戳647583423距离当前时间相差天数（生日天数（不考虑年份））
select datediff(date_format(from_unixtime(647583423),"%y-%m-%d
%h:%i:%s"),now());
select datediff('2020-10-01','2019-01-10');

(4)其他常用函数

database() 返回当前数据库名
version() 返回当前服务器版本
user() 返回当前登陆用户名
inet_aton 返回当前IP地址的数字表示 inet_aton("192.168.80.250");
inet_ntoa(num) 返回当前数字表示的ip inet_ntoa(3232256250);
password(str) 返回当前str的加密版本 -- 8.0已废弃
md5(str) 返回字符串str的md5值
sha1(str)