数据据库八之 视图、触发器、事务

【零】准备数据

【1】创建表

(1)部门表
  • d_id是部门的编号
  • d_name是部门的名字
# 确保表不存在
drop table if exists department;
# 创建表
create table department(
d_id int auto_increment primary key,
d_name varchar(6)
)auto_increment = 501 ;
(2)创建员工表
  • id是员工的编号
  • name是员工的姓名
  • money是员工的金额,默认1000
  • sex是员工性别默认男性
  • department_id是所在部门的编号
# 确保表不存在
drop table if exists employee;
# 创建表
create table employee(
id int auto_increment primary key,
name varchar(10),
password varchar(20),
money int default 1000,
sex enum('female', 'male') default 'male',
department_id int
);

【2】插入数据

(1)部门信息
  • 只插入3个部门信息
insert department (d_name) values("销售部"), ("技术部"), ("售后部");
+-----+-----------+
| id  | name      |
+-----+-----------+
| 501 | 销售部    |
| 502 | 技术部    |
| 503 | 售后部    |
+-----+-----------+
(2)员工信息
  • 共5名员工
insert employee (name, sex, password, department_id)values
('John', 'male', '111',  501),
('Jane', 'female', '222', 503),
('Mike', 'male', '333',  502),
('Tom', 'male', 'aaa', 503),
('Amy', 'female', '999', 501);
+----+------+----------+-------+--------+---------------+
| id | name | password | money | sex    | department_id |
+----+------+----------+-------+--------+---------------+
|  1 | John | 111      |  1000 | male   |           501 |
|  2 | Jane | 222      |  1000 | female |           503 |
|  3 | Mike | 333      |  1000 | male   |           502 |
|  4 | Tom  | aaa      |  1000 | male   |           503 |
|  5 | Amy  | 999      |  1000 | female |           501 |
+----+------+----------+-------+--------+---------------+

【一】视图

【1】说明

(1)什么是视图
  • 视图是数据库中的一种虚拟表,其内容是一个或多个基本表的查询结果。
  • 与基本表不同,视图不存储实际数据,而是根据查询语句在使用时进行实时计算
  • 视图可以通过定义查询语句来简化复杂的查询、隐藏敏感数据、实现数据安全性和完整性约束等。
  • 用户可以对视图执行与表相同的查询操作但是多用于查询,其他容易出错
  • 视图可以是虚拟的,每次查询都会计算最新的结果,也可以是物化的,用于提高查询性能。
  • 虚拟视图适用于经常变动的数据,而物化视图适用于查询频繁但数据变动较少的情况。
  • 通过使用视图,用户可以以灵活且安全的方式访问和操作数据库中的数据,无需直接访问底层的基本表
(2)作用
  • 简化复杂查询:通过在视图中定义复杂的查询逻辑,包括连接多个表、过滤条件、聚合函数等,使用户能够以更简洁明了的方式进行数据检索。

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

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

  • 逻辑数据独立性:通过解耦应用程序与底层数据表结构,视图使得应用程序不需要了解底层表结构的细节。这样,当底层数据库发生变化时,只需调整底层视图的定义而无需修改应用程序,提高系统的可维护性和扩展性。

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

【2】使用

  • 联合多个表的时候,字段名不能重复
(1)创建视图
  • 创建视图的模板
create view 视图名 as
select 字段名,...
from 表名
join 表名 on 条件
where 条件
  • 创建视图
create view emp_dep as
select * from employee
left join department
on employee.department_id = department.d_id;
  • 查看文件结构
    • 只有frm文件,没有ibd文件
    • 只有表结构,没有数据记录

请添加图片描述

(2)修改数据
  • 先查看原本视图内容
select * from emp_dep;
+----+------+--------+---------------+------+-----------+
| id | name | sex    | department_id | d_id | d_name    |
+----+------+--------+---------------+------+-----------+
|  1 | John | male   |           501 |  501 | 销售部    |
|  5 | Amy  | female |           501 |  501 | 销售部    |
|  3 | Mike | male   |           502 |  502 | 技术部    |
|  2 | Jane | female |           503 |  503 | 售后部    |
|  4 | Tom  | male   |           503 |  503 | 售后部    |
+----+------+--------+---------------+------+-----------+
  • 员工表插入记录,并查看视图是否修改(修改了)
