jEs7fjKj

1
2
3create table if not exists comments(
4id int(10) unsigned not null primary key auto_increment, 
5comment_ varchar(20) not null, 
6user_id int(10) unsigned not null, 
7posted_at timestamp not null default current_timestamp,
8product_id int(10) unsigned not null,
9constraint fk_users_comments foreign key(user_id) references users(id)on delete cascade on update cascade,
10constraint fk_products_comments foreign key(product_id) references products(id)on delete cascade on update cascade
11);
12insert into comments values
13(1, 'nice', 2,'2020-01-09 11:11:11',2 ), 
14(2, 'nice', 3,'2020-01-09 11:11:11',1 ), 
15(3, 'good', 5,'2020-01-09 11:11:11',2 ), 
16(4, 'terrible', 1,'2020-01-09 11:11:11',3 ), 
17(5, 'happy', 5,'2020-01-09 11:11:11',4 )
18;
19
20
21delimiter //
22create trigger when_comment BEFORE INSERT ON comments for each row
23begin 
24 if length(new.comment_)<10 then
25 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'too short error';
26 end if;
27end //
28delimiter ;
29insert into comments values(5, 'good', 1,'2020-01-09 11:11:11',4  );
30select * from comments;
31
32
33
34create view product_comments as select  p.name, c.comment_, u.first_name, u.last_name, c.posted_at
35from users as u 
36join comments as c on c.user_id=u.id
37join products as p on c.product_id=p.id;
38select * from product_comments;
39
40
41select p.id, p.name, count(u.id) as nr_comm from products as p 
42join comments as c on c.product_id=p.id
43join users as u on c.user_id=u.id
44group by p.id;