MySQL进阶练习

建表语句以及原文章MySQL进阶练习

1、**查询”01”课程比”02”课程成绩高的学生的信息及课程分数;

SELECT A.*,B.score '02课程分数',C.score '01课程分数'
FROM student A JOIN (sc B,sc C)
ON A.sno = b.sno AND A.sno = C.sno AND b.cno = '02' AND c.cno = '01'
WHERE b.score > c.score;

2.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩;

SELECT s.sno,s.sname,AVG(score)
FROM student s JOIN sc 
ON s.sno = sc.sno
GROUP BY s.sno
HAVING AVG(score) >= 60;

3.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩;

SELECT s.sno,s.sname,COUNT(*),SUM(score)
FROM student s JOIN sc
ON s.sno = sc.sno
GROUP BY s.sno;

4.查询”李”姓老师的数量;

SELECT COUNT(*) FROM teacher 
WHERE tname LIKE '李%';

5.查询学过”张三”老师授课的同学的信息；

SELECT a.* FROM student a
JOIN sc 
ON sc.sno = a.sno
WHERE sc.cno = (
	SELECT cno FROM course c
	JOIN teacher t
	ON c.tno = t.tno
	WHERE t.tname = '张三'
)

6.**查询没学过”张三”老师授课的同学的信息;

SELECT * FROM student a
WHERE a.sno NOT IN(
	SELECT DISTINCT sc.sno FROM sc
	WHERE sc.cno = (
		SELECT cno FROM course c
		JOIN teacher t
		ON c.tno = t.tno
		WHERE t.tname = '张三'	
	)
)
第二种解法
SELECT * FROM STUDENT 
WHERE SNO NOT IN 
(	SELECT DISTINCT B.SNO
	FROM TEACHER A,SC B,COURSE C
	WHERE A.TNO=C.TNO AND B.CNO=C.CNO AND A.TNAME='张三');

7.查询学过编号为”01”并且也学过编号为”02”的课程的同学的信息;

SELECT A.*
FROM student A JOIN (sc B,sc C)
ON A.sno = b.sno AND A.sno = C.sno AND b.cno = '02' AND c.cno = '01';

SELECT * FROM STUDENT
WHERE SNO IN (
SELECT A.SNO FROM SC A ,SC B 
WHERE A.SNO=B.SNO AND A.CNO='01' AND B.CNO='02');

8.查询学过编号为”01”但是没有学过编号为”02”的课程的同学的信息;

SELECT * FROM student
WHERE sno IN(
	SELECT sno FROM sc
	WHERE cno = '01'
) AND sno NOT IN (
	SELECT sno FROM sc
	WHERE cno = '02'
)

9.查询没有学全所有课程的同学的信息;

SELECT * FROM student
WHERE sno NOT IN(
	SELECT sno FROM sc
	GROUP BY sno 
	HAVING COUNT(*) = 3
);

10、查询至少有一门课与学号为”01”的同学所学相同的同学的信息;

SELECT DISTINCT s.* FROM student s
JOIN sc ON s.sno=sc.`SNO`
WHERE sc.cno IN (
	SELECT cno FROM sc
	WHERE sno = '01'
) AND s.sno <> '01';

**11、查询和”01”号的同学学习的课程完全相同的其他同学的信息 ;
*课程完全相同需要两点进行保证，1.把相同的都列出来、2.计数，若计数相同则可以

SELECT * FROM STUDENT
WHERE SNO IN (
SELECT A.SNO FROM STUDENT A  INNER JOIN SC B ON A.SNO=B.SNO
WHERE A.SNO<>01 AND B.CNO IN (SELECT CNO FROM SC WHERE SNO=01)
GROUP BY A.SNO HAVING COUNT(B.CNO)=(SELECT COUNT(*) FROM SC WHERE SNO=01)
); 

12、查询没学过”张三”老师讲授的任一门课程的学生姓名；

SELECT sname FROM student
WHERE sno NOT IN (
	SELECT DISTINCT sno FROM sc
	JOIN (course c,teacher t)
	ON sc.cno = c.cno AND c.tno = t.tno
	WHERE t.tname = '张三'
);

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

SELECT s.*,AVG(score) FROM student s
JOIN sc ON s.sno = sc.sno
WHERE s.sno IN (
	SELECT sno FROM sc
	WHERE score < 60
	GROUP BY sno
	HAVING COUNT(score) >= 2
) 
GROUP BY s.sno;

