sql练习题
问题及描述：
–1.学生表
Student(s_id,s_name,s_birth,s_sex) --s_id 学生编号,s_name 学生姓名,s_birth 出生年月,s_sex 学生性别
–2.课程表
Course(c_id,c_name,t_id) --c_id --课程编号,c_name 课程名称,t_id 教师编号
–3.教师表
Teacher(t_id,t_name) --t_id 教师编号,t_name 教师姓名
–4.成绩表
Score(s_id,c_id,s_score) --s_id 学生编号,c_id 课程编号,s_score 分数
*/
创建测试数据
create table Student(s_id varchar(10),s_name nvarchar(10),s_brith datetime,s_sex nvarchar(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' ,'女');
create table Course(c_id varchar(10),c_name nvarchar(10),t_id varchar(10));
insert into Course values('01' ,'语文' ,'02');
insert into Course values('02' ,'数学' ,'01');
insert into Course values('03' ,'英语' ,'03');
create table Teacher(t_id varchar(10),t_name nvarchar(10));
insert into Teacher values('01' ,'张三');
insert into Teacher values('02' ,'李四');
insert into Teacher values('03' ,'王五');
create table Score(s_id varchar(10),c_id varchar(10),s_score decimal(18,1));
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);
５０道练习题
查询"01"课程比"02"课程成绩高的学生的信息及课程分数
解题思路
- 需要三张表
- 学生信息 stu
- 01课程 01sco
- 02课程 02sco
- 需要展现的信息
- stu的所有信息
- 01与02课程的分数
- 关联
- stu.学生id = 01sco.学生id = 02sco.学生id
- 逻辑
- 01sco.课程编号 = 01
- 01sco.课程编号 = 02
- 01sco.成绩 > 02sco.成绩
select stu.*, 01sco.s_score '01', 02sco.s_score '02'
from student stu,
score 01sco,
score 02sco
where stu.s_id = 01sco.s_id
and stu.s_id = 02sco.s_id
and 01sco.c_id = 01
and 02sco.c_id = 02
and 01sco.s_score > 02sco.s_score;
select stu.*, 01sco.s_score "01", 02sco.s_score "02"
from student stu
right join score 01sco on stu.s_id = 01sco.s_id and 01sco.c_id = 01
right join score 02sco on stu.s_id = 02sco.s_id and 02sco.c_id = 02
where 01sco.s_score > 02sco.s_score;
查询"01"课程比"02"课程成绩低的学生的信息及课程分数
2.1、查询同时存在"01"课程和"02"课程的情况
select a.*, b.s_score '01', c.s_score '02'
from Student a,
Score b,
Score c
where a.s_id = b.s_id
and a.s_id = c.s_id
and b.c_id = '01'
and c.c_id = '02'
and b.s_score < c.s_score;
2.2、查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况
select a.*, b.s_score '01', c.s_score '02'
from student a
left join score b on a.s_id = b.s_id and b.c_id = '01'
left join score c on a.s_id = c.s_id and c.c_id = '02'
where b.s_score < c.s_score;
查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select a.s_id, a.s_name, cast(avg(b.s_score) as decimal(18, 2)) avg_s_score
from Student a,
score b
where a.s_id = b.s_id
group by a.s_id, a.s_name
having cast(avg(b.s_score) as decimal(18, 2)) >= 60
order by a.s_id;
查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
4.1、查询在sc表存在成绩的学生信息的SQL语句。
select a.s_id,a.s_name,cast(avg(b.s_score) as decimal(18,2)) avf_s_score
from student a,
score b
where a.s_id = b.s_id
group by a.s_id, a.s_name
having cast(avg(b.s_score) as decimal(8,1)) <=60
order by a.s_id;
4.2、查询在sc表中不存在成绩的学生信息的SQL语句。
select a.s_id, a.s_name, ifnull(cast(avg(b.s_score) as decimal(18, 2)), 0) avg_s_score
from Student a
left join score b
on a.s_id = b.s_id
group by a.s_id, a.s_name
having ifnull(cast(avg(b.s_score) as decimal(18, 2)), 0) < 60
order by a.s_id
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
5.1、查询所有有成绩的SQL。
select a.s_id 编号, a.s_name 姓名, count(b.c_id) 选课总数, sum(s_score) 所有课程总成绩from Student a, Score bwhere a.s_id = b.s_idgroup by a.s_id, a.s_nameorder by a.s_id;
5.2、查询所有(包括有成绩和无成绩)的SQL。
select a.s_id 编号,a.s_name 学生姓名,count(b.c_id) 选课总数,sum(s_score) 所有课程总成绩from student a left join score b on a.s_id = b.s_idgroup by a.s_id, a.s_nameorder by a.s_id;
查询"李"姓老师的数量
方法1
select count(t.t_name) 李姓老师的数量from teacherwhere t_name like '李%';
方法2
select count(t_name) 李姓老师的数量from Teacherwhere left(t_name, 1) = '李';
查询学过"张三"老师授课的同学的信息
select s.*, t.t_namefrom student s left join score s2 on s.s_id = s2.s_id left join course c on s2.c_id = c.c_id left join teacher t on c.t_id = t.t_idwhere t.t_name = '张三'order by s.s_id;
查询没学过"张三"老师授课的同学的信息
select a.*from student awhere s_id not in ( #先查询出上过张三老师课同学的编号 select distinct s.s_id from score s left join course c on s.c_id = c.c_id left join teacher t on t.t_id = c.t_id where t.t_name = '张三')order by a.s_id;
查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
–方法1
select Student.*from Student, Scorewhere Student.s_id = Score.s_id and Score.c_id = '01' and exists(Select 1 from Score Score_2 where Score_2.s_id = Score.s_id and Score_2.c_id = '02')order by Student.s_id;
–方法2
select Student.*from Student, Scorewhere Student.s_id = Score.s_id and Score.c_id = '02' and exists(Select 1 from Score Score_2 where Score_2.s_id = Score.s_id and Score_2.c_id = '01')order by Student.s_id;
–方法3
select m.*from Student mwhere s_id in ( select s_id from ( select distinct s_id from Score where c_id = '01' union all select distinct s_id from Score where c_id = '02' ) t group by s_id having count(1) = 2 )order by m.s_id;
查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
–方法1
select Student.*from Student, Scorewhere Student.s_id = Score.s_id and Score.c_id = '01' and not exists(Select 1 from Score Score_2 where Score_2.s_id = Score.s_id and Score_2.c_id = '02')order by Student.s_id;
–方法2
select Student.*from Student, Scorewhere Student.s_id = Score.s_id and Score.c_id = '01' and Student.s_id not in (Select Score_2.s_id from Score Score_2 where Score_2.s_id = Score.s_id and Score_2.c_id = '02')order by Student.s_id;
查询没有学全所有课程的同学的信息
select *from student swhere s.s_id not in ( select s1.s_id from score s1 group by s1.s_id having count(1) = (select count(1) from course));
–11.1、
select Student.*from Student, Scorewhere Student.s_id = Score.s_idgroup by Student.s_id, Student.s_name, student.s_brith, Student.s_sexhaving count(c_id) < (select count(c_id) from Course);
–11.2
select Student.*from Student left join Score on Student.s_id = Score.s_idgroup by Student.s_id, Student.s_name, Student.s_brith, Student.s_sexhaving count(c_id) < (select count(c_id) from Course);
查询至少有一门课与学号为"01"的同学所学相同的同学的信息
解题思路
- 需要三张表
- 学生信息 stu
- 课程信息 sco
- 需要展现的信息
- 所学课程相同的同学所有信息
- 关联
- stu.s_id = sco.s_id
- 逻辑
- 先筛选出学号为'01'的所有课程1d
- 01sco.课程编号 = 01
- 01sco.课程编号 = 02
- 01sco.成绩 > 02sco.成绩
select distinct stu.*from score sco, student stuwhere sco.s_id = stu.s_idand sco.c_id in (select score.c_id from score where s_id = '01')and stu.s_id <> '01';
查询和"01"号的同学学习的课程完全相同的其他同学的信息 （有点难，没理解）
select Student.*from Studentwhere s_id in (select distinct Score.s_id from Score where s_id <> '01' and Score.c_id in (select distinct c_id from Score where s_id = '01') group by Score.s_id having count(1) = (select count(1) from Score where s_id = '01'));
查询没学过"张三"老师讲授的任一门课程的学生姓名
解题思路
先查张三老师教过的学生的id
再从student表中剔除上面的id
select student.*from studentwhere student.s_id not in (select distinct sc.s_id from score sc, course, teacher where sc.c_id = course.c_id and course.t_id = teacher.t_id and teacher.t_name = '张三')order by student.s_id;
select stu.*from student stuwhere stu.s_id not in ( select s.s_id from course c right join score s on s.c_id = c.c_id right join teacher t on c.t_id = t.t_id where t.t_name = '张三')order by stu.s_id;
–15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
1.先查出两门以上成绩不及格的同学学号选哪个表？ 1.需要看学号 2.需要看分数查询60分以下2.查询上列同学的学号，姓名及其平均成绩需要那张表 1.需要看学生信息 2.需要看分数求平均成绩
select s.s_id,s.s_name,cast(avg(s2.s_score) as decimal(18,2)) as AvgScorefrom student s, score s2where s.s_id = s2.s_id and s.s_id in ( select s.s_id from score s where s.s_score < 60 group by s.s_id having count(1) >= 2)group by s.s_id, s.s_name;
检索"01"课程分数小于60，按分数降序排列的学生信息
# 检索"01"课程分数小于60，按分数降序排列的学生信息# 需要什么表# 1.查看分数需要score# 2.查看学生信息需要student
select *from student sleft join score s2 on s.s_id = s2.s_idwhere s2.s_score < 60and s2.c_id = '01'order by s2.s_score desc ;
select s.*,s2.*from student s, score s2where s2.s_id = s.s_idand s2.s_score < 60and s2.c_id = '01'order by s2.s_score desc ;
(跳过)按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
–17.1 SQL 2000 静态
select a.s_id 学生编号, a.s_name 学生姓名, max(case c.c_name when '语文' then b.s_score else null end) 语文, max(case c.c_name when '数学' then b.s_score else null end) 数学, max(case c.c_name when '英语' then b.s_score else null end) 英语, cast(avg(b.s_score) as decimal(18, 2)) 平均分from Student a left join Score b on a.s_id = b.s_id left join Course c on b.c_id = c.c_idgroup by a.s_id, a.s_nameorder by 平均分 desc;
–17.2 SQL 2000 动态
declare @sql nvarchar(4000)
set @sql ='select a.s_id ' +'学生编号' + ' , a.s_name ' +'学生姓名'
select @sql = @sql +',max(case c.c_name when'''+c_name+''' then b.s_score else null end)'+c_name+' '
from (select distinct c_name from Course) as t
set @sql = @sql + ' , cast(avg(b.s_score) as decimal(18,2)) ' +'平均分' + ' from Student a left join Score b on a.s_id = b.s_id left join Course c on b.c_id = c.c_id
group by a.s_id , a.s_name order by ' +'平均分' + ' desc'
exec(@sql)
18查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率,及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
/*查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率–及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 需要的表：1.成绩信息 2.课程信息 需要用到的函数： 1.max、min、avg、sum 2.cast 3.floor 去除小数 4.concat 百分号拼接*/select c.c_id, c.c_name, max(s.s_score) 最高分, min(s.s_score) 最低分, avg(s.s_score) 平均分, concat(FLOOR(sum(s.s_score >= 60) / count(1) * 100), "%") 及格率, concat(FLOOR(sum(s.s_score >= 70 and s_score<=80) / count(1) * 100), "%") 中等率, concat(FLOOR(sum(s.s_score >= 80 and s_score<=90) / count(1) * 100), "%") 优良率, concat(FLOOR(sum(s.s_score >= 90) / count(1) * 100), "%") 优秀率from course c left join score s on c.c_id = s.c_idgroup by c.c_id, c.c_name;select c.c_name, c.c_id, max(s.s_score) 最高分, min(s.s_score) 最低分, avg(s.s_score) 平均分, cast((sum(s.s_score >= 60) / count(1)) as decimal(18, 2)) * 100 及格率, cast((sum(s_score >= 70 and s.s_score <= 80) / count(1)) as decimal(18, 2)) * 100 中等率, cast((sum(s_score >= 80 and s.s_score <= 90) / count(1)) as decimal(18, 2)) * 100 优良率from course c left join score s on c.c_id = s.c_idgroup by c.c_name, c.c_id;
–19、（视频）按各科成绩进行排序，并显示排名
–19.1 sql 2000用子查询完成
–s_score重复时保留名次空缺
select t.* , px = (select count(1) from Score where c_id = t.c_id and s_score > t.s_score) + 1 from sc t order by t.c_id , px
–s_score重复时合并名次
select t.* , px = (select count(distinct s_score) from Score where c_id = t.c_id and s_score >= t.s_score) from sc t order by t.c_id , px
–19.2 sql 2005用rank,DENSE_RANK完成
–s_score重复时保留名次空缺(rank完成)
select t.* , px = rank() over(partition by c_id order by s_score desc) from sc t order by t.c_id , px
–s_score重复时合并名次(DENSE_RANK完成)
select t.* , px = DENSE_RANK() over(partition by c_id order by s_score desc) from sc t order by t.c_id , px
–20、（半视频）查询学生的总成绩并进行排名
–20.1 查询学生的总成绩
select s.s_id, s.s_name, sum(s2.s_score) 总分from student s left join score s2 on s.s_id = s2.s_idgroup by s.s_id, s.s_nameorder by 总分 desc;
–20.2(视频) 查询学生的总成绩并进行排名，sql 2000用子查询完成，分总分重复时保留名次空缺和不保留名次空缺两种。
select t1.* , px = (select count(1) from
(
select m.s_id 学生编号 ,
m.s_name 学生姓名 ,
isnull(sum(s_score),0) 总成绩
from Student m left join Score n on m.s_id = n.s_id
group by m.s_id , m.s_name
) t2 where 总成绩 > t1.总成绩) + 1 from
(
select m.s_id 学生编号 ,
m.s_name 学生姓名 ,
isnull(sum(s_score),0) 总成绩
from Student m left join Score n on m.s_id = n.s_id
group by m.s_id , m.s_name
) t1
order by px
select t1.* , px = (select count(distinct 总成绩) from
(
select m.s_id 学生编号 ,
m.s_name 学生姓名 ,
isnull(sum(s_score),0) 总成绩
from Student m left join Score n on m.s_id = n.s_id
group by m.s_id , m.s_name
) t2 where 总成绩 >= t1.总成绩) from
(
select m.s_id 学生编号 ,
m.s_name 学生姓名 ,
isnull(sum(s_score),0) 总成绩
from Student m left join Score n on m.s_id = n.s_id
group by m.s_id , m.s_name
) t1
order by px
–20.3 查询学生的总成绩并进行排名，sql 2005用rank,DENSE_RANK完成，分总分重复时保留名次空缺和不保留名次空缺两种。
select t.* , px = rank() over(order by 总成绩 desc) from
(
select m.s_id 学生编号 ,
m.s_name 学生姓名 ,
isnull(sum(s_score),0) 总成绩
from Student m left join Score n on m.s_id = n.s_id
group by m.s_id , m.s_name
) t
order by px
select t.* , px = DENSE_RANK() over(order by 总成绩 desc) from
(
select m.s_id 学生编号 ,
m.s_name 学生姓名 ,
isnull(sum(s_score),0) 总成绩
from Student m left join Score n on m.s_id = n.s_id
group by m.s_id , m.s_name
) t
order by px
–21、查询不同老师所教不同课程平均分从高到低显示
# 需要的表# 1.老师信息# 2.课程信息# 3.分数信息select t.t_name,c.c_name,avg(s.s_score) avgfrom teacher tleft join course c on t.t_id = c.t_idleft join score s on c.c_id = s.c_idgroup by t.t_name, c.c_nameorder by avg desc ;
–22、（视频）查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
–22.1 sql 2000用子查询完成
–s_score重复时保留名次空缺
select * from (select t.* , px = (select count(1) from Score where c_id = t.c_id and s_score > t.s_score) + 1 from sc t) m where px between 2 and 3 order by m.c_id , m.px
–s_score重复时合并名次
select * from (select t.* , px = (select count(distinct s_score) from Score where c_id = t.c_id and s_score >= t.s_score) from sc t) m where px between 2 and 3 order by m.c_id , m.px
–22.2 sql 2005用rank,DENSE_RANK完成
–s_score重复时保留名次空缺(rank完成)
select * from (select t.* , px = rank() over(partition by c_id order by s_score desc) from sc t) m where px between 2 and 3 order by m.c_id , m.px
–s_score重复时合并名次(DENSE_RANK完成)
select * from (select t.* , px = DENSE_RANK() over(partition by c_id order by s_score desc) from sc t) m where px between 2 and 3 order by m.c_id , m.px
–23、（视频）统计各科成绩各分数段人数：课程编号,课程名称, 100-85 , 85-70 , 70-60 , 0-60 及所占百分比
–23.1 统计各科成绩各分数段人数：课程编号,课程名称, 100-85 , 85-70 , 70-60 , 0-60
–横向显示
select Course.c_id 课程编号 , c_name as 课程名称 ,
sum(case when s_score >= 85 then 1 else 0 end) 85-100 ,
sum(case when s_score >= 70 and s_score < 85 then 1 else 0 end) 70-85 ,
sum(case when s_score >= 60 and s_score < 70 then 1 else 0 end) 60-70 ,
sum(case when s_score < 60 then 1 else 0 end) 0-60
from sc , Course
where Score.c_id = Course.c_id
group by Course.c_id , Course.c_name
order by Course.c_id
–纵向显示1(显示存在的分数段)
select m.c_id 课程编号 , m.c_name 课程名称 , 分数段 = (
case when n.s_score >= 85 then'85-100'
when n.s_score >= 70 and n.s_score < 85 then'70-85'
when n.s_score >= 60 and n.s_score < 70 then'60-70'
else'0-60'
end) ,
count(1) 数量
from Course m , sc n
where m.c_id = n.c_id
group by m.c_id , m.c_name , (
case when n.s_score >= 85 then'85-100'
when n.s_score >= 70 and n.s_score < 85 then'70-85'
when n.s_score >= 60 and n.s_score < 70 then'60-70'
else'0-60'
end)
order by m.c_id , m.c_name , 分数段
–纵向显示2(显示存在的分数段，不存在的分数段用0显示)
select m.c_id 课程编号 , m.c_name 课程名称 , 分数段 = (
case when n.s_score >= 85 then'85-100'
when n.s_score >= 70 and n.s_score < 85 then'70-85'
when n.s_score >= 60 and n.s_score < 70 then'60-70'
else'0-60'
end) ,
count(1) 数量
from Course m , sc n
where m.c_id = n.c_id
group by all m.c_id , m.c_name , (
case when n.s_score >= 85 then'85-100'
when n.s_score >= 70 and n.s_score < 85 then'70-85'
when n.s_score >= 60 and n.s_score < 70 then'60-70'
else'0-60'
end)
order by m.c_id , m.c_name , 分数段
–23.2 统计各科成绩各分数段人数：课程编号,课程名称, 100-85 , 85-70 , 70-60 , <60 及所占百分比
–横向显示
select m.c_id 课程编号, m.c_name 课程名称,
(select count(1) from Score where c_id = m.c_id and s_score < 60) 0-60 ,
cast((select count(1) from Score where c_id = m.c_id and s_score < 60)100.0 / (select count(1) from Score where c_id = m.c_id) as decimal(18,2)) 百分比 ,
(select count(1) from Score where c_id = m.c_id and s_score >= 60 and s_score < 70) 60-70 ,
cast((select count(1) from Score where c_id = m.c_id and s_score >= 60 and s_score < 70)100.0 / (select count(1) from Score where c_id = m.c_id) as decimal(18,2)) 百分比 ,
(select count(1) from Score where c_id = m.c_id and s_score >= 70 and s_score < 85) 70-85 ,
cast((select count(1) from Score where c_id = m.c_id and s_score >= 70 and s_score < 85)100.0 / (select count(1) from Score where c_id = m.c_id) as decimal(18,2)) 百分比 ,
(select count(1) from Score where c_id = m.c_id and s_score >= 85) 85-100 ,
cast((select count(1) from Score where c_id = m.c_id and s_score >= 85)100.0 / (select count(1) from Score where c_id = m.c_id) as decimal(18,2)) 百分比
from Course m
order by m.c_id
–纵向显示1(显示存在的分数段)
select m.c_id 课程编号 , m.c_name 课程名称 , 分数段 = (
case when n.s_score >= 85 then'85-100'
when n.s_score >= 70 and n.s_score < 85 then'70-85'
when n.s_score >= 60 and n.s_score < 70 then'60-70'
else'0-60'
end) ,
count(1) 数量 ,
cast(count(1) * 100.0 / (select count(1) from sc where c_id = m.c_id) as decimal(18,2)) 百分比
from Course m , sc n
where m.c_id = n.c_id
group by m.c_id , m.c_name , (
case when n.s_score >= 85 then'85-100'
when n.s_score >= 70 and n.s_score < 85 then'70-85'
when n.s_score >= 60 and n.s_score < 70 then'60-70'
else'0-60'
end)
order by m.c_id , m.c_name , 分数段
–纵向显示2(显示存在的分数段，不存在的分数段用0显示)
select m.c_id 课程编号 , m.c_name 课程名称 , 分数段 = (
case when n.s_score >= 85 then'85-100'
when n.s_score >= 70 and n.s_score < 85 then'70-85'
when n.s_score >= 60 and n.s_score < 70 then'60-70'
else'0-60'
end) ,
count(1) 数量 ,
cast(count(1) * 100.0 / (select count(1) from sc where c_id = m.c_id) as decimal(18,2)) 百分比
from Course m , sc n
where m.c_id = n.c_id
group by all m.c_id , m.c_name , (
case when n.s_score >= 85 then'85-100'
when n.s_score >= 70 and n.s_score < 85 then'70-85'
when n.s_score >= 60 and n.s_score < 70 then'60-70'
else'0-60'
end)
order by m.c_id , m.c_name , 分数段
–24、（视频）查询学生平均成绩及其名次
–24.1 查询学生的平均成绩并进行排名，sql 2000用子查询完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。
select t1.* , px = (select count(1) from
(
select m.s_id 学生编号 ,
m.s_name 学生姓名 ,
isnull(cast(avg(s_score) as decimal(18,2)),0) 平均成绩
from Student m left join Score n on m.s_id = n.s_id
group by m.s_id , m.s_name
) t2 where 平均成绩 > t1.平均成绩) + 1 from
(
select m.s_id 学生编号 ,
m.s_name 学生姓名 ,
isnull(cast(avg(s_score) as decimal(18,2)),0) 平均成绩
from Student m left join Score n on m.s_id = n.s_id
group by m.s_id , m.s_name
) t1
order by px
select t1.* , px = (select count(distinct 平均成绩) from
(
select m.s_id 学生编号 ,
m.s_name 学生姓名 ,
isnull(cast(avg(s_score) as decimal(18,2)),0) 平均成绩
from Student m left join Score n on m.s_id = n.s_id
group by m.s_id , m.s_name
) t2 where 平均成绩 >= t1.平均成绩) from
(
select m.s_id 学生编号 ,
m.s_name 学生姓名 ,
isnull(cast(avg(s_score) as decimal(18,2)),0) 平均成绩
from Student m left join Score n on m.s_id = n.s_id
group by m.s_id , m.s_name
) t1
order by px
–24.2 查询学生的平均成绩并进行排名，sql 2005用rank,DENSE_RANK完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。
select t.* , px = rank() over(order by 平均成绩 desc) from
(
select m.s_id 学生编号 ,
m.s_name 学生姓名 ,
isnull(cast(avg(s_score) as decimal(18,2)),0) 平均成绩
from Student m left join Score n on m.s_id = n.s_id
group by m.s_id , m.s_name
) t
order by px
select t.* , px = DENSE_RANK() over(order by 平均成绩 desc) from
(
select m.s_id 学生编号 ,
m.s_name 学生姓名 ,
isnull(cast(avg(s_score) as decimal(18,2)),0) 平均成绩
from Student m left join Score n on m.s_id = n.s_id
group by m.s_id , m.s_name
) t
order by px
–25、(视频)查询各科成绩前三名的记录
–25.1 分数重复时保留名次空缺
select m.* , n.c_id , n.s_score from Student m, Score n where m.s_id = n.s_id and n.s_score in
(select top 3 s_score from sc where c_id = n.c_id order by s_score desc) order by n.c_id , n.s_score desc
–25.2 分数重复时不保留名次空缺，合并名次
–sql 2000用子查询实现
select * from (select t.* , px = (select count(distinct s_score) from Score where c_id = t.c_id and s_score >= t.s_score) from sc t) m where px between 1 and 3 order by m.c_id , m.px
–sql 2005用DENSE_RANK实现
select * from (select t.* , px = DENSE_RANK() over(partition by c_id order by s_score desc) from sc t) m where px between 1 and 3 order by m.c_id , m.px
查询每门课程被选修的学生数
select s.c_id, count(s.s_id) 学生数from score sgroup by s.c_id;
查询出只有两门课程的全部学生的学号和姓名
select s2.s_id 学号,s.s_name 姓名from score s2left join student s on s2.s_id = s.s_idgroup by s2.s_id, s.s_namehaving COUNT(c_id) = 2;
查询男生、女生人数
# 第一种方法比较容易理解就不多研究了select s_sex 性别,count(s_sex) 人数from studentwhere s_sex != 'null'group by s_sex;# 这里使用了case函数select sum(case when s_sex = '男' then 1 else 0 end) '男生人数', sum(case when s_sex = '女' then 1 else 0 end) ’女生人数‘from student;# 与case函数的方式相似，但是比较精简select sum(s.s_sex = '男') '男生人数',sum(s_sex = '女') ’女生人数‘from student s;
查询名字中含有"风"字的学生信息
select *from student swhere s_name like '%风%';
查询同名同姓学生名单，并统计同名人数
select s_name,count(1) 姓名持有人数from studentgroup by s_namehaving count(1) > 1;
查询1990年出生的学生名单(注：Student表中s_birth列的类型是datetime)
select *from studentwhere year(s_brith) = 1990;
select * from Student where year(s_birth) = 1990
select * from Student where datediff(yy,s_birth,'1990-01-01') = 0
select * from Student where datepart(yy,s_birth) = 1990
select * from Student where convert(varchar(4),s_birth,120) ='1990'
查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select s.c_id,c.c_name,cast(avg(s.s_score) as decimal (18,2)) as avgfrom score sjoin course c on s.c_id = c.c_idgroup by s.c_id,c.c_nameorder by avg desc;
查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select s.s_id, s.s_name, cast(avg(s2.s_score) as decimal (18,2)) avg_s_scorefrom student s left join score s2 on s.s_id = s2.s_idgroup by s.s_id, s.s_namehaving cast(avg(s2.s_score) as decimal (18,2)) >= 85order by s.s_id;
select a.s_id , a.s_name , cast(avg(b.s_score) as decimal(18,2)) avg_s_score
from Student a , sc b
where a.s_id = b.s_id
group by a.s_id , a.s_name
having cast(avg(b.s_score) as decimal(18,2)) >= 85
order by a.s_id
查询课程名称为"数学"，且分数低于60的学生姓名和分数
select c.c_name, s.s_id, s2.s_namefrom course c left join score s on c.c_id = s.c_id left join student s2 on s.s_id = s2.s_idwhere c.c_name = '数学' and s.s_score < 60; select s_name, s_scorefrom Student, Score, Coursewhere Score.s_id = Student.s_id and Score.c_id = Course.c_id and Course.c_name = N'数学' and s_score < 60;
查询所有学生的课程及分数情况
select s.s_name,c.c_name,s_scorefrom student sleft join score s2 on s.s_id = s2.s_idleft join course c on s2.c_id = c.c_idorder by s.s_id,s2.c_id;
查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select s.*,c.c_name,s2.s_scorefrom student s left join score s2 on s.s_id = s2.s_id left join course c on s2.c_id = c.c_idwhere s2.s_score > 70;
查询不及格的课程
select s.s_id, s.s_name, c.c_name, s2.s_scorefrom student s left join score s2 on s.s_id = s2.s_id left join course c on s2.c_id = c.c_idwhere s2.s_score < 60order by s.s_id,s2.s_score;
查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select s.s_id, s.s_name,s2.s_scorefrom student s left join score s2 on s.s_id = s2.s_idwhere s2.c_id = '01'and s2.s_score > 80;//没有80分以上的
求每门课程的学生人数
select s.c_id, c.c_name, count(*) 学生人数from score s left join course c on s.c_id = c.c_idgroup by s.c_id, c.c_nameorder by s.c_id,c.c_name;select Course.c_id, Course.c_name, count(*) 学生人数from Course, Scorewhere Course.c_id = Score.c_idgroup by Course.c_id, Course.c_nameorder by Course.c_id, Course.c_name;
查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select s.*, c.c_name, s2.c_id, s2.s_scorefrom student s left join score s2 on s.s_id = s2.s_id left join course c on s2.c_id = c.c_id left join teacher t on c.t_id = t.t_idwhere t.t_name = '张三' and s2.s_score = (select max(s_score) from score s2 left join course c on s2.c_id = c.c_id left join teacher t on c.t_id = t.t_id where t.t_name = '张三');
（视频） 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
–方法1
# 1先找出全修课程的学生编号select s.s_idfrom score s left join course c on s.c_id = c.c_idgroup by s.s_idhaving count(1) = (select count(1) from course);# 根据学生编号查询学生信息select *from student swhere s_id in (select s.s_id from score s left join course c on s.c_id = c.c_id group by s.s_id having count(1) = (select count(1) from course));
–方法2
select m.* from Score m where exists (select 1 from (select c_id , s_score from Score group by c_id , s_score having count(1) > 1) n
where m.c_id= n.c_id and m.s_score = n.s_score) order by m.c_id , m.s_score , m.s_id
（视频）查询每门功成绩最好的前两名
select t.* from sc t where s_score in (select top 2 s_score from sc where c_id = T.c_id order by s_score desc) order by t.c_id , t.s_score desc
统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select s.c_id, c.c_name, count(1) 选修人数from score s left join course c on s.c_id = c.c_idgroup by s.c_id, c.c_namehaving count(*) > 5order by 选修人数 desc ,s.c_id;select s.c_id,c.c_name, count(1) 选修人数from score s, course cwhere s.c_id = c.c_idgroup by s.c_id,c.c_nameorder by 选修人数 desc ,s.c_id;
检索至少选修两门课程的学生学号
select s.s_id, s1.s_namefrom score s inner join student s1 on s.s_id = s1.s_idgroup by s.s_id, s1.s_namehaving count(s.s_id) > 1;select s.s_id, s1.s_namefrom score s, student s1where s.s_id = s1.s_idgroup by s.s_id, s1.s_name;
（视频）查询选修了全部课程的学生信息
–方法1 根据数量来完成
select student.* from student where s_id in
(select s_id from sc group by s_id having count(1) = (select count(1) from course))
–方法2 使用双重否定来完成
select t.* from student t where t.s_id not in
(
select distinct m.s_id from
(
select s_id , c_id from student , course
) m where not exists (select 1 from sc n where n.s_id = m.s_id and n.c_id = m.c_id)
)
–方法3 使用双重否定来完成
select t.* from student t where not exists(select 1 from
(
select distinct m.s_id from
(
select s_id , c_id from student , course
) m where not exists (select 1 from sc n where n.s_id = m.s_id and n.c_id = m.c_id)
) k where k.s_id = t.s_id
)
–46、重学查询各学生的年龄
–46.1 只按照年份来算
select * , datediff(yy , s_birth , getdate()) 年龄 from student
–46.2 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select * , case when right(convert(varchar(10),getdate(),120),5) < right(convert(varchar(10),s_birth,120),5) then datediff(yy , s_birth , getdate()) - 1 else datediff(yy , s_birth , getdate()) end 年龄 from student
–47、查询本周过生日的学生
select * from student where datediff(week,datename(yy,getdate()) + right(convert(varchar(10),s_birth,120),6),getdate()) = 0
–48、查询下周过生日的学生
select * from student where datediff(week,datename(yy,getdate()) + right(convert(varchar(10),s_birth,120),6),getdate()) = -1
–49、查询本月过生日的学生
select * from student where datediff(mm,datename(yy,getdate()) + right(convert(varchar(10),s_birth,120),6),getdate()) = 0
–50、查询下月过生日的学生
select * from student where datediff(mm,datename(yy,getdate()) + right(convert(varchar(10),s_birth,120),6),getdate()) = -1
疑惑测试
where和having的区别
- where和having都可以使用的场景
select s_idfrom studentwhere s_id > 5;select s_idfrom studenthaving s_id > 5;/*解释：上面的having可以用的前提是我已经筛选出了s_id字段，在这种情况下和where的效果是等效的，但是如果我没有select s_id 就会报错！！因为having是从前筛选的字段再筛选，而where是从数据表中的字段直接进行的筛选的。 */
- 只可以用where，不可以用having的情况
select s_namefrom studentwhere s_id > 5;select s_namefrom studenthaving s_id > 5; # [42S22][1054] Unknown column 's_id' in 'having clause'/*解释：因为前面并没有筛选出s_id字段 */
- 只可以用having，不可以用where情况
# 全班学生的语数英成绩平均分，输出65分以上的科目select avg(s.s_score) sc, c.c_namefrom score s left join course c on s.c_id = c.c_idgroup by c.c_namehaving sc > 65;select avg(s.s_score) sc, c.c_namefrom score s left join course c on s.c_id = c.c_idwhere sc > 65group by c.c_name;/*注意:where 后面要跟的是数据表里的字段，如果我把sc换成AVG(s.s_score)也是错误的！因为表里没有该字段。而having只是根据前面查询出来的是什么就可以后面接什么。*/
left函数
# 查询"李"姓老师的数量select count(t_name) 李姓老师的数量from Teacherwhere left(t_name, 1) = '李';/*sql的left()函数表示的是从字符表达式最左边一个字符开始返回指定数目的字符.若 b 的值大于 a 的长度,则返回字符表达式的全部字符a.如果 b 为负值或 0,则返回空字符串*/select left('12345678aaa',9); #12345678aselect left('李四 - 输出第一位数',1); # 李
exists和not exists和select 1的理解
原理解释：
exists（sql返回结果集为真）
select *from student awhere exists(select 1 from score s where a.s_id = a.s_name);
not exists（sql不返回结果集为真或返回结果集为假）
select *from student awhere not exists(select 1 from score s where a.s_id = a.s_name);
group by到底是干嘛用的
count(1) 和 count(字段)区别在哪
case函数怎么用
简单CASE WHEN函数：CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' ENDCASE SCORE WHEN 'B' THEN '良' ELSE '不及格' ENDCASE SCORE WHEN 'C' THEN '中' ELSE '不及格' END等同于，使用CASE WHEN条件表达式函数实现：CASE WHEN SCORE = 'A' THEN '优' WHEN SCORE = 'B' THEN '良' WHEN SCORE = 'C' THEN '中' ELSE '不及格' END
案例1:给所有同学的分数进行分级，以60分为界限
select s.s_id, s2.s_name, c.c_name, s.s_score, ( case s_score when s.s_score < 60 then '不及格' else '及格' end ) as '级别'from score s left join student s2 on s.s_id = s2.s_id left join course c on s.c_id = c.c_id;//不能识别出null
案例2:给所有同学的分数进行分级，没10分为一个等级，null为异常
select s.s_id, s2.s_name, s.s_score, c.c_name, (case when s_score < 60 then '不及格' when s_score >= 60 and s_score < 80 then '及格' when s_score >= 80 and s_score < 90 then '优秀' when s_score >= 90 and s_score <= 100 then '卓越' else '异常' end) as '级别'from score s left join student s2 on s.s_id = s2.s_id left join course c on s.c_id = c.c_id;//null为异常
牛客
考察插入数据
drop table if exists actor;CREATE TABLE actor ( actor_id smallint(5) NOT NULL PRIMARY KEY, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, last_update DATETIME NOT NULL)
请你对于表actor批量插入如下数据(不能有2条insert语句哦!)
| actor_id | first_name | last_name | last_update |
|---|---|---|---|
| 1 | PENELOPE | GUINESS | 2006-02-15 12:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 12:34:33 |
insert into actor(actor_id, first_name, last_name, last_update)VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'), (2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33');
条件插入
drop table if exists actor;CREATE TABLE actor ( actor_id smallint(5) NOT NULL PRIMARY KEY, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, last_update DATETIME NOT NULL);insert into actor values ('3', 'WD', 'GUINESS', '2006-02-15 12:34:33');
对于表actor插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)
| actor_id | first_name | last_name | last_update |
|---|---|---|---|
| '3' | 'ED' | 'CHASE' | '2006-02-15 12:34:33' |
insert ignore into actorvalues ("3", "ED", "CHASE", "2006-02-15 12:34:33");
创建表格
对于如下表actor，其对应的数据为:
| actor_id | first_name | last_name | last_update |
|---|---|---|---|
| 1 | PENELOPE | GUINESS | 2006-02-15 12:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 12:34:33 |
请你创建一个actor_name表，并且将actor表中的所有first_name以及last_name导入该表.
actor_name表结构如下：
| 列表 | 类型 | 是否为NULL | 含义 |
|---|---|---|---|
| first_name | varchar(45) | not null | 名字 |
| last_name | varchar(45) | not null | 姓氏 |
本题目考察MYSQL创建数据表的三种方法：1.常规创建create table if not exists 目标表2.复制表格create 目标表 like 来源表3.将table1的部分拿来创建table2create table if not exists actor_name(first_name varchar(45) not null,last_name varchar(45) not null)select first_name,last_namefrom actor
创建唯一索引
针对如下表actor结构创建索引：
(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作，
mysql支持ALTER TABLE创建索引)
CREATE TABLE actor ( actor_id smallint(5) NOT NULL PRIMARY KEY, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, last_update datetime NOT NULL);
对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname
MySQL中四种方式给字段添加索引关于MySQL中给字段创建索引的四种方式：添加主键12ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);// 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。添加唯一索引12ALTER TABLE tbl_name ADD UNIQUE index_name (col_list);// 这条语句创建索引的值必须是唯一的。添加普通索引12ALTER TABLE tbl_name ADD INDEX index_name (col_list);// 添加普通索引，索引值可出现多次。添加全文索引12ALTER TABLE tbl_name ADD FULLTEXT index_name (col_list);// 该语句指定了索引为 FULLTEXT ，用于全文索引。PS: 附赠删除索引的语法：1234DROP INDEX index_name ON tbl_name;// 或者ALTER TABLE tbl_name DROP INDEX index_name；ALTER TABLE tbl_name DROP PRIMARY KEY;
在last_update后面新增加一列名字为create_date#
描述
存在actor表，包含如下列信息：
CREATE TABLE actor ( actor_id smallint(5) NOT NULL PRIMARY KEY, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, last_update datetime NOT NULL);
现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'2020-10-01 00:00:00'
示例1
输入：
drop table if exists actor;CREATE TABLE actor ( actor_id smallint(5) NOT NULL PRIMARY KEY, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, last_update datetime NOT NULL);
复制
输出：
1
考点是 ALTER TABLE 表名 ADD COLUMN 和 DEFAULT '值'， 题解如下：ALTER TABLE actor ADD (create_date datetime NOT NULL DEFAULT '2020-10-01 00:00:00');
构造一个触发器audit_log
描述
构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。
CREATE TABLE employees_test(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);CREATE TABLE audit(EMP_no INT NOT NULL,NAME TEXT NOT NULL);
后台会往employees_test插入一条数据:
INSERT INTO employees_test (ID,NAME,AGE,ADDRESS,SALARY)VALUES (1, 'Paul', 32, 'California', 20000.00 );
然后从audit里面使用查询语句:
select * from audit;
示例1
输入：
drop table if exists employees_test;drop table if exists audit;CREATE TABLE employees_test( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);CREATE TABLE audit( EMP_no INT NOT NULL, NAME TEXT NOT NULL);
复制
输出：
1|Paul
答案
create trigger audit_logafter insert on employees_testfor each rowbegin insert into audit values(new.id,new.name);end
在MySQL中，创建触发器语法如下：
CREATE TRIGGER trigger_name
trigger_time trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt
其中：
- trigger_name：标识触发器名称，用户自行指定；
- trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
- trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
- tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
- trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句，每条语句结束要分号结尾。
【NEW 与 OLD 详解】
MySQL 中定义了 NEW 和 OLD，用来表示
触发器的所在表中，触发了触发器的那一行数据。
具体地：
- 在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
- 在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
- 在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
使用方法： NEW.columnName （columnName 为相应数据表某一列名）
参考
https://blog.csdn.net/weixin_41177699/article/details/80302987
