Mysql 基础知识(二)

摘要

Mysql Version：5.7.36
官方文档：https://dev.mysql.com/doc/refman/ (opens new window)
样例表：下载

一：视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。

为什么使用视图？

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

视图的规则和限制：

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

性能问题：

因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。

例子：列出订购物品TNT2的所有客户（下面语句使用内连接查询）

SELECT cust_name, cust_contact FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';

1.1 新增视图

视图用 CREATE VIEW 语句来创建。

使用 SHOW CREATE VIEW viewname 查看创建视图的语句。

用上面例子，创建一个名为 productcustomers 的视图

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;

如果查询所有，返回已订购了任意产品的所有客户列表

SELECT * FROM productcustomers;

检索订购了产品TNT2的客户

SELECT * FROM productcustomers WHERE prod_id = 'TNT2';

MySQL处理此查询时，它将指定的WHERE子句添加到视图查询中已有的WHERE子句中，以便正确过滤数据

1.2 修改视图

更新视图时，可以先用DROP再用CREATE，也可以直接用 CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。

CREATE OR REPLACE VIEW productcustomers AS
	SELECT cust_name, cust_contact 
	FROM customers, orders, orderitems
	WHERE customers.cust_id = orders.cust_id
	AND orderitems.order_num = orders.order_num;

1.3 删除视图

用DROP删除视图，其语法为 DROP VIEW viewname

DROP VIEW productcustomers;

1.4 操作视图

可以对视图执行 SELECT 操作，过滤和排序数据，将视图连接到其他视图或表。甚至能添加和更新数据（添加和更新数据存在某些限制）

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

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

二：存储过程

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

使用原因：

• 通过把处理封装在容易使用的单元中，简化复杂的操作；
• 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性；
• 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化；
• 提高性能。因为使用存储过程比使用单独的SQL语句要快；
• 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

高度总结，存储过程3个主要好处，即简单、安全、高性能。

缺陷：

• 一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验；
• 可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。（MySQL将编写存储过程的安全和访问与执行存储过程的安全和访问区分开来）

2.1 创建存储过程

CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage FROM products;
END;

说明：创建存储过程名为productpricing；第一行 () 内，可以定义参数，BEGIN和END用来限定存储过程体。

MySQL默认语句分隔符为;，如果命令行要解释存储过程自身内的 ; 符号，则它们最终不会存储过程中的成分，这会使存储过程中的SQL出现语句错误。
解决办法是临时更改命令行实用程序的语句分隔符。
DELIMITER // 告诉命令行程序使用 // 作为新的语句结束分隔符。这样，存储过程体内的;仍然保持不动，并且正确地传递给数据库引擎。最后，为恢复为原来的语句分隔符可使用 DELIMITER ;

2.2 使用存储过程

执行上面例子中创建的存储过程

CALL productpricing();

为存储过程实际上是一种函数，所以存储过程名后需要有()符号（即使不传递参数也需要）

2.3 删除存储过程

删除上面例子中创建的存储过程

-- 注意：这里没有衔接括号
DROP PROCEDURE productpricing;

如果指定的存储过程不存在，则上面语句会报错，如果想忽略，可以使用 IF EXISTS

DROP PROCEDURE IF EXISTS productpricing;

2.4 使用参数

productpricing只是一个简单的存储过程，它简单地显示SELECT语句的结果。一般，存储过程并不显示结果，而是把结果返回给你指定的变量。

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

CREATE PROCEDURE productpricing(
	OUT p1 DECIMAL(8,2),
	OUT ph DECIMAL(8,2),
	OUT pa DECIMAL(8,2)
)
BEGIN
	SELECT Min(prod_price) INTO p1 FROM products;
	SELECT Max(prod_price) INTO ph FROM products;
	SELECT Avg(prod_price) INTO pa FROM products;
END;

• OUT：指相应的参数用来从存储过程传出值（返回给调用者），
• IN：传递给存储过程
• INTO：保存到相应的变量

