常见sql 练习题

最新推荐文章于 2022-06-14 14:36:22 发布
最新推荐文章于 2022-06-14 14:36:22 发布
阅读量684 收藏 1
点赞数 2
分类专栏： mysql 文章标签： mysql常见面试题
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Master_chaoAndQi/article/details/90734534
版权

题目来源于https://blog.csdn.net/flycat296/article/details/63681089?spm=a2c4e.11153940.blogcont637168.8.1f5022a5GeYTL3

1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
2 查询选择" 01 "课程和" 02 "课程学生信息
3 查询选择" 01 "课程但可能不存在" 02 "课程的学生信息
4  查询不存在" 01 "课程但存在" 02 "课程的学生信息
5. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
6. 查询在 SC 表存在成绩的学生信息
7.  查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 无 )
8. 查询「李」姓老师的数量 
9. 查询学过「张三」老师授课的同学的信息 
10. 查询没有学全所有课程的同学的信息 
11. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息 
12 . 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息 
13. 查询没学过"张三"老师讲授的任一门课程的学生姓名 
14. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 
15. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
16. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
17. 查询各科成绩最高分、最低分和平均分：
    以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
    及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
    要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
18. 查询各科成绩前三名的记录
19. 查询每门课程被选修的学生数 
20. 查询出只选修两门课程的学生学号和姓名 
21. 查询男生、女生人数
22. 查询同名同性学生名单，并统计同名人数
23. 查询 1990 年出生的学生名单
24. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
25. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 
26. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数 
27. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
28. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
29. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 
30. 查询各学生的年龄，只按年份来算 
31. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

二 数据借用一下那个博主的数据 

CREATE DATABASE sqldemo;

use sqldemo;

--学生表  sid 学生编号   sname 学生编号   sage 学生年龄     sbirthday 生日  ssex 学生性别
create table Student( sid varchar(10),sname VARCHAR(10),sage  int (3),sbirthday datetime ,ssex CHAR(20))CHARACTER set utf8;

insert into Student values('01' , '赵雷' , '20','1990-01-01' , 'women');
insert into Student values('02' , '钱电' ,'19', '1990-12-21' , 'man');
insert into Student values('03' , '孙风' , '30','1990-05-20' , 'man');
insert into Student values('04' , '李云' , '50','1990-08-06' , 'man');
insert into Student values('05' , '周梅' ,'30', '1991-12-01' , 'women');
insert into Student values('06' , '吴兰' , '45','1992-03-01' , 'women');
insert into Student values('07' , '郑竹' , '35','1989-07-01' , 'women');
insert into Student values('08' , '王菊' , '20','1990-01-20' , 'women');

--科目表 Course  cid 课程编号   cname 课程名称    tid 教师编号
create table Course(cid varchar(10),Cname nvarchar(10),tid varchar(10))CHARACTER set utf8;

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),Tname nvarchar(10))CHARACTER set utf8;
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');



--学生成绩表 SC scid 学生id  cid 课程id  score 分数

create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1))CHARACTER set utf8;
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

三 开始sql练习   （非常感谢博主的数据）

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

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

use sqldemo;

# 查询主表信息  学生 课程  分数  三表关联查询  关联两次sc 表   查询出满足条件的01的课程信息

SELECT s.sid,s.sname,s.sage ,s.sbirthday,s.ssex,c.cname,sc1.score from student s 
left join  sc  sc1 on sc1.sid =s.sid 
left join  sc  sc2 on sc2.sid =s.sid 
left join course c on sc1.cid = c.cid
where sc1.cid = "01" and sc2.cid="02" and  sc1.score>sc2.score;

 #修改为使用子查询的方式  子查询查询出满足查询条件  01 的课程分数>02的课程分数的学生id  加上外层课程id = 01 过滤出 满足条件的查询
 SELECT s.sid,s.sname,s.sage ,s.sbirthday,s.ssex,c.cname,sc.score from student s 
	  left join  sc on sc.sid =s.sid 
		left join course c on sc.cid = c.cid
		where  s.sid in (
			SELECT sc1.sid from  sc sc1 left join sc sc2 on  sc1.sid = sc2.sid
			where sc1.cid = "01" and sc2.cid="02" and  sc1.score>sc2.score
		) and  c.cid = '01';

#查询结果
02	钱电	19	1990-12-21 00:00:00	man	语文	70
04	李云	50	1990-08-06 00:00:00	man	语文	50




