Mosh｜SQL教程第六弹（视图+存储+函数）

一、视图

1、创建视图CREATE VIEW viewname AS

这样就可以在左侧导航栏看到新增的view了，如果没有的话刷新一下就好了

可以把视图当表格使用

或者

注意：视图不存储数据，数据存储在表中

练习：创建一个视图，叫做客户结余，存储每位客户的结余，包含client_id、name、balance列。结余的计算方法是用发票表中的发票总额减去支付总额

2、修改或删除视图

删除视图：DROP VIEW view_name

 

一般来说我们不直接删除视图，可以使用REPLACE进行修改

CREATE OR REPLACE VIEW clients_balance AS
SELECT c.client_id,c.name,SUM(invoice_total - payment_total) balance
FROM clients c
LEFT JOIN invoices i USING (client_id)
GROUP BY client_id ,name;

3、可更新视图

创建一个不包含distinct、group by等方法的视图

CREATE OR REPLACE VIEW invoices_with_balance AS
SELECT
	invoice_id,
    number,
    client_id,
    invoice_total,
    payment_total,
    invoice_total - payment_total balance,
    invoice_date,
    due_date,
    payment_date
FROM invoices
WHERE (invoice_total - payment_total) > 0
-- 这里无法直接用balance
WITH CHECK OPTION;
-- 防止update或delete操作将行从视图删除

更新视图，比如删除invoice_id = 1

DELETE FROM invoices_with_balance
WHERE invoice_id = 1;

或者 更新invoice_id = 2 的日期延后两天

UPDATE invoices_with_balance
SET due_date = DATE_ADD(due_date,INTERVAL 2 DAY)
WHERE invoice_id = 2;

二、存储过程

1、创建一个存储，BEGIN ... END 中间是存储的内容

-- DELIMITER改变默认分隔符 可以自定义任何没有在SQL用到的字符序列
-- 一般用的是 $$ 
DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
	SELECT * FROM clients; -- 使用多条语句用分号分割 
END$$
DELIMITER ; -- 将分隔符改回 ;

 通过运行代码，可以看到成功生成了一个存储，通过闪电符号可以进行查看数据。

-- 也可以使用CALL来查看
CALL get_clients();

练习：获取有结余的发票（即结余大于0） ,这里可以使用之前创建的视图

DELIMITER $$
CREATE PROCEDURE get_invoices_with_balance()
BEGIN
	SELECT * 
    FROM invoices_with_balance
    WHERE balance > 0;
END$$
DELIMITER ; 

 也可以选择直接通过可视化界面创建，在Stored Procedures上右键选择Create Stored Procedure

在这个窗口就不需要担心分隔符的问题了

2、删除存储过程

DROP PROCEDURE name

DROP PROCEDURE get_invoices_with_balance;

或者保险起见：

DROP PROCEDURE IF EXISTS get_invoices_with_balance;

3、配合参数使用

-- 获取州名和该州的客户
DELIMITER $$
CREATE PROCEDURE get_clients_by_state(state CHAR(2))
BEGIN
	SELECT * 
    FROM clients c
    WHERE c.state = state;
END$$
DELIMITER ; 

可以看到左侧菜单栏出现了新的存储，点击运行会弹出框让我们输入参数

或者通过sql语句查询（不填值会报错，sql中所有参数必须填值）

CALL sql_invoicing.get_clients_by_state('CA');

得到结果如下：

  练习：写一个存储，返回给定客户的发票get_invoices_by_client

DROP PROCEDURE IF EXISTS get_invoices_by_client;

DELIMITER $$
CREATE PROCEDURE get_invoices_by_client(client_id INT) -- 根据clients中client_id的数据类型
BEGIN
	SELECT * 
    FROM invoices i
    WHERE i.client_id = client_id;
END$$
DELIMITER ; 

 带默认值的参数

DROP PROCEDURE IF EXISTS get_clients_by_state;

DELIMITER $$
CREATE PROCEDURE get_clients_by_state(state CHAR(2))
BEGIN
	IF state IS NULL THEN
		SET state = 'CA';
	END IF;
	SELECT * 
    FROM clients c
    WHERE c.state = state;
