MySQL学习记录7、8、9- 视图&存储过程&触发器

本文主要是介绍MySQL学习记录7、8、9- 视图&存储过程&触发器，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

学习内容来自B站SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！

7视图

1.创建视图

CREATE VIEW clients_balance AS
SELECT 
	client_id,
    name,
    SUM(invoice_total) - SUM(payment_total) AS banlance
FROM invoices
JOIN clients USING(client_id)
GROUP BY client_id

2.更改或删除视图

（1）-- 删除视图
DROP VIEW IF EXISTS client_banlance
（2）-- 更改或创建视图
CREATE OR REPLACE VIEW client_banlance AS ......

3.可更新视图

-- 没有用到DISTINCT关键字,没有任何聚合函数或GROUP BY 子句，也没用UNION运算符，就是可更新视图，我们可以用它来修改数据 

4.WITH CHECK OPTION子句

-- 首先视图只操作它可以查询出来的数据，对于它查询不出的数据，即使基表有，也不可以通过视图来操作。 
（1）对于update,有with check option，要保证update后，数据要被视图查询出来
（2）对于delete,有无with check option都一样
（3）对于insert,有with check option，要保证insert后，数据要被视图查询出来
（4）对于没有where 子句的视图，使用with check option是多余的

8.存储过程

1.创建一个存储过程

-- DELIMITER 后面设置分割符，后面需要替换为默认的;
-- 存储过程里的每条语句必须加分号
-- 调用存储过程：CALL+存储过程名字（参数）
DELIMITER $$
CREATE  PROCEDURE get_invoices_with_banlance()
BEGIN
	SELECT *
    FROM invoices
	WHERE invoice_total - payment_total > 0;
END$$
DELIMITER ;

2.删除存储过程

DROP PROCEDURE IF EXISTS get_invoices_with_banlance;

3.存储过程参数

-- 创建一个存储过程找出给定客户的发票
-- 后面调用存储过程找出客户id为3的所有发票
DROP PROCEDURE IF EXISTS get_invoices_by_client;

DELIMITER $$
CREATE PROCEDURE get_invoices_by_client
(
	p_client_id INT
)
BEGIN
	SELECT *
    FROM invoices
    WHERE client_id = p_client_id;
END$$
DELIMITER ;

CALL get_invoices_by_client(3);

4.带默认参数的存储过程

-- 传入空值则使用默认的语句
-- （2）和（1）意义相同但（2）更便捷
（1）DROP PROCEDURE IF EXISTS get_payments;

DELIMITER $$
CREATE PROCEDURE  get_payments
(
	p_client_id INT(4),
    p_payment_method_id TINYINT(1)
)
BEGIN
	IF p_client_id IS NOT NULL AND p_payment_method_id IS NOT NULL
		THEN 
			SELECT *
            FROM payments
            WHERE client_id = p_client_id AND payment_method = p_payment_method_id;
	ELSEIF p_client_id IS NOT NULL AND p_payment_method_id IS NULL
		THEN 
			SELECT *
            FROM payments
			WHERE client_id = p_client_id;
	ELSEIF p_client_id IS NULL AND p_payment_method_id IS NOT NULL
		THEN 
			SELECT *
            FROM payments
            WHERE payment_method = p_payment_method_id;
	ELSE
		SELECT *
        FROM payments;
	END IF;
END$$
DELIMITER ;

CALL get_payments(NULL,2);
（2）
DROP PROCEDURE IF EXISTS get_payments;

DELIMITER $$
CREATE PROCEDURE  get_payments
(
	p_client_id INT(4),
    p_payment_method_id TINYINT(1)
)
BEGIN
	SELECT *
    FROM payments
    WHERE
		client_id = IFNULL(p_client_id,client_id) 
		AND payment_method = IFNULL(p_payment_method_id,payment_method);
END$$
DELIMITER ;

CALL get_payments(NULL,2);

5.参数验证

-- BEGIN后面的第一句用于检查输入数据，如果满足了语句则报错
-- 尽量少在存储过程中使用验证逻辑，会造成存储过程变得复杂难以维护
DROP PROCEDURE IF EXISTS get_payments;

DELIMITER $$
CREATE PROCEDURE  get_payments
(
	p_client_id INT(4),
    p_payment_method_id TINYINT(1)
)
BEGIN
	IF p_client_id <= 0 THEN
		SIGNAL SQLSTATE '45000'
			SET MESSAGE_TEXT = 'Invalid input..';
	END IF;
	SELECT *
    FROM payments
    WHERE
		client_id = IFNULL(p_client_id,client_id) 
		AND payment_method = IFNULL(p_payment_method_id,payment_method);
END$$
DELIMITER ;

CALL get_payments(-1,2);
结果如下

6.输出参数

-- 找出未支付发票的客户
-- OUT 后面的参数代表需要传回，默认是IN
-- 用户定义变量要加@
-- 下面将SELECT 选出的值传入 OUT后面的变量 要加 INTO
DELIMITER $$
CREATE PROCEDURE get_unpaid_invoices_for_client
(
	client_id INT,
    OUT invoices_count INT,
    OUT invoices_total DECIMAL(9,2)
)
BEGIN 
	SELECT COUNT(*),SUM(invoice_total)
    INTO invoices_count, invoices_total
    FROM invoices i
    WHERE i.client_id = client_id AND payment_total = 0;
END$$

DELIMITER ;

SET @invoices_count = 0;
SET @invoices_total = 0;
CALL get_unpaid_invoices_for_client(3,@invoices_count,@invoices_total);
SELECT @invoices_count,@invoices_total;