2 查询选择" 01 "课程和" 02 "课程学生信息

 # 主干为  学生信息 条件 为 同时选择课程01 和 02
 
 # 子查询方式
 SELECT s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex 
 from student s 
	where s.sid in(
			SELECT s.sid from 
			student s 
			left join sc sc1 on s.sid = sc1.sid 
			left join sc sc2 on s.sid = sc2.sid 
			where  sc1.cid = "01" and sc2.cid ="02"
		
	);
	#关联方式查询  用学生关联成绩信息表两次 用where 条件过滤 符合条件的数据
 SELECT s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex 
 from student s 
left join sc sc1 on s.sid = sc1.sid  
left join sc sc2 on s.sid = sc2.sid 
where  sc1.cid = "01" and sc2.cid ="02"
;

#查询结果
01	赵雷	20	1990-01-01 00:00:00	women
02	钱电	19	1990-12-21 00:00:00	man
03	孙风	30	1990-05-20 00:00:00	man
04	李云	50	1990-08-06 00:00:00	man
05	周梅	30	1991-12-01 00:00:00	women
3 查询选择" 01 "课程但可能不存在" 02 "课程的学生信息

#查询选择" 01 "课程但可能不存在" 02 "课程的学生信息
# 主干 学生信息
# 条件  ：选择课程1  课程2不确定  =====> 选择课程1 其他的任意

# 子查询   查询出选择了课程1 的学生id 

 SELECT s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex 
 from student s 
	where s.sid in(
			SELECT s.sid from 
			student s 
			left join sc sc1 on s.sid = sc1.sid 
			
			where  sc1.cid = "01" 

	);
#关联查询
SELECT s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex 
 from student s 
left join sc sc1 on s.sid = sc1.sid  

where  sc1.cid = "01" 
;

#查询结果
01	赵雷	20	1990-01-01 00:00:00	women
02	钱电	19	1990-12-21 00:00:00	man
03	孙风	30	1990-05-20 00:00:00	man
04	李云	50	1990-08-06 00:00:00	man
05	周梅	30	1991-12-01 00:00:00	women
06	吴兰	45	1992-03-01 00:00:00	women
#4 查询不选择" 01 "课程但存在" 02 "课程的学生信息
#主干信息   学生信息
#条件  选择课程2 不选择课程1  查询出同时选择了课程01 和02的学生id

 SELECT 
 s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex 
 from student s 
	where s.sid not in(
			SELECT s.sid from 
			student s 
			left join sc sc1 on s.sid = sc1.sid 
			left join sc sc2 on s.sid = sc2.sid 
			where  sc1.cid = "01" and sc2.cid ="02"
		
	);

#查询结果
06	吴兰	45	1992-03-01 00:00:00	women
07	郑竹	35	1989-07-01 00:00:00	women
08	王菊	20	1990-01-20 00:00:00	women
#5 查询平均成绩大于等于 60 分的同学的学生编号 学生姓名  平均成绩
# 按 学生分组  求平均成绩 
# 主信息为学生信息
 SELECT 
 s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex,
 temp.avgScore
 from student s 
 inner join (
 SELECT sc.sid as "sid",
 avg(sc.score) as "avgScore"
 from sc 
 GROUP BY (sc.sid) having (avgScore >60) 
 ) temp on  temp.sid = s.sid

#查询结果
01	赵雷	20	1990-01-01 00:00:00	women	89.66667
02	钱电	19	1990-12-21 00:00:00	man	70
03	孙风	30	1990-05-20 00:00:00	man	80
05	周梅	30	1991-12-01 00:00:00	women	81.5
07	郑竹	35	1989-07-01 00:00:00	women	93.5
#6 查询在 SC 表存在成绩的学生信息    group by (sc.sid) 分组去重 
 
 
 SELECT 
 s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex
 from student s  
 inner join sc on sc.sid= s.sid 
 where sc.score is not null group by (sc.sid);


# 查询结果
01	赵雷	20	1990-01-01 00:00:00	women
02	钱电	19	1990-12-21 00:00:00	man
03	孙风	30	1990-05-20 00:00:00	man
04	李云	50	1990-08-06 00:00:00	man
05	周梅	30	1991-12-01 00:00:00	women
06	吴兰	45	1992-03-01 00:00:00	women
07	郑竹	35	1989-07-01 00:00:00	women
 #7 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 无 )
 SELECT 
 s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex,
 ifnull(temp.sum,"无") as "sum",
 ifnull(temp.ccount,"无") as "countc"

 from student s  
 left  join  (
  SELECT sc.sid as "sid",
 sum(sc.score) as "sum",
 count(sc.cid) as "ccount"
 from sc 
 GROUP BY (sc.sid) 
 ) temp  on  temp.sid = s.sid;

