2020.8.19课堂笔记（触发器，MySQL视图）
MySQL触发器
自动：能减少我们的操作，主要指增删改
只要对表的数据发生改变了，一般来说触发的动作也是增删改（3种操作增删改，2种时间before after）
一个时间对应一个事件是一一对应的，当定义了一种情况就不能重复定义，例如：before insert不能同时存在两个
确保不会出现失败的时候可以使用触发器来做，
1
mysql> select * from account;
±—±---------±----------+
| id | name | cash |
±—±---------±----------+
| 1 | shop | 2000 |
| 2 | customer | 30000 |
| 6 | bank | 999990000 |
±—±---------±----------+
mysql> create trigger trigger_a_update after update on account for each row
-> update account set cash=cash+10000 where ‘customer’=name;
mysql> mysql> show triggers\G
*************************** 1. row ***************************
Trigger: trigger_a_update
Event: UPDATE
Table: account
Statement: update account set cash=cash+10000 where ‘customer’=name
Timing: AFTER
Created: NULL
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Definer: [email protected]
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
mysql> select * from information_schema.triggers\G
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: MySchool
TRIGGER_NAME: trigger_a_update
EVENT_MANIPULATION: UPDATE
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: MySchool
EVENT_OBJECT_TABLE: account
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: update account set cash=cash+10000 where ‘customer’=name
ACTION_ORIENTATION: ROW
ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
DEFINER: [email protected]
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
mysql> update account set cash=cash-10000 where name=‘bank’;
ERROR 1442 (HY000): Can’t update table ‘account’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
错误原因：这里是一个表级锁，在执行update的时候把把表给锁了，触发器又要执行一个update所以执行不了那条语句
重新建一张表：账户流水表，当对金额进行变动的时候，记录一下变动的情况
mysql> drop trigger trigger_a_update;
mysql> create table account_history(
-> hid int(11) auto_increment primary key,
-> account_name varchar(30),
-> changed_cash decimal(20,2)
-> );
mysql> create trigger trig_account_his after update on account
-> for each row
-> insert into account_history (account_name,changed_cash) values(‘customer’,‘10000’);
Query OK, 0 rows affected (0.00 sec)
mysql> update account set cash=cash+10000 where name=‘customer’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from account_history;
±----±-------------±-------------+
| hid | account_name | changed_cash |
±----±-------------±-------------+
| 1 | customer | 10000.00 |
±----±-------------±-------------+
1 row in set (0.00 sec)
Database changed
mysql> delimiter @@
mysql> select * from account_history;
-> @@
±----±-------------±-------------+
| hid | account_name | changed_cash |
±----±-------------±-------------+
| 1 | customer | 10000.00 |
±----±-------------±-------------+
1 row in set (0.00 sec)
不以分号作为结束，一般推荐@@
mysql> delimiter @@
mysql> create trigger trigger_a_insert after insert on account
-> for each row
-> begin
-> insert into account_history(account_name,changed_cash) values(‘cast’,‘10000’);
-> [email protected]@
Query OK, 0 rows affected (0.00 sec)
mysql> insert into account(name,cash) values(‘cust’,10000);
-> @@
Query OK, 1 row affected (0.00 sec)
mysql> delimiter ;
mysql> select * from account_history;
±----±-------------±-------------+
| hid | account_name | changed_cash |
±----±-------------±-------------+
| 1 | customer | 10000.00 |
| 2 | cast | 10000.00 |
±----±-------------±-------------+
2 rows in set (0.00 sec)
mysql> select * from account;
±—±---------±----------+
| id | name | cash |
±—±---------±----------+
| 1 | shop | 2000 |
| 2 | customer | 40000 |
| 6 | bank | 999990000 |
| 7 | cust | 10000 |
±—±---------±----------+
4 rows in set (0.00 sec)
mysql> show create trigger trigger_a_insert\G
*************************** 1. row ***************************
Trigger: trigger_a_insert
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=root@localhost trigger trigger_a_insert after insert on account
for each row
begin
insert into account_history(account_name,changed_cash) values(‘cast’,‘10000’);
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
mysql> drop trigger trig_account_his;
Query OK, 0 rows affected (0.00 sec)
mysql> drop trigger trigger_a_insert;
Query OK, 0 rows affected (0.00 sec)
mysql> create trigger trigger_a_insert after insert on account
-> for each row
-> insert into account_history(account_name,changed_cash)
-> values(new.name,new.cash);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into account(name,cash) values(‘abc’,‘100’);
Query OK, 1 row affected (0.00 sec)
mysql> select * from account;
±—±---------±----------+
| id | name | cash |
±—±---------±----------+
| 1 | shop | 2000 |
| 2 | customer | 40000 |
| 6 | bank | 999990000 |
| 7 | cust | 10000 |
| 8 | abc | 100 |
±—±---------±----------+
5 rows in set (0.00 sec)
mysql> select * from account_history;
±----±-------------±-------------+
| hid | account_name | changed_cash |
±----±-------------±-------------+
| 1 | customer | 10000.00 |
| 2 | cast | 10000.00 |
| 3 | abc | 100.00 |
±----±-------------±-------------+
3 rows in set (0.00 sec)
mysql> insert into account(name,cash) values(‘randon’,cast(rand()*10000 as decimal));
Query OK, 1 row affected (0.00 sec)
mysql> select * from account_history;
±----±-------------±-------------+
| hid | account_name | changed_cash |
±----±-------------±-------------+
| 1 | customer | 10000.00 |
| 2 | cast | 10000.00 |
| 3 | abc | 100.00 |
| 4 | randon | 2435.00 |
±----±-------------±-------------+
4 rows in set (0.00 sec)
mysql> select * from account;
±—±---------±----------+
| id | name | cash |
±—±---------±----------+
| 1 | shop | 2000 |
| 2 | customer | 40000 |
| 6 | bank | 999990000 |
| 7 | cust | 10000 |
| 8 | abc | 100 |
| 9 | randon | 2435 |
±—±---------±----------+
6 rows in set (0.00 sec)
mysql> create trigger trig_a_delete after delete on account
-> for each row
-> insert into account_history(account_name,changed_cash)
-> values(old.name,old.cash*-1);
Query OK, 0 rows affected (0.00 sec)
mysql> delete from account where id=7;
Query OK, 1 row affected (0.01 sec)
mysql> select * from account_history;
±----±-------------±-------------+
| hid | account_name | changed_cash |
±----±-------------±-------------+
| 1 | customer | 10000.00 |
| 2 | cast | 10000.00 |
| 3 | abc | 100.00 |
| 4 | randon | 2435.00 |
| 5 | cust | -10000.00 |
±----±-------------±-------------+
5 rows in set (0.00 sec)
mysql> select * from account;
±—±---------±----------+
| id | name | cash |
±—±---------±----------+
| 1 | shop | 2000 |
| 2 | customer | 40000 |
| 6 | bank | 999990000 |
| 8 | abc | 100 |
| 9 | randon | 2435 |
±—±---------±----------+
5 rows in set (0.00 sec)
mysql> create trigger trig_a_update after update on account
-> for each row
-> insert into account_history(account_name,changed_cash)
-> values(concat(‘old:’,old.name,’;new:’,new.name),new.cash-old.cash);
Query OK, 0 rows affected (0.00 sec)
mysql> update account set cash=cash-10000,name=‘ICBC’ where name=‘bank’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from account;
±—±---------±----------+
| id | name | cash |
±—±---------±----------+
| 1 | shop | 2000 |
| 2 | customer | 40000 |
| 6 | ICBC | 999980000 |
| 8 | abc | 100 |
| 9 | randon | 2435 |
±—±---------±----------+
5 rows in set (0.00 sec)
mysql> select * from account_history;
±----±------------------±-------------+
| hid | account_name | changed_cash |
±----±------------------±-------------+
| 1 | customer | 10000.00 |
| 2 | cast | 10000.00 |
| 3 | abc | 100.00 |
| 4 | randon | 2435.00 |
| 5 | cust | -10000.00 |
| 6 | old:bank;new:ICBC | -10000.00 |
±----±------------------±-------------+
6 rows in set (0.00 sec)
DROP TRIGGER trig_a_insert;
DROP TABLE stu_history;
CREATE TABLE stu_history(
stu_action VARCHAR(10),
old_count INT(10),
new_count INT(10)
)
CREATE TRIGGER trig_a_insert AFTER INSERT ON student
FOR EACH ROW
INSERT INTO stu_history(stu_action,old_count,new_count)
VALUES(‘insert’,(SELECT COUNT(1)-1 FROM student),(SELECT COUNT(1) FROM student));
MySQL视图
存储在数据库中的查询语句
select s.stu_id,sub_no,stu_name,SubjectName,score,s.grade_id,gradename from result r join student s on r.stu_id=s.stu_id join subject on sub_no=subjectno
join grade g on s.grade_id=g.gradeid;
这种情况就比较建议使用视图，把整条语句做成一个视图，本质是查询语句的结果存入一个临时空间
视图在数据库中默认没有物理存储，只是相当于临时表，优点：简单化，所见及所得，简单、安全，可以写别名逻辑独立性，看不到原来字段叫啥，对外的接口稳定，使用别名限制
缺点：每次使用的时候相当于再查一遍，性能差、修改不方便
视图是由查询语句生成的，不能用来增删改 create view v_stu_sub_g as select s.stu_id,sub_no,stu_name,SubjectName,score,s.grade_id,gradename from result r join student s on r.stu_id=s.stu_id join subject on sub_no=subjectno
join grade g on s.grade_id=g.gradeid;
mysql> select * from information_schema. views\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: MySchool
TABLE_NAME: v_stu_sub_g
VIEW_DEFINITION: select s.stu_id AS stu_id,r.sub_no AS sub_no,s.stu_name AS stu_name,MySchool.subject.SubjectName AS SubjectName,r.score AS score,s.grade_id AS grade_id,g.GradeName AS gradename from (((MySchool.result r join MySchool.student s on((r.stu_id = s.stu_id))) join MySchool.subject on((r.sub_no = MySchool.subject.SubjectNo))) join MySchool.grade g on((s.grade_id = g.GradeID)))
CHECK_OPTION: NONE
IS_UPDATABLE: YES
DEFINER: [email protected]
SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.01 sec)
mysql> delete from v_stu_sub_g where stu_id=1011 and sub_no=4;
ERROR 1395 (HY000): Can not delete from join view ‘MySchool.v_stu_sub_g’
mysql> create view v_s as select * from student;
mysql> delete from v_s where stu_id=1013;
mysql> show create view v_stu_sub_g\G
*************************** 1. row ***************************
View: v_stu_sub_g
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW v_stu_sub_g AS select s.stu_id AS stu_id,r.sub_no AS sub_no,s.stu_name AS stu_name,subject.SubjectName AS SubjectName,r.score AS score,s.grade_id AS grade_id,g.GradeName AS gradename from (((result r join student s on((r.stu_id = s.stu_id))) join subject on((r.sub_no = subject.SubjectNo))) join grade g on((s.grade_id = g.GradeID)))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
CREATE VIEW v_stu_g AS
SELECT s.stu_id,s.stu_name,s.grade_id,gradeName FROM student s
JOIN grade g ON g.GradeID= s.grade_id
CREATE VIEW v_stu_r_sub AS
SELECT s.stu_name,subjectName,score FROM student s
JOIN result r ON r.stu_id=s.stu_id
JOIN SUBJECT sub ON r.sub_no= sub.subjectNo
CREATE VIEW v_stu_r_sub_g AS
SELECT v_stu_g.gradeName,v_stu_r_sub.* FROM v_stu_r_sub
JOIN v_stu_g ON v_stu_r_sub.stu_name=v_stu_g.stu_name
SELECT * FROM v_stu_r_sub_g a
WHERE (SELECT COUNT(a.score) FROM v_stu_r_sub_g b
WHERE a.gradeName=b.gradeName AND a.subjectName=b.subjectName AND a.score<b.score)<5
mysql> DELIMITER @@
mysql> CREATE PROCEDURE test()
-> BEGIN
-> IF 1>0 THEN SELECT 1+1; END IF;
-> [email protected]@
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> call test();
±----+
| 1+1 |
±----+
| 2 |
±----+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER @@
mysql> CREATE PROCEDURE testcase(IN VALUE INT(11))
-> BEGIN
-> CASE VALUE
-> WHEN 1 THEN SELECT 1+1;
-> WHEN 2 THEN SELECT 1+2;
-> WHEN 3 THEN SELECT 1+3;
-> ELSE SELECT 1+VALUE;
-> END CASE;
-> [email protected]@
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> call testcase(1);
±----+
| 1+1 |
±----+
| 2 |
±----+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> call testcase(5);
±--------+
| 1+VALUE |
±--------+
| 6 |
±--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)