7.变量

-- 6中的变量为用户变量，在存储过程中相应参数前面有OUT（默认为IN）会被回传（用户变量申明前面有@）
-- 还有一种本地变量，作用域为存储过程中，使用DECLARE申明（类似存储过程中的局部变量）
-- 本地变量如果不设置默认值则为NULL
-- 下面利用本地变量计算发票风险值
DELIMITER $$
CREATE PROCEDURE get_risk_factor()
BEGIN
	DECLARE risk_factor DECIMAL(9,2) DEFAULT 0;
    DECLARE invoices_total DECIMAL(9,2);
    DECLARE invoices_count INT;
    
    SELECT COUNT(*),SUM(invoice_total)
    INTO invoices_count,invoices_total
    FROM invoices;
    
    SET risk_factor = invoices_total / invoices_count * 5;
    
    SELECT risk_factor;
END$$
DELIMITER ;

8.函数

-- 函数只能返回单一值，与存储过程有所区别
-- 此函数也是计算发票风险值，只不过要返回
-- BEGIN 前面要有RETURNS 类型
-- 使用时与内置函数类似，传入对应的参数即可获得结果

DELIMITER $$
CREATE FUNCTION get_risk_factor_func
(
	f_client_id INT
)
RETURNS INTEGER
READS SQL DATA
BEGIN 
	DECLARE risk_factor DECIMAL(9,2) DEFAULT 0;
    DECLARE invoices_total DECIMAL(9,2);
    DECLARE invoices_count INT;
    
    SELECT COUNT(*),SUM(invoice_total)
    INTO invoices_count,invoices_total
    FROM invoices i
    WHERE i.client_id = f_client_id;
    
    SET risk_factor = invoices_total / invoices_count * 5;
    
    RETURN risk_factor;
END$$
DELIMITER ;

9.触发器

1.创建触发器
-- 触发器后的操作只能用于指定表之外的表，否则会造成无限循环触发
-- 下面（1）触发器在我们删除付款的时候触发，会减少invoices表中的付款总额
-- （2）触发器在我们插入记录后触发，增加invoices中的付款总额
（1）
DROP TRIGGER IF EXISTS payments_after_delete;

DELIMITER $$
CREATE TRIGGER payments_after_delete
	BEFORE DELETE ON payments
    FOR EACH ROW
BEGIN
	UPDATE invoices
    SET payment_total = payment_total - OLD.amount
    WHERE invoice_id = OLD.invoice_id;
END$$
DELIMITER ;
（2）
DROP TRIGGER IF EXISTS payments_after_insert;

DELIMITER $$
CREATE TRIGGER payments_after_insert
	AFTER INSERT ON payments
    FOR EACH ROW
BEGIN
	UPDATE invoices
    SET payment_total = payment_total + NEW.amount
    WHERE invoice_id = NEW.invoice_id;
    
END $$

DELIMITER ;

SHOW triggers

2.查看触发器

-- SHOW TRIGGERS 列出所有触发器
-- SHOW TRIGGERS LIKE ‘payment%’ 列出以payments开头的所有触发器

3.删除触发器

DROP TRIGGER IF EXISTS payments_after_insert

4.使用触发器进行审计

-- 通过创建一个审计表来记录执行了哪些操作
-- 下面在两个触发器里进行更新审计表
-- 如下图
（1）
DROP TRIGGER IF EXISTS payments_after_insert;

DELIMITER $$
CREATE TRIGGER payments_after_insert
	AFTER INSERT ON payments
    FOR EACH ROW
BEGIN
	UPDATE invoices
    SET payment_total = payment_total + NEW.amount
    WHERE invoice_id = NEW.invoice_id;
    
    INSERT INTO payments_audit
    VALUES (NEW.client_id,NEW.date,NEW.amount,'Insert',NOW());
END $$

DELIMITER ;

SHOW triggers
（2）
DROP TRIGGER IF EXISTS payments_after_delete;

DELIMITER $$
CREATE TRIGGER payments_after_delete
	BEFORE DELETE ON payments
    FOR EACH ROW
BEGIN
	UPDATE invoices
    SET payment_total = payment_total - OLD.amount
    WHERE invoice_id = OLD.invoice_id;
    
    INSERT INTO payments_audit
    VALUES (OLD.client_id,OLD.date,OLD.amount,’Delete',NOW());
END$$
DELIMITER ;

5.事件

-- 通过事件可以让系统不断检查进而执行相应的条件
-- 下面创建一个事件，每一年在一个时间会删除一年之前的审计记录，即只保留最近一年的记录
DROP EVENT IF EXISTS yearly_delete_stale_audit_rows

DELIMITER $$
CREATE EVENT yearly_delete_stale_audit_rows
ON SCHEDULE
	-- AT '2019-05-01'
    EVERY 1 YEAR STARTS '2019-01-01' ENDS '2029-01-01'
DO BEGIN
	DELETE FROM payment_audit
    WHERE action_date < NOW() - INTERVAL 1 YEAR;
END $$
DELIMITER ;

6.查看、删除和更改事件

-- 与之前类似
-- 查看事件，SHOW EVENTS
-- 删除事件，DROP EVENTS IF EXISTS event_name
-- 修改事件，ALTER EVENT +内容
-- 修改事件的内容与创建事件相同，还可以通过修改来禁止或打开一个事件（		ALTER EVENT event_name DISABLE/ENABLE）

这篇关于MySQL学习记录7、8、9- 视图&存储过程&触发器的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！