#查询结果
01	赵雷	20	1990-01-01 00:00:00	women	269.0	3
02	钱电	19	1990-12-21 00:00:00	man	   210.0	3
03	孙风	30	1990-05-20 00:00:00	man	   240.0	3
04	李云	50	1990-08-06 00:00:00	man	   100.0	3
05	周梅	30	1991-12-01 00:00:00	women	163.0	2
06	吴兰	45	1992-03-01 00:00:00	women	65.0	2
07	郑竹	35	1989-07-01 00:00:00	women	187.0	2
08	王菊	20	1990-01-20 00:00:00	women	无	   无
 
 # 8  查询「李」姓老师的数量  
 
 SELECT count(1) from teacher where  INSTR(tname,'李')>0
  
  SELECT count(1) from teacher where  tname like '李%';
#查询结果
1
 # 9 查询学过「张三」老师授课的同学的信息 
 
 # 学生信息   条件教师 信息为张三
 # SELECT c.cid  from  course c right join teacher t on c.tid = t.tid where t.tname ="张三" and  c.cid is not null 
 # 以教师为主表 right join 连接 如果存在没有教课的老师的话 会存在c.cid为空 加上c.cid is not null 过滤
 
  SELECT 
 s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex
 from student s  
 left join sc on s.sid =sc.sid 
 where sc.cid in (
 
 SELECT c.cid  from  course c right join teacher t on c.tid = t.tid where t.tname ="张三" and  c.cid is not null
 
 );

#查询结果
01	赵雷	20	1990-01-01 00:00:00	women
02	钱电	19	1990-12-21 00:00:00	man
03	孙风	30	1990-05-20 00:00:00	man
04	李云	50	1990-08-06 00:00:00	man
05	周梅	30	1991-12-01 00:00:00	women
07	郑竹	35	1989-07-01 00:00:00	women
 #10 查询没有学全所有课程的同学的信息
 # 先统计出总的课程数  查询出 小于此数的学生信息  再拼上没有选课的学生信息
 
   SELECT 
 s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex,
 temp.ccount
 from student s 

 left  join  (
  SELECT sc.sid as "sid",
 count(sc.cid) as "ccount"
 from sc 
 GROUP BY (sc.sid) 
 ) temp  on  temp.sid = s.sid
 where temp.ccount < (SELECT count(1) from course )
 UNION
 SELECT 
 s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex,
 "0"
 from student s where  s.sid not in  (SELECT sc.sid from sc )
 ;
 # 查询结果
05	周梅	30	1991-12-01 00:00:00	women	2
06	吴兰	45	1992-03-01 00:00:00	women	2
07	郑竹	35	1989-07-01 00:00:00	women	2
08	王菊	20	1990-01-20 00:00:00	women	0
#11 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息  
#先查询出 01 同学所学的课程
# 再查询出所学课程在01所学课程集合的同学信息


 SELECT
 s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex
 from student s 
	  left join  sc on sc.sid =s.sid 
		left join course c on sc.cid = c.cid
		where c.cid in  (
					SELECT
						c.cid
			 from student s 
					left join  sc on sc.sid =s.sid 
					left join course c on sc.cid = c.cid
					where s.sid ="01"
		) group by (s.sid);
#结果

01	赵雷	20	1990-01-01 00:00:00	women
02	钱电	19	1990-12-21 00:00:00	man
03	孙风	30	1990-05-20 00:00:00	man
04	李云	50	1990-08-06 00:00:00	man
05	周梅	30	1991-12-01 00:00:00	women
06	吴兰	45	1992-03-01 00:00:00	women
07	郑竹	35	1989-07-01 00:00:00	women
#12 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息  
 # 字符串拼接 判断完全相等 
 
  SELECT
 s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex
 from student s 
 left join
 (
		select 
		sc.sid as "sid",
		GROUP_CONCAT(sc.cid) as "cids"
		
		from sc 
		GROUP BY(sc.sid)

 ) temp  on  temp.sid = s.sid 
 
 where temp.cids = (
		select 
			GROUP_CONCAT(sc.cid) as "cids" 
			from sc 
			where sc.sid ="01"
 );

