sql语句练习

SQL例题：学生表：Student(sid,sname,sbirth,ssex) –学生编号,学生姓名, 出生年月,学生性别建表：create table Student(
   sid varchar(20),
   sname varchar(20) not null default ‘’,
   sbirth varchar(20) not null default ‘’,
   ssex varchar(10) not null default’’,
    primary key(sid)
);插入数据：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(cid,cname,tid) – –课程编号, 课程名称, 教师编号建表：create table Course(
cid varchar(20),
cname varchar(20) not null default ‘’,
tid varchar(20)not null,
primary key(cid)
);插入数据：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(20),
tname varchar(20) not null default’’,
primary key(tid)
);插入数据：insert into Teacher values(‘01’ , ‘张三’);
insert into Teacher values(‘02’ , ‘李四’);
insert into Teacher values(‘03’ , ‘王五’);成绩表 Score(sid,cid,sscore) –学生编号,课程编号,分数建表：create table Score(
sid varchar(20),
cid varchar(20),
sscore int(3),
primary key(sid,cid)
);插入数据：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);
​
为23题添加数据：
insert into Score values(‘08’ , ‘02’ , 90);练习题目：以cid=01 为主1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 select *from Student right join
(select t1.sid,score1,score2
from
(select sid,cid ,sscore score1 from Score where cid=01)t1,
(select sid,cid ,sscore score2 from Score where cid=02)t2
where t1.sid=t2.sid and t1.score1>t2.score2)r
on Student.sid=r.sid;2、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息方法①select from Student right join
(select t1.sid 
from
(select sid,cid from Score where cid=01)t1,
(select sid,cid from Score where cid=02)t2
where t1.sid=t2.sid)r
on Student.sid=r.sid;
​方法②select s.
from Student s,Score sc1,Score sc2
where s.sid = sc1.sid  
and s.sid = sc2.sid 
and sc1.cid=01 
and sc2.cid=02;3、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息select s.from Student s
where 
s.sid in
  (select sid from Score where cid=01)
and s.sid not in
  (select sid from Score where cid=02);    4、查询没有选所有课程的同学的信息select from Student s where s.sid not in(
select sc.sid from Score sc
group by sc.sid
having count(sc.cid)=
(select count(cid) from Course));以sid=01为主：5、查询至少有一门课与学号为"01"的同学所选课程相同的同学的信息 select from Student s where s.sid in(
select sc.sid from Score sc where sc.cid in(
select sc.cid from Score sc where sc.sid=01));6、查询和"01"号的同学学习的课程完全相同的其他同学的信息 select from Student s where s.sid in(
select distinct sid from Score sc where sid!=01 and cid in(
select cid from Score where sid=01)
group by sid
having count(sid)=(select count(cid) from Score where sid=01));7、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩方法①select s.sid,s.sname,score from Student s,(
select sid,avg(sscore)score from Score
group by sid
having avg(sscore)>=60)r
where s.sid=r.sid;方法②select s.sid,s.sname,AVG(sc.sscore)avgscore
from Student s
join Score sc on s.sid = sc.sid
GROUP BY s.sid,s.sname 
HAVING AVG(sc.sscore)>=60;8、查询在Score表中存在成绩的 学生的信息方法①select from Student s where s.sid in(
select distinct sid from Score);方法②select distinct s.
from Student s ,Score sc
where s.sid=sc.sid;9、查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和select s.sid,s.sname ,cidno,sumscore from Student s,
(select sid,count(cid)cidno,sum(sscore)sumscore from Score
group by sid)r
where s.sid=r.sid;10、查询「李」姓老师的数量select count() from Teacher where tname like ‘李%’;11、查询学过「张三」老师授课的同学的信息方法①select from Student where sid in(
select sid from Score where cid in(  
select cid from Course where tid in(
  select tid from Teacher where tname=“张三”)));
​方法②select s.from Student s,Teacher t,Course c,Score sc
where s.sid=sc.sid
and c.cid=sc.cid
and c.tid=t.tid
and tname=“张三”;12、查询没学过"张三"老师讲授的任一门课程的学生姓名方法①select from Student where sid not in(
select sid from Score where cid in(
select cid from Course where tid in(
  select tid from Teacher where tname=“张三”)));方法②select from Student s where s.sid not in(
select sc.sid from Teacher t,Course c,Score sc
where c.cid=sc.cid
and c.tid=t.tid
and tname=“张三”);13、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩select s.sid ,s.sname,t2.avgscore from Student s,
(select t1.sid,avg(sscore)avgscore from
      (select from Score where sscore<60)t1
     group by t1.sid
     having count(t1.sid)>=2)t2
 where s.sid=t2.sid;14、查询01课程分数小于60，按分数降序排序的学生信息select s.,sc.sscore from Student s,Score sc
