mysql练习之学生表教师表课程表成绩表

最新推荐文章于 2023-09-26 16:16:59 发布
最新推荐文章于 2023-09-26 16:16:59 发布
阅读量3.4k 收藏 26
点赞数 5
分类专栏： mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/C_time/article/details/99696151
版权

建表语句在最后

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

-- 1.这样就查出来了 满足 查询"01"课程比"02"课程成绩高的学生 的id
select distinct sc1.s_id
from score sc1, score sc2
where sc1.c_id = '01' and sc2.c_id = '02' and sc1.s_score > sc2.s_score and sc1.s_id = sc2.s_id 

-- 2.根据id可以查出 唯一的学生信息
select distinct s.s_id, s.s_name,s.s_birth, s.s_sex
from student s, score sc
where s.s_id in 
(select distinct sc1.s_id
from score sc1, score sc2
where sc1.c_id = '01' and sc2.c_id = '02' and sc1.s_score > sc2.s_score and sc1.s_id = sc2.s_id)
-- 3.那么怎么根据id查出一个学生的所有课程分数呢
select sc.s_id, c.c_id, c.c_name, sc.s_score
from score sc, course c
where sc.s_id in ('02', '03')  and c.c_id = sc.c_id


-- 4.将三者合起来
select distinct s.s_id, s.s_name,s.s_birth, s.s_sex, c.c_id, c.c_name, sc.s_score
from student s, score sc, course c
where s.s_id in 
(select distinct sc1.s_id
from score sc1, score sc2
where sc1.c_id = '01' and sc2.c_id = '02' and sc1.s_score > sc2.s_score and sc1.s_id = sc2.s_id)
and c.c_id = sc.c_id

注意这样出来的的数据 是好多行 错误的 钱电和李云分别有好多行成绩

02	钱电	1990-12-21	男	01	语文	80
02	钱电	1990-12-21	男	01	语文	70
02	钱电	1990-12-21	男	01	语文	50
原因是  sc.s_id = s.s_id   忘记加上 
where 条件里面的score sc跟form里面的score sc1 score sc2没有关系
所以需要重新限定条件

select distinct s.s_id, s.s_name,s.s_birth, s.s_sex, c.c_id, c.c_name, sc.s_score
from student s, score sc, course c
where s.s_id in 
(select distinct sc1.s_id
from score sc1, score sc2
where sc1.c_id = '01' and sc2.c_id = '02' and sc1.s_score > sc2.s_score and sc1.s_id = sc2.s_id)
and c.c_id = sc.c_id
and sc.s_id = s.s_id
这样就对了
02	钱电	1990-12-21	男	01	语文	70
04	李云	1990-08-06	男	01	语文	50
02	钱电	1990-12-21	男	02	数学	60
04	李云	1990-08-06	男	02	数学	30
02	钱电	1990-12-21	男	03	英语	80
04	李云	1990-08-06	男	03	英语	20
还有更简便的方法 left join

select s.s_id, s.s_name,s.s_birth, s.s_sex, c.c_id, c.c_name, stemp.s_score
from score sc1
left join score stemp on sc1.s_id = stemp.s_id
left join student s on s.s_id = sc1.s_id
left join course c on stemp.c_id = c.c_id 
where sc1.c_id = '01' 
and 
(sc1.s_score > (select sc2.s_score from score sc2 where sc2.c_id = '02' and sc2.s_id = sc1.s_id))

这里要注意一共使用了三个score表
还是不太理解这个left join

– 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

select distinct s.s_id, s.s_name,s.s_birth, s.s_sex, c.c_id, c.c_name, sc.s_score
from student s, score sc, course c
where s.s_id in 
(select distinct sc1.s_id
from score sc1, score sc2
where sc1.c_id = '01' and sc2.c_id = '02' and sc1.s_score < sc2.s_score and sc1.s_id = sc2.s_id)
and c.c_id = sc.c_id
and sc.s_id = s.s_id

01	赵雷	1990-01-01	男	01	语文	80
05	周梅	1991-12-01	女	01	语文	76
01	赵雷	1990-01-01	男	02	数学	90
05	周梅	1991-12-01	女	02	数学	87
01	赵雷	1990-01-01	男	03	英语	99

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

select sc.s_id, s.s_name, avg(sc.s_score) as '平均成绩'
from score sc, student s
where s.s_id = sc.s_id
group by sc.s_id
having avg(sc.s_score) >= 60


01	赵雷	89.6667
02	钱电	70
03	孙风	80
05	周梅	81.5
07	郑竹	93.5



-- 另外一种使用join的写法 没看懂
select
    b.s_id,
    b.s_name,
    round(avg(a.s_score), 2) as avg_score
from
    student b
join score a on
    b.s_id = a.s_id
group by
    b.s_id,
    b.s_name
having
    round(avg(a.s_score), 2)>= 60;
		
-- 还有使用left join 跟我使用where差不多
select
    t2.s_id,
    t2.s_name,
    count(t1.c_id) as sun_course,
    sum(t1.s_score) as sum_score
from
    student t2
left join score t1 on
    t1.s_id = t2.s_id
group by
    t1.s_id

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

		-- ifnull(sc.s_score, 0)
		-- ifnull(cast(avg(sc.s_score) as decimal(18,2)),0) 平均分 可以去掉ifnull， 
		-- cast(avg(sc.s_score) as decimal(18,2))



