MySQL单表操作面试题（朋友的SQL面试题以及自己的）

有兴趣的小伙伴可以加我一起讨论QQ：304808680，加好友验证：我的心事！ 

十分欢迎你们的光临，我也十分的喜欢讨论问题

1. 求所有学生科目分数高于80分的学生姓名及成绩。

结果：

select name,chengji from t_table group by name having min(chengji) > 80 

2.按年月分别展示数据

select year,
sum(case when month = 1 then mon else 0 end) as m1,
sum(case when month = 2 then mon else 0 end) as m2,
sum(case when month = 3 then mon else 0 end) as m3,
sum(case when month = 4 then mon else 0 end) as m4
from t_table group by year

3.删除表中重复的数据，并且保留一条

delete from t_table where name in (
    select name from t_table group by name having count(name)>1
) and id not in(
    select id from t_table group by name having count(name)>1
)



SELECT * from user_coupon where coupon_id = '1a0ea49011e5432ca53d51cc1eb1ea4c' 
AND user_id in (
    select user_id from user_coupon WHERE coupon_id = '1a0ea49011e5432ca53d51cc1eb1ea4c' group by user_id having count(user_id)>1
) and user_coupon_id not in(
    select MIN(user_coupon_id) from user_coupon WHERE coupon_id = '1a0ea49011e5432ca53d51cc1eb1ea4c' group by user_id having count(user_id)>1
);

4.对一个班的同学分组，并且对每个班的学生进行排序

 

SELECT (@i := CASE WHEN @pre_times=banji then @i + 1 ELSE 1 END )rownum,b.*,@pre_times:=banji as newBanji from
(SELECT id,name,banji FROM t_banjiName  GROUP BY banji,name ) b,
(SELECT @i := 0,@pre_times :='')as a

在mysql 8.x中可以使用下面这段代码实现上面的功能

SELECT *,row_number() over(partition by c_id order by id ) num FROM test;

5. 根据提供的四张表查询数据

解答：

1. 

select s.sno from sc as s inner join course as c on s.cno = c.cno 
where c.cname = "课程1" and s.score > (select max(s.score) from sc as s 
    inner join course as c on s.cno = c.cno and c.cname = "课程2"
)

2.

select * from student as st inner join sc as s on st.sno = s.sno 
inner jion course as c on s.cno = c.cno and c.cname = "课程1" or c.cname = "课程2" 
group by st.sno having count(st.sno) > 1

3.

我目前的立即和第一题的理解是一样的，有可能我没理解清楚，目前我是按照第一个SQL执行的

 

6.Mysql使用联合索引

联合索引的创建格式：ALTER table user add INDEX `sindex` (`aaa`,`bbb`,`ccc`)
#这条语句列出来只想说明 mysql没有那么笨，where里面的条件顺序在查询之前会被mysql自动优化
EXPLAIN select * from user where  ccc=4628 and bbb=315 and aaa=70;

#a用到索引，b没有用，所以c是没有用到索引效果的
EXPLAIN select * from user where aaa=70 and ccc=4628;

#a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引
EXPLAIN select * from user where aaa = 3 and bbb > 315 and ccc=4628;
EXPLAIN select * from user where bbb > 315 and aaa = 3 and ccc=4628;

#因为a索引没有使用，所以这里 bc都没有用上索引效果
EXPLAIN select * from user where bbb=3 and ccc=4;

#a索引失效了  b没有使用，c没有使用
EXPLAIN select * from user where aaa=44 and bbb<777 and ccc=9999;

#a用到了索引，b在结果排序中也用到了索引的效果，a下面任意一段的b是排好序的
EXPLAIN select * from user where aaa=3 order by bbb;

#a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort
EXPLAIN select * from user where aaa=3 and bbb=222 order by ccc;

#b没有用到索引，排序中a也没有发挥索引效果
EXPLAIN select * from user where bbb=3 order by aaa;

#总结 
1. 使用联合索引查询必须存在建立索引最左边的的那个字段，不然会导致直接索引失效
2. 使用联合索引查询时不会因为字段的顺序而导致索引失效
3. 使用联合索引查询时比较符也会使索引失效，失效顺序为 最左边的先失效会导致右边的全部都失效，然后以此类推
4. 使用联合索引查询数据并且排序时查询下面的查询最优
EXPLAIN select * from user where aaa=3 order by bbb;
EXPLAIN select * from user where aaa=3 and bbb=222 order by ccc;