#结果


01	赵雷	20	1990-01-01 00:00:00	women
03	孙风	30	1990-05-20 00:00:00	man
#13 查询没学过"张三"老师讲授的任一门课程的学生姓名 
 

 SELECT
 s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex
 from student s 
 left join
 (
		select 
		sc.sid as "sid",
		GROUP_CONCAT(sc.cid) as "cids"
		
		from sc 
		GROUP BY(sc.sid)

 ) temp  on  temp.sid = s.sid 
 
 where INSTR( temp.cids,(SELECT c.cid  from  course c right join teacher t on c.tid = t.tid where t.tname ="张三" and  c.cid is not null))=0
 
 # 查询结果
06	吴兰	45	1992-03-01 00:00:00	women
#14 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 
 
 SELECT 
 s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex,
 temp.avgScore
 from student s 
 inner join (
 SELECT sc.sid as "sid",
 avg(sc.score) as "avgScore",
 count(sc.cid) as "countc"
 from sc 
 GROUP BY (sc.sid) having (avgScore <60) 
 ) temp on  temp.sid = s.sid
 where  temp.countc >=2;

# 查询结果
04	李云	50	1990-08-06 00:00:00	man	33.33333
06	吴兰	45	1992-03-01 00:00:00	women	32.5
 #15  检索" 01 "课程分数小于 60，按分数降序排列的学生信息
 
 
 SELECT s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex 
 from student s 
left join sc sc1 on s.sid = sc1.sid  

where  sc1.cid = "01" and  sc1.score <60 ORDER BY  sc1.score desc ;

#结果

04	李云	50	1990-08-06 00:00:00	man
06	吴兰	45	1992-03-01 00:00:00	women
#16  按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
# 聚合函数求平均成绩   
#通过case when xxx then  else  end + 聚合函数将列转行

SELECT 
 s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex,
ifnull((select avg(sc.score) from sc where  sc.Sid=s.sid GROUP BY(sc.sid)),0)as "avgScore",
 SUM(case when temp.cname="语文" then temp.score else 0 end ) as "语文",
 SUM(case when temp.cname="数学" then temp.score else 0 end ) as "数学",
 SUM(case when temp.cname="英语" then temp.score else 0 end ) as "英语"
 from student s 
 left join (
	SELECT c.cid  as "cid",
	c.cname as "cname",
	sc.score as "score", 
	sc.sid  as "sid"
	from  course c 
	left join sc on  c.cid =sc.cid
 )temp on temp.sid = s.sid
 GROUP BY(s.sid)
 ORDER BY avgScore desc ;

#结果
07	郑竹	35	1989-07-01 00:00:00	women	93.5	0	89	98
01	赵雷	20	1990-01-01 00:00:00	women	89.66667	80	90	99
05	周梅	30	1991-12-01 00:00:00	women	81.5	76	87	0
03	孙风	30	1990-05-20 00:00:00	man	80	80	80	80
02	钱电	19	1990-12-21 00:00:00	man	70	70	60	80
04	李云	50	1990-08-06 00:00:00	man	33.33333	50	30	20
06	吴兰	45	1992-03-01 00:00:00	women	32.5	31	0	34
08	王菊	20	1990-01-20 00:00:00	women	0	0	0	0

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

   # 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 不及格
   # 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
   #要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

