MySQL进阶语法【视图触发器事务存储过程流程控制】

MySQL进阶语法

【一】视图

【1】概念

在MySQL数据库中，视图（View）是虚拟的表，其内容是基于执行特定查询所得到的结果集。
视图是通过查询来定义的，它可以包含一个或多个表中的特定列，也可以包含计算字段、聚合函数等。
视图并不实际存储数据，而是动态地从基本表中获取数据并进行组合，每当查询视图时，都会实时地生成结果。

MySQL数据库视图：视图定义、创建视图、修改视图-CSDN博客

形象的例子：

皇上想要了解皇宫的国库的相关情况，想知道酒窖有什么酒，剩多少，窖藏多少年，于是派最信任的高公公去清点，高公公去国库清点后报给了朕；朕又想知道藏书情况，于是又派高公公去清点并回来报告给朕，又想知道金银珠宝如何，又派高公公清点。。。过一段时间又想知道藏书情况，高公公还得重新再去清点，皇上问一次，高公公就得跑一次路。

后来皇上觉得高公公不容易，就成立了国库管理部门，小邓子负责酒窖，小卓子负责藏书，而小六子负责金库的清点。。。后来皇上每次想了解国库就直接问话负责人，负责人就按照职责要求进行汇报。

上述图中
- 小邓子，小卓子，小六子就是一个个的视图
- 当我们需要查询特定的数据时，就可以直接调用视图，由视图去执行查询操作

【2】优缺点

【2.1】优点

简化复杂查询：视图可以将复杂的查询逻辑封装在一个可重用的结构中。这样，用户不必编写长而复杂的SQL语句，而是可以直接查询视图来获取他们需要的数据，从而简化了查询过程。
数据安全性：通过视图，可以隐藏基本表的特定列或行，只向用户提供他们需要的数据子集。这有助于确保敏感数据不被未经授权的用户访问，从而提高了数据安全性。
简化权限管理：通过在视图上设置权限，可以方便地控制用户对数据的访问权限。这样，数据库管理员可以根据用户的角色和需求，轻松地管理和分配权限。
数据抽象：视图提供了一种数据抽象层，使用户不必关心底层数据结构的细节。这样，即使基础表的结构发生变化，视图仍然可以保持不变，从而提高了应用程序的可维护性和灵活性。
性能优化：在某些情况下，视图可以提高查询性能。通过将常用的查询结果存储为视图，可以减少查询的执行时间，尤其是在涉及到复杂计算或连接多个表的情况下。
重用查询逻辑：通过创建视图，可以将常用的查询逻辑抽象出来，以便多次重用。这样，可以减少重复编写相同的查询代码，提高了代码的可重用性和可维护性。

【2.2】缺点

性能开销：在某些情况下，查询视图可能会引入性能开销。因为视图本质上是一个虚拟的表，查询视图实际上是执行基础查询并生成结果集，这可能会增加查询的执行时间，特别是当涉及到复杂的视图定义或底层表数据量较大时。
限制：某些数据库管理系统对视图的使用有一些限制，例如不支持在视图中使用某些特定的SQL语句或函数，或者对视图的更新操作有限制。这可能会限制视图的灵活性和适用性。
数据一致性：视图的数据是根据基础表的数据动态生成的，因此可能存在数据一致性的问题。如果基础表的数据发生了变化，但视图没有及时更新，可能会导致视图的数据不一致。
复杂性：在某些情况下，使用视图可能会引入额外的复杂性。特别是当涉及到多个嵌套视图或视图之间的依赖关系时，可能会使数据库结构变得复杂和难以理解。
维护困难：在某些情况下，维护视图可能会变得困难。特别是当涉及到复杂的视图定义或视图依赖于多个基础表时，对视图的修改和维护可能会变得复杂和容易出错。

虽然视图具有一些缺点，但在许多情况下，它们仍然是一个非常有用的工具，可以帮助简化查询、提高数据安全性和重用查询逻辑。因此，在使用视图时，需要权衡其优缺点，并根据具体情况来决定是否使用视图。

【3】用法

【3.0】数据准备

-- 建部门表
create table dep(
		id int PRIMARY KEY AUTO_INCREMENT,
    name varchar(20)
);

