视图，触发器，事务，存储过程，函数，流程控制，索引，事务隔离，锁机制，数据库的三大范式

Ⅰ MySQL进阶知识之视图

【一】视图介绍

【1】什么是视图

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

在计算机科学中，视图（View）是一种虚拟表，其内容是一个或多个基本表的查询结果。
- 视图基于数据库中的数据，通过定义查询语句来构建，并在需要时动态地计算数据。
- 与基本表不同，视图不存储实际的数据，而是根据查询语句在使用时进行实时计算。
视图可以用于简化复杂查询、隐藏敏感数据、实现数据安全性和完整性约束等。
- 通过定义视图，用户可以从一个或多个基本表中选择所需的列和行，以满足特定的查询需求。
- 用户可以对视图执行与表相同的查询操作，如SELECT、INSERT、UPDATE 和 DELETE。
视图可以是虚拟的，也可以是物化的。
- 虚拟视图是基于查询结果的定义，每次查询时都会计算最新的结果。
- 物化视图是基于查询结果的缓存，用于提高查询性能。
- 虚拟视图适用于经常变动的数据，而物化视图适用于查询频繁但数据变动较少的情况。
总之，视图是一种查询结果的抽象表示，它提供了一种灵活和安全的方式来访问和操作数据库中的数据。
- 通过使用视图，用户可以根据自己的需求获取所需的数据，而无需直接访问底层的基本表。

【2】为什么要用视图

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

【3】视图的优点

简化复杂查询：
- 视图可以对基本表进行复杂的操作，包括连接多个表、过滤条件、聚合函数等。
- 通过定义视图，可以将复杂的查询逻辑封装到一个简单的视图中，使用户能够以更简洁明了的方式进行数据检索。
数据安全性：
- 视图可以限制用户对数据的访问权限，通常用于隐藏敏感数据或只提供部分数据给特定的用户。
- 通过定义视图并设置相应的权限，可以保护数据的安全性，防止未经授权的用户访问敏感信息。
数据完整性：
- 视图可以用于实现数据完整性约束，即对数据的有效性进行验证。
- 通过定义视图并添加计算列、过滤条件等约束，可以确保所返回的数据满足一定的条件，提高数据的准确性和一致性。
逻辑数据独立性：
- 视图使得应用程序与数据之间解耦，即应用程序不需要了解底层表结构的细节。
- 这样，当底层数据库发生变化时（如表结构修改），只需调整底层视图的定义而无需修改应用程序，从而提高系统的可维护性和扩展性。
性能优化：
- 物化视图是一种缓存机制，可以将视图的查询结果存储在磁盘上，以提高查询性能。
- 当基本表的数据频繁变动时，物化视图可以减少查询的计算开销和响应时间，提升系统的性能。

【4】总结

使用视图可以简化查询操作、保护数据安全性、实现数据完整性、提高系统的可维护性和性能优化。
视图提供了一种灵活且安全的数据访问方式，使用户能够根据自身需要方便地获取和操作数据。

【二】如何使用视图

【1】语法结构

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

【2】创建视图

首先，您需要使用CREATE VIEW语句创建一个视图。
- 在创建视图时，您需要指定视图的名称以及要查询的源表和过滤条件。
例如，下面是一个创建视图的示例语句:

CREATE VIEW my_view AS SELECT column1, column2 FROM my_table WHERE condition;

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

【3】使用视图进行查询

一旦视图创建成功，您可以像查询普通表一样使用视图进行数据检索操作。
- 例如，下面是一个使用视图查询数据的示例语句：

SELECT * FROM my_view;

上述语句将返回视图my_view中的所有数据行。

【4】更新视图

如果需要对视图进行更新操作，您可以使用UPDATE语句。
- 注意，不是所有的视图都可以进行更新，只有满足一定条件的视图才支持更新操作。
- 以下是一个更新视图的示例语句：

UPDATE my_view SET column1 = value1 WHERE condition;

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

【5】删除视图

如果您不再需要某个视图，可以使用DROP VIEW语句将其删除。
示例如下：

DROP VIEW my_view;

上述语句将删除名为my_view的视图。

【三】总结

创建视图咋硬盘上只会有表结构，没有表数据
- 表数据还是来自之前的表
视图一般只用来查询
- 不建议对视图内的数据进行更改，有可能会对原始数据产生影响
视图的使用频率并不高
- 当创建了较多视图后，会造成数据的难以维护

Ⅱ MySQL进阶知识之触发器

【一】触发器介绍

【1】什么是触发器

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

触发器是数据库管理系统中的一个概念，它是一种在数据库中定义的特殊对象，用于在满足特定条件时触发自动化的操作或逻辑。
- 触发器主要用于监视数据库表的增删改操作，并在满足指定条件时触发相关的响应行为。
触发器通常与数据库表关联，当数据库表发生特定的数据变化时，触发器会自动执行相关的操作
- 比如插入、更新、删除或查询数据等。
触发器可以作为一种数据库的约束，用于保证数据的完整性和一致性。

【2】触发器的特点

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

【3】为何使用触发器

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

使用触发器可以实现很多功能
- 比如数据验证、数据补全、数据同步、日志记录等。
但需要注意，触发器的使用也需要谨慎，过多或不当的触发器可能会对数据库性能产生负面影响，因此在设计和使用触发器时应考虑到业务需求和性能方面的平衡。

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

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

【三】语法结构

针对触发器的名字，建议简明知意

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

【四】自定义触发器

【1】创建触发器

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

【2】定义触发器逻辑

在创建触发器时，您需要定义触发器在触发时所执行的逻辑。
- 这可以是任何数据库支持的操作
比如插入数据、更新数据、删除数据、查询数据等。
- 您可以使用SQL语句或调用存储过程、函数来实现触发器的逻辑。

【3】定触发条件

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

【4】绑定触发器

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

【5】测试触发器

在绑定触发器后，您可以进行一些测试操作，验证触发器的逻辑是否按照预期执行。
- 可以插入、更新或删除数据，观察触发器是否正确地处理这些操作。

【五】触发器案例

【1】在插入之前触发示例 before

create trigger tri_before_insert_t1 
before 
insert 
on t1 
for each row
begin
	sql语句
end

【2】在插入之后触发示例 after

create trigger tri_before_insert_t1 
after 
insert 
on t1 
for each row
begin
	sql语句
end

【3】创建触发器示例（在插入数据前触发）

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;

【4】定义触发器逻辑示例（在插入数据前触发）

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    -- 您可以在这里执行各种操作，比如插入另一张表、更新其他表、计算值等。
    -- 下面是一个示例，将新插入的数据中的某一列设置为当前时间。
    SET NEW.column_name = NOW();
END;

【5】绑定触发器示例

-- 将名为 "trigger_name" 的触发器绑定到名为 "table_name" 的表上
ALTER TABLE table_name
ADD CONSTRAINT trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;

【6】查看当前库下所有的触发器信息

show triggers\G;

【7】删除当前库下指定的触发器信息

drop trigger 触发器名称;

【六】使用案例

【1】创建表

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
);

代码示例

mysql> 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代表执行失败
    -> );
Query OK, 0 rows affected (0.46 sec)

mysql> CREATE TABLE errlog (
    ->   id INT PRIMARY KEY auto_increment,
    ->   err_cmd CHAR (64),
    ->   err_time datetime
    -> );
Query OK, 0 rows affected (0.29 sec)

【2】需求

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

# 将mysql默认的结束符由;换成$$
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 ;

示例代码

mysql> 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 $$
Query OK, 0 rows affected (0.27 sec)
mysql> delimiter ;  # 记得创建好触发器之后将mysql默认的结束符由$$换回来;

【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');

代码示例

mysql> 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');
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

【4】查看数据

select * from cmd;

代码示例

mysql> select * from cmd;
+----+-------+------+-----------------+---------------------+---------+
| id | USER  | priv | cmd             | sub_time            | success |
+----+-------+------+-----------------+---------------------+---------+
|  1 | kevin | 0755 | ls -l /etc      | 2024-06-03 17:10:40 | yes     |
|  2 | kevin | 0755 | cat /etc/passwd | 2024-06-03 17:10:40 | no      |
|  3 | kevin | 0755 | useradd xxx     | 2024-06-03 17:10:40 | no      |
|  4 | kevin | 0755 | ps aux          | 2024-06-03 17:10:40 | yes     |
+----+-------+------+-----------------+---------------------+---------+
4 rows in set (0.00 sec)

查看错误日志

select * from errlog;

代码示例

# cmd表插入数据的success如果值为no 则去errlog表中插入一条记录
mysql> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd         | err_time            |
+----+-----------------+---------------------+
|  1 | cat /etc/passwd | 2024-06-03 17:10:40 |
|  2 | useradd xxx     | 2024-06-03 17:10:40 |
+----+-----------------+---------------------+
2 rows in set (0.00 sec)

【七】语句结束符

在写触发器的语法时，会遇到特殊情况，需要修改默认的语句结束符
临时修改的原因是因为触发器存储过程等技术点代码中也需要使用分号
如果不修改则无法书写出完成的代码

【1】创建触发器

创建语句前修改默认语句结束符

# 将mysql默认的结束符由;换成$$
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 ;

【2】删除触发器

删除触发器 
drop TRIGGER 触发器名字;

代码示例

mysql> drop TRIGGER tri_after_insert_cmd;
Query OK, 0 rows affected (0.10 sec)

【八】参考模板

【1】在表中插入新记录时，自动生成唯一标识符

CREATE TRIGGER generate_uuid_trigger BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    SET NEW.uuid = UUID();
END;

【2】在表中更新记录时，更新最后修改时间

CREATE TRIGGER update_last_modified_trigger BEFORE UPDATE ON table_name
FOR EACH ROW
BEGIN
    SET NEW.last_modified = NOW();
END;

【3】在表中删除记录时，将记录添加到历史记录表

CREATE TRIGGER archive_deleted_record_trigger AFTER DELETE ON table_name
FOR EACH ROW
BEGIN
    INSERT INTO history_table (id, deleted_at)
    VALUES (OLD.id, NOW());
END;

【4】在表中插入新记录时，检查是否满足某个条件，若不满足则取消插入操作

CREATE TRIGGER check_condition_trigger BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    IF NEW.column_name < 10 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Value must be greater than 10';
    END IF;
END;

上述模板中的"table_name"和"column_name"应替换为您实际使用的表名和列名。

【5】日志模板

# 创建语句前修改默认语句结束符
delimiter $$
# 主要是为了区分错误执行语句和全局结束语句
create trigger tri_after_insert_cmd after insert on cmd 
for each row
begin
	if NEW.success = "no" then
		insert into errlog(err_cmd,err_time)
	values(NEW.cmd,NEW.sub_time);
		end if;
end $$
# 使用完以后要修改回原来的默认语句
delimiter ;

Ⅲ MySQL进阶知识之事务

【一】什么是事务

开启一个事务可以包含多条语句，这些语句要么同时成功，要么都不成功
事务是指一系列相关操作的集合，这些操作被视为一个不可分割的工作单元。
事务的目标是确保在多个操作中的每一个都要么全部成功执行，要么全部失败回滚。
- 即事务的原子性

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

