MySQL面试题 模糊查询like、汇总count、分组group by

4.面试题——模糊查询 like

“%” 百分号通配符: 表示任何字符出现任意次数 (可以是0次)。
“" 下划线通配符:表示只能匹配单个字符,不能多也不能少,就是一个字符。当然，也可以like "陈___”，数量不限。
like操作符:LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配进行比较；但如果like后面没出现通配符，则在SQL执行优化时将 like 默认为 “=”执行

4.1查询姓‘猴’的学生名单（模糊查询 like）

SELECT * FROM student WHERE stu_name LIKE '猴%';

4.2查询姓名最后一个字是‘猴’的学生名单

SELECT * FROM student WHERE stu_name LIKE '%猴';

4.3查询姓名中带‘猴’的学生名单

SELECT * FROM student WHERE stu_name LIKE '%猴%';

4.4 查询姓‘孟’老师的个数
一： 查询姓‘孟’的老师信息

SELECT * FROM teacher WHERE teacher_name LIKE '孟%';

二： 以教师姓名分组

SELECT COUNT(teacher_name) FROM teacher;

三： 以教师姓名分组并查询出姓‘孟’的教师

SELECT COUNT(teacher_name) FROM teacher WHERE teacher_name LIKE '孟%';

5.面试题——汇总 count

5.1查询课程编号为‘k2’的总成绩
1）查询成绩表总信息

SELECT * FROM score;

2）查询课程为‘k2’的信息

SELECT * FROM score WHERE course_id='k2';

3）查询课程为‘k2’的总成绩

SELECT SUM(score) FROM score WHERE course_id='k2';

5.2查询选了课程的学生人数
1）查询成绩表

SELECT * FROM score;

2）以学号为分组查询人数

SELECT COUNT(stu_id) FROM score;

3）以学号为分组查询人数，去掉重复值 distinct

SELECT COUNT(DISTINCT stu_id) FROM score;

6.面试题——分组查询 group by

6.1查询各科成绩的最高分和最低分
最大值 max（） 最小值min（）

SELECT course_id,MAX(score),MIN(score) FROM score GROUP BY course_id;

SELECT course_id 课程名称,MAX(score) 最高分,MIN(score) 最低分 FROM score GROUP BY course_id;

6.2查询课程的热门程度（被学生选修的数量）

SELECT course_id,COUNT(stu_id) FROM score GROUP BY course_id;

6.3查询男生、女生的人数

SELECT * FROM student;
SELECT gender,COUNT(gender) FROM student WHERE gender='男';

SELECT gender 性别,COUNT(*) 人数 FROM student GROUP BY gender;
SELECT gender 性别,COUNT(gender) 人数 FROM student GROUP BY gender;

6.4查询平均成绩大于60的学生学号和平均成绩

SELECT stu_id,AVG(score) 平均分 FROM score GROUP BY stu_id ;

SELECT stu_id,AVG(score) 平均分 FROM score  GROUP BY stu_id HAVING 平均分>60;

6.5查询至少选修两门课程的学生学号
HAVING子句通常与GROUP BY子句一起使用，以根据指定的条件过滤分组。如果省略GROUP BY子句，则HAVING子句的行为与WHERE子句类似。
HAVING子句将过滤条件应用于每组分行，而WHERE子句将过滤条件应用于每个单独的行。
1）查询各学生的选修课程数量

SELECT stu_id,COUNT(course_id) FROM score GROUP BY stu_id;

2）查询学生选修课程数量大于2的学生学号

SELECT stu_id,COUNT(course_id) 选修课程数量 FROM score GROUP BY stu_id HAVING 选修课程数量>2 ;

6.6查询同名同姓的学生信息并统计同名同姓的人数
1）按名字进行分组，并统计数量

SELECT stu_name,COUNT(stu_name) FROM student GROUP BY stu_name;

2）按名字分组的数量大于1，说明有人重名

SELECT stu_name,COUNT(stu_name) 人数 FROM student GROUP BY stu_name HAVING 人数>1;

6.7查询低于80的课程并按课程号从大到小排列
1）小于80分 SELECT * FROM score WHERE score<=80;

2）小于80分，以课程编号降序排列 order by
Order by 对查询结果排序 ASC 升序（默认） DESC 降序

SELECT * FROM score WHERE score<=80 ORDER BY course_id DESC;

6.8查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
1）以课程进行分组，查询平均分

SELECT course_id,AVG(score) FROM score GROUP BY course_id;

2）查询每门课的平均分并以平均分降序排列

SELECT course_id,AVG(score) 平均分 FROM score GROUP BY course_id ORDER BY 平均分 DESC;

3）查询每门课的平均分，以平均分的升序和课程编号的降序排列

SELECT course_id,AVG(score) 平均分 FROM score GROUP BY course_id ORDER BY 平均分 ASC,course_id DESC;

6.9检索课程编号为“k2”且分数大于70的学生学号，结果按按分数降序排列

SELECT * FROM score WHERE course_id='k2' ;
SELECT * FROM score WHERE course_id='k2' AND score>70 ;
SELECT * FROM score WHERE course_id='k2' AND score>70 ORDER BY score DESC;

6.10统计每门课程的学生选修人数(超过2人的课程才统计)
要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

SELECT course_id,COUNT(stu_id) 选修人数 FROM score GROUP BY course_id;

SELECT course_id,COUNT(stu_id) 选修人数 FROM score GROUP BY course_id ORDER BY 选修人数 DESC ,course_id ASC;

6.11查询两门以上到70课程的同学的学号及其平均成绩
1）查询学生到70以上的课程数量

SELECT stu_id,COUNT(score) 到70数量 FROM score WHERE score>70 GROUP BY stu_id ;

2）显示学生的学号和平均分，用where条件去掉成绩低于70的学生信息，用having条件去筛选两门以下课程信息

SELECT stu_id,AVG(score) 平均分 FROM score WHERE score>70 GROUP BY stu_id HAVING COUNT(course_id)>2

6.12查询学生的总成绩并进行排名

SELECT stu_id,SUM(score) 总成绩 FROM score GROUP BY stu_id ORDER BY 总成绩;

6.13查询平均成绩大于70分的学生的学号和平均成绩

SELECT stu_id,AVG(score) 平均分 FROM score GROUP BY stu_id HAVING 平均分>70;