-- 建员工表
CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    sex ENUM("male","female") NOT NULL DEFAULT "male",
    age INT,
    dep_id INT
);

-- 插入部门数据
insert into dep values
("200","技术部"),
("201","人力资源"),
("202","销售部"),
("203","运营部"),
("204","售后部");

-- 插入员工数据
insert into emp(name,sex,age,dep_id) values
("alex","male",18,200),
("alice","female",18,201),
("peter","male",38,202),
("tom","male",18,203),
("jerry","male",28,204),
("happy","male",18,205);

【3.1】创建视图

create view view_name as 
select column1,column2,... 
from tablename 
where condition;

create view 视图名 as 查询的SQL语句;

-- 创建一个视图展示员工id，员工姓名，员工部门id，员工部门姓名
create view emp2dep as
-- 联表查询
select emp.id,emp.name,dep_id,dep.name as dep_name from emp
left join dep on emp.dep_id = dep.id
union
select emp.id,emp.name,dep_id,dep.name as dep_name from emp
right join dep on emp.dep_id = dep.id;

【3.2】使用视图查询

select * from 视图名;
-- 视图其实就是一个虚拟表，可以通过select查询数据

【3.3】更新视图[删除再新建]

修改视图，可以使用 ALTER VIEW 语句。但是，目前MySQL不支持直接修改视图的定义，因此通常的做法是先删除现有的视图，然后重新创建

drop view if exists view_name;

create view view_name as 查询语句;

强调
1. 在硬盘中，视图只有表结构文件，没有表数据文件
2. 视图通常是用于查询，尽量不要修改视图中的数据

【3.4】删除视图

drop view view_name;

【二】触发器

【1】概念

触发器（Triggers）是在特定事件发生时自动执行的一系列SQL语句集合，它们与特定的表相关联。
当表上的某个事件（如INSERT、UPDATE、DELETE）发生时，触发器会被触发执行，通常用于在数据库中实现数据的完整性约束、审计跟踪、日志记录等功能。

【2】优缺点

优点：

数据完整性：触发器可以用来强制实施数据库的数据完整性约束，例如检查约束、外键约束等。它们可以确保数据在进行修改时保持一致性。
自动化任务：触发器可以自动执行指定的任务或逻辑，无需手动干预。这对于执行诸如审计跟踪、日志记录、数据变更监视等任务非常有用。
实时性：触发器能够实时响应数据库中的事件，因此可以在数据更改发生时立即执行相应的操作，而不需要等待手动触发。
简化应用程序逻辑：通过将一些逻辑转移到数据库层面，可以简化应用程序的逻辑。这有助于保持应用程序代码的简洁性和清晰性。
更好的性能：在某些情况下，触发器可以提高性能。例如，在执行复杂的计算或更新操作时，将逻辑放在触发器中可能比在应用程序中执行更有效率。

缺点：

隐藏性：触发器的存在和行为可能不明显，特别是在数据库中存在大量触发器时。这可能使数据库的行为变得不透明，增加了理解和维护的难度。
隐式性能开销：触发器的执行会引入额外的性能开销，尤其是当触发器逻辑较为复杂时。这可能会影响数据库的性能和响应时间。
复杂性：触发器的使用可能增加数据库的复杂性。特别是当存在多个触发器、触发器之间存在依赖关系、或者触发器逻辑较为复杂时，可能会使数据库结构变得混乱和难以理解。
难以调试：触发器中的错误可能难以调试和定位。由于触发器是在特定的事件发生时自动执行的，因此很难追踪触发器的执行过程和状态。
限制：某些数据库管理系统对触发器的支持有一些限制，例如不能在触发器中执行某些特定的SQL语句、不能在触发器中调用存储过程或函数等。

【3】用法

【3.1】创建触发器

delimiter $$ -- 触发器的结束需要分号【;】，需要先将结束符修改成其他符号以保证触发器语句成功执行

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

delimiter ; -- 创建完毕后将结束符调整回来

create table t1(name varchar(64),age tinyint(3));
create table log(logging text,log_time datetime);

delimiter $$
create trigger bef_ins_t1 
before insert on t1 for each row 
begin
	insert into log(logging,log_time) values('insert one message',now());