【1】原子性（Atomicity）

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

【2】一致性（Consistency）

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

【3】隔离性（Isolation）

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

【4】持久性（Durability）

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

【三】事务的作用

在操作多条数据的时候，可能会出现某几条操作不成功的情况

【1】数据一致性

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

【2】并发控制

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

【3】故障恢复

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

【4】高效运行

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

【5】数据完整性和安全性

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

【6】用户提交订单操作示例

检查库存：
- 系统需要检查所需商品的库存是否足够。
- 如果库存不足，系统会提示用户库存不足，无法完成订单。
扣减库存：
- 如果库存充足，系统会将所购商品对应的库存数量减少。
生成订单：
- 系统会生成一个新的订单，包括订单号、商品信息、购买数量、价格等相关信息。
计算总价：
- 根据订单中的商品信息和购买数量，系统会计算出订单的总价格。
更新用户账户：
- 根据用户选择的支付方式，在扣除相应金额后，系统会更新用户账户余额或积分。
生成支付信息：
- 系统会生成相应的支付信息，以便用户完成支付。
通知物流部门：
- 系统会通知物流部门准备配送相关商品。
发送订单确认邮件/短信：
- 系统会向用户发送订单确认的邮件或短信，包括订单详细信息、配送信息等。
监控商品配送：
- 系统会跟踪订单的配送情况，并向用户提供订单状态更新。
完成订单：
- 当用户收到商品并确认满意后，订单状态会被更新为“已完成”。

【四】如何使用事务

【1】事务相关的关键字

# 开启事务
start transaction;

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

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

事务（Transaction）是数据库管理系统（DBMS）中的一种机制，用于保证数据库操作的一致性和可靠性。
当多个数据库操作需要作为一个整体进行提交或回滚时，可以使用事务来实现

【2】使用事务的基本步骤

（1）开始事务

使用数据库提供的特定语句或命令来开始一个事务。

# 通常使用以下语句开始一个新的事务。
BEGIN、START TRANSACTION
# 或
BEGIN TRANSACTION

（2）执行事务操作

在事务中执行需要的数据库操作，包括插入、更新、删除等操作。
这些操作可以是单个SQL语句，也可以是多个SQL语句的组合。

（3）提交事务或回滚事务

提交事务：
- 当所有的操作都执行成功且符合业务逻辑要求时，使用COMMIT语句来提交事务。
- 提交后，系统会把数据库中的修改永久保存下来。
回滚事务：
- 如果在事务执行过程中发生错误或违反了业务规则，需要撤销当前事务所做的所有更改

# 使用以下语句来回滚事务。
ROLLBACK

（4）结束事务

不论是提交还是回滚事务，都需要通过特定的语句来结束事务。

# 在大部分关系型数据库中，可以使用以下语句来结束事务。
END     -- 结束事务
# 或
COMMIT	 -- 提交事务，将修改永久保存

（5）使用事务示例（使用SQL语言）

START TRANSACTION; -- 开始事务

-- 执行事务操作
INSERT INTO 表名 (列1, 列2, 列3) VALUES (值1, 值2, 值3);
UPDATE 表名 SET 列1 = 值1 WHERE 条件;
DELETE FROM 表名 WHERE 条件;

COMMIT; -- 提交事务，将修改永久保存
OR
ROLLBACK; -- 回滚事务，撤销所有修改

END; -- 结束事务

值得注意的是，事务不仅仅适用于关系型数据库，很多NoSQL数据库也支持事务操作。

（6）使用事务示例和没有使用事务示例对比

使用事务示例

开启事务，插入数据

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

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

示例代码

mysql> insert into user(name,password) values('happy',666);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+---------+----------+
| id | name    | password |
+----+---------+----------+
|  1 | silence | 741      |
|  2 | mark    | 123      |
|  3 | happy   | 666      |
+----+---------+----------+
3 rows in set (0.00 sec)

发现数据不对，事务回滚

# 数据存在

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

# 数据消失

示例代码

mysql> rollback;
Query OK, 0 rows affected (0.12 sec)

mysql> select * from user;
+----+---------+----------+
| id | name    | password |
+----+---------+----------+
|  1 | silence | 741      |
|  2 | mark    | 123      |
+----+---------+----------+
2 rows in set (0.00 sec)

确认数据正常，直接提交事务

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

示例代码

mysql> insert into user(name,password) values('happy',666);
Query OK, 1 row affected (0.05 sec)

mysql> select * from user;
+----+---------+----------+
| id | name    | password |
+----+---------+----------+
|  1 | silence | 741      |
|  2 | mark    | 123      |
|  4 | happy   | 666      |
+----+---------+----------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+---------+----------+
| id | name    | password |
+----+---------+----------+
|  1 | silence | 741      |
|  2 | mark    | 123      |
|  4 | happy   | 666      |
+----+---------+----------+
3 rows in set (0.00 sec)

没有使用事务示例

直接插入数据

# 直接插入数据
mysql> insert into user(username,password) values('white',999);
Query OK, 1 row affected (0.04 sec)

# 发现数据存在

示例代码

mysql> insert into user(name,password) values('white',999);
Query OK, 1 row affected (0.15 sec)

mysql> select * from user;
+----+---------+----------+
| id | name    | password |
+----+---------+----------+
|  1 | silence | 741      |
|  2 | mark    | 123      |
|  4 | happy   | 666      |
|  5 | white   | 999      |
+----+---------+----------+
4 rows in set (0.00 sec)

进行回滚

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

# 数据依然存在

示例代码

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+---------+----------+
| id | name    | password |
+----+---------+----------+
|  1 | silence | 741      |
|  2 | mark    | 123      |
|  4 | happy   | 666      |
|  5 | white   | 999      |
+----+---------+----------+
4 rows in set (0.00 sec)

【五】模拟转账过程中的事务(Python)

【1】示例

在模拟转账过程中使用事务机制可以确保转账操作的一致性和可靠性。
- 事务的原子性（Atomicity）特性可以确保转账过程中的数据库操作要么全部成功提交，
- 要么全部回滚。
以下是一个简单的代码演示，使用Python和MySQL数据库来模拟转账过程中的事务机制：

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"

在上述代码中
- 首先建立数据库连接，并使用start_transaction()方法开始一个新的事务。
- 接下来，通过游标执行数据库操作，包括查询发送者的余额、检查余额是否足够、更新发送者和接收者的余额。
- 如果所有操作成功执行，就调用commit()方法提交事务，并关闭游标和连接。
- 如果在任意一个操作中出现异常，就调用rollback()方法回滚事务，并关闭游标和连接。
拓展
- 在查询发送者的余额时，可以使用FOR UPDATE语句锁定了该行数据，以确保在事务结束前其他事务不能修改这条数据。
- 这样可以避免并发情况下的数据一致性问题。

【2】模拟转账功能

（1）创建表

create table user(
	id int primary key auto_increment,
    name varchar(16),
    balance int
);

（2）插入数据

insert into user(name,balance) values
("silence",1000),
("mark",1000),
("happy",1000);

（3）事务流程

# 开启事务
start transaction;

# 多条SQL语句
update user set balance=900 where name="silence";
update user set balance=1010 where name="mark";
update user set balance=1090 where name="happy";

# 事务回滚(取消交易)
rollback

# 二次确认(确认交易)
commit;

【六】总结

当让多条SQL语句保持一致性的时候(要么同时成功，要么同事失败)，可以考虑使用事务

Ⅳ MySQL进阶知识之存储过程

【一】什么是存储过程

- 存储过程就类似于Python中的自定义函数
- 内部包含了一系列可以执行的SQL语句，存储过程存储在MySQL服务端中，可以通过调用存储过程触发内部的SQL语句
  - 存储过程是在关系型数据库中存储的一组预定义的SQL语句集合，可以接收参数并返回结果。
    - 它们被封装在数据库服务器中，并由应用程序通过调用存储过程来执行特定的数据库操作。

【二】存储过程的特点

预编译：
- 存储过程在首次创建时会被编译和优化，之后每次执行时都不需要再进行编译，这样可以提高数据库的执行效率。
数据库端执行：
- 与应用程序中直接执行SQL语句相比，存储过程在数据库服务器端执行，减少了网络传输开销，提高了数据访问性能。
代码重用：
- 存储过程可以被多个应用程序共享和重用，避免了重复编写相同的SQL语句，提高了开发效率。
安全性：
- 通过存储过程，可以将对数据库的访问权限限制在一定范围内，从而提高数据的安全性。
事务支持：
- 存储过程可以包含事务处理逻辑，保证数据库操作的一致性和完整性。
简化复杂操作：
- 存储过程可以执行复杂的数据操作和计算，简化了应用程序的开发和维护过程。

【三】如何使用存储过程

【1】定义存储器

create procedure 存储过程的名字(形参1,形参2...)
begin
	sql 代码
end

【2】调用

call 存储过程的名字();

【3】查看存储过程具体信息

show create procedure pro1;

【4】查看所有存储过程

show procedure status;

【5】删除存储过程

drop procedure pro1;

【四】存储过程的开发模式

【1】存储过程的三种开发模式

（1）第一种(提前编好存储过程)

应用程序
- 程序员写代码开发
MySQL
- 提前编好存储过程，供应用程序调用
优点
- 开发效率提升、执行效率提升
缺点
- 考虑到人为因素、跨部门沟通等问题
- 后续的存储过程的扩展性差

（2）第二种(自己动手写据库操作)

应用程序
- 程序员写代码开发之前
- 涉及到数据库操作需要自己动手写
优点
- 扩展性高
缺点
- 开发效率低
- 编写SQL语句繁琐，并且后续还需要考虑优化问题

（3）第三种(ORM框架)

应用程序
- 只写程序代码
- 不写SQL语句
- 基于别人写好的操作MySQL的Python的框架直接调用即可(ORM框架)
优点
- 开发效率比上面的两种高
缺点
- 语句的扩展性差
- 可能会出现效率低下的问题

【2】存储过程的三种开发模式参考版

（1）命令式开发模式（Imperative Development Model）

在这种模式下，存储过程以类似编程语言的方式编写，包括条件语句、循环结构和异常处理等。
这种模式下的存储过程逻辑较为灵活，可以使用各种流程控制语句，但也容易导致存储过程复杂难懂且维护困难。

（2）声明式开发模式（Declarative Development Model）

在这种模式下，存储过程的逻辑将更多地关注数据操作语句，如查询、插入、更新和删除等。
存储过程的设计主要通过声明数据操作的目标和条件来实现，而不是依赖于具体的流程控制语句。
这种模式下的存储过程简洁明了，更易于理解和维护。

（3）触发器开发模式（Trigger Development Model）

触发器是一种特殊的存储过程，它与数据库表相关联，并在预定义的事件发生时自动触发执行。
触发器可以用于实现数据一致性、数据验证、日志记录等功能。
触发器的开发模式与其他存储过程略有不同，因为触发器的执行时机和上下文是由数据库事件触发的，而不是由外部调用。

【五】开启存储过程的参考步骤

【1】创建存储过程