select sc.s_id, s.s_name, avg(sc.s_score) as '平均成绩'
from score sc, student s
where s.s_id = sc.s_id
group by sc.s_id
having ifnull(avg(sc.s_score),0) < 60



select sc.s_id, s.s_name, avg(sc.s_score) as '平均成绩'
from score sc, student s
where s.s_id = sc.s_id
group by sc.s_id
having ifnull(cast(avg(sc.s_score) as decimal(18,2)),0) < 60


04	李云	33.3333
06	吴兰	32.5

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

SELECT s.s_id, s.s_name, count(*) AS '选课总数', sum(sc.s_score) AS '总成绩'
FROM student s, score sc
WHERE s.s_id = sc.s_id
GROUP BY sc.s_id


01	赵雷	3	269
02	钱电	3	210
03	孙风	3	240
04	李云	3	100
05	周梅	2	163
06	吴兰	2	65
07	郑竹	2	187

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

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

1

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

-- 先查出 张三教的课程号 使用course 和 teacher 表
-- 再根据课程号 查出 成绩表中含有的课程号的学员id
-- 根据学员id 查出所有信息
SELECT c.c_id
FROM course c , teacher t
WHERE c.t_id = t.t_id and t.t_name = '张三';
-- 还是直接写比较好 使用了join 刚刚试了left join right join 都不行 没理解

SELECT s.s_id, s.s_name, s.s_birth, s.s_sex
FROM student s 
JOIN score sc ON sc.s_id = s.s_id 
JOIN course c ON sc.c_id = c.c_id
JOIN teacher t ON c.t_id = t.t_id AND t.t_name = '张三';


01	赵雷	1990-01-01	男
02	钱电	1990-12-21	男
03	孙风	1990-05-20	男
04	李云	1990-08-06	男
05	周梅	1991-12-01	女
07	郑竹	1989-07-01	女

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

select s.s_id, s.s_name, s.s_birth, s.s_sex
from student s 
where s.s_id not in(select sc.s_id from score sc
join course c on sc.c_id = c.c_id
join teacher t on c.t_id = t.t_id and t.t_name = '张三');

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

select s.s_id, s.s_name, s.s_birth, s.s_sex
from student s
join score sc1 on sc1.s_id = s.s_id 
join score sc2 on sc2.s_id = s.s_id
and sc1.s_id = sc2.s_id 
and sc1.c_id = '01'
and sc2.c_id = '02';


-- 用两个score分别统计01 02课程
-- 还要注意三个表 两两关联

01	赵雷	1990-01-01	男
02	钱电	1990-12-21	男
03	孙风	1990-05-20	男
04	李云	1990-08-06	男
05	周梅	1991-12-01	女

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

select s.s_id, s.s_name, s.s_birth, s.s_sex
from student s
join score sc1 on sc1.s_id = s.s_id and sc1.c_id = '01'
where sc1.s_id not in 
(select sc2.s_id from score sc2 where  sc2.c_id = '02');
												
												
06	吴兰	1992-03-01	女

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

SELECT s.s_id, s.s_name, s.s_birth, s.s_sex
FROM student s
JOIN score sc1 ON sc1.s_id = s.s_id 
GROUP BY sc1.s_id
HAVING count(*) < (select count(*) from course c)


05	周梅	1991-12-01	女
06	吴兰	1992-03-01	女
07	郑竹	1989-07-01	女

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

SELECT DISTINCT s.s_id, s.s_name, s.s_birth, s.s_sex
FROM student s
JOIN score sc1 ON sc1.s_id = s.s_id and sc1.s_id != '01'
AND sc1.c_id IN (SELECT sc2.c_id FROM score sc2 WHERE sc2.s_id = '01')


02	钱电	1990-12-21	男
03	孙风	1990-05-20	男
04	李云	1990-08-06	男
05	周梅	1991-12-01	女
06	吴兰	1992-03-01	女
07	郑竹	1989-07-01	女

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

四句就能解决 字符串拼接！！！！

-- 这个太长了 不看也罢 具体过程在 我建表的文件库里存着查询语句及分析


select s.s_id, s.s_name, s.s_birth, s.s_sex
from student s, 
(
select t.a from
(SELECT sc2.s_id as a, COUNT(*) as b from score sc2, score sc1 where sc2.s_id != '01' AND sc1.s_id = '01'
AND sc2.c_id = sc1.c_id GROUP BY sc2.s_id)t
where t.b=(SELECT COUNT(*) from score sc1 where sc1.s_id = '01' GROUP BY sc1.s_id) 

)p
where a = s.s_id AND a in 
(
    select sc.s_id from score sc WHERE sc.s_id != '01'  GROUP BY sc.s_id HAVING COUNT(*) = (SELECT COUNT(*) from score sc1 where sc1.s_id = '01' GROUP BY sc1.s_id)
)

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

--  查出学过的学生， 然后not in 就找出来没学过的了
--  先找出张三教的哪门课 teacher 和 course表
--  然后再score表找出选那门课的所有学生 剔除这些学生即可
-- 就是一层套一层
SELECT s.s_name 
FROM student s
WHERE s.s_id IN
(
	SELECT DISTINCT sc.s_id 
	FROM score sc
	where sc.s_id NOT IN
(

		SELECT sc.s_id
		FROM score sc
		WHERE sc.c_id IN  
		(SELECT c.c_id FROM course c WHERE c.t_id IN 
			(SELECT t.t_id FROM teacher t WHERE t.t_name = '张三')
		)

)

)

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