insert employee (name, sex, department_id)
values('lulu', 'female', 502);
select * from emp_dep;
+----+------+--------+---------------+------+-----------+
| id | name | sex    | department_id | d_id | d_name    |
+----+------+--------+---------------+------+-----------+
|  1 | John | male   |           501 |  501 | 销售部    |
|  5 | Amy  | female |           501 |  501 | 销售部    |
|  3 | Mike | male   |           502 |  502 | 技术部    |
|  6 | lulu | female |           502 |  502 | 技术部    |
|  2 | Jane | female |           503 |  503 | 售后部    |
|  4 | Tom  | male   |           503 |  503 | 售后部    |
+----+------+--------+---------------+------+-----------+
  • 视图表删除数据记录,查看视图表是否修改(不能)
delete from emp_dep
where id = 6;
ERROR 1288 (HY000): The target table emp_dep of the DELETE is not updatable
  • 视图表修改数据记录,查看视图表是否修改(不能)
update emp_dep
set department_id = 502
where name = tom;
ERROR 1288 (HY000): The target table emp_dep of the UPDATE is not updatable
  • 小结
    • 视图表多用于查询
    • 若要修改,在原表进行修改
(3)删除视图
  • 查看方法:
    • 和查看表一样
show tables;
+---------------+
| Tables_in_db4 |
+---------------+
| department    |
| emp_dep       |
| employee      |
+---------------+
desc emp_dep;
+---------------+-----------------------+------+-----+---------+-------+
| Field         | Type                  | Null | Key | Default | Extra |
+---------------+-----------------------+------+-----+---------+-------+
| id            | int(11)               | NO   |     | 0       |       |
| name          | varchar(10)           | YES  |     | NULL    |       |
| sex           | enum('female','male') | YES  |     | male    |       |
| department_id | int(11)               | YES  |     | NULL    |       |
| d_id          | int(11)               | YES  |     | 0       |       |
| d_name        | varchar(6)            | YES  |     | NULL    |       |
+---------------+-----------------------+------+-----+---------+-------+
  • 删除视图
    • 关键字稍微不同
drop view id exists 触发器名;
drop view emp_dep;

【二】触发器

【1】说明

(1)什么是触发器
  • 触发器是数据库管理系统中的一种特殊对象,用于在数据库表发生特定的数据变化时自动触发相关的操作或逻辑
  • 触发器通常与数据库表关联,监视表的增删改操作,当满足指定条件时,触发器会执行相应的响应行为。
(2)主要作用
  • 自动执行操作: 触发器可以在表的数据发生变化时自动执行相关的操作,如插入、更新、删除或查询数据等。
  • 维护数据一致性: 触发器可以用作一种约束,用于保证数据的完整性和一致性。通过在触发器中定义逻辑,可以确保数据库中的数据始终符合特定的规则或条件。
  • 实现业务规则: 触发器可以用于实现特定的业务规则。例如,当某个表的特定列达到某个阈值时,触发器可以自动执行相应的业务逻辑。
  • 记录操作历史: 触发器可以用于记录数据的修改历史,例如在表的某个字段发生变化时,触发器可以将变化记录到一个历史表中。
(3)使用场景
  • 使用触发器可以实现很多功能
    • 比如数据验证、数据补全、数据同步、日志记录等。
  • 在增删改的前后都可以使用触发器,故有六种使用情况
    • 增前、增后
    • 删前、删后
    • 改前、改后

【2】使用

(0)修改默认语句结束符
  • sql语句默认结束符是
  • 但是在接下来的语句中会有出现
  • 所以我们需要学会修改默认语句结束符
# 修改为 $$
delimiter $$
# 修改回来 ;
delimiter ;
(1)创建触发器
  • 模板
delimiter $$
create trigger 触发器名
{before | after} {insert | update | delete} on 表名
for each row
begin--触发器执行的语句--new--old
end $$	
delimiter ;
  • 触发器的名字需要让人很容易的理解其含义
