day35
目录
- 创建表
- 查询所有大于60分的学生的姓名和学号 (DISTINCT: 去重)
- 查询每个老师教授的课程数量 和 老师信息
- 查询学生的信息以及学生所在的班级信息
- 学生中男生的个数和女生的个数
- 获取所有学习'生物'的学生的学号和成绩；姓名
- 查询平均成绩大于60分的同学的学号和平均成绩
- 查询姓“李”的老师的个数
- 查询课程成绩小于60分的同学的学号、姓名
- 删除学习“叶平”老师课的SC表记录
- 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
- 查询每门课程被选修的学生数
- 查询姓“张”的学生名单
- 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
- 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
- 查询课程编号为3且课程成绩在80分以上的学生的学号和姓名
- 查询各个课程及相应的选修人数
- 查询“4”课程分数小于60，按分数降序排列的同学学号
- 删除学号为“2”的同学的“1”课程的成绩
创建表
mysql> select * from class;
+-----+----------+
| cid | caption |
+-----+----------+
| 1 | 三年二班 |
| 2 | 一年三班 |
| 3 | 三年一班 |
+-----+----------+
3 rows in set (0.00 sec)
mysql> select * from student;
+-----+-------+--------+----------+
| sid | sname | gender | class_id |
+-----+-------+--------+----------+
| 1 | 铁蛋 | 女 | 1 |
| 2 | 铁锤 | 女 | 1 |
| 3 | 山炮 | 男 | 2 |
+-----+-------+--------+----------+
3 rows in set (0.00 sec)
mysql> select * from teacher;
+-----+-------+
| tid | tname |
+-----+-------+
| 1 | 波多 |
| 2 | 苍空 |
| 3 | 饭岛 |
+-----+-------+
3 rows in set (0.00 sec)
mysql> select * from course;
+-----+-------+------------+
| cid | cname | teacher_id |
+-----+-------+------------+
| 1 | 生物 | 1 |
| 2 | 体育 | 1 |
| 3 | 物理 | 2 |
+-----+-------+------------+
3 rows in set (0.00 sec)
mysql> select * from score;
+-----+------------+-----------+--------+
| sid | student_id | course_id | number |
+-----+------------+-----------+--------+
| 1 | 1 | 1 | 60 |
| 2 | 1 | 2 | 59 |
| 3 | 2 | 2 | 100 |
+-----+------------+-----------+--------+
3 rows in set (0.00 sec)
查询所有大于60分的学生的姓名和学号 (DISTINCT: 去重)
mysql> select distinct sname,student.sid from score left join student on student
_id=student.sid where number>60;
+-------+------+
| sname | sid |
+-------+------+
| 铁锤 | 2 |
+-------+------+
1 row in set (0.00 sec)
查询每个老师教授的课程数量 和 老师信息
mysql> select tid, tname, count(tid)
-> from teacher left join course on
-> tid = teacher_id
-> group by tid;
+-----+-------+------------+
| tid | tname | count(tid) |
+-----+-------+------------+
| 1 | 波多 | 2 |
| 2 | 苍空 | 1 |
| 3 | 饭岛 | 1 |
+-----+-------+------------+
3 rows in set (0.00 sec)
查询学生的信息以及学生所在的班级信息
mysql> select sid,sname,gender,caption from student left join class on class_id=
class.cid;
+-----+-------+--------+----------+
| sid | sname | gender | caption |
+-----+-------+--------+----------+
| 1 | 铁蛋 | 女 | 三年二班 |
| 2 | 铁锤 | 女 | 三年二班 |
| 3 | 山炮 | 男 | 一年三班 |
+-----+-------+--------+----------+
3 rows in set (0.00 sec)
学生中男生的个数和女生的个数
mysql> select gender,count(sid) as num from student group by gender;
+--------+-----+
| gender | num |
+--------+-----+
| 男 | 1 |
| 女 | 2 |
+--------+-----+
2 rows in set (0.00 sec)
获取所有学习'生物'的学生的学号和成绩；姓名
mysql> select student.sid,number,cname from score left join student on student_id=student.sid
-> left join course on course_id=course.cid where cname='生物';
+------+--------+-------+
| sid | number | cname |
+------+--------+-------+
| 1 | 60 | 生物 |
+------+--------+-------+
1 row in set (0.00 sec)
查询平均成绩大于60分的同学的学号和平均成绩
mysql> select student.sid,avg(number) as number from score left join student on student_id=student.sid group by student.sid having
-> number>60;
+------+----------+
| sid | number |
+------+----------+
| 2 | 100.0000 |
+------+----------+
1 row in set (0.00 sec)
查询姓“李”的老师的个数
mysql> select count(tname) as 姓李 from teacher where tname like '李%';
+------+
| 姓李 |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
查询课程成绩小于60分的同学的学号、姓名
mysql> select student.sid,sname from score left join student on student_id=student.sid where number<60;
+------+-------+
| sid | sname |
+------+-------+
| 1 | 铁蛋 |
+------+-------+
1 row in set (0.00 sec)
删除学习“叶平”老师课的SC表记录
mysql> delete from score
-> where course_id in (select cid from course left join teacher on
-> teacher_id = tid
-> where tname = '叶平');
查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
mysql> select cid,max(number) as max,min(number) as min from score left join course on course_id=course.cid group by cid;
+------+------+------+
| cid | max | min |
+------+------+------+
| 1 | 60 | 60 |
| 2 | 100 | 59 |
+------+------+------+
2 rows in set (0.00 sec)
查询每门课程被选修的学生数
mysql> select cname,count(course_id) as '学生数' from score left join course on course_id=course.cid group by cname;
+-------+--------+
| cname | 学生数 |
+-------+--------+
| 体育 | 2 |
| 生物 | 1 |
+-------+--------+
2 rows in set (0.00 sec)
查询姓“张”的学生名单
mysql> select * from student where sname like '张%';
Empty set (0.00 sec)
查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
mysql> ;select cname,avg(number) from score left join course on course_id=course.cid group by cname;
ERROR:
No query specified
+-------+-------------+
| cname | avg(number) |
+-------+-------------+
| 体育 | 79.5000 |
| 生物 | 60.0000 |
+-------+-------------+
2 rows in set (0.00 sec)
查询平均成绩大于85的所有学生的学号、姓名和平均成绩
mysql> select student.sid,sname,avg(number) as av from score left join student on student_id=student.sid group by student.sid having av>85;
+------+-------+----------+
| sid | sname | av |
+------+-------+----------+
| 2 | 铁锤 | 100.0000 |
+------+-------+----------+
1 row in set (0.00 sec)
查询课程编号为3且课程成绩在80分以上的学生的学号和姓名
mysql> select student_id , sname from student left join score on
-> student_id = student.sid
-> where course_id = 3 and number > 80;
Empty set (0.00 sec)
查询各个课程及相应的选修人数
mysql> select cname,count(cname) from score right join course on course_id=course.cid group by cname;
+-------+--------------+
| cname | count(cname) |
+-------+--------------+
| 体育 | 2 |
| 物理 | 1 |
| 生物 | 1 |
+-------+--------------+
3 rows in set (0.00 sec)
查询“4”课程分数小于60，按分数降序排列的同学学号
mysql> select student_id from score right join course on course_id=course.cid where cid=4 and number<60 order by number desc ;
Empty set (0.00 sec)
删除学号为“2”的同学的“1”课程的成绩
mysql> delete from score
-> where student_id = 2 and course_id = 1;
Query OK, 0 rows affected (0.00 sec)