-- 这个贼简单 两个表一拼就行
SELECT  s.s_id, s.s_name, AVG(sc.s_score) as '平均成绩'
FROM student s, score sc
WHERE s.s_id = sc.s_id
GROUP BY sc.s_id
HAVING AVG(sc.s_score) < 60

04	李云	33.3333
06	吴兰	32.5

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

-- 这个还是两个表一拼就行 

SELECT s.s_id, s.s_name, s.s_birth, s.s_sex, sc.s_score as '01score'
FROM student s, score sc
where s.s_id = sc.s_id AND sc.c_id = '01'AND sc.s_score < 60  
ORDER BY sc.s_score DESC

04	李云	1990-08-06	男	50
06	吴兰	1992-03-01	女	31

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

-- 不会显示课程成绩
-- 直接在表头上操作 

-- 怎么在一行中一个人的显示语数英三门成绩

-- 百度搜到了答案 没有那么复杂 （不过 course的01与语文的关系不是查出来的 而是直接使用） 
-- 直接在select后面操作 ，放表头

-- 首先 原型是
SELECT s.s_id, s.s_name ,'语文', '数学', '英语', AVG(sc.s_score) as '平均成绩'
FROM student s, score sc
WHERE s.s_id = sc.s_id
GROUP BY sc.s_id
ORDER BY AVG(sc.s_score) DESC

-- 07	郑竹	语文	数学	英语	93.5
-- 01	赵雷	语文	数学	英语	89.6667
-- 05	周梅	语文	数学	英语	81.5
-- 03	孙风	语文	数学	英语	80
-- 02	钱电	语文	数学	英语	70
-- 04	李云	语文	数学	英语	33.3333
-- 06	吴兰	语文	数学	英语	32.5
-- 然后再修改表头 


SELECT s.s_id, s.s_name ,
 (SELECT s_score FROM score WHERE s_id = sc.s_id AND c_id = '01')	AS '语文',
 (SELECT s_score FROM score WHERE s_id = sc.s_id AND c_id = '02') AS '数学',
 (SELECT s_score FROM score WHERE s_id = sc.s_id AND c_id = '03') AS '英语',
  AVG(sc.s_score) as '平均成绩'
FROM student s, score sc
WHERE s.s_id = sc.s_id
GROUP BY sc.s_id
ORDER BY AVG(sc.s_score) DESC

-- 07	郑竹		89	98	93.5
-- 01	赵雷	80	90	99	89.6667
-- 05	周梅	76	87		81.5
-- 03	孙风	80	80	80	80
-- 02	钱电	70	60	80	70
-- 04	李云	50	30	20	33.3333
-- 06	吴兰	31		34	32.5

– 18.查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
–及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

SELECT 
	sc.c_id AS '课程ID',
	c.c_name AS '课程name',  
	MAX(sc.s_score) AS '最高分',
	MIN(sc.s_score) AS '最低分',
	AVG(sc.s_score) AS '平均分',
	SUM(CASE WHEN sc.s_score >= 60 THEN 1 ELSE 0 END) / COUNT(*) AS '及格率',
	SUM(CASE WHEN sc.s_score >= 70 AND sc.s_score < 80 THEN 1 ELSE 0 END) / COUNT(*) AS'中等率',
	SUM(CASE WHEN sc.s_score >= 80 AND sc.s_score < 90 THEN 1 ELSE 0 END) / COUNT(*) AS'优良率',
	SUM(CASE WHEN sc.s_score >= 90 THEN 1 ELSE 0 END) / COUNT(*) AS'优秀率'
FROM
score sc, course c
WHERE sc.c_id = c.c_id
GROUP BY sc.c_id 

-- 01	语文	80	31	64.5	0.6667	0.3333	0.3333	0
-- 02	数学	90	30	72.6667	0.8333	0	0.5	0.1667
-- 03	英语	99	20	68.5	0.6667	0	0.3333	0.3333

-- 格式优化
SELECT CONCAT(FORMAT(0.665269* 100,2),'%')

	-- 19、按各科成绩进行排序，并显示排名(实现不完全)

SELECT 
	sc.c_id AS '课程ID',
	c.c_name AS '课程name',  
	MAX(sc.s_score) AS '最高分',
	MIN(sc.s_score) AS '最低分',
	AVG(sc.s_score) AS '平均分',
CONCAT(FORMAT((SUM(CASE WHEN sc.s_score >= 60 THEN 1 ELSE 0 END) / COUNT(*))* 100,2),'%') 	 AS '及格率',
CONCAT(FORMAT((	SUM(CASE WHEN sc.s_score >= 70 AND sc.s_score < 80 THEN 1 ELSE 0 END) / COUNT(*))* 100,2),'%') AS'中等率',
CONCAT(FORMAT((SUM(CASE WHEN sc.s_score >= 80 AND sc.s_score < 90 THEN 1 ELSE 0 END) / COUNT(*))* 100,2),'%')	 AS'优良率',
CONCAT(FORMAT((SUM(CASE WHEN sc.s_score >= 90 THEN 1 ELSE 0 END) / COUNT(*))* 100,2),'%')	 AS'优秀率'
FROM
score sc, course c
WHERE sc.c_id = c.c_id
GROUP BY sc.c_id 