# 例如
tri_表名_after_insert
  • NEW
    • NEW 用于引用触发器中正在处理的行的新值。在 BEFORE INSERTBEFORE UPDATE 触发器中,NEW 包含即将插入或更新到表中的数据。在 AFTER INSERTAFTER UPDATEAFTER DELETE 触发器中,NEW 包含已经插入、更新或删除的数据。
    • BEFORE INSERTAFTER INSERT 触发器中,NEW 是唯一的,并包含即将或已经插入到表中的数据。
  • OLD
    • OLD 用于引用触发器中正在处理的行的旧值。在 BEFORE UPDATEAFTER UPDATE 触发器中,OLD 包含即将或已经被更新的行的旧值。在 BEFORE DELETEAFTER DELETE 触发器中,OLD 包含即将或已经被删除的行的旧值。
    • BEFORE UPDATEAFTER UPDATE 触发器中,OLD 包含被更新的数据的旧值。在 BEFORE DELETEAFTER DELETE 触发器中,OLD 包含被删除的数据的旧值。
(2)删除触发器
  • 删除语句很简单
    • 和视图的删除差不多
drop trigger if exists 触发器名
(2)例一:部门检查
  • 要求:在插入数据之前进行部门id校验
delimiter $$
create trigger tri_employee_before_insert before insert on employee
for each row
beginif new.department_id not in (select d_id from department) thensignal sqlstate '45000'set message_text = "部门id不存在";end if;
end $$
delimiter ;
  • 检查检查(正确)
insert employee (name, sex, department_id) values('liuliu', 'male', 505);
ERROR 1644 (45000): 部门id不存在
minsert employee (name, sex, department_id) values('liuliu', 'male', 501);
Query OK, 1 row affected (0.00 sec)
(3)例二:密码安全性提醒
  • 要求:每次在修改密码以后进行判断
    • 如果密码是全新的密码,不做提醒
    • 如果密码和之前的密码一样,提醒安全性较低
drop trigger if exists tri_employee_after_update;
delimiter $$
create trigger tri_employee_after_update after update on employee
for each row
beginif old.password = new.password thensignal sqlstate '01000'set message_text = '修改成功,但密码安全性较低,和之前一样';# 不允许返回值# select '修改成功,但密码安全性较低,和之前一样';end if;	
end $$
delimiter ;
  • 检查检查
# 查看id为2的员工密码
select password 
from employee
where id=2;
+----------+
| password |
+----------+
| 222      |
+----------+
# 修改的密码和之前一样
update employee
set password = '222'
where id = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
  • 很遗憾,不会触发
    • 但是可以修改错误代码为45000
    • 验证逻辑是没有问题的
    • 只不过这种应用并不适合
    • 有待更改完善或者使用新的例子
(4)一些模板
  • 插入数据之前,自动生成唯一标识符
CREATE TRIGGER generate_uuid_trigger BEFORE INSERT ON table_name
FOR EACH ROW
BEGINSET NEW.uuid = UUID();
END
  • 更新数据时,更新最后修改时间
CREATE TRIGGER update_last_modified_trigger BEFORE UPDATE ON table_name
FOR EACH ROW
BEGINSET NEW.last_modified = NOW();
END
  • 删除数据时,将记录添加到历史记录表
CREATE TRIGGER archive_deleted_record_trigger AFTER DELETE ON table_name
FOR EACH ROW
BEGININSERT INTO history_table (id, deleted_at)VALUES (OLD.id, NOW());
END
  • 插入数据进行某个条件检查,不满足抛出异常
CREATE TRIGGER check_condition_trigger BEFORE INSERT ON table_name
FOR EACH ROW
BEGINIF NEW.column_name < 10 THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Value must be greater than 10';END IF;
END
  • 日志模板
# 主要是为了区分错误执行语句和全局结束语句
create trigger tri_after_insert_cmd after insert on cmd 
for each row
beginif NEW.success = "no" theninsert into errlog(err_cmd,err_time)values(NEW.cmd,NEW.sub_time);end if;
end

