【一】MySQL进阶知识之视图

【1】视图介绍

（1）什么是视图

视图就是通过查询得到一张虚拟表，然后保存下来，下次可以直接使用

视图也是一张表

• 在计算机科学中，视图（View）是一种虚拟表，其内容是一个或多个基本表的查询结果。

• • 视图基于数据库中的数据，通过定义查询语句来构建，并在需要时动态地计算数据。
  • 与基本表不同，视图不存储实际的数据，而是根据查询语句在使用时进行实时计算。

• 视图可以用于简化复杂查询、隐藏敏感数据、实现数据安全性和完整性约束等。

• • 通过定义视图，用户可以从一个或多个基本表中选择所需的列和行，以满足特定的查询需求。
  • 用户可以对视图执行与表相同的查询操作，如SELECT、INSERT、UPDATE 和 DELETE。

• 视图可以是虚拟的，也可以是物化的。

• • 虚拟视图是基于查询结果的定义，每次查询时都会计算最新的结果。
  • 物化视图是基于查询结果的缓存，用于提高查询性能。
  • 虚拟视图适用于经常变动的数据，而物化视图适用于查询频繁但数据变动较少的情况。

• 总之，视图是一种查询结果的抽象表示，它提供了一种灵活和安全的方式来访问和操作数据库中的数据。

• • 通过使用视图，用户可以根据自己的需求获取所需的数据，而无需直接访问底层的基本表。

（2）为什么要用视图

如果要频繁操作一张虚拟表(拼表组成)，就可以制作成视图，后续直接操作

（3）视图的优点

• 简化复杂查询：

• • 视图可以对基本表进行复杂的操作，包括连接多个表、过滤条件、聚合函数等。
  • 通过定义视图，可以将复杂的查询逻辑封装到一个简单的视图中，使用户能够以更简洁明了的方式进行数据检索。

• 数据安全性：

• • 视图可以限制用户对数据的访问权限，通常用于隐藏敏感数据或只提供部分数据给特定的用户。
  • 通过定义视图并设置相应的权限，可以保护数据的安全性，防止未经授权的用户访问敏感信息。

• 数据完整性：

• • 视图可以用于实现数据完整性约束，即对数据的有效性进行验证。
  • 通过定义视图并添加计算列、过滤条件等约束，可以确保所返回的数据满足一定的条件，提高数据的准确性和一致性。

• 逻辑数据独立性：

• • 视图使得应用程序与数据之间解耦，即应用程序不需要了解底层表结构的细节。
  • 这样，当底层数据库发生变化时（如表结构修改），只需调整底层视图的定义而无需修改应用程序，从而提高系统的可维护性和扩展性。

• 性能优化：

• • 物化视图是一种缓存机制，可以将视图的查询结果存储在磁盘上，以提高查询性能。
  • 当基本表的数据频繁变动时，物化视图可以减少查询的计算开销和响应时间，提升系统的性能。

【2】如何使用视图

（1）创建视图

create view 视图名(表名) as 虚拟表的查询SQL语句;

-- 示例
create view my_view(column1,column2) as select column1,column2 from my_table where condition;

• 在上述语句中
  • my_view 是视图的名称
  • my_table 是源表的名称
  • column1 和 column2 是要选择的列
  • condition 是过滤条件

（2）在视图中查询数据

select * from my_view;

（3）更新视图数据

• 更新的是视图表的数据而不是原本的表中的数据

UPDATE my_view SET column1 = value1 WHERE condition;

• 上述语句中
  • column1 是要更新的列， value1 是要设置的值， condition 是更新条件。

（4）删除视图

drop view my_view;

【二】触发器

【1】什么是触发器

在满足对表数据进行增删改的情况下，自动触发的功能，称为触发器

• 触发器是数据库管理系统中的一个概念，它是一种在数据库中定义的特殊对象，用于在满足特定条件时触发自动化的操作或逻辑。

• • 触发器主要用于监视数据库表的增删改操作，并在满足指定条件时触发相关的响应行为。

• 触发器通常与数据库表关联，当数据库表发生特定的数据变化时，触发器会自动执行相关的操作

• • 比如插入、更新、删除或查询数据等。