# 计算出满足条件的人数
 
  SELECT c.cid,
 c.cname ,
 temp.maxScore as "最高分",
 temp.minScore as "最低分",
 temp.avgScore as "平均分",
 temp.countc as "选修人数",
 IF(temp.count1 is not null,temp.count1/temp.countc,0) as "不及格率",
  IF(temp.count2 is not null,temp.count2/temp.countc,0) as "及格率",
  IF(temp.count3 is not null,temp.count3/temp.countc,0) as "中等率",
  IF(temp.count4 is not null,temp.count4/temp.countc,0) as "优良率",
 IF(temp.count5 is not null,temp.count5/temp.countc,0)as "优秀率"
 from  course c 
 INNER join  (
 SELECT c.cid,
 (SELECT MAX(sc.score)  from  sc  where  sc.cid= c.cid GROUP BY(sc.Cid)) as "maxScore",
 (SELECT Min(sc.score)  from  sc  where  sc.cid= c.cid GROUP BY(sc.Cid)) as "minScore",
 (SELECT Avg(sc.score)  from  sc  where  sc.cid= c.cid GROUP BY(sc.Cid)) as "avgScore",
 (SELECT count(DISTINCT sc.sid)  from  sc  where  sc.cid= c.cid GROUP BY(sc.Cid)) as "countc",
  (SELECT count(DISTINCT sc.sid)  from  sc  where  sc.cid= c.cid  and  sc.score<60  GROUP BY(sc.Cid)) as "count1",
 (SELECT count(DISTINCT sc.sid)  from  sc  where  sc.cid= c.cid  and  sc.score>=60 and sc.score<70 GROUP BY(sc.Cid)) as "count2",
 (SELECT count(DISTINCT sc.sid)  from  sc  where  sc.cid= c.cid  and  sc.score>=70 and sc.score<80 GROUP BY(sc.Cid)) as "count3",
 (SELECT count(DISTINCT sc.sid)  from  sc  where  sc.cid= c.cid  and  sc.score>=80 and sc.score<90  GROUP BY(sc.Cid)) as "count4",
 (SELECT count(DISTINCT sc.sid)  from  sc  where  sc.cid= c.cid  and  sc.score>=90  GROUP BY(sc.Cid)) as "count5"
 from  course c
 ) temp on temp.cid =c.cid ORDER BY temp.countc desc ,c.cid asc;


结果：
01	语文	80	31	64.5	6	0.3333	0	0.3333	0.3333	0
02	数学	90	30	72.66667	6	0.1667	0.1667	0	0.5	0.1667
03	英语	99	20	68.5	6	0.3333	0	0	0.3333	0.3333

#18 查询各科成绩前三名的学生记录（mysql 分组组内Topn） （现在还有一点问题就是数据充重复得时候没考虑到）
# 1 组内统计个数 Having过滤 
 
 SELECT 
 s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex,
 c.cid,
 c.Cname,
 t.score
 from student s 
 INNER join (
 
	SELECT
		sc1.Cid,
		sc1.sid,
		sc1.score,
		sum( ( CASE WHEN sc2.score > sc1.score  THEN 1 ELSE 0 END ) ) AS "count" 
	FROM
		sc sc1
		LEFT JOIN sc sc2 ON sc1.cid = sc2.cid 
	GROUP BY
		sc1.Cid,
		sc1.sid,
		sc1.score 
	HAVING
		( count < 3 ) 
	ORDER BY
		sc1.Cid,
		sc1.score DESC 
	) T ON s.sid = t.sid
	LEFT JOIN course c ON t.cid = c.cid;

#查询结果
01	赵雷	20	1990-01-01 00:00:00	women	01	语文	80
03	孙风	30	1990-05-20 00:00:00	man	01	语文	80
05	周梅	30	1991-12-01 00:00:00	women	01	语文	76
01	赵雷	20	1990-01-01 00:00:00	women	02	数学	90
07	郑竹	35	1989-07-01 00:00:00	women	02	数学	89
05	周梅	30	1991-12-01 00:00:00	women	02	数学	87
01	赵雷	20	1990-01-01 00:00:00	women	03	英语	99
07	郑竹	35	1989-07-01 00:00:00	women	03	英语	98
02	钱电	19	1990-12-21 00:00:00	man	03	英语	80
03	孙风	30	1990-05-20 00:00:00	man	03	英语	80

#组内分组topN 查询出每科成绩前三得信息
SELECT sc1.Cid,sc1.sid,sc1.score as "score" from  sc sc1 

where 3> (SELECT count(*) from sc sc2 where sc2.score >sc1.score and  sc1.Cid = sc2.Cid  )

ORDER BY sc1.Cid ,sc1.score desc 

#查询结果
01	01	80
01	03	80
01	05	76
02	01	90
02	07	89
02	05	87
03	01	99
03	07	98
03	02	80
03	03	80


遇到得问题：  成绩表中缺少字段Id 否则可通过分组排序加having(count(id)<3)完成

 #  19. 查询每门课程被选修的学生数 
SELECT
	c.cname,
	t.count AS "选修人数" 
FROM
	course c
	LEFT JOIN ( SELECT Cid, count( sc.sid ) AS "count" FROM sc GROUP BY ( sc.cid ) ) T ON c.cid = t.Cid