【三】事务

【1】说明

(1)什么是事务
  • 事务是数据库管理系统中的一个概念,它表示一系列相关的数据库操作,这些操作要么全部成功执行要么全部失败回滚,保证数据库的一致性和完整性。
(2)事务的四大特性
  • 原子性(Atomicity): 事务被视为一个原子操作,不可再分割。要么所有的操作都成功执行,要么所有的操作都会被回滚到事务开始前的状态,确保数据的一致性。
  • 一致性(Consistency): 事务执行前后,数据库应保持一致的状态。在事务开始之前和结束之后,数据库必须满足所有的完整性约束,如数据类型、关系等。
  • 隔离性(Isolation): 事务的执行结果对其他并发执行的事务是隔离的。即一个事务的执行不应受到其他事务的干扰,各个事务之间应该相互独立工作,从而避免数据的不一致性。
  • 持久性(Durability): **一旦事务被提交,其结果应该永久保存在数据库中,并且可以被系统故障恢复。**即使系统发生宕机或崩溃,事务提交后的更改也应该是永久性的。
(3)简单示例
  • 用户提交订单的操作流程可以总结为以下步骤:

    1. 检查库存: 系统检查所需商品的库存是否足够。
    2. 库存充足: 如果库存足够,系统继续执行后续步骤;如果库存不足,系统提示用户库存不足,无法完成订单。
    3. 扣减库存: 系统减少所购商品对应的库存数量。
    4. 生成订单: 系统生成一个新的订单,包括订单号、商品信息、购买数量、价格等相关信息。
    5. 计算总价: 系统根据订单中的商品信息和购买数量计算订单的总价格。
    6. 更新用户账户: 根据用户选择的支付方式,在扣除相应金额后,系统更新用户账户余额或积分。
    7. 生成支付信息: 系统生成相应的支付信息,以便用户完成支付。
    8. 通知物流部门: 系统通知物流部门准备配送相关商品。
    9. 发送订单确认邮件/短信: 系统向用户发送订单确认的邮件或短信,包括订单详细信息、配送信息等。
    10. 监控商品配送: 系统跟踪订单的配送情况,并向用户提供订单状态更新。
    11. 完成订单: 当用户收到商品并确认满意后,订单状态会被更新为“已完成”。
  • 这个流程确保了在用户提交订单后,系统进行了必要的检查、处理、通知和更新操作,以提供完整的购物体验。

【2】使用

(1)创建事务
  • 模板
# 开启事务
start transaction;# 执行事务操作
......
......# 提交事务或者回滚事务
# 此时会结束事务
commit; | rollback;
(2)示例:转钱
  • 任务

    • 初始每个人都是1000元

    • 让我们来试试一号给二号转100元试试

  • 开启事务

start transaction;
  • 1号员工扣钱、2号员工加钱
update employee 
set money = money - 100
where id = 1;
update employee 
set money = money + 100
where id = 2;
  • 查看此时他们的金额
select id, money
from employee
where id in (1, 2);
+----+-------+
| id | money |
+----+-------+
|  1 |   900 |
|  2 |  1100 |
+----+-------+
  • 回滚试试,看看会发生什么
    • 金额变回来了
rollback;
select id, money
from employee
where id in (1, 2);
+----+-------+
| id | money |
+----+-------+
|  1 |  1000 |
|  2 |  1000 |
+----+-------+
  • 再次转钱
update employee 
set money = money - 100
where id = 1;
update employee 
set money = money + 100
where id = 2;
  • 提交事务
commit;
  • 尝试回滚并查看数据
    • 虽然回滚没有报错
    • 但是数据并不会发生回滚了
    • 因为事务在commit以后已经结束了
rollback;
select id, money
from employee
where id in (1, 2);
+----+-------+
| id | money |
+----+-------+
|  1 |   900 |
|  2 |  1100 |
+----+-------+

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/248760.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

腾讯云部署vue+node项目