这种写法，只统计了没有及格的分数的平均分。
SELECT A.SNO,A.SNAME,AVG(B.SCORE) AVG_SCORE
FROM STUDENT A,SC B 
WHERE A.SNO=B.SNO AND B.SCORE<60
GROUP BY A.SNO 
HAVING  COUNT(*)>=2

14、检索”01”课程分数小于60，按分数降序排列的学生信息；

SELECT s.*,sc.score FROM student s
JOIN sc ON sc.sno = s.sno 
WHERE sc.cno = '01' AND sc.`SCORE`< 60
ORDER BY sc.score DESC;

**15、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩;

SELECT SNO,GROUP_CONCAT(CNO),GROUP_CONCAT(SCORE),AVG(SCORE) FROM SC
GROUP BY SNO ORDER BY AVG(SCORE) DESC

**16、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程
name，最高分，最低分，平均分，及格率;

SELECT CNO,CNAME,
(SELECT MAX(SCORE) FROM SC WHERE CNO=A.CNO)MAX_SCORE,
(SELECT MIN(SCORE) FROM SC WHERE CNO=A.CNO)MIN_SCORE,
(SELECT AVG(SCORE) FROM SC WHERE CNO=A.CNO)AVG_SCORE,
(SELECT COUNT(*) FROM SC WHERE CNO=A.CNO AND SCORE>=60)/(SELECT COUNT(*) FROM SC WHERE CNO=A.CNO)*100 '及格率%'
FROM COURSE AS A ORDER BY CNO

**17、按各科成绩进行排序，并显示排名

SELECT SNO,CNO,SCORE,(SELECT COUNT(1)+1 FROM SC WHERE SCORE> A.SCORE AND CNO=A.CNO) `RANK`
FROM SC A;
ORDER BY CNO,SCORE DESC;

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

SELECT SNO,SUM(SCORE)
FROM SC
GROUP BY SNO 
ORDER BY SUM(SCORE) DESC

19、查询不同老师所教不同课程平均分从高到低显示

SELECT tname,sc.cno,AVG(score) FROM teacher t
JOIN (sc,course c) 
ON sc.cno = c.cno AND c.tno = t.tno
GROUP BY sc.`CNO`
ORDER BY AVG(score) DESC;

20、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

21、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分.
**

SELECT CNO,
SUM(CASE WHEN SCORE<=100 AND SCORE>=85 THEN 1 ELSE 0 END) '[100-85]人数',
CAST(SUM(CASE WHEN SCORE<=100 AND SCORE>=85 THEN 1 ELSE 0 END)/COUNT(*) AS DECIMAL(5,2)) '占比',
SUM(CASE WHEN SCORE<85 AND SCORE>=70 THEN 1 ELSE 0 END) '[70-85]人数',
CAST(SUM(CASE WHEN SCORE<85 AND SCORE>=70 THEN 1 ELSE 0 END)/COUNT(*) AS DECIMAL(5,2)) '占比',
SUM(CASE WHEN SCORE<70 AND SCORE>=60 THEN 1 ELSE 0 END) '[60-70]人数',
CAST(SUM(CASE WHEN SCORE<70 AND SCORE>=60 THEN 1 ELSE 0 END)/COUNT(*) AS DECIMAL(5,2)) '占比',
SUM(CASE WHEN SCORE<60 THEN 1 ELSE 0 END) '[0-60]人数',
CAST(SUM(CASE WHEN SCORE<60 THEN 1 ELSE 0 END)/COUNT(*) AS DECIMAL(5,2)) '占比'
FROM SC
GROUP BY CNO

22、查询学生平均成绩及其名次
**

SELECT A.SNO,A.AVG,COUNT(*) '名次'
FROM (SELECT SNO,AVG(SCORE) AVG FROM SC GROUP BY SNO) A
INNER JOIN (SELECT SNO,AVG(SCORE) AVG FROM SC GROUP BY SNO) B
ON A.AVG<=B.AVG
GROUP BY A.SNO
ORDER BY A.AVG DESC

23、查询各科成绩前三名的记录

24、查询每门课程被选修的学生数

SELECT CNO,COUNT(*)
FROM SC GROUP BY CNO

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

SELECT A.SNO,B.SNAME
FROM SC  A INNER JOIN STUDENT B ON A.SNO=B.SNO
GROUP BY A.SNO HAVING COUNT(*)=2

