MySQL 强化练习八

• 在score表上创建一个触发器，当有新的数据插入时，在score_bak表里记录新插入的数据的所有字段信息，并用tstamp字段标注数据的插入时间

mysql> desc score;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| sid       | int(11) | NO   | PRI | NULL    |       |
| course_id | int(11) | NO   | PRI | NULL    |       |
| score     | int(11) | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

delimiter //
create trigger simple_trigger3
after insert
on score for each row
begin
insert into score_bak values(new.sid,new.course_id,new.score,now());
end;
//
delimiter ;

 

• 在score表上创建一个触发器，当有新的数据插入时，在score_avg表里记录对应学生的所有课程的平均成绩（注意，如果在score_avg表里已经有了学生的记录，需要update）

delimiter //
create trigger simple_trigger4
after insert
on score for each row
begin
Declare n int;
Select count(*) into n from score_avg where sid=new.sid;
If n=1 then
update score_avg set score_avg=(select avg(score) from score where sid=new.sid)
where sid=new.sid;
else
insert into score_avg select sid,avg(score) from score 
where sid=new.sid group by sid;
end if;
end;
//
delimiter ;

mysql> desc score;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| sid       | int(11) | NO   | PRI | NULL    |       |
| course_id | int(11) | NO   | PRI | NULL    |       |
| score     | int(11) | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> create table score_avg(sid int,score_avg int);
Query OK, 0 rows affected (0.12 sec)

mysql> desc score_avg;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| sid       | int(11) | YES  |     | NULL    |       |
| score_avg | int(11) | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> delimiter //
mysql> create trigger simple_trigger4
    -> after insert
    -> on score for each row
    -> begin
    -> Declare n int;
    -> Select count(*) into n from score_avg where sid=new.sid;
    -> If n=1 then
    -> update score_avg set score_avg=(select avg(score) from score where sid=new.sid)
    -> where sid=new.sid;
    -> else
    -> insert into score_avg select sid,avg(score) from score 
    -> where sid=new.sid group by sid;
    -> end if;
    -> end;
    -> //
Query OK, 0 rows affected (0.09 sec)

mysql> delimiter ;
mysql> 
mysql> select * from score_avg;
Empty set (0.00 sec)

mysql> select * from score;
+-----+-----------+-------+
| sid | course_id | score |
+-----+-----------+-------+
|   1 |         1 |    76 |
|   1 |         2 |    90 |
|   1 |         3 |    82 |
|   1 |         5 |    56 |
|   2 |         2 |    78 |
|   2 |         3 |    77 |
|   2 |         4 |    92 |
|   2 |         5 |    65 |
|   3 |         1 |    48 |
|   3 |         2 |    95 |
|   3 |         3 |    75 |
|   3 |         4 |    89 |
|   3 |         5 |    92 |
|   4 |         3 |    78 |
|   4 |         4 |    67 |
|   5 |         1 |    75 |
|   5 |         3 |    90 |
|   5 |         4 |    82 |
|   6 |         2 |    58 |
|   6 |         4 |    88 |
|   7 |         1 |    55 |
|   7 |         2 |    65 |
|   7 |         3 |    63 |
|   7 |         4 |    68 |
|   7 |         5 |    70 |
|   8 |         4 |    88 |
|   8 |         5 |   100 |
+-----+-----------+-------+
27 rows in set (0.01 sec)

mysql> insert into score values(1,4,78);
Query OK, 1 row affected (0.02 sec)

mysql> select * from score_avg;
+------+-----------+
| sid  | score_avg |
+------+-----------+
|    1 |        76 |
+------+-----------+
1 row in set (0.00 sec)

mysql> select * from course;
+----+-------------+------------+
| id | course_name | teacher_id |
+----+-------------+------------+
|  1 | math        |          3 |
|  2 | english     |          2 |
|  3 | chinese     |          4 |
|  4 | history     |          1 |
|  5 | biology     |          5 |
+----+-------------+------------+
5 rows in set (0.04 sec)

mysql> show create table score;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| score | CREATE TABLE `score` (
  `sid` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`,`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into score values(1,6,89);
Query OK, 1 row affected (0.16 sec)

mysql> select * from score_avg;
+------+-----------+
| sid  | score_avg |
+------+-----------+
|    1 |        79 |
+------+-----------+
1 row in set (0.00 sec)

mysql> insert into score values(10,1,89);
Query OK, 1 row affected (0.09 sec)

mysql> select * from score_avg;
+------+-----------+
| sid  | score_avg |
+------+-----------+
|    1 |        79 |
|   10 |        89 |
+------+-----------+
2 rows in set (0.01 sec)

mysql> insert into score values(10,2,95);
Query OK, 1 row affected (0.08 sec)

mysql> select * from score_avg;
+------+-----------+
| sid  | score_avg |
+------+-----------+
|    1 |        79 |
|   10 |        92 |
+------+-----------+
2 rows in set (0.00 sec)