数据表介绍

--1.学生表 Student(SId,Sname,Sage,Ssex) --SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

--2.课程表 Course(CId,Cname,TId) --CId 课程编号,Cname 课程名称,TId 教师编号

--3.教师表 Teacher(TId,Tname) --TId 教师编号,Tname 教师姓名

--4.成绩表 SC(SId,CId,score) --SId 学生编号,CId 课程编号,score 分数

学生表 Student

create table Student(SId 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-12-20' , '男');

insert into student values('04' , '李云' , '1990-12-06' , '男');

insert into student values('05' , '周梅' , '1991-12-01' , '女');

insert into student values('06' , '吴兰' , '1992-01-01' , '女');

insert into student values('07' , '郑竹' , '1989-01-01' , '女');

insert into student values('09' , '张三' , '2017-12-20' , '女');

insert into student values('10' , '李四' , '2017-12-25' , '女');

insert into student values('11' , '李四' , '2012-06-06' , '女');

insert into student values('12' , '赵六' , '2013-06-13' , '女');

insert into student values('13' , '孙七' , '2014-06-01' , '女');

科目表 Course

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

教师表 Teacher

create table Teacher(TId varchar(10),Tname varchar(10));

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

成绩表 SC

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));

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);

1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数 select * from student right join (select t1.SId,score1,score2 from (select SId,score as score1 from sc where sc.CId='01') as t1,(select SId,score as score2 from sc where sc.CId='02') as t2 where t1.SId=t2.SId and t1.score1>t2.score2 ) as r on (student.SId=r.SId); 1.1.查询同时存在" 01 "课程和" 02 "课程的情况 select * from  (select * from sc where sc.CId='01') as t1,( select * from sc where sc.CId='02') as t2 where t1.Sid=t2.SId;

1.2.查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null ) select * from  (select * from sc where sc.CId = '01') as t1 left join  (select * from sc where sc.CId = '02') as t2 on t1.SId = t2.Id; d 1.3.查询不存在" 01 "课程但存在" 02 "课程的情况 select * from sc where sc.SId not in (select SId from sc where sc.SId='01') and sc.CId='02'; 2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩 select r.SId,sname,g from (select SID,avg(score) as g from sc group by SId having avg(score)>60)r left join (select student.SId,student.sname from student)a on r.SId=a.SId; 3.查询在sc表存在成绩的学生的信息 select * from student right join (select SId,score from sc) on (student.SId=sc.SId);  4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )  select * from (select SId,Sname from student)r right join (select SId,count(CId),sum(score) from sc group by SId)a on (r.SId=a.SId);     4.2.查询有成绩的学生的信息 select * from student right join  (select SId,score from sc )r on (student.SId=r.SId); 5.查询李姓老师的数量 select count(Tname) from Teacher where Tname like '李%'; 6.查询学过「张三」老师授课的同学的信息 select * from student (select CId from sc where CId='02'; 7.查询没有学全所有课程的同学的信息 select * from student where student.sid not in (select sc.sid from sc group by sc.sid having count(sc.cid)= (select count(cid) from course)); 8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息 select * from student where student.sid in (select sc.sid from sc where sc.cid in (select sc.cid from sc where sc.sid='01')); 10.查询没学过"张三"老师讲授的任一门课程的学生姓名 select sname from student where student.sid  not in (select sid from sc where cid='02' group by sid); 11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 select student.sid,student.sname,b.avg from student right join (select sid,avg(score) as avg from sc where sc.sid in (select sid from sc where sc.score1 ) group by sid )b on student.sid=b.sid; 12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息 select student.*,score from student right join (select sid,score from sc where cid='01' and score1; 24.查询 1990 年出生的学生名单 select sname from student where year(sage)=1990; 25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 select cid,avg(score)  from sc as a group by cid order by a desc,cid asc; 26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 select student.sid,student.sname,r.avg from student right join (select sid,avg(score) as avg from sc group by sid having avg(score)>85)r on student.sid=r.sid; 27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数 select student.sname,sc.score from student,sc where student.sid=sc.sid and sc.cid='02' and  sc.score70)r on student.sid=r.sid; 30.查询不及格的课程 select course.cname,sc.score from course right join sc on course.cid=sc.cid where sc.score80; 32.求每门课程的学生人数 select cid,count(sid) from sc group by cid; 33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩 select student.*,r.max(score) from student right join (select cid,max(score)  from sc group by cid having sc.cid='02')r on student.sid 34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 select a.* from sc as a right join sc as b on a.sid=b.sid where a.cid!=b.cid and a.score=b.score group by sid,cid,score; 36.查询每门功成绩最好的前两名 select a.sid,a.cid,a.score from sc as a left join sc as b on a.cid = b.cid and a.score=2; 39.查询选修了全部课程的学生信息 select student.* from student right join (select sid,coount(cid) from sc group by sid having count(sid)=3)r on student.sid=r.sid; 40.查询各学生的年龄，只按年份来算 select sage  41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

42.查询本周过生日的学生 select * from student where weekofyear(student.sage)=weekofyear(curdate()); 43.查询下周过生日的学生 select * from student where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1; 44.查询本月过生日的学生 select * from student where month(sage)=month(curdate()); 45.查询下月过生日的学生 select * from student where month(sage)=month(curdate())+1; 面试题 1.向student表中插入一条数据 insert into student values(1,'毛艺霖'，'男','2021计科2班'); 2.查询课程名为数据库，且分数低于60的学生姓名与分数 select student.name,score.degree from student right join score on student.sco=score.sco where score.cno='数据库' and score.degree