#结果
语文	6
数学	6
英语	6
 # 20 查询出只选修两门课程的学生学号和姓名 
 #按学生分组  统计所选修的课程数据 通过HAving做筛选
 
  SELECT 
 s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex
 from student s
 where  sid in  (
	SELECT sc.sid
	from sc GROUP BY sc.Sid  HAVING(count(sc.Cid)=2 )
 );
#结果
05	周梅	30	1991-12-01 00:00:00	women
06	吴兰	45	1992-03-01 00:00:00	women
07	郑竹	35	1989-07-01 00:00:00	women
 #21. 查询男生、女生人数
 #主表信息为学生表
SELECT
CASE	WHEN s.ssex = 'women' THEN "女" ELSE "男" END AS "性别",
	count( 1 ) AS "人数" 
FROM
	student s 
GROUP BY
	s.ssex
#结果
男	3
女	5
#22. 查询同名同性学生名单，并统计同名人数
# 由于没有数据 手动插入一条数据
#insert into student values('09',"王菊",32,'1990-01-20 00:00:00','women');
# INSTR(str,str1)  判断一个字符串在另一个字符串中的位置   返回结果0 不存在  >0 存在
#GROUP_CONCAT(expr) 分组拼接  分组后对结果数据进行拼接

SELECT 
 s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex
 from student s where INSTR((SELECT 
GROUP_CONCAT("'",s.sid,"'") 
 from student s
 GROUP BY s.sname,s.ssex HAVING (count(1)>1)),s.sid)>0;
#结果
08	王菊	20	1990-01-20 00:00:00	women
09	王菊	32	1990-01-20 00:00:00	women
#23. 查询 1990 年出生的学生名单 
# 按照出生日期进行查询 只需要年
# 第一种方式直接like '1990%'
# 第二种方式使用时间函数获取生日的年 进行比对

SELECT 
 s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex
 from student s 
 where s.sbirthday like '1990%';

SELECT 
 s.sid,
 s.sname,
 s.sage ,
 s.sbirthday,
 s.ssex
 from student s 
 where DATE_FORMAT(s.sbirthday,"%Y")  = '1990';
#结果
01	赵雷	20	1990-01-01 00:00:00	women
02	钱电	19	1990-12-21 00:00:00	man
03	孙风	30	1990-05-20 00:00:00	man
04	李云	50	1990-08-06 00:00:00	man
08	王菊	20	1990-01-20 00:00:00	women
09	王菊	32	1990-01-20 00:00:00	women
 #24. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 平均分保留两位小数
 
 # 每门课程 按照课程分组 求平均值  
 
 
 SELECT c.cname ,
 c.cid,
 round(t.avgScore,2) as "平均分"
 
 
 from course c
 left join (
 SELECT sc.cid  as "cid",
 AVG(sc.score)  as "avgScore"
 from sc GROUP BY sc.Cid
 )T on t.cid = c.cid 
 
 ORDER BY t.avgScore desc,c.cid asc;

#结果
数学	02	72.67
英语	03	68.5
语文	01	64.5
#25. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 
 
 # 求学生的平均成绩 和学生的信息
 
SELECT
	s.sid,
	s.sname,
	ROUND( t.avgScore )  AS "平均成绩"
FROM
	student s
	INNER JOIN (
SELECT
	sc.Sid AS "sid",
	AVG( sc.score ) AS "avgScore" 
FROM
	sc 
GROUP BY
	sc.sid 
HAVING
	( avgScore > 85 ) 
	) T ON s.sid = t.sid;

#结果
01	赵雷	90
07	郑竹	94
 #26 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数 
 
 SELECT 
  s.sid,
 s.sname,

 sc.score
 from student s 
  left join sc on sc.sid =s.sid
	left join course c on sc.cid= c.cid 
	where c.cname="数学" and sc.score<60;
#结果
04	李云	30
#27 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
 SELECT 
  s.sid,
 s.sname,
 t.maxScore
 from student s INNER join 
 (
SELECT sc.sid,c.cid,c.cname,MAX( DISTINCT sc.score)as "maxScore" from course c
left join teacher t on t.tid = c.tid 
LEFT join sc on c.cid=sc.cid
where  t.Tname ="张三") t on t.sid =s.sid;

#结果
01	赵雷	90
#28. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
# 查询出最高成绩然后过滤信息即可
SELECT
	s.sid,
	s.sname,
	sc.score 
