笔记总结：MySQL——存储过程、函数、触发器、视图

存储过程、函数、触发器、视图

一、变量

1.全局变量

当服务启动时，全局变量设置为默认值。更改全局变量需要管理员权限，作用域是整个服务器周期。

-- 显示所有全局变量
SHOW GLOBAL VARIABLES;
-- 设置全局变量
SET GLOBAL sql_warnings = NO;
SET @@GLOBAL.sql_warnings = OFF;
--查询全局变量的值
SELECT @@GLOBAL.sql_warnings;
SHOW GLOBAL VARIABLES LIKE '%sql_warnings%';

2.会话变量

服务器为每个连接的客户端维护的变量，作用域为当前连接，会话变量是独立的。

-- 显示所有会话变量、
SHOW SESSION VARIABLES;
-- 设置会话变量
SET SESSION auto_increment_increment = 1;
SET @@SESSION.auto_increment_increment = 2;
SET auto_increment_increment = 1;
-- 查询会话变量
SELECT @@auto_increment_increment;
SELECT @@SESSION.auto_increment_increment;
SHOW SESSION VARIABLES LIKE '%auto_increment_increment%';
--SESSION可省略，也可用LOCAL代替

3.用户变量

作用域为当前连接。使用时直接使用“@变量名”。

SET @age = 19;
SET @age := 19;

SELECT @age := 22;
SELECT @age := age FROM stu WHERE `name` = '张华';

SELECT age INTO @age FROM stu WHERE `name` = '张华';
SELECT @age;

Example:用户变量用于计数

SELECT (SELECT @index := @index + 1) num, a.* FROM score a, (SELECT @index := 0) b WHERE id>100;

先设置index为0，每次查询出一组记录，index+1，由此实现计数。

4.局部变量

只用于BEGIN/END语句块

-- 定义
DECLARE age INT(3) DEFAULT 0;
-- 为局部变量赋值
SET age = 10;
SELECT age := 10;
SELECT 10 INTO age;
SELECT age;

二、存储过程

1.定义

存储过程是一组为完成特定功能而存储在数据库中的SQL语句集，一次编译永久有效。类似于函数。

2.使用存储过程的原因

• 运行速度块

  再执行同样过程可以从内存中直接调用

• 减少网络传输

  存储过程直接在数据库服务器上跑，不需要传输数据到其他服务器

• 增强安全性

  客户端只传输调用指令，防止SQL被截获，篡改

3.使用存储过程

-- 声明分隔符
[DELIMITER $$]
CREATE PROCEDURE 存储过程名称 ([IN|OUT|INOUT] 参数名1 数据类型，[IN|OUT|INOUT] 参数名2 数据类型，...)
-- 语句块开始
BEGIN
--SQL语句集
END[$$]
--还原分隔符
[DELIMITER ; ]

--调用存储过程
CALL 存储过程名(参数1，参数2,...);

-- 如果影响行数为1那么执行下一条语句否则执行ELSE语句
IF row_count() = 1 THEN 语句
ELSE 语句
END IF;
-- IF语句可嵌套

4.事物

1. 定义

   访问并可能操作各种数据项的一个数据库操作序列，要么全执行，要么全不执行。

2. 事物特性（ACID）

   • 原子性（Atomicity）

     事务各元素不可分的，是一个整体

   • 一致性（Consistency）

     当事务完成时，必须保证所有数据保持一致状态。例如转账完成后，所有账户总金额不变，此时数据处于一致性状态。

   • 隔离性（Isolation）

     数据操作的多个并发事物相互独立。

   • 持久性（Durability）

     已提交事物，系统保证该事务对数据库的修改不会丢失，即使数据库故障