使用数据库管理工具（如MySQL Workbench、SQL Server Management Studio等），编写存储过程的SQL语句，并在数据库中创建该存储过程。
存储过程的创建可以包括参数的定义和逻辑的实现。

【2】参数定义

如果存储过程需要接收参数，可以在存储过程的创建语句中定义参数类型、名称和属性。
参数可以分为输入参数（用于传递数据给存储过程）和输出参数（用于存储过程返回结果给调用者）。

【3】存储过程逻辑

在存储过程中编写SQL语句或其他处理逻辑，用于完成特定的数据库操作。
这些逻辑可以包括查询、插入、更新、删除等操作，也可以包括条件判断、循环、异常处理等控制结构。

【4】调用存储过程

在应用程序中通过SQL语句调用存储过程。
调用存储过程时，提供必要的参数，并获取可能的输出结果。
调用存储过程可以使用特定的SQL语法，也可以通过数据库连接的API来实现。

【5】处理存储过程返回的结果

根据存储过程的设计，处理从存储过程返回的结果。
这可能包括读取查询结果、解析输出参数值等。
处理结果的方式与具体的应用程序和数据库访问框架有关。

【六】具体演示

【1】在MySQL中

创建存储过程

# 把原本的结束符替换到
delimiter $$

# 创建存储过程
create procedure p1(
    # in表示这个参数必须只能是传入不能被返回出去
	in m int,
    in n int,

    # out表示这个参数可以被返回出去
    # 还有一个inout表示即可以传入也可以被返回出去
    out res int
)

begin
    select name from emp where dep_id > m and dep_id <n;
     # 将res变量修改，用来标识当前的存储过程代码确实执行了
    set res = 666;
end$$


delimiter ;

使用存储过程

# 定义存储过程中的变量
set @res=10;  

# 查看写好的存储过程
select @res; 

# 调用存储过程
call p1(1,5,@res)  

# 查看存储过程信息
select @res;

示例代码

mysql> create table mp(
    -> id int primary key auto_increment,
    -> name varchar(50),
    -> dep_id int
    -> );
Query OK, 0 rows affected (0.31 sec)

mysql> delimiter $$
mysql> create procedure p1(
    ->     # in表示这个参数必须只能是传入不能被返回出去
    -> in m int,
    ->     in n int,
    ->
    ->     # out表示这个参数可以被返回出去
    ->     # 还有一个inout表示即可以传入也可以被返回出去
    ->     out res int
    -> )
    ->
    -> begin
    ->     select name from emp where dep_id > m and dep_id <n;
    ->      # 将res变量修改，用来标识当前的存储过程代码确实执行了
    ->     set res = 666;
    -> end$$
Query OK, 0 rows affected (0.20 sec)

mysql> delimiter ;
mysql> set @res=100;
Query OK, 0 rows affected (0.10 sec)

mysql> select @res;
+------+
| @res |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

mysql> call p1(50,200,@res);
Empty set (0.14 sec)

Query OK, 0 rows affected (0.14 sec)

mysql> select @res;
+------+
| @res |
+------+
|  666 |
+------+
1 row in set (0.00 sec)

【2】在Python中

在Python中，我们通常使用数据库来存储和管理数据。存储过程是一组预定义的SQL语句集合，它们以特定的名称存储在数据库中，并可以在需要时被调用执行。在Python代码中演示存储过程的示例，我们将使用MySQL数据库。
首先，确保你已安装mysql-connector-python库，可以使用以下命令进行安装：

pip install mysql-connector-python

使用以下示例代码来演示在Python中调用MySQL存储过程：

import mysql.connector

# 连接到数据库
connection = mysql.connector.connect(
    host='<YOUR_HOST>',
    user='<YOUR_USERNAME>',
    password='<YOUR_PASSWORD>',
    database='<YOUR_DATABASE>'
)

# 调用存储过程
def call_stored_procedure():
    try:
        cursor = connection.cursor()
        cursor.callproc('<YOUR_STORED_PROCEDURE_NAME>')
        for result in cursor.stored_results():
            print(result.fetchall())
        cursor.close()

    except (mysql.connector.Error) as e:
        print(f"Error calling stored procedure: {e}")

# 调用存储过程函数
call_stored_procedure()

# 关闭数据库连接
connection.close()

在上述示例代码中，你需要替换以下参数：
- <YOUR_HOST>: 数据库主机名
- <YOUR_USERNAME>: 数据库用户名
- <YOUR_PASSWORD>: 数据库密码
- <YOUR_DATABASE>: 数据库名称
- <YOUR_STORED_PROCEDURE_NAME>: 存储过程的名称

在call_stored_procedure函数中，我们使用cursor.callproc命令来调用存储过程。然后，通过cursor.stored_results方法获取存储过程的结果，并使用fetchall方法打印结果。

【3】代码演示参考

存储过程（Stored Procedure）是预先编译好并存储在数据库中的一组SQL语句。
- 它可以接受参数、执行复杂的逻辑和业务操作，并可以返回结果集或输出参数。
下面是一个简单的存储过程代码演示及详解：

CREATE PROCEDURE GetEmployeeCountByDepartment
    @DepartmentName NVARCHAR(50),
    @EmployeeCount INT OUTPUT
AS
BEGIN
    SELECT @EmployeeCount = COUNT(*) 
    FROM Employees
    WHERE Department = @DepartmentName
END

这个存储过程名为"GetEmployeeCountByDepartment"，有两个参数：
- @DepartmentName和@EmployeeCount。
- @DepartmentName是输入参数，表示要查询的部门名称；
- @EmployeeCount是输出参数，用于存储查询结果。
在存储过程体内，我们使用了SELECT语句来查询Employees表中符合条件的记录数量，并将结果赋值给@EmployeeCount。
- 具体来说，COUNT(*)用于统计符合条件的记录数量，WHERE子句过滤部门名称等于输入参数@DepartmentName的记录。
使用存储过程时，可以通过以下示例代码来调用它并获取结果：

DECLARE @Result INT
EXEC GetEmployeeCountByDepartment 'IT', @Result OUTPUT
SELECT @Result AS 'EmployeeCount'

在这段示例代码中，首先声明了一个叫做@Result的整型变量。
- 然后使用EXEC关键字调用存储过程，并将部门名称参数设置为'IT'，同时指定输出参数@Result。
- 最后使用SELECT语句输出@Result的值，以获取查询结果。

【七】存储过程的优点

【1】代码复用

存储过程只需要编写一次，可以在多个地方被调用，提高了代码的复用性。

【2】数据库性能优化

存储过程在数据库中被编译、优化和缓存，可以减少网络传输开销，提高查询性能。

【3】安全性增强

存储过程可以限制直接对表的访问权限，通过存储过程来进行数据操作，可以为数据库提供更好的安全性。

Ⅴ MySQL进阶知识之函数

【一】什么是函数

跟存储过程是有区别的，存储过程是自定义函数，函数就类似于内置函数
注意与存储过程的区别，mysql内置的函数只能在sql语句中使用！

【二】字符串函数

【1】介绍

CONCAT(str1, str2, ...): 将多个字符串连接成一个字符串。
SUBSTRING(str, start, length): 返回字符串的子串。
UPPER(str): 将字符串转换为大写。
LOWER(str): 将字符串转换为小写。
LENGTH(str): 返回字符串的长度。
Trim、LTrim、RTrim: 移除指定字符
Left、Right: 获取左右起始指定个数字符
Soundex: 返回读音相似值(对英文效果)

【2】数据准备

创建表

CREATE TABLE Customers (
    Name VARCHAR(100),
    Username VARCHAR(100)
);

插入数据

INSERT INTO Customers (Name, Username) VALUES 
    ('John Doe', 'JDoe'),
    ('Jane Smith', 'JSmith'),
    ('Jim Brown', 'JBrown'),
    ('Joseph Lee', 'JLee');

【3】CONCAT

CONCAT()函数可以将两个或更多的字符串连接在一起。

SELECT CONCAT('Hello', ' ', 'World') AS Greeting;
-- 输出：Hello World

示例代码

mysql> CREATE TABLE Customers (    Name VARCHAR(100),    Username VARCHAR(100));
Query OK, 0 rows affected (0.35 sec)

mysql> INSERT INTO Customers (Name, Username) VALUES     ('John Doe', 'JDoe'),    ('Jane Smith', 'JSmith'),    ('Jim Brown', 'JBrown'),    ('Joseph Lee', 'JLee');
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT CONCAT('Hello', ' ', 'World') AS Greeting;
+-------------+
| Greeting    |
+-------------+
| Hello World |
+-------------+
1 row in set (0.00 sec)

【4】SUBSTRING

SUBSTRING()函数可以从字符串中提取部分字符。

SELECT SUBSTRING('Hello World', 1, 5) AS Substring;
-- 输出：Hello

示例代码

mysql> SELECT SUBSTRING('Hello World', 1, 5) AS Substring;
+-----------+
| Substring |
+-----------+
| Hello     |
+-----------+
1 row in set (0.03 sec)

【5】UPPER/LOWER

UPPER()函数将字符串转换为大写
LOWER()函数将字符串转换为小写。

SELECT UPPER('hello world') AS Uppercase;
-- 输出：HELLO WORLD

SELECT LOWER('HELLO WORLD') AS Lowercase;
-- 输出：hello world

示例代码

mysql> SELECT UPPER('hello world') AS Uppercase;
+-------------+
| Uppercase   |
+-------------+
| HELLO WORLD |
+-------------+
1 row in set (0.04 sec)

mysql> SELECT LOWER('HELLO WORLD') AS Lowercase;
+-------------+
| Lowercase   |
+-------------+
| hello world |
+-------------+
1 row in set (0.02 sec)

【6】LENGTH

LENGTH()函数返回字符串的长度。

SELECT LENGTH('Hello World') AS Length;
-- 输出：11

示例代码

mysql> SELECT LENGTH('Hello World') AS Length;
+--------+
| Length |
+--------+
|     11 |
+--------+
1 row in set (0.11 sec)

【7】Trim/LTrim/RTrim

TRIM()函数可以删除字符串开头和结尾处的所有指定字符。
LTRIM()和RTRIM()分别只删除开头和结尾的指定字符。

SELECT TRIM('   hello world   ') AS TrimmedString;
-- 输出：hello world

SELECT LTRIM('   hello world   ') AS LeftTrimmedString;
-- 输出：hello world

SELECT RTRIM('   hello world   ') AS RightTrimmedString;
-- 输出：   hello world

示例代码

mysql> SELECT TRIM('   hello world   ') AS TrimmedString;
+---------------+
| TrimmedString |
+---------------+
| hello world   |
+---------------+
1 row in set (0.02 sec)

mysql> SELECT LTRIM('   hello world   ') AS LeftTrimmedString;
+-------------------+
| LeftTrimmedString |
+-------------------+
| hello world       |
+-------------------+
1 row in set (0.03 sec)

mysql> SELECT RTRIM('   hello world   ') AS RightTrimmedString;
+--------------------+
| RightTrimmedString |
+--------------------+
|    hello world     |
+--------------------+
1 row in set (0.00 sec)

