MySQL面试题

ID Name
1 bill
2 Gaiz
2 hell
2 Till
3 ggoe
3 ggoe
3 ggoe
3 ggoe

1、查询表中存在ID重复三次以上的记录
ID Name
2 Gaiz
2 hell
2 Till

答:创建表db

 select * from db where id in (select id from db group by id having count(id)>3);

首先用分组对数据进行分组,分组条件为id重复次数>3,在主体检索函数用in关键字将分组作为条件

学生成绩表
id name kecheng fenshu 
1 张三 语文    81
1 张三 数学    75
2 李四 语文    40
2 李四 数学    70
3 王五 语文    81
3 王五 数学    100
3 王五 英语    90

2、查询平均成绩大于60分的同学的学号和平均成绩

创建student表

 select id,avg(fenshu) from student group by id having avg(fenshu)>60;

将同学id分组,然后用聚合函数计算平均值

3. 用一条SQL 语句 查询出每门课都大于80 分的学生姓名

select name from student group by name having min(fenshu)>80;

每门课都大于80分即最小分数>80分,使用聚合函数

 4. 学生表 如下:
自动编号 学号 姓名 课程编号 课程名称 分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
删除自动编号不同, 其他都相同的学生冗余信息

创建学生表,并对id(自动编号)设置主键且自动排序

但是中间遇到了一些问题:

①直接使用查询删除

delete from student1 where id not in(select min(id) from student1 group by xuehao,name,kecheng,mingcheng,fenshu);

会提示报错 ERROR 1093 (HY000): You can't specify target table 'student1' for update in FROM clause 

这是因为在MySQL中，不能通过嵌套子查询来直接删除或者修改记录，需要通过别名来指定嵌套子查询作为一个临时表。

解决办法：给嵌套子查询的结果取一个别名，然后从这个表中再次查询出记录,然后再做删除或者修改操作。

②修改后的语句:delete from student1 where id not in (select t.id from(select id from student1 group by xuehao,name,kecheng,mingcheng,fenshu) t);

此时会提示报错 ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sqlmianshi.student1.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这个错误是由于 MySQL 的新版本中默认开启了ONLY_FULL_GROUP_BY模式(我的MySQL是8.0)，所以在 GROUP BY 语句中的 SELECT 列表中，只能包含分组或聚合函数，不能包含其他列。

解决方法:关闭only_full_group_by的规则校验

SELECT @@sql_mode;(用这个查询你的MySQL的规则)

set @@GLOBAL.sql_mode='';

set @@sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

 但是此方法只是改变了MySQL的全局设置,只对新的数据库生效,所以还要去表本身的数据库执行set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

解决上述问题后,再输入语句: delete from student1 where id not in (select t.id from(select id from student1 group by xuehao,name,kecheng,mingcheng,fenshu) t);

 

5. 面试题：怎么把这样一个表儿
year month amount
1991 1        1.1
1991 2     1.2
1991 3     1.3
1991 4     1.4
1992 1     2.1
1992 2     2.2
1992 3     2.3
1992 4     2.4

查成这样一个结果
year m1   m2   m3   m4
1991 1.1  1.2  1.3  1.4
1992 2.1  2.2  2.3  2.4

提示：创建视图
create view m1 as select * from biao wuere month =1;

先创建一个表

分别创建视图m1,m2,m3,m4

 create view m1 as select * from times where month=1;

select year, 
(select amount from times m where month=1 and m.year=times.year) m1,
(select amount from times m where month=2 and m.year=times.year) m2,
(select amount from times m where month=3 and m.year=times.year) m3,
(select amount from times m where month=4 and m.year=times.year) m4
from times group by year;

用year分组,用amount列查询,用m数值进行分类