sql_study_03

小肉丸丸

于 2024-08-15 13:54:40 发布

阅读量584

点赞数 11

分类专栏： MySql 文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/m0_51972677/article/details/141220372

版权

MySql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

第三部分：提高效率——视图、存储过程、函数

视图 Views

CREATE VIEW

创建视图

就把一些经常用到的复杂查询放到虚拟表里面，也算是封装的一种吧，避免每次都重复写SQL，每次使用视图的时候，视图背后的代码都会重复执行

-- CREATE VIEW
CREATE VIEW sales_by_client AS
SELECT
    c.client_id,
    c.name,
    SUM(i.invoice_total) AS total_sales
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY c.client_id, c.name
ORDER BY c.client_id;

DROP VIEW

-- DROP VIEW
DROP VIEW IF EXISTS sales_by_client;

REPLACE VIEW

-- REPLACE VIEW
-- 如果存在则替换，否则创建
CREATE OR REPLACE VIEW sales_by_client AS
SELECT
    c.client_id,
    c.name,
    SUM(i.invoice_total) AS total_sales
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY c.client_id, c.name
ORDER BY c.client_id;

Update,Insert,Delete view

一般来说没有这些东西，视图才可编辑

distinct
aggregate functions （COUNT()、SUM()、AVG()）
group by
union
计算列
join
having
子查询
with check option
非确定确定性函数 ROUND() NOW()

DELETE FROM invoices_with_balance WHERE invoice_id = 1;
UPDATE invoices_with_balance SET payment_date = DATE_ADD(payment_date ,INTERVAL 3 DAY );
-- 视图里有计算列无法插入
INSERT INTO invoices_with_balance (invoice_id, number, client_id, invoice_total, invoice_date, due_date, payment_date) VALUES (DEFAULT, '11-111-1111',1,100.00,0.00,100.00,DATE(NOW()) ,NULL);
SELECT DATE(NOW());

WITH CHECK OPTION

在进行更新和删除操作的时候，with check option会检查更新后的数据是否仍然符合视图的条件,如果不符合，视图怎会拒绝该操作

-- WITH CHECK OPTION
-- 在进行更新和删除操作的时候，with check option会检查更新后的数据是否仍然符合视图的条件,如果不符合，视图怎会拒绝该操作
UPDATE invoices_with_balance SET  payment_total = invoice_total WHERE invoice_id = 3;
CREATE OR REPLACE VIEW invoices_with_balance AS
SELECT
    invoice_id,
    number,
    client_id,
    invoice_total,
    payment_total,
    (invoice_total - payment_total) AS blance,
    invoice_date,
    due_date,
    payment_date
FROM invoices
WHERE (invoice_total - payment_total) > 0
WITH CHECK OPTION;

strength of the view
1. 简单化复杂查询
  - 视图可以将复杂的查询逻辑封装起来，使得以后调用时不需要重复编写复杂的SQL语句。例如，一个涉及多表连接和聚合操作的复杂查询，可以通过视图简化为一个简单的查询调用。
2. 权限控制
  - 视图可以用于细化数据访问权限。你可以授予用户对视图的访问权限，而不是直接对基础表的权限，从而限制用户只能访问和操作部分数据。例如，你可以创建一个只包含敏感信息子集的视图，这样用户通过视图只能访问特定的列或记录，而不能接触到基础表中的所有数据。
3. 数据抽象和封装
  - 视图可以帮助隐藏数据的实际存储结构，提供一种数据抽象层。这样即使基础表的结构发生变化，只要视图的结构保持不变，依赖视图的应用程序就不需要修改。这种抽象可以提高系统的灵活性和稳定性。
4. 统一的数据访问接口
  - 在一些情况下，不同的应用或用户可能需要对数据有不同的视角。视图可以提供多个不同的数据视角，而不用改变底层的数据结构。这样可以为不同的用户群体或应用场景提供统一的接口。
5. 预计算和缓存
  - 有些数据库系统允许对视图进行物化（Materialized View），即将视图的结果缓存起来，以加快查询速度。虽然MySQL没有直接的物化视图，但通过其他机制或插件也可以实现类似的效果。
6. 分解单一表
  - 在某些场景下，基础表可能包含大量数据，但特定应用或用户只需要其中的一部分。通过视图，你可以创建多个视图，每个视图只包含某些列或某些行，这样可以减少数据传输量和处理复杂度。

存储过程 Stored Procedures

CREATE PROCEDURE
1. MySQL默认分隔符为;为了避免存储过程在sql语句中结束，所以要改默认分隔符（国际惯例$$,//）。在MySQL官方工作台要这么写，在其他DMBS中可能做了优化不用改也可以，直接用;，我这里用的是DataGrip，他的结果还是;作为分隔符。在存储过程结束后要将分隔符改回来
```
-- Store Procedure
-- 默认分隔符为；在mysql中要修改分隔符，在其他DBMS中不用，可能软件做了优化
DROP PROCEDURE IF EXISTS get_clients;
DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
   SELECT * FROM clients;-- 这里结束
END$$
DELIMITER ;
```
CALL PROCEDURE
```
-- Call procedure
CALL get_clients();
```

DROP PROCEDURE

DROP PROCEDURE IF EXISTS get_clients();

PROCEDURE PARAMS

带参数的存储过程

存储过程名(参数名参数类型)

