数据库03作业
-
查询所有大于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)
