526互联

事务、锁、触发器

发布时间 2023-11-15 01:20:03作者: 粥粥alg

第一章：MySQL 事务

MySQL Transaction

我们要向 teachers 表中插入一条 Kansas 的信息，其年龄为 41 岁，国籍为 UK，请补充 SQL 语句，来实现插入 Kansas 的信息。

-- 开启一个事务 -- 
BEGIN;
-- 插入 Kansas 的信息 --
-- Write your SQL Query here --
INSERT INTO teachers(name,age,country)
VALUES('Kansas',41,'UK');
COMMIT;

第二章：锁的认识

锁的认识

请编写 SQL 语句，查看你当前使用的数据库是什么事务隔离级别

SHOW VARIABLES like '%isolation%';

第三章：锁的类型

InnoDB 锁的种类之自增锁

请编写 SQL语句 查看 MySQL 数据库的自增锁模式

SHOW VARIABLES like '%innodb_autoinc_lock_mode%';

第四章：如何上锁

4.1 如何加表锁

我们要向 teachers 表中插入一条 Feng Qingyang 的信息，其邮箱为 feng.qingyang@163.com，其年龄为 37 岁，国籍为 CN，但是 teachers 表被上了读锁，请编写 SQL 语句，来实现插入 Feng Qingyang 的信息。

-- 对 teachers 表上读锁，不要删除该代码 --
LOCK TABLES teachers READ;

-- Write your SQL Query here --
-- example: SELECT * FROM XX_TABLE WHERE XXX --
UNLOCK TABLES;

INSERT INTO teachers ( name, email, age, country)
VALUES('Feng Qingyang','feng.qingyang@163.com',37,'CN');

4.2如何加行锁

请给 teachers 表中编号为 3 的一行数据上一个读锁
SELECT * FROM teachers WHERE id=3
LOCK IN SHARE MODE;

第五章：如何排查锁

如何排查锁

现在我们需要排查当前数据库的表锁情况，请编写 SQL 语句查看表锁分析S

SHOW STATUS LIKE 'table%';

第六章：乐观锁与悲观锁

乐观锁与悲观锁

数据库在并发情况下，常使用乐观锁与悲观锁。现要求使用乐观锁对表进行数据的保护。我们当前数据库采用 version 的方式来实现，现要求编写 SQL 语句，更新 teachers 表中名为 Western Venom 的教师信息，修改国籍为 CN。

UPDATE teachers 
SET version=version+1, country='CN' 
WHERE name='Western Venom' AND version=5;

第七章：认识触发器

创建触发器

我们要为 teachers 表创建一个触发器，并命名为 before_teachers_update，要求在修改一条数据之前执行，触发器执行内容为 SET new.country = 'CN'，请编写 SQL 语句实现

CREATE TRIGGER before_teachers_update 
BEFORE UPDATE ON
teachers
FOR EACH ROW
SET new.country='CN'

删除触发器

我们要将 teachers 表中的触发器 before_teachers_update 删除，请编写 SQL 语句实现

drop trigger before_teachers_update;

第八章：触发器基本操作（一）

8.1MySQL BEFORE INSERT Trigger(一)

students 表存储了所有学生的信息，包括学生 id 和学生姓名 name。
companies 表存储了所有公司的信息，包括公司 id 和公司名称 name。
recording 表存储了所有的简历投递数据，包括学生 id (student_id) 和 公司 id (company_id)。
请编写 SQL 语句（触发器），处理简历投递数据表的新增数据，当 student_id 在 student 表中不存在时，置为 0；当 company_id 在 companies 表中不存在时，置为 0。

CREATE TRIGGER before_insert_recording
BEFORE INSERT ON recording FOR EACH ROW 
BEGIN 
    IF 
    new.student_id NOT IN (SELECT id FROM students) THEN SET new.student_id=0;
    END IF;
    IF 
    new.company_id NOT IN (SELECT id FROM companies) THEN SET new.company_id=0;
    END IF;
END;

8.2MySQL BEFORE INSERT Trigger(二)

请编写 SQL 语句，为教师表 teachers 设计一个触发器，将 teachers 中新增的数据复制到结构相同的备份表 teachers_bkp 中

