头哥数据库实战答案及解析(1-6 到 1-9)
1-6 MySQL数据库 - 单表查询（三）第一关：对查询结果进行排序
编程要求 在右侧编辑器Begin-End处增补代码，查询门生结果表中1班同砚的所有信息并以结果降序的方式表现结果。 我们已经为你提供了门生结果表： tb_score表数据：
stu_idclass_idnamescore12Jack8121David7431Mason9242Ethan8951Gina65 分析 利用 order by 字句对筛选出来的结果进行排序，基本用法： select 字段名 from 表名 order by 字段名 (DESC) 假如不加 desc 则默以为升序
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 查询1班同学的所有信息以成绩降序的方式显示结果 ##########
select *
from tb_score
where class_id=1
order by score desc;
########## End ########## 第二关：分组查询
编程要求 在右侧编辑器Begin-End处增补代码，对班级表中的班级名称进行分组查询。 我们已经为你提供了班级表信息： tb_class表数据：
stu_idclass_idname1367Jack2366David3366Mason4367Ethan5366Gina 分析 利用 group by 字句进行分组查询，基本用法为 select 字段名 from 表名 group by 字段名
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 对班级名称进行分组查询 ##########
select * from tb_class
group by class_id
########## End ########## 第三关：利用 LIMIT 限制查询结果的数量
编程要求 在右侧编辑器Begin-End处增补代码，查询班级中第2名到第5名的门生信息，并根据门生结果进行降序排序。 我们已经为你提供了门生结果表： tb_score表数据：
idnamescore1Jack892David733Mason624Ethan935Gina766Dorris987kain868Alice91 分析 这里涉及到多个字句的顺序问题，假如一个查询语句同时含有 group by, having, order by, limit 那么它们在 sql 语句中出现的顺序就是上述的顺序。 这个题利用到了利用 limit 实现分页查询，基本的用法为 limt 开始查询的页码-1, 需要查询的数据个数，如上题中要查询 2 到 5 的数据，limit 背面的数字就是 1 和 4。
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 查询班级中第2名到第5名的学生信息 ##########
select * from tb_score
order by score desc
limit 1, 4;
########## End ########## 1-7 MySQL数据库 - 毗连查询
第一关：内毗连查询
编程要求 在右侧编辑器增补代码，查询数据表中门生姓名以及对应的班级名称，将其对应的列名分别另命名为studentName和className。 我们为你提供了两张表，内容如下： tb_student表数据：
idnameclass_id1Emma22Mary43Allen(null)4Kevin15Rose26James1 tb_class`表数据：
idname1软件16312软件16323测试16314测试1632 分析 这里用到了多表查询的内毗连查询，可以一次查多个表，到达信息的匹配，内毗连查询分为隐式的内毗连和显式的内毗连，一种是用返回的笛卡尔积加上 where 字句来实现筛查，另一种是利用 join on 字句来实现，两种查询方法并没有本质上的差别，只是写法有所区别。
USE School;
########## 查询数据表中学生姓名和对应的班级 ##########
########## Begin ##########
-- 显示的方法
select tb_student.`name` as studentName,tb_class.`name` as className
from tb_class
join tb_student on tb_class.id=tb_student.class_id;
-- 利用隐式的方法查询
select tb_student.`name` as studentName, tb_class.`name` as className
from tb_student, tb_class
where tb_student.class_id = tb_class.id;
########## End ########## 第二关：外毗连查询
编程要求 在右侧编辑器增补代码，分别利用左外毗连和右外毗连查询数据表中所有门生姓名和对应的班级名称，查询结果列分别另命名为studentName和className。 我们为你提供了两张表，内容如下： tb_student表数据：
idnameclass_id1Emma22Mary43Allen(null)4Kevin15Rose26James1 tb_class表数据：
idname1软件16312软件16323测试16314测试1632 注意：请利用 tb_student 作为左表，tb_class 作为右表。
分析 这里涉及到了外毗连的知识，先来叙述一下外毗连和内毗连的区别： 内毗连是用相同的键值来将两个表联系起来的，假如两个表中没有相同的键值则不会返回结果。 而外毗连将会表现即使匹配不上的结果，不符合的会用 null 添补，假如盼望左边的值表现出来，就用左外毗连 left join on 反之则用 right join on。
USE School;
########## 使用左外连接查询所有学生姓名和对应的班级 ##########
#请在此处添加实现代码
########## Begin ##########
select stu.name as studentName, cla.name as className
from tb_student as stu left join tb_class as cla on stu.class_id = cla.id;
########## End ##########
########## 使用右外连接查询所有学生姓名和对应的班级 ##########
#请在此处添加实现代码
########## Begin ##########
select stu.name as studentName, cla.name as className
from tb_student as stu right join tb_class as cla on stu.class_id = cla.id;
########## End ########## 第三关：复合条件毗连查询
**编程要求 在右侧编辑器增补代码，查询所有班级里分数在90分以上的门生的姓名和门生的结果以及门生地点的班级，此中门生的姓名和门生地点班级分别另命名为studentName和className。 我们为你提供了两张表，内容如下： tb_student表数据：
idnameclass_idscore1Emma2892Mary4924Kevin1765Rose3686James199 tb_class表数据：
idname1软件16312软件16323测试16314测试1632 分析 该题用到了符合条件的查询，即对查询出来的笛卡尔积用 where 字句来限制查询的结果。
USE School;
########## 查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级 ##########
#请在此处添加实现代码
########## Begin ##########
select stu.name as studentName, score, cla.name as className
from tb_student as stu, tb_class as cla
-- 如果列名不会引起误解，则可以省略表名
where score > 90 && class_id = cla.id;
########## End ########## 1-8 MySQL数据库 - 子查询
第一关：带比力运算符的子查询
编程要求 在右侧编辑器增补代码，查询大于所有均匀年岁的员工姓名与年岁。 我们为你提供了tb_emp表，数据如下：
idnameage1Mary232Allen213kevin254Tom335Nancy28 分析 这个题涉及到了子查询的知识，子查询指的是在 select 语句中出现了其他的 select 语句，这个语句既可以作为数据出现在 where 的限制条件中，也可以作为一个临时表出现在 from 背面，利用子查询可以办理很多问题。
USE Company;
#请在此处添加实现代码
########## Begin ##########
#1.查询大于所有平均年龄的员工姓名与年龄
select name, age
from tb_emp
where age > (select avg(age) from tb_emp);
########## End ########## 第二关：关键字子查询
编程要求 我们为你提供了如下数据表： tb_salary表数据：
idpositionsalary1Java80002Java84003Java90004Python65005Python10000 根据提供的数据，在右侧编辑器中增补代码： 1）查询薪资表中比Java最高工资高的所有员工职位名称和薪资； 2）查询薪资表中比Java最低工资高的所有员工职位名称和薪资； 3）查询薪资表中职位为Java的所有员工职位名称和薪资。
分析 这个题涉及到了查询的关键字，适用于多行查询，all 表示所有，有些环境下可以用 max 的单行子查询代替，any 有些环境下可以用 min 的单行子查询来代替，可以把查询出来的表看作一个数据的聚集来再次带入查询语句进行查询，
USE Company;
#请在此处添加实现代码
########## Begin ##########
#1.使用 ALL 关键字进行查询
select `position`, salary
from tb_salary
where salary > all(select salary from tb_salary where `position` = 'Java');
#2.使用 ANY 关键字进行查询
select `position`, salary
from tb_salary
where salary > any(select salary from tb_salary where `position` = 'Java');
#3.使用 IN 关键字进行查询
select `position`, salary
from tb_salary
where id in (select id from tb_salary where `position` = 'Java');
########## End ########## 1-9 MySQL数据库 - 复杂查询（一）
第一关：交换工资
编程要求 给定一张 tb_Salary 表，如下所示，有 m = 男性 和 f = 女性的值。 交换所有的 f 和 m 值（比方，将所有 f 值更改为 m，反之亦然）。
idnamesexsalary1Elonf70002Donnyf80003Careym60004Karinf90005Larisam55006Soram500 要求只利用一句更新update语句，且不答应含有任何select语句完成任务。
分析 这个题运用到了 update 语句，以及 if 语句或者 case 语句。 先来回首一下 update 语句，它的基本语法是 update 表名 set 列名 = 新元素。 再来说一下 if 语句的利用，相当于 Java 中的三元运算符，if(条件, 值1, 值2)，假如满足条件则返回第一个值，否则返回第二个值。 再来说一下 case 的用法，基本结构为 case when 条件1 then 值1 when 条件2 then 值2 else 值3 end 可以用于不止一个条件的筛选，背面可以跟多个 when 语句。
#请在此添加实现代码
########## Begin ##########
update tb_Salary
set sex = if(sex='f', 'm', 'f');
########## End ########## 第二关：换座位
编程要求 本关任务：改变相邻俩门生的座位。 小美是一所中学的信息科技老师，她有一张 tb_Seat座位表，平常用来储存门生名字和与他们相对应的座位 id。 tb_Seat表结构数据如下：
idname1Elon2Donny3Carey4Karin5Larisa 现在小美想改变相邻俩门生的座位（若门生人数为奇数，则无需改变最后一位同砚的座位），现在需要你编写SQL输出小美想要的的结果。
分析 查询语句的综合利用，先确定思绪，分为两种环境，一种是最后一位是奇数，另一种是最后一位是偶数，假如是偶数的话则处理惩罚方式相同，假如是奇数的话则因该不变，因此应该写三条 case 语句，假如是偶数的话，将 id - 1，假如是奇数且不是最后一个则 id + 1，最后一种环境就是假如是奇数且是最后一位的话，则 id 不变。
#请在此添加实现代码
########## Begin ##########
select (case
-- 当当前id是奇数并且不是最后一个id时，id+1
when mod(id,2)!=0 and id!=counts then id+1
-- 当当前id是奇数并且是最后一个id时，id不变
when mod(id,2)!=0 and id=counts then id
-- 否则，当前id是偶数，id-1
else id-1 end)
as id, name
from tb_Seat, (select max(id) as counts from tb_Seat) as tb_Seat_counts
order by id;
########## End ########## 第三关：分数排名
编程要求 编写SQL查询来实现二种排名方式的分数排名。 score表结构信息如下：
IdScore13.5223.6534.2343.8554.2363.65 假如两个分数相同，则两个分数排名（Rank）相同。 环境一：平分后的下一个名次是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。例：1、1、2、3、4、4。 环境二：排名黑白连续的。例：1、1、1、4、4、6。
分析 查询的综合应用，先将这个序列进行排序作为查询的表利用，再在查询中限制条件，第一种排序方法无序得到每个人当前的序号，只要决定排名是否增长即可，而第二种则需要记忆每个人其时的排序，决定增长的时间需要做转换。
-- 使用 set @来定义一个变量
set @dense_rank=0;
set @prev_score = null;
select Score,(
case
when @prev_score = Score
then @dense_rank # 当前分数与上一个分数相同，rank不变
-- 利用 := 来实现判断的同时赋值
when @prev_score := Score # 恒为true，当不满足上一个语句时（分数不同时）
then @dense_rank := @dense_rank + 1
end
)as Rank
from (select Score from score order by Score desc) as temp;
-- 第二种排序方法需要记忆每个成员的排名，重点看一下这个排序
set @rank=0;
set @prev_score = null;
select Score,(
-- 下面进行判断，当成绩发生变化后就切换编号
case
when @prev_score = Score then
@rank
when @prev_score := Score then
@rank := Id
end
)as Rank
from (
-- 进行每一次选择的时候都将它的 id 加一
select @id:=@id+1 AS ID, Score
-- 将 score 和 新建的 @id 变量来拼接形成一个新的表并返回
from score, (SELECT @id:=0) AS initorder
order by Score desc
)
as temp; 第四关：体育馆的人流量
编程要求 本关任务：某市建了一个新的体育馆，每日人流量信息被记录在gymnasium表中：序号 (id)、日期 (date)、 人流量 (visitors_flow)。 请编写一个查询语句，找出人流量处于高峰的记录 id、日期 date 和人流量 visitors_flow，此中高峰定义为前后连续三天人流量均不少于 100。 gymnasium表结构数据如下：
iddatevisitors_flow12019-01-015822019-01-0211032019-01-0312342019-01-046752019-01-0516862019-01-06135272019-01-0738282019-01-0832692019-01-0999 提示：天天只有一行记录，日期随着 id 的增长而增长。
分析 复杂的查询问题，这个题有两个限制的条件，一个是连续三天，另一个是要大于一百，以上述的例子来说，5 6 7 和 6 7 8 这两组是满足条件的，但该如何将它们返回呢？ 可以将表格都拼接，三个表拼接后包罗了序号的所有环境，这时间我们只需要将 a 中的数据完全表现出来即可，比如 5 6 7,6 7 8,6 5 7, 6 7 5 等等，因此需要写出三种可能的环境，即 a b c 三个表中序号是三个连续天数的所有环境。
#请在此添加实现代码
########## Begin ##########
select distinct a.* from gymnasium a,gymnasium b,gymnasium c
where a.visitors_flow>=100 and b.visitors_flow>=100 and c.visitors_flow>=100
and (
(a.id = b.id-1 and b.id = c.id -1) or
(a.id = b.id-1 and a.id = c.id +1) or
(a.id = b.id+1 and b.id = c.id +1)
) order by a.id;
########## End ########## 第五关：统计总结果
编程要求 本关任务：计算每个班的语文总结果和数学总结果，此中低于 60 分的结果不记入总结果。 tb_score结构数据：
namechinesemathsA8998B9989C5566D8866E5566F8899 tb_class表结构数据：
stunameclassnameAC1BC2CC3DC2EC1FC3 分析 起首想到的就是将两个表组合起来，并用 group by 语句进行分组，再对分组后的表格进行利用来求取总结果。
#请在此添加实现代码
########## Begin ##########
select
-- as 可以省略
classname,
sum(if(chinese>=60,chinese,0)) chinese,
sum(if(maths>=60,maths,0)) maths
from tb_score a,tb_class b where a.name=b.stuname
group by b.classname;
########## End ##########
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]