• 触发器可以作为一种数据库的约束，用于保证数据的完整性和一致性。

【2】触发器的特点

• 触发器通常与表一起创建、修改和删除。
• 触发器可以在特定的数据操作之前或之后触发执行。
• 触发器可以根据用户定义的条件判断是否执行相应的逻辑。
• 触发器可以调用存储过程、函数、触发其他触发器等，实现更复杂的业务逻辑。

操作之前：插入数据之前对数据进行校验，校验当前用户名是否存在。

操作之后：用户注册成功，插入数据成功，在另一张日志表中记录当前注册成功的日志。

【3】为何使用触发器

可以帮助我们实现日志、监控、处理等操作

• 使用触发器可以实现很多功能

• • 比如数据验证、数据补全、数据同步、日志记录等。

• 但需要注意，触发器的使用也需要谨慎，过多或不当的触发器可能会对数据库性能产生负面影响，因此在设计和使用触发器时应考虑到业务需求和性能方面的平衡。

【4】触发器的六种使用情况

• 增前
• 增后
• 删前
• 删后
• 改前
• 改后

【5】语法结构

create trigger 触发器的名字
before/after insert/update/delete
on 表名 for each row 
begin
	SQL语句
end

【6】自定义触发器的流程

（1）创建触发器

• 使用数据库管理系统提供的语法，创建一个新的触发器对象。
• 在创建触发器时，您需要指定触发器的名称、触发时机（例如在插入、更新或删除之前或之后）、触发的表以及触发时执行的逻辑。

（2）定义触发器逻辑

• 在创建触发器时，您需要定义触发器在触发时所执行的逻辑。

• • 这可以是任何数据库支持的操作

• 比如插入数据、更新数据、删除数据、查询数据等。

• • 您可以使用SQL语句或调用存储过程、函数来实现触发器的逻辑。

（3）定触发条件

• 根据您的需求，您可以为触发器指定触发条件。

• • 触发条件是一个逻辑表达式，当表中的数据满足该表达式时，触发器才会被激活执行相关的逻辑。
  • 例如，您可以指定只有当某列的值大于特定值时才触发触发器。

（4）绑定触发器

• 将触发器绑定到相应的表上。一般情况下，触发器会与指定的表相关联，当该表发生特定的数据操作时，触发器才会被触发执行。

（5）测试触发器

• 在绑定触发器后，您可以进行一些测试操作，验证触发器的逻辑是否按照预期执行。

• • 可以插入、更新或删除数据，观察触发器是否正确地处理这些操作。

【7】示例

（1）创建表

create table cmd(
	id int primary key auto_increment,
    user varchar(32),
    password varchar(10),
    cmd varchar(64),
    sub_time datetime,# 提交时间
    success enum('yes','no') # 0代表执行失败
);

create table errlog(
	id int primary key auto_increment,
    err_cmd varchar(64),
    err_time datetime
);

（2）需求

• cmd 表插入数据的success如果值为 no 则去errlog表中插入一条记录

delimiter $$  # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
  if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象
      insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
  end if;
end $$
delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了

（3）往cmd表中插入数据

INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)VALUES
  ('kevin','0755','ls -l /etc',NOW(),'yes'),
  ('kevin','0755','cat /etc/passwd',NOW(),'no'),
  ('kevin','0755','useradd xxx',NOW(),'no'),
  ('kevin','0755','ps aux',NOW(),'yes');

（4）查看数据

select * from cmd;

select * from errlog;

【三】事务

【1】什么是事务

• 开启一个事务可以包含多条语句，这些语句要么同时成功，要么都不成功

• 事务是指一系列相关操作的集合，这些操作被视为一个不可分割的工作单元。

• 事务的目标是确保在多个操作中的每一个都要么全部成功执行，要么全部失败回滚。

• • 即事务的原子性

【2】事务的四大特性(ACID)

（1）原子性（Atomicity）

• 事务被视为一个原子操作，不可再分割。
• 要么所有的操作都成功执行，要么所有的操作都会被回滚到事务开始前的状态，确保数据的一致性。

（2）一致性（Consistency）

