《MySQL必知必会》个人实现全记录（6）—

本文链接：https://blog.csdn.net/2301_79535379/article/details/147720488

《MySQL必知必会》个人实现全记录（6）——22~26章

前言

上一节链接：《MySQL必知必会》个人实现全记录（5）——15~18章-CSDN博客

本章博客内容包括：22章使用视图、23章使用存储过程、24章使用游标、25章使用触发器、26章管理事务处理，共5章内容。个人感觉是非常重要的内容，面试的时候可能被问到，所以认真学一下。

第22章：使用视图

本章将介绍视图究竟是什么，它们怎样工作，何时使用它们。我们还将看到如何利用视图简化前面章节中执行的某些SQL操作。

视图的介绍

视图的本质是一个查询结果，不包含任何列或数据，下面是视图的一些常见应用。

重用SQL语句。
简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
使用表的组成部分而不是整个表。
保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

下面是关于视图创建和使用的一些最常见的规则和限制。

与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
对于可以创建的视图数目没有限制。
为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
视图不能索引，也不能有关联的触发器或默认值。
视图可以和表一起使用。例如，编写一条联结表和视图的SELECT 语句。

使用视图

视图用CREATE VIEW语句来创建。
使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
用DROP删除视图，其语法为DROP VIEW viewname;。
更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。

视图有以下常见作用：简化复杂联结、重新格式化检索出的数据、去除不想要的属性、使用视图于计算字段，看例子

#使用视图简化复杂的联结
CREATE VIEW
    productcustomers AS
SELECT
    cust_name,cust_contact,prod_id
FROM
    customers,orders,orderitems
WHERE
    customers.cust_id=orders.cust_id
AND orderitems.order_num = orders.order_num;
#使用视图重新格式化数据
CREATE VIEW 
    vendorlocation AS
SELECT
    CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title
FROM
    vendors
ORDER BY
    vend_name;
#使用视图过滤不想要的数据
CREATE VIEW 
    customeremaillist AS
SELECT 
    cust_id, cust_name, cust_email
FROM 
    customers
WHERE 
    cust_email IS NOT NULL;
#使用视图和计算字段
CREATE VIEW 
    orderitemsexpanded AS
SELECT 
    order_num,prod_id,quantity,item_price,
    quantity*item_price AS expanded_price
FROM 
    orderitems;

更新视图

原则上我们只对视图使用检索语句，因为视图的本质就是检索结果。我们可以对视图使用insert、update、delete语句，这种操作会更新基表。

但是，并非所有视图都是可更新的。基本上可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。这实际上意味着，如果视图定义中有以下操作，则不能进行视图的更新：

分组（使用GROUP BY和HAVING）；
联结；
子查询；
并；
聚集函数（Min()、Count()、Sum()等）；
DISTINCT；
导出（计算）列。

第23章：使用存储过程

本章介绍什么是存储过程，为什么要使用存储过程以及如何使用存储过程，并且介绍创建和使用存储过程的基本语法。

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件（脚本文件），虽然它们的作用不仅限于批处理。

使用存储过程有3个主要的好处，即简单、安全、高性能。

创建、执行、删除存储过程

例子——一个返回产品平均价格的存储过程。

#创建存储过程
CREATE PROCEDURE productpricing()
BEGIN
    SELECT AVG(prod_price) AS priceaverage
    FROM products;
END;
#使用存储过程
CALL productpricing();
#删除存储过程
DROP PROCEDURE IF EXISTS productpricing;

使用参数

引入变量概念：变量（variable）是内存中一个特定的位置，用来临时存储数据。所有MySQL变量都必须以@开始。

先放一段代码

CREATE PROCEDURE productpricing(
    OUT plow DECIMAL(8,2),
    OUT phigh DECIMAL(8,2),
    OUT pavg DECIMAL(8,2)
)
BEGIN
    SELECT MIN(prod_price)
    INTO plow
    FROM products;

    SELECT MAX(prod_price)
    INTO phigh
    FROM products;

    SELECT AVG(prod_price)
    INTO pavg
    FROM products;
