mysql多选基础题_MySQL基础知识—习题练习

要想学会MySQL这项技能，只有在不断的实践练习中才能牢固掌握知识，光看不练是假把式！所以，请先在数据库中创建以下练习需要使用的4个表，一边进行复习，一边进行练习：

一、简单查询习题练习

1、查找姓‘猴’的学生名单

SELECT 学号,姓名

FROM student

WHERE 姓名 LIKE '猴%' ;

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

SELECT 学号,姓名

FROM student

WHERE 姓名 LIKE '%猴' ;

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

SELECT 学号,姓名

FROM student

WHERE 姓名 LIKE '%猴%' ;

4、查询姓‘王’的学生名单，并且名字是3个字的

SELECT 学号,姓名

FROM student

WHERE 姓名 LIKE '王__' ;-- 王后面接了两个下划线

5、查询姓‘孟’的老师的个数

SELECT 教师姓名,COUNT(教师号) AS 教师个数

FROM teacher

WHERE 教师姓名 LIKE '孟%' ;

二、汇总查询习题练习

1、查询课程号为‘0002’的总成绩

SELECT 课程号,SUM(成绩) AS 总成绩

FROM score

WHERE 课程号 = '0002' ;

2、查询选了课程的学生人数

SELECT COUNT(DISTINCT 学号) AS 学生人数

FROM score

WHERE 课程号 IS NOT NULL;-- 去除课程号为空值的数据，即学生没有选课的数据本表为演示效果，增加0004学号的数据，课程号和成绩为空值

3、查询各个课程成绩最高和最低的分

SELECT 课程号,MAX(成绩) AS 最高分,MIN(成绩) AS 最低分

FROM score

GROUP BY 课程号;

4、查询每门课程选修的学生人数

SELECT 课程号,COUNT(学号) AS 学生人数

FROM score

GROUP BY 课程号;

5、查询男生、女生人数

SELECT 性别,COUNT(姓名) AS 学生人数

FROM student

GROUP BY 性别;

6、查询平均成绩大于60分学生的学号和平均成绩(平均成绩保留两位小数)

SELECT 学号,ROUND(AVG(成绩),2) AS 平均成绩

FROM score

GROUP BY 学号

HAVING AVG(成绩) > 60;

7、查询至少选修两门课程的学生学号

SELECT 学号,COUNT(课程号) AS 选修课程数

FROM score

GROUP BY 学号

HAVING COUNT(课程号) >= 2;

8、查询同名同姓学生名单并统计同名人数

SELECT 姓名,COUNT(姓名) AS 同名人数

FROM student

GROUP BY 姓名

HAVING COUNT(姓名) >= 2;

9、查询不及格的课程并按课程号从大到小排列

SELECT 课程号,成绩

FROM score

WHERE 成绩 < 60

ORDER BY 课程号 DESC;

10、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

SELECT 课程号,ROUND(AVG(成绩),2) AS 平均成绩

FROM score

GROUP BY 课程号

ORDER BY 平均成绩 ASC ,课程号 DESC;

11、检索课程编号为“0003”且分数小于60的学生学号，结果按按分数降序排列

SELECT 学号,成绩

FROM score

WHERE 课程号 = '0003' and 成绩 < 60;

ORDER BY 成绩 DESC;该表为演示效果，将最后一行0003学号的成绩由80改为58

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

SELECT 课程号,COUNT(学号) AS 选修人数

FROM score

GROUP BY 课程号

HAVING COUNT(学号) > 2

ORDER BY 选修人数 DESC , 课程号 ASC;-- 升序是默认的，课程号后面的ASC可以不写

13、查询两门以上不及格课程的同学的学号及其平均成绩

SELECT 学号,AVG(成绩) AS 平均成绩

FROM score

WHERE 成绩 < 60

GROUP BY 学号

HAVING COUNT(课程号) >= 2;该表为演示效果，将最后一行0003学号的成绩由80改为58