文章目录 一、安装宝塔二、vue项目部署三、node项目部署 前言: 关于项目部署,一开始也是找了很多资料,费了点时间,所以记录一下。希望能对各位有所帮助。 一、安装宝塔 1.首先在控制台,进入云服务器的终端界面 2.输入命令和密码获取权限,并且安装宝塔界面 yum install -y w…

如何把png改成jpg格式?分享5个好用的方法!

你是否经常遇到需要将PNG格式的图片转换成JPG格式的情况&#xff1f;无论是因为工作需要&#xff0c;还是为了适应不同的平台和设备&#xff0c;格式转换都是我们经常要面对的问题。那么&#xff0c;如何快速、简单地完成这个任务呢&#xff1f;今天&#xff0c;我们就来为你揭…

计算机网络——IP协议

前言 网络层的主要负责地址分配和路由选择,ip负责在网络中进行数据包的路由和传输。 IPv4报文组成&#xff08;了解&#xff09; IPv4首部&#xff1a;IPv4首部包含了用于路由和传输数据的控制信息&#xff0c;其长度为20个字节&#xff08;固定长度&#xff09;。 版本&#…

图灵之旅--ArrayList顺序表LinkedList链表栈Stack队列Queue

目录 线性表顺序表ArrayList简介ArrayList使用ArrayList的构造ArrayList常见操作ArrayList的遍历ArrayList的扩容机制利用ArrayList洗牌ArrayList的优缺点 链表链表的实现双向链表的实现 LinkedListLinkedList引入LinkedList的使用LinkedList的构造LinkedList的常用方法介绍Lin…

Power ModeII 插件的下载与使用-----idea

下载 Marketplace里面搜索下载即可 使用 下载后重启软件就可以用了 下面是一些关于Power ModeII &#xff0c;我的个性化设置截图 以及相关设置解释 插件或扩展的设置面板【用于给代码编辑器或集成开发环境&#xff08;IDE&#xff09;添加视觉效果】 主要设置 ENTER POWE…

Maven安装,学习笔记,详细整理maven的一些配置

Maven 1. 初识Maven 2. Maven概述 Maven模型介绍 Maven仓库介绍 Maven安装与配置 3. IDEA集成Maven 4. 依赖管理 01. Maven课程介绍 1.1 课程安排 学习完前端Web开发技术后&#xff0c;我们即将开始学习后端Web开发技术。做为一名Java开发工程师&#xff0c;后端 Web开发技术…

STM32——USART

一、通信 1.1通信是什么&#xff1b; 通信是将一个设备的数据发送到另一个设备中&#xff0c;从而实现硬件的扩展&#xff1b; 1.2通信的目的是什么&#xff1b; 实现硬件的扩展-在STM32中集成了很多功能&#xff0c;例如PWM输出&#xff0c;AD采集&#xff0c;定时器等&am…

数据结构和算法笔记5:堆和优先队列

今天来讲一下堆&#xff0c;在网上看到一个很好的文章&#xff0c;不过它实现堆是用Golang写的&#xff0c;我这里打算用C实现一下&#xff1a; Golang: Heap data structure 1. 基本概念 满二叉树&#xff08;二叉树每层节点都是满的&#xff09;&#xff1a; 完全二叉树&a…

STM32标准库——(5)EXTI外部中断

1.中断系统 中断&#xff1a;在主程序运行过程中&#xff0c;出现了特定的中断触发条件&#xff08;中断源&#xff09;&#xff0c;使得CPU暂停当前正在运行的程序&#xff0c;转而去处理中断程序&#xff0c;处理完成后又返回原来被暂停的位置继续运行 中断优先级&#xff…

【QT+QGIS跨平台编译】之十一:【libzip+Qt跨平台编译】(一套代码、一套框架,跨平台编译)

文章目录 一、libzip介绍二、文件下载三、文件分析四、pro文件五、编译实践一、libzip介绍 libzip是一个开源C库,用于读取,创建和修改zip文件。 libzip可以从数据缓冲区,文件或直接从其他zip归档文件直接复制的压缩数据中添加文件。在不关闭存档的情况下所做的更改可以还原…

uniapp微信小程序-input默认字的样式

