SQL准备：

共四个表：course课程表、student学生表、score学生表、teacher表

----建表语句如下----

DROP TABLE IF EXISTS `course`;CREATE TABLE `course` (`course_id` varchar(20) DEFAULT NULL COMMENT '课程id',`course_name` varchar(20) DEFAULT NULL COMMENT '课程名',`tea_id` varchar(20) DEFAULT NULL COMMENT '任课老师id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `course` */insert  into `course`(`course_id`,`course_name`,`tea_id`) values ('01','语文','1003'),('02','数学','1001'),('03','英语','1004'),('04','体育','1002'),('05','音乐','1002');/*Table structure for table `score` */DROP TABLE IF EXISTS `score`;CREATE TABLE `score` (`stu_id` varchar(20) DEFAULT NULL COMMENT '学生id',`course_id` varchar(20) DEFAULT NULL COMMENT '课程id',`course` int(11) DEFAULT NULL COMMENT '成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `score` */insert  into `score`(`stu_id`,`course_id`,`course`) values ('001','01',94),('002','01',74),('004','01',85),('005','01',64),('006','01',71),('007','01',48),('008','01',56),('009','01',75),('010','01',84),('011','01',61),('012','01',44),('013','01',47),('014','01',81),('015','01',90),('016','01',71),('017','01',58),('018','01',38),('019','01',46),('020','01',89),('001','02',63),('002','02',84),('004','02',93),('005','02',44),('006','02',90),('007','02',55),('008','02',34),('009','02',78),('010','02',68),('011','02',49),('012','02',74),('013','02',35),('014','02',39),('015','02',48),('016','02',89),('017','02',34),('018','02',58),('019','02',39),('020','02',59),('001','03',79),('002','03',87),('004','03',89),('005','03',99),('006','03',59),('007','03',70),('008','03',39),('009','03',60),('010','03',47),('011','03',70),('012','03',62),('013','03',93),('014','03',32),('015','03',84),('016','03',71),('017','03',55),('018','03',49),('019','03',93),('020','03',81),('001','04',54),('002','04',100),('004','04',59),('005','04',85),('007','04',63),('009','04',79),('010','04',34),('013','04',69),('014','04',40),('016','04',94),('017','04',34),('020','04',50),('005','05',85),('007','05',63),('009','05',79),('015','05',59),('018','05',87);/*Table structure for table `student` */DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (`stu_id` varchar(20) DEFAULT NULL COMMENT '学生id',`stu_name` varchar(20) DEFAULT NULL COMMENT '学生姓名',`birthday` date DEFAULT NULL COMMENT '出生日期',`sex` varchar(20) DEFAULT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `student` */insert  into `student`(`stu_id`,`stu_name`,`birthday`,`sex`) values ('001','彭于晏','1995-05-16','男'),('002','胡歌','1994-03-20','男'),('003','周杰伦','1995-04-30','男'),('004','刘德华','1998-08-28','男'),('005','唐国强','1993-09-10','男'),('006','陈道明','1992-11-12','男'),('007','陈坤','1999-04-09','男'),('008','吴京','1994-02-06','男'),('009','郭德纲','1992-12-05','男'),('010','于谦','1998-08-23','男'),('011','潘长江','1995-05-27','男'),('012','杨紫','1996-12-21','女'),('013','蒋欣','1997-11-08','女'),('014','赵丽颖','1990-01-09','女'),('015','刘亦菲','1993-01-14','女'),('016','周冬雨','1990-06-18','女'),('017','范冰冰','1992-07-04','女'),('018','李冰冰','1993-09-24','女'),('019','邓紫棋','1994-08-31','女'),('020','宋丹丹','1991-03-01','女');/*Table structure for table `teacher` */DROP TABLE IF EXISTS `teacher`;CREATE TABLE `teacher` (`tea_id` varchar(20) DEFAULT NULL COMMENT '老师id',`tea_name` varchar(20) DEFAULT NULL COMMENT '学生姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `teacher` */insert  into `teacher`(`tea_id`,`tea_name`) values ('1001','张高数'),('1002','李体音'),('1003','王子文'),('1004','刘丽英');

1. 查询姓名中带"冰"的学生名单

select stu_name from student where stu_name like '%冰%'

2. 查询姓"王"的老师的个数

select count(0) from teacher where tea_name like '%王%'

3. 检索课程编号为"04"且分数小于60的学生学号，结果按分数降序排列

select s.stu_id from course c left join score son c.course_id = s.course_id
where s.course < 60 and c.course_id = 4 order by course desc

4. 查询数学成绩不及格的学生和其对应的成绩

select st.stu_name from score sc left join student st on st.stu_id = sc.stu_id
where course < 60 and course_id = (select course_id from course where course_name = '数学')

5. 查询课程编号为"02"的总成绩

select sum(course) '数学总成绩' from score where course_id = '02'

6. 查询参加考试的学生个数

explain select count(distinct a.stu_id) from score a;

7. 查询各科成绩最高和最低的分数，以如下的形式显示：课程号，最高分，最低分

select course_name '课程号', max(course) '最高分',min(course) '最低分' from course cleft join score scon c.course_id = sc.course_id
group by c.course_id

8. 查询每门课程有多少学生参加了考试(有考试成绩)

select c.course_id,c.course_name,count(sc.course) from course c join score sc on c.course_id = sc.course_id
group by c.course_id

9. 查询男生、女生人数

select sum(case when sex = '男' then 1 else 0 end) 男生人数,sum(case when sex = '女' then 1 else 0 end) 女生人数from student

10. 查询平均成绩大于60分学生的学号和平均成绩

select stu_id,avg(course) avgscorefrom score
group by stu_id
having avgscore > 60

11. 查询至少选修两门课程的学生学号

select stu_id,count(course_id)  count_twofrom score group by stu_idhaving count_two > 1

12. 查询同姓(假设每个学生姓名的第一个字为姓)的学生名单并统计同姓人数

select group_concat(stu_name),left(st.stu_name,1) stunm,count(left(st.stu_name,1)) stucount from student st group by stunm having stucount > 1

13. 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

select course_id,avg(course) avgscorefrom score group by course_idorder by avgscore asc,course_id desc

14. 统计参加考试人数大于等于15的学科

select course_id,count(*) countnum from scoregroup by course_idhaving countnum > 14

15. 查询学生的总成绩并进行排名

select a.*,rank() over(order by sumscore desc)from
(select stu_id,sum(course) sumscorefrom score group by stu_idorder by sumscore desc) a

16. 查询平均成绩大于60分的学生的学号和平均成绩

select stu_id,avg(course) avgscorefrom scoregroup by stu_idhaving a.avgscore > 60

17. 查询一共参加两门课程且其一门为语文课程的学生的id和姓名

select st.stu_id,st.stu_name,count(sc.course) countscore from student stleft join score scon st.stu_id = sc.stu_idright join (select * from score where course_id = (select course_id from course where course_name = '语文')) bon sc.stu_id = b.stu_idgroup by st.stu_idhaving countscore = 2

18. 查询所有课程成绩小于60分的学生的学号、姓名

select a.stu_id,st.stu_name from (select stu_id,max(course) maxcoursefrom score group by stu_idhaving maxcourse < 60) a 
left join student st
on a.stu_id = st.stu_id

19. 查询出只选修了两门课程的全部学生的学号和姓名

select st.stu_id,st.stu_name,count(course_id) countcoursefrom student stleft join score scon st.stu_id = sc.stu_idgroup by st.stu_idhaving countcourse = 2

20. 查询两门以上不及格课程的同学的学号 及其平均成绩

select b.stu_id,c.avgcourse from (select a.stu_id,count(course_id) countcourse from (select stu_id,course_id,course from score where course < 60) agroup by stu_idhaving countcourse > 2) bleft join (select stu_id,avg(course) avgcourse from scoregroup by stu_id) con b.stu_id = c.stu_id

21. 查询所有学生的学号、姓名、选课数、总成绩

select st.stu_id,st.stu_name,count(sc.course_id),sum(sc.course)from student stleft join score scon sc.stu_id = st.stu_idgroup by st.stu_id

22. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select st.stu_id,st.stu_name,avg(sc.course) avgscorefrom student stleft join score scon st.stu_id = sc.stu_idgroup by st.stu_idhaving avgscore > 85

23. 查询学生的选课情况:学号，姓名，课程号，课程名称

select st.stu_id,st.stu_name,c.course_id,c.course_namefrom student st left join score scon st.stu_id = sc.stu_idleft join course con sc.course_id = c.course_idorder by st.stu_id,c.course_id

24. 查询出每门课程的及格人数和不及格人数

select c.course_id,c.course_name,sum(case when course >= 60 then 1 else 0 end) 及格人数,sum(case when course < 60 then 1 else 0 end) 不及格人数from  score scleft join course con sc.course_id = c.course_idgroup by c.course_id

25. 检索"01"课程分数小于60，按分数降序排列的学生信息

select sc.course_id,st.stu_id,st.stu_name,sc.coursefrom student stjoin score scon st.stu_id = sc.stu_idjoin course con sc.course_id = c.course_id where c.course_id = '01' and sc.course < 60order by sc.course desc

26. 查询任何一门课程成绩在70分以上的学生的姓名、课程名称和分数

select st.stu_name,group_concat(c.course_name),group_concat(sc.course)from student stleft join score scon st.stu_id = sc.stu_idleft join course con sc.course_id = c.course_idgroup by st.stu_idhaving min(sc.course) > 70

27. 查询两门及以上不及格课程的同学的学号，姓名及其平均成绩

-- 第一种
select st.stu_id,st.stu_name,round(avg(course),2) from student stjoin (select stu_id from score where course < 60 group by stu_id having count(*) >= 2) aon st.stu_id = a.stu_idjoin score scon st.stu_id = sc.stu_idgroup by st.stu_id
-- 第二种
select d.stu_id,d.stu_name,d.countcourse,e.avgcoursefrom (select b.stu_id,b.stu_name,b.countcourse from (select a.stu_id,st.stu_name,count(a.course_id) countcoursefrom student stright join (select stu_id,course_id,coursefrom scorewhere course < 60) aon a.stu_id = st.stu_idgroup by a.stu_id) bwhere b.countcourse >= 2) dleft join (select stu_id,avg(course) avgcoursefrom scoregroup by stu_id) eon d.stu_id = e.stu_id

28. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

-- 同一学生不同课程成绩相同
select sc1.stu_id,sc1.course_id,sc1.course,sc2.stu_id,sc2.course_id,sc2.course from score sc1join score sc2on sc1.stu_id = sc2.stu_idwhere sc1.course = sc2.courseand sc1.course_id != sc2.course_id
-- 不同学生不同课程成绩相同
select sc1.stu_id,sc1.course_id,sc1.course,sc2.stu_id,sc2.course_id,sc2.course from score sc1join score sc2on sc1.course = sc2.coursewhere sc1.course = sc2.courseand sc1.stu_id != sc2.stu_idand sc1.course_id != sc2.course_id