数据库查询技巧以及横纵表数据库查询技巧以及横纵表

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=https://www.it610.com/article/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 成绩;