SQL学习day4

8.视图

（1）创建视图

目的：用视图来拯救繁琐的查询【能再使用查询or子查询】【可以大大简化查询】

用法：CREATE VIEW 命名 AS

           SELECT

            FROM

【注：VIEW是个虚拟表，不存储数据】

练习：创建一个视图clients_balance，显示每位客户的结余，包括id name balance

USE invoicing;

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

（2）更改或删除视图

1.删除视图

用法：DROP VIEW 名字 

2.更改视图

用法： CREATE OR REPLACE VIEW 名字 AS

（3）可更新视图

定义：没有DISTINCT、聚合函数、GROUP BY / HAVING 、UNION的视图称为可更新视图

用法：某些表没有修改数据的权限，可以使用可更新视图来修改数据

可以在INSERT\UPDATE\DELETE语句中使用这类视图

用法：DELETE FROM 表名

           WHERE 条件

            UPDATE 表名

            SET 要更新的内容

            WHERE 条件

（4）WITH CHECK OPTION子句

目的：防止UPDATE/DELETE语句将行从视图中删除

用法：CREATE OR REPLACE VIEW 命名 AS

           SELECT

            FROM

           WITH CHECK OPTION

（5）视图的优点

简化查询；减少数据库设计改动的影响（只需要修改视图）；使用视图限制基础表访问

9.存储过程和函数

（1）存储过程

定义：包含一堆SQL代码的数据库对象

使用存储过程来存储和管理代码；执行SQL代码更快；数据安全性

（2）创建一个存储过程

用法：DELIMITER $$ #将分隔符改为$$

           CREATE PROCEDURE 名字（）

           BEGIN 内容（存储过程的主体，用分号终结每句）

           END$$

           DELIMITER ; #将分隔符改回分号

调用或者执行这个过程：CALL 名字()

练习：创建一个存储过程来返回所有balance大于0的发票

USE invoicing;
DELIMITER $$
CREATE PROCEDURE get_invoices_with_balance()
BEGIN
	SELECT
		  invoice_id,
		  invoice_total-payment_total AS balance
	FROM invoices
	WHERE (invoice_total-payment_total)>0;
END$$

DELIMITER ;

(3)使用MySQL工作台创建存储过程

右键点击“存储过程文件夹”→创建一个存储过程

【不需要改分隔符】

（4）删除存储过程

用法：DROP PROCEDURE IF EXISTS  名字

【创建存储过程的一个基本架构】

DROP PROCEDURE IF EXISTS  名字1

DELIMITER $$ #将分隔符改为$$

CREATE PROCEDURE 名字1（）

BEGIN 内容（存储过程的主体，用分号终结每句）

END$$

DELIMITER ; #将分隔符改回分号

（5）在存储过程 中添加参数

用法：使用参数为存储过程传递

DROP PROCEDURE IF EXISTS  名字1

DELIMITER $$ #将分隔符改为$$

CREATE PROCEDURE 名字1（参数1 参数1的类型，参数2 参数2的类型）

BEGIN 内容（存储过程的主体，用分号终结每句）

END$$

DELIMITER ; #将分隔符改回分号

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

USE invoicing;
DELIMITER $$
CREATE PROCEDURE get_invoices_by_client(client_id INT)
BEGIN
	SELECT *
	FROM invoices i
	WHERE i.client_id=client_id;
END$$

DELIMITER ;

CALL get_invoices_by_client(5)

（6）带默认值的参数

目的：为参数配置默认值

用法：使用IFNULL函数

练习：写一个存储过程，需要带两个参数：client id （INT）和付款方式id(TINYINT)。如果是空值null的话返回所有记录，缺哪个就返回哪个的记录

USE invoicing;
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 get_payments(NULL,1)

（7）参数验证

目的：保证数据正确【尽量利用最少的验证逻辑】

用法：用IF函数

IF 参数不满足的条件 THEN

     SIGNAL SQLSTATE '错误代码' SET MESSAGE_TEXT='错误的原因'；

END IF；

(8)输出参数

目的：运用存储过程返回参数的值

用法：

CREATE PROCEDURE 名字（参数1  参数类型，OUT 参数2参数类型）

BEGIN

        SELECT

        INTO 参数2

        FROM

END

（9）变量

用户变量（USER VARIABLE）

用法：SET @变量名=取值

本地变量（LOCAL VARIABLE）

【在存储过程或函数内定义的，只存在于存储过程中】

使用DECLARE语句声明变量

用法：DECLARE 变量名称 变量类型 （DEFAULT 默认值）

【默认值是optional的】

使用SET语句来设置变量的值

用法：变量名=表达式

（10）函数

注：函数只能返回单一值

CREATE FUNCTION 名字（参数 参数类型）

RETURN 返回值的类型

函数属性（DETERMINISTIC/READS SQL DATA/MODIFIES SQL DATA）

BEGIN 主体

             RETURN 返回值

END

用DROP FUNCTION IF EXISTS 函数名来删除函数

10.触发器

定义：在插入、更新和删除语句前后自动执行的一堆SQL代码

使用触发器增强数据一致性

（1）定义触发器

用法：

DELIMITER $$

CREATE TRIGGER 名字[表名_AFTER/BEFORE_INSERT/UPDATE/DELETE]

       AFTER/BEFORE INSERT/UPDATE/DELETE ON   表名

        FOR EACH ROW

BEGIN 主体（UPDATE 表2 SET 表达式 WHERE 条件）

END $$

DELIMITER ; #将分隔符改回分号

【用NEW可以获取新加的那一行的数据，NEW.列名可以获取对应列的新数据】

【修改除这张表外的其他表的数据】

练习：创造一个减少付款时总额也会随之减少的trigger

DELIMITER $$
CREATE TRIGGER payments_after_delete
	AFTER 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）查看触发器

SHOW TRIGGERS (LIKE '表达式')

【加LIKE可以筛选你想看的trigger】

（3)删除触发器

DROP TRIGGERS IF EXISTS 触发器名字

（4）使用触发器进行审核

目的：可以知道谁在什么时候做了什么操作

DELIMITER $$

CREATE TRIGGER 名字[表名_AFTER/BEFORE_INSERT/UPDATE/DELETE]

       AFTER/BEFORE INSERT/UPDATE/DELETE ON   表名

        FOR EACH ROW

BEGIN 主体（UPDATE 表2 SET 表达式 WHERE 条件）

           INSERT INTO 审核表

           VALUES（）

END $$

DELIMITER ; #将分隔符改回分号

（5）事件events

定义：根据计划执行的任务或一堆SQL代码

目的：自动化数据库维护任务

打开事件调度器：SHOW VARIABLES LIKE 'event%'；

会找到 event_scheduler

创建事件：

用法：

DELIMITER $$

CREATE EVENT 事件名（频率_操作_目的）

ON SCHEDULE

        AT '日期' （一次性）

        EVERY 频率 （STARTS '日期' ENDS ’日期‘）

DO BEGIN 主体

END$$

DELIMITER ; #将分隔符改回分号

（6）查看、删除和更改事件

查看：SHOW EVENTS；

删除：DROP EVENT IF EXISTS 事件名称

更改：ALTER EVENT

【ALTER的语法和create的一样】

ALTER EVENT 事件名称 DISABLE/ENABLE #禁用或启用事件