FinalProject/Sql.sql at master · kangdechen/FinalProject · GitHub

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
use movies;

drop table if exists UserSession;
create table UserSession
(
	id int not null auto_increment,
    userName varchar(30),
	sessionId varchar(30),
      PRIMARY KEY (`id`)
);
/*Alter table recommendation drop foreign key fk_recommendation_userName; */

drop table if exists users;
create table users
(
	userId int not null auto_increment,
    userName varchar(30) unique,
	passwd varchar(30),
    picUrl varchar(100),
      PRIMARY KEY (`userId`)
);

insert into users (userName,passwd,picUrl) values ('kangde','123456','picture');
select * from users;


Alter table recommendation drop foreign key fk_tag_uploader;
Alter table recommendation drop foreign key fk_tag_movieName;

drop table if exists userPreference;
create table userPreference
(
	upId int not null auto_increment,
    userName varchar(30) ,
	uploader varchar(30),
    movieName varchar(30),
    rating int,
	recId int,
      PRIMARY KEY (upId),
       constraint  fk_tag_uploader FOREIGN KEY (uploader) REFERENCES  recommendation(userName),
        constraint  fk_tag_movieName FOREIGN KEY (movieName) REFERENCES  recommendation(movieName)
);

insert into userPreference (userName,uploader,movieName,rating)VALUES('kangde','test','avatar',1);
select * from userPreference;


/*Alter table tagvalue drop foreign key fk_tagValue_tagId; */
drop table if exists tag ;
create table tag
(
	tagId int auto_increment primary key,
    recomId int not null,
     constraint  fk_tag_recomId FOREIGN KEY (recomId) REFERENCES  recommendation(recId)
);
insert into tag (recomId)value (1);
select * from tag;
select tagid ,recomId,contexts, count(contexts)as cnt from tag
where recomId=3
group by contexts
order by cnt desc ;
/*
drop table if exists tagValue;
create table tagValue
(
	tvId int auto_increment primary key,
    tagId int not null,
    names varchar(100),

   constraint  fk_tagValue_tagId FOREIGN KEY (tagId) REFERENCES  tag(tagId)
);
insert into tagValue (tagId,names)value (1,'3d');
select * from tagValue;

*/
/*Alter table recommendation drop foreign key fk_recommendation_userName; */
Alter table tag drop foreign key fk_tag_recomId;
drop table if exists Recommendation;
create table recommendation
(
	recId int not null auto_increment,
    userName varchar(30) ,
	movieName varchar(30),
    comments varchar(30),
    rating float,
    picUrl varchar(100),
    likes int,
      PRIMARY KEY (recId),
      constraint  fk_recommendation_userName FOREIGN KEY (userName) REFERENCES  users(userName)
);
insert into recommendation (userName,movieName,comments,rating,picUrl,likes)VALUES('kangde','avatar','Best 3d movies',1,'image.jpg',0);
select * from recommendation;


Drop  Function if exists calculate_sum;
DELIMITER //
CREATE Function calculate_sum
(  userName varchar(30),
  movieName        varchar(30),
  uploader 		varchar(30)

)
RETURNS DECIMAL(9,2)
BEGIN
  DECLARE sum_rating DECIMAL(9,2);
    select sum(rating)
	into sum_rating
	from userPreference

	where userPreference.movieName =movieName OR userPreference.uploader= uploader and userPreference.userName =userName;
 RETURN sum_rating;
END//

select calculate_sum("avatar","kangde","harumaki");

select * recommendation
order by
calculate_sum("KANGDE",recommendation.movieName,recommendation.userName,);


select *  from recommendation order by calculate_sum("kangde",recommendation.movieName,recommendation.userName) desc;