end $$
delimiter ;

insert into t1 values('user',20);

【3.2】查看触发器

show triggers;
show triggers \G;

【3.3】删除触发器

drop trigger 触发器名字;

【3.4】修改触发器[删除再新建]

MySQL 不直接提供修改触发器定义的选项，但是可以通过删除现有的触发器，然后重新创建来实现修改触发器的目的。

drop trigger 触发器名字;
delimiter $$ -- 触发器的结束需要分号【;】，需要先将结束符修改成其他符号以保证触发器语句成功执行

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

delimiter ; -- 创建完毕后将结束符调整回来

【4】案例：自动生成错误日志

# 案例
CREATE TABLE cmd (
    id INT PRIMARY KEY auto_increment,
    USER CHAR (32),
    priv CHAR (10),
    cmd CHAR (64),
    sub_time datetime, #提交时间
    success enum ('yes', 'no') #0代表执行失败
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);

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 ;  # 结束之后记得再改回来，不然后面结束符就都是$$了

#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('egon','0755','ls -l /etc',NOW(),'yes'),
    ('egon','0755','cat /etc/passwd',NOW(),'no'),
    ('egon','0755','useradd xxx',NOW(),'no'),
    ('egon','0755','ps aux',NOW(),'yes');

# 查询errlog表记录
select * from errlog;
# 删除触发器
drop trigger tri_after_insert_cmd;

【三】事务

【1】概念

事务（Transaction）是指数据库操作的一个执行单元，它是由一系列数据库操作组成的逻辑工作单元。
在 MySQL 中，事务是一组SQL语句的执行，它们被视为一个单独的工作单元。
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务要么全部执行成功，要么全部失败，具有原子性、一致性、隔离性和持久性（ACID）的特性。

【2】事务的ACID特性

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

【3】事务的使用场景

数据一致性要求高：当多个数据库操作需要保持一致性时，可以将它们放在一个事务中执行，以确保操作要么全部执行成功，要么全部失败。
多步骤操作：当一个操作需要分为多个步骤执行时，可以将这些步骤放在一个事务中，以确保它们作为一个原子操作执行。
并发控制：当多个用户同时访问数据库时，可以使用事务来控制并发访问，以确保数据的一致性和隔离性。
系统故障恢复：当系统发生故障时，可以使用事务来确保数据库可以恢复到之前的一致状态。

【3.1】事务使用场景的具体例子

银行转账：
- 在银行转账过程中，需要确保从一个账户扣除金额并将其转移到另一个账户
- 这涉及到两个数据库操作：从一个账户扣款和向另一个账户添加款项。
- 使用事务可以确保这两个操作要么都成功执行，要么都不执行。
- 如果其中一个操作失败，事务会回滚，保持数据的一致性，以防止资金的不正确转移。
在线订购：
- 在进行在线订购时，通常需要在数据库中执行多个操作，例如减少库存、创建订单、扣除款项等。
- 使用事务可以确保这些操作以原子方式执行，以避免出现订单创建但库存未减少或款项未扣除的情况。
日志记录：
- 在记录日志时，可能需要将日志信息插入到日志表中，并将相关信息更新到其他表中。
- 使用事务可以确保所有日志信息都被成功插入，并且相关信息在其他表中也被正确更新
- 以保持日志的完整性和一致性。
数据导入：
- 在将数据从一个数据库导入到另一个数据库时，可能需要执行多个操作，包括插入新记录、更新现有记录和删除旧记录等。
- 使用事务可以确保数据导入过程中的所有操作都以原子方式执行，以避免出现部分导入或导入错误的情况。
电子商务订单处理：
- 在电子商务系统中，订单处理可能涉及多个步骤，例如验证订单、更新库存、生成发货信息等。
- 使用事务可以确保所有订单处理操作以原子方式执行，以避免出现订单状态不一致或库存错误的情况。

【4】用法

【4.1】事务处理的两种方法

1、用 BEGIN, ROLLBACK, COMMIT 来实现

BEGIN 或 START TRANSACTION：开用于开始一个事务。
ROLLBACK 事务回滚，取消之前的更改。
COMMIT：事务确认，提交事务，使更改永久生效。

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。

因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

【4.2】开启事务