where s.sid=sc.sid
and sc.sscore<60
and cid=01
order by sc.sscore desc;15、 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩select *  from Score sc
left join (
   select sid,avg(sscore)avgcore from Score sc
   group by sid
  )r
on sc.sid = r.sid
order by avgcore desc;17、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60中等为：70-80优良为：80-90优秀为：>=90要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列select c.cid,c.cname,t1.maxscore,t1.minscore,t1.avgscore from Course c,
(select cid,max(sscore)maxscore,min(sscore)minscore,avg(sscore)avgscore from Score
group by cid)t1
where t1.cid=c.cid;完整版：select sc.cid,c.cname,MAX(sscore)max,MIN(sscore)min,AVG(sscore)avg,
ROUND(100(SUM(case when sc.sscore>=60 then 1 else 0 end)/SUM(case when sc.sscore then 1 else 0 end)),2) as 及格率,
ROUND(100(SUM(case when sc.sscore>=70 and sc.sscore<=80 then 1 else 0 end)/SUM(case when sc.sscore then 1 else 0 end)),2) as 中等率,
ROUND(100(SUM(case when sc.sscore>=80 and sc.sscore<=90 then 1 else 0 end)/SUM(case when sc.sscore then 1 else 0 end)),2) as 优良率,
ROUND(100(SUM(case when sc.sscore>=90 then 1 else 0 end)/SUM(case when sc.sscore then 1 else 0 end)),2) as 优秀率
from Score sc left join Course c on sc.cid = c.cid GROUP BY sc.cid,c.cname;
​18按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺select sc1.cid, sc1.sid, sc1.sscore, count(sc2.sscore)+1 rank
from Score sc1
left join Score sc2
on sc1.sscore<sc2.sscore and sc1.cid = sc2.cid
group by sc1.cid, sc1.sid,sc1.sscore
order by sc1.cid, rank ASC;19、查询学生的总成绩，并进行排名，总分重复时不保留名次空缺select sid ,sum(sscore) from Score
group by sid
order by sum(sscore) desc;
​
​
​
select a.sid,
@i:=@i+1 as i,
@k:=(case when @score=a.sumscore then @k else @i end) as rank,
@score:=a.sumscore as Score
from (select sid,SUM(sscore) as sumscore from score GROUP BY sid ORDER BY sumscore DESC)a,
(select @k:=0,@i:=0,@score:=0)s；20、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩select sc1.cid, sc1.sid, sc1.sscore 
from Score sc1
inner join 
Score sc2
on sc1.sid = sc2.sid
and sc1.cid != sc2.cid
and sc1.sscore = sc2.sscore
group by cid, sid;
21、查询每门课程被选修的学生数select cid,count(sid) from Score sc
group by cid;22、查询每门课成绩最好的前两名方法①：Score和 Score连接，没看懂select sc1.sid,sc1.cid,sc1.sscore from Score sc1 left join Score sc2
on sc1.cid=sc2.cid
and sc1.sscore<sc2.sscore
group by sc1.cid,sc1.sid
having count(sc2.cid)<2
order by sc1.cid;方法②：自己做的，只实现单科排名前两名select cid,sid,sscore from Score where 
cid =01
order by sscore desc limit 2;23、成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩方法①select s.,t3.cid from Student s,
(select sc.sid,sc.cid,max(sc.sscore) maxscore from Score sc,
(select c.cid from Course c,
(select t.tid from Teacher t where t.tname=“张三”)t1
where t1.tid=c.tid)t2
where t2.cid=sc.cid)t3
where s.sid=t3.sid;方法②select s., sc.sscore, sc.cid from Student s, Teacher t, Course c,Score sc
where t.tid = c.tid
and sc.sid = s.sid
and sc.cid = c.cid
and t.tname = “张三”
having max(sc.sscore);方法③：与方法②基本相似，只是筛选条件时限制条件不同select s. ,sc.sscore, sc.cid from Student s, Teacher t, Course c,Score sc
where t.tid = c.tid
and sc.sid = s.sid
and sc.cid = c.cid
and t.tname = “张三”
order by sscore desc
limit 1;24、成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩（已添加重复数据）方法①：找到最高分，找到获得最高分的人select s., sc.sscore, sc.cid from Student s, Teacher t, Course c,Score sc
where t.tid = c.tid
and sc.sid = s.sid
and sc.cid = c.cid
and t.tname = “张三”
and sc.sscore = (
   select Max(sc.sscore)
   from Score sc,Student s, Teacher t, Course c
   where t.tid = c.tid
   and sc.sid = s.sid
   and sc.cid = c.cid
   and t.tname = “张三”
);25、查询男生、女生人数select ssex,count(sid)from Student
group by ssex;26、查询名字中含有「风」字的学生信息select from Student where sname like"%风%";27、查询同名学生名单，并统计同名人数select sname,count()from Student
group by sname
having count()>1;28、查询 1990 年出生的学生名单方法①select *from Student where sbirth like’1990%’;方法②select *
from Student
where year(sbirth)=1990;29、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列方法①select cid,AVG(sscore)avgscore from Score
GROUP BY cid
ORDER BY avgscore DESC,cid ASC;方法②select sc.cid, c.cname, avg(sc.sscore) avgscore from Score sc, Course c
where sc.cid = c.cid
group by sc.cid 
order by avgscore desc,cid asc;30、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩方法①select s.sid,s.sname,avgscore from Student s,
(select sid,avg(sscore)avgscore from Score
group by sid
having avgscore>=85)r
where s.sid=r.sid;方法②select s.sid,s.sname, avg(sc.sscore)avgscore from Student s,Score sc
where s.sid = sc.sid
group by sc.sid
having avgscore> 85;31、查询课程名称为「数学」，且分数低于 60 的学生姓名和分数方法①select s.sname,sc.sscore from Score sc
 join Student s on s.sid=sc.sid 
    where sc.cid=(
  select c.cid from Course c where c.cname =‘数学’)
  and sc.sscore<60;方法②select s.sname,sc.sscore from Score sc,Student s,Course c
