mysql基础----练习

#练习题
#1.查询每个专业的学生人数
SELECT majorid,COUNT(*)
FROM student 
GROUP BY majorid;
#2.查询参加考试的学生中，每个学生的平均分、最高分
SELECT AVG(`score`),MAX(`score`),`studentno`
FROM `result`
GROUP BY `studentno`;
#3.查询姓张的每个学生最低分大于60的学号、姓名
#(1)查询每个最低分学生的学号
SELECT `studentno`
FROM `result`
HAVING MIN(`score`);
#（2）查询满足条件（1）的学号和姓名
SELECT `studentno`,`studentname`
FROM `student`
WHERE `studentno`=(
SELECT `studentno`
FROM `result`
HAVING MIN(`score`)
)AND `studentname` LIKE '张%';

#正解
SELECT s.`studentno`,s.`studentname`,MIN(`score`)
FROM `student` s
JOIN `result` r
ON s.`studentno`=r.`studentno`
WHERE s.`studentname` LIKE '张%'
GROUP BY s.`studentno`
HAVING MIN(`score`)>60;


#4.查询生日在“1988-1-1”后的学生姓名、专业名称
SELECT `studentname`,`majorname`,`borndate`
FROM `student` s
JOIN `major` m
ON s.`majorid`=m.`majorid`
WHERE `borndate`>'1988-1-1';

#解法二
SELECT `studentname`,`majorname`,`borndate`
FROM `student` s
JOIN `major` m
ON s.`majorid`=m.`majorid`
WHERE DATEDIFF(borndate,'1988-1-1')>0;

#5.查询每个专业的男生人数和女生人数分别是多少
SELECT COUNT(*),`sex`,`majorid`
FROM `student`
GROUP BY `sex`,`majorid`;
#解法二
SELECT `majorid`,
(SELECT COUNT(*) FROM `student` WHERE `sex`='男' AND `majorid`=s.`majorid`) 男,
(SELECT COUNT(*) FROM `student` WHERE `sex`='女' AND `majorid`=s.`majorid`) 女
FROM `student` s
GROUP BY `majorid`;
#6.查询专业和张翠山一样的学生的最低分
#（1）查询张翠山的专业
SELECT `majorid`
FROM `student`
WHERE `studentname`='张翠山';
#(2)查询编号=（1）的所有学生编号
SELECT `studentno`
FROM `student`
WHERE `majorid`=(
SELECT `majorid`
FROM `student`
WHERE `studentname`='张翠山'
);

#(3)查询满足条件（2）的学生的最低分
SELECT MIN(`score`)
FROM `result`
WHERE `studentno` IN(
SELECT `studentno`
FROM `student`
WHERE `majorid`=(
SELECT `majorid`
FROM `student`
WHERE `studentname`='张翠山'
)
);


#解法二(2)查询满足条件（2）的学生的最低分
SELECT MIN(`score`) ,`majorid`
FROM `result` r
JOIN `student` s
ON r.`studentno`=s.`studentno`
WHERE `majorid`=(
SELECT `majorid`
FROM `student`
WHERE `studentname`='张翠山'
);
#7.查询大于60分的学生的姓名、密码、专业名
SELECT `studentname`,`loginpwd`,`majorname`
FROM `student` s
JOIN `major` m
ON s.`majorid`=m.`majorid`
JOIN `result` r
ON s.`studentno`=r.`studentno`
WHERE  `score`>60;

#8.按邮箱位数分组，查询每组的学生个数
SELECT COUNT(*)
FROM `student`
GROUP BY LENGTH(`email`);
#9.查询学生姓名、专业名、分数
SELECT `studentname`,`majorname`,`score`
FROM `student` s
JOIN `major` m
ON s.`majorid`=m.`majorid`
JOIN `result` r
ON s.`studentno`=r.`studentno`;
#10.查询哪个专业没有学生，分别用左连接和右连接实现
#左连接
SELECT  m.`majorid`,m.`majorname`,s.`studentname`
FROM `major` m
LEFT JOIN `student` s ON m.`majorid`=s.`majorid`
WHERE s.`studentno` IS NULL;

#右连接
SELECT  m.`majorid`,m.`majorname`,s.`studentname`
FROM `major` m
RIGHT JOIN `student` s ON m.`majorid`=s.`majorid`
WHERE s.`studentno` IS NULL;



#11.查询没有成绩的学生人数
#(1)查询没有成绩的学生
SELECT s.*,r.id
FROM `student` s
LEFT JOIN `result` r
ON s.`studentno`=r.`studentno`
WHERE r.`id`IS NULL;
#(2)直接在(1)的基础上进行cout
SELECT COUNT(*)
FROM `student` s
LEFT JOIN `result` r
ON s.`studentno`=r.`studentno`
WHERE r.`id`IS NULL;