begin;
start transaction;

【4.3】提交事务/ 回滚事务

commit; -- 事务确认，提交事务，使更改永久生效
rollback; -- 事务回滚，取消之前的更改

【4.4】保存点

SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

-- 创建保存点
savepoint 保存点名称;
-- 回滚至保存点
rollback to 保存点名称;
-- 删除保存点
release savepoint 保存点名称;


begin; -- 开启事务
-- 插入数据
insert into t1 values('001',30);
insert into t1 values('002',30);
savepoint point1; -- 创建保存点
insert into t1 values('003',30);
insert into t1 values('004',30);
rollback to point1; -- 回滚至保存点

【4.5】转账案例解释

【4.5.1】SQL语句

-- 转账案例解释
create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('jason',1000),
('egon',1000),
('tank',1000);

# 修改数据之前先开启事务操作
start transaction;

# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元

# 回滚到上一个状态
rollback;

# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;
"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""


# 站在python代码的角度，应该实现的伪代码逻辑，
try:
	# 少了开事务...
    update user set balance=900 where name='jason'; #买支付100元
    update user set balance=1010 where name='egon'; #中介拿走10元
    update user set balance=1090 where name='tank'; #卖家拿到90元
except 异常:
    rollback;
else:
    commit;

【4.5.2】使用pymysql实现转账案例

import pymysql


def transfer_funds(sender_id, receiver_id, amount):
    try:
        # 创建数据库连接
        connection = pymysql.connect(
            host="数据库主机名",
            user="用户名",
            password="密码",
            database="数据库名称"
        )

        # 建立游标
        cursor = connection.cursor()
        
        # 开启事务
        cursor.execute('START TRANSACTION;')
        
        # 查询发送者的余额
        cursor.execute("SELECT balance FROM accounts WHERE id = %s", (sender_id,))
        sender_balance = cursor.fetchone()[0]

        # 检查发送者余额是否足够
        if sender_balance < amount:
            return "Insufficient funds"

        # 更新发送者余额
        cursor.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, sender_id))

        # 更新接收者余额
        cursor.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, receiver_id))

        # 提交事务
        connection.commit()

        # 关闭游标和连接
        cursor.close()
        connection.close()

        return "Transfer successful"
    except pymysql.Error as e:
        # 回滚事务
        connection.rollback()

        # 关闭游标和连接
        cursor.close()
        connection.close()

        return str(e)


# 使用示例
result = transfer_funds(1, 2, 100)
print(result)  # 输出："Transfer successful"

【四】存储过程

【1】概念

存储过程（Stored Procedure）是一组预编译的 SQL 语句，它们作为一个单元被保存在数据库中，可以被多次调用。
存储过程本质上就是一个指向一系列执行步骤或操作的标识符。
存储过程通常用于执行特定的任务或操作，并且可以接受参数，返回结果集或执行其他数据库操作。

【2】优缺点

优点：

性能优化：存储过程是预编译的，可以在数据库中缓存，因此可以提高数据库操作的性能。此外，通过减少与数据库服务器之间的通信次数，存储过程还可以减少网络开销，进一步提高性能。
简化复杂操作：存储过程允许将复杂的操作和业务逻辑封装在一个单独的单元中，从而简化了数据库操作和应用程序代码。这有助于提高代码的可维护性和可重用性。
安全性：存储过程可以实现对数据库的安全性控制，只允许特定的用户执行特定的操作。通过存储过程，可以减少对数据库对象的直接访问，从而提高数据的安全性。
减少网络流量：存储过程可以在数据库服务器上执行，减少了与客户端之间的数据传输，从而减少了网络流量。
提高可维护性：存储过程可以在数据库中进行管理和维护，从而简化了代码的管理和更新。通过存储过程，可以将数据库操作与应用程序代码分离，使得数据库结构的修改不需要修改应用程序代码，从而降低了维护成本。
事务管理：存储过程允许将一系列数据库操作作为一个事务进行管理，以确保数据库的一致性和完整性。通过存储过程，可以在一个单元中执行多个操作，并确保它们作为一个原子操作执行。

缺点：

