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;
# 显示每部电影平均打分，电影名，评分
select a.title, avg(b.ratings)
from movies a
inner join ratings b
on a.movie_id = b.movie_id
group by a.title;
# 显示每部电影不同性别平均分
select c.title, a.gender, avg(b.ratings)
from users a
inner join ratings b
on a.user_id = b.user_id
inner join movies c
on b.movie_id = c.movie_id
group by c.title, a.gender;
# 显示电影平均分大于4分的电影
select a.title, avg(b.ratings)
from movies a
inner join ratings b
on a.movie_id = b.movie_id
group by a.title
having avg(b.ratings) > 4;
# 显示电影平均分大于4分的电影（子查询）
#显示电影平均分大于4分的电影（子查询）
select title
from movies
where movieid
in(
select movieid
from movies
where movieid
in
(
select movieid
from ratings
group by movieid
having avg(rating)>4
)
);
# 不同年龄分组电影平均打分
select c.age_min, c.age_max, avg(a.ratings)
from ratings a
inner join users b
on a.user_id = b.user_id
inner join age_dict c
on b.age = c.age_id
group by c.age_min, c.age_max;
数据查询优化: 1. 给当前查询数据添加索引create index ix_user_id on users (user_id)
2.给关系数据库添加主外键关联。
2. mysql数据库表的横纵交换。
create table b
(
姓名 varchar(20),
语文 int,
数学 int,
英语 int
);
insert into b(姓名,语文,数学,英语) values('张三',60,70,80);
insert into b(姓名,语文,数学,英语) values('李四',90,100,0);
insert into b(姓名,语文,数学,英语) values('五狗',80,90,70);
# 加了引号代表每一行都要显示该数据 as 只执行一次（适用于表头），不加引号代表查询的结果字段
select 姓名, '语文' as 课程, 语文 as 成绩 from b
union all
select 姓名, '数学' as 课程, 数学 as 成绩 from b
union all
select 姓名, '英语' as 课程, 英语 as 成绩 from b
;
# 创建纵表
create table c
select 姓名, '语文' as 课程, 语文 as 成绩 from b
union all
select 姓名, '数学' as 课程, 数学 as 成绩 from b
union all
select 姓名, '英语' as 课程, 英语 as 成绩 from b
;
select * from c;
#1)case vlaue when [compare-value]then reslut [when[compare-value]] then result ...] [else result] end
#(2)case when [condition] then result [when[condition]then result...][else result] end
#第一形式当value=compare-value时返回result
#
#第二形式当第一个为真值的condition出现时，返回该条件的结果，如果没有匹配的结果值，那么else后的结
#果将被返回，如果没有else部分，那么返回null
# 只要出现聚合函数就一定有group by。
select 姓名,
max(case 课程 when '语文' then 成绩 else 0 end) as '语文',
max(case 课程 when '数学' then 成绩 else 0 end) as '数学',
max(case 课程 when '英语' then 成绩 else 0 end) as '英语'
from c
group by 姓名;
select * from c where 课程='数学' ORDER BY 成绩;
