MySQL 面试题1

题目一：

有一个【保单车辆】数据库，数据库包括三个表：

1、保单表：保单号（po）、被保险人（iname）、产品类型（pc）、保险起期（sdate）、保险止期（edate），

五个属性组成，记为：t_poliey_base（po,iname,pc,sdate,edate）, po 为关键字。

2、车辆表：车辆编号（vo）、车牌号码（vc）、品牌（vbrand）、 车架号（vframe）、制造日期（vdate）

五个属性组成，记为：t_vehicele（vo，vc，vbrand，vframe，vdate)，vo为关键字。

3、保单车辆表：保单号（po）， 车辆编号（vo）、创建时间（cdate）

三个属性组成，记为：t_poliey_vehicle（po，vo，cdate)

# 1.将一个保单记录( 保单号:010，被保人：小美，产品类型: 109， 保险起期：2021-01 -01，保险止期：2021-12-31 )

insert into t_policy_base values('010','小美',109,'2021-01-01', '2021-12-31');

 

# 2.删除车辆表中车辆编号为 001 的数据；

delete from t_vehicle where po = '001';

 

# 3.将保单号为 002 的被保险人改为 “李四”；

update t_policy_base set iname='李四' where po = 002;

 

# 4.查询保单表中起保时商大于 2021-02-01 的所有数据，按照保单号降序排列；

select * from t_policy_base where sdate > '2021-02-01' order by po desc ;

 

# 5.查询品牌为宝马并且产品类型为109的所有车牌号码，车架号，保单号，被保险人；

select b.vc, b.vframe, a.po, a.iname from t_policy_base a ,t_vehicele b,t_poliey_vehicle c 
where a.po = c.po and b.vo = c.vo and b.vbrand = '宝马’ and a.pc = 109;

 

题目二：

　　　　

　　查询每门课程都大于80分的学生姓名

1、最小分数 > 80 

select C.name from C group by c.name HAVING min (C.fenshu) > 80;

 

2、子查询，排除小于80的，只要有一门没超过 80分 就不符合要求

select DISTINCT C.name from C where C.name not in (select C.name from C where C.fenshu <= 80);

 

题目三：集团有多个部门，部门下有多名员工，求每个部门绩效分数排名第二的人员

　　　　

根据部门自连接，再给每个员工绩效排序

第一种：先再组内进行排序，并添加一列，再取第二名的人员；

select *，
(select count(*) from b b1 where b1.departmentid = b2.departmentid and b1.score <= b2.score )
from b b2;

　　　　

select * from b b2
where
(select count(*) from b b1 where b1.departmentid = b2.departmentid and b1.score >= b2.score ) = 2;

　　　　　　

 

第二种：先排除最大的，再用 not in 取当前最大的就是第二名

select max(b.score),b.name from b where b.score not in (
select max(b.score) from b group by b.departmentid )
group by b.departmentid ;

第二行SQL取出最大的结果为：

 

第一二行SQL结果为：

 

全部执行结果为：

 

题目四：

　　　　　　

1、查询数学平均成绩最好的那个班级

->两表关联，所有数学成绩

->分组平均值

->排序取第

第一步：先查每个班级的平均成绩

select avg(a.score) from scores a, student b
where a.name = b.name and a.course = '数学'
group by b.class;

　　　　

# avg(a.score)：平均成绩；b.class：班级；scores a, student b查询那两个表
select avg(a.score) as avgscore, b.class from scores a, student b
# 两个表之间的关联条件；及数学平均成绩
where a.name = b.name and a.course = '数学'
# group by=根据班级分组；order by=平均成绩排序;limit=取第一个
group by b.class order by avgscore desc limit 1;