-- 01	语文	80	31	64.5	66.67%	33.33%	33.33%	0.00%
-- 02	数学	90	30	72.6667	83.33%	0.00%	50.00%	16.67%
-- 03	英语	99	20	68.5	66.67%	0.00%	33.33%	33.33%

– 19、按各科成绩进行排序，并显示排名(实现不完全)

-- 这是别人教我的 来慢慢分析一下
-- 楞看一下 这是两个score表 连起来了
-- 排序的话是根据课程号 和 一个起了别名rank(排名)的列 一起排序  
-- 而且是课程号一起的 所以这样就分组了 
-- 然后再按照排名升序 
-- 这样就按照成绩降序了（先不管rank怎么实现 先看外面 我们可以知道这些 ）
-- 然后我们分析，select的第二个列(就是rank)
-- 细看里面这个select 使用了count 就是求了一个数量 然后加一 变成了这一组的排名 
-- 怎么弄的呢 就在两个表在课程号相等的情况下去比较 就是吧 可以看作里面的sc1的01的一个成绩对应6个sc的成绩 一个一个去比较 求出大于它的个数 
-- 我们看着结果说 拿80来说 大于它的没有 这个select结果0 在外层加1 所以rank排序第一名
-- 另外一个80同样的情况 然后看76 大于它的有两个80 然后再加一 所以排名是3 为啥是2呢 因为我们在count加了distinct 去掉了重复的
-- 这个加distinct因人而异 看你怎么理解 加不加都可以 不加就让76是第三名 以此类推 就可以明白了 但我感觉写出来还是有难度的

SELECT sc.* ,
		(
			(	
				SELECT COUNT(DISTINCT sc1.s_score) 
				FROM score sc1
				WHERE sc1.c_id = sc.c_id AND sc1.s_score > sc.s_score
			)
				+ 1 
		)rank  
FROM score sc 
ORDER BY sc.c_id,rank ASC


sid cid score rank
-- 01	01	80	1
-- 03	01	80	1
-- 05	01	76	2
-- 02	01	70	3
-- 04	01	50	4
-- 06	01	31	5
-- 01	02	90	1
-- 07	02	89	2
-- 05	02	87	3
-- 03	02	80	4
-- 02	02	60	5
-- 04	02	30	6



-- 优化一下 加个姓名

SELECT 
		(
			(	
				SELECT COUNT(DISTINCT sc1.s_score) 
				FROM score sc1
				WHERE sc1.c_id = sc.c_id AND sc1.s_score > sc.s_score
			)
				+ 1 
		)rank , s.s_name, sc.*
FROM score sc, student s
WHERE sc.s_id = s.s_id
ORDER BY sc.c_id,rank ASC

rank name sid cid score
1	孙风	03	01	80
1	赵雷	01	01	80
2	周梅	05	01	76
3	钱电	02	01	70
4	李云	04	01	50
5	吴兰	06	01	31
1	赵雷	01	02	90
2	郑竹	07	02	89
3	周梅	05	02	87
4	孙风	03	02	80
5	钱电	02	02	60
6	李云	04	02	30
1	赵雷	01	03	99
2	郑竹	07	03	98
3	孙风	03	03	80
3	钱电	02	03	80
4	吴兰	06	03	34
5	李云	04	03	20


SELECT c_id,s_id,s_score
,IF(@prec= c_id,@rank:=@rank+1,@rank:=1) AS 名次
,@prec:= c_id
FROM score, (SELECT @rank:=0)r, (SELECT @prec:=-1)p
ORDER BY c_id,s_score DESC;

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

-- 简单

SELECT s_id , SUM(s_score) sum1 FROM score GROUP BY s_id ORDER BY sum1 DESC



SELECT sc.s_id, s.s_name, SUM(sc.s_score) AS '总成绩'
FROM score sc, student s
WHERE sc.s_id = s.s_id
GROUP BY s_id
ORDER BY SUM(sc.s_score) DESC

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

SELECT t.t_name,  c.c_name, AVG(sc.s_score)
FROM score sc, teacher t, course c
WHERE t.t_id = c.t_id and c.c_id = sc.c_id
GROUP BY sc.c_id
ORDER BY  AVG(sc.s_score) DESC

张三	数学	72.6667
王五	英语	68.5
李四	语文	64.5

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

-- 第一步 创建三组课程的视图 注意分组是按学号和课程号共同分组才可以
CREATE VIEW res AS
SELECT s.*, c.c_name, sc.s_score
FROM student s, score sc, course c
WHERE s.s_id = sc.s_id AND sc.c_id = c.c_id 
GROUP BY sc.c_id, sc.s_id;

-- 视图大概是这样的 课程是分了组的但是成绩没有排序 
-- 不能在这上面加排序 会整体排序的 就乱了
-- 01	赵雷	1990-01-01	男	语文	80
-- 02	钱电	1990-12-21	男	语文	70
-- 03	孙风	1990-05-20	男	语文	80
-- 04	李云	1990-08-06	男	语文	50
-- 05	周梅	1991-12-01	女	语文	76
-- 06	吴兰	1992-03-01	女	语文	31
.............

