首页 > 其他分享 >2022-08-17 第五组 罗佳明 学习笔记

2022-08-17 第五组 罗佳明 学习笔记

时间：2022-08-17 22:47:55浏览次数：67  
标签：JOIN NAME 17 08 score 第五组 id SELECT LEFT

一、学习重点

子查询（自连接）

  • 标量子查询：结果集只有一行一列（单行子查询）
  • 列子查询：结果集有一列多行
  • 行子查询：结果集一行多列
  • 表子查询：结果集多行多列

日期格式

二、学习内容

需求三

-- 11.查询老师的信息和他所带的科目的平均分
SELECT
	t.id,
	t.NAME,
	c.id cid,
	c.NAME cname,
	avg( r.score ) 
FROM
	teacher t
	LEFT JOIN course c ON t.id = c.t_id
	LEFT JOIN scores r ON r.c_id = c.id 
GROUP BY
	t.id,
	t.NAME,
	c.id,
	c.NAME;
-- 12.查询被"Tom"和"Jerry"教的课程的最高分和最低分
SELECT
	t.id,
	t.NAME,
	c.id cid,
	c.NAME cname,
	max( r.score ),
	min( r.score ) 
FROM
	teacher t
	LEFT JOIN course c ON t.id = c.t_id
	LEFT JOIN scores r ON r.c_id = c.id 
GROUP BY
	t.id,
	t.NAME,
	c.id,
	c.NAME 
HAVING
	t.NAME IN ( 'Tom', 'Jerry' );
-- 13.查询每个学生的最好成绩的科目名称（子查询）
SELECT
	t.id,
	t.sname,
	r.c_id,
	c.NAME,
	t.score 
FROM
	(
	SELECT
		s.id,
		s.NAME sname,
		max( r.score ) score 
	FROM
		student s
		LEFT JOIN scores r ON r.s_id = s.id 
	GROUP BY
		s.id,
		s.NAME 
	) t
	LEFT JOIN scores r ON r.s_id = t.id 
	AND r.score = t.score
	LEFT JOIN course c ON r.c_id = c.id;
-- 14.查询所有学生的课程及分数
SELECT
	s.id,
	s.NAME,
	c.id,
	c.NAME,
	r.score 
FROM
	student s
	LEFT JOIN scores r ON s.id = r.s_id
	LEFT JOIN course c ON c.id = r.c_id;
-- 15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）
SELECT
	* 
FROM
	student s 
WHERE
	s.id IN (
	SELECT
		r.s_id 
	FROM
		scores r 
	WHERE
	r.c_id = 1 
	AND r.score > 60)
--------------------------------------------------------
SELECT
	s.*,
	r.* 
FROM
	student s
	LEFT JOIN scores r ON s.id = r.s_id 
WHERE
	r.c_id = 1 
	AND r.score > 60

需求四

-- 16. 查询平均成绩大于等于70的所有学生学号、姓名和平均成绩
SELECT
	s.id,
	s.NAME,
	t.score 
FROM
	student s
	LEFT JOIN ( SELECT r.s_id, avg( r.score ) score FROM scores r GROUP BY r.s_id ) t ON s.id = t.s_id 
WHERE
	t.score >= 70;
-- 17.查询有不及格课程的学生信息
SELECT
	* 
FROM
	student s 
WHERE
	id IN ( SELECT r.s_id FROM scores r GROUP BY r.s_id HAVING min( r.score ) < 60 );
-- 18.查询每门课程有成绩的学生人数
SELECT
	c.id,
	c.NAME,
	t.number 
FROM
	course c
	LEFT JOIN ( SELECT r.c_id, count(*) number FROM scores r GROUP BY r.c_id ) t ON c.id = t.c_id;
----------------------------------------------------
SELECT
	c.id,
	c.NAME,
	count(*) 
FROM
	course c
	LEFT JOIN scores r ON c.id = r.c_id 
GROUP BY
	c.id,
	c.NAME;
-- 19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列
SELECT
	c.id,
	c.NAME,
	avg( score ) score 
FROM
	course c
	LEFT JOIN scores r ON c.id = r.c_id 
GROUP BY
	c.id,
	c.NAME 
ORDER BY
	score DESC,
	c.id ASC;
-- 20.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩
SELECT
	s.id,
	s.NAME sname,
	avg( r.score ) score 
FROM
	student s
	LEFT JOIN scores r ON r.s_id = s.id
	LEFT JOIN course c ON c.id = r.c_id 
GROUP BY
	s.id,
	s.NAME 
HAVING
	score > 65;

需求五

-- 21.查询有且仅有一门课程成绩在80分以上的学生信息
SELECT
	* 
FROM
	student 
WHERE
	id IN ( SELECT r.s_id FROM scores r WHERE r.score > 80 GROUP BY r.s_id HAVING COUNT(*) = 1 );
----------------------------------------------------------------------------
SELECT
	s.id,
	s.NAME,
	s.gender 
FROM
	student s
	LEFT JOIN scores r ON s.id = r.s_id 
WHERE
	r.score > 80 
GROUP BY
	s.id,
	s.NAME,
	s.gender 
HAVING
	count(*) = 1
-- 22.查询出只有三门课程的学生的学号和姓名
SELECT
	* 
FROM
	student s 
WHERE
	id IN ( SELECT r.s_id FROM scores r GROUP BY r.s_id HAVING count(*) = 3 );
----------------------------------------------------------------------------
SELECT
	s.id,
	s.NAME,
	s.gender 
FROM
	student s
	LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
	s.id,
	s.NAME,
	s.gender 
HAVING
	count(*) = 3
-- 23.查询有不及格课程的课程信息
SELECT
	* 
FROM
	course c 
