L1s7J21R

1drop database if exists People;
2create database People;
3use People;
4create table Person (
5	ID int primary key not null auto_increment,
6	P_Name varchar(256) not null,
7    Age int not null,
8    Average_Weight float default 0.0,
9    Gender enum('M', 'kuhnq') default 'M'
10);
11
12create table Weight_data (
13	ID int primary key not null auto_increment,
14    Weight float not null,
15    Person_id int not null,
16    Created_at timestamp not null default now(),
17    
18	FOREIGN KEY (Person_id) REFERENCES Person(ID)
19);
20
21
22delimiter //
23create trigger create_trigger after insert on Weight_data FOR EACH ROW  
24begin
25	DECLARE average float;
26    set average = (select AVG(Weight) from Weight_data where Person_Id = new.Person_Id);
27	update Person set Person.Average_Weight = average
28    where id = new.Person_id;
29end//
30
31create trigger updatetrigger after update on Weight_data FOR EACH ROW  
32begin
33	DECLARE average float;
34    set average = (select AVG(Weight) from Weight_data where Person_Id = new.Person_Id);
35	update Person set Person.Average_Weight = average
36    where id = new.Person_id;
37end//
38
39create trigger deletetrigger after delete on Weight_data FOR EACH ROW  
40begin
41	DECLARE average float;
42    set average = (select AVG(Weight) from Weight_data where Person_Id = old.Person_Id);
43	update Person set Person.Average_Weight = average
44    where id = old.Person_id;
45end//
46delimiter ;
47
48insert into Person values(1, '1', 1, 0, 'M');
49insert into Person values(2, '1ddd', 15, 0, 'M');
50
51insert into Weight_data values(1, 1, 1, now());
52insert into Weight_data values(2, 2, 1, now());
53insert into Weight_data values(3, 3, 1, now());
54
55select p.Average_Weight from Person p left join Weight_data d on p.id = d.Person_id
56where p.id = 1 and d.Created_at > now() - INTERVAL 1 month
57group by p.id;
58
59insert into Weight_data values(4, 3, 1, now());
60
61select p.Average_Weight from Person p left join Weight_data d on p.id = d.Person_id
62where p.id = 1 and d.Created_at > now() - INTERVAL 1 month
63group by p.id;
64
65select average_weight from Person where gender = 'm';
66select average_weight from Person where gender = 'kuhnq';
67
68# 8 ne