数据库高级链表查询，重点可以多看看

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

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

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

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

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

select * from student left join class on class_id = cid;

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

select gender,count(man),count(women) from student group by gender;

这是错误的写法：

正确的是这个：

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

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

select student.sid,sname,number from score 
left join student on score.student_id=student.sid
left join course on score.corse_id=course.cid
where course.cname='生物';

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

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

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

select count(tname) as '姓李的老师的个数' from teacher where tname like '李%';

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

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

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

delete from score
where corse_id=(
    select cid from  
    teacher left join course on
    teacher.tid=course.teacher_id
    where tname='叶平'
                );
分析过程：
where corse.id=(course.teacher_id=teacher.tid 然后wheretname='叶平'，查出来叶平的cid)

delete from 表名 where 查询条件；

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

select corse_id as '课程ID',max(number) as '最高分',min(number) as '最低分' 
from score
group by corse_id;

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

select cname,count(cname)
from score
left join course on
course.cid=score.corse_id
group by cname;

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

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

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

select corse_id,avg(number)
from score
group by  corse_id
order by avg(number),corse_id desc;

查询每门课程的平均数：

select corse_id,avg(number)
from score
group by  corse_id;

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

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

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

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

-- 16.查询各个课程对应的学习人数

select cname,count(cname)
from 
course left join score on
course.cid=score.corse_id
group by cname;

-- 17.查询“1”课程分数小于70的学生姓名，按分数降序排列的同学学号

select student.sid,sname
from 
student left join score on
student.sid=score.student_id
where corse_id=1 and number < 70
order by number desc;

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

delete from score 
where student_id=2 and corse_id=1;

首先创建五张表：class 、 teacher 、 student 、course、 score

班级表：

create table class(
    cid int auto_increment primary key,
    caption varchar(60) not null default ''
)charset utf8;

​

老师表：

create table teacher(
    tid int not null primary key,
    tname varchar(60) not null default ''
)charset utf8;

学生表：

create table student(
    sid int not null primary key,
    sname varchar(60) not null default '',
    gender enum('women','man'),
    class_id int not null ,
    constraint fk_stu_class foreign key (class_id) references class(cid)
)charset utf8;

课程表：

create table course(
    cid int not null primary key,
    cname varchar(60) not null ,
    teacher_id int not null ,
    constraint fk_cou_teacher foreign key(teacher_id) references teacher(tid)
    
)charset utf8;

成绩表：

create table score(
    sid int auto_increment primary key,
    student_id int not null,
    corse_id int not null ,
    number int not null,
    constraint fk_sco_student foreign key(student_id) references student(sid),
    constraint fk_sco_corse foreign key(corse_id) references course(cid),
    unique(number)
    
)charset utf8;

插入班级

insert into class values(1,'三年二班'),(2, '三年三班'),(3, '一年二班'),(4, '三年七班');

插入学生

insert into student values(1,'张三','man',1),(2, '李四', 'man', 2),(3, '王五', 'women', 3),(4, '赵六', 'women', 4),(5, '钢蛋', 'women', 4);

insert into student values(6, '铁锤', 'man', 1),(7, '如花', 'man', 2),(8, '锅炉', 'man', 3),(9, '小乔', 'man', 4),(10, '公孙离 ', 'man',1),(11, '沈雷锴', 'women', 1),(12, 'xzn', 'women', 2),(13, 'cjl', 'women', 3), (14, 'xwj', 'women', 4),(15, 'egon', 'women', 2);

插入老师

insert into teacher values(1,'涨了'),(2, '李平'),(3, '刘海燕'),(4, '朱云海'),(5, '李杰');

插入课程

insert into course values(1,'生物',1),(2, '物理', 2),(3, '体育', 3),(4, '美术', 2);

插入成绩

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