进阶8 子查询-----作业讲解

（1）查询每个专业的学生人数

SELECT COUNT(*), majorid
FROM student
GROUP BY majorid;

（2）查询参加考试的学生中，每个学生的平均分、最高分

SELECT AVG(score), MAX(score), studentno
FROM result
GROUP BY studentno;

（3）查询姓张的每个学生的最低分大于60的学号、姓名

注意：left join，以因为有可能有学生没有成绩。stuent表为主表

SELECT studentno, studentname, MIN(score)
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno
WHERE studentname LIKE ‘张%’
GROUP BY studentno
HAVING MIN(score) > 60;

（4）查询生日在’1988-1-1’后的学生姓名、专业名称

注意：borndate > ‘1988-1-1’。例如：borndate为’1989-1-1’

SELECT studentname, majorname
FROM student s
INNER JOIN major m
ON m.majorid = s.majorid
WHERE DATEDIFF(borndate, ‘1988-1-1’)>0;

（5）查询每个专业的男生人数和女生人数分别是多少

方法1

SELECT COUNT() 个数, majorid, sex
FROM student
GROUP BY majorid, sex;
/
输出结果：
个数 sex majorid
3 女 1
1 女 2
2 女 3
5 男 1
2 男 2
2 男 3
*/

方法2【效果更好】

SELECT majorid, # step3
(SELECT COUNT() FROM student WHERE sex=‘男’ AND majorid=s.majorid) 男,
(SELECT COUNT() FROM student WHERE sex=‘女’ AND majorid=s.majorid) 女
FROM student # step1
GROUP BY majorid; # step2
/*
输出结果：
majorid 男 女
1 5 3
2 2 1
3 2 2
*/

（6）查询专业和张翠山一样的学生的最低分

step1:查询张翠山的专业编号

SELECT majorid
FROM student
WHERE studentname=‘张翠山’;
/*
输出结果：
majorid
1
*/

step2：查询编号=step1的所有学生编号

SELECT studentno
FROM student
WHERE majorid = (
SELECT majorid
FROM student
WHERE studentname=‘张翠山’
);
/*
输出结果：
studentno
s001
s002
s004
s012
s014
*/

step2：查询最低分

SELECT MIN(score)
FROM result
WHERE studentno IN (
SELECT studentno
FROM student
WHERE majorid = (
SELECT majorid
FROM student
WHERE studentname=‘张翠山’
)
);
/*
输出结果：
min(score)
70
*/

（7）查询大于60分的学生的姓名、密码、专业名

注意：有些学生没有分数（null），故用左连接

SELECT studentname, loginpwd, majorname
FROM student s
INNER JOIN major m ON s.majorid = m.majorid
LEFT JOIN result r ON s.studentno = r.studentno
WHERE r.score > 60;

（8）按邮箱位数分组，查询每组的学生个数

SELECT COUNT(*)
FROM student
GROUP BY LENGTH(email);

（9）查询学生名、专业名、分数【三表连接】

注意：有些学生没有分数（null），故用左连接

SELECT studentname, majorname, score
FROM student s
INNER JOIN major m ON s.majorid = m.majorid
LEFT JOIN result r ON s.studentno = r.studentno;
/*
输出结果：
studentname score majorname
张翠山 70 javaee
赵敏 null android
杨不悔 null htmls
*/

（10）查询哪个专业没有学生，分别用左连接和右连接实现

方法1：左连接【在major表中，id为主键】【主键列没有null值】【主键列不能重复】

SELECT majorname, studentno
FROM major m
LEFT JOIN student s
ON m.majorid = s.majorid
WHERE s.studentno IS NULL;

方法2：右连接

SELECT majorname, studentno
FROM student s
RIGHT JOIN major m ON m.majorid = s.majorid
WHERE s.studentno IS NULL;

（11）查询没有成绩的学生人数

step1:没有成绩的学生【左连接】【在result表中，id为主键】

SELECT s.*, r.id
FROM student s
LEFT FROM result r ON s.studentno = r.studentno
WHERE r.id IS NULL;

step2

SELECT COUNT(*)
FROM student s
LEFT JOIN result r ON s.studentno = r.studentno
WHERE r.id IS NULL;