sql_study_04

第四部分：高阶主题——触发器、事件、事务、并发

触发器

1. CREATE TRIGGER

   1. AFTER（BEFORE） INSERT(UPDATE、DELETE、SELECT) ON payments

      在payments表中，插入后，

   2. FOR EACH ROW

      对每一行都起作用

   3. NEW.amount

      获取新插入数据的字段值，与之相对应的OLD.amount 获取刚刚删除数据的字段值

   CREATE TRIGGER payments_after_insert
       AFTER INSERT ON payments  -- 指明触发的时机
       FOR EACH ROW  -- 指明作用范围，这里指每一行
   BEGIN -- 触发体 （满足触发条件后要执行的SQL语句）
      UPDATE invoices
      SET payment_total = payment_total + NEW.amount,
          payment_date = NEW.date
      WHERE invoice_id = NEW.invoice_id;
   END; -- 触发体
   

   插入之前 invoices表

   

   执行插入语句

   INSERT INTO payments
   VALUES (DEFAULT, 5, 3, DATE(NOW()), 10, 1);
   

   执行后满足触发条件，执行触发体中的sql代码，更新invoices表数据

   

2. SHOW TRIGGER

   查询已创建的触发器，不同于函数，存储过程，视图，事件，可以从图形化界面中显示

   

3. 审计表。可以用来记录用户的操作行为

   -- 审计表
   USE sql_invoicing;
   CREATE TABLE payments_audit
   (
   	client_id 		INT 			NOT NULL,
       date 			DATE 			NOT NULL,
       amount 			DECIMAL(9, 2) 	NOT NULL,
       action_type 	VARCHAR(50) 	NOT NULL,
       action_date 	DATETIME 		NOT NULL
   );
   DROP TRIGGER IF EXISTS payments_after_insert;
   CREATE TRIGGER payments_after_insert
       AFTER INSERT ON payments  -- 指明触发的时机
       FOR EACH ROW  -- 指明作用范围，这里指每一行
   BEGIN -- 触发体
       UPDATE invoices
       SET payment_total = payment_total + NEW.amount,
           payment_date = NEW.date
       WHERE invoice_id = NEW.invoice_id;
   
       INSERT INTO payments_audit
       VALUES (5,NEW.date,NEW.amount,'INSERT',NOW());
   END;
   
   DROP TRIGGER IF EXISTS payments_after_delete;
   CREATE TRIGGER payments_after_delete
       AFTER DELETE ON payments
       FOR EACH ROW
   BEGIN
       UPDATE invoices
           SET payment_total = payment_total - OLD.amount
       WHERE invoice_id = OLD.invoice_id;
   
       INSERT INTO payments_audit
       VALUES (5,OLD.date,OLD.amount,'DELETE',NOW());
   END;
   
   INSERT INTO payments
   VALUES (DEFAULT, 5, 3, DATE(NOW()), 10, 1);
   DELETE FROM payments WHERE payment_id = 15;
   

   

事件

1. CRETE EVENT

   1. ON SCHEDULE … AT / EVERY

      事件计划执行的时间，AT ‘指定的时间’ 如果没有具体的时间，是当天的00：00：00执行

      EVERY 1 YEAR / DAY / MONTH (STARTS ‘开始时间’ ENDS ‘结束时间’ )

   2. DO BEGIN （要执行的SQL语句） END

   -- CREATE EVENT
   DROP EVENT IF EXISTS yearly_delete_stale_audit_rows;
   -- 事件如果是执行一次的，并且执行完了，就销毁了
   CREATE EVENT yearly_delete_stale_audit_rows
   ON SCHEDULE -- 定义计划执行时间
       AT '2024-08-19 14:05:00' -- 执行一次，在什么时候，如果没有设置具体的时间，默认是00：00：00执行
       -- EVERY 1 YEAR STARTS '2024-01-01' ENDS '2084-01-01' -- EVERY 每多少年月日执行一次，STARTS 和 ENDS 可写可不写
   DO BEGIN
       DELETE FROM payments_audit WHERE action_date < DATE_SUB(NOW(),INTERVAL 1 YEAR);
   END;
   

   