3. 事物的使用

   -- 开启事物
   START TRANSACTION;
   

   银行转账存储过程实例：

   DROP PROCEDURE IF EXISTS transferMoney;
   CREATE PROCEDURE transferMoney(IN transferFrom BIGINT, IN transferTo BIGINT, IN money DOUBLE(20,3), OUT result TINYINT(1))
   BEGIN
   	-- 处理器有两种方式：CONTINUE,EXIT
   	-- CONTINUE表示即使有异常发生也会执行后续语句
   	-- EXIT表示由异常发生时，直接退出当前存储过程
   	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result = 0;
   	START TRANSACTION;
   	SET result = 0;
   	UPDATE account SET balance = balance - money WHERE account=transferFrom AND balance>=money;
   	IF ROW_COUNT() = 1 THEN
   		UPDATE account SET balance = balance + money WHERE account=transferTo;
   		IF ROW_COUNT() = 1 THEN
   			SET result = 1;
   		END IF;
   	END IF;
   	IF result = 0 THEN ROLLBACK;
   	ELSE COMMIT;
   	END IF;
   END 
   
   CALL transferMoney(123456,123457,2000,@rs);
   

三、自定义函数

1.定义

一组为完成特定功能而存储在数据库中的SQL语句集，一次编译永久有效，可能会有返回值。

2.使用自定义函数

CREATE FUNCTION 函数名称 (参数名1 数据类型，参数名2 数据类型，...)
RETURNS　数据类型
DETERMINISTIC | NO SQL | READS SQL DATA | MODIFIES SQL DATA | CONTAINS SQL
BEGIN
	-- SQL语句集
	RETURN 结果;
END

函数特征	说明
DETERMINISTIC	不确定的
NO SQL	没有SQL语句(对数据库查询或修改)
READS SQL DATA	只读数据，不修改
MODIFIES SQL DATA	要修改
CONTAINS SQL	包含SQL语句

3.循环结构

WHILE 循环条件 DO
	--SQL语句集
END WHILE;

REPEAT
	--SQL语句集
UNTIL 循环终止条件 END REPEAT;

标号：LOOP
		--SQL语句集
		IF 循环终止条件 THEN LEAVE 标号;
		END IF;
	END LOOP;

生成随机字符串函数实现实例：

DROP FUNCTION IF EXISTS randomStr;
CREATE FUNCTION randomStr(len INT(11))
RETURNS VARCHAR(255)
NO SQL
BEGIN
	DECLARE s VARCHAR(50) DEFAULT 'abcdefghijklmnopqrstuvwxyz0123456789';
	DECLARE rs VARCHAR(255) DEFAULT('');
	DECLARE i INT(11) DEFAULT(0);
	DECLARE position INT(11);
	WHILE i < len DO
		SELECT ROUND(RAND() * 36) INTO position;
		SET rs = CONCAT(rs, SUBSTRING(s,position,1));
		SET i = i + 1;
	END WHILE;
	RETURN rs;
END

四、触发器

1.概念

用来保证数据完整性，当数据被修改时触发，用于加强数据的完整性约束。

2.定义触发器

CREATE TRIGGER 触发器名称 {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 FOR EACH ROW
BEGIN
	-- SQL语句集
END

3.触发器类型

触发器类型	NEW和OLD的使用
INSERT触发器	NEW表示将要或已新增数据
UPDATE触发器	OLD表示将要或者已修改的数据，NEW表示将要改成的数据
DELETE触发器	OLD表示将要或者已经删除的数据

五、视图

1.定义

视图是虚拟表，不存数据，所有数据都是从其他表查询。

2.使用视图

CREATE VIEW 视图名称 AS SELECT 列1[，列2...] FROM 表名 WHERE 条件；
CREATE OR REPLACE VIEW 视图名称 AS SELECT 列1[，列2...] FROM 表名 WHERE 条件；
DROP VIEW IF EXISTS 视图名称;

3.使用视图原因

• 如果频繁获取表中某些字段值，则可创建视图。

• 简化数据操作

• 提高安全性能，过滤字段

注意：视图不能提升查询速度，也加大了数据库服务器的压力，只是方便业务开发。