【8】Left/Right

LEFT()函数返回从左侧开始指定数量的字符
RIGHT()函数返回从右侧开始指定数量的字符

SELECT LEFT('Hello World', 5) AS 'Left';
-- 输出：Hello

SELECT RIGHT('Hello World', 5) AS 'Right';
-- 输出：orld

示例代码

mysql> SELECT LEFT('Hello World', 5) AS `Left`;
+-------+
| Left  |
+-------+
| Hello |
+-------+
1 row in set (0.05 sec)

mysql> SELECT RIGHT('Hello World', 5) AS 'Right';
+-------+
| Right |
+-------+
| World |
+-------+
1 row in set (0.00 sec)

【9】Soundex

SOUNDEX()函数返回读音相似值(对英文效果)，可以用来查找同音异形词。

SELECT SOUNDEX('John Doe') AS Soundex;
-- 输出：J530

SELECT * FROM Customers WHERE SOUNDEX(Username) = SOUNDEX('J.Lie');

eg:客户表中有一个顾客登记的用户名为J.Lee
但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
示例代码

mysql> SELECT SOUNDEX('John Doe') AS Soundex;
+---------+
| Soundex |
+---------+
| J530    |
+---------+
1 row in set (0.12 sec)

mysql> SELECT * FROM Customers WHERE SOUNDEX(Username) = SOUNDEX('J.Lie');
+------------+----------+
| Name       | Username |
+------------+----------+
| Joseph Lee | JLee     |
+------------+----------+
1 row in set (0.05 sec)

【三】数值函数

【1】介绍

ROUND(num, decimals): 对数值进行四舍五入。
FLOOR(num): 返回不大于给定数值的最大整数。
CEILING(num): 返回不小于给定数值的最小整数。
ABS(num): 返回给定数值的绝对值。

【2】示例

SELECT ROUND(3.14159, 2) AS RoundedNum; -- 3.14
SELECT FLOOR(3.99999) AS FloorNum; -- 3
SELECT CEILING(3.00001) AS CeilingNum; -- 4
SELECT ABS(-123.45) AS AbsNum; -- 123.45

【四】日期和时间函数

【1】介绍

NOW(): 返回当前日期和时间。
CURDATE(): 返回当前日期。
CURTIME(): 返回当前时间。
DATE_FORMAT(date, format): 格式化日期。

【2】示例

SELECT NOW() AS CurrentDateTime; -- 当前日期和时间
SELECT CURDATE() AS CurrentDate; -- 当前日期
SELECT CURTIME() AS CurrentTime; -- 当前时间
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS FormattedDateTime; -- 格式化后的日期和时间

示例代码

mysql> SELECT NOW() AS CurrentDateTime; -- 当前日期和时间
+---------------------+
| CurrentDateTime     |
+---------------------+
| 2024-06-03 20:30:14 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CURDATE() AS CurrentDate;-- 当前日期
+-------------+
| CurrentDate |
+-------------+
| 2024-06-03  |
+-------------+
1 row in set (0.11 sec)

mysql> SELECT CURTIME() AS CurrentTime;-- 当前时间
+-------------+
| CurrentTime |
+-------------+
| 20:30:31    |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS FormattedDateTime;-- 格式化后的日期和时间
+---------------------+
| FormattedDateTime   |
+---------------------+
| 2024-06-03 20:30:38 |
+---------------------+
1 row in set (0.10 sec)

Ⅵ MySQL进阶知识之流程控制

【一】各种语言中的流程控制语句

【1】Python

if 条件:
  子代码
elif 条件:
  子代码
else:
  子代码

【2】JavaScript

if(条件){
  子代码
}else if(条件){
  子代码
}else{
  子代码
}

【3】MySQL

if 语句

if 条件 then
      子代码
elseif 条件 then
      子代码
else
      子代码
end if;

while 语句

DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
  SELECT num ;
  SET num = num + 1 ;
END WHILE ;

【二】什么是流程控制

CASE：根据表达式的值来决定返回哪个结果。它主要用于处理非确定性的情况，例如用户选择不同的选项或者数据库中存在不同的数据。
IF 和 ELSE：根据条件判断是否执行某个操作。它主要用于简单的逻辑判断。
WHILE 和 UNTIL：当某条件满足时重复执行一段代码。它可以用于循环执行某个操作直到满足某个条件为止。
FOR：重复执行一段代码直到满足结束条件。它主要用于遍历数组或集合。

【三】IF语句

【1】介绍

IF语句用于在满足条件时执行一组语句，否则执行另一组语句。它的基本语法如下：

IF condition THEN
    statements;
ELSE
    statements;
END IF;

其中，condition是一个条件表达式，如果为真，则执行第一组语句；否则执行第二组语句。

【2】示例

SELECT 
    IFNULL(Bonus, 0) AS Bonus
FROM 
    Employees;

检查员工的奖金是否存在，如果不存在则返回0。

【四】CASE语句

【1】介绍

CASE语句用于基于多个条件执行不同的操作。它分为两种形式：
- 简单CASE和搜索CASE。

【2】简单CASE语句的基本语法

CASE expression
    WHEN value1 THEN
        statements;
    WHEN value2 THEN
        statements;
    ...
    ELSE
        statements;
END CASE;

其中，expression是待比较的表达式，当它等于某个值时，执行相应的语句。

【3】搜索CASE语句的基本语法

CASE
    WHEN condition1 THEN
        statements;
    WHEN condition2 THEN
        statements;
    ...
    ELSE
        statements;
END CASE;

在搜索CASE语句中，每个WHEN子句后面跟着一个条件表达式，当某个条件为真时，执行相应的语句。

【4】示例

SELECT 
    CASE 
        WHEN Salary > 5000 THEN 'High'
        ELSE 'Low'
    END AS SalaryGroup,
    COUNT(*) AS EmployeeCount
FROM 
    Employees
GROUP BY 
    SalaryGroup;

根据员工的薪水将其分为“高薪”和“低薪”两类，并统计每类员工的数量。

【五】WHILE语句

【1】介绍

WHILE语句允许你在满足条件的情况下反复执行一组语句。它的基本语法如下：

WHILE condition DO
    statements;
END WHILE;

只要condition为真，statements将被不断执行。

【2】示例

SET i = 1;
WHILE i <= 5 DO
    SELECT CONCAT('The number is: ', i) AS Number;
    SET i = i + 1;
END WHILE;

在这个例子中，我们反复打印出数字1到5。

【六】LOOP语句

【1】介绍

LOOP语句用于无限循环执行一组语句，直到遇到LEAVE语句或满足特定条件时才退出循环。它的基本语法如下：

LOOP
    statements;
    IF condition THEN
        LEAVE;
    END IF;
END LOOP;

你可以在循环中使用LEAVE语句来手动退出循环。

【2】示例

SET i = 1;
LOOP
    SELECT CONCAT('The number is: ', i) AS Number;
    SET i = i + 1;
    IF i > 10 THEN
        LEAVE;
    END IF;
END LOOP;

【七】FOR语句

【1】介绍

FOR语句是一种特殊的循环语句，它可以让你在一组范围内迭代并执行一系列语句。它的基本语法如下：

FOR var IN start, increment, end DO
    statements;
END FOR;

这个语句会从start开始，每次递增increment的值，一直循环到end，然后退出循环。

【2】示例

SET i = 1;
FOR j IN 1, 2, 6 DO
    SELECT CONCAT('The number is: ', i) AS Number;
    SET i = i * j;
END FOR;

在这个例子中，我们计算1到6的所有数的乘积，并在每次迭代后打印当前的乘积

【八】模板参考

【1】if 判断

DELIMITER //
CREATE PROCEDURE proc_if()
BEGIN
    DECLARE i INT DEFAULT 0;
    IF i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;
END //
DELIMITER ;

【2】while 循环

DELIMITER //
CREATE PROCEDURE proc_while()
BEGIN
    DECLARE num INT DEFAULT 0;
    WHILE num < 10 DO
        SELECT num;
        SET num = num + 1;
    END WHILE;
END //
DELIMITER ;

Ⅶ MySQL进阶知识之索引

【一】索引的概念

索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构，这也是索引最基本的功能。
索引对于良好的性能非常关键。
- 数据量越大时，索引对性能的影响也越重要，好的索引可以将查询性能提高几个数量级。
- 在数据量较小且负载较低时，不恰当的索引对性能的影响可能还不明显，但是在数据量逐渐增大时，糟糕的索引会使MySQL的性能急剧的下降。
索引优化是查询性能优化最有效的手段。
如果想要在一本书中找到某个特定主题，一般会先看书的目录，找到对应的页码，然后直接翻到对应的页码即可查看。
- 在MySQL中，存储引擎用类似的方法使用索引
  - 首先在索引中找到对应的值
  - 然后根据匹配的索引记录找到对应的数据行。
简单的说，数据库索引类似于书前面的目录，能加快数据库的查询速度。

【二】MySQL中索引的类型

创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。
索引在MySQL中也叫键，是存储引擎用于快速查找记录的一种数据结构
- 主键约束（PRIMARY KEY）
- 唯一约束（UNIQUE）
- index key
- 外键约束（FOREIGN KEY）
foreign key
- 不是用来加速查询的
primary key/unique key
- 不仅可以加速查询速度，还具有对应的约束条件
index key
- 只有加速查询速度的功能

【三】索引的本质

通过不蹲的缩小想要的数据范围筛选出最终的结果
- 同时将随机事件(一页一页的翻)变成顺序时间(先找目录再找数据)
也就是说我们有了索引机制，我们可以总是用一种固定的方式查询数据

【四】索引的缺点

当表中有大量数据存在的前提下，创建索引的速度回非常慢
在索引创建完毕后，对表的查询性能会大幅度的上升，但是写的性能也会大幅度下降不要随意地创建索引

【五】索引的使用场景

要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：
- 数据量较大，且经常对这些列进行条件查询。
- 该数据库表的插入操作，及对这些列的修改操作频率较低。
- 索引会占用额外的磁盘空间。
满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。
反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

【六】索引操作

【1】查看索引

（1）语法

show index from 表名;

（2）示例

mysql> desc emp;
+--------+-----------------------+------+-----+---------+----------------+
| Field  | Type                  | Null | Key | Default | Extra          |
+--------+-----------------------+------+-----+---------+----------------+
| id     | int(11)               | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)           | YES  |     | NULL    |                |
| sex    | enum('male','female') | NO   |     | male    |                |
| age    | int(11)               | YES  |     | NULL    |                |
| dep_id | int(11)               | YES  |     | NULL    |                |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> show index from emp;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp   |          0 | PRIMARY  |            1 | id          | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

【2】创建索引

对于非主键、非唯一约束、非外键的字段，可以创建普通索引

（1）语法

create index  索引名 on 表名(字段名);

# 索引名的命名规则一般是：index_表名_列名

（2）示例

mysql>  create index index_name on emp (name);
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from emp;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp   |          0 | PRIMARY    |            1 | id          | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| emp   |          1 | index_name |            1 | name        | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)

【3】删除索引

（1）语法

drop index 索引名 on 表名