三、复杂查询习题练习

1、查询学生选修的所有课程成绩小于60分的学生的学号、姓名

SELECT 学号,姓名

FROM student

WHERE 学号 IN (

SELECT 学号

FROM score

GROUP BY 学号

HAVING MAX(成绩) < 60

);该表为演示效果，score表有做修改

2、查询没有学全所有课的学生的学号、姓名

SELECT 学号,姓名

FROM student

WHERE 学号 IN (

SELECT 学号

FROM score

GROUP BY 学号

HAVING COUNT(学号) < (SELECT COUNT(课程号) FROM course)

);

3、查询出只选修了两门课程的全部学生的学号和姓名

SELECT 学号,姓名

FROM student

WHERE 学号 IN (

SELECT 学号

FROM score

GROUP BY 学号

HAVING COUNT(学号) =2

);

4、查询1990年出生的学生名单

SELECT 学号,姓名

FROM student

WHERE YEAR(出生日期) = '1990';

四、多表查询习题练习

1、查询所有学生的学号、姓名、选课、总成绩

SELECT a.学号,a.姓名,COUNT(b.课程号) AS 选课数,SUM(b.成绩) AS 总成绩

FROM student AS a LEFT JOIN score AS b

ON a.学号 = b.学号

GROUP BY a.学号;

2、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

SELECT a.学号,a.姓名, ROUND(AVG(b.成绩),2) AS 平均成绩

FROM student AS a LEFT JOIN score AS b

ON a.学号 = b.学号

GROUP BY a.学号

HAVING AVG(b.成绩) > 85;

3、查询学生的选课情况：学号，姓名，课程号，课程名称

SELECT a.学号,a.姓名, b.课程号,c.课程名称

FROM student AS a INNER JOIN score AS b

ON a.学号 = b.学号

INNER JOIN course AS c

ON b.课程号 = c.课程号;

4、查询每门课程的及格人数和不及格人数

SELECT 课程号,

SUM(CASE

WHEN 成绩>=60

THEN 1

ELSE 0

END) AS 及格人数,

SUM(CASE

WHEN 成绩<60

THEN 1

ELSE 0

END ) AS 不及格人数

FROM score

GROUP BY 课程号;

5、使用分段[100-85]、[85-70]、[70-60]，[<60]来统计各科成绩，分别统计：各分段人数，课程号和课程名称

SELECT a.课程号,b.课程名称,

SUM(CASE

WHEN 85< 成绩 AND 成绩 <= 100

THEN 1 ELSE 0 END) AS '[100-85]',

SUM(CASE

WHEN 70< 成绩 AND 成绩 <= 85

THEN 1 ELSE 0 END) AS '[85-70]',

SUM(CASE

WHEN 60<= 成绩 AND 成绩 <= 70

THEN 1 ELSE 0 END) AS '[70-60]',

SUM(CASE

WHEN 成绩 <60

THEN 1 ELSE 0 END) AS '[<60]'

FROM score AS a RIGHT JOIN course AS b

ON a.课程号 = b.课程号

GROUP BY a.课程号;

6、查询课编号为0003且课程成绩在80分以上的学生的学号、姓名和成绩

SELECT a.学号,a.姓名,b.成绩

FROM student AS a INNER JOIN score AS b

ON a.学号 = b.学号

WHERE b.课程号 = '0003' AND b.成绩 >80;

7、通过下图的查询结果，写出查询代码

参考代码为：

SELECT 学号,

MAX(CASE 课程号

WHEN '0001'

THEN 成绩 ELSE 0 END)

AS 课程号0001,

MAX(CASE 课程号

WHEN '0002'

THEN 成绩 ELSE 0 END)

AS 课程号0002,

MAX(CASE 课程号

WHEN '0003'

THEN 成绩 ELSE 0 END)

AS 课程号0003

FROM score

GROUP BY 学号