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,操作更方便哦