mysql视图、存储过程、函数

视图概念

视图是一条select语句返回的结果集。

• 优点：
  • 简单
  • 安全：通过视图可以实现只允许被访问到的结果集
  • 数据相对独立：表结构的添加不会影响到视图的结果集

视图的使用

创建视图语法

CREATE VIEW view_xy_detail
AS 
SELECT d_name,d_message FROM details;

查询及修改视图数据 查询

SELECT * FROM view_xy_detail;

修改

UPDATE view_xy_detail SET d_message = 'hello world';

需要注意，增删改操作修改的是源表数据

查询已经创建的视图

SHOW TABLES;

查询视图创建信息

SHOW CREATE VIEW view_xy_detail;

删除视图

DROP VIEW view_xy_detail;

存储过程概念

一组sql语句的集合，可以减少应用和数据库的连接次数，从而提升效率

存储过程简单使用

创建存储过程

DELIMITER $$

CREATE PROCEDURE pro_test1()
BEGIN
SELECT * FROM details;
END$$

DELIMITER ;

默认结束符为;，使用DELIMITER语句修改为$$

存储过程调用

CALL pro_test1();

存储过程删除

DROP PROCEDURE IF EXISTS pro_test1;

存储过程的其他相关信息查询

-- 通过数据库名查询存储过程信息
SELECT * FROM mysql.proc WHERE db = 'xydemo';

-- 查询存储过程状态信息
SHOW PROCEDURE STATUS WHERE db = 'xydemo';

-- 根据存储过程名查询存储过程的创建信息
SHOW CREATE PROCEDURE pro_test1;

存储过程编程

创建包含自定义变量、输入输出参数、if、case逻辑判断的存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_test1`(IN num INT,OUT message VARCHAR(20))
BEGIN
DECLARE size INT DEFAULT 0;
DECLARE des VARCHAR(20) DEFAULT '';
SELECT COUNT(*) INTO size FROM details;
SET size = size + num;
IF size<50 THEN
 SET des='不及格';
ELSEIF size<80 THEN
 SET des='可以';
ELSE 
 SET des='优秀';
END IF;
CASE des
 WHEN '不及格' THEN
  SET message = '很抱歉，不及格';
 WHEN '可以' THEN
  SET message = '再接再厉';
 ELSE 
  SET message = '真的很棒';
END CASE;
END$$

调用上面的存储过程

CALL pro_test1(50,@mess)

其中，@mess表示接受输出参数的会话变量，如果当前会话关闭，将其释放。

查询@mess

SELECT @mess

创建包含三种循环方式的存储过程

CREATE PROCEDURE `pro_test1`(IN num INT)
BEGIN
DECLARE numcount INT DEFAULT 0;
DECLARE total INT DEFAULT 0;
  SET numcount = num;
  -- while循环
  WHILE num>=0 DO
    SET total = total + num;
    SET num = num-1;
  END WHILE;
  SET num = numcount;
  -- repeat循环
  REPEAT 
    SET total = total + num;
    SET num = num-1;
    UNTIL num = 0
  END REPEAT;
  SET num = numcount;
  -- loop循环
  c:LOOP
    SET total = total + num;
    SET num = num - 1;
    IF num <= 0 THEN
      LEAVE c;
    END IF;
  END LOOP c;
  SELECT total;
END$$

游标的使用

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_test2`()
BEGIN
		DECLARE id INT;
		DECLARE dname VARCHAR(50);
		DECLARE message VARCHAR(100);
		DECLARE has_data INT DEFAULT 1;
		-- 创建游标
		-- DECLARE EXIT HANDLER FOR NOT FOUND SET has_code=0;必须声明在游标创建下方
		DECLARE emp_result CURSOR FOR SELECT * FROM details;
		DECLARE EXIT HANDLER FOR NOT FOUND SET has_data=0;
		
		OPEN emp_result;
		REPEAT
			FETCH emp_result INTO id,dname,message;
			SELECT CONCAT('id:',id,'dname:',dname,'message:',message);
			-- 判断has_data为0退出
			UNTIL has_data=0
		END REPEAT;
		CLOSE emp_result;
	END$$

CALL pro_test2();

函数

函数与存储过程的区别在于函数有返回值，但是存储过程没有（但是也可以返回结果）

CREATE DEFINER=`root`@`localhost` FUNCTION `fun_test1`(num INT) RETURNS INT(11)
BEGIN
	DECLARE cnum INT DEFAULT 0;
	SELECT COUNT(*) INTO cnum FROM details WHERE d_id > num;
	RETURN cnum;
END$$

调用语法

SELECT fun_test1(2);

触发器

触发器是与表有关的数据库对象，在进行增删改后，触发执行定义的sql集合

• 作用

  • 协助应用在数据库端保证数据的完整性
  • 记录日志
  • 校验参数

• 类型

  • insert触发器
  • update触发器
  • delete触发器

• 别名

  • old：操作前的数据
  • new：操作后的数据

用法

insert触发器

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `tri_insert` AFTER INSERT ON `details` 
    FOR EACH ROW 
    BEGIN
	
	INSERT INTO emp_logs(id,play_type,play_time,play_id,play_ele)
	VALUES(NULL,'insert',NOW(),new.d_id,CONCAT('name:',new.d_name,'message:',new.d_message));
    END;
$$

update触发器

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `tri_update` AFTER UPDATE ON `details` 
    FOR EACH ROW BEGIN
	INSERT INTO emp_logs(id,play_type,play_time,play_id,play_ele)
	VALUES(NULL,'update',NOW(),new.d_id,CONCAT('修改前-name:',old.d_name,'message:',old.d_message,'修改后-name:',new.d_name,'message:',new.d_message));
    END;
$$

delete触发器

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `tri_delete` AFTER DELETE ON `details` 
    FOR EACH ROW BEGIN
	INSERT INTO emp_logs(id,play_type,play_time,play_id,play_ele)
	VALUES(NULL,'delete',NOW(),old.d_id,CONCAT('删除的数据-name:',old.d_name,'message:',old.d_message));
    END;
$$

分别调用details表的insert、delete、update

INSERT INTO details VALUES(NULL,'鬼刀一开','看不见走位走位');
DELETE FROM details WHERE d_id = 6;
UPDATE details SET d_name = '看不见',d_message ='走位走位' WHERE d_id = 5;

分别执行了不同的触发器

触发器的其他相关操作

-- 查询创建的触发器
SHOW TRIGGERS;
-- 查询触发器创建信息
SHOW CREATE TRIGGER tri_delete;
-- 删除触发器
DROP TRIGGER tri_delete;