day35作业

目录

一、 创建图中的表

# 创建班级表
create table class (
cid int unsigned auto_increment primary key,
caption varchar(255) not null default ''
)charset utf8;

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

select * from class;


# 创建学生表，并外键关联班级表
create table student(
sid int unsigned auto_increment primary key,
sname varchar(255) not null default '',
gender enum ('男','女'),
class_id int unsigned not null default 0,
constraint fk_stu_class foreign key (class_id) references class(cid)
)charset utf8;

insert into student (sname,gender,class_id) values
('宋冬野','女',1),('*','男',1),('赵雷','男',2);

select * from student;

# 创建老师表
create table teacher(
tid int unsigned auto_increment primary key,
tname varchar(255) not null default ''
)charset utf8;

insert into teacher(tname) values('剥夺'),('苍空'),('饭岛');

select * from teacher;

#创建课程表，并关联老师表
create table course(
cid int unsigned auto_increment primary key,
cname varchar(255) not null default '',
teacher_id int unsigned not null default 0,
constraint fk_course_tea foreign key (teacher_id) 
references teacher(tid)
)charset utf8;

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

select * from course;


# 创建成绩表,并关联学生表和课程表
create table score(
sid int unsigned auto_increment primary key,
student_id int unsigned not null default 0,
course_id int unsigned not null default 0,
number decimal(3,0) not null default 0,

constraint fk_score_stu foreign key (student_id) references student(sid),
constraint fk_score_course foreign key (course_id) references course(cid)
)charset utf8;


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

select * from score;

二、完成下列需求

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

   select distinct student.sid,student.sname from score left join student 
   on score.student_id = student.sid where score.number > 60;

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

   select tname,count(tname) as course_num from teacher right join course 
   on teacher_id = tid group by tname ;

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

   select sname,caption from student left join class on cid = class_id;

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

   select gender,count(gender) as number from student group by gender;

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

   select student.sid,number,sname from student left join course on cid = class_id left join score on student_id = student.sid where course_id=1;

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

   select student_id,avg(number) from score 
   group by student_id having avg(number) > 60;

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

   select count(tname) as num from teacher where tname like'李%';

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

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

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

   delete from score where course_id in 
   (select cid from course left join teacher on tid = teacher_id where tname = '剥夺');

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

    select course_id,max(number),min(number) from score group by course_id;

11. 查询每门课程被选修的学生数

    select cname,count(cname) as student_num from course right join student 
    on class_id=cid group by cname;

12. 查询姓“张”的学生名单；

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

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

    select cname ,avg(number) from score left join course 
    on course_id = cid order by cname asc,cid desc;

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

    select student.sid,sname,avg(number) from score left join student 
    on student_id = student.sid  group by sname,student.sid having avg(number) > 85;

15. 查询课程编号为3且课程成绩在80分以上的学生的学号和姓名；

    select student.sid,sname from score left join student 
    on student_id = student.sid where course_id=3 and number >80;

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

    select cname,count(cname) from course right join student 
    on class_id = cid group by cname;

17. 查询“4”课程分数小于60，按分数降序排列的同学学号

    select student.sid from score join student 
    on student_id = student.sid  where number <60 order by student.sid desc ;

18. 删除学号为“2”的同学的“1”课程的成绩

    delete number from score where student_id=2 and course_id = 1;