MySQL-09-题目总结

12、6.8、题目分类

• 表关系

1、应用临时表

 2、查询“生物”课程比“物理”课程成绩高的所有学生的学号； 
 思路：1、查出所有同学生物成绩 当作表A
 	  2、查询所有同学的物理成绩，做临时表B
 	  3、联表查询  student_id 相同的是同一个学生
SELECT
	B.student_id,
	B.num,
	A.num
FROM
	(
		SELECT
			student_id,
			num
		FROM
			score
		LEFT JOIN course ON course_id = course.cid
		WHERE
			cname = '生物'
	) AS B
LEFT JOIN (
	SELECT
		student_id,
		num
	FROM
		score
	LEFT JOIN course ON course_id = course.cid
	WHERE
		cname = '物理'
) AS A ON B.student_id = A.student_id
WHERE
	B.num >
IF (ISNULL(A.num), 0, A.num);

7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
--  思路：我们在查询同一行数据的时候，也就是同一行数据比较，必须用虚拟表 
			SELECT sid, sname FROM student WHERE sid in (
			SELECT aid FROM
			(SELECT student_id aid FROM score WHERE course_id = 1) A INNER JOIN 
			(SELECT student_id bid FROM score WHERE course_id = 2) B ON aid = bid);
			
9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
-- 思路：和第七套差不多，对同一行数据做比较必须用临时表 
			SELECT sid, sname FROM student INNER JOIN (
			SELECT aid FROM
			(SELECT student_id aid, num anum FROM score WHERE course_id = 1) A INNER JOIN 
			(SELECT student_id bid, num bnum FROM score WHERE course_id = 2) B ON aid = bid WHERE anum > bnum) AS C ON sid = aid;

2、分组查询

-- 3、查询平均成绩大于60分的同学的学号和平均成绩； 
-- 			思路：1、平均用到聚合函数 2、集合函数要用havng
SELECT
	student_id,
	avg(num)
FROM
	score
GROUP BY
	student_id
HAVING
	avg(num) > 60;

 4、查询所有同学的学号、姓名、选课数、总成绩；
-- 思路：1、选课数和总成绩在score中分组查询，姓名联表

SELECT
	sid,
	sname,

IF (ISNULL(courses), 0, courses) AS courses,

IF (ISNULL(sum), 0, sum) AS sum
FROM
	student
LEFT JOIN (
	SELECT
		student_id,
		count(1) AS courses,
		sum(num) AS sum
	FROM
		score
	GROUP BY
		student_id
) AS B ON student.sid = B.student_id;

10、查询有课程成绩小于60分的同学的学号、姓名；
	SELECT * FROM score WHERE num < 60 GROUP BY student_id;
	
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
	SELECT course_id, max(num) max, min(num) min FROM score GROUP BY course_id;

3、模糊查询

很easy

5、查询姓“李”的老师的个数；
-- 			太简单
			SELECT count(1) teacher_count  FROM teacher WHERE tname LIKE "李%";

4、否定查询

有时候我们倒着想才能写对！

6、查询没学过“李平老师”老师课的同学的学号、姓名；
--  思路：1、先在分数表中查至少选一本 李平老师 课的学生 2、在在学生表中not in

SELECT
	tid,
	tname
FROM
	student
WHERE
	sid NOT IN (
		SELECT
			student_id
		FROM
			score
		WHERE
			course_id IN (
				SELECT
					cid
				FROM
					teacher
				LEFT JOIN course ON tid = course.teacher_id
				WHERE
					tname = "李平老师"
			)
		GROUP BY
			student_id
	);

5、ALL

8、查询学过“李平老师”老师所教的所有课的同学的学号、姓名；
--    思路：1、把选了 李平老师课的同学都查出来，在统计他们是否全选 用having 后面跟一个聚合函数的查询
			SELECT sid, sname FROM student WHERE sid in (
			SELECT student_id courses  FROM score WHERE course_id in (
			SELECT cid FROM course LEFT JOIN teacher on	teacher_id = tid WHERE tname = '李平老师') GROUP BY student_id
			HAVING count(1) = (SELECT count(1) FROM course LEFT JOIN teacher on	teacher_id = tid WHERE tname = '李平老师'));
			

-- 11、查询没有学所有课的同学的学号、姓名；
				
    SELECT sid, sname FROM student INNER JOIN (
    SELECT student_id,count(1) FROM score GROUP BY student_id HAVING count(1) =
    (SELECT count(1) FROM course)) AS B ON student.sid = B.student_id;

6、至少有一个

思路：使用 in

-- 12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
			SELECT sid, sname FROM student WHERE sid in (
			SELECT student_id FROM score WHERE course_id in (
			SELECT course_id FROM score WHERE student_id = 1) and student_id != 1 GROUP BY student_id);

