数据库03作业

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

   mysql> select distinct student.sid,student.sname from score left join student on student.sid = student_id where number >80;
   +------+--------+
   | sid  | sname  |
   +------+--------+
   |    1 | 张三   |
   |    3 | 张强   |
   |    4 | 赵四   |
   |    5 | 张娟   |
   |    6 | 压缩   |
   |    7 | 安妮   |
   +------+--------+
   6 rows in set (0.00 sec)
   

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

mysql> select teacher.tid,teacher.tname ,count(teacher_id) as course_number
    -> from
    -> course left join teacher on teacher_id = teacher.tid
    -> group by teacher_id;
+------+-----------+---------------+
| tid  | tname     | course_number |
+------+-----------+---------------+
|    1 | 赵信      |             2 |
|    2 | 瑞文      |             2 |
|    3 | 张鱼妈    |             1 |
|    4 | 瑞兹      |             1 |
+------+-----------+---------------+
4 rows in set (0.00 sec)

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

mysql> select * from student left join class on class_id = class.cid;
+-----+--------+--------+----------+------+--------------+
| sid | sname  | gender | class_id | cid  | caption      |
+-----+--------+--------+----------+------+--------------+
|   1 | 张三   | 男     |        1 |    1 | 三年二班     |
|   5 | 张娟   | 女     |        1 |    1 | 三年二班     |
|   2 | 王红   | 女     |        2 |    2 | 一年三班     |
|   3 | 张强   | 男     |        2 |    2 | 一年三班     |
|   6 | 压缩   | 男     |        2 |    2 | 一年三班     |
|   4 | 赵四   | 男     |        3 |    3 | 三年一班     |
|   7 | 安妮   | 女     |        3 |    3 | 三年一班     |
+-----+--------+--------+----------+------+--------------+
7 rows in set (0.00 sec)

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

mysql> select count(sid) as stu_num,gender from student group by gender;
+---------+--------+
| stu_num | gender |
+---------+--------+
|       4 | 男     |
|       3 | 女     |
+---------+--------+
2 rows in set (0.00 sec)

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

mysql> select student.sid, student.sname ,number
    -> from
    -> score left join student
    -> on
    -> student_id = student.sid
    -> where
    -> course_id = 1
    -> order by number;
+------+--------+--------+
| sid  | sname  | number |
+------+--------+--------+
|    2 | 王红   |     62 |
|    5 | 张娟   |     64 |
|    3 | 张强   |     70 |
|    6 | 压缩   |     78 |
|    1 | 张三   |     85 |
|    4 | 赵四   |     88 |
|    7 | 安妮   |     96 |
+------+--------+--------+
7 rows in set (0.00 sec)

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

mysql> select student_id ,avg(number) from score group by student_id having avg(number)>80;
+------------+-------------+
| student_id | avg(number) |
+------------+-------------+
|          4 |     83.5000 |
|          6 |     80.5000 |
|          7 |     87.2500 |
+------------+-------------+
3 rows in set (0.00 sec)

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

mysql> select count(tname) as tea_num from teacher where tname like '赵%';
+---------+
| tea_num |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

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

mysql> select student.sname, student.sid from score left join student
    -> on student_id = student.sid
    -> where number <80;
+--------+------+
| sname  | sid  |
+--------+------+
| 张三   |    1 |
| 张三   |    1 |
| 王红   |    2 |
| 王红   |    2 |
| 王红   |    2 |
| 王红   |    2 |
| 张强   |    3 |
| 张强   |    3 |
| 赵四   |    4 |
| 张娟   |    5 |
| 张娟   |    5 |
| 张娟   |    5 |
| 压缩   |    6 |
| 压缩   |    6 |
| 安妮   |    7 |
+--------+------+
15 rows in set (0.00 sec)

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

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

mysql> select course_id,max(number),min(number) from score group by course_id;
+-----------+-------------+-------------+
| course_id | max(number) | min(number) |
+-----------+-------------+-------------+
|         1 |          96 |          62 |
|         2 |          97 |          65 |
|         3 |          90 |          60 |
|         4 |          92 |          65 |
+-----------+-------------+-------------+
4 rows in set (0.00 sec)

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