26、查询男、女生人数

 SELECT SSEX,COUNT(*) FROM STUDENT GROUP BY SSEX

27、查询名字中含有”风”字的学生信息

SELECT * FROM STUDENT WHERE SNAME LIKE '%风%'

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

SELECT SNAME,COUNT(*)
FROM STUDENT 
GROUP BY SNAME
HAVING COUNT(*)>1

29、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)

SELECT SNAME FROM STUDENT
WHERE YEAR(SAGE)=1990

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

SELECT CNO,AVG(SCORE)
FROM SC GROUP BY CNO
ORDER BY AVG(SCORE) DESC,CNO

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

SELECT A.SNO,B.SNAME,AVG(A.SCORE)
FROM SC A INNER JOIN STUDENT B
ON A.SNO=B.SNO
GROUP BY A.SNO
HAVING AVG(A.SCORE)>=85

32、查询课程名称为”数学”，且分数低于60的学生姓名和分数

SELECT sname,score FROM student s
JOIN sc ON s.sno = sc.`SNO`
WHERE score<60 AND sc.cno = (
	SELECT cno FROM course
	WHERE cname = '数学'
);
SELECT A.SNO,A.SNAME,B.SCORE
FROM STUDENT A JOIN (SC B,COURSE C)
ON A.SNO=B.SNO AND B.CNO=C.CNO
WHERE C.CNAME='数学' AND B.SCORE<60

33、查询所有学生的课程及分数情况；

SELECT A.SNAME,C.CNAME,B.SCORE
FROM STUDENT A,SC B,COURSE C
WHERE A.SNO=B.SNO AND B.CNO=C.CNO

34、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数；

SELECT A.SNAME,C.CNAME,B.SCORE
FROM STUDENT A,SC B,COURSE C
WHERE A.SNO=B.SNO AND B.CNO=C.CNO
AND B.SCORE>=70

35、查询不及格的学生的学号和课程号

SELECT SNO,CNO FROM SC WHERE SCORE<60

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

SELECT B.SNO,B.SNAME
FROM SC A INNER JOIN STUDENT B ON A.SNO=B.SNO
WHERE A.CNO=01 AND A.SCORE>=80

37、求每门课程的学生人数

SELECT CNO,COUNT(*) FROM SC GROUP BY CNO;

38、查询选修”张三”老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT A.*,MAX(B.SCORE)
FROM STUDENT A,SC B,COURSE C,TEACHER D
WHERE A.SNO=B.SNO AND B.CNO=C.CNO AND C.TNO=D.TNO
AND D.TNAME='张三'

39、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT DISTINCT A.*
FROM SC A INNER JOIN SC B 
ON A.SNO=B.SNO
WHERE A.CNO<>B.CNO AND A.SCORE=B.SCORE

40、查询每门功成绩最好的前两名。
41、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT cno,COUNT(*) FROM sc 
GROUP BY cno
HAVING COUNT(*) > 5
ORDER BY COUNT(*) DESC,cno;

42、检索至少选修两门课程的学生学号

SELECT SNO FROM SC
GROUP BY SNO
HAVING COUNT(*)>=2

43、查询选修了全部课程的学生信息

SELECT B.*
FROM SC A INNER JOIN STUDENT B ON A.SNO=B.SNO
GROUP BY A.SNO
HAVING COUNT(*)=(SELECT COUNT(*) FROM COURSE)

44、查询各学生的年龄

SELECT SNAME,YEAR(NOW())-YEAR(SAGE) AGE
FROM STUDENT

45、查询本周过生日的学生
week函数返回的是这周是本年的第几周。

SELECT SNAME FROM STUDENT WHERE WEEK(SAGE)=WEEK(NOW())

46、查询下周过生日的学生

SELECT SNAME FROM STUDENT WHERE WEEK(SAGE)=WEEK(NOW())+1

47、查询本月过生日的学生

SELECT SNAME FROM STUDENT WHERE MONTH(SAGE)=MONTH(NOW())

48、查询下月过生日的学生

SELECT SNAME FROM STUDENT WHERE MONTH(SAGE)=MONTH(NOW())+1

49、查询学生成绩的及格状况，以学生姓名、科目、及格状况显示。

SELECT B.SNAME,C.CNAME,A.SCORE,IF(A.SCORE>=60,'及格','不及格')
FROM SC A,STUDENT B,COURSE C
WHERE A.SNO=B.SNO AND A.CNO=C.CNO