表结构:
`student`('id','name','code','age','sex') 学生表
`teacher`('id','name') 教师表
`course`('id','name','teacher_id') 课程表
`score`('student_id','course_id','score') 成绩表
问题:
1: 查询 001 课程比 002 课程成绩高的所有学生的信息
2: 查询所有课程成绩小于 60 分的同学的信息名
3: 查询平均成绩大于 60 分的同学平均成绩和学生的信息
4: 查询所有同学的信息, 选课数, 总成绩
5: 查询没学过 "叶平老师" 课的同学的信息
6: 查询学过 "001" 并且也学过编号 "002" 课程的同学的信息
7: 查询没有学全所有课的同学的信息
8: 查询至少有一门课与学号为 "1001" 的同学所学相同同学的信息
9: 查询至少学过学号为 1001 的同学所有课程的 其他同学的信息
10: 把 "score" 表中 "叶平老师" 教的课的成绩都更改为此课程的平均成绩
解决:
创建表
- CREATE TABLE `student` (
- `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
- `name` varchar(30) DEFAULT NULL,
- `code` varchar(15) DEFAULT NULL,
- `age` int(11) DEFAULT NULL,
- `sex` int(11) DEFAULT '1' COMMENT '1 男 2 女',
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
- CREATE TABLE `teacher` (
- `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
- `name` varchar(30) DEFAULT ''COMMENT'老师名',
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
- CREATE TABLE `course` (
- `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
- `name` varchar(30) DEFAULT NULL COMMENT '课程名',
- `teache_id` int(11) DEFAULT NULL COMMENT '教师 ID',
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
- CREATE TABLE `score` (
- `student_id` int(11) DEFAULT NULL COMMENT '学生 ID',
- `course_id` int(11) DEFAULT NULL COMMENT '课程 ID',
- `score` int(11) DEFAULT NULL COMMENT '成绩'
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
问题 1: 查询 001 课程比 002 课程成绩高的所有学生的信息;
SELECT st.* FROM student st WHERE ( SELECT sc.`score` FROM score sc LEFT JOIN `course` co ON co.`id`=sc.`course_id` WHERE st.`id` = sc.`student_id` AND co.`name` = '001' )> ( SELECT sc.`score` FROM score sc LEFT JOIN `course` co ON co.`id`=sc.`course_id` WHERE st.`id` = sc.`student_id` AND co.`name` = '002' );
分解:
1: 按题意理解, 写的如下 SQL
SELECT st.* FROM student st WHERE ( )> ( );
2: 获取指定 ID 的学生的 001 课程的成绩
SELECT sc.score FROM score sc LEFT JOIN course co ON co.id=sc.course_id WHERE [指定 ID] = sc.student_id AND co.name = '001';
3: 获取指定 ID 的学生的 002 课程的成绩
SELECT sc.score FROM score sc LEFT JOIN course co ON co.id=sc.course_id WHERE [指定 ID] = sc.student_id AND co.name = '002';
4: 组装 SQL
SELECT st.* FROM student st WHERE ( SELECT sc.score FROM score sc LEFT JOIN course co ON co.id=sc.course_id WHERE st.id = sc.student_id AND co.name = '001' )> ( SELECT sc.score FROM score sc LEFT JOIN course co ON co.id=sc.course_id WHERE st.id = sc.student_id AND co.name = '002' );
问题 2: 查询所有课程成绩小于 60 分的同学的信息;
SELECT st.* FROM `student` st WHERE st.id NOT IN ( SELECT sc.`student_id` FROM `score` sc WHERE sc.`score`> 60 );
分解:
1: 先是获取成绩大于 60 的同学 (题意是所有成绩都小于 60 的才符合, 那么排除只要有一门成绩大于 60 的即可)
SELECT sc.student_id FROM score sc WHERE sc.score> 60;
2: 然后获取剩余的学生信息 (通过 NOT IN)
SELECT st.* FROM student st WHERE st.id NOT IN ( SELECT sc.student_id FROM score sc WHERE sc.score> 60 );
问题 3: 查询平均成绩大于 60 分的同学的学号和平均成绩和学生的信息;
SELECT st.*,AVG( sc.`score`) as AvgScore FROM `score` sc LEFT JOIN student st ON st.`id` = sc.`student_id` GROUP BY sc.`student_id` HAVING AVG( sc.`score` )> 60;
注意:
HAVING 应用与对 where 和 group by 查询出来的分组进行过滤, 查询出满足条件的分组结果.
1> having 只能应用与 group by(分组统计语句中)
2> where 是用于在初始表中筛选查询, having 用于在 where 和 group by 结果分组中查询
3> having 子句中的每一个元素也必须出现在 select 列表中
4> having 语句可以使用聚合函数, 而 where 不使用
问题 4: 查询所有同学的信息, 选课数, 总成绩;
SELECT st.*,(SELECT COUNT( sc.`course_id`) FROM `score` sc WHERE sc.`student_id` = st.`id` ) courseNum, (SELECT SUM(sc.`score`) FROM `score` sc WHERE sc.`student_id` = st.`id`) scoreNum FROM student st;
分解:
1: 获取所有同学的信息
SELECT st.* FROM student st;
2: 获取选课数 ( 每一个同学都是一个特定的 ID)
SELECT COUNT( sc.course_id) FROM score sc WHERE sc.student_id = [特定 ID];
3: 获取总成绩 (每一个同学的)
SELECT SUM(sc.score) FROM score sc WHERE sc.student_id = [特定 ID];
4: 组装 SQL
SELECT st.*,(SELECT COUNT( sc.course_id) FROM score sc WHERE sc.student_id = st.id ) courseNum, (SELECT SUM(sc.score) FROM score sc WHERE sc.student_id = st.id) scoreNum FROM student st;
问题 5: 查询没学过 "叶平老师" 课的同学信息
SELECT st.* FROM `student` st WHERE st.`id` NOT IN ( SELECT sc.`student_id` FROM `score` sc LEFT JOIN `course` co ON co.`id` = sc.`course_id` LEFT JOIN `teacher` te ON te.`id` = co.`teache_id` WHERE te.`name` = '叶平老师' );
分解:
1: 根据题意, 取反, 先获取学过 "叶平老师" 课的同学
SELECT sc.student_id FROM score sc LEFT JOIN course co ON co.id = sc.course_id LEFT JOIN teacher te ON te.id = co.teache_id WHERE te.name = '叶平老师';
2: 然后在取反, 获取剩余的学生信息即可
SELECT st.* FROM student st WHERE st.id NOT IN ( SELECT sc.student_id FROM score sc LEFT JOIN course co ON co.id = sc.course_id LEFT JOIN teacher te ON te.id = co.teache_id WHERE te.name = '叶平老师' );
问题 6: 查询学过 "001" 也学过编号 "002" 课程的同学信息
解决方法 1:
SELECT st.* FROM `student` st WHERE (SELECT count(*) FROM `score` sc LEFT JOIN `course` co ON co.`id` = sc.`course_id` WHERE sc.`student_id` = st.`id` AND co.`name` = '001')> 0 AND (SELECT count(*) FROM `score` sc LEFT JOIN `course` co ON co.`id` = sc.`course_id` WHERE sc.`student_id` = st.`id` AND co.`name` = '002')> 0;
分解:
1: 统计某一学生是否学过 001 课程的信息
SELECT count(*) FROM score sc LEFT JOIN course co ON co.id = sc.course_id WHERE sc.student_id = [特定 ID] AND co.name = '001';
2: 统计某一学生是否学过 002 课程的信息
SELECT count(*) FROM score sc LEFT JOIN course co ON co.id = sc.course_id WHERE sc.student_id = [特定 ID] AND co.name = '002';
3: 直接获取 条件 1 和 条件 2 同时成立的数据
SELECT st.* FROM student st WHERE (SELECT count() FROM score sc LEFT JOIN course co ON co.id = sc.course_id WHERE sc.student_id = st.id AND co.name = '001')> 0 AND (SELECT count() FROM score sc LEFT JOIN course co ON co.id = sc.course_id WHERE sc.student_id = st.id AND co.name = '002')> 0;
解决方法 2:
SELECT * FROM `student` st WHERE st.`id` IN ( SELECT st1.student_id FROM ( SELECT sc.`student_id` FROM `score` sc LEFT JOIN `course` co ON co.`id` = sc.`course_id` WHERE co.`name` = '001' ) st1,( SELECT sc.`student_id` FROM `score` sc LEFT JOIN `course` co ON co.`id` = sc.`course_id` WHERE co.`name` = '002' )st2 WHERE st1.`student_id` = st2.`student_id` );
或者
SELECT st.* FROM `student` st,(SELECT st1.student_id FROM ( SELECT sc.`student_id` FROM `score` sc LEFT JOIN `course` co ON co.`id` = sc.`course_id` WHERE co.`name` = '001' ) st1,( SELECT sc.`student_id` FROM `score` sc LEFT JOIN `course` co ON co.`id` = sc.`course_id` WHERE co.`name` = '002' )st2 WHERE st1.`student_id` = st2.`student_id`) st3 WHERE st3.`student_id`= st.`id`;
分解:
1: 获取学过 001 课程的学生 ID
SELECT sc.student_id FROM score sc LEFT JOIN course co ON co.id = sc.course_id WHERE co.name = '001';
2: 获取学过 001 课程的学生 ID
SELECT sc.student_id FROM score sc LEFT JOIN course co ON co.id = sc.course_id WHERE co.name = '002'
3: 获取即学过 001 又学过 002 课程的学生 ID
SELECT st1.student_id FROM ( SELECT sc.student_id FROM score sc LEFT JOIN course co ON co.id = sc.course_id WHERE co.name = '001' ) st1, ( SELECT sc.student_id FROM score sc LEFT JOIN course co ON co.id = sc.course_id WHERE co.name = '002' ) st2 WHERE st1.student_id = st2.student_id;
4: 根据学生 ID 获取学生信息 (可以有多种写法)
-- IN 写法:
SELECT * FROM student st WHERE st.id IN ( SELECT st1.student_id FROM ( SELECT sc.student_id FROM score sc LEFT JOIN course co ON co.id = sc.course_id WHERE co.name = '001' ) st1,( SELECT sc.student_id FROM score sc LEFT JOIN course co ON co.id = sc.course_id WHERE co.name = '002' )st2 WHERE st1.student_id = st2.student_id );
-- 把结果当作一个表, 起别名再去查询:
SELECT st.* FROM student st,(SELECT st1.student_id FROM ( SELECT sc.student_id FROM score sc LEFT JOIN course co ON co.id = sc.course_id WHERE co.name = '001' ) st1,( SELECT sc.student_id FROM score sc LEFT JOIN course co ON co.id = sc.course_id WHERE co.name = '002' )st2 WHERE st1.student_id = st2.student_id) st3 WHERE st3.student_id= st.id;
问题 7: 查询没有学全所有课的同学的信息
SELECT st.* FROM `student` st WHERE (SELECT count(*) FROM `score` sc WHERE sc.`student_id` = st.`id`) < (SELECT count(*) FROM `course`);
分解:
1: 获取课的总数;
SELECT count(*) FROM course;
2: 获取每个人的学习的课的总数;
SELECT count(*) FROM score sc WHERE sc.student_id = [特定 ID];
3: 然后查询的是 没有学全所有课的学生, 也就是学习的课数小于总课数
(SELECT count(* ) FROM score sc WHERE sc.student_id = [特定 ID]) < (SELECT count(*) FROM course );
4: 获取学生的所有信息, 组合 sql 如下:
SELECT st.* FROM student st WHERE (SELECT count(* ) FROM score sc WHERE sc.student_id = st.id) < (SELECT count(*) FROM course);
问题 8: 查询至少有一门课与学号为 1001 的同学所学相同同学的信息
解决方法 1:
SELECT DISTINCT st.* FROM `student` st INNER JOIN `score` sc ON sc.`student_id` = st.`id` WHERE sc.`course_id` IN ( SELECT sc.`course_id` FROM `student` st LEFT JOIN `score` sc ON sc.`student_id` = st.`id` WHERE st.`code` = '1001' );
分解:
先获取到学号为 1001 同学的所有学习课程, 然后根据获取的课程 ID 去查所有的学生信息, 然后 DISTINCT 去重即可.
1: 先获取到学号为 1001 同学的所有学习课程;
SELECT sc.course_id FROM student st LEFT JOIN score sc ON sc.student_id = st.id WHERE st.code = '1001';
2: 然后根据获取的课程 ID 去查所有的学生信息, 同时去重即可;
SELECT DISTINCT st.* FROM student st INNER JOIN score sc ON sc.student_id = st.id WHERE sc.course_id IN ( SELECT sc.course_id FROM student st LEFT JOIN score sc ON sc.student_id = st.id WHERE st.code = '1001' );
解决方法 2:
SELECT st.* FROM `student` st WHERE st.`id` IN ( SELECT DISTINCT sc.`student_id` FROM `score` sc WHERE sc.`course_id` IN ( SELECT sc.`course_id` FROM `student` st LEFT JOIN `score` sc ON sc.`student_id` = st.`id` WHERE st.`code` = '1001' ) );
分解:
先获取学号为 1001 学生的课程, 然后根据获取到课程 ID 获取学生 ID, 然后去重, 然后获取学生信息.(嵌套子查询)
1: 先获取到学号为 1001 同学的所有学习课程;
SELECT sc.course_id FROM student st LEFT JOIN score sc ON sc.student_id = st.id WHERE st.code = '1001';
2: 然后根据获取到课程 ID 获取学生 ID;
SELECT DISTINCT sc.student_id FROM score sc WHERE sc.course_id IN ( SELECT sc.course_id FROM student st LEFT JOIN score sc ON sc.student_id = st.id WHERE st.code = '1001' );
3: 然后获取学生信息
SELECT st.* FROM student st WHERE st.id IN ( SELECT DISTINCT sc.student_id FROM score sc WHERE sc.course_id IN ( SELECT sc.course_id FROM student st LEFT JOIN score sc ON sc.student_id = st.id WHERE st.code = '1001' ) );
问题 9: 查询至少学过学号为 1001 的同学所有课程的 其他同学的信息
SELECT st.* FROM `student` st WHERE st.`id` IN ( SELECT sc1.`student_id` FROM ( SELECT sc.* FROM `score` sc WHERE sc.`course_id` IN ( SELECT sc.`course_id` FROM `student` st LEFT JOIN `score` sc ON sc.`student_id` = st.`id` WHERE st.`code` = '1001' ) ) sc1 GROUP BY sc1.`student_id` HAVING COUNT(*) = ( SELECT COUNT(*) FROM `student` st LEFT JOIN `score` sc ON sc.`student_id` = st.`id` WHERE st.`code` = '1001' ) );
分解:
1: 获取学号为 1001 的同学的所有课程 ID;
SELECT sc.course_id FROM student st LEFT JOIN score sc ON sc.student_id = st.id WHERE st.code = '1001';
2: 获取对应课程的所有学习同学的 ID, 并且分组;
SELECT sc.student_id FROM score sc WHERE sc.course_id IN ( SELECT sc.course_id FROM student st LEFT JOIN score sc ON sc.student_id = st.id WHERE st.code = '1001' ) GROUP BY sc.student_id;
到此为止发现问题: 只学了其中一门的也被查询出来了, 应该去掉.
3: 获取学号为 1001 的同学所学课程数量
SELECT COUNT(*) FROM student st LEFT JOIN score sc ON sc.student_id = st.id WHERE st.code = '1001';
4: 所以所有的符合条件的学生的 ID 集为:
SELECT sc.student_id FROM score sc WHERE sc.course_id IN ( SELECT sc.course_id FROM student st LEFT JOIN score sc ON sc.student_id = st.id WHERE st.code = '1001' ) GROUP BY sc.student_id HAVING COUNT() = ( SELECT COUNT() FROM student st LEFT JOIN score sc ON sc.student_id = st.id WHERE st.code = '1001' );
5: 组装 SQL, 查询学生信息.
SELECT st.* FROM student st WHERE st.id IN ( SELECT sc.student_id FROM score sc WHERE sc.course_id IN ( SELECT sc.course_id FROM student st LEFT JOIN score sc ON sc.student_id = st.id WHERE st.code = '1001' ) GROUP BY sc.student_id HAVING COUNT() = ( SELECT COUNT() FROM student st LEFT JOIN score sc ON sc.student_id = st.id WHERE st.code = '1001' ) );
问题 10: 把 "score" 表中 "叶平老师" 教的课的成绩都更改为此课程的平均成绩
UPDATE `score` sc SET sc.`score` = ( SELECT AVG(sc1.`score`) avgScore FROM (SELECT sc.* FROM `score` sc LEFT JOIN `course` co ON co.`id` = sc.`course_id` LEFT JOIN `teacher` te ON te.`id` = co.`teache_id` WHERE te.`name` = '叶平老师' ) sc1 ) WHERE sc.`course_id` = ( SELECT co.`id` FROM `course` co LEFT JOIN `teacher` te ON te.`id` = co.`teache_id` WHERE te.`name` = '叶平老师' );
分解:
1: 理解为修改特定 ID 的数据
UPDATE score sc SET sc.score = () WHERE sc.course_id = ();
2: 要修改的数据 ( 获取 "score" 表中 "叶平老师" 教的课的成绩)
SELECT sc.* FROM score sc LEFT JOIN course co ON co.id = sc.course_id LEFT JOIN teacher te ON te.id = co.teache_id WHERE te.name = '叶平老师'
3: 确定要修改的值 (获取要修改的数据的平均值)
SELECT AVG(sc1.score) avgScore FROM (SELECT sc.* FROM score sc LEFT JOIN course co ON co.id = sc.course_id LEFT JOIN teacher te ON te.id = co.teache_id WHERE te.name = '叶平老师' ) sc1
4: 确定修改的条件 (获取叶平老师所带课程的 ID)
SELECT co.* FROM course co LEFT JOIN teacher te ON te.id = co.teache_id WHERE te.name = '叶平老师'
5: 组装 SQL 即可
UPDATE score sc SET sc.score = ( SELECT AVG(sc1.score) avgScore FROM (SELECT sc.* FROM score sc LEFT JOIN course co ON co.id = sc.course_id LEFT JOIN teacher te ON te.id = co.teache_id WHERE te.name = '叶平老师' ) sc1 ) WHERE sc.course_id = ( SELECT co.id FROM course co LEFT JOIN teacher te ON te.id = co.teache_id WHERE te.name = '叶平老师' );
来源: https://www.cnblogs.com/laowenBlog/p/8795865.html