（2）示例

mysql> drop index index_name on emp;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from emp;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp   |          0 | PRIMARY  |            1 | id          | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

【七】索引的数据结构

对于索引的操作是非常简单的，但是关键在于我们要去学习支持索引的数据结构。
在数据结构那门课程里，我们可以使用二叉搜索树来加快查询，但是元素个数一多，对于输的高度就就会很高，而树高就代表着比较次数多，在实际项目中代表中I/O的访问次数多，因为数据库中的数据是存在硬盘里的。
或者我们还学过使用哈希表，他查询的时间复杂度为O(1)但是哈希表不支持范围查找，不支持模糊匹配。
实际上索引的背后使用的是B+树。
在了解B+树之前，先要了解B树，如果有考过408同学应该是对这个数据结构是非常熟悉的

【1】 B树和B+树

（1）B树

B树本质上就是一个N叉的搜索树（查找树）。

（2）B+树

B+树是在B树的基础上进行改进

（3）B+的特点

一个结点，可以存储N个key,N个Key划分出N个区间（B树是N+1个）；
每个结点的Key值，都会在子节点中存在（并且是子节点的最大值）；
B+树的叶子结点首位相连，类似链表；
整个树的所有数据都包含在叶子结点中。所以非叶子结点的Key最终都会出现在叶子结点中。
B+树还有一个显著特点，他的每一个叶子结点都关联这一个记录，这个记录就是我们实际数据库里每一个表里的每一行记录。

（4）B+树的优势

当前一个结点保存更多的key,最终树的高度是相对更矮的（B树也有这个优点），查询的时候可以减少IO的访问次数。
所有的查询最终都会落在叶子结点上（查询任何一个数据，经过的IO访问次数，是一样的。）稳定是很重要的，稳定可以让程序员对程序的运行效率有更准确的评估。
B+树的所有叶子结点都用链表进行了链接(并且是一个双向链表)，这样就支持更直接的范围查询了。同时代码也更好写了。
由于数据都在叶子结点上，非叶子结点只存了key，所以我们就可以将叶子结点的一部分进行缓存（B树非叶子结点是存记录的），这样可以进一步减少IO次数。

（5）示例

mysql> desc student;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| sn         | int(11)     | YES  | UNI | NULL    |                |
| name       | varchar(20) | YES  |     | unkown  |                |
| qq_mail    | varchar(20) | YES  |     | NULL    |                |
| classes_id | int(11)     | YES  | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

表的数据还是按照id为主键，构建出B+树，通过叶子结点组织所有的数据行。其次针对name如果我又创建了一个索引，那么此时在底层是又构建了一个B+树，这个B+树的叶子结点不再存储这一行的完整数据，而是存主键id。
此时，如果根据name来查询，查到叶子结点得到的只是主键Id,还需要再次通过主键id主键的B+树里再查一次。
上述过程称之为回表，是mysql自己完成的，用户感知不到。

【2】B+树

（1）什么是B+树

B+树是一种平衡查找树的数据结构，被广泛应用于数据库系统和文件系统中的索引结构。
它相比于其他树状结构（如二叉搜索树）具有更高的查询性能和更好的存储利用率。

（2）B+树的特点

平衡性：B+树是一棵平衡树，即每个节点的左子树和右子树的高度差不超过1。这样可以保持树的高度相对较小，使得查找操作的复杂度保持在O(log n)级别。
多路搜索：B+树的每个内部节点可以拥有多个子节点，这使得B+树能够在每个节点上存储更多的关键字，提高了存储的效率。
顺序访问性：B+树的所有叶子节点按照关键字的大小顺序形成一个有序链表，可以支持范围查询和排序等操作。同时，由于内部节点只包含关键字而不包含具体数据，可以在内存中存放更多的节点，从而提高了缓存的命中率。
存储利用率高：B+树的内部节点不存储数据，只存储关键字，因此相比于其他树结构（如B树），B+树可以存储更多的关键字。这使得B+树在磁盘或内存中的存储利用率更高。

（3）B+树的应用

B+树的应用主要包括数据库系统和文件系统中的索引结构。
在数据库系统中，B+树常被用作数据库表的索引结构，能够高效地支持查找、插入、删除等操作。
在文件系统中，B+树常被用来维护文件的目录结构，可以快速定位和访问文件。

（4）小结

总之，B+树是一种高效的数据结构，具有平衡性、多路搜索、顺序访问性和存储利用率高等特点，适用于需要高效查询和排序的场景。
只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据
查询次数由树的层级决定，层级越低次数越少

【八】聚集索引(主键索引)

【1】什么是聚集索引

聚集索引（Clustered Index）是关系型数据库中的一种索引类型，它决定了表中数据的物理存储顺序。
在一个表中，只能有一个聚集索引。
聚集索引对表进行了重新组织，使得数据按照聚集索引的键值顺序存储在磁盘上。
由于聚集索引决定了数据的物理存储顺序，因此通过聚集索引可以快速地找到特定范围内的数据
MySQL的聚簇索引是基于B+树的数据结构实现的，它会把数据存储在索引的叶子节点上，叶子节点之间按顺序链接，使得按主键进行搜索时速度最快。
如果没有主键，如果按主键搜索，速度是最快的。

【2】聚集索引的特点

数据的逻辑顺序和物理顺序是一致的，通过聚集索引可以直接访问特定行，因此聚集索引的查询速度很快。
聚集索引的键值必须是唯一的，不允许重复值存在。
当表中的数据发生插入、删除或更新操作时，聚集索引需要进行相应的调整以保持数据的有序性，这可能会对性能产生一定影响。
如果表中没有定义聚集索引，那么表的数据存储顺序将按照物理地址来存储。
表不建立主键，也会有个隐藏字段是主键，是主键索引
主键索引对于按照主键进行查询的性能非常高。

【3】语法

数据列不允许重复，不允许为NULL，一个表只能有一个主键。

ALTER TABLE table_name ADD PRIMARY KEY (column);

【4】小结

聚集索引适用于经常需要按照某个特定的列或列组进行查询的情况。
- 例如，在一个订单表中，如果根据订单号频繁地进行查询，那么可以将订单号作为聚集索引，这样可以提高订单查询的效率。
需要注意的是，聚集索引的选择需要根据具体的业务需求和数据访问模式进行权衡。
- 在一些特定情况下，聚集索引可能并不适合或者不符合最佳实践，此时可以考虑使用非聚集索引等其他索引类型。

【九】辅助索引(普通索引)

【1】什么是辅助索引

辅助索引（Non-clustered Index）是关系型数据库中的一种索引类型，与聚集索引相对应。
辅助索引的键值并不决定数据在磁盘上的物理存储顺序，而是创建一个额外的数据结构来帮助加快查询速度。一个表可以有多个辅助索引。

【2】辅助索引的特点

辅助索引的建立并不直接影响数据的物理存储顺序，它是基于聚集索引（如果存在）或表本身的物理存储结构进行构建的。
辅助索引的键值可以重复，允许在同一个键值上有多行数据。
辅助索引存储着键值和指向实际数据行的指针，通过辅助索引可以快速定位到具体的数据行。
辅助索引的维护对数据的插入、删除、更新操作的性能有一定影响，因为每次操作都需要更新辅助索引。

【3】语法

MySQL中的基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和NULL值。
一个表允许多个列创建普通索引。

ALTER TABLE table_name ADD INDEX index_name (column);

【4】小结

辅助索引适用于类似于搜索或排序等需要频繁进行的查询操作。
- 当查询涉及到的列不是聚集索引的键值时，辅助索引可以提供更高效的查询性能。
- 例如，在一个学生信息表中，如果经常按照学生的姓名进行查询，那么可以在姓名列上创建一个辅助索引，这样可以加速姓名查询的速度。
需要注意的是，在设计数据库时，应根据实际情况综合考虑聚集索引和辅助索引的选择。
- 聚集索引适用于经常以特定顺序扫描整个表或者频繁按照某个列进行范围查找的场景，而辅助索引则适用于单列或多列的等值或范围查找场景。
- 理想情况下，辅助索引应当能够满足大部分查询需求，从而避免全表扫描。

【十】唯一索引（unique）

【1】什么是唯一索引

唯一索引是指该索引的所有值都是唯一的，不允许出现重复值。

【2】语法

MySQL中可以通过以下语法创建唯一索引

ALTER TABLE table_name ADD UNIQUE KEY index_name (column);

【3】注意事项

与普通索引不同的是，如果尝试向包含唯一索引的列中插入重复的值，则会引发错误。
唯一索引可以用于确保数据的一致性和完整性，并且可以帮助提高查询性能。

【十一】组合索引(联合索引)

【1】什么是组合索引

组合索引是一种由两个或更多列组成的索引。
当查询涉及多个列时，组合索引可以大大提高查询性能。

【2】语法

MySQL中可以通过以下语法创建组合索引

ALTER TABLE table_name ADD INDEX index_name (column1, column2, ...);

需要注意的是，组合索引中的列顺序很重要。
在执行查询时，MySQL会首先使用最左的列进行匹配，然后逐步向右扩展。
因此，应该将最常用的查询条件放在组合索引的前面。

【3】缺点

虽然组合索引可以提高查询性能，但是也存在一些缺点。
例如，如果添加了不需要的列，或者删除了不需要的列，可能会导致组合索引变得无效。
此外，如果创建了太多的组合索引，也可能会增加索引维护的成本。
因此，在创建组合索引时需要谨慎考虑。

【十二】全文索引

【1】什么是全文索引

全文索引是一种特殊的索引，它可以用来存储和检索文本数据。
全文索引可以包含单词、短语和其他类型的文本内容，并支持模糊匹配和近似匹配。

【2】语法

MySQL中可以通过以下语法创建全文索引

CREATE FULLTEXT INDEX index_name ON table_name (column);

【3】注意事项

需要注意的是，只有MyISAM和InnoDB存储引擎支持全文索引。
此外，创建全文索引可能会增加索引维护的成本，并且可能会降低其他类型的查询性能。
因此，在创建全文索引时需要权衡其利弊。

【十三】前缀索引

【1】什么是前缀索引

前缀索引是一种特殊的索引，它只存储索引列的一部分，而不是完整的值。
前缀索引通常用于处理非常大的列，例如IP地址或邮政编码。
在这种情况下，全列索引可能会消耗大量的存储空间，并且可能会导致查询性能下降。
前缀索引可以大大减少索引大小，并且可以更快地执行范围查询。

【2】语法

MySQL中可以通过以下语法创建前缀索引

ALTER TABLE table_name ADD INDEX index_name (column(length));

其中，length参数表示要保留的字符数。

【3】示例

例如，如果我们有一个邮政编码列，并且我们只需要后两位数字进行索引，我们可以创建以下前缀索引：

ALTER TABLE table_name ADD INDEX idx_postcode(postcode(2));

在这个例子中，索引只会包含邮政编码的最后两位数字。
当我们执行范围查询时，MySQL可以直接使用前缀索引来缩小搜索范围，从而提高查询性能。

【十四】覆盖索引

【1】什么是覆盖索引