学习曲线：编写和管理存储过程可能需要一定的学习成本，特别是对于初学者来说，可能需要一些时间来熟悉存储过程的语法和功能。
性能下降：在某些情况下，存储过程的执行可能会导致性能下降。特别是在存储过程中包含复杂的逻辑或大量的数据库操作时，可能会增加数据库的负载并降低性能。
维护困难：随着存储过程数量的增加，可能会增加数据库的复杂性，使得存储过程的管理和维护变得困难。特别是当存储过程之间存在依赖关系时，可能需要额外的注意来确保它们的正确性和一致性。
平台依赖性：存储过程的语法和功能在不同的数据库管理系统之间可能存在差异，因此编写的存储过程可能在不同的数据库平台上无法通用。
难以调试：存储过程中的错误可能难以调试和定位。由于存储过程是在数据库服务器上执行的，因此可能需要额外的工具和技术来调试存储过程中的问题。

【3】用法

【3.1】创建并调用存储过程

delimiter //

create procedure 存储过程名(参数[可选])
begin
    -- 存储过程的主体，包含一系列的 SQL 语句和控制结构
end //

delimiter ;

call 存储过程名; -- 调用存储过程

举栗子

delimiter $$ # 修改结束符

create procedure i1()
begin
	# 执行插入数据
	insert into t1 value('001',18);
    insert into t1 value('002',18);
    insert into t1 value('003',18);
end $$

create procedure p1()
begin
	select * from t1; # 执行查询t1表的sql语句
end $$

delimiter ;  # 将结束符修改回【;】

# 调用
call i1();
call p1();

【3.2】删除存储过程

drop procedure [if exists] 存储过程的名字;

【3.3】修改存储过程

alter procedure 存储过程名(参数)
begin
	-- 存储过程的主体，包含一系列的 SQL 语句和控制结构
end;
# 同样的也可以删除重新创建

【3.4】查看存储过程

show create procedure 存储过程名;

【3.5】使用有参存储过程

create procedure 存储过程名称(参数)
begin
	...
end;

【3.5.1】指定参数的类型

in / out / inout

create procedure 存储过程名称(
    in 参数名 参数类型, -- 表示这个参数只可以传入，而不能被返回
    out 参数名 参数类型, -- 表示这个参数可以被返回出去
    inout 参数名 参数类型, -- 表示这个参数既可以被传入，也可以被返回
)
begin
	...
end;

【3.5.2】示例

inout

delimiter $$
-- 创建存储过程
create procedure p1(inout a int,inout b int) # 参数a和参数b设置为inout数字类型
begin
	set a = a + b; # 设置a的值更新为和
	set b = a - b; # 设置b的值更新为差
end $$

delimiter ;
set @a = 10; # 设置变量a
set @b = 5; # 设置变量b
call p1(@a,@b); -- 将变量传入存储过程
select @a,@b;

in + out

delimiter $$

create procedure p2(
    in m int,  # in表示这个参数必须只能是传入不能被返回出去
    in n int,  
    out res int  # out表示这个参数可以被返回出去
)
begin
	# 查看在m < age < n 的数据
    select name,age from t1 where age > m and age < n;
    set res=0;  # 用来标志存储过程是否执行
end $$
delimiter ;


# 针对res需要先提前定义
set @res=1;  # 定义
select @res;  # 查看
call p2(15,30,@res);  # 调用
select @res;  # 查看新值

# res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储1
call p2(15,30,1);  # 报错

【4】存储过程的三种开发模型

命令式模型（Imperative Model）：
- 在命令式模型中，存储过程类似于一段程序代码，包含了一系列的命令和控制结构，如条件语句、循环等。
- 这种模型的存储过程主要关注于描述实现某种特定功能或业务逻辑的具体步骤。
- 开发人员需要编写具体的 SQL 语句，并通过控制结构来定义存储过程的行为。
- 命令式模型的存储过程更加灵活，可以执行复杂的逻辑操作，但也更容易导致存储过程的复杂性和难以维护。
声明式模型（Declarative Model）：
- 在声明式模型中，存储过程更像是一个查询或视图，它们描述了要从数据库中获取的数据，而不是具体的操作步骤。
- 开发人员主要关注于定义数据的获取方式和逻辑，而不需要关心具体的实现细节。
- 声明式模型的存储过程更加简洁和易于理解，可以提高开发人员的工作效率，但有时可能不够灵活，无法处理复杂的逻辑操作。
事件驱动模型（Event-driven Model）：
- 在事件驱动模型中，存储过程与数据库中的特定事件相关联，当事件发生时自动执行相应的存储过程。
- 常见的事件包括数据的插入、更新、删除等操作，以及特定的时间点或调度任务。
- 事件驱动模型的存储过程可以实现自动化的任务调度和数据处理，提高了数据库的灵活性和自动化程度，但开发人员需要了解数据库的事件模型和机制，以确保存储过程的正确执行。