2. ALERT EVENT

   修改事件或者修改事件属性

   -- 修改事件体或者一些属性
   ALTER EVENT yearly_delete_stale_audit_rows
   ON SCHEDULE
       EVERY 1 DAY STARTS '2024-08-19 00:00:00' ENDS '2024-08-25 00:00:00'
   DO BEGIN
       DELETE FROM payments_audit WHERE action_date < DATE_SUB(NOW(),INTERVAL 1 YEAR );
   END;
   ALTER EVENT yearly_delete_stale_audit_rows ENABLE ;
   ALTER EVENT yearly_delete_stale_audit_rows DISABLE ;
   

3. SHOW EVENT

   SHOW EVENTS LIKE 'yearly%';
   

   

事务

怎么说呢，事务就是把一堆SQL语句围起来成一个整体，成功提交了就全部成功了，失败了就全部失败了。然后事务可以多个一起运行，可以设置隔离级别（为了处理多个事务并行出现的问题）

那些傻逼ACID就不去记了，没什么鸟用，什么四大特性，全是废话

-- 事务
USE sql_store;
START TRANSACTION ;
INSERT INTO orders(customer_id, order_date, status)
VALUES (1,'2024-08-19',1);
INSERT INTO order_items
VALUES (LAST_INSERT_ID(),1,1,1);
COMMIT ;-- ROLLBACK 手动回滚事务
-- 默认一条SQL就是一个事务，Mysql将一条sql自动放入一个事务中，然后自动提交
SHOW variables like 'autocommit%';
SET AUTOCOMMIT = OFF;

1. LAST_INSERT_ID()

   获取最后一条插入数据的ID

2. 多个事务对同一行数据进行操作时，会对这一行数据上锁（行锁）后进入的事务会等待先进入的事务执行完后再执行(正常情况下是这样的)，先执行前四句，不COMMIT，然后再去启动一个新的事务，这个事务因该会打圈圈等待的，用MysqlWorkBench是这样的，但是我用DataGrip他会给我自动提交第一个未结束的事务🙃。查了下原因可能是DG做了优化。。。我也不知道是不是这样，这里如果有知道的大佬可以解答一下

   

3. TRANSACTION ISOLATION LEVEL

   1. READ-UNCOMMITED

      这个隔离级别，事务会读取其他事务未提交的数据

      执行顺序：136–>11–>12 13 14–>137

      结果：读出的数据数据是未提交的数据20

      如果事务发生了回滚，就可能读出数据库不存在的数据，就发生了所谓的脏读

      

   2. READ-COMMITED

      这个隔离级别，就可以解决脏读问题，事务未提交的数据，别的事务无法读取

      但是如果一个事务里面有多次对同一行数据进行读取，会出现两次读取的数据不一致的问题

      执行顺序：141–>142–>143–>11–>12 13 14–>15–>144–>145

      结果：143读出2352 144读出20

      

   3. REPEATABLE-READ

      MySQL默认隔离级别，可以解决两次读出数据不一致的问题，两次读取都是一样

      **执行顺序：**148–>149–>150–>151–>11–>12 13 14 15–>152–>153

      **结果：**151–20 152–20

      可能造成的后果，如果另一个事务对数据进行了跟新，当前事务可能无法读取到更新后的数据

      

   4. SERIALIZABLE

      最高隔离级别，必须等待另一个事务结束提交后才执行SQL

      

      

4. DEAD LOCK

   两个事务相互等待对方释放锁（事务提交后释放锁）

   -- DEAD LOCK
   USE sql_store;
   START TRANSACTION ;
   UPDATE customers SET state = 'VA' WHERE customer_id = 1; -- 1 对customer_id = 1的数据加锁
   UPDATE orders SET status = 1 WHERE order_id = 1; -- 3 请求order_id = 1 的数据
   COMMIT ;
   

   START TRANSACTION ;
   UPDATE orders SET status = 1 WHERE order_id = 1;-- 2 对order_id = 1的数据加锁
   UPDATE customers SET state = 'VA' WHERE customer_id = 1; -- 4 请求customer_id = 1的数据
   COMMIT ;