形参必须指定类型，这里指定 DECIMAL(8,2) 类型

执行存储过程：

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

在调用时，这条局域并不会显示任何数据。它返回之后可以通过 SELECT 查询

SELECT @pricelow, @pricehigh, @priceaverage;

例子：得到某订单总价

CREATE PROCEDURE ordertotal(
	IN onumber INT,
	OUT ototal DECIMAL(8,2)
)
BEGIN
	SELECT Sum(item_price * quantity) FROM orderitems 
	WHERE order_num = onumber 
	INTO ototal;
END;

执行存储过程：

CALL ordertotal(20005, @total);

SELECT @total;

2.5 复杂场景

存储过程只有包含业务规则和智能处理时，才能真正体现其价值。

场景：需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客（或许是所在管区的顾客）。把步骤拆分，需要如下三个步骤：

1. 获取合计；
2. 把营业税有条件地添加到合计；
3. 返回合计（带或不带税）。

-- Name: ordertotal
-- Parameters: onumber = 订单号
--             taxable = 0 不征税，1 征税
--             otatal  = 订单总变量

CREATE PROCEDURE ordertotal(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
) COMMENT '获取订单总额，可选择加税'
BEGIN
    -- 为合计声明变量
    DECLARE total DECIMAL(8,2);
    -- 为营业税声明变量
    DECLARE taxrate INT DEFAULT 6;

    -- 得到合计
    SELECT Sum(item_price*quantity) FROM orderitems
    WHERE order_num = onumber INTO total;
    
    -- 是否征税
    IF taxable THEN
        -- 征收，增加营业税入合计
        SELECT total + (total/100*taxrate) INTO total;
    END IF;
    
    -- 保存输出的变量
    SELECT total INTO ototal;
END;

• COMMENT：备注，说明
• DECLARE：定义了局部变量，需要指定变量名和数据类型，同时支持可选默认值
• IF...THEN..END IF：if条件语句，同时支持ELSEIF 和 ELSE

验证：

-- 执行存储过程
CALL ordertotal(20005, 0, @total);

-- 获取合计
SELECT @total;

-- 执行存储过程
CALL ordertotal(20005, 1, @total);

-- 获取合计
SELECT @total;

2.6 检查存储过程

显示创建存储过程的语句

SHOW CREATE PROCEDURE ordertotal;

列出所有存储过程：(包含何时、由谁创建等详细信息)

SHOW PROCEDURE STATUS;

上面信息较多，为了限制输出，可以指定 LIKE 指定一个过滤模式

SHOW PROCEDURE STATUS LIKE 'ordert%';

三：游标

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

只能用于存储过程（和函数），可根据需要频繁地打开和关闭游标

3.1 创建游标

DECLARE 命名游标

下面用DECLARE定义了一个名ordernumbers游标

CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;

存储过程处理完成后，游标就消失（因为它局限于存储过程）。

3.2 打开和关闭游标

打开游标

OPEN ordernumbers;

关闭游标

CLOSE ordernumbers;

隐式关闭：如果没有明确关闭游标，MySQL将会在到达END语句时自动关闭它。

修改前面的例子：

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

3.3 使用游标数据

FETCH：访问它的每一行，指定检索什么数据（所需的列），检索出的数据存储的地方，移动内部指针读取下一行。

例子一：从游标中检索单个行（第一行）

CREATE PROCEDURE processorders() 
BEGIN
	-- 声明变量
	DECLARE o INT;

	-- 声明游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	-- 打开游标
	OPEN ordernumbers;
	
	-- 得到合计
	FETCH ordernumbers INTO o;
	
	-- 关闭游标
	CLOSE ordernumbers;
END;

例子二：从游标中检索数据（第一行到最后一行）

CREATE PROCEDURE processorders() 
BEGIN
	-- 声明变量
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;

	-- 声明游标
	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;
	
	-- 结束循环
	UNTIL done END REPEAT;
	
	-- 关闭游标
	CLOSE ordernumbers;