-- 然后挨个查询
SELECT * FROM res where c_name = '语文' ORDER BY s_score DESC LIMIT 1, 2; 
SELECT * FROM res WHERE c_name = '数学' ORDER BY s_score DESC LIMIT 1, 2;
SELECT * FROM res WHERE c_name = '英语' ORDER BY s_score DESC LIMIT 1, 2;
--

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

-- 上边有个类似的 所以这里感觉简单了
SELECT 
sc.c_id AS '课程编号',
c.c_name AS '课程名称',
CONCAT(FORMAT(SUM(CASE WHEN sc.s_score >= 85 THEN 1 ELSE 0 END) / COUNT(*),2)*100,'%')      AS '[100-85]',
CONCAT(FORMAT(SUM(CASE WHEN sc.s_score >= 70 AND sc.s_score < 85 THEN 1 ELSE 0 END) / COUNT(*),2)*100,'%')   	AS '[85-70]',
CONCAT(FORMAT(SUM(CASE WHEN sc.s_score >= 60 AND sc.s_score < 70 THEN 1 ELSE 0 END) / COUNT(*),2)*100,'%')  		AS '[70-60]',
CONCAT(FORMAT(SUM(CASE WHEN sc.s_score < 60 THEN 1 ELSE 0 END) / COUNT(*),2)*100,'%')  		AS '[0-60]'
FROM score sc, course c
WHERE sc.c_id = c.c_id
GROUP BY sc.c_id

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

-- 第一次 这样的结果没有排名 所以要加
SELECT sc.s_id, AVG(sc.s_score) AS '平均成绩'
FROM score sc
GROUP BY sc.s_id 
ORDER BY  AVG(sc.s_score) DESC
-- 07	93.5
-- 01	89.6667
-- 05	81.5
-- 03	80
-- 02	70
-- 04	33.3333
-- 06	32.5

-- 第二次根据网上搜的 首先 select 和from 后面加的那两个东西都要起别名才可以 然后吧 这样他会随着一起排序
SELECT (@i:=@i+1)rank, sc.s_id, AVG(sc.s_score) AS '平均成绩'
FROM score sc,(select @i:=0)t
GROUP BY sc.s_id 
ORDER BY  AVG(sc.s_score) DESC
-- 7	07	93.5
-- 1	01	89.6667
-- 5	05	81.5
-- 3	03	80
-- 2	02	70
-- 4	04	33.3333
-- 6	06	32.5
-- 所以第三次 想了个办法 将结果作为一个表 重新输出 这样就可以顺序加上序号了

SELECT (@i:=@i+1)rank, q.s_id, q.avg 
from 
(SELECT sc.s_id, AVG(sc.s_score) AS avg
FROM score sc
GROUP BY sc.s_id 
ORDER BY  AVG(sc.s_score) DESC)q, (select @i:=0)t


-- 1	07	93.5
-- 2	01	89.6667
-- 3	05	81.5
-- 4	03	80
-- 5	02	70
-- 6	04	33.3333
-- 7	06	32.5

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

CREATE VIEW res1 AS
SELECT s.*, c.c_name, sc.s_score
FROM student s, score sc, course c
WHERE s.s_id = sc.s_id AND sc.c_id = c.c_id;
-- 01	赵雷	1990-01-01	男	语文	80
-- 02	钱电	1990-12-21	男	语文	70
-- 03	孙风	1990-05-20	男	语文	80
-- 04	李云	1990-08-06	男	语文	50
......
SELECT * FROM res1 WHERE c_name = '语文' ORDER BY s_score DESC LIMIT 0, 3;
SELECT * FROM res1 WHERE c_name = '数学' ORDER BY s_score DESC LIMIT 0, 3;
SELECT * FROM res1 WHERE c_name = '英语' ORDER BY s_score DESC LIMIT 0, 3;

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

--  这个很简单
SELECT c_id, COUNT(*)
FROM score
GROUP BY c_id

-- 01	6
-- 02	6
-- 03	6

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

-- 根据学号分组 这个学号选了几个课程 COUNT计算出来
SELECT sc.s_id, s.s_name
FROM score sc, student s
WHERE sc.s_id = s.s_id
GROUP BY sc.s_id
HAVING COUNT(sc.c_id) = 2

-- 05	周梅
-- 06	吴兰
-- 07	郑竹

– 28、查询男生、女生人数

SELECT COUNT(*) FROM student WHERE s_sex = ‘男’
SELECT COUNT(s_sex) a FROM student WHERE s_sex = ‘女’

SELECT s.s_sex, COUNT(*)
FROM student s
GROUP BY s.s_sex

男	4
女	4

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

SELECT s.*
FROM student s
WHERE s.s_name LIKE '%风%';

03	孙风	1990-05-20	男

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

insert into Student values('09' , '王菊' , '1990-01-20' , '女');

SELECT s1.*, COUNT(*)
FROM student s1, student s2
WHERE s1.s_id != s2.s_id AND s1.s_name = s2.s_name
GROUP BY s1.s_name

                         count
09	王菊	1990-01-20	女	2

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

-- 两个方法 网上搜的
SELECT s.*
FROM student s
WHERE s.s_birth LIKE '1990-%';

SELECT s.* 
FROM student s
WHERE s.s_birth >= '1990-01-01' AND s.s_birth <= '1990-12-31';