【5】存储过程中的业务逻辑模式

业务逻辑模式是一种设计模式，强调在应用程序中将业务逻辑与数据库操作分离，并将业务逻辑封装在独立的业务逻辑层中。
在存储过程的开发中，业务逻辑模式指的是将特定的业务逻辑或业务规则实现为存储过程的模式。
在业务逻辑模式下，存储过程被用来执行与业务相关的操作，例如数据验证、完整性检查、计算结果等。
这些存储过程可以由应用程序调用，以执行数据库操作，并且可以确保数据库中的数据操作符合特定的业务规则。

【5.1】主要特点

封装业务逻辑：存储过程被用来封装特定的业务逻辑或业务规则，将其与应用程序的其他部分分离开来。
业务逻辑层：通过将业务逻辑封装在存储过程中，可以创建一个独立的业务逻辑层，提高了系统的可维护性和扩展性。
数据操作和业务逻辑的分离：业务逻辑模式强调将数据操作与业务逻辑分离，使得业务逻辑可以独立于数据库的具体实现而进行测试和维护。
提高性能：通过将复杂的业务逻辑实现为存储过程，可以提高数据库操作的性能和效率，减少了应用程序与数据库之间的数据传输量和交互次数。
保持一致性：存储过程可以确保数据库中的数据操作符合特定的业务规则，从而提高了数据的一致性和完整性。

【6】在Python中使用存储过程

注：存储过程在哪个库下面创建的只能在对应的库下面才能使用

cursor.callproc('p1', [a, b])
# 获取存储过程的输出参数或结果集
cursor.execute('select @_p1_0 as a , @_p1_1 as b')

# 内部原理：@_p1_0=a,@_p1_1=b;

import pymysql

# 连接到MySQL数据库
conn = pymysql.connect(host='localhost',
                       user='root',
                       password='密码',
                       database='库名',
                       cursorclass=pymysql.cursors.DictCursor)

try:
    with conn.cursor() as cursor:
        # 准备存储过程的输入参数
        a = 15
        b = 30

        # 调用存储过程
        cursor.callproc('p1', [a, b])

        # 获取存储过程的输出参数或结果集
        cursor.execute('select @_p1_0 as a,@_p1_1 as b')
        # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;
        result = cursor.fetchone()
        print(result)

finally:
    # 关闭数据库连接
    conn.close()

【7】存储过程与事务使用举例(了解)

delimiter //
create PROCEDURE p5(
    OUT p_return_code tinyint
)
BEGIN
    DECLARE exit handler for sqlexception
    BEGIN
        -- ERROR
        set p_return_code = 1;
        rollback;
    END;


  DECLARE exit handler for sqlwarning
  BEGIN
      -- WARNING
      set p_return_code = 2;
      rollback;
  END;

  START TRANSACTION;
      update user set balance=900 where id =1;
      update user123 set balance=1010 where id = 2;
      update user set balance=1090 where id =3;
  COMMIT;

  -- SUCCESS
  set p_return_code = 0; #0代表执行成功


END //
delimiter ;

【补】在数据库中设置变量

通过set @变量名 = 变量值;设置变量

set @res=10;  定义
select @res;  查看
# 在 MySQL 中，没有专门的删除变量的语法，你只能通过给变量赋新的值或者设置为 NULL 来模拟删除变量
set @res = null;

【补】快速导出数据

通过navicat【数据连接】，快速将表结构以及表数据进行转移

【五】流程控制

流程控制语句一般也与delimiter【修改结束符】配合使用
MySQL中的循环结构只能在存储过程或函数中使用。