• 事务执行前后，数据库应保持一致的状态。
• 在事务开始之前和结束之后，数据库必须满足所有的完整性约束，如数据类型、关系等。

（3）隔离性（Isolation）

• 事务的执行结果对其他并发执行的事务是隔离的。
• 即一个事务的执行不应受到其他事务的干扰，各个事务之间应该相互独立工作，从而避免数据的不一致性。

（4）持久性（Durability）

• 也叫永久性
• 一旦事务被提交，其结果应该永久保存在数据库中，并且可以被系统故障恢复。
• 即使系统发生宕机或崩溃，事务提交后的更改也应该是永久性的。

【3】事务的作用

• 在操作多条数据的时候，可能会出现某几条操作不成功的情况，需要进行事务回滚

（1）数据一致性

• 事务可以确保数据库的一致性。
• 在一个事务中，要么所有的操作都成功执行，要么全部回滚，保证了数据的完整性和一致性。
• 例如，在一个转账操作中，如果转出账户扣款成功而转入账户未能成功接收资金，事务可以将操作全部回滚，以确保资金的一致性。

（2）并发控制

• 事务提供了并发控制机制，确保多个并发执行的事务不会相互干扰，并避免数据的混乱和冲突。
• 通过隔离级别的设置，事务可以控制不同事务之间的可见性和影响范围，保证并发执行时的数据一致性和隔离性。

（3）故障恢复

• 事务的持久性特性确保了在事务提交后，即使系统发生故障或崩溃，提交的结果仍然可以被恢复。
• 数据库管理系统通过使用日志文件等机制，将事务的操作记录下来，以便在需要时进行恢复和重放。

（4）高效运行

• 通过组织多个操作为一个事务，可以减少与数据库交互的次数，从而提高数据库的操作效率和性能。事务可以减少磁盘I/O、锁的竞争等操作开销，提高数据库的并发处理能力。

（5）数据完整性和安全性

• 事务可以保护数据的完整性和安全性。
• 通过在事务中定义一些条件和约束，可以确保数据的有效性和准确性。
• 例如，在一个银行系统中，事务可以检查账户余额是否足够以及转账金额是否合法，从而保证数据的安全性和正确性。

【4】业务示例

• 检查库存：

• • 系统需要检查所需商品的库存是否足够。
  • 如果库存不足，系统会提示用户库存不足，无法完成订单。

• 扣减库存：

• • 如果库存充足，系统会将所购商品对应的库存数量减少。

• 生成订单：

• • 系统会生成一个新的订单，包括订单号、商品信息、购买数量、价格等相关信息。

• 计算总价：

• • 根据订单中的商品信息和购买数量，系统会计算出订单的总价格。

• 更新用户账户：

• • 根据用户选择的支付方式，在扣除相应金额后，系统会更新用户账户余额或积分。

• 生成支付信息：

• • 系统会生成相应的支付信息，以便用户完成支付。

• 通知物流部门：

• • 系统会通知物流部门准备配送相关商品。

• 发送订单确认邮件/短信：

• • 系统会向用户发送订单确认的邮件或短信，包括订单详细信息、配送信息等。

• 监控商品配送：

• • 系统会跟踪订单的配送情况，并向用户提供订单状态更新。

• 完成订单：

• • 当用户收到商品并确认满意后，订单状态会被更新为“已完成”。

-- 用户下单 ---》支付钱款 ---》资金并不是直接交付商户手中 ---》经过平台缓存 ---》用户点击确认收货之后，资金才会到达商家手中

【5】如何使用事务

# 开启事务
start transaction;

# 回滚(回到事务执行之前的操作)
rollback;

# 二次确认(确认之后无法回滚)
commit;

# 先开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

# 插入数据
mysql> insert into user(username,password) values('opppp',666);
Query OK, 1 row affected (0.00 sec)

# 数据存在

# 发现数据不对，事务回滚
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)

# 数据消失

# 如果确认数据正常，直接提交事务
mysql> commit;
Query OK, 0 rows affected (0.03 sec)

------------ 再次插入数据
# 直接插入数据
mysql> insert into user(username,password) values('opppp',666);
Query OK, 1 row affected (0.04 sec)

# 发现数据存在

# 进行事务回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

# 数据依然存在