END$$
DELIMITER ; 

或者

DROP PROCEDURE IF EXISTS get_clients_by_state;

DELIMITER $$
CREATE PROCEDURE get_clients_by_state(state CHAR(2))
BEGIN
	IF state IS NULL THEN
		SET state = 'CA';
	ELSE
		SELECT * 
		FROM clients c
		WHERE c.state = state;
	END IF;
END$$
DELIMITER ; 

或者

DROP PROCEDURE IF EXISTS get_clients_by_state;

-- 获取州名和该州的客户
DELIMITER $$
CREATE PROCEDURE get_clients_by_state(state CHAR(2))
BEGIN
		SELECT * 
		FROM clients c
		WHERE c.state = IFNULL('CA',state);
END$$
DELIMITER ; 

  查询 ,返回 state = 'CA'的客户

CALL get_clients_by_state(NULL);

练习：写一个存储过程get_payments，带两个参数：client_id(int)，payment_method_id(tinyint)

DELIMITER $$
CREATE PROCEDURE get_payments(client_id INT,payment_method_id TINYINT) 
BEGIN
	SELECT * 
    FROM payments p
    WHERE p.client_id = IFNULL(client_id,p.client_id) AND
		  p.payment_method = IFNULL(payment_method_id,p.payment_method);
END$$
DELIMITER ; 

测试：

call sql_invoicing.get_payments(1, NULL);
可以尝试
call sql_invoicing.get_payments(5, NULL);
call sql_invoicing.get_payments(5, 1);
等验证结果

得到结果：

4、参数验证

创建一个存储make_payment，这里我在sql_invoicing数据库右键直接创建了

如果是命令行创建则：

运行并输入参数

命令行形式为：

call sql_invoicing.make_payment(2, 100, '2019-01-01');

查看 sql_invoicing 数据库的 invoices 表，可以看到 invoice_id = 2 的数据成功修改了

为了确保数据准确性和规范性(例如payment_amount不得小于0等等），要进行参数验证

CREATE DEFINER=`root`@`localhost` PROCEDURE `make_payment`(
	invoice_id INT,
	payment_amount DECIMAL(9,2),
    payment_date DATE
)
BEGIN
	IF payment_amount <= 0 THEN
    -- 浏览器搜索‘sqlstate errors’可以查看错误代码，22表示数据异常，22003表示数据超位值
		SIGNAL SQLSTATE '22003' SET MESSAGE_TEXT = '不合理的付款金额';
	END IF;
    
	-- 为给定invoice_id更新发票
    UPDATE invoices i
    SET
		i.payment_total = payment_amount,
        i.payment_date = payment_date
	WHERE i.invoice_id = invoice_id;
END

测试一下在存储金额输入负数

运行得到错误提醒

5、输出参数（OUT 关键词）

USE `sql_invoicing`;
DROP procedure IF EXISTS `get_unpaid_invoices_for_client`;

DELIMITER $$
USE `sql_invoicing`$$
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 ;

查询id为5的结果

得到结果

 6、变量（通过declare声明）

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

运行得到结果

6、函数（Functions）

创建函数计算每位客户的风险因素，通过右键创建

函数具有三种属性

-- 三种属性（可以多选）
DETERMINISTIC 
-- 具有确定性，即一样的输入对象总是会返回一样的输出对象
READS SQL DATA
-- 读取SQL数据
MODIFIES SQL DATA

创建一个函数

CREATE FUNCTION get_risk_factor_for_client
(
	client_id INT
)
RETURNS INTEGER
READS SQL DATA  -- 读取SQL数据

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 = client_id;
    
    -- 计算风险因素
    SET risk_factor = invoices_total / invoices_count * 5;
	RETURN IFNULL(risk_factor,0);
END

 调用函数

USE sql_invoicing;
SELECT 
	client_id,
    name,
    get_risk_factor_for_client(client_id)
FROM clients;

得到结果

 

删除函数

DROP FUNCTION IF EXISTS get_risk_factor_for_client