-- procedure params
CREATE PROCEDURE get_clients_by_state(state CHAR(2))
BEGIN
    SELECT * FROM clients c
        WHERE c.state = state;
END;
CALL get_clients_by_state('CA');

带默认参数

-- params with default values
-- 使用IF
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;
CREATE PROCEDURE get_clients_by_state(state CHAR(2))
BEGIN
    IF state IS NULL THEN
        SELECT * FROM clients;
    ELSE
        SELECT * FROM clients c
        WHERE c.state = state;
    END IF;
END;
-- 使用IFNULL
CREATE PROCEDURE get_clients_by_state(state CHAR(2))
BEGIN
    SELECT * FROM clients c
    WHERE c.state = IFNULL(state,c.state);
END;

写到这里突然想到一个问题，存储过程也是将复杂查询封装，视图也是，那他们有什么区别🤔

嗯总的来说

• 视图是一个简化的虚拟表，用于封装复杂的查询，并提供数据抽象和访问控制。本质上还是查数据库中的表。

• 存储过程 是一个可以包含复杂业务逻辑的预编译代码块，能够处理查询、数据操作、事务、条件判断等。它是动态的，可以接受输入参数并执行多种操作存储过程主体里面可以有好多好多sql，减少用户和数据库表的联系。

-- Exercise
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;
CALL get_payments(NULL,2);

参数非法

SINGNAL SQLSTATE

标注sql状态，类似Java中的抛出异常，设置错误码
SET MESSAGE_TEXT

设置提醒信息

-- parameters validation
-- signal sqlstate 用来标注sql状态，set message_text设置提醒文本
DROP PROCEDURE IF EXISTS make_payment;
CREATE PROCEDURE make_payment(p_invoice_id INT,p_payment_amount DECIMAL(9,2),p_payment_date DATE)
BEGIN
    IF p_payment_amount<=0 THEN
        SIGNAL SQLSTATE '22003'
            SET MESSAGE_TEXT = 'Invalid p_payment_amount';
    END IF;
    UPDATE invoices
        SET payment_total = p_payment_amount,
            payment_date = p_payment_date
    WHERE invoice_id = p_invoice_id;
END;
CALL make_payment(2,-100,DATE(NOW()));

Output Parameters

将存储过程的结果存入到输出参数中，在存储过程结束后，自动销毁，所以要一个变量来接收

-- Output Parameters
DROP PROCEDURE IF EXISTS get_unpaid_invoices_for_client;
CREATE PROCEDURE get_unpaid_invoices_for_client
    (
        p_client_id INT,
        OUT p_invoices_count INT,
        OUT p_invoices_total DECIMAL(9,2)
    )
BEGIN
    SELECT COUNT(*),SUM(invoice_total)
    INTO p_invoices_count,p_invoices_total
    FROM invoices
        WHERE client_id = p_client_id
        AND payment_total = 0;
END;

-- 设置接收结果的临时变量
SET @p_invoices_count = 0;
SET @p_invoices_total = 0;
CALL get_unpaid_invoices_for_client(5,@p_invoices_count,@p_invoices_total);
SELECT @p_invoices_count,@p_invoices_total;

Location Variable

在存储过程体中声明（DECLARE）只能在存储过程中使用，过程结束，自动销毁

-- location variable
-- 只能在存储过程中使用，过程结束，自动销毁
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 将结果存入到两个变量中
    INTO invoices_count,invoices_total
    FROM invoices;
    SET risk_factor = invoices_total/invoices_count*5;
    SELECT risk_factor;
END;
CALL get_risk_factor();

函数 Functions

CREATE FUNCTION

函数修饰符（没有相关内容就不要写）
1. RETURN INTEGER 指定函数的返回类型。
2. DETERMINISTIC 声明函数是确定性的，即对于相同的输入参数，函数始终返回相同的结果。
3. READS SQL DATA 声明函数会读取数据库中的数据，但不会修改数据。
4. MODIFIES SQL DATA 声明函数会对数据库数据进行修改。

-- create functions
DROP FUNCTION IF EXISTS get_risk_factor_for_client;
CREATE FUNCTION get_risk_factor_for_client(p_client_id INT)
RETURNS INTEGER -- 函数返回参数类型
DETERMINISTIC -- 返回值确定
READS SQL DATA  -- 需要读数据
MODIFIES 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 = p_client_id;
    SET risk_factor = invoices_total/invoices_count*5;
    RETURN IFNULL(risk_factor,0);
END;

调用函数

-- 调用函数
SELECT
    client_id,
    name,
    get_risk_factor_for_client(client_id) AS risk_factor
FROM clients;

小肉丸丸

关注

11
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
sql_study_03

是一个可以包含复杂业务逻辑的预编译代码块，能够处理查询、数据操作、事务、条件判断等。它是动态的，可以接受输入参数并执行多种操作存储过程主体里面可以有好多好多sql，减少用户和数据库表的联系。在进行更新和删除操作的时候，with check option会检查更新后的数据是否仍然符合视图的条件,如果不符合，视图怎会拒绝该操作。是一个简化的虚拟表，用于封装复杂的查询，并提供数据抽象和访问控制。本质上还是查数据库中的表。标注sql状态，类似Java中的抛出异常，设置错误码。存储过程名(参数名参数类型)
复制链接

扫一扫