demo

class表：

mysql> create table class(
    -> cid int auto_increment primary key,
    -> caption char(32) not null default ''
    -> )charset utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into class(caption) values('三年二班'),('一年三班'),('三年一班');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from class;
+-----+----------+
| cid | caption  |
+-----+----------+
|   1 | 三年二班 |
|   2 | 一年三班 |
|   3 | 三年一班 |
+-----+----------+
3 rows in set (0.00 sec)

student表：

mysql>  create table student(
    ->  sid int auto_increment primary key,
    ->  sname char(20) not null default '',
    -> gender enum('男','女') not null default '女',
    ->  class_id int not null default 1,
    ->  constraint fk_stu_class foreign key(class_id) references class(cid)
    ->  )charset utf8;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into student(sname,gender,class_id) values('钢蛋','女',1),('铁锤','女',1),('山炮','男',2),('张三','男',3),('张四','女',2),('如花','男',1),('六六','男',1);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0
mysql> select * from student;
+-----+-------+--------+----------+
| sid | sname | gender | class_id |
+-----+-------+--------+----------+
|   1 | 钢蛋  | 女     |        1 |
|   2 | 铁锤  | 女     |        1 |
|   3 | 山炮  | 男     |        2 |
|   4 | 张三  | 男     |        3 |
|   5 | 张四  | 女     |        2 |
|   6 | 如花  | 男     |        1 |
|   7 | 六六  | 男     |        1 |
+-----+-------+--------+----------+
7 rows in set (0.00 sec)

teacher表：

mysql> create table teacher(
    -> tid int auto_increment primary key,
    -> tname char(32) not null default ''
    -> )charset utf8;
mysql> insert into teacher(tname) values('波多野结衣'),('苍井空'),('饭岛'),('李白'),('叶平'),('李杰'),('张雷');
mysql> select * from teacher;
+-----+------------+
| tid | tname      |
+-----+------------+
|   1 | 波多野结衣 |
|   2 | 苍井空     |
|   3 | 饭岛       |
|   4 | 李白       |
|   5 | 叶平       |
|   6 | 李杰       |
|   7 | 张雷       |
+-----+------------+
7 rows in set (0.00 sec)

course表：

mysql> create table course(
    -> cid int auto_increment primary key,
    -> cname char(32) not null default '',
    -> teacher_id int not null default 1,
    -> constraint fk_cour_tea foreign key(teacher_id) references teacher(tid)
    -> )charset utf8;
mysql> insert into course(cname,teacher_id) values('生物',1),('体育',1),('物理',2),('语文',3),('数学',4),('英语',5),('美术',6),('计算机',7),('政治',7);
mysql> select * from course;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 体育   |          1 |
|   3 | 物理   |          2 |
|   4 | 语文   |          3 |
|   5 | 数学   |          4 |
|   6 | 英语   |          5 |
|   7 | 美术   |          6 |
|   8 | 计算机 |          7 |
|   9 | 政治   |          7 |
+-----+--------+------------+
9 rows in set (0.00 sec)

score表：

mysql> create table score(
    -> sid int auto_increment primary key,
    -> student_id int not null default 1,
    -> course_id int not null default 1,
    -> number int not null default 1,
    -> constraint fk_score_student foreign key (student_id) references student(si
d),
    -> constraint fk_score_course foreign key (course_id) references course(cid)
    -> )charset utf8;
mysql>insert into score(student_id,course_id,number) values (1,1,60),(1,2,59),(2,2,100),(2,3,88),(3,4,55),(4,5,100),(5,6,90),(6,4,78),(7,7,54);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from score;
+-----+------------+-----------+--------+
| sid | student_id | course_id | number |
+-----+------------+-----------+--------+
|   1 |          1 |         1 |     60 |
|   2 |          1 |         2 |     59 |
|   3 |          2 |         2 |    100 |
|   4 |          2 |         3 |     88 |
|   5 |          3 |         4 |     55 |
|   6 |          4 |         5 |    100 |
|   7 |          5 |         6 |     90 |
|   8 |          6 |         4 |     78 |
|   9 |          7 |         7 |     54 |
+-----+------------+-----------+--------+
9 rows in set (0.00 sec)