需要的是这样的 问题 正常是在input框上面写样式就行&#xff0c;但是uniapp不起作用 解决 直接在input上写placeholder-style"color就解决了 <input class"findInput" type"text" placeholder"关键词查询"placeholder-style"co…

Phoncent博客,探索Rie Kudan的GPT创作之举

近日&#xff0c;大家都在谈论日本作家Rie Kudan&#xff0c;她凭借其小说《东京共鸣塔》&#xff08;"Tokyo-to Dojo-to"&#xff09;荣获了日本极具声望的芥川奖。这本小说引起了广泛的讨论和思考&#xff0c;因为令人惊讶的是&#xff0c;Kudan在其中直接引用了人…

2023美赛A题之Lotka-Volterra【完整思路+代码】

这是2023年的成功&#xff0c;考虑到曾经付费用户的负责&#xff0c;2024年可以发出来了。去年我辅导队伍数量&#xff1a;15&#xff0c;获奖M为主&#xff0c;个别F&#xff0c;H&#xff0c;零S。言归正传&#xff0c;这里我开始分享去年的方案。由于时间久远&#xff0c;我…

【华为 ICT HCIA eNSP 习题汇总】——题目集9

1、缺省情况下&#xff0c;广播网络上 OSPF 协议 Hello 报文发送的周期和无效周期分别为&#xff08;&#xff09;。 A、10s&#xff0c;40s B、40s&#xff0c;10s C、30s&#xff0c;20s D、20s&#xff0c;30s 考点&#xff1a;①路由技术原理 ②OSPF 解析&#xff1a;&…

【ArcGIS微课1000例】0099:土地利用变化分析

本实验讲述在ArcGIS软件中基于两期土地利用数据,做土地利用变化分析。 文章目录 一、实验描述二、实验过程三、注意事项一、实验描述 对城市土地利用情况进行分析时,需要考虑不同时期土地利用图层在空间上的差异性,如农用地转建筑用地的空间变化。而该变化过程表现为各时期…

【LeetCode】排序精选12题

目录 排序&#xff1a; 1. 合并区间&#xff08;中等&#xff09; 2. 数组的相对排序&#xff08;简单&#xff09; 快速排序&#xff1a; 1. 颜色分类&#xff08;中等&#xff09; 2. 排序数组&#xff08;中等&#xff09; 3. 数组中的第K个最大元素&#xff08;中等…

HCIA-HarmonyOS设备开发认证-3.内核基础

目录 前言目标一、进程与线程待续。。。 前言 对于任何一个操作系统而言&#xff0c;内核的运行机制与原理是最为关键的部分。本章内容从多角度了解HarmonyOS的内核运行机制&#xff0c;涵盖进程与线程的概念&#xff0c;内存管理机制&#xff0c;网络特性&#xff0c;文件系统…

Arduino开发实例-DRV8833电机驱动器控制直流电机

DRV8833电机驱动器控制直流电机 文章目录 DRV8833电机驱动器控制直流电机1、DRV8833电机驱动器介绍2、硬件接线图3、代码实现DRV8833 使用 MOSFET,而不是 BJT。 MOSFET 的压降几乎可以忽略不计,这意味着几乎所有来自电源的电压都会传递到电机。 这就是为什么 DRV8833 不仅比基…

Excel中将16进制数转化成10进制(有/无符号)

Excel中将16进制数转化成10进制&#xff08;有/无符号&#xff09; Excel或者matlab中常用XXX2XXX进行不同进制的转换 16进制转10进制&#xff08;无符号数&#xff09;&#xff1a;HEX2DEC 16进制转10进制&#xff08;有符号数&#xff09;&#xff1a; FA46为例&#xff0c…

【ARM Trace32(劳特巴赫) 使用介绍 6.1 -- 外设寄存器查看与修改】

请阅读【Trace32 ARM 专栏导读】 文章目录 外设寄存器查看与修改寄存器值修改外设寄存器查看与修改 外设寄存器的查看与修改,离不开TRACE32的外设文件(*.per),per文件一般存在于TRACE32的安装根目录下。 一般情况下,在调试时,TRACE32会根据当前选择的芯片名自动选择合适的…