END;

此存储过程接受3个参数：pl存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。每个参数必须具有指定的类型，这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。MySQL支持IN（传递给存储过程）、OUT（从存储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参数。存储过程的代码位于BEGIN和END语句内，如前所见，它们是一系列 SELECT语句，用来检索值，然后保存到相应的变量（通过指定INTO关键字）。

对应的调用语句

CALL productpricing(@pricelow,@pricehigh,@priceaverage);
SELECT @pricehigh,@pricelow,@priceaverage;

建立智能存储过程

先看代码

#智能存储过程
--Name: ordertotal
--Parameters: onumber = order number
--taxable = 0 if not taxable, 1 if taxable
--ototal = order total variable.
CREATE PROCEDURE ordertotal(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally addingtax'
BEGIN
    -- Declare variable for total
    DECLARE total DECIMAL(8,2);
    -- Declare tax percentage
    DECLARE taxrate INT DEFAULT 6;
    -- Get the order total
    SELECT Sum(item_price*quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO total;
    -- Is this taxable?
    IF taxable THEN
        -- Yes, so add taxrate to the total
        SELECT total+(total/100*taxrate) INTO total;
    END IF;
    -- And finally, save to out variable
    SELECT total INTO ototal;
END;

COMMENT关键字

本例子中的存储过程在CREATE PROCEDURE语句中包含了一个COMMENT值。它不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示。

DECLARE要求指定变量名和数据类型，它也支持可选的默认值（这个例子中的taxrate的默认被设置为6%）。

第24章：使用游标

游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

不像多数DBMS，MySQL游标只能用于存储过程（和函数）。

创建游标、打开和关闭游标

游标关键字：cursor，用declare像声明变量那样声明游标即可

打开游标：open

关闭游标：close，如果不明确关闭，在end时自动关闭

在一个游标关闭后，如果没有重新打开，则不能使用它。但是，使用声明过的游标不需要再次声明，用OPEN语句打开它就可以了。

CREATE PROCEDURE processorders()
BEGIN
    #创建游标
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    #打开游标
    OPEN ordernumbers;
    #关闭游标
    CLOSE ordernumbers;
END;

使用游标数据

来看一个例子

CREATE PROCEDURE processorders()
BEGIN
    DECLARE o INT;
    DECLARE done BOOLEAN DEFAULT 0;

    #创建游标
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    #设置终止条件
    DECLARE CONTINUE HANDLER 
        FOR SQLSTATE '02000' 
        SET done=1;
    #打开游标
    OPEN ordernumbers;
    REPEAT
        #使用游标数据
        FETCH ordernumbers INTO o;
    #直到done变为1时重复终止
    UNTIL done END REPEAT;
    #关闭游标
    CLOSE ordernumbers;
END;

新引入了CONTINUE HANDLER 和repeat-end repeat

CONTINUE HANDLER是在条件出现时被执行的代码。这里，它指出当SQLSTATE '02000’出现时，SET done=1。SQLSTATE '02000’是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。

除这里使用的REPEAT语句外，MySQL还支持循环语句，它可用来重复执行代码，直到使用LEAVE语句手动退出为止。通常REPEAT语句的语法使它更适合于对游标进行循环。

第25章：使用触发器

触发器是MySQL响应delete、update、insert语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）。

只有表才支持触发器，视图不支持（临时表也不支持）。

现在最好是在数据库范围内使用唯一的触发器名。

创建和删除触发器

在创建触发器时，需要给出4条信息：

唯一的触发器名；
触发器关联的表；
触发器应该响应的活动（DELETE、INSERT或UPDATE）；
触发器何时执行（处理之前或之后）。

来看例子：

#创建触发器
CREATE TRIGGER newproduct
AFTER INSERT ON products
FOR EACH ROW 
#SELECT 'Product added';
BEGIN
    SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'Product added';
END;
#删除触发器
DROP TRIGGER newproduct;

其他注意事项：

每个表最多支持6个触发器（每条INSERT、UPDATE 和DELETE的之前和之后）。

单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

如果BEFORE触发器失败，则MySQL将不执行请求的操作。此外，如果BEFORE触发器或语句本身失败，MySQL 将不执行AFTER触发器（如果有的话）。

触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。

使用触发器

INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点：

在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；
在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；
对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT 执行之后包含新的自动生成值。

DELETE触发器在DELETE语句执行之前或之后执行。需要知道以下两点：

在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
OLD中的值全都是只读的，不能更新。

UPDATE触发器在UPDATE语句执行之前或之后执行。需要知道以下几点：

在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；
在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；
OLD中的值全都是只读的，不能更新。

第26章：管理事务处理

InnoDB引擎支持事务处理而MyISAM不支持。

事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。

使用ROLLBACK

MySQL的ROLLBACK命令用来回退（撤销）MySQL语句。

来看例子：

#使用ROLLBACK
SELECT * FROM orderitems;
START TRANSACTION;
DELETE FROM orderitems;
SELECT * FROM orderitems;
ROLLBACK;
SELECT * FROM orderitems;

可以验证撤销功能。

事务处理用来管理INSERT、UPDATE和 DELETE语句。你不能回退SELECT语句。（这样做也没有什么意义。）你不能回退CREATE或DROP操作。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销。

使用COMMIT

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。输入但是，在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句，如下所示：

#使用COMMIT
START TRANSACTION;
DELETE FROM orderitems WHERE order_num=20010;
DELETE FROM orders WHERE order_num=20010;
COMMIT;

在这个例子中，从系统中完全删除订单20010。因为涉及更新两个数据库表orders和orderItems，所以使用事务处理块来保证订单不被部分删除。最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用，但第二条失败，则DELETE不会提交（实际上，它是被自动撤销的）。

当COMMIT或ROLLBACK语句执行后，事务会自动关闭（将来的更改会隐含提交）。

使用保留点SAVEPOINT

存档点，合理情况下越多越好，为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。这些占位符称为保留点。

保留点在事务处理完成（执行一条ROLLBACK或 COMMIT）后自动释放。自MySQL 5以来，也可以用RELEASE SAVEPOINT明确地释放保留点。

更改默认提交行为

autocommit标志决定是否自动提交更改，不管有没有COMMIT 语句。设置autocommit为0（假）指示MySQL不自动提交更改（直到autocommit被设置为真为止）。

标志为连接专用，autocommit标志是针对每个连接而不是服务器的。

结语

《Mysql必知必会》这本书的学习就到此为止了，但是我们数据库的学习才刚刚开始。我的数据库部分计划是：看一本mysql语法书（就是这本）、看一本kv类型数据库语法书、看一本关系型数据库原理、看一本kv型数据库实现原理（然后可能会去做我自己的kv数据库项目）。

关于这本书和目前学到的知识点：书还是简明易懂的，让我快速上手了数据库。已经学了很多mysql语句语法，但是还有很多知识点需要学习，比如各种函数和语句，比如控制过程，比如索引（关系到原理部分），后续有时间可能会写这本书的总结和补记博客吧。

5.7号我有一门课的结课考试，这两天忙着复习。最近还需要完成我们学校的数据库实验报告，还是很忙的。

目前操作系统是做到了12章末尾，计划是明天再做一部分，13章是硬盘驱动比较短，14章是搭建文件系统，15章是完成系统交互。我争取在5.15前完成我们整个操作系统吧。

《MySQL必知必会》个人实现全记录（6）——22~26章

《MySQL必知必会》个人实现全记录（6）——22~26章

文章目录

前言

第22章：使用视图

视图的介绍

使用视图

更新视图

第23章：使用存储过程

创建、执行、删除存储过程

使用参数

建立智能存储过程

第24章：使用游标

创建游标、打开和关闭游标

使用游标数据

第25章：使用触发器

创建和删除触发器

使用触发器

第26章：管理事务处理

使用ROLLBACK

使用COMMIT

使用保留点SAVEPOINT

更改默认提交行为

结语