mysql> select count(student_id) from score group by course_id ;
+-------------------+
| count(student_id) |
+-------------------+
|                 7 |
|                 7 |
|                 7 |
|                 7 |
+-------------------+
4 rows in set (0.00 sec)

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

mysql> select sid,sname,gender,class.caption from student left join class on class_id = class.cid
    -> where sname like '张%';
+-----+--------+--------+--------------+
| sid | sname  | gender | caption      |
+-----+--------+--------+--------------+
|   1 | 张三   | 男     | 三年二班     |
|   5 | 张娟   | 女     | 三年二班     |
|   3 | 张强   | 男     | 一年三班     |
+-----+--------+--------+--------------+
3 rows in set (0.00 sec)

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

mysql> select avg(number) from score group by course_id order by avg(number),course_id desc ;
+-------------+
| avg(number) |
+-------------+
|     74.0000 |
|     77.5714 |
|     79.7143 |
|     79.8571 |
+-------------+
4 rows in set (0.00 sec)

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

mysql> select student.sid ,student.sname,avg(number) from score left join student
    -> on student_id = student.sid
    -> group by student_id having avg(number)>85;
+------+--------+-------------+
| sid  | sname  | avg(number) |
+------+--------+-------------+
|    7 | 安妮   |     87.2500 |
+------+--------+-------------+
1 row in set (0.00 sec)

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

mysql> select student.sname,student.sid from score left join student on
    -> student_id = student.sid
    -> where course_id = 3 and number >80;
+--------+------+
| sname  | sid  |
+--------+------+
| 赵四   |    4 |
| 压缩   |    6 |
+--------+------+
2 rows in set (0.00 sec)

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

mysql> select course.cname,count(student_id) as stu_num from score left join course
    -> on course_id = course.cid
    -> group by course_id;
+--------+---------+
| cname  | stu_num |
+--------+---------+
| 补刀   |       7 |
| 兵线   |       7 |
| 打野   |       7 |
| 团战   |       7 |
+--------+---------+
4 rows in set (0.00 sec)

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

mysql> select student.sid from score left join student
    -> on student_id = student.sid
    -> where course_id = 4 and number < 80 order by number desc;
+------+
| sid  |
+------+
|    5 |
|    6 |
|    2 |
+------+
3 rows in set (0.00 sec)

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

mysql> delete from score where student_id = 2 and course_id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from score;
+-----+------------+-----------+--------+
| sid | student_id | course_id | number |
+-----+------------+-----------+--------+
|   1 |          1 |         1 |     85 |
|   2 |          1 |         2 |     75 |
|   3 |          1 |         3 |     60 |
|   4 |          1 |         4 |     80 |
|   6 |          2 |         2 |     65 |
|   7 |          2 |         3 |     78 |
|   8 |          2 |         4 |     65 |
|   9 |          3 |         1 |     70 |
|  10 |          3 |         2 |     80 |
|  11 |          3 |         3 |     77 |
|  12 |          3 |         4 |     89 |
|  13 |          4 |         1 |     88 |
|  14 |          4 |         2 |     66 |
|  15 |          4 |         3 |     88 |
|  16 |          4 |         4 |     92 |
|  17 |          5 |         1 |     64 |
|  18 |          5 |         2 |     87 |
|  19 |          5 |         3 |     61 |
|  20 |          5 |         4 |     75 |
|  21 |          6 |         1 |     78 |
|  22 |          6 |         2 |     88 |
|  23 |          6 |         3 |     90 |
|  24 |          6 |         4 |     66 |
|  25 |          7 |         1 |     96 |
|  26 |          7 |         2 |     97 |
|  27 |          7 |         3 |     64 |
|  28 |          7 |         4 |     92 |
+-----+------------+-----------+--------+
27 rows in set (0.00 sec)