覆盖索引（Covering Index）是指在关系型数据库中，创建一个辅助索引包含了查询所需要的所有列，从而避免了对主表进行额外的数据检索操作。
只在辅助索引的叶子节点中就已经找到了所有我们想要的数据

select name from user where name='dream';

【2】覆盖索引的特点

覆盖索引包含了查询所需要的所有列，无需再通过辅助索引定位到实际的数据行。这样，数据库可以直接从索引中获取到查询所需的数据，而不需要再访问主表，提高了查询性能。
覆盖索引减少了磁盘I/O操作的次数。相比于使用辅助索引定位到数据行后再读取数据，覆盖索引可以直接从索引中读取所需数据，减少了磁盘读取的次数，从而加快了查询速度。
覆盖索引在一些特定的查询场景下非常有效，特别是当查询只需要返回索引包含的列时。例如，如果需要查询一个学生表中的学生姓名和年龄，而这两个列在一个名为"student_idx"的辅助索引上都有覆盖，那么查询时就可以直接使用该覆盖索引，而无需再去读取主表中的其他列，从而提高查询效率。

【3】小结

根据具体的查询需求，我们可以通过创建适当的覆盖索引来提高查询性能。
- 需要考虑的因素包括查询的列、查询的条件、表的大小以及对数据插入和更新操作的影响。
- 创建过多的覆盖索引可能增加了存储空间的占用，并对数据的插入、删除和更新操作性能产生负面影响。
因此，在创建覆盖索引时需要权衡索引的选择，避免过度索引化的情况。

【十五】非覆盖索引

【1】什么是非覆盖索引

非覆盖索引是指在数据库中的索引结构中，存储了对应的键值（例如：主键、唯一键、普通索引）以及相应的行的定位信息（如物理存储位置或行标识），但没有包含查询所需的其他列数据。
当执行一个查询时，使用非覆盖索引需要通过索引定位到对应的行，并进一步访问主表来获取所需的列数据。
与覆盖索引相比，非覆盖索引需要进行额外的查询操作来检索主表中的其他列数据，因此在某些情况下可能会导致性能下降。
虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找

select age from user where name='silence';

【2】非覆盖索引的适用场景和优势

提供高效的筛选能力：非覆盖索引可以根据所建索引的键值快速定位到满足查询条件的行，这可以减少需要扫描的数据量，提供高效的筛选能力。
减少磁盘I/O操作：虽然非覆盖索引需要额外的访问主表来获取数据，但是相对于全表扫描或者需要访问大量数据页的情况，非覆盖索引仍然可以减少磁盘的I/O操作次数，从而提升查询性能。
降低内存消耗：非覆盖索引通常比较小，占用的内存空间相对较少。这对于有限的内存资源来说，可以更好地利用内存空间。

【3】注意事项

列选择性：索引的列选择性是指该列上不同值的数量与总行数之间的比率。当列具有较高的选择性时，非覆盖索引的效果通常会更好。因为高选择性的列能够更快地筛选出满足查询条件的行。
查询性能评估：在设计索引时，需要仔细评估查询的频率和性能需求。如果某个查询经常执行，而且对性能要求很高，那么建立合适的非覆盖索引可以提升查询效率。

【4】小结

总结而言，非覆盖索引是一种常见的索引类型，在特定场景下可以提供高效的筛选能力和降低磁盘I/O操作的优势。
但在选择索引类型时，需要综合考虑查询需求、列选择性以及数据表大小等因素，以选择最合适的索引优化方案。

Ⅷ MySQL进阶知识之事务隔离

【一】数据库读现象的本质

是数据库在高并发场景下
多个同时执行的事务带来的影响。

【二】数据库三大读现象

在数据库中，不同的事务隔离级别可能会导致脏读（Dirty Read）、不可重复读（Non-repeatable Read）和幻读（Phantom Read）等问题的出现。

【1】脏读

（1）概述

事务1和事务2并发执行
事务1改了数据
事务2读取了以后
但事务1进行了回滚
导致事务2读取的数据有误。

（2）解释

脏读是指当一个事务读取了其他事务尚未提交的数据时发生的现象。
换句话说，脏读表示读取到的数据并不一定会最终存入数据库中，因此这些数据实际上是不存在的。
脏读现象发生在读取到了不一定存在的数据的情况下。

（3）总结

脏读指的是读当前事务到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，
也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读

【2】不可重复读

（1）概述

事务1读取了数据
事务2修改了数据并且提交了
接着事务1再次读取
发现两次的数据不相同

（2）解释

不可重复读是指在一个事务内多次读取同一批数据，但在事务结束之前，这批数据可能发生了变化，导致读取结果不一致的情况。
不可重复读的产生通常是由于在事务A多次读取同一数据的过程中，事务B对数据进行了更新并提交。

（3）总结

解释：不可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据出现不一致的情况
导致的原因：事务 A 多次读取同一数据，但事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致

【3】幻读

（1）概述

本质上说是不可重复读的一种现象
事务1更改或查询了数据
在极短时间内,事务2又插入了一条新的数据
导致事务1在接下来的查询中
就会发现有⼏列数据是它先前所没有的。

（2）错误的理解

[1]解释

有时候，人们错误地将幻读理解为在两次select操作中获得了不同的数据集
- 例如第一次select得到10条记录
- 第二次select得到15条记录。
实际上，这种情况仍然属于不可重复读而非幻读。

[2]总结

幻读是事务A 执行两次 select 操作得到不同的数据集，即 select 1 得到 10 条记录，select 2 得到 15 条记录。
这其实并不是幻读，既然第一次和第二次读取的不一致，那不还是不可重复读吗，所以这是不可重复读的一种。

（3）正确的理解

[1]解释

幻读的本质在于某一次select操作得到的结果无法支撑后续的业务操作。
具体来说，例如在执行select判断某条记录是否存在时，假设该记录不存在，准备插入该记录，但在执行insert时却发现该记录已经存在，导致无法插入，这即是幻读的发生。

[2]总结

幻读，并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。
更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读

【4】解决办法

要解决脏读，不可重复读和幻读的问题
我们就要引入几个概念：MVCC机制，事务隔离机制和数据库锁机制。

【三】数据库事务隔离机制

事务具有原子性、一致性、隔离性、持久性四大特性

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

（1）原子性（Atomicity）

事务是一个不可再分割的单位，要么全部执行成功，要么全部回滚到初始状态，没有中间状态。
这意味着如果事务中的任何一步操作失败，整个事务都会被回滚，以确保数据的一致性。

（2）一致性（Consistency）

事务执行前后，数据库的状态必须保持一致。
这意味着事务在执行期间对数据的操作必须满足预定义的规则和完整性约束，以确保数据的有效性和正确性。

（3）隔离性（Isolation）

多个事务可能同时执行，事务之间应该相互隔离，互不影响。
隔离性确保每个事务的操作在逻辑上独立于其他并发事务的操作，从而避免了数据不一致的问题。

（4）持久性（Durability）

一旦事务提交，对数据库的更改应该是永久性的，即使在系统故障的情况下也不应该丢失。
持久性通过将事务记录在持久存储介质（如磁盘）上来实现。

【2】隔离性的四种级别

（1）引入

而隔离性顾名思义指的就是事务彼此之间隔离开
- 多个事务在同时处理一个数据时彼此之间互相不影响
- 如如果隔离的不够好就有可能会产生脏读、不可重复度、幻读等读现象
为此，隔离性总共分为四种级别
由低到高依次为
- Read uncommitted（未提交读）
- Read committed （提交读）
- Repeatable read（可重复读）
- Serializable（串行化）

（2）四种级别

[1]Read uncommitted（读未提交）

最低的隔离级别，在这个级别下，一个事务可以读取到另一个事务尚未提交的数据，可能导致脏读（Dirty Read）问题，即读取到未经验证的数据。

[2]Read committed（读已提交）

在这个级别下，一个事务只能读取到已经提交的数据，避免了脏读问题。
但是可能会出现不可重复读（Non-repeatable Read）问题，即同一事务中，两次读取相同的记录可能得到不同的结果，因为其他事务修改了这些记录。

[3]Repeatable read（可重复读取）

在这个级别下，事务开始读取数据后，其他事务无法修改这些数据，保证了同一个事务内两次读取相同记录的一致性。
但是可能会出现幻读（Phantom Read）问题，即同一查询在同一事务中两次执行可能返回不同的结果，因为其他事务插入或删除了符合查询条件的记录。

[4]Serializable（串行化）

最高级别的隔离级别，要求事务串行执行，事务之间完全隔离，避免了脏读、不可重复读和幻读问题。
但是这会牺牲并发性能，因为并发事务被限制为顺序执行。

【3】四大隔离级别解决了什么问题

Read uncommitted（读未提交）
- 存在脏读、不可重复读和幻读问题。
Read committed（读已提交）
- 解决了脏读问题，但仍可能出现不可重复读和幻读。
Repeatable read（可重复读取）
- 解决了脏读和不可重复读问题，但仍可能出现幻读。
Serializable（串行化）
- 解决了脏读、不可重复读和幻读问题，但在效率方面有所牺牲。

【4】MySQL的存储引擎默认的隔离级别

Repratable read (可重复读)

【5】幻读的解决办法

MySQL引入了Next-key lock的行级锁来解决，我们将会在下一节里详细叙述

【四】MVCC机制

【1】什么是MVCC机制

MVCC是MySQL InnoDB存储引擎实现的一种基于多版本的并发控制协议。
基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) 。

【2】MVCC的优势

MVCC实现了读不加锁，避免了读写冲突，提高了系统的并发性能。
MVCC解决了数据的脏读问题，保证了数据的一致性。

【3】MVCC的读操作

MVCC的并发控制的系统中，读操作可分为两类：当前读和快照读。

（1）快照读

快照读是指对数据库进行简单的select操作时，不会加锁，而是通过查询当前事务开始时的系统时间点，获取该时间点之前的数据副本。
由于每个事务都有自己的时间点，所以每个事务看到的都是自己创建时的状态，从而避免了脏读的发生。

（2）当前读

特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。
当前读是指对数据库进行插入、更新或删除操作时，需要加锁，以防止其他事务修改已经锁定的数据。只有当事务完成所有更新后，才会将其提交到数据库，并释放所有的锁。
这种机制可以确保数据的一致性和完整性，但会降低系统的并发性能。

Ⅸ MySQL进阶知识之锁机制

【一】什么是锁机制

我们可以通过一个很简单的比喻来理解事务的锁机制。
比如同一个办公室的同事们
- 都想使用打印机打印文件
  - 如果不加以控制
  - 可能出现两个人同时打印不同的内容在一个文件里
  - 就会引起内容混乱。
- 于是，我们就引入了锁的概念
  - 当有并发的多个事务同时操作同一份数据时
  - 只有“抢到”了锁的事务
  - 才能真正去操作数据
  - 使得数据的安全性得到保证。

【二】为什么要用锁机制

锁保证并发的多个事务同一时间只有一个能运行
- 会一定程度上降低程序的运行效率
- 但是能大大提升数据的安全性。

【三】数据库锁的分类

【1】按粒度分

