mysql至少选修了两门课程_MySQL学习-6

练习一：简单查询

1.查询姓“猴”的学生名单

SELECT * FROM student

WHERE `姓名` LIKE '猴%';结果图-1

2.查询姓名中最后一个字是“猴”的学生名单

SELECT * FROM student

WHERE `姓名` LIKE '%猴';结果图-2

3.查询姓名中带“猴”的学生名单

SELECT * FROM student

WHERE `姓名` LIKE '%猴%';结果图-3

4.查询姓“孟”老师的个数

SELECT * FROM teacher

WHERE `教师姓名` LIKE '孟%';结果图-4

练习二：汇总分析

1.查询课程表编号为“002”的总成绩

SELECT SUM(成绩) FROM score

WHERE `课程号`=0002;结果图-1

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

SELECT DISTINCT COUNT(DISTINCT `学号`) FROM score;结果图-2

3.查询每科成绩最高和最低分

SELECT `课程号`,MAX(`成绩`),MIN(`成绩`) FROM score

GROUP BY `课程号`;结果图-3

4.查询每门课程被选修的学生数

SELECT `课程号`,COUNT(DISTINCT `学号`) FROM score

GROUP BY `课程号`;结果图-4

5.查询男生、女生人数

SELECT `性别`,COUNT(`姓名`) FROM student

GROUP BY `性别`;结果图-5

6.查询平均成绩大于60分学生的学号和平均成绩

SELECT `学号`,AVG(成绩) AS "平均成绩" FROM score

GROUP BY `学号`

HAVING AVG(成绩) >60;结果图-6

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

SELECT `学号`,COUNT(`课程号`) AS "课程数" FROM score

GROUP BY `学号`

HAVING COUNT(`课程号`) >1;结果图-7

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

SELECT COUNT(`姓名`) FROM student

GROUP BY `姓名`

HAVING COUNT(`姓名`) >1;结果图-8

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

SELECT `课程号` FROM score

WHERE `成绩`<60

ORDER BY `课程号` DESC;结果图-9(全都及格了！好厉害哦！)

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

SELECT `课程号`,AVG(`成绩`) FROM score

GROUP BY `课程号`

ORDER BY AVG(`成绩`) ASC,`课程号` DESC;结果图-10

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

SELECT `学号`,`成绩`FROM score

WHERE `课程号`=0004 AND `成绩`<60

ORDER BY `成绩` DESC;结果图-11(嗯，没人选，这课程可真完蛋)

12.统计每门课程的学生选修人数(超过2人的课程才统计)

要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

SELECT `课程号`,COUNT(`学号`)FROM score

GROUP BY `课程号`

HAVING COUNT(`学号`)>2

ORDER BY COUNT(`学号`) DESC,`课程号` ASC;结果图-12

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

SELECT `学号`,AVG(`成绩`)FROM score

WHERE `成绩`<60

GROUP BY `学号`

HAVING COUNT(`课程号`)>1;结果图-13不挂科！！！

练习三：复杂查询

1.查询所有课程成绩小于80分学生的学号、姓名

SELECT `学号`,`姓名` FROM student

WHERE `学号` in (SELECT `学号` FROM score WHERE `成绩`<80 GROUP BY `学号` );结果图-1

2.查询没有学全所有课程的学生的学号，姓名

SELECT `学号`,`姓名` FROM student

WHERE `学号` in

(

SELECT `学号` FROM score

GROUP BY `学号`

HAVING COUNT(`课程号`)<

(

SELECT COUNT(`课程号`) FROM course

)

);结果图-2

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

SELECT `学号`,`姓名` FROM student

WHERE `学号` in

(

SELECT `学号` FROM score

GROUP BY `学号`

HAVING COUNT(`课程号`)=2

);结果图-3

4.1990年出生的学生名单

SELECT * FROM student

WHERE YEAR(`出生日期`)=1990;结果图-4

5.按课程号分组取成绩最大值所在行的数据

SELECT * FROM score AS a

WHERE `成绩`=

(

SELECT MAX(`成绩`) FROM score AS b

WHERE b.`课程号` =a.`课程号`

GROUP BY `课程号`

);结果图-5

6.按课程号分组取成绩最小值所在行的数据

SELECT * FROM score AS a

WHERE `成绩`=

(

SELECT MIN(`成绩`) FROM score AS b

WHERE b.`课程号` =a.`课程号`

GROUP BY `课程号`

);结果图-6

7.查询每科成绩前两名的记录

(SELECT * FROM score WHERE `课程号`=0001 ORDER BY `成绩` DESC LIMIT 2)

UNION ALL

(SELECT * FROM score WHERE `课程号`=0002 ORDER BY `成绩` DESC LIMIT 2)

UNION ALL

(SELECT * FROM score WHERE `课程号`=0003 ORDER BY `成绩` DESC LIMIT 2);结果图-7

练习四：多表查询

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

SELECT a.`学号`,a.`姓名`,COUNT(b.`课程号`),SUM(b.`成绩`)

FROM student AS a

LEFT JOIN

score as b

ON a.`学号`=b.`学号`

GROUP BY a.`学号`;结果图-1

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

SELECT a.`学号`,a.`姓名`,AVG(b.`成绩`)

FROM student AS a

LEFT JOIN

score as b

ON a.`学号`=b.`学号`

GROUP BY a.`学号`

HAVING AVG(b.`成绩`) >85;结果图-2

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

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

FROM student AS a

LEFT JOIN

score as b

ON a.`学号`=b.`学号`

INNER JOIN course AS c

ON b.`课程号`=c.`课程号`;结果图-3

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 `课程号`;结果图-4

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

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

SUM(CASE WHEN `成绩`BETWEEN 100 AND 85 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 LEFT JOIN course AS b ON a.`课程号`=b.`课程号`

GROUP BY a.`课程号`,b.`课程名称`;结果图-5

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

SELECT a.`学号`,b.`姓名` FROM score AS a LEFT JOIN student as b ON a.`学号`=b.`学号`

WHERE a.`课程号`=0003 AND a.`成绩`>80;结果图-6

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 `学号`结果图-7

笔记一：提高SQL效率

1.select字句中尽量避免使用*

2.where子句比较符号左侧避免函数，可换到等号右边去

3.尽量避免使用in和not in，用between代替

4.尽量避免使用or，用union代替

5.使用limit子句限制返回的数据行数