0fSXubPj

1
2 --------comments------------------
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;
45------------comments end-----------------
46
47
48----------price history-------------------
49
50
51create table if not exists price_history(
52id int(10) unsigned not null primary key auto_increment, 
53old_price int ,
54product_id int(10) unsigned ,
55constraint fk_products_pricehist foreign key(product_id) references products(id)on delete cascade on update cascade
56);
57insert into price_history(old_price, product_id) select price,id from products;
58select *from price_history;
59
60
61
62delimiter //
63create trigger when_change AFTER UPDATE on products for each row
64begin 
65 if new.price!=old.price then
66insert into price_history(old_price, product_id)values (old.price, new.id) ;
67 end if;
68end //
69delimiter ;
70update products set price=99 where id=1;
71select * from products;
72select* from price_history;
73
74
75
76
77
78DELIMITER //
79CREATE PROCEDURE change_price(
80IN changed_price INT,
81IN product_id INT
82)
83BEGIN
84DECLARE id INT;
85SELECT products.id INTO id FROM products WHERE products.id = product_id;
86 IF (id IS NULL) THEN
87SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'missing product error';
88ELSE
89update products set price=changed_price where products.id=product_id;
90END IF;
91END//
92DELIMITER ;
93call change_price(30, 8);
94select * from products;
95
96
97
98select name, price, max(price) as maxprice, min(price) as minprice from products;
99
100---------price history end---------------------
101
102---------ratings--------------------------
103create table if not exists ratings(
104id int(10) unsigned not null primary key auto_increment, 
105value_ int not null, 
106product_id int(10) unsigned not null, 
107constraint fk_product_ratings foreign key(product_id) references products(id)
108on delete cascade on update cascade
109);
110
111insert into ratings values
112(1, 1, 5), 
113(2, 4, 2), 
114(3, 5, 3), 
115(4, 4, 4),
116(5, 3, 1)
117;
118
119
120delimiter //
121create trigger when_add BEFORE INSERT ON ratings for each row
122begin 
123 declare nr int;
124 select product_id into nr from ratings where product_id=new.product_id;
125 if (new.value_ >5 ) or (nr is not null) then
126 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'bad value';
127 end if;
128end //
129delimiter ;
130insert into ratings values(3,2,6);
131select * from ratings;
132
133
134
135create view user_ratings as select u.first_name, u.last_name, u.email, p.name, r.product_id, r.value_
136from users as u 
137join orders as o on o.user_id=u.id
138join order_products as op on op.order_id=o.id
139join products as p on p.id=op.product_id
140join ratings as r on r.product_id=p.id;
141select * from user_ratings;
142
143
144
145select p.name, value_ as average_ from products as p 
146join ratings as r on r.product_id=p.id
147order by average_ desc;
148
149
150---ratings end-------------------------