CREATE TRIGGER after_teachers_insert
after insert
on teachers for each row
begin
insert into teachers_bkp(id,name,email,age,country)
values(id, new.name, new.email, new.age, new.country);
end;

8.3MySQL BEFORE INSERT Trigger(三)

为名为 members 的表创建了一个 UPDATE 触发器，要求实现数据修改后，记录修改信息 memberId = [id]，message = 'Update {[old]} To {[new]}' 到 reminders 表中

  • [id] 为 members 中被修改的记录的主键 id
  • [old] 与 [new] 中参数说明：只显示有被修改到的字段，如：name 由 Tom 更新为 Jack ，[old] 显示为 [name=tom] ，[new] 显示为 [name=jack]
CREATE TRIGGER update_message  --新建一个触发器，命名为update_message
    AFTER UPDATE  --触发器在更新members表的任意行后触发
    ON `members` FOR EACH ROW 
BEGIN -- 开始定义触发器
    DECLARE old_str varchar(255); --定义变量1，用于表示[old]，命名为old_str，类型为上限255字的字符串
    DECLARE new_str varchar(255); --定义变量2，用于表示[new]，命名为new_str，类型为上限255字的字符串
    SET old_str= ''; --清空上次触发器的变量内容
    SET new_str= ''; --同上

    IF new.name<>old.name THEN --members的name列有变更
        SET old_str= CONCAT(old_str, '[name=', old.name, '] '), --在[old]中追加变更前的name, CONCAT中的"old_str,"用于让3个假设的变更内容可以并列显示
        new_str= CONCAT(new_str, '[name=', new.name, '] '); --在[new]中追加变更后的name
    END IF; -- 结束假设1
    IF new.email<>old.email THEN 
        SET old_str= CONCAT(old_str, '[email=', old.email,'] '),
        new_str= CONCAT(new_str,'[email=', new.email,'] ');
    END IF;
    IF new.birthDate<>old.birthDate THEN 
        SET old_str= CONCAT(old_str, '[birthDate=', old.birthDate,'] '),
        new_str= CONCAT(new_str,'[birthDate=', new.birthDate,'] ');
    END IF;

    IF (old.name<>new.name OR old.email<>new.name OR old.birthDate<>new.birthDate) THEN --如果任意列出现变更
    INSERT INTO `reminders`(memberID,message) --在reminders表中新建一条数据
    VALUES (new.id, --插入[id] 为 members 中被修改的记录的主键 id
        CONCAT('Update {', old_str, '} To {', new_str, '}')); -- 追加message内容为 'Update {[old]} To {[new]}'
    END IF;
END; -- 结束触发器定义

第九章：触发器基本操作（二）

创建多个触发器

LintCode 部门新进了一批科研人员，为了保护他们的人身安全，需要在 teachers 表中，将他们的信息删除。请编写触发器，实现每次删除 teachers 表中的信息时，会在 teachers_bkp 中记录其信息。部分德高望重的科研人员渴望加入中国（CN）国籍，请编写触发器，实现该功能。

我们需要给 teachers 表添加两个新的触发器，当删除一条教师数据时：

  • 触发器 bkp_teachers_delete：将删除的数据备份到结构相同的备份表 teachers_bkp 中；
  • 触发器 before_teachers_delete：将 teachers_bkp 表中最年长的教师国籍改为 'CN'；
  • 触发器 bkp_teachers_delete 需在触发器 before_teachers_delete 之前执行。
CREATE TRIGGER bkp_teachers_delete
BEFORE DELETE 
ON teachers FOR EACH ROW
BEGIN 
	INSERT INTO teachers_bkp(name,email,age,country)
	VALUES(old.name,old.email,old.age,old.country);
END;
CREATE TRIGGER before_teachers_delete
BEFORE DELETE
ON teachers FOR EACH ROW
FOLLOWS bkp_teachers_delete
BEGIN
	DECLARE maxAge INT;
	SELECT MAX(age) INTO maxAge FROM teachers_bkp;
	UPDATE teachers_bkp SET country = 'CN' WHERE age = maxAge;
END;