一、数据视图
1. 基本定义
视图（View）是一种虚拟表，其内容由查询定义，视图中的数据来自于一个或多个基本表，通过对这些基本表的列进行选择和重命名而得到。
视图并不包含数据，而是对基本表中的数据的引用，当删除视图表中的数据时其基表的数据也将关联删除。在更多的业务场景下通常抽取目标数据至视图再执行系列查询操作，从而优化查询效率。
视图的作用主要有以下几点：
- 简化查询：通过视图的定义，将复杂的查询简化，减少用户的工作量。
- 数据安全性：可以通过视图来限制用户对数据库中数据的访问权限，只允许用户查询视图中的指定列或行。
- 数据独立性：通过视图可以将物理数据与逻辑数据分离，增强了数据独立性，方便后期维护。
- 提高性能：对视图的查询经常被预先优化，这有助于提高查询性能。
2. 视图创建
通过 create view 关键字创建视图表。
create view <database_name>.<view_name>
as
<select_command>
3. 视图更新
视图表是虚拟表，通常是由一个或多个基本表导出的表，其内容是基于基本表的数据计算、组合或筛选的结果。
因为其数据源是基于其他表的，所以视图表的数据是不能直接修改的，视图表也被称为只读表，如果需要修改视图表中数据只需操作其基表即可，若需要删除视图表则与普通表一致通过 delete 关键字即可。
二、数据分区
1. 存储介绍
MySQL 中默认存储引擎是 InnoDB，其创建一张表会在库名目录下创建对应的 表名.frm 和 表名.ibd 两个文件，二者文件无法直接打开。
frm文件存储的是表结构信息。ibd文件存储的是表数据内容。
由于数据都存于 ibd 文件中，因此当达到一定量级时（通常为单表数据记录大于两千万）基本的查询等操作效率将大大降低，也由此通常会使用分表实现，而分区则为自带的类分表功能，在部分数据库中又称分区为分片，二者实际效果类似。
默认一张表只有一个 ibd 文件，若表开启分区则会为每个分区创建单独的 ibd 文件，从而提高检索执行效率。
MySQL中表分区需要注意以下三点：
- 分区表无法使用外键约束。
- 一个表最多只能有
1024个分区。- 若分区表存在主键则必须依据主键来实现分区。
2. 分区类型
MySQL 的分区类型分为 Range、 List、 Hash 与 Key 四类，下面分别进行介绍。
(1) Range分区
Range 分区即根据分区字段的范围进行划分。
如下示例中即根据 id 的值划分了 4 个分区 (-∞,100), [100,200), [200,300), [300,+∞)。
create table tb_info
(
id int not null auto_increment primary key,
user_id int,
phone VARCHAR(50),
address VARCHAR(50)
)
partition by range(id)
(
partition p0 values less than (100),
partition p1 values less than (200),
partition p2 values less than (300),
partition p3 values less than maxvalue
)
(2) List分区
List 分区即为根据分区字段值定义确定的集合。
如下实例中定义了两个分区，当 id 值在集合 (1, 3, 5) 中则分配至分区一，若值在集合 (2, 4, 6) 中则分配至分区二。
create table tb_info
(
id int not null auto_increment primary key,
user_id int,
phone VARCHAR(50),
address VARCHAR(50)
)
partition by list(id)
(
partition p0 values in (1, 3, 5),
partition p1 values in (2, 4, 6)
)
(3) Hash分区
Hash 分区即可通过指定表达式对分区字段进行计算从而分配所属分区。
注意若不指定表达式则使用默认的哈希算法。
create table tb_info
(
id int not null auto_increment primary key,
user_id int,
phone VARCHAR(50),
address VARCHAR(50)
)
partition by hash (id) partitions 9;
(4) Key分区
Key 分区与 Hash 分区类似，但不同的时 Hash 分区可以指定表达式而 Key 分区只能使用默认的分区算法。
create table tb_info
(
id int not null auto_increment primary key,
user_id int,
phone VARCHAR(50),
address VARCHAR(50)
)
partition by key (id) partitions 9;
3. 分区编辑
(1) 分区创建
在上面介绍了四类不同分区的在建表时的配置方式，当然也可通过 alter table 对已经存在的表划分分区。
-- add partition
alter table <table_name>
partition by <type> (<column>)
<expression>;
(2) 分区添加
在完成分区创建之后随着业务的增长可能发现之前划分的分区数量不满足当下的需求，此时即在原有的分区基础上新增分区。
注意新增分区后需重新执行分析语句 analyze table <table_name> 让数据重新分配分区。
alter table <table_name>
add partition <expression>;
(3) 分区删除
删除分区时需要注意只有 Range 和 List 类型的分区才能删除。
-- remove partition
alter table <table_name>
drop partition <partition_name>;
4. 基本使用
当一个表创建分区之后，在执行查询等操作时即可通过 partition 指定分区缩小范围从而提高效率。
如下示例中即指定查询表 tb_info 其分区 p1 中的 id 值为 (1, 2, 4) 的数据。
select
*
from
tb_info
partition(p1)
where
id in (1, 2, 4)
通过 explain partitions 查询语句涉及的分区。
explain partitions
select
*
from
tb_info
where
id in (1, 2, 4);
三、触发器
1. 基本介绍
数据库触发器是一种数据库对象，它与表相关联，能够在表上的特定事件（如插入、更新、删除）发生时自动执行一些预定义的操作。
触发器通常用于实现以下几个方面的功能：
- 数据一致性维护： 触发器可以用于确保数据库中的数据保持一致性。通过在数据修改事件发生时自动执行一系列的 SQL 操作，可以避免手动的错误和遗漏。
- 约束实施： 触发器允许你在数据修改之前或之后执行一些额外的检查，从而实施数据约束。这可以包括检查条件、计算字段值，或者拒绝操作。
- 日志记录和审计： 触发器可以用于记录数据库操作的日志，实现审计功能。例如，你可以在每次对表执行修改操作时记录一条日志，包括执行操作的用户、时间戳等信息。
- 派生数据自动更新： 触发器可以用于自动更新派生数据。当表的数据发生变化时，触发器可以更新相关联的表，确保派生数据保持最新。
- 业务规则的实施： 触发器可以用于实施业务规则。例如，在订单表上插入新行时，触发器可以检查库存表，确保库存充足。
触发器可以在行级别（每一行上的事件触发触发器）或语句级别（整个语句执行时触发触发器）执行。在创建触发器时，你可以指定触发器应该在 BEFORE（事件发生前）还是 AFTER（事件发生后）执行。
2. 创建示例
在 MySQL 的触发器中，使用 NEW 和 OLD 关键字来访问触发事件前后的行数据。这两个关键字用于引用正在被触发器处理的行。
NEW用于在BEFORE INSERT和BEFORE UPDATE触发器中引用将要被插入或更新的新行数据。OLD用于在BEFORE UPDATE和BEFORE DELETE触发器中引用将要被更新或删除的旧行数据。
CREATE TABLE `tb_log` (
`action` varchar(100) DEFAULT NULL,
`info` varchar(255) DEFAULT NULL
);
-- 创建一个触发器每次 tb_test 新建数据则新增一条日志
CREATE TRIGGER trigger_add
BEFORE INSERT ON tb_test
FOR EACH ROW
BEGIN
INSERT INTO tb_log (`action`, `info`)
VALUES ('New record', CONCAT_WS('Column1: ', NEW.Column1));
END;
3. 基本操作
触发器其他相关命令如下：
-- show all trigger
SHOW TRIGGERS;
-- delete trigger if existed
DROP TRIGGER IF EXISTS <trigger_name>;
四、存储过程
1. 创建示例
如下述示例中创建了一个存储过程用于执行两个插入命令，当其中任意一条命令执行失败都将执行事务回滚操作。
-- create the procedure
CREATE PROCEDURE <procedure_name>()
BEGIN
DECLARE errorCode INT;
DECLARE errorMessage VARCHAR(255);
-- Declare a variable to store error information
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
-- Get error information
GET DIAGNOSTICS CONDITION 1
errorCode = MYSQL_ERRNO,
errorMessage = MESSAGE_TEXT;
-- Print error information
SELECT CONCAT('Error: ', errorCode, ' - ', errorMessage) AS ErrorMessage;
-- Rollback the transaction
ROLLBACK;
END;
-- Start the transaction
START TRANSACTION;
-- Execute the command
-- Any sql throw excption will tigger the rollback
INSERT INTO tb_120601 (Column1) VALUES (1);
INSERT INTO tb_120601 (Column1) VALUES (2);
-- Commit the transaction
COMMIT;
END;
2. 命令详解
DECLARE CONTINUE HANDLER 是用于声明异常处理器的关键字。异常处理器用于捕获和处理存储过程执行过程中可能发生的异常。
(1) Exception
SQLSTATE value 是一个 SQL 标准定义的异常代码。通常，SQLEXCEPTION 用于捕获所有异常。
DECLARE CONTINUE HANDLER FOR SQLSTATE value
BEGIN
-- Exception handling code
END;
(2) GET DIAGNOSTICS
在异常处理器中，使用了 GET DIAGNOSTICS 语句。这个语句用于获取异常的详细信息，包括错误码（MYSQL_ERRNO）和错误消息（MESSAGE_TEXT）。
-- 获取第一个异常的信息。如果有多个异常，可以使用不同的条件号
-- 例如 `CONDITION 2`。
GET DIAGNOSTICS CONDITION 1
-- 是将获取到的错误码和错误消息分别赋值给相应的变量。
errorCode = MYSQL_ERRNO, errorMessage = MESSAGE_TEXT
3. 基本操作
存储过程其他相关命令如下：
-- list all procedure
SHOW PROCEDURE STATUS;
-- drop the procedure
DROP PROCEDURE IF EXISTS <procedure_name>;
-- trigger the procedure
CALL <procedure_name>();