【1】if条件语句

【1.1】单分支[if-then-else-end if]

delimiter //

-- 单分支
if 条件 then
	条件成立代码块;
else
	条件不成立代码快;
end if // -- 表示if语句结束

delimiter ;

【1.2】多分支[if-then-elseif-then-end if]

delimiter //

-- 多分支
if 条件1 then
	条件1成立代码块;
elseif 条件2 then
	条件2成立的代码块;
elseif 条件3 then
	条件3成立的代码块;
else
	以上条件都不成立的代码块;
end if // -- 表示if语句结束


delimiter ;

【1.3】示例

# if条件语句 在存储过程中的运用
delimiter //
CREATE PROCEDURE proc_if (in i int)
BEGIN
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;
END //
delimiter ;

set @i = 2;
call proc_if(@i);

【2】case语句

根据不同条件执行不同的操作或返回不同的值
CASE 语句有两种形式：简单形式和搜索形式。

【2.1】简单形式

【2.1.1】语法格式

select
case case_value
	when value1 then result1
	when value2 then result2
	...
	else result3
end ;

【2.1.2】使用场景

对固定值进行匹配：当你想要根据某个列或表达式的值与一组固定的值进行匹配时，简单形式的 CASE 语句非常有用。这可以用于将一列的值映射到另一列或进行分类。
简单的条件逻辑：当你的条件逻辑相对简单，并且每个条件都是基于固定值时，简单形式的 CASE 语句可以使得代码更加清晰和简洁。
静态数据转换：当你需要根据特定值将数据转换为其他值时，简单形式的 CASE 语句非常适合。例如，将分数映射为等级，或将状态码转换为对应的描述。
用于查询结果中的计算字段：当你需要在查询结果中添加一个计算字段，该字段的值基于其他字段的值时，简单形式的 CASE 语句可以派上用场。这样可以避免在应用程序中处理这些逻辑。

【2.1.3】示例

select 
	case 1
	when 1 then 'A'
	when 2 then 'B'
	else 'more'
end as result;

【2.2】搜索形式

【2.2.1】语法格式

select 
	case
		when condition1 then result1
		when condition2 then result2
		...
		else result3
	end,
from 表名;

【2.2.2】示例

select
name,age,
case 
	when age < 20 then '小年轻'
	when age >20 and age < 30 then '成熟人士'
	else '已过而立'
	end as ageDesc
from t1 ;

【2.2.3】将case语句保存为存储过程

-- 将上述操作保存至存储过程，以方便下次调用
delimiter //
create procedure ageDesc()
begin
	select name,age, 
	case 
	when age < 20 then '小年轻'
	when age >20 and age < 30 then '成熟人士'
	else '已过而立'
	end as ageDesc from t1;
end //
delimiter ;

insert into t1(name,age) values('004',5),('005',75),('006',24);

【3】while语句

while 条件 do
	代码体
end while;
# 只要条件为真，代码体就不断执行

【3.1】用法示例

delimiter //
create procedure printNum() -- 创建存储过程
begin
	set @i = 1;
	while @i < 5 do
	select @i as number;
	set @i = @i +1;
end while;
end //
delimiter ;

# while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
	-- declare 用于声明局部变量
    DECLARE num INT ;
    -- 局部变量num就不需要用【@】
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE ;
END //
delimiter ;

【4】loop语句

LOOP语句用于无限循环执行一组语句，直到遇到LEAVE语句或满足特定条件时才退出循环。

指定标签名:loop
	无限执行的sql语句;
	if 条件 then
	leave 指定标签名; -- 当出现leave语句时，终止循环
	end if; -- 退出if语句
end loop; -- 退出loop语句

# leave好像必须要指定标签名，试了好几次其他情况都无法成功，只有只当标签名正常运行了

【4.1】用法示例

delimiter //
create procedure proc_loop()
begin
	declare i int default 1;
	-- 声明局部变量
	myloop:loop -- 为loop加上标签
		select i as number;
		set i = i+1;
		-- 为i值设置递增
		if i = 10 then
		-- 当i=10时，退出循环
		leave myloop; -- 指定leave的loop
		end if;
	end loop;
end//
delimiter ;

【5】repeat语句

repeat
	循环的sql语句;