01	赵雷	1990-01-01	男
02	钱电	1990-12-21	男
03	孙风	1990-05-20	男
04	李云	1990-08-06	男
08	王菊	1990-01-20	女
09	王菊	1990-01-20	女

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

-- 这只是排了一种情况
SELECT sc.c_id, c.c_name, FORMAT(AVG(sc.s_score),2) as '平均成绩'
FROM score sc, course c
WHERE sc.c_id  = c.c_id 
GROUP BY sc.c_id
ORDER BY AVG(sc.s_score) DESC

-- 当平均成绩相同时，按课程编号升序排列 怎么弄呢 网上找一下 emmm 直接跟在排序的后面就行 他会按照顺序排的
SELECT sc.c_id, c.c_name, FORMAT(AVG(sc.s_score),2) as '平均成绩'
FROM score sc, course c
WHERE sc.c_id  = c.c_id 
GROUP BY sc.c_id
ORDER BY AVG(sc.s_score) DESC, sc.c_id

02	数学	72.67
03	英语	68.50
01	语文	64.50

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

SELECT sc.s_id, s.s_name, FORMAT(AVG(sc.s_score),2) as '平均成绩'
FROM score sc, student s
WHERE sc.s_id = s.s_id
GROUP BY sc.s_id
HAVING AVG(sc.s_score) >= 85

01	赵雷	89.67
07	郑竹	93.50

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

SELECT s.s_name, c.c_name,  sc.s_score
FROM student s, score sc, course c
WHERE s.s_id = sc.s_id AND c.c_id = sc.c_id AND c.c_name = '数学' AND sc.s_score < 60

李云	数学	30

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

SELECT s.*, c.*, sc.* 
FROM score sc, course c, student s
WHERE s.s_id = sc.s_id AND c.c_id = sc.c_id


01	赵雷	1990-01-01	男	01	语文	02	01	01	80
02	钱电	1990-12-21	男	01	语文	02	02	01	70
03	孙风	1990-05-20	男	01	语文	02	03	01	80


原来是一个分数一条记录，现在变成一个学生一条记录

SELECT
sc.s_id as '学号',
s.s_name AS '姓名',
(CASE WHEN sc.c_id = '01' THEN sc.s_score ELSE 0 END) AS '语文',
(CASE WHEN sc.c_id = '02' THEN sc.s_score ELSE 0 END) AS '数学',
(CASE WHEN sc.c_id = '03' THEN sc.s_score ELSE 0 END) AS '英语'
FROM student s, score sc
WHERE s.s_id = sc.s_id 


01	赵雷	80	0	0
01	赵雷	0	90	0
01	赵雷	0	0	99
但是不对啊！！！！
怎么改改

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

 SELECT s.s_name, c.c_name, sc.s_score
 FROM student s, score sc, course c
 WHERE s.s_id = sc.s_id AND sc.c_id = c.c_id AND sc.s_score > 70
 
赵雷	语文	80
孙风	语文	80
周梅	语文	76
赵雷	数学	90
孙风	数学	80
周梅	数学	87
郑竹	数学	89
赵雷	英语	99
钱电	英语	80
孙风	英语	80
郑竹	英语	98

– 37、查询不及格的课程

 SELECT s.s_name, c.c_name, sc.s_score
 FROM student s, score sc, course c
 WHERE s.s_id = sc.s_id AND sc.c_id = c.c_id AND sc.s_score < 60

-- 李云	语文	50
-- 吴兰	语文	31
-- 李云	数学	30
-- 李云	英语	20
-- 吴兰	英语	34

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

本来是查8以上没有数据 改成75
SELECT s.s_id, s.s_name
FROM student s, score sc
WHERE sc.c_id = '01' AND sc.s_score > 75 and s.s_id = sc.s_id

01	赵雷
03	孙风
05	周梅

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

SELECT sc.c_id, COUNT(*) AS '人数'
FROM score sc
GROUP BY sc.c_id

01	6
02	6
03	6

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

SELECT s.*, MAX(sc.s_score)
FROM student s, score sc
join course c on c.c_id = sc.c_id
join teacher t on t.t_id = c.c_id AND t.t_name = '张三'
WHERE s.s_id = sc.s_id 


01	赵雷	1990-01-01	男	80

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

SELECT sc1.*, sc2.*
FROM score sc1
JOIN score sc2 ON sc1.c_id != sc2.c_id AND sc1.s_score = sc2.s_score
sid cid sc  sid1 cid1 sc
02	03	80	01	01	80
03	02	80	01	01	80
03	03	80	01	01	80
01	01	80	02	03	80
03	01	80	02	03	80
03	02	80	02	03	80
......................

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

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

SELECT sc.c_id, COUNT(sc.s_id) AS '选修人数'
FROM score sc
GROUP BY sc.c_id
ORDER BY COUNT(sc.s_id) DESC, sc.c_id

01	6
02	6
03	6

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

-- 44、检索至少选修3门课程的学生学号 

SELECT sc.s_id
FROM score sc
GROUP BY sc.s_id
HAVING COUNT(sc.c_id) >= 3

01
02
03
04

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

SELECT s.*, COUNT(*)
FROM student s
JOIN score sc ON s.s_id = sc.s_id
-- JOIN course c ON sc.c_id = c.c_id 不加这个
GROUP BY sc.s_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM course)