END;

其中 DECLARE CONTINUE 它是在条件出现时被执行的代码，指出当 SQLSTATE '02000' 出现时，SET done=1。SQLSTATE '0200' 是一个未找到条件，当REPEAT由于没有更多的行循环而不能继续时，出现这个条件。其他错误代码可查看 https://dev.mysql.com/doc/refman/8.0/en/error-handling.html (opens new window)

DECLARE语句的次序
DECLARE语句的发布存在特定的次序。用DECLARE语句定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。

例子三：对取出来的数据进行处理

CREATE PROCEDURE processorders() 
BEGIN
	-- 声明变量
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	DECLARE t DECIMAL(8,2);

	-- 声明游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	-- 申明继续处理
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	
	-- 创建一个表去存储结果
	CREATE TABLE IF NOT EXISTS ordertotals
		(order_num INT, total DECIMAL(8,2));
	
	-- 打开游标
	OPEN ordernumbers;
	
	-- 循环遍历所有行
	REPEAT
	
		-- 得到合计
		FETCH ordernumbers INTO o;
		
		-- 得到这个订单总数
		CALL ordertotal(o, 1, t);
		
		-- 插入数据
		INSERT INTO ordertotals(order_num, total)
		VALUES(o, t);
	
	-- 结束循环
	UNTIL done END REPEAT;
	
	-- 关闭游标
	CLOSE ordernumbers;
END;

创建完上面的存储过程，执行完后，查看ordertotals表，可以查看到数据

SELECT * FROM ordertotals;

四：触发器

某些 MySQL语句在事件发生时自动执行。

触发器能响应的语句有以下三类：

• DELETE
• INSERT
• UPDATE

4.1 创建触发器

触发器名必须在每个表中保持唯一，但不是在每个数据库中唯一。但最好是在数据库范围内使用唯一的触发名。

规则：

1. 仅支持表，只有表才支持触发器，视图不支持（临时表也不支持）
2. 每个表最多支持6个触发器（每条INSERT、UPDATE 和 DELETE 的之前和之后）
3. 单个触发器不能与多个事件或多个表关联
4. 如果 BEFORE 触发器失败，则MySQL将不执行请求的操作。此外，如果BEFORE触发器或语句本身失败，MySQL将不执行 AFTER 触发器

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

如果执行上面语句，将会看到 [Err] 1415 - Not allowed to return a result set from a trigger 报错信息。
原因是：从MySQL5以后不支持触发器返回结果集，所以可以使用INTO 加变量，如下

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added' INTO @ftest;

AFTER INSERT：触发器将在INSERT语句成功执行后执行。 FOR EACH ROW：代码对每个插入行执行。 SELECT 'Product added'：插入成功显示 Product added

4.2 删除触发器

DROP TRIGGER newproduct;

4.3 使用触发器

INSERT 触发器

orders 表的主键是 AUTO_INCREMENT，下面创建一个触发器，INSERT返回本条记录的主键

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT NEW.order_num INTO @ntest;

插入一条新数据试试上面触发器：

INSERT INTO orders(order_date, cust_id) VALUES(Now(), 10001);

DELETE 触发器

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

例子：使用OLD保存将要被删除的行到一个存档表中：

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
	INSERT INTO archive_orders(order_num, order_date, cust_id)
  VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

BEGIN...END非必须

UPDATE 触发器

• OLD虚拟表访问以前（UPDATE语句前）的值，NEW虚拟表访问新更新的值；
• BEFORE UPDATE触发器中，NEW中的值可更新
• OLD中的值全都是只读，不更新

例子：保证州名缩写总是大写

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

五：参考文献

• 《MySQL 是怎样运行的 - 小孩子4919》
• 《MySQL 必知必会 - Ben Forta著（刘晓霞、钟鸣）译》
• 官方文档 (opens new window)