FROM
	student s
	LEFT JOIN sc ON s.sid = sc.sid 
WHERE
	sc.score = (
SELECT
	MAX( sc.score ) 
FROM
	course c
	LEFT JOIN teacher t ON t.tid = c.tid
	LEFT JOIN sc ON c.cid = sc.cid 
WHERE
	t.Tname = "张三" 
	)
#结果
01	赵雷	90
#29. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 
# 成绩相同 课程不同

SELECT
	s.sid,
	s.sname,
	c.cid,
	c.Cname,
	t.score 
FROM
	student s
	INNER JOIN (
SELECT
	sc1.sid,
	sc1.score,
	sc1.cid 
FROM
	sc sc1
	INNER JOIN sc sc2 ON sc1.score = sc2.score 
	AND sc1.cid != sc2.cid 
GROUP BY
	sc1.sid 
	) T ON t.sid = s.sid
	LEFT JOIN course c ON t.cid = c.cid;
#结果
01	赵雷	01	语文	80
02	钱电	03	英语	80
03	孙风	02	数学	80
#30. 查询各学生的年龄，只按年份来算 
# 年份格式化相减即可
	
SELECT 
 s.sid,
 s.sname,
 s.sbirthday,
 s.ssex,
 DATE_FORMAT(NOW(),"%Y")- DATE_FORMAT( s.sbirthday,"%Y") as age
 from student s;
#结果
01	赵雷	1990-01-01 00:00:00	women	29
02	钱电	1990-12-21 00:00:00	man	29
03	孙风	1990-05-20 00:00:00	man	29
04	李云	1990-08-06 00:00:00	man	29
05	周梅	1991-12-01 00:00:00	women	28
06	吴兰	1992-03-01 00:00:00	women	27
07	郑竹	1989-07-01 00:00:00	women	30
08	王菊	1990-01-20 00:00:00	women	29
09	王菊	1990-01-20 00:00:00	women	29
 #31. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
 #If(flag,xxx,yyy) 表达式 类似于三目表达式   if flag为true xxx 否则 yyy
 SELECT 
 s.sid,
 s.sname,
 s.sbirthday,
 s.ssex,
 s.sage,
 if ((DATE_FORMAT( s.sbirthday,"%m%d")<DATE_FORMAT(now(),"%m%d")),s.sage+1,s.sage) as "age"
 from student s;

#结果

01	赵雷	1990-01-01 00:00:00	women	20	21
02	钱电	1990-12-21 00:00:00	man	19	19
03	孙风	1990-05-20 00:00:00	man	30	31
04	李云	1990-08-06 00:00:00	man	50	50
05	周梅	1991-12-01 00:00:00	women	30	30
06	吴兰	1992-03-01 00:00:00	women	45	46
07	郑竹	1989-07-01 00:00:00	women	35	35
08	王菊	1990-01-20 00:00:00	women	20	21
09	王菊	1990-01-20 00:00:00	women	32	33

有什么好的实现方式可以互相讨论哈。

sql 执行脚本 

链接：https://pan.baidu.com/s/1EULRzh6ciGXwROoMpjW1fQ 
提取码：rpl4 
复制这段内容后打开百度网盘手机App，操作更方便哦