01	赵雷	1990-01-01	男	3
02	钱电	1990-12-21	男	3
03	孙风	1990-05-20	男	3
04	李云	1990-08-06	男	3

– 46、查询各学生的年龄

SELECT s.s_id, s.s_name, TIMESTAMPDIFF(YEAR,s.s_birth,CURDATE()) AS '年龄'
FROM score sc, student s
WHERE sc.s_id = s.s_id


-- 一，TIMESTAMPDIFF
-- 语法：
-- TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)。
-- 说明：
-- 返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其结果的单位由interval 参数给出。该参数必须是以下值的其中一个：
-- 153.218.
-- FRAC_SECOND。表示间隔是毫秒
-- SECOND。秒
-- MINUTE。分钟
-- HOUR。小时
-- DAY。天
-- WEEK。星期
-- MONTH。月
-- QUARTER。季度
-- YEAR。年

-- CURDATE() 函数返回当前的日期。
-- SELECT NOW(),CURDATE(),CURTIME()
-- 
-- NOW()	            CURDATE()	    CURTIME()
-- 2008-12-29 16:25:46	2008-12-29	16:25:46

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

-- 删一下30题插入的那个重新插入一条数据  这样就能查出来了
DELETE FROM student WHERE s_id = '09';

insert into Student values('09' , '王菊' , '1990-08-14' , '女');

SELECT s.*
FROM student s
WHERE WEEK(NOW()) = WEEK(s.s_birth);

09	王菊	1990-08-14	女

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

insert into Student values('10' , '王清' , '1990-08-19' , '女');

SELECT s.*
FROM student s
WHERE WEEK(NOW()) + 1 = WEEK(s.s_birth);

-- 10	王清	1990-08-19	女

– 49、查询本月过生日的学生

SELECT s.*
FROM student s
WHERE MONTH(CURDATE()) = MONTH(s.s_birth);

-- 04	李云	1990-08-06	男
-- 09	王菊	1990-08-14	女
-- 10	王清	1990-08-19	女

– 50、查询下月过生日的学生

insert into Student values('11' , '王清' , '1990-09-19' , '女');

SELECT s.*
FROM student s
WHERE MONTH(CURDATE()) + 1 = MONTH(s.s_birth);

-- 11	王清	1990-09-19	女


