day35

需求

1. 查询所有大于60分的学生的姓名和学号  (DISTINCT: 去重)

2.查询每个老师教授的课程数量 和 老师信息

3. 查询学生的信息以及学生所在的班级信息

4、学生中男生的个数和女生的个数

5、获取所有学习'生物'的学生的学号和成绩；姓名

6、查询平均成绩大于60分的同学的学号和平均成绩； 

7、查询姓“李”的老师的个数；

8、查询课程成绩小于60分的同学的学号、姓名；

9. 删除学习“叶平”老师课的SC表记录

10.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
11.查询每门课程被选修的学生数
12.查询姓“张”的学生名单；

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

14.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
15.查询课程编号为3且课程成绩在80分以上的学生的学号和姓名；

16.查询各个课程及相应的选修人数

17.查询“4”课程分数小于60，按分数降序排列的同学学号
18.删除学号为“2”的同学的“1”课程的成绩

创建表格

# 班级表
create table class(
cid int auto_increment primary key,
caption varchar(32)
) charset utf8;

# 学生表
create table student(
sid int auto_increment primary key,
sname varchar(32),
sgender enum('男','女'),
class_id int not null,
constraint fk_student_class foreign key (class_id) references class(cid)
) charset utf8;

# 老师表
create table teacher(
tid int auto_increment primary key,
tname varchar(32)
) charset utf8;

# 课程表
create table course(
cid int auto_increment primary key,
cname varchar(32),
teacher_id int,
constraint fk_course_teacher foreign key (teacher_id) references teacher (tid)
) charset utf8;

# 成绩表
create table score(
sid int auto_increment primary key,
student_id int,
course_id int,
score int,
constraint fk_score_student foreign key (student_id) references student (sid),
constraint fk_score_course foreign key (course_id) references course (cid)
) charset utf8;

录入数据

insert into class(caption) values
('三年二班'),
('一年三班'),
('三年一班');

insert into student(sname,sgender,class_id) values
('钢弹','女',1),
('铁锤','男',1),
('山炮','男',2);

insert into teacher(tname) values
('尼克'),
('坦克'),
('艾克');

insert into course(cname,teacher_id) values
('生物',1),
('体育',1),
('物理',2);

insert into score(student_id,course_id,score) values
(1,1,60),
(1,2,59),
(2,2,100);

解答：

# 1
select sname,student_id from score
left join student on score.student_id=student.sid where score>60;
 
# 2
select tname,tid,count(cname)  from teacher
left join course on teacher.tid=course.teacher_id group by tname;

# 3
select sid,sname,sgender,class_id,caption from student
left join class on student.class_id=class.cid;

# 4
select sgender,count(sgender) from student
group by sgender;

# 5
select student_id,score,sname from score
left join student on score.student_id=student.sid
left join course on score.course_id=course.cid
where score>60;
    
# 6
select student_id,sname,avg(score) from student
left join score on student.sid=score.student_id
group by sname having avg(score)>60;
    
# 7
select tname,count(tname) from teacher
group by tname having tname like '李%';

# 8
select student_id,sname from student
left join score on student.sid=score.student_id
where score<60;

# 9
 
# 10
select max(score),min(score) from score
left join course on score.course_id=course.cid
group by cname;

# 11
select cname,count(student_id) from course
left join score on course.cid=score.course_id
group by cname;

# 12
select sname from student where sname like '张%';

# 13
select cid,cname,avg(score) from course
left join score on score.course_id=course.cid
group by cname
order by avg(score),cid desc; 
    
# 14
select student_id,sname,avg(score) from student
left join score on student.sid=score.student_id
group by sname having avg(score)>85;
    
# 15
select student_id,sname from student
left join score on student.sid=score.student_id
where score.course_id=3 and score.score>85;

# 16
select cname,count(student.sname) from course
left join score on score.course_id=course.cid
left join student on score.student_id=student.sid
group by cname;

# 17
select student.sid from student
left join score on score.student_id=student.sid
where score.course_id=4
order by score desc;