7、目前最难的一题

- 14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
-- 思路：
-- 1、我们先查出课程数和2号同学相同的 
-- 2、我们再查课程数再2号学的课程中的学生（注意：他可能学的比2号同学多），所以我们要和第一中情况联表查询
SELECT sid,sname FROM student WHERE sid in(
SELECT
	score.student_id
FROM
	score
WHERE
	score.student_id in (
		SELECT
			score.student_id
		FROM
			score
		WHERE
			student_id != 2
		AND course_id IN (
			SELECT
				course_id
			FROM
				score
			WHERE
				student_id = 2
		)
		GROUP BY
			student_id
		HAVING
			count(1) = (
				SELECT
					count(1)
				FROM
					score
				WHERE
					student_id = 2
			)
	)
GROUP BY
	score.student_id
HAVING
	count(1) = (
		SELECT
			count(1)
		FROM
			score
		WHERE
			student_id = 2
	)
);

8、删除一个表再其他表中的记录

-- 15、删除学习“叶平”老师课的SC表记录；
				
	DELETE FROM score WHERE course_id in 
(SELECT cid FROM course LEFT JOIN teacher on	course.teacher_id = tid WHERE tname = '李平老师');

9、查询的数据作为添加数据

-- 16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
				-- 查询的结果放入插入语句的映射中
				INSERT INTO score (student_id, course_id, num) 
				SELECT score.student_id, course_id, avg FROM score INNER JOIN 
				(SELECT student_id,avg(num) avg FROM score WHERE course_id = 2) AS B ON B.student_id = score.student_id;
-- 

10、查询映射

查询的虚表中的映射可以有很多定义，也可以从不同的表中映射！就很灵活

-- 17、按平均成绩从低到高显示所有学生的“生物”、“体育”、“美术”三门的课程成绩，
--     按如下形式显示： 学生ID,“生物”、“体育”、“美术,有效课程数,有效平均分；
			-- 思路：就是嵌套查询，映射也可以是查询但是必须只查询一行数据
-- 					相当于循环，外层表叫s1，每次查询映射也根据外层查询的只进行查询

				SELECT student_id, (SELECT num FROM score as s2 where s2.student_id = s1.student_id and course_id = (SELECT cid FROM course WHERE cname = '生物')) AS 生物 ,
				(SELECT num FROM score as s2 where s2.student_id = s1.student_id and course_id = (SELECT cid FROM course WHERE cname = '体育')) AS 体育 ,
				(SELECT num FROM score as s2 where s2.student_id = s1.student_id and course_id = (SELECT cid FROM course WHERE cname = '美术')) AS 美术 ,
				count(1) 选课数, avg(IF(ISNULL(num), 0, num)) avg
				FROM score as s1 GROUP BY student_id;

11、逻辑判断

• case when 条件 then 满足条件的值 else 不满足的值 end
• if（isnull（某列），0，某列）；

-- 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
SELECT course_id, avg(num), sum(CASE WHEN num >= 60 THEN 1 ELSE 0 end)/sum(1) percent 
	FROM score GROUP BY course_id ORDER BY avg(num) asc, percent desc;

-- 39、查询两门以上不及格课程的同学的学号及其平均成绩；
	SELECT student_id, avg(num) FROM score GROUP BY student_id 
	HAVING sum(CASE WHEN num < 60 THEN 1 ELSE 0 END) > 2;

12、错题

查询每颗的前三名

分析：子查询：SELECT num FROM score AS s2 WHERE s1.course_id = s2.course_id GROUP BY num ORDER BY num DESC LIMIT 3,1 查询课程id为course_id 的第四名的成绩，前三名不一定有三个人所以我们使用group by去重

-- 35、查询每门课程成绩最好的前两名；
				SELECT B.student_id, B.course_id, B.num, B.third FROM
				(SELECT s1.student_id,course_id, s1.num,(SELECT num FROM score AS s2 WHERE s1.course_id = s2.course_id GROUP BY num ORDER BY num DESC LIMIT 2,1) AS third
				FROM score AS s1) AS B WHERE B.num > B.third ORDER BY B.course_id asc;

13、笛卡尔积

思路：当我们需要比较在一张表内部的同一列比较时，要用到笛卡尔积

-- 26、查询同名同姓学生名单，并统计同名人数；
			-- 思路：当我们需要比较在一张表内部的同一列比较时，要用到笛卡尔积
			SELECT s1.sid, s1.gender, s1.class_id, s1.sname FROM student s1, student s2
			WHERE s1.sid  != s2.sid AND s1.sname = s2.sname;