MySQL 50道题

最新推荐文章于 2024-07-06 21:03:48 发布

转载最新推荐文章于 2024-07-06 21:03:48 发布 · 357 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：https://blog.youkuaiyun.com/qq_45593650/article/details/116878310

文章标签：

#mysql

计算机基础同时被 2 个专栏收录

22 篇文章

订阅专栏

mysql

2 篇文章

订阅专栏

本文记录了MySQL 50道题的解题思路。介绍了表名和字段，包括学生表、课程表、教师表和成绩表。针对每道题给出思路，如查询课程成绩高低对比、平均成绩筛选、学生选课情况等，还涉及分组、联结、聚合等操作及相关注意事项。

MySQL 50道题

最近学了下MySql，准备学习下这50道题，留作记录。

注意：MySQL5.7以后会默认开启sql_mode=only_full_group_by，会导致group by 操作时select 后只能跟group by 之后的字段，可以使用select @@sql_mode查询。
win10系统下，找到MySQL安装目录，修改my.ini文件，在[mysqld]之后配置
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
打开cmd 执行MySQL重启 net stop mysql和 net start mysql

表名和字段

学生表
student(sid,sname,sage,ssex) --学生编号,学生姓名, 学生年龄,学生性别

CREATE TABLE `student` (
    `sid` varchar(10) DEFAULT NULL,
    `sname` varchar(10) DEFAULT NULL,
    `sage` datetime DEFAULT NULL,
    `ssex` varchar(10) CHARACTER SET utf8 DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO `student` VALUES ('03', '孙风', '1990-05-20 00:00:00', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-08-06 00:00:00', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01 00:00:00', '女');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-03-01 00:00:00', '女');
INSERT INTO `student` VALUES ('07', '郑竹', '1989-07-01 00:00:00', '女');
INSERT INTO `student` VALUES ('08', '王菊', '1990-01-20 00:00:00', '女');

课程表
course(cid,cname,tid) – --课程编号, 课程名称, 教师编号

CREATE TABLE `course` (
    `cid` varchar(10) DEFAULT NULL,
    `cname` varchar(10) DEFAULT NULL,
    `tid` varchar(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');

教师表
teacher(tid,tname) --教师编号,教师姓名

CREATE TABLE `teacher` (
    `tid` varchar(10) DEFAULT NULL,
    `tname` varchar(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');

成绩表
sc(sid,cid,score) --学生编号,课程编号,学生分数

CREATE TABLE `sc` (
    `sid` varchar(10) DEFAULT NULL,
    `cid` varchar(10) DEFAULT NULL,
    `score` decimal(18,1) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `sc` VALUES ('01', '01', '80.0');
INSERT INTO `sc` VALUES ('01', '02', '90.0');
INSERT INTO `sc` VALUES ('01', '03', '99.0');
INSERT INTO `sc` VALUES ('02', '01', '70.0');
INSERT INTO `sc` VALUES ('02', '02', '60.0');
INSERT INTO `sc` VALUES ('02', '03', '80.0');
INSERT INTO `sc` VALUES ('03', '01', '80.0');
INSERT INTO `sc` VALUES ('03', '02', '80.0');
INSERT INTO `sc` VALUES ('03', '03', '80.0');
INSERT INTO `sc` VALUES ('04', '01', '50.0');
INSERT INTO `sc` VALUES ('04', '02', '30.0');
INSERT INTO `sc` VALUES ('04', '03', '20.0');
INSERT INTO `sc` VALUES ('05', '01', '76.0');
INSERT INTO `sc` VALUES ('05', '02', '87.0');
INSERT INTO `sc` VALUES ('06', '01', '31.0');
INSERT INTO `sc` VALUES ('06', '03', '34.0');
INSERT INTO `sc` VALUES ('07', '02', '89.0');
INSERT INTO `sc` VALUES ('07', '03', '98.0');

题目及思路

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

思路：
（1）展示学生信息，学生是主体，所以查询student表
（2）展示两门分数，所以联结两次分数表，联结条件是两个表的sid，附带筛选条件 cid
（3）最后过滤条件，01课程比02课程成绩高


SELECT
	a.*, b.score AS score01,
	c.score AS score02
FROM
	student AS a
INNER JOIN sc AS b ON a.sid = b.sid
AND b.cid = '01'
LEFT JOIN sc AS c ON a.sid = c.sid
AND c.cid = '02'
WHERE
	b.score > c.score

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数，这个和上面的原理一样

SELECT
	a.*, b.score AS '01score',
	c.score AS '02score'
FROM
	student AS a
INNER JOIN sc AS b ON a.sid = b.sid
AND b.cid = '01'
LEFT JOIN sc AS c ON a.sid = c.sid
AND c.cid = '02'
WHERE
	b.score < c.score;

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

思路：联结聚合筛选
(1)筛选条件是最后的，且需要针对分组进行筛选，使用HAVING
(2)平均成绩需要聚合函数，分组是后面的操作，需要先联结再聚合，
(3)聚合的是分数，所以按sc表外联结，查询字段必须都在分组中

SELECT
	a.sid,
	b.sname,
	ROUND(AVG(a.score), 2) AS avg_score
FROM
	sc AS a
LEFT JOIN student AS b ON b.sid = a.sid
GROUP BY
	a.sid,
	b.sname
HAVING
	avg_score >= 60

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

思路：有成绩的和无成绩的是两套查询方案，可以最后组合查询
(1)有成绩的可参考上题，没成绩的分数为0分
(2)sc表中没有记录的sid 就是没有成绩的人。

SELECT
	a.sid,
	b.sname,
	ROUND(AVG(a.score), 2) AS avg_socre
FROM
	sc AS a
LEFT JOIN student AS b ON b.sid = a.sid
GROUP BY
	a.sid,
	b.sname
HAVING
	avg_socre < 60
UNION
	SELECT
		a.sid,
		a.sname,
		0 AS avg_score
	FROM
		student AS a
	WHERE
		a.sid NOT IN (SELECT DISTINCT sid FROM sc)

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

思路：总成绩总课程需要聚合计算
(1) 先聚合计算得到总课程数和总成绩
(2) 再和学生信息表进行联结

SELECT
	b.sid,
	b.sname,
	COUNT(cid),
	SUM(score)
FROM
	sc AS a
RIGHT JOIN student AS b ON a.sid = b.sid
GROUP BY
	b.sid,
	b.sname

6、查询"李"姓老师的数量

思路：正则匹配筛选记录，聚合统计数量

SELECT
	COUNT(t.tname)
FROM
	teacher AS t
WHERE
	t.tname REGEXP '^张.+'
GROUP BY
	t.tname

7、查询学过"张三"老师授课的同学的信息

思路：展示信息只有学生信息，但筛选条件涉及教师表和成绩表和课程表
(1) 学生表和成绩表可以直接联结
(2) 筛选条件可以用子查询

SELECT
	student.*
FROM
	student
JOIN sc ON student.sid = sc.sid
WHERE
	sc.cid IN (
		SELECT
			cid
		FROM
			course
		WHERE
			course.tid IN (
				SELECT
					tid
				FROM
					teacher
				WHERE
					tname = '张三'
			)
	)

8、查询没学过"张三"老师授课的同学的信息

思路：剔除学过的学生即可

SELECT
	*
FROM
	student
WHERE
	student.sid NOT IN (
		SELECT
			student.sid
		FROM
			student
		JOIN sc ON student.sid = sc.sid
		WHERE
			sc.cid IN (
				SELECT
					cid
				FROM
					course
				WHERE
					course.tid IN (
						SELECT
							tid
						FROM
							teacher
						WHERE
							tname = '张三'
					)
			)
	)

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

思路：联结是为了扩展信息，筛选可以直接用where
不恰当查询

SELECT
	*
FROM
	student AS st
INNER JOIN (
	SELECT
		sc1.sid
	FROM
		sc AS sc1,
		sc AS sc2
	WHERE
		sc1.sid = sc2.sid
	AND sc1.cid = '01'
	AND sc2.cid = '02'
) AS ssc ON st.sid = ssc.sid

恰当查询

SELECT
	a.*
FROM
	student AS a,
	sc AS b,
	sc AS c
WHERE
	a.sid = b.sid
AND b.sid = c.sid
AND b.cid = '01'
AND c.cid = '02'

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

思路：WHERE 后面的in 和 not in条件可以有多个

SELECT
	*
FROM
	student
WHERE
	student.sid IN (
		SELECT
			sid
		FROM
			sc
		WHERE
			cid = '01'
	)
AND student.sid NOT IN (
	SELECT
		sid
	FROM
		sc
	WHERE
		cid = '02'
)

11、查询没有学全所有课程的同学的信息

思路：所有课程数量，对比学生学习课程数量

SELECT
	*
FROM
	student
WHERE
	sid NOT IN (
		SELECT
			sc.sid
		FROM
			sc
		GROUP BY
			sc.sid
		HAVING
			COUNT(cid) = (SELECT COUNT(cid) FROM course)
	)

SELECT
	student.sid,student.sname,sc.*
FROM
	student
LEFT JOIN sc ON student.sid = sc.sid
GROUP BY
	student.sid
-- HAVING
-- 	COUNT(sc.cid) < (SELECT COUNT(*) FROM course)

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

使用连接

SELECT
	c.*
FROM
	student AS c
RIGHT JOIN (
	SELECT DISTINCT
		b.sid
	FROM
		sc AS a
	INNER JOIN sc AS b ON a.sid = '01'
	AND a.cid = b.cid
	AND b.sid != '01'
) AS d ON c.sid = d.sid;

使用子查询

SELECT
	a.*
FROM
	student a
WHERE
	a.sid IN (
		SELECT
			b.sid
		FROM
			sc b
		WHERE
			b.cid IN (
				SELECT
					c.cid
				FROM
					sc c
				WHERE
					c.sid = '01'
			)
	);

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

思路：这里面要求sid!=‘01’，sid的课程数量和’01’同学的课程数量一致，sid的课程cid不能在’01’同学的所学课程之外。
三个条件完成sid的筛选。

SELECT
	*
FROM
	student
WHERE
	sid NOT IN (
		SELECT
			sc.sid
		FROM
			sc
		WHERE
			cid NOT IN (
				SELECT
					cid
				FROM
					sc
				WHERE
					sid = '01'
			)
	)
AND sid IN (
	SELECT
		a.sid
	FROM
		sc a
	GROUP BY
		a.sid
	HAVING
		COUNT(a.cid) = (
			SELECT
				COUNT(*)
			FROM
				sc
			WHERE
				sid = '01'
		)
)
AND sid NOT IN ('01');

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

思路：首先查到张三老师的tid，进而查到张三老师教授课程的集合。
筛选出上过张三老师课程的学生，反选出没上过张三老师课程的学生。

SELECT
	a.*
FROM
	student a
WHERE
	a.sid NOT IN (
		SELECT DISTINCT
			b.sid
		FROM
			sc b
		WHERE
			b.cid IN (
				SELECT
					c.cid
				FROM
					course c
				WHERE
					c.tid = (
						SELECT
							tid
						FROM
							teacher
						WHERE
							tname = '张三'
					)
			)
	)

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

思路：先筛选出不及格课程，再利用分组计算不及格课程数量和平均成绩，再分组筛选出不及格课程数大于等于二的学生，再用连接获取学生信息。


SELECT
	*
FROM
	student
INNER JOIN (
	SELECT
		sid,
		COUNT(cid) num,
		ROUND(AVG(score))
	FROM
		sc
	WHERE
		score < 60
	GROUP BY
		sid
	HAVING
		num >= 2
) scc ON student.sid = scc.sid;

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

思路：先找出01课程分数小于60的学生，连接学生信息，再降序排列。

SELECT
	a.*, b.*
FROM
	student a,
	sc b
WHERE
	a.sid = b.sid
AND b.cid = '01'
AND b.score < 60
ORDER BY
	b.score DESC;

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

思路：结果包含同一个字段的多个值，使用自联结。计算平均成绩再次进行联结。

SELECT
	b.sid,
	(
		SELECT
			score
		FROM
			sc
		WHERE
			sid = b.sid
		AND cid = '01'
	) '01',
	(
		SELECT
			score
		FROM
			sc
		WHERE
			sid = b.sid
		AND cid = '02'
	) '02',
	(
		SELECT
			score
		FROM
			sc
		WHERE
			sid = b.sid
		AND cid = '03'
	) '03',
	c.avg_score
FROM
	sc b
RIGHT JOIN (
	SELECT
		a.sid,
		ROUND(AVG(a.score)) avg_score
	FROM
		sc a
	GROUP BY
		a.sid
) c ON b.sid = c.sid
GROUP BY
	b.sid
ORDER BY
	avg_score DESC;

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

思路：一行显示多个字段，需要联结，包含聚合字段，需要分组。百分数计算需要

SELECT
	a.cid AS 课程ID,
	b.cname AS 课程name,
	MAX(a.score) AS 最高分,
	MIN(a.score) AS 最低分,
	ROUND(AVG(a.score), 2) AS 平均分,
	(SELECT ROUND(100*(SUM(CASE WHEN a.score >=90 THEN 1 ELSE 0 END)/SUM(CASE WHEN a.score THEN 1 ELSE 0 END)),2)) AS 优秀率,
	(SELECT ROUND(100*(SUM(CASE WHEN a.score >=80 AND a.score < 90 THEN 1 ELSE 0 END)/SUM(CASE WHEN a.score THEN 1 ELSE 0 END)),2)) AS 优良率,
	(SELECT ROUND(100*(SUM(CASE WHEN a.score >=70 AND a.score < 80 THEN 1 ELSE 0 END)/SUM(CASE WHEN a.score THEN 1 ELSE 0 END)),2)) AS 中等率,
	(SELECT ROUND(100*(SUM(CASE WHEN a.score >=60 THEN 1 ELSE 0 END)/SUM(CASE WHEN a.score THEN 1 ELSE 0 END)),2)) AS 及格率
FROM
	sc AS a
LEFT JOIN course AS b ON b.cid = a.cid
GROUP BY
	a.cid

19、按各科成绩进行排序，并显示排名（这个题目还是有点问题）

虚拟列，(@i:=@i+1) 设定步长，(SELECT @i:=0)init 设定初始值

SELECT
	(@i :=@i + 1) AS virtualrow,
	@k := (CASE WHEN @score = a.score THEN @k ELSE @i	END	) AS rank,
	@score := a.score AS score
FROM
	(SELECT	sid,cid,score	FROM sc	GROUP BY sid,cid,score ORDER BY	score DESC) a,
	(SELECT	@i := 0 ,@k := 0 ,@score := 0	) init;

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

先查询学生总成绩

SELECT sid,SUM(score) AS sum_score FROM sc GROUP BY sid ORDER BY sum_score DESC;
SELECT * FROM sc GROUP BY sid,cid,score ORDER BY score DESC;

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

先左联结获取tname cid , 最后分组计算平均成绩，降序排序。

SELECT
	c.tname,
	a.cid,
	b.cname,
	ROUND(AVG(a.score), 2) avg_score
FROM
	sc AS a
LEFT JOIN course AS b ON b.cid = a.cid
LEFT JOIN teacher AS c ON b.tid = c.tid
GROUP BY
	a.cid
ORDER BY
	avg_score DESC;

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

思路:先分段统计数量

SELECT
    cid,
    SUM(CASE WHEN score>70 AND score <=85 THEN 1 ELSE 0 END)AS '[70-85]',
    ROUND(100*(SUM(CASE WHEN score>70 AND score <= 85 THEN 1 ELSE 0 END)/COUNT(*)),2)AS '百分比'
FROM
    sc
GROUP BY
    cid

SELECT
    SUM(CASE WHEN score>85 AND score<=100 THEN 1 ELSE 0 END) '[100-85]',
    ROUND(/COUNT(*),2)
FROM
    sc;

SELECT
    cid,
    SUM(CASE WHEN score>85 AND score <=100 THEN 1 ELSE 0 END)AS '[100-85]',
    ROUND(100*(SUM(CASE WHEN score > 85 AND score<=100 THEN 1 ELSE 0 END)/COUNT(*)),2)
FROM
    sc
GROUP BY
    cid

SELECT
    a.cid,
    f.cname,
    b.`[100-85]`,b.百分比,
    c.`[70-85]`,c.百分比,
    d.`[60-70]`,d.百分比,
    e.`[0-60]`,e.百分比
FROM
    sc a 
LEFT JOIN
    (SELECT cid,
            SUM(CASE WHEN score>=85 AND score <=100 THEN 1 ELSE 0 END)AS '[100-85]',
            ROUND(100*(SUM(CASE WHEN score >=85 AND score<=100 THEN 1 ELSE 0 END)/COUNT(*)),2)AS '百分比'
    FROM sc GROUP BY cid)b
on a.cid=b.cid
LEFT JOIN 
    (SELECT cid,SUM(CASE WHEN score>=70 AND score <85 THEN 1 ELSE 0 END)AS '[70-85]',
    ROUND(100*(SUM(CASE WHEN score>=70 AND score <85 THEN 1 ELSE 0 END)/COUNT(*)),2)AS '百分比'
    FROM sc GROUP BY cid)c 
ON a.cid=c.cid
LEFT JOIN 
    (SELECT cid,
	SUM(CASE WHEN score>=60 AND score <70 THEN 1 ELSE 0 END)AS '[60-70]',
	ROUND(100*(SUM(CASE WHEN score>=60 AND score <70 THEN 1 ELSE 0 END)/COUNT(*)),2)AS '百分比' 
	FROM sc GROUP BY cid)d 
ON a.cid=d.cid
LEFT JOIN 
    (SELECT cid,
    SUM(CASE WHEN score>=0 AND score <60 THEN 1 ELSE 0 END)AS '[0-60]',
    ROUND(100*(SUM(CASE WHEN score>=0 AND score<60 THEN 1 ELSE 0 END)/COUNT(*)),2)AS '百分比' 
    FROM sc GROUP BY cid)e
ON a.cid=e.cid
LEFT JOIN course f ON a.cid=f.cid GROUP BY cid

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

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

join on 是联结两个表，结果会根据条件不同，汇总表行数会多于单张表行数，on后面的条件整体作为筛选条件，从n*n个组合中挑出满足条件的部分。
group by 是对现有数据分组，不会增加新的行，by后面的字段是分组的条件，在现有行中指定的几个字段值完全相同的隶属于一个组。
先用join 操作得到想要的列并进行第一次筛选，在通过group by 对行数据进行筛选。

SELECT
	a.sid,
	a.cid,
	a.score
FROM
	sc AS a
LEFT JOIN sc AS b ON a.cid = b.cid
AND a.score < b.score
GROUP BY
	a.sid,
	a.cid,
	a.score
HAVING
	COUNT(b.sid) < 3
ORDER BY
	a.cid,
	a.score DESC

GROUP BY a.sid HAVING COUNT(b.sid)<3 ORDER BY a.cid,b.score DESC;
-- (cid1的数量)^2+(cid2的数量)^2+(cid3的数量)^2
SELECT * FROM sc AS a JOIN sc AS b ON a.cid=b.cid

SELECT COUNT(*),cid FROM sc GROUP BY cid,score

SELECT * FROM sc ORDER BY cid

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

思路：按课程分组，再按学生进行统计

SELECT cid,COUNT(sid) FROM sc GROUP BY cid

27、查询出只有两门课程的全部学生的学号和姓名

SELECT
	a.sid,
	b.sname
FROM
	sc a
LEFT JOIN student b ON a.sid = b.sid
GROUP BY
	a.sid
HAVING
	COUNT(a.cid) = 2

28、查询男生、女生人数

SELECT ssex,COUNT(*) 人数 FROM student GROUP BY ssex;

29、查询名字中含有"风"字的学生信息

SELECT * FROM student WHERE sname LIKE "%风%";

30、查询同名同性学生名单，并统计同名人数

SELECT a.*,COUNT(sid) FROM student a GROUP BY sname,ssex HAVING COUNT(sid)>1;

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

SELECT * FROM student WHERE sage REGEXP '^1990.'

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

SELECT cid,ROUND(AVG(score),2) 平均成绩 FROM sc GROUP BY cid ORDER BY 平均成绩 DESC,cid ASC;

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

SELECT
	a.sid,
	b.sname,
	ROUND(AVG(a.score), 2) avg_score
FROM
	sc a
LEFT JOIN student b ON a.sid = b.sid
GROUP BY
	a.sid
HAVING
	avg_score >= 85;

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

SELECT
	a.sid,
	b.sname,
	a.score
FROM
	sc a
LEFT JOIN student b ON a.sid = b.sid
WHERE
	cid IN (
		SELECT
			cid
		FROM
			course
		WHERE
			cname = '数学'
	)
AND a.score < 60

35、查询所有学生的课程及分数情况(这个实现有点问题)

SELECT
	a.sid,
	b.sname,
	(SELECT score	FROM sc	WHERE	cid IN (SELECT cid FROM	course WHERE cname = '语文') AND sid = a.sid) 语文,
	(SELECT	score FROM sc	WHERE	cid IN (SELECT cid FROM	course WHERE cname = '数学') AND sid = a.sid) 数学,
	(SELECT score FROM sc	WHERE	cid IN (SELECT cid FROM	course WHERE cname = '英语') AND sid = a.sid) 英语
FROM
	sc a
LEFT JOIN student b ON a.sid = b.sid
GROUP BY sid

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

SELECT
	b.sname,
	c.cname,
	a.score
FROM
	sc a
JOIN student b ON a.sid = b.sid
AND a.score > 70
LEFT JOIN course c ON a.cid = c.cid;

37、查询不及格的课程

SELECT
	a.sid,
	b.sname,
	c.cname,
	a.score
FROM
	sc a
LEFT JOIN student b ON a.sid = b.sid
LEFT JOIN course c ON a.cid = c.cid
WHERE
	score < 60

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

SELECT
	a.sid,
	b.sname,
	a.cid,
	a.score
FROM
	sc a
LEFT JOIN student b ON a.sid = b.sid
WHERE
	a.score >= 80
AND a.cid = '01';

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

SELECT cid,COUNT(*) AS '选课人数' FROM sc GROUP BY cid;

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

SELECT
	d.*, MAX(a.score)
FROM
	sc a
JOIN course b ON a.cid = b.cid
JOIN teacher c ON b.tid = c.tid
AND c.tname = '张三'
LEFT JOIN student d ON a.sid = d.sid

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

SELECT DISTINCT
	a.sid,
	a.cid,
	a.score
FROM
	sc a,
	sc b
WHERE
	a.cid != b.cid
AND a.score = b.score;

42、查询每门功成绩最好的前两名

where 中的条件不仅可以是并列的，而且可以是嵌套的，整体作为筛选依据。

SELECT
	a.sid,
	a.cid,
	a.score
FROM
	sc a
WHERE
	(
		SELECT
			COUNT(1)
		FROM
			sc b
		WHERE
			a.cid = b.cid
		AND a.score <= b.score
	) <= 2
ORDER BY
	a.cid

43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT
	a.cid,
	COUNT(1) total
FROM
	sc a
GROUP BY
	a.cid
HAVING
	total > 5
ORDER BY
	total,
	a.cid ASC;

44、检索选修大于两门课程的学生学号

SELECT a.sid FROM sc a GROUP BY a.sid HAVING COUNT(1)>2

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

SELECT
	a.sid
FROM
	sc a
LEFT JOIN student b ON a.sid = b.sid
GROUP BY
	a.sid
HAVING
	COUNT(1) = (SELECT COUNT(cid) FROM course)

46、查询各学生的年龄

按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

SELECT
	a.sid,
	a.sage,
	DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(a.sage, '%Y') + (
		CASE
		WHEN DATE_FORMAT(NOW(), '%m%d') > DATE_FORMAT(a.sage, '%m%d') THEN
			0
		ELSE
			1
		END
	) AS age
FROM
	student a

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

SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(sage)

select WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))

MySQL 50道题

MySQL 50道题

表名和字段

题目及思路

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

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数，这个和上面的原理一样

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

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

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

6、查询"李"姓老师的数量

7、查询学过"张三"老师授课的同学的信息

8、查询没学过"张三"老师授课的同学的信息

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

11、查询没有学全所有课程的同学的信息

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

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

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

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

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

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

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

19、按各科成绩进行排序，并显示排名（这个题目还是有点问题）

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

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

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

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

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

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

27、查询出只有两门课程的全部学生的学号和姓名

28、查询男生、女生人数

29、查询名字中含有"风"字的学生信息

30、查询同名同性学生名单，并统计同名人数

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

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

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

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

35、查询所有学生的课程及分数情况(这个实现有点问题)

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

37、查询不及格的课程

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

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

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

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

42、查询每门功成绩最好的前两名

43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

44、检索选修大于两门课程的学生学号

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

46、查询各学生的年龄

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

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