mysql高级
视图
【1】介绍
视图是一张虚拟表，是通过查询其他一张表或者多张表的结果而保存的，是基于 SQL 语句的结果集的数据库对象。
视图与真实的表不同，因为它本身不存储数据。
视图是一种可以保存复杂sql查询的方式，并且像普通表一样使用。
视图可以包含一个或多个表中的数据，并可以用于隐藏复杂的 SQL 语句、简化用户的数据库操作，以及提供数据的安全性。
【2】视图的优点
安全性：可以限制用户对数据的访问
方便性：简化复杂的查询
抽象：隐藏数据的底层复杂性
【3】语法
-- 创建视图
create view 视图名 as
select 字段名...
from 表名
where 条件
;
-- 查询视图
select * from 视图名;
【4】例子
-- 创建一个员工名称以及领导名称的视图
-- 查询员工名称以及领导名称的语句
select t2.name as emp_name,t1.name as leader_name from emp as t1 join emp as t2
on t1.id = t2.leader_id;
-- 创建视图
create view view_1 as
select t2.name as emp_name,t1.name as leader_name from emp as t1 join emp as t2
on t1.id = t2.leader_id
-- 调用视图
select * from view_1;
【5】注意事项
- 修改视图可能会影响依赖于该视图的其他数据库对象。
- 视图性能可能不如直接查询基础表，特别是在涉及大量数据和复杂查询时。
视图是数据库中非常有用的工具，特别是在需要简化数据库操作和提高数据安全性时
触发器
【1】介绍
MySQL触发器是一种特殊类型的存储过程，它会在特定的数据库表上执行某些操作，会在insert,update,delete等操作执行时自动触发。
【2】特点
自动执行：在定义触发器的特定事件自动执行
数据完整性：有助于保持数据的一致性和完整性
隐藏业务逻辑：可以在数据库层面实现业务逻辑，对应用程序透明。
监控与日志记录：用于监控数据变化和操作日志记录很方便
【3】语法
delimiter $$
create trigger 触发器名
<before|after> <insert|update|delete>
on <表名> for each row
begin
-- sql语句
end$$
delimiter ;
【4】例子
-- 每当向user表中插入数据，就记录到user_logs表中
-- 创建触发器
drop trigger if exists user_insert_trigger;
delimiter $$
create trigger user_insert_trigger
before insert on user for each row
begin
insert into user_logs values(null,'insert',now(),new.id,concat('插入的数据:','username=',new.username,'password=',new.password));
end;
$$
delimiter ;
事务
【1】介绍
事务是一个作为单个逻辑单元执行的一系列操作。在事务中，要么所有的操作都成功完成，要么所有的操作都不生效。事务主要用来处理需要多个步骤的复杂操作，确保数据的一致性和完整性。
【2】事务的基本特性（ACID）
原子性（Atomicity）：
- 事务是不可分割的最小工作单位，事务内的操作要么全部完成，要么全部不执行。
- 如果事务的一部分操作失败，整个事务将回滚到开始状态。
一致性（Consistency）：
- 事务必须使数据库从一个一致性状态转变到另一个一致性状态。
- 一致性确保事务完成后，所有的数据规则都得到遵守，例如外键约束。
隔离性（Isolation）：
- 事务的执行不应该被其他事务干扰。
- 即使多个事务同时运行，每个事务应该与其他事务隔离。
持久性（Durability）：
- 一旦事务提交，它对数据库的改变就是永久性的，即使系统崩溃也不会丢失数据。
【3】提交事务模式
每一个sql语句都是一个事务，在默认情况下，sql语句会自动提交事务，当然我们也可以修改成手动提交事务
结束一个事务的方式有三种，一种是提交事务，另一种是回滚，第三种是直接断开连接
-- 查看当前的提交事务模式
select @@autocommit #如果查出来的结果是1，则为自动提交事务
-- 修改提交事务模式为手动提交
set @@autocommit = 0
-- 提交事务
commit;
-- 回滚
rollback;
【4】并发事务引发的问题
-
脏读
一个事务读取到了另外一个事务还没有提交的数据
-
不可重复度
一个事务先后读取到同条记录，但是两次读取到的数据不同
事务a 第一次查询数据为500 第二次查询为400
事务b 修改了数据为400 并且commit
-
幻读
一个事务查询某一条记录的时候，没有对应的记录，但是在插入这条记录的时候发现这条记录已经存在，就好像出现幻觉了一样
【5】事务隔离级别
| 隔离级别 | 描述 | 优点 | 缺点 |
|---|---|---|---|
| READ UNCOMMITTED | 事务可以读取未提交的数据更改（脏读）。 | - | 可能导致脏读、不可重复读和幻读问题。 |
| READ COMMITTED | 事务只能读取已提交的数据更改。 | 避免脏读。 | 可能遇到不可重复读和幻读问题。 |
| REPEATABLE READ | 保证在事务内多次读取同一数据结果一致。 | 避免脏读和不可重复读。 | 可能遇到幻读问题。 |
| SERIALIZABLE | 事务完全串行执行。 | 避免脏读、不可重复读和幻读。 | 性能开销最大。 |
-- 查看事务隔离级别
select @@transaction_isolation;
-- 修改事务隔离级别
set {session|global}transaction isolation level <隔离级别>;
【6】语法
start transaction; -- 开始事务 也可以用begin代替
select * from account where name = 'hqq';
update account set balance = balance - 100 where name = 'hqq';
update account set balance = balance + 100 where name = 'green';
-- 假设这里有一个检查操作是否成功的逻辑
commit; -- 如果全部成功则提交事务
rollback; -- 如果遇到异常则执行回滚操作
存储过程
【1】介绍
- 存储过程是一种在数据库中保存的sql语句集合，它可以执行一系列的数据库操作。
- 使用存储过程有以下几点好处
- 性能提升：存储过程在服务器端执行，减少了网络通信量和延迟。
- 代码重用和标准化：存储过程可以被多次调用，有助于代码重用和保持一致性。
- 安全性：可以限制对特定数据的直接访问，而是通过存储过程提供控制的接口。
- 维护：修改存储过程比更改多个应用程序中的SQL语句要容易
【2】创建和调用存储过程
- 在MySQL中，你可以使用
CREATE PROCEDURE语句来创建一个存储过程。这里是一个基本示例：
-- 创建存储过程
delimiter $$
create procedure p1()
begin
select * from dep;
end$$
delimiter ;
-- 调用存储过程
call p1;
delimiter $$
create procedure p1()
begin
select name,
if(gender='male','小哥哥','小姐姐') from emp;
end$$
delimiter ;
【3】参数
- 在mysql中，存储过程可以接收三种类型的参数
- 输入参数(in):这就像是你给这个小程序的原材料。你告诉它需要用到什么，它就会用这些材料来完成任务。比如，你可以告诉它需要查询哪个用户的信息。
- 输出参数（OUT）：这就像是小程序做好的菜肴。完成任务后，它会把结果给你。比如，小程序可以告诉你某个用户的年龄。
- 输入输出参数（INOUT）：这就像是你给小程序一个半成品，让它加工后再还给你。你给它一些信息，它处理这些信息，然后把处理后的信息返回给你。比如，你给它一个数字，它把这个数字加倍后还给你。
-- 语法
create procedure <存储过程名称>([in/out/inout <参数名><参数类型>])
begin
sql语句
end;
-- 例子
drop procedure if exists p1;
delimiter $$
create procedure p1(in age int,out res varchar(15))
begin
set res=case
when age < 20 then '小奶狗'
when age between 20 and 30 then '小年轻'
when age between 30 and 40 then '小叔叔'
else '老前辈'
end;
end$$
delimiter ;