where sc.sid=s.sid
and sc.cid=c.cid
and sscore<60
and c.cname=‘数学’;32、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数方法①select s.sname,c.cid,t1.sscore from Student s,Course c ,
  (select from Score sc where sc.sscore>70)t1
where c.cid=t1.cid
and s.sid=t1.sid;方法②select s.sname, c.cname,sc.sscore from Student s,Course c,Score sc
where sc.sscore>70
and s.sid = sc.sid
and sc.cid = c.cid;33、查询存在不及格的课程方法①select c.cid,c.cname,t1.sscore from Course c ,
(select sc.sid,sc.cid,sc.sscore from Score sc where sc.sscore<60)t1
where c.cid=t1.cid; 方法②select sc.sid,sc.cid,c.cname,sc.sscore from Score sc left join Course c on sc.cid = c.cid
where sc.sscore<60; 34、查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名方法①select s.sid,s.sname,sc.cid,sc.sscore 
from Student s,Score sc
where s.sid=sc.sid
and sc.cid=01
and sc.sscore>60;方法②select s.sid,s.sname,t1.sscore from Student s,
(select sc.sid,sc.sscore from Score sc
where sc.cid=01
and sc.sscore>60)t1
where s.sid=t1.sid;35、求每门课程的学生人数select cid,count(sid) from Score
group by cid;36、统计每门课程的学生选修人数（超过 5 人的课程才统计）select cid,count(sid)count from Score
group by cid
having count>5;37、查询至少选修两门课程的学生学号select sid,count(cid) from Score
group by sid
having count(sid)>=2;38、查询出只选修两门课程的学生学号和姓名select s.sid,s.sname from Student s,
(select sid,count(cid) from Score
group by sid
having count(sid)=2)t1
where s.sid=t1.sid;39查询选修了全部课程的学生信息方法①select s. from Student s,
(select sc.sid,count(sc.cid) from Score sc
     GROUP BY sc.sid 
      HAVING count(sc.cid)=(select count(c.cid) from Course c))t1
where s.sid=t1.sid;
                             方法②select s.*from Score sc ,Student s
 where sc.sid=s.sid
  GROUP BY sc.sid
   HAVING count(sc.cid) = (select DISTINCT count(cid) from Course)40、查询各学生的年龄，只按年份来算方法①select s.sid,s.sname,
timestampdiff(year,s.sbirth,curdate())age
from Student s;
​方法②select sbirth,(date_format(now(),’%Y’)-date_format(sbirth,’%Y’) -
(case when date_format(now(),’%m%d’)>date_format(sbirth,’%m%d’) then 0 else 1 end))age
from Student;41、查询本周过生日的学生select *
from Student s
where weekofyear(s.sbirth)=weekofyear(curdate());??方法②select * from Student where yearweek(sbirth)=yearweek(date_format(now(),’%Y%m%d’))
42、查询下周过生日的学生select *
from Student s
where weekofyear(s.sbirth)=weekofyear(curdate())+1;select * from Student where WEEK(DATE_FORMAT(NOW(),’%Y%m%d’))+1 =WEEK(sbirth)43、查询本月过生日的学生select *
from Student s
where month(s.sbirth)=moth(curdate());select * from Student where MONTH(DATE_FORMAT(NOW(),’%Y%m%d’)) =MONTH(sbirth);44、查询下月过生日的学生select *
from Student s
where month(s.sbirth)=month(curdate())+1;select * from Student s where MONTH(DATE_FORMAT(NOW(),’%Y%m%d’))+1 =MONTH(sbirth);