数据库的锁按粒度分为
- 行级锁
- 表级锁
- 页级锁

（1）什么是⾏级锁

⾏级锁是Mysql中锁定粒度最细的⼀种锁
- 表示只针对当前操作的⾏进⾏加锁。
⾏级锁能⼤⼤减少数据库操作的冲突。
- 其加锁粒度最⼩，但加锁的开销也最⼤。
⾏级锁分为共享锁和排他锁。

（2）⾏级锁的特点

开销⼤，加锁慢；
会出现死锁；
锁定粒度最⼩，发⽣锁冲突的概率最低，并发度也最⾼

（3）⾏级锁解释

由于数据库的库和表都是事先建好的
- 所以我们针对数据库的操作一般都是针对记录。
- 而对记录进行的四种操作（增删改查）
- 我们可以分为两类
  - 增删改属于读操作
  - 而查询属于写操作。
写操作默认就会加锁，且加的是互斥锁
- 很容易理解，在进行写行为的时候一定是必须“排他”的。
- 读操作默认不受任何锁影响
- 但是互斥锁和共享锁都可以加。
读操作加互斥锁 for update;
读操作加共享锁 lock in share mode;

提示：关于共享锁和互斥锁，我们将在下一小节更详细地讲述

（4）行级锁锁的是索引

行级锁锁的是索引
- 命中索引以后才会锁行
- 如果没有命中索引
- 会把整张表都锁起来。
命中主键索引就锁定这条语句命中的主键索引
- 命中辅助索引就会先锁定这条辅助索引
- 再锁定相关的主键索引
- 考虑到性能，innodb默认支持行级锁
- 但是只有在命中索引的情况下才锁行，
否则锁住所有行
- 本质还是行锁
- 但是此刻相当于锁表了

（5）行级锁的三种算法

1、Record lock
2、Gap lock
3、Next-key lock
其中 Next-key lock 为MySQL默认的锁机制
- 相当于另外两种锁的功能的整合
- 并能够解决幻读问题。
提示：
- 在RR事务隔离机制下，才会锁间隙
- 而RR机制是mysql的默认事务隔离机制。
- 所以，在默认情况下，其实innodb存储引擎锁的是行以及间隙.
我们可以用一个实验来验证上述关于行锁的结论

（6）实验