Master_slaves
关注
  • 2
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
数据库SQL查询语句练习题.doc
04-10
常见语句 更新：update table1 set field1=value1 where 范围 查找：select * from table1 where field1 like ’%value1%’ （所有包含‘value1’这个模式的字符串） 排序：select * from table1 order by field1...
SQL查询成绩大于某个值的所有学生姓名
weixin_44971857的博客
04-16 2241
use MyDB go if OBJECT_ID('Stu','U') > 0 drop table Stu create table Stu( Id int identity(1,1) not null primary key, [Name] nvarchar(20), Course nvarchar(10), Score int ) insert into MyDB.dbo.Stu values('张三', '语文', 78) insert into MyDB.dbo.Stu va
SQL查询语句练习题27道
weixin_34221773的博客
10-15 2912
练习环境为：XP+SQL2000数据库 练习使用的数据库为：学生管理数据库 数据库下载地址为: http://download.csdn.net/download/friendan/4648150 说明 这是我在学习数据库课时，老师给的27道SELECT语句练习题，在写这篇文章时，老师并没有给参考答案， 写这篇文章的目的完全是为了加深我对SQL语句的理解和方便我以后...
MySQL查询
weixin_39857851的博客
10-14 622
一、 现有表结构以及数据如下： 表1：cust 表2：mark (1)写出计算所有学生的英语平均成绩的sql语句 SELECT avg(english) FROM cust a JOIN mark b ON a.Studentno = b.studentno; (2）现有五个学生，其学生学号假定为11,22,33,44,55；请用一条sql语句实现列出这五个学生的数学成绩及其姓名、学生地址、电话号码； SELECT math AS 数学成绩, NAME AS 姓名,
SQL简单实践之sql语句
类菌体 .net
11-06 2060
 小引：大家都知道SQL是结构化查询语言，是关系数据库的标准语言，是一个综合的，功能极强的同时又简洁易学的它集级数据查询（Data Quest），数据操纵（Data Manipulation），数据定义（Data Definition），数据控制（Data Control）于一体即：SQL语言包含4个部分： 　　※ 数据定义语言(DDL)，例如：CREATE、DROP、AL
经典sql面试题
10-25
经典数据库面试题，提供给数据库面试人员进行面试前练习
sql常见习题整理1.md
01-04
sql基础练习
经典的SQL笔试题题目
08-30
经典sql练习，几道经典的SQL笔试题题目。sql就在于多多练习
50道sql练习.sql
09-14
里面包含常见的50道sql练习题答案，都是自己做亲手做的，带注释
常见的sql经典练习题
04-11
常用的经典sql语句，是你的sql编写能力得到瞬间提高
mysql最强练习代码.sql
04-27
练习题分为初级、中级和高级三个难度等级，每个等级包含若干道题目，涵盖了常见的 SQL 操作、数据处理、数据查询、数据过滤、数据分析等方面。 适用人群： 本资源适用于想要学习和掌握 MySQL 数据库应用的各个方面...
mysql-45练习题.sql
02-10
mysql练习题，里面包含了自己的一些思考，方法一主要是自己的解法，方法二是看网上的答案。小白开始写，慢慢熟悉了常见的查询语句，但对于排序和生日的计算，仍需加深学习，其他部分，需要理解方法二的思路。总之，...
sql面试常见笔试题（内含答案）
01-16
sql面试常见笔试题（内含答案）适合应届生练习使用
sql，数据库题，自行练习
03-03
常见的sql练习，如果可以做出来基本上日常工作没问题
MySQL语句面试实例
qq_29423387的博客
10-19 590
参考链接 需要数据库表：（使用phpmyadmin创建） 学生表-Student(SID,Sname,Sage,Ssex) --SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别； 课程表-Course(CID,Cname,TID) --CID --课程编号,Cname 课程名称,TID 教师编号； 教师表-Teacher(TID,Tname) --TID 教师编号,Tname 教师姓名； 成绩表-SC(SID,CID,score) --SID 学生编号,CID 课程编号,sc
SQL语句操作练习（一）
热门推荐
卡戎星8023的博客
03-25 1万+
文章目录题目查询学生选课表中的全部数据查询计算机系的学生的姓名、年龄查询成绩在70-80分之间的学生的学号、课程号和成绩查询计算机系年龄在18-20之间且性别为“男”的学生的姓名、年龄查询课程号为“c001”的课程的最高的分数查询计算机系学生的最大年龄和最小年龄统计每个系的学生个数统计每门课程的选课人数和考试最高分数统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果查询总成绩超过200分的学生，要求列出学号、总成绩考察内容 题目 利用Student、Course、SC表结构实现如下操作： 1.查询
SQL查询语法30例
软件自动化测试技术交流、资源分享
06-14 4942
01基础表
SQL简答
qq_53334209的博客
04-28 1577
1.请使用 select into...outfile 语句，备份数据库 db_test 中表 content 的全部数据到 c 盘的 backup 目录下一个名为 backupcontent. txt 的文件中 答： usedb_test; SELECT* FROMcontent INTOOUTFILE ‘C:/baczup/backupcontent. txt’ FIELDS TERMINATED BY ',’OPTIONALY ENCLOSED BY'"'LINES TE...
sql语句练习题csdn
最新发布
07-21
在CSDN上，可以找到很多作者分享了自己整理的SQL练习题，或者其他用户的提问和分享。这些题目和答案一般会包括数据库查询、数据插入、更新、删除、表连接等常见操作。学习者可以根据自己的需要选择感兴趣的题目进行...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Master_slaves

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值