建表--建表
--学生表
CREATE TABLE `Student`(
    `s_id` VARCHAR(20),
    `s_name` VARCHAR(20) NOT NULL DEFAULT '',
    `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
    `s_sex` VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `Course`(
    `c_id`  VARCHAR(20),
    `c_name` VARCHAR(20) NOT NULL DEFAULT '',
    `t_id` VARCHAR(20) NOT NULL,
    PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `Teacher`(
    `t_id` VARCHAR(20),
    `t_name` VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `Score`(
    `s_id` VARCHAR(20),
    `c_id`  VARCHAR(20),
    `s_score` INT(3),
    PRIMARY KEY(`s_id`,`c_id`)
);
--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
C_time
关注
专栏目录
mysql 查询语句学生表、课程表、 成绩表、教师表.docx
12-17
mysql 查询语句学生表、课程表、成绩表、教师表 在本文中，我们将对 mysql 查询语句学生表、课程表、成绩表、教师表进行详细的分析和解释。该文档提供了 8 个常用 sql 语句，涵盖了学生表、课程表、成绩表、教师表...
数据库统计每个课程不及格人数
牧空的博客
06-19 8165
假设有表如下 Grade(CourseID,StudentID,Score) Course(CourseID,CourseName) Student(StudentID,StudentName) 现创建一个视图用于统计每个课程不及格的人数,输出位课程名称,课程编号,不及格人数 代码如下 create view View_fail as select A.courseid as "课程编号", co.coursename as "课程名字",A.不及格人数 from course as co join (s
学习 MySQL 必备的几个示例数据库
Tony.Dong的专栏
06-17 9619
MySQL 官方网站提供了以下几个示例数据库：Sakila、Employees、world、world_x 以及 menagerie。这些数据库既可以用于日常学习和测试，也可以作为我们设计时数据库的一个参考。本文就来介绍一下这些数据库的模式结构以及如何下载和安装。
Mysql常见数据库和表
最新发布
m0_68096246的博客
09-26 71
mysql> insert into t_hero values(11,"司马懿","仲达",'未知',"魏国","大都督","null");mysql> insert into t_hero values(11,"司马懿","仲达",'36',"魏国","大都督","null");mysql> insert into t_hero values(14,"程昱","程立",'32',"魏国","保家卫国","null");
-- 19、按各科成绩进行排序，并显示排名(实现不完全)
Ele_sorriu的博客
10-05 2万+
SELECT * FROM (SELECT * FROM score WHERE c_id = '01' ORDER BY s_score DESC LIMIT 0,100 ) a UNION ALL SELECT * FROM (SELECT * FROM score WHERE c_id = '02' ORDER BY s_score DESC LIMIT 0,100) b UNION ALL SELECT * FROM (SELECT * FROM score WHERE c_id = '03'
SQL45道经典练习题练习
Brand_New2020的博客
10-01 1291
SQL45道经典练习题练习 创建表 use 45test; CREATE TABLE students (sno VARCHAR(3) NOT NULL, sname VARCHAR(4) NOT NULL, ssex VARCHAR(2) NOT NULL, sbirthday DATETIME, class VARCHAR(5)); CREATE TABLE courses (cno VARCHAR(5) NOT NULL, cname VARCHAR(10) NOT NULL, tno V
mysql查询语句学生表、课程表、成绩表、教师表参照.pdf
02-12
MySQL 查询语句学生表、课程表、成绩表、教师表参照 本文档提供了一份关于 MySQL 查询语句的学习资源，涵盖了学生表、课程表、成绩表、教师表四个表之间的关系，并提供了 50 个常用的 SQL 语句示例。这些示例涵盖了...
mysql查询语句学生表、课程表、成绩表、教师表文.pdf
11-04
MySQL 查询语句学生表、课程表、成绩表、教师表 本文档提供了 8 个关于学生表、课程表、成绩表、教师表的常用 SQL 查询语句，这些查询语句涵盖了基本的数据检索、数据分析、数据统计等方面，对于 MySQL 数据库的...
学生表关联练习程序案例.rar
09-09
在学生管理系统中，可能有多个表，如“学生表”、“课程表”和“成绩表”，它们通过学生ID、课程ID等关联起来。 例如，"学生表"可能包含学生的个人信息，如学生ID、姓名、性别、出生日期；"课程表"则列出所有可用的...
数据库-视图view（查询自己的成绩，每个同学的平均分，统计各系人数，单个系部学生信息，删除视图 ）
weixin_44270194的博客
05-11 2万+
1、查询你自己各门课的成绩：V_自己的成绩（学号、姓名、课程名、分数） create view v_自己的成绩 as select sc.学号,姓名,课程名,分数 from 学生表 s , 课程表 c , 成绩表 sc where s.学号=sc.学号 and c.ID=sc.课程ID and sc.学号='151011029' 2、查询你自己各门课的成绩：V_自己的成绩（学号、姓名、课程名、分数，要求这几个字段用英文命名:Num,Name,CourseName,Score） create v
mysql学生表选课表课程表_sql server 很好的基础练习 <学生表-老师表-课程表-选课表>...
weixin_32550525的博客
02-08 1129
表结构--学生表tblStudent(编号StuId、姓名StuName、年龄StuAge、性别StuSex)--课程表tblCourse(课程编号CourseId、课程名称CourseName、教师编号TeaId)--成绩表tblScore(学生编号StuId、课程编号CourseId、成绩Score)--教师表tblTeacher(教师编号TeaId、姓名TeaName) CREATE TA...
sql经典50题之--15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
liyang_nash的专栏
08-15 1万+
表 student sc teacher course 准备工作 1.1group by后面存在多个字段 1.2 left join sc as b 给sc起名为b 1.3 left join… on a.score<b.score 输出内容是: 是循环a表中的全部行, 与b表第一行比, 显示出满足条件的行, 行的列包...
sql面试经典50题练习笔记二
maggrect的博客
10-10 3190
继续sql面试经典50题练习笔记一的练习… 没有看过sql面试经典50题练习笔记一的朋友建议可以点击下面的练习先从一开始看起，数据库测试表和题目来源都有在那篇博客说明~ sql面试经典50题练习笔记一：https://blog.csdn.net/maggrect/article/details/102461646 让我们开始吧… 16、检索"01"课程分数小于60，按分数降序排列的学生信息 分析：...
大数据——Scala算子练习题
Vicky_Tang
07-21 1万+
package cn.kgc.praitce import java.io.File import scala.collection.mutable import scala.io.Source object Test { def main(args: Array[String]): Unit = { //各表数据 val course = Source.fromFile(new File("file/course.txt")) .getLines().map(...
SQL50之按各科成绩进行排序，并显示排名
qq_40745994的博客
08-19 4540
让我第一次感觉到sql的强大sql的美丽。 https://blog.csdn.net/liyang_nash/article/details/99641571
各科成绩排序，不分科目进行成绩排序
梦船水萍
01-13 3069
1、按成绩(不分科目)进行排序，并显示排名，Score 重复时保留名次空缺 select *,RANK()over(order by score desc)名次 from SC
MySQL经典45题（4）| 15-18题
weixin_45730948的博客
04-20 1093
第四天【15-18题】 15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺 15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次 16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺 16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺 17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比 18.查询各科成绩前三名的记录 第五天【19-3
创建一个数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）
热门推荐
汉子一枚
04-18 4万+
创建一个数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。四个表的结构分别如表1-1的表（一）~表（四）所示，数据如表1-2的表… 表（一）Student (学生表) 属性名 数据类型 可否为空 含 义 Sno varchar (20) 否 学号 Sname varchar (20) 否 学生姓名 Ssex varchar ...
MYSQL表的增删改查进阶（上）
m0_52322019的博客
12-25 1605
MYSQL表的增删改查进阶1. 数据库约束1.1 约束类型二级目录三级目录 1. 数据库约束 1.1 约束类型 not null 指某列不能存储null值。 unique 保证某列的每行必须有唯一的值。 1.2 null约束 二级目录 三级目录 ...
MySQL全方位练习（学生表 教师表 课程表 分数表）
06-08
好的，我可以为您提供MySQL全方位练习的建议。...这样，您就可以使用这些表进行MySQL的全方位练习，例如查询某个学生的成绩、查询某个课程的平均分数、查询某个老师教授的所有课程等等。祝您练习愉快！
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值