mysql 数据库设计 纵表_数据库查询技巧以及横纵表

1. 查询技巧

查询时先写出查询结构,将需要连接的表进行join on 连接好，最后再写出查询的数据

select

from

join

on

[where]

group by 字段[order by 字段 asc/desc]

[having]

实例：

# 显示列表包含电影名、用户性别、评分

select a.title, c.gender, b.ratings

from movies a

inner join ratings b

on a.movie_id = b.movie_id

inner join users c

on b.user_id = c.user_id

limit 5;

# 显示列表包含电影名、用户性别、评分 (只包含男性)

select a.title, c.gender, b.ratings

from movies a

inner join ratings b

on a.movie_id = b.movie_id

inner join users c

on b.user_id = c.user_id

where c.gender = 'M' limit 10;

# 显示男女平均打分，只有两行记录

select a.gender, avg(b.ratings)

from users a

inner join ratings b

on a.user_id = b.user_id

group by a.gender;

<