WHERE
	id IN (
	SELECT
		r.c_id 
	FROM
		scores r 
	GROUP BY
		r.c_id 
	HAVING
	min( r.score ) < 60 
	)
----------------------------------------------------------------------------
SELECT
	c.id,
	c.NAME 
FROM
	course c
	LEFT JOIN scores sc ON c.id = sc.c_id 
GROUP BY
	sc.c_id,
	c.NAME 
HAVING
	min( sc.score ) < 60;
-- 24.查询至少选择4门课程的学生信息
SELECT
	* 
FROM
	student 
WHERE
	id IN (
	SELECT
		r.s_id 
	FROM
		scores r 
	GROUP BY
		r.s_id 
	HAVING
	count(*) >= 4 
	)
----------------------------------------------------------------------------
SELECT
	s.id,
	s.NAME 
FROM
	student s
	LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
	s.id,
	s.NAME 
HAVING
	count(*) >= 4;
-- 25.查询没有选全所有课程的同学的信息
SELECT
	* 
FROM
	student 
WHERE
	id IN (
	SELECT
		r.s_id 
	FROM
		scores r 
	GROUP BY
		r.s_id 
	HAVING
	count(*) != 5
	)

-- 26.查询选全所有课程的同学的信息
SELECT
	s.id,
	s.NAME,
	count(*) number 
FROM
	student s
	LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
	s.id,
	s.NAME 
HAVING
	number = ( SELECT count(*) FROM course );
-- 27.查询各学生都选了多少门课
SELECT
	s.id,
	s.NAME,
	count(*) number 
FROM
	student s
	LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
	s.id,
	s.NAME
-- 28.查询课程名称为"java"，且分数低于60分的学生姓名和分数
SELECT
	s.id,
	s.NAME,
	r.score 
FROM
	student s
	LEFT JOIN scores r ON s.id = r.s_id
	LEFT JOIN course c ON r.c_id = c.id 
WHERE
	c.NAME = 'java' 
	AND r.score < 60;
-- 29.查询学过"Tony"老师授课的同学的信息
SELECT
	s.id,
	s.NAME 
FROM
	student s
	LEFT JOIN scores r ON r.s_id = s.id
	LEFT JOIN course c ON c.id = r.c_id
	LEFT JOIN teacher t ON t.id = c.t_id 
WHERE
	t.NAME = 'Tom';
-- 30.查询没学过"Tony"老师授课的学生信息
SELECT
	* 
FROM
	student 
WHERE
	id NOT IN (
	SELECT DISTINCT
		s.id 
	FROM
		student s
		LEFT JOIN scores r ON r.s_id = s.id
		LEFT JOIN course c ON c.id = r.c_id
		LEFT JOIN teacher t ON t.id = c.t_id 
	WHERE
	t.NAME = 'Tom' 
	)

三、学习总结

标签：JOIN,NAME,17,08,score,第五组,id,SELECT,LEFT
From： https://www.cnblogs.com/ljmskipper/p/16596324.html

相关文章

  • 【IELTS】-2022.8.17开始
    【120】-irrelevant-不相关的，离题的--TheSellercanaskamoderatortoremoveanyirrelevantcommentsfromtheirthread.卖家也可以要求管理员删去任何离题的评语......
  • 22/8/17深入理解计算机系统第七章笔记
    7.13库打桩机制库打桩：允许截获对共享库的调用，转而执行自己的代码。使用这个机制可以追踪某个特殊库函数的调用次数，验证和追踪它的输入和输出值，或者替换它。需要创建一个......
  • 8.17
    CF1718A题意：给定一个序列，每次可以花费\(\lceil\frac{r-l+1}{2}\rceil\)的代价选择一段区间\([l,r]\)，并把区间里的每个数字异或上\(x\)，最少花费多少代价可以让整个序列变......
  • 20220817 第一组 于芮 mysql数据库查询（第三十四天）
     小白成长记——第三十四天   今天主要学习了mysql数据库的查询语句，对于一个合格的程序猿来说，掌握数据库的查询语句是非常重要的，所以今天不仅学习了理论知识，还作了......
  • 20220806-第八组-王凌霄-学习笔记
    List：数据是有顺序（添加的先后顺序）的，数据是可以重复的ArrayList：内部结构是数组，比较适合做高频率的查找、遍历LinkedList：双向链表，比较适合做高频率的新增和删除继承和实现......
  • 2022-8-17数据库查询联系
    DQL查询语言子查询按照结果集的行列数不同，子查询可以分为以下几类：标量子查询：结果集只有一行一列（单行子查询）列子查询：结果集有一列多行行子查询：结果集有一行多列......
  • Codeforces 1713C - Build Permutation
    题意为给出一个长度为n的空数组，数组下标为0至n-1。我们需要在数组中的每个位置上填上合适的数A[i]，使得i+A[i]为完全平方数。并且数组最后需为0至n-1的一个排列。......
  • 2022-08-17 第三小组 陈迪 学习笔记
    --1根据学生学号查询2020年-1-1之后借过哪些书籍SELECTs.sid,s.sname,bk.bname,b.bdateFROMborrowrecoredbLEFTJOINstudentsONs.cid=b.cidLEFTJOINbook......
  • 2022-08-17 第六小组 高佳誉 学习笔记
    DQL查询语言子查询按照结果集的行列数不同，子查询可以分为以下几类：标量子查询：结果集只有一行一列（单行子查询）列子查询：结果集有一列多行行子查询：结果集有一行多列表子......
  • 2022-08-16 第五组 赖哲栋 学习笔记
    DQL数据库查询语言重点，DQL是我们每天都要接触编写最多也是最难的SQL，该语言用来查询记录，不会修改数据库和表结构。构建数据库创建一张student表：DROPTABLEIFEXISTSst......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99