MySQL笔试题整理（二）

一、学生表、班级表 

1、学生表：学生编号ID、班级编号c_ID、姓名sname、性别sex、分数score

2、班级表：班级编号cID、班级cname

--建表
create table Student
(ID varchar(10),c_ID int,sname varchar(10),sex varchar(10),score decimal(5,0));
insert into Student values('001','1','张三','男','90');
insert into Student values('002','1','李四','女','80');
insert into Student values('003','1','王五','男','65');
insert into Student values('004','2','小刘','男','60');
insert into Student values('005','2','赵六','女','75');
insert into Student values('006','3','李五','女','90');


create table class(cID int,cname varchar(10));
insert into class values('1','一班');
insert into class values('2','二班');




-- 题目：1.1、查询出得分最高的学生
select * from Student where score=(select max(score) from Student);




-- 题目：1.2、查询出一班、二班的人数和平均分，并且按照由高到低排序
select class.cname,count(*),avg(score) from Student,class
where Student.c_ID=class.cID and class.cname in('一班','二班')
group by class.cname order by avg(score) desc;




-- 题目：1.3、写一条sql语句查询出没有班级的学生名字、性别、分数
select a.sname,a.sex,a.score from Student a
left join class b on a.c_ID=b.cID where b.cname is null;

二、保单车辆表

1、保单表policy_base：保单号（po）、被保险人（iname）、产品类型（pc）、保险起期（state）、保险止期（edate）
2、车辆表vehicle：车辆编号（vo）、车牌号码（vc）、品牌（vbrand）、车架号（vframe）
3、保单车辆表policy_vehicle：保单号（ppo）、车辆编号（pvo），创建时间（pdate）

--建表
create table policy_base
(po varchar(10),iname varchar(10),pc varchar(10),state datetime,edate datetime);
insert into policy_base values('001','李四','109','2021/2/1 13:50','2021/2/28 13:50');
insert into policy_base values('002','王五','109','2021/1/1 13:50','2021/1/31 13:50');
insert into policy_base values('003','赵二','110','2021/2/4 13:50','2021/2/25 13:50');
insert into policy_base values('004','小白','110','2021/2/25 13:50','2021/2/25 13:50');
insert into policy_base values('005','张三','109','2021/1/1 13:50','2021/12/31 13:50');


create table vehicle
(vo varchar(10),vc varchar(10),vbrand varchar(10),vframe varchar(10));
insert into vehicle values('001','湘A12345','宝马','12345');
insert into vehicle values('002','湘A12346','宝马','12346');
insert into vehicle values('003','湘A12347','宝马','12347');
insert into vehicle values('004','湘A12348','宝马','12348');
insert into vehicle values('005','湘A12349','宝马','12349');


create table policy_vehicle(ppo varchar(10),pvo varchar(10),pdate datetime);
insert into policy_vehicle values('001','001','2021/3/2 13:58');
insert into policy_vehicle values('002','002','2021/3/3 13:58');
insert into policy_vehicle values('003','003','2021/3/4 13:58');
insert into policy_vehicle values('004','004','2021/3/5 13:58');
insert into policy_vehicle values('005','005','2021/3/6 13:58');



-- 题目：2.1、将一个保单记录
（保单号：010，被保人：小美，产品类型109，保险起期：2021-01-01；保险止期：2021-12-31）
插入policy_base表中

insert into policy_base values('010','小美','109','2021/1/1 13:50','2021/12/31 13:50');




-- 题目：2.2、删除车辆表中车辆编号为001的数据；
delete from vehicle where vo='001';




-- 题目：2.3、将保单号为002的被保险人修改为“李四”；
update policy_base set iname='李四' where po='002';




-- 题目：2.4、查询保单表中起保时间晚于2021-02-01的所有数据，按照保单号降序排列
select * from policy_base  where state>'2021/2/1 23:59' order by po desc;




