1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
| drop database if exists reviewdb;
create database reviewdb
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
use reviewdb;
create table review (
id int not null AUTO_INCREMENT,
memo varchar(20),
primary key (id)
) engine=InnoDB
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
create table user (
id int not null AUTO_INCREMENT,
name varchar(20),
primary key (id)
) engine=InnoDB
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
create table likes (
rid int,
uid int,
primary key(rid, uid),
foreign key(rid) references review(id),
foreign key(uid) references user(id)
) engine=InnoDB
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
#######
insert into review values (1, '양고기 맛있네요');
insert into review values (2, '아귀포 최고');
insert into review values (3, '동네 맛집');
insert into review values (4, '역시 삼겹살!');
insert into user values (1, '홍길동');
insert into user values (2, '임꺽정');
insert into user values (3, '장길산');
insert into likes values (3, 1);
insert into likes values (3, 2);
insert into likes values (1, 3);
select * from review r
left join likes l on r.id = l.rid
left join user u on l.uid = u.id;
#######
select r.memo, count(distinct l.uid) as likes from review r
left join likes l on r.id = l.rid
left join user u on l.uid = u.id
group by r.id
order by count(distinct l.uid) desc;
#######
alter table review add total_likes int unsigned default 0;
desc review;
truncate table likes;
select r.memo, count(distinct l.uid) as likes from review r
left join likes l on r.id = l.rid
left join user u on l.uid = u.id
group by r.id
order by count(distinct l.uid) desc;
select review.memo, review.total_likes
from review
order by review.total_likes desc;
#######
delimiter $
create trigger insert_likes
after insert
on likes for each row
begin
update review set total_likes = total_likes + 1
where review.id = new.rid;
end$
delimiter ;
#######
# delimiter $
# create trigger insert_delete_likes
# after insert, delete
# on likes for each row
# begin
# update review set total_likes =
# (select count(*) from likes
# where likes.rid = new.rid)
# where review.id = new.rid;
# end$
# delimiter ;
#######
insert into likes values (3, 1);
insert into likes values (3, 2);
insert into likes values (1, 3);
select id, memo, total_likes
from review
order by total_likes desc;
#######
delimiter $
create trigger delete_likes
after delete
on likes for each row
begin
update review set total_likes = total_likes - 1
where review.id = old.rid;
end$
delimiter ;
#######
delete from likes where rid = 3 and uid = 1;
select id, memo, total_likes
from review
order by total_likes desc;
#######
delimiter $
create trigger update_likes
after update
on likes for each row
begin
update review set total_likes = total_likes - 1
where review.id = old.rid;
update review set total_likes = total_likes + 1
where review.id = new.rid;
end$
delimiter ;
#######
update likes set rid = 4 where rid = 3 and uid = 2;
select id, memo, total_likes
from review
order by total_likes desc;
#######
show triggers;
|