一：学生、课程、分数、教师表建立
student学生表
create table Student(
Sno varchar(10),
Sname varchar(10),
Sage datetime,
Ssex varchar(10));
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' , '女');
course课程表
create table Course(
Cno varchar(10),
Cname nvarchar(10),
T varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
teacher教师表
create table Teacher(
Tno varchar(10),
Tname nvarchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
sco成绩表
create table SCo(
Sno varchar(10),
Cno varchar(10),
score decimal(18,1));
insert into SCo values('01' , '01' , 80);
insert into SCo values('01' , '02' , 90);
insert into SCo values('01' , '03' , 99);
insert into SCo values('02' , '01' , 70);
insert into SCo values('02' , '02' , 60);
insert into SCo values('02' , '03' , 80);
insert into SCO values('03' , '01' , 80);
insert into SCo values('03' , '02' , 80);
insert into SCo values('03' , '03' , 80);
insert into SCo values('04' , '01' , 50);
insert into SCo values('04' , '02' , 30);
insert into SCo values('04' , '03' , 20);
insert into SCo values('05' , '01' , 76);
insert into SCo values('05' , '02' , 87);
insert into SCo values('06' , '01' , 31);
insert into SCo values('06' , '03' , 34);
insert into SCo values('07' , '02' , 89);
insert into SCo values('07' , '03' , 98);
二：练习题
1.1 查询同时存在" 01 "课程和" 02 "课程的情况
方法一：
select
*
from (select * from sco where cno="01")a
inner join (select * from sco where cno="02") b
on a.sno=b.sno;
方法二：
select
*
from sco a
inner join sco b
on a.sno=b.sno
where a.cno='01' and b.cno='02';
1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select
*
from (SELECT * FROM SCO WHERE CNO='01') a
left join (SELECT * FROM SCO WHERE CNO='02') b
on a.sno=b.sno;
方法二：
select
*
from (SELECT * FROM SCO WHERE CNO='01') a
left join sco b
on a.sno=b.sno and b.cno='02';
1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
select
*
from (select * from sco where cno='02') a
where a.sno not in (select sno from sco where cno='01');
- 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select * from sco;
select
a.sno,
a.sname,
b.avg_score
from student a
inner join
(select
sno,
avg(score) as avg_score
from sco
group by sno having avg_score>=60) b
on a.sno=b.sno;`
- 查询在 SC 表存在成绩的学生信息
select
s.*
from student s
inner join
(select
distinct sno
from sco where score is not null) b
on s.sno=b.sno;
- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select
a.sno,
a.sname,
b.cons,
b.sum_score
from student a
left join
(select
sno,
count(1) as cons,
sum(score) as sum_score
from sco
group by sno) b
on a.sno=b.sno;
这个执行不了的原因？？？
select
a.sno,
a.sname,
count(b.cno) as cons,
sum(b.score) as sum_score
from student a
left join sco b
on a.sno=b.sno group by a.sno;
- 查询「李」姓老师的数量
select
count(*) as cons
from teacher
where tname like '李%';
#6. 查询学过「张三」老师授课的同学的信息
select * from course;
select * from student;
select * from sco;
select * from teacher;
先求出张三老师教授的课程，关联 course 和teacher表;
select
c.cno
from teacher t
join course c
on t.tno=c.t
where t.tname='张三';
在求出对应此课程学习的学生编号；
select
sno
from sco
where cno= (select c.cno from teacher t join course c on t.tno=c.t where t.tname='张三');
关联张三
select
a.*
from student a
inner join (select sno
from sco
where cno= (select c.cno from teacher t join course c on t.tno=c.t where t.tname='张三')) b on a.sno=b.sno;
- 查询没有学全所有课程的同学的信息 (是否包含8王菊) 如包含left 如不包含不加left;
select
a.*
from student a
join sco b
on a.sno=b.sno
group by a.sno
having count(b.cno)<(select count(cno) from course);
- 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select cno from sco where sno=01;
select
sno
from sco
where cno in (select cno from sco where sno=01)
group by sno;
select
*
from student s
right join
(select
sno
from sco
where cno in (select cno from sco where sno=01) group by sno) b
on s.sno=b.sno;
- 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
select cno from sco where sno='01';
select sno from sco where cno not in (select cno from sco where sno='01');
select count(cno) as cnos from course;
select
a.*
from student a
inner join sco b
on a.sno=b.sno
where a.sno not in (select sno from sco where cno not in (select cno from sco where sno='01'))
group by a.sno
having count(b.cno)=(select count(cno) as cnos from sco where sno='01') and a.sno<>1;
- 查询没学过"张三"老师讲授的任一门课程的学生姓名
select
a.sno
from sco a
left join course b
on a.cno=b.cno
left join teacher c
on c.tno=b.t
where c.Tname='张三';
select
*
from student
where sno not (select
a.sno
from sco a
left join course b
on a.cno=b.cno
left join teacher c
on c.tno=b.t
where c.Tname='张三');
???#11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select
a.sno,
b.sname,
avg(a.score)
from (select * from sco where score<60) a
left join student b
on a.sno=b.sno
group by a.sno
having count(a.sno)>=2;
- 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select
sno,
score
from sco
where cno='01'and score<60;
select
b.*,
a.score
from (select
sno,
score
from sco
where cno='01'and score<60) a
left join student b
on a.sno=b.sno
order by a.score desc;
select
b.*,
a.score
from sco a
left join student b
on a.sno=b.sno
where a.score<60 and a.cno='01' order by a.score desc;
- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select
sno,
avg(score) as avg_score
from sco
group by sno;
select
a.*,
b.avg_score
from sco a
left join (select
sno,
avg(score) as avg_score
from sco
group by sno) b
on a.sno=b.sno
order by b.avg_score desc;
- 查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
提示：分组聚合，这里考察条件计数技巧；
1/各科的平均分/最高分/及格率；
select
cno,
max(score) as '最高分',
min(score) as '最低分',
avg(score) as '平均分',
count(1) as '选修人数',
sum(case when score>=60 then 1 else 0 end)/count(1) as '及格率',
sum(case when score >=70 and score<80 then 1 else 0 end)/count(1) as '中等率',
sum(case when score >=80 and score<90 then 1 else 0 end)/count(1) as '优良率',
sum(case when score >=90 then 1 else 0 end)/count(1) as '优秀率'
from sco
group by cno
order by '选修人数' desc, cno asc;
#15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select @rank:=1;
select @rank;
select
a.*,
@rank:=@rank+1 as rank
from sco as a ,(select @rank:=0) as t
order by score desc;
15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
select
sno,
cno,
score,
case when @sco=score then @rank
else @rank:=@rank+1 end as rn,
@sco:=score
from sco, (select @rank:=0, @score:=null) as t
order by score desc;
select
sno,
cno,
score,
case when @sco=score then @rank
when @sco:=score then @rank:=@rank+1
end as rn
from sco, (select @rank:=0, @sco:=null) as t
order by score desc;
#16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
#自定义变量
#定义方法一：
set @a:=2;
#定义方法二：
select @b:=4;
#重新赋值就是修改值；
select @b:=6;
#先求学生的总成绩：
select
sno,
sum(score) as scos
from sco
group by sno
order by scos desc;
#名次排序：
select
a.*,
@rank:=if(@sco=scos,'',@rank+1) as rank,
@sco:=scos
from (select
sno,
sum(score) as scos
from sco
group by sno
order by scos desc) a
inner join
(select @sco:=null, @rank:=0) b;
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
select
a.*,
@rank:=if(@sco=scos, @rank, @rank+1) as rank,
@sco:=scos
from (select
sno,
sum(score) as scos
from sco
group by sno
order by scos desc) a
inner join
(select @sco:=null, @rank:=0) b;
- 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select* from course
select
a.cno,
b.Cname,
count(sno) as '选修人数',
sum(case when score>=85 then 1 else 0 end)/count(1) as '[100-85]',
sum(case when score>=70 and score<85 then 1 else 0 end)/count(1) as '[85-70]',
sum(case when score>=60 and score<70 then 1 else 0 end)/count(1) as '[70-60]',
sum(case when score>=0 and score<60 then 1 else 0 end)/count(1) as '[60-0]'
from sco a
left join course b
on a.cno=b.cno
group by cno;
#增加% select concat('date','frog')用法；
select
a.cno,
b.Cname,
count(sno) as '选修人数',
concat(sum(case when score>=85 then 1 else 0 end)/count(1) *100, '%') as '[100-85]',
concat(sum(case when score>=70 and score<85 then 1 else 0 end)/count(1) *100,'%') as '[85-70]',
concat(sum(case when score>=60 and score<70 then 1 else 0 end)/count(1) *100,'%') as '[70-60]',
concat(sum(case when score>=0 and score<60 then 1 else 0 end)/count(1) *100,'%') as '[60-0]'
from sco a
left join course b
on a.cno=b.cno
group by cno;
- 查询各科成绩前三名的记录
select
a.*
from sco a
where (select count(*) from sco b where a.cno=b.cno and a.score<b.score)<3
order by cno desc, score desc;
- 查询每门课程被选修的学生数
select
cno,
count(1) as cons
from sco
group by cno;
- 查询出只选修两门课程的学生学号和姓名
select
a.sno,
b.sname
from sco a
left join student b
on a.sno=b.sno
group by a.sno,b.sname
having count(1)=2;
- 查询男生、女生人数
select
ssex,
count(ssex) as cons
from student
group by ssex;
- 查询名字中含有「风」字的学生信息
select
*
from student
where sname like '%风%';
??#23. 查询同名同性学生名单，并统计同名人数
select
a.sname,
a.ssex,
count(a.sno) as cons
from student a
inner join student b
on a.sname=b.sname and a.ssex=b.ssex and a.sno!=b.sno
group by a.sname, a.ssex;
- 查询 1990 年出生的学生名单 year 函数 select year('2020-02-01')
select
*
from student
where date_format(sage, '%Y')=1990;
select
*
from student
where year(sage)='1990';
25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select
cno,
avg(score) as avg_score
from sco
group by cno
order by avg_score desc, cno asc;
- 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select
a.sno,
b.sname,
avg(a.score) as avg_score
from sco a
left join student b
on a.sno=b.sno
group by a.sno
having avg_score>=85;
- 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
方法一
select
c.sname,
b.cname,
a.score
from sco a
left join course b
on a.cno=b.cno
left join student c
on a.sno=c.sno
where b.cname='数学' and a.score<60;
#方法二
select
a.sname,
b.score
from student a
join sco b
on a.sno=b.sno
where b.cno=(select cno from course where cname='数学')
and b.score<60;
- 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select
a.*,
b.cno,
b.score,
c.cname,
c.t
from student a
left join sco b
on a.sno=b.sno
left join course c
on b.cno=c.cno;
29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select
b.sname,
c.cname,
a.score
from sco a
left join student b
on a.sno=b.sno
left join course c
on a.cno=c.cno
where a.score>70;
#30.查询不及格的课程
select
a.*
from course a
where a.cno in (select distinct cno from sco where score<60);
- 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select
a.sno,
b.sname
from sco a
left join student b
on a.sno=b.sno
where a.cno='01' and a.score>80;
32. 求每门课程的学生人数
select
cno,
count(1) as cons
from sco
group by cno;
- 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select
b.*,
max(a.score) as max_score
from sco a
left join student b
on a.sno=b.sno
left join course c
on a.cno=c.cno
left join teacher t
on t.tno=c.t
where t.tname='张三';
???#34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select
*
from
(select
a.*,
case when @score=score then @rank
when @score:=score then @rank:=@rank+1 end as rn
from
(select
b.*,
a.score
from sco a
left join student b
on a.sno=b.sno
left join course c
on a.cno=c.cno
left join teacher t
on t.tno=c.t
where t.tname='张三') a, (select @score:=null, @rank:=0) t) s;
???#35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select
a.*
from sco a
inner join sco b
on a.sno=b.sno
where a.cno!=b.cno and a.score=b.score
group by a.sno,a.cno;
???#36. 查询每门功成绩最好的前两名 #用户变量
select
sno,
cno,
score,
rank
from
(select
sco.*,
@rank:=if(@c_cno=cno, if(@sco=score, @rank, @rank+1), 1) as rank,
@sco:=score,
@c_cno:=cno
from sco, (select @sco:=null, @rank:=0, @c_cno:=null) b
order by cno, score desc) a
where a.rank<3;
- 统计每门课程的学生选修人数（超过 5 人的课程才统计）。
select
cno,
count(sno) as cons
from sco
group by cno
having count(1)>5;
- 检索至少选修两门课程的学生学号
select
sno,
count(1) as cons
from sco
group by sno
having count(1)>=2
- 查询选修了全部课程的学生信息
select
*
from student
where sno
in (select sno from sco group by sno having count(sno)=(select count(distinct cno) from sco));
select
sno
from sco
group by sno
having count(1)=(select count(1) from course);
select
a.*
from student a
where (select count(1) from sco b where a.sno=b.sno)
=(select count(1) from course);
#最好是这么写
select
*
from student a
right join
(select
sno
from sco
group by sno
having count(1)=(select count(*) from course)) b
on a.sno=b.sno;
- 查询各学生的年龄，只按年份来算 year 函数
select CURRENT_DATE() #整取年月日
select CURDATE() #整取年月日
select now() #年月日时间
#方法一：
select
*,
(date_format(now(), '%Y')-date_format(sage, '%Y')) as age
from student;
#方法二：
select
*,
year(now())-year(sage) as age
from student;
- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select* from student;
#timestampdiff 日期相减函数
select timestampdiff(year, '2002-05-01', '2003-06-01 00:10:50');
select timestampdiff(day, '2002-05-01', '2001-01-01');
select timestampdiff(hour, '2008-08-08 12:00:00', '2008-08-08 00:00:00');
#获取当前时间
select now();
select curdate();
select
*,
timestampdiff(year, sage, now()) as age
from student;
- 查询本周过生日的学生 week 函数
select week('2020/5/21');
select
*,
week(sage),
week(now())
from student
where week(sage)=week(now());
- 查询下周过生日的学生
select week(now())+1
select
*,
week(sage),
week(now())+1
from student
where week(sage)=week(now())+1
- 查询本月过生日的学生
方法一：
select *
from student
where date_format(sage,'%m')=date_format(now(),'%m');
方法二：
select
*
from student
where month(sage)=month(now());
- 查询下月过生日的学生;
select
*
from student
where month(sage)=month(now())+1;
- 查询上月过生日的学生;
select *
from student
where date_format(sage,'%m')=date_format(date_sub(now(), interval 2 month),'%m');
select
*
from student
where month(sage)=month(now())-1;```