1. 查询所有大于60分的学生的姓名和学号  (DISTINCT: 去重)
mysql> select distinct sname,student.sid from student left join score on student.s
id=score.student_id where number>60;
+-------+-----+
| sname | sid |
+-------+-----+
| 铁锤  |   2 |
| 张三  |   4 |
| 张四  |   5 |
| 如花  |   6 |
+-------+-----+
4 rows in set (0.00 sec)
-- 2.查询每个老师教授的课程数量 和 老师信息
mysql> select count(cname),teacher.tname from course right join teacher on teach
er_id=teacher.tid group by teacher.tname;
+--------------+------------+
| count(cname) | tname      |
+--------------+------------+
|            1 | 叶平       |
|            2 | 张雷       |
|            1 | 李杰       |
|            1 | 李白       |
|            2 | 波多野结衣 |
|            1 | 苍井空     |
|            1 | 饭岛       |
+--------------+------------+
7 rows in set (0.00 sec)
-- 3. 查询学生的信息以及学生所在的班级信息
mysql> select sname,class.caption from student left join class on class_id=class.cid;
+-------+----------+
| sname | caption  |
+-------+----------+
| 钢蛋  | 三年二班 |
| 铁锤  | 三年二班 |
| 如花  | 三年二班 |
| 六六  | 三年二班 |
| 山炮  | 一年三班 |
| 张四  | 一年三班 |
| 张三  | 三年一班 |
+-------+----------+
7 rows in set (0.01 sec)
3 rows in set (0.00 sec)
-- 4、学生中男生的个数和女生的个数
mysql> select count(sid),gender from student group by gender;
+------------+--------+
| count(sid) | gender |
+------------+--------+
|          4 | 男     |
|          3 | 女     |
+------------+--------+
2 rows in set (0.00 sec)
-- 5、获取所有学习'生物'的学生的学号和成绩；姓名
mysql> select student.sid,sname,number,cname from student left join score on stu
dent.sid=score.student_id left join course on course.cid=score.course_id where c
name='生物';
+-----+-------+--------+-------+
| sid | sname | number | cname |
+-----+-------+--------+-------+
|   1 | 钢蛋  |     60 | 生物  |
+-----+-------+--------+-------+
1 row in set (0.00 sec)
-- 6、查询平均成绩大于60分的同学的学号和平均成绩； 
mysql> select student_id,avg(number) from score group by student_id having avg(n
umber)>60;
+------------+-------------+
| student_id | avg(number) |
+------------+-------------+
|          2 |     94.0000 |
|          4 |    100.0000 |
|          5 |     90.0000 |
|          6 |     78.0000 |
+------------+-------------+
4 rows in set (0.00 sec)
-- 7、查询姓“李”的老师的个数；
mysql>  select count(tname) from teacher where tname like '李%';
+--------------+
| count(tname) |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)
-- 8、查询课程成绩小于60分的同学的学号、姓名；
mysql> select sname,student.sid,number from student left join score on student.s
id=score.student_id where number<60;
+-------+-----+--------+
| sname | sid | number |
+-------+-----+--------+
| 钢蛋  |   1 |     59 |
| 山炮  |   3 |     55 |
| 六六  |   7 |     54 |
+-------+-----+--------+
3 rows in set (0.00 sec)
-- 9. 删除学习“叶平”老师课的SC表记录
mysql> delete from score where course_id in(select cid from course left join tea
cher on course.teacher_id=teacher.tid where tname='叶平');
Query OK, 1 row affected (0.00 sec)
-- 10.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
mysql> select course_id,max(number),min(number) from score group by course_id;
+-----------+-------------+-------------+
| course_id | max(number) | min(number) |
+-----------+-------------+-------------+
|         1 |          60 |          60 |
|         2 |         100 |          59 |
|         3 |          88 |          88 |
|         4 |          78 |          55 |
|         5 |         100 |         100 |
|         7 |          54 |          54 |
+-----------+-------------+-------------+
6 rows in set (0.00 sec)
-- 11.查询每门课程被选修的学生数
mysql> select count(student_id) as total,course_id from score group by course_id;
+-------+-----------+
| total | course_id |
+-------+-----------+
|     1 |         1 |
|     2 |         2 |
|     1 |         3 |
|     2 |         4 |
|     1 |         5 |
|     1 |         7 |
+-------+-----------+
6 rows in set (0.00 sec)
-- 12.查询姓“张”的学生名单；
mysql> select sname from student where sname like '张%';
+-------+
| sname |
+-------+
| 张三  |
| 张四  |
+-------+
2 rows in set (0.00 sec)

-- 13.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
mysql> select avg(number) as pj,course_id from score group by course_id order by pj asc,course_id desc;
+----------+-----------+
| pj       | course_id |
+----------+-----------+
|  54.0000 |         7 |
|  60.0000 |         1 |
|  66.5000 |         4 |
|  79.5000 |         2 |
|  88.0000 |         3 |
| 100.0000 |         5 |
+----------+-----------+
6 rows in set (0.00 sec)

-- 14.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
mysql> select student.sid,sname,avg(number) from score left join student on score.student_id=student.sid group by student_id having avg(number)>85;
+------+-------+-------------+
| sid  | sname | avg(number) |
+------+-------+-------------+
|    2 | 铁锤  |     94.0000 |
|    4 | 张三  |    100.0000 |
+------+-------+-------------+
2 rows in set (0.00 sec)
-- 15.查询课程编号为3且课程成绩在80分以上的学生的学号和姓名；
mysql> select student.sid,sname from score left join student on score.student_id
=student.sid where score.number>80 and course_id=3;
+------+-------+
| sid  | sname |
+------+-------+
|    2 | 铁锤  |
+------+-------+
1 row in set (0.00 sec)
-- 16.查询各个课程及相应的选修人数
mysql> select cname,count(cid) from course group by cname;
+--------+------------+
| cname  | count(cid) |
+--------+------------+
| 体育   |          1 |
| 政治   |          1 |
| 数学   |          1 |
| 物理   |          1 |
| 生物   |          1 |
| 美术   |          1 |
| 英语   |          1 |
| 计算机 |          1 |
| 语文   |          1 |
+--------+------------+
9 rows in set (0.00 sec)
-- 17.查询“4”课程分数小于60，按分数降序排列的同学学号
mysql>  select student_id,course_id,number from score where course_id=4 and numb
er<60 order by number desc;
+------------+-----------+--------+
| student_id | course_id | number |
+------------+-----------+--------+
|          3 |         4 |     55 |
+------------+-----------+--------+
1 row in set (0.00 sec)

-- 18.删除学号为“2”的同学的“1”课程的成绩
mysql> delete from score where student_id=2 and course_id=1;
Query OK, 0 rows affected (0.00 sec)