-- 题目：2.5、查询匹配为宝马并且产品类型为109的所有车辆号码，车架号，保单号，被保险人；
select b.vc,b.vframe,a.po,a.iname from policy_base a,vehicle b,policy_vehicle c 
where a.po=c.ppo and b.vo=c.pvo and b.vbrand='宝马' and a.pc='109';

三、成绩表、学生表

1、成绩表：学生姓名ssname、科目sskc、分数ssfs

2、学生表：学生姓名ssname、班级ssclass、年龄ssage

--建表
create table sscore(ssname varchar(10),sskc varchar(10),ssfs decimal(5,0));
insert into sscore values('小绿','语文','81');
insert into sscore values('小绿','数学','75');
insert into sscore values('小蓝','语文','76');
insert into sscore values('小蓝','数学','90');
insert into sscore values('小红','语文','81');
insert into sscore values('小红','数学','100');
insert into sscore values('小红','英语','90');
insert into sscore values('小紫','语文','70');
insert into sscore values('小紫','数学','100');
insert into sscore values('小紫','英语','72');
insert into sscore values('小黄','语文','73');
insert into sscore values('小黄','数学','60');
insert into sscore values('小黄','英语','68');


create table sstudent(ssname varchar(10),ssclass varchar(10),ssage int);
insert into sstudent values('小绿','一班','15');
insert into sstudent values('小蓝','二班','15');
insert into sstudent values('小红','三班','15');
insert into sstudent values('小紫','四班','16');
insert into sstudent values('小黄','四班','16');




-- 题目3.1、查询每门课程都大于80的学生姓名
select ssname from sscore group by ssname having min(ssfs)>80;




-- 题目：3.2、查询数学平均成绩最好的班级
-- 当平均分只有一个时
select b.ssclass from sscore a,sstudent b 
where a.ssname=b.ssname and a.sskc='数学' group by b.ssclass 
order by avg(a.ssfs) desc limit 1;


-- 当平均分有多个时
select b.ssclass from sscore a,sstudent b 
where a.ssname=b.ssname and a.sskc='数学'
group by b.ssclass having avg(a.ssfs)=
(select avg(a.ssfs) from sscore a,sstudent b 
where a.ssname=b.ssname and a.sskc='数学' group by b.ssclass 
order by avg(a.ssfs) desc limit 1);




-- 题目：3.3、查询每个科目排名第一的学生姓名、科目名称、分数
select b.* from (select sskc,max(ssfs) m from sscore group by sskc) a
inner join sscore b on a.sskc=b.sskc and a.m=b.ssfs order by b.sskc;




-- 题目：3.4、查询每个科目排名前三的学生姓名、科目名称、分数
--第1种方法
select a.* from sscore a
left join (select distinct sskc,ssfs from sscore) b
on a.sskc=b.sskc and a.ssfs<b.ssfs
group by ssname,sskc,ssfs having count(1)<3
order by sskc,ssfs desc;


--第2种方法
select a.* from sscore a
where (select count(distinct b.ssfs) from sscore b 
where b.sskc=a.sskc and a.ssfs<b.ssfs)<3 
order by sskc,ssfs desc;




-- 题目：3.5、查询数学第二名的学生姓名、分数
select * from sscore where sskc='数学' and ssfs=
(select distinct ssfs from sscore where sskc='数学' order by ssfs desc limit 1,1);




-- 题目：3.6、查询四班总分最高的学生的姓名和总分  
--最高分只有一个的情况
select a.ssname,sum(a.ssfs) from sscore a,sstudent b
where a.ssname=b.ssname and b.ssclass='四班' 
group by a.ssname order by sum(a.ssfs) desc limit 1;


--最高分有多个的情况
select a.ssname,sum(a.ssfs) from sscore a,sstudent b
where a.ssname=b.ssname and b.ssclass='四班' group by a.ssname
having sum(a.ssfs)=(select sum(a.ssfs) from sscore a,sstudent b
where a.ssname=b.ssname and b.ssclass='四班' 
group by a.ssname order by sum(a.ssfs) desc limit 1);

小白练习mysql之路！加油

参考：mysql笔试题目_zhouye_li的博客-优快云博客_mysql笔试题