学生表：
create table student
(
stuId varchar(10) primary key,
stuName varchar(10) not null,
stuSex char(2) check(stuSex = "男" or stuSex = "女" ),
age int check(age between 15 and 45)
)
课程表：
create table course
(
couId varchar(5) primary key,
couName varchar(20) not null
)
成绩表：
create table score
(
stuId varchar(10),
couId varchar(5),
grade int check(grade between 0 and 100),
constraint fk_stuId foreign key(stuId) references student(stuId),
constraint fk_couId foreign key(couId) references course(couId),
primary key(stuId, couId)
)
每个学生的所有信息查询：
select student.stuId,student.name,student.sex,student.age,course.couName,score.score from student,course,score where student.stuId = score.stuId and course.couId = score.couId;
查询每个学生的平均成绩：
select student.name as "姓名", avg(score) as "平均成绩" from student left join score on student.stuId = score.stuId group by student.stuId;
查询每个学生的总成绩：
mysql> select student.name as "姓名", avg(score) as "总成绩" from student left join score on student.stuId = score.stuId group by student.stuId;
查询每门科目的总成绩：
select course.couName as ”科目“, sum(score) as ”总成绩“ from course left join score on course.couId = score.couId group by course.couId;
查询每门科目的平均成绩：
mysql> select course.couName as "科目名称", avg(score) as "平均成绩" from course left join score on course.couId = score.couId group by course.couId;