事务一	事务二
start transaction;	开启事务start transaction;
	-- 加排他锁select from t1 where id=7 for update; -- 须知-- 1、上述语句命中了索引，所以加的是行锁-- 2、InnoDB对于行的查询都是采用了Next-Key Lock的算法，锁定的不是单个值，而是一个范围（GAP）表记录的索引值为1，5，7，11，其记录的GAP区间如下：（-∞,1]，(1,5]，(5,7]，(7,11]，(11,+∞）因为记录行默认就是按照主键自增的，所以是一个左开右闭的区间其中上述查询条件id=7处于区间(5,7]中，所以Next-Key lock会锁定该区间的记录，但是还没完-- 3、InnoDB存储引擎还会对辅助索引下一个键值加上gap lock*。区间(5,7]的下一个Gap是(7,11],所以(7,11]也会被锁定综上所述，最终确定5-11之间的值都会被锁定
-- 下述sql全都会阻塞在原地insert t1 values(5);insert t1 values(6);insert t1 values(7);insert t1 values(8);insert t1 values(9);insert t1 values(10); -- 下述等sql均不会阻塞insert t1 values(11); insert t1 values(1); insert t1 values(2);insert t1 values(3);insert t1 values(4);
-- 提交一下事务，不要影响下一次实验commit;	-- 提交一下事务，不要影响下一次实验commit;

【2】按级别分

数据库的锁按级别分为
- 共享锁，排他锁，共享锁
- 又被称作读锁，s锁
  - 含义是多个事务共享同一把锁
  - 其中每个事务都能访问到数据
  - 但是没有办法进行修改。
注意：
- 如果事务T对数据A加上共享锁后
- 则其他事务只能对A再加共享锁或不加锁（在其他事务里一定不能再加排他锁
- 但是在事务T自己里面是可以加的）
排他锁又被称作互斥锁，写锁，x锁
- 含义是如果有一个事务获取了一个数据的排他锁
- 那么其它的事务都无法再次获得该数据的任何锁了
- 但是排他锁支持文件读取，修改和写入。

【3】按使用方式分

数据库的锁按使用方式分为
- 悲观锁、乐观锁

（1）悲观锁（Pessimistic Locking）

顾名思义指的是对外界将要进行的数据修改操作持悲观态度
因此，在整个数据处理过程中，将数据处于锁定状态。
现在由于互联网的高并发架构，即使加上悲观锁也无法保证数据不被外界修改，因此不推荐使用。

（2）乐观锁（Optimistic Locking）

相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突
所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测
如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。
通常乐观锁的实现是在表中加一个字段（可能是时间戳或版本号）
在写入的时候会查询一下版本号
- 如果版本号没有改变，就写入数据库并同时改变版本号。
从本质上来说，乐观锁并没有加锁
- 所以效率会大大提升
- 但也有一定的缺陷，就是可能导致一部分任务的写入失败。

【四】死锁问题

我们举一个例子来形象的说明死锁这个概念。
比如你和你的邻居同时被锁在了屋子里
- 然而你有你邻居的钥匙
- 你的邻居也有你的钥匙
- 你们互相可以打开对方的房门
- 但是却都被锁在了各自的屋子里
- 这就是一个简单的死锁现象

【1】第一种情况的死锁

事务1	事务2
begin	begin
select * from t1 where id=6 for update;	delete from t1 where id=3;
update t1 set age=18 where id=3;	delete from t1 where id=6; -- 阻塞

第一种死锁情况非常好理解，也是最常见的死锁
- 每个事务执行两条SQL，分别持有了一把锁
- 然后加另一把锁，产生死锁。
大多数死锁问题
- innodb存储引擎都会发现并抛出异常
- 但是有一种死锁问题极其隐蔽。

【2】第二种情况的死锁

与上一种死锁情况不同的是
- 这种死锁现象必须是两个事务同时运行的情况下才可能发生。
前面我们提到过，聚集索引对应的是一整行数据记录。
- 当事务1根据一定的过滤条件
- 筛选出两条辅助索引时
- 根据索引的有序性
- 在锁完辅助索引后锁主键索引时
- 先锁主键1对应的记录再锁主键2。
- 如果在此同时
  - 事务2通过别的辅助索引同样访问到了这两条数据
  - 但顺序却是先锁主键2再锁主键1
  - 就会互相锁住
  - 产生死锁现象
  - 而且这种情况非常隐蔽，较难排查。

Ⅹ 数据库的三大范式

【面试题】数据库的三大范式

【一】什么是范式

范式就是我们在设置数据库的表时，一些共同需要遵守的规范
掌握这些设计时的范式，可以让我们在项目之初，设计库的表结构更加合理和优雅

【二】三大范式之间的关系

三大范式之间，是逐级递进的关系，也就是说后一个范式是在前一个范式的基础上推行。
我们举一个形象的例子来说
- 我们吃饭的时候
- 第一步是买菜
- 第二步是炒菜
- 第三步才是吃菜
这三者之间不能颠倒，后者都是建立在前者之上的，顺序不能颠倒。

【三】【1NF】第一范式

即一个字段的数据不能再被进一步分解为更小的数据单元

【1】什么是第一范式

表库设计时，主要是为了确保原子性，也就是存储的数据具有不可再分性。
注意
- 这里的原子性不等同于MySQL特点中的原子性
MySQL特性之原子性
- 指事务是操作数据库的基本单位，要么全部执行成功，要么全部失败回滚
- 这样做确保了数据库在任何情况下都能保持一致的状态，不会出现中间数据
第一范式的原子性
- 指一个字段不可再分割，其中不能包含其他更小的数据单元
- 也就是说，一个字段的数据不能再被进一步分解为更小的数据单元
为了更好的理解上述所说的原子性，我们以一个例子来说明

【2】基表

比如我们有一张 student 表
- 表结构和内容如下

+----------------------+--------+-------+
| student              | course | score |
+----------------------+--------+-------+
| 小张，男，185cm      | 语文   |    95 |
| 小张，男，185cm      | 数学   |   100 |
| 小张，男，185cm      | 英语   |    88 |
| 小郭，女，170cm      | 语文   |    99 |
| 小郭，女，170cm      | 数学   |    90 |
| 小郭，女，170cm      | 英语   |    95 |
+----------------------+--------+-------+

student	course	score
小张，男，185cm	语文	95
小张，男，185cm	数学	100
小张，男，185cm	英语	88
小郭，女，170cm	语文	99
小郭，女，170cm	数学	90
小郭，女，170cm	英语	95

在上述的学生表中，有一个字段是 student ，我们可以明显的看出，它不符合我们规定的第一范式要求
- 即一个字段的数据不能再被进一步分解为更小的数据单元
student 这一列的数据仍然可以再继续拆分成姓名、性别和身高三个字段

【3】基于第一范式优化

+--------------+-------------+----------------+--------+-------+
| student_name | student_sex | student_height | course | score |
+--------------+-------------+----------------+--------+-------+
| 小张         | 男          | 185cm          | 语文   |    95 |
| 小张         | 男          | 185cm          | 数学   |   100 |
| 小张         | 男          | 185cm          | 英语   |    88 |
| 小郭         | 女          | 170cm          | 语文   |    99 |
| 小郭         | 女          | 170cm          | 数学   |    90 |
| 小郭         | 女          | 170cm          | 英语   |    95 |
+--------------+-------------+----------------+--------+-------+

student_name	student_sex	student_height	course	score
小张	男	185cm	语文	95
小张	男	185cm	数学	100
小张	男	185cm	英语	88
小郭	女	170cm	语文	99
小郭	女	170cm	数学	90
小郭	女	170cm	英语	95

将 student 字段拆分成三个字段后，我们可以明显的发现存储结构的设计更为合理和优雅
通过上述优化后，此时这张表符合了第一范式的规范

【4】如果不去拆分列满足第一范式，会造成什么影响呢？

首先，就是我们录入的数据无法和数据库中的表形成合理和明确的一一对应关系
其次，当我们将表中的数据查询到时，我们仍然需要对我们查到的数据进行额外拆分优化
最后，在我们插入数据的时候，我们要按照数据库中的数据格式进行拼装存储
简单来书，我们使用不符合第一范式的表结构去做业务开发时，操作会比较麻烦一些，当我们进行符合第一范式的二次设计后，虽然表的字段变多了，但是数据结构变得清晰了很多

【5】第一范式(1NF)小结

第一范式，我们通常也叫 1NF
第一范式要求我们必须遵守原子性
- 即数据库表的每一列都是不可分割
  - 每列的值具有原子性，不可再分割
  - 每个字段的值都只能是单一值

【四】【2NF】第二范式

【1】什么是第二范式

首先第二范式是在满足第一范式的基础上
- 第一范式我们已经能够理解了，那么我们再来看看第二范式
第二范式要求表中的所有列，其数据依赖于主键
- 即一张表只存储同一类型的数据，不能有任何一列数据与主键没有关系

【2】第一范式基表

表结构如下

+--------------+-------------+----------------+--------+-------+
| student_name | student_sex | student_height | course | score |
+--------------+-------------+----------------+--------+-------+
| 小张         | 男          | 185cm          | 语文   |    95 |
| 小张         | 男          | 185cm          | 数学   |   100 |
| 小张         | 男          | 185cm          | 英语   |    88 |
| 小郭         | 女          | 170cm          | 语文   |    99 |
| 小郭         | 女          | 170cm          | 数学   |    90 |
| 小郭         | 女          | 170cm          | 英语   |    95 |
+--------------+-------------+----------------+--------+-------+

【3】基于第二范式优化

（1）引入

虽然上述的表结构已经满足了数据库的第一范式
但我们也能明显的看到 course 和 score 这两列数据，跟前面的几列数据实际上依赖并不大
并且这样的表结构也导致了前面几列的数据出现了大量冗余
那我们再对边结构进行优化

（2）结构优化

表一：student

+------------+--------+------+--------+--------------+--------------+
| student_id | name   | sex  | height | department   | dean         |
+------------+--------+------+--------+--------------+--------------+
|          1 | 小张   | 男   | 185cm  | 计算机系     | 竹子老大     |
|          2 | 小郭   | 女   | 170cm  | 金融系       | 熊猫老大     |
+------------+--------+------+--------+--------------+--------------+

student_id	name	sex	height	department	dean
1	小张	男	185cm	计算机系	竹子老大
2	小郭	女	170cm	金融系	熊猫老大

表二：course

+-----------+-------------+
| course_id | course_name |
+-----------+-------------+
|         1 | 语文        |
|         2 | 数学        |
|         3 | 英语        |
+-----------+-------------+

course_id	course_name
1	语文
2	数学
3	英语

表三：score

+----------+------------+-----------+-------+
| score_id | student_id | course_id | score |
+----------+------------+-----------+-------+
|        1 |          1 |         1 |    95 |
|        2 |          1 |         2 |   100 |
|        3 |          1 |         3 |    88 |
|        4 |          2 |         1 |    99 |
|        5 |          2 |         2 |    90 |
|        6 |          2 |         3 |    95 |
+----------+------------+-----------+-------+

score_id	student_id	course_id	score
1	1	1	95
2	1	2	100
3	1	3	88
4	2	1	99
5	2	2	90
6	2	3	95

（3）小结

进过上述结构优化后，原来的 student 表被我们拆分成了三张表
- 分别是 student 、 course 、 score 三张表
每一张表中的 id 字段作为自己的主键，其他字段都依赖于自己的主键
无论是哪张表都可以通过 id 字段确定其他字段的值

【4】主键可以不用id，但最好是自增的主键ID，这跟索引有关

我们再细看经过优化后的三张表
- 原来的学生表有 6 条记录，其中 4 条是冗余数据
- 现在的学生表有 2 条记录，同时这张表只存储学生信息
经过这次优化后，每一张表都有了各自的业务属性，都具有了唯一性
- 也就是每张表只描述一件事
- 不会存在一张表中会出现两个及以上业务属性
  - 例如之前的学生表存储了学生信息和课程信息

【5】第二范式(2NF)小结

第二范式，我们通常也叫 2NF
- 第二范式(2NF)是在第一范式(1NF)的基础上建立起来得
- 满足第二范式(2NF)必须先满足第一范式(1NF)
第一范式要求我们必须遵守原子性
第二范式要求表中的所有列，其数据依赖于主键
- 即一张表只存储同一类型的数据，不能有任何一列数据与主键没有关系
  - 如果表是单主键，那么主键以外的列必须完全依赖于主键，其它列需要跟主键有关系
  - 如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分

【五】【3NF】第三范式

【1】什么是第三范式

首先第三范式是在满足第二范式的基础上
- 第二范式我们已经能够理解了，那么我们再来看看第三范式
第三范式要求表中每一列数据不能与主键之外的字段有直接关系
- 表中的非主键列必须和主键直接相关而不能间接相关
- 非主键列之间不能相关依赖，不存在传递依赖

【2】第二范式基表

+------------+--------+------+--------+--------------+--------------+
| student_id | name   | sex  | height | department   | dean         |
+------------+--------+------+--------+--------------+--------------+
|          1 | 小张   | 男   | 185cm  | 计算机系     | 竹子老大     |
|          2 | 小郭   | 女   | 170cm  | 金融系       | 熊猫老大     |
+------------+--------+------+--------+--------------+--------------+

【3】基于第三范式优化

（1）引入

我们以学生表为例，学生表目前符合第一范式和第二范式。
但是我们观察后面两个字段
- department 相当于当前学生所属的院校
- dean 相当于院系的院长
一般来说，一个学生的院长是谁取决于学生所在的院系
- 因此，最后的 dean 字段与department 字段明显存在依赖关系

（2）结构优化

department

+---------------+-----------------+-----------------+
| department_id | department_name | department_dean |
+---------------+-----------------+-----------------+
|             1 | 计算机系        | 竹子老大        |
|             2 | 金融系          | 熊猫老大        |
+---------------+-----------------+-----------------+

department_id	department_name	department_dean
1	计算机系	竹子老大
2	金融系	熊猫老大

student

+------------+--------+------+--------+---------------+
| student_id | name   | sex  | height | department_id |
+------------+--------+------+--------+---------------+
|          1 | 小张   | 男   | 185cm  |             1 |
|          2 | 小郭   | 女   | 170cm  |             2 |
+------------+--------+------+--------+---------------+

student_id	name	sex	height	department_id
1	小张	男	185cm	1
2	小郭	女	170cm	2

（3）小结

经过上述优化后，我们又将学生表拆成了学生表和院系表。
- 学生表只存储一个院系对应的 ID
- 院系表存储院系相关的数据
通过以上优化，我们可以发现
- 学生表中的每个非主键字段与其他非主键字段之间都是相互独立的
- 彼此之间不存在任何依赖关系
- 所有的字段都依赖于主键

【4】如果不调整表结构会如何？

如果不调整上述结构，那么我们在操作数据表的时候就会发生如下问题
- 当一个院系院长换人后，需要修改学生信息表中的每一条数据
- 当一个院长离职后，需要删除院长的相关数据，包括学生表中的相关数据
由此，会引发很多意料之外的错误和数据异常，让整张表较难维护

【5】第三范式(3NF)小结

第三范式，我们通常也叫 3NF
- 第三范式(3NF)是在第二范式(2NF)的基础上建立起来得
- 满足第三范式(3NF)必须先满足第二范式(2NF)
第一范式要求我们必须遵守原子性
第二范式要求表中的所有列，其数据依赖于主键
- 即一张表只存储同一类型的数据，不能有任何一列数据与主键没有关系
  - 如果表是单主键，那么主键以外的列必须完全依赖于主键，其它列需要跟主键有关系
  - 如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分
第三范式要求表中每一列数据不能与主键之外的字段有直接关系
- 表中的非主键列必须和主键直接相关而不能间接相关
- 非主键列之间不能相关依赖，不存在传递依赖

【六】数据库三范式小结

到这里就已经将库表设计的三范式做了直观阐述，总结如下：
- 第一范式：确保原子性，表中每一个列数据都必须是不可再分的字段。
- 第二范式：确保唯一性，每张表都只描述一种业务属性，一张表只描述一件事。
- 第三范式：确保独立性，表中除主键外，每个字段之间不存在任何依赖，都是独立的。
经过三范式的示例后，数据库中的表数量也逐渐多了起来，似乎设计符合三范式的库表结构，反而更加麻烦了对吗？
答案并非如此，因为在没有按照范式设计时，会存在几个问题：
- 整张表数据比较冗余，同一个学生信息会出现多条。
- 表结构特别臃肿，不易于操作，要新增一个学生信息时，需添加大量数据。
- 需要更新其他业务属性的数据时，比如院系院长换人了，需要修改所有学生的记录。
但按照三范式将表结构拆开后
- 假设要新增一条学生数据，就只需要插入学生相关的信息即可
- 同时如果某个院系的院长换人了，只需要修改院系表中的院长就行，学生表中的数据无需发生任何更改。
经过三范式的设计优化后，整个库中的所有表结构，会显得更为优雅，灵活性也会更强
参考借鉴

posted on 2024-06-03 23:28 silence^ 阅读(81) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· 数据库初始，SQL语句介绍，MySQL数据库安装，SQL语句基础，操作MySQL数据库

· MySQL四种主要的存储引擎，约束条件null not null，严格模式，基本字段类型之整型,浮点型,字符串类型,日期时间类型，字段类型之枚举和集合类型

· MySQL进阶知识之视图、触发器、事务

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

· 【20.0】MySQL进阶知识之索引

zyb123

视图，触发器，事务，存储过程，函数，流程控制，索引，事务隔离，锁机制，数据库的三大范式

Ⅰ MySQL进阶知识之视图

【一】视图介绍

【1】什么是视图

【2】为什么要用视图

【3】视图的优点

【4】总结

【二】如何使用视图

【1】语法结构

【2】创建视图

【3】使用视图进行查询

【4】更新视图

【5】删除视图

【三】总结

Ⅱ MySQL进阶知识之触发器

【一】触发器介绍

【1】什么是触发器

【2】触发器的特点

【3】为何使用触发器

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

【三】语法结构

【四】自定义触发器

【1】创建触发器

【2】定义触发器逻辑

【3】定触发条件

【4】绑定触发器

【5】测试触发器

【五】触发器案例

【1】在插入之前触发示例 before

【2】在插入之后触发示例 after

【3】创建触发器示例（在插入数据前触发）

【4】定义触发器逻辑示例（在插入数据前触发）

【5】绑定触发器示例

【6】查看当前库下所有的触发器信息

【7】删除当前库下指定的触发器信息

【六】使用案例

【1】创建表

【2】需求

【3】仅仅往cmd表中插入数据

【4】查看数据

【七】语句结束符

【1】创建触发器

【2】删除触发器

【八】参考模板

【1】在表中插入新记录时，自动生成唯一标识符

【2】在表中更新记录时，更新最后修改时间

【3】在表中删除记录时，将记录添加到历史记录表

【4】在表中插入新记录时，检查是否满足某个条件，若不满足则取消插入操作

【5】日志模板

Ⅲ MySQL进阶知识之事务

【一】什么是事务

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

【1】原子性（Atomicity）

【2】一致性（Consistency）

【3】隔离性（Isolation）

【4】持久性（Durability）

【三】事务的作用

【1】数据一致性

【2】并发控制

【3】故障恢复

【4】高效运行

【5】数据完整性和安全性

【6】用户提交订单操作示例

【四】如何使用事务

【1】事务相关的关键字

【2】使用事务的基本步骤

（1）开始事务

（2）执行事务操作

（3）提交事务或回滚事务

（4）结束事务

（5）使用事务示例（使用SQL语言）

（6）使用事务示例和没有使用事务示例对比

【五】模拟转账过程中的事务(Python)

【1】示例

【2】模拟转账功能

（1）创建表

（2）插入数据

（3）事务流程

【六】总结