本文详细介绍了SQL中的视图创建、更新及WITH CHECK OPTION子句，讲解了存储过程的创建、删除、参数使用，包括带默认参数的存储过程。接着阐述了触发器的创建、查看、删除及其在数据审计中的应用，并简述了事件的创建和管理，用于定期执行任务。这些内容有助于提升SQL数据库管理和数据操作的效率。
学习内容来自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）
扫一扫
946
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
