id uid video_id start_time end_time if_follow if_like if_retweet comment_id
1 101 2001
2021-10-01
10:00:00
2021-10-01
10:00:30
0 1 1 NULL
2 102 2001
2021-10-01
10:00:00
2021-10-01
10:00:24
0 0 1 NULL
3 103 2001
2021-10-01
11:00:00
2021-10-01
11:00:34
0 1 0 1732526
4 101 2002
2021-09-01
10:00:00
2021-9-01
10:00:42
1 0 1 NULL
5 102 2002
2021-10-01
11:00:00
2021-10-01
10:00:30
1 0 1 NULL
id video_id author tag duration release_time
1 2001 901 影视 30 2021-01-01 07:00:00
2 2002 901 美食 60 2021-01-01 07:00:00
3 2003 902 旅游 90 2021-01-01 07:00:00
某音短视频 SQL 练习
数据如下:
用户-视频互动表tb_user_video_log
(uid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间, if_follow-是否关注,
if_like-是否点赞, if_retweet-是否转发, comment_id-评论ID)
短视频信息表tb_video_info
(video_id-视频ID, author-创作者ID, tag-类别标签, duration-视频时长(秒), release_time-发布时
间)
DROP TABLE IF EXISTS tb_user_video_log, tb_video_info;
CREATE TABLE tb_user_video_log (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid INT NOT NULL COMMENT '用户ID',
video_id INT NOT NULL COMMENT '视频ID',
start_time datetime COMMENT '开始观看时间',
end_time datetime COMMENT '结束观看时间',
if_follow TINYINT COMMENT '是否关注',
if_like TINYINT COMMENT '是否点赞',
if_retweet TINYINT COMMENT '是否转发',
comment_id INT COMMENT '评论ID'
) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE TABLE tb_video_info (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
video_id INT UNIQUE NOT NULL COMMENT '视频ID',
author INT NOT NULL COMMENT '创作者ID',