until 条件
end repeat //

【5.1】用法示例

create procedure proc_repeat()
begin
	declare i int default 1;
	-- 需要注意，不能在循环内部声明变量
	repeat
	select i as number;
	set i = i +1;
	until i > 5
	end repeat;
end //
-- 打印了1~5

-- 哪怕条件一开始条件就是否，也会执行一条

【总结】常用的流程控制语句的特点

MySQL 中常用的流程控制语句的特点：

IF 语句：
- IF 语句用于根据条件执行不同的操作。
- 可以使用 IF、ELSEIF 和 ELSE 来定义多个条件分支。
- 可以嵌套使用 IF 语句来处理更复杂的条件逻辑。
CASE 语句：
- CASE 语句用于根据条件执行不同的操作或返回不同的值。
- 有两种形式：简单形式和搜索形式。
- 可以嵌套使用 CASE 语句来处理更复杂的条件逻辑。
WHILE 语句：
- WHILE 语句用于执行循环，直到指定的条件为假。
- 在每次迭代中，会评估条件表达式，如果为真则执行循环体，然后继续下一次迭代。
- 需要小心避免无限循环，应确保在循环体中修改条件以使其最终为假。
LOOP 语句：
- LOOP 语句用于无限循环，直到显式的退出循环。
- 通常需要在循环体内部使用 LEAVE 语句来退出循环。
- 使用 LOOP 语句时需要格外小心，以避免无限循环。
REPEAT 语句：
- REPEAT 语句是一个循环结构，类似于 DO...WHILE 循环，至少会执行一次循环体。
- 在每次迭代之后，会评估条件表达式，如果为真则退出循环。
- 与 WHILE 语句相比，REPEAT 语句保证循环体至少会执行一次，即使条件一开始就为假。

posted @ 2024-02-22 20:43 Lea4ning 阅读(11) 评论(0) 编辑收藏举报

刷新页面返回顶部

Lea4ning

MySQL进阶语法【视图 触发器 事务 存储过程 流程控制】

MySQL进阶语法

【一】视图

【1】概念

【2】优缺点

【2.1】优点

【2.2】缺点

【3】用法

【3.0】数据准备

【3.1】创建视图

【3.2】使用视图查询

【3.3】更新视图[删除再新建]

【3.4】删除视图

【二】触发器

【1】概念

【2】优缺点

优点：

缺点：

【3】用法

【3.1】创建触发器

【3.2】查看触发器

【3.3】删除触发器

【3.4】修改触发器[删除再新建]

【4】案例：自动生成错误日志

【三】事务

【1】概念

【2】事务的ACID特性

【3】事务的使用场景

【3.1】事务使用场景的具体例子

【4】用法

【4.1】事务处理的两种方法

【4.2】开启事务

【4.3】提交事务/ 回滚事务

【4.4】保存点

【4.5】转账案例解释

【4.5.1】SQL语句

【4.5.2】使用pymysql实现转账案例

【四】存储过程

【1】概念

【2】优缺点

优点：

缺点：

【3】用法

【3.1】创建并调用存储过程

【3.2】删除存储过程

【3.3】修改存储过程

【3.4】查看存储过程

【3.5】使用有参存储过程

【3.5.1】指定参数的类型

【3.5.2】示例

【4】存储过程的三种开发模型

【5】存储过程中的业务逻辑模式

【5.1】主要特点

【6】在Python中使用存储过程

【7】存储过程与事务使用举例(了解)

【补】在数据库中设置变量

【补】快速导出数据

【五】流程控制

【1】if条件语句

【1.1】单分支[if-then-else-end if]

【1.2】多分支[if-then-elseif-then-end if]

【1.3】示例

【2】case语句

【2.1】简单形式

【2.1.1】语法格式

【2.1.2】使用场景

【2.1.3】示例

【2.2】搜索形式

【2.2.1】语法格式

【2.2.2】示例

【2.2.3】将case语句保存为存储过程

【3】while语句

【3.1】用法示例

【4】loop语句

【4.1】用法示例

【5】repeat语句

【5.1】用法示例

【总结】常用的流程控制语句的特点

公告

MySQL进阶语法【视图触发器事务存储过程流程控制】