“ Life is never fair, and perhaps it is a good thing for most of us that it is not.”
【前文链接】
12. 增删改查
12.1 插入数据
插入的方式
插入完整的行 插入行的一部分 插入多行 插入某些查询的结果
插入完整的行，不安全，如下：
insert into tbl_name values(v1, v2, ...);复制
编写insert语句的更安全的方法：将列名标出来，如下：
insert into customers(col1, col2, ...) values(v1, v2, ...);复制
如果表的定义允许，则可以在insert操作中省略某些列。省略的列必须满足以下某个条件：
该列定义为允许NULL值 在表定义中给出默认值。
如果对表中不允许null且没有默认值的列不给出值，则mysql将报错。
提高整体性能：
数据库经常被多个客户访问，insert会很耗时，而且可能降低等待处理的select语句的性能。如果数据检索是重要的，可以通过在insert和into之间添加关键字LOW_PRIORITY
，指示mysql降低insert语句的优先级。如下：
INSERT LOW_PRIORITY INTO ...复制
另外，LOW_PRORITY也适用于UPDATE和DELETE语句
插入多行
values后面直接跟多组数据，数据间用逗号隔开
insert into customers(col1, col2, ...) values(v1, v2, ...),
(v3, v4, ...),
(v5, v6, ...);复制
插入检索出来的数据
利用insert将一条select语句的结果插入表中，即所谓的insert select语句。由一条insert和一条select语句组成，如下：
INSERT INTO customers(col1, col2, ...)
SELECT col1, col2, ...
FROM tbl_name
WHERE ...复制
12.2 更新数据（注意使用安全）
更新方式
更新特定行（一定要where，where一定要正确筛选到） 更新所有行
删除某个列
UPDATE还用于删除某个列的值，即清空某一列的值，使用UPDATE而不是DELETE，如下：
UPDATE tbl_name
SET col = NULL
WHERE ...复制
IGNORE关键字
如果用UPDATE语句更新多行，并且在其中一行出错，则会回退取消整个UPDATE操作，如果要求即使发生错误也继续进行更新，可使用IGNORE关键字，如下：
UPDATE IGNORE tbl_name ...复制
12.3 删除数据（注意使用安全）
删除方式
删除特定行（一定要where，where一定要正确筛选到） 删除所有行
更快的删除
如果想删除表中所有的行，可使用TRUNCATE TABLE
语句，它完成删除表格相同的工作，但速度更快（删除原来的并重新建一个表，而不是逐行删除表中的数据）。
重要：
为了保证UPDATE和DELETE操作不误伤（没有where或者where的效果不同于预期）：
先测试WHERE子句对不对，确保它过滤的是正确的记录； 使用强制实施引用完整性的数据库，这样与其他表有关联的行也不能被删除。
13. 视图
视图是虚拟的表，是几个表联结查询的动态结果。 视图不存储数据，每次查看视图时，要重新执行查询基表语句，因此大型、复杂视图性能低。
13.1 为何使用视图
重用SQL语句，简化复杂查询，不必重复编写查询联结细节； 保护数据，可以给用户授权只能访问表的一部分数据； 更改数据格式和表示，与底表可以不同。
13.2 视图的规则与限制
视图名字必须唯一； 视图数目无限制； 创建视图需要有相应表的访问权限； 视图可以嵌套，即联结其他视图来创建新视图； 视图中order by作用，会被基表中的order by覆盖； 视图不能有索引、触发器、默认值； 视图可以和表一起使用，即连接查询。
13.3 使用视图
创建视图
CREATE VIEW view_name AS SELECT ...复制
查看创建视图的语句
SHOW CREATE VIEW view_name;复制
删除视图
DROP VIEW view_name;复制
如果指定的视图不存在，则DROP VIEW将产生一个错误。可以使用如下语句防止产生错误：
DROP VIEW IF EXISTS view_name;复制
更新视图
先删除视图，再重新创建； 使用 create or replace view view_name
，如果视图不存在就创建，视图存在就替换。
如果视图定义中有以下操作，则不能进行视图更新：
分组（GROUP BY、HAVING）; 联结; 子查询; 并查询（组合查询UNION）; 聚集函数; 唯一值（DISTINCT）; 计算列。
注意：一般应将视图用于检索，而非更新。对视图的增、删、改，实际是对底表数据进行操作。
14. 存储过程
存储过程，简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。
可将其视为批文件，虽然他们的作用不仅限于批处理。
14.1 优缺点
优点
执行效率非常快！存储过程是在数据库的服务器端执行
简化复杂操作。因为把处理封装在容易使用的单元中；
保证了数据的完整性。不要求反复建立一系列处理步骤（防止错误）；
简化对变动的管理。如果表名、列名或业务逻辑有变化，只需要修改存储过程的代码。使用它的人员甚至不需要知道这些变化（减少了数据讹误，确保了安全性）；
提高性能。存储过程比单独的SQL语句要快；
代码更强更灵活。存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。
缺点
存储过程比SQL语句复杂得多； 移植性很差，不同的数据库的存储过程是不能移植的； 并不是所有人都有创建存储过程的安全访问权限。
14.2 创建存储过程
CREATE PROCEDURE proc_name([[IN|INOUT|OUT] param_name TYPE|...])
BEGIN
SELECT ...
FROM tbl_name;
END;复制
注意：
（1）BEGIN
和END
用来限定存储过程体。
（2）默认的MySQL分隔符为;
，但是存储过程中包含有;
，这会使存储过程中的SQL语句出现句法错误。解决办法就是临时更改命令行实用程序的语句分隔符：
DELIMITER $
CREATE PROCEDURE proc_name([ [IN|INOUT|OUT] param_name TYPE| ... ])
BEGIN
SELECT ...
FROM tbl_name;
END $
DELIMITER ;复制
DELEMITER $
告诉实用程序实用$
作为新的语句结束分隔符，可以看到标志存储过程结束的END定义为END $
。这样存储体内的;
可以保持不变，正确地传递给引擎，最后再恢复为原来的语句分隔符;
。除了\
之外都可定义为分隔符，\
一般用为转义符。
（3）没有参数的存储过程也不能省略()
，它就相当于一个空参的函数，调用时也要遵守这一原则。
（4）如果有参数，要明确参数类型：数据类型和输入输出类型（IN/OUT/INOUT
)：
OUT
：指出相应的参数用来从存储过程传出一个值，返回给调用者；IN
：传递给存储过程；INOUT
：对存储过程传入和传出。
如下代码所示：
DELIMITER $
CREATE PRODUCER proc_name(IN param1 INT, OUT param2 DECIMAL(8, 2))
BEGIN
SELECT Min(col_name)
FROM tbl_name
WHERE id = param1
INTO param2;
END $
DELIMITER ;复制
注意：IN类型参数和OUT类型参数的位置。
14.3 查看存储过程
（1）SHOW PROCEDURE STATUS
列出所有存储过程，为了限制其显示某些特定的存储过程，可以用LIKE关键字指定一个过滤模式：
SHOW PROCEDURE STATUS LIKE 'proc_name'复制
（2）如果要获得存储过程的详细信息，可以使用SHOW语句进行显示：
SHOW CREATE PROCEDURE proc_name; -- 只给出存储过程名字复制
注意：此时只给出存储过程名字即可，不再加()
。
14.4 执行存储过程
MySQL中使用关键字CALL
来执行存储过程，CALL
接收存储过程的名字以及需要传递给它的任意参数（变量）。
变量是内存中一个特定的位置，用来临时存储数据。
所有MySQL的变量都必须以@
开始。
针对上述的带参存储过程，执行语句如下：
CALL proc_name(1001, @result);复制
其中的1001
是传入给存储过程，变量@result
是由存储过程传出的值。
一般情况下，带有输出变量的存储过程执行后并不显示结果，而是把结果返回给你指定的变量（如上述的@result），若要显示结果，则需另外执行SELECT语句显示返回结果，如下：
SELECT @result;复制
14.5 删除存储过程
DROP PROCEDURE proc_name;复制
此时仍然只给出存储过程名字即可，不再加()
。如果指定的存储过程不存在，则DROP PROCEDURE将产生一个错误。可以使用如下语句防止产生错误：
DROP PROCEDURE IF EXISTS proc_name;复制
14.6 建立复杂存储过程
复杂存储过程可能用到的关键字：
-- ：注释
DECLARE：定义局部变量。形式如：
DECLARE variable_name TYPE
; 变量名前不用加@符号IF：条件语句，还支持ELSEIF（使用THEN）， ELSE（不使用THEN）语句
COMMENT：不是必需的，但是如果给出，将在
SHOW PROCEDURE STATUS
的结果中显示，
一个实例：
考虑这个场景：有一个订单表需要获得订单合计，但需要对合计增加营业税，不过只针对某些客户。
根据场景列出存储过程中应执行的事件：
获得合计； 把营业税有条件地添加到合计； 返回合计(带税或不带税)。
源代码如下：
-- Name：ordertotal
-- Parameters：onumber = order number
-- taxable = 0 if not taxable, 1 if taxable
-- ototal = order total variable
DELIMITER $
CREATE PROCEDURE ordertotal(IN onumber INT, IN taxable BOOLEAN,OUT ototal DECIMAL(8, 2))
COMMIT "obtain order total, optionally adding tex"
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 it 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 $
DELIMITER ;复制
执行存储过程并显示结果：
CALL orderototal(20005, 0, @total);
SELECT @total;复制
15. 触发器
MySQL中的触发器概念，和Java中的事件监听器有点相似。当你想要某条语句在某个事件发生时自动执行，就要用到触发器了。
触发器能响应如下三类语句：DELETE、INSERT和UPDATE。
15.1 创建触发器
创建须知：
只有表才支持触发器，视图不支持，临时表也不支持； 每个表的每个事件每次只允许一个触发器； 每个表最多支持6个触发器（即每个INSERT、UPDATE和DELETE可以选择使用BEFORE或AFTER修饰）； 单一触发器不能与多个事件或多个表关联，所以如果需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。
创建触发器时，需要给出4条信息：
唯一的触发器名 触发器关联的表 触发器应该响应的活动(DELETE、INSERT、UPDATE) 触发器何时执行(处理之前或之后)
创建代码如下：
CREATE TRIGGER tri_name
AFTER INSERT ON tbl_name FOR EACH ROW
SELECT 'insert success';复制
分析：
CREATE TRIGGER用来创建名为tri_name的新触发器。触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT，所以此触发器将在INSERT语句成功执行后执行。这个触发器还指定FOR EACH ROW，因此代码对每个插入行执行。在这个例子中，文本“insert success”将对每个插入的行显示一次。
15.2 INSERT触发器
INSERT触发器在INSERT语句执行之前或之后执行 ：
在INSERT触发器代码内，可引用一个名为 NEW
的虚拟表，访问被插入的行；在BEFORE INSERT触发器中， NEW
的值也可以被更新(允许更改被插入的值)；对于AUTO_INCREMENT列， NEW
在INSERT执行前包含0，在INSERT被执行后包含新的自动生成值。
如下代码：
CREATE TRIGGER tri_name
AFTER INSERT ON tbl_name FOR EACH ROW
SELECT NEW.col_name INTO @vari;复制
分析：
此代码创建一个名为tri_name的触发器，在插入新信息到tbl_name表并成功后，插入信息会保存在NEW
表中，触发器从NEW
表中取得col_name的值并赋给变量@vari
，因为从MySQL 5以后不支持触发器返回结果集，可以执行SELECT @vari
来显示它的值。
注意：
使用BEFORE还是AFTER：通常，将BEFORE用于对NEW表进行数据验证和净化（目的是保证插入表中的数据确实是需要的数据），此条也适用于UPDATE触发器。 触发器失败：如果BEFORE触发器失败，则MySQL将不执行请求的操作。此外，如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER触发器（如果有的话）。
15.3 DELETE触发器
DELETE触发器在DELETE语句执行之前或之后执行：
在DELETE触发器代码内，可以引用一个名为 OLD
的虚拟表，访问被删除的行；OLD
中的值全部都是只能读的，不能更新
如下代码：
DELIMITER $
CREATE TRIGGER tri_name
BEFORE DELETE ON tbl_name FOR EACH ROW
BEGIN
INSERT INTO archive_table(id, name, age) VALUES(OLD.id, OLD.name, OLD.age);
END $
DELIMITER ;复制
分析：
在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的值（要被删除的订单信息）保存到一个名为archive_table的存档表中（为实际使用这个例子，你需要建立一个和被删除信息表具有相同结构的archive_table表）。
★相对于AFTER DELETE触发器来说，使用BEFORE DELETE触发器的优点为：如果由于某种原因，订单不能存档，那么DELETE本身将被放弃，也即不再执行DELETE操作。
使用BEGIN END块的好处是触发器能容纳多条SQL语句。
15.4 UPDATE触发器
UPDATE触发器在UPDATE语句执行之前或之后执行：
在UPDATE触发器代码中，对于更新前的旧值，可以引用 OLD
虚拟表访问；对于新值，可以引用NEW
虚拟表访问；在BEFORE UPDATE触发器中， NEW
中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；OLD
中的值全都是只读的，不能更新。
代码如下：
CREATE TRIGGER tri_name
BEFORE UPDATE ON tbl_name FOR EACH ROW
SET NEW.name = Upper(NEW.name);复制
分析：此代码表示在执行更新前，把将要插入的数据中的name值全部转换为大写，以此来净化插入数据。
15.5 删除触发器
DROP TRIGGER [if exists] tri_name;复制
注意：触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。
15.6 触发器进一步介绍
与其他DBMS相比，MySQL 5中支持的触发器相当初级。未来的MySQL版本中有一些改进和增强触发器支持的计划。 创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果INSERT、UPDATE或DELETE语句能够执行，则相关的触发器也能执行。 应该用触发器来保证数据的一致性（大小写、格式等）。在触发器 中执行这种类型的处理的优点是它总是进行这种处理，而且是透明地进行，与客户机应用无关。 触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易。 遗憾的是，MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。
✨喜欢的老铁动动手指点个关注吧✨
点击『在看』是对我最大的支持 
